File indexing completed on 2024-04-14 05:53:06
0001 DROP TABLE `tmp_cat_tag_proj`; 0002 0003 CREATE TABLE `tmp_cat_tag_proj` 0004 ( 0005 `project_id` INT(11) NOT NULL, 0006 `project_category_id` INT(11) NOT NULL, 0007 `tag_id` INT(11) NOT NULL, 0008 `ancestor_id_path` VARCHAR(50) NULL DEFAULT NULL, 0009 PRIMARY KEY (`project_id`, `project_category_id`, `tag_id`) 0010 ) 0011 COLLATE = 'latin1_swedish_ci' 0012 ENGINE = MyISAM 0013 ; 0014 0015 0016 DELIMITER $ 0017 DROP PROCEDURE IF EXISTS `generate_tmp_cat_tag_proj`$ 0018 0019 CREATE PROCEDURE `generate_tmp_cat_tag_proj`() 0020 BEGIN 0021 0022 TRUNCATE TABLE `tmp_cat_tag_proj`; 0023 0024 INSERT INTO `tmp_cat_tag_proj` 0025 0026 SELECT `p`.`project_id`, `p`.`project_category_id`, `c`.`tag_id`, `t`.`ancestor_id_path` 0027 FROM `project` `p` 0028 JOIN `stat_cat_tree` `t` ON `t`.`project_category_id` = `p`.`project_category_id` 0029 JOIN `category_tag` `c` ON `c`.`category_id` = `t`.`project_category_id` 0030 JOIN `tag` `ta` ON `ta`.`tag_id` = `c`.`tag_id` 0031 WHERE `p`.`status` = 100; 0032 0033 INSERT IGNORE INTO `tmp_cat_tag_proj` 0034 #ebene 1 0035 SELECT `p`.`project_id`, `c`.`category_id` AS `project_category_id`, `c`.`tag_id`, `t`.`ancestor_id_path` 0036 FROM `project` `p` 0037 JOIN `stat_cat_tree` `t` ON `t`.`project_category_id` = `p`.`project_category_id` 0038 JOIN `category_tag` `c` ON `c`.`category_id` = (SPLIT_STRING(`t`.`ancestor_id_path`, ',', 1)) 0039 JOIN `tag` `ta` ON `ta`.`tag_id` = `c`.`tag_id` 0040 WHERE `p`.`status` = 100; 0041 0042 INSERT IGNORE INTO `tmp_cat_tag_proj` 0043 #ebene 2 0044 SELECT `p`.`project_id`, `c`.`category_id` AS `project_category_id`, `c`.`tag_id`, `t`.`ancestor_id_path` 0045 FROM `project` `p` 0046 JOIN `stat_cat_tree` `t` ON `t`.`project_category_id` = `p`.`project_category_id` 0047 JOIN `category_tag` `c` ON `c`.`category_id` = (SPLIT_STRING(`t`.`ancestor_id_path`, ',', 2)) 0048 JOIN `tag` `ta` ON `ta`.`tag_id` = `c`.`tag_id` 0049 WHERE `p`.`status` = 100; 0050 0051 INSERT IGNORE INTO `tmp_cat_tag_proj` 0052 #ebene 3 0053 SELECT `p`.`project_id`, `c`.`category_id` AS `project_category_id`, `c`.`tag_id`, `t`.`ancestor_id_path` 0054 FROM `project` `p` 0055 JOIN `stat_cat_tree` `t` ON `t`.`project_category_id` = `p`.`project_category_id` 0056 JOIN `category_tag` `c` ON `c`.`category_id` = (SPLIT_STRING(`t`.`ancestor_id_path`, ',', 3)) 0057 JOIN `tag` `ta` ON `ta`.`tag_id` = `c`.`tag_id` 0058 WHERE `p`.`status` = 100; 0059 0060 INSERT IGNORE INTO `tmp_cat_tag_proj` 0061 #ebene 4 0062 SELECT `p`.`project_id`, `c`.`category_id` AS `project_category_id`, `c`.`tag_id`, `t`.`ancestor_id_path` 0063 FROM `project` `p` 0064 JOIN `stat_cat_tree` `t` ON `t`.`project_category_id` = `p`.`project_category_id` 0065 JOIN `category_tag` `c` ON `c`.`category_id` = (SPLIT_STRING(`t`.`ancestor_id_path`, ',', 4)) 0066 JOIN `tag` `ta` ON `ta`.`tag_id` = `c`.`tag_id` 0067 WHERE `p`.`status` = 100; 0068 0069 INSERT IGNORE INTO `tmp_cat_tag_proj` 0070 #ebene 5 0071 SELECT `p`.`project_id`, `c`.`category_id` AS `project_category_id`, `c`.`tag_id`, `t`.`ancestor_id_path` 0072 FROM `project` `p` 0073 JOIN `stat_cat_tree` `t` ON `t`.`project_category_id` = `p`.`project_category_id` 0074 JOIN `category_tag` `c` ON `c`.`category_id` = (SPLIT_STRING(`t`.`ancestor_id_path`, ',', 5)) 0075 JOIN `tag` `ta` ON `ta`.`tag_id` = `c`.`tag_id` 0076 WHERE `p`.`status` = 100; 0077 0078 INSERT IGNORE INTO `tmp_cat_tag_proj` 0079 #ebene 6 0080 SELECT `p`.`project_id`, `c`.`category_id` AS `project_category_id`, `c`.`tag_id`, `t`.`ancestor_id_path` 0081 FROM `project` `p` 0082 JOIN `stat_cat_tree` `t` ON `t`.`project_category_id` = `p`.`project_category_id` 0083 JOIN `category_tag` `c` ON `c`.`category_id` = (SPLIT_STRING(`t`.`ancestor_id_path`, ',', 6)) 0084 JOIN `tag` `ta` ON `ta`.`tag_id` = `c`.`tag_id` 0085 WHERE `p`.`status` = 100; 0086 0087 INSERT IGNORE INTO `tmp_cat_tag_proj` 0088 #ebene 7 0089 SELECT `p`.`project_id`, `c`.`category_id` AS `project_category_id`, `c`.`tag_id`, `t`.`ancestor_id_path` 0090 FROM `project` `p` 0091 JOIN `stat_cat_tree` `t` ON `t`.`project_category_id` = `p`.`project_category_id` 0092 JOIN `category_tag` `c` ON `c`.`category_id` = (SPLIT_STRING(`t`.`ancestor_id_path`, ',', 7)) 0093 JOIN `tag` `ta` ON `ta`.`tag_id` = `c`.`tag_id` 0094 WHERE `p`.`status` = 100; 0095 0096 INSERT IGNORE INTO `tmp_cat_tag_proj` 0097 #ebene 8 0098 SELECT `p`.`project_id`, `c`.`category_id` AS `project_category_id`, `c`.`tag_id`, `t`.`ancestor_id_path` 0099 FROM `project` `p` 0100 JOIN `stat_cat_tree` `t` ON `t`.`project_category_id` = `p`.`project_category_id` 0101 JOIN `category_tag` `c` ON `c`.`category_id` = (SPLIT_STRING(`t`.`ancestor_id_path`, ',', 8)) 0102 JOIN `tag` `ta` ON `ta`.`tag_id` = `c`.`tag_id` 0103 WHERE `p`.`status` = 100; 0104 0105 INSERT IGNORE INTO `tmp_cat_tag_proj` 0106 #ebene 9 0107 SELECT `p`.`project_id`, `c`.`category_id` AS `project_category_id`, `c`.`tag_id`, `t`.`ancestor_id_path` 0108 FROM `project` `p` 0109 JOIN `stat_cat_tree` `t` ON `t`.`project_category_id` = `p`.`project_category_id` 0110 JOIN `category_tag` `c` ON `c`.`category_id` = (SPLIT_STRING(`t`.`ancestor_id_path`, ',', 9)) 0111 JOIN `tag` `ta` ON `ta`.`tag_id` = `c`.`tag_id` 0112 WHERE `p`.`status` = 100; 0113 0114 INSERT IGNORE INTO `tmp_cat_tag_proj` 0115 #ebene 10 0116 SELECT `p`.`project_id`, `c`.`category_id` AS `project_category_id`, `c`.`tag_id`, `t`.`ancestor_id_path` 0117 FROM `project` `p` 0118 JOIN `stat_cat_tree` `t` ON `t`.`project_category_id` = `p`.`project_category_id` 0119 JOIN `category_tag` `c` ON `c`.`category_id` = (SPLIT_STRING(`t`.`ancestor_id_path`, ',', 10)) 0120 JOIN `tag` `ta` ON `ta`.`tag_id` = `c`.`tag_id` 0121 WHERE `p`.`status` = 100; 0122 0123 END$ 0124 0125 DELIMITER ; 0126 0127 DELIMITER $ 0128 0129 DROP PROCEDURE IF EXISTS `SPLIT_STRING`$ 0130 0131 CREATE FUNCTION `SPLIT_STRING`(`s` VARCHAR(1024), 0132 `del` CHAR(1), 0133 `i` INT) 0134 RETURNS varchar(1024) CHARSET `latin1` 0135 BEGIN 0136 0137 DECLARE `n` INT; 0138 0139 -- get max number of items 0140 SET `n` = LENGTH(`s`) - LENGTH(REPLACE(`s`, `del`, '')) + 1; 0141 0142 IF `i` > `n` THEN 0143 RETURN NULL ; 0144 ELSE 0145 RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(`s`, `del`, `i`), `del`, -1); 0146 END IF; 0147 0148 END$ 0149 DELIMITER ;