*** 1,4 ****
! ***Proposed Virtual Table Mechanism For SQLite***
A virtual table is an object that is registered with
an open SQLite database connection. From the perspective
--- 1,4 ----
! ***Virtual Tables In SQLite***
A virtual table is an object that is registered with
an open SQLite database connection. From the perspective
***************
*** 11,227 ****
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, though particular virtual table implementations
! might restrict what actions are allowed on a case by case basis.
A virtual table might represent an in-memory data
structures. Or it might represent a view of data on disk
that is not in the SQLite format. Or the application
might compute the content of the virtual table on demand.
! Two data structures are used by the virtual table
! implementation:
! typedef struct sqlite3_vtab_cursor sqlite3_vtab_cursor;
! typedef struct sqlite3_vtab sqlite3_vtab;
! The sqlite3_vtab_cursor structure is an opaque type that
! represents a pointer into a virtual table. The
! sqlite3_vtab structure is the method table for the virtual
! table object. A read-only instance of this structure defines
! the format of the virtual table and the callback methods that
! are invoked when SQL is executed against the virtual table.
! The sqlite3_vtab structure looks like this:
! struct sqlite3_vtab {
! /* The first group of fields define the format of the virtual table
! */
! int nCol; /* Number of columns in the virtual table */
! const char **azColName; /* Name of each column */
! const char **azDeclType; /* Declared data type for each column */
! int intPrimKey; /* The INTEGER PRIMARY KEY column. -1 for none */
! int nIdx; /* Number of indices */
! const struct _vtab_idx { /* For each index */
! int nCol; /* Number of columns in this index */
! const struct _vtab_idx_col { /* For each column in this index */
! int iCol; /* The column in table that is indexed */
! const char *zColl; /* Name of the collating sequence */
! } *aCol;
! } *aIdx;
!
! /* The rest of this structure define callbacks for various operations
! ** against the virtual table
! */
! int (*xConnect)(void *pArg);
! int (*xOpenCursor)(void *pArg, sqlite3_vtab_cursor**, int idx);
! int (*xInsert)(sqlite3_vtab_cursor*, sqlite3_int64 rowid, int nCol,
! sqlite3_value **apCol);
! sqlite3_int64 (*xNewRowid)(sqlite3_vtab_cursor*);
! int (*xDelete)(sqlite3_vtab_cursor*);
! int (*xSeek)(sqlite3_vtab_cursor*, int, sqlite3_value**, int*);
! int (*xFirst)(sqlite3_vtab_cursor*);
! int (*xLast)(sqlite3_vtab_cursor*);
! int (*xNext)(sqlite3_vtab_cursor*);
! int (*xPrevious)(sqlite3_vtab_cursor*);
! int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int);
! int (*xCloseCursor)(sqlite3_vtab_cursor*);
! int (*xBegin)(void *pArg);
! int (*xSync)(void *pArg);
! int (*xCommit)(void *pArg);
! int (*xRollback)(void *pArg);
! int (*xIsInTrans)(void *pArg);
! void (*xDisconnect)(void *pArg);
! };
! A virtual table implementation must define and initialize an
! instance of the sqlite_vtab structure and pass a pointer to
! that structure into the sqlite3_create_virtual_table API in
! order to register the virtual table object with an SQLite
! database connection. The details of how a sqlite3_vtab
! object must be initialized are documented separately.
!
! The sqlite3_create_virtual_table API used to create a new
! virtual table is as follows:
!
! int sqlite3_create_virtual_table(
! sqlite3 *db,
! const char *zTableName,
! const sqlite3_vtab *pVTab,
! void *pArg
! );
! The zTableName paramater is the name of the table to be created.
! The pVTab parameter defines the characteristics of the table.
! The pArg parameter can have additional information needed by
! the virtual table instance. The pArg parameter is passed
! unaltered into many of the callback methods defined in the
sqlite3_vtab structure.
! -----
! _The latest thoughts on virtual tables as of 2006-06-02:_
!
! A virtual table is created using SQL as follows:
! CREATE TABLE vtabname USING c_module(arg-list);
! For example, suppose we want to create a virtual table that
! will do full-text search on the content of email messages:
!
! CREATE TABLE emailfts USING fulltextsearch1;
!
! The "fulltextsearch1" identifier is associated with a C-language
! module that is registered separately, in a manner similar to
! the way collating sequences and user-defined functions are
! registered. The argument-list to the module is optional. No
! arguments are shown in the example above, though in practice we
! would probably add arguments to specify various options to the
! full text search engine, such as what tokenizer and stemmer
! algorithms to use and whether or not token positional information
! should be stored in order to help with phrase queries.
!
! In the fulltextsearch1 module, the columns of the virtual
! table are fixed. One can image a more sophisticated implementation
! in which the columns are specified by arguments:
!
! CREATE TABLE emailfts USING fts2(
! docid INTEGER PRIMARY KEY,
! subj TEXT INDEXED,
! body TEXT INDEXED
! );
! The comma-separate arguments to the C-language module in a
! virtual table create statement can be any arbitrary sequence
! of tokens. The arguments are passed into the module as a
! array of strings, one string for each argument. The module
! is responsible for parsing the arguments. In the example
! above, we would expect the module to create a virtual table
! containing three columns named docid, subj, and body. We
! further expect the virtual table to cause docid to be the
! rowid and that subj and body are both columns that participate
! in the full-text index. In this example, the internal parser
! in fts2 would interpret its arguments very much like a standard
! SQL table declaration.
!
! But arguments do not have to be interpreted this way. The
! arguments could be used to specify optional parameters.
! Consider the following example:
!
! CREATE TABLE emailfts3 USING fts3(
! stemmer = porter,
! retain content = off,
! fast phrase searching = off
);
! Here we have a example of a full-text search
! engine that has fixed column names, but is configured to
! select a different stemmer, to store only the index and not the
! original text of the documents being indexed, and to use a slower
! but more disk-space efficient algorithm for doing phrase searching.
!
! All of the examples above are hypothetical. They are intended to
! show the flexibility of the virtual table mechanism. An actual
! full-text search virtual table might work differently from the
! examples above. The examples are intended to illustrate the
! power and flexibility of virtual tables, not to serve as a
! design spec for full-text search.
!
! The CREATE TABLE statements for virtual tables
! are stored in the SQLITE_MASTER table and are reread when a
! new connection is created or after a schema change. The
! implementation modules (the "fulltextsearch1") need not be
! registered at the time the virtual table is created.
! But the virtual table cannot be used until after the
! module has been registered.
!
! A full-text index would require additional real tables and/or
! indices to store auxiliary information needed to help speed
! the full-text search. These additional tables and indices
! would be created automatically, if they do not already exist,
! when the CREATE TABLE statement is first executed, or when
! the fulltextsearch1 module is first registered - whichever
! comes second. Triggers would also be created to cause
! changes to the email table to make corresponding changes to
! the real tables and indices that back the virtual table.
! A new C-language API will be developed that allows the
! module to mark these triggers as protected so that they
! cannot be accidently dropped. These triggers would also
! provent other processes from modifying the email table unless
! they had the fulltestsearch1 module registered.
!
! To better support full-text search, a new binary operator called
! "MATCH" will be provided. The default operation of MATCH is to
! call a function named "match" with parameters which are the left
! and right operands of the MATCH operator. Virtual tables can
! specify alternative processing for the MATCH operator, however.
! The optimizer will detect this and allow virtual tables to perform
! the MATCH operation for themselves. Thus, full-text query
! statements can be of the form:
!
! SELECT * FROM emailfts WHERE body MATCH 'search string'
! ORDER BY date DESC LIMIT 10;
!
! A default implementation of the MATCH procedure will do full
! text search without using indices. This will be slow for
! large data sets, but will provide consistent results even
! in the absence of a full-text index.
!
! ----
!
!
! Potential uses:
!
! *: Access to SQLite's internal symbol table information.
!
! *: Access to variables in the host scripting language.
!
! *: A full-text search interface
!
! *: Spatial Indexes
!
! *: Enabling SQL manipulation of data in statistics packages like R
! *: Constructing a federated group of SQLite engines on a cluster for large scale data mining.
! *: Make a completely different backing store for SQLite tables: memory mapped files, other databases, etc.
--- 11,214 ----
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 almost anything to a virtual table that they
! can do to a real table. There are some exceptions, though.
!
! *: You cannot create a trigger on a virtual table
! *: You cannot create additional indices on a virtual table.
! (Virtual tables can have indices but that must be built into
! the virtual table implementation. They cannot be added separately
! using CREATE INDEX statements.)
! *: You cannot run ALTER TABLE 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.
A virtual table might represent an in-memory data
structures. Or it might represent a view of data on disk
that is not in the SQLite format. Or the application
might compute the content of the virtual table on demand.
! Here are some postulated uses for virtual tables:
! *: Access to SQLite's internal symbol table information.
! *: Access to variables in the host scripting language.
! *: A full-text search interface
! *: Spatial Indexes
! *: Enabling SQL manipulation of data in statistics packages like R
! *: Constructing a federated group of SQLite engines on a cluster
! for large scale data mining.
! *: Make a completely different backing store for SQLite tables:
! memory mapped files, other databases, etc.
!
! *Usage*
!
! Create a virtual table using a CREATE VIRTUAL TABLE statement.
! This statement creates a table with a particular name and associates
! the table with a "module".
!
! CREATE TABLE tablename USING modulename;
!
! You can also provide comma-separated arguments to the module
! following the module name:
!
! CREATE TABLE tablename USING modulename(arg1, arg2, ...);
!
! The format of the arguments to the module is very general.
! Each argument can consist of keywords, string literals,
! identifiers, numbers, and punctuation.
! The arguments are passed as written (as text) into a
! constructor for the for the virtual table when the
! virtual table is created and the constructor is responsible
! for parsing and interpreting the arguments. The argument
! syntax is sufficiently general that a virtual table
! implementation can, if it wants to, interpret its arguments
! as column definitions in an ordinary CREATE TABLE statement.
! Or the implementation could impose some other interpretation
! on the arguments. The SQLite core does not care.
!
! Once a virtual table has been created, it can be used like
! any other table with the exceptions noted above and imposed
! by specific virtual table implementations. A virtual table
! is destroyed using the ordinary DROP TABLE syntax.
! *Implementation*
! Several new data structures are used by the virtual table
! implementation:
+ typedef struct sqlite3_vtab sqlite3_vtab;
+ typedef struct sqlite3_index_info sqlite3_index_info;
+ typedef struct sqlite3_vtab_cursor sqlite3_vtab_cursor;
+ typedef struct sqlite3_module sqlite3_module;
! The sqlite3_module structure defines a module object
! used to implement a virtual table. Think of a module
! as a class from which you can construct multiple virtual
! tables having similar properties. For example, one might
! have a module that provides read-only access to
! comma-separated-value (CSV) files on disk. That one
! module can then be used to create several virtual tables
! where each virtual table refers to a different CSV file.
!
! The module structure contains
! methods that are invoked by SQLite to perform various
! actions on the virtual table such as creating new instances
! of a virtual table or destroying old ones, reading and writing
! data, searching for and deleting, updating, or inserting
! rows. The module structure is explained in more detail
! below.
! Each virtual table instance is represented by an
sqlite3_vtab structure.
+ The sqlite3_vtab structure looks like this:
! struct sqlite3_vtab {
! const sqlite3_module *pModule;
! };
! Virtual table implementation will normally
! subclass this structure to add additional fields
! that are private to that implementation.
!
! The sqlite3_vtab_cursor structure
! represents a pointer into a virtual table.
! This is what an sqlite3_vtab_cursor looks like:
! struct sqlite3_vtab_cursor {
! sqlite3_vtab *pVtab;
! };
! Once again, useful implementation will likely subclass
! this structure to add additional fields that are
! private to the implementation.
!
! The sqlite3_index_info structure is used to pass
! information into out of the xBestIndex method of
! the module that implements a virtual table. For
! additional information on this structure, see the
! VirtualTableBestIndexMethod documentation.
!
! Before a CREATE VIRTUAL TABLE statement can be run,
! the module specified in that statement must be
! registered with the database connection. This
! is accomplished using this routine:
!
! int sqlite3_create_module(
! sqlite3 *db, /* SQLite connection to register module with */
! const char *zName, /* Name of the module */
! const sqlite3_module *, /* Methods for the module */
! void * /* Client data for xCreate/xConnect */
);
! The sqlite3_create_module() command associates a module
! name with an sqlite3_module structure and a separate
! client data that is specific to each module. The
! module structure looks like this:
!
! struct sqlite3_module {
! int iVersion;
! const char *zName;
! int (*xCreate)(sqlite3*, void *pAux,
! int argc, char **argv,
! sqlite3_vtab **ppVTab);
! int (*xConnect)(sqlite3*, void *pAux,
! int argc, char **argv,
! sqlite3_vtab **ppVTab);
! int (*xBestIndex)(sqlite3_vtab *pVTab, sqlite3_index_info*);
! int (*xDisconnect)(sqlite3_vtab *pVTab);
! int (*xDestroy)(sqlite3_vtab *pVTab);
! int (*xOpen)(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor);
! int (*xClose)(sqlite3_vtab_cursor*);
! int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr,
! int argc, sqlite3_value **argv);
! int (*xNext)(sqlite3_vtab_cursor*);
! int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int);
! int (*xRowid)(sqlite3_vtab_cursor*, sqlite_int64 *pRowid);
! int (*xUpdate)(sqlite3_vtab *, int, sqlite3_value **, sqlite_int64 *);
! int (*xBegin)(sqlite3_vtab *pVTab);
! int (*xSync)(sqlite3_vtab *pVTab);
! int (*xCommit)(sqlite3_vtab *pVTab);
! int (*xRollback)(sqlite3_vtab *pVTab);
! };
! The module structure defines all of the methods for each
! virtual table object. The module structure also contains
! the iVersion field which defines the particular edition of
! the module table structure. Currently, iVersion is always
! 1, but in future releases of SQLite the module structure
! definition might be extended with additional methods and in
! that case the iVersion value will be increased.
!
! The module structure also contains the zName field which
! is the name of the module used for error messages.
!
! The rest of the module structure consists of methods used
! to implement various features of the virtual table.
! Details on what each of these methods do are provided
! on the VirtualTableMethods page.
!
! *Creating New Virtual Tables*
!
! Follow these steps to create your own virtual table:
!
! 1: Write all necessary methods.
! 2: Create an instance of the sqlite3_module structure
! containing pointers to all the methods from step 1.
! 3: Register your sqlite3_module structure using the
! sqlite3_create_module() API.
! 4: Run a CREATE VIRTUAL TABLE command that specifies
! your module in the USING clause.
!
! The only really hard part is step 1. You might want to
! start with an existing virtual table implementation and
! modify it to suit your needs. There are several virtual
! table methods implemented in the SQLite source tree
! (for testing purposes).
! You might can use one of those as a guide. Locate
! these test virtual methods by searching for "sqlite3_create_method".
! You might also want to implement your new virtual table
! as a {link: wiki?p=LoadableExtensions loadable extension}.
Small. Fast. Reliable.
Choose any three.
Choose any three.
|
|