I have a set of python scripts to help me manage a few SQL Servers at work, and one of the things I do is take database backups using BACKUP DATABASE and BACKUP LOG. I’ve been using pymssql to connect, but today tried switching to pyodbc. pymssql seems to be having momentum problems, so I figured I’d try the alternative and see how well it works.

BACKUP and RESTORE operations on SQL Server don’t run like normal queries

The first was trivial to fix, the second took some digging. If you try to run a BACKUP via pyodbc, the cursor.execute() call starts and finishes with no error, but the backup doesn’t get made. With help from CubicWeb‘s post MS SQL Server Backuping gotcha, I learned that BACKUP and RESTOREs over ODBC trigger some kind of asynchronous / multiple result set mode. To get around this, you can poll for file size (as Cubicweb did), but that gets ugly when making a backup on a remote server.

In a backup, I think each “X percent processed.” message is considered a different result set to ODBC. Instead of polling the file size, you can call cursor.nextset in a loop to get all the “percent processed” sets:

After adding that while loop, backups of small and medium sized databases worked like a charm.

A new decade means time for a fresh windows install at work. I ran into some trouble with windows 7, visual studio 2008, and SQL 2008 Express. Here’s how I resolved them. Contrary to most things I found on the web, I’m not using betas or release candidates.

First off, installing SQL 2008 Express. I only wanted the management tools, and this was a little hard to come by. I downloaded various EXE files from MSDN, but none of them worked (they would error out, bring up an seemingly unrelated installer, or any other confusing behavior that may have led you here). Here’s what worked for me:

Next up, Visual Studio 2008 (VS2008). My company has an MSDN subscription, so we downloaded an ISO (named en_visual_studio_2008_professional_x86_x64wow_dvd_X14-26326.iso) and I used freeware MagicISO to mount it, then ran “setup.exe”. The install failed on the “Microsoft Visual Studio Web Authoring Component” (MVSWAC). Here’s what worked for me:

Digging into the ISO using 7zip, the problem is /WCU/WebDesignerCore/WebDesignerCore.EXE is corrupt. To get VS2008 to install cleanly, first we need to install MVSWAC, at which point the VS2008 installer will happily skip past the corrupt file. I ran across several blog/forum posts with horror stories about VS2008 installing SQL2005, and needing to uninstall half the planet to get things working right.

As always, be sure to hit up windows update, and change your update settings so you get fixes for VS2008 and SQL2008.

I’ve been doing some maintenance programming for a few days solid (rare for me to get to program that much), and I again find myself amazed that any software works at all. I’ve only been programming seriously for about a decade (mostly web apps), but it feels like I’m building rickety crap on top of other people’s horrible hacks.

The bar for quality software seems so abysmally low. When coding around some bizarre behavior I’m seeing out of the .NET framework, I know I’m introducing weird brittle bits. It feels wrong, but I don’t see any other option. And this is new code, written for the latest released version of a very popular system! It seems like everyone else is doing the same thing in every programming environment I’ve seen.

My best guess is I’m working at maybe the 1000th layer of abstraction over the bare metal, and that sounds low. That’s a lot of cruft, hacks, bugs, security holes, late-night fixes, bad compromises and coffee.

Maybe my sense of “clean code” is just OCD? Sometimes I wonder if writing good code is just a waste of time. Is shoddy copy/paste winning the evolutionary battle for the software base that will drive humanity for the next millennium?

From javascript I pass to the server the lat/lng region currently shown on the google map, and what size heat map to generate, in pixels.

lisp pulls weights from my database within the given lat/lng region

lisp iterates over the db results, mapping lat/lng to x/y coordinates for the final heat map image

lisp uses the list of mapped (x y weight) to draw the heat map in png

javascript throws the png on top of the google map

I tried a few things based upon the comments I got back from the helpful lisp community.

used zpng to get direct pixel access, and calculated each pixel’s color using a weighted average of nearby points using distance. This didn’t produce good images, and was pretty slow.

used zpng to get direct pixel access, and calculated each pixel’s color using the gravity formula against nearby points. This didn’t produce good images, and was very slow.

I did some more research and learned about the Generic Mapping Tools and bicubic interpolation. The GMT is a set of C programs, similar to the Imagemagick suite. GMT showed one way to draw heat maps in the Image Presentations tutorial. It spoke of gridded data sets, and that gave me one more vecto-based idea: split the desired heat-map into a grid and color each square in the grid based upon an average of the weights mapped in that square. This is a neat effect, but not what I was going for:

This is reasonably fast, taking about 1 second on my dev server. To quickly find what weights belong in which grid square, I make a spatial index of all the weights, using an r-tree from the spatial-trees library.

The next method I tried was to use interpolation to get a smooth look. I found Cyrus Harmon‘s ch-image library supports image interpolation, and got to it. As Patrick Stein noted elsewhere, ch-image isn’t easy to install. It’s not asdf-installable, and the project page doesn’t list all its dependencies. For future reference, here’s what I think I needed to install it:

The first step is very similar to the code I wrote to make the grid version above. Instead of drawing a rectangle, I draw a pixel using ch-image’s pixel access functions. This was a little weird because ch-image’s coordinate system has 0,0 at the top left of the image. I’m still not sure how to best choose the size of this smaller image, but ultimately it should depend on my data. For now I just have it hard-coded be 20x smaller than the desired size:

Yep, that’s pretty small. Applying a transform to scale it up to the desired size using bilinear interpolation yields:

It looks pretty good and takes about a half-second to draw. If you click into the larger version, you can see some discontinuities in there, which is a well-known result of bilinear interpolation. However, based upon other graphics I’ve seen, what I really want is bicubic interpolation. Luckily, ch-image has this built in:

Oops, maybe not so luckily. I can certainly see the kinds of look I’m wanting in all the garbled stuff, but ch-image is freaking out somewhere there.

Bilinear it is! Here’s a screenshot of the overlay in place on the map:

It’s pretty fast, and looks pretty nice, and is fairly close to the look I wanted. I probably still have some off-by-one errors somewhere, and need to check the git repos for the ch-* libs to see if there might be newer versions than the tarballs I installed. I still count this as great progress for 5 hours of coding and research. Huzzah for the much-maligned lisp libraries!

When using google to find things in the excellent Postgresql documentation, I often end up on pages showing old postgres versions. For example, googling for “postgresql create index”, the first hit is for the postgresql 8.2 docs, and I’m running 8.4 now. My co-workers made a greasemonkey script to automatically redirect to the current version, and I adapted that into a bookmarklet.

I really like how git tells me how many lines inserted/removed when I commit, and wanted to get something similar from Subversion. I’m working on a refactoring of an older system, and I wanted to know how my refactorings were effecting the code. I think I’m going to remove a lot more code than I add, but why wonder when svn has all this info?

Using my horrible bash skills and this post on SVN Line Output Totals, I came up with an inefficient bash program to do what I want:

I stole some time from my increasing non-technical workload to play with generating heat-maps of residential energy consumption in my http://gainesville-green.com project. The initial results are promising:

There are a few neat things going on here. I’ve got a url handler in my lisp that looks to the query string for lat-lng bounds, image size, and some other variables to generate a PNG file. I pass that URL to a Google Maps APIGGroundOverlay to put the image onto the map. Add some javascript event glue and I can do cool things like automatically regenerate the heat map overlay when you zoom/pan the map around, and display an animated heat map showing consumption over the course of the year. There’s still a lot of UI interaction to sort out, but I think it’s a nice approach.

The heat map itself is generated using Vecto, and I think I’m doing it wrong. I jump through some hoops to map lat-lng to image pixel coordinates, pull from the database, and end up with a list of (x y weight) tuples, with the weight being a number between 0.0 and 1.0 representing the relative consumption of the home that should be at pixel x,y in the result image. Then I start painting, which is where I think I should be doing more math. For each point, I pick a color between green and red based on the weight, using the handy cl-colors library to interpolate:

I actually have to go from green->yellow, then yellow->red, with some goofy adjustments to the percent to make the interpolation work out. Once I have that, then I have my color, and my pixel, so I can start drawing. To get a smoother look, for each point I draw concentric circles with different radius and opacity, so each individual data point is rendered like this:

This is enlarged to show some of the blockiness, it ends up looking pretty nice when they are small. Here’s the actual function:

Max-radius determines how large the largest circle is, and is calculated based on how many points I’m drawing.

There are a few drawbacks to this approach. First, it’s slow. Drawing operations aren’t exactly cheap, especially when messing with alpha channels. It takes me around 5s for 578 data points, which is fine for offline tasks, but on a web-app it needs to be super zippy or you fickle internet folk will close the tab. I also want it to be easy to show animations, so generating a bunch of them quickly would be nice. The time spent increases fairly linearly with data points, and I’d like to be able to render heat maps for large areas with tens of thousands of data points. Profiling shows practically all of my time and bytes consed are spent in the draw-point function. UPDATE: after more profiling, vecto:fill-path is most of my time, which makes sense.

Second, I have to be really careful to draw these points from lowest weight to highest weight, because I want red dots to be painted on top of green dots. It seems like I should decide what color each pixel should be, then draw it once, rather then accumulating the right color in the image canvas. Right now there’s also some bug with drawing lots of data points, I just get a big green image, when I would expect some reds or yellows.

Another issue is for apartments I have coordinates for the apartment complex, but not each individual unit. This makes some funny results, like the big orange blob on the right side of the screenshot above where I’ve painted a few dozen points on top of each other.

I did some googling on heat-map algorithms, and found some actionscript and java code, but the actionscript was using a similar approach and the java was incomprehensible. I think I’ll try making a big array for the result image, and calculating an average weight for each pixel, then loop through that and draw once. I’m also going to try calculating the weights using magnetic field strength or gravity math. I think that approach will end up faster, look nicer, and should be a fun problem.

As a Common Lisp programmer, Clojure irritates me for various irrational reasons. As an exercise in breaking those down, I ported Lau’s 67 line program (which had no comments) to CL running on SBCL using asdf-installable libraries. I used lispbuilders-sdl for display and pcall for concurrency. I ended up with 115 lines, including comments and some significant differences in the program.

I went through a few revisions, initially trying to transliterate the code, looking at the fine clojure API docs to figure out what different things did. Then I gave up on that wrote more idiomatic (at least for me) lisp, but still resisted the urge to use iterate of alexandria. I wanted to have code that was as close to the bare language as possible, so I could make an apples-to-apples comparison. Now that the exercise is done, I think that goal was unattainable. It’s close, but the differences in the languages are significant, so it’s not an great comparison.

After the first round, I started diverging more from the Lau’s version, looking for higher FPS and nicer lisp. I ended up with a few major differences:

I used a 2D array to represent the world, the Lau used a single long vector and I didn’t quite understand how it was determining adjacency

I had a lot more functions to abstract out that data structure choice (ie: instead of calling aref everywhere, I made a get-cell function)

Lau called pmap function to calculate each cell’s next value in parallel, and I used pcall to calculate the next whole world state while the main thread rendered.

Lau drew boxes for each rendering loop, I made two SDL surfaces up front and blitted them in at the right spots

I spent a little under 4 hours playing with it, and a lot of that was reading documentation. I don’t think any conclusions can be made from this for a “common lisp vs clojure” flame war, these are both fairly throw-away pieces of code. I have no doubt that any experiences lisper or clojurer would find a lot of obvious improvements.

Some of my observations along the way:

getting the lisp libraries to work (which I’ve done in the past) is probably harder than getting clojure working and using java libs.

java libs look like a pain in the ass. This softens the “and you can use java libs!” selling point of clojure for me. They’re still java libs.

The places where clojure calls java are kinda ugly, it’s a square peg in a round hole.

clojure has a ton of lazy-evaluation semantics built into the language. In this case, that seemed to be a bad thing, and most of Lau’s code was calling some wrapper function to say “no really, I want you to actually do this”.

Clojure has more syntax than I thought, using # % [ ] _ to mean different things (maybe in different contexts?).

I’m not sure how the STM features I’ve heard a lot about come into play here, if at all.

I should be asdf loading my libs in a nicer way, right now you need to evaluate those first lines, and then compile the file. I didn’t have the motivation to create an .asd file or finally learn how to use eval-when properly.

I like long, descriptive function names. Some of the ones from clojure irriated me: doall, doto. It reminds me of arc a little.

I was confused by the per-cell parallelism in the clojure version (I think clojure uses native threads in a threadpool). Pcall does the same thing, but I figured I’d be spending more time context switching than calculating, and it was getting late.

I got an arduino microcontroller a little while ago, and have played with it a little but found it’s C/C++ development environment annoying. I wanted to control it from lisp, and that meant serial IO. Many other languages have special serial libraries you can use, where you instatiate a Serial object with configuration like baud, parity, etc. John Wiseman wrote arduino_serial.py that shows this pattern.

use sb-ext:run-program to call out to python/C/whatever to deal with the serial port (we do something similar at work to render trac wiki markup to HTML in lisp)

write a small C program and FFI to that (was tempting for the experience)

After much trial and error and some advice from the helpful folks on #lisp, I got method #1 working tonight. I was able to read from arduino pretty easily, but I needed to issue this magic stty command before I could write:

I had been curious how lisp (or my underlying linux) would know what baud, parity, etc to use, and it makes perfect sense that I need to set these first. After that, the lisp side ends up pretty simple. It took a little tweaking to find the right :direction, :if-exists, and :external-format arguments.

My dream goal is to have lisp controlling motors that are spinning mirrors to reflect a laser in very particular patterns. I’d use this on halloween decorations for starters, combining with fog machine/dry ice to create nifty patterns and make people wonder how the hell I did it. Maybe, if I have the willpower to see that through, then I’ll also hook up a USB camera (using cl-v4l2) and get lisp to track and hightlight objects, augmented-reality style. That’d be great for table-top games, being able to overlay terrain or effects on a grid mat.