source: src/create_tables.sql @ 68b2df2

qt5
Last change on this file since 68b2df2 was 865aab6, checked in by Philipp Spitzer <philipp@…>, 10 years ago

Importing persons for multiple conferences works now.

  • 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 UNIQUE NOT NULL
4    , subtitle VARCHAR
5    , venue VARCHAR
6    , city VARCHAR NOT NULL
7    , start INTEGER NOT NULL
8    , end INTEGER NOT NULL
9    , days INTEGER
10    , day_change INTEGER
11    , timeslot_duration INTEGER
12    , active INTEGER DEFAULT 0
13    , url VARCHAR
14    , map VARCHAR);
15
16CREATE TABLE TRACK ( id INTEGER  PRIMARY KEY AUTOINCREMENT  NOT NULL
17    , xid_conference INTEGER NOT NULL
18    , name VARCHAR NOT NULL
19    , UNIQUE (xid_conference, name));
20
21CREATE TABLE ROOM ( id INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL
22    , xid_conference INTEGER NOT NULL
23    , name VARCHAR NOT NULL
24    , picture VARCHAR NOT NULL
25    , UNIQUE (xid_conference, name));
26
27CREATE TABLE PERSON ( id INTEGER NOT NULL
28    , xid_conference INTEGER NOT NULL
29    , name VARCHAR NOT NULL
30    , UNIQUE (xid_conference, name)
31    , PRIMARY KEY (id, xid_conference));
32
33CREATE TABLE EVENT ( xid_conference INTEGER  NOT NULL
34    , id INTEGER NOT NULL
35    , start INTEGER NOT NULL
36    , duration INTEGER NOT NULL
37    , xid_track INTEGER NOT NULL REFERENCES TRACK(id)
38    , type VARCHAR
39    , language VARCHAR
40    , tag VARCHAR
41    , title VARCHAR NOT NULL
42    , subtitle VARCHAR
43    , abstract VARCHAR
44    , description VARCHAR
45    , favourite INTEGER DEFAULT 0
46    , alarm INTEGER DEFAULT 0
47    , PRIMARY KEY (xid_conference ,id)
48    , FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id)
49    , FOREIGN KEY(xid_track) REFERENCES TRACK(id));
50
51CREATE TABLE EVENT_PERSON ( xid_conference INTEGER NOT NULL
52    , xid_event INTEGER NOT NULL
53    , xid_person INTEGER NOT NULL
54    , UNIQUE ( xid_conference , xid_event , xid_person ) ON CONFLICT REPLACE
55    , FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id)
56    , FOREIGN KEY(xid_conference, xid_event) REFERENCES EVENT(xid_conference, id)
57    , FOREIGN KEY(xid_conference, xid_person) REFERENCES PERSON(xid_conference, id));
58
59CREATE TABLE EVENT_ROOM ( xid_conference INTEGER NOT NULL
60    , xid_event INTEGER NOT NULL
61    , xid_room INTEGER NOT NULL
62    , UNIQUE ( xid_conference , xid_event , xid_room ) ON CONFLICT REPLACE
63    , FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id)
64    , FOREIGN KEY(xid_conference, xid_event) REFERENCES EVENT(xid_conference, id)
65    , FOREIGN KEY(xid_conference, xid_room) REFERENCES ROOM(xid_conference, id));
66
67CREATE TABLE LINK ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
68    , xid_conference INTEGER NOT NULL
69    , xid_event INTEGER NOT NULL
70    , name VARCHAR
71    , url VARCHAR NOT NULL
72    , UNIQUE ( xid_conference , xid_event , url ) ON CONFLICT REPLACE
73    , FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id)
74    , FOREIGN KEY(xid_conference, xid_event) REFERENCES EVENT(xid_conference, id));
75COMMIT;
Note: See TracBrowser for help on using the repository browser.