File indexing completed on 2024-05-12 05:58:07
0001 0002 CREATE DATABASE /*!32312 IF NOT EXISTS*/ `ocs` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_general_ci */; 0003 0004 USE `ocs`; 0005 /*!40101 SET @saved_cs_client = @@character_set_client */; 0006 /*!40101 SET character_set_client = utf8 */; 0007 CREATE TABLE `activity_log` ( 0008 `activity_log_id` int(11) NOT NULL AUTO_INCREMENT, 0009 `member_id` int(11) NOT NULL COMMENT 'Log action of this memeber', 0010 `project_id` int(11) DEFAULT NULL, 0011 `object_id` int(11) NOT NULL COMMENT 'Key to the action (add comment, pling, ...)', 0012 `object_ref` varchar(45) NOT NULL COMMENT 'Refferenz to the object table (plings, project, project_comment,...)', 0013 `object_title` varchar(90) DEFAULT NULL COMMENT 'Title to show', 0014 `object_text` varchar(150) DEFAULT NULL COMMENT 'Short text of this object (first 150 characters)', 0015 `object_img` varchar(255) DEFAULT NULL, 0016 `activity_type_id` int(11) NOT NULL DEFAULT '0' COMMENT 'Wich type of activity: create, update,delete.', 0017 `time` timestamp NULL DEFAULT CURRENT_TIMESTAMP, 0018 PRIMARY KEY (`activity_log_id`), 0019 KEY `member_id` (`member_id`), 0020 KEY `project_id` (`project_id`), 0021 KEY `object_id` (`object_id`), 0022 KEY `activity_log_id` (`activity_log_id`,`member_id`,`project_id`,`object_id`), 0023 KEY `idx_time` (`time`,`member_id`) 0024 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Log all actions of a user. Wen can then generate a newsfeed '; 0025 /*!40101 SET character_set_client = @saved_cs_client */; 0026 /*!40101 SET @saved_cs_client = @@character_set_client */; 0027 /*!40101 SET character_set_client = utf8 */; 0028 CREATE TABLE `activity_log_types` ( 0029 `activity_log_type_id` int(11) NOT NULL, 0030 `type_text` varchar(45) DEFAULT NULL, 0031 PRIMARY KEY (`activity_log_type_id`) 0032 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Type of activities'; 0033 /*!40101 SET character_set_client = @saved_cs_client */; 0034 /*!40101 SET @saved_cs_client = @@character_set_client */; 0035 /*!40101 SET character_set_client = utf8 */; 0036 CREATE TABLE `comment_types` ( 0037 `comment_type_id` int(11) DEFAULT NULL, 0038 `name` varchar(50) DEFAULT NULL, 0039 KEY `pk` (`comment_type_id`) 0040 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0041 /*!40101 SET character_set_client = @saved_cs_client */; 0042 /*!40101 SET @saved_cs_client = @@character_set_client */; 0043 /*!40101 SET character_set_client = utf8 */; 0044 CREATE TABLE `comments` ( 0045 `comment_id` int(11) NOT NULL AUTO_INCREMENT, 0046 `comment_target_id` int(11) NOT NULL, 0047 `comment_member_id` int(11) NOT NULL, 0048 `comment_parent_id` int(11) DEFAULT NULL, 0049 `comment_type` int(11) DEFAULT '0', 0050 `comment_pling_id` int(11) DEFAULT NULL, 0051 `comment_text` text, 0052 `comment_active` int(1) DEFAULT '1', 0053 `comment_created_at` datetime DEFAULT NULL, 0054 `source_id` int(11) DEFAULT '0', 0055 `source_pk` int(11) DEFAULT NULL, 0056 PRIMARY KEY (`comment_id`), 0057 UNIQUE KEY `uk_hive_pk` (`source_pk`,`source_id`), 0058 KEY `idx_target` (`comment_target_id`), 0059 KEY `idx_created` (`comment_created_at`), 0060 KEY `idx_parent` (`comment_parent_id`), 0061 KEY `idx_pling` (`comment_pling_id`), 0062 KEY `idx_type_active` (`comment_type`,`comment_active`), 0063 KEY `idx_member` (`comment_member_id`) 0064 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 0065 /*!40101 SET character_set_client = @saved_cs_client */; 0066 /*!50003 SET @saved_cs_client = @@character_set_client */ ; 0067 /*!50003 SET @saved_cs_results = @@character_set_results */ ; 0068 /*!50003 SET @saved_col_connection = @@collation_connection */ ; 0069 /*!50003 SET character_set_client = utf8mb4 */ ; 0070 /*!50003 SET character_set_results = utf8mb4 */ ; 0071 /*!50003 SET collation_connection = utf8mb4_general_ci */ ; 0072 /*!50003 SET @saved_sql_mode = @@sql_mode */ ; 0073 /*!50003 SET sql_mode = '' */ ; 0074 DELIMITER ;; 0075 /*!50003 CREATE*/ /*!50017 DEFINER=CURRENT_USER*/ /*!50003 TRIGGER `comment_created` BEFORE INSERT ON `comments` FOR EACH ROW 0076 BEGIN 0077 IF NEW.comment_created_at IS NULL THEN 0078 SET NEW.comment_created_at = NOW(); 0079 END IF; 0080 0081 IF NEW.comment_type = 0 THEN 0082 0083 UPDATE project p 0084 SET p.count_comments = (p.count_comments+1) 0085 WHERE p.project_id = NEW.comment_target_id; 0086 0087 END IF; 0088 END */;; 0089 DELIMITER ; 0090 /*!50003 SET sql_mode = @saved_sql_mode */ ; 0091 /*!50003 SET character_set_client = @saved_cs_client */ ; 0092 /*!50003 SET character_set_results = @saved_cs_results */ ; 0093 /*!50003 SET collation_connection = @saved_col_connection */ ; 0094 /*!50003 SET @saved_cs_client = @@character_set_client */ ; 0095 /*!50003 SET @saved_cs_results = @@character_set_results */ ; 0096 /*!50003 SET @saved_col_connection = @@collation_connection */ ; 0097 /*!50003 SET character_set_client = utf8mb4 */ ; 0098 /*!50003 SET character_set_results = utf8mb4 */ ; 0099 /*!50003 SET collation_connection = utf8mb4_general_ci */ ; 0100 /*!50003 SET @saved_sql_mode = @@sql_mode */ ; 0101 /*!50003 SET sql_mode = '' */ ; 0102 DELIMITER ;; 0103 /*!50003 CREATE*/ /*!50017 DEFINER=CURRENT_USER*/ /*!50003 TRIGGER `comment_update` BEFORE UPDATE ON `comments` FOR EACH ROW BEGIN 0104 0105 IF NEW.comment_active = 0 AND OLD.comment_active = 1 THEN 0106 0107 UPDATE project p 0108 SET p.count_comments = (p.count_comments-1) 0109 WHERE p.project_id = NEW.comment_target_id; 0110 0111 END IF; 0112 0113 0114 END */;; 0115 DELIMITER ; 0116 /*!50003 SET sql_mode = @saved_sql_mode */ ; 0117 /*!50003 SET character_set_client = @saved_cs_client */ ; 0118 /*!50003 SET character_set_results = @saved_cs_results */ ; 0119 /*!50003 SET collation_connection = @saved_col_connection */ ; 0120 /*!40101 SET @saved_cs_client = @@character_set_client */; 0121 /*!40101 SET character_set_client = utf8 */; 0122 CREATE TABLE `config_operating_system` ( 0123 `os_id` int(11) NOT NULL AUTO_INCREMENT, 0124 `name` varchar(50) DEFAULT '0', 0125 `displayname` varchar(50) DEFAULT '0', 0126 `order` int(11) DEFAULT NULL, 0127 `created_at` datetime DEFAULT NULL, 0128 `changend_at` datetime DEFAULT NULL, 0129 `deleted_at` datetime DEFAULT NULL, 0130 PRIMARY KEY (`os_id`) 0131 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0132 /*!40101 SET character_set_client = @saved_cs_client */; 0133 /*!40101 SET @saved_cs_client = @@character_set_client */; 0134 /*!40101 SET character_set_client = utf8 */; 0135 CREATE TABLE `config_store` ( 0136 `store_id` int(11) NOT NULL AUTO_INCREMENT, 0137 `host` varchar(45) NOT NULL, 0138 `name` varchar(45) NOT NULL, 0139 `config_id_name` varchar(45) NOT NULL, 0140 `mapping_id_name` varchar(45) DEFAULT NULL, 0141 `order` int(11) DEFAULT '0', 0142 `default` int(1) DEFAULT '0', 0143 `is_client` int(1) DEFAULT '0', 0144 `google_id` varchar(45) DEFAULT NULL, 0145 `package_type` varchar(45) DEFAULT NULL COMMENT '1-n package_type_ids', 0146 `cross_domain_login` int(1) NOT NULL DEFAULT '0', 0147 `created_at` datetime DEFAULT NULL, 0148 `changed_at` datetime DEFAULT NULL, 0149 `deleted_at` datetime DEFAULT NULL, 0150 PRIMARY KEY (`store_id`) 0151 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0152 /*!40101 SET character_set_client = @saved_cs_client */; 0153 /*!50003 SET @saved_cs_client = @@character_set_client */ ; 0154 /*!50003 SET @saved_cs_results = @@character_set_results */ ; 0155 /*!50003 SET @saved_col_connection = @@collation_connection */ ; 0156 /*!50003 SET character_set_client = utf8mb4 */ ; 0157 /*!50003 SET character_set_results = utf8mb4 */ ; 0158 /*!50003 SET collation_connection = utf8mb4_general_ci */ ; 0159 /*!50003 SET @saved_sql_mode = @@sql_mode */ ; 0160 /*!50003 SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO' */ ; 0161 DELIMITER ;; 0162 /*!50003 CREATE*/ /*!50017 DEFINER=CURRENT_USER*/ /*!50003 TRIGGER `config_store_BEFORE_INSERT` BEFORE INSERT ON `config_store` FOR EACH ROW BEGIN 0163 IF NEW.created_at IS NULL THEN 0164 SET NEW.created_at = NOW(); 0165 END IF; 0166 END */;; 0167 DELIMITER ; 0168 /*!50003 SET sql_mode = @saved_sql_mode */ ; 0169 /*!50003 SET character_set_client = @saved_cs_client */ ; 0170 /*!50003 SET character_set_results = @saved_cs_results */ ; 0171 /*!50003 SET collation_connection = @saved_col_connection */ ; 0172 /*!40101 SET @saved_cs_client = @@character_set_client */; 0173 /*!40101 SET character_set_client = utf8 */; 0174 CREATE TABLE `config_store_category` ( 0175 `store_category_id` int(11) NOT NULL AUTO_INCREMENT, 0176 `store_id` int(11) DEFAULT NULL, 0177 `project_category_id` int(11) DEFAULT NULL, 0178 `order` int(11) DEFAULT '0', 0179 `created_at` datetime DEFAULT NULL, 0180 `changed_at` datetime DEFAULT NULL, 0181 `deleted_at` datetime DEFAULT NULL, 0182 PRIMARY KEY (`store_category_id`), 0183 KEY `project_category_id_idx` (`project_category_id`), 0184 KEY `fk_store_id_idx` (`store_id`), 0185 CONSTRAINT `fk_project_category_id` FOREIGN KEY (`project_category_id`) REFERENCES `project_category` (`project_category_id`) ON DELETE CASCADE ON UPDATE NO ACTION 0186 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0187 /*!40101 SET character_set_client = @saved_cs_client */; 0188 /*!50003 SET @saved_cs_client = @@character_set_client */ ; 0189 /*!50003 SET @saved_cs_results = @@character_set_results */ ; 0190 /*!50003 SET @saved_col_connection = @@collation_connection */ ; 0191 /*!50003 SET character_set_client = utf8mb4 */ ; 0192 /*!50003 SET character_set_results = utf8mb4 */ ; 0193 /*!50003 SET collation_connection = utf8mb4_general_ci */ ; 0194 /*!50003 SET @saved_sql_mode = @@sql_mode */ ; 0195 /*!50003 SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO' */ ; 0196 DELIMITER ;; 0197 /*!50003 CREATE*/ /*!50017 DEFINER=CURRENT_USER*/ /*!50003 TRIGGER `config_store_category_BEFORE_INSERT` BEFORE INSERT ON `config_store_category` FOR EACH ROW BEGIN 0198 IF NEW.created_at IS NULL THEN 0199 SET NEW.created_at = NOW(); 0200 END IF; 0201 END */;; 0202 DELIMITER ; 0203 /*!50003 SET sql_mode = @saved_sql_mode */ ; 0204 /*!50003 SET character_set_client = @saved_cs_client */ ; 0205 /*!50003 SET character_set_results = @saved_cs_results */ ; 0206 /*!50003 SET collation_connection = @saved_col_connection */ ; 0207 /*!40101 SET @saved_cs_client = @@character_set_client */; 0208 /*!40101 SET character_set_client = utf8 */; 0209 CREATE TABLE `image` ( 0210 `id` int(11) NOT NULL AUTO_INCREMENT, 0211 `code` varchar(255) NOT NULL, 0212 `filename` varchar(255) NOT NULL, 0213 `name` varchar(255) DEFAULT NULL, 0214 `member_id` int(11) DEFAULT NULL, 0215 `model` varchar(255) DEFAULT NULL, 0216 `foreign_key` varchar(255) DEFAULT NULL, 0217 `foreign_id` int(11) DEFAULT NULL, 0218 `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 0219 PRIMARY KEY (`id`) 0220 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 0221 /*!40101 SET character_set_client = @saved_cs_client */; 0222 /*!40101 SET @saved_cs_client = @@character_set_client */; 0223 /*!40101 SET character_set_client = utf8 */; 0224 CREATE TABLE `mail_template` ( 0225 `mail_template_id` int(11) unsigned NOT NULL AUTO_INCREMENT, 0226 `name` varchar(50) NOT NULL, 0227 `subject` varchar(250) NOT NULL, 0228 `text` text NOT NULL, 0229 `created_at` datetime NOT NULL, 0230 `changed_at` datetime DEFAULT NULL, 0231 `deleted_at` datetime DEFAULT NULL, 0232 PRIMARY KEY (`mail_template_id`), 0233 UNIQUE KEY `unique_name` (`name`) 0234 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 0235 /*!40101 SET character_set_client = @saved_cs_client */; 0236 /*!40101 SET @saved_cs_client = @@character_set_client */; 0237 /*!40101 SET character_set_client = utf8 */; 0238 CREATE TABLE `member` ( 0239 `member_id` int(10) NOT NULL AUTO_INCREMENT, 0240 `uuid` varchar(255) DEFAULT NULL, 0241 `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 0242 `mail` varchar(255) DEFAULT NULL, 0243 `password` varchar(255) DEFAULT NULL, 0244 `roleId` int(11) NOT NULL, 0245 `avatar` varchar(255) NOT NULL DEFAULT 'default-profile.png', 0246 `type` int(1) NOT NULL DEFAULT '0' COMMENT 'Type: 0 = Member, 1 = group', 0247 `is_active` int(1) NOT NULL DEFAULT '0', 0248 `is_deleted` int(1) NOT NULL DEFAULT '0', 0249 `mail_checked` int(1) NOT NULL DEFAULT '0', 0250 `agb` int(1) NOT NULL DEFAULT '0', 0251 `newsletter` int(1) NOT NULL DEFAULT '0', 0252 `login_method` varchar(45) NOT NULL DEFAULT 'local' COMMENT 'local (registered on pling), facebook, twitter', 0253 `firstname` varchar(200) DEFAULT NULL, 0254 `lastname` varchar(200) DEFAULT NULL, 0255 `street` varchar(255) DEFAULT NULL, 0256 `zip` varchar(5) DEFAULT NULL, 0257 `city` varchar(255) DEFAULT NULL, 0258 `country` varchar(255) DEFAULT NULL, 0259 `phone` varchar(255) DEFAULT NULL, 0260 `last_online` datetime DEFAULT NULL, 0261 `biography` text, 0262 `paypal_mail` varchar(255) DEFAULT NULL, 0263 `paypal_valid_status` mediumint(9) DEFAULT NULL, 0264 `wallet_address` varchar(255) DEFAULT NULL, 0265 `dwolla_id` varchar(45) DEFAULT NULL, 0266 `main_project_id` int(10) DEFAULT NULL COMMENT 'Die ID des .me-Projekts', 0267 `profile_image_url` varchar(355) DEFAULT '/images/system/default-profile.png' COMMENT 'URL to the profile-image', 0268 `profile_image_url_bg` varchar(355) DEFAULT NULL, 0269 `profile_img_src` varchar(45) DEFAULT 'local' COMMENT 'social,gravatar,local', 0270 `social_username` varchar(50) DEFAULT NULL COMMENT 'Username on facebook/twitter. Used to generate profile-img-url.', 0271 `social_user_id` varchar(50) DEFAULT NULL COMMENT 'ID from twitter, facebook,...', 0272 `gravatar_email` varchar(45) DEFAULT NULL COMMENT 'email, wich is connected to gravatar.', 0273 `facebook_username` varchar(45) DEFAULT NULL, 0274 `twitter_username` varchar(45) DEFAULT NULL, 0275 `link_facebook` varchar(300) DEFAULT NULL COMMENT 'Link to facebook', 0276 `link_twitter` varchar(300) DEFAULT NULL COMMENT 'Link to twitter', 0277 `link_website` varchar(300) DEFAULT NULL COMMENT 'Link to homepage', 0278 `link_google` varchar(300) DEFAULT NULL COMMENT 'Link to google', 0279 `link_github` varchar(300) DEFAULT NULL, 0280 `validated_at` datetime DEFAULT NULL, 0281 `validated` int(1) DEFAULT '0', 0282 `created_at` datetime DEFAULT NULL, 0283 `changed_at` datetime DEFAULT NULL, 0284 `deleted_at` datetime DEFAULT NULL, 0285 `source_id` int(11) DEFAULT '0' COMMENT '0 = local, 1 = hive01', 0286 `source_pk` int(11) DEFAULT NULL COMMENT 'pk on the source', 0287 PRIMARY KEY (`member_id`), 0288 KEY `uuid` (`uuid`), 0289 KEY `idx_created` (`created_at`), 0290 KEY `idx_login` (`mail`,`username`,`password`,`is_active`,`is_deleted`,`login_method`), 0291 KEY `idx_mem_search` (`member_id`,`username`,`is_deleted`,`mail_checked`), 0292 KEY `idx_source` (`source_id`,`source_pk`), 0293 KEY `idx_username` (`username`), 0294 KEY `idx_id_active` (`member_id`,`is_active`,`is_deleted`) 0295 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 0296 /*!40101 SET character_set_client = @saved_cs_client */; 0297 /*!50003 SET @saved_cs_client = @@character_set_client */ ; 0298 /*!50003 SET @saved_cs_results = @@character_set_results */ ; 0299 /*!50003 SET @saved_col_connection = @@collation_connection */ ; 0300 /*!50003 SET character_set_client = utf8mb4 */ ; 0301 /*!50003 SET character_set_results = utf8mb4 */ ; 0302 /*!50003 SET collation_connection = utf8mb4_general_ci */ ; 0303 /*!50003 SET @saved_sql_mode = @@sql_mode */ ; 0304 /*!50003 SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO' */ ; 0305 DELIMITER ;; 0306 /*!50003 CREATE*/ /*!50017 DEFINER=CURRENT_USER*/ /*!50003 TRIGGER `member_created` BEFORE INSERT ON `member` FOR EACH ROW BEGIN 0307 IF NEW.created_at IS NULL THEN 0308 SET NEW.created_at = NOW(); 0309 END IF; 0310 END */;; 0311 DELIMITER ; 0312 /*!50003 SET sql_mode = @saved_sql_mode */ ; 0313 /*!50003 SET character_set_client = @saved_cs_client */ ; 0314 /*!50003 SET character_set_results = @saved_cs_results */ ; 0315 /*!50003 SET collation_connection = @saved_col_connection */ ; 0316 /*!40101 SET @saved_cs_client = @@character_set_client */; 0317 /*!40101 SET character_set_client = utf8 */; 0318 CREATE TABLE `member_dl_plings` ( 0319 `yearmonth` int(6) DEFAULT NULL, 0320 `project_id` int(11) NOT NULL DEFAULT '0', 0321 `project_category_id` int(11) NOT NULL DEFAULT '0', 0322 `member_id` int(11) NOT NULL, 0323 `mail` varchar(255) DEFAULT NULL, 0324 `paypal_mail` varchar(255) DEFAULT NULL, 0325 `num_downloads` bigint(21) NOT NULL DEFAULT '0', 0326 `dl_pling_factor` decimal(3,2) NOT NULL DEFAULT '0.00', 0327 `probably_payout_amount` decimal(25,4) DEFAULT NULL, 0328 `created_at` datetime DEFAULT NULL, 0329 `updated_at` datetime DEFAULT NULL, 0330 UNIQUE KEY `uk_month_proj` (`yearmonth`,`member_id`,`project_id`), 0331 KEY `idx_member` (`member_id`) 0332 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0333 /*!40101 SET character_set_client = @saved_cs_client */; 0334 /*!40101 SET @saved_cs_client = @@character_set_client */; 0335 /*!40101 SET character_set_client = utf8 */; 0336 CREATE TABLE `member_email` ( 0337 `email_id` int(11) NOT NULL AUTO_INCREMENT, 0338 `email_member_id` int(11) NOT NULL, 0339 `email_address` varchar(255) NOT NULL, 0340 `email_primary` int(1) DEFAULT '0', 0341 `email_deleted` int(1) DEFAULT '0', 0342 `email_created` datetime DEFAULT NULL, 0343 `email_checked` datetime DEFAULT NULL, 0344 `email_verification_value` varchar(255) DEFAULT NULL, 0345 PRIMARY KEY (`email_id`), 0346 KEY `idx_address` (`email_address`), 0347 KEY `idx_member` (`email_member_id`), 0348 KEY `idx_verification` (`email_verification_value`) 0349 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0350 /*!40101 SET character_set_client = @saved_cs_client */; 0351 /*!50003 SET @saved_cs_client = @@character_set_client */ ; 0352 /*!50003 SET @saved_cs_results = @@character_set_results */ ; 0353 /*!50003 SET @saved_col_connection = @@collation_connection */ ; 0354 /*!50003 SET character_set_client = utf8mb4 */ ; 0355 /*!50003 SET character_set_results = utf8mb4 */ ; 0356 /*!50003 SET collation_connection = utf8mb4_general_ci */ ; 0357 /*!50003 SET @saved_sql_mode = @@sql_mode */ ; 0358 /*!50003 SET sql_mode = '' */ ; 0359 DELIMITER ;; 0360 /*!50003 CREATE*/ /*!50017 DEFINER=CURRENT_USER*/ /*!50003 TRIGGER pling.member_email_BEFORE_INSERT BEFORE INSERT ON member_email FOR EACH ROW 0361 BEGIN 0362 IF NEW.email_created IS NULL THEN 0363 0364 SET NEW.email_created = NOW(); 0365 0366 END IF; 0367 END */;; 0368 DELIMITER ; 0369 /*!50003 SET sql_mode = @saved_sql_mode */ ; 0370 /*!50003 SET character_set_client = @saved_cs_client */ ; 0371 /*!50003 SET character_set_results = @saved_cs_results */ ; 0372 /*!50003 SET collation_connection = @saved_col_connection */ ; 0373 /*!40101 SET @saved_cs_client = @@character_set_client */; 0374 /*!40101 SET character_set_client = utf8 */; 0375 CREATE TABLE `member_follower` ( 0376 `member_follower_id` int(11) NOT NULL AUTO_INCREMENT, 0377 `member_id` int(11) DEFAULT NULL, 0378 `follower_id` int(11) DEFAULT NULL, 0379 PRIMARY KEY (`member_follower_id`), 0380 KEY `follower_id` (`follower_id`) 0381 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 0382 /*!40101 SET character_set_client = @saved_cs_client */; 0383 /*!40101 SET @saved_cs_client = @@character_set_client */; 0384 /*!40101 SET character_set_client = utf8 */; 0385 CREATE TABLE `member_payout` ( 0386 `id` int(11) NOT NULL AUTO_INCREMENT, 0387 `yearmonth` int(11) NOT NULL, 0388 `member_id` int(11) NOT NULL, 0389 `mail` varchar(50) NOT NULL, 0390 `paypal_mail` varchar(50) DEFAULT NULL, 0391 `num_downloads` int(11) NOT NULL, 0392 `num_points` int(11) NOT NULL, 0393 `amount` double NOT NULL, 0394 `status` int(11) NOT NULL DEFAULT '0' COMMENT '0=new,1=start request,10=processed,100=completed,999=error', 0395 `created_at` datetime NOT NULL, 0396 `updated_at` datetime NOT NULL, 0397 `timestamp_masspay_start` timestamp NULL DEFAULT NULL, 0398 `timestamp_masspay_last_ipn` timestamp NULL DEFAULT NULL, 0399 `last_paypal_ipn` text, 0400 `last_paypal_status` text, 0401 PRIMARY KEY (`id`), 0402 UNIQUE KEY `UK_PAYOUT` (`yearmonth`,`member_id`), 0403 KEY `idx_member` (`member_id`,`yearmonth`) 0404 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Table for our monthly payouts'; 0405 /*!40101 SET character_set_client = @saved_cs_client */; 0406 /*!40101 SET @saved_cs_client = @@character_set_client */; 0407 /*!40101 SET character_set_client = utf8 */; 0408 CREATE TABLE `member_ref` ( 0409 `member_ref_id` int(11) NOT NULL AUTO_INCREMENT, 0410 `member_id` int(11) NOT NULL, 0411 `project_id` int(11) DEFAULT NULL, 0412 PRIMARY KEY (`member_ref_id`) 0413 ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Wich items are interresting for a user. Used for the newsfee'; 0414 /*!40101 SET character_set_client = @saved_cs_client */; 0415 /*!40101 SET @saved_cs_client = @@character_set_client */; 0416 /*!40101 SET character_set_client = utf8 */; 0417 CREATE TABLE `member_role` ( 0418 `member_role_id` int(11) NOT NULL, 0419 `title` varchar(100) NOT NULL, 0420 `shortname` varchar(50) NOT NULL, 0421 `is_active` int(1) NOT NULL DEFAULT '0', 0422 `is_deleted` int(1) NOT NULL DEFAULT '0', 0423 `created_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', 0424 `changed_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', 0425 `deleted_at` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', 0426 PRIMARY KEY (`member_role_id`) 0427 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 0428 /*!40101 SET character_set_client = @saved_cs_client */; 0429 /*!40101 SET @saved_cs_client = @@character_set_client */; 0430 /*!40101 SET character_set_client = utf8 */; 0431 CREATE TABLE `member_token` ( 0432 `token_id` int(11) NOT NULL AUTO_INCREMENT, 0433 `token_member_id` int(11) NOT NULL, 0434 `token_provider_name` varchar(45) NOT NULL, 0435 `token_value` varchar(45) NOT NULL, 0436 `token_provider_username` varchar(45) DEFAULT NULL, 0437 `token_fingerprint` varchar(45) DEFAULT NULL, 0438 `token_created` datetime DEFAULT NULL, 0439 `token_changed` datetime DEFAULT NULL, 0440 `token_deleted` datetime DEFAULT NULL, 0441 PRIMARY KEY (`token_id`), 0442 KEY `idx_token` (`token_member_id`,`token_provider_name`,`token_value`) 0443 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0444 /*!40101 SET character_set_client = @saved_cs_client */; 0445 ALTER DATABASE `ocs-apiserver` CHARACTER SET latin1 COLLATE latin1_swedish_ci ; 0446 /*!50003 SET @saved_cs_client = @@character_set_client */ ; 0447 /*!50003 SET @saved_cs_results = @@character_set_results */ ; 0448 /*!50003 SET @saved_col_connection = @@collation_connection */ ; 0449 /*!50003 SET character_set_client = utf8 */ ; 0450 /*!50003 SET character_set_results = utf8 */ ; 0451 /*!50003 SET collation_connection = utf8_general_ci */ ; 0452 /*!50003 SET @saved_sql_mode = @@sql_mode */ ; 0453 /*!50003 SET sql_mode = 'STRICT_ALL_TABLES' */ ; 0454 DELIMITER ;; 0455 /*!50003 CREATE*/ /*!50017 DEFINER=CURRENT_USER*/ /*!50003 TRIGGER `ocs-apiserver`.`member_token_BEFORE_INSERT` BEFORE INSERT ON `member_token` FOR EACH ROW 0456 BEGIN 0457 IF NEW.token_created IS NULL THEN 0458 SET NEW.token_created = NOW(); 0459 END IF; 0460 END */;; 0461 DELIMITER ; 0462 /*!50003 SET sql_mode = @saved_sql_mode */ ; 0463 /*!50003 SET character_set_client = @saved_cs_client */ ; 0464 /*!50003 SET character_set_results = @saved_cs_results */ ; 0465 /*!50003 SET collation_connection = @saved_col_connection */ ; 0466 ALTER DATABASE `ocs-apiserver` CHARACTER SET latin1 COLLATE latin1_general_ci ; 0467 /*!40101 SET @saved_cs_client = @@character_set_client */; 0468 /*!40101 SET character_set_client = utf8 */; 0469 CREATE TABLE `package_types` ( 0470 `package_type_id` int(11) NOT NULL AUTO_INCREMENT, 0471 `name` varchar(255) NOT NULL, 0472 `order` int(11) DEFAULT NULL, 0473 `is_active` int(1) DEFAULT '1', 0474 PRIMARY KEY (`package_type_id`) 0475 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0476 /*!40101 SET character_set_client = @saved_cs_client */; 0477 /*!40101 SET @saved_cs_client = @@character_set_client */; 0478 /*!40101 SET character_set_client = utf8 */; 0479 CREATE TABLE `paypal_valid_status` ( 0480 `id` int(11) NOT NULL, 0481 `title` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, 0482 `description` text COLLATE latin1_general_ci, 0483 `color` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, 0484 `is_active` int(1) DEFAULT '1', 0485 PRIMARY KEY (`id`) 0486 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 0487 /*!40101 SET character_set_client = @saved_cs_client */; 0488 /*!40101 SET @saved_cs_client = @@character_set_client */; 0489 /*!40101 SET character_set_client = utf8 */; 0490 CREATE TABLE `plings` ( 0491 `id` int(11) NOT NULL AUTO_INCREMENT, 0492 `member_id` int(11) NOT NULL COMMENT 'pling-Owner', 0493 `project_id` int(11) DEFAULT NULL COMMENT 'Witch project was plinged', 0494 `status_id` int(11) DEFAULT '0' COMMENT 'Stati des pling: 0 = inactive, 1 = active (plinged), 2 = payed successfull, 99 = deleted', 0495 `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation-time', 0496 `pling_time` timestamp NULL DEFAULT NULL COMMENT 'When was a project plinged?', 0497 `active_time` timestamp NULL DEFAULT NULL COMMENT 'When did paypal say, that this pling was payed successfull', 0498 `delete_time` timestamp NULL DEFAULT NULL, 0499 `amount` double(10,2) DEFAULT '0.00' COMMENT 'Amount of money', 0500 `comment` varchar(140) DEFAULT NULL COMMENT 'Comment from the plinger', 0501 `payment_provider` varchar(45) DEFAULT NULL, 0502 `payment_reference_key` varchar(255) DEFAULT NULL COMMENT 'uniquely identifies the request', 0503 `payment_transaction_id` varchar(255) DEFAULT NULL COMMENT 'uniquely identify caller (developer, facilliator, marketplace) transaction', 0504 `payment_raw_message` varchar(2000) DEFAULT NULL COMMENT 'the raw text message ', 0505 `payment_raw_error` varchar(2000) DEFAULT NULL, 0506 `payment_status` varchar(45) DEFAULT NULL, 0507 PRIMARY KEY (`id`), 0508 KEY `project_id` (`project_id`), 0509 KEY `status_id` (`status_id`), 0510 KEY `member_id` (`member_id`), 0511 KEY `PLINGS_IX_01` (`status_id`,`project_id`,`member_id`,`active_time`,`amount`) 0512 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0513 /*!40101 SET character_set_client = @saved_cs_client */; 0514 /*!40101 SET @saved_cs_client = @@character_set_client */; 0515 /*!40101 SET character_set_client = utf8 */; 0516 CREATE TABLE `project` ( 0517 `project_id` int(11) NOT NULL AUTO_INCREMENT, 0518 `member_id` int(11) NOT NULL DEFAULT '0', 0519 `content_type` varchar(255) NOT NULL DEFAULT 'text', 0520 `project_category_id` int(11) NOT NULL DEFAULT '0', 0521 `hive_category_id` int(11) NOT NULL DEFAULT '0', 0522 `is_active` int(1) NOT NULL DEFAULT '0', 0523 `is_deleted` int(1) NOT NULL DEFAULT '0', 0524 `status` int(11) NOT NULL DEFAULT '0', 0525 `uuid` varchar(255) DEFAULT NULL, 0526 `pid` int(11) DEFAULT NULL COMMENT 'ParentId', 0527 `type_id` int(11) DEFAULT '0' COMMENT '0 = DummyProject, 1 = Project, 2 = Update', 0528 `title` varchar(100) DEFAULT NULL, 0529 `description` text, 0530 `version` varchar(50) DEFAULT NULL, 0531 `image_big` varchar(255) DEFAULT NULL, 0532 `image_small` varchar(255) DEFAULT NULL, 0533 `start_date` datetime DEFAULT NULL, 0534 `content_url` varchar(255) DEFAULT NULL, 0535 `created_at` datetime DEFAULT NULL, 0536 `changed_at` datetime DEFAULT NULL, 0537 `deleted_at` datetime DEFAULT NULL, 0538 `creator_id` int(11) DEFAULT NULL COMMENT 'Member_id of the creator. Importent for groups.', 0539 `facebook_code` text, 0540 `github_code` text, 0541 `twitter_code` text, 0542 `google_code` text, 0543 `link_1` text, 0544 `embed_code` text, 0545 `ppload_collection_id` varchar(255) DEFAULT NULL, 0546 `validated` int(1) DEFAULT '0', 0547 `validated_at` datetime DEFAULT NULL, 0548 `featured` int(1) DEFAULT '0', 0549 `approved` int(1) DEFAULT '0', 0550 `spam_checked` int(1) NOT NULL DEFAULT '0', 0551 `amount` int(11) DEFAULT NULL, 0552 `amount_period` varchar(45) DEFAULT NULL, 0553 `claimable` int(1) DEFAULT NULL, 0554 `claimed_by_member` int(11) DEFAULT NULL, 0555 `count_likes` int(11) DEFAULT '0', 0556 `count_dislikes` int(11) DEFAULT '0', 0557 `count_comments` int(11) DEFAULT '0', 0558 `count_downloads_hive` int(11) DEFAULT '0', 0559 `source_id` int(11) DEFAULT '0', 0560 `source_pk` int(11) DEFAULT NULL, 0561 `source_type` varchar(50) DEFAULT NULL, 0562 PRIMARY KEY (`project_id`), 0563 UNIQUE KEY `uk_source` (`source_id`,`source_pk`,`source_type`), 0564 KEY `idx_project_cat_id` (`project_category_id`), 0565 KEY `idx_uuid` (`uuid`), 0566 KEY `idx_member_id` (`member_id`), 0567 KEY `idx_pid` (`pid`), 0568 KEY `idx_created_at` (`created_at`), 0569 KEY `idx_title` (`title`), 0570 KEY `idx_source` (`source_id`,`source_pk`,`source_type`), 0571 KEY `idx_status` (`status`,`ppload_collection_id`,`project_category_id`,`project_id`), 0572 KEY `idx_type_status` (`type_id`,`status`,`project_category_id`,`project_id`), 0573 KEY `idx_ppload` (`ppload_collection_id`,`status`), 0574 KEY `idx_src_status` (`status`,`source_pk`,`source_type`) 0575 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 0576 /*!40101 SET character_set_client = @saved_cs_client */; 0577 /*!40101 SET @saved_cs_client = @@character_set_client */; 0578 /*!40101 SET character_set_client = utf8 */; 0579 CREATE TABLE `project_category` ( 0580 `project_category_id` int(11) NOT NULL AUTO_INCREMENT, 0581 `lft` int(11) NOT NULL, 0582 `rgt` int(11) NOT NULL, 0583 `title` varchar(100) NOT NULL, 0584 `is_active` int(1) NOT NULL DEFAULT '0', 0585 `is_deleted` int(1) NOT NULL DEFAULT '0', 0586 `xdg_type` varchar(50) DEFAULT NULL, 0587 `name_legacy` varchar(50) DEFAULT NULL, 0588 `orderPos` int(11) DEFAULT NULL, 0589 `dl_pling_factor` double unsigned DEFAULT '1', 0590 `show_description` int(1) NOT NULL DEFAULT '0', 0591 `created_at` datetime DEFAULT NULL, 0592 `changed_at` datetime DEFAULT NULL, 0593 `deleted_at` datetime DEFAULT NULL, 0594 PRIMARY KEY (`project_category_id`), 0595 KEY `idxLeft` (`project_category_id`,`lft`), 0596 KEY `idxRight` (`project_category_id`,`rgt`), 0597 KEY `idxPrimaryRgtLft` (`project_category_id`,`rgt`,`lft`,`is_active`,`is_deleted`), 0598 KEY `idxActive` (`is_active`) 0599 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 0600 /*!40101 SET character_set_client = @saved_cs_client */; 0601 /*!40101 SET @saved_cs_client = @@character_set_client */; 0602 /*!40101 SET character_set_client = utf8 */; 0603 CREATE TABLE `project_cc_license` ( 0604 `license_id` int(11) NOT NULL AUTO_INCREMENT, 0605 `project_id` int(11) NOT NULL, 0606 `by` int(1) DEFAULT NULL, 0607 `nc` int(1) DEFAULT NULL, 0608 `nd` int(1) DEFAULT NULL, 0609 `sa` int(1) DEFAULT NULL, 0610 PRIMARY KEY (`license_id`) 0611 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0612 /*!40101 SET character_set_client = @saved_cs_client */; 0613 /*!40101 SET @saved_cs_client = @@character_set_client */; 0614 /*!40101 SET character_set_client = utf8 */; 0615 CREATE TABLE `project_follower` ( 0616 `project_follower_id` int(11) NOT NULL AUTO_INCREMENT, 0617 `project_id` int(11) DEFAULT NULL, 0618 `member_id` int(11) DEFAULT NULL, 0619 PRIMARY KEY (`project_follower_id`), 0620 KEY `FIND_FOLLOWER` (`project_id`,`member_id`) 0621 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 0622 /*!40101 SET character_set_client = @saved_cs_client */; 0623 /*!40101 SET @saved_cs_client = @@character_set_client */; 0624 /*!40101 SET character_set_client = utf8 */; 0625 CREATE TABLE `project_gallery_picture` ( 0626 `project_id` int(11) NOT NULL, 0627 `sequence` int(11) NOT NULL, 0628 `picture_src` varchar(255) NOT NULL, 0629 PRIMARY KEY (`project_id`,`sequence`) 0630 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0631 /*!40101 SET character_set_client = @saved_cs_client */; 0632 /*!40101 SET @saved_cs_client = @@character_set_client */; 0633 /*!40101 SET character_set_client = utf8 */; 0634 CREATE TABLE `project_package_type` ( 0635 `project_id` int(11) NOT NULL, 0636 `file_id` int(11) NOT NULL, 0637 `package_type_id` int(11) NOT NULL, 0638 PRIMARY KEY (`project_id`,`file_id`), 0639 KEY `idx_type_id` (`package_type_id`) 0640 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0641 /*!40101 SET character_set_client = @saved_cs_client */; 0642 /*!40101 SET @saved_cs_client = @@character_set_client */; 0643 /*!40101 SET character_set_client = utf8 */; 0644 CREATE TABLE `project_rating` ( 0645 `rating_id` int(11) NOT NULL AUTO_INCREMENT, 0646 `member_id` int(11) NOT NULL DEFAULT '0', 0647 `project_id` int(11) NOT NULL DEFAULT '0', 0648 `user_like` int(1) DEFAULT '0', 0649 `user_dislike` int(1) DEFAULT '0', 0650 `comment_id` int(11) DEFAULT '0' COMMENT 'review for rating', 0651 `rating_active` int(1) DEFAULT '1' COMMENT 'active = 1, deleted = 0', 0652 `source_id` int(1) DEFAULT '0', 0653 `source_pk` int(1) DEFAULT '0', 0654 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, 0655 PRIMARY KEY (`rating_id`), 0656 KEY `idx_project_id` (`project_id`) 0657 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0658 /*!40101 SET character_set_client = @saved_cs_client */; 0659 /*!40101 SET @saved_cs_client = @@character_set_client */; 0660 /*!40101 SET character_set_client = utf8 */; 0661 CREATE TABLE `project_updates` ( 0662 `project_update_id` int(11) NOT NULL AUTO_INCREMENT, 0663 `project_id` int(11) NOT NULL DEFAULT '0', 0664 `member_id` int(11) NOT NULL DEFAULT '0', 0665 `public` int(1) NOT NULL DEFAULT '0', 0666 `title` varchar(200) DEFAULT NULL, 0667 `text` text, 0668 `created_at` datetime DEFAULT '0000-00-00 00:00:00', 0669 `changed_at` datetime DEFAULT '0000-00-00 00:00:00', 0670 `source_id` int(11) DEFAULT '0', 0671 `source_pk` int(11) DEFAULT NULL, 0672 PRIMARY KEY (`project_update_id`) 0673 ) ENGINE=MyISAM DEFAULT CHARSET=utf8; 0674 /*!40101 SET character_set_client = @saved_cs_client */; 0675 /*!40101 SET @saved_cs_client = @@character_set_client */; 0676 /*!40101 SET character_set_client = utf8 */; 0677 CREATE TABLE `project_widget` ( 0678 `widget_id` int(11) NOT NULL AUTO_INCREMENT, 0679 `uuid` varchar(255) DEFAULT NULL, 0680 `project_id` int(11) DEFAULT NULL, 0681 `config` text, 0682 PRIMARY KEY (`widget_id`), 0683 KEY `idxPROJECT` (`project_id`), 0684 KEY `idxUUID` (`uuid`) 0685 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0686 /*!40101 SET character_set_client = @saved_cs_client */; 0687 /*!40101 SET @saved_cs_client = @@character_set_client */; 0688 /*!40101 SET character_set_client = utf8 */; 0689 CREATE TABLE `project_widget_default` ( 0690 `widget_id` int(11) NOT NULL AUTO_INCREMENT, 0691 `uuid` varchar(255) DEFAULT NULL, 0692 `project_id` int(11) DEFAULT NULL, 0693 `config` text, 0694 PRIMARY KEY (`widget_id`), 0695 KEY `idxPROJECT` (`project_id`), 0696 KEY `idxUuid` (`uuid`) 0697 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0698 /*!40101 SET character_set_client = @saved_cs_client */; 0699 /*!40101 SET @saved_cs_client = @@character_set_client */; 0700 /*!40101 SET character_set_client = utf8 */; 0701 CREATE TABLE `queue` ( 0702 `queue_id` int(10) unsigned NOT NULL AUTO_INCREMENT, 0703 `queue_name` varchar(100) NOT NULL, 0704 `timeout` smallint(5) unsigned NOT NULL DEFAULT '30', 0705 PRIMARY KEY (`queue_id`) 0706 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 0707 /*!40101 SET character_set_client = @saved_cs_client */; 0708 /*!40101 SET @saved_cs_client = @@character_set_client */; 0709 /*!40101 SET character_set_client = utf8 */; 0710 CREATE TABLE `queue_message` ( 0711 `message_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, 0712 `queue_id` int(10) unsigned NOT NULL, 0713 `handle` char(32) DEFAULT NULL, 0714 `body` text NOT NULL, 0715 `md5` char(32) NOT NULL, 0716 `timeout` decimal(14,4) unsigned DEFAULT NULL, 0717 `created` int(10) unsigned NOT NULL, 0718 PRIMARY KEY (`message_id`), 0719 UNIQUE KEY `message_handle` (`handle`), 0720 KEY `message_queueid` (`queue_id`), 0721 CONSTRAINT `queue_message_ibfk_1` FOREIGN KEY (`queue_id`) REFERENCES `queue` (`queue_id`) ON DELETE CASCADE ON UPDATE CASCADE 0722 ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 0723 /*!40101 SET character_set_client = @saved_cs_client */; 0724 /*!40101 SET @saved_cs_client = @@character_set_client */; 0725 /*!40101 SET character_set_client = utf8 */; 0726 CREATE TABLE `reports_comment` ( 0727 `report_id` int(11) NOT NULL AUTO_INCREMENT, 0728 `project_id` int(11) NOT NULL, 0729 `comment_id` int(11) NOT NULL, 0730 `reported_by` int(11) NOT NULL, 0731 `is_deleted` int(1) DEFAULT NULL, 0732 `is_active` int(1) DEFAULT NULL, 0733 `created_at` datetime DEFAULT NULL, 0734 PRIMARY KEY (`report_id`), 0735 KEY `idxComment` (`comment_id`), 0736 KEY `idxMember` (`reported_by`) 0737 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0738 /*!40101 SET character_set_client = @saved_cs_client */; 0739 /*!50003 SET @saved_cs_client = @@character_set_client */ ; 0740 /*!50003 SET @saved_cs_results = @@character_set_results */ ; 0741 /*!50003 SET @saved_col_connection = @@collation_connection */ ; 0742 /*!50003 SET character_set_client = utf8 */ ; 0743 /*!50003 SET character_set_results = utf8 */ ; 0744 /*!50003 SET collation_connection = utf8_general_ci */ ; 0745 /*!50003 SET @saved_sql_mode = @@sql_mode */ ; 0746 /*!50003 SET sql_mode = '' */ ; 0747 DELIMITER ;; 0748 /*!50003 CREATE*/ /*!50017 DEFINER=CURRENT_USER*/ /*!50003 TRIGGER `report_comment_created` BEFORE INSERT ON `reports_comment` FOR EACH ROW 0749 BEGIN 0750 IF NEW.created_at IS NULL THEN 0751 SET NEW.created_at = NOW(); 0752 END IF; 0753 END */;; 0754 DELIMITER ; 0755 /*!50003 SET sql_mode = @saved_sql_mode */ ; 0756 /*!50003 SET character_set_client = @saved_cs_client */ ; 0757 /*!50003 SET character_set_results = @saved_cs_results */ ; 0758 /*!50003 SET collation_connection = @saved_col_connection */ ; 0759 /*!40101 SET @saved_cs_client = @@character_set_client */; 0760 /*!40101 SET character_set_client = utf8 */; 0761 CREATE TABLE `reports_member` ( 0762 `report_id` int(11) NOT NULL AUTO_INCREMENT, 0763 `member_id` int(11) NOT NULL, 0764 `reported_by` int(11) NOT NULL, 0765 `is_deleted` int(1) DEFAULT NULL, 0766 `created_at` datetime DEFAULT NULL, 0767 PRIMARY KEY (`report_id`), 0768 KEY `idxMemberId` (`member_id`), 0769 KEY `idxReportedBy` (`reported_by`) 0770 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0771 /*!40101 SET character_set_client = @saved_cs_client */; 0772 /*!50003 SET @saved_cs_client = @@character_set_client */ ; 0773 /*!50003 SET @saved_cs_results = @@character_set_results */ ; 0774 /*!50003 SET @saved_col_connection = @@collation_connection */ ; 0775 /*!50003 SET character_set_client = utf8 */ ; 0776 /*!50003 SET character_set_results = utf8 */ ; 0777 /*!50003 SET collation_connection = utf8_general_ci */ ; 0778 /*!50003 SET @saved_sql_mode = @@sql_mode */ ; 0779 /*!50003 SET sql_mode = '' */ ; 0780 DELIMITER ;; 0781 /*!50003 CREATE*/ /*!50017 DEFINER=CURRENT_USER*/ /*!50003 TRIGGER `reports_member_created` BEFORE INSERT ON `reports_member` FOR EACH ROW 0782 BEGIN 0783 IF NEW.created_at IS NULL THEN 0784 SET NEW.created_at = NOW(); 0785 END IF; 0786 0787 END */;; 0788 DELIMITER ; 0789 /*!50003 SET sql_mode = @saved_sql_mode */ ; 0790 /*!50003 SET character_set_client = @saved_cs_client */ ; 0791 /*!50003 SET character_set_results = @saved_cs_results */ ; 0792 /*!50003 SET collation_connection = @saved_col_connection */ ; 0793 /*!40101 SET @saved_cs_client = @@character_set_client */; 0794 /*!40101 SET character_set_client = utf8 */; 0795 CREATE TABLE `reports_project` ( 0796 `report_id` int(11) NOT NULL AUTO_INCREMENT, 0797 `project_id` int(11) NOT NULL, 0798 `reported_by` int(11) NOT NULL, 0799 `is_deleted` int(1) NOT NULL DEFAULT '0', 0800 `created_at` datetime DEFAULT NULL, 0801 PRIMARY KEY (`report_id`), 0802 KEY `idxReport` (`project_id`,`reported_by`,`is_deleted`,`created_at`) 0803 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0804 /*!40101 SET character_set_client = @saved_cs_client */; 0805 /*!40101 SET @saved_cs_client = @@character_set_client */; 0806 /*!40101 SET character_set_client = utf8 */; 0807 CREATE TABLE `session` ( 0808 `session_id` int(11) NOT NULL AUTO_INCREMENT, 0809 `member_id` int(11) NOT NULL, 0810 `remember_me_id` varchar(255) NOT NULL, 0811 `expiry` datetime DEFAULT NULL, 0812 `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP, 0813 `changed` timestamp NULL DEFAULT NULL, 0814 PRIMARY KEY (`session_id`), 0815 KEY `idx_remember` (`member_id`,`remember_me_id`) 0816 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0817 /*!40101 SET character_set_client = @saved_cs_client */; 0818 /*!40101 SET @saved_cs_client = @@character_set_client */; 0819 /*!40101 SET character_set_client = utf8 */; 0820 CREATE TABLE `stat_cat_prod_count` ( 0821 `project_category_id` int(11) NOT NULL, 0822 `package_type_id` int(11) DEFAULT NULL, 0823 `count_product` int(11) DEFAULT NULL, 0824 KEY `idx_package` (`project_category_id`,`package_type_id`) 0825 ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 0826 /*!40101 SET character_set_client = @saved_cs_client */; 0827 /*!40101 SET @saved_cs_client = @@character_set_client */; 0828 /*!40101 SET character_set_client = utf8 */; 0829 CREATE TABLE `stat_cat_tree` ( 0830 `project_category_id` int(11) NOT NULL, 0831 `lft` int(11) NOT NULL, 0832 `rgt` int(11) NOT NULL, 0833 `title` varchar(100) COLLATE latin1_general_ci NOT NULL, 0834 `name_legacy` varchar(50) COLLATE latin1_general_ci DEFAULT NULL, 0835 `is_active` int(1) DEFAULT NULL, 0836 `orderPos` int(11) DEFAULT NULL, 0837 `depth` int(11) NOT NULL, 0838 `ancestor_id_path` varchar(100) COLLATE latin1_general_ci DEFAULT NULL, 0839 `ancestor_path` varchar(256) COLLATE latin1_general_ci DEFAULT NULL, 0840 `ancestor_path_legacy` varchar(256) COLLATE latin1_general_ci DEFAULT NULL, 0841 `stores` varchar(256) COLLATE latin1_general_ci DEFAULT NULL, 0842 PRIMARY KEY (`project_category_id`,`lft`,`rgt`) 0843 ) ENGINE=MEMORY DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 0844 /*!40101 SET character_set_client = @saved_cs_client */; 0845 /*!40101 SET @saved_cs_client = @@character_set_client */; 0846 /*!40101 SET character_set_client = utf8 */; 0847 CREATE TABLE `stat_daily` ( 0848 `daily_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', 0849 `project_id` int(11) NOT NULL COMMENT 'ID of the project', 0850 `project_category_id` int(11) DEFAULT '0' COMMENT 'Category', 0851 `project_type_id` int(11) NOT NULL COMMENT 'type of the project', 0852 `count_views` int(11) DEFAULT '0', 0853 `count_plings` int(11) DEFAULT '0', 0854 `count_updates` int(11) DEFAULT NULL, 0855 `count_comments` int(11) DEFAULT NULL, 0856 `count_followers` int(11) DEFAULT NULL, 0857 `count_supporters` int(11) DEFAULT NULL, 0858 `count_money` float DEFAULT NULL, 0859 `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, 0860 `year` int(11) DEFAULT NULL COMMENT 'z.B.: 1988', 0861 `month` int(11) DEFAULT NULL COMMENT 'z.b: 1-12', 0862 `day` int(11) DEFAULT NULL COMMENT 'z.B. 1-31', 0863 `year_week` int(11) DEFAULT NULL COMMENT 'z.b.: 201232', 0864 `ranking_value` float DEFAULT NULL, 0865 PRIMARY KEY (`daily_id`), 0866 KEY `indexKeys` (`project_id`,`project_category_id`,`project_type_id`), 0867 KEY `project_id` (`project_id`), 0868 KEY `project_category_id` (`project_category_id`), 0869 KEY `project_type_id` (`project_type_id`) 0870 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='Store daily statistic'; 0871 /*!40101 SET character_set_client = @saved_cs_client */; 0872 /*!40101 SET @saved_cs_client = @@character_set_client */; 0873 /*!40101 SET character_set_client = utf8 */; 0874 CREATE TABLE `stat_daily_pageviews` ( 0875 `project_id` int(11) NOT NULL COMMENT 'ID of the project', 0876 `cnt` int(11) DEFAULT NULL, 0877 `project_category_id` int(11) NOT NULL, 0878 `created_at` date DEFAULT NULL 0879 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0880 /*!40101 SET character_set_client = @saved_cs_client */; 0881 /*!40101 SET @saved_cs_client = @@character_set_client */; 0882 /*!40101 SET character_set_client = utf8 */; 0883 CREATE TABLE `stat_downloads_half_year` ( 0884 `project_id` int(11) NOT NULL DEFAULT '0', 0885 `project_category_id` int(11) NOT NULL DEFAULT '0', 0886 `ppload_collection_id` varchar(255) CHARACTER SET utf8 DEFAULT NULL, 0887 `amount` bigint(21) NOT NULL DEFAULT '0', 0888 `category_title` varchar(100) CHARACTER SET utf8 NOT NULL, 0889 KEY `idx_project_id` (`project_id`) 0890 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0891 /*!40101 SET character_set_client = @saved_cs_client */; 0892 /*!40101 SET @saved_cs_client = @@character_set_client */; 0893 /*!40101 SET character_set_client = utf8 */; 0894 CREATE TABLE `stat_downloads_quarter_year` ( 0895 `project_id` int(11) NOT NULL DEFAULT '0', 0896 `project_category_id` int(11) NOT NULL DEFAULT '0', 0897 `ppload_collection_id` varchar(255) CHARACTER SET utf8 DEFAULT NULL, 0898 `amount` bigint(21) NOT NULL DEFAULT '0', 0899 `category_title` varchar(100) CHARACTER SET utf8 NOT NULL, 0900 KEY `idx_project_id` (`project_id`), 0901 KEY `idx_collection_id` (`ppload_collection_id`) 0902 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0903 /*!40101 SET character_set_client = @saved_cs_client */; 0904 SET @saved_cs_client = @@character_set_client; 0905 SET character_set_client = utf8; 0906 /*!50001 CREATE TABLE `stat_now` ( 0907 `project_id` tinyint NOT NULL, 0908 `project_type_id` tinyint NOT NULL, 0909 `project_category_id` tinyint NOT NULL, 0910 `count_views` tinyint NOT NULL, 0911 `count_plings` tinyint NOT NULL, 0912 `count_updates` tinyint NOT NULL, 0913 `count_comments` tinyint NOT NULL, 0914 `count_followers` tinyint NOT NULL, 0915 `count_supporters` tinyint NOT NULL, 0916 `count_money` tinyint NOT NULL, 0917 `ranking_value` tinyint NOT NULL, 0918 `created_at` tinyint NOT NULL, 0919 `year` tinyint NOT NULL, 0920 `month` tinyint NOT NULL, 0921 `day` tinyint NOT NULL, 0922 `year_week` tinyint NOT NULL 0923 ) ENGINE=MyISAM */; 0924 SET character_set_client = @saved_cs_client; 0925 /*!40101 SET @saved_cs_client = @@character_set_client */; 0926 /*!40101 SET character_set_client = utf8 */; 0927 CREATE TABLE `stat_page_views` ( 0928 `stat_page_views_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', 0929 `project_id` int(11) NOT NULL COMMENT 'ID of the project', 0930 `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Timestamp of the view', 0931 `ip` varchar(45) NOT NULL COMMENT 'User-IP', 0932 `member_id` int(11) DEFAULT NULL COMMENT 'ID of the member, if possible', 0933 PRIMARY KEY (`stat_page_views_id`), 0934 KEY `idx_created` (`created_at`,`project_id`), 0935 KEY `project_id` (`project_id`), 0936 KEY `idx_member` (`member_id`,`created_at`) 0937 ) ENGINE=InnoDB AUTO_INCREMENT=16232 DEFAULT CHARSET=utf8 COMMENT='Counter of project-page views'; 0938 /*!40101 SET character_set_client = @saved_cs_client */; 0939 /*!40101 SET @saved_cs_client = @@character_set_client */; 0940 /*!40101 SET character_set_client = utf8 */; 0941 CREATE TABLE `stat_page_views_mv` ( 0942 `project_id` int(11) NOT NULL COMMENT 'ID of the project', 0943 `count_views` bigint(21) NOT NULL DEFAULT '0', 0944 `count_visitor` bigint(21) NOT NULL DEFAULT '0', 0945 `last_view` timestamp NULL DEFAULT NULL COMMENT 'Timestamp of the view', 0946 KEY `idx_project_id` (`project_id`) 0947 ) ENGINE=InnoDB DEFAULT CHARSET=latin1; 0948 /*!40101 SET character_set_client = @saved_cs_client */; 0949 /*!40101 SET @saved_cs_client = @@character_set_client */; 0950 /*!40101 SET character_set_client = utf8 */; 0951 CREATE TABLE `stat_page_views_today_mv` ( 0952 `id` int(11) NOT NULL AUTO_INCREMENT, 0953 `project_id` int(11) NOT NULL COMMENT 'ID of the project', 0954 `count_views` int(11) DEFAULT '0', 0955 `count_visitor` int(11) DEFAULT '0', 0956 `last_view` datetime DEFAULT NULL COMMENT 'Timestamp of the view', 0957 PRIMARY KEY (`id`), 0958 KEY `idx_project` (`project_id`) 0959 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 0960 /*!40101 SET character_set_client = @saved_cs_client */; 0961 SET @saved_cs_client = @@character_set_client; 0962 SET character_set_client = utf8; 0963 /*!50001 CREATE TABLE `stat_plings` ( 0964 `project_id` tinyint NOT NULL, 0965 `amount_received` tinyint NOT NULL, 0966 `count_plings` tinyint NOT NULL, 0967 `count_plingers` tinyint NOT NULL, 0968 `latest_pling` tinyint NOT NULL 0969 ) ENGINE=MyISAM */; 0970 SET character_set_client = @saved_cs_client; 0971 /*!40101 SET @saved_cs_client = @@character_set_client */; 0972 /*!40101 SET character_set_client = utf8 */; 0973 CREATE TABLE `stat_projects` ( 0974 `project_id` int(11) NOT NULL DEFAULT '0', 0975 `member_id` int(11) NOT NULL DEFAULT '0', 0976 `content_type` varchar(255) CHARACTER SET utf8 NOT NULL DEFAULT 'text', 0977 `project_category_id` int(11) NOT NULL DEFAULT '0', 0978 `hive_category_id` int(11) NOT NULL DEFAULT '0', 0979 `status` int(11) NOT NULL DEFAULT '0', 0980 `uuid` varchar(255) CHARACTER SET utf8 DEFAULT NULL, 0981 `pid` int(11) DEFAULT NULL COMMENT 'ParentId', 0982 `type_id` int(11) DEFAULT '0' COMMENT '0 = DummyProject, 1 = Project, 2 = Update', 0983 `title` varchar(100) CHARACTER SET utf8 DEFAULT NULL, 0984 `description` text CHARACTER SET utf8, 0985 `version` varchar(50) CHARACTER SET utf8 DEFAULT NULL, 0986 `image_big` varchar(255) CHARACTER SET utf8 DEFAULT NULL, 0987 `image_small` varchar(255) CHARACTER SET utf8 DEFAULT NULL, 0988 `start_date` datetime DEFAULT NULL, 0989 `content_url` varchar(255) CHARACTER SET utf8 DEFAULT NULL, 0990 `created_at` datetime DEFAULT NULL, 0991 `changed_at` datetime DEFAULT NULL, 0992 `deleted_at` datetime DEFAULT NULL, 0993 `creator_id` int(11) DEFAULT NULL COMMENT 'Member_id of the creator. Importent for groups.', 0994 `facebook_code` text CHARACTER SET utf8, 0995 `github_code` text CHARACTER SET utf8, 0996 `twitter_code` text CHARACTER SET utf8, 0997 `google_code` text CHARACTER SET utf8, 0998 `link_1` text CHARACTER SET utf8, 0999 `embed_code` text CHARACTER SET utf8, 1000 `ppload_collection_id` varchar(255) CHARACTER SET utf8 DEFAULT NULL, 1001 `validated` int(1) DEFAULT '0', 1002 `validated_at` datetime DEFAULT NULL, 1003 `featured` int(1) DEFAULT '0', 1004 `approved` int(1) DEFAULT '0', 1005 `amount` int(11) DEFAULT NULL, 1006 `amount_period` varchar(45) CHARACTER SET utf8 DEFAULT NULL, 1007 `claimable` int(1) DEFAULT NULL, 1008 `claimed_by_member` int(11) DEFAULT NULL, 1009 `count_likes` int(11) DEFAULT '0', 1010 `count_dislikes` int(11) DEFAULT '0', 1011 `count_comments` int(11) DEFAULT '0', 1012 `count_downloads_hive` int(11) DEFAULT '0', 1013 `source_id` int(11) DEFAULT '0', 1014 `source_pk` int(11) DEFAULT NULL, 1015 `source_type` varchar(50) CHARACTER SET utf8 DEFAULT NULL, 1016 `project_validated` int(1) DEFAULT '0', 1017 `project_uuid` varchar(255) CHARACTER SET utf8 DEFAULT NULL, 1018 `project_status` int(11) NOT NULL DEFAULT '0', 1019 `project_created_at` datetime DEFAULT NULL, 1020 `project_changed_at` datetime DEFAULT NULL, 1021 `laplace_score` int(11) DEFAULT NULL, 1022 `member_type` int(1) NOT NULL DEFAULT '0' COMMENT 'Type: 0 = Member, 1 = group', 1023 `project_member_id` int(10) NOT NULL DEFAULT '0', 1024 `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, 1025 `profile_image_url` varchar(355) CHARACTER SET utf8 DEFAULT '/images/system/default-profile.png' COMMENT 'URL to the profile-image', 1026 `city` varchar(255) CHARACTER SET utf8 DEFAULT NULL, 1027 `country` varchar(255) CHARACTER SET utf8 DEFAULT NULL, 1028 `member_created_at` datetime DEFAULT NULL, 1029 `paypal_mail` varchar(255) CHARACTER SET utf8 DEFAULT NULL, 1030 `cat_title` varchar(100) CHARACTER SET utf8 NOT NULL, 1031 `cat_xdg_type` varchar(50) CHARACTER SET utf8 DEFAULT NULL, 1032 `cat_name_legacy` varchar(50) CHARACTER SET utf8 DEFAULT NULL, 1033 `cat_show_description` int(1) NOT NULL DEFAULT '0', 1034 `amount_received` double(19,2) DEFAULT NULL, 1035 `count_plings` bigint(21) DEFAULT '0', 1036 `count_plingers` bigint(21) DEFAULT '0', 1037 `latest_pling` timestamp NULL DEFAULT NULL COMMENT 'When did paypal say, that this pling was payed successfull', 1038 `amount_reports` bigint(21) DEFAULT '0', 1039 `package_types` text CHARACTER SET utf8, 1040 `package_names` text CHARACTER SET latin1, 1041 `tags` text COLLATE latin1_general_ci, 1042 PRIMARY KEY (`project_id`), 1043 KEY `idx_cat` (`project_category_id`) 1044 ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 1045 /*!40101 SET character_set_client = @saved_cs_client */; 1046 SET @saved_cs_client = @@character_set_client; 1047 SET character_set_client = utf8; 1048 /*!50001 CREATE TABLE `stat_projects_v` ( 1049 `project_id` tinyint NOT NULL, 1050 `member_id` tinyint NOT NULL, 1051 `content_type` tinyint NOT NULL, 1052 `project_category_id` tinyint NOT NULL, 1053 `hive_category_id` tinyint NOT NULL, 1054 `status` tinyint NOT NULL, 1055 `uuid` tinyint NOT NULL, 1056 `pid` tinyint NOT NULL, 1057 `type_id` tinyint NOT NULL, 1058 `title` tinyint NOT NULL, 1059 `description` tinyint NOT NULL, 1060 `version` tinyint NOT NULL, 1061 `image_big` tinyint NOT NULL, 1062 `image_small` tinyint NOT NULL, 1063 `start_date` tinyint NOT NULL, 1064 `content_url` tinyint NOT NULL, 1065 `created_at` tinyint NOT NULL, 1066 `changed_at` tinyint NOT NULL, 1067 `deleted_at` tinyint NOT NULL, 1068 `creator_id` tinyint NOT NULL, 1069 `facebook_code` tinyint NOT NULL, 1070 `github_code` tinyint NOT NULL, 1071 `twitter_code` tinyint NOT NULL, 1072 `google_code` tinyint NOT NULL, 1073 `link_1` tinyint NOT NULL, 1074 `embed_code` tinyint NOT NULL, 1075 `ppload_collection_id` tinyint NOT NULL, 1076 `validated` tinyint NOT NULL, 1077 `validated_at` tinyint NOT NULL, 1078 `featured` tinyint NOT NULL, 1079 `approved` tinyint NOT NULL, 1080 `amount` tinyint NOT NULL, 1081 `amount_period` tinyint NOT NULL, 1082 `claimable` tinyint NOT NULL, 1083 `claimed_by_member` tinyint NOT NULL, 1084 `count_likes` tinyint NOT NULL, 1085 `count_dislikes` tinyint NOT NULL, 1086 `count_comments` tinyint NOT NULL, 1087 `count_downloads_hive` tinyint NOT NULL, 1088 `source_id` tinyint NOT NULL, 1089 `source_pk` tinyint NOT NULL, 1090 `source_type` tinyint NOT NULL, 1091 `project_validated` tinyint NOT NULL, 1092 `project_uuid` tinyint NOT NULL, 1093 `project_status` tinyint NOT NULL, 1094 `project_created_at` tinyint NOT NULL, 1095 `member_type` tinyint NOT NULL, 1096 `project_member_id` tinyint NOT NULL, 1097 `project_changed_at` tinyint NOT NULL, 1098 `laplace_score` tinyint NOT NULL, 1099 `username` tinyint NOT NULL, 1100 `profile_image_url` tinyint NOT NULL, 1101 `city` tinyint NOT NULL, 1102 `country` tinyint NOT NULL, 1103 `member_created_at` tinyint NOT NULL, 1104 `paypal_mail` tinyint NOT NULL, 1105 `cat_title` tinyint NOT NULL, 1106 `cat_xdg_type` tinyint NOT NULL, 1107 `cat_name_legacy` tinyint NOT NULL, 1108 `cat_show_description` tinyint NOT NULL, 1109 `amount_received` tinyint NOT NULL, 1110 `count_plings` tinyint NOT NULL, 1111 `count_plingers` tinyint NOT NULL, 1112 `latest_pling` tinyint NOT NULL, 1113 `amount_reports` tinyint NOT NULL 1114 ) ENGINE=MyISAM */; 1115 SET character_set_client = @saved_cs_client; 1116 /*!40101 SET @saved_cs_client = @@character_set_client */; 1117 /*!40101 SET character_set_client = utf8 */; 1118 CREATE TABLE `stat_ranking_history` ( 1119 `ranking_history_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', 1120 `project_id` int(11) NOT NULL COMMENT 'ID of the project', 1121 `type_id` int(11) DEFAULT NULL, 1122 `project_category_id` int(11) DEFAULT '0' COMMENT 'Kategorie', 1123 `count_plings` int(11) DEFAULT '0', 1124 `count_views` int(11) DEFAULT '0', 1125 `count_comments` int(11) DEFAULT NULL, 1126 `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP, 1127 `year` int(11) DEFAULT NULL COMMENT 'z.B.: 1988', 1128 `month` int(11) DEFAULT NULL COMMENT 'z.b: 1-12', 1129 `day` int(11) DEFAULT NULL COMMENT 'z.B. 1-31', 1130 `year_week` int(11) DEFAULT NULL COMMENT 'z.b.: 201232', 1131 `ranking` int(11) DEFAULT NULL, 1132 PRIMARY KEY (`ranking_history_id`) 1133 ) ENGINE=InnoDB AUTO_INCREMENT=739 DEFAULT CHARSET=utf8 COMMENT='Statistic of the ranking-values'; 1134 /*!40101 SET character_set_client = @saved_cs_client */; 1135 SET @saved_cs_client = @@character_set_client; 1136 SET character_set_client = utf8; 1137 /*!50001 CREATE TABLE `stat_ratings` ( 1138 `project_id` tinyint NOT NULL, 1139 `count_likes` tinyint NOT NULL, 1140 `count_dislikes` tinyint NOT NULL, 1141 `laplace_score` tinyint NOT NULL 1142 ) ENGINE=MyISAM */; 1143 SET character_set_client = @saved_cs_client; 1144 SET @saved_cs_client = @@character_set_client; 1145 SET character_set_client = utf8; 1146 /*!50001 CREATE TABLE `stat_views` ( 1147 `project_id` tinyint NOT NULL, 1148 `count_views` tinyint NOT NULL, 1149 `count_visitor` tinyint NOT NULL, 1150 `last_view` tinyint NOT NULL 1151 ) ENGINE=MyISAM */; 1152 SET character_set_client = @saved_cs_client; 1153 /*!40101 SET @saved_cs_client = @@character_set_client */; 1154 /*!40101 SET character_set_client = utf8 */; 1155 CREATE TABLE `support` ( 1156 `id` int(11) NOT NULL AUTO_INCREMENT, 1157 `member_id` int(11) NOT NULL COMMENT 'Supporter', 1158 `status_id` int(11) DEFAULT '0' COMMENT 'Stati der donation: 0 = inactive, 1 = active (donated), 2 = payed successfull, 99 = deleted', 1159 `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation-time', 1160 `donation_time` timestamp NULL DEFAULT NULL COMMENT 'When was a project plinged?', 1161 `active_time` timestamp NULL DEFAULT NULL COMMENT 'When did paypal say, that this donation was payed successfull', 1162 `delete_time` timestamp NULL DEFAULT NULL, 1163 `amount` double(10,2) DEFAULT '0.00' COMMENT 'Amount of money', 1164 `comment` varchar(140) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'Comment from the supporter', 1165 `payment_provider` varchar(45) COLLATE latin1_general_ci DEFAULT NULL, 1166 `payment_reference_key` varchar(255) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'uniquely identifies the request', 1167 `payment_transaction_id` varchar(255) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'uniquely identify caller (developer, facilliator, marketplace) transaction', 1168 `payment_raw_message` varchar(2000) COLLATE latin1_general_ci DEFAULT NULL COMMENT 'the raw text message ', 1169 `payment_raw_error` varchar(2000) COLLATE latin1_general_ci DEFAULT NULL, 1170 `payment_status` varchar(45) COLLATE latin1_general_ci DEFAULT NULL, 1171 PRIMARY KEY (`id`), 1172 KEY `status_id` (`status_id`), 1173 KEY `member_id` (`member_id`), 1174 KEY `DONATION_IX_01` (`status_id`,`member_id`,`active_time`,`amount`) 1175 ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 1176 /*!40101 SET character_set_client = @saved_cs_client */; 1177 /*!40101 SET @saved_cs_client = @@character_set_client */; 1178 /*!40101 SET character_set_client = utf8 */; 1179 CREATE TABLE `tag` ( 1180 `tag_id` int(11) NOT NULL AUTO_INCREMENT, 1181 `tag_name` varchar(45) COLLATE latin1_general_ci NOT NULL, 1182 PRIMARY KEY (`tag_id`), 1183 UNIQUE KEY `idx_name` (`tag_name`) 1184 ) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 1185 /*!40101 SET character_set_client = @saved_cs_client */; 1186 /*!40101 SET @saved_cs_client = @@character_set_client */; 1187 /*!40101 SET character_set_client = utf8 */; 1188 CREATE TABLE `tag_group` ( 1189 `group_id` int(11) NOT NULL AUTO_INCREMENT, 1190 `group_name` varchar(45) COLLATE latin1_general_ci NOT NULL, 1191 PRIMARY KEY (`group_id`) 1192 ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 1193 /*!40101 SET character_set_client = @saved_cs_client */; 1194 /*!40101 SET @saved_cs_client = @@character_set_client */; 1195 /*!40101 SET character_set_client = utf8 */; 1196 CREATE TABLE `tag_group_item` ( 1197 `tag_group_item_id` int(11) NOT NULL AUTO_INCREMENT, 1198 `tag_group_id` int(11) NOT NULL, 1199 `tag_id` int(11) NOT NULL, 1200 PRIMARY KEY (`tag_group_item_id`), 1201 KEY `tag_group_idx` (`tag_group_id`), 1202 KEY `tag_idx` (`tag_id`), 1203 CONSTRAINT `tag` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`tag_id`) ON DELETE CASCADE ON UPDATE NO ACTION, 1204 CONSTRAINT `tag_group` FOREIGN KEY (`tag_group_id`) REFERENCES `tag_group` (`group_id`) ON DELETE CASCADE ON UPDATE NO ACTION 1205 ) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 1206 /*!40101 SET character_set_client = @saved_cs_client */; 1207 /*!40101 SET @saved_cs_client = @@character_set_client */; 1208 /*!40101 SET character_set_client = utf8 */; 1209 CREATE TABLE `tag_object` ( 1210 `tag_item_id` int(11) NOT NULL AUTO_INCREMENT, 1211 `tag_id` int(11) NOT NULL, 1212 `tag_type_id` int(11) NOT NULL, 1213 `tag_object_id` int(11) NOT NULL, 1214 `tag_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP, 1215 `tag_changed` datetime DEFAULT NULL, 1216 PRIMARY KEY (`tag_item_id`), 1217 UNIQUE KEY `tags_unique` (`tag_id`,`tag_type_id`,`tag_object_id`), 1218 KEY `tags_idx` (`tag_id`), 1219 KEY `types_idx` (`tag_type_id`) 1220 ) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 1221 /*!40101 SET character_set_client = @saved_cs_client */; 1222 /*!50003 SET @saved_cs_client = @@character_set_client */ ; 1223 /*!50003 SET @saved_cs_results = @@character_set_results */ ; 1224 /*!50003 SET @saved_col_connection = @@collation_connection */ ; 1225 /*!50003 SET character_set_client = utf8 */ ; 1226 /*!50003 SET character_set_results = utf8 */ ; 1227 /*!50003 SET collation_connection = utf8_general_ci */ ; 1228 /*!50003 SET @saved_sql_mode = @@sql_mode */ ; 1229 /*!50003 SET sql_mode = 'STRICT_ALL_TABLES' */ ; 1230 DELIMITER ;; 1231 /*!50003 CREATE*/ /*!50017 DEFINER=CURRENT_USER*/ /*!50003 TRIGGER `ocs-apiserver`.`tag_object_BEFORE_INSERT` BEFORE INSERT ON `tag_object` FOR EACH ROW 1232 BEGIN 1233 IF NEW.tag_changed IS NULL THEN 1234 SET NEW.tag_changed = NOW(); 1235 END IF; 1236 END */;; 1237 DELIMITER ; 1238 /*!50003 SET sql_mode = @saved_sql_mode */ ; 1239 /*!50003 SET character_set_client = @saved_cs_client */ ; 1240 /*!50003 SET character_set_results = @saved_cs_results */ ; 1241 /*!50003 SET collation_connection = @saved_col_connection */ ; 1242 /*!40101 SET @saved_cs_client = @@character_set_client */; 1243 /*!40101 SET character_set_client = utf8 */; 1244 CREATE TABLE `tag_type` ( 1245 `tag_type_id` int(11) NOT NULL AUTO_INCREMENT, 1246 `tag_type_name` varchar(45) COLLATE latin1_general_ci NOT NULL, 1247 PRIMARY KEY (`tag_type_id`) 1248 ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; 1249 /*!40101 SET character_set_client = @saved_cs_client */; 1250 SET @saved_cs_client = @@character_set_client; 1251 SET character_set_client = utf8; 1252 /*!50001 CREATE TABLE `view_reported_projects` ( 1253 `project_id` tinyint NOT NULL, 1254 `amount_reports` tinyint NOT NULL, 1255 `latest_report` tinyint NOT NULL 1256 ) ENGINE=MyISAM */; 1257 SET character_set_client = @saved_cs_client; 1258 /*!50106 SET @save_time_zone= @@TIME_ZONE */ ; 1259 DELIMITER ;; 1260 /*!50003 SET @saved_cs_client = @@character_set_client */ ;; 1261 /*!50003 SET @saved_cs_results = @@character_set_results */ ;; 1262 /*!50003 SET @saved_col_connection = @@collation_connection */ ;; 1263 /*!50003 SET character_set_client = utf8 */ ;; 1264 /*!50003 SET character_set_results = utf8 */ ;; 1265 /*!50003 SET collation_connection = utf8_general_ci */ ;; 1266 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;; 1267 /*!50003 SET sql_mode = 'STRICT_ALL_TABLES' */ ;; 1268 /*!50003 SET @saved_time_zone = @@time_zone */ ;; 1269 /*!50003 SET time_zone = 'SYSTEM' */ ;; 1270 /*!50106 CREATE*/ /*!50117 DEFINER=CURRENT_USER*/ /*!50106 EVENT `e_generate_page_views_today` ON SCHEDULE EVERY 30 MINUTE STARTS '2017-06-30 05:00:00' ON COMPLETION PRESERVE ENABLE COMMENT 'Regenerates page views counter for projects on every hour' DO CALL pling.generate_stat_views_today() */ ;; 1271 /*!50003 SET time_zone = @saved_time_zone */ ;; 1272 /*!50003 SET sql_mode = @saved_sql_mode */ ;; 1273 /*!50003 SET character_set_client = @saved_cs_client */ ;; 1274 /*!50003 SET character_set_results = @saved_cs_results */ ;; 1275 /*!50003 SET collation_connection = @saved_col_connection */ ;; 1276 DELIMITER ;; 1277 /*!50003 SET @saved_cs_client = @@character_set_client */ ;; 1278 /*!50003 SET @saved_cs_results = @@character_set_results */ ;; 1279 /*!50003 SET @saved_col_connection = @@collation_connection */ ;; 1280 /*!50003 SET character_set_client = utf8 */ ;; 1281 /*!50003 SET character_set_results = utf8 */ ;; 1282 /*!50003 SET collation_connection = utf8_general_ci */ ;; 1283 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;; 1284 /*!50003 SET sql_mode = 'STRICT_ALL_TABLES' */ ;; 1285 /*!50003 SET @saved_time_zone = @@time_zone */ ;; 1286 /*!50003 SET time_zone = 'SYSTEM' */ ;; 1287 /*!50106 CREATE*/ /*!50117 DEFINER=CURRENT_USER*/ /*!50106 EVENT `e_generate_stat_cat_prod_count` ON SCHEDULE EVERY 2 MINUTE STARTS '2017-08-11 05:00:00' ON COMPLETION PRESERVE ENABLE COMMENT 'Regenerates generate_stat_cat_prod_count table' DO CALL pling.generate_stat_cat_prod_count() */ ;; 1288 /*!50003 SET time_zone = @saved_time_zone */ ;; 1289 /*!50003 SET sql_mode = @saved_sql_mode */ ;; 1290 /*!50003 SET character_set_client = @saved_cs_client */ ;; 1291 /*!50003 SET character_set_results = @saved_cs_results */ ;; 1292 /*!50003 SET collation_connection = @saved_col_connection */ ;; 1293 DELIMITER ;; 1294 /*!50003 SET @saved_cs_client = @@character_set_client */ ;; 1295 /*!50003 SET @saved_cs_results = @@character_set_results */ ;; 1296 /*!50003 SET @saved_col_connection = @@collation_connection */ ;; 1297 /*!50003 SET character_set_client = utf8 */ ;; 1298 /*!50003 SET character_set_results = utf8 */ ;; 1299 /*!50003 SET collation_connection = utf8_general_ci */ ;; 1300 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;; 1301 /*!50003 SET sql_mode = 'STRICT_ALL_TABLES' */ ;; 1302 /*!50003 SET @saved_time_zone = @@time_zone */ ;; 1303 /*!50003 SET time_zone = 'SYSTEM' */ ;; 1304 /*!50106 CREATE*/ /*!50117 DEFINER=CURRENT_USER*/ /*!50106 EVENT `e_generate_stat_cat_tree` ON SCHEDULE EVERY 60 MINUTE STARTS '2017-08-17 05:00:00' ON COMPLETION PRESERVE ENABLE COMMENT 'Regenerates generate_stat_cat_tree table' DO CALL pling.generate_stat_cat_tree() */ ;; 1305 /*!50003 SET time_zone = @saved_time_zone */ ;; 1306 /*!50003 SET sql_mode = @saved_sql_mode */ ;; 1307 /*!50003 SET character_set_client = @saved_cs_client */ ;; 1308 /*!50003 SET character_set_results = @saved_cs_results */ ;; 1309 /*!50003 SET collation_connection = @saved_col_connection */ ;; 1310 DELIMITER ;; 1311 /*!50003 SET @saved_cs_client = @@character_set_client */ ;; 1312 /*!50003 SET @saved_cs_results = @@character_set_results */ ;; 1313 /*!50003 SET @saved_col_connection = @@collation_connection */ ;; 1314 /*!50003 SET character_set_client = utf8 */ ;; 1315 /*!50003 SET character_set_results = utf8 */ ;; 1316 /*!50003 SET collation_connection = utf8_general_ci */ ;; 1317 /*!50003 SET @saved_sql_mode = @@sql_mode */ ;; 1318 /*!50003 SET sql_mode = 'STRICT_ALL_TABLES' */ ;; 1319 /*!50003 SET @saved_time_zone = @@time_zone */ ;; 1320 /*!50003 SET time_zone = 'SYSTEM' */ ;; 1321 /*!50106 CREATE*/ /*!50117 DEFINER=CURRENT_USER*/ /*!50106 EVENT `e_generate_stat_projects` ON SCHEDULE EVERY 5 MINUTE STARTS '2017-08-08 05:00:00' ON COMPLETION PRESERVE ENABLE COMMENT 'Regenerates stat_projects table' DO CALL pling.generate_stat_project() */ ;; 1322 /*!50003 SET time_zone = @saved_time_zone */ ;; 1323 /*!50003 SET sql_mode = @saved_sql_mode */ ;; 1324 /*!50003 SET character_set_client = @saved_cs_client */ ;; 1325 /*!50003 SET character_set_results = @saved_cs_results */ ;; 1326 /*!50003 SET collation_connection = @saved_col_connection */ ;; 1327 DELIMITER ; 1328 /*!50106 SET TIME_ZONE= @save_time_zone */ ; 1329 /*!50003 SET @saved_cs_client = @@character_set_client */ ; 1330 /*!50003 SET @saved_cs_results = @@character_set_results */ ; 1331 /*!50003 SET @saved_col_connection = @@collation_connection */ ; 1332 /*!50003 SET character_set_client = utf8 */ ; 1333 /*!50003 SET character_set_results = utf8 */ ; 1334 /*!50003 SET collation_connection = utf8_general_ci */ ; 1335 /*!50003 SET @saved_sql_mode = @@sql_mode */ ; 1336 /*!50003 SET sql_mode = 'STRICT_ALL_TABLES' */ ; 1337 DELIMITER ;; 1338 CREATE DEFINER=CURRENT_USER FUNCTION `get_stores`(category_id INT) RETURNS text CHARSET utf8 1339 READS SQL DATA 1340 BEGIN 1341 1342 DECLARE _stores text DEFAULT ''; 1343 1344 SELECT 1345 GROUP_CONCAT(csc.store_id) AS store_id_list 1346 INTO _stores 1347 FROM 1348 config_store_category AS csc 1349 WHERE 1350 csc.project_category_id IN (SELECT 1351 pc2.project_category_id AS ancestor_id 1352 FROM 1353 project_category AS pc, 1354 project_category AS pc2 1355 WHERE 1356 (pc.lft BETWEEN pc2.lft AND pc2.rgt) 1357 AND pc.project_category_id = category_id 1358 GROUP BY pc2.lft 1359 ORDER BY pc2.lft) 1360 ; 1361 1362 RETURN _stores; 1363 END ;; 1364 DELIMITER ; 1365 /*!50003 SET sql_mode = @saved_sql_mode */ ; 1366 /*!50003 SET character_set_client = @saved_cs_client */ ; 1367 /*!50003 SET character_set_results = @saved_cs_results */ ; 1368 /*!50003 SET collation_connection = @saved_col_connection */ ; 1369 /*!50003 SET @saved_cs_client = @@character_set_client */ ; 1370 /*!50003 SET @saved_cs_results = @@character_set_results */ ; 1371 /*!50003 SET @saved_col_connection = @@collation_connection */ ; 1372 /*!50003 SET character_set_client = utf8 */ ; 1373 /*!50003 SET character_set_results = utf8 */ ; 1374 /*!50003 SET collation_connection = utf8_general_ci */ ; 1375 /*!50003 SET @saved_sql_mode = @@sql_mode */ ; 1376 /*!50003 SET sql_mode = 'STRICT_ALL_TABLES' */ ; 1377 DELIMITER ;; 1378 CREATE DEFINER=CURRENT_USER FUNCTION `laplace_score`(upvotes INT, downvotes INT) RETURNS int(11) 1379 BEGIN 1380 DECLARE score INT(10); 1381 SET score = (round(((upvotes + 6) / ((upvotes + downvotes) + 12)),2) * 100); 1382 RETURN score; 1383 END ;; 1384 DELIMITER ; 1385 /*!50003 SET sql_mode = @saved_sql_mode */ ; 1386 /*!50003 SET character_set_client = @saved_cs_client */ ; 1387 /*!50003 SET character_set_results = @saved_cs_results */ ; 1388 /*!50003 SET collation_connection = @saved_col_connection */ ; 1389 /*!50003 SET @saved_cs_client = @@character_set_client */ ; 1390 /*!50003 SET @saved_cs_results = @@character_set_results */ ; 1391 /*!50003 SET @saved_col_connection = @@collation_connection */ ; 1392 /*!50003 SET character_set_client = utf8mb4 */ ; 1393 /*!50003 SET character_set_results = utf8mb4 */ ; 1394 /*!50003 SET collation_connection = utf8mb4_general_ci */ ; 1395 /*!50003 SET @saved_sql_mode = @@sql_mode */ ; 1396 /*!50003 SET sql_mode = '' */ ; 1397 DELIMITER ;; 1398 CREATE DEFINER=CURRENT_USER PROCEDURE `create_stat_ranking_categroy`(IN `project_category_id` INT) 1399 BEGIN 1400 1401 1402 IF(project_category_id = 0 || project_category_id IS NULL) THEN 1403 1404 #ALL 1405 DELETE FROM pling.stat_ranking_category WHERE project_category_id = 0; 1406 1407 SET @i=0; 1408 insert into stat_ranking_category ( 1409 SELECT null,0,project_id, title, (round(((p.count_likes + 6) / ((p.count_likes + p.count_dislikes) + 12)),2) * 100) as score, @i:=@i+1 AS rank 1410 FROM pling.project p 1411 WHERE p.status = 100 1412 ORDER BY (round(((p.count_likes + 6) / ((p.count_likes + p.count_dislikes) + 12)),2) * 100) DESC 1413 ); 1414 ELSE 1415 #CATEGORY 1416 DELETE FROM pling.stat_ranking_category WHERE project_category_id = project_category_id; 1417 1418 SET @i=0; 1419 insert into stat_ranking_category ( 1420 SELECT null,project_category_id,project_id, title, (round(((p.count_likes + 6) / ((p.count_likes + p.count_dislikes) + 12)),2) * 100) as score, @i:=@i+1 AS rank 1421 FROM pling.project p 1422 WHERE p.status = 100 1423 AND p.project_category_id = project_category_id 1424 ORDER BY (round(((p.count_likes + 6) / ((p.count_likes + p.count_dislikes) + 12)),2) * 100) DESC 1425 ); 1426 1427 END IF; 1428 1429 END ;; 1430 DELIMITER ; 1431 /*!50003 SET sql_mode = @saved_sql_mode */ ; 1432 /*!50003 SET character_set_client = @saved_cs_client */ ; 1433 /*!50003 SET character_set_results = @saved_cs_results */ ; 1434 /*!50003 SET collation_connection = @saved_col_connection */ ; 1435 /*!50003 SET @saved_cs_client = @@character_set_client */ ; 1436 /*!50003 SET @saved_cs_results = @@character_set_results */ ; 1437 /*!50003 SET @saved_col_connection = @@collation_connection */ ; 1438 /*!50003 SET character_set_client = utf8 */ ; 1439 /*!50003 SET character_set_results = utf8 */ ; 1440 /*!50003 SET collation_connection = utf8_general_ci */ ; 1441 /*!50003 SET @saved_sql_mode = @@sql_mode */ ; 1442 /*!50003 SET sql_mode = 'STRICT_ALL_TABLES' */ ; 1443 DELIMITER ;; 1444 CREATE DEFINER=CURRENT_USER PROCEDURE `generate_stat_cat_prod_count`() 1445 BEGIN 1446 1447 DROP TABLE IF EXISTS tmp_stat_cat_prod_count; 1448 CREATE TABLE tmp_stat_cat_prod_count 1449 ( 1450 `project_category_id` int(11) NOT NULL, 1451 `package_type_id` int(11) NULL, 1452 `count_product` int(11) NULL, 1453 INDEX `idx_package` (`project_category_id`,`package_type_id`) 1454 ) 1455 ENGINE Memory 1456 AS 1457 SELECT 1458 sct2.project_category_id, 1459 NULL as package_type_id, 1460 count(distinct p.project_id) as count_product 1461 FROM stat_cat_tree as sct1 1462 JOIN stat_cat_tree as sct2 ON sct1.lft between sct2.lft AND sct2.rgt 1463 LEFT JOIN stat_projects as p ON p.project_category_id = sct1.project_category_id 1464 GROUP BY sct2.project_category_id 1465 1466 UNION 1467 1468 SELECT 1469 sct2.project_category_id, 1470 ppt.package_type_id, 1471 count(distinct p.project_id) as count_product 1472 FROM stat_cat_tree as sct1 1473 JOIN stat_cat_tree as sct2 ON sct1.lft between sct2.lft AND sct2.rgt 1474 JOIN stat_projects as p ON p.project_category_id = sct1.project_category_id 1475 JOIN project_package_type AS ppt ON ppt.project_id = p.project_id 1476 GROUP BY sct2.lft, ppt.package_type_id 1477 ; 1478 1479 IF EXISTS(SELECT table_name 1480 FROM INFORMATION_SCHEMA.TABLES 1481 WHERE table_schema = DATABASE() 1482 AND table_name = 'stat_cat_prod_count') 1483 1484 THEN 1485 1486 RENAME TABLE stat_cat_prod_count TO old_stat_cat_prod_count, tmp_stat_cat_prod_count TO stat_cat_prod_count; 1487 1488 ELSE 1489 1490 RENAME TABLE tmp_stat_cat_prod_count TO stat_cat_prod_count; 1491 1492 END IF; 1493 1494 1495 DROP TABLE IF EXISTS old_stat_cat_prod_count; 1496 1497 END ;; 1498 DELIMITER ; 1499 /*!50003 SET sql_mode = @saved_sql_mode */ ; 1500 /*!50003 SET character_set_client = @saved_cs_client */ ; 1501 /*!50003 SET character_set_results = @saved_cs_results */ ; 1502 /*!50003 SET collation_connection = @saved_col_connection */ ; 1503 /*!50003 SET @saved_cs_client = @@character_set_client */ ; 1504 /*!50003 SET @saved_cs_results = @@character_set_results */ ; 1505 /*!50003 SET @saved_col_connection = @@collation_connection */ ; 1506 /*!50003 SET character_set_client = utf8 */ ; 1507 /*!50003 SET character_set_results = utf8 */ ; 1508 /*!50003 SET collation_connection = utf8_general_ci */ ; 1509 /*!50003 SET @saved_sql_mode = @@sql_mode */ ; 1510 /*!50003 SET sql_mode = 'STRICT_ALL_TABLES' */ ; 1511 DELIMITER ;; 1512 CREATE DEFINER=CURRENT_USER PROCEDURE `generate_stat_cat_tree`() 1513 BEGIN 1514 1515 DROP TABLE IF EXISTS tmp_stat_cat_tree; 1516 CREATE TABLE tmp_stat_cat_tree 1517 ( 1518 `project_category_id` int(11) NOT NULL, 1519 `lft` int(11) NOT NULL, 1520 `rgt` int(11) NOT NULL, 1521 `title` varchar(100) NOT NULL, 1522 `name_legacy` varchar(50) NULL, 1523 `is_active` int(1), 1524 `orderPos` int(11) NULL, 1525 `depth` int(11) NOT NULL, 1526 `ancestor_id_path` varchar(100), 1527 `ancestor_path` varchar(256), 1528 `ancestor_path_legacy` varchar(256), 1529 PRIMARY KEY `primary` (project_category_id, lft, rgt) 1530 ) 1531 ENGINE Memory 1532 AS 1533 SELECT 1534 pc.project_category_id, 1535 pc.lft, 1536 pc.rgt, 1537 pc.title, 1538 pc.name_legacy, 1539 pc.is_active, 1540 pc.orderPos, 1541 count(pc.lft) - 1 AS depth, 1542 GROUP_CONCAT(pc2.project_category_id ORDER BY pc2.lft) AS ancestor_id_path, 1543 GROUP_CONCAT(pc2.title ORDER BY pc2.lft SEPARATOR ' | ') AS ancestor_path, 1544 GROUP_CONCAT(IF(LENGTH(TRIM(pc2.name_legacy))>0,pc2.name_legacy,pc2.title) ORDER BY pc2.lft SEPARATOR ' | ') AS ancestor_path_legacy 1545 FROM project_category AS pc, project_category AS pc2 1546 WHERE (pc.lft BETWEEN pc2.lft AND pc2.rgt) AND pc.is_active = 1 and pc2.is_active = 1 1547 GROUP BY pc.lft -- HAVING depth >= 1 1548 ORDER BY pc.lft, pc.orderPos 1549 ; 1550 1551 DROP TABLE IF EXISTS tmp_stat_cat; 1552 CREATE TABLE tmp_stat_cat 1553 ( 1554 `project_category_id` int(11) NOT NULL, 1555 `lft` int(11) NOT NULL, 1556 `rgt` int(11) NOT NULL, 1557 `title` varchar(100) NOT NULL, 1558 `name_legacy` varchar(50) NULL, 1559 `is_active` int(1), 1560 `orderPos` int(11) NULL, 1561 `depth` int(11) NOT NULL, 1562 `ancestor_id_path` varchar(100), 1563 `ancestor_path` varchar(256), 1564 `ancestor_path_legacy` varchar(256), 1565 `stores` varchar(256), 1566 PRIMARY KEY `primary` (project_category_id, lft, rgt) 1567 ) 1568 ENGINE Memory 1569 AS 1570 SELECT 1571 sct.*, 1572 GROUP_CONCAT(csc.store_id ORDER BY csc.store_id) AS stores 1573 FROM 1574 tmp_stat_cat_tree AS sct 1575 LEFT JOIN 1576 config_store_category AS csc ON FIND_IN_SET(csc.project_category_id,sct.ancestor_id_path) 1577 GROUP BY sct.project_category_id 1578 ORDER BY sct.lft 1579 ; 1580 1581 IF EXISTS(SELECT table_name 1582 FROM INFORMATION_SCHEMA.TABLES 1583 WHERE table_schema = DATABASE() 1584 AND table_name = 'stat_cat_tree') 1585 1586 THEN 1587 1588 RENAME TABLE stat_cat_tree TO old_stat_cat_tree, tmp_stat_cat TO stat_cat_tree; 1589 1590 ELSE 1591 1592 RENAME TABLE tmp_stat_cat TO stat_cat_tree; 1593 1594 END IF; 1595 1596 1597 DROP TABLE IF EXISTS old_stat_cat_tree; 1598 DROP TABLE IF EXISTS tmp_stat_cat_tree; 1599 1600 END ;; 1601 DELIMITER ; 1602 /*!50003 SET sql_mode = @saved_sql_mode */ ; 1603 /*!50003 SET character_set_client = @saved_cs_client */ ; 1604 /*!50003 SET character_set_results = @saved_cs_results */ ; 1605 /*!50003 SET collation_connection = @saved_col_connection */ ; 1606 /*!50003 SET @saved_cs_client = @@character_set_client */ ; 1607 /*!50003 SET @saved_cs_results = @@character_set_results */ ; 1608 /*!50003 SET @saved_col_connection = @@collation_connection */ ; 1609 /*!50003 SET character_set_client = utf8 */ ; 1610 /*!50003 SET character_set_results = utf8 */ ; 1611 /*!50003 SET collation_connection = utf8_general_ci */ ; 1612 /*!50003 SET @saved_sql_mode = @@sql_mode */ ; 1613 /*!50003 SET sql_mode = 'STRICT_ALL_TABLES' */ ; 1614 DELIMITER ;; 1615 CREATE DEFINER=CURRENT_USER PROCEDURE `generate_stat_project`() 1616 BEGIN 1617 DROP TABLE IF EXISTS tmp_reported_projects; 1618 CREATE TEMPORARY TABLE tmp_reported_projects 1619 (PRIMARY KEY `primary` (project_id) ) 1620 AS 1621 SELECT 1622 `reports_project`.`project_id` AS `project_id`, 1623 COUNT(`reports_project`.`project_id`) AS `amount_reports`, 1624 MAX(`reports_project`.`created_at`) AS `latest_report` 1625 FROM 1626 `reports_project` 1627 WHERE 1628 (`reports_project`.`is_deleted` = 0) 1629 GROUP BY `reports_project`.`project_id` 1630 ; 1631 1632 DROP TABLE IF EXISTS tmp_project_package_types; 1633 CREATE TEMPORARY TABLE tmp_project_package_types 1634 (PRIMARY KEY `primary` (project_id)) 1635 ENGINE MyISAM 1636 AS 1637 SELECT 1638 project_id, 1639 GROUP_CONCAT(DISTINCT project_package_type.package_type_id) AS package_type_id_list, 1640 GROUP_CONCAT(DISTINCT package_types.`name`) AS `package_name_list` 1641 FROM 1642 project_package_type 1643 JOIN 1644 package_types ON project_package_type.package_type_id = package_types.package_type_id 1645 WHERE 1646 package_types.is_active = 1 1647 GROUP BY project_id 1648 ; 1649 1650 DROP TABLE IF EXISTS tmp_project_tags; 1651 CREATE TEMPORARY TABLE tmp_project_tags 1652 (PRIMARY KEY `primary` (tag_project_id)) 1653 ENGINE MyISAM 1654 AS 1655 SELECT GROUP_CONCAT(tag.tag_name) AS tag_names, tgo.tag_object_id AS tag_project_id 1656 FROM tag_object AS tgo 1657 JOIN tag ON tag.tag_id = tgo.tag_id 1658 WHERE tag_type_id = 1 1659 GROUP BY tgo.tag_object_id 1660 ORDER BY tgo.tag_object_id; 1661 1662 -- DROP TABLE IF EXISTS tmp_project_tags; 1663 -- CREATE TEMPORARY TABLE tmp_project_tags 1664 -- (PRIMARY KEY `primary` (tag_project_id)) 1665 -- ENGINE MyISAM 1666 -- AS 1667 -- SELECT GROUP_CONCAT(concat(tg.group_name, '##', tag.tag_name)) AS tag_names, tgo.tag_object_id AS tag_project_id 1668 -- FROM tag_object AS tgo 1669 -- JOIN tag ON tag.tag_id = tgo.tag_id 1670 -- JOIN tag_group_item as tgi ON tgi.tag_id = tgo.tag_id 1671 -- JOIN tag_group as tg ON tg.group_id = tgi.tag_group_id 1672 -- WHERE tag_type_id = 1 1673 -- GROUP BY tgo.tag_object_id 1674 -- ORDER BY tgo.tag_object_id; 1675 1676 1677 DROP TABLE IF EXISTS tmp_stat_projects; 1678 CREATE TABLE tmp_stat_projects 1679 (PRIMARY KEY `primary` (`project_id`), INDEX `idx_cat` (`project_category_id`)) 1680 ENGINE MyISAM 1681 AS 1682 SELECT 1683 `project`.`project_id` AS `project_id`, 1684 `project`.`member_id` AS `member_id`, 1685 `project`.`content_type` AS `content_type`, 1686 `project`.`project_category_id` AS `project_category_id`, 1687 `project`.`hive_category_id` AS `hive_category_id`, 1688 `project`.`status` AS `status`, 1689 `project`.`uuid` AS `uuid`, 1690 `project`.`pid` AS `pid`, 1691 `project`.`type_id` AS `type_id`, 1692 `project`.`title` AS `title`, 1693 `project`.`description` AS `description`, 1694 `project`.`version` AS `version`, 1695 `project`.`image_big` AS `image_big`, 1696 `project`.`image_small` AS `image_small`, 1697 `project`.`start_date` AS `start_date`, 1698 `project`.`content_url` AS `content_url`, 1699 `project`.`created_at` AS `created_at`, 1700 `project`.`changed_at` AS `changed_at`, 1701 `project`.`deleted_at` AS `deleted_at`, 1702 `project`.`creator_id` AS `creator_id`, 1703 `project`.`facebook_code` AS `facebook_code`, 1704 `project`.`github_code` AS `github_code`, 1705 `project`.`twitter_code` AS `twitter_code`, 1706 `project`.`google_code` AS `google_code`, 1707 `project`.`link_1` AS `link_1`, 1708 `project`.`embed_code` AS `embed_code`, 1709 `project`.`ppload_collection_id` AS `ppload_collection_id`, 1710 `project`.`validated` AS `validated`, 1711 `project`.`validated_at` AS `validated_at`, 1712 `project`.`featured` AS `featured`, 1713 `project`.`approved` AS `approved`, 1714 `project`.`amount` AS `amount`, 1715 `project`.`amount_period` AS `amount_period`, 1716 `project`.`claimable` AS `claimable`, 1717 `project`.`claimed_by_member` AS `claimed_by_member`, 1718 `project`.`count_likes` AS `count_likes`, 1719 `project`.`count_dislikes` AS `count_dislikes`, 1720 `project`.`count_comments` AS `count_comments`, 1721 `project`.`count_downloads_hive` AS `count_downloads_hive`, 1722 `project`.`source_id` AS `source_id`, 1723 `project`.`source_pk` AS `source_pk`, 1724 `project`.`source_type` AS `source_type`, 1725 `project`.`validated` AS `project_validated`, 1726 `project`.`uuid` AS `project_uuid`, 1727 `project`.`status` AS `project_status`, 1728 `project`.`created_at` AS `project_created_at`, 1729 `project`.`changed_at` AS `project_changed_at`, 1730 laplace_score(`project`.`count_likes`, `project`.`count_dislikes`) AS `laplace_score`, 1731 `member`.`type` AS `member_type`, 1732 `member`.`member_id` AS `project_member_id`, 1733 `member`.`username` AS `username`, 1734 `member`.`profile_image_url` AS `profile_image_url`, 1735 `member`.`city` AS `city`, 1736 `member`.`country` AS `country`, 1737 `member`.`created_at` AS `member_created_at`, 1738 `member`.`paypal_mail` AS `paypal_mail`, 1739 `project_category`.`title` AS `cat_title`, 1740 `project_category`.`xdg_type` AS `cat_xdg_type`, 1741 `project_category`.`name_legacy` AS `cat_name_legacy`, 1742 `project_category`.`show_description` AS `cat_show_description`, 1743 `stat_plings`.`amount_received` AS `amount_received`, 1744 `stat_plings`.`count_plings` AS `count_plings`, 1745 `stat_plings`.`count_plingers` AS `count_plingers`, 1746 `stat_plings`.`latest_pling` AS `latest_pling`, 1747 `trp`.`amount_reports` AS `amount_reports`, 1748 `tppt`.`package_type_id_list` AS `package_types`, 1749 `tppt`.`package_name_list` AS `package_names`, 1750 `t`.`tag_names` AS `tags` 1751 FROM 1752 `project` 1753 JOIN `member` ON `member`.`member_id` = `project`.`member_id` 1754 JOIN `project_category` ON `project`.`project_category_id` = `project_category`.`project_category_id` 1755 LEFT JOIN `stat_plings` ON `stat_plings`.`project_id` = `project`.`project_id` 1756 LEFT JOIN `tmp_reported_projects` AS trp ON `trp`.`project_id` = `project`.`project_id` 1757 LEFT JOIN `tmp_project_package_types` AS tppt ON tppt.project_id = `project`.project_id 1758 LEFT JOIN `tmp_project_tags` AS t ON t.`tag_project_id` = project.`project_id` 1759 WHERE 1760 `member`.`is_deleted` = 0 1761 AND `member`.`is_active` = 1 1762 AND `project`.`type_id` = 1 1763 AND `project`.`status` = 100 1764 AND `project_category`.`is_active` = 1 1765 ; 1766 1767 RENAME TABLE stat_projects TO old_stat_projects, tmp_stat_projects TO stat_projects; 1768 1769 DROP TABLE IF EXISTS old_stat_projects; 1770 END ;; 1771 DELIMITER ; 1772 /*!50003 SET sql_mode = @saved_sql_mode */ ; 1773 /*!50003 SET character_set_client = @saved_cs_client */ ; 1774 /*!50003 SET character_set_results = @saved_cs_results */ ; 1775 /*!50003 SET collation_connection = @saved_col_connection */ ; 1776 /*!50003 SET @saved_cs_client = @@character_set_client */ ; 1777 /*!50003 SET @saved_cs_results = @@character_set_results */ ; 1778 /*!50003 SET @saved_col_connection = @@collation_connection */ ; 1779 /*!50003 SET character_set_client = utf8 */ ; 1780 /*!50003 SET character_set_results = utf8 */ ; 1781 /*!50003 SET collation_connection = utf8_general_ci */ ; 1782 /*!50003 SET @saved_sql_mode = @@sql_mode */ ; 1783 /*!50003 SET sql_mode = 'STRICT_ALL_TABLES' */ ; 1784 DELIMITER ;; 1785 CREATE DEFINER=CURRENT_USER PROCEDURE `generate_stat_views_today`() 1786 BEGIN 1787 1788 DROP TABLE IF EXISTS `temp_stat_views_today`; 1789 1790 CREATE TABLE `temp_stat_views_today` ( 1791 `id` INT NOT NULL AUTO_INCREMENT, 1792 `project_id` INT(11) NOT NULL, 1793 `count_views` INT(11) NULL DEFAULT 0, 1794 `count_visitor` INT(11) NULL DEFAULT 0, 1795 `last_view` DATETIME NULL DEFAULT NULL, 1796 PRIMARY KEY (`id`), 1797 INDEX `idx_project` (`project_id` ASC) 1798 ) 1799 ENGINE = MyISAM 1800 AS 1801 SELECT 1802 project_id, 1803 COUNT(*) AS count_views, 1804 COUNT(DISTINCT `stat_page_views`.`ip`) AS `count_visitor`, 1805 MAX(`stat_page_views`.`created_at`) AS `last_view` 1806 FROM stat_page_views 1807 WHERE (stat_page_views.`created_at` 1808 BETWEEN DATE_FORMAT(NOW(), '%Y-%m-%d 00:00') AND DATE_FORMAT(NOW(), '%Y-%m-%d 23:59') 1809 ) 1810 GROUP BY project_id; 1811 1812 IF EXISTS(SELECT table_name 1813 FROM INFORMATION_SCHEMA.TABLES 1814 WHERE table_schema = DATABASE() 1815 AND table_name = 'stat_page_views_today_mv' 1816 ) 1817 1818 THEN 1819 1820 ALTER TABLE `stat_page_views_today_mv` 1821 RENAME TO `old_stat_views_today_mv` ; 1822 1823 END IF; 1824 1825 ALTER TABLE `temp_stat_views_today` 1826 RENAME TO `stat_page_views_today_mv` ; 1827 1828 DROP TABLE IF EXISTS `old_stat_views_today_mv`; 1829 1830 END ;; 1831 DELIMITER ; 1832 /*!50003 SET sql_mode = @saved_sql_mode */ ; 1833 /*!50003 SET character_set_client = @saved_cs_client */ ; 1834 /*!50003 SET character_set_results = @saved_cs_results */ ; 1835 /*!50003 SET collation_connection = @saved_col_connection */ ; 1836 /*!50003 SET @saved_cs_client = @@character_set_client */ ; 1837 /*!50003 SET @saved_cs_results = @@character_set_results */ ; 1838 /*!50003 SET @saved_col_connection = @@collation_connection */ ; 1839 /*!50003 SET character_set_client = utf8 */ ; 1840 /*!50003 SET character_set_results = utf8 */ ; 1841 /*!50003 SET collation_connection = utf8_general_ci */ ; 1842 /*!50003 SET @saved_sql_mode = @@sql_mode */ ; 1843 /*!50003 SET sql_mode = 'STRICT_ALL_TABLES' */ ; 1844 DELIMITER ;; 1845 CREATE DEFINER=CURRENT_USER PROCEDURE `solr_query_deleted_pk`(IN lastIndexed VARCHAR(255)) 1846 BEGIN 1847 SELECT project_id 1848 FROM project 1849 JOIN member ON member.member_id = project.member_id 1850 JOIN project_category AS pc ON pc.project_category_id = project.project_category_id 1851 WHERE project.deleted_at > lastIndexed OR member.deleted_at > lastIndexed OR 1852 (project.changed_at > lastIndexed AND project.status < 100); 1853 END ;; 1854 DELIMITER ; 1855 /*!50003 SET sql_mode = @saved_sql_mode */ ; 1856 /*!50003 SET character_set_client = @saved_cs_client */ ; 1857 /*!50003 SET character_set_results = @saved_cs_results */ ; 1858 /*!50003 SET collation_connection = @saved_col_connection */ ; 1859 /*!50003 SET @saved_cs_client = @@character_set_client */ ; 1860 /*!50003 SET @saved_cs_results = @@character_set_results */ ; 1861 /*!50003 SET @saved_col_connection = @@collation_connection */ ; 1862 /*!50003 SET character_set_client = utf8 */ ; 1863 /*!50003 SET character_set_results = utf8 */ ; 1864 /*!50003 SET collation_connection = utf8_general_ci */ ; 1865 /*!50003 SET @saved_sql_mode = @@sql_mode */ ; 1866 /*!50003 SET sql_mode = 'STRICT_ALL_TABLES' */ ; 1867 DELIMITER ;; 1868 CREATE DEFINER=CURRENT_USER PROCEDURE `solr_query_delta`(IN lastIndexed varchar(255)) 1869 BEGIN 1870 SELECT DISTINCT project_id 1871 FROM project 1872 JOIN member ON member.member_id = project.member_id 1873 JOIN project_category AS pc ON pc.project_category_id = project.project_category_id 1874 LEFT JOIN tag_object AS tgo ON tgo.tag_object_id = project.project_id AND tgo.tag_type_id = 1 1875 WHERE (project.`status` = 100 AND project.`type_id` = 1 AND member.`is_active` = 1 AND pc.`is_active` = 1 AND project.changed_at > lastIndexed) 1876 OR (project.`status` = 100 AND project.`type_id` = 1 AND member.`is_active` = 1 AND pc.`is_active` = 1 AND (tgo.tag_created > lastIndexed OR tgo.tag_changed > lastIndexed)) 1877 ; 1878 END ;; 1879 DELIMITER ; 1880 /*!50003 SET sql_mode = @saved_sql_mode */ ; 1881 /*!50003 SET character_set_client = @saved_cs_client */ ; 1882 /*!50003 SET character_set_results = @saved_cs_results */ ; 1883 /*!50003 SET collation_connection = @saved_col_connection */ ; 1884 /*!50003 SET @saved_cs_client = @@character_set_client */ ; 1885 /*!50003 SET @saved_cs_results = @@character_set_results */ ; 1886 /*!50003 SET @saved_col_connection = @@collation_connection */ ; 1887 /*!50003 SET character_set_client = utf8 */ ; 1888 /*!50003 SET character_set_results = utf8 */ ; 1889 /*!50003 SET collation_connection = utf8_general_ci */ ; 1890 /*!50003 SET @saved_sql_mode = @@sql_mode */ ; 1891 /*!50003 SET sql_mode = 'STRICT_ALL_TABLES' */ ; 1892 DELIMITER ;; 1893 CREATE DEFINER=CURRENT_USER PROCEDURE `solr_query_delta_import`(IN projectID INT(11)) 1894 BEGIN 1895 DROP TABLE IF EXISTS tmp_project_tags; 1896 CREATE TEMPORARY TABLE tmp_project_tags AS 1897 SELECT GROUP_CONCAT(tag.tag_name) AS tag_names, tgo.tag_object_id AS tag_project_id 1898 FROM tag_object AS tgo 1899 JOIN tag ON tag.tag_id = tgo.tag_id 1900 WHERE tag_type_id = 1 1901 GROUP BY tgo.tag_object_id 1902 ORDER BY tgo.tag_object_id; 1903 1904 DROP TABLE IF EXISTS tmp_cat_tree; 1905 CREATE TEMPORARY TABLE tmp_cat_tree AS 1906 SELECT 1907 pc.project_category_id, 1908 pc.title, 1909 pc.is_active, 1910 count(pc.lft) AS depth, 1911 GROUP_CONCAT(pc2.project_category_id ORDER BY pc2.lft) AS ancestor_id_path, 1912 GROUP_CONCAT(pc2.title ORDER BY pc2.lft SEPARATOR ' | ') AS ancestor_path 1913 FROM project_category AS pc, project_category AS pc2 1914 WHERE (pc.lft BETWEEN pc2.lft AND pc2.rgt) 1915 GROUP BY pc.lft 1916 ORDER BY pc.lft; 1917 1918 DROP TABLE IF EXISTS tmp_cat_store; 1919 CREATE TEMPORARY TABLE tmp_cat_store AS 1920 SELECT 1921 tct.project_category_id, 1922 tct.ancestor_id_path, 1923 tct.title, 1924 tct.is_active, 1925 group_concat(store_id) AS stores 1926 FROM tmp_cat_tree AS tct, config_store_category AS csc 1927 WHERE FIND_IN_SET(csc.project_category_id, tct.ancestor_id_path) > 0 1928 GROUP BY tct.project_category_id 1929 ORDER BY tct.project_category_id; 1930 1931 DROP TABLE IF EXISTS solr_project_package_types; 1932 CREATE TEMPORARY TABLE solr_project_package_types 1933 (PRIMARY KEY `primary` (package_project_id)) 1934 ENGINE MyISAM 1935 AS 1936 SELECT 1937 project_id as package_project_id, 1938 GROUP_CONCAT(DISTINCT project_package_type.package_type_id) AS package_type_id_list, 1939 GROUP_CONCAT(DISTINCT package_types.`name`) AS `package_name_list` 1940 FROM 1941 project_package_type 1942 JOIN 1943 package_types ON project_package_type.package_type_id = package_types.package_type_id 1944 WHERE 1945 package_types.is_active = 1 1946 GROUP BY project_id 1947 ; 1948 1949 SELECT 1950 project_id, 1951 project.member_id AS project_member_id, 1952 project.project_category_id AS project_category_id, 1953 project.title AS project_title, 1954 description, 1955 image_small, 1956 member.username, 1957 member.firstname, 1958 member.lastname, 1959 tcs.title AS cat_title, 1960 `project`.`count_likes` AS `count_likes`, 1961 `project`.`count_dislikes` AS `count_dislikes`, 1962 laplace_score(project.count_likes, project.count_dislikes) AS `laplace_score`, 1963 project.created_at, 1964 project.changed_at, 1965 tcs.stores, 1966 tcs.ancestor_id_path AS `cat_id_ancestor_path`, 1967 sppt.package_type_id_list AS `package_ids`, 1968 sppt.package_name_list AS `package_names`, 1969 t.tag_names AS `tags` 1970 FROM project 1971 JOIN member ON member.member_id = project.member_id 1972 JOIN tmp_cat_store AS tcs ON project.project_category_id = tcs.project_category_id 1973 LEFT JOIN solr_project_package_types AS sppt ON sppt.package_project_id = project.project_id 1974 LEFT JOIN tmp_project_tags AS t ON t.tag_project_id = project.project_id 1975 WHERE project_id = projectID; 1976 END ;; 1977 DELIMITER ; 1978 /*!50003 SET sql_mode = @saved_sql_mode */ ; 1979 /*!50003 SET character_set_client = @saved_cs_client */ ; 1980 /*!50003 SET character_set_results = @saved_cs_results */ ; 1981 /*!50003 SET collation_connection = @saved_col_connection */ ; 1982 /*!50003 SET @saved_cs_client = @@character_set_client */ ; 1983 /*!50003 SET @saved_cs_results = @@character_set_results */ ; 1984 /*!50003 SET @saved_col_connection = @@collation_connection */ ; 1985 /*!50003 SET character_set_client = utf8 */ ; 1986 /*!50003 SET character_set_results = utf8 */ ; 1987 /*!50003 SET collation_connection = utf8_general_ci */ ; 1988 /*!50003 SET @saved_sql_mode = @@sql_mode */ ; 1989 /*!50003 SET sql_mode = 'STRICT_ALL_TABLES' */ ; 1990 DELIMITER ;; 1991 CREATE DEFINER=CURRENT_USER PROCEDURE `solr_query_import`() 1992 BEGIN 1993 DROP TABLE IF EXISTS tmp_project_tags; 1994 CREATE TEMPORARY TABLE tmp_project_tags AS 1995 SELECT GROUP_CONCAT(tag.tag_name) AS tag_names, tgo.tag_object_id AS tag_project_id 1996 FROM tag_object AS tgo 1997 JOIN tag ON tag.tag_id = tgo.tag_id 1998 WHERE tag_type_id = 1 1999 GROUP BY tgo.tag_object_id 2000 ORDER BY tgo.tag_object_id; 2001 2002 DROP TABLE IF EXISTS tmp_cat_tree; 2003 CREATE TEMPORARY TABLE tmp_cat_tree AS 2004 SELECT 2005 pc.project_category_id, 2006 pc.title, 2007 pc.is_active, 2008 count(pc.lft) AS depth, 2009 GROUP_CONCAT(pc2.project_category_id ORDER BY pc2.lft) AS ancestor_id_path, 2010 GROUP_CONCAT(pc2.title ORDER BY pc2.lft SEPARATOR ' | ') AS ancestor_path 2011 FROM project_category AS pc, project_category AS pc2 2012 WHERE (pc.lft BETWEEN pc2.lft AND pc2.rgt) 2013 GROUP BY pc.lft 2014 ORDER BY pc.lft; 2015 2016 DROP TABLE IF EXISTS tmp_cat_store; 2017 CREATE TEMPORARY TABLE tmp_cat_store AS 2018 SELECT 2019 tct.project_category_id, 2020 tct.ancestor_id_path, 2021 tct.title, 2022 tct.is_active, 2023 group_concat(store_id) AS stores 2024 FROM tmp_cat_tree AS tct, config_store_category AS csc 2025 WHERE FIND_IN_SET(csc.project_category_id, tct.ancestor_id_path) > 0 2026 GROUP BY tct.project_category_id 2027 ORDER BY tct.project_category_id; 2028 2029 DROP TABLE IF EXISTS solr_project_package_types; 2030 CREATE TEMPORARY TABLE solr_project_package_types 2031 (PRIMARY KEY `primary` (package_project_id)) 2032 ENGINE MyISAM 2033 AS 2034 SELECT 2035 project_id as package_project_id, 2036 GROUP_CONCAT(DISTINCT project_package_type.package_type_id) AS package_type_id_list, 2037 GROUP_CONCAT(DISTINCT package_types.`name`) AS `package_name_list` 2038 FROM 2039 project_package_type 2040 JOIN 2041 package_types ON project_package_type.package_type_id = package_types.package_type_id 2042 WHERE 2043 package_types.is_active = 1 2044 GROUP BY project_id 2045 ; 2046 2047 SELECT 2048 project_id, 2049 project.member_id AS project_member_id, 2050 project.project_category_id AS project_category_id, 2051 project.title AS project_title, 2052 description, 2053 image_small, 2054 member.username, 2055 member.firstname, 2056 member.lastname, 2057 tcs.title AS cat_title, 2058 `project`.`count_likes` AS `count_likes`, 2059 `project`.`count_dislikes` AS `count_dislikes`, 2060 laplace_score(project.count_likes, project.count_dislikes) AS `laplace_score`, 2061 project.created_at, 2062 project.changed_at, 2063 tcs.stores, 2064 tcs.ancestor_id_path AS `cat_id_ancestor_path`, 2065 sppt.package_type_id_list AS `package_ids`, 2066 sppt.package_name_list AS `package_names`, 2067 t.tag_names AS `tags` 2068 FROM project 2069 JOIN member ON member.member_id = project.member_id 2070 JOIN tmp_cat_store AS tcs ON project.project_category_id = tcs.project_category_id 2071 LEFT JOIN solr_project_package_types AS sppt ON sppt.package_project_id = project.project_id 2072 LEFT JOIN tmp_project_tags AS t ON t.tag_project_id = project.project_id 2073 WHERE project.`status` = 100 AND project.`type_id` = 1 AND member.`is_active` = 1 AND tcs.`is_active` = 1; 2074 END ;; 2075 DELIMITER ; 2076 /*!50003 SET sql_mode = @saved_sql_mode */ ; 2077 /*!50003 SET character_set_client = @saved_cs_client */ ; 2078 /*!50003 SET character_set_results = @saved_cs_results */ ; 2079 /*!50003 SET collation_connection = @saved_col_connection */ ; 2080 2081 USE `ocs-apiserver`; 2082 /*!50001 DROP TABLE IF EXISTS `stat_now`*/; 2083 /*!50001 SET @saved_cs_client = @@character_set_client */; 2084 /*!50001 SET @saved_cs_results = @@character_set_results */; 2085 /*!50001 SET @saved_col_connection = @@collation_connection */; 2086 /*!50001 SET character_set_client = utf8 */; 2087 /*!50001 SET character_set_results = utf8 */; 2088 /*!50001 SET collation_connection = utf8_general_ci */; 2089 /*!50001 CREATE ALGORITHM=UNDEFINED */ 2090 /*!50013 DEFINER=CURRENT_USER SQL SECURITY DEFINER */ 2091 /*!50001 VIEW `stat_now` AS select `prj`.`project_id` AS `project_id`,`prj`.`type_id` AS `project_type_id`,`prj`.`project_category_id` AS `project_category_id`,(select count(1) from `stat_page_views` `pv` where ((`pv`.`project_id` = `prj`.`project_id`) and (`pv`.`created_at` between date_format(now(),'%Y-%m-%d %H:00') and date_format(now(),'%Y-%m-%d %H:59'))) group by `pv`.`project_id`) AS `count_views`,(select count(1) from `plings` `p` where ((`p`.`project_id` = `prj`.`project_id`) and (`p`.`pling_time` between date_format(now(),'%Y-%m-%d %H:00') and date_format(now(),'%Y-%m-%d %H:59')) and (`p`.`status_id` in (2,3,4))) group by `p`.`project_id`) AS `count_plings`,(select count(1) from `project` `pu` where ((`pu`.`project_id` = `prj`.`project_id`) and (`pu`.`created_at` between date_format(now(),'%Y-%m-%d %H:00') and date_format(now(),'%Y-%m-%d %H:59')) and (`pu`.`type_id` = 2)) group by `pu`.`project_id`) AS `count_updates`,(select count(1) from `comments` `c` where ((`c`.`comment_target_id` = `prj`.`project_id`) and (`c`.`comment_created_at` between date_format(now(),'%Y-%m-%d %H:00') and date_format(now(),'%Y-%m-%d %H:59'))) group by `c`.`comment_target_id`) AS `count_comments`,(select count(1) from `project_follower` `pf` where (`pf`.`project_id` = `prj`.`project_id`)) AS `count_followers`,(select count(distinct `p`.`member_id`) from `plings` `p` where ((`p`.`project_id` = 40) and (`p`.`status_id` in (2,3,4))) group by `p`.`project_id`) AS `count_supporters`,(select format(sum(`pt`.`amount`),2) from `plings` `pt` where ((`pt`.`project_id` = `prj`.`project_id`) and (`pt`.`active_time` between date_format(now(),'%Y-%m-%d %H:00') and date_format(now(),'%Y-%m-%d %H:59')) and (`pt`.`status_id` = 2)) group by `pt`.`project_id`) AS `count_money`,((((((ifnull((select count(1) from `stat_page_views` `pv` where ((`pv`.`project_id` = `prj`.`project_id`) and (`pv`.`created_at` between date_format(now(),'%Y-%m-%d %H:00') and date_format(now(),'%Y-%m-%d %H:59'))) group by `pv`.`project_id`),0) * 0.05) + (ifnull((select count(1) from `plings` `p` where ((`p`.`project_id` = `prj`.`project_id`) and (`p`.`pling_time` between date_format(now(),'%Y-%m-%d %H:00') and date_format(now(),'%Y-%m-%d %H:59')) and (`p`.`status_id` in (2,3,4))) group by `p`.`project_id`),0) * 0.5)) + (ifnull((select count(1) from `project` `pu` where ((`pu`.`project_id` = `prj`.`project_id`) and (`pu`.`created_at` between date_format(now(),'%Y-%m-%d %H:00') and date_format(now(),'%Y-%m-%d %H:59')) and (`pu`.`type_id` = 2)) group by `pu`.`project_id`),0) * 0.2)) + (ifnull((select count(1) from `project_follower` `pf` where (`pf`.`project_id` = `prj`.`project_id`)),0) * 0.1)) + (ifnull((select format(sum(`pt`.`amount`),2) from `plings` `pt` where ((`pt`.`project_id` = `prj`.`project_id`) and (`pt`.`active_time` between date_format(now(),'%Y-%m-%d %H:00') and date_format(now(),'%Y-%m-%d %H:59')) and (`pt`.`status_id` = 2)) group by `pt`.`project_id`),0) * 0.2)) / 1.05) AS `ranking_value`,now() AS `created_at`,date_format(now(),'%Y') AS `year`,date_format(now(),'%m') AS `month`,date_format(now(),'%d') AS `day`,yearweek(now(),1) AS `year_week` from `project` `prj` where ((`prj`.`is_deleted` = 0) and (`prj`.`is_active` = 1) and (`prj`.`type_id` = 1)) group by `prj`.`project_id` */; 2092 /*!50001 SET character_set_client = @saved_cs_client */; 2093 /*!50001 SET character_set_results = @saved_cs_results */; 2094 /*!50001 SET collation_connection = @saved_col_connection */; 2095 /*!50001 DROP TABLE IF EXISTS `stat_plings`*/; 2096 /*!50001 SET @saved_cs_client = @@character_set_client */; 2097 /*!50001 SET @saved_cs_results = @@character_set_results */; 2098 /*!50001 SET @saved_col_connection = @@collation_connection */; 2099 /*!50001 SET character_set_client = utf8 */; 2100 /*!50001 SET character_set_results = utf8 */; 2101 /*!50001 SET collation_connection = utf8_general_ci */; 2102 /*!50001 CREATE ALGORITHM=UNDEFINED */ 2103 /*!50013 DEFINER=CURRENT_USER SQL SECURITY DEFINER */ 2104 /*!50001 VIEW `stat_plings` AS select `plings`.`project_id` AS `project_id`,sum(`plings`.`amount`) AS `amount_received`,count(1) AS `count_plings`,count(distinct `plings`.`member_id`) AS `count_plingers`,max(`plings`.`active_time`) AS `latest_pling` from `plings` where (`plings`.`status_id` = 2) group by `plings`.`project_id` */; 2105 /*!50001 SET character_set_client = @saved_cs_client */; 2106 /*!50001 SET character_set_results = @saved_cs_results */; 2107 /*!50001 SET collation_connection = @saved_col_connection */; 2108 /*!50001 DROP TABLE IF EXISTS `stat_projects_v`*/; 2109 /*!50001 SET @saved_cs_client = @@character_set_client */; 2110 /*!50001 SET @saved_cs_results = @@character_set_results */; 2111 /*!50001 SET @saved_col_connection = @@collation_connection */; 2112 /*!50001 SET character_set_client = utf8 */; 2113 /*!50001 SET character_set_results = utf8 */; 2114 /*!50001 SET collation_connection = utf8_general_ci */; 2115 /*!50001 CREATE ALGORITHM=UNDEFINED */ 2116 /*!50013 DEFINER=CURRENT_USER SQL SECURITY DEFINER */ 2117 /*!50001 VIEW `stat_projects_v` AS select `project`.`project_id` AS `project_id`,`project`.`member_id` AS `member_id`,`project`.`content_type` AS `content_type`,`project`.`project_category_id` AS `project_category_id`,`project`.`hive_category_id` AS `hive_category_id`,`project`.`status` AS `status`,`project`.`uuid` AS `uuid`,`project`.`pid` AS `pid`,`project`.`type_id` AS `type_id`,`project`.`title` AS `title`,`project`.`description` AS `description`,`project`.`version` AS `version`,`project`.`image_big` AS `image_big`,`project`.`image_small` AS `image_small`,`project`.`start_date` AS `start_date`,`project`.`content_url` AS `content_url`,`project`.`created_at` AS `created_at`,`project`.`changed_at` AS `changed_at`,`project`.`deleted_at` AS `deleted_at`,`project`.`creator_id` AS `creator_id`,`project`.`facebook_code` AS `facebook_code`,`project`.`github_code` AS `github_code`,`project`.`twitter_code` AS `twitter_code`,`project`.`google_code` AS `google_code`,`project`.`link_1` AS `link_1`,`project`.`embed_code` AS `embed_code`,`project`.`ppload_collection_id` AS `ppload_collection_id`,`project`.`validated` AS `validated`,`project`.`validated_at` AS `validated_at`,`project`.`featured` AS `featured`,`project`.`approved` AS `approved`,`project`.`amount` AS `amount`,`project`.`amount_period` AS `amount_period`,`project`.`claimable` AS `claimable`,`project`.`claimed_by_member` AS `claimed_by_member`,`project`.`count_likes` AS `count_likes`,`project`.`count_dislikes` AS `count_dislikes`,`project`.`count_comments` AS `count_comments`,`project`.`count_downloads_hive` AS `count_downloads_hive`,`project`.`source_id` AS `source_id`,`project`.`source_pk` AS `source_pk`,`project`.`source_type` AS `source_type`,`project`.`validated` AS `project_validated`,`project`.`uuid` AS `project_uuid`,`project`.`status` AS `project_status`,`project`.`created_at` AS `project_created_at`,`member`.`type` AS `member_type`,`member`.`member_id` AS `project_member_id`,`project`.`changed_at` AS `project_changed_at`,`laplace_score`(`project`.`count_likes`,`project`.`count_dislikes`) AS `laplace_score`,`member`.`username` AS `username`,`member`.`profile_image_url` AS `profile_image_url`,`member`.`city` AS `city`,`member`.`country` AS `country`,`member`.`created_at` AS `member_created_at`,`member`.`paypal_mail` AS `paypal_mail`,`project_category`.`title` AS `cat_title`,`project_category`.`xdg_type` AS `cat_xdg_type`,`project_category`.`name_legacy` AS `cat_name_legacy`,`project_category`.`show_description` AS `cat_show_description`,`stat_plings`.`amount_received` AS `amount_received`,`stat_plings`.`count_plings` AS `count_plings`,`stat_plings`.`count_plingers` AS `count_plingers`,`stat_plings`.`latest_pling` AS `latest_pling`,`view_reported_projects`.`amount_reports` AS `amount_reports` from ((((`project` join `member` on((`member`.`member_id` = `project`.`member_id`))) join `project_category` on((`project`.`project_category_id` = `project_category`.`project_category_id`))) left join `stat_plings` on((`stat_plings`.`project_id` = `project`.`project_id`))) left join `view_reported_projects` on((`view_reported_projects`.`project_id` = `project`.`project_id`))) where ((`member`.`is_deleted` = 0) and (`member`.`is_active` = 1) and (`project`.`type_id` = 1) and (`project`.`status` = 100)) */; 2118 /*!50001 SET character_set_client = @saved_cs_client */; 2119 /*!50001 SET character_set_results = @saved_cs_results */; 2120 /*!50001 SET collation_connection = @saved_col_connection */; 2121 /*!50001 DROP TABLE IF EXISTS `stat_ratings`*/; 2122 /*!50001 SET @saved_cs_client = @@character_set_client */; 2123 /*!50001 SET @saved_cs_results = @@character_set_results */; 2124 /*!50001 SET @saved_col_connection = @@collation_connection */; 2125 /*!50001 SET character_set_client = utf8 */; 2126 /*!50001 SET character_set_results = utf8 */; 2127 /*!50001 SET collation_connection = utf8_general_ci */; 2128 /*!50001 CREATE ALGORITHM=UNDEFINED */ 2129 /*!50013 DEFINER=CURRENT_USER SQL SECURITY DEFINER */ 2130 /*!50001 VIEW `stat_ratings` AS select `r`.`project_id` AS `project_id`,sum(`r`.`user_like`) AS `count_likes`,sum(`r`.`user_dislike`) AS `count_dislikes`,(round(((sum(`r`.`user_like`) + 6) / ((sum(`r`.`user_like`) + sum(`r`.`user_dislike`)) + 12)),2) * 100) AS `laplace_score` from `project_rating` `r` group by `r`.`project_id` */; 2131 /*!50001 SET character_set_client = @saved_cs_client */; 2132 /*!50001 SET character_set_results = @saved_cs_results */; 2133 /*!50001 SET collation_connection = @saved_col_connection */; 2134 /*!50001 DROP TABLE IF EXISTS `stat_views`*/; 2135 /*!50001 SET @saved_cs_client = @@character_set_client */; 2136 /*!50001 SET @saved_cs_results = @@character_set_results */; 2137 /*!50001 SET @saved_col_connection = @@collation_connection */; 2138 /*!50001 SET character_set_client = utf8 */; 2139 /*!50001 SET character_set_results = utf8 */; 2140 /*!50001 SET collation_connection = utf8_general_ci */; 2141 /*!50001 CREATE ALGORITHM=TEMPTABLE */ 2142 /*!50013 DEFINER=CURRENT_USER SQL SECURITY DEFINER */ 2143 /*!50001 VIEW `stat_views` AS select `stat_page_views`.`project_id` AS `project_id`,count(1) AS `count_views`,count(distinct `stat_page_views`.`ip`) AS `count_visitor`,max(`stat_page_views`.`created_at`) AS `last_view` from `stat_page_views` group by `stat_page_views`.`project_id` */; 2144 /*!50001 SET character_set_client = @saved_cs_client */; 2145 /*!50001 SET character_set_results = @saved_cs_results */; 2146 /*!50001 SET collation_connection = @saved_col_connection */; 2147 /*!50001 DROP TABLE IF EXISTS `view_reported_projects`*/; 2148 /*!50001 SET @saved_cs_client = @@character_set_client */; 2149 /*!50001 SET @saved_cs_results = @@character_set_results */; 2150 /*!50001 SET @saved_col_connection = @@collation_connection */; 2151 /*!50001 SET character_set_client = utf8 */; 2152 /*!50001 SET character_set_results = utf8 */; 2153 /*!50001 SET collation_connection = utf8_general_ci */; 2154 /*!50001 CREATE ALGORITHM=UNDEFINED */ 2155 /*!50013 DEFINER=CURRENT_USER SQL SECURITY DEFINER */ 2156 /*!50001 VIEW `view_reported_projects` AS select `reports_project`.`project_id` AS `project_id`,count(`reports_project`.`project_id`) AS `amount_reports`,max(`reports_project`.`created_at`) AS `latest_report` from `reports_project` where (`reports_project`.`is_deleted` <> 1) group by `reports_project`.`project_id` */; 2157 /*!50001 SET character_set_client = @saved_cs_client */; 2158 /*!50001 SET character_set_results = @saved_cs_results */; 2159 /*!50001 SET collation_connection = @saved_col_connection */;