< img src="https://images.idgesg.net/images/article/2018/10/abstract-arrows_direction_process_magnifying-glass_search_investigate-100777420-large.jpg?auto=webp&quality=85,70"alt= "" > I was identified to write my Mastodon #introduction today. To get started I used the tag search in the dashboard I’m developing.
IDG The concept was to take a look at a bunch of other #introduction posts to get a feel for how mine should go. When you browse particularly for hashtags, the Mastodon search API returns this details. “hashtags”: [“name”:”intro”,”url”:”https:, A very first version of the dashboard, having only this data to deal with, just listed the names of tags matching the search term along with matching URLs. Here was the preliminary query.select name, url from mastodon_search_hashtag where inquiry=’intro ‘That produced a list of links, like https://mastodon.social/tags/introduction, to web page for variations of the tag. These work links! Each goes to a page where you can see who is publishing to the tag.To make this view a little more useful, I tapped the third component of the API reaction, history, in a revised query.with information as (choose name, url, (jsonb_array_elements(history) ->> ‘uses’ ):: int as usages from mastodon_search_hashtag where query=’intro’) choose name, url, amount(uses) from data group by name, url order by amount desc
These results help me choose which variant to use.
+——————-+—————————————————+——+|name|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 still require to visit each link’s page to explore how it’s being utilized. It would be great to emerge more context in the control panel, and I found an awesome method to do it, however first let’s harp on the revised query for a minute. Postgres’s JSON features are powerful and it’s typically an obstacle (a minimum of for me) to picture how they work.The Postgres jsonb_array_elements()function is what’s called a set-returning function. Here it unpacks Postgres’s JSON representation of the list of history structures returned from the Mastodon API. In its simplest form, the function call jsonb_array_elements(history) produces a momentary table with per-tag, per-day information.
select name, jsonb_array_elements(history) as history from mastodon_search_hashtag where query=’intro’ +——————————–+—————————————————-+|name|history|+——————————–+—————————————————-+|intro||| introduction||| intro||| introduction|”accounts”:”253″,”day”:”1670112000″,”uses”:”270″|| intro||| introduction|”accounts”:”345″,”day”:”1669939200″,”uses”:”383″|| introduction|”accounts”:”307″,”day”:”1669852800″,”usages”:”339″|| introductionsfr||| introductionsfr||| introductionsfr||| introductionsfr|”accounts”:”0″,”day”:”1670112000″,”usages”:”0″|| introductionsfr||
history is a JSONB column that holds a things with three fields. The modified inquiry utilizes Postgres’s JSON indexing operator ->> to reach into that item and raise the number of day-to-day usages into its own column, so it can be the target of a SQL amount function.OK, ready for
the cool option? Remember that https://mastodon.social/tags/introduction is the home page for that version of the tag. There you can see intro posts from people using the tag. Those posts usually include other tags. In the dashboard revealed above you can see that Kathy Nickels is utilizing these: #Music #Art #Equestrian #Nature #Animals. The tags appear in her introduction post.
IDG I didn’t instantly see how to record them for usage in the control panel. Then I kept in mind that certain classes of Mastodon page have matching RSS feeds, and questioned if the tag pages are members of one such class. Certainly they are, and https://mastodon.social/tags/introduction.rss is a thing. That link, formed by tacking.rss onto the base URL, provides the additional context I was trying to find. Here’s the last version of the query.with information as (select name, url, (jsonb_array_elements(history) ->> ‘usages’ ):: int as usages from mastodon_search_hashtag where question=’introduction’ ), utilizes as (select name, url|| ‘. rss’ as feed_link, sum(uses) as recent_uses from data group by connection, name, url) choose u.name, r.guid as link, to_char(r.published, ‘YYYY-MM-DD’) as released, r.categories from uses u join rss_item r on r.feed _ link = u.feed _ link where recent_uses > 1 order by recent_uses desc, released desc)
The new components, courtesy of the RSS feed, are guid, which links to a specific intro like Kathy’s; released, which is the day the intro appeared; and classifications, which has actually the tags used in the intro post. Sweet! Now I can scan the control panel to get a sense of which intros I wish to check out.
The very first three inquiries use the Steampipe plugin for Mastodon, and in specific its mastodon_search_hashtag table, which encapsulates the Mastodon API for searching tags. The last variation joins that table with the rss_item table offered by the RSS plugin, utilizing the common base URL as the basis of the join.This thrills me in many ways. When the blogosphere first emerged in the early 2000s, a few of us discovered that the RSS procedure can far more than just delivering feeds to RSS readers. The other brand-new hot protocol in that era was XML web services. As an InfoWorld analyst I was expected to be cheering the latter as an enterprise-grade innovation, however I could not assist seeing that RSS kept turning out to be a great way to move information between cooperating systems. That’s always held true, and I like how this example reminds us that it’s still true.I’m equally delighted to demonstrate how Steampipe enables this contemporary workout in RSS-powered combination. Steampipe was, originally, an engine for mapping arise from JSON API endpoints to SQL tables. Gradually, though, it has expanded its view of what constitutes an API. You can utilize Steampipe to query CSV files, or Terraform files, or– as we see here– RSS feeds. Information is available in all sort of tastes. Steampipe abstracts those differences and brings all the tastes into a typical area where you can reason over them using SQL.And finally, it’s simply terrific to be at the intersection of Mastodon, Steampipe, and RSS in this amazing minute. I’ll readily admit that nostalgia is an aspect. However RSS did bust things large open twenty years ago, Mastodon’s doing that now, and I love that RSS can help it occur once again.
Now I require to write that #introduction! Copyright © 2022 IDG Communications, Inc. Source
