simonw/sqlite-utils 4.2

on GitHub

• New table.checks, table.column_checks and table.table_checks introspection properties expose column-level and table-level CHECK constraints. (#834)
• New sqlite_utils.ANY marker type for creating and introspecting SQLite ANY columns. The Python API and CLI can create, add and transform these columns, and table.transform() and table.extract() now preserve ANY columns and their values in STRICT tables. (#790)
• table.default_values now unescapes doubled single quotes in string defaults, so a default such as 'O''Brien' is returned as "O'Brien". Thanks, ikatyal2110. (#811)
• table.default_values now decodes unquoted TRUE, FALSE and NULL default literals as True, False and None respectively. (#836)
• table.enable_fts(..., tokenize=...) and sqlite-utils enable-fts --tokenize now safely quote the tokenizer argument, preventing a crafted value from injecting additional SQL. Thanks, Bunlong Heng. (#828)
• rows_where(), pks_and_rows_where(), search() and search_sql() now support offset= without requiring limit=. The sqlite-utils rows --offset option now works without --limit too. Thanks, ethanhawkes-gif. (#816, #821)
• Empty or whitespace-only input passed to rows_from_file() is now handled as an empty CSV file instead of raising csv.Error. Thanks, Rami Abdelrazzaq. (#808, #837)
• sqlite-utils convert --dry-run now works for table and column names containing closing square brackets. (#829)
• table.indexes and table.xindexes now work for table, index and column names containing double quotes. This also fixes table.transform() for tables with those identifiers. Thanks, nyxst4ck. (#824, #825)
• Improved type annotations throughout the package and added Pyright regression checks to CI. (#833)
• Changing a TEXT column to INTEGER, FLOAT or REAL using table.transform() or sqlite-utilstransform now converts exact empty strings to NULL. Previously they remained empty strings in the numeric column. Thanks, ikatyal2110. (#488, #805)

table.transform() can handle many more edge-cases:

• table.transform() now preserves column-level and composite UNIQUE constraints, including constraint names, collations, sort order and ON CONFLICT behavior. Renaming columns updates those constraints, while dropping any constituent column removes the entire constraint. (#762)
• table.transform() now preserves AUTOINCREMENT primary keys and their sequence high-water marks. Previously a transform removed AUTOINCREMENT and could reuse deleted row IDs. (#602)
• table.transform() now preserves CHECK constraints, including comments within their expressions. Renaming a column rewrites identifier references in checks without changing string literals or function names. Dropping a column drops a check owned by that column, and raises TransformError if a remaining check depends on it. (#762)
• table.transform() now preserves comments immediately before or after column definitions. These comments move with the column if it is renamed or reordered, and are removed if the column is dropped. (#762)
• table.transform(rename=...) now preserves explicit indexes on renamed columns by dropping and recreating those indexes against the new column names. Previously this raised a TransformError. (#822)
• table.transform() now works for tables that are referenced by views. Previously the ALTER TABLE... RENAME TO step raised no such table if a view referenced the table being transformed. View definitions are left unchanged - see Tables referenced by views. This also fixes a bug where transform(keep_table=...) silently rewrote dependent views to point at the frozen backup table instead of the live one. (#831)