Merge Mattermost Channels

merge.sql

-- This makes a few assumptions that the secondary channel's setup information is now irrelevant and is being deleted
-- this means sidebar preferences, data retention, channel members, etc. are all being deleted in favor of what is setup on the primary

-- Playbook channel data IS moved to the new channel. However, if the playbook resides on the old team it'll need to be manually moved.

DO $$
    DECLARE
        _primaryChannelId VARCHAR := 'primary_channel_id';
        _secondaryChannelId VARCHAR := 'secondary_channel_name';

        -- these are the channels.name values. It should be a `-` spaced value.
        _primaryChannelName VARCHAR := 'channel-name';
        _secondaryChannelName VARCHAR := 'channel-name';
    BEGIN
        -- Perform the delete operation for old channel data
        DELETE FROM channels WHERE id = _secondaryChannelId;
        DELETE FROM channelmemberhistory WHERE channelid = _secondaryChannelId;
        DELETE FROM channelmembers WHERE channelid = _secondaryChannelId;

        -- channels that have group constraints to them
        DELETE FROM groupchannels WHERE channelid = _secondaryChannelId;

        -- shared channels
        DELETE FROM sharedchannels WHERE channelid = _secondaryChannelId;
        DELETE FROM sharedchannelremotes WHERE channelid = _secondaryChannelId;
        DELETE FROM sharedchannelremotes WHERE channelid = _secondaryChannelId;


        -- more deletes misc
        DELETE FROM retentionpolicieschannels WHERE channelid = _secondaryChannelId;
        DELETE FROM sidebarchannels WHERE channelid = _secondaryChannelId;
        DELETE FROM preferences WHERE name = _secondaryChannelName;


        -- moving the webhooks over
        UPDATE commandwebhooks SET channelid = _primaryChannelId WHERE channelid = _secondaryChannelId;
        UPDATE incomingwebhooks SET channelid = _primaryChannelId WHERE channelid = _secondaryChannelId;
        UPDATE outgoingwebhooks SET channelid = _primaryChannelId WHERE channelid = _secondaryChannelId;

        -- everything related to posts
        UPDATE drafts SET channelid = _primaryChannelId WHERE channelid = _secondaryChannelId;
        UPDATE posts SET channelid = _primaryChannelId WHERE channelid = _secondaryChannelId;
        UPDATE postspriority SET channelid = _primaryChannelId WHERE channelid = _secondaryChannelId;
        UPDATE reactions SET channelid = _primaryChannelId WHERE channelid = _secondaryChannelId;
        UPDATE threads SET channelid = _primaryChannelId WHERE channelid = _secondaryChannelId;

        -- find and replace of the channel links in a message
        UPDATE posts
        SET message = REPLACE(message, '~' || _secondaryChannelName, '~' || _primaryChannelName)
        WHERE message LIKE '%~' || _secondaryChannelName || '%';

        -- playbooks
        UPDATE ir_channelaction SET channelid = _primaryChannelId WHERE channelid = _secondaryChannelId;

        UPDATE ir_incident SET channelid = _primaryChannelId WHERE channelid = _secondaryChannelId;
        UPDATE ir_incident
        SET concatenatedbroadcastchannelids = REPLACE(concatenatedbroadcastchannelids, _secondaryChannelId, _secondaryChannelName)
        WHERE concatenatedbroadcastchannelids LIKE '%' || _secondaryChannelId || '%';

        UPDATE ir_playbook SET broadcastchannelid = _primaryChannelId WHERE broadcastchannelid = _secondaryChannelId;
        UPDATE ir_playbook
        SET concatenatedbroadcastchannelids = REPLACE(concatenatedbroadcastchannelids, _secondaryChannelId, _secondaryChannelName)
        WHERE concatenatedbroadcastchannelids LIKE '%' || _secondaryChannelId || '%';
        UPDATE ir_playbook SET channelid = _primaryChannelId WHERE channelid = _secondaryChannelId;

        -- moving any users who did not exist already.
        UPDATE ir_viewedchannel
        SET channelid = _primaryChannelId
        WHERE channelid = _secondaryChannelId
          AND NOT EXISTS (
            SELECT 1
            FROM ir_viewedchannel AS existing
            WHERE existing.channelid = _primaryChannelId
              AND existing.userid = ir_viewedchannel.userid
        );
        DELETE FROM ir_viewedchannel WHERE channelid = _secondaryChannelId;



        UPDATE uploadsessions SET channelid = _primaryChannelId WHERE channelid = _secondaryChannelId;




    END $$;