php Magento 安装脚本中的 ALTER TABLE 不使用 SQL

ALTER TABLE in Magento setup script without using SQL

提问by clockworkgeek

Jonathon Daysays

乔纳森日说

"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 命令的形式”。我还没有遇到任何无法通过 Magento 的配置结构执行的 DDL 或 DML 语句。

(In the question How can I migrate configuration changes from development to production environment?)

（在问题如何将配置更改从开发环境迁移到生产环境？）

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?

此外，还有哪些操作只能在 SQL 中完成？

回答by Ivan Chepurnyi

You can use such methods within your setup script:

您可以在安装脚本中使用这些方法：

• 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);
  

• Use setup connection ($this->getConnection()) methods:

  • 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, 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)
  • 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
  • modifyColumnmethod 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)
  • changeColumnmethod 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)
  • changeTableEnginemethod 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)

• 使用Varien_Db_Ddl_Tableclass 创建新表，可以在其中配置所有字段、键、关系，并结合$this->getConnection()->createTable($tableObject)示例：

  /* @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)等）
  • addConstraint()方法创建一个新的约束外键。有这样的参数
    • $fkName- 外键名称，每个数据库应该是唯一的，如果不指定FK_前缀，它将自动添加
    • $tableName- 添加外键的表名
    • $columnName- 应该引用到另一个表的列名，如果您有复杂的外键，请使用逗号指定多列
    • $refTableName- 外部表名，将被处理
    • $refColumnName- 外部表中的列名
    • $onDelete- 在外部表中删除行的操作。可以是空字符串（什么都不做），cascade, set null。此字段是可选的，如果未指定，cascade将使用 value。
    • $onUpdate外部表中行键更新的操作。可以是空字符串（什么都不做），cascade, set null。此字段是可选的，如果未指定，cascade将使用 value。
    • $purge- 在添加外键后启用行清理的标志（例如删除未引用的记录）
  • addKey()方法用于向表添加索引。它有这样的参数：
    • $tableName- 应该添加索引的表名
    • $indexName- 索引名称
    • $fields- 索引中使用的列名
    • $indexType- 索引的类型。可能的值是：index, unique, primary, fulltext。这个参数是可选的，所以默认值为index
  • dropColumn()方法用于从现有表中删除列。它有这样的参数：
    • $tableName- 应该修改的表名
    • $columnName- 列的名称，应该删除
  • dropForeignKey()方法用于删除外键。它有这样的参数：
    • $tableName- 删除外键的表名
    • $fkName- 外键名称
  • dropKey()方法用于删除表索引。它有这样的参数：
    • $tableName- 应删除索引的表名
    • $keyName- 索引名称
  • modifyColumn方法用于修改表中的现有列。它有这样的参数：
    • $tableName- 应该修改的表名
    • $columnName- 列的名称，应该重命名
    • $definition-列的新的定义（INT(10)，DECIMAL(12,4)等）
  • changeColumn方法用于修改和重命名表中的现有列。它有这样的参数：
    • $tableName- 应该修改的表名
    • $oldColumnName- 列的旧名称，应重命名和修改
    • $newColumnName- 列的新名称
    • $definition-列的新的定义（INT(10)，DECIMAL(12,4)等）
  • changeTableEngine方法用于更改表引擎，例如从 MyISAM 更改为 InnoDB。它有这样的参数：
    • $tableName- 表名
    • $engine-新的引擎名称（MEMORY，MyISAM，InnoDB等）

Also you can use tableColumnExistsmethod to check existence of the column.

您也可以使用tableColumnExists方法来检查列的存在。

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_Mysqland Zend_Db_Adapter_Abstractclasses.

它不是可供您使用的完整方法列表，以摆脱直接编写 SQL 查询。您可以在Varien_Db_Adapter_Pdo_Mysql和Zend_Db_Adapter_Abstract课程中找到更多信息。

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 :)

不要犹豫，查看您将要使用的类定义，您可以为自己找到很多有趣的东西:)

回答by Alan Storm

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

任何 Magento 更新都不应该包含 SQL 的想法是基于这样的想法

1. Magento Objects provide abstractions on top of you database/datastore layer

2. 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)

1. Magento 对象在您的数据库/数据存储层之上提供抽象

2. 您应该使用抽象来更新 Magento，这确保如果 Magento 团队更改对象与数据存储交互的方式，您的更新仍然有效（假设核心团队维护对象方法暗示的原始“合同”）

So, the problem is an ALTER TABLEstatement directly changesthe 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).

所以，问题是一个ALTER TABLE语句直接改变了数据存储。如果您只订阅上述两个想法，则永远不应更改数据存储。（在添加列或索引的情况下，意味着只使用 EAV 模型，使用设置资源来管理更改，并接受 Magento 的索引）。

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.

一个好的一般经验法则是，如果您要更改或添加某些核心 Magento 功能（产品、评论等），请避免直接更改数据库结构，除非您愿意在升级期间对其进行仔细管理。

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.

如果您正在构建新的对象和功能，请使用您想要通过设置资源创建和更改表的任何 SQL。如果您查看安装程序/升级文件，您会发现核心 Magento 团队自己执行此操作。

回答by Bouni

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

要更改表并添加带有外键的列，我已经使用 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
        );

Those are methods from Varien_Db_Adapter_Pdo_Mysql.

这些是来自Varien_Db_Adapter_Pdo_Mysql.