Debian Codegraph @

django_pgschemas/postgresql/introspection.py

raw
1 from typing import Any
2
3 from django.db.backends.base.introspection import FieldInfo, TableInfo
4 from django.utils.encoding import force_str
5
6 from django_pgschemas.schema import get_current_schema
7 from django_pgschemas.settings import get_base_backend_module, get_original_backend_module
8
9 from . import _constraints
10
11 try:
12 module = get_original_backend_module("introspection")
13 except AttributeError:
14 module = get_base_backend_module("introspection")
15
16
17 class DatabaseSchemaIntrospection(module.DatabaseIntrospection): # type: ignore[name-defined] # pragma: no cover
18 """
19 database schema introspection class
20 """
21
22 _get_indexes_query = """
23 SELECT attr.attname, idx.indkey, idx.indisunique, idx.indisprimary
24 FROM pg_catalog.pg_class c,
25 INNER JOIN pg_catalog.pg_index idx ON c.oid = idx.indrelid
26 INNER JOIN pg_catalog.pg_class c2 ON idx.indexrelid = c2.oid
27 INNER JOIN pg_catalog.pg_attribute attr ON attr.attrelid = c.oid and attr.attnum = idx.indkey[0]
28 INNER JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
29 WHERE c.relname = %s AND n.nspname = %s
30 """
31
32 def get_table_list(self, cursor: Any) -> list[TableInfo]:
33 """
34 Returns a list of table names in the current database and schema.
35 """
36
37 cursor.execute(
38 """
39 SELECT c.relname, c.relkind
40 FROM pg_catalog.pg_class c
41 LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
42 WHERE c.relkind IN ('r', 'v', '')
43 AND n.nspname = '%s'
44 AND pg_catalog.pg_table_is_visible(c.oid)"""
45 % get_current_schema().schema_name
46 )
47
48 return [
49 TableInfo(row[0], {"r": "t", "v": "v"}.get(row[1]))
50 for row in cursor.fetchall()
51 if row[0] not in self.ignored_tables
52 ]
53
54 def get_table_description(self, cursor: Any, table_name: str) -> list[FieldInfo]:
55 "Returns a description of the table, with the DB-API cursor.description interface."
56 # As cursor.description does not return reliably the nullable property,
57 # we have to query the information_schema (#7783)
58 cursor.execute(
59 """
60 SELECT column_name, is_nullable, column_default
61 FROM information_schema.columns
62 WHERE table_schema = %s and table_name = %s""",
63 [get_current_schema().schema_name, table_name],
64 )
65 field_map = {line[0]: line[1:] for line in cursor.fetchall()}
66 cursor.execute("SELECT * FROM %s LIMIT 1" % self.connection.ops.quote_name(table_name))
67 return [
68 FieldInfo(
69 *(
70 (force_str(line[0]),)
71 + line[1:6]
72 + (field_map[force_str(line[0])][0] == "YES", field_map[force_str(line[0])][1])
73 )
74 )
75 for line in cursor.description
76 ]
77
78 def get_indexes(self, cursor: Any, table_name: str) -> dict[str, dict[str, bool]]:
79 # This query retrieves each index on the given table, including the
80 # first associated field name
81 cursor.execute(self._get_indexes_query, [table_name, get_current_schema().schema_name])
82 indexes = {}
83 for row in cursor.fetchall():
84 # row[1] (idx.indkey) is stored in the DB as an array. It comes out as
85 # a string of space-separated integers. This designates the field
86 # indexes (1-based) of the fields that have indexes on the table.
87 # Here, we skip any indexes across multiple fields.
88 if " " in row[1]:
89 continue
90 if row[0] not in indexes:
91 indexes[row[0]] = {"primary_key": False, "unique": False}
92 # It's possible to have the unique and PK constraints in separate indexes.
93 if row[3]:
94 indexes[row[0]]["primary_key"] = True
95 if row[2]:
96 indexes[row[0]]["unique"] = True
97 return indexes
98
99 def get_relations(self, cursor: Any, table_name: str) -> dict[str, tuple[str, str]]:
100 """
101 Returns a dictionary of {field_name: (field_name_other_table, other_table)}
102 representing all relationships to the given table.
103 """
104 cursor.execute(
105 """
106 SELECT c2.relname, a1.attname, a2.attname
107 FROM pg_constraint con
108 LEFT JOIN pg_class c1 ON con.conrelid = c1.oid
109 LEFT JOIN pg_namespace n ON n.oid = c1.relnamespace
110 LEFT JOIN pg_class c2 ON con.confrelid = c2.oid
111 LEFT JOIN pg_attribute a1 ON c1.oid = a1.attrelid AND a1.attnum = con.conkey[1]
112 LEFT JOIN pg_attribute a2 ON c2.oid = a2.attrelid AND a2.attnum = con.confkey[1]
113 WHERE c1.relname = %s and n.nspname = %s
114 AND con.contype = 'f'""",
115 [table_name, get_current_schema().schema_name],
116 )
117 relations = {}
118 for row in cursor.fetchall():
119 relations[row[1]] = (row[2], row[0])
120 return relations
121
122 get_constraints = _constraints.get_constraints
123
124 def get_key_columns(self, cursor: Any, table_name: str) -> list[tuple[str, str, str]]:
125 key_columns: list[tuple[str, str, str]] = []
126 cursor.execute(
127 """
128 SELECT kcu.column_name, ccu.table_name AS referenced_table, ccu.column_name AS referenced_column
129 FROM information_schema.constraint_column_usage ccu
130 LEFT JOIN information_schema.key_column_usage kcu
131 ON ccu.constraint_catalog = kcu.constraint_catalog
132 AND ccu.constraint_schema = kcu.constraint_schema
133 AND ccu.constraint_name = kcu.constraint_name
134 LEFT JOIN information_schema.table_constraints tc
135 ON ccu.constraint_catalog = tc.constraint_catalog
136 AND ccu.constraint_schema = tc.constraint_schema
137 AND ccu.constraint_name = tc.constraint_name
138 WHERE kcu.table_name = %s AND tc.constraint_type = 'FOREIGN KEY' AND tc.table_schema = %s
139 """,
140 [table_name, get_current_schema().schema_name],
141 )
142 key_columns.extend(cursor.fetchall())
143 return key_columns