Blogging for work

Not of real interest to anybody who doesn’t use BMC Database Automation, but I’m doing a little bit of blogging for my day job as well.

Scratching my itch

It’s been a while since I wrote some code to scratch purely my own itch. Most of my time is spent writing code to scratch my employer’s itches, and occasionally I get to write little programs that scratch small itches I get while writing code for my employer — things like extensions to git-p4 that allow me to pull information from a git repository and use it to generate merge commands for Perforce, so I don’t have to figure out which commits/changes I want to merge.

I know; nothing someone else would be interested in.

But I’ve had an itch for a little while that someone else might be interested in. I listen to the NPR Hourly News Summary via my podcast app on my Nexus 6. The web page might list a bunch of back episodes, but the RSS feed only publishes the most recent summary. But I want to listen to SOME older episodes, just not all of them. What I had been doing was having my podcast app keep every episode and then mark the ones I wasn’t interested in as done, but that was tedious, especially considering I only wanted to listen to four or five of the 24 episodes published each day.

So I thought about what I wanted. I wanted a program that would fetch the RSS feed every hour and check to see if the currently published episode was one of the ones I wanted, and, if it was, store it in a database and then spit out a new RSS feed with the last N episodes I’d stored in the database. I realized I didn’t need to actually fetch the episodes themselves, because NPR doesn’t remove the episodes after they disappear from the RSS feed (as evidenced by the web page with multiple episodes). I also realized I didn’t need to generate this new RSS feed dynamically: NPR’s feed only gets updated once an hour, so I only needed to generate my feed once NPR’s feed was updated, and, since I wasn’t generating the feed every time my podcast app asked for it, I could generate the feed on my desktop computer and then copy the XML file up to my web server (since my desktop has way more computing power than my web server).

And, of course, I wanted to use perl, because that’s my favorite programming language.

One of perl’s strengths is that, whatever you want to do, there’s probably a CPAN module that will do the heavy lifting for you. There’s also a Perl Cookbook for commonly used patterns in perl programming.  I found the recipe for Reading and Writing RSS Files, and there was an example for filtering an RSS feed and generating a new one. The example uses LWP::Simple to fetch the RSS feed, XML::RSSLite to parse the feed, and  XML::RSS to generate a new RSS feed. The cookbook even states “It would be easier, of course, to do it all with XML::RSS”. So I did.

Actually, I didn’t rewrite the RSS too much. Rather than building a completely new RSS feed, I used XML::RSS to parse the feed and extract the one item from it. But even though XML::RSS has a method for adding items to the feed, it doesn’t have a method for removing items from the feed. This left me with no choice but to dig through the source code of XML::RSS and figure out what was necessary to clear out the list of items. Once I cleared out the one item out of the feed, I re-loaded the feed with the items I’d stored.

Wait… I had stored items, right?  Oh, crud, I forgot about that part.  Ok, I need to store the last N items. I could use a text file, but that’s difficult to manage.  I could set up a database in PostgreSQL or MySQL, but that’s a lot of overhead for just storing a bit of data. If only there was a self-contained, serverless, zero-configuration, transactional SQL database engine.  Something like… SQLite!

So I set up a simple schema; one table with two columns: one to hold the timestamp of the episode, and one to hold the block data I needed to shove the episode back into the feed. Since it’s SQLite, I didn’t expect the datafile to exist the first time I ran it, so I put in a test to see if the data file existed before I connected to it, and, if it didn’t, create the schema.

The rest is fairly straightforward; I checked the current episode extracted from the feed to see if it was one of the times I wanted. If it wasn’t, I just skipped ahead to generating the new feed. If it was one of the episodes I wanted, I checked to make sure I didn’t already have it in the database.  If I did, I skipped ahead.  If it wasn’t in the database, I added it to the database and then deleted everything in the database older than 7 days.

#!/Users/packy/perl5/perlbrew/perls/perl-5.22.0/bin/perl -w

use DBI;
use Data::Dumper::Concise;
use DateTime::Format::Mail;
use LWP::Simple;
use XML::RSS;
use strict;

use feature 'say';

# list of times we want
my @keywords = qw( 7AM 8AM 12PM 7PM );
my $days_to_keep = 7;

# get the RSS
my $URL = 'http://www.npr.org/rss/podcast.php?id=500005';
my $content = get($URL);

# parse the RSS
my $rss = XML::RSS->new();
$rss->parse($content);


my @items = get_items( $rss->_get_items );

# make new RSS feed
$rss->{num_items} = 0;
$rss->{items} = [];

foreach my $item ( @items ) {
    $rss->add_item(%$item);
}

say $rss->as_string;


sub get_items {
    my $items = shift;

    # build the regex from keywords
    my $re = join "|", @keywords;
    $re = qr/\b(?:$re)\b/i;

    my $mail = DateTime::Format::Mail->new;

    my $dbh = get_dbh();

    my $insert = $dbh->prepare("INSERT INTO shows (pubdate, item) ".
                               "           VALUES (?, ?)");

    my $exists_in_db = $dbh->prepare("SELECT COUNT(*) FROM shows ".
                                     " WHERE pubdate = ?");

    foreach my $item (@$items) {
        my $title = $item->{title};
        $title =~ s{\s+}{ };  $title =~ s{^\s+}{}; $title =~ s{\s+$}{};

        if ($title !~ /$re/) {
            next;
        }

        my $dt = $mail->parse_datetime($item->{pubDate});
        my $epoch = $dt->epoch;

        $exists_in_db->execute($epoch);
        my ($exists) = $exists_in_db->fetchrow;
        if ($exists > 0) {
            next;
        }

        $insert->execute($epoch, Dumper($item));
    }

    my $now = DateTime->now();
    my $too_old = $now->epoch - ($days_to_keep * 24 * 60 * 60);
    $dbh->do("DELETE FROM shows WHERE pubdate < $too_old"); my $query = $dbh->prepare("SELECT * FROM shows ORDER BY pubdate");
    $query->execute();

    my @list;
    while ( my($pubdate, $item) = $query->fetchrow ) {
        push @list, eval $item;
    }

    return @list;
}

