编程提取RDBMS中的表与外键之间的关系？

Question

我正在反向设计Oracle数据库中中等数量的表（50+）之间的关系，这些表之间没有定义外键。我可以计算（有点）能够跨表匹配列名称。例如，列名称“SomeDescriptiveName”在整个表格中可能是相同的。

我希望能够做的是找到一种更好的方式，根据那些匹配列名称来提取一些关系集，而不是逐个手动遍历表。我可以使用Java DatabaseMetaData方法做一些事情，但似乎这是某人以前可能需要编写脚本的任务之一。也许用Perl或其他脚本语言lang提取列名称，使用列名作为散列键并将表添加到由散列键指向的数组？

任何人有任何提示或建议，可能会使这个更简单或提供一个良好的起点？这是一个丑陋的需求，如果外键已经被定义，理解关系会容易得多。

谢谢。

Answer 1

你几乎在你的问题中写了答案。

my %column_tables; 
foreach my $table (@tables) { 
    foreach my $column ($table->columns) { 
     push @{$column_tables[$column]}, $table; 
    } 
} 
print "Likely foreign key relationships:\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

您可以使用组合的三个（或四个）接近，这取决于如何混淆的模式是：

• 动态方法
  • 观察：
    • 在RDBMS（或ODBC层）中启用跟踪，然后
    • 在应用程序（理想地记录创建）执行各种活动，然后
    • 识别紧序列被改变哪些表，以怎样的列值对序列的时间间隔期间在一个以上的列存在
    • 值可以指示一个外键关系
• 静态方法（只分析现有数据，没有必要有一个运行的应用程序）
  • 命名：尽量推断列名
  • 统计关系：看看最小/最大（也可能是平均值）唯一值的所有数值列，并尝试进行匹配
  • 代码反向工程：您的最后一招（除非使用脚本处理） - 不是为微弱的心脏:)

Answer 3

我的策略是使用Oracle系统目录查找关口在列相同的名称和数据类型但在不同，表名称。另外哪一列是表的主键或唯一键的一部分。

这里有可能接近这样一个查询，但我没有一个Oracle实例方便对其进行测试：

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; 

当然，这不会得到那些相关列任何情况下，但名称不同。

Answer 4

这是一个有趣的问题。我采取的方法是强力搜索匹配小样本集的类型和值的列。您可能必须调整启发式才能为您的架构提供良好的结果。我在一个没有使用自动递增键的模式上运行它，它运行良好。该代码是为MySQL编写的，但适应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}))) 
     { 
      # For better confidence, add other heuristics such as 
      # joining the tables and verifying that every value 
      # appears in the master. Also it may be useful to exclude 
      # columns in large tables without an index although that 
      # heuristic may fail for composite keys. 
      # 
      # Heuristics such as columns having the same name are too 
      # brittle for many of the schemas I've worked with. It may 
      # be too much to even require identical types. 

      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; 
}