Magento – Install, install upgrade, data and data upgrade scripts

If you are in the business of creating your own custom extension (module) for magento, then chances are that at some point you will need your extension to be able to persist data to database, this is where the installation scripts come in place. Later on, when you upgrade your extension and decide to add few new tables to database or few new columns to database table that was initially created by the original installation extension you will need to look for upgrade script. Finally, if you want to set some initial default values in the database tables of your extension you will need to look for data scripts.
This article assumes you already know how to build basic Magento extension, and are familiar with extension configuration file.
Please note that this article was written for Magento 1.7.0.2. Future releases of Magento might have completely new approach for install, upgrade and data scripts. Be sure to check on that.
Before we jump into the code, lets just do a quick recap on some basic Magento model understanding. In order to have a “fully” defined entity data model that persists to database, you need to create four files:

• model class file – dummy class, has no database persistence capabilities on its own,
• model resource class file – companion to model class file, enables the model to have persistence to database
• model collection class file – nifty way of manipulating several entities at once,
• installation file – the one that actually creates the required database tables to which to persist the entity or any other data

Install script (and two sample models)

Since it makes no sense to define installation scripts unless you have some models as well, this section will go ahead and create the models first then the actual installation script.
1. First we will start by creating a simple Inchoo_DBScript. The name of the extension might not be original, but it will have to do for this example.
Create the extension registration file app/etc/modules/Inchoo_DBScript.xml with the following content.

<?xml version="1.0"?>
 
<config>
    <modules>
        <Inchoo_DBScript>
            <active>true</active>
            <codePool>community</codePool>
        </Inchoo_DBScript>
    </modules>
</config>

2. Login into Magento administration interface and go under System > Configuration > Advanced > Advanced > Disable Modules > Output. You should be able to see Inchoo_DBScript extension there. In case you do not see it, be sure to clear the Magento cache under System > Cache Management (this usually helps).
3. Now lets go ahead and create extension configuration file with the following initial content.

<?xml version="1.0"?>
 
<config>
    <modules>
        <Inchoo_DBScript>
            <version>3.1.5.6</version>
        </Inchoo_DBScript>
    </modules>
    <global>
 
    </global>
</config>

This is your basic extension configuration file, which does “nothing” interesting at the moment. Whats interesting though is the version tag that specifies value 3.1.5.6. This is done in purpose so later we can have a more clean overview of correlation between this number and name of the installation, upgrade and data scripts.
4. Now lets go ahead and define configuration entries for our Ticket and Comment models. Edit the app/code/community/Inchoo/DBScript/etc/config.xml by adding the following in between the global tag.

        <models>
            <inchoo_dbscript>
                <class>Inchoo_DBScript_Model</class>
                <resourceModel>inchoo_dbscript_resource</resourceModel>
            </inchoo_dbscript>
            <inchoo_dbscript_resource>
                <class>Inchoo_DBScript_Model_Resource</class>
                <entities>
                    <ticket>
                        <table>inchoo_dbscript_ticket</table>
                    </ticket>
                    <comment>
                        <table>inchoo_dbscript_comment</table>
                    </comment>
                </entities>
            </inchoo_dbscript_resource>
        </models>

There is quite a lot going on here. First, everything we added in this code chunk goes within models tag which goes within global tag, so far is clear. Tag inchoo_dbscript within models tag is a freely given name, within which we define a so called class group for our models. You can think of a class group as a location on a folder within app/code/{community/local} folder. So the value within config > global > models > inchoo_dbscript > class is basically a folder location for Magento autoloader through which Magento knows where to find the model classes (to put it like that). By themselves Magento models cannot be persisted to database, they are just a dumb classes. In order to persist model to database, we need model resource class. Value within config > global > models > inchoo_dbscript > resourceModel is used to point to resource tag within the same config > global > models tag, which in this case is config > global > models > inchoo_dbscript_resource.
Tag inchoo_dbscript_resource seems a bit more complex at first then it is. First we have a definition of config > global > models > inchoo_dbscript_resource > class tag, whose value similar to model definition points to folder location where resource classes are located. Next we have config > global > models > inchoo_dbscript_resource > entities tag where we define the actual database tables for our models.
5. Now that we have defined the appropriate class groups within the config.xml, let us proceed with creating the model class files as shown below.
app/code/community/Inchoo/DBScript/Model/Ticket.php

