[[Comparer deux schémas Oracle avec PERL]]
 
Piste: » perso » Adsl Monitoring ou verifier la connexion adsl » A Simple Cocoa Command-Line Tool » Comparer deux schémas Oracle avec PERL
Vous êtes ici: danjer » Publications Techniques de Danjer » Comparer deux schémas Oracle avec PERL
Table des matières

Comparer deux schémas Oracle avec PERL

But

Lors d’une migration oracle 8.0.6 vers 9i, nous avions besoin de vérifier que les objets présents dans une base étaient bien présent dans une autre.

Détails techniques

Environnement

  • Oracle 8.0.6
  • Oracle 9i
  • Tru64
  • DBA pas du tout pro actif

Principes techniques

Le script génère un csv pour être transforme en feuille Excel. Le tout avec une joli barre de progression.

Code

#!/usr/bin/env perl
 
use strict;
#use warnings;
use Term::ProgressBar;
use DBI;
 
my $h = {
	 nine => {
		  env => '/produits/oracleb/920/products',
		  host =>'host1',
		  dbname =>'dbname1',
		  user   =>'user1',
		  pass   =>'pass1',
		  port  => '1522',
	      },
	 height => {
		    env => '/produits/oraclea/806/products',
		    host =>'host2',
		    dbname =>'dbname2',
		    user   =>'user2',
		    pass   =>'pass2',
		    port  => '1521',
	      },
	};
my $sql = {
	   count_all_table => 'select COUNT(table_name) from user_tables',
	   get_all_table => 'select table_name from user_tables',
	   get_all_objs => 'select OBJECT_TYPE,OBJECT_NAME '
	   . 'from dba_objects ORDER BY OBJECT_NAME,OBJECT_TYPE',
	   get_table_col => 'select ' 
	   . 'COLUMN_ID, COLUMN_NAME, NULLABLE, '
	   . 'DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE '
	   . 'from user_tab_columns '
	   . 'where TABLE_NAME=? ORDER BY COLUMN_ID',
	   get_dba_table_col => 'select ' 
	   . 'COLUMN_ID, COLUMN_NAME, NULLABLE, '
	   . 'DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DATA_SCALE '
	   . 'from dba_tab_columns '
	   . 'where TABLE_NAME=? ORDER BY COLUMN_ID',
	  };
#DBI->trace(15, '/tmp/dbi_trace');
 
sub dbconnect {
  my $ver = shift;
#  $ENV{ORACLE_HOME} = $h->{$ver}->{env};
  my $dbh = DBI->connect(join('',
		    "dbi:Oracle:",
		    "host=$h->{$ver}->{host}",
		    ";sid=$h->{$ver}->{dbname}",
		    defined $h->{$ver}->{port} ?
		    ";port=$h->{$ver}->{port}" : ""),
	       "$h->{$ver}->{user}/$h->{$ver}->{pass}", '')
    or die "Unable to connect to $h->{$ver}->{dbname}: $DBI::errstr\n";
#  print STDERR "Connected $ver\n";
  $h->{$ver}->{dbh} = $dbh;
  foreach my $k (keys %{$sql}) {
    $h->{$ver}->{sql}->{$k} = $dbh->prepare($sql->{$k});
  }
  return ($dbh);
}
 
sub dbexecute {
  my $ver = shift;
  my $sql_name = shift;
#  print STDERR "execute $sql_name version $ver\n";
  my $sth = $h->{$ver}->{sql}->{$sql_name};
  my $rv = $sth->execute(@_);
  $sth->fetchall_arrayref();
}
 
sub describe_table {
  my $ver = shift;
  my $t = shift;
#  print STDERR "execute describe $t version $ver\n";
  my $sth = $h->{$ver}->{dbh}->column_info(undef, undef, $t, undef, {
					   TABLE_NAME => $t,
					  });
  my $res = $sth->fetchall_arrayref();
  die unless defined $res;
  return ($res);
}
 
