#3 DB 設計

Open

opened 1 week ago by みてるぞ · 6 comments

要件を満足するために，どのやぅなテーブル定義が必要か，練りに練ってみる．

あくまでも “ニジカの脳内” となる DB であることは忘れないやぅにしよう．
単なる会話記録用ではない．

↑ 目的が肥大化しすぎてない?
もともとは放送局とブルスカでばらばらになったニジカの AI を一体化する目的での DB 化だったはず．
それ以上の野望はいったんさて置くことにしたい．

放送局ログの移行は冗長になるのが嫌なのでこっちに統一できればいいな程度で，それありきで設計するのはよくない気がする．

うーん，でも，次期放送局には感情パラメータ導入したいんだよなぁ……
この DB に全ニジカを収容したいところだが……

Tables

queries
answers
users
query_answer_histories

queries

物理名	論理名	型	サイズ	NULL 許容	デフォルト	備考
id	代理キー	BIGINT
user_id	クエリ主	BIGINT		○
target_character	クエリ先キャラクタ	INT				1: 伊地知ニジカ 2: ゴートうひとり
content	クエリ内容	TEXT
image_url	添附画像 URL	VARCHAR	255	○	NULL
query_type	クエリ区分	INT				1: YouTube コメント 2: Bluesky のリプ 3: 放送局システム 4: Bluesky システム
model	GPT のモデル	INT				1: GPT 3 Turbo 2: GPT 4-o
sent_at	送信日時	DATETIME	6
answered	回答済	TINYINT			0

answers

物理名	論理名	型	サイズ	デフォルト	備考
id	代理キー	BIGINT
query_id	クエリ	BIGINT
character	キャラクタ区分	INT			1: 伊地知ニジカ 2: ゴートうひとり
content	回答内容	TEXT
answer_type	回答区分	INT			1: 放送局コメへの返答 2: Bluesky リプへの返答 3: 放送局システム 4: Bluesky システム
sent_at	送信日時	DATETIME	6
answered	回答済	TINYINT		0

users

物理名	論理名	型	サイズ	NULL 許容	デフォルト	備考
id	代理キー	BIGINT
platform	プラットフォーム区分	INT				1: YouTube 2: Bluesky
code	ユーザ・コード（プラットフォーム依存）	VARCHAR	255
name	ユーザ名（プラットフォーム内）	VARCHAR	255
icon	アイコン	BLOB		○	NULL

query_answer_histories

物理名	論理名	型
id	代理キー	BIGINT
query_id	クエリ	BIGINT
answer_id	回答	BIGINT

### Tables - queries - answers - users - query_answer_histories #### queries | 物理名 | 論理名 | 型 | サイズ | NULL 許容 | デフォルト | 備考 | | :---: | :--: | :-: | :---: | :------: | :--: | :-- | | id | 代理キー | BIGINT | | | | | | user_id | クエリ主 | BIGINT | | ○ | | | | target_character | クエリ先キャラクタ | INT | | | | 1: 伊地知ニジカ 2: ゴートうひとり | | content | クエリ内容 | TEXT | | | | | | image_url | 添附画像 URL | VARCHAR | 255 | ○ | NULL | | | query_type | クエリ区分 | INT | | | | 1: YouTube コメント 2: Bluesky のリプ 3: 放送局システム 4: Bluesky システム | | model | GPT のモデル | INT | | | | 1: GPT 3 Turbo 2: GPT 4-o | sent_at | 送信日時 | DATETIME | 6 | | | | | answered | 回答済 | TINYINT | | | 0 | | #### answers | 物理名 | 論理名 | 型 | サイズ | NULL 許容 | デフォルト | 備考 | | :---: | :--: | :-: | :---: | :------: | :--: | :-- | | id | 代理キー | BIGINT | | | | | | query_id | クエリ | BIGINT | | | | | | character | キャラクタ区分 | INT | | | | 1: 伊地知ニジカ 2: ゴートうひとり | | content | 回答内容 | TEXT | | | | | | answer_type | 回答区分 | INT | | | | 1: 放送局コメへの返答 2: Bluesky リプへの返答 3: 放送局システム 4: Bluesky システム | | sent_at | 送信日時 | DATETIME | 6 | | | | | answered | 回答済 | TINYINT | | | 0 | | #### users | 物理名 | 論理名 | 型 | サイズ | NULL 許容 | デフォルト | 備考 | | :---: | :--: | :-: | :---: | :------: | :--: | :-- | | id | 代理キー | BIGINT | | | | | | platform | プラットフォーム区分 | INT | | | | 1: YouTube 2: Bluesky | | code | ユーザ・コード（プラットフォーム依存） | VARCHAR | 255 | | | | | name | ユーザ名（プラットフォーム内） | VARCHAR | 255 | | | | | icon | アイコン | BLOB | | ○ | NULL | | #### query_answer_histories | 物理名 | 論理名 | 型 | サイズ | NULL 許容 | デフォルト | 備考 | | :---: | :--: | :-: | :---: | :------: | :--: | :-- | | id | 代理キー | BIGINT | | | | | | query_id | クエリ | BIGINT | | | | | | answer_id | 回答 | BIGINT | | | | |

