MasonSQL - CRUD interface - version 1.6.3

The document describes the communication interface for the exchange of data for the essential Create, Retrieve, Update and Delete (CRUD) operations.

The interface between the client and the server is only available over the standard HTTPS with basic authentication.

In order to maintain a backwards compatibility with previous versions of MasonSQL, and to facilitate the development of the client, we used exclusively GET and POST request methods.

MasonSQL GUI never uses the CRUD interface!
The CRUD interface is provided for integrating external applications.

On this page:

Class of Object

The class of object is encoded in an Uniform Resource Identifier (URI).

Examples:

https://myapp.mydomain.com/dbms/campionamenti
https://myapp.mydomain.com/dbms/parametri
https://myapp.mydomain.com/dbms/formulari/siti

The method and the unique key of the object are also encoded in the requested Uniform Resource Locator (URL).

Examples:

https://myapp.mydomain.com/dbms/campionamenti?method=update&key=12
https://myapp.mydomain.com/dbms/parametri?key=888
https://myapp.mydomain.com/dbms/parametri?method=select&key=23
https://myapp.mydomain.com/dbms/campionamenti?method=delete&key=4
https://myapp.mydomain.com/dbms/parametri?method=newkey
https://myapp.mydomain.com/dbms/parametri?method=insert&key=33
https://myapp.mydomain.com/dbms/campionamenti?method=xls

Each call is of an atomic type (stateless) or it ends with a transaction completed response from the server - it does not require subsequent interactions. One exception to this rule is the newkey method that returns a key for the subsequent insertion of a new object. The key is then discarded after it has been used.

The data objects (sets of records) are exchanged in UTF-8 encoded XML format.

The following scheme is used for the response for a backwards compatibility reasons:

<?xml version="1.0" encoding="utf-8"?>
<dbms method="retrieve" user="administrator" timestamp="28/04/2010 07:45:17.997114 CEST" start="0" rows="1" max_rows="1529">
      <campionamenti id="..." ... />
</dbms>

The <dbms> tag identifies the initial path of the URI and encloses the a tag which identifies the object's class; The enclosed tag name is composed of scheme name and table name separated by a dot (scheme_name.table_name); In the absence of the scheme name the default name defined in Apache file conf. DefaultSchema is assumed (Default value: public).

Warning: It is mandatory to use the tag name from the corresponding URI for the edit and the insert operations.

The <dbms> tag attributes:

Attribute	Description
method	required method
username	corresponds to the user who made the request
timestamp	a time reference of the transaction.

Attributes specific for the retrieve operation:

Attribute	Description
start	offset of the first returned record
rows	the number of records returned
max_rows	a maximum number of processed records

The table fields in the XML schema are represented by the tag attributes.

Example:

<?xml version="1.0" encoding="utf-8"?>
<dbms method="retrieve" user="administrator" timestamp="28/04/2010 07:48:19.200846 CEST" start="0" rows="1" max_rows="1">
      <campionamenti stato_rapporto="firmato" data_ingresso="01/01/2010" persona_prelievo="1135"
          persona_responsabile3="" id_laboratorio="12" data_inizio_prova="01/01/2010"
          persona_responsabile2="" stato="Completato" durata_prelievo="" id="1136"
          id_tipi_campionamenti="514" osservazioni="" persona_responsabile="41" id_sito="41"
          data_fine_prova="09/10/2000" codice="[555444/03]" ora_prelievo="11:40"
          data_prelievo="03/10/2000" id_rapporto="1114"/>
</dbms>

A prohibited or reserved characters in the names of the attributes (table fields) must be encoded:

Prohibited or Reserved Characters	Encoded Characters
<	&lt;
&	&amp;
<CR>	\n
...	...

father_key attribute

A special value -2 should be used in cases when the method requires the father_key attribute and the table doesn't have a father. -2 inhibits the control of the father's presence.
Table has a father if there exists a method FATHER in its .mql file.

Authentication

The interface provides basic authentication (password protection, HTTPS SSL encrypted channel). Users inherit the group rights according to the application's group policies. If the user belongs to a certain group it will have the right to access the inherent objects, as is the case across the entire web interface.

Access Permissions

The autorizations table is available through web interface from the menu Autorizzazioni->Funzioni e autorizzazioni.
The table maps permitted operations (Dbms, Select, Update, Insert, Delete, Log, Menu, Print, PrintSel, XLS) for each group.
In order to use CRUD interface for access to a specific object/table the user must belong to a group with the Dbms permission set.
Any access to the methods and classes of objects/tables from CRUD interface is controlled by the Dbms authorization and by autorizations Select, Update, Insert, Delete to data access.
The granted permissions (on objects/tables) can be verified with the info method.

