File indexing completed on 2024-04-28 09:56:34
0001 ALTER TABLE `pling`.`project` 0002 ADD COLUMN `hive_category_id` INT(11) NOT NULL DEFAULT 0 AFTER `source_type`; 0003 0004 0005 #create view and tables 0006 DROP VIEW IF EXISTS `stat_projects_v`; 0007 CREATE VIEW `stat_projects_v` AS 0008 SELECT `project`.`project_id` AS `project_id`, 0009 `project`.`member_id` AS `member_id`, 0010 `project`.`content_type` AS `content_type`, 0011 `project`.`project_category_id` AS `project_category_id`, 0012 `project`.`hive_category_id` AS `hive_category_id`, 0013 `project`.`is_active` AS `is_active`, 0014 `project`.`is_deleted` AS `is_deleted`, 0015 `project`.`status` AS `status`, 0016 `project`.`uuid` AS `uuid`, 0017 `project`.`pid` AS `pid`, 0018 `project`.`type_id` AS `type_id`, 0019 `project`.`title` AS `title`, 0020 `project`.`description` AS `description`, 0021 `project`.`version` AS `version`, 0022 `project`.`image_big` AS `image_big`, 0023 `project`.`image_small` AS `image_small`, 0024 `project`.`start_date` AS `start_date`, 0025 `project`.`content_url` AS `content_url`, 0026 `project`.`created_at` AS `created_at`, 0027 `project`.`changed_at` AS `changed_at`, 0028 `project`.`deleted_at` AS `deleted_at`, 0029 `project`.`creator_id` AS `creator_id`, 0030 `project`.`facebook_code` AS `facebook_code`, 0031 `project`.`github_code` AS `github_code`, 0032 `project`.`twitter_code` AS `twitter_code`, 0033 `project`.`google_code` AS `google_code`, 0034 `project`.`link_1` AS `link_1`, 0035 `project`.`embed_code` AS `embed_code`, 0036 `project`.`ppload_collection_id` AS `ppload_collection_id`, 0037 `project`.`validated` AS `validated`, 0038 `project`.`validated_at` AS `validated_at`, 0039 `project`.`featured` AS `featured`, 0040 `project`.`approved` AS `approved`, 0041 `project`.`amount` AS `amount`, 0042 `project`.`amount_period` AS `amount_period`, 0043 `project`.`claimable` AS `claimable`, 0044 `project`.`claimed_by_member` AS `claimed_by_member`, 0045 `project`.`count_likes` AS `count_likes`, 0046 `project`.`count_dislikes` AS `count_dislikes`, 0047 `project`.`count_comments` AS `count_comments`, 0048 `project`.`count_downloads_hive` AS `count_downloads_hive`, 0049 `project`.`source_id` AS `source_id`, 0050 `project`.`source_pk` AS `source_pk`, 0051 `project`.`source_type` AS `source_type`, 0052 `project`.`validated` AS `project_validated`, 0053 `project`.`uuid` AS `project_uuid`, 0054 `project`.`status` AS `project_status`, 0055 `project`.`created_at` AS `project_created_at`, 0056 `member`.`type` AS `member_type`, 0057 `member`.`member_id` AS `project_member_id`, 0058 `project`.`changed_at` AS `project_changed_at`, 0059 (ROUND(((`project`.`count_likes` + 6) / ((`project`.`count_likes` + `project`.`count_dislikes`) + 12)), 2) * 0060 100) AS `laplace_score`, 0061 `member`.`username` AS `username`, 0062 `member`.`profile_image_url` AS `profile_image_url`, 0063 `member`.`city` AS `city`, 0064 `member`.`country` AS `country`, 0065 `member`.`created_at` AS `member_created_at`, 0066 `member`.`paypal_mail` AS `paypal_mail`, 0067 `project_category`.`title` AS `cat_title`, 0068 `stat_plings`.`amount_received` AS `amount_received`, 0069 `stat_plings`.`count_plings` AS `count_plings`, 0070 `stat_plings`.`count_plingers` AS `count_plingers`, 0071 `stat_plings`.`latest_pling` AS `latest_pling` 0072 FROM (((`project` 0073 JOIN `member` ON (((`project`.`member_id` = `member`.`member_id`) AND (`member`.`is_active` = 1) AND 0074 (`member`.`is_deleted` = 0)))) 0075 JOIN `project_category` ON ((`project`.`project_category_id` = `project_category`.`project_category_id`))) 0076 LEFT JOIN `stat_plings` ON ((`project`.`project_id` = `stat_plings`.`project_id`))) 0077 WHERE ((`project`.`status` = 100) AND (`project`.`type_id` = 1)) 0078 ; 0079 0080 CREATE TABLE `stat_projects` 0081 ( 0082 `project_id` INT(11) NOT NULL DEFAULT '0', 0083 `member_id` INT(11) NOT NULL DEFAULT '0', 0084 `content_type` VARCHAR(255) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci', 0085 `project_category_id` INT(11) NOT NULL DEFAULT '0', 0086 `hive_category_id` INT(11) NOT NULL DEFAULT '0', 0087 `is_active` INT(1) NOT NULL DEFAULT '0', 0088 `is_deleted` INT(1) NOT NULL DEFAULT '0', 0089 `status` INT(11) NOT NULL DEFAULT '0', 0090 `uuid` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci', 0091 `pid` INT(11) NULL DEFAULT NULL COMMENT 'ParentId', 0092 `type_id` INT(11) NULL DEFAULT NULL COMMENT '0 = DummyProject, 1 = Project, 2 = Update', 0093 `title` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8_general_ci', 0094 `description` TEXT NULL COLLATE 'utf8_general_ci', 0095 `version` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci', 0096 `image_big` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci', 0097 `image_small` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci', 0098 `start_date` DATETIME NULL DEFAULT NULL, 0099 `content_url` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci', 0100 `created_at` DATETIME NULL DEFAULT NULL, 0101 `changed_at` DATETIME NULL DEFAULT NULL, 0102 `deleted_at` DATETIME NULL DEFAULT NULL, 0103 `creator_id` INT(11) NULL DEFAULT NULL COMMENT 'Member_id of the creator. Importent for groups.', 0104 `facebook_code` TEXT NULL COLLATE 'utf8_general_ci', 0105 `github_code` TEXT NULL COLLATE 'utf8_general_ci', 0106 `twitter_code` TEXT NULL COLLATE 'utf8_general_ci', 0107 `google_code` TEXT NULL COLLATE 'utf8_general_ci', 0108 `link_1` TEXT NULL COLLATE 'utf8_general_ci', 0109 `embed_code` TEXT NULL COLLATE 'utf8_general_ci', 0110 `ppload_collection_id` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci', 0111 `validated` INT(1) NULL DEFAULT NULL, 0112 `validated_at` DATETIME NULL DEFAULT NULL, 0113 `featured` INT(1) NULL DEFAULT NULL, 0114 `approved` INT(1) NULL DEFAULT NULL, 0115 `amount` INT(11) NULL DEFAULT NULL, 0116 `amount_period` VARCHAR(45) NULL DEFAULT NULL COLLATE 'utf8_general_ci', 0117 `claimable` INT(1) NULL DEFAULT NULL, 0118 `claimed_by_member` INT(11) NULL DEFAULT NULL, 0119 `count_likes` INT(11) NULL DEFAULT NULL, 0120 `count_dislikes` INT(11) NULL DEFAULT NULL, 0121 `count_comments` INT(11) NULL DEFAULT NULL, 0122 `count_downloads_hive` INT(11) NULL DEFAULT NULL, 0123 `source_id` INT(11) NULL DEFAULT NULL, 0124 `source_pk` INT(11) NULL DEFAULT NULL, 0125 `source_type` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_general_ci', 0126 `project_validated` INT(1) NULL DEFAULT NULL, 0127 `project_uuid` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci', 0128 `project_status` INT(11) NOT NULL DEFAULT '0', 0129 `project_created_at` DATETIME NULL DEFAULT NULL, 0130 `member_type` INT(1) NOT NULL DEFAULT '0' COMMENT 'Type: 0 = Member, 1 = group', 0131 `project_member_id` INT(10) NOT NULL DEFAULT '0', 0132 `project_changed_at` DATETIME NULL DEFAULT NULL, 0133 `laplace_score` DECIMAL(17, 2) NULL DEFAULT NULL, 0134 `username` VARCHAR(255) NOT NULL COLLATE 'utf8_bin', 0135 `profile_image_url` VARCHAR(355) NULL DEFAULT NULL COMMENT 'URL to the profile-image' COLLATE 'utf8_general_ci', 0136 `city` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci', 0137 `country` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci', 0138 `member_created_at` DATETIME NULL DEFAULT NULL, 0139 `paypal_mail` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8_general_ci', 0140 `cat_title` VARCHAR(100) NOT NULL COLLATE 'utf8_general_ci', 0141 `amount_received` DOUBLE(19, 2) NULL DEFAULT NULL, 0142 `count_plings` BIGINT(21) NULL DEFAULT NULL, 0143 `count_plingers` BIGINT(21) NULL DEFAULT NULL, 0144 `latest_pling` TIMESTAMP NULL DEFAULT NULL COMMENT 'When did paypal say, that this pling was payed successfull', 0145 INDEX `idx_project_cat_id` (`project_category_id`) 0146 ) 0147 ENGINE = InnoDB 0148 ; 0149 0150 0151 #Update machanism 0152 START TRANSACTION; 0153 CREATE TABLE `stat_projects_new` LIKE `stat_projects`; 0154 INSERT INTO `stat_projects_new` (SELECT * FROM `stat_projects_v`); 0155 RENAME TABLE `stat_projects` TO `stat_projects_old`; 0156 RENAME TABLE `stat_projects_new` TO `stat_projects`; 0157 DROP TABLE `stat_projects_old`; 0158 COMMIT; 0159 0160