testSQLCatalog.py 41.3 KB
Newer Older
1
# -*- coding: utf-8 -*-
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 32 33 34 35 36 37 38
##############################################################################
#
# Copyright (c) 2006-2009 Nexedi SA and Contributors. All Rights Reserved.
#          Jerome Perrin <jerome@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.
#
##############################################################################

import unittest
from Products.ZSQLCatalog.SQLCatalog import Catalog as SQLCatalog
from Products.ZSQLCatalog.SQLCatalog import Query
from Products.ZSQLCatalog.SQLCatalog import ComplexQuery
from Products.ZSQLCatalog.SQLCatalog import SimpleQuery
from Products.ZSQLCatalog.Query.EntireQuery import EntireQuery
from Products.ZSQLCatalog.Query.RelatedQuery import RelatedQuery
from DateTime import DateTime
39
from Products.ZSQLCatalog.SQLExpression import MergeConflictError
40
from Products.ERP5Type.tests.ERP5TypeTestCase import ERP5TypeTestCase
41

42
class MatchList(list):
43 44
  def __repr__(self):
    return '<%s %r>' % (self.__class__.__name__, self[:])
45

46
class ReferenceQuery:
47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85
  """
    This class is made to be able to compare a generated query tree with a
    reference one.

    It supports the following types of queries:
      SimpleQuery
        This can be compared with a ReferenceQuery in the form:
        ReferenceQuery(operator=some_operator, column=value)
        Where:
        - operator is the expected comparison operator (see
          ZSQLCatalog/Operator/ComparisonOperator.py:operator_dict keys)
        - column is the expected column name (without table mapping)
        - value is the expected value (rendered as text)
      ComplexQuery
        This can be compares with a ReferenceQuery in the form:
        ReferenceQuery(*arg, operator=logical_operator)
        Where:
        - args is a list of sub-queries (each will be searched for into
          compared query tree, so order doesn't matter)
        - operator is a logical operator name (see ComplexQuery class)
      EntireQuery
        This type of query is considered as an operator-less, single-subquery
        ComplexQuery. Its embeded query will be recursed into.
      RelatedQuery
        This type of query is considered as an operator-less, single-subquery
        ComplexQuery. Its "join condition" query will be recursed into (raw sql
        will not).
      AutoQuery (known here as "Query")
        This type of query is considered as an operator-less, single-subquery
        ComplexQuery. Its wrapped (=auto-generated equivalent query) query will
        be recursed into.

    Note: This code is quite ugly as it references query classes and access
    instance attributes directly.
    But I (Vincent) believe that it would be pointless to design individual
    __eq__ methods on all queries, as anyway they must know the compared query
    class, and as such it would spread the dirtyness among code which is not
    limited to tests.
  """
86 87 88 89 90 91 92 93 94 95 96 97 98 99
  operator = None
  column = None
  value = None

  def __init__(self, *args, **kw):
    self.operator = kw.pop('operator', None)
    assert len(args) == 0 or len(kw) == 0
    self.args = []
    for arg in args:
      if isinstance(arg, (tuple, list)):
        self.args.extend(arg)
      else:
        self.args.append(arg)
    if len(kw) == 1:
100 101 102 103
      self.column, value = kw.items()[0]
      if not isinstance(value, MatchList):
        value = MatchList([value])
      self.value = value
104 105 106 107 108 109 110
    elif len(kw) > 1:
      raise ValueError, 'kw must not have more than one item: %r' % (kw, )

  def __eq__(self, other):
    if isinstance(other, SimpleQuery):
      return self.column is not None and \
             other.getColumn() == self.column and \
111
             other.getValue() in self.value and \
112
             other.comparison_operator == self.operator
113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137
    elif isinstance(other, ComplexQuery):
      if not (len(other.query_list) == len(self.args) and \
              other.logical_operator == self.operator):
        return False
      other_query_list = other.query_list[:]
      for subquery in self.args:
        for other_query_id in xrange(len(other_query_list)):
          other_query = other_query_list[other_query_id]
          if subquery == other_query:
            other_query_list.pop(other_query_id)
            break
        else:
          return False
      return len(other_query_list) == 0
    elif isinstance(other, EntireQuery):
      return len(self.args) == 1 and \
             self.args[0] == other.query
    elif isinstance(other, RelatedQuery):
      return self == other.join_condition
    elif isinstance(other, Query):
      return self == other.wrapped_query
    else:
      raise TypeError, 'Compared value is not a (known) Query instance: (%s) %r' % (other.__class__.__name__, other)

  def __repr__(self):
138 139 140 141 142 143 144
    if self.args:
      # ComplexQuery-ish
      representation = (' %s ' % (self.operator, )).join(repr(x) for x in self.args)
    else:
      # SimpleQuery-ish
      representation = '%r %r %r' % (self.column, self.operator, self.value)
    return '<%s %s>' % (self.__class__.__name__, representation)
145 146

class RelatedReferenceQuery:
147 148 149 150 151
  """
    This class has the same objective as ReferenceQuery, but it is limited to
    RelatedQuery comparison: the compared query *must* be a RelatedQuery
    instance for equality to be confirmed.
  """
152 153 154 155
  def __init__(self, reference_subquery):
    self.subquery = reference_subquery

  def __eq__(self, other):
156 157
    return isinstance(other, RelatedQuery) and \
           self.subquery == other.join_condition
158 159 160 161 162 163

  def __repr__(self):
    return '<%s %r>' % (self.__class__.__name__, self.subquery)

