SQLiteデータベースの活用まとめ

ElectronやNode.jsなどで業務用アプリを作るとなると、割と避けられないのがデータベースの使用。サーバー型のMySQLやPostgreSQLを使うことになると思いますが、オフラインでの使用やスマフォ用となると、SQLiteがなんだかんだ行ってもベターです(NoSQLデータベースもありますが)。

今回は自分の為のメモとしてSQLiteを扱うアプリでのコードスニペットや知見として色々とまとめておこうと思います。(言語はNode.jsで操作をしています)

目次

今回使用するライブラリ等

DBの作成やビューの構成などは今回はDB Browser for SQLiteを利用して作成します。Windows,macOS,Linuxとマルチプラットフォームにアプリが用意されています。

データベースの作成

データベースの型

SQLiteは他のデータベースと異なり、けっこう型の扱いはラフに作られています。サーバー型のDBやAccessなどから比較しても、型の取り扱いが厳格ではないので、扱いやすくスマフォのアプリなどでも不動の地位を築いています。

大きく5つの型が用意されており、列の型になります。

  • Integer – 整数値を格納
  • Text – テキストを格納(UTF8,UTF16で格納)
  • Blob – バイナリデータを格納(ファイル等)
  • Real – 浮動小数点を格納
  • Numeric, Null – なんでも型(Accessの変数で言う所のVariantみたいなもの)

となっています。BooleanやDateといった型が存在せず、関数などで処理を行うことになります。この辺りがおいおい開発を行う上での壁になります。

図:5つの大きな型だけがある

インデックスの作成

MySQLやAccessの場合、テーブル作成時に通常、AutoincrementなID列にインデックスを同時貼れるのですが、SQLiteの場合は作成後に任意にインデックスを作成する必要があります。検索時などの高速化などで必要なインデックスですが、高速化だけでなく重複を許可しないなどの制約を加える為にも必要となります。以下の手順で作成します。

  1. テーブルを作成する
  2. 作成後にDB BrowserのメニューよりEdit -> Create Indexを開く
  3. nameに適当なインデックス名を付け、重複を許可しない場合はUniqueにチェックを入れます。
  4. Columnsよりインデックスを貼るカラムを選び、▶をクリック。ソートを選択
  5. OKをクリックするとインデックスが作成されます。

図:忘れずに作成しましょう

外部キー制約

Accessでいう所のリレーションシップに於ける連鎖更新・連鎖削除の設定。設定しておく事でマスタ側で例えば削除された場合、連結してるサブマスタ側でも同じIDを持つレコードが連鎖削除されたりします。逆にマスタ側にないIDをサブマスタ側で追加する事を制限する事が可能です。

DB Browserを利用した場合にはテーブルのカラム項目の一番右側に「Foreign Key」の項目があります。ここで

  1. サブマスタ側のテーブルを開く
  2. 連結したいカラムを決める(例えば親IDといった列)
  3. Foreign Keyの部分をダブルクリックする
  4. プルダウン等が出てくるので、左からまず紐つけるマスタ側テーブルを選択する
  5. 連結するマスタ側の列を選択する(今回はマスタ側のID列)
  6. 最後のボックスには、DELETE CASCADE ON UPDATE CASCADE を入力する

これで削除時、更新時に連鎖的に動作するようになります。CASCADEで連鎖更新・連鎖削除の動作となります。RISTRICTでマスタ側で削除・更新を行うとエラーとなる動作となります。他にもSET NULLでマスタ側で削除・更新を行うとNULL値になるといったものがありますが、通常はCASCADEとRISTRICTの2つを利用します。

図:データの更新漏れを防ぐのに有効

インポート・エクスポート

テーブルを作成後、プログラムを作るにあたってやバックアップを取る為の機能がDB Browserには備わっています。インポートではSQLファイルおよびCSVファイル、エクスポートではSQLファイル、CSVファイル、JSONファイルに対応しています。

プログラム作成時のサンプルデータを用意するのにこの機能を使ってテストをしましょう。

エクスポート処理

以下の手順でファイルをエクスポート出来ます。CSVの場合は1つのテーブルを1ファイルとして、SQLファイルの場合は1個のファイルにして出力します。また、SQLファイルの場合は出力時にインポートする時のInsert文に対してのオプション設定を行うことが可能です。ファイル -> Exportで実行します。

図:CSVの場合の出力画面

図:SQLの場合の出力画面

インポート処理

出力しておいたCSVファイルやSQLファイルをSQLiteのデータベースにインポートする機能です。インポートには2種類あり、テーブルが存在していない場合に新規にテーブルを作ってインポートと、既存のテーブルにインポートの2つになります。後者の場合、データが空ならば問題ないのですが、インデックスを張っていたり、カラムがUnique値を設定してる場合、インポートはエラーになります。予めデータは空にしておきましょう。通常はCSVでバックアップを取っておくと良いでしょう。

