This is part one of the Building a Voice-Driven TV Remote series:

1. Getting The Data
2. Adding Search
3. The Device API
4. Some Basic Alexa Commands
5. Adding a Listings Search Command
6. Starting to Migrate from HTTP to MQTT
7. Finishing the Migration from HTTP to MQTT
8. Tracking Performance with Application Insights

When thinking recently about the types of things I wish I could control by voice in my house, one of the obvious answers was everything in my media center setup. I've had a Harmony One for over eight years now which has always done a great job at helping coordinate all the different devices and activities (seriously, few products age as well as that thing has), but that of course requires actually using the remote. I figured it was time for an upgrade, so I picked up a Harmony Elite which has a number of nice upgrades over the One, and also provides an Alexa skill for controlling your devices.

Overall the Elite is a great device, as I expected, and the Alexa skill is decent. It works really well for being able to issue commands like "Alexa, turn on the TV", "Alexa, turn on HBO", or "Alexa, turn on Apple TV". I found that even this limited functionality was an immediate win in usability. Need to turn off the TV? No need to go hunt down the remote now. Want to change the channel while pacing aound the kitchen making dinner? No problem.

I quickly found myself wanting more, though. The channel support is ok, but requires adding any channel you want to use as a favorite, and then clumsily removing and readding the skill to get it to take. I want to be able to say things like "Alexa, turn on the Rangers game", "Alexa, what's on HBO?", or "Alexa, when is Westworld on?" I also want to be able to trigger other remote commands outside of just changing channels, such as muting the receiver or pausing the playback. It quickly became clear that I was going to descend into the rabbit hole a bit and see what I could piece together to make something work.

I figured that along the way I may as well blog the steps as they happen as added incentive to keep making progress, as well as to just share how I'm building it and how things turn out. I have no clue how long this will take since it's very much a spare time project, or what the "final" form will be. My main goals from the start are for it to be cheap and quick. I am going to use Azure for the infrastructure pieces, and F# as the programming language whenever possible. Let's get to it!

Getting The Data

First things first: I need data. There are a variety of services out there offering channel and listing data, with a wide range of costs associated with them. For now I've decided to go with XML TV Listings, who cost a measly $20 per year and seem to have pretty good data so far.

On the Azure side I went with the smallest SQL Server option they had which is more than enough for the small amounts of data I'll be storing and costs just $5 per month.

Channels

Since channel data doesn't really change very much, for now I'm just going with a once-off channel import into the database. I can always come back and update this again down the line, but it's good enough to get started. I pulled down a list of my local channels from their API and saved it to a local file. The format looks like this (I also don't want to reproduce their data here so this is just one channel):

<?xml version="1.0" encoding="UTF-8" ?>
<tv date="12/03/2016" source-info-url="http://www.tvmedia.ca" source-info-name="TV Media">
    <channel id="1766.stations.xmltv.tvmedia.ca">
        <display-name>CBS (WCBS) New York, NY</display-name>
        <display-name>WCBS-TV1</display-name>
        <display-name>2</display-name>
        <url>newyork.cbslocal.com/</url>
        <icon src="http://cdn.tvpassport.com/image/station/76x28/cbs.png"/>
    </channel>
</tv>

Now I just needed to get that into my SQL database. Thankfully, the magic of F# type providers make ETL processes like this super easy and typesafe! Here's what that script looked like:

#r "../packages/FSharp.Data/lib/net40/FSharp.Data.dll"
#r "../packages/FSharp.Data.SqlClient/lib/net40/FSharp.Data.SqlClient.dll"
#r "System.Xml.Linq.dll"
#r "System.Configuration"

open System.IO
open System.Text.RegularExpressions
open FSharp.Data
open FSharp.Data.SqlClient

type Channels = XmlProvider<"./channels.xml">
let doc = Channels.Parse (File.ReadAllText "./channels.xml")

type TvChannel = { XmlTvId: string; FullName: string; DisplayName: string; Number: int }

doc.Channels
|> Array.map (fun channel ->
    match channel.DisplayNames with
    | [| fullName; displayName; number |] -> Some({ XmlTvId = channel.Id
                                                    FullName = fullName.String.Value
                                                    DisplayName = Regex.Replace(displayName.String.Value, "\\-HD$", "", RegexOptions.None)
                                                    Number = number.Number.Value })
    | _ -> None
)
|> Array.choose id
|> Array.filter (fun channel -> channel.Number > 500 && channel.Number < 1000)
|> Array.iter (fun channel ->
    use cmd = new SqlCommandProvider<"INSERT INTO Channel VALUES (@xmlTvId, @displayName, @fullName, @number)", "name=TVListings">()
    cmd.Execute(xmlTvId = channel.XmlTvId, displayName = channel.DisplayName, fullName = channel.FullName, number = channel.Number) |> ignore

    printfn "Added channel: %s" channel.DisplayName
)

This script goes through the channels as defined in the XML file, filters out ones outside the range of channels I care about, and inserts them into the database. It also does a little bit of data cleanup to remove -HD suffixes. There is certainly more cleanup I'll try to do in the future, but this was more than good enough for a start. Now we've got channels!

Listings

