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

0001 CREATE DATABASE IF NOT EXISTS `ocs` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
0002 
0003 USE `ocs`;
0004 
0005 CREATE TABLE IF NOT EXISTS `activity_log` (
0006   `activity_log_id` int(11) NOT NULL AUTO_INCREMENT,
0007   `member_id` int(11) NOT NULL COMMENT 'Log action of this member',
0008   `project_id` int(11) DEFAULT NULL,
0009   `object_id` int(11) NOT NULL COMMENT 'Key to the action (add comment, pling, ...)',
0010   `object_ref` varchar(45) NOT NULL COMMENT 'Reference to the object table (plings, project, project_comment,...)',
0011   `object_title` varchar(90) DEFAULT NULL COMMENT 'Title to show',
0012   `object_text` varchar(150) DEFAULT NULL COMMENT 'Short text of this object (first 150 characters)',
0013   `object_img` varchar(255) DEFAULT NULL,
0014   `activity_type_id` int(11) NOT NULL DEFAULT '0' COMMENT 'Wich ENGINE of activity: create, update,delete.',
0015   `time` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
0016   PRIMARY KEY (`activity_log_id`),
0017   KEY `member_id` (`member_id`),
0018   KEY `project_id` (`project_id`),
0019   KEY `object_id` (`object_id`),
0020   KEY `activity_log_id` (`activity_log_id`,`member_id`,`project_id`,`object_id`),
0021   KEY `idx_time` (`member_id`,`time`)
0022 ) ENGINE=InnoDB COMMENT='Log all actions of a user. Wen can then generate a newsfeed ';
0023 
0024 CREATE TABLE IF NOT EXISTS `activity_log_types` (
0025   `activity_log_type_id` int(11) NOT NULL,
0026   `type_text` varchar(45) DEFAULT NULL,
0027   PRIMARY KEY (`activity_log_type_id`)
0028 ) ENGINE=InnoDB COMMENT='Type of activities';
0029 TRUNCATE `activity_log_types`;
0030 ALTER TABLE `activity_log_types` DISABLE KEYS ;
0031 INSERT INTO `activity_log_types` VALUES (0,'ProjectCreated'),(1,'ProjectUpdated'),(2,'ProjectDeleted'),(3,'ProjectStopped'),(4,'ProjectRestarted'),(7,'ProjectEdited'),(8,'ProjectPublished'),(9,'ProjectUnpublished'),(10,'ProjectItemCreated'),(11,'ProjectItemUpdated'),(12,'ProjectItemDeleted'),(13,'ProjectItemStopped'),(14,'ProjectItemRestarted'),(17,'ProjectItemEdited'),(18,'ProjectItemPublished'),(19,'ProjectItemUnpublished'),(20,'ProjectPlinged'),(21,'ProjectDisplinged'),(30,'ProjectItemPlinged'),(31,'ProjectItemDisplinged'),(40,'ProjectCommentCreated'),(41,'ProjectCommentUpdated'),(42,'ProjectCommentDeleted'),(43,'ProjectCommentReply'),(50,'ProjectFollowed'),(51,'ProjectUnfollowed'),(52,'ProjectShared'),(60,'ProjectRatedHigher'),(61,'ProjectRatedLower'),(100,'MemberJoined'),(101,'MemberUpdated'),(102,'MemberDeleted'),(107,'MemberEdited'),(150,'MemberFollowed'),(151,'MemberUnfollowed'),(152,'MemberShared'),(200,'ProjectFilesCreated'),(210,'ProjectFilesUpdated'),(220,'ProjectFilesDeleted'),(302,'BackendLogin'),(304,'BackendLogout'),(310,'BackendProjectDelete'),(312,'BackendProjectFeature'),(314,'BackendProjectApproved'),(316,'BackendProjectCatChanged'),(318,'BackendProjectPlingExcluded'),(320,'BackendUserDeleted');
0032 ALTER TABLE `activity_log_types` ENABLE KEYS ;
0033 
0034 CREATE TABLE IF NOT EXISTS `comment_types` (
0035   `comment_type_id` int(11) DEFAULT NULL,
0036   `name` varchar(50) DEFAULT NULL,
0037   KEY `pk` (`comment_type_id`)
0038 ) ENGINE=InnoDB;
0039 TRUNCATE `comment_types`;
0040 ALTER TABLE `comment_types` DISABLE KEYS ;
0041 INSERT INTO `comment_types` VALUES (0,'project');
0042 ALTER TABLE `comment_types` ENABLE KEYS ;
0043 
0044 CREATE TABLE IF NOT EXISTS `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;
0065 
0066 CREATE TABLE IF NOT EXISTS `config_store` (
0067   `store_id` int(11) NOT NULL AUTO_INCREMENT,
0068   `host` varchar(45) NOT NULL,
0069   `name` varchar(45) NOT NULL,
0070   `config_id_name` varchar(45) NOT NULL,
0071   `mapping_id_name` varchar(45) DEFAULT NULL,
0072   `order` int(11) DEFAULT '0',
0073   `default` int(1) DEFAULT '0',
0074   `is_client` int(1) DEFAULT '0',
0075   `google_id` varchar(45) DEFAULT NULL,
0076   `package_type` varchar(45) DEFAULT NULL COMMENT '1-n package_type_ids',
0077   `cross_domain_login` int(1) NOT NULL DEFAULT '0',
0078   `created_at` datetime DEFAULT NULL,
0079   `changed_at` datetime DEFAULT NULL,
0080   `deleted_at` datetime DEFAULT NULL,
0081   PRIMARY KEY (`store_id`)
0082 ) ENGINE=InnoDB;
0083 DELIMITER ;;
0084 DROP TRIGGER IF EXISTS `config_store_BEFORE_INSERT`;;
0085 CREATE DEFINER=CURRENT_USER TRIGGER `config_store_BEFORE_INSERT` BEFORE INSERT ON `config_store` FOR EACH ROW BEGIN
0086 
0087   IF NEW.created_at IS NULL THEN
0088 
0089     SET NEW.created_at = NOW();
0090 
0091   END IF;
0092 
0093 END ;;
0094 DELIMITER ;
0095 TRUNCATE `config_store`;
0096 ALTER TABLE `config_store` DISABLE KEYS ;
0097 INSERT INTO `config_store` VALUES (1,'localhost','localhost-develop','default',NULL,1,1,0,'',NULL,1,'2016-05-23 05:57:08',NULL,NULL);
0098 ALTER TABLE `config_store` ENABLE KEYS ;
0099 
0100 /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
0101 /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
0102 
0103 CREATE TABLE IF NOT EXISTS `config_store_category` (
0104   `store_category_id` int(11) NOT NULL AUTO_INCREMENT,
0105   `store_id` int(11) DEFAULT NULL,
0106   `project_category_id` int(11) DEFAULT NULL,
0107   `order` int(11) DEFAULT '0',
0108   `created_at` datetime DEFAULT NULL,
0109   `changed_at` datetime DEFAULT NULL,
0110   `deleted_at` datetime DEFAULT NULL,
0111   PRIMARY KEY (`store_category_id`),
0112   KEY `project_category_id_idx` (`project_category_id`),
0113   KEY `fk_store_id_idx` (`store_id`),
0114   CONSTRAINT `fk_project_category_id` FOREIGN KEY (`project_category_id`) REFERENCES `project_category` (`project_category_id`) ON DELETE CASCADE ON UPDATE NO ACTION
0115 ) ENGINE=InnoDB;
0116 DELIMITER ;;
0117 DROP TRIGGER IF EXISTS `config_store_category_BEFORE_INSERT`;;
0118 CREATE DEFINER=CURRENT_USER TRIGGER `config_store_category_BEFORE_INSERT` BEFORE INSERT ON `config_store_category` FOR EACH ROW BEGIN
0119 
0120   IF NEW.created_at IS NULL THEN
0121 
0122     SET NEW.created_at = NOW();
0123 
0124   END IF;
0125 
0126 END ;;
0127 DELIMITER ;
0128 /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
0129 /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
0130 
0131 CREATE TABLE IF NOT EXISTS `mail_template` (
0132   `mail_template_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
0133   `name` varchar(50) NOT NULL,
0134   `subject` varchar(250) NOT NULL,
0135   `text` text NOT NULL,
0136   `created_at` datetime NOT NULL,
0137   `changed_at` datetime DEFAULT NULL,
0138   `deleted_at` datetime DEFAULT NULL,
0139   PRIMARY KEY (`mail_template_id`),
0140   UNIQUE KEY `unique_name` (`name`)
0141 ) ENGINE=InnoDB;
0142 TRUNCATE `mail_template`;
0143 ALTER TABLE `mail_template` DISABLE KEYS ;
0144 INSERT INTO `mail_template` VALUES (5,'tpl_verify_user','%servername%: Please verify your email address','<h2>Hey %username%,</h2>\r\n<p><br />Thank you for signing up to %servername%.</p>\r\n<p><br />Please click the button below to verify this email address:</p>\r\n<div><!-- [if mso]>\r\n    <v:roundrect xmlns:v=\"urn:schemas-microsoft-com:vml\" xmlns:w=\"urn:schemas-microsoft-com:office:word\"\r\n                 href=\"%verificationurl%\" style=\"height:40px;v-text-anchor:middle;width:300px;\" arcsize=\"10%\"\r\n                 stroke=\"f\" fillcolor=\"#34495C\">\r\n        <w:anchorlock/>\r\n        <center style=\"color:#ffffff;font-family:sans-serif;font-size:16px;font-weight:bold;\">\r\n            Verify your e-mail address\r\n        </center>\r\n    </v:roundrect>\r\n    <![endif]--> <!-- [if !mso]> <!-->\r\n<table cellspacing=\"0\" cellpadding=\"0\">\r\n<tbody>\r\n<tr>\r\n<td style=\"-webkit-border-radius: 5px; -moz-border-radius: 5px; border-radius: 5px; color: #ffffff; display: block;\" align=\"center\" bgcolor=\"#34495C\" width=\"300\" height=\"40\"><a style=\"color: #ffffff; font-size: 16px; font-weight: bold; font-family: sans-serif; text-decoration: none; line-height: 40px; width: 100%; display: inline-block;\" href=\"%verificationurl%\"> Verify your e-mail address </a></td>\r\n</tr>\r\n</tbody>\r\n</table>\r\n<!-- <![endif]--></div>\r\n<p><br />If the button doesn&rsquo;t work, you can copy and paste the following link to your browser:<br /><br />%verificationlink%&nbsp;</p>\r\n<p><br />If you have any problems, feel free to contact us at any time!</p>\r\n<p><br /><br />Kind regards,<br />Your openDesktop Team <br /><a href=\"mailto:contact@opendesktop.org\" target=\"_blank\">contact@opendesktop.org</a><br /><br /></p>','2011-11-07 10:28:43','2015-12-09 16:27:02',NULL),(7,'tpl_social_mail','<%sender%> sent you a recommendation','<p>&lt;%sender%&gt; has suggested that you could be interested in this member</p>\r\n<h2>%username%.</h2>\r\n<p>%permalinktext%</p>\r\n<p><br />If the link doesn&rsquo;t work, you can copy and paste the following link to your browser:</p>\r\n<h4>%permalink%</h4>\r\n<p><br />Kind regards,<br />\r\n    Team Pling</p>','2011-11-07 10:36:48','2013-11-08 11:51:44',NULL),(8,'tpl_user_message','opendesktop.org - Du hast eine Nachricht erhalten','Hallo %username%,<br/><br/>%sender% hat dir eine Nachricht geschickt.<br/><br/><div style=\'width: 500px; background-color: #F2F2F2; border: 1px solid #C1C1C1; padding: 10px;\'>%message_text%</div>','2011-11-07 10:40:06','2011-11-28 16:18:48',NULL),(9,'tpl_newuser_notification','opendesktop.org - Neue Memberanmeldung','Jemand hat sich angemeldet: <strong>%username%</strong> angemeldet.<br/><br/><br/>Grüße das pling-System :)','2011-11-28 15:50:59',NULL,NULL),(10,'tpl_user_newpass','opendesktop.org - your new password','<p>Hello %username%,<br /><br />We created this new password for your opendesktop.org account: <b>%newpass%</b><br /><br /><p><br />If you have any problems, feel free to contact us at any time!</p>\r\n<p><br /><br />Kind regards,<br />Your openDesktop Team <br /><a href=\"mailto:contact@opendesktop.org\" target=\"_blank\">contact@opendesktop.org</a><br /><br /></p>','2011-11-28 15:55:38','2015-12-09 16:26:10',NULL),(11,'tpl_newproject_notification','opendesktop.org - Neue Projektanmeldung','Ein neues Projekt wurde von <strong>%username%</strong> angemeldet.<br/>Mehr dazu im Backend hier: http://opendesktop.org/backend/project/apply<br/>Grüße das opendesktop.org-System :)','2011-11-28 16:41:00',NULL,NULL),(12,'tpl_verify_button_user','%servername%: Please verify your email address','<h2>Hey %username%,</h2>\r\n<p><br />thank you for signing up to opendesktop.org</p>\r\n<p>We have generated a new password for you. We recommend you to change this password as soon as possible in your settings.<br /><br />Your password: %password%</p>\r\n<p><br />Before you&nbsp;can use your button and&nbsp;receive loads of plings or love and pling other products, please klick the link below&nbsp;to verify your email address.</p>\r\n<p><br />If the link doesn&rsquo;t work, you can copy and paste the following link to your browser:<br /><br />%verificationlinktext%&nbsp;</p>\r\n<p><br />In case the problem still occurs, feel free to contact us at any time!</p>\r\n<p><br /><br />Kind regards,<br />Your openDesktop Team <br /><a href=\"mailto:contact@opendesktop.org\" target=\"_blank\">contact@opendesktop.org</a><br /><br /></p>','2014-04-24 08:40:27','2015-12-09 17:29:18',NULL),(13,'tpl_social_mail_product','<%sender%> sent you a recommendation','<p>&lt;%sender%&gt; has suggested that you could be interested in this product</p>\r\n<h2>%title%</h2>\r\n<p>from our opendesktop.org member <em>%username%</em>.\r\n</p>\r\n<p>%permalinktext%</p>\r\n<p><br />If the link doesn&rsquo;t work, you can copy and paste the following link to your browser:</p>\r\n<h4>%permalinktext%</h4>\r\n<p><br />Kind regards,<br />\r\n    Team opendesktop.org</p>','2013-11-08 10:46:42','2013-11-08 11:52:04',NULL),(14,'tpl_mail_claim_product','User wants to claim a product','<p>The opendesktop.org-system received a request from a user</p>\r <p>%userid% :: %username% :: %usermail%</p>\r <p>who wants to claim the following product:</p>\r <p>%productid% :: %producttitle%&nbsp;</p>\r <p>&nbsp;</p>\r <p>Greetings from the opendesktop.org-system</p>','2014-05-14 10:15:22','2014-05-14 10:43:21',NULL),(15,'tpl_mail_claim_confirm','opendesktop.org: We received your inquiry','<h2>Hello %username%,</h2>\r\n<p>you want to claim the following product:</p>\r\n<p><a href=\"%productlink%\">%producttitle%</a></p>\r\n<p>We try to process your request as quickly as possible.<br />You will receive a notice shortly if your claim has been approved.</p>\r\n<p><br /><br />Kind regards,<br />Team opendesktop.org&nbsp;<br /><a href=\"mailto:contact@opendesktop.org\">contact@opendesktop.org</a></p>','2014-05-14 10:39:59','2015-12-09 17:29:52',NULL),(16,'tpl_user_comment_note','opendesktop.org - You Received A New Comment','<h2>Hey %username%,</h2>\r\n<p><br />you received a new comment on <b>%product_title%</b></p>\r\n<p><br />Here is what the user wrote:</p>\r\n<div><br />%comment_text%</div>\r\n<p><br /><br />Please do not reply to the email, but use the comment system for this product instead:<br />\r\n<a href=\"https://www.opendesktop.org/p/%product_id%\">%product_title%</a></p>\r\n<p><br /><br />Kind regards,<br />Your openDesktop Team <br /><a href=\"mailto:contact@opendesktop.org\" target=\"_blank\">contact@opendesktop.org</a><br /><br /></p>','2016-09-15 08:16:00','2016-09-15 08:31:07',NULL),(17,'tpl_verify_email','%servername% - Please verify your email address','<h2>Hey %username%,</h2>\r\n<p>\r\n  Help us secure your account by verifying your email address\r\n  (<a href=\"mailto:%email_address%\">%email_address%</a>).\r\n    This will let you receive notifications and password resets from our system.\r\n</p>\r\n<div><!-- [if mso]>\r\n    <v:roundrect xmlns:v=\"urn:schemas-microsoft-com:vml\" xmlns:w=\"urn:schemas-microsoft-com:office:word\"\r\n                 href=\"%verificationurl%\" style=\"height:40px;v-text-anchor:middle;width:300px;\" arcsize=\"10%\"\r\n                 stroke=\"f\" fillcolor=\"#34495C\">\r\n        <w:anchorlock/>\r\n        <center style=\"color:#ffffff;font-family:sans-serif;font-size:16px;font-weight:bold;\">\r\n            Verify your e-mail address here\r\n        </center>\r\n    </v:roundrect>\r\n    <![endif]--> <!-- [if !mso]> <!-->\r\n<table cellspacing=\"0\" cellpadding=\"0\">\r\n<tbody>\r\n<tr>\r\n<td style=\"-webkit-border-radius: 5px; -moz-border-radius: 5px; border-radius: 5px; color: #ffffff; display: block;\" align=\"center\" bgcolor=\"#34495C\" width=\"300\" height=\"40\"><a style=\"color: #ffffff; font-size: 16px; font-weight: bold; font-family: sans-serif; text-decoration: none; line-height: 40px; width: 100%; display: inline-block;\" href=\"%verificationurl%\"> Verify your e-mail address </a></td>\r\n</tr>\r\n</tbody>\r\n</table>\r\n<!-- <![endif]--></div>\r\n<p><br />If the button doesn&rsquo;t work, you can copy and paste the following link to your browser:<br /><br />%verificationlink%&nbsp;</p>\r\n<p><br />If you have any problems, feel free to contact us at any time!</p>\r\n<p><br /><br />Kind regards,<br />Your openDesktop Team <br /><a href=\"mailto:contact@opendesktop.org\" target=\"_blank\">contact@opendesktop.org</a><br /><br /></p>','2016-09-23 07:16:31','2016-09-23 07:16:31',NULL),(18,'tpl_user_comment_reply_note','opendesktop.org - You received a new reply to your comment','<h2>Hey %username%,</h2>\r\n<p><br />you received a new reply to your comment on <b>%product_title%</b></p>\r\n<p><br />Here is what the user wrote:</p>\r\n<div><br />%comment_text%</div>\r\n<p><br /><br />Please do not reply to the email, but use the comment system for this product instead:<br />\r\n<a href=\"https://www.opendesktop.org/p/%product_id%\">%product_title%</a></p>\r\n<p><br /><br />Kind regards,<br />Your openDesktop Team <br /><a href=\"mailto:contact@opendesktop.org\" target=\"_blank\">contact@opendesktop.org</a><br /><br /></p>','2016-10-07 10:49:15','2016-10-07 10:49:15',NULL);
0145 ALTER TABLE `mail_template` ENABLE KEYS ;
0146 
0147 CREATE TABLE IF NOT EXISTS `member` (
0148   `member_id` int(10) NOT NULL AUTO_INCREMENT,
0149   `uuid` varchar(255) DEFAULT NULL,
0150   `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
0151   `mail` varchar(255) DEFAULT NULL,
0152   `password` varchar(255) DEFAULT NULL,
0153   `roleId` int(11) NOT NULL,
0154   `avatar` varchar(255) NOT NULL DEFAULT 'default-profile.png',
0155   `type` int(1) NOT NULL DEFAULT '0' COMMENT 'Type: 0 = Member, 1 = group',
0156   `is_active` int(1) NOT NULL DEFAULT '0',
0157   `is_deleted` int(1) NOT NULL DEFAULT '0',
0158   `mail_checked` int(1) NOT NULL DEFAULT '0',
0159   `agb` int(1) NOT NULL DEFAULT '0',
0160   `newsletter` int(1) NOT NULL DEFAULT '0',
0161   `login_method` varchar(45) NOT NULL DEFAULT 'local' COMMENT 'local (registered on pling), facebook, twitter',
0162   `firstname` varchar(200) DEFAULT NULL,
0163   `lastname` varchar(200) DEFAULT NULL,
0164   `street` varchar(255) DEFAULT NULL,
0165   `zip` varchar(5) DEFAULT NULL,
0166   `city` varchar(255) DEFAULT NULL,
0167   `country` varchar(255) DEFAULT NULL,
0168   `phone` varchar(255) DEFAULT NULL,
0169   `last_online` datetime DEFAULT NULL,
0170   `biography` text,
0171   `paypal_mail` varchar(255) DEFAULT NULL,
0172   `paypal_valid_status` mediumint(9) DEFAULT NULL,
0173   `wallet_address` varchar(255) DEFAULT NULL,
0174   `dwolla_id` varchar(45) DEFAULT NULL,
0175   `main_project_id` int(10) DEFAULT NULL COMMENT 'Die ID des .me-Projekts',
0176   `profile_image_url` varchar(355) DEFAULT '/images/system/default-profile.png' COMMENT 'URL to the profile-image',
0177   `profile_image_url_bg` varchar(355) DEFAULT NULL,
0178   `profile_img_src` varchar(45) DEFAULT 'local' COMMENT 'social,gravatar,local',
0179   `social_username` varchar(50) DEFAULT NULL COMMENT 'Username on facebook/twitter. Used to generate profile-img-url.',
0180   `social_user_id` varchar(50) DEFAULT NULL COMMENT 'ID from twitter, facebook,...',
0181   `gravatar_email` varchar(45) DEFAULT NULL COMMENT 'email, wich is connected to gravatar.',
0182   `facebook_username` varchar(45) DEFAULT NULL,
0183   `twitter_username` varchar(45) DEFAULT NULL,
0184   `link_facebook` varchar(300) DEFAULT NULL COMMENT 'Link to facebook',
0185   `link_twitter` varchar(300) DEFAULT NULL COMMENT 'Link to twitter',
0186   `link_website` varchar(300) DEFAULT NULL COMMENT 'Link to homepage',
0187   `link_google` varchar(300) DEFAULT NULL COMMENT 'Link to google',
0188   `link_github` varchar(300) DEFAULT NULL,
0189   `validated_at` datetime DEFAULT NULL,
0190   `validated` int(1) DEFAULT '0',
0191   `created_at` datetime DEFAULT NULL,
0192   `changed_at` datetime DEFAULT NULL,
0193   `deleted_at` datetime DEFAULT NULL,
0194   `source_id` int(11) DEFAULT '0' COMMENT '0 = local, 1 = hive01',
0195   `source_pk` int(11) DEFAULT NULL COMMENT 'pk on the source',
0196   PRIMARY KEY (`member_id`),
0197   KEY `uuid` (`uuid`),
0198   KEY `idx_created` (`created_at`),
0199   KEY `idx_login` (`mail`,`username`,`password`,`is_active`,`is_deleted`,`login_method`),
0200   KEY `idx_mem_search` (`member_id`,`username`,`is_deleted`,`mail_checked`),
0201   KEY `idx_source` (`source_id`,`source_pk`),
0202   KEY `idx_username` (`username`),
0203   KEY `idx_id_active` (`member_id`,`is_active`,`is_deleted`)
0204 ) ENGINE=InnoDB;
0205 DELIMITER ;;
0206 DROP TRIGGER IF EXISTS `member_created`;;
0207 CREATE DEFINER=CURRENT_USER TRIGGER `member_created` BEFORE INSERT ON `member` FOR EACH ROW BEGIN
0208 
0209   IF NEW.created_at IS NULL THEN
0210 
0211     SET NEW.created_at = NOW();
0212 
0213   END IF;
0214 
0215 END ;;
0216 DROP TRIGGER IF EXISTS `member_BEFORE_UPDATE`;;
0217 CREATE DEFINER=CURRENT_USER TRIGGER `member_BEFORE_UPDATE` BEFORE UPDATE ON `member` FOR EACH ROW
0218   BEGIN
0219     SET NEW.changed_at = NOW();
0220   END ;;
0221 DELIMITER ;
0222 
0223 CREATE TABLE IF NOT EXISTS `member_email` (
0224   `email_id` int(11) NOT NULL AUTO_INCREMENT,
0225   `email_member_id` int(11) NOT NULL,
0226   `email_address` varchar(255) NOT NULL,
0227   `email_primary` int(1) DEFAULT '0',
0228   `email_deleted` int(1) DEFAULT '0',
0229   `email_created` datetime DEFAULT NULL,
0230   `email_checked` datetime DEFAULT NULL,
0231   `email_verification_value` varchar(255) DEFAULT NULL,
0232   PRIMARY KEY (`email_id`),
0233   KEY `idx_address` (`email_address`),
0234   KEY `idx_member` (`email_member_id`),
0235   KEY `idx_verification` (`email_verification_value`)
0236 ) ENGINE=InnoDB;
0237 DELIMITER ;;
0238 DROP TRIGGER IF EXISTS `member_email_BEFORE_INSERT`;;
0239 CREATE DEFINER=CURRENT_USER TRIGGER member_email_BEFORE_INSERT BEFORE INSERT ON member_email FOR EACH ROW
0240 
0241   BEGIN
0242     IF NEW.email_created IS NULL THEN
0243       SET NEW.email_created = NOW();
0244     END IF;
0245 
0246   END ;;
0247 DELIMITER ;
0248 
0249 CREATE TABLE IF NOT EXISTS `member_dl_plings` (
0250   `yearmonth` int(6) DEFAULT NULL,
0251   `project_id` int(11) NOT NULL DEFAULT '0',
0252   `project_category_id` int(11) NOT NULL DEFAULT '0',
0253   `member_id` int(11) NOT NULL,
0254   `mail` varchar(255) DEFAULT NULL,
0255   `paypal_mail` varchar(255) DEFAULT NULL,
0256   `num_downloads` bigint(21) NOT NULL DEFAULT '0',
0257   `dl_pling_factor` decimal(3,2) NOT NULL DEFAULT '0.00',
0258   `probably_payout_amount` decimal(25,4) DEFAULT NULL,
0259   `created_at` datetime DEFAULT NULL,
0260   `updated_at` datetime DEFAULT NULL,
0261   UNIQUE KEY `uk_month_proj` (`yearmonth`,`member_id`,`project_id`),
0262   KEY `idx_yearmonth` (`yearmonth`)
0263 ) ENGINE=InnoDB;
0264 DELIMITER ;;
0265 DROP TRIGGER IF EXISTS `member_dl_plings_BEFORE_INSERT`;;
0266 CREATE DEFINER=CURRENT_USER TRIGGER member_dl_plings_BEFORE_INSERT BEFORE INSERT ON member_dl_plings FOR EACH ROW
0267 
0268   BEGIN
0269     IF NEW.created_at IS NULL THEN
0270       SET NEW.created_at = NOW();
0271     END IF;
0272   END ;;
0273 DELIMITER ;
0274 
0275 CREATE TABLE IF NOT EXISTS `member_follower` (
0276   `member_follower_id` int(11) NOT NULL AUTO_INCREMENT,
0277   `member_id` int(11) DEFAULT NULL,
0278   `follower_id` int(11) DEFAULT NULL,
0279   PRIMARY KEY (`member_follower_id`),
0280   KEY `follower_id` (`follower_id`)
0281 ) ENGINE=InnoDB;
0282 
0283 CREATE TABLE IF NOT EXISTS `member_group` (
0284   `member_group_id` int(11) NOT NULL AUTO_INCREMENT,
0285   `member_id` int(11) DEFAULT NULL,
0286   `group_id` int(11) DEFAULT NULL,
0287   `role_id` int(11) DEFAULT '1' COMMENT 'Role of the rgoup-member. 1 = User, 2 = admin. See table member_group_role.',
0288   `is_active` int(11) NOT NULL DEFAULT '0' COMMENT 'Group-Member active?',
0289   `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
0290   `changed_at` timestamp NULL DEFAULT NULL,
0291   `deleted_at` timestamp NULL DEFAULT NULL,
0292   `is_deleted` int(11) DEFAULT '0',
0293   PRIMARY KEY (`member_group_id`)
0294 ) ENGINE=InnoDB COMMENT='Connection between members ans groups. ';
0295 
0296 CREATE TABLE IF NOT EXISTS `member_group_role` (
0297   `member_group_role_id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID',
0298   `title` varchar(45) DEFAULT NULL,
0299   `short_text` varchar(45) DEFAULT NULL,
0300   `is_active` int(11) DEFAULT '1',
0301   `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
0302   `deleted_at` timestamp NULL DEFAULT NULL,
0303   PRIMARY KEY (`member_group_role_id`)
0304 ) ENGINE=InnoDB;
0305 TRUNCATE `member_group_role`;
0306 INSERT INTO `member_group_role` VALUES (1,'user','normal user',1,'2012-11-13 15:54:27',NULL),(2,'admin','super admin',1,'2012-11-13 15:54:27',NULL);
0307 
0308 CREATE TABLE IF NOT EXISTS `member_payout` (
0309   `id` int(11) NOT NULL AUTO_INCREMENT,
0310   `yearmonth` int(11) NOT NULL,
0311   `member_id` int(11) NOT NULL,
0312   `mail` varchar(50) DEFAULT NULL,
0313   `paypal_mail` varchar(50) DEFAULT NULL,
0314   `num_downloads` int(11) DEFAULT NULL,
0315   `num_points` int(11) DEFAULT NULL,
0316   `amount` double DEFAULT NULL,
0317   `status` int(11) NOT NULL DEFAULT '0' COMMENT '0=new,1=start request,10=processed,100=completed,999=error',
0318   `created_at` datetime DEFAULT NULL,
0319   `updated_at` datetime DEFAULT NULL,
0320   `timestamp_masspay_start` timestamp NULL DEFAULT NULL,
0321   `timestamp_masspay_last_ipn` timestamp NULL DEFAULT NULL,
0322   `last_paypal_ipn` text,
0323   `last_paypal_status` text,
0324   `payment_reference_key` varchar(255) DEFAULT NULL COMMENT 'uniquely identifies the request',
0325   `payment_transaction_id` varchar(255) DEFAULT NULL COMMENT 'uniquely identify caller (developer, facilliator, marketplace) transaction',
0326   `payment_raw_message` varchar(2000) DEFAULT NULL COMMENT 'the raw text message ',
0327   `payment_raw_error` varchar(2000) DEFAULT NULL,
0328   `payment_status` varchar(45) DEFAULT NULL,
0329   PRIMARY KEY (`id`),
0330   UNIQUE KEY `UK_PAYOUT` (`yearmonth`,`member_id`)
0331 ) ENGINE=InnoDB COMMENT='Table for our monthly payouts';
0332 DELIMITER ;;
0333 DROP TRIGGER IF EXISTS `member_payout_BEFORE_INSERT`;;
0334 CREATE DEFINER=CURRENT_USER TRIGGER member_payout_BEFORE_INSERT BEFORE INSERT ON member_payout FOR EACH ROW
0335 
0336   BEGIN
0337    IF NEW.created_at IS NULL THEN
0338      SET NEW.created_at = NOW();
0339    END IF;
0340   END ;;
0341 DELIMITER ;
0342 
0343 CREATE TABLE IF NOT EXISTS `member_paypal` (
0344   `id` int(11) NOT NULL AUTO_INCREMENT,
0345   `member_id` int(11) DEFAULT NULL,
0346   `paypal_address` varchar(150) DEFAULT NULL,
0347   `is_active` int(1) unsigned DEFAULT '1',
0348   `name` varchar(150) DEFAULT NULL,
0349   `address` varchar(150) DEFAULT NULL,
0350   `currency` varchar(150) DEFAULT NULL,
0351   `country_code` varchar(150) DEFAULT NULL,
0352   `last_payment_status` varchar(150) DEFAULT NULL,
0353   `last_payment_amount` double DEFAULT NULL,
0354   `last_transaction_id` varchar(50) DEFAULT NULL,
0355   `last_transaction_event_code` varchar(50) DEFAULT NULL,
0356   `created_at` timestamp NULL DEFAULT NULL,
0357   `changed_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
0358   PRIMARY KEY (`id`),
0359   UNIQUE KEY `uk_paypal_address` (`paypal_address`)
0360 ) ENGINE=InnoDB;
0361 
0362 CREATE TABLE IF NOT EXISTS `member_role` (
0363   `member_role_id` int(11) NOT NULL,
0364   `title` varchar(100) NOT NULL,
0365   `shortname` varchar(50) NOT NULL,
0366   `is_active` int(1) NOT NULL DEFAULT '0',
0367   `is_deleted` int(1) NOT NULL DEFAULT '0',
0368   `created_at` datetime DEFAULT NULL,
0369   `changed_at` datetime DEFAULT NULL,
0370   `deleted_at` datetime DEFAULT NULL,
0371   PRIMARY KEY (`member_role_id`)
0372 ) ENGINE=InnoDB;
0373 TRUNCATE `member_role`;
0374 ALTER TABLE `member_role` DISABLE KEYS;
0375 INSERT INTO `member_role` VALUES (100,'Administrator','admin',1,0,NULL,NULL,NULL),(200,'Mitarbeiter','staff',1,0,NULL,NULL,NULL),(300,'FrontendBenutzer','feuser',1,0,NULL,NULL,NULL);
0376 ALTER TABLE `member_role` ENABLE KEYS;
0377 
0378 CREATE TABLE IF NOT EXISTS `member_token` (
0379   `token_id` int(11) NOT NULL AUTO_INCREMENT,
0380   `token_member_id` int(11) NOT NULL,
0381   `token_provider_name` varchar(45) NOT NULL,
0382   `token_value` varchar(45) NOT NULL,
0383   `token_provider_username` varchar(45) DEFAULT NULL,
0384   `token_fingerprint` varchar(45) DEFAULT NULL,
0385   `token_created` datetime DEFAULT NULL,
0386   `token_changed` datetime DEFAULT NULL,
0387   `token_deleted` datetime DEFAULT NULL,
0388   PRIMARY KEY (`token_id`),
0389   KEY `idx_token` (`token_member_id`,`token_provider_name`,`token_value`)
0390 ) ENGINE=InnoDB;
0391 DELIMITER ;;
0392 DROP TRIGGER IF EXISTS `member_token_before_insert`;;
0393 CREATE DEFINER=CURRENT_USER TRIGGER `member_token_before_insert` BEFORE INSERT ON `member_token` FOR EACH ROW BEGIN
0394 
0395   IF NEW.token_created IS NULL THEN
0396     SET NEW.token_created = NOW();
0397   END IF;
0398 
0399 END ;;
0400 DELIMITER ;
0401 
0402 CREATE TABLE IF NOT EXISTS `payout` (
0403   `id` int(11) NOT NULL AUTO_INCREMENT,
0404   `yearmonth` int(11) NOT NULL,
0405   `member_id` int(11) NOT NULL,
0406   `amount` int(11) NOT NULL,
0407   `timestamp_start` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
0408   `timestamp_success` timestamp NULL DEFAULT NULL,
0409   `paypal_ipn` text,
0410   PRIMARY KEY (`id`),
0411   UNIQUE KEY `UK_PAYOUT` (`yearmonth`,`member_id`)
0412 ) ENGINE=InnoDB COMMENT='Table for our monthly payouts';
0413 
0414 CREATE TABLE IF NOT EXISTS `payout_status` (
0415   `id` int(11) NOT NULL,
0416   `type` varchar(50) NOT NULL DEFAULT 'info',
0417   `title` varchar(50) DEFAULT NULL,
0418   `description` text,
0419   `color` varchar(50) DEFAULT NULL,
0420   `icon` varchar(50) DEFAULT 'glyphicon-info-sign',
0421   `is_active` int(1) DEFAULT '1',
0422   PRIMARY KEY (`id`)
0423 ) ENGINE=InnoDB;
0424 TRUNCATE `payout_status`;
0425 ALTER TABLE `payout_status` DISABLE KEYS ;
0426 INSERT INTO `payout_status` VALUES (1,'info','Status: Requested','We send your payout. The actual status is: Requested.','#31708f;','glyphicon-info-sign',0),(10,'info','Status: Processed','We send your payout. The actual status is: Processed.','#31708f;','glyphicon-info-sign',0),(50,'info','Status: Pending','We send your payout. The actual status is: Pending.','#31708f;','glyphicon-info-sign',1),(99,'info','Status: Refund','We tried to payout your plings, but your payment was refund.','#112c8b;','glyphicon-info-sign',0),(100,'success','Status: Completed','For this month we has successfully paid you.','#3c763d;','glyphicon-ok-sign',1),(900,'info','Status: Refunded','We send you the payment, but you refunded it. ','#0f2573','glyphicon-exclamation-sign',1),(901,'info','Status: Refunded by Paypal','Your Mailadress is not signed up for a PayPal account or you did not complete the registration process.','#112c8b','glyphicon-info-sign',1),(910,'warning','Status: Not allowed','PayPal denies our payment because you only can receive website payments. Please change your settings on PayPal.','#bd8614','glyphicon-exclamation-sign',1),(920,'warning','Status: Personal Payments','We tried to send you money, but the PayPal message was: Sorry, this recipient can’t accept personal payments.','#bd8614','glyphicon-exclamation-sign',1),(930,'danger','Status: currently unable','We tried to send you money, but Paypal denied this with the following message: This recipient is currently unable to receive money.','#a94442;','glyphicon-exclamation-sign',1),(940,'danger','Status: Denied','We tried to send you money, but Paypal denied this with the following message: We can’t send your payment right now. If you keep running into this issue, please contact.','#a94442;','glyphicon-exclamation-sign',1),(950,'danger','Status: Failed','Our Payment failed','#a94442;','glyphicon-exclamation-sign',1),(999,'danger','API Error','We tried to send the money automatically via the Paypal-API, but we temporarily got an error.  We will try the payout again, so please stay tuned.','#f71f1f','glyphicon-info-sign',1);
0427 ALTER TABLE `payout_status` ENABLE KEYS ;
0428 
0429 CREATE TABLE IF NOT EXISTS `paypal_ipn` (
0430   `id` int(11) NOT NULL AUTO_INCREMENT,
0431   `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
0432   `txn_type` varchar(50) DEFAULT NULL,
0433   `ipn_track_id` varchar(50) DEFAULT NULL,
0434   `txn_id` varchar(50) DEFAULT NULL,
0435   `payer_email` varchar(50) DEFAULT NULL,
0436   `payer_id` varchar(50) DEFAULT NULL,
0437   `auth_amount` varchar(50) DEFAULT NULL,
0438   `mc_currency` varchar(50) DEFAULT NULL,
0439   `mc_fee` varchar(50) DEFAULT NULL,
0440   `mc_gross` varchar(50) DEFAULT NULL,
0441   `memo` varchar(50) DEFAULT NULL,
0442   `payer_status` varchar(50) DEFAULT NULL,
0443   `payment_date` varchar(50) DEFAULT NULL,
0444   `payment_fee` varchar(50) DEFAULT NULL,
0445   `payment_status` varchar(50) DEFAULT NULL,
0446   `payment_type` varchar(50) DEFAULT NULL,
0447   `pending_reason` varchar(50) DEFAULT NULL,
0448   `reason_code` varchar(50) DEFAULT NULL,
0449   `custom` varchar(50) DEFAULT NULL,
0450   `raw` text,
0451   PRIMARY KEY (`id`)
0452 ) ENGINE=InnoDB COMMENT='Save all PayPal IPNs here';
0453 
0454 CREATE TABLE IF NOT EXISTS `paypal_valid_status` (
0455   `id` int(11) NOT NULL,
0456   `title` varchar(50) DEFAULT NULL,
0457   `description` text,
0458   `color` varchar(50) DEFAULT NULL,
0459   `is_active` int(1) DEFAULT '1',
0460   PRIMARY KEY (`id`)
0461 ) ENGINE=InnoDB;
0462 
0463 CREATE TABLE IF NOT EXISTS `plings` (
0464   `id` int(11) NOT NULL AUTO_INCREMENT,
0465   `member_id` int(11) NOT NULL COMMENT 'pling-Owner',
0466   `project_id` int(11) DEFAULT NULL COMMENT 'Witch project was plinged',
0467   `status_id` int(11) DEFAULT '0' COMMENT 'Stati des pling: 0 = inactive, 1 = active (plinged), 2 = payed successfull, 99 = deleted',
0468   `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation-time',
0469   `pling_time` timestamp NULL DEFAULT NULL COMMENT 'When was a project plinged?',
0470   `active_time` timestamp NULL DEFAULT NULL COMMENT 'When did paypal say, that this pling was payed successfull',
0471   `delete_time` timestamp NULL DEFAULT NULL,
0472   `amount` double(10,2) DEFAULT '0.00' COMMENT 'Amount of money',
0473   `comment` varchar(140) DEFAULT NULL COMMENT 'Comment from the plinger',
0474   `payment_provider` varchar(45) DEFAULT NULL,
0475   `payment_reference_key` varchar(255) DEFAULT NULL COMMENT 'uniquely identifies the request',
0476   `payment_transaction_id` varchar(255) DEFAULT NULL COMMENT 'uniquely identify caller (developer, facilliator, marketplace) transaction',
0477   `payment_raw_message` varchar(2000) DEFAULT NULL COMMENT 'the raw text message ',
0478   `payment_raw_error` varchar(2000) DEFAULT NULL,
0479   `payment_status` varchar(45) DEFAULT NULL,
0480   PRIMARY KEY (`id`),
0481   KEY `project_id` (`project_id`),
0482   KEY `status_id` (`status_id`),
0483   KEY `member_id` (`member_id`),
0484   KEY `PLINGS_IX_01` (`status_id`,`project_id`,`member_id`,`active_time`,`amount`)
0485 ) ENGINE=InnoDB;
0486 
0487 CREATE TABLE IF NOT EXISTS `project` (
0488   `project_id` int(11) NOT NULL AUTO_INCREMENT,
0489   `member_id` int(11) NOT NULL DEFAULT '0',
0490   `content_type` varchar(255) NOT NULL DEFAULT 'text',
0491   `project_category_id` int(11) NOT NULL DEFAULT '0',
0492   `hive_category_id` int(11) NOT NULL DEFAULT '0',
0493   `is_active` int(1) NOT NULL DEFAULT '0',
0494   `is_deleted` int(1) NOT NULL DEFAULT '0',
0495   `status` int(11) NOT NULL DEFAULT '0',
0496   `uuid` varchar(255) DEFAULT NULL,
0497   `pid` int(11) DEFAULT NULL COMMENT 'ParentId',
0498   `type_id` int(11) DEFAULT '0' COMMENT '0 = DummyProject, 1 = Project, 2 = Update',
0499   `title` varchar(100) DEFAULT NULL,
0500   `description` text,
0501   `version` varchar(50) DEFAULT NULL,
0502   `image_big` varchar(255) DEFAULT NULL,
0503   `image_small` varchar(255) DEFAULT NULL,
0504   `start_date` datetime DEFAULT NULL,
0505   `content_url` varchar(255) DEFAULT NULL,
0506   `created_at` datetime DEFAULT NULL,
0507   `changed_at` datetime DEFAULT NULL,
0508   `deleted_at` datetime DEFAULT NULL,
0509   `creator_id` int(11) DEFAULT NULL COMMENT 'Member_id of the creator. Importent for groups.',
0510   `facebook_code` text,
0511   `github_code` text,
0512   `twitter_code` text,
0513   `google_code` text,
0514   `link_1` text,
0515   `embed_code` text,
0516   `ppload_collection_id` varchar(255) DEFAULT NULL,
0517   `validated` int(1) DEFAULT '0',
0518   `validated_at` datetime DEFAULT NULL,
0519   `featured` int(1) DEFAULT '0',
0520   `approved` int(1) DEFAULT '0',
0521   `spam_checked` int(1) NOT NULL DEFAULT '0',
0522   `pling_excluded` int(1) NOT NULL DEFAULT '0' COMMENT 'Project was excluded from pling payout',
0523   `amount` int(11) DEFAULT NULL,
0524   `amount_period` varchar(45) DEFAULT NULL,
0525   `claimable` int(1) DEFAULT NULL,
0526   `claimed_by_member` int(11) DEFAULT NULL,
0527   `count_likes` int(11) DEFAULT '0',
0528   `count_dislikes` int(11) DEFAULT '0',
0529   `count_comments` int(11) DEFAULT '0',
0530   `count_downloads_hive` int(11) DEFAULT '0',
0531   `source_id` int(11) DEFAULT '0',
0532   `source_pk` int(11) DEFAULT NULL,
0533   `source_type` varchar(50) DEFAULT NULL,
0534   PRIMARY KEY (`project_id`),
0535   UNIQUE KEY `uk_source` (`source_id`,`source_pk`,`source_type`),
0536   KEY `idx_project_cat_id` (`project_category_id`),
0537   KEY `idx_uuid` (`uuid`),
0538   KEY `idx_member_id` (`member_id`),
0539   KEY `idx_pid` (`pid`),
0540   KEY `idx_created_at` (`created_at`),
0541   KEY `idx_title` (`title`),
0542   KEY `idx_source` (`source_id`,`source_pk`,`source_type`),
0543   KEY `idx_status` (`status`,`ppload_collection_id`,`project_category_id`,`project_id`),
0544   KEY `idx_type_status` (`type_id`,`status`,`project_category_id`,`project_id`),
0545   KEY `idx_ppload` (`ppload_collection_id`,`status`),
0546   KEY `idx_src_status` (`status`,`source_pk`,`source_type`)
0547 ) ENGINE=InnoDB;
0548 
0549 SET FOREIGN_KEY_CHECKS=0;
0550 CREATE TABLE IF NOT EXISTS `project_category` (
0551   `project_category_id` int(11) NOT NULL AUTO_INCREMENT,
0552   `lft` int(11) NOT NULL,
0553   `rgt` int(11) NOT NULL,
0554   `title` varchar(100) NOT NULL,
0555   `is_active` int(1) NOT NULL DEFAULT '0',
0556   `is_deleted` int(1) NOT NULL DEFAULT '0',
0557   `xdg_type` varchar(50) DEFAULT NULL,
0558   `name_legacy` varchar(50) DEFAULT NULL,
0559   `orderPos` int(11) DEFAULT NULL,
0560   `dl_pling_factor` double unsigned DEFAULT '1',
0561   `show_description` int(1) NOT NULL DEFAULT '0',
0562   `created_at` datetime DEFAULT NULL,
0563   `changed_at` datetime DEFAULT NULL,
0564   `deleted_at` datetime DEFAULT NULL,
0565   PRIMARY KEY (`project_category_id`),
0566   KEY `idxLeft` (`project_category_id`,`lft`),
0567   KEY `idxRight` (`project_category_id`,`rgt`),
0568   KEY `idxPrimaryRgtLft` (`project_category_id`,`rgt`,`lft`,`is_active`,`is_deleted`),
0569   KEY `idxActive` (`is_active`)
0570 ) ENGINE=InnoDB;
0571 ALTER TABLE `project_category` DISABLE KEYS ;
0572 TRUNCATE `project_category`;
0573 INSERT INTO `project_category` VALUES (1,0,1,'root',1,0,NULL,NULL,0,1,0,'2015-01-14 13:06:56','2017-10-25 03:23:11',NULL);
0574 ALTER TABLE `project_category` ENABLE KEYS ;
0575 DELIMITER ;;
0576 DROP TRIGGER IF EXISTS `project_category_BEFORE_INSERT`;;
0577 CREATE DEFINER=CURRENT_USER TRIGGER `project_category_BEFORE_INSERT` BEFORE INSERT ON `project_category` FOR EACH ROW
0578   BEGIN
0579     IF NEW.created_at IS NULL THEN
0580       SET NEW.created_at = NOW();
0581     END IF;
0582   END ;;
0583 DROP TRIGGER IF EXISTS `project_category_BEFORE_UPDATE`;;
0584 CREATE DEFINER=CURRENT_USER TRIGGER `project_category_BEFORE_UPDATE` BEFORE UPDATE ON `project_category` FOR EACH ROW
0585   BEGIN
0586     SET NEW.changed_at = NOW();
0587   END ;;
0588 DELIMITER ;
0589 
0590 SET FOREIGN_KEY_CHECKS=1;
0591 
0592 CREATE TABLE IF NOT EXISTS `project_gallery_picture` (
0593   `project_id` int(11) NOT NULL,
0594   `sequence` int(11) NOT NULL,
0595   `picture_src` varchar(255) NOT NULL,
0596   PRIMARY KEY (`project_id`,`sequence`)
0597 ) ENGINE=InnoDB;
0598 
0599 CREATE TABLE IF NOT EXISTS `project_package_type` (
0600   `project_id` int(11) NOT NULL,
0601   `file_id` int(11) NOT NULL,
0602   `package_type_id` int(11) NOT NULL,
0603   PRIMARY KEY (`project_id`,`file_id`),
0604   KEY `idx_type_id` (`package_type_id`)
0605 ) ENGINE=InnoDB;
0606 
0607 CREATE TABLE IF NOT EXISTS `project_rating` (
0608   `rating_id` int(11) NOT NULL AUTO_INCREMENT,
0609   `member_id` int(11) NOT NULL DEFAULT '0',
0610   `project_id` int(11) NOT NULL DEFAULT '0',
0611   `user_like` int(1) DEFAULT '0',
0612   `user_dislike` int(1) DEFAULT '0',
0613   `comment_id` int(11) DEFAULT '0' COMMENT 'review for rating',
0614   `rating_active` int(1) DEFAULT '1' COMMENT 'active = 1, deleted = 0',
0615   `source_id` int(1) DEFAULT '0',
0616   `source_pk` int(1) DEFAULT '0',
0617   `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
0618   PRIMARY KEY (`rating_id`),
0619   KEY `idx_project_id` (`project_id`)
0620 ) ENGINE=InnoDB;
0621 
0622 CREATE TABLE IF NOT EXISTS `project_updates` (
0623   `project_update_id` int(11) NOT NULL AUTO_INCREMENT,
0624   `project_id` int(11) NOT NULL DEFAULT '0',
0625   `member_id` int(11) NOT NULL DEFAULT '0',
0626   `public` int(1) NOT NULL DEFAULT '0',
0627   `title` varchar(200) DEFAULT NULL,
0628   `text` text,
0629   `created_at` datetime DEFAULT NULL,
0630   `changed_at` datetime DEFAULT NULL,
0631   `source_id` int(11) DEFAULT '0',
0632   `source_pk` int(11) DEFAULT NULL,
0633   PRIMARY KEY (`project_update_id`)
0634 ) ENGINE=MyISAM;
0635 
0636 CREATE TABLE IF NOT EXISTS `project_widget` (
0637   `widget_id` int(11) NOT NULL AUTO_INCREMENT,
0638   `uuid` varchar(255) DEFAULT NULL,
0639   `project_id` int(11) DEFAULT NULL,
0640   `config` text,
0641   PRIMARY KEY (`widget_id`),
0642   KEY `idxPROJECT` (`project_id`),
0643   KEY `idxUUID` (`uuid`)
0644 ) ENGINE=InnoDB;
0645 
0646 CREATE TABLE IF NOT EXISTS `project_widget_default` (
0647   `widget_id` int(11) NOT NULL AUTO_INCREMENT,
0648   `uuid` varchar(255) DEFAULT NULL,
0649   `project_id` int(11) DEFAULT NULL,
0650   `config` text,
0651   PRIMARY KEY (`widget_id`),
0652   KEY `idxPROJECT` (`project_id`),
0653   KEY `idxUuid` (`uuid`)
0654 ) ENGINE=InnoDB;
0655 TRUNCATE `project_widget_default`;
0656 ALTER TABLE `project_widget_default` DISABLE KEYS;
0657 INSERT INTO `project_widget_default` VALUES (1,'95b1a84890153c9852c4019778c27639',659,'{\"text\":{\"content\":\"I\'m currently raising money through Pling it. Click the Pling it button to help!\",\"headline\":\"Churches\",\"button\":\"Pling it!\"},\"amounts\":{\"donation\":\"10\",\"showDonationAmount\":true,\"current\":\"0\",\"goal\":\"\"},\"colors\":{\"widgetBg\":\"#2673B0\",\"widgetContent\":\"#ffffff\",\"headline\":\"#ffffff\",\"text\":\"#000000\",\"button\":\"#428bca\",\"buttonText\":\"#ffffff\"},\"showSupporters\":true,\"showComments\":true,\"logo\":\"grey\"}'),(2,'c5079dc902feb85c9570f4afda705079',683,'{\"text\":{\"content\":\"HELP!!!\",\"headline\":\"Ronald\'s Music\",\"button\":\"Pling it!\"},\"amounts\":{\"donation\":\"10\",\"showDonationAmount\":true,\"current\":\"10\",\"goal\":\"\"},\"colors\":{\"widgetBg\":\"#2e3e5e\",\"widgetContent\":\"#ff0000\",\"headline\":\"#ffffff\",\"text\":\"#000000\",\"button\":\"#428bca\",\"buttonText\":\"#ffffff\"},\"showSupporters\":true,\"showComments\":true,\"logo\":\"orange\"}'),(3,'e33d4e2932c29031a17d9a0587f72ab0',200,'{\"text\":{\"content\":\"I\'m currently raising money through Pling it. Click the Pling it button to help!\",\"headline\":\"Netrunner Enigma 2 (13.12) - 32bit RC\",\"button\":\"Pling it!\"},\"amounts\":{\"donation\":\"10\",\"showDonationAmount\":true,\"current\":\"23\",\"goal\":\"50\"},\"colors\":{\"widgetBg\":\"#2673B0\",\"widgetContent\":\"#ffffff\",\"headline\":\"#ffffff\",\"text\":\"#000000\",\"button\":\"#428bca\",\"buttonText\":\"#ffffff\"},\"showSupporters\":true,\"showComments\":true,\"logo\":\"grey\"}'),(4,'5cb3b4e7998d9ceef02622f648fa367a',42,'{\"text\":{\"content\":\"I\'m currently raising money through Pling it. Click the Pling it button to help!\",\"headline\":\"wums plattform\",\"button\":\"Pling it!\"},\"amounts\":{\"donation\":\"10\",\"showDonationAmount\":true,\"current\":\"21.75\",\"goal\":\"\"},\"colors\":{\"widgetBg\":\"#2673B0\",\"widgetContent\":\"#ffffff\",\"headline\":\"#ffffff\",\"text\":\"#000000\",\"button\":\"#428bca\",\"buttonText\":\"#ffffff\"},\"showSupporters\":true,\"showComments\":true,\"logo\":\"grey\"}'),(5,'d2bace2e8c3dea33ba776f5c5a20d313',83,'{\"text\":{\"content\":\"I\'m currently raising money through Pling it. Click the Pling it button to help!\",\"headline\":\"Makkes2\",\"button\":\"Pling it!\"},\"amounts\":{\"donation\":\"10\",\"showDonationAmount\":true,\"current\":\"0.75\",\"goal\":\"\"},\"colors\":{\"widgetBg\":\"#2673B0\",\"widgetContent\":\"#ffffff\",\"headline\":\"#ffffff\",\"text\":\"#000000\",\"button\":\"#428bca\",\"buttonText\":\"#ffffff\"},\"showSupporters\":true,\"showComments\":true,\"logo\":\"grey\"}'),(6,'5215736d456c2cafe71c61712f8e8b0e',204,'{\"text\":{\"content\":\"I\'m currently raising money through Pling it. Click the Pling it button to help!\",\"headline\":\"Video2\",\"button\":\"Pling it!\"},\"amounts\":{\"donation\":\"10\",\"showDonationAmount\":true,\"current\":\"0\",\"goal\":\"\"},\"colors\":{\"widgetBg\":\"#2673B0\",\"widgetContent\":\"#ffffff\",\"headline\":\"#ffffff\",\"text\":\"#000000\",\"button\":\"#428bca\",\"buttonText\":\"#ffffff\"},\"showSupporters\":true,\"showComments\":true,\"logo\":\"grey\"}'),(7,'4e557eb9f8fb030ec83f8404b21a7d63',727,'{\"text\":{\"content\":\"I\'m currently raising money through Pling it. Click the Pling it button to help!\",\"headline\":\"wert\",\"button\":\"Pling it!\"},\"amounts\":{\"donation\":\"10\",\"showDonationAmount\":true,\"current\":\"0\",\"goal\":\"5\"},\"colors\":{\"widgetBg\":\"#2673B0\",\"widgetContent\":\"#ffffff\",\"headline\":\"#ffffff\",\"text\":\"#000000\",\"button\":\"#428bca\",\"buttonText\":\"#ffffff\"},\"showSupporters\":true,\"showComments\":true,\"logo\":\"grey\"}'),(8,'b4b1ee0a037b90016a738781469cfa3a',703,'{\"text\":{\"content\":\"Discover more about my product on Pling.it.\",\"headline\":\"Wallpaper Test\",\"button\":\"Pling it!\"},\"amounts\":{\"donation\":\"10\",\"showDonationAmount\":true,\"current\":\"0\",\"goal\":\"\"},\"colors\":{\"widgetBg\":\"#2673B0\",\"widgetContent\":\"#ffffff\",\"headline\":\"#ffffff\",\"text\":\"#000000\",\"button\":\"#428bca\",\"buttonText\":\"#ffffff\"},\"showSupporters\":true,\"showComments\":true,\"logo\":\"grey\"}'),(9,'2e5aef93a4a6acdffafde1aa2f4b765e',723,'{\"text\":{\"content\":\"Discover more about my product on Pling.it.\",\"headline\":\"heinzi\",\"button\":\"Pling it!\"},\"amounts\":{\"donation\":\"10\",\"showDonationAmount\":true,\"current\":\"0\",\"goal\":\"2000\"},\"colors\":{\"widgetBg\":\"#2673B0\",\"widgetContent\":\"#ffffff\",\"headline\":\"#ffffff\",\"text\":\"#000000\",\"button\":\"#428bca\",\"buttonText\":\"#ffffff\"},\"showSupporters\":true,\"showComments\":true,\"logo\":\"icon\"}'),(10,'b6f2f8683a8d90f55f7498922de307ea',745,'{\"text\":{\"content\":\"Discover more about my product on Pling.it.\",\"headline\":\"sebas product\",\"button\":\"Pling it!\"},\"amounts\":{\"donation\":\"10\",\"showDonationAmount\":true,\"current\":\"0\",\"goal\":\"10000\"},\"colors\":{\"widgetBg\":\"#2673B0\",\"widgetContent\":\"#ffffff\",\"headline\":\"#ffffff\",\"text\":\"#000000\",\"button\":\"#428bca\",\"buttonText\":\"#ffffff\"},\"showSupporters\":true,\"showComments\":true,\"logo\":\"icon\"}'),(11,'8cff689cb9a96005c07127afa42d5359',1099990,'{\"text\":{\"content\":\"I\'m currently raising money through Pling it. Click the Pling it button to help!dsafds\",\"headline\":\"dsafdsafds\",\"button\":\"Pling it!\"},\"amounts\":{\"donation\":\"10\",\"showDonationAmount\":true,\"current\":\"0\",\"goal\":\"\"},\"colors\":{\"widgetBg\":\"#b0264f\",\"widgetContent\":\"#ffffff\",\"headline\":\"#ffffff\",\"text\":\"#000000\",\"button\":\"#428bca\",\"buttonText\":\"#785555\"},\"showSupporters\":true,\"showComments\":true,\"logo\":\"grey\"}'),(12,'0fbfdb7be17403e774a01a44bd12ab94',1176780,'{\"text\":{\"content\":\"I\'m currently raising money through opendesktop.org . Click the donate button to help!\",\"headline\":\"sheep\",\"button\":\"Pling it!\"},\"amounts\":{\"donation\":\"10\",\"showDonationAmount\":true,\"current\":\"0\",\"goal\":\"\"},\"colors\":{\"widgetBg\":\"#2673B0\",\"widgetContent\":\"#ffffff\",\"headline\":\"#ffffff\",\"text\":\"#000000\",\"button\":\"#428bca\",\"buttonText\":\"#ffffff\"},\"showSupporters\":true,\"showComments\":true,\"logo\":\"grey\"}'),(13,'4cdb50c0d931251e08dc920da2af93fe',1170226,'{\"text\":{\"content\":\"I\'m currently raising money through opendesktop.org . Click the donate button to help!\",\"headline\":\"KAtomic Snap\",\"button\":\"Pling it!\"},\"amounts\":{\"donation\":\"10\",\"showDonationAmount\":true,\"current\":\"0\",\"goal\":\"\"},\"colors\":{\"widgetBg\":\"#2673B0\",\"widgetContent\":\"#ffffff\",\"headline\":\"#ffffff\",\"text\":\"#000000\",\"button\":\"#428bca\",\"buttonText\":\"#ffffff\"},\"showSupporters\":true,\"showComments\":true,\"logo\":\"grey\"}');
0658 ALTER TABLE `project_widget_default` ENABLE KEYS;
0659 
0660 SET FOREIGN_KEY_CHECKS=0;
0661 CREATE TABLE IF NOT EXISTS `queue` (
0662   `queue_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
0663   `queue_name` varchar(100) NOT NULL,
0664   `timeout` smallint(5) unsigned NOT NULL DEFAULT '30',
0665   PRIMARY KEY (`queue_id`)
0666 ) ENGINE=InnoDB;
0667 ALTER TABLE `queue` DISABLE KEYS;
0668 TRUNCATE `queue`;
0669 INSERT INTO `queue` VALUES (1,'website_validate',30),(2,'search',30),(3,'ocs_jobs',30);
0670 ALTER TABLE `queue` ENABLE KEYS;
0671 
0672 CREATE TABLE IF NOT EXISTS `queue_message` (
0673   `message_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
0674   `queue_id` int(10) unsigned NOT NULL,
0675   `handle` char(32) DEFAULT NULL,
0676   `body` text NOT NULL,
0677   `md5` char(32) NOT NULL,
0678   `timeout` decimal(14,4) unsigned DEFAULT NULL,
0679   `created` int(10) unsigned NOT NULL,
0680   PRIMARY KEY (`message_id`),
0681   UNIQUE KEY `message_handle` (`handle`),
0682   KEY `message_queueid` (`queue_id`),
0683   CONSTRAINT `queue_message_ibfk_1` FOREIGN KEY (`queue_id`) REFERENCES `queue` (`queue_id`) ON DELETE CASCADE ON UPDATE CASCADE
0684 ) ENGINE=InnoDB;
0685 SET FOREIGN_KEY_CHECKS=1;
0686 
0687 CREATE TABLE IF NOT EXISTS `reports_comment` (
0688   `report_id` int(11) NOT NULL AUTO_INCREMENT,
0689   `project_id` int(11) NOT NULL,
0690   `comment_id` int(11) NOT NULL,
0691   `reported_by` int(11) NOT NULL,
0692   `is_deleted` int(1) DEFAULT NULL,
0693   `is_active` int(1) DEFAULT NULL,
0694   `created_at` datetime DEFAULT NULL,
0695   PRIMARY KEY (`report_id`),
0696   KEY `idxComment` (`comment_id`),
0697   KEY `idxMember` (`reported_by`)
0698 ) ENGINE=InnoDB;
0699 
0700 DELIMITER ;;
0701 DROP TRIGGER IF EXISTS `report_comment_created`;;
0702 CREATE DEFINER=CURRENT_USER TRIGGER `report_comment_created` BEFORE INSERT ON `reports_comment` FOR EACH ROW
0703   BEGIN
0704     IF NEW.created_at IS NULL THEN
0705       SET NEW.created_at = NOW();
0706     END IF;
0707   END ;;
0708 DELIMITER ;
0709 
0710 CREATE TABLE IF NOT EXISTS `reports_member` (
0711   `report_id` int(11) NOT NULL AUTO_INCREMENT,
0712   `member_id` int(11) NOT NULL,
0713   `reported_by` int(11) NOT NULL,
0714   `is_deleted` int(1) DEFAULT NULL,
0715   `created_at` datetime DEFAULT NULL,
0716   PRIMARY KEY (`report_id`),
0717   KEY `idxMemberId` (`member_id`),
0718   KEY `idxReportedBy` (`reported_by`)
0719 ) ENGINE=InnoDB;
0720 
0721 DELIMITER ;;
0722 DROP TRIGGER IF EXISTS `reports_member_created`;;
0723 CREATE DEFINER=CURRENT_USER TRIGGER `reports_member_created` BEFORE INSERT ON `reports_member` FOR EACH ROW
0724   BEGIN
0725     IF NEW.created_at IS NULL THEN
0726       SET NEW.created_at = NOW();
0727     END IF;
0728   END ;;
0729 DELIMITER ;
0730 
0731 CREATE TABLE IF NOT EXISTS `reports_project` (
0732   `report_id` int(11) NOT NULL AUTO_INCREMENT,
0733   `project_id` int(11) NOT NULL,
0734   `reported_by` int(11) NOT NULL,
0735   `is_deleted` int(1) NOT NULL DEFAULT '0',
0736   `created_at` datetime DEFAULT NULL,
0737   PRIMARY KEY (`report_id`),
0738   KEY `idxReport` (`project_id`,`reported_by`,`is_deleted`,`created_at`)
0739 ) ENGINE=InnoDB;
0740 
0741 DELIMITER ;;
0742 DROP TRIGGER IF EXISTS `report_project_created`;;
0743 CREATE DEFINER=CURRENT_USER TRIGGER `report_project_created` BEFORE INSERT ON `reports_project` FOR EACH ROW
0744   BEGIN
0745     IF NEW.created_at IS NULL THEN
0746       SET NEW.created_at = NOW();
0747     END IF;
0748   END ;;
0749 DELIMITER ;
0750 
0751 CREATE TABLE IF NOT EXISTS `session` (
0752   `session_id` int(11) NOT NULL AUTO_INCREMENT,
0753   `member_id` int(11) NOT NULL,
0754   `remember_me_id` varchar(255) NOT NULL,
0755   `expiry` datetime NOT NULL,
0756   `created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
0757   `changed` timestamp NULL DEFAULT NULL,
0758   PRIMARY KEY (`session_id`),
0759   KEY `idx_remember` (`member_id`,`remember_me_id`,`expiry`)
0760 ) ENGINE=InnoDB;
0761 
0762 CREATE TABLE IF NOT EXISTS `support` (
0763   `id` int(11) NOT NULL AUTO_INCREMENT,
0764   `member_id` int(11) NOT NULL COMMENT 'Supporter',
0765   `status_id` int(11) DEFAULT '0' COMMENT 'Stati der donation: 0 = inactive, 1 = active (donated), 2 = payed successfull, 99 = deleted',
0766   `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Creation-time',
0767   `donation_time` timestamp NULL DEFAULT NULL COMMENT 'When was a project plinged?',
0768   `active_time` timestamp NULL DEFAULT NULL COMMENT 'When did paypal say, that this donation was payed successfull',
0769   `delete_time` timestamp NULL DEFAULT NULL,
0770   `amount` double(10,2) DEFAULT '0.00' COMMENT 'Amount of money',
0771   `comment` varchar(140) DEFAULT NULL COMMENT 'Comment from the supporter',
0772   `payment_provider` varchar(45) DEFAULT NULL,
0773   `payment_reference_key` varchar(255) DEFAULT NULL COMMENT 'uniquely identifies the request',
0774   `payment_transaction_id` varchar(255) DEFAULT NULL COMMENT 'uniquely identify caller (developer, facilliator, marketplace) transaction',
0775   `payment_raw_message` varchar(2000) DEFAULT NULL COMMENT 'the raw text message ',
0776   `payment_raw_error` varchar(2000) DEFAULT NULL,
0777   `payment_status` varchar(45) DEFAULT NULL,
0778   PRIMARY KEY (`id`),
0779   KEY `status_id` (`status_id`),
0780   KEY `member_id` (`member_id`),
0781   KEY `DONATION_IX_01` (`status_id`,`member_id`,`active_time`,`amount`)
0782 ) ENGINE=InnoDB;
0783 
0784 SET FOREIGN_KEY_CHECKS=0;
0785 CREATE TABLE IF NOT EXISTS `tag` (
0786   `tag_id` int(11) NOT NULL AUTO_INCREMENT,
0787   `tag_name` varchar(45) NOT NULL,
0788   PRIMARY KEY (`tag_id`),
0789   UNIQUE KEY `idx_name` (`tag_name`)
0790 ) ENGINE=InnoDB;
0791 ALTER TABLE `tag` DISABLE KEYS ;
0792 TRUNCATE `tag`;
0793 INSERT INTO `tag` VALUES (13,''),(1,'1024x768'),(3,'1600x900'),(2,'800x600'),(9,'abstract'),(7,'background'),(8,'building'),(12,'Debian'),(6,'flowers'),(5,'gnome'),(4,'kde'),(10,'Linux'),(11,'Windows');
0794 ALTER TABLE `tag` ENABLE KEYS ;
0795 
0796 CREATE TABLE IF NOT EXISTS `tag_group` (
0797   `group_id` int(11) NOT NULL AUTO_INCREMENT,
0798   `group_name` varchar(45) NOT NULL,
0799   PRIMARY KEY (`group_id`)
0800 ) ENGINE=InnoDB;
0801 ALTER TABLE `tag_group` DISABLE KEYS;
0802 TRUNCATE `tag_group`;
0803 INSERT INTO `tag_group` VALUES (1,'resolution'),(2,'badge'),(3,'usertag'),(4,'OS');
0804 ALTER TABLE `tag_group` ENABLE KEYS ;
0805 
0806 CREATE TABLE IF NOT EXISTS `tag_group_item` (
0807   `tag_group_item_id` int(11) NOT NULL AUTO_INCREMENT,
0808   `tag_group_id` int(11) NOT NULL,
0809   `tag_id` int(11) NOT NULL,
0810   PRIMARY KEY (`tag_group_item_id`),
0811   KEY `tag_group_idx` (`tag_group_id`),
0812   KEY `tag_idx` (`tag_id`),
0813   CONSTRAINT `tag` FOREIGN KEY (`tag_id`) REFERENCES `tag` (`tag_id`) ON DELETE CASCADE ON UPDATE NO ACTION,
0814   CONSTRAINT `tag_group` FOREIGN KEY (`tag_group_id`) REFERENCES `tag_group` (`group_id`) ON DELETE CASCADE ON UPDATE NO ACTION
0815 ) ENGINE=InnoDB;
0816 ALTER TABLE `tag_group_item` DISABLE KEYS ;
0817 TRUNCATE `tag_group_item`;
0818 INSERT INTO `tag_group_item` VALUES (1,1,1),(2,1,2),(3,1,3),(4,2,4),(5,2,5),(6,3,6),(7,3,7),(8,3,8),(9,3,9),(10,4,10),(11,4,11),(12,4,12);
0819 ALTER TABLE `tag_group_item` ENABLE KEYS ;
0820 
0821 SET FOREIGN_KEY_CHECKS=1;
0822 
0823 CREATE TABLE IF NOT EXISTS `tag_object` (
0824   `tag_item_id` int(11) NOT NULL AUTO_INCREMENT,
0825   `tag_id` int(11) NOT NULL,
0826   `tag_type_id` int(11) NOT NULL,
0827   `tag_object_id` int(11) NOT NULL,
0828   `tag_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
0829   `tag_changed` datetime DEFAULT NULL,
0830   PRIMARY KEY (`tag_item_id`),
0831   UNIQUE KEY `tags_unique` (`tag_id`,`tag_type_id`,`tag_object_id`),
0832   KEY `tags_idx` (`tag_id`),
0833   KEY `types_idx` (`tag_type_id`)
0834 ) ENGINE=InnoDB;
0835 DELIMITER ;;
0836 DROP TRIGGER IF EXISTS `tag_object_BEFORE_INSERT`;;
0837 CREATE DEFINER=CURRENT_USER TRIGGER `tag_object_BEFORE_INSERT` BEFORE INSERT ON `tag_object` FOR EACH ROW
0838   BEGIN
0839     IF NEW.tag_changed IS NULL THEN
0840       SET NEW.tag_changed = NOW();
0841     END IF;
0842   END ;;
0843 DELIMITER ;
0844 
0845 CREATE TABLE IF NOT EXISTS `tag_type` (
0846   `tag_type_id` int(11) NOT NULL AUTO_INCREMENT,
0847   `tag_type_name` varchar(45) NOT NULL,
0848   PRIMARY KEY (`tag_type_id`)
0849 ) ENGINE=InnoDB;
0850 ALTER TABLE `tag_type` DISABLE KEYS ;
0851 TRUNCATE `tag_type`;
0852 INSERT INTO `tag_type` VALUES (1,'project'),(2,'member'),(3,'file'),(4,'download'),(5,'image'),(6,'video'),(7,'comment'),(8,'activity');
0853 ALTER TABLE `tag_type` ENABLE KEYS ;
0854 
0855 DELIMITER $$
0856 DROP FUNCTION IF EXISTS `laplace_score`$$
0857 CREATE DEFINER=CURRENT_USER FUNCTION `laplace_score`(upvotes INT, downvotes INT) RETURNS int(11)
0858 DETERMINISTIC
0859   BEGIN
0860     DECLARE score INT(10);
0861     SET score = (round(((upvotes + 6) / ((upvotes + downvotes) + 12)),2) * 100);
0862     RETURN score;
0863   END$$
0864 DELIMITER ;
0865 
0866 DELIMITER $$
0867 DROP PROCEDURE IF EXISTS `generate_stat_views_today`$$
0868 CREATE DEFINER=CURRENT_USER PROCEDURE `generate_stat_views_today`()
0869   BEGIN
0870 
0871     DROP TABLE IF EXISTS `temp_stat_views_today`;
0872 
0873     CREATE TABLE `temp_stat_views_today` (
0874       `id`            INT      NOT NULL AUTO_INCREMENT,
0875       `project_id`    INT(11)  NOT NULL,
0876       `count_views`   INT(11)  NULL     DEFAULT 0,
0877       `count_visitor` INT(11)  NULL     DEFAULT 0,
0878       `last_view`     DATETIME NULL     DEFAULT NULL,
0879       PRIMARY KEY (`id`),
0880       INDEX `idx_project` (`project_id` ASC)
0881     )
0882       ENGINE = MyISAM
0883       AS
0884         SELECT
0885           project_id,
0886           COUNT(*)                               AS count_views,
0887           COUNT(DISTINCT `stat_page_views`.`ip`) AS `count_visitor`,
0888           MAX(`stat_page_views`.`created_at`)    AS `last_view`
0889         FROM stat_page_views
0890         WHERE (stat_page_views.`created_at`
0891         BETWEEN DATE_FORMAT(NOW(), '%Y-%m-%d 00:00') AND DATE_FORMAT(NOW(), '%Y-%m-%d 23:59')
0892         )
0893         GROUP BY project_id;
0894 
0895     IF EXISTS(SELECT table_name
0896               FROM INFORMATION_SCHEMA.TABLES
0897               WHERE table_schema = DATABASE()
0898                     AND table_name = 'stat_page_views_today_mv')
0899 
0900     THEN
0901 
0902       ALTER TABLE `stat_page_views_today_mv`
0903       RENAME TO `old_stat_views_today_mv`;
0904 
0905     END IF;
0906 
0907     ALTER TABLE `temp_stat_views_today`
0908     RENAME TO `stat_page_views_today_mv`;
0909 
0910     DROP TABLE IF EXISTS `old_stat_views_today_mv`;
0911 
0912   END$$
0913 DELIMITER ;
0914 
0915 DELIMITER $$
0916 DROP PROCEDURE IF EXISTS `generate_stat_project`$$
0917 CREATE DEFINER=CURRENT_USER PROCEDURE `generate_stat_project`()
0918   BEGIN
0919     DROP TABLE IF EXISTS tmp_reported_projects;
0920     CREATE TEMPORARY TABLE tmp_reported_projects
0921     (PRIMARY KEY `primary` (project_id) )
0922       AS
0923         SELECT
0924           `reports_project`.`project_id` AS `project_id`,
0925           COUNT(`reports_project`.`project_id`) AS `amount_reports`,
0926           MAX(`reports_project`.`created_at`) AS `latest_report`
0927         FROM
0928           `reports_project`
0929         WHERE
0930           (`reports_project`.`is_deleted` = 0)
0931         GROUP BY `reports_project`.`project_id`
0932     ;
0933 
0934     DROP TABLE IF EXISTS tmp_project_package_types;
0935     CREATE TEMPORARY TABLE tmp_project_package_types
0936     (PRIMARY KEY `primary` (project_id))
0937       ENGINE MyISAM
0938       AS
0939         SELECT
0940           project_id,
0941           GROUP_CONCAT(DISTINCT project_package_type.package_type_id) AS package_type_id_list,
0942           GROUP_CONCAT(DISTINCT package_types.`name`) AS `package_name_list`
0943         FROM
0944           project_package_type
0945           JOIN
0946           package_types ON project_package_type.package_type_id = package_types.package_type_id
0947         WHERE
0948           package_types.is_active = 1
0949         GROUP BY project_id
0950     ;
0951 
0952     DROP TABLE IF EXISTS tmp_project_tags;
0953     CREATE TEMPORARY TABLE tmp_project_tags
0954     (PRIMARY KEY `primary` (tag_project_id))
0955       ENGINE MyISAM
0956       AS
0957         SELECT GROUP_CONCAT(tag.tag_name) AS tag_names, tgo.tag_object_id AS tag_project_id
0958         FROM tag_object AS tgo
0959           JOIN tag ON tag.tag_id = tgo.tag_id
0960         WHERE tag_type_id = 1
0961         GROUP BY tgo.tag_object_id
0962         ORDER BY tgo.tag_object_id;
0963 
0964     DROP TABLE IF EXISTS tmp_stat_projects;
0965     CREATE TABLE tmp_stat_projects
0966     (PRIMARY KEY `primary` (`project_id`), INDEX `idx_cat` (`project_category_id`))
0967       ENGINE MyISAM
0968       AS
0969         SELECT
0970           `project`.`project_id` AS `project_id`,
0971           `project`.`member_id` AS `member_id`,
0972           `project`.`content_type` AS `content_type`,
0973           `project`.`project_category_id` AS `project_category_id`,
0974           `project`.`hive_category_id` AS `hive_category_id`,
0975           `project`.`status` AS `status`,
0976           `project`.`uuid` AS `uuid`,
0977           `project`.`pid` AS `pid`,
0978           `project`.`type_id` AS `type_id`,
0979           `project`.`title` AS `title`,
0980           `project`.`description` AS `description`,
0981           `project`.`version` AS `version`,
0982           `project`.`image_big` AS `image_big`,
0983           `project`.`image_small` AS `image_small`,
0984           `project`.`start_date` AS `start_date`,
0985           `project`.`content_url` AS `content_url`,
0986           `project`.`created_at` AS `created_at`,
0987           `project`.`changed_at` AS `changed_at`,
0988           `project`.`deleted_at` AS `deleted_at`,
0989           `project`.`creator_id` AS `creator_id`,
0990           `project`.`facebook_code` AS `facebook_code`,
0991           `project`.`github_code` AS `github_code`,
0992           `project`.`twitter_code` AS `twitter_code`,
0993           `project`.`google_code` AS `google_code`,
0994           `project`.`link_1` AS `link_1`,
0995           `project`.`embed_code` AS `embed_code`,
0996           `project`.`ppload_collection_id` AS `ppload_collection_id`,
0997           `project`.`validated` AS `validated`,
0998           `project`.`validated_at` AS `validated_at`,
0999           `project`.`featured` AS `featured`,
1000           `project`.`approved` AS `approved`,
1001           `project`.`amount` AS `amount`,
1002           `project`.`amount_period` AS `amount_period`,
1003           `project`.`claimable` AS `claimable`,
1004           `project`.`claimed_by_member` AS `claimed_by_member`,
1005           `project`.`count_likes` AS `count_likes`,
1006           `project`.`count_dislikes` AS `count_dislikes`,
1007           `project`.`count_comments` AS `count_comments`,
1008           `project`.`count_downloads_hive` AS `count_downloads_hive`,
1009           `project`.`source_id` AS `source_id`,
1010           `project`.`source_pk` AS `source_pk`,
1011           `project`.`source_type` AS `source_type`,
1012           `project`.`validated` AS `project_validated`,
1013           `project`.`uuid` AS `project_uuid`,
1014           `project`.`status` AS `project_status`,
1015           `project`.`created_at` AS `project_created_at`,
1016           `project`.`changed_at` AS `project_changed_at`,
1017           laplace_score(`project`.`count_likes`, `project`.`count_dislikes`) AS `laplace_score`,
1018           `member`.`type` AS `member_type`,
1019           `member`.`member_id` AS `project_member_id`,
1020           `member`.`username` AS `username`,
1021           `member`.`profile_image_url` AS `profile_image_url`,
1022           `member`.`city` AS `city`,
1023           `member`.`country` AS `country`,
1024           `member`.`created_at` AS `member_created_at`,
1025           `member`.`paypal_mail` AS `paypal_mail`,
1026           `project_category`.`title` AS `cat_title`,
1027           `project_category`.`xdg_type` AS `cat_xdg_type`,
1028           `project_category`.`name_legacy` AS `cat_name_legacy`,
1029           `project_category`.`show_description` AS `cat_show_description`,
1030           `stat_plings`.`amount_received` AS `amount_received`,
1031           `stat_plings`.`count_plings` AS `count_plings`,
1032           `stat_plings`.`count_plingers` AS `count_plingers`,
1033           `stat_plings`.`latest_pling` AS `latest_pling`,
1034           `trp`.`amount_reports` AS `amount_reports`,
1035           `tppt`.`package_type_id_list` as `package_types`,
1036           `tppt`.`package_name_list` as `package_names`,
1037           `t`.`tag_names` AS `tags`
1038         FROM
1039           `project`
1040           JOIN `member` ON `member`.`member_id` = `project`.`member_id`
1041           JOIN `project_category` ON `project`.`project_category_id` = `project_category`.`project_category_id`
1042           LEFT JOIN `stat_plings` ON `stat_plings`.`project_id` = `project`.`project_id`
1043           LEFT JOIN `tmp_reported_projects` AS trp ON `trp`.`project_id` = `project`.`project_id`
1044           LEFT JOIN `tmp_project_package_types` AS tppt ON tppt.project_id = `project`.project_id
1045           LEFT JOIN `tmp_project_tags` AS t ON t.`tag_project_id` = project.`project_id`
1046         WHERE
1047           `member`.`is_deleted` = 0
1048           AND `member`.`is_active` = 1
1049           AND `project`.`type_id` = 1
1050           AND `project`.`status` = 100
1051           AND `project_category`.`is_active` = 1
1052     ;
1053 
1054     RENAME TABLE stat_projects TO old_stat_projects, tmp_stat_projects TO stat_projects;
1055 
1056     DROP TABLE IF EXISTS old_stat_projects;
1057   END$$
1058 DELIMITER ;
1059 
1060 DELIMITER $$
1061 DROP PROCEDURE IF EXISTS `generate_stat_cat_tree`$$
1062 CREATE DEFINER=CURRENT_USER PROCEDURE `generate_stat_cat_tree`()
1063   BEGIN
1064 
1065     DROP TABLE IF EXISTS tmp_stat_cat_tree;
1066     CREATE TABLE tmp_stat_cat_tree
1067     (
1068       `project_category_id` int(11) NOT NULL,
1069       `lft` int(11) NOT NULL,
1070       `rgt` int(11) NOT NULL,
1071       `title` varchar(100) NOT NULL,
1072       `name_legacy` varchar(50) NULL,
1073       `is_active` int(1),
1074       `orderPos` int(11) NULL,
1075       `depth` int(11) NOT NULL,
1076       `ancestor_id_path` varchar(100),
1077       `ancestor_path` varchar(256),
1078       `ancestor_path_legacy` varchar(256),
1079       PRIMARY KEY `primary` (project_category_id, lft, rgt)
1080     )
1081       ENGINE Memory
1082       AS
1083         SELECT
1084           pc.project_category_id,
1085           pc.lft,
1086           pc.rgt,
1087           pc.title,
1088           pc.name_legacy,
1089           pc.is_active,
1090           pc.orderPos,
1091           count(pc.lft) - 1                                        AS depth,
1092           GROUP_CONCAT(pc2.project_category_id ORDER BY pc2.lft)   AS ancestor_id_path,
1093           GROUP_CONCAT(pc2.title ORDER BY pc2.lft SEPARATOR ' | ') AS ancestor_path,
1094           GROUP_CONCAT(IF(LENGTH(TRIM(pc2.name_legacy))>0,pc2.name_legacy,pc2.title) ORDER BY pc2.lft SEPARATOR ' | ') AS ancestor_path_legacy
1095         FROM project_category AS pc, project_category AS pc2
1096         WHERE (pc.lft BETWEEN pc2.lft AND pc2.rgt) AND pc.is_active = 1 and pc2.is_active = 1
1097         GROUP BY pc.lft -- HAVING depth >= 1
1098         ORDER BY pc.lft, pc.orderPos
1099     ;
1100 
1101     IF EXISTS(SELECT table_name
1102               FROM INFORMATION_SCHEMA.TABLES
1103               WHERE table_schema = DATABASE()
1104                     AND table_name = 'stat_cat_tree')
1105 
1106     THEN
1107 
1108       RENAME TABLE stat_cat_tree TO old_stat_cat_tree, tmp_stat_cat_tree TO stat_cat_tree;
1109 
1110     ELSE
1111 
1112       RENAME TABLE tmp_stat_cat_tree TO stat_cat_tree;
1113 
1114     END IF;
1115 
1116 
1117     DROP TABLE IF EXISTS old_stat_cat_tree;
1118 
1119   END$$
1120 DELIMITER ;
1121 
1122 DELIMITER $$
1123 DROP PROCEDURE IF EXISTS `generate_stat_cat_prod_count`$$
1124 CREATE DEFINER=CURRENT_USER PROCEDURE `generate_stat_cat_prod_count`()
1125   BEGIN
1126 
1127     DROP TABLE IF EXISTS tmp_stat_cat_prod_count;
1128     CREATE TABLE tmp_stat_cat_prod_count
1129     (
1130       `project_category_id` int(11) NOT NULL,
1131       `package_type_id` int(11) NULL,
1132       `count_product` int(11) NULL,
1133       INDEX `idx_package` (`project_category_id`,`package_type_id`)
1134     )
1135       ENGINE Memory
1136       AS
1137         SELECT
1138           sct2.project_category_id,
1139           NULL as package_type_id,
1140           count(distinct p.project_id) as count_product
1141         FROM stat_cat_tree as sct1
1142           JOIN stat_cat_tree as sct2 ON sct1.lft between sct2.lft AND sct2.rgt
1143           LEFT JOIN stat_projects as p ON p.project_category_id = sct1.project_category_id AND p.amount_reports is null
1144         GROUP BY sct2.project_category_id
1145 
1146         UNION
1147 
1148         SELECT
1149           sct2.project_category_id,
1150           ppt.package_type_id,
1151           count(distinct p.project_id) as count_product
1152         FROM stat_cat_tree as sct1
1153           JOIN stat_cat_tree as sct2 ON sct1.lft between sct2.lft AND sct2.rgt
1154           JOIN stat_projects as p ON p.project_category_id = sct1.project_category_id AND p.amount_reports is null
1155           JOIN project_package_type AS ppt ON ppt.project_id = p.project_id
1156         GROUP BY sct2.lft, ppt.package_type_id
1157     ;
1158 
1159     IF EXISTS(SELECT table_name
1160               FROM INFORMATION_SCHEMA.TABLES
1161               WHERE table_schema = DATABASE()
1162                     AND table_name = 'stat_cat_prod_count')
1163 
1164     THEN
1165 
1166       RENAME TABLE stat_cat_prod_count TO old_stat_cat_prod_count, tmp_stat_cat_prod_count TO stat_cat_prod_count;
1167 
1168     ELSE
1169 
1170       RENAME TABLE tmp_stat_cat_prod_count TO stat_cat_prod_count;
1171 
1172     END IF;
1173 
1174 
1175     DROP TABLE IF EXISTS old_stat_cat_prod_count;
1176 
1177   END$$
1178 DELIMITER ;
1179 
1180 DELIMITER $$
1181 DROP PROCEDURE IF EXISTS `create_stat_ranking_categroy`$$
1182 CREATE DEFINER=CURRENT_USER PROCEDURE `create_stat_ranking_categroy`(IN `project_category_id` INT)
1183   BEGIN
1184 
1185 
1186     IF(project_category_id = 0 || project_category_id IS NULL) THEN
1187 
1188       #ALL
1189       DELETE FROM pling.stat_ranking_category WHERE project_category_id = 0;
1190 
1191       SET @i=0;
1192       insert into stat_ranking_category (
1193         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
1194         FROM pling.project p
1195         WHERE p.status = 100
1196         ORDER BY (round(((p.count_likes + 6) / ((p.count_likes + p.count_dislikes) + 12)),2) * 100) DESC
1197       );
1198     ELSE
1199       #CATEGORY
1200       DELETE FROM pling.stat_ranking_category WHERE project_category_id = project_category_id;
1201 
1202       SET @i=0;
1203       insert into stat_ranking_category (
1204         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
1205         FROM pling.project p
1206         WHERE p.status = 100
1207               AND p.project_category_id = project_category_id
1208         ORDER BY (round(((p.count_likes + 6) / ((p.count_likes + p.count_dislikes) + 12)),2) * 100) DESC
1209       );
1210 
1211     END IF;
1212 
1213   END$$
1214 DELIMITER ;