class DummyCatalog(SQLCatalog):
  """
164 165 166
    Mimic a table stucture definition.
    Removes the need to instanciate a complete catalog and the need to create
    associated tables. This offers a huge flexibility.
167 168 169 170
  """

  sql_catalog_keyword_search_keys = ('keyword', )
  sql_catalog_datetime_search_keys = ('date', )
171
  sql_catalog_full_text_search_keys = ('old_fulltext', )
172
  sql_catalog_scriptable_keys = ('scriptable_keyword | scriptableKeyScript', )
173 174
  sql_catalog_search_keys = ('fulltext | MroongaFullTextKey',
                             'fulltext_boolean | MroongaBooleanFullTextKey',)
175 176

  def getColumnMap(self):
177 178 179
    """
      Fake table structure description.
    """
180 181 182 183 184
    return {
      'uid': ['foo', 'bar'],
      'default': ['foo', ],
      'keyword': ['foo', ],
      'date': ['foo', ],
185
      'old_fulltext': ['foo', ],
186
      'fulltext': ['foo', ],
187
      'fulltext_boolean': ['foo', ],
188 189
      'other_uid': ['bar', ],
      'ambiguous_mapping': ['foo', 'bar'],
190 191 192
    }

  def getSQLCatalogRelatedKeyList(self, key_list):
193 194 195
    """
      Fake auto-generated related key definitions.
    """
196 197 198 199 200 201 202
    return [
      'related_default | bar,foo/default/z_related_table',
      'related_keyword | bar,foo/keyword/z_related_table',
      'related_date | bar,foo/date/z_related_table'
    ]

  def z_related_table(self, *args, **kw):
203 204 205
    """
      Mimics a ZSQLMethod subobject.
    """
206 207 208 209
    assert kw.get('src__', False)
    assert 'query_table' in kw
    assert 'table_0' in kw
    assert 'table_1' in kw
210
    assert 'AND' in kw.pop('RELATED_QUERY_SEPARATOR')
211 212 213
    assert len(kw) == 4
    return '%(table_0)s.uid = %(query_table)s.uid AND %(table_0)s.other_uid = %(table_1)s' % kw

214
  def scriptableKeyScript(self, value):
215 216 217
    """
      Mimics a scriptable key (PythonScript) subobject.
    """
218
    return SimpleQuery(comparison_operator='=', keyword=value)
219

220
class TestSQLCatalog(ERP5TypeTestCase):
221 222 223 224 225 226
  def setUp(self):
    self._catalog = DummyCatalog('dummy_catalog')

  def assertCatalogRaises(self, exception, kw):
    self.assertRaises(exception, self._catalog, src__=1, query_table='foo', **kw)

227
  def catalog(self, reference_tree, kw, check_search_text=True,
228
      check_select_expression=True, expected_failure=False):
229
    reference_param_dict = self._catalog.buildSQLQuery(query_table='foo', **kw)
230
    query = self._catalog.buildEntireQuery(kw).query
231 232
    assertEqual = self.assertEqual
    if expected_failure:
233
      assertEqual = unittest.expectedFailure(assertEqual)
234 235

    assertEqual(reference_tree, query)
236 237 238
    search_text = query.asSearchTextExpression(self._catalog)
    if check_search_text:
      # XXX: sould "keyword" be always used for search text searches ?
239
      search_text_param_dict = self._catalog.buildSQLQuery(query_table='foo', keyword=search_text)
240 241 242
      if not check_select_expression:
        search_text_param_dict.pop('select_expression')
        reference_param_dict.pop('select_expression')
243
      assertEqual(reference_param_dict, search_text_param_dict,
244 245 246
          'Query: %r\nSearchText: %r\nReference: %r\nSecond rendering: %r' % \
                       (query, search_text, reference_param_dict, search_text_param_dict))

247 248
  def asSQLExpression(self, kw, **build_entire_query_kw):
    entire_query = self._catalog.buildEntireQuery(kw, **build_entire_query_kw)
249 250
    return entire_query.asSQLExpression(self._catalog, False)

251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267
  def _testDefaultKey(self, column):
    self.catalog(ReferenceQuery(ReferenceQuery(operator='=', default='a'), operator='and'),
                 {column: 'a'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='like', default='%a'), operator='and'),
                 {column: '%a'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='<', default='a'), operator='and'),
                 {column: '<a'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='<=', default='a'), operator='and'),
                 {column: '<=a'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='>=', default='a'), operator='and'),
                 {column: '>=a'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='>', default='a'), operator='and'),
                 {column: '>a'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='!=', default='a'), operator='and'),
                 {column: '!=a'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='=', default='a b'), operator='and'),
                 {column: 'a b'})
268
    self.catalog(ReferenceQuery(ReferenceQuery(ReferenceQuery(operator='=', default='a'), ReferenceQuery(operator='>', default='b'), operator='and'), operator='and'),
269 270 271
                 {column: 'a >b'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='=', default='a > b'), operator='and'),
                 {column: 'a > b'})
272
    self.catalog(ReferenceQuery(ReferenceQuery(ReferenceQuery(operator='>', default='a'), ReferenceQuery(operator='>', default='b'), operator='and'), operator='and'),
273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293
                 {column: '>a >b'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='=', default='>a >b'), operator='and'),
                 {column: '">a >b"'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='>', default='>a >b'), operator='and'),
                 {column: '>">a >b"'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='in', default=['a', 'b']), operator='and'),
                 {column: 'a OR b'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='=', default='a OR b'), operator='and'),
                 {column: '"a OR b"'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='<', default='path'), operator='and'),
                 {column: {'query': 'path', 'range': 'max'}})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='in', default=['a', 'b']), operator='and'),
                 {column: ['a', 'b']})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='in', default=['a', 'b']), operator='and'),
                 {column: ['=a', '=b']})

  def test_DefaultKey(self):
    self._testDefaultKey('default')

  def test_relatedDefaultKey(self):
    self._testDefaultKey('related_default')
