Android Database code optimization (2)-From SQLite

Speaking from SQLite

Without the foundation of SQLite, we just learn from the SQLite API of the Android package, which inevitably limits our thinking. So, we still need to honestly learn sqlite from the beginning.
When we have a sqlite martial arts, then to see the Android package, you can better understand how to play the strengths of SQLite.

There is only one C file at the core of SQLite, and a file is also available for accessing the DB. So, we can completely embed it in another program.

On a Mac, you can install it through homebrew. After installation, we can write the code using the Sqlite3 API.

Let's start with an example of a sqlite3 call to make.

Let's find an example of the easiest way to open a SQLite database on the Web:

#include <stdio.h>#include <sqlite3.h>#include <stdlib.h>intMainintargcChar*argv[]) {sqlite3 *db;Char*zerrmsg =0;intRc rc = Sqlite3_open ("Contacts.db", &db);if(RC) {fprintf(stderr,"Can ' t Open database:%s\n", sqlite3_errmsg (db));Exit(0); }Else{fprintf(stderr,"opened database successfully\n"); } sqlite3_close (db);}

Let's just compile it first, regardless of what it means:

-o test_sqlite test.-lsqlite3

Then run it, you need to have a local database called Contacts.db.

./test_sqlite

The output is:

Opened database successfully

From the above example, we can learn two easy-to-understand Api:sqlite3_open and sqlite3_close.

SQLite3 is a vdbe-based database engine

With a functioning environment, let's take a look at the structure of the SQLite database engine:

From this official chart, we can see that in addition to tools and test code, the core of SQLite is divided into three parts: The core, the compiler and the backend.

The core part is the processing of the SQL command, which is compiled by the compiler into code that the VDBE (Virtual Engine) can execute.
The backend is the part that really operates on the database, including the B-tree lookup structure.

Like to focus on the attention of the students, the point is: Call the SQLite3 Database code optimization of the first point is to save the compiled bytecode, the next time you use the direct call.
Such an important feature of the SQLite3 API is of course available, which is what we will learn extensively later using the Sqlite3_prepare and SQLITE3_PREPARE_V2 functions.
Android also has the same encapsulation, which provides sqlitestatement to save precompiled code.

A classmate asked, my SQL statement is not immutable, the arguments in the statement often change, so that the compiled code is useless ah?
This is certainly considered in the design of SQLite3, the compiled statement, can support parameters. We first compile with SQLITE3_PREPARE_V2 and then bind the parameters with the sqlite3_bind_* function. The next time you change the parameters, call Sqlite3_reset to clear out the binding information, and then re-use sqlite3_bind_* to do the binding new parameters, you can.

A function that invokes sqlite3 to implement database operations can be applied in the following steps:
1. Construct SQL statements based on business requirements
2. Call the SQLITE3_PREPARE_V2 function to compile the SQL statement
3. If there are arguments, call the sqlite3_bind_* function to bind the parameters
4. Call the Sqlite3_step function to perform a SQL operation until all operations are complete
5. The next time you use the 2nd step compiled statement, call the Sqlite3_reset function cleanup parameter. Then repeat steps 3rd.
6. Finally, call Sqlite3_finalize to destroy the precompiled statement

Let's start with the actual operation, first of all, let's give an example of select.
For example, we take a simplified version of the calls table in the Android contact database:

CREATE TABLE calls (_id INTEGER PRIMARY KEY AUTOINCREMENT,sourceid TEXT,number TEXT...);

Although there are many fields, we should focus on the ID and number.

How to write a query statement

Let's start with half, we pick the _id and number columns, and see if the returned data is two columns:
The core code is as follows:

    rc = sqlite3_prepare_v2(db, sql_select_caller, -1, &stmt, &tail);    rc = sqlite3_step(stmt);    int ncols = sqlite3_column_count(stmt);    printf("The column counts of calls is:%d\n", ncols);    sqlite3_finalize(stmt);

The full version of the code for everyone to experiment:

