Magento Install and Upgrade Data Scripts Explained

While developing custom modules in Magento, I often have to deal with install and upgrade script issues. Normally, these issues arise because of mistakes in the naming convention, improper version numbers, or incorrect syntax. In this post, we’ll focus specifically on how to write infallible Magento install and upgrade scripts.
A Magento install and upgrade script is a part of module development, thus it’s recommended that you have some basic understanding of module development prior to reading this article.
Whenever you install or create any new module that contains database interaction, you will find an install and upgrade script in that module’s code directory that will run once you hit the URL. Magento’s core modules also follow the same install and upgrade structure. If you want to see some examples, openapp/code/core/Mage/Catalog/sql/ catalog_setup. Here, you will find several install and upgrade scripts with proper naming conventions along with their version numbers.

Install Script

To run the install script, we need to create a custom module. We are not going to create a whole new module as it’s beyond the scope of this article; rather, we assume that we have an already createdcustom module with the package name Sitepoint and the module name Articles which keeps records of all written articles in the database.
For a quick guide let’s define the basic module components right here:

• Blocks: Class instance of frontend templates. Frontend templates directly use class functions.
• Models: Contain business logic same as in the typical MVC pattern.
• Resource Models: Push and Pull data from database tables.
• Controllers: Load layouts blocks when a URL is hit.
• etc: An XML file contains module related configurations.
• Helpers: As the name suggests, classes that contain functions which can be accessible across all modules as a helper, regardless of module scope.
• sql: A file which contains database upgrade and install scripts to create and update the database schema.

Directory structure of module should be same as shown below.

app
 --code
 ---local
 ----Sitepoint
 ------Articles
 --------Block
  --------controllers
   --------etc
    --------Model
     --------sql

Go to app/code/local/Sitepoint/Articles/etc and open a file config.xml. We need to add some configuration related to the install script’s location in this file. In config.xml, under the global tag add the following child tags like so:

<global>
    <models>
        <articles>
            <class>Sitepoint_Articles_Model</class> <!-- Model class files -->
            <resourceModel>articles_mysql4</resourceModel> <!--Resource model -->
        </articles>
        <articles_mysql4>
            <class>Sitepoint_Articles_Model_Mysql4</class>
            <entities>
                <articles>
                    <table>articles</table>  <!-- Db table name  -->
                </articles>
            </entities>
        </articles_mysql4>
    </models>
    <resources>
        <articles_setup>
            <setup>
                <module>Sitepoint_Articles</module>
            </setup>
            <connection>
                <use>core_setup</use>
            </connection>
        </articles_setup>
        <articles_write>
            <connection>
                <use>core_write</use>
            </connection>
        </articles_write>
        <articles_read>
            <connection>
                <use>core_read</use>
            </connection>
        </articles_read>
    </resources>
</global>

There’s a very important tag articles_setup and it’s located under the resources tag, which tells Magento that our database setup files reside under the articles_setup directory.
Go to Articles/sql/articles_setup and create the install script mysql4-install-0.1.0.php

