blocks|key|412253|text|任何Magento更新都不应该包含SQL的想法是基于|type|unstyled|depth|inlineStyleRanges|entityRanges|data|412254|412255|Magento+Objects在您的数据库/数据存储层|ordered-list-item|412256|上提供抽象您应该使用抽象来更新Magento，这可确保如果Magento团队更改了对象与数据存储的交互方式，您的更新仍将有效(假设核心团队维护对象方法所隐含的原始“契约”)|412257|412258|因此，问题是ALTER+TABLE语句直接更改数据存储。如果您完全赞同上述两种想法，则永远不应该更改数据存储。(在添加列或索引的情况下，这意味着独占使用EAV模型，使用设置资源来管理更改，并接受Magento的索引)。|offset|length|style|CODE|BOLD|412259|一个很好的通用经验法则是，如果您正在更改或添加一些核心Magento功能(产品、评论等)，请避免直接更改数据库结构，除非您愿意在升级期间仔细管理它。|412260|如果您正在构建新的对象和功能，请使用您想要创建的任何SQL，并通过Setup+Resources更改您的表。如果您查看安装程序/升级文件，您可以看到Magento核心团队自己完成了这项工作。|412261|entityMap^0|0|0|0|0|0|6|B|J|4|0|0|0^^$0|@$1|2|3|4|5|6|7|V|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|W|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|X|8|@]|9|@]|A|$]]|$1|F|3|G|5|E|7|Y|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|10|8|@$K|11|L|12|M|N]|$K|13|L|14|M|O]]|9|@]|A|$]]|$1|P|3|Q|5|6|7|15|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|16|8|@]|9|@]|A|$]]|$1|T|3|-4|5|6|7|17|8|@]|9|@]|A|$]]]|U|$]]

The idea that any Magento updates SHOULD NOT include SQL is based on the idea that 

<ol>
<li>Magento Objects provide abstractions on top of you database/datastore layer</li>
<li>You should use the abstractions to update Magento, which ensures if the Magento team changes how the objects interact with the datastore, your updates will still work (assuming the core team maintains the original "contracts" implied by the Object methods)</li>
</ol>

