File indexing completed on 2024-04-21 06:00:37
0001 0002 0003 DELIMITER $$ 0004 DROP PROCEDURE IF EXISTS `solr_query_fullimport_prepare`; 0005 CREATE PROCEDURE `solr_query_fullimport_prepare`() 0006 BEGIN 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`.`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 IFNULL(`r`.`likes`, 0) AS `count_likes`, 0124 IFNULL(`r`.`dislikes`, 0) AS `count_dislikes`, 0125 IFNULL(`r`.`score_with_pling`, 500) 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 LEFT JOIN `stat_rating_project` `r` ON `r`.`project_id` = `project`.`project_id` 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 DELIMITER ; 0151 0152 0153 DELIMITER $$ 0154 DROP PROCEDURE `solr_query_delta_import_new`; 0155 CREATE PROCEDURE `solr_query_delta_import_new`(IN `projectID` INT(11)) 0156 BEGIN 0157 0158 DROP TABLE IF EXISTS `tmp_cat_tree`; 0159 CREATE TEMPORARY TABLE `tmp_cat_tree` 0160 ( 0161 PRIMARY KEY `primary` (`project_category_id`) 0162 ) 0163 AS 0164 SELECT `pc`.`project_category_id`, 0165 `pc`.`title`, 0166 `pc`.`is_active`, 0167 count(`pc`.`lft`) AS `depth`, 0168 GROUP_CONCAT(`pc2`.`project_category_id` ORDER BY `pc2`.`lft`) AS `ancestor_id_path`, 0169 GROUP_CONCAT(`pc2`.`title` ORDER BY `pc2`.`lft` SEPARATOR ' | ') AS `ancestor_path` 0170 FROM `project_category` AS `pc`, 0171 `project_category` AS `pc2` 0172 WHERE (`pc`.`lft` BETWEEN `pc2`.`lft` AND `pc2`.`rgt`) 0173 GROUP BY `pc`.`lft` 0174 ORDER BY `pc`.`lft`; 0175 0176 DROP TABLE IF EXISTS `tmp_solr_cat_store`; 0177 CREATE TEMPORARY TABLE `tmp_solr_cat_store` 0178 ( 0179 PRIMARY KEY `primary` (`project_category_id`) 0180 ) 0181 AS 0182 SELECT `tct`.`project_category_id`, 0183 `tct`.`ancestor_id_path`, 0184 `tct`.`title`, 0185 `tct`.`is_active`, 0186 group_concat(`store_id`) AS `stores` 0187 FROM `tmp_cat_tree` AS `tct`, 0188 `config_store_category` AS `csc` 0189 WHERE FIND_IN_SET(`csc`.`project_category_id`, `tct`.`ancestor_id_path`) > 0 0190 GROUP BY `tct`.`project_category_id` 0191 ORDER BY `tct`.`project_category_id`; 0192 0193 SELECT `project`.`project_id`, 0194 `project`.`member_id` AS `project_member_id`, 0195 `project`.`project_category_id` AS `project_category_id`, 0196 `project`.`title` AS `project_title`, 0197 `description`, 0198 `image_small`, 0199 `member`.`username`, 0200 `member`.`firstname`, 0201 `member`.`lastname`, 0202 `tcs`.`title` AS `cat_title`, 0203 IFNULL(`r`.`likes`, 0) AS `count_likes`, 0204 IFNULL(`r`.`dislikes`, 0) AS `count_dislikes`, 0205 IFNULL(`r`.`score_with_pling`, 500) AS `laplace_score`, 0206 DATE_FORMAT(`project`.`created_at`, '%Y-%m-%dT%TZ') AS `created_at`, 0207 DATE_FORMAT(`project`.`changed_at`, '%Y-%m-%dT%TZ') AS `changed_at`, 0208 `tcs`.`stores`, 0209 `tcs`.`ancestor_id_path` AS `cat_id_ancestor_path`, 0210 ( 0211 SELECT GROUP_CONCAT(`tag`.`tag_name`) AS `tag_names` 0212 FROM `tag_object`, 0213 `tag`, 0214 `ppload`.`ppload_files` `files` 0215 WHERE `tag`.`tag_id` = `tag_object`.`tag_id` 0216 AND `tag_object`.`tag_group_id` = 8 0217 AND `tag_object`.`tag_type_id` = 3 0218 AND `tag_object`.`is_deleted` = 0 0219 AND `tag_object`.`tag_parent_object_id` = `project`.`project_id` 0220 AND `tag_object`.`tag_object_id` = `files`.`id` 0221 AND `files`.`active` = 1 0222 ) AS `package_names`, 0223 ( 0224 SELECT GROUP_CONCAT(`tag`.`tag_name`) AS `tag_names` 0225 FROM `tag_object`, 0226 `tag`, 0227 `ppload`.`ppload_files` `files` 0228 WHERE `tag`.`tag_id` = `tag_object`.`tag_id` 0229 AND `tag_object`.`tag_group_id` = 9 0230 AND `tag_object`.`tag_type_id` = 3 0231 AND `tag_object`.`is_deleted` = 0 0232 AND `tag_object`.`tag_parent_object_id` = `project`.`project_id` 0233 AND `tag_object`.`tag_object_id` = `files`.`id` 0234 AND `files`.`active` = 1 0235 ) AS `arch_names`, 0236 ( 0237 SELECT GROUP_CONCAT(`tag`.`tag_name`) AS `tag_names` 0238 FROM `tag_object`, 0239 `tag` 0240 WHERE `tag`.`tag_id` = `tag_object`.`tag_id` 0241 AND `tag_object`.`tag_group_id` = 7 0242 AND `tag_object`.`tag_type_id` = 1 0243 AND `tag_object`.`is_deleted` = 0 0244 AND `tag_object`.`tag_object_id` = `project`.`project_id` 0245 ) AS `license_names`, 0246 ( 0247 SELECT GROUP_CONCAT(`tag`.`tag_name`) AS `tag_names` 0248 FROM `tag_object`, 0249 `tag` 0250 WHERE `tag`.`tag_id` = `tag_object`.`tag_id` 0251 AND `tag_object`.`tag_group_id` IN (5, 6) 0252 AND `tag_object`.`tag_type_id` = 1 0253 AND `tag_object`.`is_deleted` = 0 0254 AND `tag_object`.`tag_object_id` = `project`.`project_id` 0255 ) AS `tags` 0256 FROM `project` 0257 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0258 LEFT JOIN `tmp_solr_cat_store` AS `tcs` ON `project`.`project_category_id` = `tcs`.`project_category_id` 0259 LEFT JOIN `stat_rating_project` `r` ON `r`.`project_id` = `project`.`project_id` 0260 WHERE `project`.`project_id` = `projectID` 0261 AND `project`.`status` = 100 0262 AND `member`.`is_active` = 1; 0263 0264 0265 END$$ 0266 DELIMITER ; 0267 0268 0269 DELIMITER $$ 0270 DROP PROCEDURE `solr_query_delta_new`; 0271 CREATE PROCEDURE `solr_query_delta_new`(IN `lastIndexed` varchar(255)) 0272 BEGIN 0273 SELECT DISTINCT `project_id` 0274 FROM ( 0275 SELECT `project_id` 0276 FROM `project` 0277 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0278 WHERE (`project`.`status` = 100 AND `project`.`type_id` = 1 AND `member`.`is_active` = 1 AND 0279 `project`.`changed_at` > timestamp(DATE_SUB(`lastIndexed`, INTERVAL 1 DAY))) 0280 UNION 0281 SELECT DISTINCT `tag_object_id` AS `project_id` 0282 FROM `tag_object` 0283 WHERE `tag_type_id` = 1 0284 AND (`tag_created` > timestamp(DATE_SUB(`lastIndexed`, INTERVAL 1 DAY)) OR 0285 `tag_changed` > timestamp(DATE_SUB(`lastIndexed`, INTERVAL 1 DAY))) 0286 UNION 0287 SELECT DISTINCT `tag_parent_object_id` AS `project_id` 0288 FROM `tag_object` 0289 WHERE `tag_type_id` IN (8, 9) 0290 AND (`tag_created` > timestamp(DATE_SUB(`lastIndexed`, INTERVAL 1 DAY)) OR 0291 `tag_changed` > timestamp(DATE_SUB(`lastIndexed`, INTERVAL 1 DAY))) 0292 UNION 0293 SELECT `project_id` 0294 FROM `project` 0295 JOIN `ppload`.`ppload_files` `files` 0296 ON `project`.`ppload_collection_id` = `files`.`collection_id` AND 0297 `files`.`updated_timestamp` > timestamp(DATE_SUB(`lastIndexed`, INTERVAL 1 DAY)) 0298 GROUP BY `project_id` 0299 UNION 0300 SELECT `project_id` 0301 FROM `project_rating` 0302 WHERE `created_at` > timestamp(DATE_SUB(`lastIndexed`, INTERVAL 1 DAY)) 0303 GROUP BY `project_id` 0304 ) `t`; 0305 END$$ 0306 DELIMITER ;