2u9M7zyR

1use website_rest;
2ALTER DATABASE `website_rest` DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
3
4SET FOREIGN_KEY_CHECKS = 0;
5
6drop table if exists users;
7drop table if exists gameplans;
8drop table if exists friendships;
9drop table if exists gameplan_members;
10
11CREATE TABLE users (
12  `googlesub` varchar(255) UNIQUE NOT NULL,
13  `name` varchar (50) NOT NULL,
14  `email` varchar (50) NOT NULL,
15  `creation_datetime` DATETIME not null,
16  `enabled` tinyint(1) NOT NULL,
17  `role` VARCHAR (30) NOT NULL,
18  `nickname` varchar (16) UNIQUE,
19  PRIMARY KEY (`googlesub`),
20  KEY (`nickname`)
21) ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8_bin;
22
23
24CREATE TABLE gameplans (
25	`id` int(11) NOT NULL AUTO_INCREMENT,
26    `title` varchar(50) NOT NULL,
27    `main_text` varchar(3000),
28    `author_googlesub` varchar (50) NOT NULL,
29    `creation_datetime` DATETIME not null,
30    PRIMARY KEY (`id`),
31    CONSTRAINT `FK_gameplans` FOREIGN KEY (`author_googlesub`)
32    REFERENCES `users` (`googlesub`),
33    CONSTRAINT `UQ_author_title_` UNIQUE(`author_googlesub`, `title`)
34) ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8_bin;
35
36CREATE TABLE friendships (
37`id` int(11) NOT NULL AUTO_INCREMENT,
38`friend_1_googlesub` varchar(255),
39`friend_2_googlesub` varchar(255),
40`status`  tinyint(1) NOT NULL,
41PRIMARY KEY (`id`),
42KEY `FK_friend_1_idx` (`friend_1_googlesub`),
43KEY `FK_friend_2_idx` (`friend_2_googlesub`),
44UNIQUE KEY `fiends_combination` (`friend_1_googlesub`, `friend_2_googlesub`),
45
46CHECK(`status` between 0 and 1),
47CHECK(`friend_1_googlesub`<>`friend_2_googlesub`),
48
49CONSTRAINT `FK_friend_1` FOREIGN KEY (`friend_1_googlesub`)
50REFERENCES `users` (`googlesub`),
51
52CONSTRAINT `FK_friend_2` FOREIGN KEY (`friend_2_googlesub`)
53REFERENCES `users` (`googlesub`)
54) ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8_bin;
55
56delimiter $
57create trigger uniquefriendships before insert on friendships
58for each row
59begin
60  if exists (
61    select *
62    from friendships
63    where friend_1_googlesub = new.friend_2_googlesub and friend_2_googlesub = new.friend_1_googlesub
64  )
65  then
66    signal sqlstate '45000' set message_text = ' uniquefriendships trigger: inverse friendship exists! ';
67  end if;
68end$
69delimiter ;
70
71CREATE TABLE gameplan_members (
72`id` int(11) NOT NULL AUTO_INCREMENT,
73`gameplan_id` int(11) NOT NULL,
74`member_user_googlesub` varchar(255),
75`status` tinyint(1) DEFAULT 0,
76PRIMARY KEY (`id`),
77
78CONSTRAINT `FK1_game_members` foreign key (`gameplan_id`) references `gameplans` (`id`),
79CONSTRAINT `FK2_game_members` foreign key (`member_user_googlesub`) references `users` (`googlesub`),
80
81UNIQUE KEY `gameplan_member_combination` (`gameplan_id`, `member_user_googlesub`),
82
83CHECK(`status` between 0 and 1)
84) ENGINE=InnoDB DEFAULT CHARSET=utf8 DEFAULT COLLATE utf8_bin;
85
86SET FOREIGN_KEY_CHECKS = 1;