CREATE TABLE product_accounts (
  product_id INT,
  account_id INT,
  PRIMARY KEY (product_id, account_id),
  FOREIGN KEY (product_id) REFERENCES products(id),
  FOREIGN KEY (account_id) REFERENCES accounts(id)
);

CREATE TABLE comments (
  comment_id SERIAL PRIMARY KEY,
  parent_id BIGINT UNSIGNED,  -- 直近の親コメントID
  comment TEXT NOT NULL,
  FOREIGN KEY (parent_id) REFERENCES comments(comment_id)
);

-- 孫コメント取得（3階層必要）
SELECT c3.* FROM comments c1
JOIN comments c2 ON c1.comment_id = c2.parent_id
JOIN comments c3 ON c2.comment_id = c3.parent_id
WHERE c1.comment_id = 1;

-- コメント2を削除すると、コメント3が孤立
DELETE FROM comments WHERE comment_id = 2;
-- 外部キー制約で削除できない、または孤立ノードが発生

-- コメント3のルートコメントを取得（複数JOINが必要）
SELECT c1.* FROM comments c1
JOIN comments c2 ON c1.comment_id = c2.parent_id
JOIN comments c3 ON c2.comment_id = c3.parent_id
WHERE c3.comment_id = 3;

-- 再帰的な処理が必要（アプリケーション層で実装）
SELECT * FROM comments WHERE parent_id = 1;  -- 直接の子のみ

WITH RECURSIVE cte AS (
  SELECT * FROM comments WHERE comment_id = 1
  UNION ALL
  SELECT c.* FROM comments c
  JOIN cte ON c.parent_id = cte.comment_id
)
SELECT * FROM cte;

ALTER TABLE comments ADD path VARCHAR(1000);
-- データ例: path = '/1/2/' (コメント2の場合)

-- コメント1の全子孫取得
SELECT * FROM comments
WHERE path LIKE '/1/%';

ALTER TABLE comments ADD nsleft INT, ADD nsright INT;
-- データ例: nsleft=1, nsright=8 (コメント1の場合)

-- コメント1の全子孫取得
SELECT * FROM comments
WHERE nsleft > 1 AND nsright < 8;

CREATE TABLE comment_relations (
  ancestor_id BIGINT UNSIGNED,
  descendant_id BIGINT UNSIGNED,
  depth INT,
  PRIMARY KEY (ancestor_id, descendant_id),
  FOREIGN KEY (ancestor_id) REFERENCES comments(comment_id),
  FOREIGN KEY (descendant_id) REFERENCES comments(comment_id)
);

-- コメント1の全子孫取得
SELECT c.* FROM comments c
JOIN comment_relations r ON c.comment_id = r.descendant_id
WHERE r.ancestor_id = 1;

CREATE TABLE Bugs (
  id BIGINT NOT NULL PRIMARY KEY,        -- 不要な代理キー
  bug_id VARCHAR(10) UNIQUE NOT NULL,    -- 実際の自然キー
  description VARCHAR(1000),
  status VARCHAR(20)
);

INSERT INTO Bugs VALUES (1, 'BUG-001', 'ログイン画面でエラー', 'Open');
INSERT INTO Bugs VALUES (2, 'BUG-002', 'データ保存失敗', 'Fixed');

CREATE TABLE Bugs (
  bug_id VARCHAR(10) PRIMARY KEY,        -- 自然キーを主キーに
  description VARCHAR(1000),
  status VARCHAR(20)
);

CREATE TABLE BugProducts (
  id BIGINT NOT NULL PRIMARY KEY,        -- 代理キー
  bug_id VARCHAR(10),
  product_id BIGINT
);

-- 同じバグと製品の組み合わせが重複登録される
INSERT INTO BugProducts VALUES (1, 'BUG-001', 100);
INSERT INTO BugProducts VALUES (2, 'BUG-001', 100);  -- 重複！

CREATE TABLE BugProducts (
  bug_id VARCHAR(10),
  product_id BIGINT,
  PRIMARY KEY (bug_id, product_id)       -- 複合主キーで一意性保証
);

-- 重複挿入はエラーになる
INSERT INTO BugProducts VALUES ('BUG-001', 100);
INSERT INTO BugProducts VALUES ('BUG-001', 100);  -- エラー

CREATE TABLE Users (
  id BIGINT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE Orders (
  id BIGINT PRIMARY KEY,
  user_id BIGINT,
  total DECIMAL(10,2)
);

-- JOINクエリが分かりにくい
SELECT u.id, o.id, u.name, o.total
FROM Users u
JOIN Orders o ON u.id = o.user_id;

CREATE TABLE Users (
  user_id BIGINT PRIMARY KEY,            -- 意味のある列名
  name VARCHAR(100)
);

CREATE TABLE Orders (
  order_id BIGINT PRIMARY KEY,           -- 意味のある列名
  user_id BIGINT,
  total DECIMAL(10,2)
);

-- JOINクエリが明確
SELECT u.user_id, o.order_id, u.name, o.total
FROM Users u
JOIN Orders o ON u.user_id = o.user_id;

-- ONを使用する必要がある
SELECT * FROM Users u
JOIN Orders o ON u.id = o.user_id;

-- USINGでシンプルに記述可能
SELECT * FROM Users u
JOIN Orders o USING (user_id);

-- Railsの場合、デフォルトでidカラムが必要
CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- フレームワークが期待する形式
  email VARCHAR(255) UNIQUE,
  name VARCHAR(100)
);

-- 商品コードが変更される可能性がある
CREATE TABLE products (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- 安定した代理キー
  product_code VARCHAR(20) UNIQUE,       -- 変更される可能性がある自然キー
  name VARCHAR(200),
  price DECIMAL(10,2)
);

-- 複雑な複合キーの例
CREATE TABLE sales_details (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- シンプルな代理キー
  company_code VARCHAR(10),
  branch_code VARCHAR(10),
  department_code VARCHAR(10),
  product_code VARCHAR(20),
  sale_date DATE,
  quantity INT,
  UNIQUE KEY unique_sale (company_code, branch_code, department_code, product_code, sale_date)
);

-- 分散環境でのUUID使用例
CREATE TABLE orders (
  id CHAR(36) PRIMARY KEY,  -- UUID
  customer_id CHAR(36),
  order_date TIMESTAMP,
  total_amount DECIMAL(12,2)
);

-- データ挿入例
INSERT INTO orders VALUES (UUID(), 'customer-uuid-here', NOW(), 1500.00);

-- 履歴テーブルの例
CREATE TABLE user_history (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- 履歴レコードの一意識別
  user_id BIGINT,                        -- 元のユーザーID
  email VARCHAR(255),
  name VARCHAR(100),
  updated_at TIMESTAMP,
  operation_type ENUM('INSERT', 'UPDATE', 'DELETE')
);

