File indexing completed on 2024-05-19 05:58:39
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’t work, you can copy and paste the following link to your browser:<br /><br />%verificationlink% </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><%sender%> 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’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 can use your button and receive loads of plings or love and pling other products, please klick the link below to verify your email address.</p>\r\n<p><br />If the link doesn’t work, you can copy and paste the following link to your browser:<br /><br />%verificationlinktext% </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><%sender%> 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’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% </p>\r <p> </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 <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’t work, you can copy and paste the following link to your browser:<br /><br />%verificationlink% </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 ;