ndxM2SnE

1-----------------------------------------
2-- Drop old schmema and create new one
3-----------------------------------------
4
5DROP TABLE IF EXISTS "user" CASCADE;
6DROP TABLE IF EXISTS "question" CASCADE;
7DROP TABLE IF EXISTS "answer" CASCADE;
8DROP TABLE IF EXISTS "form" CASCADE;
9DROP TABLE IF EXISTS "questionsForm" CASCADE;
10DROP TABLE IF EXISTS "sharedQuestion" CASCADE;
11DROP TABLE IF EXISTS "questionTag" CASCADE;
12DROP TABLE IF EXISTS "formTag" CASCADE;
13DROP TABLE IF EXISTS "userQuestionTags" CASCADE;
14DROP TABLE IF EXISTS "userFormTags" CASCADE;
15DROP TABLE IF EXISTS "questionsToTags" CASCADE;
16DROP TABLE IF EXISTS "formToTags" CASCADE;
17DROP TABLE IF EXISTS "test" CASCADE;
18DROP TABLE IF EXISTS "userManageTest" CASCADE;
19DROP TYPE IF EXISTS "correctness" CASCADE;
20DROP TYPE IF EXISTS "newness" CASCADE;
21DROP TYPE IF EXISTS "period" CASCADE;
22
23-----------------------------------------
24-- Types
25-----------------------------------------
26
27CREATE TYPE "correctness" AS ENUM ('incorrect', 'correct');
28CREATE TYPE "newness" AS ENUM ('new', 'old');
29CREATE TYPE "period" AS ENUM ('normal', 'makeup');
30
31-----------------------------------------
32-- Tables
33-----------------------------------------
34
35CREATE TABLE "user"(
36    id  SERIAL PRIMARY KEY,
37    username  text UNIQUE NOT NULL ,
38    password text NOT NULL CHECK (length(password) > 8),
39    description text,
40    image text,
41    email text UNIQUE NOT NULL
42);
43
44CREATE TABLE "question"(
45    id  SERIAL PRIMARY KEY,
46    TYPE "newness" NOT NULL,
47    derived_question INTEGER REFERENCES question(id),
48    author_id INTEGER REFERENCES "user"(id),
49    category text NOT NULL, -- category is understood as the subject of the question, if it is a databases questions, an object oriented question, etc ..
50    title text NOT NULL,
51    description text NOT NULL,
52    image text
53);
54
55CREATE TABLE "answer"(
56    id  SERIAL PRIMARY KEY,
57    TYPE correctness NOT NULL,
58    question_id INTEGER REFERENCES question(id) ON DELETE CASCADE,
59    feedback text,
60    description text NOT NULL
61);
62
63CREATE TABLE "test" (
64    id SERIAL PRIMARY KEY,
65    class text NOT NULL,
66    examDate TIMESTAMPTZ DEFAULT Now(),
67    course text NOT NULL,
68    TYPE "period" NOT NULL
69);
70
71CREATE TABLE "form" (
72    id SERIAL PRIMARY KEY,
73    owner_id INTEGER NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
74    alternativeVersion text NOT NULL,
75    test_id INTEGER NOT NULL REFERENCES test(id) ON DELETE CASCADE
76);
77
78CREATE TABLE "sharedQuestion" (
79    question_id INTEGER NOT NULL REFERENCES "question"(id) ON DELETE CASCADE,
80    user_id INTEGER NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
81    PRIMARY KEY(question_id, user_id)
82);
83
84CREATE TABLE "questionsForm" (
85    question_id INTEGER NOT NULL REFERENCES "question"(id) ON DELETE CASCADE,
86    form_id INTEGER REFERENCES "form"(id) ON DELETE CASCADE,
87    PRIMARY KEY(question_id, form_id)
88);
89
90CREATE TABLE "questionTag" (
91    id SERIAL PRIMARY KEY,
92    question_id INTEGER NOT NULL REFERENCES "question"(id) ON DELETE CASCADE,
93    label text NOT NULL
94);
95
96CREATE TABLE "formTag" (
97    id SERIAL PRIMARY KEY,
98    form_id INTEGER NOT NULL REFERENCES "form"(id) ON DELETE CASCADE,
99    label text NOT NULL
100);
101
102CREATE TABLE "userQuestionTags" (
103    user_id INTEGER NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
104    questionTag_id INTEGER NOT NULL REFERENCES "questionTag"(id),
105    PRIMARY KEY(user_id, questionTag_id)
106);
107
108CREATE TABLE "userFormTags" (
109    user_id INTEGER NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
110    formTag_id INTEGER NOT NULL REFERENCES "form"(id),
111    PRIMARY KEY(user_id, formTag_id)
112);
113
114CREATE TABLE "questionsToTags" (
115    question_id INTEGER NOT NULL REFERENCES "question"(id) ON DELETE CASCADE,
116    questionTag_id INTEGER NOT NULL REFERENCES "questionTag"(id) ON DELETE CASCADE,
117    PRIMARY KEY(question_id, questionTag_id)   
118);
119
120CREATE TABLE "formToTags" (
121    form_id INTEGER NOT NULL REFERENCES "form"(id) ON DELETE CASCADE,
122    formTag_id INTEGER NOT NULL REFERENCES "formTag"(id) ON DELETE CASCADE,
123    PRIMARY KEY(form_id, formTag_id)    
124);
125
126CREATE TABLE "userManageTest"(
127    user_id INTEGER NOT NULL REFERENCES "user"(id) ON DELETE CASCADE,
128    test_id INTEGER NOT NULL REFERENCES "test"(id) ON DELETE CASCADE,
129    PRIMARY KEY(user_id, test_id)
130);
131
132-----------------------------------------
133-- end of table creation
134-----------------------------------------