sqlite_orm
sqlite_orm copied to clipboard
fnc12
`sqlite_sequence` table cleared out when doing `sync_schema()`
Right now I've been working on a test application so I can iron out how I'm going to use sqlite_orm in one of my larger projects. This includes figuring out how to do migrations. V1 of my DB schema had a table with a PRIMARY_KEY AUTOINCRMENT on it. So I would see a sqlite_sequence table generated in the DB.
The issue arose when in V2 of my DB schema, I changed a column in that table, thus making the old rows invalid. And then In another function I was going to migrate the data by loading a backup V1, re-inserting each row into V2 with the updated data.
Part of the issue is that I noticed the sqlite_sequence table was cleared out after the migration. Shouldn't this be preserved doing a sync_schema()?
IDK. How does it work with raw SQLite? sqlite_orm does not operate with sqlite_sequence straightly. So probably you'll face the same issue with raw SQLite
Looks like it's created automatically when using the AUTOINCREMENT keyword:
- https://www.sqlite.org/autoinc.html
- https://www.sqlite.org/fileformat2.html#seqtab
Yeah, I'm still here, just a bit busy.
This might be moot now since you provided me with a better method for migration so I don't need to do the "insert & update" for a migration, so sqlite_sequence will be repopulated. My only concern is if there is a case where someone might say insert 100 rows, delete the 50 most recent, then do a migration. They might expect their next row to have the id of 101, but instead it would be 51. IDK if this is much of a concern or not.
I don't think I did. I'm a bit busy with other projects at the moment so I can't spend time on testing this. When I do (much later), I'll get back to you on this.
Hi, what do you mean by " is the issue actual?" I have not done anything with sqlite_orm in the past few month, as I've been focused on other projects. I do plan to get back to it.
ids sequence is dropped only when table is dropped. Please check the result of sync_schema. It shows what happened to your table. If the result is 'dropped and recreated' so it means the storage called DROP TABLE %table-name% and then CREATE TABLE .... In that case you have two different table instances and two different sequences. You can try to do it with raw SQLite - the result will be the same. I think the issue can be closed cause this behavior is expected and even if we assume that this behavior is incorrect we don't have a way of fixing this cause we can't drop a table without resetting id sequence. If you have a case when you call sync_schema and table is preserved but ids are reset please paste the code here to reproduce. Thanks