Tell me more ×
Magento Stack Exchange is a question and answer site for users of the Magento e-Commerce platform. It's 100% free, no registration required.
up vote 1 down vote favorite
1

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.

share|improve this question

2 Answers

up vote 1 down vote accepted

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');
share|improve this answer
Thanks Phil, the update seems the logical move right now. Thx for the info! – Jongosi Jun 10 at 22:40
up vote 1 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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