boulder-oracle-sprint: Changed Oracle CLOB to NCLOB for i18n.

Fixed Oracle backend's get_date_trunc_sql() function.


git-svn-id: http://code.djangoproject.com/svn/django/branches/boulder-oracle-sprint@4064 bcc190cf-cafb-0310-a4f2-bffc1f526a37

7 files changed, 125 insertions, 106 deletions

diff --git a/django/core/management.py b/django/core/management.py
index 2ada98fb24..2dcba1f198 100644
--- a/django/core/management.py
+++ b/django/core/management.py
@@ -203,16 +203,15 @@ def _get_sql_model_create(model, known_models=set()):
         sequence_statement = 'CREATE SEQUENCE %s;' % sequence_name
         final_output.append(sequence_statement)
         trigger_statement = '' + \
-            'CREATE OR REPLACE trigger %s\n'    % truncate_name('%s_tr' % opts.db_table, backend.get_max_name_length()) + \
-            '  before insert on %s\n'           % backend.quote_name(opts.db_table) + \
-            '    for each row\n'  + \
-            '      when (new.id is NULL)\n' + \
-            '        begin\n' + \
-            '         select %s.NEXTVAL into :new.id from DUAL;\n' % sequence_name + \
-            '      end;\n'
+            'CREATE OR REPLACE TRIGGER %s\n'    % truncate_name('%s_tr' % opts.db_table, backend.get_max_name_length()) + \
+            '  BEFORE INSERT ON %s\n'           % backend.quote_name(opts.db_table) + \
+            '  FOR EACH ROW\n'  + \
+            '  WHEN (new.id IS NULL)\n' + \
+            '    BEGIN\n' + \
+            '      SELECT %s.nextval INTO :new.id FROM dual;\n' % sequence_name + \
+            '    END;\n'
         final_output.append(trigger_statement)
 
-
     return final_output, pending_references
 
 def _get_sql_for_pending_references(model, pending_references):
@@ -283,13 +282,13 @@ def _get_many_to_many_sql_for_model(model):
                 sequence_statement = 'CREATE SEQUENCE %s;' % sequence_name
                 final_output.append(sequence_statement)
                 trigger_statement = '' + \
-                'CREATE OR REPLACE trigger %s\n'    % truncate_name('%s_tr' % m_table, backend.get_max_name_length()) + \
-                '  before insert on %s\n'           % backend.quote_name(m_table) + \
-                '    for each row\n'  + \
-                '      when (new.id is NULL)\n' + \
-                '        begin\n' + \
-                '         select %s.NEXTVAL into :new.id from DUAL;\n' % sequence_name + \
-                '      end;\n'
+                    'CREATE OR REPLACE TRIGGER %s\n'    % truncate_name('%s_tr' % m_table, backend.get_max_name_length()) + \
+                    '  BEFORE INSERT ON %s\n'           % backend.quote_name(m_table) + \
+                    '  FOR EACH ROW\n'  + \
+                    '  WHEN (new.id IS NULL)\n' + \
+                    '    BEGIN\n' + \
+                    '      SELECT %s.nextval INTO :new.id FROM dual;\n' % sequence_name + \
+                    '    END;\n'
                 final_output.append(trigger_statement)
     return final_output
 
diff --git a/django/db/backends/mysql/base.py b/django/db/backends/mysql/base.py
index c39b9ffca8..33ed74c10b 100644
--- a/django/db/backends/mysql/base.py
+++ b/django/db/backends/mysql/base.py
@@ -180,9 +180,6 @@ def get_drop_foreignkey_sql():
 def get_pk_default_value():
     return "DEFAULT"
 
