Databases and Versioning

I’m thinking about creating an app with Xojo that manages data. I’ve been reading up on using databases within Xojo and I’ve created some basic apps so far but one thing occurred to me that I haven’t seen anyone here discuss… and that’s ‘versioning’.

Specifically, how to handle existing data files as your application grows and improves with each version that you release. For the purposes of this conversation, let’s assume that we’re using the database server that’s built in to Xojo and not an external database server; and that we’re saving our database to a file in our Documents folder. (Think about something like QuickBooks) Every time we open the app, we choose a database file and open it to view and manage what’s inside.

But what happens when the new version of your app uses a slightly altered version of your database schema? Upon opening the data file, it needs to recognize that it’s schema is out of date and update it without destroying any data.

While version 1.0 may declare the entire schema with a series of complete CREATE commands (and certainly new files could be created that way), the application must track each modification that occurred between 1.0 and 1.1… and apply it to a data file that was created with 1.0 in order to upgrade it to a datafile that will work with version 1.1 of your software.

Before I go and develop a whole versioning system, I thought that I’d put this out here and ask if I’m reinventing the wheel or not. Does anyone have any experience with this? Does something already exist that I should consider using? Even general feedback and ideas of how you might do this would be appreciated.

I usually add a table called VERSION that contains ONE field, and ONE record
each time the app starts it reads that value… if the app is versioned ahead of the database, it executes code to bring it up to date, and update the version table …

hmmm… that sounds like a reasonable approach for recognizing the version of the data file. What about the system of tracking changes and updating it’s schema?

I’m thinking of some sort of database that is internal to the app (perhaps populated by loading an external file) that tracks the ALTER statements for each version. You add a record for each version in a version table… and then look for the most recent record when checking the version. Then a second table for the ALTER statements that would need to be executed in order to bring a datafile up to the latest version.

[quote=296866:@Kristin Green]hmmm… that sounds like a reasonable approach for recognizing the version of the data file. What about the system of tracking changes and updating it’s schema?

I’m thinking of some sort of database that is internal to the app (perhaps populated by loading an external file) that tracks the ALTER statements for each version. You add a record for each version in a version table… and then look for the most recent record when checking the version. Then a second table for the ALTER statements that would need to be executed in order to bring a datafile up to the latest version.[/quote]

at some point you may also need to make bigger changes to the existing schema where you need to move existing data into a new data structure. In that case you would need to create the new database and move the old data to the new in code…

For that case you would either need to either build a conversion method into the new version, or provide a separate conversion utility.

While you may think such changes would never be necessary, over time feature/scope creep for the app tends to happen in ways unanticipated originally, and so I wonder if the generic approach you suggest might be more work to set up than it might be worth for the long run.

  • Karen

