LoginSignup
1
0

More than 5 years have passed since last update.

MonacaAdvent Calendar 2017Day 9
@miz21358(miz21358)

[monaca][onsenui][AngularJS]Web SQL Database のカスタマイズ

Last updated at Posted at 2017-12-08

はじめに

公式にも「Web SQL Database」の扱い方がさらっと書いてありますが、実際のアプリに適用するにはちょっと……うーん。
＃Monacaに限らず、DB系のサンプルは大抵そうですけどねｗ

「DBを触るところはできるだけ隠ぺいしたい」
「バージョン管理したいよね」
「クエリを各TBLごとに書くのめんどくさい」

……という訳で、何とかしてみました。

特徴

  • SELECT, INSERT等、基本的なクエリは、テーブルクラス定義のみで処理OK
  • DB定義もテーブルクラス宣言のみで基本的にはOK
  • DB処理は非同期
  • バージョン管理可能
  • DB関連スクリプト は、ライブラリ依存なし。（単体でも使えます）

ベース

[monaca][onsenui][AngularJS][ui-router]onsenui/ui-router最小限プロジェクト

html

index.html
<!DOCTYPE HTML>
<html>
<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1, user-scalable=no">
  <meta http-equiv="Content-Security-Policy" content="default-src * data: gap: https://ssl.gstatic.com; style-src * 'unsafe-inline'; script-src * 'unsafe-inline' 'unsafe-eval'">
  <script src="components/loader.js"></script>
  <script src="js/script.js"></script>
  <script src="js/db.js"></script>
  <script src="js/dbTable.js"></script>

  <link rel="stylesheet" href="components/loader.css">
  <link rel="stylesheet" href="css/style.css">
</head>
<body ng-app="MyApp">

    <ons-page>
        <ons-toolbar>
            <div class="center">DB</div>
        </ons-toolbar>

        <div style="margin: 10px;">
            <ons-button ui-sref="person">人物</ons-button>
            <ons-button ui-sref="url">URL</ons-button>
        </div>

        <div style="padding: 20px 10px;" ui-view>
            <h1>使い方</h1>
            <ul>
                <li>各画面では、初期処理として、全件データ検索を行います。</li>
                <li>画面上部の入力フォームにデータを入力して…
                    <ul>
                        <li>「登録」ボタンを押下すると、新規登録（INSERT）されます。</li>
                        <li>「検索」ボタンを押下すると、条件付き検索されます。</li>
                    </ul>
                </li></li>
                <li>検索結果の項目を押下すると、入力フォームに値が設定されます。
                    <ul>
                        <li>値を変更し、「登録」ボタンを押下すると、更新（UPDATE）されます。</li>
                        <li>「削除」ボタンを押下すると、削除（DELETE）されます。</li>
                    </ul>
                </li>
            </ul>

        </div>

    </ons-page>

    <ons-template id="person.html">
        <div ng-controller="PersonController">
            <ons-input type="text" ng-model="person_sei" modifier="material" float placeholder="姓"></ons-input>
            <ons-input type="text" ng-model="person_mei" modifier="material" float placeholder="名"></ons-input>
            <ons-input type="date" ng-model="person_birthday" modifier="material" float placeholder="生年月日"></ons-input>
            <ons-select ng-model="person_sex" modifier="material" >
                <option value="1">男性</option>
                <option value="2">女性</option>
                <option value="0">どちらでもない</option>
            </ons-select>
            <ons-button ng-click="save();">登録</ons-button>
            <ons-button ng-click="delete();">削除</ons-button>
            <ons-button ng-click="search();">検索</ons-button>
            [{{person_id}}]

            <ul>
                <li ng-repeat="r in results" ng-click="modify(r);">
                    {{r.col_id}}: {{r.col_sei}}, {{r.col_mei}}, {{r.col_birthday}}, {{r.col_sex}}
                </li>
            </ul>

        </div>
    </ons-template>

    <ons-template id="url.html">
        <div ng-controller="UrlController">
            <ons-input type="text" ng-model="url_name" modifier="material" float placeholder="名称"></ons-input>
            <ons-input type="url" ng-model="url_url" modifier="material" float placeholder="URL"></ons-input>
            <ons-button ng-click="save();">登録</ons-button>
            <ons-button ng-click="delete();">削除</ons-button>
            <ons-button ng-click="search();">検索</ons-button>
            [{{url_id}}]

            <ul>
                <li ng-repeat="r in results" ng-click="modify(r);">
                    {{r.col_id}}: {{r.col_name}}, {{r.col_url}}
                </li>
            </ul>

        </div>
    </ons-template>

