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