· 4 years ago · May 25, 2021, 09:24 AM
1-- --------------------------------------------------------
2-- Host: 127.0.0.1
3-- Versione server: 8.0.20 - MySQL Community Server - GPL
4-- S.O. server: Linux
5-- HeidiSQL Versione: 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-- Dump della struttura del database befloral
16CREATE DATABASE IF NOT EXISTS `befloral` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */;
17USE `befloral`;
18
19-- Dump della struttura di tabella befloral.migrations
20CREATE TABLE `migrations` (
21 `id` bigint NOT NULL AUTO_INCREMENT,
22 `name` varchar(255) NOT NULL DEFAULT '0',
23 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
24 PRIMARY KEY (`id`)
25);
26
27-- Dump della struttura di tabella befloral.products
28CREATE TABLE `products` (
29 `id` bigint NOT NULL AUTO_INCREMENT,
30 `name` varchar(128) NOT NULL,
31 `description` text NOT NULL,
32 `shortDescription` varchar(45) DEFAULT NULL,
33 `metaDescription` varchar(45) DEFAULT NULL,
34 `metaKeyword` varchar(45) DEFAULT NULL,
35 `price` double NOT NULL,
36 `weight` double NOT NULL DEFAULT 255,
37 `available` tinyint NOT NULL DEFAULT 0,
38 `discount` double NOT NULL DEFAULT 0,
39 `onSale` int NOT NULL DEFAULT 0,
40 `quantity` int NOT NULL DEFAULT 0,
41 PRIMARY KEY (`id`)
42);
43
44
45-- Dump dei dati della tabella befloral.migrations: 1 row
46INSERT INTO `migrations` (`name`) VALUES
47 ('2021_04_05_12_01_05_create_products_table');
48
49-- Dump dei dati della tabella befloral.products: 4 rows
50INSERT INTO `products` (`name`, `description`, `shortDescription`, `metaDescription`, `metaKeyword`, `price`, `weight`, `available`, `discount`, `onSale`, `quantity`) VALUES
51 ('Bouquet di Anturium', 'Bouquet di Anturium e Azalee', 'Anturium', 'bouquet_anturium', 'anturium', 20.33, 1.2, 1, 12, 1, 10),
52 ('Bouquet di Rose', 'Bouquet di Rose Rosse bulgare', 'Boquette Rose', 'bouquet_rose', 'boquette', 19.3, 1.6, 1, 0, 1, 9),
53 ('Margherita', 'Margherita bianca con petali rossi floreali', 'Margerita', 'margerita', 'margherita', 8.5, 0.5, 1, 0, 1, 120),
54 ('Tulipano Giallo', 'Tulipano giallo himalaiano', 'Tulipano', 'tulipano', 'tulipano', 9.25, 0.3, 1, 0, 1, 50),
55 ('Orchidea Gialla', 'Orchidea gialla in vaso', 'Orchidea', 'orchidea', 'orchidea', 29.90, 2.3, 1, 0, 0, 50),
56 ('Orchidea Rosa', 'Orchidea rosa in vaso', 'Orchidea', 'orchidea', 'orchidea', 29.90, 2.3, 1, 0, 0, 50),
57 ('Orchidea Verde', 'Orchidea verde in vaso', 'Orchidea', 'orchidea', 'orchidea', 29.90, 2.3, 1, 0, 0, 50),
58 ('Rosa rossa', 'Rosa rossa con gambo di 80cm', 'Rosa rossa', 'rosa', 'rosa', 3.90, 0.5, 1, 0, 0, 50),
59 ('Bouquet di Gerbera', 'Bouquet di Gerbera di colori misti', 'Bouquet Gerbera', 'bouquet_gerbera', 'gerbera', 20.90, 0.5, 1, 0, 0, 50),
60 ('Vaso Terra Cotta', 'Vaso di terra cotta rosso', 'Vaso', 'vaso', 'varo', 12.4, 0.5, 1, 0, 1, 12);
61
62/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
63/*!40014 SET FOREIGN_KEY_CHECKS=IF(@OLD_FOREIGN_KEY_CHECKS IS NULL, 1, @OLD_FOREIGN_KEY_CHECKS) */;
64/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
65USE `befloral`;
66
67INSERT INTO `migrations` (`name`) VALUES
68 ('2021_05_12_13_12_05_create_users_table');
69
70CREATE TABLE `users` (
71 `id` bigint NOT NULL AUTO_INCREMENT,
72 `username` varchar(45) NOT NULL,
73 `password` varchar(45) NOT NULL,
74 `role` varchar(45) NOT NULL,
75 `active` tinyint NOT NULL DEFAULT 0,
76 PRIMARY KEY (`id`)
77);
78
79INSERT INTO `users` (`username`, `password`, `role`, `active`) VALUES
80 ('admin', 'admin', 'administrator', 0),
81 ('employee', 'employee', 'employee', 0),
82 ('customer', 'customer', 'customer', 0);
83
84USE `befloral`;
85
86INSERT INTO `migrations` (`name`) VALUES
87 ('2021_05_14_09_55_14_create_customers_table');
88
89CREATE TABLE `customers` (
90 `id` int NOT NULL AUTO_INCREMENT,
91 `uid` bigint NOT NULL,
92 `firstName` varchar(45) NOT NULL,
93 `lastName` varchar(45) NOT NULL,
94 `gender` varchar(20) DEFAULT NULL,
95 `subscription` tinyint DEFAULT NULL,
96 `birthday` date NOT NULL,
97 PRIMARY KEY (`id`),
98 CONSTRAINT `users` FOREIGN KEY (`uid`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
99);
100
101ALTER TABLE `users`
102 CHANGE COLUMN `username` `email` VARCHAR(45) NOT NULL AFTER `id`,
103 ADD UNIQUE INDEX `email` (`email`);
104
105 USE `befloral`;
106
107INSERT INTO `migrations` (`name`) VALUES
108 ('2021_05_15_21_09_50_create_orders_table');
109
110CREATE TABLE `orders` (
111 `id` bigint AUTO_INCREMENT,
112 `uid` bigint NOT NULL,
113 `destination` varchar(255) NOT NULL,
114 `totalProducts` int NOT NULL,
115 `totalPaid` double NOT NULL,
116 `trackNumber` varchar(45) NULL,
117 `gift` tinyint DEFAULT 0,
118 `giftMessage` varchar(255) NULL,
119 PRIMARY KEY (`id`),
120 CONSTRAINT `fk_user` FOREIGN KEY (`uid`) REFERENCES `users` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
121);
122
123CREATE TABLE `order_items` (
124 `id` bigint AUTO_INCREMENT,
125 `oid` bigint NOT NULL,
126 `name` varchar(128) NOT NULL,
127 `description` text NOT NULL,
128 `shortDescription` varchar(45) DEFAULT NULL,
129 `price` double NOT NULL,
130 `weight` double NOT NULL DEFAULT 255,
131 `discount` double NOT NULL DEFAULT 0,
132 `quantity` int NOT NULL DEFAULT 0,
133 PRIMARY KEY (`id`),
134 CONSTRAINT `fk_order` FOREIGN KEY (`oid`) REFERENCES `orders` (`id`) ON DELETE RESTRICT ON UPDATE RESTRICT
135);
136USE `befloral`;
137
138INSERT INTO `migrations` (`name`) VALUES
139 ('2021_05_19_20_39_40_edit_users_table');
140
141UPDATE `befloral`.`users` SET `email`='admin@befloral.com', active = 1 WHERE `id`=1;
142UPDATE `befloral`.`users` SET `email`='customer@befloral.com', active = 1 WHERE `id`=3;
143UPDATE `befloral`.`users` SET `email`='employee@befloral.com', active = 1 WHERE `id`=2;
144
145USE `befloral`;
146
147INSERT INTO `migrations` (`name`) VALUES
148 ('2021_05_21_10_37_add_createdat_to_orders_table');
149
150ALTER TABLE orders
151ADD COLUMN `createdAt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
152 AFTER `giftMessage`;
153
154 USE `befloral`;
155
156INSERT INTO `migrations` (`name`) VALUES
157 ('2021_05_21_14_21_20_create_addresses_table');
158
159ALTER TABLE `customers`
160 CHANGE COLUMN `id` `id` BIGINT NOT NULL FIRST;
161
162CREATE TABLE `addresses` (
163 `id` bigint AUTO_INCREMENT,
164 `cid` bigint NOT NULL,
165 `firstName` varchar(45) NOT NULL,
166 `lastName` varchar(45) NOT NULL,
167 `address` varchar(255) NOT NULL,
168 `postalCode` varchar(5) NOT NULL,
169 `city` varchar(60) NOT NULL,
170 `province` varchar(2) NOT NULL,
171 `phone` varchar(20) NULL,
172 `info` varchar(255) NULL,
173 `alias` varchar(45) NULL,
174 `preferred`tinyint NULL DEFAULT NULL,
175 PRIMARY KEY (`id`),
176 UNIQUE INDEX `cid_preferred` (`cid`, `preferred`),
177 CONSTRAINT `FK__customers` FOREIGN KEY (`cid`) REFERENCES `customers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
178);
179
180
181USE `befloral`;
182
183INSERT INTO `migrations` (`name`) VALUES
184 ('2021_05_21_17_11_add_status_to_orders_table');
185
186ALTER TABLE orders
187ADD COLUMN `status` VARCHAR(255) NULL
188 AFTER `trackNumber`;
189
190USE `befloral`;
191
192ALTER TABLE `addresses`
193 DROP FOREIGN KEY `FK__customers`;
194
195DROP TABLE `customers`;
196
197ALTER TABLE users
198ADD COLUMN `firstName` varchar(45) NOT NULL AFTER `id`,
199ADD COLUMN `lastName` varchar(45) NOT NULL AFTER `firstName`,
200ADD COLUMN `gender` varchar(20) NOT NULL DEFAULT "Undefined" AFTER `lastName`,
201ADD COLUMN `subscription` tinyint NOT NULL DEFAULT 0 AFTER `gender`,
202ADD COLUMN `birthday` date NOT NULL DEFAULT '1900-01-01' after `subscription`;
203
204ALTER TABLE `addresses`
205 CHANGE COLUMN `cid` `uid` BIGINT(19) NOT NULL AFTER `id`,
206 DROP INDEX `cid_preferred`,
207 ADD UNIQUE INDEX `cid_preferred` (`uid`, `preferred`) USING BTREE;
208
209ALTER TABLE `addresses`
210 ADD CONSTRAINT `FK_addresses_users` FOREIGN KEY (`uid`) REFERENCES `users` (`id`) ON UPDATE CASCADE ON DELETE CASCADE;
211
212
213INSERT INTO `migrations` (`name`) VALUES
214 ('2021_05_22_14_10_01_remove_customers_table');