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?
Best Answer
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.