"""

Oracle database backend for Django.

Requires cx_Oracle: http://cx-oracle.sourceforge.net/

"""

import os

import time

# Oracle takes client-side character set encoding from the environment.

os.environ['NLS_LANG'] = '.UTF8'

# This prevents unicode from getting mangled by getting encoded into the

# potentially non-unicode database character set.

os.environ['ORA_NCHAR_LITERAL_REPLACE'] = 'TRUE'

try:

    import cx_Oracle as Database

except ImportError, e:

    from django.core.exceptions import ImproperlyConfigured

    raise ImproperlyConfigured("Error loading cx_Oracle module: %s" % e)

from django.db.backends import *

from django.db.backends.signals import connection_created

from django.db.backends.oracle.client import DatabaseClient

from django.db.backends.oracle.creation import DatabaseCreation

from django.db.backends.oracle.introspection import DatabaseIntrospection

from django.utils.encoding import smart_str, force_unicode

DatabaseError = Database.DatabaseError

IntegrityError = Database.IntegrityError

# Check whether cx_Oracle was compiled with the WITH_UNICODE option.  This will

# also be True in Python 3.0.

if int(Database.version.split('.', 1)[0]) >= 5 and not hasattr(Database, 'UNICODE'):

    convert_unicode = force_unicode

else:

    convert_unicode = smart_str

class DatabaseFeatures(BaseDatabaseFeatures):

    empty_fetchmany_value = ()

    needs_datetime_string_cast = False

    interprets_empty_strings_as_nulls = True

    uses_savepoints = True

    can_return_id_from_insert = True

class DatabaseOperations(BaseDatabaseOperations):

    def autoinc_sql(self, table, column):

        # To simulate auto-incrementing primary keys in Oracle, we have to

        # create a sequence and a trigger.

        sq_name = get_sequence_name(table)

        tr_name = get_trigger_name(table)

        tbl_name = self.quote_name(table)

        col_name = self.quote_name(column)

        sequence_sql = """

DECLARE

    i INTEGER;

BEGIN

    SELECT COUNT(*) INTO i FROM USER_CATALOG

        WHERE TABLE_NAME = '%(sq_name)s' AND TABLE_TYPE = 'SEQUENCE';

    IF i = 0 THEN

        EXECUTE IMMEDIATE 'CREATE SEQUENCE "%(sq_name)s"';

    END IF;

END;

/""" % locals()

        trigger_sql = """

CREATE OR REPLACE TRIGGER "%(tr_name)s"

BEFORE INSERT ON %(tbl_name)s

FOR EACH ROW

WHEN (new.%(col_name)s IS NULL)

    BEGIN

        SELECT "%(sq_name)s".nextval

        INTO :new.%(col_name)s FROM dual;

    END;

/""" % locals()

        return sequence_sql, trigger_sql

    def date_extract_sql(self, lookup_type, field_name):

        # http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/functions42a.htm#1017163

        if lookup_type == 'week_day':

            # TO_CHAR(field, 'D') returns an integer from 1-7, where 1=Sunday.

            return "TO_CHAR(%s, 'D')" % field_name

        else:

            return "EXTRACT(%s FROM %s)" % (lookup_type, field_name)

    def date_trunc_sql(self, lookup_type, field_name):

        # Oracle uses TRUNC() for both dates and numbers.

        # http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/functions155a.htm#SQLRF06151

        if lookup_type == 'day':

            sql = 'TRUNC(%s)' % field_name

        else:

            sql = "TRUNC(%s, '%s')" % (field_name, lookup_type)

        return sql

    def datetime_cast_sql(self):

        return "TO_TIMESTAMP(%s, 'YYYY-MM-DD HH24:MI:SS.FF')"

    def deferrable_sql(self):

        return " DEFERRABLE INITIALLY DEFERRED"

    def drop_sequence_sql(self, table):

        return "DROP SEQUENCE %s;" % self.quote_name(get_sequence_name(table))

    def fetch_returned_insert_id(self, cursor):

        return long(cursor._insert_id_var.getvalue())

    def field_cast_sql(self, db_type):

        if db_type and db_type.endswith('LOB'):

            return "DBMS_LOB.SUBSTR(%s)"

        else:

            return "%s"

    def last_insert_id(self, cursor, table_name, pk_name):

        sq_name = get_sequence_name(table_name)

        cursor.execute('SELECT "%s".currval FROM dual' % sq_name)

        return cursor.fetchone()[0]

    def lookup_cast(self, lookup_type):

        if lookup_type in ('iexact', 'icontains', 'istartswith', 'iendswith'):

            return "UPPER(%s)"

        return "%s"

    def max_name_length(self):

        return 30

    def prep_for_iexact_query(self, x):

        return x

    def process_clob(self, value):

        if value is None:

            return u''

        return force_unicode(value.read())

    def quote_name(self, name):

        # SQL92 requires delimited (quoted) names to be case-sensitive.  When

        # not quoted, Oracle has case-insensitive behavior for identifiers, but

        # always defaults to uppercase.

        # We simplify things by making Oracle identifiers always uppercase.

        if not name.startswith('"') and not name.endswith('"'):

            name = '"%s"' % util.truncate_name(name.upper(),

                                               self.max_name_length())

        return name.upper()

    def random_function_sql(self):

        return "DBMS_RANDOM.RANDOM"

    def regex_lookup_9(self, lookup_type):

        raise NotImplementedError("Regexes are not supported in Oracle before version 10g.")

    def regex_lookup_10(self, lookup_type):

        if lookup_type == 'regex':

            match_option = "'c'"

        else:

            match_option = "'i'"

        return 'REGEXP_LIKE(%%s, %%s, %s)' % match_option

    def regex_lookup(self, lookup_type):

        # If regex_lookup is called before it's been initialized, then create

        # a cursor to initialize it and recur.

        from django.db import connection

        connection.cursor()

        return connection.ops.regex_lookup(lookup_type)

    def return_insert_id(self):

        return "RETURNING %s INTO %%s", (InsertIdVar(),)

    def savepoint_create_sql(self, sid):

        return convert_unicode("SAVEPOINT " + self.quote_name(sid))

    def savepoint_rollback_sql(self, sid):

        return convert_unicode("ROLLBACK TO SAVEPOINT " + self.quote_name(sid))

    def sql_flush(self, style, tables, sequences):

        # Return a list of 'TRUNCATE x;', 'TRUNCATE y;',

        # 'TRUNCATE z;'... style SQL statements

        if tables:

            # Oracle does support TRUNCATE, but it seems to get us into

            # FK referential trouble, whereas DELETE FROM table works.

            sql = ['%s %s %s;' % \

                    (style.SQL_KEYWORD('DELETE'),

                     style.SQL_KEYWORD('FROM'),

                     style.SQL_FIELD(self.quote_name(table)))

                    for table in tables]

            # Since we've just deleted all the rows, running our sequence

            # ALTER code will reset the sequence to 0.

            for sequence_info in sequences:

                sequence_name = get_sequence_name(sequence_info['table'])

                table_name = self.quote_name(sequence_info['table'])

                column_name = self.quote_name(sequence_info['column'] or 'id')

                query = _get_sequence_reset_sql() % {'sequence': sequence_name,

                                                     'table': table_name,

                                                     'column': column_name}

                sql.append(query)

            return sql

        else:

            return []

    def sequence_reset_sql(self, style, model_list):

        from django.db import models

        output = []

        query = _get_sequence_reset_sql()

        for model in model_list:

            for f in model._meta.local_fields:

                if isinstance(f, models.AutoField):

                    table_name = self.quote_name(model._meta.db_table)

                    sequence_name = get_sequence_name(model._meta.db_table)

                    column_name = self.quote_name(f.column)

                    output.append(query % {'sequence': sequence_name,

                                           'table': table_name,

                                           'column': column_name})

                    # Only one AutoField is allowed per model, so don't

                    # continue to loop

                    break

            for f in model._meta.many_to_many:

                if not f.rel.through:

                    table_name = self.quote_name(f.m2m_db_table())

                    sequence_name = get_sequence_name(f.m2m_db_table())

                    column_name = self.quote_name('id')

                    output.append(query % {'sequence': sequence_name,

                                           'table': table_name,

                                           'column': column_name})

        return output

    def start_transaction_sql(self):

        return ''

    def tablespace_sql(self, tablespace, inline=False):

        return "%sTABLESPACE %s" % ((inline and "USING INDEX " or ""),

            self.quote_name(tablespace))

    def value_to_db_time(self, value):

        if value is None:

            return None

        if isinstance(value, basestring):

            return datetime.datetime(*(time.strptime(value, '%H:%M:%S')[:6]))

        return datetime.datetime(1900, 1, 1, value.hour, value.minute,

                                 value.second, value.microsecond)

    def year_lookup_bounds_for_date_field(self, value):

        first = '%s-01-01'

        second = '%s-12-31'

        return [first % value, second % value]

    def combine_expression(self, connector, sub_expressions):

        "Oracle requires special cases for %% and & operators in query expressions"

        if connector == '%%':

            return 'MOD(%s)' % ','.join(sub_expressions)

        elif connector == '&':

            return 'BITAND(%s)' % ','.join(sub_expressions)

        elif connector == '|':

            raise NotImplementedError("Bit-wise or is not supported in Oracle.")

        return super(DatabaseOperations, self).combine_expression(connector, sub_expressions)

