Re: Questions on specifying table relationships - Mailing list pgsql-general

On Friday 01 Nov 2002 5:34 pm, Patrick Bakker wrote:
> Thanks for the reply Richard.
> I was thinking about your query templates suggestion and I don't think that
> is a sufficient solution for what I'm hoping to do. I have two purposes in
> mind for the query generator.
>
> My application allows the user to choose a starting point (ie. item, order,
> customer, location) and then shows a list - on the left-hand side of the
> screen, at the full height of the screen - presenting identifying elements
> (ie. name, order #, etc.) specific to whichever starting point the user has
> chosen. In this way, as the user changes selections on this list the
> content on the right automatically updates to reflect the new selection.
> Now the user can change any of the options on the query for each of these
> selection lists, in any combination, to create arbitrary selection lists
> which are more focused for their purposes. Presenting each possible query
> is likely to get very involved and I'd have to repeat it for every starting
> point.
>
> Additionally, I'm planning on using the same query system to describe
> reports in external files. The entire report system will consist of
> externally defined reports like this. So in effect the query system I'm
> looking for here is the underlying organization which would make your query
> template system possible and completely dynamic. ie. I can add a new report
> to the system by dropping in a report definition file in the server's
> shared network drive where the application reads its configuration from.

OK - so how would I create something like:

SELECT o.ord_date, p.prod_type, sum(o.sale_price) AS tot_sales
FROM orders o, products p
WHERE o.prod_id=p.prod_id
GROUP BY o.ord_date, p.prod_type;

Does your LH column list *all* the possible fields resulting from all possible
joins on the order table (products, customers, departments...)

> Can you elaborate on your statement "without knowing what it means to
> connect two tables via two columns I'm not sure the automated system could
> decide between options."? Are you referring to the autoquery generator
> being unable to guess the join because it depends on the meaning of the
> data in the tables or are you saying that the type of join will vary for
> each query and therefore cannot be known ahead of time?

I'm saying that even if you specify all possible joins between 2 tables, you
might have to choose between two ways connecting 3 tables. The only way to do
that is if you know what the columns *mean* and what your query is trying to
achieve.

> I have a few other options for providing more information to the auto-query
> generator. Since I'm parsing all of the fields
> and relationships from EJB descriptors (jbosscmp-jdbc.xml, jaws.xml and
> ejb-jar.xml) I have the following information available for describing
> relationships:
>
[sample information]
>
> If I do it this way and require every query to begin with the same EJB bean
> then each query would fully describe the relationship path needed for each
> field.

Well, you have enough here to specify relationships, but I still think it will
need a sentient being to decide on the details. I could be wrong though.

Are you familiar with MS-Access (or similar consumer DBs) and it's grid way of
building queries? That's about as general purpose and graphical as I've seen.
You identify the tables you want, and then the columns within. It
automatically indicates joins that it has been told about, or you can make
your own. You still need to know something about the structure of the
database though.

PS - knocking off for the day now, so probably no more msgs until Monday.
--
  Richard Huxton