freepost/database.schema.sql

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;