1 from types import StringTypes
10 from PLC.Faults import *
11 from PLC.Parameter import Parameter, Mixed, python_type
13 class Filter(Parameter, dict):
15 A type of parameter that represents a filter on one or more
16 columns of a database table.
17 Special features provide support for negation, upper and lower bounds,
18 as well as sorting and clipping.
21 fields should be a dictionary of field names and types
22 Only filters on non-sequence type fields are supported.
23 example : fields = {'node_id': Parameter(int, "Node identifier"),
24 'hostname': Parameter(int, "Fully qualified hostname", max = 255),
28 filter should be a dictionary of field names and values
29 representing the criteria for filtering.
30 example : filter = { 'hostname' : '*.edu' , site_id : [34,54] }
31 Whether the filter represents an intersection (AND) or a union (OR)
32 of these criteria is determined by the join_with argument
33 provided to the sql method below
37 * a field starting with the ~ character means negation.
38 example : filter = { '~peer_id' : None }
40 * a field starting with < [ ] or > means lower than or greater than
41 < > uses strict comparison
42 [ ] is for using <= or >= instead
43 example : filter = { ']event_id' : 2305 }
44 example : filter = { '>time' : 1178531418 }
45 in this example the integer value denotes a unix timestamp
47 * if a value is a sequence type, then it should represent
48 a list of possible values for that field
49 example : filter = { 'node_id' : [12,34,56] }
51 * a (string) value containing either a * or a % character is
52 treated as a (sql) pattern; * are replaced with % that is the
53 SQL wildcard character.
54 example : filter = { 'hostname' : '*.jp' }
56 * fields starting with - are special and relate to row selection, i.e. sorting and clipping
57 * '-SORT' : a field name, or an ordered list of field names that are used for sorting
58 * these fields may start with + (default) or - for denoting increasing or decreasing order
59 example : filter = { '-SORT' : [ '+node_id', '-hostname' ] }
60 * '-OFFSET' : the number of first rows to be ommitted
61 * '-LIMIT' : the amount of rows to be returned
62 example : filter = { '-OFFSET' : 100, '-PAGE':25}
64 A realistic example would read
65 GetNodes ( { 'hostname' : '*.edu' , '-SORT' : 'hostname' , '-OFFSET' : 30 , '-PAGE' : 25 } )
66 and that would return nodes matching '*.edu' in alphabetical order from 31th to 55th
69 def __init__(self, fields = {}, filter = {}, doc = "Attribute filter"):
70 # Store the filter in our dict instance
71 dict.__init__(self, filter)
73 # Declare ourselves as a type of parameter that can take
74 # either a value or a list of values for each of the specified
78 for field, expected in fields.iteritems():
79 # Cannot filter on sequences
80 if python_type(expected) in (list, tuple, set):
83 # Accept either a value or a list of values of the specified type
84 self.fields[field] = Mixed(expected, [expected])
86 # Null filter means no filter
87 Parameter.__init__(self, self.fields, doc = doc, nullok = True)
89 # this code is not used anymore
90 # at some point the select in the DB for event objects was done on
91 # the events table directly, that is stored as a timestamp, thus comparisons
92 # needed to be done based on SQL timestamps as well
93 def unix2timestamp (self,unix):
95 return "TIMESTAMP'%04d-%02d-%02d %02d:%02d:%02d'" % (s.tm_year,s.tm_mon,s.tm_mday,
96 s.tm_hour,s.tm_min,s.tm_sec)
98 def sql(self, api, join_with = "AND"):
100 Returns a SQL conditional that represents this filter.
103 # So that we always return something
104 if join_with == "AND":
105 conditionals = ["True"]
106 elif join_with == "OR":
107 conditionals = ["False"]
109 assert join_with in ("AND", "OR")
115 for field, value in self.iteritems():
116 # handle negation, numeric comparisons
117 # simple, 1-depth only mechanism
119 modifiers={'~' : False,
120 '<' : False, '>' : False,
121 '[' : False, ']' : False,
125 for char in modifiers.keys():
127 modifiers[char]=True;
132 if not modifiers['-']:
133 if field not in self.fields:
134 raise PLCInvalidArgument, "Invalid filter field '%s'" % field
136 if isinstance(value, (list, tuple, set)):
137 # Turn empty list into (NULL) instead of invalid ()
142 value = map(str, map(api.db.quote, value))
143 value = "(%s)" % ", ".join(value)
148 elif isinstance(value, StringTypes) and \
149 (value.find("*") > -1 or value.find("%") > -1):
151 value = str(api.db.quote(value.replace("*", "%")))
163 value = str(api.db.quote(value))
165 clause = "%s %s %s" % (field, operator, value)
168 clause = " ( NOT %s ) " % (clause)
170 conditionals.append(clause)
171 # sorting and clipping
173 if field not in ('SORT','OFFSET','LIMIT'):
174 raise PLCInvalidArgument, "Invalid filter, unknown sort and clip field %r"%field
177 if not isinstance(value,(list,tuple,set)):
183 elif field[0] == '-':
186 if field not in self.fields:
187 raise PLCInvalidArgument, "Invalid field %r in SORT filter"%field
188 sorts.append("%s %s"%(field,order))
190 elif field == 'OFFSET':
191 clips.append("OFFSET %d"%value)
193 elif field == 'LIMIT' :
194 clips.append("LIMIT %d"%value)
196 where_part = (" %s " % join_with).join(conditionals)
199 clip_part += " ORDER BY " + ",".join(sorts)
201 clip_part += " " + " ".join(clips)
202 # print 'where_part=',where_part,'clip_part',clip_part
203 return (where_part,clip_part)