Oddbean new post about login | logout @4ebb1885 Oh man, I had a client complain two weeks ago that VACUUM mixed up their rowids. Sorry buddy, use your own identifiers.
@9d78c879 wow hang on, can VACUUM do that?
@4ebb1885 VACUUM is literally implemented as ".dump into an empty database that replaces the current one", and makes no promises about the internal rowid column (obviously if you specify your own primary key then it is preserved)
@9d78c879 yeah, I'm going to have to figure out what to do about that I'm planning a feature where people can attach comments to arbitrary rows in data that they might have uploaded themselves from a primary-key free CSV file - was going to hook that to the rowids
@4ebb1885 Can you use CSV file line number as an INTEGER PRIMARY KEY? If not you're going to have to cobble an ID system yourself because the behaviour is quite documented (§3¶3 in https://www.sqlite.org/lang_vacuum.html)
@9d78c879 I think I'll just force a _id integer primary key column on any uploaded CSV files and let it autoincrement
@9d78c879 @4ebb1885 Oh. Would that also happen if the primary key is set via UUID? Or just with auto increment?
@a9320b13 @9d78c879 Auto-increment is completely fine, provided you set your own primary key using e.g. "id integer primary key" (which auto-increments by default) It's just tables with no explicitly defined primary key at all which cause problems - SQLite assigns those a rowid primary key which isn't visible in the schema, and that's the ID which can be re-assigned on a VACUUM without you realizing it