Two sqlite3 gotchas

Ran into a couple of odd problems when doing database schema migrations on my sqlite3 database.

Transactions don’t work like you’d expect in the sqlite3 command line tool! I wrote a few lines of SQL to create a new table and drop an old one, then fed that script to sqlite with the command line tool like this: sqlite3 my.db < script.sql. I was clever and wrapped my script.sql in a “begin transaction… commit” so that if there were any errors, nothing would be committed. Smart, right? Wrong. sqlite3 will gladly execute line n+1 in a script it’s fed even if line n fails. This makes sense for interactive use, but is a disaster for a script that wants a transaction; the commit at the end will always run. The solution is to put .bail on in the top of your script so any errors cause execution to terminate. See also this discussion.

I was using an “insert or replace” command to be sure I only had one row per key in my database, along with a unique constraint to tell the DB what the key was. Only I didn’t actually add the unique constraint during my migration, because it’s a PITA to do schema changes like that in sqlite3. unique constraints are only hints, right, it’s like a type checker! Well not if you rely on the constraint violation enabling an “insert or replace” to work. Oops.

 

Progress

Still working away on my LoL / Slack bot. Today’s project was rejiggering all the code to use a new synthetic “personId” as the primary identifier. Previously I was using “summonerId”, but that’s only unique to a single region (like North America) and now I need to make the thing work for multiple regions.

Scary code change because it touches all the files and a lot of assumptions. But no changed types; it’s swapping one int for another. My simple test suite paid off in spades here, giving me the confidence my changes worked. So far so good!

I even had to do a database migration, adding a column to a couple of tables. That I just hacked up by hand but it seems to have done the job. I need to do another now to drop the superfluous column. Unfortunately sqlite doesn’t really support dropping columns, will have to do some more complicated stuff involving creating a new table and renaming it.

 

Testing discipline

One of my successes in my professional career has been advocating for unit testing. I helped lead the charge for automated unit test suites inside Google, particularly for our Java code, and that ended up being hugely useful.

I’m nowhere near as disciplined when I’m working alone on my little hacks. It’s still just too much effort to write tests! It feels like twice the work. For a long lived software project with many users, sure, it’s worth the investment. But a one-off hack you’re not going to ever pick up again? Is it even worth the bother of remembering Python’s baroque unittest syntax to set up a test framework? (I have to look that up every… single… time…)

But small hacks have a way of becoming bigger things, vital projects. I’m continuing to work on my Slack / League of Legends integration. I may even get a bunch of new users, I found a community of a few hundred people who could use it! Yay! But I’ve gotten to a point of paralysis where I was afraid to make changes because I couldn’t tell if I would break things other than pushing it to the live system and then see if cron mailed me any errors. Derp.

Unfortunately this project is particularly thorny to test. I’m integrating with two different online APIs (Riot and Slack). I’m also storing data in a database, and I’ve still never seen a good solution for mocking out a stateful database. For now I’m just going with ugly hacks. I’ve put wrappers around the Riot API calls that will load canned data from a pickle, a sort of persistent memoization. And I’m just accepting the test script needs to create a fresh test database and populate it from scratch. That’s not the ideal of unit tests, but it is still some form of useful automated testing.

I already feel relief and confidence from having even something very basic in place. Now I can type “./test.sh” before deploying and at least know if the scripts are going to blow up on some really stupid error. And I have a place now to add more sophisticated tests for correctness.

Update boy what a difference that simple test suite has made. I’ve felt free to make way more aggressive changes to my code now. Without fear.

 

Slack API: channels, groups, IMs

I’m writing a fancy Slack bot to post messages in a Slack for 500+ users. Fun!

There are at least three different kinds of things a Slack bot can post to:

  • Channels. These are the public channels like #general. Find them with channels.list. Bots can’t call channels.join; I think they’re de facto members of all public channels.
  • Groups. These are private channels. The bot has to be invited via /invite, then it can see the group IDs with channels.list.
  • IMs. These are direct messages. You can see all of a bot’s IMs with im.list. I think someone has to message a bot first before the IM channel is created, not sure how a bot can initiate an IM (if at all).

I mostly like the Slack API but it’s a bit warty for things like this. My impression is it’s grown organically and they’ve never gone through and deprecated old stuff. It’s nice that old code keeps working, but it’s a bit confusing if you’re starting now. I’m also really confused about the various kinds of integrations, bots, applications, etc.

sqlite upserts, inserts, etc

sqlite has a nifty feature; “on conflict”, aka “insert or”. You apply it to insert and update operators to tell sqlite what to do if the operation would violate a constraint like unique or not null.

I’m using “insert or ignore” to only insert a row if it doesn’t exist.

insert or ignore into mytable(userid) value (23)

You can also use “insert or replace” to do an upsert

insert or replace into mytable(userid, name) value (23, "Joe")

 

Slack API thoughts

I’m working on a Slack integration that marries the Riot games API to the Slack API, for a group of League of Legends gamers I play with. I’ve been doing simple message posting from a cron job for 6 months now and it works great. Now doing fancier stuff.

I’m not a fan of the message formatting options. There’s the basic markup which they call “markdown” even though it’s not markdown. Then you can add attachments which has a bit of a tabular layout to them but it’s not very flexible. I can’t make it look like what I want, so I’m reduced to doing fixed-width formatting of plain text. Ugly. I wish they’d let me do free form HTML but I guess they’re aiming for display consistency so don’t want that.

I am impressed with how easy it is to add basic slash commands though. You can just create a “Configuration” that causes /foo to fire off a POST to the URL of your choice. Done! There’s fancier stuff for installable commands, too.

Last comment; the slackclient.py that looks official is not awesome. There’s no proper release schedule so what’s on PyPI is out of date. And it seems to rely on an odd websockets implementation. I may want to look into alternative Python wrappers if I do anything too complicated. I’ve only been using the REST Web API so far, not any streams.

 

Slack API code to look up IM channel

I want to write a Slack bot that sends IM Direct Messages. It’s a bit indirect in Slack. You have to first call im.list to get a list of all the “DM channels” the bot can write to. Then you have to look up the user info for each channel to figure out which person it actually is. Here’s some throwaway code that does that.

Bonus wrapper for the official Python slackclient library to decode the response and throw appropriate exceptions. That library has gotten little love from Slack, it could use some help.

from pprint import pprint
from slackclient import SlackClient
import json

_slack = SlackClient("SECRET")
def slack(*args, **kwargs):
    "Wrapper for Slack API. Manages stub, also decodes responses and raises exceptions"
    rb = _slack.api_call(*args, **kwargs)
    r = json.loads(rb.decode("utf-8"))
    if r.get("ok", False) != True:
        raise Exception("Error making Slack call", r)
    else:
        return r

imList = slack("im.list")
for user in imList['ims']:
    pprint(user)
    info = slack("users.info", user=user['user'])
    pprint(info)