Z SQL Method problems (now two confirmed bugs)
[Moving this to Zope-Dev] I've done some more investigation and determined there is a fundamental flaw in the way Z SQL Method parameters are handled. On Tue, 7 Aug 2001, Andy Dustman wrote:
I have a Z SQL Method (SELECT) with the following parameters:
HostId DomainId Name IP
and the following SQL:
SELECT HostId, DomainId, RecordType, Name, IP, Distance, Target, TTL, StartEndTime, Location, Updated FROM Hosts <dtml-sqlgroup where> <dtml-sqltest HostId column="HostId" type=int optional> <dtml-and> <dtml-sqltest DomainId column="DomainId" type=int optional> <dtml-and> <dtml-sqltest Name column="Name" type=nb optional> <dtml-and> <dtml-sqltest IP column="IP" type=int optional> </dtml-sqlgroup>
Note that all tested columns are optional. Using the Test tab, everything works correctly: If I leave off values, that portion isn't rendered.
Now... I have a DTML Method which performs:
<dtml-in SELECT mapping> ... </dtml-in>
This bombs unless all the parameters are specified in form data.
Now, for the reason it bombs: When called, a Z SQL Method ultimately gets information about parameters from the parse() function in Aquaduct.py. For each parameter parsed (and there are problems with the parsing, but that's another problem), it checks trys three things: 1) Get the parameter value from REQUEST. 2) Failing that: Assign the parameter it's default value, if it exists. 5^H3) Failing that: If the parameter is marked optional, ignore it. Here is the problem: There is no way for parse() to mark a parameter as optional. It simply is not tested for in any way. It also cannot get this information from the <dtml-sqltest ...> element, because it doesn't have access to it or the template. Solution: parse() does have the capability of finding a parameter type (for the above-mentioned reason), i.e. the parameters could be written: HostId:int DomainId:int Name:nb IP:int I propose also allowing :optional to follow the type, so that parse() can set this properly, i.e.: HostId:int:optional DomainId:int:optional Name:nb:optional IP:int:optional Note that currently default values take precedence over optional values (if it could find any). I'll prepare a patch that can do this if requested (DC/ZopeCorp decide this is a good solution) and it will also fix the parameter="value" parsing problem (broken in 2.4.0). -- Andy Dustman PGP: 0xC72F3F1D @ .net http://dustman.net/andy I'll give spammers one bite of the apple, but they'll have to guess which bite has the razor blade in it.
Andy Dustman wrote:
[snip]
Solution: parse() does have the capability of finding a parameter type (for the above-mentioned reason), i.e. the parameters could be written:
HostId:int DomainId:int Name:nb IP:int
I propose also allowing :optional to follow the type, so that parse() can set this properly, i.e.:
HostId:int:optional DomainId:int:optional Name:nb:optional IP:int:optional
Note that currently default values take precedence over optional values (if it could find any).
I'll prepare a patch that can do this if requested (DC/ZopeCorp decide this is a good solution) and it will also fix the parameter="value" parsing problem (broken in 2.4.0).
This sounds like a good solution. I have worked around this same issue many times myself. -- | Casey Duncan | Kaivo, Inc. | cduncan@kaivo.com `------------------>
Andy, Thanks very much for tracking this down! Do you think you can put it in the Collector? Andy Dustman wrote:
[Moving this to Zope-Dev] I've done some more investigation and determined there is a fundamental flaw in the way Z SQL Method parameters are handled.
On Tue, 7 Aug 2001, Andy Dustman wrote:
I have a Z SQL Method (SELECT) with the following parameters:
HostId DomainId Name IP
and the following SQL:
SELECT HostId, DomainId, RecordType, Name, IP, Distance, Target, TTL, StartEndTime, Location, Updated FROM Hosts <dtml-sqlgroup where> <dtml-sqltest HostId column="HostId" type=int optional> <dtml-and> <dtml-sqltest DomainId column="DomainId" type=int optional> <dtml-and> <dtml-sqltest Name column="Name" type=nb optional> <dtml-and> <dtml-sqltest IP column="IP" type=int optional> </dtml-sqlgroup>
Note that all tested columns are optional. Using the Test tab, everything works correctly: If I leave off values, that portion isn't rendered.
Now... I have a DTML Method which performs:
<dtml-in SELECT mapping> ... </dtml-in>
This bombs unless all the parameters are specified in form data.
Now, for the reason it bombs: When called, a Z SQL Method ultimately gets information about parameters from the parse() function in Aquaduct.py. For each parameter parsed (and there are problems with the parsing, but that's another problem), it checks trys three things:
1) Get the parameter value from REQUEST.
2) Failing that: Assign the parameter it's default value, if it exists.
5^H3) Failing that: If the parameter is marked optional, ignore it.
Here is the problem: There is no way for parse() to mark a parameter as optional. It simply is not tested for in any way. It also cannot get this information from the <dtml-sqltest ...> element, because it doesn't have access to it or the template.
Solution: parse() does have the capability of finding a parameter type (for the above-mentioned reason), i.e. the parameters could be written:
HostId:int DomainId:int Name:nb IP:int
I propose also allowing :optional to follow the type, so that parse() can set this properly, i.e.:
HostId:int:optional DomainId:int:optional Name:nb:optional IP:int:optional
Note that currently default values take precedence over optional values (if it could find any).
I'll prepare a patch that can do this if requested (DC/ZopeCorp decide this is a good solution) and it will also fix the parameter="value" parsing problem (broken in 2.4.0).
-- Andy Dustman PGP: 0xC72F3F1D @ .net http://dustman.net/andy I'll give spammers one bite of the apple, but they'll have to guess which bite has the razor blade in it.
_______________________________________________ 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 )
-- Chris McDonough Zope Corporation http://www.zope.org http://www.zope.com """ Killing hundreds of birds with thousands of stones """
On Thu, 9 Aug 2001, Chris McDonough wrote:
Thanks very much for tracking this down! Do you think you can put it in the Collector?
It is covered by bugs 2410 and 2495. The patch is relatively small so here it comes: --- Zope-2.4.0/lib/python/Shared/DC/ZRDB/Aqueduct.py.orig Thu Aug 9 15:55:39 2001 +++ Zope-2.4.0/lib/python/Shared/DC/ZRDB/Aqueduct.py Thu Aug 9 15:57:26 2001 @@ -341,22 +341,24 @@ def __len__(self): return len(self._data) +unparmre=re.compile( + r'([\000- ]*([^\000- ="]+))') +parmre=re.compile( + r'([\000- ]*([^\000- ="]+)=([^\000- ="]+))') +qparmre=re.compile( + r'([\000- ]*([^\000- ="]+)="([^"]*)")') + def parse(text, result=None, keys=None, - unparmre=re.compile( - r'([\000- ]*([^\000- ="]+))'), - parmre=re.compile( - r'([\000- ]*([^\000- ="]+)=([^\000- ="]+))'), - qparmre=re.compile( - r'([\000- ]*([^\000- ="]+)="([^"]*)")'), ): - if result is None: result = {} keys = [] + if not text or not text.strip(): return Args(result,keys) + __traceback_info__=text mo = parmre.match(text) @@ -382,13 +384,16 @@ value = {} l = len(mo.group(1)) else: - if not text or not text.strip(): return Args(result,keys) raise InvalidParameter, text lt=name.find(':') if lt > 0: - value['type']=name[lt+1:] + t=value['type']=name[lt+1:] name=name[:lt] + lt=t.find(':') + if lt > 0: + value['type']=t[:lt] + value[t[lt+1:]]=1 result[name]=value keys.append(name) -- Andy Dustman PGP: 0xC72F3F1D @ .net http://dustman.net/andy I'll give spammers one bite of the apple, but they'll have to guess which bite has the razor blade in it.
For extra credit, consider writing a proposal (and implementing, of course) to change SQL methods to be capable of handling positional and/or named parameters -- i.e. pass params as PARAMETERS and let the DRIVER figure out what to do. So, instead of SELECT * FROM emp WHERE empno = 7902; you could SELECT * FROM emp WHERE empno = :empno. The current implemention changes everything to a string before sending it to the driver, it cannot handle parameters. The numero-uno gotcha is the 6 or so different ways parameters can be passed. Still, if converting from DTML-ish notation, you could generate the proper statement AND parameter list at the same time. ----- Original Message ----- From: "Andy Dustman" <andy@dustman.net> To: <zope-dev@zope.org> Sent: Thursday, August 09, 2001 3:15 PM Subject: [Zope-dev] Z SQL Method problems (now two confirmed bugs)
[Moving this to Zope-Dev] I've done some more investigation and determined there is a fundamental flaw in the way Z SQL Method parameters are handled.
On Tue, 7 Aug 2001, Andy Dustman wrote:
I have a Z SQL Method (SELECT) with the following parameters:
HostId DomainId Name IP
and the following SQL:
SELECT HostId, DomainId, RecordType, Name, IP, Distance, Target, TTL, StartEndTime, Location, Updated FROM Hosts <dtml-sqlgroup where> <dtml-sqltest HostId column="HostId" type=int optional> <dtml-and> <dtml-sqltest DomainId column="DomainId" type=int optional> <dtml-and> <dtml-sqltest Name column="Name" type=nb optional> <dtml-and> <dtml-sqltest IP column="IP" type=int optional> </dtml-sqlgroup>
Note that all tested columns are optional. Using the Test tab, everything works correctly: If I leave off values, that portion isn't rendered.
Now... I have a DTML Method which performs:
<dtml-in SELECT mapping> ... </dtml-in>
This bombs unless all the parameters are specified in form data.
Now, for the reason it bombs: When called, a Z SQL Method ultimately gets information about parameters from the parse() function in Aquaduct.py. For each parameter parsed (and there are problems with the parsing, but that's another problem), it checks trys three things:
1) Get the parameter value from REQUEST.
2) Failing that: Assign the parameter it's default value, if it exists.
5^H3) Failing that: If the parameter is marked optional, ignore it.
Here is the problem: There is no way for parse() to mark a parameter as optional. It simply is not tested for in any way. It also cannot get this information from the <dtml-sqltest ...> element, because it doesn't have access to it or the template.
Solution: parse() does have the capability of finding a parameter type (for the above-mentioned reason), i.e. the parameters could be written:
HostId:int DomainId:int Name:nb IP:int
I propose also allowing :optional to follow the type, so that parse() can set this properly, i.e.:
HostId:int:optional DomainId:int:optional Name:nb:optional IP:int:optional
Note that currently default values take precedence over optional values (if it could find any).
I'll prepare a patch that can do this if requested (DC/ZopeCorp decide this is a good solution) and it will also fix the parameter="value" parsing problem (broken in 2.4.0).
-- Andy Dustman PGP: 0xC72F3F1D @ .net http://dustman.net/andy I'll give spammers one bite of the apple, but they'll have to guess which bite has the razor blade in it.
_______________________________________________ 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 )
On Thu, 9 Aug 2001, Matthew T. Kromer wrote:
For extra credit, consider writing a proposal (and implementing, of course) to change SQL methods to be capable of handling positional and/or named parameters -- i.e. pass params as PARAMETERS and let the DRIVER figure out what to do.
Like this?: http://classic.zope.org:8080/Collector/1867/view Not my patch, just came across it researching my problem. I haven't tested it at all, and since MySQL doesn't bind variables, not much reason to... One patch I would very, very much like to see make into Zope is: http://classic.zope.org:8080/Collector/1576/view I have a Product I am working on that is nearly intractable (or at least much less flexible) without this. In a nutshell, it adds <dtml-comma> as a legal element inside of <dtml-sqlgroup ...> blocks, analogously to <dtml-and> and <dtml-or>. Someone else had a patch that implemented <dtml-union>, which I don't personally have a use for, but it would be trivial to add to the one above (two lines). It seems like there are a lot of pending bugs in the Collector, at least if you search for SQL. -- Andy Dustman PGP: 0xC72F3F1D @ .net http://dustman.net/andy I'll give spammers one bite of the apple, but they'll have to guess which bite has the razor blade in it.
Andy Dustman wrote:
Like this?: http://classic.zope.org:8080/Collector/1867/view
...and Andy's other patches.. How's the CVS opening coming along? It sure would be nice to get Zope taking the other big Open Source advantage: lots of developers. ...especially when they're motivated and have great ideas like Andy :-) cheers, Chris
Like this?: http://classic.zope.org:8080/Collector/1867/view
...and Andy's other patches..
How's the CVS opening coming along?
It sure would be nice to get Zope taking the other big Open Source advantage: lots of developers.
...especially when they're motivated and have great ideas like Andy :-)
I totally agree. I know I've talked to a few people individually, but I should give a little more public status report. Ken (Manheimer) has done an outstanding job getting all of the technical infrastructure running like a champ, so that part is ready. Right now we are only waiting for the lawyering to be finished (producing the necessary legal-butt-covering-wet-signature document). I don't have an exact ETA on this, but it should be Real Soon Now - I'll be letting people know as soon as I know :) Brian Lloyd brian@zope.com Zope Corporation www.zope.com
participants (6)
-
Andy Dustman -
Brian Lloyd -
Casey Duncan -
Chris McDonough -
Chris Withers -
Matthew T. Kromer