From c13a48874a074964506600352b4291647838d881 Mon Sep 17 00:00:00 2001 From: Philipp Spitzer Date: Wed, 21 Jul 2021 19:54:38 +0200 Subject: [PATCH] Add utc_offset and display_time_shift columns to conference database. --- src/db.qrc | 4 +- src/dbschema001to002.sql | 9 ++++ src/dbschema002.sql | 92 ++++++++++++++++++++++++++++++++++++++++ src/sql/sqlengine.cpp | 10 ++++- src/sql/sqlengine.h | 1 + 5 files changed, 113 insertions(+), 3 deletions(-) create mode 100644 src/dbschema001to002.sql create mode 100644 src/dbschema002.sql diff --git a/src/db.qrc b/src/db.qrc index faa1eac..705c934 100644 --- a/src/db.qrc +++ b/src/db.qrc @@ -1,7 +1,7 @@ - dbschema001.sql dbschema000to001.sql + dbschema001to002.sql + dbschema002.sql - diff --git a/src/dbschema001to002.sql b/src/dbschema001to002.sql new file mode 100644 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 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 a3cf22a..ae4a016 100644 --- 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 0a0c277..a428e74 100644 --- 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, -- 2.39.5