Virtual Tables in the SQLite3 framework

Virtual Table module classes

In order to implement a new Virtual Table type, you'll have to define a so called Module to handle the fields and data access and an associated Cursor for the SELECT statements. This is implemented by the two TSQLVirtualTable and TSQLVirtualTableCursor classes as defined in the SQLite3Commons.pas unit.

For instance, here are the default Virtual Table classes deriving from those classes:

TSQLVirtualTableJSON, TSQLVirtualTableBinary and TSQLVirtualTableCursorJSON classes will implement a Virtual Table using a TSQLRestServerStaticInMemory instance to handle fast in-memory static databases. Disk storage will be encoded either as UTF-8 JSON (for the TSQLVirtualTableJSON class, i.e. the 'JSON' module), either in a proprietary SynLZ compressed format (for the TSQLVirtualTableBinary class, i.e. the 'Binary' module). File extension on disk will be simply .json for the 'JSON' module, and .data for the 'Binary' module. Just to mention the size on disk difference, the 502 KB People.json content (as created by included regression tests) is stored into a 92 KB People.data file, in our proprietary optimized format.

Note that the virtual table module name is retrieved from the class name. For instance, the TSQLVirtualTableJSON class will have its module named as 'JSON' in the SQL code.

As you probably have already stated, all those Virtual Table mechanism is implemented in SQLite3Commons. Therefore, it is independent from the SQLite3 engine, even if, to my knowledge, there is no SQL database engine around which is implementing this pretty nice feature.

Defining a Virtual Table module

Here is how the TSQLVirtualTableLog class type is defined, which will implement a Virtual Table module named "Log". Adding a new module is just made by overriding some Delphi methods:

  TSQLVirtualTableLog = class(TSQLVirtualTable)
  protected
    fLogFile: TSynLogFile;
  public
    class procedure GetTableModuleProperties(
      var aProperties: TVirtualTableModuleProperties); override;
    constructor Create(aModule: TSQLVirtualTableModule; const aTableName: RawUTF8;
      FieldCount: integer; Fields: PPUTF8CharArray); override;
    destructor Destroy; override;
  end;

This module will allow direct Read-Only access to a .log file content, which file name will be specified by the corresponding SQL table name.

The following method will define the properties of this Virtual Table Module:

class procedure TSQLVirtualTableLog.GetTableModuleProperties(
  var aProperties: TVirtualTableModuleProperties);
begin
  aProperties.Features := [vtWhereIDPrepared];
  aProperties.CursorClass := TSQLVirtualTableCursorLog;
  aProperties.RecordClass := TSQLRecordLogFile;
end;

The supplied feature set defines a read-only module (since vtWrite is not selected), and vtWhereIDPrepared indicates that any RowID=? SQL statement will be handled as such in the cursor class (we will use the log row as ID number, start counting at 1, so we can speed up RowID=? WHERE clause easily). The associated cursor class is returned. And a TSQLRecord class is specified, to define the handled fields - its published properties definition will be used by the inherited Structure method to specify to the SQLite3 engine which kind of fields are expected in the SQL statements:

  TSQLRecordLogFile = class(TSQLRecordVirtualTableAutoID)
  protected
    fContent: RawUTF8;
    fDateTime: TDateTime;
    fLevel: TSynLogInfo;
  published
    /// the log event time stamp
    property DateTime: TDateTime read fDateTime;
    /// the log event level
    property Level: TSynLogInfo read fLevel;
    /// the textual message associated to the log event
    property Content: RawUTF8 read fContent;
  end;

You could have overridden the Structure method in order to provide the CREATE TABLE SQL statement expected. But using Delphi class RTTI allows the construction of this SQL statement with the appropriate column type and collation, common to what the rest of the ORM will expect.

Of course, this RecordClass property is not mandatory. For instance, the TSQLVirtualTableJSON.GetTableModuleProperties method won't return any associated TSQLRecordClass, since it will depend on the table it is implementing, i.e. the running TSQLRestServerStaticInMemory instance. Instead, the Structure method is overridden, and will return the corresponding field layout of each associated table.

Here is how the Prepare method is implemented, and will handle the vtWhereIDPrepared feature:

