What is the best way to create a PHP/MySQL upgrade script for a software developer?

Question

I am a PHP software developer and am looking for the best solution to get around this concern I have. I am create a script that will in the future have new releases that includes new features, bugfixes, etc. I know how to do the code changes in the upgrade script, however I the script I am developing uses MySQL to store data in multiple tables.

Now here is the question, I have figured out how to make an install script for the initial release, however what is the best solution/method to making an upgrade script that can upgrade any previous version up to the latest version? The latest version has new MySQL tables (not a problem), however it also changes the database structure (new columns, delete columns, etc.) I will create a scenario below to better picture what I am worried about.

v0.1.0 - Initial Release

v0.1.1 - Has a new database table and some fields added to the table structure

v0.2.0 - Has more new fields added to different tables

My concern is the the upgrade v0.1.0 > v0.2.0 because there were changes between the two versions.

UPDATE I probably could have mentioned that I am using GitHub as my VCS for the code alone. All code changes are saved there and in the install or upgrade script for code changes, I just plan on overwriting the user's current files as they shouldn't have any data within class/function files.

Answer 1

In my experience, the best approach is to create a versioning table in MySQL that includes the application's version number along with any queries that change the structure of the database. So essentially:

CREATE TABLE versions (
    app_version DOUBLE NOT NULL,
    query TEXT,
    created TIMESTAMP NOT NULL 
);

With our install script, we take note of our initial application version and our destination application version and select all queries that need to be executed for this to work.

SELECT query FROM versions WHERE app_version > {$initialAppVersion} AND app_version <= {$destinationAppVersion} ORDER BY created ASC;

In PHP:

foreach ($resultset AS $row) {
    $stmt = $db->prepare($row['query']);
    try {
       $stmt->execute();
    } catch (Exception $e) { /* handle exception */ }
}

Answer 2

You could store the current schema version they are on somewhere.

For the upgrade script have it check to make sure it's on the previous version before it continues making any updates. This will prevent updates from being skipped or missed.

Answer 3

Another suggestion:

Some ORMs like Propel and Doctrine allow to create automatically such migration scripts.

Check for example:

• http://www.propelorm.org/wiki/Documentation/1.6/WhatsNew#Migrations
• http://www.doctrine-project.org/blog/new-to-migrations-in-1-1