WordPressに新規テーブル作成 – HeidiSQLでデータベースらくらく開発

今回は 少し前に作成したプラグインアンテナという WordPress カテゴリー別人気プラグイン紹介サービスを作成した時のデータベース処理についての紹介です

データベーステーブル作成

WordPress に新規テーブルを作成して、SQLによる基本的なアクセス方法について紹介します

WordPress にはデータベース操作用の wpdb クラス関数が用意されているので、その使い方と合わせて紹介します

リレーショナル・データベースというだけで、なんとなく抵抗を感じていましたが、WordPressで使われている MySQL は情報も多いので学びやすくもあります (^^)

準備

ローカル環境の XAMPP を使って WordPress の開発環境を作成して下さい

ちょっと古い記事ですが、この辺りを参考に WordPress で使用しているデータベースに接続出来るようにして下さい

次に Windows 限定となってしまいますが、HeidiSQL が超絶便利なのでインストールして下さい。 phpMyAdmin や MySQL workbench でも良いのですが、HeidiSQL のほうが作業がはかどります (^^)

準備が出来たら、プラグインから $wpdb を使いデータベースを操作するコードを順に作成していきます

WordPressは、内部でデータベースを使用しているので、データベースの作成と接続処理は悩まなくてもOKです。既に使用できる状態なので、直ぐにテーブルを作成することが出来ます

 

テーブル設計

テーブルと言うのは、行（レコード）と列（フィールド）によって構成される表計算のような形式のデータの集まりです

リレーショナルデータベースというのは、データを正規化という、無駄な重複データを持たないように適切に複数のテーブルに分割して単純化することで、その複数のテーブルを関連付け（リレーション）して必要なデータを効率よく格納、取得するためのデータ管理システムです

 

テーブル設計って言っても…　思考停止しそうですが、お茶でも飲んで気楽にいきましょう (^^)

 

簡単に言えば、データを全て洗い出して、そのデータの追加、更新、削除、検索等が無駄なく効率良くできるようにテーブルを作成するということです

検索した時にどんなデータがほしいのか考えれば、テーブル構造は自然と決まってくるような気がします 。例えば、ダウンロード順に並べたいとか、登録日順に並べたいとか、日本製を選択したいとか漠然とイメージするだけでも必要になりそうなデータが思い浮かんで来ると思います

 

まずはデータです

どんなデータがあり、どれを使うのかを決めます

今回はプラグインを紹介するサービスなので、データは WordPress.org API を使って収集したデータを使用します

様々なデータが取得できますが、下記データを使うこととします

slug	プラグインスラッグ（ユニーク）
name	プラグイン名称
author_profile	wordpress.org 登録プロフィール
author	作者及びホームサイトへのリンク
contributors	協力者
version	プラグインバージョン
added	プラグイン登録日
last_updated	プラグイン最終更新日
requires	必要な WPバージョン
downloaded	ダウンロード数
active_installs	有効インストール推定数
num_ratings	レビュー数
rating	レビュー評価点
icons	プラグインアイコン（形式別）
homepage	プラグインホームページへのリンク

この個別のデータがフィールドとなり、その集まりとしてのプラグインデータがレコードとなります

このままのデータを一つのテーブルで作ってしまえば一見わかりやすそうですが、何万件というデータを考えると重複するデータ等を整理しないと無駄が多くなりパフォーマンスや管理面で好ましくありません

例えば、このデータではプラグインのデータと作者のデータに分けることが出来ます。

作者は、いくつものプラグインを作成することが出来ます。ここに作者データを含めてしまうとデータが重複し、作者データに修正等があった場合に処理が煩雑になります

他にも、ダウンロード数やレビュー数等日々変化するデータを分割することも考えられますが、今回は作者とプラグインテーブルの２つに分割するだけにしておきます

次に各データの型とサイズを決めます

基本的なデータ型

まとめてくれているサイトがあるのでリンクを紹介して、済ませます　(^_^;)

今回は以下のデータ型を使用します

smallint	2byte整数
int	4byte整数
varchar	可変長文字列(文字数上限指定)
text	可変長テキスト(64Kバイト以下)
datetime	日時型
float	単精度浮動小数点

※varchar と text はどちらも文字列を格納できますが、varchar は初期値の設定と文字数の上限を指定出来ます。どちらか迷ったら数Kバイト以下なら varchar, それ以上なら text または mediumtext を使っておけば大抵は大丈夫です

次に各テーブルに格納するデータは、主キー（PRIMARY KEY）を設けてデータを一意に決められるようにする必要があります

また、各フィールドには UNIQE, NOT NULL, AUTO_INCREMENT, DEFAULT 等を指定することで格納するデータに制約や初期値を設定することが出来ます

• 各テーブルに１つだけ識別IDを自動的に設定するための PRIMARY KEY を AUTO_INCREMENT でセットします
• データが重複してはいけないフィールドには UNIQUE KEY を、必須データのフィールドには NOT NULL を指定して不正なデータが登録されないよう制約を設けます

