In-depth Summary of SQLite basic operations

Sqlite provides some C function interfaces that you can use to operate databases. By using these interfaces, you can pass some standard SQL statements (in char * type) to the sqlite function, and sqlite will operate the database for you. Sqlite is a file-type database like MS access. That is to say, a database is a file. Many tables can be created in this database, and indexes and triggers can be created. However, what it actually gets is a file. Back up this file to back up the entire database. Sqlite does not need any database engine, which means that if you need sqlite to save some user data, you do not even need to install the database.

The following describes basic database operations.
1. Basic Process
(1) Key Data Structure:
The most common type in sqlite is sqlite3. Since the database is opened, sqlite needs to prepare memory for this type until the database is closed. This type is used throughout the process. When the database is opened, this type of variable represents the database you want to operate on. The following is a detailed introduction.
(2) Open the database:
Int sqlite3_open (file name, sqlite3 **); use this function to start database operations. Two parameters are required. One is the database file name, such as .. \ test \ testDatabase. db.
The file name does not need to exist. If the file does not exist, sqlite will automatically create it. If it exists, try to open it as a database file. The sqlite3 ** parameter is the key data structure mentioned above. Do not close the underlying details of this structure.
The Return Value of the function indicates whether the operation is correct. If it is SQLITE_ OK, the operation is normal. Related return values sqlite defines some macros. For more information about these macros, see the sqlite3.h file. Detailed definitions are provided.
(3) shut down the database:
Int sqlite3_close (sqlite3 *); if you have enabled a database with sqlite3_open, do not forget to use this function to close the database at the end.
Sqlite database operation example

Copy codeThe Code is as follows: # include "./sqlite3.h"
Int main (int, char **)
{
Sqlite3 * db = NULL; // declare sqlite key structure pointer
Int result;
// You need to input the pointer of the db pointer,
// Because the sqlite3_open function needs to allocate memory for this pointer, And the db pointer must point to this memory Zone
Result = sqlite3_open (".. \ test \ testDatabase. db", & db); // open the database
If (result! = SQLITE_ OK)
{
Return-1; // failed to open the database
}
// Database operation code
//... -
// The database is successfully opened.
Sqlite3_close (db); // close the database
Return 0;
}

This is a database operation process.

