Add utc_offset and display_time_shift columns to conference database.

diff --git a/src/db.qrc b/src/db.qrc
index faa1eac959bd397df6b41cd1f928bb7a9797026b..705c9348b361aa99de6eaff1e88c3e6295cbd1e7 100644 (file)
--- a/src/db.qrc
+++ b/src/db.qrc
@@ -1,7 +1,7 @@
 <!DOCTYPE RCC><RCC version="1.0">
 <qresource>
-    <file>dbschema001.sql</file>
     <file>dbschema000to001.sql</file>
+    <file>dbschema001to002.sql</file>
+    <file>dbschema002.sql</file>
 </qresource>
 </RCC>
-

diff --git a/src/dbschema001to002.sql b/src/dbschema001to002.sql
new file mode 100644 (file)
index 0000000..e497928
--- /dev/null
+++ b/src/dbschema001to002.sql
@@ -0,0 +1,9 @@
+BEGIN TRANSACTION;
+
+ALTER TABLE conference ADD COLUMN utc_offset INTEGER DEFAULT NULL;
+ALTER TABLE conference ADD COLUMN display_time_shift INTEGER DEFAULT NULL;
+
+PRAGMA user_version=2;
+
+COMMIT;
+

diff --git a/src/dbschema002.sql b/src/dbschema002.sql
new file mode 100644 (file)
index 0000000..c0a3d17
--- /dev/null
+++ b/src/dbschema002.sql
@@ -0,0 +1,92 @@
+BEGIN TRANSACTION;
+
+CREATE TABLE conference (
+       id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+       title VARCHAR NOT NULL,
+       subtitle VARCHAR,
+       venue VARCHAR,
+       city VARCHAR,
+       start INTEGER NOT NULL, -- timezone-less timestamp (Unix Epoch)
+       end INTEGER NOT NULL,   -- timezone-less timestamp (Unix Epoch)
+       day_change INTEGER,
+       timeslot_duration INTEGER,
+       utc_offset INTEGER DEFAULT NULL, -- if known, conference UTC offset in seconds (e.g. CET = +3600)
+       display_time_shift INTEGER DEFAULT NULL, -- if not null, add number in seconds to conference times when showing times
+       active INTEGER DEFAULT 0,
+       url VARCHAR
+);
+
+CREATE TABLE track (
+       id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+       xid_conference INTEGER NOT NULL REFERENCES conference(id),
+       name VARCHAR NOT NULL,
+       UNIQUE (xid_conference, name)
+);
+
+CREATE TABLE room (
+       id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
+       xid_conference INTEGER NOT NULL REFERENCES conference(id),
+       name VARCHAR NOT NULL,
+       picture VARCHAR,
+       UNIQUE (xid_conference, name)
+);
+
+CREATE TABLE person (
+       id INTEGER NOT NULL,
+       xid_conference INTEGER NOT NULL REFERENCES conference(id),
+       name VARCHAR NOT NULL,
+       PRIMARY KEY (id, xid_conference)
+); 
+
+CREATE TABLE event (
+       xid_conference INTEGER NOT NULL REFERENCES conference(id),
+       id INTEGER NOT NULL,
+       start INTEGER NOT NULL,    -- timezone-less timestamp (Unix Epoch)
+       duration INTEGER NOT NULL, -- duration of the event in seconds
+       xid_track INTEGER NOT NULL REFERENCES track(id),
+       type VARCHAR,
+       language VARCHAR,
+       tag VARCHAR,
+       title VARCHAR NOT NULL,
+       subtitle VARCHAR,
+       abstract VARCHAR,
+       description VARCHAR,
+       favourite INTEGER DEFAULT 0, -- 0 ... no favourite, 1 ... strong favourite, 2 ... weak favourite/alternative to strong favourite
+       alarm INTEGER DEFAULT 0,
+       PRIMARY KEY (xid_conference, id)
+);
+
+CREATE TABLE event_person (
+       xid_conference INTEGER NOT NULL,
+       xid_event INTEGER NOT NULL,
+       xid_person 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),
+       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,
+       xid_room 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),
+       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,
+       xid_event INTEGER NOT NULL,
+       name VARCHAR,
+       url VARCHAR NOT NULL,
+       UNIQUE (xid_conference, xid_event , url) ON CONFLICT REPLACE,
+       FOREIGN KEY(xid_conference) REFERENCES conference(id),
+       FOREIGN KEY(xid_conference, xid_event) REFERENCES event(xid_conference, id)
+);
+
+PRAGMA user_version=2;
+
+COMMIT;

diff --git a/src/sql/sqlengine.cpp b/src/sql/sqlengine.cpp
index a3cf22ad3df23a5fa022dcdf15988b6a9fc8477e..ae4a0164ac1f9d2f94ba10b766fa9f02ad5b7e79 100644 (file)
--- a/src/sql/sqlengine.cpp
+++ b/src/sql/sqlengine.cpp
@@ -91,8 +91,13 @@ bool SqlEngine::updateDbSchemaVersion000To001() {
 }
 
 
+bool SqlEngine::updateDbSchemaVersion001To002() {
+    return applySqlFile(":/dbschema001to002.sql");
+}
+
+
 bool SqlEngine::createCurrentDbSchema() {
-    return applySqlFile(":/dbschema001.sql");
+    return applySqlFile(":/dbschema002.sql");
 }
 
 
@@ -109,6 +114,9 @@ bool SqlEngine::createOrUpdateDbSchema() {
         // db schema version 0
         return updateDbSchemaVersion000To001();
     case 1:
+        // db schema version 1
+        return updateDbSchemaVersion001To002();
+    case 2:
         // current schema
         return true;
     default:

diff --git a/src/sql/sqlengine.h b/src/sql/sqlengine.h
index 0a0c2778f6efe680595f89e8eb46bef61ddfc44f..a428e7418c6a003374519968373b0794d65937b7 100644 (file)
--- a/src/sql/sqlengine.h
+++ b/src/sql/sqlengine.h
@@ -50,6 +50,7 @@ class SqlEngine : public QObject {
         int dbSchemaVersion();
         /// called by createOrUpdateDbSchema. Do not use directly. true for success.
         bool updateDbSchemaVersion000To001();
+        bool updateDbSchemaVersion001To002();
         /// called by createOrUpdateDbSchma. Do not use directly. true for success.
         bool createCurrentDbSchema();
         /// creates the current database schema if an empty database is found,