rev |
line source |
bsw@1735
|
1 CREATE TABLE survey (
|
bsw@1735
|
2 id SERIAL4 PRIMARY KEY,
|
bsw@1735
|
3 title TEXT NOT NULL,
|
bsw@1735
|
4 text TEXT NOT NULL,
|
bsw@1746
|
5 finished_title TEXT NOT NULL,
|
bsw@1735
|
6 finished_text TEXT NOT NULL,
|
bsw@1735
|
7 created TIMESTAMPTZ NOT NULL DEFAULT now(),
|
bsw@1735
|
8 open_from TIMESTAMPTZ NOT NULL,
|
bsw@1735
|
9 open_until TIMESTAMPTZ NOT NULL
|
bsw@1735
|
10 );
|
bsw@1735
|
11
|
bsw@1735
|
12 CREATE TABLE survey_question (
|
bsw@1735
|
13 id SERIAL4 PRIMARY KEY,
|
bsw@1735
|
14 survey_id INT4 NOT NULL REFERENCES survey(id),
|
bsw@1735
|
15 position INT4 NOT NULL,
|
bsw@1735
|
16 question TEXT NOT NULL,
|
bsw@1735
|
17 description TEXT,
|
bsw@1735
|
18 answer_type TEXT NOT NULL,
|
bsw@1735
|
19 answer_options json
|
bsw@1735
|
20 );
|
bsw@1735
|
21
|
bsw@1735
|
22 CREATE TABLE survey_answer_set (
|
bsw@1735
|
23 ident TEXT NOT NULL PRIMARY KEY,
|
bsw@1818
|
24 survey_id INT4 NOT NULL REFERENCES survey(id),
|
bsw@1818
|
25 data JSON
|
bsw@1735
|
26 );
|
bsw@1735
|
27
|
bsw@1735
|
28 CREATE TABLE survey_answer (
|
bsw@1735
|
29 id SERIAL8 PRIMARY KEY,
|
bsw@1735
|
30 survey_answer_set_ident TEXT NOT NULL REFERENCES survey_answer_set(ident) ON DELETE CASCADE,
|
bsw@1735
|
31 survey_question_id INT4 NOT NULL REFERENCES survey_question(id),
|
bsw@1735
|
32 answer TEXT NOT NULL
|
bsw@1735
|
33 );
|
bsw@1735
|
34
|
bsw@1735
|
35 CREATE TABLE survey_member (
|
bsw@1735
|
36 id SERIAL4 PRIMARY KEY,
|
bsw@1735
|
37 survey_id INT4 NOT NULL REFERENCES survey(id),
|
bsw@1735
|
38 member_id INT4 NOT NULL REFERENCES member(id),
|
bsw@1735
|
39 survey_answer_set_ident TEXT REFERENCES survey_answer_set(ident),
|
bsw@1735
|
40 started TIMESTAMPTZ DEFAULT now(),
|
bsw@1735
|
41 finished TIMESTAMPTZ,
|
bsw@1735
|
42 rejected TIMESTAMPTZ
|
bsw@1735
|
43 );
|
bsw@1735
|
44
|
bsw@1735
|
45 INSERT INTO survey (id, title, text, finished_text, open_from, open_until) VALUES (
|
bsw@1735
|
46 1,
|
bsw@1735
|
47 'Example survey',
|
bsw@1735
|
48 'This is just an example',
|
bsw@1735
|
49 '<strong>Done!</strong><br>You finished it. Thank you very much.',
|
bsw@1735
|
50 '2021-10-06 12:00',
|
bsw@1735
|
51 '2021-10-14 12:00'
|
bsw@1735
|
52 );
|
bsw@1735
|
53
|
bsw@1735
|
54 INSERT INTO survey_question (survey_id, position, question, answer_type, answer_options) VALUES
|
bsw@1735
|
55 (1, 1, 'What color do you like?', 'radio', '[ "Red", "Green", "Blue" ]'),
|
bsw@1735
|
56 (1, 2, 'What form do you like?', 'radio', '[ "Square", "Circle", "Hexagon" ]')
|
bsw@1735
|
57 ;
|
bsw@1735
|
58
|
bsw@1735
|
59
|