Nu3iW0u6

1/* TPL Flatten.sql */
2
3SET @@group_concat_max_len = 10240;
4
5DROP TABLE IF EXISTS _flatten_template;
6CREATE TABLE _flatten_template (
7  `template_name` VARCHAR(100) NOT NULL,
8  `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,
9  `module_name` VARCHAR(100) NOT NULL,
10  `position_name` VARCHAR(100) NOT NULL,
11  `module_order` INT(11) UNSIGNED NOT NULL,
12  `content_id` INT(11) UNSIGNED NOT NULL,
13  `configuration` VARCHAR(10000) NOT NULL,
14  KEY `template_name` (`template_name`,`module_name`,`position_name`,`module_order`,`content_id`)
15) ENGINE=INNODB DEFAULT CHARSET=utf8;
16
17DELETE FROM _flatten_template; INSERT INTO _flatten_template
18SELECT template_name, GROUP_CONCAT(page_name ORDER BY page_name), module_name, position_name, module_order, content_id, IF(configuration IS NULL, '', configuration) FROM (
19	SELECT template_name, page_name, module_name, position_name, module_order, content_id,
20		GROUP_CONCAT(CONCAT("'", parameter_name, "'", ':', "'", parameter_value, "'")) AS configuration,
21		GROUP_CONCAT(CONCAT(parameter_name, MD5(parameter_value))) AS md5_configuration
22	FROM predefined_page_layouts_v
23	LEFT JOIN predefined_module_config_parameter_values ON predefined_page_layouts_v.id = predefined_page_layout_id
24	WHERE predefined_layout_name = 'default'
25	GROUP BY predefined_page_layouts_v.id
26) AS concatenated_configuration
27GROUP BY template_name, module_name, position_name, module_order, content_id, md5_configuration;
28	
29SET @NR = 0;
30SELECT @NR:=@NR+1 nr, tc.template_name, tc.module_name, tc.position_name, tc.module_order, tc.content_id, 
31GROUP_CONCAT(tc.content_types ORDER BY tc.content_types SEPARATOR ' => ') AS content_types,
32GROUP_CONCAT(tc.configuration ORDER BY tc.content_types SEPARATOR ' => ') AS configuration
33FROM _flatten_template AS tc, _flatten_template AS sc
34WHERE sc.template_name = tc.template_name
35AND sc.module_name = tc.module_name
36AND sc.position_name = tc.position_name
37AND sc.module_order = tc.module_order
38AND sc.content_id = tc.content_id
39/*
40AND !(tc.position_name = 'main' AND tc.content_types = tc.module_name)
41AND !(tc.position_name = 'main' AND tc.content_types IN ('articles_listing', 'articles_listing,articles_listing') AND tc.module_name = 'articlelist')
42AND !(tc.position_name = 'main' AND tc.content_types IN ('page_index', 'page,page_index') AND tc.module_name = 'page')
43AND !(tc.position_name = 'main' AND tc.content_types IN ('ws_categories_list') AND tc.module_name = 'ws_categorylist')
44AND !(tc.position_name = 'main' AND tc.content_types IN ('ws_products_list') AND tc.module_name = 'ws_productlist')
45
46AND (sc.configuration != tc.configuration OR sc.content_types != tc.content_types)
47*/
48GROUP BY template_name, module_name, position_name, module_order, content_id
49ORDER BY template_name, module_name, position_name, module_order, content_id;