Coverage for datakiln/metadata/foreign_key.py: 98%
40 statements
« prev ^ index » next coverage.py v7.17.3, created at 2026-11-16 23:23 +0000
« prev ^ index » next coverage.py v7.17.3, created at 2026-11-16 23:23 +0000
1"""
2Foreign key metadata.
3"""
4from textwrap import dedent
6from dataclasses import dataclass
9@dataclass
10class ForeignKeyReference:
11 """Metadata for table referenced in foreign key.
13 :param schema: Schema of foreign table.
14 :type schema: str
16 :param table: Table referenced by foreign key.
17 :type table: str
19 :param columns: Columns referenced by foreign key.
20 :type columns: str
21 """
22 schema: str
23 table: str
24 columns: list
27class ForeignKeyMeta():
28 """Metadata for foreign key."""
30 def __init__(self, table, columns, reference, dtypes=None):
31 """
32 :param table: Table referencing the foreign key.
33 :type table: TableMeta
35 :param columns: Columns referencing the foreign key.
36 :type columns: str
38 :param reference: Metadata for referenced table and columns.
39 :type reference: dict
41 :return: Foreign key metadata.
42 :rtype: ForeignKeyMeta
43 """
44 self.table = table
45 self.columns = columns
46 self.reference = ForeignKeyReference(**reference)
47 self.dtypes = dtypes
49 # Set default dtypes
50 if dtypes is None:
51 self.dtypes = dict.fromkeys(["default"] * len(columns), "TEXT")
53 def create(self, conn):
54 """Create statement.
56 :param conn: SQLAlchemy connection or session.
57 :type conn: sqlalchemy.Engine
59 :return: None
60 """
61 stmt = dedent("""
62 ALTER TABLE {table.schema}.{table.name}
63 ADD FOREIGN KEY ({t_columns})
64 REFERENCES {reference.schema}.{reference.table} ({r_columns})
65 """).format(
66 table=self.table,
67 t_columns=", ".join(self.columns),
68 reference=self.reference,
69 r_columns=", ".join(self.reference.columns)
70 )
71 conn.execute(stmt)
73 def _conditions(self, alias=None, dialect="default"):
74 """Generate comparisons to match each foreign key column.
76 :return: Comparisons for each foreign key column.
77 :rtype: list[str]
79 :param dialect: Dialect of the database.
80 :type dialect: str
81 """
82 conditions = []
84 # Zip column pair together along with data type
85 column_pair = zip(
86 self.columns,
87 self.reference.columns,
88 self.dtypes
89 )
91 # Generate one comparison statement for each foreign key column
92 alias = alias or self.reference.table
93 for column, reference, dtype in column_pair:
94 conditions.append(
95 f" AND {self.table.name}.{column}::{dtype[dialect]} = "
96 f"{alias}.{reference}"
97 )
99 # Remove AND clause from first comparison
100 conditions[0] = conditions[0][5:]
102 return conditions
104 def join(self, alias=None, dialect="default"):
105 """Generate JOIN statement for the foreign key.
107 :return: JOIN statement.
108 :rtype: list[str]
110 :param dialect: Dialect of the database.
111 :type dialect: str
112 """
113 join = [f"LEFT JOIN {self.reference.schema}.{self.reference.table}"]
114 join.extend(self._conditions(alias=alias, dialect=dialect))
116 if alias:
117 join[0] += f" {alias}"
119 # Add ON clause to first comparison
120 join[1] = " ON" + join[1]
122 return join
124 def distinct_reference(self, alias=None, dialect="default"):
125 """Filter foreign key values that do not exist in the reference tables,
126 allowing null values.
128 :return: Comparisons for each foreign key column.
129 :rtype: list[str]
131 :param dialect: Dialect of the database.
132 :type dialect: str
133 """
134 conditions = ["OR (", " 1 = 1"]
136 # Zip column pair together along with data type
137 column_pair = zip(
138 self.columns,
139 self.reference.columns,
140 self.dtypes
141 )
143 # Generate one comparison statement for each foreign key column
144 alias = alias or self.reference.table
145 for column, reference, dtype in column_pair:
146 conditions.append(
147 f" AND {alias}.{reference} IS DISTINCT FROM "
148 f"{self.table.name}.{column}::{dtype[dialect]}"
149 )
151 return conditions + [")"]