I wrote a customizable personal database manager (http://www.rdS.com/ds_lockbox/index.html) and I use the method above, over the years the database schema has changed 9 times, as various new features were added etc.

I did not bother to create a complex schema comparison function… there is a method that has database code to apply the updates on a version by verison level. sometimes its adding a new field, sometimes adding a whole new table.

This way older versions of the program stay compatible with older versions of the database.

I do raise an error message if the version of the existing database is greater than the supported version of the installed app (ie. someone replaced a newer version of the app with an older version)

I also add a Creator table with one Record that holds my informations (first name, last name, eMail, Application Name, etc.).

None of theses two Tables are crypted (it can be read by anyone).

There was a conversation on this topic not too long ago. I don’t have time to look for it now, but perhaps someone has a link?

As an aside, do not update the original copy of the database that ships within or next to your app. Copy it someplace else, like Application Support, first.

While I agree with what Kem just said… Personally I rarely ship an app with a populated or template database… (unless the data I would supply is the focal point of the app)… I usually include SQL code that builds the app (in the correct location)

I’ve written a tool that I use to perform schema maintenance. The tool saves the schema updates and writes a Xojo module to maintain the database. It also writes an ORM class for each table. I then just import the whole lot into my project - saves me heaps of time.

@Dave S
I see what you mean… simply adding lines to the method responsible for updating the database schema instead of fetching commands from a database. While I like the simplicity of your idea, something tells me that creating a database of ALTER statements will somehow allow me to keep track of things better… keep the code cleaner… etc… However, the idea of shipping an app with a pre-populated database (even if that database is only for internal use) does sound like a bad idea.

Here is another idea: I wonder if including an .sql file in the project would work. Reading in one line of a text file at a time… each line is an SQL statement… blank lines and lines that start with # are skipped… keep reading lines until a semi-colon is reached… if it failed, you could kick back an error with the offending line number. You could also edit it with a standard text editor instead of having to make every command a string and then execute that command.

@Wayne Golding
Does your tool update existing data files so that no data is lost? It sounds pretty advanced… a tool that writes code? wow

@Wayne Golding
You’ve just opened my mind to the idea of using ORM, and I thank you for that. I had not considered that before. Probably a big enough topic for it’s own thread. :wink:

You could put the data from that sql file into a constant as well.

Keep in mind that you might want schema changes inside of a transaction as well so that you can roll back if something fails.

(Darn autocorrect)

Wayne wrote a blog post on the Xojo blog describing one technique:

https://blog.xojo.com/2014/12/06/guest-post-managing-database-schema-control-over-multiple-versions/

Something similar to this is also built in to the Storm ORM:

https://github.com/paullefebvre/storm

Thanks Paul!

I like Wayne’s approach but I wonder how it would handle a large number of schema changes per version. I’m also not a fan of writing and maintaining SQL statements as strings within SQLExecute commands. I’d rather have a true .sql file that I can compose in an editor that provides proper indentation and colour-coding… and then have my program ‘import’ it. This would also allow me to have separate .sql files for each version and potentially hundreds of statements per version without things getting out of hand.

I think I’ll create something similar that retrieves each version’s schema changes from external files but it has been great to see that everyone was thinking the same way I was… let’s me know that I’m on the right track and not reinventing the wheel.

[quote=297008:@Kristin Green]Thanks Paul!

I like Wayne’s approach but I wonder how it would handle a large number of schema changes per version. I’m also not a fan of writing and maintaining SQL statements as strings within SQLExecute commands. I’d rather have a true .sql file that I can compose in an editor that provides proper indentation and colour-coding… and then have my program ‘import’ it. This would also allow me to have separate .sql files for each version and potentially hundreds of statements per version without things getting out of hand.

I think I’ll create something similar that retrieves each version’s schema changes from external files but it has been great to see that everyone was thinking the same way I was… let’s me know that I’m on the right track and not reinventing the wheel.[/quote]

You can compose your sql in any tool you want then just drag & drop the resulting file it the ide or paste its contents into a constant in the IDE and use it

This is essentially how I’ve used Storm. I would have an update SQL file for each version that contains all the SQL to update the schema. I drag this file into the project and then let Storm process the SQL to update the DB.

I’d love to learn more about Storm! I’ve downloaded the files but I’m not sure what to do with them. Do you have instructions or a tutorial for this?

The wiki on the GitHub site has some docs:

https://github.com/paullefebvre/storm/wiki/Getting-Started

Kristin

I didn’t read this entire thread so if this was mentioned… sorry for the duplication.

Before I explain our process, unlike a lot of applications, most of our applications allow the user to create an unlimited number of databases to manage client data. Most accountants refer to this as service bureau type software. Quickbooks allows you to do this as do tax software that most accountants use for tax preparation.

XOJO was not our first development language and in our prior language of choice (Visual Foxpro) we basically just had a table that has the database version number in it and whenever it didn’t match what was in our latest application EXE, there was code to update it to the current version.

In XOJO, we decided to take a different approach because of some additional benefits that could be derived from the additional time it took to make this work. Rather than just track the version number, we decided to write our own schema manager. We keep all our schemas in this application and whenever we need to modify a schema for an application, we do it through the schema manager. We then export a file from the schema manager that is distributed with the application that has the schema in it. The schema manager allows you to create new schemas, make changes (add tables or fields), and tracks those changes over time. You can also identify fields that can be exported and enter additional information specifically for exporting the data.

Our service bureau applications can create new blank databases, update databases, and export data based on the information in the schema. Naturally, we had to add methods to our applications to compare versions and update it where necessary, as well as add “default” data to new fields that might be added. Whenever the schema needs to be updated, it will create a new temporary database, import data from the current one, make any changes to the data necessary to accommodate schema changes, rename the current database (just in case something goes wrong we have a backup), and then rename the temporary database to the name the user assigned to the original database.

It all works pretty well and we have the added benefit of being able to print the schema for any application using the schema database manager.

This is a lot of work if you’re only going to develop one application, but if you plan to develop multiple applications that rely on a similar approach for versioning, in my opinion, this has benefits that you will appreciate as your database/application evolve.

[quote=297090:@Paul Lefebvre]The wiki on the GitHub site has some docs:

https://github.com/paullefebvre/storm/wiki/Getting-Started[/quote]
duh! I should have seen that. Sorry… just getting started with github.