WordPress:Creating Tables with Plugins

目錄

• 1 Create Database Tables
• 2 創(chuàng)建數(shù)據(jù)庫表格
  • 2.1 Database Table Prefix
  • 2.2 數(shù)據(jù)庫表格前綴
  • 2.3 Is the Table Already There?
  • 2.4 表格已經(jīng)存在嗎?
  • 2.5 Creating or Updating the Table
  • 2.6 創(chuàng)建或者更新表格
  • 2.7 Adding Initial Data
  • 2.8 添加初始數(shù)據(jù)
  • 2.9 A Version Option
  • 2.10 版本選項
  • 2.11 The Whole Function
  • 2.12 整個函數(shù)
• 3 Calling the function
• 4 調(diào)用函數(shù)
• 5 Adding an Upgrade Function
• 6 添加升級函數(shù)
• 7 Resources
• 8 資源

If you are writing a plugin for WordPress, you will almost certainly find that you need to store some information in the WordPress database. There are two types of information you could store:

如果你正在為WordPress編寫一個插件，你肯定會發(fā)現(xiàn)你需要向WordPress數(shù)據(jù)庫儲存一些信息。你可以儲存兩種類型的信息：

• Setup information -- user choices that are entered when the user first sets up your plugin, and don't tend to grow much beyond that (for example, in a tag-related plugin, the user's choices regarding the format of the tag cloud in the sidebar). Setup information will generally be stored using the WordPress options mechanism.

• 設置 信息 –用戶首次設置你的插件的時候，會輸入用戶選擇，而且選擇不會增加（例如，在標簽相關的插件中，用戶對標簽cloud形式相關的選擇出現(xiàn)在邊欄中）。設置信息，一般是使用WordPress 選項 機制創(chuàng)建的。

• Data -- information that is added as the user continues to use your plugin, which is generally expanded information related to posts, categories, uploads, and other WordPress components (for example, in a tag-related plugin, the tag names and correspondence between tags and articles). Data will generally be stored in a separate MySQL table, which will have to be created.

• 數(shù)據(jù) –用戶繼續(xù)使用你的插件，就會增加有關文章，類別，上傳內(nèi)容，和其它的WordPress成分方面的信息（例如，在標簽相關的插件中，有標簽名，和標簽和文章之間的相關信息）。數(shù)據(jù)一般儲存在單獨的MySQL表格中，這個表格需要創(chuàng)建。

This article describes how to have your plugin automatically create a MySQL table to store its data. Note that as an alternative to following the steps here, you could have the plugin user run an install script when they install your plugin. Another approach would be to have the user execute an SQL query on their own, using something like WordPress:phpMyAdmin. But neither of those options is very satisfactory, since a user could easily forget to run the install script or screw up the query (and they might not have phpMyAdmin available).

這篇文章描述了怎樣使你的插件自動創(chuàng)建MySQL表格儲存數(shù)據(jù)。注意，還可以使用另一種方法，遵循下面的步驟，用戶安裝你的插件的時候，你可以讓用戶運行安裝腳本。另一種方法是使用戶使用，如phpMyAdmin自己執(zhí)行SQL查詢。但是這兩種方法都不能讓人感到很滿意，因此用戶容易忘記運行安裝腳本或者會混亂查詢（而且用戶可能沒有phpMyAdmin）。

So, it is recommended that you follow the steps below to have your plugin automatically create its database tables:

因此，建議你遵循下面的步驟，使得插件自動地創(chuàng)建數(shù)據(jù)庫表格：

1. Write a PHP function that creates the table.

1. 編寫創(chuàng)建表格的PHP函數(shù)。

1. Ensure that WordPress calls the function when the plugin is activated.

1. 確定插件激活的時候，WordPress調(diào)用了函數(shù)。

1. Create an upgrade function, if a new version of your plugin needs to have a different table structure.

1. 如果你的插件的新版本需要不同的表格結構，創(chuàng)建升級函數(shù)。

創(chuàng)建數(shù)據(jù)庫表格

The first step in making your plugin create database tables automatically is to create a PHP function within your plugin that adds a table or tables to the WordPress MySQL database. For purposes of this article, we'll assume you want to call this function jal_install.