Warning: Be carefull before disabling any permissions for the Funzioni record because with that you will prevent access to the permissions table itself from the Autorizzazioni->Funzioni e autorizzazioni menu.

Class Methods and Attributes

Key name

The classes of objects (tables) use a unique key column with default name id. However other name can be used instead of the default name so, to make sure, the unique key name can be retrieved with keyname method:

Example:

https://myapp.mydomain.com/dbms/campionamenti?method=keyname

It will return the following XML document containing the name of the key field:

Codice: 200 OK
Content-Type: text/xml; charset=utf-8

<?xml version="1.0" encoding="utf-8"?>
<dbms method="keyname" user="admin" timestamp="28/04/2010 12:03:10" >
      <campionamenti keyname="id" />
</dbms>

In case of an error, the server responds with the error code 400 accompanied by the document containing the error message.

Info

The info method returns a XML document containing information about the object.

Example:

https://myapp.mydomain.com/dbms/campionamenti?method=info

Will return the following XML:

Codice: 200 OK
Content-Type: text/xml; charset=utf-8

<?xml version="1.0" encoding="utf-8"?>
<dbms method="info" user="admin" timestamp="28/04/2010 12:00:00" >
      <campionamenti keyname="id" log="0" delete="1" print="1" insert="1" dbms="1" xls="1" 
                  printsel="1" update="1" select="1" father_name="" father_id_name="" father_key_name="" >
            <field size="4" name="id" type="integer" descr="" primary_key="1" notnull="1" />
            <field size="4" name="id_sito" type="integer" descr="Sito prelievo" notnull="1" />
            <field size="4" name="data_prelievo" type="date" descr="Data di prelievo" />
            ......
            <field size="500" name="osservazioni" type="character" descr="Osservazioni" />
            <child field="id" table="parametri" key="id_campione" />
            <relation field="persona_responsabile" table="anagrafiche" />
            <relation field="id_sito" table="siti" />
            ......
            <relation field="cod_campionamenti" table="cod_campionamenti" key="codice" />
      </campionamenti>
</dbms>

• <field/> identifies the characteristics of the attribute,
• <child/> identifies the relationship with the child object (relation n:1),
• <relation/> identifies the relationship with a different object (relation 1:n)

Ensure that <child/> and <relation/> appear in the declaration if the objects are described with their relationships (methods ENTITY_CHILDREN and ENTITY_RELATIONS).

Number of records

The numrec method returns a XML document containing the amount of records presently in the database.

Example:

https://myapp.mydomain.com/dbms/campionamenti?method=numrec

Called method: GET

Parameter	Value	Description
father_key	FFFF	unique key of the father object (mandatory if the object has a father)

Example:

Codice: 200 OK
Content-Type: text/xml; charset=utf-8

<dbms method="numrec" user="admin" timestamp="28/04/2010 12:00:00" >
    <campionamenti numrec="1780" />
</dbms>

Reading

The URL for performing a read operation of an object is constructed in the following way:

https://myapp.mydomain.com/dbms/parametri?method=retrieve&key=NNNN

or

https://myapp.mydomain.com/dbms/parametri?method=retrieve&father_key=NNNN

You can also search for multiple objects with parameter where with a limited SQL query. The example uses the like clause:

https://myapp.mydomain.com/dbms/parametri?method=retrieve&rows=10&where=campione like 'mycode%'

Warning: In the above example the characters such as the space in the URL are converted (the space is converted to 20%); These characters are shown here without the conversion, because some browsers perform a conversion themselves before transmitting the characters to the server.

Called method: GET

Parameter	Value	Description
key	NNNN	unique key of the object to be recovered (is generally a natural number)
father_key	FFFF	unique key of the father
rows	RRR	number of rows to be returned (when not using the key parameter, limited to 65536)
start	SSS	offset of the first row to be read (0 corresponds to the first line)
where	code=XXX	search SQL query limited to the comparison operators =, =, <,>, <=, >=, like, *~ and the operator and

Examples:

https://myapp.mydomain.com/dbms/parametri?key=888
https://myapp.mydomain.com/dbms/parametri?method=retrieve&key=23
https://myapp.mydomain.com/dbms/parametri?method=retrieve&father_key=700
https://myapp.mydomain.com/dbms/parametri?method=retrieve&father_key=700&where=cod_campionamenti='biogas'

