Source code for datakiln.metadata.foreign_key

"""
Foreign key metadata.
"""
from textwrap import dedent

from dataclasses import dataclass


[docs] @dataclass class ForeignKeyReference: """Metadata for table referenced in foreign key. :param schema: Schema of foreign table. :type schema: str :param table: Table referenced by foreign key. :type table: str :param columns: Columns referenced by foreign key. :type columns: str """ schema: str table: str columns: list
[docs] class ForeignKeyMeta(): """Metadata for foreign key.""" def __init__(self, table, columns, reference, dtypes=None): """ :param table: Table referencing the foreign key. :type table: TableMeta :param columns: Columns referencing the foreign key. :type columns: str :param reference: Metadata for referenced table and columns. :type reference: dict :return: Foreign key metadata. :rtype: ForeignKeyMeta """ self.table = table self.columns = columns self.reference = ForeignKeyReference(**reference) self.dtypes = dtypes # Set default dtypes if dtypes is None: self.dtypes = dict.fromkeys(["default"] * len(columns), "TEXT")
[docs] def create(self, conn): """Create statement. :param conn: SQLAlchemy connection or session. :type conn: sqlalchemy.Engine :return: None """ stmt = dedent(""" ALTER TABLE {table.schema}.{table.name} ADD FOREIGN KEY ({t_columns}) REFERENCES {reference.schema}.{reference.table} ({r_columns}) """).format( table=self.table, t_columns=", ".join(self.columns), reference=self.reference, r_columns=", ".join(self.reference.columns) ) conn.execute(stmt)
def _conditions(self, alias=None, dialect="default"): """Generate comparisons to match each foreign key column. :return: Comparisons for each foreign key column. :rtype: list[str] :param dialect: Dialect of the database. :type dialect: str """ conditions = [] # Zip column pair together along with data type column_pair = zip( self.columns, self.reference.columns, self.dtypes ) # Generate one comparison statement for each foreign key column alias = alias or self.reference.table for column, reference, dtype in column_pair: conditions.append( f" AND {self.table.name}.{column}::{dtype[dialect]} = " f"{alias}.{reference}" ) # Remove AND clause from first comparison conditions[0] = conditions[0][5:] return conditions
[docs] def join(self, alias=None, dialect="default"): """Generate JOIN statement for the foreign key. :return: JOIN statement. :rtype: list[str] :param dialect: Dialect of the database. :type dialect: str """ join = [f"LEFT JOIN {self.reference.schema}.{self.reference.table}"] join.extend(self._conditions(alias=alias, dialect=dialect)) if alias: join[0] += f" {alias}" # Add ON clause to first comparison join[1] = " ON" + join[1] return join
[docs] def distinct_reference(self, alias=None, dialect="default"): """Filter foreign key values that do not exist in the reference tables, allowing null values. :return: Comparisons for each foreign key column. :rtype: list[str] :param dialect: Dialect of the database. :type dialect: str """ conditions = ["OR (", " 1 = 1"] # Zip column pair together along with data type column_pair = zip( self.columns, self.reference.columns, self.dtypes ) # Generate one comparison statement for each foreign key column alias = alias or self.reference.table for column, reference, dtype in column_pair: conditions.append( f" AND {alias}.{reference} IS DISTINCT FROM " f"{self.table.name}.{column}::{dtype[dialect]}" ) return conditions + [")"]