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?

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

Solution 2

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.

Solution 3

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.

Comments

[Anton S]

I'm huge fan of your articles in knowledge base :) but not all magento (critical and most used) objects can't be extended with EAV as they are flat table structures (like order and quote) and adding variables to those require usage of altering tables even if you do that through provided methods. Rolling back this kind of changes takes extra steps on data integrity and management.

[Alan Storm]

Nice to know those are there!

[Alan Storm]

Anton, that's what "unless you're willing to carefully manage it during upgrades." means

[Ivan Chepurnyi]

@Anton, by the way, orders and quotes have pseudo EAV API in setup scripts. For example, calling of addAttribute method for order entity will result in creating of a new column in order_flat table.

[Anton S]

Sure :) I do magneto extension support for ~2000 clients daily and extending quote/order with new variables is one of most performed tasks/wish in customization requests. Thats why I pointed this out cause the "carefully managed way" is quite common actually. Sure it confuses developers and users especially if they are upgrading from EAV structures that they were used to and were used prior 1.4.1.* to flat models used in latest.

[Anton S]

@Ivan I have to test this out cause somehow old setup scripts are not generating the columns like you mention

[Ivan Chepurnyi]

@Anton Module setup class should be extended from Mage_Sales_Model_Mysql4_Setup for this features

[clockworkgeek]

Thanks Ivan. That is an awesome reference of methods! I had looked in the Mage_* classes and not seen anything like this so was beginning to despair. Hopefully my update scripts will be neater in future.

[Ivan Chepurnyi]

@Anton Welcome, have fun with Magento

[Ivan Chepurnyi]

@clockworkgeek Welcome. By the way, Mage contains only MVC implementation of Magento, API for working with FileSystem, DB, Sockets, different file formats are placed under lib/Varien, and a lot of that classes are extended from Zend Framework ones

[Jonathan Day]

@Alan - Just because the core Mage team has used raw SQL in some of their setup scripts doesn't make it right. There are numerous instances where the Mage team doesn't follow their own processes/structures when available.

[Jonathan Day]

@clockworkgeek - This is a great answer, but you will likely still experience some frustration with these scripts. They do work, but can be finicky. You will get very familiar with editing the core_resource table for your module's data_version to test the scripts! Good luck

[Alan Storm]

@Jonathan: If they do it, it's an implicit guideline. The only guideline that matters is getting it done and being willing to support it. Everything else is frosting.

[Ivan Chepurnyi]

@Jonathan hope Magento 2.0 will be more developer friendly, especially in a way of database upgrades. But of course it just will be extended Zend_Db. Using of Doctrine 2.0 orm would solve the this issue, but it requires to rewrite Magento from scratch :)

[Ivan Chepurnyi]

@Alan The methods presented in my answer was not in first Magento versions, so there are a lot of old code in "Core" and seems no one will refactor it in the nearest future.

[Alan Storm]

@Ivan: Agreed, although I stand my my larger point. "The Right" way to do something is 1. Does it work; 2. Are you willing to support it.

[clockworkgeek]

I just had cause to look this up again, and again the reference was helpful. I also discovered that addColumn doesn't break if the column is already there which is extra handy.

[demonkoryu]

When was Varien_Db_Ddl_Table introduced?

[clockworkgeek]

@demonkoryu I don't see it in 1.3 and do see it in 1.4.

[Enrique]

WARNING! you can't create AUTO_INCREMENT columns with Varien_Db_Ddl_Table (this is probably a bug), look here for details: stackoverflow.com/questions/5341693/…

[Ivan Chepurnyi]

Starting from 1.6.x 'identity' option used for specifying auto-increment fields.

[feeela]

As of Magento 1.7 one can't add an ENUM column.

[balrok]

It seems that the array support in modifyColumn isn't fully implemented. For example you can't do this array('type'=>'varchar') in magento 1.7.0.2 since they seem to have forgotten the entry in $_ddlColumnTypes in Varien_Db_Adapter_Pdo_Mysql . - The workaround is simply using a string: modifyColumn('table', 'col', 'VARCHAR(255)');

[Tyler V.]

It is also important to know how to do this, in addition to Ivan's answer.

[aki]

One little thing i've found (and needed), there isn't an "after" option for positioning a column.