db/00167/MakeRankNumberStockPropViews.pm

   1 #!/usr/bin/env perl
   2
   3 =head1 NAME
   4
   5  MakeRankNumberStockPropViews.pm
   6
   7 =head1 SYNOPSIS
   8
   9 mx-run MakeRankNumberStockPropViews.pm [options] -H hostname -D dbname -u username [-F]
  10
  11 this is a subclass of L<CXGN::Metadata::Dbpatch>
  12 see the perldoc of parent class for more details.
  13
  14 =head1 DESCRIPTION
  15
  16 This patch updates the rank value in the materialized view for all stockprops
  17
  18 =head1 AUTHOR
  19
  20  tep46@cornell.edu>
  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 MakeRankNumberStockPropViews;
  33
  34 use Moose;
  35 use Bio::Chado::Schema;
  36 use SGN::Model::Cvterm;
  37
  38 extends 'CXGN::Metadata::Dbpatch';
  39
  40
  41 has '+description' => ( default => ' This patch changes the rank from being stored as a string to being stored as a number.');
  42
  43 has '+prereq' => (
  44     default => sub {
  45         [ ],
  46     },
  47 );
  48
  49 sub patch {
  50     my $self=shift;
  51
  52     print STDOUT "Executing the patch:\n " .   $self->name . ".\n\nDescription:\n  ".  $self->description . ".\n\nExecuted by:\n " .  $self->username . " .";
  53
  54     print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
  55
  56     print STDOUT "\nExecuting the SQL commands.\n";
  57
  58     my $schema = Bio::Chado::Schema->connect( sub { $self->dbh->clone } );
  59
  60     # Now re-generate the materialized view (code lifted from db patch 00165)
  61     my $block_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'block', 'stock_property')->cvterm_id();
  62     my $col_number_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'col_number', 'stock_property')->cvterm_id();
  63     my $igd_synonym_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'igd_synonym', 'stock_property')->cvterm_id();
  64     my $is_a_control_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'is a control', 'stock_property')->cvterm_id();
  65     my $location_code_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'location_code', 'stock_property')->cvterm_id();
  66     my $organization_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'organization', 'stock_property')->cvterm_id();
  67     my $plant_index_number_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'plant_index_number', 'stock_property')->cvterm_id();
  68     my $subplot_index_number_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'subplot_index_number', 'stock_property')->cvterm_id();
  69     my $tissue_sample_index_number_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'tissue_sample_index_number', 'stock_property')->cvterm_id();
  70     my $plot_number_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'plot number', 'stock_property')->cvterm_id();
  71     my $plot_geo_json_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'plot_geo_json', 'stock_property')->cvterm_id();
  72     my $range_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'range', 'stock_property')->cvterm_id();
  73     my $replicate_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'replicate', 'stock_property')->cvterm_id();
  74     my $row_number_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'row_number', 'stock_property')->cvterm_id();
  75     my $synonym_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'stock_synonym', 'stock_property')->cvterm_id();
  76     my $T1_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'T1', 'stock_property')->cvterm_id();
  77     my $T2_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'T2', 'stock_property')->cvterm_id();
  78     my $transgenic_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'transgenic', 'stock_property')->cvterm_id();
  79     my $variety_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'variety', 'stock_property')->cvterm_id();
  80     my $notes_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'notes', 'stock_property')->cvterm_id();
  81     my $state_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'state', 'stock_property')->cvterm_id();
  82     my $accession_number_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'accession number', 'stock_property')->cvterm_id();
  83     my $PUI_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'PUI', 'stock_property')->cvterm_id();
  84     my $donor_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'donor', 'stock_property')->cvterm_id();
  85     my $donor_institute_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'donor institute', 'stock_property')->cvterm_id();
  86     my $donor_PUI_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'donor PUI', 'stock_property')->cvterm_id();
  87     my $seed_source_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'seed source', 'stock_property')->cvterm_id();
  88     my $institute_code_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'institute code', 'stock_property')->cvterm_id();
  89     my $institute_name_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'institute name', 'stock_property')->cvterm_id();
  90     my $biological_status_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'biological status of accession code', 'stock_property')->cvterm_id();
  91     my $country_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'country of origin', 'stock_property')->cvterm_id();
  92     my $germplasm_storage_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'type of germplasm storage code', 'stock_property')->cvterm_id();
  93     my $entry_number_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'entry number', 'stock_property')->cvterm_id();
  94     my $acquisition_date_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'acquisition date', 'stock_property')->cvterm_id();
  95     my $current_count_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'current_count', 'stock_property')->cvterm_id();
  96     my $current_weight_gram_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'current_weight_gram', 'stock_property')->cvterm_id();
  97     my $crossing_metadata_json_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'crossing_metadata_json', 'stock_property')->cvterm_id();
  98     my $ploidy_level_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'ploidy_level', 'stock_property')->cvterm_id();
  99     my $genome_structure_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'genome_structure', 'stock_property')->cvterm_id();
 100     my $introgression_parent_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'introgression_parent', 'stock_property')->cvterm_id();
 101     my $introgression_backcross_parent_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'introgression_backcross_parent', 'stock_property')->cvterm_id();
 102     my $introgression_map_version_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'introgression_map_version', 'stock_property')->cvterm_id();
 103     my $introgression_chromosome_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'introgression_chromosome', 'stock_property')->cvterm_id();
 104     my $introgression_start_position_bp_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'introgression_start_position_bp', 'stock_property')->cvterm_id();
 105     my $introgression_end_position_bp_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'introgression_end_position_bp', 'stock_property')->cvterm_id();
 106     my $is_blank_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'is_blank', 'stock_property')->cvterm_id();
 107     my $concentration_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'concentration', 'stock_property')->cvterm_id();
 108     my $volume_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'volume', 'stock_property')->cvterm_id();
 109     my $extraction_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'extraction', 'stock_property')->cvterm_id();
 110     my $dna_person_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'dna_person', 'stock_property')->cvterm_id();
 111     my $tissue_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'tissue_type', 'stock_property')->cvterm_id();
 112     my $ncbi_taxonomy_id_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'ncbi_taxonomy_id', 'stock_property')->cvterm_id();
 113     my $seedlot_quality_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'seedlot_quality', 'stock_property')->cvterm_id();
 114
 115
 116     my $selection_marker_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'SelectionMarker', 'stock_property')->cvterm_id();
 117     my $cloning_organism_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'CloningOrganism', 'stock_property')->cvterm_id();
 118     my $cassette_name_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'CassetteName', 'stock_property')->cvterm_id();
 119     my $microbial_strain_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'Strain', 'stock_property')->cvterm_id();
 120     my $inherent_marker_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'InherentMarker', 'stock_property')->cvterm_id();
 121     my $backbone_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'Backbone', 'stock_property')->cvterm_id();
 122     my $svector_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'VectorType', 'stock_property')->cvterm_id();
 123     my $gene_id_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'Gene', 'stock_property')->cvterm_id();
 124     my $promotors_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'Promotors', 'stock_property')->cvterm_id();
 125     my $terminators_type_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'Terminators', 'stock_property')->cvterm_id();
 126
 127     $self->dbh->do(<<EOSQL);
 128 --do your SQL here
 129
 130 DROP EXTENSION IF EXISTS tablefunc CASCADE;
 131 CREATE EXTENSION tablefunc;
 132
 133 DROP MATERIALIZED VIEW IF EXISTS public.materialized_stockprop CASCADE;
 134 CREATE MATERIALIZED VIEW public.materialized_stockprop AS
 135 SELECT *
 136 FROM crosstab(
 137   'SELECT stockprop.stock_id, stock.uniquename, stock.type_id, stock_cvterm.name, stock.organism_id, stockprop.type_id, jsonb_object_agg(stockprop.value, 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',
 138   'SELECT type_id FROM (VALUES
 139     (''$block_cvterm_id''),
 140     (''$col_number_cvterm_id''),
 141     (''$igd_synonym_cvterm_id''),
 142     (''$is_a_control_cvterm_id''),
 143     (''$location_code_cvterm_id''),
 144     (''$organization_cvterm_id''),
 145     (''$plant_index_number_cvterm_id''),
 146     (''$subplot_index_number_cvterm_id''),
 147     (''$tissue_sample_index_number_cvterm_id''),
 148     (''$plot_number_cvterm_id''),
 149     (''$plot_geo_json_cvterm_id''),
 150     (''$range_cvterm_id''),
 151     (''$replicate_cvterm_id''),
 152     (''$row_number_cvterm_id''),
 153     (''$synonym_cvterm_id''),
 154     (''$T1_cvterm_id''),
 155     (''$T2_cvterm_id''),
 156     (''$transgenic_cvterm_id''),
 157     (''$variety_cvterm_id''),
 158     (''$notes_cvterm_id''),
 159     (''$state_cvterm_id''),
 160     (''$accession_number_cvterm_id''),
 161     (''$PUI_cvterm_id''),
 162     (''$donor_cvterm_id''),
 163     (''$donor_institute_cvterm_id''),
 164     (''$donor_PUI_cvterm_id''),
 165     (''$seed_source_cvterm_id''),
 166     (''$institute_code_cvterm_id''),
 167     (''$institute_name_cvterm_id''),
 168     (''$biological_status_cvterm_id''),
 169     (''$country_cvterm_id''),
 170     (''$germplasm_storage_cvterm_id''),
 171     (''$entry_number_cvterm_id''),
 172     (''$acquisition_date_cvterm_id''),
 173     (''$current_count_cvterm_id''),
 174     (''$current_weight_gram_cvterm_id''),
 175     (''$crossing_metadata_json_cvterm_id''),
 176     (''$ploidy_level_cvterm_id''),
 177     (''$genome_structure_cvterm_id''),
 178     (''$introgression_parent_cvterm_id''),
 179     (''$introgression_backcross_parent_cvterm_id''),
 180     (''$introgression_map_version_cvterm_id''),
 181     (''$introgression_chromosome_cvterm_id''),
 182     (''$introgression_start_position_bp_cvterm_id''),
 183     (''$introgression_end_position_bp_cvterm_id''),
 184     (''$is_blank_cvterm_id''),
 185     (''$concentration_cvterm_id''),
 186     (''$volume_cvterm_id''),
 187     (''$extraction_cvterm_id''),
 188     (''$dna_person_cvterm_id''),
 189     (''$tissue_type_cvterm_id''),
 190     (''$seedlot_quality_cvterm_id''),
 191     (''$selection_marker_cvterm_id''),
 192     (''$cloning_organism_cvterm_id''),
 193     (''$cassette_name_cvterm_id''),
 194     (''$microbial_strain_cvterm_id''),
 195     (''$inherent_marker_cvterm_id''),
 196     (''$backbone_cvterm_id''),
 197     (''$svector_type_cvterm_id''),
 198     (''$gene_id_cvterm_id''),
 199     (''$promotors_cvterm_id''),
 200     (''$terminators_type_cvterm_id''),
 201     (''$ncbi_taxonomy_id_cvterm_id'')) AS t (type_id);'
 202 )
 203 AS (stock_id int,
 204     "uniquename" text,
 205     "stock_type_id" int,
 206     "stock_type_name" text,
 207     "organism_id" int,
 208     "block" json,
 209     "col_number" jsonb,
 210     "igd_synonym" jsonb,
 211     "is a control" jsonb,
 212     "location_code" jsonb,
 213     "organization" jsonb,
 214     "plant_index_number" jsonb,
 215     "subplot_index_number" jsonb,
 216     "tissue_sample_index_number" jsonb,
 217     "plot number" jsonb,
 218     "plot_geo_json" jsonb,
 219     "range" jsonb,
 220     "replicate" jsonb,
 221     "row_number" jsonb,
 222     "stock_synonym" jsonb,
 223     "T1" jsonb,
 224     "T2" jsonb,
 225     "transgenic" jsonb,
 226     "variety" jsonb,
 227     "notes" jsonb,
 228     "state" jsonb,
 229     "accession number" jsonb,
 230     "PUI" jsonb,
 231     "donor" jsonb,
 232     "donor institute" jsonb,
 233     "donor PUI" jsonb,
 234     "seed source" jsonb,
 235     "institute code" jsonb,
 236     "institute name" jsonb,
 237     "biological status of accession code" jsonb,
 238     "country of origin" jsonb,
 239     "type of germplasm storage code" jsonb,
 240     "entry number" jsonb,
 241     "acquisition date" jsonb,
 242     "current_count" jsonb,
 243     "current_weight_gram" jsonb,
 244     "crossing_metadata_jsonb" jsonb,
 245     "ploidy_level" jsonb,
 246     "genome_structure" jsonb,
 247     "introgression_parent" jsonb,
 248     "introgression_backcross_parent" jsonb,
 249     "introgression_map_version" jsonb,
 250     "introgression_chromosome" jsonb,
 251     "introgression_start_position_bp" jsonb,
 252     "introgression_end_position_bp" jsonb,
 253     "is_blank" jsonb,
 254     "concentration" jsonb,
 255     "volume" jsonb,
 256     "extraction" jsonb,
 257     "dna_person" jsonb,
 258     "tissue_type" jsonb,
 259     "seedlot_quality" jsonb,
 260     "SelectionMarker" jsonb,
 261     "CloningOrganism" jsonb,
 262     "CassetteName" jsonb,
 263     "Strain" jsonb,
 264     "InherentMarker" jsonb,
 265     "Backbone" jsonb,
 266     "VectorType" jsonb,
 267     "Gene" jsonb,
 268     "Promotors" jsonb,
 269     "Terminators" jsonb,
 270     "ncbi_taxonomy_id" jsonb
 271 );
 272 CREATE UNIQUE INDEX materialized_stockprop_stock_idx ON public.materialized_stockprop(stock_id) WITH (fillfactor=100);
 273 ALTER MATERIALIZED VIEW public.materialized_stockprop OWNER TO web_usr;
 274
 275 CREATE OR REPLACE FUNCTION public.refresh_materialized_stockprop() RETURNS VOID AS '
 276 REFRESH MATERIALIZED VIEW public.materialized_stockprop;'
 277     LANGUAGE SQL;
 278
 279 ALTER FUNCTION public.refresh_materialized_stockprop() OWNER TO web_usr;
 280
 281 CREATE OR REPLACE FUNCTION public.refresh_materialized_stockprop_concurrently() RETURNS VOID AS '
 282 REFRESH MATERIALIZED VIEW CONCURRENTLY public.materialized_stockprop;'
 283     LANGUAGE SQL;
 284
 285 ALTER FUNCTION public.refresh_materialized_stockprop_concurrently() OWNER TO web_usr;
 286 --
 287
 288 EOSQL
 289
 290
 291
 292
 293     print STDERR "Done!\n";
 294 }
 295
 296
 297 ####
 298 1; #
 299 ####