X-Git-Url: https://git.mxchange.org/?a=blobdiff_plain;f=install%2Finstall.sql;h=40ce463f1469d7849adfdb423e6f014e294f8dfb;hb=75306257f643e1ca7441d6d3d4169eee81cd20f7;hp=a59bab4346c89de004ed6a46354b612fb2cfba66;hpb=006c5ceb743fedd83420388a06ce50ab5e0cc90f;p=pizzaservice-ejb.git diff --git a/install/install.sql b/install/install.sql index a59bab4..40ce463 100644 --- a/install/install.sql +++ b/install/install.sql @@ -1,18 +1,21 @@ -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', `first_name` varchar(100) NOT NULL COMMENT 'First name', `family_name` varchar(100) NOT NULL COMMENT 'Family name', - `company_name` varchar(255) DEFAULT NULL COMMENT 'Company name', `street` varchar(255) DEFAULT NULL COMMENT 'Street name', `house_number` smallint(5) unsigned DEFAULT NULL COMMENT 'House number', `city` varchar(100) DEFAULT NULL COMMENT 'City name', @@ -23,36 +26,59 @@ CREATE TABLE IF NOT EXISTS `contacts` ( `fax_number` varchar(100) DEFAULT NULL COMMENT 'Fax number', `email_address` varchar(100) DEFAULT NULL COMMENT 'Email addres', `birthday` date DEFAULT NULL COMMENT 'Birth day', - `comment` tinytext NOT NULL COMMENT 'Comment', + `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' NOT 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 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 `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', + `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 (`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 IF NOT EXISTS `products` ( +CREATE TABLE `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', + `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` (`category`) -) ENGINE=InnoDBDEFAULT CHARSET=utf8mb4 COMMENT='Products' ; + INDEX (`category`) +) ENGINE=InnoDBDEFAULT CHARSET=utf8 COMMENT='Products'; ALTER TABLE `category` MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key'; @@ -63,14 +89,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 `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 `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`); ALTER TABLE `products` -ADD FOREIGN KEY (`category`) REFERENCES `category` (`id`) ON DELETE SET NULL; +ADD CONSTRAINT `products_ibfk_1` FOREIGN KEY (`category`) REFERENCES `category` (`id`) ON DELETE SET NULL ON UPDATE NO ACTION; + +SET FOREIGN_KEY_CHECKS=1;