-- 高パフォーマンスが要求されるログテーブル
CREATE TABLE access_logs (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,  -- 数値型で高速
  user_id BIGINT,                        -- 外部キー参照も高速
  access_time TIMESTAMP,
  page_url VARCHAR(500),
  INDEX idx_user_time (user_id, access_time)
);

-- 問題のあるケース：複数クライアントが同じ値を取得
-- クライアント1: SELECT NEXTVAL('seq1') → 100
-- クライアント2: SELECT NEXTVAL('seq1') → 100 (同じ値！)

CREATE SEQUENCE seq1;
-- 複数のバックエンドに独立したシーケンス番号が割り当てられることは保証されている
SELECT NEXTVAL('seq1');  -- 各クライアントで異なる値が返される

-- シーケンスによって生成されるすべての値は、グローバルで一意
-- 同時クエリで同じ値が観察されることはない
SELECT seq1.NEXTVAL;

-- PostgreSQLでキャッシュを使用する場合
CREATE SEQUENCE seq1 CACHE 10;

-- AUTO_INCREMENTカラムの値を取得
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

INSERT INTO users (name) VALUES ('田中');
SELECT LAST_INSERT_ID();  -- 挿入されたidを取得

-- 採番用テーブルの作成
CREATE TABLE sequence (id INT NOT NULL) ENGINE=InnoDB;
INSERT INTO sequence VALUES (0);

-- 採番処理
UPDATE sequence SET id=LAST_INSERT_ID(id+1);
SELECT LAST_INSERT_ID();  -- 新しい番号を取得

-- 複数行を一度に挿入
INSERT INTO users (name) VALUES ('佐藤'), ('鈴木'), ('高橋');

-- LAST_INSERT_ID()は最初の行のidのみ返す
SELECT LAST_INSERT_ID();  -- 最初に挿入された行のid
SELECT ROW_COUNT();       -- 挿入された行数

-- 全てのidを計算で求める
-- id範囲: LAST_INSERT_ID() ～ (LAST_INSERT_ID() + ROW_COUNT() - 1)

-- 同一セッション内でのみ有効
-- セッション1でINSERT → セッション2でLAST_INSERT_ID()を実行しても無効
INSERT INTO users (name) VALUES ('山田');
-- 別のセッションでは以下は0を返す
SELECT LAST_INSERT_ID();

-- 標準的なシーケンスを使用
CREATE SEQUENCE order_seq;
INSERT INTO orders (id, customer_name)
VALUES (NEXTVAL('order_seq'), '顧客名');

-- AUTO_INCREMENTを優先的に使用
CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  customer_name VARCHAR(100)
);

-- 特別な採番が必要な場合のみシーケンステーブルを使用

-- 外部キーなしの場合
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,  -- 外部キー制約なし
  product_id INT,   -- 外部キー制約なし
  quantity INT
);

-- 存在しない顧客IDでも挿入可能
INSERT INTO orders VALUES (1, 999, 100, 5);  -- customer_id=999が存在しなくても成功

-- 外部キーありの場合の処理フロー
INSERT INTO orders VALUES (1, 10, 100, 5);
-- 1. customer_id=10が存在するかチェック
-- 2. product_id=100が存在するかチェック
-- 3. 両方存在すれば挿入実行

-- 外部キーなしの場合
INSERT INTO orders VALUES (1, 10, 100, 5);
-- 1. 直接挿入実行（チェック処理なし）

-- 顧客テーブル
CREATE TABLE customers (
  customer_id INT PRIMARY KEY,
  name VARCHAR(100)
);

-- 注文テーブル（外部キーなし）
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,  -- 外部キー制約なし
  total_amount DECIMAL(10,2)
);

-- データ挿入
INSERT INTO customers VALUES (1, '田中太郎');
INSERT INTO orders VALUES (100, 1, 5000);

-- 顧客を削除
DELETE FROM customers WHERE customer_id = 1;

-- 孤児レコードが残る
SELECT * FROM orders WHERE customer_id = 1;  -- 存在しない顧客への参照が残る

-- 存在しない顧客IDでの注文作成
INSERT INTO orders VALUES (101, 999, 3000);  -- customer_id=999は存在しない

-- 結果確認
SELECT o.order_id, o.customer_id, c.name
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id;
-- order_id=101のnameはNULLになる（データ不整合）

-- 外部キーなしの場合、インデックスが自動作成されない
SELECT c.name, o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id;  -- フルテーブルスキャンが発生

-- EXPLAIN結果例
-- type: ALL (フルテーブルスキャン)
-- rows: 全行数

-- アプリケーション側での検証が必要
-- 1. 顧客存在チェック
SELECT COUNT(*) FROM customers WHERE customer_id = ?;

-- 2. 存在する場合のみ注文挿入
INSERT INTO orders VALUES (?, ?, ?);

-- 改善されたテーブル定義
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  product_id INT,
  quantity INT,
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

-- 親レコード削除時の動作を制御
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  total_amount DECIMAL(10,2),
  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
    ON DELETE CASCADE    -- 顧客削除時に関連注文も削除
    ON UPDATE CASCADE    -- 顧客ID更新時に注文の参照も更新
);

-- 不正データの挿入を防止
INSERT INTO orders VALUES (101, 999, 3000);
-- ERROR: Cannot add or update a child row: a foreign key constraint fails

-- 参照されているレコードの削除を防止
DELETE FROM customers WHERE customer_id = 1;
-- ERROR: Cannot delete or update a parent row: a foreign key constraint fails

-- 外部キー列にインデックスを明示的に作成
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_product_id ON orders(product_id);

-- 大量データ処理時は一時的に制約を無効化
SET FOREIGN_KEY_CHECKS = 0;
-- 大量INSERT処理
SET FOREIGN_KEY_CHECKS = 1;

-- 基本エンティティテーブル
CREATE TABLE Issue (
  issue_id BIGINT PRIMARY KEY
);

-- 汎用属性テーブル（EAV）
CREATE TABLE IssueAttribute (
  issue_id BIGINT NOT NULL,
  attr_name VARCHAR(100) NOT NULL,
  attr_value VARCHAR(100),
  PRIMARY KEY (issue_id, attr_name),
  FOREIGN KEY (issue_id) REFERENCES Issue(issue_id)
);

-- シンプルな1行のクエリ
SELECT issue_id, date_reported FROM Issue;

-- 属性を個別に取得する必要がある
SELECT issue_id, attr_value AS date_reported
FROM IssueAttribute
WHERE attr_name = 'date_reported';

-- 通常設計では NOT NULL 制約で必須項目を保証
CREATE TABLE Issue (
  issue_id BIGINT PRIMARY KEY,
  date_reported DATE NOT NULL,  -- 必須項目として保証
  status VARCHAR(20) NOT NULL
);

-- EAV設計では必須属性を保証できない
-- date_reportedが存在しないレコードも作成可能
INSERT INTO Issue VALUES (1235);
-- date_reported属性が挿入されていない = 必須項目の欠落

