Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- MySQL Administrator dump 1.4
- --
- -- ------------------------------------------------------
- -- Server version 5.1.32-community-log
- /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
- /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
- /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
- /*!40101 SET NAMES utf8 */;
- /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
- /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
- /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
- --
- -- Create schema water_uwi
- --
- CREATE DATABASE IF NOT EXISTS water_uwi;
- USE water_uwi;
- --
- -- Temporary table structure for view `calctime`
- --
- DROP TABLE IF EXISTS `calctime`;
- DROP VIEW IF EXISTS `calctime`;
- CREATE TABLE `calctime` (
- `delivery_id` INT(11),
- `LAST_DELIVERY` TIME,
- `delivery_date` TIMESTAMP,
- `description` VARCHAR(100)
- );
- --
- -- Temporary table structure for view `last_call_time`
- --
- DROP TABLE IF EXISTS `last_call_time`;
- DROP VIEW IF EXISTS `last_call_time`;
- CREATE TABLE `last_call_time` (
- `call_id` INT(11),
- `description` VARCHAR(100),
- `call_date` datetime,
- `notes` VARCHAR(500),
- `status` VARCHAR(45)
- );
- --
- -- Temporary table structure for view `last_delivery`
- --
- DROP TABLE IF EXISTS `last_delivery`;
- DROP VIEW IF EXISTS `last_delivery`;
- CREATE TABLE `last_delivery` (
- `delivery_id` INT(11),
- `delivery_date` TIMESTAMP,
- `driver_name` VARCHAR(45),
- `capacity` INT(11),
- `capacity_measure` VARCHAR(45),
- `description` VARCHAR(100)
- );
- --
- -- Temporary table structure for view `tank_cap`
- --
- DROP TABLE IF EXISTS `tank_cap`;
- DROP VIEW IF EXISTS `tank_cap`;
- CREATE TABLE `tank_cap` (
- `tank_id` VARCHAR(10),
- `capacity` INT(11),
- `capacity_measure` VARCHAR(45),
- `description` VARCHAR(100)
- );
- --
- -- Temporary table structure for view `tank_list`
- --
- DROP TABLE IF EXISTS `tank_list`;
- DROP VIEW IF EXISTS `tank_list`;
- CREATE TABLE `tank_list` (
- `location_code` VARCHAR(45),
- `description` VARCHAR(100),
- `capacity` INT(11),
- `capacity_measure` VARCHAR(45)
- );
- --
- -- Definition of table `cap_measure`
- --
- DROP TABLE IF EXISTS `cap_measure`;
- CREATE TABLE `cap_measure` (
- `meter_id` INT(11) NOT NULL AUTO_INCREMENT,
- `cap_measure` VARCHAR(45) NOT NULL,
- PRIMARY KEY (`meter_id`)
- ) ENGINE=MyISAM AUTO_INCREMENT=2324 DEFAULT CHARSET=latin1;
- --
- -- Dumping data for table `cap_measure`
- --
- /*!40000 ALTER TABLE `cap_measure` DISABLE KEYS */;
- /*!40000 ALTER TABLE `cap_measure` ENABLE KEYS */;
- --
- -- Definition of table `delivery`
- --
- DROP TABLE IF EXISTS `delivery`;
- CREATE TABLE `delivery` (
- `delivery_id` INT(11) NOT NULL AUTO_INCREMENT,
- `delivery_date` TIMESTAMP NULL DEFAULT NULL,
- `truck_id` VARCHAR(6) NOT NULL,
- `location_id` INT(11) NOT NULL,
- `tank_id` VARCHAR(10) NOT NULL,
- PRIMARY KEY (`delivery_id`),
- KEY `idtruck` (`truck_id`),
- KEY `dev_loc` (`location_id`),
- KEY `tank_d` (`tank_id`),
- CONSTRAINT `dev_loc` FOREIGN KEY (`location_id`) REFERENCES `tank_location` (`tank_location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
- CONSTRAINT `idtruck` FOREIGN KEY (`truck_id`) REFERENCES `truck` (`truck_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
- CONSTRAINT `tank_d` FOREIGN KEY (`tank_id`) REFERENCES `tank` (`tank_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
- ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
- --
- -- Dumping data for table `delivery`
- --
- /*!40000 ALTER TABLE `delivery` DISABLE KEYS */;
- INSERT INTO `delivery` (`delivery_id`,`delivery_date`,`truck_id`,`location_id`,`tank_id`) VALUES
- (1,'2010-02-05 03:04:44','TR123',2325,'43434'),
- (2,'2010-02-04 03:03:00','ST453',2324,'43434'),
- (4,'2010-02-06 07:05:22','ST453',2325,'43434'),
- (5,'2010-02-05 17:51:50','TR234',2325,'43434');
- /*!40000 ALTER TABLE `delivery` ENABLE KEYS */;
- --
- -- Definition of table `location`
- --
- DROP TABLE IF EXISTS `location`;
- CREATE TABLE `location` (
- `location_id` INT(11) NOT NULL AUTO_INCREMENT,
- `location_code` VARCHAR(45) NOT NULL,
- PRIMARY KEY (`location_id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
- --
- -- Dumping data for table `location`
- --
- /*!40000 ALTER TABLE `location` DISABLE KEYS */;
- INSERT INTO `location` (`location_id`,`location_code`) VALUES
- (1,'Mary Secole'),
- (2,'Taylor'),
- (3,'Rex'),
- (4,'Preston'),
- (5,'Chanc');
- /*!40000 ALTER TABLE `location` ENABLE KEYS */;
- --
- -- Definition of table `phone_call`
- --
- DROP TABLE IF EXISTS `phone_call`;
- CREATE TABLE `phone_call` (
- `call_id` INT(11) NOT NULL AUTO_INCREMENT,
- `location_id` INT(11) NOT NULL,
- `call_date` datetime NOT NULL,
- `status` INT(11) DEFAULT NULL,
- `notes` VARCHAR(500) DEFAULT NULL,
- PRIMARY KEY (`call_id`),
- KEY `call_loc_id` (`location_id`),
- KEY `stat` (`status`),
- CONSTRAINT `call_loc_id` FOREIGN KEY (`location_id`) REFERENCES `tank_location` (`tank_location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
- CONSTRAINT `stat` FOREIGN KEY (`status`) REFERENCES `status` (`status_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
- ) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=latin1;
- --
- -- Dumping data for table `phone_call`
- --
- /*!40000 ALTER TABLE `phone_call` DISABLE KEYS */;
- INSERT INTO `phone_call` (`call_id`,`location_id`,`call_date`,`status`,`notes`) VALUES
- (1,2324,'2010-02-05 02:41:36',1,NULL),
- (12,2325,'2010-02-12 03:08:45',2,NULL);
- /*!40000 ALTER TABLE `phone_call` ENABLE KEYS */;
- --
- -- Definition of table `status`
- --
- DROP TABLE IF EXISTS `status`;
- CREATE TABLE `status` (
- `status_id` INT(11) NOT NULL AUTO_INCREMENT,
- `description` VARCHAR(45) NOT NULL,
- PRIMARY KEY (`status_id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
- --
- -- Dumping data for table `status`
- --
- /*!40000 ALTER TABLE `status` DISABLE KEYS */;
- INSERT INTO `status` (`status_id`,`description`) VALUES
- (1,'empty'),
- (2,'full');
- /*!40000 ALTER TABLE `status` ENABLE KEYS */;
- --
- -- Definition of table `tank`
- --
- DROP TABLE IF EXISTS `tank`;
- CREATE TABLE `tank` (
- `tank_id` VARCHAR(10) NOT NULL,
- `location_id` INT(11) NOT NULL,
- `capacity` INT(11) NOT NULL,
- `capacity_measure` VARCHAR(45) NOT NULL,
- PRIMARY KEY (`tank_id`),
- KEY `tank_loc_id` (`location_id`),
- CONSTRAINT `tank_loc_id` FOREIGN KEY (`location_id`) REFERENCES `tank_location` (`tank_location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
- --
- -- Dumping data for table `tank`
- --
- /*!40000 ALTER TABLE `tank` DISABLE KEYS */;
- INSERT INTO `tank` (`tank_id`,`location_id`,`capacity`,`capacity_measure`) VALUES
- ('23223',2325,23232,'M'),
- ('43434',2324,232323,'L');
- /*!40000 ALTER TABLE `tank` ENABLE KEYS */;
- --
- -- Definition of table `tank_location`
- --
- DROP TABLE IF EXISTS `tank_location`;
- CREATE TABLE `tank_location` (
- `tank_location_id` INT(11) NOT NULL AUTO_INCREMENT,
- `location_id` INT(11) NOT NULL,
- `description` VARCHAR(100) NOT NULL,
- PRIMARY KEY (`tank_location_id`),
- KEY `tank_loc` (`location_id`),
- CONSTRAINT `tank_loc` FOREIGN KEY (`location_id`) REFERENCES `location` (`location_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
- ) ENGINE=InnoDB AUTO_INCREMENT=2326 DEFAULT CHARSET=latin1;
- --
- -- Dumping data for table `tank_location`
- --
- /*!40000 ALTER TABLE `tank_location` DISABLE KEYS */;
- INSERT INTO `tank_location` (`tank_location_id`,`location_id`,`description`) VALUES
- (1,1,'2 Floor'),
- (2324,1,'Front Gate'),
- (2325,2,'Roosters');
- /*!40000 ALTER TABLE `tank_location` ENABLE KEYS */;
- --
- -- Definition of table `truck`
- --
- DROP TABLE IF EXISTS `truck`;
- CREATE TABLE `truck` (
- `truck_id` VARCHAR(6) NOT NULL,
- `driver_name` VARCHAR(45) DEFAULT NULL,
- `capacity` INT(11) DEFAULT NULL,
- `capacity_measure` VARCHAR(45) DEFAULT NULL,
- PRIMARY KEY (`truck_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
- --
- -- Dumping data for table `truck`
- --
- /*!40000 ALTER TABLE `truck` DISABLE KEYS */;
- INSERT INTO `truck` (`truck_id`,`driver_name`,`capacity`,`capacity_measure`) VALUES
- ('FT322','jim brown',22221,'M'),
- ('ST453','andre pink',22556,'M'),
- ('TR123','john brown',232323,'CM'),
- ('TR234','joe black',22222,'G'),
- ('WQ232','daine k',3434322,'L');
- /*!40000 ALTER TABLE `truck` ENABLE KEYS */;
- --
- -- Definition of table `user`
- --
- DROP TABLE IF EXISTS `user`;
- CREATE TABLE `user` (
- `user_id` INT(11) NOT NULL AUTO_INCREMENT,
- `username` VARCHAR(45) NOT NULL,
- `user_pass` VARCHAR(45) NOT NULL,
- `user_type` INT(11) NOT NULL,
- PRIMARY KEY (`user_id`),
- KEY `u_type` (`user_type`),
- CONSTRAINT `u_type` FOREIGN KEY (`user_type`) REFERENCES `user_type` (`usertype_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
- ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
- --
- -- Dumping data for table `user`
- --
- /*!40000 ALTER TABLE `user` DISABLE KEYS */;
- INSERT INTO `user` (`user_id`,`username`,`user_pass`,`user_type`) VALUES
- (1,'admin','password',1),
- (2,'test','test',2);
- /*!40000 ALTER TABLE `user` ENABLE KEYS */;
- --
- -- Definition of table `user_type`
- --
- DROP TABLE IF EXISTS `user_type`;
- CREATE TABLE `user_type` (
- `usertype_id` INT(11) NOT NULL,
- `description` VARCHAR(45) NOT NULL,
- PRIMARY KEY (`usertype_id`)
- ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
- --
- -- Dumping data for table `user_type`
- --
- /*!40000 ALTER TABLE `user_type` DISABLE KEYS */;
- INSERT INTO `user_type` (`usertype_id`,`description`) VALUES
- (1,'admin'),
- (2,'normal');
- /*!40000 ALTER TABLE `user_type` ENABLE KEYS */;
- --
- -- Definition of procedure `get_delivery`
- --
- DROP PROCEDURE IF EXISTS `get_delivery`;
- DELIMITER $$
- /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `get_delivery`()
- BEGIN
- SELECT * FROM delivery;
- END $$
- /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
- DELIMITER ;
- --
- -- Definition of procedure `get_pass`
- --
- DROP PROCEDURE IF EXISTS `get_pass`;
- DELIMITER $$
- /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `get_pass`(user_nm VARCHAR(50))
- BEGIN
- SELECT user_pass FROM USER WHERE username = user_um ;
- END $$
- /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
- DELIMITER ;
- --
- -- Definition of procedure `get_tank`
- --
- DROP PROCEDURE IF EXISTS `get_tank`;
- DELIMITER $$
- /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `get_tank`()
- BEGIN
- SELECT * FROM tank;
- END $$
- /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
- DELIMITER ;
- --
- -- Definition of procedure `insert_delivery`
- --
- DROP PROCEDURE IF EXISTS `insert_delivery`;
- DELIMITER $$
- /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_delivery`(delivery_dt DATETIME, truck_num VARCHAR(6),loc_id INT,tank_num VARCHAR(6))
- BEGIN
- INSERT INTO delivery (delivery_date,truck_id,location_id,tank_id) VALUES (delivery_dt,truck_num,loc_id,tank_num);
- END $$
- /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
- DELIMITER ;
- --
- -- Definition of procedure `insert_location`
- --
- DROP PROCEDURE IF EXISTS `insert_location`;
- DELIMITER $$
- /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_location`(loc_code VARCHAR(50))
- BEGIN
- INSERT INTO location (location_code) VALUES (loc_code);
- END $$
- /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
- DELIMITER ;
- --
- -- Definition of procedure `insert_phone_call`
- --
- DROP PROCEDURE IF EXISTS `insert_phone_call`;
- DELIMITER $$
- /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_phone_call`(loc_id INT,call_dt DATETIME, stat INT, nt VARCHAR(500))
- BEGIN
- INSERT INTO phone_call(location_id,call_date,STATUS,notes) VALUES (loc_id,call_dt,stat,nt);
- END $$
- /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
- DELIMITER ;
- --
- -- Definition of procedure `insert_tank`
- --
- DROP PROCEDURE IF EXISTS `insert_tank`;
- DELIMITER $$
- /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_tank`(tank_num VARCHAR(10), loc_id INT, cap INT, cap_meter VARCHAR(45))
- BEGIN
- INSERT INTO tank (tank_id,location_id,capacity,capacity_measure) VALUES (tank_num,loc_id,cap,cap_meter);
- END $$
- /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
- DELIMITER ;
- --
- -- Definition of procedure `insert_tank_location`
- --
- DROP PROCEDURE IF EXISTS `insert_tank_location`;
- DELIMITER $$
- /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_tank_location`(loc_id INT, descript VARCHAR(100))
- BEGIN
- INSERT INTO tank_location (location_id,description) VALUES (loc_id,descript);
- END $$
- /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
- DELIMITER ;
- --
- -- Definition of procedure `insert_truck`
- --
- DROP PROCEDURE IF EXISTS `insert_truck`;
- DELIMITER $$
- /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_truck`(truck_license VARCHAR(6), driver_nm VARCHAR(50), cap INT, cap_meter VARCHAR(45))
- BEGIN
- INSERT INTO truck (truck_id,driver_name,capacity,capacity_measure) VALUES (tank_license,driver_nm,cap,cap_meter);
- END $$
- /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
- DELIMITER ;
- --
- -- Definition of procedure `insert_user`
- --
- DROP PROCEDURE IF EXISTS `insert_user`;
- DELIMITER $$
- /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_user`(user_nm VARCHAR(50),user_ps VARCHAR(50), user_t INT)
- BEGIN
- INSERT INTO USER(username,user_pass,user_type) VALUES (user_nm,user_ps,user_t);
- END $$
- /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
- DELIMITER ;
- --
- -- Definition of procedure `query_calltime`
- --
- DROP PROCEDURE IF EXISTS `query_calltime`;
- DELIMITER $$
- /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `query_calltime`(call_dt DATETIME)
- BEGIN
- SELECT
- phone_call.call_id,
- phone_call.call_date,
- phone_call.STATUS,
- phone_call.notes,
- tank_location.description
- FROM phone_call
- INNER JOIN tank_location ON tank_location.tank_location_id = phone_call.location_id
- WHERE phone_call.call_date = call_dt;
- END $$
- /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
- DELIMITER ;
- --
- -- Definition of procedure `query_date`
- --
- DROP PROCEDURE IF EXISTS `query_date`;
- DELIMITER $$
- /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `query_date`(dt DATETIME)
- BEGIN
- SELECT
- delivery.delivery_id,
- delivery.delivery_date,
- tank_location.description,
- truck.driver_name,
- truck.capacity,
- truck.capacity_measure,
- tank.tank_id,
- tank.capacity,
- tank.capacity_measure
- FROM
- delivery
- INNER JOIN truck ON truck.truck_id = delivery.truck_id
- INNER JOIN tank_location ON tank_location.tank_location_id = delivery.location_id
- INNER JOIN tank ON tank.tank_id = delivery.tank_id
- WHERE delivery.delivery_date = dt;
- END $$
- /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
- DELIMITER ;
- --
- -- Definition of procedure `query_daterange`
- --
- DROP PROCEDURE IF EXISTS `query_daterange`;
- DELIMITER $$
- /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `query_daterange`()
- BEGIN
- SELECT timediff(`delivery`.`delivery_date`,CURRENT_TIMESTAMP()) AS `delivery_date`
- FROM ((`delivery` JOIN `truck` ON((`truck`.`truck_id` = `delivery`.`truck_id`))) JOIN `tank_location` ON((`tank_location`.`tank_location_id` = `delivery`.`location_id`)))
- WHERE
- delivery.delivery_date BETWEEN CURRENT_TIMESTAMP() AND delivery.delivery_date
- ORDER BY `delivery`.`delivery_date` DESC LIMIT 1;
- END $$
- /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
- DELIMITER ;
- --
- -- Definition of procedure `query_lastdelivery`
- --
- DROP PROCEDURE IF EXISTS `query_lastdelivery`;
- DELIMITER $$
- /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `query_lastdelivery`()
- BEGIN
- 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
- FROM ((`delivery` JOIN `truck` ON((`truck`.`truck_id` = `delivery`.`truck_id`))) JOIN `tank_location` ON((`tank_location`.`tank_location_id` = `delivery`.`location_id`)))
- ORDER BY `delivery`.`delivery_date` DESC LIMIT 1;
- END $$
- /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
- DELIMITER ;
- --
- -- Definition of procedure `query_location`
- --
- DROP PROCEDURE IF EXISTS `query_location`;
- DELIMITER $$
- /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `query_location`()
- BEGIN
- SELECT * FROM location;
- END $$
- /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
- DELIMITER ;
- --
- -- Definition of procedure `query_tank`
- --
- DROP PROCEDURE IF EXISTS `query_tank`;
- DELIMITER $$
- /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `query_tank`(loc_id INT)
- BEGIN
- SELECT * FROM tank WHERE location_id = loc_id;
- END $$
- /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
- DELIMITER ;
- --
- -- Definition of procedure `query_truck`
- --
- DROP PROCEDURE IF EXISTS `query_truck`;
- DELIMITER $$
- /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `query_truck`()
- BEGIN
- SELECT * FROM truck;
- END $$
- /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
- DELIMITER ;
- --
- -- Definition of procedure `validate_user`
- --
- DROP PROCEDURE IF EXISTS `validate_user`;
- DELIMITER $$
- /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$
- CREATE DEFINER=`root`@`localhost` PROCEDURE `validate_user`(user_nm VARCHAR(50),user_ps VARCHAR(50), user_t INT)
- BEGIN
- SELECT user_type FROM USER WHERE username = um AND user_pass = user_ps;
- END $$
- /*!50003 SET SESSION SQL_MODE=@TEMP_SQL_MODE */ $$
- DELIMITER ;
- --
- -- Definition of view `calctime`
- --
- DROP TABLE IF EXISTS `calctime`;
- DROP VIEW IF EXISTS `calctime`;
- CREATE 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`)));
- --
- -- Definition of view `last_call_time`
- --
- DROP TABLE IF EXISTS `last_call_time`;
- DROP VIEW IF EXISTS `last_call_time`;
- CREATE 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`)));
- --
- -- Definition of view `last_delivery`
- --
- DROP TABLE IF EXISTS `last_delivery`;
- DROP VIEW IF EXISTS `last_delivery`;
- CREATE 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`)));
- --
- -- Definition of view `tank_cap`
- --
- DROP TABLE IF EXISTS `tank_cap`;
- DROP VIEW IF EXISTS `tank_cap`;
- CREATE 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`)));
- --
- -- Definition of view `tank_list`
- --
- DROP TABLE IF EXISTS `tank_list`;
- DROP VIEW IF EXISTS `tank_list`;
- CREATE 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`)));
- /*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
- /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
- /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
- /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
- /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
- /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
- /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement