source: src/dbschema001.sql @ 79a7671

qt5
Last change on this file since 79a7671 was 95db98d, checked in by gregor herrmann <gregoa@…>, 5 years ago

whitespace

  • Property mode set to 100644
File size: 2.7 KB
RevLine 
[cfe9990]1BEGIN TRANSACTION;
2
[38b61bc]3CREATE TABLE conference (
[4403ab4]4        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
5        title VARCHAR NOT NULL,
6        subtitle VARCHAR,
7        venue VARCHAR,
8        city VARCHAR,
[9457aed]9        start INTEGER NOT NULL, -- timezone-less timestamp (Unix Epoch)
10        end INTEGER NOT NULL,   -- timezone-less timestamp (Unix Epoch)
[4403ab4]11        day_change INTEGER,
12        timeslot_duration INTEGER,
13        active INTEGER DEFAULT 0,
14        url VARCHAR
15);
[cfe9990]16
[38b61bc]17CREATE TABLE track (
[4403ab4]18        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[38b61bc]19        xid_conference INTEGER NOT NULL REFERENCES conference(id),
[4403ab4]20        name VARCHAR NOT NULL,
21        UNIQUE (xid_conference, name)
22);
[cfe9990]23
[38b61bc]24CREATE TABLE room (
[4403ab4]25        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[38b61bc]26        xid_conference INTEGER NOT NULL REFERENCES conference(id),
[4403ab4]27        name VARCHAR NOT NULL,
28        picture VARCHAR,
29        UNIQUE (xid_conference, name)
30);
[cfe9990]31
[38b61bc]32CREATE TABLE person (
[4403ab4]33        id INTEGER NOT NULL,
[38b61bc]34        xid_conference INTEGER NOT NULL REFERENCES conference(id),
[4403ab4]35        name VARCHAR NOT NULL,
36        PRIMARY KEY (id, xid_conference)
37);
[cfe9990]38
[38b61bc]39CREATE TABLE event (
40        xid_conference INTEGER NOT NULL REFERENCES conference(id),
[4403ab4]41        id INTEGER NOT NULL,
[9457aed]42        start INTEGER NOT NULL,    -- timezone-less timestamp (Unix Epoch)
[4403ab4]43        duration INTEGER NOT NULL, -- duration of the event in seconds
[38b61bc]44        xid_track INTEGER NOT NULL REFERENCES track(id),
[4403ab4]45        type VARCHAR,
46        language VARCHAR,
47        tag VARCHAR,
48        title VARCHAR NOT NULL,
49        subtitle VARCHAR,
50        abstract VARCHAR,
51        description VARCHAR,
[95db98d]52        favourite INTEGER DEFAULT 0, -- 0 ... no favourite, 1 ... strong favourite, 2 ... weak favourite/alternative to strong favourite
[4403ab4]53        alarm INTEGER DEFAULT 0,
54        PRIMARY KEY (xid_conference, id)
55);
[cfe9990]56
[38b61bc]57CREATE TABLE event_person (
[4403ab4]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,
[38b61bc]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)
[4403ab4]65);
[cfe9990]66
[38b61bc]67CREATE TABLE event_room (
[4403ab4]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,
[38b61bc]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)
[4403ab4]75);
76
[38b61bc]77CREATE TABLE link (
[4403ab4]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,
[38b61bc]84        FOREIGN KEY(xid_conference) REFERENCES conference(id),
85        FOREIGN KEY(xid_conference, xid_event) REFERENCES event(xid_conference, id)
[4403ab4]86);
[61346c9]87
88PRAGMA user_version=1;
[4403ab4]89
[cfe9990]90COMMIT;
Note: See TracBrowser for help on using the repository browser.