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

[The Doctor What]

--UlVJffcvxoiEqYs2
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

* Adrian Hungate (ahungate@acucorp.com) [010420 05:06]:
> May be I am misunderstanding your problem here, but are you suggesting th=
at
>=20
>     SELECT tab1.col1 col1, tab2.col1 col2
>     FROM ... etc ...
>=20
> 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=3D'tab1.col1'> (note single quotes)

> The normality (or abnormality) of your databases should make no differenc=
e,
> (Except to how complex your individual SQL statements become). What datab=
ase
> 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=3D0' 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=3D0</param>
<dtml-if expr=3D"foo <> 0">Not Zero<dtml-else>Zero</dtml-if>

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

It will ALWAYS print 'Zero'.

This makes it hard to do something with a param that defaults to
nothing like:
----------------------------------------
<param>category_id=3D""</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=3D"xref_url_category.categoryid" typ=
e=3D"int">
      <dtml-and>
      xref_url_category.urlid =3D url.id
    </dtml-if>
</dtml-sqlgroup>
----------------------------------------

You can't even replace the param with "category_id:int=3D0" and then
check for category_id <=3D 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!

--=20
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

--UlVJffcvxoiEqYs2
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.4 (GNU/Linux)
Comment: For info see http://www.gnupg.org

iD8DBQE64EWukJDks3INMZURAjy2AJ9dqnhCeQRVmjMljx12xwIF5pdOvACg0fbW
s6iFcUWqIicd56+BBjCGg70=
=+v8c
-----END PGP SIGNATURE-----

--UlVJffcvxoiEqYs2--