X-Git-Url: http://git.onelab.eu/?a=blobdiff_plain;f=PLC%2FFilter.py;h=fe00c2bd6e2e834f280230fa66ea86b661c34bc0;hb=0c1abf0ac34f0d3d13001bc7e03b173de1396cdd;hp=5d0d22dc1da53b3766249349e48f1ef55a014c38;hpb=fe81b2c91b436b1882f63023413c7f51b29538ed;p=plcapi.git diff --git a/PLC/Filter.py b/PLC/Filter.py index 5d0d22d..fe00c2b 100644 --- a/PLC/Filter.py +++ b/PLC/Filter.py @@ -1,5 +1,6 @@ -# $Id$ -# $URL$ +# +# Thierry Parmentelat - INRIA +# from types import StringTypes try: set @@ -16,8 +17,8 @@ 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. + Special features provide support for negation, upper and lower bounds, + sorting and clipping and more... fields should be a dictionary of field names and types. @@ -29,30 +30,12 @@ class Filter(Parameter, dict): filter should be a dictionary of field names and values - representing the criteria for filtering. + 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 as follows: - * if the dictionnary has the '-AND' or the '-OR' key, this is chosen - * otherwise, the join_with argument, as provided to the sql method below, - is expected to hold the 'AND' or 'OR' string - this argument defaults to 'AND' and in most of the code, this default applies - as the join_with argument is left unspecified - - Special features: - * a field starting with '&' or '|' should refer to a sequence type - the semantic 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' } - * a field starting with the ~ character means negation. example : filter = { '~peer_id' : None } @@ -63,32 +46,47 @@ class Filter(Parameter, dict): 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 + * 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' } + 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 + * '-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 + * '-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' , + 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 + 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 @@ -110,7 +108,7 @@ 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 = dict ( [ ( field, Mixed (expected, [expected])) + self.fields = dict ( [ ( field, Mixed (expected, [expected])) for (field,expected) in fields.iteritems() ] ) # Null filter means no filter @@ -121,10 +119,10 @@ class Filter(Parameter, dict): Returns a SQL conditional that represents this filter. """ - if self.has_key('-AND'): + if self.has_key('-AND'): del self['-AND'] join_with='AND' - if self.has_key('-OR'): + if self.has_key('-OR'): del self['-OR'] join_with='OR' @@ -138,20 +136,20 @@ class Filter(Parameter, dict): else: assert join_with in ("AND", "OR") - # init + # init sorts = [] clips = [] for field, value in self.iteritems(): - # handle negation, numeric comparisons - # simple, 1-depth only mechanism + # handle negation, numeric comparisons + # simple, 1-depth only mechanism - modifiers={'~' : False, - '<' : False, '>' : False, - '[' : False, ']' : False, + modifiers={'~' : False, + '<' : False, '>' : False, + '[' : False, ']' : False, '-' : False, '&' : False, '|' : False, - } + } def check_modifiers(field): if field[0] in modifiers.keys(): modifiers[field[0]] = True @@ -176,7 +174,7 @@ class Filter(Parameter, dict): value = "NULL" elif isinstance(value, StringTypes) and \ (value.find("*") > -1 or value.find("%") > -1): - operator = "LIKE" + 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 @@ -199,7 +197,7 @@ class Filter(Parameter, dict): 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 + # 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['|']: @@ -211,42 +209,31 @@ class Filter(Parameter, dict): value = "FALSE" clause = "%s %s %s" % (field, operator, value) else: - value = map(str, map(api.db.quote, value)) - do_join = True vals = {} for val in value: base_op, val = get_op_and_val(val) - if base_op != '=': - do_join = False if base_op in vals: vals[base_op].append(val) else: vals[base_op] = [val] - if do_join: - if modifiers['&']: - operator = "@>" - value = "ARRAY[%s]" % ", ".join(value) - elif modifiers['|']: - operator = "&&" - value = "ARRAY[%s]" % ", ".join(value) - else: - operator = "IN" - value = "(%s)" % ", ".join(value) - clause = "%s %s %s" % (field, operator, value) - else: - # We need something more complex - subclauses = [] - for operator in vals.keys(): - if operator == '=': - subclauses.append("(%s IN (%s))" % (field, ",".join(vals[operator]))) - elif operator == 'IS': - subclauses.append("(%s IS NULL)" % field) + 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: - for value in vals[operator]: - subclauses.append("(%s %s %s)" % (field, operator, value)) - clause = "(" + " OR ".join(subclauses) + ")" + 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 = get_op_and_val(value) + clause = "%s %s %s" % (field, operator, value) if modifiers['~']: