Difference between revisions of "SQLDBRestBridge"

From Lazarus wiki
(Using a datamodule)
(TODO)
Line 351: Line 351:
  
 
(under construction)
 
(under construction)
 +
 +
== Examples ==
 +
 +
=== FPC ===
 +
* in FPC Packages, the fcl-web/examples/restbridge directory, there is a small example that serves a expenses tracker database.
 +
* The directory (delphiclient) below contains a small Delphi project that shows how to connect a TClientDataset to the SQLDB Rest bridge.
 +
 +
=== Lazarus ===
 +
 +
* The demo/restbridge directory of Lazarus/Components contains the same example program, using a datamodule.
 +
 +
More examples will follow, in particular a pas2js client application for the expenses tracker;.
  
 
== TODO ==
 
== TODO ==
  
 
The following extensions are still planned:
 
The following extensions are still planned:
<ul>
+
 
<li>ADO XML data packets</li>
+
* ADO XML data packets
<li>FPC's TBUFDataset XML data packets.</li>
+
* FPC's TBUFDataset XML data packets.
<li>Support for <code>?q=filterexpression</code> in URL filters.</li>
+
* Support for <code>?q=filterexpression</code> in URL filters.
<li>Connection management API. (zero-config service)</li>
+
* Connection management API. (zero-config service)
<li><p>Use HTTP credentials to connect to the database.</p></li></ol>
+
* Use HTTP credentials to connect to the database.
</ul>
 

Revision as of 17:08, 24 February 2019

Purpose

The SQLDBRestBridge unit offers a means to expose any database that can be accessed by SQLDB using a REST API.

It is not meant as a general REST API framework, for this other frameworks exist. It is also not a RPC framework.

The design is such that a complete database can be exposed using a single line of code, with reasonable default.

Features

SQL Database side.

  1. Every REST resource is defined by up to 4 SQL statements, corresponding to the 4 CRUD operations.
  2. The CRUD Statements can be auto generated on the fly, based on a table name and field definitions.
  3. For every field, an alias can be provided.
  4. The following client-side types are available, automatically mapped from the native database type:
    1. String
    2. 64-bit integer
    3. 32-bit integer
    4. Boolean
    5. Date, Time, DateTime
    6. Blob (base64-encoded)
  5. Support for a sequence to generate IDs is built-in.
  6. Full control over which operations (GET,POST,PUT,DELETE) are allowed.
  7. Resources are collected in a schema.
  8. Multiple schemas can be attached to the service.
  9. Multiple databases (connections) can be defined.
  10. A schema can be tied to a single connection, or connections can share a schemas (use case: a different connection per client).
  11. Business processor components can be hooked to a resource, to make it easier to implement business logic.
  12. SQL Statements can contain parameters, values for the parameters will be picked up from the request URL.
  13. Support to get custom datasets.
  14. Support for client-side provided SQL SELECT statements (optional, disabled by default)
  15. Full support for configuration through an .ini file out of the box.

HTTP side

  1. Authentication is handled using the HTTP protocol.
  2. Basic authentication is included by default, but is completely pluggable.
  3. Basic authentication can look up valid users in a database (by default the database being exposed)
  4. Output format can be fixed or detected per request (?fmt=format). Detection based on content-type is also available.
  5. list of fields to include in output can be specified in the URL:
    fl=field1,field2
  6. Field list of fields to exclude from output can be specified in the URL:
    fe=field1,field2
  7. Various output formats are available out of the box
    1. JSON (the default)
    2. XML (a custom format)
    3. CSV (for in and output)
    4. CDS (Format used by Delphi TClientDataset)
    5. Planned: ADO data packets (as used by MS Access)
  8. A factory pattern is used, new formats can be added at will.
  9. Simple URL schemes. 2 basic schemes are available

    BASEURL/Resource/
    BASEURL/Resource/ID

    or using the connection as prefix:

    BASEURL/Connection/Resource/
    BASEURL/Connection/Resource/ID
  10. Support for introspection/discovery or metadata:

    BASEURL/metadata/

    returns the list of available resources and their operation.

    BASEURL/metadata/resourcename
    returns the structure of the resource: fields, types, etc.
  11. limit and offset parameters for paging of results:

    BASEURL/resourcename?limit=10
    BASEURL/resourcename?limit=10&Offset=50
    A maximum limit can be enforced. When the SQL statements support it, the limit and offset are translated to SQL fetch/offset clauses.
  12. Sort order can be specified in the request URL, if the resource definition allows it, using ?sort=fieldname. The ability to sort can be specified on a field basis.

    BASEURL/resourcename?sort=MyField
    BASEURL/resourcename?sort=MyField%20desc
  13. Filtering can be specified on a field basis in the request URL, and the filter operation is translated to SQL Where:

    BASEURL/resourcename?MyField=10
    BASEURL/resourcename?MyField_null=1
    BASEURL/resourcename?MyField_null=0
    BASEURL/resourcename?MyField_lt=10
    BASEURL/resourcename?MyField_lte=10
    BASEURL/resourcename?MyField_gt=10
    BASEURL/resourcename?MyField_gte=10

    is translated to a SQL where clause:

    (MyField=10)
    (MyField is null)
    (MyField is not null)
    (MyField<10)
    (MyField_lte<=10)
    (MyField>10)
    (MyField>=10)
    The ability to filter on a field can be specified in the schema for each field.
  14. The request can specify whether metadata should be included in the response:

    BASEURL/resourcename?metadata=1
  15. The request can specify whether the result should be human-readable or not

    BASEURL/resourcename?humanreadable=1

    If set to 1, the request result will be formatted. Not all formats support this.