ツッコミ

複数キャラ宛のクエリは?
- キャラ区分を bit 列に対応させ，その bit 論理和とする．
ブルスカではスレッド形式のやり取りになると思ふが，その会話はどこで追ふ?
プラットフォームごとにテーブルを分けるのは美しくない．
- users テーブルを生やし，platform コラムによってプラットフォームを識別する．
どうやって履歴情報を積む?
- 最優先で解決策を見出さなきゃいけなぃ事案．少なくとも JSON は NG．
- histories といふ名前で自己参照型の履歴テーブル作るか．
- 実際に投げるクエリと等価のテーブル作る．若干，といふか，かなり，冗長な作りになるけど，まぁ，しかたなさす．
- 何でこんなことするかといふと，柔軟性を持たせるため．過去データ以外を参照させたいとき，それこそ，他システムからデータを引っ張ってくるとき，queries や answers に依存したままだと対応できないだろ．
- だいぶよくない設計．そもそもデータ上等価であるはずの queries と answers が別テーブルなのがよくないのだが．
- すっげぇ嫌な実装ではあるけど，queries テーブルに histories コラム追加し，q1,a1,q2,a2 のやぅな q/a の後ろに対応する Id. を置き，それをコンマ区切りで管理することにする（先の例では queries の Id. = 1 → answers の Id. = 1 → queries の Id. = 2 → answers の Id. = 2）．いや，わかる．すっげぇきもぃ．でも，ほかのケースはもっときもぃ．
  - うーん，でもやっぱ実質-tic に繰返し属性含むデザインになっちゃってるのがもやる．第 1 正規形すら担保できるか微妙．
  - てか answers テーブルに query_id が含まれてるんだから queries への参照は要らなくね? で，query_answers みたぃな対応つくればこの問題は解決されるはず（あれ，振出しに戻った……）．
    - いやだからそれだと順番を示せないんだって!!!!! ぶっ○すぬ゛～゛ん゛（怒）．
    - てわけでやっぱさっきのやつから q/a を省いたやつにするか．query_answers テーブルはややこしすぎるね……といふのも，queries と answers の関係それ自体は 1 対多なのに，履歴データとしての queries 対 answers の関係を取ると多対多になってしまふので．
    - やっぱ query_answer_histories といふ中間テーブル作ることにする．順序の問題は answers テーブルを参照した際の sent_at 順で……（きもーい）
ニジカの回答を行ふプラットフォームを明示すべき．
- それは回答区分で行ふことかな．
users，過去ログ移行の際 code 不明となるが，どうする?
- NULL とする．
  - だめな DB 設計になりつつある．users は code 不明時とさぅでないときでテーブル分けるべき．
    - 過去ログ移行は保留とする．まづは両方運用することとし，いづれ移行することとしよう．

