Introduction and Analysis of SQLite (II)-Design and Concept

Preface: Thank you for your attention. I didn't expect so many people to be interested. I am also excited and stressed that I have been using SQLite for just a few days and have never used it in actual development. I only came to study it based on the needs of recent projects, so I am very worried about whether my article will be wrong or mislead others. However, I would like to share my learning achievements with you, so if you think I have something wrong, I hope you will give me some advice.
I originally intended to start directly with VDBE because it plays a crucial role and is the core of the entire SQLite system and analyzes the source code. However, considering this is a series of articles, I hope to answer all the questions, so we should start with the basic concepts. For Beginners, if they do not have these concepts, they will continue. Now, let's start Chapter 2. As this chapter contains a lot of content, I will discuss it in two parts. Next, let's start the first part.

1. API
It consists of two parts: core API and extension API)
Core API functions implement basic database operations: connect to the database, process SQL statements, and traverse result sets. It also includes some practical functions, such as String Conversion, Operation Control, debugging, and error handling.
The extension API expands SQLite by creating your custom SQL functions.

1.1 some new features of SQLite Version 3:
(1) SQLite APIs are all re-designed, from 15 Functions in the second version to 88 functions. These functions include functions that support UTF-8 and UTF-16 encoding.
(2) Improve concurrency performance. The lock escalation model (lock escalation model) is introduced in the lock subsystem to solve the starvation problem of the second version of the write process (this problem is a problem that any DBMS must face ). This model ensures that the write process obtains the Exclusive Lock according to the first-come-first-served algorithm ). Even by writing the results into the Temporary Buffer, the write process can start to work before obtaining the exclusion lock. This improves the performance of applications with high write requirements by 400% (reference ).
(3) improved B-tree. The B + tree is used for tables, which greatly improves the query efficiency.
(4) The most important change in SQLite 3 is its storage model. The second edition only supports text models and supports five local data types.
In short, SQLite Version 3 is very different from SQLite Vertion 2, which greatly improves flexibility, features, and performance.

1.2 The Principal Data Structures)
SQLite consists of many components, including parser, tokenize, and virtual machine. But from the programmer's point of view, the most important thing to know is: connection, statements, B-tree and pager. The relationship between them is as follows:

 

It tells us three main aspects that need to be known in programming: API, Transaction and Locks ). Technically, B-tree and pager are not part of APIs. But they play a key role in transactions and locks (will be discussed later ).

 

1.3. Connections and Statements
Connection and statement are two main data structures involved in SQL command execution. They are used for almost all operations through APIS. A Connection represents a connection (connection represents a single Connection to a database as well as A single transaction context) in an independent transaction environment ). Each statement is associated with a connection. It usually represents a compiled SQL statement. Internally, it is represented by VDBE bytecode. Statement includes all required to execute a command, including resources required to save the execution status of the vdbe program, B-tree cursor pointing to the hard disk record, and parameters.

1.4. B-tree and pager
A connection can have multiple database objects-a primary database and an attached database. Each database object has a B-tree object, a B-tree has a pager object (the object here is not an object-oriented "object", just to clarify the problem ).
Statement eventually reads or writes data from the database through the B-tree and pager of the connection, and traverses the records stored on the page through the B-tree cursor (cursor. The cursor loads the number from disk to memory before accessing the page, which is the task of pager. At any time, if B-tree requires a page, it will request pager to read data from the disk, and then load the page to the page buffer (page cache, b-tree and the associated cursor can access records on the page.
If cursor changes the page, pager must save the original page in a special way to prevent transaction rollback. In general, pager is responsible for reading and writing databases, managing memory caches and pages, and managing transactions, locks, and crash recovery (these will be detailed in the transaction section ).
In short, you must know two things about connection and transaction:
(1) Any operation on the database, a connection exists in a transaction.
(2) A connection will never have multiple transactions at the same time.
Whenever a connection does anything with a database, it always operates under exactly one
Transaction, no more, no less.

1.5 core API

The core API is mainly related to executing SQL commands. In essence, there are two ways to execute SQL statements: prepared query and wrapped query. Prepared query consists of three phases: preparation, execution, and finalization. In fact, wrapped query is only used to encapsulate the three prepared query processes, and will eventually be converted to the execution of prepared query.

1.5.1 The Connection Lifecycle)
It is the same as most databases and consists of three processes:
(1) Connect to the database ):
Each SQLite database is stored in a separate operating system file. connect to and open the c api of the database: sqlite3_open (). Its implementation is located in the main. c file, as follows:
Int sqlite3_open (const char * zFilename, sqlite3 ** ppDb)
{
Return openDatabase (zFilename, ppDb, SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE, 0 );
}
When a database is connected to a disk, SQLite opens a file if the database file exists. If the file does not exist, SQLite assumes that you want to create a new database. In this case, SQLite does not immediately create a file on the disk. Only when you write data to the database will you create a file, such as creating tables, views, or other database objects. If you open a data file without doing anything, and close it, SQLite will create a file, just an empty file.
Another reason for not creating a new file immediately is that some database parameters, such as encoding and page size, are only set before database creation. By default, the page size is 1024 bytes, but you can select a number with a power of 2 between 512-32768 bytes. In some cases, large pages can process a large amount of data more effectively.
(2) execute the transaction (Perform transactions ):
All commands are executed within transactions. By default, transactions are automatically committed, that is, every SQL statement runs in an independent transaction. You can also use BEGIN .. COMMIT to manually submit a transaction.
(3) Disconnect from the database ):
It is mainly used to close database files.

