[sqlite] ROWID schema surgery
On Fri, 26 Feb 2016 at 16:42 Simon Slavin wrote: > > I thought I knew where it was documented but I can't find it at the > moment. The idea is that if you have given the column an explicit name (in > your case 'storeID') then you might be referring to it in your code, so > SQLite shouldn't change the values. > > That should work correctly in the current and all future versions of > SQLite3. It's an assumption lots of programmers make. > My question is whether modifying "sqlite_master" after the fact to make the schema like this has any possible pitfalls/caveats, as opposed to creating the table this way from the start. It's not really clear to me how to tell what schema modifications are sound and what are not (eg. if you add the column to the beginning of the schema instead of the end, the table gets corrupted because now all of the columns are offset 1 from how they're actually stored).
[sqlite] ROWID schema surgery
Axiom is an ORM'ish layer on top of SQLite in Python. Due to unfortunate historic reasons, Axiom relies on the implicit ROWID (actually "oid") column present in every table in SQLite, without declaring an explicit PRIMARY KEY; this, of course, means that VACUUMing an Axiom table will corrupt it as some or all of the ROWIDs will change. Over on the Axiom bug tracker[1], we're trying to figure out a way to transition away from this unfortunate situation to using an explicit PRIMARY KEY. Unfortunately, ALTER TABLE may not use a PRIMARY KEY constraint, so it doesn't work for this purpose. However, the following evil trick seems to work: PRAGMA writable_schema=on; UPDATE sqlite_master SET sql='CREATE TABLE some_table_name (..., storeID INTEGER PRIMARY KEY)' WHERE tbl_name='some_table_name'; The following notebook session seems to demonstrate that it works: http://nbviewer.jupyter.org/url/bucket.mithrandi.net/sqlite-vacuum-working.ipynb and for completeness, here's a similar session demonstrating how VACUUM changes the values without the schema trickery: http://nbviewer.jupyter.org/url/bucket.mithrandi.net/sqlite-vacuum-broken.ipynb However, the question is whether this is something we can and should be relying on to function correctly. Are there any likely problems with this trick? [1] https://github.com/twisted/axiom/issues/35
[sqlite] Schema syntax error
Divmod Axiom[1] is a Python ORM built on SQLite; one of the book keeping tables it creates in the database has a column named "indexed", which became a reserved word around SQLite 3.6.4 (?). The "obvious" fix for this problem is to simply quote the column name using "", but the problem is that it is now impossible to load older databases which didn't have the column created with the name quoted: Error: malformed database schema (axiom_attributes) - near "indexed": syntax error What sort of migration path exists for converting / fixing these old databases? Ideally there would be some mechanism that does not require reinstalling an older version of SQLite. -- mithrandi, i Ainil en-Balandor, a faer Ambar ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
