[ZCM] [ZC] 2183/ 3 Comment "sqltest inconsistency"

Issue #2183 Update (Comment) "sqltest inconsistency"
 Status Pending, Zope/feature+solution medium
To followup, visit:
  http://www.zope.org/Collectors/Zope/2183

==============================================================
= Comment - Entry #3 by jhreis on Mar 15, 2007 12:07 pm

After reading the Zope Book with more attention, I realized that the sqltest tag is used for comparisons and not attributions. So, in the case of the null values , we have to use different operators for equality ( is ) and difference ( is not ). 
Follows a new diff with the suggested modifications:

132a133,138
>         if v is None:
>             try:
>                 return "%s %s %s" % (self.column, null_operators[self.op], 'null')
>             except KeyError:
>                 raise ValueError, 'Invalid operator for null value, <em>%s</em>' % name
>
200a207,208
>
> null_operators = { '=': 'is', '<>': 'is not' }
________________________________________
= Comment - Entry #2 by jhreis on Sep 4, 2006 6:55 pm

Sorry, my example are wrong. The correct is:

if called with 'abc="123", dof=None, ghi="789 this would result in:

update table
set (abc = '123', dof = null)
where ghi = '789'
________________________________________
= Request - Entry #1 by jhreis on Sep 4, 2006 4:31 pm


Uploaded:  "sqltest.pat"
 - http://www.zope.org/Collectors/Zope/2183/sqltest.pat/view
Some time ago Dieter Maurer changed the sqlvar behaviour to map Python None to SQL NULL for all types (issue 556).  I think that sqltest should have the same behavior, so parameters explicitly passed as None are mapped to SQL null.

This would bring consistency between sqlvar and sqltest and would allow to greater flexibility in the construction of zsql methods, like in this example:

update table
set <dtml-sqlgroup>
  <dtml-sqltest abc type="nb" optional><dtml-comma>
  <dtml-sqltest dof type="nb" optional>
</dtml-sqlgroup>
where ghi = <dtml-sqlvar ghi type="nb">

if called with 'abc="123", ghi="789 this would result in:

update table
set (abc = '123', dof =  null)
where ghi = '789'

==============================================================