How to not reinvent the wheel - technologies/frameworks everywhere

[Stephan Mueller]

Hello out there!
That's a rather generic issue so I hope I've chosen the correct forum in the end.

I'm about to write a DB-Query application.
The access is READ-ONLY and against already-existing schemas.

Let me explain the odds & ends:
A Client (provides the GUI for the queries) connects to a server/retrieves
a server-instance (JNDI/RMI) that provides a list of datapools.
A dataPool is configured using a plugin mechanism, where a plugin consists
of a xml-configuration, describing the db-host, the plugin/dataPool starter-class
and things like what will be presented to the user in the gui.
The "what the user will see" part consists of:

a query editor to write and execute plain, old SQL

Available pre-defined queries, with coresponding parameters

A list of Entities and their attributes, from which the user can drag/drop a sample table to define how the result list will look like and what the query constraints would be (this comes down to a QBE implementation)
You may have seen this QBE approach in Paradox, Star/Openoffice (yes, Access is similar)

The crucial part: The list of entities is either generated from the database metadata,
JDBC provides nice functionality for this, or provided by classes, delivered with the plugin, to
provide the user with functional names/relations rather then the techy-named tables/columns.
(the functional-to-technical translation may be done via config files or read from an existing repository
like most CRMs bring with them).

The helper classes from the plugin will know how to generate the SQL for a given Entity and their related Entities - if the user chose such things.

Now the "reinvent the wheel" part. There are a lot of frameworks out there, in particular Hibernate that allow for abstract query (QBE) generation. I have no experience with this but from what I've read across the web it would be hard to form a QBE for:
the relations: a(id), b(id), ab(aID, bID) and to retrieve "the 'a's that are linked to every 'b'" which would resolve to a query like

SELECT a.id 
FROM a 
WHERE a.id NOT IN 
   (SELECT t1.id 
    FROM a AS t1 JOIN b as t2 
    WHERE (t1.id, t2.id) NOT IN 
       (SELECT t1.id, t2.id 
        FROM a AS t1 JOIN ab AS inter ON inter.aid = t1.id
        LEFT JOIN b AS t2 ON t2.id = inter.bid));

or

SELECT a.id FROM  a WHERE (SELECT COUNT(DISTINCT bid) FROM ab WHERE ab.aid = a.id) = (SELECT COUNT(b.id) FROM b)

or (fancy stuff with HAVING)
or ...
You get the point.

Now we need a question:
Do you know of any QBE implementations that allow complex query generation
(I've read that hibernate has an implementation of QBE and E-QBE but never worked with them, tbh, I've little experience with hibernate) ?

Can you suggest frameworks for what I want to achieve?
Is my intention clear at all, if not, what other information do you need?
Is there something I should get straight in my thoughts about the problem ?

Looking forward to your comments!

Regards.

[Bear Bibeault]

"srm wasHere", please check your private messages for an important administrative matter.

[Campbell Ritchie]

Damn! You've beaten me to the warning. Is your middle name Rob by any chance?

[Stephan Mueller]

Wow.
Not really the comment I'd wished for but first things first, right.
Fixed and ready to go.

[Campbell Ritchie]

Moving to our databases forum, where you will be more likely to get a useful answer.

[Jan Cumps]

I don't know any library that has these capabilities.

Oracle has a product called Discoverer, that performs most of the capabilities you are describing (minus data pool / jndi)
It is a business layer above database tables/views.
The layer exists of objects with attributes.The links between these objects are defined in the layer.

When creating a report(this is done by selecting objects and attributes, and setting conditions), Discoverer cunningly builds an sql statement against the underlying database tables / views.

Not trivial! Difficult queries! Lots of grouping on lots of levels! Beware!

[Stephan Mueller]

Jan, thank you for the pointer to Oracle's tool. I'll see if I can have a look at this one and see, how they tried to solve these problems.
On the one hand, it's good to hear that there are no state of the art solutions to this, so I'm probably not reinventing the wheel (as a sidenote, this application will be part of my diploma thesis), on the other hand, it would have been great to look into other solutions to get a feeling on what is to be done.
I guess I have to dig into hibernates current qbe implementation, also a look into openoffice might be worth a try (even if they c++).

Discoverer cunningly builds an sql statement

Do you have any internas on this?

Not trivial! Difficult queries! Lots of grouping on lots of levels! Beware!

Yes, that's what I'm looking forward to (or what I'm afraid of).
Another area of concern is how to provide a user interface, that allows the user to express his ideas in a way, it get's correctly interpreted and translated. The old QBE interfaces look rather formal und archaic.
Let's see with what I can come up with.

[Jan Cumps]

Do you have any internas on this?

No. But at one time in my career I was responsible for solving issues like: "My figure in my report is wrong and it worked last month and I got this report from Jim who has left the company". I would then ask Discoverer to show me the sql statement it generated for that report, and would track back from there to the source data.
These queries could be rather complex if the report was using summaries, grouping, breaks, totals, ... at various levels.
There are also some challenges with circular references (the tool does its very best to detect them and warn the user).
Kudos to Oracle for getting it done.
And kudos to you.

[Stephan Mueller]

Thank you for the discussion, Jan.
A particular "thank you" for the fact, that I could recognize that
I'm not developing a (full fledged) reporting tool - rather an application that allows
to transform raw technical data into a functional view which can be used for further
business intelligence (et al) operation, this transformation is done by letting the user define how
the result should look like (as stated in the above posts).
So I'm taking a lot of complexity out of

These queries could be rather complex if the report was using summaries, grouping, breaks, totals, ... at various levels.

Still, there's enough to do and think through. Let's see what I can accomplish, and if my employer is ok with it, the core parts of the application will be open-source
and I'll post it here to discuss this with the community.

Regards,
Stephan

[Jan Cumps]