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

[The Doctor What]

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

* Paul Erickson (erickson@kaivo.com) [010419 17:02]:
> The Doctor What wrote:
> > > * Loosing the variable between the form and dtml-if
>=20
> I don't understand this.  I'm assuming that you are losing values that
> are not in your argument list.  All you have to do is add the arguments.

That isn't what I mean.  Try this (typed in, so it may need to be
adjusted):
<param>foo=3D0</param>
<dtml-if expr=3D"foo !=3D 0">Life is good</dtml-if>

You'll NEVER get Life is good to print out.  EVER.  This is because
ZSQL is setting foo to 0.  But dtml-var and dtml-sqlvar both work.

> > > * Inability to handle table.field names for variables
> > > * And enough flexibility to work around the above problems
> > >
> > > This makes ZSQL extreamely nasty, and impossible to use with a
> > > normalized database.
>=20
> Database normalization isn't really an issue.  It sounds like you're
> really just having problems with the syntax of joins.

Nope, can do joins.  Been doing joins (mainly inner).  Not the
problem.  The problem is that if I have tables like:
Table1
------
id
name
desc

Table2
------
id
Table1ID   <-- Foreign Key thrown in for fun.
name
desc

And I join them, then I MUST rename all the selects using AS:
select
  Table1.id as id1
  Table1.name as name1
=2E..etc....

Because I can't have zsql put the variables in the caller's
namespace as "Table1.id".  It puts them in as "id" (without the AS).

Fortunately, I found the column for sqltest (which is the other end
of ZSQL):
<dtml-sqltest id1 column=3D"Table1.id" ....

> > >
> > > What's the point?  ZSQL sucks, how do I talk to the DB directly?
>=20
> Grab the Python database adapter and write some python classes or
> external methods to do what you need.

Can I use them from a python script?  If not, what's the point?  I
mean: External methods are nice to have when you have *no other
choice*, but they aren't something I'd want to debug and deal with
for object.

> > This makes things work MUCH better.  So there are work arounds.
>=20
> This isn't a work-around, it is the way that it is intended to be used.

I didn't see a single example of it.  The Zope Book didn't mention
this at all (except as a single line reference).  I would *never*
have gone looking for the word column.  Why? It's a field!

> Again, I think that it's a matter of understanding how ZSQL works,
> rather than a limitation of ZSQL.  If you have column name conflicts,
> you can always use something like:
>=20
> SELECT a.field as a_field, b.field as b_field FROM ...
>=20
> Then refer to them in dtml like &dtml-a_field; or <dtml-var
> name=3D"a_field">

Right, but you still have to do extra work to get them back into a
ZSQL object.  column lets you do that.  It's got no examples to help
out with.  It's documented once.  I didn't see it.  Life is better
now.

> > I still would love some examples.  Do people end up with 4 ZSQL
> > objects per thing they manipulate in their database?:
> > UPDATE, SELECT, INSERT and DELETE?  Or do they mix them somehow?
>=20
> I typically wind up with a ZSQL method for each of the CRUD operations.=
=20
> Sometimes I have separate selects methods for more complex joins, if the
> DTML code gets too ugly when I try to combine them into a single method.
>
>=20
> Example of a fairly typical join statement (for MySQL database):
>=20
> Arguments-
> begin_date:date end_date:date=3D"2037-01-01"
>=20
> SELECT search_string, search_result,  search_date, result_category,
> legal_category.name
> FROM search_log LEFT JOIN legal_category ON
> search_log.result_category=3Dlegal_category.code
>=20
> <dtml-sqlgroup required where>
>   <dtml-sqltest name=3D"begin_date" op=3D"gt" type=3D"nb"
>       column=3D"search_date">
> <dtml-and>
>   <dtml-sqltest name=3D"end_date" type=3D"nb" op=3D"lt"
>       column=3D"search_date" optional>
> </dtml-sqlgroup>

Hey!  An example!

> Good Luck

Thanks.  So I'm still having trouble.  I can't get any of the list
examples to work.

I build a select multiple list, and then try to dtml-in on it.  It
doesn't seem to work.  REQUEST shows in it th other and form
namespaces, as a list, but I can't actually dtml-var it or anything.

Example:
q<dtml-var dalist>p
<br>
<dtml-in VirtualRootPhysicalPath>
d<dtml-var sequence-item>b
</dtml-in>
<hr>
<dtml-var REQUEST>

If you call this with this URL (substitute as needed):
http://docwhat.gerf.org:9673/links/test?dalist%0D%0A%3Alist=3D1&dalist%0D%0=
A%3Alist=3D3&dalist%0D%0A%3Alist=3D8

Which is the URL pattern formed by a <SELECT name=3D"dalist:list"
multiple>....

Then you get errors on dalist.  The examples at
http://www.zope.org/Members/AlexR/SelectionLists

Don't work as exactl above.

Help?

Ciao!

--=20
Outside of a dog, a book is a man's best friend.  Inside a dog it's too dar=
k to read.
	-- Groucho Marx

The Doctor What: Need I say more?                http://docwhat.gerf.org/
docwhat@gerf.org                                                   KF6VNC

--MnLPg7ZWsaic7Fhd
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

iD8DBQE632TskJDks3INMZURAueeAKCzNXnJ1pEFk0wVMDdnqwnVZsA+swCg8xC/
YXqVauvl4WgoD1Z9y1XORB4=
=JL8X
-----END PGP SIGNATURE-----

--MnLPg7ZWsaic7Fhd--