■前準備（テーブルのCreate）

CREATE TABLE Accounts (
  account_id        SERIAL PRIMARY KEY,
  account_name      VARCHAR(20),
  first_name        VARCHAR(20),
  last_name         VARCHAR(20),
  email             VARCHAR(100),
  password_hash     CHAR(64),
  portrait_image    BLOB,
  hourly_rate       NUMERIC(9,2)
);

CREATE TABLE BugStatus (
  status            VARCHAR(20) PRIMARY KEY
);

CREATE TABLE Bugs (
  bug_id SERIAL     PRIMARY KEY,
  date_reported     DATE NOT NULL,
  summary           VARCHAR(80),
  description       VARCHAR(1000),
  resolution        VARCHAR(1000),
  reported_by       BIGINT UNSIGNED NOT NULL,
  assigned_to       BIGINT UNSIGNED,
  verified_by       BIGINT UNSIGNED,
  status            VARCHAR(20) NOT NULL DEFAULT 'NEW',
  priority          VARCHAR(20),
  hours             NUMERIC(9,2),
  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),
  FOREIGN KEY (assigned_to) REFERENCES Accounts(account_id),
  FOREIGN KEY (verified_by) REFERENCES Accounts(account_id),
  FOREIGN KEY (status) REFERENCES BugStatus(status)
);

CREATE TABLE Screenshots (
  bug_id            BIGINT UNSIGNED NOT NULL,
  image_id          BIGINT UNSIGNED NOT NULL,
  screenshot_image  BLOB,
  caption           VARCHAR(100),
  PRIMARY KEY (bug_id, image_id),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);

CREATE TABLE Tags (
  bug_id            BIGINT UNSIGNED NOT NULL,
  tag               VARCHAR(20) NOT NULL,
  PRIMARY KEY (bug_id, tag),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id)
);

CREATE TABLE Products (
  product_id        SERIAL PRIMARY KEY,
  product_name      VARCHAR(50)
);

CREATE TABLE BugsProducts(
  bug_id            BIGINT UNSIGNED NOT NULL,
  product_id        BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (bug_id, product_id),
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);


create table comments_anti (
  comment_id serial primary key,
  parent_id bigint unsigned,
  bug_id bigint unsigned not null,
  author bigint unsigned not null,
  comment_date datetime not null,
  comment text not null,
  foreign key (parent_id) references comments_anti(comment_id),
  foreign key (bug_id) references bugs(bug_id),
  foreign key (author) references accounts(account_id)
);

■前準備２（データのInsert）
INSERT INTO Accounts (account_name) VALUES ('Fran'), ('Ollie'), ('Kukla');

INSERT INTO BugStatus VALUES ('NEW');

INSERT INTO Products (product_name) VALUES
  ('Open RoundFile'),
  ('Visual TurboBuilder'),
  ('ReConsider');

INSERT INTO Bugs   
(date_reported, summary, reported_by) 
VALUES
  (CURRENT_DATE, 'crash when I save', 1),
  (CURRENT_DATE, 'increase performance', 2),
  (CURRENT_DATE, 'screen goes blank', 3),
  (CURRENT_DATE, 'unknown conflict between products', 1)  ;
  
INSERT INTO BugsProducts (bug_id, product_id) VALUES
  (1, 1),
  (2, 3),
  (3, 2),
  (2, 1),
  (3, 3);

insert into comments_anti (comment_id, parent_id, author, comment, bug_id, comment_date) values
  (1, null, 1, 'このバグの原因は何かな？'                         ,1,CURRENT_DATE),
  (2,    1, 2, 'ヌルポインターのせいじゃないかな？'               ,1,CURRENT_DATE),
  (3,    2, 1, 'そうじゃないよ。それは確認済みだ。'               ,1,CURRENT_DATE),
  (4,    1, 3, '無効な入力を調べてみたら？'                       ,1,CURRENT_DATE),
  (5,    4, 2, 'そうか、バグの原因はそれだな'                     ,1,CURRENT_DATE),
  (6,    4, 1, 'よし、じゃあチェック機能を追加してもらえるかな？' ,1,CURRENT_DATE),
  (7,    6, 3, '了解。修正したよ'                                 ,1,CURRENT_DATE);
  
■2.2.1 parent.sql
SELECT c1.comment_id as c1_comment_id, c1.comment as c1_comment, 
       c2.comment_id as c2_comment_id, c2.parent_id as c2_parent_id, 
       c2.comment as c2_comment
FROM comments_anti c1 LEFT OUTER JOIN comments_anti c2
  ON c2.parent_id = c1.comment_id;

■2.2.1 ancestors.sql
SELECT c1.*, c2.*, c3.*, c4.*
FROM comments_anti c1 -- 1階層目
  LEFT OUTER JOIN comments_anti c2
    ON c2.parent_id = c1.comment_id -- 2階層目
  LEFT OUTER JOIN comments_anti c3
    ON c3.parent_id = c2.comment_id -- 3階層目
  LEFT OUTER JOIN comments_anti c4
    ON c4.parent_id = c3.comment_id; -- 4階層目

■2.2.2  ON DELETE CASCADE
create table comments_anti (
  comment_id serial primary key,
  parent_id bigint unsigned,
  bug_id bigint unsigned not null,
  author bigint unsigned not null,
  comment_date datetime not null,
  comment text not null,
  foreign key (parent_id) references comments_anti(comment_id) ON DELETE CASCADE,
  foreign key (bug_id) references bugs(bug_id),
  foreign key (author) references accounts(account_id)
);

