| 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 |