X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=PLC%2FFilter.py;h=4e950f1e1a495aa51cf04624b5df6aa5dbf26055;hb=refs%2Fheads%2Fplanetlab-4_0-branch;hp=c20fd9c5512ed14219afc09401ee6c835cb3bbb0;hpb=5d80c8d9c8016143aaf74dfd3a7e0561f7538aad;p=plcapi.git diff --git a/PLC/Filter.py b/PLC/Filter.py index c20fd9c5..4e950f1e 100644 --- a/PLC/Filter.py +++ b/PLC/Filter.py @@ -14,34 +14,56 @@ class Filter(Parameter, dict): """ A type of parameter that represents a filter on one or more columns of a database table. + Special features provide support for negation, upper and lower bounds, + as well as sorting and clipping. - field should be a dictionary of field names and types, e.g. - - {'node_id': Parameter(int, "Node identifier"), - 'hostname': Parameter(int, "Fully qualified hostname", max = 255), - ...} + fields should be a dictionary of field names and types Only filters on non-sequence type fields are supported. + example : fields = {'node_id': Parameter(int, "Node identifier"), + 'hostname': Parameter(int, "Fully qualified hostname", max = 255), + ...} + filter should be a dictionary of field names and values - representing an intersection (if join_with is AND) or union (if - join_with is OR) filter. If a value is a sequence type, then it - should represent a list of possible values for that field. + representing the criteria for filtering. + example : filter = { 'hostname' : '*.edu' , site_id : [34,54] } + Whether the filter represents an intersection (AND) or a union (OR) + of these criteria is determined by the join_with argument + provided to the sql method below + + Special features: - Special forms: * a field starting with the ~ character means negation. - example : { '~peer_id' : None } + example : filter = { '~peer_id' : None } + * a field starting with < [ ] or > means lower than or greater than < > uses strict comparison [ ] is for using <= or >= instead - example : { '>time' : 1178531418 } - example : { ']event_id' : 2305 } - * a field starting with [ or ] means older than or more recent than - the associated value should be a given unix timestamp + example : filter = { ']event_id' : 2305 } + example : filter = { '>time' : 1178531418 } + in this example the integer value denotes a unix timestamp + + * if a value is a sequence type, then it should represent + a list of possible values for that field + example : filter = { 'node_id' : [12,34,56] } + * a (string) value containing either a * or a % character is treated as a (sql) pattern; * are replaced with % that is the SQL wildcard character. - example : { 'hostname' : '*.jp' } + example : filter = { 'hostname' : '*.jp' } + + * fields starting with - are special and relate to row selection, i.e. sorting and clipping + * '-SORT' : a field name, or an ordered list of field names that are used for sorting + * these fields may start with + (default) or - for denoting increasing or decreasing order + example : filter = { '-SORT' : [ '+node_id', '-hostname' ] } + * '-OFFSET' : the number of first rows to be ommitted + * '-LIMIT' : the amount of rows to be returned + example : filter = { '-OFFSET' : 100, 'LIMIT':25} + + A realistic example would read + GetNodes ( { 'hostname' : '*.edu' , '-SORT' : 'hostname' , '-OFFSET' : 30 , '-LIMIT' : 25 } ) + and that would return nodes matching '*.edu' in alphabetical order from 31th to 55th """ def __init__(self, fields = {}, filter = {}, doc = "Attribute filter"): @@ -86,6 +108,10 @@ class Filter(Parameter, dict): else: assert join_with in ("AND", "OR") + # init + sorts = [] + clips = [] + for field, value in self.iteritems(): # handle negation, numeric comparisons # simple, 1-depth only mechanism @@ -93,6 +119,7 @@ class Filter(Parameter, dict): modifiers={'~' : False, '<' : False, '>' : False, '[' : False, ']' : False, + '-' : False, } for char in modifiers.keys(): @@ -101,45 +128,76 @@ class Filter(Parameter, dict): field = field[1:] break - if field not in self.fields: -# print 'current fields',self.fields - raise PLCInvalidArgument, "Invalid filter field '%s'" % field - - if isinstance(value, (list, tuple, set)): - # Turn empty list into (NULL) instead of invalid () - if not value: - value = [None] - - operator = "IN" - value = map(str, map(api.db.quote, value)) - value = "(%s)" % ", ".join(value) + # filter on fields + if not modifiers['-']: + if field not in self.fields: + raise PLCInvalidArgument, "Invalid filter field '%s'" % field + + if isinstance(value, (list, tuple, set)): + # Turn empty list into (NULL) instead of invalid () + if not value: + value = [None] + + operator = "IN" + value = map(str, map(api.db.quote, value)) + value = "(%s)" % ", ".join(value) + else: + if value is None: + operator = "IS" + value = "NULL" + elif isinstance(value, StringTypes) and \ + (value.find("*") > -1 or value.find("%") > -1): + operator = "LIKE" + value = str(api.db.quote(value.replace("*", "%"))) + else: + operator = "=" + if modifiers['<']: + operator='<' + if modifiers['>']: + operator='>' + if modifiers['[']: + operator='<=' + if modifiers[']']: + operator='>=' + else: + value = str(api.db.quote(value)) + + clause = "%s %s %s" % (field, operator, value) + + if modifiers['~']: + clause = " ( NOT %s ) " % (clause) + + conditionals.append(clause) + # sorting and clipping else: - if value is None: - operator = "IS" - value = "NULL" - elif isinstance(value, StringTypes) and \ - (value.find("*") > -1 or value.find("%") > -1): - operator = "LIKE" - value = str(api.db.quote(value.replace("*", "%"))) - else: - operator = "=" - if modifiers['<']: - operator='<' - if modifiers['>']: - operator='>' - if modifiers['[']: - operator='<=' - if modifiers[']']: - operator='>=' - else: - value = str(api.db.quote(value)) - - clause = "%s %s %s" % (field, operator, value) - - if modifiers['~']: - clause = " ( NOT %s ) " % (clause) - - conditionals.append(clause) - -# print 'sql=',(" %s " % join_with).join(conditionals) - return (" %s " % join_with).join(conditionals) + if field not in ('SORT','OFFSET','LIMIT'): + raise PLCInvalidArgument, "Invalid filter, unknown sort and clip field %r"%field + # sorting + if field == 'SORT': + if not isinstance(value,(list,tuple,set)): + value=[value] + for field in value: + order = 'ASC' + if field[0] == '+': + field = field[1:] + elif field[0] == '-': + field = field[1:] + order = 'DESC' + if field not in self.fields: + raise PLCInvalidArgument, "Invalid field %r in SORT filter"%field + sorts.append("%s %s"%(field,order)) + # clipping + elif field == 'OFFSET': + clips.append("OFFSET %d"%value) + # clipping continued + elif field == 'LIMIT' : + clips.append("LIMIT %d"%value) + + where_part = (" %s " % join_with).join(conditionals) + clip_part = "" + if sorts: + clip_part += " ORDER BY " + ",".join(sorts) + if clips: + clip_part += " " + " ".join(clips) +# print 'where_part=',where_part,'clip_part',clip_part + return (where_part,clip_part)