Re: Questions on specifying table relationships

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.

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 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:

>From jbosscmp-jdbc.xml:

<ejb-relation>
<ejb-relation-name>OrderLine-Item</ejb-relation-name>
<foreign-key-mapping/>
<ejb-relationship-role>

<ejb-relationship-role-name>OrderLine-has-Item</ejb-relationship-role-name>
<fk-constraint>true</fk-constraint>
<key-fields/>
</ejb-relationship-role>
<ejb-relationship-role>

<ejb-relationship-role-name>Item-usedby-OrderLine</ejb-relationship-role-nam
e>
<key-fields>
<key-field>
<field-name>itemPK</field-name>
<column-name>pk_item</column-name>
</key-field>
</key-fields>
</ejb-relationship-role>
</ejb-relation>

And from ejb-jar.xml:

<ejb-relation>
<ejb-relation-name>OrderLine-Item</ejb-relation-name>
<!-- unidirectional -->
<ejb-relationship-role >

<ejb-relationship-role-name>OrderLine-has-Item</ejb-relationship-role-name>
<multiplicity>Many</multiplicity>
<relationship-role-source >
<ejb-name>SalesOrder/OrderLine</ejb-name>
</relationship-role-source>
<cmr-field >
<cmr-field-name>item</cmr-field-name>
</cmr-field>
</ejb-relationship-role>
<ejb-relationship-role >

<ejb-relationship-role-name>Item-OrderLine</ejb-relationship-role-name>
<multiplicity>One</multiplicity>
<relationship-role-source >
<ejb-name>Inventory/Item</ejb-name>
</relationship-role-source>
</ejb-relationship-role>
</ejb-relation>

I'm thinking of using a standard naming convention for the
<ejb-relationship-role>. For example:
-has- 1:1 INNER JOIN
-mayhave- LEFT/RIGHT JOIN
-usedby- table on left doesn't know about table on right

Additionally, I'm thinking of writing all query specifications using a
notatation patterned after the EJB beans and fields.

Item.name
Item.orderLine.order.customer.name

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.

What do you think?
Patrick

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Richard Huxton
Sent: Friday, November 01, 2002 8:18 AM
To: Patrick Bakker; PostgreSQL General (E-mail)
Subject: Re: [GENERAL] Questions on specifying table relationships

On Thursday 31 Oct 2002 12:36 am, Patrick Bakker wrote:
[long description of automatic query generator]
> I'm not entirely clear what I'm asking here but I think it comes down to
> confirming the following:
> (Q) What do I need to autogenerate the relationships in a query?
> (A)
> - tables used in the query
> - fields connecting the tables together
> - what type of join exists between each table

And I fear, the semantics of each possible join. Without knowing what it
means
to connect two tables via two columns I'm not sure the automated system
could
decide between options.

> (Q) Since the user can choose fields from tables which are not directly
> connected, what is the best way to
> determine a relationship/join path to connect the tables? ie. which
> items did this customer buy?
> would require item -> order_line -> order -> customer given only item
> and customer ...
>
> - something is missing here: because item -> purchase_line ->
> purchase_order -> customer could also
> match (unless purchase_order people are listed in vendor instead of
> customer ...)

I think it's the meaning that's missing. I don't have any clever suggestions
here as to how to model the semantics a query.

Could you get away with a set of query templates and then select which
fields?

e.g.
choice 1 "show items purchased per customer"
choice 2 "list customer id,name item name, quantity, price"
choice 3 "for Jan 2002, sorted by cutomer id"

Choice 1 would be to pick a template and would determine both the join and
what fields were available for subsequent choices.

> Sorry for the brain dump but I would appreciate it somebody can clarify
> anything.
> Patrick

Sorry I've only addressed a couple of your questions. Hope my mutterings
have
been of some use.

--
Richard Huxton

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster