what is magento sql/setup?

While you are writing a new module or customizing existing ones’ you may need to do some tweaking in your db. Magento gives you facility to write sql insaltaller/updater which will run exactly once in its lifetime , do the tweaking for you need and then instantiate you module. In case of complete new module you have to write installer sql and in case of customizing module updater sql.

How does module installer works ?

At the time of instantiating magento looks all Xml files inside the forder /app/etc/module/ to get primiry information about it’s all active module. Then for each module it read the module’s etc/config.xml for is there any sql setup handler available or not. If any sql setup handler declared in config.xml, it looks for the current version number of the module in db core_resource table. The core_resource table contains list of setup handler’s and their corresponding version number.

If no sql handler is listed in the core_resource table then magento runs the installer, if version number in config.xml is greater then the version number in the core_resource table, magento runs the proper updater sql file. For example core_resource table version number is 0.1.0 and in your config.xml you give version number 0.2.0, then magento run the mysql4-upgrade-0.1.0-0.2.0.php.

Writing installer/updater php

Writing an installer/updater is very simple. Get the installer instance, startup installation, run the desired query , and then stop the setup process. For you better understanding I am giving a simple example :-

i)Declare you module and its code pool in
/app/etc/modules/mymodulename.xml

<?xml version="1.0"?>
<config>
  <modules>
    <Mypackagename_Mymodulename>
      <active>true</active>
        <codePool>local</codePool>
    </Mypackagename_Mymodulename>
  </modules>
</config>

ii)Define the setup handler in
/app/code/local/Mypackagename/Mymodulename/etc/config.xml

<?xml version="1.0"?>
<config>

  <modules>
    <Mypackagename_Mymodulename>
      <version>0.1.0</version>
    </Mypackagename_Mymodulename>
  </modules>

  <global>
    <resources>

      <!-- Declaring module setup handler -->
      <!-- [start] -->
      <mymodulename_setup>
        <setup>
          <module>Mypackagename_Mymodulename</module>
        </setup>
        <connection>
          <use>core_setup</use>
        </connection>
      </mymodulename_setup>
      <!-- [End] -->

      <mymodulename_write>
        <connection>
          <use>core_write</use>
        </connection>
      </mymodulename_write>

      <mymodulename_read>
        <connection>
          <use>core_read</use>
        </connection>
      </mymodulename_read>

    </resources>
  </global>
</config>

iii)Write the installer php
Mymodulename/sql/mymodulename_setup/mysql4-install-0.1.0.php

<?php

  $installer = $this;

  $installer->startSetup();

  $installer->run("

    DROP TABLE IF EXISTS {$this->getTable('securityquestion')};

    CREATE TABLE {$this->getTable('securityquestion')} (
      `securityquestion_id` int(11) unsigned NOT NULL auto_increment,
      `title` varchar(255) NOT NULL default '',
      `status` smallint(6) NOT NULL default '0',
      `created_time` datetime NULL,
      `update_time` datetime NULL,
      PRIMARY KEY (`securityquestion_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

    INSERT INTO {$this->getTable('securityquestion')}(
    `securityquestion_id` ,
    `title` ,
    `status`
    )
    VALUES ('1', 'Mother\'s maiden name', '1') ,
    ('2', 'Father\'s middle name', '1') ,
    ('3', 'My first car', '1') ,
    ('4', 'High School name', '1');

  ");

  $installer->endSetup();

Ok, we are done !!! Please leave your comments here :-) .

Advertisement