-- EAV設計では全て文字列型で保存
INSERT INTO IssueAttribute VALUES
  (1234, 'date_reported', '2009-06-01'),  -- 日付も文字列
  (1234, 'priority_level', '5'),          -- 数値も文字列
  (1234, 'is_critical', 'true');         -- 真偽値も文字列

-- 日付計算が困難
SELECT attr_value
FROM IssueAttribute
WHERE attr_name = 'date_reported'
  AND STR_TO_DATE(attr_value, '%Y-%m-%d') > '2009-01-01';  -- 型変換が必要

-- 通常設計では外部キー制約が可能
CREATE TABLE Issue (
  issue_id BIGINT PRIMARY KEY,
  status_id INT,
  FOREIGN KEY (status_id) REFERENCES BugStatus(status_id)
);

-- EAV設計では外部キー制約が設定できない
-- attr_valueに不正な値が混入する可能性
INSERT INTO IssueAttribute VALUES (1234, 'status', 'INVALID_STATUS');

-- 複数属性を取得するための複雑なクエリ
SELECT i.issue_id,
       i1.attr_value AS date_reported,
       i2.attr_value AS status,
       i3.attr_value AS priority,
       i4.attr_value AS description
FROM Issue AS i
LEFT OUTER JOIN IssueAttribute AS i1
  ON i.issue_id = i1.issue_id AND i1.attr_name = 'date_reported'
LEFT OUTER JOIN IssueAttribute AS i2
  ON i.issue_id = i2.issue_id AND i2.attr_name = 'status'
LEFT OUTER JOIN IssueAttribute AS i3
  ON i.issue_id = i3.issue_id AND i3.attr_name = 'priority'
LEFT OUTER JOIN IssueAttribute AS i4
  ON i.issue_id = i4.issue_id AND i4.attr_name = 'description'
WHERE i.issue_id = 1234;

-- 属性名の不整合が発生しやすい
INSERT INTO IssueAttribute VALUES
  (1234, 'date_reported', '2009-06-01'),
  (1235, 'report_date', '2009-06-02'),    -- 異なる属性名
  (1236, 'reported_on', '2009-06-03');   -- さらに異なる属性名

-- 検索時に全ての可能性を考慮する必要がある
SELECT issue_id, attr_value
FROM IssueAttribute
WHERE attr_name IN ('date_reported', 'report_date', 'reported_on');

