ALTER TABLE in Magento setup script without using SQL

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?

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 :)

The idea that any Magento updates SHOULD NOT include SQL is based on the idea that

1. Magento Objects provide abstractions on top of you database/datastore layer

2. You should use the abstractions to update Magento, which ensures if the Magento team changes how the objects interact with the datastore, your updates will still work (assuming the core team maintains the original "contracts" implied by the Object methods)

So, the problem is an ALTER TABLE statement directly changes the datastore. If you subscribe exclusively to the above two ideas, you should never be changing the data store. (which, in the case of adding a column or an index means using EAV models exclusively, using the Setup Resources to manage changes, and accepting Magento's indexing).

A good general rule of thumb is, if you're changing or adding onto some core Magento functionality (Products, Reviews, etc.), stay away from directly changing the database structure unless you're willing to carefully manage it during upgrades.

If you're building new objects and functionality use whatever SQL you want to create and change your tables via Setup Resources. If you look at the installer/upgrade files you can see that the core Magento team does this themselves.

To alter table and add column with a foreign key, I have used this successfully using Magento CE v1.6.1.0 :

// Alter table to add column
$installer->getConnection()

        ->addColumn(
            $installer->getTable('modulekey/model'), 
            'column_name',  
            array(
                'type'      => Varien_Db_Ddl_Table::TYPE_INTEGER,
                'length'    => null,
                'unsigned'  => true,
                'nullable'  => true,
                'comment'   => 'Foreign key'
            )
        );

// Add foreign key constraint
$installer->getConnection()

        ->addForeignKey(
            $installer->getFkName( 
                'modulekey/model',  'column_name',
                'modulekey/foreign_model',  'foreign_column_name'
            ),
            $installer->getTable('modulekey/model'), 
            'column_name',
            $installer->getTable('modulekey/foreign_model'),
            'foreign_column_name',
            Varien_Db_Ddl_Table::ACTION_SET_NULL, 
            Varien_Db_Ddl_Table::ACTION_SET_NULL
        );

Those are methods from Varien_Db_Adapter_Pdo_Mysql.