media_rot.sql

-- media_rot.sql
--
-- Copyright codl <codl@codl.fr>
--
-- Permission to use, copy, modify, and/or distribute this software for any
-- purpose with or without fee is hereby granted.
--
-- THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES
-- WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF
-- MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY
-- SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES
-- WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION
-- OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN
-- CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.





-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
-- You should not run mystery SQL from internet randos on your Masto server!
-- !!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
--
-- If you don't know PostgreSQL well enough to figure out if this is safe,
-- ask someone who does and whom you trust.





-- This file catalogs fediverse servers who have moved their media without
-- setting up redirects or keeping the old files online.
--
-- When run on the postgresql database of a Mastodon server, it will rewrite
-- the old, no-longer-functional URLs to point instead at the new location.
--
-- This is necessary because old cached media that has been pruned with
-- `tootctl media delete` will need to be fetched again when someone loads up
-- that status again. If the URL has changed, then Mastodon will not be able to
-- refetch media. Media URLs should ideally never change but in my experience
-- this is not a well known or well followed recommendation, and it is only
-- fairly recently that it has been added to the official Mastodon
-- documentation.



-- Wanna help catalog? Do you run an instance?
-- Check logs for requests for /media_proxy/XXXXXXXXX that respond with 404
--     SELECT m.remote_url, m.status_id, s.uri FROM media_attachments AS m LEFT JOIN statuses AS s ON m.status_id = s.id WHERE m.id = XXXXXXXXX;
-- will retrieve that attachment's url as your instance remembers it
-- as well as the status's remote URL.
-- Clicking through to the status and comparing its media's url to the one the
-- local instance remembers should be enough to find values
-- of old_prefix and new_prefix.
-- Email findings to codl@codl.fr, mentioning if & how you'd like to be credited
--
-- Wanna help but don't run an instance?
-- If you see old remote posts with broken media, try clicking on the "unavailable media" box, which
-- should open a new tab with an error. That's the old URL, write it down. Then
-- open the status on the original instance, and middle click the media
-- (or right click -> open video in new tab) to open it in a new tab. That
-- is the new URL, write it down also.
-- Email findings to codl@codl.fr, mentioning if & how you'd like to be credited

BEGIN;

CREATE PROCEDURE pg_temp.migrate_media(old_prefix text, new_prefix text) as $$
	UPDATE media_attachments
		SET remote_url = replace(remote_url, old_prefix, new_prefix)
		WHERE remote_url LIKE old_prefix || '%';
	-- this only does media attachments at the time
        -- this could also potentially do avatars and headers but I don't think
        -- those are pruned by tootctl media delete so i'm not sure how useful
        -- it would be
$$ LANGUAGE SQL;

-- template
-- CALL pg_temp.migrate_media('old url prefix',
--                            'new url prefix');

-- anticapitalist.party
CALL pg_temp.migrate_media('https://anticapitalist.party/system/',
                           'https://cdn.masto.host/acp/');

-- awoo.space
CALL pg_temp.migrate_media('https://awoo.space/system/',
                           'https://media.awoo.space/awoo.space/');
CALL pg_temp.migrate_media('https://s3-eu-west-3.amazonaws.com/awoospace-media/',
                           'https://media.awoo.space/awoo.space/');


-- catdon.life
CALL pg_temp.migrate_media('https://catdon.life/system/',
                           'https://s3-ap-northeast-1.amazonaws.com/catdon-life/');

-- deadinsi.de
CALL pg_temp.migrate_media('https://files.slis.icu/deadinside-media/',
                           'https://deadinside.sfo2.cdn.digitaloceanspaces.com/');

-- donphan.social
CALL pg_temp.migrate_media('https://s3-ca-central-1.amazonaws.com/donphan-social/',
                           'https://pool.jortage.com/donphansocial/');
CALL pg_temp.migrate_media('https://donphan.social/system/',
                           'https://pool.jortage.com/donphansocial/');

-- framapiaf.org
CALL pg_temp.migrate_media('https://framapiaf.org/system/',
                           'https://framapiaf.s3.framasoft.org/framapiaf/');

