db/00092/UpdateStockPropViews.pm

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