問題描述
喬納森日說
"更新不應該是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的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！
【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！