Debian Codegraph @

django/db/backends/oracle/introspection.py

raw
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