Warning, file /webapps/ocs-webserver/sql_code/20190213_system_tags_update.sql was not indexed or was modified since last indexation (in which case cross-reference links may be missing, inaccurate or erroneous).
0001 -- drop old procedure 0002 DROP PROCEDURE `generate_tmp_cat_tag_proj`; 0003 0004 0005 DROP TABLE IF EXISTS `stat_cat_tree_hierachie`; 0006 0007 CREATE TABLE `stat_cat_tree_hierachie` 0008 ( 0009 `project_category_id` int, 0010 `ancestor_id_path` varchar(50), 0011 `catid1` int, 0012 `catid2` int, 0013 `catid3` int, 0014 `catid4` int, 0015 `catid5` int, 0016 `catid6` int, 0017 `created_at` timestamp NOT NULL DEFAULT now(), 0018 PRIMARY KEY (`project_category_id`), 0019 INDEX `ix_stat_cat_tree_hierachie_1` (`catid1`), 0020 INDEX `ix_stat_cat_tree_hierachie_2` (`catid2`), 0021 INDEX `ix_stat_cat_tree_hierachie_3` (`catid3`), 0022 INDEX `ix_stat_cat_tree_hierachie_4` (`catid4`), 0023 INDEX `ix_stat_cat_tree_hierachie_5` (`catid5`), 0024 INDEX `ix_stat_cat_tree_hierachie_6` (`catid6`) 0025 ); 0026 0027 TRUNCATE TABLE `stat_cat_tree_hierachie`; 0028 INSERT INTO `stat_cat_tree_hierachie` 0029 SELECT `t`.`project_category_id`, 0030 `t`.`ancestor_id_path`, 0031 SPLIT_STRING(`t`.`ancestor_id_path`, ',', 1) AS `catid1`, -- root no category tags ignore 0032 SPLIT_STRING(`t`.`ancestor_id_path`, ',', 2) AS `catid2`, 0033 SPLIT_STRING(`t`.`ancestor_id_path`, ',', 3) AS `catid3`, 0034 SPLIT_STRING(`t`.`ancestor_id_path`, ',', 4) AS `catid4`, 0035 SPLIT_STRING(`t`.`ancestor_id_path`, ',', 5) AS `catid5`, 0036 SPLIT_STRING(`t`.`ancestor_id_path`, ',', 6) AS `catid6`, 0037 now() AS `created_at` 0038 FROM `stat_cat_tree` `t`; 0039 0040 0041 DROP TABLE IF EXISTS `tmp_project_system_tag`; 0042 0043 CREATE TABLE `tmp_project_system_tag` 0044 ( 0045 `project_id` INT(11) NOT NULL, 0046 `project_category_id` INT(11) NOT NULL, 0047 `tag_id` INT(11) NOT NULL, 0048 `ancestor_id_path` VARCHAR(50) NULL DEFAULT NULL, 0049 INDEX (`project_id`, `project_category_id`, `tag_id`) 0050 ); 0051 0052 DROP PROCEDURE IF EXISTS `generate_tmp_cat_tag_proj_init`; 0053 0054 DELIMITER $$ 0055 CREATE PROCEDURE `generate_tmp_cat_tag_proj_init`() 0056 BEGIN 0057 0058 TRUNCATE TABLE `tmp_project_system_tag`; 0059 0060 TRUNCATE TABLE `stat_cat_tree_hierachie`; 0061 INSERT INTO `stat_cat_tree_hierachie` 0062 SELECT `t`.`project_category_id`, 0063 `t`.`ancestor_id_path`, 0064 SPLIT_STRING(`t`.`ancestor_id_path`, ',', 1) AS `catid1`, -- root no category tags ignore 0065 SPLIT_STRING(`t`.`ancestor_id_path`, ',', 2) AS `catid2`, 0066 SPLIT_STRING(`t`.`ancestor_id_path`, ',', 3) AS `catid3`, 0067 SPLIT_STRING(`t`.`ancestor_id_path`, ',', 4) AS `catid4`, 0068 SPLIT_STRING(`t`.`ancestor_id_path`, ',', 5) AS `catid5`, 0069 SPLIT_STRING(`t`.`ancestor_id_path`, ',', 6) AS `catid6`, 0070 now() AS `created_at` 0071 FROM `stat_cat_tree` `t`; 0072 0073 INSERT INTO `tmp_project_system_tag` 0074 SELECT `p`.`project_id`, `p`.`project_category_id`, `c`.`tag_id`, `t`.`ancestor_id_path` 0075 FROM `project` `p` 0076 JOIN `stat_cat_tree_hierachie` `t` ON `t`.`project_category_id` = `p`.`project_category_id` 0077 JOIN `category_tag` `c` ON `c`.`category_id` = `t`.`catid2` 0078 WHERE `p`.`status` = 100; 0079 0080 INSERT INTO `tmp_project_system_tag` 0081 SELECT `p`.`project_id`, `p`.`project_category_id`, `c`.`tag_id`, `t`.`ancestor_id_path` 0082 FROM `project` `p` 0083 JOIN `stat_cat_tree_hierachie` `t` ON `t`.`project_category_id` = `p`.`project_category_id` 0084 JOIN `category_tag` `c` ON `c`.`category_id` = `t`.`catid3` 0085 WHERE `p`.`status` = 100; 0086 0087 INSERT INTO `tmp_project_system_tag` 0088 SELECT `p`.`project_id`, `p`.`project_category_id`, `c`.`tag_id`, `t`.`ancestor_id_path` 0089 FROM `project` `p` 0090 JOIN `stat_cat_tree_hierachie` `t` ON `t`.`project_category_id` = `p`.`project_category_id` 0091 JOIN `category_tag` `c` ON `c`.`category_id` = `t`.`catid4` 0092 WHERE `p`.`status` = 100; 0093 0094 INSERT INTO `tmp_project_system_tag` 0095 SELECT `p`.`project_id`, `p`.`project_category_id`, `c`.`tag_id`, `t`.`ancestor_id_path` 0096 FROM `project` `p` 0097 JOIN `stat_cat_tree_hierachie` `t` ON `t`.`project_category_id` = `p`.`project_category_id` 0098 JOIN `category_tag` `c` ON `c`.`category_id` = `t`.`catid5` 0099 WHERE `p`.`status` = 100; 0100 0101 INSERT INTO `tmp_project_system_tag` 0102 SELECT `p`.`project_id`, `p`.`project_category_id`, `c`.`tag_id`, `t`.`ancestor_id_path` 0103 FROM `project` `p` 0104 JOIN `stat_cat_tree_hierachie` `t` ON `t`.`project_category_id` = `p`.`project_category_id` 0105 JOIN `category_tag` `c` ON `c`.`category_id` = `t`.`catid6` 0106 WHERE `p`.`status` = 100; 0107 0108 0109 DROP TABLE IF EXISTS `tmp_tag_object_to_delete`; 0110 CREATE TEMPORARY TABLE `tmp_tag_object_to_delete` 0111 ( 0112 PRIMARY KEY `primary` (`tag_item_id`) 0113 ) 0114 ENGINE MyISAM 0115 AS 0116 SELECT `o`.`tag_item_id` 0117 FROM `tag_object` `o` 0118 LEFT JOIN `tmp_project_system_tag` `t` 0119 ON `t`.`project_id` = `o`.`tag_object_id` AND `t`.`tag_id` = `o`.`tag_id` 0120 WHERE `o`.`tag_group_id` = 6 0121 AND `o`.`is_deleted` = 0 0122 AND `t`.`project_id` IS NULL; 0123 0124 /*DELETE SYSTEM TAGS -- 12155 TO DELETE*/ 0125 0126 UPDATE `tag_object` 0127 SET `is_deleted` = 1, 0128 `tag_changed` = now() 0129 WHERE `tag_item_id` IN 0130 ( 0131 SELECT `o`.`tag_item_id` 0132 FROM `tmp_tag_object_to_delete` `o` 0133 ); 0134 0135 0136 DROP TABLE IF EXISTS `tmp_tag_object_to_insert`; 0137 CREATE TEMPORARY TABLE `tmp_tag_object_to_insert` 0138 /*(INDEX (project_id,project_category_id,tag_id))*/ 0139 ENGINE MyISAM 0140 AS 0141 SELECT `t`.* 0142 FROM `tmp_project_system_tag` `t` 0143 LEFT JOIN `tag_object` `o` ON `t`.`project_id` = `o`.`tag_object_id` AND `t`.`tag_id` = `o`.`tag_id` AND 0144 `o`.`tag_group_id` = 6 0145 WHERE `o`.`tag_item_id` IS NULL; 0146 0147 0148 INSERT INTO `tag_object` 0149 SELECT NULL AS `tag_item_id`, 0150 `p`.`tag_id`, 0151 1 AS `tag_type_id`, 0152 6 AS `tag_group_id`, 0153 `p`.`project_id` AS `tag_object_id`, 0154 NULL AS `tag_parenet_object_id`, 0155 NOW() AS `tag_created`, 0156 NULL AS `tag_changed`, 0157 0 AS `is_deleted` 0158 FROM ( 0159 SELECT DISTINCT * 0160 FROM `tmp_tag_object_to_insert` 0161 ) `p`; 0162 0163 0164 END; 0165 $$ 0166 0167 CREATE EVENT `e_generate_tmp_cat_tag_proj_init` 0168 ON SCHEDULE 0169 EVERY 1 DAY STARTS '2019-02-19 17:00:00' 0170 ON COMPLETION NOT PRESERVE 0171 ENABLE 0172 COMMENT '' 0173 DO 0174 BEGIN 0175 CALL generate_tmp_cat_tag_proj_init(); 0176 END$$ 0177 0178 DELIMITER ;