function TSQLVirtualTable.Prepare(var Prepared: TSQLVirtualTablePrepared): boolean;
begin
  result := Self<>nil;
  if result then
    if (vtWhereIDPrepared in fModule.Features) and
       Prepared.IsWhereIDEquals(true) then
    with Prepared.Where[0] do begin // check ID=?
      Value.VType := varAny; // mark TSQLVirtualTableCursorJSON expects it
      OmitCheck := true;
      Prepared.EstimatedCost := 1;
    end else
      Prepared.EstimatedCost := 1E10; // generic high cost
end;

Then here is how each 'log' virtual table module instance is created:

constructor TSQLVirtualTableLog.Create(aModule: TSQLVirtualTableModule;
  const aTableName: RawUTF8; FieldCount: integer; Fields: PPUTF8CharArray);
var aFileName: TFileName;
begin
  inherited;
  if (FieldCount=1) then
    aFileName := UTF8ToString(Fields[0]) else
    aFileName := aModule.FileName(aTableName);
  fLogFile := TSynLogFile.Create(aFileName);
end;

It only associates a TSynLogFile instance according to the supplied file name (our SQL CREATE VIRTUAL TABLE statement only expects one parameter, which is the .log file name on disk - if this file name is not specified, it will use the SQL table name instead).

The TSQLVirtualTableLog.Destroy destructor will free this fLogFile instance:

destructor TSQLVirtualTableLog.Destroy;
begin
  FreeAndNil(fLogFile);
  inherited;
end;

Then the corresponding cursor is defined as such:

  TSQLVirtualTableCursorLog = class(TSQLVirtualTableCursorIndex)
  public
    function Search(const Prepared: TSQLVirtualTablePrepared): boolean; override;
    function Column(aColumn: integer; var aResult: TVarData): boolean; override;
  end;

Since this class inherits from TSQLVirtualTableCursorIndex, it will have the generic fCurrent / fMax protected fields, and will have the HasData, Next and Search methods using those properties to handle navigation throughout the cursor.

The overridden Search method consists only in:

function TSQLVirtualTableCursorLog.Search(
  const Prepared: TSQLVirtualTablePrepared): boolean;
begin
  result := inherited Search(Prepared); // mark EOF by default
  if result then begin
    fMax := TSQLVirtualTableLog(Table).fLogFile.Count-1;
    if Prepared.IsWhereIDEquals(false) then begin
      fCurrent := Prepared.Where[0].Value.VInt64-1; // ID=? -> index := ID-1
      if cardinal(fCurrent)<=cardinal(fMax) then
        fMax := fCurrent else // found one
        fMax := fCurrent-1;   // out of range ID
    end;
  end;
end;

The overridden Search method consists only in:

function TSQLVirtualTableCursorLog.Search(
  const Prepared: TSQLVirtualTablePrepared): boolean;
begin
  result := inherited Search(Prepared); // mark EOF by default
  if result then begin
    fMax := TSQLVirtualTableLog(Table).fLogFile.Count-1;
    if Prepared.IsWhereIDEquals(false) then begin
      fCurrent := Prepared.Where[0].Value.VInt64-1; // ID=? -> index := ID-1
      if cardinal(fCurrent)<=cardinal(fMax) then
        fMax := fCurrent else // found one
        fMax := fCurrent-1;   // out of range ID
    end;
  end;
end;

The only purpose of this method is to handle RowID=? statement SELECT WHERE clause, returning fCurrent=fMax=ID-1 for any valid ID, or fMax / fCurrent, i.e. no result if the ID is out of range. In fact, the Search method of the cursor class must handle all cases which has been notified as handled during the call to the Prepare method. In our case, since we have set the vtWhereIDPrepared feature and the Prepare method identified it in the request and set the OmitCheck flag, our Search method MUST handle the RowID=? case.

If the WHERE clause is not RowID=? (i.e. if Prepared.IsWhereIDEquals returns false), it will return fCurrent=0 and fMax=fLogFile.Count-1, i.e. it will let the SQLite3 engine loop through all rows searching for the data.

Each column value is retrieved by this method:

function TSQLVirtualTableCursorLog.Column(aColumn: integer;
  var aResult: TVarData): boolean;