The server response will be a XML document containing the requested record (with the key parameter) or the children records of the father subject or the result of the search query.

Example:

Codice: 200 OK
Content-Type: text/xml; charset=utf-8

<?xml version="1.0" encoding="utf-8"?>
<dbms method="retrieve" user="admin" timestamp="28/04/2010 12:00:00" start="0" rows="2" max_rows="1801">
        <parametri parametro="Quantità media dei liquami" risultato="22" id_campione="11111" ord="0" 
                   id_metodo="321" um="mc/h" cod_parametri="Qmedia" incertezza="" tipo="N" metodo="Portata" 
                   parametro_metodo="" id="24335" sinal="0" valore="20" />
        <parametri parametro="Coefficente Rip. Portata" risultato="0.17" id_campione="11111" ord="1"
                   id_metodo="313" um="." cod_parametri="coeff_ripp" incertezza="" tipo="K" metodo="CoeffRPot"
                   parametro_metodo="" id="243334" valore="0.2225" />
</dbms>

In case of an error, the server responds with the error code 400 accompanied by the document containing the error message.
The father's key is obligatory in case the father was specified.
To access a full set of records, larger than the imposed rows limit of 65536, the parameter start should be utilized in order to make multiple queries.
To retrieve the last record in the recordset, use the numrec method to find out the number of records then perform a retrieve method with start = numrec - rows.

Reading with Json encoded response

To perform a read operation of an object with the response in JSON format add the parameter envelope_response = rest.

Example:

https://myapp.mydomain.com/dbms/parametri?method=retrieve&key=999&envelope_response=rest

The server responds with a JSON document containing the requested record:

Codice: 200 OK
Content-Type: application/json; charset=utf-8
Content-Range: items 0-29/30

 [
{"nome":"Admins","commento":"Utenti amministratori","descrizione":"Amministratori","id":1},
...   
]

New key

A new object can be inserted by providing the unique key or without it. In the first case it is possible to find an unique key by calling method GET with the URI of object's class (table) and a newkey method.

Example:

https://myapp.mydomain.com/dbms/campionamenti?method=newkey

Will return the following XML document containing the unique code:

Codice: 200 OK
Content-Type: text/xml; charset=utf-8

    <?xml version="1.0" encoding="utf-8"?>
    <dbms method="newkey" user="admin" timestamp="28/04/2010 12:00:00" >
        <campionamenti key="1136" keyname="id" />
</dbms>

In case of an error, the server responds with an error code 400 accompanied by the document containing the error message.

Creation

The URL for insert operation of a new object is constructed in the following way:

https://myapp.mydomain.com/dbms/campionamenti?method=create&key=NNNN&father_key=FFFF

Called method: POST

Parameter	Value	Description
key	NNNN	unique key of the new object (optional parameter)
father_key	FFFF	unique key of object's father (optional parameter)

Examples:

https://myapp.mydomain.com/dbms/campionamenti?method=create&key=1136
https://myapp.mydomain.com/dbms/parametri?method=create&key=1137&father_key=12
https://myapp.mydomain.com/dbms/parametri?method=create&father_key=120

<dbms>
  <campionamenti stato_rapporto="firmato" persona_prelievo="134" 
   id_tipi_campionamenti="491" osservazioni="" persona_responsabile="241" id_sito="101" />
</dbms>

If the unique key is not provided the server will create it from the newly inserted record.
Unsupplied fields will remain empty or initialized with available default values.

Warning: Its not recommended to create any new records without providing of the unique key because, in case of communication problems, there is the risk of including identical records or losing the key of the newly inserted record.

The server response will be a XML document containing the generated records:

Codice: 200 OK
Content-Type: text/xml; charset=utf-8

      <?xml version="1.0" encoding="utf-8"?>
      <dbms method="create" user="admin" timestamp="28/04/2010 12:00:00" >
           <campionamenti stato_rapporto="firmato" ... id="1136" />
</dbms>

In case of an error, the server responds with an error code 400 accompanied by the document containing the error message.

Modification

The URL for performing a modification operation of an object is constructed in the following way:

https://myapp.mydomain.com/dbms/parametri?method=update&key=NNNN

Called method: POST

Parameter	Value	Description
key	NNNN	unique key of the recovered object (is generally a natural number)

Example:

https://myapp.mydomain.com/dbms/campionamenti?method=update&key=123

<?xml version="1.0" encoding="utf-8"?>
<dbms>
        <campionamenti stato_rapporto="firmato" ... id="123" />
