Debian Codegraph @

django/db/backends/oracle/schema.py

raw
1 import copy
2 import datetime
3 import re
4
5 from django.db import DatabaseError
6 from django.db.backends.base.schema import (
7 BaseDatabaseSchemaEditor,
8 _related_non_m2m_objects,
9 )
10 from django.utils.duration import duration_iso_string
11
12
13 class DatabaseSchemaEditor(BaseDatabaseSchemaEditor):
14 sql_create_column = "ALTER TABLE %(table)s ADD %(column)s %(definition)s"
15 sql_alter_column_type = "MODIFY %(column)s %(type)s%(collation)s"
16 sql_alter_column_null = "MODIFY %(column)s NULL"
17 sql_alter_column_not_null = "MODIFY %(column)s NOT NULL"
18 sql_alter_column_default = "MODIFY %(column)s DEFAULT %(default)s"
19 sql_alter_column_no_default = "MODIFY %(column)s DEFAULT NULL"
20 sql_alter_column_no_default_null = sql_alter_column_no_default
21
22 sql_delete_column = "ALTER TABLE %(table)s DROP COLUMN %(column)s"
23 sql_create_column_inline_fk = (
24 "CONSTRAINT %(name)s REFERENCES %(to_table)s(%(to_column)s)%(deferrable)s"
25 )
26 sql_delete_table = "DROP TABLE %(table)s CASCADE CONSTRAINTS"
27 sql_create_index = "CREATE INDEX %(name)s ON %(table)s (%(columns)s)%(extra)s"
28
29 def quote_value(self, value):
30 if isinstance(value, (datetime.date, datetime.time, datetime.datetime)):
31 return "'%s'" % value
32 elif isinstance(value, datetime.timedelta):
33 return "'%s'" % duration_iso_string(value)
34 elif isinstance(value, str):
35 return "'%s'" % value.replace("'", "''")
36 elif isinstance(value, (bytes, bytearray, memoryview)):
37 return "'%s'" % value.hex()
38 elif isinstance(value, bool):
39 return "1" if value else "0"
40 else:
41 return str(value)
42
43 def remove_field(self, model, field):
44 # If the column is an identity column, drop the identity before
45 # removing the field.
46 if self._is_identity_column(model._meta.db_table, field.column):
47 self._drop_identity(model._meta.db_table, field.column)
48 super().remove_field(model, field)
49
50 def delete_model(self, model):
51 # Run superclass action
52 super().delete_model(model)
53 # Clean up manually created sequence.
54 self.execute(
55 """
56 DECLARE
57 i INTEGER;
58 BEGIN
59 SELECT COUNT(1) INTO i FROM USER_SEQUENCES
60 WHERE SEQUENCE_NAME = '%(sq_name)s';
61 IF i = 1 THEN
62 EXECUTE IMMEDIATE 'DROP SEQUENCE "%(sq_name)s"';
63 END IF;
64 END;
65 /"""
66 % {
67 "sq_name": self.connection.ops._get_no_autofield_sequence_name(
68 model._meta.db_table
69 )
70 }
71 )
72
73 def alter_field(self, model, old_field, new_field, strict=False):
74 try:
75 super().alter_field(model, old_field, new_field, strict)
76 except DatabaseError as e:
77 description = str(e)
78 # If we're changing type to an unsupported type we need a
79 # SQLite-ish workaround
80 if "ORA-22858" in description or "ORA-22859" in description:
81 self._alter_field_type_workaround(model, old_field, new_field)
82 # If an identity column is changing to a non-numeric type, drop the
83 # identity first.
84 elif "ORA-30675" in description:
85 self._drop_identity(model._meta.db_table, old_field.column)
86 self.alter_field(model, old_field, new_field, strict)
87 # If a primary key column is changing to an identity column, drop
88 # the primary key first.
89 elif "ORA-30673" in description and old_field.primary_key:
90 self._delete_primary_key(model, strict=True)
91 self._alter_field_type_workaround(model, old_field, new_field)
92 # If a collation is changing on a primary key, drop the primary key
93 # first.
94 elif "ORA-43923" in description and old_field.primary_key:
95 self._delete_primary_key(model, strict=True)
96 self.alter_field(model, old_field, new_field, strict)
97 # Restore a primary key, if needed.
98 if new_field.primary_key:
99 self.execute(self._create_primary_key_sql(model, new_field))
100 else:
101 raise
102
103 def _alter_field_type_workaround(self, model, old_field, new_field):
104 """
105 Oracle refuses to change from some type to other type.
106 What we need to do instead is:
107 - Add a nullable version of the desired field with a temporary name. If
108 the new column is an auto field, then the temporary column can't be
109 nullable.
110 - Update the table to transfer values from old to new
111 - Drop old column
112 - Rename the new column and possibly drop the nullable property
113 """
114 # Make a new field that's like the new one but with a temporary
115 # column name.
116 new_temp_field = copy.deepcopy(new_field)
117 new_temp_field.null = new_field.get_internal_type() not in (
118 "AutoField",
119 "BigAutoField",
120 "SmallAutoField",
121 )
122 new_temp_field.column = self._generate_temp_name(new_field.column)
123 # Add it
124 self.add_field(model, new_temp_field)
125 # Explicit data type conversion
126 # https://docs.oracle.com/en/database/oracle/oracle-database/21/sqlrf
127 # /Data-Type-Comparison-Rules.html#GUID-D0C5A47E-6F93-4C2D-9E49-4F2B86B359DD
128 new_value = self.quote_name(old_field.column)
129 old_type = old_field.db_type(self.connection)
130 if re.match("^N?CLOB", old_type):
131 new_value = "TO_CHAR(%s)" % new_value
132 old_type = "VARCHAR2"
133 if re.match("^N?VARCHAR2", old_type):
134 new_internal_type = new_field.get_internal_type()
135 if new_internal_type == "DateField":
136 new_value = "TO_DATE(%s, 'YYYY-MM-DD')" % new_value
137 elif new_internal_type == "DateTimeField":
138 new_value = "TO_TIMESTAMP(%s, 'YYYY-MM-DD HH24:MI:SS.FF')" % new_value
139 elif new_internal_type == "TimeField":
140 # TimeField are stored as TIMESTAMP with a 1900-01-01 date part.
141 new_value = "CONCAT('1900-01-01 ', %s)" % new_value
142 new_value = "TO_TIMESTAMP(%s, 'YYYY-MM-DD HH24:MI:SS.FF')" % new_value
143 # Transfer values across
144 self.execute(
145 "UPDATE %s set %s=%s"
146 % (
147 self.quote_name(model._meta.db_table),
148 self.quote_name(new_temp_field.column),
149 new_value,
150 )
151 )
152 # Drop the old field
153 self.remove_field(model, old_field)
154 # Rename and possibly make the new field NOT NULL
155 super().alter_field(model, new_temp_field, new_field)
156 # Recreate foreign key (if necessary) because the old field is not
157 # passed to the alter_field() and data types of new_temp_field and
158 # new_field always match.
159 new_type = new_field.db_type(self.connection)
160 if (
161 (old_field.primary_key and new_field.primary_key)
162 or (old_field.unique and new_field.unique)
163 ) and old_type != new_type:
164 for _, rel in _related_non_m2m_objects(new_temp_field, new_field):
165 if rel.field.db_constraint:
166 self.execute(
167 self._create_fk_sql(rel.related_model, rel.field, "_fk")
168 )
169
170 def _alter_column_type_sql(
171 self, model, old_field, new_field, new_type, old_collation, new_collation
172 ):
173 auto_field_types = {"AutoField", "BigAutoField", "SmallAutoField"}
174 # Drop the identity if migrating away from AutoField.
175 if (
176 old_field.get_internal_type() in auto_field_types
177 and new_field.get_internal_type() not in auto_field_types
178 and self._is_identity_column(model._meta.db_table, new_field.column)
179 ):
180 self._drop_identity(model._meta.db_table, new_field.column)
181 return super()._alter_column_type_sql(
182 model, old_field, new_field, new_type, old_collation, new_collation
183 )
184
185 def normalize_name(self, name):
186 """
187 Get the properly shortened and uppercased identifier as returned by
188 quote_name() but without the quotes.
189 """
190 nn = self.quote_name(name)
191 if nn[0] == '"' and nn[-1] == '"':
192 nn = nn[1:-1]
193 return nn
194
195 def _generate_temp_name(self, for_name):
196 """Generate temporary names for workarounds that need temp columns."""
197 suffix = hex(hash(for_name)).upper()[1:]
198 return self.normalize_name(for_name + "_" + suffix)
199
200 def prepare_default(self, value):
201 return self.quote_value(value)
202
203 def _field_should_be_indexed(self, model, field):
204 create_index = super()._field_should_be_indexed(model, field)
205 db_type = field.db_type(self.connection)
206 if (
207 db_type is not None
208 and db_type.lower() in self.connection._limited_data_types
209 ):
210 return False
211 return create_index
212
213 def _is_identity_column(self, table_name, column_name):
214 if not column_name:
215 return False
216 with self.connection.cursor() as cursor:
217 cursor.execute(
218 """
219 SELECT
220 CASE WHEN identity_column = 'YES' THEN 1 ELSE 0 END
221 FROM user_tab_cols
222 WHERE table_name = %s AND
223 column_name = %s
224 """,
225 [self.normalize_name(table_name), self.normalize_name(column_name)],
226 )
227 row = cursor.fetchone()
228 return row[0] if row else False
229
230 def _drop_identity(self, table_name, column_name):
231 self.execute(
232 "ALTER TABLE %(table)s MODIFY %(column)s DROP IDENTITY"
233 % {
234 "table": self.quote_name(table_name),
235 "column": self.quote_name(column_name),
236 }
237 )
238
239 def _get_default_collation(self, table_name):
240 with self.connection.cursor() as cursor:
241 cursor.execute(
242 """
243 SELECT default_collation FROM user_tables WHERE table_name = %s
244 """,
245 [self.normalize_name(table_name)],
246 )
247 return cursor.fetchone()[0]
248
249 def _collate_sql(self, collation, old_collation=None, table_name=None):
250 if collation is None and old_collation is not None:
251 collation = self._get_default_collation(table_name)
252 return super()._collate_sql(collation, old_collation, table_name)