database.schema.sql 4.2 KB

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