django/db/backends/oracle/base.py

   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()