db/00090/AddStockPropViews.pm

   1 #!/usr/bin/env perl
   2
   3 =head1 NAME
   4
   5 AddStockPropViews.pm
   6
   7 =head1 SYNOPSIS
   8
   9 mx-run AddStockPropViews [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 nm529@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 AddStockPropViews;
  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 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 $crossing_metadata_json_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'crossing_metadata_json', 'stock_property')->cvterm_id();
  90     my $ploidy_level_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'ploidy_level', 'stock_property')->cvterm_id();
  91     my $genome_structure_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'genome_structure', 'stock_property')->cvterm_id();
  92     my $introgression_parent_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'introgression_parent', 'stock_property')->cvterm_id();
  93     my $introgression_backcross_parent_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'introgression_backcross_parent', 'stock_property')->cvterm_id();
  94     my $introgression_map_version_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'introgression_map_version', 'stock_property')->cvterm_id();
  95     my $introgression_chromosome_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'introgression_chromosome', 'stock_property')->cvterm_id();
  96     my $introgression_start_position_bp_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'introgression_start_position_bp', 'stock_property')->cvterm_id();
  97     my $introgression_end_position_bp_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'introgression_end_position_bp', 'stock_property')->cvterm_id();
  98
  99     $self->dbh->do(<<EOSQL);
 100 --do your SQL here
 101
 102 DROP EXTENSION IF EXISTS tablefunc CASCADE;
 103 CREATE EXTENSION tablefunc;
 104
 105 DROP MATERIALIZED VIEW IF EXISTS public.materialized_stockprop CASCADE;
 106 CREATE MATERIALIZED VIEW public.materialized_stockprop AS
 107 SELECT *
 108 FROM crosstab(
 109   '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',
 110   'SELECT type_id FROM (VALUES
 111     (''$block_cvterm_id''),
 112     (''$col_number_cvterm_id''),
 113     (''$igd_synonym_cvterm_id''),
 114     (''$is_a_control_cvterm_id''),
 115     (''$location_code_cvterm_id''),
 116     (''$organization_cvterm_id''),
 117     (''$plant_index_number_cvterm_id''),
 118     (''$subplot_index_number_cvterm_id''),
 119     (''$tissue_sample_index_number_cvterm_id''),
 120     (''$plot_number_cvterm_id''),
 121     (''$plot_geo_json_cvterm_id''),
 122     (''$range_cvterm_id''),
 123     (''$replicate_cvterm_id''),
 124     (''$row_number_cvterm_id''),
 125     (''$synonym_cvterm_id''),
 126     (''$T1_cvterm_id''),
 127     (''$T2_cvterm_id''),
 128     (''$transgenic_cvterm_id''),
 129     (''$variety_cvterm_id''),
 130     (''$notes_cvterm_id''),
 131     (''$state_cvterm_id''),
 132     (''$accession_number_cvterm_id''),
 133     (''$PUI_cvterm_id''),
 134     (''$donor_cvterm_id''),
 135     (''$donor_institute_cvterm_id''),
 136     (''$donor_PUI_cvterm_id''),
 137     (''$seed_source_cvterm_id''),
 138     (''$institute_code_cvterm_id''),
 139     (''$institute_name_cvterm_id''),
 140     (''$biological_status_cvterm_id''),
 141     (''$country_cvterm_id''),
 142     (''$germplasm_storage_cvterm_id''),
 143     (''$entry_number_cvterm_id''),
 144     (''$acquisition_date_cvterm_id''),
 145     (''$current_count_cvterm_id''),
 146     (''$crossing_metadata_json_cvterm_id''),
 147     (''$ploidy_level_cvterm_id''),
 148     (''$genome_structure_cvterm_id''),
 149     (''$introgression_parent_cvterm_id''),
 150     (''$introgression_backcross_parent_cvterm_id''),
 151     (''$introgression_map_version_cvterm_id''),
 152     (''$introgression_chromosome_cvterm_id''),
 153     (''$introgression_start_position_bp_cvterm_id''),
 154     (''$introgression_end_position_bp_cvterm_id'') ) AS t (type_id);'
 155 )
 156 AS (stock_id int,
 157     "uniquename" text,
 158     "stock_type_id" int,
 159     "stock_type_name" text,
 160     "organism_id" int,
 161     "block" json,
 162     "col_number" jsonb,
 163     "igd_synonym" jsonb,
 164     "is a control" jsonb,
 165     "location_code" jsonb,
 166     "organization" jsonb,
 167     "plant_index_number" jsonb,
 168     "subplot_index_number" jsonb,
 169     "tissue_sample_index_number" jsonb,
 170     "plot number" jsonb,
 171     "plot_geo_json" jsonb,
 172     "range" jsonb,
 173     "replicate" jsonb,
 174     "row_number" jsonb,
 175     "stock_synonym" jsonb,
 176     "T1" jsonb,
 177     "T2" jsonb,
 178     "transgenic" jsonb,
 179     "variety" jsonb,
 180     "notes" jsonb,
 181     "state" jsonb,
 182     "accession number" jsonb,
 183     "PUI" jsonb,
 184     "donor" jsonb,
 185     "donor institute" jsonb,
 186     "donor PUI" jsonb,
 187     "seed source" jsonb,
 188     "institute code" jsonb,
 189     "institute name" jsonb,
 190     "biological status of accession code" jsonb,
 191     "country of origin" jsonb,
 192     "type of germplasm storage code" jsonb,
 193     "entry number" jsonb,
 194     "acquisition date" jsonb,
 195     "current_count" jsonb,
 196     "crossing_metadata_jsonb" jsonb,
 197     "ploidy_level" jsonb,
 198     "genome_structure" jsonb,
 199     "introgression_parent" jsonb,
 200     "introgression_backcross_parent" jsonb,
 201     "introgression_map_version" jsonb,
 202     "introgression_chromosome" jsonb,
 203     "introgression_start_position_bp" jsonb,
 204     "introgression_end_position_bp" jsonb
 205 );
 206 CREATE UNIQUE INDEX materialized_stockprop_stock_idx ON public.materialized_stockprop(stock_id) WITH (fillfactor=100);
 207 ALTER MATERIALIZED VIEW public.materialized_stockprop OWNER TO web_usr;
 208
 209 CREATE OR REPLACE FUNCTION public.refresh_materialized_stockprop() RETURNS VOID AS '
 210 REFRESH MATERIALIZED VIEW public.materialized_stockprop;'
 211     LANGUAGE SQL;
 212
 213 ALTER FUNCTION public.refresh_materialized_stockprop() OWNER TO web_usr;
 214
 215 CREATE OR REPLACE FUNCTION public.refresh_materialized_stockprop_concurrently() RETURNS VOID AS '
 216 REFRESH MATERIALIZED VIEW CONCURRENTLY public.materialized_stockprop;'
 217     LANGUAGE SQL;
 218
 219 ALTER FUNCTION public.refresh_materialized_stockprop_concurrently() OWNER TO web_usr;
 220 --
 221
 222 EOSQL
 223
 224 print "You're done!\n";
 225 }
 226
 227
 228 ####
 229 1; #
 230 ####