使得你的插件自動創(chuàng)建數(shù)據(jù)庫表格，首先在插件內(nèi)創(chuàng)建PHP函數(shù)，向WordPress MySQL數(shù)據(jù)庫添加一個或者多個表格。鑒于本篇文章的目的，我們假定你稱這個函數(shù)為jal_install。

數(shù)據(jù)庫表格前綴

In the wp-config.php file, a WordPress site owner can define a database table prefix. By default, the prefix is "wp_", but you'll need to check on the actual value and use it to define your database table name. This value is found in the $wpdb->prefix variable. (If you're developing for a version of WordPress older than 2.0, you'll need to use the $table_prefix global variable, which is deprecated in version 2.1).

在wp-config.php文件中，WordPress站點所有人可以定義數(shù)據(jù)庫表格前綴。默認情況下，前綴是"wp_"，但是你需要查看真正的參數(shù)值并且使用這個參數(shù)值定義你的數(shù)據(jù)庫表格名稱?？梢栽?tt>$wpdb->prefix變數(shù)中找到這個參數(shù)值的名稱。（如果你為WordPress2.0之前的版本創(chuàng)建，你需要使用$table_prefix全局變數(shù)，這個變數(shù)在2.1版本時，已取消）。 So, if you want to create a table called (prefix)liveshoutbox, the first few lines of your table-creation function will be:

因此，如果你想要創(chuàng)建稱為(prefix)liveshoutbox的表格，你創(chuàng)建表格的函數(shù)的前幾行是：

