運営インスタンスの全トゥートを取得、分析したい…


#1

運営しているインスタンスの全トゥート内容を取得したいのですが、、
検索しても良さそうなコマンドを見つける事ができず…。
このようなことは可能でしょうか。。。
あまりにも初心者的な内容で、スミマセン…


#2

Slackの方でお話していた件の続きですか?
開発案件として相談したが、折り合いがつかなかったというところでしょうか。

検索して出てこないのは、全トゥートを取得するコマンドを設けることに設計上意味を見いだしにくく、用意する人も、それを求める人もいないからではないかと思います。全トゥートを対象とするのであれば、目的に対し相応の設計を行ったAPIとして実装するか、別のプログラムでデータベースに対して直接アクセスするようにするのが自然です。

ということで、単純に可能かということであれば、もちろん可能です。
ご自身のインスタンスであれば、PostgreSQLのデータベースに格納されていますので、SQLで取得してください。


#3

掲示板の方は、
金額が合わず諦めました…。

せめてtoot内容を取り出して、分析をしたいなぁと思いまして…。

mastodon=> \d
List of relations
Schema | Name | Type | Owner
--------±-----------------------------±---------±---------
public | account_domain_blocks | table | mastodon
public | account_domain_blocks_id_seq | sequence | mastodon
public | accounts | table | mastodon
public | accounts_id_seq | sequence | mastodon
public | ar_internal_metadata | table | mastodon
public | blocks | table | mastodon
public | blocks_id_seq | sequence | mastodon
public | conversation_mutes | table | mastodon
public | conversation_mutes_id_seq | sequence | mastodon
public | conversations | table | mastodon
public | conversations_id_seq | sequence | mastodon
public | domain_blocks | table | mastodon
public | domain_blocks_id_seq | sequence | mastodon
public | favourites | table | mastodon
public | favourites_id_seq | sequence | mastodon
public | follow_requests | table | mastodon
public | follow_requests_id_seq | sequence | mastodon
public | follows | table | mastodon
public | follows_id_seq | sequence | mastodon
public | imports | table | mastodon
public | imports_id_seq | sequence | mastodon
public | media_attachments | table | mastodon
public | media_attachments_id_seq | sequence | mastodon
public | mentions | table | mastodon
public | mentions_id_seq | sequence | mastodon
public | mutes | table | mastodon
public | mutes_id_seq | sequence | mastodon
public | notifications | table | mastodon
public | notifications_id_seq | sequence | mastodon
public | oauth_access_grants | table | mastodon
public | oauth_access_grants_id_seq | sequence | mastodon
public | oauth_access_tokens | table | mastodon
public | oauth_access_tokens_id_seq | sequence | mastodon
public | oauth_applications | table | mastodon
public | oauth_applications_id_seq | sequence | mastodon
public | preview_cards | table | mastodon
public | preview_cards_id_seq | sequence | mastodon
public | reports | table | mastodon
public | reports_id_seq | sequence | mastodon
public | schema_migrations | table | mastodon
public | settings | table | mastodon
public | settings_id_seq | sequence | mastodon
public | statuses | table | mastodon
public | statuses_id_seq | sequence | mastodon
public | statuses_tags | table | mastodon
public | stream_entries | table | mastodon
public | stream_entries_id_seq | sequence | mastodon
public | subscriptions | table | mastodon
public | subscriptions_id_seq | sequence | mastodon
public | tags | table | mastodon
public | tags_id_seq | sequence | mastodon
public | users | table | mastodon
public | users_id_seq | sequence | mastodon
public | web_settings | table | mastodon
public | web_settings_id_seq | sequence | mastodon
(55 rows)
–More–

ここまではやってみたのですが、、、
ここから先に進めず…。。


#4

中身を確認したことは無いのですが、statuses テーブルに全部入っているのでは。
psqlコマンドで、出力データを加工してCSVなりTSV形式なりにすれば、他のツールで読み込めるかと思います。


#5

