sqlite-knowledge-patch

SQLite 3.46+ Knowledge Patch

Claude's baseline knowledge covers SQLite through 3.45. This skill provides changes from 3.46.0 (2024-05-23) onwards.

New SQL Functions

Function	Version	Description
json_pretty(X) / json_pretty(X,indent)	3.46.0	Pretty-print JSON with optional indent string
unistr(X)	3.50.0	Convert string with \uXXXX Unicode escapes to characters
unistr_quote(X)	3.50.0	Quote string using Unicode escape syntax
jsonb_each(X)	3.51.0	Like json_each() but returns JSONB for array/object values
jsonb_tree(X)	3.51.0	Like json_tree() but returns JSONB for array/object values
median(X)	3.47.0	Median aggregate (CLI extension)
percentile(X,P)	3.47.0	Percentile aggregate (CLI extension)
percentile_cont(X,P)	3.47.0	Continuous percentile (CLI extension)
percentile_disc(X,P)	3.47.0	Discrete percentile (CLI extension)

iif() / if() Enhancements

-- 3.48.0: if() is an alias for iif()
-- 3.48.0: 2-argument form returns NULL when false
SELECT
  if (x > 0, 'positive');

-- NULL when x <= 0
-- 3.49.0: accepts any number of args >= 2 (chained if/elseif)
SELECT
  iif (x > 0, 'positive', x < 0, 'negative', 'zero');

Numeric Literal Underscores (3.46.0)

SELECT
  1_000_000;

-- 1000000
SELECT
  3.141_592_653;

-- 3.141592653
SELECT
  0xFF_FF;

-- 65535

Date/Time Enhancements (3.46.0)

-- New strftime format specifiers
SELECT strftime('%G', 'now');  -- ISO 8601 week-numbering year
SELECT strftime('%V', 'now');  -- ISO 8601 week number

-- Ceiling/floor modifiers for ambiguous month shifts
SELECT date('2024-01-31', '+1 month', 'ceiling');  -- 2024-03-01
SELECT date('2024-01-31', '+1 month', 'floor');    -- 2024-02-29

New C APIs

See references/new-apis.md for details.

• sqlite3_setlk_timeout() (3.50.0) — separate timeout for blocking locks
• sqlite3_set_errmsg() (3.51.0) — extensions can set error messages
• sqlite3_db_status64() (3.51.0) — 64-bit version of sqlite3_db_status()
• sqlite3changeset_apply_v3() (3.51.0) — session extension

DBCONFIG Options

// 3.49.0 — all default to ON
sqlite3_db_config(db, SQLITE_DBCONFIG_ENABLE_ATTACH_CREATE, 0, 0); // prevent ATTACH creating new DBs
sqlite3_db_config(db, SQLITE_DBCONFIG_ENABLE_ATTACH_WRITE, 0, 0);  // make ATTACH read-only
sqlite3_db_config(db, SQLITE_DBCONFIG_ENABLE_COMMENTS, 0, 0);      // disallow SQL comments

JSON Operator Change (3.47.0)

-- Negative RHS on ->> accesses from the right of an array
SELECT '[10,20,30]' ->> -1;  -- 30 (last element)
SELECT '[10,20,30]' ->> -2;  -- 20 (second to last)

FTS5 Enhancements

• 3.47.0: fts5_tokenizer_v2 API and locale=1 option for locale-aware tokenizers
• 3.47.0: contentless_unindexed=1 for contentless tables with persistent UNINDEXED columns
• 3.48.0: insttoken config option and fts5_insttoken() function for prefix queries
• 3.51.0: STRICT typing enforced on computed columns

Other Notable Changes

• 3.47.0: group_concat() returns empty string (not NULL) for single empty string input
• 3.47.0: sqlite3_rsync — experimental tool for replicating SQLite databases
• 3.48.0: Max function arguments increased from 127 to 1000
• 3.48.0: Build only requires C compiler + make (no TCL dependency)
• 3.50.0: %Q/%q printf with # flag converts control chars to unistr() escapes
• 3.51.0: PRAGMA wal_checkpoint=NOOP — check if checkpoint is needed without doing one
• 3.51.0: carray and percentile extensions in amalgamation (compile with -DSQLITE_ENABLE_CARRAY / -DSQLITE_ENABLE_PERCENTILE)