# -*- coding: iso-8859-1 -*-

# copyright 2003-2011 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

from logilab.common.testlib import TestCase, unittest_main

from rql import BadRQLQuery, RQLSyntaxError

from cubicweb import QueryError, Unauthorized, Binary

from cubicweb.server.sqlutils import SQL_PREFIX

from cubicweb.server.utils import crypt_password

from cubicweb.server.sources.native import make_schema

from cubicweb.devtools import get_test_db_handler, TestServerConfiguration

from cubicweb.devtools.repotest import tuplify, BaseQuerierTC

from unittest_session import Variable

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

from rql.utils import register_function, FunctionDescr

class group_sort_value(FunctionDescr):

    supported_backends = ('sqlite',)

    rtype = 'Int'

try:

    register_function(group_sort_value)

except AssertionError:

    pass

from cubicweb.server.sqlutils import SQL_CONNECT_HOOKS

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)

from logilab.database import _GenericAdvFuncHelper

TYPEMAP = _GenericAdvFuncHelper.TYPE_MAPPING

class MakeSchemaTC(TestCase):

    def test_known_values(self):

        solution = {'A': 'String', 'B': 'CWUser'}

        self.assertEqual(make_schema((Variable('A'), Variable('B')), solution,

                                      'table0', TYPEMAP),

                          ('C0 text,C1 integer', {'A': 'table0.C0', 'B': 'table0.C1'}))

def setUpModule(*args):

    global repo, cnx

    config = TestServerConfiguration(apphome=UtilsTC.datadir)

    handler = get_test_db_handler(config)

    handler.build_db_cache()

    repo, cnx = handler.get_repo_and_cnx()

def tearDownModule(*args):

    global repo, cnx

    cnx.close()

    repo.shutdown()

    del repo, cnx

class UtilsTC(BaseQuerierTC):

    def setUp(self):

        self.__class__.repo = repo

        super(UtilsTC, self).setUp()

    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):

        reid = self.execute('Any X WHERE X is CWRType, X name "owned_by"')[0][0]

        rqlst = self._prepare('Any COUNT(RDEF) WHERE RDEF relation_type X, X eid %(x)s', {'x': reid})

        self.assertEqual(rqlst.solutions, [{'RDEF': 'CWAttribute'}, {'RDEF': 'CWRelation'}])

    def test_preprocess_2(self):

        teid = self.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('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(len(rqlst.solutions), 1)

    def test_preprocess_security(self):

        plan = self._prepare_plan('Any ETN,COUNT(X) GROUPBY ETN '

                                  'WHERE X is ET, ET name ETN')

        plan.session = self.user_groups_session('users')

        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), 3)

        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.assertEqual(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.assertEqual(rql,  'Any ETN,X WHERE X is ET, ET name ETN, ET is CWEType, X is IN(BaseTransition, Bookmark, CWAttribute, CWCache, CWConstraint, CWConstraintType, CWEType, CWGroup, CWPermission, CWProperty, CWRType, CWRelation, CWSource, CWUniqueTogetherConstraint, CWUser, Card, Comment, Division, Email, EmailAddress, EmailPart, EmailThread, ExternalUri, File, Folder, Note, Personne, RQLExpression, Societe, State, SubDivision, SubWorkflowExitPoint, Tag, TrInfo, Transition, Workflow, WorkflowTransition)')

        self.assertListEqual(sorted(solutions),

                              sorted([{'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': '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': 'EmailAddress', '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': 'Note', '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(X owned_by %(C)s), '

                          'ET is CWEType, X is Basket')

        self.assertEqual(solutions, [{'ET': 'CWEType',

                                       'X': 'Basket',

                                       'ETN': 'String',

                                       }])

    def test_preprocess_security_aggregat(self):

        plan = self._prepare_plan('Any MAX(X)')

        plan.session = self.user_groups_session('users')

        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), 3)

        self.assertEqual([t.as_string() for t in union.children[0].selection],

                          ['MAX(X)'])

    def test_preprocess_nonregr(self):

        rqlst = self._prepare('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.execute('Any X WHERE X eid %(x)s', {'x': self.session.user.eid})

        self.assertEqual(rset.description[0][0], 'CWUser')

        rset = self.execute('Any 1')

        self.assertEqual(rset.description[0][0], 'Int')

        rset = self.execute('Any TRUE')

        self.assertEqual(rset.description[0][0], 'Boolean')

        rset = self.execute('Any "hop"')

        self.assertEqual(rset.description[0][0], 'String')

        rset = self.execute('Any TODAY')

        self.assertEqual(rset.description[0][0], 'Date')

        rset = self.execute('Any NOW')

        self.assertEqual(rset.description[0][0], 'Datetime')

        rset = self.execute('Any %(x)s', {'x': 1})

        self.assertEqual(rset.description[0][0], 'Int')

        rset = self.execute('Any %(x)s', {'x': 1L})

        self.assertEqual(rset.description[0][0], 'Int')

        rset = self.execute('Any %(x)s', {'x': True})

        self.assertEqual(rset.description[0][0], 'Boolean')

        rset = self.execute('Any %(x)s', {'x': 1.0})

        self.assertEqual(rset.description[0][0], 'Float')

        rset = self.execute('Any %(x)s', {'x': datetime.now()})

        self.assertEqual(rset.description[0][0], 'Datetime')

        rset = self.execute('Any %(x)s', {'x': 'str'})

        self.assertEqual(rset.description[0][0], 'String')

        rset = self.execute('Any %(x)s', {'x': u'str'})

        self.assertEqual(rset.description[0][0], 'String')

class QuerierTC(BaseQuerierTC):

    def setUp(self):

        self.__class__.repo = repo

        super(QuerierTC, self).setUp()

    def test_encoding_pb(self):

        self.assertRaises(RQLSyntaxError, self.execute,

                          'Any X WHERE X is CWRType, X name "�wned_by"')

    def test_unknown_eid(self):

        # should return an empty result set

        self.failIf(self.execute('Any X WHERE X eid 99999999'))

    def test_typed_eid(self):

        # should return an empty result set

        rset = self.execute('Any X WHERE X eid %(x)s', {'x': '1'})

        self.assertIsInstance(rset[0][0], (int, long))

    def test_bytes_storage(self):

        feid = self.execute('INSERT File X: X data_name "foo.pdf", X data_format "text/plain", X data %(data)s',

                            {'data': Binary("xxx")})[0][0]

        fdata = self.execute('Any D WHERE X data D, X eid %(x)s', {'x': feid})[0][0]

        self.assertIsInstance(fdata, Binary)

        self.assertEqual(fdata.getvalue(), 'xxx')

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

    def test_select_1(self):

        rset = self.execute('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.execute('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.execute('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.execute('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.execute('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.execute('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], 'CWRelation') # XXX may change as schema evolve

    def test_select_groupby_orderby(self):

        rset = self.execute('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.execute('Any N GROUPBY N WHERE X name N')

        rset = self.execute('Any N,MAX(D) GROUPBY N LIMIT 5 WHERE X name N, X creation_date D')

    def test_select_inlined_groupby(self):

        seid = self.execute('State X WHERE X name "deactivated"')[0][0]

        rset = self.execute('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.execute('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.execute('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.execute('Any N ORDERBY N WHERE X name N')

        self.assertEqual(sorted(rset1.rows), rset1.rows)

        rset = self.execute('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.execute('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.execute("INSERT Personne X: X nom 'bidule'")[0]