ファイル -> Importの実行で取り込めます。

CSVファイルはUTF-8 BOM無しで用意しておきましょう。

図:CSVであらかじめデータを作っておくと良いでしょう

ビューの作成

DB Browser for SQLiteではAccessのようにGUIからビュー(クエリ)を作成する機能がありません。そのため、作成するにはSQL文を叩いて作成する必要があります。また、すでにビューを作成してる場合、変更をする場合は一旦、ビューをドロップしてから作成する事になります。

作成手順は以下の通りです。

  1. DB BrowserのExecute SQLを開く
  2. SQL文として、以下のような構文を入力する

CREATE VIEW VIEWの名前に続けて、SELECT文を続ければ良いだけ。SQLiteはシンプルなので方言的にはAccessほどキツくはないので、SQL文を作るのが苦手な人は、Accessのクエリで作ったSQL文でもここにCREATEに続けてコピペでも、ビューを作ることは可能です。

※ビューが存在する場合、それを用いたテーブルに変更を加える場合も同様に一旦、ビューを削除しなければなりません。

図:ビューを作成する画面

テーブルの結合

テーブルの結合には、内部結合、外部結合、ユニオンの大きく3つがあり、他にも交差結合や自然結合などが用意されています。ビューを作成する上で、2つ以上のテーブルを連結してビューを作成するには必須です。

内部結合

連結する列においてお互いのテーブルで同じ値を持つものだけを抽出して連結します。マスタ側にしか無いようなデータの場合には、ビューには出てきません。必ずマスタ・サブマスタ両方に同じID等を持つデータが必要になります。

上記の例では、masterテーブルのIDとsubmasterのMID列を連結し、お互いに同じ値を持つレコードが抽出されます。

外部結合

連結する列において片方のテーブルにだけデータがある場合でも抽出してくれる連結方法です。マスタ側にだけデータがあってサブマスタに無い場合でも、マスタ側のデータが抽出されるようになります(サブマスタ側の値は空っぽのままになります)。

但し、SQLiteはRIGHT OUTER JOINをサポートしていないので、サブマスタ側からマスタ側への外部結合は利用出来ません。

ユニオン

2つの全く同じカラムと型を持つテーブルを1つのテーブルに合体させる、Accessでいうユニオンクエリに該当するものです。例えば支店毎にテーブルを作成しておき、各々の支店はそちらのテーブルを参照。管理者はこれらを結合させたユニオンビューを持って処理をするといったようなセグメントを設ける場合に利用します。

上記の例では、SHIBUYAテーブルとSHINJUKUテーブルは全く同じタイプのテーブルで、2つを1つのテーブルにUNION句を使って結合させます。但し注意点として、この場合のビューでお互いにある連番の列(Accessで言う所のオートナンバーに該当)する列は、重複しないような値にするべきでしょう。また、そのレコードがどちらのテーブルに該当するレコードなのかの見分けがつかないと、コードを書く時に面倒です。

日付の差分を取る

SQLiteは日付型というものが列の型に存在しないので、例えば2つの日付の差分を取る場合どうしたら良いのか?と言うと、用意されてる関数をSQL文内で利用して差分を取る事が可能です。また、この時の日付はyyyy/mm/ddではなくyyyy-mm-ddの形式である必要があるので、よくある日付の型の場合には変換が必要になります。

例えばサブマスタの日付列の値と現在の日時からの日付差分をdiffdateとして演算する場合は以下のような構文になります。

  • 日付がyyyy/mm/ddの場合はreplace関数でスラッシュをハイフンに変換してあげます。
  • 日付の差分として取りたい場合は一旦、日付の値をjulianday関数で数値に変換します。
  • julianday(‘now’)にて現在の日付の数値を取得出来ます
  • 差分を取る場合には、上記の関数で変換した2つの値をマイナスで差し引きし、CAST関数でinteger型に変換して列名を設定します。

図:expireの日付から現在日付の差分をdiffdateとして取り出した

時刻の差分を取る

日付の差分だけでなく、給与計算に於ける勤怠などで活躍するのが「時刻の差分」を取ること。09:00 – 18:00といったような日付データの無い値でも差分を取ることができます。SQLiteの関数を使って差分を取りますが、秒で取れるので分に直す60で割るところまでを含めると良いです。

例えば、出勤時刻と退勤時刻の差分を取る場合には以下のように組みます。

  • 差分を取る時は退勤時刻のendtimeから出勤時刻のstarttimeを差し引きします
  • 差し引きした値は秒なので、コレを分に直す60で割ったものをsabunという名の列として表示
  • 時刻文字列を時刻として認識するにはstrftime関数を利用します。
  • オプションの%sを%Mにすれば、秒ではなく分で取ることも可能(その場合60で割るといったことは不要)。オプションは結構豊富にあります。

