-DROP TABLE IF EXISTS `category`;
-CREATE TABLE IF NOT EXISTS `category` (
-`id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
- `title` varchar(255) NOT NULL COMMENT 'Category title',
- `parent` bigint(20) unsigned DEFAULT NULL COMMENT 'Parent category',
- PRIMARY KEY (`id`),
- INDEX `parent` (`parent`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Categories' ;
-
-DROP TABLE IF EXISTS `contacts`;
-CREATE TABLE IF NOT EXISTS `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',
- `zip_code` smallint(5) unsigned DEFAULT NULL COMMENT 'ZIP code',
- `country_code` char(2) DEFAULT NULL COMMENT 'Country code',
- `phone_number` varchar(100) DEFAULT NULL COMMENT 'Phone number',
- `cellphone_number` varchar(100) DEFAULT NULL COMMENT 'Cellphone number',
- `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',
- `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Entry created',
- `updated` timestamp NULL DEFAULT NULL COMMENT 'Entry updated',
- PRIMARY KEY (`id`)
-) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Contacts data' ;
-
-DROP TABLE IF EXISTS `products`;
-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=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Products' ;
+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(100) NOT NULL COMMENT 'Category title',
+ `parent` bigint(20) unsigned DEFAULT NULL COMMENT 'Parent category',
+ PRIMARY KEY (`id`),
+ INDEX `parent` (`parent`)
+) ENGINE=InnoDBDEFAULT CHARSET=utf8 COMMENT='Categories';
+
+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',
+ `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',
+ `zip_code` smallint(5) unsigned DEFAULT NULL COMMENT 'ZIP code',
+ `country_code` char(2) DEFAULT NULL COMMENT 'Country code',
+ `phone_number` varchar(100) DEFAULT NULL COMMENT 'Phone number',
+ `cellphone_number` varchar(100) DEFAULT NULL COMMENT 'Cellphone number',
+ `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 NULL DEFAULT NULL COMMENT 'Comment',
+ `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Entry created',
+ `updated` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT 'Updated entry',
+ PRIMARY KEY (`id`)
+) ENGINE=InnoDBDEFAULT CHARSET=utf8 COMMENT='Contacts data';
+
+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) 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` timestamp NULL DEFAULT NULL COMMENT 'Last locked timestamp',
+ PRIMARY KEY (`id`),
+ UNIQUE (`customer_confirm_key`),
+ UNIQUE (`customer_number`),
+ INDEX (`customer_contact_id`)
+) 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 `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';
ALTER TABLE `contacts`
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 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;
projects / pizzaservice-ejb.git / blobdiff