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?