source: src/create_tables.sql @ cfe9990

qt5
Last change on this file since cfe9990 was cfe9990, checked in by Philipp Spitzer <philipp@…>, 11 years ago

Created schema for the database with additional colum xid_conference in the tables track, room and person.

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