図:無事に差分を取る事ができました。

条件判定をする

Accessなどでは、計算列として特定の値の内容を元に条件判定した結果をIIF関数を使って処理することが結構あります。クエリ上で数式を構築して判定が出来るのでコードの節約にもなりますが、これをSQLiteで実現する場合、IIF関数が使えるわけではないので、躓くポイントになります。またその時に「○○を含む場合」といったケースでは、Like演算子を使う点は同じですが、ワイルドカードの指定方法が異なるので、ここも含めて

特定の列の値に【固定】という文字列が含まれる場合には、-1を、そうでない場合0を返し、judgmentという列で表示する

を実現する場合以下のようになります。多数含まれるデータに対して行ったのでグループ化していますが、グループ化は必須項目ではありません。

  • 条件判定は、CASE~WHEN句を使って行わせます。
  • WHENが○○の場合といった形で複数追加できるので、IFというよりSELECT文のようなスタイルです
  • ELSEは上記の条件以外の場合、VBAのCASE ELSEに該当します。
  • ENDで終わらせて、as judgmentで列として表記しています。
  • THENで値を返します。
  • ○○を含む場合には、ワイルドカードをLike演算子とともに使いますが、アスタリスクではなく「%」で括ります
  • グループ化する場合、通常の列はgroup byでグループ化しますが、判定列は演算列なのでグループ化する必要はありません。
  • WHERE条件をつけて、judgmentの値が-1のだけを抽出も同時に行えます

グループ化

レコードデータを特定列でグルーピングし、合計や平均、カウントなどを行うためのGROUP BYを使うことが可能です。さらにこのグループ化後のデータに対しては、HAVING句を用いて条件設定をすることで抽出する事も可能です。

上記の例では、SUBMASTERのuid列だけを取り出し、カウントの列としてsharecntを設ける。group byにてuid列をグルーピング化しています。また、havingにてsharecnt列の値が0より上のレコードだけを抽出するように条件設定も加えています。

count関数などの集計関数はグループ化していないと利用出来ませんし、このグループ化は基本最初に取り出したすべての列に対して設定しなければならないので、group byの後に全部の列が列挙されていないとエラーになります。どういった列だけ取り出しグループ化すべきかが悩むポイントです。

※但し、SQLiteはAccessのようなPivot句には未対応なので、クロス集計は出来ません。ので、アプリ側でピボット前のデータからクロス集計データを配列で作ってあげるなどの対応が必要になります。

図:集計では必ず使うグルーピング

トリガー

あるテーブルにデータを追加や更新を掛けた時に、別のテーブルに対してもなんらかのSQL文を自動で発行して作業をさせる為のものがトリガーで、例えばINSERT履歴を自動で残したりレプリカのテーブルに自動で追加したり、プレ集計を行わせておいて実際の集計時間の短縮など、プログラミング無しで行わせる便利な反面、プログラミングほど自由度があるわけではないので、使い所は選ぶ機能です。

ビュー同様、DB Browser上ではSQL文を叩いて作成を行います。作成手順は以下の通りです。

  1. DB BrowserのExecute SQLを開く
  2. SQL文として、以下のような構文を入力する

上記の例だと、テーブル名に対して UPDATE をした時にBEGIN以下のSQL文を実行するという意味になります。自動実行するSQL文は複数設置可能で、UPDATE以外にINSERT、DELETEがあります。

SQL文ではnewをカラム名につける事で新しい値、oldをつけることで古い値を取得し、INSERT文などで利用することが可能です。

図:但しトリガーは多用するべからず

データベース暗号化

SQLiteにSQLCipherという仕組みを組み合わせて、セキュアなデータベース運用を行う事が可能です。DB Browser for SQLiteインストール時に通常はノーマルなSQLiteのみがインストールされますが、オプションでSQLCiperを選ぶと、暗号化対応版のDB Browser for SQLiteも別途インストールされます。

インストール後はすぐに使える状態になっており、

  1. ツールより「Tool」-> 「set encryption」を選ぶ
  2. パスワードを入力し、暗号化オプションを選び、OKをクリック
  3. セットされると再読み込みされて、パスワードを再度入力するとオープンできるようになります。

但し暗号化したSQLiteファイルの場合、プログラムからの読み書きはSQLCipher対応のモジュールが必要で、Node.jsだとnode-sqlite3ではなく、better-sqlite3-sqlcipherを利用することになります。(node-sqlite3でも自力でビルドすればSQLCipher対応できるようですが)

図:暗号化SQLiteを作成中

Vacuum

