Quick question. I already have apps out in the world. When I want to make an update to the db as in add a table or add a column in a table, can this just be added at startup? In other words, in one of my apps, I made a table in the db called DBversion that simply holds a version number. When I make a change to a table or add a new one, I have been changing the version number in the table. On start of the app, if newVersion > oldVersion, then the modifications are made to the table.
I am now working on an older project where I did not originally put a DBversion table and I want to add a table to the db. If I just put this modification in a IsDBNewer method and check that at start after connecting to the db, will this be added without adding a new table with the same name every time the app starts? Is SQLite smart enough to say “Hey, there’s already a table named MyCoolNewTable, or there’s already a column named MyNeatNewColumn, and I’m not going to add it again since it already exists”?
Just checking to see if I have to do something creative to see that the db is newer before I can add a new table or modify an existing table without it making oodles of duplicate tables/columns.
Actually, the user_version pragma is intended for tracking: https://www.sqlite.org/pragma.html#pragma_user_version as the application_id is used more to differentiate between file types. You could also read the schema_version, but never write to it, and it’s easy for this number to change unexpectedly.
As for actually making the changes, Tim’s idea is definitely safe. However, you can get away with using the user_version to determine what changes need to be made. Remember that your users will skip versions.
My normal practice when changing schema is to build a new database, import the old data, and rebuild indexes. This keeps things consistent, since you need to do similar steps anyway should you need to remove a column, for example.
The Constructor determines if changes need to be made, and if so, creates a new database and calls BuildSchema then MigrateData to import the old data into the new schema. The end of MigrateData does some things you probably aren’t interested in, which is moving some files from disk into the database.
One significant advantage of this design is that it retains the old data. A disadvantage is needing to remember what kinds of things changed between each version.
Thank you all. I was just attempting Tim’s suggestion, and think the TableSchema is not on iOS. I forgot to mention that this is the project I am working on now. I think all my desktop apps tables have a DBversion table, but the users table in my iOS app does not.