前書き
皆さん、こんにちは！
プロダクツ本部 プロダクツエンジニアグループ ビジネスアプリケーションチーム(長いｗ)のMGKです。
私は社内業務アプリケーションの開発を担当しております。日々のお客様の大事な注文やお問い合わせを対応するカスタマーサポートセンターや物流センターのオペレーターは、各工程に最適化された業務アプリケーションを使っています。
これらの業務アプリケーションは99.99%自社開発で作られています。その中には、Webアプリケーションもあればデスクトップアプリケーション、ハンディターミナル用のアプリケーションもあります。これらをいかに高速で使いやすくするかが、我々のミッションです。
技術そのものを最新にすることも重要ですが、既存の技術でも古い技術でも業務改善に繋がるのであれば迷いなく採用し、業務システムを現場に最適化や改善を進めています。
最終的にお客様に「なる早」で商品をお届けしたい、ニーズに答えたい、そのためには別に古くても「変わった」ことでもなんでも良し！
人気がなくなった技術って学びたくないと思ってるエンジニアもいると思いますが、最新技術打からと言って、すべてを解決してくれるものではありません。
ドアは鍵を握る人がどう回すかによって、開ける閉めるが行われますね。古くても最適した使い方すればそれが最適であり、最新の使い方だと思います。(CやC++が一番最速のプログラムを作る一番古い技術であることは間違いないですね)
ちょっと変わったSQLiteの使い方
今日紹介したいのはちょっと変わったSQLiteの使い方です。
SQLiteが何か、どんなものか、基本的な使い方はグーグル検索から簡単に確認できますのでここでは割愛させていただきます。
ではどんな変わった使い方でしょうか。
業務アプリケーションを使ったことがある方はわかると思いますが、いろんなプルダウンの選択肢があります。各テーブルの値はすべて意味を持ってます。項目の分類を選択して検索することでオペレーターは目的のデータにたどり着くことになります。
もちろんフリーキーワードから全文検索のような動きもありますが、大量のデータから複数条件に合致するデータを探すにはあまり向いていません。オペレーターは常にリアルタイムで動く注文データや、今投稿されたばかりのお問い合わせを確認しなければなりません。キャッシュやSolrなどを使うこともなかなか難しいです。
使う側からみるとあまり気にしないかもしれませんが、作る側から見るとプルダウンの数が増える分だけ、その数の分のDBアクセスになります。プルダウンを表示するために毎回マスターを取得することになります。しかも表示するマスターが多い画面になると取得から表示するまで遅延が発生します。1テーブルの検索には0.1秒もかかりませんが、数があると画面を開くときに若干のラグ発生させることになります。
あまりに気にしない方はいるかもですが、私は最初に画面を開くときに「ワンテンポ」遅れることがすごく気になります。
本当に「なんか引っかかる」ような人感覚的なレベルですが、一度わかってしまうとずっときになってしまいますね。
特に朝の業務開始の時間とお昼休み明けは、全オペレーターが一斉にアプリケーションを起動することになるのでDBアクセスも集中してしまいます。ミリ秒単位のロックが多発すると妙なロックが発生します。小さいマスターテーブルなのでIndexを追加するなどチューニングもできない状況です。
この問題を解消したくていろいろ工夫をしてみました。試してなんとか解決できたとしても「美しくない」状態だったり、力技でできたように見えるものの、すっきりしませんでした。
そこにSQLiteの超軽量・超高速の特徴に着目して、改善策を実施してみました。
簡単に言うとプルダウンとなる各マスター系のデータをすべてSQLiteに保存して画面を開く際に使う。と言うことです。
「わーー！すごい！ーーいいね！」じゃないですよね。「え？それだけ？」だと思うと思います。が、言いたいのはそれではありません。結果はあってますが、そのやり方の紹介となります。数多いマスターテーブルをSQLiteに保存するにはそのテーブルの生成やデータの取得、保存するためのコードを書く必要があります。また、新しいテーブルが追加された場合は同じことを追加して各アプリケーションのリリースをすることになります。
もう考えただけで「面倒くさいな～」という気持ちになりますね。アプリケーションの高速化だけではなくシステム改修も楽になる方法を探した結果が今回のご紹介したい内容になります。
実際のロジックを見ましょう
SQLiteはMysql、OracleなどRDBMSと似てますが少し違いますね。ただ、よ～く見てみると共通点があります。今回はMysqlとの連動なのでMysqlのデータタイプに合わせた説明となります。まずはテーブル作成が必要です。なるべく本テーブルと似た形でテーブルを作成したいです。
SQLiteのデータタイプを大きく5種類でまとめました。
TEXT : CHAR,VARCHAR,NCHAR,NVARCHAR,TEXT,CLOB,ENUM,DATE,DATETIME,TIME,TIMESTAMP
NUMERIC : NUMERIC,DECIMAL,BOOLEAN
INTEGER : INT,TINYINT,SMALLINT,MEDIUMINT,BIGINT,UNSIGNED INTEGER
REAL : REAL,DOUBLE,DOUBLE PRECISION,FLOAT
BLOB : BLOB,その他TEXT※DATEタイプは本来Numericタイプになるが日付の比較など不便のため、TEXTとして登録する。
参考：https://qiita.com/TomK/items/132831ab45e2aba822a8
右側に並べてるのはMysqlのデータタイプです。
ターゲットになるマスターテーブルの定義を取得します。
string sqlDesc = $"DESC {dbName}.{tableName}";
DataTable dt = SelectDao.Select(sqlDesc);あとはテーブルデータタイプにあわせてSQLiteのCreate文を自動生成します。
/// SQLite CreateSqlを自動生成
public static string MakeSQLiteCreateSql(string dbName, string tableName, List colList, DBProperties.CNST_SERVER server_id)
{
//TEXT : CHAR,VARCHAR,NCHAR,NVARCHAR,TEXT,CLOB,ENUM,DATE,DATETIME,TIME,TIMESTAMP
//NUMERIC : NUMERIC,DECIMAL,BOOLEAN
//INTEGER : INT,TINYINT,SMALLINT,MEDIUMINT,BIGINT,UNSIGNED INTEGER
//REAL : REAL,DOUBLE,DOUBLE PRECISION,FLOAT
//BLOB : BLOB,その他
string typeText = "CHAR,VARCHAR,NCHAR,NVARCHAR,TEXT,CLOB,ENUM,DATE,DATETIME,TIME,TIMESTAMP";
string typeNumeric = "NUMERIC,DECIMAL,BOOLEAN";
string typeInteger = "INT,TINYINT,SMALLINT,MEDIUMINT,BIGINT,UNSIGNED INTEGER";
string typeReal = "REAL,DOUBLE,DOUBLE PRECISION,FLOAT";
string typeBlob = "BLOB";
string sqlResult = "";
string sqlDesc = $"DESC {dbName}.{tableName}";
StringBuilder sql = new StringBuilder();
sql.AppendLine($" {tableName} (");
using (DataTable dt = SelectDao.Select(sqlDesc, null, server_id))
{
int cnt = 0;
foreach (DataRow dr in dt.Rows)
{
string colField = "";
string colType = "";
string colNull = "";
string colKey = "";
string strField = ((string)dr["Field"]).Trim();
string strType = ((string)dr["Type"]).Trim();
string strNull = ((string)dr["Null"]).Trim();
string strKey = ((string)dr["Key"]).Trim();
if (!colList.Contains(strField))
{
continue;
}
if (strType.IndexOf("(") > -1)
{
string[] arr = strType.Split('(');
if (arr.Length > 1)
{
strType = arr[0];
}
}
if (cnt > 0) { sql.Append(","); }
colField = strField;
strType = strType.ToUpper();
if (typeText.IndexOf(strType) > -1) colType = "TEXT";
else if (typeNumeric.IndexOf(strType) > -1) colType = "NUMERIC";
else if (typeInteger.IndexOf(strType) > -1) colType = "INTEGER";
else if (typeReal.IndexOf(strType) > -1) colType = "REAL";
else if (typeBlob.IndexOf(strType) > -1) colType = "BLOB";
else colType = "BLOB";
if (string.Equals(strNull, "NO")) colNull = "NOT NULL";
if (string.Equals(strKey, "PRI")) colKey = "PRIMARY KEY";
sql.AppendLine($" {colField} {colType} {colNull} {colKey} ");
cnt += 1;
}
sql.Append($" ) ");
sqlResult = sql.ToString();
}
return sqlResult;
}このコードの結果は各マスターテーブルのCreate文となります。これをSQLite側で実行することでMysql側とほぼ同じタイプのテーブルが生成されます。
/// SQLiteDBを自動生成。
/// Primary Keyが複数はNG
public static int CreateSQLiteTable(bool dropCreateFlg, string dbName, string tableName, List colList, DBProperties.CNST_SERVER serverId)
{
int result = 0;
StringBuilder sql = new StringBuilder();
if (dropCreateFlg)
{
sql.AppendLine($" DROP TABLE IF EXISTS {tableName};");
sql.AppendLine(" CREATE TABLE IF NOT EXISTS ");
}
else
{
sql.AppendLine(" CREATE TABLE IF NOT EXISTS ");
}
string createSql = DaoUtils.MakeSQLiteCreateSql(dbName, tableName, colList, serverId);
sql.Append(createSql);
Debug.WriteLine(sql.ToString());
UpdateDataSingle(sql.ToString(), DBProperties.SQLiteDobar, null);
//データ取得
int cnt = 0;
string strColumn = "";
sql = new StringBuilder();
foreach (string s in colList)
{
if (cnt > 0)
{
sql.Append(",");
}
sql.Append(s);
cnt += 1;
}
strColumn = sql.ToString();
string selectSql = string.Concat("SELECT ", strColumn, " FROM ", dbName, ".", tableName);
DataTable dt = SelectDao.Select(selectSql, null, serverId);
cnt = 0;
sql = new StringBuilder();
foreach (string s in colList)
{
if (cnt > 0)
{
sql.Append(",");
}
sql.Append($"@{s}");
cnt += 1;
}
string strColumnD = sql.ToString();
cnt = 0;
string insertSql = $" REPLACE INTO {tableName} ({strColumn}) VALUES ";
StringBuilder sqlCol = new StringBuilder();
foreach (DataRow dr in dt.Rows)
{
if (cnt > 0) { sqlCol.Append(","); }
int cntCol = 0;
sqlCol.Append("(");
foreach (string s in colList)
{
if (cntCol > 0) { sqlCol.Append(","); }
sqlCol.Append(string.Concat("'", dr[s], "'"));
cntCol += 1;
}
sqlCol.Append(")");
cnt += 1;
}
insertSql = insertSql + sqlCol.ToString();
result = UpdateDataSingle(insertSql, DBProperties.SQLiteDobar, null);
Debug.WriteLine(result);
return result;
}対象となるテーブルのリストさえ渡せば、ぐるぐるしながらマスターテーブルを生成してくれますのでテーブルの形がそれぞれ異なっても心配ないですし、いくら多くてもいちいち対応する必要もありません。
アプリケーションを起動時に、このリストを1回だけ実行することで最新のマスターテーブルが各オペレーターのローカル環境に生成されます。誰にも邪魔されない自分専用のデータベースです。
アプリケーションのプルダウンの表示はローカルのSQLiteデータを参照することで超高速にプルダウンを作成することができました。画面開く時の微妙なタイムラグもなくなりました。
このやり方の活用方法はいろんな場面にあります。データの編集ログを残したいとき、データ更新するたびにDBに編集ログを保存することになりますが、データの編集ログをリアルタイムでためる必要が無い場合はローカル環境のSQLiteにためておいて業務終了時にサーバに一括アップロードすることも考えられますね。
SQLiteに保存できるデータの量はユーザー環境にも影響しますが、数百万件のデータでも問題なく一瞬で更新できます。
最後に
今日はSQLiteを使って繰り返しマスター系のプルダウン生成を行うためにローカルデータベースを生成することで、実機への接続を減らし快適なアプリケーション開発に使えるTipsを紹介しました。
業務アプリケーションを開発するときは状況にあわせて工夫をすることが多いです。必ずしも最新技術じゃないといけないことはありません。既存の技術の少し考え方を変えた使い方をすることで大きな効果を発揮することがあります。
技術が新しいかどうかでよりも「使い方が新しいか」の考え方はいかがですか？発想の転換は新しい技術と同じだと思います。
SQLiteをどの場面で使えばいいか迷う方や業務アプリケーション運用の方に参考になればと思います。
以上！
