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

[Paul Erickson]

The Doctor What wrote:
> 
> * The Doctor What (docwhat@gerf.org) [010419 11:57]:
> > Does any one have an example of ZSQL being used witha normalized
> > database?  Or is ZSQL just useless?
> >
> > Near as I can tell, between:
> > * Broken type marshalling
> > * Loosing the variable between the form and dtml-if

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.

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

Database normalization isn't really an issue.  It sounds like you're
really just having problems with the syntax of joins.

> >
> > What's the point?  ZSQL sucks, how do I talk to the DB directly?

Grab the Python database adapter and write some python classes or
external methods to do what you need.

> >
> > Yeah, I keep almost getting this to be useful.  But damn it if I
> > don't keep hitting a brick wall.  And there are no complete examples
> > or demos that I can find to load up.
> >
> > Irritatedly yours,
> >         DocWhat
> 
> I would like to apologize for being particularly pissy.  Things are
> quite as bad as I say up there...
> 
> My third point is only half true.  I can have SQLTEST specify a
> column name (aka a field):
> <dtml-sqltest somevarname column="SQLTABLE.sqlfield" type...>
> 
> This makes things work MUCH better.  So there are work arounds.

This isn't a work-around, it is the way that it is intended to be used.

  But
> this doesn't excuse this not working:
> SELECT  table.field1, table.field2 FROM ....
> 
> and then:
> <dtml-var name="table.field1">  <--doesn't work
> <dtml-var name="field1"> <-- does work
> 
> I know that the '.' has a special meaning, but there should be ways
> around this if the use wants.

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:

SELECT a.field as a_field, b.field as b_field FROM ...

Then refer to them in dtml like &dtml-a_field; or <dtml-var
name="a_field">

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

I typically wind up with a ZSQL method for each of the CRUD operations. 
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.


Example of a fairly typical join statement (for MySQL database):

Arguments-
begin_date:date end_date:date="2037-01-01"

SELECT search_string, search_result,  search_date, result_category,
legal_category.name
FROM search_log LEFT JOIN legal_category ON
search_log.result_category=legal_category.code

<dtml-sqlgroup required where>
  <dtml-sqltest name="begin_date" op="gt" type="nb"
      column="search_date">
<dtml-and>
  <dtml-sqltest name="end_date" type="nb" op="lt"
      column="search_date" optional>
</dtml-sqlgroup>

Good Luck

-Paul