Simple Example: dblist Module
The first example takes the output of the PRAGMA database_list
command and presents it as a table. Since the output from the PRAGMA command is already in the same
structure as a table, this conversion is fairly simple. The main reason for
doing this is to use the full SELECT
syntax, including WHERE conditions,
against the virtual table. This is not possible with the PRAGMA command.
The PRAGMA
database_list command normally returns three columns:
seq, name, and file. The
seq column is a sequence value that
indicates which “slot” the database is attached to. The name column is the logical name of the
database, such as main or temp, or whatever name was given to the
ATTACH DATABASE command. (See ATTACH DATABASE in Appendix C).
The file column displays the full path to
the database file, if such a file exists. In-memory databases, for example,
do not have any associated filenames.
To keep things simple, the module uses the seq value as our virtual ROWID value. The seq value is an integer value and is unique across all of
the active databases, so it serves this purpose quite well.
Create and Connect
The first set of functions we’ll be looking at are used to create or connect a virtual table instance. The functions you need to provide are:
-
int xCreate( sqlite3 *db, void *udp, int argc, char **argv, sqlite3_vtab **vtab, char **errMsg ) Required. This function is called by SQLite in response to a
CREATE VIRTUAL TABLEcommand. This function creates a new instance ...