class DatabaseWrapper(BaseDatabaseWrapper):

    operators = {

        'exact': '= %s',

        'iexact': '= UPPER(%s)',

        'gt': '> %s',

        'gte': '>= %s',

        'lt': '< %s',

        'lte': '<= %s',

    }

    oracle_version = None

    def __init__(self, *args, **kwargs):

        super(DatabaseWrapper, self).__init__(*args, **kwargs)

        self.features = DatabaseFeatures()

        self.ops = DatabaseOperations()

        self.client = DatabaseClient(self)

        self.creation = DatabaseCreation(self)

        self.introspection = DatabaseIntrospection(self)

    def _valid_connection(self):

        return self.connection is not None

    def _connect_string(self):

        settings_dict = self.settings_dict

        else:

    def _cursor(self):

        cursor = None

        if not self._valid_connection():

            conn_string = convert_unicode(self._connect_string())

            cursor = FormatStylePlaceholderCursor(self.connection)

            # Set oracle date to ansi date format.  This only needs to execute

            # once when we create a new connection. We also set the Territory

            # to 'AMERICA' which forces Sunday to evaluate to a '1' in TO_CHAR().

            cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS' "

                           "NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF' "

                           "NLS_TERRITORY = 'AMERICA'")

            try:

                self.oracle_version = int(self.connection.version.split('.')[0])

                # There's no way for the DatabaseOperations class to know the

                # currently active Oracle version, so we do some setups here.

                # TODO: Multi-db support will need a better solution (a way to

                # communicate the current version).

                if self.oracle_version <= 9:

                    self.ops.regex_lookup = self.ops.regex_lookup_9

                else:

                    self.ops.regex_lookup = self.ops.regex_lookup_10

            except ValueError:

                pass

            try:

                self.connection.stmtcachesize = 20

            except:

                # Django docs specify cx_Oracle version 4.3.1 or higher, but

                # stmtcachesize is available only in 4.3.2 and up.

                pass

            connection_created.send(sender=self.__class__)

        if not cursor:

            cursor = FormatStylePlaceholderCursor(self.connection)

        return cursor

    # Oracle doesn't support savepoint commits.  Ignore them.

    def _savepoint_commit(self, sid):

        pass