How best to handle database versioning?
lazarus / 16-Databases

----------------------------------------------------------------------
seaton · 2013-03-18 22:09

G'day one and all,

I'm curious asto how others with more DB application development
experience handle their database versioning during their development? 
And how do you handle updates through you application revisions?

Regards Stephen

----------------------------------------------------------------------
Chronos · 2013-03-18 22:28

If you application is monolithic and not modular then you could
suffice with single linear number going up similar to subversion
revision number. Then everytime then structure is changed you will
store SQL update script under this number. Then you could have some
component which will show to user that database is outdated and need
to upgrade to newer. Application could allow database backup before
upgrade process.

As nothing is simple as it looks there could be many other problems.
For example if you develop alfa version in trunk and maintain in
parallel stable version then you will end up with two persistence
branches and you will have to do not only source code merging but also
persistence structure merging. Which could go wild.

You should also maintain some initial sample data SQL script as empty
database is not enough for complete testing as data bindings and
structure constraint and other consistency checks.

Database structure version number itself could be stored in some table
named for example Version as simply row. You can also store complete
log of all diff changes of structure in some table with time of
upgrade.

It would be good idea to make some library or better package with
component for better code generalization and reuse.

----------------------------------------------------------------------
Graeme · 2013-03-19 09:16

We have a DBInfo table with a Version text field. This is the version
of the database. Our application checks at startup what version is in
the database, and knows the minimum version it can work with. If they
are out of sync, the application prompts the user to contact there
administrator, then quits.

As for the update scripts. We have a 'updates' folder. In there we
have directories numbered in sequential order. It is actually the
build number of our application. We place update scripts in the
related directory, and they too are numbered in sequence - that way we
know what order to apply them in. eg: 001-usesr.sql, 002-company.sql
etc... The last script is always 999-version.sql which updates the
DBInfo table with a new version number. Obviously not all releases
have update scripts. In those cases the directory is simply empty, or
doesn't exist.

I then developed an external tool (basic SQL editor & DB browser) that
also has an Upgrade function. I tell it the 'updates' directory
location, then have two edit fields where I enter the To and From
build numbers. The application will then run through those directories
and apply the scripts in order.

Years ago we had the application do the  DB updating at startup, but
that caused all kinds of problems. eg: Somebody ran the new executable
just to see, then in auto updated the database, but they were not
ready to roll out the new version yet. Then all existing clients
couldn't connect, because there executables were then out of date. So
now we separated the updating process, and allow the administrator to
do it in his own time. This also gives him a chance to make backups
before the update.

----------------------------------------------------------------------
seaton · 2013-03-21 07:24

Thanks for the info guys that's given a good direction to go in

Regards,

Stephen