This 30-day project explores the refactoring of a legacy system. The Everything Engine is an aging software project that powers Perl Monks, Everything 2, and a few other websites. It suffers from poor design and maintainiability. Learn what it’s like to look over the shoulder of an experienced developer as he refactors, redesigns, and updates the code.

Today’s task is adding a SQLite backend.

Day 28: Whoa, Day 28 Already?

The nice thing about a 30-day project is that it is roughly equivalent to a work week. Between dealing with e-mail, meetings, and other interruptions, if I can put in 30 hours straight on my normal work, I feel pretty satisfied.

By that measure, it’s getting close to Friday afternoon in this project. What have I done so far? I’ve fixed one persistent source of bugs and slowdowns by turning nodes into actual Perl classes. I’ve improved the test suite dramatically, not just in numbers of tests run but in design and reusability. I’ve started improving the database use, partly by improving the object model.

Ultimately, the best thing I can do with my remaining time is to get as far as I can to run the test code against an actual database. I don’t want to require MySQL or PostgreSQL configured properly and running just to run the tests, so SQLite seems like the best solution.

Unfortunately, Everything doesn’t currently work on SQLite. Fortunately, there’s SQL::Translator and SQL::Abstract. Can I make them work?

The easiest first step seems to be copying Everything::DB::mysql and editing it into Everything::DB::sqlite. Before I do that, I want to fix the documentation in the module from block comments to working POD. Once upon a time I had a program that does this, but finding it and fixing it up might take longer than editing the handful of block comments manually. It’s more fun though.

The updated version passes perl -wc and all tests still pass. That’s always nice. This is commit #885.

Running the tests brought up another question; why didn’t the t/NodeBase/mysql.t test fail when I moved the module to Everything::DB::mysql? I’m not sure. That worries me, but I can move it to the right place (t/DB/mysql.t) for now and worry about that later.

It’s more important to be able to test against a live database than to fix test code that works against mocks only.

The move breaks no tests — 7675 still run and all pass — so this is checkin #886.

Alright, here’s the new experiment:

  $ cp lib/Everything/DB/mysql.pm lib/Everything/DB/sqlite.pm
  $ sh scripts/add_text_file.sh !$

What needs to change? In databaseConnect(), the connection string is a little bit different. There’s no particular host, and the name of the database I can reuse as the name of the database file.

In lastValue(), there’s no reason not to use the DBI’s last_insert_id() method. That fix can go in the parent module and can go away in the MySQL and PostgreSQL classes. Hooray for less code!

getFieldsHash() has to change. (It ought to change for the MySQL version as well to use the new INFORMATION_SCHEMA. At least, that’s all I know about it so far.) In SQLite, how do you find the columns of a table? One quick and dirty hack is to use a SQL query such as SELECT * FROM $table LIMIT 1 and then use a $dbh property or fetchrow_hashref() to get the names of the fields. That seems reasonable. (If it fails, I’ll find out soon.)

tableExists() is easier to write by checking the sqlite_master table.

I think createNodeTable() is a little simpler too. createGroupTable() only needs a small change. dropFieldFromTable() is more difficult; SQLite does not support this well. To drop a column, you have to copy the table to a new temporary table without the column, drop the table, create the new table without the column, and copy the data back in to the new table. Yick. That’s more work than I want to do now, and I don’t precisely need it at this exact moment anyway. I’ll make a note to fix it when I run this against a real database.

I think addFieldToTable() works as it is, though I’m not sure about the primary key code. SQLite doesn’t let you add a new column as a primary key, so there’s an alter and recreate there. I don’t need this right now either. (These table manipulation methods are for the dbtable editing interface, which I won’t use for a while.)

I think the beginTransaction, commitTransaction(), and rollbackTransaction() code can go into Everything::DB, but I’ll leave it here for now.

genLimitString() and genTableName() stay as they are.

databaseExists() has to change; there’s only one database per file. I’ll just cache the name of the file from databaseConnect() and check that here.

list_tables() has to change slightly, per tableExists()

I’m not sure what to do with now() and timediff(), so I’ll just change the former to return the results of time() and leave the second unmodified. That may or may not work.

With that, I have a compilable SQLite class. Does it work? I have no idea, and I’m out of time for the day. Tomorrow I need to translate the schema and see if I can install ecore to a SQLite database. Good luck!

Hm, running the test suite reveals that the MySQL test is failing… due to bad coding. It’s because the test calls the methods as functions. (I was younger then, and this hadn’t hurt me yet.) Changing just that test to use the Test::MockObject::Extends trick, and being a lot less specific on how it works, fixes things. Now 7673 tests should run and all pass, and I can check in this new code. The lastValue() move is checkin #887 and the SQLite class is checkin #888.