I finished reading an introductory SQL book. (Which seemed decent enough.) So now I don’t really have many excuses to avoid starting the transition of dbcdb to an SQL back end.
Which I don’t really feel like doing, even though it’s clearly the right thing to do next. This means one thing: I should split my stories.
So: what are the steps? I have existing data stored in a custom representation, and I don’t want to throw away that data. So I need a tool to dump the data to SQL. It doesn’t necessarily have to be the most robust tool in the world, but it’s hard to do without.
I could imagine writing a tool to go the other way as well, but I don’t really see the point of that. So let’s skip that step.
After I have the data in SQL, I should transition my software to generate the web pages straight from the database.
Once that’s in place, I won’t have any more need for my custom Java representation. So I’ll need to write tools to update the database directly, instead of updating my custom representation and dumping it to Java.
That’s the high-level plan; let’s drill down a little. The first part is to dump the data into an SQL database. So I guess I need to come up with a database schema. And I need to learn about ways to interact with SQL from Java; JDBC seems to be the tool to use. I should think about how to test this; I guess I can do acceptance tests by sending SQL queries from a shell script, but I’ll need unit tests, too. (I could skip it, given the throw-away nature of the code, but I don’t think skipping it will save me any time.) Michael Feathers says your unit tests shouldn’t interact with the actual database; I could either look into an in-memory database, or I could ignore the advice. I think I’ll do the latter until it starts biting me: right now, build times are much longer than test times, and I doubt the database-level unit tests will take that long to execute. I think writing the data out is conceptually straightforward, though I can’t quite envision what the abstractions will be in my code.
Next: generate the web pages straight from the database. For books, this will be completely straightforward. For authors, it will be almost as straightforward; I’ll have to redo the way I generate my list of books by an author, but that’s no big deal. And once I have all the individual objects under by belt, hopefully the indices will be straightforward enough. So there should be an easy task breakdown there, with a natural order of the tasks.
Updating the data is an interesting issue. I could actually just go and edit the database directly, using some tool like phpMyAdmin. Or I could write a Java CLI tool. I don’t think I want to write a custom web tool for this just yet: that will come much later in the project. Honestly, it will be a little hard to write a CLI tool that’s as easy to use as my current format (there might be a lesson to be learned in that), but I guess the CLI tool will be easier to use than mucking with the database more or less by hand. Shouldn’t be too hard to do; I’ll plan it in detail once I come to that.
Sounds like a plan; hopefully there’s enough detail here to get me to stop procrastinating. Clearly I should start learning about JDBC, at any rate.
Post Revisions:
There are no revisions for this post.
Posts
For just simple dumping, it’s probably easiest to dump your data into a text file containing SQL insert statements of the INSERT INTO table VALUES(“this”, “that”, “The other”, …) type. That saves a lot of writing code, since all SQL database engines can understand files of SQL statements.
12/18/2005 @ 6:05 pm
Hmm. Is JDBC complicated enough that using it is significantly harder than generating a flat text file as you suggest? I would have thought that the conceptual overhead of going straight to JDBC would be pretty small. (Not that I’ve looked at JDBC at all.)
Of course, even if it’s small, it’s probably nonzero. So one question is: given that I eventually want to be able to write out the data to the database and read the data from the database, where in this transition does it make sense for me to start using JDBC? Should I use JDBC right when I start using SQL, or should I postpone its use until later? (Where doing the latter might smooth out the learning curve some.) Not clear.
And we can also look at the problem in light of the fact that I’ll eventually need to update the database programatically. But that’s probably too much thinking ahead to be useful right now.
I’ll think about it more once I’ve looked into JDBC.
12/19/2005 @ 9:16 am
[…] Despite my earlier planning, I still haven’t done any work towards switching dbcdb to an SQL backend. Part of this is other things intervening in my life, but most of this is the inertia of starting something new, and that I haven’t broken tasks down enough. […]
1/16/2006 @ 11:30 am