C/C ++ APIs for SQLite Third Edition

C/C ++ APIs for SQLite Third Edition

1.0 Overview

SQLite 3.0 is a new version of SQLite. It inherits from SQLite 2.8.13, but has an incompatible file format and API. SQLite 3.0 is created based on the following requirements:

• UTF-16 is supported.
• User-Defined text sorting.
• Stores blob as an index column.

It must be migrated to version 3.0 because every change is incompatible with the database file format. Some other incompatible changes, such as the cleared API, are introduced in the following theory. It is best to remove your incompatible changes at one time.

The APIs of version 3.0 are similar to those of version 2.x, but there are also some important changes. The most significant difference is that the prefix "SQLite _" that appears before all API functions and data structures is changed to "sqlite3 _". This avoids confusion between Level 2 APIs and allows the linker to respond to both SQLite 2.x and SQLite 3.0.

The C type in the UTF-16 should be on what kind there is no uniformity. Therefore, SQLite uses a generic type void * to point to a UTF-16 string. Customer software can convert void * to any data type that suits the system.

2.0 C/C ++ Interface

In addition to several data structures and # define, SQLite 3.0 APIs include 83 independent functions. (A complete api reference is provided as an independent document .) Fortunately, the interface is not as complex as the size it shows. A simple program can still work with only three functions: sqlite3_open (), sqlite3_exec (), and sqlite3_close (). For more database engine operation control, you can use sqlite3_prepare () to compile an SQLite statement into byte code and execute it through sqlite3_step. A command sequence starting with sqlite3_column _ can be used to extract information about the query results. Many interface functions appear in pairs in the form of UTF-8 and UTF-16. In addition, there is a comparison between user-defined SQL functions and user-defined text functions.

2.1 Open and Close a database

Typedef struct sqlite3 sqlite3;
Int sqlite3_open (const char *, sqlite3 **);
Int sqlite3_open16 (const void *, sqlite3 **);
Int sqlite3_close (sqlite3 *);
Const char * sqlite3_errmsg (sqlite3 *);
Const void * sqlite3_errmsg16 (sqlite3 *);
Int sqlite3_errcode (sqlite3 *);

The sqlite3_open () program returns an integer error code, instead of returning a structure pointing to sqlite3 as sqlite2 does. The difference between sqlite3_open () and sqlite3_open16 () Is that sqlite3_open16 () uses UTF-16 (in local byte order) as the database file name. If a new database file needs to be created, sqlite3_open16 () sets the local text expression to the UTF-16 while sqlite3_open () sets the text expression to the UTF-8.

The database file is opened or created when the user is created ). This allows options and parameters, such as local text representation and default page size, to be set using the Pragma statement.

The sqlite3_errcode () command returns the result code of a recent main API call. Sqlite3_errmsg () returns an English message of the latest error. The error code may be temporary-it may disappear in any subsequent SQLite function call. Sqlite3_errmsg16 () works like sqlite3_errsmg (), except that it returns a UTF-16 error message in local bytes.

The error code of SQLite 3 is not changed compared with version 2. They are as follows:

# Define sqlite_ OK 0/* successful results */# define sqlite_error 1/* SQL error or no database */# define sqlite_internal 2/* internal logic error of a SQLite */# define sqlite_perm 3/* access permission denied */# define sqlite_abort 4/* requires an interrupted callback command */# define sqlite_busy 5/* the Data Warehouse file is locked */# define sqlite_locked 6/* a table in the database is locked */# define sqlite_nomem 7/* malloc () failed */# define sqlite_readonly 8/* try to write a read-only database */# define sqlite_interrupt 9/* The operation is sqlite_interrupt () end */# define sqlite_ioerr 10/* a disk I/O error occurs */# define sqlite_0000upt 11/* database disk image exception */# define sqlite_notfound 12/* (internal only) table or record does not exist */# define sqlite_full 13/* database full insertion failed */# define sqlite_cantopen 14/* database file cannot be opened */# define sqlite_protocol 15/* database incorrect Protocol Error */# define sqlite_empty 16/* (internal only) the database table is empty */# define sqlite_schema 17/* the database structure is changed */# define sqlite_toobig 18/* too many rows in a table */# define sqlite_constraint 19/* Due to constraints abort due to conflict */# define sqlite_mismatch 20/* Data Type Mismatch */# define sqlite_misuse 21/* the database is incorrectly used */# define sqlite_nolfs 22/* unsupported by the host OS features */# define sqlite_auth 23/* Authorization denied */# define sqlite_row 100/* sqlite_step () another row is ready */# define sqlite_done 101/* sqlite_step () has been executed */

2.2 Execute SQL statements

typedef int (*sqlite_callback)(void*,int,char**, char**);int sqlite3_exec(sqlite3*, const char *sql, sqlite_callback, void*, char**);

The sqlite3_exec () function works very similar to the method in SQLite 2. The zero or multiple SQL statements given in the second parameter are compiled and executed. The query result is returned with a callback function. For more information, see api reference.

