2021年7月5日 / 最終更新日時 : 2021年9月28日 officeの杜 Electron

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

ElectronやNode.jsなどで業務用アプリを作るとなると、割と避けられないのがデータベースの使用。サーバー型のMySQLやPostgreSQLを使うことになると思いますが、オフラインでの使用やスマフォ用となると、SQLiteがなんだかんだ行ってもベターです（NoSQLデータベースもありますが）。

今回は自分の為のメモとしてSQLiteを扱うアプリでのコードスニペットや知見として色々とまとめておこうと思います。（言語はNode.jsで操作をしています）

リンク

1 今回使用するライブラリ等
2 データベースの作成
3 SQLiteの読み書き
4 他のDBから自身のDBにデータをコピーする
- 4.1 事前準備
- 4.2 ソースコード
5 関連リンク

今回使用するライブラリ等

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文として、以下のような構文を入力する

CREATE VIEW VIEWの名前 AS SELECT * FROM MASTER;

1	CREATE VIEW VIEWの名前 AS SELECT * FROM MASTER;

CREATE VIEW VIEWの名前に続けて、SELECT文を続ければ良いだけ。SQLiteはシンプルなので方言的にはAccessほどキツくはないので、SQL文を作るのが苦手な人は、Accessのクエリで作ったSQL文でもここにCREATEに続けてコピペでも、ビューを作ることは可能です。

※ビューが存在する場合、それを用いたテーブルに変更を加える場合も同様に一旦、ビューを削除しなければなりません。

図：ビューを作成する画面

テーブルの結合

テーブルの結合には、内部結合、外部結合、ユニオンの大きく３つがあり、他にも交差結合や自然結合などが用意されています。ビューを作成する上で、２つ以上のテーブルを連結してビューを作成するには必須です。

内部結合

連結する列においてお互いのテーブルで同じ値を持つものだけを抽出して連結します。マスタ側にしか無いようなデータの場合には、ビューには出てきません。必ずマスタ・サブマスタ両方に同じID等を持つデータが必要になります。

SELECT * FROM MASTER INNER JOIN SUBMASTER ON MASTER.ID = SUBMASTER = MID

1	SELECT * FROM MASTER INNER JOIN SUBMASTER ON MASTER.ID = SUBMASTER = MID

上記の例では、masterテーブルのIDとsubmasterのMID列を連結し、お互いに同じ値を持つレコードが抽出されます。

外部結合

連結する列において片方のテーブルにだけデータがある場合でも抽出してくれる連結方法です。マスタ側にだけデータがあってサブマスタに無い場合でも、マスタ側のデータが抽出されるようになります（サブマスタ側の値は空っぽのままになります）。

SELECT * FROM MASTER LEFT OUTER JOIN SUBMASTER ON MASTER.ID = SUBMASTER.MID

1	SELECT * FROM MASTER LEFT OUTER JOIN SUBMASTER ON MASTER.ID = SUBMASTER.MID

但し、SQLiteはRIGHT OUTER JOINをサポートしていないので、サブマスタ側からマスタ側への外部結合は利用出来ません。

ユニオン

２つの全く同じカラムと型を持つテーブルを１つのテーブルに合体させる、Accessでいうユニオンクエリに該当するものです。例えば支店毎にテーブルを作成しておき、各々の支店はそちらのテーブルを参照。管理者はこれらを結合させたユニオンビューを持って処理をするといったようなセグメントを設ける場合に利用します。

SELECT * FROM SIBUYA UNION SELECT * FROM SHINJUKU

1	SELECT * FROM SIBUYA UNION SELECT * FROM SHINJUKU

上記の例では、SHIBUYAテーブルとSHINJUKUテーブルは全く同じタイプのテーブルで、２つを１つのテーブルにUNION句を使って結合させます。但し注意点として、この場合のビューでお互いにある連番の列（Accessで言う所のオートナンバーに該当）する列は、重複しないような値にするべきでしょう。また、そのレコードがどちらのテーブルに該当するレコードなのかの見分けがつかないと、コードを書く時に面倒です。

日付の差分を取る

SQLiteは日付型というものが列の型に存在しないので、例えば２つの日付の差分を取る場合どうしたら良いのか？と言うと、用意されてる関数をSQL文内で利用して差分を取る事が可能です。また、この時の日付はyyyy/mm/ddではなくyyyy-mm-ddの形式である必要があるので、よくある日付の型の場合には変換が必要になります。