CREATE TABLE Issue (
  issue_id BIGINT PRIMARY KEY,
  product_id INT,
  date_reported DATE NOT NULL,
  status VARCHAR(20) NOT NULL,
  description TEXT,
  reported_by VARCHAR(100),
  version_affected VARCHAR(50),
  severity VARCHAR(50),
  priority VARCHAR(20),
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

-- 基本テーブル
CREATE TABLE Issue (
  issue_id BIGINT PRIMARY KEY,
  date_reported DATE NOT NULL,
  status VARCHAR(20) NOT NULL,
  description TEXT
);

-- バグ専用テーブル
CREATE TABLE Bug (
  issue_id BIGINT PRIMARY KEY,
  severity VARCHAR(50),
  steps_to_reproduce TEXT,
  FOREIGN KEY (issue_id) REFERENCES Issue(issue_id)
);

-- 機能要望専用テーブル
CREATE TABLE FeatureRequest (
  issue_id BIGINT PRIMARY KEY,
  sponsor VARCHAR(100),
  FOREIGN KEY (issue_id) REFERENCES Issue(issue_id)
);

-- コメントテーブル（問題のある設計）
CREATE TABLE Comments (
  comment_id SERIAL PRIMARY KEY,
  issue_type VARCHAR(20),     -- 'Bug' または 'FeatureRequest'
  issue_id BIGINT,           -- 二重目的の外部キー
  comment_text TEXT,
  comment_date TIMESTAMP
);

-- 参照先テーブル1
CREATE TABLE Bugs (
  issue_id SERIAL PRIMARY KEY,
  summary VARCHAR(80),
  description VARCHAR(1000)
);

-- 参照先テーブル2
CREATE TABLE FeatureRequests (
  issue_id SERIAL PRIMARY KEY,
  summary VARCHAR(80),
  description VARCHAR(1000)
);

-- 以下の制約は設定できない（構文エラー）
ALTER TABLE Comments ADD CONSTRAINT fk_issue
FOREIGN KEY (issue_id) REFERENCES Bugs(issue_id);  -- Bugsのみ参照

-- または
ALTER TABLE Comments ADD CONSTRAINT fk_issue
FOREIGN KEY (issue_id) REFERENCES FeatureRequests(issue_id);  -- FeatureRequestsのみ参照

-- 条件付き外部キー制約は存在しない
-- "issue_type='Bug'の場合はBugsテーブル、'FeatureRequest'の場合はFeatureRequestsテーブル"
-- このような制約は設定不可能

-- 存在しないBugへのコメント挿入
INSERT INTO Comments VALUES (4, 'Bug', 9999, '存在しないバグへのコメント');
-- エラーにならず挿入される

-- 存在しないFeatureRequestへのコメント挿入
INSERT INTO Comments VALUES (5, 'FeatureRequest', 8888, '存在しない機能要望へのコメント');
-- エラーにならず挿入される

-- 不正なissue_typeの挿入
INSERT INTO Comments VALUES (6, 'InvalidType', 1234, '不正なタイプ');
-- エラーにならず挿入される

-- Bugレコードを削除
DELETE FROM Bugs WHERE issue_id = 1234;

-- 関連するコメントは残り続ける（孤児レコード）
SELECT * FROM Comments WHERE issue_type = 'Bug' AND issue_id = 1234;
-- 削除されたBugを参照するコメントが残る

-- 全コメントと関連する課題情報を取得
SELECT c.comment_id, c.comment_text,
       COALESCE(b.summary, f.summary) AS issue_summary,
       c.issue_type
FROM Comments c
LEFT OUTER JOIN Bugs b
  ON c.issue_type = 'Bug' AND c.issue_id = b.issue_id
LEFT OUTER JOIN FeatureRequests f
  ON c.issue_type = 'FeatureRequest' AND c.issue_id = f.issue_id;

-- 各課題タイプのコメント数を集計
SELECT issue_type, COUNT(*) as comment_count
FROM Comments
GROUP BY issue_type;

-- しかし、実際に存在する課題のコメント数は別途計算が必要
SELECT 'Bug' as issue_type, COUNT(*) as valid_comment_count
FROM Comments c
JOIN Bugs b ON c.issue_id = b.issue_id
WHERE c.issue_type = 'Bug'
UNION ALL
SELECT 'FeatureRequest' as issue_type, COUNT(*) as valid_comment_count
FROM Comments c
JOIN FeatureRequests f ON c.issue_id = f.issue_id
WHERE c.issue_type = 'FeatureRequest';

-- バグコメント用交差テーブル
CREATE TABLE BugComments (
  comment_id BIGINT,
  issue_id BIGINT,
  PRIMARY KEY (comment_id, issue_id),
  FOREIGN KEY (comment_id) REFERENCES Comments(comment_id),
  FOREIGN KEY (issue_id) REFERENCES Bugs(issue_id)
);

-- 機能要望コメント用交差テーブル
CREATE TABLE FeatureRequestComments (
  comment_id BIGINT,
  issue_id BIGINT,
  PRIMARY KEY (comment_id, issue_id),
  FOREIGN KEY (comment_id) REFERENCES Comments(comment_id),
  FOREIGN KEY (issue_id) REFERENCES FeatureRequests(issue_id)
);

-- コメントテーブル（シンプル化）
CREATE TABLE Comments (
  comment_id SERIAL PRIMARY KEY,
  comment_text TEXT,
  comment_date TIMESTAMP
);

-- 共通の親テーブル
CREATE TABLE Issues (
  issue_id SERIAL PRIMARY KEY,
  summary VARCHAR(80),
  description VARCHAR(1000),
  issue_type VARCHAR(20)
);

-- 子テーブル（バグ固有の属性）
CREATE TABLE Bugs (
  issue_id BIGINT PRIMARY KEY,
  severity VARCHAR(20),
  steps_to_reproduce TEXT,
  FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);

-- 子テーブル（機能要望固有の属性）
CREATE TABLE FeatureRequests (
  issue_id BIGINT PRIMARY KEY,
  sponsor VARCHAR(100),
  FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);

-- コメントテーブル（単一の外部キー）
CREATE TABLE Comments (
  comment_id SERIAL PRIMARY KEY,
  issue_id BIGINT,
  comment_text TEXT,
  comment_date TIMESTAMP,
  FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);

-- シンプルなJOINクエリ
SELECT c.comment_text, i.summary, i.issue_type
FROM Comments c
JOIN Issues i ON c.issue_id = i.issue_id;

-- 参照整合性の保証
INSERT INTO Comments VALUES (1, 9999, 'テスト');
-- ERROR: 外部キー制約違反

-- 孤児レコードの防止
DELETE FROM Issues WHERE issue_id = 1234;
-- 関連するコメントも自動的に削除される（CASCADE設定時）

CREATE TABLE Bugs (
  bug_id SERIAL PRIMARY KEY,
  description VARCHAR(1000),
  tag1 VARCHAR(20),
  tag2 VARCHAR(20),
  tag3 VARCHAR(20)
);

-- 'performance'タグを持つバグを検索
SELECT * FROM Bugs
WHERE tag1 = 'performance'
   OR tag2 = 'performance'
   OR tag3 = 'performance';

-- 'performance'と'printing'両方のタグを持つバグを検索
SELECT * FROM Bugs
WHERE (tag1 = 'performance' OR tag2 = 'performance' OR tag3 = 'performance')
  AND (tag1 = 'printing' OR tag2 = 'printing' OR tag3 = 'printing');

SELECT * FROM Bugs
WHERE 'performance' IN (tag1, tag2, tag3)
  AND 'printing' IN (tag1, tag2, tag3);

-- タグを追加する前に空きスロットを確認
SELECT bug_id, tag1, tag2, tag3
FROM Bugs
WHERE bug_id = 3456;

-- 空いている列にタグを追加
UPDATE Bugs SET tag2 = 'performance' WHERE bug_id = 3456;

-- 空きスロットを見つけるロジックが必要
UPDATE Bugs
SET tag1 = CASE WHEN tag1 IS NULL THEN 'newTag' ELSE tag1 END,
    tag2 = CASE WHEN tag1 IS NOT NULL AND tag2 IS NULL THEN 'newTag' ELSE tag2 END,
    tag3 = CASE WHEN tag1 IS NOT NULL AND tag2 IS NOT NULL AND tag3 IS NULL THEN 'newTag' ELSE tag3 END
WHERE bug_id = 1234;

-- 'crash'タグを削除
UPDATE Bugs
SET tag1 = CASE WHEN tag1 = 'crash' THEN NULL ELSE tag1 END,
    tag2 = CASE WHEN tag2 = 'crash' THEN NULL ELSE tag2 END,
    tag3 = CASE WHEN tag3 = 'crash' THEN NULL ELSE tag3 END
WHERE bug_id = 1234;

-- 重複タグの挿入が可能
INSERT INTO Bugs VALUES (9999, 'テストバグ', 'crash', 'crash', 'performance');
-- tag1とtag2に同じ'crash'が格納される

-- 各タグの使用回数を集計
SELECT tag, COUNT(*) as tag_count
FROM (
  SELECT tag1 as tag FROM Bugs WHERE tag1 IS NOT NULL
  UNION ALL
  SELECT tag2 as tag FROM Bugs WHERE tag2 IS NOT NULL
  UNION ALL
  SELECT tag3 as tag FROM Bugs WHERE tag3 IS NOT NULL
) AS all_tags
GROUP BY tag
ORDER BY tag_count DESC;

-- 新しいタグ列を追加（スキーマ変更が必要）
ALTER TABLE Bugs ADD COLUMN tag4 VARCHAR(20);
ALTER TABLE Bugs ADD COLUMN tag5 VARCHAR(20);

-- 既存のクエリもすべて修正が必要
SELECT * FROM Bugs
WHERE 'performance' IN (tag1, tag2, tag3, tag4, tag5);  -- 修正必要

-- バグテーブル（シンプル化）
CREATE TABLE Bugs (
  bug_id SERIAL PRIMARY KEY,
  description VARCHAR(1000)
);

-- タグテーブル
CREATE TABLE Tags (
  tag_id SERIAL PRIMARY KEY,
  tag_name VARCHAR(20) UNIQUE
);

-- バグ-タグ関連テーブル（交差テーブル）
CREATE TABLE BugTags (
  bug_id BIGINT,
  tag_id BIGINT,
  PRIMARY KEY (bug_id, tag_id),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (tag_id) REFERENCES Tags(tag_id)
);

-- 'performance'タグを持つバグを検索
SELECT DISTINCT b.*
FROM Bugs b
JOIN BugTags bt ON b.bug_id = bt.bug_id
JOIN Tags t ON bt.tag_id = t.tag_id
WHERE t.tag_name = 'performance';

-- 'performance'と'printing'両方のタグを持つバグを検索
SELECT b.*
FROM Bugs b
JOIN BugTags bt ON b.bug_id = bt.bug_id
JOIN Tags t ON bt.tag_id = t.tag_id
WHERE t.tag_name IN ('performance', 'printing')
GROUP BY b.bug_id, b.description
HAVING COUNT(DISTINCT t.tag_name) = 2;

-- 新しいタグを追加
INSERT INTO BugTags (bug_id, tag_id)
SELECT 1234, tag_id FROM Tags WHERE tag_name = 'newTag';

-- 各タグの使用回数を集計
SELECT t.tag_name, COUNT(*) as usage_count
FROM Tags t
JOIN BugTags bt ON t.tag_id = bt.tag_id
GROUP BY t.tag_id, t.tag_name
ORDER BY usage_count DESC;

-- 年度別にテーブルを分割
CREATE TABLE Bugs_2020 (
  bug_id SERIAL PRIMARY KEY,
  summary VARCHAR(80),
  description VARCHAR(1000),
  date_reported DATE,
  status VARCHAR(20)
);

CREATE TABLE Bugs_2021 (
  bug_id SERIAL PRIMARY KEY,
  summary VARCHAR(80),
  description VARCHAR(1000),
  date_reported DATE,
  status VARCHAR(20)
);

CREATE TABLE Bugs_2022 (
  bug_id SERIAL PRIMARY KEY,
  summary VARCHAR(80),
  description VARCHAR(1000),
  date_reported DATE,
  status VARCHAR(20)
);

-- 新しい列を追加する場合、全テーブルに適用が必要
ALTER TABLE Bugs_2020 ADD COLUMN priority VARCHAR(20);
ALTER TABLE Bugs_2021 ADD COLUMN priority VARCHAR(20);
ALTER TABLE Bugs_2022 ADD COLUMN priority VARCHAR(20);
-- 年度が増えるたびに同じ作業が必要

-- 各テーブルに同じ制約を設定
ALTER TABLE Bugs_2020 ADD CONSTRAINT chk_status_2020
  CHECK (status IN ('NEW', 'ASSIGNED', 'FIXED', 'VERIFIED'));
ALTER TABLE Bugs_2021 ADD CONSTRAINT chk_status_2021
  CHECK (status IN ('NEW', 'ASSIGNED', 'FIXED', 'VERIFIED'));
ALTER TABLE Bugs_2022 ADD CONSTRAINT chk_status_2022
  CHECK (status IN ('NEW', 'ASSIGNED', 'FIXED', 'VERIFIED'));

-- 2020年から2022年の全バグを取得
SELECT bug_id, summary, date_reported, status FROM Bugs_2020
UNION ALL
SELECT bug_id, summary, date_reported, status FROM Bugs_2021
UNION ALL
SELECT bug_id, summary, date_reported, status FROM Bugs_2022
ORDER BY date_reported;

-- 年度別のバグ件数を集計
SELECT '2020' as year, COUNT(*) as bug_count FROM Bugs_2020
UNION ALL
SELECT '2021' as year, COUNT(*) as bug_count FROM Bugs_2021
UNION ALL
SELECT '2022' as year, COUNT(*) as bug_count FROM Bugs_2022;

-- 同じbug_idが複数のテーブルに存在する可能性
INSERT INTO Bugs_2020 VALUES (1, 'バグ1', '説明1', '2020-01-01', 'NEW');
INSERT INTO Bugs_2021 VALUES (1, 'バグ2', '説明2', '2021-01-01', 'NEW');
-- bug_id=1が重複している

-- 毎年新しいテーブルを作成
CREATE TABLE Bugs_2023 (
  bug_id SERIAL PRIMARY KEY,
  summary VARCHAR(80),
  description VARCHAR(1000),
  date_reported DATE,
  status VARCHAR(20),
  priority VARCHAR(20)  -- 新しい列も忘れずに追加
);

-- 動的にテーブル名を生成する必要がある
SET @table_name = CONCAT('Bugs_', YEAR(CURDATE()));
SET @sql = CONCAT('SELECT * FROM ', @table_name, ' WHERE status = "NEW"');
PREPARE stmt FROM @sql;
EXECUTE stmt;

-- MySQL 8.0のパーティション機能を使用
CREATE TABLE Bugs (
  bug_id BIGINT AUTO_INCREMENT,
  summary VARCHAR(80),
  description VARCHAR(1000),
  date_reported DATE,
  status VARCHAR(20),
  PRIMARY KEY (bug_id, date_reported)
) PARTITION BY RANGE (YEAR(date_reported)) (
  PARTITION p2020 VALUES LESS THAN (2021),
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024)
);

