<commit_before>#!/software/bin/perl -w

#
# updates the external db tables on all of the core databases on a given host
#

use strict;

use Getopt::Long;
use DBI;
use IO::File;

my ( $host, $user,        $pass,   $port, @dbnames,
     $file, $release_num, $master, $nonreleasemode, $force );

GetOptions( "dbhost|host=s",        \$host,
            "dbuser|user=s",        \$user,
            "dbpass|pass=s",        \$pass,
            "dbport|port=i",        \$port,
            "file=s",        \$file,
            "dbnames=s@",    \@dbnames,
            "release_num=i", \$release_num,
            "master=s",      \$master,
	    "nonreleasemode", \$nonreleasemode, 
            "force",         \$force );

$port ||= 3306;

$file ||= "external_dbs.txt";

usage("[DIE] Need a host") if(!$host);

#release num XOR dbname are required.
usage(   "[DIE] Need either both a release number and "
       . "database names or neither" )
  if ( ( $release_num && @dbnames ) || ( !$release_num && !@dbnames ) );

if(!$nonreleasemode){ 
  # master database is required
  usage("[DIE] Master database required") if (!$master);
}

my $dsn = "DBI:mysql:host=$host;port=$port";

my $db = DBI->connect( $dsn, $user, $pass, {RaiseError => 1} );

if($release_num) {
  @dbnames = map {$_->[0] } @{ $db->selectall_arrayref( "show databases" ) };

  #
  # filter out all non-core databases
  #
  @dbnames = grep {/^[a-zA-Z]+\_[a-zA-Z]+\_(core|est|estgene|vega|otherfeatures|cdna)\_${release_num}\_\d+[A-Za-z]?$/} @dbnames;
}


#
# make sure the user wishes to continue
#
print STDERR "Please make sure you've updated $file from CVS!\n";
print STDERR
  "The following databases will have their external_db tables "
  . "updated if necessary:\n  ";
print join( "\n  ", @dbnames );
print "\nContinue with update (yes/no)>  ";

my $input = lc(<STDIN>);
chomp($input);
if ($input ne 'yes') {
  print "external_db conversion aborted\n";
  exit();
}

#
# read all of the new external_db entries from the file
#
my $fh = IO::File->new();
$fh->open($file) or die("Could not open input file $file");
my @rows;
my $row;
while ($row = <$fh>) {
  chomp($row);
  next if ($row =~ /^#/);    # skip comments
  next if ($row =~ /^$/);    # and blank lines
  next if ($row =~ /^\s+$/); # and whitespace-only lines
  my @a = split(/\t/, $row);
  push @rows, {
      'external_db_id'         => $a[0],
      'db_name'                => $a[1],
      'release'                => $a[2],
      'status'                 => $a[3],
      'dbprimary_acc_linkable' => $a[4],
      'display_label_linkable' => $a[5],
      'priority'               => $a[6],
      'db_display_name'        => $a[7],
      'type'                   => $a[8] };

  if ( $a[1] =~ /-/ ) {
    print STDERR "Database name "
      . $a[1]
      . " contains '-' characters "
      . "which will break Mart, "
      . "please replace them with '_' until Mart is fixed\n";
    exit(1);
  }

  if ( $a[1] =~ /^$/ || $a[1] =~ /^\s+$/ || $a[1] =~ /^\d+$/ ) {
    print STDERR "Cannot parse the following line:\n" 
      . $row
      . "\nIt probably has spaces separating the fields "
      . "rather than tabs.\n";
    exit(1);
  }
}
$fh->close();

# Load into master database
  if(!$nonreleasemode){
    load_database($db, $master, @rows);
  }
  # Check each other database in turn
  # Load if no extra rows in db that aren't in master
  # Warn and skip if there are
  
  foreach my $dbname (@dbnames) {
    
    print STDERR "Looking at $dbname ... \n";
    if ($force || $nonreleasemode) {
      
      print STDERR "Forcing overwrite of external_db table in "
	. "$dbname from $file\n";
      load_database( $db, $dbname, @rows );
      
    } elsif (compare_external_db($db, $master, $dbname)) {
      
      print STDERR "$dbname has no additional rows. "
	. "Overwriting external_db table from $file\n";
      load_database( $db, $dbname, @rows );
      
    } else {
      
      print STDERR "$dbname has extra rows "
	. "that are not in $file, skipping\n";
	
    }
 


  }

print STDERR "Updates complete\n";



sub load_database {
  my ($db, $dbname, @rows) = @_;

  $db->do("USE $dbname");

  # Save all existing release information from the table.
  my $sth = $db->prepare(
    qq( SELECT  external_db_id, db_release
        FROM    external_db) );
  $sth->execute();

  my %saved_release;
  while ( my ( $id, $release ) = $sth->fetchrow_array() ) {
    if ( defined($release) && $release ne '1' ) {
      $saved_release{$id} = $release;
    }
  }
  $sth->finish();

  # Delete the existing table
  $sth = $db->prepare('DELETE FROM external_db');<commit_msg>Added lots of sanity checking to track down formatting errors.<commit_after>#!/software/bin/perl -w

#
# updates the external db tables on all of the core databases on a given host
#

use strict;

use Getopt::Long;
use DBI;
use IO::File;

my ( $host, $user,        $pass,   $port, @dbnames,
     $file, $release_num, $master, $nonreleasemode, $force );

GetOptions( "dbhost|host=s",        \$host,
            "dbuser|user=s",        \$user,
            "dbpass|pass=s",        \$pass,
            "dbport|port=i",        \$port,
            "file=s",        \$file,
            "dbnames=s@",    \@dbnames,
            "release_num=i", \$release_num,
            "master=s",      \$master,
	    "nonreleasemode", \$nonreleasemode, 
            "force",         \$force );

$port ||= 3306;

$file ||= "external_dbs.txt";

usage("[DIE] Need a host") if(!$host);

#release num XOR dbname are required.
usage(   "[DIE] Need either both a release number and "
       . "database names or neither" )
  if ( ( $release_num && @dbnames ) || ( !$release_num && !@dbnames ) );

if(!$nonreleasemode){ 
  # master database is required
  usage("[DIE] Master database required") if (!$master);
}

my $dsn = "DBI:mysql:host=$host;port=$port";

my $db = DBI->connect( $dsn, $user, $pass, {RaiseError => 1} );

if($release_num) {
  @dbnames = map {$_->[0] } @{ $db->selectall_arrayref( "show databases" ) };

  #
  # filter out all non-core databases
  #
  @dbnames = grep {/^[a-zA-Z]+\_[a-zA-Z]+\_(core|est|estgene|vega|otherfeatures|cdna)\_${release_num}\_\d+[A-Za-z]?$/} @dbnames;
}

my @field_names = qw(external_db_id db_name release status dbprimary_acc_linkable display_label_linkable priority  db_display_name type);

my @types = qw(ARRAY ALT_TRANS MISC LIT PRIMARY_DB_SYNONYM);

#
# make sure the user wishes to continue
#
print STDERR "Please make sure you've updated $file from CVS!\n";
print STDERR
  "The following databases will have their external_db tables "
  . "updated if necessary:\n  ";
print join( "\n  ", @dbnames );
print "\nContinue with update (yes/no)>  ";

my $input = lc(<STDIN>);
chomp($input);
if ($input ne 'yes') {
  print "external_db conversion aborted\n";
  exit();
}

#
# read all of the new external_db entries from the file
#
my $fh = IO::File->new();
$fh->open($file) or die("Could not open input file $file");
my @rows;
my %bad_lines;

while (my $row = <$fh>) {
  chomp($row);
  next if ($row =~ /^#/);    # skip comments
  next if ($row =~ /^$/);    # and blank lines
  next if ($row =~ /^\s+$/); # and whitespace-only lines
  my @a = split(/\t/, $row);
  push @rows, {
      'external_db_id'         => $a[0],
      'db_name'                => $a[1],
      'release'                => $a[2],
      'status'                 => $a[3],
      'dbprimary_acc_linkable' => $a[4],
      'display_label_linkable' => $a[5],
      'priority'               => $a[6],
      'db_display_name'        => $a[7],
      'type'                   => $a[8] };

  if ( $a[1] =~ /-/ ) {
    print STDERR "Database name "
      . $a[1]
      . " contains '-' characters "
      . "which will break Mart, "
      . "please replace them with '_' until Mart is fixed\n";
    exit(1);
  }

  # do some formatting checks
  my $blank;
  for (my $i=0; $i < scalar(@a); $i++) {
    if ($a[$i] eq '') {
      $bad_lines{$row} = $field_names[$i] . " - field blank - check all tabs/spaces in line";
    }
  }

  if ($a[1] =~ /\s/) {
    $bad_lines{$row} = "db_name field appears to contain spaces";
  }
  if ($a[1] =~ /^$/) {
    $bad_lines{$row} = "db_name field appears to be missing";
  }
  if ($a[1] =~ /^\s+$/) {
    $bad_lines{$row} = "db_name field appears to be blank";
  }
  if ($a[1] =~ /^\d+$/) {
    $bad_lines{$row} = "db_name field appears to be numeric - check formatting";
  }

  my $type_ok;
  foreach my $type (@types) {
    $type_ok = 1 if ($a[8] eq $type);
  }
  $bad_lines{$row} = "type field is " . $a[8] . ", not one of the recognised types" if (!$type_ok);

}
$fh->close();

if (%bad_lines) {
  print STDERR "Cannot parse the following line(s) from $file; check that all fields are present and are separated by one tab (not spaces). \n";
  print STDERR "Name of problem field, and the error is printed in brackets first\n\n";
  foreach my $row (keys %bad_lines) {
    print STDERR "[". $bad_lines{$row} . "]" .  " $row\n";
  }
  exit(1);
}

# Load into master database
  if(!$nonreleasemode){
    load_database($db, $master, @rows);
  }
  # Check each other database in turn
  # Load if no extra rows in db that aren't in master
  # Warn and skip if there are

  foreach my $dbname (@dbnames) {

    print STDERR "Looking at $dbname ... \n";
    if ($force || $nonreleasemode) {

      print STDERR "Forcing overwrite of external_db table in "
	. "$dbname from $file\n";
      load_database( $db, $dbname, @rows );

    } elsif (compare_external_db($db, $master, $dbname)) {

      print STDERR "$dbname has no additional rows. "
	. "Overwriting external_db table from $file\n";
      load_database( $db, $dbname, @rows );

    } else {

      print STDERR "$dbname has extra rows "
	. "that are not in $file, skipping\n";
	
    }

  }

print STDERR "Updates complete\n";



sub load_database {
  my ($db, $dbname, @rows) = @_;

  $db->do("USE $dbname");

  # Save all existing release information from the table.
  my $sth = $db->prepare(
    qq( SELECT  external_db_id, db_release
        FROM    external_db) );
  $sth->execute();

  my %saved_release;
  while ( my ( $id, $release ) = $sth->fetchrow_array() ) {
    if ( defined($release) && $release ne '1' ) {
      $saved_release{$id} = $release;
    }
  }
  $sth->finish();

  # Delete the existing table
  $sth = $db->prepare('DELETE FROM external_db');
{'fraction_non_alphanumeric': 0.17299496319256102, 'fraction_numerical': 0.00493994575745835, 'mean_word_length': 2.6038394415357766, 'pattern_counts': {'":': 0, '<': 8, '<?xml version=': 0, '>': 53, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '6194441', 'n_tokens_mistral': 3858, 'n_tokens_neox': 3695, 'n_words': 1032}