malvasia bianca

I’m now fully populating the database with information from my collection. It took longer than I expected; I’ll keep that in mind the next time I plan to work in a unfamiliar area. (It wouldn’t have been so bad, of course, if I were working in chunks larger than an hour or two at a time.)

The process was reasonably pleasant, and I ended up with a nice abstraction layer hiding the details of the SQL code. Some sort of layer was necessary just for testing purposes, though perhaps I could have found a pre-written testing layer. But writing my own testing layer wasn’t very hard – right now, I’m using a very small subset of JDBC, and doing just a handful of SQL queries – and it actually ended up making the code much more readable. For one thing, it helped me make a clean separation of SQL code from non-SQL code – I only used java.sql classes in three short source files, and the only job of those files is to translate into java.sql classes. (For the curious, they are implementations of abstract interfaces called SqlRow, which is an abstraction of a single row in a table; SqlRows, which is an abstraction of a collection of rows in a table; and SqlSource, which is an abstraction of a connection to a database.) And, for another thing, the abstraction layer gave me a convenient place to translate from my custom types into standard types – otherwise, I’d either have had to come up with a layer whose only job was to do that translation or done the translation by hand in many places.

Maybe the single most annoying thing was handling dates. I was aware that Java has a class java.util.Date which is now frowned upon, in favor of a superior class java.util.Calendar. And I’m even somewhat sympathetic to that idea – it’s good for people to have to deal with internationalization issues, and time zones are certainly part of that. But I don’t care about time zones for this project – all I care about are dates, and I can’t say that I’m too interested in the argument “not everybody uses the Gregorian calendar”. So the upshot was that I had written my own Date class (which was useful for other reasons, e.g. to handle books that I’m in the progress of reading right now).

But when writing out to SQL, I wanted to do things the appropriate JDBC way, which meant using its class java.sql.Date when writing out SQL dates. I was hoping that I could easily convert from my own Date class to the JDBC Date class – after all, an SQL date is just a date, it doesn’t have a timestamp, so there’s no need for, say, abstractions to handle time zones. And the JDBC Date class does have a constructor taking a year, month, and day, which I could have used, but it’s marked as deprecated. Instead, for better or for worse, you’re supposed to use the constructor whose argument is the milliseconds that the date started after January 1, 1970. (The argument can be negative, for dates before 1970, though the docs weren’t crystal clear on that matter; they also mutter something about normalization, which I can’t say I understand, either.)

After a while, I finally came up with the correct code: the right thing to do turns out to be

new java.sql.Date(new java.util.GregorianCalendar(date.year(), date.month() - 1, date.day()).getTimeInMillis())

Which is wrong on several levels. For one thing, when converting from one abstraction of a date to another abstraction of a date, I shouldn’t have to talk about milliseconds. For another thing, “getTimeInMillis” is a lousy method name. And for a third thing, why are months numbered from 0 to 11 while days in a month are numbered from 1 to 31? Sigh. I am glad that I’m starting to understand how to use Java dates “correctly”, but I’m not impressed with this aspect of JDBC’s design.

Anyways, I’m pretty sure I have the database populated correctly now. (And doing so turned up a couple of mistakes in my database schema, too.) The next thing is to write code to read from the database. I’ll have a bit of free time in the middle of the week, so I’ll be able to get it started soon; at first I was optimistic that I’d actually be able to finish it in the middle of the week, but now I’m thinking it will be a bit trickier than that. If it were just a matter of writing JDBC code to read from a database, then I don’t think that would take too long – it would take a little while, because I’ve never done that before, but I think I understand all the ideas, and have a pretty good idea of what most of the new necessary abstractions are.

(To be sure, I’ll probably spend a fair amount of time playing God of War instead of programming. Good game, though not quite as superb as some people make it out to be; not something to play while Miranda is around, though, while I can program when Miranda’s around, so I’ll probably spend a fair amount of time on that. And I have some blog reading and writing to do; in particular, I want to talk about the stunning Killer 7.)

But, setting aside other matters competing for my time, the next step will also force me to approach certain notions from a quite different angle than I’m doing now. For example, right now, when I create a book, I add that book to an internal table attached to that author, so that, when I ask the author to generate its HTML page, it can list all the books by that author. But, in the new world, the right way to generate that list is to look it up in the database; so, instead of keeping a collection of Books in each Author, I’ll either want each Author to contain a pointer to some sort of database abstraction, or I’ll want to pass a database abstraction to the method on Author that generates the HTML. Right now, I’m leaning towards the latter, but it’s not entirely obvious to me what the pros and cons are; I can easily imagine proceeding a fair way down one path, realizing that it’s a mistake, and backtracking and heading down a different path. (Hey, I just thought of a way to get the former to work well – each entity (Author, Book, whatever) already has a ‘Key’ associated to it; I could stick the database backpointer in that Key. That would only require minimal alterations of my current code, and it’s not immediately obvious to me that doing so would be inelegant.)

I am looking forward to the day when I can get back to adding new features. I really want to add a list of links to each entity, so I can have pointers from dbcdb to the blog, not just pointers in the other direction. And I just finished a book with three authors, which I currently can’t represent. And I’m thinking that it’s time to add more media types – CDs, certainly, and eventually DVDs. And at some point I want to switch to Ruby: I already suspect that working in a typed language will make my next step harder than it could be.