-- 通常のクエリとして実行可能
SELECT * FROM Bugs WHERE date_reported BETWEEN '2020-01-01' AND '2022-12-31';
-- パーティション機能により、関連するパーティションのみアクセス

-- 基本情報テーブル
CREATE TABLE Bugs (
  bug_id BIGINT PRIMARY KEY,
  summary VARCHAR(80),
  date_reported DATE,
  status VARCHAR(20)
);

-- 詳細情報テーブル（大きなデータ）
CREATE TABLE BugDetails (
  bug_id BIGINT PRIMARY KEY,
  description TEXT,
  reproduction_steps TEXT,
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);

-- 統一されたバグテーブル
CREATE TABLE Bugs (
  bug_id BIGINT AUTO_INCREMENT PRIMARY KEY,
  summary VARCHAR(80),
  description VARCHAR(1000),
  date_reported DATE,
  status VARCHAR(20)
);

-- 年度別の統計情報が必要な場合は、ビューを作成
CREATE VIEW BugsByYear AS
SELECT
  YEAR(date_reported) as report_year,
  COUNT(*) as bug_count,
  COUNT(CASE WHEN status = 'FIXED' THEN 1 END) as fixed_count
FROM Bugs
GROUP BY YEAR(date_reported);

-- 商品テーブル（問題のある設計）
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(100),
  price FLOAT  -- 浮動小数点型を使用
);

-- データ挿入
INSERT INTO products VALUES (1, '商品A', 59.95);

-- 実際に格納される値を確認
SELECT product_id, name, price FROM products WHERE product_id = 1;
-- 結果: price = 59.950000762939 (期待値: 59.95)

-- 税込価格の計算例
CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  unit_price FLOAT,
  quantity INT,
  tax_rate FLOAT
);

INSERT INTO orders VALUES (1, 59.95, 1000, 0.10);

-- 税込総額の計算
SELECT
  order_id,
  unit_price * quantity * (1 + tax_rate) AS total_with_tax
FROM orders WHERE order_id = 1;
-- 結果: 65945.0083923 (期待値: 65945.00)

-- 投資テーブル
CREATE TABLE investments (
  investment_id INT PRIMARY KEY,
  principal FLOAT,      -- 元本
  interest_rate FLOAT,  -- 年利
  years INT            -- 運用年数
);

INSERT INTO investments VALUES (1, 1000000.0, 0.05, 10);

-- 複利計算（10年間）
-- 正確な計算: 1,000,000 × (1.05)^10 = 1,628,894.63
SELECT
  investment_id,
  principal * POWER(1 + interest_rate, years) AS final_amount
FROM investments WHERE investment_id = 1;
-- 結果: 1628894.6875 (誤差: 約0.05円)

-- 価格比較テーブル
CREATE TABLE price_comparisons (
  id INT PRIMARY KEY,
  calculated_price FLOAT,
  expected_price FLOAT
);

