Add an auto_increment column in Magento setup script without using SQL

Previously I asked how to ALTER TABLE in Magento setup script without using SQL. There, Ivan gave an excellent answer which I still refer to even now.

However I have yet to discover how to use Varien_Db_Ddl_Table::addColumn() to specify an auto_increment column. I think it has something to do with an option called identity but so far have had no luck.

Is this even possible or is that functionality incomplete?

2 thoughts on “Add an auto_increment column in Magento setup script without using SQL”

  1. One can create an autoincrement column like that (at least since Magento 1.6, maybe even earlier):

    /** @var $table Varien_Db_Ddl_Table */
    $table->addColumn( 'id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
        'auto_increment' => true,
        'unsigned' => true,
        'nullable' => false,
        'primary' => true,
    ), 'ID' );
    

    Instead of “auto_increment”, one may also use the keyword “identity”.

  2. I think that’s something that hasn’t been implemented yet.

    If you look at the source to addColumn, you can see it looks for a identity/auto_increment option and sets an IDENTITY attribute on the internal column representation.

    #File: lib/Varien/Db/Ddl/Table.php
    if (!empty($options['identity']) || !empty($options['auto_increment'])) {
        $identity = true;
    }
    
    $upperName = strtoupper($name);
    $this->_columns[$upperName] = array(
        'COLUMN_NAME'       => $name,
        'COLUMN_TYPE'       => $type,
        'COLUMN_POSITION'   => $position,
        'DATA_TYPE'         => $type,
        'DEFAULT'           => $default,
        'NULLABLE'          => $nullable,
        'LENGTH'            => $length,
        'SCALE'             => $scale,
        'PRECISION'         => $precision,
        'UNSIGNED'          => $unsigned,
        'PRIMARY'           => $primary,
        'PRIMARY_POSITION'  => $primaryPosition,
        'IDENTITY'          => $identity
    );
    

    However, if you look at the createTable method on the connection object

    #File: lib/Varien/Db/Adapter/Pdo/Mysql.php
    public function createTable(Varien_Db_Ddl_Table $table)
    {
        $sqlFragment    = array_merge(
            $this->_getColumnsDefinition($table),
            $this->_getIndexesDefinition($table),
            $this->_getForeignKeysDefinition($table)
        );
        $tableOptions   = $this->_getOptionsDefination($table);
    
        $sql = sprintf("CREATE TABLE %s (n%sn) %s",
            $this->quoteIdentifier($table->getName()),
            implode(",n", $sqlFragment),
            implode(" ", $tableOptions));
    
        return $this->query($sql);
    }
    

    you can see _getColumnsDefinition, _getIndexesDefinition, and _getForeignKeysDefinition are used to create a CREATE SQL fragment. None of these methods make any reference to identity or auto_increment, nor do they appear to generate any sql that would create an auto increment.

    The only possible candidates in this class are

    /**
     * Autoincrement for bind value
     *
     * @var int
     */
    protected $_bindIncrement       = 0;
    

    which is used to control the increment number for a PDO bound parameter (nothing to do with auto_increment).

    There’s also a mention of auto_increment here

    protected function _getOptionsDefination(Varien_Db_Ddl_Table $table)
    {
        $definition = array();
        $tableProps = array(
            'type'              => 'ENGINE=%s',
            'checksum'          => 'CHECKSUM=%d',
            'auto_increment'    => 'AUTO_INCREMENT=%d',
            'avg_row_length'    => 'AVG_ROW_LENGTH=%d',
            'comment'           => 'COMMENT='%s'',
            'max_rows'          => 'MAX_ROWS=%d',
            'min_rows'          => 'MIN_ROWS=%d',
            'delay_key_write'   => 'DELAY_KEY_WRITE=%d',
            'row_format'        => 'row_format=%s',
            'charset'           => 'charset=%s',
            'collate'           => 'COLLATE=%s'
        );
        foreach ($tableProps as $key => $mask) {
            $v = $table->getOption($key);
            if (!is_null($v)) {
                $definition[] = sprintf($mask, $v);
            }
        }
    
        return $definition;
    }
    

    but this is used to process options set on the table. This auto_increment controls the table AUTO_INCREMENT options, which can be used to control which integer an AUTO_INCREMENT starts at.

Leave a Reply

Your email address will not be published. Required fields are marked *