Extraire de manière programmée les relations entre les tables dans un SGBDR sans clés étrangères ?

Question

Je suis en train de faire de l'ingénierie inversée des relations entre un nombre moyen de tables (50+) dans une base de données Oracle où il n'y a pas de clés étrangères définies entre les tables. Je peux compter (dans une certaine mesure) sur le fait de pouvoir faire correspondre les noms de colonnes à travers les tables. Par exemple, le nom de colonne "SomeDescriptiveName" est probablement le même pour l'ensemble des tables.

Ce que j'aimerais pouvoir faire, c'est trouver un moyen plus efficace d'extraire un ensemble de relations basé sur ces noms de colonnes correspondants que de passer manuellement en revue les tables une par une. Je pourrais faire quelque chose avec les méthodes Java DatabaseMetaData mais il semble que ce soit l'une de ces tâches que quelqu'un a probablement dû scripter auparavant. Peut-être extraire les noms des colonnes avec Perl ou un autre langage de script, utiliser les noms de colonnes comme clé de hachage et ajouter des tables à un tableau pointé par la clé de hachage ?

Est-ce que quelqu'un a des astuces ou des suggestions qui pourraient rendre cela plus simple ou fournir un bon point de départ ? C'est un besoin laborieux, si des clés étrangères avaient déjà été définies, comprendre les relations aurait été beaucoup plus facile.

Merci.

Answer 1

Vous avez à peu près écrit la réponse dans votre question.

my %column_tables;
foreach my $table (@tables) {
    foreach my $column ($table->columns) {
        push @{$column_tables[$column]}, $table;
    }
}
print "Relations de clés étrangères probables:\n";
foreach my $column (keys %column_tables) {
    my @tables = @{$column_tables[$column]};
    next
        if @tables < 2;
    print $column, ': ';
    foreach my $table (@tables) {
        print $table->name, ' ';
    }
    print "\n";
}

Answer 2

Vous pouvez utiliser une combinaison de trois (ou quatre) approches, en fonction du degré d'obfuscation du schéma :

• méthodes dynamiques
  • observation:
    • activer la traçabilité dans le SGBDR (ou la couche ODBC), puis
    • effectuer diverses activités dans l'application (idéalement, enregistrer la création d'enregistrements), puis
    • identifier quelles tables ont été modifiées dans une séquence serrée, et avec quelles paires de valeurs de colonnes
    • les valeurs apparaissant dans plus d'une colonne pendant l'intervalle de séquence peuvent indiquer une relation de clé étrangère
• méthodes statiques (analyser simplement les données existantes, pas besoin d'avoir une application en cours d'exécution)
  • nomenclature : essayer de déduire les relations à partir des noms de colonnes
  • statistique : regarder les valeurs minimales/maximales (et éventuellement la moyenne) des valeurs uniques dans toutes les colonnes numériques, et essayer de faire correspondre
  • ingénierie inverse du code : votre dernier recours (sauf en cas de scripts) - pas pour les âmes sensibles :)

Answer 3

Ma stratégie consisterait à utiliser le catalogue système Oracle pour trouver des colonnes qui ont le même nom de colonne et le même type de données mais qui sont différentes en termes de nom de table. Je voudrais aussi savoir quelle colonne fait partie d'une clé primaire ou unique d'une table.

Voici une requête qui pourrait être proche de ce que vous recherchez, mais je n'ai pas d'instance Oracle sous la main pour la tester :

SELECT col1.table_name || '.' || col1.column_name || ' -> ' 
    || col2.table_name || '.' || col2.column_name
FROM all_tab_columns col1 
  JOIN all_tab_columns col2
    ON (col1.column_name = col2.column_name 
    AND col1.data_type = col2.data_type)
  JOIN all_cons_columns cc
    ON (col2.table_name = cc.table_name 
    AND col2.column_name = cc.column_name)
  JOIN all_constraints con
    ON (cc.constraint_name = con.constraint_name 
    AND cc.table_name = con.table_name 
    AND con.constraint_type IN ('P', 'U'))
WHERE col1.table_name != col2.table_name;

Évidemment, cette requête ne couvrira pas les cas où des colonnes sont liées mais ont des noms différents.

Answer 4

C'est une question intéressante. L'approche que j'ai utilisée était une recherche par force brute pour les colonnes correspondant aux types et aux valeurs pour un petit ensemble d'échantillons. Vous devrez probablement ajuster les heuristiques pour obtenir de bons résultats pour votre schéma. J'ai exécuté cela sur un schéma qui n'utilisait pas des clés auto-incrémentées et cela a bien fonctionné. Le code est écrit pour MySQL, mais il est très facile à adapter à Oracle.

use strict;
use warnings;
use DBI;

my $dbh = DBI->connect("dbi:mysql:host=localhost;database=SCHEMA", "USER", "PASS");

my @list;
foreach my $table (show_tables()) {
    foreach my $column (show_columns($table)) {
        push @list, { table => $table, column => $column };
    }
}

foreach my $m (@list) {
    my @match;
    foreach my $f (@list) {
        if (($m->{table} ne $f->{table}) &&
            ($m->{column}{type} eq $f->{column}{type}) &&
            (samples_found($m->{table}, $m->{column}{name}, $f->{column}{samples})))
        {
            # Pour une meilleure confiance, ajoutez d'autres heuristiques telles que
            # joindre les tables et vérifier que chaque valeur
            # apparaît dans le maître. Il peut également être utile d'exclure
            # les colonnes dans les grandes tables sans index bien que
            # cette heuristique puisse échouer pour les clés composites.
            #
            # Des heuristiques telles que les colonnes ayant le même nom sont trop
            # fragiles pour bon nombre des schémas sur lesquels j'ai travaillé. Il peut
            # être trop demander même de requérir des types identiques.

            push @match, "$f->{table}.$f->{column}{name}";
        }
    }
    if (@match) {
        print "$m->{table}.$m->{column}{name} $m->{column}{type} <-- @match\n";
    }
}

$dbh->disconnect();

exit;

sub show_tables {
    my $result = query("show tables");
    return ($result) ? @$result : ();
}

sub show_columns {
    my ($table) = @_;
    my $result = query("desc $table");
    my @columns;
    if ($result) {
        @columns = map {
            { name => $_->[0],
              type => $_->[1],
              samples => query("select distinct $_->[0] from $table limit 10") }
        } @$result;
    }
    return @columns;
}

sub samples_found {
    my ($table, $column, $samples) = @_;
    foreach my $v (@$samples) {
        my $result = query("select count(1) from $table where $column=?", $v);
        if (!$result || $result->[0] == 0) {
            return 0;
        }
    }
    return 1;
}

sub query {
    my ($sql, @binding) = @_;
    my $result = $dbh->selectall_arrayref($sql, undef, @binding);
    if ($result && $result->[0] && @{$result->[0]} == 1) {
        foreach my $row (@$result) {
            $row = $row->[0];
        }
    }
    return $result;
}