294 295 296 297 298 299 300 301 302

  def test_002_keyOverride(self):
    self.catalog(ReferenceQuery(ReferenceQuery(operator='=', default='%a'), operator='and'),
                 {'default': {'query': '%a', 'key': 'ExactMatch'}},
                 check_search_text=False)
    self.catalog(ReferenceQuery(ReferenceQuery(operator='=', default='<a'), operator='and'),
                 {'default': {'query': '<a', 'key': 'ExactMatch'}},
                 check_search_text=False)

303
  def _testDateTimeKey(self, column, timezone):
304
    self.catalog(ReferenceQuery(ReferenceQuery(operator='>=', date=DateTime('2008/10/01 12:10:21')), operator='and'),
305 306 307 308 309 310
        {column: {'query': '>"2008/10/01 12:10:20"', 'format': '%Y/%m/%d', 'type': 'date'}})
    self.catalog(ReferenceQuery(ReferenceQuery(
          ReferenceQuery(operator='>=', date=DateTime('2008/10/01 12:10:21')),
          ReferenceQuery(operator='<', date=DateTime('2008/10/02 10:00:00')),
        operator='and'), operator='and'),
      {column: {'query': '>"2008/10/01 12:10:20" AND <"2008/10/02 10:00:00"', 'format': '%Y/%m/%d', 'type': 'date'}})
311
    self.catalog(ReferenceQuery(ReferenceQuery(operator='>=', date=DateTime('2008/10/01 12:10:21 CEST')), operator='and'),
312
        {column: {'query': '>"2008/10/01 12:10:20 CEST"', 'format': '%Y/%m/%d', 'type': 'date'}})
313
    self.catalog(ReferenceQuery(ReferenceQuery(operator='>=', date=DateTime('2008/10/01 12:10:21 CET')), operator='and'),
314
        {column: {'query': '>"2008/10/01 12:10:20 CET"', 'format': '%Y/%m/%d', 'type': 'date'}})
315
    self.catalog(ReferenceQuery(ReferenceQuery(
316 317
                   ReferenceQuery(operator='>=', date=DateTime('2008/10/01 %s' % timezone)),
                   ReferenceQuery(operator='<', date=DateTime('2008/10/02 %s' % timezone))
318
                 , operator='and'), operator='and'),
319 320 321 322 323 324
                 {column: '2008/10/01 %s' % timezone})
    if timezone == 'GMT+9':
      self.catalog(ReferenceQuery(ReferenceQuery(
                     ReferenceQuery(operator='>=', date=DateTime('2008/01/01 %s' % timezone)),
                     ReferenceQuery(operator='<', date=DateTime('2009/01/01 %s' % timezone))
                   , operator='and'), operator='and'),
Yusei Tahara's avatar
Yusei Tahara committed
325
                   {column: '2008 %s' % timezone})
326 327 328 329 330 331
    else:
      self.catalog(ReferenceQuery(ReferenceQuery(
                     ReferenceQuery(operator='>=', date=DateTime('2008/01/01 %s' % timezone)),
                     ReferenceQuery(operator='<', date=DateTime('2009/01/01 %s' % timezone))
                   , operator='and'), operator='and'),
                   {column: '2008 %s' % timezone})
332
    self.catalog(ReferenceQuery(ReferenceQuery(
333 334
                   ReferenceQuery(operator='>=', date=DateTime('2008/01/01 %s' % timezone)),
                   ReferenceQuery(operator='<', date=DateTime('2008/02/01 %s' % timezone))
335
                 , operator='and'), operator='and'),
336
                 {column: '2008/01 %s' % timezone})
337
    self.catalog(ReferenceQuery(ReferenceQuery(
338 339
                   ReferenceQuery(operator='>=', date=DateTime('2008/10/01 %s' % timezone)),
                   ReferenceQuery(operator='<', date=DateTime('2008/10/02 %s' % timezone))
340
                 , operator='and'), operator='and'),
341
                 {column: {'type': 'date', 'query': '10/01/2008 %s' % timezone, 'format': '%m/%d/%Y'}})
342
    self.catalog(ReferenceQuery(ReferenceQuery(
343 344
                   ReferenceQuery(operator='>=', date=DateTime('2008/10/01 %s' % timezone)),
                   ReferenceQuery(operator='<', date=DateTime('2008/10/02 %s' % timezone))
345
                 , operator='and'), operator='and'),
346
                 {column: {'type': 'date', 'query': '01/10/2008 %s' % timezone, 'format': '%d/%m/%Y'}})
347 348 349 350 351 352 353 354 355 356
    self.catalog(ReferenceQuery(ReferenceQuery(
        ReferenceQuery(
          ReferenceQuery(operator='>=', date=DateTime('2008/01/10 ' + timezone)),
          ReferenceQuery(operator='<', date=DateTime('2008/01/11 ' + timezone)),
        operator='and'),
        ReferenceQuery(
          ReferenceQuery(operator='>=', date=DateTime('2008/01/09 ' + timezone)),
          ReferenceQuery(operator='<', date=DateTime('2008/01/10 ' + timezone)),
        operator='and'),
      operator='or'), operator='and'),
357
                 {column: {'query': ['2008/01/10 %s' % timezone, '2008/01/09 %s' % timezone], 'operator': 'in'}},
358
                 check_search_text=False)
