SQLite3-powered, not SQLite3-limited

Database agnosticism

Since revision 1.15, our ORM RESTful framework is able to access any available database engine, via a set of generic units and classes.

The framework still relies on SQLite3 as its SQL core on the server, but a dedicated mechanism allows access to any remote database, and mix those tables content with the native ORM tables of the framework.
Thanks to the unique Virtual Tables mechanism of SQLite3, those external tables may be accessed as native SQLite3 tables in our SQL statements.

The current list of available external database classes is:
- Any OleDB provider (including MS SQL Server, Jet or others);
- Any ODBC provider (including FireBird, MySQL, or others);
- Any ZeosLib provider (direct ZDBC access);
- Any DB.pas / TDataset based provider (including NexusDB, DBExpress, AnyDac, UniDAC, BDE...);
- Oracle direct access (via OCI);
- SQLite3 database file.

This list is not closed, and may be completed in the near future. Any help is welcome here: it is not difficult to implement a new unit, following the patterns already existing. You may start from an existing driver (e.g. Zeos or Alcinoe libraries). Open Source contribution are always welcome!

In fact, OleDB is a good candidate for database access with good performance, Unicode native, with a lot of available providers. Thanks to OleDB, we are already able to access to almost any existing database. The code overhead in the server executable will also be much less than with adding any other third-party Delphi library. And we will let Microsoft or the OleDB provider perform all the testing and debugging for each driver.
ODBC is the new standard, since Microsoft is deprecating OleDB.

Since revision 1.18, any ZeosLib / ZDBC driver can be used and DB.pas can be used with our SynDB classes. Of course, using TDataset as intermediate layer will be slower than the SynDB direct access pattern. But it will allow you to re-use any existing (third-party) database connection driver, which could make sense in case of evolution of an existing application, or to use an unsupported database engine.

An Oracle dedicated direct access was added, because all available OleDB providers for Oracle (i.e. both Microsoft's and Oracle's) do have problems with handling BLOB, and we wanted our Clients to have a light-weight and as fast as possible access to this great database.

Thanks to the design of our classes, it was very easy (and convenient) to implement SQLite3 direct access. It is even used for our regression tests, in order to implement stand-alone unitary testing.

ORM Integration

An external record can be defined as such:

type
  TSQLRecordPeopleExt = class(TSQLRecord)
  private
    fData: TSQLRawBlob;
    fFirstName: RawUTF8;
    fLastName: RawUTF8;
    fYearOfBirth: integer;
    fYearOfDeath: word;
    fLastChange: TModTime;
    fCreatedAt: TCreateTime;
  published
    property FirstName: RawUTF8 index 40 read fFirstName write fFirstName;
    property LastName: RawUTF8 index 40 read fLastName write fLastName;
    property Data: TSQLRawBlob read fData write fData;
    property YearOfBirth: integer read fYearOfBirth write fYearOfBirth;
    property YearOfDeath: word read fYearOfDeath write fYearOfDeath;
    property LastChange: TModTime read fLastChange write fLastChange;
    property CreatedAt: TCreateTime read fCreatedAt write fCreatedAt;
  end;

As you can see, there is no difference with an internal ORM class: it inherits from TSQLRecord, but it may want it to inherit from TSQLRecordMany to use many-to-many relationship.

