In depth summary of SQLite basic operations
- 2020-05-17 06:47:02
- OfStack
sqlite provides an interface to C functions that you can use to manipulate databases. By using these interfaces, pass some standard sql statements (of type char *) to the sqlite function, and sqlite will operate the database for you. sqlite, like access1 of MS, is a file database. That is to say, a database is a file. This database can build many tables, indexes, triggers, etc., but what it actually gets is a file. Backing up this file backs up the entire database. sqlite doesn't need any database engine, which means if you need sqlite to save some user data, you don't even need to install the database.
Let's start with basic database operations.
1. Basic process
(1) key data structure:
The most commonly used type in sqlite is sqlite3 *. sqlite prepares memory for this type from the time the database is opened until the database is closed, and this type is used throughout the process. When the database is opened, this type of variable represents the database you are working on. More on that below.
(2) open the database:
int sqlite3_open(file name, sqlite3 **); Start the database operation with this function. Need to pass in two parameters, 1 is the database file name, such as:.. \ \ test \ \ testDatabase db.
The file name does not need to be 1 definite, if the file does not exist, sqlite will automatically create it. If it exists, try opening it as a database file. The sqlite3 ** parameter is the key data structure mentioned above. How about the underlying details of this structure, you don't close it.
The return value of the function indicates whether the operation is correct or not, and if it is SQLITE_OK, it indicates that the operation is normal. The associated return value sqlite defines some macros. The specific meaning of these macros can be referred to the sqlite3.h file. There are detailed definitions.
(3) close the database:
int sqlite3_close (sqlite3 *); If you opened a database with sqlite3_open, don't forget to close the database with this function at the end.
Example of sqlite database operation
#include "./sqlite3.h"
int main( int , char** )
{
sqlite3 * db = NULL; // The statement sqlite Key structure pointer
int result;
// Need to db The pointer to this pointer,
// because sqlite3_open The function allocates memory for this pointer, and also lets db The pointer points to this memory area
result = sqlite3_open("..\\test\\testDatabase.db", &db);// Open the database
if( result != SQLITE_OK )
{
return -1; // Database opening failed
}
// Database operation code
// ... -
// Database opened successfully
sqlite3_close( db ); // Shut down the database
return 0;
}
This is a database operation process.
2. SQL statement operation (how to use sqlite to execute standard sql syntax)
(1) execute sql statement: int sqlite3_exec(sqlite3*, const char *sql, sqlite3_callback, void *, char **errmsg); This is the function that executes an sql statement.
Parameter description:
The first argument is no longer a pointer to the open function. That's the key data structure.
The second parameter, const char *sql, is an sql statement ending in \0.
The third parameter, sqlite3_callback, is a callback. When the statement is executed, sqlite3 will call the function you provided.
The fourth parameter, void *, is the pointer you provide. You can pass any pointer to this point, and 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 fill in NULL. We'll look at how the callback function is written in a second, and how this parameter is used.
The fifth parameter, char ** errmsg, is an error message. Notice it's a pointer to a pointer. There are a lot of fixed error messages in sqlite3. After executing sqlite3_exec, you can refer to this pointer (direct printf("%s\n",errmsg) in case of a failure) to get a string of information that tells you what went wrong. The sqlite3_exec function modifies the pointer you pass in by pointing the pointer you provide to the error message, so that the sqlite3_exec function can get a specific error message from the char*.
Note: in general, NULL can be filled in both sqlite3_callback and void * after it. NULL means you do not need to call back. If you do insert and delete, there is no need to use a callback. And when you do select, you have to use a callback, because sqlite3 is looking up the data, and you have to use a callback to tell you what you're looking up.
(2) exec callback: typedef int (*sqlite3_callback)(void*,int,char**, char**); Your callback function must be defined as the type of the function above.
Example of sqlite database operation:
//sqlite3 The callback function of
// sqlite Each to check the 1 Is called 1 Call this callback
//para Are you in sqlite3_exec In the incoming void * parameter
// through para Parameter, you can pass in 1 Special Pointers (such as class Pointers, structure Pointers), and then cast the corresponding type inside
// Here's one void* Type, which must be cast to your type to be usable. Then manipulate the data
//n_column Is this 1 A record of how many fields there are ( That's how many columns this record has )
//char ** column_value It's a key value, all the data that you find is stored here, it's actually a key value 1 Dimension array (don't think it is 2 Dimension array),
// every 1 All of them 1 a char * Value, it is 1 Field content (represented as a string, so \0 At the end)
//char ** column_name with column_value Is the corresponding, representing the field name of this field
int LoadMyInfo( void * para, int n_column, char ** column_value, char ** column_name )
{
// Here, I'm not going to use it para Parameters. Ignore it .
int i;
printf( " Record contains %d A field \n", n_column );
for( i = 0 ; i < n_column; i ++ )
{
printf( " The field name :%s ?> The field values :%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; // Database opening failed
}
// Database operation code
// Create a test table called MyTable_1 , there are 2 A field: ID and name . Among them ID is 1 Two auto-incrementing types,
// after insert Instead of specifying this field, it will take care of itself 0 Began to increase
result = sqlite3_exec( db, "create table MyTable_1( ID integer primary key autoincrement, name nvarchar(32) ))", NULL, NULL, errmsg );
if(result != SQLITE_OK )
{
printf(" Table creation failed, error code :%d , the wrong reason :%s\n", result, errmsg );
}
// insert 1 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 , the wrong reason :%s\n " , result, errmsg );
}
result = sqlite3_exec( db,"insert into MyTable_1( name ) values (' Ride a bike ')", 0, 0, errmsg);
if(result != SQLITE_OK )
{
printf(" Insert record failed, error code :%d , the wrong reason :%s\n", result, errmsg );
}
result = sqlite3_exec( db, "insert into MyTable_1( name ) values ( ' A bus ')", 0, 0, errmsg );
if(result != SQLITE_OK )
{
printf( " Insert record failed, error code :%d , the wrong reason :%s\n", result, errmsg );
}
result = sqlite3_exec( db, "select * from MyTable_1", LoadMyInfo, NULL, errmsg );// Start querying the database sqlite3_close( db ); // Shut down the database
return 0;
}
With the example above, you should know how to open a database and how to do basic database operations.
(3) no callback is used to query the database
sqlite3_exec USES callbacks to perform select operations. There is also a way to query directly without needing a callback. However, I personally feel good or callback, because the code can be more tidy, but with a callback is very troublesome, you have to declare a function, if this function is a class member function, you also have to declare it into static (C + + member function actually hides a parameters: this, C + + calling class member functions, implied the class pointer as the first argument of the function. As a result, this results in a mismatch with the parameters of the sqlite callback function described earlier. It is only when the member function is declared as static that it has no redundant implicit this arguments. Although callbacks look neat, there are times when you want a non-callback select query. 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:
I won't say much about the first argument, but let's look at the previous example.
The second parameter is the sql statement, which is the same as sql in sqlite3_exec. Is a very normal char * string ending in \0.
The third parameter is the query result, which is still a 1-dimensional array (don't think of it as a 2-dimensional array, let alone a 3-dimensional array). The memory layout is: line 1 is the field name, followed by the value of each field. Let's use an example.
The fourth parameter is how many records are queried (that is, how many rows are retrieved).
The fifth parameter is how many fields (how many columns).
The sixth parameter is the error message, just like the previous one, which I won't talk about here.
Example of sqlite database operation:
int main( int , char ** )
{
sqlite3* db;
int result;
char* errmsg = NULL;
char **dbResult; // is char ** Type, two * No.
int nRow, nColumn;
int i , j;
int index;
result = sqlite3_open("..\\test\\testDatabase.db", &db );
if( result != SQLITE_OK )
{
return -1; // Database opening failed
}
// Database operation code
// Let's say I created it earlier MyTable_1 table
// Start the query, incoming dbResult Is already char ** I'm going to add it here 1 a & You take the address, and you pass it in char ***
result = sqlite3_get_table( db, "select * from MyTable_1", &dbResult, &nRow, &nColumn, &errmsg );
if( SQLITE_OK == result ) // The query is successful
{
index = nColumn; // Said earlier dbResult In front of the first 1 The row data is the field name from nColumn Index start is the real data
printf(" To check the %d records \n", nRow );
for( i = 0; i < nRow ; i++ )
{
printf( " The first %d records \n", i+1 );
for( j = 0 ; j < nColumn; j++ )
{
printf(" The field name :%s ß> The field values :%s\n", dbResult[j], dbResult [index]);
// dbResult The field values are continuous from the first 0 The index to the first nColumn - 1 Indexes are field names
// From the first nColumn You start with the index, and then you have the field values,
// It is the 1 a 2 Dimension table (traditional row and column representation) is used 1 It's in a flat form
++index;
}
printf( "\n" );
}
}
// At this point, whether the database query is successful or not, it is released char** Query the results using sqlite Provides the functionality to be released
sqlite3_free_table( dbResult );
sqlite3_close( db );// Shut down the database
return 0;
}
So far as this example is concerned, the common usage of sqlite3 has been covered. With the above method, can completely deal with the vast majority of database requirements.
3. Transaction processing
sqlite supports transaction processing. If you know you're going to delete a lot of data at the same time, don't make it a one transaction. Usually 1 sqlite3_exec is 1 transaction. If you want to delete 10,000 pieces of data, sqlite does it 10,000 times: start a new transaction - > Delete 1 data - > Commit transaction - > Start a new business - > ... In the process. This operation is slow. Because time is spent starting and committing transactions. You can make one transaction out of these same types of operations so that you can roll back the transaction if you make a mistake. The operation of the transaction has no special interface function, it is just a normal sql statement:
They are as follows:
int result;
result = sqlite3_exec( db, "begin transaction", 0, 0, &zErrorMsg ); // start 1 A transaction
result = sqlite3_exec( db, "commit transaction", 0, 0, &zErrorMsg ); // Commit the transaction
result = sqlite3_exec( db, "rollback transaction", 0, 0, &zErrorMsg ); // Roll back the transaction