Automating ingest of StillStream signups

I help manage the StillStream internet radio site. We’re in the midst of a conversion of the site to WordPress to make it a lot more usable for both our listeners and our DJs, but at the moment we’re still running off our old custom PHP site coded by Palancar back when he first created the station.

One of the things this means is that we get all new signups by artists as emails delivered to me, and I have to manually enter the signup details into the site via PHPmyadmin. Our new system will automatically store the data in the database and let us approve the artists to be shown publicly, but for right now it’s a lot of cutting and pasting. Tedious and potentially easy to make a mistake, so I decided to see if I could automate it at least a little.

First, I’ve already got Gmail tagging the signups with their own label. gmvault, a nice Python program that can connect to Gmail and back up one’s emails, has recently been enhanced to allow you to pull down emails with a specific label. I log in to Gmail, check what the proper “in:” string is to select those emails, and then run gmvault to download just those:

gmvault sync –type custom –gmail-req “in:stillstream-inbox-artists” my.email@gmail.com

Joes-MacBook-Pro:~ joemcmahon$ cd gmvault-db/db/

Joes-MacBook-Pro:db joemcmahon$ ls

2014-05 2014-06 2014-07 2014-08 2014-09 2014-10 chats

(The ‘chats’ directory only contains Google Chat messages, so I’ll just ignore it.) Now I’ve got the emails, and I need to process them. Each email is essentially a dump of data from the signup forms. Here’s a sample email body – I’ve dropped the headers because they’re all the same and don’t contain any data I use for the process, and the field contents are redacted so my example artist doesn’t get spammed.

An artist has digitally signed the StillStream agreement.

Full Name: [John Doe]

Artist Name: [Does in the Woods]

URL: [http:/doesinthewoods.com]

Addr1: [123 Someplace Street]

Addr2: []

City: [Bay City]

State: [AK]

Zip: [99999]

Country: [US]

Email: [john.doe@doesinthewoods.com]

Phone: [715-555-1212]

Agreement Version: [3]

REMEMBER – artist is not in the database automatically any more – you must add them BY HAND.

What I need to do now is transform this into a nice MySQL UPDATE or INSERT statement I can execute on the StillStream server to add the artist to our database. A quick little Perl program seems the right idea.