SQLite uses (iii) && core API Usage

Overview
SQLite provides a series of interfaces for users to access the database, mainly including connecting the database, processing SQL, iterative query results and so on. This article lists the core APIs for our main scenario using SQLite, detailing the usage of the API and giving code use cases.
1. Open the Close database
Sqlite3_open_v2
Prototype:

int sqlite3_open_v2(
const char *filename, /* Database filename (UTF-8) */
sqlite3 **ppDb, /* OUT: SQLite db handle */
int flags, /* Flags */
const char *zVfs /* Name of VFS module to use */
);

Role: Open a database connection
Key parameters: Flags
Sqlite_open_nomutex: Setting the database connection to run in multithreaded mode (without specifying single-threaded mode)
Sqlite_open_fullmutex: Sets the database connection to run in serial mode.
Sqlite_open_sharedcache: Set run in shared cache mode.
Sqlite_open_privatecache: Set run in unshared cache mode.
Sqlite_open_readwrite: Specifies that the database connection can be read and written.
Sqlite_open_create: If the database does not exist, it is created.

Sqlite3_close_v2
Prototype:

int sqlite3_close_v2 (sqlite3*);

Role: Closes the database connection, and if the connection has uncommitted transactions on shutdown, the transaction is automatically rolled back.

1.1 Example : Open Close Database connection

sqlite3* pDb;
char* filename="/u01/sqlite/test.db";
sqlite3_open_v2(filename, &pDb, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_NOMUTEX | SQLITE_OPEN_SHAREDCACHE, NULL);
....
....
sqlite3_close_v2(pDb);

Open the database file test.db, the corresponding database connection is readable and writable, runs in multithreaded mode, runs in shared cache mode, and closes the database connection after the operation is done.

2. Update SQL
Update SQL mainly includes CREATE TABLE, insert, delete, update record, etc., the update API commonly used in SQLite has two, one is sqlite3_exec, the other is SQLITE3_PREPARE_V2.

2.1 Sqlite3_exec
Prototype:

 
 
int sqlite3_exec(
sqlite3*, /* An open database */
const char *sql, /* SQL to be evaluated */
int (*callback)(void*,int,char**,char**), /* Callback function */
void *, /* 1st argument to callback */
char **errmsg /* Error msg written here */
);

Where the parameter SQL can contain multiple SQL commands, separated by semicolons, sqlite3_exec () parses and executes each command in the SQL string until the end of the string is reached or an error is encountered. The command to run the Modify database (create, INSERT, delete, update) is perfect, and a function call can do all the work. It is important to note that although sqlite3_exec () can execute multiple SQL commands, the function does not guarantee that the transaction, which is the executed statement, will not be rolled back because of subsequent failed statements.

2.2 SQLITE3_PERPARE_V2
Prototype:

 
int sqlite3_prepare_v2(
sqlite3 *db, /* Database handle */
const char *zSql, /* SQL statement, UTF-8 encoded */
int nByte, /* Maximum length of zSql in bytes. */
sqlite3_stmt **ppStmt, /* OUT: Statement handle */
const char **pzTail /* OUT: Pointer to unused portion of zSql */
);

Sqlite3_exec is actually a compilation, execution is encapsulated, and an equivalent set of functions is SQLITE3_PREPARE_V2 (), Sqlite3_step (), and Sqlite3_finalize (). The SQLITE3_PREPARE_V2 () compiled SQL statement generates a VDBE execution code, SQLITE3_STEP () executes, sqlite3_finalize () Closes the statement handle, freeing the resource. In both ways, you can get the number of rows affected by the statement by calling Sqlite3_changes (PDB).

2.3 Comparison of two ways
(1). Sqlite3_exec mode interface is very simple to implement the same function, less than the Sqlite3_perpare_v2 interface code.
(2). The Sqlite3_prepare method is more efficient because it needs to be compiled once and can be repeated n times.
(3). Sqlite3_prepare mode supports parameterized SQL.

Given the differences between the two approaches, for a simple PRAGMA Set statement (PRAGMA cache_size=2000), the transaction Set statement (BEGIN transaction,commit,rollback) uses sqlite3_exec mode, which is simpler And for batch update, query statement, it is more efficient to use Sqlite3_prepare mode.

2.4 Example : Prepare mode executes a multi-SQL example, pnext initializes the SQL statement header, executes a SQL, and moves to the next SQL header.

 
Const char *pNext = (const char *)sql;
While (pNext && strlen(pNext) > 0) {
Rc = sqlite3_prepare_v2(pDb, pNext, -1, &pStmt, &pNext);
If(SQLITE_OK != rc){
Error handling
Break;
}
   
     Rc = sqlite3_step(pStmt);
     If(SQLITE_OK != rc && SQLITE_DONE != rc){
Error handling
Break;
     }

     Rc = SQLITE_OK;

     /*Statistically affect the number of records*/
     resultCount += sqlite3_changes(pDb);

     /* Clean up the statement handle and prepare to execute the next statement */
     Sqlite3_finalize(pStmt);
}

