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.
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.
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...
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.