In SQLite 3, The sqlite3_exec () function is like a container that contains a call to a predefined statement interface.

typedef struct sqlite3_stmt sqlite3_stmt;int sqlite3_prepare(sqlite3*, const char*, int, sqlite3_stmt**, const char**);int sqlite3_prepare16(sqlite3*, const void*, int, sqlite3_stmt**, const void**);int sqlite3_finalize(sqlite3_stmt*);int sqlite3_reset(sqlite3_stmt*);

The sqlite3_prepare interface compiles a single SQL statement into executable byte code. This interface is now a better way to access the database.

The SQL statement for sqlite3_prepare () is a string for a UTF-8. Sqlite3_prepare16 () is the same except that the string input is a UTF-16. Only the first statement of the input string is compiled. The fourth parameter is a pointer to the next (uncompiled) SQLite statement, if any. The sqlite3_finalize () function processes a prepared SQL statement. All prepared SQL statements before the database is closed must be completed. The sqlite_reset () function resets the prepared statement so that it can be executed again.

The SQL statement can contain "?" Or "? "Nnn" or ": AA" identifier here "nnn" is an integer while "AA" is an identifier. These identifiers indicate undefined text values (or "wildcards") to be filled by the sqlite3_bind interface. Each wildcard has a corresponding data. It is a sequence in the expression or "nnn" with "? Nnn format. This allows the same wildcard to appear instead of once in the statement. In this case, the wildcard will be truly filled with the same value. The unrestricted wildcard will have a null value.

   int sqlite3_bind_blob(sqlite3_stmt*, int, const void*, int n, void(*)(void*));   int sqlite3_bind_double(sqlite3_stmt*, int, double);   int sqlite3_bind_int(sqlite3_stmt*, int, int);   int sqlite3_bind_int64(sqlite3_stmt*, int, long long int);   int sqlite3_bind_null(sqlite3_stmt*, int);   int sqlite3_bind_text(sqlite3_stmt*, int, const char*, int n, void(*)(void*));   int sqlite3_bind_text16(sqlite3_stmt*, int, const void*, int n, void(*)(void*));   int sqlite3_bind_value(sqlite3_stmt*, int, const sqlite3_value*);

This is a sqlite3_bind command used to assign a specified category to prepared statements. The undefined wildcard is interpreted as null. The binding cannot be reset by sqlite3_reset. However, wildcards can be rebound to new values after sqlite3_reset.

After an SQL statement is prepared (optional), it is executed using the following statement:

Int sqlite3_step (sqlite3_stmt *); sqlite3_step () command returns sqlite_row when a record of the result set is returned, or if the execution is completed, sqlite_done is returned or normal or depends on an error. If you cannot open a database file, it may return sqlite_busy. If a program returns a sqlite_row value, the following command can be used to extract information about the result set rows:

Const void * Forward (sqlite3_stmt *, int icol); int forward (sqlite3_stmt *, int icol); int forward (sqlite3_stmt *, int icol); int forward (sqlite3_stmt *); const char * handle (sqlite3_stmt *, int icol); const void * handle (sqlite3_stmt *, int icol); double handle (sqlite3_stmt *, int icol); int sqlite3_col Umn_int (sqlite3_stmt *, int icol); long int weight (sqlite3_stmt *, int icol); const char * sqlite3_column_name (sqlite3_stmt *, int icol ); const void * handle (sqlite3_stmt *, int icol); const unsigned char * handle (sqlite3_stmt *, int icol); const void * handle (sqlite3_stmt *, int icol ); int sqlite3_column_type (sqlite3_stmt *, int icol); sqlite3_col The number of columns in the result set returned by the umn_count () function. Sqlite3_cloumn_count () can be called at any time after sqlite3_prepare. The function of sqlte3_data_count () is similar to that of sqlite3_column_count () except that it is only valid after sqlite3_step. If sqlite3_step () is called to return sqlite_done or an error code, sqlite3_data_count () returns 0. sqlite3_cloumn_count () will continue to return the number of rows in the result set.
The returned results are checked by another sqlite3_column _ *** () function. All the results contain a column number as their second parameter. Columns start from 0 to left. Note that this is different from the parameter, It is indexed from the beginning.
The sqlite3_column_type () function returns the Data Type of the nth column. The return value is one of the following values:
# Define sqlite_integer 1 # define sqlite_float 2 # define sqlite_text 3 # define sqlite_blob 4 # define sqlite_null 5sqlite3_column_decltype () returns a column type text declared using the create table statement. For an expression, the return value is an empty string. Sqlite3_column_bytes () returns the name of column N. Sqlite3_column_bytes () returns the number of bytes of the text type blob or UTF-8 encoding. Sqlite3_column_bytes16 () returns the same value for BLOB type while text returns the number of bytes encoded in UTF-16. Sqlite3_column_text () returns UTF-8 data. Sqlite3_column_text16 () return UTF-16 data. Sqlite3_column_int () returns the local integer data of the host. Sqlite_column_int64 returns a 64-bit integer. Finally, sqlite_column_duble () returns the number of floating point types.
Obtaining data through sqlite3_column_type () is not required. If a different format is required, the data type is automatically converted.
2.3 User-Defined Functions
User-defined functions can be implemented using the following Han numbers:
   typedef struct sqlite3_value sqlite3_value;   int sqlite3_create_function(     sqlite3 *,     const char *zFunctionName,     int nArg,     int eTextRep,     void*,     void (*xFunc)(sqlite3_context*,int,sqlite3_value**),     void (*xStep)(sqlite3_context*,int,sqlite3_value**),     void (*xFinal)(sqlite3_context*)   );   int sqlite3_create_function16(     sqlite3*,     const void *zFunctionName,     int nArg,     int eTextRep,     void*,     void (*xFunc)(sqlite3_context*,int,sqlite3_value**),     void (*xStep)(sqlite3_context*,int,sqlite3_value**),     void (*xFinal)(sqlite3_context*)   );   #define SQLITE_UTF8     1   #define SQLITE_UTF16    2   #define SQLITE_UTF16BE  3   #define SQLITE_UTF16LE  4   #define SQLITE_ANY      5
