Google SpreadsheetでRDBのように一貫性管理するGAS（デモ編）

## ちょうどいい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-氏名」のようにここで設定しています。これが、テーブルの編集時にテーブルに反映されます。

![1createTable.mov.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/678049/c77b2431-0f18-9d92-dfac-2844cd303a55.gif)


### 行のInsert

このテーブルにデータを挿入してみます。

- 最後に追加するには、メニューから「新規行追加」を選ぶと、上記のテンプレート行からコピーされます。
- そうでなくても、直接書き込んでもテンプレート行が反映されます。
- その際に、**Key, created_at, updated_at, IDが自動的に更新されます！** Railsのような機能ですね。

ここでは新規行の追加の例をお見せしましたが、行の更新(Update)や削除(Delete)も、通常の表計算のように操作できます。
Key, created_at, updated_at, IDの属性は必須で、自動更新としていますが、見せたくないなら、非表示にすればいいかと思います。

![2insert.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/678049/c418beb7-ba67-8ad5-30a6-1f94ec9dbb74.gif)

### テーブルをもう一つ作成 

次にbooksテーブルを追加してみます。

1. schemaシートの下の方に、テーブル定義をコピペします。
2. テーブル名を書きます。
3. 属性名も、右の方に追加します。
4. もう一度、メニューから「スキーマ反映」を実行すると、booksシートができあがります。

こんな感じで、複数テーブルを作ることができます。

![3addTable.mov.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/678049/b38f5c9d-553b-2d87-ec37-ca9f66a7d866.gif)

### カラム追加

booksテーブル定義に、カラムを追加してみます。

1. 今回は「貸出者」および貸出者の「email」を追加してみます。ここで、
    - 「貸出者」は、**外部キー** 、つまり、usersテーブルのキーを参照するものとします。
    - 「email」は、外部キーのテーブルの属性、つまり、usersテーブルの貸出者のemailアドレスとします。これは、SQLで言うところのJoinをしたものに相当します。
3. そこで、カラム「貸出者」「email」という属性を追加し、その2行下に、それぞれusers, users.emailと記入します。この働きについては次項をご覧ください。
4. メニューから「スキーマ反映」を実行すると、booksシートに反映されます。


![4addColumn.mov.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/678049/554205b8-ad9a-8b83-6808-34c16d671ed1.gif)

### 外部キーを含むテーブルにInsert

今度は、booksシートで、外部キーを含んだテーブルに追記してみます。

1. booksシートにデータを追記します。
2. 「書名」はこれまで通り、直接入力できます。
3. 外部キーである「退出者」については、外部テーブルから **ドロップダウンで選択できるようになっています！** ここは、 **Visible Keyなので、IDを数字で選んでも、だれのことだか分からない、と言うこともありません！**
4. そしてさらに、外部キーでJoinされた「email」についても、**外部キーを選択すれば、自動的に外部テーブルから対応する内容が反映**されます！

つまり、前項でschemaシートの制約の行に書いたものは、次のような意味でした。
- users: 外部キーのテーブル名。自動的に、外部テーブルのKeyを参照するようになります。
- users.email: usersテーブルのemail属性。上記の外部キーが設定されていなければ機能しませんが、設定されていればJoinされたテーブルのように機能します。


![5insertForeign.mov.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/678049/3790b9fe-c4d1-e90d-58b1-2e1a12e60fb8.gif)


### 外部テーブルを更新してみる

では、賢いあなたからの疑問である、「いやいや、Visibleキーだと、利用者の名前が変わったらどうするの。」を試してみましょう。

ここでは、外部テーブルであるusersの氏名と、メールアドレスを変更してみます。

1. usersの「名前」と、「email」を変更してみます。ここでは、 "井上" → ”今田”, "i＠example.com" → "m＠example.com" に変更してみます。
2. そうすると、usersシートのKeyの列も、自動的に "1-井上" から "1-今田" に変わりました。
3. では、それを参照するbooksテーブルはどうでしょうか？ booksシートを見てみると、ちゃんと、"今田"、"m＠example.com"に更新されています！ **気持ちいいですね！**

![6updateForeign.mov.gif](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/678049/3c7d8318-d9af-81a7-3f6e-010f7a8f94d0.gif)

## まとめ

GASベースでRDBの一部の機能を実現しているので、性能は良くないと思いますが、リレーショナルデータベースの基本的な一貫性管理はしたい、という用途には使えるのではないでしょうか。
もう少し、いじりたいところも残っていますので、それらを整備して、次回、コード等を公開しますね。