File indexing completed on 2024-07-14 16:03:29

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 */;