-- 計算結果を挿入
INSERT INTO price_comparisons VALUES
  (1, 0.1 + 0.2, 0.3);

-- 等価比較
SELECT * FROM price_comparisons
WHERE calculated_price = expected_price;
-- 結果: 0行 (0.1 + 0.2 ≠ 0.3 in binary)

-- 実際の値を確認
SELECT
  calculated_price,
  expected_price,
  calculated_price - expected_price AS difference
FROM price_comparisons WHERE id = 1;
-- difference: 5.551115123125783e-17 (非常に小さいが0ではない)

-- 売上データ
CREATE TABLE sales (
  sale_id INT PRIMARY KEY,
  amount FLOAT
);

-- 小数値を大量挿入
INSERT INTO sales VALUES
  (1, 0.10), (2, 0.20), (3, 0.30), (4, 0.40), (5, 0.50);

-- 合計計算
SELECT SUM(amount) AS total FROM sales;
-- 結果: 1.5000000596046448 (期待値: 1.50)

-- 改善されたテーブル設計
CREATE TABLE products (
  product_id INT PRIMARY KEY,
  name VARCHAR(100),
  price DECIMAL(10,2)  -- 固定小数点型を使用
);

-- 正確な値が格納される
INSERT INTO products VALUES (1, '商品A', 59.95);

SELECT price FROM products WHERE product_id = 1;
-- 結果: 59.95 (正確な値)

CREATE TABLE investments (
  investment_id INT PRIMARY KEY,
  principal DECIMAL(15,2),
  interest_rate DECIMAL(5,4),  -- 0.0500 (5%)
  years INT
);

INSERT INTO investments VALUES (1, 1000000.00, 0.0500, 10);

-- 正確な複利計算
SELECT
  investment_id,
  principal * POWER(1 + interest_rate, years) AS final_amount
FROM investments WHERE investment_id = 1;
-- 結果: 1628894.63 (正確な値)

CREATE TABLE sales (
  sale_id INT PRIMARY KEY,
  amount DECIMAL(10,2)
);

INSERT INTO sales VALUES
  (1, 0.10), (2, 0.20), (3, 0.30), (4, 0.40), (5, 0.50);

SELECT SUM(amount) AS total FROM sales;
-- 結果: 1.50 (正確な値)

-- DECIMAL(precision, scale)の指定方法
CREATE TABLE financial_data (
  amount DECIMAL(10,2),    -- 全体10桁、小数点以下2桁
  rate DECIMAL(5,4),       -- 全体5桁、小数点以下4桁 (例: 0.0525)
  percentage DECIMAL(6,3)  -- 全体6桁、小数点以下3桁 (例: 100.125)
);

-- 天体観測データ
CREATE TABLE astronomical_data (
  observation_id INT PRIMARY KEY,
  star_distance FLOAT,      -- 光年単位（非常に大きな値）
  luminosity DOUBLE,        -- 太陽光度単位
  temperature FLOAT         -- ケルビン温度
);

-- 科学計算での使用例
INSERT INTO astronomical_data VALUES
  (1, 4.37e12, 1.5e-4, 2800.0),  -- 近傍恒星のデータ
  (2, 2.5e15, 3.2e6, 50000.0);   -- 遠方恒星のデータ

-- 環境センサーデータ
CREATE TABLE sensor_readings (
  reading_id BIGINT PRIMARY KEY,
  sensor_id INT,
  temperature FLOAT,        -- 温度センサー値
  humidity FLOAT,           -- 湿度センサー値
  pressure FLOAT,           -- 気圧センサー値
  timestamp TIMESTAMP
);

-- センサーデータは元々概数
INSERT INTO sensor_readings VALUES
  (1, 101, 23.7, 65.2, 1013.25, NOW()),
  (2, 101, 23.8, 65.1, 1013.30, NOW());

-- ゲームスコアシステム
CREATE TABLE game_scores (
  player_id BIGINT,
  game_session_id BIGINT,
  score FLOAT,              -- 高速計算が優先
  play_time FLOAT,          -- 秒単位の概数
  INDEX idx_player_score (player_id, score)
);

-- 大量データでの集計処理が高速
SELECT player_id, AVG(score) as avg_score
FROM game_scores
GROUP BY player_id;

-- 外部システム連携テーブル
CREATE TABLE legacy_import (
  record_id INT PRIMARY KEY,
  external_value FLOAT,     -- 外部システムがFLOAT形式で送信
  import_timestamp TIMESTAMP
);

-- 外部システムからのデータ受信
INSERT INTO legacy_import VALUES
  (1, 123.456789, NOW());   -- 外部システムの精度に依存

-- 在庫予測システム
CREATE TABLE inventory_forecast (
  product_id INT,
  forecast_date DATE,
  predicted_demand FLOAT,   -- 予測値（概数で十分）
  confidence_level FLOAT,   -- 信頼度（0.0-1.0）
  PRIMARY KEY (product_id, forecast_date)
);

-- 予測計算での使用
SELECT
  product_id,
  AVG(predicted_demand) as avg_demand,
  STDDEV(predicted_demand) as demand_variance
FROM inventory_forecast
GROUP BY product_id;

-- GPS位置データ
CREATE TABLE gps_locations (
  location_id BIGINT PRIMARY KEY,
  latitude DOUBLE,          -- 緯度（GPS精度の範囲内）
  longitude DOUBLE,         -- 経度（GPS精度の範囲内）
  altitude FLOAT,           -- 高度（概数）
  accuracy_meters FLOAT     -- GPS精度（メートル）
);

-- 等価比較を避ける
-- ❌ 避けるべき比較
SELECT * FROM sensor_readings WHERE temperature = 23.5;

-- ✅ 範囲比較を使用
SELECT * FROM sensor_readings
WHERE temperature BETWEEN 23.4 AND 23.6;

-- ✅ 許容誤差を考慮した比較
SELECT * FROM sensor_readings
WHERE ABS(temperature - 23.5) < 0.1;

-- CHECK制約を使用した場合
CREATE TABLE Bugs (
  bug_id SERIAL PRIMARY KEY,
  summary VARCHAR(80),
  status VARCHAR(20) CHECK (status IN ('NEW', 'IN PROGRESS', 'FIXED')),
  description VARCHAR(1000)
);

-- MySQL のENUM型を使用した場合
CREATE TABLE Bugs (
  bug_id SERIAL PRIMARY KEY,
  summary VARCHAR(80),
  status ENUM('NEW', 'IN PROGRESS', 'FIXED'),
  description VARCHAR(1000)
);

-- CHECK制約の場合、制約定義を解析する必要がある
SELECT column_name, check_clause
FROM information_schema.check_constraints
WHERE table_name = 'Bugs' AND column_name = 'status';

