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
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
|
CREATE TABLE IF NOT EXISTS theme(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
main VARCHAR(255) NOT NULL,
description VARCHAR(255) NOT NULL,
votes INTEGER DEFAULT FALSE,
hidden BOOLEAN DEFAULT FALSE,
UNIQUE KEY main (main, description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- TODO: Remove dropping
-- DROP TABLE IF EXISTS motto_votes;
-- DROP TABLE IF EXISTS mottos;
-- DROP TABLE IF EXISTS quotes;
-- DROP TABLE IF EXISTS ranking_questions;
-- DROP TABLE IF EXISTS ranking_answers;
-- DROP TABLE IF EXISTS profile_comments;
-- DROP TABLE IF EXISTS profile_answers;
-- DROP TABLE IF EXISTS profile_questions;
-- DROP TABLE IF EXISTS users;
-- DROP TABLE IF EXISTS types;
-- DROP TABLE IF EXISTS class;
CREATE TABLE IF NOT EXISTS types(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS class(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS users(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(255) NOT NULL UNIQUE,
name VARCHAR(255) NOT NULL,
middlename VARCHAR(255) DEFAULT NULL,
surname VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
class_id INTEGER NOT NULL,
type_id INTEGER NOT NULL,
UNIQUE KEY uk_name (name, middlename, surname),
CONSTRAINT `fk_class_user` FOREIGN KEY (class_id) REFERENCES class (id),
CONSTRAINT `fk_type_user` FOREIGN KEY (type_id) REFERENCES types (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS quotes(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
user_id INTEGER NOT NULL, -- Person who heard it
author_id INTEGER NOT NULL, -- Person who said it
quote VARCHAR(255) NOT NULL,
UNIQUE KEY uk_quote (author_id, quote),
CONSTRAINT `fk_user_quote1` FOREIGN KEY (user_id) REFERENCES users (id),
CONSTRAINT `fk_user_quote2` FOREIGN KEY (author_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS ranking_questions(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
question VARCHAR(255) NOT NULL,
type_id INTEGER NOT NULL,
UNIQUE KEY uk_question (question, type_id),
CONSTRAINT `fk_type_question` FOREIGN KEY (type_id) REFERENCES types (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS ranking_answers(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
question_id INTEGER NOT NULL,
user_id INTEGER NOT NULL, -- Submitter
answer_id INTEGER NOT NULL, -- Selected pupil
UNIQUE KEY uk_answer (question_id, user_id),
CONSTRAINT `fk_question_answer` FOREIGN KEY (question_id) REFERENCES users (id),
CONSTRAINT `fk_user_answer1` FOREIGN KEY (user_id) REFERENCES users (id),
CONSTRAINT `fk_user_answer2` FOREIGN KEY (answer_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS mottos(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
description VARCHAR(255) NOT NULL DEFAULT '',
UNIQUE KEY main (name, description)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS motto_votes(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
user_id INTEGER NOT NULL,
motto_id INTEGER NOT NULL,
votes SMALLINT UNSIGNED NOT NULL DEFAULT 0,
UNIQUE KEY uk_vote (user_id, motto_id),
CONSTRAINT `fk_voted_user` FOREIGN KEY (user_id) REFERENCES users (id),
CONSTRAINT `fk_voted_vote` FOREIGN KEY (motto_id) REFERENCES mottos (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS profile_questions(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
question VARCHAR(255) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS profile_answers(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
question_id INTEGER NOT NULL,
user_id INTEGER NOT NULL,
answer TEXT NULL, -- Consider VARCHAR
UNIQUE KEY uk_answer (question_id, user_id),
CONSTRAINT `fk_profile_user` FOREIGN KEY (user_id) REFERENCES users (id),
CONSTRAINT `fk_profile_question` FOREIGN KEY (question_id) REFERENCES profile_questions (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE IF NOT EXISTS profile_comments(
id INTEGER PRIMARY KEY AUTO_INCREMENT,
profile_id INTEGER NOT NULL, -- User's profile
user_id INTEGER NOT NULL, -- User who commented
comment TEXT NOT NULL,
CONSTRAINT `fk_user_profile` FOREIGN KEY (profile_id) REFERENCES users (id),
CONSTRAINT `fk_user_commenter` FOREIGN KEY (user_id) REFERENCES users (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
|
