db/00125/UpdateWizardMaterializedViewsForAnalysisResults.pm

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