update_meta_coord.pl 3.15 KB
Newer Older
1 2 3 4 5 6 7
#!/usr/local/ensembl/bin/perl -w

use strict;
use Bio::EnsEMBL::DBSQL::DBConnection;
use Getopt::Long;

my $help = 0;
8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35
my ( $host, $port, $user, $pass, $dbpattern );

my $usage = qq(
  $0 --host ens-staging --port 3306 --user ensadmin \\
    --pass XXX --dbpattern core

  [--help] displays this menu.

This script will dump the current meta_coord table in the latest
homo_sapiens_core.meta_coord file.  Then it will update the meta_coord
table for all the following table names one by one

  assembly_exception
  gene
  exon
  density_feature
  ditag
  ditag_feature
  dna_align_feature
  karyotype
  marker_feature
  misc_feature
  qtl_feature
  prediction_exon
  prediction_transcript
  protein_align_feature
  repeat_feature
  simple_feature
36
  splicing_event
37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59
  transcript
  );

if (scalar @ARGV == 0 ) {
  print $usage, "\n";
  exit 0;
}

GetOptions( 'help'        => \$help,
            'host=s'      => \$host,
            'port=i'      => \$port,
            'user=s'      => \$user,
            'pass=s'      => \$pass,
            'dbpattern=s' => \$dbpattern );

if ($help) {
  print $usage, "\n";
  exit 0;
}

#print "help: $help argv:"
  #. scalar(@ARGV)
  #. "$host $port $user $pass $dbname\n";
60

61 62 63 64 65
my $dsn = "DBI:mysql:host=$host";
$dsn .= ";port=$port" if ($port);

my $db = DBI->connect( $dsn, $user, $pass );

66 67
my @dbnames =
  map { $_->[0] } @{ $db->selectall_arrayref("show databases") };
68 69 70

for my $dbname (@dbnames) {

71 72 73 74 75 76 77 78
  next if ( $dbname !~ /$dbpattern/ );

  my $dbc =
    new Bio::EnsEMBL::DBSQL::DBConnection( -host   => $host,
                                           -port   => $port,
                                           -user   => $user,
                                           -pass   => $pass,
                                           -dbname => $dbname );
79

80
  my @table_names = qw(
81 82 83 84 85 86 87 88 89 90 91 92 93 94 95
    assembly_exception
    gene
    exon
    density_feature
    ditag_feature
    dna_align_feature
    karyotype
    marker_feature
    misc_feature
    qtl_feature
    prediction_exon
    prediction_transcript
    protein_align_feature
    repeat_feature
    simple_feature
96
    splicing_event
97 98 99 100
    transcript
  );

  unless (
101
           system(     "mysql -h$host -P$port -u$user -p'$pass' -N "
102 103 104 105
                     . "-e 'SELECT * FROM meta_coord' $dbname "
                     . "> $dbname.meta_coord.backup"
           ) == 0 )
  {
106 107 108
    print STDERR "Can't dump the original meta_coord for back up\n";
    exit 1;
  } else {
109 110
    print STDERR "Original meta_coord table backed up in "
      . "$dbname.meta_coord.backup\n";
111
  }
112

113 114
  foreach my $table_name (@table_names) {
    print STDERR "Updating $table_name table entries...";
115
    my $sql = "DELETE FROM meta_coord WHERE table_name = ?";
116 117 118
    my $sth = $dbc->prepare($sql);
    $sth->execute($table_name);
    $sth->finish;
Felix Kokocinski's avatar
Felix Kokocinski committed
119

120 121 122 123 124 125 126
    $sql =
        "INSERT INTO meta_coord "
      . "SELECT '$table_name', s.coord_system_id, "
      . "MAX( t.seq_region_end - t.seq_region_start + 1 ) "
      . "FROM $table_name t, seq_region s "
      . "WHERE t.seq_region_id = s.seq_region_id "
      . "GROUP BY s.coord_system_id";
127 128 129 130 131
    $sth = $dbc->prepare($sql);
    $sth->execute;
    $sth->finish;
    print STDERR "Done\n";
  }
132
} ## end for my $dbname (@dbnames)