Switch to: V10 • V9 • V8 • V7 • V6 • V5
/ valentina / articles / vserver_rest
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

© 1998-2020 Paradigma Software Inc. All rights reserved.
valentina/articles/vserver_rest.txt · Last modified: 2019/06/11 15:27 by ivan