Created
January 29, 2023 23:11
-
-
Save blacklight/5f08fdfb629d6c910a1142a302f320d8 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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() |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment