File indexing completed on 2024-04-21 06:00:36
0001 0002 0003 DROP TABLE IF EXISTS `tag`; 0004 CREATE TABLE `tag` 0005 ( 0006 `tag_id` int(11) NOT NULL AUTO_INCREMENT, 0007 `tag_name` varchar(45) NOT NULL, 0008 PRIMARY KEY (`tag_id`), 0009 UNIQUE KEY `idx_name` (`tag_name`) 0010 ) ENGINE = InnoDB; 0011 0012 DROP TABLE IF EXISTS `tag_group`; 0013 CREATE TABLE `tag_group` 0014 ( 0015 `group_id` int(11) NOT NULL AUTO_INCREMENT, 0016 `group_name` varchar(45) NOT NULL, 0017 PRIMARY KEY (`group_id`) 0018 ) ENGINE = InnoDB; 0019 0020 DROP TABLE IF EXISTS `tag_group_item`; 0021 CREATE TABLE `tag_group_item` 0022 ( 0023 `tag_group_item_id` int(11) NOT NULL AUTO_INCREMENT, 0024 `tag_group_id` int(11) NOT NULL, 0025 `tag_id` int(11) NOT NULL, 0026 PRIMARY KEY (`tag_group_item_id`), 0027 KEY `tag_group_idx` (`tag_group_id`), 0028 KEY `tag_idx` (`tag_id`), 0029 CONSTRAINT `tag` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`tag_id`) ON DELETE CASCADE ON UPDATE NO ACTION, 0030 CONSTRAINT `tag_group` FOREIGN KEY (`tag_group_id`) REFERENCES `tag_group` (`group_id`) ON DELETE CASCADE ON UPDATE NO ACTION 0031 ) ENGINE = InnoDB; 0032 0033 DROP TABLE IF EXISTS `tag_object`; 0034 CREATE TABLE `tag_object` 0035 ( 0036 `tag_item_id` int(11) NOT NULL AUTO_INCREMENT, 0037 `tag_id` int(11) NOT NULL, 0038 `tag_type_id` int(11) NOT NULL, 0039 `tag_object_id` int(11) NOT NULL, 0040 `tag_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP, 0041 `tag_changed` datetime DEFAULT NULL, 0042 PRIMARY KEY (`tag_item_id`), 0043 UNIQUE KEY `tags_unique` (`tag_id`, `tag_type_id`, `tag_object_id`), 0044 KEY `tags_idx` (`tag_id`), 0045 KEY `types_idx` (`tag_type_id`) 0046 ) ENGINE = InnoDB; 0047 0048 DELIMITER $$ 0049 DROP TRIGGER IF EXISTS `tag_object_BEFORE_INSERT`$$ 0050 CREATE DEFINER = CURRENT_USER TRIGGER `tag_object_BEFORE_INSERT` 0051 BEFORE INSERT 0052 ON `tag_object` 0053 FOR EACH ROW 0054 BEGIN 0055 IF `NEW`.`tag_changed` IS NULL THEN 0056 SET `NEW`.`tag_changed` = NOW(); 0057 END IF; 0058 END$$ 0059 DELIMITER ; 0060 0061 DROP TABLE IF EXISTS `tag_type`; 0062 CREATE TABLE `tag_type` 0063 ( 0064 `tag_type_id` int(11) NOT NULL AUTO_INCREMENT, 0065 `tag_type_name` varchar(45) NOT NULL, 0066 PRIMARY KEY (`tag_type_id`) 0067 ) ENGINE = InnoDB; 0068 0069 INSERT INTO `tag_type` (`tag_type_id`, `tag_type_name`) 0070 VALUES ('1', 'project'); 0071 INSERT INTO `tag_type` (`tag_type_id`, `tag_type_name`) 0072 VALUES ('2', 'member'); 0073 INSERT INTO `tag_type` (`tag_type_id`, `tag_type_name`) 0074 VALUES ('3', 'file'); 0075 INSERT INTO `tag_type` (`tag_type_id`, `tag_type_name`) 0076 VALUES ('4', 'download'); 0077 INSERT INTO `tag_type` (`tag_type_id`, `tag_type_name`) 0078 VALUES ('5', 'image'); 0079 INSERT INTO `tag_type` (`tag_type_id`, `tag_type_name`) 0080 VALUES ('6', 'video'); 0081 INSERT INTO `tag_type` (`tag_type_id`, `tag_type_name`) 0082 VALUES ('7', 'comment'); 0083 INSERT INTO `tag_type` (`tag_type_id`, `tag_type_name`) 0084 VALUES ('8', 'activity'); 0085 0086 DROP PROCEDURE IF EXISTS `solr_query_import`; 0087 DELIMITER $$ 0088 CREATE PROCEDURE `solr_query_import`() 0089 BEGIN 0090 DROP TABLE IF EXISTS `tmp_project_tags`; 0091 CREATE TEMPORARY TABLE `tmp_project_tags` AS 0092 SELECT GROUP_CONCAT(`tag`.`tag_name`) AS `tag_names`, `tgo`.`tag_object_id` AS `tag_project_id` 0093 FROM `tag_object` AS `tgo` 0094 JOIN `tag` ON `tag`.`tag_id` = `tgo`.`tag_id` 0095 WHERE `tag_type_id` = 1 0096 GROUP BY `tgo`.`tag_object_id` 0097 ORDER BY `tgo`.`tag_object_id`; 0098 0099 DROP TABLE IF EXISTS `tmp_cat_tree`; 0100 CREATE TEMPORARY TABLE `tmp_cat_tree` AS 0101 SELECT `pc`.`project_category_id`, 0102 `pc`.`title`, 0103 `pc`.`is_active`, 0104 count(`pc`.`lft`) AS `depth`, 0105 GROUP_CONCAT(`pc2`.`project_category_id` ORDER BY `pc2`.`lft`) AS `ancestor_id_path`, 0106 GROUP_CONCAT(`pc2`.`title` ORDER BY `pc2`.`lft` SEPARATOR ' | ') AS `ancestor_path` 0107 FROM `project_category` AS `pc`, 0108 `project_category` AS `pc2` 0109 WHERE (`pc`.`lft` BETWEEN `pc2`.`lft` AND `pc2`.`rgt`) 0110 GROUP BY `pc`.`lft` 0111 ORDER BY `pc`.`lft`; 0112 0113 DROP TABLE IF EXISTS `tmp_cat_store`; 0114 CREATE TEMPORARY TABLE `tmp_cat_store` AS 0115 SELECT `tct`.`project_category_id`, 0116 `tct`.`ancestor_id_path`, 0117 `tct`.`title`, 0118 `tct`.`is_active`, 0119 group_concat(`store_id`) AS `stores` 0120 FROM `tmp_cat_tree` AS `tct`, 0121 `config_store_category` AS `csc` 0122 WHERE FIND_IN_SET(`csc`.`project_category_id`, `tct`.`ancestor_id_path`) > 0 0123 GROUP BY `tct`.`project_category_id` 0124 ORDER BY `tct`.`project_category_id`; 0125 0126 DROP TABLE IF EXISTS `solr_project_package_types`; 0127 CREATE TEMPORARY TABLE `solr_project_package_types` 0128 ( 0129 PRIMARY KEY `primary` (`package_project_id`) 0130 ) 0131 ENGINE MyISAM 0132 AS 0133 SELECT `project_id` AS `package_project_id`, 0134 GROUP_CONCAT(DISTINCT `project_package_type`.`package_type_id`) AS `package_type_id_list`, 0135 GROUP_CONCAT(DISTINCT `package_types`.`name`) AS `package_name_list` 0136 FROM `project_package_type` 0137 JOIN 0138 `package_types` ON `project_package_type`.`package_type_id` = `package_types`.`package_type_id` 0139 WHERE `package_types`.`is_active` = 1 0140 GROUP BY `project_id`; 0141 0142 SELECT `project_id`, 0143 `project`.`member_id` AS `project_member_id`, 0144 `project`.`project_category_id` AS `project_category_id`, 0145 `project`.`title` AS `project_title`, 0146 `description`, 0147 `image_small`, 0148 `member`.`username`, 0149 `member`.`firstname`, 0150 `member`.`lastname`, 0151 `tcs`.`title` AS `cat_title`, 0152 `project`.`count_likes` AS `count_likes`, 0153 `project`.`count_dislikes` AS `count_dislikes`, 0154 laplace_score(`project`.`count_likes`, `project`.`count_dislikes`) AS `laplace_score`, 0155 `project`.`created_at`, 0156 `project`.`changed_at`, 0157 `tcs`.`stores`, 0158 `tcs`.`ancestor_id_path` AS `cat_id_ancestor_path`, 0159 `sppt`.`package_type_id_list` AS `package_ids`, 0160 `sppt`.`package_name_list` AS `package_names`, 0161 `t`.`tag_names` AS `tags` 0162 FROM `project` 0163 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0164 JOIN `tmp_cat_store` AS `tcs` ON `project`.`project_category_id` = `tcs`.`project_category_id` 0165 LEFT JOIN `solr_project_package_types` AS `sppt` ON `sppt`.`package_project_id` = `project`.`project_id` 0166 LEFT JOIN `tmp_project_tags` AS `t` ON `t`.`tag_project_id` = `project`.`project_id` 0167 WHERE `project`.`status` = 100 0168 AND `project`.`type_id` = 1 0169 AND `member`.`is_active` = 1 0170 AND `tcs`.`is_active` = 1; 0171 END$$ 0172 DELIMITER ; 0173 0174 DROP PROCEDURE IF EXISTS `solr_query_delta_import`; 0175 DELIMITER $$ 0176 CREATE PROCEDURE `solr_query_delta_import`(IN `projectID` INT(11)) 0177 BEGIN 0178 DROP TABLE IF EXISTS `tmp_project_tags`; 0179 CREATE TEMPORARY TABLE `tmp_project_tags` AS 0180 SELECT GROUP_CONCAT(`tag`.`tag_name`) AS `tag_names`, `tgo`.`tag_object_id` AS `tag_project_id` 0181 FROM `tag_object` AS `tgo` 0182 JOIN `tag` ON `tag`.`tag_id` = `tgo`.`tag_id` 0183 WHERE `tag_type_id` = 1 0184 GROUP BY `tgo`.`tag_object_id` 0185 ORDER BY `tgo`.`tag_object_id`; 0186 0187 DROP TABLE IF EXISTS `tmp_cat_tree`; 0188 CREATE TEMPORARY TABLE `tmp_cat_tree` AS 0189 SELECT `pc`.`project_category_id`, 0190 `pc`.`title`, 0191 `pc`.`is_active`, 0192 count(`pc`.`lft`) AS `depth`, 0193 GROUP_CONCAT(`pc2`.`project_category_id` ORDER BY `pc2`.`lft`) AS `ancestor_id_path`, 0194 GROUP_CONCAT(`pc2`.`title` ORDER BY `pc2`.`lft` SEPARATOR ' | ') AS `ancestor_path` 0195 FROM `project_category` AS `pc`, 0196 `project_category` AS `pc2` 0197 WHERE (`pc`.`lft` BETWEEN `pc2`.`lft` AND `pc2`.`rgt`) 0198 GROUP BY `pc`.`lft` 0199 ORDER BY `pc`.`lft`; 0200 0201 DROP TABLE IF EXISTS `tmp_cat_store`; 0202 CREATE TEMPORARY TABLE `tmp_cat_store` AS 0203 SELECT `tct`.`project_category_id`, 0204 `tct`.`ancestor_id_path`, 0205 `tct`.`title`, 0206 `tct`.`is_active`, 0207 group_concat(`store_id`) AS `stores` 0208 FROM `tmp_cat_tree` AS `tct`, 0209 `config_store_category` AS `csc` 0210 WHERE FIND_IN_SET(`csc`.`project_category_id`, `tct`.`ancestor_id_path`) > 0 0211 GROUP BY `tct`.`project_category_id` 0212 ORDER BY `tct`.`project_category_id`; 0213 0214 DROP TABLE IF EXISTS `solr_project_package_types`; 0215 CREATE TEMPORARY TABLE `solr_project_package_types` 0216 ( 0217 PRIMARY KEY `primary` (`package_project_id`) 0218 ) 0219 ENGINE MyISAM 0220 AS 0221 SELECT `project_id` AS `package_project_id`, 0222 GROUP_CONCAT(DISTINCT `project_package_type`.`package_type_id`) AS `package_type_id_list`, 0223 GROUP_CONCAT(DISTINCT `package_types`.`name`) AS `package_name_list` 0224 FROM `project_package_type` 0225 JOIN 0226 `package_types` ON `project_package_type`.`package_type_id` = `package_types`.`package_type_id` 0227 WHERE `package_types`.`is_active` = 1 0228 GROUP BY `project_id`; 0229 0230 SELECT `project_id`, 0231 `project`.`member_id` AS `project_member_id`, 0232 `project`.`project_category_id` AS `project_category_id`, 0233 `project`.`title` AS `project_title`, 0234 `description`, 0235 `image_small`, 0236 `member`.`username`, 0237 `member`.`firstname`, 0238 `member`.`lastname`, 0239 `tcs`.`title` AS `cat_title`, 0240 `project`.`count_likes` AS `count_likes`, 0241 `project`.`count_dislikes` AS `count_dislikes`, 0242 laplace_score(`project`.`count_likes`, `project`.`count_dislikes`) AS `laplace_score`, 0243 `project`.`created_at`, 0244 `project`.`changed_at`, 0245 `tcs`.`stores`, 0246 `tcs`.`ancestor_id_path` AS `cat_id_ancestor_path`, 0247 `sppt`.`package_type_id_list` AS `package_ids`, 0248 `sppt`.`package_name_list` AS `package_names`, 0249 `t`.`tag_names` AS `tags` 0250 FROM `project` 0251 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0252 JOIN `tmp_cat_store` AS `tcs` ON `project`.`project_category_id` = `tcs`.`project_category_id` 0253 LEFT JOIN `solr_project_package_types` AS `sppt` ON `sppt`.`package_project_id` = `project`.`project_id` 0254 LEFT JOIN `tmp_project_tags` AS `t` ON `t`.`tag_project_id` = `project`.`project_id` 0255 WHERE `project_id` = `projectID`; 0256 END$$ 0257 DELIMITER ; 0258 0259 DROP PROCEDURE IF EXISTS `solr_query_delta`; 0260 DELIMITER $$ 0261 CREATE PROCEDURE `solr_query_delta`(IN `lastIndexed` varchar(255)) 0262 BEGIN 0263 SELECT DISTINCT `project_id` 0264 FROM `project` 0265 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0266 JOIN `project_category` AS `pc` ON `pc`.`project_category_id` = `project`.`project_category_id` 0267 LEFT JOIN `tag_object` AS `tgo` 0268 ON `tgo`.`tag_object_id` = `project`.`project_id` AND `tgo`.`tag_type_id` = 1 0269 WHERE (`project`.`status` = 100 AND `project`.`type_id` = 1 AND `member`.`is_active` = 1 AND 0270 `pc`.`is_active` = 1 AND `project`.`changed_at` > `lastIndexed`) 0271 OR (`project`.`status` = 100 AND `project`.`type_id` = 1 AND `member`.`is_active` = 1 AND 0272 `pc`.`is_active` = 1 AND (`tgo`.`tag_created` > `lastIndexed` OR `tgo`.`tag_changed` > `lastIndexed`)); 0273 END$$ 0274 DELIMITER ; 0275 0276 0277 DROP PROCEDURE IF EXISTS `generate_stat_project`; 0278 DELIMITER $$ 0279 CREATE 0280 DEFINER = CURRENT_USER PROCEDURE `generate_stat_project`() 0281 BEGIN 0282 DROP TABLE IF EXISTS `tmp_reported_projects`; 0283 CREATE TEMPORARY TABLE `tmp_reported_projects` 0284 ( 0285 PRIMARY KEY `primary` (`project_id`) 0286 ) 0287 AS 0288 SELECT `reports_project`.`project_id` AS `project_id`, 0289 COUNT(`reports_project`.`project_id`) AS `amount_reports`, 0290 MAX(`reports_project`.`created_at`) AS `latest_report` 0291 FROM `reports_project` 0292 WHERE (`reports_project`.`is_deleted` = 0) 0293 GROUP BY `reports_project`.`project_id`; 0294 0295 DROP TABLE IF EXISTS `tmp_project_package_types`; 0296 CREATE TEMPORARY TABLE `tmp_project_package_types` 0297 ( 0298 PRIMARY KEY `primary` (`project_id`) 0299 ) 0300 ENGINE MyISAM 0301 AS 0302 SELECT `project_id`, 0303 GROUP_CONCAT(DISTINCT `project_package_type`.`package_type_id`) AS `package_type_id_list`, 0304 GROUP_CONCAT(DISTINCT `package_types`.`name`) AS `package_name_list` 0305 FROM `project_package_type` 0306 JOIN 0307 `package_types` ON `project_package_type`.`package_type_id` = `package_types`.`package_type_id` 0308 WHERE `package_types`.`is_active` = 1 0309 GROUP BY `project_id`; 0310 0311 DROP TABLE IF EXISTS `tmp_project_tags`; 0312 CREATE TEMPORARY TABLE `tmp_project_tags` 0313 ( 0314 PRIMARY KEY `primary` (`tag_project_id`) 0315 ) 0316 ENGINE MyISAM 0317 AS 0318 SELECT GROUP_CONCAT(`tag`.`tag_name`) AS `tag_names`, `tgo`.`tag_object_id` AS `tag_project_id` 0319 FROM `tag_object` AS `tgo` 0320 JOIN `tag` ON `tag`.`tag_id` = `tgo`.`tag_id` 0321 WHERE `tag_type_id` = 1 0322 GROUP BY `tgo`.`tag_object_id` 0323 ORDER BY `tgo`.`tag_object_id`; 0324 0325 0326 DROP TABLE IF EXISTS `tmp_stat_projects`; 0327 CREATE TABLE `tmp_stat_projects` 0328 ( 0329 PRIMARY KEY `primary` (`project_id`), 0330 INDEX `idx_cat` (`project_category_id`) 0331 ) 0332 ENGINE MyISAM 0333 AS 0334 SELECT `project`.`project_id` AS `project_id`, 0335 `project`.`member_id` AS `member_id`, 0336 `project`.`content_type` AS `content_type`, 0337 `project`.`project_category_id` AS `project_category_id`, 0338 `project`.`hive_category_id` AS `hive_category_id`, 0339 `project`.`status` AS `status`, 0340 `project`.`uuid` AS `uuid`, 0341 `project`.`pid` AS `pid`, 0342 `project`.`type_id` AS `type_id`, 0343 `project`.`title` AS `title`, 0344 `project`.`description` AS `description`, 0345 `project`.`version` AS `version`, 0346 `project`.`image_big` AS `image_big`, 0347 `project`.`image_small` AS `image_small`, 0348 `project`.`start_date` AS `start_date`, 0349 `project`.`content_url` AS `content_url`, 0350 `project`.`created_at` AS `created_at`, 0351 `project`.`changed_at` AS `changed_at`, 0352 `project`.`deleted_at` AS `deleted_at`, 0353 `project`.`creator_id` AS `creator_id`, 0354 `project`.`facebook_code` AS `facebook_code`, 0355 `project`.`github_code` AS `github_code`, 0356 `project`.`twitter_code` AS `twitter_code`, 0357 `project`.`google_code` AS `google_code`, 0358 `project`.`link_1` AS `link_1`, 0359 `project`.`embed_code` AS `embed_code`, 0360 `project`.`ppload_collection_id` AS `ppload_collection_id`, 0361 `project`.`validated` AS `validated`, 0362 `project`.`validated_at` AS `validated_at`, 0363 `project`.`featured` AS `featured`, 0364 `project`.`approved` AS `approved`, 0365 `project`.`amount` AS `amount`, 0366 `project`.`amount_period` AS `amount_period`, 0367 `project`.`claimable` AS `claimable`, 0368 `project`.`claimed_by_member` AS `claimed_by_member`, 0369 `project`.`count_likes` AS `count_likes`, 0370 `project`.`count_dislikes` AS `count_dislikes`, 0371 `project`.`count_comments` AS `count_comments`, 0372 `project`.`count_downloads_hive` AS `count_downloads_hive`, 0373 `project`.`source_id` AS `source_id`, 0374 `project`.`source_pk` AS `source_pk`, 0375 `project`.`source_type` AS `source_type`, 0376 `project`.`validated` AS `project_validated`, 0377 `project`.`uuid` AS `project_uuid`, 0378 `project`.`status` AS `project_status`, 0379 `project`.`created_at` AS `project_created_at`, 0380 `project`.`changed_at` AS `project_changed_at`, 0381 laplace_score(`project`.`count_likes`, `project`.`count_dislikes`) AS `laplace_score`, 0382 `member`.`type` AS `member_type`, 0383 `member`.`member_id` AS `project_member_id`, 0384 `member`.`username` AS `username`, 0385 `member`.`profile_image_url` AS `profile_image_url`, 0386 `member`.`city` AS `city`, 0387 `member`.`country` AS `country`, 0388 `member`.`created_at` AS `member_created_at`, 0389 `member`.`paypal_mail` AS `paypal_mail`, 0390 `project_category`.`title` AS `cat_title`, 0391 `project_category`.`xdg_type` AS `cat_xdg_type`, 0392 `project_category`.`name_legacy` AS `cat_name_legacy`, 0393 `project_category`.`show_description` AS `cat_show_description`, 0394 `stat_plings`.`amount_received` AS `amount_received`, 0395 `stat_plings`.`count_plings` AS `count_plings`, 0396 `stat_plings`.`count_plingers` AS `count_plingers`, 0397 `stat_plings`.`latest_pling` AS `latest_pling`, 0398 `trp`.`amount_reports` AS `amount_reports`, 0399 `tppt`.`package_type_id_list` AS `package_types`, 0400 `tppt`.`package_name_list` AS `package_names`, 0401 `t`.`tag_names` AS `tags` 0402 FROM `project` 0403 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0404 JOIN `project_category` ON `project`.`project_category_id` = `project_category`.`project_category_id` 0405 LEFT JOIN `stat_plings` ON `stat_plings`.`project_id` = `project`.`project_id` 0406 LEFT JOIN `tmp_reported_projects` AS `trp` ON `trp`.`project_id` = `project`.`project_id` 0407 LEFT JOIN `tmp_project_package_types` AS `tppt` ON `tppt`.`project_id` = `project`.`project_id` 0408 LEFT JOIN `tmp_project_tags` AS `t` ON `t`.`tag_project_id` = `project`.`project_id` 0409 WHERE `member`.`is_deleted` = 0 0410 AND `member`.`is_active` = 1 0411 AND `project`.`type_id` = 1 0412 AND `project`.`status` = 100 0413 AND `project_category`.`is_active` = 1; 0414 0415 RENAME TABLE `stat_projects` TO `old_stat_projects`, `tmp_stat_projects` TO `stat_projects`; 0416 0417 DROP TABLE IF EXISTS `old_stat_projects`; 0418 END$$ 0419 0420 DELIMITER ;