Magento Install and Upgrade Data Scripts Explained

View: 1384    Dowload: 0   Comment: 0   Post by: daii   Category: Php&mySql   Fields: none

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, open app/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 created custom 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:


    
        
            Sitepoint_Articles_Model      
            articles_mysql4 
        
        
            Sitepoint_Articles_Model_Mysql4
            
                
                    articles

Sitepoint_Articles core_setup core_write core_read

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

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:

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 the config.xml file.


    
        0.1.0    
    
	

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.

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:

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 :


	
	    0.1.1    
	

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!

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.

Posted on 20-11-2015 

Comment:

To comment you must be logged in members.

Files with category

  • SimpleXML is a PHP extension that allows us to easily manipulate and get XML data

    SimpleXML is a PHP extension that allows us to easily manipulate and get XML data

    View: 337    Download: 0   Comment: 0

    Category: Php&mySql     Fields: none

    SimpleXML provides an easy way of getting an element's name, attributes and textual content if you know the XML document's structure or layout. SimpleXML turns an XML document into a data structure you can iterate through like a collection of arrays...

  • How to use the PHP download file script? Class Download PHP

    How to use the PHP download file script? Class Download PHP

    View: 215    Download: 2   Comment: 0

    Category: Php&mySql     Fields: Other

    This is my favorite PHP download script. I’ve used a different more simple method until a client wanted to be able to allow their site visitors to download a large file from a password protected directory. The PHP script works on Apache web servers...

  • Installing OCI8 & PHP 7.2 on RHEL or CentOS 6

    Installing OCI8 & PHP 7.2 on RHEL or CentOS 6

    View: 359    Download: 0   Comment: 0

    Category: Php&mySql     Fields: Other

    You’ll need to stop Apache (web server) using the following command. In any Linux commands I show in this article, it presumes you aren’t logged in as the root user, so you will need to use sudo to issue privileged commands.

  • PHP source code: get content of google search

    PHP source code: get content of google search

    View: 281    Download: 2   Comment: 0

    Category: Php&mySql     Fields: Other

    How to get the results of google search and display on your website. This PHP code can do that. Refer to the following code.

  • PHP as a Scripting Language for C#

    PHP as a Scripting Language for C#

    View: 227    Download: 0   Comment: 0

    Category: Php&mySql     Fields: none

    When creating .NET applications (including desktop and web applications), it may be useful to allow extending the application using some scripting language. The users of the application can write simple scripts to configure the application, modify...

  • How to Picking the Brains of Your Customers with Microsoft’s Text Analytics

    How to Picking the Brains of Your Customers with Microsoft’s Text Analytics

    View: 4306    Download: 0   Comment: 0

    Category: Php&mySql     Fields: Other

    With the explosion of machine learning services in recent years, it has become easier than ever for developers to create “smart apps”. In this article, I’ll introduce you to Microsoft’s offering for providing machine-learning capabilities to apps.

  • How to MySqli Tutorial PHP MySqli Extension

    How to MySqli Tutorial PHP MySqli Extension

    View: 623    Download: 0   Comment: 0

    Category: Php&mySql     Fields: Other

    PHP provides three api to connect mysql Database.

  • Make Laravel Artisan Commands

    Make Laravel Artisan Commands

    View: 592    Download: 0   Comment: 0

    Category: Php&mySql     Fields: Other

    Artisan is the command line tool used in Laravel framework. It offers a bunch of useful command that can help you develop application quickly. Apart from Artisan available commands, you can create your own custom commands to improve your workflow.

 
File suggestion for you
File top downloads
Codetitle - library source code to share, download the file to the community
Copyright © 2018. All rights reserved. codetitle Develope by Vinagon .Ltd