未分類

HOME
未分類
SQLiteデータベースの活用まとめ - 後編

2022年8月4日 / 最終更新日時 : 2025年1月1日 officeの杜未分類

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

前回の記事、前編の続きである後編になります。データ量が多くなってしまったので、前後編で分けました。前編の記事は以下のエントリーになります。

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

1 他のDBから自身のDBにデータをコピーする
- 1.1 事前準備
- 1.2 ソースコード
2 Cheetah GridのAuto Pagingで同期的に取得
3 関連リンク

他の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で圧縮する関数を作るのも良いです。

//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から呼び出して利用しています。

バックアップ時

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

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のファイルを解凍し、中に入ってるテーブルを一つずつ拾って、自身のテーブルにバルクインサートで処理をします。処理後には解凍したファイルを削除するのを忘れずに。

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しないと、アプリが掴んだままの状態になってしまうので、必ずデータベースを閉じる処理が必要です。

//バックアップの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について詳細に記述していますので、合わせてご覧ください。

Google Apps ScriptでVuetify v2を使ってUIを作る【GAS】

HTML側コード（レンダラプロセス）

Vuetify mount時の処理

//データのトータル件数を取得
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変数とリクエスト用関数

//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というグローバル変数に格納しています。

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）

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側コード（メインプロセス）

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になります。

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

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

事前準備

ソースコード

バックアップ時

解凍時

バルクインサート

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

HTML側コード（レンダラプロセス）

Vuetify mount時の処理

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

Cheetah Gridの初期化

preload.js（ContextBridge）

JS側コード（メインプロセス）

関連リンク

コメントを残すコメントをキャンセル

Google Apps ScriptでSVGを使ったイメージマップを操作 – 応用編【GAS】

ElectronとMicrosoft365で座席表アプリを作ってみた

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

事前準備

ソースコード

バックアップ時

解凍時

バルクインサート

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

HTML側コード（レンダラプロセス）

Vuetify mount時の処理

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

Cheetah Gridの初期化

preload.js（ContextBridge）

JS側コード（メインプロセス）

関連リンク

コメントを残す コメントをキャンセル

Google Apps ScriptでSVGを使ったイメージマップを操作 – 応用編【GAS】

ElectronとMicrosoft365で座席表アプリを作ってみた

コメントを残すコメントをキャンセル