testZSQLCatalog.py 15.6 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 30 31
##############################################################################
#
# Copyright (c) 2006 Nexedi SA and Contributors. All Rights Reserved.
#          Jerome Perrin <jerome@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
import sys

32
from DateTime import DateTime
33 34 35 36 37
from Products.ZSQLMethods.SQL import SQL as ZSQLMethod
from Products.CMFCore.Expression import Expression

from Products.ZSQLCatalog.SQLCatalog import Catalog as SQLCatalog
from Products.ZSQLCatalog.ZSQLCatalog import ZCatalog as ZSQLCatalog
38 39
from Products.ZSQLCatalog.SQLCatalog import Query
from Products.ZSQLCatalog.SQLCatalog import ComplexQuery
40
from Products.ZSQLCatalog.SQLCatalog import NegatedQuery
41 42 43 44 45 46 47


class TestZSQLCatalog(unittest.TestCase):
  """Tests for ZSQL Catalog.
  """
  def setUp(self):
    self._catalog = ZSQLCatalog()
48
  # TODO ?
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 86 87 88 89 90 91 92 93 94 95 96 97 98


class TestSQLCatalog(unittest.TestCase):
  """Tests for SQL Catalog.
  """
  def setUp(self):
    self._catalog = SQLCatalog('dummy_catalog')
    self._catalog._setObject('z_dummy_method',
                             ZSQLMethod('z_dummy_method', '', '', '', ''))
    self._catalog.sql_catalog_object_list = ('z_dummy_method', )

  def test_getFilterableMethodList(self):
    self.failUnless(self._catalog.z_dummy_method in
                    self._catalog.getFilterableMethodList())

  def test_manage_editFilter(self):
    request = dict(z_dummy_method_box=1, z_dummy_method_expression='python: 1')
    self._catalog.manage_editFilter(REQUEST=request)
    self.assertTrue(self._catalog.filter_dict.has_key('z_dummy_method'))

  def test_isMethodFiltered(self):
    request = dict(z_dummy_method_box=1, z_dummy_method_expression='python: 1')
    self._catalog.manage_editFilter(REQUEST=request)
    self.assertTrue(self._catalog.isMethodFiltered('z_dummy_method'))
    self.assertFalse(self._catalog.isMethodFiltered('not_exist'))

  def test_getFilterExpression(self):
    request = dict(z_dummy_method_box=1, z_dummy_method_expression='python: 1')
    self._catalog.manage_editFilter(REQUEST=request)
    self.assertEquals('python: 1', self._catalog.getExpression('z_dummy_method'))
    self.assertEquals('', self._catalog.getExpression('not_exists'))

  def test_getFilterExpressionInstance(self):
    request = dict(z_dummy_method_box=1, z_dummy_method_expression='python: 1')
    self._catalog.manage_editFilter(REQUEST=request)
    self.assertTrue(isinstance(
        self._catalog.getExpressionInstance('z_dummy_method'), Expression))
    self.assertEquals(None, self._catalog.getExpressionInstance('not_exists'))

  def test_isPortalTypeSelected(self):
    request = dict(z_dummy_method_box=1, z_dummy_method_type=['Selected'])
    self._catalog.manage_editFilter(REQUEST=request)
    self.assertTrue(
        self._catalog.isPortalTypeSelected('z_dummy_method', 'Selected'))
    self.assertFalse(
        self._catalog.isPortalTypeSelected('z_dummy_method', 'Not Selected'))
    self.assertFalse(
        self._catalog.isPortalTypeSelected('not_exists', 'Selected'))


99
class TestQuery(unittest.TestCase):
100 101
  """Test SQL bits generated from Queries
  """
102 103 104
  def testSimpleQuery(self):
    q = Query(title='Foo')
    self.assertEquals(
Ivan Tyagov's avatar
Ivan Tyagov committed
105
          dict(where_expression="((((title = 'Foo'))))",
106
               select_expression_list=[]),
Ivan Tyagov's avatar
Ivan Tyagov committed
107 108 109
          q.asSQLExpression(keyword_search_keys=[],
                            datetime_search_keys = [],
                            full_text_search_keys=[]))
110

111 112 113 114 115
  def testQueryMultipleKeys(self):
    # using multiple keys is invalid and raises
    # KeyError: 'Query must have only one key'
    self.assertRaises(KeyError, Query, title='Foo', reference='bar')

