db/00058/UpdateMatViews.pm

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6 UpdateMatViews.pm
   7
   8 =head1 SYNOPSIS
   9
  10 mx-run UpdateMatViews [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 index of materialized_fullview and the queries used to create the materialized view for each individual category. It adds views for new categories: trial type and trial design
  18
  19 =head1 AUTHOR
  20
  21 Bryan Ellerbrock<bje24@cornell.edu>
  22
  23 =head1 COPYRIGHT & LICENSE
  24
  25 Copyright 2010 Boyce Thompson Institute for Plant Research
  26
  27 This program is free software; you can redistribute it and/or modify
  28 it under the same terms as Perl itself.
  29
  30 =cut
  31
  32
  33 package UpdateMatViews;
  34
  35 use Moose;
  36 extends 'CXGN::Metadata::Dbpatch';
  37
  38
  39 has '+description' => ( default => <<'' );
  40 This patch updates the index of materialized_fullview and the queries used to create the materialized view for each individual category. It adds views for new categories: trial type and trial design
  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 DROP EXTENSION dblink;
  55 DROP MATERIALIZED VIEW public.materialized_fullview CASCADE;
  56 CREATE MATERIALIZED VIEW public.materialized_phenoview AS
  57  SELECT plant.uniquename AS plant_name,
  58     plant.stock_id AS plant_id,
  59     plot.uniquename AS plot_name,
  60     plot.stock_id AS plot_id,
  61     accession.uniquename AS accession_name,
  62     accession.stock_id AS accession_id,
  63     nd_experiment.nd_geolocation_id AS location_id,
  64     nd_geolocation.description AS location_name,
  65     projectprop.value AS year_id,
  66     projectprop.value AS year_name,
  67     trial.project_id AS trial_id,
  68     trial.name AS trial_name,
  69     trialterm.cvterm_id AS trial_type_id,
  70     trialterm.name AS trial_type_name,
  71     trialdesign.value AS trial_design_id,
  72     trialdesign.value AS trial_design_value,
  73     breeding_program.project_id AS breeding_program_id,
  74     breeding_program.name AS breeding_program_name,
  75     cvterm.cvterm_id AS trait_id,
  76     (((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text AS trait_name,
  77     phenotype.phenotype_id,
  78     phenotype.value AS phenotype_value
  79    FROM stock accession
  80      LEFT JOIN stock_relationship plot_relationship ON accession.stock_id = plot_relationship.object_id
  81      LEFT JOIN stock plot ON plot_relationship.subject_id = plot.stock_id AND plot.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plot')
  82      LEFT JOIN stock_relationship plant_relationship ON plot.stock_id = plant_relationship.subject_id
  83      LEFT JOIN stock plant ON plant_relationship.object_id = plant.stock_id AND plant.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plant')
  84      LEFT JOIN nd_experiment_stock nd_experiment_plot ON plot.stock_id = nd_experiment_plot.stock_id
  85      LEFT JOIN nd_experiment_stock nd_experiment_accession ON accession.stock_id = nd_experiment_accession.stock_id
  86      LEFT JOIN nd_experiment ON nd_experiment_plot.nd_experiment_id = nd_experiment.nd_experiment_id
  87      LEFT JOIN nd_geolocation ON nd_experiment.nd_geolocation_id = nd_geolocation.nd_geolocation_id
  88      LEFT JOIN nd_experiment_project ON nd_experiment_plot.nd_experiment_id = nd_experiment_project.nd_experiment_id
  89      LEFT JOIN project trial ON nd_experiment_project.project_id = trial.project_id
  90      LEFT JOIN project_relationship ON trial.project_id = project_relationship.subject_project_id
  91      LEFT JOIN projectprop breeding_programprop ON project_relationship.object_project_id = breeding_programprop.project_id
  92      LEFT JOIN project breeding_program ON breeding_programprop.project_id = breeding_program.project_id
  93      LEFT JOIN projectprop ON trial.project_id = projectprop.project_id AND projectprop.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'project year' )
  94      LEFT JOIN projectprop trialdesign ON trial.project_id = trialdesign.project_id AND trialdesign.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'design' )
  95      LEFT JOIN projectprop trialprop ON trial.project_id = trialprop.project_id AND trialprop.type_id IN (SELECT cvterm_id from cvterm JOIN cv USING(cv_id) WHERE cv.name = 'project_type' )
  96      LEFT JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
  97      LEFT JOIN nd_experiment_phenotype ON nd_experiment_plot.nd_experiment_id = nd_experiment_phenotype.nd_experiment_id
  98      LEFT JOIN phenotype ON nd_experiment_phenotype.phenotype_id = phenotype.phenotype_id
  99      LEFT JOIN cvterm ON phenotype.cvalue_id = cvterm.cvterm_id
 100      LEFT JOIN dbxref ON cvterm.dbxref_id = dbxref.dbxref_id
 101      LEFT JOIN db ON dbxref.db_id = db.db_id
 102   WHERE accession.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'accession') AND breeding_programprop.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'breeding_program')
 103   GROUP BY plant.stock_id, plant.uniquename, plot.stock_id, plot.uniquename, accession.uniquename, accession.stock_id, (((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text, cvterm.cvterm_id, trial.name, trial.project_id, breeding_program.name, breeding_program.project_id, projectprop.value, trialdesign.value, trialterm.cvterm_id, trialterm.name, nd_experiment.nd_geolocation_id, nd_geolocation.description, phenotype.phenotype_id, phenotype.value
 104   WITH DATA;
 105
 106 CREATE UNIQUE INDEX unq_pheno_idx ON public.materialized_phenoview(accession_id,plot_id,plant_id,phenotype_id) WITH (fillfactor=100);
 107 CREATE INDEX accession_id_pheno_idx ON public.materialized_phenoview(accession_id) WITH (fillfactor=100);
 108 CREATE INDEX breeding_program_id_idx ON public.materialized_phenoview(breeding_program_id) WITH (fillfactor=100);
 109 CREATE INDEX location_id_idx ON public.materialized_phenoview(location_id) WITH (fillfactor=100);
 110 CREATE INDEX plot_id_idx ON public.materialized_phenoview(plot_id) WITH (fillfactor=100);
 111 CREATE INDEX plant_id_idx ON public.materialized_phenoview(plant_id) WITH (fillfactor=100);
 112 CREATE INDEX trait_id_idx ON public.materialized_phenoview(trait_id) WITH (fillfactor=100);
 113 CREATE INDEX trial_id_idx ON public.materialized_phenoview(trial_id) WITH (fillfactor=100);
 114 CREATE INDEX trial_type_id_idx ON public.materialized_phenoview(trial_type_id) WITH (fillfactor=100);
 115 CREATE INDEX trial_design_id_idx ON public.materialized_phenoview(trial_design_id) WITH (fillfactor=100);
 116 CREATE INDEX year_id_idx ON public.materialized_phenoview(year_id) WITH (fillfactor=100);
 117 ALTER MATERIALIZED VIEW materialized_phenoview OWNER TO web_usr;
 118
 119 CREATE MATERIALIZED VIEW public.materialized_genoview AS
 120  SELECT stock.uniquename AS accession_name,
 121     stock.stock_id AS accession_id,
 122     nd_experiment_protocol.nd_protocol_id AS genotyping_protocol_id,
 123     nd_protocol.name AS genotyping_protocol_name,
 124     genotype.genotype_id AS genotype_id,
 125     genotype.uniquename AS genotype_name
 126    FROM stock
 127      LEFT JOIN nd_experiment_stock ON stock.stock_id = nd_experiment_stock.stock_id
 128      LEFT JOIN nd_experiment_protocol ON nd_experiment_stock.nd_experiment_id = nd_experiment_protocol.nd_experiment_id
 129      LEFT JOIN nd_protocol ON nd_experiment_protocol.nd_protocol_id = nd_protocol.nd_protocol_id
 130      LEFT JOIN nd_experiment_genotype ON nd_experiment_stock.nd_experiment_id = nd_experiment_genotype.nd_experiment_id
 131      LEFT JOIN genotype ON genotype.genotype_id = nd_experiment_genotype.genotype_id
 132   WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'accession')
 133   GROUP BY stock.uniquename, stock.stock_id, nd_experiment_protocol.nd_protocol_id, nd_protocol.name, genotype.genotype_id, genotype.uniquename
 134   WITH DATA;
 135
 136 CREATE UNIQUE INDEX unq_geno_idx ON public.materialized_genoview(accession_id,genotype_id) WITH (fillfactor=100);
 137 CREATE INDEX accession_id_geno_idx ON public.materialized_genoview(accession_id) WITH (fillfactor=100);
 138 CREATE INDEX genotyping_protocol_id_idx ON public.materialized_genoview(genotyping_protocol_id) WITH (fillfactor=100);
 139 CREATE INDEX genotype_id_idx ON public.materialized_genoview(genotype_id) WITH (fillfactor=100);
 140 ALTER MATERIALIZED VIEW materialized_genoview OWNER TO web_usr;
 141
 142 DROP TABLE public.matviews;
 143 CREATE TABLE public.matviews (
 144     mv_id SERIAL PRIMARY KEY
 145   , mv_name NAME NOT NULL
 146   , mv_dependents NAME ARRAY
 147   , currently_refreshing BOOLEAN
 148   , last_refresh TIMESTAMP WITH TIME ZONE
 149 );
 150 ALTER TABLE public.matviews OWNER TO web_usr;
 151
 152 INSERT INTO matviews (mv_name, mv_dependents, currently_refreshing, last_refresh) VALUES ('materialized_phenoview', '{"accessionsXbreeding_programs","accessionsXlocations","accessionsXplants","accessionsXplots","accessionsXtraits","accessionsXtrials","accessionsXtrial_designs","accessionsXtrial_types","accessionsXyears","breeding_programsXgenotyping_protocols","breeding_programsXlocations","breeding_programsXplants","breeding_programsXplots","breeding_programsXtraits","breeding_programsXtrials","breeding_programsXtrial_designs","breeding_programsXtrial_types","breeding_programsXyears","genotyping_protocolsXlocations","genotyping_protocolsXplants","genotyping_protocolsXplots","genotyping_protocolsXtraits","genotyping_protocolsXtrials","genotyping_protocolsXtrial_designs","genotyping_protocolsXtrial_types","genotyping_protocolsXyears","locationsXplants","locationsXplots","locationsXtraits","locationsXtrials","locationsXtrial_designs","locationsXtrial_types","locationsXyears","plantsXplots","plantsXtraits","plantsXtrials","plantsXtrial_designs","plantsXtrial_types","plantsXyears","plotsXtraits","plotsXtrials","plotsXtrial_designs","plotsXtrial_types","plotsXyears","traitsXtrials","traitsXtrial_designs","traitsXtrial_types","traitsXyears","trial_designsXtrials","trial_typesXtrials","trialsXyears","trial_designsXtrial_types","trial_designsXyears","trial_typesXyears"}', FALSE, CURRENT_TIMESTAMP);
 153 INSERT INTO matviews (mv_name, mv_dependents, currently_refreshing, last_refresh) VALUES ('materialized_genoview', '{"accessionsXgenotyping_protocols","breeding_programsXgenotyping_protocols","genotyping_protocolsXlocations","genotyping_protocolsXplants","genotyping_protocolsXplots","genotyping_protocolsXtraits","genotyping_protocolsXtrials","genotyping_protocolsXtrial_designs","genotyping_protocolsXtrial_types","genotyping_protocolsXyears"}', FALSE, CURRENT_TIMESTAMP);
 154
 155 CREATE MATERIALIZED VIEW public.accessions AS
 156   SELECT stock.stock_id AS accession_id,
 157   stock.uniquename AS accession_name
 158   FROM stock
 159   WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'accession')
 160   GROUP BY stock.stock_id, stock.uniquename
 161   WITH DATA;
 162 CREATE UNIQUE INDEX accessions_idx ON public.accessions(accession_id) WITH (fillfactor=100);
 163 ALTER MATERIALIZED VIEW accessions OWNER TO web_usr;
 164 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('accessions', FALSE, CURRENT_TIMESTAMP);
 165 CREATE MATERIALIZED VIEW public.accessionsXbreeding_programs AS
 166 SELECT public.materialized_phenoview.accession_id,
 167     public.materialized_phenoview.breeding_program_id
 168    FROM public.materialized_phenoview
 169   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.breeding_program_id
 170   WITH DATA;
 171 CREATE UNIQUE INDEX accessionsXbreeding_programs_idx ON public.accessionsXbreeding_programs(accession_id, breeding_program_id) WITH (fillfactor=100);
 172 ALTER MATERIALIZED VIEW accessionsXbreeding_programs OWNER TO web_usr;
 173 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('accessionsXbreeding_programs', FALSE, CURRENT_TIMESTAMP);
 174 CREATE MATERIALIZED VIEW public.accessionsXgenotyping_protocols AS
 175 SELECT public.materialized_genoview.accession_id,
 176     public.materialized_genoview.genotyping_protocol_id
 177    FROM public.materialized_genoview
 178   GROUP BY public.materialized_genoview.accession_id, public.materialized_genoview.genotyping_protocol_id
 179   WITH DATA;
 180 CREATE UNIQUE INDEX accessionsXgenotyping_protocols_idx ON public.accessionsXgenotyping_protocols(accession_id, genotyping_protocol_id) WITH (fillfactor=100);
 181 ALTER MATERIALIZED VIEW accessionsXgenotyping_protocols OWNER TO web_usr;
 182 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('accessionsXgenotyping_protocols', FALSE, CURRENT_TIMESTAMP);
 183 CREATE MATERIALIZED VIEW public.accessionsXlocations AS
 184 SELECT public.materialized_phenoview.accession_id,
 185     public.materialized_phenoview.location_id
 186    FROM public.materialized_phenoview
 187   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.location_id
 188   WITH DATA;
 189 CREATE UNIQUE INDEX accessionsXlocations_idx ON public.accessionsXlocations(accession_id, location_id) WITH (fillfactor=100);
 190 ALTER MATERIALIZED VIEW accessionsXlocations OWNER TO web_usr;
 191 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('accessionsXlocations', FALSE, CURRENT_TIMESTAMP);
 192 CREATE MATERIALIZED VIEW public.accessionsXplants AS
 193 SELECT public.materialized_phenoview.accession_id,
 194     public.materialized_phenoview.plant_id
 195    FROM public.materialized_phenoview
 196   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.plant_id
 197   WITH DATA;
 198 CREATE UNIQUE INDEX accessionsXplants_idx ON public.accessionsXplants(accession_id, plant_id) WITH (fillfactor=100);
 199 ALTER MATERIALIZED VIEW accessionsXplants OWNER TO web_usr;
 200 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('accessionsXplants', FALSE, CURRENT_TIMESTAMP);
 201 CREATE MATERIALIZED VIEW public.accessionsXplots AS
 202 SELECT public.materialized_phenoview.accession_id,
 203     public.materialized_phenoview.plot_id
 204    FROM public.materialized_phenoview
 205   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.plot_id
 206 WITH DATA;
 207 CREATE UNIQUE INDEX accessionsXplots_idx ON public.accessionsXplots(accession_id, plot_id) WITH (fillfactor=100);
 208 ALTER MATERIALIZED VIEW accessionsXplots OWNER TO web_usr;
 209 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('accessionsXplots', FALSE, CURRENT_TIMESTAMP);
 210 CREATE MATERIALIZED VIEW public.accessionsXtraits AS
 211 SELECT public.materialized_phenoview.accession_id,
 212     public.materialized_phenoview.trait_id
 213    FROM public.materialized_phenoview
 214   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.trait_id
 215 WITH DATA;
 216 CREATE UNIQUE INDEX accessionsXtraits_idx ON public.accessionsXtraits(accession_id, trait_id) WITH (fillfactor=100);
 217 ALTER MATERIALIZED VIEW accessionsXtraits OWNER TO web_usr;
 218 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('accessionsXtraits', FALSE, CURRENT_TIMESTAMP);
 219 CREATE MATERIALIZED VIEW public.accessionsXtrials AS
 220 SELECT public.materialized_phenoview.accession_id,
 221     public.materialized_phenoview.trial_id
 222    FROM public.materialized_phenoview
 223   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.trial_id
 224 WITH DATA;
 225 CREATE UNIQUE INDEX accessionsXtrials_idx ON public.accessionsXtrials(accession_id, trial_id) WITH (fillfactor=100);
 226 ALTER MATERIALIZED VIEW accessionsXtrials OWNER TO web_usr;
 227 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('accessionsXtrials', FALSE, CURRENT_TIMESTAMP);
 228 CREATE MATERIALIZED VIEW public.accessionsXtrial_designs AS
 229 SELECT public.materialized_phenoview.accession_id,
 230     public.materialized_phenoview.trial_design_id
 231    FROM public.materialized_phenoview
 232   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.trial_design_id
 233 WITH DATA;
 234 CREATE UNIQUE INDEX accessionsXtrial_designs_idx ON public.accessionsXtrial_designs(accession_id, trial_design_id) WITH (fillfactor=100);
 235 ALTER MATERIALIZED VIEW accessionsXtrial_designs OWNER TO web_usr;
 236 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('accessionsXtrial_designs', FALSE, CURRENT_TIMESTAMP);
 237 CREATE MATERIALIZED VIEW public.accessionsXtrial_types AS
 238 SELECT public.materialized_phenoview.accession_id,
 239     public.materialized_phenoview.trial_type_id
 240    FROM public.materialized_phenoview
 241   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.trial_type_id
 242 WITH DATA;
 243 CREATE UNIQUE INDEX accessionsXtrial_types_idx ON public.accessionsXtrial_types(accession_id, trial_type_id) WITH (fillfactor=100);
 244 ALTER MATERIALIZED VIEW accessionsXtrial_types OWNER TO web_usr;
 245 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('accessionsXtrial_types', FALSE, CURRENT_TIMESTAMP);
 246 CREATE MATERIALIZED VIEW public.accessionsXyears AS
 247 SELECT public.materialized_phenoview.accession_id,
 248     public.materialized_phenoview.year_id
 249    FROM public.materialized_phenoview
 250   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.year_id
 251 WITH DATA;
 252 CREATE UNIQUE INDEX accessionsXyears_idx ON public.accessionsXyears(accession_id, year_id) WITH (fillfactor=100);
 253 ALTER MATERIALIZED VIEW accessionsXyears OWNER TO web_usr;
 254 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('accessionsXyears', FALSE, CURRENT_TIMESTAMP);
 255
 256 CREATE MATERIALIZED VIEW public.breeding_programs AS
 257 SELECT project.project_id AS breeding_program_id,
 258     project.name AS breeding_program_name
 259    FROM project join projectprop USING (project_id)
 260    WHERE projectprop.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'breeding_program')
 261   GROUP BY project.project_id, project.name
 262 WITH DATA;
 263 CREATE UNIQUE INDEX breeding_programs_idx ON public.breeding_programs(breeding_program_id) WITH (fillfactor=100);
 264 ALTER MATERIALIZED VIEW breeding_programs OWNER TO web_usr;
 265 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('breeding_programs', FALSE, CURRENT_TIMESTAMP);
 266 CREATE MATERIALIZED VIEW public.breeding_programsXgenotyping_protocols AS
 267 SELECT public.materialized_phenoview.breeding_program_id,
 268     public.materialized_genoview.genotyping_protocol_id
 269    FROM public.materialized_phenoview
 270    JOIN public.materialized_genoview USING(accession_id)
 271   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_genoview.genotyping_protocol_id
 272 WITH DATA;
 273 CREATE UNIQUE INDEX breeding_programsXgenotyping_protocols_idx ON public.breeding_programsXgenotyping_protocols(breeding_program_id, genotyping_protocol_id) WITH (fillfactor=100);
 274 ALTER MATERIALIZED VIEW breeding_programsXgenotyping_protocols OWNER TO web_usr;
 275 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('breeding_programsXgenotyping_protocols', FALSE, CURRENT_TIMESTAMP);
 276 CREATE MATERIALIZED VIEW public.breeding_programsXlocations AS
 277 SELECT public.materialized_phenoview.breeding_program_id,
 278     public.materialized_phenoview.location_id
 279    FROM public.materialized_phenoview
 280   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_phenoview.location_id
 281 WITH DATA;
 282 CREATE UNIQUE INDEX breeding_programsXlocations_idx ON public.breeding_programsXlocations(breeding_program_id, location_id) WITH (fillfactor=100);
 283 ALTER MATERIALIZED VIEW breeding_programsXlocations OWNER TO web_usr;
 284 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('breeding_programsXlocations', FALSE, CURRENT_TIMESTAMP);
 285 CREATE MATERIALIZED VIEW public.breeding_programsXplants AS
 286 SELECT public.materialized_phenoview.breeding_program_id,
 287     public.materialized_phenoview.plant_id
 288    FROM public.materialized_phenoview
 289   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_phenoview.plant_id
 290 WITH DATA;
 291 CREATE UNIQUE INDEX breeding_programsXplants_idx ON public.breeding_programsXplants(breeding_program_id, plant_id) WITH (fillfactor=100);
 292 ALTER MATERIALIZED VIEW breeding_programsXplants OWNER TO web_usr;
 293 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('breeding_programsXplants', FALSE, CURRENT_TIMESTAMP);
 294 CREATE MATERIALIZED VIEW public.breeding_programsXplots AS
 295 SELECT public.materialized_phenoview.breeding_program_id,
 296     public.materialized_phenoview.plot_id
 297    FROM public.materialized_phenoview
 298   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_phenoview.plot_id
 299 WITH DATA;
 300 CREATE UNIQUE INDEX breeding_programsXplots_idx ON public.breeding_programsXplots(breeding_program_id, plot_id) WITH (fillfactor=100);
 301 ALTER MATERIALIZED VIEW breeding_programsXplots OWNER TO web_usr;
 302 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('breeding_programsXplots', FALSE, CURRENT_TIMESTAMP);
 303 CREATE MATERIALIZED VIEW public.breeding_programsXtraits AS
 304 SELECT public.materialized_phenoview.breeding_program_id,
 305     public.materialized_phenoview.trait_id
 306    FROM public.materialized_phenoview
 307   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_phenoview.trait_id
 308 WITH DATA;
 309 CREATE UNIQUE INDEX breeding_programsXtraits_idx ON public.breeding_programsXtraits(breeding_program_id, trait_id) WITH (fillfactor=100);
 310 ALTER MATERIALIZED VIEW breeding_programsXtraits OWNER TO web_usr;
 311 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('breeding_programsXtraits', FALSE, CURRENT_TIMESTAMP);
 312 CREATE MATERIALIZED VIEW public.breeding_programsXtrials AS
 313 SELECT public.materialized_phenoview.breeding_program_id,
 314     public.materialized_phenoview.trial_id
 315    FROM public.materialized_phenoview
 316   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_phenoview.trial_id
 317 WITH DATA;
 318 CREATE UNIQUE INDEX breeding_programsXtrials_idx ON public.breeding_programsXtrials(breeding_program_id, trial_id) WITH (fillfactor=100);
 319 ALTER MATERIALIZED VIEW breeding_programsXtrials OWNER TO web_usr;
 320 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('breeding_programsXtrials', FALSE, CURRENT_TIMESTAMP);
 321 CREATE MATERIALIZED VIEW public.breeding_programsXtrial_designs AS
 322 SELECT public.materialized_phenoview.breeding_program_id,
 323     public.materialized_phenoview.trial_design_id
 324    FROM public.materialized_phenoview
 325   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_phenoview.trial_design_id
 326 WITH DATA;
 327 CREATE UNIQUE INDEX breeding_programsXtrial_designs_idx ON public.breeding_programsXtrial_designs(breeding_program_id, trial_design_id) WITH (fillfactor=100);
 328 ALTER MATERIALIZED VIEW breeding_programsXtrial_designs OWNER TO web_usr;
 329 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('breeding_programsXtrial_designs', FALSE, CURRENT_TIMESTAMP);
 330 CREATE MATERIALIZED VIEW public.breeding_programsXtrial_types AS
 331 SELECT public.materialized_phenoview.breeding_program_id,
 332     public.materialized_phenoview.trial_type_id
 333    FROM public.materialized_phenoview
 334   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_phenoview.trial_type_id
 335 WITH DATA;
 336 CREATE UNIQUE INDEX breeding_programsXtrial_types_idx ON public.breeding_programsXtrial_types(breeding_program_id, trial_type_id) WITH (fillfactor=100);
 337 ALTER MATERIALIZED VIEW breeding_programsXtrial_types OWNER TO web_usr;
 338 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('breeding_programsXtrial_types', FALSE, CURRENT_TIMESTAMP);
 339 CREATE MATERIALIZED VIEW public.breeding_programsXyears AS
 340 SELECT public.materialized_phenoview.breeding_program_id,
 341     public.materialized_phenoview.year_id
 342    FROM public.materialized_phenoview
 343   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_phenoview.year_id
 344 WITH DATA;
 345 CREATE UNIQUE INDEX breeding_programsXyears_idx ON public.breeding_programsXyears(breeding_program_id, year_id) WITH (fillfactor=100);
 346 ALTER MATERIALIZED VIEW breeding_programsXyears OWNER TO web_usr;
 347 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('breeding_programsXyears', FALSE, CURRENT_TIMESTAMP);
 348
 349 CREATE MATERIALIZED VIEW public.genotyping_protocols AS
 350 SELECT nd_protocol.nd_protocol_id AS genotyping_protocol_id,
 351     nd_protocol.name AS genotyping_protocol_name
 352    FROM nd_protocol
 353   GROUP BY public.nd_protocol.nd_protocol_id, public.nd_protocol.name
 354 WITH DATA;
 355 CREATE UNIQUE INDEX genotyping_protocols_idx ON public.genotyping_protocols(genotyping_protocol_id) WITH (fillfactor=100);
 356 ALTER MATERIALIZED VIEW genotyping_protocols OWNER TO web_usr;
 357 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('genotyping_protocols', FALSE, CURRENT_TIMESTAMP);
 358 CREATE MATERIALIZED VIEW public.genotyping_protocolsXlocations AS
 359 SELECT public.materialized_genoview.genotyping_protocol_id,
 360     public.materialized_phenoview.location_id
 361    FROM public.materialized_genoview
 362    JOIN public.materialized_phenoview USING(accession_id)
 363   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.location_id
 364 WITH DATA;
 365 CREATE UNIQUE INDEX genotyping_protocolsXlocations_idx ON public.genotyping_protocolsXlocations(genotyping_protocol_id, location_id) WITH (fillfactor=100);
 366 ALTER MATERIALIZED VIEW genotyping_protocolsXlocations OWNER TO web_usr;
 367 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('genotyping_protocolsXlocations', FALSE, CURRENT_TIMESTAMP);
 368 CREATE MATERIALIZED VIEW public.genotyping_protocolsXplants AS
 369 SELECT public.materialized_genoview.genotyping_protocol_id,
 370     public.materialized_phenoview.plant_id
 371    FROM public.materialized_genoview
 372    JOIN public.materialized_phenoview USING(accession_id)
 373   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.plant_id
 374 WITH DATA;
 375 CREATE UNIQUE INDEX genotyping_protocolsXplants_idx ON public.genotyping_protocolsXplants(genotyping_protocol_id, plant_id) WITH (fillfactor=100);
 376 ALTER MATERIALIZED VIEW genotyping_protocolsXplants OWNER TO web_usr;
 377 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('genotyping_protocolsXplants', FALSE, CURRENT_TIMESTAMP);
 378 CREATE MATERIALIZED VIEW public.genotyping_protocolsXplots AS
 379 SELECT public.materialized_genoview.genotyping_protocol_id,
 380     public.materialized_phenoview.plot_id
 381    FROM public.materialized_genoview
 382    JOIN public.materialized_phenoview USING(accession_id)
 383   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.plot_id
 384 WITH DATA;
 385 CREATE UNIQUE INDEX genotyping_protocolsXplots_idx ON public.genotyping_protocolsXplots(genotyping_protocol_id, plot_id) WITH (fillfactor=100);
 386 ALTER MATERIALIZED VIEW genotyping_protocolsXplots OWNER TO web_usr;
 387 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('genotyping_protocolsXplots', FALSE, CURRENT_TIMESTAMP);
 388 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtraits AS
 389 SELECT public.materialized_genoview.genotyping_protocol_id,
 390     public.materialized_phenoview.trait_id
 391    FROM public.materialized_genoview
 392    JOIN public.materialized_phenoview USING(accession_id)
 393   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.trait_id
 394 WITH DATA;
 395 CREATE UNIQUE INDEX genotyping_protocolsXtraits_idx ON public.genotyping_protocolsXtraits(genotyping_protocol_id, trait_id) WITH (fillfactor=100);
 396 ALTER MATERIALIZED VIEW genotyping_protocolsXtraits OWNER TO web_usr;
 397 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('genotyping_protocolsXtraits', FALSE, CURRENT_TIMESTAMP);
 398 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrials AS
 399 SELECT public.materialized_genoview.genotyping_protocol_id,
 400     public.materialized_phenoview.trial_id
 401    FROM public.materialized_genoview
 402    JOIN public.materialized_phenoview USING(accession_id)
 403   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.trial_id
 404 WITH DATA;
 405 CREATE UNIQUE INDEX genotyping_protocolsXtrials_idx ON public.genotyping_protocolsXtrials(genotyping_protocol_id, trial_id) WITH (fillfactor=100);
 406 ALTER MATERIALIZED VIEW genotyping_protocolsXtrials OWNER TO web_usr;
 407 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('genotyping_protocolsXtrials', FALSE, CURRENT_TIMESTAMP);
 408 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrial_designs AS
 409 SELECT public.materialized_genoview.genotyping_protocol_id,
 410     public.materialized_phenoview.trial_design_id
 411    FROM public.materialized_genoview
 412    JOIN public.materialized_phenoview USING(accession_id)
 413   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.trial_design_id
 414 WITH DATA;
 415 CREATE UNIQUE INDEX genotyping_protocolsXtrial_designs_idx ON public.genotyping_protocolsXtrial_designs(genotyping_protocol_id, trial_design_id) WITH (fillfactor=100);
 416 ALTER MATERIALIZED VIEW genotyping_protocolsXtrial_designs OWNER TO web_usr;
 417 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('genotyping_protocolsXtrial_designs', FALSE, CURRENT_TIMESTAMP);
 418 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrial_types AS
 419 SELECT public.materialized_genoview.genotyping_protocol_id,
 420     public.materialized_phenoview.trial_type_id
 421    FROM public.materialized_genoview
 422    JOIN public.materialized_phenoview USING(accession_id)
 423   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.trial_type_id
 424 WITH DATA;
 425 CREATE UNIQUE INDEX genotyping_protocolsXtrial_types_idx ON public.genotyping_protocolsXtrial_types(genotyping_protocol_id, trial_type_id) WITH (fillfactor=100);
 426 ALTER MATERIALIZED VIEW genotyping_protocolsXtrial_types OWNER TO web_usr;
 427 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('genotyping_protocolsXtrial_types', FALSE, CURRENT_TIMESTAMP);
 428 CREATE MATERIALIZED VIEW public.genotyping_protocolsXyears AS
 429 SELECT public.materialized_genoview.genotyping_protocol_id,
 430     public.materialized_phenoview.year_id
 431    FROM public.materialized_genoview
 432    JOIN public.materialized_phenoview USING(accession_id)
 433   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.year_id
 434 WITH DATA;
 435 CREATE UNIQUE INDEX genotyping_protocolsXyears_idx ON public.genotyping_protocolsXyears(genotyping_protocol_id, year_id) WITH (fillfactor=100);
 436 ALTER MATERIALIZED VIEW genotyping_protocolsXyears OWNER TO web_usr;
 437 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('genotyping_protocolsXyears', FALSE, CURRENT_TIMESTAMP);
 438
 439 CREATE MATERIALIZED VIEW public.locations AS
 440 SELECT nd_geolocation.nd_geolocation_id AS location_id,
 441   nd_geolocation.description AS location_name
 442    FROM nd_geolocation
 443   GROUP BY public.nd_geolocation.nd_geolocation_id, public.nd_geolocation.description
 444 WITH DATA;
 445 CREATE UNIQUE INDEX locations_idx ON public.locations(location_id) WITH (fillfactor=100);
 446 ALTER MATERIALIZED VIEW locations OWNER TO web_usr;
 447 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('locations', FALSE, CURRENT_TIMESTAMP);
 448 CREATE MATERIALIZED VIEW public.locationsXplants AS
 449 SELECT public.materialized_phenoview.location_id,
 450     public.materialized_phenoview.plant_id
 451    FROM public.materialized_phenoview
 452   GROUP BY public.materialized_phenoview.location_id, public.materialized_phenoview.plant_id
 453 WITH DATA;
 454 CREATE UNIQUE INDEX locationsXplants_idx ON public.locationsXplants(location_id, plant_id) WITH (fillfactor=100);
 455 ALTER MATERIALIZED VIEW locationsXplants OWNER TO web_usr;
 456 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('locationsXplants', FALSE, CURRENT_TIMESTAMP);
 457 CREATE MATERIALIZED VIEW public.locationsXplots AS
 458 SELECT public.materialized_phenoview.location_id,
 459     public.materialized_phenoview.plot_id
 460    FROM public.materialized_phenoview
 461   GROUP BY public.materialized_phenoview.location_id, public.materialized_phenoview.plot_id
 462 WITH DATA;
 463 CREATE UNIQUE INDEX locationsXplots_idx ON public.locationsXplots(location_id, plot_id) WITH (fillfactor=100);
 464 ALTER MATERIALIZED VIEW locationsXplots OWNER TO web_usr;
 465 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('locationsXplots', FALSE, CURRENT_TIMESTAMP);
 466 CREATE MATERIALIZED VIEW public.locationsXtraits AS
 467 SELECT public.materialized_phenoview.location_id,
 468     public.materialized_phenoview.trait_id
 469    FROM public.materialized_phenoview
 470   GROUP BY public.materialized_phenoview.location_id, public.materialized_phenoview.trait_id
 471 WITH DATA;
 472 CREATE UNIQUE INDEX locationsXtraits_idx ON public.locationsXtraits(location_id, trait_id) WITH (fillfactor=100);
 473 ALTER MATERIALIZED VIEW locationsXtraits OWNER TO web_usr;
 474 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('locationsXtraits', FALSE, CURRENT_TIMESTAMP);
 475 CREATE MATERIALIZED VIEW public.locationsXtrials AS
 476 SELECT public.materialized_phenoview.location_id,
 477     public.materialized_phenoview.trial_id
 478    FROM public.materialized_phenoview
 479   GROUP BY public.materialized_phenoview.location_id, public.materialized_phenoview.trial_id
 480 WITH DATA;
 481 CREATE UNIQUE INDEX locationsXtrials_idx ON public.locationsXtrials(location_id, trial_id) WITH (fillfactor=100);
 482 ALTER MATERIALIZED VIEW locationsXtrials OWNER TO web_usr;
 483 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('locationsXtrials', FALSE, CURRENT_TIMESTAMP);
 484 CREATE MATERIALIZED VIEW public.locationsXtrial_designs AS
 485 SELECT public.materialized_phenoview.location_id,
 486     public.materialized_phenoview.trial_design_id
 487    FROM public.materialized_phenoview
 488   GROUP BY public.materialized_phenoview.location_id, public.materialized_phenoview.trial_design_id
 489 WITH DATA;
 490 CREATE UNIQUE INDEX locationsXtrial_designs_idx ON public.locationsXtrial_designs(location_id, trial_design_id) WITH (fillfactor=100);
 491 ALTER MATERIALIZED VIEW locationsXtrial_designs OWNER TO web_usr;
 492 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('locationsXtrial_designs', FALSE, CURRENT_TIMESTAMP);
 493 CREATE MATERIALIZED VIEW public.locationsXtrial_types AS
 494 SELECT public.materialized_phenoview.location_id,
 495     public.materialized_phenoview.trial_type_id
 496    FROM public.materialized_phenoview
 497   GROUP BY public.materialized_phenoview.location_id, public.materialized_phenoview.trial_type_id
 498 WITH DATA;
 499 CREATE UNIQUE INDEX locationsXtrial_types_idx ON public.locationsXtrial_types(location_id, trial_type_id) WITH (fillfactor=100);
 500 ALTER MATERIALIZED VIEW locationsXtrial_types OWNER TO web_usr;
 501 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('locationsXtrial_types', FALSE, CURRENT_TIMESTAMP);
 502 CREATE MATERIALIZED VIEW public.locationsXyears AS
 503 SELECT public.materialized_phenoview.location_id,
 504     public.materialized_phenoview.year_id
 505    FROM public.materialized_phenoview
 506   GROUP BY public.materialized_phenoview.location_id, public.materialized_phenoview.year_id
 507 WITH DATA;
 508 CREATE UNIQUE INDEX locationsXyears_idx ON public.locationsXyears(location_id, year_id) WITH (fillfactor=100);
 509 ALTER MATERIALIZED VIEW locationsXyears OWNER TO web_usr;
 510 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('locationsXyears', FALSE, CURRENT_TIMESTAMP);
 511
 512 CREATE MATERIALIZED VIEW public.plants AS
 513 SELECT stock.stock_id AS plant_id,
 514     stock.uniquename AS plant_name
 515    FROM stock
 516    WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plant')
 517   GROUP BY public.stock.stock_id, public.stock.uniquename
 518 WITH DATA;
 519 CREATE UNIQUE INDEX plants_idx ON public.plants(plant_id) WITH (fillfactor=100);
 520 ALTER MATERIALIZED VIEW plants OWNER TO web_usr;
 521 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plants', FALSE, CURRENT_TIMESTAMP);
 522 CREATE MATERIALIZED VIEW public.plantsXplots AS
 523 SELECT public.materialized_phenoview.plant_id,
 524     public.materialized_phenoview.plot_id
 525    FROM public.materialized_phenoview
 526   GROUP BY public.materialized_phenoview.plant_id, public.materialized_phenoview.plot_id
 527 WITH DATA;
 528 CREATE UNIQUE INDEX plantsXplots_idx ON public.plantsXplots(plant_id, plot_id) WITH (fillfactor=100);
 529 ALTER MATERIALIZED VIEW plantsXplots OWNER TO web_usr;
 530 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plantsXtraits', FALSE, CURRENT_TIMESTAMP);
 531 CREATE MATERIALIZED VIEW public.plantsXtraits AS
 532 SELECT public.materialized_phenoview.plant_id,
 533     public.materialized_phenoview.trait_id
 534    FROM public.materialized_phenoview
 535   GROUP BY public.materialized_phenoview.plant_id, public.materialized_phenoview.trait_id
 536 WITH DATA;
 537 CREATE UNIQUE INDEX plantsXtraits_idx ON public.plantsXtraits(plant_id, trait_id) WITH (fillfactor=100);
 538 ALTER MATERIALIZED VIEW plantsXtraits OWNER TO web_usr;
 539 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plantsXtraits', FALSE, CURRENT_TIMESTAMP);
 540 CREATE MATERIALIZED VIEW public.plantsXtrials AS
 541 SELECT public.materialized_phenoview.plant_id,
 542     public.materialized_phenoview.trial_id
 543    FROM public.materialized_phenoview
 544   GROUP BY public.materialized_phenoview.plant_id, public.materialized_phenoview.trial_id
 545 WITH DATA;
 546 CREATE UNIQUE INDEX plantsXtrials_idx ON public.plantsXtrials(plant_id, trial_id) WITH (fillfactor=100);
 547 ALTER MATERIALIZED VIEW plantsXtrials OWNER TO web_usr;
 548 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plantsXtrials', FALSE, CURRENT_TIMESTAMP);
 549 CREATE MATERIALIZED VIEW public.plantsXtrial_designs AS
 550 SELECT public.materialized_phenoview.plant_id,
 551     public.materialized_phenoview.trial_design_id
 552    FROM public.materialized_phenoview
 553   GROUP BY public.materialized_phenoview.plant_id, public.materialized_phenoview.trial_design_id
 554 WITH DATA;
 555 CREATE UNIQUE INDEX plantsXtrial_designs_idx ON public.plantsXtrial_designs(plant_id, trial_design_id) WITH (fillfactor=100);
 556 ALTER MATERIALIZED VIEW plantsXtrial_designs OWNER TO web_usr;
 557 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plantsXtrial_designs', FALSE, CURRENT_TIMESTAMP);
 558 CREATE MATERIALIZED VIEW public.plantsXtrial_types AS
 559 SELECT public.materialized_phenoview.plant_id,
 560     public.materialized_phenoview.trial_type_id
 561    FROM public.materialized_phenoview
 562   GROUP BY public.materialized_phenoview.plant_id, public.materialized_phenoview.trial_type_id
 563 WITH DATA;
 564 CREATE UNIQUE INDEX plantsXtrial_types_idx ON public.plantsXtrial_types(plant_id, trial_type_id) WITH (fillfactor=100);
 565 ALTER MATERIALIZED VIEW plantsXtrial_types OWNER TO web_usr;
 566 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plantsXtrial_types', FALSE, CURRENT_TIMESTAMP);
 567 CREATE MATERIALIZED VIEW public.plantsXyears AS
 568 SELECT public.materialized_phenoview.plant_id,
 569     public.materialized_phenoview.year_id
 570    FROM public.materialized_phenoview
 571   GROUP BY public.materialized_phenoview.plant_id, public.materialized_phenoview.year_id
 572 WITH DATA;
 573 CREATE UNIQUE INDEX plantsXyears_idx ON public.plantsXyears(plant_id, year_id) WITH (fillfactor=100);
 574 ALTER MATERIALIZED VIEW plantsXyears OWNER TO web_usr;
 575 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plantsXyears', FALSE, CURRENT_TIMESTAMP);
 576
 577 CREATE MATERIALIZED VIEW public.plots AS
 578 SELECT stock.stock_id AS plot_id,
 579     stock.uniquename AS plot_name
 580    FROM stock
 581    WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plot')
 582   GROUP BY public.stock.stock_id, public.stock.uniquename
 583 WITH DATA;
 584 CREATE UNIQUE INDEX plots_idx ON public.plots(plot_id) WITH (fillfactor=100);
 585 ALTER MATERIALIZED VIEW plots OWNER TO web_usr;
 586 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plots', FALSE, CURRENT_TIMESTAMP);
 587 CREATE MATERIALIZED VIEW public.plotsXtraits AS
 588 SELECT public.materialized_phenoview.plot_id,
 589     public.materialized_phenoview.trait_id
 590    FROM public.materialized_phenoview
 591   GROUP BY public.materialized_phenoview.plot_id, public.materialized_phenoview.trait_id
 592 WITH DATA;
 593 CREATE UNIQUE INDEX plotsXtraits_idx ON public.plotsXtraits(plot_id, trait_id) WITH (fillfactor=100);
 594 ALTER MATERIALIZED VIEW plotsXtraits OWNER TO web_usr;
 595 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plotsXtraits', FALSE, CURRENT_TIMESTAMP);
 596 CREATE MATERIALIZED VIEW public.plotsXtrials AS
 597 SELECT public.materialized_phenoview.plot_id,
 598     public.materialized_phenoview.trial_id
 599    FROM public.materialized_phenoview
 600   GROUP BY public.materialized_phenoview.plot_id, public.materialized_phenoview.trial_id
 601 WITH DATA;
 602 CREATE UNIQUE INDEX plotsXtrials_idx ON public.plotsXtrials(plot_id, trial_id) WITH (fillfactor=100);
 603 ALTER MATERIALIZED VIEW plotsXtrials OWNER TO web_usr;
 604 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plotsXtrials', FALSE, CURRENT_TIMESTAMP);
 605 CREATE MATERIALIZED VIEW public.plotsXtrial_designs AS
 606 SELECT public.materialized_phenoview.plot_id,
 607     public.materialized_phenoview.trial_design_id
 608    FROM public.materialized_phenoview
 609   GROUP BY public.materialized_phenoview.plot_id, public.materialized_phenoview.trial_design_id
 610 WITH DATA;
 611 CREATE UNIQUE INDEX plotsXtrial_designs_idx ON public.plotsXtrial_designs(plot_id, trial_design_id) WITH (fillfactor=100);
 612 ALTER MATERIALIZED VIEW plotsXtrial_designs OWNER TO web_usr;
 613 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plotsXtrial_designs', FALSE, CURRENT_TIMESTAMP);
 614 CREATE MATERIALIZED VIEW public.plotsXtrial_types AS
 615 SELECT public.materialized_phenoview.plot_id,
 616     public.materialized_phenoview.trial_type_id
 617    FROM public.materialized_phenoview
 618   GROUP BY public.materialized_phenoview.plot_id, public.materialized_phenoview.trial_type_id
 619 WITH DATA;
 620 CREATE UNIQUE INDEX plotsXtrial_types_idx ON public.plotsXtrial_types(plot_id, trial_type_id) WITH (fillfactor=100);
 621 ALTER MATERIALIZED VIEW plotsXtrial_types OWNER TO web_usr;
 622 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plotsXtrial_types', FALSE, CURRENT_TIMESTAMP);
 623 CREATE MATERIALIZED VIEW public.plotsXyears AS
 624 SELECT public.materialized_phenoview.plot_id,
 625     public.materialized_phenoview.year_id
 626    FROM public.materialized_phenoview
 627   GROUP BY public.materialized_phenoview.plot_id, public.materialized_phenoview.year_id
 628 WITH DATA;
 629 CREATE UNIQUE INDEX plotsXyears_idx ON public.plotsXyears(plot_id, year_id) WITH (fillfactor=100);
 630 ALTER MATERIALIZED VIEW plotsXyears OWNER TO web_usr;
 631 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plotsXyears', FALSE, CURRENT_TIMESTAMP);
 632
 633 CREATE MATERIALIZED VIEW public.traits AS
 634 SELECT   cvterm.cvterm_id AS trait_id,
 635   (((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text AS trait_name
 636   FROM cvterm
 637   JOIN dbxref ON cvterm.dbxref_id = dbxref.dbxref_id
 638   JOIN db ON dbxref.db_id = db.db_id
 639   WHERE db.db_id =
 640 (SELECT dbxref.db_id
 641    FROM stock
 642    JOIN nd_experiment_stock USING(stock_id)
 643    JOIN nd_experiment_phenotype USING(nd_experiment_id)
 644    JOIN phenotype USING(phenotype_id)
 645    JOIN cvterm ON phenotype.cvalue_id = cvterm.cvterm_id
 646    JOIN dbxref ON cvterm.dbxref_id = dbxref.dbxref_id LIMIT 1)
 647    GROUP BY public.cvterm.cvterm_id, trait_name
 648 WITH DATA;
 649 CREATE UNIQUE INDEX traits_idx ON public.traits(trait_id) WITH (fillfactor=100);
 650 ALTER MATERIALIZED VIEW traits OWNER TO web_usr;
 651 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('traits', FALSE, CURRENT_TIMESTAMP);
 652 CREATE MATERIALIZED VIEW public.traitsXtrials AS
 653 SELECT public.materialized_phenoview.trait_id,
 654     public.materialized_phenoview.trial_id
 655    FROM public.materialized_phenoview
 656   GROUP BY public.materialized_phenoview.trait_id, public.materialized_phenoview.trial_id
 657 WITH DATA;
 658 CREATE UNIQUE INDEX traitsXtrials_idx ON public.traitsXtrials(trait_id, trial_id) WITH (fillfactor=100);
 659 ALTER MATERIALIZED VIEW traitsXtrials OWNER TO web_usr;
 660 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('traitsXtrials', FALSE, CURRENT_TIMESTAMP);
 661 CREATE MATERIALIZED VIEW public.traitsXtrial_designs AS
 662 SELECT public.materialized_phenoview.trait_id,
 663     public.materialized_phenoview.trial_design_id
 664    FROM public.materialized_phenoview
 665   GROUP BY public.materialized_phenoview.trait_id, public.materialized_phenoview.trial_design_id
 666 WITH DATA;
 667 CREATE UNIQUE INDEX traitsXtrial_designs_idx ON public.traitsXtrial_designs(trait_id, trial_design_id) WITH (fillfactor=100);
 668 ALTER MATERIALIZED VIEW traitsXtrial_designs OWNER TO web_usr;
 669 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('traitsXtrial_designs', FALSE, CURRENT_TIMESTAMP);
 670 CREATE MATERIALIZED VIEW public.traitsXtrial_types AS
 671 SELECT public.materialized_phenoview.trait_id,
 672     public.materialized_phenoview.trial_type_id
 673    FROM public.materialized_phenoview
 674   GROUP BY public.materialized_phenoview.trait_id, public.materialized_phenoview.trial_type_id
 675 WITH DATA;
 676 CREATE UNIQUE INDEX traitsXtrial_types_idx ON public.traitsXtrial_types(trait_id, trial_type_id) WITH (fillfactor=100);
 677 ALTER MATERIALIZED VIEW traitsXtrial_types OWNER TO web_usr;
 678 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('traitsXtrial_types', FALSE, CURRENT_TIMESTAMP);
 679 CREATE MATERIALIZED VIEW public.traitsXyears AS
 680 SELECT public.materialized_phenoview.trait_id,
 681     public.materialized_phenoview.year_id
 682    FROM public.materialized_phenoview
 683   GROUP BY public.materialized_phenoview.trait_id, public.materialized_phenoview.year_id
 684 WITH DATA;
 685 CREATE UNIQUE INDEX traitsXyears_idx ON public.traitsXyears(trait_id, year_id) WITH (fillfactor=100);
 686 ALTER MATERIALIZED VIEW traitsXyears OWNER TO web_usr;
 687 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('traitsXyears', FALSE, CURRENT_TIMESTAMP);
 688
 689 CREATE MATERIALIZED VIEW public.trial_designs AS
 690 SELECT projectprop.value AS trial_design_id,
 691   projectprop.value AS trial_design_name
 692    FROM projectprop
 693    JOIN cvterm ON(projectprop.type_id = cvterm.cvterm_id)
 694    WHERE cvterm.name = 'design'
 695    GROUP BY projectprop.value
 696 WITH DATA;
 697 CREATE UNIQUE INDEX trial_designs_idx ON public.trial_designs(trial_design_id) WITH (fillfactor=100);
 698 ALTER MATERIALIZED VIEW trial_designs OWNER TO web_usr;
 699 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trial_designs', FALSE, CURRENT_TIMESTAMP);
 700 CREATE MATERIALIZED VIEW public.trial_designsXtrial_types AS
 701 SELECT public.materialized_phenoview.trial_design_id,
 702     public.materialized_phenoview.trial_type_id
 703    FROM public.materialized_phenoview
 704   GROUP BY public.materialized_phenoview.trial_design_id, public.materialized_phenoview.trial_type_id
 705 WITH DATA;
 706 CREATE UNIQUE INDEX trial_designsXtrial_types_idx ON public.trial_designsXtrial_types(trial_design_id, trial_type_id) WITH (fillfactor=100);
 707 ALTER MATERIALIZED VIEW trial_designsXtrial_types OWNER TO web_usr;
 708 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trial_designsXtrial_types', FALSE, CURRENT_TIMESTAMP);
 709 CREATE MATERIALIZED VIEW public.trial_designsXtrials AS
 710 SELECT public.materialized_phenoview.trial_id,
 711     public.materialized_phenoview.trial_design_id
 712    FROM public.materialized_phenoview
 713   GROUP BY public.materialized_phenoview.trial_id, public.materialized_phenoview.trial_design_id
 714 WITH DATA;
 715 CREATE UNIQUE INDEX trial_designsXtrials_idx ON public.trial_designsXtrials(trial_id, trial_design_id) WITH (fillfactor=100);
 716 ALTER MATERIALIZED VIEW trial_designsXtrials OWNER TO web_usr;
 717 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trial_designsXtrials', FALSE, CURRENT_TIMESTAMP);
 718 CREATE MATERIALIZED VIEW public.trial_designsXyears AS
 719 SELECT public.materialized_phenoview.trial_design_id,
 720     public.materialized_phenoview.year_id
 721    FROM public.materialized_phenoview
 722   GROUP BY public.materialized_phenoview.trial_design_id, public.materialized_phenoview.year_id
 723 WITH DATA;
 724 CREATE UNIQUE INDEX trial_designsXyears_idx ON public.trial_designsXyears(trial_design_id, year_id) WITH (fillfactor=100);
 725 ALTER MATERIALIZED VIEW trial_designsXyears OWNER TO web_usr;
 726 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trial_designsXyears', FALSE, CURRENT_TIMESTAMP);
 727
 728 CREATE MATERIALIZED VIEW public.trial_types AS
 729 SELECT cvterm.cvterm_id AS trial_type_id,
 730   cvterm.name AS trial_type_name
 731    FROM cvterm
 732    JOIN cv USING(cv_id)
 733    WHERE cv.name = 'project_type'
 734    GROUP BY cvterm.cvterm_id
 735 WITH DATA;
 736 CREATE UNIQUE INDEX trial_types_idx ON public.trial_types(trial_type_id) WITH (fillfactor=100);
 737 ALTER MATERIALIZED VIEW trial_types OWNER TO web_usr;
 738 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trial_types', FALSE, CURRENT_TIMESTAMP);
 739 CREATE MATERIALIZED VIEW public.trial_typesXtrials AS
 740 SELECT public.materialized_phenoview.trial_id,
 741     public.materialized_phenoview.trial_type_id
 742    FROM public.materialized_phenoview
 743   GROUP BY public.materialized_phenoview.trial_id, public.materialized_phenoview.trial_type_id
 744 WITH DATA;
 745 CREATE UNIQUE INDEX trial_typesXtrials_idx ON public.trial_typesXtrials(trial_id, trial_type_id) WITH (fillfactor=100);
 746 ALTER MATERIALIZED VIEW trial_typesXtrials OWNER TO web_usr;
 747 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trial_typesXtrials', FALSE, CURRENT_TIMESTAMP);
 748 CREATE MATERIALIZED VIEW public.trial_typesXyears AS
 749 SELECT public.materialized_phenoview.trial_type_id,
 750     public.materialized_phenoview.year_id
 751    FROM public.materialized_phenoview
 752   GROUP BY public.materialized_phenoview.trial_type_id, public.materialized_phenoview.year_id
 753 WITH DATA;
 754 CREATE UNIQUE INDEX trial_typesXyears_idx ON public.trial_typesXyears(trial_type_id, year_id) WITH (fillfactor=100);
 755 ALTER MATERIALIZED VIEW trial_typesXyears OWNER TO web_usr;
 756 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trial_typesXyears', FALSE, CURRENT_TIMESTAMP);
 757
 758 CREATE MATERIALIZED VIEW public.trials AS
 759 SELECT project.project_id AS trial_id,
 760     project.name AS trial_name
 761    FROM project join projectprop USING (project_id)
 762    WHERE projectprop.type_id IS DISTINCT FROM (SELECT cvterm_id from cvterm where cvterm.name = 'breeding_program')
 763    AND projectprop.type_id IS DISTINCT FROM (SELECT cvterm_id from cvterm where cvterm.name = 'trial_folder')
 764   GROUP BY public.project.project_id, public.project.name
 765 WITH DATA;
 766 CREATE UNIQUE INDEX trials_idx ON public.trials(trial_id) WITH (fillfactor=100);
 767 ALTER MATERIALIZED VIEW trials OWNER TO web_usr;
 768 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trials', FALSE, CURRENT_TIMESTAMP);
 769 CREATE MATERIALIZED VIEW public.trialsXyears AS
 770 SELECT public.materialized_phenoview.trial_id,
 771     public.materialized_phenoview.year_id
 772    FROM public.materialized_phenoview
 773   GROUP BY public.materialized_phenoview.trial_id, public.materialized_phenoview.year_id
 774 WITH DATA;
 775 CREATE UNIQUE INDEX trialsXyears_idx ON public.trialsXyears(trial_id, year_id) WITH (fillfactor=100);
 776 ALTER MATERIALIZED VIEW trialsXyears OWNER TO web_usr;
 777 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trialsXyears', FALSE, CURRENT_TIMESTAMP);
 778
 779 CREATE MATERIALIZED VIEW public.years AS
 780 SELECT projectprop.value AS year_id,
 781   projectprop.value AS year_name
 782    FROM projectprop
 783    WHERE projectprop.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'project year')
 784   GROUP BY public.projectprop.value
 785 WITH DATA;
 786 CREATE UNIQUE INDEX years_idx ON public.years(year_id) WITH (fillfactor=100);
 787 ALTER MATERIALIZED VIEW years OWNER TO web_usr;
 788 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('years', FALSE, CURRENT_TIMESTAMP);
 789
 790 CREATE OR REPLACE FUNCTION public.refresh_materialized_views() RETURNS VOID AS '
 791 REFRESH MATERIALIZED VIEW public.materialized_phenoview;
 792 REFRESH MATERIALIZED VIEW public.materialized_genoview;
 793 REFRESH MATERIALIZED VIEW public.accessions;
 794 REFRESH MATERIALIZED VIEW public.accessionsXbreeding_programs;
 795 REFRESH MATERIALIZED VIEW public.accessionsXlocations;
 796 REFRESH MATERIALIZED VIEW public.accessionsXgenotyping_protocols;
 797 REFRESH MATERIALIZED VIEW public.accessionsXplants;
 798 REFRESH MATERIALIZED VIEW public.accessionsXplots;
 799 REFRESH MATERIALIZED VIEW public.accessionsXtraits;
 800 REFRESH MATERIALIZED VIEW public.accessionsXtrial_designs;
 801 REFRESH MATERIALIZED VIEW public.accessionsXtrial_types;
 802 REFRESH MATERIALIZED VIEW public.accessionsXtrials;
 803 REFRESH MATERIALIZED VIEW public.accessionsXyears;
 804 REFRESH MATERIALIZED VIEW public.breeding_programs;
 805 REFRESH MATERIALIZED VIEW public.breeding_programsXlocations;
 806 REFRESH MATERIALIZED VIEW public.breeding_programsXgenotyping_protocols;
 807 REFRESH MATERIALIZED VIEW public.breeding_programsXplants;
 808 REFRESH MATERIALIZED VIEW public.breeding_programsXplots;
 809 REFRESH MATERIALIZED VIEW public.breeding_programsXtraits;
 810 REFRESH MATERIALIZED VIEW public.breeding_programsXtrial_designs;
 811 REFRESH MATERIALIZED VIEW public.breeding_programsXtrial_types;
 812 REFRESH MATERIALIZED VIEW public.breeding_programsXtrials;
 813 REFRESH MATERIALIZED VIEW public.breeding_programsXyears;
 814 REFRESH MATERIALIZED VIEW public.genotyping_protocols;
 815 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXlocations;
 816 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXplants;
 817 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXplots;
 818 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXtraits;
 819 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXtrial_designs;
 820 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXtrial_types;
 821 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXtrials;
 822 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXyears;
 823 REFRESH MATERIALIZED VIEW public.locations;
 824 REFRESH MATERIALIZED VIEW public.locationsXplants;
 825 REFRESH MATERIALIZED VIEW public.locationsXplots;
 826 REFRESH MATERIALIZED VIEW public.locationsXtraits;
 827 REFRESH MATERIALIZED VIEW public.locationsXtrial_designs;
 828 REFRESH MATERIALIZED VIEW public.locationsXtrial_types;
 829 REFRESH MATERIALIZED VIEW public.locationsXtrials;
 830 REFRESH MATERIALIZED VIEW public.locationsXyears;
 831 REFRESH MATERIALIZED VIEW public.plants;
 832 REFRESH MATERIALIZED VIEW public.plantsXplots;
 833 REFRESH MATERIALIZED VIEW public.plantsXtraits;
 834 REFRESH MATERIALIZED VIEW public.plantsXtrial_designs;
 835 REFRESH MATERIALIZED VIEW public.plantsXtrial_types;
 836 REFRESH MATERIALIZED VIEW public.plantsXtrials;
 837 REFRESH MATERIALIZED VIEW public.plantsXyears;
 838 REFRESH MATERIALIZED VIEW public.plots;
 839 REFRESH MATERIALIZED VIEW public.plotsXtraits;
 840 REFRESH MATERIALIZED VIEW public.plotsXtrial_designs;
 841 REFRESH MATERIALIZED VIEW public.plotsXtrial_types;
 842 REFRESH MATERIALIZED VIEW public.plotsXtrials;
 843 REFRESH MATERIALIZED VIEW public.plotsXyears;
 844 REFRESH MATERIALIZED VIEW public.traits;
 845 REFRESH MATERIALIZED VIEW public.traitsXtrial_designs;
 846 REFRESH MATERIALIZED VIEW public.traitsXtrial_types;
 847 REFRESH MATERIALIZED VIEW public.traitsXtrials;
 848 REFRESH MATERIALIZED VIEW public.traitsXyears;
 849 REFRESH MATERIALIZED VIEW public.trial_designs;
 850 REFRESH MATERIALIZED VIEW public.trial_designsXtrial_types;
 851 REFRESH MATERIALIZED VIEW public.trial_designsXtrials;
 852 REFRESH MATERIALIZED VIEW public.trial_designsXyears;
 853 REFRESH MATERIALIZED VIEW public.trial_types;
 854 REFRESH MATERIALIZED VIEW public.trial_typesXtrials;
 855 REFRESH MATERIALIZED VIEW public.trial_typesXyears;
 856 REFRESH MATERIALIZED VIEW public.trials;
 857 REFRESH MATERIALIZED VIEW public.trialsXyears;
 858 REFRESH MATERIALIZED VIEW public.years;
 859 UPDATE public.matviews SET currently_refreshing=FALSE, last_refresh=CURRENT_TIMESTAMP;'
 860     LANGUAGE SQL;
 861
 862 ALTER FUNCTION public.refresh_materialized_views() OWNER TO web_usr;
 863
 864 CREATE OR REPLACE FUNCTION public.refresh_materialized_views_concurrently() RETURNS VOID AS '
 865 REFRESH MATERIALIZED VIEW CONCURRENTLY public.materialized_phenoview;
 866 REFRESH MATERIALIZED VIEW CONCURRENTLY public.materialized_genoview;
 867 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessions;
 868 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXbreeding_programs;
 869 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXlocations;
 870 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXgenotyping_protocols;
 871 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXplants;
 872 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXplots;
 873 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXtraits;
 874 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXtrial_designs;
 875 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXtrial_types;
 876 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXtrials;
 877 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXyears;
 878 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programs;
 879 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXlocations;
 880 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXgenotyping_protocols;
 881 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXplants;
 882 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXplots;
 883 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXtraits;
 884 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXtrial_designs;
 885 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXtrial_types;
 886 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXtrials;
 887 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXyears;
 888 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocols;
 889 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXlocations;
 890 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXplants;
 891 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXplots;
 892 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXtraits;
 893 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXtrial_designs;
 894 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXtrial_types;
 895 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXtrials;
 896 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXyears;
 897 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locations;
 898 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXplants;
 899 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXplots;
 900 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXtraits;
 901 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXtrial_designs;
 902 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXtrial_types;
 903 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXtrials;
 904 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXyears;
 905 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plants;
 906 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXplots;
 907 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXtraits;
 908 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXtrial_designs;
 909 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXtrial_types;
 910 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXtrials;
 911 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXyears;
 912 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plots;
 913 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXtraits;
 914 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXtrial_designs;
 915 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXtrial_types;
 916 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXtrials;
 917 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXyears;
 918 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traits;
 919 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traitsXtrial_designs;
 920 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traitsXtrial_types;
 921 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traitsXtrials;
 922 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traitsXyears;
 923 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_designs;
 924 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_designsXtrial_types;
 925 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_designsXtrials;
 926 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_designsXyears;
 927 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_types;
 928 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_typesXtrials;
 929 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_typesXyears;
 930 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trials;
 931 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trialsXyears;
 932 REFRESH MATERIALIZED VIEW CONCURRENTLY public.years;
 933 UPDATE public.matviews SET currently_refreshing=FALSE, last_refresh=CURRENT_TIMESTAMP;'
 934     LANGUAGE SQL;
 935
 936 ALTER FUNCTION public.refresh_materialized_views_concurrently() OWNER TO web_usr;
 937 --
 938
 939 EOSQL
 940
 941 print "You're done!\n";
 942 }
 943
 944
 945 ####
 946 1; #
 947 ####