source: src/dbschema000to001.sql @ 31d8a4c

Last change on this file since 31d8a4c was 9457aed, checked in by Philipp Spitzer <philipp@…>, 9 years ago

Added comments to the SQL statements (back in October).

  • Property mode set to 100644
File size: 3.5 KB
Line 
1BEGIN TRANSACTION;
2
3ALTER TABLE conference RENAME TO conference_old;
4ALTER TABLE track RENAME TO track_old;
5ALTER TABLE room RENAME TO room_old;
6ALTER TABLE person RENAME TO person_old;
7ALTER TABLE event RENAME TO event_old;
8ALTER TABLE event_person RENAME TO event_person_old;
9ALTER TABLE event_room RENAME TO event_room_old;
10ALTER TABLE link RENAME TO link_old;
11
12CREATE TABLE conference (
13        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
14        title VARCHAR NOT NULL,
15        subtitle VARCHAR,
16        venue VARCHAR,
17        city VARCHAR,
18        start INTEGER NOT NULL, -- timezone-less timestamp (Unix Epoch)
19        end INTEGER NOT NULL,   -- timezone-less timestamp (Unix Epoch)
20        day_change INTEGER,
21        timeslot_duration INTEGER,
22        active INTEGER DEFAULT 0,
23        url VARCHAR
24);
25
26CREATE TABLE track (
27        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
28        xid_conference INTEGER NOT NULL REFERENCES conference(id),
29        name VARCHAR NOT NULL,
30        UNIQUE (xid_conference, name)
31);
32
33CREATE TABLE room (
34        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
35        xid_conference INTEGER NOT NULL REFERENCES conference(id),
36        name VARCHAR NOT NULL,
37        picture VARCHAR,
38        UNIQUE (xid_conference, name)
39);
40
41CREATE TABLE person (
42        id INTEGER NOT NULL,
43        xid_conference INTEGER NOT NULL REFERENCES conference(id),
44        name VARCHAR NOT NULL,
45        PRIMARY KEY (id, xid_conference)
46);
47
48CREATE TABLE event (
49        xid_conference INTEGER NOT NULL REFERENCES conference(id),
50        id INTEGER NOT NULL,
51        start INTEGER NOT NULL,
52        duration INTEGER NOT NULL, -- duration of the event in seconds
53        xid_track INTEGER NOT NULL REFERENCES track(id),
54        type VARCHAR,
55        language VARCHAR,
56        tag VARCHAR,
57        title VARCHAR NOT NULL,
58        subtitle VARCHAR,
59        abstract VARCHAR,
60        description VARCHAR,
61        favourite INTEGER DEFAULT 0,
62        alarm INTEGER DEFAULT 0,
63        PRIMARY KEY (xid_conference, id)
64);
65
66CREATE TABLE event_person (
67        xid_conference INTEGER NOT NULL,
68        xid_event INTEGER NOT NULL,
69        xid_person INTEGER NOT NULL,
70        UNIQUE (xid_conference, xid_event, xid_person ) ON CONFLICT REPLACE,
71        FOREIGN KEY(xid_conference) REFERENCES conference(id),
72        FOREIGN KEY(xid_conference, xid_event) REFERENCES event(xid_conference, id),
73        FOREIGN KEY(xid_conference, xid_person) REFERENCES person(xid_conference, id)
74);
75
76CREATE TABLE event_room (
77        xid_conference INTEGER NOT NULL,
78        xid_event INTEGER NOT NULL,
79        xid_room INTEGER NOT NULL,
80        UNIQUE (xid_conference, xid_event, xid_room) ON CONFLICT REPLACE,
81        FOREIGN KEY(xid_conference) REFERENCES conference(id),
82        FOREIGN KEY(xid_conference, xid_event) REFERENCES event(xid_conference, id),
83        FOREIGN KEY(xid_conference, xid_room) REFERENCES room(xid_conference, id)
84);
85
86CREATE TABLE link (
87        id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
88        xid_conference INTEGER NOT NULL,
89        xid_event INTEGER NOT NULL,
90        name VARCHAR,
91        url VARCHAR NOT NULL,
92        UNIQUE (xid_conference, xid_event , url) ON CONFLICT REPLACE,
93        FOREIGN KEY(xid_conference) REFERENCES conference(id),
94        FOREIGN KEY(xid_conference, xid_event) REFERENCES event(xid_conference, id)
95);
96
97INSERT INTO conference SELECT id, title, subtitle, venue, city, start, end, day_change, timeslot_duration, active, url FROM conference_old;
98INSERT INTO track SELECT * FROM track_old;
99INSERT INTO room SELECT * FROM room_old;
100INSERT INTO person SELECT * FROM person_old;
101INSERT INTO event SELECT * FROM event_old;
102INSERT INTO event_person SELECT * FROM event_person_old;
103INSERT INTO event_room SELECT * FROM event_room_old;
104INSERT INTO link SELECT * FROM link_old;
105
106DROP TABLE conference_old;
107DROP TABLE track_old;
108DROP TABLE room_old;
109DROP TABLE person_old;
110DROP TABLE event_old;
111DROP TABLE event_person_old;
112DROP TABLE event_room_old;
113DROP TABLE link_old;
114
115PRAGMA user_version=1;
116
117COMMIT;
118
Note: See TracBrowser for help on using the repository browser.