magento 数据表升级_Magento安装和升级数据脚本介绍

magento 数据表升级

While developing custom modules in Magento, I often have to deal with install and upgrade script issues. Normally, these issues arise because of mistakes in the naming convention, improper version numbers, or incorrect syntax. In this post, we’ll focus specifically on how to write infallible Magento install and upgrade scripts.

在Magento中开发自定义模块时，我经常不得不处理安装和升级脚本问题。 通常，出现这些问题是由于命名约定错误，版本号不正确或语法不正确。 在这篇文章中，我们将专注于如何编写无懈可击的Magento安装和升级脚本。

A Magento install and upgrade script is a part of module development, thus it’s recommended that you have some basic understanding of module development prior to reading this article.

Magento安装和升级脚本是模块开发的一部分，因此建议您在阅读本文之前对模块开发有一些基本的了解。

Whenever you install or create any new module that contains database interaction, you will find an install and upgrade script in that module’s code directory that will run once you hit the URL. Magento’s core modules also follow the same install and upgrade structure. If you want to see some examples, open app/code/core/Mage/Catalog/sql/ catalog_setup. Here, you will find several install and upgrade scripts with proper naming conventions along with their version numbers.

每当您安装或创建包含数据库交互的任何新模块时，都将在该模块的代码目录中找到一个安装和升级脚本，当您单击URL时，该脚本就会运行。 Magento的核心模块也遵循相同的安装和升级结构。 如果要查看一些示例，请打开app/code/core/Mage/Catalog/sql/ catalog_setup 。 在这里，您将找到几个具有正确命名约定的安装和升级脚本以及它们的版本号。

安装脚本 (Install Script)

To run the install script, we need to create a custom module. We are not going to create a whole new module as it’s beyond the scope of this article; rather, we assume that we have an already created custom module with the package name Sitepoint and the module name Articles which keeps records of all written articles in the database.

要运行安装脚本，我们需要创建一个自定义模块。 我们不会创建一个全新的模块，因为它超出了本文的范围。 相反，我们假设我们已经创建了一个自定义模块 ，该模块的包名称为Sitepoint ，模块名称为Articles ，用于在数据库中保留所有书面文章的记录。

For a quick guide let’s define the basic module components right here:

作为快速指南，让我们在此处定义基本模块组件：

• Blocks: Class instance of frontend templates. Frontend templates directly use class functions.

  块：前端模板的类实例。 前端模板直接使用类函数。

• Models: Contain business logic same as in the typical MVC pattern.

  模型：包含与典型MVC模式相同的业务逻辑。

• Resource Models: Push and Pull data from database tables.

  资源模型：从数据库表中推入和拉出数据。

• Controllers: Load layouts blocks when a URL is hit.

  控制器：点击URL时加载布局块。

• etc: An XML file contains module related configurations.

  等等： XML文件包含与模块相关的配置。

• Helpers: As the name suggests, classes that contain functions which can be accessible across all modules as a helper, regardless of module scope.

  助手：顾名思义，此类包含的函数可以作为助手在所有模块之间访问，而与模块范围无关。

• sql: A file which contains database upgrade and install scripts to create and update the database schema.

  sql：一个文件，其中包含用于创建和更新数据库架构的数据库升级和安装脚本。

Directory structure of module should be same as shown below.

模块的目录结构应如下所示。

app
 --code
 ---local
 ----Sitepoint
 ------Articles
 --------Block
  --------controllers
   --------etc
    --------Model
     --------sql

Go to app/code/local/Sitepoint/Articles/etc and open a file config.xml. We need to add some configuration related to the install script’s location in this file. In config.xml, under the global tag add the following child tags like so:

转到app/code/local/Sitepoint/Articles/etc并打开一个文件config.xml 。 我们需要在此文件中添加一些与安装脚本的位置有关的配置。 在config.xml ，在global标记下添加以下子标记，如下所示：

<global>
    <models>
        <articles>
            <class>Sitepoint_Articles_Model</class> <!-- Model class files -->     
            <resourceModel>articles_mysql4</resourceModel> <!--Resource model -->
        </articles>
        <articles_mysql4>
            <class>Sitepoint_Articles_Model_Mysql4</class>
            <entities>
                <articles>
                    <table>articles</table>  <!-- Db table name  -->
                </articles>
            </entities>
        </articles_mysql4>
    </models>
    <resources>  
        <articles_setup>
            <setup>
                <module>Sitepoint_Articles</module>
            </setup>
            <connection>
                <use>core_setup</use>
            </connection>
        </articles_setup>
        <articles_write>
            <connection>
                <use>core_write</use>
            </connection>
        </articles_write>
        <articles_read>
            <connection>
                <use>core_read</use>
            </connection>
        </articles_read>
    </resources>
</global>

There’s a very important tag articles_setup and it’s located under the resources tag, which tells Magento that our database setup files reside under the articles_setup directory.

