source: src/dbschema001.sql @ 5293ef0

qt5
Last change on this file since 5293ef0 was 14c46c7, checked in by Philipp Spitzer <philipp@…>, 10 years ago

Suggestion for database schema version 001.

  • Property mode set to 100644
File size: 2.7 KB
Line 
1BEGIN TRANSACTION;
2CREATE TABLE CONFERENCE ( id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL
3    , title VARCHAR NOT NULL
4    , subtitle VARCHAR
5    , venue VARCHAR
6    , city VARCHAR
7    , start INTEGER NOT NULL -- timestamp (Unix Epoch)
8    , end INTEGER NOT NULL   -- timestamp (Unix Epoch)
9    , day_change INTEGER
10    , timeslot_duration INTEGER
11    , active INTEGER DEFAULT 0
12    , url VARCHAR);
13
14CREATE TABLE TRACK ( id INTEGER  PRIMARY KEY AUTOINCREMENT  NOT NULL
15    , xid_conference INTEGER NOT NULL REFERENCES CONFERENCE(id)
16    , name VARCHAR NOT NULL
17    , UNIQUE (xid_conference, name));
18
19CREATE TABLE ROOM ( id INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL
20    , xid_conference INTEGER NOT NULL REFERENCES CONFERENCE(id)
21    , name VARCHAR NOT NULL
22    , picture VARCHAR
23    , UNIQUE (xid_conference, name));
24
25CREATE TABLE PERSON ( id INTEGER NOT NULL
26    , xid_conference INTEGER NOT NULL REFERENCES CONFERENCE(id)
27    , name VARCHAR NOT NULL
28    , PRIMARY KEY (id, xid_conference));
29
30CREATE TABLE EVENT ( xid_conference INTEGER  NOT NULL REFERENCES CONFERENCE(id)
31    , id INTEGER NOT NULL
32    , start INTEGER NOT NULL
33    , duration INTEGER NOT NULL -- duration of the event in seconds
34    , xid_track INTEGER NOT NULL REFERENCES TRACK(id)
35    , type VARCHAR
36    , language VARCHAR
37    , tag VARCHAR
38    , title VARCHAR NOT NULL
39    , subtitle VARCHAR
40    , abstract VARCHAR
41    , description VARCHAR
42    , favourite INTEGER DEFAULT 0
43    , alarm INTEGER DEFAULT 0
44    , PRIMARY KEY (xid_conference ,id));
45
46CREATE TABLE EVENT_PERSON ( xid_conference INTEGER NOT NULL
47    , xid_event INTEGER NOT NULL
48    , xid_person INTEGER NOT NULL
49    , UNIQUE ( xid_conference , xid_event , xid_person ) ON CONFLICT REPLACE
50    , FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id)
51    , FOREIGN KEY(xid_conference, xid_event) REFERENCES EVENT(xid_conference, id)
52    , FOREIGN KEY(xid_conference, xid_person) REFERENCES PERSON(xid_conference, id));
53
54CREATE TABLE EVENT_ROOM ( xid_conference INTEGER NOT NULL
55    , xid_event INTEGER NOT NULL
56    , xid_room INTEGER NOT NULL
57    , UNIQUE ( xid_conference , xid_event , xid_room ) ON CONFLICT REPLACE
58    , FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id)
59    , FOREIGN KEY(xid_conference, xid_event) REFERENCES EVENT(xid_conference, id)
60    , FOREIGN KEY(xid_conference, xid_room) REFERENCES ROOM(xid_conference, id));
61
62CREATE TABLE LINK ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
63    , xid_conference INTEGER NOT NULL
64    , xid_event INTEGER NOT NULL
65    , name VARCHAR
66    , url VARCHAR NOT NULL
67    , UNIQUE ( xid_conference , xid_event , url ) ON CONFLICT REPLACE
68    , FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id)
69    , FOREIGN KEY(xid_conference, xid_event) REFERENCES EVENT(xid_conference, id));
70
71PRAGMA user_version=1;
72COMMIT;
Note: See TracBrowser for help on using the repository browser.