Why a new Framework?

ab · 2010-06-21 16:15:35

The Synopse SQLite3 database Framework interfaces the SQlite3 database engine into pure Delphi code: database access, User Interface generation, security, i18n and reporting are handled in a safe and fast Client/Server AJAX/RESTful model.

SQLite3-based
The Database storage of this framework uses the SQLite3 library, which is a Free, Secure, Zero-Configuration, ServerLess, Single Stable Cross-Platform Database File database engine. As stated below, you can use any other database access layer than the SQLite3 library, if you wish. A fast in-memory engine is included.
The Synopse SQLite3 database framework is a freeware set of classes, released with full source code, which interfaces the SQLite3 library with native Delphi code, and acts as a true Object-relational mapping framework.

Framework presentation
This framework can be defined as:
- Flexible - in process, local or remote access;
- REST and MVC - built on the great Model-View-Controller and Representational state transfer architectural patterns;
- Standard - full UTF-8 and Unicode, SQLite3 engine (enhanced but not hacked);
- Precise - individual records can be locked for update;
- Secure - tested, multi-thread oriented, atomic commit, encryption ready;
- Simple - statically linked into a single Delphi unit (no external dll nor framework required);
- Light - use native classes, not TDataSet nor TDataSource;
- Smart - queries share a JSON-based memory cache for immediate response;
- Fast - tuned object pascal and i386 assembler code with direct use of FastMM4;
- Open - can use any database engine as storage (not only SQLite3)
- Easy - tables are defined from classes properties, most queries made with no SQL written;
- AJAX - use of JSON format for all data transfers makes it 100% AJAX ready - server can be accessed without any Delphi client;
- Free - full source code provided, with permissive license.

Framework description
From the technical point of view, this framework uses a compiled version of the official SQLite3 library source code, and includes it natively into Delphi code. In such an integration, some points can be highlighted:
- The framework is unitary tested with provided regression tests;
- It uses purely UTF-8 encoded strings: fast Ansi/Unicode conversion routines are included, and is also Delphi 2009 ready (but Unicode works very well with older Delphi versions, whatever the marketing says);
- This framework allows optional on the fly fast encryption of the data on disk;
- Coder has to inherit the TSQLRecord class to define Table structure once in some published properties and easily interact with the server (alla Ruby on Rails): you don't need to know about the SQL language itself - just play with objects;
- TSQLDatabase can cache the last results for SELECT statements, in order to speed up most read queries, for lighter web server or client UI e.g.;
- Uses ISO 8601:2004 format to properly handle date/time values in TEXT field, or in a faster and smaller to store integer equivalent;
- Client / Server oriented, with optimized request caching and intelligent update (SQLite3 doesn't cache any query just disk accesses);
- Since JSON format is used internally, and HTTP is available as transport layer, this framework is AJAX and Web 2.0 ready;
- User authentication ready (SQLite3 is user-free designed);
- Direct User Interface generation: grids are created on the fly, together with a modern Ribbon ('Office 2007'-like) screen layout - the code just has to define actions, and assign them to the tables, in order to construct the whole interface from a few lines of code, without any IDE usage;
- Included full-featured Reporting feature;
- Direct Grid search lookup by pressing keys: handle unicode and case insensitive search, and also soundex (in English, French or Spanish) search;
- Full UTF-8 and i18n (i.e. internationalization) handled from the ground up;
- Most common SQL command consists in a record retrieval from its ID: this particular query is natively speed up via SQL statement preparation and pre-compilation.

SQLite3-enhanced
This framework therefore adds some very useful capabilities to the Standard SQLite3 database engine, but keeping all its advantages, as listed in the previous paragraph of this document:
- Faster database access, through unified memory model, and usage of the FastMM4 memory manager (which is almost 10 times faster than the default Windows memory manager for memory allocation);
- Optional direct encryption of the data on the disk (up to AES-256 level, that is Top-Secret security);
- Database layout is declared once in the Delphi source code (as published properties of classes), avoiding common field or table names mismatch;
- Locking of the database at the record level (SQLite3 only handles file-level locking);
- SQLite3 library unit was compiled including RTREE extension for doing very fast range queries;
- It can optionaly include FTS3 full text search engine (MATCH operator) after sqlite3.c recompile (by default, FTS3 is not compiled, saving more than 50KB of code);
- The framework makes use only of newest API (sqlite3_prepare_v2) and follows SQLite3 official documentation;
- SQLite3 source code was compiled without thread mutex: the caller has to be thread-safe aware; this is faster on most configurations, since mutex has to be acquired once): low level sqlite3_*() functions are not thread-safe, asTSQLRequest and TSQLBlobStream which just wrap them; but TSQLDataBase is thread-safe, asTSQLTableDB/TSQLRestServerDB/TSQLRestClientDB which call TSQLDataBase;
- Compiled with SQLITE_OMIT_SHARED_CACHE define, since with the new Client/Server approach of this framework, no concurrent access could happen, and an internal efficient caching algorithm is added, avoiding most call of the SQLite3 engine in multi-user environnment (any AJAX usage should benefit of it);
- The embedded SQLite3 database engine can be easily updated from the official SQLite3 source code available at http://sqlite.org.

