-
Notifications
You must be signed in to change notification settings - Fork 0
/
schema.sql
60 lines (46 loc) · 1.37 KB
/
schema.sql
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
BEGIN;
DROP TABLE IF EXISTS buildings CASCADE;
-- TODO: This could probably just use name for the primary key?
CREATE TABLE buildings (
id serial PRIMARY KEY,
name varchar(32) NOT NULL
);
DROP TABLE IF EXISTS rooms CASCADE;
CREATE TABLE rooms (
id serial PRIMARY KEY,
building_id integer NOT NULL REFERENCES buildings(id)
ON DELETE CASCADE ON UPDATE CASCADE,
number varchar(16) NOT NULL,
description varchar(32) NOT NULL
);
DROP TYPE IF EXISTS weekday CASCADE;
CREATE TYPE weekday AS ENUM (
'M',
'T',
'W',
'Th',
'F',
'Sa',
'Su'
);
DROP TABLE IF EXISTS events;
-- Alternatively, this could be represented as a classes table that is
-- referenced by multiple class periods, where classroom and start/end times
-- are contained in the class period table.
CREATE TABLE events (
id serial PRIMARY KEY,
room_id integer NOT NULL REFERENCES rooms(id)
ON DELETE CASCADE ON UPDATE CASCADE,
name varchar(32) NOT NULL,
days weekday[] NOT NULL,
start_time time NOT NULL,
end_time time NOT NULL
);
CREATE OR REPLACE FUNCTION time_subtype_diff(x time, y time) RETURNS float8 AS
'SELECT EXTRACT(EPOCH FROM (x - y))' LANGUAGE sql STRICT IMMUTABLE;
DROP TYPE IF EXISTS timerange CASCADE;
CREATE TYPE timerange AS RANGE (
subtype = time,
subtype_diff = time_subtype_diff
);
COMMIT;