Available Components

TSQLDBRestSchema

Represents a REST schema : a list of available resources, with their allowed operations, fields definitions, SQL statements.

Properties

  • Resources A collection of resources. Each item is of type TSQLDBRestResource
  • ConnectionName Set this to a connection name if you want all resources of this schema to use this connection.

Methods

  • SaveToFile save the resource definitions to file (as JSON)
Procedure SaveToFile(Const aFileName : UTF8String);
  • SaveToFile save the resource definitions to stream (as JSON).
Procedure SaveToStream(Const aStream : TStream);
  • AsJSON return the resource definitions as a JSON object.

function AsJSON(const aPropName: UTF8String=''): TJSONData;

If specified, aPropName is the name of the property below which all definitions are stored.

  • LoadFromFile Load the resource definitions from a JSON file.
Procedure LoadFromFile(Const aFileName : UTF8String);
  • LoadFromStream Load the resource definitions from a JSON file.
Procedure LoadFromStream(Const aStream : TStream);
  • FromJSON Load the resource definitions from a JSON file.
Procedure FromJSON(aData: TJSONData;const aPropName: UTF8String='');

If specified, aPropName is the name of the property below which all definitions are located.

  • PopulateResourceFields create resource fields based on table name.
procedure PopulateResourceFields(aConn: TSQLConnection; aRes: TSQLDBRestResource; aMinFieldOpts : TRestFieldOptions = []);

PopulateResourceFields populates the field definitions in aRes by checking the table in database connection aConn. Every created field gets at least aMinFieldOptions in its Options property.

  • PopulateResources creates a resource definition for each table in a database connection.
procedure PopulateResources(aConn: TSQLConnection; aTables: array of string; aMinFieldOpts: TRestFieldOptions= []);
Procedure PopulateResources(aConn : TSQLConnection; aTables : TStrings = Nil; aMinFieldOpts : TRestFieldOptions = []);
  • PopulateResources retrieves the list of tables from aConn, creates a resource definition for the table and calls PopulateResourceFields to fill the resource with fields. If aTables is specified, definitions will only be created for the tables in that list.

TSQLDBRestDispatcher

Properties

  • Active (Boolean) Register or unregister HTTP routes.
  • Connections (TSQLDBRestConnectionList) List of database connections to connect to.
  • Schemas (TSQLDBRestSchemaList) List of REST schemas to serve
  • BasePath (UTF8String) Base URL for REST URLS.
  • DefaultConnection (UTF8String) Default connection to use if none is detected from request/schema
  • Strings (TRestStringsConfig Property) Input/Output strings configurations
  • OutputOptions (TRestOutputOptions) default Output options, modifiable by query. One or more of
  1. ooMetadata Send metadata in request
  2. ooSparse Do not send null values (if format allows it)
  3. ooHumanReadable Pretty-print output.
  • InputFormat (string) Name of Input format to use for all requests.
  • OutputFormat (string) Name of Output format to use for all requests.
  • DispatchOptions (TRestDispatcherOptions) Dispatcher options. One or more of
  1. rdoConnectionInURL Use the connection as a path element in the URL.
  2. rdoExposeMetadata Expose the metadata URL.
  3. rdoCustomView allow the use of a customview URL (Select SQL specified in request)
  4. rdoHandleCORS Handle CORS preflight request.
  • Authenticator (TRestAuthenticator) Authenticator for requests. If OnBasicAuthentication is set, this can be left empty.
  • EnforceLimit (Integer) If larger than zero, Enforce a limit on output results.
  • CORSAllowedOrigins (String) Comma separated list of Domains that are allowed to use this REST service. If empty, * will be returned in preflight request.