<?php
 
class Inchoo_DBScript_Model_Ticket extends Mage_Core_Model_Abstract
{
    protected function _construct()
    {
        $this->_init('inchoo_dbscript/ticket');
    }
}

app/code/community/Inchoo/DBScript/Model/Comment.php

<?php
 
class Inchoo_DBScript_Model_Comment extends Mage_Core_Model_Abstract
{
    protected function _construct()
    {
        $this->_init('inchoo_dbscript/comment');
    }
}

6. Once the model class is defined, we go ahead and define model resource class like shown below.
app/code/community/Inchoo/DBScript/Model/Resource/Ticket.php

<?php
 
class Inchoo_DBScript_Model_Resource_Ticket extends Mage_Core_Model_Resource_Db_Abstract
{
    protected function _construct()
    {
        $this->_init('inchoo_dbscript/ticket', 'ticket_id');
    }
}

app/code/community/Inchoo/DBScript/Model/Resource/Comment.php

<?php
 
class Inchoo_DBScript_Model_Resource_Comment extends Mage_Core_Model_Resource_Db_Abstract
{
    protected function _construct()
    {
        $this->_init('inchoo_dbscript/comment', 'comment_id');
    }
}

7. Next we create the model collection class file like shown below.
app/code/community/Inchoo/DBScript/Model/Resource/Ticket/Collection.php

<?php
class Inchoo_DBScript_Model_Resource_Ticket_Collection extends Mage_Core_Model_Resource_Db_Collection_Abstract
{
    public function _construct()
    {
        $this->_init('inchoo_dbscript/ticket');
    }
}

app/code/community/Inchoo/DBScript/Model/Resource/Comment/Collection.php

<?php
class Inchoo_DBScript_Model_Resource_Comment_Collection extends Mage_Core_Model_Resource_Db_Collection_Abstract
{
    public function _construct()
    {
        $this->_init('inchoo_dbscript/comment');
    }
}

8. With the above defined classes we get the model that can persist to database table. However, we still need one more last step, the database table itself. Finally, lets go ahead and define configuration entries that will make Magento see our directory of possible installation, upgrade and data scripts. Edit the app/code/community/Inchoo/DBScript/etc/config.xml by adding the following in between the global tag.

        <resources>
            <inchoo_dbscript_setup>
                <setup>
                    <module>Inchoo_DBScript</module>
                </setup>
            </inchoo_dbscript_setup>
        </resources>   

What this does is that it makes Magento see the folder of our possible installation, upgrade and data script. So where is the folder name her? Easy, tag config > global > resources > inchoo_dbscript_setup name should match the name of the app/code/community/Inchoo/DBScript/sql/inchoo_dbscript_setup folder name.
9. Create the installation file app/code/community/Inchoo/DBScript/sql/inchoo_dbscript_setup/install-3.1.5.6.php with the content like shown below.

<?php
 
$installer = $this;
 
$installer->startSetup();
 
$table = $installer->getConnection()
    ->newTable($installer->getTable('inchoo_dbscript/ticket'))
    ->addColumn('ticket_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'identity'  => true,
        'unsigned'  => true,
        'nullable'  => false,
        'primary'   => true,
        ), 'Id')
    ->addColumn('title', Varien_Db_Ddl_Table::TYPE_VARCHAR, null, array(
        'nullable'  => false,
        ), 'Title')
    ->addColumn('description', Varien_Db_Ddl_Table::TYPE_TEXT, null, array(
        'nullable'  => false,
        ), 'Description');
$installer->getConnection()->createTable($table);
 
