Thursday, April 2, 2009

Stage fright

I'm notorious for not getting stage fright. Since I was still in saddle shoes, I could get up in front of any number of people and spout off about pretty much anything. Impromptu, structured, slide-laden, scripted: none of it has ever been a real problem for me.

Then why do I get stage fright when it comes to posting code?

In my line of work, I write a lot of small, helpful scripts to take some of the monotony out of certain parts of my job. There aren't many functions I have left that don't have at least one py scrit associated with it.

At PyCon, I mentioned that I had written a library for Trac that printed out reports. It started out as someone wanting a listing of what had happened to my tickets over the past month. True, one can simply do a query against the database, but there's something quirky about the Trac database:

It's done in sqlite.

Sqlite SUCKS when it comes to date functions.

All dates are in unixtime. There are no functions for dates. You can't subtract a certain number of seconds from a unixtime date in a query. You can't call some cute function that gives you a date range. You have to work out the unixtime on your own and input it.

Sqlite people, I love you for making a lightweight database. I HATE you for this one oversight. Really? You didn't think people would care about time? Developers are cool, but not so cool that we've ascended the space/time continuum and live on a plane where all times are now and therefore irrelevant.

Anyway, after discovering this oversight, and getting tired of using online converters (no, I cannot do unixtime in my head), I decided to write a program that would do it for me. It hit against trac.db and got me all tickets for a month based on milestone. Even printed me a nice report.

After discovering how to do that, I got more requests for Trac reports. Over time, the library grew, function by function.

Anyway, the fellow Pythonista asked if I could share my code with him. Being the gregarious sort I am, I said sure! Upon returning home, I actually looked at the code, and began to panic.

Oh, crap. Now I knew how all those people who hated public speaking felt. Unfun. In order to stem the tide of dread, I started to analyze what had me so concerned.


Most of the need for small scripts grows organically out of a particular need. This leads to some strange code building strategies, like making functions too large, or forgetting to add docstrings. Or making functions -too- specific (I have several that make SQL queries and return the results. Surely this could be streamlined?).

It's the way of cubeland: someone catches on that you have a fast way to do one task, and you have people testing you with every variation of that task they can come up with.

Unique to me?

Is the code I write really useful to anyone else? I mean, I had a very specific need. Who else could possibly need a report that spits out all tickets that have been touched in the past ten days? Are other people really that obsessed with the Trac database? If there are, is there a support group I can join?

Can anyone else even understand this crap?

This fear came over me with my first programming class my sophomore year of high school, and has never really left. Can people understand the crap I write? Most of my work scripts are never meant to see the light of day. They sit quietly in a jumble, waiting for me to pop in and feed them some data, then get out. They're like the red-headed stepchildren of lore. Good for doing chores, but don't take them to the ball.

But it's just 300 lines!

Three hundred lines isn't a lot. I'm not sure how many lines make up the major frameworks that are about today, but it's sure as hell more than 300. Three hundred lines of code really rank their own Google code project?

At the end of the day, I decided to toss those worries aside and post the code. When I do public speaking, my last thought is often "What's the worse that could happen? Someone shoots me on stage? At least I'll end up on TV." What's the worse thing that could happen if I post some code? Someone ends up not using it? At worse, I get told what's wrong with the code, and I get a chance to learn and fix it up.

So the red-head is going to the ball. I encourage everyone else to take out their own step-children and see if they can do some other people some good.


Coderanger said...

Two things, SQLite does actually have some limited date/time functions:

Also, be sure to post your script on Trac-hacks! :-)

Daniel Lindsley said...

You shouldn't worry. The point of open sourcing something is to help others. In return, you'll often get a little help back (bug reports/patches).

I do have a suggestion. You might consider doing string interpolation instead of concatenation on your queries. i.e.

query = "SELECT * FROM foo WHERE field = '%s'" % (my_var,)

Even better would be using the `parameters` argument to `execute`, as that will safely bind your data into the query. See

And in case no one says it sooner, thank you very much.

_Mark_ said...

Yeah, at a quick glance, you probably haven't run into the quoting issue (milestones and status values are unlikely to have ' in them) but someone using the the interface without reading all the code might get caught by it, especially if they feed it untrustable user input.

I personally find that properly bound statements are more readable than language-level string mucking anyway - for example, getMilestoneOverview could say

cur.execute("select id from ticket where milestone = ? and status = ?", [milestone, status[0]])

(not tested, just extrapolated from some of my own sqlite code.)

kcunning said...

@coderanger Do you know when that went in? I swear that wasn't in there two years ago. Awesome find, though!

Also, I'd be happy to get it into trac-hacks! I've certainly used them enough :)

@Daniel When I wrote the code, I still hadn't had to do much with string interpolation. I think I'll make that my first ticket for code clean-up :) And you're welcome!

@Mark - thanks for the tip! I definitely need some documentation to go with it. So far, only I've been the one to use the code, so I'm a test case of one :(

Doug Hellmann said...

I wish there was a way to make reports like this through the trac query interface. We use some custom SQL through the report UI, but that makes it a little harder to parameterize the way you have here. Thanks for posting this, I'm going to subscribe to updates to the project!