Debian Codegraph @

django_pgschemas/postgresql/_constraints.py

raw
1 from typing import Any
2
3 from django.db.models.indexes import Index
4
5 from django_pgschemas.schema import get_current_schema
6
7
8 def get_constraints(self: Any, cursor: Any, table_name: str) -> dict[str, dict[str, Any]]:
9 """
10 Retrieve any constraints or keys (unique, pk, fk, check, index) across
11 one or more columns. Also retrieve the definition of expression-based
12 indexes.
13 """
14 constraints = {}
15 # Loop over the key table, collecting things as constraints. The column
16 # array must return column names in the same order in which they were
17 # created.
18 # The subquery containing generate_series can be replaced with
19 # "WITH ORDINALITY" when support for Postgres 9.3 is dropped.
20 cursor.execute(
21 """
22 SELECT
23 c.conname,
24 array(
25 SELECT attname
26 FROM (
27 SELECT unnest(c.conkey) AS colid,
28 generate_series(1, array_length(c.conkey, 1)) AS arridx
29 ) AS cols
30 JOIN pg_attribute AS ca ON cols.colid = ca.attnum
31 WHERE ca.attrelid = c.conrelid
32 ORDER BY cols.arridx
33 ),
34 c.contype,
35 (SELECT fkc.relname || '.' || fka.attname
36 FROM pg_attribute AS fka
37 JOIN pg_class AS fkc ON fka.attrelid = fkc.oid
38 WHERE fka.attrelid = c.confrelid AND fka.attnum = c.confkey[1]),
39 cl.reloptions
40 FROM pg_constraint AS c
41 JOIN pg_class AS cl ON c.conrelid = cl.oid
42 JOIN pg_namespace AS ns ON cl.relnamespace = ns.oid
43 WHERE ns.nspname = %s AND cl.relname = %s
44 """,
45 [get_current_schema().schema_name, table_name],
46 )
47 for constraint, columns, kind, used_cols, options in cursor.fetchall():
48 constraints[constraint] = {
49 "columns": columns,
50 "primary_key": kind == "p",
51 "unique": kind in ["p", "u"],
52 "foreign_key": tuple(used_cols.split(".", 1)) if kind == "f" else None,
53 "check": kind == "c",
54 "index": False,
55 "definition": None,
56 "options": options,
57 }
58 # Now get indexes
59 # The row_number() function for ordering the index fields can be
60 # replaced by WITH ORDINALITY in the unnest() functions when support
61 # for Postgres 9.3 is dropped.
62 cursor.execute(
63 """
64 SELECT
65 indexname, array_agg(attname ORDER BY rnum), indisunique, indisprimary,
66 array_agg(ordering ORDER BY rnum), amname, exprdef, s2.attoptions
67 FROM (
68 SELECT
69 row_number() OVER () as rnum, c2.relname as indexname,
70 idx.*, attr.attname, am.amname,
71 CASE
72 WHEN idx.indexprs IS NOT NULL THEN
73 pg_get_indexdef(idx.indexrelid)
74 END AS exprdef,
75 CASE am.amname
76 WHEN 'btree' THEN
77 CASE (option & 1)
78 WHEN 1 THEN 'DESC' ELSE 'ASC'
79 END
80 END as ordering,
81 c2.reloptions as attoptions
82 FROM (
83 SELECT
84 *, unnest(i.indkey) as key, unnest(i.indoption) as option
85 FROM pg_index i
86 ) idx
87 LEFT JOIN pg_class c ON idx.indrelid = c.oid
88 LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
89 LEFT JOIN pg_class c2 ON idx.indexrelid = c2.oid
90 LEFT JOIN pg_am am ON c2.relam = am.oid
91 LEFT JOIN pg_attribute attr ON attr.attrelid = c.oid AND attr.attnum = idx.key
92 WHERE c.relname = %s and n.nspname = %s
93 ) s2
94 GROUP BY indexname, indisunique, indisprimary, amname, exprdef, attoptions;
95 """,
96 [table_name, get_current_schema().schema_name],
97 )
98 for index, columns, unique, primary, orders, type_, definition, options in cursor.fetchall():
99 if index not in constraints:
100 constraints[index] = {
101 "columns": columns if columns != [None] else [],
102 "orders": orders if orders != [None] else [],
103 "primary_key": primary,
104 "unique": unique,
105 "foreign_key": None,
106 "check": False,
107 "index": True,
108 "type": Index.suffix if type_ == "btree" else type_,
109 "definition": definition,
110 "options": options,
111 }
112 return constraints