2. SQL statement operations (how to use sqlite to Execute standard SQL syntax)
(1) execute the SQL statement: int sqlite3_exec (sqlite3 *, const char * SQL, sqlite3_callback, void *, char ** errmsg); this is the function for executing an SQL statement.
Parameter description:
The first parameter is a pointer obtained by the open function. It is the key data structure.
The 2nd const char * SQL is an SQL statement ending with \ 0.
The 3rd sqlite3_callback parameter is a callback. After this statement is executed, sqlite3 will call the function you provided.
The third parameter void * is the pointer you provide. You can pass any pointer parameter here. This parameter will eventually be passed to the callback function. If you do not need to pass the pointer to the callback function, you can enter NULL. Let's take a look at the method of the callback function and the use of this parameter.
The first parameter char ** errmsg is the error message. Note the pointer. Sqlite3 contains many fixed error messages. After sqlite3_exec is executed, you can view this pointer (directly printf ("% s \ n", errmsg) When execution fails to get a string, this string of Information tells you where the error is. The sqlite3_exec function points the pointer you provided to the error message by modifying the pointer you passed in, so that the sqlite3_exec function can use this char * to get a specific error message.
Note: Normally, either sqlite3_callback or void * after it can be NULL. If this parameter is set to NULL, callback is not required. For example, if you perform insert and delete operations, there is no need to use callback. When you do select, you need to use a callback, because sqlite3 checks the data and uses a callback to tell you what data has been found.
(2) exec callback: typedef int (* sqlite3_callback) (void *, int, char **, char **); your callback function must be defined as the type of the above function.

Sqlite database operation example:Copy codeThe Code is as follows: // sqlite3 callback function
// Each time sqlite finds a record, this callback is called once.
// Para is the void * parameter you pass in sqlite3_exec
// Using the para parameter, you can pass in some special pointers (such as class pointers and structure pointers), and then forcibly convert them to the corresponding type.
// (This is the void * type, which must be converted to your type ). Then operate on the data
// N_column indicates the number of fields in the record (that is, the number of columns in the record)
// Char ** column_value is the key value. The retrieved data is saved here. It is actually a one-dimensional array (not a two-dimensional array ),
// Each element is a char * value and a field content (represented by a string and ended with \ 0)
// Char ** column_name corresponds to column_value, indicating the field name of this field
Int LoadMyInfo (void * para, int n_column, char ** column_value, char ** column_name)
{
// Here, I do not use the para parameter. Ignore its existence.
Int I;
Printf ("the record contains % d fields \ n", n_column );
For (I = 0; I <n_column; I ++)
{
Printf ("field name: % s?> Field Value: % s \ n ", column_name [I], column_value [I]);
}
Printf ("\ n ");
Return 0;
}

Int main (int, char **)
{
Sqlite3 * db;
Int result;
Char * errmsg = NULL;
Result = sqlite3_open (".. \ test \ testDatabase. db", & db );
If (result! = SQLITE_ OK)
{
Return-1; // failed to open the database
}
// Database operation code
// Create a test table named MyTable_1 with two fields: ID and name. ID is an automatically added type,
// You do not specify this field for future insert operations. This field will be added from 0.
Result = sqlite3_exec (db, "create table MyTable_1 (ID integer primary key autoincrement, name nvarchar (32)", NULL, NULL, errmsg );
If (result! = SQLITE_ OK)
{
Printf ("failed to create table, error code: % d, error cause: % s \ n", result, errmsg );
}
// Insert some records
Result = sqlite3_exec (db, "insert into MyTable_1 (name) values ('Walk ')", 0, 0, errmsg );
If (result! = SQLITE_ OK)
{
Printf ("Insert record failed, error code: % d, error cause: % s \ n", result, errmsg );
}

Result = sqlite3_exec (db, "insert into MyTable_1 (name) values ('Cyclists ')", 0, 0, errmsg );
If (result! = SQLITE_ OK)
{
Printf ("insert record failed, error code: % d, error cause: % s \ n", result, errmsg );
}

Result = sqlite3_exec (db, "insert into MyTable_1 (name) values ('chelay')", 0, 0, errmsg );
If (result! = SQLITE_ OK)
{
Printf ("insert record failed, error code: % d, error cause: % s \ n", result, errmsg );
}
Result = sqlite3_exec (db, "select * from MyTable_1", LoadMyInfo, NULL, errmsg); // start querying the database sqlite3_close (db); // close the database
Return 0;
}

The above example shows how to open a database and perform basic database operations.
(3) do not use callback to query the database
Sqlite3_exec uses a callback to perform the select Operation. There is also a way to directly query without callback. However, I personally think the callback is good, because the code can be more neat, but it is very troublesome to use the callback. You must declare a function. If this function is a class member function, you have to declare it as static (when the C ++ member function actually hides a parameter: this, C ++ calls a member function of the class, the class pointer is passed as the first parameter of the function. The result is inconsistent with the parameters of the sqlite callback function described above. Only when the member function is declared as static does it have unnecessary implicit this parameter ). Although the callback code looks neat, sometimes you still want select queries that are not called back. This can be done through the sqlite3_get_table function.
Int sqlite3_get_table (sqlite3 *, const char * SQL, char *** resultp, int * nrow, int * ncolumn, char ** errmsg );
Parameter description:
The 1st parameters are no longer mentioned. refer to the previous example.
The 2nd parameters are SQL statements, which are the same as SQL statements in sqlite3_exec. It is a common char * string ending with \ 0.
The first parameter is the query result, and it is still a one-dimensional array (do not think it is a two-dimensional array, or a three-dimensional array ). Its memory layout is: the first line is the field name, followed by the value of each field. The following is an example.
The 4th parameter indicates how many records are queried (that is, how many rows are queried ).
The number of fields (columns) of the 5th parameter ).
The first parameter is the error message, which is the same as above.
Sqlite database operation example:Copy codeThe Code is as follows: int main (int, char **)
{
Sqlite3 * db;
Int result;
Char * errmsg = NULL;
Char ** dbResult; // It is of the char ** type and has two * numbers.
Int nRow, nColumn;
Int I, j;
Int index;
Result = sqlite3_open (".. \ test \ testDatabase. db", & db );
If (result! = SQLITE_ OK)
{
Return-1; // failed to open the database
}
// Database operation code
// Assume that the MyTable_1 table has been created.
// Start the query. The passed dbResult is already char **. Here, an & get address character is added, and the passed result is char ***
Result = sqlite3_get_table (db, "select * from MyTable_1", & dbResult, & nRow, & nColumn, & errmsg );
If (SQLITE_ OK = result) // query successful
{
Index = nColumn; // the data in the first row before dbResult is the field name, which is the real data starting from the nColumn index.
Printf ("% d records \ n", nRow );
For (I = 0; I <nRow; I ++)
{
Printf ("% d records \ n", I + 1 );
For (j = 0; j <nColumn; j ++)
{
Printf ("field name: % s success> Field Value: % s \ n", dbResult [j], dbResult [index]);
// The Field Value of dbResult is continuous. From The 0th index to the nColumn-1 index, it is the field name.
// Starting from the nColumn index, fields are followed by values,
// It represents a two-dimensional table (traditional row-column representation) in a flat form.
++ Index;
}
Printf ("\ n ");
}
}
// Here, the char query result is released no matter whether the database query is successful or not, and the function provided by sqlite is used for release.
Sqlite3_free_table (dbResult );
Sqlite3_close (db); // close the database
Return 0;
}

Until this example, the common usage of sqlite3 has been described. The above method can meet the vast majority of database requirements.

3. Transaction Processing
Sqlite supports transaction processing. If you know that you want to delete a lot of data synchronously, do not make them into a unified transaction. One sqlite3_exec is usually a transaction. If you want to delete 10 thousand pieces of data, sqlite will do 10 thousand times: start a new transaction-> delete a piece of data-> commit a transaction-> Start a new transaction->... . This operation is very slow. Because time is spent on starting and committing transactions. You can make these similar operations into a transaction, so that if the operation is incorrect, you can roll back the transaction. Transactions do not have special interface functions. They are just a common SQL statement:
They are as follows:Copy codeThe Code is as follows: int result;
Result = sqlite3_exec (db, "begin transaction", 0, 0, & zErrorMsg); // start a transaction
Result = sqlite3_exec (db, "commit transaction", 0, 0, & zErrorMsg); // submit the transaction
Result = sqlite3_exec (db, "rollback transaction", 0, 0, & zErrorMsg); // roll back the transaction