*** 100,105 **** --- 100,168 ---- unaltered into many of the callback methods defined in the sqlite3_vtab structure. + ----- + _The latest thoughts on virtual tables as of 2006-06-01:_ + + The concept of a virtual table has expanded to include + a virtual view. A virtual table is an entity unto itself. + A virtual view provides an alternative access mechanism to + an existing real table. Full-text search will likely use + virtual views instead of virtual tables. + + A virtual table or view is created using SQL as follows: + + CREATE TABLE vtabname USING c_module(arg-list); + CREATE VIEW vview ON table(column-list) USING c_module(arg-list); + + For example, suppose we have a table containing the content + of email messages: + + CREATE TABLE email(xto TEXT, xfrom TEXT, subj TEXT, body TEXT); + + To create a full-text index on the subject and body of this + table, one might do the following: + + CREATE VIEW emailfts ON email(subj,body) USING fulltextsearch1; + + The "fulltextsearch1" identifier is associated with a C-language + module that is registered separately, in a mannery similar to + the say 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. + + The CREATE TABLE and CREATE VIEW 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 or view is created. + But the virtual table or view 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 VIEW 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 view. + 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. + + Question: Should we allow multiple tables to be associated + to a virtual view? + + ---- + + Potential uses: *: Access to SQLite's internal symbol table information.
Small. Fast. Reliable.
Choose any three.
Choose any three.
|
|