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

sun-flower

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.


30 comments

  1. Thanks for your post,
    i have only one problem (1.9.2) all tables are created and table update also working but adding data not working (data-install-3.1.5.6.php) Do you have any solution for this?

  2. Works well on Magento ver. 1.9.2.0.
    Were problems with install script (had seen nothing in DB), but I found bad spot – it was because of the blank string on top of .xml config file.

    1. Hi Valeriy,
      This script has not been creating tables in database also..what do you mean by blank string in config.xml, plz elaborate ….if you can pictorize it

    2. I have the exact same issue, nothing is being created in the database.

  3. I have no problem to create the new table and insert the data to the table. But when I try the Mage::getModel(…)->getCollection(). I got the following error :Model collection name is not defined ….

    I am very sure my config.xml is right otherwise it will not create the new table and insert the data.
    What is wrong to cause the above error?

  4. Thanks for your article.I want to know What’s different between mysql4-install-3.1.5.6.php and install-3.1.5.6.php?

  5. Nice article.
    But if go ahead and make a package, then after installing it on Magento 1.7.0.2(Ubuntu 14.04) we got broken sql-installation file(install-3.1.5.6.php) which system interprets as directory instead of simply script-file.

    Have you got the same issue?

    1. Oh, seems i found the reason. It’s related to common path long. If summary path string length is too long, old Magento(1.7.0.2) unpack script will corrupt file name. For ex.(in my case),

      /var/www/storeema/public_html/app/code/community/MyCompany/Thenameofdevelopedmodule/sql/mycompany_thenameofdevelopedmodule/install-3.1.5.6.php
      will cause the problem i’ve described above…

  6. Hi, I am big fan of your website. Whenever my developer face any magento development issue. i tell them to visit your website and check your articles.

    Thanks for create good platform for Magento Developers..

  7. Good step by step explanation made it easier to understand how all this installer script works.

    Its good if you provide model level information that i.e. model class Data.php will be accessible via model in Mage::getModel(‘inchoo_dbsetup’) can be accessible without adding /data to it.

    Hope this helps

  8. Thanks for the post!

    If anything to improve comes to mind, it would be adding examples for dropping columns and explaining the under-the-hood functionality for those. Bet it’s somewhat straightforward comparing to addColumn etc. but still, food for thought.

  9. hi Branko Ajzele,
    is there any way to get the resent data inserted into the tables of magento…if there are plz do sugest me…

    thanks,
    Dheeraj

  10. Hi Branko

    I am new to magento
    I have followed the above steps to create table.
    After i code all . i cant see the table which create in backend can you help

  11. Hi there Branko. I am a beginner

    My question for you:

    how configure :
    congfig.xml,

    adminhtml.xml,

    design/adminhtml/default/default/layout/

  12. Hi there Branko! Great post – this is really, really helpful to someone just learning Magento.

    My question for you is about third party extensions. If I wanted to add a column or two to a third party’s extension (already installed on my installation) would it make sense to do an upgrade script right inside the extension? Or is there a way to override a setup folder? Or would I be better off extending the entire thing?

    Thanks! And again – great post!

  13. Thank you a lot for this article. I was able to make update schema script working (Qasim Khan) but for some reason I have problem with data install/update. It is related to model definition.

    Fatal error: Call to a member function setData() on a non-object in C:\_Dev\ecommerce\app\code\local\Inchoo\DBScript\data\inchoo_dbscript_setup\data-install-3.1.5.6.php on line 23

    not sure where is the problem. Please respond if you can.

    Thank you.

  14. The following script not working on magento 1.8.
    I don’t know what I am doing wrong I copy all code as it is. Magento module showing in beckend->advance and it is enable but in core_resource table it is not showing.
    I m using magento 1.8. Please help

  15. Hello Branko Ajzele,

    Thanks for your post,
    I’m new learner in magento,
    i’m puzzled why my sql setup not run,
    After reading your post it help and solve my query.

  16. Hello Branko,
    I am a fan of your blog’s and i have learn lot’s from them. i really want thank for that.
    Today i am customize a theme and and add some modules which i buy from other site. but i seen that my magento instance unable to run sql setup file to create table and attributes. what will be a problem?
    it not showing any error ?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <blockquote cite=""> <code> <del datetime=""> <em> <s> <strike> <strong>. You may use following syntax for source code: <pre><code>$current = "Inchoo";</code></pre>.