共通列の設計
いつ入った行なのか分からない
運用が始まると、必ず聞かれる質問があります。
この行はいつ入ったのか。最後に更新されたのはいつか。
障害調査でも、不正の調査でも、集計の食い違いの調査でも、最初に見るのが時刻です。ところが設計時に列を用意していないと、答えようがありません。あとから列を足しても、過去の行の値は永久に分かりません。
そこで、全テーブルに標準装備する列を決めます。これを監査列と呼びます。
標準装備する2列
別題材の図書館で同じ書き方を試すと、次のようになります。
SQL クエリ
CREATE TABLE reading_sessions (
id integer PRIMARY KEY,
created_at timestamptz NOT NULL DEFAULT now(),
updated_at timestamptz NOT NULL DEFAULT now()
);created_at は行が生まれた時点、updated_at は最後に書き換えられた時点です。どちらも NOT NULL にできます。行を作る時点で必ず決まるからです。
既定値を書いておく理由
DEFAULT now() を書いておくと、INSERT のときに値を渡さなくても現在時刻が入ります。
これには2つの意味があります。1つは書く側が楽になること。もう1つが重要で、入れ忘れが起きなくなることです。アプリごとに時刻をセットする実装だと、新しく増えた書き込み経路が入れ忘れて NULL になります。既定値は、どの経路から書かれても効きます。
さらに now() はデータベースの時計を使うので、書き込むサーバーの時計がずれていても値がずれません。複数台のアプリサーバーから書き込む構成では、これが効きます。
updated_at は自動では動かない
注意が要るのは updated_at です。DEFAULT は INSERT のときにしか効かないので、UPDATE では自動で更新されません。
更新の方法は次の2つです。
| 方法 | 特徴 |
|---|---|
UPDATE 文に毎回 updated_at = now() を書く | 単純。ただし書き忘れる |
| トリガーで自動更新する | 書き忘れが起きない。仕掛けが1つ増える |
入口が複数あるならトリガー、アプリ1つに閉じているなら UPDATE 文で書く、という判断になります。理由は外部キーのときと同じで、入口の数が判断を決めます。
消した記録も残したいとき
もう1つ、deleted_at timestamptz を足して、行を物理削除せずに時刻を入れる設計があります。論理削除と呼びます。復元できる利点がある一方、すべての SELECT に WHERE deleted_at IS NULL が必要になり、書き忘れると消したはずのデータが表に出ます。全テーブルに機械的に付けるものではなく、消し戻しが業務上あり得る表だけに絞ります。
2列まとめて追加する
1つの ALTER TABLE で、同じ表への操作をカンマで並べられます。
SQL クエリ
ALTER TABLE libraries
ADD COLUMN created_at timestamptz NOT NULL DEFAULT now(),
ADD COLUMN updated_at timestamptz NOT NULL DEFAULT now();timestamptz は時刻を保持する型です。now() は現在時刻を返し、DEFAULT は値を省略したときの値を決めます。既にある行もこの既定値で埋まるので NOT NULL を付けられます。演習では1表につき1文で同じ2列を追加します。
手を動かす
4つのテーブル全部に監査列を足し、既定値まで含めて確かめます。
stores、products、customers、orders の4つすべてに監査列を足してください。
各テーブルに次の2列を、この順で足します。
created_at timestamptz NOT NULL DEFAULT now()updated_at timestamptz NOT NULL DEFAULT now()
書き方は ALTER TABLE <表> ADD COLUMN created_at timestamptz NOT NULL DEFAULT now(); です。1文に ADD COLUMN をカンマで2つ並べ、1つの表に2列まとめて足します。
仕上げに、information_schema から table_schema が 'public' で column_name が 'created_at' か 'updated_at' の行を数え、table_name と件数 audit_columns の2列を table_name の順に取り出してください。
列の確認は用意済みです。1表につき1文で監査列を追加します。
ひな形の「ここに学ぶ部分を追記する」を SQL に置き換えます。模範解答では 4 行を補います。確認用の SQL は用意してあります。
テーブル構造
CREATE TABLE stores (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE products (
id integer PRIMARY KEY,
name text NOT NULL,
price integer NOT NULL
);
CREATE TABLE customers (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders (
id integer PRIMARY KEY,
store_id integer NOT NULL REFERENCES stores (id),
customer_id integer NOT NULL REFERENCES customers (id),
ordered_on date NOT NULL
);
期待される出力
| table_name | audit_columns |
|---|---|
| customers | 2 |
| orders | 2 |
| products | 2 |
| stores | 2 |
ヒント
まだ開いていません
付箋は 2 枚。下の段ほど答えに近づきます
ヒント 2答えに近い