statuses テーブルを読み込んでみたら、
連合タイムラインが取れそうでした。
量が膨大にあるのでなかなか難しそうですが、、、。
ありがとうございました!


#6

やっぱりローカルラインの
トゥートの取得が難しそうなので、、、
どなたかお仕事として、
ご依頼できないでしょうか…。


#7

statuses(トゥート)のテーブル定義がこれで、

mastodon=> \d statuses
                                       テーブル "public.statuses"
           列           |             型              |                     修飾語                      
------------------------+-----------------------------+-------------------------------------------------
 id                     | bigint                      | not null default timestamp_id('statuses'::text)
 uri                    | character varying           | 
 text                   | text                        | not null default ''::text
 created_at             | timestamp without time zone | not null
 updated_at             | timestamp without time zone | not null
 in_reply_to_id         | bigint                      | 
 reblog_of_id           | bigint                      | 
 url                    | character varying           | 
 sensitive              | boolean                     | not null default false
 visibility             | integer                     | not null default 0
 spoiler_text           | text                        | not null default ''::text
 reply                  | boolean                     | not null default false
 favourites_count       | integer                     | not null default 0
 reblogs_count          | integer                     | not null default 0
 language               | character varying           | 
 conversation_id        | bigint                      | 
 local                  | boolean                     | default false
 account_id             | bigint                      | not null
 application_id         | bigint                      | 
 in_reply_to_account_id | bigint                      | 
(以下略)

アカウントのテーブル定義がこれなので、

mastodon=> \d accounts
                                          テーブル "public.accounts"
           列            |             型              |                        修飾語                         
-------------------------+-----------------------------+-------------------------------------------------------
 username                | character varying           | not null default ''::character varying
 domain                  | character varying           | 
 secret                  | character varying           | not null default ''::character varying
 private_key             | text                        | 
 public_key              | text                        | not null default ''::text
 remote_url              | character varying           | not null default ''::character varying
 salmon_url              | character varying           | not null default ''::character varying
 hub_url                 | character varying           | not null default ''::character varying
 created_at              | timestamp without time zone | not null
 updated_at              | timestamp without time zone | not null
 note                    | text                        | not null default ''::text
 display_name            | character varying           | not null default ''::character varying
 uri                     | character varying           | not null default ''::character varying
 url                     | character varying           | 
 avatar_file_name        | character varying           | 
 avatar_content_type     | character varying           | 
 avatar_file_size        | integer                     | 
 avatar_updated_at       | timestamp without time zone | 
 header_file_name        | character varying           | 
 header_content_type     | character varying           | 
 header_file_size        | integer                     | 
 header_updated_at       | timestamp without time zone | 
 avatar_remote_url       | character varying           | 
 subscription_expires_at | timestamp without time zone | 
 silenced                | boolean                     | not null default false
 suspended               | boolean                     | not null default false
 locked                  | boolean                     | not null default false
 header_remote_url       | character varying           | not null default ''::character varying
 statuses_count          | integer                     | not null default 0
 followers_count         | integer                     | not null default 0
 following_count         | integer                     | not null default 0
 last_webfingered_at     | timestamp without time zone | 
 inbox_url               | character varying           | not null default ''::character varying
 outbox_url              | character varying           | not null default ''::character varying
 shared_inbox_url        | character varying           | not null default ''::character varying
 followers_url           | character varying           | not null default ''::character varying
 protocol                | integer                     | not null default 0
 id                      | bigint                      | not null default nextval('accounts_id_seq'::regclass)
 memorial                | boolean                     | not null default false
 moved_to_account_id     | bigint                      | 
 featured_collection_url | character varying           | 
(以下略)

こんな感じに組み合わせて、

select
    username,
    display_name,
    spoiler_text,
    text
from
    statuses
    inner join accounts on statuses.account_id = accounts.id
where
    local = true
    and reblog_of_id is null
order by
    statuses.created_at desc
limit
    5;

ローカルの最新トゥート(とりあえず5件)を引っぱってきます。

