Re: [Zope-dev] ZSQL and Normalized databases (or why ZSQL sucks)
On Thu, Apr 19, 2001 at 01:00:37PM -0500, 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 * Inability to handle table.field names for variables * And enough flexibility to work around the above problems
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...>
I know that the '.' has a special meaning, but there should be ways around this if the use wants.
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 cannot emphasize enough. Do not try to make all-singing all-dancing ZSQL methods. This seems to be something that lots of people want to do for some reason. Observation 1: SQL syntax is incredibly irregular. It is almost impossible to write, in any language, a sinlge statement that can handle these four options. Observation 2: You almost always want fewer arguments to a delete than to an insert. You also typically have more worries about deletes than inserts w.r.t. social issues. That is, typically there are more people who you allow to select than you allow to insert than you allow to update than you allow to delete. For both control reasons and format reasons you do not want a single SQL statement to handle all possible operations. Observation 3: Simple SQL statements are much easier to debug than complicated ones. Observation 4: Simple SQL statements are much easier to develop than complicated ones. Observation 5: As far as I can tell, there is very little, if any, space or performance penalty for using many small ZSQL methods rather than one complicated one. So, when should you use the fancier options? If you need to have rollback work, you may need to combine several statements into a single method. If you have a report that changes behavior depending on its input, maybe you want to use a complicated ZSQL method to grab the data. E.g. I have a form that accepts a min and a max part number and a min and max description (and several other min/max pairs). I want to sort depending on the first pair filled in. This is conveniently done with a complicated ZSQL method. I have not yet decided if it is wise to do so. Rule of thumb: methods should seldom span more than 2 or 3 screenfuls. The major exception would be a method with an enormous number of parameters. Zope is best used with the KISS principle in mind. It is better to factor a problem into a largish number of very simple components than it is to use a small number of very complicated components. This is especially true when developing through the web, where you have smallish text windows and more primitive tools. Jim Penny
Ciao!
-- Who are you going to believe, me or your own eyes? -- Groucho Marx
The Doctor What: Need I say more? http://docwhat.gerf.org/ docwhat@gerf.org KF6VNC
* Jim Penny (jpenny@universal-fasteners.com) [010419 14:10]:
I cannot emphasize enough. Do not try to make all-singing all-dancing ZSQL methods. This seems to be something that lots of people want to do for some reason.
I was looking more for *any* examples I could actually play with. Examples that would be using a real, normalized database (3rd or 4th normalized form) that are a littel more complicated than just a single table.
Observation 1: SQL syntax is incredibly irregular. It is almost impossible to write, in any language, a sinlge statement that can handle these four options.
Agreed, but I merged for my project the insert and update, because they *are* nearly the same, except one has an ID already and the other doesn't.
Observation 2: You almost always want fewer arguments to a delete than to an insert. You also typically have more worries about deletes than inserts w.r.t. social issues. That is, typically there are more people who you allow to select than you allow to insert than you allow to update than you allow to delete. For both control reasons and format reasons you do not want a single SQL statement to handle all possible operations.
Yes, delete is separate, and the view is seperate.
Observation 3: Simple SQL statements are much easier to debug than complicated ones.
Yes.
Observation 4: Simple SQL statements are much easier to develop than complicated ones.
I have a problem with a database (like a contact management db) that might have: Accounts, Contacts, phone numbers, addresses, and emails. Each would need either 3 or 4 zsql objects for a total of 15-20. That's a big pain in the ass to manage.
Observation 5: As far as I can tell, there is very little, if any, space or performance penalty for using many small ZSQL methods rather than one complicated one.
I'm sure that's true.
So, when should you use the fancier options? If you need to have rollback work, you may need to combine several statements into a single method. If you have a report that changes behavior depending on its input, maybe you want to use a complicated ZSQL method to grab the data. E.g. I have a form that accepts a min and a max part number and a min and max description (and several other min/max pairs). I want to sort depending on the first pair filled in. This is conveniently done with a complicated ZSQL method. I have not yet decided if it is wise to do so.
Natch, delete for example deletes from a main table and an xref table.
Rule of thumb: methods should seldom span more than 2 or 3 screenfuls. The major exception would be a method with an enormous number of parameters. Zope is best used with the KISS principle in mind. It is better to factor a problem into a largish number of very simple components than it is to use a small number of very complicated components. This is especially true when developing through the web, where you have smallish text windows and more primitive tools.
Yes, but Keep It Simple also means not having an unmanagable number of objects. If there was a way to organize objects by purpose without loosing the advantage of having them in the same folder or creating an ungodly number of folders, that'd be great. Ciao! -- Computers are useless. They can only give you answers. -- Pablo Picasso The Doctor What: Need I say more? http://docwhat.gerf.org/ docwhat@gerf.org KF6VNC
Howdy again.... I use a dtml-call to call a zsql object that does an insert. I don't specify ID because ID is auto-sequenced by the DB. How can I find out what the ID of that just inserted object is so I can do more operations on it? Related question: Why doesn't this work? <dtml-call expr="RESPONSE.redirect('index_html?message=Link+added&url_id='+url_id)"> Zope complains about a builtin... Ciao! -- "Always do right. This will gratify some people and astonish the rest." -- Mark Twain The Doctor What: A really hip dude http://docwhat.gerf.org/ docwhat@gerf.org KF6VNC
* The Doctor What (docwhat@gerf.org) [010419 21:02]:
Howdy again....
I use a dtml-call to call a zsql object that does an insert. I don't specify ID because ID is auto-sequenced by the DB.
How can I find out what the ID of that just inserted object is so I can do more operations on it?
Related question: Why doesn't this work? <dtml-call expr="RESPONSE.redirect('index_html?message=Link+added&url_id='+url_id)">
Zope complains about a builtin...
If figured it out in the end. It was actually a Postgres question: INSERT INTO URL (val_ProtocolID, Url, Name, Summary, Description, Created) VALUES ( <dtml-sqlvar protocol_id type=int>, <dtml-sqlvar url_part type=nb>, <dtml-sqlvar url_name type=nb>, <dtml-sqlvar url_summary type=nb>, <dtml-sqlvar url_description type=nb>, now() ) <dtml-var sql_delimiter> SELECT id as url_id FROM URL WHERE id = currval('url_id_seq') Ciao! -- Any programming language is at its best before it is implemented and used. The Doctor What: Un-Humble http://docwhat.gerf.org/ docwhat@gerf.org KF6VNC
If you want to be more on the safe side here's another way of doing it. # create random string like 208h28hd280D7ych987YD98N80e048N3C # or make that a param INSERT into table SET name='<dtml-var name>', lastname='208h28hd280D7ych987YD98N80e048N3C' <dtml-var sql_delimiter> SELECT id FROM table WHERE lastname='208h28hd280D7ych987YD98N80e048N3C' <dtml-var sql_delimiter> UPDATE table SET lastname='<dtml-var lastname>' WHERE lastname='208h28hd280D7ych987YD98N80e048N3C' Get it? Peter
-----Message d'origine----- De : zope-admin@zope.org [mailto:zope-admin@zope.org]De la part de The Doctor What Envoye : vendredi 20 avril 2001 05:48 A : zope@zope.org Cc : Jim Penny Objet : Re: [Zope] ZSQL - finding the id of what you just inserted?
* The Doctor What (docwhat@gerf.org) [010419 21:02]:
Howdy again....
I use a dtml-call to call a zsql object that does an insert. I don't specify ID because ID is auto-sequenced by the DB.
How can I find out what the ID of that just inserted object is so I can do more operations on it?
Related question: Why doesn't this work? <dtml-call expr="RESPONSE.redirect('index_html?message=Link+added&url_id='+url_id)">
Zope complains about a builtin...
If figured it out in the end. It was actually a Postgres question: INSERT INTO URL (val_ProtocolID, Url, Name, Summary, Description, Created) VALUES ( <dtml-sqlvar protocol_id type=int>, <dtml-sqlvar url_part type=nb>, <dtml-sqlvar url_name type=nb>, <dtml-sqlvar url_summary type=nb>, <dtml-sqlvar url_description type=nb>, now() ) <dtml-var sql_delimiter> SELECT id as url_id FROM URL WHERE id = currval('url_id_seq')
Ciao!
-- Any programming language is at its best before it is implemented and used.
The Doctor What: Un-Humble http://docwhat.gerf.org/ docwhat@gerf.org KF6VNC
_______________________________________________ Zope maillist - Zope@zope.org http://lists.zope.org/mailman/listinfo/zope ** No cross posts or HTML encoding! ** (Related lists - http://lists.zope.org/mailman/listinfo/zope-announce http://lists.zope.org/mailman/listinfo/zope-dev )
The Doctor What wrote:
Howdy again....
I use a dtml-call to call a zsql object that does an insert. I don't specify ID because ID is auto-sequenced by the DB.
How can I find out what the ID of that just inserted object is so I can do more operations on it?
I usually have my insert ZSQL-method return it insert into mytable(name) values('myname') ; select nextval('mytable_id_seq') ---------------- Hannu
participants (4)
-
Hannu Krosing -
Jim Penny -
Peter Bengtsson -
The Doctor What