359 360 361 362 363 364 365 366 367 368 369 370 371
    self.catalog(ReferenceQuery(ReferenceQuery(
        ReferenceQuery(
          ReferenceQuery(operator='>=', date=DateTime('2008/01/10 ' + timezone)),
          ReferenceQuery(operator='<', date=DateTime('2008/01/11 ' + timezone)),
        operator='and'),
        ReferenceQuery(
          ReferenceQuery(operator='>=', date=DateTime('2008/01/09 ' + timezone)),
          ReferenceQuery(operator='<', date=DateTime('2008/01/10 ' + timezone)),
        operator='and'),
      operator='or'), operator='and'),
                 {column: ['2008/01/10 %s' % timezone, '2008/01/09 %s' % timezone]},
                 check_search_text=False)
    self.catalog(ReferenceQuery(ReferenceQuery(operator='>=', date=DateTime('2008/01/11 %s' % timezone)), operator='and'),
372
                 {column: {'query': '2008/01/10 %s' % timezone, 'range': 'nlt'}},
373 374
                 check_search_text=False)
    self.catalog(ReferenceQuery(ReferenceQuery(
375 376
                   ReferenceQuery(operator='>=', date=DateTime('2008/01/01 %s' % timezone)),
                   ReferenceQuery(operator='<', date=DateTime('2009/01/01 %s' % timezone))
377
                 , operator='and'), operator='and'),
378
                 {column: '2008 %s' % timezone})
379
    self.catalog(ReferenceQuery(ReferenceQuery(
380 381
                   ReferenceQuery(operator='>=', date=DateTime('2008/02/01 %s' % timezone)),
                   ReferenceQuery(operator='<', date=DateTime('2008/03/01 %s' % timezone))
382
                 , operator='and'), operator='and'),
383
                 {column: '2008/02 %s' % timezone})
384
    self.catalog(ReferenceQuery(ReferenceQuery(
385 386
                   ReferenceQuery(operator='>=', date=DateTime('2008/02/02 %s' % timezone)),
                   ReferenceQuery(operator='<', date=DateTime('2008/02/03 %s' % timezone))
387
                 , operator='and'), operator='and'),
388
                 {column: '2008/02/02 %s' % timezone})
389
    self.catalog(ReferenceQuery(ReferenceQuery(
390 391
                   ReferenceQuery(operator='>=', date=DateTime('2008/02/02 10:00:00 %s' % timezone)),
                   ReferenceQuery(operator='<', date=DateTime('2008/02/02 11:00:00 %s' % timezone))
392
                 , operator='and'), operator='and'),
393
                 {column: '2008/02/02 10 %s' % timezone})
394
    self.catalog(ReferenceQuery(ReferenceQuery(
395 396
                   ReferenceQuery(operator='>=', date=DateTime('2008/02/02 10:10:00 %s' % timezone)),
                   ReferenceQuery(operator='<', date=DateTime('2008/02/02 10:11:00 %s' % timezone))
397
                 , operator='and'), operator='and'),
398
                 {column: '2008/02/02 10:10 %s' % timezone})
399
    self.catalog(ReferenceQuery(ReferenceQuery(
400 401
                   ReferenceQuery(operator='>=', date=DateTime('2008/02/02 10:10:10 %s' % timezone)),
                   ReferenceQuery(operator='<', date=DateTime('2008/02/02 10:10:11 %s' % timezone))
402
                 , operator='and'), operator='and'),
403
                 {column: '2008/02/02 10:10:10 %s' % timezone})
404 405
    self.catalog(ReferenceQuery(ReferenceQuery(operator='is', date=None), operator='and'),
                 {column: None}, check_search_text=False)
406

407
  def test_DateTimeKey(self):
408 409 410
    # Try multiple timezones
    self._testDateTimeKey('date', 'UTC')
    self._testDateTimeKey('date', 'GMT+9')
411 412 413 414 415 416 417 418 419 420 421 422 423 424
    # XXX: It is unknown what these tests should produce when used with a
    # related key: should the join happen or not ?
    self.catalog(
      ReferenceQuery(ReferenceQuery([], operator='or'), operator='and'),
      {'date': ' '})
    self.catalog(
      ReferenceQuery(ReferenceQuery([], operator='or'), operator='and'),
      {'date': '<>2008/01/01'})
    self.catalog(
      ReferenceQuery(ReferenceQuery([], operator='or'), operator='and'),
      {'date': '<'})
    self.catalog(
      ReferenceQuery(ReferenceQuery([], operator='or'), operator='and'),
      {'date': '00:00:00'})
425 426

  def test_relatedDateTimeKey(self):
427 428 429
    # Try multiple timezones
    self._testDateTimeKey('related_date', 'UTC')
    self._testDateTimeKey('related_date', 'GMT+9')
430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445

  def _testKeywordKey(self, column):
    self.catalog(ReferenceQuery(ReferenceQuery(operator='like', keyword='%a%'), operator='and'),
                 {column: 'a'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='like', keyword='%a b%'), operator='and'),
                 {column: 'a b'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='like', keyword='%a b%'), operator='and'),
                 {column: '"a b"'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='!=', keyword='a'), operator='and'),
                 {column: '!=a'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='not like', keyword='%a'), operator='and'),
                 {column: '!=%a'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='like', keyword='%a'), operator='and'),
                 {column: '%a'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='<', keyword='a'), operator='and'),
                 {column: '<a'})
