ALTER TABLE in Magento setup script without using SQL

Question

Jonathon Day says

"updates SHOULD NOT be in the form of SQL commands". I haven't come across any DDL or DML statments that cannot be executed via Magento's config structures.

(In the question How can I migrate configuration changes from development to production environment?)

I would like to know how best to add/modify/remove a column or index to/from a table in this manner, but without relying on SQL? Is it even possible?

Furthermore, what other actions can only be done in SQL?

Answer 1

You can use such methods within your setup script:

• Use Varien_Db_Ddl_Table class to create new tables, where you can configure all the fields, keys, relations in combination with $this->getConnection()->createTable($tableObject) Example:

  /* @var $this Mage_Core_Model_Resource_Setup */
  $table = new Varien_Db_Ddl_Table();
  $table->setName($this->getTable('module/table'));
  $table->addColumn('id', Varien_Db_Ddl_Table::TYPE_INT, 10, 
                    array('unsigned' => true, 'primary' => true));
  
  $table->addColumn('name', Varien_Db_Ddl_Table::TYPE_VARCHAR, 255);
  $table->addIndex('name', 'name');
  $table->setOption('type', 'InnoDB');
  $table->setOption('charset', 'utf8');
  
  $this->getConnection()->createTable($table);
  

• Use setup connection ($this->getConnection()) methods:

  • addColumn() method adds new column to exiting table. It has such parameters:
    • $tableName - the table name that should be modified
    • $columnName- the name of the column, that should be added
    • $definition - definition of the column (INT(10), DECIMAL(12,4), etc)
  • addConstraint() method creates a new constraint foreign key. It has such parameters
    • $fkName - the foreign key name, should be unique per database, if you don't specify FK_ prefix, it will be added automatically
    • $tableName - the table name for adding a foreign key
    • $columnName - the column name that should be referred to another table, if you have complex foreign key, use comma to specify more than one column
    • $refTableName - the foreign table name, which will be handled
    • $refColumnName - the column name(s) in the foreign table
    • $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.
    • $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.
    • $purge - a flag for enabling cleaning of the rows after foreign key adding (e.g. remove the records that are not referenced)
  • addKey() method is used for adding of indexes to a table. It has such parameters:
    • $tableName - the table name where the index should be added
    • $indexName - the index name
    • $fields - column name(s) used in the index
    • $indexType - type of the index. Possible values are: index, unique, primary, fulltext. This parameter is optional, so the default value is index
  • dropColumn() method is used for removing of columns from the existing table. It has such parameters:
    • $tableName - the table name that should be modified
    • $columnName- the name of the column, that should removed
  • dropForeignKey() method is used for removing of foreign keys. It has such parameters:
    • $tableName - the table name for removing a foreign key
    • $fkName - the foreign key name
  • dropKey() method is used for removing of the table indexes. It has such parameters:
    • $tableName - the table name where the index should be removed
    • $keyName - the index name
  • modifyColumn method is used to modify existing column in the table. It has such parameters:
    • $tableName - the table name that should be modified
    • $columnName- the name of the column, that should be renamed
    • $definition - a new definition of the column (INT(10), DECIMAL(12,4), etc)
  • changeColumn method is used to modify and rename existing column in the table. It has such parameters:
    • $tableName - the table name that should be modified
    • $oldColumnName- the old name of the column, that should be renamed and modified
    • $newColumnName- a new name of the column
    • $definition - a new definition of the column (INT(10), DECIMAL(12,4), etc)
  • changeTableEngine method is used to change table engine, from MyISAM to InnoDB for instance. It has such parameters:
    • $tableName - the table name
    • $engine - new engine name (MEMORY, MyISAM, InnoDB, etc)

Also you can use tableColumnExists method to check existence of the column.

It is not the full list of methods that are available for you, to get rid of direct SQL queries writing. You can find more at Varien_Db_Adapter_Pdo_Mysql and Zend_Db_Adapter_Abstract classes.

Do not hesitate to look into the class definition which you are going to use, you can find a lot of interesting things for yourself :)