SQLExpression.py 15.3 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29
##############################################################################
#
# Copyright (c) 2008-2009 Nexedi SARL and Contributors. All Rights Reserved.
#                    Jean-Paul Smets-Solanes <jp@nexedi.com>
#                    Vincent Pelletier <vincent@nexedi.com>
#
# WARNING: This program as such is intended to be used by professional
# programmers who take the whole responsability of assessing all potential
# consequences resulting from its eventual inadequacies and bugs
# End users who are looking for a ready-to-use solution with commercial
# garantees and support are strongly adviced to contract a Free Software
# Service Company
#
# This program is Free Software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License
# as published by the Free Software Foundation; either version 2
# of the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
#
##############################################################################

30
import warnings
31
from interfaces.sql_expression import ISQLExpression
32 33
from zope.interface.verify import verifyClass
from zope.interface import implements
34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56
from types import NoneType

SQL_LIST_SEPARATOR = ', '
SQL_TABLE_FORMAT = '%s' # XXX: should be changed to '`%s`', but this breaks some ZSQLMethods.
SQL_SELECT_ALIAS_FORMAT = '%s AS `%s`'

"""
  TODO:
    - change table_alias_dict in internals to represent computed tables:
       ie: '(SELECT * FROM `bar` WHERE `baz` = "hoge") AS `foo`'
           '`foo` LEFT JOIN `bar` WHERE (`baz` = "hoge")'
"""

# Set to true to keep a reference to the query which created us.
# Set to false to avoid keeping a reference to an object.
DEBUG = True

def defaultDict(value):
  if value is None:
    return {}
  assert isinstance(value, dict)
  return value.copy()

57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79
class MergeConflictError(ValueError):
  pass

class MergeConflict(object):
  """
  This class allows lazy errors.

  SQLExpression detects merge conflicts when 2 different values exist for the
  same key in 2 SQLExpression tree nodes.
  This class allows to postpone raising an exception, to allow conflicting
  values as long as they are not actualy used.
  """
  # TODO (?): Include the traceback as of instanciation in error message,
  #           if it can help debugging.
  def __init__(self, message):
    self._message = message

  def __call__(self):
    raise MergeConflictError(self._message)

def conflictSafeGet(dikt, key, default=None):
  result = dikt.get(key, default)
  if isinstance(result, MergeConflict):
80
    result() # Raises
81 82
  return result

83 84
class SQLExpression(object):

85
  implements(ISQLExpression)
86 87 88 89 90 91 92 93 94 95 96 97

  def __init__(self,
               query,
               table_alias_dict=None,
               order_by_list=(),
               order_by_dict=None,
               group_by_list=(),
               where_expression=None,
               where_expression_operator=None,
               sql_expression_list=(),
               select_dict=None,
               limit=None,
98
               from_expression=None,
99
               can_merge_select_dict=False):
100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118
    if DEBUG:
      self.query = query
    self.table_alias_dict = defaultDict(table_alias_dict)
    self.order_by_list = list(order_by_list)
    self.group_by_list = list(group_by_list)
    self.order_by_dict = defaultDict(order_by_dict)
    # Only one of (where_expression, where_expression_operator) must be given (never both)
    assert None in (where_expression, where_expression_operator)
    # Exactly one of (where_expression, where_expression_operator) must be given, except if sql_expression_list is given and contains exactly one entry
    assert where_expression is not None or where_expression_operator is not None or (sql_expression_list is not None and len(sql_expression_list) == 1)
    # where_expression must be a basestring instance if given
    assert isinstance(where_expression, (NoneType, basestring))
    # where_expression_operator must be 'and', 'or' or 'not' (if given)
    assert where_expression_operator in (None, 'and', 'or', 'not'), where_expression_operator
    self.where_expression = where_expression
    self.where_expression_operator = where_expression_operator
    # Exactly one of (where_expression, sql_expression_list) must be given (XXX: duplicate of previous conditions ?)
    assert where_expression is not None or sql_expression_list is not None
    if isinstance(sql_expression_list, (list, tuple)):
119 120 121
      self.sql_expression_list = [x for x in sql_expression_list if x is not None]
    else:
      self.sql_expression_list = list(sql_expression_list)
122 123 124 125 126 127 128 129 130 131
    if limit is None:
      self.limit = ()
    elif isinstance(limit, (list, tuple)):
      if len(limit) < 3:
        self.limit = limit
      else:
        raise ValueError, 'Unrecognized "limit" value: %r' % (limit, )
    else:
      self.limit = (limit, )
    if from_expression is not None:
132 133
      warnings.warn("Providing a 'from_expression' is deprecated.",
                    DeprecationWarning)
134
    self.from_expression = from_expression