1.5.2 run Prepared Query
As mentioned above, Prepared Query is a way for SQLite to execute all SQL commands, including the following three processes:
(1) Prepared Query:
Analyzer (parser), tokenizer (tokenizer), and code generator (code generator) Compile SQL Statement into VDBE bytecode. The Compiler creates a statement handle (sqlite3_stmt ), it includes bytecode and other resources that execute commands and traverse the result set.
The corresponding c api is sqlite3_prepare (), which is located in the prepare. c file, as follows:
Int sqlite3_prepare (
Sqlite3 * db,/* Database handle .*/
Const char * zSql,/* UTF-8 encoded SQL statement .*/
Int nBytes,/* Length of zSql in bytes .*/
Sqlite3_stmt ** ppStmt,/* OUT: A pointer to the prepared statement */
Const char ** pzTail/* OUT: End of parsed string */
){
Int rc;
Rc = sqlite3LockAndPrepare (db, zSql, nBytes, 0, ppStmt, pzTail );
Assert (rc = SQLITE_ OK | ppStmt = 0 | * ppStmt = 0);/* VERIFY: F13021 */
Return rc;
}
(2) Execution:
The virtual machine executes bytecode. the execution process is a step-wise process. Each step is started by sqlite3_step () and a segment of bytecode is executed by VDBE. The byte code is compiled by sqlite3_prepare and executed by sqlite3_step () on the virtual machine. In the process of traversing the result set, it returns SQLITE_ROW. When the result ends, it returns SQLITE_DONE.
(3) Finalization:
VDBE closes statement and releases resources. The corresponding c api is sqlite3_finalize ().

It is easier to understand the process:

 

Finally, we end this section with a specific example to discuss transactions in the next section.

Code
# Include <stdio. h>
# Include <stdlib. h>
# Include "sqlite3.h"

# Include <string. h>

Int main (int argc, char ** argv)
{
Int rc, I, ncols;
Sqlite3 * db;
Sqlite3_stmt * stmt;
Char * SQL;
Const char * tail;
// Open Data
Rc = sqlite3_open ("foods. db", & db );

If (rc ){
Fprintf (stderr, "Can't open database: % s \ n", sqlite3_errmsg (db ));
Sqlite3_close (db );
Exit (1 );
}

SQL = "select * from episodes ";
// Preprocessing
Rc = sqlite3_prepare (db, SQL, (int) strlen (SQL), & stmt, & tail );

If (rc! = SQLITE_ OK ){
Fprintf (stderr, "SQL error: % s \ n", sqlite3_errmsg (db ));
}

Rc = sqlite3_step (stmt );
Ncols = sqlite3_column_count (stmt );

While (rc = SQLITE_ROW ){

For (I = 0; I <ncols; I ++ ){
Fprintf (stderr, "'% S'", sqlite3_column_text (stmt, I ));
}

Fprintf (stderr, "\ n ");

Rc = sqlite3_step (stmt );
}
// Release statement
Sqlite3_finalize (stmt );
// Close the database
Sqlite3_close (db );

Return 0;
}

/Files/hustcat/2008/Learn.rar

 

For more information, see The Definitive Guide to SQLite.