Stevie the cat

We've got a little party coming up where the music is provided through a Spotify playlist. The list is maintained by 5 people and currently contains 825 songs, which is well over 60 hours of music. That makes it kinda hard to answer the expected questions at the party like "He! I know this song, who are they?"

In order to give a swift answer to those questions I thought it would be nice to provide the guests with an easy interface: an up to date oled screen that holds the artist and title of the current song.

Currently playing Spotify song

First hurdle is to get the current Spotify song. Even though Spotify said well over a year ago that they would put it in their API, it is still not there. Luckily there is a workaround. If you connect your Spotify account to last.fm, the current song is available via their API.

Second thing to consider is the actual hardware. As I had an OLED screen lying around as well as a Particle Photon, I choose these to be the tools of choise. Mind that my OLED screen has 4 connectors (VCC, GND, SDA and SCL), while others around usually have more connections.

Now for the actual work. First thing I needed was the last.fm part. A few minutes after applying for an API key I received it, meanwhile I had found the user.getRecentTracks method on their API site. The first check with curl and jq looked very promising:

read -p 'Last.fm user name: ' user read -p 'Last.fm API key: ' api_key curl \ --silent \ -d method=user.getrecenttracks \ -d user=${user} \ -d api_key=${api_key} \ -d format=json \ -d limit=1 \ http://ws.audioscrobbler.com/2.0/ |\ jq '.recenttracks.track[0].artist."#text", .recenttracks.track[0].name'

This provided me with:

"Monkey3" "Icarus"

My first thought now was to write a .ino file, perform the request to last.fm from there, parse the JSON within the .ino file and publish the text to the OLED.

Webhooks

As I thought a little more about that, I figured I might as well use the webhooks that Particle provides. This saves me the trouble of parsing the JSON data and only leaving very little data to be transfered to the Photon.