SQLiteも立派なデータベース。データの追加や削除、テーブルの削除などに伴って、本来であれば減った分だけファイルサイズが減るべきなのですが、SQLiteはテーブル作成時にオプション指定で、Vacuumの指定をしていないと、DBが肥大化する事があります。SQLのVacuum文を任意に実行する事で、残ったノイズデータ文だけシュリンクしてくれるわけですが、毎回手動で行うのもちょっと考えもの。

ということで、テーブル作成前でなければなりませんが、DB Browser for SQLiteにてAuto_vacuumのオプション指定をしておきましょう。

  1. DB Browser for SQLiteで新規にデータベースを作成する
  2. タブのプラグマ編集を開く
  3. Auto Vacuumの項目を「Full」に指定する
  4. データベースにテーブルを作って保存する

Vacuum文はノイズの除去とフラグメント解消をしますが、Auto Vacuumはトランザクション -> コミットのたびにノイズ除去をしますが、フラグメント解消はしません。

図:Auto Vacuum設定はテーブル作成前に

SQLiteの読み書き

Node.jsなどのアプリでSQLiteに接続してデータを読み書きする場合、メソッド毎に読み書きの構文を書いているとプログラムが冗長になってしまうので、通常はSELECT, INSERT, UPDATE, DELETEの代表的な4構文に関しては関数化しておいたり、もう一歩進めてクラス化しておいて使うと、再利用性が高まって、見通しが良くなります。

冒頭部分

Node.jsの場合、上記のコードのように予め接続するdb.sqlite3ファイルの場所を指定し、接続しておくようにします。基本的に個人で使うデータベースであるため、マルチユーザで同時書き込みをするようなものには用いないので(サーバで使う場合は別ですが)、自分の場合はMySQLの時のようにロックを考慮したりしません。また暗号化についてもデフォルトでは未対応であるため、別の手段を用意する必要があります。

必要な時に開いて、そして閉じるというシンプルな処理を4パターンに応じて作っています(実際には2パターン辺りまで統合できますが)

インメモリ化

以下のように、:memory:を加えると、SQLiteのインメモリデータベース化が可能です。

open_readwrite, open_create, open_readonlyの3種類のモードがあります。

必要な時に接続し終了する

アプリで使用する場合、基本的に個人なので、冒頭でsqliteのファイルを読み込み開きっぱなしでそのまま運用する事が多いですが、必要な時にだけ接続して、SQL実行後にはきちんとクローズする場合には、冒頭で接続しっぱなしにするのではなく、都度インスタンス化してCloseさせます。

次にまた接続する場合は、再度、new sqlite.Databaseにてインスタンス化してから処理を行うことになります。

SELECT

SQL実行関数

クエリを受け取って実行し、成功したらrowsにレコードが入ってるので、callbackで呼び出し元へと返しています。SQL文ついては以下のようになります。もちろん、ビューに対してSELECT文を発行することも出来ます。

呼び出し側

callbackされてきたrowsの値を取り出し、それぞれをretmanという連想配列にデータ部分と件数とにわけて入れ込み、Electronの場合はレンダラ側にevent.sender.sendで送っています。

指定件数ずつ取得する

一度に大量のデータを取得するとプログラム側にとって少々不具合が出るケースというものがあります。いきなり全件取得ですと表示するまでに時間が掛かるので、遅延ロードなどを併用して10000件ずつ取得して表示などなど。こういったケースでは、次の10000件を取得して既存のデータに追加するといった処理を装備しますが、SQLite側で「次の10000件を呼び出し」といったことをするためには、offset句とLimit句を利用します。

上記の例の場合、offset変数に値を入れることで、データの取得位置(行番号)を変更出来るようになる。初期値は0とし次の1万件を取得したいならば、offsetには10000を入れる(0からスタートしてる点に注意)。つまりこれが、データの取得開始位置となる。

Limit句で10000件を取得するように同時に制限を入れないと、offsetの位置から後ろのデータを全部取得してしまうので注意。

INSERT

SQL実行関数

レコードの追加の場合の処理です。続けてサブマスタにレコードを追加する場合を考慮して、追加時のAutoincrementで自動で挿入されるIDの数値を取得して返すようにしています。

SQL文と追加するvaluesについては以下のようになります。

valuesは配列で渡すのですが、二次元配列の形で渡せば、バルクインサートが可能になっています(但し、一度に沢山のレコードをprepareで追加するとNGで、1回900レコード程度の制限があるみたい)。

但し、大量のレコードを普通にInsertで1行ずつ入れていくととてつもなく遅いです。

呼び出し側

呼び出し側は上記の関数を呼び出し、処理が終わったらcallbackしてもらうように組んでいます。retに追加したレコードのIDが返ってくるので、それを取得して引き続きサブマスタへの追加処理へとつなげます。

トランザクション処理

SQLiteにもトランザクション処理があり、特にInsert処理の場合は、1行ずつInsertしていくと、

  • DBオープン
  • DBへ書き込み
  • DBへコミット

