Making changes to SQLite db (add tables, columns, etc)

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.

Thanks!

We recently added a DBUpdates module tracking to ARGen, so if you use ARGen check out the newest features.
https://bkeeney.com/argen

I would recommend, instead of trying and erring, that you iterate the table schema and look to see if your DBVersion table is missing: http://documentation.xojo.com/index.php/Database.TableSchema

You could then create it and do your database updates as if the db version was zero.

And just to answer your question:

Yes, this will cause Database.Error to be true, and there will be a nice error message to explain what happened.

If you don’t need a full table, you could use the user_version pragma with something like this:

[code]// Set user_version
DB.SQLExecute(“PRAGMA user_version=4;”)

// Get user_version
Dim rs As RecordSet = DB.SQLSelect(“PRAGMA user_version”)
Dim version As Integer = rs.IdxField(1).IntegerValue[/code]

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.

You can see a real-world example of this in one of my apps: https://github.com/thommcgrath/Beacon/blob/master/Project/Data%20Model/LocalData.xojo_code

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.

Is there something comparable in iOS?

On iOS, query the sqlite_master table.

Paul - Is that something like “SELECT * FROM sqlite_master”? Then what is found in this table?

That’s right. Use that query with any SQLite tool to see it’s exact contents.

Something like this should show just the tables:

SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;

Thank you! I appreciate everyone’s help