-
Notifications
You must be signed in to change notification settings - Fork 24
/
category.sql
134 lines (112 loc) · 7.25 KB
/
category.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
-- Enable `entity_id` column for catalog category entity
DELETE FROM catalog_category_entity WHERE row_id NOT IN (
SELECT tmp_category.row_id FROM (SELECT MAX(row_id) AS row_id FROM catalog_category_entity GROUP BY entity_id) tmp_category
);
ALTER TABLE `catalog_category_entity_datetime`
ADD COLUMN `entity_id` INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Entity ID';
ALTER TABLE `catalog_category_entity_decimal`
ADD COLUMN `entity_id` INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Entity ID';
ALTER TABLE `catalog_category_entity_int`
ADD COLUMN `entity_id` INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Entity ID';
ALTER TABLE `catalog_category_entity_text`
ADD COLUMN `entity_id` INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Entity ID';
ALTER TABLE `catalog_category_entity_varchar`
ADD COLUMN `entity_id` INT(10) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Entity ID';
-- Clean duplicates for catalog category entity
DELETE e
FROM `catalog_product_entity` e
LEFT OUTER JOIN (
SELECT MAX(`updated_in`) as `last_updated_in`, `entity_id`
FROM `catalog_product_entity`
GROUP BY `entity_id`
) AS p
ON e.`entity_id` = p.`entity_id` AND e.`updated_in` = p.`last_updated_in`
WHERE p.`last_updated_in` IS NULL;
-- Populate `entity_id` column for catalog category entity
UPDATE `catalog_category_entity_datetime` v INNER JOIN `catalog_category_entity` e ON v.`row_id` = e.`row_id`
SET v.`entity_id` = e.`entity_id`
WHERE 1;
UPDATE `catalog_category_entity_decimal` v INNER JOIN `catalog_category_entity` e ON v.`row_id` = e.`row_id`
SET v.`entity_id` = e.`entity_id`
WHERE 1;
UPDATE `catalog_category_entity_int` v INNER JOIN `catalog_category_entity` e ON v.`row_id` = e.`row_id`
SET v.`entity_id` = e.`entity_id`
WHERE 1;
UPDATE `catalog_category_entity_text` v INNER JOIN `catalog_category_entity` e ON v.`row_id` = e.`row_id`
SET v.`entity_id` = e.`entity_id`
WHERE 1;
UPDATE `catalog_category_entity_varchar` v INNER JOIN `catalog_category_entity` e ON v.`row_id` = e.`row_id`
SET v.`entity_id` = e.`entity_id`
WHERE 1;
-- -------------------------------------------------------------------
-- Delete entities which does not exists in catalog_category_entity --
-- -------------------------------------------------------------------
DELETE FROM `catalog_category_entity_datetime` WHERE row_id NOT IN (SELECT MAX(row_id) FROM catalog_category_entity GROUP BY entity_id) ORDER BY `entity_id` ASC;
DELETE FROM `catalog_category_entity_decimal` WHERE row_id NOT IN (SELECT MAX(row_id) FROM catalog_category_entity GROUP BY entity_id) ORDER BY `entity_id` ASC;
DELETE FROM `catalog_category_entity_int` WHERE row_id NOT IN (SELECT MAX(row_id) FROM catalog_category_entity GROUP BY entity_id) ORDER BY `entity_id` ASC;
DELETE FROM `catalog_category_entity_text` WHERE row_id NOT IN (SELECT MAX(row_id) FROM catalog_category_entity GROUP BY entity_id) ORDER BY `entity_id` ASC;
DELETE FROM `catalog_category_entity_varchar` WHERE row_id NOT IN (SELECT MAX(row_id) FROM catalog_category_entity GROUP BY entity_id) ORDER BY `entity_id` ASC;
-- ------------------------------------------------------------------
-- Update the `entity_id` relation link for catalog product entity --
-- ------------------------------------------------------------------
-- Datetime
ALTER TABLE `catalog_category_entity_datetime`
DROP FOREIGN KEY `CAT_CTGR_ENTT_DTIME_ROW_ID_CAT_CTGR_ENTT_ROW_ID`,
DROP INDEX `CATALOG_CATEGORY_ENTITY_DATETIME_ROW_ID_ATTRIBUTE_ID_STORE_ID`,
ADD CONSTRAINT `CATALOG_CATEGORY_ENTITY_DATETIME_ENTITY_ID_ATTRIBUTE_ID_STORE_ID` UNIQUE KEY (`entity_id`,`attribute_id`,`store_id`),
DROP COLUMN `row_id`;
-- Decimal
ALTER TABLE `catalog_category_entity_decimal`
DROP FOREIGN KEY `CAT_CTGR_ENTT_DEC_ROW_ID_CAT_CTGR_ENTT_ROW_ID`,
DROP INDEX `CATALOG_CATEGORY_ENTITY_DECIMAL_ROW_ID_ATTRIBUTE_ID_STORE_ID`,
ADD CONSTRAINT `CATALOG_CATEGORY_ENTITY_DECIMAL_ENTITY_ID_ATTRIBUTE_ID_STORE_ID` UNIQUE KEY (`entity_id`,`attribute_id`,`store_id`),
DROP COLUMN `row_id`;
-- Int
ALTER TABLE `catalog_category_entity_int`
DROP FOREIGN KEY `CAT_CTGR_ENTT_INT_ROW_ID_CAT_CTGR_ENTT_ROW_ID`,
DROP INDEX `CATALOG_CATEGORY_ENTITY_INT_ROW_ID_ATTRIBUTE_ID_STORE_ID`,
ADD CONSTRAINT `CATALOG_CATEGORY_ENTITY_INT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID` UNIQUE KEY (`entity_id`,`attribute_id`,`store_id`),
DROP COLUMN `row_id`;
-- Text
ALTER TABLE `catalog_category_entity_text`
DROP FOREIGN KEY `CAT_CTGR_ENTT_TEXT_ROW_ID_CAT_CTGR_ENTT_ROW_ID`,
DROP INDEX `CATALOG_CATEGORY_ENTITY_TEXT_ROW_ID_ATTRIBUTE_ID_STORE_ID`,
ADD CONSTRAINT `CATALOG_CATEGORY_ENTITY_TEXT_ENTITY_ID_ATTRIBUTE_ID_STORE_ID` UNIQUE KEY (`entity_id`,`attribute_id`,`store_id`),
DROP COLUMN `row_id`;
-- Varchar
ALTER TABLE `catalog_category_entity_varchar`
DROP FOREIGN KEY `CAT_CTGR_ENTT_VCHR_ROW_ID_CAT_CTGR_ENTT_ROW_ID`,
DROP INDEX `CATALOG_CATEGORY_ENTITY_VARCHAR_ROW_ID_ATTRIBUTE_ID_STORE_ID`,
ADD CONSTRAINT `CATALOG_CATEGORY_ENTITY_VARCHAR_ENTITY_ID_ATTRIBUTE_ID_STORE_ID` UNIQUE KEY (`entity_id`,`attribute_id`,`store_id`),
DROP COLUMN `row_id`;
-- Entity
SET FOREIGN_KEY_CHECKS = 0;
ALTER TABLE `catalog_category_entity`
DROP FOREIGN KEY `CAT_CTGR_ENTT_ENTT_ID_SEQUENCE_CAT_CTGR_SEQUENCE_VAL`,
DROP COLUMN `row_id`,
DROP COLUMN `created_in`,
DROP COLUMN `updated_in`,
MODIFY COLUMN `entity_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Entity ID',
ADD PRIMARY KEY (`entity_id`);
SET FOREIGN_KEY_CHECKS = 1;
-- Foreign keys
ALTER TABLE `catalog_category_entity_datetime`
ADD CONSTRAINT `CAT_CTGR_ENTT_DTIME_ROW_ID_CAT_CTGR_ENTT_ROW_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `catalog_category_entity_decimal`
ADD CONSTRAINT `CAT_CTGR_ENTT_DEC_ROW_ID_CAT_CTGR_ENTT_ROW_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `catalog_category_entity_int`
ADD CONSTRAINT `CAT_CTGR_ENTT_INT_ROW_ID_CAT_CTGR_ENTT_ROW_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `catalog_category_entity_text`
ADD CONSTRAINT `CAT_CTGR_ENTT_TEXT_ROW_ID_CAT_CTGR_ENTT_ROW_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `catalog_category_entity_varchar`
ADD CONSTRAINT `CAT_CTGR_ENTT_VCHR_ROW_ID_CAT_CTGR_ENTT_ROW_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE RESTRICT;
-- ----------------
-- Drop sequence --
-- ----------------
ALTER TABLE `catalog_category_product`
DROP FOREIGN KEY `CAT_CTGR_PRD_CTGR_ID_SEQUENCE_CAT_CTGR_SEQUENCE_VAL`,
ADD CONSTRAINT `CAT_CTGR_PRD_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID` FOREIGN KEY (`category_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE RESTRICT;
ALTER TABLE `catalog_url_rewrite_product_category`
DROP FOREIGN KEY `CAT_URL_REWRITE_PRD_CTGR_CTGR_ID_SEQUENCE_CAT_CTGR_SEQUENCE_VAL`,
ADD CONSTRAINT `CAT_URL_REWRITE_PRD_CTGR_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID` FOREIGN KEY (`category_id`) REFERENCES `catalog_category_entity` (`entity_id`) ON DELETE CASCADE ON UPDATE RESTRICT;
DROP TABLE `sequence_catalog_category`;