Jetendo CMS now has scripted database upgrades

Sun, Feb 09, 2014 at 9:45PM

In addition to the automated database installation feature I finished last week, I've finished the database upgrade system now.

Database upgrade system features:

A simple framework for scripting changes to the database was made so that these changes can be distributed to other servers and to other developers through version control.

The system supports performing an automatic upgrade from one version to a newer version of the database in one step.  You don't have to run many separate upgrade steps when you are multiple version behind.  I built it to be able to perform the upgrades in sequence all at once.

There is a feature to automatically compare the installed database version to the source code version when the app first starts or when developer runs the cache clearing features.  This makes it very easy to make the database match the source code version.  We plan on make additional automation for pulling these upgrades from the github repository later, but for now, you can execute the "git pull origin" command yourself to upgrade Jetendo CMS.

Schema Validation and Synchronization SQL commands

Before an upgrade occurs, the system verifies the current schema against the source code schema.  If it is found to be in an invalid state the system generates the SQL commands necessary for correcting the database structure.  It asks you to run these queries manually so that you are sure you want to do this.  This prevents losing new work on the development server, for example, when someone adds an important new field, without using the scripted upgrade feature.  We don't want to force you to lose your customization automatically.  It's important to make you aware of these problems.

This features support all the feature Jetendo CMS uses including:

  • Force indexes to match
  • Force triggers to match
  • Force column structure to match
  • Force table structure to match
  • Force column order to match

Data loss protection built-in

Changing the database structure on a production server is dangerous, so I've added a lot of safe-guards to reduce the chances of ending up with a broken database.

  • Before running an upgrade script, the current schema is verified.  If it doesn't match, it can't be ignored on a production server, and must be manually repaired first.  On a development server, this can be ignored because it is assumed the developer has intentionally upgraded the database.  
  • If the developer is running a newer version of the database then is specified in the source code, then the system fails with an error alerting them of this problem, and it requires manual correction of the schema.  This would happen if they attempt to go back to a previous version of the software, which could always result in data loss since I don't plan on making a feature to downgrade the software.  They'd have to use other backups to do this.
  • Automatic backup of only the affected tables prior to an upgrade.  Better then a full backup, because it is faster / less potential downtime.
  • Any failure in the upgrade process results in aborting the remaining steps and a complete restoration of the affected tables.  If new tables were created, they are dropped.
  • Upon completion of the upgrade, a full schema verification is run.  If the schema doesn't match the version that you upgraded to, a full restoration of the tables is performed, and the upgrade is aborted.
  • The backups are not deleted after an upgrade.  This makes it more certain that a manual restore could be performed if the system fails to do it automatically.

When an upgrade completes, and it was a newer version then the source code schema, the new schema is saved, so you can publish it to other servers & version control.   It also creates a copy of the installed schema, which is to used for future pre-upgrade verification.  

Database upgrades are now required to be scripted

Developers will have to be trained / given documentation on how to use the framework to make database changes because making changes directly using a MySQL GUI is no longer possible if you want to maintain upgrade compatibility.  An upgrade script basically requires specifying the tables that will be affected, and running 1 or more queries to upgrade the database.  The implementation must follow an interface and use specific functions, logic and return values.   We've already included a few examples in the project in the "database-upgrades" directory.

Maintaining upgrade compatibility over time

If a user runs an old version of Jetendo for many months or years, and then chooses to upgrade to the latest version, there may have been dozens of database upgrades required to be compatible with the latest version.   While it is going to be possible to upgrade like this automatically, there is always a chance of a bug occurring in the process due to mistakes and/or lack of testing.  

If any mistake exists in the upgrade scripts, the user will be unable to upgrade, and will depend on fix being made to allow the upgrade to work as expected.   The system generates a log as it upgrades, so you know exactly where it failed.    A developer that is in charge of fixing the bug can download the version that was in source control system prior to that change being made.  Then the developer can install a fresh copy of the database based on that version of Jetendo CMS.  This makes it easier to reproduce the problem that caused the upgrade to fail.  My goal is to allow Jetendo CMS to be able to be upgraded all the way from the first version 1 to the most recent version without problems.   Someone can test this kind of upgrade every so often to make sure it stays compatible with an older version.  

No downgrade/uninstall support

It's important to realize that while we wish to make upgrades easy, we are not doing anything to support downgrading the software.   If you are concerned about the new version causing problems, the best thing to do is to make a full backup of the entire database and source code.   Always perform major upgrades on a copy of the software before applying to your production server(s).  This will ensure that if there is a problem, it won't impact your users.  Let us know if we have made upgrading too difficult, and we may be able to come up with a solution or help you in the process of upgrading the software.


This was a very complex system, but making upgrade scripts was made as simple as possible.   There was around 3000 to 4000 lines of new code written to add this feature.  It's very exciting to have this now because it fully completes the process of making Jetendo CMS easier to work with programmers throughout the world.

Visit the Jetendo CMS web site for more information.

Bookmark & Share