1 """

     2 Oracle database backend for Django.

     3 

     4 Requires cx_Oracle: http://www.python.net/crew/atuining/cx_Oracle/

     5 """

     6 

     7 import os

     8 

     9 from django.db.backends import BaseDatabaseWrapper, BaseDatabaseFeatures, BaseDatabaseOperations, util

    10 from django.db.backends.oracle import query

    11 from django.utils.datastructures import SortedDict

    12 from django.utils.encoding import smart_str, force_unicode

    13 

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

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

    16 try:

    17     import cx_Oracle as Database

    18 except ImportError, e:

    19     from django.core.exceptions import ImproperlyConfigured

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

    21 

    22 DatabaseError = Database.Error

    23 IntegrityError = Database.IntegrityError

    24 

    25 class DatabaseFeatures(BaseDatabaseFeatures):

    26     allows_group_by_ordinal = False

    27     allows_unique_and_pk = False        # Suppress UNIQUE/PK for Oracle (ORA-02259)

    28     empty_fetchmany_value = ()

    29     needs_datetime_string_cast = False

    30     needs_upper_for_iops = True

    31     supports_tablespaces = True

    32     uses_case_insensitive_names = True

    33     uses_custom_query_class = True

    34 

    35 class DatabaseOperations(BaseDatabaseOperations):

    36     def autoinc_sql(self, table, column):

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

    38         # create a sequence and a trigger.

    39         sq_name = get_sequence_name(table)

    40         tr_name = get_trigger_name(table)

    41         tbl_name = self.quote_name(table)

    42         col_name = self.quote_name(column)

    43         sequence_sql = 'CREATE SEQUENCE %s;' % sq_name

    44         trigger_sql = """

    45             CREATE OR REPLACE TRIGGER %(tr_name)s

    46             BEFORE INSERT ON %(tbl_name)s

    47             FOR EACH ROW

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

    49                 BEGIN

    50                     SELECT %(sq_name)s.nextval

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

    52                 END;

    53                 /""" % locals()

    54         return sequence_sql, trigger_sql

    55 

    56     def date_extract_sql(self, lookup_type, field_name):

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

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

    59 

    60     def date_trunc_sql(self, lookup_type, field_name):

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

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

    63         if lookup_type == 'day':

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

    65         else:

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

    67         return sql

    68 

    69     def datetime_cast_sql(self):

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

    71 

    72     def deferrable_sql(self):

    73         return " DEFERRABLE INITIALLY DEFERRED"

    74 

    75     def drop_sequence_sql(self, table):

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

    77 

    78     def field_cast_sql(self, db_type):

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

    80             return "DBMS_LOB.SUBSTR(%s)"

    81         else:

    82             return "%s"

    83 

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

    85         sq_name = util.truncate_name(table_name, self.max_name_length() - 3)

    86         cursor.execute('SELECT %s_sq.currval FROM dual' % sq_name)

    87         return cursor.fetchone()[0]

    88 

    89     def limit_offset_sql(self, limit, offset=None):

    90         # Limits and offset are too complicated to be handled here.

    91         # Instead, they are handled in django/db/backends/oracle/query.py.

    92         return ""

    93 

    94     def lookup_cast(self, lookup_type):

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

    96             return "UPPER(%s)"

    97         return "%s"

    98 

    99     def max_name_length(self):

   100         return 30

   101 

   102     def query_class(self, DefaultQueryClass):

   103         return query.query_class(DefaultQueryClass, Database)

   104 

   105     def quote_name(self, name):

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

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

   108         # always defaults to uppercase.

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

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

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

   112         return name.upper()

   113 

   114     def random_function_sql(self):

   115         return "DBMS_RANDOM.RANDOM"

   116 

   117     def regex_lookup_9(self, lookup_type):

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

   119 

   120     def regex_lookup_10(self, lookup_type):

   121         if lookup_type == 'regex':

   122             match_option = "'c'"

   123         else:

   124             match_option = "'i'"

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

   126 

   127     def regex_lookup(self, lookup_type):

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

   129         # a cursor to initialize it and recur.

   130         from django.db import connection

   131         connection.cursor()

   132         return connection.ops.regex_lookup(lookup_type)

   133 

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

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

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

   137         if tables:

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

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

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

   141                     (style.SQL_KEYWORD('DELETE'),

   142                      style.SQL_KEYWORD('FROM'),

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

   144                      ) for table in tables]

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

   146             # ALTER code will reset the sequence to 0.

   147             for sequence_info in sequences:

   148                 table_name = sequence_info['table']

   149                 seq_name = get_sequence_name(table_name)

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

   151                 query = _get_sequence_reset_sql() % {'sequence': seq_name,

   152                                                      'table': self.quote_name(table_name),

   153                                                      'column': column_name}

   154                 sql.append(query)

   155             return sql

   156         else:

   157             return []

   158 

   159     def sequence_reset_sql(self, style, model_list):

   160         from django.db import models

   161         output = []

   162         query = _get_sequence_reset_sql()

   163         for model in model_list:

   164             for f in model._meta.fields:

   165                 if isinstance(f, models.AutoField):

   166                     sequence_name = get_sequence_name(model._meta.db_table)

   167                     column_name = self.quote_name(f.db_column or f.name)

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

   169                                            'table': model._meta.db_table,

   170                                            'column': column_name})

   171                     break # Only one AutoField is allowed per model, so don't bother continuing.

   172             for f in model._meta.many_to_many:

   173                 sequence_name = get_sequence_name(f.m2m_db_table())

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

   175                                        'table': f.m2m_db_table(),

   176                                        'column': self.quote_name('id')})

   177         return output

   178 

   179     def start_transaction_sql(self):

   180         return ''

   181 

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

   183         return "%sTABLESPACE %s" % ((inline and "USING INDEX " or ""), self.quote_name(tablespace))

   184 

   185 class DatabaseWrapper(BaseDatabaseWrapper):

   186     features = DatabaseFeatures()

   187     ops = DatabaseOperations()

   188     operators = {

   189         'exact': '= %s',

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

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

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

   193         'gt': '> %s',

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

   195         'lt': '< %s',

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

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

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

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

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

   201     }

   202     oracle_version = None

   203 

   204     def _valid_connection(self):

   205         return self.connection is not None

   206 

   207     def _cursor(self, settings):

   208         cursor = None

   209         if not self._valid_connection():

   210             if len(settings.DATABASE_HOST.strip()) == 0:

   211                 settings.DATABASE_HOST = 'localhost'

   212             if len(settings.DATABASE_PORT.strip()) != 0:

   213                 dsn = Database.makedsn(settings.DATABASE_HOST, int(settings.DATABASE_PORT), settings.DATABASE_NAME)

   214                 self.connection = Database.connect(settings.DATABASE_USER, settings.DATABASE_PASSWORD, dsn, **self.options)

   215             else:

   216                 conn_string = "%s/%s@%s" % (settings.DATABASE_USER, settings.DATABASE_PASSWORD, settings.DATABASE_NAME)

   217                 self.connection = Database.connect(conn_string, **self.options)

   218             cursor = FormatStylePlaceholderCursor(self.connection)

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

   220             # once when we create a new connection.

   221             cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD' "

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

   223             try:

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

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

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

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

   228                 # communicate the current version).

   229                 if self.oracle_version <= 9:

   230                     self.ops.regex_lookup = self.ops.regex_lookup_9

   231                 else:

   232                     self.ops.regex_lookup = self.ops.regex_lookup_10

   233             except ValueError:

   234                 pass

   235             try:

   236                 self.connection.stmtcachesize = 20

   237             except:

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

   239                 # stmtcachesize is available only in 4.3.2 and up.

   240                 pass

   241         if not cursor:

   242             cursor = FormatStylePlaceholderCursor(self.connection)

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

   244         cursor.arraysize = 100

   245         return cursor

   246 

   247 class FormatStylePlaceholderCursor(Database.Cursor):

   248     """

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

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

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

   252 

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

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

   255     """

   256     charset = 'utf-8'

   257 

   258     def _format_params(self, params):

   259         if isinstance(params, dict):

   260             result = {}

   261             charset = self.charset

   262             for key, value in params.items():

   263                 result[smart_str(key, charset)] = smart_str(value, charset)

   264             return result

   265         else:

   266             return tuple([smart_str(p, self.charset, True) for p in params])

   267 

   268     def _guess_input_sizes(self, params_list):

   269         # Mark any string parameter greater than 4000 characters as an NCLOB.

   270         if isinstance(params_list[0], dict):

   271             sizes = {}

   272             iterators = [params.iteritems() for params in params_list]

   273         else:

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

   275             iterators = [enumerate(params) for params in params_list]

   276         for iterator in iterators:

   277             for key, value in iterator:

   278                 if isinstance(value, basestring) and len(value) > 4000:

   279                     sizes[key] = Database.NCLOB

   280         if isinstance(sizes, dict):

   281             self.setinputsizes(**sizes)

   282         else:

   283             self.setinputsizes(*sizes)

   284 

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

   286         if params is None:

   287             params = []

   288         else:

   289             params = self._format_params(params)

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

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

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

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

   294         # is being passed to SQL*Plus.

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

   296             query = query[:-1]

   297         query = smart_str(query, self.charset) % tuple(args)

   298         self._guess_input_sizes([params])

   299         return Database.Cursor.execute(self, query, params)

   300 

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

   302         try:

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

   304         except (IndexError, TypeError):

   305           # No params given, nothing to do

   306           return None

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

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

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

   310         # is being passed to SQL*Plus.

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

   312             query = query[:-1]

   313         query = smart_str(query, self.charset) % tuple(args)

   314         new_param_list = [self._format_params(i) for i in params]

   315         self._guess_input_sizes(new_param_list)

   316         return Database.Cursor.executemany(self, query, new_param_list)

   317 

   318     def fetchone(self):

   319         row = Database.Cursor.fetchone(self)

   320         if row is None:

   321             return row

   322         return tuple([to_unicode(e) for e in row])

   323 

   324     def fetchmany(self, size=None):

   325         if size is None:

   326             size = self.arraysize

   327         return tuple([tuple([to_unicode(e) for e in r]) for r in Database.Cursor.fetchmany(self, size)])

   328 

   329     def fetchall(self):

   330         return tuple([tuple([to_unicode(e) for e in r]) for r in Database.Cursor.fetchall(self)])

   331 

   332 def to_unicode(s):

   333     """

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

   335     unchanged).

   336     """

   337     if isinstance(s, basestring):

   338         return force_unicode(s)

   339     return s

   340 

   341 def _get_sequence_reset_sql():

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

   343     return """

   344         DECLARE

   345             startvalue integer;

   346             cval integer;

   347         BEGIN

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

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

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

   351             cval := startvalue - cval;

   352             IF cval != 0 THEN

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

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

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

   356             END IF;

   357             COMMIT;

   358         END;

   359         /"""

   360 

   361 def get_sequence_name(table):

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

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

   364 

   365 def get_trigger_name(table):

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

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