$table = $installer->getConnection()
    ->newTable($installer->getTable('inchoo_dbscript/comment'))
    ->addColumn('comment_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'identity'  => true,
        'unsigned'  => true,
        'nullable'  => false,
        'primary'   => true,
        ), 'Id')
    ->addColumn('comment', Varien_Db_Ddl_Table::TYPE_VARCHAR, null, array(
        'nullable'  => false,
        ), 'Comment');
$installer->getConnection()->createTable($table);
 
$installer->endSetup();

10. Open up your Magento shop in browser, refresh any page either on frontend or administration area. Everytime you refresh a page, Magento checks if there is anything that needs to be installed. This check is done by looking into the core_resource database table. If the extension has installation scripts, an entry will be made into the core_resource database table whose version column value matches the value of our config.xml config > modules > Inchoo_DBScript > version tag. Based on that version number and config > global > resources > inchoo_dbscript_setup tag name, Magento will look for install-{version-number}.php file within your extension sql folder. So in our case, it will look for app/code/community/Inchoo/DBScript/sql/inchoo_dbscript_setup/install-3.1.5.6.php file.
Tip: While in the development, you might want to test out your install script several times until you get it right. Once the Magento makes an entry into its core_resource database table for exact a certain version number, it will no longer run installation scripts with the same or lower installation number. What you might want to do in such cases, is to manually delete the entry from core_resource database table in order to test the same install script multiple times.

Install upgrade script

Imagine you got your extension developed and deployed to Magnento Connect. Few days/weeks/months later, you decide to add some new features to it. These features might require few new columns to already existing inchoo_dbscript_ticket and inchoo_dbscript_comment database tables or even completely new database tables. For the sake of simplicity, lets fo an example where we upgrade our extension to new 3.2.0.1 version number adding simply two new columns under inchoo_dbscript_ticket database table.
1. First thing we need to do, is to increase the version number in our config.xml, we do so by changing the value of config > modules > Inchoo_DBScript > version tag from 3.1.5.6 to 3.2.0.1 like shown below.

<config>
    <modules>
        <Inchoo_DBScript>
            <version>3.2.0.1</version>
        </Inchoo_DBScript>
    </modules>
	<!-- the rest of the code -->

2. We need to create the upgrade script file with the following name pattern upgrade-{old-version-number}-{new-version-number}.php under the same folder we have our install script. For our example, go ahead and create the app/code/community/Inchoo/DBScript/sql/inchoo_dbscript_setup/upgrade-3.1.5.6-3.2.0.1.php file with the following content.

<?php
 
$installer = $this;
$connection = $installer->getConnection();
 
$installer->startSetup();
 
$installer->getConnection()
    ->addColumn($installer->getTable('inchoo_dbscript/ticket'),
    'created_at',
    array(
        'type' => Varien_Db_Ddl_Table::TYPE_TIMESTAMP,
        'nullable' => true,
        'default' => null,
        'comment' => 'Created At'
    )
);
 
$installer->endSetup();

Once executed, code shown above will merely add a new column called created_at to already existing inchoo_dbscript_ticket database table.
3. Open up your Magento shop in browser, refresh any page either on frontend or administration area. As noted previously, on each run Magento checks the configurations for version number changes (among other things). If an extension has changed the version number to higher than the one already recorded under core_resource table, than Magento will look for upgrade file matching the “changed to new version” number value, which in this case is upgrade-3.1.5.6-3.2.0.1.php file To confirm that the upgrade-3.1.5.6-3.2.0.1.php file has been executed, just check the core_resource table for updated version number for the inchoo_dbscript_setup code.
Tip: In case your Magento does not fire the upgrade file, try refreshing the system cache.
At this point, one important question raises: What happens if I now take my extension and install it on another Magento installation. Will install script get run, or will Magento run only the upgrade script. Remember, if we move this extension to another Magento, it will see only the 3.2.0.1 version, since this is whats read from config.xml.
We can test that easily on our current Magento installation just by deleting the inchoo_dbscript_setup code from core_resource table and deleting the inchoo_dbscript_ticket and inchoo_dbscript_ticket database tables, then refreshing any page either on frontend or administration area of our shop. What happens, surprisingly, is that Magento runs both the install script and upgrade script, even though the install script number is lower than the one written under the config.xml

