Magento – how to upgrade schema to create new table in existing module in magento 1.9

databasemagento-1.9table

I need to create a table having id,customer_id,in_time,out_time,in_coordinates,out_coordinates where customer_id will be primary key.
please tell what are the steps after creating sql file in sql folder.?

my file name is :
mysql4-data-upgrade-0.1.15-0.1.16.php 

$installer = $this;

$installer->startSetup();

$installer->run("
-- DROP TABLE IF EXISTS {$this->getTable('sm_tracker')};
CREATE TABLE {$this->getTable('sm_tracker')} (
  `id` int(11) NOT NULL COMMENT 'Inc Id',
  `customer_id` varchar(30) NOT NULL COMMENT 'Customer Id',
  `punch_in_time` varchar(30) NOT NULL COMMENT 'Punch-In Time ',
  `punch_out_time` varchar(30) NOT NULL COMMENT 'Punch-Out Time',
  `in_coordinates` varchar(30) NOT NULL COMMENT 'Punch-In Coordinates',
  `out_coordinates` varchar(30) NOT NULL COMMENT 'Punch-Out Coordinates',

  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;");

$installer->endSetup(); 

?>

config.xml –> version

 <modules>
    <Test_Manager>
      <version>0.1.16</version>
    </Test_Manager>
  </modules>

and defined : in config 

.....
     <smtracker>
                    <table>sm_tracker</table>
                  </smtracker>
            </entities>

Best Answer

It's pretty simple. You will need to edit config.xml and add few files. First, edit your config.xml file:

In <entities> section add this:

             <entities>
                <unique_name>
                    <table>your_table_name</table>
                </unique_name>
            </entities>

Now, check the version and note of the version number. For example if version is <version>1.0.2</version> then note of 1.0.2.

Then go to your sql setup folder and create a file called upgrade-1.0.2-1.0.3.php. Add all sql codes in this file:

<?php

$installer = $this;

$installer->startSetup();

//id,customer_id,in_time,out_time,in_coordinates,out_coordinates where customer_id

$table = $installer->getConnection()
    ->newTable($installer->getTable('module_name/unique_name')) //this will select your table
    ->addColumn('id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'identity'  => true,
        'nullable'  => false,
        'primary'   => true,
        ), 'Id')
    ->addColumn('customer_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'nullable'  => false,
        ), 'Customer Id')
    ->addColumn('in_time', Varien_Db_Ddl_Table::TYPE_TIMESTAMP, null, array(
        ), 'In Time') //you may want to check TYPE
    ->addColumn('out_time', Varien_Db_Ddl_Table::TYPE_TIMESTAMP, null, array(
        ), 'Out Time') //you may want to check TYPE
    ->addColumn('out_coordinates', Varien_Db_Ddl_Table::TYPE_TEXT, 20, array(
        ), 'Out coordinates') //check type

    ->addColumn('user_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'unsigned'  => true,
        ), 'User Id')
    ->addIndex($installer->getIdxName('module_name/unique_name', array('customer_id')),
        array('customer_id'))
    ->addForeignKey($installer->getFkName('module_name/unique_name', 'customer_id', 'customer/entity', 'entity_id'),
        'customer_id', $installer->getTable('customer/entity'), 'entity_id',
        Varien_Db_Ddl_Table::ACTION_CASCADE, Varien_Db_Ddl_Table::ACTION_CASCADE)
    ->setComment('Your comment');

$installer->getConnection()->createTable($table);

$installer->endSetup();

You also would want to add Model/Uniquename.php and Resource class for your new table.

Now, check the <version> in config.xml and increase by one. For ex. if current version is like this: <version>1.0.2</version> then make it <version>1.0.3</version>

And finally, clear your cache. This should install the table for you.

Backup your site before updating any module. This is the best practise.

Related Topic