ab · 2010-06-21 16:18:54

Notice about JSON
As we just stated, the JSON format is used internaly in this framework. By definition, the JavaScript Object Notation (JSON) is a standard, open and lightweight computer data interchange format.

Usage if this layout, instead of other like XML or any proprietary format, results in several particularities:
- Like XML, it's a text-based, human-readable format for representing simple data structures and associative arrays (called objects);
- It's easier to read (for both human beings and machines), quicker to implement, and much smaller in size than XML;
- It's a very efficient format for data caching;
- Its layout allows to be rewritten in place into individual zero-terminated UTF-8 strings, with almost no wasted space: this feature is used for blazzling fast JSON to text conversion of the tables results, with no memory allocation nor data copy;
- It's natively supported by the JavaScript language, making it a perfect serialization format in any AJAX (i.e. Web 2.0) application;
- The JSON format is specified in this RFC
- The default text encoding for both JSON and SQLite3 is UTF-8, which allows the full Unicode charset to be stored and communicated;
- It is the default data format used by ASP.NET AJAX services created in Windows Communication Foundation (WCF) since .NET framework 3.5; so it's Microsoft officialy "ready";
- For binary blob transmission, we simply encode the binary data as hexadecimal using the SQLite3 BLOB literals format: hexadecimal data preceded by a single "x" or "X" character (for example: X'53514C697465').

Notice about REST
Representational state transfer (REST) is a style of software architecture for distributed hypermedia systems such as the World Wide Web. As such, it is not just a method for building "web services". The terms "representational state transfer" and "REST" were introduced in 2000 in the doctoral dissertation of Roy Fielding, one of the principal authors of the Hypertext Transfer Protocol (HTTP) specification, on which the whole Internet rely.
The Synopse SQLite3 database Framework was designed in accordance with Fielding's REST architectural style without using HTTP and without interacting with the World Wide Web. Such Systems which follow REST principles are often referred to as "RESTful". Optionaly, the Framework is able to serve standard HTTP/1.1 pages over the Internet (by using the SQLite3Http unit and the TSQLite3HttpServer and TSQLite3HttpClient classes), in an embedded low resource and fast HTTP server.

The standard RESTful methods are implemented:
- GET to list the members of the collection;
- PUT to update a member of the collection;
- POST to create a new entry in the collection;
- DELETE to delete a member of the collection.

The following methods were added to the standard REST definition, for locking individual records and for handling database transactions (which speed up database process):
- LOCK to lock a member of the collection;
- UNLOCK to unlock a member of the collection;
- BEGIN to initiate a transaction;
- END to commit a transaction;
- ABORT to rollback a transaction.

ab · 2010-07-02 07:26:52

In the blog, there was some details about the RESTful implementation of the framework:
http://blog.synopse.info/post/2010/02/0 … ersion-1.4

The HTTP Client/Server sample application can be used to show how the framework is AJAX-ready, and can be proudly compared to a REST server (like CouchDB):
- Start the Project04Server.exe program: the background HTTP server, together with its SQLite3 database engine;
- Start any Project04Client.exe instances, and add/find any entry, to populate the database a little;
- Close the Project04Client.exe programs, if you want;
- Open your browser, and type into the address bar:
http://localhost:8080/root
- You'll see an error message:
TSQLite3HttpServer Server Error 400
- Type into the address bar:
http://localhost:8080/root/SampleRecord
- You'll see the result of all SampleRecord IDs, encoded as a JSON list, e.g.

[{"ID":1},{"ID":2},{"ID":3},{"ID":4}]

- Type into the address bar:
http://localhost:8080/root/SampleRecord/1
- You'll see the content of the SampleRecord of ID=1, encoded as JSON, e.g.

{"ID":1,"Time":"2010-02-08T11:07:09","Name":"AB","Question":"To be or not to be"}

- Type into the address bar any other REST command, and the database will reply to your request...

You've got a full HTTP/SQLite3 RESTful JSON server within less than 400KB !!

ab · 2010-07-02 07:30:40

