[geary/mjog/934-gmail-duplicate-messages: 4/4] sql: Add db migrations to clean up duplicate messages and message_ids

[Michael Gratton <mjog src gnome org>]

commit 766158ce9ab82bc121eb16cf875c5f24d01a97d4
Author: Michael Gratton <mike vee net>
Date:   Sat Aug 22 17:06:50 2020 +1000

    sql: Add db migrations to clean up duplicate messages and message_ids

 sql/version-028.sql |  6 ++++++
 sql/version-029.sql | 10 ++++++++++
 2 files changed, 16 insertions(+)
---
diff --git a/sql/version-028.sql b/sql/version-028.sql
new file mode 100644
index 000000000..ff38053ef
--- /dev/null
+++ b/sql/version-028.sql
@@ -0,0 +1,6 @@
+--
+-- Rebuild corrupted message ids, again
+--
+
+UPDATE MessageTable
+SET message_id = trim(trim(message_id), '\n');
diff --git a/sql/version-029.sql b/sql/version-029.sql
new file mode 100644
index 000000000..2e823ec0b
--- /dev/null
+++ b/sql/version-029.sql
@@ -0,0 +1,10 @@
+--
+-- Drop duplicate messages. These will remove any messages with
+-- non-empty message ids that are duplicates of an earlier
+-- message. Any missing messages will be re-downloaded.
+--
+
+DELETE FROM MessageTable
+WHERE message_id != '' AND id NOT IN (
+    SELECT min(id) FROM MessageTable GROUP BY message_id
+)