So, the problem is an <code>ALTER TABLE</code> statement directly changes the datastore. If you subscribe exclusively to the above two ideas, you should never be changing the data store. (which, in the case of adding a column or an index means using EAV models exclusively, using the Setup Resources to manage changes, and accepting Magento's indexing).

A good general rule of thumb is, if you're changing or adding onto some core Magento functionality (Products, Reviews, etc.), stay away from directly changing the database structure unless you're willing to carefully manage it during upgrades. 

If you're building new objects and functionality use whatever SQL you want to create and change your tables via Setup Resources. If you look at the installer/upgrade files you can see that the core Magento team does this themselves.

blocks|key|3464399|text|您可以在设置脚本中使用以下方法：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3464400|3464401|使用Varien_Db_Ddl_Table类创建新的表，可以结合$this->getConnection()->createTable($tableObject)示例配置所有的字段、键、关系：|unordered-list-item|offset|length|style|CODE|3464402|3464403|/*+@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);|3464404|Use安装连接($this->getConnection())方法：|3464405|3464406|-+`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)|code-block|syntax|javascript|3464407|​|3464408|-+`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)|3464409|3464410|-+`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`|3464411|3464412|-+`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|3464413|3464414|-+`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|3464415|3464416|-+`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|3464417|3464418|-+`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)|3464419|3464420|-+`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)|3464421|3464422|-+`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)|3464423|3464424|3464425|您还可以使用tableColumnExists方法来检查列的存在。|3464426|它不是你可以使用的所有方法的完整列表，可以摆脱直接编写SQL查询的麻烦。您可以在Varien_Db_Adapter_Pdo_Mysql和Zend_Db_Adapter_Abstract类中找到更多内容。|3464427|请不要犹豫地查看您将要使用的类定义，您可以自己找到许多有趣的东西:)|3464428|entityMap^0|0|0|2|J|W|1D|0|0|0|8|M|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|0|6|H|0|14|R|1W|O|0|0^^$0|@$1|2|3|4|5|6|7|1T|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|1U|8|@]|9|@]|A|$]]|$1|C|3|D|5|E|7|1V|8|@$F|1W|G|1X|H|I]|$F|1Y|G|1Z|H|I]]|9|@]|A|$]]|$1|J|3|-4|5|6|7|20|8|@]|9|@]|A|$]]|$1|K|3|L|5|6|7|21|8|@]|9|@]|A|$]]|$1|M|3|N|5|E|7|22|8|@$F|23|G|24|H|I]]|9|@]|A|$]]|$1|O|3|-4|5|6|7|25|8|@]|9|@]|A|$]]|$1|P|3|Q|5|R|7|26|8|@]|9|@]|A|$S|T]]|$1|U|3|V|5|6|7|27|8|@]|9|@]|A|$]]|$1|W|3|X|5|R|7|28|8|@]|9|@]|A|$S|T]]|$1|Y|3|V|5|6|7|29|8|@]|9|@]|A|$]]|$1|Z|3|10|5|R|7|2A|8|@]|9|@]|A|$S|T]]|$1|11|3|V|5|6|7|2B|8|@]|9|@]|A|$]]|$1|12|3|13|5|R|7|2C|8|@]|9|@]|A|$S|T]]|$1|14|3|V|5|6|7|2D|8|@]|9|@]|A|$]]|$1|15|3|16|5|R|7|2E|8|@]|9|@]|A|$S|T]]|$1|17|3|V|5|6|7|2F|8|@]|9|@]|A|$]]|$1|18|3|19|5|R|7|2G|8|@]|9|@]|A|$S|T]]|$1|1A|3|V|5|6|7|2H|8|@]|9|@]|A|$]]|$1|1B|3|1C|5|R|7|2I|8|@]|9|@]|A|$S|T]]|$1|1D|3|V|5|6|7|2J|8|@]|9|@]|A|$]]|$1|1E|3|1F|5|R|7|2K|8|@]|9|@]|A|$S|T]]|$1|1G|3|V|5|6|7|2L|8|@]|9|@]|A|$]]|$1|1H|3|1I|5|R|7|2M|8|@]|9|@]|A|$S|T]]|$1|1J|3|V|5|6|7|2N|8|@]|9|@]|A|$]]|$1|1K|3|V|5|6|7|2O|8|@]|9|@]|A|$]]|$1|1L|3|1M|5|6|7|2P|8|@$F|2Q|G|2R|H|I]]|9|@]|A|$]]|$1|1N|3|1O|5|6|7|2S|8|@$F|2T|G|2U|H|I]|$F|2V|G|2W|H|I]]|9|@]|A|$]]|$1|1P|3|1Q|5|6|7|2X|8|@]|9|@]|A|$]]|$1|1R|3|-4|5|6|7|2Y|8|@]|9|@]|A|$]]]|1S|$]]

You can use such methods within your setup script:

<ul>
<li>Use <code>Varien_Db_Ddl_Table</code> class to create new tables, where you can configure all the fields, keys, relations in combination with <code>$this-&gt;getConnection()-&gt;createTable($tableObject)</code>
Example:

<pre><code>/* @var $this Mage_Core_Model_Resource_Setup */
$table = new Varien_Db_Ddl_Table();
$table-&gt;setName($this-&gt;getTable('module/table'));
$table-&gt;addColumn('id', Varien_Db_Ddl_Table::TYPE_INT, 10, 
 array('unsigned' =&gt; true, 'primary' =&gt; true));

$table-&gt;addColumn('name', Varien_Db_Ddl_Table::TYPE_VARCHAR, 255);
$table-&gt;addIndex('name', 'name');
$table-&gt;setOption('type', 'InnoDB');
$table-&gt;setOption('charset', 'utf8');

$this-&gt;getConnection()-&gt;createTable($table);
</code></pre></li>
<li>Use setup connection (<code>$this-&gt;getConnection()</code>) methods:

<ul>
<li><code>addColumn()</code> method adds new column to exiting table. It has such parameters: 

<ul>
<li><code>$tableName</code> - the table name that should be modified</li>
<li><code>$columnName</code>- the name of the column, that should be added</li>
<li><code>$definition</code> - definition of the column (<code>INT(10)</code>, <code>DECIMAL(12,4)</code>, etc)</li>
</ul></li>
<li><code>addConstraint()</code> method creates a new constraint foreign key. It has such parameters 

<ul>
<li><code>$fkName</code> - the foreign key name, should be unique per database, if you don't specify <code>FK_</code> prefix, it will be added automatically</li>
<li><code>$tableName</code> - the table name for adding a foreign key</li>
<li><code>$columnName</code> - the column name that should be referred to another table, if you have complex foreign key, use comma to specify more than one column</li>
<li><code>$refTableName</code> - the foreign table name, which will be handled</li>
<li><code>$refColumnName</code> - the column name(s) in the foreign table</li>
<li><code>$onDelete</code> - action on row removing in the foreign table. Can be empty string (do nothing), <code>cascade</code>, <code>set null</code>. This field is optional, and if it is not specified, <code>cascade</code> value will be used. </li>
<li><code>$onUpdate</code> action on row key updating in the foreign table. Can be empty string (do nothing), <code>cascade</code>, <code>set null</code>. This field is optional, and if it is not specified, <code>cascade</code> value will be used. </li>
<li><code>$purge</code> - a flag for enabling cleaning of the rows after foreign key adding (e.g. remove the records that are not referenced)</li>
</ul></li>
<li><code>addKey()</code> method is used for adding of indexes to a table. It has such parameters:

<ul>
<li><code>$tableName</code> - the table name where the index should be added</li>
<li><code>$indexName</code> - the index name</li>
<li><code>$fields</code> - column name(s) used in the index</li>
<li><code>$indexType</code> - type of the index. Possible values are: <code>index</code>, <code>unique</code>, <code>primary</code>, <code>fulltext</code>. This parameter is optional, so the default value is <code>index</code></li>
</ul></li>
<li><code>dropColumn()</code> method is used for removing of columns from the existing table. It has such parameters:

<ul>
<li><code>$tableName</code> - the table name that should be modified</li>
<li><code>$columnName</code>- the name of the column, that should removed</li>
</ul></li>
<li><code>dropForeignKey()</code> method is used for removing of foreign keys. It has such parameters:

<ul>
<li><code>$tableName</code> - the table name for removing a foreign key</li>
<li><code>$fkName</code> - the foreign key name</li>
</ul></li>
<li><code>dropKey()</code> method is used for removing of the table indexes. It has such parameters:

<ul>
<li><code>$tableName</code> - the table name where the index should be removed</li>
<li><code>$keyName</code> - the index name</li>
</ul></li>
<li><code>modifyColumn</code> method is used to modify existing column in the table. It has such parameters:

<ul>
<li><code>$tableName</code> - the table name that should be modified</li>
<li><code>$columnName</code>- the name of the column, that should be renamed</li>
<li><code>$definition</code> - a new definition of the column (<code>INT(10)</code>, <code>DECIMAL(12,4)</code>, etc)</li>
</ul></li>
<li><code>changeColumn</code> method is used to modify and rename existing column in the table. It has such parameters:

<ul>
<li><code>$tableName</code> - the table name that should be modified</li>
<li><code>$oldColumnName</code>- the old name of the column, that should be renamed and modified</li>
<li><code>$newColumnName</code>- a new name of the column</li>
<li><code>$definition</code> - a new definition of the column (<code>INT(10)</code>, <code>DECIMAL(12,4)</code>, etc)</li>
</ul></li>
<li><code>changeTableEngine</code> method is used to change table engine, from MyISAM to InnoDB for instance. It has such parameters:

<ul>
<li><code>$tableName</code> - the table name</li>
<li><code>$engine</code> - new engine name (<code>MEMORY</code>, <code>MyISAM</code>, <code>InnoDB</code>, etc)</li>
</ul></li>
</ul></li>
</ul>

Also you can use <code>tableColumnExists</code> method to check existence of the column. 

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 <code>Varien_Db_Adapter_Pdo_Mysql</code> and <code>Zend_Db_Adapter_Abstract</code> 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 :)

