SQLiteデータベースの活用まとめ
ElectronやNode.jsなどで業務用アプリを作るとなると、割と避けられないのがデータベースの使用。サーバー型のMySQLやPostgreSQLを使うことになると思いますが、オフラインでの使用やスマフォ用となると、SQLiteがなんだかんだ行ってもベターです（NoSQLデータベースもありますが）。
今回は自分の為のメモとしてSQLiteを扱うアプリでのコードスニペットや知見として色々とまとめておこうと思います。
今回使用するライブラリ等
DBの作成やビューの構成などは今回はDB Browser for SQLiteを利用して作成します。Windows,macOS,Linuxとマルチプラットフォームにアプリが用意されています。
データベースの作成
データベースの型
SQLiteは他のデータベースと異なり、けっこう型の扱いはラフに作られています。サーバー型のDBやAccessなどから比較しても、型の取り扱いが厳格ではないので、扱いやすくスマフォのアプリなどでも不動の地位を築いています。
大きく5つの型が用意されており、列の型になります。
- Integer – 整数値を格納
- Text – テキストを格納（UTF8,UTF16で格納）
- Blob – バイナリデータを格納（ファイル等）
- Real – 浮動小数点を格納
- Numeric, Null – なんでも型（Accessの変数で言う所のVariantみたいなもの）
となっています。BooleanやDateといった型が存在せず、関数などで処理を行うことになります。この辺りがおいおい開発を行う上での壁になります。
図：５つの大きな型だけがある
インデックスの作成
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の２つを利用します。
図：データの更新漏れを防ぐのに有効
インポート・エクスポート
テーブルを作成後、プログラムを作るにあたってやバックアップを取る為の機能がDB Browserには備わっています。インポートではSQLファイルおよびCSVファイル、エクスポートではSQLファイル、CSVファイル、JSONファイルに対応しています。
プログラム作成時のサンプルデータを用意するのにこの機能を使ってテストをしましょう。
エクスポート処理
以下の手順でファイルをエクスポート出来ます。CSVの場合は１つのテーブルを1ファイルとして、SQLファイルの場合は1個のファイルにして出力します。また、SQLファイルの場合は出力時にインポートする時のInsert文に対してのオプション設定を行うことが可能です。ファイル -> Exportで実行します。
図：CSVの場合の出力画面
図：SQLの場合の出力画面
インポート処理
出力しておいたCSVファイルやSQLファイルをSQLiteのデータベースにインポートする機能です。インポートには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に続けてコピペでも、ビューを作ることは可能です。
※ビューが存在する場合、それを用いたテーブルに変更を加える場合も同様に一旦、ビューを削除しなければなりません。
図：ビューを作成する画面
テーブルの結合
テーブルの結合には、内部結合、外部結合、ユニオンの大きく３つがあり、他にも交差結合や自然結合などが用意されています。ビューを作成する上で、２つ以上のテーブルを連結してビューを作成するには必須です。
内部結合
連結する列においてお互いのテーブルで同じ値を持つものだけを抽出して連結します。マスタ側にしか無いようなデータの場合には、ビューには出てきません。必ずマスタ・サブマスタ両方に同じ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をサポートしていないので、サブマスタ側からマスタ側への外部結合は利用出来ません。
ユニオン
２つの全く同じカラムと型を持つテーブルを１つのテーブルに合体させる、Accessでいうユニオンクエリに該当するものです。例えば支店毎にテーブルを作成しておき、各々の支店はそちらのテーブルを参照。管理者はこれらを結合させたユニオンビューを持って処理をするといったようなセグメントを設ける場合に利用します。
|
1 |
SELECT * FROM SIBUYA UNION SELECT * FROM SHINJUKU |
上記の例では、SHIBUYAテーブルとSHINJUKUテーブルは全く同じタイプのテーブルで、２つを１つのテーブルにUNION句を使って結合させます。但し注意点として、この場合のビューでお互いにある連番の列（Accessで言う所のオートナンバーに該当）する列は、重複しないような値にするべきでしょう。また、そのレコードがどちらのテーブルに該当するレコードなのかの見分けがつかないと、コードを書く時に面倒です。
日付の差分を取る
SQLiteは日付型というものが列の型に存在しないので、例えば２つの日付の差分を取る場合どうしたら良いのか？と言うと、用意されてる関数をSQL文内で利用して差分を取る事が可能です。また、この時の日付はyyyy/mm/ddではなくyyyy-mm-ddの形式である必要があるので、よくある日付の型の場合には変換が必要になります。
例えばサブマスタの日付列の値と現在の日時からの日付差分をdiffdateとして演算する場合は以下のような構文になります。
|
1 |
SELECT *, CAST(julianday(replace(SUBMASTER.expire,'/','-')) - julianday('now') as integer) AS `diffdate` FROM SUBMASTER |
- 日付がyyyy/mm/ddの場合はreplace関数でスラッシュをハイフンに変換してあげます。
- 日付の差分として取りたい場合は一旦、日付の値をjulianday関数で数値に変換します。
- julianday(‘now’)にて現在の日付の数値を取得出来ます
- 差分を取る場合には、上記の関数で変換した２つの値をマイナスで差し引きし、CAST関数でinteger型に変換して列名を設定します。
図：expireの日付から現在日付の差分をdiffdateとして取り出した
グループ化
レコードデータを特定列でグルーピングし、合計や平均、カウントなどを行うための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; }); |
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文は実行が出来ないのです。結構使うテクニックなので、このままだと非常に困ります。
▼以下の例は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 ) |
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; }); |
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のファイルは削除をしておく
特に、実際の業務用データなどが入ってる場合には、レコード数が数万を超えて入ってる可能性があるため、復元時はバルクインサート処理でなければ、非常に時間が掛かってしまうので、このような汎用的なバルクインサートの実装は必要不可欠です。
関連リンク
- Node.jsでSQLiteの書き込み
- SQLiteデータ型の仕様とセキュリティ問題
- 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を作成するにはどうすればよいですか？
