Opinion: Every Project should have an Upgrade Framework
- Share
-
- |
Read later
My Reading List
HostedQA, JIRA, Confluence, and Jive Forums all have implemented frameworks to manage changes to db schema's and data migrations between subsequent versions of their products. Rails Migrations also internalized this concept with first class support in the Rails framework in the Ruby world.
Patrick Lightbody, founder of HostedQA and co-founder of WebWork 2 thinks that all enterprise apps should include an upgrade framework on their projects to manage changes between versions. A framework that combines both code and SQL to change schemas and migrate data is needed, because simple SQL scripts are not enough and Hibernate's schema update utilities "take you from step A to Z without giving you an opportunity to make iterative changes to the data."
Further commenting on the benefits, Pat cites the familiar example of a developer sending out an email to the team with info on how to update their DB before running the latest checked in code.
15
Discuss
Patrick Lightbody, founder of HostedQA and co-founder of WebWork 2 thinks that all enterprise apps should include an upgrade framework on their projects to manage changes between versions. A framework that combines both code and SQL to change schemas and migrate data is needed, because simple SQL scripts are not enough and Hibernate's schema update utilities "take you from step A to Z without giving you an opportunity to make iterative changes to the data."
To really make software easy to deploy, you need to streamline the upgrade process to be trivial while also automating upgrades from any arbitrary version to any other arbitrary version.
Further commenting on the benefits, Pat cites the familiar example of a developer sending out an email to the team with info on how to update their DB before running the latest checked in code.
If your team had an upgrade framework, "Bob" wouldn't have to send out an email and depend on each team member reading the email and correctly executing his recommended steps. In short: it eliminates room for human error during development.Pat also provided some advice on how to best implement an upgrade framework:
- Don't depend on your O/R framework
- Don't depend on any of your application code, just stick to plain JDBC
Rate this Article
- Editor Review
- Chief Editor Action
Submit
Submit
Hello stranger!
You need to Register an InfoQ account or Login or login to post comments. But there's so much more behind being registered.Get the most out of the InfoQ experience.
Tell us what you think
Community comments
One small change...
by
Patrick Lightbody
Posted
something in between
by
Tom Eugelink
Posted
Could there be a generic framework for this?
by
Floyd Marinescu
Posted
Re: Could there be a generic framework for this?
by
Marc Logemann
Posted
Re: Could there be a generic framework for this?
by
Patrick Lightbody
Posted
Re: Could there be a generic framework for this?
by
Don Brown
Posted
Re: Could there be a generic framework for this?
by
Chris Gardner
Posted
Re: Could there be a generic framework for this?
by
Sam Pullara
Posted
Re: Could there be a generic framework for this?
by
James Richardson
Posted
Re: Could there be a generic framework for this?
by
Patrick Lightbody
Posted
Re: Could there be a generic framework for this?
by
Patrick Lightbody
Posted
There is a generic framework for this!
by
Ahmed Mohombe
Posted
Re: Could there be a generic framework for this?
by
Tim Brown
Posted
Re: Could there be a generic framework for this?
by
Patrick Lightbody
Posted
Re: Could there be a generic framework for this?
by
Daniel Ostermeier
Posted
One small change...
by
Patrick Lightbody
I'm glad to see my article get picked up! I'd make one small change to the subject of the InfoQ article: scratch the word "enterprise". That word typically implies shipping software, and in my article I argue that even simple internal apps would benefit from an upgrade framework.
Could there be a generic framework for this?
by
Floyd Marinescu
I'm curious if there could be a generic framework for this that Java projects could use, instead of rolling their own. The rails community is benefitting from the consistency that migrations bring, we could use someting similar.
Re: Could there be a generic framework for this?
by
Marc Logemann
There could be a generic framework for this but IMO there is none. I researched this topic at laest for some days and our product has the same problems as many others that depend on DB upgrades.
Right now i am using an XML schema by Kodo and some kodo provided ANT tasks. With that its possible to even generate ALTER statements based on the current DB and the supplied XML DB schema. But there are some problems too because not all scenarios can be handled.
IMO an open source framework soley conentrating on this issue would be a killer. I think my current approach is not so bad and Solarmetric aka BEA did a good job but the motivation behind the described toolkit was not to create an DB upgrade framework but something that handles the existing Kodo toolkits inluding Kodos visual schema tool.
If there are some people out there with good ideas and some motivation, i could think of creating such an initiative, i would have to do something in that area anyway, but if nobody joins, i will do it closed source :)
Right now i am using an XML schema by Kodo and some kodo provided ANT tasks. With that its possible to even generate ALTER statements based on the current DB and the supplied XML DB schema. But there are some problems too because not all scenarios can be handled.
IMO an open source framework soley conentrating on this issue would be a killer. I think my current approach is not so bad and Solarmetric aka BEA did a good job but the motivation behind the described toolkit was not to create an DB upgrade framework but something that handles the existing Kodo toolkits inluding Kodos visual schema tool.
If there are some people out there with good ideas and some motivation, i could think of creating such an initiative, i would have to do something in that area anyway, but if nobody joins, i will do it closed source :)
Re: Could there be a generic framework for this?
by
Patrick Lightbody
Possibly. I think a better option is for a Rails-like stack to be released that includes this as part of it. I have such a stack that I've build HostedQA on (WebWork, Maven, Spring, iBatis, upgrade framwork, convention-based, etc)... and I hope to release it when I get the time. I think that would be a good place for it.
Re: Could there be a generic framework for this?
by
Don Brown
I posted my findings on this topic on my blog, and someone added a comment pointing to Scriptella. Apparently, it is an open source project that was derived out of the same requirements.
Re: Could there be a generic framework for this?
by
Sam Pullara
This is one of the first things that we built at Gauntlet when we realized that following the instructions in an email wasn't going to work very well. I should probably rewrite it and release it as OSS but it was essentially a clone of rails migration without even knowing. Other things that could be added include support for multiple database types, non-database data (like preferences), etc. It would be nice if there was a generic superset DDL framework so I wouldn't have to write so much database specific SQL, but oh well.
Re: Could there be a generic framework for this?
by
James Richardson
These auto-upgrade processes are all good. However, they would be even better if people took into account that in larger organisations application "user" accounts tend not to have table creation & altering privileges.
The principle of least privilege applies very much here.
So, rather than actually applying the changes to the DB, it would be great if the changes could be output to a file, so that they can be applied using normal change procedures (which means that DB owner accounts would be used to apply them)
Yes - it might not be so whizz-bang - but its much more likely to avoid the policy exceptions required when applications assume that they have schema privileges...
The principle of least privilege applies very much here.
So, rather than actually applying the changes to the DB, it would be great if the changes could be output to a file, so that they can be applied using normal change procedures (which means that DB owner accounts would be used to apply them)
Yes - it might not be so whizz-bang - but its much more likely to avoid the policy exceptions required when applications assume that they have schema privileges...
Re: Could there be a generic framework for this?
by
Patrick Lightbody
James,
In my blog entry, I pointed out to Jive's more advanced approach of telling users what to do manually if the automation step didn't work. So yes, in larger organizations, you might want to take it one step further.
In my blog entry, I pointed out to Jive's more advanced approach of telling users what to do manually if the automation step didn't work. So yes, in larger organizations, you might want to take it one step further.
Re: Could there be a generic framework for this?
by
Patrick Lightbody
Sam,
Good notes. When I was at Jive and helped build the initial framework (it's been improved a ton since then), I probably recreated the same thing a lot of us have: an API to create DDL scripts for many DB vendors.
With HostedQA, I didn't go as far because the only DBs I care about at the moment on HSQL and Postgres. So instead I just made the framework know how to pick up SQL scripts and would append the vendor name to the file. It's a little more redundant since the DDL is very similar in both scripts, but I didn't have to write that API again :)
It looks like some people are going to kick start an opensource project to do this, Database Migration for Java (dbm4j). Drop me a note if you want to send us your code/ideas to be included!
Good notes. When I was at Jive and helped build the initial framework (it's been improved a ton since then), I probably recreated the same thing a lot of us have: an API to create DDL scripts for many DB vendors.
With HostedQA, I didn't go as far because the only DBs I care about at the moment on HSQL and Postgres. So instead I just made the framework know how to pick up SQL scripts and would append the vendor name to the file. It's a little more redundant since the DDL is very similar in both scripts, but I didn't have to write that API again :)
It looks like some people are going to kick start an opensource project to do this, Database Migration for Java (dbm4j). Drop me a note if you want to send us your code/ideas to be included!
There is a generic framework for this!
by
Ahmed Mohombe
IMHO there is such a generic framework: DDLUtils
Unfortunately it's still beta quality, but it does allot already.
It does all the tasks required for such an upgrade, and I've already used it in one small project to take care of the db upgrades. It has reverse engineer of DB, create DB, export data, import data, generate DDLs, generate upgrade SQLs, etc.
I don't know how complete is the support for all databases, but for MySQL is pretty good, so I was able to use it without problems.
Ahmed.
Unfortunately it's still beta quality, but it does allot already.
It does all the tasks required for such an upgrade, and I've already used it in one small project to take care of the db upgrades. It has reverse engineer of DB, create DB, export data, import data, generate DDLs, generate upgrade SQLs, etc.
I don't know how complete is the support for all databases, but for MySQL is pretty good, so I was able to use it without problems.
Ahmed.
Re: Could there be a generic framework for this?
by
Tim Brown
Doesn't Hibernate do some of this? I seem to remember that it can (and will, if configured) update the schema as it's configuration files are updated.
Don't remember if it's a destructive upgrade or not.
We've used Martin Fowler & Pramod Sadalage's approach with good success. The main issue we had was overflowing DB2 logs when applying a MASSIVE amount of changes linearly. That wasn't an issue with the approach, more of an issue with the details of our environment.
Just read Marc's response below, and I agree a common approach to this challenge would indeed be killer. Fun project indeed.
Don't remember if it's a destructive upgrade or not.
We've used Martin Fowler & Pramod Sadalage's approach with good success. The main issue we had was overflowing DB2 logs when applying a MASSIVE amount of changes linearly. That wasn't an issue with the approach, more of an issue with the details of our environment.
Just read Marc's response below, and I agree a common approach to this challenge would indeed be killer. Fun project indeed.
Re: Could there be a generic framework for this?
by
Patrick Lightbody
Tim,
Hibernate's stuff takes you from point A to point Z without giving you an opportunity to manipulate data or even do non-database stuff in between. It's not a good solution for a reliable, production ready upgrade system.
Hibernate's stuff takes you from point A to point Z without giving you an opportunity to manipulate data or even do non-database stuff in between. It's not a good solution for a reliable, production ready upgrade system.
Re: Could there be a generic framework for this?
by
Daniel Ostermeier
Patrick,
It is true that Hibernate takes you from point A to point Z, but only if that is the only information you give it. Just as you would create multiple upgrade tasks to handle each step of your upgrade, you can also split the changes in your hibernate .hbm.xml file into the individual changes.
For example, if you needed to add 3 tables, you can add 1 table at a time by creating 3 .hbm.xml file fragments, each containing the mapping for one of the tables. You can then execute hibernates update schema process on each of these mappings files in turn, upgrading you from point A to point B to point C ... etc.
With hibernates update process, the things to remember are
a) You can use partial .hbm.xml files that define only the necessary changes
b) You do not need to reference valid java classes in the mappings.
c) Hibernate will not delete tables, drop columns if they are not present in the mappings
You will still require some raw SQL to handle the migration of data, and if you are keen, the removal of old tables / columns. However, having hibernate deal with the database schema variations makes a huge difference.
It is true that Hibernate takes you from point A to point Z, but only if that is the only information you give it. Just as you would create multiple upgrade tasks to handle each step of your upgrade, you can also split the changes in your hibernate .hbm.xml file into the individual changes.
For example, if you needed to add 3 tables, you can add 1 table at a time by creating 3 .hbm.xml file fragments, each containing the mapping for one of the tables. You can then execute hibernates update schema process on each of these mappings files in turn, upgrading you from point A to point B to point C ... etc.
With hibernates update process, the things to remember are
a) You can use partial .hbm.xml files that define only the necessary changes
b) You do not need to reference valid java classes in the mappings.
c) Hibernate will not delete tables, drop columns if they are not present in the mappings
You will still require some raw SQL to handle the migration of data, and if you are keen, the removal of old tables / columns. However, having hibernate deal with the database schema variations makes a huge difference.
something in between
by
Tom Eugelink
We use something in between emails and a framework and works fine for the most common environment (being: only one brand DB engine in multiple enviroments like dev*n, tst, acc, prd). The approach is: every, and I mean every, change to the database has to be made using a SQL file.
These SQL files are named chronologically (yyyymmddx.sql) and checked into a directory in the code repository. The naming style takes care of multideveloper conflicts: FCFS.
If an environment is updated, the SQL files come along with the code changes, so the upgrader knows he has some SQL to execute against the DB's under his responsibility, before the application will run again. Similar a DB administrator can update the SQL part only from the repository and do the upgrade.
As an extra every SQL file logs its name in a history table, so it is visible when and who executed a script.
This solution works for 99% of all update situations and works fine as long as the upgrader is not an end-user.
My 2ct.
These SQL files are named chronologically (yyyymmddx.sql) and checked into a directory in the code repository. The naming style takes care of multideveloper conflicts: FCFS.
If an environment is updated, the SQL files come along with the code changes, so the upgrader knows he has some SQL to execute against the DB's under his responsibility, before the application will run again. Similar a DB administrator can update the SQL part only from the repository and do the upgrade.
As an extra every SQL file logs its name in a history table, so it is visible when and who executed a script.
This solution works for 99% of all update situations and works fine as long as the upgrader is not an end-user.
My 2ct.
