From: Janne Koschinski Date: Fri, 25 Aug 2017 21:09:00 +0000 (+0200) Subject: Implement database backend for buffer-activity patch X-Git-Tag: travis-deploy-test~250 X-Git-Url: https://git.quassel-irc.org/?p=quassel.git;a=commitdiff_plain;h=e5d6f457a0f3eef578dcb9d7e4a3db61826dc232 Implement database backend for buffer-activity patch --- diff --git a/src/core/SQL/PostgreSQL/migrate_write_buffer.sql b/src/core/SQL/PostgreSQL/migrate_write_buffer.sql index 35ef61da..8f1e5e9f 100644 --- a/src/core/SQL/PostgreSQL/migrate_write_buffer.sql +++ b/src/core/SQL/PostgreSQL/migrate_write_buffer.sql @@ -1,2 +1,2 @@ -INSERT INTO buffer (bufferid, userid, groupid, networkid, buffername, buffercname, buffertype, lastmsgid, lastseenmsgid, markerlinemsgid, key, joined) +INSERT INTO buffer (bufferid, userid, groupid, networkid, buffername, buffercname, buffertype, lastmsgid, lastseenmsgid, markerlinemsgid, bufferactivity, key, joined) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) diff --git a/src/core/SQL/PostgreSQL/select_buffer_bufferactivities.sql b/src/core/SQL/PostgreSQL/select_buffer_bufferactivities.sql new file mode 100644 index 00000000..e40467cd --- /dev/null +++ b/src/core/SQL/PostgreSQL/select_buffer_bufferactivities.sql @@ -0,0 +1,3 @@ +SELECT bufferid, bufferactivity +FROM buffer +WHERE userid = :userid \ No newline at end of file diff --git a/src/core/SQL/PostgreSQL/select_buffer_bufferactivity.sql b/src/core/SQL/PostgreSQL/select_buffer_bufferactivity.sql new file mode 100644 index 00000000..294a6ccb --- /dev/null +++ b/src/core/SQL/PostgreSQL/select_buffer_bufferactivity.sql @@ -0,0 +1,6 @@ +SELECT COALESCE(SUM(t.type),0) +FROM + (SELECT DISTINCT TYPE + FROM backlog + WHERE bufferid = :bufferid + AND messageid > :lastseenmsgid) t; \ No newline at end of file diff --git a/src/core/SQL/PostgreSQL/setup_050_buffer.sql b/src/core/SQL/PostgreSQL/setup_050_buffer.sql index a9206acb..a10265b5 100644 --- a/src/core/SQL/PostgreSQL/setup_050_buffer.sql +++ b/src/core/SQL/PostgreSQL/setup_050_buffer.sql @@ -9,6 +9,7 @@ create TABLE buffer ( lastmsgid integer NOT NULL DEFAULT 0, lastseenmsgid integer NOT NULL DEFAULT 0, markerlinemsgid integer NOT NULL DEFAULT 0, + bufferactivity integer NOT NULL DEFAULT 0, key varchar(128), joined boolean NOT NULL DEFAULT FALSE, -- BOOL UNIQUE(userid, networkid, buffercname), diff --git a/src/core/SQL/PostgreSQL/update_buffer_bufferactivity.sql b/src/core/SQL/PostgreSQL/update_buffer_bufferactivity.sql new file mode 100644 index 00000000..29400b99 --- /dev/null +++ b/src/core/SQL/PostgreSQL/update_buffer_bufferactivity.sql @@ -0,0 +1,3 @@ +UPDATE buffer +SET bufferactivity = :bufferactivity +WHERE userid = :userid AND bufferid = :bufferid \ No newline at end of file diff --git a/src/core/SQL/PostgreSQL/version/23/upgrade_000_alter_buffer_add_bufferactivity.sql b/src/core/SQL/PostgreSQL/version/23/upgrade_000_alter_buffer_add_bufferactivity.sql new file mode 100644 index 00000000..b5cfa6cb --- /dev/null +++ b/src/core/SQL/PostgreSQL/version/23/upgrade_000_alter_buffer_add_bufferactivity.sql @@ -0,0 +1,2 @@ +ALTER TABLE buffer +ADD COLUMN bufferactivity integer NOT NULL DEFAULT 0 \ No newline at end of file diff --git a/src/core/SQL/SQLite/migrate_read_buffer.sql b/src/core/SQL/SQLite/migrate_read_buffer.sql index a2cc558e..5d200375 100644 --- a/src/core/SQL/SQLite/migrate_read_buffer.sql +++ b/src/core/SQL/SQLite/migrate_read_buffer.sql @@ -1,2 +1,2 @@ -SELECT bufferid, userid, groupid, networkid, buffername, buffercname, buffertype, lastmsgid, lastseenmsgid, markerlinemsgid, key, joined +SELECT bufferid, userid, groupid, networkid, buffername, buffercname, buffertype, lastmsgid, lastseenmsgid, markerlinemsgid, bufferactivity, key, joined FROM buffer diff --git a/src/core/SQL/SQLite/select_buffer_bufferactivity.sql b/src/core/SQL/SQLite/select_buffer_bufferactivity.sql new file mode 100644 index 00000000..294a6ccb --- /dev/null +++ b/src/core/SQL/SQLite/select_buffer_bufferactivity.sql @@ -0,0 +1,6 @@ +SELECT COALESCE(SUM(t.type),0) +FROM + (SELECT DISTINCT TYPE + FROM backlog + WHERE bufferid = :bufferid + AND messageid > :lastseenmsgid) t; \ No newline at end of file diff --git a/src/core/SQL/SQLite/setup_030_buffer.sql b/src/core/SQL/SQLite/setup_030_buffer.sql index 66f22fae..3e09d061 100644 --- a/src/core/SQL/SQLite/setup_030_buffer.sql +++ b/src/core/SQL/SQLite/setup_030_buffer.sql @@ -9,6 +9,7 @@ CREATE TABLE buffer ( lastmsgid INTEGER NOT NULL DEFAULT 0, lastseenmsgid INTEGER NOT NULL DEFAULT 0, markerlinemsgid INTEGER NOT NULL DEFAULT 0, + bufferactivity INTEGER NOT NULL DEFAULT 0, key TEXT, joined INTEGER NOT NULL DEFAULT 0, -- BOOL CHECK (lastseenmsgid <= lastmsgid) diff --git a/src/core/SQL/SQLite/version/24/upgrade_000_alter_buffer_add_bufferactivity.sql b/src/core/SQL/SQLite/version/24/upgrade_000_alter_buffer_add_bufferactivity.sql new file mode 100644 index 00000000..b5cfa6cb --- /dev/null +++ b/src/core/SQL/SQLite/version/24/upgrade_000_alter_buffer_add_bufferactivity.sql @@ -0,0 +1,2 @@ +ALTER TABLE buffer +ADD COLUMN bufferactivity integer NOT NULL DEFAULT 0 \ No newline at end of file diff --git a/src/core/abstractsqlstorage.h b/src/core/abstractsqlstorage.h index 91950cc9..6eb5f8e5 100644 --- a/src/core/abstractsqlstorage.h +++ b/src/core/abstractsqlstorage.h @@ -233,6 +233,7 @@ public: int lastmsgid; int lastseenmsgid; int markerlinemsgid; + int bufferactivity; QString key; bool joined; }; diff --git a/src/core/postgresqlstorage.cpp b/src/core/postgresqlstorage.cpp index db8387f3..1adb749b 100644 --- a/src/core/postgresqlstorage.cpp +++ b/src/core/postgresqlstorage.cpp @@ -1385,6 +1385,61 @@ QHash PostgreSqlStorage::bufferMarkerLineMsgIds(UserId user) return markerLineHash; } + +void PostgreSqlStorage::setBufferActivity(UserId user, const BufferId &bufferId, const int &bufferActivity) +{ + QSqlQuery query(logDb()); + query.prepare(queryString("update_buffer_bufferactivity")); + + query.bindValue(":userid", user.toInt()); + query.bindValue(":bufferid", bufferId.toInt()); + query.bindValue(":bufferactivity", bufferActivity); + safeExec(query); + watchQuery(query); +} + +QHash PostgreSqlStorage::bufferActivities(UserId user) +{ + QHash bufferActivityHash; + + QSqlDatabase db = logDb(); + if (!beginReadOnlyTransaction(db)) { + qWarning() << "PostgreSqlStorage::bufferActivities(): cannot start read only transaction!"; + qWarning() << " -" << qPrintable(db.lastError().text()); + return bufferActivityHash; + } + + QSqlQuery query(db); + query.prepare(queryString("select_buffer_bufferactivity")); + query.bindValue(":userid", user.toInt()); + safeExec(query); + if (!watchQuery(query)) { + db.rollback(); + return bufferActivityHash; + } + + while (query.next()) { + bufferActivityHash[query.value(0).toInt()] = (Message::Type) query.value(1).toInt(); + } + + db.commit(); + return bufferActivityHash; +} + +int PostgreSqlStorage::bufferActivity(BufferId &bufferId, MsgId &lastSeenMsgId) +{ + QSqlQuery query(logDb()); + query.prepare(queryString("select_buffer_bufferactivity")); + query.bindValue(":bufferid", bufferId.toInt()); + query.bindValue(":lastseenmsgid", lastSeenMsgId.toInt()); + safeExec(query); + watchQuery(query); + int result = 0; + if (query.first()) + result = query.value(0).toInt(); + return result; +} + bool PostgreSqlStorage::logMessage(Message &msg) { QSqlDatabase db = logDb(); @@ -1978,8 +2033,9 @@ bool PostgreSqlMigrationWriter::writeMo(const BufferMO &buffer) bindValue(7, buffer.lastmsgid); bindValue(8, buffer.lastseenmsgid); bindValue(9, buffer.markerlinemsgid); - bindValue(10, buffer.key); - bindValue(11, buffer.joined); + bindValue(10, buffer.bufferactivity); + bindValue(11, buffer.key); + bindValue(12, buffer.joined); return exec(); } diff --git a/src/core/postgresqlstorage.h b/src/core/postgresqlstorage.h index c11a819e..b248aecf 100644 --- a/src/core/postgresqlstorage.h +++ b/src/core/postgresqlstorage.h @@ -96,6 +96,9 @@ public slots: virtual QHash bufferLastSeenMsgIds(UserId user); virtual void setBufferMarkerLineMsg(UserId user, const BufferId &bufferId, const MsgId &msgId); virtual QHash bufferMarkerLineMsgIds(UserId user); + virtual void setBufferActivity(UserId id, const BufferId &bufferId, const int &type); + virtual QHash bufferActivities(UserId id); + virtual int bufferActivity(BufferId &bufferId, MsgId &lastSeenMsgId); /* Message handling */ virtual bool logMessage(Message &msg); diff --git a/src/core/sql.qrc b/src/core/sql.qrc index 63c884ad..c5f1c549 100644 --- a/src/core/sql.qrc +++ b/src/core/sql.qrc @@ -34,6 +34,8 @@ ./SQL/PostgreSQL/select_authuser.sql ./SQL/PostgreSQL/select_bufferByName.sql ./SQL/PostgreSQL/select_bufferExists.sql + ./SQL/PostgreSQL/select_buffer_bufferactivities.sql + ./SQL/PostgreSQL/select_buffer_bufferactivity.sql ./SQL/PostgreSQL/select_buffer_by_id.sql ./SQL/PostgreSQL/select_buffer_lastseen_messages.sql ./SQL/PostgreSQL/select_buffer_markerlinemsgids.sql @@ -75,6 +77,7 @@ ./SQL/PostgreSQL/setup_120_alter_messageid_seq.sql ./SQL/PostgreSQL/setup_130_function_lastmsgid.sql ./SQL/PostgreSQL/update_backlog_bufferid.sql + ./SQL/PostgreSQL/update_buffer_bufferactivity.sql ./SQL/PostgreSQL/update_buffer_lastseen.sql ./SQL/PostgreSQL/update_buffer_markerlinemsgid.sql ./SQL/PostgreSQL/update_buffer_name.sql @@ -104,6 +107,7 @@ ./SQL/PostgreSQL/version/21/upgrade_000_add_function_backlog_lastmsgid_update.sql ./SQL/PostgreSQL/version/21/upgrade_001_add_trigger_backlog_lastmsgid_update.sql ./SQL/PostgreSQL/version/22/upgrade_000_alter_quasseluser_add_authenticator.sql + ./SQL/PostgreSQL/version/23/upgrade_000_alter_buffer_add_bufferactivity.sql ./SQL/PostgreSQL/version/23/upgrade_000_create_senderprefixes.sql ./SQL/SQLite/delete_backlog_by_uid.sql ./SQL/SQLite/delete_backlog_for_buffer.sql @@ -139,6 +143,7 @@ ./SQL/SQLite/select_authuser.sql ./SQL/SQLite/select_bufferByName.sql ./SQL/SQLite/select_bufferExists.sql + ./SQL/SQLite/select_buffer_bufferactivity.sql ./SQL/SQLite/select_buffer_by_id.sql ./SQL/SQLite/select_buffer_lastseen_messages.sql ./SQL/SQLite/select_buffer_markerlinemsgids.sql @@ -282,6 +287,7 @@ ./SQL/SQLite/version/22/upgrade_000_add_trigger_backlog_lastmsgid_update_direct_insert.sql ./SQL/SQLite/version/22/upgrade_001_add_trigger_backlog_lastmsgid_update_direct_update.sql ./SQL/SQLite/version/23/upgrade_000_alter_quasseluser_add_authenticator.sql + ./SQL/SQLite/version/24/upgrade_000_alter_buffer_add_bufferactivity.sql ./SQL/SQLite/version/24/upgrade_000_create_senderprefixes.sql diff --git a/src/core/sqlitestorage.cpp b/src/core/sqlitestorage.cpp index 9fcd2b95..66c85dc0 100644 --- a/src/core/sqlitestorage.cpp +++ b/src/core/sqlitestorage.cpp @@ -1503,6 +1503,79 @@ QHash SqliteStorage::bufferMarkerLineMsgIds(UserId user) return markerLineHash; } +void SqliteStorage::setBufferActivity(UserId user, const BufferId &bufferId, const int &bufferActivity) +{ + QSqlDatabase db = logDb(); + db.transaction(); + + { + QSqlQuery query(db); + query.prepare(queryString("update_buffer_bufferactivity")); + query.bindValue(":userid", user.toInt()); + query.bindValue(":bufferid", bufferId.toInt()); + query.bindValue(":bufferactivity", bufferActivity); + + lockForWrite(); + safeExec(query); + watchQuery(query); + } + db.commit(); + unlock(); +} + + +QHash SqliteStorage::bufferActivities(UserId user) +{ + QHash bufferActivityHash; + + QSqlDatabase db = logDb(); + db.transaction(); + + bool error = false; + { + QSqlQuery query(db); + query.prepare(queryString("select_buffer_bufferactivities")); + query.bindValue(":userid", user.toInt()); + + lockForRead(); + safeExec(query); + error = !watchQuery(query); + if (!error) { + while (query.next()) { + bufferActivityHash[query.value(0).toInt()] = (Message::Type) query.value(1).toInt(); + } + } + } + + db.commit(); + unlock(); + return bufferActivityHash; +} + + +int SqliteStorage::bufferActivity(BufferId &bufferId, MsgId &lastSeenMsgId) +{ + QSqlDatabase db = logDb(); + db.transaction(); + + int result = 0; + { + QSqlQuery query(db); + query.prepare(queryString("select_buffer_bufferactivity")); + query.bindValue(":bufferid", bufferId.toInt()); + query.bindValue(":lastseenmsgid", lastSeenMsgId.toInt()); + + lockForRead(); + safeExec(query); + if (query.first()) + result = query.value(0).toInt(); + } + + db.commit(); + unlock(); + return result; +} + bool SqliteStorage::logMessage(Message &msg) { QSqlDatabase db = logDb(); @@ -1953,8 +2026,9 @@ bool SqliteMigrationReader::readMo(BufferMO &buffer) buffer.lastmsgid = value(7).toInt(); buffer.lastseenmsgid = value(8).toInt(); buffer.markerlinemsgid = value(9).toInt(); - buffer.key = value(10).toString(); - buffer.joined = value(11).toInt() == 1 ? true : false; + buffer.bufferactivity = value(10).toInt(); + buffer.key = value(11).toString(); + buffer.joined = value(12).toInt() == 1 ? true : false; return true; } diff --git a/src/core/sqlitestorage.h b/src/core/sqlitestorage.h index 54f1cb4e..089f3e77 100644 --- a/src/core/sqlitestorage.h +++ b/src/core/sqlitestorage.h @@ -97,6 +97,9 @@ public slots: virtual QHash bufferLastSeenMsgIds(UserId user); virtual void setBufferMarkerLineMsg(UserId user, const BufferId &bufferId, const MsgId &msgId); virtual QHash bufferMarkerLineMsgIds(UserId user); + virtual void setBufferActivity(UserId id, const BufferId &bufferId, const int &type); + virtual QHash bufferActivities(UserId id); + virtual int bufferActivity(BufferId &bufferId, MsgId &lastSeenMsgId); /* Message handling */ virtual bool logMessage(Message &msg); diff --git a/src/core/storage.h b/src/core/storage.h index fec8d861..5eed79cf 100644 --- a/src/core/storage.h +++ b/src/core/storage.h @@ -384,6 +384,33 @@ public slots: */ virtual QHash bufferMarkerLineMsgIds(UserId user) = 0; + //! Update the BufferActivity for a Buffer + /** This Method is used to make the activity state of a Buffer persistent + * \note This method is threadsafe. + * + * \param user The Owner of that Buffer + * \param bufferId The buffer id + * \param MsgId The Message id where the marker line should be placed + */ + virtual void setBufferActivity(UserId id, const BufferId &bufferId, const int &type) = 0; + + //! Get a Hash of all buffer activity states + /** This Method is called when the Quassel Core is started to restore the BufferActivities + * \note This method is threadsafe. + * + * \param user The Owner of the buffers + */ + virtual QHash bufferActivities(UserId id) = 0; + + //! Get the bitset of buffer activity states for a buffer + /** This method is used to load the activity state of a buffer when its last seen message changes. + * \note This method is threadsafe. + * + * \param bufferId The buffer + * \param lastSeenMsgId The last seen message + */ + virtual int bufferActivity(BufferId &bufferId, MsgId &lastSeenMsgId) = 0; + /* Message handling */ //! Store a Message in the storage backend and set its unique Id.