unittest_querier.py

# -*- coding: iso-8859-1 -*-
# copyright 2003-2016 LOGILAB S.A. (Paris, FRANCE), all rights reserved.
# contact http://www.logilab.fr/ -- mailto:contact@logilab.fr
#
# This file is part of CubicWeb.
#
# CubicWeb is free software: you can redistribute it and/or modify it under the
# terms of the GNU Lesser General Public License as published by the Free
# Software Foundation, either version 2.1 of the License, or (at your option)
# any later version.
#
# CubicWeb 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 Lesser General Public License for more
# details.
#
# You should have received a copy of the GNU Lesser General Public License along
# with CubicWeb.  If not, see <http://www.gnu.org/licenses/>.
"""unit tests for modules cubicweb.server.querier and cubicweb.server.ssplanner
"""

from datetime import date, datetime, timedelta, tzinfo
import unittest

import pytz

from six import PY2, integer_types, binary_type, text_type

from rql import BadRQLQuery
from rql.utils import register_function, FunctionDescr

from cubicweb import QueryError, Unauthorized, Binary, devtools
from cubicweb.server.sqlutils import SQL_CONNECT_HOOKS, SQL_PREFIX
from cubicweb.server.utils import crypt_password
from cubicweb.server.querier import manual_build_descr, _make_description
from cubicweb.devtools.testlib import CubicWebTC
from cubicweb.devtools.repotest import tuplify, BaseQuerierTC


class FixedOffset(tzinfo):
    def __init__(self, hours=0):
        self.hours = hours

    def utcoffset(self, dt):
        return timedelta(hours=self.hours)

    def dst(self, dt):
        return timedelta(0)


# register priority/severity sorting registered procedure

class group_sort_value(FunctionDescr):
    supported_backends = ('sqlite',)
    rtype = 'Int'

try:
    register_function(group_sort_value)
except AssertionError:
    pass


def init_sqlite_connexion(cnx):

    def group_sort_value(text):
        return {"managers": "3", "users": "2", "guests": "1", "owners": "0"}[text]

    cnx.create_function("GROUP_SORT_VALUE", 1, group_sort_value)

SQL_CONNECT_HOOKS['sqlite'].append(init_sqlite_connexion)


def setUpClass(cls, *args):
    global repo, cnx
    config = devtools.TestServerConfiguration('data', __file__)
    handler = devtools.get_test_db_handler(config)
    handler.build_db_cache()
    repo, cnx = handler.get_repo_and_cnx()
    cls.repo = repo

def tearDownClass(cls, *args):
    global repo, cnx
    repo.shutdown()
    del repo, cnx


class Variable:
    def __init__(self, name):
        self.name = name
        self.children = []

    def get_type(self, solution, args=None):
        return solution[self.name]
    def as_string(self):
        return self.name

class Function:
    def __init__(self, name, varname):
        self.name = name
        self.children = [Variable(varname)]
    def get_type(self, solution, args=None):
        return 'Int'

class MakeDescriptionTC(unittest.TestCase):
    def test_known_values(self):
        solution = {'A': 'Int', 'B': 'CWUser'}
        self.assertEqual(_make_description((Function('max', 'A'), Variable('B')), {}, solution),
                          ['Int','CWUser'])


class UtilsTC(BaseQuerierTC):
    setUpClass = classmethod(setUpClass)
    tearDownClass = classmethod(tearDownClass)

    def get_max_eid(self):
        # no need for cleanup here
        return None
    def cleanup(self):
        # no need for cleanup here
        pass

    def test_preprocess_1(self):
        with self._access.cnx() as cnx:
            reid = cnx.execute('Any X WHERE X is CWRType, X name "owned_by"')[0][0]
            rqlst = self._prepare(cnx, 'Any COUNT(RDEF) WHERE RDEF relation_type X, X eid %(x)s',
                                  {'x': reid})
            self.assertEqual([{'RDEF': 'CWAttribute'}, {'RDEF': 'CWRelation'}],
                             rqlst.solutions)

    def test_preprocess_2(self):
        with self._access.cnx() as cnx:
            teid = cnx.execute("INSERT Tag X: X name 'tag'")[0][0]
            #geid = self.execute("CWGroup G WHERE G name 'users'")[0][0]
            #self.execute("SET X tags Y WHERE X eid %(t)s, Y eid %(g)s",
            #             {'g': geid, 't': teid}, 'g')
            rqlst = self._prepare(cnx, 'Any X WHERE E eid %(x)s, E tags X', {'x': teid})
            # the query may be optimized, should keep only one solution
            # (any one, etype will be discarded)
            self.assertEqual(1, len(rqlst.solutions))

    def assertRQLEqual(self, expected, got):
        from rql import parse
        self.assertMultiLineEqual(text_type(parse(expected)),
                                  text_type(parse(got)))

    def test_preprocess_security(self):
        with self.user_groups_session('users') as cnx:
            plan = self._prepare_plan(cnx, 'Any ETN,COUNT(X) GROUPBY ETN '
                                      'WHERE X is ET, ET name ETN')
            union = plan.rqlst
            plan.preprocess(union)
            self.assertEqual(len(union.children), 1)
            self.assertEqual(len(union.children[0].with_), 1)
            subq = union.children[0].with_[0].query
            self.assertEqual(len(subq.children), 4)
            self.assertEqual([t.as_string() for t in union.children[0].selection],
                              ['ETN','COUNT(X)'])
            self.assertEqual([t.as_string() for t in union.children[0].groupby],
                              ['ETN'])
            partrqls = sorted(((rqlst.as_string(), rqlst.solutions) for rqlst in subq.children))
            rql, solutions = partrqls[0]
            self.assertRQLEqual(rql,
                                'Any ETN,X WHERE X is ET, ET name ETN, (EXISTS(X owned_by %(B)s))'
                                ' OR ((((EXISTS(D concerne C?, C owned_by %(B)s, '
                                '               X identity D, C is Division, D is Affaire))'
                                ' OR (EXISTS(H concerne G?, G owned_by %(B)s, G is SubDivision, '
                                '            X identity H, H is Affaire)))'
                                ' OR (EXISTS(I concerne F?, F owned_by %(B)s, F is Societe, '
                                '            X identity I, I is Affaire)))'
                                ' OR (EXISTS(J concerne E?, E owned_by %(B)s, E is Note, '
                                '            X identity J, J is Affaire)))'
                                ', ET is CWEType, X is Affaire')
            self.assertEqual(solutions, [{'C': 'Division',
                                           'D': 'Affaire',
                                           'E': 'Note',
                                           'F': 'Societe',
                                           'G': 'SubDivision',
                                           'H': 'Affaire',
                                           'I': 'Affaire',
                                           'J': 'Affaire',
                                           'X': 'Affaire',
                                           'ET': 'CWEType', 'ETN': 'String'}])
            rql, solutions = partrqls[1]
            self.assertRQLEqual(rql,  'Any ETN,X WHERE X is ET, ET name ETN, ET is CWEType, '
                                'X is IN(BaseTransition, Bookmark, CWAttribute, CWCache, CWComputedRType, '
                                '        CWConstraint, CWConstraintType, CWEType, CWGroup, CWPermission, CWProperty, '
                                '        CWRType, CWRelation, CWSource, CWUniqueTogetherConstraint, CWUser, Card, '
                                '        Comment, Division, Email, EmailPart, EmailThread, ExternalUri, File, Folder, '
                                '        Frozable, Note, Old, Personne, RQLExpression, Societe, State, SubDivision, '
                                '        SubWorkflowExitPoint, Tag, TrInfo, Transition, Workflow, WorkflowTransition)')
            self.assertCountEqual(solutions,
                                  [{'X': 'BaseTransition', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'Bookmark', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'Card', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'Comment', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'Division', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'CWCache', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'CWComputedRType', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'CWConstraint', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'CWConstraintType', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'CWEType', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'CWAttribute', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'CWGroup', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'CWRelation', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'CWPermission', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'CWProperty', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'CWRType', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'CWSource', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'CWUniqueTogetherConstraint', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'CWUser', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'Email', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'EmailPart', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'EmailThread', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'ExternalUri', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'File', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'Folder', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'Frozable', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'Note', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'Old', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'Personne', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'RQLExpression', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'Societe', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'State', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'SubDivision', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'SubWorkflowExitPoint', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'Tag', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'Transition', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'TrInfo', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'Workflow', 'ETN': 'String', 'ET': 'CWEType'},
                                   {'X': 'WorkflowTransition', 'ETN': 'String', 'ET': 'CWEType'}])
            rql, solutions = partrqls[2]
            self.assertEqual(rql,
                             'Any ETN,X WHERE X is ET, ET name ETN, EXISTS(%(D)s use_email X), '
                             'ET is CWEType, X is EmailAddress')
            self.assertEqual(solutions, [{'X': 'EmailAddress', 'ET': 'CWEType', 'ETN': 'String'}])
            rql, solutions = partrqls[3]
            self.assertEqual(rql,
                              'Any ETN,X WHERE X is ET, ET name ETN, EXISTS(X owned_by %(C)s), '
                              'ET is CWEType, X is Basket')
            self.assertEqual(solutions, [{'X': 'Basket', 'ET': 'CWEType', 'ETN': 'String'}])

    def test_preprocess_security_aggregat(self):
        with self.user_groups_session('users') as cnx:
            plan = self._prepare_plan(cnx, 'Any MAX(X)')
            union = plan.rqlst
            plan.preprocess(union)
            self.assertEqual(len(union.children), 1)
            self.assertEqual(len(union.children[0].with_), 1)
            subq = union.children[0].with_[0].query
            self.assertEqual(len(subq.children), 4)
            self.assertEqual([t.as_string() for t in union.children[0].selection],
                              ['MAX(X)'])

    def test_preprocess_nonregr(self):
        with self._access.cnx() as cnx:
            rqlst = self._prepare(cnx, 'Any S ORDERBY SI WHERE NOT S ecrit_par O, S para SI')
            self.assertEqual(len(rqlst.solutions), 1)

    def test_build_description(self):
        # should return an empty result set
        rset = self.qexecute('Any X WHERE X eid %(x)s', {'x': self._access._user.eid})
        self.assertEqual(rset.description[0][0], 'CWUser')
        rset = self.qexecute('Any 1')
        self.assertEqual(rset.description[0][0], 'Int')
        rset = self.qexecute('Any TRUE')
        self.assertEqual(rset.description[0][0], 'Boolean')
        rset = self.qexecute('Any "hop"')
        self.assertEqual(rset.description[0][0], 'String')
        rset = self.qexecute('Any TODAY')
        self.assertEqual(rset.description[0][0], 'Date')
        rset = self.qexecute('Any NOW')
        self.assertEqual(rset.description[0][0], 'Datetime')
        rset = self.qexecute('Any %(x)s', {'x': 1})
        self.assertEqual(rset.description[0][0], 'Int')
        if PY2:
            rset = self.qexecute('Any %(x)s', {'x': long(1)})
            self.assertEqual(rset.description[0][0], 'Int')
        rset = self.qexecute('Any %(x)s', {'x': True})
        self.assertEqual(rset.description[0][0], 'Boolean')
        rset = self.qexecute('Any %(x)s', {'x': 1.0})
        self.assertEqual(rset.description[0][0], 'Float')
        rset = self.qexecute('Any %(x)s', {'x': datetime.now()})
        self.assertEqual(rset.description[0][0], 'Datetime')
        rset = self.qexecute('Any %(x)s', {'x': 'str'})
        self.assertEqual(rset.description[0][0], 'String')
        rset = self.qexecute('Any %(x)s', {'x': u'str'})
        self.assertEqual(rset.description[0][0], 'String')

    def test_build_descr1(self):
        with self._access.cnx() as cnx:
            rset = cnx.execute('(Any U,L WHERE U login L) UNION '
                               '(Any G,N WHERE G name N, G is CWGroup)')
            orig_length = len(rset)
            rset.rows[0][0] = 9999999
            description = manual_build_descr(cnx, rset.syntax_tree(), None, rset.rows)
            self.assertEqual(len(description), orig_length - 1)
            self.assertEqual(len(rset.rows), orig_length - 1)
            self.assertNotEqual(rset.rows[0][0], 9999999)

    def test_build_descr2(self):
        rset = self.qexecute('Any X,Y WITH X,Y BEING ((Any G,NULL WHERE G is CWGroup) UNION '
                             '(Any U,G WHERE U in_group G))')
        for x, y in rset.description:
            if y is not None:
                self.assertEqual(y, 'CWGroup')

    def test_build_descr3(self):
        rset = self.qexecute('(Any G,NULL WHERE G is CWGroup) UNION '
                             '(Any U,G WHERE U in_group G)')
        for x, y in rset.description:
            if y is not None:
                self.assertEqual(y, 'CWGroup')


