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

1""" 

2Foreign key metadata. 

3""" 

4from textwrap import dedent 

5 

6from dataclasses import dataclass 

7 

8 

9@dataclass 

10class ForeignKeyReference: 

11 """Metadata for table referenced in foreign key. 

12 

13 :param schema: Schema of foreign table. 

14 :type schema: str 

15 

16 :param table: Table referenced by foreign key. 

17 :type table: str 

18 

19 :param columns: Columns referenced by foreign key. 

20 :type columns: str 

21 """ 

22 schema: str 

23 table: str 

24 columns: list 

25 

26 

27class ForeignKeyMeta(): 

28 """Metadata for foreign key.""" 

29 

30 def __init__(self, table, columns, reference, dtypes=None): 

31 """ 

32 :param table: Table referencing the foreign key. 

33 :type table: TableMeta 

34 

35 :param columns: Columns referencing the foreign key. 

36 :type columns: str 

37 

38 :param reference: Metadata for referenced table and columns. 

39 :type reference: dict 

40 

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 

48 

49 # Set default dtypes 

50 if dtypes is None: 

51 self.dtypes = dict.fromkeys(["default"] * len(columns), "TEXT") 

52 

53 def create(self, conn): 

54 """Create statement. 

55 

56 :param conn: SQLAlchemy connection or session. 

57 :type conn: sqlalchemy.Engine 

58 

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) 

72 

73 def _conditions(self, alias=None, dialect="default"): 

74 """Generate comparisons to match each foreign key column. 

75 

76 :return: Comparisons for each foreign key column. 

77 :rtype: list[str] 

78 

79 :param dialect: Dialect of the database. 

80 :type dialect: str 

81 """ 

82 conditions = [] 

83 

84 # Zip column pair together along with data type 

85 column_pair = zip( 

86 self.columns, 

87 self.reference.columns, 

88 self.dtypes 

89 ) 

90 

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 ) 

98 

99 # Remove AND clause from first comparison 

100 conditions[0] = conditions[0][5:] 

101 

102 return conditions 

103 

104 def join(self, alias=None, dialect="default"): 

105 """Generate JOIN statement for the foreign key. 

106 

107 :return: JOIN statement. 

108 :rtype: list[str] 

109 

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

115 

116 if alias: 

117 join[0] += f" {alias}" 

118 

119 # Add ON clause to first comparison 

120 join[1] = " ON" + join[1] 

121 

122 return join 

123 

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. 

127 

128 :return: Comparisons for each foreign key column. 

129 :rtype: list[str] 

130 

131 :param dialect: Dialect of the database. 

132 :type dialect: str 

133 """ 

134 conditions = ["OR (", " 1 = 1"] 

135 

136 # Zip column pair together along with data type 

137 column_pair = zip( 

138 self.columns, 

139 self.reference.columns, 

140 self.dtypes 

141 ) 

142 

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 ) 

150 

151 return conditions + [")"]