sub array_comp {
  my ($a, $b) = @_;
  print "a: $a\n";
  print "b: $b\n";
  my $i = 0;
  while (defined $a->[$i] or defined $b->[$i]) {
#    unless ((defined $a->[$i] and defined $b->[$i]) or
#	    ($a->[$i] eq $b->[$i])){
#      print "$i =>", join(':',
#			map { defined $_ ? $_ : "NULL"}
#			($a->[$i], $b->[$i])), "\n" ;
#    }
    return ([$i, $a->[$i], $b->[$i]])
      unless defined $a->[$i] and defined $b->[$i];
    return ([$i, $a->[$i], $b->[$i]])
      unless $a->[$i] eq $b->[$i];
    return undef if ($b->[$i] eq "");
    $i++
  }
  return undef;
}
 
sub compare_table_old {
  my ($p, $table, @vers) = @_;
  my $table_info = [];
  foreach my $ver (@vers) {
    $p->update($p->next_call);
    push @$table_info, describe_table($ver, $table);
 
  }
  my $i = [];
  $p->update($p->next_call);
  while ( @{$table_info->[0]} and @{$table_info->[1]} ) {
    #print scalar @{$table_info->[0]}, " " , scalar @{$table_info->[1]}, "\n";
    $i->[0] = shift @{$table_info->[0]};
    $i->[1] = shift @{$table_info->[1]};
    my $res = array_comp(@$i);
    $p->update($p->next_call) and return $res if defined $res;
  }
  $p->update($p->next_call);
  return undef;
}
 
 
sub compare_table {
  my ($p, $type, $table, @vers) = @_;
  my $table_info = [];
  foreach my $ver (@vers) {
    $p->update($p->next_call);
    my $ret = dbexecute($ver, $type, $table);
#    push @$table_info, describe_table($ver, $table);
#    print "ret: $ret\n";
    my $info;
    foreach my $line (@$ret) {
      push @$info, join(' ', $line->[0], $line->[1],  $line->[2] eq 'Y' ? "NULLABLE": "        ", "$line->[3]($line->[4])[$line->[5]/$line->[6]]") ;
    }
    push @$table_info, $info;
  }
  my $i = [];
  my $res = undef;
  $p->update($p->next_call);
  $p->update($p->next_call) and return undef
    if not defined $table_info->[0] or not defined $table_info->[1];
  while ( @{$table_info->[0]} and @{$table_info->[1]} ) {
    #print scalar @{$table_info->[0]}, " " , scalar @{$table_info->[1]}, "\n";
    $i->[0] = shift @{$table_info->[0]};
    $i->[1] = shift @{$table_info->[1]};
    $res = $i->[0] eq $i->[1] ? undef : "$i->[0] => $i->[1]";
    $p->update($p->next_call) and return $res if defined $res;
  }
  $p->update($p->next_call);
  return undef;
}
 
sub compare_table_list {
  my @vers = @_;
  my $r = {};
 
  my $progress = Term::ProgressBar->new({name  => 'Getting Tables',
                                         count => 2,
                                         ETA   => 'linear',
					 fh    => \*STDERR,
					});
 
  my $i = 0;
  foreach my $ver (@vers) {
    $progress->update(++$i);
    $r->{$ver}->{res} = dbexecute($ver, 'get_all_table');
  }
  my $count = 0;
  map {$count += scalar @{$r->{$_}->{res}}} @vers;
  $progress = Term::ProgressBar->new({name  => 'Sorting Tables',
				      count => $count,
				      ETA   => 'linear',
				      fh    => \*STDERR,
				     });
  $i = 0;
  foreach my $ver (@vers) {
#    print "ref: $r->{$ver}->{res}\n";
    foreach my $row (@{$r->{$ver}->{res}}) {
      my ($table) = @$row;
      $r->{table}->{$table}++;
      $r->{$ver}->{table}->{$table}++;
      $progress->update(++$i);
    }
  }
  print FILE_OUT "table;";
  map { print FILE_OUT "$h->{$_}->{dbname};" } @vers;
  print FILE_OUT "\n";
  $count = scalar keys %{$r->{table}};
  $progress = Term::ProgressBar->new({name  => 'Analysing Tables',
				      count => $count * 4,
				      ETA   => 'linear',
				      fh    => \*STDERR,
				     });
  foreach my $table (sort keys %{$r->{table}}) {
    print FILE_OUT "$table;";
    foreach my $ver (@vers) {
      my $line = defined $r->{$ver}->{table}->{$table}
	? "presente;": "absente;";
      print FILE_OUT $line;
    }
    if ($r->{table}->{$table} == 2) {
      my $res;
      $res = compare_table($progress, 'get_table_col', $table, @vers);
      if (defined $res) {
	print FILE_OUT "$res;";
      }
    }
    else {
      my $nexti = $progress->next_call(4);
#      print "Count ", $count*4, " Next call => $i\n";
      $progress->update($nexti);
    }
 
    print FILE_OUT "\n";
  }
  print FILE_OUT "\nTotal des tables;";
  foreach my $ver (@vers) {
    print FILE_OUT scalar keys %{$r->{$ver}->{table}}, ";";
  }
  print FILE_OUT scalar keys %{$r->{table}}, ";\n";
}
 