446 447 448 449
    self.catalog(ReferenceQuery(ReferenceQuery(ReferenceQuery(operator='like', keyword='%a%'), ReferenceQuery(operator='like', keyword='%b%'), operator='and'), operator='and'),
                 {column: 'a AND b'})
    self.catalog(ReferenceQuery(ReferenceQuery(ReferenceQuery(operator='like', keyword='%a%'), ReferenceQuery(operator='like', keyword='%b%'), operator='or'), operator='and'),
                 {column: 'a OR b'})
450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471
    self.catalog(ReferenceQuery(ReferenceQuery(operator='<', keyword='path'), operator='and'),
                 {column: {'query': 'path', 'range': 'max'}})
    self.catalog(ReferenceQuery(ReferenceQuery(
                     ReferenceQuery(operator='like', keyword='%a%'),
                     ReferenceQuery(operator='like', keyword='%b%')
                   , operator='or'), operator='and'),
                 {column: ['a', 'b']})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='in', keyword=['a', 'b']), operator='and'),
                 {column: ['=a', '=b']})
    self.catalog(ReferenceQuery(ReferenceQuery(
                     ReferenceQuery(operator='like', keyword='%a%'),
                     ReferenceQuery(operator='<', keyword='b')
                   , operator='or'), operator='and'),
                 {column: ['a', '<b']})
    self.catalog(ReferenceQuery(ReferenceQuery(
                     ReferenceQuery(operator='like', keyword='%a%'),
                     ReferenceQuery(operator='like', keyword='%b')
                   , operator='or'), operator='and'),
                 {column: ['a', '%b']})

  def test_KeywordKey(self):
    self._testKeywordKey('keyword')
472

473 474
  def test_relatedKeywordKey(self):
    self._testKeywordKey('related_keyword')
475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498

  def test_005_SearchText(self):
    self.catalog(ReferenceQuery(ReferenceQuery(ReferenceQuery(operator='like', keyword='%=a%'), ReferenceQuery(operator='like', keyword='%=b%'), operator='or'), operator='and'),
                 {'keyword': '"=a" OR "=b"'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='in', keyword=['a', 'b']), operator='and'),
                 {'keyword': '="a" OR ="b"'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='in', keyword=['a', 'b']), operator='and'),
                 {'keyword': '=a OR =b'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='in', keyword=['a', 'b', 'c']), operator='and'),
                 {'keyword': '=a OR =b OR =c'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='like', keyword='%a%'), operator='and'),
                 {'keyword': 'keyword:a'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='=', default='a'), operator='and'),
                 {'keyword': 'default:a'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='like', keyword='%a b%'), operator='and'),
                 {'keyword': 'a b'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='like', keyword='%=a OR =b%'), operator='and'),
                 {'keyword': '"=a OR =b"'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='=', keyword='=a OR =b'), operator='and'),
                 {'keyword': '="=a OR =b"'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='<', keyword='=a OR =b'), operator='and'),
                 {'keyword': '<"=a OR =b"'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='like', keyword='%"a" OR "b"%'), operator='and'),
                 {'keyword': '"\\"a\\" OR \\"b\\""'})
499
    # This example introduces impossible-to-merge search text criterion, which
500
    # is allowed as long as
501
    reference_query = ReferenceQuery(
502 503
        ReferenceQuery(ReferenceQuery(operator='mroonga', fulltext='a'),
        ReferenceQuery(ReferenceQuery(operator='mroonga', fulltext='b'),
504 505
      operator='not'), operator='and'), operator='and')
    self.catalog(reference_query, {'fulltext': 'a NOT b'})
506
    # The same, with an order by, must raise
507
    self.assertRaises(MergeConflictError, self.catalog, reference_query,
508
      {'fulltext': 'a NOT b', 'order_by_list': [('fulltext__score__', ), ]},
509 510
      check_search_text=False)
    # If one want to sort on, he must use the equivalent FullText syntax:
511
    self.catalog(ReferenceQuery(ReferenceQuery(operator='mroonga',
512
      fulltext=MatchList(['a -b', '-b a'])), operator='and'),
513
      {'fulltext': 'a -b', 'order_by_list': [('fulltext__score__', ), ]},
514
      check_search_text=False)
515 516
    self.catalog(ReferenceQuery(ReferenceQuery(ReferenceQuery(operator='mroonga', fulltext='a'),
                                               ReferenceQuery(ReferenceQuery(operator='mroonga', fulltext='b'), operator='not'), operator='or'), operator='and'),
517
                 {'fulltext': 'a OR NOT b'})
518 519
    self.catalog(ReferenceQuery(ReferenceQuery(ReferenceQuery(operator='mroonga', fulltext='a'),
                                               ReferenceQuery(ReferenceQuery(operator='mroonga', fulltext='b'), operator='not'), operator='and'), operator='and'),
520
                 {'fulltext': 'a AND NOT b'})
521 522 523 524 525 526 527 528 529 530 531

  def test_006_testRelatedKey_with_multiple_join(self):
    # The name of catalog parameter does not matter at all
    # ComplexQuery(ComplexQuery(AutoQuery(RelatedQuery(SimpleQuery())), AutoQuery(RelatedQuery(SimpleQuery()))))
    # 'AutoQuery' doesn't need any ReferenceQuery equivalent.
    self.catalog(ReferenceQuery(ReferenceQuery(
                     ReferenceQuery(RelatedReferenceQuery(ReferenceQuery(operator='=', default='a')), operator='and'),
                     ReferenceQuery(RelatedReferenceQuery(ReferenceQuery(operator='=', default='b')), operator='and')
                   , operator='and'), operator='and'),
                 {'query': ComplexQuery(Query(related_default='a'), Query(related_default='b'))})