また、テーブルを作者とプラグインデータの２つに分割するので、テーブル間でリレーションを行えるように作者テーブルの PRIMARY KEY ID値を格納するため、プラグインテーブルに author_id というフィールド用意、他にも国情報等いくつか必要になるフィールドを追加します

※リレーションに対して FORIGN KEY という参照整合性の為の制約機能は用いることも出来ますが、今回は使用せずにテーブル間は疎な結合状態のまま扱います

また、現時点ではまだ、インデックスに関してはあまり考えなくてもOKです。後から設定できるので実際にデータを登録してチューニングしながら設定していきます

ここまでくればテーブル設計はほとんど終わったようなもんです

えっよくわからないって

とりあえず作ってみれば、だんだん分かったような気になるので大丈夫です (^^)

 

テーブル作成

設計したテーブルを作成する SQL の CREATE TABL コマンドを作成します

こんなコードです

/**
 * プラグインと著者情報のデータベーステーブル作成
 */
public function table_create() {

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    global $wpdb;
    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE $this->authors_table (
            id int(11) unsigned NOT NULL AUTO_INCREMENT,
            profile varchar(100) NOT NULL,
            author varchar(200) NOT NULL,
            location varchar(32),
            updated datetime,
            PRIMARY KEY  (id),
            UNIQUE KEY profile (profile)
            ) $charset_collate;";
    dbDelta( $sql );
        
    $sql = "CREATE TABLE $this->plugins_table (
            id int(11) unsigned NOT NULL AUTO_INCREMENT,
            slug varchar(80) NOT NULL,
            name varchar(200) NOT NULL,
            optag varchar(32),
            author_id int(11),
            contributors text,
            version varchar(16),
            added datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            last_updated datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            requires varchar(8),
            tested varchar(8),
            downloaded int(11) DEFAULT 0,
            active_installs varchar(32),
            num_ratings smallint(4),
            rating float,
            icon_link varchar(200),
            homepage varchar(200),
            country varchar(20),
            PRIMARY KEY  (id),
            KEY author_id (author_id),
            UNIQUE KEY slug (slug)
            ) $charset_collate;";
    dbDelta( $sql );
}

このSQLコマンドを dbDelta 関数を使って実行すればテーブルの作成だけでなく、軽微な変更にも対応できるようになります

 

dbDelta 関数

dbDelta は、便利な関数ですがいくつかの使用上の決まり事があります

• 1行につき1つのフィールドを定義（複数定義不可）
• PRIMARY KEYと主キーの定義の間には二つのスペースが必要
• インデックスの指定は、INDEXではなく、KEYという言葉を使うこと
• フィールド名をアポストロフィやバッククォートで括らないこと
• フィールドのデータ型は小文字で指定すること
• SQL のキーワード CREATE TABLE, UPDATE 等は大文字で指定すること
• 長さパラメータを受け付けるすべてのフィールドに長さを指定すること（例 int(11)）

※大文字、小文字の指定が守られていないと一見問題なくテーブルが作成されたように見えても、更新時のALTER TABLE 処理でエラーが発生する場合があります。また、複合インデックスを指定する場合カンマで区切った後にスペースを入れると誤動作するので注意です

これらの決まりが守られていれば、現在のテーブル構造を調べ、指定されたSQLと比較して、CREATE TABEL / ALTER TABLE で柔軟に処理してくれます

詳しくは下記ページを参照して下さい

上記参照ページでは、バージョン等を別途オプションデータとして保持して、変化があった場合のみ実行させるサンプルが乗っていますが、dbDelta は、対象テーブルのフィールド定義が同じなら何もしないので本来はバージョンチェックは不要と思われます

但し、大きな変更がある場合は、dbDelta で対応できない場合もあるので、バージョンデータ等を使って、自前で DROP TABLE や CREATE TABLE を使い再作成したほうが良い場合もあります。いづれにせよ、テーブルを変更する場合は十分な確認が必要ですので dbDelta を使っているからうまくいくはずという過信は禁物です

使えるデータベースエンジンは、MyISAMとInnoDB がありますが、それぞれ特徴があり用途によってはどちらのエンジンを使用するのか指定する必要があります。例えば、トランザクションが必要とされる場合に はInnoDBを指定するということになりますが、今回はトランザクション処理は行わないので、特に指定せず、デフォルトで作成します

テーブルを作成するタイミング

管理画面でプラグインが有効化された時に register_activation_hook を使って登録した関数をコールバックするように作ります

テーブルの作成に失敗する場合

テーブル作成例を検索するとクラス内で register_activation_hook を使っている例が沢山見受けられたのですが、やってみるとクラスのインスタンス生成タイミングの関係なのか register_activation_hook で呼び出されませんでした。

失敗する場合は、クラス外にグローバル関数として定義してみてください

//プラグイン有効時のデータベーステーブル更新
//クラスメソッドでの有効化フックがうまく動作しないのでクラス外で処理する
require_once 'plugins_DB.php';
if(is_admin()){ 
    function wp_plugin_ranking_activation() {
        $pdb = new Plugins_DB();
        $pdb->table_create();
    }
    register_activation_hook( __FILE__,   'wp_plugin_ranking_activation' );
}

 

作成したテーブルを確認