function jal_install () {
   global $wpdb;

   $table_name = $wpdb->prefix . "liveshoutbox";

function jal_install () {
   global $wpdb;

   $table_name = $wpdb->prefix . "liveshoutbox";

表格已經(jīng)存在嗎?

The next step in creating the table is to see if the table has already been created. The following if statement runs a SHOW TABLES SQL query to try to find the table, and then compares the result to our table name:

其次，是要查看表格是否已經(jīng)創(chuàng)建好了。下面的if聲明運行SHOW TABLES SQL查詢，嘗試找到表格，然后將結果與我們的表格名稱相比較：

    if($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {

    if($wpdb->get_var("SHOW TABLES LIKE '$table_name'") != $table_name) {

創(chuàng)建或者更新表格

The next step is to actually create the database table. Rather than executing an SQL query directly, we'll use the dbDelta function in wp-admin/upgrade-functions.php (we'll have to load this file, as it is not loaded by default). The dbDelta function examines the current table structure, compares it to the desired table structure, and either adds or modifies the table as necessary, so it can be very handy for updates (see wp-admin/upgrade-schema.php for more examples of how to use dbDelta). Note that the dbDelta function is rather picky, however. For instance:

接著是要真正的創(chuàng)建數(shù)據(jù)庫表格。不直接地執(zhí)行SQL查詢，我們在wp-admin/upgrade-functions.php中使用dbDelta函數(shù)（我們需要載入這個文件，因為默認不會載入這個文件）。dbDelta函數(shù)檢查了當前表格結構，將這個結構與理想的表格結構相比較，添加或者更改表格，這樣表格會便于更新（查看wp-admin/upgrade-schema.php，關于怎樣使用dbDelta的更多的例子）。注意，dbDelta函數(shù)非常地特別，例如：

• You have to put each field on its own line in your SQL statement.

• 你需要將每個欄放入你的SQL聲明中各自的行中。

• You have to have two spaces between the words PRIMARY KEY and the definition of your primary key.

• 在單詞PRIMARY KEY和你的primary key定義之間需要有兩個空格。

• You must use the key word KEY rather than its synonym INDEX

• 你必須使用key word KEY而不是其近義詞INDEX

With those caveats, here are the next lines in our function, which will actually create or update the table. You'll need to substitute your own table structure in the $sql variable:

下面是我們的函數(shù)中的另外幾行，會真正地創(chuàng)建或者更新表格。你需要在$sql變數(shù)中取代你自己的表格結構。

$sql = "CREATE TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(55) NOT NULL,
	  UNIQUE KEY id (id)
	);";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql);

$sql = "CREATE TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(55) NOT NULL,
	  UNIQUE KEY id (id)
	);";

require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
dbDelta($sql);

添加初始數(shù)據(jù)

Finally, you may want to add some data to the table you just created. Here is an example of how to do that:

最后，你肯能需要為你剛剛創(chuàng)建的表格添加一些數(shù)據(jù)。下面是關于怎樣操作的例子：

  $welcome_name = "Mr. Wordpress";
  $welcome_text = "Congratulations, you just completed the installation!";

  $insert = "INSERT INTO " . $table_name .
            " (time, name, text) " .
            "VALUES ('" . time() . "','" . $wpdb->escape($welcome_name) . "','" . $wpdb->escape($welcome_text) . "')";

  $results = $wpdb->query( $insert );

  $welcome_name = "Mr. Wordpress";
  $welcome_text = "Congratulations, you just completed the installation!";

  $insert = "INSERT INTO " . $table_name .
            " (time, name, text) " .
            "VALUES ('" . time() . "','" . $wpdb->escape($welcome_name) . "','" . $wpdb->escape($welcome_text) . "')";

  $results = $wpdb->query( $insert );

NOTE: Even though we defined $welcome_name and $welcome_text in this function and know that there are no SQL special characters in them, it's still a good idea to always run a variable through the $wpdb->escape function before passing it to the database to prevent security problems and random bugs.

注:即使我們在這個函數(shù)中定義了$welcome_name 和$welcome_text而且知道其中沒有特別的SQL字符，通過$wpdb->escape函數(shù)運行變數(shù)，然后再將變數(shù)傳遞到數(shù)據(jù)庫，仍然是個好注意，這樣可以避免一些安全問題以及一些隨意的程序錯誤。

版本選項

Another excellent idea is to add an option to record a version number for your database table structure, so you can use that information later if you need to update the table:

另一個好主意是，添加選項，記錄你的數(shù)據(jù)庫表格結構的版本數(shù)字，這樣如果隨后你需要更新表格，你可以使用這個信息：

add_option("jal_db_version", "1.0");

add_option("jal_db_version", "1.0");

整個函數(shù)

This function is done. Let's see it all in one piece. Note that the version number is now stored in a global variable.

這個函數(shù)完成了。讓我們看看這個整體。注意版本數(shù)字現(xiàn)在儲存在全局變數(shù)中。

$jal_db_version = "1.0";

function jal_install () {
   global $wpdb;
   global $jal_db_version;

   $table_name = $wpdb->prefix . "liveshoutbox";
   if($wpdb->get_var("show tables like '$table_name'") != $table_name) {
      
      $sql = "CREATE TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(55) NOT NULL,
	  UNIQUE KEY id (id)
	);";

      require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
      dbDelta($sql);

      $welcome_name = "Mr. Wordpress";
      $welcome_text = "Congratulations, you just completed the installation!";

      $insert = "INSERT INTO " . $table_name .
            " (time, name, text) " .
            "VALUES ('" . time() . "','" . $wpdb->escape($welcome_name) . "','" . $wpdb->escape($welcome_text) . "')";

      $results = $wpdb->query( $insert );
 
      add_option("jal_db_version", $jal_db_version);

   }
}

$jal_db_version = "1.0";

function jal_install () {
   global $wpdb;
   global $jal_db_version;

   $table_name = $wpdb->prefix . "liveshoutbox";
   if($wpdb->get_var("show tables like '$table_name'") != $table_name) {
      
      $sql = "CREATE TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(55) NOT NULL,
	  UNIQUE KEY id (id)
	);";

      require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
      dbDelta($sql);

      $welcome_name = "Mr. Wordpress";
      $welcome_text = "Congratulations, you just completed the installation!";

      $insert = "INSERT INTO " . $table_name .
            " (time, name, text) " .
            "VALUES ('" . time() . "','" . $wpdb->escape($welcome_name) . "','" . $wpdb->escape($welcome_text) . "')";

      $results = $wpdb->query( $insert );
 
      add_option("jal_db_version", $jal_db_version);

   }
}

調(diào)用函數(shù)

Now that we have the initialization function defined, we want to make sure that WordPress calls this function when the plugin is activated by a WordPress administrator. To do that, we will use the activate_ action hook. If your plugin file is wp-content/plugins/plugindir/pluginfile.php, you'll add the following line to the main body of your plugin:

