· 9 years ago · May 04, 2017, 08:50 AM
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=6 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 (5,'2010-02-05 17:51:50','TR234',2325,'43434');
136/*!40000 ALTER TABLE `delivery` ENABLE KEYS */;
137
138
139--
140-- Definition of table `location`
141--
142
143DROP TABLE IF EXISTS `location`;
144CREATE TABLE `location` (
145 `location_id` int(11) NOT NULL AUTO_INCREMENT,
146 `location_code` varchar(45) NOT NULL,
147 PRIMARY KEY (`location_id`)
148) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
149
150--
151-- Dumping data for table `location`
152--
153
154/*!40000 ALTER TABLE `location` DISABLE KEYS */;
155INSERT INTO `location` (`location_id`,`location_code`) VALUES
156 (1,'Mary Secole'),
157 (2,'Taylor'),
158 (3,'Rex'),
159 (4,'Preston'),
160 (5,'Chanc');
161/*!40000 ALTER TABLE `location` ENABLE KEYS */;
162
163
164--
165-- Definition of table `phone_call`
166--
167
168DROP TABLE IF EXISTS `phone_call`;
169CREATE TABLE `phone_call` (
170 `call_id` int(11) NOT NULL AUTO_INCREMENT,
171 `location_id` int(11) NOT NULL,
172 `call_date` datetime NOT NULL,
173 `status` int(11) DEFAULT NULL,
174 `notes` varchar(500) DEFAULT NULL,
175 PRIMARY KEY (`call_id`),
176 KEY `call_loc_id` (`location_id`),
177 KEY `stat` (`status`),
178 CONSTRAINT `call_loc_id` FOREIGN KEY (`location_id`) REFERENCES `tank_location` (`tank_location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
179 CONSTRAINT `stat` FOREIGN KEY (`status`) REFERENCES `status` (`status_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
180) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
181
182--
183-- Dumping data for table `phone_call`
184--
185
186/*!40000 ALTER TABLE `phone_call` DISABLE KEYS */;
187INSERT INTO `phone_call` (`call_id`,`location_id`,`call_date`,`status`,`notes`) VALUES
188 (1,2324,'2010-02-05 02:41:36',1,NULL),
189 (12,2325,'2010-02-12 03:08:45',2,NULL);
190/*!40000 ALTER TABLE `phone_call` ENABLE KEYS */;
191
192
193--
194-- Definition of table `status`
195--
196
197DROP TABLE IF EXISTS `status`;
198CREATE TABLE `status` (
199 `status_id` int(11) NOT NULL AUTO_INCREMENT,
200 `description` varchar(45) NOT NULL,
201 PRIMARY KEY (`status_id`)
202) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
203
204--
205-- Dumping data for table `status`
206--
207
208/*!40000 ALTER TABLE `status` DISABLE KEYS */;
209INSERT INTO `status` (`status_id`,`description`) VALUES
210 (1,'empty'),
211 (2,'full');
212/*!40000 ALTER TABLE `status` ENABLE KEYS */;
213
214
215--
216-- Definition of table `tank`
217--
218
219DROP TABLE IF EXISTS `tank`;
220CREATE TABLE `tank` (
221 `tank_id` varchar(10) NOT NULL,
222 `location_id` int(11) NOT NULL,
223 `capacity` int(11) NOT NULL,
224 `capacity_measure` varchar(45) NOT NULL,
225 PRIMARY KEY (`tank_id`),
226 KEY `tank_loc_id` (`location_id`),
227 CONSTRAINT `tank_loc_id` FOREIGN KEY (`location_id`) REFERENCES `tank_location` (`tank_location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
228) ENGINE=InnoDB DEFAULT CHARSET=latin1;
229
230--
231-- Dumping data for table `tank`
232--
233
234/*!40000 ALTER TABLE `tank` DISABLE KEYS */;
235INSERT INTO `tank` (`tank_id`,`location_id`,`capacity`,`capacity_measure`) VALUES
236 ('23223',2325,23232,'M'),
237 ('43434',2324,232323,'L');
238/*!40000 ALTER TABLE `tank` ENABLE KEYS */;
239
240
241--
242-- Definition of table `tank_location`
243--
244
245DROP TABLE IF EXISTS `tank_location`;
246CREATE TABLE `tank_location` (
247 `tank_location_id` int(11) NOT NULL AUTO_INCREMENT,
248 `location_id` int(11) NOT NULL,
249 `description` varchar(100) NOT NULL,
250 PRIMARY KEY (`tank_location_id`),
251 KEY `tank_loc` (`location_id`),
252 CONSTRAINT `tank_loc` FOREIGN KEY (`location_id`) REFERENCES `location` (`location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
253) ENGINE=InnoDB AUTO_INCREMENT=2326 DEFAULT CHARSET=latin1;
254
255--
256-- Dumping data for table `tank_location`
257--
258
259/*!40000 ALTER TABLE `tank_location` DISABLE KEYS */;
260INSERT INTO `tank_location` (`tank_location_id`,`location_id`,`description`) VALUES
261 (1,1,'2 Floor'),
262 (2324,1,'Front Gate'),
263 (2325,2,'Roosters');
264/*!40000 ALTER TABLE `tank_location` ENABLE KEYS */;
265
266
267--
268-- Definition of table `truck`
269--
270
271DROP TABLE IF EXISTS `truck`;
272CREATE TABLE `truck` (
273 `truck_id` varchar(6) NOT NULL,
274 `driver_name` varchar(45) DEFAULT NULL,
275 `capacity` int(11) DEFAULT NULL,
276 `capacity_measure` varchar(45) DEFAULT NULL,
277 PRIMARY KEY (`truck_id`)
278) ENGINE=InnoDB DEFAULT CHARSET=latin1;
279
280--
281-- Dumping data for table `truck`
282--
283
284/*!40000 ALTER TABLE `truck` DISABLE KEYS */;
285INSERT INTO `truck` (`truck_id`,`driver_name`,`capacity`,`capacity_measure`) VALUES
286 ('FT322','jim brown',22221,'M'),
287 ('ST453','andre pink',22556,'M'),
288 ('TR123','john brown',232323,'CM'),
289 ('TR234','joe black',22222,'G'),
290 ('WQ232','daine k',3434322,'L');
291/*!40000 ALTER TABLE `truck` ENABLE KEYS */;
292
293
294--
295-- Definition of table `user`
296--
297
298DROP TABLE IF EXISTS `user`;
299CREATE TABLE `user` (
300 `user_id` int(11) NOT NULL AUTO_INCREMENT,
301 `username` varchar(45) NOT NULL,
302 `user_pass` varchar(45) NOT NULL,
303 `user_type` int(11) NOT NULL,
304 PRIMARY KEY (`user_id`),
305 KEY `u_type` (`user_type`),
306 CONSTRAINT `u_type` FOREIGN KEY (`user_type`) REFERENCES `user_type` (`usertype_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
307) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
308
309--
310-- Dumping data for table `user`
311--
312
313/*!40000 ALTER TABLE `user` DISABLE KEYS */;
314INSERT INTO `user` (`user_id`,`username`,`user_pass`,`user_type`) VALUES
315 (1,'admin','password',1),
316 (2,'test','test',2);
317/*!40000 ALTER TABLE `user` ENABLE KEYS */;
318
319
320--
321-- Definition of table `user_type`
322--
323
324DROP TABLE IF EXISTS `user_type`;
325CREATE TABLE `user_type` (
326 `usertype_id` int(11) NOT NULL,
327 `description` varchar(45) NOT NULL,
328 PRIMARY KEY (`usertype_id`)
329) ENGINE=InnoDB DEFAULT CHARSET=latin1;
330
331--
332-- Dumping data for table `user_type`
333--
334
335/*!40000 ALTER TABLE `user_type` DISABLE KEYS */;
336INSERT INTO `user_type` (`usertype_id`,`description`) VALUES
337 (1,'admin'),
338 (2,'normal');
339/*!40000 ALTER TABLE `user_type` ENABLE KEYS */;
340
341
342--
343-- Definition of procedure `get_delivery`
344--
345
346DROP PROCEDURE IF EXISTS `get_delivery`;
347
348DELIMITER $$
349
350/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
351CREATE DEFINER=`root`@`localhost` PROCEDURE `get_delivery`()
352BEGIN
353 SELECT * FROM delivery;
354END $$
355/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
356
357DELIMITER ;
358
359--
360-- Definition of procedure `get_pass`
361--
362
363DROP PROCEDURE IF EXISTS `get_pass`;
364
365DELIMITER $$
366
367/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
368CREATE DEFINER=`root`@`localhost` PROCEDURE `get_pass`(user_nm VARCHAR(50))
369BEGIN
370 SELECT user_pass FROM user WHERE username = user_um ;
371END $$
372/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
373
374DELIMITER ;
375
376--
377-- Definition of procedure `get_tank`
378--
379
380DROP PROCEDURE IF EXISTS `get_tank`;
381
382DELIMITER $$
383
384/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
385CREATE DEFINER=`root`@`localhost` PROCEDURE `get_tank`()
386BEGIN
387 SELECT * from tank;
388END $$
389/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
390
391DELIMITER ;
392
393--
394-- Definition of procedure `insert_delivery`
395--
396
397DROP PROCEDURE IF EXISTS `insert_delivery`;
398
399DELIMITER $$
400
401/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
402CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_delivery`(delivery_dt DATETIME, truck_num VARCHAR(6),loc_id INT,tank_num VARCHAR(6))
403BEGIN
404 INSERT INTO delivery (delivery_date,truck_id,location_id,tank_id) VALUES (delivery_dt,truck_num,loc_id,tank_num);
405END $$
406/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
407
408DELIMITER ;
409
410--
411-- Definition of procedure `insert_location`
412--
413
414DROP PROCEDURE IF EXISTS `insert_location`;
415
416DELIMITER $$
417
418/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
419CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_location`(loc_code VARCHAR(50))
420BEGIN
421 INSERT INTO location (location_code) VALUES (loc_code);
422END $$
423/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
424
425DELIMITER ;
426
427--
428-- Definition of procedure `insert_phone_call`
429--
430
431DROP PROCEDURE IF EXISTS `insert_phone_call`;
432
433DELIMITER $$
434
435/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
436CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_phone_call`(loc_id INT,call_dt DATETIME, stat INT, nt VARCHAR(500))
437BEGIN
438 INSERT INTO phone_call(location_id,call_date,status,notes) VALUES (loc_id,call_dt,stat,nt);
439END $$
440/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
441
442DELIMITER ;
443
444--
445-- Definition of procedure `insert_tank`
446--
447
448DROP PROCEDURE IF EXISTS `insert_tank`;
449
450DELIMITER $$
451
452/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
453CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_tank`(tank_num VARCHAR(10), loc_id INT, cap INT, cap_meter VARCHAR(45))
454BEGIN
455 INSERT INTO tank (tank_id,location_id,capacity,capacity_measure) VALUES (tank_num,loc_id,cap,cap_meter);
456END $$
457/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
458
459DELIMITER ;
460
461--
462-- Definition of procedure `insert_tank_location`
463--
464
465DROP PROCEDURE IF EXISTS `insert_tank_location`;
466
467DELIMITER $$
468
469/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
470CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_tank_location`(loc_id INT, descript VARCHAR(100))
471BEGIN
472 INSERT INTO tank_location (location_id,description) VALUES (loc_id,descript);
473END $$
474/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
475
476DELIMITER ;
477
478--
479-- Definition of procedure `insert_truck`
480--
481
482DROP PROCEDURE IF EXISTS `insert_truck`;
483
484DELIMITER $$
485
486/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
487CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_truck`(truck_license VARCHAR(6), driver_nm VARCHAR(50), cap INT, cap_meter VARCHAR(45))
488BEGIN
489 INSERT INTO truck (truck_id,driver_name,capacity,capacity_measure) VALUES (tank_license,driver_nm,cap,cap_meter);
490END $$
491/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
492
493DELIMITER ;
494
495--
496-- Definition of procedure `insert_user`
497--
498
499DROP PROCEDURE IF EXISTS `insert_user`;
500
501DELIMITER $$
502
503/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
504CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_user`(user_nm VARCHAR(50),user_ps VARCHAR(50), user_t INT)
505BEGIN
506 INSERT INTO user(username,user_pass,user_type) VALUES (user_nm,user_ps,user_t);
507END $$
508/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
509
510DELIMITER ;
511
512--
513-- Definition of procedure `query_calltime`
514--
515
516DROP PROCEDURE IF EXISTS `query_calltime`;
517
518DELIMITER $$
519
520/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
521CREATE DEFINER=`root`@`localhost` PROCEDURE `query_calltime`(call_dt DATETIME)
522BEGIN
523 SELECT
524 phone_call.call_id,
525 phone_call.call_date,
526 phone_call.status,
527 phone_call.notes,
528 tank_location.description
529FROM phone_call
530Inner Join tank_location ON tank_location.tank_location_id = phone_call.location_id
531WHERE phone_call.call_date = call_dt;
532END $$
533/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
534
535DELIMITER ;
536
537--
538-- Definition of procedure `query_date`
539--
540
541DROP PROCEDURE IF EXISTS `query_date`;
542
543DELIMITER $$
544
545/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
546CREATE DEFINER=`root`@`localhost` PROCEDURE `query_date`(dt DATETIME)
547BEGIN
548 SELECT
549 delivery.delivery_id,
550 delivery.delivery_date,
551 tank_location.description,
552 truck.driver_name,
553 truck.capacity,
554 truck.capacity_measure,
555 tank.tank_id,
556 tank.capacity,
557 tank.capacity_measure
558FROM
559delivery
560Inner Join truck ON truck.truck_id = delivery.truck_id
561Inner Join tank_location ON tank_location.tank_location_id = delivery.location_id
562Inner Join tank ON tank.tank_id = delivery.tank_id
563WHERE delivery.delivery_date = dt;
564
565END $$
566/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
567
568DELIMITER ;
569
570--
571-- Definition of procedure `query_daterange`
572--
573
574DROP PROCEDURE IF EXISTS `query_daterange`;
575
576DELIMITER $$
577
578/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
579CREATE DEFINER=`root`@`localhost` PROCEDURE `query_daterange`()
580BEGIN
581select timediff(`delivery`.`delivery_date`,current_timestamp()) AS `delivery_date`
582from ((`delivery` join `truck` on((`truck`.`truck_id` = `delivery`.`truck_id`))) join `tank_location` on((`tank_location`.`tank_location_id` = `delivery`.`location_id`)))
583WHERE
584delivery.delivery_date BETWEEN current_timestamp() AND delivery.delivery_date
585order by `delivery`.`delivery_date` DESC LIMIT 1;
586
587END $$
588/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
589
590DELIMITER ;
591
592--
593-- Definition of procedure `query_lastdelivery`
594--
595
596DROP PROCEDURE IF EXISTS `query_lastdelivery`;
597
598DELIMITER $$
599
600/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
601CREATE DEFINER=`root`@`localhost` PROCEDURE `query_lastdelivery`()
602BEGIN
603SELECT
604delivery.delivery_id AS delivery_id,
605delivery.delivery_date AS delivery_date,
606truck.driver_name AS driver_name,
607truck.capacity AS capacity,
608truck.capacity_measure AS capacity_measure,
609tank_location.description
610from ((`delivery` join `truck` on((`truck`.`truck_id` = `delivery`.`truck_id`))) join `tank_location` on((`tank_location`.`tank_location_id` = `delivery`.`location_id`)))
611order by `delivery`.`delivery_date` DESC LIMIT 1;
612
613END $$
614/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
615
616DELIMITER ;
617
618--
619-- Definition of procedure `query_location`
620--
621
622DROP PROCEDURE IF EXISTS `query_location`;
623
624DELIMITER $$
625
626/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
627CREATE DEFINER=`root`@`localhost` PROCEDURE `query_location`()
628BEGIN
629 SELECT * from location;
630END $$
631/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
632
633DELIMITER ;
634
635--
636-- Definition of procedure `query_tank`
637--
638
639DROP PROCEDURE IF EXISTS `query_tank`;
640
641DELIMITER $$
642
643/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
644CREATE DEFINER=`root`@`localhost` PROCEDURE `query_tank`(loc_id INT)
645BEGIN
646 SELECT * from tank where location_id = loc_id;
647END $$
648/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
649
650DELIMITER ;
651
652--
653-- Definition of procedure `query_truck`
654--
655
656DROP PROCEDURE IF EXISTS `query_truck`;
657
658DELIMITER $$
659
660/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
661CREATE DEFINER=`root`@`localhost` PROCEDURE `query_truck`()
662BEGIN
663 SELECT * FROM truck;
664END $$
665/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
666
667DELIMITER ;
668
669--
670-- Definition of procedure `validate_user`
671--
672
673DROP PROCEDURE IF EXISTS `validate_user`;
674
675DELIMITER $$
676
677/*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
678CREATE DEFINER=`root`@`localhost` PROCEDURE `validate_user`(user_nm VARCHAR(50),user_ps VARCHAR(50), user_t INT)
679BEGIN
680 SELECT user_type FROM user WHERE username = um AND user_pass = user_ps;
681END $$
682/*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
683
684DELIMITER ;
685
686--
687-- Definition of view `calctime`
688--
689
690DROP TABLE IF EXISTS `calctime`;
691DROP VIEW IF EXISTS `calctime`;
692CREATE 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`)));
693
694--
695-- Definition of view `last_call_time`
696--
697
698DROP TABLE IF EXISTS `last_call_time`;
699DROP VIEW IF EXISTS `last_call_time`;
700CREATE 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`)));
701
702--
703-- Definition of view `last_delivery`
704--
705
706DROP TABLE IF EXISTS `last_delivery`;
707DROP VIEW IF EXISTS `last_delivery`;
708CREATE 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`)));
709
710--
711-- Definition of view `tank_cap`
712--
713
714DROP TABLE IF EXISTS `tank_cap`;
715DROP VIEW IF EXISTS `tank_cap`;
716CREATE 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`)));
717
718--
719-- Definition of view `tank_list`
720--
721
722DROP TABLE IF EXISTS `tank_list`;
723DROP VIEW IF EXISTS `tank_list`;
724CREATE 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`)));
725
726
727
728/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
729/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
730/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
731/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
732/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
733/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
734/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;