# firebird/base.py
# Copyright (C) 2005-2018 the SQLAlchemy authors and contributors
# <see AUTHORS file>
#
# This module is part of SQLAlchemy and is released under
# the MIT License: http://www.opensource.org/licenses/mit-license.php

r"""

.. dialect:: firebird
    :name: Firebird

Firebird Dialects
-----------------

Firebird offers two distinct dialects_ (not to be confused with a
SQLAlchemy ``Dialect``):

dialect 1
  This is the old syntax and behaviour, inherited from Interbase pre-6.0.

dialect 3
  This is the newer and supported syntax, introduced in Interbase 6.0.

The SQLAlchemy Firebird dialect detects these versions and
adjusts its representation of SQL accordingly.  However,
support for dialect 1 is not well tested and probably has
incompatibilities.

Locking Behavior
----------------

Firebird locks tables aggressively.  For this reason, a DROP TABLE may
hang until other transactions are released.  SQLAlchemy does its best
to release transactions as quickly as possible.  The most common cause
of hanging transactions is a non-fully consumed result set, i.e.::

    result = engine.execute("select * from table")
    row = result.fetchone()
    return

Where above, the ``ResultProxy`` has not been fully consumed.  The
connection will be returned to the pool and the transactional state
rolled back once the Python garbage collector reclaims the objects
which hold onto the connection, which often occurs asynchronously.
The above use case can be alleviated by calling ``first()`` on the
``ResultProxy`` which will fetch the first row and immediately close
all remaining cursor/connection resources.

RETURNING support
-----------------

Firebird 2.0 supports returning a result set from inserts, and 2.1
extends that to deletes and updates. This is generically exposed by
the SQLAlchemy ``returning()`` method, such as::

    # INSERT..RETURNING
    result = table.insert().returning(table.c.col1, table.c.col2).\
                   values(name='foo')
    print result.fetchall()

    # UPDATE..RETURNING
    raises = empl.update().returning(empl.c.id, empl.c.salary).\
                  where(empl.c.sales>100).\
                  values(dict(salary=empl.c.salary * 1.1))
    print raises.fetchall()


.. _dialects: http://mc-computing.com/Databases/Firebird/SQL_Dialect.html

"""

import datetime

from sqlalchemy import schema as sa_schema
from sqlalchemy import exc, types as sqltypes, sql, util
from sqlalchemy.sql import expression
from sqlalchemy.engine import base, default, reflection
from sqlalchemy.sql import compiler
from sqlalchemy.sql.elements import quoted_name

from sqlalchemy.types import (BIGINT, BLOB, DATE, FLOAT, INTEGER, NUMERIC,
                              SMALLINT, TEXT, TIME, TIMESTAMP, Integer)


RESERVED_WORDS = set([
    "active", "add", "admin", "after", "all", "alter", "and", "any", "as",
    "asc", "ascending", "at", "auto", "avg", "before", "begin", "between",
    "bigint", "bit_length", "blob", "both", "by", "case", "cast", "char",
    "character", "character_length", "char_length", "check", "close",
    "collate", "column", "commit", "committed", "computed", "conditional",
    "connect", "constraint", "containing", "count", "create", "cross",
    "cstring", "current", "current_connection", "current_date",
    "current_role", "current_time", "current_timestamp",
    "current_transaction", "current_user", "cursor", "database", "date",
    "day", "dec", "decimal", "declare", "default", "delete", "desc",
    "descending", "disconnect", "distinct", "do", "domain", "double",
    "drop", "else", "end", "entry_point", "escape", "exception",
    "execute", "exists", "exit", "external", "extract", "fetch", "file",
    "filter", "float", "for", "foreign", "from", "full", "function",
    "gdscode", "generator", "gen_id", "global", "grant", "group",
    "having", "hour", "if", "in", "inactive", "index", "inner",
    "input_type", "insensitive", "insert", "int", "integer", "into", "is",
    "isolation", "join", "key", "leading", "left", "length", "level",
    "like", "long", "lower", "manual", "max", "maximum_segment", "merge",
    "min", "minute", "module_name", "month", "names", "national",
    "natural", "nchar", "no", "not", "null", "numeric", "octet_length",
    "of", "on", "only", "open", "option", "or", "order", "outer",
    "output_type", "overflow", "page", "pages", "page_size", "parameter",
    "password", "plan", "position", "post_event", "precision", "primary",
    "privileges", "procedure", "protected", "rdb$db_key", "read", "real",
    "record_version", "recreate", "recursive", "references", "release",
    "reserv", "reserving", "retain", "returning_values", "returns",
    "revoke", "right", "rollback", "rows", "row_count", "savepoint",
    "schema", "second", "segment", "select", "sensitive", "set", "shadow",
    "shared", "singular", "size", "smallint", "snapshot", "some", "sort",
    "sqlcode", "stability", "start", "starting", "starts", "statistics",
    "sub_type", "sum", "suspend", "table", "then", "time", "timestamp",
    "to", "trailing", "transaction", "trigger", "trim", "uncommitted",
    "union", "unique", "update", "upper", "user", "using", "value",
    "values", "varchar", "variable", "varying", "view", "wait", "when",
    "where", "while", "with", "work", "write", "year",
])


