*** 101,118 ****
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:
--- 101,111 ----
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 have a table containing the content
of email messages:
***************
*** 125,132 ****
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
--- 118,125 ----
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 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
***************
*** 134,174 ****
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?
!
! Virtual tables and view can be used in the FROM clause of
! a SELECT statement just like regular tables and views. But
! in addition, virtual tables and views in the FROM clause
! can take arguments. These arguments are passed to the
! open-cursor method of the C-language implementation. The
! arguments are used, for example, to specify the search string:
!
! SELECT id FROM emailfts('subj:features virtual table')
! ORDER BY date DESC LIMIT 30;
----
--- 127,171 ----
algorithms to use and whether or not token positional information
should be stored in order to help with phrase queries.
! 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.
----
Small. Fast. Reliable.
Choose any three.
Choose any three.
|
|