File indexing completed on 2024-04-28 17:16:08
0001 ALTER TABLE `project_category` 0002 ADD COLUMN `show_description` INT(1) NOT NULL DEFAULT '0' AFTER `dl_pling_factor`; 0003 0004 ALTER TABLE `stat_projects` 0005 ADD COLUMN `cat_show_description` INT(1) NULL DEFAULT NULL AFTER `cat_name_legacy`; 0006 0007 DROP VIEW `stat_projects_v`; 0008 CREATE VIEW `stat_projects_v` AS 0009 SELECT `project`.`project_id` AS `project_id`, 0010 `project`.`member_id` AS `member_id`, 0011 `project`.`content_type` AS `content_type`, 0012 `project`.`project_category_id` AS `project_category_id`, 0013 `project`.`hive_category_id` AS `hive_category_id`, 0014 `project`.`status` AS `status`, 0015 `project`.`uuid` AS `uuid`, 0016 `project`.`pid` AS `pid`, 0017 `project`.`type_id` AS `type_id`, 0018 `project`.`title` AS `title`, 0019 `project`.`description` AS `description`, 0020 `project`.`version` AS `version`, 0021 `project`.`image_big` AS `image_big`, 0022 `project`.`image_small` AS `image_small`, 0023 `project`.`start_date` AS `start_date`, 0024 `project`.`content_url` AS `content_url`, 0025 `project`.`created_at` AS `created_at`, 0026 `project`.`changed_at` AS `changed_at`, 0027 `project`.`deleted_at` AS `deleted_at`, 0028 `project`.`creator_id` AS `creator_id`, 0029 `project`.`facebook_code` AS `facebook_code`, 0030 `project`.`github_code` AS `github_code`, 0031 `project`.`twitter_code` AS `twitter_code`, 0032 `project`.`google_code` AS `google_code`, 0033 `project`.`link_1` AS `link_1`, 0034 `project`.`embed_code` AS `embed_code`, 0035 `project`.`ppload_collection_id` AS `ppload_collection_id`, 0036 `project`.`validated` AS `validated`, 0037 `project`.`validated_at` AS `validated_at`, 0038 `project`.`featured` AS `featured`, 0039 `project`.`approved` AS `approved`, 0040 `project`.`amount` AS `amount`, 0041 `project`.`amount_period` AS `amount_period`, 0042 `project`.`claimable` AS `claimable`, 0043 `project`.`claimed_by_member` AS `claimed_by_member`, 0044 `project`.`count_likes` AS `count_likes`, 0045 `project`.`count_dislikes` AS `count_dislikes`, 0046 `project`.`count_comments` AS `count_comments`, 0047 `project`.`count_downloads_hive` AS `count_downloads_hive`, 0048 `project`.`source_id` AS `source_id`, 0049 `project`.`source_pk` AS `source_pk`, 0050 `project`.`source_type` AS `source_type`, 0051 `project`.`validated` AS `project_validated`, 0052 `project`.`uuid` AS `project_uuid`, 0053 `project`.`status` AS `project_status`, 0054 `project`.`created_at` AS `project_created_at`, 0055 `member`.`type` AS `member_type`, 0056 `member`.`member_id` AS `project_member_id`, 0057 `project`.`changed_at` AS `project_changed_at`, 0058 (round(((`project`.`count_likes` + 6) / ((`project`.`count_likes` + `project`.`count_dislikes`) + 12)), 2) * 0059 100) AS `laplace_score`, 0060 `member`.`username` AS `username`, 0061 `member`.`profile_image_url` AS `profile_image_url`, 0062 `member`.`city` AS `city`, 0063 `member`.`country` AS `country`, 0064 `member`.`created_at` AS `member_created_at`, 0065 `member`.`paypal_mail` AS `paypal_mail`, 0066 `project_category`.`title` AS `cat_title`, 0067 `project_category`.`xdg_type` AS `cat_xdg_type`, 0068 `project_category`.`name_legacy` AS `cat_name_legacy`, 0069 `project_category`.`show_description` AS `cat_show_description`, 0070 `stat_plings`.`amount_received` AS `amount_received`, 0071 `stat_plings`.`count_plings` AS `count_plings`, 0072 `stat_plings`.`count_plingers` AS `count_plingers`, 0073 `stat_plings`.`latest_pling` AS `latest_pling`, 0074 `view_reported_projects`.`amount_reports` AS `amount_reports` 0075 FROM ((((`project` JOIN `member` ON ((`member`.`member_id` = `project`.`member_id`))) JOIN `project_category` ON (( 0076 `project`.`project_category_id` = 0077 `project_category`.`project_category_id`))) LEFT JOIN `stat_plings` ON ((`stat_plings`.`project_id` = `project`.`project_id`))) 0078 LEFT JOIN `view_reported_projects` ON ((`view_reported_projects`.`project_id` = `project`.`project_id`))) 0079 WHERE ((`member`.`is_deleted` = 0) AND (`member`.`is_active` = 1) AND (`project`.`type_id` = 1) AND 0080 (`project`.`status` = 100)); 0081 0082 0083 0084 DROP PROCEDURE IF EXISTS `generate_stat_project`; 0085 0086 DELIMITER $$ 0087 USE `pling`$$ 0088 CREATE PROCEDURE `generate_stat_project`() 0089 BEGIN 0090 DROP TABLE IF EXISTS `tmp_reported_projects`; 0091 CREATE TEMPORARY TABLE `tmp_reported_projects` 0092 ( 0093 PRIMARY KEY `primary` (`project_id`) 0094 ) 0095 AS 0096 SELECT `reports_project`.`project_id` AS `project_id`, 0097 COUNT(`reports_project`.`project_id`) AS `amount_reports`, 0098 MAX(`reports_project`.`created_at`) AS `latest_report` 0099 FROM `reports_project` 0100 WHERE (`reports_project`.`is_deleted` = 0) 0101 GROUP BY `reports_project`.`project_id`; 0102 0103 DROP TABLE IF EXISTS `tmp_project_package_types`; 0104 CREATE TEMPORARY TABLE `tmp_project_package_types` 0105 ( 0106 PRIMARY KEY `primary` (`project_id`) 0107 ) 0108 ENGINE MyISAM 0109 AS 0110 SELECT `project_id`, 0111 GROUP_CONCAT(DISTINCT `project_package_type`.`package_type_id`) AS `package_type_id_list`, 0112 GROUP_CONCAT(DISTINCT `package_types`.`name`) AS `package_name_list` 0113 FROM `project_package_type` 0114 JOIN 0115 `package_types` ON `project_package_type`.`package_type_id` = `package_types`.`package_type_id` 0116 WHERE `package_types`.`is_active` = 1 0117 GROUP BY `project_id`; 0118 0119 0120 DROP TABLE IF EXISTS `tmp_stat_projects`; 0121 CREATE TABLE `tmp_stat_projects` 0122 ( 0123 PRIMARY KEY `primary` (`project_id`), 0124 INDEX `idx_cat` (`project_category_id`) 0125 ) 0126 ENGINE MyISAM 0127 AS 0128 SELECT `project`.`project_id` AS `project_id`, 0129 `project`.`member_id` AS `member_id`, 0130 `project`.`content_type` AS `content_type`, 0131 `project`.`project_category_id` AS `project_category_id`, 0132 `project`.`hive_category_id` AS `hive_category_id`, 0133 `project`.`status` AS `status`, 0134 `project`.`uuid` AS `uuid`, 0135 `project`.`pid` AS `pid`, 0136 `project`.`type_id` AS `type_id`, 0137 `project`.`title` AS `title`, 0138 `project`.`description` AS `description`, 0139 `project`.`version` AS `version`, 0140 `project`.`image_big` AS `image_big`, 0141 `project`.`image_small` AS `image_small`, 0142 `project`.`start_date` AS `start_date`, 0143 `project`.`content_url` AS `content_url`, 0144 `project`.`created_at` AS `created_at`, 0145 `project`.`changed_at` AS `changed_at`, 0146 `project`.`deleted_at` AS `deleted_at`, 0147 `project`.`creator_id` AS `creator_id`, 0148 `project`.`facebook_code` AS `facebook_code`, 0149 `project`.`github_code` AS `github_code`, 0150 `project`.`twitter_code` AS `twitter_code`, 0151 `project`.`google_code` AS `google_code`, 0152 `project`.`link_1` AS `link_1`, 0153 `project`.`embed_code` AS `embed_code`, 0154 `project`.`ppload_collection_id` AS `ppload_collection_id`, 0155 `project`.`validated` AS `validated`, 0156 `project`.`validated_at` AS `validated_at`, 0157 `project`.`featured` AS `featured`, 0158 `project`.`approved` AS `approved`, 0159 `project`.`amount` AS `amount`, 0160 `project`.`amount_period` AS `amount_period`, 0161 `project`.`claimable` AS `claimable`, 0162 `project`.`claimed_by_member` AS `claimed_by_member`, 0163 `project`.`count_likes` AS `count_likes`, 0164 `project`.`count_dislikes` AS `count_dislikes`, 0165 `project`.`count_comments` AS `count_comments`, 0166 `project`.`count_downloads_hive` AS `count_downloads_hive`, 0167 `project`.`source_id` AS `source_id`, 0168 `project`.`source_pk` AS `source_pk`, 0169 `project`.`source_type` AS `source_type`, 0170 `project`.`validated` AS `project_validated`, 0171 `project`.`uuid` AS `project_uuid`, 0172 `project`.`status` AS `project_status`, 0173 `project`.`created_at` AS `project_created_at`, 0174 `project`.`changed_at` AS `project_changed_at`, 0175 laplace_score(`project`.`count_likes`, `project`.`count_dislikes`) AS `laplace_score`, 0176 `member`.`type` AS `member_type`, 0177 `member`.`member_id` AS `project_member_id`, 0178 `member`.`username` AS `username`, 0179 `member`.`profile_image_url` AS `profile_image_url`, 0180 `member`.`city` AS `city`, 0181 `member`.`country` AS `country`, 0182 `member`.`created_at` AS `member_created_at`, 0183 `member`.`paypal_mail` AS `paypal_mail`, 0184 `project_category`.`title` AS `cat_title`, 0185 `project_category`.`xdg_type` AS `cat_xdg_type`, 0186 `project_category`.`name_legacy` AS `cat_name_legacy`, 0187 `project_category`.`show_description` AS `cat_show_description`, 0188 `stat_plings`.`amount_received` AS `amount_received`, 0189 `stat_plings`.`count_plings` AS `count_plings`, 0190 `stat_plings`.`count_plingers` AS `count_plingers`, 0191 `stat_plings`.`latest_pling` AS `latest_pling`, 0192 `trp`.`amount_reports` AS `amount_reports`, 0193 `tppt`.`package_type_id_list` AS `package_types`, 0194 `tppt`.`package_name_list` AS `package_names` 0195 FROM `project` 0196 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0197 JOIN `project_category` ON `project`.`project_category_id` = `project_category`.`project_category_id` 0198 LEFT JOIN `stat_plings` ON `stat_plings`.`project_id` = `project`.`project_id` 0199 LEFT JOIN `tmp_reported_projects` AS `trp` ON `trp`.`project_id` = `project`.`project_id` 0200 LEFT JOIN `tmp_project_package_types` AS `tppt` ON `tppt`.`project_id` = `project`.`project_id` 0201 WHERE `member`.`is_deleted` = 0 0202 AND `member`.`is_active` = 1 0203 AND `project`.`type_id` = 1 0204 AND `project`.`status` = 100 0205 AND `project_category`.`is_active` = 1; 0206 0207 RENAME TABLE `stat_projects` TO `old_stat_projects`, `tmp_stat_projects` TO `stat_projects`; 0208 0209 DROP TABLE IF EXISTS `old_stat_projects`; 0210 END$$ 0211 0212 DELIMITER ; 0213 0214 0215 DROP EVENT IF EXISTS `e_generate_stat_projects`; 0216 CREATE 0217 DEFINER = CURRENT_USER 0218 EVENT IF NOT EXISTS `e_generate_stat_projects` 0219 ON SCHEDULE 0220 EVERY 5 MINUTE STARTS DATE_FORMAT(NOW(), '%Y-%m-%d 05:00:00') 0221 ON COMPLETION PRESERVE 0222 -- DISABLE ON SLAVE 0223 COMMENT 'Regenerates stat_projects table' 0224 DO 0225 CALL `pling`.generate_stat_project();