The webhook works with mustache as a templating/filtering engine, so I needed to rewrite the jq syntax to mustache. At first this was a bit of trial and error and, after reading the docs a bit better, was not that hard. The mustache template to get the artist and track is {{#recenttracks}}{{#track}}{{#@attr.nowplaying}}{{artist.#text}} - {{name}}{{/@attr.nowplaying}}{{/track}}{{/recenttracks}}.

The condition wether or not the song is now playing made the mustache solution even better than the original jq one.

I've put this in a jsfiddle if you want to play with this.

So the hook responses with something like:

hook-response/lastfm_currenttrack/0, data: traumahelikopter - Down In the City

I've created the webhook itself with the following json file (replacing the username and api_key with the proper values):

{ "eventName": "lastfm_currenttrack", "name": "lastfm_hook", "url": "http://ws.audioscrobbler.com/2.0/", "requestType": "GET", "headers": null, "query": { "method": "user.getrecenttracks", "format": "json", "user": "LASTFMUSER", "api_key": "LASTFMAPIKEY", "limit": 1 }, "responseTemplate": "{{#recenttracks}}{{#track}}{{#@attr.nowplaying}}{{artist.#text}} - {{name}}{{/@attr.nowplaying}}{{/track}}{{/recenttracks}}", "json": null, "auth": null, "mydevices": true }

Saving this to file lastfm_hook.json and running the code: particle webhook create lastfm_hook.json.

Getting the data to the OLED display

This part was kinda straight-forward as well. I quickly found the required library via https://community.particle.io/t/adafruit-ssd1306-library-ported/3505/36, connected the wires (VCC, GND, SDA to D0 and SCL to D1) and wrote the lastfm_playing.ino file. Compiled it all with Particle Dev and it worked in one go.

The webhook is called every ten seconds, which leaves some overlap in songs, but that does not annoy me. I don't want to hammer the API's if it is not needed. Another thing to notice is that the artist and song might be a bit more text than fits on the display (but that does not bother me either).

I've forked the Adafruit_SSD1306 code in steviethecat/Adafruit_SSD1306, adding the example lastfm_* files.

If your database is filled by a webform or other user input, there is a fair change that the character varying fields contain some <script> tags. Scriptkiddies try all kinds of stuf to see if they can break the system. In case you have not that much influence on the input side of the application, you might want to harden the database fields. The most used way to accomplish this is probably to add check constraints to every character varying field in your database. There is in PostgreSQL, a very easy alternative, namely using a domain and updating the columns to use that domain. "A domain is essentially a data type with optional constraints (restrictions on the allowed set of values)."

So first create the domain:

create domain noscriptkiddies as character varying check ( value !~* '^.*]*script.*>.*$' );

The regular expression states that any entry containing a <script> tag should be denied. A simple test shows that this is working:

pg=> select 'hello world'::noscriptkiddies; noscriptkiddies ───────────────── hello world (1 row) pg=> select 'hello world<script>alert("nice");</script>'::noscriptkiddies; ERROR: value for domain noscriptkiddies violates check constraint "noscriptkiddies_check" pg=>

Now update your table(s) to get all of your columns valid:

alter table sometable alter column description type noscriptkiddies;

In case you already had some scriptkiddies filling your description column, the above won't work, with the check constraint violation. This can be easily fixed, by updating the affected records first:

update table sometable set description is regexp_replace(description, '^(.*)]*script.*>(.*)$', '\1 \2') where description ~* '^.*]*script.*>.*$';

You might want to change the regexp to your needs.

Another option is, if your database only is accessed through views (which it should) to alter the views, casting the entries. The only side-effect is that the whole view will fail:

pg=> select description::noscriptkiddies from (values ('aap'), ('noot'), ('mies'), ('<script>alert("hello world")</script>')) as x (description); ERROR: value for domain noscriptkiddies violates check constraint "noscriptkiddies_check"

I really love the fact that PostgreSQL has support for network address types. As I was in need of a way to convert both ipv4 and ipv6 addresses to the in-addr.arpa domain, I decided to write a function for that. It learned me some more of the PostgreSQL string functions and was not that hard to write. Iit does the trick for both IPv4 and IPv6:

=> select in_arpa('192.168.3.2'::inet); in_arpa ────────────────────────── 2.3.168.192.in-addr.arpa (1 row) => select in_arpa('2001:db8::567:89ab'::inet); in_arpa ─────────────────────────────────────────────────────────────────────────── b.a.9.8.7.6.5.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.0.8.b.d.1.0.0.2.ip6.arpa (1 row)

https://gist.github.com/steviethecat/04a8c98cbf44be9bf295

One of the things I hate about at is that it always seems to think my time specification is garbled. This small wrapper ought to fix that annoyance once and for all.

https://gist.github.com/steviethecat/bff07bc5111a6fc2d222

On OS X there are some small apps that make life better. Caffeine used to be one of these small apps. It takes care of my Mac not switching into sleep mode and thus disconnecting my VPN when I get some coffee.

When I tried to install Caffeine again on my fresh El Capitan install, I got a not too helpful message from the AppStore: "Caffeine failed to download. Us the Purchass page to try again." Not really helpful as I was already on the Purchases page. On my other Mac where I upgraded to El Capitan (instead of a fresh install), Caffeine just worked without problems. I overlooked this difference for a while, which is why I contacted Apple support for debugging this. It turned out that Caffeine uses some kernel hooks that are no longer allowed by Apple to be in the app store. Apple could and should have made this clear in a more describing error message.

Now that I found the cause of the problem, the working alternative Coca was quickly found. Only problem that I had with this Coca is that I don't like the default icon that much. Getting the Caffeine icon back is an in-app-purchase. However I did notice that the Caffeine icon was already distributed by the Coca app in the Application Resources folder.

So restoring the icon to the Caffeine icon was also quickly fixed. I opened up the terminal:

At the office we use a Xerox Colorcube printer. Ever since my (forced) switch back to Linux as desktop computer, printing with a PIN code stopped working. Which is kinda annoying. Luckily I stumbled on this blogpost of Arcticdog’s Kennel. Only issue is that the PIN code in that blogpost is hardcoded. I'd rather set my own PIN instead of 1234. One of the comments of the mentioned blogpost refers to this site, which contains the Xerox PIN encryption method. As I only want to encode the PIN, I used the mapping for the PIN in the following perl script:

https://gist.github.com/steviethecat/1b7b92f70cab5056593f

Save the script and use the 4 number PIN as the argument when calling it. E.g.:

$ perl encode-xerox-pin.pl 4321 DXVT

Ever since I got some Philips HUE lights in the house, I wanted to use them not only as lights that had some scenes and alarm clocks configured, but also as a lifehacking tool.

The first thing that came to mind is that I want my hallway light to be red in the morning if there is a traffic jam on the highway to my work. If that is the case, I will use Waze during my drive to work and hopefully drive around the jam instead of wasting my time in it.

So first thing to check is wether or not there is a traffic jam on my way. I drive a part of the A7 in the Netherlands. It seems that Microsoft has a fairly easy to use API for fetching realtime traffic information. So I signed up at https://www.bingmapsportal.com/ for an API key.

Thanks to google maps I figured out the coordinates of the area that I am interested in. This results in the following curl request, where the envrionment variable ${API_KEY} is my API key.

curl \ --request GET \ --header "Content-Type: application/json; charset=UTF-8" \ "http://dev.virtualearth.net/REST/v1/Traffic/Incidents/52.96,5.92,53.21,6.57?key=${API_KEY}"

This gives back a JSON answer that is very well parsable. This time I'll revert to a bash script with some perl juice on top for parsing the JSON answer.

https://gist.github.com/steviethecat/b8b88fae32f6b8884c2d

This gist can be called from cron, where you'll need to set the BING_API_KEY, HUE_BRIDGE and HUE_KEY as well (or you'll just put them in the script). E.g.:

As I am trying to minimize the amount of paper in the house, I've bought a Fujitsu S1300i ScanSnap scanner. This allows me to scan a lot of documents into searchable pdf's at speed. It can scan up to 12 doublesided pages a minute. I'll try to explain the workflow that I use, as scanning is only one step in the whole process. First thing to do is install ScanSnap Manager (which is straightforward). Next step is adding a new profile. I called my profile "Batch scan". An animated gif of the settings should look something like this: ScanSnap Manager stores it's preferences in plist format (~/Library/Preferences/jp.co.pfu.ScanSnap.V10L10.plist). In plist format, the new profile contains:

{ "Application ID" = 214; "BW Brightness Setting" = 1; "Book Divide Type" = 0; "Button Defination" = 1; "CS Custom Pagesize Code" = 0; "CS Page Size Code" = 1; "CS Process Mode" = 1; "Color Mode" = 1; "Continue Scan" = 0; "Continue Scan For S1100" = 1; "Custom Pagesize Code" = 0; "Dark Mode" = 0; DoMarkerOCR = 0; "Document mode" = 0; "File Format" = 1; "File Split" = 0; "File index digits" = 3; "File index start" = 1; "File name head" = untitled; "File naming method" = 1; "First TimesScan Interval" = 4; HighQualityImage = 0; "Katamuki Hosei" = 0; MMR2 = 0; MarkerMode = 1; "Mixed Paper Size Scan" = 0; "Mixed Paper Size Scan With Multifeed" = 2; "Muki Hosei" = 1; "Multi crop" = 1; "Non Viewer" = 0; PDFLagOcr = 10; PDFPageOcr = 1; PDFSearchable = 0; "Page Size Code" = 1; "Paper Type" = 0; "Pre Correction" = 1; QTbl = 3; "Quickmenu Mode" = 1; "Quickmenu Mode index" = 3; "Reduce Bleed Through" = 0; Rename = 0; Resolutions = 3; "SV600 ContinueScan Mode" = 0; "SV600 ContinueScan Setting" = 0; "SV600 StartScan Setting" = 0; "Save Folder" = "/Users/dirk-jan/Documents/scan/2015/05"; "Second TimesScan Interval" = 4; "Setting Name" = "Batch scan"; "Skip Blank Page" = 1; "Split Page Number" = 1; }

Important things to notice is that the "Save Folder" is set to a scan directory within my Documents folder and than a $year/$month directory. This way I get my scanned documents together in a month folder after processing them. So when I can't find the PDF with other software (which I'll come to in a later post), I still can do the manual search within the monthly directory. Most of the time I know about when I scanned a pdf. As the settings file from Scansnap manager won't change the Save Folder automatically, we need to give it a bit of help. Here comes the earlier mentioned Plistbuddy to the rescue, together with a crontab entry. I've written a small gist that can be used for updating the preferences.