例えばサブマスタの日付列の値と現在の日時からの日付差分をdiffdateとして演算する場合は以下のような構文になります。

SELECT *, 
CAST(julianday(replace(SUBMASTER.expire,'/','-')) - julianday('now') as integer) AS `diffdate` 
FROM SUBMASTER

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として取り出した

条件判定をする

Accessなどでは、計算列として特定の値の内容を元に条件判定した結果をIIF関数を使って処理することが結構あります。クエリ上で数式を構築して判定が出来るのでコードの節約にもなりますが、これをSQLiteで実現する場合、IIF関数が使えるわけではないので、躓くポイントになります。またその時に「○○を含む場合」といったケースでは、Like演算子を使う点は同じですが、ワイルドカードの指定方法が異なるので、ここも含めて

「特定の列の値に【固定】という文字列が含まれる場合には、-1を、そうでない場合0を返し、judgmentという列で表示する」

を実現する場合以下のようになります。多数含まれるデータに対して行ったのでグループ化していますが、グループ化は必須項目ではありません。

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

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句を用いて条件設定をすることで抽出する事も可能です。

SELECT uid,count(uid) AS `sharecnt` from SUBMASTER GROUP BY uid HAVING sharecnt > 0

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文として、以下のような構文を入力する

CREATE TRIGGER トリガー名 UPDATE ON テーブル名
BEGIN
insert into info (name) values(new.foldername);
END

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構文に関しては関数化しておいたり、もう一歩進めてクラス化しておいて使うと、再利用性が高まって、見通しが良くなります。

冒頭部分

//SQLite3ファイルに接続
const sqlite3 = require('sqlite3').verbose();
var dbfile = __dirname + '/db.sqlite3';
var db = new sqlite3.Database(dbfile);

//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のインメモリデータベース化が可能です。

var sqlite = require("sqlite3").verbose();
var db = new sqlite.Database(":memory:",sqlite.OPEN_READWRITE | sqlite.OPEN_CREATE);

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させます。

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);
});
}
});
}

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実行関数

//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);
}
});
});
}

//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文を発行することも出来ます。

var query = "select * from manager where company = 1"

1	var query = "select * from manager where company = 1"

呼び出し側

callbackされてきたrowsの値を取り出し、それぞれをretmanという連想配列にデータ部分と件数とにわけて入れ込み、Electronの場合はレンダラ側にevent.sender.sendで送っています。

var retman = {};
sqliteselect(query,function (rows){
//取得データを返す
retman.data = rows;　
retman.count = rows.length;
event.sender.send(sender, retman);
return;
});

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句を利用します。

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;
});

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実行関数

//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);
});
}
});
});
}

//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については以下のようになります。

//SQL文
var query = "insert into employee (empid,uname,mail,company) values (?,?,?,?);"
//Valuesの例
var values = [selerec.empid,selerec.uname,selerec.mail,selerec.company];

//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が返ってくるので、それを取得して引き続きサブマスタへの追加処理へとつなげます。

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;
});

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構文としては

//通常の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),

//通常の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で紹介されています。

//二次元配列のデータ
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;
});
});

//二次元配列のデータ

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処理が出来ました。

//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

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実行関数

//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");
}
});
});
}

//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については以下のようになります。

//UPDATE構文
var query = "update employee set empid=? retireflg=? where ID=3"
//Values
var values = [selerec.empid,selerec.retireflg];

//UPDATE構文

var query = "update employee set empid=? retireflg=? where ID=3"

//Values

var values = [selerec.empid,selerec.retireflg];

Valuesについては、Insert処理同様にバルクインサート処理を行えば高速アップデートを実行可能です。

呼び出し側

呼び出し側は処理が完了したらOKだけを返すようにしています。エラー時にはダイアログ表示をして処理を終了します。

sqliteupdate(query,values,function (ret){
//終了処理
event.sender.send(retfunc);
return;
});

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で更新するクエリ

UPDATE tslog INNER JOIN holiday_public ON tslog.workdate = holiday_public.hdate2 SET tslog.event = 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文と同じ結果を得られるとのことで、実行してみましたが見事にアップデート出来ました。

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
)

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実行関数

//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");
}
});
});
}

//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文としては以下のようになります。