を何度も繰り返すことになり、非常に遅いです。そこで、トランザクション処理を行う事で一回のDBオープンとコミットだけにし、連続で書き込み処理を行わせる事で高速化出来ます(およそ25,000行のデータで1分程(17カラム))。

基本は

  • db.run(“begin transaction”)でトランザクション開始
  • async.eachSeriesのループ内で、db.runにて配列データを順次追加作業
  • db.run(“commit”)でコミット
  • エラーが発生すると自動でロールバック

となります。

バルクインサート

バルクインサートの基本

通常、1回のInsert処理で追加できるレコードは1行のみです。しかし、1回のInsert処理で複数行のレコードを一括で処理するのがバルクインサート。劇的に追加速度がアップします。SQL構文としては

といったように、values以降に、値を格納したものを、カンマ区切りで並べてつなげれば、バルクインサートになります。しかし、あまりにも多くの値をつなげると、too many sql variableというエラーが発生してしまいます(SQLiteの制限)。こちらのサイトに制限についての記載があります。v3.32以前のSQLiteだと999個、それ以降だと32766個がデフォルト設定のようです。これは、?の部分が999個が上限ということですね。

しかし、Node.jsでこれをやろうとするとValuesの部分を構築するのが面倒であるため、例えば100個ずつに区分けして、Insert Intoを実行するようにし、2次元配列は1次元配列に変換して渡すようにするというテクニックがstackoverflowで紹介されています。

2次元配列を1次元配列にし、またレコードの数だけ必要なplaceholderを用意するという点が独特です。

100レコード単位でバルクインサート

SQLiteのInsert処理に於いて、db.runが非同期に実行されてしまうので、データの処理を考えて同期的に処理を実施しながら、100レコード単位でバルクインサートを実施してみた所、40,000行のデータでおよそ15秒程度で完了(17カラム)できました。これにはElectron側でこの全データをCheetah Gridで表示する所まで含めてこの値なので、当初のトランザクションも使わず、バルクインサートもせずにasync.eachSeriesで1件ずつトロトロインサートしていた時よりも遥かに高速にInsert処理が出来ました。

100件分を取り出すspliceの項目。一番最後のInsert時には100指定しての場合、実際の配列の数以上の値を指定してしまってることになりますが、取得されるtemparray変数にはきちんと残りの未処理の配列分だけ取り出せています。エラーも出ず残りの配列数を気にすることなく切り出せるので、非常に便利です。spliceはsplice(0,100)で、0番から100個目までの配列を取り出すことになり、arrayから除去されますので、次のターンでもsplice(0,100)で次の200個を取り出せます。

UPDATE

SQL実行関数

レコードの更新処理の時の関数です。基本は更新が成功したかどうかだけを呼び出し元へと返します。SQL文とvaluesについては以下のようになります。

Valuesについては、Insert処理同様にバルクインサート処理を行えば高速アップデートを実行可能です。

呼び出し側

呼び出し側は処理が完了したらOKだけを返すようにしています。エラー時にはダイアログ表示をして処理を終了します。

他のテーブルとJOINしてUPDATE

MySQLやAccessなどでは普通にできることなのですが、2つ以上のテーブルをJOINしてその値を元にUPDATEするということが、SQLiteの場合実行することができません。例えば以下のようなSQL文は実行が出来ないのです(Version 3.33より前のバージョン)。結構使うテクニックなので、このままだと非常に困ります。

▼以下の例はtslogのeventの値をtslog.workdate<->holiday_public.hdate2で連結して、holiday_publicのhnameで更新するクエリ

StackOverflowの解決法によると、この場合、値をSETするカラムに対してサブクエリを実行し、where条件にEXISTS句でも同じようなサブクエリを実行することで、上記のUPDATE文と同じ結果を得られるとのことで、実行してみましたが見事にアップデート出来ました。但し、大量のデータを更新するとサブクエリを実行してる関係で、非常に遅いです。

上記の事例だと、1つの列の値を更新するだけで、複数の列を更新したい場合困ります。その場合は以下のような記述が可能です。

()で括って、カンマ区切りで列名を列挙し、SELECT文の側でも同様に列を列挙することで、指定の複数の列のアップデートをテーブル結合で実現出来ます。また、SQLite Version 3.33以降は、UPDATE FROMが使えるようになり、より簡単に記述が可能になり、以下のような書き方でもアップデートが可能です。

DELETE

SQL実行関数

更新時処理と殆ど同じなので、UPDATEとDELETEは一個の関数としても良いのですが、UPDATEの場合は今回は利用していませんが、Bulk Updateの処理も存在し得るので、Transaction処理を考慮して、別に分けています。SQL文としては以下のようになります。

呼び出し側

基本的にはUPDATE文の時の関数処理と同じ。

テーブルを新規作成する