3. Querying SQL
3.1 sqlite3_get_table
Prototype:

 
int sqlite3_get_table(
sqlite3 *db, /* An open database */
const char *zSql, /* SQL to be evaluated */
char ***pazResult, /* Results of the query */
int *pnRow, /* Number of result rows written here */
int *pnColumn, /* Number of result columns written here */
char **pzErrmsg /* Error msg written here */
);

The function receives all records returned by the SQL statement, uses the memory allocated internally by SQLite, stores it in the parameter RESULTP, and must use Sqlite3_free_table () to free memory. Because the result set can be very large and can cause memory to explode, this is not recommended for queries of large result sets.

3.2 Sqlite3_prepare_v2
The prepare approach also supports query statements, which are divided into 3 phases, compile, execute, and result set processing. The basic steps of prepare are described in the previous Update SQL section, which focuses on the result set processing section. First, the number of columns of the result set can be obtained by Sqlite3_column_count (), and the storage type of a particular column can be obtained by Sqlite3_column_type (), so that we could call the appropriate SQLITE3_COLUMN_XXX interface to process the value of the field. The main categories are:
Sqlite3_column_int
Sqlite3_column_int64
Sqlite3_column_double
Sqlite3_column_text
Sqlite3_column_blob

3.3 Example : traversing result sets

 
Int rc = sqlite3_prepare_v2(pDb, sql, -1, &pStmt, NULL);
/ / Get the number of columns
Int n_columns = sqlite3_column_count(pStmt);
Do{
Ret = sqlite3_step(stmt);
If (ret == SQLITE_ROW)
{
/ / Process each column
For (i = 0; i < n_columns; i++)
{
          /*Get column storage type*/
Type = sqlite3_column_type(stmt,i);
Switch(type)
{
Case SQLITE_INTEGER:
/*Processing integer*/
Sqlite3_column_int(stmt,i);
Break;
Case SQLITE_FLOAT:
/*Processing floating point numbers*/
Sqlite3_column_double(stmt,i);
Break;
Case SQLITE_TEXT:
/*Processing strings*/
Sqlite3_column_text(stmt,i);
              Break;
Case SQLITE_BLOB:
/*Processing Binary*/
Sqlite3_column_blob(stmt, i));
Break;
Case SQLITE_NULL:
/*Processing empty*/
}
}
}
Else if (ret == SQLITE_DONE) //end
{
Break;
}
}while(true);

4. Parameter binding
     sqlite can support parameterized SQL statements through the prepare interface, which is an SQL statement with a question mark. For example, the query statement select * FROM t where id=?, or insert a statement inserts into T (A,B,C) VALUES (?,?,?). With parameterized SQL, you can achieve the goal of compiling multiple executions at once, because the question mark is meaningless, so you need to call the Sqlite3_bind_xxx interface to bind the specific parameters. There are mainly the following categories:
sqlite3_bind_int

sqlite3_bind_double
sqlite3_bind_text
sqlite3_bind_blob
sqlite3_bind_ Null
about binding parameters here, for Sqlite3_bind_text and Sqlite3_bind_blob interfaces, Whether the storage space occupied by the binding parameters can be reused by SQLite. interface, the parameter values can be sqlite_static and sqlite_transient, as specified by the last parameter.

sqlite_static: Notifies the bind function, the parameter uses the space is constant, does not change, does not have the copy copy inside SQLITE.
sqlite_transient: Notify bind function, parameter use space may change, SQLITE internal need to have own copy.

4.1 Example : Bulk Import

 
//begin a transaction
If(sqlite3_exec(pdb, "begin", NULL, NULL, &errmsg) != SQLITE_OK)
{
Error handling
Return ERROR;
}

Sqlite3_prepare_v2(pdb, "insert into t1 values(?,?,?);", &stmt);
For (i = 0; i < n_rows; i++)
{
For (j = 0; j < n_columns; j++)
{
Switch(type)
{
Case SQLITE_INTEGER:
/*Processing integer*/
Sqlite3_bind_int()
Break;
Case SQLITE_FLOAT:
/*Processing floating point type*/
Sqlite3_bind_double()
Break;
Case SQLITE_TEXT:
/*Process string type*/
Sqlite3_bind_text()
Break;
Case SQLITE_BLOB:
/* handle binary type */
Sqlite3_bind_blob
Break;
Case SQLITE_NULL:
Sqlite3_bind_null(stmt, index);
Break;
}
}

Sqlite3_step(stmt); //execution
Sqlite3_reset(stmt); //Restore the compiled SQL statement to its initial state, retaining the resources associated with the statement
}

Sqlite3_finalize(stmt); //End statement, release statement handle

If(sqlite3_exec(pdb, "commit", NULL, NULL, &errmsg) != SQLITE_OK)
{
Error handling return ERROR;
}

Summary
This article describes in detail the implementation of SQLite in creating, modifying, querying the database interface use, including single-SQL statements, multi-SQL statements and parameterized SQL. Mainly from four scenarios to expand the description, open the Close database connection, UPDATE statements, query statements and parameterized statements, and for each of the use of the scene, gave the corresponding code demonstration, hope to be familiar with the use of sqlite helpful.

SQLite uses (iii) && core API Usage