db/00101/AdaptToNewGenotypeStorage.pm

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6  AdaptToNewGenotypeStorage
   7
   8 =head1 SYNOPSIS
   9
  10 mx-run AdaptToNewGenotypeStorage [options] -H hostname -D dbname -u username [-F]
  11
  12 this is a subclass of L<CXGN::Metadata::Dbpatch>
  13 see the perldoc of parent class for more details.
  14
  15 =head1 DESCRIPTION
  16 This patch adapts to the new genotype storage by changing nd_protocolprop.value to JSONB and by changing the 'snp genotyping' values in genotypeprop to {'markername1' : {'DS' : '1'}, 'markername2' : {'DS' : '0'}, ... } and by changing the type of these genotypeprops to 'vcf_snp_genotyping'
  17 This subclass uses L<Moose>. The parent class uses L<MooseX::Runnable>
  18
  19 =head1 AUTHOR
  20
  21
  22 =head1 COPYRIGHT & LICENSE
  23
  24 Copyright 2010 Boyce Thompson Institute for Plant Research
  25
  26 This program is free software; you can redistribute it and/or modify
  27 it under the same terms as Perl itself.
  28
  29 =cut
  30
  31
  32 package AdaptToNewGenotypeStorage;
  33
  34 use Moose;
  35 use Bio::Chado::Schema;
  36 use Try::Tiny;
  37 use SGN::Model::Cvterm;
  38 use JSON;
  39 extends 'CXGN::Metadata::Dbpatch';
  40
  41
  42 has '+description' => ( default => <<'' );
  43 This patch adapts to the new genotype storage by changing nd_protocolprop.value to JSONB and by changing the 'snp genotyping' values in genotypeprop to {'markername1' : {'DS' : '1'}, 'markername2' : {'DS' : '0'}, ... } and by changing the type of these genotypeprops to 'vcf_snp_genotyping'
  44
  45 has '+prereq' => (
  46         default => sub {
  47         [],
  48     },
  49
  50   );
  51
  52 sub patch {
  53     my $self=shift;
  54
  55     print STDOUT "Executing the patch:\n " .   $self->name . ".\n\nDescription:\n  ".  $self->description . ".\n\nExecuted by:\n " .  $self->username . " .";
  56
  57     print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
  58
  59     print STDOUT "\nExecuting the SQL commands.\n";
  60     my $schema = Bio::Chado::Schema->connect( sub { $self->dbh->clone } );
  61
  62     my $snp_genotyping_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'snp genotyping', 'genotype_property')->cvterm_id();
  63     my $vcf_snp_genotyping_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'vcf_snp_genotyping', 'genotype_property')->cvterm_id();
  64
  65     my $coderef = sub {
  66         my $sql = <<SQL;
  67 ALTER TABLE nd_protocolprop ALTER COLUMN value TYPE JSONB USING value::JSON;
  68 SQL
  69         $schema->storage->dbh->do($sql);
  70
  71         my $pre_q = "SELECT genotype_id FROM genotype;";
  72         my $q = "SELECT genotypeprop_id, value FROM genotypeprop WHERE type_id = $snp_genotyping_cvterm_id AND genotype_id = ?;";
  73         my $update1_q = "UPDATE genotypeprop SET value = ? WHERE genotypeprop_id = ?;";
  74         my $update2_q = "UPDATE genotypeprop SET type_id = $vcf_snp_genotyping_cvterm_id WHERE genotypeprop_id = ?;";
  75
  76         my $pre_h = $schema->storage->dbh()->prepare($pre_q);
  77         my $h = $schema->storage->dbh()->prepare($q);
  78         my $h_update1 = $schema->storage->dbh()->prepare($update1_q);
  79         my $h_update2 = $schema->storage->dbh()->prepare($update2_q);
  80
  81         $pre_h->execute();
  82         while (my ($genotype_id) = $pre_h->fetchrow_array()) {
  83             $h->execute($genotype_id);
  84             while (my ($genotypeprop_id, $json_val) = $h->fetchrow_array()) {
  85                 print STDERR "Converting $genotypeprop_id \n";
  86                 my $val = decode_json $json_val;
  87                 my %new_val;
  88                 while (my ($marker_name, $dosage_value) = each %$val) {
  89                     $new_val{$marker_name} = {'DS' => $dosage_value};
  90                 }
  91                 my $genotypeprop_json = encode_json \%new_val;
  92                 $h_update1->execute($genotypeprop_json, $genotypeprop_id);
  93                 $h_update2->execute($genotypeprop_id);
  94             }
  95         }
  96     };
  97
  98     my $transaction_error;
  99     try {
 100         $schema->txn_do($coderef);
 101     } catch {
 102         $transaction_error =  $_;
 103     };
 104     if ($transaction_error){
 105         print STDERR "ERROR: $transaction_error\n";
 106     } else {
 107         print "You're done!\n";
 108     }
 109 }
 110
 111
 112 ####
 113 1; #
 114 ####