File indexing completed on 2024-03-24 06:03:44
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 JOIN `ppload`.`ppload_files` `files` ON `files`.`id` = `t`.`tag_object_id` AND `files`.`active` = 1 0087 WHERE `t`.`tag_type_id` = 3 0088 AND `t`.`tag_group_id` = 8 0089 AND `t`.`is_deleted` = 0 0090 GROUP BY `tag_parent_object_id`; 0091 0092 DROP TABLE IF EXISTS `tmp_solr_project_arch_types`; 0093 CREATE TEMPORARY TABLE `tmp_solr_project_arch_types` 0094 ( 0095 PRIMARY KEY `primary` (`arch_project_id`) 0096 ) 0097 ENGINE MyISAM 0098 AS 0099 SELECT `t`.`tag_parent_object_id` AS `arch_project_id`, 0100 GROUP_CONCAT(DISTINCT `ta`.`tag_id`) AS `arch_type_id_list`, 0101 GROUP_CONCAT(DISTINCT `ta`.`tag_name`) AS `arch_name_list` 0102 FROM `tag_object` `t` 0103 INNER JOIN `tag` `ta` ON `ta`.`tag_id` = `t`.`tag_id` 0104 JOIN `ppload`.`ppload_files` `files` ON `files`.`id` = `t`.`tag_object_id` AND `files`.`active` = 1 0105 WHERE `t`.`tag_type_id` = 3 0106 AND `t`.`tag_group_id` = 9 0107 AND `t`.`is_deleted` = 0 0108 GROUP BY `tag_parent_object_id`; 0109 0110 DROP TABLE IF EXISTS `tmp_solr_query_fullimport`; 0111 CREATE TABLE `tmp_solr_query_fullimport` AS 0112 0113 SELECT `project_id`, 0114 `project`.`member_id` AS `project_member_id`, 0115 `project`.`project_category_id` AS `project_category_id`, 0116 `project`.`title` AS `project_title`, 0117 `description`, 0118 `image_small`, 0119 `member`.`username`, 0120 `member`.`firstname`, 0121 `member`.`lastname`, 0122 `tcs`.`title` AS `cat_title`, 0123 `project`.`count_likes` AS `count_likes`, 0124 `project`.`count_dislikes` AS `count_dislikes`, 0125 laplace_score(`project`.`count_likes`, `project`.`count_dislikes`) AS `laplace_score`, 0126 DATE_FORMAT(`project`.`created_at`, '%Y-%m-%dT%TZ') AS `created_at`, 0127 DATE_FORMAT(`project`.`changed_at`, '%Y-%m-%dT%TZ') AS `changed_at`, 0128 `tcs`.`stores`, 0129 `tcs`.`ancestor_id_path` AS `cat_id_ancestor_path`, 0130 `sppt`.`package_name_list` AS `package_names`, 0131 `appt`.`arch_name_list` AS `arch_names`, 0132 `c`.`license_name_list` AS `license_names`, 0133 `t`.`tag_names` AS `tags` 0134 FROM `project` 0135 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0136 LEFT JOIN `tmp_solr_cat_store` AS `tcs` ON `project`.`project_category_id` = `tcs`.`project_category_id` 0137 LEFT JOIN `tmp_solr_project_package_types` AS `sppt` 0138 ON `sppt`.`package_project_id` = `project`.`project_id` 0139 LEFT JOIN `tmp_solr_project_arch_types` AS `appt` ON `appt`.`arch_project_id` = `project`.`project_id` 0140 LEFT JOIN `tmp_solr_project_license` AS `c` ON `c`.`license_project_id` = `project`.`project_id` 0141 LEFT JOIN `tmp_solr_project_tags` AS `t` ON `t`.`tag_project_id` = `project`.`project_id` 0142 0143 WHERE `project`.`status` = 100 0144 AND `project`.`type_id` = 1 0145 AND `member`.`is_active` = 1 0146 AND `tcs`.`is_active` = 1; 0147 0148 0149 END$$ 0150 0151 DELIMITER ; 0152 0153 0154 CALL solr_query_fullimport_prepare(); 0155 0156 0157 DROP PROCEDURE `solr_query_delta_import_new`; 0158 0159 DELIMITER $$ 0160 CREATE PROCEDURE `solr_query_delta_import_new`(IN `projectID` INT(11)) 0161 BEGIN 0162 0163 DROP TABLE IF EXISTS `tmp_cat_tree`; 0164 CREATE TEMPORARY TABLE `tmp_cat_tree` 0165 ( 0166 PRIMARY KEY `primary` (`project_category_id`) 0167 ) 0168 AS 0169 SELECT `pc`.`project_category_id`, 0170 `pc`.`title`, 0171 `pc`.`is_active`, 0172 count(`pc`.`lft`) AS `depth`, 0173 GROUP_CONCAT(`pc2`.`project_category_id` ORDER BY `pc2`.`lft`) AS `ancestor_id_path`, 0174 GROUP_CONCAT(`pc2`.`title` ORDER BY `pc2`.`lft` SEPARATOR ' | ') AS `ancestor_path` 0175 FROM `project_category` AS `pc`, 0176 `project_category` AS `pc2` 0177 WHERE (`pc`.`lft` BETWEEN `pc2`.`lft` AND `pc2`.`rgt`) 0178 GROUP BY `pc`.`lft` 0179 ORDER BY `pc`.`lft`; 0180 0181 DROP TABLE IF EXISTS `tmp_solr_cat_store`; 0182 CREATE TEMPORARY TABLE `tmp_solr_cat_store` 0183 ( 0184 PRIMARY KEY `primary` (`project_category_id`) 0185 ) 0186 AS 0187 SELECT `tct`.`project_category_id`, 0188 `tct`.`ancestor_id_path`, 0189 `tct`.`title`, 0190 `tct`.`is_active`, 0191 group_concat(`store_id`) AS `stores` 0192 FROM `tmp_cat_tree` AS `tct`, 0193 `config_store_category` AS `csc` 0194 WHERE FIND_IN_SET(`csc`.`project_category_id`, `tct`.`ancestor_id_path`) > 0 0195 GROUP BY `tct`.`project_category_id` 0196 ORDER BY `tct`.`project_category_id`; 0197 0198 SELECT `project_id`, 0199 `project`.`member_id` AS `project_member_id`, 0200 `project`.`project_category_id` AS `project_category_id`, 0201 `project`.`title` AS `project_title`, 0202 `description`, 0203 `image_small`, 0204 `member`.`username`, 0205 `member`.`firstname`, 0206 `member`.`lastname`, 0207 `tcs`.`title` AS `cat_title`, 0208 `project`.`count_likes` AS `count_likes`, 0209 `project`.`count_dislikes` AS `count_dislikes`, 0210 laplace_score(`project`.`count_likes`, `project`.`count_dislikes`) AS `laplace_score`, 0211 DATE_FORMAT(`project`.`created_at`, '%Y-%m-%dT%TZ') AS `created_at`, 0212 DATE_FORMAT(`project`.`changed_at`, '%Y-%m-%dT%TZ') AS `changed_at`, 0213 `tcs`.`stores`, 0214 `tcs`.`ancestor_id_path` AS `cat_id_ancestor_path`, 0215 ( 0216 SELECT GROUP_CONCAT(`tag`.`tag_name`) AS `tag_names` 0217 FROM `tag_object`, 0218 `tag`, 0219 `ppload`.`ppload_files` `files` 0220 WHERE `tag`.`tag_id` = `tag_object`.`tag_id` 0221 AND `tag_object`.`tag_group_id` = 8 0222 AND `tag_object`.`tag_type_id` = 3 0223 AND `tag_object`.`is_deleted` = 0 0224 AND `tag_object`.`tag_parent_object_id` = `project`.`project_id` 0225 AND `tag_object`.`tag_object_id` = `files`.`id` 0226 AND `files`.`active` = 1 0227 ) AS `package_names`, 0228 ( 0229 SELECT GROUP_CONCAT(`tag`.`tag_name`) AS `tag_names` 0230 FROM `tag_object`, 0231 `tag`, 0232 `ppload`.`ppload_files` `files` 0233 WHERE `tag`.`tag_id` = `tag_object`.`tag_id` 0234 AND `tag_object`.`tag_group_id` = 9 0235 AND `tag_object`.`tag_type_id` = 3 0236 AND `tag_object`.`is_deleted` = 0 0237 AND `tag_object`.`tag_parent_object_id` = `project`.`project_id` 0238 AND `tag_object`.`tag_object_id` = `files`.`id` 0239 AND `files`.`active` = 1 0240 ) AS `arch_names`, 0241 ( 0242 SELECT GROUP_CONCAT(`tag`.`tag_name`) AS `tag_names` 0243 FROM `tag_object`, 0244 `tag` 0245 WHERE `tag`.`tag_id` = `tag_object`.`tag_id` 0246 AND `tag_object`.`tag_group_id` = 7 0247 AND `tag_object`.`tag_type_id` = 1 0248 AND `tag_object`.`is_deleted` = 0 0249 AND `tag_object`.`tag_object_id` = `project`.`project_id` 0250 ) AS `license_names`, 0251 ( 0252 SELECT GROUP_CONCAT(`tag`.`tag_name`) AS `tag_names` 0253 FROM `tag_object`, 0254 `tag` 0255 WHERE `tag`.`tag_id` = `tag_object`.`tag_id` 0256 AND `tag_object`.`tag_group_id` IN (5, 6) 0257 AND `tag_object`.`tag_type_id` = 1 0258 AND `tag_object`.`is_deleted` = 0 0259 AND `tag_object`.`tag_object_id` = `project`.`project_id` 0260 ) AS `tags` 0261 FROM `project` 0262 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0263 LEFT JOIN `tmp_solr_cat_store` AS `tcs` ON `project`.`project_category_id` = `tcs`.`project_category_id` 0264 WHERE `project_id` = `projectID` 0265 AND `project`.`status` = 100 0266 AND `member`.`is_active` = 1; 0267 0268 0269 END$$ 0270 0271 0272 DELIMITER ; 0273 0274 0275 DROP PROCEDURE `solr_query_import_new`; 0276 0277 DELIMITER $$ 0278 0279 CREATE PROCEDURE `solr_query_import_new`() 0280 BEGIN 0281 SELECT `project_id`, 0282 `project_member_id`, 0283 `project_category_id`, 0284 `project_title`, 0285 `description`, 0286 `image_small`, 0287 `username`, 0288 `firstname`, 0289 `lastname`, 0290 `cat_title`, 0291 `count_likes`, 0292 `count_dislikes`, 0293 `laplace_score`, 0294 `created_at`, 0295 `changed_at`, 0296 `stores`, 0297 `cat_id_ancestor_path`, 0298 `package_names`, 0299 `arch_names`, 0300 `license_names`, 0301 `tags` 0302 FROM `tmp_solr_query_fullimport`; 0303 END$$ 0304 0305 DELIMITER ; 0306 0307 DROP PROCEDURE `solr_query_delta_new`; 0308 0309 DELIMITER $$ 0310 CREATE PROCEDURE `solr_query_delta_new`(IN `lastIndexed` varchar(255)) 0311 BEGIN 0312 SELECT DISTINCT `project_id` 0313 FROM ( 0314 SELECT `project_id` 0315 FROM `project` 0316 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0317 WHERE (`project`.`status` = 100 AND `project`.`type_id` = 1 AND `member`.`is_active` = 1 AND 0318 `project`.`changed_at` > timestamp(DATE_SUB(`lastIndexed`, INTERVAL 1 DAY))) 0319 UNION 0320 SELECT DISTINCT `tag_object_id` AS `project_id` 0321 FROM `tag_object` 0322 WHERE `tag_type_id` = 1 0323 AND (`tag_created` > timestamp(DATE_SUB(`lastIndexed`, INTERVAL 1 DAY)) OR 0324 `tag_changed` > timestamp(DATE_SUB(`lastIndexed`, INTERVAL 1 DAY))) 0325 UNION 0326 SELECT DISTINCT `tag_parent_object_id` AS `project_id` 0327 FROM `tag_object` 0328 WHERE `tag_type_id` IN (8, 9) 0329 AND (`tag_created` > timestamp(DATE_SUB(`lastIndexed`, INTERVAL 1 DAY)) OR 0330 `tag_changed` > timestamp(DATE_SUB(`lastIndexed`, INTERVAL 1 DAY))) 0331 UNION 0332 SELECT `project_id` 0333 FROM `project` 0334 JOIN `ppload`.`ppload_files` `files` 0335 ON `project`.`ppload_collection_id` = `files`.`collection_id` AND 0336 `files`.`updated_timestamp` > timestamp(DATE_SUB(`lastIndexed`, INTERVAL 1 DAY)) 0337 GROUP BY `project_id` 0338 ) `t`; 0339 END$$ 0340 0341 DELIMITER ; 0342 0343 0344 DROP PROCEDURE `solr_query_deleted_pk_new`; 0345 DELIMITER $$ 0346 CREATE PROCEDURE `solr_query_deleted_pk_new`(IN `lastIndexed` VARCHAR(255)) 0347 BEGIN 0348 SELECT `project_id` 0349 FROM `project` 0350 WHERE `project`.`type_id` = 1 0351 AND ( 0352 `project`.`deleted_at` > timestamp(DATE_SUB(`lastIndexed`, INTERVAL 1 DAY)) 0353 OR (`project`.`changed_at` > timestamp(DATE_SUB(`lastIndexed`, INTERVAL 1 DAY)) AND 0354 `project`.`status` < 100) 0355 ); 0356 0357 END$$ 0358 0359 DELIMITER ; 0360