I am working on following somewhat in Peter’s footsteps. He has his FreshRSS reader connected to his weblog, for everything he ‘stars’ in his feed reader. Those get posted to his ‘favourites’ stream, and send webmentions so they end up as ‘likes’ underneath the original posts. See Peter’s description on his blog.

My plans are a bit more ambitious, an ambition that may yet well bump into the limtis of my php / mysql capabilities.

I want to be able to mark articles in the feed reader for three things: bookmarking, favouriting, and replies. Then I want to add two pieces of content, and post that to my site.

When I favourite something on my blog, I basically always add 2 things when posting it: a rationale for why I like something, and a quote from the original article that meant something to me. See the image below, a screenshot of a ‘favourite’ I marked earlier this month.

That post is made of a short template that I now activate when in my blog’s editor with a hotkey (using AlfredApp) that creates the star, and has a place for the URL, title and author, plus my own motivation for posting, and a quote with the author’s name repeated.
Favourites, bookmarks and replies follow the same template. The only difference is the symbol shown, and the microformat used to signal to the original article what sort of webmention I’m sending their way (so they know how to treat it, e.g. as a like or as a comment/reply)

Peter’s example of favouriting by starring leads to setting a boolean field in the content table itself of the database.
FreshRSS lacks three distinct ‘starring’ buttons, but I can easily add labels to a posting. Those labels are stored in a separate table from the feed articles, unlike when starring.

The first step to take then is to gain access to the postings I mark for follow-up during feed reading. I’ve used the following SQL query:

SELECT ton_entry.link, ton_entry.author, ton_entry.title, ton_entrytag.id_tag, uncompress(ton_entry.content_bin) as content FROM ton_entry JOIN ton_entrytag ON ton_entry.id=ton_entrytag.id_entry

The two key differences with Peter’s SQL statement are, the JOIN part, and the uncompress part. The JOIN statement combines the table that knows what labels I applied with the table that contains all articles, and selects only those where an article ID is in both tables, resulting in a list of the articles I applied labels to. Because in a later step I want to select a quote from the source article the SQL statement also grabs the content of an article. That content is stored as a compressed binary blob (yes, blob is its official name) in the database. Using ‘uncompress’ makes the content blob human readable again, and the ‘as content’ bit puts it in a variable called content.

The next step is allowing me to provide my remarks and select a quote through a form, and the third step to add all that in to the right template based on the label (favourite, bookmark, reply), after which I need to put it into my WordPress install in the right categories and publish it.

2 reactions on “Messing Around In The FreshRSS Back-End

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.