Topic 4 – Part 4: Install/Upgrade Scripts (continue)

Have you known much about the DDL class in Magento? My article today is intended to answer all frequent questions of using the DDL class in setup scripts and obviously makes you more understand it.

What is the DDL?

DDL stands for Data Definition Language or Data Description Language. It’s the language to define data, like programming language. The SQL command is a form of DDL.

What is the DDL class in Magento?

• The only DDL class in Magento is Varien_Db_Ddl_Table

• The DDL class consists of a const variable corresponding with types of data (Boolean, smallint, integer) and some keywords in SQL (CASCADE, RESTRICT,…)

• The DDL class includes functions to work with the Table object in the database such as:

• addColumn()
• addForeignKey()
• addIndex()

Where does Magento use the DDL class?

Magento uses the DDL class in setup files to create or update the data tables.

How to use the DDL class in creating and editing Table?

• For example, the command paragraph to create the data table for a static blog is as follows:

$table = $installer->getConnection()
    ->newTable($installer->getTable('cms/block'))
    ->addColumn('block_id', Varien_Db_Ddl_Table::TYPE_SMALLINT, null, array(
        'identity'  => true,
        'nullable'  => false,
        'primary'   => true,
        ), 'Block ID')
    ->addColumn('title', Varien_Db_Ddl_Table::TYPE_TEXT, 255, array(
        'nullable'  => false,
        ), 'Block Title')
    ->addColumn('identifier', Varien_Db_Ddl_Table::TYPE_TEXT, 255, array(
        'nullable'  => false,
        ), 'Block String Identifier')
    ->addColumn('content', Varien_Db_Ddl_Table::TYPE_TEXT, '2M', array(
        ), 'Block Content')
    ->addColumn('creation_time', Varien_Db_Ddl_Table::TYPE_TIMESTAMP, null, array(
        ), 'Block Creation Time')
    ->addColumn('update_time', Varien_Db_Ddl_Table::TYPE_TIMESTAMP, null, array(
        ), 'Block Modification Time')
    ->addColumn('is_active', Varien_Db_Ddl_Table::TYPE_SMALLINT, null, array(
        'nullable'  => false,
        'default'   => '1',
        ), 'Is Block Active')
    ->setComment('CMS Block Table');
$installer->getConnection()->createTable($table);

• Some important functions in the DDL class:

• addColumn($name, $type, $size = null, $options = array(), $comment = null)

1. $name: the name of a field (column)
2. $type: the type of data. For instance: TYPE_BOOLEAN, TYPE_SMALLINT, TYPE_INTEGER…
3. $size: the size of a field (0, 255, 2M,…)
4. $option: identity (true/false), nullable(true/false), primary (true/false), default (value)
5. $comment: add comments to the field

• addForeignKey($fkName, $column, $refTable, $refColumn, $onDelete = null, $onUpdate = null)

1. $fkName: the name of the foreign key
2. $column: the name of the field (column) set into the foreign key
3. $ refTable: the name of the reference table
4. $ refColumn: the name of the column table
5. $onDelete: identify an action needs to be implemented when the data of the reference table is deleted (ACTION_CASCADE, ACTION_SET_NULL, ACTION_NO_ACTION, ACTION_RESTRICT, ACTION_SET_DEFAULT)
6. $onUpdate: : identify an action needs to be implemented when the data of the reference table is updated ( ACTION_CASCADE, ACTION_SET_NULL, ACTION_NO_ACTION, ACTION_RESTRICT, ACTION_SET_DEFAULT)

• addIndex($indexName, $fields, $options = array())

1. $ indexName: the name of index
2. $fields: the name/ array of field which is set into index (array or string)

Our topic ends here. Hope that all the reference we provided in this topic will partly aid you in working with the Magento database. Have a nice week! 😉