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.