RelatedKey.py 12.9 KB
Newer Older
1
from __future__ import absolute_import
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 30 31
##############################################################################
#
# Copyright (c) 2002-2006 Nexedi SARL and Contributors. All Rights Reserved.
# Copyright (c) 2007-2009 Nexedi SA 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.
#
##############################################################################

32
from six.moves import xrange
33
from .SearchKey import SearchKey
34 35 36 37
from Products.ZSQLCatalog.Query.Query import Query
from Products.ZSQLCatalog.Query.RelatedQuery import RelatedQuery
from Products.ZSQLCatalog.Query.SQLQuery import SQLQuery
from Products.ZSQLCatalog.SQLExpression import SQLExpression
38
from Products.ZSQLCatalog.interfaces.search_key import IRelatedKey
39
from zope.interface.verify import verifyClass
40
from zope.interface import implementer
41
from Products.ZSQLCatalog.TableDefinition import TableAlias, InnerJoin, LeftJoin
42

43 44 45
from logging import getLogger
log = getLogger(__name__)

46 47
BACKWARD_COMPATIBILITY = True

48 49
RELATED_QUERY_SEPARATOR = "\nAND -- related query separator\n"

50 51 52 53 54 55 56
RELATED_KEY_MISMATCH_MESSAGE = "\
A rendered related key must contain the same number of querying \
conditions as the tables it relates, properly separated by \
RELATED_QUERY_SEPARATOR. \n\
Offending related key: %r, for column %r, table_alias_list: %r, \
rendered_related_key: \n%s"

57 58 59 60
RELATED_KEY_ALIASED_MESSAGE = "\
Support for explicit joins of aliased related keys is not yet implemented. \
Offending related key: %r, for column %r, table_alias_list: %r"

61
@implementer(IRelatedKey)
62 63
class RelatedKey(SearchKey):
  """
64
    This SearchKey handles searches on virtual columns of RelatedKey type.
65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118
    It generates joins required by the virtual column to reach the actual
    column to compare, plus a regular query on that column if needed.
  """

  related_key_definition = None

  def _buildRelatedKey(self, related_key_definition):
    """
      Extract RelatedKey parameters from its definition, and cache this
      result. If related_key_definition changes since last computation, cached
      values will be refreshed.

      related_key_definition (string)
        Describes parameters of a RelatedKey. It is composed of 3 mains parts,
        separated by '/':
        - a list of table names
          Table names are separated by ','
        - a column name
        - the name of the related key ZSQLMethod
    """
    assert related_key_definition is not None
    if self.related_key_definition != related_key_definition:
      self.related_key_definition = related_key_definition
      # Extract related_key_id, column_id and table_list from related_key_definition
      table_list, self.real_column, self.related_key_id = related_key_definition.split('/')
      self.table_list = table_list.split(',')

  def _getSearchKey(self, sql_catalog, search_key_name):
    """
      Get search key relevant to the actual column.

      sql_catalog (SQLCatalog)
        Used to access SearchKey provider.
      search_key_name (string, None)
        See SQLCatalog.getSearchKey.
    """
    return sql_catalog.getSearchKey(self.real_column, search_key_name)

  def getSearchKey(self, sql_catalog, related_key_definition, search_key_name=None):
    """
      Get search key relevant to the actual column, extracting information
      about that column first if needed.

      sql_catalog (SQLCatalog)
        Used to access SearchKey provider.
      related_key_definition (string)
        See _buildRelatedKey.
      search_key_name (string, None)
        See SQLCatalog.getSearchKey.
    """
    self._buildRelatedKey(related_key_definition)
    return self._getSearchKey(sql_catalog, search_key_name)

  def buildQuery(self, sql_catalog, related_key_definition,
119
                 search_value=None):
120
    self._buildRelatedKey(related_key_definition)
121 122
    if isinstance(search_value, Query):
      search_value.setGroup(self.getColumn())
123
    return RelatedQuery(search_key=self,
124
                        join_condition=search_value)