-- ENUM型の場合
SELECT column_type
FROM information_schema.columns
WHERE table_name = 'Bugs' AND column_name = 'status';
-- 結果: enum('NEW','IN PROGRESS','FIXED')

-- 新しいステータス 'CODE COMPLETE' を追加する場合
-- CHECK制約の場合：制約を削除して再作成
ALTER TABLE Bugs DROP CONSTRAINT bugs_status_check;
ALTER TABLE Bugs ADD CONSTRAINT bugs_status_check
  CHECK (status IN ('NEW', 'IN PROGRESS', 'FIXED', 'CODE COMPLETE'));

-- ENUM型の場合：列を再定義
ALTER TABLE Bugs MODIFY COLUMN status
  ENUM('NEW', 'IN PROGRESS', 'FIXED', 'CODE COMPLETE');

-- 1. データをバックアップ
CREATE TABLE Bugs_backup AS SELECT * FROM Bugs;

-- 2. テーブルを削除
DROP TABLE Bugs;

-- 3. 新しい定義でテーブルを再作成
CREATE TABLE Bugs (
  bug_id SERIAL PRIMARY KEY,
  summary VARCHAR(80),
  status ENUM('NEW', 'IN PROGRESS', 'FIXED', 'CODE COMPLETE'),
  description VARCHAR(1000)
);

-- 4. データを復元
INSERT INTO Bugs SELECT * FROM Bugs_backup;

-- 'FIXED' を廃止して 'CODE COMPLETE' と 'VERIFIED' に分ける場合
-- 1. まず新しい値を追加
ALTER TABLE Bugs MODIFY COLUMN status
  ENUM('NEW', 'IN PROGRESS', 'FIXED', 'CODE COMPLETE', 'VERIFIED');

-- 2. 既存の 'FIXED' データをどう扱うか？
UPDATE Bugs SET status = 'CODE COMPLETE' WHERE status = 'FIXED';
-- または
UPDATE Bugs SET status = 'VERIFIED' WHERE status = 'FIXED';

-- 3. 'FIXED' を削除（既存データがあると削除できない）
-- ALTER TABLE Bugs MODIFY COLUMN status
--   ENUM('NEW', 'IN PROGRESS', 'CODE COMPLETE', 'VERIFIED');

-- MySQL
CREATE TABLE Bugs (
  status ENUM('NEW', 'IN PROGRESS', 'FIXED')
);

-- PostgreSQL（ENUM型の構文が異なる）
CREATE TYPE bug_status AS ENUM ('NEW', 'IN PROGRESS', 'FIXED');
CREATE TABLE Bugs (
  status bug_status
);

-- SQL Server（ENUM型が存在しない）
CREATE TABLE Bugs (
  status VARCHAR(20) CHECK (status IN ('NEW', 'IN PROGRESS', 'FIXED'))
);

-- ステータス参照テーブル
CREATE TABLE BugStatus (
  status_id SERIAL PRIMARY KEY,
  status_name VARCHAR(20) UNIQUE NOT NULL,
  active BOOLEAN DEFAULT TRUE  -- 廃止フラグ
);

-- バグテーブル
CREATE TABLE Bugs (
  bug_id SERIAL PRIMARY KEY,
  summary VARCHAR(80),
  status_id INT,
  description VARCHAR(1000),
  FOREIGN KEY (status_id) REFERENCES BugStatus(status_id)
);

-- 初期データ挿入
INSERT INTO BugStatus (status_name) VALUES
  ('NEW'),
  ('IN PROGRESS'),
  ('FIXED');

-- シンプルなSELECT文で取得可能
SELECT status_id, status_name
FROM BugStatus
WHERE active = TRUE;

-- 単純なINSERT文で追加
INSERT INTO BugStatus (status_name) VALUES ('CODE COMPLETE');

-- UPDATE文で名称変更
UPDATE BugStatus
SET status_name = 'COMPLETED'
WHERE status_name = 'FIXED';

-- 物理削除せずに論理削除
UPDATE BugStatus
SET active = FALSE
WHERE status_name = 'FIXED';

-- 有効なステータスのみ取得
SELECT bs.status_name, COUNT(*) as bug_count
FROM Bugs b
JOIN BugStatus bs ON b.status_id = bs.status_id
WHERE bs.active = TRUE
GROUP BY bs.status_id, bs.status_name;

-- 相互排他的で変更されない値
CREATE TABLE Users (
  user_id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  gender ENUM('MALE', 'FEMALE'),  -- 変更される可能性が低い
  is_active BOOLEAN               -- ON/OFF の二択
);

CREATE TABLE Users (
  user_id SERIAL PRIMARY KEY,
  username VARCHAR(50),
  profile_image_path VARCHAR(255)  -- ファイルパスを格納
);

-- データ例
INSERT INTO Users VALUES
  (1, 'taro', '/uploads/images/user1_profile.jpg'),
  (2, 'hanako', '/uploads/images/user2_profile.jpg');

-- ユーザーを削除
DELETE FROM Users WHERE user_id = 1;
-- データベースの行は削除されるが、'/uploads/images/user1_profile.jpg' は残る

-- 孤児ファイルの確認
SELECT profile_image_path FROM Users WHERE user_id = 1;
-- 結果: 0行（データベースには存在しない）
-- しかし、ファイルシステムには '/uploads/images/user1_profile.jpg' が残存

-- ファイルシステムでファイルを削除後
-- rm /uploads/images/user2_profile.jpg

-- データベースには参照が残る
SELECT username, profile_image_path FROM Users WHERE user_id = 2;
-- 結果: hanako, /uploads/images/user2_profile.jpg
-- しかし、実際のファイルは存在しない（リンク切れ）

-- トランザクション開始
BEGIN;

-- 新しいユーザーを追加
INSERT INTO Users VALUES (3, 'jiro', '/uploads/images/user3_profile.jpg');

-- この時点で他のクライアントからファイルにアクセス可能
-- （COMMITしていないのに）

-- 何らかの理由でロールバック
ROLLBACK;

-- データベースの行は元に戻るが、ファイルは残る
-- '/uploads/images/user3_profile.jpg' が孤児ファイルとして残存

BEGIN;

-- 既存ユーザーの画像を更新
UPDATE Users
SET profile_image_path = '/uploads/images/user1_new_profile.jpg'
WHERE user_id = 1;

-- 古いファイルを削除（アプリケーション側で実行）
-- rm /uploads/images/user1_profile.jpg

-- エラーが発生してロールバック
ROLLBACK;

-- データベースは元の状態に戻る
SELECT profile_image_path FROM Users WHERE user_id = 1;
-- 結果: /uploads/images/user1_profile.jpg

-- しかし、実際のファイルは削除済みで復元されない

-- データベースバックアップ（例：MySQL）
mysqldump --single-transaction mydb > backup.sql

-- ファイルシステムバックアップ（別途必要）
tar -czf files_backup.tar.gz /uploads/images/

