问题描述
乔纳森日说
"更新不应该是SQL 命令".我没有遇到过任何 DDL 或 DML 语句不能通过 Magento 的配置执行结构.
"updates SHOULD NOT be in the form of SQL commands". I haven't come across any DDL or DML statments that cannot be executed via Magento's config structures.
(在问题如何将配置更改从开发环境迁移到生产环境?)
我想知道如何最好地以这种方式在表中添加/修改/删除列或索引，但又不依赖于 SQL?甚至有可能吗?
I would like to know how best to add/modify/remove a column or index to/from a table in this manner, but without relying on SQL? Is it even possible?
此外，还有哪些操作只能在 SQL 中完成?
Furthermore, what other actions can only be done in SQL?
推荐答案
您可以在安装脚本中使用此类方法:
You can use such methods within your setup script:
使用
Varien_Db_Ddl_Table类创建新表，可以在其中配置所有字段、键、关系，结合$this->getConnection()->createTable($tableObject)示例:
Use
Varien_Db_Ddl_Tableclass to create new tables, where you can configure all the fields, keys, relations in combination with$this->getConnection()->createTable($tableObject)Example:
/* @var $this Mage_Core_Model_Resource_Setup */
$table = new Varien_Db_Ddl_Table();
$table->setName($this->getTable('module/table'));
$table->addColumn('id', Varien_Db_Ddl_Table::TYPE_INT, 10,
array('unsigned' => true, 'primary' => true));
$table->addColumn('name', 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()方法将新列添加到现有表中.它有这样的参数:$tableName- 需要修改的表名$columnName- 需要添加的列名$definition- 列的定义(INT(10)、DECIMAL(12,4)等)
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)
$fkName- 外键名称，每个数据库应该是唯一的，如果你不指定FK_前缀，它会自动添加$tableName- 添加外键的表名$columnName- 应该引用到另一个表的列名，如果你有复杂的外键，用逗号指定多列$refTableName- 外表名，将被处理$refColumnName- 外部表中的列名$onDelete- 在外部表中删除行的操作.可以是空字符串(什么都不做)，cascade，set null.此字段是可选的，如果未指定，将使用cascade值.$onUpdate外部表中行键更新的操作.可以是空字符串(什么都不做)，cascade，set null.此字段是可选的，如果未指定，将使用cascade值.$purge- 在添加外键后启用行清理的标志(例如，删除未引用的记录)
$fkName- the foreign key name, should be unique per database, if you don't specifyFK_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,cascadevalue will be used.$onUpdateaction 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,cascadevalue 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)
$tableName- 应该添加索引的表名$indexName- 索引名称$fields- 索引中使用的列名$indexType- 索引的类型.可能的值有:index、unique、primary、fulltext.该参数是可选的，所以默认值为index
$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 isindex
$tableName- 需要修改的表名$columnName- 应该删除的列的名称
$tableName- the table name that should be modified$columnName- the name of the column, that should removed
$tableName- 删除外键的表名$fkName- 外键名称
$tableName- the table name for removing a foreign key$fkName- the foreign key name
$tableName- 应该删除索引的表名$keyName- 索引名称
$tableName- the table name where the index should be removed$keyName- the index name
$tableName- 需要修改的表名$columnName- 应该重命名的列的名称$definition- 列的新定义(INT(10)、DECIMAL(12,4)等)
$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)
$tableName- 需要修改的表名$oldColumnName- 列的旧名称，应重命名和修改$newColumnName- 列的新名称$definition- 列的新定义(INT(10)、DECIMAL(12,4)等)
$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)
$tableName- 表名$engine- 新引擎名称(MEMORY、MyISAM、InnoDB等)
$tableName- the table name$engine- new engine name (MEMORY,MyISAM,InnoDB, etc)
您也可以使用
tableColumnExists方法来检查列是否存在.Also you can use
tableColumnExistsmethod to check existence of the column.这不是您可以使用的完整方法列表，以摆脱直接编写 SQL 查询.您可以在
Varien_Db_Adapter_Pdo_Mysql和Zend_Db_Adapter_Abstract类中找到更多信息.It is not the full list of methods that are available for you, to get rid of direct SQL queries writing. You can find more at
Varien_Db_Adapter_Pdo_MysqlandZend_Db_Adapter_Abstractclasses.不要犹豫，查看您将要使用的类定义，您可以为自己找到很多有趣的东西:)
Do not hesitate to look into the class definition which you are going to use, you can find a lot of interesting things for yourself :)
这篇关于Magento 安装脚本中的 ALTER TABLE 不使用 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！