gistfile1.sql

DROP TABLE DiscussionComment;
DROP TABLE DiscussionRoot;
DROP TABLE BugComponent;
DROP TABLE Component;
DROP TABLE Project;
DROP TABLE Bug;
DROP TABLE BugStatus;
DROP TABLE qaengineer;
DROP TABLE ProjectManager;
DROP TABLE Employee;

CREATE TABLE Employee(
        id INT PRIMARY KEY,
		email character varying(256) UNIQUE, 
		name character varying(256));

CREATE TABLE qaengineer (
		id INT PRIMARY KEY,
		FOREIGN KEY(id) REFERENCES Employee
);

CREATE TABLE ProjectManager (
		id INT PRIMARY KEY,
		FOREIGN KEY(id) REFERENCES Employee
);

CREATE TABLE BugStatus(id INT PRIMARY KEY, value CHAR(20) NOT NULL);


CREATE TABLE bug (
		num integer PRIMARY KEY, 
		title character varying(256), 
		submission_date DATE,
		owner_id INT, 
		status_id INT,
		FOREIGN KEY(owner_id) REFERENCES qaengineer,
		FOREIGN KEY(status_id) REFERENCES BugStatus);


CREATE TABLE Project (
		id INT PRIMARY KEY,
        title VARCHAR(256),
        manager_id INT,
        FOREIGN KEY(manager_id) REFERENCES ProjectManager
	);


CREATE TABLE component (
		id INT PRIMARY KEY, 
		title character varying(256),
		project_id INT,
		FOREIGN KEY(project_id) REFERENCES Project
	);


CREATE TABLE BugComponent(
		bug_num INT, 
		component_id INT, 
		UNIQUE(bug_num, component_id),
		FOREIGN KEY (bug_num) REFERENCES Bug, 
		FOREIGN KEY (component_id) REFERENCES Component
	);

CREATE TABLE DiscussionRoot(
        id INT PRIMARY KEY,
        message TEXT,
        starter_id INT,
        bug_id INT,
        FOREIGN KEY(bug_id) References Bug,
        FOREIGN KEY(starter_id) REFERENCES Employee
);

-- Таблица с деревом комментариев приведена для справки. Для ДЗ №5 она несущественна 
CREATE TABLE DiscussionComment(id INT PRIMARY KEY, 
    message TEXT, 
    parent_id INT, 
    root_id INT, 
    author_id INT,
  FOREIGN KEY(parent_id) REFERENCES DiscussionComment,
  FOREIGN KEY(root_id) REFERENCES DiscussionRoot(id),
  FOREIGN KEY(author_id) REFERENCES Employee,
  CHECK(parent_id IS NOT NULL AND root_id IS NULL OR parent_id IS NULL AND root_id IS NOT NULL));

DROP TABLE DiscussionRoot;
DROP FUNCTION checkDiscussionRoot;

CREATE FUNCTION checkDiscussionRoot(integer, integer) RETURNS boolean
    AS '
    $1 = (select manager_id from Progect where Project.id = $2)  
' LANGUAGE sql;

CREATE TABLE DiscussionRoot(
        id INT PRIMARY KEY,
        message TEXT,
        starter_id INT,
        bug_id INT,
        component_id INT,
        project_id INT,
        FOREIGN KEY(bug_id) References Bug,
        FOREIGN KEY(project_id) References Project,
        FOREIGN KEY(component_id) References component,
        FOREIGN KEY(starter_id) REFERENCES Employee,
        CHECK((bug_id is not null and component_id is null and project_id is null) or
            (bug_id is null and component_id is not null and project_id is null) or
            (bug_id is null and component_id is null and project_id is not null)
        ),
        CHECK(project_id is null or (project_id is not null and checkDiscussionRoot(starter_id, project_id))
);