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-name>
          <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@postgresql.org
[mailto:pgsql-general-owner@postgresql.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