Magento Certified Developer GuideCategory: 4. DatabaseWhich methods are generally available in setup scripts to manipulate database tables and indexes
Manish Prakash Staff asked 5 years ago

The functions which are available in normal/flat table setup scripts are taken from class Mage_Core_Model_Resource_Setup. Few of the most commonly used functions are

This function is used to disable foreign key checks. This function is defined Varien_Db_Adaptor_Pdo_Mysql

public function startSetup()
        $this->raw_query("SET SQL_MODE=''");
        $this->raw_query("SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0");
        $this->raw_query("SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO'");

        return $this;

This mainly re-enables foreign key checks

public function endSetup()
        $this->raw_query("SET SQL_MODE=IFNULL(@OLD_SQL_MODE,'')");
        $this->raw_query("SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS=0, 0, 1)");

        return $this;

This is used to run multiple sql queries directly. They just need to be separated by semi-colon.

Then we have host of options if get the connection object using $this->getConnection()


addColumn($tableName, $columnName, $definition, $schemaName = null)
dropColumn($tableName, $columnName, $schemaName = null)
changeColumn($tableName, $oldColumnName, $newColumnName, $definition, $flushData = false,
        $schemaName = null)

truncate($tableName, $schemaName = null)
truncateTable($tableName, $schemaName = null)

insertForce($table, array $bind)
insertMultiple($table, array $data)
insertArray($table, array $columns, array $data)

newTable($tableName = null, $schemaName = null)
createTemporaryTable(Varien_Db_Ddl_Table $table)
dropTable($tableName, $schemaName = null)
dropTemporaryTable($tableName, $schemaName = null)

addForeignKey($fkName, $tableName, $columnName, $refTableName, $refColumnName,
        $onDelete = Varien_Db_Adapter_Interface::FK_ACTION_CASCADE,
        $onUpdate = Varien_Db_Adapter_Interface::FK_ACTION_CASCADE,
        $purge = false, $schemaName = null, $refSchemaName = null)

addIndex($tableName, $indexName, $fields,
        $indexType = Varien_Db_Adapter_Interface::INDEX_TYPE_INDEX, $schemaName = null)
dropIndex($tableName, $keyName, $schemaName = null)

Next we have many other setup script functions for eav tables.
Details of those can be seen here