135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166

    select_dict = defaultDict(select_dict)
    mergeable_set = set()
    if can_merge_select_dict:
      mergeable_set.update(select_dict)
    for sql_expression in self.sql_expression_list:
      can_merge_sql_expression = sql_expression.can_merge_select_dict
      mergeable_set.update(sql_expression._mergeable_set)
      for alias, column in sql_expression._select_dict.iteritems():
        existing_value = select_dict.get(alias)
        if existing_value not in (None, column):
          if can_merge_sql_expression and alias in mergeable_set:
            # Custom conflict resolution
            column = '%s + %s' % (existing_value, column)
          elif alias.endswith('__score__'):
            # We only support the first full text score in select dict.
            pass
          else:
            message = '%r is a known alias for column %r, can\'t alias it now to column %r' % (alias, existing_value, column)
            if DEBUG:
              message = message + '. I was created by %r, and I am working on %r (%r) out of [%s]' % (
                self.query,
                sql_expression,
                sql_expression.query,
                ', '.join('%r (%r)' % (x, x.query) for x in self.sql_expression_list))
            raise ValueError(message)
        select_dict[alias] = column
        if can_merge_sql_expression:
          mergeable_set.add(alias)
    self._select_dict = select_dict
    self._mergeable_set = mergeable_set
    self._reversed_select_dict = {y: x for x, y in select_dict.iteritems()}
167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187

  def getTableAliasDict(self):
    """
      Returns a dictionary:
        key: table alias (string)
        value: table name (string)

      If there are nested SQLExpressions, it aggregates their mappings and
      checks that they don't alias different table with the same name. If they
      do, it raises a ValueError.
    """
    result = self.table_alias_dict.copy()
    for sql_expression in self.sql_expression_list:
      for alias, table_name in sql_expression.getTableAliasDict().iteritems():
        existing_value = result.get(alias)
        if existing_value not in (None, table_name):
          message = '%r is a known alias for table %r, can\'t alias it now to table %r' % (alias, existing_value, table_name)
          if DEBUG:
            message = message + '. I was created by %r, and I am working on %r (%r) out of [%s]' % (
              self.query,
              sql_expression,
188 189
              sql_expression.query,
              ', '.join('%r (%r)' % (x, x.query) for x in self.sql_expression_list))
190 191 192 193 194 195
          raise ValueError, message
        result[alias] = table_name
    return result

  def getFromExpression(self):
    """
196
      Returns a TableDefinition stored in one of the from_expressions or None
197 198 199 200 201 202 203 204

      If there are nested SQLExpression, it checks that they either don't
      define any from_expression or the exact same from_expression. Otherwise,
      it raises a ValueError.
    """
    result = self.from_expression
    for sql_expression in self.sql_expression_list:
      from_expression = sql_expression.getFromExpression()
205
      if from_expression not in (result, None):
206 207 208 209 210
        message = 'I don\'t know how to merge from_expressions'
        if DEBUG:
          message = message + '. I was created by %r, and I am working on %r (%r) out of [%s]' % (
            self.query,
            sql_expression,
211 212
            sql_expression.query,
            ', '.join('%r (%r)' % (x, x.query) for x in self.sql_expression_list))
213 214 215 216 217 218 219 220 221 222 223 224
        raise ValueError, message
    return result

  def getOrderByList(self):
    """
      Returns a list of strings.

      If there are nested SQLExpression, it checks that they don't define
      sorts for columns which are already sorted. If they do, it raises a
      ValueError.
    """
    result = self.order_by_list[:]
225
    known_column_set = {x[0] for x in result}
226 227 228
    for sql_expression in self.sql_expression_list:
      for order_by in sql_expression.getOrderByList():
        if order_by[0] in known_column_set:
229
          raise ValueError("I don't know how to merge order_by yet")
230 231 232 233 234
        else:
          result.append(order_by)
          known_column_set.add(order_by[0])
    return result

235
  def _getOrderByDict(self, delay_error=True):
236 237
    result_dict = self.order_by_dict.copy()
    for sql_expression in self.sql_expression_list:
238
      order_by_dict = sql_expression._getOrderByDict(delay_error=delay_error)
239
      for key, value in order_by_dict.iteritems():
240 241
        if key in result_dict and value != result_dict[key] \
            and not isinstance(value, MergeConflict):
242 243 244 245 246 247
          message = 'I don\'t know how to merge order_by_dict with ' \
                    'conflicting entries for key %r: %r vs. %r' % (key, result_dict[key], value)
          if DEBUG:
            message = message + '. I was created by %r, and I am working on %r (%r) out of [%s]' % (
              self.query,
              sql_expression,
248 249
              sql_expression.query,
              ', '.join('%r (%r)' % (x, x.query) for x in self.sql_expression_list))
250 251 252 253
          if delay_error:
            order_by_dict[key] = MergeConflict(message)
          else:
            raise MergeConflictError, message
254 255 256
      result_dict.update(order_by_dict)
    return result_dict

257 258 259
  def getOrderByDict(self):
    return self._getOrderByDict(delay_error=False)

260 261 262 263 264 265
  def getOrderByExpression(self):
    """
      Returns a string.

      Returns a rendered "order by" expression. See getOrderByList.
    """
