On any fast paced software development project, the task of keeping the development and production databases in sync become a sticky wicket. Magento offers a system to create a versioned resource migration scripts that can help your team deal with this often contentious part of the development process.

The following is part of a longer series about Magento aimed at developers familiar with PHP MVC development. While each article can be read stand alone, each article does build on concepts and code covered in previous articles. If you’re confused, be sure to catch up on the older stuff first.

Last time we created a model for a weblog post. At the time, we ran our CREATE TABLE statements directly against the database. This time, we’ll create a Setup Resource for our module that will create the table for us. We’ll also create an upgrade script for our module that will update an already installed module. The steps we’ll need to take are

1. Add the Setup Resource to our config

2. Create our resource class file

3. Create our installer script

4. Create our upgrade script

Adding the Setup Resource

So, let’s continue with the weblog module we created last time. In our <resources /> section, add the following

<resources>
    <!-- ... -->
    <weblog_setup>
        <setup>
            <module>Alanstormdotcom_Weblog</module>
            <class>Alanstormdotcom_Weblog_Model_Resource_Mysql4_Setup</class>
        </setup>
        <connection>
            <use>core_setup</use>
        </connection>
    </weblog_setup>
    <!-- ... -->
</resources>

The <weblog_setup> tag will be used to uniquely identify this Setup Resource. It’s encouraged, but not necessary, that you use the modelname_setup naming convention. The <module>Alanstormdotcom_Weblog</module> tag block should contain the Packagename_Modulename of your module. Finally, <class>Alanstormdotcom_Weblog_Model_Resource_Mysql4_Setup</class> should contain the name of the class we’ll be creating for our Setup Resource. For basic setup scripts it’s not necessary to create a custom class, but by doing it now you’ll give yourself more flexibility down the line.

After adding the above section to your config, clear your Magento cache and try to load any page of your Magento site. You’ll see an exception something like

Fatal error: Class 'Alanstormdotcom_Weblog_Model_Resource_Mysql4_Setup' not found in

Magento just tried to instantiate the class you specified in your config, but couldn’t find it. You’ll want to create the following file, with the following contents.

File: app/code/local/Alanstormdotcom/Weblog/Model/Resource/Mysql4/Setup.php

class Alanstormdotcom_Weblog_Model_Resource_Mysql4_Setup extends Mage_Core_Model_Resource_Setup {
}

Now, reload any page of your Magento site. The exception should be gone, and your page should load as expected.

Creating our Installer Script

Next, we’ll want to create our installer script. This is the script that will contain any CREATE TABLE or other SQL code that needs to be run to initialize our module.

First, take a look at your config.xml file

<modules>
    <Alanstormdotcom_Weblog>
        <version>0.1.0</version>
    </Alanstormdotcom_Weblog>
</modules>

This section is required in all config.xml files, and identifies the module as well as the its version number. Your installer script’s name will be based on this version number. The following assumes the current version of your module is 0.1.0.

Create the following file at the following location

File: app/code/local/Alanstormdotcom/Weblog/sql/weblog_setup/mysql4-install-0.1.0.php

echo 'Running This Upgrade: '.get_class($this)."n <br /> n";
die("Exit for now");    

The weblog_setup portion of the path should match the tag you created in your config.xml file (<weblog_setup />). The 0.1.0 portion of the filename should match the starting version of your module. Clear your Magento cache and reload any page in your Magento site and you should see something like

Running This Upgrade: Alanstormdotcom_Weblog_Model_Resource_Mysql4_Setup
Exit for now
 ...

Which means your update script ran. Eventually we’ll put our SQL update scripts here, but for now we’re going to concentrate on the setup mechanism itself. Remove the “die” statement from your script so it looks like the following

echo 'Running This Upgrade: '.get_class($this)."n <br /> n";

Reload your page. You should see your upgrade message displayed at the top of the page. Reload again, and your page should be displayed as normal.

Resource Versions

Magento’s Setup Resources allow you to simply drop your install scripts (and upgrade scripts, which we’ll get to in a bit) onto the server, and have the system automatically run them. This allows you to have all your database migrations scripts stored in the system in a consistent format.

Using your favorite database client, take a look at the the core_resource table