■2.5.1
CREATE TABLE comments_enum (
  comment_id serial primary key,
  path varchar(1000),
  bug_id bigint unsigned not null,
  author bigint unsigned not null,
  comment_date datetime not null,
  comment text not null,
  foreign key (bug_id) references bugs(bug_id),
  foreign key (author) references accounts(account_id)
);

■表2-2
insert into comments_enum (comment_id, path, author, comment, bug_id, comment_date) values
  (1, '1/'      , 1, 'このバグの原因は何かな？'                         ,1,CURRENT_DATE),
  (2, '1/2/'    , 2, 'ヌルポインターのせいじゃないかな？'               ,1,CURRENT_DATE),
  (3, '1/2/3/'  , 1, 'そうじゃないよ。それは確認済みだ。'               ,1,CURRENT_DATE),
  (4, '1/4/'    , 3, '無効な入力を調べてみたら？'                       ,1,CURRENT_DATE),
  (5, '1/4/5/'  , 2, 'そうか、バグの原因はそれだな'                     ,1,CURRENT_DATE),
  (6, '1/4/6/'  , 1, 'よし、じゃあチェック機能を追加してもらえるかな？' ,1,CURRENT_DATE),
  (7, '1/4/6/7/', 3, '了解。修正したよ'                                 ,1,CURRENT_DATE);

■2.5.1  ancestors.sql
SELECT * FROM comments_enum as c
WHERE '1/4/6/7' LIKE concat(c.path, '%');

■2.5.1  descendants.sql
SELECT * FROM comments_enum as c
WHERE c.path LIKE concat('1/4/', '%'); 

■2.5.1  count.sql
SELECT c.author, a.account_name, COUNT(*)
FROM comments_enum as c, accounts as a
WHERE c.path LIKE concat('1/4/', '%') and c.author = a.account_id
GROUP BY c.author;

■2.5.1  insert.sql
INSERT INTO comments_enum (bug_id, author, comment_date, comment)
  VALUES (1, 1, CURRENT_TIMESTAMP, 'ありがとう！');

■2.5.2
create_table.sql

CREATE TABLE comments_nest (
comment_id serial primary key,
nsleft integer not null,
nsright integer not null,
bug_id bigint unsigned not null,
author bigint unsigned not null,
comment_date datetime not null,
comment text not null,
foreign key (bug_id) references bugs (bug_id),
foreign key (author) references accounts(account_id)
);

■表2-3
INSERT INTO comments_nest 
(comment_id, nsleft, nsright, bug_id, author, comment_date, comment)
VALUES
(1,  1, 14, 1, 1, CURRENT_TIMESTAMP, 'このバグの原因は何かな？'),
(2,  2,  5, 1, 2, CURRENT_TIMESTAMP, 'ヌルポインターのせいじゃないかな？'),
(3,  3,  4, 1, 1, CURRENT_TIMESTAMP, 'そうじゃないよ。それは確認済みだ。'),
(4,  6, 13, 1, 3, CURRENT_TIMESTAMP, '無効なインプットを調べてみたら？'),
(5,  7,  8, 1, 2, CURRENT_TIMESTAMP, 'そうか、バグの原因はそれだな。'),
(6,  9, 12, 1, 1, CURRENT_TIMESTAMP, 'よし、じゃあチェック機能を追加してもらえるかな？'),
(7, 10, 11, 1, 3, CURRENT_TIMESTAMP, '了解。修正したよ。');

■2.5.2  descendants.sql
SELECT c2.*
FROM comments_nest AS c1
  INNER JOIN comments_nest as c2
    ON c2.nsleft BETWEEN c1.nsleft AND c1.nsright
WHERE c1.comment_id = 4;

■2.5.3
CREATE TABLE comments_closure (
  comment_id   SERIAL PRIMARY KEY,
  bug_id       BIGINT UNSIGNED NOT NULL,
  author       BIGINT UNSIGNED NOT NULL,
  comment_date DATETIME NOT NULL,
  comment      TEXT NOT NULL,
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (author) REFERENCES Accounts(account_id)
);

CREATE TABLE TreePaths (
  ancestor    BIGINT UNSIGNED NOT NULL,
  descendant  BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (ancestor, descendant),
  FOREIGN KEY (ancestor) REFERENCES comments_closure(comment_id),
  FOREIGN KEY (descendant) REFERENCES comments_closure(comment_id)
);

INSERT INTO comments_closure 
(comment_id, bug_id, author, comment_date, comment)
VALUES
(1, 1, 1, CURRENT_TIMESTAMP, 'このバグの原因は何かな？'),
(2, 1, 2, CURRENT_TIMESTAMP, 'ヌルポインターのせいじゃないかな？'),
(3, 1, 1, CURRENT_TIMESTAMP, 'そうじゃないよ。それは確認済みだ。'),
(4, 1, 3, CURRENT_TIMESTAMP, '無効なインプットを調べてみたら？'),
(5, 1, 2, CURRENT_TIMESTAMP, 'そうか、バグの原因はそれだな。'),
(6, 1, 1, CURRENT_TIMESTAMP, 'よし、じゃあチェック機能を追加してもらえるかな？'),
(7, 1, 3, CURRENT_TIMESTAMP, '了解。修正したよ。');


insert into treepaths (ancestor, descendant) VALUES
(1,1),
(1,2),
(1,3),
(1,4),
(1,5),
(1,6),
(1,7),
(2,2),
(2,3),
(3,3),
(4,4),
(4,5),
(4,6),
(4,7),
(5,5),
(6,6),
(6,7),
(7,7);