「管理表」や「一覧表」にまだExcel使ってる？SQLiteで簡単RDB化のすすめ

情シス担当のますいです。
業務で「管理表」や「一覧表」を作成する機会は多いですよね。しかし数が増えるとデータを最新に保つのが大変です。
たとえば、弊社ではかつて人の異動やPCの入れ替えがあるたびに「資産管理表」「ソフトウェアライセンス割り当て表」「IPアドレス管理表」など様々なExcelファイルを更新する必要がありました。同じ情報を複数のファイルに同じように書き込む必要があって、運用していくうちに不整合が多発していました。
こんなとき、技術者ならこう考えることでしょう。「こういうデータってRDBで管理した方が楽だよな」と。

Excelの操作感とリレーショナルなデータ管理の両立

RDBによるデータ管理は整合性の面では運用管理がしやすいですが、更新のためにいちいちSQLを書くのは不便。MS Access を利用するのも1つの手ですが、XLOOKUPを駆使したりPower Queryを使ったりして「ExcelでRDBっぽい運用」をしている方もいらっしゃるのではないでしょうか。
または、サイボウズ社のキントーンなどのクラウドベースで業務アプリを作成できるツールや、Excelライクな操作感をもったクラウドデータベースサービスを利用するという方法もあるでしょう。
しかし、手間はかけたくない、でも予算はつかないとお悩みのみなさんにお勧めの方法があります。

SQLiteでExcelのように運用する

SQLiteという簡易RDBMSをご存じでしょうか？
SQLiteは単一ファイルにデータが保存される軽量なデータベースです。サーバを立てる必要はなく、ランタイムさえあれば動作するのでPCに簡単にインストールすることができますし、ファイル1つコピーするだけでバックアップできるので運用も簡単。なお、パブリックドメインで公開されていて無料で使用することができます。
SQLiteのデータベースを操作できるフリーソフトやOSSを用いることで、Excelのような操作感でデータベースの作成や更新が可能になります。

SQLiteをWindowsにインストールする

SQLiteの公式ダウンロードページから、「Precompiled Binaries for Windows」の中にある「64-bit DLL (x64) for SQLite」をダウンロードし、zipファイル内にある sqlite3.dll を C:¥Windows¥System32 などパスの通ったディレクトリに置いてください。
たったこれだけで準備完了です。コマンドラインによる操作もしたい場合は、sqlite-tools-win32-x86 もダウンロードし、同じようにパスの通ったディレクトリ内に解凍したファイルを配置してください。

DB Browser for SQLite をインストールする

SQLiteデータベースを操作できるOSS、DB Browser for SQLite をインストールしましょう。ダウンロードページで、自分の環境に合ったファイルをダウンロードしてインストール（または解凍）してください。ライセンスは MPL-2.0、GPL-3.0 となっているので念のためご確認を。

DB Browser for SQLite でDBを作成してみる

DB Browser for SQLite を起動し、「新しいデータベース」ボタンを押して適当な名前を付けてファイルを作成する。

テーブル定義のウインドウが開くのでテーブル名を記入し、「追加」ボタンを押してカラムを追加していきます。使用できるデータ型は、INTEGER, TEXT, BLOB, REAL, NUMERIC の五種類です。制約もつけることができ、
- ［NN］Not Null
- ［PK］Primary Key
- ［U］Unique
の各チェックボックスにチェックすることで定義できます。［AI］にチェックを入れると、オートインクリメントするプライマリキーになります。

テーブルの作成

試しに「members」というテーブルを作成してみます。member_idをプライマリキーとして、名前と所属部署を入れるカラムを作りました。

データの入力

では早速このテーブルにデータを入力してみます。「データ閲覧」タブを開くとmembersテーブルが見えています。
レコード追加ボタンを押すと行が1つ追加されるので、セルに直接データを書き込んでいきます。member_idカラムはオートインクリメントにしたので数値が自動的に入力されています。

入力したら「変更を書き込み」ボタンを押しましょう。これを押すことでコミットされます。コミットせずにファイルを閉じてしまうと保存されません。また、変更を始めてからコミットするまでの間はデータベース全体がロックされ、他の人が開いてもコミットすることができません。こまめにコミットするようにしましょう。

Excelで作成したデータをコピペで貼り付けることもできます。必要な分だけ先に行を追加する必要はありますが、Excelから簡単にデータを移植できます。
カラム名のところをクリックするとそのカラムで昇順・降順にソートされ、「フィルター」欄に文字列を入力すると部分一致でフィルタリングされて表示されるので特定のデータを抽出するのにも便利です。

ビューの作成

SQLiteは標準的なDDL、DMLに対応しています。DB Browser for SQLite では「SQL実行」タブ内で実行することができます。
PC資産のテーブルを作成し、membersテーブルと結合して所有するPCのホスト名一覧を表示するビューを作成してみましょう。

ビュー作成文

CREATE VIEW v_pcuser AS
SELECT
  members.name,
  members.section,
  group_concat(pc.host_name) host_name
FROM
  members
  left outer join pc on members.member_id = pc.member_id
GROUP by
  members.name

スクリプトでSQLiteにデータを書き込む

何らかの情報を収集して定期的に更新したり、大量のデータを一斉に更新するような場合、スクリプトでSQLを実行してデータを更新することも可能です。

SQLite3 ODBC Driverのインストール

スクリプトからSQLiteを利用する場合は、SQLite3 ODBC Driver をインストールしましょう。このサイト http://www.ch-werner.de/sqliteodbc/ で sqliteodbc_w64.exe をダウンロードしてインストールしてください。

JScriptでSQLiteデータベースを更新するサンプル

membersテーブルの"千葉一"さんを"営業部"に異動するSQLを実行するスクリプト（JScript）を書いてみます。

// コネクションオブジェクトの作成と接続
var objConn = WScript.CreateObject("ADODB.Connection");
objConn.ConnectionString = "DRIVER=SQLite3 ODBC Driver;Database=" + DB_FILE_PATH;
objConn.Open();

// コマンドオブジェクトの作成
var objComm = WScript.CreateObject("ADODB.Command");
objComm.ActiveConnection = objConn;

// SQLの生成と実行
try {
    objComm.CommandText = "UPDATE members SET section = ? WHERE name = ?";
    objComm.Parameters(0).Value = "営業部";
    objComm.Parameters(1).Value = "千葉 一";
    objComm.Execute();
} catch (e) {
    WScript.Echo(e.message);
}

// コネクション切断
objConn.Close();
objComm = null;
objConn = null;

これを update.js として保存し、コマンドプロンプトから cscript update.js と実行すると更新されます。
DB Browsser for SQLite 上で [F5] キーを押すか、テーブル選択プルダウンの隣にあるデータ更新ボタンを押すと更新後のデータが表示されます。