System.Data.SQLite
Check-in [44c116ca23]
Not logged in

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Work in progress on a tool to update the embedded doc comments for the ISQLiteNativeModule interface.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | vtabDocComments
Files: files | file ages | folders
SHA1: 44c116ca23e81fb6df3c8d0aa476794cbf251b95
User & Date: mistachkin 2015-10-18 00:20:38
Context
2015-10-18
01:51
Fix line/paragraph handling. Add support for line prefix. check-in: 4e474edb97 user: mistachkin tags: vtabDocComments
00:20
Work in progress on a tool to update the embedded doc comments for the ISQLiteNativeModule interface. check-in: 44c116ca23 user: mistachkin tags: vtabDocComments
00:18
Move the ISQLiteNativeModule interface to its own file. check-in: b233d0f72d user: mistachkin tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Added Doc/Extra/Core/vtab.html.

            1  +<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
            2  +<html><head>
            3  +<meta http-equiv="content-type" content="text/html; charset=UTF-8">
            4  +<title>The Virtual Table Mechanism Of SQLite</title>
            5  +<style type="text/css">
            6  +body {
            7  +    margin: auto;
            8  +    font-family: Verdana, sans-serif;
            9  +    padding: 8px 1%;
           10  +}
           11  +
           12  +a { color: #044a64 }
           13  +a:visited { color: #734559 }
           14  +
           15  +.logo { position:absolute; margin:3px; }
           16  +.tagline {
           17  +  float:right;
           18  +  text-align:right;
           19  +  font-style:italic;
           20  +  width:300px;
           21  +  margin:12px;
           22  +  margin-top:58px;
           23  +}
           24  +
           25  +.menubar {
           26  +  clear: both;
           27  +  border-radius: 8px;
           28  +  background: #044a64;
           29  +  padding: 0px;
           30  +  margin: 0px;
           31  +  cell-spacing: 0px;
           32  +}    
           33  +.toolbar {
           34  +  text-align: center;
           35  +  line-height: 1.6em;
           36  +  margin: 0;
           37  +  padding: 0px 8px;
           38  +}
           39  +.toolbar a { color: white; text-decoration: none; padding: 6px 12px; }
           40  +.toolbar a:visited { color: white; }
           41  +.toolbar a:hover { color: #044a64; background: white; }
           42  +
           43  +.content    { margin: 5%; }
           44  +.content dt { font-weight:bold; }
           45  +.content dd { margin-bottom: 25px; margin-left:20%; }
           46  +.content ul { padding:0px; padding-left: 15px; margin:0px; }
           47  +
           48  +/* Things for "fancyformat" documents start here. */
           49  +.fancy img+p {font-style:italic}
           50  +.fancy .codeblock i { color: darkblue; }
           51  +.fancy h1,.fancy h2,.fancy h3,.fancy h4 {font-weight:normal;color:#044a64}
           52  +.fancy h2 { margin-left: 10px }
           53  +.fancy h3 { margin-left: 20px }
           54  +.fancy h4 { margin-left: 30px }
           55  +.fancy th {white-space:nowrap;text-align:left;border-bottom:solid 1px #444}
           56  +.fancy th, .fancy td {padding: 0.2em 1ex; vertical-align:top}
           57  +.fancy #toc a        { color: darkblue ; text-decoration: none }
           58  +.fancy .todo         { color: #AA3333 ; font-style : italic }
           59  +.fancy .todo:before  { content: 'TODO:' }
           60  +.fancy p.todo        { border: solid #AA3333 1px; padding: 1ex }
           61  +.fancy img { display:block; }
           62  +.fancy :link:hover, .fancy :visited:hover { background: wheat }
           63  +.fancy p,.fancy ul,.fancy ol,.fancy dl { margin: 1em 5ex }
           64  +.fancy li p { margin: 1em 0 }
           65  +/* End of "fancyformat" specific rules. */
           66  +
           67  +.yyterm {
           68  +  background: #fff;
           69  +  border: 1px solid #000;
           70  +  border-radius: 11px;
           71  +  padding-left: 4px;
           72  +  padding-right: 4px;
           73  +}
           74  +</style>
           75  +  
           76  +</head>
           77  +<body>
           78  +<div><!-- container div to satisfy validator -->
           79  +
           80  +<a href="index.html">
           81  +<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite Logo"
           82  + border="0"></a>
           83  +<div><!-- IE hack to prevent disappearing logo--></div>
           84  +<div class="tagline">Small. Fast. Reliable.<br>Choose any three.</div>
           85  +
           86  +<table width=100% class="menubar"><tr>
           87  +  <td width=100%>
           88  +  <div class="toolbar">
           89  +    <a href="about.html">About</a>
           90  +    <a href="sitemap.html">Sitemap</a>
           91  +    <a href="docs.html">Documentation</a>
           92  +    <a href="download.html">Download</a>
           93  +    <a href="copyright.html">License</a>
           94  +    <a href="news.html">News</a>
           95  +    <a href="support.html">Support</a>
           96  +  </div>
           97  +<script>
           98  +  gMsg = "Search SQLite Docs..."
           99  +  function entersearch() {
          100  +    var q = document.getElementById("q");
          101  +    if( q.value == gMsg ) { q.value = "" }
          102  +    q.style.color = "black"
          103  +    q.style.fontStyle = "normal"
          104  +  }
          105  +  function leavesearch() {
          106  +    var q = document.getElementById("q");
          107  +    if( q.value == "" ) { 
          108  +      q.value = gMsg
          109  +      q.style.color = "#044a64"
          110  +      q.style.fontStyle = "italic"
          111  +    }
          112  +  }
          113  +  function hideorshow(btn,obj){
          114  +    var x = document.getElementById(obj);
          115  +    var b = document.getElementById(btn);
          116  +    if( x.style.display!='none' ){
          117  +      x.style.display = 'none';
          118  +      b.innerHTML='show';
          119  +    }else{
          120  +      x.style.display = '';
          121  +      b.innerHTML='hide';
          122  +    }
          123  +    return false;
          124  +  }
          125  +</script>
          126  +<td>
          127  +    <div style="padding:0 1em 0px 0;white-space:nowrap">
          128  +    <form name=f method="GET" action="https://www.sqlite.org/search">
          129  +      <input id=q name=q type=text
          130  +       onfocus="entersearch()" onblur="leavesearch()" style="width:24ex;padding:1px 1ex; border:solid white 1px; font-size:0.9em ; font-style:italic;color:#044a64;" value="Search SQLite Docs...">
          131  +      <input type=submit value="Go" style="border:solid white 1px;background-color:#044a64;color:white;font-size:0.9em;padding:0 1ex">
          132  +    </form>
          133  +    </div>
          134  +  </table>
          135  +
          136  +<div class=startsearch></div>
          137  +  
          138  +
          139  +
          140  +<h1 align="center">The Virtual Table Mechanism Of SQLite</h1>
          141  +
          142  +
          143  +<h2>1.0 Introduction</h2>
          144  +
          145  +<p>A virtual table is an object that is registered with an open SQLite
          146  +<a href="c3ref/sqlite3.html">database connection</a>. From the perspective of an SQL statement,
          147  +the virtual table object looks like any other table or view. 
          148  +But behind the scenes, queries and updates on a virtual table
          149  +invoke callback methods of the virtual table object instead of
          150  +reading and writing to the database file.
          151  +
          152  +<p>The virtual table mechanism allows an application to publish
          153  +interfaces that are accessible from SQL statements as if they were
          154  +tables. SQL statements can do almost anything to a
          155  +virtual table that they can do to a real table, with the following
          156  +exceptions:
          157  +
          158  +<p>
          159  +<ul>
          160  +<li> One cannot create a trigger on a virtual table.
          161  +<li> One cannot create additional indices on a virtual table. 
          162  +     (Virtual tables can have indices but that must be built into
          163  +     the virtual table implementation.  Indices cannot be added
          164  +     separately using <a href="lang_createindex.html">CREATE INDEX</a> statements.)
          165  +<li> One cannot run <a href="lang_altertable.html">ALTER TABLE ... ADD COLUMN</a>
          166  +     commands against a virtual table.
          167  +</ul>
          168  +
          169  +<p>Individual virtual table implementations might impose additional
          170  +constraints. For example, some virtual implementations might provide
          171  +read-only tables. Or some virtual table implementations might allow
          172  +<a href="lang_insert.html">INSERT</a> or <a href="lang_delete.html">DELETE</a> but not <a href="lang_update.html">UPDATE</a>.  Or some virtual table implementations
          173  +might limit the kinds of UPDATEs that can be made.
          174  +
          175  +<p>A virtual table might represent an in-memory data structures. 
          176  +Or it might represent a view of data on disk that is not in the
          177  +SQLite format. Or the application might compute the content of the 
          178  +virtual table on demand.
          179  +
          180  +<p>Here are some existing and postulated uses for virtual tables:
          181  +
          182  +<ul>
          183  +<li> A <a href="fts3.html">full-text search</a> interface
          184  +<li> Spatial indices using <a href="rtree.html">R-Trees</a>
          185  +<li> Introspect the disk content of an SQLite database file
          186  +     (the <a href="dbstat.html">dbstat virtual table</a>)
          187  +<li> Read and/or write the content of a comma-separated value (CSV)
          188  +     file
          189  +<li> Access the filesystem of the host computer as if it were a database table
          190  +<li> Enabling SQL manipulation of data in statistics packages like R
          191  +</ul>
          192  +
          193  +<h3>1.1 Usage</h3>
          194  +
          195  +<p>A virtual table is created using a <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement.
          196  +
          197  +<p><b><a href="syntax/create-virtual-table-stmt.html">create-virtual-table-stmt:</a></b>
          198  +<button id='x1475' onclick='hideorshow("x1475","x1476")'>hide</button></p>
          199  + <blockquote id='x1476'>
          200  + <img alt="syntax diagram create-virtual-table-stmt" src="images/syntax/create-virtual-table-stmt.gif" />
          201  +</blockquote>
          202  +
          203  +
          204  +<p>The CREATE VIRTUAL TABLE statement creates a new table
          205  +called <span class='yyterm'>table-name</span> derived from the class
          206  +class <span class='yyterm'>module-name</span>.  The <span class='yyterm'>module-name</span>
          207  +is the name that is registered for the virtual table by
          208  +the <a href="c3ref/create_module.html">sqlite3_create_module()</a> interface.
          209  +
          210  +<blockquote><pre>
          211  +   CREATE VIRTUAL TABLE tablename USING modulename;
          212  +</pre></blockquote>
          213  +
          214  +<p>One can also provide comma-separated arguments to the module following 
          215  +the module name:
          216  +
          217  +<blockquote><pre>
          218  +   CREATE VIRTUAL TABLE tablename USING modulename(arg1, arg2, ...);
          219  +</pre></blockquote>
          220  +
          221  +<p>The format of the arguments to the module is very general. Each 
          222  +<span class='yyterm'>module-argument</span>
          223  +may contain keywords, string literals, identifiers, numbers, and 
          224  +punctuation. Each <span class='yyterm'>module-argument</span> is passed as 
          225  +written (as text) into the
          226  +<a href="vtab.html#xcreate">constructor method</a> of the virtual table implementation 
          227  +when the virtual 
          228  +table is created and that constructor is responsible for parsing and 
          229  +interpreting the arguments. The argument syntax is sufficiently general 
          230  +that a virtual table implementation can, if it wants to, interpret its
          231  +arguments as <a href="lang_createtable.html#tablecoldef">column definitions</a> in an ordinary <a href="lang_createtable.html">CREATE TABLE</a> statement. 
          232  +The implementation could also impose some other interpretation on the 
          233  +arguments.
          234  +
          235  +<p>Once a virtual table has been created, it can be used like any other 
          236  +table with the exceptions noted above and imposed by specific virtual
          237  +table implementations. A virtual table is destroyed using the ordinary
          238  +<a href="lang_droptable.html">DROP TABLE</a> syntax.
          239  +
          240  +<h4>1.1.1 Temporary virtual tables</h4>
          241  +
          242  +<p>There is no "CREATE TEMP VIRTUAL TABLE" statement.  To create a
          243  +temporary virtual table, add the "temp" schema
          244  +before the virtual table name.
          245  +
          246  +<blockcuqote><pre>
          247  +   CREATE VIRTUAL TABLE <b>temp.</b>tablename USING module(arg1, ...);
          248  +</pre></blockquote>
          249  +
          250  +<a name="epovtab"></a>
          251  +
          252  +<h4>1.1.2 Eponymous virtual tables</h4>
          253  +
          254  +<p>Some virtual tables exist automatically in the "main" schema of
          255  +every database connection in which their
          256  +module is registered, even without a <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement.
          257  +Such virtual tables are called "eponymous virtual tables".
          258  +To use an eponymous virtual table, simple use the 
          259  +module name as if it were a table.
          260  +Eponymous virtual tables exist in the "main" schema only, so they will
          261  +not work if prefixed with a different schema name.
          262  +
          263  +<p>An example of an eponymous virtual table is the <a href="dbstat.html">dbstat virtual table</a>.
          264  +To use the dbstat virtual table as an eponymous virtual table, 
          265  +simply query against the "dbstat"
          266  +module name, as if it were an ordinary table.  (Note that SQLite
          267  +must be compiled with the <a href="compile.html#enable_dbstat_vtab">SQLITE_ENABLE_DBSTAT_VTAB</a> option to include
          268  +the dbstat virtual table in the build.)
          269  +
          270  +<blockquote><pre>
          271  +   SELECT * FROM dbstat;
          272  +</pre></blockquote>
          273  +
          274  +<p>A virtual table is eponymous if its <a href="vtab.html#xcreate">xCreate</a> method is the exact same
          275  +function as the <a href="vtab.html#xconnect">xConnect</a> method, or if the <a href="vtab.html#xcreate">xCreate</a> method is NULL.
          276  +The <a href="vtab.html#xcreate">xCreate</a> method is called when a virtual table is first created
          277  +using the <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement.  The <a href="vtab.html#xconnect">xConnect</a> method whenever
          278  +a database connection attaches to or reparses a schema. When these two methods
          279  +are the same, that indicates that the virtual table has no persistent
          280  +state that needs to be created and destroyed.
          281  +
          282  +<a name="epoonlyvtab"></a>
          283  +
          284  +<h5>1.1.2.1 Eponymous-only virtual tables</h5>
          285  +<p>If the <a href="vtab.html#xcreate">xCreate</a> method is NULL, then
          286  +<a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statements are prohibited for that virtual table,
          287  +and the virtual table is an "eponymous-only virtual table".
          288  +Eponymous-only virtual tables are useful as 
          289  +<a href="vtab.html#tabfunc2">table-valued functions</a>.
          290  +
          291  +<p>
          292  +Note that SQLite versions prior to 3.9.0 did not check the xCreate method
          293  +for NULL before invoking it.  So if an eponymous-only virtual table is
          294  +registered with SQLite version 3.8.11.1 or earlier and a <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a>
          295  +command is attempted against that virtual table module, a jump to a NULL
          296  +pointer will occur, resulting in a crash.
          297  +
          298  +<h3>1.2 Implementation</h3>
          299  +
          300  +<p>Several new C-level objects are used by the virtual table implementation:
          301  +
          302  +<blockquote><pre>
          303  +  typedef struct sqlite3_vtab sqlite3_vtab;
          304  +  typedef struct sqlite3_index_info sqlite3_index_info;
          305  +  typedef struct sqlite3_vtab_cursor sqlite3_vtab_cursor;
          306  +  typedef struct sqlite3_module sqlite3_module;
          307  +</pre></blockquote>
          308  +
          309  +<p>The <a href="c3ref/module.html">sqlite3_module</a> structure defines a module object used to implement
          310  +a virtual table. Think of a module as a class from which one can 
          311  +construct multiple virtual tables having similar properties. For example,
          312  +one might have a module that provides read-only access to 
          313  +comma-separated-value (CSV) files on disk. That one module can then be
          314  +used to create several virtual tables where each virtual table refers
          315  +to a different CSV file.
          316  +
          317  +<p>The module structure contains methods that are invoked by SQLite to
          318  +perform various actions on the virtual table such as creating new
          319  +instances of a virtual table or destroying old ones, reading and
          320  +writing data, searching for and deleting, updating, or inserting rows. 
          321  +The module structure is explained in more detail below.
          322  +
          323  +<p>Each virtual table instance is represented by an <a href="c3ref/vtab.html">sqlite3_vtab</a> structure. 
          324  +The sqlite3_vtab structure looks like this:
          325  +
          326  +<blockquote><pre>
          327  +  struct sqlite3_vtab {
          328  +    const sqlite3_module *pModule;
          329  +    int nRef;
          330  +    char *zErrMsg;
          331  +  };
          332  +</pre></blockquote>
          333  +
          334  +<p>Virtual table implementations will normally subclass this structure 
          335  +to add additional private and implementation-specific fields. 
          336  +The nRef field is used internally by the SQLite core and should not 
          337  +be altered by the virtual table implementation. The virtual table 
          338  +implementation may pass error message text to the core by putting 
          339  +an error message string in zErrMsg.
          340  +Space to hold this error message string must be obtained from an
          341  +SQLite memory allocation function such as <a href="c3ref/mprintf.html">sqlite3_mprintf()</a> or
          342  +<a href="c3ref/free.html">sqlite3_malloc()</a>.
          343  +Prior to assigning a new value to zErrMsg, the virtual table 
          344  +implementation must free any preexisting content of zErrMsg using 
          345  +<a href="c3ref/free.html">sqlite3_free()</a>. Failure to do this will result in a memory leak. 
          346  +The SQLite core will free and zero the content of zErrMsg when it 
          347  +delivers the error message text to the client application or when 
          348  +it destroys the virtual table. The virtual table implementation only 
          349  +needs to worry about freeing the zErrMsg content when it overwrites 
          350  +the content with a new, different error message.
          351  +
          352  +<p>The <a href="c3ref/vtab_cursor.html">sqlite3_vtab_cursor</a> structure represents a pointer to a specific
          353  +row of a virtual table. This is what an sqlite3_vtab_cursor looks like:
          354  +
          355  +<blockquote><pre>
          356  +  struct sqlite3_vtab_cursor {
          357  +    sqlite3_vtab *pVtab;
          358  +  };
          359  +</pre></blockquote>
          360  +
          361  +<p>Once again, practical implementations will likely subclass this 
          362  +structure to add additional private fields.
          363  +
          364  +<p>The <a href="c3ref/index_info.html">sqlite3_index_info</a> structure is used to pass information into
          365  +and out of the xBestIndex method of the module that implements a 
          366  +virtual table.
          367  +
          368  +<p>Before a <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement can be run, the module 
          369  +specified in that statement must be registered with the database 
          370  +connection. This is accomplished using either of the <a href="c3ref/create_module.html">sqlite3_create_module()</a>
          371  +or <a href="c3ref/create_module.html">sqlite3_create_module_v2()</a> interfaces:
          372  +
          373  +<blockquote><pre>
          374  +  int sqlite3_create_module(
          375  +    sqlite3 *db,               /* SQLite connection to register module with */
          376  +    const char *zName,         /* Name of the module */
          377  +    const sqlite3_module *,    /* Methods for the module */
          378  +    void *                     /* Client data for xCreate/xConnect */
          379  +  );
          380  +  int sqlite3_create_module_v2(
          381  +    sqlite3 *db,               /* SQLite connection to register module with */
          382  +    const char *zName,         /* Name of the module */
          383  +    const sqlite3_module *,    /* Methods for the module */
          384  +    void *,                    /* Client data for xCreate/xConnect */
          385  +    void(*xDestroy)(void*)     /* Client data destructor function */
          386  +  );
          387  +</pre></blockquote>
          388  +
          389  +<p>The <a href="c3ref/create_module.html">sqlite3_create_module()</a> and <a href="c3ref/create_module.html">sqlite3_create_module_v2()</a>
          390  +routines associates a module name with 
          391  +an <a href="c3ref/module.html">sqlite3_module</a> structure and a separate client data that is specific 
          392  +to each module.  The only difference between the two create_module methods
          393  +is that the _v2 method includes an extra parameter that specifies a
          394  +destructor for client data pointer.  The module structure is what defines
          395  +the behavior of a virtual table.  The module structure looks like this:
          396  +
          397  +<blockquote><pre>  
          398  +  struct sqlite3_module {
          399  +    int iVersion;
          400  +    int (*xCreate)(sqlite3*, void *pAux,
          401  +                 int argc, char **argv,
          402  +                 sqlite3_vtab **ppVTab,
          403  +                 char **pzErr);
          404  +    int (*xConnect)(sqlite3*, void *pAux,
          405  +                 int argc, char **argv,
          406  +                 sqlite3_vtab **ppVTab,
          407  +                 char **pzErr);
          408  +    int (*xBestIndex)(sqlite3_vtab *pVTab, sqlite3_index_info*);
          409  +    int (*xDisconnect)(sqlite3_vtab *pVTab);
          410  +    int (*xDestroy)(sqlite3_vtab *pVTab);
          411  +    int (*xOpen)(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor);
          412  +    int (*xClose)(sqlite3_vtab_cursor*);
          413  +    int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr,
          414  +                  int argc, sqlite3_value **argv);
          415  +    int (*xNext)(sqlite3_vtab_cursor*);
          416  +    int (*xEof)(sqlite3_vtab_cursor*);
          417  +    int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int);
          418  +    int (*xRowid)(sqlite3_vtab_cursor*, sqlite_int64 *pRowid);
          419  +    int (*xUpdate)(sqlite3_vtab *, int, sqlite3_value **, sqlite_int64 *);
          420  +    int (*xBegin)(sqlite3_vtab *pVTab);
          421  +    int (*xSync)(sqlite3_vtab *pVTab);
          422  +    int (*xCommit)(sqlite3_vtab *pVTab);
          423  +    int (*xRollback)(sqlite3_vtab *pVTab);
          424  +    int (*xFindFunction)(sqlite3_vtab *pVtab, int nArg, const char *zName,
          425  +                       void (**pxFunc)(sqlite3_context*,int,sqlite3_value**),
          426  +                       void **ppArg);
          427  +    int (*Rename)(sqlite3_vtab *pVtab, const char *zNew);
          428  +    /* The methods above are in version 1 of the sqlite_module object. Those 
          429  +    ** below are for version 2 and greater. */
          430  +    int (*xSavepoint)(sqlite3_vtab *pVTab, int);
          431  +    int (*xRelease)(sqlite3_vtab *pVTab, int);
          432  +    int (*xRollbackTo)(sqlite3_vtab *pVTab, int);
          433  +  };
          434  +</pre></blockquote>
          435  +
          436  +<p>The module structure defines all of the methods for each virtual 
          437  +table object. The module structure also contains the iVersion field which
          438  +defines the particular edition of the module table structure. Currently, 
          439  +iVersion is always 1, but in future releases of SQLite the module structure 
          440  +definition might be extended with additional methods and in that case 
          441  +the iVersion value will be increased.
          442  +
          443  +<p>The rest of the module structure consists of methods used to implement
          444  +various features of the virtual table. Details on what each of these 
          445  +methods do are provided in the sequel.
          446  +
          447  +<h3>1.3 Virtual Tables And Shared Cache</h3>
          448  +
          449  +<p>Prior to SQLite <a href="releaselog/3_6_17.html">version 3.6.17</a>, the virtual table mechanism assumes 
          450  +that each <a href="c3ref/sqlite3.html">database connection</a> kept
          451  +its own copy of the database schema. Hence, the virtual table mechanism
          452  +could not be used in a database that has <a href="sharedcache.html">shared cache mode</a> enabled. 
          453  +The <a href="c3ref/create_module.html">sqlite3_create_module()</a> interface would return an error if 
          454  +<a href="sharedcache.html">shared cache mode</a> is enabled.  That restriction was relaxed
          455  +beginning with SQLite <a href="releaselog/3_6_17.html">version 3.6.17</a>.
          456  +
          457  +<h3>1.4 Creating New Virtual Table Implementations</h3>
          458  +
          459  +<p>Follow these steps to create your own virtual table:
          460  +
          461  +<p>
          462  +<ol>
          463  +<li> Write all necessary methods.
          464  +<li> Create an instance of the <a href="c3ref/module.html">sqlite3_module</a> structure containing pointers
          465  +     to all the methods from step 1.
          466  +<li> Register your <a href="c3ref/module.html">sqlite3_module</a> structure using one of the
          467  +     <a href="c3ref/create_module.html">sqlite3_create_module()</a> or <a href="c3ref/create_module.html">sqlite3_create_module_v2()</a> interfaces.
          468  +<li> Run a <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> command that specifies the new module in 
          469  +     the USING clause. 
          470  +</ol>
          471  +
          472  +<p>The only really hard part is step 1. You might want to start with an 
          473  +existing virtual table implementation and modify it to suit your needs.
          474  +There are several virtual table implementations in the SQLite source tree 
          475  +(for testing purposes). You might use one of those as a guide. Locate 
          476  +these test virtual table implementations by searching 
          477  +for "sqlite3_create_module".
          478  +
          479  +<p>You might also want to implement your new virtual table as a 
          480  +<a href="c3ref/load_extension.html">loadable extension</a>.
          481  +
          482  +<h2>2.0 Virtual Table Methods</h2>
          483  +
          484  +<a name="xcreate"></a>
          485  +
          486  +<h3>2.1 The xCreate Method</h3>
          487  +
          488  +<blockquote><pre>
          489  +  int (*xCreate)(sqlite3 *db, void *pAux,
          490  +               int argc, char **argv,
          491  +               sqlite3_vtab **ppVTab,
          492  +               char **pzErr);
          493  +</pre></blockquote>
          494  +
          495  +<p>This method is called to create a new instance of a virtual table 
          496  +in response to a <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement. 
          497  +The db parameter is a pointer to the SQLite <a href="c3ref/sqlite3.html">database connection</a> that 
          498  +is executing the <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement. 
          499  +The pAux argument is the copy of the client data pointer that was the 
          500  +fourth argument to the <a href="c3ref/create_module.html">sqlite3_create_module()</a> or
          501  +<a href="c3ref/create_module.html">sqlite3_create_module_v2()</a> call that registered the 
          502  +<a href="c3ref/module.html">virtual table module</a>. 
          503  +The argv parameter is an array of argc pointers to null terminated strings. 
          504  +The first string, argv[0], is the name of the module being invoked.   The
          505  +module name is the name provided as the second argument to 
          506  +<a href="c3ref/create_module.html">sqlite3_create_module()</a> and as the argument to the USING clause of the
          507  +<a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement that is running.
          508  +The second, argv[1], is the name of the database in which the new virtual table is being created. The database name is "main" for the primary database, or
          509  +"temp" for TEMP database, or the name given at the end of the <a href="lang_attach.html">ATTACH</a>
          510  +statement for attached databases.  The third element of the array, argv[2], 
          511  +is the name of the new virtual table, as specified following the TABLE
          512  +keyword in the <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement.
          513  +If present, the fourth and subsequent strings in the argv[] array report 
          514  +the arguments to the module name in the <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> statement.
          515  +
          516  +<p>The job of this method is to construct the new virtual table object
          517  +(an <a href="c3ref/vtab.html">sqlite3_vtab</a> object) and return a pointer to it in *ppVTab.
          518  +
          519  +<p>As part of the task of creating a new <a href="c3ref/vtab.html">sqlite3_vtab</a> structure, this 
          520  +method <u>must</u> invoke <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> to tell the SQLite 
          521  +core about the columns and datatypes in the virtual table. 
          522  +The <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> API has the following prototype:
          523  +
          524  +<blockquote><pre>
          525  +    int sqlite3_declare_vtab(sqlite3 *db, const char *zCreateTable)
          526  +</pre></blockquote>
          527  +
          528  +<p>The first argument to <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> must be the same 
          529  +<a href="c3ref/sqlite3.html">database connection</a> pointer as the first parameter to this method.
          530  +The second argument to <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> must a zero-terminated 
          531  +UTF-8 string that contains a well-formed <a href="lang_createtable.html">CREATE TABLE</a> statement that 
          532  +defines the columns in the virtual table and their data types. 
          533  +The name of the table in this CREATE TABLE statement is ignored, 
          534  +as are all constraints. Only the column names and datatypes matter.
          535  +The CREATE TABLE statement string need not to be 
          536  +held in persistent memory.  The string can be
          537  +deallocated and/or reused as soon as the <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a>
          538  +routine returns.
          539  +
          540  +<p>The xCreate method need not initialize the pModule, nRef, and zErrMsg
          541  +fields of the <a href="c3ref/vtab.html">sqlite3_vtab</a> object.  The SQLite core will take care of 
          542  +that chore.
          543  +
          544  +<p>The xCreate should return <a href="rescode.html#ok">SQLITE_OK</a> if it is successful in 
          545  +creating the new virtual table, or <a href="rescode.html#error">SQLITE_ERROR</a> if it is not successful.
          546  +If not successful, the <a href="c3ref/vtab.html">sqlite3_vtab</a> structure must not be allocated. 
          547  +An error message may optionally be returned in *pzErr if unsuccessful.
          548  +Space to hold the error message string must be allocated using
          549  +an SQLite memory allocation function like 
          550  +<a href="c3ref/free.html">sqlite3_malloc()</a> or <a href="c3ref/mprintf.html">sqlite3_mprintf()</a> as the SQLite core will
          551  +attempt to free the space using <a href="c3ref/free.html">sqlite3_free()</a> after the error has
          552  +been reported up to the application.
          553  +
          554  +<p>
          555  +If the xCreate method is omitted (left as a NULL pointer) then the
          556  +virtual table is an <a href="vtab.html#epoonlyvtab">eponymous-only virtual table</a>.  New instances of
          557  +the virtual table cannot be created using <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> and the
          558  +virtual table can only be used via its module name.
          559  +Note that SQLite versions prior to 3.9.0 do not understand
          560  +eponymous-only virtual tables and will segfault if an attempt is made
          561  +to <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> on an eponymous-only virtual table because
          562  +the xCreate method was not checked for null.
          563  +
          564  +<p>
          565  +If the xCreate method is the exact same pointer as the <a href="vtab.html#xconnect">xConnect</a> method,
          566  +that indicates that the virtual table does not need to initialize backing
          567  +store.  Such a virtual table can be used as an <a href="vtab.html#epovtab">eponymous virtual table</a>
          568  +or as a named virtual table using <a href="lang_createvtab.html">CREATE VIRTUAL TABLE</a> or both.
          569  +
          570  +<a name="hiddencol"></a>
          571  +
          572  +<h4>2.1.1 Hidden columns in virtual tables</h4>
          573  +<p>If a column datatype contains the special keyword "HIDDEN"
          574  +(in any combination of upper and lower case letters) then that keyword
          575  +it is omitted from the column datatype name and the column is marked 
          576  +as a hidden column internally. 
          577  +A hidden column differs from a normal column in three respects:
          578  +
          579  +<p>
          580  +<ul>
          581  +<li> Hidden columns are not listed in the dataset returned by 
          582  +     "<a href="pragma.html#pragma_table_info">PRAGMA table_info</a>",
          583  +<li> Hidden columns are not included in the expansion of a "*"
          584  +     expression in the result set of a <a href="lang_select.html">SELECT</a>, and
          585  +<li> Hidden columns are not included in the implicit column-list 
          586  +     used by an <a href="lang_insert.html">INSERT</a> statement that lacks an explicit column-list. 
          587  +</ul>
          588  +
          589  +<p>For example, if the following SQL is passed to <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a>:
          590  +
          591  +<blockquote><pre>
          592  +   CREATE TABLE x(a HIDDEN VARCHAR(12), b INTEGER, c INTEGER Hidden);
          593  +</pre></blockquote>
          594  +
          595  +<p>Then the virtual table would be created with two hidden columns,
          596  +and with datatypes of "VARCHAR(12)" and "INTEGER".
          597  +
          598  +<p>An example use of hidden columns can be seen in the <a href="fts3.html">FTS3</a> virtual 
          599  +table implementation, where every FTS virtual table
          600  +contains an <a href="fts3.html#hiddencol">FTS hidden column</a> that is used to pass information from the
          601  +virtual table into <a href="fts3.html#snippet">FTS auxiliary functions</a> and to the <a href="fts3.html#section_3">FTS MATCH</a> operator.
          602  +
          603  +<a name="tabfunc2"></a>
          604  +
          605  +<h4>2.1.2 Table-valued functions</h4>
          606  +
          607  +<p>A <a href="vtab.html">virtual table</a> that contains <a href="vtab.html#hiddencol">hidden columns</a> can be used like
          608  +a table-valued function in the FROM clause of a <a href="lang_select.html">SELECT</a> statement.
          609  +The arguments to the table-valued function become constraints on 
          610  +the HIDDEN columns of the virtual table.
          611  +
          612  +<p>For example, the "generate_series" extension (located in the
          613  +<a href="http://www.sqlite.org/src/artifact?ci=trunk&filename=ext/misc/series.c">ext/misc/series.c</a>
          614  +file in the <a href="http://www.sqlite.org/src/tree?ci=trunk">source tree</a>)
          615  +implements an <a href="vtab.html#epovtab">eponymous virtual table</a> with the following schema:
          616  +
          617  +<blockquote><pre>
          618  +CREATE TABLE generate_series(
          619  +  value,
          620  +  start HIDDEN,
          621  +  stop HIDDEN,
          622  +  step HIDDEN
          623  +);
          624  +</pre></blockquote>
          625  +
          626  +<p>The <a href="vtab.html#xbestindex">sqlite3_module.xBestIndex</a> method in the implementation of this
          627  +table checks for equality constraints against the HIDDEN columns, and uses
          628  +those as input parameters to determine the range of integer "value" outputs
          629  +to generate.  Reasonable defaults are used for any unconstrained columns.
          630  +For example, to list all integers between 5 and 50:
          631  +
          632  +<blockquote><pre>
          633  +SELECT value FROM generate_series(5,50);
          634  +</pre></blockquote>
          635  +
          636  +<p>The previous query is equivalent to the following:
          637  +
          638  +<blockquote><pre>
          639  +SELECT value FROM generate_series WHERE start=5 AND stop=50;
          640  +</pre></blockquote>
          641  +
          642  +<p>Arguments on the virtual table name are matched to <a href="vtab.html#hiddencol">hidden columns</a>
          643  +in order.  The number of arguments can be less than the
          644  +number of hidden columns, in which case the latter hidden columns are
          645  +unconstrained.  However, an error results if there are more arguments
          646  +than there are hidden columns in the virtual table.
          647  +
          648  +
          649  +<a name="xconnect"></a>
          650  +
          651  +<h3>2.2 The xConnect Method</h3>
          652  +
          653  +<blockquote><pre>
          654  +  int (*xConnect)(sqlite3*, void *pAux,
          655  +               int argc, char **argv,
          656  +               sqlite3_vtab **ppVTab,
          657  +               char **pzErr);
          658  +</pre></blockquote>
          659  +
          660  +<p>The xConnect method is very similar to <a href="vtab.html#xcreate">xCreate</a>. 
          661  +It has the same parameters and constructs a new <a href="c3ref/vtab.html">sqlite3_vtab</a> structure 
          662  +just like xCreate. 
          663  +And it must also call <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> like xCreate.
          664  +
          665  +<p>The difference is that xConnect is called to establish a new 
          666  +connection to an existing virtual table whereas xCreate is called 
          667  +to create a new virtual table from scratch.
          668  +
          669  +<p>The xCreate and xConnect methods are only different when the
          670  +virtual table has some kind of backing store that must be initialized 
          671  +the first time the virtual table is created. The xCreate method creates 
          672  +and initializes the backing store. The xConnect method just connects 
          673  +to an existing backing store.  When xCreate and xConnect are the same,
          674  +the table is an <a href="vtab.html#epovtab">eponymous virtual table</a>.
          675  +
          676  +<p>As an example, consider a virtual table implementation that 
          677  +provides read-only access to existing comma-separated-value (CSV)
          678  +files on disk. There is no backing store that needs to be created 
          679  +or initialized for such a virtual table (since the CSV files already 
          680  +exist on disk) so the xCreate and xConnect methods will be identical 
          681  +for that module.
          682  +
          683  +<p>Another example is a virtual table that implements a full-text index. 
          684  +The xCreate method must create and initialize data structures to hold 
          685  +the dictionary and posting lists for that index. The xConnect method,
          686  +on the other hand, only has to locate and use an existing dictionary 
          687  +and posting lists that were created by a prior xCreate call.
          688  +
          689  +<p>The xConnect method must return <a href="rescode.html#ok">SQLITE_OK</a> if it is successful 
          690  +in creating the new virtual table, or <a href="rescode.html#error">SQLITE_ERROR</a> if it is not 
          691  +successful. If not successful, the <a href="c3ref/vtab.html">sqlite3_vtab</a> structure must not be 
          692  +allocated. An error message may optionally be returned in *pzErr if 
          693  +unsuccessful. 
          694  +Space to hold the error message string must be allocated using
          695  +an SQLite memory allocation function like 
          696  +<a href="c3ref/free.html">sqlite3_malloc()</a> or <a href="c3ref/mprintf.html">sqlite3_mprintf()</a> as the SQLite core will
          697  +attempt to free the space using <a href="c3ref/free.html">sqlite3_free()</a> after the error has
          698  +been reported up to the application.
          699  +
          700  +<p>The xConnect method is required for every virtual table implementation, 
          701  +though the <a href="vtab.html#xcreate">xCreate</a> and xConnect pointers of the <a href="c3ref/module.html">sqlite3_module</a> object
          702  +may point to the same function if the virtual table does not need to
          703  +initialize backing store.
          704  +
          705  +<a name="xbestindex"></a>
          706  +
          707  +<h3>2.3 The xBestIndex Method</h3>
          708  +
          709  +<p>SQLite uses the xBestIndex method of a virtual table module to determine
          710  +the best way to access the virtual table. 
          711  +The xBestIndex method has a prototype like this:
          712  +
          713  +<blockquote><pre>
          714  +  int (*xBestIndex)(sqlite3_vtab *pVTab, sqlite3_index_info*);
          715  +</pre></blockquote>
          716  +
          717  +<p>The SQLite core communicates with the xBestIndex method by filling 
          718  +in certain fields of the <a href="c3ref/index_info.html">sqlite3_index_info</a> structure and passing a 
          719  +pointer to that structure into xBestIndex as the second parameter. 
          720  +The xBestIndex method fills out other fields of this structure which
          721  +forms the reply. The <a href="c3ref/index_info.html">sqlite3_index_info</a> structure looks like this:
          722  +
          723  +<blockquote><pre>
          724  +  struct sqlite3_index_info {
          725  +    /* Inputs */
          726  +    const int nConstraint;     /* Number of entries in aConstraint */
          727  +    const struct sqlite3_index_constraint {
          728  +       int iColumn;              /* Column on left-hand side of constraint */
          729  +       unsigned char op;         /* Constraint operator */
          730  +       unsigned char usable;     /* True if this constraint is usable */
          731  +       int iTermOffset;          /* Used internally - xBestIndex should ignore */
          732  +    } *const aConstraint;      /* Table of WHERE clause constraints */
          733  +    const int nOrderBy;        /* Number of terms in the ORDER BY clause */
          734  +    const struct sqlite3_index_orderby {
          735  +       int iColumn;              /* Column number */
          736  +       unsigned char desc;       /* True for DESC.  False for ASC. */
          737  +    } *const aOrderBy;         /* The ORDER BY clause */
          738  +
          739  +    /* Outputs */
          740  +    struct sqlite3_index_constraint_usage {
          741  +      int argvIndex;           /* if >0, constraint is part of argv to xFilter */
          742  +      unsigned char omit;      /* Do not code a test for this constraint */
          743  +    } *const aConstraintUsage;
          744  +    int idxNum;                /* Number used to identify the index */
          745  +    char *idxStr;              /* String, possibly obtained from sqlite3_malloc */
          746  +    int needToFreeIdxStr;      /* Free idxStr using sqlite3_free() if true */
          747  +    int orderByConsumed;       /* True if output is already ordered */
          748  +    double estimatedCost;      /* Estimated cost of using this index */
          749  +    <b>/* Fields below are only available in SQLite 3.8.2 and later */</b>
          750  +    sqlite3_int64 estimatedRows;    /* Estimated number of rows returned */
          751  +    <b>/* Fields below are only available in SQLite 3.9.0 and later */</b>
          752  +    int idxFlags;              /* Mask of SQLITE_INDEX_SCAN_* flags */
          753  +  };
          754  +</pre></blockquote>
          755  +
          756  +<p>Please note the warnings on the "estimatedRows" and "idxFlags" field.
          757  +These fields were added with SQLite versions 3.8.2 and 3.9.0, respectively. 
          758  +Any extension that reads or writes these fields must first check that the 
          759  +version of the SQLite library in use is greater than or equal to 3.8.2 or
          760  +3.9.0 - perhaps using a call to <a href="c3ref/libversion.html">sqlite3_version()</a>. The result of attempting 
          761  +to access these fields in an sqlite3_index_info structure created by an 
          762  +older version of SQLite are undefined.
          763  +
          764  +<p>In addition, there are some defined constants:
          765  +
          766  +<blockquote><pre>
          767  +  #define SQLITE_INDEX_CONSTRAINT_EQ    2
          768  +  #define SQLITE_INDEX_CONSTRAINT_GT    4
          769  +  #define SQLITE_INDEX_CONSTRAINT_LE    8
          770  +  #define SQLITE_INDEX_CONSTRAINT_LT    16
          771  +  #define SQLITE_INDEX_CONSTRAINT_GE    32
          772  +  #define SQLITE_INDEX_CONSTRAINT_MATCH 64
          773  +  #define SQLITE_INDEX_SCAN_UNIQUE      1     /* Scan visits at most 1 row */
          774  +</pre></blockquote>
          775  +
          776  +<p>The SQLite core calls the xBestIndex method when it is compiling a query
          777  +that involves a virtual table. In other words, SQLite calls this method 
          778  +when it is running <a href="c3ref/prepare.html">sqlite3_prepare()</a> or the equivalent. 
          779  +By calling this method, the 
          780  +SQLite core is saying to the virtual table that it needs to access 
          781  +some subset of the rows in the virtual table and it wants to know the
          782  +most efficient way to do that access. The xBestIndex method replies 
          783  +with information that the SQLite core can then use to conduct an 
          784  +efficient search of the virtual table.
          785  +
          786  +<p>While compiling a single SQL query, the SQLite core might call 
          787  +xBestIndex multiple times with different settings in <a href="c3ref/index_info.html">sqlite3_index_info</a>.
          788  +The SQLite core will then select the combination that appears to 
          789  +give the best performance.
          790  +
          791  +<p>Before calling this method, the SQLite core initializes an instance 
          792  +of the <a href="c3ref/index_info.html">sqlite3_index_info</a> structure with information about the
          793  +query that it is currently trying to process. This information 
          794  +derives mainly from the WHERE clause and ORDER BY or GROUP BY clauses 
          795  +of the query, but also from any ON or USING clauses if the query is a 
          796  +join. The information that the SQLite core provides to the xBestIndex 
          797  +method is held in the part of the structure that is marked as "Inputs". 
          798  +The "Outputs" section is initialized to zero.
          799  +
          800  +<p>The information in the <a href="c3ref/index_info.html">sqlite3_index_info</a> structure is ephemeral
          801  +and may be overwritten or deallocated as soon as the xBestIndex method
          802  +returns.  If the xBestIndex method needs to remember any part of the
          803  +<a href="c3ref/index_info.html">sqlite3_index_info</a> structure, it should make a copy.  Care must be
          804  +take to store the copy in a place where it will be deallocated, such
          805  +as in the idxStr field with needToFreeIdxStr set to 1.
          806  +
          807  +<p>Note that xBestIndex will always be called before <a href="vtab.html#xfilter">xFilter</a>, since
          808  +the idxNum and idxStr outputs from xBestIndex are required inputs to
          809  +xFilter.  However, there is no guarantee that xFilter will be called
          810  +following a successful xBestIndex.  
          811  +
          812  +<p>The xBestIndex method is required for every virtual table implementation.
          813  +
          814  +<h4>2.3.1 Inputs</h4>
          815  +
          816  +<p>The main thing that the SQLite core is trying to communicate to 
          817  +the virtual table is the constraints that are available to limit 
          818  +the number of rows that need to be searched. The aConstraint[] array 
          819  +contains one entry for each constraint. There will be exactly 
          820  +nConstraint entries in that array.
          821  +
          822  +<p>Each constraint will correspond to a term in the WHERE clause
          823  +or in a USING or ON clause that is of the form
          824  +
          825  +<blockquote>
          826  +     column  OP  EXPR
          827  +</blockquote>
          828  +
          829  +<p>Where "column" is a column in the virtual table, OP is an operator 
          830  +like "=" or "<", and EXPR is an arbitrary expression. So, for example,
          831  +if the WHERE clause contained a term like this:
          832  +
          833  +<blockquote><pre>
          834  +     a = 5
          835  +</pre></blockquote>
          836  +
          837  +<p>Then one of the constraints would be on the "a" column with 
          838  +operator "=" and an expression of "5". Constraints need not have a
          839  +literal representation of the WHERE clause. The query optimizer might
          840  +make transformations to the 
          841  +WHERE clause in order to extract as many constraints 
          842  +as it can. So, for example, if the WHERE clause contained something 
          843  +like this:
          844  +
          845  +<blockquote><pre>
          846  +     x BETWEEN 10 AND 100 AND 999>y
          847  +</pre></blockquote>
          848  +
          849  +<p>The query optimizer might translate this into three separate constraints:
          850  +
          851  +<blockquote><pre>
          852  +     x >= 10
          853  +     x <= 100
          854  +     y < 999
          855  +</pre></blockquote>
          856  +
          857  +<p>For each constraint, the aConstraint[].iColumn field indicates which 
          858  +column appears on the left-hand side of the constraint.
          859  +The first column of the virtual table is column 0. 
          860  +The rowid of the virtual table is column -1. 
          861  +The aConstraint[].op field indicates which operator is used. 
          862  +The SQLITE_INDEX_CONSTRAINT_* constants map integer constants 
          863  +into operator values.
          864  +Columns occur in the order they were defined by the call to
          865  +<a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> in the <a href="vtab.html#xcreate">xCreate</a> or <a href="vtab.html#xconnect">xConnect</a> method.
          866  +Hidden columns are counted when determining the column index.
          867  +
          868  +<p>The aConstraint[] array contains information about all constraints 
          869  +that apply to the virtual table. But some of the constraints might
          870  +not be usable because of the way tables are ordered in a join. 
          871  +The xBestIndex method must therefore only consider constraints 
          872  +that have an aConstraint[].usable flag which is true.
          873  +
          874  +<p>In addition to WHERE clause constraints, the SQLite core also 
          875  +tells the xBestIndex method about the ORDER BY clause. 
          876  +(In an aggregate query, the SQLite core might put in GROUP BY clause 
          877  +information in place of the ORDER BY clause information, but this fact
          878  +should not make any difference to the xBestIndex method.) 
          879  +If all terms of the ORDER BY clause are columns in the virtual table, 
          880  +then nOrderBy will be the number of terms in the ORDER BY clause 
          881  +and the aOrderBy[] array will identify the column for each term 
          882  +in the order by clause and whether or not that column is ASC or DESC.
          883  +
          884  +<h4>2.3.2 Outputs</h4>
          885  +
          886  +<p>Given all of the information above, the job of the xBestIndex 
          887  +method it to figure out the best way to search the virtual table.
          888  +
          889  +<p>The xBestIndex method fills the idxNum and idxStr fields with 
          890  +information that communicates an indexing strategy to the <a href="vtab.html#xfilter">xFilter</a> 
          891  +method. The information in idxNum and idxStr is arbitrary as far 
          892  +as the SQLite core is concerned. The SQLite core just copies the 
          893  +information through to the <a href="vtab.html#xfilter">xFilter</a> method. Any desired meaning can 
          894  +be assigned to idxNum and idxStr as long as xBestIndex and xFilter 
          895  +agree on what that meaning is.
          896  +
          897  +<p>The idxStr value may be a string obtained from an SQLite
          898  +memory allocation function such as <a href="c3ref/mprintf.html">sqlite3_mprintf()</a>. 
          899  +If this is the case, then the needToFreeIdxStr flag must be set to 
          900  +true so that the SQLite core will know to call <a href="c3ref/free.html">sqlite3_free()</a> on 
          901  +that string when it has finished with it, and thus avoid a memory leak.
          902  +
          903  +<p>If the virtual table will output rows in the order specified by 
          904  +the ORDER BY clause, then the orderByConsumed flag may be set to 
          905  +true. If the output is not automatically in the correct order 
          906  +then orderByConsumed must be left in its default false setting. 
          907  +This will indicate to the SQLite core that it will need to do a 
          908  +separate sorting pass over the data after it comes out of the virtual table.
          909  +
          910  +<p>The estimatedCost field should be set to the estimated number
          911  +of disk access operations required to execute this query against 
          912  +the virtual table. The SQLite core will often call xBestIndex 
          913  +multiple times with different constraints, obtain multiple cost
          914  +estimates, then choose the query plan that gives the lowest estimate.
          915  +
          916  +<p>If the current version of SQLite is 3.8.2 or greater, the estimatedRows
          917  +field may be set to an estimate of the number of rows returned by the
          918  +proposed query plan. If this value is not explicitly set, the default 
          919  +estimate of 25 rows is used.
          920  +
          921  +<p>If the current version of SQLite is 3.9.0 or greater, the idxFlags field
          922  +may be set to SQLITE_INDEX_SCAN_UNIQUE to indicate that the virtual table
          923  +will return only zero or one rows given the input constraints.  Additional
          924  +bits of the idxFlags field might be understood in later versions of SQLite.
          925  +
          926  +<p>The aConstraintUsage[] array contains one element for each of 
          927  +the nConstraint constraints in the inputs section of the 
          928  +<a href="c3ref/index_info.html">sqlite3_index_info</a> structure. 
          929  +The aConstraintUsage[] array is used by xBestIndex to tell the 
          930  +core how it is using the constraints.
          931  +
          932  +<p>The xBestIndex method may set aConstraintUsage[].argvIndex 
          933  +entries to values greater than zero. 
          934  +Exactly one entry should be set to 1, another to 2, another to 3, 
          935  +and so forth up to as many or as few as the xBestIndex method wants. 
          936  +The EXPR of the corresponding constraints will then be passed 
          937  +in as the argv[] parameters to xFilter.
          938  +
          939  +<p>For example, if the aConstraint[3].argvIndex is set to 1, then 
          940  +when xFilter is called, the argv[0] passed to xFilter will have 
          941  +the EXPR value of the aConstraint[3] constraint.
          942  +
          943  +<p>By default, the SQLite core double checks all constraints on 
          944  +each row of the virtual table that it receives. If such a check 
          945  +is redundant, the xBestFilter method can suppress that double-check by 
          946  +setting aConstraintUsage[].omit.
          947  +
          948  +<a name="xdisconnect"></a>
          949  +
          950  +<h3>2.4 The xDisconnect Method</h3>
          951  +
          952  +<blockquote><pre>
          953  +  int (*xDisconnect)(sqlite3_vtab *pVTab);
          954  +</pre></blockquote>
          955  +
          956  +<p>This method releases a connection to a virtual table. 
          957  +Only the <a href="c3ref/vtab.html">sqlite3_vtab</a> object is destroyed.
          958  +The virtual table is not destroyed and any backing store 
          959  +associated with the virtual table persists. 
          960  +
          961  +This method undoes the work of <a href="vtab.html#xconnect">xConnect</a>.
          962  +
          963  +<p>This method is a destructor for a connection to the virtual table.
          964  +Contrast this method with <a href="vtab.html#sqlite3_module.xDestroy">xDestroy</a>.  The xDestroy is a destructor
          965  +for the entire virtual table.
          966  +
          967  +<p>The xDisconnect method is required for every virtual table implementation,
          968  +though it is acceptable for the xDisconnect and <a href="vtab.html#sqlite3_module.xDestroy">xDestroy</a> methods to be
          969  +the same function if that makes sense for the particular virtual table.
          970  +
          971  +<a name="sqlite3_module.xDestroy"></a>
          972  +
          973  +<h3>2.5 The xDestroy Method</h3>
          974  +
          975  +<blockquote><pre>
          976  +  int (*xDestroy)(sqlite3_vtab *pVTab);
          977  +</pre></blockquote>
          978  +
          979  +<p>This method releases a connection to a virtual table, just like 
          980  +the <a href="vtab.html#xdisconnect">xDisconnect</a> method, and it also destroys the underlying 
          981  +table implementation. This method undoes the work of <a href="vtab.html#xcreate">xCreate</a>.
          982  +
          983  +<p>The <a href="vtab.html#xdisconnect">xDisconnect</a> method is called whenever a database connection
          984  +that uses a virtual table is closed. The xDestroy method is only 
          985  +called when a <a href="lang_droptable.html">DROP TABLE</a> statement is executed against the virtual table.
          986  +
          987  +<p>The xDestroy method is required for every virtual table implementation,
          988  +though it is acceptable for the <a href="vtab.html#xdisconnect">xDisconnect</a> and xDestroy methods to be
          989  +the same function if that makes sense for the particular virtual table.
          990  +
          991  +<a name="xopen"></a>
          992  +
          993  +<h3>2.6 The xOpen Method</h3>
          994  +
          995  +<blockquote><pre>
          996  +  int (*xOpen)(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor);
          997  +</pre></blockquote>
          998  +
          999  +<p>The xOpen method creates a new cursor used for accessing (read and/or
         1000  +writing) a virtual table.  A successful invocation of this method 
         1001  +will allocate the memory for the <a href="c3ref/vtab_cursor.html">sqlite3_vtab_cursor</a> (or a subclass),
         1002  +initialize the new object, and make *ppCursor point to the new object.
         1003  +The successful call then returns <a href="rescode.html#ok">SQLITE_OK</a>.
         1004  +
         1005  +<p>For every successful call to this method, the SQLite core will
         1006  +later invoke the <a href="vtab.html#xclose">xClose</a> method to destroy 
         1007  +the allocated cursor.
         1008  +
         1009  +<p>The xOpen method need not initialize the pVtab field of the
         1010  +<a href="c3ref/vtab_cursor.html">sqlite3_vtab_cursor</a> structure.  The SQLite core will take care
         1011  +of that chore automatically.
         1012  +
         1013  +<p>A virtual table implementation must be able to support an arbitrary
         1014  +number of simultaneously open cursors.
         1015  +
         1016  +<p>When initially opened, the cursor is in an undefined state.
         1017  +The SQLite core will invoke the <a href="vtab.html#xfilter">xFilter</a> method
         1018  +on the cursor prior to any attempt to position or read from the cursor.
         1019  +
         1020  +<p>The xOpen method is required for every virtual table implementation.
         1021  +
         1022  +<a name="xclose"></a>
         1023  +
         1024  +<h3>2.7 The xClose Method</h3>
         1025  +
         1026  +<blockquote><pre>
         1027  +  int (*xClose)(sqlite3_vtab_cursor*);
         1028  +</pre></blockquote>
         1029  +
         1030  +<p>The xClose method closes a cursor previously opened by 
         1031  +<a href="vtab.html#xopen">xOpen</a>. 
         1032  +The SQLite core will always call xClose once for each cursor opened 
         1033  +using xOpen.
         1034  +
         1035  +<p>This method must release all resources allocated by the
         1036  +corresponding xOpen call. The routine will not be called again even if it
         1037  +returns an error.  The SQLite core will not use the
         1038  +<a href="c3ref/vtab_cursor.html">sqlite3_vtab_cursor</a> again after it has been closed.
         1039  +
         1040  +<p>The xClose method is required for every virtual table implementation.
         1041  +
         1042  +<a name="xeof"></a>
         1043  +
         1044  +<h3>2.8 The xEof Method</h3>
         1045  +
         1046  +<blockquote><pre>
         1047  +  int (*xEof)(sqlite3_vtab_cursor*);
         1048  +</pre></blockquote>
         1049  +
         1050  +<p>The xEof method must return false (zero) if the specified cursor 
         1051  +currently points to a valid row of data, or true (non-zero) otherwise. 
         1052  +This method is called by the SQL engine immediately after each 
         1053  +<a href="vtab.html#xfilter">xFilter</a> and <a href="vtab.html#xnext">xNext</a> invocation.
         1054  +
         1055  +<p>The xEof method is required for every virtual table implementation.
         1056  +
         1057  +<a name="xfilter"></a>
         1058  +
         1059  +<h3>2.9 The xFilter Method</h3>
         1060  +
         1061  +<blockquote><pre>
         1062  +  int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr,
         1063  +                int argc, sqlite3_value **argv);
         1064  +</pre></blockquote>
         1065  +
         1066  +<p>This method begins a search of a virtual table. 
         1067  +The first argument is a cursor opened by <a href="vtab.html#xopen">xOpen</a>. 
         1068  +The next two arguments define a particular search index previously 
         1069  +chosen by <a href="vtab.html#xbestindex">xBestIndex</a>. The specific meanings of idxNum and idxStr 
         1070  +are unimportant as long as xFilter and xBestIndex agree on what 
         1071  +that meaning is.
         1072  +
         1073  +<p>The xBestIndex function may have requested the values of 
         1074  +certain expressions using the aConstraintUsage[].argvIndex values 
         1075  +of the <a href="c3ref/index_info.html">sqlite3_index_info</a> structure. 
         1076  +Those values are passed to xFilter using the argc and argv parameters.
         1077  +
         1078  +<p>If the virtual table contains one or more rows that match the
         1079  +search criteria, then the cursor must be left point at the first row.
         1080  +Subsequent calls to <a href="vtab.html#xeof">xEof</a> must return false (zero).
         1081  +If there are no rows match, then the cursor must be left in a state 
         1082  +that will cause the <a href="vtab.html#xeof">xEof</a> to return true (non-zero).
         1083  +The SQLite engine will use
         1084  +the <a href="vtab.html#xcolumn">xColumn</a> and <a href="vtab.html#xrowid">xRowid</a> methods to access that row content.
         1085  +The <a href="vtab.html#xnext">xNext</a> method will be used to advance to the next row.
         1086  +
         1087  +<p>This method must return <a href="rescode.html#ok">SQLITE_OK</a> if successful, or an sqlite 
         1088  +<a href="rescode.html">error code</a> if an error occurs.
         1089  +
         1090  +<p>The xFilter method is required for every virtual table implementation.
         1091  +
         1092  +<a name="xnext"></a>
         1093  +
         1094  +<h3>2.10 The xNext Method</h3>
         1095  +
         1096  +<blockquote><pre>
         1097  +  int (*xNext)(sqlite3_vtab_cursor*);
         1098  +</pre></blockquote>
         1099  +
         1100  +<p>The xNext method advances a <a href="c3ref/vtab_cursor.html">virtual table cursor</a>
         1101  +to the next row of a result set initiated by <a href="vtab.html#xfilter">xFilter</a>. 
         1102  +If the cursor is already pointing at the last row when this 
         1103  +routine is called, then the cursor no longer points to valid 
         1104  +data and a subsequent call to the <a href="vtab.html#xeof">xEof</a> method must return true (non-zero). 
         1105  +If the cursor is successfully advanced to another row of content, then
         1106  +subsequent calls to <a href="vtab.html#xeof">xEof</a> must return false (zero).
         1107  +
         1108  +<p>This method must return <a href="rescode.html#ok">SQLITE_OK</a> if successful, or an sqlite 
         1109  +<a href="rescode.html">error code</a> if an error occurs.
         1110  +
         1111  +<p>The xNext method is required for every virtual table implementation.
         1112  +
         1113  +<a name="xcolumn"></a>
         1114  +
         1115  +<h3>2.11 The xColumn Method</h3>
         1116  +
         1117  +<blockquote><pre>
         1118  +  int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int N);
         1119  +</pre></blockquote>
         1120  +
         1121  +<p>The SQLite core invokes this method in order to find the value for 
         1122  +the N-th column of the current row. N is zero-based so the first column 
         1123  +is numbered 0. 
         1124  +The xColumn method may return its result back to SQLite using one of the
         1125  +following interface:
         1126  +
         1127  +<p>
         1128  +<ul>
         1129  +<li> <a href="c3ref/result_blob.html">sqlite3_result_blob()</a>
         1130  +<li> <a href="c3ref/result_blob.html">sqlite3_result_double()</a>
         1131  +<li> <a href="c3ref/result_blob.html">sqlite3_result_int()</a>
         1132  +<li> <a href="c3ref/result_blob.html">sqlite3_result_int64()</a>
         1133  +<li> <a href="c3ref/result_blob.html">sqlite3_result_null()</a>
         1134  +<li> <a href="c3ref/result_blob.html">sqlite3_result_text()</a>
         1135  +<li> <a href="c3ref/result_blob.html">sqlite3_result_text16()</a>
         1136  +<li> <a href="c3ref/result_blob.html">sqlite3_result_text16le()</a>
         1137  +<li> <a href="c3ref/result_blob.html">sqlite3_result_text16be()</a>
         1138  +<li> <a href="c3ref/result_blob.html">sqlite3_result_zeroblob()</a>
         1139  +</ul>
         1140  +</p>
         1141  +
         1142  +<p>If the xColumn method implementation calls none of the functions above,
         1143  +then the value of the column defaults to an SQL NULL.
         1144  +
         1145  +<p>To raise an error, the xColumn method should use one of the result_text() 
         1146  +methods to set the error message text, then return an appropriate
         1147  +<a href="rescode.html">error code</a>.  The xColumn method must return <a href="rescode.html#ok">SQLITE_OK</a> on success.
         1148  +
         1149  +<p>The xColumn method is required for every virtual table implementation.
         1150  +
         1151  +<a name="xrowid"></a>
         1152  +
         1153  +<h3>2.12 The xRowid Method</h3>
         1154  +
         1155  +<blockquote><pre>
         1156  +  int (*xRowid)(sqlite3_vtab_cursor *pCur, sqlite_int64 *pRowid);
         1157  +</pre></blockquote>
         1158  +
         1159  +<p>A successful invocation of this method will cause *pRowid to be
         1160  +filled with the <a href="lang_createtable.html#rowid">rowid</a> of row that the
         1161  +<a href="c3ref/vtab_cursor.html">virtual table cursor</a> pCur is currently pointing at.
         1162  +This method returns <a href="rescode.html#ok">SQLITE_OK</a> on success.
         1163  +It returns an appropriate <a href="rescode.html">error code</a> on failure.</p>
         1164  +
         1165  +<p>The xRowid method is required for every virtual table implementation.
         1166  +
         1167  +<a name="xupdate"></a>
         1168  +
         1169  +<h3>2.13 The xUpdate Method</h3>
         1170  +
         1171  +<blockquote><pre>
         1172  +  int (*xUpdate)(
         1173  +    sqlite3_vtab *pVTab,
         1174  +    int argc,
         1175  +    sqlite3_value **argv,
         1176  +    sqlite_int64 *pRowid
         1177  +  );
         1178  +</pre></blockquote>
         1179  +
         1180  +<p>All changes to a virtual table are made using the xUpdate method.
         1181  +This one method can be used to insert, delete, or update.
         1182  +
         1183  +<p>The argc parameter specifies the number of entries in the argv array. 
         1184  +The value of argc will be 1 for a pure delete operation or N+2 for an insert
         1185  +or replace or update where N is the number of columns in the table.  
         1186  +In the previous sentence, N includes any hidden columns.
         1187  +
         1188  +<p>Every argv entry will have a non-NULL value in C but may contain the 
         1189  +SQL value NULL.  In other words, it is always true that
         1190  +<tt>argv&#91;i&#93;!=0</tt> for <b>i</b> between 0 and <tt>argc-1</tt>.
         1191  +However, it might be the case that
         1192  +<tt>sqlite3_value_type(argv&#91;i&#93;)==SQLITE_NULL</tt>.
         1193  +
         1194  +<p>The argv[0] parameter is the <a href="lang_createtable.html#rowid">rowid</a> of a row in the virtual table 
         1195  +to be deleted. If argv[0] is an SQL NULL, then no deletion occurs.
         1196  +
         1197  +<p>The argv[1] parameter is the rowid of a new row to be inserted 
         1198  +into the virtual table. If argv[1] is an SQL NULL, then the implementation 
         1199  +must choose a rowid for the newly inserted row. Subsequent argv[] 
         1200  +entries contain values of the columns of the virtual table, in the 
         1201  +order that the columns were declared. The number of columns will
         1202  +match the table declaration that the <a href="vtab.html#xconnect">xConnect</a> or <a href="vtab.html#xcreate">xCreate</a> method made 
         1203  +using the <a href="c3ref/declare_vtab.html">sqlite3_declare_vtab()</a> call.  All hidden columns are included.
         1204  +
         1205  +<p>When doing an insert without a rowid (argc>1, argv[1] is an SQL NULL), the 
         1206  +implementation must set *pRowid to the rowid of the newly inserted row; 
         1207  +this will become the value returned by the <a href="c3ref/last_insert_rowid.html">sqlite3_last_insert_rowid()</a>
         1208  +function. Setting this value in all the other cases is a harmless no-op;
         1209  +the SQLite engine ignores the *pRowid return value if argc==1 or 
         1210  +argv[1] is not an SQL NULL.
         1211  +
         1212  +<p>Each call to xUpdate will fall into one of cases shown below.
         1213  +Not that references to <b>argv&#91;i&#93</b> mean the SQL value
         1214  +held within the argv&#91;i&#93; object, not the argv&#91;i&#93;
         1215  +object itself.
         1216  +
         1217  +<blockquote>
         1218  +<dl>
         1219  +<dt><b>argc = 1</b>
         1220  +<dd><p>The single row with rowid equal to argv[0] is deleted. No insert occurs.
         1221  +
         1222  +<dt><b>argc > 1 <br> argv[0] = NULL</b>
         1223  +<dd><p>A new row is inserted with a rowid argv[1] and column values in
         1224  +       argv[2] and following.  If argv[1] is an SQL NULL,
         1225  +       the a new unique rowid is generated automatically.
         1226  +
         1227  +<dt><b>argc > 1 <br> argv[0] &ne; NULL <br> argv[0] = argv[1]</b>
         1228  +<dd><p>The row with rowid argv[0] is updated with new values 
         1229  +       in argv[2] and following parameters.
         1230  +
         1231  +<dt><b>argc > 1 <br> argv[0] &ne; NULL <br> argv[0] &ne; argv[1]</b>
         1232  +<dd><p> The row with rowid argv[0] is updated with rowid argv[1] 
         1233  +and new values in argv[2] and following parameters. This will occur 
         1234  +when an SQL statement updates a rowid, as in the statement:
         1235  +<blockquote>
         1236  +   <a href="lang_update.html">UPDATE</a> table SET rowid=rowid+1 WHERE ...; 
         1237  +</blockquote>
         1238  +</dl>
         1239  +</blockquote>
         1240  +
         1241  +<p>The xUpdate method must return <a href="rescode.html#ok">SQLITE_OK</a> if and only if it is
         1242  +successful.  If a failure occurs, the xUpdate must return an appropriate
         1243  +<a href="rescode.html">error code</a>.  On a failure, the pVTab->zErrMsg element may optionally
         1244  +be replaced with error message text stored in memory allocated from SQLite 
         1245  +using functions such as <a href="c3ref/mprintf.html">sqlite3_mprintf()</a> or <a href="c3ref/free.html">sqlite3_malloc()</a>.
         1246  +
         1247  +<p>If the xUpdate method violates some constraint of the virtual table
         1248  +(including, but not limited to, attempting to store a value of the wrong 
         1249  +datatype, attempting to store a value that is too
         1250  +large or too small, or attempting to change a read-only value) then the
         1251  +xUpdate must fail with an appropriate <a href="rescode.html">error code</a>.
         1252  +
         1253  +<p>There might be one or more <a href="c3ref/vtab_cursor.html">sqlite3_vtab_cursor</a> objects open and in use 
         1254  +on the virtual table instance and perhaps even on the row of the virtual
         1255  +table when the xUpdate method is invoked.  The implementation of
         1256  +xUpdate must be prepared for attempts to delete or modify rows of the table
         1257  +out from other existing cursors.  If the virtual table cannot accommodate
         1258  +such changes, the xUpdate method must return an <a href="rescode.html">error code</a>.
         1259  +
         1260  +<p>The xUpdate method is optional.
         1261  +If the xUpdate pointer in the <a href="c3ref/module.html">sqlite3_module</a> for a virtual table
         1262  +is a NULL pointer, then the virtual table is read-only.
         1263  +
         1264  +
         1265  +<a name="xfindfunction"></a>
         1266  +
         1267  +<h3>2.14 The xFindFunction Method</h3>
         1268  +
         1269  +<blockquote><pre>
         1270  +  int (*xFindFunction)(
         1271  +    sqlite3_vtab *pVtab,
         1272  +    int nArg,
         1273  +    const char *zName,
         1274  +    void (**pxFunc)(sqlite3_context*,int,sqlite3_value**),
         1275  +    void **ppArg
         1276  +  );
         1277  +</pre></blockquote>
         1278  +
         1279  +<p>This method is called during <a href="c3ref/prepare.html">sqlite3_prepare()</a> to give the virtual
         1280  +table implementation an opportunity to overload functions. 
         1281  +This method may be set to NULL in which case no overloading occurs.
         1282  +
         1283  +<p>When a function uses a column from a virtual table as its first 
         1284  +argument, this method is called to see if the virtual table would 
         1285  +like to overload the function. The first three parameters are inputs: 
         1286  +the virtual table, the number of arguments to the function, and the 
         1287  +name of the function. If no overloading is desired, this method
         1288  +returns 0. To overload the function, this method writes the new 
         1289  +function implementation into *pxFunc and writes user data into *ppArg 
         1290  +and returns 1.
         1291  +
         1292  +<p>Note that infix functions (<a href="lang_expr.html#like">LIKE</a>, <a href="lang_expr.html#glob">GLOB</a>, <a href="lang_expr.html#regexp">REGEXP</a>, and <a href="lang_expr.html#match">MATCH</a>) reverse 
         1293  +the order of their arguments. So "like(A,B)" is equivalent to "B like A". 
         1294  +For the form "B like A" the B term is considered the first argument 
         1295  +to the function. But for "like(A,B)" the A term is considered the 
         1296  +first argument.
         1297  +
         1298  +<p>The function pointer returned by this routine must be valid for
         1299  +the lifetime of the <a href="c3ref/vtab.html">sqlite3_vtab</a> object given in the first parameter.
         1300  +
         1301  +<a name="xBegin"></a>
         1302  +
         1303  +<h3>2.15 The xBegin Method</h3>
         1304  +
         1305  +<blockquote><pre>
         1306  +  int (*xBegin)(sqlite3_vtab *pVTab);
         1307  +</pre></blockquote>
         1308  +
         1309  +<p>This method begins a transaction on a virtual table.
         1310  +This is method is optional.  The xBegin pointer of <a href="c3ref/module.html">sqlite3_module</a>
         1311  +may be NULL.
         1312  +
         1313  +<p>This method is always followed by one call to either the
         1314  +<a href="vtab.html#xcommit">xCommit</a> or <a href="vtab.html#xrollback">xRollback</a> method.  Virtual table transactions do
         1315  +not nest, so the xBegin method will not be invoked more than once
         1316  +on a single virtual table
         1317  +without an intervening call to either <a href="vtab.html#xcommit">xCommit</a> or <a href="vtab.html#xrollback">xRollback</a>.
         1318  +Multiple calls to other methods can and likely will occur in between
         1319  +the xBegin and the corresponding <a href="vtab.html#xcommit">xCommit</a> or <a href="vtab.html#xrollback">xRollback</a>.
         1320  +
         1321  +<a name="xsync"></a>
         1322  +
         1323  +<h3>2.16 The xSync Method</h3>
         1324  +
         1325  +<blockquote><pre>
         1326  +  int (*xSync)(sqlite3_vtab *pVTab);
         1327  +</pre></blockquote>
         1328  +
         1329  +
         1330  +<p>This method signals the start of a two-phase commit on a virtual
         1331  +table.
         1332  +This is method is optional.  The xSync pointer of <a href="c3ref/module.html">sqlite3_module</a>
         1333  +may be NULL.
         1334  +
         1335  +<p>This method is only invoked after call to the <a href="vtab.html#xBegin">xBegin</a> method and
         1336  +prior to an <a href="vtab.html#xcommit">xCommit</a> or <a href="vtab.html#xrollback">xRollback</a>.  In order to implement two-phase
         1337  +commit, the xSync method on all virtual tables is invoked prior to
         1338  +invoking the <a href="vtab.html#xcommit">xCommit</a> method on any virtual table.  If any of the 
         1339  +xSync methods fail, the entire transaction is rolled back.
         1340  +
         1341  +<a name="xcommit"></a>
         1342  +
         1343  +<h3>2.17 The xCommit Method</h3>
         1344  +
         1345  +<blockquote><pre>
         1346  +  int (*xCommit)(sqlite3_vtab *pVTab);
         1347  +</pre></blockquote>
         1348  +
         1349  +<p>This method causes a virtual table transaction to commit.
         1350  +This is method is optional.  The xCommit pointer of <a href="c3ref/module.html">sqlite3_module</a>
         1351  +may be NULL.
         1352  +
         1353  +<p>A call to this method always follows a prior call to <a href="vtab.html#xBegin">xBegin</a> and
         1354  +<a href="vtab.html#xsync">xSync</a>.
         1355  +
         1356  +
         1357  +<a name="xrollback"></a>
         1358  +
         1359  +<h3>2.18 The xRollback Method</h3>
         1360  +
         1361  +<blockquote><pre>
         1362  +  int (*xRollback)(sqlite3_vtab *pVTab);
         1363  +</pre></blockquote>
         1364  +
         1365  +<p>This method causes a virtual table transaction to rollback.
         1366  +This is method is optional.  The xRollback pointer of <a href="c3ref/module.html">sqlite3_module</a>
         1367  +may be NULL.
         1368  +
         1369  +<p>A call to this method always follows a prior call to <a href="vtab.html#xBegin">xBegin</a>.
         1370  +
         1371  +
         1372  +<a name="xrename"></a>
         1373  +
         1374  +<h3>2.19 The xRename Method</h3>
         1375  +
         1376  +<blockquote><pre>
         1377  +  int (*xRename)(sqlite3_vtab *pVtab, const char *zNew);
         1378  +</pre></blockquote>
         1379  +
         1380  +<p>This method provides notification that the virtual table implementation
         1381  +that the virtual table will be given a new name. 
         1382  +If this method returns <a href="rescode.html#ok">SQLITE_OK</a> then SQLite renames the table.
         1383  +If this method returns an <a href="rescode.html">error code</a> then the renaming is prevented.
         1384  +
         1385  +<p>The xRename method is required for every virtual table implementation.
         1386  +
         1387  +<a name="xsavepoint"></a>
         1388  +
         1389  +<h3>2.20 The xSavepoint, xRelease, and xRollbackTo Methods</h3>
         1390  +
         1391  +<blockquote><pre>
         1392  +  int (*xSavepoint)(sqlite3_vtab *pVtab, int);
         1393  +  int (*xRelease)(sqlite3_vtab *pVtab, int);
         1394  +  int (*xRollbackTo)(sqlite3_vtab *pVtab, int);
         1395  +</pre></blockquote>
         1396  +
         1397  +<p>
         1398  +These methods provide the virtual table implementation an opportunity to
         1399  +implement nested transactions.  They are always optional and will only be
         1400  +called in SQLite <a href="releaselog/3_7_7.html">version 3.7.7</a> and later.
         1401  +</p>
         1402  +
         1403  +<p>
         1404  +When xSavepoint(X,N) is invoked, that is a signal to the virtual table X
         1405  +that it should save its current state as savepoint N.  
         1406  +A subsequent call
         1407  +to xRollbackTo(X,R) means that the state of the virtual table should return
         1408  +to what it was when xSavepoint(X,R) was last called.  
         1409  +The call
         1410  +to xRollbackTo(X,R) will invalidate all savepoints with N>R; none of the
         1411  +invalided savepoints will be rolled back or released without first
         1412  +being reinitialized by a call to xSavepoint().  
         1413  +A call to xRelease(X,M) invalidates all savepoints where N>=M.
         1414  +</p>
         1415  +
         1416  +<p>
         1417  +None of the xSavepoint(), xRelease(), or xRollbackTo() methods will ever
         1418  +be called except in between calls to xBegin() and 
         1419  +either xCommit() or xRollback().
         1420  +</p>
         1421  +

Added Doc/vtab.tcl.

            1  +###############################################################################
            2  +#
            3  +# vtab.tcl --
            4  +#
            5  +# Written by Joe Mistachkin.
            6  +# Released to the public domain, use at your own risk!
            7  +#
            8  +###############################################################################
            9  +
           10  +proc readFile { fileName } {
           11  +  set file_id [open $fileName RDONLY]
           12  +  fconfigure $file_id -encoding binary -translation binary
           13  +  set result [read $file_id]
           14  +  close $file_id
           15  +  return $result
           16  +}
           17  +
           18  +proc writeFile { fileName data } {
           19  +  set file_id [open $fileName {WRONLY CREAT TRUNC}]
           20  +  fconfigure $file_id -encoding binary -translation binary
           21  +  puts -nonewline $file_id $data
           22  +  close $file_id
           23  +  return ""
           24  +}
           25  +
           26  +proc englishToList { value } {
           27  +  set result [list]
           28  +
           29  +  foreach element [split $value "\t\n ,"] {
           30  +    if {[string tolower $element] ni [list "" and or]} then {
           31  +      lappend result $element
           32  +    }
           33  +  }
           34  +
           35  +  return $result
           36  +}
           37  +
           38  +proc processLine { line } {
           39  +  if {[string length [string trim $line]] == 0 || \
           40  +      [regexp -- {<h\d(?: |>)} [string range $line 0 3]]} then {
           41  +    return ""
           42  +  }
           43  +
           44  +  set result $line
           45  +
           46  +  foreach remove [list \
           47  +      {<a name=".*?">} {<a href=".*?">} {</a>} {<b>} {</b>} \
           48  +      {<dd>} {</dd>} {<dl>} {</dl>} {<dt>} {</dt>} {<li>} \
           49  +      {</li>} {<ol>} {</ol>} {<p>} {</p>} {<ul>} {</ul>}] {
           50  +    regsub -all -- $remove $result "" result
           51  +
           52  +    if {[string length [string trim $result]] == 0} then {
           53  +      puts "STOP with no content, original line = $line"
           54  +      return ""
           55  +    }
           56  +  }
           57  +
           58  +  regsub -all -- {<br>} $result \n result
           59  +  regsub -all -- {&ne;} $result {\&#8800;} result
           60  +  regsub -all -- {&#91(?:;)?} $result {[} result
           61  +  regsub -all -- {&#93(?:;)?} $result {]} result
           62  +  regsub -all -- {<( |\"|\d|=)} $result {\&lt;\1} result
           63  +  regsub -all -- {( |\"|\d|=)>} $result {\1\&gt;} result
           64  +  regsub -all -- {<blockquote><pre>} $result <code> result
           65  +  regsub -all -- {</pre></blockquote>} $result </code> result
           66  +  regsub -all -- {<blockquote>} $result <code> result
           67  +  regsub -all -- {</blockquote>} $result </code> result
           68  +
           69  +  return $result
           70  +}
           71  +
           72  +proc extractMethod { name lines pattern indexVarName methodsVarName } {
           73  +  upvar 1 $indexVarName index
           74  +  upvar 1 $methodsVarName methods
           75  +
           76  +  set paragraph 0
           77  +  set length [llength $lines]
           78  +
           79  +  while {$index < $length} {
           80  +    set line [lindex $lines $index]
           81  +
           82  +    if {[regexp -- $pattern $line]} then {
           83  +      break; # stop on this line for outer loop.
           84  +    } else {
           85  +      set trimLine [string trim $line]; set data ""
           86  +
           87  +      if {$paragraph > 0 && [string length $trimLine] == 0} then {
           88  +        # blank line, close paragraph.
           89  +        append data \n </para>; incr paragraph -1
           90  +      } elseif {[string range $trimLine 0 2] eq "<p>"} then {
           91  +        # open paragraph ... maybe one line?
           92  +        if {[string range $trimLine end-3 end] eq "</p>"} then {
           93  +          set newLine [processLine $line]
           94  +
           95  +          if {[string length $newLine] > 0} then {
           96  +            # one line paragraph, wrap.
           97  +            if {[info exists methods($name)]} then {
           98  +              append data \n
           99  +            }
          100  +
          101  +            append data <para> \n $newLine \n </para>
          102  +          }
          103  +        } else {
          104  +          if {[info exists methods($name)]} then {
          105  +            append data \n
          106  +          }
          107  +
          108  +          append data <para>
          109  +
          110  +          set newLine [processLine $line]
          111  +
          112  +          if {[string length $newLine] > 0} then {
          113  +            # open paragraph, add line to it.
          114  +            append data $newLine
          115  +          }
          116  +
          117  +          incr paragraph
          118  +        }
          119  +      } else {
          120  +        set newLine [processLine $line]
          121  +
          122  +        if {[string length $newLine] > 0} then {
          123  +          if {[info exists methods($name)]} then {
          124  +            append data \n
          125  +          }
          126  +
          127  +          append data $newLine
          128  +        }
          129  +      }
          130  +
          131  +      if {[string length $data] > 0} then {
          132  +        append methods($name) $data
          133  +      }
          134  +
          135  +      incr index; # consume this line for outer loop.
          136  +    }
          137  +  }
          138  +}
          139  +
          140  +#
          141  +# NOTE: This is the entry point for this script.
          142  +#
          143  +set path [file normalize [file dirname [info script]]]
          144  +
          145  +set coreDocPath [file join $path Extra Core]
          146  +set interfacePath [file join [file dirname $path] System.Data.SQLite]
          147  +
          148  +set inputFileName [file join $coreDocPath vtab.html]
          149  +set outputFileName [file join $interfacePath ISQLiteNativeModule.cs]
          150  +
          151  +set methodPattern {^<h3>2\.\d+ The (.*) Method(?:s)?</h3>$}
          152  +set start false
          153  +array set methods {}
          154  +set lines [split [readFile $inputFileName] \n]
          155  +set length [llength $lines]
          156  +
          157  +for {set index 0} {$index < $length} {} {
          158  +  set line [lindex $lines $index]
          159  +
          160  +  if {$start} then {
          161  +    if {[regexp -- {^<a name=".*"></a>$} $line]} then {
          162  +      incr index; continue
          163  +    }
          164  +
          165  +    if {[regexp -- $methodPattern $line dummy capture]} then {
          166  +      foreach method [englishToList $capture] {
          167  +        set methodIndex [expr {$index + 1}]
          168  +        extractMethod $method $lines $methodPattern methodIndex methods
          169  +      }
          170  +
          171  +      set index $methodIndex
          172  +    } else {
          173  +      incr index
          174  +    }
          175  +  } elseif {[regexp -- {^<h2>2\.0 Virtual Table Methods</h2>$} $line]} then {
          176  +    set start true; incr index
          177  +  } else {
          178  +    incr index
          179  +  }
          180  +}
          181  +
          182  +# "        /// <summary>"
          183  +# "        /// </summary>"
          184  +# exit 0