"""Adapters for native cubicweb sources.

Notes:

* extid (aka external id, the primary key of an entity in the external source

  from which it comes from) are stored in a varchar column encoded as a base64

  string. This is because it should actually be Bytes but we want an index on

  it for fast querying.

:organization: Logilab

:copyright: 2001-2009 LOGILAB S.A. (Paris, FRANCE), license is LGPL v2.

:contact: http://www.logilab.fr/ -- mailto:contact@logilab.fr

:license: GNU Lesser General Public License, v2.1 - http://www.gnu.org/licenses

"""

__docformat__ = "restructuredtext en"

from threading import Lock

from datetime import datetime

from base64 import b64decode, b64encode

from logilab.common.cache import Cache

from logilab.common.configuration import REQUIRED

from logilab.common.adbh import get_adv_func_helper

from indexer import get_indexer

from cubicweb import UnknownEid, AuthenticationError, Binary, server

from cubicweb.server.utils import crypt_password

from cubicweb.server.sqlutils import SQL_PREFIX, SQLAdapterMixIn

from cubicweb.server.rqlannotation import set_qdata

from cubicweb.server.sources import AbstractSource, dbg_st_search, dbg_results

from cubicweb.server.sources.rql2sql import SQLGenerator

ATTR_MAP = {}

NONSYSTEM_ETYPES = set()

NONSYSTEM_RELATIONS = set()

class LogCursor(object):

    def __init__(self, cursor):

        self.cu = cursor

    def execute(self, query, args=None):

        """Execute a query.

        it's a function just so that it shows up in profiling

        """

        if server.DEBUG & server.DBG_SQL:

            print 'exec', query, args

        try:

            self.cu.execute(str(query), args)

        except Exception, ex:

            print "sql: %r\n args: %s\ndbms message: %r" % (

                query, args, ex.args[0])

            raise

    def fetchall(self):

        return self.cu.fetchall()

    def fetchone(self):

        return self.cu.fetchone()

def make_schema(selected, solution, table, typemap):

    """return a sql schema to store RQL query result"""

    sql = []

    varmap = {}

    for i, term in enumerate(selected):

        name = 'C%s' % i

        key = term.as_string()

        varmap[key] = '%s.%s' % (table, name)

        ttype = term.get_type(solution)

        try:

            sql.append('%s %s' % (name, typemap[ttype]))

        except KeyError:

            # assert not schema(ttype).is_final()

            sql.append('%s %s' % (name, typemap['Int']))

    return ','.join(sql), varmap

def _modified_sql(table, etypes):

    # XXX protect against sql injection

    if len(etypes) > 1:

        restr = 'type IN (%s)' % ','.join("'%s'" % etype for etype in etypes)

    else:

        restr = "type='%s'" % etypes[0]

    if table == 'entities':

        attr = 'mtime'

    else:

        attr = 'dtime'

    return 'SELECT type, eid FROM %s WHERE %s AND %s > %%(time)s' % (

        table, restr, attr)

