-- SQLアンチパターン 7章 マルチカラムアトリビュート

-- ■7.2 前準備
-- MySQL
CREATE TABLE Bugs_anti (
  bug_id      SERIAL PRIMARY KEY,
  description VARCHAR(1000),
  tag1        VARCHAR(20),
  tag2        VARCHAR(20),
  tag3        VARCHAR(20)
);

-- HiRDB, ORACLE
CREATE TABLE Bugs_anti (
  bug_id      INT,
  description VARCHAR(1000),
  tag1        VARCHAR(20),
  tag2        VARCHAR(20),
  tag3        VARCHAR(20),
  PRIMARY KEY (bug_id)
);

INSERT INTO Bugs_anti (bug_id, description, tag1, tag2, tag3) VALUES 
  (1234, '保存処理でクラッシュする', 'crash', NULL, NULL), 
  (3456, 'パフォーマンスの向上', 'printing', 'performance', NULL), 
  (5678, 'XMLのサポート', NULL, NULL, NULL);

-- ■7.2.1
SELECT * FROM Bugs_anti 
WHERE tag1 = 'performance' 
   OR tag2 = 'performance' 
   OR tag3 = 'performance';

SELECT * FROM Bugs_anti 
WHERE (tag1 = 'performance' OR tag2 = 'performance' OR tag3 = 'performance') 
  AND (tag1 = 'printing' OR tag2 = 'printing' OR tag3 = 'printing');

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


-- ■7.2.2
SELECT * FROM Bugs_anti WHERE bug_id = 3456;

UPDATE Bugs_anti SET tag2 = 'performance' WHERE bug_id = 3456;

UPDATE Bugs_anti
SET tag1 = NULLIF(tag1, 'performance'),
    tag2 = NULLIF(tag2, 'performance'),
    tag3 = NULLIF(tag3, 'performance')
WHERE bug_id = 3456;

UPDATE Bugs_anti 
SET tag1 = CASE 
      WHEN 'performance' IN (tag2, tag3) THEN tag1 
      ELSE COALESCE(tag1, 'performance') END, 
    tag2 = CASE 
      WHEN 'performance' IN (tag1, tag3) THEN tag2 
      ELSE COALESCE(tag2, 'performance') END, 
    tag3 = CASE 
      WHEN 'performance' IN (tag1, tag2) THEN tag3 
      ELSE COALESCE(tag3, 'performance') END 
WHERE bug_id = 3456;

-- ■7.2.3
INSERT INTO Bugs_anti (description, tag1, tag2, tag3)
  VALUES ('印刷処理が遅い', 'printing', 'performance', 'performance');
  
-- ■7.2.4
ALTER TABLE Bugs_anti ADD COLUMN tag4 VARCHAR(20);

SELECT * FROM Bugs_anti
WHERE tag1 = 'performance'
   OR tag2 = 'performance'
   OR tag3 = 'performance'
   OR tag4 = 'performance';

-- ■7.5
CREATE TABLE Bugs_soln (
  bug_id      SERIAL PRIMARY KEY,
  description VARCHAR(1000)
);


CREATE Table Tags_soln (
  bug_id  BIGINT UNSIGNED NOT NULL,
  tag VARCHAR(20),
  PRIMARY KEY (bug_id, tag),
  FOREIGN KEY (bug_id) REFERENCES Bugs_soln(bug_id)
);

INSERT INTO Bugs_soln (bug_id, description) VALUES 
  (1234, '保存処理でクラッシュする'),
  (3456, 'パフォーマンスの向上'),
  (5678, 'XMLのサポート');
  
INSERT INTO Tags_soln (bug_id, tag) VALUES
  (1234, 'crash'),
  (3456, 'printing'),
  (3456, 'performance');


SELECT * FROM Bugs_soln INNER JOIN Tags_soln USING(bug_id)
WHERE tag = 'performance';

SELECT * FROM Bugs_soln
  INNER JOIN Tags_soln AS t1 USING (bug_id)
  INNER JOIN Tags_soln AS t2 USING (bug_id)
WHERE t1.tag = 'printing' AND t2.tag = 'performance';


INSERT INTO Tags_soln (bug_id, tag) VALUES (1234, 'save');

DELETE FROM Tags_soln WHERE bug_id = 1234 AND tag = 'crash';

-- ■HiRDB Array型カラム
CREATE TABLE Bugs_array (
  bug_id      INT,
  description VARCHAR(1000),
  tag         VARCHAR(20) ARRAY[3],
  PRIMARY KEY (bug_id)
);

INSERT INTO Bugs_array (bug_id, description, tag) 
  VALUES (3456, 'パフォーマンスの向上', ARRAY['printing', 'performance', NULL]);
  
SELECT bug_id, description, tag[1] 
FROM Bugs_array 
WHERE tag[2] = 'performance';