get reactions on SimpleX

simplex_get_reactions.md

This is the sql I used to get reactions on SimpleX. Everyone should be able to find out who's reacting to stuff since reactions are sometimes abused on SimpleX and they're accessible via the database anyway.

I think the easiest way is to use the cli version because the database isn't encrypted and the cli database is different from the desktop client's by default. Here's the link for instructions on how to use the cli: https://simplex.chat/docs/cli.html

I wouldn't recommend messing around with your real client's database even if you only plan on doing SELECT statements, but you can. Instructions on how to use sqlcipher to open an encrypted database are here: https://simplex.chat/docs/android.html#decrypting-databases

simplex_get_reactions.sql

-- to make things more readable, you can use the following 2 commands before executing the SELECT statement
--.mode columns
--.headers on

SELECT
    groups.local_display_name as group_display_name,
    group_members_message_sender.local_display_name as message_sender_name,
    replace(replace(replace(substr(chat_items.item_text, 0, 50), '\n', ' '), CHAR(10), " "), CHAR(13), " ") as message_text,
    group_members_reactors.local_display_name as reactor_name,
    replace(replace(chat_item_reactions.reaction, '{"type":"emoji","emoji":"', ''), '"}', '') as reaction
FROM chat_item_reactions
JOIN chat_items ON chat_item_reactions.shared_msg_id = chat_items.shared_msg_id
JOIN group_members AS group_members_reactors ON chat_item_reactions.group_member_id = group_members_reactors.group_member_id
JOIN group_members AS group_members_message_sender ON chat_items.group_member_id = group_members_message_sender.group_member_id
JOIN groups ON chat_item_reactions.group_id = groups.group_id
ORDER BY chat_items.item_ts;