chat_message_join can indicate that a single message belongs to multiple chats

[ReagentX]

We iterate over the messages table with this query:

imessage-exporter/imessage-database/src/tables/messages.rs

Lines 528 to 581 in 102347f

	/// Stream messages from the database with optional filters
	///
	/// # Example:
	///
	/// ```
	/// use imessage_database::util::dirs::default_db_path;
	/// use imessage_database::tables::table::{Diagnostic, get_connection};
	/// use imessage_database::tables::messages::Message;
	/// use imessage_database::util::query_context::QueryContext;
	///
	/// let db_path = default_db_path();
	/// let conn = get_connection(&db_path).unwrap();
	/// let context = QueryContext::default();
	/// Message::stream_rows(&conn, &context).unwrap();
	pub fn stream_rows<'a>(
	db: &'a Connection,
	context: &'a QueryContext,
	) -> Result<Statement<'a>, TableError> {
	if !context.has_filters() {
	return Self::get(db);
	} else {
	let filters = context.generate_filter_statement();
	// If database has `thread_originator_guid`, we can parse replies, otherwise default to 0
	Ok(db.prepare(&format!(
	"SELECT
	*,
	c.chat_id,
	(SELECT COUNT(*) FROM {MESSAGE_ATTACHMENT_JOIN} a WHERE m.ROWID = a.message_id) as num_attachments,
	(SELECT COUNT(*) FROM {MESSAGE} m2 WHERE m2.thread_originator_guid = m.guid) as num_replies
	FROM
	message as m
	LEFT JOIN {CHAT_MESSAGE_JOIN} as c ON m.ROWID = c.message_id
	{filters}
	ORDER BY
	m.date;
	"
	))
	.unwrap_or(db.prepare(&format!(
	"SELECT
	*,
	c.chat_id,
	(SELECT COUNT(*) FROM {MESSAGE_ATTACHMENT_JOIN} a WHERE m.ROWID = a.message_id) as num_attachments,
	(SELECT 0) as num_replies
	FROM
	message as m
	LEFT JOIN {CHAT_MESSAGE_JOIN} as c ON m.ROWID = c.message_id
	{filters}
	ORDER BY
	m.date;
	"
	)).map_err(TableError::Messages)?))
	}
	}

The query uses a LEFT JOIN, so we see one row for each occurrence of a message in the chat_message_join table.

imessage-exporter/imessage-database/src/tables/messages.rs

Line 560 in 102347f

LEFT JOIN {CHAT_MESSAGE_JOIN} as c ON m.ROWID = c.message_id

This results in duplicated messages in a conversation, since this software successfully deduplicates chats:

imessage-exporter/imessage-database/src/tables/chat_handle.rs

Lines 88 to 112 in 102347f

	/// Given the initial set of duplicated chats, deduplicate them based on the participants
	///
	/// This returns a new hashmap that maps the real chat ID to a new deduplicated unique chat ID
	/// that represents a single chat for all of the same participants, even if they have multiple handles
	fn dedupe(duplicated_data: &HashMap<i32, Self::T>) -> HashMap<i32, i32> {
	let mut deduplicated_chats: HashMap<i32, i32> = HashMap::new();
	let mut participants_to_unique_chat_id: HashMap<Self::T, i32> = HashMap::new();
	// Build cache of each unique set of participants to a new identifier:
	let mut unique_chat_identifier = 0;
	for (chat_id, participants) in duplicated_data {
	match participants_to_unique_chat_id.get(participants) {
	Some(id) => {
	deduplicated_chats.insert(chat_id.to_owned(), id.to_owned());
	}
	None => {
	participants_to_unique_chat_id
	.insert(participants.to_owned(), unique_chat_identifier);
	deduplicated_chats.insert(chat_id.to_owned(), unique_chat_identifier);
	unique_chat_identifier += 1;
	}
	}
	}
	deduplicated_chats
	}

In my testing, the only messages that exhibited this were iMessages resent as SMS.

[ReagentX]

Using a subquery solves the problem:

SELECT
    *,
    c.chat_id,
    (SELECT COUNT(*) FROM message_attachment_join a WHERE m.ROWID = a.message_id) as num_attachments,
    (SELECT b.chat_id FROM chat_recoverable_message_join b WHERE m.ROWID = b.message_id) as deleted_from,
    (SELECT COUNT(*) FROM message m2 WHERE m2.thread_originator_guid = m.guid) as num_replies
FROM
    message as m
    LEFT JOIN (SELECT * from chat_message_join GROUP BY message_id) as c ON m.ROWID = c.message_id
ORDER BY
    m.date;

However, this comes with a very large performance impact: a normal export (no attachment management, debug build) nosedived from an average rate of 6,928.03 messages per second to about 202.26.

[ReagentX]

Since the result set from the query is ordered:

imessage-exporter/imessage-database/src/tables/messages.rs

Lines 562 to 563 in 102347f

	ORDER BY
	m.date;

We can just keep track of the last-rendered ROWID and skip rendering if we see the same ID twice. This has negligible performance impact.