Using SQLite

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 TABLE command. This function creates a new instance ...