var LogFile: TSynLogFile;
begin
  result := false;
  if (self=nil) or (fCurrent>fMax) then
    exit;
  LogFile := TSQLVirtualTableLog(Table).fLogFile;
  if LogFile=nil then
    exit;
  case aColumn of
   -1: SetColumn(aResult,fCurrent+1); // ID = index + 1
    0: SetColumn(aResult,LogFile.EventDateTime(fCurrent));
    1: SetColumn(aResult,ord(LogFile.EventLevel[fCurrent]));
    2: SetColumn(aResult,LogFile.LinePointers[fCurrent],LogFile.LineSize(fCurrent));
    else exit;
  end;
  result := true;
end;

As stated by the documentation of the TSQLVirtualTableCursor class, -1 is the column index for the RowID, and then will follow the columns as defined in the text returned by the Structure method (in our case, the DateTime, Level, Content fields of TSQLRecordLogFile).

The SetColumn overloaded methods can be used to set the appropriate result to the aResult variable. For UTF-8 text, it will use a temporary in-memory space, to ensure that the text memory will be still available at least until the next Column method call.

Using a Virtual Table module

From the low-level SQLite3 point of view, here is how this "Log" virtual table module can be used, directly from the SQLite3 engine.

First we will register this module to a DB connection (this method is to be used only in case of such low-level access - in our ORM you should never call this method, but TSQLModel. VirtualTableRegister instead, cf. next paragraph):

 Demo.RegisterVirtualTableModule(TSQLVirtualTableLog);

Then we can execute the following SQL statement to create the virtual table for the Demo database connection:

 Demo.Execute('CREATE VIRTUAL TABLE test USING log(temptest.log);');

This will create the virtual table. Since all fields are already known by the TSQLVirtualTableLog class, it's not necessary to specify the fields at this level. We only specify the log file name, which will be retrieved by TSQLVirtualTableLog. Create constructor.

 Demo.Execute('select count(*) from test',Res);
 Check(Res=1);
 s := Demo.ExecuteJSON('select * from test');
 s2 := Demo.ExecuteJSON('select * from test where rowid=1');
 s3 := Demo.ExecuteJSON('select * from test where level=3');

You can note that there is no difference with a normal SQLite3 table, from the SQL point of view. In fact, the full power of the SQL language as implemented by SQLite3 - see http://sqlite.org/lang.html - can be used with any kind of data, if you define the appropriate methods of a corresponding Virtual Table module.

Virtual Table, ORM and TSQLRecord

The framework ORM is able to use Virtual Table modules, just by defining some TSQLRecord, inheriting from some TSQLRecordVirtual dedicated classes:

TSQLRecordVirtualTableAutoID children can be defined for Virtual Table implemented in Delphi, with a new ID generated automatically at INSERT.

TSQLRecordVirtualTableForcedID children can be defined for Virtual Table implemented in Delphi, with an ID value forced at INSERT (in a similar manner than for TSQLRecordRTree or TSQLRecordFTS3/4).

TSQLRecordLogFile was defined to map the column name as retrieved by the TSQLVirtualTableLog ('log') module, and should not to be used for any other purpose.

The Virtual Table module associated from such classes is retrieved from an association made to the server TSQLModel. In a Client-Server application, the association is not needed (nor to be used, since it may increase code size) on the Client side. But on the server side, the TSQLModel. VirtualTableRegister method must be called to associate a TSQLVirtualTableClass (i.e. a Virtual Table module implementation) to a TSQLRecordVirtualClass (i.e. its ORM representation).

For instance, the following code will register two TSQLRecord classes, the first using the 'JSON' virtual table module, the second using the 'Binary' module:

  Model.VirtualTableRegister(TSQLRecordDali1,TSQLVirtualTableJSON);
  Model.VirtualTableRegister(TSQLRecordDali2,TSQLVirtualTableBinary);

This registration should be done on the Server side only, before calling TSQLRestServer.Create (or TSQLRestClientDB.Create, for a stand-alone application). Otherwise, an exception is raised at virtual table creation.

In-Memory "static" process

