Untitled

# Fonction pour récupérer les contraintes des tables (Primary Key et Foreign Key)
def get_table_constraints(client, dataset_id):
    query = f"""
    SELECT
        table_name,
        constraint_name,
        constraint_type
    FROM
        `{dataset_id}.INFORMATION_SCHEMA.TABLE_CONSTRAINTS`
    """
    query_job = client.query(query)
    return {(row["table_name"], row["constraint_name"]): row["constraint_type"] for row in query_job}

# Fonction pour récupérer les colonnes impliquées dans des contraintes (PK et FK)
def get_key_column_usage(client, dataset_id):
    query = f"""
    SELECT
        table_name,
        column_name,
        constraint_name
    FROM
        `{dataset_id}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE`
    """
    query_job = client.query(query)
    return [(row["table_name"], row["column_name"], row["constraint_name"]) for row in query_job]

# Fonction pour identifier les relations entre colonnes (PK et FK)
def get_column_relations(client, dataset_id):
    query = f"""
    WITH all_columns AS (
      SELECT
        table_name,
        column_name,
        data_type
      FROM
        `{dataset_id}.INFORMATION_SCHEMA.COLUMNS`
    ),
    foreign_keys AS (
      SELECT
        tc.table_name AS source_table,
        kcu.column_name AS source_column,
        ccu.table_name AS target_table,
        ccu.column_name AS target_column
      FROM
        `{dataset_id}.INFORMATION_SCHEMA.TABLE_CONSTRAINTS` AS tc
      JOIN
        `{dataset_id}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE` AS kcu
        ON tc.constraint_name = kcu.constraint_name
      JOIN
        `{dataset_id}.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE` AS ccu
        ON ccu.constraint_name = tc.constraint_name
      WHERE
        tc.constraint_type = 'FOREIGN KEY'
    )
    SELECT
      col.table_name,
      col.column_name,
      CASE
        WHEN fk.source_column IS NOT NULL THEN 'YES'
        ELSE 'NO'
      END AS has_relation,
      fk.target_table AS related_table,
      fk.target_column AS related_column
    FROM
      all_columns col
    LEFT JOIN
      foreign_keys fk
    ON
      col.table_name = fk.source_table
      AND col.column_name = fk.source_column

    UNION ALL

    SELECT
      fk.target_table AS table_name,
      fk.target_column AS column_name,
      'YES' AS has_relation,
      fk.source_table AS related_table,
      fk.source_column AS related_column
    FROM (
      SELECT
        tc.table_name AS source_table,
        kcu.column_name AS source_column,
        ccu.table_name AS target_table,
        ccu.column_name AS target_column
      FROM
        `{dataset_id}.INFORMATION_SCHEMA.TABLE_CONSTRAINTS` AS tc
      JOIN
        `{dataset_id}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE` AS kcu
        ON tc.constraint_name = kcu.constraint_name
      JOIN
        `{dataset_id}.INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE` AS ccu
        ON ccu.constraint_name = tc.constraint_name
      WHERE
        tc.constraint_type = 'FOREIGN KEY'
    ) AS fk
    ORDER BY table_name, column_name;
    """

    query_job = client.query(query)
    return [(row["table_name"], row["column_name"], row["has_relation"], row["related_table"], row["related_column"]) for row in query_job]