Alter Table in Magento Setup Script Without Using SQL

ALTER TABLE in Magento setup script without using SQL

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

Add an auto_increment column in Magento setup script without using SQL

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".

Non-SQL module setup script (Magento)

This is an appropriate usage - you are working with the database if you are adding or updating CMS. In fact, it's how the initial homepage fixture data is implemented.

Note that in CE >= 1.6 and EE >= 1.11 there is a complete DDL which should be used: ALTER TABLE in Magento setup script without using SQL

magento adding a column in existing table

It is not recommended to add/modify or do changes to any core files . Better you make a new module to add an extra column .

You have to mention correct version for module upgrade in app/code/local/your/module/sql/your_module_setup/upgrade-0.1.2-0.1.3.php file. (This means your upgrade the module version from 0.1.2 to 0.1.3). If your are not using upgrade script, remember to define <resources> in module config.xml and the setup script name is mysql4-install-0.1.0.php

Below is Mysql setup script file - upgrade-0.1.2-0.1.3.php

    <?php
        ini_set('display_errors', '1');

        $installer = $this;
        $installer->startSetup();
        $installer->getConnection()
                 ->addColumn(
                  $installer->getTable('newsletter/subscriber'), //Get the newsletter Table
                  'your_field_name', //New Field Name
             array(
               'type'      => Varien_Db_Ddl_Table::TYPE_TEXT, //Field Type like TYPE_INTEGER ...
               'nullable'  => true,
               'length'    => 255,
               'default'   => 'Some thing default value',
               'comment'   => 'Your field comment'
            )
        );             
        $installer->endSetup();
        ?>

and after that change app/code/local/your/module/etc/config.xml version for example

<config>
    <modules>
        <NameSpace_ModuleName>
            <version>0.1.3</version> <!-- if upgrade script version is 0.1.3 -->
        </NameSpace_ModuleName>
    </modules>
   <global>
     <resources>
        <NameSpace_ModuleName_setup>
            <setup>
                <module>NameSpace_ModuleName</module>
                <class>Mage_Catalog_Model_Resource_Setup</class>
            </setup>
            <connection>
                <use>core_setup</use>
            </connection>
        </NameSpace_ModuleName_setup>
      </resources>
   </global>
</config>

Magento Doesn't Run Sql Setup Script

Thanks for everyone's suggestions.

, was missing after 'select'

$data = array(
    'label' => 'Brands',
    'type' => 'select'
    'input' => 'text',

It should be

$data = array(
        'label' => 'Brands',
        'type' => 'select',
        'input' => 'text',

Magento Module DB Setup Script not running

In Magento module entry stored in code field like below:
yourmodulename_setup

Check for record like check_setup in code field of core_resource table.

So for run install script again you have to find module record in module and remove that record.
It will automatically run the script again.

Magento modify/add columns to model resource

I had the same thought (or similar) a while ago.

ALTER TABLE in Magento setup script without using SQL

Ivan's answer was particularly good and I still refer to it often. It is essential reading.

---

Related Topics