We have seen that the TSQLVirtualTableJSON, TSQLVirtualTableBinary and TSQLVirtualTableCursorJSON classes implement a Virtual Table module using a TSQLRestServerStaticInMemory instance to handle fast static in-memory database.

Why use such a database type, when you can create a SQLite3 in-memory table, using the :memory: file name? That is the question...
- SQlite3 in-memory tables are not persistent, whereas our JSON or Binary virtual table modules can be written on disk on purpose, if the aServer.StaticVirtualTable[aClass].CommitShouldNotUpdateFile property is set to true - in this case, file writing should be made by calling explicitely the aServer.StaticVirtualTable[aClass].UpdateToFile method;
- SQlite3 in-memory tables will need two database connections, or call to the ATTACH DATABASE SQL statement - both of them are not handled natively by our Client-Server framework;
- SQlite3 in-memory tables are only accessed via SQL statements, whereas TSQLRestServerStaticInMemory tables can have faster direct access for most common RESTful commands (GET / POST / PUT / DELETE individual rows) - this could make a difference in server CPU load, especially with the Batch feature of the framework;
- On the server side, it could be very convenient to have a direct list of in-memory TSQLRecord instances to work with in pure Delphi code; this is exactly what TSQLRestServerStaticInMemory allows, and definitively makes sense for an ORM framework;
- On the client or server side, you could create calculated fields easily with TSQLRestServerStaticInMemory dedicated "getter" methods written in Delphi, whereas SQlite3 in-memory tables would need additional SQL coding;
- SQLite3 tables are stored in the main database file - in some cases, it could be much convenient to provide some additional table content in some separated database file (for a round robin table, a configuration table written in JSON, some content to be shared among users...): this is made possible using our JSON or Binary virtual table modules (but, to be honest, the ATTACH DATABASE statement could provide a similar feature);
- The TSQLRestServerStaticInMemory class can be used stand-alone, i.e. without the SQLite3 engine so it could be used to produce small efficient server software - see the "SQLite3- In Memory ORM" folder.

In-Memory tables

A first way of using static tables, independently from the SQLite3 engine, is to call the TSQLRestServer. StaticDataCreate method.

This method is only to be called server-side, of course. For the Client, there is no difference between a regular and a static table.

The in-memory TSQLRestServerStaticInMemory instance handling the storage can be accessed later via the StaticDataServer[] property array of TSQLRestServer.

As we just stated, this primitive but efficient database engine can be used without need of the SQLite3 database engine to be linked to the executable, saving some KB of code if necessary. It will be enough to handle most basic RESTful requests.

In-Memory virtual tables

A more advanced and powerful way of using static tables is to define some classes inheriting from TSQLRecordVirtualTableAutoID, and associate them with some TSQLVirtualTable classes. The TSQLRecordVirtualTableAutoID parent class will specify that associated virtual table modules will behave like normal SQLite3 tables, so will have their RowID property computed at INSERT).

For instance, the supplied regression tests define such two tables with three columns, named FirstName, YearOfBirth and YearOfDeath, after the published properties definition:

  TSQLRecordDali1 = class(TSQLRecordVirtualTableAutoID)
  private
    fYearOfBirth: integer;
    fFirstName: RawUTF8;
    fYearOfDeath: word;
  published
    property FirstName: RawUTF8 read fFirstName write fFirstName;
    property YearOfBirth: integer read fYearOfBirth write fYearOfBirth;
    property YearOfDeath: word read fYearOfDeath write fYearOfDeath;
  end;
  TSQLRecordDali2 = class(TSQLRecordDali1);

Both class types are then added to the TSQLModel instance of the application, common to both Client and Server side:

  ModelC := TSQLModel.Create(
    [TSQLRecordPeople,  (...)
     TSQLRecordDali1,TSQLRecordDali2],'root');

Then, on the Server side, the corresponding Virtual Table modules are associated with those both classes:

  ModelC.VirtualTableRegister(TSQLRecordDali1,TSQLVirtualTableJSON);
  ModelC.VirtualTableRegister(TSQLRecordDali2,TSQLVirtualTableBinary);

