不使用SQL在Magento设置脚本中更改TABLE
“更新不应该是SQL命令的形式”。我还没有遇到任何不能通过Magento的配置结构执行的DDL或DML语句。
(在问题How can I migrate configuration changes from development to production environment?中)
我想知道如何以这种方式在表中添加/修改/删除列或索引，而不依赖于SQL？这有可能吗？
此外，还有哪些操作只能在SQL中完成？
回答 3
Stack Overflow用户
发布于 2010-12-01 04:52:32
您可以在设置脚本中使用以下方法：
- 使用
Varien_Db_Ddl_Table类创建新的表，可以结合$this->getConnection()->createTable($tableObject)示例配置所有的字段、键、关系：
/* @var $this Mage_Core_Model_Resource_Setup */ $table = Varien_Db_Ddl_Table()；$table->setName($this->getTable('module/table'))；$ => ->addColumn(‘id’，Varien_Db_Ddl_Table::TYPE_INT，10，/*(‘unsigned’=> true，'primary‘=> true))；$table->addColumn(’Varien_Db_Ddl_Table‘，Varien_Db_Ddl_Table::TYPE_VARCHAR，255)；$table->addIndex('name'，'name')；$table->setOption('type'，'InnoDB')；$table->setOption('charset'，'utf8')；$this->getConnection()->createTable($table);
- Use安装连接(
$this->getConnection())方法：
- `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)
您还可以使用tableColumnExists方法来检查列的存在。
它不是你可以使用的所有方法的完整列表，可以摆脱直接编写SQL查询的麻烦。您可以在Varien_Db_Adapter_Pdo_Mysql和Zend_Db_Adapter_Abstract类中找到更多内容。
请不要犹豫地查看您将要使用的类定义，您可以自己找到许多有趣的东西:)
Stack Overflow用户
发布于 2010-12-01 04:08:02
任何Magento更新都不应该包含SQL的想法是基于
- Magento Objects在您的数据库/数据存储层
- 上提供抽象您应该使用抽象来更新Magento，这可确保如果Magento团队更改了对象与数据存储的交互方式，您的更新仍将有效(假设核心团队维护对象方法所隐含的原始“契约”)
因此，问题是ALTER TABLE语句直接更改数据存储。如果您完全赞同上述两种想法，则永远不应该更改数据存储。(在添加列或索引的情况下，这意味着独占使用EAV模型，使用设置资源来管理更改，并接受Magento的索引)。
一个很好的通用经验法则是，如果您正在更改或添加一些核心Magento功能(产品、评论等)，请避免直接更改数据库结构，除非您愿意在升级期间仔细管理它。
如果您正在构建新的对象和功能，请使用您想要创建的任何SQL，并通过Setup Resources更改您的表。如果您查看安装程序/升级文件，您可以看到Magento核心团队自己完成了这项工作。
Stack Overflow用户
发布于 2013-08-21 17:06:19
要使用外键修改表和添加列，我已经在Magento CE v1.6.1.0中成功地使用了这一点：
// Alter table to add column
$installer->getConnection()
->addColumn(
$installer->getTable('modulekey/model'),
'column_name',
array(
'type' => Varien_Db_Ddl_Table::TYPE_INTEGER,
'length' => null,
'unsigned' => true,
'nullable' => true,
'comment' => 'Foreign key'
)
);
// Add foreign key constraint
$installer->getConnection()
->addForeignKey(
$installer->getFkName(
'modulekey/model', 'column_name',
'modulekey/foreign_model', 'foreign_column_name'
),
$installer->getTable('modulekey/model'),
'column_name',
$installer->getTable('modulekey/foreign_model'),
'foreign_column_name',
Varien_Db_Ddl_Table::ACTION_SET_NULL,
Varien_Db_Ddl_Table::ACTION_SET_NULL
);这些都是来自Varien_Db_Adapter_Pdo_Mysql的方法。
https://stackoverflow.com/questions/4315660
复制相似问题