SQLite Design and Concepts

Api

Divided into two major categories

1. Core API. Basic SQL Operations
2. Extension API. Create a custom SQL operation.

Basic Data Structures

There are connections, statments, B-trees, and pager that need to be understood.

In order to write good SQLite code, you need to understand the basic concepts, APIs, transactions, and locks.

Connection and Statments

The basis of these two data structures and query statements. A connection represents a connection to the database and is also a transaction context. Statments are generated by these connections. The statments is internally represented by a vdbe byte code.

B-tree and Pager

Each database connection can have multiple databases. Each database object has a B-tree object, and each B-tree object has a pager object.

Pager's work includes importing the page from disk into memory, writing databases, managing transactions, handling locks, and recovering from crash.

A database connection is operated in a transaction, and a database connection does not have two transactions at the same time.

Core API

Two ways to execute SQL statements.

1. Prepared query. The final way that SQLite executes the command. Consists of three stages
   1. Preparation
   2. Execution
   3. Finalization
2. Wrapped Query

Connecting to a database

sqlite3_open():memory:an in-memory database is created by passing in or an empty string as the name of the database.

If the pagesize of the database and the pagesize of the operating system are the same, the IO efficiency is increased.

Run prepared query

• prepation: Converts a string into a vdbe byte code.sqlite3_prepare_v2()generatessqlite3_stmta handle, including compiled bytecode and all resources that need to execute the command and iterate the result set.
• Execution: sqlite3_stepCauses the vdbe to step through the byte code. The first call acquires some kind of lock.
• Finalization: sqlite3_finalize()Close the statment and release the associated resources.

Using parameterized SQL

Includes two parameter bindings

1. Positional
2. Named

Advantages of using parameter binding

1. You do not need to compile, you can execute statment multiple times. Just reset and then bind again, run.
2. Automatically handles the improvement of semantic characters to avoid database injection and syntax errors.

Run wrapped Query

sqlite3_exec()Andsqlite3_get_table()

exec()Parse the command, identify the individual statements, and process them sequentially.

Handling Errors

sqlite3_errcode()Returns the error code of the last executing function. Usesqlite3_errmsg()to provide a description of the last error.

Formatting SQL statements

sqlite3_mprintf()Similarsprintf(),%qand%ssimilar, but will each single quotation mark into a continuous two, to prevent the database injection.%QA single quotation mark is added to the end of the string, and the null pointer becomes NULL

Operation Control

Use the Register filter or callback function to achieve a monitor,control when a particular event occurs.

There are three hook functions

1. sqlite3_commit_hook()
2. sqlite3_rollback_hook()
3. sqlite3_update_hook()
4. wal_hook()(in version 3.7)

Using multithreading

Shared cache mode: uses multiple threads to manage multiple SQL connections that share the page cache and reduce the memory usage of the server.

Extension the life cycle of API transaction transactions

1. What exactly is running under the transaction.
   For each database object in each data connection, there is a b-tree and pager object. Pager manages transactions, locks, caches, crash recovery.
2. When the transaction starts, when it ends, and when it begins to affect other database connections.
   It can be as short as a statement and can grow until you command the end. An operation defaults to autocommit mode, where each command runs in a single transaction. When the influence is related to the state of the lock. # # #The state of the lock unlocked,reservedtheexclusizelock status starts
   

Read transactions

Two read operations

Using transactions: unlocked->pending->shared->unlocked

Do not use transactions: unlocked->pending->shared->unlocked->pending->shared->unlocked

Transaction not applicable, data may change between two reads

Write transactions

Every operation, reading or writing, must go through unlocked->pending->shared. Pending is Gateway lock.

Reserved state

When you want to write to the database, you need to go from shared to reserved state. After the reserved state, the changes can be written to the local cache instead of the database.

After entering the reserved state, pager initializes the rollback journal, which is the file used for rollback and crash recovery. The actual content of the database before the change.

In the reserved state, there are three pages that need to be pager maintained: the changed pages (in the page cache), the unchanged pages (the read-unchanged page), and the log pages (not in page cache, Instead, write joural before b-tree changes the page.

Pending state

Pending lock is the gateway lock, which guarantees that there will be no lock from unlocked to shared state.

Exclusive state

In this state, the main task is to flush the changed page from page cache into the database file. At this point pager really started to change the database.

Before writing to the database, make sure that the log files have been written to disk.

Logs are the only way to recover a database after a system crash or power-down.

The log file will not be purged until after commit.

Automatic submission and efficiency

Autocommit is inefficient because each statement acquires a lock. And the statements may be changed.

Adjust page cache to turn mutex state

In general, when pager cannot store more data, it shifts from the reserved state to the mutex state. In fact, there are soft limit and hard limit.

Soft limit refers to the first time that page cache is full, including a mix of changed and unchanged page. Pager will remove the unchanged page, so repeat until all the page that has been changed is full. The page cache is now a changed page, entering hard limit and having to turn to a mutex state.

cache_sizeAdjusts the size of the page cache.

Determine the size of the page cache

Usesqlite3_analyer

Wait for the lock to use busy handler

sqlite3_busy_timeout()

Use the correct transaction

Write operation usingbegin immediate

SQLite Design and Concepts