Note that most common RESTful JSON used a more verbose format for the JSON content: see for example http://bitworking.org/news/restful_json which proposed to put whole URL in the JSON content

[
  "http://example.org/coll/1",
  "http://example.org/coll/2",
  "http://example.org/coll/3",
  ...
  "http://example.org/coll/N",
]

I really prefer our implementation

[{"ID":1},{"ID":2},{"ID":3},{"ID":4}]

which preserves bandwidth and human readibility: if you were able to send a GET request to the URL http://example.org/coll you will be able to append this URL at the beginning of every future request, doesn't it make sense?
In all cases, our framework always return the JSON content just as a pure response of a SQL query, with an array and field names.

Note that our JSON content has two layouts, which can be produced according to the TSQLRestServer.NoAJAXJSON property:

1. the "expanded" or standard/AJAX layout, which allows you to create pure JavaScript objects from the JSON content, because the field name / JavaScript object property name is supplied for every value:

[{"ID":0,"Int":0,"Test":"abcde+¬ef+á+¬","Unicode":"abcde+¬ef+á+¬","Ansi":"abcde+¬ef+á+¬","ValFloat":3.14159265300000E+0000,"ValWord":1203,"ValDate":"2009-03-10T21:19:36","Next":0},{..}]

2. the "not expanded" layout, which reflects exactly the layout of the SQL request: first line are the field names, then all next lines are the field content:

{"FieldCount":9,"Values":["ID","Int","Test","Unicode","Ansi","ValFloat","ValWord","ValDate","Next",0,0,"abcde+¬ef+á+¬","abcde+¬ef+á+¬","abcde+¬ef+á+¬",3.14159265300000E+0000,1203,"2009-03-10T21:19:36",0,..]}

By default, the NoAJAXJSON property is set to TRUE in TSQLRestServer.ExportServerNamedPipe: if you use named pipes for communication, you probably won't use javascript because browser communicates via HTTP!

But otherwise, NoAJAXJSON property is set to FALSE. You could force its value to TRUE if you'd save some bandwidth and don't use javascript: even the parsing of the JSON Content will be faster with Delphi if JSON content is not expanded.

In this "not expanded" layout, the first JSON content of this post will be transfered as:

{"FieldCount":1,"Values":["ID",1,2,3,4,5]}

longge007 · 2010-07-02 09:05:31

i have one questiong,the RestfulJson Servser only support qureyes according ID,how to get some DATA according name or time.or Multi-conditions.
if i want to get a lot of records according Time,how to realize it.
thanks a lot.

Last edited by longge007 (2010-07-02 09:24:49)

ab · 2010-07-02 10:03:29

See the TSQLRestServer.URI() method implementation in SQlite3Commons.pas unit.

You can add some parameters to an URL like ModelRoot/TableName to create a SQL request:

SELECT someselect FROM tableName [WHERE wherestatement] [ORDER BY sortstatement] [LIMIT resultstatement] [OFFSET startindexstament] [DESC]

with the URL parameters:
- select=someselect
- where=wherestatement
- sort=sortstatement
- startindex=startindexstament
- results=resultstatement
- dir=DESC will add the DESC statement

If no select is set, i.e. for a " ModelRoot/TableName " URL with no parameter, it will use 'ID' as someselect value, i.e. all IDs of this table are returned to the client.

See the comment in the method description:

    // - for 'GET ModelRoot/TableName', url parameters can be either "select" and
// "where" (to specify a SQL Query, from the SQLFromSelectWhere function),
// either "sort", "dir", "startIndex", "results", as expected by the YUI
// DataSource Request Syntax for data pagination - see
// @http://developer.yahoo.com/yui/datatable/#data

and visit http://developer.yahoo.com/yui/datatable/#data

All this URL encoding of parameters is handled by the client part of the framework automaticaly, from Delphi methods like Retrieve()...

esmondb · 2011-09-18 22:31:06

If I'm using TSQLRestServer.URI() with the yui datatable syntax to get some paginated data how do I get the total records in the set?

Also could it be worth using the odata (www.odata.org) syntax instead of yui's - it seems more thoroughly thought out.

Many thanks.

ab · 2011-09-19 07:30:16

esmondb wrote:

If I'm using TSQLRestServer.URI() with the yui datatable syntax to get some paginated data how do I get the total records in the set?

You can either paginate without knowing the whole row count (click on next page until it returns no more data), either use COUNT() in the SQL query.

But I understand this is not a perfect fit.

The current implementation just relies on SQLite3 for the paging. This is simple and efficient.
But in order to implement completely the server-side paging, the code should be modified in order to keep the whole JSON list in memory, then do the paging using dedicated Delphi methods.

