db/00126/SeedlotQualityNSource.pm

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6  SeedlotQualityNSource.pm
   7
   8 =head1 SYNOPSIS
   9
  10 mx-run SeedlotQualityNSource.pm [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
  17 This is a test dummy patch.
  18 This subclass uses L<Moose>. The parent class uses L<MooseX::Runnable>
  19
  20 =head1 AUTHOR
  21
  22  Naama Menda<nm249@cornell.edu>
  23
  24 =head1 COPYRIGHT & LICENSE
  25
  26 Copyright 2010 Boyce Thompson Institute for Plant Research
  27
  28 This program is free software; you can redistribute it and/or modify
  29 it under the same terms as Perl itself.
  30
  31 =cut
  32
  33
  34 package SeedlotQualityNSource;
  35
  36 use Moose;
  37 use Bio::Chado::Schema;
  38 use SGN::Model::Cvterm;
  39
  40 extends 'CXGN::Metadata::Dbpatch';
  41
  42
  43 has '+description' => ( default => ' This patch adds required stock prop types for seedlot quality.');
  44
  45 has '+prereq' => (
  46     default => sub {
  47         [ ],
  48     },
  49   );
  50
  51 sub patch {
  52     my $self=shift;
  53
  54     print STDOUT "Executing the patch:\n " .   $self->name . ".\n\nDescription:\n  ".  $self->description . ".\n\nExecuted by:\n " .  $self->username . " .";
  55
  56     print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
  57
  58     print STDOUT "\nExecuting the SQL commands.\n";
  59
  60      my $schema = Bio::Chado::Schema->connect( sub { $self->dbh->clone } );
  61
  62     $schema->resultset("Cv::Cvterm")->create_with(
  63         {
  64             name => 'seedlot_quality',
  65             cv => 'stock_property'
  66         });
  67
  68
  69
  70     # Now re-generate the materialized view (code lifted from db patch 00113)
  71         my $block_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'block', 'stock_property')->cvterm_id();
  72     my $col_number_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'col_number', 'stock_property')->cvterm_id();
  73     my $igd_synonym_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'igd_synonym', 'stock_property')->cvterm_id();
  74     my $is_a_control_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'is a control', 'stock_property')->cvterm_id();
  75     my $location_code_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'location_code', 'stock_property')->cvterm_id();
  76     my $organization_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'organization', 'stock_property')->cvterm_id();
  77     my $plant_index_number_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'plant_index_number', 'stock_property')->cvterm_id();
  78     my $subplot_index_number_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'subplot_index_number', 'stock_property')->cvterm_id();
  79     my $tissue_sample_index_number_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'tissue_sample_index_number', 'stock_property')->cvterm_id();
  80     my $plot_number_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'plot number', 'stock_property')->cvterm_id();
  81     my $plot_geo_json_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'plot_geo_json', 'stock_property')->cvterm_id();
  82     my $range_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'range', 'stock_property')->cvterm_id();
  83     my $replicate_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'replicate', 'stock_property')->cvterm_id();
  84     my $row_number_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'row_number', 'stock_property')->cvterm_id();
  85     my $synonym_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'stock_synonym', 'stock_property')->cvterm_id();
  86     my $T1_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'T1', 'stock_property')->cvterm_id();
  87     my $T2_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'T2', 'stock_property')->cvterm_id();
  88     my $transgenic_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'transgenic', 'stock_property')->cvterm_id();
  89     my $variety_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'variety', 'stock_property')->cvterm_id();
  90     my $notes_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'notes', 'stock_property')->cvterm_id();
  91     my $state_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'state', 'stock_property')->cvterm_id();
  92     my $accession_number_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'accession number', 'stock_property')->cvterm_id();
  93     my $PUI_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'PUI', 'stock_property')->cvterm_id();
  94     my $donor_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'donor', 'stock_property')->cvterm_id();
  95     my $donor_institute_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'donor institute', 'stock_property')->cvterm_id();
  96     my $donor_PUI_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'donor PUI', 'stock_property')->cvterm_id();
  97     my $seed_source_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'seed source', 'stock_property')->cvterm_id();
  98     my $institute_code_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'institute code', 'stock_property')->cvterm_id();
  99     my $institute_name_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'institute name', 'stock_property')->cvterm_id();
 100     my $biological_status_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'biological status of accession code', 'stock_property')->cvterm_id();
 101     my $country_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'country of origin', 'stock_property')->cvterm_id();
 102     my $germplasm_storage_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'type of germplasm storage code', 'stock_property')->cvterm_id();
 103     my $entry_number_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'entry number', 'stock_property')->cvterm_id();
 104     my $acquisition_date_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'acquisition date', 'stock_property')->cvterm_id();
 105     my $current_count_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'current_count', 'stock_property')->cvterm_id();
 106     my $current_weight_gram_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'current_weight_gram', 'stock_property')->cvterm_id();
 107     my $crossing_metadata_json_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'crossing_metadata_json', 'stock_property')->cvterm_id();
 108     my $ploidy_level_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'ploidy_level', 'stock_property')->cvterm_id();
 109     my $genome_structure_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'genome_structure', 'stock_property')->cvterm_id();
 110     my $introgression_parent_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'introgression_parent', 'stock_property')->cvterm_id();
 111     my $introgression_backcross_parent_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'introgression_backcross_parent', 'stock_property')->cvterm_id();
 112     my $introgression_map_version_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'introgression_map_version', 'stock_property')->cvterm_id();
 113     my $introgression_chromosome_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'introgression_chromosome', 'stock_property')->cvterm_id();
 114     my $introgression_start_position_bp_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'introgression_start_position_bp', 'stock_property')->cvterm_id();
 115     my $introgression_end_position_bp_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'introgression_end_position_bp', 'stock_property')->cvterm_id();
 116     my $is_blank_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'is_blank', 'stock_property')->cvterm_id();
 117     my $concentration_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'concentration', 'stock_property')->cvterm_id();
 118     my $volume_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'volume', 'stock_property')->cvterm_id();
 119     my $extraction_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'extraction', 'stock_property')->cvterm_id();
 120     my $dna_person_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'dna_person', 'stock_property')->cvterm_id();
 121     my $tissue_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'tissue_type', 'stock_property')->cvterm_id();
 122     my $ncbi_taxonomy_id_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'ncbi_taxonomy_id', 'stock_property')->cvterm_id();
 123     my $seedlot_quality_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'seedlot_quality', 'stock_property')->cvterm_id();
 124
 125     $self->dbh->do(<<EOSQL);
 126 --do your SQL here
 127
 128 DROP EXTENSION IF EXISTS tablefunc CASCADE;
 129 CREATE EXTENSION tablefunc;
 130
 131 DROP MATERIALIZED VIEW IF EXISTS public.materialized_stockprop CASCADE;
 132 CREATE MATERIALIZED VIEW public.materialized_stockprop AS
 133 SELECT *
 134 FROM crosstab(
 135   'SELECT stockprop.stock_id, stock.uniquename, stock.type_id, stock_cvterm.name, stock.organism_id, stockprop.type_id, jsonb_object_agg(stockprop.value, ''RANK'' || stockprop.rank) FROM public.stockprop JOIN public.stock USING(stock_id) JOIN public.cvterm as stock_cvterm ON (stock_cvterm.cvterm_id=stock.type_id) GROUP BY (stockprop.stock_id, stock.uniquename, stock.type_id, stock_cvterm.name, stock.organism_id, stockprop.type_id) ORDER by stockprop.stock_id ASC',
 136   'SELECT type_id FROM (VALUES
 137     (''$block_cvterm_id''),
 138     (''$col_number_cvterm_id''),
 139     (''$igd_synonym_cvterm_id''),
 140     (''$is_a_control_cvterm_id''),
 141     (''$location_code_cvterm_id''),
 142     (''$organization_cvterm_id''),
 143     (''$plant_index_number_cvterm_id''),
 144     (''$subplot_index_number_cvterm_id''),
 145     (''$tissue_sample_index_number_cvterm_id''),
 146     (''$plot_number_cvterm_id''),
 147     (''$plot_geo_json_cvterm_id''),
 148     (''$range_cvterm_id''),
 149     (''$replicate_cvterm_id''),
 150     (''$row_number_cvterm_id''),
 151     (''$synonym_cvterm_id''),
 152     (''$T1_cvterm_id''),
 153     (''$T2_cvterm_id''),
 154     (''$transgenic_cvterm_id''),
 155     (''$variety_cvterm_id''),
 156     (''$notes_cvterm_id''),
 157     (''$state_cvterm_id''),
 158     (''$accession_number_cvterm_id''),
 159     (''$PUI_cvterm_id''),
 160     (''$donor_cvterm_id''),
 161     (''$donor_institute_cvterm_id''),
 162     (''$donor_PUI_cvterm_id''),
 163     (''$seed_source_cvterm_id''),
 164     (''$institute_code_cvterm_id''),
 165     (''$institute_name_cvterm_id''),
 166     (''$biological_status_cvterm_id''),
 167     (''$country_cvterm_id''),
 168     (''$germplasm_storage_cvterm_id''),
 169     (''$entry_number_cvterm_id''),
 170     (''$acquisition_date_cvterm_id''),
 171     (''$current_count_cvterm_id''),
 172     (''$current_weight_gram_cvterm_id''),
 173     (''$crossing_metadata_json_cvterm_id''),
 174     (''$ploidy_level_cvterm_id''),
 175     (''$genome_structure_cvterm_id''),
 176     (''$introgression_parent_cvterm_id''),
 177     (''$introgression_backcross_parent_cvterm_id''),
 178     (''$introgression_map_version_cvterm_id''),
 179     (''$introgression_chromosome_cvterm_id''),
 180     (''$introgression_start_position_bp_cvterm_id''),
 181     (''$introgression_end_position_bp_cvterm_id''),
 182     (''$is_blank_cvterm_id''),
 183     (''$concentration_cvterm_id''),
 184     (''$volume_cvterm_id''),
 185     (''$extraction_cvterm_id''),
 186     (''$dna_person_cvterm_id''),
 187     (''$tissue_type_cvterm_id''),
 188     (''$seedlot_quality_cvterm_id''),
 189     (''$ncbi_taxonomy_id_cvterm_id'')) AS t (type_id);'
 190 )
 191 AS (stock_id int,
 192     "uniquename" text,
 193     "stock_type_id" int,
 194     "stock_type_name" text,
 195     "organism_id" int,
 196     "block" json,
 197     "col_number" jsonb,
 198     "igd_synonym" jsonb,
 199     "is a control" jsonb,
 200     "location_code" jsonb,
 201     "organization" jsonb,
 202     "plant_index_number" jsonb,
 203     "subplot_index_number" jsonb,
 204     "tissue_sample_index_number" jsonb,
 205     "plot number" jsonb,
 206     "plot_geo_json" jsonb,
 207     "range" jsonb,
 208     "replicate" jsonb,
 209     "row_number" jsonb,
 210     "stock_synonym" jsonb,
 211     "T1" jsonb,
 212     "T2" jsonb,
 213     "transgenic" jsonb,
 214     "variety" jsonb,
 215     "notes" jsonb,
 216     "state" jsonb,
 217     "accession number" jsonb,
 218     "PUI" jsonb,
 219     "donor" jsonb,
 220     "donor institute" jsonb,
 221     "donor PUI" jsonb,
 222     "seed source" jsonb,
 223     "institute code" jsonb,
 224     "institute name" jsonb,
 225     "biological status of accession code" jsonb,
 226     "country of origin" jsonb,
 227     "type of germplasm storage code" jsonb,
 228     "entry number" jsonb,
 229     "acquisition date" jsonb,
 230     "current_count" jsonb,
 231     "current_weight_gram" jsonb,
 232     "crossing_metadata_jsonb" jsonb,
 233     "ploidy_level" jsonb,
 234     "genome_structure" jsonb,
 235     "introgression_parent" jsonb,
 236     "introgression_backcross_parent" jsonb,
 237     "introgression_map_version" jsonb,
 238     "introgression_chromosome" jsonb,
 239     "introgression_start_position_bp" jsonb,
 240     "introgression_end_position_bp" jsonb,
 241     "is_blank" jsonb,
 242     "concentration" jsonb,
 243     "volume" jsonb,
 244     "extraction" jsonb,
 245     "dna_person" jsonb,
 246     "tissue_type" jsonb,
 247     "seedlot_quality" jsonb,
 248     "ncbi_taxonomy_id" jsonb
 249 );
 250 CREATE UNIQUE INDEX materialized_stockprop_stock_idx ON public.materialized_stockprop(stock_id) WITH (fillfactor=100);
 251 ALTER MATERIALIZED VIEW public.materialized_stockprop OWNER TO web_usr;
 252
 253 CREATE OR REPLACE FUNCTION public.refresh_materialized_stockprop() RETURNS VOID AS '
 254 REFRESH MATERIALIZED VIEW public.materialized_stockprop;'
 255     LANGUAGE SQL;
 256
 257 ALTER FUNCTION public.refresh_materialized_stockprop() OWNER TO web_usr;
 258
 259 CREATE OR REPLACE FUNCTION public.refresh_materialized_stockprop_concurrently() RETURNS VOID AS '
 260 REFRESH MATERIALIZED VIEW CONCURRENTLY public.materialized_stockprop;'
 261     LANGUAGE SQL;
 262
 263 ALTER FUNCTION public.refresh_materialized_stockprop_concurrently() OWNER TO web_usr;
 264 --
 265
 266 EOSQL
 267
 268
 269
 270
 271     print STDERR "Done!\n";
 272 }
 273
 274
 275 ####
 276 1; #
 277 ####