liquid_feedback_frontend: db/survey.sql annotate

liquid_feedback_frontend

annotate db/survey.sql @ 1827:a5e1de39e19a

Jump to member list after adding member as a contact

author	bsw
date	Thu Feb 03 12:05:42 2022 +0100 (2022-02-03)
parents	2d4136357989
children

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