File indexing completed on 2024-04-28 17:16:09
0001 ALTER TABLE `project` 0002 ADD COLUMN `project_license_id` INT(11) NULL DEFAULT NULL AFTER `version`; 0003 0004 0005 CREATE TABLE `project_license` 0006 ( 0007 `project_license_id` INT(11) NOT NULL AUTO_INCREMENT, 0008 `title` VARCHAR(50) NULL DEFAULT NULL, 0009 `description` VARCHAR(255) NULL DEFAULT NULL, 0010 PRIMARY KEY (`project_license_id`) 0011 ) 0012 ENGINE = InnoDB 0013 ; 0014 0015 0016 INSERT INTO `project_license` 0017 VALUES (0, 'Other', NULL); 0018 INSERT INTO `project_license` 0019 VALUES (1, 'GPLv2 or later', NULL); 0020 INSERT INTO `project_license` 0021 VALUES (2, 'LGPL', NULL); 0022 INSERT INTO `project_license` 0023 VALUES (3, 'Artistic 2.0', NULL); 0024 INSERT INTO `project_license` 0025 VALUES (4, 'X11', NULL); 0026 INSERT INTO `project_license` 0027 VALUES (5, 'QPL', NULL); 0028 INSERT INTO `project_license` 0029 VALUES (6, 'BSD', NULL); 0030 INSERT INTO `project_license` 0031 VALUES (7, 'Proprietary License', NULL); 0032 INSERT INTO `project_license` 0033 VALUES (8, 'GFDL', NULL); 0034 INSERT INTO `project_license` 0035 VALUES (9, 'CPL 1.0', NULL); 0036 INSERT INTO `project_license` 0037 VALUES (10, 'Creative Commons by', NULL); 0038 INSERT INTO `project_license` 0039 VALUES (11, 'Creative Commons by-sa', NULL); 0040 INSERT INTO `project_license` 0041 VALUES (12, 'Creative Commons by-nd', NULL); 0042 INSERT INTO `project_license` 0043 VALUES (13, 'Creative Commons by-nc', NULL); 0044 INSERT INTO `project_license` 0045 VALUES (14, 'Creative Commons by-nc-sa', NULL); 0046 INSERT INTO `project_license` 0047 VALUES (15, 'Creative Commons by-nc-nd', NULL); 0048 INSERT INTO `project_license` 0049 VALUES (16, 'AGPL', NULL); 0050 INSERT INTO `project_license` 0051 VALUES (17, 'CC0 1.0 Universal (Public Domain)', NULL); 0052 INSERT INTO `project_license` 0053 VALUES (18, 'GPLv2 only', NULL); 0054 INSERT INTO `project_license` 0055 VALUES (19, 'GPLv3', NULL); 0056 0057 0058 0059 DROP PROCEDURE IF EXISTS `generate_stat_project`; 0060 CREATE PROCEDURE `generate_stat_project`() 0061 LANGUAGE SQL 0062 NOT DETERMINISTIC 0063 CONTAINS SQL 0064 SQL SECURITY DEFINER 0065 COMMENT '' 0066 BEGIN 0067 DROP TABLE IF EXISTS `tmp_reported_projects`; 0068 CREATE TEMPORARY TABLE `tmp_reported_projects` 0069 ( 0070 PRIMARY KEY `primary` (`project_id`) 0071 ) 0072 AS 0073 SELECT `reports_project`.`project_id` AS `project_id`, 0074 COUNT(`reports_project`.`project_id`) AS `amount_reports`, 0075 MAX(`reports_project`.`created_at`) AS `latest_report` 0076 FROM `reports_project` 0077 WHERE (`reports_project`.`is_deleted` = 0) 0078 GROUP BY `reports_project`.`project_id`; 0079 0080 DROP TABLE IF EXISTS `tmp_project_package_types`; 0081 CREATE TEMPORARY TABLE `tmp_project_package_types` 0082 ( 0083 PRIMARY KEY `primary` (`project_id`) 0084 ) 0085 ENGINE MyISAM 0086 AS 0087 SELECT `project_id`, 0088 GROUP_CONCAT(DISTINCT `project_package_type`.`package_type_id`) AS `package_type_id_list`, 0089 GROUP_CONCAT(DISTINCT `package_types`.`name`) AS `package_name_list` 0090 FROM `project_package_type` 0091 JOIN 0092 `package_types` ON `project_package_type`.`package_type_id` = `package_types`.`package_type_id` 0093 WHERE `package_types`.`is_active` = 1 0094 GROUP BY `project_id`; 0095 0096 DROP TABLE IF EXISTS `tmp_project_tags`; 0097 CREATE TEMPORARY TABLE `tmp_project_tags` 0098 ( 0099 PRIMARY KEY `primary` (`tag_project_id`) 0100 ) 0101 ENGINE MyISAM 0102 AS 0103 SELECT GROUP_CONCAT(`tag`.`tag_name`) AS `tag_names`, `tgo`.`tag_object_id` AS `tag_project_id` 0104 FROM `tag_object` AS `tgo` 0105 JOIN `tag` ON `tag`.`tag_id` = `tgo`.`tag_id` 0106 WHERE `tag_type_id` = 1 0107 GROUP BY `tgo`.`tag_object_id` 0108 ORDER BY `tgo`.`tag_object_id`; 0109 0110 0111 DROP TABLE IF EXISTS `tmp_stat_projects`; 0112 CREATE TABLE `tmp_stat_projects` 0113 ( 0114 PRIMARY KEY `primary` (`project_id`), 0115 INDEX `idx_cat` (`project_category_id`) 0116 ) 0117 ENGINE MyISAM 0118 AS 0119 SELECT `project`.`project_id` AS `project_id`, 0120 `project`.`member_id` AS `member_id`, 0121 `project`.`content_type` AS `content_type`, 0122 `project`.`project_category_id` AS `project_category_id`, 0123 `project`.`hive_category_id` AS `hive_category_id`, 0124 `project`.`status` AS `status`, 0125 `project`.`uuid` AS `uuid`, 0126 `project`.`pid` AS `pid`, 0127 `project`.`type_id` AS `type_id`, 0128 `project`.`title` AS `title`, 0129 `project`.`description` AS `description`, 0130 `project`.`version` AS `version`, 0131 `project`.`project_license_id` AS `project_license_id`, 0132 `project`.`image_big` AS `image_big`, 0133 `project`.`image_small` AS `image_small`, 0134 `project`.`start_date` AS `start_date`, 0135 `project`.`content_url` AS `content_url`, 0136 `project`.`created_at` AS `created_at`, 0137 `project`.`changed_at` AS `changed_at`, 0138 `project`.`deleted_at` AS `deleted_at`, 0139 `project`.`creator_id` AS `creator_id`, 0140 `project`.`facebook_code` AS `facebook_code`, 0141 `project`.`github_code` AS `github_code`, 0142 `project`.`twitter_code` AS `twitter_code`, 0143 `project`.`google_code` AS `google_code`, 0144 `project`.`link_1` AS `link_1`, 0145 `project`.`embed_code` AS `embed_code`, 0146 `project`.`ppload_collection_id` AS `ppload_collection_id`, 0147 `project`.`validated` AS `validated`, 0148 `project`.`validated_at` AS `validated_at`, 0149 `project`.`featured` AS `featured`, 0150 `project`.`ghns_excluded` AS `ghns_excluded`, 0151 `project`.`amount` AS `amount`, 0152 `project`.`amount_period` AS `amount_period`, 0153 `project`.`claimable` AS `claimable`, 0154 `project`.`claimed_by_member` AS `claimed_by_member`, 0155 `project`.`count_likes` AS `count_likes`, 0156 `project`.`count_dislikes` AS `count_dislikes`, 0157 `project`.`count_comments` AS `count_comments`, 0158 `project`.`count_downloads_hive` AS `count_downloads_hive`, 0159 `project`.`source_id` AS `source_id`, 0160 `project`.`source_pk` AS `source_pk`, 0161 `project`.`source_type` AS `source_type`, 0162 `project`.`validated` AS `project_validated`, 0163 `project`.`uuid` AS `project_uuid`, 0164 `project`.`status` AS `project_status`, 0165 `project`.`created_at` AS `project_created_at`, 0166 `project`.`changed_at` AS `project_changed_at`, 0167 laplace_score(`project`.`count_likes`, `project`.`count_dislikes`) AS `laplace_score`, 0168 `member`.`type` AS `member_type`, 0169 `member`.`member_id` AS `project_member_id`, 0170 `member`.`username` AS `username`, 0171 `member`.`profile_image_url` AS `profile_image_url`, 0172 `member`.`city` AS `city`, 0173 `member`.`country` AS `country`, 0174 `member`.`created_at` AS `member_created_at`, 0175 `member`.`paypal_mail` AS `paypal_mail`, 0176 `project_category`.`title` AS `cat_title`, 0177 `project_category`.`xdg_type` AS `cat_xdg_type`, 0178 `project_category`.`name_legacy` AS `cat_name_legacy`, 0179 `project_category`.`show_description` AS `cat_show_description`, 0180 `stat_plings`.`amount_received` AS `amount_received`, 0181 `stat_plings`.`count_plings` AS `count_plings`, 0182 `stat_plings`.`count_plingers` AS `count_plingers`, 0183 `stat_plings`.`latest_pling` AS `latest_pling`, 0184 `trp`.`amount_reports` AS `amount_reports`, 0185 `tppt`.`package_type_id_list` AS `package_types`, 0186 `tppt`.`package_name_list` AS `package_names`, 0187 `t`.`tag_names` AS `tags`, 0188 `sdqy`.`amount` AS `count_downloads_quarter`, 0189 `project_license`.`title` AS `project_license_title` 0190 FROM `project` 0191 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 0192 JOIN `project_category` ON `project`.`project_category_id` = `project_category`.`project_category_id` 0193 LEFT JOIN `stat_plings` ON `stat_plings`.`project_id` = `project`.`project_id` 0194 LEFT JOIN `tmp_reported_projects` AS `trp` ON `trp`.`project_id` = `project`.`project_id` 0195 LEFT JOIN `tmp_project_package_types` AS `tppt` ON `tppt`.`project_id` = `project`.`project_id` 0196 LEFT JOIN `tmp_project_tags` AS `t` ON `t`.`tag_project_id` = `project`.`project_id` 0197 LEFT JOIN `stat_downloads_quarter_year` AS `sdqy` ON `sdqy`.`project_id` = `project`.`project_id` 0198 LEFT JOIN `project_license` ON `project_license`.`project_license_id` = `project`.`project_license_id` 0199 WHERE `member`.`is_deleted` = 0 0200 AND `member`.`is_active` = 1 0201 AND `project`.`type_id` = 1 0202 AND `project`.`status` = 100 0203 AND `project_category`.`is_active` = 1; 0204 0205 RENAME TABLE `stat_projects` TO `old_stat_projects`, `tmp_stat_projects` TO `stat_projects`; 0206 0207 DROP TABLE IF EXISTS `old_stat_projects`; 0208 END