アプリのアプデ時に過去の版にSQLiteのファイルを上書きするようなことをしてしまうとデータが消えてしまいます。そこで、既存のDBに対してテーブルの有無チェック(後述)を行い、なかった場合には新規にテーブルを作ってあげないとアプリの運用ができなくなります。といったようなシーンや膨大なデータを新規のテーブルに切り出して分割するようなシーンでも使うのが「テーブルの新規作成」。作成時にはテーブル名、列の名前、データ型の指定が必要になります(デフォルト値のセットも出来ます)

  • Create Table文を使って新規に作成します。
  • ‘列名’ 型 といった形でカンマ区切りで列を指定してゆきます。
  • ID列等の場合は、Primary keyの指定が必要且つ、Autoincrementの指定をするのが定石です。
  • db.runでクエリ文を実行するとテーブルが作成されて保存されます。

列を新規に追加する

テーブルの新規追加の他にも過去のアプリとの互換性の為などを主な目的として新規に列を追加するケースがあります。列の追加だけでなく削除や型の変更など様々なテーブル構造の変更を行う場合に使うのが「Alter文」です。但し、SQLiteの場合は以下のような制限があります。

  • 列の追加などは一度に複数の列を追加できず、1列ずつ追加する事になる
  • alter table ~ modify ~といった感じで、カラム構成の変更は出来ない。
  • Primary Keyの列は変更出来ないし、新規に追加も出来ない
  • unique指定も出来ません
  • 追加時にデフォルト値をセットする事が出来るけれど、関数などの指定などは出来ない

などなど。結構制約が多いので多用することはないと思います。この当たりの制約をまとめたページがこちらにあります。既存のテーブルに同名の列チェックを行った後に(後述)、列を追加するSQL文を実行します。

  • 2つ以上の列追加は、一列ずつ追加していく事になります。
  • Alter Table ~ Add Column ~句を使って、列名と型を最低でも指定して実行します。

特定の列の存在有無チェック

アプリのバージョンアップを重ねていく内に、SQLiteのテーブル構造も変更するシーンが出てきます。そうなると、過去のアプリとデータベースの互換性が失われてしまうことがあります。それが「特定の列の有無」。後のバージョンで列を追加したのであれば、アプデ時にデータベースも上書きすれば問題無いですがデータも消えます。ということで、列の有無をチェックしてさらに「列を追加する」処理を装備すれば、データベースを上書きせずに、DB側を弄る事でユーザのアプデ上の障害を取り除けます。

  • テーブル情報を取得するためのSQLは、pragma table_infoという変わったSQLを利用します。これで列情報が連想配列で返ってきます。
  • ループで一個ずつ列をチェックして、対象の列の名前と一致するものがあったら、フラグを立てます
  • フラグが建ってる場合は列が存在するので、処理をせず、ない場合には例えば列追加の処理を追記します。

図:列の名前や型がわかる

対象のテーブルの存在有無チェック

Node.jsなどのプログラムで、他のSQLite3のデータベースをインポートして入れ替えるといった仕組みの場合、バージョンを重ねるごとに、古いバックアップのdb.sqlite3ファイルには存在しないテーブルというものが出来ていくこともあるでしょう。しかし、当然のように普通にインポート処理をコードで記述してしまうと、存在しないテーブルに接続してデータを取り込もうとしてエラーが発生する事になります。

そこで、対象のSQLite3ファイルに「対象となるテーブルが存在しているかどうか?」をチェックするSQL文を発行して、存在しなければ処理をスルーするような仕組みが必要になります。この場合、以下のようなSQL文を構築し、存在すれば1、存在しなければ0が返ってくるので、それを元に処理を分岐させる事が可能です。

  • SELECT文ではCOUNT関数を使ってレコード数を返すようにしています。
  • 検索対象は表向き見えない管理用テーブルであるsqlite_masterテーブルに対してクエリを発行しています。
  • WHERE条件は、typeで「table」を指定してテーブルのみとし、「name」にてテーブル名を指定します(recにはテーブル名を入れておく)
  • 該当するテーブル名のtypeがtableのデータがあれば、1が返ります。なければ0が返ります。
  • dbbackはインポートするDBに対して接続しているインスタンスです。それに対して、db.getにてクエリを発行します。
  • resには[{‘COUNT(*)’:1}]といった値が返ってくるので、countman変数ではその値のみを取り出しています。

図:管理用テーブルを参照するテクニックです。

AccessからODBC接続

SQLiteは大変便利なんだけれど、テーブルにデータを流し込むであったり、Accessの2GBの制限を超えるためにもSQLiteは役に立ちます。但し、データを流し込むには、DB Browser for SQLiteはちょっと使いにくく、データのコピペでイケるAccess + SQLite ODBC Driverの組み合わせが、Windowsで扱うには便利です。

以下の手順で、AccessからSQLiteに接続しデータを扱う事が可能です。

  1. SQLite ODBC Driverをダウンロードしてインストールしておく(64bitだとsqliteodbc_w64.exeがそれになる)
  2. Windowsの検索窓から、ODBCデータソースアドミニストレータを開く(今回は64bit版)
  3. ユーザDSNが開かれてる状態なので、追加をクリック
  4. SQLite3 ODBC Driverを選択
  5. Data Sorce Nameは適当に付ける。Database NameはBrowseをクリックして、接続するdb.sqlite3ファイルを指定する
  6. OKをクリックする
  7. Accessを起動する
  8. 外部データ -> 新しいデータソース -> 他のソース -> ODBCデータベースを開く
  9. リンクテーブルを作成して、ソースデータにリンクするにチェックを入れてOKをクリック
  10. コンピュータデータソースをクリックし、5.で作ったデータ設定を選んでOKをクリック
  11. データベース一覧が出てくるので、リンクするものをクリックして選ぶ
  12. リンクテーブルが貼られるので、後は好きなように操作する

Accessでクエリを作り、そのSQL文はそのままDB Browser for SQLiteでも使えたりするので、Viewを作ったりする時にもAccessのUIは役に立ちます。

また、コピペでデータを流し込める上に、SQLiteの場合はaccdbファイルのような2GBの制限が無いので、大量のデータでこの制限に引っかかってるケースでも有効な手段になります。

VBAで手動で接続する場合には、以下のような接続文字列を作る事で接続が可能です(ActiveX Data Object 6.1 Library等を参照設定で指定しておく必要があります)

図:セットアップは至ってシンプル

図:リンクするテーブルを選ぶ

他のDBから自身のDBにデータをコピーする

事前準備

SQLiteは当たり前ですが、Accessのようなリンクテーブルが使えるわけではないので、他のsqliteファイルに入ってるデータを自身のsqliteファイルへデータをコピーするにはロジックを用意してあげる必要があります。

概要を言えば、

  1. 他のDBに対してSELECTでテーブルデータを取得し、変数に格納する
  2. 格納したデータ(JSON形式)を、バルクインサートで自身のDBへとINSERTする
  3. この時、SELECTするテーブルが複数あって、各々カラムが異なる場合、汎用的なロジックが必須となる(でないと、テーブル毎にロジックを組む羽目になる)

ということになるのですが、なかなか面倒なロジックを構築する必要があります。この仕組は主に自身のアプリのDBバックアップ&復元機能を実装するのに必要な機能なので、しっかりと構築してあげる必要があります。

今回、DBバックアップ時にはdb.sqlite3をまるごと暗号化ZIPで固めて、復元時は解凍し上記のプロセスで自身のDBへバルクインサートする処理を書いてみました。事前にいくつかのモジュールを追加する必要があります。

  • archiver – ZIP圧縮する為のモジュール
  • 7zip-bin – ZIP解凍時に使用するモジュール。7zip対応
  • node-7z – Node.jsで7zipを扱う為のモジュール
  • archiver-zip-encryptable – archiverモジュールで暗号化を扱う為のモジュール
  • date-utils – 今回ファイル名を日付で作るので日付を扱うモジュールとして採用

上記のモジュールをnpmでインストールしておき、index.jsの冒頭で以下のように呼び出しておきます。

※node-7zでも圧縮は可能なので、archiverを使わず、node-7zで圧縮する関数を作るのも良いです。

ソースコード

今回のコードはElectronから呼び出して利用しています。

バックアップ時