116 117 118 119 120
  def testNoneQuery(self):
    q = Query(title=None)
    self.assertEquals(
          dict(where_expression="title is NULL",
               select_expression_list=[]),
Ivan Tyagov's avatar
Ivan Tyagov committed
121 122 123
          q.asSQLExpression(keyword_search_keys=[], 
                            datetime_search_keys = [],
                            full_text_search_keys=[]))
124 125 126 127 128 129 130 131 132 133

  def testEmptyQueryNotIgnoreEmptyString(self):
    q = Query(title='')
    # if you want to search with an empty string, pass ignore_empty_string=0 to
    # asSQLExpression. XXX not to __init__ ?
    self.assertEquals(
          dict(where_expression="title = ''",
               select_expression_list=[]),
          q.asSQLExpression(ignore_empty_string=0,
                            keyword_search_keys=[],
Ivan Tyagov's avatar
Ivan Tyagov committed
134
                            datetime_search_keys = [],
135 136 137 138 139 140 141 142
                            full_text_search_keys=[]))

  def testEmptyQuery(self):
    q = Query(title='')
    # query are true by default
    self.assertEquals(
          dict(where_expression="1",
               select_expression_list=[]),
Ivan Tyagov's avatar
Ivan Tyagov committed
143 144 145
          q.asSQLExpression(keyword_search_keys=[], 
                            datetime_search_keys = [],
                            full_text_search_keys=[]))
146 147 148 149
    
  def testMultiValuedQuery(self):
    q = Query(title=['Foo', 'Bar'])
    self.assertEquals(
Ivan Tyagov's avatar
Ivan Tyagov committed
150
          dict(where_expression="(((((title = 'Foo')))) OR ((((title = 'Bar')))))",
151
               select_expression_list=[]),
Ivan Tyagov's avatar
Ivan Tyagov committed
152 153 154
          q.asSQLExpression(keyword_search_keys=[], 
                            datetime_search_keys = [],
                            full_text_search_keys=[]))
155

156 157 158 159 160
  def testINQuery(self):
    q = Query(title=['Foo', 'Bar'], operator='IN')
    self.assertEquals(
          dict(where_expression="title IN ('Foo', 'Bar')",
               select_expression_list=[]),
Ivan Tyagov's avatar
Ivan Tyagov committed
161 162 163
          q.asSQLExpression(keyword_search_keys=[], 
                            datetime_search_keys = [],
                            full_text_search_keys=[]))
164 165 166 167 168 169

  def testEmptyINQuery(self):
    q = Query(title=[], operator='IN')
    self.assertEquals(
          dict(where_expression="0",
               select_expression_list=[]),
Ivan Tyagov's avatar
Ivan Tyagov committed
170 171 172
          q.asSQLExpression(keyword_search_keys=[],
                            datetime_search_keys = [],
                            full_text_search_keys=[]))
173

174 175 176 177 178
  def testMinQuery(self):
    q = Query(title='Foo', range='min')
    self.assertEquals(
          dict(where_expression="title >= 'Foo'",
               select_expression_list=[]),
Ivan Tyagov's avatar
Ivan Tyagov committed
179 180 181
          q.asSQLExpression(keyword_search_keys=[], 
                            datetime_search_keys = [],
                            full_text_search_keys=[]))
182 183 184 185 186 187
    
  def testMaxQuery(self):
    q = Query(title='Foo', range='max')
    self.assertEquals(
          dict(where_expression="title < 'Foo'",
               select_expression_list=[]),
Ivan Tyagov's avatar
Ivan Tyagov committed
188 189 190
          q.asSQLExpression(keyword_search_keys=[], 
                            datetime_search_keys = [],
                            full_text_search_keys=[]))
191

192
  # format
193
  def testDateFormat(self):
Ivan Tyagov's avatar
Ivan Tyagov committed
194 195
    date = DateTime(2001, 02, 03)
    q = Query(date=date, format='%Y/%m/%d', type='date')
196 197
    self.assertEquals(
          dict(where_expression=
Ivan Tyagov's avatar
Ivan Tyagov committed
198 199
            "((((date >= '%s' AND date < '%s'))))" \
                 %(date.toZone('UTC').ISO(), (date + 1).toZone('UTC').ISO()),
200
               select_expression_list=[]),
Ivan Tyagov's avatar
Ivan Tyagov committed
201 202 203
          q.asSQLExpression(keyword_search_keys=[], 
                            datetime_search_keys = [],
                            full_text_search_keys=[]))
204
  
205
  # full text
