DB_MySQL.txt

-- ---
-- Globals
-- ---

-- SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
-- SET FOREIGN_KEY_CHECKS=0;

-- ---
-- Table 'storage'
-- Table with info about storage (product, count, load timestamp)
-- ---

DROP TABLE IF EXISTS `storage`;
		
CREATE TABLE `storage` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `product_id` INTEGER NOT NULL AUTO_INCREMENT,
  `product_count` INTEGER NOT NULL DEFAULT 0,
  `delivery_timestamp` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) COMMENT 'Table with info about storage (product, count, load timestam';

-- ---
-- Table 'store'
-- Table with info about store shelves (width, height, length, carrying capacity, shelf number, product ID, product count, last charge)
-- ---

DROP TABLE IF EXISTS `store`;
		
CREATE TABLE `store` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `width` INTEGER NOT NULL DEFAULT 0,
  `height` INTEGER NOT NULL DEFAULT 0,
  `length` INTEGER NOT NULL DEFAULT 0,
  `carrying_capacity` INTEGER NOT NULL DEFAULT 0,
  `shelf_number` INTEGER NOT NULL DEFAULT 0,
  `product_id` INTEGER NULL DEFAULT NULL,
  `product_count` INTEGER NOT NULL DEFAULT 0,
  `last_charge` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) COMMENT 'Table with info about store shelves (width, height, length, ';

-- ---
-- Table 'sales'
-- Table with info about sales (product, date, sold count) 
-- ---

DROP TABLE IF EXISTS `sales`;
		
CREATE TABLE `sales` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `product_id` INTEGER NOT NULL,
  `sold_date` DATETIME NULL DEFAULT NULL,
  `sold_count` INTEGER NOT NULL DEFAULT 0,
  `sold_price` FLOAT NOT NULL DEFAULT 0.0,
  PRIMARY KEY (`id`)
) COMMENT 'Table with info about sales (product, date, sold count) ';

-- ---
-- Table 'product'
-- Table with info about products (shelf life, width, height, length, weight)
-- ---

DROP TABLE IF EXISTS `product`;
		
CREATE TABLE `product` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `shelf_life` DATETIME NULL DEFAULT NULL,
  `width` INTEGER NOT NULL DEFAULT 0,
  `height` INTEGER NOT NULL DEFAULT 0,
  `length` INTEGER NOT NULL DEFAULT 0,
  `weight` INTEGER NOT NULL DEFAULT 0,
  `price` FLOAT NOT NULL DEFAULT 0.0,
  `stackable` TINYINT(1) NOT NULL DEFAULT 0,
  `name` VARCHAR(30) NOT NULL,
  PRIMARY KEY (`id`)
) COMMENT 'Table with info about products (shelf life, width, height, l';

-- ---
-- Table 'stock'
-- Table for promotional products (product ID, shelf ID (if needed), stock price, stock start timestamp, stock period)
-- ---

DROP TABLE IF EXISTS `stock`;
		
CREATE TABLE `stock` (
  `id` INTEGER NULL AUTO_INCREMENT DEFAULT NULL,
  `product_id` INTEGER NOT NULL,
  `shelf_id` INTEGER NULL DEFAULT NULL,
  `price` FLOAT NOT NULL DEFAULT 0.0,
  `start_timestamp` DATETIME NULL DEFAULT NULL,
  `end_timestamp` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) COMMENT 'Table for promotional products (product ID, shelf ID (if nee';

-- ---
-- Table 'failure'
-- Table with info about failures (failure text, failure severity, failrute timestamp)
-- ---

DROP TABLE IF EXISTS `failure`;
		
CREATE TABLE `failure` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `text` MEDIUMTEXT NOT NULL,
  `severity` INTEGER(1) NOT NULL DEFAULT 1,
  `timestamp` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) COMMENT 'Table with info about failures (failure text, failure severi';

-- ---
-- Table 'statistics'
-- Table for statistics about each day of supermarket work (day of work, revenue at this day, sold products count, is it a stock day, failures count)
-- ---

DROP TABLE IF EXISTS `statistics`;
		
CREATE TABLE `statistics` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `day` DATE NULL DEFAULT NULL,
  `revenue` DOUBLE NOT NULL DEFAULT 0.0,
  `sold_count` INTEGER NOT NULL DEFAULT 0,
  `is_stock_day` TINYINT(1) NOT NULL DEFAULT 0,
  `failures_count` INTEGER NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) COMMENT 'Table for statistics about each day of supermarket work (day';

-- ---
-- Table 'user'
-- Table with info about each user (name, login, password, email (if defined), last access timestamp, group name)
-- ---

DROP TABLE IF EXISTS `user`;
		
CREATE TABLE `user` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `login` VARCHAR(30) NOT NULL,
  `password` VARCHAR(100) NOT NULL,
  `email` VARCHAR(100) NULL DEFAULT NULL,
  `last_access` DATETIME NULL DEFAULT NULL,
  `group` ENUM('personel', 'management', 'admin') NOT NULL DEFAULT personel,
  PRIMARY KEY (`id`)
) COMMENT 'Table with info about each user (name, login, password, emai';

-- ---
-- Foreign Keys 
-- ---

ALTER TABLE `storage` ADD FOREIGN KEY (product_id) REFERENCES `product` (`id`);
ALTER TABLE `store` ADD FOREIGN KEY (product_id) REFERENCES `product` (`id`);
ALTER TABLE `sales` ADD FOREIGN KEY (product_id) REFERENCES `product` (`id`);
ALTER TABLE `stock` ADD FOREIGN KEY (product_id) REFERENCES `product` (`id`);
ALTER TABLE `stock` ADD FOREIGN KEY (shelf_id) REFERENCES `store` (`id`);

-- ---
-- Table Properties
-- ---

-- ALTER TABLE `storage` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `store` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `sales` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `product` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `stock` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `failure` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `statistics` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
-- ALTER TABLE `user` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- ---
-- Test Data
-- ---

-- INSERT INTO `storage` (`id`,`product_id`,`product_count`,`delivery_timestamp`) VALUES
-- ('','','','');
-- INSERT INTO `store` (`id`,`width`,`height`,`length`,`carrying_capacity`,`shelf_number`,`product_id`,`product_count`,`last_charge`) VALUES
-- ('','','','','','','','','');
-- INSERT INTO `sales` (`id`,`product_id`,`sold_date`,`sold_count`,`sold_price`) VALUES
-- ('','','','','');
-- INSERT INTO `product` (`id`,`shelf_life`,`width`,`height`,`length`,`weight`,`price`,`stackable`,`name`) VALUES
-- ('','','','','','','','','');
-- INSERT INTO `stock` (`id`,`product_id`,`shelf_id`,`price`,`start_timestamp`,`end_timestamp`) VALUES
-- ('','','','','','');
-- INSERT INTO `failure` (`id`,`text`,`severity`,`timestamp`) VALUES
-- ('','','','');
-- INSERT INTO `statistics` (`id`,`day`,`revenue`,`sold_count`,`is_stock_day`,`failures_count`) VALUES
-- ('','','','','','');
-- INSERT INTO `user` (`id`,`login`,`password`,`email`,`last_access`,`group`) VALUES
-- ('','','','','','');