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の場合は作成後に任意にインデックスを作成する必要があります。検索時などの高速化などで必要なインデックスですが、高速化だけでなく重複を許可しないなどの制約を加える為にも必要となります。以下の手順で作成します。
- テーブルを作成する
- 作成後にDB BrowserのメニューよりEdit -> Create Indexを開く
- nameに適当なインデックス名を付け、重複を許可しない場合はUniqueにチェックを入れます。
- Columnsよりインデックスを貼るカラムを選び、▶をクリック。ソートを選択
- OKをクリックするとインデックスが作成されます。
図:忘れずに作成しましょう
外部キー制約
Accessでいう所のリレーションシップに於ける連鎖更新・連鎖削除の設定。設定しておく事でマスタ側で例えば削除された場合、連結してるサブマスタ側でも同じIDを持つレコードが連鎖削除されたりします。逆にマスタ側にないIDをサブマスタ側で追加する事を制限する事が可能です。
DB Browserを利用した場合にはテーブルのカラム項目の一番右側に「Foreign Key」の項目があります。ここで
- サブマスタ側のテーブルを開く
- 連結したいカラムを決める(例えば親IDといった列)
- Foreign Keyの部分をダブルクリックする
- プルダウン等が出てくるので、左からまず紐つけるマスタ側テーブルを選択する
- 連結するマスタ側の列を選択する(今回はマスタ側のID列)
- 最後のボックスには、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文を叩いて作成する必要があります。また、すでにビューを作成してる場合、変更をする場合は一旦、ビューをドロップしてから作成する事になります。
作成手順は以下の通りです。
- DB BrowserのExecute SQLを開く
- SQL文として、以下のような構文を入力する
1 |
CREATE VIEW VIEWの名前 AS SELECT * FROM MASTER; |
CREATE VIEW VIEWの名前に続けて、SELECT文を続ければ良いだけ。SQLiteはシンプルなので方言的にはAccessほどキツくはないので、SQL文を作るのが苦手な人は、Accessのクエリで作ったSQL文でもここにCREATEに続けてコピペでも、ビューを作ることは可能です。
※ビューが存在する場合、それを用いたテーブルに変更を加える場合も同様に一旦、ビューを削除しなければなりません。
図:ビューを作成する画面
テーブルの結合
テーブルの結合には、内部結合、外部結合、ユニオンの大きく3つがあり、他にも交差結合や自然結合などが用意されています。ビューを作成する上で、2つ以上のテーブルを連結してビューを作成するには必須です。
内部結合
連結する列においてお互いのテーブルで同じ値を持つものだけを抽出して連結します。マスタ側にしか無いようなデータの場合には、ビューには出てきません。必ずマスタ・サブマスタ両方に同じID等を持つデータが必要になります。
1 |
SELECT * FROM MASTER INNER JOIN SUBMASTER ON MASTER.ID = SUBMASTER = MID |
上記の例では、masterテーブルのIDとsubmasterのMID列を連結し、お互いに同じ値を持つレコードが抽出されます。
外部結合
連結する列において片方のテーブルにだけデータがある場合でも抽出してくれる連結方法です。マスタ側にだけデータがあってサブマスタに無い場合でも、マスタ側のデータが抽出されるようになります(サブマスタ側の値は空っぽのままになります)。
1 |
SELECT * FROM MASTER LEFT OUTER JOIN SUBMASTER ON MASTER.ID = SUBMASTER.MID |
但し、SQLiteはRIGHT OUTER JOINをサポートしていないので、サブマスタ側からマスタ側への外部結合は利用出来ません。
ユニオン
2つの全く同じカラムと型を持つテーブルを1つのテーブルに合体させる、Accessでいうユニオンクエリに該当するものです。例えば支店毎にテーブルを作成しておき、各々の支店はそちらのテーブルを参照。管理者はこれらを結合させたユニオンビューを持って処理をするといったようなセグメントを設ける場合に利用します。
1 |
SELECT * FROM SIBUYA UNION SELECT * FROM SHINJUKU |
上記の例では、SHIBUYAテーブルとSHINJUKUテーブルは全く同じタイプのテーブルで、2つを1つのテーブルにUNION句を使って結合させます。但し注意点として、この場合のビューでお互いにある連番の列(Accessで言う所のオートナンバーに該当)する列は、重複しないような値にするべきでしょう。また、そのレコードがどちらのテーブルに該当するレコードなのかの見分けがつかないと、コードを書く時に面倒です。
日付の差分を取る
SQLiteは日付型というものが列の型に存在しないので、例えば2つの日付の差分を取る場合どうしたら良いのか?と言うと、用意されてる関数をSQL文内で利用して差分を取る事が可能です。また、この時の日付はyyyy/mm/ddではなくyyyy-mm-ddの形式である必要があるので、よくある日付の型の場合には変換が必要になります。
例えばサブマスタの日付列の値と現在の日時からの日付差分をdiffdateとして演算する場合は以下のような構文になります。
1 2 3 |
SELECT *, CAST(julianday(replace(SUBMASTER.expire,'/','-')) - julianday('now') as integer) AS `diffdate` FROM SUBMASTER |
- 日付がyyyy/mm/ddの場合はreplace関数でスラッシュをハイフンに変換してあげます。
- 日付の差分として取りたい場合は一旦、日付の値をjulianday関数で数値に変換します。
- julianday(‘now’)にて現在の日付の数値を取得出来ます
- 差分を取る場合には、上記の関数で変換した2つの値をマイナスで差し引きし、CAST関数でinteger型に変換して列名を設定します。
図:expireの日付から現在日付の差分をdiffdateとして取り出した
時刻の差分を取る
日付の差分だけでなく、給与計算に於ける勤怠などで活躍するのが「時刻の差分」を取ること。09:00 – 18:00といったような日付データの無い値でも差分を取ることができます。SQLiteの関数を使って差分を取りますが、秒で取れるので分に直す60で割るところまでを含めると良いです。
例えば、出勤時刻と退勤時刻の差分を取る場合には以下のように組みます。
1 2 3 |
select starttime,endtime, (strftime('%s', endtime)-strftime('%s', starttime))/60 as sabun from earlyhosei |
- 差分を取る時は退勤時刻のendtimeから出勤時刻のstarttimeを差し引きします
- 差し引きした値は秒なので、コレを分に直す60で割ったものをsabunという名の列として表示
- 時刻文字列を時刻として認識するにはstrftime関数を利用します。
- オプションの%sを%Mにすれば、秒ではなく分で取ることも可能(その場合60で割るといったことは不要)。オプションは結構豊富にあります。
図:無事に差分を取る事ができました。
条件判定をする
Accessなどでは、計算列として特定の値の内容を元に条件判定した結果をIIF関数を使って処理することが結構あります。クエリ上で数式を構築して判定が出来るのでコードの節約にもなりますが、これをSQLiteで実現する場合、IIF関数が使えるわけではないので、躓くポイントになります。またその時に「○○を含む場合」といったケースでは、Like演算子を使う点は同じですが、ワイルドカードの指定方法が異なるので、ここも含めて
「特定の列の値に【固定】という文字列が含まれる場合には、-1を、そうでない場合0を返し、judgmentという列で表示する」
を実現する場合以下のようになります。多数含まれるデータに対して行ったのでグループ化していますが、グループ化は必須項目ではありません。
1 2 3 4 5 6 7 8 9 |
SELECT tslog.empid, tslog.empname, tslog.kinmu, CASE WHEN tslog.kinmu Like '%固定%' THEN -1 ELSE 0 END as judgment FROM tslog WHERE judgment = -1 group by empid, empname, kinmu order by empid |
- 条件判定は、CASE~WHEN句を使って行わせます。
- WHENが○○の場合といった形で複数追加できるので、IFというよりSELECT文のようなスタイルです
- ELSEは上記の条件以外の場合、VBAのCASE ELSEに該当します。
- ENDで終わらせて、as judgmentで列として表記しています。
- THENで値を返します。
- ○○を含む場合には、ワイルドカードをLike演算子とともに使いますが、アスタリスクではなく「%」で括ります。
- グループ化する場合、通常の列はgroup byでグループ化しますが、判定列は演算列なのでグループ化する必要はありません。
- WHERE条件をつけて、judgmentの値が-1のだけを抽出も同時に行えます
グループ化
レコードデータを特定列でグルーピングし、合計や平均、カウントなどを行うためのGROUP BYを使うことが可能です。さらにこのグループ化後のデータに対しては、HAVING句を用いて条件設定をすることで抽出する事も可能です。
1 |
SELECT uid,count(uid) AS `sharecnt` from SUBMASTER GROUP BY uid HAVING sharecnt > 0 |
上記の例では、SUBMASTERのuid列だけを取り出し、カウントの列としてsharecntを設ける。group byにてuid列をグルーピング化しています。また、havingにてsharecnt列の値が0より上のレコードだけを抽出するように条件設定も加えています。
count関数などの集計関数はグループ化していないと利用出来ませんし、このグループ化は基本最初に取り出したすべての列に対して設定しなければならないので、group byの後に全部の列が列挙されていないとエラーになります。どういった列だけ取り出しグループ化すべきかが悩むポイントです。
※但し、SQLiteはAccessのようなPivot句には未対応なので、クロス集計は出来ません。ので、アプリ側でピボット前のデータからクロス集計データを配列で作ってあげるなどの対応が必要になります。
図:集計では必ず使うグルーピング
トリガー
あるテーブルにデータを追加や更新を掛けた時に、別のテーブルに対してもなんらかのSQL文を自動で発行して作業をさせる為のものがトリガーで、例えばINSERT履歴を自動で残したり、レプリカのテーブルに自動で追加したり、プレ集計を行わせておいて実際の集計時間の短縮など、プログラミング無しで行わせる便利な反面、プログラミングほど自由度があるわけではないので、使い所は選ぶ機能です。
ビュー同様、DB Browser上ではSQL文を叩いて作成を行います。作成手順は以下の通りです。
- DB BrowserのExecute SQLを開く
- SQL文として、以下のような構文を入力する
1 2 3 4 |
CREATE TRIGGER トリガー名 UPDATE ON テーブル名 BEGIN insert into info (name) values(new.foldername); END |
上記の例だと、テーブル名に対して UPDATE をした時にBEGIN以下のSQL文を実行するという意味になります。自動実行するSQL文は複数設置可能で、UPDATE以外にINSERT、DELETEがあります。
SQL文ではnewをカラム名につける事で新しい値、oldをつけることで古い値を取得し、INSERT文などで利用することが可能です。
図:但しトリガーは多用するべからず
データベース暗号化
SQLiteにSQLCipherという仕組みを組み合わせて、セキュアなデータベース運用を行う事が可能です。DB Browser for SQLiteインストール時に通常はノーマルなSQLiteのみがインストールされますが、オプションでSQLCiperを選ぶと、暗号化対応版のDB Browser for SQLiteも別途インストールされます。
インストール後はすぐに使える状態になっており、
- ツールより「Tool」-> 「set encryption」を選ぶ
- パスワードを入力し、暗号化オプションを選び、OKをクリック
- セットされると再読み込みされて、パスワードを再度入力するとオープンできるようになります。
但し暗号化した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のオプション指定をしておきましょう。
- DB Browser for SQLiteで新規にデータベースを作成する
- タブのプラグマ編集を開く
- Auto Vacuumの項目を「Full」に指定する
- データベースにテーブルを作って保存する
Vacuum文はノイズの除去とフラグメント解消をしますが、Auto Vacuumはトランザクション -> コミットのたびにノイズ除去をしますが、フラグメント解消はしません。
図:Auto Vacuum設定はテーブル作成前に
SQLiteの読み書き
Node.jsなどのアプリでSQLiteに接続してデータを読み書きする場合、メソッド毎に読み書きの構文を書いているとプログラムが冗長になってしまうので、通常はSELECT, INSERT, UPDATE, DELETEの代表的な4構文に関しては関数化しておいたり、もう一歩進めてクラス化しておいて使うと、再利用性が高まって、見通しが良くなります。
冒頭部分
1 2 3 4 |
//SQLite3ファイルに接続 const sqlite3 = require('sqlite3').verbose(); var dbfile = __dirname + '/db.sqlite3'; var db = new sqlite3.Database(dbfile); |
Node.jsの場合、上記のコードのように予め接続するdb.sqlite3ファイルの場所を指定し、接続しておくようにします。基本的に個人で使うデータベースであるため、マルチユーザで同時書き込みをするようなものには用いないので(サーバで使う場合は別ですが)、自分の場合はMySQLの時のようにロックを考慮したりしません。また暗号化についてもデフォルトでは未対応であるため、別の手段を用意する必要があります。
必要な時に開いて、そして閉じるというシンプルな処理を4パターンに応じて作っています(実際には2パターン辺りまで統合できますが)
インメモリ化
以下のように、:memory:を加えると、SQLiteのインメモリデータベース化が可能です。
1 2 |
var sqlite = require("sqlite3").verbose(); var db = new sqlite.Database(":memory:",sqlite.OPEN_READWRITE | sqlite.OPEN_CREATE); |
open_readwrite, open_create, open_readonlyの3種類のモードがあります。
必要な時に接続し終了する
アプリで使用する場合、基本的に個人なので、冒頭でsqliteのファイルを読み込み開きっぱなしでそのまま運用する事が多いですが、必要な時にだけ接続して、SQL実行後にはきちんとクローズする場合には、冒頭で接続しっぱなしにするのではなく、都度インスタンス化してCloseさせます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
var sqlite = require("sqlite3").verbose(); var dbfile = __dirname + '/db.sqlite3'; function tomato() { //インスタンス化 db = new sqlite.Database(dbfile); //値 var values = ["tomato",100]; //SQL文を実行 db.run(`UPDATE yasai SET vname = ? WHERE id = ?`, values, (err) => { if (err){ return console.log(err); }else{ // データベースを閉じる db.close(err => { //エラーハンドリング if (err) return console.log(err); }); } }); } |
次にまた接続する場合は、再度、new sqlite.Databaseにてインスタンス化してから処理を行うことになります。
SELECT
SQL実行関数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
//SQLiteへのSQL発行(select) function sqliteselect(query,callback){ //SQLiteファイルからOAuth情報を取得する db.serialize(function(){ //SELECT文を発行する db.all(query,function(err,rows){ if(err){ //メッセージを表示 //メッセージオプション var options ={ type:'info', title:"エラーメッセージ", button:['OK'], message:'データ取得エラー', detail:String(err) } //表示する dialog.showMessageBoxSync(null,options); return; }else{ //取得したデータを変数に格納しておく callback(rows); } }); }); } |
クエリを受け取って実行し、成功したらrowsにレコードが入ってるので、callbackで呼び出し元へと返しています。SQL文ついては以下のようになります。もちろん、ビューに対してSELECT文を発行することも出来ます。
1 |
var query = "select * from manager where company = 1" |
呼び出し側
callbackされてきたrowsの値を取り出し、それぞれをretmanという連想配列にデータ部分と件数とにわけて入れ込み、Electronの場合はレンダラ側にevent.sender.sendで送っています。
1 2 3 4 5 6 7 8 |
var retman = {}; sqliteselect(query,function (rows){ //取得データを返す retman.data = rows; retman.count = rows.length; event.sender.send(sender, retman); return; }); |
指定件数ずつ取得する
一度に大量のデータを取得するとプログラム側にとって少々不具合が出るケースというものがあります。いきなり全件取得ですと表示するまでに時間が掛かるので、遅延ロードなどを併用して10000件ずつ取得して表示などなど。こういったケースでは、次の10000件を取得して既存のデータに追加するといった処理を装備しますが、SQLite側で「次の10000件を呼び出し」といったことをするためには、offset句とLimit句を利用します。
1 2 3 4 5 6 7 8 9 10 |
var query = "select * from jisseki limit 10000 offset " + offset + ";"; //クエリを実行 sqliteselect(query, function (rows) { //取得データを返す retman.data = rows; retman.count = rows.length; event.sender.send(sender, retman); return; }); |
上記の例の場合、offset変数に値を入れることで、データの取得位置(行番号)を変更出来るようになる。初期値は0とし次の1万件を取得したいならば、offsetには10000を入れる(0からスタートしてる点に注意)。つまりこれが、データの取得開始位置となる。
Limit句で10000件を取得するように同時に制限を入れないと、offsetの位置から後ろのデータを全部取得してしまうので注意。
INSERT
SQL実行関数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
//SQLiteへのSQL発行(insert) function sqliteinsert(query,values,table,callback){ db.serialize(() => { db.run(query, values,function(err) { if (err){ callback(["NG",err]); return; }else{ //最終行のrowidを取得する let lastrowid = this.lastID; //追加した行のAuto inrementのIDを取得して返す db.all("select *, rowid from " + table + " where rowid = " + lastrowid, function (err, row) { var retid = row[0].ID; callback(retid); }); } }); }); } |
レコードの追加の場合の処理です。続けてサブマスタにレコードを追加する場合を考慮して、追加時のAutoincrementで自動で挿入されるIDの数値を取得して返すようにしています。
SQL文と追加するvaluesについては以下のようになります。
1 2 3 4 5 |
//SQL文 var query = "insert into employee (empid,uname,mail,company) values (?,?,?,?);" //Valuesの例 var values = [selerec.empid,selerec.uname,selerec.mail,selerec.company]; |
valuesは配列で渡すのですが、二次元配列の形で渡せば、バルクインサートが可能になっています(但し、一度に沢山のレコードをprepareで追加するとNGで、1回900レコード程度の制限があるみたい)。
但し、大量のレコードを普通にInsertで1行ずつ入れていくととてつもなく遅いです。
呼び出し側
呼び出し側は上記の関数を呼び出し、処理が終わったらcallbackしてもらうように組んでいます。retに追加したレコードのIDが返ってくるので、それを取得して引き続きサブマスタへの追加処理へとつなげます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
sqliteinsert(query,values,"employee",function (ret){ if(ret[0] == "NG"){ //ダイアログオプション var options = { type:'error', title:"エラー", button:['OK'], message:'エラーが発生しました。', detail:ret[1] } dialog.showMessageBoxSync(null,options); event.sender.send("closeman"); return; } //終了処理 event.sender.send(retfunc); return; }); |
トランザクション処理
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構文としては
1 2 3 4 5 |
//通常のInsert処理 Insert Into MASTER (empid,uname,mail,company) values (?,?,?,?); //バルクインサートの処理 Insert Into MASTER (empid,uname,mail,company) values (val1,val2,val3,val4),(val5,val6,val7,val8), |
といったように、values以降に、値を格納したものを、カンマ区切りで並べてつなげれば、バルクインサートになります。しかし、あまりにも多くの値をつなげると、too many sql variableというエラーが発生してしまいます(SQLiteの制限)。こちらのサイトに制限についての記載があります。v3.32以前のSQLiteだと999個、それ以降だと32766個がデフォルト設定のようです。これは、?の部分が999個が上限ということですね。
しかし、Node.jsでこれをやろうとするとValuesの部分を構築するのが面倒であるため、例えば100個ずつに区分けして、Insert Intoを実行するようにし、2次元配列は1次元配列に変換して渡すようにするというテクニックがstackoverflowで紹介されています。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
//二次元配列のデータ var array = [[a1,a2,a3,a4],[a5,a6,a7,a8],[a9,a10,a11,a12],[a13,14,15,16]] //1レコード分のplaceholderを配列の数だけ用意 let placeholders = array.map(() => "(?, ?, ?, ?)").join(', '); //placeholderを加えたクエリ文を構築する let query = "INSERT INTO master (empid, empname, mail, age) VALUES " + placeholders; //値を格納する1次元配列 let flatArray = []; //2次元配列を1次元配列に変換する array.forEach((arr) => { arr.forEach((item) => { flatArray.push(item) }) }); //DBにInsert処理を実行 db.serialize(function(){ db.run(query, flatArray, function(err){ if(err) throw err; }); }); |
2次元配列を1次元配列にし、またレコードの数だけ必要なplaceholderを用意するという点が独特です。
100レコード単位でバルクインサート
SQLiteのInsert処理に於いて、db.runが非同期に実行されてしまうので、データの処理を考えて同期的に処理を実施しながら、100レコード単位でバルクインサートを実施してみた所、40,000行のデータでおよそ15秒程度で完了(17カラム)できました。これにはElectron側でこの全データをCheetah Gridで表示する所まで含めてこの値なので、当初のトランザクションも使わず、バルクインサートもせずにasync.eachSeriesで1件ずつトロトロインサートしていた時よりも遥かに高速にInsert処理が出来ました。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 |
//TSログインポート処理 ipcMain.on('tsimportdiag', function (event) { //メッセージオプション var options = { properties: ['openFile'], title: "ファイルの選択", filters: [ { name: 'TSログファイル', extensions: ['csv'] } ] } const filepath = dialog.showOpenDialogSync(null, options); var items = ""; if (filepath == undefined) { console.log("キャンセル") event.sender.send('message', "処理はキャンセルされました。"); return; } else { items = filepath[0] } //CSVファイル読み込み fs.createReadStream(items) .pipe(csv.parse({ columns: true }, function (err, data) { //JSONデータに変換する var jsondata = JSON.stringify(data); jsondata = JSON.parse(jsondata); var length = jsondata.length; //インポート用の配列 var array = []; //jsondataを加工して配列を組み直す for (var i = 0; i < length; i++) { //データが空を検知したら終了 if (jsondata[i]["会社コード"] == "") { continue; } //日付が空の場合は次のレコードへ if (jsondata[i]["日付"] == "") { continue; } //一時連想配列を用意 var temparr = []; //データ追加する temparr.push(jsondata[i]["会社コード"]); temparr.push(jsondata[i]["社員コード"]); temparr.push(jsondata[i]["社員名"]); temparr.push(jsondata[i]["勤務体系: 勤務体系名"]); temparr.push(paddingNull(jsondata[i]["日付"])); temparr.push(paddingNull(jsondata[i]["日タイプ"])); temparr.push(paddingNull(jsondata[i]["イベント"])); temparr.push(paddingNull(jsondata[i]["イベントタイプ"])); temparr.push(paddingNull(jsondata[i]["出社時刻・時"])); temparr.push(paddingNull(jsondata[i]["退社時刻・時"])); temparr.push(paddingNull(jsondata[i]["総労働時間"])); temparr.push(paddingNull(jsondata[i]["深夜労働時間"])); temparr.push(paddingNull(jsondata[i]["所定休憩時間"])); temparr.push(paddingNull(jsondata[i]["所定労働時間"])); temparr.push(paddingNull(jsondata[i]["実労働時間"])); temparr.push(paddingNull(jsondata[i]["休暇ID1: 勤怠休暇名"])); temparr.push(paddingNull(jsondata[i]["時間単位休暇ID1: 勤怠休暇名"])); //配列にpushする array.push(temparr); } //データの件数を検知 var dlength = array.length if (dlength == 0) { event.sender.send('message', "取り込めるデータの件数が0件でした"); return; } //100回単位で区切った場合のループ回数を計算(小数点以下切り上げ) var loopman = Math.ceil(dlength / 100) var query = "DELETE FROM tslog"; sqlitedelete(query, function (ret) { if (ret == "NG") { event.sender.send('message', "tslogテーブルのデータ削除に失敗しました。"); return; } else { //トランザクション処理開始 db.run("begin transaction") //バルクインサート処理を開始 topgun(loopman, array).then(result => { if (result == "OK") { //コミット db.run("commit"); //処理を完了 event.sender.send('tsimportend', 'TSログの取り込みが完了しました。'); } else { event.sender.send('message', 'ログ読み取り時にエラーが発生しました。'); } }); } }); })); }); //同期的にデータをバルクインサート(100レコード単位で) async function topgun(loopman,array){ //baseとなるクエリ var querybase = "INSERT INTO tslog (compid,empid,empname,kinmu,workdate,datetype,event,eventtype,starttime,endtime,totaltime,nighttime,resttime,syoteitime,worktime,restname,timerestname) VALUES "; //バルクインサート処理 for(var i = 0;i<loopman;i++){ //一時配列を用意 var temparray; //100件分取り出す temparray = await array.splice(0,100) //1レコード分のplaceholderを配列の数だけ用意 var placeholders = await temparray.map(() => "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)").join(', '); //placeholderを加えたクエリ文を構築する var query = querybase + placeholders; //値を格納する1次元配列 var flatArray = []; new Promise((resolve, reject) => { //2次元配列を1次元配列に変換する temparray.forEach((arr) => { arr.forEach((item) => { flatArray.push(item) }) }); resolve(flatArray); }).then((ret) => { db.run(query,ret); }).catch(e => { console.log('エラーを捕捉'); return "NG"; }) } return "OK" } |
100件分を取り出すspliceの項目。一番最後のInsert時には100指定しての場合、実際の配列の数以上の値を指定してしまってることになりますが、取得されるtemparray変数にはきちんと残りの未処理の配列分だけ取り出せています。エラーも出ず残りの配列数を気にすることなく切り出せるので、非常に便利です。spliceはsplice(0,100)で、0番から100個目までの配列を取り出すことになり、arrayから除去されますので、次のターンでもsplice(0,100)で次の200個を取り出せます。
UPDATE
SQL実行関数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
//SQLiteへのSQL発行(Update) function sqliteupdate(query,values,callback){ db.serialize(() => { db.run(query, values,function(err) { if (err){ //メッセージを表示 //メッセージオプション var options ={ type:'info', title:"エラーメッセージ", button:['OK'], message:'データ取得エラー', detail:String(err) } //表示する dialog.showMessageBoxSync(null,options); return; }else{ callback("OK"); } }); }); } |
レコードの更新処理の時の関数です。基本は更新が成功したかどうかだけを呼び出し元へと返します。SQL文とvaluesについては以下のようになります。
1 2 3 4 5 |
//UPDATE構文 var query = "update employee set empid=? retireflg=? where ID=3" //Values var values = [selerec.empid,selerec.retireflg]; |
Valuesについては、Insert処理同様にバルクインサート処理を行えば高速アップデートを実行可能です。
呼び出し側
呼び出し側は処理が完了したらOKだけを返すようにしています。エラー時にはダイアログ表示をして処理を終了します。
1 2 3 4 5 |
sqliteupdate(query,values,function (ret){ //終了処理 event.sender.send(retfunc); return; }); |
他のテーブルとJOINしてUPDATE
MySQLやAccessなどでは普通にできることなのですが、2つ以上のテーブルをJOINしてその値を元にUPDATEするということが、SQLiteの場合実行することができません。例えば以下のようなSQL文は実行が出来ないのです(Version 3.33より前のバージョン)。結構使うテクニックなので、このままだと非常に困ります。
▼以下の例はtslogのeventの値をtslog.workdate<->holiday_public.hdate2で連結して、holiday_publicのhnameで更新するクエリ
1 |
UPDATE tslog INNER JOIN holiday_public ON tslog.workdate = holiday_public.hdate2 SET tslog.event = holiday_public.hname; |
StackOverflowの解決法によると、この場合、値をSETするカラムに対してサブクエリを実行し、where条件にEXISTS句でも同じようなサブクエリを実行することで、上記のUPDATE文と同じ結果を得られるとのことで、実行してみましたが見事にアップデート出来ました。但し、大量のデータを更新するとサブクエリを実行してる関係で、非常に遅いです。
1 2 3 4 5 6 7 8 9 10 11 |
UPDATE tslog SET event = (SELECT holiday_public.hname FROM holiday_public WHERE holiday_public.hdate2 = tslog.workdate ) WHERE EXISTS ( SELECT * FROM holiday_public WHERE holiday_public.hdate2 = tslog.workdate ) |
上記の事例だと、1つの列の値を更新するだけで、複数の列を更新したい場合困ります。その場合は以下のような記述が可能です。
1 2 3 4 5 6 7 8 9 10 11 |
UPDATE tslog SET (tslog.event, tslog.eventflg) = (SELECT holiday_public.hname, holiday_pulic.hflg FROM holiday_public WHERE holiday_public.hdate2 = tslog.workdate ) WHERE EXISTS ( SELECT * FROM holiday_public WHERE holiday_public.hdate2 = tslog.workdate ) |
()で括って、カンマ区切りで列名を列挙し、SELECT文の側でも同様に列を列挙することで、指定の複数の列のアップデートをテーブル結合で実現出来ます。また、SQLite Version 3.33以降は、UPDATE FROMが使えるようになり、より簡単に記述が可能になり、以下のような書き方でもアップデートが可能です。
1 2 3 4 5 6 7 8 9 10 11 12 |
UPDATE tslog SET event = daily.event ,eventflg= daily.eventflg FROM daily WHERE daily.ID = tslog.dailyid; もしくは UPDATE testman SET (dailyname, restflg) = (daily.dailyname, daily.restflg) FROM daily WHERE daily.ID = testman.dailyid; |
DELETE
SQL実行関数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
//SQLiteへのSQL発行(Delete) function sqlitedelete(query,callback){ db.serialize(() => { db.run(query, function(err) { if (err){ //メッセージを表示 //メッセージオプション var options ={ type:'info', title:"エラーメッセージ", button:['OK'], message:'データ取得エラー', detail:String(err) } //表示する dialog.showMessageBoxSync(null,options); return; }else{ callback("OK"); } }); }); } |
更新時処理と殆ど同じなので、UPDATEとDELETEは一個の関数としても良いのですが、UPDATEの場合は今回は利用していませんが、Bulk Updateの処理も存在し得るので、Transaction処理を考慮して、別に分けています。SQL文としては以下のようになります。
1 2 |
//DELETE構文 var query = "DELETE FROM employee where ID=3" |
呼び出し側
基本的にはUPDATE文の時の関数処理と同じ。
1 2 3 4 5 |
sqlitedelete(query,function (ret){ //終了処理 event.sender.send("delmemresult"); return; }); |
テーブルを新規作成する
アプリのアプデ時に過去の版にSQLiteのファイルを上書きするようなことをしてしまうとデータが消えてしまいます。そこで、既存のDBに対してテーブルの有無チェック(後述)を行い、なかった場合には新規にテーブルを作ってあげないとアプリの運用ができなくなります。といったようなシーンや膨大なデータを新規のテーブルに切り出して分割するようなシーンでも使うのが「テーブルの新規作成」。作成時にはテーブル名、列の名前、データ型の指定が必要になります(デフォルト値のセットも出来ます)
1 2 3 4 5 6 7 8 |
//busyoテーブルの作成 var query = "CREATE TABLE 'テーブル名' ('ID' INTEGER, 'compid' INTEGER, 'busyo' TEXT, PRIMARY KEY('ID' AUTOINCREMENT))" //作成の実行 db.serialize(() => { console.log("テーブルを作成"); db.run(query); }); |
- Create Table文を使って新規に作成します。
- ‘列名’ 型 といった形でカンマ区切りで列を指定してゆきます。
- ID列等の場合は、Primary keyの指定が必要且つ、Autoincrementの指定をするのが定石です。
- db.runでクエリ文を実行するとテーブルが作成されて保存されます。
列を新規に追加する
テーブルの新規追加の他にも過去のアプリとの互換性の為などを主な目的として新規に列を追加するケースがあります。列の追加だけでなく削除や型の変更など様々なテーブル構造の変更を行う場合に使うのが「Alter文」です。但し、SQLiteの場合は以下のような制限があります。
- 列の追加などは一度に複数の列を追加できず、1列ずつ追加する事になる
- alter table ~ modify ~といった感じで、カラム構成の変更は出来ない。
- Primary Keyの列は変更出来ないし、新規に追加も出来ない
- unique指定も出来ません
- 追加時にデフォルト値をセットする事が出来るけれど、関数などの指定などは出来ない
などなど。結構制約が多いので多用することはないと思います。この当たりの制約をまとめたページがこちらにあります。既存のテーブルに同名の列チェックを行った後に(後述)、列を追加するSQL文を実行します。
1 2 3 4 5 6 7 8 9 10 |
//busyoおよびcompid列を追加 db.serialize(() => { //busyo列を追加 var mkcol = "ALTER TABLE ngemployee ADD COLUMN busyo TEXT"; db.run(mkcol); //compid列を追加 mkcol = "ALTER TABLE ngemployee ADD COLUMN compid INTEGER"; db.run(mkcol) }); |
- 2つ以上の列追加は、一列ずつ追加していく事になります。
- Alter Table ~ Add Column ~句を使って、列名と型を最低でも指定して実行します。
特定の列の存在有無チェック
アプリのバージョンアップを重ねていく内に、SQLiteのテーブル構造も変更するシーンが出てきます。そうなると、過去のアプリとデータベースの互換性が失われてしまうことがあります。それが「特定の列の有無」。後のバージョンで列を追加したのであれば、アプデ時にデータベースも上書きすれば問題無いですがデータも消えます。ということで、列の有無をチェックしてさらに「列を追加する」処理を装備すれば、データベースを上書きせずに、DB側を弄る事でユーザのアプデ上の障害を取り除けます。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
//対象のテーブルの情報を取得する var query = "pragma table_info('テーブル名')"; //SQLを実行する db.all(query, function (err, rows) { if (err) { console.log(err); }else{ //テーブルの列を一個ずつ走査する async.eachSeries(rows, function (rec, next) { //busyoが含まれているかチェック var colname = rec.name; if (colname == "対象の列の名前") { console.log("列あったよ") noflg = true; } //次の列名 next(); }, function (err) { //エラートラップ if (err) { //メッセージを返して処理を中断 console.log(err); return; } //列がなかった場合の処理 if (noflg == false) { } }); } }); |
- テーブル情報を取得するためのSQLは、pragma table_infoという変わったSQLを利用します。これで列情報が連想配列で返ってきます。
- ループで一個ずつ列をチェックして、対象の列の名前と一致するものがあったら、フラグを立てます
- フラグが建ってる場合は列が存在するので、処理をせず、ない場合には例えば列追加の処理を追記します。
図:列の名前や型がわかる
対象のテーブルの存在有無チェック
Node.jsなどのプログラムで、他のSQLite3のデータベースをインポートして入れ替えるといった仕組みの場合、バージョンを重ねるごとに、古いバックアップのdb.sqlite3ファイルには存在しないテーブルというものが出来ていくこともあるでしょう。しかし、当然のように普通にインポート処理をコードで記述してしまうと、存在しないテーブルに接続してデータを取り込もうとしてエラーが発生する事になります。
そこで、対象のSQLite3ファイルに「対象となるテーブルが存在しているかどうか?」をチェックするSQL文を発行して、存在しなければ処理をスルーするような仕組みが必要になります。この場合、以下のようなSQL文を構築し、存在すれば1、存在しなければ0が返ってくるので、それを元に処理を分岐させる事が可能です。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
//対象のテーブルが存在するかチェックする function sqliteexist(rec){ //SQL文を構築 var query = "SELECT COUNT(*) FROM sqlite_master where type='table' AND name='" + rec + "';"; //SQL実行 dbback.get(query,function(err,res){ if (err){ //ダイアログオプション var options = { type:'error', title:"エラー", button:['OK'], message:'エラーが発生しました。', detail:String(err) } dialog.showMessageBoxSync(mainWindow,options); return; }else{ //取得したデータを変数に格納しておく var countman = res["COUNT(*)"]; console.log(countman); return countman; } }); } |
- 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に接続しデータを扱う事が可能です。
- SQLite ODBC Driverをダウンロードしてインストールしておく(64bitだとsqliteodbc_w64.exeがそれになる)
- Windowsの検索窓から、ODBCデータソースアドミニストレータを開く(今回は64bit版)
- ユーザDSNが開かれてる状態なので、追加をクリック
- SQLite3 ODBC Driverを選択
- Data Sorce Nameは適当に付ける。Database NameはBrowseをクリックして、接続するdb.sqlite3ファイルを指定する
- OKをクリックする
- Accessを起動する
- 外部データ -> 新しいデータソース -> 他のソース -> ODBCデータベースを開く
- リンクテーブルを作成して、ソースデータにリンクするにチェックを入れてOKをクリック
- コンピュータデータソースをクリックし、5.で作ったデータ設定を選んでOKをクリック
- データベース一覧が出てくるので、リンクするものをクリックして選ぶ
- リンクテーブルが貼られるので、後は好きなように操作する
Accessでクエリを作り、そのSQL文はそのままDB Browser for SQLiteでも使えたりするので、Viewを作ったりする時にもAccessのUIは役に立ちます。
また、コピペでデータを流し込める上に、SQLiteの場合はaccdbファイルのような2GBの制限が無いので、大量のデータでこの制限に引っかかってるケースでも有効な手段になります。
VBAで手動で接続する場合には、以下のような接続文字列を作る事で接続が可能です(ActiveX Data Object 6.1 Library等を参照設定で指定しておく必要があります)
1 2 3 4 5 6 7 8 |
Public Function testdb() '接続文字列 Dim strconn As New ADODB.Connection strconn.Open "DRIVER=SQLite3 ODBC Driver;Database=db.sqlite3" strconn.Close Set strconn = Nothing End Function |
図:セットアップは至ってシンプル
図:リンクするテーブルを選ぶ
他のDBから自身のDBにデータをコピーする
事前準備
SQLiteは当たり前ですが、Accessのようなリンクテーブルが使えるわけではないので、他のsqliteファイルに入ってるデータを自身のsqliteファイルへデータをコピーするにはロジックを用意してあげる必要があります。
概要を言えば、
- 他のDBに対してSELECTでテーブルデータを取得し、変数に格納する
- 格納したデータ(JSON形式)を、バルクインサートで自身のDBへとINSERTする
- この時、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で圧縮する関数を作るのも良いです。
1 2 3 4 5 6 7 |
//ZIP圧縮用モジュール var archiver = require('archiver'); var sevenBin = require('7zip-bin'); var seven = require("node-7z"); archiver.registerFormat('zip-encryptable', require('archiver-zip-encryptable')); require('date-utils'); var zippass = "ここにZIPパスワードを入力"; |
ソースコード
今回のコードはElectronから呼び出して利用しています。
バックアップ時
バックアップ時の圧縮処理はそこまで難しくありません。保存場所を指定して圧縮を実行するだけ。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
function ziparchive(){ //ZIP形式でバックアップを実行 let foldername = dialog.showOpenDialogSync(setWindow, { properties: ['openDirectory'], title: 'バックアップ先指定', defaultPath: dir_desktop }); //フォルダ名を取得する var target = foldername[0]; //zipで圧縮を実行 zipman(target,function(ret){ //ファイル名を取得 var fname = ret; //圧縮完了通知を送る event.sender.send('zipok',fname); }); } //ZIPバックアップ用関数 function zipman(folname,callback){ //デスクトップパスdir_desktop //日付でファイル名を生成 var today = new Date(); var tempfname = today.toFormat("YYYYMMDDHH24MISS") + "_backup.zip"; var filename = folname + "//" + tempfname; //archiverの準備 var archive = archiver('zip-encryptable', { zlib: { level: 9 }, forceLocalTime: true, password: zippass }); //出力 var output = fs.createWriteStream(filename); archive.pipe(output); //圧縮実行 //db.sqlite3を圧縮 archive.file( dbfile, { name: 'db.sqlite3' }); //圧縮完了後 output.on("close", function () { //ZIPファイルパスをcallback callback(tempfname); }); //エラー発生時 archive.on('error', function(err){ throw err; }); //ZIP圧縮を行う archive.finalize(); } |
- ダイアログにて、保存場所を指定する
- zipman関数でZIP圧縮を行わせています。この時、ファイル名を日付で生成し、passwordで暗号化のフレーズを指定
- archive.fileにて今回は単一のファイルを暗号化ZIPで固めています。dbfileにはdb.sqlite3ファイルのフルパスを指定しています。
- 圧縮はarchive.finalizeで実行。完了後にoutput.on(“close” 以降が動き、呼び出し元へcallbackしています
- callbackする際には、生成した日付のファイル名を返してあげています。
解凍時
問題は暗号化ZIPで圧縮されたdb.sqlite3のファイルを解凍し、中に入ってるテーブルを一つずつ拾って、自身のテーブルにバルクインサートで処理をします。処理後には解凍したファイルを削除するのを忘れずに。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 |
function zipextract(){ //メッセージオプション var options ={ properties:['openFile'], title:"ファイルの選択", filters:[ {name:'バックアップファイル',extensions:['zip']} ] } const filepath = dialog.showOpenDialogSync(null, options); var items; if(filepath == undefined){ console.log("キャンセル") return; }else{ items = filepath[0] } //暗号化ZIPを解凍する zipmelt(items,function(ret){ //ファイル名を取得(ファイルのリスト) var fname = ret; //db.sqlite3のパスを構築して、インポート処理 var db2 = dir_desktop + "//" + fname[0]; //インポート処理に引き継ぐ backimport(db2); }); } //ZIP復元用関数 function zipmelt(filename,callback){ //ZIPファイルの場所と解凍先を指定 const zipPath = filename; const target = dir_desktop; //解凍ファイルリスト用 var extract = []; //解凍実行 const zipStream = seven.extractFull(zipPath, target, { password: zippass, $bin: sevenBin.path7za }) //解凍実行後処理 zipStream.on('end', () => { callback(extract); }) //解凍中 zipStream.on('data', function (data) { extract.push(data.file); }) } |
- zipmelt関数が解凍するメイン関数です。
- 解凍後にはファイルのパスを持って、backimport関数に渡しています。
- 解凍時に7zipのモジュールで暗号化解除し解凍を行っています。
- 今回は解凍先はデスクトップのパスを指定しています。
バルクインサート
さて、解凍したdb.sqlite3に接続し、自身のdb.sqlite3ファイルへはAccessのようには簡単に行きません。ましてや、今回のバルクインサートの場合はテーブル毎にカラムは異なりますし、当然placeholderの数も異なります。これらに対応して一括でトランザクション処理にて高速インサート処理をテーブルの数だけ繰り返す必要があります。また、処理後はインポート元のdb.sqlite3はcloseしないと、アプリが掴んだままの状態になってしまうので、必ずデータベースを閉じる処理が必要です。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 |
//バックアップのdb.sqlite3から復元する処理 function backimport(db2){ //DB接続処理 dbback = new sqlite3.Database(db2); //テーブル一覧 let tables = [ "company", "employee","holiday_private", "holiday_public","ts_holiday","tslog","mailTemplate" ] //トランザクション処理開始 db.run("begin transaction") //Promiseで順番に処理 new Promise((resolve, reject) => { //テーブルデータをクリアする async.eachSeries(tables,function(rec,next){ db.serialize(() => { db.run("DELETE FROM " + rec); }); //次の処理 next(); },function(err){ //エラートラップ if(err){ //メッセージを返して処理を中断 mainWindow.webContents.send('message', err); return; } //インサート処理へ resolve("OK"); }); }).then((ret) => { //インサート処理 async.eachSeries(tables,function(rec,next){ //クエリを構築 var squery = "SELECT * FROM " + rec; //インポート元に接続してデータを取り出し sqliteselect2(squery,function (rows){ //データの件数を検知(0件の場合は処理をスルーする) var dlength = rows.length if(dlength == 0){ next(); return; } //カラムデータを生成する var keys = Object.keys(rows[0]); var columns = keys.toString(); //カラムの数だけプレースホルダを生成する var temprec = Object.keys(rows[0]).map(function (key) {return "?"}); var placeman = "(" + temprec.toString() + ")"; //JSONデータを配列に変換 var array = []; for(var i = 0;i<rows.length;i++){ //1レコード取り出す var record = rows[i]; //連想配列を配列に変換 var json = Object.keys(record).map(function (key) {return record[key]}); //配列にpush array.push(json); } //100回単位で区切った場合のループ回数を計算(小数点以下切り上げ) var loopman = Math.ceil(dlength / 100) //クエリを構築 var querybase = `insert into ${rec} (${columns}) values `; topgun2(loopman,querybase,placeman,array).then(result => { if(result == "OK"){ //処理を完了 next(); }else{ //エラー発生時 dbback.close(); //解凍したファイルを削除する try{ fs.unlinkSync(db2); }catch(e){ } mainWindow.webContents.send('message', "復元時にエラーが発生しました"); } }); }); },function(err){ //コミットとクローズ処理 db.run("commit"); dbback.close(function(){ //解凍したファイルを削除する try{ fs.unlinkSync(db2); }catch(e){ } }); //エラートラップ if(err){ //メッセージを返して処理を中断 mainWindow.webContents.send('message', err); return; } //インポート処理完了 mainWindow.webContents.send('restoreend'); }); }).catch(e => { //エラー処理 mainWindow.webContents.send('message', e); return; }); } //復元時用のバルクインサート関数 async function topgun2(loopman,querybase,placeman,array){ //バルクインサート処理 for(var i = 0;i<loopman;i++){ //一時配列を用意 var temparray; //100件分取り出す temparray = await array.splice(0,100); //1レコード分のplaceholderを配列の数だけ用 var placeholders = await temparray.map(() => placeman).join(', '); //placeholderを加えたクエリ文を構築する var query = querybase + placeholders; //値を格納する1次元配列 var flatArray = []; new Promise((resolve, reject) => { //2次元配列を1次元配列に変換する temparray.forEach((arr) => { arr.forEach((item) => { flatArray.push(item) }) }); resolve(flatArray); }).then((ret) => { db.run(query,ret); }).catch(e => { console.log('エラーを捕捉'); return "NG"; }); } return "OK" } //SQLiteへのSQL発行(select) function sqliteselect2(query,callback){ //SQLiteファイルから全データを取得する dbback.serialize(function(){ //SELECT文を発行する dbback.all(query,function(err,rows){ if(err){ //メッセージを表示 //メッセージオプション var options ={ type:'info', title:"エラーメッセージ", button:['OK'], message:'データ取得エラー', detail:String(err) } //表示する dialog.showMessageBoxSync(null,options); return; }else{ //取得したデータを変数に格納しておく callback(rows); } }); }); } |
- テーブル一覧の配列をもって回し、まずは自身の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について詳細に記述していますので、合わせてご覧ください。
HTML側コード(レンダラプロセス)
Vuetify mount時の処理
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
//データのトータル件数を取得 var temparr = { args: "gettotalrec", args2: "SELECT COUNT(*) as maxrec FROM jisseki;", args3: "message" //エラー時に受け取る側 } ipc.send("getSelectData", JSON.stringify(temparr)); //データのMax件数を取得 ipc.on("gettotalrec", (arg) => { //ローダーを停止 vm.isLoading = false; var data = arg.data; rectotal = data[0].maxrec; //データをセットする gridman.dataSource = dataSource; }); |
- VuetifyのMountにてデータのトータル件数を取得させておきます。帰ってきた値はIPCの受け手であるgettotalrecで取得
- rectotalに件数を格納したら、後述のCheetah Gridの変数であるgridmanのdatasourceとしてdatasource変数を指定します。
datasource変数とリクエスト用関数
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
//IPC通信用 var ipc = window.api; //auto pagingの設定用変数 const GET_RECORDS_SIZE = 10000; //1度のリクエストで要求するレコード件数 const BUFFER_RECORDS_SIZE = (Math.floor(GET_RECORDS_SIZE / 2) || 1); const loadedData = {}; let isAllLoaded = false; var rectotal = 10000; //レコードの最大件数を格納 //データソースのロードを行う変数 const dataSource = new cheetahGrid.data.CachedDataSource({ get(index) { const loadStartIndex = Math.floor(index / GET_RECORDS_SIZE) * GET_RECORDS_SIZE; if (!loadedData[loadStartIndex]) { const promiseObject = getRecordsWithAjax(loadStartIndex, GET_RECORDS_SIZE).// return Promise Object then((data) => { console.log(data.length) if (isAllLoaded) { return data; } // length update? const length = loadStartIndex + data.length; if (data.length < GET_RECORDS_SIZE) { // all loaded!! dataSource.length = length; isAllLoaded = true; } else if (dataSource.length <= length) { // append length!! dataSource.length = length + BUFFER_RECORDS_SIZE; } return data; }); loadedData[loadStartIndex] = promiseObject; } return loadedData[loadStartIndex]. then((data) => data[index - loadStartIndex]); }, length: BUFFER_RECORDS_SIZE, //init records count }); //1万件ずつSQLiteにデータをリクエストする関数 function getRecordsWithAjax(startIndex, num) { return new Promise((resolve) => { //ラストレコードかどうかを判定 const loadedCount = startIndex + num; let last = false; if (loadedCount >= rectotal) { num = rectotal - startIndex; last = true; } (async () => { var recman = await ipc.doAction(startIndex); var records = []; //取得したデータをrecordsに追加する for (var i = 0; i < num; i++) { //recdataからデータを取り出してrecordsにpushする records.push(recman[i]); } //取得データを返す resolve(records); })(); }); } |
- Cheetah Gridのデータソースにはdatasource変数を割り当ててます。
- 全件ロードが完了しているかどうかを判定して、スクロールで一番下まで行った時にgetRecordWithAjax関数で次の1万件をリクエストしています。
- レコード全件数と現在のindexを比較して、まだ取得出来る場合には、ipc.doActionにてメインプロセス側にリクエストを同期的に行い、返り値をrecords変数にpushしてdatasourceに返しています。
Cheetah Gridの初期化
Vuetify Mount時に初期化させています。gridmanというグローバル変数に格納しています。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
gridman = new cheetahGrid.ListGrid({ //グリッドを表示する要素を指定 parentElement: document.querySelector('#gridarea'), theme: userTheme, //Gridのヘッダー行 header: [ { field: 'ID', caption: 'ID', width: 130 }, { field: 'kijunday', caption: '基準日', width: 100 }, ・・・・・ 中略 ・・・・・ ], //カラム固定の指定 frozenColCount: 3, }); gridman.configure('fadeinWhenCallbackInPromise', true); |
- 初期化時にはgridmanに対してはデータソースを指定していません。データの総件数を取得した時にセットするようにしています。
- gridareaというIDを持つdivに対してCheetah Gridを表示しています。
preload.js(ContextBridge)
1 2 3 4 5 6 7 8 9 |
const { contextBridge, ipcRenderer} = require("electron"); contextBridge.exposeInMainWorld( "api", { doAction: async (arg) => { return await ipcRenderer.invoke('an-action', arg); } }, ); |
- IPC通信で利用するpreload.js側のコードです。メインとレンダラをつなぐ中間に位置するものになります。
- doActionという関数を用意し、asyncにてレンダラ側から呼び出し、メインプロセスにipcRenderer.invokeにて送り込み、返り値をレンダラ側に返します。
- invoke自体は非同期実行ですが、awaitをつけることで帰ってくるまで同期的に待機します。
- メインプロセス側のIPCの受け手であるan-actionに対して引数付きで渡しています。
JS側コード(メインプロセス)
1 2 3 4 5 6 7 8 9 10 11 12 |
ipcMain.handle('an-action', async (event, arg) => { //クエリを構築 var query = "select * from jisseki order by ID DESC limit 10000 offset " + arg + ";"; //同期的にSELECT文を発行 return new Promise((resolve, reject) => { db.all(query, (err,rows) => { if (err) reject(); else resolve(rows); }); }); }); |
- レンダラ側からリクエストを受け付けるan-action側です。
- 10000件のlimitをつけ、ID列を降順、offsetとして引数の値で指定した位置から取得します。
- db.allにPromiseで括ることで同期的に処理を行わせています。
- 取得したデータはresolveで返します(通常はinvokeで受けた場合はreturnで返すのですが、Promiseを使ってるので、resolveになります。
関連リンク
- Node.jsでSQLiteの書き込み
- SQLiteデータ型の仕様とセキュリティ問題
- SQLite3 Error – cannot rollback – no transaction is active
- SQLiteで複数の行を1行のINSERT文でまとめて追加する
- SQLiteについて
- SQLiteの暗号化
- better-sqlite3-sqlcipher
- SQLiteの注意点3つ!テーブル作成と同時にINDEX貼れないetc
- [SQLite]Bulk insert最大レコード数は約270,000行
- FOREIGN KEY制約(外部キー制約を設定する)
- (SQLite)FULL OUTER JOINの代用方法
- julianday関数
- 文字列を別の文字列に置換(replace関数)
- 日付と時刻を取得する(date関数, time関数, datetime関数, julianday関数, strftime関数)
- Why is my JulianDay returning null when trying to get number of days between two dates
- Python と SQLite3 で pivot を実現する
- Node.jsでSQLiteデータベースのCRUD処理を行う
- SQLiteの暗号化
- Node.jsでSQLite3にトランザクション張って列追加したり一括更新したり
- 【SQLite入門】AutoIncrement の使用方法と注意すること
- sqlite3 1万件を超えるならUPDATE/DELETEは使っちゃダメ
- 【SQLite3】 ハイパフォーマンスなインデックスをつけるために覚えておくべきこと
- 【SQLite】で限界を極めろ!
- Node.js sqlite3: very slow bulk inserts
- too many SQL variables: , while compiling: DELETE FROM sample_tbl WHERE rowid IN (?,?,?,…
- データの挿入:バルクインサート
- Inserting multiple rows with multiple columns in Node and sqlite3
- MySQL Triggerのデメリットが大きいので使わないで欲しい
- Copying data from one DB to another with node-sqlite – formatting the ‘insert’ statement
- SQLiteのVACUUMメモ
- SQLで高速化を考えた時にできるテクニック・コツをまとめて紹介!
- Using the SQLite Online Backup API
- SQLiteProvider
- Update table values from another table with the same user name
- SQLiteでINNER JOIN してUPDATEさせるには
- SQLiteでテーブルを結合しているときにUPDATEを作成するにはどうすればよいですか?
- Electron IPC通信を行う方法まとめ
- [Electron] contextBridge経由でIPC通信を行う
- ElectronでのIPCの例(send, sendSync, invoke, etc.)
- [Electron] IPC には新しい ipcRenderer.invoke() メソッドを使ったほうが便利 (v7+)
- Electron(v.15.0.0 現在)の IPC 通信入門 – よりセキュアな方法への変遷
- [Electron]contextBridgeでipcRenderer.invokeを使う