class _StringType(sqltypes.String):
    """Base for Firebird string types."""

    def __init__(self, charset=None, **kw):
        self.charset = charset
        super(_StringType, self).__init__(**kw)


class VARCHAR(_StringType, sqltypes.VARCHAR):
    """Firebird VARCHAR type"""
    __visit_name__ = 'VARCHAR'

    def __init__(self, length=None, **kwargs):
        super(VARCHAR, self).__init__(length=length, **kwargs)


class CHAR(_StringType, sqltypes.CHAR):
    """Firebird CHAR type"""
    __visit_name__ = 'CHAR'

    def __init__(self, length=None, **kwargs):
        super(CHAR, self).__init__(length=length, **kwargs)


class _FBDateTime(sqltypes.DateTime):
    def bind_processor(self, dialect):
        def process(value):
            if type(value) == datetime.date:
                return datetime.datetime(value.year, value.month, value.day)
            else:
                return value
        return process

colspecs = {
    sqltypes.DateTime: _FBDateTime
}

ischema_names = {
    'SHORT': SMALLINT,
    'LONG': INTEGER,
    'QUAD': FLOAT,
    'FLOAT': FLOAT,
    'DATE': DATE,
    'TIME': TIME,
    'TEXT': TEXT,
    'INT64': BIGINT,
    'DOUBLE': FLOAT,
    'TIMESTAMP': TIMESTAMP,
    'VARYING': VARCHAR,
    'CSTRING': CHAR,
    'BLOB': BLOB,
}


# TODO: date conversion types (should be implemented as _FBDateTime,
# _FBDate, etc. as bind/result functionality is required)

class FBTypeCompiler(compiler.GenericTypeCompiler):
    def visit_boolean(self, type_, **kw):
        return self.visit_SMALLINT(type_, **kw)

    def visit_datetime(self, type_, **kw):
        return self.visit_TIMESTAMP(type_, **kw)

    def visit_TEXT(self, type_, **kw):
        return "BLOB SUB_TYPE 1"

    def visit_BLOB(self, type_, **kw):
        return "BLOB SUB_TYPE 0"

    def _extend_string(self, type_, basic):
        charset = getattr(type_, 'charset', None)
        if charset is None:
            return basic
        else:
            return '%s CHARACTER SET %s' % (basic, charset)

    def visit_CHAR(self, type_, **kw):
        basic = super(FBTypeCompiler, self).visit_CHAR(type_, **kw)
        return self._extend_string(type_, basic)

    def visit_VARCHAR(self, type_, **kw):
        if not type_.length:
            raise exc.CompileError(
                "VARCHAR requires a length on dialect %s" %
                self.dialect.name)
        basic = super(FBTypeCompiler, self).visit_VARCHAR(type_, **kw)
        return self._extend_string(type_, basic)


