fix(database): make v2->v3 chatlog migration deterministic and FK-safe

_migrate_to_v3 deduplicated duplicate archive_id rows with a bare-column
GROUP BY, which lets SQLite keep an arbitrary row per group (in practice the
older row, dropping the latest), and copied rows with SELECT *, which relies
on positional column matching between the v2 and v3 schemas.

- Keep MAX(id) per archive_id group ("latest insert wins") instead of
  GROUP BY's implementation-defined pick.
- Sweep replaces_db_id / replaced_by_db_id after dedupe, NULLing references
  to dropped rows so the migrated table carries no dangling XEP-0308 (LMC)
  links (the "Broken LMC reference" condition verify_integrity flags).
- List columns explicitly instead of SELECT * so the copy cannot mis-bind,
  and note at the schema that column changes must be mirrored here.

Refs #128

src/common.c

@@ -748,17 +748,17 @@ call_external(gchar** argv)
  *
  * This function constructs an argument vector (argv) based on the provided template string, replacing placeholders ("%u" and "%p") with the provided URL and filename, respectively.
  *
- * @param template  The template string with placeholders.
- * @param url       The URL to replace "%u" (or NULL to skip).
- * @param filename  The filename to replace "%p" (or NULL to skip).
- * @return          The constructed argument vector (argv) as a null-terminated array of strings.
+ * @param template_fmt  The template string with placeholders.
+ * @param url           The URL to replace "%u" (or NULL to skip).
+ * @param filename      The filename to replace "%p" (or NULL to skip).
+ * @return              The constructed argument vector (argv) as a null-terminated array of strings.
  *
  * @note Remember to free the returned argument vector using `auto_gcharv` or `g_strfreev()`.
  */
 gchar**
-format_call_external_argv(const char* template, const char* url, const char* filename)
+format_call_external_argv(const char* template_fmt, const char* url, const char* filename)
 {
-    gchar** argv = g_strsplit(template, " ", 0);
+    gchar** argv = g_strsplit(template_fmt, " ", 0);
 
     guint num_args = 0;
     while (argv[num_args]) {
@@ -767,7 +767,7 @@ format_call_external_argv(const char* template, const char* url, const char* fil
             argv[num_args] = g_strdup(url);
         } else if (0 == g_strcmp0(argv[num_args], "%p") && filename != NULL) {
             g_free(argv[num_args]);
-            argv[num_args] = strdup(filename);
+            argv[num_args] = g_strdup(filename);
         }
         num_args++;
     }

src/common.h

@@ -181,7 +181,7 @@ void get_file_paths_recursive(const char* directory, GSList** contents);
 gchar* get_random_string(int length);
 
 gboolean call_external(gchar** argv);
-gchar** format_call_external_argv(const char* template, const char* url, const char* filename);
+gchar** format_call_external_argv(const char* template_fmt, const char* url, const char* filename);
 
 gchar* unique_filename_from_url(const char* url, const char* path);
 gchar* get_expanded_path(const char* path);

src/database_sqlite.c

@@ -178,6 +178,9 @@ _sqlite_init(ProfAccount* account)
     //   replaced_by_db_id Inverse FK: id of the most recent correction.
     //                     Maintained by the AFTER INSERT trigger below so
     //                     readers can follow the chain forward in O(1).
+    //
+    // NOTE: any change to these columns (add/remove/reorder) must be mirrored
+    // in `_migrate_to_v3`, which copies rows via an explicit column list.
     const char* query = "CREATE TABLE IF NOT EXISTS `ChatLogs` ("
                         "`id` INTEGER PRIMARY KEY AUTOINCREMENT, "
                         "`from_jid` TEXT NOT NULL, "
@@ -865,11 +868,37 @@ _migrate_to_v3(void)
         "`replaces_db_id` INTEGER, "
         "`replaced_by_db_id` INTEGER)",
 
+        // Copy rows into the deduped table. Columns are listed explicitly
+        // (instead of `SELECT *`) so the migration cannot silently mis-bind
+        // should the v2/v3 column order ever diverge — keep this list in sync
+        // with the `ChatLogs_v3_migration` schema above and the live `ChatLogs`
+        // schema. For duplicate `archive_id` groups (MAM rebroadcasts) keep the
+        // highest `id` deterministically ("latest insert wins") rather than
+        // GROUP BY's implementation-defined arbitrary pick.
         "INSERT INTO `ChatLogs_v3_migration` "
-        "SELECT * FROM `ChatLogs` "
-        "WHERE `archive_id` IS NULL "
+        "(`id`, `from_jid`, `to_jid`, `from_resource`, `to_resource`, `message`, "
+        "`timestamp`, `type`, `stanza_id`, `archive_id`, `encryption`, "
+        "`marked_read`, `replace_id`, `replaces_db_id`, `replaced_by_db_id`) "
+        "SELECT `id`, `from_jid`, `to_jid`, `from_resource`, `to_resource`, `message`, "
+        "`timestamp`, `type`, `stanza_id`, `archive_id`, `encryption`, "
+        "`marked_read`, `replace_id`, `replaces_db_id`, `replaced_by_db_id` "
+        "FROM `ChatLogs` WHERE `archive_id` IS NULL "
         "UNION ALL "
-        "SELECT * FROM (SELECT * FROM `ChatLogs` WHERE `archive_id` IS NOT NULL GROUP BY `archive_id`)",
+        "SELECT `id`, `from_jid`, `to_jid`, `from_resource`, `to_resource`, `message`, "
+        "`timestamp`, `type`, `stanza_id`, `archive_id`, `encryption`, "
+        "`marked_read`, `replace_id`, `replaces_db_id`, `replaced_by_db_id` "
+        "FROM `ChatLogs` WHERE `archive_id` IS NOT NULL "
+        "AND `id` IN (SELECT MAX(`id`) FROM `ChatLogs` WHERE `archive_id` IS NOT NULL GROUP BY `archive_id`)",
+
+        // Dropping duplicate rows above can leave the surviving rows' correction
+        // back-links pointing at ids that no longer exist. NULL them out so the
+        // XEP-0308 (LMC) chain carries no dangling local foreign keys.
+        "UPDATE `ChatLogs_v3_migration` SET `replaces_db_id` = NULL "
+        "WHERE `replaces_db_id` IS NOT NULL "
+        "AND `replaces_db_id` NOT IN (SELECT `id` FROM `ChatLogs_v3_migration`)",
+        "UPDATE `ChatLogs_v3_migration` SET `replaced_by_db_id` = NULL "
+        "WHERE `replaced_by_db_id` IS NOT NULL "
+        "AND `replaced_by_db_id` NOT IN (SELECT `id` FROM `ChatLogs_v3_migration`)",
 
         "DROP TABLE `ChatLogs` ",
         "ALTER TABLE `ChatLogs_v3_migration` RENAME TO `ChatLogs` ",

src/event/common.h

@@ -10,6 +10,8 @@
 #ifndef EVENT_COMMON_H
 #define EVENT_COMMON_H
 
+#include "glib.h"
+
 void ev_disconnect_cleanup(void);
 void ev_inc_connection_counter(void);
 void ev_reset_connection_counter(void);

src/pgp/gpg.h

@@ -10,6 +10,8 @@
 #ifndef PGP_GPG_H
 #define PGP_GPG_H
 
+#include "glib.h"
+
 typedef struct pgp_key_t
 {
     gchar* id;

src/tools/bookmark_ignore.h

@@ -10,8 +10,10 @@
 #ifndef BOOKMARK_IGNORE_H
 #define BOOKMARK_IGNORE_H
 
+#include "xmpp/xmpp.h"
+
 void bookmark_ignore_on_connect(const char* const barejid);
-void bookmark_ignore_on_disconnect(void);
+void bookmark_ignore_on_disconnect();
 gboolean bookmark_ignored(Bookmark* bookmark);
 gchar** bookmark_ignore_list(gsize* len);
 void bookmark_ignore_add(const char* const barejid);

src/ui/titlebar.h

@@ -10,6 +10,8 @@
 #ifndef UI_TITLEBAR_H
 #define UI_TITLEBAR_H
 
+#include "glib.h"
+
 void create_title_bar(void);
 void free_title_bar(void);
 void title_bar_update_virtual(void);

src/xmpp/blocking.h

@@ -10,6 +10,8 @@
 #ifndef XMPP_BLOCKING_H
 #define XMPP_BLOCKING_H
 
+#include <strophe.h>
+
 void blocking_request(void);
 int blocked_set_handler(xmpp_stanza_t* stanza);
 int reporting_set_handler(xmpp_stanza_t* stanza);

src/xmpp/iq.h

@@ -10,6 +10,8 @@
 #ifndef XMPP_IQ_H
 #define XMPP_IQ_H
 
+#include <strophe.h>
+
 typedef int (*ProfIqCallback)(xmpp_stanza_t* const stanza, void* const userdata);
 typedef void (*ProfIqFreeCallback)(void* userdata);
 

src/xmpp/ox.h

@@ -7,6 +7,8 @@
  * SPDX-License-Identifier: GPL-3.0-or-later WITH OpenSSL-exception
  */
 
+#include "glib.h"
+
 /*!
  * \page OX OX Implementation
  *

src/xmpp/roster.h

@@ -10,6 +10,9 @@
 #ifndef XMPP_ROSTER_H
 #define XMPP_ROSTER_H
 
+#include "glib.h"
+#include <strophe.h>
+
 void roster_request(void);
 void roster_set_handler(xmpp_stanza_t* const stanza);
 void roster_result_handler(xmpp_stanza_t* const stanza);

src/xmpp/vcard_funcs.h

@@ -12,6 +12,7 @@
 
 #include "ui/win_types.h"
 #include "xmpp/vcard.h"
+#include <strophe.h>
 
 vCard* vcard_new();
 void vcard_free(vCard* vcard);