206 207 208 209 210 211
  def testSimpleQueryFullText(self):
    q = Query(title='Foo')
    self.assertEquals(dict(where_expression="MATCH title AGAINST ('Foo' )",
                           select_expression_list=
                        ["MATCH title AGAINST ('Foo' ) AS title_relevance"]),
          q.asSQLExpression(keyword_search_keys=[],
Ivan Tyagov's avatar
Ivan Tyagov committed
212
                            datetime_search_keys = [],
213 214
                            full_text_search_keys=['title']))

215 216 217 218 219 220 221 222
  def testSimpleQueryFullTextSearchMode(self):
    q = Query(title='Foo',
              search_mode='in_boolean_mode')
    self.assertEquals(dict(
      where_expression="MATCH title AGAINST ('Foo' IN BOOLEAN MODE)",
      select_expression_list=
        ["MATCH title AGAINST ('Foo' IN BOOLEAN MODE) AS title_relevance"]),
          q.asSQLExpression(keyword_search_keys=[],
Ivan Tyagov's avatar
Ivan Tyagov committed
223
                            datetime_search_keys = [],
224 225 226 227 228 229 230 231 232 233
                            full_text_search_keys=['title']))
  
  def testSimpleQueryFullTextStat__(self):
    # stat__ is an internal implementation artifact to prevent adding
    # select_expression for countFolder
    q = Query(title='Foo')
    self.assertEquals(dict(
                    where_expression="MATCH title AGAINST ('Foo' )",
                    select_expression_list=[]),
          q.asSQLExpression(keyword_search_keys=[],
Ivan Tyagov's avatar
Ivan Tyagov committed
234
                            datetime_search_keys = [],
235 236 237 238
                            full_text_search_keys=['title'],
                            stat__=1))

  def testSimpleQueryKeywordSearchKey(self):
239
    q = Query(title='Foo')
Ivan Tyagov's avatar
Ivan Tyagov committed
240
    self.assertEquals(dict(where_expression="((((title LIKE '%Foo%'))))",
241 242
                           select_expression_list=[]),
          q.asSQLExpression(keyword_search_keys=['title'],
Ivan Tyagov's avatar
Ivan Tyagov committed
243
                            datetime_search_keys = [],
244 245
                            full_text_search_keys=[]))

246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269
  def testQueryKeywordSearchKeyWithPercent(self):
    q = Query(title='F%o')
    self.assertEquals(dict(where_expression="((((title LIKE '%F%o%'))))",
                           select_expression_list=[]),
          q.asSQLExpression(keyword_search_keys=['title'],
                            datetime_search_keys = [],
                            full_text_search_keys=[]))

  def testQueryKeywordSearchKeyWithMinus(self):
    q = Query(title='F-o')
    self.assertEquals(dict(where_expression="((((title LIKE '%F-o%'))))",
                           select_expression_list=[]),
          q.asSQLExpression(keyword_search_keys=['title'],
                            datetime_search_keys = [],
                            full_text_search_keys=[]))

  def testQueryKeywordSearchKeyWithSpace(self):
    q = Query(title='F o')
    self.assertEquals(dict(where_expression="((((title LIKE '%F o%'))))",
                           select_expression_list=[]),
          q.asSQLExpression(keyword_search_keys=['title'],
                            datetime_search_keys = [],
                            full_text_search_keys=[]))

270 271 272 273
  def testNegatedQuery(self):
    q1 = Query(title='Foo')
    q = NegatedQuery(q1)
    self.assertEquals(
Ivan Tyagov's avatar
Ivan Tyagov committed
274
        dict(where_expression="(NOT (((((title = 'Foo'))))))",
275 276
                           select_expression_list=[]),
          q.asSQLExpression(keyword_search_keys=[],
Ivan Tyagov's avatar
Ivan Tyagov committed
277
                            datetime_search_keys = [],
278 279
                            full_text_search_keys=[]))

280
  # complex queries
281 282 283 284 285
  def testSimpleComplexQuery(self):
    q1 = Query(title='Foo')
    q2 = Query(reference='Bar')
    q = ComplexQuery(q1, q2)
    self.assertEquals(
Ivan Tyagov's avatar
Ivan Tyagov committed
286
        dict(where_expression="((((((title = 'Foo'))))) AND (((((reference = 'Bar'))))))",
287 288
                           select_expression_list=[]),
          q.asSQLExpression(keyword_search_keys=[],
Ivan Tyagov's avatar
Ivan Tyagov committed
289
                            datetime_search_keys = [],
290 291
                            full_text_search_keys=[]))

