SQLiteデータベースの活用まとめ - 後編
前回の記事、前編の続きである後編になります。データ量が多くなってしまったので、前後編で分けました。前編の記事は以下のエントリーになります。
リンク
目次
他の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を使う
