sd-spreadsheet/schema.sql

/*
SQLyog Ultimate v12.14 (64 bit)
MySQL - 5.7.11 : Database - sdpickup
*********************************************************************
*/

/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

/*!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' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
/*Table structure for table `company` */

DROP TABLE IF EXISTS `company`;

CREATE TABLE `company` (
  `id_company` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `ftp_directory` varchar(32) DEFAULT NULL COMMENT 'name of subdirectory for ftp scanning',
  `name` varchar(64) NOT NULL,
  `description` text,
  PRIMARY KEY (`id_company`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

/*Table structure for table `driver` */

DROP TABLE IF EXISTS `driver`;

CREATE TABLE `driver` (
  `id_driver` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(64) DEFAULT NULL,
  `date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_driver`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;

/*Table structure for table `driver_company` */

DROP TABLE IF EXISTS `driver_company`;

CREATE TABLE `driver_company` (
  `id_driver` int(10) unsigned NOT NULL,
  `id_company` smallint(5) unsigned NOT NULL,
  PRIMARY KEY (`id_driver`,`id_company`),
  KEY `id_company` (`id_company`),
  CONSTRAINT `driver_company_ibfk_1` FOREIGN KEY (`id_driver`) REFERENCES `driver` (`id_driver`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `driver_company_ibfk_2` FOREIGN KEY (`id_company`) REFERENCES `company` (`id_company`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Table structure for table `file` */

DROP TABLE IF EXISTS `file`;

CREATE TABLE `file` (
  `id_file` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_company` smallint(5) unsigned NOT NULL,
  `dir` varchar(64) DEFAULT NULL,
  `filename` varchar(64) DEFAULT NULL,
  `md5_hash` char(32) DEFAULT NULL,
  `size` bigint(20) unsigned DEFAULT NULL,
  `num_rows` int(10) unsigned DEFAULT NULL,
  `num_ok` int(10) unsigned DEFAULT NULL,
  `num_fail` int(10) unsigned DEFAULT NULL,
  `num_skip` int(10) unsigned DEFAULT NULL,
  `date_processed` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_file`),
  UNIQUE KEY `filename` (`md5_hash`,`size`),
  KEY `id_company` (`id_company`),
  CONSTRAINT `file_ibfk_1` FOREIGN KEY (`id_company`) REFERENCES `company` (`id_company`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=latin1;

/*Table structure for table `file_row` */

DROP TABLE IF EXISTS `file_row`;

CREATE TABLE `file_row` (
  `id_file_row` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `id_file` int(10) unsigned NOT NULL,
  `row_number` int(11) unsigned NOT NULL,
  `division` varchar(64) DEFAULT NULL COMMENT 'Dynamic Delivery Services',
  `consignee` varchar(32) DEFAULT NULL COMMENT 'GW-PVH',
  `date_called_in` date DEFAULT NULL COMMENT '05/04/2016',
  `dispatch_number` varchar(32) DEFAULT NULL COMMENT '22940402-001',
  `shipper` varchar(128) DEFAULT NULL COMMENT 'Joe Shipping Firm',
  `date_ready` date DEFAULT NULL COMMENT '05/04/2016',
  `address_1` varchar(128) DEFAULT NULL COMMENT '123 4th St',
  `address_2` varchar(128) DEFAULT NULL COMMENT 'Apt 1',
  `city` varchar(128) DEFAULT NULL COMMENT 'Beverly Hills',
  `state` char(2) DEFAULT NULL COMMENT 'CA',
  `zip` varchar(32) DEFAULT NULL COMMENT '90210',
  `phone` varchar(32) DEFAULT NULL COMMENT '2015551234',
  `time_ready` time DEFAULT NULL COMMENT '08:00 AM',
  `time_close` time DEFAULT NULL COMMENT '05:00 PM',
  `time_pickup` datetime DEFAULT NULL COMMENT 'user entered',
  `time_depart` datetime DEFAULT NULL COMMENT 'user entered',
  `bol_delivered` datetime DEFAULT NULL COMMENT 'user entered',
  `dispatch_reference` varchar(32) DEFAULT NULL,
  `ctns` int(11) DEFAULT NULL COMMENT '20',
  `weight` decimal(10,0) DEFAULT NULL COMMENT '500.00',
  `cube` decimal(10,0) DEFAULT NULL COMMENT '42.00',
  `date_processed` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `status` varchar(16) DEFAULT NULL COMMENT '"ok" or "fail"',
  `id_driver` int(10) unsigned DEFAULT NULL,
  `trailer` varchar(32) DEFAULT NULL,
  `date_canceled` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id_file_row`),
  UNIQUE KEY `id_file` (`id_file`,`row_number`),
  KEY `id_driver` (`id_driver`),
  CONSTRAINT `file_row_ibfk_1` FOREIGN KEY (`id_file`) REFERENCES `file` (`id_file`) ON UPDATE CASCADE,
  CONSTRAINT `file_row_ibfk_2` FOREIGN KEY (`id_driver`) REFERENCES `driver` (`id_driver`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=360 DEFAULT CHARSET=latin1;

/*Table structure for table `file_row_update` */

DROP TABLE IF EXISTS `file_row_update`;

CREATE TABLE `file_row_update` (
  `id_file_row_update` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `id_file_row` bigint(10) unsigned NOT NULL,
  `time_pickup` datetime DEFAULT NULL,
  `time_depart` datetime DEFAULT NULL,
  `bol_delivered` datetime DEFAULT NULL,
  `id_user` int(10) unsigned NOT NULL,
  `date_modified` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_file_row_update`),
  KEY `id_user` (`id_user`),
  KEY `id_record` (`id_file_row`),
  CONSTRAINT `file_row_update_ibfk_1` FOREIGN KEY (`id_user`) REFERENCES `user` (`id_user`) ON UPDATE CASCADE,
  CONSTRAINT `file_row_update_ibfk_2` FOREIGN KEY (`id_file_row`) REFERENCES `file_row` (`id_file_row`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id_user` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_company` smallint(5) unsigned NOT NULL,
  `name` varchar(128) DEFAULT NULL,
  `email` varchar(64) NOT NULL,
  `password` varchar(128) DEFAULT NULL,
  `date_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id_user`),
  UNIQUE KEY `email` (`email`),
  KEY `id_user` (`id_user`),
  KEY `id_company` (`id_company`),
  CONSTRAINT `user_ibfk_1` FOREIGN KEY (`id_company`) REFERENCES `company` (`id_company`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;