https://gist.github.com/steviethecat/fd5bb7447593bda3d977

Just save this gist to ~/bin/scansnap-manager-settings.sh and call this from cron:

1 0 * * 1 ~/bin/scansnap-manager-settings.sh >/dev/null

At 00:01 on the first of the month, the script will be called and update the save folder to reflect the new month. The script has some neat tricks in it. For instance:

NAME=${1:-Batch scan}

This simple line allows the NAME variable to contain the first argument given. If no argument is given, fall back to the default "Batch scan".

CNT=$(/usr/libexec/PlistBuddy -c "print 'Scanning Settings:'" ${PLIST} |\ awk -F' = ' -v name="${NAME}" ' BEGIN { c=0; found=0 }; /Setting Name/ { if ( $2 == name ) { found=1; exit }; c++; }; END { if ( found == 1 ) { print c } else { print "" }; }; ')

On an OS X system a lot of applications keep their preferences in so called .plist-files. These files can be of 3 different types:

Apple binary property list

XML document text

JSON

The binary type is the most common, but in essence are they all the same. I did not encounter the JSON format on my own system, but that does not really matter. What matters is that these files contain valuable information and can be edited with the right tool. Most common way to edit these files is via the preferences menu option of the application. Though some of the preferences might not even be accessible via the preferences menu. Let's first use the non-risky way of taking a look in the plist files with the plutil tool.