The only difference is this index 40 attribute in the definition of FirstName and LastName published properties: this will define the length (in WideChar) to be used when creating the external field for TEXT column. In fact, SQLite3 does not care about textual field length, but almost all other database engines expect a maximum length to be specified when defining a VARCHAR column in a table. If you do not specify any length in your field definition (i.e. if there is no index ??? attribute), the ORM will create a column with an unlimited length (e.g. varchar(max) for MS SQL Server in this case, code will work, but performance and disk usage may be degraded.

Here is an extract of the regression test corresponding to external databases:

var RInt: TSQLRecordPeople;
    RExt: TSQLRecordPeopleExt;
  (...)
fConnection := TSQLDBSQLite3ConnectionProperties.Create(':memory:','','','');
VirtualTableExternalRegister(fModel,TSQLRecordPeopleExt,fConnection,'PeopleExternal');
fClient := TSQLRestClientDB.Create(fModel,nil,'test.db3',TSQLRestServerDB);
fClient.Server.StaticVirtualTableDirect := StaticVirtualTableDirect;
fClient.Server.CreateMissingTables;
(...)
while RInt.FillOne do begin
  RExt.Data := RInt.Data;
(...)
  aID := fClient.Add(RExt,true);
(...)
  Check(fClient.Retrieve(aID,RExt));
(...)
end;
Check(fClient.Server.CreateSQLMultiIndex(
  TSQLRecordPeopleExt,['FirstName','LastName'],false));
Check(RInt.FillRewind);
while RInt.FillOne do begin
  RExt.FillPrepare(fClient,'FirstName=? and LastName=?',[],
    [RInt.FirstName,RInt.LastName]); // query will use index -> fast 
  while RExt.FillOne do begin
    Check(RExt.FirstName=RInt.FirstName);
(...)
  end;
end;
Now := fClient.ServerTimeStamp;
for i := 1 to aID do
  if i mod 100=0 then begin
    Check(fClient.Retrieve(i,RExt,true),'for update');
    RExt.YearOfBirth := RExt.YearOfDeath;
    Check(fClient.Update(RExt),'Update 1/100 rows');
    Check(fClient.UnLock(RExt));
  end;
for i := 1 to aID do
  if i and 127=0 then
    Check(fClient.Delete(TSQLRecordPeopleExt,i),'Delete 1/128 rows');
for i := 1 to aID do begin
  ok := fClient.Retrieve(i,RExt,false);
  Check(ok=(i and 127<>0),'deletion');
  if ok then begin
    Check(RExt.CreatedAt<=Now);
    if i mod 100=0 then begin
      Check(RExt.YearOfBirth=RExt.YearOfDeath,'Updated');
      Check(RExt.LastChange>=Now);
    end else begin
      Check(RExt.YearOfBirth<>RExt.YearOfDeath,'Not Updated');
      Check(RExt.LastChange<=Now);
    end;
  end;
end;

As you can see, there is no difference with using the local SQLite3 engine or a remote database engine.
From the Client point of view, you just call the usual RESTful methods, i.e. Add / Retrieve / Update / UnLock / Delete, and you can even handle advanced methods like a FillPrepare with a complex WHERE clause, or CreateSQLMultiIndex / CreateMissingTables on the server side. Even the creation of the table in the remote database (the 'CREATE TABLE...' SQL statement) is performed by the framework, with the appropriate column properties according to the database expectations (e.g. a TEXT for SQLite3 will be a NVARCHAR2 field for Oracle).

The only specific instruction is the global VirtualTableExternalRegister function, which has to be run on the server side (it does not make any sense to run it on the client side, since for the client there is no difference between any tables - in short, the client do not care about storage; the server does).

Note that the LastChange field was defined as a TModTime: in fact, the current date and time will be stored each time the record is updated, i.e. for each fClient.Add or fClient.Update calls. This is tested by both RExt.LastChange>=Now and RExt.LastChange<=Now checks in the latest loop. The time used is the "server-time", i.e. the current time and date on the server (not on the client), and, in the case of external databases, the time of the remote server (it will execute e.g. a select getdate() under MS SQL to synchronize the date to be inserted for LastChange). In order to retrieve this server-side time stamp, we use Now := fClient.ServerTimeStamp instead of the local Iso8601Now function.

A similar feature is tested for the CreatedAt published field, which was defined as TCreateTime: it will be set automatically to the current server time at record creation (and not changed on modifications). This is the purpose of the RExt.CreatedAt<=Now check in the above code.

It's worth noting a genuine capability of our ORM architecture:

The Virtual Table feature of SQLite3 will allow those remote tables to be accessed just like "native" SQLite3 tables - in fact, you may be able e.g. to write a valid SQL query with a JOIN between SQlite3 tables, Microsoft SQL Server, MySQL and Oracle databases, even with multiple connections and several remote servers.
Think as an ORM-based Business Intelligence from any database source. Added to our code-based reporting engine (able to generate pdf), it could be a very powerful way of consolidating any kind of data.

For a new project, you define regular TSQLRecord classes and use SQLite3 as your main storage by default, and create TSQLRecordMany external classes if needed (new since revision 1.17); but you could also call VirtualTableExternalRegister, to use a dedicated server for better response time or additional features (like data sharing with other applications or languages).

RTFM

The SAD documents contains also some new diagrams, which will help understand how the Client-Server ORM architecture works.

For instance, a typical Client-Server RESTful POST / Add request over HTTP/1.1 will be implemented as such, on Server side, to handle both "normal" SQLite3 requests and "Virtual Tables" requests:

In fact, the above diagram corresponds to a database model with only external virtual tables, and with StaticVirtualTableDirect=false, i.e. calling the Virtual Table mechanism of SQlite3 for each request.

Most of the time, i.e. for RESTful commands, the execution is more direct: the static TSQLRestServerStaticExternal instance is called for most RESTful access, for an overhead reduced by 50%. In practice, this design will induce no speed penalty, when compared to a direct database access. It could be even faster, if your mORMot / SQLite3 server is located on the same computer than the database: in this case, use of JSON and REST could be faster - even faster when using batch process!

More details available in the documentation!

Feedback and comments are welcome on our forum.

Synopse Open Source