class FBCompiler(sql.compiler.SQLCompiler):
    """Firebird specific idiosyncrasies"""

    ansi_bind_rules = True

    # def visit_contains_op_binary(self, binary, operator, **kw):
    # cant use CONTAINING b.c. it's case insensitive.

    # def visit_notcontains_op_binary(self, binary, operator, **kw):
    # cant use NOT CONTAINING b.c. it's case insensitive.

    def visit_now_func(self, fn, **kw):
        return "CURRENT_TIMESTAMP"

    def visit_startswith_op_binary(self, binary, operator, **kw):
        return '%s STARTING WITH %s' % (
            binary.left._compiler_dispatch(self, **kw),
            binary.right._compiler_dispatch(self, **kw))

    def visit_notstartswith_op_binary(self, binary, operator, **kw):
        return '%s NOT STARTING WITH %s' % (
            binary.left._compiler_dispatch(self, **kw),
            binary.right._compiler_dispatch(self, **kw))

    def visit_mod_binary(self, binary, operator, **kw):
        return "mod(%s, %s)" % (
            self.process(binary.left, **kw),
            self.process(binary.right, **kw))

    def visit_alias(self, alias, asfrom=False, **kwargs):
        if self.dialect._version_two:
            return super(FBCompiler, self).\
                visit_alias(alias, asfrom=asfrom, **kwargs)
        else:
            # Override to not use the AS keyword which FB 1.5 does not like
            if asfrom:
                alias_name = isinstance(alias.name,
                                        expression._truncated_label) and \
                    self._truncated_identifier("alias",
                                               alias.name) or alias.name

                return self.process(
                    alias.original, asfrom=asfrom, **kwargs) + \
                    " " + \
                    self.preparer.format_alias(alias, alias_name)
            else:
                return self.process(alias.original, **kwargs)

    def visit_substring_func(self, func, **kw):
        s = self.process(func.clauses.clauses[0])
        start = self.process(func.clauses.clauses[1])
        if len(func.clauses.clauses) > 2:
            length = self.process(func.clauses.clauses[2])
            return "SUBSTRING(%s FROM %s FOR %s)" % (s, start, length)
        else:
            return "SUBSTRING(%s FROM %s)" % (s, start)

    def visit_length_func(self, function, **kw):
        if self.dialect._version_two:
            return "char_length" + self.function_argspec(function)
        else:
            return "strlen" + self.function_argspec(function)

    visit_char_length_func = visit_length_func

    def function_argspec(self, func, **kw):
        # TODO: this probably will need to be
        # narrowed to a fixed list, some no-arg functions
        # may require parens - see similar example in the oracle
        # dialect
        if func.clauses is not None and len(func.clauses):
            return self.process(func.clause_expr, **kw)
        else:
            return ""

    def default_from(self):
        return " FROM rdb$database"

    def visit_sequence(self, seq, **kw):
        return "gen_id(%s, 1)" % self.preparer.format_sequence(seq)

    def get_select_precolumns(self, select, **kw):
        """Called when building a ``SELECT`` statement, position is just
        before column list Firebird puts the limit and offset right
        after the ``SELECT``...
        """

        result = ""
        if select._limit_clause is not None:
            result += "FIRST %s " % self.process(select._limit_clause, **kw)
        if select._offset_clause is not None:
            result += "SKIP %s " % self.process(select._offset_clause, **kw)
        if select._distinct:
            result += "DISTINCT "
        return result

    def limit_clause(self, select, **kw):
        """Already taken care of in the `get_select_precolumns` method."""

        return ""

    def returning_clause(self, stmt, returning_cols):
        columns = [
            self._label_select_column(None, c, True, False, {})
            for c in expression._select_iterables(returning_cols)
        ]

        return 'RETURNING ' + ', '.join(columns)


class FBDDLCompiler(sql.compiler.DDLCompiler):
    """Firebird syntactic idiosyncrasies"""

    def visit_create_sequence(self, create):
        """Generate a ``CREATE GENERATOR`` statement for the sequence."""

        # no syntax for these
        # http://www.firebirdsql.org/manual/generatorguide-sqlsyntax.html
        if create.element.start is not None:
            raise NotImplemented(
                "Firebird SEQUENCE doesn't support START WITH")
        if create.element.increment is not None:
            raise NotImplemented(
                "Firebird SEQUENCE doesn't support INCREMENT BY")

        if self.dialect._version_two:
            return "CREATE SEQUENCE %s" % \
                self.preparer.format_sequence(create.element)
        else:
            return "CREATE GENERATOR %s" % \