Mastodon, Steampipe, and RSS | InfoWorld

I used to be decided to jot down my Mastodon #introduction right now. 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. Once you search particularly for hashtags, the Mastodon search API returns this data.

"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 information 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 
  identify,
  url
from 
  mastodon_search_hashtag 
the place 
  question = 'introduction'

That produced a listing of hyperlinks, like https://mastodon.social/tags/introduction, to dwelling pages for variants of the tag. These are helpful hyperlinks! Every goes to a web page the place you’ll be able to 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 information as (
  choose 
    identify,
    url,
    ( jsonb_array_elements(historical past) ->> 'makes use of' )::int as makes use of
  from 
    mastodon_search_hashtag 
  the place 
    question = 'introduction'
)
choose
  identify,
  url,
  sum(makes use of)
from
  information
group by
  identify, url
order by
  sum desc

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

+-------------------+---------------------------------------------------+------+
| identify              | 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 solution to do it, however first let’s dwell on the revised question for a minute. Postgres’s JSON options are highly effective and it’s usually a problem (at the least for me) to visualise how they work.

The Postgres jsonb_array_elements() perform is what’s referred to as a set-returning perform. Right here it unpacks Postgres’s JSON illustration of the listing of historical past constructions returned from the Mastodon API. In its easiest kind, the perform name jsonb_array_elements(historical past) produces a short lived desk with per-tag, per-day information.

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

+--------------------------------+----------------------------------------------------+
| identify                           | 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 succeed in into that object and hoist the variety of each day makes use of into its personal column, so it may be the goal of a SQL SUM perform.

OK, prepared for the nifty answer? Recall that https://mastodon.social/tags/introduction is the house web page for that variant of the tag. There you’ll be able to see introduction posts from folks utilizing the tag. These posts usually embody different tags. Within the dashboard proven above you’ll be able to 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 lessons of Mastodon web page have corresponding RSS feeds, and questioned if the tag pages are members of 1 such class. Certain sufficient they’re, and https://mastodon.social/tags/introduction.rss is a factor. That hyperlink, shaped by tacking .rss onto the bottom URL, supplies the additional context I used to be in search of. Right here’s the ultimate model of the question.

with information as (
  choose 
    identify,
    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 
      identify,
      url || '.rss' as feed_link,
      sum(makes use of) as recent_uses
    from 
      information 
    group 
      by connection, identify, url
  )
  choose
    u.identify,
    r.guid as hyperlink,
    to_char(r.revealed, 'YYYY-MM-DD') as revealed,
    r.classes
  from
    makes use of u
  be part of
    rss_item r
  on 
    r.feed_link = u.feed_link
  the place
    recent_uses > 1
  order by 
    recent_uses desc, revealed desc
)

The brand new substances, courtesy of the RSS feed, are guid, which hyperlinks to a person introduction like Kathy’s; revealed, 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 particularly its mastodon_search_hashtag desk, which encapsulates the Mastodon API for looking tags. The ultimate model joins that desk with the rss_item desk offered by the RSS plugin, utilizing the widespread base URL as the idea of the be 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 excess of simply delivering feeds to RSS readers. The opposite new scorching protocol in that period was XML internet providers. As an InfoWorld analyst I used to be imagined to be cheering the latter as an enterprise-grade know-how, however I couldn’t assist noticing that RSS saved turning out to be a good way to maneuver information between cooperating methods. That’s at all times been true, and I really like how this instance reminds us that it’s nonetheless true.

I’m equally delighted to point out how Steampipe permits 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 information, or Terraform information, or—as we see right here—RSS feeds. Knowledge is available in all types of flavors. Steampipe abstracts these variations and brings all of the flavors into a standard house the place you’ll be able to purpose over them utilizing SQL.

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

Now I would like to jot down that #introduction!