casebench

test cases straight onto the board.
git clone https://owenrusk.dev/casebench.git
casebench / schema.sql -rw-r--r-- · 1926 bytes
 1 -- the board tables casebench touches, as it expects them. the hub owns the real
 2 -- ones; this is for a local database to test against.
 3 --   createdb casebench && psql casebench -f schema.sql
 4 
 5 create table players (
 6     id uuid primary key default gen_random_uuid(),
 7     handle text not null unique
 8 );
 9 
10 create table board_cases (
11     player_id uuid not null references players (id) on delete cascade,
12     case_id text not null,
13     title text not null,
14     source text not null default 'runtime' check (source in ('runtime', 'bench')),
15     placed_at timestamptz not null default now(),
16     primary key (player_id, case_id)
17 );
18 
19 create table board_items (
20     player_id uuid not null,
21     case_id text not null,
22     item_id text not null,
23     position int not null,
24     title text not null,
25     body text not null,
26     images text[] not null default '{}',  -- sha256 of rows in board_media
27     primary key (player_id, case_id, item_id),
28     foreign key (player_id, case_id) references board_cases on delete cascade
29 );
30 
31 create table board_media (
32     sha256 text primary key,
33     mime text not null,
34     data bytea not null
35 );
36 
37 create table board_notes (
38     id bigserial primary key,
39     player_id uuid not null,
40     case_id text not null,
41     item_id text,  -- null: under the case's title
42     body text not null,
43     written_at timestamptz not null default now(),
44     foreign key (player_id, case_id) references board_cases on delete cascade
45 );
46 
47 create table board_locks (
48     player_id uuid not null,
49     case_id text not null,
50     text text not null default 'locked.',
51     lines int not null,
52     salt bytea not null,
53     answer_hashes text[] not null,
54     rest_after int not null,
55     rest_minutes int not null,
56     wrong int not null default 0,
57     resting_until timestamptz,
58     opened_at timestamptz,
59     primary key (player_id, case_id),
60     foreign key (player_id, case_id) references board_cases on delete cascade
61 );