作成したテーブルは HeidiSQL でを使って簡単に確認出来ます

MySQLに接続して wordpress データベースをクリックするとテーブルが作成されていれば表示されるはずです

これでテーブルが作成されたのが確認出来ました (^^)

作者テーブル wp_plugins_ranking_author

プラグインテーブル wp_plugins_ranking_base

うまく作成されない場合は、NetBeans のデバッガを使って wpdbを参照すれば エラー内容やエラーを起こしたSQL文を確認出来ますので間違いがないかよく確認しましょう

SQLを修正する場合は、プログラムを直接修正して確認するより HeidiSQL からSQLを打ち込んで実行してみるのが効率が良いです。この時作成されたテーブルは HeidiSQL を使って削除してから、プログラムを修正して再度確認します

こんなふうにプログラムと HeidiSQL での作業を交互に行いながら、今後も作業していくわけですが HeidiSQL の動作がとても軽快なので気持ちいいです (^^)

 

データの登録、更新

まだテーブルが空なのでデータを登録していきます

データ収集の詳細については、ここでは カッツアイ (LIFE 好きです) 、Wordpress.org API を使って収集したデータを wp_cron 処理を使って自動的に登録、更新、削除 するプログラムで処理しています

データの登録条件としては、

• 既存のデータが存在するかチェックして登録か更新かを判断
• 登録するプラグインデータは最後の更新から２年以内のもの

使用しているSQLコマンドは主に SELECT, INSERT, UPDATE です

wpdb クラスの query 関数を使えば、様々な SQL クエリを実行できますが、SELECTには扱いやすいようにラッピングした関数もあるので get_row(), get_result() 等を使えば結果をオブジェクトや配列型に変換して取得できるので便利です

また、SQLを発行する場合はセキュリティを考慮してSQLインジェクション対策を行います

SQLコマンドを生成する時に変数等の値を用いている場合には、悪意を持った値をセットされているかも知れませんので、実行する前にクエリデータを sprintf() に似た構文を持つ prepare メソッドを使って SQL エスケープします

詳しくは wpdb Class  – WordPress Codex 日本語版 を参照して下さい

SELECT

登録済みのデータから条件を指定して取得します。また登録済みかどうかの判定でも使用します

今回は、作者テーブルやプラグイン情報テーブルに登録されているデータをユニークなフィールドデータに一致する条件で、個々に取得する例です

//作者情報
// $profile プロフィール "//profiles.wordpress.org/xxxxxxxx/"
$obj = $wpdb->get_row( $wpdb->prepare("SELECT * FROM $this->authors_table WHERE profile = %s", $profile));

//プラグイン情報
// $slug  プラグインスラッグ
$obj = $wpdb->get_row( $wpdb->prepare("SELECT * FROM $this->plugins_table WHERE slug = %s", $slug));

※リレーションを使う SELECT は次回で紹介します

INSERT

SELECT コマンドで既存データがない場合に、 INSERT を使って新規データを登録していきます

取得したプラグインデータを作者とプラグインテーブルの２つに分けて登録します

1. 先に作者テーブルに登録
2. wpdb クラスの insert_id に、今登録した作者テーブルの AUTO_INCREMENT によって生成された ID が格納されているので取得
3. 次に、そのID値をプラグインデータの author_id としてセットしてリレーション出来るようにして、プラグインテーブルに登録

実際には、作者データが登録済みの場合もあるので、既に登録されていたら作者テーブルの更新日時とプラグインデータの更新日時を比較してスキップさせたり、更新したりともう少し複雑な処理をしているがここでは カッツアイ　して分かりやすいように主要部のみ抜き出したコード例を示します

//作者及びプラグインテーブル登録

  $res = $wpdb->query( $wpdb->prepare("INSERT INTO $this->authors_table (profile, author, location, updated) VALUES ( %s, %s, %s, %s)", $pobj->author_profile, $pobj->author, $location, $now->format('Y-m-d H:i:s')) )
  $author_id = ($res !== false) ? $wpdb->insert_id : null;

  if(!empty($author_id)){

      $res = $wpdb->query( $wpdb->prepare("INSERT INTO $this->plugins_table 
              (slug, name, author_id, contributors, version, added, last_updated, requires, tested, downloaded, active_installs, num_ratings, rating, icon_link, homepage, country, optag )
              VALUES ( %s, %s, %d, %s, %s, %s, %s, %s, %s, %d, %s, %d, %f, %s, %s, %s, %s)", 
              $pobj->slug,
              $pobj->name,
              $author_id,
              $contributors,
              $pobj->version,
              $add->format('Y-m-d H:i:s'),
              $upd->format('Y-m-d H:i:s'),
              $requires,
              $tested,
              $pobj->downloaded,
              $pobj->active_installs,
              $pobj->num_ratings,
              $pobj->rating,
              $icon_link,
              $pobj->homepage,
              $country, 
              $optag ));

   }

UPDATE

登録済みの既存データに変更があった場合は UPDATE で更新します

