source: src/dbschema000.sql @ 79a7671

qt5
Last change on this file since 79a7671 was 61346c9, checked in by Philipp Spitzer <philipp@…>, 10 years ago

Restructured the SqlEngine?. Not yet finished (see "TODO" in the code).

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