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

[Bryan Baszczewski]

<<<< ...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 havent followed the entire conversation so forgive me if we are on the
same page but in different books.  Have you tried to alias the column names?

e.g.:  SELECT tab1.col1 AS COL1_A, tab2.col1 AS COL1_B
		FROM etc...




-----Original Message-----
From: The Doctor What [mailto:docwhat@gerf.org]
Sent: Friday, April 20, 2001 10:21 AM
To: zope-dev@zope.org
Subject: Re: [Zope-dev] ZSQL and Normalized databases (or why ZSQL
sucks)


* 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.

Perhaps like so:
<dtml-var name='tab1.col1'> (note single quotes)

> The normality (or abnormality) of your databases should make no
difference,
> (Except to how complex your individual SQL statements become). What
database
> are you running against? Maybe you have hit a database connector bug?

Postgresql.  I've tried all three of the DAs: Psyco, Popy and
PostresDA

[Nice Example Snipped]

What DB was that?  The syntax isn't what I expect from Postgres,
Tandem and MySQL.

> As far as the 'foo=0' example, if you set foo to 0, and do nothing in
> between to change it, of course a test for not equal to 0 will fail, what
> would you expect?

Here is the example again, but better:
<param>foo:int=0</param>
<dtml-if expr="foo <> 0">Not Zero<dtml-else>Zero</dtml-if>

If called like so:
<dtml-var expr="zsql_example(foo=200)">

It will ALWAYS print 'Zero'.

This makes it hard to do something with a param that defaults to
nothing like:
----------------------------------------
<param>category_id=""</param>
SELECT
    url.id,
    url.name
    <dtml-if category_id>,
    xref_url_category.categoryid as category_id,
    </dtml-if>
FROM
    url
    <dtml-if category_id>,
    xref_url_category
    </dtml-if>
<dtml-sqlgroup where>
    <dtml-if category_id>
      <dtml-sqltest category_id column="xref_url_category.categoryid"
type="int">
      <dtml-and>
      xref_url_category.urlid = url.id
    </dtml-if>
</dtml-sqlgroup>
----------------------------------------

You can't even replace the param with "category_id:int=0" and then
check for category_id <= 0 in the dtml-ifs.

I finished my project, so I'm happy for the moment.  I have another
ZSQL project (a DVD, Comic Book and Video tracker for my Manga and
Anime), so I'll be back.

Thanks to everyone who spent time to help out.

Ciao!

--
Who are you going to believe, me or your own eyes?
	-- Groucho Marx

The Doctor What: Guru to the Gods                http://docwhat.gerf.org/
docwhat@gerf.org                                                   KF6VNC