以下のような感じで連想配列を用い変化したフィールドのみを書き換えます

  $data = '';
  if($gobj->name != $pobj->name)
      $data['name'] = $pobj->name;
  if(empty($gobj->author_id) || $gobj->author_id != $author_id)
      $data['author_id'] = $author_id;                
  if($gobj->contributors != $contributors)
      $data['contributors'] = $contributors;
　
　　以下各フィールドの変更チェック
　　　｜
　　　｜
　　　｜

  if(!empty($data)) {
      $sql = "UPDATE $this->plugins_table SET ";
      $idx = count($data);
      foreach ($data as $key => $val) {
          $sql .= "$key = '$val'";
          $sql .= ($idx > 1)? ', ' : ' ';
          $idx--;
      }
      $sql .= "WHERE id = '$lastid'";
      $res = $wpdb->query( $sql ); 
  }

 

以上の条件でデータを収集して登録すると 作者データが約１万件、プラグインデータが約２万件登録されました（日々変化していきますが…）

登録されたデータを HeidiSQL で確認します

作者データ

プラグインデータ

 

作成したデータベース処理クラスのコード

何かの参考になるかもしれませんので、今回作成したデータベース処理クラスを一応貼り付けておきます

国情報やカテゴリー分類等クラスの処理を呼び出している部分もあり、わかりにくいところもあるかも知れませんがとりあえずデータベース関連の処理をまとめたクラスになっています

<?php
/**
 * Description of plugins_DB
 *
 * @author eno_y
 */
class Plugins_DB {

    private $plugins_table;
    private $authors_table;

    public function __construct() {
        global $wpdb;
        $this->plugins_table = $wpdb->prefix . 'plugins_ranking_base';
        $this->authors_table = $wpdb->prefix . 'plugins_ranking_author';        
    }

