124 lines
4.6 KiB
SQL
124 lines
4.6 KiB
SQL
-- PostgreSQL / Supabase schema for the exam result query module.
|
|
-- Each exam is independent. Scores are stored under one exam only and do
|
|
-- not affect any other exam.
|
|
|
|
create extension if not exists pgcrypto;
|
|
|
|
create table if not exists public.exam_exams (
|
|
id uuid primary key default gen_random_uuid(),
|
|
exam_code text not null unique,
|
|
exam_name text not null,
|
|
exam_date date,
|
|
is_published boolean not null default true,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now()
|
|
);
|
|
|
|
create table if not exists public.exam_results (
|
|
id uuid primary key default gen_random_uuid(),
|
|
exam_id uuid not null references public.exam_exams(id) on delete cascade,
|
|
student_name text not null,
|
|
admission_no text not null,
|
|
class_name text,
|
|
remark text,
|
|
created_at timestamptz not null default now(),
|
|
updated_at timestamptz not null default now(),
|
|
constraint exam_results_one_candidate_per_exam unique (exam_id, admission_no, student_name)
|
|
);
|
|
|
|
create table if not exists public.exam_score_items (
|
|
id uuid primary key default gen_random_uuid(),
|
|
result_id uuid not null references public.exam_results(id) on delete cascade,
|
|
item_name text not null,
|
|
score numeric(10, 2) not null,
|
|
full_score numeric(10, 2),
|
|
sort_order integer not null default 0,
|
|
created_at timestamptz not null default now()
|
|
);
|
|
|
|
create index if not exists exam_results_lookup_idx
|
|
on public.exam_results (exam_id, student_name, admission_no);
|
|
|
|
create unique index if not exists exam_score_items_unique_item_idx
|
|
on public.exam_score_items (result_id, item_name);
|
|
|
|
create index if not exists exam_score_items_result_idx
|
|
on public.exam_score_items (result_id, sort_order);
|
|
|
|
grant select on public.exam_exams to anon, authenticated;
|
|
grant select on public.exam_results to anon, authenticated;
|
|
grant select on public.exam_score_items to anon, authenticated;
|
|
|
|
alter table public.exam_exams enable row level security;
|
|
alter table public.exam_results enable row level security;
|
|
alter table public.exam_score_items enable row level security;
|
|
|
|
drop policy if exists "Public can read published exams" on public.exam_exams;
|
|
create policy "Public can read published exams"
|
|
on public.exam_exams
|
|
for select
|
|
to anon, authenticated
|
|
using (is_published = true);
|
|
|
|
drop policy if exists "Public can read published exam results" on public.exam_results;
|
|
create policy "Public can read published exam results"
|
|
on public.exam_results
|
|
for select
|
|
to anon, authenticated
|
|
using (
|
|
exists (
|
|
select 1
|
|
from public.exam_exams
|
|
where public.exam_exams.id = exam_results.exam_id
|
|
and public.exam_exams.is_published = true
|
|
)
|
|
);
|
|
|
|
drop policy if exists "Public can read published score items" on public.exam_score_items;
|
|
create policy "Public can read published score items"
|
|
on public.exam_score_items
|
|
for select
|
|
to anon, authenticated
|
|
using (
|
|
exists (
|
|
select 1
|
|
from public.exam_results
|
|
join public.exam_exams on public.exam_exams.id = public.exam_results.exam_id
|
|
where public.exam_results.id = exam_score_items.result_id
|
|
and public.exam_exams.is_published = true
|
|
)
|
|
);
|
|
|
|
insert into public.exam_exams (exam_code, exam_name, exam_date)
|
|
values
|
|
('2026-spring-midterm', '2026 年春季期中考试', '2026-04-18'),
|
|
('2026-spring-monthly-01', '2026 年春季第一次月考', '2026-03-12')
|
|
on conflict (exam_code) do nothing;
|
|
|
|
with target_exam as (
|
|
select id from public.exam_exams where exam_code = '2026-spring-midterm'
|
|
),
|
|
inserted_result as (
|
|
insert into public.exam_results (exam_id, student_name, admission_no, class_name, remark)
|
|
select id, '张三', 'EX20260001', '高一 1 班', '本场考试独立计分，实验规范扣分仅影响本场期中考试。'
|
|
from target_exam
|
|
on conflict (exam_id, admission_no, student_name) do update
|
|
set class_name = excluded.class_name,
|
|
remark = excluded.remark,
|
|
updated_at = now()
|
|
returning id
|
|
)
|
|
insert into public.exam_score_items (result_id, item_name, score, full_score, sort_order)
|
|
select id, item_name, score, full_score, sort_order
|
|
from inserted_result,
|
|
(values
|
|
('语文', 108, 150, 10),
|
|
('数学', 126, 150, 20),
|
|
('英语', 119, 150, 30),
|
|
('实验规范扣分', -2, null, 40)
|
|
) as item_rows(item_name, score, full_score, sort_order)
|
|
on conflict (result_id, item_name) do update
|
|
set score = excluded.score,
|
|
full_score = excluded.full_score,
|
|
sort_order = excluded.sort_order;
|