x7W5WK9a

1DROP TABLE IF EXISTS `brands`;
2CREATE TABLE IF NOT EXISTS `brands` (
3    `id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
4    `name` varchar(100) NOT NULL,
5    PRIMARY KEY (`id`),
6    UNIQUE KEY `name` (`name`)
7) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
8
9DROP TABLE IF EXISTS `categories`;
10CREATE TABLE IF NOT EXISTS `categories` (
11    `id` tinyint(3) unsigned NOT NULL AUTO_INCREMENT,
12    `name` varchar(100) NOT NULL,
13    `parent_id` tinyint(3) unsigned NOT NULL DEFAULT '0',
14    `comment` text COMMENT 'USP for a specific category. Would be displayed on the webpages',
15    PRIMARY KEY (`id`),
16    UNIQUE KEY `name` (`name`),
17    KEY `parent_id` (`parent_id`)
18) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='List of all categories'
19DROP TABLE IF EXISTS `brand_category`;
20CREATE TABLE IF NOT EXISTS `brand_category` (
21    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
22    `brand_id` smallint(5) unsigned NOT NULL,
23    `category_id` tinyint(3) unsigned NOT NULL,
24    `added` date NOT NULL,
25    PRIMARY KEY (`id`),
26    KEY `brand_name` (`brand_id`),
27    KEY `brand_category_name` (`category_id`),
28    CONSTRAINT `brand_category_name` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON UPDATE CASCADE,
29    CONSTRAINT `brand_name` FOREIGN KEY (`brand_id`) REFERENCES `brands` (`id`) ON UPDATE CASCADE
30) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Relations between brands and category';
31
32
33DROP TABLE IF EXISTS `prices`;
34CREATE TABLE IF NOT EXISTS `prices` (
35    `product_id` int(10) unsigned NOT NULL,
36    `price` float unsigned NOT NULL,
37    `date` date NOT NULL,
38    UNIQUE KEY `product_id_date` (`product_id`,`date`),
39    CONSTRAINT `price_product_id` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON UPDATE CASCADE
40) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Prices for products set on specific dates';
41
42DROP TABLE IF EXISTS `products`;
43CREATE TABLE IF NOT EXISTS `products` (
44    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
45    `brand_category_id` int(10) unsigned NOT NULL,
46    `model_name` varchar(255) NOT NULL,
47    `video_url` varchar(150) DEFAULT NULL,
48    `display_name` text NOT NULL,
49    `description` text DEFAULT NULL,
50    `retail_price` float unsigned NOT NULL,
51    `current_price` float unsigned NOT NULL DEFAULT '0',
52    `launch_date` date DEFAULT NULL,
53    `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
54    PRIMARY KEY (`id`),
55    UNIQUE KEY `brand_category_id_model_name` (`brand_category_id`,`model_name`),
56    CONSTRAINT `product_brand_id` FOREIGN KEY (`brand_category_id`) REFERENCES `brand_category` (`id`) ON UPDATE CASCADE
57) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='List of all products available';
58	
59DELIMITER //
60
61    CREATE PROCEDURE `product_entry`(
62        IN `category_name` VARCHAR(100),
63        IN `brand_name` VARCHAR(100),
64        IN `model_name` VARCHAR(255),
65        IN `video_url` VARCHAR(150),
66        IN `display_name` text,
67        IN `description` text,
68        IN `retail_price` float,
69        OUT `category_id` tinyint(3),
70        OUT `product_id` int(10)
71    )
72    BEGIN
73        DECLARE brand_id smallint(5);
74        DECLARE brand_category_id int(10);
75
76        DECLARE EXIT HANDLER FOR SQLEXCEPTION
77        BEGIN
78            ROLLBACK;
79        END;
80
81        START TRANSACTION;
82
83        INSERT INTO categories (name)
84        VALUES (category_name)
85        ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id);
86
87        SET category_id = LAST_INSERT_ID();
88
89        INSERT INTO brands (name)
90        VALUES (brand_name)
91        ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id);
92
93        SET brand_id = LAST_INSERT_ID();
94
95        INSERT INTO brand_category (brand_id, category_id, added)
96        VALUES (brand_id, category_id, CURDATE())
97        ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id);
98
99        SET brand_category_id = LAST_INSERT_ID();
100
101        INSERT INTO product (brand_category_id, model_name, video_url, display_name, description, retail_price, launch_date)
102        VALUES (brand_category_id, model_name, video_url, display_name, description, retail_price, CURDATE())
103        ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id);
104
105        SET product_id = LAST_INSERT_ID();
106
107        INSERT INTO prices (product_id, price, date)
108        VALUES (product_id, retail_price, CURDATE());
109
110        COMMIT;
111
112END//