266 267
    result = []
    append = result.append
268
    order_by_dict = self._getOrderByDict()
269 270
    for (column, direction, cast) in self.getOrderByList():
      expression = conflictSafeGet(order_by_dict, column, str(column))
271
      expression = self._reversed_select_dict.get(expression, expression)
272 273 274 275 276 277
      if cast not in (None, ''):
        expression = 'CAST(%s AS %s)' % (expression, cast)
      if direction is not None:
        expression = '%s %s' % (expression, direction)
      append(expression)
    return SQL_LIST_SEPARATOR.join(result)
278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316

  def getWhereExpression(self):
    """
      Returns a string.

      Returns a rendered "where" expression.
    """
    if self.where_expression is not None:
      result = self.where_expression
    else:
      if self.where_expression_operator == 'not':
        assert len(self.sql_expression_list) == 1
        result = '(NOT %s)' % (self.sql_expression_list[0].getWhereExpression())
      elif len(self.sql_expression_list) == 1:
        result = self.sql_expression_list[0].getWhereExpression()
      elif len(self.sql_expression_list) == 0:
        result = '(1)'
      else:
        operator = '\n  ' + self.where_expression_operator.upper() + ' '
        result = '(%s)' % (operator.join(x.getWhereExpression() for x in self.sql_expression_list), )
    return result

  def getLimit(self):
    """
      Returns a list of 1 or 2 items (int or string).

      If there are nested SQLExpression, it checks that they either don't
      define any limit or the exact same limit. Otherwise it raises a
      ValueError.
    """
    result = list(self.limit)
    for sql_expression in self.sql_expression_list:
      other_limit = sql_expression.getLimit()
      if other_limit not in ([], result):
        message = 'I don\'t know how to merge limits yet'
        if DEBUG:
          message = message + '. I was created by %r, and I am working on %r (%r) out of [%s]' % (
            self.query,
            sql_expression,
317 318
            sql_expression.query,
            ', '.join('%r (%r)' % (x, x.query) for x in self.sql_expression_list))
319 320 321 322 323 324
        raise ValueError, message
    return result

  def getLimitExpression(self):
    """
      Returns a string.
325

326 327 328 329 330 331 332 333 334 335 336
      Returns a rendered "limit" expression. See getLimit.
    """
    return SQL_LIST_SEPARATOR.join(str(x) for x in self.getLimit())

  def getGroupByset(self):
    """
      Returns a set of strings.

      If there are nested SQLExpression, it merges (union of sets) them with
      local value.
    """
337
    result = {self._reversed_select_dict.get(x, x) for x in self.group_by_list}
338 339 340 341 342 343 344 345 346 347 348 349
    for sql_expression in self.sql_expression_list:
      result.update(sql_expression.getGroupByset())
    return result

  def getGroupByExpression(self):
    """
      Returns a string.

      Returns a rendered "group by" expression. See getGroupBySet.
    """
    return SQL_LIST_SEPARATOR.join(self.getGroupByset())

350 351 352 353 354 355 356 357 358 359
  def getSelectDict(self):
    """
      Returns a dict:
        key: alias (string)
        value: column (string) or None

      If there are nested SQLExpression, it aggregates their mappings and
      checks that they don't alias different columns with the same name. If
      they do, it raises a ValueError.
    """
360
    return self._select_dict
361 362 363 364 365 366 367 368 369 370 371

  def getSelectExpression(self):
    """
      Returns a string.

      Returns a rendered "select" expression. See getSelectDict.
    """
    return SQL_LIST_SEPARATOR.join(
      SQL_SELECT_ALIAS_FORMAT % (column, alias)
      for alias, column in self.getSelectDict().iteritems())

372
  def getFromTableList(self):
373
    table_alias_dict = self.getTableAliasDict()
374 375
    if not table_alias_dict:
      return None
376 377 378
    from_table_list = []
    append = from_table_list.append
    for alias, table in table_alias_dict.iteritems():
379
      append((SQL_TABLE_FORMAT % (alias, ), SQL_TABLE_FORMAT % (table, )))
380 381 382 383 384 385 386 387 388 389
    return from_table_list

  def asSQLExpressionDict(self):
    from_expression = self.getFromExpression()
    from_table_list = self.getFromTableList()
    assert None in (from_expression,
                    from_table_list), ("Cannot return both a from_expression "
                                       "and a from_table_list")
    if from_expression is not None:
      from_expression = from_expression.render()
390 391 392 393 394 395 396 397 398 399 400 401
    return {
      'where_expression': self.getWhereExpression(),
      'order_by_expression': self.getOrderByExpression(),
      'from_table_list': from_table_list,
      'from_expression': from_expression,
      'limit_expression': self.getLimitExpression(),
      'select_expression': self.getSelectExpression(),
      'group_by_expression': self.getGroupByExpression()
    }

verifyClass(ISQLExpression, SQLExpression)