X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=install%2Finstall.sql;h=3ab7e23b81e447cf3aa36896664488d328f92efa;hb=595afd1788645b8eaac20253c85ff3fb31bc5fdb;hp=61e8e906cda4f3ecdc85c5d2dad3b210da24c20c;hpb=be964054831c41f2995b570f533e3f9820ee31da;p=pizzaservice-ejb.git diff --git a/install/install.sql b/install/install.sql index 61e8e90..3ab7e23 100644 --- a/install/install.sql +++ b/install/install.sql @@ -1,12 +1,16 @@ -CREATE TABLE IF NOT EXISTS `category` ( +SET FOREIGN_KEY_CHECKS=0; +SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO"; +SET time_zone = "+00:00"; + +CREATE TABLE `category` ( `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key', - `title` varchar(255) NOT NULL COMMENT 'Category title', + `title` varchar(100) NOT NULL COMMENT 'Category title', `parent` bigint(20) unsigned DEFAULT NULL COMMENT 'Parent category', PRIMARY KEY (`id`), INDEX `parent` (`parent`) -) ENGINE=InnoDBDEFAULT CHARSET=utf8mb4 COMMENT='Categories' ; +) ENGINE=InnoDBDEFAULT CHARSET=utf8 COMMENT='Categories'; -CREATE TABLE IF NOT EXISTS `contacts` ( +CREATE TABLE `contacts` ( `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key', `own_contact` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether own contact', `gender` varchar(10) NOT NULL DEFAULT 'UNKNOWN' COMMENT 'Gender', @@ -25,46 +29,57 @@ CREATE TABLE IF NOT EXISTS `contacts` ( `birthday` date DEFAULT NULL COMMENT 'Birth day', `comment` tinytext NULL DEFAULT NULL COMMENT 'Comment', `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Entry created', - `updated` timestamp NULL DEFAULT NULL COMMENT 'Entry updated', + `updated` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated entry', PRIMARY KEY (`id`) -) ENGINE=InnoDBDEFAULT CHARSET=utf8mb4 COMMENT='Contacts data' ; +) ENGINE=InnoDBDEFAULT CHARSET=utf8 COMMENT='Contacts data'; -CREATE TABLE IF NOT EXISTS `customer` ( - `id` bigint(20) unsigned NOT NULL COMMENT 'Primay key', +CREATE TABLE `customer` ( + `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key', `customer_contact_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference on "contact"', - `customer_number` varchar(20) COLLATE 'utf8mb4_general_ci' NOT NULL COMMENT 'Customer number', - `customer_password_hash` varchar(255) COLLATE 'utf8mb4_general_ci' NULL DEFAULT NULL COMMENT 'Password hash', - `customer_confirm_key` varchar(50) COLLATE 'utf8mb4_general_ci' NULL DEFAULT NULL COMMENT 'Email confirmation key', - `customer_status` enum('UNCONFIRMED','CONFIRMED','LOCKED') COLLATE 'utf8mb4_general_ci' NOT NULL DEFAULT 'UNCONFIRMED' COMMENT 'Account status', + `customer_number` varchar(20) NOT NULL COMMENT 'Customer number', + `customer_password_hash` varchar(255) DEFAULT NULL COMMENT 'Password hash', + `customer_confirm_key` varchar(50) DEFAULT NULL COMMENT 'Email confirmation key', + `customer_status` enum('UNCONFIRMED','CONFIRMED','LOCKED') NOT NULL DEFAULT 'UNCONFIRMED' COMMENT 'Account status', `customer_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Account creation', - `customer_locked` datetime NULL DEFAULT NULL COMMENT 'Last locked timestamp', + `customer_locked` timestamp NULL DEFAULT NULL COMMENT 'Last locked timestamp', PRIMARY KEY (`id`), UNIQUE (`customer_confirm_key`), UNIQUE (`customer_number`), INDEX (`customer_contact_id`) -) COMMENT='Customer data' ENGINE='InnoDB' COLLATE 'utf8mb4_general_ci'; +) ENGINE=InnoDBDEFAULT CHARSET=utf8 COMMENT='Customer data'; -CREATE TABLE IF NOT EXISTS `products` ( - `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key', - `category` bigint(20) unsigned DEFAULT NULL COMMENT 'Category id', - `title` varchar(255) NOT NULL COMMENT 'Title of product', - `price` decimal(20,2) unsigned NOT NULL COMMENT 'Product price', - `available` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether product is available', - PRIMARY KEY (`id`), - INDEX `category` (`category`) -) ENGINE=InnoDBDEFAULT CHARSET=utf8mb4 COMMENT='Products' ; - -CREATE TABLE IF NOT EXISTS `orders` ( +CREATE TABLE `ordered_items` ( `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key', + `order_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference orders', `order_status` enum('UNCONFIRMED','CONFIRMED','DELIVERED','CANCELED') NOT NULL DEFAULT 'UNCONFIRMED' COMMENT 'Order status', - `customer_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Table reference customer', `product_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference products', + `item_type` VARCHAR(20) NOT NULL COMMENT 'Item type', `amount` bigint(20) unsigned NOT NULL COMMENT 'Ordered amount', `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record inserted', PRIMARY KEY (`id`), - INDEX `product_id` (`product_id`), - INDEX `customer_product` (`customer_id`,`product_id`) -) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Customer orders'; + INDEX (`order_id`), + INDEX (`product_id`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Ordered items'; + +CREATE TABLE `orders` ( + `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key', + `customer_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference customer', + `access_key` varchar(100) NULL DEFAULT NULL COMMENT 'Access key to receipt', + `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Order created', + PRIMARY KEY (`id`), + INDEX (`customer_id`), + UNIQUE (`access_key`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Customer orders'; + +CREATE TABLE `products` ( + `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key', + `category` bigint(20) unsigned DEFAULT NULL COMMENT 'Category id', + `title` varchar(100) NOT NULL COMMENT 'Title of product', + `price` decimal(20,2) unsigned NOT NULL COMMENT 'Product price', + `available` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether product is available', + PRIMARY KEY (`id`), + INDEX (`category`) +) ENGINE=InnoDBDEFAULT CHARSET=utf8 COMMENT='Products'; ALTER TABLE `category` MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key'; @@ -75,21 +90,28 @@ MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key'; ALTER TABLE `customer` MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key'; -ALTER TABLE `products` +ALTER TABLE `ordered_items` MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key'; ALTER TABLE `orders` MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key'; +ALTER TABLE `products` +MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key'; + ALTER TABLE `category` -ADD FOREIGN KEY (`parent`) REFERENCES `category` (`id`) ON DELETE SET NULL; +ADD CONSTRAINT `category_ibfk_1` FOREIGN KEY (`parent`) REFERENCES `category` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION; ALTER TABLE `customer` -ADD FOREIGN KEY (`customer_contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE; +ADD CONSTRAINT `customer_ibfk_1` FOREIGN KEY (`customer_contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE; -ALTER TABLE `products` -ADD FOREIGN KEY (`category`) REFERENCES `category` (`id`) ON DELETE SET NULL; +ALTER TABLE `ordered_items` +ADD CONSTRAINT `ordered_items_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE; ALTER TABLE `orders` -ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`) ON DELETE SET NULL, -ADD CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE; +ADD CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`id`); + +ALTER TABLE `products` +ADD CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category`) REFERENCES `category` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION; + +SET FOREIGN_KEY_CHECKS=1;