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
|
CREATE TABLE IF NOT EXISTS projects (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
title VARCHAR(1024) NOT NULL,
description MEDIUMTEXT NOT NULL DEFAULT ''
);
CREATE TABLE IF NOT EXISTS users (
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(256) NOT NULL UNIQUE,
name VARCHAR(1024) NOT NULL DEFAULT '',
dn VARCHAR(256) NULL
);
CREATE TABLE IF NOT EXISTS project_users (
project BIGINT UNSIGNED NOT NULL,
user BIGINT UNSIGNED NOT NULL,
default_role TINYINT UNSIGNED NOT NULL,
maintainer BOOLEAN NOT NULL DEFAULT 0,
PRIMARY KEY (project, user),
CONSTRAINT `fk_project_users_project`
FOREIGN KEY (project) REFERENCES projects (id)
ON DELETE CASCADE
ON UPDATE RESTRICT,
CONSTRAINT `fk_project_users_user`
FOREIGN KEY (user) REFERENCES users (id)
ON DELETE CASCADE
ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS reviews (
id BIGINT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
project BIGINT UNSIGNED NOT NULL,
owner BIGINT UNSIGNED NOT NULL,
title VARCHAR(1024) NOT NULL,
description MEDIUMTEXT NOT NULL DEFAULT '',
state TINYINT UNSIGNED NOT NULL DEFAULT 0,
progress FLOAT NOT NULL DEFAULT 0,
CONSTRAINT `fk_reviews_project`
FOREIGN KEY (project) REFERENCES projects (id)
ON DELETE CASCADE
ON UPDATE RESTRICT,
CONSTRAINT `fk_reviews_owner`
FOREIGN KEY (owner) REFERENCES users (id)
ON DELETE CASCADE
ON UPDATE RESTRICT
);
CREATE TABLE IF NOT EXISTS review_users (
review BIGINT UNSIGNED NOT NULL,
user BIGINT UNSIGNED NOT NULL,
role TINYINT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (review, user),
CONSTRAINT `fk_review_users_review`
FOREIGN KEY (review) REFERENCES reviews (id)
ON DELETE CASCADE
ON UPDATE RESTRICT,
CONSTRAINT `fk_review_users_user`
FOREIGN KEY (user) REFERENCES users (id)
ON DELETE CASCADE
ON UPDATE RESTRICT
);
|