    /**
     * テーブルの存在確認
     * 
     * @return boolean
     */
    public function is_table_exist() {
        global $wpdb;
        $is_base = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->plugins_table));
        $is_author = $wpdb->get_var($wpdb->prepare("SHOW TABLES LIKE %s", $this->authors_table));
        if($is_base == $this->plugins_table && $is_author == $this->authors_table)
            return true;
        else
            return false;
    }
        
    /**
     * プラグインと著者情報のデータベーステーブル作成
     */
    public function table_create() {

        require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
        global $wpdb;
        $charset_collate = $wpdb->get_charset_collate();

        $sql = "CREATE TABLE $this->authors_table (
            id int(11) unsigned NOT NULL AUTO_INCREMENT,
            profile varchar(100) NOT NULL,
            author varchar(200) NOT NULL,
            location varchar(32),
            updated datetime,
            PRIMARY KEY  (id),
            UNIQUE KEY profile (profile)
        ) $charset_collate;";
        dbDelta( $sql );
        
        $sql = "CREATE TABLE $this->plugins_table (
            id int(11) unsigned NOT NULL AUTO_INCREMENT,
            slug varchar(80) NOT NULL,
            name varchar(200) NOT NULL,
            optag varchar(32),
            author_id int(11),
            contributors text,
            version varchar(16),
            added datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            last_updated datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
            requires varchar(8),
            tested varchar(8),
            downloaded int(11) DEFAULT 0,
            active_installs varchar(32),
            num_ratings smallint(4),
            rating float,
            icon_link varchar(200),
            homepage varchar(200),
            country varchar(20),
            PRIMARY KEY  (id),
            KEY author_id (author_id),
            UNIQUE KEY slug (slug),
            KEY new (added),
            KEY japan (country(5)),
            KEY download (downloaded),
            KEY review (num_ratings),
            KEY updated (last_updated),
            KEY cat_download (optag(4),downloaded),
            KEY cat_review (optag(4),num_ratings),
            KEY cat_updated (optag(4),last_updated)
        ) $charset_collate;";
        //複合インデックス指定時はカンマ区切りの後にスペースあるとエラーとなるので注意
        //データ型 unsigned は 小文字で指定しないと変更ありとして認識されるので注意
        dbDelta( $sql );
    }

    /**
     * プラグインと著者情報のデータベーステーブル削除
     */
    public function table_drop() {
        global $wpdb;
		$wpdb->query( "DROP TABLE IF EXISTS $this->plugins_table" );
		$wpdb->query( "DROP TABLE IF EXISTS $this->authors_table" );
    }
    
    /**
     * 作者情報オブジェクト取得
     * 
     * @param string $profile プロフィール "//profiles.wordpress.org/xxxxxxxx/"
     * @return object 作者情報オブジェクト
     */
    public function get_authorinfo($profile) {
        global $wpdb;
        $obj = $wpdb->get_row( $wpdb->prepare("SELECT * FROM $this->authors_table  WHERE profile = %s", $profile));
        return $obj;
    }
    
    /**
     * 作者情報登録/更新
     * 
     * @param object $pobj      wordpress.org プラグイン情報APIで取得したデータ
     * @param string $rlocation 参照引数 loaction 情報を呼び出し元変数にセットする
     * @return author_table id
     */
    public function set_authorinfo($pobj, &$rlocation) {
        global $wpdb;
        $aobj = $this->get_authorinfo($pobj->author_profile);
        if(!is_object($aobj)){
            $lastid = null;
            if(!empty($pobj->author_profile)){
                $location = null;
                $location = WP_plugin_ranking::get_location($pobj->author_profile, $pobj->author);
                $now = new DateTime();
                $res = $wpdb->query( $wpdb->prepare("INSERT INTO $this->authors_table (profile, author, location, updated) VALUES ( %s, %s, %s, %s)", $pobj->author_profile, $pobj->author, $location, $now->format('Y-m-d H:i:s')) );
                $lastid = ($res !== false) ? $wpdb->insert_id : null;
            }
        } 
        else {
            //作者データ保存日よりプラグイン更新日時が新しい場合に取得（同一作者の別プラグインでの無駄な取得を抑制する）
            $lastid = $aobj->id;
            $location = $aobj->location;
            $upd  = new DateTime($pobj->last_updated);
            $gupd = new DateTime($aobj->updated); 
            if($upd > $gupd || empty($location)) {
                $data = '';
                if($aobj->author != $pobj->author)
                    $data['author'] = $pobj->author;

                $location = WP_plugin_ranking::get_location($pobj->author_profile, $pobj->author);
                if($location !== null){
                    if($aobj->location != $location){
                        $data['location'] = $location;
                    }
                }
                if(!empty($data)) {
                    $now = new DateTime();
                    $data['updated'] = $now->format('Y-m-d H:i:s');
                    $sql = "UPDATE $this->authors_table SET ";
                    $idx = count($data);
                    foreach ($data as $key => $val) {
                        $sql .= "$key = '$val'";
                        $sql .= ($idx > 1)? ', ' : ' ';
                        $idx--;
                    }
                    $sql .= "WHERE id = '$lastid'";
                    $res = $wpdb->query( $sql ); 
                    $lastid = ($res !== false) ? $lastid : null;
                }
            }
        }
        $rlocation = $location;
        return $lastid;
    }

    /**
     * プラグイン情報オブジェクト取得
     * 
     * @param string $slug  プラグインスラッグ
     * @return object  プラグイン情報オブジェクト
     */
    //SELECT * FROM wp_plugins_ranking_author AS a INNER JOIN wp_plugins_ranking_base As p ON a.id = p.author_id WHERE p.country = 'Japan' And p.last_updated > '2012-05-01'
    public function get_plugininfo($slug, $withauthor=true) {
        global $wpdb;
        if($withauthor){
            $obj = $wpdb->get_row( $wpdb->prepare("SELECT * FROM $this->authors_table AS a INNER JOIN $this->plugins_table As p ON a.id = p.author_id WHERE p.slug = %s", $slug));
        }
        else {
            $obj = $wpdb->get_row( $wpdb->prepare("SELECT * FROM $this->plugins_table As p WHERE p.slug = %s", $slug));
        }
        return $obj;
    }
    
    /**
     * プラグイン情報登録/更新
     *  
     * @param object $pobj      wordpress.org プラグイン情報APIで取得したデータ
     * @param object $gobj      データベースから取得したプラグイン情報データ
     * @param int  $author_id   作者テーブルのID
     * @return int  プラグインテーブル登録/更新時のID
     */
    public function set_plugininfo($pobj, $gobj, $author_id, $location) {
        global $wpdb;
        $lastid = null;
        if(!empty($author_id) && is_object($pobj)){
            $requires = '';
            if(preg_match('#([0-9]\.[0-9])(\.[0-9])?#i', $pobj->requires, $match))
                $requires = $match[1];
            $tested = '';
            if(preg_match('#([0-9]\.[0-9])(\.[0-9])?#i', $pobj->tested, $match))
                $tested = $match[1];
            $contributors = (!empty($pobj->contributors))? serialize($pobj->contributors) : '';
            $icon_link = WP_plugin_ranking::get_pulugin_iconlink( $pobj->icons);
            $add = new DateTime($pobj->added);
            $upd = new DateTime($pobj->last_updated);
            $optag = WP_plugin_ranking::get_opcategory($pobj->name);
            $country = null;
            if(!empty($location)) { //country は location 設定後にセット
                $country = '-';
                if($location == 'Japan'){
                    $country = 'Japan';   //Location 'Japan' 優先
                }
                elseif(!empty($pobj->homepage)) {
                    //プラグインのホームページのドメインかIPアドレスから国を取得
                    $pattern = '/(https?):\/\/([-_.!~*\'()a-zA-Z0-9;\/?:\@&=+\$,%#]+)/u';
                    if(preg_match($pattern, $pobj->homepage, $match)){
                        $country = WP_plugin_ranking::get_ipcountry($match[1] . '://' . $match[2]);
                    }
                }
            }
            if(!is_object($gobj)){
                $res = $wpdb->query( $wpdb->prepare("INSERT INTO $this->plugins_table 
                        (slug, name, author_id, contributors, version, added, last_updated, requires, tested, downloaded, active_installs, num_ratings, rating, icon_link, homepage, country, optag )
                        VALUES ( %s, %s, %d, %s, %s, %s, %s, %s, %s, %d, %s, %d, %f, %s, %s, %s, %s)", 
                        $pobj->slug,
                        $pobj->name,
                        $author_id,
                        $contributors,
                        $pobj->version,
                        $add->format('Y-m-d H:i:s'),
                        $upd->format('Y-m-d H:i:s'),
                        $requires,
                        $tested,
                        $pobj->downloaded,
                        $pobj->active_installs,
                        $pobj->num_ratings,
                        $pobj->rating,
                        $icon_link,
                        $pobj->homepage,
                        $country, 
                        $optag ));
                $lastid = ($res !== false) ? $wpdb->insert_id : null;
            } 
            else {
                $lastid = $gobj->id;
                $data = '';
                if($gobj->name != $pobj->name)
                    $data['name'] = $pobj->name;
                if(empty($gobj->author_id) || $gobj->author_id != $author_id)
                    $data['author_id'] = $author_id;                
                if($gobj->contributors != $contributors)
                    $data['contributors'] = $contributors;
                if($gobj->version != $pobj->version)
                    $data['version'] = $pobj->version;
                if($gobj->requires != $requires)
                    $data['requires'] = $requires;
                if($gobj->tested != $tested)
                    $data['tested'] = $tested;
                if($gobj->icon_link != $icon_link)
                    $data['icon_link'] = $icon_link;
                if($gobj->active_installs != $active_installs)
                    $data['active_installs'] = $active_installs;
                if($gobj->homepage != $pobj->homepage)
                    $data['homepage'] = $pobj->homepage;
                $data['last_updated'] = $upd->format('Y-m-d H:i:s');
                $data['downloaded'] = $pobj->downloaded;
                $data['num_ratings'] = $pobj->num_ratings;
                $data['rating'] = $pobj->rating;
                $data['country'] = $country;
                $data['optag'] = $optag;
                if(!empty($data)) {
                    $sql = "UPDATE $this->plugins_table SET ";
                    $idx = count($data);
                    foreach ($data as $key => $val) {
                        $sql .= "$key = '$val'";
                        $sql .= ($idx > 1)? ', ' : ' ';
                        $idx--;
                    }
                    $sql .= "WHERE id = '$lastid'";
                    $res = $wpdb->query( $sql ); 
                    $lastid = ($res !== false) ? $lastid : null;
                }
            }
        }
        return $lastid;
    }

    /**
     * プラグインのダウンロード数やカテゴリー分類の変更データ更新
     * 
     * @param object $pobj      wordpress.org プラグイン情報APIで取得したデータ
     * @param object $gobj      データベースから取得したプラグイン情報データ
     */
    public function update_plugin_info($pobj, $gobj) {
        global $wpdb;
        $res = null;
        $data = '';
        if(is_object($pobj)){
            if($gobj->country === null){  //country は location 設定後にセットされる
                $country = null;
                $author = $this->get_authorinfo($pobj->author_profile);
                if(is_object($author)){
                    if($author->location === null){
                        $location = WP_plugin_ranking::get_location($pobj->author_profile, $pobj->author);
                        if($location === null){
                            //５日以上 location null なら取得不可として - をセットしておく
                            $now = new DateTime();
                            $aupd = new DateTime($author->updated);
                            $ckdate = $aupd->modify("+5 day");
                            if($ckdate < $now ){
                                $location = '-';    //ロケーション未設定
                            }
                        }
                        if($location !== null){
                            $res = $wpdb->query( $wpdb->prepare( "UPDATE $this->authors_table SET location = %s WHERE id = %d", $location, $author->author_id));
                            if($res === false)
                                $location = null;
                        }
                    }
                    else {
                        $location = $author->location;
                    }
                    if($location !== null){
                        //country 更新                
                        $country = '-';
                        if($location == 'Japan'){
                            $country = 'Japan';   //Location 'Japan' 優先
                        }
                        elseif(!empty($pobj->homepage)) {
                            //プラグインのホームページのドメインかIPアドレスから国を取得
                            $pattern = '/(https?):\/\/([-_.!~*\'()a-zA-Z0-9;\/?:\@&=+\$,%#]+)/u';
                            if(preg_match($pattern, $pobj->homepage, $match)){
                                $country = WP_plugin_ranking::get_ipcountry($match[1] . '://' . $match[2]);
                            }
                        }
                        $data['country'] = $country;
                    }
                }
            }
            if($gobj->num_ratings != $pobj->num_ratings){
                $data['num_ratings'] = $pobj->num_ratings;
                $data['rating'] = $pobj->rating;
            }
            if(empty($gobj->optag)){
                $data['optag'] = WP_plugin_ranking::get_opcategory($pobj->name);
            }
            if(empty($data)) {
                $diff = 10;
                if($gobj->downloaded > 100000)
                    $diff = 5000;
                elseif($gobj->downloaded > 500)
                    $diff = $gobj->downloaded / 100 * 5;
                if($gobj->downloaded + $diff < $pobj->downloaded ){
                    //DBへの負荷低減のためダウンロード数が 5%程度変化したら更新する（あまり利用されていないプラグインだと１ヶ月程度かかる）
                    //毎日のDB更新を可能な限り減らしたい
                    $data['downloaded'] = $pobj->downloaded;
                }
            }
            else {
                $data['downloaded'] = $pobj->downloaded;
            }
            if(!empty($data)) {
                $sql = "UPDATE $this->plugins_table SET ";
                $idx = count($data);
                foreach ($data as $key => $val) {
                    $sql .= "$key = '$val'";
                    $sql .= ($idx > 1)? ', ' : ' ';
                    $idx--;
                }
                $sql .= "WHERE id = '$gobj->id'";
                $res = $wpdb->query( $sql ); 
            }
        }
        return $res;
    }
    
    
    /**
     * プラグインテーブルの登録データ数取得
     * @return count
     */
    public function count_plugins() {
		global $wpdb;
        $count = $wpdb->get_var( "SELECT COUNT( id ) FROM $this->plugins_table" );
		return $count;
	}
    
    /**
     * 指定日以降に更新されたプラグイン数取得
     * 
     * @param string $strdate 'Y-m-d H:i:s'
     * @return count
     */
    //SELECT COUNT(id) FROM wp_plugins_ranking_base WHERE wp_plugins_ranking_base.last_updated > '2015-05-25 00:00:00' 
    public function count_updated_plugins($strdate ) {
		global $wpdb;
        $count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT( id ) FROM $this->plugins_table WHERE last_updated >= %s", $strdate ) );
		return $count;
	}
    
    /**
     * 指定日数更新されていないプラグイン数取得
     * 
     * @param int $before_days  日数　例：２年前 730
     * @return count
     */
    public function count_noupdate_plugins( $before_days ) {
        global $wpdb;
        $now = new DateTime();
        $ckdate = $now->modify("-$before_days day")->format('Y-m-d H:i:s');
		$count = $wpdb->get_var( $wpdb->prepare("SELECT COUNT( id ) FROM $this->plugins_table WHERE last_updated < %s ", $ckdate ));
		return $count;
    }

    /**
     * 作者 author テーブルの登録データ数取得
     * 
     * @return count
     */
    public function count_authors() {
		global $wpdb;
        $count = $wpdb->get_var( "SELECT COUNT( id ) FROM $this->authors_table" );
		return $count;
	}

    /**
     * 指定日以降に更新された作者情報数取得
     * 
     * @param string $strdate 'Y-m-d H:i:s'
     * @return count
     */
    //SELECT COUNT(id) FROM wp_plugins_ranking_author WHERE wp_plugins_ranking_author.updated > '2015-05-26 00:00:00' 
    public function count_updated_authors($strdate ) {
		global $wpdb;
        $count = $wpdb->get_var( $wpdb->prepare( "SELECT COUNT( id ) FROM $this->authors_table WHERE updated >= %s", $strdate ) );
		return $count;
	}

    /**
     * プラグインテーブルのカテゴリーフィールド optag クリア
     * ※カテゴリー分類のプログラム修正後に再セットさせるためのクリア
     */
    public function clear_plugins_optag() {
        global $wpdb;
        $res = $wpdb->query( "UPDATE $this->plugins_table SET optag = NULL WHERE optag IS NOT NULL");
        return $res;
    }
    
    /**
     * 指定日数以上更新されていないプラグインデータを削除（プラグインデータは削除しても日々の更新で復活することもある）
     * 
     * @param int $before_days  日数　例：２年前 730
     */
    //DELETE FROM `wp_plugins_ranking_base` WHERE `last_updated` < '2005-05-28 00:00:00';
    public function delete_plugins_record( $before_days ) {
        global $wpdb;
        $now = new DateTime();
        $ckdate = $now->modify("-$before_days day")->format('Y-m-d H:i:s');
		$res = $wpdb->query( $wpdb->prepare("DELETE FROM $this->plugins_table WHERE last_updated < %s", $ckdate ));
        return $res;
    }
    
    /**
     * 作者テーブルから登録プラグインがない作者IDを取得
     * ※ delete_plugininfo 削除後の作者テーブルの最適化時に使用する
     * 
     * @return type
     */
    //SELECT a.id FROM wp_plugins_ranking_author AS a LEFT JOIN wp_plugins_ranking_base As p ON a.id = p.author_id WHERE p.author_id IS NULL;
    public function get_author_noplugin() {
        global $wpdb;
        $res = $wpdb->get_results("SELECT a.id FROM $this->authors_table AS a LEFT JOIN $this->plugins_table As p ON a.id = p.author_id WHERE p.author_id IS NULL");
        return $res;
    }
    
    /**
     * 作者テーブルから登録プラグインがない作者情報を削除
     * ※ delete_plugininfo 削除後の作者テーブルの最適化時に使用する
     *    先に SELECT で確認してから実行するように２段階の処理が必要
     * 
     * @global type $wpdb
     * @return type
     */
    //DELATE wp_plugins_ranking_author FROM wp_plugins_ranking_author LEFT JOIN wp_plugins_ranking_base ON wp_plugins_ranking_author.id = wp_plugins_ranking_base.author_id WHERE wp_plugins_ranking_base.author_id IS NULL
    public function delete_author_noplugin() {
        global $wpdb;
        $res = $wpdb->get_results("DELETE $this->authors_table FROM $this->authors_table LEFT JOIN $this->plugins_table ON $this->authors_table.id = $this->plugins_table.author_id WHERE $this->plugins_table.author_id IS NULL");
        return $res;
    }

    /**
     * DB COUNT 処理が重いのでデータ収集後に事前にタイプ別の最大カウントを取得しておく
     */
    public function get_maxcount_types() {
		global $wpdb;
        
        //SQL 構築
        $acat = WP_plugin_ranking::get_opcategory_keyarray('key');
        $acat[] = 'etc';
        $acat[] = 'popular';
        $acat[] = 'japan';
        $now = new DateTime();
        $edate = $now->format('Y-m-d');
        $sdate = $now->modify("-2 year")->format('Y-m-d');
        $option = '';
        $maxcount = null;
        //種別
        foreach ($acat as $type){
            if($type === 'japan'){
                $option = "WHERE p.country = 'Japan' AND ";
            }
            elseif($type === 'popular'){
                $option = "WHERE ";
            }
            else {
                $option = "WHERE p.optag = '$type' AND ";
            }
            $option .= "p.last_updated BETWEEN '$sdate 00:00:00' AND '$edate 23:59:59' ";
            $count = $wpdb->get_var( "SELECT COUNT( p.id ) FROM $this->authors_table AS a INNER JOIN $this->plugins_table  As p ON a.id = p.author_id $option" );
            if($type === 'japan' || $type === 'popular'){
                $maxcount[$type] = $count;
            }
            elseif($count >= 100){
                $maxcount[$type] = 100;
            }
            else {
                $maxcount[$type] = $count;
            }
        }
        return $maxcount;
    }
    
    /**
     * 指定クエリー条件からプラグイン情報オブジェクトを生成する
     * 
     * @param type $query array('type'=>$type, 'order'=>$order, 'paged'=>$page)
     * @return type
     */
	public function get_pluginslist($query) {
		global $wpdb;
        $obj = new stdClass();
        $type = $query['type'];
        $order  = $query['order'];
        $paged  = (empty($query['paged']))? 1 : $query['paged'];
        //SQL 構築
        $acat = WP_plugin_ranking::get_opcategory_keyarray('key');
        $acat[] = 'etc';
        $now = new DateTime();
        $option = '';
        $index = '';
        //種別
        if(in_array($type, $acat)){
            $option = "WHERE p.optag = '$type' ";
            $index  = "cat_$order";
            $maxcount = 100;
        }
        elseif($type == 'newpickup'){
            $edate = $now->format('Y-m-d');
            $sdate = $now->modify("-3 month")->format('Y-m-d');
            $option = "WHERE p.added BETWEEN '$sdate 00:00:00' AND '$edate 23:59:59' ";
            $index  = "new";
            $maxcount = 100;
        }
        elseif($type == 'japan'){
            $option = "WHERE p.country = 'Japan' ";
            $index  = "japan";
            $maxcount = -1;
        }
        else{ //popular
            $option = "";
            $index  = "$order";
            $maxcount = -1;
        }
        //データ数取得
        $obj->query = $query;
        $obj->max_count = $maxcount;
        //カウント取得クエリーが重いので毎日のデータ取得後に事前に取得しておく
        $access = get_option('wp_plugin_ranking');
        if(empty($access['count'][$type])){
            $obj->max_count = ($maxcount !== -1)? $maxcount : 100; 
        }
        else {
            $obj->max_count = $access['count'][$type];
        }
        if($obj->max_count > 0){
            //並びとページング（１ページあたり20個）
            $maxcount = (int)$obj->max_count;
            $limit = '';
            if($order == 'download'){
                $limit .= "ORDER BY p.downloaded DESC ";
            }
            elseif($order == 'review'){
                $limit .= "ORDER BY p.num_ratings DESC ";
            }
            elseif($order == 'updated'){
                $limit .= "ORDER BY p.last_updated DESC ";
            }
            $offset = $paged * 20 - 20;
            $limit .= "LIMIT $offset, 20 ";
            $obj->pages = ($maxcount % 20 == 0)? $maxcount / 20 : (int)($maxcount / 20) + 1;
            $obj->paged = ($obj->pages >= $paged)? $paged : $obj->pages;
            //$obj->plugins = $wpdb->get_results( "SELECT * FROM $this->authors_table AS a INNER JOIN $this->plugins_table  As p ON a.id = p.author_id $option  $limit");
            // SELECT * FROM wp_plugins_ranking_author AS a INNER JOIN wp_plugins_ranking_base As p USE INDEX (pop_review) ON a.id = p.author_id ORDER BY p.num_ratings DESC, p.added DESC LIMIT 0, 20
            $obj->plugins = $wpdb->get_results( "SELECT * FROM $this->authors_table AS a INNER JOIN $this->plugins_table  As p USE INDEX ($index) ON a.id = p.author_id $option  $limit");
        }
        return $obj;
	}
}

 

以上

今回は、テーブルの新規作成からデータの登録、更新処理までを紹介しました (^^)

ちなみに下記ページで人気プラグイン等をランキング形式で表示出来ますので、試してみてください

次回はリレーションについて紹介したいと思います