diff options
| -rw-r--r-- | sql/drop.sql | 4 | ||||
| -rw-r--r-- | sql/schema.sql | 34 | ||||
| -rw-r--r-- | sql/tcourse.sql | 19 |
3 files changed, 39 insertions, 18 deletions
diff --git a/sql/drop.sql b/sql/drop.sql index 6efe552..cc21040 100644 --- a/sql/drop.sql +++ b/sql/drop.sql @@ -1,3 +1,5 @@ DROP TABLE choices; -DROP TABLE courses; DROP TABLE users; +DROP TABLE courses; +DROP TABLE ctypes; +DROP TABLE cgroups; diff --git a/sql/schema.sql b/sql/schema.sql index 7514464..ecf31a2 100644 --- a/sql/schema.sql +++ b/sql/schema.sql @@ -1,24 +1,34 @@ +CREATE TABLE ctypes ( + name TEXT PRIMARY KEY NOT NULL +); +CREATE TABLE cgroups ( + name TEXT PRIMARY KEY NOT NULL +); CREATE TABLE courses ( id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY, nmax INTEGER NOT NULL, - title TEXT, - ctype TEXT, - teacher TEXT, - location TEXT + title TEXT NOT NULL, + teacher TEXT NOT NULL, + location TEXT NOT NULL, + ctype TEXT NOT NULL, + FOREIGN KEY(ctype) REFERENCES ctypes(name), + cgroup TEXT NOT NULL, + FOREIGN KEY(cgroup) REFERENCES cgroups(name) ); CREATE TABLE users ( - id TEXT PRIMARY KEY NOT NULL, - name TEXT, - email TEXT, - department TEXT, + id TEXT PRIMARY KEY NOT NULL, -- should be UUID + name TEXT NOT NULL, + email TEXT NOT NULL, + department TEXT NOT NULL, session TEXT, - expr BIGINT + expr BIGINT -- seconds ); CREATE TABLE choices ( PRIMARY KEY (userid, courseid), seltime BIGINT NOT NULL, -- microseconds - userid TEXT NOT NULL, - courseid INTEGER NOT NULL, + userid TEXT NOT NULL, -- should be UUID FOREIGN KEY(userid) REFERENCES users(id), - FOREIGN KEY(courseid) REFERENCES courses(id) + courseid INTEGER NOT NULL, + FOREIGN KEY(courseid) REFERENCES courses(id), + UNIQUE (userid, courseid) ); diff --git a/sql/tcourse.sql b/sql/tcourse.sql index a20ad31..171a30c 100644 --- a/sql/tcourse.sql +++ b/sql/tcourse.sql @@ -1,5 +1,14 @@ -INSERT INTO courses (title, ctype, teacher, location, nmax) VALUES('Floorball', 'Sport', 'Chen Xiaojia', 'Gym', 1000); -INSERT INTO courses (title, ctype, teacher, location, nmax) VALUES('Flag Football', 'Sport', 'Ding Zhaoyuan', 'Pitch', 1000); -INSERT INTO courses (title, ctype, teacher, location, nmax) VALUES('Chamber Music', 'Enrichment', '(?)', 'Music Rooms', 1000); -INSERT INTO courses (title, ctype, teacher, location, nmax) VALUES('Math and Computer Science', 'Enrichment', 'Jeff Zhang', '2307', 1000); -INSERT INTO courses (title, ctype, teacher, location, nmax) VALUES('Chinese Drama', 'Culture', 'Monica Chen (?)', 'Black Box (?)', 1000); +INSERT INTO ctypes (name) VALUES('Enrichment'); +INSERT INTO ctypes (name) VALUES('Sport'); +INSERT INTO ctypes (name) VALUES('Culture'); +INSERT INTO cgroups (name) VALUES('MW1'); +INSERT INTO cgroups (name) VALUES('MW2'); +INSERT INTO cgroups (name) VALUES('MW3'); +INSERT INTO cgroups (name) VALUES('TT1'); +INSERT INTO cgroups (name) VALUES('TT2'); +INSERT INTO cgroups (name) VALUES('TT3'); +INSERT INTO courses (title, ctype, cgroup, teacher, location, nmax) VALUES('Floorball', 'Sport', 'MW1', 'Chen Xiaojia', 'Gym', 1000); +INSERT INTO courses (title, ctype, cgroup, teacher, location, nmax) VALUES('Flag Football', 'Sport', 'MW2', 'Ding Zhaoyuan', 'Pitch', 1000); +INSERT INTO courses (title, ctype, cgroup, teacher, location, nmax) VALUES('Chamber Music', 'Enrichment', 'TT1', '(?)', 'Music Rooms', 1000); +INSERT INTO courses (title, ctype, cgroup, teacher, location, nmax) VALUES('Math and Computer Science', 'Enrichment', 'TT2', 'Jeff Zhang', '2307', 1000); +INSERT INTO courses (title, ctype, cgroup, teacher, location, nmax) VALUES('Chinese Drama', 'Culture', 'TT2', 'Monica Chen (?)', 'Black Box (?)', 1000); |