</dbms>

Unsupplied fields will not be modified.

The server response will be a XML document containing the modified record:

Codice: 200 OK
Content-Type: text/xml; charset=utf-8

<?xml version="1.0" encoding="utf-8"?>
<dbms method="update" user="admin" timestamp="28/04/2010 12:00:00" >
        <campionamenti data_ingresso="02/03/2010" codice="AX400/2" id="123"
         osservazioni="Commento\nsu\npiù\nrighe" />
</dbms>

In case of an error, the server responds with the error code 400 accompanied by the document containing the error message.

Cancelation

The URL to perform an erasing operation of an object is constructed in the following way:

https://myapp.mydomain.com/dbms/parametri?method=delete&key=NNNN

Method call: GET

Parameter	Value	Description
key	NNNN	an unique key of the object to be deleted (it is usually a natural number)

Example:

https://myapp.mydomain.com/dbms/parametri?method=delete&key=23

<?xml version="1.0" encoding="utf-8"?>
<dbms method="delete" user="admin" timestamp="28/04/2010 12:00:00" >
        <parametri id="23" />
</dbms>

The server response will be code OK (200) accompanied by the XML document containing the deleted records, or error code 400 accompanied by the document containing the error message.

If the referenced records are non-existent or not accessible by the user, they will not be reported in the XML document.

Xls

The URL to download a file in a XLS format containing selected records is constructed as follows:

https://myapp.mydomain.com/dbms/parametri?method=xls

Method call: GET

Parameter	Value	Description
key	NNNN	an unique key of the recovered object (is generally a natural number)
father_key	FFFF	unique key of the father
rows	RRR	number of rows to return (limited to 65536 when not using the key parameter)
start	SSS	offset position from which to display data (0 corresponds to the first line)
where	code=XXX	search queries are limited to the comparison operators =, =, <, >, <=, >=, like, *~ and the operator and

Error Messages

If an error occurs in the request, if there is violation of the rights or the database response, the error message is sent to the client with an error code 400 Bad Request accompanied by the XML document containing the error message. In case the debug level is enabled the error message also contains the stack-trace of the error.

<error user="administrator" timestamp="28/04/2010 13:10:22.434 CEST" >
   <message>No access to record or record not exists</message>
   <debug row="1">
    Stack: [/opt/masonsql/htdocs/lib/my_library.comp:300], [/usr/local/share/perl ...n.comp:1112]\n
   </debug>
</error>

Here are some of the most significant error messages:

Message	Description
No DBMS permission	The user has no right to access the functionality
No insert permission	The user has no right to introduce new objects
No delete permission	The user has no right to delete objects
No update permission	The user has no right to modify objects
No father retrieve permission	The user has no right to read the father
No related ObjectName retrieve permission	The user has no right to read the linked object ObjectName
No father records retrieve	The user has no right to read the specific parent object
No related ObjectName retrieve records	The user has no right to read the specific linked object ObjectName
No key param	The key parameter is missing in the request
Multiple no key in param key	Multiple keys were provided in the request
No record retrieve or records not exists	The provided object does not exist or user doesn't have sufficient rights to access the object
Delete records return N rows	The object deletion returned a number of objects rather than just one
No father_id param with father 'FATHER'	Missing father_id parameter that relates to the FATHER object
No father_id == -1	The key value -1 is reserved by the framework and if used generates error
No key == -1	The key value -1 is reserved by the framework and if used generates error
Defined father_id param without father definition	father_id parameter was provided in the call to an object without a father
Key Already used	The provided key is already in use (you will still have access to that object)
Key greater last key	The provided key is higher than the last generated key
No content	Empty call to POST method
Malformed xml content: no tag	The submitted document does not contain the XML block: ...

The reported errors related to where field, used by the retrieve operation:

• Unauthorized use of the reserved word '***'
• Unauthorized use of the operator '***'
• Unauthorized use of the field '***'
• Using the barred function '***'
• Syntax error: unknown term '***'
• Syntax error: missing a string delimiter
• Syntax error: missing the closing parenthesis
• Syntax error: closing parenthesis without the corresponding opening bracket

Testing and Debugging

To evaluate the behavior of the interface you can use this html page:

https://myapp.mydomain.com/test/test_crud.html

You can specify the schema, table, the key, the XML document (for update and for insert) and the JSON/XML format (only for retreive) on which to perform operations: info, numrec, newkey, update, insert, and delete. The response returns a record in the underlying box.