· 8 years ago · Mar 05, 2018, 11:44 AM
1-- Role
2CREATE TABLE IF NOT EXISTS `projekt`.`role` (
3 `id_role` INT NOT NULL AUTO_INCREMENT,
4 `name_role` VARCHAR(50) NOT NULL,
5 PRIMARY KEY (`id_role`)
6 );
7
8-- User
9CREATE TABLE IF NOT EXISTS `projekt`.`user` (
10 `id_user` INT NOT NULL AUTO_INCREMENT,
11 `name_user` VARCHAR(50) NOT NULL UNIQUE,
12 `login_user` VARCHAR(50) NOT NULL UNIQUE,
13 `password_user` VARCHAR(50) NOT NULL,
14 `id_role_fk` INT NOT NULL,
15 PRIMARY KEY (`id_user`),
16 FOREIGN KEY (id_role_fk) REFERENCES role (id_role)
17 );
18
19-- Group
20CREATE TABLE IF NOT EXISTS `projekt`.`group` (
21 `id_group` INT NOT NULL AUTO_INCREMENT,
22 `name_group` VARCHAR(50) NOT NULL UNIQUE,
23 PRIMARY KEY (`id_group`)
24 );
25
26-- UserGroup
27CREATE TABLE IF NOT EXISTS `projekt`.`usergroup` (
28 `id_usergroup` INT NOT NULL AUTO_INCREMENT,
29 `id_user_fk` INT NOT NULL,
30 `id_group_fk` INT NOT NULL,
31 PRIMARY KEY (`id_usergroup`),
32 FOREIGN KEY (id_user_fk) REFERENCES `user` (id_user),
33 FOREIGN KEY (id_group_fk) REFERENCES `group` (id_group)
34 );
35
36-- Task
37CREATE TABLE IF NOT EXISTS `projekt`.`task` (
38 `id_task` INT NOT NULL AUTO_INCREMENT,
39 `name_task` VARCHAR(50) NOT NULL,
40 `timestamp_task` DATE NOT NULL,
41 `date_task` DATE NOT NULL,
42 `text_task` TEXT NOT NULL,
43 `done_task` BOOLEAN NOT NULL,
44 `id_group_fk` INT NOT NULL,
45 PRIMARY KEY (`id_task`),
46 FOREIGN KEY (id_group_fk) REFERENCES `group` (id_group)
47 );
48
49-- Comment
50CREATE TABLE IF NOT EXISTS `projekt`.`comment` (
51 `id_comment` INT NOT NULL AUTO_INCREMENT,
52 `text_comment` TEXT NOT NULL,
53 `id_user_fk` INT NOT NULL,
54 `id_task_fk` INT NOT NULL,
55 `timestamp_comment` DATETIME DEFAULT CURRENT_TIMESTAMP,
56 PRIMARY KEY (`id_comment`),
57 FOREIGN KEY (id_user_fk) REFERENCES `user` (id_user),
58 FOREIGN KEY (id_task_fk) REFERENCES `task` (id_task)
59 );
60
61
62-- Test roles
63INSERT INTO `role` (name_role) VALUES ("admin");
64INSERT INTO `role` (name_role) VALUES ("user");
65
66-- Test users and groups
67INSERT INTO `user` (name_user, login_user, password_user, id_role_fk)
68VALUES ("Janusz", "admin1", "a", (SELECT `id_role` FROM `role` WHERE `name_role` = "admin"));
69INSERT INTO `group` (name_group) VALUES ("Janusz");
70
71INSERT INTO `usergroup` (id_user_fk, id_group_fk)
72
73VALUES ((SELECT `id_user` FROM `user` WHERE `name_user` = "Janusz"), (SELECT `id_group` FROM `group` WHERE `name_group` = "Janusz"));
74
75
76INSERT INTO `user` (name_user, login_user, password_user, id_role_fk)
77VALUES ("Andrzej", "user1", "u", (SELECT `id_role` FROM `role` WHERE `name_role` = "user"));
78
79INSERT INTO `group` (name_group) VALUES ("Andrzej");
80
81INSERT INTO `usergroup` (id_user_fk, id_group_fk)
82VALUES ((SELECT `id_user` FROM `user` WHERE `name_user` = "Andrzej"), (SELECT `id_group` FROM `group` WHERE `name_group` = "Andrzej"));
83
84
85INSERT INTO `user` (name_user, login_user, password_user, id_role_fk)
86VALUES ("Paweł", "user2", "u", (SELECT `id_role` FROM `role` WHERE `name_role` = "user"));
87
88INSERT INTO `group` (name_group) VALUES ("Paweł");
89
90INSERT INTO `usergroup` (id_user_fk, id_group_fk)
91VALUES ((SELECT `id_user` FROM `user` WHERE `name_user` = "Paweł"), (SELECT `id_group` FROM `group` WHERE `name_group` = "Paweł"));
92
93
94INSERT INTO `user` (name_user, login_user, password_user, id_role_fk)
95VALUES ("Grażyna", "user3", "u", (SELECT `id_role` FROM `role` WHERE `name_role` = "user"));
96
97INSERT INTO `group` (name_group) VALUES ("Grażyna");
98
99INSERT INTO `usergroup` (id_user_fk, id_group_fk)
100VALUES ((SELECT `id_user` FROM `user` WHERE `name_user` = "Grażyna"), (SELECT `id_group` FROM `group` WHERE `name_group` = "Grażyna"));
101
102
103INSERT INTO `group` (name_group) VALUES ("GrażynaIPaweł");
104
105INSERT INTO `usergroup` (id_user_fk, id_group_fk)
106VALUES ((SELECT `id_user` FROM `user` WHERE `name_user` = "Grażyna"), (SELECT `id_group` FROM `group` WHERE `name_group` = "GrażynaIPaweł"));
107
108INSERT INTO `usergroup` (id_user_fk, id_group_fk)
109VALUES ((SELECT `id_user` FROM `user` WHERE `name_user` = "Paweł"), (SELECT `id_group` FROM `group` WHERE `name_group` = "GrażynaIPaweł"));
110
111-- Test tasks
112INSERT INTO `task` (name_task, timestamp_task, date_task, text_task, done_task, id_group_fk)
113VALUES ("Task1",
114 CURRENT_TIMESTAMP,
115 CURRENT_TIMESTAMP + INTERVAL 14 DAY,
116 "To bardzo ważny task, który zwany jest jako The One Task. Zrób to teraz.",
117 0,
118 (SELECT `id_group` FROM `group` WHERE `name_group` = "Grażyna")
119 );
120
121INSERT INTO `task` (name_task, timestamp_task, date_task, text_task, done_task, id_group_fk)
122VALUES ("Task2",
123 CURRENT_TIMESTAMP,
124 CURRENT_TIMESTAMP + INTERVAL 10 DAY,
125 "To bardzo ważny task, który zwany jest jako Secundo Taskundo. Zrób to teraz.",
126 0,
127 (SELECT `id_group` FROM `group` WHERE `name_group` = "GrażynaIPaweł")
128 );
129
130INSERT INTO `task` (name_task, timestamp_task, date_task, text_task, done_task, id_group_fk)
131VALUES ("Task3",
132 CURRENT_TIMESTAMP,
133 CURRENT_TIMESTAMP + INTERVAL 5 DAY,
134 "To bardzo nieważny task, który zwany jest jako The Tree. Zrób to teraz.",
135 0,
136 (SELECT `id_group` FROM `group` WHERE `name_group` = "Paweł")
137 );
138
139-- Test comments
140INSERT INTO `comment` (text_comment, id_user_fk, id_task_fk)
141VALUES ("Pierwszy!",
142 (SELECT `id_user` FROM `user` WHERE `name_user` = "Paweł"),
143 (SELECT `id_task` FROM `task` WHERE `name_task` = "Task1")
144 );
145
146INSERT INTO `comment` (text_comment, id_user_fk, id_task_fk)
147VALUES ("Task1 jest najlepszy!",
148 (SELECT `id_user` FROM `user` WHERE `name_user` = "Paweł"),
149 (SELECT `id_task` FROM `task` WHERE `name_task` = "Task1")
150 );
151
152INSERT INTO `comment` (text_comment, id_user_fk, id_task_fk)
153VALUES ("Cześć, jestem Grażyna!",
154 (SELECT `id_user` FROM `user` WHERE `name_user` = "Andrzej"),
155 (SELECT `id_task` FROM `task` WHERE `name_task` = "Task1")
156 );
157
158INSERT INTO `comment` (text_comment, id_user_fk, id_task_fk)
159VALUES ("Nie.",
160 (SELECT `id_user` FROM `user` WHERE `name_user` = "Janusz"),
161 (SELECT `id_task` FROM `task` WHERE `name_task` = "Task1")
162 );
163
164INSERT INTO `comment` (text_comment, id_user_fk, id_task_fk)
165VALUES ("Cześć, jestem Grażyna!",
166 (SELECT `id_user` FROM `user` WHERE `name_user` = "Andrzej"),
167 (SELECT `id_task` FROM `task` WHERE `name_task` = "Task2")
168 );
169
170INSERT INTO `comment` (text_comment, id_user_fk, id_task_fk)
171VALUES ("Przestań.",
172 (SELECT `id_user` FROM `user` WHERE `name_user` = "Janusz"),
173 (SELECT `id_task` FROM `task` WHERE `name_task` = "Task2")
174 );
175
176INSERT INTO `comment` (text_comment, id_user_fk, id_task_fk)
177VALUES ("Cześć, jestem Grażyna!",
178 (SELECT `id_user` FROM `user` WHERE `name_user` = "Grażyna"),
179 (SELECT `id_task` FROM `task` WHERE `name_task` = "Task3")
180 );
181
182INSERT INTO `comment` (text_comment, id_user_fk, id_task_fk)
183VALUES ("*User successfully banned.*",
184 (SELECT `id_user` FROM `user` WHERE `name_user` = "Janusz"),
185 (SELECT `id_task` FROM `task` WHERE `name_task` = "Task3")
186 );
187
188INSERT INTO `comment` (text_comment, id_user_fk, id_task_fk)
189VALUES ("Ups, to jednak prawdziwa Grażyna...",
190 (SELECT `id_user` FROM `user` WHERE `name_user` = "Janusz"),
191 (SELECT `id_task` FROM `task` WHERE `name_task` = "Task3")
192 );