読者です 読者をやめる 読者になる 読者になる

JSONデータとして取得したDBの検索結果をExtJSで表示するWebシステムのサンプル - Sinatra, Grails, CakePHP

DB の検索結果を JSON で返して、JavaScriptExtJS)で表示するような Web システムのサンプルを Sinatra, Grails, CakePHP の 3種類のフレームワークで作成してみました。

処理の概要は以下の通りで、MySQL の information_schema データベース TABLES テーブルに対する検索結果を ExtJS で表示します。

  • サーバー処理
    • (1) table_schema の一覧を JSON で返す(URL は informations/databases)
    • (2) 指定 table_schema に属するテーブル情報の一覧を JSON で返す(URL は informations/tables/
  • クライアント処理
    • (1) の結果をドロップダウンリストへ表示
    • ドロップダウンリストで table_schema を選択した際に (2) を取得
    • (2) の結果を GridPanel へ表示

なお、クライアント側は共通の HTML ファイルを使用するようにしましたが、JSON の日付フォーマットに違いがあったため、その部分だけが異なっています。

Sinatra の場合

今回は、JRuby を使用し、DB検索に Sequel、JSON 変換に ActiveSupport を使いました。
なお、Sequel はモデルクラスを使わずに SQL をそのまま実行するようにしています。

コントローラー table_list.rb
require "rubygems"
require "sinatra"
require "sequel"
require "mysql-connector-java-5.1.6-bin.jar"
require "active_support"

DB = Sequel.connect("jdbc:mysql://localhost/information_schema?user=root")

# (1) table_schema の一覧取得
get '/informations/databases' do
    DB["SELECT DISTINCT table_schema FROM tables ORDER BY table_schema"].to_json
end

# (2) 指定 table_schema に属する table の一覧取得
get '/informations/tables/:table_schema' do |t|
    sql = "SELECT table_name, table_type, engine, avg_row_length, create_time FROM tables WHERE table_schema=?"
    DB[sql, t].to_json
end

Web ページの実装は以下の通り。初期化時に取得した table_schema の一覧をドロップダウンリスト(Ext.form.ComboBox)に表示、table_schema を選択した際に、table 情報を取得して Ext.grid.GridPanel に表示します。

Webページ public/index.html
<!DOCTYPE html>
<html>
<head>
<link rel="stylesheet" type="text/css" href="ext-3.2.1/resources/css/ext-all.css" />
<script src="ext-3.2.1/adapter/ext/ext-base.js" type="text/javascript"></script>
<script src="ext-3.2.1/ext-all.js" type="text/javascript"></script>
<script type="text/javascript">
    Ext.onReady(function() {
        //JSONデータ(table 情報)の日付フォーマット
        var dformat = "Y/m/d G:i:s O";

        var dbStore = new Ext.data.JsonStore({
            url: "informations/databases",
            fields: ["table_schema"]
        });

        //table_schema の一覧を取得
        dbStore.load();

        //ドロップダウンリストの定義
        var dbList = new Ext.form.ComboBox({
            store: dbStore,
            displayField: "table_schema",
            mode: "local",
            autoWidth: true,
            triggerAction : 'all',
            editable: false,
            applyTo: "dbList"
        });

        var tableStore = new Ext.data.JsonStore({
            //proxy を自動設定するためにダミーのURLを設定
            url: "informations/tables/0",
            fields: [
                "table_name", 
                "table_type", 
                "engine", 
                {name: "avg_row_length", type: "int"}, 
                {name: "create_time", type: "date", dateFormat: dformat}
            ]
        });

        //table 情報を表示するための
        var grid = new Ext.grid.GridPanel({
            store: tableStore,
            columns: [
                {dataIndex: "table_name", header: "TABLE_NAME", sortable: true},
                {dataIndex: "table_type", header: "TABLE_TYPE", sortable: true},
                {dataIndex: "engine", header: "ENGINE", sortable: true},
                {dataIndex: "avg_row_length", header: "AVG_ROW_LENGTH", sortable: true, width: 130},
                {id: "create_time", dataIndex: "create_time", header: "CREATE_TIME", sortable: true, renderer: Ext.util.Format.dateRenderer('Y/m/d G:i:s')}
            ],
            width: 600,
            height: 200,
            applyTo: "tableList",
            autoExpandColumn: "create_time",
            stripeRows: true
        });

        dbList.on("select", function(combo, record, index) {
            //URL の変更(選択した table_schema を組み込んだ URL を設定)
            tableStore.proxy.setUrl("informations/tables/" + record.data["table_schema"]);
            //table 情報を取得
            tableStore.load();
        });
    });
</script>
</head>
<body>
<div>
    <input type="text" id="dbList" />
</div>
<div id="tableList"></div>
</body>
</html>

Grails の場合

Grails を使って上記と同様の処理を実装してみます。

まず、MySQL の information_schema に接続するための設定を実施します。

DB設定 grails-app/conf/DataSource.groovy
dataSource {
    pooled = true
    driverClassName = "com.mysql.jdbc.Driver"
}
hibernate {
    cache.use_second_level_cache = true
    cache.use_query_cache = true
    cache.provider_class = 'net.sf.ehcache.hibernate.EhCacheProvider'
    dialect = 'org.hibernate.dialect.MySQLDialect'
//  show_sql = true
}
// environment specific settings
environments {
    development {
        dataSource {
            url = "jdbc:mysql://localhost/information_schema?user=root"
        }
    }
    ・・・
}

次にドメインクラス(モデル)を定義します。ドメインクラスではクライアントの処理に合わせて DB のフィールド名をそのまま変数名(プロパティ名)に使いました。id が未設定の場合にエラーが発生したため、適当に設定しています。(今回の用途では使用しないので問題なし)

モデル grails-app/domain/fits/sample_mysql/Table.groovy
package fits.sample_mysql
import java.sql.Timestamp

class Table {
    static constraints = {
    }
    static mapping = {
        //テーブル名の設定
        table "tables"
        //id は適当に設定しておく
        id name: "table_name"
        version false
    }
    String table_schema
    String table_name
    String table_type
    String engine
    int avg_row_length
    Timestamp create_time
}

コントローラー側では、ドメインクラスを検索し、その結果を JSON 化します。
なお、Criteria を使い "SELECT DISTINCT table_schema FROM tables" が実行されるようにしています。
また、table 情報を取得するためのURL "informations/tables/" のマッピングに grails-app/conf/UrlMappings.groovy のデフォルト定義 "/$controller/$action?/$id?" を使っているため、params.id で table_schema の値を取得していますが、マッピング設定を追加して変更することも可能です。

コントローラー grails-app/controllers/fits/sample_mysql/InformationsController.groovy
package sample_mysql
import grails.converters.*

class InformationsController {
    // (1) table_schema の一覧取得
    def databases = {
        def c = Table.createCriteria()
        //table_schema を検索
        def result = c.list {
            projections {
                distinct("table_schema")
            }
            order("table_schema")
        }
        //table_schema をキーにした連想配列の配列に変換
        result = result.collect {
            [table_schema: it]
        }
        render result as JSON
    }

    // (2) 指定 table_schema に属する table の一覧取得
    //指定した table_schema の値は params.id で取得する
    def tables = {
        def result = Table.findAllByTable_schema(params.id)
        render result as JSON
    }
}

Web ページの実装は以下の通り。
ActiveSupport で作成した JSON とは日付のフォーマットが異なるために、dformat の値を変更しています。

Webページ web-app/index.html
<!DOCTYPE html>
<html>
・・・
<script type="text/javascript">
    Ext.onReady(function() {
        //JSONデータの日付フォーマット
        var dformat = "c";
        ・・・
    });
</script>
・・・
</html>

CakePHP の場合

最後に CakePHP で実装してみます。基本的な実装の仕方は Grails と同じです。

DB設定 app/config/database.php
<?php
class DATABASE_CONFIG {
    var $default = array(
        'driver' => 'mysql',
        'persistent' => false,
        'host' => 'localhost',
        'login' => 'root',
        'password' => '',
        'database' => 'information_schema',
        'prefix' => '',
    );
}

CakePHP のモデルクラスは検索結果を連想配列の配列で返すだけなので、モデルクラス内で table_schema 取得等の検索処理を実装しました。

モデル app/models/datasources/table.php
<?php
class Table extends AppModel {
    // (1) table_schema の一覧取得
    function findAllSchemas() {
        $list = $this->find("all", array(
            "fields" => "DISTINCT table_schema", 
            "order" => "table_schema"
        ));

        return $this->convertResult($list);
    }

    // (2) 指定 table_schema に属する table の一覧取得
    function findTables($tableSchema) {
        $list = $this->find("all", array(
            "conditions" => array(
                "table_schema" => $tableSchema
            ),
            "fields" => "table_name, table_type, engine, avg_row_length, create_time"
        ));

        return $this->convertResult($list);
    }

    /** 
     * 検索結果を変換する(モデル名をキーにした連想配列の部分を取り除く)
     *
     * 例.
     * <変換前>
     *  array(
     *    array("Table" => array("table_schema" => ・・・)),
     *    array("Table" => array("table_schema" => ・・・)),
     *    ・・・
     *  )
     *
     * <変換後>
     *  array(
     *    array("table_schema" => ・・・),
     *    array("table_schema" => ・・・),
     *    ・・・
     *  )
     */
    private function convertResult($findResult) {
        //無名関数内から外側スコープの $this にアクセスできないため変数に代入
        $outerThis = $this;

        //無名関数内で $outerThis 変数が使用できるように use 文で指定
        return array_map(function($l) use ($outerThis) {
            return $l[$outerThis->name];
        }, $findResult);
    }
}

コントローラーでは検索結果の JSON 化を実施します。

コントローラー app/controllers/informations_controller.php
<?php
class InformationsController extends AppController {
    //使用するモデルの定義
    var $uses = "Table";
    //View を使わないようにするための設定
    var $autoRender = false;

    // (1) table_schema の一覧取得(JSON化)
    function databases() {
        $list = $this->Table->findAllSchemas();
        return json_encode($list);
    }

    // (2) 指定 table_schema に属する table の一覧取得(JSON化)
    function tables($tableSchema) {
        $list = $this->Table->findTables($tableSchema);
        return json_encode($list);
    }
}

Web ページの実装は以下の通り。Grails のものと同じです。

Webページ app/webroot/index.html
<!DOCTYPE html>
<html>
・・・
<script type="text/javascript">
    Ext.onReady(function() {
        //JSONデータの日付フォーマット
        var dformat = "c";
        ・・・
    });
</script>
・・・
</html>