db/00086/AddSeedlotViews.pm

   1 #!/usr/bin/env perl
   2
   3 =head1 NAME
   4
   5 AddSeedlotViews.pm
   6
   7 =head1 SYNOPSIS
   8
   9 mx-run AddSeedlotViews [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 the materialized views to include seedlots
  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 AddSeedlotViews;
  33
  34 use Moose;
  35 extends 'CXGN::Metadata::Dbpatch';
  36
  37
  38 has '+description' => ( default => <<'' );
  39 This patch updates the materialized views to include seedlots.
  40
  41 sub patch {
  42     my $self=shift;
  43
  44     print STDOUT "Executing the patch:\n " .   $self->name . ".\n\nDescription:\n  ".  $self->description . ".\n\nExecuted by:\n " .  $self->username . " .";
  45
  46     print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
  47
  48     print STDOUT "\nExecuting the SQL commands.\n";
  49
  50     $self->dbh->do(<<EOSQL);
  51 --do your SQL here
  52
  53 -- REDEFINE materialized_phenoview, adding seedlot:
  54
  55 DROP MATERIALIZED VIEW IF EXISTS public.materialized_phenoview CASCADE;
  56 CREATE MATERIALIZED VIEW public.materialized_phenoview AS
  57 SELECT
  58   breeding_program.project_id AS breeding_program_id,
  59   nd_experiment.nd_geolocation_id AS location_id,
  60   projectprop.value AS year_id,
  61   trial.project_id AS trial_id,
  62   accession.stock_id AS accession_id,
  63   seedlot.stock_id AS seedlot_id,
  64   stock.stock_id AS stock_id,
  65   phenotype.phenotype_id as phenotype_id,
  66   phenotype.cvalue_id as trait_id
  67   FROM stock accession
  68      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')
  69      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')
  70      LEFT JOIN stock_relationship seedlot_relationship ON stock.stock_id = seedlot_relationship.subject_id AND seedlot_relationship.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'seed transaction')
  71      LEFT JOIN stock seedlot ON seedlot_relationship.object_id = seedlot.stock_id AND seedlot.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'seedlot')
  72      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')))
  73      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')))
  74      FULL OUTER JOIN nd_geolocation ON nd_experiment.nd_geolocation_id = nd_geolocation.nd_geolocation_id
  75      LEFT JOIN nd_experiment_project ON nd_experiment_stock.nd_experiment_id = nd_experiment_project.nd_experiment_id
  76      FULL OUTER JOIN project trial ON nd_experiment_project.project_id = trial.project_id
  77      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' )
  78      FULL OUTER JOIN project breeding_program ON project_relationship.object_project_id = breeding_program.project_id
  79      LEFT JOIN projectprop ON trial.project_id = projectprop.project_id AND projectprop.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'project year' )
  80      LEFT JOIN nd_experiment_phenotype ON(nd_experiment_stock.nd_experiment_id = nd_experiment_phenotype.nd_experiment_id)
  81      LEFT JOIN phenotype ON nd_experiment_phenotype.phenotype_id = phenotype.phenotype_id
  82   WHERE accession.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'accession')
  83   ORDER BY breeding_program_id, location_id, trial_id, accession_id, seedlot_id, stock.stock_id, phenotype_id, trait_id
  84 WITH DATA;
  85
  86 CREATE UNIQUE INDEX unq_pheno_idx ON public.materialized_phenoview(stock_id,phenotype_id,trait_id) WITH (fillfactor=100);
  87 CREATE INDEX accession_id_pheno_idx ON public.materialized_phenoview(accession_id) WITH (fillfactor=100);
  88 CREATE INDEX seedlot_id_pheno_idx ON public.materialized_phenoview(seedlot_id) WITH (fillfactor=100);
  89 CREATE INDEX breeding_program_id_idx ON public.materialized_phenoview(breeding_program_id) WITH (fillfactor=100);
  90 CREATE INDEX location_id_idx ON public.materialized_phenoview(location_id) WITH (fillfactor=100);
  91 CREATE INDEX stock_id_idx ON public.materialized_phenoview(stock_id) WITH (fillfactor=100);
  92 CREATE INDEX trial_id_idx ON public.materialized_phenoview(trial_id) WITH (fillfactor=100);
  93 CREATE INDEX year_id_idx ON public.materialized_phenoview(year_id) WITH (fillfactor=100);
  94 ALTER MATERIALIZED VIEW materialized_phenoview OWNER TO web_usr;
  95
  96 --Add jsonb genotypes to geno matview
  97
  98 DROP MATERIALIZED VIEW IF EXISTS public.materialized_genoview CASCADE;
  99 CREATE MATERIALIZED VIEW public.materialized_genoview AS
 100  SELECT stock.stock_id AS accession_id,
 101     nd_experiment_protocol.nd_protocol_id AS genotyping_protocol_id,
 102     genotypeprop.genotype_id AS genotype_id,
 103     to_jsonb(genotypeprop.value::JSON) AS value
 104    FROM stock
 105      LEFT JOIN nd_experiment_stock ON stock.stock_id = nd_experiment_stock.stock_id
 106      LEFT JOIN nd_experiment_protocol ON nd_experiment_stock.nd_experiment_id = nd_experiment_protocol.nd_experiment_id
 107      LEFT JOIN nd_protocol ON nd_experiment_protocol.nd_protocol_id = nd_protocol.nd_protocol_id
 108      LEFT JOIN nd_experiment_genotype ON nd_experiment_stock.nd_experiment_id = nd_experiment_genotype.nd_experiment_id
 109      LEFT JOIN genotypeprop ON nd_experiment_genotype.genotype_id = genotypeprop.genotype_id
 110   WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'accession')
 111   GROUP BY 1,2,3,4
 112   WITH DATA;
 113
 114 CREATE UNIQUE INDEX unq_geno_idx ON public.materialized_genoview(accession_id,genotype_id) WITH (fillfactor=100);
 115 CREATE INDEX accession_id_geno_idx ON public.materialized_genoview(accession_id) WITH (fillfactor=100);
 116 CREATE INDEX genotyping_protocol_id_idx ON public.materialized_genoview(genotyping_protocol_id) WITH (fillfactor=100);
 117 CREATE INDEX genotype_id_idx ON public.materialized_genoview(genotype_id) WITH (fillfactor=100);
 118 ALTER MATERIALIZED VIEW materialized_genoview OWNER TO web_usr;
 119
 120
 121 UPDATE matviews set mv_dependents = '{"accessionsXbreeding_programs","accessionsXlocations","accessionsXplants","accessionsXplots","accessionsXseedlots","accessionsXtrait_components","accessionsXtraits","accessionsXtrials","accessionsXtrial_designs","accessionsXtrial_types","accessionsXyears","breeding_programsXgenotyping_protocols","breeding_programsXlocations","breeding_programsXplants","breeding_programsXplots","breeding_programsXseedlots","breeding_programsXtrait_components","breeding_programsXtraits","breeding_programsXtrials","breeding_programsXtrial_designs","breeding_programsXtrial_types","breeding_programsXyears","genotyping_protocolsXlocations","genotyping_protocolsXplants","genotyping_protocolsXplots","genotyping_protocolsXseedlots","genotyping_protocolsXtrait_components","genotyping_protocolsXtraits","genotyping_protocolsXtrials","genotyping_protocolsXtrial_designs","genotyping_protocolsXtrial_types","genotyping_protocolsXyears","locationsXplants","locationsXplots","locationsXseedlots","locationsXtrait_components","locationsXtraits","locationsXtrials","locationsXtrial_designs","locationsXtrial_types","locationsXyears","plantsXplots","plantsXseedlots","plantsXtrait_components","plantsXtraits","plantsXtrials","plantsXtrial_designs","plantsXtrial_types","plantsXyears","plotsXseedlots","plotsXtrait_components","plotsXtraits","plotsXtrials","plotsXtrial_designs","plotsXtrial_types","plotsXyears","seedlotsXtrait_components","seedlotsXtraits","seedlotsXtrial_designs","seedlotsXtrial_types","seedlotsXtrials","seedlotsXyears","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';
 122
 123 --add seedlots view
 124
 125 CREATE MATERIALIZED VIEW public.seedlots AS
 126 SELECT stock.stock_id AS seedlot_id,
 127 stock.uniquename AS seedlot_name
 128 FROM stock
 129 WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'seedlot') AND is_obsolete = 'f'
 130 GROUP BY 1,2
 131 WITH DATA;
 132 CREATE UNIQUE INDEX seedlots_idx ON public.seedlots(seedlot_id) WITH (fillfactor=100);
 133 ALTER MATERIALIZED VIEW seedlots OWNER TO web_usr;
 134
 135 -- add other individual category views
 136
 137 DROP MATERIALIZED VIEW IF EXISTS public.accessions CASCADE;
 138 CREATE MATERIALIZED VIEW public.accessions AS
 139   SELECT stock.stock_id AS accession_id,
 140   stock.uniquename AS accession_name
 141   FROM stock
 142   WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'accession') AND is_obsolete = 'f'
 143   GROUP BY stock.stock_id, stock.uniquename
 144   WITH DATA;
 145 CREATE UNIQUE INDEX accessions_idx ON public.accessions(accession_id) WITH (fillfactor=100);
 146 ALTER MATERIALIZED VIEW accessions OWNER TO web_usr;
 147
 148 DROP MATERIALIZED VIEW IF EXISTS public.breeding_programs CASCADE;
 149 CREATE MATERIALIZED VIEW public.breeding_programs AS
 150 SELECT project.project_id AS breeding_program_id,
 151     project.name AS breeding_program_name
 152    FROM project join projectprop USING (project_id)
 153    WHERE projectprop.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'breeding_program')
 154   GROUP BY project.project_id, project.name
 155 WITH DATA;
 156 CREATE UNIQUE INDEX breeding_programs_idx ON public.breeding_programs(breeding_program_id) WITH (fillfactor=100);
 157 ALTER MATERIALIZED VIEW breeding_programs OWNER TO web_usr;
 158
 159 DROP MATERIALIZED VIEW IF EXISTS public.genotyping_protocols CASCADE;
 160 CREATE MATERIALIZED VIEW public.genotyping_protocols AS
 161 SELECT nd_protocol.nd_protocol_id AS genotyping_protocol_id,
 162     nd_protocol.name AS genotyping_protocol_name
 163    FROM nd_protocol
 164   GROUP BY public.nd_protocol.nd_protocol_id, public.nd_protocol.name
 165 WITH DATA;
 166 CREATE UNIQUE INDEX genotyping_protocols_idx ON public.genotyping_protocols(genotyping_protocol_id) WITH (fillfactor=100);
 167 ALTER MATERIALIZED VIEW genotyping_protocols OWNER TO web_usr;
 168
 169 DROP MATERIALIZED VIEW IF EXISTS public.locations CASCADE;
 170 CREATE MATERIALIZED VIEW public.locations AS
 171 SELECT nd_geolocation.nd_geolocation_id AS location_id,
 172   nd_geolocation.description AS location_name
 173    FROM nd_geolocation
 174   GROUP BY public.nd_geolocation.nd_geolocation_id, public.nd_geolocation.description
 175 WITH DATA;
 176 CREATE UNIQUE INDEX locations_idx ON public.locations(location_id) WITH (fillfactor=100);
 177 ALTER MATERIALIZED VIEW locations OWNER TO web_usr;
 178
 179 DROP MATERIALIZED VIEW IF EXISTS public.plants CASCADE;
 180 CREATE MATERIALIZED VIEW public.plants AS
 181 SELECT stock.stock_id AS plant_id,
 182     stock.uniquename AS plant_name
 183    FROM stock
 184    WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plant') AND is_obsolete = 'f'
 185   GROUP BY public.stock.stock_id, public.stock.uniquename
 186 WITH DATA;
 187 CREATE UNIQUE INDEX plants_idx ON public.plants(plant_id) WITH (fillfactor=100);
 188 ALTER MATERIALIZED VIEW plants OWNER TO web_usr;
 189
 190 DROP MATERIALIZED VIEW IF EXISTS public.plots CASCADE;
 191 CREATE MATERIALIZED VIEW public.plots AS
 192 SELECT stock.stock_id AS plot_id,
 193     stock.uniquename AS plot_name
 194    FROM stock
 195    WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plot') AND is_obsolete = 'f'
 196   GROUP BY public.stock.stock_id, public.stock.uniquename
 197 WITH DATA;
 198 CREATE UNIQUE INDEX plots_idx ON public.plots(plot_id) WITH (fillfactor=100);
 199 ALTER MATERIALIZED VIEW plots OWNER TO web_usr;
 200
 201 DROP MATERIALIZED VIEW IF EXISTS public.trait_components CASCADE;
 202 CREATE MATERIALIZED VIEW public.trait_components AS
 203 SELECT cvterm.cvterm_id AS trait_component_id,
 204 (((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text AS trait_component_name
 205     FROM cv
 206     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}')))
 207     JOIN cvterm ON(cvprop.cv_id = cvterm.cv_id)
 208     JOIN dbxref USING(dbxref_id)
 209     JOIN db ON(dbxref.db_id = db.db_id)
 210     LEFT JOIN cvterm_relationship is_subject ON cvterm.cvterm_id = is_subject.subject_id
 211     LEFT JOIN cvterm_relationship is_object ON cvterm.cvterm_id = is_object.object_id
 212     WHERE is_object.object_id IS NULL AND is_subject.subject_id IS NOT NULL
 213     GROUP BY 2,1 ORDER BY 2,1
 214 WITH DATA;
 215 CREATE UNIQUE INDEX trait_components_idx ON public.trait_components(trait_component_id) WITH (fillfactor=100);
 216 ALTER MATERIALIZED VIEW trait_components OWNER TO web_usr;
 217
 218 DROP MATERIALIZED VIEW IF EXISTS public.traits CASCADE;
 219 CREATE MATERIALIZED VIEW public.traits AS
 220   SELECT cvterm.cvterm_id AS trait_id,
 221   (((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text AS trait_name
 222         FROM cv
 223     JOIN cvprop ON(cv.cv_id = cvprop.cv_id AND cvprop.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'trait_ontology'))
 224     JOIN cvterm ON(cvprop.cv_id = cvterm.cv_id)
 225           JOIN dbxref USING(dbxref_id)
 226     JOIN db ON(dbxref.db_id = db.db_id)
 227     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')
 228     WHERE is_variable.subject_id IS NOT NULL
 229     GROUP BY 1,2
 230   UNION
 231   SELECT cvterm.cvterm_id AS trait_id,
 232   (((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text AS trait_name
 233   FROM cv
 234     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'))
 235     JOIN cvterm ON(cvprop.cv_id = cvterm.cv_id)
 236     JOIN dbxref USING(dbxref_id)
 237     JOIN db ON(dbxref.db_id = db.db_id)
 238     LEFT JOIN cvterm_relationship is_subject ON cvterm.cvterm_id = is_subject.subject_id
 239     WHERE is_subject.subject_id IS NOT NULL
 240     GROUP BY 1,2 ORDER BY 2
 241   WITH DATA;
 242   CREATE UNIQUE INDEX traits_idx ON public.traits(trait_id) WITH (fillfactor=100);
 243   ALTER MATERIALIZED VIEW traits OWNER TO web_usr;
 244
 245 DROP MATERIALIZED VIEW IF EXISTS public.trials CASCADE;
 246 CREATE MATERIALIZED VIEW public.trials AS
 247 SELECT trial.project_id AS trial_id,
 248     trial.name AS trial_name
 249     FROM project breeding_program
 250     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'))
 251     JOIN project trial ON(subject_project_id = trial.project_id)
 252     JOIN projectprop on(trial.project_id = projectprop.project_id)
 253     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)
 254     GROUP BY trial.project_id, trial.name
 255 WITH DATA;
 256 CREATE UNIQUE INDEX trials_idx ON public.trials(trial_id) WITH (fillfactor=100);
 257 ALTER MATERIALIZED VIEW trials OWNER TO web_usr;
 258
 259 DROP MATERIALIZED VIEW IF EXISTS public.trial_designs CASCADE;
 260 CREATE MATERIALIZED VIEW public.trial_designs AS
 261 SELECT projectprop.value AS trial_design_id,
 262   projectprop.value AS trial_design_name
 263    FROM projectprop
 264    JOIN cvterm ON(projectprop.type_id = cvterm.cvterm_id)
 265    WHERE cvterm.name = 'design'
 266    GROUP BY projectprop.value
 267 WITH DATA;
 268 CREATE UNIQUE INDEX trial_designs_idx ON public.trial_designs(trial_design_id) WITH (fillfactor=100);
 269 ALTER MATERIALIZED VIEW trial_designs OWNER TO web_usr;
 270
 271 DROP MATERIALIZED VIEW IF EXISTS public.trial_types CASCADE;
 272 CREATE MATERIALIZED VIEW public.trial_types AS
 273 SELECT cvterm.cvterm_id AS trial_type_id,
 274   cvterm.name AS trial_type_name
 275    FROM cvterm
 276    JOIN cv USING(cv_id)
 277    WHERE cv.name = 'project_type'
 278    GROUP BY cvterm.cvterm_id
 279 WITH DATA;
 280 CREATE UNIQUE INDEX trial_types_idx ON public.trial_types(trial_type_id) WITH (fillfactor=100);
 281 ALTER MATERIALIZED VIEW trial_types OWNER TO web_usr;
 282
 283 DROP MATERIALIZED VIEW IF EXISTS public.years CASCADE;
 284 CREATE MATERIALIZED VIEW public.years AS
 285 SELECT projectprop.value AS year_id,
 286   projectprop.value AS year_name
 287    FROM projectprop
 288    WHERE projectprop.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'project year')
 289   GROUP BY public.projectprop.value
 290 WITH DATA;
 291 CREATE UNIQUE INDEX years_idx ON public.years(year_id) WITH (fillfactor=100);
 292 ALTER MATERIALIZED VIEW years OWNER TO web_usr;
 293
 294 -- add seedlots binary views and ADD BACK remaining BINARY VIEWS that were dropped during cascade
 295
 296 CREATE MATERIALIZED VIEW public.accessionsXseedlots AS
 297 SELECT public.materialized_phenoview.accession_id,
 298     public.stock.stock_id AS seedlot_id
 299    FROM public.materialized_phenoview
 300    LEFT JOIN stock_relationship seedlot_relationship ON materialized_phenoview.accession_id = seedlot_relationship.subject_id AND seedlot_relationship.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'collection_of')
 301    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')
 302   GROUP BY public.materialized_phenoview.accession_id,public.stock.stock_id
 303 WITH DATA;
 304 CREATE UNIQUE INDEX accessionsXseedlots_idx ON public.accessionsXseedlots(accession_id, seedlot_id) WITH (fillfactor=100);
 305 ALTER MATERIALIZED VIEW accessionsXseedlots OWNER TO web_usr;
 306
 307 CREATE MATERIALIZED VIEW public.breeding_programsXseedlots AS
 308 SELECT public.materialized_phenoview.breeding_program_id,
 309     public.nd_experiment_stock.stock_id AS seedlot_id
 310    FROM public.materialized_phenoview
 311    LEFT JOIN nd_experiment_project ON materialized_phenoview.breeding_program_id = nd_experiment_project.project_id
 312    LEFT JOIN nd_experiment ON nd_experiment_project.nd_experiment_id = nd_experiment.nd_experiment_id AND nd_experiment.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'seedlot_experiment')
 313    LEFT JOIN nd_experiment_stock ON nd_experiment.nd_experiment_id = nd_experiment_stock.nd_experiment_id
 314   GROUP BY 1,2
 315 WITH DATA;
 316 CREATE UNIQUE INDEX breeding_programsXseedlots_idx ON public.breeding_programsXseedlots(breeding_program_id, seedlot_id) WITH (fillfactor=100);
 317 ALTER MATERIALIZED VIEW breeding_programsXseedlots OWNER TO web_usr;
 318
 319 CREATE MATERIALIZED VIEW public.genotyping_protocolsXseedlots AS
 320 SELECT public.materialized_genoview.genotyping_protocol_id,
 321 public.stock.stock_id AS seedlot_id
 322 FROM public.materialized_genoview
 323 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')
 324 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')
 325   GROUP BY 1,2
 326 WITH DATA;
 327 CREATE UNIQUE INDEX genotyping_protocolsXseedlots_idx ON public.genotyping_protocolsXseedlots(genotyping_protocol_id, seedlot_id) WITH (fillfactor=100);
 328 ALTER MATERIALIZED VIEW genotyping_protocolsXseedlots OWNER TO web_usr;
 329
 330 CREATE MATERIALIZED VIEW public.locationsXseedlots AS
 331 SELECT public.nd_experiment.nd_geolocation_id AS location_id,
 332 public.nd_experiment_stock.stock_id AS seedlot_id
 333 FROM nd_experiment
 334 LEFT JOIN nd_experiment_stock ON nd_experiment.nd_experiment_id = nd_experiment_stock.nd_experiment_id
 335 WHERE nd_experiment.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'seedlot_experiment')
 336 GROUP BY 1,2
 337 WITH DATA;
 338 CREATE UNIQUE INDEX locationsXseedlots_idx ON public.locationsXseedlots(location_id, seedlot_id) WITH (fillfactor=100);
 339 ALTER MATERIALIZED VIEW locationsXseedlots OWNER TO web_usr;
 340
 341 CREATE MATERIALIZED VIEW public.plantsXseedlots AS
 342 SELECT public.stock.stock_id AS plant_id,
 343     public.materialized_phenoview.seedlot_id
 344    FROM public.materialized_phenoview
 345    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'))
 346   GROUP BY 1,2
 347 WITH DATA;
 348 CREATE UNIQUE INDEX plantsXseedlots_idx ON public.plantsXseedlots(plant_id, seedlot_id) WITH (fillfactor=100);
 349 ALTER MATERIALIZED VIEW plantsXseedlots OWNER TO web_usr;
 350
 351 CREATE MATERIALIZED VIEW public.plotsXseedlots AS
 352 SELECT public.stock.stock_id AS plot_id,
 353     public.materialized_phenoview.seedlot_id
 354    FROM public.materialized_phenoview
 355    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'))
 356   GROUP BY 1,2
 357 WITH DATA;
 358 CREATE UNIQUE INDEX plotsXseedlots_idx ON public.plotsXseedlots(plot_id, seedlot_id) WITH (fillfactor=100);
 359 ALTER MATERIALIZED VIEW plotsXseedlots OWNER TO web_usr;
 360
 361 CREATE MATERIALIZED VIEW public.seedlotsXtrait_components AS
 362 SELECT public.materialized_phenoview.seedlot_id,
 363 trait_component.cvterm_id AS trait_component_id
 364 FROM public.materialized_phenoview
 365 JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 366 JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 367 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 368 GROUP BY 1,2
 369 WITH DATA;
 370 CREATE UNIQUE INDEX seedlotsXtrait_components_idx ON public.seedlotsXtrait_components(seedlot_id, trait_component_id) WITH (fillfactor=100);
 371 ALTER MATERIALIZED VIEW seedlotsXtrait_components OWNER TO web_usr;
 372
 373 CREATE MATERIALIZED VIEW public.seedlotsXtraits AS
 374 SELECT public.materialized_phenoview.seedlot_id,
 375     public.materialized_phenoview.trait_id
 376    FROM public.materialized_phenoview
 377   GROUP BY 1,2
 378 WITH DATA;
 379 CREATE UNIQUE INDEX seedlotsXtraits_idx ON public.seedlotsXtraits(seedlot_id, trait_id) WITH (fillfactor=100);
 380 ALTER MATERIALIZED VIEW seedlotsXtraits OWNER TO web_usr;
 381
 382 CREATE MATERIALIZED VIEW public.seedlotsXtrials AS
 383 SELECT public.materialized_phenoview.seedlot_id,
 384     public.materialized_phenoview.trial_id
 385    FROM public.materialized_phenoview
 386   GROUP BY 1,2
 387 WITH DATA;
 388 CREATE UNIQUE INDEX seedlotsXtrials_idx ON public.seedlotsXtrials(seedlot_id, trial_id) WITH (fillfactor=100);
 389 ALTER MATERIALIZED VIEW seedlotsXtrials OWNER TO web_usr;
 390
 391 CREATE MATERIALIZED VIEW public.seedlotsXtrial_designs AS
 392 SELECT public.materialized_phenoview.seedlot_id,
 393     trialdesign.value AS trial_design_id
 394    FROM public.materialized_phenoview
 395    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' )
 396   GROUP BY 1,2
 397 WITH DATA;
 398 CREATE UNIQUE INDEX seedlotsXtrial_designs_idx ON public.seedlotsXtrial_designs(seedlot_id, trial_design_id) WITH (fillfactor=100);
 399 ALTER MATERIALIZED VIEW seedlotsXtrial_designs OWNER TO web_usr;
 400
 401 CREATE MATERIALIZED VIEW public.seedlotsXtrial_types AS
 402 SELECT public.materialized_phenoview.seedlot_id,
 403     trialterm.cvterm_id AS trial_type_id
 404    FROM public.materialized_phenoview
 405    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' )
 406    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 407   GROUP BY 1,2
 408 WITH DATA;
 409 CREATE UNIQUE INDEX seedlotsXtrial_types_idx ON public.seedlotsXtrial_types(seedlot_id, trial_type_id) WITH (fillfactor=100);
 410 ALTER MATERIALIZED VIEW seedlotsXtrial_types OWNER TO web_usr;
 411
 412 CREATE MATERIALIZED VIEW public.seedlotsXyears AS
 413 SELECT public.materialized_phenoview.seedlot_id,
 414     public.materialized_phenoview.year_id
 415    FROM public.materialized_phenoview
 416   GROUP BY 1,2
 417 WITH DATA;
 418 CREATE UNIQUE INDEX seedlotsXyears_idx ON public.seedlotsXyears(seedlot_id, year_id) WITH (fillfactor=100);
 419 ALTER MATERIALIZED VIEW seedlotsXyears OWNER TO web_usr;
 420
 421
 422 CREATE MATERIALIZED VIEW public.accessionsXtraits AS
 423 SELECT public.materialized_phenoview.accession_id,
 424     public.materialized_phenoview.trait_id
 425    FROM public.materialized_phenoview
 426   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.trait_id
 427 WITH DATA;
 428 CREATE UNIQUE INDEX accessionsXtraits_idx ON public.accessionsXtraits(accession_id, trait_id) WITH (fillfactor=100);
 429 ALTER MATERIALIZED VIEW accessionsXtraits OWNER TO web_usr;
 430
 431 CREATE MATERIALIZED VIEW public.breeding_programsXtraits AS
 432 SELECT public.materialized_phenoview.breeding_program_id,
 433     public.materialized_phenoview.trait_id
 434    FROM public.materialized_phenoview
 435   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_phenoview.trait_id
 436 WITH DATA;
 437 CREATE UNIQUE INDEX breeding_programsXtraits_idx ON public.breeding_programsXtraits(breeding_program_id, trait_id) WITH (fillfactor=100);
 438 ALTER MATERIALIZED VIEW breeding_programsXtraits OWNER TO web_usr;
 439
 440 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtraits AS
 441 SELECT public.materialized_genoview.genotyping_protocol_id,
 442     public.materialized_phenoview.trait_id
 443    FROM public.materialized_genoview
 444    JOIN public.materialized_phenoview USING(accession_id)
 445   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.trait_id
 446   WITH DATA;
 447 CREATE UNIQUE INDEX genotyping_protocolsXtraits_idx ON public.genotyping_protocolsXtraits(genotyping_protocol_id, trait_id) WITH (fillfactor=100);
 448 ALTER MATERIALIZED VIEW genotyping_protocolsXtraits OWNER TO web_usr;
 449
 450 CREATE MATERIALIZED VIEW public.locationsXtraits AS
 451 SELECT public.materialized_phenoview.location_id,
 452     public.materialized_phenoview.trait_id
 453    FROM public.materialized_phenoview
 454   GROUP BY public.materialized_phenoview.location_id, public.materialized_phenoview.trait_id
 455 WITH DATA;
 456 CREATE UNIQUE INDEX locationsXtraits_idx ON public.locationsXtraits(location_id, trait_id) WITH (fillfactor=100);
 457 ALTER MATERIALIZED VIEW locationsXtraits OWNER TO web_usr;
 458
 459 CREATE MATERIALIZED VIEW public.plantsXtraits AS
 460 SELECT public.stock.stock_id AS plant_id,
 461     public.materialized_phenoview.trait_id
 462    FROM public.materialized_phenoview
 463    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'))
 464   GROUP BY public.stock.stock_id, public.materialized_phenoview.trait_id
 465 WITH DATA;
 466 CREATE UNIQUE INDEX plantsXtraits_idx ON public.plantsXtraits(plant_id, trait_id) WITH (fillfactor=100);
 467 ALTER MATERIALIZED VIEW plantsXtraits OWNER TO web_usr;
 468
 469 CREATE MATERIALIZED VIEW public.plotsXtraits AS
 470 SELECT public.stock.stock_id AS plot_id,
 471     public.materialized_phenoview.trait_id
 472    FROM public.materialized_phenoview
 473    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'))
 474   GROUP BY public.stock.stock_id, public.materialized_phenoview.trait_id
 475 WITH DATA;
 476 CREATE UNIQUE INDEX plotsXtraits_idx ON public.plotsXtraits(plot_id, trait_id) WITH (fillfactor=100);
 477 ALTER MATERIALIZED VIEW plotsXtraits OWNER TO web_usr;
 478
 479 CREATE MATERIALIZED VIEW public.traitsXtrials AS
 480 SELECT public.materialized_phenoview.trait_id,
 481     public.materialized_phenoview.trial_id
 482    FROM public.materialized_phenoview
 483   GROUP BY public.materialized_phenoview.trait_id, public.materialized_phenoview.trial_id
 484 WITH DATA;
 485 CREATE UNIQUE INDEX traitsXtrials_idx ON public.traitsXtrials(trait_id, trial_id) WITH (fillfactor=100);
 486 ALTER MATERIALIZED VIEW traitsXtrials OWNER TO web_usr;
 487
 488 CREATE MATERIALIZED VIEW public.traitsXtrial_designs AS
 489 SELECT public.materialized_phenoview.trait_id,
 490     trialdesign.value AS trial_design_id
 491    FROM public.materialized_phenoview
 492    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' )
 493   GROUP BY public.materialized_phenoview.trait_id, trialdesign.value
 494 WITH DATA;
 495 CREATE UNIQUE INDEX traitsXtrial_designs_idx ON public.traitsXtrial_designs(trait_id, trial_design_id) WITH (fillfactor=100);
 496 ALTER MATERIALIZED VIEW traitsXtrial_designs OWNER TO web_usr;
 497
 498 CREATE MATERIALIZED VIEW public.traitsXtrial_types AS
 499 SELECT public.materialized_phenoview.trait_id,
 500     trialterm.cvterm_id AS trial_type_id
 501    FROM public.materialized_phenoview
 502    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' )
 503    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 504   GROUP BY public.materialized_phenoview.trait_id, trialterm.cvterm_id
 505 WITH DATA;
 506 CREATE UNIQUE INDEX traitsXtrial_types_idx ON public.traitsXtrial_types(trait_id, trial_type_id) WITH (fillfactor=100);
 507 ALTER MATERIALIZED VIEW traitsXtrial_types OWNER TO web_usr;
 508
 509 CREATE MATERIALIZED VIEW public.traitsXyears AS
 510 SELECT public.materialized_phenoview.trait_id,
 511     public.materialized_phenoview.year_id
 512    FROM public.materialized_phenoview
 513   GROUP BY public.materialized_phenoview.trait_id, public.materialized_phenoview.year_id
 514 WITH DATA;
 515 CREATE UNIQUE INDEX traitsXyears_idx ON public.traitsXyears(trait_id, year_id) WITH (fillfactor=100);
 516 ALTER MATERIALIZED VIEW traitsXyears OWNER TO web_usr;
 517
 518
 519
 520 CREATE MATERIALIZED VIEW public.accessionsXtrait_components AS
 521 SELECT public.materialized_phenoview.accession_id,
 522     trait_component.cvterm_id AS trait_component_id
 523    FROM public.materialized_phenoview
 524    JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 525    JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 526    JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 527   GROUP BY 1,2
 528 WITH DATA;
 529 CREATE UNIQUE INDEX accessionsXtrait_components_idx ON public.accessionsXtrait_components(accession_id, trait_component_id) WITH (fillfactor=100);
 530 ALTER MATERIALIZED VIEW accessionsXtrait_components OWNER TO web_usr;
 531 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('accessionsXtrait_components', FALSE, CURRENT_TIMESTAMP);
 532
 533 CREATE MATERIALIZED VIEW public.breeding_programsXtrait_components AS
 534 SELECT public.materialized_phenoview.breeding_program_id,
 535 trait_component.cvterm_id AS trait_component_id
 536 FROM public.materialized_phenoview
 537 JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 538 JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 539 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 540 GROUP BY 1,2
 541 WITH DATA;
 542 CREATE UNIQUE INDEX breeding_programsXtrait_components_idx ON public.breeding_programsXtrait_components(breeding_program_id, trait_component_id) WITH (fillfactor=100);
 543 ALTER MATERIALIZED VIEW breeding_programsXtrait_components OWNER TO web_usr;
 544 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('breeding_programsXtrait_components', FALSE, CURRENT_TIMESTAMP);
 545
 546 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrait_components AS
 547 SELECT public.materialized_genoview.genotyping_protocol_id,
 548 trait_component.cvterm_id AS trait_component_id
 549 FROM public.materialized_genoview
 550 JOIN public.materialized_phenoview USING(accession_id)
 551 JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 552 JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 553 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 554 GROUP BY 1,2
 555 WITH DATA;
 556 CREATE UNIQUE INDEX genotyping_protocolsXtrait_components_idx ON public.genotyping_protocolsXtrait_components(genotyping_protocol_id, trait_component_id) WITH (fillfactor=100);
 557 ALTER MATERIALIZED VIEW genotyping_protocolsXtrait_components OWNER TO web_usr;
 558 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('genotyping_protocolsXtrait_components', FALSE, CURRENT_TIMESTAMP);
 559
 560 CREATE MATERIALIZED VIEW public.locationsXtrait_components AS
 561 SELECT public.materialized_phenoview.location_id,
 562 trait_component.cvterm_id AS trait_component_id
 563 FROM public.materialized_phenoview
 564 JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 565 JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 566 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 567 GROUP BY 1,2
 568 WITH DATA;
 569 CREATE UNIQUE INDEX locationsXtrait_components_idx ON public.locationsXtrait_components(location_id, trait_component_id) WITH (fillfactor=100);
 570 ALTER MATERIALIZED VIEW locationsXtrait_components OWNER TO web_usr;
 571 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('locationsXtrait_components', FALSE, CURRENT_TIMESTAMP);
 572
 573 CREATE MATERIALIZED VIEW public.plantsXtrait_components AS
 574 SELECT public.stock.stock_id AS plant_id,
 575 trait_component.cvterm_id AS trait_component_id
 576 FROM public.materialized_phenoview
 577 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'))
 578 JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 579 JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 580 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 581 GROUP BY 1,2
 582 WITH DATA;
 583 CREATE UNIQUE INDEX plantsXtrait_components_idx ON public.plantsXtrait_components(plant_id, trait_component_id) WITH (fillfactor=100);
 584 ALTER MATERIALIZED VIEW plantsXtrait_components OWNER TO web_usr;
 585 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plantsXtrait_components', FALSE, CURRENT_TIMESTAMP);
 586
 587 CREATE MATERIALIZED VIEW public.plotsXtrait_components AS
 588 SELECT public.stock.stock_id AS plot_id,
 589 trait_component.cvterm_id AS trait_component_id
 590 FROM public.materialized_phenoview
 591 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'))
 592 JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 593 JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 594 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 595 GROUP BY 1,2
 596 WITH DATA;
 597 CREATE UNIQUE INDEX plotsXtrait_components_idx ON public.plotsXtrait_components(plot_id, trait_component_id) WITH (fillfactor=100);
 598 ALTER MATERIALIZED VIEW plotsXtrait_components OWNER TO web_usr;
 599 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plotsXtrait_components', FALSE, CURRENT_TIMESTAMP);
 600
 601 CREATE MATERIALIZED VIEW public.trait_componentsXtraits AS
 602 SELECT traits.trait_id,
 603 trait_component.cvterm_id AS trait_component_id
 604 FROM traits
 605 JOIN cvterm_relationship ON(traits.trait_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 606 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 607 GROUP BY 1,2
 608 WITH DATA;
 609 CREATE UNIQUE INDEX trait_componentsXtraits_idx ON public.trait_componentsXtraits(trait_component_id, trait_id) WITH (fillfactor=100);
 610 ALTER MATERIALIZED VIEW trait_componentsXtraits OWNER TO web_usr;
 611 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trait_componentsXtraits', FALSE, CURRENT_TIMESTAMP);
 612
 613 CREATE MATERIALIZED VIEW public.trait_componentsXtrials AS
 614 SELECT trait_component.cvterm_id AS trait_component_id,
 615     public.materialized_phenoview.trial_id
 616     FROM public.materialized_phenoview
 617     JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 618     JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 619     JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 620     GROUP BY 1,2
 621 WITH DATA;
 622 CREATE UNIQUE INDEX trait_componentsXtrials_idx ON public.trait_componentsXtrials(trait_component_id, trial_id) WITH (fillfactor=100);
 623 ALTER MATERIALIZED VIEW trait_componentsXtrials OWNER TO web_usr;
 624 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trait_componentsXtrials', FALSE, CURRENT_TIMESTAMP);
 625
 626 CREATE MATERIALIZED VIEW public.trait_componentsXtrial_designs AS
 627 SELECT trait_component.cvterm_id AS trait_component_id,
 628     trialdesign.value AS trial_design_id
 629    FROM public.materialized_phenoview
 630    JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 631    JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 632    JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 633    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' )
 634   GROUP BY 1,2
 635 WITH DATA;
 636 CREATE UNIQUE INDEX trait_componentsXtrial_designs_idx ON public.trait_componentsXtrial_designs(trait_component_id, trial_design_id) WITH (fillfactor=100);
 637 ALTER MATERIALIZED VIEW trait_componentsXtrial_designs OWNER TO web_usr;
 638 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trait_componentsXtrial_designs', FALSE, CURRENT_TIMESTAMP);
 639
 640 CREATE MATERIALIZED VIEW public.trait_componentsXtrial_types AS
 641 SELECT trait_component.cvterm_id AS trait_component_id,
 642     trialterm.cvterm_id AS trial_type_id
 643    FROM public.materialized_phenoview
 644    JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 645    JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 646    JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 647    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' )
 648    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 649   GROUP BY 1,2
 650 WITH DATA;
 651 CREATE UNIQUE INDEX trait_componentsXtrial_types_idx ON public.trait_componentsXtrial_types(trait_component_id, trial_type_id) WITH (fillfactor=100);
 652 ALTER MATERIALIZED VIEW trait_componentsXtrial_types OWNER TO web_usr;
 653 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trait_componentsXtrial_types', FALSE, CURRENT_TIMESTAMP);
 654
 655 CREATE MATERIALIZED VIEW public.trait_componentsXyears AS
 656 SELECT trait_component.cvterm_id AS trait_component_id,
 657     public.materialized_phenoview.year_id
 658    FROM public.materialized_phenoview
 659    JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 660    JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 661    JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 662   GROUP BY 1,2
 663 WITH DATA;
 664 CREATE UNIQUE INDEX trait_componentsXyears_idx ON public.trait_componentsXyears(trait_component_id, year_id) WITH (fillfactor=100);
 665 ALTER MATERIALIZED VIEW trait_componentsXyears OWNER TO web_usr;
 666 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trait_componentsXyears', FALSE, CURRENT_TIMESTAMP);
 667
 668
 669 -- FIX VIEWS FOR PLANTS, PLOTS, TRIAL DESIGNS AND TRIAL TYPES
 670
 671 DROP MATERIALIZED VIEW IF EXISTS public.accessions;
 672 CREATE MATERIALIZED VIEW public.accessions AS
 673   SELECT stock.stock_id AS accession_id,
 674   stock.uniquename AS accession_name
 675   FROM stock
 676   WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'accession') AND is_obsolete = 'f'
 677   GROUP BY stock.stock_id, stock.uniquename
 678   WITH DATA;
 679 CREATE UNIQUE INDEX accessions_idx ON public.accessions(accession_id) WITH (fillfactor=100);
 680 ALTER MATERIALIZED VIEW accessions OWNER TO web_usr;
 681
 682 CREATE MATERIALIZED VIEW public.accessionsXbreeding_programs AS
 683 SELECT public.materialized_phenoview.accession_id,
 684     public.materialized_phenoview.breeding_program_id
 685    FROM public.materialized_phenoview
 686   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.breeding_program_id
 687   WITH DATA;
 688 CREATE UNIQUE INDEX accessionsXbreeding_programs_idx ON public.accessionsXbreeding_programs(accession_id, breeding_program_id) WITH (fillfactor=100);
 689 ALTER MATERIALIZED VIEW accessionsXbreeding_programs OWNER TO web_usr;
 690
 691 CREATE MATERIALIZED VIEW public.accessionsXgenotyping_protocols AS
 692 SELECT public.materialized_genoview.accession_id,
 693     public.materialized_genoview.genotyping_protocol_id
 694    FROM public.materialized_genoview
 695   GROUP BY public.materialized_genoview.accession_id, public.materialized_genoview.genotyping_protocol_id
 696   WITH DATA;
 697 CREATE UNIQUE INDEX accessionsXgenotyping_protocols_idx ON public.accessionsXgenotyping_protocols(accession_id, genotyping_protocol_id) WITH (fillfactor=100);
 698 ALTER MATERIALIZED VIEW accessionsXgenotyping_protocols OWNER TO web_usr;
 699
 700 CREATE MATERIALIZED VIEW public.accessionsXlocations AS
 701 SELECT public.materialized_phenoview.accession_id,
 702     public.materialized_phenoview.location_id
 703    FROM public.materialized_phenoview
 704   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.location_id
 705   WITH DATA;
 706 CREATE UNIQUE INDEX accessionsXlocations_idx ON public.accessionsXlocations(accession_id, location_id) WITH (fillfactor=100);
 707 ALTER MATERIALIZED VIEW accessionsXlocations OWNER TO web_usr;
 708
 709 CREATE MATERIALIZED VIEW public.accessionsXplants AS
 710 SELECT public.materialized_phenoview.accession_id,
 711     public.stock.stock_id AS plant_id
 712    FROM public.materialized_phenoview
 713    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'))
 714   GROUP BY public.materialized_phenoview.accession_id, public.stock.stock_id
 715   WITH DATA;
 716 CREATE UNIQUE INDEX accessionsXplants_idx ON public.accessionsXplants(accession_id, plant_id) WITH (fillfactor=100);
 717 ALTER MATERIALIZED VIEW accessionsXplants OWNER TO web_usr;
 718
 719 CREATE MATERIALIZED VIEW public.accessionsXplots AS
 720 SELECT public.materialized_phenoview.accession_id,
 721     public.stock.stock_id AS plot_id
 722    FROM public.materialized_phenoview
 723    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'))
 724   GROUP BY public.materialized_phenoview.accession_id, public.stock.stock_id
 725 WITH DATA;
 726 CREATE UNIQUE INDEX accessionsXplots_idx ON public.accessionsXplots(accession_id, plot_id) WITH (fillfactor=100);
 727 ALTER MATERIALIZED VIEW accessionsXplots OWNER TO web_usr;
 728
 729 CREATE MATERIALIZED VIEW public.accessionsXtrial_designs AS
 730 SELECT public.materialized_phenoview.accession_id,
 731     trialdesign.value AS trial_design_id
 732    FROM public.materialized_phenoview
 733    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' )
 734   GROUP BY public.materialized_phenoview.accession_id, trialdesign.value
 735 WITH DATA;
 736 CREATE UNIQUE INDEX accessionsXtrial_designs_idx ON public.accessionsXtrial_designs(accession_id, trial_design_id) WITH (fillfactor=100);
 737 ALTER MATERIALIZED VIEW accessionsXtrial_designs OWNER TO web_usr;
 738
 739 CREATE MATERIALIZED VIEW public.accessionsXtrial_types AS
 740 SELECT public.materialized_phenoview.accession_id,
 741     trialterm.cvterm_id AS trial_type_id
 742    FROM public.materialized_phenoview
 743    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' )
 744    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 745   GROUP BY public.materialized_phenoview.accession_id, trialterm.cvterm_id
 746 WITH DATA;
 747 CREATE UNIQUE INDEX accessionsXtrial_types_idx ON public.accessionsXtrial_types(accession_id, trial_type_id) WITH (fillfactor=100);
 748 ALTER MATERIALIZED VIEW accessionsXtrial_types OWNER TO web_usr;
 749
 750 CREATE MATERIALIZED VIEW public.accessionsXtrials AS
 751 SELECT public.materialized_phenoview.accession_id,
 752     public.materialized_phenoview.trial_id
 753    FROM public.materialized_phenoview
 754   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.trial_id
 755 WITH DATA;
 756 CREATE UNIQUE INDEX accessionsXtrials_idx ON public.accessionsXtrials(accession_id, trial_id) WITH (fillfactor=100);
 757 ALTER MATERIALIZED VIEW accessionsXtrials OWNER TO web_usr;
 758
 759 CREATE MATERIALIZED VIEW public.accessionsXyears AS
 760 SELECT public.materialized_phenoview.accession_id,
 761     public.materialized_phenoview.year_id
 762    FROM public.materialized_phenoview
 763   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.year_id
 764 WITH DATA;
 765 CREATE UNIQUE INDEX accessionsXyears_idx ON public.accessionsXyears(accession_id, year_id) WITH (fillfactor=100);
 766 ALTER MATERIALIZED VIEW accessionsXyears OWNER TO web_usr;
 767
 768
 769 CREATE MATERIALIZED VIEW public.breeding_programsXgenotyping_protocols AS
 770 SELECT public.materialized_phenoview.breeding_program_id,
 771     public.materialized_genoview.genotyping_protocol_id
 772    FROM public.materialized_phenoview
 773    JOIN public.materialized_genoview USING(accession_id)
 774   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_genoview.genotyping_protocol_id
 775 WITH DATA;
 776 CREATE UNIQUE INDEX breeding_programsXgenotyping_protocols_idx ON public.breeding_programsXgenotyping_protocols(breeding_program_id, genotyping_protocol_id) WITH (fillfactor=100);
 777 ALTER MATERIALIZED VIEW breeding_programsXgenotyping_protocols OWNER TO web_usr;
 778
 779 CREATE MATERIALIZED VIEW public.breeding_programsXlocations AS
 780 SELECT public.materialized_phenoview.breeding_program_id,
 781     public.materialized_phenoview.location_id
 782    FROM public.materialized_phenoview
 783   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_phenoview.location_id
 784 WITH DATA;
 785 CREATE UNIQUE INDEX breeding_programsXlocations_idx ON public.breeding_programsXlocations(breeding_program_id, location_id) WITH (fillfactor=100);
 786 ALTER MATERIALIZED VIEW breeding_programsXlocations OWNER TO web_usr;
 787
 788 CREATE MATERIALIZED VIEW public.breeding_programsXplants AS
 789 SELECT public.materialized_phenoview.breeding_program_id,
 790     public.stock.stock_id AS plant_id
 791    FROM public.materialized_phenoview
 792    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'))
 793   GROUP BY public.materialized_phenoview.breeding_program_id, public.stock.stock_id
 794   WITH DATA;
 795 CREATE UNIQUE INDEX breeding_programsXplants_idx ON public.breeding_programsXplants(breeding_program_id, plant_id) WITH (fillfactor=100);
 796 ALTER MATERIALIZED VIEW breeding_programsXplants OWNER TO web_usr;
 797
 798 CREATE MATERIALIZED VIEW public.breeding_programsXplots AS
 799 SELECT public.materialized_phenoview.breeding_program_id,
 800     public.stock.stock_id AS plot_id
 801    FROM public.materialized_phenoview
 802    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'))
 803   GROUP BY public.materialized_phenoview.breeding_program_id, public.stock.stock_id
 804 WITH DATA;
 805 CREATE UNIQUE INDEX breeding_programsXplots_idx ON public.breeding_programsXplots(breeding_program_id, plot_id) WITH (fillfactor=100);
 806 ALTER MATERIALIZED VIEW breeding_programsXplots OWNER TO web_usr;
 807
 808 CREATE MATERIALIZED VIEW public.breeding_programsXtrial_designs AS
 809 SELECT public.materialized_phenoview.breeding_program_id,
 810     trialdesign.value AS trial_design_id
 811    FROM public.materialized_phenoview
 812    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' )
 813   GROUP BY public.materialized_phenoview.breeding_program_id, trialdesign.value
 814 WITH DATA;
 815 CREATE UNIQUE INDEX breeding_programsXtrial_designs_idx ON public.breeding_programsXtrial_designs(breeding_program_id, trial_design_id) WITH (fillfactor=100);
 816 ALTER MATERIALIZED VIEW breeding_programsXtrial_designs OWNER TO web_usr;
 817
 818 CREATE MATERIALIZED VIEW public.breeding_programsXtrial_types AS
 819 SELECT public.materialized_phenoview.breeding_program_id,
 820     trialterm.cvterm_id AS trial_type_id
 821    FROM public.materialized_phenoview
 822    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' )
 823    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 824   GROUP BY public.materialized_phenoview.breeding_program_id, trialterm.cvterm_id
 825 WITH DATA;
 826 CREATE UNIQUE INDEX breeding_programsXtrial_types_idx ON public.breeding_programsXtrial_types(breeding_program_id, trial_type_id) WITH (fillfactor=100);
 827 ALTER MATERIALIZED VIEW breeding_programsXtrial_types OWNER TO web_usr;
 828
 829 CREATE MATERIALIZED VIEW public.breeding_programsXtrials AS
 830 SELECT public.materialized_phenoview.breeding_program_id,
 831     public.materialized_phenoview.trial_id
 832    FROM public.materialized_phenoview
 833   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_phenoview.trial_id
 834 WITH DATA;
 835 CREATE UNIQUE INDEX breeding_programsXtrials_idx ON public.breeding_programsXtrials(breeding_program_id, trial_id) WITH (fillfactor=100);
 836 ALTER MATERIALIZED VIEW breeding_programsXtrials OWNER TO web_usr;
 837
 838 CREATE MATERIALIZED VIEW public.breeding_programsXyears AS
 839 SELECT public.materialized_phenoview.breeding_program_id,
 840     public.materialized_phenoview.year_id
 841    FROM public.materialized_phenoview
 842   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_phenoview.year_id
 843 WITH DATA;
 844 CREATE UNIQUE INDEX breeding_programsXyears_idx ON public.breeding_programsXyears(breeding_program_id, year_id) WITH (fillfactor=100);
 845 ALTER MATERIALIZED VIEW breeding_programsXyears OWNER TO web_usr;
 846
 847
 848 CREATE MATERIALIZED VIEW public.genotyping_protocolsXlocations AS
 849 SELECT public.materialized_genoview.genotyping_protocol_id,
 850     public.materialized_phenoview.location_id
 851    FROM public.materialized_genoview
 852    JOIN public.materialized_phenoview USING(accession_id)
 853   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.location_id
 854 WITH DATA;
 855 CREATE UNIQUE INDEX genotyping_protocolsXlocations_idx ON public.genotyping_protocolsXlocations(genotyping_protocol_id, location_id) WITH (fillfactor=100);
 856 ALTER MATERIALIZED VIEW genotyping_protocolsXlocations OWNER TO web_usr;
 857
 858 CREATE MATERIALIZED VIEW public.genotyping_protocolsXplants AS
 859 SELECT public.materialized_genoview.genotyping_protocol_id,
 860     public.stock.stock_id AS plant_id
 861    FROM public.materialized_genoview
 862    JOIN public.materialized_phenoview USING(accession_id)
 863    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'))
 864   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.stock.stock_id
 865   WITH DATA;
 866 CREATE UNIQUE INDEX genotyping_protocolsXplants_idx ON public.genotyping_protocolsXplants(genotyping_protocol_id, plant_id) WITH (fillfactor=100);
 867 ALTER MATERIALIZED VIEW genotyping_protocolsXplants OWNER TO web_usr;
 868
 869 CREATE MATERIALIZED VIEW public.genotyping_protocolsXplots AS
 870 SELECT public.materialized_genoview.genotyping_protocol_id,
 871     public.stock.stock_id AS plot_id
 872     FROM public.materialized_genoview
 873     JOIN public.materialized_phenoview USING(accession_id)
 874    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'))
 875   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.stock.stock_id
 876 WITH DATA;
 877 CREATE UNIQUE INDEX genotyping_protocolsXplots_idx ON public.genotyping_protocolsXplots(genotyping_protocol_id, plot_id) WITH (fillfactor=100);
 878 ALTER MATERIALIZED VIEW genotyping_protocolsXplots OWNER TO web_usr;
 879
 880 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrial_designs AS
 881 SELECT public.materialized_genoview.genotyping_protocol_id,
 882     trialdesign.value AS trial_design_id
 883     FROM public.materialized_genoview
 884     JOIN public.materialized_phenoview USING(accession_id)
 885    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' )
 886   GROUP BY public.materialized_genoview.genotyping_protocol_id, trialdesign.value
 887 WITH DATA;
 888 CREATE UNIQUE INDEX genotyping_protocolsXtrial_designs_idx ON public.genotyping_protocolsXtrial_designs(genotyping_protocol_id, trial_design_id) WITH (fillfactor=100);
 889 ALTER MATERIALIZED VIEW genotyping_protocolsXtrial_designs OWNER TO web_usr;
 890
 891 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrial_types AS
 892 SELECT public.materialized_genoview.genotyping_protocol_id,
 893     trialterm.cvterm_id AS trial_type_id
 894     FROM public.materialized_genoview
 895     JOIN public.materialized_phenoview USING(accession_id)
 896    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' )
 897    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 898   GROUP BY public.materialized_genoview.genotyping_protocol_id, trialterm.cvterm_id
 899 WITH DATA;
 900 CREATE UNIQUE INDEX genotyping_protocolsXtrial_types_idx ON public.genotyping_protocolsXtrial_types(genotyping_protocol_id, trial_type_id) WITH (fillfactor=100);
 901 ALTER MATERIALIZED VIEW genotyping_protocolsXtrial_types OWNER TO web_usr;
 902
 903 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrials AS
 904 SELECT public.materialized_genoview.genotyping_protocol_id,
 905     public.materialized_phenoview.trial_id
 906    FROM public.materialized_genoview
 907    JOIN public.materialized_phenoview USING(accession_id)
 908   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.trial_id
 909 WITH DATA;
 910 CREATE UNIQUE INDEX genotyping_protocolsXtrials_idx ON public.genotyping_protocolsXtrials(genotyping_protocol_id, trial_id) WITH (fillfactor=100);
 911 ALTER MATERIALIZED VIEW genotyping_protocolsXtrials OWNER TO web_usr;
 912
 913 CREATE MATERIALIZED VIEW public.genotyping_protocolsXyears AS
 914 SELECT public.materialized_genoview.genotyping_protocol_id,
 915     public.materialized_phenoview.year_id
 916    FROM public.materialized_genoview
 917    JOIN public.materialized_phenoview USING(accession_id)
 918   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.year_id
 919 WITH DATA;
 920 CREATE UNIQUE INDEX genotyping_protocolsXyears_idx ON public.genotyping_protocolsXyears(genotyping_protocol_id, year_id) WITH (fillfactor=100);
 921 ALTER MATERIALIZED VIEW genotyping_protocolsXyears OWNER TO web_usr;
 922
 923
 924
 925 CREATE MATERIALIZED VIEW public.locationsXplants AS
 926 SELECT public.materialized_phenoview.location_id,
 927     public.stock.stock_id AS plant_id
 928    FROM public.materialized_phenoview
 929    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'))
 930   GROUP BY public.materialized_phenoview.location_id, public.stock.stock_id
 931   WITH DATA;
 932 CREATE UNIQUE INDEX locationsXplants_idx ON public.locationsXplants(location_id, plant_id) WITH (fillfactor=100);
 933 ALTER MATERIALIZED VIEW locationsXplants OWNER TO web_usr;
 934
 935 CREATE MATERIALIZED VIEW public.locationsXplots AS
 936 SELECT public.materialized_phenoview.location_id,
 937     public.stock.stock_id AS plot_id
 938    FROM public.materialized_phenoview
 939    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'))
 940   GROUP BY public.materialized_phenoview.location_id, public.stock.stock_id
 941 WITH DATA;
 942 CREATE UNIQUE INDEX locationsXplots_idx ON public.locationsXplots(location_id, plot_id) WITH (fillfactor=100);
 943 ALTER MATERIALIZED VIEW locationsXplots OWNER TO web_usr;
 944
 945 CREATE MATERIALIZED VIEW public.locationsXtrial_designs AS
 946 SELECT public.materialized_phenoview.location_id,
 947     trialdesign.value AS trial_design_id
 948    FROM public.materialized_phenoview
 949    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' )
 950   GROUP BY public.materialized_phenoview.location_id, trialdesign.value
 951 WITH DATA;
 952 CREATE UNIQUE INDEX locationsXtrial_designs_idx ON public.locationsXtrial_designs(location_id, trial_design_id) WITH (fillfactor=100);
 953 ALTER MATERIALIZED VIEW locationsXtrial_designs OWNER TO web_usr;
 954
 955 CREATE MATERIALIZED VIEW public.locationsXtrial_types AS
 956 SELECT public.materialized_phenoview.location_id,
 957     trialterm.cvterm_id AS trial_type_id
 958    FROM public.materialized_phenoview
 959    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' )
 960    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 961   GROUP BY public.materialized_phenoview.location_id, trialterm.cvterm_id
 962 WITH DATA;
 963 CREATE UNIQUE INDEX locationsXtrial_types_idx ON public.locationsXtrial_types(location_id, trial_type_id) WITH (fillfactor=100);
 964 ALTER MATERIALIZED VIEW locationsXtrial_types OWNER TO web_usr;
 965
 966 CREATE MATERIALIZED VIEW public.locationsXtrials AS
 967 SELECT public.materialized_phenoview.location_id,
 968     public.materialized_phenoview.trial_id
 969    FROM public.materialized_phenoview
 970   GROUP BY public.materialized_phenoview.location_id, public.materialized_phenoview.trial_id
 971 WITH DATA;
 972 CREATE UNIQUE INDEX locationsXtrials_idx ON public.locationsXtrials(location_id, trial_id) WITH (fillfactor=100);
 973 ALTER MATERIALIZED VIEW locationsXtrials OWNER TO web_usr;
 974
 975 CREATE MATERIALIZED VIEW public.locationsXyears AS
 976 SELECT public.materialized_phenoview.location_id,
 977     public.materialized_phenoview.year_id
 978    FROM public.materialized_phenoview
 979   GROUP BY public.materialized_phenoview.location_id, public.materialized_phenoview.year_id
 980 WITH DATA;
 981 CREATE UNIQUE INDEX locationsXyears_idx ON public.locationsXyears(location_id, year_id) WITH (fillfactor=100);
 982 ALTER MATERIALIZED VIEW locationsXyears OWNER TO web_usr;
 983
 984
 985
 986 DROP MATERIALIZED VIEW IF EXISTS public.plants;
 987 CREATE MATERIALIZED VIEW public.plants AS
 988 SELECT stock.stock_id AS plant_id,
 989     stock.uniquename AS plant_name
 990    FROM stock
 991    WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plant') AND is_obsolete = 'f'
 992   GROUP BY public.stock.stock_id, public.stock.uniquename
 993 WITH DATA;
 994 CREATE UNIQUE INDEX plants_idx ON public.plants(plant_id) WITH (fillfactor=100);
 995 ALTER MATERIALIZED VIEW plants OWNER TO web_usr;
 996
 997 CREATE MATERIALIZED VIEW public.plantsXplots AS
 998 SELECT plant.stock_id AS plant_id,
 999     plot.stock_id AS plot_id
1000    FROM public.materialized_phenoview
1001    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'))
1002    JOIN stock_relationship plant_relationship ON plot.stock_id = plant_relationship.subject_id
1003    JOIN stock plant ON plant_relationship.object_id = plant.stock_id AND plant.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plant')
1004   GROUP BY plant.stock_id, plot.stock_id
1005 WITH DATA;
1006 CREATE UNIQUE INDEX plantsXplots_idx ON public.plantsXplots(plant_id, plot_id) WITH (fillfactor=100);
1007 ALTER MATERIALIZED VIEW plantsXplots OWNER TO web_usr;
1008 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plantsXplots', FALSE, CURRENT_TIMESTAMP);
1009
1010 CREATE MATERIALIZED VIEW public.plantsXtrials AS
1011 SELECT public.stock.stock_id AS plant_id,
1012     public.materialized_phenoview.trial_id
1013    FROM public.materialized_phenoview
1014    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'))
1015   GROUP BY public.stock.stock_id, public.materialized_phenoview.trial_id
1016 WITH DATA;
1017 CREATE UNIQUE INDEX plantsXtrials_idx ON public.plantsXtrials(plant_id, trial_id) WITH (fillfactor=100);
1018 ALTER MATERIALIZED VIEW plantsXtrials OWNER TO web_usr;
1019
1020 CREATE MATERIALIZED VIEW public.plantsXtrial_designs AS
1021 SELECT public.stock.stock_id AS plant_id,
1022     trialdesign.value AS trial_design_id
1023    FROM public.materialized_phenoview
1024    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'))
1025    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' )
1026   GROUP BY stock.stock_id, trialdesign.value
1027 WITH DATA;
1028 CREATE UNIQUE INDEX plantsXtrial_designs_idx ON public.plantsXtrial_designs(plant_id, trial_design_id) WITH (fillfactor=100);
1029 ALTER MATERIALIZED VIEW plantsXtrial_designs OWNER TO web_usr;
1030
1031 CREATE MATERIALIZED VIEW public.plantsXtrial_types AS
1032 SELECT public.stock.stock_id AS plant_id,
1033     trialterm.cvterm_id AS trial_type_id
1034    FROM public.materialized_phenoview
1035    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'))
1036    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' )
1037    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
1038   GROUP BY public.stock.stock_id, trialterm.cvterm_id
1039 WITH DATA;
1040 CREATE UNIQUE INDEX plantsXtrial_types_idx ON public.plantsXtrial_types(plant_id, trial_type_id) WITH (fillfactor=100);
1041 ALTER MATERIALIZED VIEW plantsXtrial_types OWNER TO web_usr;
1042
1043 CREATE MATERIALIZED VIEW public.plantsXyears AS
1044 SELECT public.stock.stock_id AS plant_id,
1045     public.materialized_phenoview.year_id
1046    FROM public.materialized_phenoview
1047    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'))
1048   GROUP BY public.stock.stock_id, public.materialized_phenoview.year_id
1049 WITH DATA;
1050 CREATE UNIQUE INDEX plantsXyears_idx ON public.plantsXyears(plant_id, year_id) WITH (fillfactor=100);
1051 ALTER MATERIALIZED VIEW plantsXyears OWNER TO web_usr;
1052
1053
1054
1055 DROP MATERIALIZED VIEW IF EXISTS public.plots;
1056 CREATE MATERIALIZED VIEW public.plots AS
1057 SELECT stock.stock_id AS plot_id,
1058     stock.uniquename AS plot_name
1059    FROM stock
1060    WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plot') AND is_obsolete = 'f'
1061   GROUP BY public.stock.stock_id, public.stock.uniquename
1062 WITH DATA;
1063 CREATE UNIQUE INDEX plots_idx ON public.plots(plot_id) WITH (fillfactor=100);
1064 ALTER MATERIALIZED VIEW plots OWNER TO web_usr;
1065
1066 CREATE MATERIALIZED VIEW public.plotsXtrials AS
1067 SELECT public.stock.stock_id AS plot_id,
1068     public.materialized_phenoview.trial_id
1069    FROM public.materialized_phenoview
1070    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'))
1071   GROUP BY public.stock.stock_id, public.materialized_phenoview.trial_id
1072 WITH DATA;
1073 CREATE UNIQUE INDEX plotsXtrials_idx ON public.plotsXtrials(plot_id, trial_id) WITH (fillfactor=100);
1074 ALTER MATERIALIZED VIEW plotsXtrials OWNER TO web_usr;
1075
1076 CREATE MATERIALIZED VIEW public.plotsXtrial_designs AS
1077 SELECT public.stock.stock_id AS plot_id,
1078     trialdesign.value AS trial_design_id
1079    FROM public.materialized_phenoview
1080    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'))
1081    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' )
1082   GROUP BY stock.stock_id, trialdesign.value
1083 WITH DATA;
1084 CREATE UNIQUE INDEX plotsXtrial_designs_idx ON public.plotsXtrial_designs(plot_id, trial_design_id) WITH (fillfactor=100);
1085 ALTER MATERIALIZED VIEW plotsXtrial_designs OWNER TO web_usr;
1086
1087 CREATE MATERIALIZED VIEW public.plotsXtrial_types AS
1088 SELECT public.stock.stock_id AS plot_id,
1089     trialterm.cvterm_id AS trial_type_id
1090    FROM public.materialized_phenoview
1091    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'))
1092    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' )
1093    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
1094   GROUP BY public.stock.stock_id, trialterm.cvterm_id
1095 WITH DATA;
1096 CREATE UNIQUE INDEX plotsXtrial_types_idx ON public.plotsXtrial_types(plot_id, trial_type_id) WITH (fillfactor=100);
1097 ALTER MATERIALIZED VIEW plotsXtrial_types OWNER TO web_usr;
1098
1099 CREATE MATERIALIZED VIEW public.plotsXyears AS
1100 SELECT public.stock.stock_id AS plot_id,
1101     public.materialized_phenoview.year_id
1102    FROM public.materialized_phenoview
1103    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'))
1104   GROUP BY public.stock.stock_id, public.materialized_phenoview.year_id
1105 WITH DATA;
1106 CREATE UNIQUE INDEX plotsXyears_idx ON public.plotsXyears(plot_id, year_id) WITH (fillfactor=100);
1107 ALTER MATERIALIZED VIEW plotsXyears OWNER TO web_usr;
1108
1109
1110
1111 DROP MATERIALIZED VIEW IF EXISTS public.trial_designs;
1112 CREATE MATERIALIZED VIEW public.trial_designs AS
1113 SELECT projectprop.value AS trial_design_id,
1114   projectprop.value AS trial_design_name
1115    FROM projectprop
1116    JOIN cvterm ON(projectprop.type_id = cvterm.cvterm_id)
1117    WHERE cvterm.name = 'design'
1118    GROUP BY projectprop.value
1119 WITH DATA;
1120 CREATE UNIQUE INDEX trial_designs_idx ON public.trial_designs(trial_design_id) WITH (fillfactor=100);
1121 ALTER MATERIALIZED VIEW trial_designs OWNER TO web_usr;
1122
1123 CREATE MATERIALIZED VIEW public.trial_designsXtrial_types AS
1124 SELECT trialdesign.value AS trial_design_id,
1125     trialterm.cvterm_id AS trial_type_id
1126    FROM public.materialized_phenoview
1127    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' )
1128    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' )
1129    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
1130   GROUP BY trialdesign.value, trialterm.cvterm_id
1131 WITH DATA;
1132 CREATE UNIQUE INDEX trial_designsXtrial_types_idx ON public.trial_designsXtrial_types(trial_design_id, trial_type_id) WITH (fillfactor=100);
1133 ALTER MATERIALIZED VIEW trial_designsXtrial_types OWNER TO web_usr;
1134
1135 CREATE MATERIALIZED VIEW public.trial_designsXtrials AS
1136 SELECT trialdesign.value AS trial_design_id,
1137     public.materialized_phenoview.trial_id
1138    FROM public.materialized_phenoview
1139    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' )
1140   GROUP BY trialdesign.value, public.materialized_phenoview.trial_id
1141 WITH DATA;
1142 CREATE UNIQUE INDEX trial_designsXtrials_idx ON public.trial_designsXtrials(trial_id, trial_design_id) WITH (fillfactor=100);
1143 ALTER MATERIALIZED VIEW trial_designsXtrials OWNER TO web_usr;
1144
1145 CREATE MATERIALIZED VIEW public.trial_designsXyears AS
1146 SELECT trialdesign.value AS trial_design_id,
1147     public.materialized_phenoview.year_id
1148    FROM public.materialized_phenoview
1149    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' )
1150   GROUP BY trialdesign.value, public.materialized_phenoview.year_id
1151 WITH DATA;
1152 CREATE UNIQUE INDEX trial_designsXyears_idx ON public.trial_designsXyears(trial_design_id, year_id) WITH (fillfactor=100);
1153 ALTER MATERIALIZED VIEW trial_designsXyears OWNER TO web_usr;
1154
1155
1156
1157 DROP MATERIALIZED VIEW IF EXISTS public.trial_types;
1158 CREATE MATERIALIZED VIEW public.trial_types AS
1159 SELECT cvterm.cvterm_id AS trial_type_id,
1160   cvterm.name AS trial_type_name
1161    FROM cvterm
1162    JOIN cv USING(cv_id)
1163    WHERE cv.name = 'project_type'
1164    GROUP BY cvterm.cvterm_id
1165 WITH DATA;
1166 CREATE UNIQUE INDEX trial_types_idx ON public.trial_types(trial_type_id) WITH (fillfactor=100);
1167 ALTER MATERIALIZED VIEW trial_types OWNER TO web_usr;
1168
1169 CREATE MATERIALIZED VIEW public.trial_typesXtrials AS
1170 SELECT trialterm.cvterm_id AS trial_type_id,
1171     public.materialized_phenoview.trial_id
1172    FROM public.materialized_phenoview
1173    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' )
1174    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
1175   GROUP BY trialterm.cvterm_id, public.materialized_phenoview.trial_id
1176 WITH DATA;
1177 CREATE UNIQUE INDEX trial_typesXtrials_idx ON public.trial_typesXtrials(trial_id, trial_type_id) WITH (fillfactor=100);
1178 ALTER MATERIALIZED VIEW trial_typesXtrials OWNER TO web_usr;
1179
1180 CREATE MATERIALIZED VIEW public.trial_typesXyears AS
1181 SELECT trialterm.cvterm_id AS trial_type_id,
1182     public.materialized_phenoview.year_id
1183    FROM public.materialized_phenoview
1184    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' )
1185    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
1186   GROUP BY trialterm.cvterm_id, public.materialized_phenoview.year_id
1187 WITH DATA;
1188 CREATE UNIQUE INDEX trial_typesXyears_idx ON public.trial_typesXyears(trial_type_id, year_id) WITH (fillfactor=100);
1189 ALTER MATERIALIZED VIEW trial_typesXyears OWNER TO web_usr;
1190
1191
1192
1193 CREATE MATERIALIZED VIEW public.trialsXyears AS
1194 SELECT public.materialized_phenoview.trial_id,
1195     public.materialized_phenoview.year_id
1196    FROM public.materialized_phenoview
1197   GROUP BY public.materialized_phenoview.trial_id, public.materialized_phenoview.year_id
1198 WITH DATA;
1199 CREATE UNIQUE INDEX trialsXyears_idx ON public.trialsXyears(trial_id, year_id) WITH (fillfactor=100);
1200 ALTER MATERIALIZED VIEW trialsXyears OWNER TO web_usr;
1201
1202 CREATE OR REPLACE FUNCTION public.refresh_materialized_views() RETURNS VOID AS '
1203 REFRESH MATERIALIZED VIEW public.materialized_phenoview;
1204 REFRESH MATERIALIZED VIEW public.materialized_genoview;
1205 REFRESH MATERIALIZED VIEW public.accessions;
1206 REFRESH MATERIALIZED VIEW public.breeding_programs;
1207 REFRESH MATERIALIZED VIEW public.genotyping_protocols;
1208 REFRESH MATERIALIZED VIEW public.locations;
1209 REFRESH MATERIALIZED VIEW public.plants;
1210 REFRESH MATERIALIZED VIEW public.plots;
1211 REFRESH MATERIALIZED VIEW public.seedlots;
1212 REFRESH MATERIALIZED VIEW public.trait_components;
1213 REFRESH MATERIALIZED VIEW public.traits;
1214 REFRESH MATERIALIZED VIEW public.trial_designs;
1215 REFRESH MATERIALIZED VIEW public.trial_types;
1216 REFRESH MATERIALIZED VIEW public.trials;
1217 REFRESH MATERIALIZED VIEW public.years;
1218 REFRESH MATERIALIZED VIEW public.accessionsXbreeding_programs;
1219 REFRESH MATERIALIZED VIEW public.accessionsXlocations;
1220 REFRESH MATERIALIZED VIEW public.accessionsXgenotyping_protocols;
1221 REFRESH MATERIALIZED VIEW public.accessionsXplants;
1222 REFRESH MATERIALIZED VIEW public.accessionsXplots;
1223 REFRESH MATERIALIZED VIEW public.accessionsXseedlots;
1224 REFRESH MATERIALIZED VIEW public.accessionsXtrait_components;
1225 REFRESH MATERIALIZED VIEW public.accessionsXtraits;
1226 REFRESH MATERIALIZED VIEW public.accessionsXtrial_designs;
1227 REFRESH MATERIALIZED VIEW public.accessionsXtrial_types;
1228 REFRESH MATERIALIZED VIEW public.accessionsXtrials;
1229 REFRESH MATERIALIZED VIEW public.accessionsXyears;
1230 REFRESH MATERIALIZED VIEW public.breeding_programsXlocations;
1231 REFRESH MATERIALIZED VIEW public.breeding_programsXgenotyping_protocols;
1232 REFRESH MATERIALIZED VIEW public.breeding_programsXplants;
1233 REFRESH MATERIALIZED VIEW public.breeding_programsXplots;
1234 REFRESH MATERIALIZED VIEW public.breeding_programsXseedlots;
1235 REFRESH MATERIALIZED VIEW public.breeding_programsXtrait_components;
1236 REFRESH MATERIALIZED VIEW public.breeding_programsXtraits;
1237 REFRESH MATERIALIZED VIEW public.breeding_programsXtrial_designs;
1238 REFRESH MATERIALIZED VIEW public.breeding_programsXtrial_types;
1239 REFRESH MATERIALIZED VIEW public.breeding_programsXtrials;
1240 REFRESH MATERIALIZED VIEW public.breeding_programsXyears;
1241 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXlocations;
1242 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXplants;
1243 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXplots;
1244 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXseedlots;
1245 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXtrait_components;
1246 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXtraits;
1247 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXtrial_designs;
1248 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXtrial_types;
1249 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXtrials;
1250 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXyears;
1251 REFRESH MATERIALIZED VIEW public.locationsXplants;
1252 REFRESH MATERIALIZED VIEW public.locationsXplots;
1253 REFRESH MATERIALIZED VIEW public.locationsXseedlots;
1254 REFRESH MATERIALIZED VIEW public.locationsXtrait_components;
1255 REFRESH MATERIALIZED VIEW public.locationsXtraits;
1256 REFRESH MATERIALIZED VIEW public.locationsXtrial_designs;
1257 REFRESH MATERIALIZED VIEW public.locationsXtrial_types;
1258 REFRESH MATERIALIZED VIEW public.locationsXtrials;
1259 REFRESH MATERIALIZED VIEW public.locationsXyears;
1260 REFRESH MATERIALIZED VIEW public.plantsXplots;
1261 REFRESH MATERIALIZED VIEW public.plantsXseedlots;
1262 REFRESH MATERIALIZED VIEW public.plantsXtrait_components;
1263 REFRESH MATERIALIZED VIEW public.plantsXtraits;
1264 REFRESH MATERIALIZED VIEW public.plantsXtrial_designs;
1265 REFRESH MATERIALIZED VIEW public.plantsXtrial_types;
1266 REFRESH MATERIALIZED VIEW public.plantsXtrials;
1267 REFRESH MATERIALIZED VIEW public.plantsXyears;
1268 REFRESH MATERIALIZED VIEW public.plotsXseedlots;
1269 REFRESH MATERIALIZED VIEW public.plotsXtrait_components;
1270 REFRESH MATERIALIZED VIEW public.plotsXtraits;
1271 REFRESH MATERIALIZED VIEW public.plotsXtrial_designs;
1272 REFRESH MATERIALIZED VIEW public.plotsXtrial_types;
1273 REFRESH MATERIALIZED VIEW public.plotsXtrials;
1274 REFRESH MATERIALIZED VIEW public.plotsXyears;
1275 REFRESH MATERIALIZED VIEW public.seedlotsXtrait_components;
1276 REFRESH MATERIALIZED VIEW public.seedlotsXtraits;
1277 REFRESH MATERIALIZED VIEW public.seedlotsXtrial_designs;
1278 REFRESH MATERIALIZED VIEW public.seedlotsXtrial_types;
1279 REFRESH MATERIALIZED VIEW public.seedlotsXtrials;
1280 REFRESH MATERIALIZED VIEW public.seedlotsXyears;
1281 REFRESH MATERIALIZED VIEW public.trait_componentsXtraits;
1282 REFRESH MATERIALIZED VIEW public.trait_componentsXtrial_designs;
1283 REFRESH MATERIALIZED VIEW public.trait_componentsXtrial_types;
1284 REFRESH MATERIALIZED VIEW public.trait_componentsXtrials;
1285 REFRESH MATERIALIZED VIEW public.trait_componentsXyears;
1286 REFRESH MATERIALIZED VIEW public.traitsXtrial_designs;
1287 REFRESH MATERIALIZED VIEW public.traitsXtrial_types;
1288 REFRESH MATERIALIZED VIEW public.traitsXtrials;
1289 REFRESH MATERIALIZED VIEW public.traitsXyears;
1290 REFRESH MATERIALIZED VIEW public.trial_designsXtrial_types;
1291 REFRESH MATERIALIZED VIEW public.trial_designsXtrials;
1292 REFRESH MATERIALIZED VIEW public.trial_designsXyears;
1293 REFRESH MATERIALIZED VIEW public.trial_typesXtrials;
1294 REFRESH MATERIALIZED VIEW public.trial_typesXyears;
1295 REFRESH MATERIALIZED VIEW public.trialsXyears;
1296 UPDATE public.matviews SET currently_refreshing=FALSE, last_refresh=CURRENT_TIMESTAMP;'
1297     LANGUAGE SQL;
1298
1299 ALTER FUNCTION public.refresh_materialized_views() OWNER TO web_usr;
1300
1301 CREATE OR REPLACE FUNCTION public.refresh_materialized_views_concurrently() RETURNS VOID AS '
1302 REFRESH MATERIALIZED VIEW CONCURRENTLY public.materialized_phenoview;
1303 REFRESH MATERIALIZED VIEW CONCURRENTLY public.materialized_genoview;
1304 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessions;
1305 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programs;
1306 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocols;
1307 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locations;
1308 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plants;
1309 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plots;
1310 REFRESH MATERIALIZED VIEW CONCURRENTLY public.seedlots;
1311 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trait_components;
1312 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traits;
1313 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_designs;
1314 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_types;
1315 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trials;
1316 REFRESH MATERIALIZED VIEW CONCURRENTLY public.years;
1317 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXbreeding_programs;
1318 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXlocations;
1319 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXgenotyping_protocols;
1320 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXplants;
1321 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXplots;
1322 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXseedlots;
1323 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXtrait_components;
1324 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXtraits;
1325 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXtrial_designs;
1326 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXtrial_types;
1327 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXtrials;
1328 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXyears;
1329 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXlocations;
1330 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXgenotyping_protocols;
1331 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXplants;
1332 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXplots;
1333 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXseedlots;
1334 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXtrait_components;
1335 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXtraits;
1336 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXtrial_designs;
1337 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXtrial_types;
1338 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXtrials;
1339 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXyears;
1340 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXlocations;
1341 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXplants;
1342 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXplots;
1343 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXseedlots;
1344 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXtrait_components;
1345 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXtraits;
1346 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXtrial_designs;
1347 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXtrial_types;
1348 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXtrials;
1349 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXyears;
1350 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXplants;
1351 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXplots;
1352 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXseedlots;
1353 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXtrait_components;
1354 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXtraits;
1355 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXtrial_designs;
1356 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXtrial_types;
1357 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXtrials;
1358 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXyears;
1359 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXplots;
1360 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXseedlots;
1361 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXtrait_components;
1362 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXtraits;
1363 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXtrial_designs;
1364 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXtrial_types;
1365 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXtrials;
1366 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXyears;
1367 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXseedlots;
1368 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXtrait_components;
1369 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXtraits;
1370 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXtrial_designs;
1371 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXtrial_types;
1372 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXtrials;
1373 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXyears;
1374 REFRESH MATERIALIZED VIEW CONCURRENTLY public.seedlotsXtrait_components;
1375 REFRESH MATERIALIZED VIEW CONCURRENTLY public.seedlotsXtraits;
1376 REFRESH MATERIALIZED VIEW CONCURRENTLY public.seedlotsXtrial_designs;
1377 REFRESH MATERIALIZED VIEW CONCURRENTLY public.seedlotsXtrial_types;
1378 REFRESH MATERIALIZED VIEW CONCURRENTLY public.seedlotsXtrials;
1379 REFRESH MATERIALIZED VIEW CONCURRENTLY public.seedlotsXyears;
1380 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trait_componentsXtraits;
1381 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trait_componentsXtrial_designs;
1382 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trait_componentsXtrial_types;
1383 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trait_componentsXtrials;
1384 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trait_componentsXyears;
1385 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traitsXtrial_designs;
1386 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traitsXtrial_types;
1387 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traitsXtrials;
1388 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traitsXyears;
1389 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_designsXtrial_types;
1390 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_designsXtrials;
1391 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_designsXyears;
1392 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_typesXtrials;
1393 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_typesXyears;
1394 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trialsXyears;
1395 UPDATE public.matviews SET currently_refreshing=FALSE, last_refresh=CURRENT_TIMESTAMP;'
1396     LANGUAGE SQL;
1397
1398 ALTER FUNCTION public.refresh_materialized_views_concurrently() OWNER TO web_usr;
1399 --
1400
1401 EOSQL
1402
1403 print "You're done!\n";
1404 }
1405
1406
1407 ####
1408 1; #
1409 ####