install/install.sql

   1 CREATE TABLE IF NOT EXISTS `category` (
   2         `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
   3         `title` varchar(255) NOT NULL COMMENT 'Category title',
   4         `parent` bigint(20) unsigned DEFAULT NULL COMMENT 'Parent category',
   5         PRIMARY KEY (`id`),
   6         INDEX `parent` (`parent`)
   7 ) ENGINE=InnoDBDEFAULT CHARSET=utf8mb4 COMMENT='Categories' ;
   8
   9 CREATE TABLE IF NOT EXISTS `contacts` (
  10         `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
  11         `own_contact` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether own contact',
  12         `gender` varchar(10) NOT NULL DEFAULT 'UNKNOWN' COMMENT 'Gender',
  13         `first_name` varchar(100) NOT NULL COMMENT 'First name',
  14         `family_name` varchar(100) NOT NULL COMMENT 'Family name',
  15         `company_name` varchar(255) DEFAULT NULL COMMENT 'Company name',
  16         `street` varchar(255) DEFAULT NULL COMMENT 'Street name',
  17         `house_number` smallint(5) unsigned DEFAULT NULL COMMENT 'House number',
  18         `city` varchar(100) DEFAULT NULL COMMENT 'City name',
  19         `zip_code` smallint(5) unsigned DEFAULT NULL COMMENT 'ZIP code',
  20         `country_code` char(2) DEFAULT NULL COMMENT 'Country code',
  21         `phone_number` varchar(100) DEFAULT NULL COMMENT 'Phone number',
  22         `cellphone_number` varchar(100) DEFAULT NULL COMMENT 'Cellphone number',
  23         `fax_number` varchar(100) DEFAULT NULL COMMENT 'Fax number',
  24         `email_address` varchar(100) DEFAULT NULL COMMENT 'Email addres',
  25         `birthday` date DEFAULT NULL COMMENT 'Birth day',
  26         `comment` tinytext NULL DEFAULT NULL COMMENT 'Comment',
  27         `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Entry created',
  28         `updated` timestamp NULL DEFAULT NULL COMMENT 'Entry updated',
  29         PRIMARY KEY (`id`)
  30 ) ENGINE=InnoDBDEFAULT CHARSET=utf8mb4 COMMENT='Contacts data' ;
  31
  32 CREATE TABLE IF NOT EXISTS `customer` (
  33         `id` bigint(20) unsigned NOT NULL COMMENT 'Primay key',
  34         `customer_contact_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference on "contact"',
  35         `customer_number` varchar(20) COLLATE 'utf8mb4_general_ci' NOT NULL COMMENT 'Customer number',
  36         `customer_password_hash` varchar(255) COLLATE 'utf8mb4_general_ci' NULL DEFAULT NULL COMMENT 'Password hash',
  37         `customer_confirm_key` varchar(50) COLLATE 'utf8mb4_general_ci' NULL DEFAULT NULL COMMENT 'Email confirmation key',
  38         `customer_status` enum('UNCONFIRMED','CONFIRMED','LOCKED') COLLATE 'utf8mb4_general_ci' NOT NULL DEFAULT 'UNCONFIRMED' COMMENT 'Account status',
  39         `customer_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Account creation',
  40         `customer_locked` datetime NULL DEFAULT NULL COMMENT 'Last locked timestamp',
  41         PRIMARY KEY (`id`),
  42         UNIQUE (`customer_confirm_key`),
  43         UNIQUE (`customer_number`),
  44         INDEX (`customer_contact_id`)
  45 ) COMMENT='Customer data' ENGINE='InnoDB' COLLATE 'utf8mb4_general_ci';
  46
  47 CREATE TABLE IF NOT EXISTS `products` (
  48         `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
  49         `category` bigint(20) unsigned DEFAULT NULL COMMENT 'Category id',
  50         `title` varchar(255) NOT NULL COMMENT 'Title of product',
  51         `price` decimal(20,2) unsigned NOT NULL COMMENT 'Product price',
  52         `available` tinyint(1) NOT NULL DEFAULT '0' COMMENT 'Whether product is available',
  53         PRIMARY KEY (`id`),
  54         INDEX `category` (`category`)
  55 ) ENGINE=InnoDBDEFAULT CHARSET=utf8mb4 COMMENT='Products' ;
  56
  57 CREATE TABLE IF NOT EXISTS `orders` (
  58         `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
  59         `customer_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference customer',
  60         `access_key` varchar(50) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT 'Access key to receipt',
  61         `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Order created',
  62         PRIMARY KEY (`id`),
  63         UNIQUE KEY `access_key` (`access_key`),
  64         INDEX `customer_id` (`customer_id`)
  65 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Customer orders';
  66
  67 CREATE TABLE IF NOT EXISTS `ordered_items` (
  68         `id` bigint(20) unsigned NOT NULL COMMENT 'Primary key',
  69         `order_id` bigint(20) unsigned DEFAULT NULL COMMENT 'Table reference orders',
  70         `order_status` enum('UNCONFIRMED','CONFIRMED','DELIVERED','CANCELED') NOT NULL DEFAULT 'UNCONFIRMED' COMMENT 'Order status',
  71         `product_id` bigint(20) unsigned NOT NULL COMMENT 'Table reference products',
  72         `amount` bigint(20) unsigned NOT NULL COMMENT 'Ordered amount',
  73         `added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Record inserted',
  74         PRIMARY KEY (`id`),
  75         INDEX `order_id` (`order_id`),
  76         INDEX `product_id` (`product_id`),
  77         INDEX `customer_product` (`customer_id`,`product_id`)
  78 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Customer orders';
  79
  80 ALTER TABLE `category`
  81 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
  82
  83 ALTER TABLE `contacts`
  84 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
  85
  86 ALTER TABLE `customer`
  87 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
  88
  89 ALTER TABLE `products`
  90 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
  91
  92 ALTER TABLE `orders`
  93 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
  94
  95 ALTER TABLE `ordered_items`
  96 MODIFY `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Primary key';
  97
  98 ALTER TABLE `category`
  99 ADD FOREIGN KEY (`parent`) REFERENCES `category` (`id`) ON DELETE SET NULL;
 100
 101 ALTER TABLE `customer`
 102 ADD FOREIGN KEY (`customer_contact_id`) REFERENCES `contacts` (`id`) ON DELETE CASCADE;
 103
 104 ALTER TABLE `products`
 105 ADD FOREIGN KEY (`category`) REFERENCES `category` (`id`) ON DELETE SET NULL;
 106
 107 ALTER TABLE `ordered_items`
 108 ADD CONSTRAINT `orders_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE;