28 ms·
> Unfortunately, I don’t think there’s a way to ALTER a table to make it strict. I think you have to copy the data out of the non-strict table into the strict o
by simonw 4mo ago
> Unfortunately, I don’t think there’s a way to ALTER a table to make it strict. I think you have to copy the data out of the non-strict table into the strict one.
This inspired me to add a feature to my sqlite-utils Python library and CLI tool, so you can now use it to transform non-strict tables to strict (and vice-versa) like this:
uvx sqlite-utils transform data.db mytable --strict
Or in Python:
import sqlite_utils
db = sqlite_utils.Database("data.db")
db.table("mytable").transform(
strict=True
)
Release notes for 4.1 here: https://sqlite-utils.datasette.io/en/stable/changelog.html#v4-1 https://sqlite-utils.datasette.io/en/stable/changelog.html#v...
Here are the relevant docs:
- Using table.transform(strict=True): https://sqlite-utils.datasette.io/en/stable/python-api.html#changing-strict-mode https://sqlite-utils.datasette.io/en/stable/python-api.html#...
- The sqlite-utils transform command: https://sqlite-utils.datasette.io/en/stable/cli.html#transforming-tables https://sqlite-utils.datasette.io/en/stable/cli.html#transfo...
- OskarS 4mo agoDoes this trick work with foreign keys? Like, if you have an ON CASCADE DELETE, does it delete a bunch of rows in other tables when converting the table to strict?
- simonw 4mo agoIt uses "PRAGMA foreign_keys=0" and "PRAGMA defer_foreign_keys= ON" before running the transformation, then resets those settings afterwards: https://github.com/simonw/sqlite-utils/blob/3f0471701b5f8c7de888a467020e5dd34310ce9a/sqlite_utils/db.py#L2571 https://github.com/simonw/sqlite-utils/blob/3f0471701b5f8c7d... That's the pattern recommended by SQLite here: https://www.sqlite.org/lang_altertable.html#otheralter https://www.sqlite.org/lang_altertable.html#otheralter
- simonw 4mo agoThanks for the nudge, I just added a new test explicitly covering this: https://github.com/simonw/sqlite-utils/commit/d71420065903ff54247b5062b8c6af6165b7e638 https://github.com/simonw/sqlite-utils/commit/d71420065903ff...
- throwlifeaway 4mo ago> This inspired me to add a feature to my sqlite-utils Python library "me" == "ChatGPT", apparently: > Can the .transform() internal Python method turn a non-strict table into a strict table? > No. Table.transform() preserves the table’s existing strictness; it cannot change it. Its signature has no strict= parameter > add an optional strict= boolean parameter to the transform() method - if it is None (the default) then the strict is not changed, otherwise True means change to strict and False means change to non strict. Implement with red/green TDD and uv run pytest -k https://gist.github.com/simonw/ab8256b81646ad967a601975e206de64 https://gist.github.com/simonw/ab8256b81646ad967a601975e206d... I appreciate the transparency, at least.
- simonw 4mo agoRight, the transcript is linked from the issue: https://github.com/simonw/sqlite-utils/issues/787#issuecomment-4949274867 https://github.com/simonw/sqlite-utils/issues/787#issuecomme... As far as I can tell I've shared more prompt transcripts than anyone else. Happy to be proven wrong about that. Look through my commit history on https://github.com/simonw/tools/commits https://github.com/simonw/tools/commits and https://github.com/simonw/sqlite-utils/commits https://github.com/simonw/sqlite-utils/commits and https://github.com/simonw/datasette/commits https://github.com/simonw/datasette/commits and you'll see that commits that were AI-assisted almost all link to a transcript or include a prompt or both.
- hamburglar 4mo agoWhat are you trying to achieve with this comment? Does everything need to turn into a sniping contest about whether or not something is ai-produced? This is the world we live in now.