SQLite is case insensitive for column names, sqlite-utils is not

[simonw]

I'm going to consider the case-insensitivity thing separately. SQLite is case insensitive for column names but sqlite-utils is not, in a bunch of places.

Originally posted by @simonw in #629

[simonw]

I had Codex Desktop run an audit ("Audit the code for everywhere that case sensitivity of columns might matter")

Audit Findings

No code edits made. I found several real case-sensitivity seams where SQLite would accept mixed-case identifiers, but Python-side dict/set checks do not.

• db.py line 3713: insert() can successfully write to an existing mixed-case schema, then raise KeyError while populating last_pk. Repro: table has Source, caller uses pk=("source", ...).
• db.py line 3731: upsert() with an inferred existing PK now depends on exact record-key casing. Dict mode fails at first_record_dict[p]; list mode fails at column_names.index(p). This is the lowercase follow-up from issue Should upsert() work with compound primary key? #629.
• db.py line 3271: upsert SQL generation compares all_columns and pk_cols case-sensitively. With schema-cased PKs and lower-case input columns, PK columns can be treated as non-PK update columns. The old upsert path is stricter and fails on record[pk] at db.py line 3322.
• db.py line 3888: lookup() compares unique-index columns case-sensitively, so it may create redundant indexes for Name vs name. It also fails returning rows[0][pk] when existing PK is ID and pk="id" at db.py line 3900.
• db.py line 1159: Database.create_table(..., transform=True) treats same columns with different casing as missing/dropped columns. I confirmed {"name": str, "age": int} against existing Name, Age can hit OperationalError: duplicate column name: name.
• db.py line 2084: Table.transform() uses exact matches for types, drop, and rename. Lower-case rename={"name": ...} or drop=("name",) against Name silently does nothing. not_null/defaults with lower-case names can later assert in create_table_sql() at db.py line 1017.
• db.py line 2056: drop_foreign_keys and FK-preserving rename logic are exact-case. I confirmed drop_foreign_keys=("parent_id",) does not remove an FK on Parent_ID.
• db.py line 1315: FK validation is exact-case in add_foreign_keys(). Same pattern appears in create_table_sql() for referenced columns at db.py line 1041, add_column(fk_col=...) at db.py line 2422, and add_foreign_key() at db.py line 2520.
• db.py line 2231: extract() validates requested columns with exact-case set membership. extract("name") fails against a table with Name.
• db.py line 3135: convert(..., multi=True) selects through SQLite but then indexes the returned row dict with the caller’s casing. convert("name", ..., multi=True) fails against Name.

Mostly OK

• create_index(["name"]), plain convert("name", ...), enable_fts(["name"]), search(columns=["name"]), get(), delete(), and update() mostly work because they pass identifiers into SQL and do not do much Python-side key lookup.
• db.py line 3836 already has a case-insensitive check in add_missing_columns().
• CLI commands mostly inherit the Python API behavior. Notable direct CLI pass-throughs are transform options at cli.py line 2602, extract at cli.py line 2682, convert at cli.py line 3142, and spatial-index validation at cli.py line 3265.