[Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)
Adrian Hungate
ahungate@acucorp.com
Fri, 20 Apr 2001 08:26:42 -0700
This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.
------_=_NextPart_001_01C0C9AE.50940A60
Content-Type: text/plain;
charset="iso-8859-1"
I am using ZODBC talking to an MS-SQL7 database (Yack spit), so the syntax
is a little different.
As for the unchangeable defaults, I use defaults a lot, and I don't think
I've ever seen your problem.
What you appear to be trying to do with the ZSQL Method in your example
strikes me as 'A Bad Idea'(tm). Having the schema of the returned data
change depending on the value of a parameter seems to be asking for problems
(IMHO).
If you look back at the my last example, and replace the PARAMS with:
dDate="2001/03/31"
sOffsetSize="month"
nOffset="1"
This certainly does work, I just tested it. Interestingly, so does:
PARAMS: testval:int=0
<dtml-if testval>
select count(shipped_date) cnt
<dtml-else>
select count(shipped_date)/2 halfcnt
</dtml-if>
from order_
If you call it with testval=1, you get a column called cnt, with testval=0
you get a column called halfcnt. Isn't this just what you are saying doesn't
work?
Adrian...
--
Adrian Hungate
Manager, European I.S.
Acucorp UK Limited
-----Original Message-----
From: The Doctor What [mailto:docwhat@gerf.org]
Sent: Friday, 20 April 2001 15:21
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
------_=_NextPart_001_01C0C9AE.50940A60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2650.12">
<TITLE>RE: [Zope-dev] ZSQL and Normalized databases (or why ZSQL =
sucks)</TITLE>
</HEAD>
<BODY>
<P><FONT SIZE=3D2>I am using ZODBC talking to an MS-SQL7 database (Yack =
spit), so the syntax is a little different.</FONT>
</P>
<P><FONT SIZE=3D2>As for the unchangeable defaults, I use defaults a =
lot, and I don't think I've ever seen your problem.</FONT>
</P>
<P><FONT SIZE=3D2>What you appear to be trying to do with the ZSQL =
Method in your example strikes me as 'A Bad Idea'(tm). Having the =
schema of the returned data change depending on the value of a =
parameter seems to be asking for problems (IMHO).</FONT></P>
<P><FONT SIZE=3D2>If you look back at the my last example, and replace =
the PARAMS with:</FONT>
</P>
<P><FONT SIZE=3D2> =
dDate=3D"2001/03/31"</FONT>
<BR><FONT SIZE=3D2> =
sOffsetSize=3D"month"</FONT>
<BR><FONT SIZE=3D2> nOffset=3D"1"</FONT>
</P>
<P><FONT SIZE=3D2>This certainly does work, I just tested it. =
Interestingly, so does:</FONT>
</P>
<P><FONT SIZE=3D2>PARAMS: testval:int=3D0</FONT>
<BR><FONT SIZE=3D2><dtml-if testval></FONT>
<BR><FONT SIZE=3D2>select count(shipped_date) cnt</FONT>
<BR><FONT SIZE=3D2><dtml-else></FONT>
<BR><FONT SIZE=3D2>select count(shipped_date)/2 halfcnt</FONT>
<BR><FONT SIZE=3D2></dtml-if></FONT>
<BR><FONT SIZE=3D2>from order_</FONT>
</P>
<P><FONT SIZE=3D2>If you call it with testval=3D1, you get a column =
called cnt, with testval=3D0 you get a column called halfcnt. Isn't =
this just what you are saying doesn't work?</FONT></P>
<P><FONT SIZE=3D2>Adrian...</FONT>
</P>
<P><FONT SIZE=3D2>--</FONT>
<BR><FONT SIZE=3D2>Adrian Hungate</FONT>
<BR><FONT SIZE=3D2>Manager, European I.S.</FONT>
<BR><FONT SIZE=3D2>Acucorp UK Limited</FONT>
</P>
<P><FONT SIZE=3D2>-----Original Message-----</FONT>
<BR><FONT SIZE=3D2>From: The Doctor What [<A =
HREF=3D"mailto:docwhat@gerf.org">mailto:docwhat@gerf.org</A>]</FONT>
<BR><FONT SIZE=3D2>Sent: Friday, 20 April 2001 15:21</FONT>
<BR><FONT SIZE=3D2>To: zope-dev@zope.org</FONT>
<BR><FONT SIZE=3D2>Subject: Re: [Zope-dev] ZSQL and Normalized =
databases (or why ZSQL</FONT>
<BR><FONT SIZE=3D2>sucks)</FONT>
</P>
<BR>
<P><FONT SIZE=3D2>* Adrian Hungate (ahungate@acucorp.com) [010420 =
05:06]:</FONT>
<BR><FONT SIZE=3D2>> May be I am misunderstanding your problem here, =
but are you suggesting that</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> SELECT tab1.col1 col1, =
tab2.col1 col2</FONT>
<BR><FONT SIZE=3D2>> FROM ... etc ...</FONT>
<BR><FONT SIZE=3D2>> </FONT>
<BR><FONT SIZE=3D2>> Does not expose 'col1' and 'col2' in the =
namespace for you? This is not my</FONT>
<BR><FONT SIZE=3D2>> experience. Ok, I only have about 50 ZSQL =
methods on my intranet but they</FONT>
<BR><FONT SIZE=3D2>> are all complex queries with column naming =
clashes. If it failed to work as</FONT>
<BR><FONT SIZE=3D2>> you are suggesting, I would not have continued =
using Zope - Database</FONT>
<BR><FONT SIZE=3D2>> connectivity was one of my criteria.</FONT>
</P>
<P><FONT SIZE=3D2>I mean that:</FONT>
<BR><FONT SIZE=3D2> SELECT tab1.col1, tab2.col1</FONT>
<BR><FONT SIZE=3D2> FROM ... etc ...</FONT>
</P>
<P><FONT SIZE=3D2>Does not expose 'tab1.col1' and 'tab2.col1'. In =
otherwords, it</FONT>
<BR><FONT SIZE=3D2>doesn't gracefully handle name clashes. I =
realize that calling out</FONT>
<BR><FONT SIZE=3D2>the variable 'tab1.col1' out of the namespace is a =
pain, but</FONT>
<BR><FONT SIZE=3D2>(hopefully) in the future it would be made =
easier.</FONT>
</P>
<P><FONT SIZE=3D2>Perhaps like so:</FONT>
<BR><FONT SIZE=3D2><dtml-var name=3D'tab1.col1'> (note single =
quotes)</FONT>
</P>
<P><FONT SIZE=3D2>> The normality (or abnormality) of your databases =
should make no difference,</FONT>
<BR><FONT SIZE=3D2>> (Except to how complex your individual SQL =
statements become). What database</FONT>
<BR><FONT SIZE=3D2>> are you running against? Maybe you have hit a =
database connector bug?</FONT>
</P>
<P><FONT SIZE=3D2>Postgresql. I've tried all three of the DAs: =
Psyco, Popy and</FONT>
<BR><FONT SIZE=3D2>PostresDA</FONT>
</P>
<P><FONT SIZE=3D2>[Nice Example Snipped]</FONT>
</P>
<P><FONT SIZE=3D2>What DB was that? The syntax isn't what I =
expect from Postgres,</FONT>
<BR><FONT SIZE=3D2>Tandem and MySQL.</FONT>
</P>
<P><FONT SIZE=3D2>> As far as the 'foo=3D0' example, if you set foo =
to 0, and do nothing in</FONT>
<BR><FONT SIZE=3D2>> between to change it, of course a test for not =
equal to 0 will fail, what</FONT>
<BR><FONT SIZE=3D2>> would you expect?</FONT>
</P>
<P><FONT SIZE=3D2>Here is the example again, but better:</FONT>
<BR><FONT SIZE=3D2><param>foo:int=3D0</param></FONT>
<BR><FONT SIZE=3D2><dtml-if expr=3D"foo <> 0">Not =
Zero<dtml-else>Zero</dtml-if></FONT>
</P>
<P><FONT SIZE=3D2>If called like so:</FONT>
<BR><FONT SIZE=3D2><dtml-var =
expr=3D"zsql_example(foo=3D200)"></FONT>
</P>
<P><FONT SIZE=3D2>It will ALWAYS print 'Zero'.</FONT>
</P>
<P><FONT SIZE=3D2>This makes it hard to do something with a param that =
defaults to</FONT>
<BR><FONT SIZE=3D2>nothing like:</FONT>
<BR><FONT SIZE=3D2>----------------------------------------</FONT>
<BR><FONT =
SIZE=3D2><param>category_id=3D""</param></FONT>
<BR><FONT SIZE=3D2>SELECT</FONT>
<BR><FONT SIZE=3D2> url.id,</FONT>
<BR><FONT SIZE=3D2> url.name</FONT>
<BR><FONT SIZE=3D2> <dtml-if =
category_id>,</FONT>
<BR><FONT SIZE=3D2> xref_url_category.categoryid as =
category_id,</FONT>
<BR><FONT SIZE=3D2> </dtml-if></FONT>
<BR><FONT SIZE=3D2>FROM</FONT>
<BR><FONT SIZE=3D2> url</FONT>
<BR><FONT SIZE=3D2> <dtml-if =
category_id>,</FONT>
<BR><FONT SIZE=3D2> xref_url_category</FONT>
<BR><FONT SIZE=3D2> </dtml-if></FONT>
<BR><FONT SIZE=3D2><dtml-sqlgroup where></FONT>
<BR><FONT SIZE=3D2> <dtml-if =
category_id></FONT>
<BR><FONT SIZE=3D2> <dtml-sqltest =
category_id column=3D"xref_url_category.categoryid" =
type=3D"int"></FONT>
<BR><FONT SIZE=3D2> =
<dtml-and></FONT>
<BR><FONT SIZE=3D2> =
xref_url_category.urlid =3D url.id</FONT>
<BR><FONT SIZE=3D2> </dtml-if></FONT>
<BR><FONT SIZE=3D2></dtml-sqlgroup></FONT>
<BR><FONT SIZE=3D2>----------------------------------------</FONT>
</P>
<P><FONT SIZE=3D2>You can't even replace the param with =
"category_id:int=3D0" and then</FONT>
<BR><FONT SIZE=3D2>check for category_id <=3D 0 in the =
dtml-ifs.</FONT>
</P>
<P><FONT SIZE=3D2>I finished my project, so I'm happy for the =
moment. I have another</FONT>
<BR><FONT SIZE=3D2>ZSQL project (a DVD, Comic Book and Video tracker =
for my Manga and</FONT>
<BR><FONT SIZE=3D2>Anime), so I'll be back.</FONT>
</P>
<P><FONT SIZE=3D2>Thanks to everyone who spent time to help out.</FONT>
</P>
<P><FONT SIZE=3D2>Ciao!</FONT>
</P>
<P><FONT SIZE=3D2>-- </FONT>
<BR><FONT SIZE=3D2>Who are you going to believe, me or your own =
eyes?</FONT>
<BR> <FONT SIZE=3D2>-- =
Groucho Marx</FONT>
</P>
<P><FONT SIZE=3D2>The Doctor What: Guru to the =
Gods &n=
bsp; <A HREF=3D"http://docwhat.gerf.org/" =
TARGET=3D"_blank">http://docwhat.gerf.org/</A></FONT>
<BR><FONT =
SIZE=3D2>docwhat@gerf.org  =
;  =
;  =
;  =
; KF6VNC</FONT>
</P>
</BODY>
</HTML>
------_=_NextPart_001_01C0C9AE.50940A60--