既然我們已經(jīng)定義了初始化函數(shù)，我們希望確定當插件由WordPress管理員激活的時候，WordPress調(diào)用了這個函數(shù)。要做到這一點，我們將要使用activate_ action hook。如果你的插件文件是wp-content/plugins/plugindir/pluginfile.php，你將會將下面這些行添加到你的插件的主要部分中。

register_activation_hook(__FILE__,'jal_install');

register_activation_hook(__FILE__,'jal_install');

See WordPress:Function_Reference/register_activation_hook for more details. 更多詳細信息，請看看 Function_Reference/register_activation_hook。

添加升級函數(shù)

Over the lifetime of your plugin, you may find that you need to change the plugin's database structure in an upgraded version. To do that, you will need to create update code within your plugin that will detect that a new version has been installed, and upgrade the database structure. The easiest thing to do is to add the code to the jal_install function we just created.

在你的插件的使用壽命之內(nèi)，你可能會發(fā)現(xiàn)你需要在已升級的版本中，更改插件的數(shù)據(jù)庫結構。你需要在插件內(nèi)創(chuàng)建更新代碼，這個代碼會探測到已經(jīng)安裝了新的版本，并且升級數(shù)據(jù)庫結構。最簡單的方法就是向我們剛剛創(chuàng)建的jal_install函數(shù)，添加代碼。

Note that you will need to make sure that the function gets called. So tell your plugin users that when they upgrade, they need to deactivate the plugin, copy in the new plugin file, and then activate the plugin again.

現(xiàn)在你需要確定函數(shù)已經(jīng)得到調(diào)用。告訴你的插件用戶什么時候升級，用戶取藥取消運行插件，在新的插件文件中復制，然后再激活插件。

So, let's assume that the function above was used to create database version 1.0 of your plugin, and you are now upgrading to version 1.1 so that the URL field can be wider (100 characters instead of 55). You will need to add the following lines to the end of your jal_install function, to check the version and upgrade if necessary:

因此，我們假定上述的函數(shù)是用戶創(chuàng)建你的插件1.0版本的數(shù)據(jù)庫的，現(xiàn)在你升級到1.1版本，這樣URL field會更寬（100個字符而不是55個字符）。你需要在你的jal_install函數(shù)的結尾部分添加下面的行，查看版本，如果必要，就升級：

   $installed_ver = get_option( "jal_db_version" );

   if( $installed_ver != $jal_db_version ) {

      $sql = "CREATE TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(100) NOT NULL,
	  UNIQUE KEY id (id)
	);";

      require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
      dbDelta($sql);

      update_option( "jal_db_version", $jal_db_version );
  }

   $installed_ver = get_option( "jal_db_version" );

   if( $installed_ver != $jal_db_version ) {

      $sql = "CREATE TABLE " . $table_name . " (
	  id mediumint(9) NOT NULL AUTO_INCREMENT,
	  time bigint(11) DEFAULT '0' NOT NULL,
	  name tinytext NOT NULL,
	  text text NOT NULL,
	  url VARCHAR(100) NOT NULL,
	  UNIQUE KEY id (id)
	);";

      require_once(ABSPATH . 'wp-admin/includes/upgrade.php');
      dbDelta($sql);

      update_option( "jal_db_version", $jal_db_version );
  }

You'll also need to change the global $jal_db_version variable at the top of the file, and of course you'll want to change the initialization section created above to use the new table structure.

在文件的頂上方，你也需要更改全局$jal_db_version變數(shù)，當前，你想要更改上面創(chuàng)建的初始化部分，使用新的表格結構。

資源

For further reading on plugin development, check out WordPress:Plugin Resources, a comprehensive list of plugin resources. You may also find this post from the wp-hackers mailing list to be helpful: WordPress Hackers Mailing List: Answer to Plugin Requires Additional Tables

要深入地閱讀插件發(fā)展內(nèi)容，請查看插件資源，插件資源的完整列表。你也可以在wp-hackers mailing list中發(fā)現(xiàn)這篇文章很有用：WordPress Hackers Mailing 列表: Answer to Plugin Requires Additional Tables