db/00092/UpgradeGenotypeStorage.pm

   1 #!/usr/bin/env perl
   2
   3 =head1 NAME
   4
   5 UpgradeGenotypeStorage.pm
   6
   7 =head1 SYNOPSIS
   8
   9 mx-run UpgradeGenotypeStorage [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 converts genotypeprop values to jsonb, and fixes genotype related materialized views
  17
  18 =head1 AUTHOR
  19
  20 Bryan Ellerbrock<bje24@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 UpgradeGenotypeStorage;
  33
  34 use Moose;
  35 use CXGN::BreederSearch;
  36 use Try::Tiny;
  37 extends 'CXGN::Metadata::Dbpatch';
  38
  39
  40 has '+description' => ( default => <<'' );
  41 This patch converts genotypeprop values to jsonb, and fixes genotype related materialized views.
  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     $self->dbh->do(<<EOSQL);
  53
  54 --do your SQL here
  55
  56 DROP MATERIALIZED VIEW IF EXISTS public.materialized_genoview CASCADE;
  57
  58 UPDATE genotypeprop SET value = '{"igd number":"' || value || '"}' WHERE type_id = (SELECT cvterm_id FROM cvterm WHERE name = 'igd number');
  59 ALTER TABLE genotypeprop ALTER COLUMN value TYPE JSONB USING value::JSON;
  60
  61 CREATE MATERIALIZED VIEW public.materialized_genoview AS
  62  SELECT stock.stock_id AS accession_id,
  63     nd_experiment_protocol.nd_protocol_id AS genotyping_protocol_id,
  64     nd_experiment_genotype.genotype_id AS genotype_id
  65    FROM stock
  66      LEFT JOIN nd_experiment_stock ON stock.stock_id = nd_experiment_stock.stock_id
  67      LEFT JOIN nd_experiment_protocol ON nd_experiment_stock.nd_experiment_id = nd_experiment_protocol.nd_experiment_id
  68      LEFT JOIN nd_protocol ON nd_experiment_protocol.nd_protocol_id = nd_protocol.nd_protocol_id
  69      LEFT JOIN nd_experiment_genotype ON nd_experiment_stock.nd_experiment_id = nd_experiment_genotype.nd_experiment_id
  70   WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'accession')
  71   GROUP BY 1,2,3
  72   WITH DATA;
  73
  74 CREATE UNIQUE INDEX unq_geno_idx ON public.materialized_genoview(accession_id,genotype_id) WITH (fillfactor=100);
  75 CREATE INDEX accession_id_geno_idx ON public.materialized_genoview(accession_id) WITH (fillfactor=100);
  76 CREATE INDEX genotyping_protocol_id_idx ON public.materialized_genoview(genotyping_protocol_id) WITH (fillfactor=100);
  77 CREATE INDEX genotype_id_idx ON public.materialized_genoview(genotype_id) WITH (fillfactor=100);
  78 ALTER MATERIALIZED VIEW materialized_genoview OWNER TO web_usr;
  79
  80 CREATE MATERIALIZED VIEW public.genotyping_protocolsXseedlots AS
  81 SELECT public.materialized_genoview.genotyping_protocol_id,
  82 public.stock.stock_id AS seedlot_id
  83 FROM public.materialized_genoview
  84 LEFT JOIN stock_relationship seedlot_relationship ON materialized_genoview.accession_id = seedlot_relationship.subject_id AND seedlot_relationship.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'collection_of')
  85 LEFT JOIN stock ON seedlot_relationship.object_id = stock.stock_id AND stock.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'seedlot')
  86   GROUP BY 1,2
  87 WITH DATA;
  88 CREATE UNIQUE INDEX genotyping_protocolsXseedlots_idx ON public.genotyping_protocolsXseedlots(genotyping_protocol_id, seedlot_id) WITH (fillfactor=100);
  89 ALTER MATERIALIZED VIEW genotyping_protocolsXseedlots OWNER TO web_usr;
  90
  91 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtraits AS
  92 SELECT public.materialized_genoview.genotyping_protocol_id,
  93     public.materialized_phenoview.trait_id
  94    FROM public.materialized_genoview
  95    JOIN public.materialized_phenoview USING(accession_id)
  96   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.trait_id
  97   WITH DATA;
  98 CREATE UNIQUE INDEX genotyping_protocolsXtraits_idx ON public.genotyping_protocolsXtraits(genotyping_protocol_id, trait_id) WITH (fillfactor=100);
  99 ALTER MATERIALIZED VIEW genotyping_protocolsXtraits OWNER TO web_usr;
 100
 101 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrait_components AS
 102 SELECT public.materialized_genoview.genotyping_protocol_id,
 103 trait_component.cvterm_id AS trait_component_id
 104 FROM public.materialized_genoview
 105 JOIN public.materialized_phenoview USING(accession_id)
 106 JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 107 JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 108 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 109 GROUP BY 1,2
 110 WITH DATA;
 111 CREATE UNIQUE INDEX genotyping_protocolsXtrait_components_idx ON public.genotyping_protocolsXtrait_components(genotyping_protocol_id, trait_component_id) WITH (fillfactor=100);
 112 ALTER MATERIALIZED VIEW genotyping_protocolsXtrait_components OWNER TO web_usr;
 113 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('genotyping_protocolsXtrait_components', FALSE, CURRENT_TIMESTAMP);
 114
 115 CREATE MATERIALIZED VIEW public.accessionsXgenotyping_protocols AS
 116 SELECT public.materialized_genoview.accession_id,
 117     public.materialized_genoview.genotyping_protocol_id
 118    FROM public.materialized_genoview
 119   GROUP BY public.materialized_genoview.accession_id, public.materialized_genoview.genotyping_protocol_id
 120   WITH DATA;
 121 CREATE UNIQUE INDEX accessionsXgenotyping_protocols_idx ON public.accessionsXgenotyping_protocols(accession_id, genotyping_protocol_id) WITH (fillfactor=100);
 122 ALTER MATERIALIZED VIEW accessionsXgenotyping_protocols OWNER TO web_usr;
 123
 124 CREATE MATERIALIZED VIEW public.breeding_programsXgenotyping_protocols AS
 125 SELECT public.materialized_phenoview.breeding_program_id,
 126     public.materialized_genoview.genotyping_protocol_id
 127    FROM public.materialized_phenoview
 128    JOIN public.materialized_genoview USING(accession_id)
 129   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_genoview.genotyping_protocol_id
 130 WITH DATA;
 131 CREATE UNIQUE INDEX breeding_programsXgenotyping_protocols_idx ON public.breeding_programsXgenotyping_protocols(breeding_program_id, genotyping_protocol_id) WITH (fillfactor=100);
 132 ALTER MATERIALIZED VIEW breeding_programsXgenotyping_protocols OWNER TO web_usr;
 133
 134 CREATE MATERIALIZED VIEW public.genotyping_protocolsXlocations AS
 135 SELECT public.materialized_genoview.genotyping_protocol_id,
 136     public.materialized_phenoview.location_id
 137    FROM public.materialized_genoview
 138    JOIN public.materialized_phenoview USING(accession_id)
 139   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.location_id
 140 WITH DATA;
 141 CREATE UNIQUE INDEX genotyping_protocolsXlocations_idx ON public.genotyping_protocolsXlocations(genotyping_protocol_id, location_id) WITH (fillfactor=100);
 142 ALTER MATERIALIZED VIEW genotyping_protocolsXlocations OWNER TO web_usr;
 143
 144 CREATE MATERIALIZED VIEW public.genotyping_protocolsXplants AS
 145 SELECT public.materialized_genoview.genotyping_protocol_id,
 146     public.stock.stock_id AS plant_id
 147    FROM public.materialized_genoview
 148    JOIN public.materialized_phenoview USING(accession_id)
 149    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plant'))
 150   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.stock.stock_id
 151   WITH DATA;
 152 CREATE UNIQUE INDEX genotyping_protocolsXplants_idx ON public.genotyping_protocolsXplants(genotyping_protocol_id, plant_id) WITH (fillfactor=100);
 153 ALTER MATERIALIZED VIEW genotyping_protocolsXplants OWNER TO web_usr;
 154
 155 CREATE MATERIALIZED VIEW public.genotyping_protocolsXplots AS
 156 SELECT public.materialized_genoview.genotyping_protocol_id,
 157     public.stock.stock_id AS plot_id
 158     FROM public.materialized_genoview
 159     JOIN public.materialized_phenoview USING(accession_id)
 160    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plot'))
 161   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.stock.stock_id
 162 WITH DATA;
 163 CREATE UNIQUE INDEX genotyping_protocolsXplots_idx ON public.genotyping_protocolsXplots(genotyping_protocol_id, plot_id) WITH (fillfactor=100);
 164 ALTER MATERIALIZED VIEW genotyping_protocolsXplots OWNER TO web_usr;
 165
 166 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrial_designs AS
 167 SELECT public.materialized_genoview.genotyping_protocol_id,
 168     trialdesign.value AS trial_design_id
 169     FROM public.materialized_genoview
 170     JOIN public.materialized_phenoview USING(accession_id)
 171    JOIN public.projectprop trialdesign ON materialized_phenoview.trial_id = trialdesign.project_id AND trialdesign.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'design' )
 172   GROUP BY public.materialized_genoview.genotyping_protocol_id, trialdesign.value
 173 WITH DATA;
 174 CREATE UNIQUE INDEX genotyping_protocolsXtrial_designs_idx ON public.genotyping_protocolsXtrial_designs(genotyping_protocol_id, trial_design_id) WITH (fillfactor=100);
 175 ALTER MATERIALIZED VIEW genotyping_protocolsXtrial_designs OWNER TO web_usr;
 176
 177 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrial_types AS
 178 SELECT public.materialized_genoview.genotyping_protocol_id,
 179     trialterm.cvterm_id AS trial_type_id
 180     FROM public.materialized_genoview
 181     JOIN public.materialized_phenoview USING(accession_id)
 182    JOIN projectprop trialprop ON materialized_phenoview.trial_id = trialprop.project_id AND trialprop.type_id IN (SELECT cvterm_id from cvterm JOIN cv USING(cv_id) WHERE cv.name = 'project_type' )
 183    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 184   GROUP BY public.materialized_genoview.genotyping_protocol_id, trialterm.cvterm_id
 185 WITH DATA;
 186 CREATE UNIQUE INDEX genotyping_protocolsXtrial_types_idx ON public.genotyping_protocolsXtrial_types(genotyping_protocol_id, trial_type_id) WITH (fillfactor=100);
 187 ALTER MATERIALIZED VIEW genotyping_protocolsXtrial_types OWNER TO web_usr;
 188
 189 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrials AS
 190 SELECT public.materialized_genoview.genotyping_protocol_id,
 191     public.materialized_phenoview.trial_id
 192    FROM public.materialized_genoview
 193    JOIN public.materialized_phenoview USING(accession_id)
 194   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.trial_id
 195 WITH DATA;
 196 CREATE UNIQUE INDEX genotyping_protocolsXtrials_idx ON public.genotyping_protocolsXtrials(genotyping_protocol_id, trial_id) WITH (fillfactor=100);
 197 ALTER MATERIALIZED VIEW genotyping_protocolsXtrials OWNER TO web_usr;
 198
 199 CREATE MATERIALIZED VIEW public.genotyping_protocolsXyears AS
 200 SELECT public.materialized_genoview.genotyping_protocol_id,
 201     public.materialized_phenoview.year_id
 202    FROM public.materialized_genoview
 203    JOIN public.materialized_phenoview USING(accession_id)
 204   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.year_id
 205 WITH DATA;
 206 CREATE UNIQUE INDEX genotyping_protocolsXyears_idx ON public.genotyping_protocolsXyears(genotyping_protocol_id, year_id) WITH (fillfactor=100);
 207 ALTER MATERIALIZED VIEW genotyping_protocolsXyears OWNER TO web_usr;
 208
 209 --
 210
 211 EOSQL
 212
 213 print "You're done!\n";
 214 }
 215
 216
 217 ####
 218 1; #
 219 ####