#include <stdio.h>#include <sqlite3.h>#include <stdlib.h>intMainintargcChar*argv[]) {sqlite3 *db;Char*zerrmsg =0;intRcConst Char*sql_select_caller ="Select _id, number from calls"; Sqlite3_stmt *stmt;Const Char*tail; rc = Sqlite3_open ("Contacts.db", &db);if(RC) {fprintf(stderr,"Can ' t Open database:%s\n", sqlite3_errmsg (db));Exit(0); }Else{fprintf(stderr,"opened database successfully\n"); }//select _id, number from callsrc = SQLITE3_PREPARE_V2 (db, Sql_select_caller,-1, &stmt, &tail); rc = Sqlite3_step (stmt);intNcols = Sqlite3_column_count (stmt);printf("The column counts of calls is:%d\n", ncols);    Sqlite3_finalize (stmt); Sqlite3_close (db);}

Next we call Sqlite3_step to read each record, adding the following paragraph:

    while(rc == SQLITE_ROW){        printf("calls ID=%d,\t",sqlite3_column_int(stmt,0));        printf("number=%s\n",sqlite3_column_text(stmt,1));        rc = sqlite3_step(stmt);    }

If the result returned by Sqlite3_step is Sqlite_row, this time the execution takes a record that matches the criteria. Fetch one record at a time.

The complete code is as follows:

#include <stdio.h>#include <sqlite3.h>#include <stdlib.h>intMainintargcChar*argv[]) {sqlite3 *db;Char*zerrmsg =0;intRcConst Char*sql_select_caller ="Select _id, number from calls"; Sqlite3_stmt *stmt;Const Char*tail; rc = Sqlite3_open ("Contacts.db", &db);if(RC) {fprintf(stderr,"Can ' t Open database:%s\n", sqlite3_errmsg (db));Exit(0); }Else{fprintf(stderr,"opened database successfully\n"); }//select _id, number from callsrc = SQLITE3_PREPARE_V2 (db, Sql_select_caller,-1, &stmt, &tail); rc = Sqlite3_step (stmt);intNcols = Sqlite3_column_count (stmt);printf("The column counts of calls is:%d\n", ncols); while(rc = = Sqlite_row) {printf("Calls id=%d,\t", Sqlite3_column_int (stmt,0));printf("number=%s\n", Sqlite3_column_text (stmt,1));    rc = Sqlite3_step (stmt);    } sqlite3_finalize (stmt); Sqlite3_close (db);}

The output is the following example:

The column counts ofCalls is:2Calls Id=1, Number=18600009876Calls Id=2, Number=18600019876Calls Id=3, Number=18600029876Calls Id=4, Number=18600039876Calls Id=5, Number=18600049876Calls Id=6, Number=18600059876Calls Id=7, Number=18600069876Calls Id=8, Number=18600079876Calls Id=9, Number=18600089876Calls Id=Ten, Number=18600099876

How to write non-query statements

The above example is for the query statement, we will give an example of non-query statements. For example, let's try an insert example.

voidInsert_item (Sqlite3 *db) {Const Char*sql_insert_sample ="Insert or ignore into calls (_id,number) values (? 1,?2);"; sqlite3_stmt *stmt = NULL;Const Char*tail = NULL;intrc = SQLITE3_PREPARE_V2 (db, Sql_insert_sample,-1, &stmt, &tail); Sqlite3_bind_int (stmt,1, +); Sqlite3_bind_text (stmt,2,"01084993677", One, NULL); rc = Sqlite3_step (stmt);if(rc = = Sqlite_done) {printf("last inserted row id=%ld\n", (Long) Sqlite3_last_insert_rowid (db)); }Else{printf("Insert failed!"); } sqlite3_finalize (stmt);}

The output is as follows:

Last inserted row id=0

Summary

Above, we learned how to use the SQLite3 API for both query and non-query scenarios.
The rest of the work is mostly about constructing SQL statements and processing the returned results.

Android Database code optimization (2)-From SQLite