jQueryのプラグインテーブル(jTableなど)とSQLite3+PHP連携

今、電子工作が今マイブームである。首都圏ではないので、なかなか気軽に部品が買えない・揃わない。(ネットが楽だが、送料がイタイのでまとめ買いしたい)また、たくさん部品が必要になるが、自分がストックしている部品がなにか把握できないので、間違って同じものを買ってしまったりする。また、型番だけではまだスペックが浮かばないのでいちいち各メーカサイトを回るのも正直しんどい。

そこで今ストックしている部品の在庫管理とスペック検索を自作サーバー上で稼動させたいと考えている。MySQLとPHP・・という流れが一般的かもしれないし、別の目的ですでに稼動しているが、管理データの機密性はないので、はもっと気楽にデータ管理したい。いろいろ検討した結果、jQueryのTableプラグインをGUIとして、CGIはPHP、DBはSQLite3でやってみることにした。(以前の投稿はその伏線である)

jQueryのテーブルはいろいろあって見た目もGoodだが、参照用途に特化したもの、入力に特化したものにわかれていたので、今回は入力用の管理テーブルと参照用のテーブルに分けることにした。入力用はjTable、参照用はDataTablesが良かったので採用。

jTableのサイト jtable.org
DataTablesのサイト datatables.net

まずは入力用のjTableをつかったアプリの開発からはじめる。jTableがサポートしているデータタイプをもとにDBテーブルを作成する。凝ったことはできないが、一般的なことは大抵できる。

PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE `sampleTbl2` (
    `Id`    INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
    `TextValue` TEXT,
    `NumberValue`   NUMERIC NOT NULL,
    `DateValue` TEXT DEFAULT (datetime('now','localtime')),
    `ComboList` INTEGER NOT NULL,
    `RadioButton`   TEXT NOT NULL,
    `CheckBox`  TEXT NOT NULL DEFAULT 1,
    `Comment`  BLOB
);
INSERT INTO "sampleTbl2" VALUES(1,'aaa',123,'2014.07.12 00:01:22',1,'A','1','xxx yyy');
INSERT INTO "sampleTbl2" VALUES(2,'bbb',10.1,'2014.07.13 12:22:13',2,'B','1','');
INSERT INTO "sampleTbl2" VALUES(3,'ccc',0.000,'2014-07-17 17:38:39',2,'C','2','zzz');
DELETE FROM sqlite_sequence;
INSERT INTO "sqlite_sequence" VALUES('sampleTbl2',5);
COMMIT;
INSERT INTO sampleTbl2
(TextValue, NumberValue, ComboList, RadioButton, CheckBox, Comment) 
VALUES ('bbb','999.99',1,'B','1','');
UPDATE sampleTbl2
SET TextValue = 'abc'
,NumberValue = 1234.5678
,ComboList = 1
,RadioButton ='A'
,CheckBox = 1
,Comment =''
WHERE id=1;
Delete From sampleTbl2
WHERE Id= 1;

サンプルデータをSQLite Database Browserで表示したところ
sampleTbl2

HTMLのポイント
jtable.jsとthemeのcssを指定する

<script type="text/javascript" src="/pathto/jquery.jtable.js"></script>
<link type="text/css" href="/pathto/jtable.min.css" rel="stylesheet">

ドキュメントロード時にテーブルのデータを定義する。ここで表示するカラムとデータ入力パネルの表示形式を指定する。また、actionsではそれぞれのデータ処理時にコールするCGIを指定する。jTableでは$_GET$_POSTで画面データがサーバに送信される。最後にjtable(‘load’)を実行して、画面を更新する。

※注意点として、checkboxタイプだけなぜかチェックOFFだとPOST送信されない不具合?がある。送信されなくても適切な値がセットされるようにチェックボックス値に対応するカラムにDEFAULT値を設定しておいたほうが良い。

<script type="text/javascript">
$(function()
{
    $('#sampleTbl2').jtable({
 
    title: 'Sample Table',
    paging: true, //Enable paging
    pageSize: 10, //Set page size (default: 10)
    sorting: true, //Enable sorting
    defaultSorting: 'Id ASC', //Set default sorting
    actions: {
        listAction: 'sample2.php?action=list',
        createAction: 'sample2.php?action=create',
        updateAction: 'sample2.php?action=update',
        deleteAction: 'sample2.php?action=delete'
    },
    fields: {
        Id: {
            title: 'Id',
            key: true,
            create: false,
            edit: false,
            list: true
        },
        TextValue: {
            title: 'Text',
            width: '10%'
        },
        NumberValue: {
            title: 'Number',
            width: '10%'
        },
        DateValue:{
            title: 'Date',
            width: '15%',
            type: 'date',
            displayFormat: 'yy.mm.dd',
            create: false,
            edit: false,
            sorting: false
        },
        ComboList:{
            title : 'Combo',
            width : '15%',
            options: { '1': 'XXX', '2': 'YYY' }
        },
        RadioButton:{
            title : 'Radio',
            width : '15%',
            type: 'radiobutton',
            options: { 'A': 'AAA', 
                   'B': 'BBB', 
                   'C': 'CCC' },
            defaultValue: 'A'
        },
        CheckBox: {
            title : 'Check',
            width: '15%',
            type: 'checkbox',
            values: { '1': 'No', '2': 'Yes' },
            defaultValue: 'false'
        },
        Comment:{
            title: 'Text Area',
            width : '20%',
            type: 'textarea',
            list: true
        }
    }
     
    });
 
    $('#sampleTbl2').jtable('load');
});
</script>

HTMLのBodyにはテーブルを表示するDivを追加するだけ。

<div id="sampleTbl2" style="width:1000px;"></div>

とりあえず動作するPHPソースは以下のとおり。jTableの仕様上、javascript側でデータタイプやカラムを定義する必要があるので、なるべくサーバサイド側のPHPでは汎用的に処理したい。SQLのカラム名などはPOSTの連想配列のキーから動的にSQLを作成するようにしている。ただ、キーとなるIDだけはどうにもならんので、プログラムの最初で定義することにしている。

PHPのログを除くとこんな感じになっている。

<!--?php
    include_once( "../lib/class.sqliteex.php" );
 
    function createColumnsAndBindKeys( $iarray, &$ocolumns, &$okeys )
    {
        $ocolumns = "";
        $okeys = "";
 
        $keys = array_keys( $iarray );
        foreach( $keys as $key )
        {
            if( $ocolumns != "" )
            {
                $ocolumns .= ",";
                $okeys .= ",";
            }
            $ocolumns .= $key;
            $okeys .= ":";
            $okeys .= $key;
        }
        return;
    }
    function createUpdateColumns( $iarray, &$ocolumns )
    {
        $ocolumns = "";
        $keys = array_keys( $iarray );
        foreach( $keys as $key )
        {
            if( $ocolumns != "" )
            {
                $ocolumns .= ",";   
            }
            $ocolumns .= sprintf( "%s = :%s", $key, $key );
        }
        return;
    }
 
    if( isset($_GET) )
    {
        error_log("[_GET]");
        error_log(print_r($_GET,true));
    }
    if( isset($_POST) )
    {
        error_log("[_POST]");
        error_log(print_r($_POST,true));
    }
 
    // DBテーブル情報
    $tbl = "sampleTbl2";
    $db = "sample2.db";
    $primaryKey = "Id";
 
    $sqex = new SqLiteEx( $db, SQLITE3_OPEN_READWRITE );
    $result = null;
    $count = 0;
    $jTableResult = array();
    try
    {
        $sqex--->dbopen();
 
        if($_GET["action"] == "list")
        {
            $jTableResult['TotalRecordCount'] = $sqex->querySingle( "select count(*) from ".$tbl, false );
 
            $result = $sqex->query( 
                sprintf( "select * from %s order by %s limit %d , %d",
                    $tbl, $_GET["jtSorting"], $_GET["jtStartIndex"], $_GET["jtPageSize"] )
                );
             
            $jTableResult['Result'] = "OK";
            $jTableResult['Records'] = $sqex->fetchArray( $result );
        }
        else if($_GET["action"] == "create")
        {
            $ocolumns = "";
            $okeys = "";
            createColumnsAndBindKeys( $_POST, $ocolumns, $okeys );
            //error_log("ocolumns : " . $ocolumns );
            //error_log("okeys : " . $okeys );
 
            $sqex->begin();
            $istmt = $sqex->prepare( 
                sprintf( "insert into %s ( %s ) values( %s )", $tbl, $ocolumns, $okeys )
                );
 
            foreach( $_POST as $column => $value )
            {
                $sqex->prepareBindValue( $istmt, ":".$column, $value, SQLITE3_TEXT );
            }
 
            $sqex->prepareExecute( $istmt );
            $sqex->commit();
 
            $result = $sqex->query( 
                sprintf( "select * from %s where %s = last_insert_rowid()", $tbl, $primaryKey )
                );
             
            $jTableResult['Result'] = "OK";
            $jTableResult['Record'] = $sqex->fetchArray( $result );
 
        }
        else if($_GET["action"] == "update")
        {
            $ocolumns = "";
            createUpdateColumns( $_POST, $ocolumns );
 
            $sqex->begin();
            $istmt = $sqex->prepare( 
                sprintf(
                    "update %s set %s where %s = :%s",
                    $tbl, $ocolumns, $primaryKey, $primaryKey )
                );
 
            foreach( $_POST as $column => $value )
            {
                $sqex->prepareBindValue( $istmt, ":".$column, $value, SQLITE3_TEXT );
            }
 
            $sqex->prepareExecute( $istmt );
            $sqex->commit();
 
            //Return result to jTable
            $jTableResult = array();
            $jTableResult['Result'] = "OK";
             
        }
        else if($_GET["action"] == "delete")
        {
            $sqex->begin();
 
            $istmt = $sqex->exec( 
                sprintf( "delete from %s where %s = %s", $tbl, $primaryKey, $_POST[ $primaryKey ] )
                );
             
            $sqex->commit();
 
            //Return result to jTable
            $jTableResult = array();
            $jTableResult['Result'] = "OK";
        }
    }
    catch( Exception $e )
    {
        $jTableResult['Result'] = "ERROR";
        $jTableResult['TotalRecordCount'] = 0;
    }
     
    $sqex->dbclose();
 
    /*
    error_log("[jTableResult]");
    error_log(print_r($jTableResult,true));
    */
    print json_encode( $jTableResult );
 