Thanks to the VirtualTableRegister calls, on the server side, the 'JSON' and 'Binary' Virtual Table modules will be launched automatically when the SQLite3 DB connection will be initialized:

  Client := TSQLRestClientDB.Create(ModelC,nil,Demo,TSQLRestServerTest);

This TSQLRestClientDB has in fact a TSQLRestServerDB instance running, which will be used for all Database access, including Virtual Table process.

Two files will be created on disk, named 'Dali1.json' and 'Dali2.data'. As stated above, the JSON version will be much bigger, but also more easy to handle from outside the application.

From the code point of view, there is no difference in our ORM with handling those virtual tables, compared to regular TSQLRecord tables. For instance, here is some code extracted from the supplied regression tests:

  if aClient.TransactionBegin(TSQLRecordDali1) then
  try
    // add some items to the file
    V2.FillPrepare(aClient,'LastName=:("Dali"):');
    n := 0;
    while V2.FillOne do begin
      VD.FirstName := V2.FirstName;
      VD.YearOfBirth := V2.YearOfBirth;
      VD.YearOfDeath := V2.YearOfDeath;
      inc(n);
      Check(aClient.Add(VD,true)=n,Msg);
    end;
    // update some items in the file
    for i := 1 to n do begin
      Check(aClient.Retrieve(i,VD),Msg);
      Check(VD.ID=i);
      Check(IdemPChar(pointer(VD.FirstName),'SALVADOR'));
      Check(VD.YearOfBirth=1904);
      Check(VD.YearOfDeath=1989);
      VD.YearOfBirth := VD.YearOfBirth+i;
      VD.YearOfDeath := VD.YearOfDeath+i;
      Check(aClient.Update(VD),Msg);
    end;
    // check SQL requests
    for i := 1 to n do begin
      Check(aClient.Retrieve(i,VD),Msg);
      Check(VD.YearOfBirth=1904+i);
      Check(VD.YearOfDeath=1989+i);
    end;
    Check(aClient.TableRowCount(TSQLRecordDali1)=1001);
    aClient.Commit;
  except
    aClient.RollBack;
  end;

A Commit is needed from the Client side to write anything on disk. From the Server side, in order to create disk content, you'll have to explicitly call such code on purpose:

As we already noticed, data will be written by default on disk with our TSQLRestServerStaticInMemory-based virtual tables. In fact, the Commit method in the above code will call TSQLRestServerStaticInMemory.UpdateFile.

Please note that the SQlite3 engine will handle any Virtual Table just like regular SQLite3 tables, concerning the atomicity of the data. That is, if no explicit transaction is defined (via TransactionBegin / Commit methods), such a transaction will be performed for every database modification (INSERT / UPDATE / DELETE). The TSQLRestServerStaticInMemory. UpdateToFile method is not immediate, because it will write all table data each time on disk. It's therefore mandatory, for performance reasons, to nest multiple modification to a Virtual Table with such a transaction, for better performance. And in all cases, it's the standard way of using the ORM. If for some reason, you later change your mind and e.g. move your table from the TSQLVirtualTableJSON / TSQLVirtualTableBinary engine to the default SQlite3 engine, your code could remain untouched.

It's possible to force the In-Memory virtual table data to stay in memory, and the COMMIT statement to write nothing on disk, using the following property:

 Server.StaticVirtualTable[TSQLRecordDali1].CommitShouldNotUpdateFile := true;

In order to create disk content, you'll then have to explicitly call the corresponding method on purpose:

 Server.StaticVirtualTable[TSQLRecordDali1].UpdateToFile;

Since StaticVirtualTable property is only available on the Server side, you are the one to blame if your client updates the table data and this update never reachs the disk!

Want more?

The documentation has been enhanced a lot those past weeks. A chapter is dedicated to Virtual Tables (see the SAD document, pages 59 to 71).
In fact, this blog article is an extract from this documentation.

By the way, I think our Open Source SQLite3.pas unit is the only one implementing Virtual Tables in Delphi. Even the SQLite3 libraries you pay for (no name, please!) don't include this very distinctive SQLite3 feature at the Delphi level: the low-level C API is available, but you'll have to manage memory and registration by hand, with no associated documentation. Let me be proud for one minute (or two).

Feedback and questions are welcome in our forum, as usual!

Synopse