2 # Thierry Parmentelat - INRIA
4 from types import StringTypes
13 from PLC.Faults import *
14 from PLC.Parameter import Parameter, Mixed, python_type
16 class Filter(Parameter, dict):
18 A type of parameter that represents a filter on one or more
19 columns of a database table.
20 Special features provide support for negation, upper and lower bounds,
21 as well as sorting and clipping.
24 fields should be a dictionary of field names and types.
25 As of PLCAPI-4.3-26, we provide support for filtering on
26 sequence types as well, with the special '&' and '|' modifiers.
27 example : fields = {'node_id': Parameter(int, "Node identifier"),
28 'hostname': Parameter(int, "Fully qualified hostname", max = 255),
32 filter should be a dictionary of field names and values
33 representing the criteria for filtering.
34 example : filter = { 'hostname' : '*.edu' , site_id : [34,54] }
37 Whether the filter represents an intersection (AND) or a union (OR)
38 of these criteria is determined as follows:
39 * if the dictionnary has the '-AND' or the '-OR' key, this is chosen
40 * otherwise, the join_with argument, as provided to the sql method below,
41 is expected to hold the 'AND' or 'OR' string
42 this argument defaults to 'AND' and in most of the code, this default applies
43 as the join_with argument is left unspecified
48 * a field starting with '&' or '|' should refer to a sequence type
49 the semantic is then that the object value (expected to be a list)
50 should contain all (&) or any (|) value specified in the corresponding
51 filter value. See other examples below.
52 example : filter = { '|role_ids' : [ 20, 40 ] }
53 example : filter = { '|roles' : ['tech', 'pi'] }
54 example : filter = { '&roles' : ['admin', 'tech'] }
55 example : filter = { '&roles' : 'tech' }
57 * a field starting with the ~ character means negation.
58 example : filter = { '~peer_id' : None }
60 * a field starting with < [ ] or > means lower than or greater than
61 < > uses strict comparison
62 [ ] is for using <= or >= instead
63 example : filter = { ']event_id' : 2305 }
64 example : filter = { '>time' : 1178531418 }
65 in this example the integer value denotes a unix timestamp
67 * if a value is a sequence type, then it should represent
68 a list of possible values for that field
69 example : filter = { 'node_id' : [12,34,56] }
71 * a (string) value containing either a * or a % character is
72 treated as a (sql) pattern; * are replaced with % that is the
73 SQL wildcard character.
74 example : filter = { 'hostname' : '*.jp' }
76 * the filter's keys starting with '-' are special and relate to sorting and clipping
77 * '-SORT' : a field name, or an ordered list of field names that are used for sorting
78 these fields may start with + (default) or - for denoting increasing or decreasing order
79 example : filter = { '-SORT' : [ '+node_id', '-hostname' ] }
80 * '-OFFSET' : the number of first rows to be ommitted
81 * '-LIMIT' : the amount of rows to be returned
82 example : filter = { '-OFFSET' : 100, '-LIMIT':25}
85 Here are a few realistic examples
87 GetNodes ( { 'node_type' : 'regular' , 'hostname' : '*.edu' ,
88 '-SORT' : 'hostname' , '-OFFSET' : 30 , '-LIMIT' : 25 } )
89 would return regular (usual) nodes matching '*.edu' in alphabetical order from 31th to 55th
91 GetNodes ( { '~peer_id' : None } )
92 returns the foreign nodes - that have an integer peer_id
94 GetPersons ( { '|role_ids' : [ 20 , 40] } )
95 would return all persons that have either pi (20) or tech (40) roles
97 GetPersons ( { '&role_ids' : 10 } )
98 GetPersons ( { '&role_ids' : 10 } )
99 GetPersons ( { '|role_ids' : [ 10 ] } )
100 GetPersons ( { '|role_ids' : [ 10 ] } )
101 all 4 forms are equivalent and would return all admin users in the system
107 def __init__(self, fields = {}, filter = {}, doc = "Attribute filter"):
108 # Store the filter in our dict instance
109 dict.__init__(self, filter)
111 # Declare ourselves as a type of parameter that can take
112 # either a value or a list of values for each of the specified
114 self.fields = dict ( [ ( field, Mixed (expected, [expected]))
115 for (field,expected) in fields.iteritems() ] )
117 # Null filter means no filter
118 Parameter.__init__(self, self.fields, doc = doc, nullok = True)
120 def sql(self, api, join_with = "AND"):
122 Returns a SQL conditional that represents this filter.
125 if self.has_key('-AND'):
128 if self.has_key('-OR'):
132 self.join_with=join_with
134 # So that we always return something
135 if join_with == "AND":
136 conditionals = ["True"]
137 elif join_with == "OR":
138 conditionals = ["False"]
140 assert join_with in ("AND", "OR")
146 for field, value in self.iteritems():
147 # handle negation, numeric comparisons
148 # simple, 1-depth only mechanism
150 modifiers={'~' : False,
151 '<' : False, '>' : False,
152 '[' : False, ']' : False,
154 '&' : False, '|' : False,
156 def check_modifiers(field):
157 if field[0] in modifiers.keys():
158 modifiers[field[0]] = True
160 return check_modifiers(field)
162 field = check_modifiers(field)
165 if not modifiers['-']:
166 if field not in self.fields:
167 raise PLCInvalidArgument, "Invalid filter field '%s'" % field
169 # handling array fileds always as compound values
170 if modifiers['&'] or modifiers['|']:
171 if not isinstance(value, (list, tuple, set)):
174 def get_op_and_val(value):
178 elif isinstance(value, StringTypes) and \
179 (value.find("*") > -1 or value.find("%") > -1):
181 # insert *** in pattern instead of either * or %
182 # we dont use % as requests are likely to %-expansion later on
183 # actual replacement to % done in PostgreSQL.py
184 value = value.replace ('*','***')
185 value = value.replace ('%','***')
186 value = str(api.db.quote(value))
197 value = str(api.db.quote(value))
198 return (operator, value)
200 if isinstance(value, (list, tuple, set)):
201 # handling filters like '~slice_id':[]
202 # this should return true, as it's the opposite of 'slice_id':[] which is false
203 # prior to this fix, 'slice_id':[] would have returned ``slice_id IN (NULL) '' which is unknown
204 # so it worked by coincidence, but the negation '~slice_ids':[] would return false too
206 if modifiers['&'] or modifiers['|']:
213 clause = "%s %s %s" % (field, operator, value)
217 base_op, val = get_op_and_val(val)
219 vals[base_op].append(val)
221 vals[base_op] = [val]
223 for operator in vals.keys():
226 subclauses.append("(%s @> ARRAY[%s])" % (field, ",".join(vals[operator])))
228 subclauses.append("(%s && ARRAY[%s])" % (field, ",".join(vals[operator])))
230 subclauses.append("(%s IN (%s))" % (field, ",".join(vals[operator])))
231 elif operator == 'IS':
232 subclauses.append("(%s IS NULL)" % field)
234 for value in vals[operator]:
235 subclauses.append("(%s %s %s)" % (field, operator, value))
236 clause = "(" + " OR ".join(subclauses) + ")"
238 operator, value = get_op_and_val(value)
240 clause = "%s %s %s" % (field, operator, value)
243 clause = " ( NOT %s ) " % (clause)
245 conditionals.append(clause)
246 # sorting and clipping
248 if field not in ('SORT','OFFSET','LIMIT'):
249 raise PLCInvalidArgument, "Invalid filter, unknown sort and clip field %r"%field
252 if not isinstance(value,(list,tuple,set)):
258 elif field[0] == '-':
261 if field not in self.fields:
262 raise PLCInvalidArgument, "Invalid field %r in SORT filter"%field
263 sorts.append("%s %s"%(field,order))
265 elif field == 'OFFSET':
266 clips.append("OFFSET %d"%value)
268 elif field == 'LIMIT' :
269 clips.append("LIMIT %d"%value)
271 where_part = (" %s " % join_with).join(conditionals)
274 clip_part += " ORDER BY " + ",".join(sorts)
276 clip_part += " " + " ".join(clips)
277 if Filter.debug: print 'Filter.sql: where_part=',where_part,'clip_part',clip_part
278 return (where_part,clip_part)