532 533 534
  def test_007_testScriptableKey(self):
    self.catalog(ReferenceQuery(ReferenceQuery(operator='=', keyword='%a%'), operator='and'),
                 {'scriptable_keyword': '%a%'})
535 536
    self.catalog(ReferenceQuery(ReferenceQuery(operator='=', keyword='%a%'), operator='and'),
                 {'default': 'scriptable_keyword:%a%'})
537

538 539 540 541 542 543 544 545 546
  def test_008_testRawKey(self):
    self.catalog(ReferenceQuery(ReferenceQuery(operator='=', default='%a%'), operator='and'),
                 {'default': {'query': '%a%', 'key': 'RawKey'}},
                 check_search_text=False)
    self.catalog(ReferenceQuery(ReferenceQuery(operator='=', default='>a'), operator='and'),
                 {'default': {'query': '>a', 'key': 'RawKey'}},
                 check_search_text=False)

  def test_009_testFullTextKey(self):
547
    self.catalog(ReferenceQuery(ReferenceQuery(operator='mroonga', fulltext='a'), operator='and'),
548 549
                 {'fulltext': 'a'})

550 551 552 553 554 555
  def test_isAdvancedSearchText(self):
    self.assertFalse(self._catalog.isAdvancedSearchText('a')) # No operator, no explicit column
    self.assertTrue(self._catalog.isAdvancedSearchText('a AND b')) # "AND" is an operator
    self.assertTrue(self._catalog.isAdvancedSearchText('default:a')) # "default" exists as a column
    self.assertFalse(self._catalog.isAdvancedSearchText('b:a')) # "b" doesn't exist as a column

556 557
  def test_FullTextSearchMergesQueries(self):
    """
558 559 560 561 562
      XXX this test is for old FullTextKey, not for MroongaFullTextKey
      that merges queries only when logical_operator is 'and'. Also
      _renderValueAsSearchText it not perfect so that we cannot use the
      test codes below for mroonga search key.

563 564 565
      FullText criterion on the same scope must be merged into one query.
      Logical operator is ignored, as fulltext operators are expected instead.
    """
566 567 568 569 570 571
    self.catalog(ReferenceQuery(ReferenceQuery(operator='match', old_fulltext='a b'), operator='and'),
                 {'old_fulltext': 'a AND b'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='match', old_fulltext='a b'), operator='and'),
                 {'old_fulltext': 'a OR b'})
    self.catalog(ReferenceQuery(ReferenceQuery(ReferenceQuery(operator='match', old_fulltext='a b'), operator='not'), operator='and'),
                 {'old_fulltext': 'NOT (a b)'})
572

573 574 575 576 577 578 579 580 581 582 583 584 585
  def test_NoneValueToSimpleQuery(self):
    """
      When a SimpleQuery receives a python None value and an "=" comparison
      operator (be it the default or explictely provided), it must change that
      operator into an "is" operator.
      If "is" compariton operator is explicitely provided with a non-None
      value, raise.
      If non-"=" compariton operator is provided with a None value, raise.
    """
    self.assertEqual(ReferenceQuery(operator='is', default=None),
                     SimpleQuery(default=None))
    self.assertEqual(ReferenceQuery(operator='is', default=None),
                     SimpleQuery(default=None, comparison_operator='='))
586 587 588 589
    self.assertEqual(ReferenceQuery(operator='is not', default=None),
                     SimpleQuery(default=None, comparison_operator='!='))
    self.assertEqual(ReferenceQuery(operator='is not', default=None),
                     SimpleQuery(default=None, comparison_operator='is not'))
590 591 592
    self.assertRaises(ValueError, SimpleQuery, default=None, comparison_operator='>=')
    self.assertRaises(ValueError, SimpleQuery, default=1, comparison_operator='is')

593 594
  def test_FullTextBooleanMode(self):
    """
595 596 597
      XXX this test is for old FullTextKey, not for MroongaFullTextKey
      that does no automatic mode switch.

598 599 600
      Fulltext searches must switch automatically to boolean mode if boolean
      operators are found in search value.
    """
601
    self.catalog(ReferenceQuery(ReferenceQuery(operator='match_boolean',
602 603
                                old_fulltext=MatchList(['a*'])), operator='and'),
                 {'old_fulltext': 'a*'})
604 605

    self.catalog(ReferenceQuery(ReferenceQuery(operator='match_boolean',
606 607
                                old_fulltext=MatchList(['a* b'])), operator='and'),
                 {'old_fulltext': 'a* b'})
608

609
    self.catalog(ReferenceQuery(ReferenceQuery(operator='match', old_fulltext='*a'),
610
                                operator='and'),
611
                 {'old_fulltext': '*a'})
612

613
    self.catalog(ReferenceQuery(ReferenceQuery(operator='match', old_fulltext='a'),
614
                                operator='and'),
615
                 {'old_fulltext': 'a'})
616

617 618
    self.catalog(ReferenceQuery(ReferenceQuery(operator='match', old_fulltext='a+b'), operator='and'),
                 {'old_fulltext': 'a+b'})
619

620
    self.catalog(ReferenceQuery(ReferenceQuery(operator='match_boolean',
621 622
      old_fulltext=MatchList(['a +b', '+b a'])), operator='and'),
                 {'old_fulltext': 'a +b'}, check_search_text=False)
623

