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 (file)
index 0000000..062dc51
--- /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 9fe95b9..7e436fc 100644 (file)
--- 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 fa20173..7a88399 100644 (file)
--- a/src/core/sql.qrc
+++ b/src/core/sql.qrc
@@ -347,5 +347,6 @@
     <file>./SQL/SQLite/version/29/upgrade_030_rename_sender_tmp_sender.sql</file>
     <file>./SQL/SQLite/version/29/upgrade_040_update_sender_add_realname_avatarurl.sql</file>
     <file>./SQL/SQLite/version/30/upgrade_000_create_corestate.sql</file>
+    <file>./SQL/SQLite/version/31/upgrade_000_update_buffer_set_time_extended.sql</file>
 </qresource>
 </RCC>

diff --git a/src/core/sqlitestorage.cpp b/src/core/sqlitestorage.cpp
index e40150c..db174cb 100644 (file)
--- 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<Message> 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<Message> 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<Message> 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<Message> 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();