| 1 |
from collections import namedtuple |
| 2 |
|
| 3 |
from django.db import models |
| 4 |
from django.db.backends.base.introspection import BaseDatabaseIntrospection |
| 5 |
from django.db.backends.base.introspection import FieldInfo as BaseFieldInfo |
| 6 |
from django.db.backends.base.introspection import TableInfo as BaseTableInfo |
| 7 |
from django.db.backends.oracle.oracledb_any import oracledb |
| 8 |
|
| 9 |
FieldInfo = namedtuple( |
| 10 |
"FieldInfo", BaseFieldInfo._fields + ("is_autofield", "is_json", "comment") |
| 11 |
) |
| 12 |
TableInfo = namedtuple("TableInfo", BaseTableInfo._fields + ("comment",)) |
| 13 |
|
| 14 |
|
| 15 |
class DatabaseIntrospection(BaseDatabaseIntrospection): |
| 16 |
cache_bust_counter = 1 |
| 17 |
|
| 18 |
# Maps type objects to Django Field types. |
| 19 |
data_types_reverse = { |
| 20 |
oracledb.DB_TYPE_DATE: "DateField", |
| 21 |
oracledb.DB_TYPE_BINARY_DOUBLE: "FloatField", |
| 22 |
oracledb.DB_TYPE_BLOB: "BinaryField", |
| 23 |
oracledb.DB_TYPE_CHAR: "CharField", |
| 24 |
oracledb.DB_TYPE_CLOB: "TextField", |
| 25 |
oracledb.DB_TYPE_INTERVAL_DS: "DurationField", |
| 26 |
oracledb.DB_TYPE_NCHAR: "CharField", |
| 27 |
oracledb.DB_TYPE_NCLOB: "TextField", |
| 28 |
oracledb.DB_TYPE_NVARCHAR: "CharField", |
| 29 |
oracledb.DB_TYPE_NUMBER: "DecimalField", |
| 30 |
oracledb.DB_TYPE_TIMESTAMP: "DateTimeField", |
| 31 |
oracledb.DB_TYPE_VARCHAR: "CharField", |
| 32 |
} |
| 33 |
|
| 34 |
def get_field_type(self, data_type, description): |
| 35 |
if data_type == oracledb.NUMBER: |
| 36 |
precision, scale = description[4:6] |
| 37 |
if scale == 0: |
| 38 |
if precision > 11: |
| 39 |
return ( |
| 40 |
"BigAutoField" |
| 41 |
if description.is_autofield |
| 42 |
else "BigIntegerField" |
| 43 |
) |
| 44 |
elif 1 < precision < 6 and description.is_autofield: |
| 45 |
return "SmallAutoField" |
| 46 |
elif precision == 1: |
| 47 |
return "BooleanField" |
| 48 |
elif description.is_autofield: |
| 49 |
return "AutoField" |
| 50 |
else: |
| 51 |
return "IntegerField" |
| 52 |
elif scale == -127: |
| 53 |
return "FloatField" |
| 54 |
elif data_type == oracledb.NCLOB and description.is_json: |
| 55 |
return "JSONField" |
| 56 |
|
| 57 |
return super().get_field_type(data_type, description) |
| 58 |
|
| 59 |
def get_table_list(self, cursor): |
| 60 |
"""Return a list of table and view names in the current database.""" |
| 61 |
cursor.execute(""" |
| 62 |
SELECT |
| 63 |
user_tables.table_name, |
| 64 |
't', |
| 65 |
user_tab_comments.comments |
| 66 |
FROM user_tables |
| 67 |
LEFT OUTER JOIN |
| 68 |
user_tab_comments |
| 69 |
ON user_tab_comments.table_name = user_tables.table_name |
| 70 |
WHERE |
| 71 |
NOT EXISTS ( |
| 72 |
SELECT 1 |
| 73 |
FROM user_mviews |
| 74 |
WHERE user_mviews.mview_name = user_tables.table_name |
| 75 |
) |
| 76 |
UNION ALL |
| 77 |
SELECT view_name, 'v', NULL FROM user_views |
| 78 |
UNION ALL |
| 79 |
SELECT mview_name, 'v', NULL FROM user_mviews |
| 80 |
""") |
| 81 |
return [ |
| 82 |
TableInfo(self.identifier_converter(row[0]), row[1], row[2]) |
| 83 |
for row in cursor.fetchall() |
| 84 |
] |
| 85 |
|
| 86 |
def get_table_description(self, cursor, table_name): |
| 87 |
""" |
| 88 |
Return a description of the table with the DB-API cursor.description |
| 89 |
interface. |
| 90 |
""" |
| 91 |
# A default collation for the given table/view/materialized view. |
| 92 |
cursor.execute( |
| 93 |
""" |
| 94 |
SELECT user_tables.default_collation |
| 95 |
FROM user_tables |
| 96 |
WHERE |
| 97 |
user_tables.table_name = UPPER(%s) AND |
| 98 |
NOT EXISTS ( |
| 99 |
SELECT 1 |
| 100 |
FROM user_mviews |
| 101 |
WHERE user_mviews.mview_name = user_tables.table_name |
| 102 |
) |
| 103 |
UNION ALL |
| 104 |
SELECT user_views.default_collation |
| 105 |
FROM user_views |
| 106 |
WHERE user_views.view_name = UPPER(%s) |
| 107 |
UNION ALL |
| 108 |
SELECT user_mviews.default_collation |
| 109 |
FROM user_mviews |
| 110 |
WHERE user_mviews.mview_name = UPPER(%s) |
| 111 |
""", |
| 112 |
[table_name, table_name, table_name], |
| 113 |
) |
| 114 |
row = cursor.fetchone() |
| 115 |
default_table_collation = row[0] if row else "" |
| 116 |
# user_tab_columns gives data default for columns |
| 117 |
cursor.execute( |
| 118 |
""" |
| 119 |
SELECT |
| 120 |
user_tab_cols.column_name, |
| 121 |
user_tab_cols.data_default, |
| 122 |
CASE |
| 123 |
WHEN user_tab_cols.collation = %s |
| 124 |
THEN NULL |
| 125 |
ELSE user_tab_cols.collation |
| 126 |
END collation, |
| 127 |
CASE |
| 128 |
WHEN user_tab_cols.char_used IS NULL |
| 129 |
THEN user_tab_cols.data_length |
| 130 |
ELSE user_tab_cols.char_length |
| 131 |
END as display_size, |
| 132 |
CASE |
| 133 |
WHEN user_tab_cols.identity_column = 'YES' THEN 1 |
| 134 |
ELSE 0 |
| 135 |
END as is_autofield, |
| 136 |
CASE |
| 137 |
WHEN EXISTS ( |
| 138 |
SELECT 1 |
| 139 |
FROM user_json_columns |
| 140 |
WHERE |
| 141 |
user_json_columns.table_name = user_tab_cols.table_name AND |
| 142 |
user_json_columns.column_name = user_tab_cols.column_name |
| 143 |
) |
| 144 |
THEN 1 |
| 145 |
ELSE 0 |
| 146 |
END as is_json, |
| 147 |
user_col_comments.comments as col_comment |
| 148 |
FROM user_tab_cols |
| 149 |
LEFT OUTER JOIN |
| 150 |
user_col_comments ON |
| 151 |
user_col_comments.column_name = user_tab_cols.column_name AND |
| 152 |
user_col_comments.table_name = user_tab_cols.table_name |
| 153 |
WHERE user_tab_cols.table_name = UPPER(%s) |
| 154 |
""", |
| 155 |
[default_table_collation, table_name], |
| 156 |
) |
| 157 |
field_map = { |
| 158 |
column: ( |
| 159 |
display_size, |
| 160 |
default.rstrip() if default and default != "NULL" else None, |
| 161 |
collation, |
| 162 |
is_autofield, |
| 163 |
is_json, |
| 164 |
comment, |
| 165 |
) |
| 166 |
for ( |
| 167 |
column, |
| 168 |
default, |
| 169 |
collation, |
| 170 |
display_size, |
| 171 |
is_autofield, |
| 172 |
is_json, |
| 173 |
comment, |
| 174 |
) in cursor.fetchall() |
| 175 |
} |
| 176 |
self.cache_bust_counter += 1 |
| 177 |
cursor.execute( |
| 178 |
"SELECT * FROM {} WHERE ROWNUM < 2 AND {} > 0".format( |
| 179 |
self.connection.ops.quote_name(table_name), self.cache_bust_counter |
| 180 |
) |
| 181 |
) |
| 182 |
description = [] |
| 183 |
for desc in cursor.description: |
| 184 |
name = desc[0] |
| 185 |
( |
| 186 |
display_size, |
| 187 |
default, |
| 188 |
collation, |
| 189 |
is_autofield, |
| 190 |
is_json, |
| 191 |
comment, |
| 192 |
) = field_map[name] |
| 193 |
name %= {} # oracledb, for some reason, doubles percent signs. |
| 194 |
description.append( |
| 195 |
FieldInfo( |
| 196 |
self.identifier_converter(name), |
| 197 |
desc[1], |
| 198 |
display_size, |
| 199 |
desc[3], |
| 200 |
desc[4] or 0, |
| 201 |
desc[5] or 0, |
| 202 |
*desc[6:], |
| 203 |
default, |
| 204 |
collation, |
| 205 |
is_autofield, |
| 206 |
is_json, |
| 207 |
comment, |
| 208 |
) |
| 209 |
) |
| 210 |
return description |
| 211 |
|
| 212 |
def identifier_converter(self, name): |
| 213 |
"""Identifier comparison is case insensitive under Oracle.""" |
| 214 |
return name.lower() |
| 215 |
|
| 216 |
def get_sequences(self, cursor, table_name, table_fields=()): |
| 217 |
cursor.execute( |
| 218 |
""" |
| 219 |
SELECT |
| 220 |
user_tab_identity_cols.sequence_name, |
| 221 |
user_tab_identity_cols.column_name |
| 222 |
FROM |
| 223 |
user_tab_identity_cols, |
| 224 |
user_constraints, |
| 225 |
user_cons_columns cols |
| 226 |
WHERE |
| 227 |
user_constraints.constraint_name = cols.constraint_name |
| 228 |
AND user_constraints.table_name = user_tab_identity_cols.table_name |
| 229 |
AND cols.column_name = user_tab_identity_cols.column_name |
| 230 |
AND user_constraints.constraint_type = 'P' |
| 231 |
AND user_tab_identity_cols.table_name = UPPER(%s) |
| 232 |
""", |
| 233 |
[table_name], |
| 234 |
) |
| 235 |
# Oracle allows only one identity column per table. |
| 236 |
row = cursor.fetchone() |
| 237 |
if row: |
| 238 |
return [ |
| 239 |
{ |
| 240 |
"name": self.identifier_converter(row[0]), |
| 241 |
"table": self.identifier_converter(table_name), |
| 242 |
"column": self.identifier_converter(row[1]), |
| 243 |
} |
| 244 |
] |
| 245 |
# To keep backward compatibility for AutoFields that aren't Oracle |
| 246 |
# identity columns. |
| 247 |
for f in table_fields: |
| 248 |
if isinstance(f, models.AutoField): |
| 249 |
return [{"table": table_name, "column": f.column}] |
| 250 |
return [] |
| 251 |
|
| 252 |
def get_relations(self, cursor, table_name): |
| 253 |
""" |
| 254 |
Return a dictionary of {field_name: (field_name_other_table, other_table)} |
| 255 |
representing all foreign keys in the given table. |
| 256 |
""" |
| 257 |
table_name = table_name.upper() |
| 258 |
cursor.execute( |
| 259 |
""" |
| 260 |
SELECT ca.column_name, cb.table_name, cb.column_name |
| 261 |
FROM user_constraints, USER_CONS_COLUMNS ca, USER_CONS_COLUMNS cb |
| 262 |
WHERE user_constraints.table_name = %s AND |
| 263 |
user_constraints.constraint_name = ca.constraint_name AND |
| 264 |
user_constraints.r_constraint_name = cb.constraint_name AND |
| 265 |
ca.position = cb.position""", |
| 266 |
[table_name], |
| 267 |
) |
| 268 |
|
| 269 |
return { |
| 270 |
self.identifier_converter(field_name): ( |
| 271 |
self.identifier_converter(rel_field_name), |
| 272 |
self.identifier_converter(rel_table_name), |
| 273 |
) |
| 274 |
for field_name, rel_table_name, rel_field_name in cursor.fetchall() |
| 275 |
} |
| 276 |
|
| 277 |
def get_primary_key_columns(self, cursor, table_name): |
| 278 |
cursor.execute( |
| 279 |
""" |
| 280 |
SELECT |
| 281 |
cols.column_name |
| 282 |
FROM |
| 283 |
user_constraints, |
| 284 |
user_cons_columns cols |
| 285 |
WHERE |
| 286 |
user_constraints.constraint_name = cols.constraint_name AND |
| 287 |
user_constraints.constraint_type = 'P' AND |
| 288 |
user_constraints.table_name = UPPER(%s) |
| 289 |
ORDER BY |
| 290 |
cols.position |
| 291 |
""", |
| 292 |
[table_name], |
| 293 |
) |
| 294 |
return [self.identifier_converter(row[0]) for row in cursor.fetchall()] |
| 295 |
|
| 296 |
def get_constraints(self, cursor, table_name): |
| 297 |
""" |
| 298 |
Retrieve any constraints or keys (unique, pk, fk, check, index) across |
| 299 |
one or more columns. |
| 300 |
""" |
| 301 |
constraints = {} |
| 302 |
# Loop over the constraints, getting PKs, uniques, and checks |
| 303 |
cursor.execute( |
| 304 |
""" |
| 305 |
SELECT |
| 306 |
user_constraints.constraint_name, |
| 307 |
LISTAGG(LOWER(cols.column_name), ',') |
| 308 |
WITHIN GROUP (ORDER BY cols.position), |
| 309 |
CASE user_constraints.constraint_type |
| 310 |
WHEN 'P' THEN 1 |
| 311 |
ELSE 0 |
| 312 |
END AS is_primary_key, |
| 313 |
CASE |
| 314 |
WHEN user_constraints.constraint_type IN ('P', 'U') THEN 1 |
| 315 |
ELSE 0 |
| 316 |
END AS is_unique, |
| 317 |
CASE user_constraints.constraint_type |
| 318 |
WHEN 'C' THEN 1 |
| 319 |
ELSE 0 |
| 320 |
END AS is_check_constraint |
| 321 |
FROM |
| 322 |
user_constraints |
| 323 |
LEFT OUTER JOIN |
| 324 |
user_cons_columns cols |
| 325 |
ON user_constraints.constraint_name = cols.constraint_name |
| 326 |
WHERE |
| 327 |
user_constraints.constraint_type = ANY('P', 'U', 'C') |
| 328 |
AND user_constraints.table_name = UPPER(%s) |
| 329 |
GROUP BY user_constraints.constraint_name, user_constraints.constraint_type |
| 330 |
""", |
| 331 |
[table_name], |
| 332 |
) |
| 333 |
for constraint, columns, pk, unique, check in cursor.fetchall(): |
| 334 |
constraint = self.identifier_converter(constraint) |
| 335 |
constraints[constraint] = { |
| 336 |
"columns": columns.split(","), |
| 337 |
"primary_key": pk, |
| 338 |
"unique": unique, |
| 339 |
"foreign_key": None, |
| 340 |
"check": check, |
| 341 |
"index": unique, # All uniques come with an index |
| 342 |
} |
| 343 |
# Foreign key constraints |
| 344 |
cursor.execute( |
| 345 |
""" |
| 346 |
SELECT |
| 347 |
cons.constraint_name, |
| 348 |
LISTAGG(LOWER(cols.column_name), ',') |
| 349 |
WITHIN GROUP (ORDER BY cols.position), |
| 350 |
LOWER(rcols.table_name), |
| 351 |
LOWER(rcols.column_name) |
| 352 |
FROM |
| 353 |
user_constraints cons |
| 354 |
INNER JOIN |
| 355 |
user_cons_columns rcols |
| 356 |
ON rcols.constraint_name = cons.r_constraint_name AND rcols.position = 1 |
| 357 |
LEFT OUTER JOIN |
| 358 |
user_cons_columns cols |
| 359 |
ON cons.constraint_name = cols.constraint_name |
| 360 |
WHERE |
| 361 |
cons.constraint_type = 'R' AND |
| 362 |
cons.table_name = UPPER(%s) |
| 363 |
GROUP BY cons.constraint_name, rcols.table_name, rcols.column_name |
| 364 |
""", |
| 365 |
[table_name], |
| 366 |
) |
| 367 |
for constraint, columns, other_table, other_column in cursor.fetchall(): |
| 368 |
constraint = self.identifier_converter(constraint) |
| 369 |
constraints[constraint] = { |
| 370 |
"primary_key": False, |
| 371 |
"unique": False, |
| 372 |
"foreign_key": (other_table, other_column), |
| 373 |
"check": False, |
| 374 |
"index": False, |
| 375 |
"columns": columns.split(","), |
| 376 |
} |
| 377 |
# Now get indexes |
| 378 |
cursor.execute( |
| 379 |
""" |
| 380 |
SELECT |
| 381 |
ind.index_name, |
| 382 |
LOWER(ind.index_type), |
| 383 |
LOWER(ind.uniqueness), |
| 384 |
LISTAGG(LOWER(cols.column_name), ',') |
| 385 |
WITHIN GROUP (ORDER BY cols.column_position), |
| 386 |
LISTAGG(cols.descend, ',') WITHIN GROUP (ORDER BY cols.column_position) |
| 387 |
FROM |
| 388 |
user_ind_columns cols, user_indexes ind |
| 389 |
WHERE |
| 390 |
cols.table_name = UPPER(%s) AND |
| 391 |
NOT EXISTS ( |
| 392 |
SELECT 1 |
| 393 |
FROM user_constraints cons |
| 394 |
WHERE ind.index_name = cons.index_name |
| 395 |
) AND cols.index_name = ind.index_name |
| 396 |
GROUP BY ind.index_name, ind.index_type, ind.uniqueness |
| 397 |
""", |
| 398 |
[table_name], |
| 399 |
) |
| 400 |
for constraint, type_, unique, columns, orders in cursor.fetchall(): |
| 401 |
constraint = self.identifier_converter(constraint) |
| 402 |
constraints[constraint] = { |
| 403 |
"primary_key": False, |
| 404 |
"unique": unique == "unique", |
| 405 |
"foreign_key": None, |
| 406 |
"check": False, |
| 407 |
"index": True, |
| 408 |
"type": "idx" if type_ == "normal" else type_, |
| 409 |
"columns": columns.split(","), |
| 410 |
"orders": orders.split(","), |
| 411 |
} |
| 412 |
return constraints |