sub compare_objs_list {
  my @vers = @_;
  my $r = {};
 
  my $progress = Term::ProgressBar->new({name  => 'Getting Objects',
                                         count => 2,
                                         ETA   => 'linear',
					 fh    => \*STDERR,
					});
 
  my $i = 0;
  foreach my $ver (@vers) {
    $progress->update(++$i);
    $r->{$ver}->{res} = dbexecute($ver, 'get_all_objs');
  }
  my $count = 0;
  map {$count += scalar @{$r->{$_}->{res}}} @vers;
  $progress = Term::ProgressBar->new({name  => 'Sorting Objects',
				      count => $count,
				      ETA   => 'linear',
				      fh    => \*STDERR,
				     });
  $i = 0;
  foreach my $ver (@vers) {
#    print "ref: $r->{$ver}->{res}\n";
    foreach my $objs (@{$r->{$ver}->{res}}) {
      $r->{objs_name}->{$objs->[1]} = $objs;
      $r->{objs}->{$objs->[1]}++;
      $r->{$ver}->{objs}->{$objs->[1]}++;
      $progress->update(++$i);
    }
  }
  print FILE_OUT "object type; object type;";
  map { print FILE_OUT "$h->{$_}->{dbname};" } @vers;
  print FILE_OUT "\n";
  $count = scalar keys %{$r->{objs}};
  $progress = Term::ProgressBar->new({name  => 'Analysing Objects',
				      count => $count * 4,
				      ETA   => 'linear',
				      fh    => \*STDERR,
				     });
  foreach my $objs (sort keys %{$r->{objs}}) {
    $objs = $r->{objs_name}->{$objs};
    print FILE_OUT $objs->[0], ';', $objs->[1], ';';
    foreach my $ver (@vers) {
      my $line = defined $r->{$ver}->{objs}->{$objs->[1]}
	? "presente;": "absente;";
      print FILE_OUT $line;
    }
    if ($r->{objs}->{$objs->[1]} == 2 and $objs->[1] eq 'TABLE') {
      my $res;
      $res = compare_table($progress, 'get_dba_table_col',$objs->[1], @vers);
      if (defined $res) {
	print FILE_OUT "$res;";
      }
    }
    else {
      my $nexti = $progress->next_call(4);
#      print "Count ", $count*4, " Next call => $i\n";
      $progress->update($nexti);
    }
 
    print FILE_OUT "\n";
  }
  print FILE_OUT "\nTotal des objects;";
  foreach my $ver (@vers) {
    print FILE_OUT scalar keys %{$r->{$ver}->{objs}}, ";";
  }
  print FILE_OUT scalar keys %{$r->{objs}}, ";\n";
}
 
 
sub dbconneting {
  my @vers = @_;
  my $progress = Term::ProgressBar->new({name  => 'Connecting',
                                         count => scalar @vers,
                                         ETA   => 'linear',
					 fh    => \*STDERR,
					});
  my $i = 0;
  foreach my $ver (@vers) {
    dbconnect($ver);
    $progress->update(++$i);
  }
}
 
open FILE_OUT, ">list_objs.csv" or die;
dbconneting(keys %$h);
#compare_table_list(keys %$h); #user fashion
compare_objs_list(keys %$h); #dba way
 
tech/comparer_deux_bases_de_donnees_oracle.txt · Dernière modification: 2006/08/26 11:50 par danjer
 
Recent changes RSS feed Valid XHTML 1.0 Valid CSS Driven by DokuWiki Powered by Lescampeurs