SQLite Design and Concepts
Huahuahu? 2021-07-20 04:25:47

   SQLite Design and Concepts    


There are two main categories

  1. core API. Basic SQL operation
  2. 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 .

Core API

Two ways to perform SQL sentence .

  1. prepared query. SQLite The ultimate way to execute a command . There are three stages
    1. preparation
    2. execution
    3. finalization
  2. 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

  1. positional
  2. named

The advantage of using parameter binding

  1. No compilation required , It can be executed statment many times . It only needs reset, And then again bind, function .
  2. 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 .

Handling errors

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

Operational control

Use registration filter or callback Function to achieve that when a specific event occurs, it will be monitor,control.

There are three hook function

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

Using multithreading

shared cache mode: Use multiple threads to manage multiple SQL Connect , These connections share page cache, To reduce the server Memory usage .

extension API


The life cycle of a transaction

  1. 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 .
  2. 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

Read business

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

Write business

Every operation , Read or write , All have to go through unlocked->pending->shared. pending yes gateway lock.

reserved state

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 state

pending lock yes gateway lock, Make sure there's no lock unlocked To shared state .

exclusive 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

utilize sqlite3_analyer  

Waiting for lock

Use busy handler


Use the right transaction

Write operation use begin immediate

Please bring the original link to reprint ,thank
Similar articles