File indexing completed on 2024-04-14 05:53:06
0001 USE `pling-import`; 0002 DROP PROCEDURE IF EXISTS `solr_query_import`; 0003 DELIMITER $$ 0004 USE `pling-import`$$ 0005 CREATE PROCEDURE `solr_query_import`() 0006 BEGIN 0007 DROP TABLE IF EXISTS `tmp_cat_tree`; 0008 CREATE TEMPORARY TABLE `tmp_cat_tree` AS 0009 SELECT `pc`.`project_category_id`, 0010 `pc`.`title`, 0011 `pc`.`is_active`, 0012 count(`pc`.`lft`) AS `depth`, 0013 GROUP_CONCAT(`pc2`.`project_category_id` ORDER BY `pc2`.`lft`) AS `ancestor_id_path`, 0014 GROUP_CONCAT(`pc2`.`title` ORDER BY `pc2`.`lft` SEPARATOR ' | ') AS `ancestor_path` 0015 FROM `project_category` AS `pc`, 0016 `project_category` AS `pc2` 0017 WHERE (`pc`.`lft` BETWEEN `pc2`.`lft` AND `pc2`.`rgt`) 0018 GROUP BY `pc`.`lft` 0019 ORDER BY `pc`.`lft`; 0020 0021 DROP TABLE IF EXISTS `tmp_cat_store`; 0022 CREATE TEMPORARY TABLE `tmp_cat_store` AS 0023 SELECT `tct`.`project_category_id`, 0024 `tct`.`ancestor_id_path`, 0025 `tct`.`title`, 0026 `tct`.`is_active`, 0027 group_concat(`store_id`) AS `stores` 0028 FROM `tmp_cat_tree` AS `tct`, 0029 `config_store_category` AS `csc` 0030 WHERE FIND_IN_SET(`csc`.`project_category_id`, `tct`.`ancestor_id_path`) > 0 0031 GROUP BY `tct`.`project_category_id` 0032 ORDER BY `tct`.`project_category_id`; 0033 0034 0035 SELECT `project_id`, 0036 `project`.`member_id` AS `project_member_id`, 0037 `project`.`project_category_id` AS `project_category_id`, 0038 `project`.`title` AS `project_title`, 0039 `description`, 0040 `image_small`, 0041 `member`.`username`, 0042 `member`.`firstname`, 0043 `member`.`lastname`, 0044 `tcs`.`title` AS `cat_title`, 0045 `project`.`count_likes` AS `count_likes`, 0046 `project`.`count_dislikes` AS `count_dislikes`, 0047 (ROUND(((`project`.`count_likes` + 6) / ((`project`.`count_likes` + `project`.`count_dislikes`) + 12)), 2) * 0048 100) AS `laplace_score`, 0049 `project`.`created_at`, 0050 `project`.`changed_at`, 0051 `tcs`.`stores`, 0052 `tcs`.`ancestor_id_path` AS `cat_id_ancestor_path` 0053 FROM `project` 0054 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0055 JOIN `tmp_cat_store` AS `tcs` ON `project`.`project_category_id` = `tcs`.`project_category_id` 0056 WHERE `project`.`status` = 100 0057 AND `project`.`type_id` = 1 0058 AND `member`.`is_active` = 1 0059 AND `tcs`.`is_active` = 1; 0060 END$$ 0061 DELIMITER ; 0062 0063 USE `pling-import`; 0064 DROP PROCEDURE IF EXISTS `solr_query_delta_import`; 0065 DELIMITER $$ 0066 USE `pling-import`$$ 0067 CREATE PROCEDURE `solr_query_delta_import`(IN `projectID` INT(11)) 0068 BEGIN 0069 DROP TABLE IF EXISTS `tmp_cat_tree`; 0070 CREATE TEMPORARY TABLE `tmp_cat_tree` AS 0071 SELECT `pc`.`project_category_id`, 0072 `pc`.`title`, 0073 `pc`.`is_active`, 0074 count(`pc`.`lft`) AS `depth`, 0075 GROUP_CONCAT(`pc2`.`project_category_id` ORDER BY `pc2`.`lft`) AS `ancestor_id_path`, 0076 GROUP_CONCAT(`pc2`.`title` ORDER BY `pc2`.`lft` SEPARATOR ' | ') AS `ancestor_path` 0077 FROM `project_category` AS `pc`, 0078 `project_category` AS `pc2` 0079 WHERE (`pc`.`lft` BETWEEN `pc2`.`lft` AND `pc2`.`rgt`) 0080 GROUP BY `pc`.`lft` 0081 ORDER BY `pc`.`lft`; 0082 0083 DROP TABLE IF EXISTS `tmp_cat_store`; 0084 CREATE TEMPORARY TABLE `tmp_cat_store` AS 0085 SELECT `tct`.`project_category_id`, 0086 `tct`.`ancestor_id_path`, 0087 `tct`.`title`, 0088 `tct`.`is_active`, 0089 group_concat(`store_id`) AS `stores` 0090 FROM `tmp_cat_tree` AS `tct`, 0091 `config_store_category` AS `csc` 0092 WHERE FIND_IN_SET(`csc`.`project_category_id`, `tct`.`ancestor_id_path`) > 0 0093 GROUP BY `tct`.`project_category_id` 0094 ORDER BY `tct`.`project_category_id`; 0095 0096 SELECT `project_id`, 0097 `project`.`member_id` AS `project_member_id`, 0098 `project`.`project_category_id` AS `project_category_id`, 0099 `project`.`title` AS `project_title`, 0100 `description`, 0101 `image_small`, 0102 `member`.`username`, 0103 `member`.`firstname`, 0104 `member`.`lastname`, 0105 `tcs`.`title` AS `cat_title`, 0106 `project`.`count_likes` AS `count_likes`, 0107 `project`.`count_dislikes` AS `count_dislikes`, 0108 (ROUND(((`project`.`count_likes` + 6) / ((`project`.`count_likes` + `project`.`count_dislikes`) + 12)), 2) * 0109 100) AS `laplace_score`, 0110 `project`.`created_at`, 0111 `project`.`changed_at`, 0112 `tcs`.`stores`, 0113 `tcs`.`ancestor_id_path` AS `cat_id_ancestor_path` 0114 FROM `project` 0115 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0116 JOIN `tmp_cat_store` AS `tcs` ON `project`.`project_category_id` = `tcs`.`project_category_id` 0117 WHERE `project_id` = `projectID`; 0118 END$$ 0119 DELIMITER ; 0120 0121 USE `pling-import`; 0122 DROP PROCEDURE IF EXISTS `solr_query_delta`; 0123 DELIMITER $$ 0124 USE `pling-import`$$ 0125 CREATE PROCEDURE `solr_query_delta`(IN `lastIndexed` varchar(255)) 0126 BEGIN 0127 SELECT `project_id` 0128 FROM `project` 0129 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0130 JOIN `project_category` AS `pc` ON `pc`.`project_category_id` = `project`.`project_category_id` 0131 WHERE `project`.`status` = 100 0132 AND `project`.`type_id` = 1 0133 AND `member`.`is_active` = 1 0134 AND `pc`.`is_active` = 1 0135 AND `project`.`changed_at` > `lastIndexed`; 0136 END$$ 0137 DELIMITER ; 0138 0139 0140 USE `pling-import`; 0141 DROP PROCEDURE IF EXISTS `solr_query_deleted_pk`; 0142 DELIMITER $$ 0143 USE `pling-import`$$ 0144 CREATE PROCEDURE `solr_query_deleted_pk`(IN `lastIndexed` VARCHAR(255)) 0145 BEGIN 0146 SELECT `project_id` 0147 FROM `project` 0148 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0149 JOIN `project_category` AS `pc` ON `pc`.`project_category_id` = `project`.`project_category_id` 0150 WHERE `project`.`deleted_at` > `lastIndexed` 0151 OR `member`.`deleted_at` > `lastIndexed` 0152 OR (`project`.`changed_at` > `lastIndexed` AND `project`.`status` < 100); 0153 END$$ 0154 DELIMITER ; 0155 0156 USE `pling-import`; 0157 DELIMITER $$ 0158 DROP TRIGGER IF EXISTS `project_BEFORE_UPDATE`$$ 0159 USE `pling-import`$$ 0160 CREATE DEFINER = CURRENT_USER TRIGGER `project_BEFORE_UPDATE` 0161 BEFORE UPDATE 0162 ON `project` 0163 FOR EACH ROW 0164 BEGIN 0165 SET `NEW`.`changed_at` = NOW(); 0166 END$$ 0167 DELIMITER ; 0168 0169 0170 USE `pling-import`; 0171 DELIMITER $$ 0172 DROP TRIGGER IF EXISTS `member_BEFORE_UPDATE`$$ 0173 USE `pling-import`$$ 0174 CREATE DEFINER = CURRENT_USER TRIGGER `member_BEFORE_UPDATE` 0175 BEFORE UPDATE 0176 ON `member` 0177 FOR EACH ROW 0178 BEGIN 0179 SET `NEW`.`changed_at` = NOW(); 0180 END$$ 0181 DELIMITER ; 0182 0183 USE `pling-import`; 0184 DELIMITER $$ 0185 DROP TRIGGER IF EXISTS `project_category_BEFORE_INSERT`$$ 0186 USE `pling-import`$$ 0187 CREATE DEFINER = CURRENT_USER TRIGGER `project_category_BEFORE_INSERT` 0188 BEFORE INSERT 0189 ON `project_category` 0190 FOR EACH ROW 0191 BEGIN 0192 IF `NEW`.`created_at` IS NULL THEN 0193 SET `NEW`.`created_at` = NOW(); 0194 END IF; 0195 END$$ 0196 DELIMITER ; 0197 0198 USE `pling-import`; 0199 DELIMITER $$ 0200 DROP TRIGGER IF EXISTS `project_category_BEFORE_UPDATE`$$ 0201 USE `pling-import`$$ 0202 CREATE DEFINER = CURRENT_USER TRIGGER `project_category_BEFORE_UPDATE` 0203 BEFORE UPDATE 0204 ON `project_category` 0205 FOR EACH ROW 0206 BEGIN 0207 SET `NEW`.`changed_at` = NOW(); 0208 END$$ 0209 DELIMITER ;