Mastodon, Steampipe, and RSS | InfoWorld

I used to be decided to put in writing my Mastodon #introduction right this moment. To get began I used the tag search within the dashboard I’m constructing.

IDG

The concept was to have a look at a bunch of different #introduction posts to get a really feel for the way mine ought to go. While you search particularly for hashtags, the Mastodon search API returns this info.

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

A primary model of the dashboard, having solely this knowledge to work with, simply listed the names of tags matching the search time period together with corresponding URLs. Right here was the preliminary question.

choose 
  title,
  url
from 
  mastodon_search_hashtag 
the place 
  question = 'introduction'

That produced an inventory of hyperlinks, like https://mastodon.social/tags/introduction, to residence pages for variants of the tag. These are helpful hyperlinks! Every goes to a web page the place you may see who’s posting to the tag.

To make this view barely extra helpful, I tapped the third aspect of the API response, historical past, in a revised question.

with knowledge as (
  choose 
    title,
    url,
    ( jsonb_array_elements(historical past) ->> 'makes use of' )::int as makes use of
  from 
    mastodon_search_hashtag 
  the place 
    question = 'introduction'
)
choose
  title,
  url,
  sum(makes use of)
from
  knowledge
group by
  title, url
order by
  sum desc

These outcomes assist me resolve which variant to make use of.

+-------------------+---------------------------------------------------+------+
| 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    |

However I nonetheless want to go to every hyperlink’s web page to discover the way it’s getting used. It could be good to floor extra context within the dashboard, and I discovered a nifty technique to do it, however first let’s dwell on the revised question for a minute. Postgres’s JSON features are highly effective and it’s typically a problem (no less than for me) to visualise how they work.

The Postgres jsonb_array_elements() perform is what’s known as a set-returning perform. Right here it unpacks Postgres’s JSON illustration of the checklist of historical past buildings returned from the Mastodon API. In its easiest type, the perform name jsonb_array_elements(historical past) produces a brief desk with per-tag, per-day knowledge.

choose
  title,
  jsonb_array_elements(historical past) as historical past
from
  mastodon_search_hashtag 
the place 
  question = 'introduction'

+--------------------------------+----------------------------------------------------+
| title                           | historical past                                            |
+--------------------------------+----------------------------------------------------+
| introduction                   | {"accounts":"16","day":"1670371200","makes use of":"19"}   |
| introduction                   | {"accounts":"250","day":"1670284800","makes use of":"269"} |
| introduction                   | {"accounts":"259","day":"1670198400","makes use of":"274"} |
| introduction                   | {"accounts":"253","day":"1670112000","makes use of":"270"} |
| introduction                   | {"accounts":"245","day":"1670025600","makes use of":"269"} |
| introduction                   | {"accounts":"345","day":"1669939200","makes use of":"383"} |
| introduction                   | {"accounts":"307","day":"1669852800","makes use of":"339"} |
| introductionsfr                | {"accounts":"0","day":"1670371200","makes use of":"0"}     |
| introductionsfr                | {"accounts":"0","day":"1670284800","makes use of":"0"}     |
| introductionsfr                | {"accounts":"0","day":"1670198400","makes use of":"0"}     |
| introductionsfr                | {"accounts":"0","day":"1670112000","makes use of":"0"}     |
| introductionsfr                | {"accounts":"0","day":"1670025600","makes use of":"0"}     |

historical past is a JSONB column that holds an object with three fields. The revised question makes use of Postgres’s JSON indexing operator ->> to achieve into that object and hoist the variety of every day makes use of into its personal column, so it may be the goal of a SQL SUM perform.

OK, prepared for the nifty resolution? Recall that https://mastodon.social/tags/introduction is the house web page for that variant of the tag. There you may see introduction posts from individuals utilizing the tag. These posts usually embrace different tags. Within the dashboard proven above you may see that Kathy Nickels is utilizing these: #Music #Artwork #Equestrian #Nature #Animals. The tags seem in her introduction publish.

IDG

I didn’t instantly see seize them to be used within the dashboard. Then I remembered that sure courses of Mastodon web page have corresponding RSS feeds, and puzzled if the tag pages are members of 1 such class. Positive sufficient they’re, and https://mastodon.social/tags/introduction.rss is a factor. That hyperlink, shaped by tacking .rss onto the bottom URL, offers the additional context I used to be searching for. Right here’s the ultimate model of the question.

with knowledge as (
  choose 
    title,
    url,
    ( jsonb_array_elements(historical past) ->> 'makes use of' )::int as makes use of
  from 
    mastodon_search_hashtag 
  the place 
    question = 'introduction'
  ),
  makes use of as (
    choose 
      title,
      url || '.rss' as feed_link,
      sum(makes use of) as recent_uses
    from 
      knowledge 
    group 
      by connection, title, url
  )
  choose
    u.title,
    r.guid as hyperlink,
    to_char(r.printed, 'YYYY-MM-DD') as printed,
    r.classes
  from
    makes use of u
  be a part of
    rss_item r
  on 
    r.feed_link = u.feed_link
  the place
    recent_uses > 1
  order by 
    recent_uses desc, printed desc
)

The brand new components, courtesy of the RSS feed, are guid, which hyperlinks to a person introduction like Kathy’s; printed, which is the day the introduction appeared; and classes, which has the tags used within the introduction publish. Candy! Now I can scan the dashboard to get a way of which introductions I need to take a look at.

The primary three queries use the Steampipe plugin for Mastodon, and specifically its mastodon_search_hashtag desk, which encapsulates the Mastodon API for looking out tags. The ultimate model joins that desk with the rss_item desk offered by the RSS plugin, utilizing the frequent base URL as the idea of the be a part of.

This delights me in so some ways. When the blogosphere first emerged within the early 2000s, a few of us found that the RSS protocol was able to way over simply delivering feeds to RSS readers. The opposite new sizzling protocol in that period was XML net companies. As an InfoWorld analyst I used to be imagined to be cheering the latter as an enterprise-grade expertise, however I couldn’t assist noticing that RSS saved turning out to be a good way to maneuver knowledge between cooperating techniques. That’s all the time been true, and I like how this instance reminds us that it’s nonetheless true.

I’m equally delighted to indicate how Steampipe allows this contemporary train in RSS-powered integration. Steampipe was, initially, an engine for mapping outcomes from JSON API endpoints to SQL tables. Over time, although, it has broadened its view of what constitutes an API. You should utilize Steampipe to question CSV recordsdata, or Terraform files, or—as we see right here—RSS feeds. Knowledge is available in every kind of flavors. Steampipe abstracts these variations and brings all of the flavors into a typical house the place you may cause over them utilizing SQL.

And at last, it’s simply fantastic to be on the intersection of Mastodon, Steampipe, and RSS on this exceptional second. I’ll readily admit that nostalgia is an element. However RSS did bust issues large open 20 years in the past, Mastodon’s doing that now, and I like that RSS may also help it occur once more.

Now I would like to put in writing that #introduction!