Magento Setup scripts explained with example

Since its highly discouraged from magento to run custom queries from setup script . If you are using custom mysql query from setup script then you might face problem while fetching their values on other place because in case of custom query your table might not be treated as an entity. For example, You can see the problem coming while creating a grid in admin section because while showing values on grid you need to create collection of database values and if your database table is not properly created then collection wouldn’t get created. The other benefit of using newer approach is

Below are the available method that can be used with setup connection ($this->getConnection())
Note: ‘cute’ is the model name
• createTable() method will create new table. It has such parameters:
o $table – it contain table definition

Example of CreateTable in magento setup scripts

$this->startSetup();
$table = $this->getConnection()
->newTable($this->getTable('cute/table1'))
->addColumn('column1', Varien_Db_Ddl_Table::TYPE_VARCHAR, 255, array(
        'nullable' => false,
        'default' => '',
        ), 'Title');
$this->getConnection()->createTable($table);
$this->endSetup();

• addColumn() method adds new column to exiting table. It has such parameters:
o $tableName – the table name that should be modified
o $columnName- the name of the column, that should be added
o $definition – definition of the column (INT(10), DECIMAL(12,4), etc)

Example of addColumn in magento setup script

$this->startSetup();
$this->getConnection()->addColumn($this->getTable('your_table_definition'), 'your column name', "column definition");
$this->endSetup();

//example:
$this->startSetup();
$table = $this->getConnection()->addColumn($this->getTable('cute/table1'),'discountamt',"DECIMAL(5,2)");
$this->endSetup();

• addConstraint() method creates a new constraint foreign key. It has such parameters
o $fkName – the foreing key name, should be unique per database, if you don’t specify FK_ prefix, it will be added automaticaly
o $tableName – the table name for adding a foreign key
o $columnName – the column name that should be refered to another table, if you have complex foreign key, use comma to specify more than one column
o $refTableName – the foreign table name, wich will be handled
o $refColumnName – the column name(s) in the foreign table
o $onDelete – action on row removing in the foreign table. Can be empty string (do nothing), cascade, set null. This field is optional, and if it is not specified, cascade value will be used.
o $onUpdate action on row key updating in the foreign table. Can be empty string (do nothing), cascade, set null. This field is optional, and if it is not specified, cascade value will be used.
o $purge – a flag for enabling cleaning of the rows after foreign key adding (e.g. remove the recodrs that are not referenced)

Example of AddConstraint in magento setup script

$this->startSetup();
$installer = $this;
$installer->getConnection()
    ->addConstraint(
        'FK_ITEMS_RELATION_ITEM',
        $installer->getTable('cute/table1'),
        'column2',
        $installer->getTable('sales_flat_invoice'),
        'entity_id',
        'cascade',
        'cascade'
);
$this->endSetup();

• addKey() method is used for adding of indexes to a table. It has such parameters:
o $tableName – the table name where the index should be added
o $indexName – the index name
o $fields – column name(s) used in the index
o $indexType – type of the index. Possible values are: index, unique, primary, fulltext. This parameter is optional, so the default value is index

Example of addKey in magento setup script

$this->startSetup();
$table = $this->getConnection()->addKey($this->getTable('cute/table1'),'IDX_column1','column1','fulltext');
$this->endSetup();

• dropColumn() method is used for removing of columns from the existing table. It has such parameters:
o $tableName – the table name that should be modified
o $columnName- the name of the column, that should removed

Example of dropColumn in magento setup script

$this->startSetup();
$installer = $this;
$installer->getConnection()
    ->dropColumn($this->getTable('cute/table1'),'column1');
$this->endSetup();

• dropForeignKey() method is used for removing of foreing keys. It has such parameters:
o $tableName – the table name for removing a foreign key
o $fkName – the foreing key name

Example of dropForeignKey in magento setup script

$this->startSetup();
$installer = $this;
$installer->getConnection()
    ->dropForeignKey($this->getTable('cute/table1'),'FK_ITEMS_RELATION_ITEM');
$this->endSetup();

• dropKey() method is used for removing of the table indexes. It has such parameters:
o $tableName – the table name where the index should be removed
o $keyName – the index name

Example of dropKey in magento setup script

$this->startSetup();
$installer = $this;
$installer->getConnection()
    ->dropKey($this->getTable('cute/table1'),'IDX_column1');
$this->endSetup();

• modifyColumn method is used to modify existing column in the table. It has such parameters:
o $tableName – the table name that should be modified
o $columnName- the name of the column, that should be renamed
o $definition – a new definition of the column (INT(10), DECIMAL(12,4), etc)

Example of modifyColumn in magento setup script

/* @var $installer Mage_Core_Model_Resource_Setup */
$installer = $this;
$installer->startSetup();
// Updating existing offertemp and offer tables column
$table = $installer->getConnection()->modifyColumn($installer->getTable('ncr_nrone/offertemp'),'discountamt',"DECIMAL(5,2)");
$installer->endSetup();

• changeColumn method is used to modify and rename existing column in the table. It has such parameters:
o $tableName – the table name that should be modified
o $oldColumnName- the old name of the column, that should be renamed and modified
o $newColumnName- a new name of the column
o $definition – a new definition of the column (INT(10), DECIMAL(12,4), etc)

Example of changeColumn in magento setup script

$this->startSetup();
$table = $this->getConnection()->changeColumn($this->getTable('cute/table1'),'discountamt',"column2",Varien_Db_Ddl_Table::TYPE_INTEGER);
$this->endSetup();

• changeTableEngine method is used to change table engine, from MyISAM to InnoDB for instance. It has such parameters:
o $tableName – the table name
o $engine – new engine name (MEMORY, MyISAM, InnoDB, etc)

Exampld of changeTableEngine in magento setup script

$this->startSetup();
$table = $this->getConnection()->changeTableEngine($this->getTable('cute/table1'),'MyISAM');
$this->endSetup();

The following two tabs change content below.

• Bio
• Latest Posts

Chandra Shekhar

GCP Architect

Chandra Shekhar Pandey is Google certified Cloud engineer, I am Magento2 Trained developer. Having huge experience in designing cloud solution. I have around 12 years of experience with world enterprise IT companies and fortune 500 clients. During my architecture design I am always caring about high availability, fast performance and resilient system. From the programmer background I have huge experience in LAMP stack as well. Throughout my carrier I have worked on Retail, E-Learning, Video Conferencing and social media domain. The motive of creating cutehits was just to share the knowledge/solutions I get to know during my day to day life so that if possible I can help someone for same problems/solutions. CuteHits.com is a really a very effort for sharing knowledge to rest of the world. For any query/suggestion about same you can contact me on below details:- Email: shekharmca2005 at gmail.com Phone: +91-9560201363

Latest posts by Chandra Shekhar (see all)

• Best practices for micro service design - January 23, 2022
• Spring Boot - January 23, 2022
• Java - January 23, 2022