Lycolog

自分の投稿にあるタグの使用回数を知りたかったのでMastodonの本番DBを見る方法を調べた記録。

確認環境

Mastodon v4.5.4

やり方

postgresになってmastodon_productionを開くと見れる。

sudo su - postgres
psql mastodon_production

テーブル一覧は恐らくhttps://mstdn.example.com/pghero/で見れるものがそれだと思う。

DBスキーマ

有志が公開しているMastodonのER図を参考にさせてもらった。

今回打ったクエリ

自分が投稿しているタグの総数が知りたかったので、こんな感じにしてみた。たぶん自インスタンスのdomainはnullと思われる。適当なので知らんけど。

SELECT
  tags.name
  ,COUNT(*) as post_count
FROM statuses
  INNER JOIN statuses_tags
    ON statuses.id = statuses_tags.status_id
  INNER JOIN tags
    ON statuses_tags.tag_id = tags.id
WHERE
  statuses.account_id = (SELECT id FROM accounts WHERE username = 'Lycolia' AND domain IS NULL)
GROUP BY
  tags.name
ORDER BY
  post_count DESC;

クエリの結果

ゾンビランドサガと三宮に対する言及がめちゃくちゃ多いことがよく分かった。

参考

• MastodonのDBを外出ししてみたメモ
• Liam ERDで綺麗でインタラクティブなER図を自動生成する