· 8 years ago · Dec 03, 2017, 12:44 PM
1/* TPL Layout.sql */
2
3SET @@group_concat_max_len = 10240;
4
5
6DROP TABLE IF EXISTS _layout_template;
7CREATE TABLE _layout_template (
8 `template_name` VARCHAR(100) NOT NULL,
9 `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,
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(100) NOT NULL,
15 `parameter_value` VARCHAR(2000) NOT NULL,
16 KEY `template_name` (`template_name`,`module_name`,`position_name`,`module_order`,`content_id`,`parameter_name`)
17) ENGINE=INNODB DEFAULT CHARSET=utf8;
18
19
20DELETE FROM _layout_template; INSERT INTO _layout_template
21SELECT 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)
22FROM predefined_page_layouts_v
23LEFT JOIN predefined_module_config_parameter_values ON predefined_page_layouts_v.id = predefined_page_layout_id
24WHERE predefined_layout_name = 'default'
25GROUP BY template_name, module_name, position_name, module_order, content_id, parameter_name, parameter_value;
26
27
28DROP TABLE IF EXISTS _pages_sets;
29CREATE TABLE _pages_sets (
30 `template_name` VARCHAR(100) NOT NULL,
31 `ct_bit_count` INT(11) UNSIGNED NOT NULL,
32 `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,
33 KEY `template_name` (`template_name`)
34) ENGINE=INNODB DEFAULT CHARSET=utf8;
35
36
37DELETE FROM _pages_sets; INSERT INTO _pages_sets
38SELECT template_name, BIT_COUNT(content_types), content_types FROM (
39 SELECT DISTINCT _layout_template.template_name, _layout_template.content_types
40 FROM _layout_template, (SELECT template_name, MAX(LENGTH(content_types)) AS ct_length FROM _layout_template GROUP BY template_name) AS template_ct_lengths
41 WHERE _layout_template.template_name = template_ct_lengths.template_name
42 AND LENGTH(_layout_template.content_types) = template_ct_lengths.ct_length
43) AS cts
44ORDER BY template_name;
45
46SET @NR = 0;
47SELECT @NR:=@NR+1 nr, tpl_layout.* FROM ((
48 SELECT 'MISSING' AS instance_diff, tc.template_name, tc.module_name, tc.position_name, tc.module_order, tc.content_id,
49 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 content_types,
50 IF(tc.parameter_name != '', GROUP_CONCAT(CONCAT("'", tc.parameter_name, "'", ':', "'", tc.parameter_value, "'")), '') AS configuration
51 FROM _layout_template AS tc, _pages_sets AS sc
52 WHERE sc.template_name = tc.template_name
53 AND sc.content_types != tc.content_types
54
55 AND !(tc.position_name = 'main' AND tc.content_types = tc.module_name)
56 AND !(tc.position_name = 'main' AND tc.content_types IN ('articles_listing', 'articles_listing,articles_listing') AND tc.module_name = 'articlelist')
57 AND !(tc.position_name = 'main' AND tc.content_types IN ('page_index', 'page,page_index') AND tc.module_name = 'page')
58 AND !(tc.position_name = 'main' AND tc.content_types IN ('ws_categories_list') AND tc.module_name = 'ws_categorylist')
59 AND !(tc.position_name = 'main' AND tc.content_types IN ('ws_products_list', 'ws_categories_list') AND tc.module_name = 'ws_productlist')
60
61 GROUP BY tc.template_name, tc.module_name, tc.position_name, tc.module_order, tc.content_id, tc.content_types
62 ORDER BY tc.template_name, tc.module_name, tc.position_name, tc.module_order, tc.content_id
63) UNION (
64 SELECT 'DIFFERENCE' AS instance_diff, tc.template_name, tc.module_name, tc.position_name, tc.module_order, tc.content_id,
65 GROUP_CONCAT(tc.content_types ORDER BY tc.content_types SEPARATOR ' => ') AS content_types,
66 GROUP_CONCAT(CONCAT("'", tc.parameter_name, "'", ':', "'", tc.parameter_value, "'")) AS configuration
67 FROM _layout_template AS tc, _layout_template AS sc
68 WHERE sc.template_name = tc.template_name
69 AND sc.module_name = tc.module_name
70 AND sc.position_name = tc.position_name
71 AND sc.module_order = tc.module_order
72 AND sc.content_id = tc.content_id
73
74 AND !(tc.position_name = 'main' AND tc.content_types = tc.module_name)
75 AND !(tc.position_name = 'main' AND tc.content_types IN ('articles_listing', 'articles_listing,articles_listing' ) AND tc.module_name = 'articlelist')
76 AND !(tc.position_name = 'main' AND tc.content_types IN ('page_index', 'page,page_index') AND tc.module_name = 'page')
77 AND !(tc.position_name = 'main' AND tc.content_types IN ('ws_categories_list') AND tc.module_name = 'ws_categorylist')
78 AND !(tc.position_name = 'main' AND tc.content_types IN ('ws_products_list') AND tc.module_name = 'ws_productlist')
79
80 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)
81
82 GROUP BY tc.template_name, tc.module_name, tc.position_name, tc.module_order, tc.content_id
83 ORDER BY tc.template_name, tc.module_name, tc.position_name, tc.module_order, tc.content_id
84)) AS tpl_layout
85ORDER BY template_name, position_name, module_order;