WebSQLの代替としてSqlWebを使用する

iOS15からWebSQLが完全に廃止されたため、IndexedDBに置き換えることになった。(もっと早くやれという話だが)
しかし既存アプリに書かれているSQLをIndexedDBに置換するのは大変なため、IndexedDBでもSQLが使えるという「SqlWeb」というラッパーライブラリを採用することにした。
https://github.com/ujjwalguptaofficial/sqlweb
なおSqlWebは「JsStore」というライブラリの拡張機能である。
https://jsstore.net/tutorial/get-started/
以下にWebSQLでの処理をSqlWebに置き換えたメモである。名前が紛らわしい。

インストール

フレームワークを採用していないアプリのため、こちらのサンプルを基に実装する。
https://github.com/ujjwalguptaofficial/sqlweb/tree/master/examples/simple%20example
sqlweb.jsは2020年10月時点で最新版、JsStoreはv4のものにしたら動かなかったのでv3系の最新版をダウンロードした。

sqlweb V1.6.0
https://github.com/ujjwalguptaofficial/sqlweb/tree/1.6.0/dist

jsstore V3.13.6
https://github.com/ujjwalguptaofficial/JsStore/releases/tag/3.13.6

SQLの書き換え

WebSQL(SQLLite)と文法が違うのでSQLを書き換える必要があった。

• エイリアス(xxx AS A とか TABLE_A TA)は使えない

• 型指定はvarcharはstring、decimalはnumberなどになる https://github.com/ujjwalguptaofficial/sqlweb/wiki/Column
  これらはDDL書き換えが必要
  なおnumber型のカラムに「'10'」などで更新しようとするとエラーになるので注意。「10」ならOK

• 主キーに複合キーが使用できない。1つのみ指定可
  正確には複数指定してもエラーにはならないが、1つだけ有効になる。
  複合キーだったテーブルはAUTOINCREMENTのPRIMARYKEYを作って対応した

• JOINするテーブルに同じ名前のカラムがあるとエラーになる。ただしJOINの条件(ON句)ならOK
  なので主キーカラム名を一律「ID」などにすると駄目。「テーブル名_ID」とかにする。

• SELECT * しか指定できない

• パラメータ(WHERE hoge = ?)が使えない
  呼出元で置換する必要がある

• 関数や文字列結合が使えない
  SELECT * のみなので当然だが。IFNULLなどもないので、返ってきた結果を判定や編集を行う

• JOINのON句のイコールの前後にスペースが入れられない

// NG
Orders.CustomerID = Customers.CustomerID

// OK
Orders.CustomerID=Customers.CustomerID

これはJsStoreの書き方らしい

• IS NULL や IS NOT NULL は使えない

困ったときはQueryを使う

SqlWebは結局のところ、SQLをJsStoreに渡すためのパーサーみたいなものである。なのでJsStoreではできるはずのことが、SqlWebを介するとできないときがある。そんなときはQueryを使う。

var query = new connection.$sql.Query("DELETE FROM Student WHERE Id='@studentId'");

QueryオブジェクトはSQLをパースしてJsStoreが理解できる形式にしたもの(だと思う)。なのでこれを加工してやれば何とかなる。

一気にCREATE TABLEしたい

SqlWebで複数のテーブルを作成する方法がわからなかったが、JsStoreではできるようなので、

  var arrCreateSQL = ["DEFINE TABLE Student1(Id PRIMARYKEY AUTOINCREMENT, .....",
        "DEFINE TABLE Student2(Id PRIMARYKEY AUTOINCREMENT, ....."];

  var query = null;
  var tables = null;
  for (var i in arrCreateSQL) {
    // とりあえずQueryを生成
    query = new connection.$sql.Query(arrCreateSQL[i]);
    // Queryからtablesを取って集めておく
    if (tables === null) {
      tables = query.query_.data.tables;
    } else {
      tables = tables.concat(query.query_.data.tables);
    }
  }
  // 集めたtablesをセット
  query.query_.data.tables = tables;

  connection.$sql.run(query).then(function (isDbCreated) {
    // 更新処理など
  });

Queryの構造

  "query_": {
    "api": "initDb",
    "data": {
      "name": "dbName",
      "tables": [
        {
          "name": "Student1",
          "columns": {
            "ID": {
              "unique": false,
              "autoIncrement": true,
              "default": null,
              "notNull": false,
              "dataType": "number",
              "primaryKey": true,
              "multiEntry": false,
              "enableSearch": true
            },
            .....
        },
        {
          "name": "Student2",
          "columns": {
            "ID": {
            .....

こんな感じでQueryの中のtablesを入れ替えてしまう。無理やり感があるが、他にもっといい方法があるのか不明。

JOINすると何故かORDER BYが指定できない

select from Customers inner join Orders on Orders.CustomerID=Customers.CustomerID
order by Customers.CustomerID, Customers.Country

これがなぜかorder byの後に「.」があるみたいなシンタックスエラーになる。たぶんSqlWebのパーサーのバグだと思うが。。なのでこうする。

var sql = `select from Customers
inner join Orders on Orders.CustomerID=Customers.CustomerID
order by CustomerID, Country`;

var query = new connection.$sql.Query(sql);
var order = query.query_.data.order;
// orderを取り出してテーブル名を付けてやる
order[0].by = 'Customers.' + order[0].by;
order[1].by = 'Customers.' + order[1].by;

集計関数

JsStoreのaggregateを使う。

var sqlSelect = "SELECT TABLE_A";
sqlSelect += sqlWhere;
var query = new connection.$sql.Query(sqlSelect);
var aggregate = {};
aggregate.sum = 'ACCOUNTS';
aggregate.count = 'CD';
query.query_.data.aggregate = aggregate;

connection.$sql.run(query).then(function (result) {
    console.log(result[0]['sum(ACCOUNTS)']);
    console.log(result[0]['count(CD)']);

#トランザクションは？
複数のSQLが成功したらコミット、1つでも失敗したらロールバック、みたいなことはできなさそう。

画面遷移時

テーブルにデータを作成し、次の画面に遷移してからそのデータを使いたい場合はDBをオープンする必要がある。

// 遷移元
window.location.href = '遷移先';

// 遷移先
connection.$sql.run('OPENDB ' + dbName).then(function () {
	return;
});

ちなみにSqlWebと比較検討していた「Lovefield」は、SPA専用なのか明示的にDBをオープンする方法がなく（？）、画面遷移後にテーブルのレコードを読み込むことができなかった。

DBを削除したいとき

スキーマも含めて1から作り直したい場合。JsStoreにclearというメソッドが用意されているが、何故か上手く動かなかったので、indexedDBのAPIを使うことにした。

		var ks = indexedDB.deleteDatabase('KeyStore');
		ks.onsuccess = function (event) {
			var db = indexedDB.deleteDatabase(dbname);
			db.onsuccess = function (event) {
				// 後続処理
			}
			db.onerror = function () {
				throw new Error('db.onerror');
			}
		}
		ks.onerror = function () {
			throw new Error('ks.onerror');
		}

その他注意

オブジェクトリテラルでのスプレッド構文を使用しているため、iOS11.3未満では動かない。

参考記事

IndexedDBをSQLで操作可能なSqlWebというライブラリを使ってみた