db/00167/AddVectorStockPropsToMatviewStockprop2.pm

   1 #!/usr/bin/env perl
   2
   3 =head1 NAME
   4
   5  AddVectorStockPropsToMatviewStockprop2.pm
   6
   7 =head1 SYNOPSIS
   8
   9 mx-run AddVectorStockPropsToMatviewStockprop2.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 AddVectorStockPropsToMatviewStockprop2;
  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 vectors.');
  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     my $plant_marker_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'PlantAntibioticResistantMarker', 'stock_property')->cvterm_id();
 136     my $bacterial_maker_cvterm_id = SGN::Model::Cvterm->get_cvterm_row($schema, 'BacterialResistantMarker', 'stock_property')->cvterm_id();
 137
 138     $self->dbh->do(<<EOSQL);
 139 --do your SQL here
 140
 141 DROP EXTENSION IF EXISTS tablefunc CASCADE;
 142 CREATE EXTENSION tablefunc;
 143
 144 DROP MATERIALIZED VIEW IF EXISTS public.materialized_stockprop CASCADE;
 145 CREATE MATERIALIZED VIEW public.materialized_stockprop AS
 146 SELECT *
 147 FROM crosstab(
 148   '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',
 149   'SELECT type_id FROM (VALUES
 150     (''$block_cvterm_id''),
 151     (''$col_number_cvterm_id''),
 152     (''$igd_synonym_cvterm_id''),
 153     (''$is_a_control_cvterm_id''),
 154     (''$location_code_cvterm_id''),
 155     (''$organization_cvterm_id''),
 156     (''$plant_index_number_cvterm_id''),
 157     (''$subplot_index_number_cvterm_id''),
 158     (''$tissue_sample_index_number_cvterm_id''),
 159     (''$plot_number_cvterm_id''),
 160     (''$plot_geo_json_cvterm_id''),
 161     (''$range_cvterm_id''),
 162     (''$replicate_cvterm_id''),
 163     (''$row_number_cvterm_id''),
 164     (''$synonym_cvterm_id''),
 165     (''$T1_cvterm_id''),
 166     (''$T2_cvterm_id''),
 167     (''$transgenic_cvterm_id''),
 168     (''$variety_cvterm_id''),
 169     (''$notes_cvterm_id''),
 170     (''$state_cvterm_id''),
 171     (''$accession_number_cvterm_id''),
 172     (''$PUI_cvterm_id''),
 173     (''$donor_cvterm_id''),
 174     (''$donor_institute_cvterm_id''),
 175     (''$donor_PUI_cvterm_id''),
 176     (''$seed_source_cvterm_id''),
 177     (''$institute_code_cvterm_id''),
 178     (''$institute_name_cvterm_id''),
 179     (''$biological_status_cvterm_id''),
 180     (''$country_cvterm_id''),
 181     (''$germplasm_storage_cvterm_id''),
 182     (''$entry_number_cvterm_id''),
 183     (''$acquisition_date_cvterm_id''),
 184     (''$current_count_cvterm_id''),
 185     (''$current_weight_gram_cvterm_id''),
 186     (''$crossing_metadata_json_cvterm_id''),
 187     (''$ploidy_level_cvterm_id''),
 188     (''$genome_structure_cvterm_id''),
 189     (''$introgression_parent_cvterm_id''),
 190     (''$introgression_backcross_parent_cvterm_id''),
 191     (''$introgression_map_version_cvterm_id''),
 192     (''$introgression_chromosome_cvterm_id''),
 193     (''$introgression_start_position_bp_cvterm_id''),
 194     (''$introgression_end_position_bp_cvterm_id''),
 195     (''$is_blank_cvterm_id''),
 196     (''$concentration_cvterm_id''),
 197     (''$volume_cvterm_id''),
 198     (''$extraction_cvterm_id''),
 199     (''$dna_person_cvterm_id''),
 200     (''$tissue_type_cvterm_id''),
 201     (''$seedlot_quality_cvterm_id''),
 202     (''$selection_marker_cvterm_id''),
 203     (''$cloning_organism_cvterm_id''),
 204     (''$cassette_name_cvterm_id''),
 205     (''$microbial_strain_cvterm_id''),
 206     (''$inherent_marker_cvterm_id''),
 207     (''$backbone_cvterm_id''),
 208     (''$svector_type_cvterm_id''),
 209     (''$gene_id_cvterm_id''),
 210     (''$promotors_cvterm_id''),
 211     (''$terminators_type_cvterm_id''),
 212     (''$plant_marker_cvterm_id''),
 213     (''$bacterial_maker_cvterm_id''),
 214     (''$ncbi_taxonomy_id_cvterm_id'')) AS t (type_id);'
 215
 216 )
 217 AS (stock_id int,
 218     "uniquename" text,
 219     "stock_type_id" int,
 220     "stock_type_name" text,
 221     "organism_id" int,
 222     "block" json,
 223     "col_number" jsonb,
 224     "igd_synonym" jsonb,
 225     "is a control" jsonb,
 226     "location_code" jsonb,
 227     "organization" jsonb,
 228     "plant_index_number" jsonb,
 229     "subplot_index_number" jsonb,
 230     "tissue_sample_index_number" jsonb,
 231     "plot number" jsonb,
 232     "plot_geo_json" jsonb,
 233     "range" jsonb,
 234     "replicate" jsonb,
 235     "row_number" jsonb,
 236     "stock_synonym" jsonb,
 237     "T1" jsonb,
 238     "T2" jsonb,
 239     "transgenic" jsonb,
 240     "variety" jsonb,
 241     "notes" jsonb,
 242     "state" jsonb,
 243     "accession number" jsonb,
 244     "PUI" jsonb,
 245     "donor" jsonb,
 246     "donor institute" jsonb,
 247     "donor PUI" jsonb,
 248     "seed source" jsonb,
 249     "institute code" jsonb,
 250     "institute name" jsonb,
 251     "biological status of accession code" jsonb,
 252     "country of origin" jsonb,
 253     "type of germplasm storage code" jsonb,
 254     "entry number" jsonb,
 255     "acquisition date" jsonb,
 256     "current_count" jsonb,
 257     "current_weight_gram" jsonb,
 258     "crossing_metadata_jsonb" jsonb,
 259     "ploidy_level" jsonb,
 260     "genome_structure" jsonb,
 261     "introgression_parent" jsonb,
 262     "introgression_backcross_parent" jsonb,
 263     "introgression_map_version" jsonb,
 264     "introgression_chromosome" jsonb,
 265     "introgression_start_position_bp" jsonb,
 266     "introgression_end_position_bp" jsonb,
 267     "is_blank" jsonb,
 268     "concentration" jsonb,
 269     "volume" jsonb,
 270     "extraction" jsonb,
 271     "dna_person" jsonb,
 272     "tissue_type" jsonb,
 273     "seedlot_quality" jsonb,
 274     "SelectionMarker" jsonb,
 275     "CloningOrganism" jsonb,
 276     "CassetteName" jsonb,
 277     "Strain" jsonb,
 278     "InherentMarker" jsonb,
 279     "Backbone" jsonb,
 280     "VectorType" jsonb,
 281     "Gene" jsonb,
 282     "Promotors" jsonb,
 283     "Terminators" jsonb,
 284     "PlantAntibioticResistantMarker" jsonb,
 285     "BacterialResistantMarker" jsonb,
 286     "ncbi_taxonomy_id" jsonb
 287 );
 288 CREATE UNIQUE INDEX materialized_stockprop_stock_idx ON public.materialized_stockprop(stock_id) WITH (fillfactor=100);
 289 ALTER MATERIALIZED VIEW public.materialized_stockprop OWNER TO web_usr;
 290
 291 CREATE OR REPLACE FUNCTION public.refresh_materialized_stockprop() RETURNS VOID AS '
 292 REFRESH MATERIALIZED VIEW public.materialized_stockprop;'
 293     LANGUAGE SQL;
 294
 295 ALTER FUNCTION public.refresh_materialized_stockprop() OWNER TO web_usr;
 296
 297 CREATE OR REPLACE FUNCTION public.refresh_materialized_stockprop_concurrently() RETURNS VOID AS '
 298 REFRESH MATERIALIZED VIEW CONCURRENTLY public.materialized_stockprop;'
 299     LANGUAGE SQL;
 300
 301 ALTER FUNCTION public.refresh_materialized_stockprop_concurrently() OWNER TO web_usr;
 302 --
 303
 304 EOSQL
 305
 306
 307
 308
 309     print STDERR "Done!\n";
 310 }
 311
 312
 313 ####
 314 1; #
 315 ####