db/00074/UpdateMaterializedPhenoview.pm

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6 UpdateMaterializedPhenoview.pm
   7
   8 =head1 SYNOPSIS
   9
  10 mx-run UpdateMaterializedPhenoview [options] -H hostname -D dbname -u username [-F]
  11
  12 this is a subclass of L<CXGN::Metadata::Dbpatch>
  13 see the perldoc of parent class for more details.
  14
  15 =head1 DESCRIPTION
  16
  17 This patch updates the materialized_phenoview view and the binary views that depend on it to remove unnecessary fields and better handle plants vs plots.
  18
  19 =head1 AUTHOR
  20
  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 UpdateMaterializedPhenoview;
  34
  35 use Moose;
  36 extends 'CXGN::Metadata::Dbpatch';
  37
  38
  39 has '+description' => ( default => <<'' );
  40 This patch updates the materialized_phenoview view and the binary views that depend on it to remove unnecessary fields and better handle plants vs plots.
  41
  42 sub patch {
  43     my $self=shift;
  44
  45     print STDOUT "Executing the patch:\n " .   $self->name . ".\n\nDescription:\n  ".  $self->description . ".\n\nExecuted by:\n " .  $self->username . " .";
  46
  47     print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
  48
  49     print STDOUT "\nExecuting the SQL commands.\n";
  50
  51     $self->dbh->do(<<EOSQL);
  52 --do your SQL here
  53
  54 -- REDEFINE materialized_phenoview, fixing handling of plant vs plot and dropping unnecessary fields:
  55
  56 DROP MATERIALIZED VIEW IF EXISTS public.materialized_phenoview CASCADE;
  57 CREATE MATERIALIZED VIEW public.materialized_phenoview AS
  58 SELECT
  59   breeding_program.project_id AS breeding_program_id,
  60   nd_experiment.nd_geolocation_id AS location_id,
  61   projectprop.value AS year_id, trial.project_id AS trial_id,
  62   accession.stock_id AS accession_id,
  63   stock.stock_id AS stock_id,
  64   phenotype.phenotype_id as phenotype_id,
  65   phenotype.cvalue_id as trait_id
  66   FROM stock accession
  67      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 = 'plot_of' OR cvterm.name = 'plant_of')
  68      LEFT JOIN stock ON stock_relationship.subject_id = stock.stock_id AND stock.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'plot' OR cvterm.name = 'plant')
  69      LEFT JOIN nd_experiment_stock ON(stock.stock_id = nd_experiment_stock.stock_id AND nd_experiment_stock.type_id IN (SELECT cvterm_id from cvterm where cvterm.name IN ('phenotyping_experiment', 'field_layout')))
  70      LEFT JOIN nd_experiment ON(nd_experiment_stock.nd_experiment_id = nd_experiment.nd_experiment_id AND nd_experiment.type_id IN (SELECT cvterm_id from cvterm where cvterm.name IN ('phenotyping_experiment', 'field_layout')))
  71      FULL OUTER JOIN nd_geolocation ON nd_experiment.nd_geolocation_id = nd_geolocation.nd_geolocation_id
  72      LEFT JOIN nd_experiment_project ON nd_experiment_stock.nd_experiment_id = nd_experiment_project.nd_experiment_id
  73      FULL OUTER JOIN project trial ON nd_experiment_project.project_id = trial.project_id
  74      LEFT JOIN project_relationship ON trial.project_id = project_relationship.subject_project_id AND project_relationship.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'breeding_program_trial_relationship' )
  75      FULL OUTER JOIN project breeding_program ON project_relationship.object_project_id = breeding_program.project_id
  76      LEFT JOIN projectprop ON trial.project_id = projectprop.project_id AND projectprop.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'project year' )
  77      LEFT JOIN nd_experiment_phenotype ON(nd_experiment_stock.nd_experiment_id = nd_experiment_phenotype.nd_experiment_id)
  78      LEFT JOIN phenotype ON nd_experiment_phenotype.phenotype_id = phenotype.phenotype_id
  79   WHERE accession.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'accession')
  80   ORDER BY breeding_program_id, location_id, trial_id, accession_id, stock.stock_id, phenotype_id, trait_id
  81 WITH DATA;
  82
  83 CREATE UNIQUE INDEX unq_pheno_idx ON public.materialized_phenoview(stock_id,phenotype_id,trait_id) WITH (fillfactor=100);
  84 CREATE INDEX accession_id_pheno_idx ON public.materialized_phenoview(accession_id) WITH (fillfactor=100);
  85 CREATE INDEX breeding_program_id_idx ON public.materialized_phenoview(breeding_program_id) WITH (fillfactor=100);
  86 CREATE INDEX location_id_idx ON public.materialized_phenoview(location_id) WITH (fillfactor=100);
  87 CREATE INDEX stock_id_idx ON public.materialized_phenoview(stock_id) WITH (fillfactor=100);
  88 CREATE INDEX trial_id_idx ON public.materialized_phenoview(trial_id) WITH (fillfactor=100);
  89 CREATE INDEX year_id_idx ON public.materialized_phenoview(year_id) WITH (fillfactor=100);
  90 ALTER MATERIALIZED VIEW materialized_phenoview OWNER TO web_usr;
  91
  92 DROP MATERIALIZED VIEW IF EXISTS public.materialized_genoview CASCADE;
  93 CREATE MATERIALIZED VIEW public.materialized_genoview AS
  94  SELECT stock.stock_id AS accession_id,
  95     nd_experiment_protocol.nd_protocol_id AS genotyping_protocol_id,
  96     genotype.genotype_id AS genotype_id
  97    FROM stock
  98      LEFT JOIN nd_experiment_stock ON stock.stock_id = nd_experiment_stock.stock_id
  99      LEFT JOIN nd_experiment_protocol ON nd_experiment_stock.nd_experiment_id = nd_experiment_protocol.nd_experiment_id
 100      LEFT JOIN nd_protocol ON nd_experiment_protocol.nd_protocol_id = nd_protocol.nd_protocol_id
 101      LEFT JOIN nd_experiment_genotype ON nd_experiment_stock.nd_experiment_id = nd_experiment_genotype.nd_experiment_id
 102      LEFT JOIN genotype ON genotype.genotype_id = nd_experiment_genotype.genotype_id
 103   WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'accession')
 104   GROUP BY stock.stock_id, nd_experiment_protocol.nd_protocol_id, genotype.genotype_id
 105   WITH DATA;
 106
 107 CREATE UNIQUE INDEX unq_geno_idx ON public.materialized_genoview(accession_id,genotype_id) WITH (fillfactor=100);
 108 CREATE INDEX accession_id_geno_idx ON public.materialized_genoview(accession_id) WITH (fillfactor=100);
 109 CREATE INDEX genotyping_protocol_id_idx ON public.materialized_genoview(genotyping_protocol_id) WITH (fillfactor=100);
 110 CREATE INDEX genotype_id_idx ON public.materialized_genoview(genotype_id) WITH (fillfactor=100);
 111 ALTER MATERIALIZED VIEW materialized_genoview OWNER TO web_usr;
 112
 113 UPDATE matviews set mv_dependents = '{"accessionsXbreeding_programs","accessionsXlocations","accessionsXplants","accessionsXplots","accessionsXtrait_components","accessionsXtraits","accessionsXtrials","accessionsXtrial_designs","accessionsXtrial_types","accessionsXyears","breeding_programsXgenotyping_protocols","breeding_programsXlocations","breeding_programsXplants","breeding_programsXplots","breeding_programsXtrait_components","breeding_programsXtraits","breeding_programsXtrials","breeding_programsXtrial_designs","breeding_programsXtrial_types","breeding_programsXyears","genotyping_protocolsXlocations","genotyping_protocolsXplants","genotyping_protocolsXplots","genotyping_protocolsXtrait_components","genotyping_protocolsXtraits","genotyping_protocolsXtrials","genotyping_protocolsXtrial_designs","genotyping_protocolsXtrial_types","genotyping_protocolsXyears","locationsXplants","locationsXplots","locationsXtrait_components","locationsXtraits","locationsXtrials","locationsXtrial_designs","locationsXtrial_types","locationsXyears","plantsXplots","plantsXtrait_components","plantsXtraits","plantsXtrials","plantsXtrial_designs","plantsXtrial_types","plantsXyears","plotsXtraits","plotsXtrait_components","plotsXtrials","plotsXtrial_designs","plotsXtrial_types","plotsXyears","trait_componentsXtraits","trait_componentsXtrial_designs","trait_componentsXtrial_types","trait_componentsXtrials","trait_componentsXyears","traitsXtrials","traitsXtrial_designs","traitsXtrial_types","traitsXyears","trial_designsXtrials","trial_typesXtrials","trialsXyears","trial_designsXtrial_types","trial_designsXyears","trial_typesXyears"}' WHERE mv_name = 'materialized_phenoview';
 114
 115 -- REDEFINE TRAIT VIEW, getting all proximal terms from cvs that have a term associated with at least one phenotype measurement
 116
 117 DROP MATERIALIZED VIEW IF EXISTS public.trials CASCADE;
 118 CREATE MATERIALIZED VIEW public.trials AS
 119 SELECT trial.project_id AS trial_id,
 120     trial.name AS trial_name
 121    FROM project breeding_program
 122    JOIN project_relationship ON(breeding_program.project_id = object_project_id AND project_relationship.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'breeding_program_trial_relationship'))
 123    JOIN project trial ON(subject_project_id = trial.project_id)
 124    JOIN projectprop on(trial.project_id = projectprop.project_id)
 125    WHERE projectprop.type_id NOT IN (SELECT cvterm.cvterm_id FROM cvterm WHERE cvterm.name::text = 'cross'::text OR cvterm.name::text = 'trial_folder'::text OR cvterm.name::text = 'folder_for_trials'::text OR cvterm.name::text = 'folder_for_crosses'::text)
 126    GROUP BY trial.project_id, trial.name
 127 WITH DATA;
 128 CREATE UNIQUE INDEX trials_idx ON public.trials(trial_id) WITH (fillfactor=100);
 129 ALTER MATERIALIZED VIEW trials OWNER TO web_usr;
 130
 131 DROP MATERIALIZED VIEW IF EXISTS public.trait_components CASCADE;
 132 CREATE MATERIALIZED VIEW public.trait_components AS
 133 SELECT cvterm.cvterm_id AS trait_component_id,
 134 (((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text AS trait_component_name
 135             FROM cv
 136             JOIN cvprop ON(cv.cv_id = cvprop.cv_id AND cvprop.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = ANY ('{object_ontology,attribute_ontology,method_ontology,unit_ontology,time_ontology}')))
 137             JOIN cvterm ON(cvprop.cv_id = cvterm.cv_id)
 138             JOIN dbxref USING(dbxref_id)
 139             JOIN db ON(dbxref.db_id = db.db_id)
 140             LEFT JOIN cvterm_relationship is_subject ON cvterm.cvterm_id = is_subject.subject_id
 141             LEFT JOIN cvterm_relationship is_object ON cvterm.cvterm_id = is_object.object_id
 142             WHERE is_object.object_id IS NULL AND is_subject.subject_id IS NOT NULL
 143             GROUP BY 2,1 ORDER BY 2,1
 144     WITH DATA;
 145     CREATE UNIQUE INDEX trait_components_idx ON public.trait_components(trait_component_id) WITH (fillfactor=100);
 146     ALTER MATERIALIZED VIEW trait_components OWNER TO web_usr;
 147     INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trait_components', FALSE, CURRENT_TIMESTAMP);
 148
 149 DROP MATERIALIZED VIEW IF EXISTS public.traits CASCADE;
 150 CREATE MATERIALIZED VIEW public.traits AS
 151   SELECT cvterm.cvterm_id AS trait_id,
 152   (((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text AS trait_name
 153         FROM cv
 154     JOIN cvprop ON(cv.cv_id = cvprop.cv_id AND cvprop.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'trait_ontology'))
 155     JOIN cvterm ON(cvprop.cv_id = cvterm.cv_id)
 156           JOIN dbxref USING(dbxref_id)
 157     JOIN db ON(dbxref.db_id = db.db_id)
 158     LEFT JOIN cvterm_relationship is_variable ON cvterm.cvterm_id = is_variable.subject_id AND is_variable.type_id = (SELECT cvterm_id FROM cvterm WHERE name = 'VARIABLE_OF')
 159     WHERE is_variable.subject_id IS NOT NULL
 160     GROUP BY 1,2
 161   UNION
 162   SELECT cvterm.cvterm_id AS trait_id,
 163   (((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text AS trait_name
 164   FROM cv
 165     JOIN cvprop ON(cv.cv_id = cvprop.cv_id AND cvprop.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'composed_trait_ontology'))
 166     JOIN cvterm ON(cvprop.cv_id = cvterm.cv_id)
 167     JOIN dbxref USING(dbxref_id)
 168     JOIN db ON(dbxref.db_id = db.db_id)
 169     LEFT JOIN cvterm_relationship is_subject ON cvterm.cvterm_id = is_subject.subject_id
 170     WHERE is_subject.subject_id IS NOT NULL
 171     GROUP BY 1,2 ORDER BY 2
 172   WITH DATA;
 173   CREATE UNIQUE INDEX traits_idx ON public.traits(trait_id) WITH (fillfactor=100);
 174   ALTER MATERIALIZED VIEW traits OWNER TO web_usr;
 175
 176 -- ADD BACK DROPPED BINARY VIEWS, UPDATING WHERE NECESSARY (PLOTS, PLANTS, TRIAL DESIGNS AND TYPES) TO WORK WITH MODIFIED MATPHENO VIEW AND NEW CATEGORIES
 177
 178 CREATE MATERIALIZED VIEW public.accessionsXtraits AS
 179 SELECT public.materialized_phenoview.accession_id,
 180     public.materialized_phenoview.trait_id
 181    FROM public.materialized_phenoview
 182   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.trait_id
 183 WITH DATA;
 184 CREATE UNIQUE INDEX accessionsXtraits_idx ON public.accessionsXtraits(accession_id, trait_id) WITH (fillfactor=100);
 185 ALTER MATERIALIZED VIEW accessionsXtraits OWNER TO web_usr;
 186
 187 CREATE MATERIALIZED VIEW public.breeding_programsXtraits AS
 188 SELECT public.materialized_phenoview.breeding_program_id,
 189     public.materialized_phenoview.trait_id
 190    FROM public.materialized_phenoview
 191   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_phenoview.trait_id
 192 WITH DATA;
 193 CREATE UNIQUE INDEX breeding_programsXtraits_idx ON public.breeding_programsXtraits(breeding_program_id, trait_id) WITH (fillfactor=100);
 194 ALTER MATERIALIZED VIEW breeding_programsXtraits OWNER TO web_usr;
 195
 196 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtraits AS
 197 SELECT public.materialized_genoview.genotyping_protocol_id,
 198     public.materialized_phenoview.trait_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.trait_id
 202   WITH DATA;
 203 CREATE UNIQUE INDEX genotyping_protocolsXtraits_idx ON public.genotyping_protocolsXtraits(genotyping_protocol_id, trait_id) WITH (fillfactor=100);
 204 ALTER MATERIALIZED VIEW genotyping_protocolsXtraits OWNER TO web_usr;
 205
 206 CREATE MATERIALIZED VIEW public.locationsXtraits AS
 207 SELECT public.materialized_phenoview.location_id,
 208     public.materialized_phenoview.trait_id
 209    FROM public.materialized_phenoview
 210   GROUP BY public.materialized_phenoview.location_id, public.materialized_phenoview.trait_id
 211 WITH DATA;
 212 CREATE UNIQUE INDEX locationsXtraits_idx ON public.locationsXtraits(location_id, trait_id) WITH (fillfactor=100);
 213 ALTER MATERIALIZED VIEW locationsXtraits OWNER TO web_usr;
 214
 215 CREATE MATERIALIZED VIEW public.plantsXtraits AS
 216 SELECT public.stock.stock_id AS plant_id,
 217     public.materialized_phenoview.trait_id
 218    FROM public.materialized_phenoview
 219    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'))
 220   GROUP BY public.stock.stock_id, public.materialized_phenoview.trait_id
 221 WITH DATA;
 222 CREATE UNIQUE INDEX plantsXtraits_idx ON public.plantsXtraits(plant_id, trait_id) WITH (fillfactor=100);
 223 ALTER MATERIALIZED VIEW plantsXtraits OWNER TO web_usr;
 224
 225 CREATE MATERIALIZED VIEW public.plotsXtraits AS
 226 SELECT public.stock.stock_id AS plot_id,
 227     public.materialized_phenoview.trait_id
 228    FROM public.materialized_phenoview
 229    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'))
 230   GROUP BY public.stock.stock_id, public.materialized_phenoview.trait_id
 231 WITH DATA;
 232 CREATE UNIQUE INDEX plotsXtraits_idx ON public.plotsXtraits(plot_id, trait_id) WITH (fillfactor=100);
 233 ALTER MATERIALIZED VIEW plotsXtraits OWNER TO web_usr;
 234
 235 CREATE MATERIALIZED VIEW public.traitsXtrials AS
 236 SELECT public.materialized_phenoview.trait_id,
 237     public.materialized_phenoview.trial_id
 238    FROM public.materialized_phenoview
 239   GROUP BY public.materialized_phenoview.trait_id, public.materialized_phenoview.trial_id
 240 WITH DATA;
 241 CREATE UNIQUE INDEX traitsXtrials_idx ON public.traitsXtrials(trait_id, trial_id) WITH (fillfactor=100);
 242 ALTER MATERIALIZED VIEW traitsXtrials OWNER TO web_usr;
 243
 244 CREATE MATERIALIZED VIEW public.traitsXtrial_designs AS
 245 SELECT public.materialized_phenoview.trait_id,
 246     trialdesign.value AS trial_design_id
 247    FROM public.materialized_phenoview
 248    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' )
 249   GROUP BY public.materialized_phenoview.trait_id, trialdesign.value
 250 WITH DATA;
 251 CREATE UNIQUE INDEX traitsXtrial_designs_idx ON public.traitsXtrial_designs(trait_id, trial_design_id) WITH (fillfactor=100);
 252 ALTER MATERIALIZED VIEW traitsXtrial_designs OWNER TO web_usr;
 253
 254 CREATE MATERIALIZED VIEW public.traitsXtrial_types AS
 255 SELECT public.materialized_phenoview.trait_id,
 256     trialterm.cvterm_id AS trial_type_id
 257    FROM public.materialized_phenoview
 258    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' )
 259    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 260   GROUP BY public.materialized_phenoview.trait_id, trialterm.cvterm_id
 261 WITH DATA;
 262 CREATE UNIQUE INDEX traitsXtrial_types_idx ON public.traitsXtrial_types(trait_id, trial_type_id) WITH (fillfactor=100);
 263 ALTER MATERIALIZED VIEW traitsXtrial_types OWNER TO web_usr;
 264
 265 CREATE MATERIALIZED VIEW public.traitsXyears AS
 266 SELECT public.materialized_phenoview.trait_id,
 267     public.materialized_phenoview.year_id
 268    FROM public.materialized_phenoview
 269   GROUP BY public.materialized_phenoview.trait_id, public.materialized_phenoview.year_id
 270 WITH DATA;
 271 CREATE UNIQUE INDEX traitsXyears_idx ON public.traitsXyears(trait_id, year_id) WITH (fillfactor=100);
 272 ALTER MATERIALIZED VIEW traitsXyears OWNER TO web_usr;
 273
 274
 275
 276 CREATE MATERIALIZED VIEW public.accessionsXtrait_components AS
 277 SELECT public.materialized_phenoview.accession_id,
 278     trait_component.cvterm_id AS trait_component_id
 279    FROM public.materialized_phenoview
 280    JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 281    JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 282    JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 283   GROUP BY 1,2
 284 WITH DATA;
 285 CREATE UNIQUE INDEX accessionsXtrait_components_idx ON public.accessionsXtrait_components(accession_id, trait_component_id) WITH (fillfactor=100);
 286 ALTER MATERIALIZED VIEW accessionsXtrait_components OWNER TO web_usr;
 287 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('accessionsXtrait_components', FALSE, CURRENT_TIMESTAMP);
 288
 289 CREATE MATERIALIZED VIEW public.breeding_programsXtrait_components AS
 290 SELECT public.materialized_phenoview.breeding_program_id,
 291 trait_component.cvterm_id AS trait_component_id
 292 FROM public.materialized_phenoview
 293 JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 294 JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 295 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 296 GROUP BY 1,2
 297 WITH DATA;
 298 CREATE UNIQUE INDEX breeding_programsXtrait_components_idx ON public.breeding_programsXtrait_components(breeding_program_id, trait_component_id) WITH (fillfactor=100);
 299 ALTER MATERIALIZED VIEW breeding_programsXtrait_components OWNER TO web_usr;
 300 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('breeding_programsXtrait_components', FALSE, CURRENT_TIMESTAMP);
 301
 302 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrait_components AS
 303 SELECT public.materialized_genoview.genotyping_protocol_id,
 304 trait_component.cvterm_id AS trait_component_id
 305 FROM public.materialized_genoview
 306 JOIN public.materialized_phenoview USING(accession_id)
 307 JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 308 JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 309 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 310 GROUP BY 1,2
 311 WITH DATA;
 312 CREATE UNIQUE INDEX genotyping_protocolsXtrait_components_idx ON public.genotyping_protocolsXtrait_components(genotyping_protocol_id, trait_component_id) WITH (fillfactor=100);
 313 ALTER MATERIALIZED VIEW genotyping_protocolsXtrait_components OWNER TO web_usr;
 314 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('genotyping_protocolsXtrait_components', FALSE, CURRENT_TIMESTAMP);
 315
 316 CREATE MATERIALIZED VIEW public.locationsXtrait_components AS
 317 SELECT public.materialized_phenoview.location_id,
 318 trait_component.cvterm_id AS trait_component_id
 319 FROM public.materialized_phenoview
 320 JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 321 JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 322 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 323 GROUP BY 1,2
 324 WITH DATA;
 325 CREATE UNIQUE INDEX locationsXtrait_components_idx ON public.locationsXtrait_components(location_id, trait_component_id) WITH (fillfactor=100);
 326 ALTER MATERIALIZED VIEW locationsXtrait_components OWNER TO web_usr;
 327 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('locationsXtrait_components', FALSE, CURRENT_TIMESTAMP);
 328
 329 CREATE MATERIALIZED VIEW public.plantsXtrait_components AS
 330 SELECT public.stock.stock_id AS plant_id,
 331 trait_component.cvterm_id AS trait_component_id
 332 FROM public.materialized_phenoview
 333 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'))
 334 JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 335 JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 336 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 337 GROUP BY 1,2
 338 WITH DATA;
 339 CREATE UNIQUE INDEX plantsXtrait_components_idx ON public.plantsXtrait_components(plant_id, trait_component_id) WITH (fillfactor=100);
 340 ALTER MATERIALIZED VIEW plantsXtrait_components OWNER TO web_usr;
 341 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plantsXtrait_components', FALSE, CURRENT_TIMESTAMP);
 342
 343 CREATE MATERIALIZED VIEW public.plotsXtrait_components AS
 344 SELECT public.stock.stock_id AS plot_id,
 345 trait_component.cvterm_id AS trait_component_id
 346 FROM public.materialized_phenoview
 347 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'))
 348 JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 349 JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 350 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 351 GROUP BY 1,2
 352 WITH DATA;
 353 CREATE UNIQUE INDEX plotsXtrait_components_idx ON public.plotsXtrait_components(plot_id, trait_component_id) WITH (fillfactor=100);
 354 ALTER MATERIALIZED VIEW plotsXtrait_components OWNER TO web_usr;
 355 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plotsXtrait_components', FALSE, CURRENT_TIMESTAMP);
 356
 357 CREATE MATERIALIZED VIEW public.trait_componentsXtraits AS
 358 SELECT traits.trait_id,
 359 trait_component.cvterm_id AS trait_component_id
 360 FROM traits
 361 JOIN cvterm_relationship ON(traits.trait_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 362 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 363 GROUP BY 1,2
 364 WITH DATA;
 365 CREATE UNIQUE INDEX trait_componentsXtraits_idx ON public.trait_componentsXtraits(trait_component_id, trait_id) WITH (fillfactor=100);
 366 ALTER MATERIALIZED VIEW trait_componentsXtraits OWNER TO web_usr;
 367 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trait_componentsXtraits', FALSE, CURRENT_TIMESTAMP);
 368
 369 CREATE MATERIALIZED VIEW public.trait_componentsXtrials AS
 370 SELECT trait_component.cvterm_id AS trait_component_id,
 371     public.materialized_phenoview.trial_id
 372     FROM public.materialized_phenoview
 373     JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 374     JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 375     JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 376     GROUP BY 1,2
 377 WITH DATA;
 378 CREATE UNIQUE INDEX trait_componentsXtrials_idx ON public.trait_componentsXtrials(trait_component_id, trial_id) WITH (fillfactor=100);
 379 ALTER MATERIALIZED VIEW trait_componentsXtrials OWNER TO web_usr;
 380 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trait_componentsXtrials', FALSE, CURRENT_TIMESTAMP);
 381
 382 CREATE MATERIALIZED VIEW public.trait_componentsXtrial_designs AS
 383 SELECT trait_component.cvterm_id AS trait_component_id,
 384     trialdesign.value AS trial_design_id
 385    FROM public.materialized_phenoview
 386    JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 387    JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 388    JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 389    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' )
 390   GROUP BY 1,2
 391 WITH DATA;
 392 CREATE UNIQUE INDEX trait_componentsXtrial_designs_idx ON public.trait_componentsXtrial_designs(trait_component_id, trial_design_id) WITH (fillfactor=100);
 393 ALTER MATERIALIZED VIEW trait_componentsXtrial_designs OWNER TO web_usr;
 394 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trait_componentsXtrial_designs', FALSE, CURRENT_TIMESTAMP);
 395
 396 CREATE MATERIALIZED VIEW public.trait_componentsXtrial_types AS
 397 SELECT trait_component.cvterm_id AS trait_component_id,
 398     trialterm.cvterm_id AS trial_type_id
 399    FROM public.materialized_phenoview
 400    JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 401    JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 402    JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 403    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' )
 404    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 405   GROUP BY 1,2
 406 WITH DATA;
 407 CREATE UNIQUE INDEX trait_componentsXtrial_types_idx ON public.trait_componentsXtrial_types(trait_component_id, trial_type_id) WITH (fillfactor=100);
 408 ALTER MATERIALIZED VIEW trait_componentsXtrial_types OWNER TO web_usr;
 409 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trait_componentsXtrial_types', FALSE, CURRENT_TIMESTAMP);
 410
 411 CREATE MATERIALIZED VIEW public.trait_componentsXyears AS
 412 SELECT trait_component.cvterm_id AS trait_component_id,
 413     public.materialized_phenoview.year_id
 414    FROM public.materialized_phenoview
 415    JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 416    JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 417    JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 418   GROUP BY 1,2
 419 WITH DATA;
 420 CREATE UNIQUE INDEX trait_componentsXyears_idx ON public.trait_componentsXyears(trait_component_id, year_id) WITH (fillfactor=100);
 421 ALTER MATERIALIZED VIEW trait_componentsXyears OWNER TO web_usr;
 422 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trait_componentsXyears', FALSE, CURRENT_TIMESTAMP);
 423
 424
 425 -- FIX VIEWS FOR PLANTS, PLOTS, TRIAL DESIGNS AND TRIAL TYPES
 426
 427 DROP MATERIALIZED VIEW IF EXISTS public.accessions;
 428 CREATE MATERIALIZED VIEW public.accessions AS
 429   SELECT stock.stock_id AS accession_id,
 430   stock.uniquename AS accession_name
 431   FROM stock
 432   WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'accession') AND is_obsolete = 'f'
 433   GROUP BY stock.stock_id, stock.uniquename
 434   WITH DATA;
 435 CREATE UNIQUE INDEX accessions_idx ON public.accessions(accession_id) WITH (fillfactor=100);
 436 ALTER MATERIALIZED VIEW accessions OWNER TO web_usr;
 437
 438 CREATE MATERIALIZED VIEW public.accessionsXbreeding_programs AS
 439 SELECT public.materialized_phenoview.accession_id,
 440     public.materialized_phenoview.breeding_program_id
 441    FROM public.materialized_phenoview
 442   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.breeding_program_id
 443   WITH DATA;
 444 CREATE UNIQUE INDEX accessionsXbreeding_programs_idx ON public.accessionsXbreeding_programs(accession_id, breeding_program_id) WITH (fillfactor=100);
 445 ALTER MATERIALIZED VIEW accessionsXbreeding_programs OWNER TO web_usr;
 446
 447 CREATE MATERIALIZED VIEW public.accessionsXgenotyping_protocols AS
 448 SELECT public.materialized_genoview.accession_id,
 449     public.materialized_genoview.genotyping_protocol_id
 450    FROM public.materialized_genoview
 451   GROUP BY public.materialized_genoview.accession_id, public.materialized_genoview.genotyping_protocol_id
 452   WITH DATA;
 453 CREATE UNIQUE INDEX accessionsXgenotyping_protocols_idx ON public.accessionsXgenotyping_protocols(accession_id, genotyping_protocol_id) WITH (fillfactor=100);
 454 ALTER MATERIALIZED VIEW accessionsXgenotyping_protocols OWNER TO web_usr;
 455
 456 CREATE MATERIALIZED VIEW public.accessionsXlocations AS
 457 SELECT public.materialized_phenoview.accession_id,
 458     public.materialized_phenoview.location_id
 459    FROM public.materialized_phenoview
 460   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.location_id
 461   WITH DATA;
 462 CREATE UNIQUE INDEX accessionsXlocations_idx ON public.accessionsXlocations(accession_id, location_id) WITH (fillfactor=100);
 463 ALTER MATERIALIZED VIEW accessionsXlocations OWNER TO web_usr;
 464
 465 CREATE MATERIALIZED VIEW public.accessionsXplants AS
 466 SELECT public.materialized_phenoview.accession_id,
 467     public.stock.stock_id AS plant_id
 468    FROM public.materialized_phenoview
 469    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'))
 470   GROUP BY public.materialized_phenoview.accession_id, public.stock.stock_id
 471   WITH DATA;
 472 CREATE UNIQUE INDEX accessionsXplants_idx ON public.accessionsXplants(accession_id, plant_id) WITH (fillfactor=100);
 473 ALTER MATERIALIZED VIEW accessionsXplants OWNER TO web_usr;
 474
 475 CREATE MATERIALIZED VIEW public.accessionsXplots AS
 476 SELECT public.materialized_phenoview.accession_id,
 477     public.stock.stock_id AS plot_id
 478    FROM public.materialized_phenoview
 479    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'))
 480   GROUP BY public.materialized_phenoview.accession_id, public.stock.stock_id
 481 WITH DATA;
 482 CREATE UNIQUE INDEX accessionsXplots_idx ON public.accessionsXplots(accession_id, plot_id) WITH (fillfactor=100);
 483 ALTER MATERIALIZED VIEW accessionsXplots OWNER TO web_usr;
 484
 485 CREATE MATERIALIZED VIEW public.accessionsXtrial_designs AS
 486 SELECT public.materialized_phenoview.accession_id,
 487     trialdesign.value AS trial_design_id
 488    FROM public.materialized_phenoview
 489    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' )
 490   GROUP BY public.materialized_phenoview.accession_id, trialdesign.value
 491 WITH DATA;
 492 CREATE UNIQUE INDEX accessionsXtrial_designs_idx ON public.accessionsXtrial_designs(accession_id, trial_design_id) WITH (fillfactor=100);
 493 ALTER MATERIALIZED VIEW accessionsXtrial_designs OWNER TO web_usr;
 494
 495 CREATE MATERIALIZED VIEW public.accessionsXtrial_types AS
 496 SELECT public.materialized_phenoview.accession_id,
 497     trialterm.cvterm_id AS trial_type_id
 498    FROM public.materialized_phenoview
 499    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' )
 500    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 501   GROUP BY public.materialized_phenoview.accession_id, trialterm.cvterm_id
 502 WITH DATA;
 503 CREATE UNIQUE INDEX accessionsXtrial_types_idx ON public.accessionsXtrial_types(accession_id, trial_type_id) WITH (fillfactor=100);
 504 ALTER MATERIALIZED VIEW accessionsXtrial_types OWNER TO web_usr;
 505
 506 CREATE MATERIALIZED VIEW public.accessionsXtrials AS
 507 SELECT public.materialized_phenoview.accession_id,
 508     public.materialized_phenoview.trial_id
 509    FROM public.materialized_phenoview
 510   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.trial_id
 511 WITH DATA;
 512 CREATE UNIQUE INDEX accessionsXtrials_idx ON public.accessionsXtrials(accession_id, trial_id) WITH (fillfactor=100);
 513 ALTER MATERIALIZED VIEW accessionsXtrials OWNER TO web_usr;
 514
 515 CREATE MATERIALIZED VIEW public.accessionsXyears AS
 516 SELECT public.materialized_phenoview.accession_id,
 517     public.materialized_phenoview.year_id
 518    FROM public.materialized_phenoview
 519   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.year_id
 520 WITH DATA;
 521 CREATE UNIQUE INDEX accessionsXyears_idx ON public.accessionsXyears(accession_id, year_id) WITH (fillfactor=100);
 522 ALTER MATERIALIZED VIEW accessionsXyears OWNER TO web_usr;
 523
 524
 525 CREATE MATERIALIZED VIEW public.breeding_programsXgenotyping_protocols AS
 526 SELECT public.materialized_phenoview.breeding_program_id,
 527     public.materialized_genoview.genotyping_protocol_id
 528    FROM public.materialized_phenoview
 529    JOIN public.materialized_genoview USING(accession_id)
 530   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_genoview.genotyping_protocol_id
 531 WITH DATA;
 532 CREATE UNIQUE INDEX breeding_programsXgenotyping_protocols_idx ON public.breeding_programsXgenotyping_protocols(breeding_program_id, genotyping_protocol_id) WITH (fillfactor=100);
 533 ALTER MATERIALIZED VIEW breeding_programsXgenotyping_protocols OWNER TO web_usr;
 534
 535 CREATE MATERIALIZED VIEW public.breeding_programsXlocations AS
 536 SELECT public.materialized_phenoview.breeding_program_id,
 537     public.materialized_phenoview.location_id
 538    FROM public.materialized_phenoview
 539   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_phenoview.location_id
 540 WITH DATA;
 541 CREATE UNIQUE INDEX breeding_programsXlocations_idx ON public.breeding_programsXlocations(breeding_program_id, location_id) WITH (fillfactor=100);
 542 ALTER MATERIALIZED VIEW breeding_programsXlocations OWNER TO web_usr;
 543
 544 CREATE MATERIALIZED VIEW public.breeding_programsXplants AS
 545 SELECT public.materialized_phenoview.breeding_program_id,
 546     public.stock.stock_id AS plant_id
 547    FROM public.materialized_phenoview
 548    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'))
 549   GROUP BY public.materialized_phenoview.breeding_program_id, public.stock.stock_id
 550   WITH DATA;
 551 CREATE UNIQUE INDEX breeding_programsXplants_idx ON public.breeding_programsXplants(breeding_program_id, plant_id) WITH (fillfactor=100);
 552 ALTER MATERIALIZED VIEW breeding_programsXplants OWNER TO web_usr;
 553
 554 CREATE MATERIALIZED VIEW public.breeding_programsXplots AS
 555 SELECT public.materialized_phenoview.breeding_program_id,
 556     public.stock.stock_id AS plot_id
 557    FROM public.materialized_phenoview
 558    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'))
 559   GROUP BY public.materialized_phenoview.breeding_program_id, public.stock.stock_id
 560 WITH DATA;
 561 CREATE UNIQUE INDEX breeding_programsXplots_idx ON public.breeding_programsXplots(breeding_program_id, plot_id) WITH (fillfactor=100);
 562 ALTER MATERIALIZED VIEW breeding_programsXplots OWNER TO web_usr;
 563
 564 CREATE MATERIALIZED VIEW public.breeding_programsXtrial_designs AS
 565 SELECT public.materialized_phenoview.breeding_program_id,
 566     trialdesign.value AS trial_design_id
 567    FROM public.materialized_phenoview
 568    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' )
 569   GROUP BY public.materialized_phenoview.breeding_program_id, trialdesign.value
 570 WITH DATA;
 571 CREATE UNIQUE INDEX breeding_programsXtrial_designs_idx ON public.breeding_programsXtrial_designs(breeding_program_id, trial_design_id) WITH (fillfactor=100);
 572 ALTER MATERIALIZED VIEW breeding_programsXtrial_designs OWNER TO web_usr;
 573
 574 CREATE MATERIALIZED VIEW public.breeding_programsXtrial_types AS
 575 SELECT public.materialized_phenoview.breeding_program_id,
 576     trialterm.cvterm_id AS trial_type_id
 577    FROM public.materialized_phenoview
 578    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' )
 579    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 580   GROUP BY public.materialized_phenoview.breeding_program_id, trialterm.cvterm_id
 581 WITH DATA;
 582 CREATE UNIQUE INDEX breeding_programsXtrial_types_idx ON public.breeding_programsXtrial_types(breeding_program_id, trial_type_id) WITH (fillfactor=100);
 583 ALTER MATERIALIZED VIEW breeding_programsXtrial_types OWNER TO web_usr;
 584
 585 CREATE MATERIALIZED VIEW public.breeding_programsXtrials AS
 586 SELECT public.materialized_phenoview.breeding_program_id,
 587     public.materialized_phenoview.trial_id
 588    FROM public.materialized_phenoview
 589   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_phenoview.trial_id
 590 WITH DATA;
 591 CREATE UNIQUE INDEX breeding_programsXtrials_idx ON public.breeding_programsXtrials(breeding_program_id, trial_id) WITH (fillfactor=100);
 592 ALTER MATERIALIZED VIEW breeding_programsXtrials OWNER TO web_usr;
 593
 594 CREATE MATERIALIZED VIEW public.breeding_programsXyears AS
 595 SELECT public.materialized_phenoview.breeding_program_id,
 596     public.materialized_phenoview.year_id
 597    FROM public.materialized_phenoview
 598   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_phenoview.year_id
 599 WITH DATA;
 600 CREATE UNIQUE INDEX breeding_programsXyears_idx ON public.breeding_programsXyears(breeding_program_id, year_id) WITH (fillfactor=100);
 601 ALTER MATERIALIZED VIEW breeding_programsXyears OWNER TO web_usr;
 602
 603
 604 CREATE MATERIALIZED VIEW public.genotyping_protocolsXlocations AS
 605 SELECT public.materialized_genoview.genotyping_protocol_id,
 606     public.materialized_phenoview.location_id
 607    FROM public.materialized_genoview
 608    JOIN public.materialized_phenoview USING(accession_id)
 609   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.location_id
 610 WITH DATA;
 611 CREATE UNIQUE INDEX genotyping_protocolsXlocations_idx ON public.genotyping_protocolsXlocations(genotyping_protocol_id, location_id) WITH (fillfactor=100);
 612 ALTER MATERIALIZED VIEW genotyping_protocolsXlocations OWNER TO web_usr;
 613
 614 CREATE MATERIALIZED VIEW public.genotyping_protocolsXplants AS
 615 SELECT public.materialized_genoview.genotyping_protocol_id,
 616     public.stock.stock_id AS plant_id
 617    FROM public.materialized_genoview
 618    JOIN public.materialized_phenoview USING(accession_id)
 619    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'))
 620   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.stock.stock_id
 621   WITH DATA;
 622 CREATE UNIQUE INDEX genotyping_protocolsXplants_idx ON public.genotyping_protocolsXplants(genotyping_protocol_id, plant_id) WITH (fillfactor=100);
 623 ALTER MATERIALIZED VIEW genotyping_protocolsXplants OWNER TO web_usr;
 624
 625 CREATE MATERIALIZED VIEW public.genotyping_protocolsXplots AS
 626 SELECT public.materialized_genoview.genotyping_protocol_id,
 627     public.stock.stock_id AS plot_id
 628     FROM public.materialized_genoview
 629     JOIN public.materialized_phenoview USING(accession_id)
 630    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'))
 631   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.stock.stock_id
 632 WITH DATA;
 633 CREATE UNIQUE INDEX genotyping_protocolsXplots_idx ON public.genotyping_protocolsXplots(genotyping_protocol_id, plot_id) WITH (fillfactor=100);
 634 ALTER MATERIALIZED VIEW genotyping_protocolsXplots OWNER TO web_usr;
 635
 636 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrial_designs AS
 637 SELECT public.materialized_genoview.genotyping_protocol_id,
 638     trialdesign.value AS trial_design_id
 639     FROM public.materialized_genoview
 640     JOIN public.materialized_phenoview USING(accession_id)
 641    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' )
 642   GROUP BY public.materialized_genoview.genotyping_protocol_id, trialdesign.value
 643 WITH DATA;
 644 CREATE UNIQUE INDEX genotyping_protocolsXtrial_designs_idx ON public.genotyping_protocolsXtrial_designs(genotyping_protocol_id, trial_design_id) WITH (fillfactor=100);
 645 ALTER MATERIALIZED VIEW genotyping_protocolsXtrial_designs OWNER TO web_usr;
 646
 647 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrial_types AS
 648 SELECT public.materialized_genoview.genotyping_protocol_id,
 649     trialterm.cvterm_id AS trial_type_id
 650     FROM public.materialized_genoview
 651     JOIN public.materialized_phenoview USING(accession_id)
 652    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' )
 653    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 654   GROUP BY public.materialized_genoview.genotyping_protocol_id, trialterm.cvterm_id
 655 WITH DATA;
 656 CREATE UNIQUE INDEX genotyping_protocolsXtrial_types_idx ON public.genotyping_protocolsXtrial_types(genotyping_protocol_id, trial_type_id) WITH (fillfactor=100);
 657 ALTER MATERIALIZED VIEW genotyping_protocolsXtrial_types OWNER TO web_usr;
 658
 659 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrials AS
 660 SELECT public.materialized_genoview.genotyping_protocol_id,
 661     public.materialized_phenoview.trial_id
 662    FROM public.materialized_genoview
 663    JOIN public.materialized_phenoview USING(accession_id)
 664   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.trial_id
 665 WITH DATA;
 666 CREATE UNIQUE INDEX genotyping_protocolsXtrials_idx ON public.genotyping_protocolsXtrials(genotyping_protocol_id, trial_id) WITH (fillfactor=100);
 667 ALTER MATERIALIZED VIEW genotyping_protocolsXtrials OWNER TO web_usr;
 668
 669 CREATE MATERIALIZED VIEW public.genotyping_protocolsXyears AS
 670 SELECT public.materialized_genoview.genotyping_protocol_id,
 671     public.materialized_phenoview.year_id
 672    FROM public.materialized_genoview
 673    JOIN public.materialized_phenoview USING(accession_id)
 674   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.year_id
 675 WITH DATA;
 676 CREATE UNIQUE INDEX genotyping_protocolsXyears_idx ON public.genotyping_protocolsXyears(genotyping_protocol_id, year_id) WITH (fillfactor=100);
 677 ALTER MATERIALIZED VIEW genotyping_protocolsXyears OWNER TO web_usr;
 678
 679
 680
 681 CREATE MATERIALIZED VIEW public.locationsXplants AS
 682 SELECT public.materialized_phenoview.location_id,
 683     public.stock.stock_id AS plant_id
 684    FROM public.materialized_phenoview
 685    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'))
 686   GROUP BY public.materialized_phenoview.location_id, public.stock.stock_id
 687   WITH DATA;
 688 CREATE UNIQUE INDEX locationsXplants_idx ON public.locationsXplants(location_id, plant_id) WITH (fillfactor=100);
 689 ALTER MATERIALIZED VIEW locationsXplants OWNER TO web_usr;
 690
 691 CREATE MATERIALIZED VIEW public.locationsXplots AS
 692 SELECT public.materialized_phenoview.location_id,
 693     public.stock.stock_id AS plot_id
 694    FROM public.materialized_phenoview
 695    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'))
 696   GROUP BY public.materialized_phenoview.location_id, public.stock.stock_id
 697 WITH DATA;
 698 CREATE UNIQUE INDEX locationsXplots_idx ON public.locationsXplots(location_id, plot_id) WITH (fillfactor=100);
 699 ALTER MATERIALIZED VIEW locationsXplots OWNER TO web_usr;
 700
 701 CREATE MATERIALIZED VIEW public.locationsXtrial_designs AS
 702 SELECT public.materialized_phenoview.location_id,
 703     trialdesign.value AS trial_design_id
 704    FROM public.materialized_phenoview
 705    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' )
 706   GROUP BY public.materialized_phenoview.location_id, trialdesign.value
 707 WITH DATA;
 708 CREATE UNIQUE INDEX locationsXtrial_designs_idx ON public.locationsXtrial_designs(location_id, trial_design_id) WITH (fillfactor=100);
 709 ALTER MATERIALIZED VIEW locationsXtrial_designs OWNER TO web_usr;
 710
 711 CREATE MATERIALIZED VIEW public.locationsXtrial_types AS
 712 SELECT public.materialized_phenoview.location_id,
 713     trialterm.cvterm_id AS trial_type_id
 714    FROM public.materialized_phenoview
 715    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' )
 716    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 717   GROUP BY public.materialized_phenoview.location_id, trialterm.cvterm_id
 718 WITH DATA;
 719 CREATE UNIQUE INDEX locationsXtrial_types_idx ON public.locationsXtrial_types(location_id, trial_type_id) WITH (fillfactor=100);
 720 ALTER MATERIALIZED VIEW locationsXtrial_types OWNER TO web_usr;
 721
 722 CREATE MATERIALIZED VIEW public.locationsXtrials AS
 723 SELECT public.materialized_phenoview.location_id,
 724     public.materialized_phenoview.trial_id
 725    FROM public.materialized_phenoview
 726   GROUP BY public.materialized_phenoview.location_id, public.materialized_phenoview.trial_id
 727 WITH DATA;
 728 CREATE UNIQUE INDEX locationsXtrials_idx ON public.locationsXtrials(location_id, trial_id) WITH (fillfactor=100);
 729 ALTER MATERIALIZED VIEW locationsXtrials OWNER TO web_usr;
 730
 731 CREATE MATERIALIZED VIEW public.locationsXyears AS
 732 SELECT public.materialized_phenoview.location_id,
 733     public.materialized_phenoview.year_id
 734    FROM public.materialized_phenoview
 735   GROUP BY public.materialized_phenoview.location_id, public.materialized_phenoview.year_id
 736 WITH DATA;
 737 CREATE UNIQUE INDEX locationsXyears_idx ON public.locationsXyears(location_id, year_id) WITH (fillfactor=100);
 738 ALTER MATERIALIZED VIEW locationsXyears OWNER TO web_usr;
 739
 740
 741
 742 DROP MATERIALIZED VIEW IF EXISTS public.plants;
 743 CREATE MATERIALIZED VIEW public.plants AS
 744 SELECT stock.stock_id AS plant_id,
 745     stock.uniquename AS plant_name
 746    FROM stock
 747    WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plant') AND is_obsolete = 'f'
 748   GROUP BY public.stock.stock_id, public.stock.uniquename
 749 WITH DATA;
 750 CREATE UNIQUE INDEX plants_idx ON public.plants(plant_id) WITH (fillfactor=100);
 751 ALTER MATERIALIZED VIEW plants OWNER TO web_usr;
 752
 753 CREATE MATERIALIZED VIEW public.plantsXplots AS
 754 SELECT plant.stock_id AS plant_id,
 755     plot.stock_id AS plot_id
 756    FROM public.materialized_phenoview
 757    JOIN stock plot ON(public.materialized_phenoview.stock_id = plot.stock_id AND plot.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plot'))
 758    JOIN stock_relationship plant_relationship ON plot.stock_id = plant_relationship.subject_id
 759    JOIN stock plant ON plant_relationship.object_id = plant.stock_id AND plant.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plant')
 760   GROUP BY plant.stock_id, plot.stock_id
 761 WITH DATA;
 762 CREATE UNIQUE INDEX plantsXplots_idx ON public.plantsXplots(plant_id, plot_id) WITH (fillfactor=100);
 763 ALTER MATERIALIZED VIEW plantsXplots OWNER TO web_usr;
 764 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plantsXplots', FALSE, CURRENT_TIMESTAMP);
 765
 766 CREATE MATERIALIZED VIEW public.plantsXtrials AS
 767 SELECT public.stock.stock_id AS plant_id,
 768     public.materialized_phenoview.trial_id
 769    FROM public.materialized_phenoview
 770    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'))
 771   GROUP BY public.stock.stock_id, public.materialized_phenoview.trial_id
 772 WITH DATA;
 773 CREATE UNIQUE INDEX plantsXtrials_idx ON public.plantsXtrials(plant_id, trial_id) WITH (fillfactor=100);
 774 ALTER MATERIALIZED VIEW plantsXtrials OWNER TO web_usr;
 775
 776 CREATE MATERIALIZED VIEW public.plantsXtrial_designs AS
 777 SELECT public.stock.stock_id AS plant_id,
 778     trialdesign.value AS trial_design_id
 779    FROM public.materialized_phenoview
 780    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'))
 781    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' )
 782   GROUP BY stock.stock_id, trialdesign.value
 783 WITH DATA;
 784 CREATE UNIQUE INDEX plantsXtrial_designs_idx ON public.plantsXtrial_designs(plant_id, trial_design_id) WITH (fillfactor=100);
 785 ALTER MATERIALIZED VIEW plantsXtrial_designs OWNER TO web_usr;
 786
 787 CREATE MATERIALIZED VIEW public.plantsXtrial_types AS
 788 SELECT public.stock.stock_id AS plant_id,
 789     trialterm.cvterm_id AS trial_type_id
 790    FROM public.materialized_phenoview
 791    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'))
 792    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' )
 793    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 794   GROUP BY public.stock.stock_id, trialterm.cvterm_id
 795 WITH DATA;
 796 CREATE UNIQUE INDEX plantsXtrial_types_idx ON public.plantsXtrial_types(plant_id, trial_type_id) WITH (fillfactor=100);
 797 ALTER MATERIALIZED VIEW plantsXtrial_types OWNER TO web_usr;
 798
 799 CREATE MATERIALIZED VIEW public.plantsXyears AS
 800 SELECT public.stock.stock_id AS plant_id,
 801     public.materialized_phenoview.year_id
 802    FROM public.materialized_phenoview
 803    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'))
 804   GROUP BY public.stock.stock_id, public.materialized_phenoview.year_id
 805 WITH DATA;
 806 CREATE UNIQUE INDEX plantsXyears_idx ON public.plantsXyears(plant_id, year_id) WITH (fillfactor=100);
 807 ALTER MATERIALIZED VIEW plantsXyears OWNER TO web_usr;
 808
 809
 810
 811 DROP MATERIALIZED VIEW IF EXISTS public.plots;
 812 CREATE MATERIALIZED VIEW public.plots AS
 813 SELECT stock.stock_id AS plot_id,
 814     stock.uniquename AS plot_name
 815    FROM stock
 816    WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plot') AND is_obsolete = 'f'
 817   GROUP BY public.stock.stock_id, public.stock.uniquename
 818 WITH DATA;
 819 CREATE UNIQUE INDEX plots_idx ON public.plots(plot_id) WITH (fillfactor=100);
 820 ALTER MATERIALIZED VIEW plots OWNER TO web_usr;
 821
 822 CREATE MATERIALIZED VIEW public.plotsXtrials AS
 823 SELECT public.stock.stock_id AS plot_id,
 824     public.materialized_phenoview.trial_id
 825    FROM public.materialized_phenoview
 826    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'))
 827   GROUP BY public.stock.stock_id, public.materialized_phenoview.trial_id
 828 WITH DATA;
 829 CREATE UNIQUE INDEX plotsXtrials_idx ON public.plotsXtrials(plot_id, trial_id) WITH (fillfactor=100);
 830 ALTER MATERIALIZED VIEW plotsXtrials OWNER TO web_usr;
 831
 832 CREATE MATERIALIZED VIEW public.plotsXtrial_designs AS
 833 SELECT public.stock.stock_id AS plot_id,
 834     trialdesign.value AS trial_design_id
 835    FROM public.materialized_phenoview
 836    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'))
 837    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' )
 838   GROUP BY stock.stock_id, trialdesign.value
 839 WITH DATA;
 840 CREATE UNIQUE INDEX plotsXtrial_designs_idx ON public.plotsXtrial_designs(plot_id, trial_design_id) WITH (fillfactor=100);
 841 ALTER MATERIALIZED VIEW plotsXtrial_designs OWNER TO web_usr;
 842
 843 CREATE MATERIALIZED VIEW public.plotsXtrial_types AS
 844 SELECT public.stock.stock_id AS plot_id,
 845     trialterm.cvterm_id AS trial_type_id
 846    FROM public.materialized_phenoview
 847    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'))
 848    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' )
 849    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 850   GROUP BY public.stock.stock_id, trialterm.cvterm_id
 851 WITH DATA;
 852 CREATE UNIQUE INDEX plotsXtrial_types_idx ON public.plotsXtrial_types(plot_id, trial_type_id) WITH (fillfactor=100);
 853 ALTER MATERIALIZED VIEW plotsXtrial_types OWNER TO web_usr;
 854
 855 CREATE MATERIALIZED VIEW public.plotsXyears AS
 856 SELECT public.stock.stock_id AS plot_id,
 857     public.materialized_phenoview.year_id
 858    FROM public.materialized_phenoview
 859    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'))
 860   GROUP BY public.stock.stock_id, public.materialized_phenoview.year_id
 861 WITH DATA;
 862 CREATE UNIQUE INDEX plotsXyears_idx ON public.plotsXyears(plot_id, year_id) WITH (fillfactor=100);
 863 ALTER MATERIALIZED VIEW plotsXyears OWNER TO web_usr;
 864
 865
 866
 867 DROP MATERIALIZED VIEW IF EXISTS public.trial_designs;
 868 CREATE MATERIALIZED VIEW public.trial_designs AS
 869 SELECT projectprop.value AS trial_design_id,
 870   projectprop.value AS trial_design_name
 871    FROM projectprop
 872    JOIN cvterm ON(projectprop.type_id = cvterm.cvterm_id)
 873    WHERE cvterm.name = 'design'
 874    GROUP BY projectprop.value
 875 WITH DATA;
 876 CREATE UNIQUE INDEX trial_designs_idx ON public.trial_designs(trial_design_id) WITH (fillfactor=100);
 877 ALTER MATERIALIZED VIEW trial_designs OWNER TO web_usr;
 878
 879 CREATE MATERIALIZED VIEW public.trial_designsXtrial_types AS
 880 SELECT trialdesign.value AS trial_design_id,
 881     trialterm.cvterm_id AS trial_type_id
 882    FROM public.materialized_phenoview
 883    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' )
 884    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' )
 885    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 886   GROUP BY trialdesign.value, trialterm.cvterm_id
 887 WITH DATA;
 888 CREATE UNIQUE INDEX trial_designsXtrial_types_idx ON public.trial_designsXtrial_types(trial_design_id, trial_type_id) WITH (fillfactor=100);
 889 ALTER MATERIALIZED VIEW trial_designsXtrial_types OWNER TO web_usr;
 890
 891 CREATE MATERIALIZED VIEW public.trial_designsXtrials AS
 892 SELECT trialdesign.value AS trial_design_id,
 893     public.materialized_phenoview.trial_id
 894    FROM public.materialized_phenoview
 895    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' )
 896   GROUP BY trialdesign.value, public.materialized_phenoview.trial_id
 897 WITH DATA;
 898 CREATE UNIQUE INDEX trial_designsXtrials_idx ON public.trial_designsXtrials(trial_id, trial_design_id) WITH (fillfactor=100);
 899 ALTER MATERIALIZED VIEW trial_designsXtrials OWNER TO web_usr;
 900
 901 CREATE MATERIALIZED VIEW public.trial_designsXyears AS
 902 SELECT trialdesign.value AS trial_design_id,
 903     public.materialized_phenoview.year_id
 904    FROM public.materialized_phenoview
 905    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' )
 906   GROUP BY trialdesign.value, public.materialized_phenoview.year_id
 907 WITH DATA;
 908 CREATE UNIQUE INDEX trial_designsXyears_idx ON public.trial_designsXyears(trial_design_id, year_id) WITH (fillfactor=100);
 909 ALTER MATERIALIZED VIEW trial_designsXyears OWNER TO web_usr;
 910
 911
 912
 913 DROP MATERIALIZED VIEW IF EXISTS public.trial_types;
 914 CREATE MATERIALIZED VIEW public.trial_types AS
 915 SELECT cvterm.cvterm_id AS trial_type_id,
 916   cvterm.name AS trial_type_name
 917    FROM cvterm
 918    JOIN cv USING(cv_id)
 919    WHERE cv.name = 'project_type'
 920    GROUP BY cvterm.cvterm_id
 921 WITH DATA;
 922 CREATE UNIQUE INDEX trial_types_idx ON public.trial_types(trial_type_id) WITH (fillfactor=100);
 923 ALTER MATERIALIZED VIEW trial_types OWNER TO web_usr;
 924
 925 CREATE MATERIALIZED VIEW public.trial_typesXtrials AS
 926 SELECT trialterm.cvterm_id AS trial_type_id,
 927     public.materialized_phenoview.trial_id
 928    FROM public.materialized_phenoview
 929    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' )
 930    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 931   GROUP BY trialterm.cvterm_id, public.materialized_phenoview.trial_id
 932 WITH DATA;
 933 CREATE UNIQUE INDEX trial_typesXtrials_idx ON public.trial_typesXtrials(trial_id, trial_type_id) WITH (fillfactor=100);
 934 ALTER MATERIALIZED VIEW trial_typesXtrials OWNER TO web_usr;
 935
 936 CREATE MATERIALIZED VIEW public.trial_typesXyears AS
 937 SELECT trialterm.cvterm_id AS trial_type_id,
 938     public.materialized_phenoview.year_id
 939    FROM public.materialized_phenoview
 940    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' )
 941    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 942   GROUP BY trialterm.cvterm_id, public.materialized_phenoview.year_id
 943 WITH DATA;
 944 CREATE UNIQUE INDEX trial_typesXyears_idx ON public.trial_typesXyears(trial_type_id, year_id) WITH (fillfactor=100);
 945 ALTER MATERIALIZED VIEW trial_typesXyears OWNER TO web_usr;
 946
 947
 948
 949 CREATE MATERIALIZED VIEW public.trialsXyears AS
 950 SELECT public.materialized_phenoview.trial_id,
 951     public.materialized_phenoview.year_id
 952    FROM public.materialized_phenoview
 953   GROUP BY public.materialized_phenoview.trial_id, public.materialized_phenoview.year_id
 954 WITH DATA;
 955 CREATE UNIQUE INDEX trialsXyears_idx ON public.trialsXyears(trial_id, year_id) WITH (fillfactor=100);
 956 ALTER MATERIALIZED VIEW trialsXyears OWNER TO web_usr;
 957
 958
 959
 960
 961
 962 CREATE OR REPLACE FUNCTION public.refresh_materialized_views() RETURNS VOID AS '
 963 REFRESH MATERIALIZED VIEW public.materialized_phenoview;
 964 REFRESH MATERIALIZED VIEW public.materialized_genoview;
 965 REFRESH MATERIALIZED VIEW public.accessions;
 966 REFRESH MATERIALIZED VIEW public.breeding_programs;
 967 REFRESH MATERIALIZED VIEW public.genotyping_protocols;
 968 REFRESH MATERIALIZED VIEW public.locations;
 969 REFRESH MATERIALIZED VIEW public.plants;
 970 REFRESH MATERIALIZED VIEW public.plots;
 971 REFRESH MATERIALIZED VIEW public.trait_components;
 972 REFRESH MATERIALIZED VIEW public.traits;
 973 REFRESH MATERIALIZED VIEW public.trial_designs;
 974 REFRESH MATERIALIZED VIEW public.trial_types;
 975 REFRESH MATERIALIZED VIEW public.trials;
 976 REFRESH MATERIALIZED VIEW public.years;
 977 REFRESH MATERIALIZED VIEW public.accessionsXbreeding_programs;
 978 REFRESH MATERIALIZED VIEW public.accessionsXlocations;
 979 REFRESH MATERIALIZED VIEW public.accessionsXgenotyping_protocols;
 980 REFRESH MATERIALIZED VIEW public.accessionsXplants;
 981 REFRESH MATERIALIZED VIEW public.accessionsXplots;
 982 REFRESH MATERIALIZED VIEW public.accessionsXtrait_components;
 983 REFRESH MATERIALIZED VIEW public.accessionsXtraits;
 984 REFRESH MATERIALIZED VIEW public.accessionsXtrial_designs;
 985 REFRESH MATERIALIZED VIEW public.accessionsXtrial_types;
 986 REFRESH MATERIALIZED VIEW public.accessionsXtrials;
 987 REFRESH MATERIALIZED VIEW public.accessionsXyears;
 988 REFRESH MATERIALIZED VIEW public.breeding_programsXlocations;
 989 REFRESH MATERIALIZED VIEW public.breeding_programsXgenotyping_protocols;
 990 REFRESH MATERIALIZED VIEW public.breeding_programsXplants;
 991 REFRESH MATERIALIZED VIEW public.breeding_programsXplots;
 992 REFRESH MATERIALIZED VIEW public.breeding_programsXtrait_components;
 993 REFRESH MATERIALIZED VIEW public.breeding_programsXtraits;
 994 REFRESH MATERIALIZED VIEW public.breeding_programsXtrial_designs;
 995 REFRESH MATERIALIZED VIEW public.breeding_programsXtrial_types;
 996 REFRESH MATERIALIZED VIEW public.breeding_programsXtrials;
 997 REFRESH MATERIALIZED VIEW public.breeding_programsXyears;
 998 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXlocations;
 999 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXplants;
1000 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXplots;
1001 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXtrait_components;
1002 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXtraits;
1003 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXtrial_designs;
1004 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXtrial_types;
1005 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXtrials;
1006 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXyears;
1007 REFRESH MATERIALIZED VIEW public.locationsXplants;
1008 REFRESH MATERIALIZED VIEW public.locationsXplots;
1009 REFRESH MATERIALIZED VIEW public.locationsXtrait_components;
1010 REFRESH MATERIALIZED VIEW public.locationsXtraits;
1011 REFRESH MATERIALIZED VIEW public.locationsXtrial_designs;
1012 REFRESH MATERIALIZED VIEW public.locationsXtrial_types;
1013 REFRESH MATERIALIZED VIEW public.locationsXtrials;
1014 REFRESH MATERIALIZED VIEW public.locationsXyears;
1015 REFRESH MATERIALIZED VIEW public.plantsXplots;
1016 REFRESH MATERIALIZED VIEW public.plantsXtrait_components;
1017 REFRESH MATERIALIZED VIEW public.plantsXtraits;
1018 REFRESH MATERIALIZED VIEW public.plantsXtrial_designs;
1019 REFRESH MATERIALIZED VIEW public.plantsXtrial_types;
1020 REFRESH MATERIALIZED VIEW public.plantsXtrials;
1021 REFRESH MATERIALIZED VIEW public.plantsXyears;
1022 REFRESH MATERIALIZED VIEW public.plotsXtrait_components;
1023 REFRESH MATERIALIZED VIEW public.plotsXtraits;
1024 REFRESH MATERIALIZED VIEW public.plotsXtrial_designs;
1025 REFRESH MATERIALIZED VIEW public.plotsXtrial_types;
1026 REFRESH MATERIALIZED VIEW public.plotsXtrials;
1027 REFRESH MATERIALIZED VIEW public.plotsXyears;
1028 REFRESH MATERIALIZED VIEW public.trait_componentsXtraits;
1029 REFRESH MATERIALIZED VIEW public.trait_componentsXtrial_designs;
1030 REFRESH MATERIALIZED VIEW public.trait_componentsXtrial_types;
1031 REFRESH MATERIALIZED VIEW public.trait_componentsXtrials;
1032 REFRESH MATERIALIZED VIEW public.trait_componentsXyears;
1033 REFRESH MATERIALIZED VIEW public.traitsXtrial_designs;
1034 REFRESH MATERIALIZED VIEW public.traitsXtrial_types;
1035 REFRESH MATERIALIZED VIEW public.traitsXtrials;
1036 REFRESH MATERIALIZED VIEW public.traitsXyears;
1037 REFRESH MATERIALIZED VIEW public.trial_designsXtrial_types;
1038 REFRESH MATERIALIZED VIEW public.trial_designsXtrials;
1039 REFRESH MATERIALIZED VIEW public.trial_designsXyears;
1040 REFRESH MATERIALIZED VIEW public.trial_typesXtrials;
1041 REFRESH MATERIALIZED VIEW public.trial_typesXyears;
1042 REFRESH MATERIALIZED VIEW public.trialsXyears;
1043 UPDATE public.matviews SET currently_refreshing=FALSE, last_refresh=CURRENT_TIMESTAMP;'
1044     LANGUAGE SQL;
1045
1046 ALTER FUNCTION public.refresh_materialized_views() OWNER TO web_usr;
1047
1048 CREATE OR REPLACE FUNCTION public.refresh_materialized_views_concurrently() RETURNS VOID AS '
1049 REFRESH MATERIALIZED VIEW CONCURRENTLY public.materialized_phenoview;
1050 REFRESH MATERIALIZED VIEW CONCURRENTLY public.materialized_genoview;
1051 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessions;
1052 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programs;
1053 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocols;
1054 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locations;
1055 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plants;
1056 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plots;
1057 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trait_components;
1058 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traits;
1059 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_designs;
1060 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_types;
1061 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trials;
1062 REFRESH MATERIALIZED VIEW CONCURRENTLY public.years;
1063 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXbreeding_programs;
1064 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXlocations;
1065 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXgenotyping_protocols;
1066 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXplants;
1067 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXplots;
1068 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXtrait_components;
1069 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXtraits;
1070 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXtrial_designs;
1071 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXtrial_types;
1072 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXtrials;
1073 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXyears;
1074 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXlocations;
1075 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXgenotyping_protocols;
1076 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXplants;
1077 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXplots;
1078 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXtrait_components;
1079 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXtraits;
1080 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXtrial_designs;
1081 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXtrial_types;
1082 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXtrials;
1083 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXyears;
1084 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXlocations;
1085 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXplants;
1086 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXplots;
1087 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXtrait_components;
1088 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXtraits;
1089 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXtrial_designs;
1090 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXtrial_types;
1091 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXtrials;
1092 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXyears;
1093 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXplants;
1094 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXplots;
1095 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXtrait_components;
1096 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXtraits;
1097 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXtrial_designs;
1098 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXtrial_types;
1099 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXtrials;
1100 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXyears;
1101 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXplots;
1102 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXtrait_components;
1103 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXtraits;
1104 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXtrial_designs;
1105 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXtrial_types;
1106 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXtrials;
1107 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXyears;
1108 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXtrait_components;
1109 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXtraits;
1110 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXtrial_designs;
1111 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXtrial_types;
1112 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXtrials;
1113 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXyears;
1114 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trait_componentsXtraits;
1115 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trait_componentsXtrial_designs;
1116 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trait_componentsXtrial_types;
1117 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trait_componentsXtrials;
1118 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trait_componentsXyears;
1119 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traitsXtrial_designs;
1120 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traitsXtrial_types;
1121 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traitsXtrials;
1122 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traitsXyears;
1123 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_designsXtrial_types;
1124 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_designsXtrials;
1125 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_designsXyears;
1126 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_typesXtrials;
1127 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_typesXyears;
1128 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trialsXyears;
1129 UPDATE public.matviews SET currently_refreshing=FALSE, last_refresh=CURRENT_TIMESTAMP;'
1130     LANGUAGE SQL;
1131
1132 ALTER FUNCTION public.refresh_materialized_views_concurrently() OWNER TO web_usr;
1133 --
1134
1135 EOSQL
1136
1137 print "You're done!\n";
1138 }
1139
1140
1141 ####
1142 1; #
1143 ####