表示したい内容と条件、順番、件数などを指定して、欲しい情報だけを取得すればいいんですよ。
SQLの書き方については、良い機会なのでこの期に勉強してみてください。

  • statusesのlocalがtrueのものがローカルです。falseのものを取得する場合はdomainも持ってきて(selectの項目に加えて)つなぎ合わせれば@user@domain 形式のアカウント名(username)がわかります。
  • statusesのcreated_atを降順(desc)にして、最新のトゥート順に並べ替えています。
  • limitで表示件数を5件に絞っています。
  • reblog_of_idがnullのものだけに絞り込んでいます。ブーストを除外するためです。
select
    SA.username,
    SA.display_name,
    RA.username,
    RA.domain,
    RA.display_name,
    R.spoiler_text,
    R.text
from
    statuses S
    inner join statuses R on S.reblog_of_id = R.id
    inner join accounts SA on S.account_id = SA.id
    inner join accounts RA on R.account_id = RA.id
where
    S.local = true
order by
    S.created_at desc
limit
    5;

ローカルユーザーがブーストしたトゥートを取得する例です。

  • ブーストの場合、対象のトゥートのidがreblog_of_idに入っているので、該当するidのトゥートを引っぱってきて、そちらのユーザー名、ドメイン、表示名、CWの警告(spoiler_text)、トゥート本体(text)を使います。

ブーストのように別のデータを参照しているようなものは、全部取り出してからどうこうしようとするよりも、このようにデータベース上で加工してしまった方が断然楽です。大量のデータから目的のものを絞り込むのも、件数を集計するのも同様です。


#8

ありがとうございます!
SQLの書き方も知識がほぼゼロでしたが、、
教えて頂いたことを参照しつつ、
google先生に聞いたりして、意味がつかめてきました。
ありがとうございます!!!

現在、、
ERROR: column “local” does not exist
LINE 1: …counts on statuses.account_id = accounts.id where local = tr…
なところで、止まってしまっておりまして、、、
localの列がない・・・とエラーが出ております。。

私のインスタンスの \d statuses \d accounts ですが、、

mastodon-> mastodon=> mastodon=> \d statuses

                                       Table "public.statuses"
     Column         |            Type             |                       Modifiers

------------------------±----------------------------±------------------------------------------------------
id | bigint | not null default nextval(‘statuses_id_seq’::regclass)
uri | character varying |
account_id | integer | not null
text | text | not null default ‘’::text
created_at | timestamp without time zone | not null
updated_at | timestamp without time zone | not null
in_reply_to_id | bigint |
reblog_of_id | bigint |
url | character varying |
sensitive | boolean | default false
visibility | integer | not null default 0
in_reply_to_account_id | integer |
application_id | integer |
spoiler_text | text | not null default ‘’::text
reply | boolean | default false
favourites_count | integer | not null default 0
reblogs_count | integer | not null default 0
language | character varying | not null default ‘en’::character varying
conversation_id | bigint |
Indexes:
“statuses_pkey” PRIMARY KEY, btree (id)
“index_statuses_on_uri” UNIQUE, btree (uri)
“index_statuses_on_account_id” btree (account_id)
“index_statuses_on_conversation_id” btree (conversation_id)
“index_statuses_on_in_reply_to_id” btree (in_reply_to_id)
“index_statuses_on_reblog_of_id” btree (reblog_of_id)
Foreign-key constraints:
“fk_rails_256483a9ab” FOREIGN KEY (reblog_of_id) REFERENCES statuses(id) ON DELETE CASCADE
Referenced by:
TABLE “statuses” CONSTRAINT “fk_rails_256483a9ab” FOREIGN KEY (reblog_of_id) REFERENCES statuses(id) ON DELETE CASCADE

mastodon=> \d accounts
Table “public.accounts”

     Column          |            Type             |                       M