292 293 294 295 296 297 298
  def testNegatedComplexQuery(self):
    q1 = Query(title='Foo')
    q2 = Query(reference='Bar')
    q3 = ComplexQuery(q1, q2)
    q = NegatedQuery(q3)
    self.assertEquals(
      # maybe too many parents here
Ivan Tyagov's avatar
Ivan Tyagov committed
299
     dict(where_expression="(NOT (((((((title = 'Foo'))))) AND (((((reference = 'Bar'))))))))",
300 301
          select_expression_list=[]),
     q.asSQLExpression(keyword_search_keys=[],
Ivan Tyagov's avatar
Ivan Tyagov committed
302
                       datetime_search_keys = [],
303
                       full_text_search_keys=[]))
304

305 306 307 308
  
  # forced keys
  def testSimpleQueryForcedKeywordSearchKey(self):
    q = Query(title='Foo', key='Keyword')
Ivan Tyagov's avatar
Ivan Tyagov committed
309
    self.assertEquals("((((title LIKE '%Foo%'))))",
310
          q.asSQLExpression(keyword_search_keys=[],
Ivan Tyagov's avatar
Ivan Tyagov committed
311
                            datetime_search_keys = [],
312 313 314 315 316 317
                            full_text_search_keys=[])['where_expression'])

  def testSimpleQueryForcedFullText(self):
    q = Query(title='Foo', key='FullText')
    self.assertEquals("MATCH title AGAINST ('Foo' )",
          q.asSQLExpression(keyword_search_keys=[],
Ivan Tyagov's avatar
Ivan Tyagov committed
318
                            datetime_search_keys = [],                            
319 320 321 322 323 324
                            full_text_search_keys=[])['where_expression'])

  def testSimpleQueryForcedExactMatch(self):
    q = Query(title='Foo', key='ExactMatch')
    self.assertEquals("title = 'Foo'",
          q.asSQLExpression(keyword_search_keys=['title'],
Ivan Tyagov's avatar
Ivan Tyagov committed
325
                            datetime_search_keys = [],  
326 327 328 329 330 331
                            full_text_search_keys=[])['where_expression'])

  def testSimpleQueryForcedExactMatchOR(self):
    q = Query(title='Foo% OR %?ar', key='ExactMatch')
    self.assertEquals("title = 'Foo% OR %?ar'",
          q.asSQLExpression(keyword_search_keys=['title'],
Ivan Tyagov's avatar
Ivan Tyagov committed
332
                            datetime_search_keys = [],
333 334
                            full_text_search_keys=[])['where_expression'])

Jérome Perrin's avatar
Jérome Perrin committed
335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380
  def testQuotedStringDefaultKey(self):
    q = Query(title='Foo d\'Ba')
    self.assertEquals(
              dict(where_expression="((((title = 'Foo d''Ba'))))",
                   select_expression_list=[]),
                q.asSQLExpression())

  def testQuotedStringKeywordKey(self):
    q = Query(title='Foo d\'Ba', type='keyword')
    self.assertEquals(
              dict(where_expression="((((title LIKE '%Foo d''Ba%'))))",
                   select_expression_list=[]),
                q.asSQLExpression())

  def testQuotedStringFullTextKey(self):
    q = Query(title='Foo d\'Ba', type='fulltext')
    self.assertEquals(
        dict(where_expression="MATCH title AGAINST ('Foo d''Ba' )",
             select_expression_list=["MATCH title AGAINST ('Foo d''Ba' )"
                                     " AS title_relevance"]),
          q.asSQLExpression())

  def testQuotedStringDateKey(self):
    q = Query(title='Foo d\'Ba', type='date')
    self.assertEquals(
        # I don't know exactly what we should expect here.
              dict(where_expression="1",
                   select_expression_list=[]),
                q.asSQLExpression())

  def testQuotedStringFloatKey(self):
    q = Query(title='Foo d\'Ba', type='float')
    self.assertEquals(
        # I don't know exactly what we should expect here.
        # At least it's safe.
              dict(where_expression="1",
                   select_expression_list=[]),
                q.asSQLExpression())

  def testQuotedStringIntKey(self):
    q = Query(title='Foo d\'Ba', type='int')
    self.assertEquals(
              dict(where_expression="((((title = 'Foo d''Ba'))))",
                   select_expression_list=[]),
                q.asSQLExpression())

381

382 383 384 385
def test_suite():
  suite = unittest.TestSuite()
  suite.addTest(unittest.makeSuite(TestSQLCatalog))
  suite.addTest(unittest.makeSuite(TestZSQLCatalog))
386
  suite.addTest(unittest.makeSuite(TestQuery))
387 388
  return suite