· 9 years ago · May 01, 2017, 10:26 PM
1-- MySQL Administrator dump 1.4
2--
3-- ------------------------------------------------------
4-- Server version 5.1.32-community-log
5
6
7/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
8/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
9/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
10/*!40101 SET NAMES utf8 */;
11
12/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
13/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
14/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
15
16
17--
18-- Create schema water_uwi
19--
20
21CREATE DATABASE IF NOT EXISTS water_uwi;
22USE water_uwi;
23
24--
25-- Temporary table structure for view `calctime`
26--
27DROP TABLE IF EXISTS `calctime`;
28DROP VIEW IF EXISTS `calctime`;
29CREATE TABLE `calctime` (
30 `delivery_id` int(11),
31 `LAST_DELIVERY` time,
32 `delivery_date` timestamp,
33 `description` varchar(100)
34);
35
36--
37-- Temporary table structure for view `last_call_time`
38--
39DROP TABLE IF EXISTS `last_call_time`;
40DROP VIEW IF EXISTS `last_call_time`;
41CREATE TABLE `last_call_time` (
42 `call_id` int(11),
43 `description` varchar(100),
44 `call_date` datetime,
45 `notes` varchar(500),
46 `status` varchar(45)
47);
48
49--
50-- Temporary table structure for view `last_delivery`
51--
52DROP TABLE IF EXISTS `last_delivery`;
53DROP VIEW IF EXISTS `last_delivery`;
54CREATE TABLE `last_delivery` (
55 `delivery_id` int(11),
56 `delivery_date` timestamp,
57 `driver_name` varchar(45),
58 `capacity` int(11),
59 `capacity_measure` varchar(45),
60 `description` varchar(100)
61);
62
63--
64-- Temporary table structure for view `tank_cap`
65--
66DROP TABLE IF EXISTS `tank_cap`;
67DROP VIEW IF EXISTS `tank_cap`;
68CREATE TABLE `tank_cap` (
69 `tank_id` varchar(10),
70 `capacity` int(11),
71 `capacity_measure` varchar(45),
72 `description` varchar(100)
73);
74
75--
76-- Temporary table structure for view `tank_list`
77--
78DROP TABLE IF EXISTS `tank_list`;
79DROP VIEW IF EXISTS `tank_list`;
80CREATE TABLE `tank_list` (
81 `location_code` varchar(45),
82 `description` varchar(100),
83 `capacity` int(11),
84 `capacity_measure` varchar(45)
85);
86
87--
88-- Definition of table `cap_measure`
89--
90
91DROP TABLE IF EXISTS `cap_measure`;
92CREATE TABLE `cap_measure` (
93 `meter_id` int(11) NOT NULL AUTO_INCREMENT,
94 `cap_measure` varchar(45) NOT NULL,
95 PRIMARY KEY (`meter_id`)
96) ENGINE=MyISAM AUTO_INCREMENT=2324 DEFAULT CHARSET=latin1;
97
98--
99-- Dumping data for table `cap_measure`
100--
101
102/*!40000 ALTER TABLE `cap_measure` DISABLE KEYS */;
103/*!40000 ALTER TABLE `cap_measure` ENABLE KEYS */;
104
105
106--
107-- Definition of table `delivery`
108--
109
110DROP TABLE IF EXISTS `delivery`;
111CREATE TABLE `delivery` (
112 `delivery_id` int(11) NOT NULL AUTO_INCREMENT,
113 `delivery_date` timestamp NULL DEFAULT NULL,
114 `truck_id` varchar(6) NOT NULL,
115 `location_id` int(11) NOT NULL,
116 `tank_id` varchar(10) NOT NULL,
117 PRIMARY KEY (`delivery_id`),
118 KEY `idtruck` (`truck_id`),
119 KEY `dev_loc` (`location_id`),
120 KEY `tank_d` (`tank_id`),
121 CONSTRAINT `dev_loc` FOREIGN KEY (`location_id`) REFERENCES `tank_location` (`tank_location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
122 CONSTRAINT `idtruck` FOREIGN KEY (`truck_id`) REFERENCES `truck` (`truck_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
123 CONSTRAINT `tank_d` FOREIGN KEY (`tank_id`) REFERENCES `tank` (`tank_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
124) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;
125
126--
127-- Dumping data for table `delivery`
128--
129
130/*!40000 ALTER TABLE `delivery` DISABLE KEYS */;
131INSERT INTO `delivery` (`delivery_id`,`delivery_date`,`truck_id`,`location_id`,`tank_id`) VALUES
132 (1,'2010-02-05 03:04:44','TR123',2325,'43434'),
133 (2,'2010-02-04 03:03:00','ST453',2324,'43434'),
134 (4,'2010-02-06 07:05:22','ST453',2325,'43434');
135/*!40000 ALTER TABLE `delivery` ENABLE KEYS */;
136
137
138--
139-- Definition of table `location`
140--
141
142DROP TABLE IF EXISTS `location`;
143CREATE TABLE `location` (
144 `location_id` int(11) NOT NULL AUTO_INCREMENT,
145 `location_code` varchar(45) NOT NULL,
146 PRIMARY KEY (`location_id`)
147) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
148
149--
150-- Dumping data for table `location`
151--
152
153/*!40000 ALTER TABLE `location` DISABLE KEYS */;
154INSERT INTO `location` (`location_id`,`location_code`) VALUES
155 (1,'Mary Secole'),
156 (2,'Taylor'),
157 (3,'Rex'),
158 (4,'Preston'),
159 (5,'Chanc');
160/*!40000 ALTER TABLE `location` ENABLE KEYS */;
161
162
163--
164-- Definition of table `phone_call`
165--
166
167DROP TABLE IF EXISTS `phone_call`;
168CREATE TABLE `phone_call` (
169 `call_id` int(11) NOT NULL AUTO_INCREMENT,
170 `location_id` int(11) NOT NULL,
171 `call_date` datetime NOT NULL,
172 `status` int(11) DEFAULT NULL,
173 `notes` varchar(500) DEFAULT NULL,
174 PRIMARY KEY (`call_id`),
175 KEY `call_loc_id` (`location_id`),
176 KEY `stat` (`status`),
177 CONSTRAINT `call_loc_id` FOREIGN KEY (`location_id`) REFERENCES `tank_location` (`tank_location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
178 CONSTRAINT `stat` FOREIGN KEY (`status`) REFERENCES `status` (`status_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
179) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
180
181--
182-- Dumping data for table `phone_call`
183--
184
185/*!40000 ALTER TABLE `phone_call` DISABLE KEYS */;
186INSERT INTO `phone_call` (`call_id`,`location_id`,`call_date`,`status`,`notes`) VALUES
187 (1,2324,'2010-02-05 02:41:36',1,NULL),
188 (12,2325,'2010-02-12 03:08:45',2,NULL);
189/*!40000 ALTER TABLE `phone_call` ENABLE KEYS */;
190
191
192--
193-- Definition of table `status`
194--
195
196DROP TABLE IF EXISTS `status`;
197CREATE TABLE `status` (
198 `status_id` int(11) NOT NULL AUTO_INCREMENT,
199 `description` varchar(45) NOT NULL,
200 PRIMARY KEY (`status_id`)
201) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
202
203--
204-- Dumping data for table `status`
205--
206
207/*!40000 ALTER TABLE `status` DISABLE KEYS */;
208INSERT INTO `status` (`status_id`,`description`) VALUES
209 (1,'empty'),
210 (2,'full');
211/*!40000 ALTER TABLE `status` ENABLE KEYS */;
212
213
214--
215-- Definition of table `tank`
216--
217
218DROP TABLE IF EXISTS `tank`;
219CREATE TABLE `tank` (
220 `tank_id` varchar(10) NOT NULL,
221 `location_id` int(11) NOT NULL,
222 `capacity` int(11) NOT NULL,
223 `capacity_measure` varchar(45) NOT NULL,
224 PRIMARY KEY (`tank_id`),
225 KEY `tank_loc_id` (`location_id`),
226 CONSTRAINT `tank_loc_id` FOREIGN KEY (`location_id`) REFERENCES `tank_location` (`tank_location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
227) ENGINE=InnoDB DEFAULT CHARSET=latin1;
228
229--
230-- Dumping data for table `tank`
231--
232
233/*!40000 ALTER TABLE `tank` DISABLE KEYS */;
234INSERT INTO `tank` (`tank_id`,`location_id`,`capacity`,`capacity_measure`) VALUES
235 ('23223',2325,23232,'M'),
236 ('43434',2324,232323,'L');
237/*!40000 ALTER TABLE `tank` ENABLE KEYS */;
238
239
240--
241-- Definition of table `tank_location`
242--
243
244DROP TABLE IF EXISTS `tank_location`;
245CREATE TABLE `tank_location` (
246 `tank_location_id` int(11) NOT NULL AUTO_INCREMENT,
247 `location_id` int(11) NOT NULL,
248 `description` varchar(100) NOT NULL,
249 PRIMARY KEY (`tank_location_id`),
250 KEY `tank_loc` (`location_id`),
251 CONSTRAINT `tank_loc` FOREIGN KEY (`location_id`) REFERENCES `location` (`location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
252) ENGINE=InnoDB AUTO_INCREMENT=2326 DEFAULT CHARSET=latin1;
253
254--
255-- Dumping data for table `tank_location`
256--
257
258/*!40000 ALTER TABLE `tank_location` DISABLE KEYS */;
259INSERT INTO `tank_location` (`tank_location_id`,`location_id`,`description`) VALUES
260 (1,1,'2 Floor'),
261 (2324,1,'Front Gate'),
262 (2325,2,'Roosters');
263/*!40000 ALTER TABLE `tank_location` ENABLE KEYS */;
264
265
266--
267-- Definition of table `truck`
268--
269
270DROP TABLE IF EXISTS `truck`;
271CREATE TABLE `truck` (
272 `truck_id` varchar(6) NOT NULL,
273 `driver_name` varchar(45) DEFAULT NULL,
274 `capacity` int(11) DEFAULT NULL,
275 `capacity_measure` varchar(45) DEFAULT NULL,
276 PRIMARY KEY (`truck_id`)
277) ENGINE=InnoDB DEFAULT CHARSET=latin1;
278
279--
280-- Dumping data for table `truck`
281--
282
283/*!40000 ALTER TABLE `truck` DISABLE KEYS */;
284INSERT INTO `truck` (`truck_id`,`driver_name`,`capacity`,`capacity_measure`) VALUES
285 ('FT322','jim brown',22221,'M'),
286 ('ST453','andre pink',22556,'M'),
287 ('TR123','john brown',232323,'CM'),
288 ('TR234','joe black',22222,'G'),
289 ('WQ232','daine k',3434322,'L');
290/*!40000 ALTER TABLE `truck` ENABLE KEYS */;
291
292
293--
294-- Definition of table `user`
295--
296
297DROP TABLE IF EXISTS `user`;
298CREATE TABLE `user` (
299 `user_id` int(11) NOT NULL AUTO_INCREMENT,
300 `username` varchar(45) NOT NULL,
301 `user_pass` varchar(45) NOT NULL,
302 `user_type` int(11) NOT NULL,
303 PRIMARY KEY (`user_id`),
304 KEY `u_type` (`user_type`),
305 CONSTRAINT `u_type` FOREIGN KEY (`user_type`) REFERENCES `user_type` (`usertype_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
306) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
307
308--
309-- Dumping data for table `user`
310--
311
312/*!40000 ALTER TABLE `user` DISABLE KEYS */;
313INSERT INTO `user` (`user_id`,`username`,`user_pass`,`user_type`) VALUES
314 (1,'admin','password',1),
315 (2,'test','test',2);
316/*!40000 ALTER TABLE `user` ENABLE KEYS */;
317
318
319--
320-- Definition of table `user_type`
321--
322
323DROP TABLE IF EXISTS `user_type`;
324CREATE TABLE `user_type` (
325 `usertype_id` int(11) NOT NULL,
326 `description` varchar(45) NOT NULL,
327 PRIMARY KEY (`usertype_id`)
328) ENGINE=InnoDB DEFAULT CHARSET=latin1;
329
330--
331-- Dumping data for table `user_type`
332--
333
334/*!40000 ALTER TABLE `user_type` DISABLE KEYS */;
335INSERT INTO `user_type` (`usertype_id`,`description`) VALUES
336 (1,'admin'),
337 (2,'normal');
338/*!40000 ALTER TABLE `user_type` ENABLE KEYS */;
339
340
341--
342-- Definition of procedure `get_pass`
343--
344
345DROP PROCEDURE IF EXISTS `get_pass`;
346
347DELIMITER $$
348
349/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
350CREATE DEFINER=`root`@`localhost` PROCEDURE `get_pass`(user_nm VARCHAR(50))
351BEGIN
352 SELECT user_pass FROM user WHERE username = user_um ;
353END $$
354/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
355
356DELIMITER ;
357
358--
359-- Definition of procedure `insert_delivery`
360--
361
362DROP PROCEDURE IF EXISTS `insert_delivery`;
363
364DELIMITER $$
365
366/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
367CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_delivery`(delivery_dt DATETIME, truck_num VARCHAR(6),loc_id INT,tank_num VARCHAR(6))
368BEGIN
369 INSERT INTO delivery (delivery_date,truck_id,location_id,tank_id) VALUES (delivery_dt,truck_num,loc_id,tank_num);
370END $$
371/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
372
373DELIMITER ;
374
375--
376-- Definition of procedure `insert_location`
377--
378
379DROP PROCEDURE IF EXISTS `insert_location`;
380
381DELIMITER $$
382
383/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
384CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_location`(loc_code VARCHAR(50))
385BEGIN
386 INSERT INTO location (location_code) VALUES (loc_code);
387END $$
388/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
389
390DELIMITER ;
391
392--
393-- Definition of procedure `insert_phone_call`
394--
395
396DROP PROCEDURE IF EXISTS `insert_phone_call`;
397
398DELIMITER $$
399
400/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
401CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_phone_call`(loc_id INT,call_dt DATETIME, stat INT, nt VARCHAR(500))
402BEGIN
403 INSERT INTO phone_call(location_id,call_date,status,notes) VALUES (loc_id,call_dt,stat,nt);
404END $$
405/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
406
407DELIMITER ;
408
409--
410-- Definition of procedure `insert_tank`
411--
412
413DROP PROCEDURE IF EXISTS `insert_tank`;
414
415DELIMITER $$
416
417/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
418CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_tank`(tank_num VARCHAR(10), loc_id INT, cap INT, cap_meter VARCHAR(45))
419BEGIN
420 INSERT INTO tank (tank_id,location_id,capacity,capacity_measure) VALUES (tank_num,loc_id,cap,cap_meter);
421END $$
422/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
423
424DELIMITER ;
425
426--
427-- Definition of procedure `insert_tank_location`
428--
429
430DROP PROCEDURE IF EXISTS `insert_tank_location`;
431
432DELIMITER $$
433
434/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
435CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_tank_location`(loc_id INT, descript VARCHAR(100))
436BEGIN
437 INSERT INTO tank_location (location_id,description) VALUES (loc_id,descript);
438END $$
439/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
440
441DELIMITER ;
442
443--
444-- Definition of procedure `insert_truck`
445--
446
447DROP PROCEDURE IF EXISTS `insert_truck`;
448
449DELIMITER $$
450
451/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
452CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_truck`(truck_license VARCHAR(6), driver_nm VARCHAR(50), cap INT, cap_meter VARCHAR(45))
453BEGIN
454 INSERT INTO truck (truck_id,driver_name,capacity,capacity_measure) VALUES (tank_license,driver_nm,cap,cap_meter);
455END $$
456/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
457
458DELIMITER ;
459
460--
461-- Definition of procedure `insert_user`
462--
463
464DROP PROCEDURE IF EXISTS `insert_user`;
465
466DELIMITER $$
467
468/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
469CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_user`(user_nm VARCHAR(50),user_ps VARCHAR(50), user_t INT)
470BEGIN
471 INSERT INTO user(username,user_pass,user_type) VALUES (user_nm,user_ps,user_t);
472END $$
473/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
474
475DELIMITER ;
476
477--
478-- Definition of procedure `query_calltime`
479--
480
481DROP PROCEDURE IF EXISTS `query_calltime`;
482
483DELIMITER $$
484
485/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
486CREATE DEFINER=`root`@`localhost` PROCEDURE `query_calltime`(call_dt DATETIME)
487BEGIN
488 SELECT
489 phone_call.call_id,
490 phone_call.call_date,
491 phone_call.status,
492 phone_call.notes,
493 tank_location.description
494FROM phone_call
495Inner Join tank_location ON tank_location.tank_location_id = phone_call.location_id
496WHERE phone_call.call_date = call_dt;
497END $$
498/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
499
500DELIMITER ;
501
502--
503-- Definition of procedure `query_date`
504--
505
506DROP PROCEDURE IF EXISTS `query_date`;
507
508DELIMITER $$
509
510/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
511CREATE DEFINER=`root`@`localhost` PROCEDURE `query_date`(dt DATETIME)
512BEGIN
513 SELECT
514 delivery.delivery_id,
515 delivery.delivery_date,
516 tank_location.description,
517 truck.driver_name,
518 truck.capacity,
519 truck.capacity_measure,
520 tank.tank_id,
521 tank.capacity,
522 tank.capacity_measure
523FROM
524delivery
525Inner Join truck ON truck.truck_id = delivery.truck_id
526Inner Join tank_location ON tank_location.tank_location_id = delivery.location_id
527Inner Join tank ON tank.tank_id = delivery.tank_id
528WHERE delivery.delivery_date = dt;
529
530END $$
531/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
532
533DELIMITER ;
534
535--
536-- Definition of procedure `query_daterange`
537--
538
539DROP PROCEDURE IF EXISTS `query_daterange`;
540
541DELIMITER $$
542
543/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
544CREATE DEFINER=`root`@`localhost` PROCEDURE `query_daterange`()
545BEGIN
546select timediff(`delivery`.`delivery_date`,current_timestamp()) AS `delivery_date`
547from ((`delivery` join `truck` on((`truck`.`truck_id` = `delivery`.`truck_id`))) join `tank_location` on((`tank_location`.`tank_location_id` = `delivery`.`location_id`)))
548WHERE
549delivery.delivery_date BETWEEN current_timestamp() AND delivery.delivery_date
550order by `delivery`.`delivery_date` DESC LIMIT 1;
551
552END $$
553/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
554
555DELIMITER ;
556
557--
558-- Definition of procedure `query_lastdelivery`
559--
560
561DROP PROCEDURE IF EXISTS `query_lastdelivery`;
562
563DELIMITER $$
564
565/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
566CREATE DEFINER=`root`@`localhost` PROCEDURE `query_lastdelivery`()
567BEGIN
568SELECT
569delivery.delivery_id AS delivery_id,
570delivery.delivery_date AS delivery_date,
571truck.driver_name AS driver_name,
572truck.capacity AS capacity,
573truck.capacity_measure AS capacity_measure,
574tank_location.description
575from ((`delivery` join `truck` on((`truck`.`truck_id` = `delivery`.`truck_id`))) join `tank_location` on((`tank_location`.`tank_location_id` = `delivery`.`location_id`)))
576order by `delivery`.`delivery_date` DESC LIMIT 1;
577
578END $$
579/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
580
581DELIMITER ;
582
583--
584-- Definition of procedure `query_location`
585--
586
587DROP PROCEDURE IF EXISTS `query_location`;
588
589DELIMITER $$
590
591/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
592CREATE DEFINER=`root`@`localhost` PROCEDURE `query_location`()
593BEGIN
594 SELECT * from location;
595END $$
596/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
597
598DELIMITER ;
599
600--
601-- Definition of procedure `query_tank`
602--
603
604DROP PROCEDURE IF EXISTS `query_tank`;
605
606DELIMITER $$
607
608/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
609CREATE DEFINER=`root`@`localhost` PROCEDURE `query_tank`(loc_id INT)
610BEGIN
611 SELECT * from tank where location_id = loc_id;
612END $$
613/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
614
615DELIMITER ;
616
617--
618-- Definition of procedure `validate_user`
619--
620
621DROP PROCEDURE IF EXISTS `validate_user`;
622
623DELIMITER $$
624
625/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
626CREATE DEFINER=`root`@`localhost` PROCEDURE `validate_user`(user_nm VARCHAR(50),user_ps VARCHAR(50), user_t INT)
627BEGIN
628 SELECT user_type FROM user WHERE username = um AND user_pass = user_ps;
629END $$
630/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
631
632DELIMITER ;
633
634--
635-- Definition of view `calctime`
636--
637
638DROP TABLE IF EXISTS `calctime`;
639DROP VIEW IF EXISTS `calctime`;
640CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `calctime` AS select `delivery`.`delivery_id` AS `delivery_id`,timediff(now(),`delivery`.`delivery_date`) AS `LAST_DELIVERY`,`delivery`.`delivery_date` AS `delivery_date`,`tank_location`.`description` AS `description` from (`delivery` join `tank_location` on((`tank_location`.`tank_location_id` = `delivery`.`location_id`)));
641
642--
643-- Definition of view `last_call_time`
644--
645
646DROP TABLE IF EXISTS `last_call_time`;
647DROP VIEW IF EXISTS `last_call_time`;
648CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `last_call_time` AS select `phone_call`.`call_id` AS `call_id`,`tank_location`.`description` AS `description`,`phone_call`.`call_date` AS `call_date`,`phone_call`.`notes` AS `notes`,`status`.`description` AS `status` from ((`phone_call` join `tank_location` on((`tank_location`.`tank_location_id` = `phone_call`.`location_id`))) join `status` on((`status`.`status_id` = `phone_call`.`status`)));
649
650--
651-- Definition of view `last_delivery`
652--
653
654DROP TABLE IF EXISTS `last_delivery`;
655DROP VIEW IF EXISTS `last_delivery`;
656CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `last_delivery` AS select `delivery`.`delivery_id` AS `delivery_id`,`delivery`.`delivery_date` AS `delivery_date`,`truck`.`driver_name` AS `driver_name`,`truck`.`capacity` AS `capacity`,`truck`.`capacity_measure` AS `capacity_measure`,`tank_location`.`description` AS `description` from ((`delivery` join `truck` on((`truck`.`truck_id` = `delivery`.`truck_id`))) join `tank_location` on((`tank_location`.`tank_location_id` = `delivery`.`location_id`)));
657
658--
659-- Definition of view `tank_cap`
660--
661
662DROP TABLE IF EXISTS `tank_cap`;
663DROP VIEW IF EXISTS `tank_cap`;
664CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `tank_cap` AS select `tank`.`tank_id` AS `tank_id`,`tank`.`capacity` AS `capacity`,`tank`.`capacity_measure` AS `capacity_measure`,`tank_location`.`description` AS `description` from (`tank` join `tank_location` on((`tank_location`.`tank_location_id` = `tank`.`location_id`)));
665
666--
667-- Definition of view `tank_list`
668--
669
670DROP TABLE IF EXISTS `tank_list`;
671DROP VIEW IF EXISTS `tank_list`;
672CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `tank_list` AS select `location`.`location_code` AS `location_code`,`tank_location`.`description` AS `description`,`tank`.`capacity` AS `capacity`,`tank`.`capacity_measure` AS `capacity_measure` from ((`tank_location` join `tank` on((`tank_location`.`tank_location_id` = `tank`.`location_id`))) join `location` on((`location`.`location_id` = `tank_location`.`location_id`)));
673
674
675
676/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
677/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
678/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
679/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
680/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
681/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
682/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;