## ツッコミ - 複数キャラ宛のクエリは? - キャラ区分を bit 列に対応させ，その bit 論理和とする． - ブルスカではスレッド形式のやり取りになると思ふが，その会話はどこで追ふ? - プラットフォームごとにテーブルを分けるのは美しくない． - users テーブルを生やし，platform コラムによってプラットフォームを識別する． - どうやって履歴情報を積む? - 最優先で解決策を見出さなきゃいけなぃ事案．少なくとも JSON は NG． - histories といふ名前で自己参照型の履歴テーブル作るか． - 実際に投げるクエリと等価のテーブル作る．若干，といふか，かなり，冗長な作りになるけど，まぁ，しかたなさす． - 何でこんなことするかといふと，柔軟性を持たせるため．過去データ以外を参照させたいとき，それこそ，他システムからデータを引っ張ってくるとき，queries や answers に依存したままだと対応できないだろ． - だいぶよくない設計．そもそもデータ上等価であるはずの queries と answers が別テーブルなのがよくないのだが． - すっげぇ嫌な実装ではあるけど，queries テーブルに histories コラム追加し，`q1,a1,q2,a2` のやぅな `q`/`a` の後ろに対応する Id. を置き，それをコンマ区切りで管理することにする（先の例では queries の Id. = 1 → answers の Id. = 1 → queries の Id. = 2 → answers の Id. = 2）．いや，わかる．すっげぇきもぃ．でも，ほかのケースはもっときもぃ． - うーん，でもやっぱ実質-tic に繰返し属性含むデザインになっちゃってるのがもやる．第 1 正規形すら担保できるか微妙． - てか answers テーブルに query_id が含まれてるんだから queries への参照は要らなくね? で，query_answers みたぃな対応つくればこの問題は解決されるはず（あれ，振出しに戻った……）． - いやだからそれだと順番を示せないんだって!!!!! ぶっ○すぬ゛～゛ん゛（怒）． - ~~てわけでやっぱさっきのやつから `q`/`a` を省いたやつにするか．query_answers テーブルはややこしすぎるね……といふのも，queries と answers の関係それ自体は 1 対多なのに，履歴データとしての queries 対 answers の関係を取ると多対多になってしまふので．~~ - やっぱ query_answer_histories といふ中間テーブル作ることにする．順序の問題は answers テーブルを参照した際の sent_at 順で……（きもーい） - ニジカの回答を行ふプラットフォームを明示すべき． - それは回答区分で行ふことかな． - users，過去ログ移行の際 code 不明となるが，どうする? - NULL とする． - だめな DB 設計になりつつある．users は code 不明時とさぅでないときでテーブル分けるべき． - 過去ログ移行は保留とする．まづは両方運用することとし，いづれ移行することとしよう．

みてるぞ added a new dependency 1 week ago

#2 放送局ニジカのログを DB に移動

user_infos を users にしたさある．
users の適切な名前を考へること．
- users（旧）要らなくね? ってなったので users（旧）廃止して user_infos を users に改名した．
- でも何らかの方法で YouTube のろたにとロロノﾄｭｰと Bluesky のろたにとロロノをニジカに同一視させる必要はあると思ってる．

一例

ろたにとロロノ “おれが時を止めた…令和6年7月14日13時17分28秒の時点でな…”
ニジカ “う゛ぅ゛ぅ゛ぅ゛ぅ゛ん゛！！止まってる？！ニジカも止まっちゃったのかなぁ……。ねえねえ、その間、何してたのかなぁ？楽しいことしてたのかなぁ？(#^.^#)”

queries

id	user_id	target_character	content	query_type	sent_at	answered
1	1	1	おれが時を止めた…令和6年7月14日13時17分28秒の時点でな…	1	2024-07-21 01:54:16.147654	1

answers

id	query_id	character	content	answer_type	sent_at	answered
1	1	1	う゛ぅ゛ぅ゛ぅ゛ぅ゛ん゛！！止まってる？！ニジカも止まっちゃったのかなぁ……。ねえねえ、その間、何してたのかなぁ？楽しいことしてたのかなぁ？(#^.^#)	1	2024-07-21 01:54:16.147654	1