5ETXY5TC

1DROP TABLE IF EXISTS "user";
2CREATE TABLE "user" (
3    id BIGSERIAL PRIMARY KEY,
4    email TEXT NOT NULL UNIQUE,
5    username TEXT NOT NULL,
6    password TEXT NOT NULL
7);
8
9DROP TABLE IF EXISTS "survey";
10CREATE TABLE "survey" (
11    id BIGSERIAL PRIMARY KEY,
12    creator_id BIGINT NOT NULL,
13    setup TEXT NOT NULL,
14    anonymous_answers BOOLEAN NOT NULL,
15    gps_required BOOLEAN NOT NULL,
16    ends TIMESTAMP NOT NULL,
17    likes INT NOT NULL DEFAULT 0,
18    CONSTRAINT FK_creator FOREIGN KEY (creator_id) REFERENCES "user"
19);
20
21DROP TABLE IF EXISTS "answer";
22CREATE TABLE "answer" (
23    id BIGSERIAL PRIMARY KEY,
24    survey_id BIGINT NOT NULL,
25    answer TEXT NOT NULL,
26    CONSTRAINT FK_survey FOREIGN KEY (survey_id) REFERENCES "survey"
27);
28
29DROP TABLE IF EXISTS "user2answer";
30CREATE TABLE "user2answer" (
31    answer_id BIGINT,
32    user_id BIGINT,
33    PRIMARY KEY (answer_id, user_id),
34    CONSTRAINT FK_answer FOREIGN KEY (answer_id) REFERENCES "answer",
35    CONSTRAINT FK_user FOREIGN KEY (user_id) REFERENCES "user"
36);
37
38
39CREATE EXTENSION IF NOT EXISTS pgcrypto;
40CREATE PROCEDURE insert_user(email_ TEXT, username_ TEXT, password_ TEXT)
41LANGUAGE sql
42AS $$
43    INSERT INTO "user" (email, username, password) VALUES (email_, username_, crypt(password_, gen_salt('md5')));
44$$;
45
46CREATE FUNCTION check_pw(id_ BIGINT, password_ TEXT) returns BOOLEAN
47LANGUAGE sql
48AS $$
49    WITH u AS (SELECT (
50     (SELECT password from "user" where id = id_) = crypt(password_, (SELECT password from "user" where id = id_)))
51     )
52    SELECT * from u;
53$$;
54
55drop function check_pw_email(email_ TEXT, password_ TEXT);
56CREATE FUNCTION check_pw_email(email_ TEXT, password_ TEXT) returns BOOLEAN
57LANGUAGE sql
58AS $$
59    WITH u AS (SELECT (
60        (SELECT password from "user" where email = email_) = crypt(password_, (SELECT password from "user" where email = email_)))
61     )
62    SELECT * from u;
63$$;
64
65DROP FUNCTION get_user_if_valid(email_ TEXT, password_ TEXT);
66CREATE FUNCTION get_user_if_valid(email_ TEXT, password_ TEXT) returns TABLE(BOOLEAN, BIGINT, TEXT, TEXT)
67LANGUAGE sql
68AS $$
69    SELECT (SELECT check_pw_email(email_, password_)), id, email, username FROM "user" WHERE email = email_;
70$$;
71
72CREATE FUNCTION check_pw_email(email_ TEXT, password_ TEXT) returns BOOLEAN
73LANGUAGE sql
74AS $$
75    WITH u AS (SELECT (
76        (SELECT password from "user" where email = email_) = crypt(password_, (SELECT password from "user" where email = email_))),
77        (SELECT email, password FROM "user" where email = email_)
78     )
79    SELECT * from u;
80$$;
81
82CREATE OR REPLACE PROCEDURE insert_answer(user_id_ bigint, survey_id_ bigint, xml_ text)
83LANGUAGE plpgsql
84AS $$
85    DECLARE
86        answer_id BIGINT := nextval('answer_id_seq');
87        not_answered BOOLEAN;
88    BEGIN
89        not_answered := (SELECT count(user_id) = 0 from user2answer
90                                RIGHT JOIN answer a on user2answer.answer_id = a.id
91                                right OUTER JOIN survey s on a.survey_id = s.id
92                                WHERE user2answer.user_id = 7 AND
93                                s.id = 7);
94
95        IF not_answered THEN
96            INSERT INTO answer VALUES (answer_id, survey_id_, xml_);
97            INSERT INTO user2answer VALUES (answer_id, user_id_);
98
99        ELSE
100            RAISE EXCEPTION 'Survey % already answered by user %', survey_id_, user_id_
101                USING HINT = 'Dont do that pls';
102        END IF;
103END $$;
104
105SELECT get_user_if_valid('bob@email.com', 'password')