-
Notifications
You must be signed in to change notification settings - Fork 0
/
setup.pgsql
152 lines (141 loc) · 3.71 KB
/
setup.pgsql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
create table quizzes (
id bigint generated by default as identity primary key,
title text not null constraint title_check check (char_length(title) <= 50 and char_length(title) > 0),
questions jsonb not null default '[]'::jsonb ,
creator_id uuid not null references auth.users,
is_published boolean not null default false,
inserted_at timestamp with time zone default timezone('utc'::text, now()) not null,
updated_at timestamp with time zone default timezone('utc'::text, now()) not null,
fts tsvector generated always as (to_tsvector('english', title)) stored
);
alter table quizzes add constraint questions_are_valid check (
extensions.validate_json_schema(
'{
"type": "array",
"items": {
"type": "object",
"properties": {
"question": {
"type": "string",
"maxLength": 200
},
"topics": {
"type": "array",
"items": {
"type": "string",
"minLength": 1,
"maxLength": 30
},
"maxItems": 10,
"uniqueItems": true
},
"answerType": {
"type": "string",
"enum": [
"singleChoice",
"multipleChoice",
"text"
]
},
"answers": {
"type": "array",
"items": {
"type": "object",
"properties": {
"answer": {
"type": "string",
"maxLength": 50
},
"isCorrect": {
"type": "boolean"
}
},
"required": [
"answer",
"isCorrect"
],
"additionalProperties": false
},
"maxItems": 10
},
"shouldShuffle": {
"type": "boolean"
}
},
"required": [
"question",
"topics",
"answerType",
"answers",
"shouldShuffle"
],
"additionalProperties": false
},
"maxItems": 25
}',
questions
)
);
set schema 'extensions';
create or replace function validate_questions_answers(questions jsonb) returns boolean as $$
select bool_and(
exists (
select *
from jsonb_array_elements(question->'answers') answer
where (answer->>'isCorrect')::boolean
)
)
from jsonb_array_elements(questions) question
where question->>'answerType' <> 'text';
$$ language sql;
set schema 'public';
alter table quizzes add constraint published_questions_are_valid check (
(not is_published) or extensions.validate_json_schema(
'{
"items": {
"properties": {
"question": {
"minLength": 1
},
"answers": {
"items": {
"properties": {
"answer": {
"minLength": 1
}
}
},
"minItems": 1
}
}
},
"minItems": 1
}',
questions
) and (
extensions.validate_questions_answers(questions)
)
);
create index quizzes_fts on quizzes using gin (fts);
alter table quizzes
enable row level security;
create policy "Creators can do anything to their own unpublished quizzes."
on quizzes
for all
using (
auth.uid() = creator_id and (not is_published)
) with check (
auth.uid() = creator_id
);
create policy "Anyone can see published quizzes."
on quizzes
for select
using (
is_published
);
create extension if not exists moddatetime schema extensions;
create trigger
handle_updated_at before update
on quizzes
for each row execute
procedure extensions.moddatetime(updated_at);