· 9 years ago · May 03, 2017, 01:52 PM
1/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
2/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
3/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
4/*!40101 SET NAMES utf8 */;
5
6/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
7/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
8/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
9
10
11--
12-- Create schema water_uwi
13--
14
15CREATE DATABASE IF NOT EXISTS water_uwi;
16USE water_uwi;
17
18--
19-- Definition of table `delivery`
20--
21
22DROP TABLE IF EXISTS `delivery`;
23CREATE TABLE `delivery` (
24 `delivery_id` int(11) NOT NULL AUTO_INCREMENT,
25 `delivery_date` date DEFAULT NULL,
26 `delivery_time` time DEFAULT NULL,
27 `truck_id` varchar(6) NOT NULL,
28 `location_id` int(11) NOT NULL,
29 `tank_id` varchar(10) NOT NULL,
30 PRIMARY KEY (`delivery_id`),
31 KEY `idtruck` (`truck_id`),
32 KEY `dev_loc` (`location_id`),
33 KEY `tank_d` (`tank_id`),
34 CONSTRAINT `idtruck` FOREIGN KEY (`truck_id`) REFERENCES `truck` (`truck_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
35 CONSTRAINT `dev_loc` FOREIGN KEY (`location_id`) REFERENCES `tank_location` (`tank_location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
36 CONSTRAINT `tank_d` FOREIGN KEY (`tank_id`) REFERENCES `tank` (`tank_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
37) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
38
39--
40-- Dumping data for table `delivery`
41--
42
43/*!40000 ALTER TABLE `delivery` DISABLE KEYS */;
44INSERT INTO `delivery` (`delivery_id`,`delivery_date`,`delivery_time`,`truck_id`,`location_id`,`tank_id`) VALUES
45 (1,'2010-02-05','03:11:39','TR123',2325,'43434');
46/*!40000 ALTER TABLE `delivery` ENABLE KEYS */;
47
48
49--
50-- Definition of table `location`
51--
52
53DROP TABLE IF EXISTS `location`;
54CREATE TABLE `location` (
55 `location_id` int(11) NOT NULL AUTO_INCREMENT,
56 `location_code` varchar(45) NOT NULL,
57 PRIMARY KEY (`location_id`)
58) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
59
60--
61-- Dumping data for table `location`
62--
63
64/*!40000 ALTER TABLE `location` DISABLE KEYS */;
65INSERT INTO `location` (`location_id`,`location_code`) VALUES
66 (1,'Mary Secole'),
67 (2,'Taylor'),
68 (3,'Rex'),
69 (4,'Preston'),
70 (5,'Chanc');
71/*!40000 ALTER TABLE `location` ENABLE KEYS */;
72
73
74--
75-- Definition of table `phone_call`
76--
77
78DROP TABLE IF EXISTS `phone_call`;
79CREATE TABLE `phone_call` (
80 `call_id` int(11) NOT NULL AUTO_INCREMENT,
81 `location_id` int(11) NOT NULL,
82 `call_date` datetime NOT NULL,
83 `status` int(11) DEFAULT NULL,
84 `notes` varchar(500) DEFAULT NULL,
85 PRIMARY KEY (`call_id`),
86 KEY `call_loc_id` (`location_id`),
87 KEY `stat` (`status`),
88 CONSTRAINT `call_loc_id` FOREIGN KEY (`location_id`) REFERENCES `tank_location` (`tank_location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
89 CONSTRAINT `stat` FOREIGN KEY (`status`) REFERENCES `status` (`status_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
90) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
91
92--
93-- Dumping data for table `phone_call`
94--
95
96/*!40000 ALTER TABLE `phone_call` DISABLE KEYS */;
97INSERT INTO `phone_call` (`call_id`,`location_id`,`call_date`,`status`,`notes`) VALUES
98 (1,2324,'2010-02-05 02:41:36',1,NULL),
99 (12,2325,'2010-02-12 03:08:45',2,NULL);
100/*!40000 ALTER TABLE `phone_call` ENABLE KEYS */;
101
102
103--
104-- Definition of table `status`
105--
106
107DROP TABLE IF EXISTS `status`;
108CREATE TABLE `status` (
109 `status_id` int(11) NOT NULL AUTO_INCREMENT,
110 `description` varchar(45) NOT NULL,
111 PRIMARY KEY (`status_id`)
112) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
113
114--
115-- Dumping data for table `status`
116--
117
118/*!40000 ALTER TABLE `status` DISABLE KEYS */;
119INSERT INTO `status` (`status_id`,`description`) VALUES
120 (1,'empty'),
121 (2,'full');
122/*!40000 ALTER TABLE `status` ENABLE KEYS */;
123
124
125--
126-- Definition of table `tank`
127--
128
129DROP TABLE IF EXISTS `tank`;
130CREATE TABLE `tank` (
131 `tank_id` varchar(10) NOT NULL,
132 `location_id` int(11) NOT NULL,
133 `capacity` int(11) NOT NULL,
134 `capacity_measure` int(11) NOT NULL,
135 PRIMARY KEY (`tank_id`),
136 KEY `tank_loc_id` (`location_id`),
137 CONSTRAINT `tank_loc_id` FOREIGN KEY (`location_id`) REFERENCES `tank_location` (`tank_location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
138) ENGINE=InnoDB DEFAULT CHARSET=latin1;
139
140--
141-- Dumping data for table `tank`
142--
143
144/*!40000 ALTER TABLE `tank` DISABLE KEYS */;
145INSERT INTO `tank` (`tank_id`,`location_id`,`capacity`,`capacity_measure`) VALUES
146 ('',2325,0,0),
147 ('43434',2324,232323,2323);
148/*!40000 ALTER TABLE `tank` ENABLE KEYS */;
149
150
151--
152-- Definition of table `tank_location`
153--
154
155DROP TABLE IF EXISTS `tank_location`;
156CREATE TABLE `tank_location` (
157 `tank_location_id` int(11) NOT NULL AUTO_INCREMENT,
158 `location_id` int(11) NOT NULL,
159 `description` varchar(100) NOT NULL,
160 PRIMARY KEY (`tank_location_id`),
161 KEY `tank_loc` (`location_id`),
162 CONSTRAINT `tank_loc` FOREIGN KEY (`location_id`) REFERENCES `location` (`location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
163) ENGINE=InnoDB AUTO_INCREMENT=2326 DEFAULT CHARSET=latin1;
164
165--
166-- Dumping data for table `tank_location`
167--
168
169/*!40000 ALTER TABLE `tank_location` DISABLE KEYS */;
170INSERT INTO `tank_location` (`tank_location_id`,`location_id`,`description`) VALUES
171 (1,1,'2 Floor'),
172 (2324,1,'Front Gate'),
173 (2325,2,'Roosters');
174/*!40000 ALTER TABLE `tank_location` ENABLE KEYS */;
175
176
177--
178-- Definition of table `truck`
179--
180
181DROP TABLE IF EXISTS `truck`;
182CREATE TABLE `truck` (
183 `truck_id` varchar(6) NOT NULL,
184 `driver_name` varchar(45) DEFAULT NULL,
185 `capacity` int(11) DEFAULT NULL,
186 `capacity_measure` int(11) DEFAULT NULL,
187 PRIMARY KEY (`truck_id`)
188) ENGINE=InnoDB DEFAULT CHARSET=latin1;
189
190--
191-- Dumping data for table `truck`
192--
193
194/*!40000 ALTER TABLE `truck` DISABLE KEYS */;
195INSERT INTO `truck` (`truck_id`,`driver_name`,`capacity`,`capacity_measure`) VALUES
196 ('FT322','jim brown',22221,11223),
197 ('ST453','andre pink',22556,343322),
198 ('TR123','john brown',232323,23231212),
199 ('TR234','joe black',22222,3455555),
200 ('WQ232','daine k',3434322,34523);
201/*!40000 ALTER TABLE `truck` ENABLE KEYS */;
202
203
204--
205-- Definition of table `user`
206--
207
208DROP TABLE IF EXISTS `user`;
209CREATE TABLE `user` (
210 `user_id` int(11) NOT NULL AUTO_INCREMENT,
211 `username` varchar(45) NOT NULL,
212 `user_pass` varchar(45) NOT NULL,
213 `user_type` int(11) NOT NULL,
214 PRIMARY KEY (`user_id`),
215 KEY `u_type` (`user_type`),
216 CONSTRAINT `u_type` FOREIGN KEY (`user_type`) REFERENCES `user_type` (`usertype_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
217) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
218
219--
220-- Dumping data for table `user`
221--
222
223/*!40000 ALTER TABLE `user` DISABLE KEYS */;
224INSERT INTO `user` (`user_id`,`username`,`user_pass`,`user_type`) VALUES
225 (1,'admin','password',1),
226 (2,'test','test',2);
227/*!40000 ALTER TABLE `user` ENABLE KEYS */;
228
229
230--
231-- Definition of table `user_type`
232--
233
234DROP TABLE IF EXISTS `user_type`;
235CREATE TABLE `user_type` (
236 `usertype_id` int(11) NOT NULL,
237 `description` varchar(45) NOT NULL,
238 PRIMARY KEY (`usertype_id`)
239) ENGINE=InnoDB DEFAULT CHARSET=latin1;
240
241--
242-- Dumping data for table `user_type`
243--
244
245/*!40000 ALTER TABLE `user_type` DISABLE KEYS */;
246INSERT INTO `user_type` (`usertype_id`,`description`) VALUES
247 (1,'admin'),
248 (2,'normal');
249/*!40000 ALTER TABLE `user_type` ENABLE KEYS */;
250
251
252--
253-- Definition of procedure `insert_delivery`
254--
255
256DROP PROCEDURE IF EXISTS `insert_delivery`;
257
258DELIMITER $$
259
260/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
261CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_delivery`(delivery_dt DATE, delivery_t TIME, truck_num VARCHAR(6),loc_id INT,tank_num VARCHAR(6))
262BEGIN
263 INSERT INTO delivery (delivery_date,delivery_time,truck_id,location_id,tank_id) VALUES (delivery_dt,delivery_t,truck_num,loc_id,tank_num);
264END $$
265/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
266
267DELIMITER ;
268
269--
270-- Definition of procedure `insert_location`
271--
272
273DROP PROCEDURE IF EXISTS `insert_location`;
274
275DELIMITER $$
276
277/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
278CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_location`(loc_code VARCHAR(50))
279BEGIN
280 INSERT INTO location (location_code) VALUES (loc_code);
281END $$
282/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
283
284DELIMITER ;
285
286--
287-- Definition of procedure `insert_phone_call`
288--
289
290DROP PROCEDURE IF EXISTS `insert_phone_call`;
291
292DELIMITER $$
293
294/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
295CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_phone_call`(loc_id INT,call_dt DATETIME, stat INT, nt VARCHAR(500))
296BEGIN
297 INSERT INTO phone_call(location_id,call_date,status,notes) VALUES (loc_id,call_dt,stat,nt);
298END $$
299/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
300
301DELIMITER ;
302
303--
304-- Definition of procedure `insert_tank`
305--
306
307DROP PROCEDURE IF EXISTS `insert_tank`;
308
309DELIMITER $$
310
311/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
312CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_tank`(tank_num VARCHAR(10), loc_id INT, cap INT, cap_meter INT)
313BEGIN
314 INSERT INTO tank (tank_id,location_id,capacity,capacity_measure) VALUES (tank_num,loc_id,cap,cap_meter);
315END $$
316/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
317
318DELIMITER ;
319
320--
321-- Definition of procedure `insert_tank_location`
322--
323
324DROP PROCEDURE IF EXISTS `insert_tank_location`;
325
326DELIMITER $$
327
328/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
329CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_tank_location`(loc_id INT, descript VARCHAR(100))
330BEGIN
331 INSERT INTO tank_location (location_id,description) VALUES (loc_id,descript);
332END $$
333/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
334
335DELIMITER ;
336
337--
338-- Definition of procedure `insert_truck`
339--
340
341DROP PROCEDURE IF EXISTS `insert_truck`;
342
343DELIMITER $$
344
345/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
346CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_truck`(truck_license VARCHAR(6), driver_nm VARCHAR(50), cap INT, cap_meter INT)
347BEGIN
348 INSERT INTO truck (truck_id,driver_name,capacity,capacity_measure) VALUES (tank_license,driver_nm,cap,cap_meter);
349END $$
350/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
351
352DELIMITER ;
353
354--
355-- Definition of procedure `insert_user`
356--
357
358DROP PROCEDURE IF EXISTS `insert_user`;
359
360DELIMITER $$
361
362/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
363CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_user`(user_nm VARCHAR(50),user_ps VARCHAR(50), user_t INT)
364BEGIN
365 INSERT INTO user(username,user_pass,user_type) VALUES (user_nm,user_ps,user_t);
366END $$
367/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
368
369DELIMITER ;
370
371--
372-- Definition of procedure `validate_user`
373--
374
375DROP PROCEDURE IF EXISTS `validate_user`;
376
377DELIMITER $$
378
379/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
380CREATE DEFINER=`root`@`localhost` PROCEDURE `validate_user`(user_nm VARCHAR(50),user_ps VARCHAR(50), user_t INT)
381BEGIN
382 SELECT user_type FROM user WHERE username = um AND user_pass = user_ps;
383END $$
384/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
385
386DELIMITER ;
387
388
389
390/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
391/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
392/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
393/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
394/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
395/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
396/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;