blocks|key|3464459|text|要使用外键修改表和添加列，我已经在Magento+CE+v1.6.1.0中成功地使用了这一点：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3464460|//+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
++++++++);|code-block|syntax|javascript|3464461|这些都是来自Varien_Db_Adapter_Pdo_Mysql的方法。|offset|length|style|CODE|3464462|entityMap^0|0|0|6|R|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

To alter table and add column with a foreign key, I have used this successfully using Magento CE v1.6.1.0 :

<pre><code>// Alter table to add column
$installer-&gt;getConnection()

 -&gt;addColumn(
 $installer-&gt;getTable('modulekey/model'), 
 'column_name', 
 array(
 'type' =&gt; Varien_Db_Ddl_Table::TYPE_INTEGER,
 'length' =&gt; null,
 'unsigned' =&gt; true,
 'nullable' =&gt; true,
 'comment' =&gt; 'Foreign key'
 )
 );

// Add foreign key constraint
$installer-&gt;getConnection()

 -&gt;addForeignKey(
 $installer-&gt;getFkName( 
 'modulekey/model', 'column_name',
 'modulekey/foreign_model', 'foreign_column_name'
 ),
 $installer-&gt;getTable('modulekey/model'), 
 'column_name',
 $installer-&gt;getTable('modulekey/foreign_model'),
 'foreign_column_name',
 Varien_Db_Ddl_Table::ACTION_SET_NULL, 
 Varien_Db_Ddl_Table::ACTION_SET_NULL
 );
</code></pre>

Those are methods from <code>Varien_Db_Adapter_Pdo_Mysql</code>.

<a href="https://stackoverflow.com/users/336905/jonathan-day">Jonathon Day</a> says

<blockquote>
 "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.
</blockquote>

(In the question <a href="https://stackoverflow.com/q/4208030/471559">How can I migrate configuration changes from development to production environment?</a>)

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?

Furthermore, what other actions can only be done in SQL?

ALTER TABLE in Magento setup script without using SQL

数据存储

数据库

V模型

说“更新不应该是SQL命令的形式”。我还没有遇到任何不能通过Magento的配置结构执行的DDL或DML语句。(在问题中)我想知道如何以这种方式在表中添加/修改/删除列或索引，而不依赖于SQL？这有可能吗？此外，还有哪些操作只能在SQL中完成？

问不使用SQL在Magento设置脚本中更改TABLE
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问不使用SQL在Magento设置脚本中更改TABLEEN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问不使用SQL在Magento设置脚本中更改TABLE
EN