Mastodon, Steampipe, and RSS | InfoWorld | Tech Prism

I used to be decided to put in writing my #intro to Mastodon right this moment. To get began, I used the tag search on the board I am creating.

IDG

The concept was to take a look at a bunch of different #intro posts to get an concept of ​​what mine ought to appear like. Whenever you particularly seek 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"
        ,
        // ...
      ]
    ,

An early model of the dashboard, which solely had this information to work with, solely listed the tag names that matched the search time period together with the 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 touchdown pages for variants of the tag. These are helpful hyperlinks! Every one goes to a web page the place you may see who’s posting to the tag.

To make this view a bit extra helpful, I touched on the third factor of the API response, historical pastin a revised question.

with information 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
  information
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 will be good to point out extra context on the dashboard, and I discovered a nifty method to try this, however first let’s dwell on the revised question for a minute. Postgres’ JSON capabilities are highly effective, and it is usually difficult (at the least for me) to visualise how they work.

the postgres jsonb_array_elements() perform is what known as set return perform. Right here you unzip the Postgres JSON illustration of the checklist of historical past buildings returned by the Mastodon API. In its easiest type, the perform name jsonb_array_elements(historical past) produces a brief desk with information by tag and by day.

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 incorporates an object with three fields. The revised question makes use of the Postgres JSON indexing operator ->> to get to that object and lift the variety of every day makes use of by itself column, so it may be focused by an SQL SUM perform.

OK, prepared for the intelligent resolution? Keep in mind that https://mastodon.social/tags/introduction is the house web page for that tag variant. There you may see intro posts from individuals utilizing the hashtag. These posts usually embrace different tags. On the board proven above, you may see that Kathy Nickels is carrying these: #Music #Artwork #Equestrian #Nature #Animals. The tags seem in her intro publish.

IDG

I did not instantly see the right way to seize them to be used on the board. Then I remembered that sure lessons of Mastodon pages have corresponding RSS feeds, and questioned if tag pages are members of a kind of lessons. Certainly they’re, and https://mastodon.social/tags/introduction.rss is one factor. That bond, shaped by tacking .rss within the base URL, it supplies the extra context you had been searching for. That is the ultimate model of the question.

with information 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 
      information 
    group 
      by connection, title, 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_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 guidwhich hyperlinks to a solo introduction like Kathy’s; revealed, which is the day the introduction appeared; Y classes, which has the tags used within the intro publish. Candy! Now I can scan the board to get an concept of ​​what shows I wish to see.

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

This delights me in some ways. When the blogosphere first emerged within the early 2000s, a few of us found that the RSS protocol was able to rather more than simply delivering feeds to RSS readers. The opposite scorching new protocol on the time was XML internet companies. As an analyst for InfoWorld, I used to be alleged to be rooting for the latter as an enterprise-grade know-how, however I could not assist however discover that RSS saved turning out to be a good way to maneuver information between cooperative techniques. That is at all times been true, and I really like how this instance reminds us that it is nonetheless true.

I am equally delighted to point out how Steampipe permits this contemporary train in RSS-based integration. Steampipe was initially an engine for mapping outcomes from JSON API endpoints to SQL tables. Nonetheless, over time, you might have broadened your 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 kinds of flavors. Steampipe abstracts away these variations and brings all of the flavors collectively in a standard house the place you may motive about them utilizing SQL.

And at last, it is simply great to be on the intersection of Mastodon, Steampipe and RSS at this extraordinary time. I am going to readily admit that nostalgia is an element. However RSS totally opened issues up 20 years in the past, Mastodon is doing it now, and I really like that RSS can assist make it occur once more.

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