624 625 626
    self.catalog(ReferenceQuery(ReferenceQuery(
        ReferenceQuery(operator='=', uid='foo'),
        ReferenceQuery(operator='match_boolean',
627 628
          old_fulltext=MatchList(['+a b', 'b +a'])),
      operator='and'), operator='and'), {'old_fulltext': '+a b uid:foo'})
629

630
  def test_FullTextQuoting(self):
631 632 633 634 635 636
    """
      XXX this test is for old FullTextKey, not for MroongaFullTextKey
      that merges queries only when logical_operator is 'and'. Also
      _renderValueAsSearchText it not perfect so that we cannot use the
      test codes below for mroonga search key.
    """
637 638
    # Quotes must be kept
    self.catalog(ReferenceQuery(ReferenceQuery(operator='match',
639 640
      old_fulltext='"a"'), operator='and'),
      {'old_fulltext': '"a"'})
641
    self.catalog(ReferenceQuery(ReferenceQuery(operator='match',
642 643
      old_fulltext='"foo" bar "baz"'), operator='and'),
      {'old_fulltext': '"foo" bar "baz"'})
644 645 646
    # ...But each column must follow rules defined in configured SearchKey for
    # that column (in this case: quotes must be stripped).
    ref_query = ReferenceQuery(ReferenceQuery(ReferenceQuery(operator='match',
647
      old_fulltext='"foo" bar'), ReferenceQuery(operator='=',
648 649
      default='hoge \"pon'), operator='and'), operator='and')
    self.catalog(ref_query, {
650
      'keyword': 'default:"hoge \\"pon" AND old_fulltext:("foo" AND bar)'})
651
    self.catalog(ref_query, {
652
      'old_fulltext': '"foo" bar AND default:"hoge \\"pon"'})
653
    ref_query = ReferenceQuery(ReferenceQuery(ReferenceQuery(operator='match',
654
      old_fulltext='"\\"foo\\" bar"'), ReferenceQuery(operator='=',
655 656
      default='hoge \"pon'), operator='and'), operator='and')
    self.catalog(ref_query, {
657
      'keyword': 'default:"hoge \\"pon" AND old_fulltext:"\\"foo\\" bar"'})
658

659 660 661 662 663 664 665 666 667
  def test_DefaultKeyTextRendering(self):
    self.catalog(ReferenceQuery(ReferenceQuery(operator='like', default='a% b'), operator='and'),
                 {'default': 'a% b'})
    self.catalog(ReferenceQuery(ReferenceQuery(operator='like', default='%a%'), operator='and'),
                 {'default': '%a%'})
    self.catalog(ReferenceQuery(ReferenceQuery(ReferenceQuery(operator='like', default='a% b'),
                                               ReferenceQuery(operator='like', default='a%'), operator='or'), operator='and'),
                 {'default': ['a% b', 'a%']})

668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687
  def test_SelectDict(self):
    # Simple case: no mapping hint, no ambiguity in table schema
    sql_expression = self.asSQLExpression({'select_dict': {'default': None}})
    select_dict = sql_expression.getSelectDict()
    self.assertTrue('default' in select_dict, select_dict)
    # Case with a valid hint
    sql_expression = self.asSQLExpression({'select_dict': {'default': 'foo'}})
    select_dict = sql_expression.getSelectDict()
    self.assertTrue('default' in select_dict, select_dict)
    # Case with an invalid hint: we trust user
    sql_expression = self.asSQLExpression({'select_dict': {'default': 'bar'}})
    select_dict = sql_expression.getSelectDict()
    self.assertTrue('default' in select_dict, select_dict)
    self.assertTrue('bar' in select_dict['default'], select_dict['default'])
    # Ambiguous case: mapping must raise if there is no hint
    self.assertRaises(ValueError, self.asSQLExpression, {'select_dict':
      {'ambiguous_mapping': None}})
    # Ambiguous case, but with a hint: must succeed
    sql_expression = self.asSQLExpression({'select_dict': {'ambiguous_mapping': 'bar'}})
    select_dict = sql_expression.getSelectDict()
688 689 690 691 692 693 694
    self.assertTrue('ambiguous_mapping' in select_dict, select_dict)
    self.assertTrue('bar' in select_dict['ambiguous_mapping'], select_dict['ambiguous_mapping'])
    # Ambiguous case, without a direct hint, but one of the tables is used in
    # the query: must succeed
    sql_expression = self.asSQLExpression({'select_dict': {'ambiguous_mapping': None},
      'other_uid': None})
    select_dict = sql_expression.getSelectDict()
695 696
    self.assertTrue('ambiguous_mapping' in select_dict, select_dict)
    self.assertTrue('bar' in select_dict['ambiguous_mapping'], select_dict['ambiguous_mapping'])
Leonardo Rochael Almeida's avatar
typo  
Leonardo Rochael Almeida committed
697
    # Dotted alias: table name must get stripped. This is required to have an
698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715
    # upgrade path from old ZSQLCatalog versions where pre-mapped columns were
    # used in their select_expression. This must only happen in the
    # "{column: None}" form, as otherwise it's the user explicitely asking for
    # such alias (which is not strictly invalid).
    sql_expression = self.asSQLExpression({'select_dict': {
      'foo.default': None,
      'foo.keyword': 'foo.keyword',
    }}, query_table='foo')
    select_dict = sql_expression.getSelectDict()
    self.assertTrue('default' in select_dict, select_dict)
    self.assertFalse('foo.default' in select_dict, select_dict)
    self.assertTrue('foo.keyword' in select_dict, select_dict)
    # Variant: same operation, but this time stripping generates an ambiguity.
    # That must be detected and cause a mapping exception.
    self.assertRaises(ValueError, self.asSQLExpression, {'select_dict': {
        'foo.ambiguous_mapping': None,
        'bar.ambiguous_mapping': None,
      }}, query_table='foo')