</body> 
</html>

javascript

画面コントロール用

script.js
var myApp = angular.module('MyApp', ['onsen', 'ui.router']);

myApp.config(['$stateProvider', '$urlRouterProvider', function($stateProvider, $urlRouterProvider) {

        $stateProvider
            .state('person', {
                url: '/person',
                templateUrl: 'person.html'
            })
            .state('url', {
                url: '/url',
                templateUrl: 'url.html'
            })

    }]);

myApp.controller('PersonController', ['$scope', 'MyDBService', function($scope, MyDBService) {

    MyDBService.test();

    loadList();

    // リスト読込処理
    function loadList() {
        // リスト取得
        MyDBService.connect().then(
            function(db) {
                // PERSONテーブルを全件検索
                return MyDBService.select(db, new PersonTableCls());
            }
        ).then(
            function(resultList) {
                // 検索結果を画面に設定する
                $scope.results = resultList;

                // 一旦初期化
                $scope.person_id = "";
                $scope.person_sei = "";
                $scope.person_mei = "";
                $scope.person_birthday = "";
                $scope.person_sex = "";
            }
        );
    };

    // 保存ボタン押下時処理
    $scope.save = function() {
        var data = new PersonTableCls($scope.person_id, $scope.person_sei, $scope.person_mei, $scope.person_birthday, $scope.person_sex);
        console.log(data);

        // 登録実行
        MyDBService.connect().then(
            function(db) {
                return MyDBService.save(db, data);
            }
        ).then(
            function(result) {
                console.log("result = "+ result);
                if (result) {
                    // 保存成功したら、リスト再読み込み
                    loadList();
                }
                else {
                    // 保存失敗
                    ons.notification.alert("保存失敗");
                }
            }
        );
    }

    // 削除ボタン押下時処理
    $scope.delete = function() {
        var data = new PersonTableCls($scope.person_id, $scope.person_sei, $scope.person_mei, $scope.person_birthday, $scope.person_sex);
        console.log(data);

        // 登録実行
        MyDBService.connect().then(
            function(db) {
                return MyDBService.delete(db, data);
            }
        ).then(
            function(result) {
                console.log("result = "+ result);
                if (result) {
                    // 削除成功したら、リスト再読み込み
                    loadList();
                }
                else {
                    // 削除失敗
                    ons.notification.alert("保存失敗");
                }
            }
        );
    }

    $scope.search = function() {
        var condition = new PersonTableCls(null, $scope.person_sei, $scope.person_mei, $scope.person_birthday, $scope.person_sex);
        console.log(condition);

        // リスト取得
        MyDBService.connect().then(
            function(db) {
                // PERSONテーブルを条件指定検索
                return MyDBService.select(db, condition);
            }
        ).then(
            function(resultList) {
                // 検索結果を画面に設定する
                $scope.results = resultList;

                // 一旦初期化
                $scope.person_id = "";
                $scope.person_sei = "";
                $scope.person_mei = "";
                $scope.person_birthday = "";
                $scope.person_sex = "";
            }
        );      
    }

    // リスト項目押下時
    $scope.modify = function(r) {
        // 入力フォームに値設定
        $scope.person_id = r.col_id;
        $scope.person_sei = r.col_sei;
        $scope.person_mei = r.col_mei;
        $scope.person_birthday = r.col_birthday;
        $scope.person_sex = r.col_sex;
    }

}]);




myApp.controller('UrlController', ['$scope', 'MyDBService', function($scope, MyDBService) {

    MyDBService.test();

    loadList();

    // リスト読込処理
    function loadList() {
        // リスト取得
        MyDBService.connect().then(
            function(db) {
                // URLテーブルを全件検索
                return MyDBService.select(db, new UrlTableCls());
            }
        ).then(
            function(resultList) {
                // 検索結果を画面に設定する
                $scope.results = resultList;

                // 一旦初期化
                $scope.url_id = "";
                $scope.url_name = "";
                $scope.url_url = "";
            }
        );
    };

    // 保存ボタン押下時処理
    $scope.save = function() {
        var data = new UrlTableCls($scope.url_id, $scope.url_name, $scope.url_url);
        console.log(data);

        // 登録実行
        MyDBService.connect().then(
            function(db) {
                return MyDBService.save(db, data);
            }
        ).then(
            function(result) {
                console.log("result = "+ result);
                if (result) {
                    // 保存成功したら、リスト再読み込み
                    loadList();
                }
                else {
                    // 保存失敗
                    ons.notification.alert("保存失敗");
                }
            }
        );
    }

    // 削除ボタン押下時処理
    $scope.delete = function() {
        var data = new UrlTableCls($scope.url_id, $scope.url_name, $scope.url_url);
        console.log(data);

        // 登録実行
        MyDBService.connect().then(
            function(db) {
                return MyDBService.delete(db, data);
            }
        ).then(
            function(result) {
                console.log("result = "+ result);
                if (result) {
                    // 削除成功したら、リスト再読み込み
                    loadList();
                }
                else {
                    // 削除失敗
                    ons.notification.alert("保存失敗");
                }
            }
        );
    }

    $scope.search = function() {
        var condition = new UrlTableCls(null, $scope.url_name, $scope.url_url);
        console.log(condition);

        // リスト取得
        MyDBService.connect().then(
            function(db) {
                // URLテーブルを条件指定検索
                return MyDBService.select(db, condition);
            }
        ).then(
            function(resultList) {
                // 検索結果を画面に設定する
                $scope.results = resultList;

                // 一旦初期化
                $scope.url_id = "";
                $scope.url_name = "";
                $scope.url_url = "";
            }
        );      
    }

    // リスト項目押下時
    $scope.modify = function(r) {
        // 入力フォームに値設定
        $scope.url_id = r.col_id;
        $scope.url_name = r.col_name;
        $scope.url_url = r.col_url;
    }

}]);

DB操作用

db.js
myApp.service('MyDBService', ['$q', '$timeout', function($q, $timeout){
    // サービスで取り扱うテーブル一覧(クラスで管理)
    var TABLES = [
            new PersonTableCls(),
            new UrlTableCls()
        ];
    // 初期化時に登録するデータ
    var INITIALIZE = [
            new PersonTableCls(null, "山田", "花子", "2000-12-25", 2),
            new UrlTableCls(null, "モナカプレス", "https://press.monaca.io/")
        ];

    // テストメソッド
    this.test = function() {
        for (var tbl of TABLES) {
            // テーブルクラス
            console.log(tbl);

            // カラムリスト
            console.log(tbl.getColumns());

            // IDを除いたカラムリスト
            console.log(tbl.getColumnsIgnoreId());
        }

        for (var data of INITIALIZE) {
            console.log(data);

            // 値リスト
            console.log(data.getValues());

            // IDを除いた値リスト
            console.log(data.getValuesIgnoreId());
        }       
    }

    // DBへの接続
    // DBに何らかの処理を行う際には、必ずこれを通す
    this.connect = function () {
        var deferred = $q.defer();

        $timeout(function(){
            console.log('Start connect');
            var db = window.openDatabase("MyDatabase", "", "My Database", 1024 * 1024);

            // バージョン管理付DB
            var M = new Migrator(db);
            // ver.1 (初回生成)
            M.migration(1, function(tx){
                console.log("create db ver.1");
                // テーブル作成 ---------------

                for (var id in TABLES) {
                    // リストにあるテーブルを作成していく
                    var tbl = TABLES[id];

                    // 一旦既存のがあったら削除する
                    var sql = 'DROP TABLE IF EXISTS '+ tbl.table_name;
                    console.log(sql);
                    tx.executeSql(sql);

                    // 改めてテーブルを作成する
                    sql = 'CREATE TABLE IF NOT EXISTS '+ tbl.table_name +' (col_id integer primary key autoincrement, '+ tbl.getColumnsIgnoreId().join(',') +')';
                    console.log(sql);
                    tx.executeSql(sql);
                }

                for (var data of INITIALIZE) {
                    console.log(data);

                    var params = data.getValuesIgnoreId();

                    // 値の数だけqueryを生成しておく
                    var query = [];
                    for (var p of params) {
                        query.push("?");
                    }

                    // 初期データを投入する
                    var sql = 'INSERT INTO '+ data.table_name +' ('+ data.getColumnsIgnoreId().join(',') +') VALUES ('+ query.join(',') +')';
                    console.log(sql);
                    tx.executeSql(sql, params);
                }
            });

            M.doIt();            

            console.log('End connect');
            deferred.resolve(db);
        }, 0);

        return deferred.promise;
    };

    // 検索条件を元にデータを返す
    // db: conncectで取得したdbクラス
    // condtion: BaseTableClsのサブクラス
    this.select = function(db, condition) {
        var deferred = $q.defer();

        var resultList = [];

        $timeout(function(){
            db.readTransaction(
                function(tx){
                    console.log("select 開始 TBL="+ condition.table_name);

                    var sql = 'SELECT * FROM '+ condition.table_name;
                    var columns = [];
                    var values = [];

                    // 条件オブジェクトで値が入っているものを条件として加える
                    for (var p in condition) {
                        if (p.toLowerCase().indexOf("col_") == 0 && condition[p]) {
                            columns.push(p);
                            values.push(condition[p]);
                        }
                    }

                    var whereSql = "";
                    if ( columns.length > 0 ) {
                        // 条件があった場合、WHERE句追加
                        whereSql = " WHERE ";
                        for (var i = 0; i < columns.length; i++) {
                            if (i > 0) {
                                // 1個以上の場合、AND追加
                                whereSql += " AND ";
                            }
                            whereSql += columns[i] + " = ?";
                        }

                        console.log(whereSql);
                    }

                    sql += whereSql;

                    console.log(sql);

                    tx.executeSql(sql , values
                        , function(tx, rs){
                            // 成功時
                            for (var i = 0; i < rs.rows.length; i++) {
                                var row = rs.rows.item(i);
                                // console.log(row);

                                // 検索条件のコンストラクタからオブジェクト再生成
                                var Const = condition.constructor;
                                var data = new Const();

                                // 検索結果をオブジェクトに設定
                                for (var r in row) {
                                    data[r] = row[r];
                                }

                                console.log(data); 

                                resultList.push(data);
                            }                           

                            console.log("resultList.length: "+ resultList.length);
                            deferred.resolve(resultList);
                        }, function(tx, err) {
                            // SELECT失敗時
                            console.error("select 失敗: TBL="+ condition.table_name);
                            console.error(err);
                            deferred.resolve(null);
                        });
                }, 
                function(err){
                    // トランザクション失敗時
                    console.error("select TRANSACTION 失敗: TBL="+ condition.table_name);
                    console.error(err);
                }, 
                function(){
                    // トランザクション成功時
                    console.log("select TRANSACTION 成功: TBL="+ condition.table_name);
                }
            );
        }, 0);

        return deferred.promise;
    }

    // IDを元に、追加または更新を実行する
    // db: conncectで取得したdbクラス
    // data: BaseTableClsのサブクラス
    this.save = function(db, data) {
        var deferred = $q.defer();

        $timeout(function(){
            db.transaction(
                function(tx){
                    console.log("save 開始 TBL="+ data.table_name);

                    var params = data.getValuesIgnoreId();

                    var sql = "";
                    if (data.col_id) {
                        // IDがある場合、更新
                        var columns = data.getColumnsIgnoreId();
                        var sets = [];

                        for (var c of columns) {
                            sets.push(c +" = ? ");
                        }

                        sql = 'UPDATE '+ data.table_name +' SET '+ sets.join(',') + " WHERE col_id = "+ data.col_id;
                    }
                    else {
                        // IDがない場合、追加

                        // 値の数だけqueryを生成しておく
                        var query = [];
                        for (var p of params) {
                            query.push("?");
                        }

                        sql = 'INSERT INTO '+ data.table_name +' ('+ data.getColumnsIgnoreId().join(',') +') VALUES ('+ query.join(',') +')';
                    }

                    console.log(sql);
                    console.log(params);                        

                    tx.executeSql(sql, params
                        , function(tx, rs){
                            // 成功時
                            console.log("save executeSql 成功: TBL="+ data.table_name);
                            deferred.resolve(true);
                        }, function(tx, err) {
                            // 失敗時
                            console.error("save executeSql 失敗: TBL="+ data.table_name);
                            console.error(err);
                        });
                }, 
                function(err){
                    // 失敗時
                    console.error("save TRANSACTION 失敗: TBL="+ data.table_name);
                    console.error(err);
                }, 
                function(){
                    // 成功時
                    console.log("save TRANSACTION 成功: TBL="+ data.table_name);
                }
            );
        }, 0);

        return deferred.promise;
    }

    // IDを元に、削除を実行する
    // db: conncectで取得したdbクラス
    // data: BaseTableClsのサブクラス
    this.delete = function(db, data) {
        var deferred = $q.defer();

        $timeout(function(){
            db.transaction(
                function(tx){
                    console.log("delete 開始 TBL="+ data.table_name);

                    var sql = 'DELETE FROM '+ data.table_name + " WHERE col_id = ?";
                    console.log(sql);

                    // IDのみを引数とし、SQL実行
                    tx.executeSql(sql, [data.col_id]
                        , function(tx, rs){
                            // 成功時
                            console.log("delete executeSql 成功: TBL="+ data.table_name);
                            deferred.resolve(true);
                        }, function(tx, err) {
                            // 失敗時
                            console.error("delete executeSql 失敗: TBL="+ data.table_name);
                            console.error(err);
                        });
                }, 
                function(err){
                    // 失敗時
                    console.error("delete TRANSACTION 失敗: TBL="+ data.table_name);
                    console.error(err);
                }, 
                function(){
                    // 成功時
                    console.log("delete TRANSACTION 成功: TBL="+ data.table_name);
                }
            );
        }, 0);

        return deferred.promise;
    }

}]);

// DBバージョン管理クラス
// 参考: http://blog.maxaller.name/2010/03/html5-web-sql-database-intro-to-versioning-and-migrations/
function Migrator(db){
    var migrations = [];
    this.migration = function(number, func){
        migrations[number] = func;
    };
    var doMigration = function(number){
        if(migrations[number]){
            db.changeVersion(db.version, String(number), function(t){
                migrations[number](t);
                }, function(err){
                    if(console.error) console.error("Error!: %o", err);
                }, function(){
                    doMigration(number+1);
                });
        }
    };
    this.doIt = function(){
        var initialVersion = parseInt(db.version) || 0;
        try {
            doMigration(initialVersion+1);
        } catch(e) {
            if(console.error) console.error(e);
        }
    }
}

参考）HTML5 Web SQL Database – Intro to Versioning and Migrations « occasionally useful

DBのテーブル管理用

dbTable.js
// テーブル：人物情報
var PersonTableCls = (function () {
    // コンストラクタ
    function PersonTableCls(id, sei, mei, birthday, sex) {
        // 親クラスのコンストラクタ呼び出し
        BaseTableCls.call(this, "PERSON_TBL", id);

        // カラム：姓
        this.col_sei = sei;

        // カラム：名
        this.col_mei = mei;

        // カラム：生年月日
        this.col_birthday = birthday;

        // カラム：性別
        this.col_sex = sex;
    }

    // configure prototype
    PersonTableCls.prototype = new BaseTableCls();
    PersonTableCls.prototype.constructor = PersonTableCls;

    return PersonTableCls; // return constructor
})();


var UrlTableCls = (function () {
    // コンストラクタ
    function UrlTableCls(id, name, url) {
        // 親クラスのコンストラクタ呼び出し
        BaseTableCls.call(this, "URL_TBL", id);

        // サブクラス自身のプロパティ
        // カラム：サイト名称
        this.col_name = name;

        // カラム：サイトURL
        this.col_url = url;
    }

    // configure prototype
    UrlTableCls.prototype = new BaseTableCls();
    UrlTableCls.prototype.constructor = UrlTableCls;

    return UrlTableCls; // return constructor
})();


function BaseTableCls(tableName, id) {
    this.table_name = tableName;

    // ID (オートインクリメント用)
    this.col_id = id;

    // カラムリストを返す
    this.getColumns = function() {
        var columns = [];

        // 自身の中で"col_"から始まるのだけ抽出する
        for (var p in this) {
            if (p.toLowerCase().indexOf("col_") == 0) {
                columns.push(p);
            }
        }

        return columns;
    }

    // IDを除いたカラムリストを返す
    this.getColumnsIgnoreId = function() {
        var columns = [];

        // 自身の中で"col_"から始まるのだけ抽出する
        // ただしIDは除く
        for (var p in this) {
            if (p.toLowerCase().indexOf("col_") == 0 && p.toLowerCase() != "col_id") {
                columns.push(p);
            }
        }

        return columns;
    }

    // 値リストを返す
    this.getValues = function() {
        var values = [];

        // 自身の中で"col_"から始まるのだけ抽出する
        for (var p in this) {
            if (p.toLowerCase().indexOf("col_") == 0) {
                values.push(this[p]);
            }
        }

        return values;
    }

    // IDを除いた値リストを返す
    this.getValuesIgnoreId = function() {
        var values = [];

        // 自身の中で"col_"から始まるのだけ抽出する
        // ただしIDは除く
        for (var p in this) {
            if (p.toLowerCase().indexOf("col_") == 0 && p.toLowerCase() != "col_id") {
                values.push(this[p]);
            }
        }

        return values;
    }

}

参考）JavaScriptのサブクラス定義チートシート - Qiita

CSS

style.css
ons-input, ons-select {
    display: block;
    margin-top: 1.5em;
    margin-bottom: 1em;
}

解説

…と言っても、特徴でほとんど書いちゃったから書く事ないんですがｗ
prototype やら constructor を分からないなりに駆使してみました。

BaseTableCls を継承したサブクラスで、テーブル定義を行います。
カラム名は、 col_ で始める、というのがサブクラスのルールです。

コントローラ側では、テーブル定義サブクラスを扱うだけで、DBの細かいところを意識する必要はありません。
非同期処理もコントローラ側ではできるだけシンプルにしたつもりです。

db.js では、各TBLの細かいカラム構成などはすべて吸収しています。
今回は、ver.1の場合のみを定義していますが、バージョンアップする場合には、M.migration の処理をver.1の後に記載してください。
（詳細は、参考URLを参照してください）

ソースコード

最後に

初めてアドベントカレンダーなるものに参加させていただきました。
来年もぼちぼち色んなことにチャレンジできたらいいな、と思っています。
メリークリスマス＆よいお年を！

1
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
1
0