File indexing completed on 2024-04-21 06:00:36
0001 DROP PROCEDURE `solr_query_fullimport_prepare`; 0002 0003 DELIMITER $$ 0004 CREATE PROCEDURE `solr_query_fullimport_prepare`() 0005 BEGIN 0006 0007 0008 DROP TABLE IF EXISTS `tmp_solr_cat_tree`; 0009 CREATE TEMPORARY TABLE `tmp_solr_cat_tree` 0010 ( 0011 PRIMARY KEY `primary` (`project_category_id`) 0012 ) 0013 AS 0014 SELECT `pc`.`project_category_id`, 0015 `pc`.`title`, 0016 `pc`.`is_active`, 0017 count(`pc`.`lft`) AS `depth`, 0018 GROUP_CONCAT(`pc2`.`project_category_id` ORDER BY `pc2`.`lft`) AS `ancestor_id_path`, 0019 GROUP_CONCAT(`pc2`.`title` ORDER BY `pc2`.`lft` SEPARATOR ' | ') AS `ancestor_path` 0020 FROM `project_category` AS `pc`, 0021 `project_category` AS `pc2` 0022 WHERE (`pc`.`lft` BETWEEN `pc2`.`lft` AND `pc2`.`rgt`) 0023 GROUP BY `pc`.`lft` 0024 ORDER BY `pc`.`lft`; 0025 0026 DROP TABLE IF EXISTS `tmp_solr_cat_store`; 0027 CREATE TEMPORARY TABLE `tmp_solr_cat_store` 0028 ( 0029 PRIMARY KEY `primary` (`project_category_id`) 0030 ) 0031 AS 0032 SELECT `tct`.`project_category_id`, 0033 `tct`.`ancestor_id_path`, 0034 `tct`.`title`, 0035 `tct`.`is_active`, 0036 group_concat(`store_id`) AS `stores` 0037 FROM `tmp_solr_cat_tree` AS `tct`, 0038 `config_store_category` AS `csc` 0039 WHERE FIND_IN_SET(`csc`.`project_category_id`, `tct`.`ancestor_id_path`) > 0 0040 GROUP BY `tct`.`project_category_id` 0041 ORDER BY `tct`.`project_category_id`; 0042 0043 DROP TABLE IF EXISTS `tmp_solr_project_tags`; 0044 CREATE TEMPORARY TABLE `tmp_solr_project_tags` 0045 ( 0046 PRIMARY KEY `primary` (`tag_project_id`) 0047 ) 0048 AS 0049 SELECT GROUP_CONCAT(`tag`.`tag_name`) AS `tag_names` 0050 , `tgo`.`tag_object_id` AS `tag_project_id` 0051 FROM `tag_object` AS `tgo` 0052 JOIN `tag` ON `tag`.`tag_id` = `tgo`.`tag_id` 0053 WHERE `tgo`.`tag_type_id` = 1 0054 AND `tgo`.`tag_group_id` IN (5, 6) 0055 AND `tgo`.`is_deleted` = 0 0056 GROUP BY `tgo`.`tag_object_id`; 0057 0058 DROP TABLE IF EXISTS `tmp_solr_project_license`; 0059 CREATE TEMPORARY TABLE `tmp_solr_project_license` 0060 ( 0061 PRIMARY KEY `primary` (`license_project_id`) 0062 ) 0063 ENGINE MyISAM 0064 AS 0065 SELECT `t`.`tag_object_id` AS `license_project_id`, 0066 GROUP_CONCAT(DISTINCT `ta`.`tag_name`) AS `license_name_list` 0067 FROM `tag_object` `t` 0068 INNER JOIN `tag` `ta` ON `ta`.`tag_id` = `t`.`tag_id` 0069 WHERE `t`.`tag_type_id` = 1 0070 AND `t`.`tag_group_id` = 7 0071 AND `t`.`is_deleted` = 0 0072 GROUP BY `tag_object_id`; 0073 0074 DROP TABLE IF EXISTS `tmp_solr_project_package_types`; 0075 CREATE TEMPORARY TABLE `tmp_solr_project_package_types` 0076 ( 0077 PRIMARY KEY `primary` (`package_project_id`) 0078 ) 0079 ENGINE MyISAM 0080 AS 0081 SELECT `t`.`tag_parent_object_id` AS `package_project_id`, 0082 GROUP_CONCAT(DISTINCT `ta`.`tag_id`) AS `package_type_id_list`, 0083 GROUP_CONCAT(DISTINCT `ta`.`tag_name`) AS `package_name_list` 0084 FROM `tag_object` `t` 0085 INNER JOIN `tag` `ta` ON `ta`.`tag_id` = `t`.`tag_id` 0086 WHERE `t`.`tag_type_id` = 3 0087 AND `t`.`tag_group_id` = 8 0088 AND `t`.`is_deleted` = 0 0089 GROUP BY `tag_parent_object_id`; 0090 0091 DROP TABLE IF EXISTS `tmp_solr_project_arch_types`; 0092 CREATE TEMPORARY TABLE `tmp_solr_project_arch_types` 0093 ( 0094 PRIMARY KEY `primary` (`arch_project_id`) 0095 ) 0096 ENGINE MyISAM 0097 AS 0098 SELECT `t`.`tag_parent_object_id` AS `arch_project_id`, 0099 GROUP_CONCAT(DISTINCT `ta`.`tag_id`) AS `arch_type_id_list`, 0100 GROUP_CONCAT(DISTINCT `ta`.`tag_name`) AS `arch_name_list` 0101 FROM `tag_object` `t` 0102 INNER JOIN `tag` `ta` ON `ta`.`tag_id` = `t`.`tag_id` 0103 WHERE `t`.`tag_type_id` = 3 0104 AND `t`.`tag_group_id` = 9 0105 AND `t`.`is_deleted` = 0 0106 GROUP BY `tag_parent_object_id`; 0107 0108 DROP TABLE IF EXISTS `tmp_solr_query_fullimport`; 0109 CREATE TABLE `tmp_solr_query_fullimport` AS 0110 0111 SELECT `project_id`, 0112 `project`.`member_id` AS `project_member_id`, 0113 `project`.`project_category_id` AS `project_category_id`, 0114 `project`.`title` AS `project_title`, 0115 `description`, 0116 `image_small`, 0117 `member`.`username`, 0118 `member`.`firstname`, 0119 `member`.`lastname`, 0120 `tcs`.`title` AS `cat_title`, 0121 `project`.`count_likes` AS `count_likes`, 0122 `project`.`count_dislikes` AS `count_dislikes`, 0123 laplace_score(`project`.`count_likes`, `project`.`count_dislikes`) AS `laplace_score`, 0124 DATE_FORMAT(`project`.`created_at`, '%Y-%m-%dT%TZ') AS `created_at`, 0125 DATE_FORMAT(`project`.`changed_at`, '%Y-%m-%dT%TZ') AS `changed_at`, 0126 `tcs`.`stores`, 0127 `tcs`.`ancestor_id_path` AS `cat_id_ancestor_path`, 0128 `sppt`.`package_name_list` AS `package_names`, 0129 `appt`.`arch_name_list` AS `arch_names`, 0130 `c`.`license_name_list` AS `license_names`, 0131 `t`.`tag_names` AS `tags` 0132 FROM `project` 0133 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0134 LEFT JOIN `tmp_solr_cat_store` AS `tcs` ON `project`.`project_category_id` = `tcs`.`project_category_id` 0135 LEFT JOIN `tmp_solr_project_package_types` AS `sppt` 0136 ON `sppt`.`package_project_id` = `project`.`project_id` 0137 LEFT JOIN `tmp_solr_project_arch_types` AS `appt` ON `appt`.`arch_project_id` = `project`.`project_id` 0138 LEFT JOIN `tmp_solr_project_license` AS `c` ON `c`.`license_project_id` = `project`.`project_id` 0139 LEFT JOIN `tmp_solr_project_tags` AS `t` ON `t`.`tag_project_id` = `project`.`project_id` 0140 0141 WHERE `project`.`status` = 100 0142 AND `project`.`type_id` = 1 0143 AND `member`.`is_active` = 1 0144 AND `tcs`.`is_active` = 1; 0145 0146 0147 END$$ 0148 0149 DELIMITER ; 0150 0151 0152 CALL solr_query_fullimport_prepare(); 0153 0154 0155 DROP PROCEDURE `solr_query_delta_import_new`; 0156 0157 DELIMITER $$ 0158 CREATE PROCEDURE `solr_query_delta_import_new`(IN `projectID` INT(11)) 0159 BEGIN 0160 0161 DROP TABLE IF EXISTS `tmp_cat_tree`; 0162 CREATE TEMPORARY TABLE `tmp_cat_tree` 0163 ( 0164 PRIMARY KEY `primary` (`project_category_id`) 0165 ) 0166 AS 0167 SELECT `pc`.`project_category_id`, 0168 `pc`.`title`, 0169 `pc`.`is_active`, 0170 count(`pc`.`lft`) AS `depth`, 0171 GROUP_CONCAT(`pc2`.`project_category_id` ORDER BY `pc2`.`lft`) AS `ancestor_id_path`, 0172 GROUP_CONCAT(`pc2`.`title` ORDER BY `pc2`.`lft` SEPARATOR ' | ') AS `ancestor_path` 0173 FROM `project_category` AS `pc`, 0174 `project_category` AS `pc2` 0175 WHERE (`pc`.`lft` BETWEEN `pc2`.`lft` AND `pc2`.`rgt`) 0176 GROUP BY `pc`.`lft` 0177 ORDER BY `pc`.`lft`; 0178 0179 DROP TABLE IF EXISTS `tmp_solr_cat_store`; 0180 CREATE TEMPORARY TABLE `tmp_solr_cat_store` 0181 ( 0182 PRIMARY KEY `primary` (`project_category_id`) 0183 ) 0184 AS 0185 SELECT `tct`.`project_category_id`, 0186 `tct`.`ancestor_id_path`, 0187 `tct`.`title`, 0188 `tct`.`is_active`, 0189 group_concat(`store_id`) AS `stores` 0190 FROM `tmp_cat_tree` AS `tct`, 0191 `config_store_category` AS `csc` 0192 WHERE FIND_IN_SET(`csc`.`project_category_id`, `tct`.`ancestor_id_path`) > 0 0193 GROUP BY `tct`.`project_category_id` 0194 ORDER BY `tct`.`project_category_id`; 0195 0196 SELECT `project_id`, 0197 `project`.`member_id` AS `project_member_id`, 0198 `project`.`project_category_id` AS `project_category_id`, 0199 `project`.`title` AS `project_title`, 0200 `description`, 0201 `image_small`, 0202 `member`.`username`, 0203 `member`.`firstname`, 0204 `member`.`lastname`, 0205 `tcs`.`title` AS `cat_title`, 0206 `project`.`count_likes` AS `count_likes`, 0207 `project`.`count_dislikes` AS `count_dislikes`, 0208 laplace_score(`project`.`count_likes`, `project`.`count_dislikes`) AS `laplace_score`, 0209 DATE_FORMAT(`project`.`created_at`, '%Y-%m-%dT%TZ') AS `created_at`, 0210 DATE_FORMAT(`project`.`changed_at`, '%Y-%m-%dT%TZ') AS `changed_at`, 0211 `tcs`.`stores`, 0212 `tcs`.`ancestor_id_path` AS `cat_id_ancestor_path`, 0213 ( 0214 SELECT GROUP_CONCAT(`tag`.`tag_name`) AS `tag_names` 0215 FROM `tag_object`, 0216 `tag` 0217 WHERE `tag`.`tag_id` = `tag_object`.`tag_id` 0218 AND `tag_object`.`tag_group_id` = 8 0219 AND `tag_object`.`tag_type_id` = 3 0220 AND `tag_object`.`is_deleted` = 0 0221 AND `tag_object`.`tag_parent_object_id` = `project`.`project_id` 0222 ) AS `package_names`, 0223 ( 0224 SELECT GROUP_CONCAT(`tag`.`tag_name`) AS `tag_names` 0225 FROM `tag_object`, 0226 `tag` 0227 WHERE `tag`.`tag_id` = `tag_object`.`tag_id` 0228 AND `tag_object`.`tag_group_id` = 9 0229 AND `tag_object`.`tag_type_id` = 3 0230 AND `tag_object`.`is_deleted` = 0 0231 AND `tag_object`.`tag_parent_object_id` = `project`.`project_id` 0232 ) AS `arch_names`, 0233 ( 0234 SELECT GROUP_CONCAT(`tag`.`tag_name`) AS `tag_names` 0235 FROM `tag_object`, 0236 `tag` 0237 WHERE `tag`.`tag_id` = `tag_object`.`tag_id` 0238 AND `tag_object`.`tag_group_id` = 7 0239 AND `tag_object`.`tag_type_id` = 1 0240 AND `tag_object`.`is_deleted` = 0 0241 AND `tag_object`.`tag_object_id` = `project`.`project_id` 0242 ) AS `license_names`, 0243 ( 0244 SELECT GROUP_CONCAT(`tag`.`tag_name`) AS `tag_names` 0245 FROM `tag_object`, 0246 `tag` 0247 WHERE `tag`.`tag_id` = `tag_object`.`tag_id` 0248 AND `tag_object`.`tag_group_id` IN (5, 6) 0249 AND `tag_object`.`tag_type_id` = 1 0250 AND `tag_object`.`is_deleted` = 0 0251 AND `tag_object`.`tag_object_id` = `project`.`project_id` 0252 ) AS `tags` 0253 FROM `project` 0254 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0255 LEFT JOIN `tmp_solr_cat_store` AS `tcs` ON `project`.`project_category_id` = `tcs`.`project_category_id` 0256 WHERE `project_id` = `projectID`; 0257 0258 0259 END$$ 0260 0261 0262 DELIMITER ; 0263 0264 0265 DROP PROCEDURE `solr_query_import_new`; 0266 0267 DELIMITER $$ 0268 0269 CREATE PROCEDURE `solr_query_import_new`() 0270 BEGIN 0271 SELECT `project_id`, 0272 `project_member_id`, 0273 `project_category_id`, 0274 `project_title`, 0275 `description`, 0276 `image_small`, 0277 `username`, 0278 `firstname`, 0279 `lastname`, 0280 `cat_title`, 0281 `count_likes`, 0282 `count_dislikes`, 0283 `laplace_score`, 0284 `created_at`, 0285 `changed_at`, 0286 `stores`, 0287 `cat_id_ancestor_path`, 0288 `package_names`, 0289 `arch_names`, 0290 `license_names`, 0291 `tags` 0292 FROM `tmp_solr_query_fullimport`; 0293 END$$ 0294 0295 DELIMITER ; 0296 0297 DROP PROCEDURE `solr_query_delta_new`; 0298 0299 DELIMITER $$ 0300 CREATE PROCEDURE `solr_query_delta_new`(IN `lastIndexed` varchar(255)) 0301 BEGIN 0302 SELECT DISTINCT `project_id` 0303 FROM ( 0304 SELECT `project_id` 0305 FROM `project` 0306 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0307 WHERE (`project`.`status` = 100 AND `project`.`type_id` = 1 AND `member`.`is_active` = 1 AND 0308 `project`.`changed_at` > `lastIndexed`) 0309 UNION 0310 SELECT DISTINCT `tag_object_id` AS `project_id` 0311 FROM `tag_object` 0312 WHERE `tag_type_id` = 1 0313 AND (`tag_created` > `lastIndexed` OR `tag_changed` > `lastIndexed`) 0314 UNION 0315 SELECT DISTINCT `tag_parent_object_id` AS `project_id` 0316 FROM `tag_object` 0317 WHERE `tag_type_id` IN (8, 9) 0318 AND (`tag_created` > `lastIndexed` OR `tag_changed` > `lastIndexed`) 0319 ) `t`; 0320 END$$ 0321 0322 DELIMITER ; 0323 0324 DROP PROCEDURE `solr_query_deleted_pk_new`; 0325 DELIMITER $$ 0326 CREATE PROCEDURE `solr_query_deleted_pk_new`(IN `lastIndexed` VARCHAR(255)) 0327 BEGIN 0328 SELECT `project_id` 0329 FROM `project` 0330 WHERE `project`.`type_id` = 1 0331 AND ( 0332 `project`.`deleted_at` > timestamp(DATE_SUB(`lastIndexed`, INTERVAL 1 DAY)) 0333 OR (`project`.`changed_at` > timestamp(DATE_SUB(`lastIndexed`, INTERVAL 1 DAY)) AND 0334 `project`.`status` < 100) 0335 ); 0336 0337 END$$ 0338 0339 DELIMITER ;