Switch to: V12 • V11 • V10 • V9 • V8 • V7 • V6 • V5
Show pagesource •
/ valentina / articles / vserver_rest

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']
# **********************************************************************************************
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

© 1998-2021 Paradigma Software Inc. All rights reserved.
valentina/articles/vserver_rest.txt · Last modified: 2021/02/14 15:41 (external edit)