Let's take a look at what we can do with plutil and the screensaver preferences file: ~/Library/Preferences/com.apple.screensaver.plist. In the terminal we'll take a look at what information this plist file holds. We use the -p option of plutil:

$ plutil -p com.apple.screensaver.plist { "askForPassword" => 1 "askForPasswordDelay" => 0 }

It stores the information wether or not to ask for a password and the delay (in minutes) before the screensaver ask for a password. With PlistBuddy you can edit the plist file from the command line. PlistBuddy is very likely to reside on your system already, as it is used by a lot of applications during updates. On my system I found 8 PlistBuddy installs under /Library/Receipts. Let's use /Library/Receipts/iTunesX.pkg/Contents/Resources/PlistBuddy, as that one seems the most recent of the 8 (from 12 March 2010, shasum 1a4c57624b100bf8b22a9381c433b858654c3911). First query the file again:

$ /Library/Receipts/iTunesX.pkg/Contents/Resources/PlistBuddy -c "Print" com.apple.screensaver.plist Dict { askForPasswordDelay = 0.000000 askForPassword = 1 }

As seen, the result is the same as before. But with PlistBuddy we can edit the file too:

/Library/Receipts/iTunesX.pkg/Contents/Resources/PlistBuddy -c "Set askForPassword 0" com.apple.screensaver.plist

So if you combine this with your crontab you can set that you want your screensaver to be password protected during daytime and in the evening not.

0 20 * * 1-5 /Library/Receipts/iTunesX.pkg/Contents/Resources/PlistBuddy -c "Set askForPassword 0" com.apple.screensaver.plist 0 7 * * 1-5 /Library/Receipts/iTunesX.pkg/Contents/Resources/PlistBuddy -c "Set askForPassword 1" com.apple.screensaver.plist

As I've mentioned before I am using a Gembird power socket for switching on and off my christmas lights. You may have noticed that I used sudo for actually switching. This should not be needed and in this post I'll show you how to get rid of sudo, without directly loggin in as root. First step is to gain some extra information on the USB device:

$ sudo sispwctrl -i Device: 0 Vendor: fd13 Id: 04b4 Manufacturer: Gembird Electronics Product: Gembird Silver Shield PM Usb-Device: 002 Bus: 003

You might as well use lsusb for this. And the insufficient rights prove is there without using sudo:

$ lsusb -D /dev/bus/usb/003/002 | head -n 5 Couldn't open device, some information will be missing Device: ID 04b4:fd13 Cypress Semiconductor Corp. Device Descriptor: bLength 18 bDescriptorType 1 bcdUSB 1.10

While editing sudo, chmod-ding and usermod-ing will work to fix the permissions, the correct way to fix this on a linux system is using udev for this. We first need a bit more information on the usb device:

$ sudo udevadm info -a -p $(udevadm info -q path -n /dev/bus/usb/003/002) | grep -i gembird ATTR{manufacturer}=="Gembird Electronics" ATTR{product}=="Gembird Silver Shield PM"

