This is part seven 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

In the last post of the series I introduced a MQTT bridge to connect my Harmony system with Azure IoT, so now it's time to switch things over and remove the need for my functions to make API calls into my house.

Switching Commands

Naturally Microsoft has a handy package available that makes communicating with Azure IoT Hubs a breeze called Microsoft.Azure.Devices. In the implementation of RemoteSkill, recall that the commands.fsx file had this as the implementation for sending a command to the device:

let private makeRequest method urlPath =
    let url = sprintf "%s/%s" (Environment.GetEnvironmentVariable("HarmonyApiUrlBase")) urlPath
    let authHeader = "Authorization", (Environment.GetEnvironmentVariable("HarmonyApiKey"))

    Http.RequestString(url, httpMethod = method, headers = [authHeader])

let executeCommand commandSlug = sprintf "commands/%s" commandSlug |> makeRequest "POST" |> ignore

This meant that every command needed to assume all of the overhead of HTTP calls into my house. In addition to the fact that this meant my house needed a public API, this was also a performance killer since a command here effectively maps to a button pressed on a remote. Entering a channel number would result in four commands being sent - three for the digits and then one to hit enter.

Here's an updated implementation that sends the command through the IoT Hub:

let serviceClient = ServiceClient.CreateFromConnectionString (Environment.GetEnvironmentVariable("IoTHubConnectionString"))

let executeCommand commandSlug = 
    async {
        sprintf "harmony-api/hubs/living-room/command;%s" commandSlug
        |> Encoding.ASCII.GetBytes
        |> fun bytes -> new Message(bytes)
        |> fun message -> serviceClient.SendAsync("harmony-bridge", message)
        |> Async.AwaitIAsyncResult 
        |> Async.Ignore
        |> ignore

        do! Async.Sleep 250
    } |> Async.RunSynchronously

Since the signature of executeCommand remains unchanged, that's all that actually has to change! The 250ms sleep in between commands here is to make sure there's a little space in between commands sent one after another. While testing this I quickly found that it was easy to crash my cable box by sending requests too quickly:

Well, today’s accomplishment is crashing my FiOS cable box by sending commands to it via an Azure IoT Hub pic.twitter.com/eBdg4r1nLa

— Greg Shackles (@gshackles) August 5, 2017

With just this one method implementation change, all commands being sent into my house are now being routed through IoT Hub.

Switching Queries

Switching command execution over to MQTT gave a real noticeable performance boost, but it didn't totally eliminate the need for a public-facing API that the functions could access. Also in the commands.fsx file was one other method that would query the API for active commands for the current activity of my media center:

let getCommand (label: string) =
    makeRequest "GET" "commands"
    |> CommandsResponse.Parse
    |> fun res -> res.Commands
    |> Seq.tryFind (fun command -> command.Label.ToLowerInvariant() = label.ToLowerInvariant())

This one was a little trickier to switch to MQTT since it relies on state that changes depending on the active activity (where activity can be watching TV, AppleTV, Fire Stick, etc). To solve for this I decided to add some more persistence to the application, where every time the activity changed it would update the persistence to have the latest set of commands available to use.

Adding the Database

I thought about introducing something like Redis or CosmosDB for it, but that ended up being at odds with my goal of keeping this thing as cheap as possible. I already have a SQL instance that costs around $5 per month, while adding Redis would be another $16, and CosmosDB another $24. Based on price, adding another table in SQL Server was the obvious answer. I went ahead and created this simple table:

CREATE TABLE AvailableCommand(
	AvailableCommandId int IDENTITY(1,1) NOT NULL,
	Name nvarchar(32) NOT NULL,
	Slug nvarchar(16) NOT NULL,
	Label nvarchar(32) NOT NULL
)

There's no real need to keep any historic data, so each time the activity changes I'll just blow away the old data and replace it with the new ones. There are realistically only going to be up to 20-30 commands for each activity anyway.

Storing the Data

With the database table in place, the next task was to update the IoT bridge I created in the last post to subscribe to the MQTT topic that gets notified when the activity changes. When it changes, it can make the HTTP API call locally (so no need for this to ever be exposed externally to the Raspberry Pi itself or the local network) to get the new set of commands, and persist that to Azure.

To do that I pulled in a couple npm packages to simplify HTTP calls and database access:

npm i --save request-promise tedious 

Next I'll add a function that, given a list of commands, updates the SQL database:

function updateAvailableCommands(commands) {
    const connection = new tedious.Connection(config.sqlConfig);
    connection.on('connect', err => {
        if (err) {
            console.error('Error connecting to SQL', err);
            return;
        }

        const truncateRequest = new tedious.Request("truncate table AvailableCommand", err => {
            if (err) {
                console.error('Error truncating table', err);
            }
        });

        truncateRequest.on('requestCompleted', () => {
            const updateCommands = connection.newBulkLoad('AvailableCommand', (err, rowCount) => {
                if (err) {
                    console.error('Error inserting commands', err);
                } else {
                    console.log(`Inserted ${rowCount} command(s)`);
                }
            });
            updateCommands.addColumn('Name', tedious.TYPES.NVarChar, { nullable: false });
            updateCommands.addColumn('Slug', tedious.TYPES.NVarChar, { nullable: false });
            updateCommands.addColumn('Label', tedious.TYPES.NVarChar, { nullable: false });

            commands.forEach(command => 
                updateCommands.addRow({ Name: command.name, 
                                        Slug: command.slug, 
                                        Label: command.label }));

            connection.execBulkLoad(updateCommands);
        })

        connection.execSql(truncateRequest);
    }); 
}

Most of the code here is either JavaScript ceremony or error logging, so there's not too much going on. It simply truncates the existing data in the table and bulk loads the new data in.

Lastly, I just need to subscribe to the topic and update the commands when the activity changes:

broker.on('publish', packet => {
    if (packet.topic !== 'harmony-api/hubs/living-room/current_activity') {
        return;
    }

    request({ url: 'http://localhost:8282/hubs/living-room/commands', json: true })
        .then(res => updateAvailableCommands(res.commands));
});

Now whenever the activity changes the SQL database will be updated with the new set of available commands.

Updating the Function

Now that I've got a data store in place with a list of the available commands, I just need to rip out that last API call from commands.fsx and replace it with a database read:

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

let getCommand (label: string) =
    use cmd = new SqlCommandProvider<"SELECT Slug FROM AvailableCommand WHERE Label=@label", "name=TVListings", ConfigFile=configFile, SingleRow=true>()
    cmd.Execute(label = label)

Thanks to the beauty of the SQL type provider that's all that's actually needed to read out the command in a typesafe way. This change does actually change the signature of getCommand over the previous version, though, in that it only returns the slug instead of the full command object. That just means I need to tweak the handleDirectCommand method in run.fsx to just expect the slug, which is all it cared about anyway:

let handleDirectCommand (intent: Intent) =
    match (Commands.getCommand intent.Slots.["command"].Value) with
    | Some(slug) ->
        Commands.executeCommand slug
        buildResponse "OK" true
    | None -> buildResponse "Sorry, that command is not available right now" true

And that's it! I no longer have any need to make direct outbound API calls from the functions into my house, so I was able to shut down the NGINX site altogether, as well as the No-IP job and Let's Encrypt...pretty much everything I'd done in part 3 of this series.

There's still some improvements that can be made, but the performance improvement by switching to MQTT has been massive and it makes this skill so much more useful. Here's a little video of me channel surfing on the current version:

Next post in series: Tracking Performance with Application Insights