-
Notifications
You must be signed in to change notification settings - Fork 0
/
database.sql
96 lines (77 loc) · 3.67 KB
/
database.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
/*Removes database if already there and creates fresh copy*/
DROP DATABASE IF EXISTS `arduino-site-users`;
CREATE DATABASE `arduino-site-users`;
USE `arduino-site-users`;
/*Creates the main module table*/
CREATE TABLE IF NOT EXISTS `module` (
`mod_modulegroupcode` varchar(25) NOT NULL,
`mod_modulegroupname` varchar(50) NOT NULL,
`mod_modulecode` varchar(25) NOT NULL,
`mod_modulename` varchar(50) NOT NULL,
`mod_modulegrouporder` int(3) NOT NULL,
`mod_moduleorder` int(3) NOT NULL,
`mod_modulepagename` varchar(255) NOT NULL,
PRIMARY KEY (`mod_modulegroupcode`,`mod_modulecode`),
UNIQUE(`mod_modulecode`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
/*Creates the user roles table*/
CREATE TABLE IF NOT EXISTS `role` (
`role_rolecode` varchar(50) NOT NULL,
`role_rolename` varchar(50) NOT NULL,
PRIMARY KEY (`role_rolecode`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
/*Creates the users permissions table*/
CREATE TABLE IF NOT EXISTS role_rights (
rr_rolecode varchar(50) NOT NULL,
rr_modulecode varchar(25) NOT NULL,
rr_create varchar(25) NOT NULL,
rr_edit varchar(25) NOT NULL,
rr_delete varchar(25) NOT NULL,
rr_view varchar(25) NOT NULL,
FOREIGN KEY (rr_rolecode) REFERENCES role (role_rolecode) ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (rr_modulecode) REFERENCES module (mod_modulecode) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=INNODB DEFAULT CHARSET=utf8;
/*Creates the main users table*/
CREATE TABLE IF NOT EXISTS `system_users` (
`u_userid` int(11) AUTO_INCREMENT NOT NULL,
`u_username` varchar(100) NOT NULL,
`u_password` varchar(255) NOT NULL,
`u_rolecode` varchar(50) NOT NULL,
PRIMARY KEY (`u_userid`),
FOREIGN KEY (`u_rolecode`) REFERENCES `role` (`role_rolecode`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=INNODB DEFAULT CHARSET=utf8;
/*Populating the Roles*/
INSERT INTO `role` (`role_rolecode`, `role_rolename`) VALUES
('ADMIN', 'Administrator'),
('VALVEADMIN', 'Valve Admin'),
('VALVETESTER', 'Valve Tester'),
('GUEST', 'Guest');
/*Creates user accounts for testing*/
INSERT INTO `system_users` (`u_username`, `u_password`, `u_rolecode`) VALUES
('carlwilding', '123456', 'ADMIN'),
('zlatan', 'ibrahimovic', 'VALVEADMIN'),
('romelu', 'lukaku', 'VALVETESTER'),
('guest', 'guest', 'GUEST');
/*Populating the module table with data to organise on the website etc*/
INSERT INTO module (mod_modulegroupcode, mod_modulegroupname, mod_modulecode, mod_modulename, mod_modulegrouporder, mod_moduleorder, mod_modulepagename) VALUES
("VALVES","Valves", "VALVESTATUS","Valve Status", 2, 1,'valve_status.php'),
("VALVES","Valves", "VALVETEST","Valve Testing", 2, 2,'valve_test.php'),
("VALVES","Valves", "TESTRESULTS","Valve Test Results", 2, 3,'test_results.php'),
("OTHER","Other","ABOUT","About", 3, 3,'about.php');
INSERT INTO `role_rights` (`rr_rolecode`, `rr_modulecode`, `rr_create`, `rr_edit`, `rr_delete`, `rr_view`) VALUES
('ADMIN', 'VALVESTATUS', 'yes', 'yes', 'yes', 'yes'),
('ADMIN', 'VALVETEST', 'yes', 'yes', 'yes', 'yes'),
('ADMIN', 'TESTRESULTS', 'yes', 'yes', 'yes', 'yes'),
('ADMIN', 'ABOUT', 'yes', 'yes', 'yes', 'yes'),
('VALVEADMIN', 'VALVESTATUS', 'yes', 'yes', 'yes', 'yes'),
('VALVEADMIN', 'VALVETEST', 'no', 'no', 'no', 'yes'),
('VALVEADMIN', 'TESTRESULTS', 'no', 'no', 'no', 'no'),
('VALVEADMIN', 'ABOUT', 'no', 'no', 'no', 'yes'),
('VALVETESTER', 'VALVESTATUS', 'no', 'no', 'no', 'yes'),
('VALVETESTER', 'VALVETEST', 'yes', 'yes', 'yes', 'yes'),
('VALVETESTER', 'TESTRESULTS', 'no', 'no', 'no', 'yes'),
('VALVETESTER', 'ABOUT', 'no', 'no', 'no', 'yes'),
('GUEST', 'VALVESTATUS', 'no', 'no', 'no', 'yes'),
('GUEST', 'VALVETEST', 'no', 'no', 'no', 'no'),
('GUEST', 'TESTRESULTS', 'no', 'no', 'no', 'no'),
('GUEST', 'ABOUT', 'no', 'no', 'no', 'yes');