Magento Varien_Db_Ddl_Table class

10th March 2016

Since Magento 1.6 was released, Magento supports more database backends than just MySQL. So in order to make our module setup scripts cross-database compatible, Magento offers a DDL (Data Definition Language) Table object. The Magento Varien_Db_Ddl_Table class can be used to add, create or remove columns within setup scripts of modules. Coupled with the Mage_Core_Model_Resource_Setup class’ createTable() method, we can add tables without the need of using SQL queries.

An example of these being used can be found in a number of Magento modules, such as the Mage_Catalog module.

$table = $installer->getConnection()
    ->newTable($installer->getTable('catalog/product'))
    ->addColumn('entity_id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'identity'  => true,
        'unsigned'  => true,
        'nullable'  => false,
        'primary'   => true,
        ), 'Entity ID')
        ....

$installer->getConnection()->createTable($table);

So can use the line below to create tables by using the newTable() method.

$installer->getConnection()->newTable()

The newTable() method takes one parameter, which is composed of the unique pair of nodes used under the model tags in config.xml, and the unique pair of nodes within the pair of entity nodes.

<?xml version="1.0"?>
<config>
    ....
    <global>
        <models>
            <catalog>
                <class>Mage_Catalog_Model</class>
                <resourceModel>catalog_resource</resourceModel>
            </catalog>
            <catalog_resource>
                <class>Mage_Catalog_Model_Resource</class>
                <deprecatedNode>catalog_resource_eav_mysql4</deprecatedNode>
                <entities>
                    <product>
                        <table>catalog_product_entity</table>
                    </product>
                    ....
                </entities>
            </catalog_resource>
            ....
        </models>
        ....
    </global>
</config>

As we can see in the Mage_Catalog‘s config.xml, this is “catalog/product”.

We then come across the addColumn() method, which can take 5 parameters.

• name – The name of the column.
• type – The data type of the column.
• size – The maximum size of the column.
• options – Included whether is column is primary, nullable, unsigned or whether the column has a default value.
• comment – A comment to add against the column.

We can see that the types are used from the Varien_Db_Ddl_Class. All of which can be seen the class itself.

<?php
class Varien_Db_Ddl_Table {
    const TYPE_BOOLEAN          = 'boolean';
    const TYPE_SMALLINT         = 'smallint';
    const TYPE_INTEGER          = 'integer';
    const TYPE_BIGINT           = 'bigint';
    const TYPE_FLOAT            = 'float';
    const TYPE_NUMERIC          = 'numeric';
    const TYPE_DECIMAL          = 'decimal';
    const TYPE_DATE             = 'date';
    const TYPE_TIMESTAMP        = 'timestamp'; // Capable to support date-time from 1970 + auto-triggers in some RDBMS
    const TYPE_DATETIME         = 'datetime'; // Capable to support long date-time before 1970
    const TYPE_TEXT             = 'text';
    const TYPE_BLOB             = 'blob'; // Used for back compatibility, when query param can't use statement options
    const TYPE_VARBINARY        = 'varbinary'; // A real blob, stored as binary inside DB

    // Deprecated column types, support is left only in MySQL adapter.
    const TYPE_TINYINT          = 'tinyint';        // Internally converted to TYPE_SMALLINT
    const TYPE_CHAR             = 'char';           // Internally converted to TYPE_TEXT
    const TYPE_VARCHAR          = 'varchar';        // Internally converted to TYPE_TEXT
    const TYPE_LONGVARCHAR      = 'longvarchar';    // Internally converted to TYPE_TEXT
    const TYPE_CLOB             = 'cblob';          // Internally converted to TYPE_TEXT
    const TYPE_DOUBLE           = 'double';         // Internally converted to TYPE_FLOAT
    const TYPE_REAL             = 'real';           // Internally converted to TYPE_FLOAT
    const TYPE_TIME             = 'time';           // Internally converted to TYPE_TIMESTAMP
    const TYPE_BINARY           = 'binary';         // Internally converted to TYPE_BLOB
    const TYPE_LONGVARBINARY    = 'longvarbinary';  // Internally converted to TYPE_BLOB
    ....
}

As we can see, there are some deprecated types that we should avoid when using them in our setup scripts. In fact, Magento converts the old types within the addColumn() method.

<?php 
class Varien_Db_Ddl_Table {
    ....
    public function addColumn($name, $type, $size = null, $options = array(), $comment = null)
    {  
        ....
        // Convert deprecated types
        switch ($type) {
            case self::TYPE_CHAR:
            case self::TYPE_VARCHAR:
            case self::TYPE_LONGVARCHAR:
            case self::TYPE_CLOB:
                $type = self::TYPE_TEXT;
                break;
            case self::TYPE_TINYINT:
                $type = self::TYPE_SMALLINT;
                break;
            case self::TYPE_DOUBLE:
            case self::TYPE_REAL:
                $type = self::TYPE_FLOAT;
                break;
            case self::TYPE_TIME:
                $type = self::TYPE_TIMESTAMP;
                break;
            case self::TYPE_BINARY:
            case self::TYPE_LONGVARBINARY:
                $type = self::TYPE_BLOB;
                break;
        }
        ....
    }
    ....
}

It’s good practice when creating a table and adding columns, to store this information in a variable such as $table, and then add the table using the createTable() method.

$installer->getConnection()->createTable($table);

Note: This article is based on Magento Community/Open Source version 1.9.