db/00135/UpdateWizardMaterializedViewsForGenoProtocols.pm

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