But I suspect that for most use, client-side paging should be enough. The JSON data is produced very quickly, and with Deflate enabled on the server side, bandwidth use is minimal. This would be an issue only with huge amount of data (more than 50,000 rows, I guess).

esmondb wrote:

Also could it be worth using the odata (www.odata.org) syntax instead of yui's - it seems more thoroughly thought out.

This standard is interesting, but sounds also a bit complex.
For my understanding of http://www.odata.org/developers/protocols/json-format , it maps the complexity of XML-based layout into JSON. I don't want to use a AtomPub-like protocol within JSON. It's far away from the KISS principle.
For instance, as far as I understood it, binary content is Base-64 encoded. Which just sounds not the best way in a RESTful protocol: we can use Base-64, but we mostly rely on specific URI for handling BLOB data.
Another issue is the layout of the produced JSON data. Why on earth do they add this "d": everywhere? This is for security reasons, but for outdated reasons. No one will ever evaluate JSON content directly in an eval() Javascript function nowadays. This is just one example of unneeded complexity.
My guess is that OData is far from efficient for handling JSON data, but is a good choice if you want to map your XML data into JSON (as Microsoft does for its WCF format).

esmondb · 2011-09-19 10:11:19

As I need the row count for the UI I'll go with a separate SQL query.

Was hoping that OData might turn out to be a KISS version of SOAP but from what you say above that seems unlikely.

Phisatho · 2011-10-23 19:02:01

When I try to connect to the http server on localhost through fire fox (http://localhost:8080/root/SampleRecord/1), I get a blank screen.
When I try through IE, I get the error: You are not authorized to view this page

ab · 2011-10-24 07:30:41

1. Is the server running?

2. Is authentication used?

If authentication is used, it won't respond from a browser. The server expect each URL to be signed with a user session.
See the documentation about authentication - or this http://blog.synopse.info/post/2011/05/2 … entication
So if you disable the authentication, you'll be able to see the JSON Content.

Phisatho · 2011-10-24 17:40:00

I can't find where the authentication is defined. All I can find is
Server := TSQLite3HttpServer.Create('8080',[DB], 'localhost');

ab · 2011-10-25 12:23:41

Phisatho wrote:

I can't find where the authentication is defined. All I can find is
Server := TSQLite3HttpServer.Create('8080',[DB], 'localhost');

But you should better use '+' instead of 'localhost'.

The authentication in this server instance is set when creating the DB instance:
- The TSQLRestServerDB.Create(aModel: TSQLModel; aDB: TSQLDataBase; aHandleUserAuthentication: boolean) instance is where you set the authentication - aHandleUserAuthentication parameter should be left to false;
- Is the DB Model.Root value really equals to "root"? This is the value expected by your URL.

Phisatho · 2011-10-25 19:19:41

It is working now.Thanks.

ab · 2012-05-25 06:32:14

Take a look at this post in the forum.

It sounds like if, in the JSON format, when you retrieve a number starting with a 0, it is not a valid JSON number, unless the next character is '.' or a plain zero.
That is, '0', '0.123' are valid numbers, and '0123' is not.
And JSON does not allow '+' to indicate positive numbers (only '-' sign).
See http://json.org/
I did not know this, and find it to be weird.
Sounds like a C-like-behavior inherited into JavaScript (for octal/hexadecimal notation)...

I've modified the ORM source code to reflect this unexpected JSON definition, about '0' and '+'.
See http://synopse.info/fossil/info/7f82fd67bd

mORMot Open Source

#1 2010-06-21 16:15:35

Why a new Framework?

#2 2010-06-21 16:18:54

Re: Why a new Framework?

#3 2010-07-02 07:26:52

Re: Why a new Framework?

#4 2010-07-02 07:30:40

Re: Why a new Framework?

#5 2010-07-02 09:05:31

Re: Why a new Framework?

#6 2010-07-02 10:03:29

Re: Why a new Framework?

#7 2011-09-18 22:31:06

Re: Why a new Framework?

#8 2011-09-19 07:30:16

Re: Why a new Framework?

#9 2011-09-19 10:11:19

Re: Why a new Framework?

#10 2011-10-23 19:02:01

Re: Why a new Framework?

#11 2011-10-24 07:30:41

Re: Why a new Framework?

#12 2011-10-24 17:40:00

Re: Why a new Framework?

#13 2011-10-25 12:23:41

Re: Why a new Framework?

#14 2011-10-25 19:19:41

Re: Why a new Framework?

#15 2012-05-25 06:32:14

Re: Why a new Framework?

Board footer