Now creating a rule in /etc/udev/rules.d/10-local.rule. The rule works for the manufacturer "Gembird Electronics". Note that the udev rule uses ATTRS (with S) instead of ATTR.

$ sudo cat << __EOT__ >> /etc/udev/rules.d/10-local.rule SUBSYSTEM=="usb", ATTRS{manufacturer}=="Gembird Electronics", SYMLINK="gembird", OWNER="dirk-jan", GROUP="dirk-jan" __EOT__

The rule changes ownership and group for the device and creates a symbolic link /dev/gembird. Of course change the owner and group to your now needs. Now (re)plugin the device and the sudo is no longer needed:

A lot of my posts seem to be about PostgreSQL, which is mainly because I work with it a lot of my professional time lately. This week I needed to convert the mysterious way in which Evault stores the backup schedule. Evault uses SQL server for storing it's schedules, but I've dumped that into a PostgreSQL database. One of the types of entry looks like binary series for on which days the backup run. So a serie of 1110110 states that the backup ought to run on Monday, Tuesday, Wednesday, Friday and Saturday. Let's create a database:

create table schedule ( description character varying(12) unique not null , daysofweek bit varying(7) check(length(daysofweek)=7) );

Note that I am using a bit string type for defining the daysofweek string. This way it directly validates added entries. The check constraint ensures that exactly 7 characters are added. So I feed this table with some sample data:

insert into schedule(description, daysofweek) values ('first', '1110101'), ('second', '0001000'), ('third', '0101010');

No rocket science so far. Now to find out which backups are scheduled for today. I'll combine a few queries:

dbname=> select extract(dow from now()); date_part ----------- 2 (1 row) dbname=> select string_to_array(daysofweek::text,null) from schedule; string_to_array ----------------- {1,1,1,0,1,0,1} {0,0,0,1,0,0,0} {0,1,0,1,0,1,0} (3 rows)

The first query asks for the day of week (dow) from the current moment. The second query splits the bit string by the null value, resulting in a array element for every bit. The bit string obviously needs to be casted to text before the string_to_array function works. Combining these two queries gives:

=> select description, (string_to_array(daysofweek::text,null))[extract(dow from now())] from schedule; description | string_to_array -------------+----------------- first | 1 second | 0 third | 1 (3 rows)

There are still a few things to note here. A PostgreSQL array starts counting it's fields with one (where most other languages I know start counting at zero). I also assumed in the example that the Evault string starts with Monday. What if it would start with Sunday? We can use the modulo for that. That leaves the remainder of a division.

somedb=> select ( ( extract(dow from now()) + 1 )::integer % 7 ) + 1; ?column? ---------- 1 (1 row)

As we have seven days, we need the 7 after the modulo sign (%). The first +1 states that we want to offset the day of the week by one. When we start counting on Monday, the Tuesday is no longer the second, but the third (second plus one) day. Hence the +1 after the dow extraction. The second +1 is there to offset for the fact that a PostgreSQL array starts counting at one instead of zero. So, the total select is:

Using JSON in PostgreSQL is extremely useful and easy. Let's pretend that you get some JSON formatted feedback from some service and add it to your database.

somedb=> create table sometable ( feedback json ); CREATE TABLE somedb=> insert into sometable ( feedback ) values ( '{"message":"hello world" , "answer":42 }');

A query on this table feeds back plain JSON:

somedb=> select * from sometable ; feedback ------------------------------------------ {"message":"hello world" , "answer":42 } (1 row)

But what if I am only interested in the answer part of the JSON code? This can be done directly with a select:

somedb=> select feedback->'answer' as answer from sometable ; answer -------- 42 (1 row)

A few years ago I got a Gembird power socket for my birthday from B10m. I did not use it that much until recently. The cool thing of this power socket is that it is switchable via USB. That offers a lot of possibilities for simple domotica.

In my current setup I have connected the Gembirds USB port to my fit2-pc server that runs linux. After installing sispwctrl on it, I now can now use it for switching on and off the christmas lights via cron:

With pgagent you can easily switch from cron for scheduling (database) jobs. One of the problems you might be facing is to mail the output of one or more selects. The lack of a "mail me the output" switch can be easily overcome by using the underlying OS tools like mutt. Figuring out how to do that might be a bit cumbersome. So I figured I'd make a gist for it. This ought to work with only configuration adjustments (setting the database and mail address).