Advertisement
Guest User

Untitled

a guest
Apr 14th, 2018
267
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. DROP TABLE IF EXISTS "user" CASCADE;
  2. DROP TABLE IF EXISTS question CASCADE;
  3. DROP TABLE IF EXISTS "comment" CASCADE;
  4. DROP TABLE IF EXISTS private_message CASCADE;
  5. DROP TABLE IF EXISTS inbox CASCADE;
  6. DROP TABLE IF EXISTS notification CASCADE;
  7. DROP TABLE IF EXISTS message_notification CASCADE;
  8. DROP TABLE IF EXISTS question_notification CASCADE;
  9. DROP TABLE IF EXISTS moderator CASCADE;
  10. DROP TABLE IF EXISTS "admin" CASCADE;
  11. DROP TABLE IF EXISTS banned_user CASCADE;
  12. DROP TABLE IF EXISTS topic CASCADE;
  13. DROP TABLE IF EXISTS question_vote CASCADE;
  14. DROP TABLE IF EXISTS comment_vote CASCADE;
  15. DROP TABLE IF EXISTS achievement CASCADE;
  16. DROP TABLE IF EXISTS reward_item CASCADE;
  17.  
  18. -- Tables
  19.  
  20. CREATE TABLE "user" (
  21.     id SERIAL NOT NULL,
  22.     username text NOT NULL,
  23.     email text NOT NULL,
  24.     password text NOT NULL,
  25.     imagePath text NOT NULL,
  26.     creationDate TIMESTAMP WITH TIME zone NOT NULL,
  27.     behaviorFlag BOOLEAN DEFAULT FALSE,
  28.     experience FLOAT NOT NULL
  29.     --is_admin BOOLEAN NOT NULL,
  30.     --is_moderator BOOLEAN NOT NULL
  31. );
  32.  
  33. CREATE TABLE question (
  34.     id SERIAL NOT NULL,
  35.     user_id INTEGER NOT NULL,
  36.     topic_id INTEGER NOT NULL,
  37.     title text NOT NULL,
  38.     textContent text NOT NULL,
  39.     dateCreated TIMESTAMP WITH TIME zone NOT NULL
  40. );
  41.  
  42. CREATE TABLE "comment" (
  43.     id SERIAL NOT NULL,
  44.     question_id INTEGER NOT NULL,
  45.     user_id INTEGER NOT NULL,
  46.     textContent text NOT NULL,
  47.     dateCreated TIMESTAMP WITH TIME zone NOT NULL
  48. );
  49.  
  50. CREATE TABLE private_message (
  51.     id SERIAL NOT NULL,
  52.     sender_id INTEGER NOT NULL,
  53.     receiver_id INTEGER NOT NULL,
  54.     textContent text NOT NULL,
  55.     dateCreated TIMESTAMP WITH TIME zone NOT NULL,
  56.     seen BOOLEAN DEFAULT FALSE
  57. );
  58.  
  59. CREATE TABLE inbox (
  60.     user_id INTEGER NOT NULL
  61. );
  62.  
  63. CREATE TABLE notification (
  64.     id SERIAL NOT NULL,
  65.     user_id INTEGER NOT NULL,
  66.     seen BOOLEAN DEFAULT FALSE
  67. );
  68.  
  69. CREATE TABLE message_notification (
  70.     notification_id INTEGER NOT NULL,
  71.     private_message_id INTEGER NOT NULL
  72. );
  73.  
  74. CREATE TABLE question_notification (
  75.     notification_id INTEGER NOT NULL,
  76.     question_id INTEGER NOT NULL
  77. );
  78.  
  79. CREATE TABLE moderator (
  80.     user_id INTEGER NOT NULL
  81. );
  82.  
  83. CREATE TABLE "admin" (
  84.     user_id INTEGER NOT NULL
  85. );
  86.  
  87. CREATE TABLE banned_user (
  88.     user_id INTEGER NOT NULL,
  89.     banDate TIMESTAMP WITH TIME zone NOT NULL
  90. );
  91.  
  92. CREATE TABLE topic (
  93.     id SERIAL NOT NULL,
  94.     name text NOT NULL
  95. );
  96.  
  97. CREATE TABLE question_vote (
  98.     question_id INTEGER NOT NULL,
  99.     user_id INTEGER NOT NULL,
  100.     "value" INTEGER NOT NULL CHECK (("value" >= -1) AND ("value" <= 1))
  101. );
  102.  
  103. CREATE TABLE comment_vote (
  104.     comment_id INTEGER NOT NULL,
  105.     user_id INTEGER NOT NULL,
  106.     "value" INTEGER NOT NULL CHECK (("value" >= -1) AND ("value" <= 1))
  107. );
  108.  
  109. CREATE TABLE achievement (
  110.     id SERIAL NOT NULL,
  111.     user_id INTEGER NOT NULL,
  112.     title text NOT NULL,
  113.     objective text NOT NULL,
  114.     dateAchieved TIMESTAMP WITH TIME zone NOT NULL,
  115.     completionFlag BOOLEAN DEFAULT FALSE,
  116.     experienceGain INTEGER NOT NULL CHECK (experienceGain > 0)
  117. );
  118.  
  119. CREATE TABLE reward_item (
  120.     id SERIAL NOT NULL,
  121.     achievement_id INTEGER NOT NULL,
  122.     name text NOT NULL,
  123.     imagePath text NOT NULL
  124. );
  125.  
  126. -- Primary Keys and Uniques
  127. ALTER TABLE ONLY "user"
  128.     ADD CONSTRAINT user_pkey PRIMARY KEY (id);
  129.  
  130. ALTER TABLE ONLY "user"
  131.     ADD CONSTRAINT user_email_key UNIQUE (email);
  132.  
  133. ALTER TABLE ONLY "user"
  134.     ADD CONSTRAINT user_username UNIQUE (username);
  135.  
  136. ALTER TABLE ONLY question
  137.     ADD CONSTRAINT question_pkey PRIMARY KEY (id);
  138.  
  139. ALTER TABLE ONLY "comment"
  140.     ADD CONSTRAINT comment_pkey PRIMARY KEY (id);
  141.  
  142. ALTER TABLE ONLY private_message
  143.     ADD CONSTRAINT private_message_pkey PRIMARY KEY (id);
  144.  
  145. ALTER TABLE ONLY inbox
  146.     ADD CONSTRAINT inbox_pkey PRIMARY KEY (user_id);
  147.  
  148. ALTER TABLE ONLY notification
  149.     ADD CONSTRAINT notification_pkey PRIMARY KEY (id);
  150.  
  151. ALTER TABLE ONLY message_notification
  152.     ADD CONSTRAINT message_notification_pkey PRIMARY KEY (notification_id);
  153.  
  154. ALTER TABLE ONLY question_notification
  155.     ADD CONSTRAINT question_notification_pkey PRIMARY KEY (notification_id);
  156.  
  157. ALTER TABLE ONLY moderator
  158.     ADD CONSTRAINT moderator_pkey PRIMARY KEY (user_id);
  159.  
  160. ALTER TABLE ONLY "admin"
  161.     ADD CONSTRAINT admin_pkey PRIMARY KEY (user_id);
  162.  
  163. ALTER TABLE ONLY banned_user
  164.     ADD CONSTRAINT banned_user_pkey PRIMARY KEY (user_id);
  165.  
  166. ALTER TABLE ONLY topic
  167.     ADD CONSTRAINT topic_pkey PRIMARY KEY (id);
  168.  
  169. ALTER TABLE ONLY topic
  170.     ADD CONSTRAINT topic_name UNIQUE (name);
  171.  
  172. ALTER TABLE ONLY question_vote
  173.     ADD CONSTRAINT question_vote_id_pkey PRIMARY KEY (question_id, user_id);
  174.  
  175. ALTER TABLE ONLY comment_vote
  176.     ADD CONSTRAINT comment_vote_id_pkey PRIMARY KEY (comment_id, user_id);
  177.  
  178. ALTER TABLE ONLY achievement
  179.     ADD CONSTRAINT achievement_pkey PRIMARY KEY (id);
  180.  
  181. ALTER TABLE ONLY achievement
  182.     ADD CONSTRAINT achievement_title UNIQUE (title);
  183.  
  184. ALTER TABLE ONLY reward_item
  185.     ADD CONSTRAINT reward_item_pkey PRIMARY KEY (id);
  186.  
  187. ALTER TABLE ONLY reward_item
  188.     ADD CONSTRAINT reward_item_name UNIQUE (name);
  189.  
  190. ALTER TABLE ONLY reward_item
  191.     ADD CONSTRAINT reward_item_image_path UNIQUE (imagePath);
  192.  
  193. -- Foreign Keys
  194. ALTER TABLE ONLY question
  195.     ADD CONSTRAINT question_user_id_fkey FOREIGN KEY (user_id) REFERENCES "user"(id) ON UPDATE CASCADE;
  196.  
  197. ALTER TABLE ONLY question
  198.     ADD CONSTRAINT question_topic_id_fkey FOREIGN KEY (topic_id) REFERENCES topic(id) ON UPDATE CASCADE;
  199.  
  200. ALTER TABLE ONLY "comment"
  201.     ADD CONSTRAINT comment_question_id_fkey FOREIGN KEY (question_id) REFERENCES question(id) ON UPDATE CASCADE;
  202.  
  203. ALTER TABLE ONLY "comment"
  204.     ADD CONSTRAINT comment_user_id_fkey FOREIGN KEY (user_id) REFERENCES "user"(id) ON UPDATE CASCADE;
  205.  
  206. ALTER TABLE ONLY private_message
  207.     ADD CONSTRAINT private_message_sender_id_fkey FOREIGN KEY (sender_id) REFERENCES "user"(id) ON UPDATE CASCADE;
  208.  
  209. ALTER TABLE ONLY private_message
  210.     ADD CONSTRAINT private_message_receiver_id_fkey FOREIGN KEY (receiver_id) REFERENCES "user"(id) ON UPDATE CASCADE;
  211.  
  212. ALTER TABLE ONLY inbox
  213.     ADD CONSTRAINT inbox_user_id_fkey FOREIGN KEY (user_id) REFERENCES "user"(id) ON UPDATE CASCADE;
  214.  
  215. ALTER TABLE ONLY notification
  216.     ADD CONSTRAINT notification_inbox_fkey FOREIGN KEY (user_id) REFERENCES "user"(id) ON UPDATE CASCADE;
  217.    
  218. ALTER TABLE ONLY message_notification
  219.     ADD CONSTRAINT message_notification_id_fkey FOREIGN KEY (notification_id) REFERENCES notification(id) ON UPDATE CASCADE;
  220.  
  221. ALTER TABLE ONLY message_notification
  222.     ADD CONSTRAINT message_notification_message_id_fkey FOREIGN KEY (private_message_id) REFERENCES private_message(id) ON UPDATE CASCADE;
  223.  
  224. ALTER TABLE ONLY question_notification
  225.     ADD CONSTRAINT question_notification_id_fkey FOREIGN KEY (notification_id) REFERENCES notification(id) ON UPDATE CASCADE;
  226.  
  227. ALTER TABLE ONLY question_notification
  228.     ADD CONSTRAINT question_notification_question_id_fkey FOREIGN KEY (question_id) REFERENCES question(id) ON UPDATE CASCADE;
  229.  
  230. ALTER TABLE ONLY moderator
  231.     ADD CONSTRAINT moderator_user_id_fkey FOREIGN KEY (user_id) REFERENCES "user"(id) ON UPDATE CASCADE;
  232.  
  233. ALTER TABLE ONLY "admin"
  234.     ADD CONSTRAINT admin_user_id_fkey FOREIGN KEY (user_id) REFERENCES "user"(id) ON UPDATE CASCADE;
  235.  
  236. ALTER TABLE ONLY banned_user
  237.     ADD CONSTRAINT banned_user_id_fkey FOREIGN KEY (user_id) REFERENCES "user"(id) ON UPDATE CASCADE;
  238.  
  239. ALTER TABLE ONLY question_vote
  240.     ADD CONSTRAINT question_vote_question_id_fkey FOREIGN KEY (question_id) REFERENCES question(id) ON UPDATE CASCADE;
  241.  
  242. ALTER TABLE ONLY question_vote
  243.     ADD CONSTRAINT question_vote_user_id_fkey FOREIGN KEY (user_id) REFERENCES "user"(id) ON UPDATE CASCADE;
  244.  
  245. ALTER TABLE ONLY comment_vote
  246.     ADD CONSTRAINT comment_vote_question_id_fkey FOREIGN KEY (comment_id) REFERENCES "comment" (id) ON UPDATE CASCADE;
  247.  
  248. ALTER TABLE ONLY comment_vote
  249.     ADD CONSTRAINT comment_vote_user_id_fkey FOREIGN KEY (user_id) REFERENCES "user"(id) ON UPDATE CASCADE;
  250.  
  251. ALTER TABLE ONLY achievement
  252.     ADD CONSTRAINT achievement_user_id_fkey FOREIGN KEY (user_id) REFERENCES "user"(id) ON UPDATE CASCADE;
  253.  
  254. ALTER TABLE ONLY reward_item
  255.     ADD CONSTRAINT reward_item_achievement_id_fkey FOREIGN KEY (achievement_id) REFERENCES achievement(id) ON UPDATE CASCADE;
  256.    
  257. -- TRIGGERS
  258.  
  259. CREATE FUNCTION equalPassword() RETURNS TRIGGER AS $equal_password$
  260. --&BODY&
  261. BEGIN
  262.     IF EXISTS (SELECT id, password FROM "user" WHERE id = NEW.id AND password = NEW.password)
  263.     THEN
  264.         RAISE EXCEPTION 'Your new password must be different than the older';
  265.     END IF;
  266.     RETURN NEW;
  267. END;
  268. --$BODY$
  269. $equal_password$ LANGUAGE plpgsql;
  270.    
  271. CREATE TRIGGER newPassEqualToOld
  272. BEFORE UPDATE ON "user"
  273. FOR EACH ROW
  274.     EXECUTE PROCEDURE equalPassword();
  275.    
  276.    
  277.  
  278. CREATE FUNCTION autoVoteQuestion() RETURNS TRIGGER AS $auto_vote_question$
  279. --&BODY&
  280. BEGIN
  281.     INSERT INTO question_vote (question_id, user_id, "value") VALUES (NEW.id, NEW.user_id, 1);
  282.     RETURN NEW;
  283. END;   
  284. --&BODY&
  285. $auto_vote_question$ LANGUAGE plpgsql;
  286.    
  287. CREATE TRIGGER questionAutoVote
  288. AFTER INSERT ON question
  289. FOR EACH ROW
  290.     EXECUTE PROCEDURE autoVoteQuestion();
  291.    
  292.    
  293.    
  294. CREATE FUNCTION autoVoteComment() RETURNS TRIGGER AS $auto_vote_comment$
  295. --&BODY&
  296. BEGIN
  297.     INSERT INTO comment_vote (comment_id, user_id, "value") VALUES (NEW.id, NEW.user_id, 1);
  298.     RETURN NEW;
  299. END;
  300. --&BODY&
  301. $auto_vote_comment$ LANGUAGE plpgsql;
  302.    
  303. CREATE TRIGGER commentAutoVote
  304. AFTER INSERT ON "comment"
  305. FOR EACH ROW
  306.     EXECUTE PROCEDURE autoVoteComment();
  307.  
  308. -- Inserts
  309.  
  310. INSERT INTO "user" (id, username, email, password, imagePath, creationDate, behaviorFlag, experience) VALUES (1, 'User1', 'testuser1@gmail.com', 'FDSG7DE9', 'defaultimage.png', '2016-02-15 15:48:29', FALSE, 3710);
  311. INSERT INTO "user" (id, username, email, password, imagePath, creationDate, behaviorFlag, experience) VALUES (2, 'User2', 'testuser2@gmail.com', 'SDFGHN12', 'defaultimage.png', '2016-05-12 19:37:52', FALSE, 67243);
  312. INSERT INTO "user" (id, username, email, password, imagePath, creationDate, behaviorFlag, experience) VALUES (3, 'User3', 'testuser3@gmail.com', 'MDO671KM', 'defaultimage.png', '2016-05-28 21:15:16', TRUE, 272);
  313. INSERT INTO "user" (id, username, email, password, imagePath, creationDate, behaviorFlag, experience) VALUES (4, 'User4', 'testuser4@gmail.com', '85SDF978', 'defaultimage.png', '2016-10-05 03:42:19', FALSE, 45412);
  314. INSERT INTO "user" (id, username, email, password, imagePath, creationDate, behaviorFlag, experience) VALUES (5, 'User5', 'testuser5@gmail.com', 'OMJHDF80', 'defaultimage.png', '2017-04-21 15:12:54', FALSE, 137532);
  315. INSERT INTO "user" (id, username, email, password, imagePath, creationDate, behaviorFlag, experience) VALUES (6, 'User6', 'testuser6@gmail.com', 'H1K2HNJL', 'defaultimage.png', '2017-07-15 09:54:45', FALSE, 54321);
  316. INSERT INTO "user" (id, username, email, password, imagePath, creationDate, behaviorFlag, experience) VALUES (7, 'User7', 'testuser7@gmail.com', 'DSH098DS', 'defaultimage.png', '2017-08-17 10:03:15', FALSE, 43523);
  317. INSERT INTO "user" (id, username, email, password, imagePath, creationDate, behaviorFlag, experience) VALUES (8, 'User8', 'testuser8@gmail.com', 'MO32MMDS', 'defaultimage.png', '2017-10-25 23:12:32', FALSE, 623);
  318. INSERT INTO "user" (id, username, email, password, imagePath, creationDate, behaviorFlag, experience) VALUES (9, 'User9', 'testuser9@gmail.com', 'DSF5D0SH', 'defaultimage.png', '2017-12-28 05:27:12', FALSE, 4713);
  319. INSERT INTO "user" (id, username, email, password, imagePath, creationDate, behaviorFlag, experience) VALUES (10, 'User10', 'testuser10@gmail.com', '54KLNNLK', 'defaultimage.png', '2018-01-04 13:07:51', TRUE, 4326);
  320. INSERT INTO "user" (id, username, email, password, imagePath, creationDate, behaviorFlag, experience) VALUES (11, 'User11', 'testuser11@gmail.com', 'KIN423SD', 'defaultimage.png', '2018-03-02 19:47:18', FALSE, 13832);
  321. INSERT INTO "user" (id, username, email, password, imagePath, creationDate, behaviorFlag, experience) VALUES (12, 'User12', 'testuser12@gmail.com', 'BDS87G9M', 'defaultimage.png', '2018-03-18 22:41:59', FALSE, 435);
  322.  
  323. INSERT INTO topic (id, name) VALUES (1, 'Character');
  324. INSERT INTO topic (id, name) VALUES (2, 'Combat');
  325. INSERT INTO topic (id, name) VALUES (3, 'Dungeon Mastering');
  326. INSERT INTO topic (id, name) VALUES (4, 'Itemization');
  327. INSERT INTO topic (id, name) VALUES (5, 'Roleplay');
  328. INSERT INTO topic (id, name) VALUES (6, 'Text Play');
  329. INSERT INTO topic (id, name) VALUES (7, 'Off-Topic');
  330.  
  331. INSERT INTO question (id, user_id, topic_id, title, textContent, dateCreated) VALUES (1, 4, 2, 'How to I engage in combat?', 'Duis pretium porta nibh, quis interdum turpis facilisis non. Nulla sed faucibus arcu. Nunc et ligula eu purus interdum laoreet non in felis. Nulla sed vulputate velit. Nulla nunc ligula, consequat sit amet auctor ac, tincidunt nec libero.', '2016-11-21 16:22:52');
  332. INSERT INTO question (id, user_id, topic_id, title, textContent, dateCreated) VALUES (2, 2, 5, 'I have a question about roleplaying...', 'Quisque maximus erat vel sem iaculis condimentum. Suspendisse sit amet mollis sapien. Nam auctor, diam eu egestas aliquam, turpis nulla accumsan mi, quis tristique sem mi sit amet orci. Nulla nisl velit, rhoncus eu neque id, consequat sodales augue. Mauris lacinia, nibh vitae vestibulum dignissim, erat turpis scelerisque felis, ut placerat massa ipsum eget neque. Sed at ante in enim facilisis finibus et ac erat.', '2017-01-05 13:28:31');
  333. INSERT INTO question (id, user_id, topic_id, title, textContent, dateCreated) VALUES (3, 5, 4, 'Making itemization rewarding for players...', 'Phasellus at neque ornare, luctus velit ac, luctus mauris. Aenean commodo velit a dui elementum, in egestas libero lobortis. Aliquam erat volutpat. Nunc vitae nibh metus. Mauris pellentesque malesuada tincidunt. Duis accumsan, arcu scelerisque malesuada scelerisque, sem nunc ultrices sapien, ac lacinia ipsum nibh non metus. Aenean nec lacus id tellus varius venenatis eu ut nisi. Vestibulum a cursus velit. Donec mauris lacus, pulvinar vel viverra vel, blandit non purus. Vestibulum vel gravida nunc, eget scelerisque turpis. Pellentesque eu feugiat tortor. Vestibulum ultricies lectus quis orci venenatis gravida.', '2017-12-10 11:41:19');
  334. INSERT INTO question (id, user_id, topic_id, title, textContent, dateCreated) VALUES (4, 7, 1, 'How can I create an interesting character?', 'Vivamus massa libero, ornare vitae elementum a, ultricies sed turpis. Suspendisse venenatis, turpis non porta blandit, neque leo gravida purus, a pulvinar nulla nunc pretium sapien. Phasellus quis enim quis tortor malesuada volutpat sit amet nec lorem. Nulla facilisi. Curabitur sit amet rhoncus sapien. In enim nibh, vestibulum a faucibus vitae, fermentum vitae purus. Nullam nulla ex, laoreet ut aliquam in, vestibulum non sapien. Morbi posuere vestibulum nulla at venenatis. Mauris sed metus eu ante suscipit mattis. Interdum et malesuada fames ac ante ipsum primis in faucibus. Maecenas a mi lobortis, scelerisque magna vel, convallis enim. Nullam bibendum scelerisque lorem. In ex nibh, pharetra vitae sodales nec, luctus vitae neque. Aliquam ut augue erat.', '2018-01-03 15:23:34');
  335. INSERT INTO question (id, user_id, topic_id, title, textContent, dateCreated) VALUES (5, 11, 3, 'As a DM, how am I supposed to keep players engaged?', 'Cras malesuada nunc vel vestibulum pharetra. Nulla eu ex arcu. Pellentesque lacinia urna ac turpis ultrices, a interdum nibh vehicula. Pellentesque eu leo mollis, sollicitudin velit quis, congue enim. Donec luctus, velit vestibulum pretium pulvinar, urna magna luctus dui, in auctor sem nisi a libero. Nam finibus nibh sed mi iaculis, vitae iaculis dui iaculis. Fusce congue suscipit nulla eu cursus. Nullam et eros id nulla placerat lacinia.', '2018-03-03 01:51:29');
  336.  
  337. INSERT INTO "comment" (id, question_id, user_id, textContent, dateCreated) VALUES (1, 1, 12, 'Cras malesuada nunc vel vestibulum pharetra. Nulla eu ex arcu. Pellentesque lacinia urna ac turpis ultrices, a interdum nibh vehicula. Pellentesque eu leo mollis, sollicitudin velit quis, congue enim.', '2016-11-22 01:25:17');
  338. INSERT INTO "comment" (id, question_id, user_id, textContent, dateCreated) VALUES (2, 1, 6, 'Vivamus massa libero, ornare vitae elementum a, ultricies sed turpis. Suspendisse venenatis, turpis non porta blandit, neque leo gravida purus, a pulvinar nulla nunc pretium sapien.', '2016-11-22 10:41:52');
  339. INSERT INTO "comment" (id, question_id, user_id, textContent, dateCreated) VALUES (3, 1, 8, 'Aliquam volutpat dictum nibh, ac hendrerit nunc pellentesque in. Donec augue elit, blandit non neque rhoncus, malesuada semper odio.', '2016-11-22 15:18:29');
  340. INSERT INTO "comment" (id, question_id, user_id, textContent, dateCreated) VALUES (4, 2, 4, 'Mauris sed metus eu ante suscipit mattis. Interdum et malesuada fames ac ante ipsum primis in faucibus. Maecenas a mi lobortis, scelerisque magna vel, convallis enim. Nullam bibendum scelerisque lorem. In ex nibh, pharetra vitae sodales nec, luctus vitae neque. Aliquam ut augue erat.', '2017-01-08 04:28:18');
  341. INSERT INTO "comment" (id, question_id, user_id, textContent, dateCreated) VALUES (5, 1, 1, 'Phasellus quis enim quis tortor malesuada volutpat sit amet nec lorem. Nulla facilisi. Curabitur sit amet rhoncus sapien. In enim nibh, vestibulum a faucibus vitae, fermentum vitae purus.', '2017-01-10 02:55:12');
  342. INSERT INTO "comment" (id, question_id, user_id, textContent, dateCreated) VALUES (6, 2, 10, 'Duis accumsan, arcu scelerisque malesuada scelerisque, sem nunc ultrices sapien, ac lacinia ipsum nibh non metus.', '2017-01-10 08:14:47');
  343. INSERT INTO "comment" (id, question_id, user_id, textContent, dateCreated) VALUES (7, 3, 7, 'Morbi posuere vestibulum nulla at venenatis. Mauris sed metus eu ante suscipit mattis. Interdum et malesuada fames ac ante ipsum primis in faucibus.', '2017-12-10 15:17:41');
  344. INSERT INTO "comment" (id, question_id, user_id, textContent, dateCreated) VALUES (8, 3, 2, 'Phasellus at neque ornare, luctus velit ac, luctus mauris.', '2017-12-11 21:38:18');
  345. INSERT INTO "comment" (id, question_id, user_id, textContent, dateCreated) VALUES (9, 2, 5, 'Sed dapibus magna ac interdum rhoncus. Maecenas laoreet scelerisque ullamcorper. Nunc eget tellus venenatis ante luctus mattis.', '2017-12-18 17:03:45');
  346. INSERT INTO "comment" (id, question_id, user_id, textContent, dateCreated) VALUES (10, 3, 5, 'Maecenas a mi lobortis, scelerisque magna vel, convallis enim. Nullam bibendum scelerisque lorem. In ex nibh, pharetra vitae sodales nec, luctus vitae neque. Aliquam ut augue erat.', '2017-12-18 17:26:12');
  347. INSERT INTO "comment" (id, question_id, user_id, textContent, dateCreated) VALUES (11, 4, 5, 'Maecenas a mi lobortis, scelerisque magna vel, convallis enim. Nullam bibendum scelerisque lorem. In ex nibh, pharetra vitae sodales nec, luctus vitae neque. Aliquam ut augue erat.', '2018-01-05 23:17:58');
  348.  
  349. INSERT INTO private_message (id, sender_id, receiver_id, textContent, dateCreated, seen) VALUES (1, 1, 2, 'Hello', '2016-05-15 00:15:40', TRUE);
  350. INSERT INTO private_message (id, sender_id, receiver_id, textContent, dateCreated, seen) VALUES (2, 2, 1, 'Hey man', '2016-05-15 01:24:17', TRUE);
  351. INSERT INTO private_message (id, sender_id, receiver_id, textContent, dateCreated, seen) VALUES (3, 1, 2, 'What''s up?', '2016-05-15 01:26:10', TRUE);
  352. INSERT INTO private_message (id, sender_id, receiver_id, textContent, dateCreated, seen) VALUES (4, 2, 1, 'Lorem Ipsum', '2016-05-15 01:29:41', TRUE);
  353. INSERT INTO private_message (id, sender_id, receiver_id, textContent, dateCreated, seen) VALUES (5, 1, 2, 'bitconnect', '2016-05-15 01:31:16', FALSE);
  354.  
  355. INSERT INTO notification (id, user_id, seen) VALUES (1, 2, TRUE);
  356. INSERT INTO notification (id, user_id, seen) VALUES (2, 1, TRUE);
  357. INSERT INTO notification (id, user_id, seen) VALUES (3, 2, TRUE);
  358. INSERT INTO notification (id, user_id, seen) VALUES (4, 1, TRUE);
  359. INSERT INTO notification (id, user_id, seen) VALUES (5, 2, FALSE);
  360. INSERT INTO notification (id, user_id, seen) VALUES (6, 4, TRUE);
  361. INSERT INTO notification (id, user_id, seen) VALUES (7, 4, TRUE);
  362. INSERT INTO notification (id, user_id, seen) VALUES (8, 4, TRUE);
  363. INSERT INTO notification (id, user_id, seen) VALUES (9, 2, TRUE);
  364. INSERT INTO notification (id, user_id, seen) VALUES (10, 4, TRUE);
  365. INSERT INTO notification (id, user_id, seen) VALUES (11, 2, TRUE);
  366. INSERT INTO notification (id, user_id, seen) VALUES (12, 5, TRUE);
  367. INSERT INTO notification (id, user_id, seen) VALUES (13, 5, TRUE);
  368. INSERT INTO notification (id, user_id, seen) VALUES (14, 2, TRUE);
  369. INSERT INTO notification (id, user_id, seen) VALUES (15, 5, TRUE);
  370. INSERT INTO notification (id, user_id, seen) VALUES (16, 7, TRUE);
  371.  
  372. INSERT INTO message_notification (notification_id, private_message_id) VALUES (1, 1);
  373. INSERT INTO message_notification (notification_id, private_message_id) VALUES (2, 2);
  374. INSERT INTO message_notification (notification_id, private_message_id) VALUES (3, 3);
  375. INSERT INTO message_notification (notification_id, private_message_id) VALUES (4, 4);
  376. INSERT INTO message_notification (notification_id, private_message_id) VALUES (5, 5);
  377.  
  378. INSERT INTO question_notification (notification_id, question_id) VALUES (6, 1);
  379. INSERT INTO question_notification (notification_id, question_id) VALUES (7, 1);
  380. INSERT INTO question_notification (notification_id, question_id) VALUES (8, 1);
  381. INSERT INTO question_notification (notification_id, question_id) VALUES (9, 2);
  382. INSERT INTO question_notification (notification_id, question_id) VALUES (10, 1);
  383. INSERT INTO question_notification (notification_id, question_id) VALUES (11, 2);
  384. INSERT INTO question_notification (notification_id, question_id) VALUES (12, 3);
  385. INSERT INTO question_notification (notification_id, question_id) VALUES (13, 3);
  386. INSERT INTO question_notification (notification_id, question_id) VALUES (14, 2);
  387. INSERT INTO question_notification (notification_id, question_id) VALUES (15, 3);
  388. INSERT INTO question_notification (notification_id, question_id) VALUES (16, 4);
  389.  
  390. INSERT INTO moderator (user_id) VALUES (2);
  391. INSERT INTO moderator (user_id) VALUES (4);
  392.  
  393. INSERT INTO "admin" (user_id) VALUES (1);
  394.  
  395. INSERT INTO banned_user (user_id, banDate) VALUES (3, '2016-05-30 12:03:27');
  396. INSERT INTO banned_user (user_id, banDate) VALUES (10, '2018-01-07 23:19:11');
  397.  
  398. --Need to check votes and achievements later
  399.  
  400. INSERT INTO question_vote (question_id, user_id, "value") VALUES (1, 1, 1);
  401. INSERT INTO question_vote (question_id, user_id, "value") VALUES (2, 1, 1);
  402. INSERT INTO question_vote (question_id, user_id, "value") VALUES (3, 1, -1);
  403. INSERT INTO question_vote (question_id, user_id, "value") VALUES (5, 1, 1);
  404.  
  405. INSERT INTO comment_vote (comment_id, user_id, "value") VALUES (4, 1, 1);
  406. INSERT INTO comment_vote (comment_id, user_id, "value") VALUES (7, 2, 1);
  407. INSERT INTO comment_vote (comment_id, user_id, "value") VALUES (8, 1, 1);
  408.  
  409. INSERT INTO achievement (id, user_id, title, objective, dateAchieved, completionFlag, experienceGain) VALUES (1, 2, 'The first of Many', 'Post your first question', '2016-05-30 12:03:27', FALSE, 10);
  410. INSERT INTO achievement (id, user_id, title, objective, dateAchieved, completionFlag, experienceGain) VALUES (3, 2, 'Template1', 'Template', '2016-05-30 12:03:27', FALSE, 10);
  411. INSERT INTO achievement (id, user_id, title, objective, dateAchieved, completionFlag, experienceGain) VALUES (2, 2, 'Template2', 'Template', '2016-05-30 12:03:27', FALSE, 10);
  412. INSERT INTO achievement (id, user_id, title, objective, dateAchieved, completionFlag, experienceGain) VALUES (4, 2, 'Template3', 'Template', '2016-05-30 12:03:27', FALSE, 10);
  413.  
  414. INSERT INTO reward_item (id, achievement_id, name, imagePath) VALUES (1, 1, 'The sword of a thousand truths', 'TheSwordOfAThousandTruths.jpg');
  415. INSERT INTO reward_item (id, achievement_id, name, imagePath) VALUES (2, 2, 'Template1', 'Template1.jpg');
  416. INSERT INTO reward_item (id, achievement_id, name, imagePath) VALUES (3, 3, 'Template2', 'Template2.jpg');
  417. INSERT INTO reward_item (id, achievement_id, name, imagePath) VALUES (4, 4, 'Template3', 'Template3.jpg');
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement