SQLite3-powered, not SQLite3-limited
By A.Bouchez on 2011, Sunday August 7, 09:46 - mORMot Framework - Permalink
Our downloadable documentation has been enhanced, and contains now a description about the main feature of the near-to-come 1.15 version, i.e. "database agnosticism".
The core database of our mORMot / SQLite3 framework uses the SQLite3 library, which is a Free, Secure, Zero-Configuration, Server-less, Single Stable Cross-Platform Database File database engine.
As stated below, you can use any other database access layer, if you wish. A fast in-memory engine is included, and can be used instead or together with the SQLite3 engine. Since revision 1.15 of the framework you may be able to access any remote database, and use one or more OleDB (or Oracle) connections to store your precious ORM objects. The SQlite3 will be used as the main SQL engine, able to JOIN all those tables, thanks to its Virtual Table unique feature.
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;
- Oracle direct access (via OCI);
- A SQLite3 database file.
This list is not closed, and may be completed in the near future. Any help is welcome here: it's 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.
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 access to this great database.
Thanks to the design of our classes, it was very easy (and convenient) to implement SQLite3 direct access. It's 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(TSQLRecordExternal)
private
fData: TSQLRawBlob;
fFirstName: RawUTF8;
fLastName: RawUTF8;
fYearOfBirth: integer;
fYearOfDeath: word;
fLastChange: TModTime;
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;
end;
All you have to do is to inherit from TSQLRecordExternal
instead of TSQLRecord.
Note also the index 40 attribute in the definition of
FirstName and LastName published properties: this
will define the length (in WideChars) 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 don't 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 Microsoft SQL Server) - in this case,
code will work, but performance and disk usage may be degraded.
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):
VirtualTableExternalRegister(fModel,TSQLRecordPeopleExt,fConnection,'PeopleExternal');
Note that the LastChange field was defined as a
TModTime: in fact, this is a new field kind introduced with
version 1.15, by which the current date and time will be stored automatically
each time the record is updated, i.e. for each fClient.Add or
fClient.Update calls. Could be very handy to synchronize multiple
databases, or complex requests. 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 use e.g. a
select getdate() under MS SQL to synchronize the date to be inserted
for the LastChange field).
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 should better use regular TSQLRecord
classes and use SQLite3 as your main storage; but you could also
define some TSQLRecordExternal classes, 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!