-- 復旧時の問題
-- 1. データベースを復旧
mysql mydb < backup.sql

-- 2. ファイルを復旧
tar -xzf files_backup.tar.gz

-- 3. 同期の確認が必要
SELECT COUNT(*) as db_count FROM Users WHERE profile_image_path IS NOT NULL;
-- ファイルシステムのファイル数と一致するか手動確認が必要

-- データベースレベルでのアクセス制御
GRANT SELECT ON Users TO readonly_user;
REVOKE DELETE ON Users FROM readonly_user;

-- しかし、ファイルシステムレベルでは別途権限設定が必要
-- chmod 644 /uploads/images/*.jpg
-- chown webapp:webapp /uploads/images/

-- readonly_userでもファイルシステム権限があれば直接ファイル削除可能
-- rm /uploads/images/user1_profile.jpg （データベース権限とは無関係）

-- 不正なパスの挿入が可能
INSERT INTO Users VALUES
  (4, 'saburo', 'invalid/path/nonexistent.jpg'),
  (5, 'shiro', NULL),  -- パスが空
  (6, 'goro', '../../../etc/passwd');  -- セキュリティリスクのあるパス

-- ファイル存在確認はアプリケーション側で実装が必要
-- SQLでは以下のような制約を設定できない
-- CHECK (file_exists(profile_image_path))  -- 存在しない関数

CREATE TABLE Users (
  user_id SERIAL PRIMARY KEY,
  username VARCHAR(50),
  profile_image LONGBLOB,           -- 画像データを直接格納
  image_mime_type VARCHAR(50),      -- MIMEタイプ
  image_size INT,                   -- ファイルサイズ
  uploaded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- バイナリデータとして挿入
INSERT INTO Users (username, profile_image, image_mime_type, image_size)
VALUES ('taro', LOAD_FILE('/tmp/upload/user_image.jpg'), 'image/jpeg', 45678);

BEGIN;

-- 画像データを更新
UPDATE Users
SET profile_image = LOAD_FILE('/tmp/new_image.jpg'),
    image_mime_type = 'image/jpeg'
WHERE user_id = 1;

-- エラーが発生した場合
ROLLBACK;
-- 画像データも含めて完全に元の状態に復元される

-- ユーザー削除時に画像も自動的に削除される
DELETE FROM Users WHERE user_id = 1;
-- profile_imageも同時に削除され、孤児ファイルは発生しない

-- 読み取り専用のアーカイブシステム
CREATE TABLE DocumentArchive (
  doc_id SERIAL PRIMARY KEY,
  title VARCHAR(200),
  file_path VARCHAR(500),  -- 変更されないアーカイブファイル
  archived_date DATE,
  readonly_flag BOOLEAN DEFAULT TRUE
);

CREATE TABLE Users (
  user_id SERIAL PRIMARY KEY,
  username VARCHAR(50),
  email VARCHAR(100),
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  phone VARCHAR(20),
  address TEXT,
  created_at TIMESTAMP,
  updated_at TIMESTAMP
);

-- 全ての列にインデックスを作成（アンチパターン）
CREATE INDEX idx_username ON Users(username);
CREATE INDEX idx_email ON Users(email);
CREATE INDEX idx_first_name ON Users(first_name);
CREATE INDEX idx_last_name ON Users(last_name);
CREATE INDEX idx_phone ON Users(phone);
CREATE INDEX idx_address ON Users(address);
CREATE INDEX idx_created_at ON Users(created_at);
CREATE INDEX idx_updated_at ON Users(updated_at);

-- ユーザー情報を更新する場合
UPDATE Users
SET first_name = '太郎',
    last_name = '田中',
    phone = '090-1234-5678',
    address = '東京都渋谷区',
    updated_at = NOW()
WHERE user_id = 1;

-- この1つのUPDATE文で以下のインデックスが更新される
-- 1. idx_first_name（first_nameの変更）
-- 2. idx_last_name（last_nameの変更）
-- 3. idx_phone（phoneの変更）
-- 4. idx_address（addressの変更）
-- 5. idx_updated_at（updated_atの変更）
-- 合計5つのインデックス更新が発生

-- インデックス数による挿入時間の比較
-- インデックスなし：1000件挿入に0.5秒
-- インデックス3個：1000件挿入に1.2秒
-- インデックス8個：1000件挿入に3.5秒（7倍の時間）

INSERT INTO Users (username, email, first_name, last_name, phone, address)
VALUES ('user1000', 'user1000@example.com', '花子', '佐藤', '080-9876-5432', '大阪府大阪市');

-- テーブルサイズとインデックスサイズの確認
SELECT
  table_name,
  ROUND(((data_length + index_length) / 1024 / 1024), 2) AS total_mb,
  ROUND((data_length / 1024 / 1024), 2) AS data_mb,
  ROUND((index_length / 1024 / 1024), 2) AS index_mb,
  ROUND((index_length / data_length) * 100, 2) AS index_ratio
FROM information_schema.tables
WHERE table_name = 'Users';

-- 結果例:
-- total_mb: 150.00
-- data_mb: 50.00
-- index_mb: 100.00 (データの2倍のサイズ)
-- index_ratio: 200.00%

-- インデックスのメモリ使用状況確認（MySQL）
SELECT
  index_name,
  table_name,
  cardinality,
  sub_part,
  packed,
  nullable,
  index_type
FROM information_schema.statistics
WHERE table_name = 'Users'
ORDER BY cardinality DESC;

-- 使用頻度の低いインデックスがメモリを無駄に消費

-- 複数のインデックスがある場合の実行計画
EXPLAIN SELECT * FROM Users
WHERE first_name = '太郎' AND last_name = '田中';

-- オプティマイザが以下のどれを選ぶか不確定:
-- 1. idx_first_name を使用
-- 2. idx_last_name を使用
-- 3. 複合インデックス（存在しない場合）
-- 4. フルテーブルスキャン

-- 結果として非効率な実行計画が選択される可能性

-- インデックス使用状況の確認（MySQL）
SELECT
  object_schema,
  object_name,
  index_name,
  count_read,
  count_write,
  sum_timer_read,
  sum_timer_write
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE object_schema = 'mydb' AND object_name = 'Users'
ORDER BY count_read DESC;

-- count_read = 0 のインデックスは使用されていない
-- 例: idx_address のcount_read = 0（住所での検索が行われていない）

-- よく使用されるクエリを分析
-- 1. ログイン処理
SELECT user_id FROM Users WHERE username = ? AND email = ?;

-- 2. ユーザー検索
SELECT * FROM Users WHERE email = ?;

-- 3. 作成日での絞り込み
SELECT * FROM Users WHERE created_at >= ?;

-- 必要なインデックスのみ作成
CREATE INDEX idx_username ON Users(username);
CREATE INDEX idx_email ON Users(email);
CREATE INDEX idx_created_at ON Users(created_at);
-- first_name, last_name, phone, address のインデックスは削除