· 9 years ago · Dec 07, 2016, 06:10 AM
1DROP DATABASE if exists NotesDB;
2CREATE DATABASE NotesDB;
3
4USE NotesDB;
5
6CREATE TABLE users (
7 username VARCHAR(30) NOT NULL,
8 password VARCHAR(25) NOT NULL,
9 email VARCHAR(50) NOT NULL,
10 firstname VARCHAR(20),
11 lastname VARCHAR(20),
12 phonenumber VARCHAR(15),
13 gender CHAR(1),
14 ResetPasswordUUID VARCHAR(50),
15
16 PRIMARY KEY (username),
17 UNIQUE (email)
18) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;;
19
20DELIMITER $$
21
22CREATE TRIGGER before_insert_check_gender
23 BEFORE INSERT ON users FOR EACH ROW
24 BEGIN
25 IF New.gender NOT IN ('M', 'F', 'U', 'N')
26 THEN
27 signal sqlstate '45000' set message_text = 'Cannot add user: gender must be M, F, U, or N.';
28# CALL 'Cannot add user: gender must be M, F, U, or N.';
29 END IF;
30 END;
31$$
32
33DELIMITER ;
34
35INSERT INTO `users` VALUES
36 ('admin','password','cprg352+admin@gmail.com','Admin','Admin','403-555-1212','U',NULL),
37 ('abe','password','cprg352+abe@gamil.com','Abe','Aberly','403-555-3434','M',NULL),
38 ('barb','password','cprg352+barb@gmail.com','Barb','Barker','403-555-5656','F',NULL);
39
40CREATE TABLE `notes` (
41 `noteid` int(11) NOT NULL AUTO_INCREMENT,
42 `datecreated` datetime NOT NULL,
43 `contents` varchar(10000) NOT NULL,
44 `owner` varchar(25) NOT NULL,
45 PRIMARY KEY (`noteid`),
46 KEY `note_owner` (`owner`),
47 CONSTRAINT `note_owner` FOREIGN KEY (`owner`) REFERENCES `users` (`username`) ON DELETE CASCADE ON UPDATE CASCADE
48) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;
49
50INSERT INTO `notes` VALUES (1,'2015-10-28 22:52:47','test','abe'), (2,'2015-10-28 22:52:48','test2','abe');
51
52CREATE TABLE `notecollaborators` (
53 `noteid` int(11) NOT NULL,
54 `username` varchar(25) NOT NULL,
55 PRIMARY KEY (`noteid`,`username`),
56 KEY `fk_NoteCollaborator_user1_idx` (`username`),
57 CONSTRAINT `fk_NoteCollaborator_note1` FOREIGN KEY (`noteid`) REFERENCES `notes` (`noteid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
58 CONSTRAINT `fk_NoteCollaborator_user1` FOREIGN KEY (`username`) REFERENCES `users` (`username`) ON DELETE NO ACTION ON UPDATE NO ACTION
59) ENGINE=InnoDB DEFAULT CHARSET=utf8;
60
61INSERT INTO `notecollaborators` VALUES (1, 'barb');
62
63CREATE TABLE `roles` (
64 `roleid` int(11) NOT NULL,
65 `rolename` varchar(25) NOT NULL,
66 PRIMARY KEY (`roleid`)
67) ENGINE=InnoDB DEFAULT CHARSET=utf8;
68
69INSERT INTO `roles` VALUES (1,'admin');
70
71CREATE TABLE `userroles` (
72 `username` varchar(25) NOT NULL,
73 `roleid` int(11) NOT NULL,
74 PRIMARY KEY (`username`,`roleid`),
75 KEY `userrole_role_idx` (`roleid`),
76 CONSTRAINT `userrole_role` FOREIGN KEY (`roleid`) REFERENCES `roles` (`roleid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
77 CONSTRAINT `userrole_user` FOREIGN KEY (`username`) REFERENCES `users` (`username`) ON DELETE NO ACTION ON UPDATE NO ACTION
78) ENGINE=InnoDB DEFAULT CHARSET=utf8;
79
80INSERT INTO `userroles` VALUES ('admin',1);