9

I manage an open source PHP/MySQL web application used by a few K-12 schools and some colleges. I'm also the project's only developer. While it used to be little more than a source download of an application my employer hosts, I've worked over the last year to make it into a "real" open source project, with documentation, numbered releases, public changelogs, etc.

I'm looking to improve the upgrade process, and one of the potentially painful areas (especially for IT expertise-starved schools) is in changes to the database schema between releases. They don't tend to happen often or be drastic changes but I would appreciate suggestions on the process.

Currently, I maintain a base SQL install script to setup the database in a new install. This includes the complete schema for the current release; no further action is required for a new install. Changes that happen between releases are stored in upgrade-$releasever.sql scripts, and it's necessary to run all the upgrade scripts incrementally for any releases that were skipped.

Shell scripts aren't a good fit, because many of our users operate on hosts without shell access. Due to other priorities, a complex PHP browser-based installer/upgrade script is unlikely to materialize. I would, however, like to do something with a browser-based PHP script to simplify the upgrades. Suggestions on how to approach it?

Improve this question

3 Answers 3

Reset to default
3

My project has an update.php file that the user runs through their browser after they have installed a new version of the software. That update script checks a database version number that is kept in a table in the active database and does any database alter operations that will get that database schema up to the latest one, which includes updating that database version number.

Improve this answer
1
  • I looked over your upgrade.php script and that seems to be what I had in mind. Thanks.
    – Michael
    Commented May 13, 2011 at 2:38
3

Drupal CMS has an interesting solution to your problem. I suggest taking a look at Drupal in general, if you are developing web-based solutions using PHP. It is my favourite PHP CMS and I will be biased enough to say it is the best out there. ;)

Drupal offers quite sophisticated database interaction wrapper. It allows to abstract the actual database type from the module developer, so you don't really care whether the server is running PostgreSQL, MySQL, etc. You can create your own interface as well. Every module is required to provide module.install file which contains hook_schema and hook_install. hook_schema is used for declaring table schema for the module, while hook_install runs the installation procedure. The architecture supports the notion of updates as well, so if the user has the module installed already, the right update hooks are invoked and allow to update the table schema easily.

Take a look: http://drupal.org/node/146862 Even if Drupal isn't for you, I am sure you can learn something from their architectural decisions.

Improve this answer
0

We did this by creating version folders. We placed all the database scripts in that folder. When it came time to roll this out to a new customer they got the latest version.

We also kept any upgrade scripts in this folder. That way we could migrate a customer from version x to version y.

It's not the best method... but it worked for us.

Improve this answer
1
  • If I understand, that sounds similar to how I'm handling it now - putting my users in charge of running upgrade scripts themselves, and in the correct order.
    – Michael
    Commented May 13, 2011 at 2:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.