use Perl

A couple of days ago I switched the development database backend for a pet project of mine. Originally I used SQLite, but the concurrency didn't quite cut it (it plain sucked basically). So I switched to MySQL which is also low maintenance and low overhead.

This is what I had to change.

DDL

• Drop statements instead of deleting the database file
• Comments: --The Comment -> /* The Comment */
• Command separator / -> ;
• Datatypes: number -> numeric, timestamp -> datetime
• primary key, auto_increment
• engine = InnoDb, to keep transaction support

Tests
In MySQL, values of type NUMERIC returned are decimal all of a sudden, in the typeless SQLite they were just returned as I inserted them. I started to change the tests to match this

is($column_value, 42, "is 42");
#42 became 42.0000 in MySQL
is($column_value + 0, 42, "is 42");

but the real fix is of course to massage the values from the database. Class::DBI provides code for a "normalize" phase, but that only applies when setting values from code, not when obtaining values from the database. So I added a "select" trigger for this purpose. We'll see if that was the correct approach.

Treading water
While fixing the broken tests I also got stuck in a time wasting black hole of "I'm not looking at what I think I'm looking at". Such a classic.

I had changed the Class::DBI connect string, but not the Test::DatabaseRow connect string which still pointed to the SQLite database... :/ I think I spent 90 minutes staring at values that didn't match up, tracing back and forth in various modules. The reason I didn't get this right away was that I didn't see any errors or missing values, but the wrong values. Why? Because since the old database had been used to run the same tests, the same PKs were there to provide rows for the queries, only the row values were of course not for the same tests.

Ovid wrote about programmers Getting Things Done a while back. I think not spending too much time chasing ghosts and running in the wrong direction is a big part of that. Simply reflecting upon what you do enough to realize when you're not even asking the right questions.