mysql> select * from core_resource;
+-------------------------+---------+
| code                    | version |
+-------------------------+---------+
| adminnotification_setup | 1.0.0   | 
| admin_setup             | 0.7.1   | 
| amazonpayments_setup    | 0.1.2   | 
| api_setup               | 0.8.1   | 
| backup_setup            | 0.7.0   | 
| bundle_setup            | 0.1.7   | 
| catalogindex_setup      | 0.7.10  | 
| cataloginventory_setup  | 0.7.5   | 
| catalogrule_setup       | 0.7.7   | 
| catalogsearch_setup     | 0.7.6   | 
| catalog_setup           | 0.7.69  | 
| checkout_setup          | 0.9.3   | 
| chronopay_setup         | 0.1.0   | 
| cms_setup               | 0.7.8   | 
| compiler_setup          | 0.1.0   | 
| contacts_setup          | 0.8.0   | 
| core_setup              | 0.8.13  | 
| cron_setup              | 0.7.1   | 
| customer_setup          | 0.8.11  | 
| cybermut_setup          | 0.1.0   | 
| cybersource_setup       | 0.7.0   | 
| dataflow_setup          | 0.7.4   | 
| directory_setup         | 0.8.5   | 
| downloadable_setup      | 0.1.14  | 
| eav_setup               | 0.7.13  | 
| eway_setup              | 0.1.0   | 
| flo2cash_setup          | 0.1.1   | 
| giftmessage_setup       | 0.7.2   | 
| googleanalytics_setup   | 0.1.0   | 
| googlebase_setup        | 0.1.1   | 
| googlecheckout_setup    | 0.7.3   | 
| googleoptimizer_setup   | 0.1.2   | 
| ideal_setup             | 0.1.0   | 
| log_setup               | 0.7.6   | 
| newsletter_setup        | 0.8.0   | 
| oscommerce_setup        | 0.8.10  | 
| paybox_setup            | 0.1.3   | 
| paygate_setup           | 0.7.0   | 
| payment_setup           | 0.7.0   | 
| paypaluk_setup          | 0.7.0   | 
| paypal_setup            | 0.7.2   | 
| poll_setup              | 0.7.2   | 
| productalert_setup      | 0.7.2   | 
| protx_setup             | 0.1.0   | 
| rating_setup            | 0.7.2   | 
| reports_setup           | 0.7.7   | 
| review_setup            | 0.7.4   | 
| salesrule_setup         | 0.7.7   | 
| sales_setup             | 0.9.38  | 
| sendfriend_setup        | 0.7.2   | 
| shipping_setup          | 0.7.0   | 
| sitemap_setup           | 0.7.2   | 
| strikeiron_setup        | 0.9.1   | 
| tag_setup               | 0.7.2   | 
| tax_setup               | 0.7.8   | 
| usa_setup               | 0.7.0   |
| weblog_setup            | 0.1.0   | 
| weee_setup              | 0.13    | 
| wishlist_setup          | 0.7.4   | 
+-------------------------+---------+
59 rows in set (0.00 sec)

This table contains a list of all the installed modules, along with the installed version number. You can see our module near the end

| weblog_setup            | 0.1.0   | 

This is how Magento knows not to re-run your script on the second, and on all successive, page loads. The weblog_setup is already installed, so it won’t be updated. If you want to re-run your installer script (useful when you’re developing), just delete the row for your module from this table. Let’s do that now, and actually add the SQL to create our table. So first, run the following SQL.

DELETE from core_resource where code = 'weblog_setup';

We’ll also want to drop the table we manually created in the previous article.

DROP TABLE blog_posts;

Then, add the following code to your setup script.

