1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
| CREATE TABLE IF NOT EXISTS 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 IF NOT EXISTS BugStatus (
`status` VARCHAR(20) PRIMARY KEY
);
CREATE TABLE IF NOT EXISTS Bugs (
bug_id SERIAL PRIMARY KEY,
date_reported DATE NOT NULL,
summary VARCHAR(80),
description VARCHAR(1000),
resolition 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 IF NOT EXISTS Comments (
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 IF NOT EXISTS Screenhots (
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 IF NOT EXISTS 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 IF NOT EXISTS Products (
product_id SERIAL PRIMARY KEY,
product_name VARCHAR(50) NOT NULL
);
CREATE TABLE IF NOT EXISTS 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)
);
|