Mastodon, Steampipe, and RSS | InfoWorld

I was determined to generate my Mastodon #introduction currently. To get started out I used the tag look for in the dashboard I’m constructing.

IDG

The plan was to seem at a bunch of other #introduction posts to get a sense for how mine ought to go. When you look for specifically for hashtags, the Mastodon search API returns this information and facts.

"hashtags": [
    
      "name": "introduction",
      "url": "https://mastodon.social/tags/introduction",
      "history": [
        
          "day": "1574553600",
          "uses": "10",
          "accounts": "9"
        ,
        // ...
      ]
    ,

A initially edition of the dashboard, having only this knowledge to do the job with, just detailed the names of tags matching the search time period alongside with corresponding URLs. Below was the original query.

select 
  name,
  url
from 
  mastodon_research_hashtag 
exactly where 
  query = 'introduction'

That created a record of back links, like https://mastodon.social/tags/introduction, to household web pages for variants of the tag. These are helpful one-way links! Each individual goes to a page the place you can see who is submitting to the tag.

To make this perspective a bit extra useful, I tapped the third component of the API response, history, in a revised question.

with info as (
  decide on 
    name,
    url,
    ( jsonb_array_aspects(background) ->> 'uses' )::int as utilizes
  from 
    mastodon_search_hashtag 
  wherever 
    query = 'introduction'
)
pick out
  name,
  url,
  sum(utilizes)
from
  information
team by
  title, url
order by
  sum desc

These benefits assistance me make your mind up which variant to use.

+-------------------+---------------------------------------------------+------+
| title              | url                                               | sum  |
+-------------------+---------------------------------------------------+------+
| introduction      | https://mastodon.social/tags/introduction         | 1816 |
| introductions     | https://mastodon.social/tags/introductions        | 218  |
| introductionpost  | https://mastodon.social/tags/introductionpost     | 19   |
| introductionfr    | https://mastodon.social/tags/introductionfr       | 6    |

But I continue to want to stop by each link’s web page to explore how it is remaining used. It would be pleasant to surface area additional context in the dashboard, and I found a nifty way to do it, but 1st let’s dwell on the revised query for a moment. Postgres’s JSON characteristics are potent and it is often a challenge (at least for me) to visualize how they perform.

The Postgres jsonb_array_factors() operate is what’s named a set-returning function. In this article it unpacks Postgres’s JSON representation of the record of background structures returned from the Mastodon API. In its most basic sort, the operate get in touch with jsonb_array_aspects(record) produces a short-term desk with per-tag, for each-day info.

choose
  identify,
  jsonb_array_things(heritage) as heritage
from
  mastodon_look for_hashtag 
where 
  question = 'introduction'

+--------------------------------+----------------------------------------------------+
| name                           | historical past                                            |
+--------------------------------+----------------------------------------------------+
| introduction                   | "accounts":"16","working day":"1670371200","uses":"19"   |
| introduction                   | "accounts":"250","working day":"1670284800","uses":"269" |
| introduction                   | "accounts":"259","day":"1670198400","uses":"274" |
| introduction                   | "accounts":"253","day":"1670112000","uses":"270" |
| introduction                   | "accounts":"245","working day":"1670025600","takes advantage of":"269" |
| introduction                   | "accounts":"345","day":"1669939200","works by using":"383" |
| introduction                   | "accounts":"307","day":"1669852800","works by using":"339" |
| introductionsfr                | "accounts":"","day":"1670371200","takes advantage of":""     |
| introductionsfr                | "accounts":"","day":"1670284800","uses":""     |
| introductionsfr                | "accounts":"","working day":"1670198400","utilizes":""     |
| introductionsfr                | "accounts":"","working day":"1670112000","works by using":""     |
| introductionsfr                | "accounts":"","working day":"1670025600","works by using":""     |

historical past is a JSONB column that holds an item with three fields. The revised query utilizes Postgres’s JSON indexing operator ->> to attain into that object and hoist the quantity of day-to-day makes use of into its possess column, so it can be the goal of a SQL SUM function.

Okay, prepared for the nifty resolution? Remember that https://mastodon.social/tags/introduction is the house web page for that variant of the tag. There you can see introduction posts from persons applying the tag. Those posts typically include things like other tags. In the dashboard proven higher than you can see that Kathy Nickels is applying these: #New music #Art #Equestrian #Character #Animals. The tags show up in her introduction write-up.

IDG

I did not promptly see how to capture them for use in the dashboard. Then I remembered that specified courses of Mastodon page have corresponding RSS feeds, and wondered if the tag web pages are members of a person this kind of course. Guaranteed plenty of they are, and https://mastodon.social/tags/introduction.rss is a detail. That website link, fashioned by tacking .rss onto the foundation URL, presents the further context I was searching for. Here’s the ultimate model of the query.

with knowledge as (
  choose 
    title,
    url,
    ( jsonb_array_elements(historical past) ->> 'uses' )::int as works by using
  from 
    mastodon_search_hashtag 
  in which 
    query = 'introduction'
  ),
  works by using as (
    select 
      identify,
      url || '.rss' as feed_website link,
      sum(utilizes) as new_uses
    from 
      information 
    group 
      by relationship, name, url
  )
  choose
    u.title,
    r.guid as hyperlink,
    to_char(r.revealed, 'YYYY-MM-DD') as revealed,
    r.classes
  from
    makes use of u
  be a part of
    rss_merchandise r
  on 
    r.feed_website link = u.feed_backlink
  in which
    current_uses > 1
  buy by 
    recent_employs desc, released desc
)

The new substances, courtesy of the RSS feed, are guid, which back links to an specific introduction like Kathy’s printed, which is the day the introduction appeared and types, which has the tags utilized in the introduction submit. Sweet! Now I can scan the dashboard to get a feeling of which introductions I want to verify out.

The very first 3 queries use the Steampipe plugin for Mastodon, and in unique its mastodon_search_hashtag table, which encapsulates the Mastodon API for looking tags. The ultimate model joins that desk with the rss_item desk provided by the RSS plugin, making use of the typical base URL as the foundation of the sign up for.

This delights me in so several approaches. When the blogosphere very first emerged in the early 2000s, some of us uncovered that the RSS protocol was able of considerably far more than just providing feeds to RSS readers. The other new hot protocol in that period was XML world-wide-web products and services. As an InfoWorld analyst I was meant to be cheering the latter as an business-grade technologies, but I could not support noticing that RSS stored turning out to be a good way to move details amongst cooperating programs. That is often been real, and I appreciate how this example reminds us that it is nevertheless true.

I’m equally delighted to show how Steampipe enables this contemporary training in RSS-powered integration. Steampipe was, at first, an motor for mapping effects from JSON API endpoints to SQL tables. Around time, while, it has broadened its look at of what constitutes an API. You can use Steampipe to question CSV data files, or Terraform documents, or—as we see here—RSS feeds. Details arrives in all forms of flavors. Steampipe abstracts these dissimilarities and brings all the flavors into a popular area wherever you can explanation more than them applying SQL.

And at last, it’s just fantastic to be at the intersection of Mastodon, Steampipe, and RSS in this amazing moment. I’ll quickly acknowledge that nostalgia is a element. But RSS did bust issues huge open 20 yrs ago, Mastodon’s accomplishing that now, and I like that RSS can help it occur all over again.

Now I need to have to produce that #introduction!