fix_mastodon_emoji_cache.py

import os

from sqlalchemy import create_engine
from sqlalchemy.sql import text

def main():
    ### Replace this with the SQLAlchemy URL associated to your database
    db_string = 'postgresql+pg8000://postgres@localhost/mastodon_production'
    ### Replace this with the base directory of your Mastodon instance
    mastodon_basedir = '/opt/mastodon/live'

    cache_dir = os.path.join(
        mastodon_basedir, 'public', 'system', 'cache',
        'custom_emojis', 'images'
    )

    engine = create_engine(db_string)
    missing_ids = set()

    with engine.connect() as db:
        # Select all the emojis
        res = db.execute('''
            SELECT id FROM custom_emojis
        ''').fetchall()

        # Find all the emojis with no associated cache files
        for emoji in res:
            emoji_id = emoji[0]
            emoji_id_padded = f'{emoji[0]:09d}'
            emoji_path = os.path.join(
                cache_dir,
                *[
                    emoji_id_padded[i:i+3]
                    for i in range(0, len(emoji_id_padded), 3)
                ],
            )

            if not os.path.isdir(emoji_path):
                missing_ids.add(emoji_id)

        print(
            f'Deleting references for {len(missing_ids)} missing icons '
            f'out of {len(res)}'
        )

        # Delete missing images from the database
        if missing_ids:
            db.execute(f'''
                DELETE FROM custom_emojis WHERE id IN (
                    {", ".join(map(str, missing_ids))}
                )
            ''')


if __name__ == '__main__':
    main()