?>

SQL処理に必要なデータが連想配列で送信される。わかりやすいつくりになっている。

[17-Jul-2014 14:36:58 Asia/Tokyo] [_GET]
[17-Jul-2014 14:36:58 Asia/Tokyo] Array
(
    [action] => list
    [jtStartIndex] => 0
    [jtPageSize] => 10
    [jtSorting] => Id ASC
)
[17-Jul-2014 14:36:58 Asia/Tokyo] [_POST]
[17-Jul-2014 14:36:58 Asia/Tokyo] Array
(
)
[17-Jul-2014 14:40:19 Asia/Tokyo] [_GET]
[17-Jul-2014 14:40:19 Asia/Tokyo] Array
(
    [action] => create
)
[17-Jul-2014 14:40:19 Asia/Tokyo] [_POST]
[17-Jul-2014 14:40:19 Asia/Tokyo] Array
(
    [TextValue] => xxxxxx
    [NumberValue] => 0.1
    [ComboList] => 1
    [RadioButton] => B
    [CheckBox] => true
    [Comment] => this is a pen.
)
[17-Jul-2014 14:41:22 Asia/Tokyo] [_GET]
[17-Jul-2014 14:41:22 Asia/Tokyo] Array
(
    [action] => update
)
[17-Jul-2014 14:41:22 Asia/Tokyo] [_POST]
[17-Jul-2014 14:41:22 Asia/Tokyo] Array
(
    [Id] => 1
    [TextValue] => abcd
    [NumberValue] => 1234.56789
    [ComboList] => 1
    [RadioButton] => B
    [CheckBox] => true
    [Comment] => xxxx
)
[17-Jul-2014 14:41:22 Asia/Tokyo] [_GET]
[17-Jul-2014 14:41:22 Asia/Tokyo] Array
(
    [action] => update
)
[17-Jul-2014 14:41:22 Asia/Tokyo] [_POST]
[17-Jul-2014 14:41:22 Asia/Tokyo] Array
(
    [Id] => 1
    [TextValue] => abcd
    [NumberValue] => 1234.56789
    [ComboList] => 1
    [RadioButton] => B
    [CheckBox] => true
    [Comment] => xxxx
)

javascript,php,DBがそれぞれうまく連携すれば以下のように表示される。

jtable_sample

新規追加、編集パネル

jtable_sample2

これで何とかデータ登録の画面ができそう。
次回はDataTablesで登録データの表示を試してみる。