125 126 127 128

  def registerColumnMap(self, column_map, table_alias_list=None):
    related_column = self.getColumn()
    group = column_map.registerRelatedKey(related_column, self.real_column)
129 130 131 132 133 134
    # Each table except last one must be registered to their own
    # group, so that the same table can be used multiple times (and
    # aliased multiple times) in the same related key. The last one
    # must be registered to the related key "main" group (ie, the
    # value of the "group" variable) to be the same as the table used
    # in join_condition.
135
    if table_alias_list is not None:
136 137
      assert len(self.table_list) == len(table_alias_list), (self.table_list,
        table_alias_list)
138 139 140 141 142 143 144 145 146
      # XXX-Leo: remove the rest of this 'if' branch after making sure
      # that ColumnMap.addRelatedKeyJoin() can handle collapsing
      # chains of inner-joins that are subsets of one another based on
      # having the same aliases:
      msg = RELATED_KEY_ALIASED_MESSAGE % (self.related_key_id,
                                           self.column,
                                           table_alias_list,)
      log.warning(msg + "\n\nForcing implicit join...")
      column_map.implicit_join = True
147 148 149 150 151 152 153
    for table_position in xrange(len(self.table_list) - 1):
      table_name = self.table_list[table_position]
      local_group = column_map.registerRelatedKeyColumn(related_column, table_position, group)
      column_map.registerTable(table_name, group=local_group)
      if table_alias_list is not None:
        # Pre-resolve all tables with given aliases
        given_name, given_alias = table_alias_list[table_position]
154
        assert table_name == given_name, (table_name, given_name)
155 156 157 158 159
        column_map.resolveTable(table_name, given_alias, group=local_group)
    table_name = self.table_list[-1]
    column_map.registerTable(table_name, group=group)
    if table_alias_list is not None:
      given_name, given_alias = table_alias_list[-1]
160
      assert table_name == given_name, (table_name, given_name)
161 162 163 164 165 166
      column_map.resolveTable(table_name, given_alias, group=group)
    # Resolve (and register) related key column in related key group with its last table.
    column_map.registerColumn(self.real_column, group=group)
    column_map.resolveColumn(self.real_column, table_name, group=group)
    return group

167 168 169 170 171
  def stitchJoinDefinition(self, table_alias_list, join_query_list, column_map):
    alias, table = table_alias_list[-1]
    right = column_map.makeTableAliasDefinition(table, alias)
    if not join_query_list:
      # nothing to do, just return the table alias
172
      assert len(table_alias_list) == 1, table_alias_list
173 174 175 176 177 178 179 180 181 182 183
      return right
    else:
      # create an InnerJoin of the last element of the alias list with
      # a chain of InnerJoins of the rest of the list conditioned on
      # the the last element of the join_query_list
      left = self.stitchJoinDefinition(table_alias_list[:-1],
                                       join_query_list[:-1],
                                       column_map)
      condition = join_query_list[-1]
      return InnerJoin(left, right, condition)

184 185 186 187 188 189 190 191 192 193 194 195
  def buildSQLExpression(self, sql_catalog, column_map, only_group_columns, group):
    """
      Render RelatedKey's ZSQLMethod by providing it table aliases from
      ColumnMap.

      sql_catalog (SQLCatalog)
      column_map (ColumnMap)
      group (string)
      only_group_columns (bool)
        Ignored.
    """
    related_key = getattr(sql_catalog, self.related_key_id)
Yusuke Muraoka's avatar
Yusuke Muraoka committed
196 197 198 199 200 201 202 203 204 205 206 207

    # related key is defined in catalog
    related_table_list, destination_table = self.table_list[:-1], self.table_list[-1]

    # method caching
    getTableAlias = column_map.getTableAlias
    getRelatedKeyGroup = column_map.getRelatedKeyGroup

    # table aliases for related tables
    table_alias_list = [(getTableAlias(related_table, group=getRelatedKeyGroup(index, group)), related_table)
                        for (index, related_table) in enumerate(related_table_list)]
    # table alias for destination table
208 209
    table_alias_list.append((getTableAlias(destination_table, group=group),
                             destination_table))
Yusuke Muraoka's avatar
Yusuke Muraoka committed
210 211

    # map aliases to use in ZSQLMethod.
212 213
    table_alias_dict = {'table_%s' % index: alias[0]
                        for index, alias in enumerate(table_alias_list)}
Yusuke Muraoka's avatar
Yusuke Muraoka committed
214

215 216
    assert len(table_alias_list) == len(table_alias_dict), (table_alias_list,
      table_alias_dict)
Yusuke Muraoka's avatar
Yusuke Muraoka committed
217

218
    query_table=column_map.getCatalogTableAlias()
219
    rendered_related_key = related_key(
220 221
      query_table=query_table,
      RELATED_QUERY_SEPARATOR=RELATED_QUERY_SEPARATOR,
222 223
      src__=1,
      **table_alias_dict)
224
    join_condition_list = rendered_related_key.split(RELATED_QUERY_SEPARATOR)
225

226
    # Important:
227
    # Previously the catalog separated join condition from the related query.
228 229 230
    # Example:
    #   ComplexQuery(Query(title="foo"),
    #                Query(subordination_title="bar")
231
    #                , logical_operator='OR')
232 233 234 235 236 237 238 239 240 241 242
    # Former catalog rendering (truncated where-expression):
    #   AND ((catalog.title LIKE '%foo%') OR
    #        (related_catalog_1.title LIKE '%bar%'))
    #   AND (related_catalog_1.uid = related_category_0.category_uid AND
    #        related_category_0.base_category_uid = 873 AND
    #        related_category_0.uid = catalog.uid)
    # As you can see, the part of the query joining the tables is *out* of the
    # OR expression, and therefor applies to the entire query.
    # This was done on purpose, because doing otherwise gives very poor
    # performances (on a simple data set, similar query can take *minutes* to
    # execute - as of MySQL 5.x).
243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263
    #
    # Because of this, we never return an SQLExpression here, as it
    # would mix join definition with column condition in the body of
    # the WHERE clause. Instead we explicitly define a Join to the
    # catalog. The ColumnMap defines whether this is an Inner Join or
    # a Left Outer Join. Notice that if an Inner Join is decided,
    # objects lacking a relationship will never appear in the result.

    if len(join_condition_list) == len(table_alias_list):
      # Good! we got a compatible method that splits the join
      # conditions according to the related tables.
      #
      # Add a join on this related key, based on the chain of
      # inner-joins of the related key tables.
      query_table_join_condition = join_condition_list.pop()
      right_side = self.stitchJoinDefinition(table_alias_list,
                                             join_condition_list,
                                             column_map)
      column_map.addRelatedKeyJoin(self.column,
                                   right_side=right_side,
                                   condition=query_table_join_condition)
264
    else:
265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289
      # Method did not render the related key condition with the
      # appropriate separators so we could split it

      # XXX: Can we try to parse rendered_related_key to select which
      # conditions go with each table? Maybe we could still use
      # explicit joins this way...

      msg = RELATED_KEY_MISMATCH_MESSAGE % (self.related_key_id,
                                            self.column,
                                            table_alias_list,
                                            rendered_related_key)
      if BACKWARD_COMPATIBILITY:
        # BBB: remove this branch of the condition, and the above
        # constant, when all zsql_methods have been adapted to return
        # the join queries properly separated by the
        # RELATED_QUERY_SEPARATOR.

        # The rendered related key doesn't have the separators for each
        # joined table, so we revert to doing implicit inner joins:
        log.warning(msg + "\n\nAdding an Implicit Join Condition...")
        column_map._addJoinQueryForColumn(self.column,
                                          SQLQuery(rendered_related_key))
      else:
        raise RuntimeError(msg)
    return None
290 291 292

verifyClass(IRelatedKey, RelatedKey)