60 lines
3.9 KiB
SQL
60 lines
3.9 KiB
SQL
CREATE TABLE `product` (
|
|
`product_id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
`name` CHAR(50) NOT NULL COLLATE 'utf8mb4_general_ci',
|
|
`description` TEXT(65535) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
|
|
`type` ENUM('subscription','in-app-currency') NOT NULL DEFAULT 'subscription' COLLATE 'utf8mb4_general_ci',
|
|
`valid_from` DATE NULL DEFAULT NULL,
|
|
`valid_to` DATE NULL DEFAULT NULL,
|
|
PRIMARY KEY (`product_id`) USING BTREE
|
|
)
|
|
COLLATE='utf8mb4_general_ci'
|
|
ENGINE=InnoDB
|
|
;
|
|
|
|
INSERT INTO `product` (`product_id`, `name`, `description`, `type`, `valid_from`, `valid_to`) VALUES (1, 'Subscription A', '700 Ft', 'subscription', '2020-11-01', NULL);
|
|
INSERT INTO `product` (`product_id`, `name`, `description`, `type`, `valid_from`, `valid_to`) VALUES (2, 'Subscription B', '1000 Ft', 'subscription', '2020-11-01', NULL);
|
|
INSERT INTO `product` (`product_id`, `name`, `description`, `type`, `valid_from`, `valid_to`) VALUES (3, 'Subscription C', '1500 Ft', 'subscription', '2020-11-01', NULL);
|
|
INSERT INTO `product` (`product_id`, `name`, `description`, `type`, `valid_from`, `valid_to`) VALUES (4, 'Subscription D', '2000 Ft', 'subscription', '2020-11-01', NULL);
|
|
INSERT INTO `product` (`product_id`, `name`, `description`, `type`, `valid_from`, `valid_to`) VALUES (5, 'Subscription E', '3000 Ft', 'subscription', '2020-11-01', NULL);
|
|
INSERT INTO `product` (`product_id`, `name`, `description`, `type`, `valid_from`, `valid_to`) VALUES (6, 'Subscription F', '5000 Ft', 'subscription', '2020-11-01', NULL);
|
|
INSERT INTO `product` (`product_id`, `name`, `description`, `type`, `valid_from`, `valid_to`) VALUES (7, 'Subscription A Yearly', '7000 Ft', 'subscription', '2020-11-01', NULL);
|
|
INSERT INTO `product` (`product_id`, `name`, `description`, `type`, `valid_from`, `valid_to`) VALUES (8, 'Subscription B Yearly', '10000', 'subscription', '2020-11-04', NULL);
|
|
INSERT INTO `product` (`product_id`, `name`, `description`, `type`, `valid_from`, `valid_to`) VALUES (9, 'Subscription C Yearly', '15000', 'subscription', '2020-11-04', NULL);
|
|
INSERT INTO `product` (`product_id`, `name`, `description`, `type`, `valid_from`, `valid_to`) VALUES (10, 'Subscription D Yearly', '20000', 'subscription', '2020-11-04', NULL);
|
|
INSERT INTO `product` (`product_id`, `name`, `description`, `type`, `valid_from`, `valid_to`) VALUES (11, 'Subscription E Yearly', '30000', 'subscription', '2020-11-04', NULL);
|
|
INSERT INTO `product` (`product_id`, `name`, `description`, `type`, `valid_from`, `valid_to`) VALUES (12, 'Subscription F Yearly', '50000', 'subscription', '2020-11-04', NULL);
|
|
|
|
CREATE TABLE `product_test` (
|
|
`product_test_id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
`product_id` INT(11) NULL DEFAULT '0',
|
|
`customer_id` INT(11) NULL DEFAULT '0',
|
|
`date_view` DATETIME NULL DEFAULT NULL,
|
|
`purchase_click` TINYINT(4) NULL DEFAULT '0',
|
|
PRIMARY KEY (`product_test_id`) USING BTREE,
|
|
INDEX `customer_id` (`customer_id`) USING BTREE
|
|
)
|
|
COLLATE='utf8mb4_general_ci'
|
|
ENGINE=InnoDB
|
|
;
|
|
|
|
CREATE TABLE `purchase` (
|
|
`purchase_id` INT(11) NOT NULL AUTO_INCREMENT,
|
|
`customer_id` INT(11) NOT NULL DEFAULT '0',
|
|
`product_id` INT(11) NOT NULL DEFAULT '0',
|
|
`date_add` DATETIME NULL DEFAULT NULL,
|
|
`purchase_sum` DOUBLE(22,0) NULL DEFAULT NULL,
|
|
`currency` CHAR(3) NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
|
|
PRIMARY KEY (`purchase_id`) USING BTREE,
|
|
INDEX `customer_id` (`customer_id`) USING BTREE
|
|
)
|
|
COLLATE='utf8mb4_general_ci'
|
|
ENGINE=InnoDB
|
|
;
|
|
|
|
|
|
INSERT INTO `purchase` (`purchase_id`, `customer_id`, `product_id`, `date_add`, `purchase_sum`, `currency`) VALUES (1, 62, 1, '2020-10-03 00:00:00', 1000, 'HUF');
|
|
INSERT INTO `purchase` (`purchase_id`, `customer_id`, `product_id`, `date_add`, `purchase_sum`, `currency`) VALUES (2, 62, 1, '2020-11-05 15:45:08', 1000, 'HUF');
|
|
INSERT INTO `purchase` (`purchase_id`, `customer_id`, `product_id`, `date_add`, `purchase_sum`, `currency`) VALUES (3, 2, 1, '2020-11-05 15:45:30', 1000, 'HUF');
|
|
|
|
|
|
UPDATE configuration set config_value = "1.0.11", date_change=CURRENT_DATE WHERE config_key = "db_version"; |