p6UbfVAC

1SET NAMES utf8;
2SET time_zone = '+00:00';
3SET foreign_key_checks = 0;
4SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
5
6
7DROP TABLE IF EXISTS `user`;
8CREATE TABLE `user` (
9  `id` int NOT NULL AUTO_INCREMENT,
10  `name` varchar(50) NOT NULL UNIQUE,
11  `surname` varchar(50) NOT NULL,
12  `email` varchar(50) NOT NULL,
13  `phone` int NOT NULL,
14  `password` varchar(50) DEFAULT NULL,
15  `country` varchar(50) DEFAULT NULL,
16  `city` varchar(50) DEFAULT NULL,
17  `street` varchar(50) DEFAULT NULL,
18  `home_number` int DEFAULT NULL,
19  `postal` varchar(50) DEFAULT NULL,
20  PRIMARY KEY (`id`)
21) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_bin;
22
23
24DROP TABLE IF EXISTS `product`;
25CREATE TABLE `product` (
26  `id` int NOT NULL AUTO_INCREMENT,
27  `name` varchar(255) NOT NULL,
28  `price` int DEFAULT NULL,
29  `description` longtext NOT NULL,
30  `category` int DEFAULT NULL,
31  PRIMARY KEY (`id`)
32) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_bin;
33
34
35
36DROP TABLE IF EXISTS `sale`;
37CREATE TABLE `sale` (
38  `id` int NOT NULL AUTO_INCREMENT,
39  `product_id` int NOT NULL,
40  `price` int NOT NULL,
41  `start` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
42  `end` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
43  `amount` int NOT NULL,
44  PRIMARY KEY (`id`)
45) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_bin;
46
47
48
49
50DROP TABLE IF EXISTS `category`;
51CREATE TABLE `category` (
52  `id` int NOT NULL AUTO_INCREMENT,
53  `name` varchar(50) NOT NULL,
54  PRIMARY KEY (`id`)
55) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_bin;
56
57
58
59DROP TABLE IF EXISTS `purchase`;
60CREATE TABLE `purchase` (
61  `purchase_id` int NOT NULL AUTO_INCREMENT,
62  `user_id` int NOT NULL,
63  `product_id` int NOT NULL,
64  `delivery_address` varchar(50) NOT NULL,
65  `is_reviewed` int NOT NULL,
66  PRIMARY KEY (`purchase_id`)
67) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_bin;
68
69
70DROP TABLE IF EXISTS `purchase_detail`;
71CREATE TABLE `purchase_detail` (
72  `id` int NOT NULL AUTO_INCREMENT,
73  `purchase_id` int NOT NULL,
74  `product_id` int NOT NULL,
75  `date` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
76  `amount` int NOT NULL,
77  PRIMARY KEY (`id`)
78) ENGINE=InnoDB DEFAULT CHARSET=latin2 COLLATE=latin2_bin;
79
80
81
82ALTER TABLE `purchase` ADD CONSTRAINT `purchase_const` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`);
83ALTER TABLE `product` ADD CONSTRAINT `category_const` FOREIGN KEY (`category`) REFERENCES `category` (`id`);
84ALTER TABLE `purchase_detail` ADD CONSTRAINT `purchase_to_details` FOREIGN KEY (`purchase_id`) REFERENCES `purchase` (`purchase_id`);
85
86
87INSERT INTO `user` VALUES (2,'user','user','user@user.pl', '111111111','user','Pl', 'Cracov', 'Dluga',2, '12-345');
88INSERT INTO `user` VALUES (3,'test','test','test@test.pl', '111111111', 'test','Eng', 'Warsaw', 'Nowa',3,'12-345');
89INSERT INTO `user` VALUES (4,'admin','admin','admin@admin.pl', '111111111', 'admin','Poland','Cracov','Wielicka', 1,'23-444');
90INSERT INTO `user` VALUES (6,'name','surname','testowy@vp.pl', '111111111', '1234','Poland','Cracov', 'Wielicka', 2, '20-222');
91
92INSERT INTO `product` VALUES (16,'Polish', 300, 'Polish t-shirt',1);
93INSERT INTO `product` VALUES (17,'Belgian',200, 'Belgian t-shirt',1);
94INSERT INTO `product` VALUES (18,'French',200, 'Polish t-shirt',1);
95INSERT INTO `product` VALUES (19,'Argentinean', 400,'Argentinean t-shirt',2);
96INSERT INTO `category` VALUES (0,'Other');
97INSERT INTO `category` VALUES (1,'Europe');
98INSERT INTO `category` VALUES (2,'South America');