On Mon, Mar 04, 2002 at 03:32:24PM -0500, Thomas B. Passin wrote:

[hans]

...

well, i could instantiate a bunch of zsql methods, each with its own query and then call them from my products methods. but how could i obviate the need to have these zsql methods and query directly? Or is it just not worth it? thx hans

Well, the extreme and simplest case is to have one zsql method that takes a single parameter called, say, "sql":

&dtml-sql;

The sql parameter would contain the entire query, that is, an entire select statement or insert statement or whatever.

This is easy and flexible, but has no security at all. If someone posted you a DELETE statement, you might end up very unhappy, for example. Still it is an easy approach to use when you are tuning up a query, as long as you don't let it loose outside your system.

The next level up from this is to have your page, or a method called by the page, construct a query statement using values from a form, then hand the query to your friendly general zsql method. This is quite a bit more more secure, but you still need to make sure it's going to be safe.

A next step up in security is to make sure that any query that can be constructed and passed to the zsql method can only access non-updateable views.

There's a tradeoff between having highly specialized zsql methods, which could be the most secure but can lead to you having to maintain many individual methods, and the completely general approach I've outlined above where you only need one method but have to manage the security issues.

These parameterized ZSQL methods are generally a bad idea. They occur to everyone at some point early in their Zope career. I know they did to me, I wrote a howto on it. But, they are almost always a bad idea. Mind you, not only is there a security tradeoff, but there are also managability, testability, and correctness tradeoffs. If you use concrete zsql methods, then you have only to examine the method to determine what arguments are needed, and often you can directly read what is being returned, modified, or deleted. That is, understanding the database interactions of the code in a folder is often as simple as looking at the ZSQL methods in the folder. If you use one of the "statement is a parameter" forms, then you have to read (at least) all the code in the folder, everything, every dtml method, pythonscript, external method, etc. to determine the interactions with the database. Worse, you learn about mistakes too late. With concrete ZSQL methods you can use the Test Tab to make sure that the method does what it is supposed to do. You are reasonably confident that the method does what it should, no more and no less, before you ever let it loose. With "statement is a parameter" code, you don't learn that there is a problem until run time. My current belief is that you should never use any ZSQL method with anything more complicated than a sqlgroup in it, and that you want to be sparing with sqlgroups. One other thing -- keep your ZSQL methods close to the folder they are accessed from. Only very common queries should be closer to the root. I suspect that in many, if not most, cases, the set of databases that you need to work with in a single folder is pretty small. I think that this is due to programmer forethought, that is, that you naturally design your databases and your folder structure to be compatible. Now, if you are operating with only one, two, or three tables per folder, then you probably need no more than a few insert, delete, select, or updates per table. This translates to four to ten ZSQL methods per table used. If you have only three tables you are working with in a paritcular folder, this is just not all that onerous. Jim Penny

Cheers,

Tom P

hans wrote:

...

well, i could instantiate a bunch of zsql methods, each with its own query and then call them from my products methods. but how could i obviate the need to have these zsql methods and query directly? Or is it just not worth it?

This is what I've done. However, it's a hack in the sense I'm relying on the current SQL() class implementation and am using Zope-2.3.3. Still, I've been quite happy with the results and does what you've been looking for. This may have to be cleaned up a little for later versions and would be curious of any updates. Hopefully one of the Zope developers will make something like this part of the Zope API. Enjoy, Albert ---------------------------------------------------------------------- # -*- mode: python; -*- import Globals import OFS import StringIO from Products.ZSQLMethods.SQL import SQL # given a connection_id string, the ZSQL template, variable list, and # definitions, execute the ZSQL statement. Return a ZSQL Result object. # Example: # # expresion = '''SELECT FirstName,LastName FROM Users # WHERE Users.Email=<dtml-sqlvar email type="string">''' # result = query(self,expression,"email",email="foo@bar.com") # # This does not store a ZSQL object in the ZODB, so that ZODB remains # unchange. IMHO, this provides the most flexibility, allowing us to # create an arbitrary number of ZSQL statements from python code, # without being tied down to the traditional point&click editing. def query(self,connection_id,template,vars="",**keywords): id = "__custom_query" sql = SQL(id, id, connection_id, vars, template) # normally if we create a ZSQL method in zope (or add sql instance # to self object), the sql instance would hold the ZSQL DA object. # So we need to manually set it. # # note that we don't store the sql object into the zope object, as # the transaction gets stored in the Data.fs. Even deleting later # makes it worse, as the "undo" records gets updated setattr(sql,sql.connection_id, getattr(self,sql.connection_id)) if keywords.has_key(MAX_ROWS): # DA parses max_rows and saves it in max_rows_ sql.max_rows=sql.max_rows_=keywords[MAX_ROWS] else: sql.max_rows=sql.max_rows_=1000 results = sql(keywords) return results