· 6 years ago · Jun 18, 2019, 02:10 PM
1-- MySQL Workbench Synchronization
2-- Generated: 2019-06-18 21:04
3-- Model: New Model
4-- Version: 1.0
5-- Project: Name of the project
6-- Author: Tuxer
7
8SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
9SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
10SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
11
12ALTER TABLE `kommu_dev`.`community`
13CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;
14
15ALTER TABLE `kommu_dev`.`store`
16CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;
17
18ALTER TABLE `kommu_dev`.`member`
19CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;
20
21ALTER TABLE `kommu_dev`.`product`
22CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;
23
24ALTER TABLE `kommu_dev`.`banner`
25CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;
26
27ALTER TABLE `kommu_dev`.`invoice`
28CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;
29
30ALTER TABLE `kommu_dev`.`product_price_log`
31CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;
32
33ALTER TABLE `kommu_dev`.`session`
34CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ;
35
36ALTER TABLE `kommu_dev`.`otp`
37CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;
38
39ALTER TABLE `kommu_dev`.`notification`
40CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;
41
42ALTER TABLE `kommu_dev`.`member_device`
43CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;
44
45ALTER TABLE `kommu_dev`.`news`
46CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;
47
48ALTER TABLE `kommu_dev`.`media`
49CHANGE COLUMN `content_type` `content_type` ENUM('image/jpeg', 'image/png') NULL DEFAULT NULL ;
50
51ALTER TABLE `kommu_dev`.`bank_account`
52CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;
53
54ALTER TABLE `kommu_dev`.`sales_order`
55CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ,
56CHANGE COLUMN `total_price` `total_amount` DOUBLE UNSIGNED NOT NULL DEFAULT 0 ,
57CHANGE COLUMN `payment_status` `payment_status` ENUM('waiting', 'verification', 'verification_failed', 'verified', 'payout', 'refund') NOT NULL DEFAULT 'waiting' COMMENT 'created: order dibuat/checkout\nverification: buyer meminta verifikasi\nverified: pembayaran berhasil diverifikasi\nprocessing: order diproses\nsending: dikirim\nsent: terkirim\nreceived: barang diterima (auto no resi)\nconfirmed: barang diterima\npayout: pembayaran dikirim ke penjual\n\nrefund: pembayaran dikembalikan ke pembeli\nexpired: kadaluarsa' ;
58
59ALTER TABLE `kommu_dev`.`sales_order_item`
60ADD COLUMN `total_item_price` DOUBLE UNSIGNED NOT NULL DEFAULT 0 AFTER `item_quantity`,
61ADD COLUMN `item_weight` INT(10) UNSIGNED NOT NULL DEFAULT 0 AFTER `total_item_price`,
62ADD COLUMN `total_item_weight` INT(10) UNSIGNED NOT NULL DEFAULT 0 AFTER `item_weight`,
63CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ,
64CHANGE COLUMN `price` `item_price` DOUBLE UNSIGNED NOT NULL DEFAULT 0 ,
65CHANGE COLUMN `quantity` `item_quantity` INT(10) UNSIGNED NOT NULL DEFAULT 1 ;
66
67ALTER TABLE `kommu_dev`.`sales_order_log`
68CHANGE COLUMN `status` `status` ENUM('created', 'checkout', 'verification', 'verification_failed', 'verified', 'processing', 'sending', 'sent', 'received', 'confirmed', 'payout', 'refund', 'expired') NOT NULL DEFAULT 'created' COMMENT 'created: order dibuat/checkout\nverification: buyer meminta verifikasi\nverified: pembayaran berhasil diverifikasi\nprocessing: order diproses\nsending: dikirim\nsent: terkirim\nreceived: barang diterima (auto no resi)\nconfirmed: barang diterima\npayout: pembayaran dikirim ke penjual\n\nrefund: pembayaran dikembalikan ke pembeli\nexpired: kadaluarsa' ;
69
70ALTER TABLE `kommu_dev`.`sales_order_store`
71ADD COLUMN `total_weight` INT(10) UNSIGNED NOT NULL DEFAULT 0 AFTER `shipping_status`,
72ADD COLUMN `total_cost` DOUBLE UNSIGNED NOT NULL DEFAULT 0 AFTER `total_price`;
73
74ALTER TABLE `kommu_dev`.`product_stock`
75CHANGE COLUMN `updated_at` `updated_at` TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP ;
76
77ALTER TABLE `kommu_dev`.`city`
78ADD CONSTRAINT `fk_city_province1`
79 FOREIGN KEY (`province_id`)
80 REFERENCES `kommu_dev`.`province` (`id`)
81 ON DELETE NO ACTION
82 ON UPDATE NO ACTION;
83
84ALTER TABLE `kommu_dev`.`member`
85ADD CONSTRAINT `fk_member_role1`
86 FOREIGN KEY (`role_id`)
87 REFERENCES `kommu_dev`.`role` (`id`)
88 ON DELETE NO ACTION
89 ON UPDATE NO ACTION;
90
91ALTER TABLE `kommu_dev`.`product`
92ADD CONSTRAINT `fk_product_product_category1`
93 FOREIGN KEY (`product_category_id`)
94 REFERENCES `kommu_dev`.`product_category` (`id`)
95 ON DELETE NO ACTION
96 ON UPDATE NO ACTION;
97
98ALTER TABLE `kommu_dev`.`product_price_log`
99ADD CONSTRAINT `fk_product_price_log_product1`
100 FOREIGN KEY (`product_id`)
101 REFERENCES `kommu_dev`.`product` (`id`)
102 ON DELETE NO ACTION
103 ON UPDATE NO ACTION;
104
105ALTER TABLE `kommu_dev`.`member_address`
106ADD CONSTRAINT `fk_member_address_city1`
107 FOREIGN KEY (`city_id`)
108 REFERENCES `kommu_dev`.`city` (`id`)
109 ON DELETE NO ACTION
110 ON UPDATE NO ACTION,
111ADD CONSTRAINT `fk_member_address_subdistrict1`
112 FOREIGN KEY (`subdistrict_id`)
113 REFERENCES `kommu_dev`.`subdistrict` (`id`)
114 ON DELETE NO ACTION
115 ON UPDATE NO ACTION;
116
117ALTER TABLE `kommu_dev`.`role_permission`
118ADD CONSTRAINT `fk_role_permission_role1`
119 FOREIGN KEY (`role_id`)
120 REFERENCES `kommu_dev`.`role` (`id`)
121 ON DELETE NO ACTION
122 ON UPDATE NO ACTION,
123ADD CONSTRAINT `fk_role_permission_permission1`
124 FOREIGN KEY (`permission_id`)
125 REFERENCES `kommu_dev`.`permission` (`id`)
126 ON DELETE NO ACTION
127 ON UPDATE NO ACTION;
128
129ALTER TABLE `kommu_dev`.`store_member`
130ADD CONSTRAINT `fk_store_member_role1`
131 FOREIGN KEY (`role_id`)
132 REFERENCES `kommu_dev`.`role` (`id`)
133 ON DELETE NO ACTION
134 ON UPDATE NO ACTION,
135ADD CONSTRAINT `fk_store_member_member1`
136 FOREIGN KEY (`member_id`)
137 REFERENCES `kommu_dev`.`member` (`id`)
138 ON DELETE NO ACTION
139 ON UPDATE NO ACTION;
140
141ALTER TABLE `kommu_dev`.`notification`
142ADD CONSTRAINT `fk_notification_member_device1`
143 FOREIGN KEY (`device_id`)
144 REFERENCES `kommu_dev`.`member_device` (`id`)
145 ON DELETE NO ACTION
146 ON UPDATE NO ACTION;
147
148ALTER TABLE `kommu_dev`.`news`
149ADD CONSTRAINT `fk_news_community1`
150 FOREIGN KEY (`community_id`)
151 REFERENCES `kommu_dev`.`community` (`id`)
152 ON DELETE NO ACTION
153 ON UPDATE NO ACTION,
154ADD CONSTRAINT `fk_news_member1`
155 FOREIGN KEY (`author_id`)
156 REFERENCES `kommu_dev`.`member` (`id`)
157 ON DELETE NO ACTION
158 ON UPDATE NO ACTION;
159
160ALTER TABLE `kommu_dev`.`bank_account`
161ADD CONSTRAINT `fk_bank_account_community1`
162 FOREIGN KEY (`community_id`)
163 REFERENCES `kommu_dev`.`community` (`id`)
164 ON DELETE NO ACTION
165 ON UPDATE NO ACTION;
166
167ALTER TABLE `kommu_dev`.`subdistrict`
168ADD CONSTRAINT `fk_subdistrict_city1`
169 FOREIGN KEY (`city_id`)
170 REFERENCES `kommu_dev`.`city` (`id`)
171 ON DELETE NO ACTION
172 ON UPDATE NO ACTION;
173
174ALTER TABLE `kommu_dev`.`sales_order`
175ADD CONSTRAINT `fk_order_member2`
176 FOREIGN KEY (`buyer_id`)
177 REFERENCES `kommu_dev`.`member` (`id`)
178 ON DELETE NO ACTION
179 ON UPDATE NO ACTION,
180ADD CONSTRAINT `fk_sales_order_member1`
181 FOREIGN KEY (`payment_verification_by`)
182 REFERENCES `kommu_dev`.`member` (`id`)
183 ON DELETE NO ACTION
184 ON UPDATE NO ACTION,
185ADD CONSTRAINT `fk_sales_order_member2`
186 FOREIGN KEY (`buyer_id`)
187 REFERENCES `kommu_dev`.`member` (`id`)
188 ON DELETE NO ACTION
189 ON UPDATE NO ACTION,
190ADD CONSTRAINT `fk_sales_order_member3`
191 FOREIGN KEY (`payment_verification_by`)
192 REFERENCES `kommu_dev`.`member` (`id`)
193 ON DELETE NO ACTION
194 ON UPDATE NO ACTION;
195
196ALTER TABLE `kommu_dev`.`sales_order_item`
197ADD CONSTRAINT `fk_sales_order_item_product1`
198 FOREIGN KEY (`product_id`)
199 REFERENCES `kommu_dev`.`product` (`id`)
200 ON DELETE NO ACTION
201 ON UPDATE NO ACTION,
202ADD CONSTRAINT `fk_sales_order_item_sales_order1`
203 FOREIGN KEY (`sales_order_id`)
204 REFERENCES `kommu_dev`.`sales_order` (`id`)
205 ON DELETE NO ACTION
206 ON UPDATE NO ACTION,
207ADD CONSTRAINT `fk_sales_order_item_product2`
208 FOREIGN KEY (`product_id`)
209 REFERENCES `kommu_dev`.`product` (`id`)
210 ON DELETE NO ACTION
211 ON UPDATE NO ACTION;
212
213ALTER TABLE `kommu_dev`.`sales_order_log`
214ADD CONSTRAINT `fk_sales_order_log_sales_order1`
215 FOREIGN KEY (`sales_order_id`)
216 REFERENCES `kommu_dev`.`sales_order` (`id`)
217 ON DELETE NO ACTION
218 ON UPDATE NO ACTION;
219
220ALTER TABLE `kommu_dev`.`sales_order_store`
221ADD CONSTRAINT `fk_sales_order_store_sales_order1`
222 FOREIGN KEY (`sales_order_id`)
223 REFERENCES `kommu_dev`.`sales_order` (`id`)
224 ON DELETE NO ACTION
225 ON UPDATE NO ACTION,
226ADD CONSTRAINT `fk_sales_order_store_store1`
227 FOREIGN KEY (`store_id`)
228 REFERENCES `kommu_dev`.`store` (`id`)
229 ON DELETE NO ACTION
230 ON UPDATE NO ACTION,
231ADD CONSTRAINT `fk_sales_order_store_invoice1`
232 FOREIGN KEY (`invoice_id`)
233 REFERENCES `kommu_dev`.`invoice` (`id`)
234 ON DELETE NO ACTION
235 ON UPDATE NO ACTION,
236ADD CONSTRAINT `fk_sales_order_store_member_address1`
237 FOREIGN KEY (`member_address_id`)
238 REFERENCES `kommu_dev`.`member_address` (`id`)
239 ON DELETE NO ACTION
240 ON UPDATE NO ACTION;
241
242
243USE `kommu_dev`;
244DROP procedure IF EXISTS `kommu_dev`.`drop_fk`;
245
246DELIMITER $$
247USE `kommu_dev`$$
248CREATE PROCEDURE `drop_fk`(IN tableName VARCHAR(255))
249BEGIN
250 DECLARE done INT DEFAULT FALSE;
251 DECLARE dropCommand VARCHAR(255);
252 DECLARE dropCur CURSOR FOR
253 SELECT CONCAT('ALTER TABLE ', DATABASE(), '.', tableName,' DROP FOREIGN KEY ', `constraint_name`, ';')
254 FROM `information_schema`.`table_constraints`
255 WHERE `constraint_type` = 'FOREIGN KEY'
256 AND `table_name` = tableName
257 AND `table_schema` = DATABASE();
258
259 DEClARE CONTINUE HANDLER FOR NOT FOUND SET DONE = true;
260
261 OPEN dropCur;
262
263 readLoop: LOOP
264 FETCH dropCur INTO dropCommand;
265 IF done THEN
266 LEAVE readLoop;
267 END IF;
268
269 SET @sdropCommand = dropCommand;
270
271 PREPARE dropClientUpdateKeyStmt FROM @sdropCommand;
272
273 EXECUTE dropClientUpdateKeyStmt;
274
275 DEALLOCATE PREPARE dropClientUpdateKeyStmt;
276 END LOOP;
277
278 CLOSE dropCur;
279END$$
280
281DELIMITER ;
282
283USE `kommu_dev`;
284DROP procedure IF EXISTS `kommu_dev`.`optimize_db`;
285
286DELIMITER $$
287USE `kommu_dev`$$
288CREATE PROCEDURE `optimize_db`()
289BEGIN
290 DECLARE curTableFinish INT DEFAULT 0;
291 DECLARE tableName VARCHAR(255);
292
293 DECLARE curTable CURSOR FOR
294 SELECT `table_name` FROM `information_schema`.`tables` WHERE `table_schema` = DATABASE();
295
296 DECLARE CONTINUE HANDLER FOR NOT FOUND SET curTableFinish = 1;
297
298 OPEN curTable;
299
300 readLoop: LOOP
301 FETCH curTable INTO tableName;
302 IF curTableFinish = 1 THEN
303 LEAVE readLoop;
304 END IF;
305
306 CALL drop_fk(tableName);
307 END LOOP readLoop;
308 CLOSE curTable;
309END$$
310
311DELIMITER ;
312
313SET SQL_MODE=@OLD_SQL_MODE;
314SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
315SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;