4 ms·
Huh, weirdly enough I've been hoping for exactly this kind of thing - albeit easily called from Python. My use-case is that with SQLite there are aspects of th
by simonw 3y ago
Huh, weirdly enough I've been hoping for exactly this kind of thing - albeit easily called from Python.
My use-case is that with SQLite there are aspects of the table - things like generated columns - which aren't yet available via the regular SQLite PRAGMAs for introspecting tables.
For those things, the only way to introspect them is to read the CREATE TABLE statement out of the "sqlite_master" table and parse it.
But you need a very robust parser!
I've been investigating tree-sitter recently for this, notes here: https://til.simonwillison.net/python/tree-sitter https://til.simonwillison.net/python/tree-sitter
If anyone needs an implementation of that advanced ALTER TABLE mechanism (where you create a new table with the new schema, copy the data cross from the old one and then swap the names) my sqlite-utils CLI tool and Python library implements that, as described here: https://simonwillison.net/2020/Sep/23/sqlite-advanced-alter-table/ https://simonwillison.net/2020/Sep/23/sqlite-advanced-alter-...
- cjohnson318 3y agoPorting this to Python is left as an exercise to the reader.
- masklinn 3y agoIt's two files, you can probably just cffi it in.
- oefrha 3y agoAn SQLite extension could be implemented on top of this parser, converting the C functions into SQLite functions. Then it could be fairly easily called from any language.
- indigo945 3y agoAt that point, it seems like it might be faster to just fork SQLite and add the PRAGMA that you need, instead of duplicating a part of SQLite's functionality in a non-future-proof way.
- reportgunner 3y agoHave you checked sqlite-utils[0] ? [0] https://sqlite-utils.datasette.io/en/stable/ https://sqlite-utils.datasette.io/en/stable/
- oefrha 3y agosimonw wrote sqlite-utils, so I’m sure he has checked it. It’s also mentioned in gp.
- Someone 3y ago> But you need a very robust parser! Making it robust should not be hard, should it? In general, the difficult part of parsing is the handling of erroneous input: generating good error messages and error recovery (not getting into the woods after seeing one error). This parser only has to parse valid SQL, and one statement at a time, so it needs neither.