Eclipse Data Tools Platform

A friend of mine, having some troubles with the Data Tools Platform, asked me if I knew much about it. I didn't -- even though I've often had Quantum installed, I usually fall back on standalone tools, either gui tools like like Oracle SQL Developer, command-line clients (mysql, sqlplus), or even generic tools like DbVisualizer.

On a whim, I decided to take a quick look at the Data Tools platform and see what I thought. After all, it might be great.

Turns out, it's ... very Eclipse. It's as if they took someone who'd barely used a database in their life, and certainly not other database tools, and yet was quite familiar with Eclipse. Setting up database connections for the first time is a little bizarre, as there are all sorts of database definitions without the required driver JARs, for which the JAR name is defined, but not supplied.

Once you've got that done, there's a data source explorer which isn't bad - lets you explore the structure of the database and metadata about the database. There's also a SQL Results view, again somewhat in keeping with other tools.

Where it gets really bizarre is when you want to do a Query. For many database tools, this is the central metaphor. There's a query box, you type in it, execute it and get results. For some tools, the idea of running scripts is secondary. That's because doing database development is often very exploratory and iterative, and it's not uncommon to build a query in pieces, executing it as you go, until you arrive at the final query that you intended from the beginning.

However, Eclipse has buried this idea deeply. You can either create an SQL file which you may edit, then right-click to execute, or you can create a launch configuration of 'adhoc query', which you can run in the Run menu. This isn't really ad-hoc, of course, because the query is defined in the launch configuration and not easily changed without going through a multi-step process. Both of these could be useful if the supplemented a simple query view which seems to be bizarrely missing.

Does this strike anyone else as a little queer?

I've gotta say, I'm tempted just to uninstall this, as it's clearly using up space my disk and possibly in memory for no reason at all. In its current form, I wouldn't bother with it.

MySQL - No Millisecond-Precision Date/Time

I guess I've been spoiled not doing anything particularly serious in MySQL thus far; I'm only just now discovering that it doesn't support millisecond-precision date-time datatypes directly. Fascinating.


Not a big deal most of the time, but for some precise date-comparison routines, we've used this capability before, in Oracle, and in other databases, so I'm just a little surprised.

Object-Relational Transparency II: the Designs

I was left feeling a little dissatisfied with this morning's post about the dangers of object-relational transparency. Although I felt I had communicated the high-level point I was trying to make, without the designs, it feels somewhat abstract.

In this instance, I was trying to map an an entity, Agreement, to different versions. Most of these versions would be effective for a particular timeframe (one month to twelve months), but some would be entirely superceded by the next version.

In the database, it seemed sensible to map this structure as follows:

create table AGREEMENT_VERSION
(
AGREEMENT_ID number not null,
EFFECTIVE_DT date,
-- Other Stuff here
)

The first version would have an effective-date in line with the agreement start, while subsequent versions would take on later effective dates. When a later version's effective-date is the same as a previous one's, the previous version's effective-date would be nullified, indicating that it was never effective.

Simple enough, so on to the object model. I wanted to be able to quickly run through the versions in effective-date order to find the version effective for a given date. Hibernate supports sorted collections via SortedSet and SortedMap. Trying to map the versions into a SortedSet is where it all starts to fall apart.

Set items are unique, requiring me to have a sort order across all items. For superceded versions with no effective date, there's no obvious sort order. I toyed with some silly ideas including using the hash code, but none felt quite right. I was trying to impose an arbitrary order on unordered things in order to support order for the ordered things, just because both happened to be stored within a single table.

After chatting with a few colleagues, partly design discussion and partly venting, I was struck, as I said this morning, with the realization that I was trying to force-fit my data model design onto the object model. Hibernate supports where clauses on collections, which allowed me to put the superceded versions into one collection and the effective versions in another:

<set name="effectiveVersions" table="AGREEMENT_VERSION" sort="natural" where="EFFECTIVE_DT is not null">...</set>
<bag name="archivedVersions" table="AGREEMENT_VERSION" where="EFFECTIVE_DT is null">...</bag>

Et voila; the object model makes sense, the data model makes sense, and the two meet happily inside a Hibernate mapping file. How sweet.

The Dangers of Object-Relational Transparency

One best qualities of a good abstraction layer is that it doesn't require you to think about that which has been abstracted. When writing C and Java, one doesn't have to know, or care, about the state of the machine's registers.

On the other hand, this can be a danger, particularly when the abstraction has leaks, even small ones. I was reminded of this the other day when, having assembled a design for a problem I was facing in the data model, I was having trouble finding a way to translate it to the object model.

After discussing several approaches with two of my colleagues, I eventually realized that the biggest stumbling block was my desire to translate the data model design to the object model, encouraged by the use of Hibernate, which does a great job of mapping like objects and tables.

In this instance, the best solution to my problem was to use a different object design than I was using in the database. As soon as I framed it this way, my challenges dropped away and I quickly found a suitable design for the object model.

This problem occurs at least in part because we are unable to accept that the database is an abstracted layer. There are some good reasons for this:

• database-application interaction remains one of the primary architecture elements that has a significant impact on scale and performance of almost any web application.
• database-level integration for two or more applications and/or tools is inexpensive and pervasive.

There is, however, a price -- you need to consider the design of each, and sometimes the best design in one is not the best design for another. I needed a reminder of this the other day. Do you?

Ad-Hoc Queries, Oracle, Using and Table-Qualified Wildcards

When I'm doing ad-hoc queries in Oracle as part of my development, debugging or research, I'll often make use of the USING clause. This lets me join two tables with a minimum of actual text.

SELECT *
FROM customer
INNER JOIN address USING (address_id);

This is more compact than specifying the id in both tables, given that our corporate nomenclature is <table>_id, so both tables tend to have columns with the same name.

Having done so, if I'm not exceptionally familiar with these tables, and haven't opened the table definition in another window, I may well want to find out the columns of one of the tables before deciding how to nail down the query. For instance, if I know I want some of the address, but I haven't decided which fields, I might do:

SELECT customer_name, address.*
FROM customer
INNER JOIN address USING (address_id);

At which point Oracle throws an ORA-25154. This is because address.* implies address.address_id, and Oracle doesn't like the idea that I might be qualifying a field that I've already indicated is the same as another field.

This frustrates me regularly. I'm not entirely sure why this is different from:

SELECT customer_name, address.*
FROM customer
INNER JOIN address ON customer.address_id = address.address_id;

Is there a way around this silliness that I have yet to discover?