<?php
$installer = $this;
$installer->startSetup();
$installer->run("-- DROP TABLE IF EXISTS {$this->getTable('articles')};
CREATE TABLE {$this->getTable('articles')} (
	  `articles_id` int(11) unsigned NOT NULL auto_increment,
	  `title` varchar(255) NOT NULL default '',
	  `short_desc` text NOT NULL default '',
	  `long_desc` text NOT NULL default '',
	  `status` tinyint(2) NOT NULL default '0',
	  `created_time` datetime NULL,
	  `update_time` datetime NULL,
	  PRIMARY KEY (`articles_id`)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	");
	$installer->endSetup();

To make our script compatible across databases, here is the alternate way using a DDL object:

<?php
$installer = $this;
$installer->startSetup();
$table = $installer->getConnection()->newTable($installer->getTable('articles'))
    ->addColumn('articles_id', Varien_Db_Ddl_Table::TYPE_INTEGER, 11, array(
        'unsigned' => true,
        'nullable' => false,
        'primary' => true,
        'identity' => true,
        ), 'Article ID')
    ->addColumn('title', Varien_Db_Ddl_Table::TYPE_VARCHAR, 255, array(
        'nullable' => false,
        'default' => '',
        ), 'Title')
    ->addColumn('short_desc', Varien_Db_Ddl_Table::TYPE_TEXT, null, array(
        'nullable' => false,
        'default' => '',
        ), 'Short Desc')
    ->addColumn('long_desc', Varien_Db_Ddl_Table::TYPE_TEXT, null, array(
        'nullable' => false,
        'default' => '',
        ), 'Long Desc')
    ->addColumn('status', Varien_Db_Ddl_Table::TYPE_TINYINT, 2, array(
        'nullable' => false,
        'default' => '0',
        ), 'Status')
    ->addColumn('created_time', Varien_Db_Ddl_Table::TYPE_DATE, null, array(
        'nullable' => true,
        'default' => null,
        ), 'Created Date')
    ->addColumn('update_time', Varien_Db_Ddl_Table::TYPE_DATE, null, array(
        'nullable' => true,
        'default' => null,
        ), 'Update Date')
    ->setComment('Articles table');
	$installer->getConnection()->createTable($table);
	$installer->endSetup();

To get the table name, we use $this->getTable('articles') as it will automatically append the table prefix (configured during the Magento installation process) to the table name.
A version number follows the name of our install script. This number is the same as defined in theconfig.xml file.

<modules>
    <Sitepoint_Articles>
        <version>0.1.0</version>    <!-- Version of module -->
    </Sitepoint_Articles>
	</modules>

The version number tells Magento about the latest install script. After setting up the install script, refresh your Magento URL and look at the database. If your install script ran successfully, your table should be created.
Now go to your SQL editor and open the core_resource table in Magento’s database. Here, you can see the entry of your install script with the name articles_setup and version number 0.1.0.
Every time you refresh a URL, Magento checks for any install scripts to run based on the version number in your config file and in the database core_resource table. If the versions do not match, it will look for the appropriate version file to run. Suppose you change the version to 0.1.1 in your config.xml. Magento will find that your core_resource table contains version number 0.1.0 and as you have mentioned a new version, it will look for a script with version number 0.1.1.
If it finds this file inside the articles_setup directory, it will run it and upgrade the version number to 0.1.1 in the core_resource table. This is how an install and upgrade script works. All Magento core modules follow this version system. In fact, the entire Magento upgrade process follows this same procedure.
Note that the install script runs only once – when you create it and refresh your URL. If you want to alter the database schema, you can do it by using the upgrade script. A shortcut is to delete your setup entry from core_resource table and refresh your URL. The script will run again and your new changes will be reflected in the database table.

Upgrade Script

When you want to update your module in terms of new database fields to provide new functionality, you need to alter your schema, change fields datatypes, introduce new columns, and so on.
This is when the upgrade script comes in handy. You can not make any direct changes via an SQL editor by running row queries, because whenever another user installs your module, that new database change will not work in their setup. It’s Magento’s recommendation to use an upgrade script for altering the database schema.
An upgrade script is similar to an install script – the only change is the name and a different version number. Let’s look at an example.
Suppose we want to add one more column and change another column. To do this, we will create an upgrade script named mysql4-upgrade-0.1.0-0.1.1.php under Articles/sql/articles_setup. Add the following code.

<?php
$installer = $this;
$installer->startSetup();
$installer->run("
	ALTER TABLE {$this->getTable('articles')}
	CHANGE COLUMN `long_desc` `long_desc` text NULL,
	ADD COLUMN `sub_title` VARCHAR(45) NOT NULL AFTER `title`;
	");
$installer->endSetup();

Here’s an alternative way of doing this using DDL:

<?php
$installer = $this;
$installer->startSetup();
$installer->getConnection()
    ->changeColumn($installer->getTable('articles'), 'long_desc', 'long_desc', array(
        'type' => Varien_Db_Ddl_Table::TYPE_TEXT,
        'nullable' => true,
    ))
    ->addColumn($installer->getTable('articles'), 'sub_title', array(
        'type' => Varien_Db_Ddl_Table::TYPE_VARCHAR,
        'nullable' => false,
        'comment' => 'Sub title'
    ));
$installer->endSetup();

Now we need to tell Magento that we have a new upgrade script ready. To do this, we need to change the version number in our config.xml file :

<modules>
	<Sitepoint_Articles>
	    <version>0.1.1</version>    <!-- Upgrade Version of module -->
	</Sitepoint_Articles>
</modules>

Once you refresh your URL, Magento will find that your config.xml file contains a higher version than the version stored in the core_resource table. It will look for an upgrade script of the same version and then run it. After refreshing your URL, you should be able to see the update to your database schema.

Conclusion

This is how the Magento install and upgrade scripts work. You can give it a try by creating a new module and running the scripts – everything we’re written here is available on Github. If you have any trouble while developing your install or upgrade scripts, feel free to mention it in the comment section and we’ll discuss it! Feedback appreciated!
Post from www.sitepoint.com