3 from types import StringTypes
12 from PLC.Faults import *
13 from PLC.Parameter import Parameter, Mixed, python_type
15 class Filter(Parameter, dict):
17 A type of parameter that represents a filter on one or more
18 columns of a database table.
19 Special features provide support for negation, upper and lower bounds,
20 as well as sorting and clipping.
23 fields should be a dictionary of field names and types.
24 As of PLCAPI-4.3-26, we provide support for filtering on
25 sequence types as well, with the special '&' and '|' modifiers.
26 example : fields = {'node_id': Parameter(int, "Node identifier"),
27 'hostname': Parameter(int, "Fully qualified hostname", max = 255),
31 filter should be a dictionary of field names and values
32 representing the criteria for filtering.
33 example : filter = { 'hostname' : '*.edu' , site_id : [34,54] }
36 Whether the filter represents an intersection (AND) or a union (OR)
37 of these criteria is determined as follows:
38 * if the dictionnary has the '-AND' or the '-OR' key, this is chosen
39 * otherwise, the join_with argument, as provided to the sql method below,
40 is expected to hold the 'AND' or 'OR' string
41 this argument defaults to 'AND' and in most of the code, this default applies
42 as the join_with argument is left unspecified
47 * a field starting with '&' or '|' should refer to a sequence type
48 the semantic is then that the object value (expected to be a list)
49 should contain all (&) or any (|) value specified in the corresponding
50 filter value. See other examples below.
51 example : filter = { '|role_ids' : [ 20, 40 ] }
52 example : filter = { '|roles' : ['tech', 'pi'] }
53 example : filter = { '&roles' : ['admin', 'tech'] }
54 example : filter = { '&roles' : 'tech' }
56 * a field starting with the ~ character means negation.
57 example : filter = { '~peer_id' : None }
59 * a field starting with < [ ] or > means lower than or greater than
60 < > uses strict comparison
61 [ ] is for using <= or >= instead
62 example : filter = { ']event_id' : 2305 }
63 example : filter = { '>time' : 1178531418 }
64 in this example the integer value denotes a unix timestamp
66 * if a value is a sequence type, then it should represent
67 a list of possible values for that field
68 example : filter = { 'node_id' : [12,34,56] }
70 * a (string) value containing either a * or a % character is
71 treated as a (sql) pattern; * are replaced with % that is the
72 SQL wildcard character.
73 example : filter = { 'hostname' : '*.jp' }
75 * the filter's keys starting with '-' are special and relate to sorting and clipping
76 * '-SORT' : a field name, or an ordered list of field names that are used for sorting
77 these fields may start with + (default) or - for denoting increasing or decreasing order
78 example : filter = { '-SORT' : [ '+node_id', '-hostname' ] }
79 * '-OFFSET' : the number of first rows to be ommitted
80 * '-LIMIT' : the amount of rows to be returned
81 example : filter = { '-OFFSET' : 100, '-LIMIT':25}
84 Here are a few realistic examples
86 GetNodes ( { 'node_type' : 'regular' , 'hostname' : '*.edu' ,
87 '-SORT' : 'hostname' , '-OFFSET' : 30 , '-LIMIT' : 25 } )
88 would return regular (usual) nodes matching '*.edu' in alphabetical order from 31th to 55th
90 GetNodes ( { '~peer_id' : None } )
91 returns the foreign nodes - that have an integer peer_id
93 GetPersons ( { '|role_ids' : [ 20 , 40] } )
94 would return all persons that have either pi (20) or tech (40) roles
96 GetPersons ( { '&role_ids' : 10 } )
97 GetPersons ( { '&role_ids' : 10 } )
98 GetPersons ( { '|role_ids' : [ 10 ] } )
99 GetPersons ( { '|role_ids' : [ 10 ] } )
100 all 4 forms are equivalent and would return all admin users in the system
106 def __init__(self, fields = {}, filter = {}, doc = "Attribute filter"):
107 # Store the filter in our dict instance
108 dict.__init__(self, filter)
110 # Declare ourselves as a type of parameter that can take
111 # either a value or a list of values for each of the specified
113 self.fields = dict ( [ ( field, Mixed (expected, [expected]))
114 for (field,expected) in fields.iteritems() ] )
116 # Null filter means no filter
117 Parameter.__init__(self, self.fields, doc = doc, nullok = True)
119 def sql(self, api, join_with = "AND"):
121 Returns a SQL conditional that represents this filter.
124 if self.has_key('-AND'):
127 if self.has_key('-OR'):
131 self.join_with=join_with
133 # So that we always return something
134 if join_with == "AND":
135 conditionals = ["True"]
136 elif join_with == "OR":
137 conditionals = ["False"]
139 assert join_with in ("AND", "OR")
145 for field, value in self.iteritems():
146 # handle negation, numeric comparisons
147 # simple, 1-depth only mechanism
149 modifiers={'~' : False,
150 '<' : False, '>' : False,
151 '[' : False, ']' : False,
153 '&' : False, '|' : False,
155 def check_modifiers(field):
156 if field[0] in modifiers.keys():
157 modifiers[field[0]] = True
159 return check_modifiers(field)
161 field = check_modifiers(field)
164 if not modifiers['-']:
165 if field not in self.fields:
166 raise PLCInvalidArgument, "Invalid filter field '%s'" % field
168 # handling array fileds always as compound values
169 if modifiers['&'] or modifiers['|']:
170 if not isinstance(value, (list, tuple, set)):
173 def get_op_and_val(value):
177 elif isinstance(value, StringTypes) and \
178 (value.find("*") > -1 or value.find("%") > -1):
180 # insert *** in pattern instead of either * or %
181 # we dont use % as requests are likely to %-expansion later on
182 # actual replacement to % done in PostgreSQL.py
183 value = value.replace ('*','***')
184 value = value.replace ('%','***')
185 value = str(api.db.quote(value))
196 value = str(api.db.quote(value))
197 return (operator, value)
199 if isinstance(value, (list, tuple, set)):
200 # handling filters like '~slice_id':[]
201 # this should return true, as it's the opposite of 'slice_id':[] which is false
202 # prior to this fix, 'slice_id':[] would have returned ``slice_id IN (NULL) '' which is unknown
203 # so it worked by coincidence, but the negation '~slice_ids':[] would return false too
205 if modifiers['&'] or modifiers['|']:
212 clause = "%s %s %s" % (field, operator, value)
216 base_op, val = get_op_and_val(val)
218 vals[base_op].append(val)
220 vals[base_op] = [val]
222 for operator in vals.keys():
225 subclauses.append("(%s @> ARRAY[%s])" % (field, ",".join(vals[operator])))
227 subclauses.append("(%s && ARRAY[%s])" % (field, ",".join(vals[operator])))
229 subclauses.append("(%s IN (%s))" % (field, ",".join(vals[operator])))
230 elif operator == 'IS':
231 subclauses.append("(%s IS NULL)" % field)
233 for value in vals[operator]:
234 subclauses.append("(%s %s %s)" % (field, operator, value))
235 clause = "(" + " OR ".join(subclauses) + ")"
237 operator, value = get_op_and_val(value)
239 clause = "%s %s %s" % (field, operator, value)
242 clause = " ( NOT %s ) " % (clause)
244 conditionals.append(clause)
245 # sorting and clipping
247 if field not in ('SORT','OFFSET','LIMIT'):
248 raise PLCInvalidArgument, "Invalid filter, unknown sort and clip field %r"%field
251 if not isinstance(value,(list,tuple,set)):
257 elif field[0] == '-':
260 if field not in self.fields:
261 raise PLCInvalidArgument, "Invalid field %r in SORT filter"%field
262 sorts.append("%s %s"%(field,order))
264 elif field == 'OFFSET':
265 clips.append("OFFSET %d"%value)
267 elif field == 'LIMIT' :
268 clips.append("LIMIT %d"%value)
270 where_part = (" %s " % join_with).join(conditionals)
273 clip_part += " ORDER BY " + ",".join(sorts)
275 clip_part += " " + " ".join(clips)
276 if Filter.debug: print 'Filter.sql: where_part=',where_part,'clip_part',clip_part
277 return (where_part,clip_part)