ふくだ学習録とは？
ふくだが学習したことの備忘録。
目に見える形で残すことによってやる気を出す個人的な作戦です。
他人に見せるように書いているわけではないので、すごく読みにくいです。
読了した本
データベースエンジニア養成読本 [DBを自由自在に活用するための知識とノウハウ満載!]
ゼロから作るDeepLearning
PHPフレームワーク CakePHP 3入門
今読んでいる本
SQLアンチパターンを読んで
集約クエリとは？
SQLでいうGROUP BYに相当。sumやmaxといったものを集約と呼んだりする。
ナイーブツリー（素朴な木）
ツリー階層になるような時に当たる問題。
コメントスレッドなど、親子関係が何階層にも発生するような時に考慮する。
ルート、リーフ、非葉ノードとは？
親を持たない最上層のノードをルート
子を持たない最下層のノードをリーフ
どちらでもないもの（親も持つし、子も持っている）のを非葉ノードと呼ぶ。
常に親のみに依存するのはアンチパターン
例えばparent_id列を作成する方法があるが、これはあまり望ましくない。
なぜなら、複数階層を持つツリー階層の場合、parent_id=children_id（同テーブル内の別列）といった関係性が何回続くかわからないので、クエリを投げれない。（もしくは投げても扱いづらい形式でデータが返ってくる。）
また一般的な隣接リストで対策した場合、葉ノードの追加や、ノードやサブツリーの移動は簡単だが、ノードの削除はかなり大変になってします。（全ての子孫を特定した上で、外部キー制約を満たすように最下層から順番に子孫を削除する必要があるため。）
どういった時にナイーブツリーになりやすい？
以下のような発言が出た時に、ナイーブツリーアンチパターンが使用されている可能性がある。
「このツリーでは、深さは何階層までサポートすればいい？」
「ツリーの中で孤児になった行を綺麗にするために、定期的にスクリプト実行しなければ」など
アンチパターンを使ってもいいとき
隣接リストに格納された階層構造をサポートするSQL拡張機能をデータベースが備えている場合。
具体的には再帰クエリ構文をサポートしている場合。(現在は大体のRDBがサポートしてるっぽい)
再帰クエリ構文の説明は下記。この記事のおかげでギリギリ理解できた。
まだスラスラは扱えなさそうやけど。。
再帰SQL -図解-
解決策：代替ツリーモデルを使用する
隣接モデルの代替えとなるのは、
経路列挙型モデル、入れ子集合モデル、閉包テーブルモデルなどがある。
経路列挙型モデル
先祖の系譜を表す文字列を、各ノードの属性として収納することで解決する。
例えばファイルのディレクトリ構造なども、経路列挙型モデルの一つと見ることができる。
データベース上では、大きめのVARCHARを用意し、そこに先祖からそのノードまでのパスを記載して格納しておく。
入れ子集合
直近の親ではなく、子孫の集合に関する情報を各ノードに格納する。
その情報はnsleftおよびnsrightと呼ばれる数値で表される。
nsleftには、そのノードより下の階層にある全てのノードが持つ値より小さな値が、nsrightにはそのノードより下の階層にある全てのノードが持つ値より大きな値が与えられる。
特徴としては、非葉ノードを削除した場合、その直下のノードが削除されたノードの親の子だと自動的に見なされること。
ただし欠点として、直近の親の取得などの、隣接リストでは簡単に行えていたクエリが一部複雑になってしまったり、ノード挿入や移動によって新しく大量にnsleftの値を再計算しなければいけないなどのデメリットがある。
ノードの挿入が頻繁に行われるモデルでは使用すべきではない。
閉包テープル
直接の親子関係だけではなく、ツリー全体のパスを格納する。
元々のデーブルとは別で、新たなテーブルを作成する（ここではTreePathsテーブルとする）
特徴として、ノード情報と階層構造を切り分ける（別テーブルで管理する）ので、階層構造だけを無くしてノードはそのまま残しておくことなどが柔軟にできるようになる。またノードが複数ツリーに所属することができるようになる。
入れ子構造に比べてはるかにシンプルになるが、やはり直近の親ノードの取得といった簡単な操作（隣接リストでは簡単に行えていた処理）に対して、複雑なクエリを投げなければならなくなる。
どの設計を使用するべきか
| 設計 | テーブル数 | 子へのクエリ実行 | ツリーへのクエリ実行 | 挿入 | 削除 | 参照整合性の維持 |
|---|---|---|---|---|---|---|
| 隣接リスト | 1 | 簡単 | 難しい | 簡単 | 簡単 | 簡単 |
| 再帰クエリ | 1 | 簡単 | 簡単 | 簡単 | 簡単 | 簡単 |
| 経路列挙 | 1 | 簡単 | 簡単 | 簡単 | 簡単 | 簡単 |
| 入れ子集合 | 1 | 難しい | 難しい | 難しい | 難しい | 難しい |
| 閉包テーブル | 2 | 簡単 | 簡単 | 簡単 | 簡単 | 簡単 |
RESTRICT,CASCADE,SET NULL,NO ACTIONとは？
外部キー制約の種類。
RESTRICT
UPDATE：エラーになる。
DELETE：エラーになる。
CASCADE
UPDATE：参照先の変更に追従する。
DELETE：参照先が無くなると同時に削除される。
SET NULL
UPDATE：NULLに置き換わる
DELETE：NULLに置き換わる
NO ACTION
UPDATE：エラーになる。
DELETE：エラーになる。
MySQLの外部キー制約RESTRICT,CASCADE,SET NULL,NO ACTIONの違いは？
CROSS JOINとは？
CROSS JOIN（別名：直積）をすると、JOINする2つのテーブルの全ての組み合わせが返ってくる。
cross join を知ると join が書きやすくなるよ、という話
IDリクワイアド（とりあえずID）
正しく主キーを扱えていない状態のことを示している。
主キーが重要になってくる場面
- 行の重複を避けたい
- クエリで個別の行を参照したい
- 外部キー参照をサポートしたい
上記に当てはまる場合は、主キーを作成した方がいい。（というか基本的には全てのテーブルで作成しておいた方がいい）
アンチパターン：全てのテーブルに「id」列を用いる
わざわざ別に「id」列を作成しなくても、同じテーブル内に自然に主キーの特性を持っている列がある場合があるため、「id」列を作成することによって、冗長な構成や逆に一意に管理できなくなってしまう可能性がある。
また、主キーの列名で「id」をつけるようにした場合、例えばbugテーブルとuserテーブルを用いたクエリ結果を連想配列として受け取る操作をした場合、「id」が重複したキーになってしまうため、意図せぬバグを引き起こしてしまう。
そのためbug_iduser_idといった主キーに設定しておく必要がある。
どういった時にIDリクワイアドになりやすい？
下記のような発言が出てきたら危険。
「このテーブルでは主キーは不要だと思うな」
→主キーと擬似キーを混同している可能性大。
主キーには擬似キーだけではく、自然キーや複合キーも使用できることを認識すべし。
「どうして多対多の関連が重複してしまったんだろう？」
→外部キー列の組み合わせに対して、複合キーで主キー宣言をする。もしくはUNIQUEキー制約を宣言する必要がある。
アンチパターンを用いても良い時。
ORMを用いる時。フレームワークが自動で主キー設定してくれることが多いので、そういった場合はフレームワークに従って進めた方がいいことが多い。
解決策：状況に応じて適切に調整する
①わかりやすい列名にする
bug_idやuser_idといったわかりやすく、そして被らない名前にする。
②自然キーと複合キーの活用
元々主キーとして利用できる自然キーや複合キーが存在している可能性は高いが、その列が今後の仕様変更で、NULLを許容したり、または列ごとなくなったりする可能性もあるため、擬似キーを使用するのもいい。
複合キーは、行を識別するための方法が、複数の属性列の組み合わせである場合にのみ使うようにする。（そのほうが変化に耐えうるようになる）
キーレスエントリ（外部キー嫌い）
外部キー制約を怠ったために、参照整合性が崩れてしまう状態のこと。
アンチパターン：外部キー制約を使用しない。
参照整合性を保証することが、RDBを使用するメリットなのに、それを実施できていない状態はアンチパターン。
下記のような考え方の時などに起こりやすい。
完璧なコードを前提にしている
→自分たちが参照整合性を保てるようなコードをかけると思っている。（もしくは参照整合性を保てるような手順で作業をできると思っている。）
ミスを調べなければならない
→ミスが起こっているかどうかを調べなければならない＝参照整合性が取れていない可能性が高い
どういった時にキーレスエントリ（外部キー嫌い）になりやすい？
下記のようなことが出てきたら要注意。
「あるテーブルには存在するけど、もう一つのテーブルには存在しない値を調べるクエリは、どう書けばいい？」
「あるテーブルの値が、別のテーブルへの挿入に使われていることを簡単にチェックできる方法ある？」
→元々外部キーをつけていれば、整合性が取れているはず、な状態での会話の可能性高し。
「外部キーは、データベースの実行速度を遅くするから使わない」
→RDBの特性を活かしきれていない。（というかそもそも速さを求めるならNoSQLデータベースを検討すべき）
アンチパターンを用いてもいい時
そもそも外部キーをサポートしていないデータベースを使用する時。
（実質、基本的にはアンチパターンは使用するべきではない）
解決策：外部キー制約を宣言する。
①カスケード処理を使用する
ON UPDATE句やON DELETE句を用いることで、カスケード処理の内容を定義できるので、これをも元に参照整合性を維持しながら、簡単にCRUD処理できるようにしておく。
EAT（エンティティ・アトリビュート・バリュー）
汎用的な属性テーブルを作成すること。
具体的には、属性テーブルを作成し、エンティティ、属性、値の3つの列を作成しておくこと。
なぜアンチパターンなのか？
属性列に対して、クエリを発行することが難しくなる。（データ型が決まっていないため）
必須属性を設定できなくなる。
対策
シングルテーブル継承
→サブタイプ特有の属性が少ない場合
具象テーブル継承
→全ての具象テーブルをまたぐ検索が少ない時に有効
クラステーブル継承
→全てのサブタイプに共通する列を参照するクエリが頻繁に実行される時に適用される。
半構造化データ
→属性が頻繁に変わる時に使用する。
ポリモーフィック関連
複数の親テーブルを参照したい時に起こる問題。
文字列型の列を追加し、それを２重目的の外部キーとして使用するのはアンチパターン。
アンチパターンを許容する場合
成熟したフレームワーク内のORMを使用する時。（参照整合性がしっかりとアプリケーション側で担保できるような仕様になっているかを確認するべし）
対策
参照を逆にし、交差テーブルを作成する。
また多対多の関係性を許可したくない場合は、UNIQUE制約を宣言する。
共通の親テーブルを作成してもよい（暗黙的に、共通の基底型を持っているから。）
COALESCE関数とは？
与えられた引数のうち、NULLでない最初の引数を返してくれる関数。
今日の一言
本日あんまり進まず！
シイナナルミとスタバでダベりたい！