[Pas2js] New feature: SQLDB Rest bridge

[Michael Van Canneyt]

Hello,

We received some feedback about Pas2JS - our effort to bring Pascal to the
browser - that easy database connectivity is an issue, hampering easy development.

To alleviate the problem I have created the SQLDB Rest bridge.

This is a set of components that allow you to expose any database supported
by FPC's SQLDB as a REST service. It is designed to be simple to use, yet extensible.

A database can be exposed using a single line of code:
  FDisp.ExposeDatabase('PostGreSQL','localhost','expensetracker','me','secret',Nil,[foFilter,foInInsert,foInUpdate,foOrderByDesc]);

But no code is needed, the whole can be configured in the IDE if so desired.

This is not meant as a general REST service framework, (you can look for
instance at the Brook framework for this) but is meant to easily expose
SQL databases using a REST protocol.

Not-so-Short summary of possibilities:

SQL/REST:

* Every REST resource is defined by up to 4 SQL statements, corresponding to the 4 CRUD operations.
   But statements can be auto-generated.

* The CRUD Statements can be auto generated on the fly, based on a table name and field definitions.

* For every field, an alias can be provided.

* The following client-side types are available, automatically mapped from the native database type:

  String, 32/64-bit integer, float, Boolean, Date, Time, DateTime, Blob

* Support for a sequence to generate IDs is built-in.

* Full control over which operations (GET,POST,PUT,DELETE) are allowed.

* Resources are collected in a schema.

* Multiple schemas can be attached to the service.

* Multiple databases (connections) can be defined.

* A schema can be tied to a single connection, or connections can share a schemas (use case: a different connection per client).

* Business processor components can be hooked to a resource, to make it easier to implement business logic. (not yet committed)

* SQL Statements can contain parameters, values for the parameters will be picked up from the request URL.

* Support to get custom datasets.

* Support for client-side provided SQL SELECT statements (optional, disabled by default)

* Full support for configuration through an <code>.ini</code> file out of the box.


HTTP:
* Authentication is handled using the HTTP protocol.

* Basic authentication is included by default, but is completely pluggable.

* Basic authentication can look up valid users in a database (by default the database being exposed)

* Output format can be fixed or detected per request (?fmt=format). Detection based on content-type is also available.

* list of fields to include in output can be specified in the URL: ?fl=field1,field2

* Field list of fields to exclude from output can be specified in the URL: ?fe=field1,field2

* Various input/output formats are available out of the box

   JSON (the default),XML,CSV, CDS (Format used by Delphi Datasnap)

* A factory pattern is used, new formats can be added at will.

* Simple URL schemes. 2 basic schemes are available:
    BASEURL/Resource[/ID]
    BASEURL/Connection/Resource[/ID]

* Support for introspection/discovery or metadata.

* limit,offset URL parameters for paging of results: ?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.

* 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)

* Filtering can be specified on a field basis in the request URL, and the filter operation is translated to SQL Where:
    ?MyField=10
    ?MyField_null=1 ?MyField_null=0
    ?MyField_lt=10 ?MyField_lte=10
    ?MyField_gt=10 ?MyField_gte=10

* The ability to filter on a field can be specified in the schema for each field.

* The request can specify whether metadata should be included in the response: ?metadata=1

* The request can specify whether the result should be human-readable (nicely formatted) or not: ?humanreadable=1

A more complete explanation is in the WIKI

http://wiki.freepascal.org/SQLDBRestBridge

2 demo programs exist, more are planned (there is even a Delphi demo showing
how to read the data with Delphi's TClientDataset)

Lazarus integration is also available, the components are compilable with FPC 3.0.4.

This is an initial version (fully functional), several extensions are planned:
- Zero-config API
   (a single generic binary can be shipped, no config files should be needed)
- ADO packages
- Integrate in lazarus Database desktop for easy development
- Integrate in pas2JS compile server for easy development.

Remarks, comments, suggestions welcome. 
(to a lesser extent: bugreports ;) )

Michael.