Mastodon, Steampipe, and RSS | InfoWorld

I was decided to write my Mastodon #introduction today. To get commenced I employed the tag research in the dashboard I’m creating.

IDG

The notion was to seem at a bunch of other #introduction posts to get a really feel for how mine should go. When you lookup specifically for hashtags, the Mastodon research API returns this details.

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

A initial version of the dashboard, acquiring only this information to do the job with, just outlined the names of tags matching the search term together with corresponding URLs. Right here was the preliminary query.

choose 
  title,
  url
from 
  mastodon_research_hashtag 
exactly where 
  question = 'introduction'

That generated a listing of links, like https://mastodon.social/tags/introduction, to residence web pages for variants of the tag. These are handy one-way links! Each and every goes to a web page where by you can see who is putting up to the tag.

To make this see slightly far more beneficial, I tapped the 3rd component of the API response, heritage, in a revised query.

with knowledge as (
  pick 
    title,
    url,
    ( jsonb_array_elements(historical past) ->> 'uses' )::int as utilizes
  from 
    mastodon_search_hashtag 
  in which 
    question = 'introduction'
)
decide on
  title,
  url,
  sum(utilizes)
from
  facts
group by
  identify, url
purchase by
  sum desc

These success assistance me make a decision 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 however need to visit every single link’s page to check out how it’s getting utilized. It would be pleasant to surface much more context in the dashboard, and I located a nifty way to do it, but first let us dwell on the revised query for a moment. Postgres’s JSON options are powerful and it’s often a problem (at minimum for me) to visualize how they operate.

The Postgres jsonb_array_features() operate is what is named a established-returning functionality. In this article it unpacks Postgres’s JSON illustration of the list of history constructions returned from the Mastodon API. In its most straightforward kind, the function get in touch with jsonb_array_components(history) creates a momentary desk with for every-tag, for each-working day data.

pick out
  name,
  jsonb_array_elements(heritage) as history
from
  mastodon_look for_hashtag 
exactly where 
  query = 'introduction'

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

history is a JSONB column that holds an item with three fields. The revised question employs Postgres’s JSON indexing operator ->> to access into that item and hoist the amount of day-to-day makes use of into its have column, so it can be the goal of a SQL SUM perform.

Okay, prepared for the nifty remedy? Recall that https://mastodon.social/tags/introduction is the home page for that variant of the tag. There you can see introduction posts from persons utilizing the tag. These posts normally incorporate other tags. In the dashboard revealed over you can see that Kathy Nickels is applying these: #Tunes #Artwork #Equestrian #Nature #Animals. The tags look in her introduction put up.

IDG

I didn’t promptly see how to seize them for use in the dashboard. Then I remembered that certain classes of Mastodon web site have corresponding RSS feeds, and questioned if the tag web pages are members of one this kind of course. Confident plenty of they are, and https://mastodon.social/tags/introduction.rss is a detail. That hyperlink, formed by tacking .rss onto the base URL, presents the extra context I was seeking for. Here’s the final model of the question.

with data as (
  select 
    identify,
    url,
    ( jsonb_array_things(background) ->> 'uses' )::int as makes use of
  from 
    mastodon_look for_hashtag 
  exactly where 
    question = 'introduction'
  ),
  works by using as (
    choose 
      name,
      url || '.rss' as feed_backlink,
      sum(works by using) as modern_takes advantage of
    from 
      data 
    group 
      by relationship, title, url
  )
  choose
    u.identify,
    r.guid as backlink,
    to_char(r.released, 'YYYY-MM-DD') as released,
    r.groups
  from
    utilizes u
  be part of
    rss_product r
  on 
    r.feed_connection = u.feed_connection
  in which
    recent_utilizes > 1
  get by 
    recent_utilizes desc, posted desc
)

The new elements, courtesy of the RSS feed, are guid, which links to an specific introduction like Kathy’s posted, which is the day the introduction appeared and categories, which has the tags employed in the introduction write-up. Sweet! Now I can scan the dashboard to get a sense of which introductions I want to examine out.

The initial a few queries use the Steampipe plugin for Mastodon, and in distinct its mastodon_look for_hashtag table, which encapsulates the Mastodon API for searching tags. The final version joins that table with the rss_item table delivered by the RSS plugin, making use of the prevalent base URL as the foundation of the sign up for.

This delights me in so several methods. When the blogosphere to start with emerged in the early 2000s, some of us learned that the RSS protocol was able of significantly a lot more than just delivering feeds to RSS audience. The other new sizzling protocol in that era was XML world-wide-web products and services. As an InfoWorld analyst I was intended to be cheering the latter as an company-grade know-how, but I could not assist noticing that RSS saved turning out to be a fantastic way to go information concerning cooperating units. Which is often been genuine, and I really like how this instance reminds us that it is however legitimate.

I’m equally delighted to show how Steampipe enables this modern day physical exercise in RSS-run integration. Steampipe was, at first, an motor for mapping results from JSON API endpoints to SQL tables. In excess of time, although, it has broadened its view of what constitutes an API. You can use Steampipe to question CSV documents, or Terraform documents, or—as we see here—RSS feeds. Data will come in all forms of flavors. Steampipe abstracts individuals discrepancies and delivers all the flavors into a frequent space where you can motive more than them applying SQL.

And last but not least, it is just excellent to be at the intersection of Mastodon, Steampipe, and RSS in this outstanding moment. I’ll readily admit that nostalgia is a issue. But RSS did bust factors large open 20 decades back, Mastodon’s carrying out that now, and I appreciate that RSS can support it occur yet again.

Now I want to produce that #introduction!