Fix SQLite buffer setup, fix PostgreSQL migration

Remove the ALTER TABLE command, this isn't needed during setup as the
buffer's getting created for the first time.

Add the CHECK constraint as seen in the upgrade script, limiting the
value of lastseenmsgid.

Add handling for lastmsgid when migrating from SQLite to PostgreSQL.

Fix 'SELECT populate_lastmsgid()' call by first resetting the query.

This fixes breakages caused by incomplete work in pull request #273.

diff --git a/src/core/SQL/PostgreSQL/20/migrate_write_buffer.sql b/src/core/SQL/PostgreSQL/20/migrate_write_buffer.sql
index cfa900b..35ef61d 100644 (file)
--- a/src/core/SQL/PostgreSQL/20/migrate_write_buffer.sql
+++ b/src/core/SQL/PostgreSQL/20/migrate_write_buffer.sql
@@ -1,2 +1,2 @@
-INSERT INTO buffer (bufferid, userid, groupid, networkid, buffername, buffercname, buffertype, lastseenmsgid, markerlinemsgid, key, joined)
-VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
+INSERT INTO buffer (bufferid, userid, groupid, networkid, buffername, buffercname, buffertype, lastmsgid, lastseenmsgid, markerlinemsgid, key, joined)
+VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

diff --git a/src/core/SQL/SQLite/21/migrate_read_buffer.sql b/src/core/SQL/SQLite/21/migrate_read_buffer.sql
index 828b660..a2cc558 100644 (file)
--- a/src/core/SQL/SQLite/21/migrate_read_buffer.sql
+++ b/src/core/SQL/SQLite/21/migrate_read_buffer.sql
@@ -1,2 +1,2 @@
-SELECT bufferid, userid, groupid, networkid, buffername, buffercname, buffertype, lastseenmsgid, markerlinemsgid, key, joined
+SELECT bufferid, userid, groupid, networkid, buffername, buffercname, buffertype, lastmsgid, lastseenmsgid, markerlinemsgid, key, joined

 FROM buffer
 FROM buffer

diff --git a/src/core/SQL/SQLite/21/setup_030_buffer.sql b/src/core/SQL/SQLite/21/setup_030_buffer.sql
index 952e20d..66f22fa 100644 (file)
--- a/src/core/SQL/SQLite/21/setup_030_buffer.sql
+++ b/src/core/SQL/SQLite/21/setup_030_buffer.sql
@@ -11,5 +11,5 @@ CREATE TABLE buffer (
        markerlinemsgid INTEGER NOT NULL DEFAULT 0,
        key TEXT,
        joined INTEGER NOT NULL DEFAULT 0, -- BOOL
        markerlinemsgid INTEGER NOT NULL DEFAULT 0,
        key TEXT,
        joined INTEGER NOT NULL DEFAULT 0, -- BOOL

-       ALTER TABLE buffer_new RENAME TO buffer;
+       CHECK (lastseenmsgid <= lastmsgid)

 )
 )

diff --git a/src/core/abstractsqlstorage.h b/src/core/abstractsqlstorage.h
index 0ae1404..fc1f67e 100644 (file)
--- a/src/core/abstractsqlstorage.h
+++ b/src/core/abstractsqlstorage.h
@@ -212,6 +212,7 @@ public:
         QString buffername;
         QString buffercname;
         int buffertype;
         QString buffername;
         QString buffercname;
         int buffertype;

+        int lastmsgid;

         int lastseenmsgid;
         int markerlinemsgid;
         QString key;
         int lastseenmsgid;
         int markerlinemsgid;
         QString key;

diff --git a/src/core/postgresqlstorage.cpp b/src/core/postgresqlstorage.cpp
index 9c340df..2877ae0 100644 (file)
--- a/src/core/postgresqlstorage.cpp
+++ b/src/core/postgresqlstorage.cpp
@@ -1969,10 +1969,11 @@ bool PostgreSqlMigrationWriter::writeMo(const BufferMO &buffer)
     bindValue(4, buffer.buffername);
     bindValue(5, buffer.buffercname);
     bindValue(6, (int)buffer.buffertype);
     bindValue(4, buffer.buffername);
     bindValue(5, buffer.buffercname);
     bindValue(6, (int)buffer.buffertype);

-    bindValue(7, buffer.lastseenmsgid);
-    bindValue(8, buffer.markerlinemsgid);
-    bindValue(9, buffer.key);
-    bindValue(10, buffer.joined);
+    bindValue(7, buffer.lastmsgid);
+    bindValue(8, buffer.lastseenmsgid);
+    bindValue(9, buffer.markerlinemsgid);
+    bindValue(10, buffer.key);
+    bindValue(11, buffer.joined);

     return exec();
 }
 
     return exec();
 }
 


@@ -2043,6 +2044,8 @@ bool PostgreSqlMigrationWriter::postProcess()
             return false;
     }
 
             return false;
     }
 

+    // Update the lastmsgid for all existing buffers.
+    resetQuery();

     newQuery(QString("SELECT populate_lastmsgid()"), db);
     if (!exec())
         return false;
     newQuery(QString("SELECT populate_lastmsgid()"), db);
     if (!exec())
         return false;

diff --git a/src/core/sqlitestorage.cpp b/src/core/sqlitestorage.cpp
index 99e97b5..f671c39 100644 (file)
--- a/src/core/sqlitestorage.cpp
+++ b/src/core/sqlitestorage.cpp
@@ -1930,10 +1930,11 @@ bool SqliteMigrationReader::readMo(BufferMO &buffer)
     buffer.buffername = value(4).toString();
     buffer.buffercname = value(5).toString();
     buffer.buffertype = value(6).toInt();
     buffer.buffername = value(4).toString();
     buffer.buffercname = value(5).toString();
     buffer.buffertype = value(6).toInt();

-    buffer.lastseenmsgid = value(7).toInt();
-    buffer.markerlinemsgid = value(8).toInt();
-    buffer.key = value(9).toString();
-    buffer.joined = value(10).toInt() == 1 ? true : false;
+    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;

     return true;
 }
 
     return true;
 }