有一个非常重要的标记articles_setup ，它位于resources标记下，这告诉Magento我们的数据库设置文件位于articles_setup目录下。

Go to Articles/sql/articles_setup and create the install script mysql4-install-0.1.0.php

转到Articles/sql/articles_setup并创建安装脚本mysql4-install-0.1.0.php

<?php
$installer = $this;
$installer->startSetup();
$installer->run("-- DROP TABLE IF EXISTS {$this->getTable('articles')};
CREATE TABLE {$this->getTable('articles')} (
	  `articles_id` int(11) unsigned NOT NULL auto_increment,
	  `title` varchar(255) NOT NULL default '',
	  `short_desc` text NOT NULL default '',
	  `long_desc` text NOT NULL default '',
	  `status` tinyint(2) NOT NULL default '0',
	  `created_time` datetime NULL,
	  `update_time` datetime NULL,
	  PRIMARY KEY (`articles_id`)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;
	");
	$installer->endSetup();

To make our script compatible across databases, here is the alternate way using a DDL object:

为了使我们的脚本跨数据库兼容，这是使用DDL对象的另一种方法：

<?php
$installer = $this;
$installer->startSetup();
$table = $installer->getConnection()->newTable($installer->getTable('articles'))
    ->addColumn('articles_id', Varien_Db_Ddl_Table::TYPE_INTEGER, 11, array(
        'unsigned' => true,
        'nullable' => false,
        'primary' => true,
        'identity' => true,
        ), 'Article ID')
    ->addColumn('title', Varien_Db_Ddl_Table::TYPE_VARCHAR, 255, array(
        'nullable' => false,
        'default' => '', 
        ), 'Title')
    ->addColumn('short_desc', Varien_Db_Ddl_Table::TYPE_TEXT, null, array(
        'nullable' => false,
        'default' => '',
        ), 'Short Desc')
    ->addColumn('long_desc', Varien_Db_Ddl_Table::TYPE_TEXT, null, array(
        'nullable' => false,
        'default' => '',
        ), 'Long Desc')
    ->addColumn('status', Varien_Db_Ddl_Table::TYPE_TINYINT, 2, array(
        'nullable' => false,
        'default' => '0',
        ), 'Status')
    ->addColumn('created_time', Varien_Db_Ddl_Table::TYPE_DATE, null, array(
        'nullable' => true,
        'default' => null,
        ), 'Created Date')
    ->addColumn('update_time', Varien_Db_Ddl_Table::TYPE_DATE, null, array(
        'nullable' => true,
        'default' => null,
        ), 'Update Date')    
    ->setComment('Articles table');
	$installer->getConnection()->createTable($table);
	$installer->endSetup();

To get the table name, we use $this->getTable('articles') as it will automatically append the table prefix (configured during the Magento installation process) to the table name.

为了获得表名，我们使用$this->getTable('articles')因为它会自动将表前缀(在Magento安装过程中配置$this->getTable('articles')附加到表名上。

A version number follows the name of our install script. This number is the same as defined in the config.xml file.

版本号紧随我们的安装脚本的名称。 此数字与config.xml文件中定义的数字相同。

<modules>
    <Sitepoint_Articles>
        <version>0.1.0</version>    <!-- Version of module -->
    </Sitepoint_Articles>
	</modules>

The version number tells Magento about the latest install script. After setting up the install script, refresh your Magento URL and look at the database. If your install script ran successfully, your table should be created.

版本号告诉Magento有关最新的安装脚本。 设置安装脚本后，刷新您的Magento URL并查看数据库。 如果安装脚本成功运行，则应创建表。

Now go to your SQL editor and open the core_resource table in Magento’s database. Here, you can see the entry of your install script with the name articles_setup and version number 0.1.0.

现在转到您SQL编辑器并在Magento的数据库中打开core_resource表。 在这里，您可以看到名称为articles_setup和版本号为0.1.0的安装脚本条目。

Every time you refresh a URL, Magento checks for any install scripts to run based on the version number in your config file and in the database core_resource table. If the versions do not match, it will look for the appropriate version file to run. Suppose you change the version to 0.1.1 in your config.xml. Magento will find that your core_resource table contains version number 0.1.0 and as you have mentioned a new version, it will look for a script with version number 0.1.1.

每次刷新URL时，Magento都会根据配置文件和数据库core_resource表中的版本号检查是否要运行任何安装脚本。 如果版本不匹配，它将寻找合适的版本文件来运行。 假设您在config.xml中将版本更改为0.1.1。 Magento将发现您的core_resource表包含版本号0.1.0，并且正如您提到的新版本一样，它将查找具有版本号0.1.1的脚本。

If it finds this file inside the articles_setup directory, it will run it and upgrade the version number to 0.1.1 in the core_resource table. This is how an install and upgrade script works. All Magento core modules follow this version system. In fact, the entire Magento upgrade process follows this same procedure.

如果它在articles_setup目录中找到该文件，它将运行该文件并将core_resource表中的版本号升级到0.1.1。 这是安装和升级脚本的工作方式。 所有Magento核心模块均遵循该版本系统。 实际上，整个Magento升级过程都遵循相同的过程。

Note that the install script runs only once – when you create it and refresh your URL. If you want to alter the database schema, you can do it by using the upgrade script. A shortcut is to delete your setup entry from core_resource table and refresh your URL. The script will run again and your new changes will be reflected in the database table.

请注意，安装脚本仅运行一次-在您创建安装脚本并刷新URL时。 如果要更改数据库架构，可以使用升级脚本来完成。 快捷方式是从core_resource表中删除设置条目并刷新URL。 该脚本将再次运行，您的新更改将反映在数据库表中。

升级脚本 (Upgrade Script)

When you want to update your module in terms of new database fields to provide new functionality, you need to alter your schema, change fields datatypes, introduce new columns, and so on.

当您要根据新的数据库字段更新模块以提供新的功能时，您需要更改架构，更改字段数据类型，引入新的列，等等。

This is when the upgrade script comes in handy. You can not make any direct changes via an SQL editor by running row queries, because whenever another user installs your module, that new database change will not work in their setup. It’s Magento’s recommendation to use an upgrade script for altering the database schema.

这是升级脚本派上用场的时候。 您无法通过运行行查询通过SQL编辑器进行任何直接更改，因为每当其他用户安装您的模块时，新的数据库更改将无法在其设置中使用。 Magento建议使用升级脚本来更改数据库架构。

An upgrade script is similar to an install script – the only change is the name and a different version number. Let’s look at an example.

升级脚本类似于安装脚本–唯一的变化是名称和不同的版本号。 让我们来看一个例子。

Suppose we want to add one more column and change another column. To do this, we will create an upgrade script named mysql4-upgrade-0.1.0-0.1.1.php under Articles/sql/articles_setup. Add the following code.

假设我们要再增加一列并更改另一列。 为此，我们将在Articles/sql/articles_setup下创建一个名为mysql4-upgrade-0.1.0-0.1.1.php的升级脚本。 添加以下代码。

<?php
$installer = $this;
$installer->startSetup();
$installer->run("
	ALTER TABLE {$this->getTable('articles')}
	CHANGE COLUMN `long_desc` `long_desc` text NULL,
	ADD COLUMN `sub_title` VARCHAR(45) NOT NULL AFTER `title`;
	");
$installer->endSetup();

Here’s an alternative way of doing this using DDL:

这是使用DDL进行此操作的另一种方法：

<?php 
$installer = $this;
$installer->startSetup();
$installer->getConnection()
    ->changeColumn($installer->getTable('articles'), 'long_desc', 'long_desc', array(
        'type' => Varien_Db_Ddl_Table::TYPE_TEXT,
        'nullable' => true,
        
    ))
    ->addColumn($installer->getTable('articles'), 'sub_title', array(
        'type' => Varien_Db_Ddl_Table::TYPE_VARCHAR,
        'nullable' => false,
        'comment' => 'Sub title'
    ));
$installer->endSetup();

Now we need to tell Magento that we have a new upgrade script ready. To do this, we need to change the version number in our config.xml file :

现在我们需要告诉Magento我们已经准备好一个新的升级脚本。 为此，我们需要在config.xml文件中更改版本号：

<modules>
	<Sitepoint_Articles>
	    <version>0.1.1</version>    <!-- Upgrade Version of module -->
	</Sitepoint_Articles>
</modules>

Once you refresh your URL, Magento will find that your config.xml file contains a higher version than the version stored in the core_resource table. It will look for an upgrade script of the same version and then run it. After refreshing your URL, you should be able to see the update to your database schema.

刷新URL后，Magento将发现config.xml文件包含的版本比core_resource表中存储的版本更高。 它将查找相同版本的升级脚本，然后运行它。 刷新URL后，您应该能够看到数据库架构的更新。

结论 (Conclusion)

This is how the Magento install and upgrade scripts work. You can give it a try by creating a new module and running the scripts – everything we’re written here is available on Github. If you have any trouble while developing your install or upgrade scripts, feel free to mention it in the comment section and we’ll discuss it! Feedback appreciated!

这是Magento安装和升级脚本的工作方式。 您可以尝试通过创建一个新模块并运行脚本来进行尝试-我们在此处编写的所有内容都可以在Github上找到 。 如果您在开发安装或升级脚本时遇到任何麻烦，请在评论部分中随意提及，我们将进行讨论！ 反馈表示赞赏！

翻译自: https://www.sitepoint.com/magento-install-upgrade-data-scripts-explained/

magento 数据表升级