odifiers
-------------------------±----------------------------±------------------------------------------------------
id | integer | not null default nextval(‘accounts_id_seq’::regclass)
username | character varying | not null default ‘’::character varying
domain | character varying |
secret | character varying | not null default ‘’::character varying
private_key | text |
public_key | text | not null default ‘’::text
remote_url | character varying | not null default ‘’::character varying
salmon_url | character varying | not null default ‘’::character varying
hub_url | character varying | not null default ‘’::character varying
created_at | timestamp without time zone | not null
updated_at | timestamp without time zone | not null
note | text | not null default ‘’::text
display_name | character varying | not null default ‘’::character varying
uri | character varying | not null default ‘’::character varying
url | character varying |
avatar_file_name | character varying |
avatar_content_type | character varying |
avatar_file_size | integer |
avatar_updated_at | timestamp without time zone |
header_file_name | character varying |
header_content_type | character varying |
header_file_size | integer |
header_updated_at | timestamp without time zone |
avatar_remote_url | character varying |
subscription_expires_at | timestamp without time zone |
silenced | boolean | not null default false
suspended | boolean | not null default false
locked | boolean | not null default false
header_remote_url | character varying | not null default ‘’::character varying
statuses_count | integer | not null default 0
followers_count | integer | not null default 0
following_count | integer | not null default 0
last_webfingered_at | timestamp without time zone |
Indexes:
“accounts_pkey” PRIMARY KEY, btree (id)
“index_accounts_on_username_and_domain” UNIQUE, btree (username, domain)
“index_accounts_on_uri” btree (uri)
“index_accounts_on_url” btree (url)
“index_accounts_on_username_and_domain_lower” btree (lower(username::text),
lower(domain::text))
“search_index” gin (((setweight(to_tsvector(‘simple’::regconfig, display_nam
e::text), ‘A’::“char”) || setweight(to_tsvector(‘simple’::regconfig, username::t
ext), ‘B’::“char”)) || setweight(to_tsvector(‘simple’::regconfig, COALESCE(domai
n, ‘’::character varying)::text), ‘C’::“char”))

という所で、、、立ち往生してしまっております。。。
誠にすみません。。。原因の方、、こちらの内容で掴めますでしょうか…。。。。

マストドンもちょっとリニューアルして改良を加えたかったりするのですが、、、
そろそろ素人の勘だけでは、立ち行かない領域に入りつつあります。。。


#9

トゥートのcolumn が分からないので、where句を抜かして
select username,display_name,spoiler_text,text from statuses inner join accounts on statuses.account_id = accounts.id order by statuses.created_at desc limit 20;
で実行してみた所、それらしきトゥートは出たのですが、、書き出されるのがメチャメチャ遅かったです。。


#10

statusテーブルのlocalカラムが生えたのが、バージョン1.6からなので、古いインスタンスだとないです。
これを機にバージョンアップしてみては?

where句は、条件を指定して処理対象を絞り込むので、省略すると処理量が増えるので・・・。


#11

バージョンの問題でしたか…。失礼しましたぁ。
ちょっとチャレンジしてみたいと思います。


#12

データベースに直接アクセスする方法の最大の弱点が、このテーブル定義の変化に追従できないというところです。migrationという仕組みで更新されていくので live/db/migrate を覗けばどんな変更がされたのか辿ることができます。

/home/mastodon/live/db/migrate/20170905165803_add_local_to_statuses.rb

class AddLocalToStatuses < ActiveRecord::Migration[5.1]
  def change
    add_column :statuses, :local, :boolean, null: true, default: nil
  end
end

まぁ、マストドンのバージョンアップも、一気にやるとなかなか大変なのではないかと思います。

難儀しているようでしたら、別にトピックたててヘルプ求めてください。ハマリどころはだいたいみんな一緒なので、経緯・経過が記されていれば、他のインスタンス管理者の参考にもなるんじゃないかと思います。

いよいよ4月、追従できていない放置インスタンスは消えていくのかな……。


#13

ちょっとバタバタしておりまして、
ご返信が遅くなりすみません。
ご教授頂いた事で理解が進み、
何とか目途がつきそうです!

いろいろとありがとうございます!