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 "\nChecking if materialized_phenoview is populated.\n";
  51
  52     try {
  53       my $populated_query = "select * from materialized_phenoview limit 1";
  54       my $sth = $self->dbh->prepare($populated_query);
  55       $sth->execute();
  56     } catch { #if test query fails because views aren't populated
  57       print STDERR "materialized_phenoview is not populated, run 'SELECT refresh_materialized_views();' in database before running this patch";
  58       return;
  59     };
  60
  61     print STDOUT "\nExecuting the SQL commands.\n";
  62
  63     $self->dbh->do(<<EOSQL);
  64
  65 --do your SQL here
  66
  67 DROP MATERIALIZED VIEW IF EXISTS public.materialized_genoview CASCADE;
  68
  69 UPDATE genotypeprop SET value = '{"igd number":"' || value || '"}' WHERE type_id = (SELECT cvterm_id FROM cvterm WHERE name = 'igd number');
  70 ALTER TABLE genotypeprop ALTER COLUMN value TYPE JSONB USING value::JSON;
  71
  72 CREATE MATERIALIZED VIEW public.materialized_genoview AS
  73  SELECT stock.stock_id AS accession_id,
  74     nd_experiment_protocol.nd_protocol_id AS genotyping_protocol_id,
  75     nd_experiment_genotype.genotype_id AS genotype_id
  76    FROM stock
  77      LEFT JOIN nd_experiment_stock ON stock.stock_id = nd_experiment_stock.stock_id
  78      LEFT JOIN nd_experiment_protocol ON nd_experiment_stock.nd_experiment_id = nd_experiment_protocol.nd_experiment_id
  79      LEFT JOIN nd_protocol ON nd_experiment_protocol.nd_protocol_id = nd_protocol.nd_protocol_id
  80      LEFT JOIN nd_experiment_genotype ON nd_experiment_stock.nd_experiment_id = nd_experiment_genotype.nd_experiment_id
  81   WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'accession')
  82   GROUP BY 1,2,3
  83   WITH DATA;
  84
  85 CREATE UNIQUE INDEX unq_geno_idx ON public.materialized_genoview(accession_id,genotype_id) WITH (fillfactor=100);
  86 CREATE INDEX accession_id_geno_idx ON public.materialized_genoview(accession_id) WITH (fillfactor=100);
  87 CREATE INDEX genotyping_protocol_id_idx ON public.materialized_genoview(genotyping_protocol_id) WITH (fillfactor=100);
  88 CREATE INDEX genotype_id_idx ON public.materialized_genoview(genotype_id) WITH (fillfactor=100);
  89 ALTER MATERIALIZED VIEW materialized_genoview OWNER TO web_usr;
  90
  91 CREATE MATERIALIZED VIEW public.genotyping_protocolsXseedlots AS
  92 SELECT public.materialized_genoview.genotyping_protocol_id,
  93 public.stock.stock_id AS seedlot_id
  94 FROM public.materialized_genoview
  95 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')
  96 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')
  97   GROUP BY 1,2
  98 WITH DATA;
  99 CREATE UNIQUE INDEX genotyping_protocolsXseedlots_idx ON public.genotyping_protocolsXseedlots(genotyping_protocol_id, seedlot_id) WITH (fillfactor=100);
 100 ALTER MATERIALIZED VIEW genotyping_protocolsXseedlots OWNER TO web_usr;
 101
 102 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtraits AS
 103 SELECT public.materialized_genoview.genotyping_protocol_id,
 104     public.materialized_phenoview.trait_id
 105    FROM public.materialized_genoview
 106    JOIN public.materialized_phenoview USING(accession_id)
 107   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.trait_id
 108   WITH DATA;
 109 CREATE UNIQUE INDEX genotyping_protocolsXtraits_idx ON public.genotyping_protocolsXtraits(genotyping_protocol_id, trait_id) WITH (fillfactor=100);
 110 ALTER MATERIALIZED VIEW genotyping_protocolsXtraits OWNER TO web_usr;
 111
 112 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrait_components AS
 113 SELECT public.materialized_genoview.genotyping_protocol_id,
 114 trait_component.cvterm_id AS trait_component_id
 115 FROM public.materialized_genoview
 116 JOIN public.materialized_phenoview USING(accession_id)
 117 JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 118 JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 119 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 120 GROUP BY 1,2
 121 WITH DATA;
 122 CREATE UNIQUE INDEX genotyping_protocolsXtrait_components_idx ON public.genotyping_protocolsXtrait_components(genotyping_protocol_id, trait_component_id) WITH (fillfactor=100);
 123 ALTER MATERIALIZED VIEW genotyping_protocolsXtrait_components OWNER TO web_usr;
 124 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('genotyping_protocolsXtrait_components', FALSE, CURRENT_TIMESTAMP);
 125
 126 CREATE MATERIALIZED VIEW public.accessionsXgenotyping_protocols AS
 127 SELECT public.materialized_genoview.accession_id,
 128     public.materialized_genoview.genotyping_protocol_id
 129    FROM public.materialized_genoview
 130   GROUP BY public.materialized_genoview.accession_id, public.materialized_genoview.genotyping_protocol_id
 131   WITH DATA;
 132 CREATE UNIQUE INDEX accessionsXgenotyping_protocols_idx ON public.accessionsXgenotyping_protocols(accession_id, genotyping_protocol_id) WITH (fillfactor=100);
 133 ALTER MATERIALIZED VIEW accessionsXgenotyping_protocols OWNER TO web_usr;
 134
 135 CREATE MATERIALIZED VIEW public.breeding_programsXgenotyping_protocols AS
 136 SELECT public.materialized_phenoview.breeding_program_id,
 137     public.materialized_genoview.genotyping_protocol_id
 138    FROM public.materialized_phenoview
 139    JOIN public.materialized_genoview USING(accession_id)
 140   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_genoview.genotyping_protocol_id
 141 WITH DATA;
 142 CREATE UNIQUE INDEX breeding_programsXgenotyping_protocols_idx ON public.breeding_programsXgenotyping_protocols(breeding_program_id, genotyping_protocol_id) WITH (fillfactor=100);
 143 ALTER MATERIALIZED VIEW breeding_programsXgenotyping_protocols OWNER TO web_usr;
 144
 145 CREATE MATERIALIZED VIEW public.genotyping_protocolsXlocations AS
 146 SELECT public.materialized_genoview.genotyping_protocol_id,
 147     public.materialized_phenoview.location_id
 148    FROM public.materialized_genoview
 149    JOIN public.materialized_phenoview USING(accession_id)
 150   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.location_id
 151 WITH DATA;
 152 CREATE UNIQUE INDEX genotyping_protocolsXlocations_idx ON public.genotyping_protocolsXlocations(genotyping_protocol_id, location_id) WITH (fillfactor=100);
 153 ALTER MATERIALIZED VIEW genotyping_protocolsXlocations OWNER TO web_usr;
 154
 155 CREATE MATERIALIZED VIEW public.genotyping_protocolsXplants AS
 156 SELECT public.materialized_genoview.genotyping_protocol_id,
 157     public.stock.stock_id AS plant_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 = 'plant'))
 161   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.stock.stock_id
 162   WITH DATA;
 163 CREATE UNIQUE INDEX genotyping_protocolsXplants_idx ON public.genotyping_protocolsXplants(genotyping_protocol_id, plant_id) WITH (fillfactor=100);
 164 ALTER MATERIALIZED VIEW genotyping_protocolsXplants OWNER TO web_usr;
 165
 166 CREATE MATERIALIZED VIEW public.genotyping_protocolsXplots AS
 167 SELECT public.materialized_genoview.genotyping_protocol_id,
 168     public.stock.stock_id AS plot_id
 169     FROM public.materialized_genoview
 170     JOIN public.materialized_phenoview USING(accession_id)
 171    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'))
 172   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.stock.stock_id
 173 WITH DATA;
 174 CREATE UNIQUE INDEX genotyping_protocolsXplots_idx ON public.genotyping_protocolsXplots(genotyping_protocol_id, plot_id) WITH (fillfactor=100);
 175 ALTER MATERIALIZED VIEW genotyping_protocolsXplots OWNER TO web_usr;
 176
 177 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrial_designs AS
 178 SELECT public.materialized_genoview.genotyping_protocol_id,
 179     trialdesign.value AS trial_design_id
 180     FROM public.materialized_genoview
 181     JOIN public.materialized_phenoview USING(accession_id)
 182    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' )
 183   GROUP BY public.materialized_genoview.genotyping_protocol_id, trialdesign.value
 184 WITH DATA;
 185 CREATE UNIQUE INDEX genotyping_protocolsXtrial_designs_idx ON public.genotyping_protocolsXtrial_designs(genotyping_protocol_id, trial_design_id) WITH (fillfactor=100);
 186 ALTER MATERIALIZED VIEW genotyping_protocolsXtrial_designs OWNER TO web_usr;
 187
 188 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrial_types AS
 189 SELECT public.materialized_genoview.genotyping_protocol_id,
 190     trialterm.cvterm_id AS trial_type_id
 191     FROM public.materialized_genoview
 192     JOIN public.materialized_phenoview USING(accession_id)
 193    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' )
 194    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 195   GROUP BY public.materialized_genoview.genotyping_protocol_id, trialterm.cvterm_id
 196 WITH DATA;
 197 CREATE UNIQUE INDEX genotyping_protocolsXtrial_types_idx ON public.genotyping_protocolsXtrial_types(genotyping_protocol_id, trial_type_id) WITH (fillfactor=100);
 198 ALTER MATERIALIZED VIEW genotyping_protocolsXtrial_types OWNER TO web_usr;
 199
 200 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrials AS
 201 SELECT public.materialized_genoview.genotyping_protocol_id,
 202     public.materialized_phenoview.trial_id
 203    FROM public.materialized_genoview
 204    JOIN public.materialized_phenoview USING(accession_id)
 205   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.trial_id
 206 WITH DATA;
 207 CREATE UNIQUE INDEX genotyping_protocolsXtrials_idx ON public.genotyping_protocolsXtrials(genotyping_protocol_id, trial_id) WITH (fillfactor=100);
 208 ALTER MATERIALIZED VIEW genotyping_protocolsXtrials OWNER TO web_usr;
 209
 210 CREATE MATERIALIZED VIEW public.genotyping_protocolsXyears AS
 211 SELECT public.materialized_genoview.genotyping_protocol_id,
 212     public.materialized_phenoview.year_id
 213    FROM public.materialized_genoview
 214    JOIN public.materialized_phenoview USING(accession_id)
 215   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.year_id
 216 WITH DATA;
 217 CREATE UNIQUE INDEX genotyping_protocolsXyears_idx ON public.genotyping_protocolsXyears(genotyping_protocol_id, year_id) WITH (fillfactor=100);
 218 ALTER MATERIALIZED VIEW genotyping_protocolsXyears OWNER TO web_usr;
 219
 220 --
 221
 222 EOSQL
 223
 224 print "You're done!\n";
 225 }
 226
 227
 228 ####
 229 1; #
 230 ####