Update my software - Update but not overwrite existing database

Looking for opinions/examples of how to update, but not overwrite, existing database when releasing new version of my application.

sqlite
windows 8
xojo 2013r2

I put my database in the specialfolder.applicationdata.

Yes, but how do you just update it (if necessary) and not overwrite the existing data.

it depends on how the DB is being changed… just adding new records for tat version? adding new columns?, Adding new tables ? adding new foreign keys?

Basically on App start check for version of the DB installed. (hopefully you have a way?)

If old version write code to do what is necessary … Either updating tehe current DB or creating a new one and copying the info out of the old one into it.

Karen, what about when the user downloads a new version. On installation I need to check if there is already a database. If there is, I want to make any updates at that time (new columns, tables, etc.), so when the new version starts it is ready to go.

Here’s a link to code I posted in the old forum, I used this to update existing table schema to match the current program’s schema, if different. I always created the tables via code and used the table creation methods to create a temporary table to compare against the current table to determine which changes needed to be made.

http://forums.realsoftware.com/viewtopic.php?f=3&t=34995&hilit=update

When the app fires up…

a) check if database exists… if not… create it
b) if so, is it the version (put a one record table to store this) appropriate for this versionof the app? if so, move on
c) if not… do what ever is required (and only you know that)… new tables? new columns etc.

Be aware… if SQLite that ALTER TABLES is limited in what it can do…

Yes… I don’t understand the issue… What specifically don’t you know how to do?

  • Karen

Thank you Mark. Is this done during software installation, or after installation when the application starts?

best to check on each App startup IMO

I would run that on application startup.

Got it! Thank you.

To elaborate on Dave’s answer a little further, I write my code to use a loop and upgrade the database one version at a time. So updating a v1 database to v3 first updates v1 to v2, then v2 to v3. Also there is a SQLite pragma you can use for storing database version, specifically designed for this. I don’t have details off-hand, but google for SQLite pragmas and you’ll find it.

Ah, ok. Looks like you got your answer. I thought your concern was having the database overwritten by the installer.

Great info! Thanks Thom.