Example of code integration

The programmer has to define its database table properties as Delphi classes, descendant from the TSQLRecord class. All published properties of these classes are then stored and retrieved from the SQLite3 database engine, by the framework. For most common usage, the coder doesn't have to write SQL queries: they are all created on the fly by the Object-relational mapping (ORM) framework.

For example, a People Table is defined in Delphi code as followed:

/// table used for the Babies queries
TSQLPeople = class(TSQLRecord)
private
fName: RawUTF8;
fAddress: RawUTF8;
fBirthDate: TDateTime;
published
property Name: RawUTF8 read fName write fName;
property Address: RawUTF8 read fAddress write fAddress;
property BirthDate: TDateTime read fBirthDate write fBirthDate;
end;

By adding this TSQLPeople class to a TSQLModel instance, common for both Client and Server, the corresponding People table is created by the Framework in the SQLite3 database engine. All SQL work ('CREATE...') is done by the framework. Just code in Pascal, and all is done for you. You won't miss any ' or ; in your SQL query any more.

To access a particular record, the following code can be used:

var People: TSQLPeople;
ID: integer;
begin
// create a new record, since Smith, Jr was just born
People := TSQLPeople.Create;
try
People.Name := 'Smith';
People.Address := 'New York City';
People.BirthDate := Now;
ID := Client.Add(People);
finally
People.Free;
end;
// retrieve record data
People := TSQLPeople.Create(Client,ID);
try
assert(People.Name='Smith');
finally
People.Free;
end;
end;

This framework also handles directly the creation of Ribbon-like interfaces, with full data view and navigation as visual Grids. The whole User Interface is designed in code, by some constant definitions. See below the RTTI usage paragraph.

Since the framework is truly object oriented, another database engine could be used instead of the SQLite3 framework. You could easily write your own TSQLRestServer descendent (as an example, we included a fast in-memory database engine) and link to a another engine (like FireBird, or a private one). You can use our framework without any link to the SQLite3 engine itself, by using our provided very fast in memory dataset (which can be made persistent by writing and reading JSON files on disk). The SQLite3 engine is implemented in a separate unit, SQLite3.pas, and the main unit of the framework is SQLite3Commons.pas.

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.

Synopse LVCL Framework

In order to optimize the size and the memory usage of the Server program, another freeware component package can be used, which is the LCVL Framework (together with its rewritten system units), which leads into faster and smaller generated code. This framework is intended to work directly with the Synopse SQLite3 Database Framework, for safe use.

Overall RTTI usage

The Delphi language (aka Object Pascal) provided Runtime Type Information (RTTI) more than a decade ago. In short, Runtime Type Information is information about an object's data type that is set into memory at run-time. The RTTI support in Delphi has been added first and foremost to allow the design-time environment to do its job, but developers can also take advantage of it to achieve certain code simplifications. The Synopse SQLite3 Database Framework makes huge use of RTTI, from the database level to the User Interface. Therefore, the resulting program has the advantages of very fast development (Rails-like), but with the robustness of strong typing, and the speed of one of the best compiler available.

In short, it allows the software logic to be extracted from the code itself. Here are the places where this technology was used:

- All database structures are set in the code by normal classes definition, and most of the needed SQL code is created on the fly by the framework, before calling the SQLite3 database engine, resulting in a true Object-relational mapping (ORM) framework;

- All User Interface is generated by the code, by using some simple data structures, relying on enumerations (see next paragraph);

- Most of the text displayed on the screen does rely on RTTI, thanks to the Camel approach (see below), ready to be translated into local languages;

- All internal Event process (such as Button press) relies on enumerations RTTI;

- Options and program parameters are using RTTI for data persistency and screen display (e.g. the Settings window of your program can be created by pure code): adding an option is a matter of a few code lines.

In Delphi, enumeration types or Enum provides a way of to define a list of values. The values have no inherent meaning, and their ordinality follows the sequence in which the identifiers are listed. These values are written once in the code, then used everywhere in the program, even for User Interface generation.

For example, some toolbar actions can be defined with:

/// Worklist toolbar actions
TPeopleAction = (
paCreateNew, paDelete, paEdit, paQuit);
Then this TPeopleAction enumerate type is used to create the User Interface ribbon of the Worklist window, just by creating an array of set of this kind: 
BarEdit: array[0..1] of set of TPeopleAction = (
[paCreateNew, paDelete, paEdit],
[paQuit] );

The caption of the buttons to be displayed on the screen is then extracted by the framework using "Camel Case": the second button, defined by the paCreateNew identifier in the source code, is displayed as "Create new" on the screen, and this "Create new" is used for direct i18n of the software. For further information about "Camel Case" and its usage in Object Pascal, Java, Dot Net, Python see CamelCase.

Advantages of the RTTI can therefore by sum up:

- Software maintenability, since the whole program logic is code-based, and the User Interface is created from it. It therefore avoid RAD (Rapid Application Development) abuse, which mix the User Interface with data logic, and could lead into "write fast, try to maintain" scenarios;

- Enhanced code security, thanks to Object Pascal strong typing;

- Direct database access from the language object model, without the need of writing SQL or use of a MVC framework;

- User Interface coherency, since most screen are created on the fly;

- Easy i18n of the software, without additional components or systems.