[Zope-dev] Patches to add range / kw searching, etc
Bradley McLean
brad@bradm.ne.mediaone.net
Thu, 18 Nov 1999 18:20:48 -0500
* Bill Anderson (bill.anderson@libc.org) [991118 00:38]:
>
> Personally, I would post a patch to the collector, and to this list (or
> if patch == large_file: procide URL for download), that way you get DC
> looking over it, as well as anyone of the rest of us that might like to
> try it out.
Thanks for the suggestion, Bill. Here it is:
--- sqlgroup.py.orig Tue Mar 9 19:15:44 1999
+++ sqlgroup.py Thu Nov 18 17:52:25 1999
@@ -150,7 +150,8 @@
import sys
class SQLGroup:
- blockContinuations='and','or'
+ # Brad McLean 11/17/99: Allow a new operator "andor"
+ blockContinuations='and','or','andor'
name='sqlgroup'
required=None
where=None
@@ -172,7 +173,12 @@
__traceback_info__=tname
s=strip(section(None, md))
if s:
- if r: r.append(tname)
+ if r:
+ # Brad McLean 11/17/99: If it's andor tag, use the
+ # Value of the arguments as the sqljoiner
+ if tname == 'andor':
+ r.append(md[args])
+ else: r.append(tname)
r.append("%s\n" % s)
if r:
--- sqltest.py.orig Thu Aug 26 13:59:36 1999
+++ sqltest.py Thu Nov 18 17:52:25 1999
@@ -126,6 +126,41 @@
with the appropriate boolean operator, as indicated by use of an
'and' or 'or' tag, otherwise, no text is inserted.
+ Extensions made 11/17/99 by Brad McLean:
+
+ There is an 'andor' tag which takes a parameter:
+ <!--#andor operator_variable--> or <dtml-andor operator_variable>
+ This permits having the user interface present something like:
+ <select name="operator_variable" size=1>
+ <option value="or" SELECTED>Or</option>
+ <option value="and">And</option>
+ <option value="and not">And Not</option>
+ </select>
+
+
+ Further, sqltest has been extended with 'expr' options:
+ 'expr' defaults to 'eq'
+ other values are 'ge','le','lt','gt','rng','kw'
+
+ For 'rng' (range), if a single parameter is given, it devolves to 'eq'.
+ If two values are provided, then a field test ensuring that the value
+ lies between the two values (inclusive) is generated. Simply repeat
+ the same 'name' attribute on two input elements on the calling form;
+ the first will be taken to be the lower element
+
+ For 'kw' (Keyword), each word provided is searched for. This could
+ use some further work to allow 'and' semantics as well as 'or', and
+ phrases would be cool too, but that's future.
+
+ This code works well with MySQL. I think it should work with most
+ other SQL databases, but I'm not sure.
+
+ Examples:
+ <dtml-sqltest C.TimeID column=TimeID type=nb expr=eq optional>
+ <dtml-sqltest C.SizeID column=SizeID type=nb multiple expr=rng optional>
+ <dtml-sqltest C.Notes column=Notes type=nb multiple expr=kw optional>
+
+
'''
__rcs_id__='$Id: sqltest.py,v 1.10 1999/08/26 17:59:36 jim Exp $'
@@ -151,8 +186,9 @@
optional=multiple=None
def __init__(self, args):
+ # Brad McLean 11/17/99:
args = parse_params(args, name='', type=None, column=None,
- multiple=1, optional=1)
+ multiple=1, optional=1, expr='')
self.__name__ = name_param(args,'sqlvar')
has_key=args.has_key
if not has_key('type'):
@@ -164,6 +200,9 @@
if has_key('multiple'): self.multiple=args['multiple']
if has_key('column'): self.column=args['column']
else: self.column=self.__name__
+ # Brad McLean 11/17/99:
+ if has_key('expr'): self.expr=args['expr']
+ else: self.expr = "eq"
def render(self, md):
name=self.__name__
@@ -201,7 +240,9 @@
'Invalid floating-point value for <em>%s</em>' % name)
else:
v=str(v)
- v=md.getitem('sql_quote__',0)(v)
+ # Brad McLean 11/17/99:
+ if self.expr != 'kw':
+ v=md.getitem('sql_quote__',0)(v)
#if find(v,"\'") >= 0: v=join(split(v,"\'"),"''")
#v="'%s'" % v
@@ -212,11 +253,55 @@
raise 'Missing Input', (
'No input was provided for <em>%s</em>' % name)
- if len(vs) > 1:
- vs=join(map(str,vs),', ')
- return "%s in (%s)" % (self.column,vs)
- return "%s=%s" % (self.column,vs[0])
+ # Brad McLean 11/17/99: Major semantics changes below:
+ if len(vs) > 1 or self.expr == 'kw':
+ if self.expr == 'eq':
+ vs=join(map(str,vs),', ')
+ return "%s in (%s)" % (self.column,vs)
+ # Range processing: Must have just two values. Future:
+ # might want a both inclusive and exclusive options.
+ elif self.expr == 'rng' and len(vs) == 2:
+ return "(%s >= %s and %s <= %s)" % (self.column,vs[0],self.column,vs[1])
+ # Keyword processing: Split each word into a separate SQL
+ # clause. This might be rather database specific; perhaps it
+ # can be parameterized or factored into a helper method somehow.
+ # Future options might include other operators than 'or', and
+ # possibly a parser smart enough to handle phrases, etc.
+ elif self.expr == 'kw':
+ result = ["("]
+ for i in vs:
+ for w in split(i):
+ result.append("locate(lower(%s),lower(%s))>0"
+ % (md.getitem('sql_quote__',0)(w),self.column))
+ result.append(" or ")
+ result[-1] = ")"
+ return join(result)
+ else:
+ raise ValueError, (
+ 'Wrong Value count for type for <em>%s</em>' % name)
+ # Single value to a range devolves to a simple eq
+ # Should there be a table of these per database in case of differing
+ # operators?
+ if self.expr == 'eq' or self.expr == 'rng':
+ return "%s=%s" % (self.column,vs[0])
+ elif self.expr == 'lt':
+ return "%s<%s" % (self.column,vs[0])
+ elif self.expr == 'gt':
+ return "%s>%s" % (self.column,vs[0])
+ elif self.expr == 'le':
+ return "%s<=%s" % (self.column,vs[0])
+ elif self.expr == 'ge':
+ return "%s>=%s" % (self.column,vs[0])
+ elif self.expr == 'ne':
+ return "%s!=%s" % (self.column,vs[0])
+ raise ValueError, (
+ 'Unknown expr type for <em>%s</em>' % name)
__call__=render
valid_type={'int':1, 'float':1, 'string':1, 'nb': 1}.has_key
+
+
+
+
+