//DELETE構文
var query = "DELETE FROM employee where ID=3"

1 2	//DELETE構文 var query = "DELETE FROM employee where ID=3"

呼び出し側

基本的にはUPDATE文の時の関数処理と同じ。

sqlitedelete(query,function (ret){
//終了処理
event.sender.send("delmemresult");
return;
});

sqlitedelete(query,function (ret){

//終了処理

event.sender.send("delmemresult");

return;

});

テーブルを新規作成する

アプリのアプデ時に過去の版にSQLiteのファイルを上書きするようなことをしてしまうとデータが消えてしまいます。そこで、既存のDBに対してテーブルの有無チェック（後述）を行い、なかった場合には新規にテーブルを作ってあげないとアプリの運用ができなくなります。といったようなシーンや膨大なデータを新規のテーブルに切り出して分割するようなシーンでも使うのが「テーブルの新規作成」。作成時にはテーブル名、列の名前、データ型の指定が必要になります（デフォルト値のセットも出来ます）

//busyoテーブルの作成
var query = "CREATE TABLE 'テーブル名' ('ID' INTEGER, 'compid' INTEGER, 'busyo' TEXT, PRIMARY KEY('ID' AUTOINCREMENT))"
//作成の実行
db.serialize(() => {
console.log("テーブルを作成");
db.run(query);
});

//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の場合は以下のような制限があります。

列の追加などは一度に複数の列を追加できず、１列ずつ追加する事になる
alter table ～ modify ～といった感じで、カラム構成の変更は出来ない。
Primary Keyの列は変更出来ないし、新規に追加も出来ない
unique指定も出来ません
追加時にデフォルト値をセットする事が出来るけれど、関数などの指定などは出来ない

などなど。結構制約が多いので多用することはないと思います。この当たりの制約をまとめたページがこちらにあります。既存のテーブルに同名の列チェックを行った後に（後述）、列を追加するSQL文を実行します。

//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)
});

//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)

});

２つ以上の列追加は、一列ずつ追加していく事になります。
Alter Table ～ Add Column ～句を使って、列名と型を最低でも指定して実行します。

特定の列の存在有無チェック

アプリのバージョンアップを重ねていく内に、SQLiteのテーブル構造も変更するシーンが出てきます。そうなると、過去のアプリとデータベースの互換性が失われてしまうことがあります。それが「特定の列の有無」。後のバージョンで列を追加したのであれば、アプデ時にデータベースも上書きすれば問題無いですがデータも消えます。ということで、列の有無をチェックしてさらに「列を追加する」処理を装備すれば、データベースを上書きせずに、DB側を弄る事でユーザのアプデ上の障害を取り除けます。

//対象のテーブルの情報を取得する
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) {
}
});
}
});

//対象のテーブルの情報を取得する

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が返ってくるので、それを元に処理を分岐させる事が可能です。

//対象のテーブルが存在するかチェックする
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;
}
});
}

//対象のテーブルが存在するかチェックする

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等を参照設定で指定しておく必要があります）

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

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

//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パスワードを入力";

//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();
}

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);
})
}

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);
}
});
});
}

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のファイルは削除をしておく

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

今回使用するライブラリ等

データベースの作成

データベースの型

インデックスの作成

外部キー制約

インポート・エクスポート

エクスポート処理

インポート処理

ビューの作成

テーブルの結合

内部結合

外部結合

ユニオン

日付の差分を取る

条件判定をする

グループ化

トリガー

データベース暗号化

Vacuum

SQLiteの読み書き

冒頭部分

インメモリ化

必要な時に接続し終了する

SELECT

SQL実行関数

呼び出し側

指定件数ずつ取得する

INSERT

SQL実行関数

呼び出し側

トランザクション処理

バルクインサート

バルクインサートの基本

100レコード単位でバルクインサート

UPDATE

SQL実行関数

呼び出し側

他のテーブルとJOINしてUPDATE

DELETE

SQL実行関数

呼び出し側

テーブルを新規作成する

列を新規に追加する

特定の列の存在有無チェック

対象のテーブルの存在有無チェック

AccessからODBC接続

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

事前準備

ソースコード

バックアップ時

解凍時

バルクインサート

関連リンク

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

Puppteerで別のマシンのChromeを操作する

他のスプレッドシートのシートをコピーする

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