“更新不应该是SQL命令的形式”。我还没有遇到任何不能通过Magento的配置结构执行的DDL或DML语句。
(在问题How can I migrate configuration changes from development to production environment?中)
我想知道如何以这种方式在表中添加/修改/删除列或索引,而不依赖于SQL?这有可能吗?
此外,还有哪些操作只能在SQL中完成?
发布于 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);
$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
类中找到更多内容。
请不要犹豫地查看您将要使用的类定义,您可以自己找到许多有趣的东西:)
发布于 2010-12-01 04:08:02
任何Magento更新都不应该包含SQL的想法是基于
因此,问题是ALTER TABLE
语句直接更改数据存储。如果您完全赞同上述两种想法,则永远不应该更改数据存储。(在添加列或索引的情况下,这意味着独占使用EAV模型,使用设置资源来管理更改,并接受Magento的索引)。
一个很好的通用经验法则是,如果您正在更改或添加一些核心Magento功能(产品、评论等),请避免直接更改数据库结构,除非您愿意在升级期间仔细管理它。
如果您正在构建新的对象和功能,请使用您想要创建的任何SQL,并通过Setup Resources更改您的表。如果您查看安装程序/升级文件,您可以看到Magento核心团队自己完成了这项工作。
发布于 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
复制相似问题