source: confclerk_git/src/dbschema001.sql @ 38b61bc

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

Changed table names to have small letters.

  • Property mode set to 100644
File size: 2.5 KB
Line 
1BEGIN TRANSACTION;
2
3CREATE 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, -- timestamp (Unix Epoch)
10        end INTEGER NOT NULL,   -- timestamp (Unix Epoch)
11        day_change INTEGER,
12        timeslot_duration INTEGER,
13        active INTEGER DEFAULT 0,
14        url VARCHAR
15);
16
17CREATE 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
24CREATE 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
32CREATE 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
39CREATE TABLE event (
40        xid_conference INTEGER NOT NULL REFERENCES conference(id),
41        id INTEGER NOT NULL,
42        start INTEGER NOT NULL,
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,
53        alarm INTEGER DEFAULT 0,
54        PRIMARY KEY (xid_conference, id)
55);
56
57CREATE 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
67CREATE 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
77CREATE 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
88PRAGMA user_version=1;
89
90COMMIT;
Note: See TracBrowser for help on using the repository browser.