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! 😉

Author

Alex is the CTO & Co-founder of Magestore which has been providing retail solutions for Magento merchants since 2009. He started as a Magento developer just one year after the release of the first version of Magento. With over 10 years experience of working with Magento and clients all over the world, he gained great knowledge on e-commerce development, order management systems, inventory control & retail POS.

4 Comments

  1. Magento Developer Reply

    Hi guys You are doing great job please Don’t stop it. i am mainly concern with Magento Certificate Preparation Material. please continue write on it… thanks for great post

    • Hello,
      Thanks much for your interest in our Magento Certificate tutorials. Certainly, we will keep writing this section. The next article is going to be available soon. You can set your mind at rest about that. Nice day!

  2. Roberto Castillo Reply

    Can you give and example of an Alter Table add Column

    • Hi Roberto,

      Yes, example to add column to table sales/order:
      $installer->getConnection()->addColumn($this->getTable(‘sales/order’), ‘affiliate_credit’, ‘decimal(12,4) default NULL’);

Write A Comment