SQLite 权威指南 (it-ebooks) (Z-Library)

【One-Line Pitch】 A practical, code-first guide to SQLite that takes you from the command-line shell to the C API, covering the relational model, SQL syntax, transactions, locking, and advanced programming hooks—ideal for developers who want to embed a lightweight, zero-config database into their applications. 【Book Arc】 - **Opening (~0%–8%)**: Introduces SQLite as a unique embedded, open-source relational database with cross-platform binary compatibility and a strong reliability story (extensive regression testing). Covers installation, compiling on Windows (VC/MinGW), and the basics of the command-line shell (CLP) for creating tables, inserting data, and formatting output. - **Early (~8%–23%)**: Moves into the relational model (tables, rows, columns) and core SQL syntax. Covers `ALTER TABLE`, the full `SELECT` statement with clauses like `WHERE`, `GROUP BY`, `HAVING`, and `ORDER BY`, plus operators like `LIKE` and `GLOB`. Introduces `INSERT` and constraints (`NOT NULL`, `CHECK`), and explains collation (BINARY, NOCASE) for text comparison. - **Early (~23%–31%)**: Delves into SQLite's dynamic typing system—storage classes (NULL, INTEGER, REAL, TEXT, BLOB) and type affinity. Explains how values of different types are stored, compared, and sorted. Introduces transactions, deadlock scenarios, index usage rules, and database configuration via `PRAGMA` commands (e.g., `cache_size`). - **Middle (~38%–54%)**: Transitions from SQL to the C API. Covers opening/creating databases (including in-memory databases), page size and encoding settings, prepared statements, and the `sqlite3_mprintf()` function with `%q`/`%Q` for safe SQL string handling (SQL injection defense). Explains the locking model in detail—UNLOCKED, SHARED, RESERVED, PENDING, EXCLUSIVE—and the rollback journal's role in crash recovery. - **Late (~54%–77%)**: Focuses on the core C API in depth: the ~80 functions (only 8 essential), UTF-8/UTF-16 variants, error codes, column metadata, parameter binding, and hooks like `sqlite3_update_hook()` and the authorization callback. Covers user-defined functions, aggregates, and collations, with a note that these are connection-scoped, not stored in the database. 【Key Takeaways】 - **SQLite is the only embedded relational database designed from scratch for that purpose** (Early): Unlike Firebird or MySQL embedded variants, SQLite is fully open-source, license-free, and purpose-built for in-process use—making it a default choice for desktop, mobile, and appliance software. - **The database file is binary-compatible across all platforms** (Early): A SQLite file created on a SPARC workstation works unchanged on Windows, Mac, or a phone, regardless of byte order or architecture. This portability is a core design guarantee, not an afterthought. - **Dynamic typing via storage classes is the heart of SQLite's flexibility** (Early): Every value has a storage class (NULL, INTEGER, REAL, TEXT, BLOB), and columns have "affinity" that influences how values are stored. Understanding the class ordering (NULL < numbers < TEXT < BLOB) is essential for writing correct queries and avoiding surprises. - **Transactions and locking are simple but require discipline** (Early): A transaction is an all-or-nothing boundary; concurrent connections can deadlock (e.g., one holding a reserved lock, another a shared lock). Knowing the lock states (UNLOCKED → SHARED → RESERVED → PENDING → EXCLUSIVE) helps you design conflict-free access patterns. - **The C API is small but powerful—only 8 functions are essential** (Late): `sqlite3_open()`, `sqlite3_exec()`, `sqlite3_prepare()`, `sqlite3_step()`, `sqlite3_finalize()`, and a few others cover the full connect-query-close cycle. The remaining ~70 functions handle specific tasks like metadata, hooks, and custom functions. - **Use `%q`/`%Q` in `sqlite3_mprintf()` to prevent SQL injection** (Middle): These format specifiers double single quotes and backslashes, making user input safe for embedding in SQL strings. This is a simple, effective defense for C applications that build queries from user input. - **Temporary tables are a workaround for concurrency constraints** (Middle): Since temp tables live outside the database file, they don't require RESERVED locks. They let you read from a table while updating a copy, then swap—useful for complex operations that would otherwise deadlock. - **User-defined functions, aggregates, and collations are connection-scoped** (Late): They live in your program's library, not in the database. This means they're powerful for custom logic but must be re-registered on every new connection—don't mistake them for stored procedures. 【Reading Tips】 - **Skim the opening chapters (0–8%)** if you already know SQL basics; the key value is the compatibility and reliability discussion, plus the CLP formatting commands (`.mode`, `.headers`, `.nullvalue`) which you'll use constantly. - **Deep-read the storage class and affinity section (~23–31%)**—it's the most conceptually tricky part and explains why SQLite behaves differently from strict-typed databases. Understanding class ordering will save you from debugging weird sort or comparison results. - **For C programmers, focus on Chapters 5–7 (~38–77%)**: the prepared statement lifecycle (`prepare` → `step` → `finalize`), error handling, and the locking model. The code examples are practical; the "empty notes" from the translator (e.g., VC6 build steps) are optional and can be skipped. - **If you're not writing C code**, skim the API chapters and just absorb the concepts: parameter binding, hooks, and custom functions. The SQL chapters (Early) are the most universally useful. - **Use the `EXPLAIN` command** (shown in the opening and middle sections) to see the VDBE bytecode—it's a great way to understand how SQLite executes queries and why indexes matter. 【Coverage Limits】 This guide covers the book's core content: SQL syntax, storage classes, transactions, locking, and the C API. It does not cover advanced topics like the full VDBE internals, performance tuning beyond `PRAGMA` basics, or the appendices (e.g., full C API reference), as those are not detailed in the sampled excerpts.