class NativeSQLSource(SQLAdapterMixIn, AbstractSource):

    """adapter for source using the native cubicweb schema (see below)

    """

    sqlgen_class = SQLGenerator

    # need default value on class since migration doesn't call init method

    has_deleted_entitites_table = True

    passwd_rql = "Any P WHERE X is CWUser, X login %(login)s, X upassword P"

    auth_rql = "Any X WHERE X is CWUser, X login %(login)s, X upassword %(pwd)s"

    _sols = ({'X': 'CWUser', 'P': 'Password'},)

    options = (

        ('db-driver',

         {'type' : 'string',

          'default': 'postgres',

          'help': 'database driver (postgres or sqlite)',

          'group': 'native-source', 'inputlevel': 1,

          }),

        ('db-host',

         {'type' : 'string',

          'default': '',

          'help': 'database host',

          'group': 'native-source', 'inputlevel': 1,

          }),

        ('db-port',

         {'type' : 'string',

          'default': '',

          'help': 'database port',

          'group': 'native-source', 'inputlevel': 1,

          }),

        ('db-name',

         {'type' : 'string',

          'default': REQUIRED,

          'help': 'database name',

          'group': 'native-source', 'inputlevel': 0,

          }),

        ('db-user',

         {'type' : 'string',

          'default': 'cubicweb',

          'help': 'database user',

          'group': 'native-source', 'inputlevel': 0,

          }),

        ('db-password',

         {'type' : 'password',

          'default': '',

          'help': 'database password',

          'group': 'native-source', 'inputlevel': 0,

          }),

        ('db-encoding',

         {'type' : 'string',

          'default': 'utf8',

          'help': 'database encoding',

          'group': 'native-source', 'inputlevel': 1,

          }),

    )

    def __init__(self, repo, appschema, source_config, *args, **kwargs):

        SQLAdapterMixIn.__init__(self, source_config)

        AbstractSource.__init__(self, repo, appschema, source_config,

                                *args, **kwargs)

        # sql generator

        self._rql_sqlgen = self.sqlgen_class(appschema, self.dbhelper,

                                             self.encoding, ATTR_MAP.copy())

        # full text index helper

        self.indexer = get_indexer(self.dbdriver, self.encoding)

        # advanced functionality helper

        self.dbhelper.fti_uid_attr = self.indexer.uid_attr

        self.dbhelper.fti_table = self.indexer.table

        self.dbhelper.fti_restriction_sql = self.indexer.restriction_sql

        self.dbhelper.fti_need_distinct_query = self.indexer.need_distinct

        # sql queries cache

        self._cache = Cache(repo.config['rql-cache-size'])

        self._temp_table_data = {}

        self._eid_creation_lock = Lock()

        # XXX no_sqlite_wrap trick since we've a sqlite locking pb when

        # running unittest_multisources with the wrapping below

        if self.dbdriver == 'sqlite' and \

               not getattr(repo.config, 'no_sqlite_wrap', False):

            from cubicweb.server.sources.extlite import ConnectionWrapper

            self.get_connection = lambda: ConnectionWrapper(self)

            self.check_connection = lambda cnx: cnx

            def pool_reset(cnx):

                cnx.close()

            self.pool_reset = pool_reset

    @property

    def _sqlcnx(self):

        # XXX: sqlite connections can only be used in the same thread, so

        #      create a new one each time necessary. If it appears to be time

        #      consuming, find another way

        return SQLAdapterMixIn.get_connection(self)

    def reset_caches(self):

        """method called during test to reset potential source caches"""

        self._cache = Cache(self.repo.config['rql-cache-size'])

    def clear_eid_cache(self, eid, etype):

        """clear potential caches for the given eid"""

        self._cache.pop('Any X WHERE X eid %s, X is %s' % (eid, etype), None)

        self._cache.pop('Any X WHERE X eid %s' % eid, None)

        self._cache.pop('Any %s' % eid, None)

    def sqlexec(self, session, sql, args=None):

        """execute the query and return its result"""

        return self.process_result(self.doexec(session, sql, args))

    def init_creating(self):

        pool = self.repo._get_pool()

        pool.pool_set()

        # check full text index availibility

        if not self.indexer.has_fti_table(pool['system']):

            self.error('no text index table')

            self.indexer = None

        pool.pool_reset()

        self.repo._free_pool(pool)

    def backup(self, backupfile):

        """method called to create a backup of the source's data"""

        self.close_pool_connections()

        try:

            self.backup_to_file(backupfile)

        finally:

            self.open_pool_connections()

    def restore(self, backupfile, drop):

        """method called to restore a backup of source's data"""

        self.close_pool_connections()

        try:

            self.restore_from_file(backupfile, drop)

        finally:

            self.open_pool_connections()

    def init(self):

        self.init_creating()

        pool = self.repo._get_pool()

        pool.pool_set()

        # XXX cubicweb < 2.42 compat

        if 'deleted_entities' in self.dbhelper.list_tables(pool['system']):

            self.has_deleted_entitites_table = True

        else:

            self.has_deleted_entitites_table = False

        pool.pool_reset()

        self.repo._free_pool(pool)

    def map_attribute(self, etype, attr, cb):

        self._rql_sqlgen.attr_map['%s.%s' % (etype, attr)] = cb

    # ISource interface #######################################################

    def compile_rql(self, rql):

        rqlst = self.repo.querier._rqlhelper.parse(rql)

        rqlst.restricted_vars = ()

        rqlst.children[0].solutions = self._sols

        self.repo.querier.sqlgen_annotate(rqlst)

        set_qdata(self.schema.rschema, rqlst, ())

        return rqlst

    def set_schema(self, schema):

        """set the instance'schema"""

        self._cache = Cache(self.repo.config['rql-cache-size'])

        self.cache_hit, self.cache_miss, self.no_cache = 0, 0, 0

        self.schema = schema

        try:

            self._rql_sqlgen.schema = schema

        except AttributeError:

            pass # __init__

        if 'CWUser' in schema: # probably an empty schema if not true...

            # rql syntax trees used to authenticate users

            self._passwd_rqlst = self.compile_rql(self.passwd_rql)

            self._auth_rqlst = self.compile_rql(self.auth_rql)

    def support_entity(self, etype, write=False):

        """return true if the given entity's type is handled by this adapter

        if write is true, return true only if it's a RW support

        """

        return not etype in NONSYSTEM_ETYPES

    def support_relation(self, rtype, write=False):

        """return true if the given relation's type is handled by this adapter

        if write is true, return true only if it's a RW support

        """

        if write:

            return not rtype in NONSYSTEM_RELATIONS

        # due to current multi-sources implementation, the system source

        # can't claim not supporting a relation

        return True #not rtype == 'content_for'

    def authenticate(self, session, login, password):

        """return CWUser eid for the given login/password if this account is

        defined in this source, else raise `AuthenticationError`

        two queries are needed since passwords are stored crypted, so we have

        to fetch the salt first

        """

        args = {'login': login, 'pwd' : password}

        if password is not None:

            rset = self.syntax_tree_search(session, self._passwd_rqlst, args)

            try:

                pwd = rset[0][0]

            except IndexError:

                raise AuthenticationError('bad login')

            # passwords are stored using the Bytes type, so we get a StringIO

            if pwd is not None:

                args['pwd'] = crypt_password(password, pwd.getvalue()[:2])

        # get eid from login and (crypted) password

        rset = self.syntax_tree_search(session, self._auth_rqlst, args)

        try:

            return rset[0][0]

        except IndexError:

            raise AuthenticationError('bad password')

    def syntax_tree_search(self, session, union, args=None, cachekey=None,

                           varmap=None):

        """return result from this source for a rql query (actually from

        a rql syntax tree and a solution dictionary mapping each used

        variable to a possible type). If cachekey is given, the query

        necessary to fetch the results (but not the results themselves)

        may be cached using this key.

        """

        assert dbg_st_search(self.uri, union, varmap, args, cachekey)

        # remember number of actually selected term (sql generation may append some)

        if cachekey is None:

            self.no_cache += 1

            # generate sql query if we are able to do so (not supported types...)

            sql, query_args = self._rql_sqlgen.generate(union, args, varmap)

        else:

            # sql may be cached

            try:

                sql, query_args = self._cache[cachekey]

                self.cache_hit += 1

            except KeyError:

                self.cache_miss += 1

                sql, query_args = self._rql_sqlgen.generate(union, args, varmap)

                self._cache[cachekey] = sql, query_args

        args = self.merge_args(args, query_args)

        assert isinstance(sql, basestring), repr(sql)

        try:

            cursor = self.doexec(session, sql, args)

        except (self.dbapi_module.OperationalError,

                self.dbapi_module.InterfaceError):

            # FIXME: better detection of deconnection pb

            self.info("request failed '%s' ... retry with a new cursor", sql)

            session.pool.reconnect(self)

            cursor = self.doexec(session, sql, args)

        results = self.process_result(cursor)

        assert dbg_results(results)

        return results

    def flying_insert(self, table, session, union, args=None, varmap=None):

        """similar as .syntax_tree_search, but inserts data in the

        temporary table (on-the-fly if possible, eg for the system

        source whose the given cursor come from). If not possible,

        inserts all data by calling .executemany().

        """

        assert dbg_st_search(

            self.uri, union, varmap, args,

            prefix='ON THE FLY temp data insertion into %s from' % table)

        # generate sql queries if we are able to do so

        sql, query_args = self._rql_sqlgen.generate(union, args, varmap)

        query = 'INSERT INTO %s %s' % (table, sql.encode(self.encoding))

        self.doexec(session, query, self.merge_args(args, query_args))

    def manual_insert(self, results, table, session):

        """insert given result into a temporary table on the system source"""

        if server.DEBUG & server.DBG_RQL:

            print '  manual insertion of', res, 'into', table

        if not results:

            return

        query_args = ['%%(%s)s' % i for i in xrange(len(results[0]))]

        query = 'INSERT INTO %s VALUES(%s)' % (table, ','.join(query_args))

        kwargs_list = []

        for row in results:

            kwargs = {}

            row = tuple(row)

            for index, cell in enumerate(row):

                if isinstance(cell, Binary):

                    cell = self.binary(cell.getvalue())

                kwargs[str(index)] = cell

            kwargs_list.append(kwargs)

        self.doexecmany(session, query, kwargs_list)

    def clean_temp_data(self, session, temptables):

        """remove temporary data, usually associated to temporary tables"""

        if temptables:

            for table in temptables:

                try:

                    self.doexec(session,'DROP TABLE %s' % table)

                except:

                    pass

                try: