db/00112/UpdateMaterializedViewsTissueSamplePhenotypingGenotyping.pm

   1 #!/usr/bin/env perl
   2
   3 =head1 NAME
   4
   5 UpdateMaterializedViewsTissueSamplePhenotypingGenotyping.pm
   6
   7 =head1 SYNOPSIS
   8
   9 mx-run UpdateMaterializedViewsTissueSamplePhenotypingGenotyping [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 updates materialized views so that the genotype materialized view includes tissue samples that were genotyped
  17
  18 =head1 AUTHOR
  19
  20 Nicolas Morales<nm529@cornell.edu>
  21 Bryan Ellerbrock<bje24@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 UpdateMaterializedViewsTissueSamplePhenotypingGenotyping;
  34
  35 use Moose;
  36 use CXGN::BreederSearch;
  37 use Try::Tiny;
  38 extends 'CXGN::Metadata::Dbpatch';
  39
  40
  41 has '+description' => ( default => <<'' );
  42 This patch updates materialized views so that the genotype materialized view includes tissue samples that were genotyped
  43
  44 sub patch {
  45     my $self=shift;
  46
  47     print STDOUT "Executing the patch:\n " .   $self->name . ".\n\nDescription:\n  ".  $self->description . ".\n\nExecuted by:\n " .  $self->username . " .";
  48
  49     print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
  50
  51     print STDOUT "\nExecuting the SQL commands.\n";
  52
  53     $self->dbh->do(<<EOSQL);
  54
  55 --do your SQL here
  56
  57 -- REDEFINE materialized_genoview, adding tissue samples
  58
  59 DROP MATERIALIZED VIEW IF EXISTS public.materialized_genoview CASCADE;
  60 CREATE MATERIALIZED VIEW public.materialized_genoview AS
  61  SELECT COALESCE (stock_relationship.object_id, accession.stock_id) AS accession_id,
  62     COALESCE (stock_type.name, 'accession') AS stock_type,
  63     COALESCE (nd_experiment_protocol.nd_protocol_id, nd_experiment_protocol_accession.nd_protocol_id) AS genotyping_protocol_id,
  64     COALESCE (nd_experiment_genotype.genotype_id, nd_experiment_genotype_accession.genotype_id) AS genotype_id
  65    FROM stock AS accession
  66      LEFT JOIN stock_relationship ON accession.stock_id = stock_relationship.object_id AND stock_relationship.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'tissue_sample_of')
  67      LEFT JOIN stock ON stock_relationship.subject_id = stock.stock_id AND stock.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'tissue_sample')
  68      LEFT JOIN cvterm AS stock_type ON (stock_type.cvterm_id = stock.type_id)
  69      LEFT JOIN nd_experiment_stock ON stock.stock_id = nd_experiment_stock.stock_id
  70      LEFT JOIN nd_experiment_protocol ON nd_experiment_stock.nd_experiment_id = nd_experiment_protocol.nd_experiment_id
  71      LEFT JOIN nd_protocol ON nd_experiment_protocol.nd_protocol_id = nd_protocol.nd_protocol_id
  72      LEFT JOIN nd_experiment_genotype ON nd_experiment_stock.nd_experiment_id = nd_experiment_genotype.nd_experiment_id
  73      LEFT JOIN nd_experiment_stock AS nd_experiment_stock_accession ON accession.stock_id = nd_experiment_stock_accession.stock_id
  74      LEFT JOIN nd_experiment_protocol AS nd_experiment_protocol_accession ON nd_experiment_stock_accession.nd_experiment_id = nd_experiment_protocol_accession.nd_experiment_id
  75      LEFT JOIN nd_protocol AS nd_protocol_accession ON nd_experiment_protocol_accession.nd_protocol_id = nd_protocol_accession.nd_protocol_id
  76      LEFT JOIN nd_experiment_genotype AS nd_experiment_genotype_accession ON nd_experiment_stock_accession.nd_experiment_id = nd_experiment_genotype_accession.nd_experiment_id
  77   WHERE accession.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'accession') AND (nd_experiment_genotype.genotype_id IS NOT NULL OR nd_experiment_genotype_accession.genotype_id IS NOT NULL)
  78   GROUP BY 1,2,3,4
  79   WITH DATA;
  80
  81 CREATE UNIQUE INDEX unq_geno_idx ON public.materialized_genoview(accession_id,genotype_id) WITH (fillfactor=100);
  82 CREATE INDEX accession_id_geno_idx ON public.materialized_genoview(accession_id) WITH (fillfactor=100);
  83 CREATE INDEX genotyping_protocol_id_idx ON public.materialized_genoview(genotyping_protocol_id) WITH (fillfactor=100);
  84 CREATE INDEX genotype_id_idx ON public.materialized_genoview(genotype_id) WITH (fillfactor=100);
  85 ALTER MATERIALIZED VIEW materialized_genoview OWNER TO web_usr;
  86
  87 CREATE MATERIALIZED VIEW public.genotyping_protocolsXseedlots AS
  88 SELECT public.materialized_genoview.genotyping_protocol_id,
  89 public.stock.stock_id AS seedlot_id
  90 FROM public.materialized_genoview
  91 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')
  92 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')
  93   GROUP BY 1,2
  94 WITH DATA;
  95 CREATE UNIQUE INDEX genotyping_protocolsXseedlots_idx ON public.genotyping_protocolsXseedlots(genotyping_protocol_id, seedlot_id) WITH (fillfactor=100);
  96 ALTER MATERIALIZED VIEW genotyping_protocolsXseedlots OWNER TO web_usr;
  97
  98 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtraits AS
  99 SELECT public.materialized_genoview.genotyping_protocol_id,
 100     public.materialized_phenoview.trait_id
 101    FROM public.materialized_genoview
 102    JOIN public.materialized_phenoview USING(accession_id)
 103   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.trait_id
 104   WITH DATA;
 105 CREATE UNIQUE INDEX genotyping_protocolsXtraits_idx ON public.genotyping_protocolsXtraits(genotyping_protocol_id, trait_id) WITH (fillfactor=100);
 106 ALTER MATERIALIZED VIEW genotyping_protocolsXtraits OWNER TO web_usr;
 107
 108 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrait_components AS
 109 SELECT public.materialized_genoview.genotyping_protocol_id,
 110 trait_component.cvterm_id AS trait_component_id
 111 FROM public.materialized_genoview
 112 JOIN public.materialized_phenoview USING(accession_id)
 113 JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 114 JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 115 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 116 GROUP BY 1,2
 117 WITH DATA;
 118 CREATE UNIQUE INDEX genotyping_protocolsXtrait_components_idx ON public.genotyping_protocolsXtrait_components(genotyping_protocol_id, trait_component_id) WITH (fillfactor=100);
 119 ALTER MATERIALIZED VIEW genotyping_protocolsXtrait_components OWNER TO web_usr;
 120 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('genotyping_protocolsXtrait_components', FALSE, CURRENT_TIMESTAMP);
 121
 122 CREATE MATERIALIZED VIEW public.accessionsXgenotyping_protocols AS
 123 SELECT public.materialized_genoview.accession_id,
 124     public.materialized_genoview.genotyping_protocol_id
 125    FROM public.materialized_genoview
 126   GROUP BY public.materialized_genoview.accession_id, public.materialized_genoview.genotyping_protocol_id
 127   WITH DATA;
 128 CREATE UNIQUE INDEX accessionsXgenotyping_protocols_idx ON public.accessionsXgenotyping_protocols(accession_id, genotyping_protocol_id) WITH (fillfactor=100);
 129 ALTER MATERIALIZED VIEW accessionsXgenotyping_protocols OWNER TO web_usr;
 130
 131 CREATE MATERIALIZED VIEW public.breeding_programsXgenotyping_protocols AS
 132 SELECT public.materialized_phenoview.breeding_program_id,
 133     public.materialized_genoview.genotyping_protocol_id
 134    FROM public.materialized_phenoview
 135    JOIN public.materialized_genoview USING(accession_id)
 136   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_genoview.genotyping_protocol_id
 137 WITH DATA;
 138 CREATE UNIQUE INDEX breeding_programsXgenotyping_protocols_idx ON public.breeding_programsXgenotyping_protocols(breeding_program_id, genotyping_protocol_id) WITH (fillfactor=100);
 139 ALTER MATERIALIZED VIEW breeding_programsXgenotyping_protocols OWNER TO web_usr;
 140
 141 CREATE MATERIALIZED VIEW public.genotyping_protocolsXlocations AS
 142 SELECT public.materialized_genoview.genotyping_protocol_id,
 143     public.materialized_phenoview.location_id
 144    FROM public.materialized_genoview
 145    JOIN public.materialized_phenoview USING(accession_id)
 146   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.location_id
 147 WITH DATA;
 148 CREATE UNIQUE INDEX genotyping_protocolsXlocations_idx ON public.genotyping_protocolsXlocations(genotyping_protocol_id, location_id) WITH (fillfactor=100);
 149 ALTER MATERIALIZED VIEW genotyping_protocolsXlocations OWNER TO web_usr;
 150
 151 CREATE MATERIALIZED VIEW public.genotyping_protocolsXplants AS
 152 SELECT public.materialized_genoview.genotyping_protocol_id,
 153     public.stock.stock_id AS plant_id
 154    FROM public.materialized_genoview
 155    JOIN public.materialized_phenoview USING(accession_id)
 156    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'))
 157   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.stock.stock_id
 158   WITH DATA;
 159 CREATE UNIQUE INDEX genotyping_protocolsXplants_idx ON public.genotyping_protocolsXplants(genotyping_protocol_id, plant_id) WITH (fillfactor=100);
 160 ALTER MATERIALIZED VIEW genotyping_protocolsXplants OWNER TO web_usr;
 161
 162 CREATE MATERIALIZED VIEW public.genotyping_protocolsXplots AS
 163 SELECT public.materialized_genoview.genotyping_protocol_id,
 164     public.stock.stock_id AS plot_id
 165     FROM public.materialized_genoview
 166     JOIN public.materialized_phenoview USING(accession_id)
 167    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'))
 168   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.stock.stock_id
 169 WITH DATA;
 170 CREATE UNIQUE INDEX genotyping_protocolsXplots_idx ON public.genotyping_protocolsXplots(genotyping_protocol_id, plot_id) WITH (fillfactor=100);
 171 ALTER MATERIALIZED VIEW genotyping_protocolsXplots OWNER TO web_usr;
 172
 173 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrial_designs AS
 174 SELECT public.materialized_genoview.genotyping_protocol_id,
 175     trialdesign.value AS trial_design_id
 176     FROM public.materialized_genoview
 177     JOIN public.materialized_phenoview USING(accession_id)
 178    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' )
 179   GROUP BY public.materialized_genoview.genotyping_protocol_id, trialdesign.value
 180 WITH DATA;
 181 CREATE UNIQUE INDEX genotyping_protocolsXtrial_designs_idx ON public.genotyping_protocolsXtrial_designs(genotyping_protocol_id, trial_design_id) WITH (fillfactor=100);
 182 ALTER MATERIALIZED VIEW genotyping_protocolsXtrial_designs OWNER TO web_usr;
 183
 184 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrial_types AS
 185 SELECT public.materialized_genoview.genotyping_protocol_id,
 186     trialterm.cvterm_id AS trial_type_id
 187     FROM public.materialized_genoview
 188     JOIN public.materialized_phenoview USING(accession_id)
 189    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' )
 190    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 191   GROUP BY public.materialized_genoview.genotyping_protocol_id, trialterm.cvterm_id
 192 WITH DATA;
 193 CREATE UNIQUE INDEX genotyping_protocolsXtrial_types_idx ON public.genotyping_protocolsXtrial_types(genotyping_protocol_id, trial_type_id) WITH (fillfactor=100);
 194 ALTER MATERIALIZED VIEW genotyping_protocolsXtrial_types OWNER TO web_usr;
 195
 196 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrials AS
 197 SELECT public.materialized_genoview.genotyping_protocol_id,
 198     public.materialized_phenoview.trial_id
 199    FROM public.materialized_genoview
 200    JOIN public.materialized_phenoview USING(accession_id)
 201   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.trial_id
 202 WITH DATA;
 203 CREATE UNIQUE INDEX genotyping_protocolsXtrials_idx ON public.genotyping_protocolsXtrials(genotyping_protocol_id, trial_id) WITH (fillfactor=100);
 204 ALTER MATERIALIZED VIEW genotyping_protocolsXtrials OWNER TO web_usr;
 205
 206 CREATE MATERIALIZED VIEW public.genotyping_protocolsXyears AS
 207 SELECT public.materialized_genoview.genotyping_protocol_id,
 208     public.materialized_phenoview.year_id
 209    FROM public.materialized_genoview
 210    JOIN public.materialized_phenoview USING(accession_id)
 211   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.year_id
 212 WITH DATA;
 213 CREATE UNIQUE INDEX genotyping_protocolsXyears_idx ON public.genotyping_protocolsXyears(genotyping_protocol_id, year_id) WITH (fillfactor=100);
 214 ALTER MATERIALIZED VIEW genotyping_protocolsXyears OWNER TO web_usr;
 215
 216 SELECT public.refresh_materialized_views_concurrently();
 217 --
 218
 219 EOSQL
 220
 221 print "You're done!\n";
 222 }
 223
 224
 225 ####
 226 1; #
 227 ####