反联合国汪族总司令
问题描述
乔纳森日说
"更新不应该是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_Table class 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 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)
- $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 is index
- $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 tableColumnExists method 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_Mysql and Zend_Db_Adapter_Abstract classes.
不要犹豫，查看您将要使用的类定义，您可以为自己找到很多有趣的东西:)
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 :)
