bin/download_accession_attributes.pl

   1
   2 =head1 NAME
   3
   4 download_accession_attributes.pl - script to download accession attributes
   5 =head1 DESCRIPTION
   6
   7 perl download_accession_attributes.pl -i trial_id -H host -D dbname -U dbuser -P dbpass
   8
   9 Downloads trials whose ids are provided as a comma separated list for the -i parameter.
  10
  11 =head1 AUTHOR
  12
  13 Lukas Mueller <lam87@cornell.edu>, March 2022.
  14
  15 =cut
  16
  17 use strict;
  18
  19 use Getopt::Std;
  20 use Data::Dumper;
  21
  22 use Bio::Chado::Schema;
  23 use CXGN::Metadata::Schema;
  24 use CXGN::Phenome::Schema;
  25 use CXGN::People::Schema;
  26 use CXGN::DB::InsertDBH;
  27 use CXGN::Stock;
  28 use Config::Any;
  29
  30
  31 our ($opt_H, $opt_D, $opt_U, $opt_P, $opt_b, $opt_i, $opt_n, $opt_t, $opt_r, $opt_c);
  32
  33 getopts('H:D:U:P:b:i:t:r:c:n');
  34
  35 my $dbhost = $opt_H;
  36 my $dbname = $opt_D;
  37 my $dbuser = $opt_U;
  38 my $dbpass = $opt_P;
  39 my $accession_file = $opt_i;
  40 my $non_interactive = $opt_n;
  41 my $sgn_local_conf_file = $opt_c || "sgn_local.conf";
  42
  43 print STDERR "Using config from $sgn_local_conf_file\n";
  44
  45 my @editable_stock_props = ("population_name", "organization_name", "synonym", "PUI", "accession number", "acquisition date", "biological status of accession code", "country of origin", "donor", "donor PUI", "donor institute", "genome_structure", "institute code", "institute name", "introgression_backcross_parent", "introgression_chromosome", "introgression_end_position_bp", "introgression_map_version", "introgression_parent", "introgression_start_position_bp", "location_code",  "ncbi_taxonomy_id", "notes","organization", "ploidy_level", "released_variety_name", "seed source", "state", "transgenic", "type of germplasm storage code", "variety" );
  46
  47 my $editable_stock_props_read_from_conf_file = 0;
  48 if (-e $sgn_local_conf_file) {
  49     my $config = Config::Any->load_files( { files => [ $sgn_local_conf_file ] , use_ext => 1} );
  50
  51     print STDERR Dumper($config);
  52     @editable_stock_props = split /\,/, $config->[0]->{$sgn_local_conf_file}->{editable_stock_props};
  53     $editable_stock_props_read_from_conf_file = 1;
  54 }
  55
  56 print STDERR "EDITABLE STOCK PROPS = ".Dumper(\@editable_stock_props);
  57 print STDERR "(READ FROM FILE: $editable_stock_props_read_from_conf_file)\n";
  58
  59 my $dbh = CXGN::DB::InsertDBH->new( { dbhost=>$dbhost,
  60                                       dbname=>$dbname,
  61                                       dbargs => {AutoCommit => 0,
  62                                                  RaiseError => 1},
  63
  64                                     },
  65
  66
  67
  68     );
  69
  70 print STDERR "Connecting to database...\n";
  71 my $schema= Bio::Chado::Schema->connect(  sub { $dbh->get_actual_dbh() },{  on_connect_do => ['set search_path=public,sgn,phenome'] } );
  72 my $people_schema = CXGN::People::Schema->connect( sub { $dbh->get_actual_dbh() } );
  73 #my $metadata_schema = CXGN::Metadata::Schema->connect( sub { $dbh->get_actual_dbh() });
  74 #my $pheome_schema = CXGN::Phenome::Schema->connect( sub { $dbh->get_actual_dbh() });
  75
  76 my @accessions;
  77
  78 my $cv_id = $schema->resultset('Cv::Cv')->find({ name => 'stock_property' })->cv_id();
  79
  80 my %db_stock_props;
  81 my $db_stock_props_rs = $schema->resultset('Cv::Cvterm')->search( { 'cv_id' => $cv_id });
  82 while (my $prop_cvterms = $db_stock_props_rs->next()) {
  83     $db_stock_props{$prop_cvterms->cvterm_id()} = $prop_cvterms->name();
  84 }
  85
  86
  87 open(my $F, "<", $accession_file) || die "Can't open $accession_file";
  88 while (<$F>) {
  89     chomp;
  90     push @accessions, $_;
  91 }
  92
  93 close($F);
  94
  95 print STDOUT join("\t", ('accession_name', 'species_name', @editable_stock_props))."\n";
  96
  97 foreach my $name (@accessions) {
  98     my $accession_rs = $schema->resultset("Stock::Stock")->search( { uniquename => $name } );
  99
 100     foreach my $row ($accession_rs->next()) {
 101
 102         my $organism = $schema->resultset("Organism::Organism")->find( { organism_id => $row->organism_id } )->species();
 103
 104         my $stockprop_rs = $schema->resultset("Stock::Stockprop")->search( { stock_id => $row->stock_id() });
 105
 106         my @stockprops;
 107         while (my $sp_row = $stockprop_rs->next()) {
 108             push @stockprops, [ $row->stock_id(), $sp_row->type_id(), $db_stock_props{$sp_row->type_id()}, $sp_row->value() ];
 109         }
 110
 111         my %stockprops;
 112         print STDERR "STOCKPROP DATA = ".Dumper(\@stockprops);
 113         foreach my $sp (@stockprops) {
 114             print STDERR "PROP NAME = $sp->[2], VALUE $sp->[3]\n";
 115             $stockprops{$sp->[2]} = $sp->[3];
 116
 117         }
 118
 119         # output the accession_name, species_name and the props in the right order
 120         #
 121         print STDOUT join("\t", ($row->uniquename(), $organism));
 122
 123
 124         my @accession_stock_props = ();
 125         foreach my $p (@editable_stock_props) {
 126             print STDERR "STOCKPROP: $stockprops{$p}\n";
 127             push @accession_stock_props, $stockprops{$p};
 128         }
 129         print STDOUT "\t".join("\t", @accession_stock_props)."\n";
 130
 131
 132     }
 133
 134
 135
 136 }
 137
 138
 139