-def get_max_name_length():
-    return 64
-
 OPERATOR_MAPPING = {
     'exact': '= %s',
     'iexact': 'LIKE %s',
diff --git a/django/db/backends/oracle/base.py b/django/db/backends/oracle/base.py
index 813ab8d385..883b3f433a 100644
--- a/django/db/backends/oracle/base.py
+++ b/django/db/backends/oracle/base.py
@@ -40,8 +40,8 @@ class DatabaseWrapper(local):
                 conn_string = "%s/%s@%s" % (settings.DATABASE_USER, settings.DATABASE_PASSWORD, settings.DATABASE_NAME)
                 self.connection = Database.connect(conn_string)
         # set oracle date to ansi date format
-        cursor =  self.connection.cursor()
-        cursor.execute("alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'")
+        cursor = self.connection.cursor()
+        cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'")
         cursor.close()
         return FormatStylePlaceholderCursor(self.connection)
 
@@ -68,54 +68,62 @@ class FormatStylePlaceholderCursor(Database.Cursor):
     Django uses "format" (e.g. '%s') style placeholders, but Oracle uses ":var" style.
     This fixes it -- but note that if you want to use a literal "%s" in a query,
     you'll need to use "%%s".
-    """
-    def execute(self, query, params=None):
+    """    
+    def _rewrite_args(self, query, params=None):
         if params is None: 
             params = []
-        args = [(':arg%s' % i) for i in range(len(params))]
+        args = [(':arg%d' % i) for i in range(len(params))]
         query = query % tuple(args)
-        
-        # cx can not execute the query with the closing ';' 
+        # cx_Oracle cannot execute a query with the closing ';' 
         if query.endswith(';'):
             query = query[:-1]
+        return query, params
+    
+    def execute(self, query, params=None):
+        query, params = self._rewrite_args(query, params)
+        self.arraysize = 200
         return Database.Cursor.execute(self, query, params)
 
     def executemany(self, query, params=None):
-        if params is None: params = []
-        query = self.convert_arguments(query, len(params[0]))
-        # cx can not execute the query with the closing ';' 
-        if query.endswith(';') :
-            query = query[0:len(query)-1]
+        query, params = self._rewrite_args(query, params)
+        self.arraysize = 200
         return Database.Cursor.executemany(self, query, params)
 
+    
 def quote_name(name):
-    if name.startswith('"') and name.endswith('"'):
-        return name # Quoting once is enough.
-        
-    # Oracle requires that quoted names be uppercase.
-    return '"%s"' % name.upper()
+    # Oracle requires that quoted names be uppercase.    
+    name = name.upper()
+    if not name.startswith('"') and not name.endswith('"'):
+        name = '"%s"' % util.truncate_name(name.upper(), get_max_name_length())
+    return name
 
 dictfetchone = util.dictfetchone
 dictfetchmany = util.dictfetchmany
 dictfetchall  = util.dictfetchall
 
 def get_last_insert_id(cursor, table_name, pk_name):
-    query = "SELECT %s_sq.currval from dual" % table_name
-    cursor.execute(query)
+    cursor.execute('SELECT %s_sq.currval FROM dual' % table_name)
     return cursor.fetchone()[0]
 
 def get_date_extract_sql(lookup_type, table_name):
     # lookup_type is 'year', 'month', 'day'
-    # http://www.psoug.org/reference/date_func.html
+    # http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96540/functions42a.htm#1017163
     return "EXTRACT(%s FROM %s)" % (lookup_type, table_name)
 
 def get_date_trunc_sql(lookup_type, field_name):
-    return "EXTRACT(%s FROM TRUNC(%s))" % (lookup_type, field_name)
+    # lookup_type is 'year', 'month', 'day'
+    # 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 get_limit_offset_sql(limit, offset=None):
     # Limits and offset are too complicated to be handled here.
-    # Instead, they are handled in django/db/query.py.
-    pass
+    # Instead, they are handled in django/db/backends/oracle/query.py.
+    raise NotImplementedError
 
 def get_random_function_sql():
     return "DBMS_RANDOM.RANDOM"
diff --git a/django/db/backends/oracle/creation.py b/django/db/backends/oracle/creation.py
index 689ce3b244..b6cdd7caf8 100644
--- a/django/db/backends/oracle/creation.py
+++ b/django/db/backends/oracle/creation.py
@@ -1,34 +1,40 @@
-import sys
+import sys, time
 
+# This dictionary maps Field objects to their associated Oracle column
+# types, as strings. Column-type strings can contain format strings; they'll
+# be interpolated against the values of Field.__dict__ before being output.
+# If a column type is set to None, it won't be included in the output.
 DATA_TYPES = {
-    'AutoField':                    'number(11)',
-    'BooleanField':                 'number(1) CHECK (%(column)s IN (0,1))',
-    'CharField':                    'varchar2(%(maxlength)s)',
-    'CommaSeparatedIntegerField':   'varchar2(%(maxlength)s)',
-    'DateField':                    'date',
-    'DateTimeField':                'timestamp with time zone',
-    'FileField':                    'varchar2(100)',
-    'FilePathField':                'varchar2(100)',
-    'FloatField':                   'number(%(max_digits)s, %(decimal_places)s)',
-    'ImageField':                   'varchar2(100)',
-    'IntegerField':                 'number(11)',
-    'IPAddressField':               'char(15)',
+    'AutoField':                    'NUMBER(11)',
+    'BooleanField':                 'NUMBER(1) CHECK (%(column)s IN (0,1))',
+    'CharField':                    'VARCHAR2(%(maxlength)s)',
+    'CommaSeparatedIntegerField':   'VARCHAR2(%(maxlength)s)',
+    'DateField':                    'DATE',
+    'DateTimeField':                'TIMESTAMP WITH TIME ZONE',
+    'FileField':                    'VARCHAR2(100)',
+    'FilePathField':                'VARCHAR2(100)',
+    'FloatField':                   'NUMBER(%(max_digits)s, %(decimal_places)s)',
+    'ImageField':                   'VARCHAR2(100)',
+    'IntegerField':                 'NUMBER(11)',
+    'IPAddressField':               'CHAR(15)',
     'ManyToManyField':              None,
-    'NullBooleanField':             'number(1) CHECK ((%(column)s IN (0,1)) OR (%(column)s IS NULL))',
-    'OneToOneField':                'number(11)',
-    'PhoneNumberField':             'varchar2(20)',
-    'PositiveIntegerField':         'number(11) CHECK (%(column)s >= 1)',
-    'PositiveSmallIntegerField':    'number(11) CHECK (%(column)s >= 1)',
-    'SlugField':                    'varchar2(50)',
-    'SmallIntegerField':            'number(11)',
-    'TextField':                    'clob',
-    'TimeField':                    'timestamp',
-    'URLField':                     'varchar2(200)',
-    'USStateField':                 'char(2)',
+    'NullBooleanField':             'NUMBER(1) CHECK ((%(column)s IN (0,1)) OR (%(column)s IS NULL))',
+    'OneToOneField':                'NUMBER(11)',
+    'PhoneNumberField':             'VARCHAR2(20)',
+    'PositiveIntegerField':         'NUMBER(11) CHECK (%(column)s >= 1)',
+    'PositiveSmallIntegerField':    'NUMBER(11) CHECK (%(column)s >= 1)',
+    'SlugField':                    'VARCHAR2(50)',
+    'SmallIntegerField':            'NUMBER(11)',
+    'TextField':                    'NCLOB',
+    'TimeField':                    'TIMESTAMP',
+    'URLField':                     'VARCHAR2(200)',
+    'USStateField':                 'CHAR(2)',
 }
 
 TEST_DATABASE_PREFIX = 'test_'
 PASSWORD = 'Im_a_lumberjack'
+OLD_DATABASE_USER = None
+OLD_DATABASE_PASSWORD = None
 
 def create_test_db(settings, connection, backend, verbosity=1, autoclobber=False):
     if verbosity >= 1:
@@ -46,7 +52,7 @@ def create_test_db(settings, connection, backend, verbosity=1, autoclobber=False
         if autoclobber or confirm == 'yes':
             try:
                 if verbosity >= 1:
-                    print "Destroying old test database..."                
+                    print "Destroying old test database..."
                 _destroy_test_db(cursor, TEST_DATABASE_NAME, verbosity)
                 if verbosity >= 1:
                     print "Creating test database..."
@@ -66,13 +72,15 @@ def create_test_db(settings, connection, backend, verbosity=1, autoclobber=False
     # the side effect of initializing the test database.
     cursor = connection.cursor()
         
-def destroy_test_db(settings, connection, old_database_name, verbosity=1):
+def destroy_test_db(settings, connection, backend, old_database_name, verbosity=1):
     if verbosity >= 1:
         print "Destroying test database..."
     connection.close()
 
     TEST_DATABASE_NAME = _test_database_name(settings)
     settings.DATABASE_NAME = old_database_name
+    #settings.DATABASE_USER = 'old_user'
+    #settings.DATABASE_PASSWORD = 'old_password'
 
     cursor = connection.cursor()
     time.sleep(1) # To avoid "database is being accessed by other users" errors.
@@ -83,19 +91,18 @@ def _create_test_db(cursor, dbname, verbosity):
     if verbosity >= 2:
         print "_create_test_db(): dbname = %s" % dbname
     statements = [
-        """create tablespace %(user)s
-           datafile '%(user)s.dbf' size 10M autoextend on next 10M  maxsize 20M
+        """CREATE TABLESPACE %(user)s
+           DATAFILE '%(user)s.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 20M
         """,
-        """create temporary tablespace %(user)s_temp
-           tempfile '%(user)s_temp.dbf' size 10M autoextend on next 10M  maxsize 20M
+        """CREATE TEMPORARY TABLESPACE %(user)s_temp
+           TEMPFILE '%(user)s_temp.dbf' SIZE 10M AUTOEXTEND ON NEXT 10M MAXSIZE 20M
         """,
-        """create user %(user)s
-           identified by %(password)s
-           default tablespace %(user)s
-           temporary tablespace %(user)s_temp
+        """CREATE USER %(user)s
+           IDENTIFIED BY %(password)s
+           DEFAULT TABLESPACE %(user)s
+           TEMPORARY TABLESPACE %(user)s_temp
         """,
-        """grant resource to %(user)s""",
-        """grant connect to %(user)s""",
+        """GRANT CONNECT, RESOURCE TO %(user)s""",
     ]
     _execute_statements(cursor, statements, dbname, verbosity)
     
@@ -103,9 +110,9 @@ def _destroy_test_db(cursor, dbname, verbosity):
     if verbosity >= 2:
         print "_destroy_test_db(): dbname=%s" % dbname
     statements = [
-        """drop user %(user)s cascade""",
-        """drop tablespace %(user)s including contents and datafiles cascade constraints""",
-        """drop tablespace %(user)s_temp including contents and datafiles cascade constraints""",
+        'DROP USER %(user)s CASCADE',
+        'DROP TABLESPACE %(user)s INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS',
+        'DROP TABLESPACE %(user)s_TEMP INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS',
         ]
     _execute_statements(cursor, statements, dbname, verbosity)
 
@@ -119,8 +126,7 @@ def _execute_statements(cursor, statements, dbname, verbosity):
             cursor.execute(stmt)
         except Exception, err:
             sys.stderr.write("Failed (%s)\n" % (err))
-            if required:
-                raise
+            raise
 
 def _test_database_name(settings):
     if settings.TEST_DATABASE_NAME:
@@ -128,4 +134,3 @@ def _test_database_name(settings):
     else:
         name = TEST_DATABASE_PREFIX + settings.DATABASE_NAME
     return name
-    
diff --git a/django/db/backends/oracle/introspection.py b/django/db/backends/oracle/introspection.py
index e556228555..02479e71e1 100644
--- a/django/db/backends/oracle/introspection.py
+++ b/django/db/backends/oracle/introspection.py
@@ -28,16 +28,16 @@ def get_relations(cursor, table_name):
     cursor.execute("""
 SELECT ta.column_id - 1, tb.table_name, tb.column_id - 1
 FROM   user_constraints, USER_CONS_COLUMNS ca, USER_CONS_COLUMNS cb,
-user_tab_cols ta, user_tab_cols tb
+       user_tab_cols ta, user_tab_cols tb
 WHERE  user_constraints.table_name = %s AND
-      ta.table_name = %s AND
-      ta.column_name = ca.column_name AND
-      ca.table_name = %s AND
-      user_constraints.constraint_name = ca.constraint_name AND
-      user_constraints.r_constraint_name = cb.constraint_name AND
-      cb.table_name = tb.table_name AND
-      cb.column_name = tb.column_name AND
-      ca.position = cb.position""", [table_name, table_name, table_name])
+       ta.table_name = %s AND
+       ta.column_name = ca.column_name AND
+       ca.table_name = %s AND
+       user_constraints.constraint_name = ca.constraint_name AND
+       user_constraints.r_constraint_name = cb.constraint_name AND
+       cb.table_name = tb.table_name AND
+       cb.column_name = tb.column_name AND
+       ca.position = cb.position""", [table_name, table_name, table_name])
 
     relations = {}
     for row in cursor.fetchall():
diff --git a/django/db/backends/oracle/query.py b/django/db/backends/oracle/query.py
index 5ffbb29857..e1ab449665 100644
--- a/django/db/backends/oracle/query.py
+++ b/django/db/backends/oracle/query.py
@@ -67,7 +67,7 @@ def get_query_set_class(DefaultQuerySet):
 
             # so here's the logic;
             # 1. retrieve each row in turn
-            # 2. convert CLOBs
+            # 2. convert NCLOBs
 
             def resolve_lobs(row):
                 for field in row:
diff --git a/django/db/models/query.py b/django/db/models/query.py
index af5340eaf3..3f27e565e1 100644
--- a/django/db/models/query.py
+++ b/django/db/models/query.py
@@ -171,7 +171,7 @@ class _QuerySet(object):
         cursor = connection.cursor()
         
         full_query = None
-        select, sql, params = self._get_sql_clause() 
+        select, sql, params, full_query = self._get_sql_clause() 
         cursor.execute("SELECT " + (self._distinct and "DISTINCT " or "") + ",".join(select) + sql, params) 
 
         fill_cache = self._select_related
@@ -196,7 +196,7 @@ class _QuerySet(object):
         counter._offset = None
         counter._limit = None
         counter._select_related = False
-        select, sql, params = counter._get_sql_clause()[:3]
+        select, sql, params, full_query = counter._get_sql_clause()
         cursor = connection.cursor()
         if self._distinct:
             id_col = "%s.%s" % (backend.quote_name(self.model._meta.db_table),
@@ -535,7 +535,7 @@ class ValuesQuerySet(QuerySet):
             field_names = [f.attname for f in self.model._meta.fields]
 
         cursor = connection.cursor()
-        select, sql, params = self._get_sql_clause()[:3]
+        select, sql, params, full_query = self._get_sql_clause()
         select = ['%s.%s' % (backend.quote_name(self.model._meta.db_table), backend.quote_name(c)) for c in columns]
         cursor.execute("SELECT " + (self._distinct and "DISTINCT " or "") + ",".join(select) + sql, params)
         while 1:
@@ -557,16 +557,26 @@ class DateQuerySet(QuerySet):
         if self._field.null:
             self._where.append('%s.%s IS NOT NULL' % \
                 (backend.quote_name(self.model._meta.db_table), backend.quote_name(self._field.column)))
-        select, sql, params = self._get_sql_clause()
-        sql = 'SELECT %s %s GROUP BY 1 ORDER BY 1 %s' % \
-            (backend.get_date_trunc_sql(self._kind, '%s.%s' % (backend.quote_name(self.model._meta.db_table),
-            backend.quote_name(self._field.column))), sql, self._order)
-        cursor = connection.cursor()
-        cursor.execute(sql, params)
-        # We have to manually run typecast_timestamp(str()) on the results, because
-        # MySQL doesn't automatically cast the result of date functions as datetime
-        # objects -- MySQL returns the values as strings, instead.
-        return [typecast_timestamp(str(row[0])) for row in cursor.fetchall()]
+        select, sql, params, full_query = self._get_sql_clause()
+        table_name = backend.quote_name(self.model._meta.db_table)
+        field_name = backend.quote_name(self._field.column)
+        date_trunc_sql = backend.get_date_trunc_sql(self._kind,
+                                                    '%s.%s' % (table_name, field_name))
+        fmt = 'SELECT %s %s GROUP BY %s ORDER BY 1 %s'
+
+        if settings.DATABASE_ENGINE == 'oracle':
+            sql = fmt % (date_trunc_sql, sql, date_trunc_sql, self._order)
+            cursor = connection.cursor()
+            cursor.execute(sql, params)
+            return [row[0] for row in cursor.fetchall()]  
+        else:
+            sql = fmt % (date_trunc_sql, sql, 1, self._order_by)
+            cursor = connection.cursor()
+            cursor.execute(sql, params)
+            # We have to manually run typecast_timestamp(str()) on the results, because
+            # MySQL doesn't automatically cast the result of date functions as datetime
+            # objects -- MySQL returns the values as strings, instead.
+            return [typecast_timestamp(str(row[0])) for row in cursor.fetchall()]
 
     def _clone(self, klass=None, **kwargs):
         c = super(DateQuerySet, self)._clone(klass, **kwargs)