Table of Contents
REST & Valentina Server
Representational State Transfer (REST) is a modern software architecture to build scalable web-services. Using REST provides a simple, open alternative interface to Valentina Server other than the native client architecture provided by Valentina ADKs. This makes it possible to build solutions around Valentina Server from almost any platform, such as mobile platforms like iOS and Android.
Valentina Release 6 introduced a REST implementation to Valentina Server.
Introduction
REST 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. In a nutshell, REST is a set of some informal rules to implement CRUD (CreateReadUpdateDelete) software paradigm. There are only four verbs used to retrieve and manipulate with data. A REST resource refers to items for which the verbs are applied:
CRUD verbs in HTTP terms
- GET - used to retrieve “resource” data
- POST - used to “create resource”
- PUT - used to “update “resource”
- DELETE - used to delete a “resource”
Whatever is treated as a 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 REST
- 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;
- cacheable;
- self-descriptive;
Using REST with Valentina Database Servers
REST is available for both native Valentina DB and SQLite databases. The only difference is a “vendor” field in the body of a POST request to “rest/session_id/sql” resource. REST must be set up in your Server ini file with or without options:
[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 an introduction to the Valentina Server INI-file to learn about important parameters for working with Valentina Server.
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" }
Answer XML: HTTP/1.1 200 OK Content-Type: application/xml <session> <user>sa</user> <SQL_Location ref="rest/session_id/sql"/> </session>
- 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
- 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']
Print cursor structure and data:
# ********************************************************************************************** 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'], 'Valentina', 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()
Execute SQL and return data (fast):
Generally, CRUD paradigm engages to separate “create” and “read” resource phases, but probably it is too strict, especially for the “temporary resource” case like execute some SQL and return either affected rows count or some cursor. Anyway, we think it could be helpful to send single “POST” request and get the result immediatelly.
# ********************************************************************************************** def do_fastsql(host, session_id, sql_url, sql_vendor, sql_database, sql, params=None): """Perform SQL and return queryResult.""" # 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 += ",\"Params\" : " + params 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()
# ********************************************************************************************** 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 and get queryResult info and interpret the JSON response fast_query_result = rest_common.do_fastsql( gHost, session_id, connection_info['SQL_Fast_Location'], 'Valentina', gSQLDatabase, gSQLQuery) 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 Reports with REST & Valentina Server
About REST and Reports you can read the following wiki page: Valentina Report Server - REST Access