* 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 + + + + +