Slash Boxes
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

jplindstrom (594)

  (email not shown publicly)

Journal of jplindstrom (594)

Wednesday July 21, 2004
06:09 AM

SQLite to MySQL

[ #19973 ]

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.


  • 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

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.

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
More | Login | Reply
Loading... please wait.
  • If it's a number, treat it like one:
    cmp_ok($column_value, '==', 42, 'is 42')
  • 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 :-)
    • 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.