class QuerierTC(BaseQuerierTC):
    setUpClass = classmethod(setUpClass)
    tearDownClass = classmethod(tearDownClass)

    def test_unknown_eid(self):
        # should return an empty result set
        self.assertFalse(self.qexecute('Any X WHERE X eid 99999999'))

    def test_typed_eid(self):
        # should return an empty result set
        rset = self.qexecute('Any X WHERE X eid %(x)s', {'x': '1'})
        self.assertIsInstance(rset[0][0], integer_types)

    def test_bytes_storage(self):
        feid = self.qexecute('INSERT File X: X data_name "foo.pdf", '
                             'X data_format "text/plain", X data %(data)s',
                            {'data': Binary(b"xxx")})[0][0]
        fdata = self.qexecute('Any D WHERE X data D, X eid %(x)s', {'x': feid})[0][0]
        self.assertIsInstance(fdata, Binary)
        self.assertEqual(fdata.getvalue(), b'xxx')

    # selection queries tests #################################################

    def test_select_1(self):
        rset = self.qexecute('Any X ORDERBY X WHERE X is CWGroup')
        result, descr = rset.rows, rset.description
        self.assertEqual(tuplify(result), [(2,), (3,), (4,), (5,)])
        self.assertEqual(descr, [('CWGroup',), ('CWGroup',), ('CWGroup',), ('CWGroup',)])

    def test_select_2(self):
        rset = self.qexecute('Any X ORDERBY N WHERE X is CWGroup, X name N')
        self.assertEqual(tuplify(rset.rows), [(2,), (3,), (4,), (5,)])
        self.assertEqual(rset.description, [('CWGroup',), ('CWGroup',), ('CWGroup',), ('CWGroup',)])
        rset = self.qexecute('Any X ORDERBY N DESC WHERE X is CWGroup, X name N')
        self.assertEqual(tuplify(rset.rows), [(5,), (4,), (3,), (2,)])

    def test_select_3(self):
        rset = self.qexecute('Any N GROUPBY N WHERE X is CWGroup, X name N')
        result, descr = rset.rows, rset.description
        result.sort()
        self.assertEqual(tuplify(result), [('guests',), ('managers',), ('owners',), ('users',)])
        self.assertEqual(descr, [('String',), ('String',), ('String',), ('String',)])

    def test_select_is(self):
        rset = self.qexecute('Any X, TN ORDERBY TN LIMIT 10 WHERE X is T, T name TN')
        result, descr = rset.rows, rset.description
        self.assertEqual(result[0][1], descr[0][0])

    def test_select_is_aggr(self):
        rset = self.qexecute('Any TN, COUNT(X) GROUPBY TN ORDERBY 2 DESC WHERE X is T, T name TN')
        result, descr = rset.rows, rset.description
        self.assertEqual(descr[0][0], 'String')
        self.assertEqual(descr[0][1], 'Int')
        self.assertEqual(result[0][0], 'RQLExpression') # XXX may change as schema evolve

    def test_select_groupby_orderby(self):
        rset = self.qexecute('Any N GROUPBY N ORDERBY N WHERE X is CWGroup, X name N')
        self.assertEqual(tuplify(rset.rows), [('guests',), ('managers',), ('owners',), ('users',)])
        self.assertEqual(rset.description, [('String',), ('String',), ('String',), ('String',)])

    def test_select_complex_groupby(self):
        rset = self.qexecute('Any N GROUPBY N WHERE X name N')
        rset = self.qexecute('Any N,MAX(D) GROUPBY N LIMIT 5 WHERE X name N, X creation_date D')

    def test_select_inlined_groupby(self):
        seid = self.qexecute('State X WHERE X name "deactivated"')[0][0]
        rset = self.qexecute('Any U,L,S GROUPBY U,L,S WHERE X in_state S, U login L, S eid %s' % seid)

    def test_select_groupby_funccall(self):
        rset = self.qexecute('Any YEAR(CD), COUNT(X) GROUPBY YEAR(CD) '
                             'WHERE X is CWUser, X creation_date CD')
        self.assertListEqual(rset.rows, [[date.today().year, 2]])

    def test_select_groupby_colnumber(self):
        rset = self.qexecute('Any YEAR(CD), COUNT(X) GROUPBY 1 '
                             'WHERE X is CWUser, X creation_date CD')
        self.assertListEqual(rset.rows, [[date.today().year, 2]])

    def test_select_complex_orderby(self):
        rset1 = self.qexecute('Any N ORDERBY N WHERE X name N')
        self.assertEqual(sorted(rset1.rows), rset1.rows)
        rset = self.qexecute('Any N ORDERBY N LIMIT 5 OFFSET 1 WHERE X name N')
        self.assertEqual(rset.rows[0][0], rset1.rows[1][0])
        self.assertEqual(len(rset), 5)

    def test_select_5(self):
        rset = self.qexecute('Any X, TMP ORDERBY TMP WHERE X name TMP, X is CWGroup')
        self.assertEqual(tuplify(rset.rows),
                         [(2, 'guests',),
                          (3, 'managers',),
                          (4, 'owners',),
                          (5, 'users',)])
        self.assertEqual(rset.description,
                         [('CWGroup', 'String',),
                          ('CWGroup', 'String',),
                          ('CWGroup', 'String',),
                          ('CWGroup', 'String',)])

    def test_select_6(self):
        self.qexecute("INSERT Personne X: X nom 'bidule'")[0]
        rset = self.qexecute('Any Y where X name TMP, Y nom in (TMP, "bidule")')
        #self.assertEqual(rset.description, [('Personne',), ('Personne',)])
        self.assertIn(('Personne',), rset.description)
        rset = self.qexecute('DISTINCT Any Y where X name TMP, Y nom in (TMP, "bidule")')
        self.assertIn(('Personne',), rset.description)

    def test_select_not_attr(self):
        peid = self.qexecute("INSERT Personne X: X nom 'bidule'")[0][0]
        seid = self.qexecute("INSERT Societe X: X nom 'chouette'")[0][0]
        rset = self.qexecute('Personne X WHERE NOT X nom "bidule"')
        self.assertEqual(len(rset.rows), 0, rset.rows)
        rset = self.qexecute('Personne X WHERE NOT X nom "bid"')
        self.assertEqual(len(rset.rows), 1, rset.rows)
        self.qexecute("SET P travaille S WHERE P nom 'bidule', S nom 'chouette'")
        rset = self.qexecute('Personne X WHERE NOT X travaille S')
        self.assertEqual(len(rset.rows), 0, rset.rows)

    def test_select_is_in(self):
        self.qexecute("INSERT Personne X: X nom 'bidule'")
        self.qexecute("INSERT Societe X: X nom 'chouette'")
        self.assertEqual(len(self.qexecute("Any X WHERE X is IN (Personne, Societe)")),
                          2)

    def test_select_not_rel(self):
        self.qexecute("INSERT Personne X: X nom 'bidule'")
        self.qexecute("INSERT Societe X: X nom 'chouette'")
        self.qexecute("INSERT Personne X: X nom 'autre'")
        self.qexecute("SET P travaille S WHERE P nom 'bidule', S nom 'chouette'")
        rset = self.qexecute('Personne X WHERE NOT X travaille S')
        self.assertEqual(len(rset.rows), 1, rset.rows)
        rset = self.qexecute('Personne X WHERE NOT X travaille S, S nom "chouette"')
        self.assertEqual(len(rset.rows), 1, rset.rows)

    def test_select_nonregr_inlined(self):
        self.qexecute("INSERT Note X: X para 'bidule'")
        self.qexecute("INSERT Personne X: X nom 'chouette'")
        self.qexecute("INSERT Personne X: X nom 'autre'")
        self.qexecute("SET X ecrit_par P WHERE X para 'bidule', P nom 'chouette'")
        rset = self.qexecute('Any U,T ORDERBY T DESC WHERE U is CWUser, '
                             'N ecrit_par U, N type T')#, {'x': self.ueid})
        self.assertEqual(len(rset.rows), 0)

    def test_select_nonregr_edition_not(self):
        groupeids = set((2, 3, 4))
        groupreadperms = set(r[0] for r in self.qexecute('Any Y WHERE X name "CWGroup", '
                                                         'Y eid IN(2, 3, 4), X read_permission Y'))
        rset = self.qexecute('DISTINCT Any Y WHERE X is CWEType, X name "CWGroup", '
                             'Y eid IN(2, 3, 4), NOT X read_permission Y')
        self.assertEqual(sorted(r[0] for r in rset.rows), sorted(groupeids - groupreadperms))
        rset = self.qexecute('DISTINCT Any Y WHERE X name "CWGroup", '
                             'Y eid IN(2, 3, 4), NOT X read_permission Y')
        self.assertEqual(sorted(r[0] for r in rset.rows), sorted(groupeids - groupreadperms))

    def test_select_outer_join(self):
        peid1 = self.qexecute("INSERT Personne X: X nom 'bidule'")[0][0]
        peid2 = self.qexecute("INSERT Personne X: X nom 'autre'")[0][0]
        seid1 = self.qexecute("INSERT Societe X: X nom 'chouette'")[0][0]
        seid2 = self.qexecute("INSERT Societe X: X nom 'chouetos'")[0][0]
        rset = self.qexecute('Any X,S ORDERBY X WHERE X travaille S?')
        self.assertEqual(rset.rows, [[peid1, None], [peid2, None]])
        self.qexecute("SET P travaille S WHERE P nom 'bidule', S nom 'chouette'")
        rset = self.qexecute('Any X,S ORDERBY X WHERE X travaille S?')
        self.assertEqual(rset.rows, [[peid1, seid1], [peid2, None]])
        rset = self.qexecute('Any S,X ORDERBY S WHERE X? travaille S')
        self.assertEqual(rset.rows, [[seid1, peid1], [seid2, None]])

    def test_select_outer_join_optimized(self):
        peid1 = self.qexecute("INSERT Personne X: X nom 'bidule'")[0][0]
        rset = self.qexecute('Any X WHERE X eid %(x)s, P? connait X', {'x':peid1})
        self.assertEqual(rset.rows, [[peid1]])
        rset = self.qexecute('Any X WHERE X eid %(x)s, X require_permission P?',
                            {'x':peid1})
        self.assertEqual(rset.rows, [[peid1]])

    def test_select_left_outer_join(self):
        rset = self.qexecute('DISTINCT Any G WHERE U? in_group G')
        self.assertEqual(len(rset), 4)
        rset = self.qexecute('DISTINCT Any G WHERE U? in_group G, U eid %(x)s',
                            {'x': self._access._user.eid})
        self.assertEqual(len(rset), 4)

    def test_select_ambigous_outer_join(self):
        teid = self.qexecute("INSERT Tag X: X name 'tag'")[0][0]
        self.qexecute("INSERT Tag X: X name 'tagbis'")[0][0]
        geid = self.qexecute("CWGroup G WHERE G name 'users'")[0][0]
        self.qexecute("SET X tags Y WHERE X eid %(t)s, Y eid %(g)s",
                     {'g': geid, 't': teid})
        rset = self.qexecute("Any GN,TN ORDERBY GN WHERE T? tags G, T name TN, G name GN")
        self.assertIn(['users', 'tag'], rset.rows)
        self.assertIn(['activated', None], rset.rows)
        rset = self.qexecute("Any GN,TN ORDERBY GN WHERE T tags G?, T name TN, G name GN")
        self.assertEqual(rset.rows, [[None, 'tagbis'], ['users', 'tag']])

    def test_select_not_inline_rel(self):
        self.qexecute("INSERT Personne X: X nom 'bidule'")
        self.qexecute("INSERT Note X: X type 'a'")
        self.qexecute("INSERT Note X: X type 'b'")
        self.qexecute("SET X ecrit_par Y WHERE X type 'a', Y nom 'bidule'")
        rset = self.qexecute('Note X WHERE NOT X ecrit_par P')
        self.assertEqual(len(rset.rows), 1, rset.rows)

    def test_select_not_unlinked_multiple_solutions(self):
        self.qexecute("INSERT Personne X: X nom 'bidule'")
        self.qexecute("INSERT Note X: X type 'a'")
        self.qexecute("INSERT Note X: X type 'b'")
        self.qexecute("SET Y evaluee X WHERE X type 'a', Y nom 'bidule'")
        rset = self.qexecute('Note X WHERE NOT Y evaluee X')
        self.assertEqual(len(rset.rows), 1, rset.rows)

    def test_select_date_extraction(self):
        self.qexecute("INSERT Personne X: X nom 'foo', X datenaiss %(d)s",
                     {'d': datetime(2001, 2,3, 12,13)})
        test_data = [('YEAR', 2001), ('MONTH', 2), ('DAY', 3),
                     ('HOUR', 12), ('MINUTE', 13), ('WEEKDAY', 6)]
        for funcname, result in test_data:
            rset = self.qexecute('Any %s(D) WHERE X is Personne, X datenaiss D'
                                % funcname)
            self.assertEqual(len(rset.rows), 1)
            self.assertEqual(rset.rows[0][0], result)
            self.assertEqual(rset.description, [('Int',)])

    def test_regexp_based_pattern_matching(self):
        peid1 = self.qexecute("INSERT Personne X: X nom 'bidule'")[0][0]
        peid2 = self.qexecute("INSERT Personne X: X nom 'cidule'")[0][0]
        rset = self.qexecute('Any X WHERE X is Personne, X nom REGEXP "^b"')
        self.assertEqual(len(rset.rows), 1, rset.rows)
        self.assertEqual(rset.rows[0][0], peid1)
        rset = self.qexecute('Any X WHERE X is Personne, X nom REGEXP "idu"')
        self.assertEqual(len(rset.rows), 2, rset.rows)

    def test_select_aggregat_count(self):
        rset = self.qexecute('Any COUNT(X)')
        self.assertEqual(len(rset.rows), 1)
        self.assertEqual(len(rset.rows[0]), 1)
        self.assertEqual(rset.description, [('Int',)])

    def test_select_aggregat_sum(self):
        rset = self.qexecute('Any SUM(O) WHERE X ordernum O')
        self.assertEqual(len(rset.rows), 1)
        self.assertEqual(len(rset.rows[0]), 1)
        self.assertEqual(rset.description, [('Int',)])

    def test_select_aggregat_min(self):
        rset = self.qexecute('Any MIN(X) WHERE X is Personne')
        self.assertEqual(len(rset.rows), 1)
        self.assertEqual(len(rset.rows[0]), 1)
        self.assertEqual(rset.description, [('Personne',)])
        rset = self.qexecute('Any MIN(O) WHERE X ordernum O')
        self.assertEqual(len(rset.rows), 1)
        self.assertEqual(len(rset.rows[0]), 1)
        self.assertEqual(rset.description, [('Int',)])

    def test_select_aggregat_max(self):
        rset = self.qexecute('Any MAX(X) WHERE X is Personne')
        self.assertEqual(len(rset.rows), 1)
        self.assertEqual(len(rset.rows[0]), 1)
        self.assertEqual(rset.description, [('Personne',)])
        rset = self.qexecute('Any MAX(O) WHERE X ordernum O')
        self.assertEqual(len(rset.rows), 1)
        self.assertEqual(len(rset.rows[0]), 1)
        self.assertEqual(rset.description, [('Int',)])

    def test_select_custom_aggregat_concat_string(self):
        rset = self.qexecute('Any GROUP_CONCAT(N) WHERE X is CWGroup, X name N')
        self.assertTrue(rset)
        self.assertEqual(sorted(rset[0][0].split(', ')), ['guests', 'managers',
                                                             'owners', 'users'])

    def test_select_custom_regproc_limit_size(self):
        rset = self.qexecute('Any TEXT_LIMIT_SIZE(N, 3) WHERE X is CWGroup, X name N, X name "managers"')
        self.assertTrue(rset)
        self.assertEqual(rset[0][0], 'man...')
        self.qexecute("INSERT Basket X: X name 'bidule', X description '<b>hop hop</b>', X description_format 'text/html'")
        rset = self.qexecute('Any LIMIT_SIZE(D, DF, 3) WHERE X is Basket, X description D, X description_format DF')
        self.assertTrue(rset)
        self.assertEqual(rset[0][0], 'hop...')

    def test_select_regproc_orderby(self):
        rset = self.qexecute('DISTINCT Any X,N ORDERBY GROUP_SORT_VALUE(N) WHERE X is CWGroup, X name N, X name "managers"')
        self.assertEqual(len(rset), 1)
        self.assertEqual(rset[0][1], 'managers')
        rset = self.qexecute('Any X,N ORDERBY GROUP_SORT_VALUE(N) WHERE X is CWGroup, X name N, NOT U in_group X, U login "admin"')
        self.assertEqual(len(rset), 3)
        self.assertEqual(rset[0][1], 'owners')

    def test_select_aggregat_sort(self):
        rset = self.qexecute('Any G, COUNT(U) GROUPBY G ORDERBY 2 WHERE U in_group G')
        self.assertEqual(len(rset.rows), 2)
        self.assertEqual(len(rset.rows[0]), 2)
        self.assertEqual(rset.description[0], ('CWGroup', 'Int',))

    def test_select_aggregat_having(self):
        rset = self.qexecute('Any N,COUNT(RDEF) GROUPBY N ORDERBY 2,N '
                            'WHERE RT name N, RDEF relation_type RT '
                            'HAVING COUNT(RDEF) > 10')
        self.assertListEqual(rset.rows,
                              [[u'description_format', 13],
                               [u'description', 14],
                               [u'name', 19],
                               [u'created_by', 45],
                               [u'creation_date', 45],
                               [u'cw_source', 45],
                               [u'cwuri', 45],
                               [u'in_basket', 45],
                               [u'is', 45],
                               [u'is_instance_of', 45],
                               [u'modification_date', 45],
                               [u'owned_by', 45]])

    def test_select_aggregat_having_dumb(self):
        # dumb but should not raise an error
        rset = self.qexecute('Any U,COUNT(X) GROUPBY U '
                            'WHERE U eid %(x)s, X owned_by U '
                            'HAVING COUNT(X) > 10', {'x': self.ueid})
        self.assertEqual(len(rset.rows), 1)
        self.assertEqual(rset.rows[0][0], self.ueid)

    def test_select_having_non_aggregat_1(self):
        rset = self.qexecute('Any L WHERE X login L, X creation_date CD '
                            'HAVING YEAR(CD) = %s' % date.today().year)
        self.assertListEqual(rset.rows,
                              [[u'admin'],
                               [u'anon']])

    def test_select_having_non_aggregat_2(self):
        rset = self.qexecute('Any L GROUPBY L WHERE X login L, X in_group G, '
                            'X creation_date CD HAVING YEAR(CD) = %s OR COUNT(G) > 1'
                            % date.today().year)
        self.assertListEqual(rset.rows,
                              [[u'admin'],
                               [u'anon']])

    def test_select_complex_sort(self):
        """need sqlite including http://www.sqlite.org/cvstrac/tktview?tn=3773 fix"""
        rset = self.qexecute('Any X ORDERBY X,D LIMIT 5 WHERE X creation_date D')
        result = rset.rows
        result.sort()
        self.assertEqual(tuplify(result), [(1,), (2,), (3,), (4,), (5,)])

    def test_select_upper(self):
        rset = self.qexecute('Any X, UPPER(L) ORDERBY L WHERE X is CWUser, X login L')
        self.assertEqual(len(rset.rows), 2)
        self.assertEqual(rset.rows[0][1], 'ADMIN')
        self.assertEqual(rset.description[0], ('CWUser', 'String',))
        self.assertEqual(rset.rows[1][1], 'ANON')
        self.assertEqual(rset.description[1], ('CWUser', 'String',))
        eid = rset.rows[0][0]
        rset = self.qexecute('Any UPPER(L) WHERE X eid %s, X login L'%eid)
        self.assertEqual(rset.rows[0][0], 'ADMIN')
        self.assertEqual(rset.description, [('String',)])

    def test_select_float_abs(self):
        # test positive number
        eid = self.qexecute('INSERT Affaire A: A invoiced %(i)s', {'i': 1.2})[0][0]
        rset = self.qexecute('Any ABS(I) WHERE X eid %(x)s, X invoiced I', {'x': eid})
        self.assertEqual(rset.rows[0][0], 1.2)
        # test negative number
        eid = self.qexecute('INSERT Affaire A: A invoiced %(i)s', {'i': -1.2})[0][0]
        rset = self.qexecute('Any ABS(I) WHERE X eid %(x)s, X invoiced I', {'x': eid})
        self.assertEqual(rset.rows[0][0], 1.2)

    def test_select_int_abs(self):
        # test positive number
        eid = self.qexecute('INSERT Affaire A: A duration %(d)s', {'d': 12})[0][0]
        rset = self.qexecute('Any ABS(D) WHERE X eid %(x)s, X duration D', {'x': eid})
        self.assertEqual(rset.rows[0][0], 12)
        # test negative number
        eid = self.qexecute('INSERT Affaire A: A duration %(d)s', {'d': -12})[0][0]
        rset = self.qexecute('Any ABS(D) WHERE X eid %(x)s, X duration D', {'x': eid})
        self.assertEqual(rset.rows[0][0], 12)

##     def test_select_simplified(self):
##         ueid = self._access._user.eid
##         rset = self.qexecute('Any L WHERE %s login L'%ueid)
##         self.assertEqual(rset.rows[0][0], 'admin')
##         rset = self.qexecute('Any L WHERE %(x)s login L', {'x':ueid})
##         self.assertEqual(rset.rows[0][0], 'admin')

    def test_select_searchable_text_1(self):
        rset = self.qexecute(u"INSERT Personne X: X nom 'bidle'")
        rset = self.qexecute(u"INSERT Societe X: X nom 'bidle'")
        rset = self.qexecute("INSERT Societe X: X nom 'chouette'")
        rset = self.qexecute('Any X where X has_text %(text)s', {'text': u'bidle'})
        self.assertEqual(len(rset.rows), 2, rset.rows)
        rset = self.qexecute(u'Any N where N has_text "bidle"')
        self.assertEqual(len(rset.rows), 2, rset.rows)
        biduleeids = [r[0] for r in rset.rows]
        rset = self.qexecute(u'Any N where NOT N has_text "bidle"')
        self.assertFalse([r[0] for r in rset.rows if r[0] in biduleeids])
        # duh?
        rset = self.qexecute('Any X WHERE X has_text %(text)s', {'text': u'a'})

    def test_select_searchable_text_2(self):
        rset = self.qexecute("INSERT Personne X: X nom 'bidule'")
        rset = self.qexecute("INSERT Personne X: X nom 'chouette'")
        rset = self.qexecute("INSERT Societe X: X nom 'bidule'")
        rset = self.qexecute('Personne N where N has_text "bidule"')
        self.assertEqual(len(rset.rows), 1, rset.rows)

    def test_select_searchable_text_3(self):
        rset = self.qexecute("INSERT Personne X: X nom 'bidule', X sexe 'M'")
        rset = self.qexecute("INSERT Personne X: X nom 'bidule', X sexe 'F'")
        rset = self.qexecute("INSERT Societe X: X nom 'bidule'")
        rset = self.qexecute('Any X where X has_text "bidule" and X sexe "M"')
        self.assertEqual(len(rset.rows), 1, rset.rows)

    def test_select_multiple_searchable_text(self):
        self.qexecute(u"INSERT Personne X: X nom 'bidle'")
        self.qexecute("INSERT Societe X: X nom 'chouette', S travaille X")
        self.qexecute(u"INSERT Personne X: X nom 'bidle'")
        rset = self.qexecute('Personne X WHERE X has_text %(text)s, X travaille S, S has_text %(text2)s',
                            {'text': u'bidle',
                             'text2': u'chouette',}
                            )
        self.assertEqual(len(rset.rows), 1, rset.rows)

    def test_select_no_descr(self):
        rset = self.qexecute('Any X WHERE X is CWGroup', build_descr=0)
        rset.rows.sort()
        self.assertEqual(tuplify(rset.rows), [(2,), (3,), (4,), (5,)])
        self.assertEqual(rset.description, ())

    def test_select_limit_offset(self):
        rset = self.qexecute('CWGroup X ORDERBY N LIMIT 2 WHERE X name N')
        self.assertEqual(tuplify(rset.rows), [(2,), (3,)])
        self.assertEqual(rset.description, [('CWGroup',), ('CWGroup',)])
        rset = self.qexecute('CWGroup X ORDERBY N LIMIT 2 OFFSET 2 WHERE X name N')
        self.assertEqual(tuplify(rset.rows), [(4,), (5,)])

    def test_select_symmetric(self):
        self.qexecute("INSERT Personne X: X nom 'machin'")
        self.qexecute("INSERT Personne X: X nom 'bidule'")
        self.qexecute("INSERT Personne X: X nom 'chouette'")
        self.qexecute("INSERT Personne X: X nom 'trucmuche'")
        self.qexecute("SET X connait Y WHERE X nom 'chouette', Y nom 'bidule'")
        self.qexecute("SET X connait Y WHERE X nom 'machin', Y nom 'chouette'")
        rset = self.qexecute('Any P WHERE P connait P2')
        self.assertEqual(len(rset.rows), 4, rset.rows)
        rset = self.qexecute('Any P WHERE NOT P connait P2')
        self.assertEqual(len(rset.rows), 1, rset.rows) # trucmuche
        rset = self.qexecute('Any P WHERE P connait P2, P2 nom "bidule"')
        self.assertEqual(len(rset.rows), 1, rset.rows)
        rset = self.qexecute('Any P WHERE P2 connait P, P2 nom "bidule"')
        self.assertEqual(len(rset.rows), 1, rset.rows)
        rset = self.qexecute('Any P WHERE P connait P2, P2 nom "chouette"')
        self.assertEqual(len(rset.rows), 2, rset.rows)
        rset = self.qexecute('Any P WHERE P2 connait P, P2 nom "chouette"')
        self.assertEqual(len(rset.rows), 2, rset.rows)

    def test_select_inline(self):
        self.qexecute("INSERT Personne X: X nom 'bidule'")
        self.qexecute("INSERT Note X: X type 'a'")
        self.qexecute("SET X ecrit_par Y WHERE X type 'a', Y nom 'bidule'")
        rset = self.qexecute('Any N where N ecrit_par X, X nom "bidule"')
        self.assertEqual(len(rset.rows), 1, rset.rows)

    def test_select_creation_date(self):
        self.qexecute("INSERT Personne X: X nom 'bidule'")
        rset = self.qexecute('Any D WHERE X nom "bidule", X creation_date D')
        self.assertEqual(len(rset.rows), 1)

    def test_select_or_relation(self):
        self.qexecute("INSERT Personne X: X nom 'bidule'")
        self.qexecute("INSERT Personne X: X nom 'chouette'")
        self.qexecute("INSERT Societe X: X nom 'logilab'")
        self.qexecute("INSERT Societe X: X nom 'caesium'")
        self.qexecute("SET P travaille S WHERE P nom 'bidule', S nom 'logilab'")
        rset = self.qexecute('DISTINCT Any P WHERE P travaille S1 OR P travaille S2, '
                             'S1 nom "logilab", S2 nom "caesium"')
        self.assertEqual(len(rset.rows), 1)
        self.qexecute("SET P travaille S WHERE P nom 'chouette', S nom 'caesium'")
        rset = self.qexecute('DISTINCT Any P WHERE P travaille S1 OR P travaille S2, '
                             'S1 nom "logilab", S2 nom "caesium"')
        self.assertEqual(len(rset.rows), 2)

    def test_select_or_sym_relation(self):
        self.qexecute("INSERT Personne X: X nom 'bidule'")
        self.qexecute("INSERT Personne X: X nom 'chouette'")
        self.qexecute("INSERT Personne X: X nom 'truc'")
        self.qexecute("SET P connait S WHERE P nom 'bidule', S nom 'chouette'")
        rset = self.qexecute('DISTINCT Any P WHERE S connait P, S nom "chouette"')