OleDB instead of ZDBC

I just made up my mind about the way to access any database servers.

Instead of using ZDBC (I even started a fork of the Zeos library), which will require a lot of debugging and will provide more features (and code overhead) than needed for our mORMot framework, I think I would rather use OleDB.

In fact, OleDB is a good candidate for database access with good performance, Unicode native, with a lot of available providers.
Of course, you've got the Microsoft SQL Native Client to access SQL server 2005/2008, but Oracle provides a native OleDB provider, even with the Instant Client package. Don't forget about the Advantage Sybase OleDB driver and such... And you've an "OleDB provider for ODBC drivers" around, to access any other kind of data (with some speed penalty).

By using our own OleDB implementation, we will be able to convert directly the OleDB binary rows to JSON, with no temporary conversion into the ZDBC/Delphi high-level types (like temporary string or variant allocations). And I guess the resulting performance will be much greater than using standard TDataSet or other components, since we will bypass most of the layers introduced by BDE/dbExpress/AnyDAC component sets.

It won't implement a whole feature set (don't expect to use this OleDB binding with your VCL DB RAD components), but would handle directly the basic DB column types, as needed by our ORM (derived from SQLite's internal column types): NULL, Int64, Double, RawUTF8 and BLOB.
It will handle parameter bindings, and perhaps some high-performance features like MS SQL Server FastLoad (which will perfectly implement our Batch sequence mechanism).

The code overhead in the server executable will also be much less than with adding ZDBC, dxExpress or any other third-party library.
And we will let Microsoft or the OleDB provider perform all the testing and debugging for each driver.

mORMot Framework

In fact, I think I'll make the following architecture:
  • Database agnostic ORM kernel, just as in the SQLite3Commons current unit, working with JSON over a RESTful connection for the Client;
  • SQlite3 engine included in the Server - we definitively will rely on its unique Virtual Table mechanism;
  • External DB access using optimized OleDB connection with lowest possible process overhead;
  • External DB will be defined via some abstract interfaces: in the future, if OleDB is not enough, any database client could be easily integrated to the framework via faster direct access (e.g. for Oracle or FireBird);
  • Full direct access to external DB, via client-side SQL statement (via a dedicated RESTful URI, using JSON for transmission);
  • ORM access to external DB tables, via dedicated TSQLRecordVirtual classes.

For the ORM mapping of external tables, I'd like to implement at least two new TSQLRecordVirtual classes:

  • A TSQLRecordExternal class, inheriting from TSQLRecordVirtualTableAutoID, which will handle a direct storage of TSQLRecord published field content with all our ORM high-level types (including dynamic arrays or such), just like the features provided with the SQLite3 engine;
  • Two TSQLRecordMappedAutoID and TSQLRecordMappedForcedID class, inheriting from TSQLRecordVirtualTableAutoID or TSQLRecordVirtualTableForcedID classes, which will introduce a column mapping between the ORM content and the database content (see next paragraph).
Here are the main benefits of this architecture:
  • The existing code will mostly remain untouched, since the previous architecture was modular enough to allow multiple database source at once (e.g. SQLite3 and in-memory tables);
  • Since the ORM MVC architecture is preserved, more than one OleDB connection could be opened at once: that is, you may have, from the same RESTful server, access to several databases, even running on several computers, with diverse SQL engines;
  • All those new tables are defined as Virtual Tables from the SQLite3 point of view: so they will be available together with all the other tables of the ORM Data Model - in fact, you could e.g. write a valid SQL query with a join between SQlite3 tables, MS SQL Server 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 for your main storage; but you could also define some TSQLRecordExternal classes, to use a dedicated server for better response time or additional features;
  • If you need to access a lot of existing data from a previous project, the new TSQLRecordMapped* classes will allow mapping of any existing table on any database: that is, you could define the table or columns names corresponding to the TSQLRecord published properties, mapping high-level Delphi types (like an enumeration, a set or even a dynamic array mapping a master/detail tables JOIN) from and into any values at the DB level - some reverse engineering guidance could be introduced at this level;
  • You still have a RESTful access to the OleDB tables via a dedicated URI, so you could even use our framework to have a remote execution of any SQL statement to a separate database (including stored procedures), with the benefits of a REST/JSON-based architecture: no Client software to install (a standalone Delphi exe to deploy with no library/framework) , standard JSON format (it's AJAX ready), easy SOA over HTTP, fast and secure communication (via SynLZ compression and RESTful authentication).

If you have "blank" any about the current architecture, don't hesitate to navigate on all the links on this page, or, even better, take a look at the current framework documentation, mainly the SAD document.

Does all this make sense to you?
Our forum is always available for any feedback or comment.

Article update:

A native layer to Oracle, via direct OCI connection, has been implemented. In fact, OleDB access to Oracle was not very stable, due to bugs in the OleDB providers supplied by Microsoft (which do not handle BLOBs), or Oracle (which has performance and BLOB issues).
Together with a native SQLite3 connection our database-agnostic mORMot is very close to its first release....
The "mORMot" identifier has even been introduced in the source code tree. ;)