X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=PLC%2FFilter.py;h=d3a17b4e105cdd33208a087ddedac1787fbc3a23;hb=e70e20fdbececafef842ec7b330fd48db42e614e;hp=a7c14ce5620a03344f34a18402d22cf471595d29;hpb=d795047a331bc305f6806fc0f0049e3b1b4c0491;p=plcapi.git diff --git a/PLC/Filter.py b/PLC/Filter.py index a7c14ce..d3a17b4 100644 --- a/PLC/Filter.py +++ b/PLC/Filter.py @@ -1,24 +1,101 @@ +# +# Thierry Parmentelat - INRIA +# +from types import StringTypes +import time + from PLC.Faults import * from PLC.Parameter import Parameter, Mixed, python_type +from PLC.Logger import logger class Filter(Parameter, dict): """ A type of parameter that represents a filter on one or more - columns of a database table. fields should be a dictionary of - field names and types, e.g. + columns of a database table. + Special features provide support for negation, upper and lower bounds, + sorting and clipping and more... + - {'node_id': Parameter(int, "Node identifier"), - 'hostname': Parameter(int, "Fully qualified hostname", max = 255), - ...} + fields should be a dictionary of field names and types. + As of PLCAPI-4.3-26, we provide support for filtering on + sequence types as well, with the special '&' and '|' modifiers. + example : fields = {'node_id': Parameter(int, "Node identifier"), + 'hostname': Parameter(int, "Fully qualified hostname", max = 255), + ...} - Only filters on non-sequence type fields are supported. 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] } + + + Special features: + + * a field starting with the ~ character means negation. + 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 : 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 : filter = { 'hostname' : '*.jp' } + + * a field starting with '&' or '|' should refer to a sequence type + the semantics is then that the object value (expected to be a list) + should contain all (&) or any (|) value specified in the corresponding + filter value. See other examples below. + example : filter = { '|role_ids' : [ 20, 40 ] } + example : filter = { '|roles' : ['tech', 'pi'] } + example : filter = { '&roles' : ['admin', 'tech'] } + example : filter = { '&roles' : 'tech' } + + * the filter's keys starting with '-' are special and relate to 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} + + * similarly the two special keys below allow to change the semantics of multi-keys filters + * '-AND' : select rows that match ALL the criteria (default) + * '-OR' : select rows that match ANY criteria + The value attached to these keys is ignored. + Please note however that because a Filter is a dict, you cannot provide two criteria on a given key. + + + Here are a few realistic examples + + GetNodes ( { 'node_type' : 'regular' , 'hostname' : '*.edu' , + '-SORT' : 'hostname' , '-OFFSET' : 30 , '-LIMIT' : 25 } ) + would return regular (usual) nodes matching '*.edu' in alphabetical order from 31th to 55th + + GetNodes ( { '~peer_id' : None } ) + returns the foreign nodes - that have an integer peer_id + + GetPersons ( { '|role_ids' : [ 20 , 40] } ) + would return all persons that have either pi (20) or tech (40) roles + + GetPersons ( { '&role_ids' : 10 } ) + GetPersons ( { '&role_ids' : 10 } ) + GetPersons ( { '|role_ids' : [ 10 ] } ) + GetPersons ( { '|role_ids' : [ 10 ] } ) + all 4 forms are equivalent and would return all admin users in the system """ + debug=False +# debug=True + def __init__(self, fields = {}, filter = {}, doc = "Attribute filter"): # Store the filter in our dict instance dict.__init__(self, filter) @@ -26,15 +103,8 @@ class Filter(Parameter, dict): # Declare ourselves as a type of parameter that can take # either a value or a list of values for each of the specified # fields. - self.fields = {} - - for field, expected in fields.iteritems(): - # Cannot filter on sequences - if python_type(expected) in (list, tuple, set): - continue - - # Accept either a value or a list of values of the specified type - self.fields[field] = Mixed(expected, [expected]) + self.fields = dict ( [ ( field, Mixed (expected, [expected])) + for (field,expected) in fields.iteritems() ] ) # Null filter means no filter Parameter.__init__(self, self.fields, doc = doc, nullok = True) @@ -44,6 +114,15 @@ class Filter(Parameter, dict): Returns a SQL conditional that represents this filter. """ + if self.has_key('-AND'): + del self['-AND'] + join_with='AND' + if self.has_key('-OR'): + del self['-OR'] + join_with='OR' + + self.join_with=join_with + # So that we always return something if join_with == "AND": conditionals = ["True"] @@ -52,26 +131,142 @@ class Filter(Parameter, dict): else: assert join_with in ("AND", "OR") + # init + sorts = [] + clips = [] + for field, value in self.iteritems(): - if field not in self.fields: - raise PLCInvalidArgument, "Invalid filter field '%s'" % field + # handle negation, numeric comparisons + # simple, 1-depth only mechanism - if isinstance(value, (list, tuple, set)): - # Turn empty list into (NULL) instead of invalid () - if not value: - value = [None] + modifiers={'~' : False, + '<' : False, '>' : False, + '[' : False, ']' : False, + '-' : False, + '&' : False, '|' : False, + } + def check_modifiers(field): + if field[0] in modifiers.keys(): + modifiers[field[0]] = True + field = field[1:] + return check_modifiers(field) + return field + field = check_modifiers(field) - operator = "IN" - value = map(str, map(api.db.quote, value)) - value = "(%s)" % ", ".join(value) - else: - if value is None: - operator = "IS" - value = "NULL" + # filter on fields + if not modifiers['-']: + if field not in self.fields: + raise PLCInvalidArgument, "Invalid filter field '%s'" % field + + # handling array fileds always as compound values + if modifiers['&'] or modifiers['|']: + if not isinstance(value, (list, tuple, set)): + value = [value,] + + def get_op_and_val(value): + if value is None: + operator = "IS" + value = "NULL" + elif isinstance(value, StringTypes) and \ + (value.find("*") > -1 or value.find("%") > -1): + operator = "ILIKE" + # insert *** in pattern instead of either * or % + # we dont use % as requests are likely to %-expansion later on + # actual replacement to % done in PostgreSQL.py + value = value.replace ('*','***') + value = value.replace ('%','***') + value = str(api.db.quote(value)) + else: + operator = "=" + if modifiers['<']: + operator='<' + if modifiers['>']: + operator='>' + if modifiers['[']: + operator='<=' + if modifiers[']']: + operator='>=' + value = str(api.db.quote(value)) + return (operator, value) + + if isinstance(value, (list, tuple, set)): + # handling filters like '~slice_id':[] + # this should return true, as it's the opposite of 'slice_id':[] which is false + # prior to this fix, 'slice_id':[] would have returned ``slice_id IN (NULL) '' which is unknown + # so it worked by coincidence, but the negation '~slice_ids':[] would return false too + if not value: + if modifiers['&'] or modifiers['|']: + operator = "=" + value = "'{}'" + else: + field="" + operator="" + value = "FALSE" + clause = "%s %s %s" % (field, operator, value) + else: + vals = {} + for val in value: + base_op, val = get_op_and_val(val) + if base_op in vals: + vals[base_op].append(val) + else: + vals[base_op] = [val] + subclauses = [] + for operator in vals.keys(): + if operator == '=': + if modifiers['&']: + subclauses.append("(%s @> ARRAY[%s])" % (field, ",".join(vals[operator]))) + elif modifiers['|']: + subclauses.append("(%s && ARRAY[%s])" % (field, ",".join(vals[operator]))) + else: + subclauses.append("(%s IN (%s))" % (field, ",".join(vals[operator]))) + elif operator == 'IS': + subclauses.append("(%s IS NULL)" % field) + else: + for value in vals[operator]: + subclauses.append("(%s %s %s)" % (field, operator, value)) + clause = "(" + " OR ".join(subclauses) + ")" else: - operator = "=" - value = str(api.db.quote(value)) + operator, value = get_op_and_val(value) - conditionals.append("%s %s %s" % (field, operator, value)) + clause = "%s %s %s" % (field, operator, value) + + if modifiers['~']: + clause = " ( NOT %s ) " % (clause) + + conditionals.append(clause) + # sorting and clipping + else: + 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) - return (" %s " % join_with).join(conditionals) + where_part = (" %s " % join_with).join(conditionals) + clip_part = "" + if sorts: + clip_part += " ORDER BY " + ",".join(sorts) + if clips: + clip_part += " " + " ".join(clips) + if Filter.debug: + logger.debug('Filter.sql: where_part={} - clip_part={}' + .format(where_part, clip_part)) + return where_part, clip_part