Methods

  • ExposeDatabase exposes a database as a REST service. This will creates a connection definition with the provided parameters and calls ExposeConnection with this definition.

    Function ExposeDatabase(Const aType,aHostName,aDatabaseName,aUserName,aPassword : String; aTables : Array of String; aMinFieldOp
    Function ExposeDatabase(Const aType,aHostName,aDatabaseName,aUserName,aPassword : String; aTables : TStrings = nil; aMinFieldOpt
    
    The connection gets the name ConnectionN, where N is a unique number, and the schema is named SchemaConnectionN. The Tables and aMinFieldOpt arguments are the same as in the PopulateResources call of TSQLDBRestSchema
  • ExposeConnection exposes a database (represented by aConnection) as a REST service. This will create a schema with a resource definition.

    Function ExposeConnection(Const aConnection : TSQLDBRestConnection; aTables : TStrings = nil; aMinFieldOpts : TRestFieldOptions
    

    The Tables and aMinFieldOpt arguments are the same as in the PopulateResources call of TSQLDBRestSchema

TRestBasicAuthenticator

TSQLDBRestModule

  • This is a simple TWebModule descendant which is usable in Lazarus IDE. It has a single Dispatcher property which must be set to a TSQLDBRestDispatcher instance.

Available Output formats

JSON (the default)

  • A simple JSON object encapsulating data, metadata and possibly error nodes.
  • rows are exported as JSON objects, field names are property names of the object.
  • Name: 'json'
  • Content-type: 'application/json'
  • Input and output.
  • Data by default under 'data'
  • Metadata by default under 'metaData'
  • Errors reported under 'error'
  • Property names are configurable in REST dispatcher.

XML (a custom format)

  • A simple XML document encapsulating data, metadata and possibly error nodes, under a root element 'datapacket'
  • rows are exported as XML elements (named 'row') , the contents as text in the element.
  • Name: 'xml'
  • Content-type: 'text/xml'
  • Input and output.
  • Data by default under 'data'
  • Metadata by default under 'metadata'
  • Errors reported under 'error'
  • Element names are configurable in REST dispatcher.

CSV

  • Simple comma-separated CSV list.
  • Name: 'csv'
  • Content-type: 'text/csv'
  • Input and output.
  • Separator: Comma
  • Quotes when needed.
  • Metadata means first line contains fieldnames.

CDS

  • XML package using the XMLFormat used by Delphi TClientDataset.
  • Name: 'cds'
  • Content-type: 'text/csv'
  • Input and output.

A Delphi demo program is provided.

Planned: ADO

  • XML format as used by ADO recordsets (as used by MS Access)
  • Name: 'ado'
  • Input and output.

Ini file Support

The following classes can load/save their configuration settings from/to an .ini file

  • TSQLDBRestDispatcher
  • TRestBasicAuthenticator
  • TSQLDBRestConnection

The support for this is implemented in 2 units;

  • the sqldbauthini unit has a type helper for the TRestBasicAuthenticator class.
  • The sqldbrestini unit has the type helpers for TSQLDBRestDispatcher and TSQLDBRestConnection

The helpers implement LoadFromFile/SaveToFile methods, as well as LoadFromIni/SaveToIni methods. There are some options to control the level of depth.

The schemas (if so required) are saved as JSON files in the same directory as the .ini file, using the name of the schema as the filename.

Lazarus Support

Lazarus support for the SQLDB REST Bridge is in the lazsqldbrest.lpk packages, under the fpweb directory. It registers the TSQLDBRestDispatcher, TSQLDBRestSchema and TRestBasicAuthenticator components on the component palette:

sqldbrestcomponents.png

Additionally it registers some component- and property editors:

Further integration with Lazarus is planned: 1. Add a nicer schema editor 1. Let the IDE act as a REST server. (alternatively, add a tool with a menu entry under tools for easy configuration)

Pas2JS Support

The SQLDB Rest bridge will be integrated in the compile server that comes with pas2JS: This means that pas2js will come with a toll that

  • Can recompile your web project on the fly
  • Can serve files on disc
  • Can act as a REST server.

So no actual server is needed during development.

Usage

In a fcl-web application.

The rest bridge was designed to be simple to use.

Expose a single database, no authentication.

Simple expose of a database. Run the following code in the startup of your program (for example, DoRun of the web application class, or startup code of the program)

FDisp:=TSQLDBRestDispatcher.Create(Self);
FDisp.ExposeDatabase(
    'postgres','localhost','expensetracker','me','secret'
    ,Nil,
    [foFilter,foInInsert,foInUpdate,foOrderByDesc]);
FDisp.Active:=True;   
FDisp.SaveToFile('demo.ini');

This exposes all tables of the database 'expenstracker', allows to filter,update and sort on all fields. The connection will be named 'Connection1', the schema 'SchemaConnection1'

The configuration will be saved in a file called 'demo.ini'.
It can be reused to quickly set up a REST dispatcher.

Expose single database, HTTP Basic authentication.

Similar to the previous example, but with HTTP BASIC authentication for a single user:

FAuth:=TRestBasicAuthenticator.Create(Self);
FAuth.DefaultUserName:='me';
FAuth.DefaultPassword:='secret';
FDisp:=TSQLDBRestDispatcher.Create(Self);
FDisp.Authenticator:=Fauth;
FDisp.ExposeDatabase(
    'postgres','localhost','expensetracker','me','secret'
    ,Nil,
    [foFilter]);
FDisp.Active:=True;

This exposes all tables of the database 'expenstracker', allows to filter,update and sort on all fields. The connection will be named 'Connection1', the schema 'SchemaConnection1'. No updates are possible: the fields will not be in update/insert statements. Sorting is also not allowed.

Expose single database, HTTP Basic authentication (2)

Smilar to the previous example, but with HTTP BASIC authentication, using the database to authenticate a user:

FAuth:=TRestBasicAuthenticator.Create(Self);
FAuth.AuthenticateUserSQL.Text:='select uID from users where (uLogin=:UserName) and (uPassword=:Password)';
FDisp:=TSQLDBRestDispatcher.Create(Self);
FDisp.Authenticator:=Fauth;
FDisp.ExposeDatabase(
    'postgres','localhost','expensetracker','me','secret'
    ,['projects','expenses'],
    []);
FDisp.Active:=True;

Here only the 'projects' and 'expenses' tables are exposed, read-only.

Using a Config file and schema files.

Start a dispatcher, loading the file demo.ini. The dioSkipReadSchemas tells it not to load schema files, the schemas will be created from the connections.

FDisp:=TSQLDBRestDispatcher.Create(Self);
FDisp.LoadFromFile('demo.ini',[dioSkipReadSchemas]);
FDisp.Active:=True;

The .ini file can be created using SaveToFile on a configured dispatcher instance.

In the lazarus IDE.

Using a datamodule

In this approach, a datamodule is created that is in memory throughout the whole lifetime of the application (fcl-web application)


  1. Create a HTTP server application or FCGI application. (a CGI will also work but is very inefficient)
  2. Add a plain datamodule to the application.
  3. Drop a TSQLDBRestSchema class from the 'FCL-Web' tab of the component palette
  4. Edit the schema (property resources), or import it from file using the component editor context menu
  5. Drop a TSQLDBRestDispatcher class from the 'FCL-Web' tab of the component palette
  6. Edit the connections
  7. Add an item to the Schemas property and point it to the TSQLDBRestSchema added previously
  8. For HTTP Basic authentication, add a TRESTBAsicAuthenticator class, and set the TSQLDBRestDispatcher instance's Authentocator property to it.

The end result should look more or less like this:

restmodule.png

That's it. The project is ready to run.

Using a SQLDBRestdatamodule

(under construction)

Examples

FPC

  • in FPC Packages, the fcl-web/examples/restbridge directory, there is a small example that serves a expenses tracker database.
  • The directory (delphiclient) below contains a small Delphi project that shows how to connect a TClientDataset to the SQLDB Rest bridge.

Lazarus

  • The demo/restbridge directory of Lazarus/Components contains the same example program, using a datamodule.

More examples will follow, in particular a pas2js client application for the expenses tracker;.

TODO

The following extensions are still planned:

  • ADO XML data packets
  • FPC's TBUFDataset XML data packets.
  • Support for ?q=filterexpression in URL filters.
  • Connection management API. (zero-config service)
  • Use HTTP credentials to connect to the database.