· 8 years ago · Sep 01, 2018, 03:17 PM
1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
6
7-- -----------------------------------------------------
8-- Schema schema_test
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema schema_test
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `schema_test` DEFAULT CHARACTER SET utf8 ;
15USE `schema_test` ;
16
17-- -----------------------------------------------------
18-- Table `schema_test`.`Users`
19-- -----------------------------------------------------
20CREATE TABLE IF NOT EXISTS `schema_test`.`Users` (
21 `User_Index` INT NOT NULL AUTO_INCREMENT,
22 `User_Username` VARCHAR(64) NOT NULL,
23 `User_Name` VARCHAR(64) NOT NULL,
24 `User_Email` VARCHAR(255) NOT NULL,
25 `User_Password` VARCHAR(64) NOT NULL,
26 `User_Modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
27 `User_Created` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
28 PRIMARY KEY (`User_Index`))
29ENGINE = InnoDB;
30
31
32-- -----------------------------------------------------
33-- Table `schema_test`.`Sects`
34-- -----------------------------------------------------
35CREATE TABLE IF NOT EXISTS `schema_test`.`Sects` (
36 `Sect_Index` INT NOT NULL AUTO_INCREMENT,
37 `Sect_Name` VARCHAR(64) NOT NULL,
38 `Sect_Social_Status` VARCHAR(64) NOT NULL,
39 PRIMARY KEY (`Sect_Index`))
40ENGINE = InnoDB;
41
42
43-- -----------------------------------------------------
44-- Table `schema_test`.`Trades`
45-- -----------------------------------------------------
46CREATE TABLE IF NOT EXISTS `schema_test`.`Trades` (
47 `Trade_Index` INT NOT NULL AUTO_INCREMENT,
48 `Trade_Name` VARCHAR(64) NOT NULL,
49 PRIMARY KEY (`Trade_Index`))
50ENGINE = InnoDB;
51
52
53-- -----------------------------------------------------
54-- Table `schema_test`.`Stats`
55-- -----------------------------------------------------
56CREATE TABLE IF NOT EXISTS `schema_test`.`Stats` (
57 `Stat_Index` INT NOT NULL AUTO_INCREMENT,
58 `Stat_Name` VARCHAR(64) NOT NULL,
59 `Stat_Proficiency` FLOAT NOT NULL,
60 PRIMARY KEY (`Stat_Index`))
61ENGINE = InnoDB;
62
63
64-- -----------------------------------------------------
65-- Table `schema_test`.`Vector3`
66-- -----------------------------------------------------
67CREATE TABLE IF NOT EXISTS `schema_test`.`Vector3` (
68 `Vector3_Index` INT NOT NULL,
69 `Vector3_X` FLOAT NOT NULL,
70 `Vector3_Y` FLOAT NOT NULL,
71 `Vector3_Z` FLOAT NOT NULL,
72 PRIMARY KEY (`Vector3_Index`))
73ENGINE = InnoDB;
74
75
76-- -----------------------------------------------------
77-- Table `schema_test`.`Transforms`
78-- -----------------------------------------------------
79CREATE TABLE IF NOT EXISTS `schema_test`.`Transforms` (
80 `Transform_Index` INT NOT NULL,
81 `Transform_Position` INT NOT NULL,
82 `Transform_Euler_Rotation` INT NOT NULL,
83 `Transform_Scale` INT NOT NULL,
84 PRIMARY KEY (`Transform_Index`, `Transform_Scale`, `Transform_Euler_Rotation`, `Transform_Position`),
85 INDEX `fk_Transform_Position_idx` (`Transform_Position` ASC) ,
86 INDEX `fk_Transform_Euler_Rotation_idx` (`Transform_Euler_Rotation` ASC) ,
87 INDEX `fk_Transform_Scale_idx` (`Transform_Scale` ASC) ,
88 CONSTRAINT `fk_Transform_Position`
89 FOREIGN KEY (`Transform_Position`)
90 REFERENCES `schema_test`.`Vector3` (`Vector3_Index`)
91 ON DELETE CASCADE
92 ON UPDATE CASCADE,
93 CONSTRAINT `fk_Transform_Euler_Rotation`
94 FOREIGN KEY (`Transform_Euler_Rotation`)
95 REFERENCES `schema_test`.`Vector3` (`Vector3_Index`)
96 ON DELETE CASCADE
97 ON UPDATE CASCADE,
98 CONSTRAINT `fk_Transform_Scale`
99 FOREIGN KEY (`Transform_Scale`)
100 REFERENCES `schema_test`.`Vector3` (`Vector3_Index`)
101 ON DELETE CASCADE
102 ON UPDATE CASCADE)
103ENGINE = InnoDB;
104
105
106-- -----------------------------------------------------
107-- Table `schema_test`.`Characters`
108-- -----------------------------------------------------
109CREATE TABLE IF NOT EXISTS `schema_test`.`Characters` (
110 `Character_Index` INT NOT NULL AUTO_INCREMENT,
111 `Character_Stats` INT NOT NULL,
112 `Character_Transform` INT NOT NULL,
113 `Character_Name` VARCHAR(64) NOT NULL,
114 `Character_Sect` INT NOT NULL,
115 `Character_Trade` INT NULL,
116 PRIMARY KEY (`Character_Index`, `Character_Stats`, `Character_Transform`),
117 INDEX `fk_Characters_Sect_idx` (`Character_Sect` ASC) ,
118 INDEX `fk_Characters_Trade_idx` (`Character_Trade` ASC) ,
119 INDEX `fk_Characters_Stats_idx` (`Character_Stats` ASC) ,
120 INDEX `fk_Characters_Transform_idx` (`Character_Transform` ASC) ,
121 CONSTRAINT `fk_Characters_Sect`
122 FOREIGN KEY (`Character_Sect`)
123 REFERENCES `schema_test`.`Sects` (`Sect_Index`)
124 ON DELETE CASCADE
125 ON UPDATE CASCADE,
126 CONSTRAINT `fk_Characters_Trade`
127 FOREIGN KEY (`Character_Trade`)
128 REFERENCES `schema_test`.`Trades` (`Trade_Index`)
129 ON DELETE CASCADE
130 ON UPDATE CASCADE,
131 CONSTRAINT `fk_Characters_Stats`
132 FOREIGN KEY (`Character_Stats`)
133 REFERENCES `schema_test`.`Stats` (`Stat_Index`)
134 ON DELETE CASCADE
135 ON UPDATE CASCADE,
136 CONSTRAINT `fk_Characters_Transform`
137 FOREIGN KEY (`Character_Transform`)
138 REFERENCES `schema_test`.`Transforms` (`Transform_Index`)
139 ON DELETE CASCADE
140 ON UPDATE CASCADE)
141ENGINE = InnoDB;
142
143
144-- -----------------------------------------------------
145-- Table `schema_test`.`Communities`
146-- -----------------------------------------------------
147CREATE TABLE IF NOT EXISTS `schema_test`.`Communities` (
148 `Community_Index` INT NOT NULL AUTO_INCREMENT,
149 `Community_Transform` INT NOT NULL,
150 `Community_Name` VARCHAR(64) NOT NULL,
151 `Community_Predominant_Sect` INT NOT NULL,
152 PRIMARY KEY (`Community_Index`, `Community_Transform`),
153 INDEX `fk_Communities_Predominant_Sect1_idx` (`Community_Predominant_Sect` ASC) ,
154 INDEX `fk_Communities_Transform_idx` (`Community_Transform` ASC) ,
155 CONSTRAINT `fk_Communities_Predominant_Sect1`
156 FOREIGN KEY (`Community_Predominant_Sect`)
157 REFERENCES `schema_test`.`Sects` (`Sect_Index`)
158 ON DELETE CASCADE
159 ON UPDATE CASCADE,
160 CONSTRAINT `fk_Communities_Transform`
161 FOREIGN KEY (`Community_Transform`)
162 REFERENCES `schema_test`.`Transforms` (`Transform_Index`)
163 ON DELETE CASCADE
164 ON UPDATE CASCADE)
165ENGINE = InnoDB;
166
167
168-- -----------------------------------------------------
169-- Table `schema_test`.`Professions`
170-- -----------------------------------------------------
171CREATE TABLE IF NOT EXISTS `schema_test`.`Professions` (
172 `Profession_Index` INT NOT NULL AUTO_INCREMENT,
173 `Professions_Trade` INT NOT NULL,
174 `Profession_Name` VARCHAR(64) NOT NULL,
175 `Profession_Proficiency` FLOAT NOT NULL,
176 PRIMARY KEY (`Profession_Index`, `Professions_Trade`),
177 INDEX `fk_Professions_Trade_idx` (`Professions_Trade` ASC) ,
178 CONSTRAINT `fk_Professions_Trade`
179 FOREIGN KEY (`Professions_Trade`)
180 REFERENCES `schema_test`.`Trades` (`Trade_Index`)
181 ON DELETE CASCADE
182 ON UPDATE CASCADE)
183ENGINE = InnoDB;
184
185
186-- -----------------------------------------------------
187-- Table `schema_test`.`Building_Classifications`
188-- -----------------------------------------------------
189CREATE TABLE IF NOT EXISTS `schema_test`.`Building_Classifications` (
190 `Building_Classifications_Index` INT NOT NULL,
191 `Building_Classifications_Name` VARCHAR(64) NOT NULL,
192 PRIMARY KEY (`Building_Classifications_Index`))
193ENGINE = InnoDB;
194
195
196-- -----------------------------------------------------
197-- Table `schema_test`.`Buildings`
198-- -----------------------------------------------------
199CREATE TABLE IF NOT EXISTS `schema_test`.`Buildings` (
200 `Building_Index` INT NOT NULL AUTO_INCREMENT,
201 `Building_Owner` INT NOT NULL,
202 `Building_Classification` INT NOT NULL,
203 `Building_Transform` INT NOT NULL,
204 `Buildings_Trade` INT NULL,
205 PRIMARY KEY (`Building_Index`, `Building_Owner`, `Building_Classification`, `Building_Transform`),
206 INDEX `fk_Buildings_Trade_idx` (`Buildings_Trade` ASC) ,
207 INDEX `fk_Buildings_Owner_idx` (`Building_Owner` ASC) ,
208 INDEX `fk_Buildings_Building_Classification_idx` (`Building_Classification` ASC) ,
209 INDEX `fk_Buildings_Transform_idx` (`Building_Transform` ASC) ,
210 CONSTRAINT `fk_Buildings_Trade`
211 FOREIGN KEY (`Buildings_Trade`)
212 REFERENCES `schema_test`.`Trades` (`Trade_Index`)
213 ON DELETE CASCADE
214 ON UPDATE CASCADE,
215 CONSTRAINT `fk_Buildings_Owner`
216 FOREIGN KEY (`Building_Owner`)
217 REFERENCES `schema_test`.`Characters` (`Character_Index`)
218 ON DELETE CASCADE
219 ON UPDATE CASCADE,
220 CONSTRAINT `fk_Buildings_Building_Classification`
221 FOREIGN KEY (`Building_Classification`)
222 REFERENCES `schema_test`.`Building_Classifications` (`Building_Classifications_Index`)
223 ON DELETE CASCADE
224 ON UPDATE CASCADE,
225 CONSTRAINT `fk_Buildings_Position`
226 FOREIGN KEY (`Building_Transform`)
227 REFERENCES `schema_test`.`Transforms` (`Transform_Index`)
228 ON DELETE CASCADE
229 ON UPDATE CASCADE)
230ENGINE = InnoDB;
231
232
233-- -----------------------------------------------------
234-- Table `schema_test`.`Trades-Professions`
235-- -----------------------------------------------------
236CREATE TABLE IF NOT EXISTS `schema_test`.`Trades-Professions` (
237 `Trades_Trade_Index` INT NOT NULL,
238 `Professions_Profession_Index` INT NOT NULL,
239 `Professions_Professions_Trade` INT NOT NULL,
240 PRIMARY KEY (`Trades_Trade_Index`, `Professions_Profession_Index`, `Professions_Professions_Trade`),
241 INDEX `fk_Trades-Professions_Professions1_idx` (`Professions_Profession_Index` ASC, `Professions_Professions_Trade` ASC) ,
242 INDEX `fk_Trades-Professions_Trades1_idx` (`Trades_Trade_Index` ASC) ,
243 CONSTRAINT `fk_Trades-Professions_Trades1`
244 FOREIGN KEY (`Trades_Trade_Index`)
245 REFERENCES `schema_test`.`Trades` (`Trade_Index`)
246 ON DELETE CASCADE
247 ON UPDATE CASCADE,
248 CONSTRAINT `fk_Trades-Professions_Professions1`
249 FOREIGN KEY (`Professions_Profession_Index` , `Professions_Professions_Trade`)
250 REFERENCES `schema_test`.`Professions` (`Profession_Index` , `Professions_Trade`)
251 ON DELETE CASCADE
252 ON UPDATE CASCADE)
253ENGINE = InnoDB;
254
255
256-- -----------------------------------------------------
257-- Table `schema_test`.`Characters-Buildings`
258-- -----------------------------------------------------
259CREATE TABLE IF NOT EXISTS `schema_test`.`Characters-Buildings` (
260 `Characters_Character_Index` INT NOT NULL,
261 `Characters_Character_Stats` INT NOT NULL,
262 `Characters_Character_Transform` INT NOT NULL,
263 `Buildings_Building_Index` INT NOT NULL,
264 `Buildings_Building_Owner` INT NOT NULL,
265 `Buildings_Building_Classification` INT NOT NULL,
266 `Buildings_Building_Transform` INT NOT NULL,
267 PRIMARY KEY (`Characters_Character_Index`, `Characters_Character_Stats`, `Characters_Character_Transform`, `Buildings_Building_Index`, `Buildings_Building_Owner`, `Buildings_Building_Classification`, `Buildings_Building_Transform`),
268 INDEX `fk_Characters-Buildings_Buildings1_idx` (`Buildings_Building_Index` ASC, `Buildings_Building_Owner` ASC, `Buildings_Building_Classification` ASC, `Buildings_Building_Transform` ASC) ,
269 INDEX `fk_Characters-Buildings_Characters1_idx` (`Characters_Character_Index` ASC, `Characters_Character_Stats` ASC, `Characters_Character_Transform` ASC) ,
270 CONSTRAINT `fk_Characters-Buildings_Characters1`
271 FOREIGN KEY (`Characters_Character_Index` , `Characters_Character_Stats` , `Characters_Character_Transform`)
272 REFERENCES `schema_test`.`Characters` (`Character_Index` , `Character_Stats` , `Character_Transform`)
273 ON DELETE CASCADE
274 ON UPDATE CASCADE,
275 CONSTRAINT `fk_Characters-Buildings_Buildings1`
276 FOREIGN KEY (`Buildings_Building_Index` , `Buildings_Building_Owner` , `Buildings_Building_Classification` , `Buildings_Building_Transform`)
277 REFERENCES `schema_test`.`Buildings` (`Building_Index` , `Building_Owner` , `Building_Classification` , `Building_Transform`)
278 ON DELETE CASCADE
279 ON UPDATE CASCADE)
280ENGINE = InnoDB;
281
282
283-- -----------------------------------------------------
284-- Table `schema_test`.`Characters-Professions`
285-- -----------------------------------------------------
286CREATE TABLE IF NOT EXISTS `schema_test`.`Characters-Professions` (
287 `Characters_Character_Index` INT NOT NULL,
288 `Characters_Character_Stats` INT NOT NULL,
289 `Characters_Character_Transform` INT NOT NULL,
290 `Professions_Profession_Index` INT NOT NULL,
291 `Professions_Professions_Trade` INT NOT NULL,
292 PRIMARY KEY (`Characters_Character_Index`, `Characters_Character_Stats`, `Characters_Character_Transform`, `Professions_Profession_Index`, `Professions_Professions_Trade`),
293 INDEX `fk_Characters-Professions_Professions1_idx` (`Professions_Profession_Index` ASC, `Professions_Professions_Trade` ASC) ,
294 INDEX `fk_Characters-Professions_Characters1_idx` (`Characters_Character_Index` ASC, `Characters_Character_Stats` ASC, `Characters_Character_Transform` ASC) ,
295 CONSTRAINT `fk_Characters-Professions_Characters1`
296 FOREIGN KEY (`Characters_Character_Index` , `Characters_Character_Stats` , `Characters_Character_Transform`)
297 REFERENCES `schema_test`.`Characters` (`Character_Index` , `Character_Stats` , `Character_Transform`)
298 ON DELETE CASCADE
299 ON UPDATE CASCADE,
300 CONSTRAINT `fk_Characters-Professions_Professions1`
301 FOREIGN KEY (`Professions_Profession_Index` , `Professions_Professions_Trade`)
302 REFERENCES `schema_test`.`Professions` (`Profession_Index` , `Professions_Trade`)
303 ON DELETE CASCADE
304 ON UPDATE CASCADE)
305ENGINE = InnoDB;
306
307
308-- -----------------------------------------------------
309-- Table `schema_test`.`Characters-Communities`
310-- -----------------------------------------------------
311CREATE TABLE IF NOT EXISTS `schema_test`.`Characters-Communities` (
312 `Characters_Character_Index` INT NOT NULL,
313 `Characters_Character_Stats` INT NOT NULL,
314 `Characters_Character_Transform` INT NOT NULL,
315 `Communities_Community_Index` INT NOT NULL,
316 `Communities_Community_Transform` INT NOT NULL,
317 PRIMARY KEY (`Characters_Character_Index`, `Characters_Character_Stats`, `Characters_Character_Transform`, `Communities_Community_Index`, `Communities_Community_Transform`),
318 INDEX `fk_Characters-Communities_Communities1_idx` (`Communities_Community_Index` ASC, `Communities_Community_Transform` ASC) ,
319 INDEX `fk_Characters-Communities_Characters1_idx` (`Characters_Character_Index` ASC, `Characters_Character_Stats` ASC, `Characters_Character_Transform` ASC) ,
320 CONSTRAINT `fk_Characters-Communities_Characters1`
321 FOREIGN KEY (`Characters_Character_Index` , `Characters_Character_Stats` , `Characters_Character_Transform`)
322 REFERENCES `schema_test`.`Characters` (`Character_Index` , `Character_Stats` , `Character_Transform`)
323 ON DELETE CASCADE
324 ON UPDATE CASCADE,
325 CONSTRAINT `fk_Characters-Communities_Communities1`
326 FOREIGN KEY (`Communities_Community_Index` , `Communities_Community_Transform`)
327 REFERENCES `schema_test`.`Communities` (`Community_Index` , `Community_Transform`)
328 ON DELETE CASCADE
329 ON UPDATE CASCADE)
330ENGINE = InnoDB;
331
332
333-- -----------------------------------------------------
334-- Table `schema_test`.`Users-Characters`
335-- -----------------------------------------------------
336CREATE TABLE IF NOT EXISTS `schema_test`.`Users-Characters` (
337 `Users_User_Index` INT NOT NULL,
338 `Characters_Character_Index` INT NOT NULL,
339 `Characters_Character_Stats` INT NOT NULL,
340 `Characters_Character_Transform` INT NOT NULL,
341 PRIMARY KEY (`Users_User_Index`, `Characters_Character_Index`, `Characters_Character_Stats`, `Characters_Character_Transform`),
342 INDEX `fk_Users-Characters_Characters1_idx` (`Characters_Character_Index` ASC, `Characters_Character_Stats` ASC, `Characters_Character_Transform` ASC) ,
343 INDEX `fk_Users-Characters_Users1_idx` (`Users_User_Index` ASC) ,
344 CONSTRAINT `fk_Users-Characters_Users1`
345 FOREIGN KEY (`Users_User_Index`)
346 REFERENCES `schema_test`.`Users` (`User_Index`)
347 ON DELETE CASCADE
348 ON UPDATE CASCADE,
349 CONSTRAINT `fk_Users-Characters_Characters1`
350 FOREIGN KEY (`Characters_Character_Index` , `Characters_Character_Stats` , `Characters_Character_Transform`)
351 REFERENCES `schema_test`.`Characters` (`Character_Index` , `Character_Stats` , `Character_Transform`)
352 ON DELETE CASCADE
353 ON UPDATE CASCADE)
354ENGINE = InnoDB;
355
356
357-- -----------------------------------------------------
358-- Table `schema_test`.`Communities-Buildings`
359-- -----------------------------------------------------
360CREATE TABLE IF NOT EXISTS `schema_test`.`Communities-Buildings` (
361 `Communities_Community_Index` INT NOT NULL,
362 `Communities_Community_Transform` INT NOT NULL,
363 `Buildings_Building_Index` INT NOT NULL,
364 `Buildings_Building_Owner` INT NOT NULL,
365 `Buildings_Building_Classification` INT NOT NULL,
366 `Buildings_Building_Transform` INT NOT NULL,
367 PRIMARY KEY (`Communities_Community_Index`, `Communities_Community_Transform`, `Buildings_Building_Index`, `Buildings_Building_Owner`, `Buildings_Building_Classification`, `Buildings_Building_Transform`),
368 INDEX `fk_Communities-Buildings_Buildings1_idx` (`Buildings_Building_Index` ASC, `Buildings_Building_Owner` ASC, `Buildings_Building_Classification` ASC, `Buildings_Building_Transform` ASC) ,
369 INDEX `fk_Communities-Buildings_Communities1_idx` (`Communities_Community_Index` ASC, `Communities_Community_Transform` ASC) ,
370 CONSTRAINT `fk_Communities-Buildings_Communities1`
371 FOREIGN KEY (`Communities_Community_Index` , `Communities_Community_Transform`)
372 REFERENCES `schema_test`.`Communities` (`Community_Index` , `Community_Transform`)
373 ON DELETE CASCADE
374 ON UPDATE CASCADE,
375 CONSTRAINT `fk_Communities-Buildings_Buildings1`
376 FOREIGN KEY (`Buildings_Building_Index` , `Buildings_Building_Owner` , `Buildings_Building_Classification` , `Buildings_Building_Transform`)
377 REFERENCES `schema_test`.`Buildings` (`Building_Index` , `Building_Owner` , `Building_Classification` , `Building_Transform`)
378 ON DELETE CASCADE
379 ON UPDATE CASCADE)
380ENGINE = InnoDB;
381
382
383-- -----------------------------------------------------
384-- Table `schema_test`.`Communities-Available-Professions`
385-- -----------------------------------------------------
386CREATE TABLE IF NOT EXISTS `schema_test`.`Communities-Available-Professions` (
387 `Communities_Community_Index` INT NOT NULL,
388 `Communities_Community_Transform` INT NOT NULL,
389 `Professions_Profession_Index` INT NOT NULL,
390 `Professions_Professions_Trade` INT NOT NULL,
391 PRIMARY KEY (`Communities_Community_Index`, `Communities_Community_Transform`, `Professions_Profession_Index`, `Professions_Professions_Trade`),
392 INDEX `fk_Communities-Professions_Professions1_idx` (`Professions_Profession_Index` ASC, `Professions_Professions_Trade` ASC) ,
393 INDEX `fk_Communities-Professions_Communities1_idx` (`Communities_Community_Index` ASC, `Communities_Community_Transform` ASC) ,
394 CONSTRAINT `fk_Communities-Professions_Communities1`
395 FOREIGN KEY (`Communities_Community_Index` , `Communities_Community_Transform`)
396 REFERENCES `schema_test`.`Communities` (`Community_Index` , `Community_Transform`)
397 ON DELETE CASCADE
398 ON UPDATE CASCADE,
399 CONSTRAINT `fk_Communities-Professions_Professions1`
400 FOREIGN KEY (`Professions_Profession_Index` , `Professions_Professions_Trade`)
401 REFERENCES `schema_test`.`Professions` (`Profession_Index` , `Professions_Trade`)
402 ON DELETE CASCADE
403 ON UPDATE CASCADE)
404ENGINE = InnoDB;
405
406USE `schema_test` ;
407
408-- -----------------------------------------------------
409-- procedure Login
410-- -----------------------------------------------------
411
412DELIMITER $$
413USE `schema_test`$$
414CREATE DEFINER=`root`@`localhost` PROCEDURE `Login`(IN `inUsername` VARCHAR(64), IN `inPassword` VARCHAR(64))
415 READS SQL DATA
416BEGIN
417 IF NOT EXISTS (SELECT * FROM users WHERE User_Username=inUsername AND User_Password=inPassword) THEN
418 SIGNAL SQLSTATE '45000'
419 SET MESSAGE_TEXT = 'Login Failed';
420 END IF;
421END$$
422
423DELIMITER ;
424
425-- -----------------------------------------------------
426-- procedure Register
427-- -----------------------------------------------------
428
429DELIMITER $$
430USE `schema_test`$$
431CREATE DEFINER=`root`@`localhost` PROCEDURE `Register`(IN inName VARCHAR(64), IN inUsername VARCHAR(64), IN inEmail VARCHAR(255), IN inPassword VARCHAR(64))
432BEGIN
433 IF NOT EXISTS (SELECT User_Username FROM users WHERE User_Username=inUsername) THEN
434 INSERT INTO users (User_Name, User_Username, User_Email, User_Password) VALUES (inName, inUsername, inEmail, inPassword);
435 ELSE
436 SIGNAL SQLSTATE '45000'
437 SET MESSAGE_TEXT = 'Username is Already Taken';
438 END IF;
439END$$
440
441DELIMITER ;
442
443GRANT ALL ON `schema_test`.* TO 'account_test';
444GRANT SELECT ON TABLE `schema_test`.* TO 'account_test';
445GRANT SELECT, INSERT, TRIGGER ON TABLE `schema_test`.* TO 'account_test';
446GRANT SELECT, INSERT, TRIGGER, UPDATE, DELETE ON TABLE `schema_test`.* TO 'account_test';
447GRANT ALTER ROUTINE, EXECUTE ON PROCEDURE `schema_test`.`Login` TO 'account_test'@'%' WITH GRANT OPTION;
448GRANT ALL ON `schema_test`.* TO 'root';
449GRANT SELECT ON TABLE `schema_test`.* TO 'root';
450GRANT SELECT, INSERT, TRIGGER ON TABLE `schema_test`.* TO 'root';
451GRANT SELECT, INSERT, TRIGGER, UPDATE, DELETE ON TABLE `schema_test`.* TO 'root';
452GRANT ALTER ROUTINE, EXECUTE ON PROCEDURE `schema_test`.`Login` TO 'root'@'%' WITH GRANT OPTION;
453
454SET SQL_MODE=@OLD_SQL_MODE;
455SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
456SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;