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
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
DB.pas can be used with our
SynDB classes. Of
TDataset as intermediate layer will be slower than
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
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.
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
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
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
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,
CreateSQLMultiIndex / CreateMissingTables on the server side.
Even the creation of the table in the remote database (the
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.Update calls. This is tested by both
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
getdate() under MS SQL to synchronize the date to be inserted for
LastChange). In order to retrieve this server-side time stamp, we
Now := fClient.ServerTimeStamp instead of the local
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
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
SQlite3 tables, Microsoft SQL Server, MySQL and
Oracle databases, even with multiple connections and several remote
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
to use a dedicated server for better response time or additional features (like
data sharing with other applications or languages).
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
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
More details available in the documentation!