J3BDmNns

1/*
2SQLyog Ultimate v12.2.6 (64 bit)
3MySQL - 5.5.60-MariaDB : Database - groupify
4*********************************************************************
5*/
6
7
8/*!40101 SET NAMES utf8 */;
9
10/*!40101 SET SQL_MODE=''*/;
11
12/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
13/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
14/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
15/*Table structure for table `fb_comment` */
16
17DROP TABLE IF EXISTS `fb_comment`;
18
19CREATE TABLE `fb_comment` (
20  `fbCommentId` int(10) unsigned NOT NULL AUTO_INCREMENT,
21  `fbUserId` int(10) unsigned NOT NULL,
22  `fbPostId` int(10) unsigned NOT NULL,
23  `text` text COLLATE utf8_bin,
24  `id` varchar(200) COLLATE utf8_bin NOT NULL,
25  PRIMARY KEY (`fbCommentId`),
26  UNIQUE KEY `user2post` (`fbUserId`,`fbPostId`),
27  UNIQUE KEY `uniqueCommentId` (`id`),
28  KEY `fbPostId` (`fbPostId`),
29  CONSTRAINT `fb_comment_ibfk_1` FOREIGN KEY (`fbUserId`) REFERENCES `fb_user` (`fbUserId`) ON DELETE CASCADE ON UPDATE CASCADE,
30  CONSTRAINT `fb_comment_ibfk_2` FOREIGN KEY (`fbPostId`) REFERENCES `fb_post` (`fbPostId`) ON DELETE CASCADE ON UPDATE CASCADE
31) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
32
33/*Table structure for table `fb_group` */
34
35DROP TABLE IF EXISTS `fb_group`;
36
37CREATE TABLE `fb_group` (
38  `fbGroupId` int(10) unsigned NOT NULL AUTO_INCREMENT,
39  `userId` int(10) unsigned NOT NULL,
40  `fbUrl` varchar(200) COLLATE utf8_bin NOT NULL,
41  `name` varchar(200) COLLATE utf8_bin NOT NULL,
42  `updatedTimestamp` timestamp NULL DEFAULT NULL,
43  `addingTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
44  `fbPostCount` smallint(5) unsigned DEFAULT NULL,
45  `fbUserCount` smallint(5) unsigned DEFAULT NULL,
46  PRIMARY KEY (`fbGroupId`),
47  UNIQUE KEY `userId` (`userId`,`fbUrl`),
48  CONSTRAINT `fb_group_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`userId`) ON DELETE CASCADE ON UPDATE CASCADE
49) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
50
51/*Table structure for table `fb_like` */
52
53DROP TABLE IF EXISTS `fb_like`;
54
55CREATE TABLE `fb_like` (
56  `fbLikeId` int(10) unsigned NOT NULL AUTO_INCREMENT,
57  `fbUserId` int(10) unsigned NOT NULL,
58  `fbPostId` int(10) unsigned NOT NULL,
59  PRIMARY KEY (`fbLikeId`),
60  UNIQUE KEY `user2post` (`fbUserId`,`fbPostId`),
61  KEY `fbPostId` (`fbPostId`),
62  CONSTRAINT `fb_like_ibfk_2` FOREIGN KEY (`fbUserId`) REFERENCES `fb_user` (`fbUserId`) ON DELETE CASCADE ON UPDATE CASCADE,
63  CONSTRAINT `fb_like_ibfk_1` FOREIGN KEY (`fbPostId`) REFERENCES `fb_post` (`fbPostId`) ON DELETE CASCADE ON UPDATE CASCADE
64) ENGINE=InnoDB AUTO_INCREMENT=2721 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
65
66/*Table structure for table `fb_post` */
67
68DROP TABLE IF EXISTS `fb_post`;
69
70CREATE TABLE `fb_post` (
71  `fbPostId` int(10) unsigned NOT NULL AUTO_INCREMENT,
72  `fbGroupId` int(10) unsigned NOT NULL,
73  `id` varchar(200) COLLATE utf8_bin NOT NULL,
74  `text` text COLLATE utf8_bin,
75  `likeCount` smallint(5) unsigned NOT NULL,
76  `shareCount` smallint(5) unsigned NOT NULL,
77  `commentCount` smallint(5) unsigned NOT NULL,
78  `updatedTimestamp` timestamp NULL DEFAULT NULL,
79  `addingTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
80  `postTimestamp` timestamp NULL DEFAULT NULL,
81  `payload` text COLLATE utf8_bin,
82  PRIMARY KEY (`fbPostId`),
83  UNIQUE KEY `post2group` (`id`,`fbGroupId`),
84  KEY `fbGroupId` (`fbGroupId`),
85  CONSTRAINT `fb_post_ibfk_1` FOREIGN KEY (`fbGroupId`) REFERENCES `fb_group` (`fbGroupId`) ON DELETE CASCADE ON UPDATE CASCADE
86) ENGINE=InnoDB AUTO_INCREMENT=15159 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
87
88/*Table structure for table `fb_share` */
89
90DROP TABLE IF EXISTS `fb_share`;
91
92CREATE TABLE `fb_share` (
93  `fbShareId` int(10) unsigned NOT NULL AUTO_INCREMENT,
94  `fbUserId` int(10) unsigned NOT NULL,
95  `fbPostId` int(10) unsigned NOT NULL,
96  PRIMARY KEY (`fbShareId`),
97  UNIQUE KEY `shareUser2Post` (`fbUserId`,`fbPostId`),
98  KEY `fbPostId` (`fbPostId`),
99  CONSTRAINT `fb_share_ibfk_2` FOREIGN KEY (`fbPostId`) REFERENCES `fb_post` (`fbPostId`) ON DELETE CASCADE ON UPDATE CASCADE,
100  CONSTRAINT `fb_share_ibfk_1` FOREIGN KEY (`fbUserId`) REFERENCES `fb_user` (`fbUserId`) ON DELETE CASCADE ON UPDATE CASCADE
101) ENGINE=InnoDB AUTO_INCREMENT=209 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
102
103/*Table structure for table `fb_user` */
104
105DROP TABLE IF EXISTS `fb_user`;
106
107CREATE TABLE `fb_user` (
108  `fbUserId` int(10) unsigned NOT NULL AUTO_INCREMENT,
109  `fbGroupId` int(10) unsigned NOT NULL,
110  `id` varchar(200) COLLATE utf8_bin NOT NULL,
111  `image` varchar(1000) COLLATE utf8_bin NOT NULL,
112  `name` varchar(200) COLLATE utf8_bin NOT NULL,
113  `url` varchar(1000) COLLATE utf8_bin NOT NULL,
114  `addingTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
115  `fbLikeCount` smallint(5) unsigned DEFAULT NULL,
116  `fbCommentCount` smallint(5) unsigned DEFAULT NULL,
117  `fbShareCount` smallint(5) unsigned DEFAULT NULL,
118  `updatedTimestamp` timestamp NULL DEFAULT NULL,
119  PRIMARY KEY (`fbUserId`),
120  UNIQUE KEY `userInGroup` (`fbGroupId`,`id`),
121  KEY `id` (`id`),
122  CONSTRAINT `fb_user_ibfk_1` FOREIGN KEY (`fbGroupId`) REFERENCES `fb_group` (`fbGroupId`) ON DELETE CASCADE ON UPDATE CASCADE
123) ENGINE=InnoDB AUTO_INCREMENT=14796 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
124
125/*Table structure for table `invite` */
126
127DROP TABLE IF EXISTS `invite`;
128
129CREATE TABLE `invite` (
130  `inviteId` int(10) unsigned NOT NULL AUTO_INCREMENT,
131  `role` enum('user','userExt') COLLATE utf8_bin NOT NULL,
132  `email` varchar(200) COLLATE utf8_bin NOT NULL,
133  `token` char(32) COLLATE utf8_bin NOT NULL,
134  `isUsed` tinyint(1) unsigned NOT NULL,
135  `addingTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
136  PRIMARY KEY (`inviteId`)
137) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
138
139/*Table structure for table `log` */
140
141DROP TABLE IF EXISTS `log`;
142
143CREATE TABLE `log` (
144  `logId` int(10) unsigned NOT NULL AUTO_INCREMENT,
145  `message` text COLLATE utf8_bin NOT NULL,
146  `priorityName` char(20) COLLATE utf8_bin NOT NULL,
147  `scope` char(200) COLLATE utf8_bin DEFAULT NULL,
148  `userAgent` char(200) COLLATE utf8_bin DEFAULT NULL,
149  `version` char(20) COLLATE utf8_bin DEFAULT NULL,
150  `env` char(20) COLLATE utf8_bin DEFAULT NULL,
151  `extra` text COLLATE utf8_bin,
152  `addingTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
153  PRIMARY KEY (`logId`)
154) ENGINE=MyISAM AUTO_INCREMENT=28 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
155
156/*Table structure for table `mail_message` */
157
158DROP TABLE IF EXISTS `mail_message`;
159
160CREATE TABLE `mail_message` (
161  `mailMessageId` int(10) unsigned NOT NULL AUTO_INCREMENT,
162  `ident` varchar(200) COLLATE utf8_bin NOT NULL,
163  `subject` varchar(200) COLLATE utf8_bin NOT NULL,
164  `body` text COLLATE utf8_bin NOT NULL,
165  `signature` text COLLATE utf8_bin NOT NULL,
166  PRIMARY KEY (`mailMessageId`),
167  KEY `ident` (`ident`)
168) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
169
170/*Table structure for table `user` */
171
172DROP TABLE IF EXISTS `user`;
173
174CREATE TABLE `user` (
175  `userId` int(10) unsigned NOT NULL AUTO_INCREMENT,
176  `role` enum('user','admin','userExt') COLLATE utf8_bin NOT NULL,
177  `email` varchar(200) COLLATE utf8_bin NOT NULL,
178  `password` varchar(200) COLLATE utf8_bin NOT NULL,
179  `name` varchar(50) COLLATE utf8_bin NOT NULL,
180  `addingTimestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
181  `isEmailConfirmed` tinyint(1) unsigned NOT NULL,
182  PRIMARY KEY (`userId`),
183  UNIQUE KEY `email` (`email`)
184) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
185
186/* Trigger structure for table `fb_comment` */
187
188DELIMITER $$
189
190/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `fb_comment_after_insert` */$$
191
192/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `fb_comment_after_insert` AFTER INSERT ON `fb_comment` FOR EACH ROW BEGIN
193UPDATE `fb_user` SET `fbCommentCount` = IFNULL(`fbCommentCount`, 0) + 1 WHERE `fbUserId` = NEW.`fbUserId`;
194END */$$
195
196
197DELIMITER ;
198
199/* Trigger structure for table `fb_comment` */
200
201DELIMITER $$
202
203/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `fb_comment_after_delete` */$$
204
205/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `fb_comment_after_delete` AFTER DELETE ON `fb_comment` FOR EACH ROW BEGIN
206UPDATE `fb_user` SET `fbCommentCount` = IFNULL(`fbCommentCount`, 0) - 1 WHERE `fbUserId` = OLD.`fbUserId`;
207END */$$
208
209
210DELIMITER ;
211
212/* Trigger structure for table `fb_like` */
213
214DELIMITER $$
215
216/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `fb_like_after_insert` */$$
217
218/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `fb_like_after_insert` AFTER INSERT ON `fb_like` FOR EACH ROW BEGIN
219        UPDATE `fb_user` SET `fbLikeCount` = IFNULL(`fbLikeCount`, 0) + 1 WHERE `fbUserId` = NEW.`fbUserId`;
220    END */$$
221
222
223DELIMITER ;
224
225/* Trigger structure for table `fb_like` */
226
227DELIMITER $$
228
229/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `fb_like_after_delete` */$$
230
231/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `fb_like_after_delete` AFTER DELETE ON `fb_like` FOR EACH ROW BEGIN
232        UPDATE `fb_user` SET `fbLikeCount` = IFNULL(`fbLikeCount`, 0) - 1 WHERE `fbUserId` = OLD.`fbUserId`;
233    END */$$
234
235
236DELIMITER ;
237
238/* Trigger structure for table `fb_post` */
239
240DELIMITER $$
241
242/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `fb_post_after_insert` */$$
243
244/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `fb_post_after_insert` AFTER INSERT ON `fb_post` FOR EACH ROW BEGIN
245        UPDATE `fb_group` SET `fbPostCount` = IFNULL(`fbPostCount`, 0) + 1 WHERE `fbGroupId` = NEW.`fbGroupId`;
246    END */$$
247
248
249DELIMITER ;
250
251/* Trigger structure for table `fb_post` */
252
253DELIMITER $$
254
255/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `fb_post_after_delete` */$$
256
257/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `fb_post_after_delete` AFTER DELETE ON `fb_post` FOR EACH ROW BEGIN
258        UPDATE `fb_group` SET `fbPostCount` = IFNULL(`fbPostCount`, 0) - 1 WHERE `fbGroupId` = OLD.`fbGroupId`;
259    END */$$
260
261
262DELIMITER ;
263
264/* Trigger structure for table `fb_share` */
265
266DELIMITER $$
267
268/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `fb_share_after_insert` */$$
269
270/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `fb_share_after_insert` AFTER INSERT ON `fb_share` FOR EACH ROW BEGIN
271UPDATE `fb_user` SET `fbShareCount` = IFNULL(`fbShareCount`, 0) + 1 WHERE `fbUserId` = NEW.`fbUserId`;
272END */$$
273
274
275DELIMITER ;
276
277/* Trigger structure for table `fb_share` */
278
279DELIMITER $$
280
281/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `fb_share_after_delete` */$$
282
283/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `fb_share_after_delete` AFTER DELETE ON `fb_share` FOR EACH ROW BEGIN
284UPDATE `fb_user` SET `fbShareCount` = IFNULL(`fbShareCount`, 0) - 1 WHERE `fbUserId` = OLD.`fbUserId`;
285END */$$
286
287
288DELIMITER ;
289
290/* Trigger structure for table `fb_user` */
291
292DELIMITER $$
293
294/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `fb_user_after_insert` */$$
295
296/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `fb_user_after_insert` AFTER INSERT ON `fb_user` FOR EACH ROW BEGIN
297        UPDATE `fb_group` SET `fbUserCount` = IFNULL(`fbUserCount`, 0) + 1 WHERE `fbGroupId` = NEW.`fbGroupId`;
298    END */$$
299
300
301DELIMITER ;
302
303/* Trigger structure for table `fb_user` */
304
305DELIMITER $$
306
307/*!50003 DROP TRIGGER*//*!50032 IF EXISTS */ /*!50003 `fb_user_after_delete` */$$
308
309/*!50003 CREATE */ /*!50017 DEFINER = 'root'@'localhost' */ /*!50003 TRIGGER `fb_user_after_delete` AFTER DELETE ON `fb_user` FOR EACH ROW BEGIN
310        UPDATE `fb_group` SET `fbUserCount` = IFNULL(`fbUserCount`, 0) - 1 WHERE `fbGroupId` = OLD.`fbGroupId`;
311    END */$$
312
313
314DELIMITER ;
315
316/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
317/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
318/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
319