Whats the problem with ZSQL? It calls a sql db with the sql statement, what more could you want. If you want more, use python. Cheers. -- Andy McKay. ----- Original Message ----- From: "Paul Browning" <paul.browning@bristol.ac.uk> To: "The Doctor What" <docwhat@gerf.org> Cc: <zope-dev@zope.org> Sent: Thursday, April 19, 2001 11:48 AM Subject: Re: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)

--On 19 April 2001 13:00 -0500 The Doctor What <docwhat@gerf.org> 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?

[intemperate stuff snipped]

I wouldn't dream of posting to zope-dev myself until I'd read around a bit. And I've been reading around for more than a year and I still haven't posted ...

...

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?

.... check out the following:

http://www.zope.org/Members/michel/ZB/RelationalDatabases.dtml and http://www.zope.org/Documentation/Guides/ZSQL

(the latter is now deprecated but you will find http://www.zope.org/Documentation/Guides/ZSQL-HTML/ZSQL.1.4.4.html useful in the present context - look for sql_delimiter)

Paul

-- The Library, Tyndall Avenue, Univ. of Bristol, Bristol, BS8 1TJ, UK E-mail: paul.browning@bristol.ac.uk URL: http://www.bris.ac.uk/

_______________________________________________ Zope-Dev maillist - Zope-Dev@zope.org http://lists.zope.org/mailman/listinfo/zope-dev ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope )

* Paul Browning (paul.browning@bristol.ac.uk) [010419 13:53]:

I wouldn't dream of posting to zope-dev myself until I'd read around a bit. And I've been reading around for more than a year and I still haven't posted ...

10 months, thought it was enough. Two and half on this problem alone. Thanks for the links below...

...

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?

.... check out the following:

http://www.zope.org/Members/michel/ZB/RelationalDatabases.dtml

Read it. Shows one insert, not very interesting.

and http://www.zope.org/Documentation/Guides/ZSQL

Out of date, same thing. The Zope Book is more up to date, but has the same example.

(the latter is now deprecated but you will find http://www.zope.org/Documentation/Guides/ZSQL-HTML/ZSQL.1.4.4.html useful in the present context - look for sql_delimiter)

I was looking for more concrete examples of a real setup. You want to be able to do deletes, inserts, and updates across a real normalized (3rd or 4th normal form) database. Ciao! -- Line Printer paper is strongest at the perforations. The Doctor What: Guru to the Gods http://docwhat.gerf.org/ docwhat@gerf.org KF6VNC

* Paul Erickson (erickson@kaivo.com) [010419 17:02]:

The Doctor What wrote:

...

...

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

That isn't what I mean. Try this (typed in, so it may need to be adjusted): <param>foo=0</param> <dtml-if expr="foo != 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.

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 ...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="Table1.id" ....

...

...

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.

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.

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:

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

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?

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>

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=1&dalist%0D%0A%3... Which is the URL pattern formed by a <SELECT name="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! -- Outside of a dog, a book is a man's best friend. Inside a dog it's too dark to read. -- Groucho Marx The Doctor What: Need I say more? http://docwhat.gerf.org/ docwhat@gerf.org KF6VNC

The Doctor What wrote:

* Paul Erickson (erickson@kaivo.com) [010419 17:02]:

...

The Doctor What wrote:

...

...

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

That isn't what I mean. Try this (typed in, so it may need to be adjusted): <param>foo=0</param> <dtml-if expr="foo != 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.

Reason: foo=0 is actually foo='0'. foo:int=0 should work like you want.

...

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 ...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="Table1.id" ....

This is a function of your field naming convention more than anything. Granted you don't always have control over that. I am surprised that name="spam.eggs" doesn't work on sqltests (it works everywhere else). Are you explicitly saying name="..."? that could be the problem, bare quoting assumes expr="..." where periods are significant.

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.

Python scripts are quite helpful for calling ZSQL methods. Just remember to pass REQUEST or the arguments explicitly. hth, -- | Casey Duncan | Kaivo, Inc. | cduncan@kaivo.com `------------------>