At Vocalabs, we use a homebrew object-relational bridge, rather than more common ones like Spring Hibernate or ActiveRecord in Ruby on Rails. The reason is simple: when we first needed one in 2001, the others either didn’t exist, or weren’t mature. Object-relational (O/R) bridges, which map database tables to classes in an object oriented language, were pioneered by Enterprise Objects Framework (EOF) in NeXTStep. (NeXT was acquired by Apple, and EOF lives on in WebObjects.) Since I had used EOF in the 1990s, I had a good idea of what I needed, so homebrew was the way to go.
Many of the differences between the Vocalabs DBObject and other O/R bridges are a matter of laziness. It’s not worth my while to write a complete front end to SQL in Java, so my code is full of hand written SQL where auto-generated SQL would be too slow or hard to write. However, there are a few cases where I’m doing good stuff that I haven’t seen elsewhere.
One such case is DBObjectExtension. The idea is to have a generic way to store metadata that can be applied to any DBObject. Some uses might include: tagging, commenting, translation, or versioning. Each of these use cases would have a subclass of DBObjectExtension, and that class would have a separate table for each DBObject class that it extends.
For example, we do surveys, and surveys have questions which in turn have response options. Thus we have the Java classes Question and ResponseOption, each of which is a subclass of DBObject. Each of them has a table in the database. Most of the time that’s all we need. But this week we have a customer who needs to survey Spanish speakers, and we want to do it within an English-language survey, rather than creating a special Spanish survey.
Thus I’m working on DBObjectLocalization, a DBObjectExtension which lets you modify a DBObject with a translation. To enable DBObjectLocalization for ResponseOption, I created this table in the database:
create table response_option_localization (
response_option_localizationID serial primary key,
response_optionID int NOT NULL references response_option(response_optionID) on delete cascade,
language varchar(2),
property varchar(255), /* The Java property that gets overwritten */
translation text,
/* The last four columns are required by DBObject */
/* mainly because when you discover you need them, */
/* it's too late to add them. */
updated_by varchar(30),
updated_at timestamp,
created_by varchar(30),
created_at timestamp);
Now a ResponseOption gets translated when I call DBObjectLocalization.translate(myResponseOption, language) in Java. We’ve got a web-based front end to our database, and adding translation support was fairly simple. But the best part is that to add localization to Question, I’ll just have to create a question_localization table that looks just like response_option_localization. No new code required. And so on for every other DBObject.
Another nice feature is that because DBObjectLocalization is itself a DBObject, I can apply DBObjectExtensions to it. Our surveys are versioned: when a change is made to the text of a Question, a DBOBjectExtension keeps track of the old text so that we know who saw which wording. My next task will be to add versions to translations. In principle, all I need to do is create a response_option_localization_version table, but since I’ve got special rules for localization in the web-based editor, there’s a little more code needed to do this.
I’m not sure how you’d do this sort of stuff with other O/R software. Versioning can be done with a single version table, but for the other uses you lose referential integrity (when you delete a Question, the ResponseOptions and localizations are automatically deleted by the database), and joins become a nightmare.
Object extensions is one of these features that I think belongs in other O/R software. It’s the sort of thing that ought to be done with a database but you can’t naturally do in SQL.