バックアップ時の圧縮処理はそこまで難しくありません。保存場所を指定して圧縮を実行するだけ。

  • ダイアログにて、保存場所を指定する
  • zipman関数でZIP圧縮を行わせています。この時、ファイル名を日付で生成し、passwordで暗号化のフレーズを指定
  • archive.fileにて今回は単一のファイルを暗号化ZIPで固めています。dbfileにはdb.sqlite3ファイルのフルパスを指定しています。
  • 圧縮はarchive.finalizeで実行。完了後にoutput.on(“close” 以降が動き、呼び出し元へcallbackしています
  • callbackする際には、生成した日付のファイル名を返してあげています。

解凍時

問題は暗号化ZIPで圧縮されたdb.sqlite3のファイルを解凍し、中に入ってるテーブルを一つずつ拾って、自身のテーブルにバルクインサートで処理をします。処理後には解凍したファイルを削除するのを忘れずに。

  • zipmelt関数が解凍するメイン関数です。
  • 解凍後にはファイルのパスを持って、backimport関数に渡しています。
  • 解凍時に7zipのモジュールで暗号化解除し解凍を行っています。
  • 今回は解凍先はデスクトップのパスを指定しています。

バルクインサート

さて、解凍したdb.sqlite3に接続し、自身のdb.sqlite3ファイルへはAccessのようには簡単に行きません。ましてや、今回のバルクインサートの場合はテーブル毎にカラムは異なりますし、当然placeholderの数も異なります。これらに対応して一括でトランザクション処理にて高速インサート処理をテーブルの数だけ繰り返す必要があります。また、処理後はインポート元のdb.sqlite3はcloseしないと、アプリが掴んだままの状態になってしまうので、必ずデータベースを閉じる処理が必要です。

  • テーブル一覧の配列をもって回し、まずは自身のdb.sqlite3ファイル内のデータをDELETEします。これらは同期的に処理が必要なので、Promiseやasync.eachSeriesで順次処理をさせています。
  • DELETE後、各テーブルに対してSELECTで全データを取得。データ件数が0ならばnext()で次のテーブルの処理へとスキップする
  • データ取得後にカラム一覧をcolumnsに生成。同じくinsert文で使うplaceholderをカラムの数だけ生成しておく
  • 取得データは一行ずつ配列に変換してarrayに格納しておく
  • querybaseにてテーブル、カラムをtemplate構文で組んでおきます。
  • バルクインサートの処理は、前項で記述したバルクインサートの記述を少し改造し、クエリとプレースホルダを追加で引数に手渡すようにしています。topgun2という関数がバルクインサートを引き受けています。
  • バルクインサートが終わったら、コミット -> インポート元のDBのクローズ処理を実行
  • クローズ後にインポート元のdb.sqlite3のファイルは削除をしておく

特に、実際の業務用データなどが入ってる場合には、レコード数が数万を超えて入ってる可能性があるため、復元時はバルクインサート処理でなければ、非常に時間が掛かってしまうので、このような汎用的なバルクインサートの実装は必要不可欠です。

Cheetah GridのAuto Pagingで同期的に取得

Vuetifyの項目で紹介してるCheetah Gridですが、100万件のデータでも高速に表示とスクロールを実現する素晴らしいデータグリッドですが、Auto Pagingを利用すると、Electronと組み合わせて、Lazyloadのようにスクロールすると次の○万件を取得してグリッドに追加するということが可能になっています。

しかし、今回利用してるnode-sqlite3は通常非同期で取得し返すように作られている為、そのままでは具合が悪く、また、Electronで現在標準のcontextBridgeであるpreload.jsを経由して取得し返す必要があるため、かなり複雑です。今回はこれを実現したコードを残しておこうと思います。以下のエントリーはVuetifyのCheetah Gridについて詳細に記述していますので、合わせてご覧ください。

Google Apps ScriptでVuetifyを使ってUIを作る

HTML側コード(レンダラプロセス)

Vuetify mount時の処理

  • VuetifyのMountにてデータのトータル件数を取得させておきます。帰ってきた値はIPCの受け手であるgettotalrecで取得
  • rectotalに件数を格納したら、後述のCheetah Gridの変数であるgridmanのdatasourceとしてdatasource変数を指定します。

datasource変数とリクエスト用関数

  • Cheetah Gridのデータソースにはdatasource変数を割り当ててます。
  • 全件ロードが完了しているかどうかを判定して、スクロールで一番下まで行った時にgetRecordWithAjax関数で次の1万件をリクエストしています。
  • レコード全件数と現在のindexを比較して、まだ取得出来る場合には、ipc.doActionにてメインプロセス側にリクエストを同期的に行い、返り値をrecords変数にpushしてdatasourceに返しています。

Cheetah Gridの初期化

Vuetify Mount時に初期化させています。gridmanというグローバル変数に格納しています。

  • 初期化時にはgridmanに対してはデータソースを指定していません。データの総件数を取得した時にセットするようにしています。
  • gridareaというIDを持つdivに対してCheetah Gridを表示しています。

preload.js(ContextBridge)

  • IPC通信で利用するpreload.js側のコードです。メインとレンダラをつなぐ中間に位置するものになります。
  • doActionという関数を用意し、asyncにてレンダラ側から呼び出し、メインプロセスにipcRenderer.invokeにて送り込み、返り値をレンダラ側に返します。
  • invoke自体は非同期実行ですが、awaitをつけることで帰ってくるまで同期的に待機します。
  • メインプロセス側のIPCの受け手であるan-actionに対して引数付きで渡しています。

JS側コード(メインプロセス)

  • レンダラ側からリクエストを受け付けるan-action側です。
  • 10000件のlimitをつけ、ID列を降順offsetとして引数の値で指定した位置から取得します。
  • db.allにPromiseで括ることで同期的に処理を行わせています。
  • 取得したデータはresolveで返します(通常はinvokeで受けた場合はreturnで返すのですが、Promiseを使ってるので、resolveになります。

関連リンク

共有してみる:

コメントを残す

メールアドレスが公開されることはありません。

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください