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.
numbers
gav on 2004-07-21T13:00:19
If it's a number, treat it like one:
cmp_ok($column_value, '==', 42, 'is 42')
Re:numbers
jplindstrom on 2004-07-22T04:28:11
Yeah, that's what I should have done from the beginning... :/
SQLite 3
Matts on 2004-07-21T20:41:13
SQLite 3 should help this (a lot!) as it only aquires an exclusive lock to actually write the transaction (i.e. during a COMMIT), everything else is mostly shared locks.
Though having gone through the conversion I suspect you don't want to go back again
:-)
Re:SQLite 3
jplindstrom on 2004-07-22T04:25:20
Oh, that's not a probelem at all. I haven't moved the db config to a config file yet, but everything works with both SQLite and MySQL with the change of three lines of code.
I don't do anything fancy SQL-wise and Class::DBI doesn't either. Actually, there is no explicit SQL code at all in the application. Class::DBI is niiice.
There is the overhead of maintaining two dialects of DDL, but that's not a problem at the moment.
