db/00165/AddVectorStockPropsToMatviewStockprop.pm

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