· 5 years ago · Oct 13, 2020, 11:34 AM
1-- --------------------------------------------------------
2-- Host: 127.0.0.1
3-- Wersja serwera: 10.4.14-MariaDB - mariadb.org binary distribution
4-- Serwer OS: Win64
5-- HeidiSQL Wersja: 11.0.0.5919
6-- --------------------------------------------------------
7
8/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
9/*!40101 SET NAMES utf8 */;
10/*!50503 SET NAMES utf8mb4 */;
11/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
12/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
13
14
15-- Zrzut struktury bazy danych shop
16CREATE DATABASE IF NOT EXISTS `shop` /*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_polish_ci */;
17USE `shop`;
18
19-- Zrzut struktury tabela shop.categories
20CREATE TABLE IF NOT EXISTS `categories` (
21 `id` int(11) NOT NULL AUTO_INCREMENT,
22 `name` varchar(50) DEFAULT NULL,
23 `description` text DEFAULT NULL,
24 `image` varchar(50) DEFAULT NULL,
25 `created_at` timestamp NULL DEFAULT current_timestamp(),
26 `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
27 PRIMARY KEY (`id`)
28) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
29
30-- Eksport danych został odznaczony.
31
32-- Zrzut struktury tabela shop.orders
33CREATE TABLE IF NOT EXISTS `orders` (
34 `id` int(11) NOT NULL AUTO_INCREMENT,
35 `user_id` int(11) NOT NULL DEFAULT 0,
36 `street` varchar(25) DEFAULT NULL,
37 `city` varchar(15) DEFAULT NULL,
38 `postcode` varchar(15) DEFAULT NULL,
39 `province` varchar(15) DEFAULT NULL,
40 `country` varchar(15) DEFAULT NULL,
41 `mail` varchar(50) DEFAULT NULL,
42 `phone_number` int(9) DEFAULT NULL,
43 `created_at` timestamp NULL DEFAULT current_timestamp(),
44 `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
45 PRIMARY KEY (`id`),
46 KEY `user_id` (`user_id`),
47 CONSTRAINT `orders-user.FK` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
48) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
49
50-- Eksport danych został odznaczony.
51
52-- Zrzut struktury tabela shop.order_products
53CREATE TABLE IF NOT EXISTS `order_products` (
54 `id` int(11) NOT NULL AUTO_INCREMENT,
55 `order_id` int(11) DEFAULT NULL,
56 `product_id` int(11) DEFAULT NULL,
57 `unit_price` int(11) DEFAULT 0,
58 `quantity` int(11) DEFAULT 0,
59 `created_at` timestamp NULL DEFAULT current_timestamp(),
60 `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
61 PRIMARY KEY (`id`),
62 KEY `order_id` (`order_id`),
63 KEY `product_id` (`product_id`),
64 CONSTRAINT `order_products-order.FK` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
65 CONSTRAINT `order_products-product.FK` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
66) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
67
68-- Eksport danych został odznaczony.
69
70-- Zrzut struktury tabela shop.products
71CREATE TABLE IF NOT EXISTS `products` (
72 `id` int(11) NOT NULL AUTO_INCREMENT,
73 `category_id` int(11) DEFAULT 0,
74 `name` varchar(50) DEFAULT NULL,
75 `description` text DEFAULT NULL,
76 `unit_price` double DEFAULT NULL,
77 `on_stock` int(11) DEFAULT 0,
78 `on_order` int(11) DEFAULT 0,
79 `created_at` timestamp NULL DEFAULT current_timestamp(),
80 `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
81 PRIMARY KEY (`id`),
82 KEY `category_id` (`category_id`),
83 CONSTRAINT `product-categories.FK` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
84) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
85
86-- Eksport danych został odznaczony.
87
88-- Zrzut struktury tabela shop.product_comments
89CREATE TABLE IF NOT EXISTS `product_comments` (
90 `id` int(11) NOT NULL AUTO_INCREMENT,
91 `product_id` int(11) DEFAULT NULL,
92 `user_id` int(11) DEFAULT NULL,
93 `comment_id` int(11) DEFAULT NULL,
94 `body` text DEFAULT NULL,
95 `created_at` timestamp NULL DEFAULT current_timestamp(),
96 `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
97 PRIMARY KEY (`id`),
98 KEY `user_id` (`user_id`),
99 KEY `product_id` (`product_id`),
100 KEY `comment_id` (`comment_id`),
101 CONSTRAINT `product_comments-products.FK` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
102 CONSTRAINT `product_comments-products_comments.FK` FOREIGN KEY (`comment_id`) REFERENCES `product_comments` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
103 CONSTRAINT `product_comments-users.FK` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
104) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
105
106-- Eksport danych został odznaczony.
107
108-- Zrzut struktury tabela shop.product_pictures
109CREATE TABLE IF NOT EXISTS `product_pictures` (
110 `id` int(11) NOT NULL AUTO_INCREMENT,
111 `product_id` int(11) DEFAULT NULL,
112 `image` varchar(50) DEFAULT NULL,
113 `created_at` timestamp NULL DEFAULT current_timestamp(),
114 `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
115 PRIMARY KEY (`id`),
116 KEY `product_id` (`product_id`),
117 CONSTRAINT `product_pictures-products.FK` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
118) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
119
120-- Eksport danych został odznaczony.
121
122-- Zrzut struktury tabela shop.product_ratings
123CREATE TABLE IF NOT EXISTS `product_ratings` (
124 `id` int(11) NOT NULL AUTO_INCREMENT,
125 `product_id` int(11) DEFAULT NULL,
126 `user_id` int(11) DEFAULT NULL,
127 `rate` int(1) DEFAULT NULL,
128 `created_at` timestamp NULL DEFAULT current_timestamp(),
129 `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
130 PRIMARY KEY (`id`),
131 KEY `product_id` (`product_id`),
132 KEY `user_id` (`user_id`),
133 CONSTRAINT `product_ratings-products.FK` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
134 CONSTRAINT `product_ratings-users.FK` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
135) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
136
137-- Eksport danych został odznaczony.
138
139-- Zrzut struktury tabela shop.roles
140CREATE TABLE IF NOT EXISTS `roles` (
141 `id` int(11) NOT NULL AUTO_INCREMENT,
142 `name` varchar(50) DEFAULT NULL,
143 `created_at` timestamp NULL DEFAULT current_timestamp(),
144 `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
145 PRIMARY KEY (`id`)
146) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
147
148-- Eksport danych został odznaczony.
149
150-- Zrzut struktury tabela shop.users
151CREATE TABLE IF NOT EXISTS `users` (
152 `id` int(11) NOT NULL AUTO_INCREMENT,
153 `name` varchar(50) DEFAULT NULL,
154 `password` varchar(50) DEFAULT NULL,
155 `mail` varchar(50) DEFAULT NULL,
156 `active` int(1) NOT NULL DEFAULT 0,
157 `created_at` timestamp NULL DEFAULT current_timestamp(),
158 `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
159 PRIMARY KEY (`id`)
160) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
161
162-- Eksport danych został odznaczony.
163
164-- Zrzut struktury tabela shop.user_details
165CREATE TABLE IF NOT EXISTS `user_details` (
166 `id` int(11) NOT NULL AUTO_INCREMENT,
167 `user_id` int(11) DEFAULT NULL,
168 `street` varchar(25) DEFAULT NULL,
169 `city` varchar(15) DEFAULT NULL,
170 `postcode` varchar(15) DEFAULT NULL,
171 `province` varchar(15) DEFAULT NULL,
172 `country` varchar(15) DEFAULT NULL,
173 `mail` varchar(50) DEFAULT NULL,
174 `phone_number` int(9) DEFAULT NULL,
175 `created_at` timestamp NULL DEFAULT current_timestamp(),
176 `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
177 PRIMARY KEY (`id`),
178 KEY `user_id` (`user_id`),
179 CONSTRAINT `user_order_details-users.FK` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
180) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
181
182-- Eksport danych został odznaczony.
183
184-- Zrzut struktury tabela shop.user_roles
185CREATE TABLE IF NOT EXISTS `user_roles` (
186 `id` int(11) NOT NULL AUTO_INCREMENT,
187 `user_id` int(11) DEFAULT NULL,
188 `role_id` int(11) DEFAULT NULL,
189 `created_at` timestamp NULL DEFAULT current_timestamp(),
190 `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
191 PRIMARY KEY (`id`),
192 KEY `user_roles-roles.FK` (`role_id`),
193 KEY `user_roles-users.FK` (`user_id`),
194 CONSTRAINT `user_roles-roles.FK` FOREIGN KEY (`role_id`) REFERENCES `roles` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION,
195 CONSTRAINT `user_roles-users.FK` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
196) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
197
198-- Eksport danych został odznaczony.
199
200/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
201/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
202/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
203