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;
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 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!