The NARG parameter specifies the number of parameters of the function. The value 0 indicates that any number of parameters are allowed. The etextrep parameter specifies the text description value in this function. This parameter value is a type defined above. SQLite 3 allows different expressions for the same function. The minimum number of text conversions required by the database engine.
Generally, the functions specify xfunc and leave xfunc and xstep empty. The aggregate function specifies xstep and xfinal and keeps xfunc empty. The same is true for sqlite3_create_aggregate () APIs.
The function name is specified in the UTF-8. A separate sqlite_craetefunction16 () API works the same as sqlite_create_function () except that the function name is specified along with the UTF-16 host byte.
Note that a function parameter is a pointer to the sqlite3_value structure, rather than a pointer to a string like SQLite 2.x. The following functions are used to obtain useful information from the following values:
   const void *sqlite3_value_blob(sqlite3_value*);   int sqlite3_value_bytes(sqlite3_value*);   int sqlite3_value_bytes16(sqlite3_value*);   double sqlite3_value_double(sqlite3_value*);   int sqlite3_value_int(sqlite3_value*);   long long int sqlite3_value_int64(sqlite3_value*);   const unsigned char *sqlite3_value_text(sqlite3_value*);   const void *sqlite3_value_text16(sqlite3_value*);   int sqlite3_value_type(sqlite3_value*);
Use the following API functions to obtain the content and report the results:
   void *sqlite3_aggregate_context(sqlite3_context*, int nbyte);   void *sqlite3_user_data(sqlite3_context*);   void sqlite3_result_blob(sqlite3_context*, const void*, int n, void(*)(void*));   void sqlite3_result_double(sqlite3_context*, double);   void sqlite3_result_error(sqlite3_context*, const char*, int);   void sqlite3_result_error16(sqlite3_context*, const void*, int);   void sqlite3_result_int(sqlite3_context*, int);   void sqlite3_result_int64(sqlite3_context*, long long int);   void sqlite3_result_null(sqlite3_context*);   void sqlite3_result_text(sqlite3_context*, const char*, int n, void(*)(void*));   void sqlite3_result_text16(sqlite3_context*, const void*, int n, void(*)(void*));   void sqlite3_result_value(sqlite3_context*, sqlite3_value*);   void *sqlite3_get_auxdata(sqlite3_context*, int);   void sqlite3_set_auxdata(sqlite3_context*, int, void*, void (*)(void*));
2.4 User-Defined sorting
Next, the program is used to sort User-Defined orders:
   sqlite3_create_collation(sqlite3*, const char *zName, int eTextRep, void*,      int(*xCompare)(void*,int,const void*,int,const void*));   sqlite3_create_collation16(sqlite3*, const void *zName, int eTextRep, void*,      int(*xCompare)(void*,int,const void*,int,const void*));   sqlite3_collation_needed(sqlite3*, void*,       void(*)(void*,sqlite3*,int eTextRep,const char*));   sqlite3_collation_needed16(sqlite3*, void*,      void(*)(void*,sqlite3*,int eTextRep,const void*));
The sqlite3_create_collation () function specifies a sorting name and the corresponding function to implement sorting. The comparison function is only used to compare text values.
The etextrep parameter sqlite_utf8, sqlite_utf16, sqlite_utf16be, or one of sqlite_any to specify the text expression used to work.
In the face of the same UTF-8, a comparison function exists independently of the UTF-16LE and the UTF-16BE aggregation sequence.
Sqlite3_create_collation16 () features the same as sqlite3_create_collation (), except that the sort name is replaced by the UTF-16 of the host's byte order column.
Sqlite3_collation_needed () function registers the callback function and allows the database engine to call it when an unknown sorting sequence is encountered.
The callback function can find a comparison function and call sqlit3_create_collation () when necessary ().
The fourth parameter of the callback function is the name of the summary sequence in UTF-8 format.
For the sqlite3_collatin_need16 () function, the callback function calls the UTF-16 summary sequence name that sends the host's bytecode.