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

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

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

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

事前準備

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

概要を言えば、

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

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

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

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

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

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

ソースコード

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

バックアップ時

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

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

解凍時

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

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

バルクインサート

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

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

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

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

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

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

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

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

Vuetify mount時の処理

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

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

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

Cheetah Gridの初期化

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

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

preload.js(ContextBridge)

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

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

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

関連リンク

コメントを残す

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

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