Channels are great and all, but listings are where we really get to start having some fun. Their API allows you to pull up to a week's worth of listings, but you can only make five lineup calls per day. Because of that, and the desire to keep the data small, I decided to go with a daily task that gets that day's listings, purges the existing listings in the database, and inserts the new ones. This will be implemented as a set of Azure Functions.

DownloadListings

The first function will run on a schedule, download the day's listings, and store those listings in a new XML file in blob storage. Here's the function.json for that:

{
  "bindings": [
    {
      "name": "timerTrigger",
      "type": "timerTrigger",
      "direction": "in",
      "schedule": "0 0 0 * * *"
    },
    {
      "type": "blob",
      "name": "lineupsBlob",
      "path": "lineups/{rand-guid}.xml",
      "connection": "tvlistingstorage_STORAGE",
      "direction": "out"
    }
  ],
  "disabled": false
}

Now we can define the function itself:

open System
open FSharp.Data

let Run(timerTrigger: TimerInfo, log: TraceWriter, lineupsBlob: byref<string>) =
    let lineupUrl = sprintf "https://www.xmltvlistings.com/xmltv/get/%s/%s/1/0" 
                            (Environment.GetEnvironmentVariable("XmlTvApiKey"))
                            (Environment.GetEnvironmentVariable("XmlTvLineupId"))
	let xml = Http.RequestString(lineupUrl, responseEncodingOverride = "utf-8")
    
    lineupsBlob <- xml

    sprintf "Successfully downloaded XML, length: %i" xml.Length |> log.Info

That's it! This uses some environment variables to put together the request URL, downloads the XML, and stuffs it into a new file in blog storage. This is a great example of the kinds of things that Azure Functions makes easy to piece together. The listings API currently returns the XML with an encoding of utf8 instead of utf-8 which makes .NET upset, but luckily F# Data lets us easily override that here.

ImportData

Next we'll want a function that is triggered by that file being added to blog storage to import it into the SQL database. Here's that function.json:

{
  "bindings": [
    {
      "name": "xml",
      "type": "blobTrigger",
      "direction": "in",
      "path": "lineups/{name}",
      "connection": "tvlistingstorage_STORAGE"
    }
  ],
  "disabled": false
}

Now, to keep things a little tidier I'm going to split the function up across a couple files. First I'll create a Database.fs module that has all the database functions:

module Database

open FSharp.Data
open FSharp.Data.SqlClient

[<Literal>]
let configFile = "D:\\home\\site\\wwwroot\\ImportLineup\\app.config"

let getChannelLookup() =
    use cmd = new SqlCommandProvider<"SELECT ChannelId, XmlTvId FROM Channel", "name=TVListings", ConfigFile=configFile>()
    cmd.Execute()
    |> Seq.map(fun row -> (row.XmlTvId, row.ChannelId))
    |> dict

let clearShows() =
    use cmd = new SqlCommandProvider<"TRUNCATE TABLE Show", "name=TVListings", ConfigFile=configFile>()
    cmd.Execute() |> ignore

let addShow title startTime endTime channelId description category =
    use cmd = new SqlCommandProvider<"INSERT INTO Show VALUES (@title, @startTime, @endTime, @channelId, @description, @category)", "name=TVListings", ConfigFile=configFile>()
    cmd.Execute(title = title, startTime = startTime, endTime = endTime,
                channelId = channelId, description = description, category = category) |> ignore

Here I'm using a SQL type provider to get nice strongly typed access to the database. It really is fantastic. Next we can define the main function implementation:

#load "Database.fs"
open System.Collections.Generic
open FSharp.Data

type Listings = XmlProvider<"D:\\home\\site\\wwwroot\\ImportLineup\\schedule-sample.xml">

let addShow (log: TraceWriter) (channelLookup: IDictionary<string, int>) (show: Listings.Programme) =
    match channelLookup.TryGetValue show.Channel with
    | (true, channelId) ->
        let title = match (show.Title.Value, show.SubTitle) with
                    | "Movie", Some(subtitle) -> subtitle.Value.Trim()
                    | title, _ -> title.Trim()
        let description = if show.Desc.IsSome then show.Desc.Value.Value else ""
        
        Database.addShow title show.Start show.Stop channelId description show.Category.Value

        sprintf "Added show : %s" title |> log.Info
    | (false, _) -> ()

let Run(xml: string, name: string, log: TraceWriter) =
    sprintf "Starting import for: %s" name |> log.Info

    let channelLookup = Database.getChannelLookup()
    let listings = Listings.Parse xml

    Database.clearShows()

    listings.Programmes
    |> Array.iter (addShow log channelLookup)

    sprintf "Finished import for: %s" name |> log.Info

This makes use of the XML type provider again, configured against a small set of sample data I extracted from their API to seed the type provider. It does some data cleanup based on one pattern I saw where movies would often have a title of "Movie" and have the movie title be in the subtitle. F# pattern matching makes this kind of parsing trivial and easy to read. Now we've got channels and listings!

Summary

This is obviously just a basic start for things, and does very little in the way of error handling or anything like that, but it was fun to see how quickly I could get these things running in a real environment. I hope it also shows the power of a language like F# in concisely defining real-world code. In the next post I'll talk about setting up a search service to allow for easily querying this data.

Next post in series: Part 2: Adding Search