1 """

     2 Oracle database backend for Django.

     3 

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

     5 """

     6 

     7 import os

     8 import datetime

     9 import time

    10 try:

    11     from decimal import Decimal

    12 except ImportError:

    13     from django.utils._decimal import Decimal

    14 

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

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

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

    18 # potentially non-unicode database character set.

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

    20 

    21 try:

    22     import cx_Oracle as Database

    23 except ImportError, e:

    24     from django.core.exceptions import ImproperlyConfigured

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

    26 

    27 from django.db.backends import *

    28 from django.db.backends.signals import connection_created

    29 from django.db.backends.oracle import query

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

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

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

    33 from django.utils.encoding import smart_str, force_unicode

    34 

    35 DatabaseError = Database.DatabaseError

    36 IntegrityError = Database.IntegrityError

    37 

    38 

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

    40 # also be True in Python 3.0.

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

    42     convert_unicode = force_unicode

    43 else:

    44     convert_unicode = smart_str

    45 

    46 

    47 class DatabaseFeatures(BaseDatabaseFeatures):

    48     empty_fetchmany_value = ()

    49     needs_datetime_string_cast = False

    50     uses_custom_query_class = True

    51     interprets_empty_strings_as_nulls = True

    52     uses_savepoints = True

    53     can_return_id_from_insert = True

    54 

    55 

    56 class DatabaseOperations(BaseDatabaseOperations):

    57 

    58     def autoinc_sql(self, table, column):

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

    60         # create a sequence and a trigger.

    61         sq_name = get_sequence_name(table)

    62         tr_name = get_trigger_name(table)

    63         tbl_name = self.quote_name(table)

    64         col_name = self.quote_name(column)

    65         sequence_sql = """

    66 DECLARE

    67     i INTEGER;

    68 BEGIN

    69     SELECT COUNT(*) INTO i FROM USER_CATALOG

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

    71     IF i = 0 THEN

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

    73     END IF;

    74 END;

    75 /""" % locals()

    76         trigger_sql = """

    77 CREATE OR REPLACE TRIGGER "%(tr_name)s"

    78 BEFORE INSERT ON %(tbl_name)s

    79 FOR EACH ROW

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

    81     BEGIN

    82         SELECT "%(sq_name)s".nextval

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

    84     END;

    85 /""" % locals()

    86         return sequence_sql, trigger_sql

    87 

    88     def date_extract_sql(self, lookup_type, field_name):

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

    90         if lookup_type == 'week_day':

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

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

    93         else:

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

    95 

    96     def date_trunc_sql(self, lookup_type, field_name):

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

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

    99         if lookup_type == 'day':

   100             sql = 'TRUNC(%s)' % field_name

   101         else:

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

   103         return sql

   104 

   105     def datetime_cast_sql(self):

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

   107 

   108     def deferrable_sql(self):

   109         return " DEFERRABLE INITIALLY DEFERRED"

   110 

   111     def drop_sequence_sql(self, table):

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

   113 

   114     def fetch_returned_insert_id(self, cursor):

   115         return long(cursor._insert_id_var.getvalue())

   116 

   117     def field_cast_sql(self, db_type):

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

   119             return "DBMS_LOB.SUBSTR(%s)"

   120         else:

   121             return "%s"

   122 

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

   124         sq_name = get_sequence_name(table_name)

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

   126         return cursor.fetchone()[0]

   127 

   128     def lookup_cast(self, lookup_type):

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

   130             return "UPPER(%s)"

   131         return "%s"

   132 

   133     def max_name_length(self):

   134         return 30

   135 

   136     def prep_for_iexact_query(self, x):

   137         return x

   138 

   139     def process_clob(self, value):

   140         if value is None:

   141             return u''

   142         return force_unicode(value.read())

   143 

   144     def query_class(self, DefaultQueryClass):

   145         return query.query_class(DefaultQueryClass, Database)

   146 

   147     def quote_name(self, name):

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

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

   150         # always defaults to uppercase.

   151         # We simplify things by making Oracle identifiers always uppercase.

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

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

   154                                                self.max_name_length())

   155         return name.upper()

   156 

   157     def random_function_sql(self):

   158         return "DBMS_RANDOM.RANDOM"

   159 

   160     def regex_lookup_9(self, lookup_type):

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

   162 

   163     def regex_lookup_10(self, lookup_type):

   164         if lookup_type == 'regex':

   165             match_option = "'c'"

   166         else:

   167             match_option = "'i'"

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

   169 

   170     def regex_lookup(self, lookup_type):

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

   172         # a cursor to initialize it and recur.

   173         from django.db import connection

   174         connection.cursor()

   175         return connection.ops.regex_lookup(lookup_type)

   176 

   177     def return_insert_id(self):

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

   179 

   180     def savepoint_create_sql(self, sid):

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

   182 

   183     def savepoint_rollback_sql(self, sid):

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

   185 

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

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

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

   189         if tables:

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

   191             # FK referential trouble, whereas DELETE FROM table works.

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

   193                     (style.SQL_KEYWORD('DELETE'),

   194                      style.SQL_KEYWORD('FROM'),

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

   196                     for table in tables]

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

   198             # ALTER code will reset the sequence to 0.

   199             for sequence_info in sequences:

   200                 sequence_name = get_sequence_name(sequence_info['table'])

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

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

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

   204                                                      'table': table_name,

   205                                                      'column': column_name}

   206                 sql.append(query)

   207             return sql

   208         else:

   209             return []

   210 

   211     def sequence_reset_sql(self, style, model_list):

   212         from django.db import models

   213         output = []

   214         query = _get_sequence_reset_sql()

   215         for model in model_list:

   216             for f in model._meta.local_fields:

   217                 if isinstance(f, models.AutoField):

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

   219                     sequence_name = get_sequence_name(model._meta.db_table)

   220                     column_name = self.quote_name(f.column)

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

   222                                            'table': table_name,

   223                                            'column': column_name})

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

   225                     # continue to loop

   226                     break

   227             for f in model._meta.many_to_many:

   228                 if not f.rel.through:

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

   230                     sequence_name = get_sequence_name(f.m2m_db_table())

   231                     column_name = self.quote_name('id')

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

   233                                            'table': table_name,

   234                                            'column': column_name})

   235         return output

   236 

   237     def start_transaction_sql(self):

   238         return ''

   239 

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

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

   242             self.quote_name(tablespace))

   243 

   244     def value_to_db_time(self, value):

   245         if value is None:

   246             return None

   247         if isinstance(value, basestring):

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

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

   250                                  value.second, value.microsecond)

   251 

   252     def year_lookup_bounds_for_date_field(self, value):

   253         first = '%s-01-01'

   254         second = '%s-12-31'

   255         return [first % value, second % value]

   256 

   257     def combine_expression(self, connector, sub_expressions):

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

   259         if connector == '%%':

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

   261         elif connector == '&':

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

   263         elif connector == '|':

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

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

   266 

   267 

   268 class DatabaseWrapper(BaseDatabaseWrapper):

   269 

   270     operators = {

   271         'exact': '= %s',

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

   273         'contains': "LIKEC %s ESCAPE '\\'",

   274         'icontains': "LIKEC UPPER(%s) ESCAPE '\\'",

   275         'gt': '> %s',

   276         'gte': '>= %s',

   277         'lt': '< %s',

   278         'lte': '<= %s',

   279         'startswith': "LIKEC %s ESCAPE '\\'",

   280         'endswith': "LIKEC %s ESCAPE '\\'",

   281         'istartswith': "LIKEC UPPER(%s) ESCAPE '\\'",

   282         'iendswith': "LIKEC UPPER(%s) ESCAPE '\\'",

   283     }

   284     oracle_version = None

   285 

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

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

   288 

   289         self.features = DatabaseFeatures()

   290         self.ops = DatabaseOperations()

   291         self.client = DatabaseClient(self)

   292         self.creation = DatabaseCreation(self)

   293         self.introspection = DatabaseIntrospection(self)

   294         self.validation = BaseDatabaseValidation()

   295 

   296     def _valid_connection(self):

   297         return self.connection is not None

   298 

   299     def _connect_string(self):

   300         settings_dict = self.settings_dict

   301         if len(settings_dict['DATABASE_HOST'].strip()) == 0:

   302             settings_dict['DATABASE_HOST'] = 'localhost'

   303         if len(settings_dict['DATABASE_PORT'].strip()) != 0:

   304             dsn = Database.makedsn(settings_dict['DATABASE_HOST'],

   305                                    int(settings_dict['DATABASE_PORT']),

   306                                    settings_dict['DATABASE_NAME'])

   307         else:

   308             dsn = settings_dict['DATABASE_NAME']

   309         return "%s/%s@%s" % (settings_dict['DATABASE_USER'],

   310                              settings_dict['DATABASE_PASSWORD'], dsn)

   311 

   312     def _cursor(self):

   313         cursor = None

   314         if not self._valid_connection():

   315             conn_string = convert_unicode(self._connect_string())

   316             self.connection = Database.connect(conn_string, **self.settings_dict['DATABASE_OPTIONS'])

   317             cursor = FormatStylePlaceholderCursor(self.connection)

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

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

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

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

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

   323                            "NLS_TERRITORY = 'AMERICA'")

   324             try:

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

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

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

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

   329                 # communicate the current version).

   330                 if self.oracle_version <= 9:

   331                     self.ops.regex_lookup = self.ops.regex_lookup_9

   332                 else:

   333                     self.ops.regex_lookup = self.ops.regex_lookup_10

   334             except ValueError:

   335                 pass

   336             try:

   337                 self.connection.stmtcachesize = 20

   338             except:

   339                 # Django docs specify cx_Oracle version 4.3.1 or higher, but

   340                 # stmtcachesize is available only in 4.3.2 and up.

   341                 pass

   342             connection_created.send(sender=self.__class__)

   343         if not cursor:

   344             cursor = FormatStylePlaceholderCursor(self.connection)

   345         return cursor

   346 

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

   348     def _savepoint_commit(self, sid):

   349         pass

   350 

   351 

   352 class OracleParam(object):

   353     """

   354     Wrapper object for formatting parameters for Oracle. If the string

   355     representation of the value is large enough (greater than 4000 characters)

   356     the input size needs to be set as CLOB. Alternatively, if the parameter

   357     has an `input_size` attribute, then the value of the `input_size` attribute

   358     will be used instead. Otherwise, no input size will be set for the

   359     parameter when executing the query.

   360     """

   361 

   362     def __init__(self, param, cursor, strings_only=False):

   363         if hasattr(param, 'bind_parameter'):

   364             self.smart_str = param.bind_parameter(cursor)

   365         else:

   366             self.smart_str = convert_unicode(param, cursor.charset,

   367                                              strings_only)

   368         if hasattr(param, 'input_size'):

   369             # If parameter has `input_size` attribute, use that.

   370             self.input_size = param.input_size

   371         elif isinstance(param, basestring) and len(param) > 4000:

   372             # Mark any string param greater than 4000 characters as a CLOB.

   373             self.input_size = Database.CLOB

   374         else:

   375             self.input_size = None

   376 

   377 

   378 class InsertIdVar(object):

   379     """

   380     A late-binding cursor variable that can be passed to Cursor.execute

   381     as a parameter, in order to receive the id of the row created by an

   382     insert statement.

   383     """

   384 

   385     def bind_parameter(self, cursor):

   386         param = cursor.var(Database.NUMBER)

   387         cursor._insert_id_var = param

   388         return param

   389 

   390 

   391 class FormatStylePlaceholderCursor(object):

   392     """

   393     Django uses "format" (e.g. '%s') style placeholders, but Oracle uses ":var"

   394     style. This fixes it -- but note that if you want to use a literal "%s" in

   395     a query, you'll need to use "%%s".

   396 

   397     We also do automatic conversion between Unicode on the Python side and

   398     UTF-8 -- for talking to Oracle -- in here.

   399     """

   400     charset = 'utf-8'

   401 

   402     def __init__(self, connection):

   403         self.cursor = connection.cursor()

   404         # Necessary to retrieve decimal values without rounding error.

   405         self.cursor.numbersAsStrings = True

   406         # Default arraysize of 1 is highly sub-optimal.

   407         self.cursor.arraysize = 100

   408 

   409     def _format_params(self, params):

   410         return tuple([OracleParam(p, self, True) for p in params])

   411 

   412     def _guess_input_sizes(self, params_list):

   413         sizes = [None] * len(params_list[0])

   414         for params in params_list:

   415             for i, value in enumerate(params):

   416                 if value.input_size:

   417                     sizes[i] = value.input_size

   418         self.setinputsizes(*sizes)

   419 

   420     def _param_generator(self, params):

   421         return [p.smart_str for p in params]

   422 

   423     def execute(self, query, params=None):

   424         if params is None:

   425             params = []

   426         else:

   427             params = self._format_params(params)

   428         args = [(':arg%d' % i) for i in range(len(params))]

   429         # cx_Oracle wants no trailing ';' for SQL statements.  For PL/SQL, it

   430         # it does want a trailing ';' but not a trailing '/'.  However, these

   431         # characters must be included in the original query in case the query

   432         # is being passed to SQL*Plus.

   433         if query.endswith(';') or query.endswith('/'):

   434             query = query[:-1]

   435         query = convert_unicode(query % tuple(args), self.charset)

   436         self._guess_input_sizes([params])

   437         try:

   438             return self.cursor.execute(query, self._param_generator(params))

   439         except DatabaseError, e:

   440             # cx_Oracle <= 4.4.0 wrongly raises a DatabaseError for ORA-01400.

   441             if e.args[0].code == 1400 and not isinstance(e, IntegrityError):

   442                 e = IntegrityError(e.args[0])

   443             raise e

   444 

   445     def executemany(self, query, params=None):

   446         try:

   447             args = [(':arg%d' % i) for i in range(len(params[0]))]

   448         except (IndexError, TypeError):

   449             # No params given, nothing to do

   450             return None

   451         # cx_Oracle wants no trailing ';' for SQL statements.  For PL/SQL, it

   452         # it does want a trailing ';' but not a trailing '/'.  However, these

   453         # characters must be included in the original query in case the query

   454         # is being passed to SQL*Plus.

   455         if query.endswith(';') or query.endswith('/'):

   456             query = query[:-1]

   457         query = convert_unicode(query % tuple(args), self.charset)

   458         formatted = [self._format_params(i) for i in params]

   459         self._guess_input_sizes(formatted)

   460         try:

   461             return self.cursor.executemany(query,

   462                                 [self._param_generator(p) for p in formatted])

   463         except DatabaseError, e:

   464             # cx_Oracle <= 4.4.0 wrongly raises a DatabaseError for ORA-01400.

   465             if e.args[0].code == 1400 and not isinstance(e, IntegrityError):

   466                 e = IntegrityError(e.args[0])

   467             raise e

   468 

   469     def fetchone(self):

   470         row = self.cursor.fetchone()

   471         if row is None:

   472             return row

   473         return self._rowfactory(row)

   474 

   475     def fetchmany(self, size=None):

   476         if size is None:

   477             size = self.arraysize

   478         return tuple([self._rowfactory(r)

   479                       for r in self.cursor.fetchmany(size)])

   480 

   481     def fetchall(self):

   482         return tuple([self._rowfactory(r)

   483                       for r in self.cursor.fetchall()])

   484 

   485     def _rowfactory(self, row):

   486         # Cast numeric values as the appropriate Python type based upon the

   487         # cursor description, and convert strings to unicode.

   488         casted = []

   489         for value, desc in zip(row, self.cursor.description):

   490             if value is not None and desc[1] is Database.NUMBER:

   491                 precision, scale = desc[4:6]

   492                 if scale == -127:

   493                     if precision == 0:

   494                         # NUMBER column: decimal-precision floating point

   495                         # This will normally be an integer from a sequence,

   496                         # but it could be a decimal value.

   497                         if '.' in value:

   498                             value = Decimal(value)

   499                         else:

   500                             value = int(value)

   501                     else:

   502                         # FLOAT column: binary-precision floating point.

   503                         # This comes from FloatField columns.

   504                         value = float(value)

   505                 elif precision > 0:

   506                     # NUMBER(p,s) column: decimal-precision fixed point.

   507                     # This comes from IntField and DecimalField columns.

   508                     if scale == 0:

   509                         value = int(value)

   510                     else:

   511                         value = Decimal(value)

   512                 elif '.' in value:

   513                     # No type information. This normally comes from a

   514                     # mathematical expression in the SELECT list. Guess int

   515                     # or Decimal based on whether it has a decimal point.

   516                     value = Decimal(value)

   517                 else:

   518                     value = int(value)

   519             elif desc[1] in (Database.STRING, Database.FIXED_CHAR,

   520                              Database.LONG_STRING):

   521                 value = to_unicode(value)

   522             casted.append(value)

   523         return tuple(casted)

   524 

   525     def __getattr__(self, attr):

   526         if attr in self.__dict__:

   527             return self.__dict__[attr]

   528         else:

   529             return getattr(self.cursor, attr)

   530 

   531     def __iter__(self):

   532         return iter(self.cursor)

   533 

   534 

   535 def to_unicode(s):

   536     """

   537     Convert strings to Unicode objects (and return all other data types

   538     unchanged).

   539     """

   540     if isinstance(s, basestring):

   541         return force_unicode(s)

   542     return s

   543 

   544 

   545 def _get_sequence_reset_sql():

   546     # TODO: colorize this SQL code with style.SQL_KEYWORD(), etc.

   547     return """

   548 DECLARE

   549     startvalue integer;

   550     cval integer;

   551 BEGIN

   552     LOCK TABLE %(table)s IN SHARE MODE;

   553     SELECT NVL(MAX(%(column)s), 0) INTO startvalue FROM %(table)s;

   554     SELECT "%(sequence)s".nextval INTO cval FROM dual;

   555     cval := startvalue - cval;

   556     IF cval != 0 THEN

   557         EXECUTE IMMEDIATE 'ALTER SEQUENCE "%(sequence)s" MINVALUE 0 INCREMENT BY '||cval;

   558         SELECT "%(sequence)s".nextval INTO cval FROM dual;

   559         EXECUTE IMMEDIATE 'ALTER SEQUENCE "%(sequence)s" INCREMENT BY 1';

   560     END IF;

   561     COMMIT;

   562 END;

   563 /"""

   564 

   565 

   566 def get_sequence_name(table):

   567     name_length = DatabaseOperations().max_name_length() - 3

   568     return '%s_SQ' % util.truncate_name(table, name_length).upper()

   569 

   570 

   571 def get_trigger_name(table):

   572     name_length = DatabaseOperations().max_name_length() - 3

   573     return '%s_TR' % util.truncate_name(table, name_length).upper()