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.
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
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
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.
lastValue(), there’s no reason not to use the
last_insert_id() method. That fix can go in the
parent module and can go away in the MySQL and PostgreSQL classes. Hooray for
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
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
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.
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
editing interface, which I won’t use for a while.)
I think the
can go into
Everything::DB, but I’ll leave it here for now.
genTableName() stay as they
databaseExists() has to change; there’s only one database per
file. I’ll just cache the name of the file from
and check that here.
list_tables() has to change slightly, per
I’m not sure what to do with
so I’ll just change the former to return the results of
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
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