1 | BEGIN TRANSACTION; |
---|
2 | |
---|
3 | CREATE TABLE conference ( |
---|
4 | id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, |
---|
5 | title VARCHAR NOT NULL, |
---|
6 | subtitle VARCHAR, |
---|
7 | venue VARCHAR, |
---|
8 | city VARCHAR, |
---|
9 | start INTEGER NOT NULL, -- timezone-less timestamp (Unix Epoch) |
---|
10 | end INTEGER NOT NULL, -- timezone-less timestamp (Unix Epoch) |
---|
11 | day_change INTEGER, |
---|
12 | timeslot_duration INTEGER, |
---|
13 | active INTEGER DEFAULT 0, |
---|
14 | url VARCHAR |
---|
15 | ); |
---|
16 | |
---|
17 | CREATE TABLE track ( |
---|
18 | id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, |
---|
19 | xid_conference INTEGER NOT NULL REFERENCES conference(id), |
---|
20 | name VARCHAR NOT NULL, |
---|
21 | UNIQUE (xid_conference, name) |
---|
22 | ); |
---|
23 | |
---|
24 | CREATE TABLE room ( |
---|
25 | id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, |
---|
26 | xid_conference INTEGER NOT NULL REFERENCES conference(id), |
---|
27 | name VARCHAR NOT NULL, |
---|
28 | picture VARCHAR, |
---|
29 | UNIQUE (xid_conference, name) |
---|
30 | ); |
---|
31 | |
---|
32 | CREATE TABLE person ( |
---|
33 | id INTEGER NOT NULL, |
---|
34 | xid_conference INTEGER NOT NULL REFERENCES conference(id), |
---|
35 | name VARCHAR NOT NULL, |
---|
36 | PRIMARY KEY (id, xid_conference) |
---|
37 | ); |
---|
38 | |
---|
39 | CREATE TABLE event ( |
---|
40 | xid_conference INTEGER NOT NULL REFERENCES conference(id), |
---|
41 | id INTEGER NOT NULL, |
---|
42 | start INTEGER NOT NULL, -- timezone-less timestamp (Unix Epoch) |
---|
43 | duration INTEGER NOT NULL, -- duration of the event in seconds |
---|
44 | xid_track INTEGER NOT NULL REFERENCES track(id), |
---|
45 | type VARCHAR, |
---|
46 | language VARCHAR, |
---|
47 | tag VARCHAR, |
---|
48 | title VARCHAR NOT NULL, |
---|
49 | subtitle VARCHAR, |
---|
50 | abstract VARCHAR, |
---|
51 | description VARCHAR, |
---|
52 | favourite INTEGER DEFAULT 0, -- 0 ... no favourite, 1 ... strong favourite, 2 ... weak favourite/alternative to strong favourite |
---|
53 | alarm INTEGER DEFAULT 0, |
---|
54 | PRIMARY KEY (xid_conference, id) |
---|
55 | ); |
---|
56 | |
---|
57 | CREATE TABLE event_person ( |
---|
58 | xid_conference INTEGER NOT NULL, |
---|
59 | xid_event INTEGER NOT NULL, |
---|
60 | xid_person INTEGER NOT NULL, |
---|
61 | UNIQUE (xid_conference, xid_event, xid_person ) ON CONFLICT REPLACE, |
---|
62 | FOREIGN KEY(xid_conference) REFERENCES conference(id), |
---|
63 | FOREIGN KEY(xid_conference, xid_event) REFERENCES event(xid_conference, id), |
---|
64 | FOREIGN KEY(xid_conference, xid_person) REFERENCES person(xid_conference, id) |
---|
65 | ); |
---|
66 | |
---|
67 | CREATE TABLE event_room ( |
---|
68 | xid_conference INTEGER NOT NULL, |
---|
69 | xid_event INTEGER NOT NULL, |
---|
70 | xid_room INTEGER NOT NULL, |
---|
71 | UNIQUE (xid_conference, xid_event, xid_room) ON CONFLICT REPLACE, |
---|
72 | FOREIGN KEY(xid_conference) REFERENCES conference(id), |
---|
73 | FOREIGN KEY(xid_conference, xid_event) REFERENCES event(xid_conference, id), |
---|
74 | FOREIGN KEY(xid_conference, xid_room) REFERENCES room(xid_conference, id) |
---|
75 | ); |
---|
76 | |
---|
77 | CREATE TABLE link ( |
---|
78 | id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, |
---|
79 | xid_conference INTEGER NOT NULL, |
---|
80 | xid_event INTEGER NOT NULL, |
---|
81 | name VARCHAR, |
---|
82 | url VARCHAR NOT NULL, |
---|
83 | UNIQUE (xid_conference, xid_event , url) ON CONFLICT REPLACE, |
---|
84 | FOREIGN KEY(xid_conference) REFERENCES conference(id), |
---|
85 | FOREIGN KEY(xid_conference, xid_event) REFERENCES event(xid_conference, id) |
---|
86 | ); |
---|
87 | |
---|
88 | PRAGMA user_version=1; |
---|
89 | |
---|
90 | COMMIT; |
---|