-- glaceon.social
CALL pg_temp.migrate_media('https://s3-us-east-2.amazonaws.com/glaceonsocialbucket/',
                           'https://pool.jortage.com/glaceonsocial/');

-- idlethumbs.social
CALL pg_temp.migrate_media('https://idlethumbs.social/system/',
                           'https://files.idlethumbs.social/');

-- jorts.horse
CALL pg_temp.migrate_media('https://jorts.horse/system/',
                           'https://media.jorts.horse/horse-media/');

-- meemu.org
CALL pg_temp.migrate_media('https://meemu.org/system/',
                           'https://media.meemu.org/');
CALL pg_temp.migrate_media('https://s3.wasabisys.com/meemu/',
                           'https://media.meemu.org/');

-- merveilles.town
CALL pg_temp.migrate_media('https://s3-eu-central-1.amazonaws.com/merveilles/',
                           'https://assets.merveilles.town/');

-- mstdn.social
CALL pg_temp.migrate_media('https://mstdn.social/system/',
                           'https://media.mstdn.social/');

-- pokemon.mastportal.info
CALL pg_temp.migrate_media('https://media.hostdon.ne.jp/hostdon-m32/',
                           'https://us-media.hostdon.ne.jp/hostdon-m32/');

-- qoto.org
CALL pg_temp.migrate_media('https://storage.gra5.cloud.ovh.net/v1/AUTH_011f6e315d3744d498d93f6fa0d9b5ee/qotoorg/',
                           'https://storage.gra.cloud.ovh.net/v1/AUTH_011f6e315d3744d498d93f6fa0d9b5ee/qotoorg/');

-- mastodon.sergal.org
CALL pg_temp.migrate_media('https://s3.wasabisys.com/sergal-assets/',
                           'https://s3.us-west-002.backblazeb2.com/sergal-mastodon/');

-- mspsocial.net
CALL pg_temp.migrate_media('https://mspsocial.net/system/',
                           'https://mspimages.sfo2.cdn.digitaloceanspaces.com/');

-- slime.global
CALL pg_temp.migrate_media('https://slime.global/system/',
                           'https://pool.jortage.com/slimeglobal/');

-- shrike.club
CALL pg_temp.migrate_media('https://shrike.club/system/',
                           'https://s3-us-west-2.amazonaws.com/shrikeclub/');

-- sleepygremlin.online
CALL pg_temp.migrate_media('https://sleepygremlin.online/system/',
                           'https://us-southeast-1.linodeobjects.com/sleepygremlin/');

-- social.tchncs.de
CALL pg_temp.migrate_media('https://f2.tchncs.de/social.tchncs.de/',
                           'https://f2.tchncs.de/');

-- tabletop.social
CALL pg_temp.migrate_media('https://storage.gra5.cloud.ovh.net/v1/AUTH_011f6e315d3744d498d93f6fa0d9b5ee/tabletop/',
                           'https://storage.gra.cloud.ovh.net/v1/AUTH_011f6e315d3744d498d93f6fa0d9b5ee/tabletop/');

-- tech.lgbt
CALL pg_temp.migrate_media('https://link.storjshare.io/raw/jvxikkhiqnksyeatwcn3iigoa3ta/techlgbt/',
                           'https://media.tech.lgbt/');

-- toot.cat
CALL pg_temp.migrate_media('https://s3-us-west-2.amazonaws.com/tootcatapril2017/',
                           'https://pool.jortage.com/tootcat/');
CALL pg_temp.migrate_media('https://tootcatapril2017.s3-us-west-2.amazonaws.com/',
                           'https://pool.jortage.com/tootcat/');

-- trollian.space
-- old media does not seem to exist at the new location... rip
-- CALL pg_temp.migrate_media('https://s3.amazonaws.com/trollian-media/',
--                            'https://s3.wasabisys.com/trollianmedia/')

-- yiff.life
CALL pg_temp.migrate_media('https://yiff.life/system/',
                           'https://cdn.yiff.life/');
CALL pg_temp.migrate_media('https://cdn.yiff.life/yl-cdn/',
                           'https://cdn.yiff.life/');
CALL pg_temp.migrate_media('https://cumdump.yiff.life/yl-cdn/',
                           'https://cdn.yiff.life/');

COMMIT;