Source Code for Module gavo.base.sqlmunge

  1  """ 
  2  Helpers for building SQL expressions. 
  3   
  4  Some of this code is concerned with SQL factories.  These are functions 
  5  with the signature:: 
  6   
  7          func(field, val, outPars) -> fragment 
  8   
  9  outPars is a dictionary that is used to transmit literal values into SQL. 
 10  The result must be an SQL boolean expression for embedding into a WHERE clause 
 11  (use None to signal no constraint).  Field is the field for which the 
 12  expression is being generated. 
 13   
 14  The factories currently are never called when val is a sequence; there's 
 15  special hard-coded behaviour for that in getSQLFactory. 
 16   
 17  To enter values in outPars, use getSQLKey.  Its docstring contains 
 18  an example that shows how that would look like. 
 19  """ 
 20   
 21  #c Copyright 2008-2019, the GAVO project 
 22  #c 
 23  #c This program is free software, covered by the GNU GPL.  See the 
 24  #c COPYING file in the source distribution. 
 25   
 26   
 27  from gavo.stc import mjdToDateTime 
 28   
 29   
 30  plusInfinity = float("Inf") 
 31  minusInfinity = float("-Inf") 
 32   
 33   
 34 -def joinOperatorExpr(operator, operands): 
 35          """filters empty operands and joins the rest using operator. 
 36   
 37          The function returns an expression string or None for the empty expression. 
 38          """ 
 39          operands = filter(None, operands) 
 40          if not operands: 
 41                  return None 
 42          elif len(operands)==1: 
 43                  return operands[0] 
 44          else: 
 45                  return operator.join([" (%s) "%op for op in operands]).strip() 
 46   
 47   
 48 -def getSQLKey(key, value, sqlPars): 
 49          """adds value to sqlPars and returns a key for inclusion in a SQL query. 
 50   
 51          This function is used to build parameter dictionaries for SQL queries,  
 52          avoiding overwriting parameters with accidental name clashes. 
 53   
 54          As an extra service, if value is a list, it is turned into a set 
 55          (rather than the default, which would be an array).  We don't believe 
 56          there's a great need to match against arrays.  If you must match against 
 57          arrays, use numpy arrays. 
 58   
 59          >>> sqlPars = {} 
 60          >>> getSQLKey("foo", 13, sqlPars) 
 61          'foo0' 
 62          >>> getSQLKey("foo", 14, sqlPars) 
 63          'foo1' 
 64          >>> getSQLKey("foo", 13, sqlPars) 
 65          'foo0' 
 66          >>> sqlPars["foo0"], sqlPars["foo1"]; sqlPars = {} 
 67          (13, 14) 
 68          >>> "WHERE foo<%%(%s)s OR foo>%%(%s)s"%(getSQLKey("foo", 1, sqlPars), 
 69          ...   getSQLKey("foo", 15, sqlPars)) 
 70          'WHERE foo<%(foo0)s OR foo>%(foo1)s' 
 71          """ 
 72          if isinstance(value, list): 
 73                  value = frozenset(value) 
 74   
 75          ct = 0 
 76          while True: 
 77                  dataKey = "%s%d"%(key, ct) 
 78                  if dataKey not in sqlPars or sqlPars[dataKey]==value: 
 79                          break 
 80                  ct += 1 
 81          sqlPars[dataKey] = value 
 82          return dataKey 
 83   
 84   
 85  _REGISTRED_SQL_FACTORIES = {} 
 86 -def registerSQLFactory(type, factory): 
 87          """registers factory as an SQL factory for the type type (a string). 
 88          """ 
 89          _REGISTRED_SQL_FACTORIES[type] = factory 
 90   
 91   
 92 -def _getSQLForSequence(field, val, sqlPars): 
 93          if len(val)==0 or (len(val)==1 and val[0] is None): 
 94                  return "" 
 95          return "%s IN %%(%s)s"%(field.name, getSQLKey(field.name, 
 96                  set(val), sqlPars)) 
 97   
 98   
 99 -def _convertIfFinite(val, converter): 
100          if minusInfinity<val<plusInfinity: 
101                  return converter(val) 
102          return val 
103   
104   
105 -def _getSQLForInterval(field, val, sqlPars): 
106          """returns SQL for DALI intervals. 
107   
108          This presumes that val is a 2-array of numbers and will return 
109          an empty condition otherwise. 
110          """ 
111          if len(val)!=2: 
112                  return "" 
113   
114          if field.hasProperty("database-column-is-date"): 
115                  val = [_convertIfFinite(v, mjdToDateTime) for v in val] 
116   
117          if val[1]==plusInfinity: 
118                  return "%s > %%(%s)s"%(field.name, getSQLKey(field.name, 
119                  val[0], sqlPars)) 
120   
121          elif val[0]==minusInfinity: 
122                  return "%s < %%(%s)s"%(field.name, getSQLKey(field.name, 
123                  val[1], sqlPars)) 
124   
125          else: 
126                  return "%s BETWEEN %%(%s)s AND %%(%s)s"%(field.name,  
127                          getSQLKey(field.name, val[0], sqlPars), 
128                          getSQLKey(field.name, val[1], sqlPars)) 
129   
130   
131 -def _getSQLForSimple(field, val, sqlPars): 
132          return "%s=%%(%s)s"%(field.name, getSQLKey(field.name, 
133                  val, sqlPars)) 
134   
135   
136 -def _getSQLFactory(field, value): 
137          """returns an SQL factory for matching field's values against value. 
138          """ 
139          if field.xtype=="interval": 
140                  return _getSQLForInterval 
141   
142          elif isinstance(value, (list, tuple)): 
143                  return _getSQLForSequence 
144   
145          elif field.type in _REGISTRED_SQL_FACTORIES: 
146                  return _REGISTRED_SQL_FACTORIES[field.type] 
147   
148          else: 
149                  return _getSQLForSimple 
150   
151   
152 -def getSQLForField(field, inPars, sqlPars): 
153          """returns an SQL fragment for a column-like thing. 
154   
155          This will be empty if no input in inPars is present.  If it is, (a) new 
156          key(s) will be left in sqlPars. 
157   
158          getSQLForField defines the default behaviour; in DBCore condDescs, 
159          it can be overridden using phrase makers. 
160   
161          inPars is supposed to be "typed"; we do not catch general parse errors 
162          here. 
163          """ 
164          val = inPars.get(field.name) 
165          if val is None: 
166                  return None 
167          if isinstance(val, (list, set, tuple)) and len(val)==1: 
168                  val = val[0] 
169   
170          factory = _getSQLFactory(field, val) 
171          return factory(field, val, sqlPars) 
172   
173   
174 -def _test(): 
175          import doctest, sqlmunge 
176          doctest.testmod(sqlmunge) 
177   
178   
179  if __name__=="__main__": 
180          _test() 
181