SQLite Study Notes (4)

Supplement to the design and concept (the previous article reposted from the mrdb predecessors:

Note several important pre-query c api functions: sqlite3_prepare_v2 (); sqlite3_step (); sqlite_finalize ().

Temporary Storage:

An important part of query processing. Ram or a simple file, which is selected by the compilation command temp_store. When you select file-based storage, use temp_store_directory to specify where to create the storage file.

Use of placeholders:

The default value is null.

 

Encapsulate several query functions: sqlite3_exec (); sqlite3_get_table ();

The previous function does not return data. The second function returns data, and the memory usage is large.

 

Error handling:

Sqlite_error and sqlite_busy.

Note: sqlite_errcode (); sqlite_errmsg ().

 

SQLite Statement Formatting:

Pay attention to sqlite3_mprintf () and its function documentation;

SQL injection attacks may occur here.

Recover the database from the backup.

 

Operational control:

Functions are implemented by callback functions:

Sqlite3_commit _ hook (), sqlite3_rollback_hook (); sqlite_update_hook (); monitors submission, rollback, and update respectively. There is also a wal_hook () hook function, which is not the same as the general hook function.

Key points: sqlite3_authorizer () hook function, which is an important hook function. It provides strong fine-grained control over the database, and has strong restrictions on function operations.

 

Thread used:

Shared cache mode.

 

 

Create a user-defined function:

Sqlite3_create_function () function application;

You can embed user-defined functions into the check constraint.

 

Create user-defined aggregation:

Main book, implementation, and call.

 

Create user-defined sorting:

Function sqlite3_create_collation ();

Three sorting rules are binary, nocase, and rtrim.

 

Read transactions and write transactions:

SQLite has three different file locks in database files: Reserved bytes, to be determined bytes, and a shared area.

Busy handling:

Do other things. Be careful with brute force usage.

Lock and Network File System:

SQLite depends on the locks concurrently used by the file management system. SQLite uses the same lock mechanism regardless of file audition.

Finalizer () function:

Transaction or locked boundary. They start the transaction and end the transaction. Obtain and release the lock.

Shared cache mode:

Multiple connections in a single thread are connected simultaneously.

In this mode, you can create multiple connections for one thread to share the same page cache. The reorganized connection can have multiple read operations and one write operation in the same database at the same time, but it is only restricted in this thread and cannot be shared between threads.

The shared cache mode is designed for Embedded servers with high concurrency when the memory needs to be retained.

Remember:

The core API involves two basic data structures used to execute SQL commands: links and statements.

Three steps for command execution: compilation, execution, and completion.

The Exec () and get_table () functions of SQLite encapsulate these three steps as a function call to automatically process the associated statement objects.

API extension provides three different custom SQLite Methods: User-defined functions, user-defined aggregation, and user-defined sorting rules.

Focus:

SQLite manages things and locks, how things and locks work in the background, and how to use them together with code.

--------------------------------- Minute ---------------------------- cut ------------------- line -------------------------------------------------