· 8 years ago · Dec 03, 2017, 12:40 PM
1/* TPL-SITE Full.sql */
2
3DROP TABLE IF EXISTS _full_template;
4CREATE TABLE _full_template (
5 `site_id` INT(11) UNSIGNED NOT NULL,
6 `site_name` VARCHAR(100) NOT NULL,
7 `predefined_page_layout_id` INT(11) UNSIGNED NOT NULL,
8 `template_name` VARCHAR(100) NOT NULL,
9 `content_type` ENUM ('article','articles_listing','contactform','error','page','page_index','rss','search','sitemap','userpage','ws_categories_list','ws_checkout','ws_product','ws_products_list','ws_shoppingcart') NOT NULL,
10 `module_name` VARCHAR(100) NOT NULL,
11 `position_name` VARCHAR(100) NOT NULL,
12 `module_order` INT(11) UNSIGNED NOT NULL,
13 `content_id` INT(11) UNSIGNED NOT NULL,
14 `parameter_name` VARCHAR(255),
15 `parameter_value` VARCHAR(2000),
16 KEY `template_name` (`site_id`,`template_name`,`module_name`,`position_name`,`module_order`,`content_id`,`parameter_name`)
17) ENGINE=INNODB DEFAULT CHARSET=utf8;
18
19DELETE FROM _full_template; INSERT INTO _full_template
20SELECT site_id, site_name, predefined_page_layouts_v.id, predefined_page_layouts_v.template_name, page_name, module_name, position_name, module_order, content_id, parameter_name, parameter_value
21FROM site_layouts_v, sites_templates, predefined_page_layouts_v
22LEFT JOIN predefined_module_config_parameter_values ON predefined_page_layouts_v.id = predefined_page_layout_id
23WHERE predefined_layout_name = 'default'
24AND layout_name = 'active'
25AND site_layouts_v.id = sites_templates.site_layout_id
26AND predefined_page_layouts_v.template_name = sites_templates.template_name;
27
28
29DROP TABLE IF EXISTS _full_site;
30CREATE TABLE _full_site (
31 `site_id` INT(11) UNSIGNED NOT NULL,
32 `site_name` VARCHAR(100) NOT NULL,
33 `page_layout_id` INT(11) UNSIGNED NOT NULL,
34 `template_name` VARCHAR(100) NOT NULL,
35 `content_type` ENUM ('article','articles_listing','contactform','error','page','page_index','rss','search','sitemap','userpage','ws_categories_list','ws_checkout','ws_product','ws_products_list','ws_shoppingcart') NOT NULL,
36 `module_name` VARCHAR(100) NOT NULL,
37 `position_name` VARCHAR(100) NOT NULL,
38 `module_order` INT(11) UNSIGNED NOT NULL,
39 `content_id` INT(11) UNSIGNED NOT NULL,
40 `parameter_name` VARCHAR(255),
41 `parameter_value` VARCHAR(2000),
42 KEY `template_name` (`site_id`,`template_name`,`module_name`,`position_name`,`module_order`,`content_id`,`parameter_name`)
43) ENGINE=INNODB DEFAULT CHARSET=utf8;
44
45DELETE FROM _full_site; INSERT INTO _full_site
46SELECT page_layouts_v.site_id, page_layouts_v.site_name, page_layouts_v.id, template_name, page_name, module_name, position_name, module_order, content_id, parameter_name, parameter_value
47FROM sites_templates, page_layouts_v
48LEFT JOIN module_config_parameter_values ON page_layouts_v.id = page_layout_id
49WHERE page_layouts_v.site_layout_id = sites_templates.site_layout_id
50AND page_layouts_v.layout_name = 'active';
51
52
53SET @SITE_ID = 'all';
54SET @MODULE = 'all';
55SET @PARAMETER = 'all';
56
57
58DROP TABLE IF EXISTS _full_diffs;
59CREATE TABLE _full_diffs (
60 `diff` VARCHAR(100) NOT NULL,
61 `site_id` INT(11) UNSIGNED NOT NULL,
62 `site_name` VARCHAR(100) NOT NULL,
63 `template_name` VARCHAR(100) NOT NULL,
64 `content_type` ENUM ('article','articles_listing','contactform','error','page','page_index','rss','search','sitemap','userpage','ws_categories_list','ws_checkout','ws_product','ws_products_list','ws_shoppingcart') NOT NULL,
65 `module_name` VARCHAR(100) NOT NULL,
66 `position_name` VARCHAR(100) NOT NULL,
67 `module_order` INT(11) UNSIGNED NOT NULL,
68 `parameter_name` VARCHAR(255),
69 `template_value` VARCHAR(2000),
70 `site_value` VARCHAR(2000),
71 `tpl_ref_id` INT(11) UNSIGNED,
72 `site_ref_id` INT(11) UNSIGNED,
73 KEY `template_name` (`diff`, `site_id`,`module_name`,`position_name`,`module_order`, `parameter_name`)
74) ENGINE=INNODB DEFAULT CHARSET=utf8;
75
76DELETE FROM _full_diffs;
77
78INSERT INTO _full_diffs
79/* Missing instance on site level */
80SELECT 'MISSING SITE INSTANCE' AS diff, ft.site_id, ft.site_name, ft.template_name, ft.content_type, ft.module_name, ft.position_name, ft.module_order,
81 ft.parameter_name, ft.parameter_value AS template_value, fs.parameter_value AS site_value,
82 ft.predefined_page_layout_id AS tpl_ref_id, fs.page_layout_id AS site_ref_id
83FROM _full_template AS ft
84LEFT JOIN _full_site AS fs
85 ON ft.site_id = fs.site_id
86 AND ft.template_name = fs.template_name
87 AND ft.content_type = fs.content_type
88 AND ft.module_name = fs.module_name
89 AND ft.position_name = fs.position_name
90 AND ft.module_order = fs.module_order
91WHERE (ft.site_id = @SITE_ID OR 'all' = @SITE_ID)
92AND (ft.module_name = @MODULE OR 'all' = @MODULE)
93AND (ft.parameter_name = @PARAMETER OR 'all' = @PARAMETER)
94AND fs.page_layout_id IS NULL;
95
96INSERT INTO _full_diffs
97/* Missing instance parameters or different parameters on site level */
98SELECT DISTINCT IF(ft.parameter_name = fs.parameter_name, 'DIFF SITE PARAM', 'MISSING SITE PARAM') AS diff,
99 ft.site_id, ft.site_name, ft.template_name, ft.content_type, ft.module_name, ft.position_name, ft.module_order,
100 ft.parameter_name, ft.parameter_value AS template_value, fs.parameter_value AS site_value,
101 ft.predefined_page_layout_id AS tpl_ref_id, spl.page_layout_id AS site_ref_id
102FROM _full_site AS spl, _full_template AS ft
103LEFT JOIN _full_site AS fs
104 ON ft.site_id = fs.site_id
105 AND ft.template_name = fs.template_name
106 AND ft.content_type = fs.content_type
107 AND ft.module_name = fs.module_name
108 AND ft.position_name = fs.position_name
109 AND ft.module_order = fs.module_order
110 AND ft.parameter_name = fs.parameter_name
111WHERE (ft.site_id = @SITE_ID OR 'all' = @SITE_ID)
112AND (ft.module_name = @MODULE OR 'all' = @MODULE)
113AND (ft.parameter_name = @PARAMETER OR 'all' = @PARAMETER)
114 AND ft.site_id = spl.site_id
115 AND ft.template_name = spl.template_name
116 AND ft.content_type = spl.content_type
117 AND ft.module_name = spl.module_name
118 AND ft.position_name = spl.position_name
119 AND ft.module_order = spl.module_order
120AND (
121 (ft.parameter_name IS NOT NULL AND fs.parameter_name IS NULL)
122 OR (ft.parameter_name = fs.parameter_name AND ft.parameter_value != fs.parameter_value)
123);
124
125INSERT INTO _full_diffs
126/* Existing instance on site level */
127SELECT 'EXISTS SITE INSTANCE' AS diff, fs.site_id, fs.site_name, fs.template_name, fs.content_type, fs.module_name, fs.position_name, fs.module_order,
128 fs.parameter_name, fs.parameter_value AS site_value, ft.parameter_value AS template_value,
129 ft.predefined_page_layout_id AS tpl_ref_id, fs.page_layout_id AS site_ref_id
130FROM _full_template AS ft
131RIGHT JOIN _full_site AS fs
132 ON ft.site_id = fs.site_id
133 AND ft.template_name = fs.template_name
134 AND ft.content_type = fs.content_type
135 AND ft.module_name = fs.module_name
136 AND ft.position_name = fs.position_name
137 AND ft.module_order = fs.module_order
138WHERE (fs.site_id = @SITE_ID OR 'all' = @SITE_ID)
139AND (fs.module_name = @MODULE OR 'all' = @MODULE)
140AND (ft.parameter_name = @PARAMETER OR 'all' = @PARAMETER)
141AND ft.predefined_page_layout_id IS NULL;
142
143INSERT INTO _full_diffs
144/* Existing instance parameters or different parameters on site level */
145SELECT DISTINCT IF(fs.parameter_name = fs.parameter_name, 'DIFF TPL PARAM', 'MISSING TPL PARAM') AS diff,
146 fs.site_id, fs.site_name, fs.template_name, fs.content_type, fs.module_name, fs.position_name, fs.module_order,
147 fs.parameter_name, fs.parameter_value AS site_value, ft.parameter_value AS template_value,
148 ft.predefined_page_layout_id AS tpl_ref_id, fs.page_layout_id AS site_ref_id
149FROM _full_template AS ft
150RIGHT JOIN _full_site AS fs
151 ON ft.site_id = fs.site_id
152 AND ft.template_name = fs.template_name
153 AND ft.content_type = fs.content_type
154 AND ft.module_name = fs.module_name
155 AND ft.position_name = fs.position_name
156 AND ft.module_order = fs.module_order
157 AND ft.parameter_name = fs.parameter_name
158WHERE (fs.site_id = @SITE_ID OR 'all' = @SITE_ID)
159AND (fs.module_name = @MODULE OR 'all' = @MODULE)
160AND (ft.parameter_name = @PARAMETER OR 'all' = @PARAMETER)
161AND (
162 (ft.parameter_name IS NULL AND fs.parameter_name IS NOT NULL)
163 OR (ft.parameter_name IS NOT NULL AND fs.parameter_name IS NOT NULL AND ft.parameter_value != fs.parameter_value)
164);
165
166
167SET @NR = 0;
168SELECT @NR:=@NR+1 nr, diff, site_id, site_name, template_name, content_types, module_name, position_name, module_order,
169 IF(parameter_name = '', '', GROUP_CONCAT(CONCAT("'", parameter_name, "':'", template_value, "' ['", site_value, "']"))) AS configuration
170 /*, tpl_refs_id, site_refs_id*/
171FROM (
172 SELECT diff, site_id, site_name, template_name,
173 GROUP_CONCAT(content_type ORDER BY content_type) AS content_types, module_name, position_name, module_order,
174 IF(parameter_name IS NULL, '', parameter_name) AS parameter_name,
175 IF(template_value IS NULL, '', template_value) AS template_value, IF(site_value IS NULL, '', site_value) AS site_value
176 /*, GROUP_CONCAT(tpl_ref_id ORDER BY content_type) AS tpl_refs_id, GROUP_CONCAT(site_ref_id ORDER BY content_type) AS site_refs_id*/
177 FROM _full_diffs
178 WHERE !(module_name IN ('articlebox', 'newsletter_signup_box', 'rssbox', 'ws_shoppingcart_box', 'ws_creditcards') AND position_name IN ('right', 'left'))
179 AND !(module_name IN ('ws_categorytree') AND parameter_name IN ('display_title', 'title'))
180 AND !(module_name IN ('menu') AND parameter_name IN ('display_menu_title', 'title'))
181 GROUP BY diff, site_id, module_name, position_name, module_order, parameter_name, template_value, site_value
182 ORDER BY site_id, position_name, module_order
183) AS layouts
184/*WHERE diff = 'MISSING SITE INSTANCE'
185AND module_name in ('ws_categorylist','ws_checkout','ws_product','ws_productlist','ws_shoppingcart')*/
186GROUP BY diff, site_id, module_name, position_name, module_order, content_types/*, tpl_refs_id, site_refs_id*/
187ORDER BY module_name, configuration, site_id, diff, position_name, module_order;