ちょうどいいMicrosoft Access的な・・・
みなさん、こんにっちゃ。突然ですが、MacOSにはAccessが無いんですよね。
Accessのようなリレーショナルデータベース（RDB）は、Excelなどと違い、スキーマ（テーブルの構造）を定義することで他のシステムから安心して（一貫性を保って）データを扱うことができます。
また、Accessは複数のテーブルをJoinしたビューを、そのまま編集できた（と思う）ので、管理用UIとしても必要最低限のものはそろっています。MySQLなどの他のRDBは、UIは別に用意しないといけませんし、Joinしたまま編集できるUIはないような気がします。
Google Spreadsheet をデータベースにするにも・・・
また、最近はWeb上で表計算ができるGoogle Spreadsheetがありますが、これをデータベース代わりにして、GAS(Google Apps Script)やスクリプトでWebアプリやAPIを作ることも増えてきているみたいです。SpreadsheetからBigQueryや他のRDBにリンクしたり、もできるようですね。
しかし、Spreadsheetはしょせん表計算であり、RDBの一貫性管理の機能はありません。また、RDBのUIとしても少し足りないところがあります。特に、テーブルのJoin時に必ず必要になる、外部キー制約周りが、「帯に長し、たすきに短し」状態になってしまいます。
Spreadsheet + 関数 + GAS で一貫性管理
そこで、ここではGoogle Spreadsheet+いくつかの関数+GASを使って、Google Spreadsheetでテーブルのスキーマ管理と、外部キー制約の管理を導入してみます。
長くなるので、今回はデモの紹介にして、次回、コード等を共有しようと思います！
リレーショナルデータモデルの復習
復習ですが、RDBでは、1つ以上のテーブルを管理します。テーブルとは、1つ以上属性（表計算的に言うと列・カラム）をもち、属性は一つの属性名（表計算的に言うと1行目のヘッダ）と属性値の集合(表計算的に言うと2行目以降のセル)を持ちます。各行をタプルと言います。
属性のうち一つは主キーと宣言することができて、これには、各行のIDに相当する、ユニークな（重複の無い）値を持たせます。
Spreadsheetで困る外部キー
さて、ここまでは表計算でまねすることができるのですが、困るのは外部キーです。外部キーとは、他のテーブルの主キーのことです。
例えば、名簿のテーブルusersと、本の貸出帳簿のテーブルbooksを扱うとしましょう。貸出帳簿booksの方には、誰が本を借りているかを記録したいので、usersテーブルの利用者ID、つまり主キーを記録することで、いわゆる本と人の「紐付け」をすることができます。
このときの、booksの方に書かれた利用者IDを、 外部キー と呼びます。また、booksから見てusersテーブルを外部テーブルと呼びます。
通常の手書きの帳簿であれば、貸出帳簿の方に、利用者IDなしで名前だけ、とか、利用者IDに加えて名前も、書きたくなってしまいますが、これをやるとRDB的にはダメなのです。データベースが正規形でなくなる、と言いますが、例えば、その方の名前が変わった時なんかに、usersテーブルの方だけを名前を変えると、貸出帳簿の方とどちらが正しいの？という話になります。RDBの思想は、データに一貫性がないとだめ、なのです。
しかし、これをUI的に考えると、貸出帳簿の方では、利用者名を別のテーブルと見比べながら、あじけの無い1,2...とかいうIDだけで利用者管理しないといけないことになります。これは、使いにくいですよね？
表計算には、これに関係するUIとして、ドロップダウン（またはプルダウン）というものがあります。別のシートなどに、選択候補リストを書いておいて、その中から選ばせるやつですね。これは選択肢が分かる意味で便利なので、つい、貸出帳簿booksに「利用者名」という列を足して、そこでusersの氏名を選択肢として選ばせたくなります。しかし、これがまたRDB的にはアウトなのです。だって、上記の正規形でなくなる、のと同じく、貸出帳簿の方に利用者IDという外部キーではなく、利用者名を書いちゃってますよね？
・・・どうしたもんでしょう？
Visibleキーという考え方
以上をふまえて、ここでは、Visibleキーという考え方を導入します。この言葉は私が勝手につけたのですが、要は、見て分かるキーをつけましょう、という感じです。正確には、今やあまり使われていない（と思われる）複合主キー、つまり複数の属性を組み合わせて主キーと見なしましょう、という考え方と同じなのですが、人が見て分かる、という気持ちを込めてVisibleキーと呼んでみます。
具体的には、例えばusersテーブルの方では、IDと氏名を結合して、"1-田中"をVisibleキーとします。これを、booksテーブルの方から外部キーとして参照するのです。そうすれば、上記のドロップダウンでも、あじけの無い数字では無く、"1-田中"、"2-井上"、と言うように意味のある値を参照できるので、少し味（？）が出てきて、選択しやすくなります。
おっと、賢いあなたには、「いやいや、Visibleキーだと、利用者の名前が変わったらどうするの。」と言われそうです。そうです、その問題は発生しますが、ここはVisibleキーを明示的に管理して、関数とGASで更新もVisibleキーに反映させることで、解決しようと思います。
というわけで、
以下のような機能をGASで作りましたので、紹介します。
- スキーマ定義機能: スキーマを定義すればそれを元にテーブルを作成、更新してくれます。（更新は完全では無いですが。）
- 各タプル（行）の、IDや、作成日や更新日の自動付与機能： タプルを編集すると、その行にIDやタイムスタンプをつけてくれます。RailsなどのORマッパーにある機能ですね。
- 外部キー参照機能: Visibleキーを使って、外部キーをうまく参照し、見やすい形で編集できます。
- 外部キーに基づいた、外部テーブルの属性値参照機能: 外部キーだけでなく、外部テーブルの他の属性値も、Joinする感じで表示することができます。
今回はデモのみということで、次回コード等を紹介しようと思います。
Demo
では、デモ行きます。
今回は、先ほどからの簡単な例と同じく、名簿が書いてあるusersテーブル、そして本を誰が借りているかを管理するbooksテーブルの2つを考えてみます。
テーブルを1つ作成
まず、schemaという名前のシートを一つ用意しておきます。
ここに、各テーブルのスキーマを書きます。これの書式は次のようになっています。
- tableで始まる数行が、1テーブルのスキーマです。 この行のB列には、テーブル名users等を書きます。
- 次の行が属性名。最初のKey（Visibleキー）, created_at, updated_at, IDの列までは必須とします。
- 次の行がテンプレート行。新規行を追加したときにこれがそのままコピーされますので、書式や入力規則も設定できます。
- 次の行が制約。unique制約、外部キー制約などを書けます。
これらを書いて、メニューから「スキーマ反映」を実行すると、usersテーブルに相当するusersシートが追加されますね。
ちなみにテンプレート行の1列目では、Visibleキーを、先ほどのように、「ID-氏名」のようにここで設定しています。これが、テーブルの編集時にテーブルに反映されます。
行のInsert
このテーブルにデータを挿入してみます。
- 最後に追加するには、メニューから「新規行追加」を選ぶと、上記のテンプレート行からコピーされます。
- そうでなくても、直接書き込んでもテンプレート行が反映されます。
- その際に、Key, created_at, updated_at, IDが自動的に更新されます！ Railsのような機能ですね。
ここでは新規行の追加の例をお見せしましたが、行の更新(Update)や削除(Delete)も、通常の表計算のように操作できます。
Key, created_at, updated_at, IDの属性は必須で、自動更新としていますが、見せたくないなら、非表示にすればいいかと思います。
テーブルをもう一つ作成
次にbooksテーブルを追加してみます。
- schemaシートの下の方に、テーブル定義をコピペします。
- テーブル名を書きます。
- 属性名も、右の方に追加します。
- もう一度、メニューから「スキーマ反映」を実行すると、booksシートができあがります。
こんな感じで、複数テーブルを作ることができます。
カラム追加
booksテーブル定義に、カラムを追加してみます。
- 今回は「貸出者」および貸出者の「email」を追加してみます。ここで、
- 「貸出者」は、外部キー 、つまり、usersテーブルのキーを参照するものとします。
- 「email」は、外部キーのテーブルの属性、つまり、usersテーブルの貸出者のemailアドレスとします。これは、SQLで言うところのJoinをしたものに相当します。
- そこで、カラム「貸出者」「email」という属性を追加し、その2行下に、それぞれusers, users.emailと記入します。この働きについては次項をご覧ください。
- メニューから「スキーマ反映」を実行すると、booksシートに反映されます。
外部キーを含むテーブルにInsert
今度は、booksシートで、外部キーを含んだテーブルに追記してみます。
- booksシートにデータを追記します。
- 「書名」はこれまで通り、直接入力できます。
- 外部キーである「退出者」については、外部テーブルから ドロップダウンで選択できるようになっています！ ここは、 Visible Keyなので、IDを数字で選んでも、だれのことだか分からない、と言うこともありません！
- そしてさらに、外部キーでJoinされた「email」についても、外部キーを選択すれば、自動的に外部テーブルから対応する内容が反映されます！
つまり、前項でschemaシートの制約の行に書いたものは、次のような意味でした。
- users: 外部キーのテーブル名。自動的に、外部テーブルのKeyを参照するようになります。
- users.email: usersテーブルのemail属性。上記の外部キーが設定されていなければ機能しませんが、設定されていればJoinされたテーブルのように機能します。
外部テーブルを更新してみる
では、賢いあなたからの疑問である、「いやいや、Visibleキーだと、利用者の名前が変わったらどうするの。」を試してみましょう。
ここでは、外部テーブルであるusersの氏名と、メールアドレスを変更してみます。
- usersの「名前」と、「email」を変更してみます。ここでは、 "井上" → ”今田”, "i＠example.com" → "m＠example.com" に変更してみます。
- そうすると、usersシートのKeyの列も、自動的に "1-井上" から "1-今田" に変わりました。
- では、それを参照するbooksテーブルはどうでしょうか？ booksシートを見てみると、ちゃんと、"今田"、"m＠example.com"に更新されています！ 気持ちいいですね！
まとめ
GASベースでRDBの一部の機能を実現しているので、性能は良くないと思いますが、リレーショナルデータベースの基本的な一貫性管理はしたい、という用途には使えるのではないでしょうか。
もう少し、いじりたいところも残っていますので、それらを整備して、次回、コード等を公開しますね。