Table of Contents
REST for Valentina Server v6.0
Introduction
Representational State Transfer (REST) is a modern software architecture to build scalable web-services. It is widely used as an alternative to SOAP, WSDL protocols and other similar web approaches. Usually REST is based on HTTP/HTTPS, but it is not obligatory - it could be even based on some proprietary binary protocols.
Actually, REST is just a set of some informal rules and it`s constrainted to implement CRUD (CreateReadUpdateDelete) software paradigm. In other words, there are only 4 verbs which can be used to retrieve and manipulate with data. Another REST's term is “resource” which the verbs above are applied to.
CRUD verbs in HTTP terms:
- GET - for retrieving “resource” data;
- POST - usually means “create resource”;
- PUT - usually means “update “resource”;
- DELETE - for deleting “resource”.
Pay attention that “resource” is flexible and service-specific term (anyway it is usually the noun which is affected by the verb). Often, “POST” and “PUT” performs similar job and many services implement only “POST” verb for “create or update resource” operations.
Why use the REST (not complete and not ordered list):
- stateless;
- simple;
- No client libs required - the only thing you need is ability to work with:
- HTTP(S) (send/ receive HTTP packets);
- URL parsing;
- JSON/XML for data in HTTP packets;
- You are free to choose almost any software language to write your apps;
- portability;
- self-descriptive;
vServer REST
REST is available for both - Valentina and SQLite databases. The only difference is a “vendor” field in the body of POST request to “rest/session_id/sql” resource.
Check your vServer's ini:
[REST] ; Port to listen REST clients. ; 0 - do not use REST port. PORT_REST = 8080 ; Optional - Port to listen REST SSL clients. ; 0 - do not use REST SSL port. ;Port_REST_SSL=0 ; Optional - The path, where a certificate is stored (The file must contain both - public certificate and private key). ;REST_SSL_Certificate=
Read More... about INI-file of Valentina Server 6.0
vServer REST resources
URL | GET | POST | DELETE |
---|---|---|---|
rest | no | yes (create session) | no |
rest/session_id | yes (info) | no | yes(close) |
rest/session_id/sql | no | yes (create QueryRes) | no |
rest/session_id/sql/QueryResult_xx | yes(queryRes) | no | yes(drop) |
…/QueryResult_xx/cursor_structure | yes | no | no |
…/QueryResult_xx/cursor_records | yes | no | no |
rest/session_id/sql_fast | no | yes (execute and return data) | no |
vServer REST rules
There are some rules for REST:
- each “connection” (session) may have many queries;
- rest/session_id is a “virtual” resource (looks like common but available for user with the same session_id cookie);
- rest/session_id/sql/QueryResult_xx is a “virtual” resource (looks like common but available - for user with the same session_id cookie). Note that user may have few QueryResults;
- closing QueryResult_xx means deleting cursor …;
- closing Session means deleting all QueryResult and logout.
vServer REST flow
POST credentials to /rest and get back own rest/session_id URL (with session-id cookie).
Request JSON: <head>{'Accept': 'application/json', 'Content-Type': 'application/json'}</head> <body> {"user" : "sa", "password" : "md5 password digest"}</body>
Request XML: <head>{'Accept': 'application/xml', 'Content-Type': 'application/xml'}</head> <body> <root> <user>sa</user> <password>md5 password digest</password> </root> </body>
Answer: HTTP/1.1 201 Created Location: rest/session_id Set-Cookie: " REST_SESSION_ID "= session_id;
GET rest/session_id
Request JSON: <head>{'Accept': 'application/json', 'Content-Type': 'application/json', 'Cookie': session_id}}</head>
Request XML: <head>{'Accept': 'application/xml', 'Content-Type': 'application/xml', 'Cookie': session_id}}</head>
Answer JSON: HTTP/1.1 200 OK Content-Type: application/json { "User": "sa", "SQL_Location": "rest/session_id/sql" "SQL_Fast_Location": "rest/session_id/sql_fast" }
Answer XML: HTTP/1.1 200 OK Content-Type: application/xml <session> <user>sa</user> <SQL_Location ref="rest/session_id/sql"/> <SQL_Fast_Location ref="rest/session_id/sql_fast"/> </session>
Send SQL-query and get answer back
"Classic" way:
- POST query to rest/session_id/sql and get back own rest/session_id/sql/QueryResult_xx URL.
Request JSON: <head>{'Accept': 'application/json', 'Content-Type': 'application/json', 'Cookie': session_id}}</head> <body> {"vendor" : "Valentina", "database" : "db1", "Query" : "select * from t1 where f1 > :1 and f2 = :2", "Params" : "[1, \"Smith\"]"}</body>
Request XML: <head>{'Accept': 'application/xml', 'Content-Type': 'application/xml', 'Cookie': session_id}}</head> <body> <root> <vendor>Valentina</vendor> <database>db1</database> <Query>select * from t1</Query> </root> </body>
Answer: HTTP/1.1 201 Created Location: rest/session_id/sql/QueryResult_xx;
- GET rest/session_id/sql/QueryResult_xx. (Similar for cursor_structure and cursor_records)
Request JSON: <head>{'Accept': 'application/json', 'Content-Type': 'application/json', 'Cookie': session_id}}</head>
Request XML: <head>{'Accept': 'application/xml', 'Content-Type': 'application/xml', 'Cookie': session_id}}</head>
Answer JSON: HTTP/1.1 200 OK Content-Type: application/json { "Cursor": { "Structure": "...", "Records": "..." }, "AffectedRows": ... }
Answer XML: HTTP/1.1 200 OK Content-Type: application/xml <SQL> <Cursor> <Structure ref="..."/> <Records ref="..."/> </Cursor> <AffectedRows>...</AffectedRows> </SQL>
- DELETE rest/session_id/sql/QueryResult_xx.
Request JSON: <head>{'Accept': 'application/json', 'Content-Type': 'application/json', 'Cookie': session_id}</head>
Request XML: <head>{'Accept': 'application/xml', 'Content-Type': 'application/xml', 'Cookie': session_id}</head>
Answer: HTTP/1.1 204 No Content
"Fast" way
- POST query to rest/session_id/sql_fast (perform sql statement and get back cursor/execution data).
Request JSON: <head>{'Accept': 'application/json', 'Content-Type': 'application/json', 'Cookie': session_id}</head> <body> {"vendor" : "Valentina", "database" : "db1", "Query" : "select * from t1 where f1 > :1 and f2 = :2", "Params" : "[1, \"Smith\"]"}</body>
Request XML: <head>{'Accept': 'application/xml', 'Content-Type': 'application/xml', 'Cookie': session_id}}</head> <body> <root> <vendor>Valentina</vendor> <database>db1</database> <Query>select * from t1</Query> </root> </body>
Answer JSON as cursor: HTTP/1.1 200 OK Content-Type: application/json { "name": "t1", "fields": ["f1","f2"], "records": [ ["val1","val2"], ["val3","val4"] ... ] }
Answer JSON as AffectedRecords: HTTP/1.1 200 OK Content-Type: application/json { "AffectedRows":... }
Answer XML as cursor: HTTP/1.1 200 OK Content-Type: application/xml <ValentinaCursor> ### or <SqliteCursor> <structure> <field name="f1"/> <field name="f2"/> ... </structure> <data> <ROW> <f1>value</f1> <f2>value</f2> ... </ROW> ... </data> </ValentinaCursor>
Answer XML as AffectedRecords: HTTP/1.1 200 OK Content-Type: application/xml <AffectedRows>...</AffectedRows>
DELETE rest/session_id.
Request JSON: <head>{'Accept': 'application/json', 'Content-Type': 'application/json', 'Cookie': session_id}</head>
Request XML: <head>{'Accept': 'application/xml', 'Content-Type': 'application/xml', 'Cookie': session_id}</head>
Answer: HTTP/1.1 204 No Content
Examples
There are few Python code snippets to show how to use it.
Python requires no additional modules to work with REST. The only thing you need is:
import urllib2 import json or xml import hashlib
Open/Close connection:
# ********************************************************************************************** def open_connection(host, user, pwd): """Open connection and return session URL.""" # Set the request authentication headers headers = {'Accept': 'application/json', 'Content-Type': 'application/json'} body = "{ \"User\" : \"" + user + "\", \"Password\" : \"" + pwd + "\" }" url = host + '/rest' # Send the POST request request = urllib2.Request(url, body, headers) request.get_method = lambda: 'POST' # Read the response opener = urllib2.build_opener(urllib2.HTTPHandler) response = opener.open(request) assert(response.code == 201) info = response.info() return info['Set-Cookie'], info['Location'] # ********************************************************************************************** def close_connection(host, session_id, session_url): """Close connection.""" if session_id is not None: # Set the request authentication headers headers = {'Accept': 'application/json', 'Content-Type': 'application/json', 'Cookie': session_id} url = host + session_url # Send the DELETE request request = urllib2.Request(url, None, headers) request.get_method = lambda: 'DELETE' # Read the response opener = urllib2.build_opener(urllib2.HTTPHandler) response = opener.open(request) assert(response.code == 204) return None
General "GET" function:
# ********************************************************************************************** def get_info(host, session_id, url): """Return GET info.""" # Set the request authentication headers headers = {'Accept': 'application/json', 'Content-Type': 'application/json', 'Cookie': session_id} url = host + url # Send the GET request request = urllib2.Request(url, None, headers) request.get_method = lambda: 'GET' # Read the response opener = urllib2.build_opener(urllib2.HTTPHandler) response = opener.open(request) assert(response.code == 200) return response.read()
Execute SQL:
# ********************************************************************************************** def do_sql(host, session_id, sql_url, sql_vendor, sql_database, sql, params = None): """Perform SQL and return queryResult URL.""" # Set the request authentication headers headers = {'Accept': 'application/json', 'Content-Type': 'application/json', 'Cookie': session_id} body = "{ \"vendor\" : \"" + sql_vendor + "\", \"database\" : \"" + sql_database + "\" ,\"Query\" : \"" + sql + "\"" if params is not None: body = body + ",\"Params\" : " + params body = body + " }" url = host + sql_url # Send the POST request request = urllib2.Request(url, body, headers) request.get_method = lambda: 'POST' # Read the response opener = urllib2.build_opener(urllib2.HTTPHandler) response = opener.open(request) assert(response.code == 201) return response.info()['Location']
# ********************************************************************************************** def do_fastsql(language, vendor, host, session_id, sql_url, sql_database, sql, paramList=None): """Perform SQL and return queryResult.""" # Set the request authentication headers headers = {'Accept': 'application/json', 'Content-Type': 'application/json', 'Cookie': session_id} body = "{ \"vendor\" : \"" + vendor + "\", \"database\" : \"" + sql_database + "\" ,\"Query\" : \"" + sql + "\"" if paramList is not None: body += ",\"Params\" : " + json.dumps(paramList) body += " }" url = host + sql_url # Send the POST request request = urllib2.Request(url, body, headers) request.get_method = lambda: 'POST' # Read the response opener = urllib2.build_opener(urllib2.HTTPHandler) response = opener.open(request) assert (response.code == 200) return response.read()
Print cursor structure and data:
"Classic" way:
# ********************************************************************************************** gHost = "http://localhost:8080" gUser = "sa" gPassword = hashlib.md5("sa").hexdigest() gSQLDatabase = "db1" gSQLQuery = "select ** from t1 where f1 > :1 and f2 = :2;" # ********************************************************************************************** def main(): session_id = None session_url = None try: # Open connection session_id, session_url = open_connection(gHost, gUser, gPassword) assert(session_id != "") # Get connection info and interpret the JSON response connection_info = json.loads(get_info(gHost, session_id, session_url).decode('utf8')) print('User - ' + connection_info['User']) print('SQL URL - ' + connection_info['SQL_Location']) # SQL query query_result_url = do_sql( gHost, session_id, connection_info['SQL_Location'], gSQLDatabase, gSQLQuery, "[1, \"Smith\"]") print('queryResultURL - ' + query_result_url) # Get queryResult info and interpret the JSON response query_result_info_processed = False query_result_info = json.loads(get_info(gHost, session_id, query_result_url).decode('utf8')) if 'Cursor' in query_result_info: print('queryResultInfo.Cursor.Structure - ' + query_result_info['Cursor']['Structure']) print('queryResultInfo.Cursor.Records - ' + query_result_info['Cursor']['Records']) # Get cursor structure and interpret the JSON response cursor_structure = json.loads(get_info( gHost, session_id, query_result_info['Cursor']['Structure']).decode('utf8')) print('------------------------------------------------------------------------------------') print(cursor_structure['fields']) print('queryResultInfo.Cursor.Structure.recordCount - ' + cursor_structure['recordCount']) print('------------------------------------------------------------------------------------') # Get cursor data and interpret the JSON response cursor_data = json.loads(get_info( gHost, session_id, query_result_info['Cursor']['Records']).decode('utf8')) for rowOfValues in cursor_data['records']: print(rowOfValues) print('------------------------------------------------------------------------------------') query_result_info_processed = True if 'AffectedRows' in query_result_info: print('queryResultInfo.AffectedRows - ' + str(query_result_info['AffectedRows'])) query_result_info_processed = True if not query_result_info_processed: print query_result_info except urllib2.HTTPError as httpErr: if httpErr.code == 400: err_body = json.loads(httpErr.read().decode('utf8')) print("Error - " + err_body['Error']) else: print("Error - " + str(httpErr)) except urllib2.URLError as urlErr: print(urlErr.reason) # Close connection session_id = close_connection(gHost, session_id, session_url) assert(session_id is None) # ********************************************************************************************** main()
"Fast" way:
# ********************************************************************************************** gHost = "http://localhost:8080" gUser = "sa" gPassword = hashlib.md5("sa").hexdigest() gSQLDatabase = "db1" gSQLQuery = "select ** from t1 where f1 > :1 and f2 = :2;" # ********************************************************************************************** def main(): session_id = None session_url = None try: # Open connection session_id, session_url = open_connection(gHost, gUser, gPassword) assert(session_id != "") # Get connection info and interpret the JSON response connection_info = json.loads(get_info(gHost, session_id, session_url).decode('utf8')) print('User - ' + connection_info['User']) print('Fast SQL URL - ' + connection_info['SQL_Fast_Location']) # SQL query and get queryResult info and interpret the JSON response fast_query_result = rest_common.do_fastsql( language, vendor, gHost, session_id, connection_info['SQL_Fast_Location'], gSQLDatabase, gSQLQuery, "[1, \"Smith\"]") print('fast_query_result - ' + fast_query_result) fast_query_result_data = json.loads(fast_query_result.decode('utf8')) # Interpret the JSON response query_result_data_processed = False if 'fields' in fast_query_result_data: print('------------------------------------------------------------------------------------') print(fast_query_result_data['fields']) print('------------------------------------------------------------------------------------') # cursor data for rowOfValues in fast_query_result_data['records']: print(rowOfValues) print('------------------------------------------------------------------------------------') query_result_data_processed = True if 'AffectedRows' in fast_query_result_data: print('fast_query_result_info.AffectedRows - ' + str(fast_query_result_data['AffectedRows'])) query_result_data_processed = True if not query_result_data_processed: print query_result_data_processed except urllib2.HTTPError as httpErr: if httpErr.code == 400: err_body = json.loads(httpErr.read().decode('utf8')) print("Error - " + err_body['Error']) else: print("Error - " + str(httpErr)) except urllib2.URLError as urlErr: print(urlErr.reason) # Close connection session_id = close_connection(gHost, session_id, session_url) assert(session_id is None) # ********************************************************************************************** main()
Generating vReports:
About REST and Reports you can read the following wiki page: Valentina Report Server - REST Access