tt6p9zAL

1use website_rest;
2ALTER DATABASE website_rest DEFAULT CHARACTER SET utf8 COLLATE utf8_bin;
3
4/* SET 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 TIMESTAMP(0) not null,
16  enabled smallint NOT NULL,
17  role VARCHAR (30) NOT NULL,
18  nickname varchar (16) UNIQUE,
19  PRIMARY KEY (googlesub)
20)  DEFAULT COLLATE
21 
22 CREATE INDEX (nickname); utf8_bin;
23
24
25CREATE SEQUENCE gameplans_seq;
26
27CREATE TABLE gameplans (
28	id int NOT NULL DEFAULT NEXTVAL ('gameplans_seq'),
29    title varchar(50) NOT NULL,
30    main_text varchar(3000),
31    author_googlesub varchar (50) NOT NULL,
32    creation_datetime TIMESTAMP(0) not null,
33    PRIMARY KEY (id),
34    CONSTRAINT FK_gameplans FOREIGN KEY (author_googlesub)
35    REFERENCES users (googlesub),
36    CONSTRAINT UQ_author_title_ UNIQUE(author_googlesub, title)
37)  DEFAULT COLLATE utf8_bin;
38
39CREATE SEQUENCE friendships_seq;
40
41CREATE TABLE friendships (
42id int NOT NULL DEFAULT NEXTVAL ('friendships_seq'),
43friend_1_googlesub varchar(255),
44friend_2_googlesub varchar(255),
45status  smallint NOT NULL,
46PRIMARY KEY (id)
47,
48CONSTRAINT fiends_combination UNIQUE  (friend_1_googlesub, friend_2_googlesub),
49
50CHECK(status between 0 and 1),
51CHECK(friend_1_googlesub<>friend_2_googlesub),
52
53CONSTRAINT FK_friend_1 FOREIGN KEY (friend_1_googlesub)
54REFERENCES users (googlesub),
55
56CONSTRAINT FK_friend_2 FOREIGN KEY (friend_2_googlesub)
57REFERENCES users (googlesub)
58)  DEFAULT COLLATE
59 
60 CREATE INDEX FK_friend_1_idx ON friendships (friend_1_googlesub);
61 CREATE INDEX FK_friend_2_idx ON friendships (friend_2_googlesub); utf8_bin;
62
63delimiter $
64create trigger uniquefriendships before insert on friendships
65for each row
66begin
67  if exists (
68    select *
69    from friendships
70    where friend_1_googlesub = new.friend_2_googlesub and friend_2_googlesub = new.friend_1_googlesub
71  )
72  then
73    signal sqlstate '45000' message_text := ' uniquefriendships trigger: inverse friendship exists! ';
74  end if;
75end$
76delimiter ;
77
78CREATE SEQUENCE gameplan_members_seq;
79
80CREATE TABLE gameplan_members (
81id int NOT NULL DEFAULT NEXTVAL ('gameplan_members_seq'),
82gameplan_id int NOT NULL,
83member_user_googlesub varchar(255),
84status smallint DEFAULT 0,
85PRIMARY KEY (id),
86
87CONSTRAINT FK1_game_members foreign key (gameplan_id) references gameplans (id),
88CONSTRAINT FK2_game_members foreign key (member_user_googlesub) references users (googlesub),
89
90CONSTRAINT gameplan_member_combination UNIQUE  (gameplan_id, member_user_googlesub),
91
92CHECK(status between 0 and 1)
93)  DEFAULT COLLATE utf8_bin;
94
95/* SET FOREIGN_KEY_CHECKS = 1; */