* Fix transform() corrupting TRUE/FALSE/NULL column defaults
quote_default_value() passed keyword-literal defaults (TRUE, FALSE, NULL)
through self.quote(), wrapping them in quotes. So transform() rebuilt a
column declared "INTEGER DEFAULT TRUE" as "INTEGER DEFAULT 'TRUE'", and a
later default insert stored the text 'TRUE' instead of the integer 1
(likewise 'FALSE' for 0 and 'NULL' for null) - silent value corruption.
Return these keyword literals unquoted, as already done for the
CURRENT_TIME/DATE/TIMESTAMP literals.
PR #764
Column names passed to Python API methods are now resolved against the
table schema case-insensitively, mirroring how SQLite itself compares
identifiers (ASCII-only case folding). Fixes KeyError populating
last_pk from insert()/upsert(), silently ignored transform()
rename/drop/types options, duplicate column errors from
create_table(transform=True), redundant lookup() indexes, and
case-sensitive foreign key validation.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
table.add_foreign_key() now accepts on_delete= and on_update= to
create foreign keys with ON DELETE/ON UPDATE actions:
table.add_foreign_key("author_id", "authors", "id", on_delete="CASCADE")
Works for compound foreign keys too.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
ForeignKey.columns and .other_columns are now tuples rather than lists,
both when introspected and when constructed directly (lists are
normalized in __post_init__). Tuples are now the documented form for
specifying compound foreign keys everywhere - in create_table()
foreign_keys=, add_foreign_key() and drop_foreign_keys=:
foreign_keys=[(("campus_name", "dept_code"), "departments")]
Lists continue to work as input but are no longer documented.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
- Changelog entries for all the compound foreign key work
- Upgrading guide notes the transform() behavior changes
- ForeignKey added to the API reference
- Updated .foreign_keys introspection example output
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
db.query() rejected statements roll back, RETURNING commits immediately.
Fixes two transaction bugs in db.query():
- A non-row-returning statement such as an UPDATE was executed and
auto-committed by execute() before the ValueError was raised, so the
write took effect despite being rejected. query() now runs the
statement inside a savepoint and rolls it back before raising, so a
rejected statement has no effect on the database - in every
connection mode, including autocommit=True.
- INSERT ... RETURNING only committed once the returned generator was
fully exhausted, so calling query() without iterating - or partially
iterating with next() - left the transaction open and the write could
be rolled back on close. The write is now completed and committed at
call time, as the documentation already promised. Plain SELECTs are
still fetched lazily.
Transaction control statements, VACUUM, ATTACH and DETACH never return
rows, so query() now rejects them without executing them. PRAGMA
statements skip the savepoint guard because some of them - such as
pragma journal_mode=wal - refuse to run inside a transaction.
Claude-Session: https://claude.ai/code/session_012U3iRfJoTZ5vd22cBSF2nJ
Raw write statements previously opened an implicit transaction that
stayed open until something committed it - the write was visible on
the same connection, making it look saved, but was silently rolled
back when the connection closed. execute() now commits any implicit
transaction it opens, so raw writes behave like every other write
in the library: committed as soon as they run, unless an explicit
transaction (db.begin() or db.atomic()) is open, in which case they
join it.
Row-returning writes such as INSERT ... RETURNING via db.query()
commit once their rows have been iterated. atomic(), commit() and
rollback() now issue literal COMMIT/ROLLBACK statements, which have
identical semantics in every sqlite3 connection mode. The CLI bulk
command uses db.atomic() instead of 'with db.conn:'.
This simplifies the transaction contract to: everything commits
immediately unless you explicitly opened a transaction. Docs
updated throughout; changelog and upgrading guide cover the
breaking change for code that relied on rolling back uncommitted
execute() writes.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01UnLnhsH25Nnv7LHhekUfPd
Connections created with sqlite3.connect(autocommit=True) make
commit() a documented no-op, and autocommit=False connections are
permanently inside a transaction - in both modes every write made
by this library appeared to work in-process but was silently
discarded when the connection closed. Database() now raises
TransactionError for these connections instead of losing data.
Tests are skipped on Python versions before 3.12, where the
autocommit parameter does not exist.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01UnLnhsH25Nnv7LHhekUfPd
A dedicated sqlite_utils.db.TransactionError exception is clearer
to catch than a generic RuntimeError, and gives future
transaction-state guards a home.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01UnLnhsH25Nnv7LHhekUfPd
Manual transaction control previously required mixing raw
db.execute("begin") strings with methods on db.conn. These thin
wrappers give the documentation and callers a single consistent
idiom. Docs updated to use them throughout.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01UnLnhsH25Nnv7LHhekUfPd
The method existed only to raise NotImplementedError, since
full-text search is not supported for views, and it showed up in
the generated API reference as a documented View method. Calling
enable_fts() on a View now raises AttributeError like any other
missing method. The sqlite-utils enable-fts command uses db.table()
and shows a clean error when pointed at a view instead of a
traceback.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01UnLnhsH25Nnv7LHhekUfPd
User-facing argument validation in db.py previously used bare
assert statements, which vanish entirely under python -O and raise
AssertionError - an exception type callers should not have to
catch for input validation. Fifteen validation sites now raise
ValueError with the same messages. Internal invariants (an
unreachable branch and a post-update rowcount check) remain
asserts.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01UnLnhsH25Nnv7LHhekUfPd
Type detection has been the default for CSV/TSV data since 4.0a1,
making this flag a no-op kept only for backwards compatibility.
4.0 is the release where it can be removed - the flag now errors,
prompting scripts to drop it. --no-detect-types is unchanged. The
dead detect_types parameter has been removed from
insert_upsert_implementation.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01UnLnhsH25Nnv7LHhekUfPd
pending() and applied() no longer create the _sqlite_migrations
table or perform the one-way legacy schema upgrade - that now only
happens in apply(). The CLI --list path no longer creates the
database file when it does not exist. applied() results are now
explicitly ordered by insertion.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01UnLnhsH25Nnv7LHhekUfPd
Changing the journal mode assigns conn.isolation_level, which
commits any open transaction as a side effect - silently breaking
the rollback guarantee of atomic() blocks and of user-managed
transactions. Both methods now raise RuntimeError if a transaction
is open. Calling them when the database is already in the requested
mode remains a no-op.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01UnLnhsH25Nnv7LHhekUfPd
A record missing a primary key value - or with None for one - can
never match an existing row, because a NULL primary key never
satisfies ON CONFLICT. Such records were previously inserted as
brand new rows (silently, for multi-record upsert_all calls) or
triggered a KeyError after the insert had already happened. Both
cases, including upserting an empty record, now raise
PrimaryKeyRequired before any SQL is executed.
Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01UnLnhsH25Nnv7LHhekUfPd
The `--detect-types` option is now automatically turned on for all commands that deal with CSV or CSV.
A new `--no-detect-types` option can be used to have all columns treated as text.
Closes#679