716

717 718 719 720
  def test_hasColumn(self):
    self.assertTrue(self._catalog.hasColumn('uid'))
    self.assertFalse(self._catalog.hasColumn('foobar'))

721 722 723 724 725 726 727
  def test_fulltextOrderBy(self):
    # No order_by_list, resulting "ORDER BY" must be empty.
    sql_expression = self.asSQLExpression({'fulltext': 'foo'})
    self.assertEqual(sql_expression.getOrderByExpression(), '')
    # order_by_list on fulltext column, resulting "ORDER BY" must be non-empty.
    sql_expression = self.asSQLExpression({'fulltext': 'foo',
      'order_by_list': [('fulltext', ), ]})
728 729
    order_by_expression = sql_expression.getOrderByExpression()
    self.assertNotEqual(order_by_expression, '')
730
    # ... and not sort by relevance
731
    self.assertFalse('MATCH' in order_by_expression, order_by_expression)
732 733 734 735 736
    # order_by_list on fulltext column + '__score__, resulting "ORDER BY" must be non-empty.
    sql_expression = self.asSQLExpression({'fulltext': 'foo',
      'order_by_list': [('fulltext__score__', ), ]})
    order_by_expression = sql_expression.getOrderByExpression()
    self.assertNotEqual(order_by_expression, '')
737
    # ... and must sort by relevance
738
    self.assertTrue('MATCH' in order_by_expression, order_by_expression)
739 740 741 742
    # ordering on fulltext column with sort order specified must preserve
    # sorting by relevance.
    for direction in ('ASC', 'DESC'):
      sql_expression = self.asSQLExpression({'fulltext': 'foo',
743
        'order_by_list': [('fulltext__score__', direction), ]})
744
      order_by_expression = sql_expression.getOrderByExpression()
745
      self.assertTrue('MATCH' in order_by_expression, (order_by_expression, direction))
746 747 748
    # Providing a None cast should work too
    for direction in ('ASC', 'DESC'):
      sql_expression = self.asSQLExpression({'fulltext': 'foo',
749
        'order_by_list': [('fulltext__score__', direction, None), ]})
750
      order_by_expression = sql_expression.getOrderByExpression()
751
      self.assertTrue('MATCH' in order_by_expression, (order_by_expression, direction))
752

753 754 755 756 757 758 759 760 761
  def test_logicalOperators(self):
    self.catalog(ReferenceQuery(ReferenceQuery(operator='=', default='AN ORB'),
        operator='and'),
      {'default': 'AN ORB'})
    self.catalog(ReferenceQuery(
        ReferenceQuery(operator='in', default=['AN', 'ORB']),
        operator='and'),
      {'default': 'AN OR ORB'})

762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793
  def _searchTextInDictQuery(self, column):
    self.catalog(ReferenceQuery(ReferenceQuery(
        ReferenceQuery(operator='>=', date=DateTime('2001/08/11')),
        ReferenceQuery(operator='<', date=DateTime('2008/10/01')),
      operator='and'), operator='and'),
      {
        column: {'query': '>2001/08/10 AND <2008/10/01', 'format': '%d/%m/%Y', 'type': 'date'},
      }
    )
    # Ambiguous date representation with format: dmY
    self.catalog(ReferenceQuery(ReferenceQuery(
        ReferenceQuery(operator='>=', date=DateTime('2001/08/11')),
        ReferenceQuery(operator='<', date=DateTime('2008/10/01')),
      operator='and'), operator='and'),
      {
        column: {'query': '>10/08/2001 AND <01/10/2008', 'format': '%d/%m/%Y', 'type': 'date'},
      }
    )
    # Ambiguous date representation with format: mdY, same input as above
    self.catalog(ReferenceQuery(ReferenceQuery(
        ReferenceQuery(operator='>=', date=DateTime('2001/10/09')),
        ReferenceQuery(operator='<', date=DateTime('2008/01/10')),
      operator='and'), operator='and'),
      {
        column: {'query': '>10/08/2001 AND <01/10/2008', 'format': '%m/%d/%Y', 'type': 'date'},
      }
    )

  def test_searchTextInDictQuery(self):
    self._searchTextInDictQuery('date')
    self._searchTextInDictQuery('related_date')

794 795 796 797 798 799 800 801 802 803 804 805 806 807
##return catalog(title=Query(title='a', operator='not'))
#return catalog(title={'query': 'a', 'operator': 'not'})
#return catalog(title={'query': ['a', 'b'], 'operator': 'not'})
#return context.portal_catalog(source_title="toto", source_description="tutu", src__=1)
#print catalog(query=ComplexQuery(Query(title='1'), ComplexQuery(Query(portal_type='Foo') ,Query(portal_type='Bar'), operator='or'), operator='and'))
#print catalog(title={'query': ('path', 2), 'operator': 'and'}, exception=TypeError)
#print catalog(sort_on=[('source_title', )], check_search_text=False)
#print catalog(query=ComplexQuery(Query(source_title='foo'), Query(source_title='bar')), sort_on=[('source_title', ), ('source_title_1', )], check_search_text=False)

def test_suite():
  suite = unittest.TestSuite()
  suite.addTest(unittest.makeSuite(TestSQLCatalog))
  return suite