SQLite Source Code and Documentation

The Test Script Interpreter is a tool designed to read and interpret ASCII text script files (ending in .test) containing SQL commands and desired results. It automates the process of executing SQL and verifying that the actual results match the expected results, reporting any discrepancies found.

Key Behaviors:

• Isolation: Each script is evaluated independently. Context (like settings or state) does not carry forward between scripts.
• Cleanup: All open database connections are closed at the end of a script, and all database files created during the script's execution are deleted upon completion.

Starting with version 3.45.0, SQLite supports JSONB, a binary encoding of JSON stored as a BLOB.

Key Characteristics:

• Performance: JSONB is typically 5% to 10% smaller than text-based RFC 8259 JSON and can be processed in less than half the CPU cycles.
• Compatibility: SQLite's built-in JSON SQL functions accept both ordinary text JSON and JSONB encoding.
• Format Note: While named after PostgreSQL's JSONB, the SQLite format is not binary compatible with PostgreSQL and has a different internal representation.
• Mechanism: JSONB replaces punctuation (quotes, braces, brackets, commas, colons) with headers containing the size and type of each element, allowing for faster access without scanning for delimiters.

FTS3 is the second full-text search extension for SQLite. It provides a similar API to the original fts1 extension but utilizes a substantially different storage schema.

Important Migration Note: Because the storage schema is different from fts1, you cannot simply rename an fts1 table to an fts3 table. Existing fts1 tables must be rebuilt into the new fts3 format to use this extension.

The SQLite JS/WASM API is distributed as a single-file sqlite3.js which is composed of several modular components. This modularity allows for including or excluding specific features (like the OO#1 API or Worker-based APIs) and facilitates different WASM build environments (like Emscripten).

Key components include:

• sqlite3-api.js: The core API, generated by concatenating various bootstrap, utility, and glue files.
• sqlite3-api-oo1.js: An optional high-level object-oriented wrapper (OO API #1).
• sqlite3-api-worker1.js: An optional Worker-thread-based API.
• sqlite3-vfs-opfs.c-pp.js: A VFS implementation supporting the Origin-Private File System (OPFS) for persistent browser storage.
• sqlite3.wasm: The compiled WebAssembly binary, built from sqlite3-wasm.c.

The SQLite repository is organized as follows:

• src/: Primary source code for the SQLite core. Files starting with test are for testing. tclsqlite3.c and tclsqlite3.h provide the TCL interface.
• test/: Testing code and TCL scripts. Files ending in .test use the testfixture interpreter.
• tool/: Build tools, scripts, and the Lemon parser generator.
• ext/: SQLite extensions (e.g., ext/fts5/ for FTS5). Some extensions are built into the amalgamation, others are in ext/misc/ and included in the SQLite CLI.
• doc/: Internal documentation. (Note: Primary application developer documentation is in a separate repository).

The SQLite build infrastructure uses several key TCL files for configuration:

• proj.tcl: Project-agnostic utility code shared across SQLite-related projects (e.g., Fossil).
• sqlite-config.tcl: Project-specific utility code used by both auto.def and other configuration drivers.
• auto.def: The primary driver for the ./configure process.
• autoconf/auto.def: A trimmed-down version of auto.def used for the 'autoconf' bundle.

JSONB is a binary encoding of JSON designed for faster parsing than text JSON. Each JSON element consists of a header and a payload.

• Header: Determines the element type and the payload size. The header size can range from 1 to 9 bytes.
• Payload: Can be any size from zero bytes up to the maximum allowed BLOB size.

To ensure fast translation to and from text JSON, JSONB uses a 'lazy' conversion approach. For example, numeric values are stored as their original ASCII text representations rather than being converted to binary numbers, minimizing CPU cycles during text-to-JSONB or JSONB-to-text operations.

As of SQLite version 3.48, you can enable ICU support during the standard build process by passing specific flags to the configure script:

• --with-icu-config
• --with-icu-ldflags
• --enable-icu-collations (optional, used with the flags above)

On some Unix-like systems, you can configure SQLite to use POSIX blocking locks instead of continuous polling. This can reduce CPU usage and facilitate OS priority transfer between processes. To enable blocking locks, you must satisfy two conditions:

1. Build the SQLite library with SQLITE_ENABLE_SETLK_TIMEOUT defined.
2. Configure a timeout in milliseconds using the sqlite3_busy_timeout() API.

To use the SQLite Query Result Formatter in your application:

1. Include the qrf.h header file.
2. Link your application against the qrf.c source file.
3. Initialize a sqlite3_qrf_spec structure (zero it out first).
4. Set .iVersion = 1.
5. Set either .pzOutput (for a memory buffer) or .xWrite (for a callback).
6. Call sqlite3_format_query_result() with your prepared statement and the spec object.

To prevent WASM heap memory leaks, you must call .dispose() on struct instances when they are no longer needed.

Cleanup Behavior:

• If the instance wraps externally-provided memory, it is not wiped.
• If zeroOnDispose was set to true in the description, the memory is zeroed out.
• ondispose hooks: You can register cleanup logic using addOnDispose(...value).
  • If ondispose is a function, it is called with the struct as this.
  • If ondispose is an array, it can contain functions, pointers (numbers), or other StructType instances.
    • Functions are executed.
    • Numbers are treated as pointers and passed to the dealloc() function.
    • StructType instances have their .dispose() method called.