The SQlite3 engine has ability to create Virtual Tables from code. From the perspective of an SQL statement, the virtual table object looks like any other table or view. But behind the scenes, queries from and updates to a virtual table invoke callback methods on the virtual table object instead of reading and writing to the database file.
The virtual table mechanism allows an application to publish interfaces that
are accessible from SQL statements as if they were tables. SQL statements can
in general do anything to a virtual table that they can do to a real table,
with the following exceptions:
- One cannot create a trigger on a virtual table.
- One cannot create additional indices on a virtual table. (Virtual tables can
have indices but that must be built into the virtual table implementation.
Indices cannot be added separately using CREATE INDEX
statements.)
- One cannot run ALTER TABLE ... ADD COLUMN commands against a
virtual table.
- Particular virtual table implementations might impose additional constraints.
For example, some virtual implementations might provide read-only tables. Or
some virtual table implementations might allow INSERT or
DELETE but not UPDATE. Or some virtual table
implementations might limit the kinds of UPDATEs that can be
made.
Example of virtual tables, already included in the SQLite3 engine,
are FTS or
RTREE tables. A
custom virtual table might represent in-memory data structures (like
TSQLVirtualTableJSON, TSQLVirtualTableBinary). Or it might
represent a view of data on disk that is not in the SQLite3 format
(e.g. TSQLVirtualTableLog). Or the application might compute the
content of the virtual table on demand.
Thanks to the generic implementation of Virtual Table in SQLite3,
you can use such tables in your SQL statement, and even safely execute a
SELECT statement with JOIN or custom functions,
mixing normal SQLite3 tables and any other Virtual Table.
A dedicated mechanism has been added to the framework, beginning with revision 1.13, in order to easily add such virtual tables with pure Delphi code, just by inheriting some classes.
