Refs #25002 -- Supported textual to temporal column alteration on Oracle.

Thanks to Tim Graham for the report and Shai Berger for the review.

2 files changed, 65 insertions, 15 deletions

diff --git a/django/db/backends/oracle/schema.py b/django/db/backends/oracle/schema.py
index e4b02fcc7b..a10bb08e17 100644
--- a/django/db/backends/oracle/schema.py
+++ b/django/db/backends/oracle/schema.py
@@ -1,6 +1,7 @@
 import binascii
 import copy
 import datetime
+import re
 
 from django.db.backends.base.schema import BaseDatabaseSchemaEditor
 from django.db.utils import DatabaseError
@@ -49,44 +50,58 @@ class DatabaseSchemaEditor(BaseDatabaseSchemaEditor):
 
     def alter_field(self, model, old_field, new_field, strict=False):
         try:
-            # Run superclass action
             super(DatabaseSchemaEditor, self).alter_field(model, old_field, new_field, strict)
         except DatabaseError as e:
             description = str(e)
-            # If we're changing to/from LOB fields, we need to do a
+            # If we're changing type to an unsupported type we need a
             # SQLite-ish workaround
             if 'ORA-22858' in description or 'ORA-22859' in description:
-                self._alter_field_lob_workaround(model, old_field, new_field)
+                self._alter_field_type_workaround(model, old_field, new_field)
             else:
                 raise
 
-    def _alter_field_lob_workaround(self, model, old_field, new_field):
+    def _alter_field_type_workaround(self, model, old_field, new_field):
         """
-        Oracle refuses to change a column type from/to LOB to/from a regular
-        column. In Django, this shows up when the field is changed from/to
-        a TextField.
+        Oracle refuses to change from some type to other type.
         What we need to do instead is:
-        - Add the desired field with a temporary name
+        - Add a nullable version of the desired field with a temporary name
         - Update the table to transfer values from old to new
         - Drop old column
-        - Rename the new column
+        - Rename the new column and possibly drop the nullable property
         """
         # Make a new field that's like the new one but with a temporary
         # column name.
         new_temp_field = copy.deepcopy(new_field)
+        new_temp_field.null = True
         new_temp_field.column = self._generate_temp_name(new_field.column)
         # Add it
         self.add_field(model, new_temp_field)
+        # Explicit data type conversion
+        # https://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements002.htm#sthref340
+        new_value = self.quote_name(old_field.column)
+        old_type = old_field.db_type(self.connection)
+        if re.match('^N?CLOB', old_type):
+            new_value = "TO_CHAR(%s)" % new_value
+            old_type = 'VARCHAR2'
+        if re.match('^N?VARCHAR2', old_type):
+            new_internal_type = new_field.get_internal_type()
+            if new_internal_type == 'DateField':
+                new_value = "TO_DATE(%s, 'YYYY-MM-DD')" % new_value
+            elif new_internal_type == 'DateTimeField':
+                new_value = "TO_TIMESTAMP(%s, 'YYYY-MM-DD HH24:MI:SS.FF')" % new_value
+            elif new_internal_type == 'TimeField':
+                # TimeField are stored as TIMESTAMP with a 1900-01-01 date part.
+                new_value = "TO_TIMESTAMP(CONCAT('1900-01-01 ', %s), 'YYYY-MM-DD HH24:MI:SS.FF')" % new_value
         # Transfer values across
         self.execute("UPDATE %s set %s=%s" % (
             self.quote_name(model._meta.db_table),
             self.quote_name(new_temp_field.column),
-            self.quote_name(old_field.column),
+            new_value,
         ))
         # Drop the old field
         self.remove_field(model, old_field)
-        # Rename the new field
-        self.alter_field(model, new_temp_field, new_field)
+        # Rename and possibly make the new field NOT NULL
+        super(DatabaseSchemaEditor, self).alter_field(model, new_temp_field, new_field)
 
     def normalize_name(self, name):
         """
diff --git a/tests/schema/tests.py b/tests/schema/tests.py
index 66a64ef31b..114d2bebaa 100644
--- a/tests/schema/tests.py
+++ b/tests/schema/tests.py
@@ -9,8 +9,8 @@ from django.db import (
 from django.db.models import Model
 from django.db.models.fields import (
     AutoField, BigIntegerField, BinaryField, BooleanField, CharField,
-    DateTimeField, IntegerField, PositiveIntegerField, SlugField, TextField,
-    TimeField,
+    DateField, DateTimeField, IntegerField, PositiveIntegerField, SlugField,
+    TextField, TimeField,
 )
 from django.db.models.fields.related import (
     ForeignKey, ManyToManyField, OneToOneField,
@@ -448,18 +448,53 @@ class SchemaTests(TransactionTestCase):
         with connection.schema_editor() as editor:
             editor.alter_field(Note, old_field, new_field, strict=True)
 
+    def test_alter_text_field_to_date_field(self):
+        """
+        #25002 - Test conversion of text field to date field.
+        """
+        with connection.schema_editor() as editor:
+            editor.create_model(Note)
+        Note.objects.create(info='1988-05-05')
+        old_field = Note._meta.get_field('info')
+        new_field = DateField(blank=True)
+        new_field.set_attributes_from_name('info')
+        with connection.schema_editor() as editor:
+            editor.alter_field(Note, old_field, new_field, strict=True)
+        # Make sure the field isn't nullable
+        columns = self.column_classes(Note)
+        self.assertFalse(columns['info'][1][6])
+
+    def test_alter_text_field_to_datetime_field(self):
+        """
+        #25002 - Test conversion of text field to datetime field.
+        """
+        with connection.schema_editor() as editor:
+            editor.create_model(Note)
+        Note.objects.create(info='1988-05-05 3:16:17.4567')
+        old_field = Note._meta.get_field('info')
+        new_field = DateTimeField(blank=True)
+        new_field.set_attributes_from_name('info')
+        with connection.schema_editor() as editor:
+            editor.alter_field(Note, old_field, new_field, strict=True)
+        # Make sure the field isn't nullable
+        columns = self.column_classes(Note)
+        self.assertFalse(columns['info'][1][6])
+
     def test_alter_text_field_to_time_field(self):
         """
         #25002 - Test conversion of text field to time field.
         """
         with connection.schema_editor() as editor:
             editor.create_model(Note)
-        Note.objects.create(info='3:16')
+        Note.objects.create(info='3:16:17.4567')
         old_field = Note._meta.get_field('info')
         new_field = TimeField(blank=True)
         new_field.set_attributes_from_name('info')
         with connection.schema_editor() as editor:
             editor.alter_field(Note, old_field, new_field, strict=True)
+        # Make sure the field isn't nullable
+        columns = self.column_classes(Note)
+        self.assertFalse(columns['info'][1][6])
 
     @skipIfDBFeature('interprets_empty_strings_as_nulls')
     def test_alter_textual_field_keep_null_status(self):