AndroidApps

My Tasker+sqlite adventures continue with a Tv Show Favs add-on

Screenshot_2013-07-29-15-59-34

Last week, I wrote about how I started looking into using sqlite with Tasker, with my first creation being an add-on for a postal tracking app. Over the weekend, I played with it a bit more, and have a few more things working. I’m not ready to write a guide for sqlite in general yet (and honestly haven’t decided if I’m going to do one at all), but I wanted to share one of my sqlite creations to provide ideas for those who know how to do it already.

I’m a TV show junkie, and I follow a lot of series. That’s why I’m using TV Show Favs, an app that allows you to track the shows you’re following. I’ve wanted Tasker integration for a long while, but despite the developer promising to look into it, nothing has come of it. With my new knowledge of sqlite, however, I managed to create that interaction myself.

TV Show Favs stores all its information in a database. Episodes are stored in a table, with columns for air date, title, and so on. I wanted to create a notification system that would give me a notification each day for that day’s shows, so the first thing I needed to extract was what episodes would air that day. I did that by simply finding unix time values for the start and end of the day and then using the command you see in the screenshot to extract any episodes that air within that range.

This returns only the series_id, by design, as I don’t need to know what the episodes that air are called, just which shows they’re from. Only a numerical ID for the series is available in the episode list, however, not a title. To find the title, it refers to another table to find the title from the id, as seen in the second screenshot. It does this with a For loop for all IDs returned the first time. It then sticks those into a notification using AutoNotification. This is done automatically every morning a 5.

This is the second creation I’ve made with sqlite that adds something that I’ve wanted for a long time to an existing app. It’s great to be able to do things like that, and it saves you from having to create a complete framework from scratch just to get a tiny feature.

tasker banner - for some reason we don't have an alt tag here

Pocketables does not accept targeted advertising, phony guest posts, paid reviews, etc. Help us keep this way with support on Patreon!
Become a patron at Patreon!

Andreas Ødegård

Andreas Ødegård is more interested in aftermarket (and user created) software and hardware than chasing the latest gadgets. His day job as a teacher keeps him interested in education tech and takes up most of his time.

Avatar of Andreas Ødegård

7 thoughts on “My Tasker+sqlite adventures continue with a Tv Show Favs add-on

  • Avatar of MobiMikes

    Nice work! I’m going to have a play with this later.

    Reply
  • I use a couple sqlite scripts to grab email information (From, Email, Subject, etc.) from the Gmail database everytime a new email comes in. I set it up very similarly to your screen off pop-up, but mine shows the sender and subject.

    Tried creating a couple tasker integrated databases of my own for some other things, but just don’t have the time…

    Reply
  • I’m still learning Tasker, but I’ve definitely learned a lot from your articles here! Now I’ve finally got a spot where I can return a bit of knowledge. :) (Databases are my area of expertise)

    SQL lets you join two or more tables into one larger (virtual) table for query purposes, using a value they have in common. In this case, the series_id field. Your two separate queries can be combined into the following (eliminating the loop in Tasker):

    select series_title
    from series, episodes
    where series.series_id = episodes.series_id
    and episodes.air_date beween %date and %enddate

    You’ll noticed I also simplified the dates. Any time you want to do “x > y and x = y and x <= z".

    Reply
    • Avatar of Andreas Ødegård

      Thank you! I had a feeling there was a more straight forward way, I’ve just been going on what’s available in the Sqlite Debugger app I’m using to build queries, browse databases, and learn what’s possible. This simplifies it a lot, thanks!

      This also proves that I’m right in not doing a guide yet. Doing a guide based on something I don’t fully know and understand is not a good idea.

      Reply
  • Not sure how that last sentence got screwed up. Apparently a character limit because it lost most of my last paragraph, but it dropped from the middle and kept the end.

    Anyways, what I was saying is that “x between y and z” is equivalent to “x >= y and x <= z".

    Reply
    • Avatar of Andreas Ødegård

      Yeah that’s the one I’m using

      Reply

Leave a Reply

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