$installer = $this;
$installer->startSetup();
$installer->run("
    CREATE TABLE `{$installer->getTable('weblog/blogpost')}` (
      `blogpost_id` int(11) NOT NULL auto_increment,
      `title` text,
      `post` text,
      `date` datetime default NULL,
      `timestamp` timestamp NOT NULL default CURRENT_TIMESTAMP,
      PRIMARY KEY  (`blogpost_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    INSERT INTO `{$installer->getTable('weblog/blogpost')}` VALUES (1,'My New Title','This is a blog post','2009-07-01 00:00:00','2009-07-02 23:12:30');        
");
$installer->endSetup();

Clear your Magento cache and reload any page in the system. You should have a new blog_posts table with a single row.

Anatomy of a Setup Script

So, let’s go over the script line-by-line. First, there’s this (or is that $this?)

$installer = $this;

Each installer script is run from the context of a Setup Resource class, the class you created above. That means any reference to $this from within the script will be a reference to an object instantiated from this class. While not necessary, most setup scripts in the core modules will alias $this to a variable called installer, which is what we’ve done here. While not necessary, it is the convention and it’s always best to follow the convention unless you have a good reason for breaking it.

Next, you’ll see our queries are bookended by the following two method calls.

$installer->startSetup();
//...
$installer->endSetup();

If you take a look at the Mage_Core_Model_Resource_Setup class in app/code/core/Mage/Core/Model/Resource/Setup.php (which your setup class inherits from) you can see that these methods do some basic SQL setup

    public function startSetup()
    {
        $this->_conn->multi_query("SET SQL_MODE='';
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
");

        return $this;
    }

    public function endSetup()
    {
        $this->_conn->multi_query("
SET SQL_MODE=IFNULL(@OLD_SQL_MODE,'');
SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS,0);
");
        return $this;
    }

Finally, there’s the call to the run method

$installer->run(...);

which accepts a string containing the SQL needed to setup your database table(s). You may specify any number of queries, separated by a semi-colon. You also probably noticed the following

$installer->getTable('weblog/blogpost')

The getTable method allows you to pass in a Magento Model URI and get its table name. While not necessary, using this method ensure that your script will continue to run, even if someone changes the name of their table in the config file. The Mage_Core_Model_Resource_Setup class contains many useful helper methods like this. The best way to become familiar with everything that’s possible is to study the installer scripts used by the core Magento modules.

Module Upgrades

So, that’s how you create a script that will setup your initial database tables, but what if you want to alter the structure of an existing module? Magento’s Setup Resources support a simple versioning scheme that will let you automatically run scripts to upgrade your modules.

Once Magento runs an installer script for a module, it will never run another installer for that module again (short of manually deleting the reference in the core_resource table). Instead, you’ll need to create an upgrade script. Upgrade scripts are very similar to installer scripts, with a few key differences.

To get started, we’ll create a script at the following location, with the following contents

File: app/code/local/Alanstormdotcom/Weblog/sql/weblog_setup/mysql4-upgrade-0.1.0-0.2.0.php

echo 'Testing our upgrade script (mysql4-upgrade-0.1.0-0.2.0.php) and halting execution to avoid updating the system version number <br />';
die();

Upgrade scripts are placed in the same folder as your installer script, but named slightly differently. First, and most obviously, the file name contains the word upgrade. Secondly, you’ll notice there are two version numbers, separated by a “-“. The first (0.1.0) is the module version that we’re upgrading from. The second (0.2.0) is the module version we’re upgrading to.

If we cleared our Magento cache and reloaded a page, our script wouldn’t run. We need to update the the version number in our module’s config.xml file to trigger the upgrade

<modules>
    <Alanstormdotcom_Weblog>
        <version>0.2.0</version>
    </Alanstormdotcom_Weblog>
</modules>

With the new version number in place, we’ll need to clear our Magento cache and load any page in our Magento site. You should now see output from your upgrade script.

Before we continue and actually implement the upgrade script, there’s one important piece of behavior you’ll want to be aware of. Create another upgrade file at the following location with the following contents.

File: app/code/local/Alanstormdotcom/Weblog/sql/weblog_setup/mysql4-upgrade-0.1.0-0.1.5.php

echo 'Testing our upgrade script (mysql4-upgrade-0.1.0-0.1.5.php) and NOT halting execution <br />';

If you reload a page, you’ll notice you see BOTH messages. When Magento notices the version number of a module has changed, it will run through all the setup scripts needed to bring that version up to date. Although we never really created a version 0.1.5 of the Weblog module, Magento sees the upgrade script, and will attempt to run it. Scripts will be run in order from lowest to highest. If you take a peek at the core_resource table,

mysql> select * from core_resource where code = 'weblog_setup';
+--------------+---------+
| code         | version |
+--------------+---------+
| weblog_setup | 0.1.5   | 
+--------------+---------+
1 row in set (0.00 sec)

you’ll notice Magento considers the version number to be 1.5. That’s because we completed executing the 1.0 to 1.5 upgrade, but did not complete execution of the 1.0 to 2.0 upgrade.

So, with all that out of the way, writing our actual upgrade script is identical to writing an installer script. Let’s change the 0.1.0-0.2.0 script to read

$installer = $this;
$installer->startSetup();
$installer->run("
    ALTER TABLE `{$installer->getTable('weblog/blogpost')}`
    CHANGE post post text not null;
");
$installer->endSetup();
die("You'll see why this is here in a second");

Try refreshing a page in your Magento site and … nothing. The upgrade script didn’t run. The post field in our table still allows null values, and more importantly, the call to die() did not halt execution. Here’s what happened

1. The weblog_setup resource was at version 0.1.0
2. We upgraded our module to version 0.2.0
3. Magento saw the upgraded module, and saw there were two upgrade scripts to run; 0.1.0 to 0.1.5 and 0.1.0 to 0.2.0
4. Magento queued up both scripts to run
5. Magento ran the 0.1.0 to 0.1.5 script
6. The weblog_setup resource is now at version 0.1.5
7. Magento ran the 0.1.0 to 0.2.0 script, execution was halted
8. On the next page load, Magento saw weblog_setup at version 0.1.5 and did not see any upgrade scripts to run since both scripts indicated they should be run from 0.1.0

The correct way to achieve what we wanted would have been to name our scripts as follows

mysql4-upgrade-0.1.0-0.1.5.php #This goes from 0.1.0 to 0.1.5
mysql4-upgrade-0.1.5-0.2.0.php #This goes 0.1.5 to 0.2.0

Magento is smart enough to run both scripts on a single page load. You can go back in time and give this a try by updating the core_resource table

update core_resource set version = '0.1.0' where code = 'weblog_setup';
...

It’s one of the odd quirks of the Magento system that the updates will run as previously configured. This means you’ll want to be careful with multiple developers adding update scripts to the system. You’ll either want a build-meister/deployment-manager type in charge of the upgrade scripts or (heaven forbid) developers will need to talk to one another.

Wrap-up

You should now know how to use Magento Setup Resources to create versioned database migration scripts, as well as understand the scripts provided in the core modules. Beyond having a standard way for developers to write migration scripts, Setup Resources become much more important when creating and modifying Entity Attribute Value models, which is where we’re headed next time.

Like this article? Then you’ll love Commerce Bug, the must have debugging extension for anyone using Magento. Whether you’re just starting out or you’re a seasoned pro, Commerce Bug will save you and your team hours everyday. Grab a copy and start working with Magento instead of against it.