dev

By anders pearson 30 Sep 2003

remember, drachen, the mp3 database frontend that i wrote a while back? in case you don't, its motivation was that i have a huge collection of mp3s and ogg vorbis files and all other mp3 players i've tried just aren't designed to handle a collection that large and their track selection interfaces are virtually unusable. so drachen is a python frontend to a postgresql database of all my mp3s.

i've been working on porting it to PyGTK and Glade (see this article). the old Tk interface that you can see in the old screenshots was getting old and from using it for a while, i had some ideas on ways to improve it. GTK looks much nicer and has a nice multi-column list widget that i wanted to use. not to mention that i wanted a good excuse to do something with Glade and figure out if it is really as slick as it looks.

i basically spent two nights getting up to speed on glade and (mostly) GTK. glade is a cinch and once i figured out that a lot of the python + glade tutorials on the web are out of date and refer to a libglade python library that has been replaced with gtk.glade, it took me no time to get the shell of the interface built and turned into a glade file that python could read. GTK is a lot more complicated and, unfortunately, not quite as well documented as i’d like. in particular, it took me quite a while to figure out how to do some basic things with the ListStore and TreeView. the individual APIs are documented pretty well, but i had to combine information and example code from a bunch of different places to figure out the general usage patterns.

then i spent this evening tying the new frontend to the old backend. luckily i designed it to be as MVC like as is possible with Tkinter before, so the database code was pretty cleanly split from the Tk code.

while playing with it though, i started to get frustrated with how slow it was at searching the database. when you enter a query into the search box, it had to do a massive join (bringing together the tracks, artists, and albums tables) and do three LIKE comparisons on the fields. even with adding every index i could think of, postgres just can’t make that kind of query fast over 20,000 records.

so i decided to go all the way and build a reverse index like a real search engine would have. i won't bother explaining the concept of a reverse index since this perl.com article does a better job than i probably could.

it turned out to be easier to implement than i thought it would. just added a reverse_index table to the database and wrote a 10 line perl script to go through all the database records and index them (it took 10 minutes to run – longer than it took to write). now, searches are nice and fast.

here's a quick screenshot of the new GTK interface. much more modern looking than the old stuff.

Tags: programming python gtk glade postgres