/******************************************************** * ADO.NET 2.0 Data Provider for SQLite Version 3.X * Written by Joe Mistachkin (joe@mistachkin.com) * * Released to the public domain, use at your own risk! ********************************************************/ namespace System.Data.SQLite { #region ISQLiteNativeModule Interface /// /// This interface represents a virtual table implementation written in /// native code. /// public interface ISQLiteNativeModule { /// /// /// int (*xCreate)(sqlite3 *db, void *pAux, /// int argc, char **argv, /// sqlite3_vtab **ppVTab, /// char **pzErr); /// /// /// The xCreate method is called to create a new instance of a virtual table /// in response to a CREATE VIRTUAL TABLE statement. /// If the xCreate method is the same pointer as the xConnect method, then the /// virtual table is an eponymous virtual table. /// If the xCreate method is omitted (if it is a NULL pointer) then the virtual /// table is an eponymous-only virtual table. /// /// /// The db parameter is a pointer to the SQLite database connection that /// is executing the CREATE VIRTUAL TABLE statement. /// The pAux argument is the copy of the client data pointer that was the /// fourth argument to the sqlite3_create_module() or /// sqlite3_create_module_v2() call that registered the /// virtual table module. /// The argv parameter is an array of argc pointers to null terminated strings. /// The first string, argv[0], is the name of the module being invoked. The /// module name is the name provided as the second argument to /// sqlite3_create_module() and as the argument to the USING clause of the /// CREATE VIRTUAL TABLE statement that is running. /// 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 /// "temp" for TEMP database, or the name given at the end of the ATTACH /// statement for attached databases. The third element of the array, argv[2], /// is the name of the new virtual table, as specified following the TABLE /// keyword in the CREATE VIRTUAL TABLE statement. /// If present, the fourth and subsequent strings in the argv[] array report /// the arguments to the module name in the CREATE VIRTUAL TABLE statement. /// /// /// The job of this method is to construct the new virtual table object /// (an sqlite3_vtab object) and return a pointer to it in *ppVTab. /// /// /// As part of the task of creating a new sqlite3_vtab structure, this /// method must invoke sqlite3_declare_vtab() to tell the SQLite /// core about the columns and datatypes in the virtual table. /// The sqlite3_declare_vtab() API has the following prototype: /// /// /// int sqlite3_declare_vtab(sqlite3 *db, const char *zCreateTable) /// /// /// The first argument to sqlite3_declare_vtab() must be the same /// database connection pointer as the first parameter to this method. /// The second argument to sqlite3_declare_vtab() must a zero-terminated /// UTF-8 string that contains a well-formed CREATE TABLE statement that /// defines the columns in the virtual table and their data types. /// The name of the table in this CREATE TABLE statement is ignored, /// as are all constraints. Only the column names and datatypes matter. /// The CREATE TABLE statement string need not to be /// held in persistent memory. The string can be /// deallocated and/or reused as soon as the sqlite3_declare_vtab() /// routine returns. /// /// /// The xCreate method need not initialize the pModule, nRef, and zErrMsg /// fields of the sqlite3_vtab object. The SQLite core will take care of /// that chore. /// /// /// The xCreate should return SQLITE_OK if it is successful in /// creating the new virtual table, or SQLITE_ERROR if it is not successful. /// If not successful, the sqlite3_vtab structure must not be allocated. /// An error message may optionally be returned in *pzErr if unsuccessful. /// Space to hold the error message string must be allocated using /// an SQLite memory allocation function like /// sqlite3_malloc() or sqlite3_mprintf() as the SQLite core will /// attempt to free the space using sqlite3_free() after the error has /// been reported up to the application. /// /// /// If the xCreate method is omitted (left as a NULL pointer) then the /// virtual table is an eponymous-only virtual table. New instances of /// the virtual table cannot be created using CREATE VIRTUAL TABLE and the /// virtual table can only be used via its module name. /// Note that SQLite versions prior to 3.9.0 (2015-10-14) do not understand /// eponymous-only virtual tables and will segfault if an attempt is made /// to CREATE VIRTUAL TABLE on an eponymous-only virtual table because /// the xCreate method was not checked for null. /// /// /// If the xCreate method is the exact same pointer as the xConnect method, /// that indicates that the virtual table does not need to initialize backing /// store. Such a virtual table can be used as an eponymous virtual table /// or as a named virtual table using CREATE VIRTUAL TABLE or both. /// /// /// If a column datatype contains the special keyword "HIDDEN" /// (in any combination of upper and lower case letters) then that keyword /// it is omitted from the column datatype name and the column is marked /// as a hidden column internally. /// A hidden column differs from a normal column in three respects: /// /// /// ]]> /// ]]> Hidden columns are not listed in the dataset returned by /// "PRAGMA table_info", /// ]]>]]> Hidden columns are not included in the expansion of a "*" /// expression in the result set of a SELECT, and /// ]]>]]> Hidden columns are not included in the implicit column-list /// used by an INSERT statement that lacks an explicit column-list. /// ]]>]]> /// /// /// For example, if the following SQL is passed to sqlite3_declare_vtab(): /// /// /// CREATE TABLE x(a HIDDEN VARCHAR(12), b INTEGER, c INTEGER Hidden); /// /// /// Then the virtual table would be created with two hidden columns, /// and with datatypes of "VARCHAR(12)" and "INTEGER". /// /// /// An example use of hidden columns can be seen in the FTS3 virtual /// table implementation, where every FTS virtual table /// contains an FTS hidden column that is used to pass information from the /// virtual table into FTS auxiliary functions and to the FTS MATCH operator. /// /// /// A virtual table that contains hidden columns can be used like /// a table-valued function in the FROM clause of a SELECT statement. /// The arguments to the table-valued function become constraints on /// the HIDDEN columns of the virtual table. /// /// /// For example, the "generate_series" extension (located in the /// ext/misc/series.c /// file in the source tree) /// implements an eponymous virtual table with the following schema: /// /// /// CREATE TABLE generate_series( /// value, /// start HIDDEN, /// stop HIDDEN, /// step HIDDEN /// ); /// /// /// The sqlite3_module.xBestIndex method in the implementation of this /// table checks for equality constraints against the HIDDEN columns, and uses /// those as input parameters to determine the range of integer "value" outputs /// to generate. Reasonable defaults are used for any unconstrained columns. /// For example, to list all integers between 5 and 50: /// /// /// SELECT value FROM generate_series(5,50); /// /// /// The previous query is equivalent to the following: /// /// /// SELECT value FROM generate_series WHERE start=5 AND stop=50; /// /// /// Arguments on the virtual table name are matched to hidden columns /// in order. The number of arguments can be less than the /// number of hidden columns, in which case the latter hidden columns are /// unconstrained. However, an error results if there are more arguments /// than there are hidden columns in the virtual table. /// /// /// Beginning with SQLite version 3.14.0 (2016-08-08), /// the CREATE TABLE statement that /// is passed into sqlite3_declare_vtab() may contain a WITHOUT ROWID clause. /// This is useful for cases where the virtual table rows /// cannot easily be mapped into unique integers. A CREATE TABLE /// statement that includes WITHOUT ROWID must define one or more columns as /// the PRIMARY KEY. Every column of the PRIMARY KEY must individually be /// NOT NULL and all columns for each row must be collectively unique. /// /// /// Note that SQLite does not enforce the PRIMARY KEY for a WITHOUT ROWID /// virtual table. Enforcement is the responsibility of the underlying /// virtual table implementation. But SQLite does assume that the PRIMARY KEY /// constraint is valid - that the identified columns really are UNIQUE and /// NOT NULL - and it uses that assumption to optimize queries against the /// virtual table. /// /// /// The rowid column is not accessible on a /// WITHOUT ROWID virtual table (of course). /// /// /// The xUpdate method was originally designed around having a /// ROWID as a single value. The xUpdate method has been expanded to /// accommodate an arbitrary PRIMARY KEY in place of the ROWID, but the /// PRIMARY KEY must still be only one column. For this reason, SQLite /// will reject any WITHOUT ROWID virtual table that has more than one /// PRIMARY KEY column and a non-NULL xUpdate method. /// /// /// /// The native database connection handle. /// /// /// The original native pointer value that was provided to the /// sqlite3_create_module(), sqlite3_create_module_v2() or /// sqlite3_create_disposable_module() functions. /// /// /// The number of arguments from the CREATE VIRTUAL TABLE statement. /// /// /// The array of string arguments from the CREATE VIRTUAL TABLE /// statement. /// /// /// Upon success, this parameter must be modified to point to the newly /// created native sqlite3_vtab derived structure. /// /// /// Upon failure, this parameter must be modified to point to the error /// message, with the underlying memory having been obtained from the /// sqlite3_malloc() function. /// /// /// A standard SQLite return code. /// SQLiteErrorCode xCreate( IntPtr pDb, IntPtr pAux, int argc, IntPtr argv, ref IntPtr pVtab, ref IntPtr pError ); /////////////////////////////////////////////////////////////////////// /// /// /// int (*xConnect)(sqlite3*, void *pAux, /// int argc, char **argv, /// sqlite3_vtab **ppVTab, /// char **pzErr); /// /// /// The xConnect method is very similar to xCreate. /// It has the same parameters and constructs a new sqlite3_vtab structure /// just like xCreate. /// And it must also call sqlite3_declare_vtab() like xCreate. /// /// /// The difference is that xConnect is called to establish a new /// connection to an existing virtual table whereas xCreate is called /// to create a new virtual table from scratch. /// /// /// The xCreate and xConnect methods are only different when the /// virtual table has some kind of backing store that must be initialized /// the first time the virtual table is created. The xCreate method creates /// and initializes the backing store. The xConnect method just connects /// to an existing backing store. When xCreate and xConnect are the same, /// the table is an eponymous virtual table. /// /// /// As an example, consider a virtual table implementation that /// provides read-only access to existing comma-separated-value (CSV) /// files on disk. There is no backing store that needs to be created /// or initialized for such a virtual table (since the CSV files already /// exist on disk) so the xCreate and xConnect methods will be identical /// for that module. /// /// /// Another example is a virtual table that implements a full-text index. /// The xCreate method must create and initialize data structures to hold /// the dictionary and posting lists for that index. The xConnect method, /// on the other hand, only has to locate and use an existing dictionary /// and posting lists that were created by a prior xCreate call. /// /// /// The xConnect method must return SQLITE_OK if it is successful /// in creating the new virtual table, or SQLITE_ERROR if it is not /// successful. If not successful, the sqlite3_vtab structure must not be /// allocated. An error message may optionally be returned in *pzErr if /// unsuccessful. /// Space to hold the error message string must be allocated using /// an SQLite memory allocation function like /// sqlite3_malloc() or sqlite3_mprintf() as the SQLite core will /// attempt to free the space using sqlite3_free() after the error has /// been reported up to the application. /// /// /// The xConnect method is required for every virtual table implementation, /// though the xCreate and xConnect pointers of the sqlite3_module object /// may point to the same function if the virtual table does not need to /// initialize backing store. /// /// /// /// The native database connection handle. /// /// /// The original native pointer value that was provided to the /// sqlite3_create_module(), sqlite3_create_module_v2() or /// sqlite3_create_disposable_module() functions. /// /// /// The number of arguments from the CREATE VIRTUAL TABLE statement. /// /// /// The array of string arguments from the CREATE VIRTUAL TABLE /// statement. /// /// /// Upon success, this parameter must be modified to point to the newly /// created native sqlite3_vtab derived structure. /// /// /// Upon failure, this parameter must be modified to point to the error /// message, with the underlying memory having been obtained from the /// sqlite3_malloc() function. /// /// /// A standard SQLite return code. /// SQLiteErrorCode xConnect( IntPtr pDb, IntPtr pAux, int argc, IntPtr argv, ref IntPtr pVtab, ref IntPtr pError ); /////////////////////////////////////////////////////////////////////// /// /// /// SQLite uses the xBestIndex method of a virtual table module to determine /// the best way to access the virtual table. /// The xBestIndex method has a prototype like this: /// /// /// int (*xBestIndex)(sqlite3_vtab *pVTab, sqlite3_index_info*); /// /// /// The SQLite core communicates with the xBestIndex method by filling /// in certain fields of the sqlite3_index_info structure and passing a /// pointer to that structure into xBestIndex as the second parameter. /// The xBestIndex method fills out other fields of this structure which /// forms the reply. The sqlite3_index_info structure looks like this: /// /// /// struct sqlite3_index_info { /// /* Inputs */ /// const int nConstraint; /* Number of entries in aConstraint */ /// const struct sqlite3_index_constraint { /// int iColumn; /* Column constrained. -1 for ROWID */ /// unsigned char op; /* Constraint operator */ /// unsigned char usable; /* True if this constraint is usable */ /// int iTermOffset; /* Used internally - xBestIndex should ignore */ /// } *const aConstraint; /* Table of WHERE clause constraints */ /// const int nOrderBy; /* Number of terms in the ORDER BY clause */ /// const struct sqlite3_index_orderby { /// int iColumn; /* Column number */ /// unsigned char desc; /* True for DESC. False for ASC. */ /// } *const aOrderBy; /* The ORDER BY clause */ /// /* Outputs */ /// struct sqlite3_index_constraint_usage { /// int argvIndex; /* if >0, constraint is part of argv to xFilter */ /// unsigned char omit; /* Do not code a test for this constraint */ /// } *const aConstraintUsage; /// int idxNum; /* Number used to identify the index */ /// char *idxStr; /* String, possibly obtained from sqlite3_malloc */ /// int needToFreeIdxStr; /* Free idxStr using sqlite3_free() if true */ /// int orderByConsumed; /* True if output is already ordered */ /// double estimatedCost; /* Estimated cost of using this index */ /// ]]>/* Fields below are only available in SQLite 3.8.2 and later */]]> /// sqlite3_int64 estimatedRows; /* Estimated number of rows returned */ /// ]]>/* Fields below are only available in SQLite 3.9.0 and later */]]> /// int idxFlags; /* Mask of SQLITE_INDEX_SCAN_* flags */ /// ]]>/* Fields below are only available in SQLite 3.10.0 and later */]]> /// sqlite3_uint64 colUsed; /* Input: Mask of columns used by statement */ /// }; /// /// /// Note the warnings on the "estimatedRows", "idxFlags", and colUsed fields. /// These fields were added with SQLite versions 3.8.2, 3.9.0, and 3.10.0, respectively. /// Any extension that reads or writes these fields must first check that the /// version of the SQLite library in use is greater than or equal to appropriate /// version - perhaps comparing the value returned from sqlite3_libversion_number() /// against constants 3008002, 3009000, and/or 3010000. The result of attempting /// to access these fields in an sqlite3_index_info structure created by an /// older version of SQLite are undefined. /// /// /// In addition, there are some defined constants: /// /// /// #define SQLITE_INDEX_CONSTRAINT_EQ 2 /// #define SQLITE_INDEX_CONSTRAINT_GT 4 /// #define SQLITE_INDEX_CONSTRAINT_LE 8 /// #define SQLITE_INDEX_CONSTRAINT_LT 16 /// #define SQLITE_INDEX_CONSTRAINT_GE 32 /// #define SQLITE_INDEX_CONSTRAINT_MATCH 64 /// #define SQLITE_INDEX_CONSTRAINT_LIKE 65 /* 3.10.0 and later */ /// #define SQLITE_INDEX_CONSTRAINT_GLOB 66 /* 3.10.0 and later */ /// #define SQLITE_INDEX_CONSTRAINT_REGEXP 67 /* 3.10.0 and later */ /// #define SQLITE_INDEX_CONSTRAINT_NE 68 /* 3.21.0 and later */ /// #define SQLITE_INDEX_CONSTRAINT_ISNOT 69 /* 3.21.0 and later */ /// #define SQLITE_INDEX_CONSTRAINT_ISNOTNULL 70 /* 3.21.0 and later */ /// #define SQLITE_INDEX_CONSTRAINT_ISNULL 71 /* 3.21.0 and later */ /// #define SQLITE_INDEX_CONSTRAINT_IS 72 /* 3.21.0 and later */ /// #define SQLITE_INDEX_SCAN_UNIQUE 1 /* Scan visits at most 1 row */ /// /// /// The SQLite core calls the xBestIndex method when it is compiling a query /// that involves a virtual table. In other words, SQLite calls this method /// when it is running sqlite3_prepare() or the equivalent. /// By calling this method, the /// SQLite core is saying to the virtual table that it needs to access /// some subset of the rows in the virtual table and it wants to know the /// most efficient way to do that access. The xBestIndex method replies /// with information that the SQLite core can then use to conduct an /// efficient search of the virtual table. /// /// /// While compiling a single SQL query, the SQLite core might call /// xBestIndex multiple times with different settings in sqlite3_index_info. /// The SQLite core will then select the combination that appears to /// give the best performance. /// /// /// Before calling this method, the SQLite core initializes an instance /// of the sqlite3_index_info structure with information about the /// query that it is currently trying to process. This information /// derives mainly from the WHERE clause and ORDER BY or GROUP BY clauses /// of the query, but also from any ON or USING clauses if the query is a /// join. The information that the SQLite core provides to the xBestIndex /// method is held in the part of the structure that is marked as "Inputs". /// The "Outputs" section is initialized to zero. /// /// /// The information in the sqlite3_index_info structure is ephemeral /// and may be overwritten or deallocated as soon as the xBestIndex method /// returns. If the xBestIndex method needs to remember any part of the /// sqlite3_index_info structure, it should make a copy. Care must be /// take to store the copy in a place where it will be deallocated, such /// as in the idxStr field with needToFreeIdxStr set to 1. /// /// /// Note that xBestIndex will always be called before xFilter, since /// the idxNum and idxStr outputs from xBestIndex are required inputs to /// xFilter. However, there is no guarantee that xFilter will be called /// following a successful xBestIndex. /// /// /// The xBestIndex method is required for every virtual table implementation. /// /// /// The main thing that the SQLite core is trying to communicate to /// the virtual table is the constraints that are available to limit /// the number of rows that need to be searched. The aConstraint[] array /// contains one entry for each constraint. There will be exactly /// nConstraint entries in that array. /// /// /// Each constraint will correspond to a term in the WHERE clause /// or in a USING or ON clause that is of the form /// /// /// column OP EXPR /// /// /// Where "column" is a column in the virtual table, OP is an operator /// like "=" or "<", and EXPR is an arbitrary expression. So, for example, /// if the WHERE clause contained a term like this: /// /// /// a = 5 /// /// /// Then one of the constraints would be on the "a" column with /// operator "=" and an expression of "5". Constraints need not have a /// literal representation of the WHERE clause. The query optimizer might /// make transformations to the /// WHERE clause in order to extract as many constraints /// as it can. So, for example, if the WHERE clause contained something /// like this: /// /// /// x BETWEEN 10 AND 100 AND 999>y /// /// /// The query optimizer might translate this into three separate constraints: /// /// /// x >= 10 /// x <= 100 /// y < 999 /// /// /// For each constraint, the aConstraint[].iColumn field indicates which /// column appears on the left-hand side of the constraint. /// The first column of the virtual table is column 0. /// The rowid of the virtual table is column -1. /// The aConstraint[].op field indicates which operator is used. /// The SQLITE_INDEX_CONSTRAINT_* constants map integer constants /// into operator values. /// Columns occur in the order they were defined by the call to /// sqlite3_declare_vtab() in the xCreate or xConnect method. /// Hidden columns are counted when determining the column index. /// /// /// The aConstraint[] array contains information about all constraints /// that apply to the virtual table. But some of the constraints might /// not be usable because of the way tables are ordered in a join. /// The xBestIndex method must therefore only consider constraints /// that have an aConstraint[].usable flag which is true. /// /// /// In addition to WHERE clause constraints, the SQLite core also /// tells the xBestIndex method about the ORDER BY clause. /// (In an aggregate query, the SQLite core might put in GROUP BY clause /// information in place of the ORDER BY clause information, but this fact /// should not make any difference to the xBestIndex method.) /// If all terms of the ORDER BY clause are columns in the virtual table, /// then nOrderBy will be the number of terms in the ORDER BY clause /// and the aOrderBy[] array will identify the column for each term /// in the order by clause and whether or not that column is ASC or DESC. /// /// /// In SQLite version 3.10.0 (2016-01-06) and later, /// the colUsed field is available /// to indicate which fields of the virtual table are actually used by the /// statement being prepared. If the lowest bit of colUsed is set, that /// means that the first column is used. The second lowest bit corresponds /// to the second column. And so forth. If the most significant bit of /// colUsed is set, that means that one or more columns other than the /// first 63 columns are used. If column usage information is needed by the /// xFilter method, then the required bits must be encoded into either /// the idxNum or idxStr output fields. /// /// /// Given all of the information above, the job of the xBestIndex /// method it to figure out the best way to search the virtual table. /// /// /// The xBestIndex method fills the idxNum and idxStr fields with /// information that communicates an indexing strategy to the xFilter /// method. The information in idxNum and idxStr is arbitrary as far /// as the SQLite core is concerned. The SQLite core just copies the /// information through to the xFilter method. Any desired meaning can /// be assigned to idxNum and idxStr as long as xBestIndex and xFilter /// agree on what that meaning is. /// /// /// The idxStr value may be a string obtained from an SQLite /// memory allocation function such as sqlite3_mprintf(). /// If this is the case, then the needToFreeIdxStr flag must be set to /// true so that the SQLite core will know to call sqlite3_free() on /// that string when it has finished with it, and thus avoid a memory leak. /// /// /// If the virtual table will output rows in the order specified by /// the ORDER BY clause, then the orderByConsumed flag may be set to /// true. If the output is not automatically in the correct order /// then orderByConsumed must be left in its default false setting. /// This will indicate to the SQLite core that it will need to do a /// separate sorting pass over the data after it comes out of the virtual table. /// /// /// The estimatedCost field should be set to the estimated number /// of disk access operations required to execute this query against /// the virtual table. The SQLite core will often call xBestIndex /// multiple times with different constraints, obtain multiple cost /// estimates, then choose the query plan that gives the lowest estimate. /// /// /// If the current version of SQLite is 3.8.2 or greater, the estimatedRows /// field may be set to an estimate of the number of rows returned by the /// proposed query plan. If this value is not explicitly set, the default /// estimate of 25 rows is used. /// /// /// If the current version of SQLite is 3.9.0 or greater, the idxFlags field /// may be set to SQLITE_INDEX_SCAN_UNIQUE to indicate that the virtual table /// will return only zero or one rows given the input constraints. Additional /// bits of the idxFlags field might be understood in later versions of SQLite. /// /// /// The aConstraintUsage[] array contains one element for each of /// the nConstraint constraints in the inputs section of the /// sqlite3_index_info structure. /// The aConstraintUsage[] array is used by xBestIndex to tell the /// core how it is using the constraints. /// /// /// The xBestIndex method may set aConstraintUsage[].argvIndex /// entries to values greater than zero. /// Exactly one entry should be set to 1, another to 2, another to 3, /// and so forth up to as many or as few as the xBestIndex method wants. /// The EXPR of the corresponding constraints will then be passed /// in as the argv[] parameters to xFilter. /// /// /// For example, if the aConstraint[3].argvIndex is set to 1, then /// when xFilter is called, the argv[0] passed to xFilter will have /// the EXPR value of the aConstraint[3] constraint. /// /// /// By default, the SQLite core double checks all constraints on /// each row of the virtual table that it receives. If such a check /// is redundant, the xBestFilter method can suppress that double-check by /// setting aConstraintUsage[].omit. /// /// /// /// The native pointer to the sqlite3_vtab derived structure. /// /// /// The native pointer to the sqlite3_index_info structure. /// /// /// A standard SQLite return code. /// SQLiteErrorCode xBestIndex( IntPtr pVtab, IntPtr pIndex ); /////////////////////////////////////////////////////////////////////// /// /// /// int (*xDisconnect)(sqlite3_vtab *pVTab); /// /// /// This method releases a connection to a virtual table. /// Only the sqlite3_vtab object is destroyed. /// The virtual table is not destroyed and any backing store /// associated with the virtual table persists. /// /// This method undoes the work of xConnect. /// /// This method is a destructor for a connection to the virtual table. /// Contrast this method with xDestroy. The xDestroy is a destructor /// for the entire virtual table. /// /// /// The xDisconnect method is required for every virtual table implementation, /// though it is acceptable for the xDisconnect and xDestroy methods to be /// the same function if that makes sense for the particular virtual table. /// /// /// /// The native pointer to the sqlite3_vtab derived structure. /// /// /// A standard SQLite return code. /// SQLiteErrorCode xDisconnect( IntPtr pVtab ); /////////////////////////////////////////////////////////////////////// /// /// /// int (*xDestroy)(sqlite3_vtab *pVTab); /// /// /// This method releases a connection to a virtual table, just like /// the xDisconnect method, and it also destroys the underlying /// table implementation. This method undoes the work of xCreate. /// /// /// The xDisconnect method is called whenever a database connection /// that uses a virtual table is closed. The xDestroy method is only /// called when a DROP TABLE statement is executed against the virtual table. /// /// /// The xDestroy method is required for every virtual table implementation, /// though it is acceptable for the xDisconnect and xDestroy methods to be /// the same function if that makes sense for the particular virtual table. /// /// /// /// The native pointer to the sqlite3_vtab derived structure. /// /// /// A standard SQLite return code. /// SQLiteErrorCode xDestroy( IntPtr pVtab ); /////////////////////////////////////////////////////////////////////// /// /// /// int (*xOpen)(sqlite3_vtab *pVTab, sqlite3_vtab_cursor **ppCursor); /// /// /// The xOpen method creates a new cursor used for accessing (read and/or /// writing) a virtual table. A successful invocation of this method /// will allocate the memory for the sqlite3_vtab_cursor (or a subclass), /// initialize the new object, and make *ppCursor point to the new object. /// The successful call then returns SQLITE_OK. /// /// /// For every successful call to this method, the SQLite core will /// later invoke the xClose method to destroy /// the allocated cursor. /// /// /// The xOpen method need not initialize the pVtab field of the /// sqlite3_vtab_cursor structure. The SQLite core will take care /// of that chore automatically. /// /// /// A virtual table implementation must be able to support an arbitrary /// number of simultaneously open cursors. /// /// /// When initially opened, the cursor is in an undefined state. /// The SQLite core will invoke the xFilter method /// on the cursor prior to any attempt to position or read from the cursor. /// /// /// The xOpen method is required for every virtual table implementation. /// /// /// /// The native pointer to the sqlite3_vtab derived structure. /// /// /// Upon success, this parameter must be modified to point to the newly /// created native sqlite3_vtab_cursor derived structure. /// /// /// A standard SQLite return code. /// SQLiteErrorCode xOpen( IntPtr pVtab, ref IntPtr pCursor ); /////////////////////////////////////////////////////////////////////// /// /// /// int (*xClose)(sqlite3_vtab_cursor*); /// /// /// The xClose method closes a cursor previously opened by /// xOpen. /// The SQLite core will always call xClose once for each cursor opened /// using xOpen. /// /// /// This method must release all resources allocated by the /// corresponding xOpen call. The routine will not be called again even if it /// returns an error. The SQLite core will not use the /// sqlite3_vtab_cursor again after it has been closed. /// /// /// The xClose method is required for every virtual table implementation. /// /// /// /// The native pointer to the sqlite3_vtab_cursor derived structure. /// /// /// A standard SQLite return code. /// SQLiteErrorCode xClose( IntPtr pCursor ); /////////////////////////////////////////////////////////////////////// /// /// /// int (*xFilter)(sqlite3_vtab_cursor*, int idxNum, const char *idxStr, /// int argc, sqlite3_value **argv); /// /// /// This method begins a search of a virtual table. /// The first argument is a cursor opened by xOpen. /// The next two arguments define a particular search index previously /// chosen by xBestIndex. The specific meanings of idxNum and idxStr /// are unimportant as long as xFilter and xBestIndex agree on what /// that meaning is. /// /// /// The xBestIndex function may have requested the values of /// certain expressions using the aConstraintUsage[].argvIndex values /// of the sqlite3_index_info structure. /// Those values are passed to xFilter using the argc and argv parameters. /// /// /// If the virtual table contains one or more rows that match the /// search criteria, then the cursor must be left point at the first row. /// Subsequent calls to xEof must return false (zero). /// If there are no rows match, then the cursor must be left in a state /// that will cause the xEof to return true (non-zero). /// The SQLite engine will use /// the xColumn and xRowid methods to access that row content. /// The xNext method will be used to advance to the next row. /// /// /// This method must return SQLITE_OK if successful, or an sqlite /// error code if an error occurs. /// /// /// The xFilter method is required for every virtual table implementation. /// /// /// /// The native pointer to the sqlite3_vtab_cursor derived structure. /// /// /// Number used to help identify the selected index. /// /// /// The native pointer to the UTF-8 encoded string containing the /// string used to help identify the selected index. /// /// /// The number of native pointers to sqlite3_value structures specified /// in . /// /// /// An array of native pointers to sqlite3_value structures containing /// filtering criteria for the selected index. /// /// /// A standard SQLite return code. /// SQLiteErrorCode xFilter( IntPtr pCursor, int idxNum, IntPtr idxStr, int argc, IntPtr argv ); /////////////////////////////////////////////////////////////////////// /// /// /// int (*xNext)(sqlite3_vtab_cursor*); /// /// /// The xNext method advances a virtual table cursor /// to the next row of a result set initiated by xFilter. /// If the cursor is already pointing at the last row when this /// routine is called, then the cursor no longer points to valid /// data and a subsequent call to the xEof method must return true (non-zero). /// If the cursor is successfully advanced to another row of content, then /// subsequent calls to xEof must return false (zero). /// /// /// This method must return SQLITE_OK if successful, or an sqlite /// error code if an error occurs. /// /// /// The xNext method is required for every virtual table implementation. /// /// /// /// The native pointer to the sqlite3_vtab_cursor derived structure. /// /// /// A standard SQLite return code. /// SQLiteErrorCode xNext( IntPtr pCursor ); /////////////////////////////////////////////////////////////////////// /// /// /// int (*xEof)(sqlite3_vtab_cursor*); /// /// /// The xEof method must return false (zero) if the specified cursor /// currently points to a valid row of data, or true (non-zero) otherwise. /// This method is called by the SQL engine immediately after each /// xFilter and xNext invocation. /// /// /// The xEof method is required for every virtual table implementation. /// /// /// /// The native pointer to the sqlite3_vtab_cursor derived structure. /// /// /// Non-zero if no more rows are available; zero otherwise. /// int xEof( IntPtr pCursor ); /////////////////////////////////////////////////////////////////////// /// /// /// int (*xColumn)(sqlite3_vtab_cursor*, sqlite3_context*, int N); /// /// /// The SQLite core invokes this method in order to find the value for /// the N-th column of the current row. N is zero-based so the first column /// is numbered 0. /// The xColumn method may return its result back to SQLite using one of the /// following interface: /// /// /// ]]> /// ]]> sqlite3_result_blob() /// ]]>]]> sqlite3_result_double() /// ]]>]]> sqlite3_result_int() /// ]]>]]> sqlite3_result_int64() /// ]]>]]> sqlite3_result_null() /// ]]>]]> sqlite3_result_text() /// ]]>]]> sqlite3_result_text16() /// ]]>]]> sqlite3_result_text16le() /// ]]>]]> sqlite3_result_text16be() /// ]]>]]> sqlite3_result_zeroblob() /// ]]>]]> /// /// /// If the xColumn method implementation calls none of the functions above, /// then the value of the column defaults to an SQL NULL. /// /// /// To raise an error, the xColumn method should use one of the result_text() /// methods to set the error message text, then return an appropriate /// error code. The xColumn method must return SQLITE_OK on success. /// /// /// The xColumn method is required for every virtual table implementation. /// /// /// /// The native pointer to the sqlite3_vtab_cursor derived structure. /// /// /// The native pointer to the sqlite3_context structure to be used /// for returning the specified column value to the SQLite core /// library. /// /// /// The zero-based index corresponding to the column containing the /// value to be returned. /// /// /// A standard SQLite return code. /// SQLiteErrorCode xColumn( IntPtr pCursor, IntPtr pContext, int index ); /////////////////////////////////////////////////////////////////////// /// /// /// int (*xRowid)(sqlite3_vtab_cursor *pCur, sqlite_int64 *pRowid); /// /// /// A successful invocation of this method will cause *pRowid to be /// filled with the rowid of row that the /// virtual table cursor pCur is currently pointing at. /// This method returns SQLITE_OK on success. /// It returns an appropriate error code on failure. /// /// /// The xRowid method is required for every virtual table implementation. /// /// /// /// The native pointer to the sqlite3_vtab_cursor derived structure. /// /// /// Upon success, this parameter must be modified to contain the unique /// integer row identifier for the current row for the specified cursor. /// /// /// A standard SQLite return code. /// SQLiteErrorCode xRowId( IntPtr pCursor, ref long rowId ); /////////////////////////////////////////////////////////////////////// /// /// /// int (*xUpdate)( /// sqlite3_vtab *pVTab, /// int argc, /// sqlite3_value **argv, /// sqlite_int64 *pRowid /// ); /// /// /// All changes to a virtual table are made using the xUpdate method. /// This one method can be used to insert, delete, or update. /// /// /// The argc parameter specifies the number of entries in the argv array. /// The value of argc will be 1 for a pure delete operation or N+2 for an insert /// or replace or update where N is the number of columns in the table. /// In the previous sentence, N includes any hidden columns. /// /// /// Every argv entry will have a non-NULL value in C but may contain the /// SQL value NULL. In other words, it is always true that /// ]]>argv[i]!=0]]> for ]]>i]]> between 0 and ]]>argc-1]]>. /// However, it might be the case that /// ]]>sqlite3_value_type(argv[i])==SQLITE_NULL]]>. /// /// /// The argv[0] parameter is the rowid of a row in the virtual table /// to be deleted. If argv[0] is an SQL NULL, then no deletion occurs. /// /// /// The argv[1] parameter is the rowid of a new row to be inserted /// into the virtual table. If argv[1] is an SQL NULL, then the implementation /// must choose a rowid for the newly inserted row. Subsequent argv[] /// entries contain values of the columns of the virtual table, in the /// order that the columns were declared. The number of columns will /// match the table declaration that the xConnect or xCreate method made /// using the sqlite3_declare_vtab() call. All hidden columns are included. /// /// /// When doing an insert without a rowid (argc>1, argv[1] is an SQL NULL), /// on a virtual table that uses ROWID (but not on a WITHOUT ROWID virtual table, /// the implementation must set *pRowid to the rowid of the newly inserted row; /// this will become the value returned by the sqlite3_last_insert_rowid() /// function. Setting this value in all the other cases is a harmless no-op; /// the SQLite engine ignores the *pRowid return value if argc==1 or /// argv[1] is not an SQL NULL. /// /// /// Each call to xUpdate will fall into one of cases shown below. /// Not that references to ]]>argv[i]]]> mean the SQL value /// held within the argv[i] object, not the argv[i] /// object itself. /// /// /// ]]> /// ]]>]]>argc = 1 ]]> argv[0] ≠ NULL]]> /// ]]>]]> /// The single row with rowid or PRIMARY KEY equal to argv[0] is deleted. /// No insert occurs. /// ]]>]]>]]>argc > 1 ]]> argv[0] = NULL]]> /// ]]>]]> /// A new row is inserted with column values taken from /// argv[2] and following. In a rowid virtual table, if argv[1] is an SQL NULL, /// then a new unique rowid is generated automatically. The argv[1] will be NULL /// for a WITHOUT ROWID virtual table, in which case the implementation should /// take the PRIMARY KEY value from the appropriate column in argv[2] and following. /// ]]>]]>]]>argc > 1 ]]> argv[0] ≠ NULL ]]> argv[0] = argv[1]]]> /// ]]>]]> /// The row with rowid or PRIMARY KEY argv[0] is updated with new values /// in argv[2] and following parameters. /// ]]>]]>]]>argc > 1 ]]> argv[0] ≠ NULL ]]> argv[0] ≠ argv[1]]]> /// ]]>]]> The row with rowid or PRIMARY KEY argv[0] is updated with /// the rowid or PRIMARY KEY in argv[1] /// and new values in argv[2] and following parameters. This will occur /// when an SQL statement updates a rowid, as in the statement: /// /// UPDATE table SET rowid=rowid+1 WHERE ...; /// /// ]]>]]> /// /// /// The xUpdate method must return SQLITE_OK if and only if it is /// successful. If a failure occurs, the xUpdate must return an appropriate /// error code. On a failure, the pVTab->zErrMsg element may optionally /// be replaced with error message text stored in memory allocated from SQLite /// using functions such as sqlite3_mprintf() or sqlite3_malloc(). /// /// /// If the xUpdate method violates some constraint of the virtual table /// (including, but not limited to, attempting to store a value of the wrong /// datatype, attempting to store a value that is too /// large or too small, or attempting to change a read-only value) then the /// xUpdate must fail with an appropriate error code. /// /// /// If the xUpdate method is performing an UPDATE, then /// sqlite3_value_nochange(X) can be used to discover which columns /// of the virtual table were actually modified by the UPDATE /// statement. The sqlite3_value_nochange(X) interface returns /// true for columns that do not change. /// On every UPDATE, SQLite will first invoke /// xColumn separately for each unchanging column in the table to /// obtain the value for that column. The xColumn method can /// check to see if the column is unchanged at the SQL level /// by invoking sqlite3_vtab_nochange(). If xColumn sees that /// the column is not being modified, it should return without setting /// a result using one of the sqlite3_result_xxxxx() /// interfaces. Only in that case sqlite3_value_nochange() will be /// true within the xUpdate method. If xColumn does /// invoke one or more sqlite3_result_xxxxx() /// interfaces, then SQLite understands that as a change in the value /// of the column and the sqlite3_value_nochange() call for that /// column within xUpdate will return false. /// /// /// There might be one or more sqlite3_vtab_cursor objects open and in use /// on the virtual table instance and perhaps even on the row of the virtual /// table when the xUpdate method is invoked. The implementation of /// xUpdate must be prepared for attempts to delete or modify rows of the table /// out from other existing cursors. If the virtual table cannot accommodate /// such changes, the xUpdate method must return an error code. /// /// /// The xUpdate method is optional. /// If the xUpdate pointer in the sqlite3_module for a virtual table /// is a NULL pointer, then the virtual table is read-only. /// /// /// /// The native pointer to the sqlite3_vtab derived structure. /// /// /// The number of new or modified column values contained in /// . /// /// /// The array of native pointers to sqlite3_value structures containing /// the new or modified column values, if any. /// /// /// Upon success, this parameter must be modified to contain the unique /// integer row identifier for the row that was inserted, if any. /// /// /// A standard SQLite return code. /// SQLiteErrorCode xUpdate( IntPtr pVtab, int argc, IntPtr argv, ref long rowId ); /////////////////////////////////////////////////////////////////////// /// /// /// int (*xBegin)(sqlite3_vtab *pVTab); /// /// /// This method begins a transaction on a virtual table. /// This is method is optional. The xBegin pointer of sqlite3_module /// may be NULL. /// /// /// This method is always followed by one call to either the /// xCommit or xRollback method. Virtual table transactions do /// not nest, so the xBegin method will not be invoked more than once /// on a single virtual table /// without an intervening call to either xCommit or xRollback. /// Multiple calls to other methods can and likely will occur in between /// the xBegin and the corresponding xCommit or xRollback. /// /// /// /// The native pointer to the sqlite3_vtab derived structure. /// /// /// A standard SQLite return code. /// SQLiteErrorCode xBegin( IntPtr pVtab ); /////////////////////////////////////////////////////////////////////// /// /// /// int (*xSync)(sqlite3_vtab *pVTab); /// /// /// This method signals the start of a two-phase commit on a virtual /// table. /// This is method is optional. The xSync pointer of sqlite3_module /// may be NULL. /// /// /// This method is only invoked after call to the xBegin method and /// prior to an xCommit or xRollback. In order to implement two-phase /// commit, the xSync method on all virtual tables is invoked prior to /// invoking the xCommit method on any virtual table. If any of the /// xSync methods fail, the entire transaction is rolled back. /// /// /// /// The native pointer to the sqlite3_vtab derived structure. /// /// /// A standard SQLite return code. /// SQLiteErrorCode xSync( IntPtr pVtab ); /////////////////////////////////////////////////////////////////////// /// /// /// int (*xCommit)(sqlite3_vtab *pVTab); /// /// /// This method causes a virtual table transaction to commit. /// This is method is optional. The xCommit pointer of sqlite3_module /// may be NULL. /// /// /// A call to this method always follows a prior call to xBegin and /// xSync. /// /// /// /// The native pointer to the sqlite3_vtab derived structure. /// /// /// A standard SQLite return code. /// SQLiteErrorCode xCommit( IntPtr pVtab ); /////////////////////////////////////////////////////////////////////// /// /// /// int (*xRollback)(sqlite3_vtab *pVTab); /// /// /// This method causes a virtual table transaction to rollback. /// This is method is optional. The xRollback pointer of sqlite3_module /// may be NULL. /// /// /// A call to this method always follows a prior call to xBegin. /// /// /// /// The native pointer to the sqlite3_vtab derived structure. /// /// /// A standard SQLite return code. /// SQLiteErrorCode xRollback( IntPtr pVtab ); /////////////////////////////////////////////////////////////////////// /// /// /// int (*xFindFunction)( /// sqlite3_vtab *pVtab, /// int nArg, /// const char *zName, /// void (**pxFunc)(sqlite3_context*,int,sqlite3_value**), /// void **ppArg /// ); /// /// /// This method is called during sqlite3_prepare() to give the virtual /// table implementation an opportunity to overload functions. /// This method may be set to NULL in which case no overloading occurs. /// /// /// When a function uses a column from a virtual table as its first /// argument, this method is called to see if the virtual table would /// like to overload the function. The first three parameters are inputs: /// the virtual table, the number of arguments to the function, and the /// name of the function. If no overloading is desired, this method /// returns 0. To overload the function, this method writes the new /// function implementation into *pxFunc and writes user data into *ppArg /// and returns 1. /// /// /// Note that infix functions (LIKE, GLOB, REGEXP, and MATCH) reverse /// the order of their arguments. So "like(A,B)" is equivalent to "B like A". /// For the form "B like A" the B term is considered the first argument /// to the function. But for "like(A,B)" the A term is considered the /// first argument. /// /// /// The function pointer returned by this routine must be valid for /// the lifetime of the sqlite3_vtab object given in the first parameter. /// /// /// /// The native pointer to the sqlite3_vtab derived structure. /// /// /// The number of arguments to the function being sought. /// /// /// The name of the function being sought. /// /// /// Upon success, this parameter must be modified to contain the /// delegate responsible for implementing the specified function. /// /// /// Upon success, this parameter must be modified to contain the /// native user-data pointer associated with /// . /// /// /// Non-zero if the specified function was found; zero otherwise. /// int xFindFunction( IntPtr pVtab, int nArg, IntPtr zName, ref SQLiteCallback callback, ref IntPtr pClientData ); /////////////////////////////////////////////////////////////////////// /// /// /// int (*xRename)(sqlite3_vtab *pVtab, const char *zNew); /// /// /// This method provides notification that the virtual table implementation /// that the virtual table will be given a new name. /// If this method returns SQLITE_OK then SQLite renames the table. /// If this method returns an error code then the renaming is prevented. /// /// /// The xRename method is required for every virtual table implementation. /// /// /// /// The native pointer to the sqlite3_vtab derived structure. /// /// /// The native pointer to the UTF-8 encoded string containing the new /// name for the virtual table. /// /// /// A standard SQLite return code. /// SQLiteErrorCode xRename( IntPtr pVtab, IntPtr zNew ); /////////////////////////////////////////////////////////////////////// /// /// /// int (*xSavepoint)(sqlite3_vtab *pVtab, int); /// int (*xRelease)(sqlite3_vtab *pVtab, int); /// int (*xRollbackTo)(sqlite3_vtab *pVtab, int); /// /// /// These methods provide the virtual table implementation an opportunity to /// implement nested transactions. They are always optional and will only be /// called in SQLite version 3.7.7 (2011-06-23) and later. /// /// /// When xSavepoint(X,N) is invoked, that is a signal to the virtual table X /// that it should save its current state as savepoint N. /// A subsequent call /// to xRollbackTo(X,R) means that the state of the virtual table should return /// to what it was when xSavepoint(X,R) was last called. /// The call /// to xRollbackTo(X,R) will invalidate all savepoints with N>R; none of the /// invalided savepoints will be rolled back or released without first /// being reinitialized by a call to xSavepoint(). /// A call to xRelease(X,M) invalidates all savepoints where N>=M. /// /// /// None of the xSavepoint(), xRelease(), or xRollbackTo() methods will ever /// be called except in between calls to xBegin() and /// either xCommit() or xRollback(). /// /// /// /// The native pointer to the sqlite3_vtab derived structure. /// /// /// This is an integer identifier under which the the current state of /// the virtual table should be saved. /// /// /// A standard SQLite return code. /// SQLiteErrorCode xSavepoint( IntPtr pVtab, int iSavepoint ); /////////////////////////////////////////////////////////////////////// /// /// /// int (*xSavepoint)(sqlite3_vtab *pVtab, int); /// int (*xRelease)(sqlite3_vtab *pVtab, int); /// int (*xRollbackTo)(sqlite3_vtab *pVtab, int); /// /// /// These methods provide the virtual table implementation an opportunity to /// implement nested transactions. They are always optional and will only be /// called in SQLite version 3.7.7 (2011-06-23) and later. /// /// /// When xSavepoint(X,N) is invoked, that is a signal to the virtual table X /// that it should save its current state as savepoint N. /// A subsequent call /// to xRollbackTo(X,R) means that the state of the virtual table should return /// to what it was when xSavepoint(X,R) was last called. /// The call /// to xRollbackTo(X,R) will invalidate all savepoints with N>R; none of the /// invalided savepoints will be rolled back or released without first /// being reinitialized by a call to xSavepoint(). /// A call to xRelease(X,M) invalidates all savepoints where N>=M. /// /// /// None of the xSavepoint(), xRelease(), or xRollbackTo() methods will ever /// be called except in between calls to xBegin() and /// either xCommit() or xRollback(). /// /// /// /// The native pointer to the sqlite3_vtab derived structure. /// /// /// This is an integer used to indicate that any saved states with an /// identifier greater than or equal to this should be deleted by the /// virtual table. /// /// /// A standard SQLite return code. /// SQLiteErrorCode xRelease( IntPtr pVtab, int iSavepoint ); /////////////////////////////////////////////////////////////////////// /// /// /// int (*xSavepoint)(sqlite3_vtab *pVtab, int); /// int (*xRelease)(sqlite3_vtab *pVtab, int); /// int (*xRollbackTo)(sqlite3_vtab *pVtab, int); /// /// /// These methods provide the virtual table implementation an opportunity to /// implement nested transactions. They are always optional and will only be /// called in SQLite version 3.7.7 (2011-06-23) and later. /// /// /// When xSavepoint(X,N) is invoked, that is a signal to the virtual table X /// that it should save its current state as savepoint N. /// A subsequent call /// to xRollbackTo(X,R) means that the state of the virtual table should return /// to what it was when xSavepoint(X,R) was last called. /// The call /// to xRollbackTo(X,R) will invalidate all savepoints with N>R; none of the /// invalided savepoints will be rolled back or released without first /// being reinitialized by a call to xSavepoint(). /// A call to xRelease(X,M) invalidates all savepoints where N>=M. /// /// /// None of the xSavepoint(), xRelease(), or xRollbackTo() methods will ever /// be called except in between calls to xBegin() and /// either xCommit() or xRollback(). /// /// /// /// The native pointer to the sqlite3_vtab derived structure. /// /// /// This is an integer identifier used to specify a specific saved /// state for the virtual table for it to restore itself back to, which /// should also have the effect of deleting all saved states with an /// integer identifier greater than this one. /// /// /// A standard SQLite return code. /// SQLiteErrorCode xRollbackTo( IntPtr pVtab, int iSavepoint ); } #endregion }