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)
214 value = map(str, map(api.db.quote, value))
218 base_op, val = get_op_and_val(val)
222 vals[base_op].append(val)
224 vals[base_op] = [val]
228 value = "ARRAY[%s]" % ", ".join(value)
231 value = "ARRAY[%s]" % ", ".join(value)
234 value = "(%s)" % ", ".join(value)
235 clause = "%s %s %s" % (field, operator, value)
237 # We need something more complex
239 for operator in vals.keys():
241 subclauses.append("(%s IN (%s))" % (field, ",".join(vals[operator])))
242 elif operator == 'IS':
243 subclauses.append("(%s IS NULL)" % field)
245 for value in vals[operator]:
246 subclauses.append("(%s %s %s)" % (field, operator, value))
247 clause = "(" + " OR ".join(subclauses) + ")"
249 operator, value = get_op_and_val(value)
250 clause = "%s %s %s" % (field, operator, value)
253 clause = " ( NOT %s ) " % (clause)
255 conditionals.append(clause)
256 # sorting and clipping
258 if field not in ('SORT','OFFSET','LIMIT'):
259 raise PLCInvalidArgument, "Invalid filter, unknown sort and clip field %r"%field
262 if not isinstance(value,(list,tuple,set)):
268 elif field[0] == '-':
271 if field not in self.fields:
272 raise PLCInvalidArgument, "Invalid field %r in SORT filter"%field
273 sorts.append("%s %s"%(field,order))
275 elif field == 'OFFSET':
276 clips.append("OFFSET %d"%value)
278 elif field == 'LIMIT' :
279 clips.append("LIMIT %d"%value)
281 where_part = (" %s " % join_with).join(conditionals)
284 clip_part += " ORDER BY " + ",".join(sorts)
286 clip_part += " " + " ".join(clips)
287 if Filter.debug: print 'Filter.sql: where_part=',where_part,'clip_part',clip_part
288 return (where_part,clip_part)