· 8 years ago · Dec 25, 2017, 07:32 PM
1CREATE TABLE user_data (
2 id_user INTEGER(20) PRIMARY KEY ,
3 email VARCHAR(255) DEFAULT NULL,
4 enabled NUMBER(1) DEFAULT '0',
5 password VARCHAR(255) DEFAULT NULL,
6 username VARCHAR(255) DEFAULT NULL);
7
8CREATE TABLE IF NOT EXISTS `user_role` (
9 `id_user_role` BIGINT(20) NOT NULL,
10 `description` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
11 `role` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
12 PRIMARY KEY (`id_user_role`)
13)
14 ENGINE = InnoDB
15 AUTO_INCREMENT = 3
16 DEFAULT CHARSET = utf8
17 COLLATE = utf8_bin;
18
19CREATE TABLE IF NOT EXISTS `user_roles` (
20 `user_id` BIGINT(20) NOT NULL,
21 `user_role_id` BIGINT(20) NOT NULL,
22 PRIMARY KEY (`user_id`, `user_role_id`),
23 KEY `FK55itppkw3i07do3h7qoclqd4k` (`user_id`),
24 KEY `FKowf3vvo8ufljmarbdf820di0t` (`user_role_id`)
25)
26 ENGINE = InnoDB
27 DEFAULT CHARSET = utf8
28 COLLATE = utf8_bin;
29
30CREATE TABLE IF NOT EXISTS `password_reset_token` (
31 `id` BIGINT(20) NOT NULL,
32 `expiry_date` DATETIME DEFAULT NULL,
33 `token` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
34 `user_id` BIGINT(20) NOT NULL,
35 PRIMARY KEY (`id`),
36 KEY `FK5lwtbncug84d4ero33v3cfxvl` (`user_id`)
37)
38 ENGINE = InnoDB
39 DEFAULT CHARSET = utf8
40 COLLATE = utf8_bin;
41
42CREATE TABLE IF NOT EXISTS `verification_token` (
43 `id` BIGINT(20) NOT NULL,
44 `expiry_date` DATETIME DEFAULT NULL,
45 `token` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
46 `user_id` BIGINT(20) NOT NULL,
47 PRIMARY KEY (`id`),
48 KEY `FKrdn0mss276m9jdobfhhn2qogw` (`user_id`)
49)
50 ENGINE = InnoDB
51 DEFAULT CHARSET = utf8
52 COLLATE = utf8_bin;
53
54CREATE TABLE IF NOT EXISTS `game` (
55 `id_game` BIGINT(20) NOT NULL,
56 `begin` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
57 `description` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
58 `end` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
59 `name` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
60 `geotag_id` BIGINT(20) DEFAULT NULL,
61 PRIMARY KEY (`id_game`),
62 KEY `FK9hq633ujk8jwbu06jmcrtn48m` (`geotag_id`)
63)
64 ENGINE = InnoDB
65 AUTO_INCREMENT = 3
66 DEFAULT CHARSET = utf8
67 COLLATE = utf8_bin;
68
69CREATE TABLE IF NOT EXISTS `game_users` (
70 `game_id` BIGINT(20) NOT NULL,
71 `user_id` BIGINT(20) NOT NULL,
72 PRIMARY KEY (`game_id`, `user_id`),
73 KEY `FKjtkfux0qusi8dp0v8mgdjc3wb` (`user_id`),
74 KEY `FKj7ccle864h4fb9f1e1km6d68o` (`game_id`)
75)
76 ENGINE = InnoDB
77 DEFAULT CHARSET = utf8
78 COLLATE = utf8_bin;
79
80CREATE TABLE IF NOT EXISTS `geotag` (
81 `id_geotag` BIGINT(20) NOT NULL,
82 `description` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
83 `latitude` DOUBLE DEFAULT NULL,
84 `longitude` DOUBLE DEFAULT NULL,
85 `name` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
86 PRIMARY KEY (`id_geotag`)
87)
88 ENGINE = InnoDB
89 AUTO_INCREMENT = 3
90 DEFAULT CHARSET = utf8
91 COLLATE = utf8_bin;
92
93-- AUTHORIZATION
94
95CREATE TABLE IF NOT EXISTS `oauth_client_details` (
96 `client_id` VARCHAR(255) COLLATE utf8_bin NOT NULL,
97 `resource_ids` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
98 `client_secret` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
99 `scope` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
100 `authorized_grant_types` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
101 `web_server_redirect_uri` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
102 `authorities` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
103 `access_token_validity` INT(11) DEFAULT NULL,
104 `refresh_token_validity` INT(11) DEFAULT NULL,
105 `additional_information` VARCHAR(4096) COLLATE utf8_bin DEFAULT NULL,
106 `autoapprove` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
107 PRIMARY KEY (`client_id`)
108)
109 ENGINE = InnoDB
110 DEFAULT CHARSET = utf8
111 COLLATE = utf8_bin;
112
113
114CREATE TABLE IF NOT EXISTS `oauth_client_token` (
115 `token_id` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
116 `token` MEDIUMBLOB,
117 `authentication_id` VARCHAR(255) COLLATE utf8_bin NOT NULL,
118 `user_name` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
119 `client_id` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
120 PRIMARY KEY (`authentication_id`)
121)
122 ENGINE = InnoDB
123 DEFAULT CHARSET = utf8
124 COLLATE = utf8_bin;
125
126
127CREATE TABLE IF NOT EXISTS `oauth_access_token` (
128 `token_id` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
129 `token` MEDIUMBLOB,
130 `authentication_id` VARCHAR(255) COLLATE utf8_bin NOT NULL,
131 `user_name` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
132 `client_id` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
133 `authentication` MEDIUMBLOB,
134 `refresh_token` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
135 PRIMARY KEY (`authentication_id`)
136)
137 ENGINE = InnoDB
138 DEFAULT CHARSET = utf8
139 COLLATE = utf8_bin;
140
141
142CREATE TABLE IF NOT EXISTS `oauth_refresh_token` (
143 `token_id` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
144 `token` MEDIUMBLOB,
145 `authentication` MEDIUMBLOB
146)
147 ENGINE = InnoDB
148 DEFAULT CHARSET = utf8
149 COLLATE = utf8_bin;
150
151
152CREATE TABLE IF NOT EXISTS `oauth_code` (
153 `code` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
154 `authentication` MEDIUMBLOB
155)
156 ENGINE = InnoDB
157 DEFAULT CHARSET = utf8
158 COLLATE = utf8_bin;
159
160
161/* https://stackoverflow.com/questions/36882149/error-1067-42000-invalid-default-value-for-created-at */
162SET sql_mode = '';
163CREATE TABLE IF NOT EXISTS `oauth_approvals` (
164 `userId` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
165 `clientId` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
166 `scope` VARCHAR(255) COLLATE utf8_bin DEFAULT NULL,
167 `status` VARCHAR(10) COLLATE utf8_bin DEFAULT NULL,
168 `expiresAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
169 `lastModifiedAt` TIMESTAMP NOT NULL DEFAULT '0000-00-00 00:00:00'
170)
171 ENGINE = InnoDB
172 DEFAULT CHARSET = utf8
173 COLLATE = utf8_bin;
174
175
176ALTER TABLE `game`
177 MODIFY `id_game` BIGINT(20) NOT NULL AUTO_INCREMENT,
178 AUTO_INCREMENT = 3;
179
180ALTER TABLE `geotag`
181 MODIFY `id_geotag` BIGINT(20) NOT NULL AUTO_INCREMENT,
182 AUTO_INCREMENT = 3;
183
184ALTER TABLE `password_reset_token`
185 MODIFY `id` BIGINT(20) NOT NULL AUTO_INCREMENT;
186
187ALTER TABLE `user`
188 MODIFY `id_user` BIGINT(20) NOT NULL AUTO_INCREMENT,
189 AUTO_INCREMENT = 4;
190
191ALTER TABLE `user_role`
192 MODIFY `id_user_role` BIGINT(20) NOT NULL AUTO_INCREMENT,
193 AUTO_INCREMENT = 3;
194
195ALTER TABLE `verification_token`
196 MODIFY `id` BIGINT(20) NOT NULL AUTO_INCREMENT;
197
198-- CALL PROC_DROP_FOREIGN_KEY('game', 'FK9hq633ujk8jwbu06jmcrtn48m');
199
200ALTER TABLE `game`
201 ADD CONSTRAINT `FK9hq633ujk8jwbu06jmcrtn48m` FOREIGN KEY (`geotag_id`) REFERENCES `geotag` (`id_geotag`);
202
203-- CALL PROC_DROP_FOREIGN_KEY('game_users', 'FKj7ccle864h4fb9f1e1km6d68o');
204-- CALL PROC_DROP_FOREIGN_KEY('game_users', 'FKjtkfux0qusi8dp0v8mgdjc3wb');
205
206ALTER TABLE `game_users`
207 ADD CONSTRAINT `FKj7ccle864h4fb9f1e1km6d68o` FOREIGN KEY (`game_id`) REFERENCES `game` (`id_game`),
208 ADD CONSTRAINT `FKjtkfux0qusi8dp0v8mgdjc3wb` FOREIGN KEY (`user_id`) REFERENCES `user` (`id_user`);
209
210-- CALL PROC_DROP_FOREIGN_KEY('password_reset_token', 'FK5lwtbncug84d4ero33v3cfxvl');
211
212ALTER TABLE `password_reset_token`
213 ADD CONSTRAINT `FK5lwtbncug84d4ero33v3cfxvl` FOREIGN KEY (`user_id`) REFERENCES `user` (`id_user`);
214
215-- CALL PROC_DROP_FOREIGN_KEY('user_roles', 'FK55itppkw3i07do3h7qoclqd4k');
216-- CALL PROC_DROP_FOREIGN_KEY('user_roles', 'FKowf3vvo8ufljmarbdf820di0t');
217
218ALTER TABLE `user_roles`
219 ADD CONSTRAINT `FK55itppkw3i07do3h7qoclqd4k` FOREIGN KEY (`user_id`) REFERENCES `user` (`id_user`),
220 ADD CONSTRAINT `FKowf3vvo8ufljmarbdf820di0t` FOREIGN KEY (`user_role_id`) REFERENCES `user_role` (`id_user_role`);
221
222-- CALL PROC_DROP_FOREIGN_KEY('verification_token', 'FKrdn0mss276m9jdobfhhn2qogw');
223
224ALTER TABLE `verification_token`
225 ADD CONSTRAINT `FKrdn0mss276m9jdobfhhn2qogw` FOREIGN KEY (`user_id`) REFERENCES `user` (`id_user`);