jplindstrom (594)

SQLite to MySQL

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

TestsIn 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

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

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
Without JavaScript enabled, you might want to
use the classic discussion system instead. If you login, you can remember this preference.