3

I'm trying to create a new table in the Magento database using Magento's *Varien_Db_Ddl_Table*. Using fresh Magento 1.7.0.2 CE install for this.

I'm at a loss for syntax though. I'm trying to create a BOOLEAN field but I will also settle for a TINYINT if needs be. My createTable looks like this:

$table_one = $installer->getConnection()
->newTable($installer->getTable('my_module/table'))
->addColumn('id', Varien_Db_Ddl_Table::TYPE_INTEGER, null, array(
    'unsigned' => true,
    'identity' => true,
    'nullable' => false,
    'primary' => true,
), 'Entity id')
->addColumn('store_id', Varien_Db_Ddl_Table::TYPE_TEXT, 63, array(
    'nullable' => true,
    'default' => null,
), 'Store view id')
->addColumn('is_active', Varien_Db_Ddl_Table::TYPE_TINYINT, 1, array(
    'nullable' => false,
    'default' => 1,
), 'Status')
->addColumn('created_at', Varien_Db_Ddl_Table::TYPE_TIMESTAMP, null, array(
    'nullable' => true,
    'default' => null, 
), 'Creation time')
->addIndex($installer->getIdxName(
    $installer->getTable('my_module/table'),
    array('store_id'),
    Varien_Db_Adapter_Interface::INDEX_TYPE_INDEX
), 
array('store_id'), 
array('type' => Varien_Db_Adapter_Interface::INDEX_TYPE_INDEX)
)
->setComment('My Module Table');
$installer->getConnection()->createTable($table_one);

The field I'm struggling with is is_active. MySQL tries to set as AUTO-INCREMENT and NULL?!?

Can anyone set me straight please or point me to some useful docs? I found these, but no syntax guidelines really :(

Thank you.

2

For the most part, Magento internals handle initial table creation with raw SQL on setup:

app/code/core/Enterprise/Cms/sql/enterprise_cms_setup/mysql4-install-1.6.0.0.0.php

$installer->run("CREATE TABLE IF NOT EXISTS `{$installer->getTable('enterprise_cms/hierarchy_metadata')}` (
  `node_id` INT(10) UNSIGNED NOT NULL,
  `meta_first_last` TINYINT(1) NOT NULL DEFAULT '0',
  `meta_next_previous` TINYINT(1) NOT NULL DEFAULT '0',
  `meta_chapter` TINYINT(1) NOT NULL DEFAULT '0',
  `meta_section` TINYINT(1) NOT NULL DEFAULT '0',
  `pager_visibility` TINYINT(4) UNSIGNED NOT NULL,
  `pager_frame` SMALLINT(6) UNSIGNED NOT NULL,
  `pager_jump` SMALLINT(6) UNSIGNED NOT NULL,
  `menu_visibility` TINYINT(4) UNSIGNED NOT NULL,
  `menu_levels_up` TINYINT(4) UNSIGNED NOT NULL,
  `menu_levels_down` TINYINT(4) UNSIGNED NOT NULL,
  `menu_ordered` TINYINT(4) UNSIGNED NOT NULL,
  `menu_list_type` VARCHAR(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`node_id`),
  CONSTRAINT `FK_ENTERPRISE_CMS_HIERARCHY_METADATA_NODE` FOREIGN KEY (`node_id`) REFERENCES `{$installer->getTable('enterprise_cms/hierarchy_node')}` (`node_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
");

You see the TINYINT type being added quite often via addColumn which is available through getConnection() in $installer:

Mage/Core/Catalog/sql/catalog_setup/mysql4-upgrade-0.7.72-0.7.73.php

$installer->getConnection()->addColumn($installer->getTable('catalog/product_super_attribute_label'),
    'use_default', 'tinyint(1) UNSIGNED DEFAULT 0 AFTER store_id');

Which is usually called on an existing table.

There's nothing wrong with using TINYINT(1) as a boolean. If you're dead set against the SHOW CREATE copy-pasta method of popuplating table schema with $installer->run, avoid the setup woes you're having and run the addColumn later as an update in your module:

mysql4-upgrade-0.1.0-0.1.1:

$installer->getConnection()->addColumn($installer->getTable('yourresource/model'),
    'is_active', 'TINYINT(1) UNSIGNED DEFAULT 0 AFTER store_id');
| improve this answer | |
  • Thanks Phil, the update seems the logical move right now. Thx for the info! – Jongosi Jun 10 '13 at 22:40
7

For future reference, the following syntax will add a TINYINT(1) field - for which BOOLEAN is a MySQL alias:

->addColumn('is_active', Varien_Db_Ddl_Table::TYPE_BOOLEAN, 1, array(
    'nullable' => false,
    'default' => 1,
), 'Status')

And the resulting field in MySQL:

Name, Type, Null, Default
is_active, tinyint(1), No, 1

Hope that helps a future visitor.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.