· 8 years ago · Dec 03, 2017, 12:48 PM
1/* SITE Layout.sql */
2
3SET @@group_concat_max_len = 10240;
4
5
6DROP TABLE IF EXISTS _layout_site;
7CREATE TABLE _layout_site (
8 `site_id` INT(11) UNSIGNED NOT NULL,
9 `site_name` VARCHAR(100) NOT NULL,
10 `template_name` VARCHAR(100) NOT NULL,
11 `content_types` SET ('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,
12 `module_name` VARCHAR(100) NOT NULL,
13 `position_name` VARCHAR(100) NOT NULL,
14 `module_order` INT(11) UNSIGNED NOT NULL,
15 `content_id` INT(11) UNSIGNED NOT NULL,
16 `parameter_name` VARCHAR(100) NOT NULL,
17 `parameter_value` VARCHAR(2000) NOT NULL,
18 KEY `template_name` (`site_id`,`template_name`,`module_name`,`position_name`,`module_order`,`content_id`,`parameter_name`)
19) ENGINE=INNODB DEFAULT CHARSET=utf8;
20
21
22DELETE FROM _layout_site; INSERT INTO _layout_site
23SELECT page_layouts_v.site_id, page_layouts_v.site_name, template_name, GROUP_CONCAT(page_name ORDER BY page_name), module_name, position_name, module_order, content_id, IF(parameter_name IS NULL, '', parameter_name), IF(parameter_value IS NULL, '', parameter_value)
24FROM site_layouts, sites_templates, page_layouts_v
25LEFT JOIN module_config_parameter_values ON page_layouts_v.id = page_layout_id
26WHERE site_layouts.id = page_layouts_v.site_layout_id
27AND site_layouts.id = sites_templates.site_layout_id
28AND page_layouts_v.layout_name = 'active'
29GROUP BY site_id, template_name, module_name, position_name, module_order, content_id, parameter_name, parameter_value;
30
31
32DROP TABLE IF EXISTS _pages_sets;
33CREATE TABLE _pages_sets (
34 `template_name` VARCHAR(100) NOT NULL,
35 `ct_bit_count` INT(11) UNSIGNED NOT NULL,
36 `content_types` SET ('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,
37 KEY `template_name` (`template_name`)
38) ENGINE=INNODB DEFAULT CHARSET=utf8;
39
40DELETE FROM _pages_sets; INSERT INTO _pages_sets
41SELECT template_name, BIT_COUNT(content_types), content_types FROM (
42 SELECT DISTINCT _layout_template.template_name, _layout_template.content_types
43 FROM _layout_template, (SELECT template_name, MAX(LENGTH(content_types)) AS ct_length FROM _layout_template GROUP BY template_name) AS template_ct_lengths
44 WHERE _layout_template.template_name = template_ct_lengths.template_name
45 AND LENGTH(_layout_template.content_types) = template_ct_lengths.ct_length
46) AS cts
47ORDER BY template_name;
48
49
50SET @NR = 0;
51SELECT @NR:=@NR+1 nr, site_layout.* FROM ((
52 SELECT 'MISSING' AS instance_diff, tc.site_id, tc.site_name, tc.template_name, tc.module_name, tc.position_name, tc.module_order, tc.content_id,
53 CONCAT(tc.content_types, ' => ', MAKE_SET(~tc.content_types, 'article','articles_listing','contactform','error','page','page_index','rss','search','sitemap','userpage','ws_categories_list','ws_checkout','ws_product','ws_products_list','ws_shoppingcart')) AS flattened_content_types,
54 IF(parameter_name != '', GROUP_CONCAT(CONCAT("'", parameter_name, "'", ':', "'", parameter_value, "'")), '') AS configuration
55 FROM _layout_site AS tc, _pages_sets AS sc
56 WHERE sc.template_name = tc.template_name
57 AND sc.content_types != tc.content_types
58
59 AND !(tc.position_name = 'main' AND tc.content_types = tc.module_name)
60 AND !(tc.position_name = 'main' AND tc.content_types IN ('articles_listing', 'articles_listing,articles_listing') AND tc.module_name = 'articlelist')
61 AND !(tc.position_name = 'main' AND tc.content_types IN ('page_index', 'page,page_index') AND tc.module_name = 'page')
62 AND !(tc.position_name = 'main' AND tc.content_types IN ('ws_categories_list') AND tc.module_name = 'ws_categorylist')
63 AND !(tc.position_name = 'main' AND tc.content_types IN ('ws_products_list', 'ws_categories_list') AND tc.module_name = 'ws_productlist')
64 AND !(tc.position_name = 'main' AND tc.content_types IN ('ws_product','ws_checkout,ws_shoppingcart') AND tc.module_name = 'ws_breadcrumb' AND tc.template_name = '3kelektro')
65 AND !(tc.position_name = 'main' AND tc.content_types IN ('ws_categories_list,ws_productlist','ws_categories_list,ws_product') AND tc.module_name = 'ws_categorytree' AND tc.template_name = '3kelektro')
66 AND !(tc.position_name = 'right' AND tc.content_types IN ('ws_categories_list,ws_product,ws_products_list') AND tc.module_name = 'ws_shoppingcart_box' AND tc.template_name = 'chopshop')
67 AND !(tc.position_name = 'right' AND tc.content_types NOT IN ('ws_shoppingcart') AND tc.module_name = 'ws_shoppingcart_box' AND tc.template_name = 'mustard')
68
69 /*AND tc.template_name = 'simpletabs'*/
70 GROUP BY tc.site_id, tc.template_name, tc.module_name, tc.position_name, tc.module_order, tc.content_id, tc.content_types
71 ORDER BY tc.template_name, tc.module_name, tc.position_name, tc.module_order, tc.content_id
72) UNION (
73 SELECT 'DIFFERENCE' AS instance_diff, tc.site_id, tc.site_name, tc.template_name, tc.module_name, tc.position_name, tc.module_order, tc.content_id,
74 GROUP_CONCAT(tc.content_types ORDER BY tc.content_types SEPARATOR ' => ') AS flattened_content_types,
75 GROUP_CONCAT(CONCAT("'", tc.parameter_name, "'", ':', "'", tc.parameter_value, "'")) AS configuration
76 FROM _layout_site AS tc, _layout_site AS sc
77 WHERE sc.site_id = tc.site_id
78 AND sc.template_name = tc.template_name
79 AND sc.module_name = tc.module_name
80 AND sc.position_name = tc.position_name
81 AND sc.module_order = tc.module_order
82 AND sc.content_id = tc.content_id
83 /*AND sc.content_types = tc.content_types)*/
84
85 AND !(tc.position_name = 'main' AND tc.content_types = tc.module_name)
86 AND !(tc.position_name = 'main' AND tc.content_types IN ('articles_listing', 'articles_listing,articles_listing') AND tc.module_name = 'articlelist')
87 AND !(tc.position_name = 'main' AND tc.content_types IN ('page_index', 'page,page_index') AND tc.module_name = 'page')
88 AND !(tc.position_name = 'main' AND tc.content_types IN ('ws_categories_list') AND tc.module_name = 'ws_categorylist')
89 AND !(tc.position_name = 'main' AND tc.content_types IN ('ws_products_list') AND tc.module_name = 'ws_productlist')
90 AND !(tc.position_name = 'main' AND tc.content_types IN ('ws_checkout', 'ws_shoppingcart') AND tc.module_name = 'ws_breadcrumb' AND tc.template_name = '3kelektro')
91 AND !(tc.position_name = 'main' AND tc.content_types IN ('ws_categories_list', 'ws_productlist') AND tc.module_name = 'ws_categorytree' AND tc.template_name = '3kelektro')
92 AND !(tc.position_name = 'right' AND tc.content_types IN ('ws_categories_list,ws_product,ws_products_list') AND tc.module_name = 'ws_shoppingcart_box' AND tc.template_name = 'chopshop')
93
94 AND ((sc.parameter_name = tc.parameter_name AND sc.parameter_value != tc.parameter_value) OR (sc.parameter_name = '' AND tc.parameter_value != '') OR sc.content_types != tc.content_types)
95
96 GROUP BY tc.site_id, tc.template_name, tc.module_name, tc.position_name, tc.module_order, tc.content_id
97 ORDER BY tc.site_id, tc.template_name, tc.module_name, tc.position_name, tc.module_order, tc.content_id
98)) AS site_layout
99/*WHERE site_id NOT IN (600179, 601068, 600632, 600110, 600139, 600503, 601667, 601669, 601411, 602164, 600615, 600520, 601744, 600273, 600126, 601735, 602075, 601450, 600620, 600556, 600842, 600208, 600340, 601142, 600621, 601917, 601632)*/
100ORDER BY template_name, site_id, position_name, module_order;