SQLite Design and Concepts
There are two main categories
- core API. Basic SQL operation
- extension API. Create custom SQL operation .
Basic data structure
The components that need to be understood are connections 、statments、B Trees 、pager.
To write well SQLite Code , The basic concepts that need to be understood ,API、 Transactions and locks .
The connection and statments
These two data structures and the basis of query statements . A connection represents a connection to the database , It's also a transaction context .statments From these connections .statments Inside by VDBE Bytecode means .
B-tree and pager
Each database connection can have multiple databases . Each database object has one B-tree object , every last B-tree The object has a pager object .
pager My work includes putting page Import from disk into memory 、 Write database 、 Manage affairs 、 Handle locks and from crash And so on .
A database connection is operated in a transaction , A database connection does not have two transactions at the same time .
Two ways to perform SQL sentence .
- prepared query. SQLite The ultimate way to execute a command . There are three stages
- wrapped query
Connect to database
sqlite3_open() Pass in :memory: Or empty string as the name of the database , Will create a database in memory .
If the database pagesize And the operating system pagesize identical , Will increase IO efficiency .
function prepared query
- prepation: Convert a string to VDBE Bytecode .sqlite3_prepare_v2() Generate sqlite3_stmt Handle , Including the compiled bytecode and all the resources that need to execute the command and iterate the result set .
- execution:sqlite3_step send VDBE Step bytecode . The first call gets some kind of lock .
- finalization:sqlite3_finalize() close statment, Release related resources .
Use parameterized SQL
It includes two kinds of parameter binding
The advantage of using parameter binding
- No compilation required , It can be executed statment many times . It only needs reset, And then again bind, function .
- Automatically handle escape characters , Avoid database injection and syntax errors .
function wrapped query
sqlite3_exec() and sqlite3_get_table()
exec() Parse command , Identify individual statements , And deal with it in turn .
sqlite3_errcode() Returns the error code of the last function executed . Use sqlite3_errmsg() Provide a description of the last error .
format SQL sentence
sqlite3_mprintf(). similar sprintf(),%q and %s similar , But it turns every single quotation mark into two consecutive , Prevent database Injection .%Q A single quotation mark is added to the beginning and end of the string , A null pointer becomes NULL
Use registration filter or callback Function to achieve that when a specific event occurs, it will be monitor,control.
There are three hook function
- wal_hook()（3.7 In the version ）
shared cache mode: Use multiple threads to manage multiple SQL Connect , These connections share page cache, To reduce the server Memory usage .
The life cycle of a transaction
- What is running under a transaction .
Every database object in every data connection , There is one B-Tree and pager object .pager Manage affairs 、 lock 、 cache 、 Crash recovery .
- When does the business start , When will it end , When to start affecting other database connections .
It can be as short as a statement , It can grow to the end of your command . An operation defaults to autocommit Pattern , That is, each command runs in a transaction . When the impact is related to the state of the lock . ### The state of the lock Every transaction takes unlocked,reserved,exclusize Lock state starts
Two read operations
With a transaction ：unlocked->pending->shared->unlocked
Do not use transactions ：unlocked->pending->shared->unlocked->pending->shared->unlocked
Not applicable to matters , The data may change between reads
Every operation , Read or write , All have to go through unlocked->pending->shared. pending yes gateway lock.
When writing to the database , Need from shared To reserved state . To reserved Post state , Changes can be written to the local cache , Not in the database .
Get into reserved Post state ,pager initialization rollback journal, It's for rollback and crash recovery The file of . It's actually the content of the database before the change .
stay reserved In state , need pager There are three kinds of maintenance pages： Changed pages（ There is page cache in ）, Unchanged pages( What's read out doesn't change page) And logs pages( be not in page cache in , But in B-Tree change page Write before joural).
pending lock yes gateway lock, Make sure there's no lock unlocked To shared state .
In this state , The main job is to change page from page cache Into the database file . here pager Really starting to change the database .
Before writing to the database , Make sure that the log file has been written to disk .
Log is after system crash or power down , Logging is the only way to recover the database .
The log file is in commit Then it's cleared .
Automatic submission and efficiency
Automatic submission is inefficient , Because every statement gets a lock . And there may be changes between statements .
adjustment page cache
Turn to mutex
Generally speaking , When pager When you can't store more data , From reserved State to mutex state . actually , Yes soft limit and hard limit.
soft limit Refer to page cache Full for the first time , This includes changed and unchanged page Mixing .pager Will remove the unchanged page, repeat , Until everything has been changed page completely fill . here page cache It's all changed page, Get into hard limit, Had to go to mutex .
cache_size adjustment page cache Size .
decision page cache Size
Waiting for lock
Use busy handler
Use the right transaction
Write operation use begin immediate