Useful Installer Snippets

To perform database operations from within an installer script, we can get the connection object. Connection objects provide functionality to modify tables, columns, indexes. From model resources, connections can be retrieved using getReadConnection() and _getWriteAdapter(), which is a protected method. Connection objects are instances of the Magento_Db_Adapter_Pdo_Mysql which extends Magento_Db_Adapter_Pdo_Mysql.

All of the following assume that the variable $installer has been assigned as $this from the installer class.

Defining an Installer

<global>
    <resources>
        <foo_bar_setup> // Individual namespace for a resource
            <setup>      // Setup information
                <class>Mage_Core_Model_Resource_Setup</class> // Class to run from within
                <module>Foo_Bar</module>
            </setup>
        </foo_bar_setup>
    </resources>
</global>

At a first glance it appears odd to have to include the module name in the setup node, however because all XML is merged into one big file, Magento has no way of knowing who the originating module of the setup declaration was.

Getting a table’s name

<?php
$installer->getTable('wishlist/wishlist');

This calls the Mage::getSingleton(‘core/resource’)->getTableName($tableName) and caches the result for future calls.

Generating Index Names

Although any name could be used as an index name, Magento includes a handy method to automatically generate index names to ensure there are no clashes between them.

<?php
$installer->getIdxName($tableName, $fields, $indexType = '');

– Table name can be retrieved with the installer’s getTableName() method.
$fields is an array which contains all of the fields the index is for.
$indexType can be one of the following:

<?php
Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE
Varien_Db_Adapter_Interface::INDEX_TYPE_PRIMARY
Varien_Db_Adapter_Interface::INDEX_TYPE_INDEX
Varien_Db_Adapter_Interface::INDEX_TYPE_FULLTEXT

Adding an index to a table

<?php
$installer->getConnection()->addIndex($installer->getTable('wishlist/wishlist'),
    $installer->getIdxName($installer->getTable('wishlist/wishlist'),
        array('customer_id'),
        Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE
    ),
    array('customer_id'),
    Varien_Db_Adapter_Interface::INDEX_TYPE_UNIQUE
);

addIndex() accepts three parameters; the first is the index name where we use getIdxName() and pass in the table name, the fields, and the index type. The second parameter an array of columns for the index, and the third is the type of index we’re creating.

Checking a Table Exists

<?php
$installer->getConnection()->isTableExists($installer->getTable('wishlist/wishlist'));

Converting a Date to MySQL Format

<?php
$installer->getConnection()->convertDate($date);
$installer->getConnection()->convertDateTime($dateTime);

Running SQL

$installer->run('DROP table `{$installer->getTable('wishlist/wishlist')}`');

This aliases the connection’s multiQuery method in Varien_Db_Adapter_Pdo_Mysql.
Note: $connection->query() could also be used.

Generating Foreign Key Names

Just like indexes, Magento provides a useful method for generating foreign key names:

<?php
$installer->getFkName($priTableName, $priColumnName, $refTableName, $refColumnName) 

Renaming a Column

$installer->run("ALTER TABLE table_name CHANGE `column_name` `new_column_name` VARCHAR(255);

Adding a Foreign Key When Creating A Table

$table->addForeignKey($installer->getFkName('catalogsearch/result', 'product_id', 'catalog/product', 'entity_id'),
        $installer->getTable('catalogsearch/result'), 'product_id',
        Varien_Db_Ddl_Table::ACTION_CASCADE, Varien_Db_Ddl_Table::ACTION_CASCADE)

– Would create a from the catalogsearch/result table to the catalog/product table from the column product_id to entity_id

Adding a Foreign Key to an Existing Table

<?php
$installer->getConnection()->addForeignKey(
    $installer->getFkName('catalogsearch/result', 'product_id', 'catalog/product', 'entity_id'),
    $installer->getTable('catalogsearch/result'),
    'product_id',
    $installer->getTable('catalog/product'),
    'entity_id'
);

– The first parameter is the foreign key name, which we generate using Magento’s getFkName() method.
– The second and third parameters are the primary table and column.
– The fourth and fifth parameters are the reference table and column.
– The sixth parameter is the database action to perform when the primary column is deleted.
– The seventh parameter is the database action to perform when the primary column is updated.

Possible options for the onDelete and onUpdate parameters are:

<?php
Varien_Db_Adapter_Interface::FK_ACTION_CASCADE
Varien_Db_Adapter_Interface::FK_ACTION_SET_NULL
Varien_Db_Adapter_Interface::FK_ACTION_NO_ACTION
Varien_Db_Adapter_Interface::FK_ACTION_RESTRICT
Varien_Db_Adapter_Interface::FK_ACTION_SET_DEFAULT

Removing a Foreign Key

As far as I’m aware, Magento doesn’t offer a utility method for removing foreign keys. Instead, we can use the run command and build the foreign key’s name with getFkName().

<?php
$wishlistTable = $installer->getTable('wishlist/wishlist');
$fkName = $installer->getFkName('wishlist/wishlist', 'customer_id', 'customer/entity', 'entity_id');

$installer->run("ALTER TABLE {$wishlistTable} DROP foreign key {$fkName}", array());

Adding a Column to an Existing Table

$this->getConnection()->addColumn(
	$this->getTable('wishlist/wishlist'),
	'cookie_id',
	array(
		'type' => Varien_Db_Ddl_Table::TYPE_TEXT,
		'length' => 255 // Will be created as VARCHAR
		'nullable' => true,
		'default' => null,
		'comment' => 'Created At’ // A comment must be provided
   )
);

– The first column is the table name and the second is the name of the column we’re adding. The third is an array of attributes for the new column; a list of types can be ascertained from the Varien_Db_Ddl_Table class.
Note: To create a column with a type of varchar, use TYPE_TEXT with a length of 255. TYPE_VARCHAR, has been deprecated and the constant is only kept in for the MySQL adapter.

Column Types

<?php
const TYPE_BOOLEAN          = 'boolean';
const TYPE_SMALLINT         = 'smallint';
const TYPE_INTEGER          = 'integer';
const TYPE_BIGINT           = 'bigint';
const TYPE_FLOAT            = 'float';
const TYPE_NUMERIC          = 'numeric';
const TYPE_DECIMAL          = 'decimal';
const TYPE_DATE             = 'date';
const TYPE_TIMESTAMP        = 'timestamp'; // Capable to support date-time from 1970 + auto-triggers in some RDBMS
const TYPE_DATETIME         = 'datetime'; // Capable to support long date-time before 1970
const TYPE_TEXT             = 'text';
const TYPE_BLOB             = 'blob'; // Used for back compatibility, when query param can't use statement options
const TYPE_VARBINARY        = 'varbinary'; // A real blob, stored as binary inside DB