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.
Best Answer
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
You see the TINYINT type being added quite often via
addColumn
which is available throughgetConnection()
in$installer
:Mage/Core/Catalog/sql/catalog_setup/mysql4-upgrade-0.7.72-0.7.73.php
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: