Add .pro.user.* to svn:ignore and remove it in the release target.

[toast/confclerk.git] / src / create_tables.sql

diff --git a/src/create_tables.sql b/src/create_tables.sql
index 52f782820e809a656b07220df0f84426765d21c5..122c1cf9240720d865145c2648bc023cf3adefac 100644 (file)
--- a/src/create_tables.sql
+++ b/src/create_tables.sql
@@ -10,26 +10,29 @@ CREATE TABLE CONFERENCE ( id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL
     , day_change INTEGER
     , timeslot_duration INTEGER
     , active INTEGER DEFAULT 0
     , day_change INTEGER
     , timeslot_duration INTEGER
     , active INTEGER DEFAULT 0

-    , url VARCHAR UNIQUE
-    , map VARCHAR);
+    , url VARCHAR);

 
 CREATE TABLE TRACK ( id INTEGER  PRIMARY KEY AUTOINCREMENT  NOT NULL
 
 CREATE TABLE TRACK ( id INTEGER  PRIMARY KEY AUTOINCREMENT  NOT NULL

-    , xid_conference INTEGER -- NOT NULL
-    , name VARCHAR UNIQUE NOT NULL );
+    , xid_conference INTEGER NOT NULL
+    , name VARCHAR NOT NULL
+    , UNIQUE (xid_conference, name));

 
 

-CREATE TABLE ROOM ( id INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL
-    , xid_conference INTEGER -- NOT NULL
-    , name VARCHAR UNIQUE NOT NULL
-    , picture VARCHAR NOT NULL);
+CREATE TABLE ROOM ( id INTEGER PRIMARY KEY AUTOINCREMENT  NOT NULL
+    , xid_conference INTEGER NOT NULL
+    , name VARCHAR NOT NULL
+    , picture VARCHAR NOT NULL
+    , UNIQUE (xid_conference, name));

 
 

-CREATE TABLE PERSON ( id INTEGER PRIMARY KEY  NOT NULL
-    , xid_conference INTEGER -- NOT NULL
-    , name VARCHAR UNIQUE NOT NULL);
+CREATE TABLE PERSON ( id INTEGER NOT NULL
+    , xid_conference INTEGER NOT NULL
+    , name VARCHAR NOT NULL
+    , UNIQUE (xid_conference, name)
+    , PRIMARY KEY (id, xid_conference)); 

 
 CREATE TABLE EVENT ( xid_conference INTEGER  NOT NULL
     , id INTEGER NOT NULL
     , start INTEGER NOT NULL
 
 CREATE TABLE EVENT ( xid_conference INTEGER  NOT NULL
     , id INTEGER NOT NULL
     , start INTEGER NOT NULL

-    , duration INTEGER NOT NULL
+    , duration INTEGER NOT NULL -- duration of the event in seconds

     , xid_track INTEGER NOT NULL REFERENCES TRACK(id)
     , type VARCHAR
     , language VARCHAR
     , xid_track INTEGER NOT NULL REFERENCES TRACK(id)
     , type VARCHAR
     , language VARCHAR


@@ -50,8 +53,7 @@ CREATE TABLE EVENT_PERSON ( xid_conference INTEGER NOT NULL
     , UNIQUE ( xid_conference , xid_event , xid_person ) ON CONFLICT REPLACE
     , FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id)
     , FOREIGN KEY(xid_conference, xid_event) REFERENCES EVENT(xid_conference, id)
     , UNIQUE ( xid_conference , xid_event , xid_person ) ON CONFLICT REPLACE
     , FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id)
     , FOREIGN KEY(xid_conference, xid_event) REFERENCES EVENT(xid_conference, id)

-    , FOREIGN KEY(xid_person) REFERENCES PERSON(id));
-
+    , FOREIGN KEY(xid_conference, xid_person) REFERENCES PERSON(xid_conference, id));

 
 CREATE TABLE EVENT_ROOM ( xid_conference INTEGER NOT NULL
     , xid_event INTEGER NOT NULL
 
 CREATE TABLE EVENT_ROOM ( xid_conference INTEGER NOT NULL
     , xid_event INTEGER NOT NULL


@@ -59,7 +61,7 @@ CREATE TABLE EVENT_ROOM ( xid_conference INTEGER NOT NULL
     , UNIQUE ( xid_conference , xid_event , xid_room ) ON CONFLICT REPLACE
     , FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id)
     , FOREIGN KEY(xid_conference, xid_event) REFERENCES EVENT(xid_conference, id)
     , UNIQUE ( xid_conference , xid_event , xid_room ) ON CONFLICT REPLACE
     , FOREIGN KEY(xid_conference) REFERENCES CONFERENCE(id)
     , FOREIGN KEY(xid_conference, xid_event) REFERENCES EVENT(xid_conference, id)

-    , FOREIGN KEY(xid_room) REFERENCES ROOM(id));
+    , FOREIGN KEY(xid_conference, xid_room) REFERENCES ROOM(xid_conference, id));

 
 CREATE TABLE LINK ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
     , xid_conference INTEGER NOT NULL
 
 CREATE TABLE LINK ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
     , xid_conference INTEGER NOT NULL