From: Shane Synan Date: Sun, 13 May 2018 20:46:11 +0000 (-0500) Subject: core: Migrate SQLite schema to MSec, warn of slow X-Git-Tag: travis-deploy-test~105 X-Git-Url: https://git.quassel-irc.org/?p=quassel.git;a=commitdiff_plain;h=56f686f70bbb212d83803be88adec6fdd225ea8e core: Migrate SQLite schema to MSec, warn of slow Migrate SQLite schema to read and store time in milliseconds, offering greater precision matching that of PostgreSQL. On upgrade, run "UPDATE backlog SET time = time * 1000" to adjust existing times. This is a full backlog update, potentially taking minutes to hours. Based on testing, this was the fastest "proper" way to migrate an existing database. A pessimistic scenario, 328 MB database on a Raspberry Pi 2 with Ubuntu 16.04 running from a Class 10 SD card takes... > In-place update (chosen method): 361.991s > Create new table, multiply int: 905.749s > Create new table, numeric: 983.501s A normal HDD is much faster, e.g. 97.686s for the same database on a 5400 RPM drive, Seagate ST96812AS. (Thanks @romibi for testing!) Other methods may be faster, but may be more prone to mistakes down the road. Update the schema upgrade message to warn of delays for major upgrades, and provide feedback when upgrade succeeds. > Installed Schema (version %1) is not up to date. Upgrading to > version %2... This may take a while for major upgrades. > Installed Schema successfully upgraded to version %1. CAUTION: Monolithic does not show any migration GUI. Monolithic installs with large databases on slower computers may appear to not be responsive. Include a mention of upgrade time in the release notes. --- diff --git a/src/core/SQL/SQLite/version/31/upgrade_000_update_buffer_set_time_extended.sql b/src/core/SQL/SQLite/version/31/upgrade_000_update_buffer_set_time_extended.sql new file mode 100644 index 00000000..062dc516 --- /dev/null +++ b/src/core/SQL/SQLite/version/31/upgrade_000_update_buffer_set_time_extended.sql @@ -0,0 +1 @@ +UPDATE backlog SET time = time * 1000 diff --git a/src/core/abstractsqlstorage.cpp b/src/core/abstractsqlstorage.cpp index 9fe95b9d..7e436fce 100644 --- a/src/core/abstractsqlstorage.cpp +++ b/src/core/abstractsqlstorage.cpp @@ -139,11 +139,20 @@ Storage::State AbstractSqlStorage::init(const QVariantMap &settings, } if (installedSchemaVersion() < schemaVersion()) { - qWarning() << qPrintable(tr("Installed Schema (version %1) is not up to date. Upgrading to version %2...").arg(installedSchemaVersion()).arg(schemaVersion())); + qWarning() << qPrintable(tr("Installed Schema (version %1) is not up to date. Upgrading to " + "version %2... This may take a while for major upgrades." + ).arg(installedSchemaVersion()).arg(schemaVersion())); + // TODO: The monolithic client won't show this message unless one looks into the debug log. + // This should be made more friendly, e.g. a popup message in the GUI. if (!upgradeDb()) { qWarning() << qPrintable(tr("Upgrade failed...")); return NotAvailable; } + // Warning messages are also sent to the console, while Info messages aren't. Add a message + // when migration succeeds to avoid confusing folks by implying the schema upgrade failed if + // later functionality does not work. + qWarning() << qPrintable(tr("Installed Schema successfully upgraded to version %1." + ).arg(schemaVersion())); } quInfo() << qPrintable(displayName()) << "storage backend is ready. Schema version:" << installedSchemaVersion(); diff --git a/src/core/sql.qrc b/src/core/sql.qrc index fa20173c..7a883993 100644 --- a/src/core/sql.qrc +++ b/src/core/sql.qrc @@ -347,5 +347,6 @@ ./SQL/SQLite/version/29/upgrade_030_rename_sender_tmp_sender.sql ./SQL/SQLite/version/29/upgrade_040_update_sender_add_realname_avatarurl.sql ./SQL/SQLite/version/30/upgrade_000_create_corestate.sql + ./SQL/SQLite/version/31/upgrade_000_update_buffer_set_time_extended.sql diff --git a/src/core/sqlitestorage.cpp b/src/core/sqlitestorage.cpp index e40150c4..db174cb4 100644 --- a/src/core/sqlitestorage.cpp +++ b/src/core/sqlitestorage.cpp @@ -1756,18 +1756,9 @@ bool SqliteStorage::logMessage(Message &msg) { QSqlQuery logMessageQuery(db); logMessageQuery.prepare(queryString("insert_message")); - // Store timestamp in seconds as 64-bit integer - // - // NOTE: This is a loss of precision. The database time column would need to store a - // fractional number to support toMSecsSinceEpoch(), or an upgrade step would need to - // convert all past times to milliseconds, multiplying by 1000. -#if QT_VERSION >= 0x050800 - logMessageQuery.bindValue(":time", msg.timestamp().toSecsSinceEpoch()); -#else - // toSecsSinceEpoch() was added in Qt 5.8. Manually downconvert to seconds for now. - // See https://doc.qt.io/qt-5/qdatetime.html#toMSecsSinceEpoch - logMessageQuery.bindValue(":time", (qint64)(msg.timestamp().toMSecsSinceEpoch() / 1000)); -#endif + // As of SQLite schema version 31, timestamps are stored in milliseconds instead of + // seconds. This nets us more precision as well as simplifying 64-bit time. + logMessageQuery.bindValue(":time", msg.timestamp().toMSecsSinceEpoch()); logMessageQuery.bindValue(":bufferid", msg.bufferInfo().bufferId().toInt()); logMessageQuery.bindValue(":type", msg.type()); logMessageQuery.bindValue(":flags", (int)msg.flags()); @@ -1849,19 +1840,9 @@ bool SqliteStorage::logMessages(MessageList &msgs) logMessageQuery.prepare(queryString("insert_message")); for (int i = 0; i < msgs.count(); i++) { Message &msg = msgs[i]; - // Store timestamp in seconds as 64-bit integer - // - // NOTE: This is a loss of precision. The database time column would need to store a - // fractional number to support toMSecsSinceEpoch(), or an upgrade step would need to - // convert all past times to milliseconds, multiplying by 1000. -#if QT_VERSION >= 0x050800 - logMessageQuery.bindValue(":time", msg.timestamp().toSecsSinceEpoch()); -#else - // toSecsSinceEpoch() was added in Qt 5.8. Manually downconvert to seconds for now. - // See https://doc.qt.io/qt-5/qdatetime.html#toMSecsSinceEpoch - logMessageQuery.bindValue(":time", - (qint64)(msg.timestamp().toMSecsSinceEpoch() / 1000)); -#endif + // As of SQLite schema version 31, timestamps are stored in milliseconds instead of + // seconds. This nets us more precision as well as simplifying 64-bit time. + logMessageQuery.bindValue(":time", msg.timestamp().toMSecsSinceEpoch()); logMessageQuery.bindValue(":bufferid", msg.bufferInfo().bufferId().toInt()); logMessageQuery.bindValue(":type", msg.type()); logMessageQuery.bindValue(":flags", (int)msg.flags()); @@ -1951,19 +1932,9 @@ QList SqliteStorage::requestMsgs(UserId user, BufferId bufferId, MsgId while (query.next()) { Message msg( - // Read timestamp in seconds as 64-bit integer - // - // NOTE: This is a loss of precision. The database time column would need to store - // a fractional number to support fromMSecsSinceEpoch(), or an upgrade step would - // need to convert all past times to milliseconds, multiplying by 1000. -#if QT_VERSION >= 0x050800 - QDateTime::fromSecsSinceEpoch(query.value(1).toLongLong()), -#else - // fromSecsSinceEpoch() was added in Qt 5.8. Manually downconvert to seconds for - // now. - // See https://doc.qt.io/qt-5/qdatetime.html#fromMSecsSinceEpoch - QDateTime::fromMSecsSinceEpoch((qint64)(query.value(1).toLongLong() * 1000)), -#endif + // As of SQLite schema version 31, timestamps are stored in milliseconds instead of + // seconds. This nets us more precision as well as simplifying 64-bit time. + QDateTime::fromMSecsSinceEpoch(query.value(1).toLongLong()), bufferInfo, (Message::Type)query.value(2).toInt(), query.value(8).toString(), @@ -2040,21 +2011,10 @@ QList SqliteStorage::requestMsgsFiltered(UserId user, BufferId bufferId while (query.next()) { Message msg( - // Read timestamp in seconds as 64-bit integer - // - // NOTE: This is a loss of precision. The database time column would need - // to store a fractional number to support fromMSecsSinceEpoch(), or an - // upgrade step would need to convert all past times to milliseconds, - // multiplying by 1000. -#if QT_VERSION >= 0x050800 - QDateTime::fromSecsSinceEpoch(query.value(1).toLongLong()), -#else - // fromSecsSinceEpoch() was added in Qt 5.8. Manually downconvert to - // seconds for now. - // See https://doc.qt.io/qt-5/qdatetime.html#fromMSecsSinceEpoch - QDateTime::fromMSecsSinceEpoch( - (qint64)(query.value(1).toLongLong() * 1000)), -#endif + // As of SQLite schema version 31, timestamps are stored in milliseconds + // instead of seconds. This nets us more precision as well as simplifying + // 64-bit time. + QDateTime::fromMSecsSinceEpoch(query.value(1).toLongLong()), bufferInfo, (Message::Type)query.value(2).toInt(), query.value(8).toString(), @@ -2112,19 +2072,9 @@ QList SqliteStorage::requestAllMsgs(UserId user, MsgId first, MsgId las while (query.next()) { Message msg( - // Read timestamp in seconds as 64-bit integer - // - // NOTE: This is a loss of precision. The database time column would need to store - // a fractional number to support fromMSecsSinceEpoch(), or an upgrade step would - // need to convert all past times to milliseconds, multiplying by 1000. -#if QT_VERSION >= 0x050800 - QDateTime::fromSecsSinceEpoch(query.value(2).toLongLong()), -#else - // fromSecsSinceEpoch() was added in Qt 5.8. Manually downconvert to seconds for - // now. - // See https://doc.qt.io/qt-5/qdatetime.html#fromMSecsSinceEpoch - QDateTime::fromMSecsSinceEpoch((qint64)(query.value(2).toLongLong() * 1000)), -#endif + // As of SQLite schema version 31, timestamps are stored in milliseconds instead of + // seconds. This nets us more precision as well as simplifying 64-bit time. + QDateTime::fromMSecsSinceEpoch(query.value(2).toLongLong()), bufferInfoHash[query.value(1).toInt()], (Message::Type)query.value(3).toInt(), query.value(9).toString(), @@ -2184,21 +2134,10 @@ QList SqliteStorage::requestAllMsgsFiltered(UserId user, MsgId first, M while (query.next()) { Message msg( - // Read timestamp in seconds as 64-bit integer - // - // NOTE: This is a loss of precision. The database time column would need - // to store a fractional number to support fromMSecsSinceEpoch(), or an - // upgrade step would need to convert all past times to milliseconds, - // multiplying by 1000. -#if QT_VERSION >= 0x050800 - QDateTime::fromSecsSinceEpoch(query.value(2).toLongLong()), -#else - // fromSecsSinceEpoch() was added in Qt 5.8. Manually downconvert to - // seconds for now. - // See https://doc.qt.io/qt-5/qdatetime.html#fromMSecsSinceEpoch - QDateTime::fromMSecsSinceEpoch( - (qint64)(query.value(2).toLongLong() * 1000)), -#endif + // As of SQLite schema version 31, timestamps are stored in milliseconds + // instead of seconds. This nets us more precision as well as simplifying + // 64-bit time. + QDateTime::fromMSecsSinceEpoch(query.value(2).toLongLong()), bufferInfoHash[query.value(1).toInt()], (Message::Type)query.value(3).toInt(), query.value(9).toString(), @@ -2519,20 +2458,9 @@ bool SqliteMigrationReader::readMo(BacklogMO &backlog) } backlog.messageid = value(0).toLongLong(); - // Read timestamp in seconds as 64-bit integer - // - // NOTE: This is a loss of precision. The database time column would need to store a - // fractional number to support fromMSecsSinceEpoch(), or an upgrade step would need to convert - // all past times to milliseconds, multiplying by 1000. -#if QT_VERSION >= 0x050800 - backlog.time = QDateTime::fromSecsSinceEpoch(value(1).toLongLong()).toUTC(); -#else - // fromSecsSinceEpoch() was added in Qt 5.8. Manually downconvert to seconds for - // now. - // See https://doc.qt.io/qt-5/qdatetime.html#fromMSecsSinceEpoch - backlog.time = QDateTime::fromMSecsSinceEpoch((qint64)(value(1).toLongLong() * 1000) - ).toUTC(); -#endif + // As of SQLite schema version 31, timestamps are stored in milliseconds instead of + // seconds. This nets us more precision as well as simplifying 64-bit time. + backlog.time = QDateTime::fromMSecsSinceEpoch(value(1).toLongLong()).toUTC(); backlog.bufferid = value(2).toInt(); backlog.type = value(3).toInt(); backlog.flags = value(4).toInt();