sub get_dbh {
    my $file = '/Users/packy/data/filter-npr-news.db';
    my $exists = -f $file;

    my $dbh = DBI->connect(          
        "dbi:SQLite:dbname=$file", 
        "",
        "",
        { RaiseError => 1}
    ) or die $DBI::errstr;

    unless ($exists) {
        # first time - set up database
        $dbh->do("CREATE TABLE shows (pubdate INTEGER PRIMARY KEY, item TEXT)");
    }
    return $dbh;
}

And it worked!  I then created a git repository on my desktop for it, and pushed it up to my GitHub account under its own project, filter-npr-news.

And that kept me satisfied for a whole day.

Next time, I’ll write about what started bothering me and the changes I made to fix that.

Learning Puppetry tech…

caroll-spinneyOne of the problems I have when I’m puppeteering Rudy Monster is that I’m stuck underneath his huge furry body and it’s really difficult to see the monitors that Kay and Jen are using to watch their own performances. So I started thinking about how to give myself a small, portable monitor that I could use while I was under Rudy.

I’d read about Caroll Spinney’s “electronic bra” that he wears inside of Big Bird, but that has a fairly large CRT video monitor on it, and I was sure that modern technology could provide me with something much, much better.

VideoGlasses

The video glasses that wouldn’t work…

At first, I tried using a pair of Video Glasses.  I figured that I could just wear these on my face and I would be able to puppeteer with the best of them. Unfortunately, I hadn’t counted on two things, the first logistical, and the second technical. First, with these glasses on, I couldn’t see anything but what the camera was showing me. No script, no other puppeteers, no nothing. I was trapped inside the glasses until I took them off.

Second, and much more importantly, they only worked for ten minutes at a time, and then they conked out. If I let them rest for a half hour or so, they’d work again, but I couldn’t afford to only get 10-20 minutes of use out of every hour; I needed to be able to use these for long puppeteering sessions. So I scrapped the glasses and went back to puppeteering blind.

But, of course, that didn’t work very well, either. I constantly had to get coaching from my coworkers about Rudy’s eye focus, and I had to feel my way through everything. Not the way I like to puppeteer.

Finally, I decided to build my own rig to approximate what Caroll Spinney has.  Looking at the pictures of Caroll’s getup, I started figuring out how I could get the appropriate parts. Of course, trying to get a CRT video monitor these days is nigh-impossible, but I found links to LCD flat-screen monitors that have the same 16:9 aspect ratio (widescreen) that our camera has. These screens were being sold for retrofitting cars that didn’t have backup cameras with aftermarket cameras.

And then I saw a related link for a wireless transmitter that allowed you to transmit video from your camera at the back of your car to your monitor on your dashboard, without wires. This was what I needed.

So I got the following equipment:

First I tested the monitor and the battery. Both had all the connectors they needed, so I was able to take the Y-splitter lead that came with the battery and plug one end into the monitor’s power cable and the other into the power supply that came with the battery. I plugged in the video RCA out from our camera and… voilá! I was watching what the camera was recording on the tiny monitor. At the very least, my getup would work if it was hardwired.

Next, I wanted to get the wireless transmission working. The pieces of the wireless transmitter were meant to be hardwired into a car, so they had bare wires as power leads. I was prepared for this (remember, I bought the package of connectors), but then I had a sudden thought: this was DC power, and unlike AC power, with DC power, polarity matters. With my misadventures in LED wiring still fresh in my mind, I popped off to Google to make sure I had my polarity correct. Finding the Wiring Color Codes reference in the free electrical engineering textbook “Lessons in Electric Circuits“, I read the following (the last sentence was bolded by me):

US DC power: The US National Electrical Code (for both AC and DC) mandates that the grounded neutral conductor of a power system be white or grey. The protective ground must be bare, green or green-yellow striped. Hot (active) wires may be any other colors except these. However, common practice (per local electrical inspectors) is for the first hot (live or active) wire to be black and the second hot to be red. The recommendations in Table below are by Wiles. [JWi] He makes no recommendation for ungrounded power system colors. Usage of the ungrounded system is discouraged for safety. However, red (+) and black (-) follows the coloring of the grounded systems in the table.

Ok, so these red wires were positive, and the black wires were negative. And… oh, lovely! This wiring diagram for the product even states that! This is what I get for not reading the documentation before diving in…

Documentation? How quaint…

So I hooked up a female DC power connector to the transmitter’s power, making sure the red wire was going into the positive terminal (then screwing it down with a #0 phillips head) and the black wire was going into the negative terminal (screw, screw, screw). Then I plugged the male power plug from the extra power supply I got and saw the little indicator light on the transmitter light up.

So far, so good.

I did the same with a male CD power connector on the receiver’s power, and then plugged it into the the Y-splitter lead that came with the battery.

BatteryCord

I picked the male power connector because the battery pack had a female power port, and the monitor had a female power plug.  This meant that the cable above would let me plug in a male plug and not have to wire up anything special.

And when I turned on the battery, the indicator light on the receiver lit up.  So, holding my breath, I plugged in our camera to the transmitter… and… success!

I was so excited, I put on the harness and took the screen upstairs to my wife.

“What’s that?” she asked, indicating the picture on the diminutive screen.

“That’s the sewing machine. Downstairs.”

I’ll post a picture of this rig in action after we film with it this coming Sunday, but I couldn’t wait to post about assembling this setup.  It felt good.