File indexing completed on 2024-04-14 05:53:06
0001 INSERT INTO `member_role` (`member_role_id`, `title`, `shortname`, `is_active`) 0002 VALUES ('400', 'Moderator', 'moderator', '1'); 0003 0004 ALTER TABLE `project` 0005 CHANGE COLUMN `approved` `ghns_excluded` INT(1) NULL DEFAULT '0' AFTER `featured`; 0006 0007 DROP PROCEDURE IF EXISTS `generate_stat_project`; 0008 0009 DELIMITER $$ 0010 CREATE 0011 DEFINER = CURRENT_USER PROCEDURE `generate_stat_project`() 0012 BEGIN 0013 DROP TABLE IF EXISTS `tmp_reported_projects`; 0014 CREATE TEMPORARY TABLE `tmp_reported_projects` 0015 ( 0016 PRIMARY KEY `primary` (`project_id`) 0017 ) 0018 AS 0019 SELECT `reports_project`.`project_id` AS `project_id`, 0020 COUNT(`reports_project`.`project_id`) AS `amount_reports`, 0021 MAX(`reports_project`.`created_at`) AS `latest_report` 0022 FROM `reports_project` 0023 WHERE (`reports_project`.`is_deleted` = 0) 0024 GROUP BY `reports_project`.`project_id`; 0025 0026 DROP TABLE IF EXISTS `tmp_project_package_types`; 0027 CREATE TEMPORARY TABLE `tmp_project_package_types` 0028 ( 0029 PRIMARY KEY `primary` (`project_id`) 0030 ) 0031 ENGINE MyISAM 0032 AS 0033 SELECT `project_id`, 0034 GROUP_CONCAT(DISTINCT `project_package_type`.`package_type_id`) AS `package_type_id_list`, 0035 GROUP_CONCAT(DISTINCT `package_types`.`name`) AS `package_name_list` 0036 FROM `project_package_type` 0037 JOIN 0038 `package_types` ON `project_package_type`.`package_type_id` = `package_types`.`package_type_id` 0039 WHERE `package_types`.`is_active` = 1 0040 GROUP BY `project_id`; 0041 0042 DROP TABLE IF EXISTS `tmp_project_tags`; 0043 CREATE TEMPORARY TABLE `tmp_project_tags` 0044 ( 0045 PRIMARY KEY `primary` (`tag_project_id`) 0046 ) 0047 ENGINE MyISAM 0048 AS 0049 SELECT GROUP_CONCAT(`tag`.`tag_name`) AS `tag_names`, `tgo`.`tag_object_id` AS `tag_project_id` 0050 FROM `tag_object` AS `tgo` 0051 JOIN `tag` ON `tag`.`tag_id` = `tgo`.`tag_id` 0052 WHERE `tag_type_id` = 1 0053 GROUP BY `tgo`.`tag_object_id` 0054 ORDER BY `tgo`.`tag_object_id`; 0055 0056 DROP TABLE IF EXISTS `tmp_stat_projects`; 0057 CREATE TABLE `tmp_stat_projects` 0058 ( 0059 PRIMARY KEY `primary` (`project_id`), 0060 INDEX `idx_cat` (`project_category_id`) 0061 ) 0062 ENGINE MyISAM 0063 AS 0064 SELECT `project`.`project_id` AS `project_id`, 0065 `project`.`member_id` AS `member_id`, 0066 `project`.`content_type` AS `content_type`, 0067 `project`.`project_category_id` AS `project_category_id`, 0068 `project`.`hive_category_id` AS `hive_category_id`, 0069 `project`.`status` AS `status`, 0070 `project`.`uuid` AS `uuid`, 0071 `project`.`pid` AS `pid`, 0072 `project`.`type_id` AS `type_id`, 0073 `project`.`title` AS `title`, 0074 `project`.`description` AS `description`, 0075 `project`.`version` AS `version`, 0076 `project`.`image_big` AS `image_big`, 0077 `project`.`image_small` AS `image_small`, 0078 `project`.`start_date` AS `start_date`, 0079 `project`.`content_url` AS `content_url`, 0080 `project`.`created_at` AS `created_at`, 0081 `project`.`changed_at` AS `changed_at`, 0082 `project`.`deleted_at` AS `deleted_at`, 0083 `project`.`creator_id` AS `creator_id`, 0084 `project`.`facebook_code` AS `facebook_code`, 0085 `project`.`github_code` AS `github_code`, 0086 `project`.`twitter_code` AS `twitter_code`, 0087 `project`.`google_code` AS `google_code`, 0088 `project`.`link_1` AS `link_1`, 0089 `project`.`embed_code` AS `embed_code`, 0090 `project`.`ppload_collection_id` AS `ppload_collection_id`, 0091 `project`.`validated` AS `validated`, 0092 `project`.`validated_at` AS `validated_at`, 0093 `project`.`featured` AS `featured`, 0094 `project`.`ghns_excluded` AS `ghns_excluded`, 0095 `project`.`amount` AS `amount`, 0096 `project`.`amount_period` AS `amount_period`, 0097 `project`.`claimable` AS `claimable`, 0098 `project`.`claimed_by_member` AS `claimed_by_member`, 0099 `project`.`count_likes` AS `count_likes`, 0100 `project`.`count_dislikes` AS `count_dislikes`, 0101 `project`.`count_comments` AS `count_comments`, 0102 `project`.`count_downloads_hive` AS `count_downloads_hive`, 0103 `project`.`source_id` AS `source_id`, 0104 `project`.`source_pk` AS `source_pk`, 0105 `project`.`source_type` AS `source_type`, 0106 `project`.`validated` AS `project_validated`, 0107 `project`.`uuid` AS `project_uuid`, 0108 `project`.`status` AS `project_status`, 0109 `project`.`created_at` AS `project_created_at`, 0110 `project`.`changed_at` AS `project_changed_at`, 0111 laplace_score(`project`.`count_likes`, `project`.`count_dislikes`) AS `laplace_score`, 0112 `member`.`type` AS `member_type`, 0113 `member`.`member_id` AS `project_member_id`, 0114 `member`.`username` AS `username`, 0115 `member`.`profile_image_url` AS `profile_image_url`, 0116 `member`.`city` AS `city`, 0117 `member`.`country` AS `country`, 0118 `member`.`created_at` AS `member_created_at`, 0119 `member`.`paypal_mail` AS `paypal_mail`, 0120 `project_category`.`title` AS `cat_title`, 0121 `project_category`.`xdg_type` AS `cat_xdg_type`, 0122 `project_category`.`name_legacy` AS `cat_name_legacy`, 0123 `project_category`.`show_description` AS `cat_show_description`, 0124 `stat_plings`.`amount_received` AS `amount_received`, 0125 `stat_plings`.`count_plings` AS `count_plings`, 0126 `stat_plings`.`count_plingers` AS `count_plingers`, 0127 `stat_plings`.`latest_pling` AS `latest_pling`, 0128 `trp`.`amount_reports` AS `amount_reports`, 0129 `tppt`.`package_type_id_list` AS `package_types`, 0130 `tppt`.`package_name_list` AS `package_names`, 0131 `t`.`tag_names` AS `tags`, 0132 `sdqy`.`amount` AS `count_downloads_quarter` 0133 FROM `project` 0134 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0135 JOIN `project_category` ON `project`.`project_category_id` = `project_category`.`project_category_id` 0136 LEFT JOIN `stat_plings` ON `stat_plings`.`project_id` = `project`.`project_id` 0137 LEFT JOIN `tmp_reported_projects` AS `trp` ON `trp`.`project_id` = `project`.`project_id` 0138 LEFT JOIN `tmp_project_package_types` AS `tppt` ON `tppt`.`project_id` = `project`.`project_id` 0139 LEFT JOIN `tmp_project_tags` AS `t` ON `t`.`tag_project_id` = `project`.`project_id` 0140 LEFT JOIN `stat_downloads_quarter_year` AS `sdqy` ON `sdqy`.`project_id` = `project`.`project_id` 0141 WHERE `member`.`is_deleted` = 0 0142 AND `member`.`is_active` = 1 0143 AND `project`.`type_id` = 1 0144 AND `project`.`status` = 100 0145 AND `project_category`.`is_active` = 1; 0146 0147 RENAME TABLE `stat_projects` TO `old_stat_projects`, `tmp_stat_projects` TO `stat_projects`; 0148 0149 DROP TABLE IF EXISTS `old_stat_projects`; 0150 END$$ 0151 0152 DELIMITER ;