home » zplus/freepost.git
ID: 24dbe9db8a1d0a0820ba1d00628efe5e383d4129
103 lines — 4K — View raw 


  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
BEGIN TRANSACTION;
CREATE TABLE `vote_post` (
  `vote` integer NOT NULL
,  `datetime` datetime NOT NULL
,  `postId` integer NOT NULL
,  `userId` integer NOT NULL
,  PRIMARY KEY (`postId`,`userId`)
,  CONSTRAINT `FK_EDE89DBC64B64DCC` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
,  CONSTRAINT `FK_EDE89DBCE094D20D` FOREIGN KEY (`postId`) REFERENCES `post` (`id`)
);
CREATE TABLE `vote_comment` (
  `vote` integer NOT NULL
,  `datetime` datetime NOT NULL
,  `commentId` integer NOT NULL
,  `userId` integer NOT NULL
,  PRIMARY KEY (`commentId`,`userId`)
,  CONSTRAINT `FK_1FC60DF464B64DCC` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
,  CONSTRAINT `FK_1FC60DF46690C3F5` FOREIGN KEY (`commentId`) REFERENCES `comment` (`id`)
);
CREATE TABLE `user` (
  `id` integer NOT NULL PRIMARY KEY AUTOINCREMENT
,  `hashId` varchar(32) NOT NULL
,  `email` varchar(255) DEFAULT NULL
,  `email_notifications` integer  NOT NULL DEFAULT '1'
,  `isActive` integer NOT NULL
,  `password` varchar(255) NOT NULL
,  `passwordResetToken` varchar(255) DEFAULT NULL
,  `passwordResetTokenExpire` datetime DEFAULT NULL
,  `registered` datetime NOT NULL
,  `salt` varchar(255) NOT NULL
,  `username` varchar(255) NOT NULL
,  `about` varchar(10000) NOT NULL DEFAULT ''
,  `session` varchar(255) DEFAULT NULL
,  `preferred_feed` varchar(64) NOT NULL DEFAULT 'hot'
,  UNIQUE (`hashId`)
,  UNIQUE (`username`)
,  UNIQUE (`email`)
,  UNIQUE (`passwordResetToken`)
,  UNIQUE (`session`)
);
CREATE TABLE `topic` (
  `post_id` integer NOT NULL
,  `name` varchar(45) NOT NULL
,  PRIMARY KEY (`post_id`,`name`)
,  CONSTRAINT `fk_topic_1` FOREIGN KEY (`post_id`) REFERENCES `post` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
);
CREATE TABLE `remember_me` (
  `token` char(128) NOT NULL
,  `userId` integer NOT NULL
,  `expires` datetime NOT NULL
,  PRIMARY KEY (`token`)
,  CONSTRAINT `FK_userId` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
);
CREATE TABLE `post` (
  `id` integer NOT NULL PRIMARY KEY AUTOINCREMENT
,  `hashId` varchar(32) NOT NULL
,  `created` datetime NOT NULL
,  `dateCreated` date NOT NULL
,  `title` varchar(255) NOT NULL
,  `link` text COLLATE BINARY
,  `text` longtext NOT NULL
,  `vote` integer NOT NULL
,  `commentsCount` integer NOT NULL
,  `userId` integer DEFAULT NULL
,  UNIQUE (`hashId`)
,  CONSTRAINT `FK_5A8A6C8D64B64DCC` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
);
CREATE TABLE `comment` (
  `id` integer NOT NULL PRIMARY KEY AUTOINCREMENT
,  `hashId` varchar(32) NOT NULL
,  `created` datetime NOT NULL
,  `dateCreated` date NOT NULL
,  `read` integer NOT NULL
,  `text` longtext NOT NULL
,  `vote` integer NOT NULL
,  `parentId` integer DEFAULT NULL
,  `parentUserId` integer DEFAULT NULL
,  `postId` integer DEFAULT NULL
,  `userId` integer DEFAULT NULL
,  UNIQUE (`hashId`)
,  CONSTRAINT `FK_9474526C10EE4CEE` FOREIGN KEY (`parentId`) REFERENCES `comment` (`id`)
,  CONSTRAINT `FK_9474526C251330C5` FOREIGN KEY (`parentUserId`) REFERENCES `user` (`id`)
,  CONSTRAINT `FK_9474526C64B64DCC` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
,  CONSTRAINT `FK_9474526CE094D20D` FOREIGN KEY (`postId`) REFERENCES `post` (`id`)
);
CREATE INDEX "idx_vote_post_IDX_EDE89DBCE094D20D" ON "vote_post" (`postId`);
CREATE INDEX "idx_vote_post_IDX_EDE89DBC64B64DCC" ON "vote_post" (`userId`);
CREATE INDEX "idx_vote_comment_IDX_1FC60DF46690C3F5" ON "vote_comment" (`commentId`);
CREATE INDEX "idx_vote_comment_IDX_1FC60DF464B64DCC" ON "vote_comment" (`userId`);
CREATE INDEX "idx_remember_me_userId" ON "remember_me" (`userId`);
CREATE INDEX "idx_post_vote" ON "post" (`vote`);
CREATE INDEX "idx_post_dateCreated" ON "post" (`dateCreated`);
CREATE INDEX "idx_post_created" ON "post" (`created`);
CREATE INDEX "idx_post_IDX_5A8A6C8D64B64DCC" ON "post" (`userId`);
CREATE INDEX "idx_comment_vote" ON "comment" (`vote`);
CREATE INDEX "idx_comment_isRead" ON "comment" (`read`);
CREATE INDEX "idx_comment_dateCreated" ON "comment" (`dateCreated`);
CREATE INDEX "idx_comment_created" ON "comment" (`created`);
CREATE INDEX "idx_comment_IDX_9474526CE094D20D" ON "comment" (`postId`);
CREATE INDEX "idx_comment_IDX_9474526C64B64DCC" ON "comment" (`userId`);
CREATE INDEX "idx_comment_IDX_9474526C251330C5" ON "comment" (`parentUserId`);
CREATE INDEX "idx_comment_IDX_9474526C10EE4CEE" ON "comment" (`parentId`);
COMMIT;