3

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>
6

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.

3

create upgrade file. The name of file will be upgrade-yourpreviousversion-updatedversion.php like upgrade-0.1.0-0.1.1.php

 <?php
$installer = $this;  
$installer->startSetup();
$installer->run("
    DROP TABLE IF EXISTS `test`;
  CREATE TABLE `test` (
  `in_time` varchar(300) DEFAULT NULL,
  `out_time` varchar(300) DEFAULT NULL,
  `in_coordinates` varchar(300) DEFAULT NULL,
  `out_coordinates ` varchar(300) DEFAULT NULL,
  `id` int(11) unsigned NOT NULL,
  `customer_id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`customer_id`)         
) ENGINE=InnoDB DEFAULT CHARSET=latin1;");
$installer->endSetup(); 
  • my file name is : mysql4-data-upgrade-0.1.15-0.1.16.php – supriya mishra Feb 17 '17 at 6:19
  • 0.1.16 must be you current version <version>0.1.16</version> and name will be mysql4-upgrade-0.1.15-0.1.16.php – Qaisar Satti Feb 17 '17 at 6:21
  • see my updated file – supriya mishra Feb 17 '17 at 6:23
  • @supriyamishra you are good to go just disabled the cache – Qaisar Satti Feb 17 '17 at 6:24
  • should i delete file and create new with new upgrade version its not reflecting – supriya mishra Feb 17 '17 at 6:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.