Data script

Data scripts do not require any special config.xml entries. Magento will be able to see them, as long as you have defined the config.xml entries for installation script as we previously shown. However, unlike install scripts and install upgrade scripts, data and data upgrade scripts have their own folder within your extension. Here is the full path of the data script for our extension app/code/community/Inchoo/DBScript/data/inchoo_dbscript_setup/data-install-3.1.5.6.php. So basically, data and data upgrade scripts are located under data/inchoo_dbscript_setup folder of your extension where data is a fixed name and inchoo_dbscript_setup matches the resource tag name as with the install scripts.
1. Go ahead and create the app/code/community/Inchoo/DBScript/data/inchoo_dbscript_setup/data-install-3.1.5.6.php file with the following content.

<?php
 
$tickets = array(
    array(
        'title' => 'Broken cart',
        'description' => 'Unable to add items to cart.',
    ),
    array(
        'title' => 'Login issues',
        'description' => 'Cannot login when using IE.',
    ),
);
 
foreach ($tickets as $ticket) {
    Mage::getModel('inchoo_dbscript/ticket')
        ->setData($ticket)
        ->save();
}

Code above is pretty straight forward. We simply define an array of sample data for our Ticket model, which will end up in the inchoo_dbscript_ticket table.
2. Delete any existing entries for inchoo_dbscript_setup code from core_resource table. Delete inchoo_dbscript_ticket and inchoo_dbscript_comment tables.
3. Open up your Magento shop in browser, refresh any page either on frontend or administration area. Magento should now run the install script, data script, install upgrade script.
4. Check the newly created inchoo_dbscript_ticket table, it should now contain our sample data.

Data upgrade script

Data upgrade scripts follow the similar pattern as install upgrade scripts, except the filename is now data-upgrade-{old-version}-{new-version}.php and they are located under the data folder.
1. Go ahead and create the app/code/community/Inchoo/DBScript/data/inchoo_dbscript_setup/data-upgrade-3.1.5.6-3.2.0.1.php file with the following content.

<?php
 
$tickets = Mage::getModel('inchoo_dbscript/ticket')
                ->getCollection();
 
foreach ($tickets as $ticket) {
    $ticket->setCreatedAt(strftime('%Y-%m-%d %H:%M:%S', time()))
           ->save();
}

What our upgrade script does, is that it simply grabs all of the Ticket(s) and sets the created_at column value to current time.
2. Delete any existing entries for inchoo_dbscript_setup code from core_resource table. Delete inchoo_dbscript_ticket and inchoo_dbscript_comment tables.
3. Open up your Magento shop in browser, refresh any page either on frontend or administration area. Magento should now run the install script, data script, install upgrade script.
4. Check the newly created inchoo_dbscript_ticket table, it should now contain our sample data, this time with some real time values under created_at column and not NULL.
There you have it: install, install upgrade, data and data upgrade scripts.
The only thing missing is uninstall scripts. For a glimpse of a little more on this topic, take a look at the app/code/core/Mage/Core/Model/Resource/Setup.php file, more precisely _getModifySqlFiles method. Within this method you will see a switch statement TYPE_DB_INSTALL, TYPE_DATA_INSTALL, TYPE_DB_UPGRADE, TYPE_DATA_UPGRADE, TYPE_DB_ROLLBACK, TYPE_DB_UNINSTALL.
Unfortuantely, TYPE_DB_ROLLBACK, TYPE_DB_UNINSTALL have no implementatons in Magento 1.7.0.2 or earlier so you have no uninstall script feature in Magento. This is pretty unfortunate because responsible developers would surely used this feature to cleanup the non business essential data that their extension might have recorded into database.
Hope this article was useful for you.
Post from inchoo.net