[Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)

[Hannu Krosing]

The Doctor What wrote:
> 
> * Adrian Hungate (ahungate@acucorp.com) [010420 05:06]:
> > May be I am misunderstanding your problem here, but are you suggesting that
> >
> >     SELECT tab1.col1 col1, tab2.col1 col2
> >     FROM ... etc ...
> >
> > Does not expose 'col1' and 'col2' in the namespace for you? This is not my
> > experience. Ok, I only have about 50 ZSQL methods on my intranet but they
> > are all complex queries with column naming clashes. If it failed to work as
> > you are suggesting, I would not have continued using Zope - Database
> > connectivity was one of my criteria.
> 
> I mean that:
>    SELECT tab1.col1, tab2.col1
>    FROM ... etc ...
> 
> Does not expose 'tab1.col1' and 'tab2.col1'.  In otherwords, it
> doesn't gracefully handle name clashes.  I realize that calling out
> the variable 'tab1.col1' out of the namespace is a pain, but
> (hopefully) in the future it would be made easier.

I suspect it is mainly a problem with SQL ;)

amphora2=# select item.item_id, item.ordinal from item where item_id =
15;
 item_id | ordinal 
---------+---------
      15 |       0
(1 row)

amphora2=# select item.item_type_id, item_type.item_type_id from
item,item_type
amphora2-# where item_id = 15 and item.item_type_id=
item_type.item_type_id;
 item_type_id | item_type_id 
--------------+--------------
            1 |            1
(1 row)

The above example is with postgreSQL, but IIRC it is how the SQL
standard 
defines it to behave.

Except that perhaps it may forbid one from writing such queries if there
is 
a possibility of both item_type_id's not being the same.

-----------------
Hannu