db/00048/ImplementWizardView.pm

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6 ImplementWizardView.pm
   7
   8 =head1 SYNOPSIS
   9
  10 mx-run ImplementWizardView [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 creates a materialized view, as well as indexes and recursive views to simplify and speed up the sort of filtering queries necessary for the wizard.
  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 ImplementWizardView;
  34
  35 use Moose;
  36 extends 'CXGN::Metadata::Dbpatch';
  37
  38
  39 has '+description' => ( default => <<'' );
  40 Description of this patch goes here
  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 CREATE MATERIALIZED VIEW public.materialized_fullview AS
  56  SELECT plot.uniquename AS plot_name,
  57     stock_relationship.subject_id AS plot_id,
  58     accession.uniquename AS accession_name,
  59     stock_relationship.object_id AS accession_id,
  60     nd_experiment.nd_geolocation_id AS location_id,
  61     nd_geolocation.description AS location_name,
  62     projectprop.value AS year_id,
  63     projectprop.value AS year_name,
  64     project_relationship.subject_project_id AS trial_id,
  65     trial.name AS trial_name,
  66     project_relationship.object_project_id AS breeding_program_id,
  67     breeding_program.name AS breeding_program_name,
  68     cvterm.cvterm_id AS trait_id,
  69     (((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text AS trait_name,
  70     phenotype.phenotype_id,
  71     phenotype.value AS phenotype_value,
  72     nd_experiment_protocol.nd_protocol_id AS genotyping_protocol_id,
  73     nd_protocol.name AS genotyping_protocol_name
  74    FROM stock accession
  75      LEFT JOIN stock_relationship ON accession.stock_id = stock_relationship.object_id
  76      LEFT JOIN stock plot ON stock_relationship.subject_id = plot.stock_id
  77      LEFT JOIN nd_experiment_stock nd_experiment_plot ON stock_relationship.subject_id = nd_experiment_plot.stock_id
  78      LEFT JOIN nd_experiment_stock nd_experiment_accession ON accession.stock_id = nd_experiment_accession.stock_id
  79      LEFT JOIN nd_experiment ON nd_experiment_plot.nd_experiment_id = nd_experiment.nd_experiment_id
  80      LEFT JOIN nd_geolocation ON nd_experiment.nd_geolocation_id = nd_geolocation.nd_geolocation_id
  81      LEFT JOIN nd_experiment_protocol ON nd_experiment_accession.nd_experiment_id = nd_experiment_protocol.nd_experiment_id
  82      LEFT JOIN nd_protocol ON nd_experiment_protocol.nd_protocol_id = nd_protocol.nd_protocol_id
  83      LEFT JOIN nd_experiment_project ON nd_experiment_plot.nd_experiment_id = nd_experiment_project.nd_experiment_id
  84      LEFT JOIN project trial ON nd_experiment_project.project_id = trial.project_id
  85      LEFT JOIN project_relationship ON trial.project_id = project_relationship.subject_project_id
  86      LEFT JOIN project breeding_program ON project_relationship.object_project_id = breeding_program.project_id
  87      LEFT JOIN projectprop ON project_relationship.subject_project_id = projectprop.project_id
  88      LEFT JOIN nd_experiment_phenotype ON nd_experiment_plot.nd_experiment_id = nd_experiment_phenotype.nd_experiment_id
  89      LEFT JOIN phenotype ON nd_experiment_phenotype.phenotype_id = phenotype.phenotype_id
  90      LEFT JOIN cvterm ON phenotype.cvalue_id = cvterm.cvterm_id
  91      LEFT JOIN dbxref ON cvterm.dbxref_id = dbxref.dbxref_id
  92      LEFT JOIN db ON dbxref.db_id = db.db_id
  93   WHERE accession.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'accession')  AND projectprop.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'project year' )
  94   GROUP BY stock_relationship.subject_id, cvterm.cvterm_id, plot.uniquename, accession.uniquename, stock_relationship.object_id, (((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text, trial.name, project_relationship.subject_project_id, breeding_program.name, project_relationship.object_project_id, projectprop.value, nd_experiment.nd_geolocation_id, nd_geolocation.description, phenotype.phenotype_id, phenotype.value, nd_experiment_protocol.nd_protocol_id, nd_protocol.name;
  95
  96 CREATE UNIQUE INDEX materializedfullview_idx ON public.materialized_fullview(trial_id, plot_id, phenotype_id, genotyping_protocol_id) WITH (fillfactor=100);
  97 CREATE INDEX accession_id_idx ON public.materialized_fullview(accession_id) WITH (fillfactor=100);
  98 CREATE INDEX breeding_program_id_idx ON public.materialized_fullview(breeding_program_id) WITH (fillfactor=100);
  99 CREATE INDEX genotyping_protocol_id_idx ON public.materialized_fullview(genotyping_protocol_id) WITH (fillfactor=100);
 100 CREATE INDEX location_id_idx ON public.materialized_fullview(location_id) WITH (fillfactor=100);
 101 CREATE INDEX phenotype_id_idx ON public.materialized_fullview(phenotype_id) WITH (fillfactor=100);
 102 CREATE INDEX plot_id_idx ON public.materialized_fullview(plot_id) WITH (fillfactor=100);
 103 CREATE INDEX trait_id_idx ON public.materialized_fullview(trait_id) WITH (fillfactor=100);
 104 CREATE INDEX trial_id_idx ON public.materialized_fullview(trial_id) WITH (fillfactor=100);
 105 CREATE INDEX year_id_idx ON public.materialized_fullview(year_id) WITH (fillfactor=100);
 106 GRANT SELECT ON materialized_fullview to web_usr;
 107
 108 CREATE TABLE public.matviews (
 109     mv_id SERIAL PRIMARY KEY
 110   , mv_name NAME NOT NULL
 111   , mv_dependents NAME ARRAY
 112   , currently_refreshing BOOLEAN
 113   , last_refresh TIMESTAMP WITH TIME ZONE
 114 );
 115 GRANT SELECT, UPDATE ON TABLE public.matviews to web_usr;
 116 INSERT INTO matviews (mv_name, mv_dependents, currently_refreshing, last_refresh) VALUES ('materialized_fullview', '{"accessions", "accessionsXbreeding_programs","accessionsXgenotyping_protocols","accessionsXlocations","accessionsXplots","accessionsXtraits","accessionsXtrials","accessionsXyears","breeding_programs","breeding_programsXgenotyping_protocols","breeding_programsXlocations","breeding_programsXplots","breeding_programsXtraits","breeding_programsXtrials","breeding_programsXyears","genotyping_protocols","genotyping_protocolsXlocations","genotyping_protocolsXplots","genotyping_protocolsXtraits","genotyping_protocolsXtrials","genotyping_protocolsXyears","locations","locationsXplots","locationsXtraits","locationsXtrials","locationsXyears","plots","plotsXtraits","plotsXtrials","plotsXyears","traits","traitsXtrials","traitsXyears","trials","trialsXyears","years"}', FALSE, CURRENT_TIMESTAMP);
 117
 118 CREATE MATERIALIZED VIEW public.accessions AS
 119 SELECT public.materialized_fullview.accession_id,
 120     public.materialized_fullview.accession_name
 121    FROM public.materialized_fullview
 122   GROUP BY public.materialized_fullview.accession_id, public.materialized_fullview.accession_name;
 123 CREATE UNIQUE INDEX accessions_idx ON public.accessions(accession_id) WITH (fillfactor=100);
 124 GRANT SELECT ON accessions to web_usr;
 125 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('accessions', FALSE, CURRENT_TIMESTAMP);
 126 CREATE MATERIALIZED VIEW public.accessionsXbreeding_programs AS
 127 SELECT public.materialized_fullview.accession_id,
 128     public.materialized_fullview.breeding_program_id
 129    FROM public.materialized_fullview
 130   GROUP BY public.materialized_fullview.accession_id, public.materialized_fullview.breeding_program_id;
 131 CREATE UNIQUE INDEX accessionsXbreeding_programs_idx ON public.accessionsXbreeding_programs(accession_id, breeding_program_id) WITH (fillfactor=100);
 132 GRANT SELECT ON accessionsXbreeding_programs to web_usr;
 133 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('accessionsXbreeding_programs', FALSE, CURRENT_TIMESTAMP);
 134 CREATE MATERIALIZED VIEW public.accessionsXgenotyping_protocols AS
 135 SELECT public.materialized_fullview.accession_id,
 136     public.materialized_fullview.genotyping_protocol_id
 137    FROM public.materialized_fullview
 138   GROUP BY public.materialized_fullview.accession_id, public.materialized_fullview.genotyping_protocol_id;
 139 CREATE UNIQUE INDEX accessionsXgenotyping_protocols_idx ON public.accessionsXgenotyping_protocols(accession_id, genotyping_protocol_id) WITH (fillfactor=100);
 140 GRANT SELECT ON accessionsXgenotyping_protocols to web_usr;
 141 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('accessionsXgenotyping_protocols', FALSE, CURRENT_TIMESTAMP);
 142 CREATE MATERIALIZED VIEW public.accessionsXlocations AS
 143 SELECT public.materialized_fullview.accession_id,
 144     public.materialized_fullview.location_id
 145    FROM public.materialized_fullview
 146   GROUP BY public.materialized_fullview.accession_id, public.materialized_fullview.location_id;
 147 CREATE UNIQUE INDEX accessionsXlocations_idx ON public.accessionsXlocations(accession_id, location_id) WITH (fillfactor=100);
 148 GRANT SELECT ON accessionsXlocations to web_usr;
 149 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('accessionsXlocations', FALSE, CURRENT_TIMESTAMP);
 150 CREATE MATERIALIZED VIEW public.accessionsXplots AS
 151 SELECT public.materialized_fullview.accession_id,
 152     public.materialized_fullview.plot_id
 153    FROM public.materialized_fullview
 154   GROUP BY public.materialized_fullview.accession_id, public.materialized_fullview.plot_id;
 155 CREATE UNIQUE INDEX accessionsXplots_idx ON public.accessionsXplots(accession_id, plot_id) WITH (fillfactor=100);
 156 GRANT SELECT ON accessionsXplots to web_usr;
 157 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('accessionsXplots', FALSE, CURRENT_TIMESTAMP);
 158 CREATE MATERIALIZED VIEW public.accessionsXtraits AS
 159 SELECT public.materialized_fullview.accession_id,
 160     public.materialized_fullview.trait_id
 161    FROM public.materialized_fullview
 162   GROUP BY public.materialized_fullview.accession_id, public.materialized_fullview.trait_id;
 163 CREATE UNIQUE INDEX accessionsXtraits_idx ON public.accessionsXtraits(accession_id, trait_id) WITH (fillfactor=100);
 164 GRANT SELECT ON accessionsXtraits to web_usr;
 165 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('accessionsXtraits', FALSE, CURRENT_TIMESTAMP);
 166 CREATE MATERIALIZED VIEW public.accessionsXtrials AS
 167 SELECT public.materialized_fullview.accession_id,
 168     public.materialized_fullview.trial_id
 169    FROM public.materialized_fullview
 170   GROUP BY public.materialized_fullview.accession_id, public.materialized_fullview.trial_id;
 171 CREATE UNIQUE INDEX accessionsXtrials_idx ON public.accessionsXtrials(accession_id, trial_id) WITH (fillfactor=100);
 172 GRANT SELECT ON accessionsXtrials to web_usr;
 173 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('accessionsXtrials', FALSE, CURRENT_TIMESTAMP);
 174 CREATE MATERIALIZED VIEW public.accessionsXyears AS
 175 SELECT public.materialized_fullview.accession_id,
 176     public.materialized_fullview.year_id
 177    FROM public.materialized_fullview
 178   GROUP BY public.materialized_fullview.accession_id, public.materialized_fullview.year_id;
 179 CREATE UNIQUE INDEX accessionsXyears_idx ON public.accessionsXyears(accession_id, year_id) WITH (fillfactor=100);
 180 GRANT SELECT ON accessionsXyears to web_usr;
 181 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('accessionsXyears', FALSE, CURRENT_TIMESTAMP);
 182
 183 CREATE MATERIALIZED VIEW public.breeding_programs AS
 184 SELECT public.materialized_fullview.breeding_program_id,
 185     public.materialized_fullview.breeding_program_name
 186    FROM public.materialized_fullview
 187   GROUP BY public.materialized_fullview.breeding_program_id, public.materialized_fullview.breeding_program_name;
 188 CREATE UNIQUE INDEX breeding_programs_idx ON public.breeding_programs(breeding_program_id) WITH (fillfactor=100);
 189 GRANT SELECT ON breeding_programs to web_usr;
 190 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('breeding_programs', FALSE, CURRENT_TIMESTAMP);
 191 CREATE MATERIALIZED VIEW public.breeding_programsXgenotyping_protocols AS
 192 SELECT public.materialized_fullview.breeding_program_id,
 193     public.materialized_fullview.genotyping_protocol_id
 194    FROM public.materialized_fullview
 195   GROUP BY public.materialized_fullview.breeding_program_id, public.materialized_fullview.genotyping_protocol_id;
 196 CREATE UNIQUE INDEX breeding_programsXgenotyping_protocols_idx ON public.breeding_programsXgenotyping_protocols(breeding_program_id, genotyping_protocol_id) WITH (fillfactor=100);
 197 GRANT SELECT ON breeding_programsXgenotyping_protocols to web_usr;
 198 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('breeding_programsXgenotyping_protocols', FALSE, CURRENT_TIMESTAMP);
 199 CREATE MATERIALIZED VIEW public.breeding_programsXlocations AS
 200 SELECT public.materialized_fullview.breeding_program_id,
 201     public.materialized_fullview.location_id
 202    FROM public.materialized_fullview
 203   GROUP BY public.materialized_fullview.breeding_program_id, public.materialized_fullview.location_id;
 204 CREATE UNIQUE INDEX breeding_programsXlocations_idx ON public.breeding_programsXlocations(breeding_program_id, location_id) WITH (fillfactor=100);
 205 GRANT SELECT ON breeding_programsXlocations to web_usr;
 206 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('breeding_programsXlocations', FALSE, CURRENT_TIMESTAMP);
 207 CREATE MATERIALIZED VIEW public.breeding_programsXplots AS
 208 SELECT public.materialized_fullview.breeding_program_id,
 209     public.materialized_fullview.plot_id
 210    FROM public.materialized_fullview
 211   GROUP BY public.materialized_fullview.breeding_program_id, public.materialized_fullview.plot_id;
 212 CREATE UNIQUE INDEX breeding_programsXplots_idx ON public.breeding_programsXplots(breeding_program_id, plot_id) WITH (fillfactor=100);
 213 GRANT SELECT ON breeding_programsXplots to web_usr;
 214 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('breeding_programsXplots', FALSE, CURRENT_TIMESTAMP);
 215 CREATE MATERIALIZED VIEW public.breeding_programsXtraits AS
 216 SELECT public.materialized_fullview.breeding_program_id,
 217     public.materialized_fullview.trait_id
 218    FROM public.materialized_fullview
 219   GROUP BY public.materialized_fullview.breeding_program_id, public.materialized_fullview.trait_id;
 220 CREATE UNIQUE INDEX breeding_programsXtraits_idx ON public.breeding_programsXtraits(breeding_program_id, trait_id) WITH (fillfactor=100);
 221 GRANT SELECT ON breeding_programsXtraits to web_usr;
 222 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('breeding_programsXtraits', FALSE, CURRENT_TIMESTAMP);
 223 CREATE MATERIALIZED VIEW public.breeding_programsXtrials AS
 224 SELECT public.materialized_fullview.breeding_program_id,
 225     public.materialized_fullview.trial_id
 226    FROM public.materialized_fullview
 227   GROUP BY public.materialized_fullview.breeding_program_id, public.materialized_fullview.trial_id;
 228 CREATE UNIQUE INDEX breeding_programsXtrials_idx ON public.breeding_programsXtrials(breeding_program_id, trial_id) WITH (fillfactor=100);
 229 GRANT SELECT ON breeding_programsXtrials to web_usr;
 230 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('breeding_programsXtrials', FALSE, CURRENT_TIMESTAMP);
 231 CREATE MATERIALIZED VIEW public.breeding_programsXyears AS
 232 SELECT public.materialized_fullview.breeding_program_id,
 233     public.materialized_fullview.year_id
 234    FROM public.materialized_fullview
 235   GROUP BY public.materialized_fullview.breeding_program_id, public.materialized_fullview.year_id;
 236 CREATE UNIQUE INDEX breeding_programsXyears_idx ON public.breeding_programsXyears(breeding_program_id, year_id) WITH (fillfactor=100);
 237 GRANT SELECT ON breeding_programsXyears to web_usr;
 238 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('breeding_programsXyears', FALSE, CURRENT_TIMESTAMP);
 239
 240 CREATE MATERIALIZED VIEW public.genotyping_protocols AS
 241 SELECT public.materialized_fullview.genotyping_protocol_id,
 242     public.materialized_fullview.genotyping_protocol_name
 243    FROM public.materialized_fullview
 244   GROUP BY public.materialized_fullview.genotyping_protocol_id, public.materialized_fullview.genotyping_protocol_name;
 245 CREATE UNIQUE INDEX genotyping_protocols_idx ON public.genotyping_protocols(genotyping_protocol_id) WITH (fillfactor=100);
 246 GRANT SELECT ON genotyping_protocols to web_usr;
 247 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('genotyping_protocols', FALSE, CURRENT_TIMESTAMP);
 248 CREATE MATERIALIZED VIEW public.genotyping_protocolsXlocations AS
 249 SELECT public.materialized_fullview.genotyping_protocol_id,
 250     public.materialized_fullview.location_id
 251    FROM public.materialized_fullview
 252   GROUP BY public.materialized_fullview.genotyping_protocol_id, public.materialized_fullview.location_id;
 253 CREATE UNIQUE INDEX genotyping_protocolsXlocations_idx ON public.genotyping_protocolsXlocations(genotyping_protocol_id, location_id) WITH (fillfactor=100);
 254 GRANT SELECT ON genotyping_protocolsXlocations to web_usr;
 255 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('genotyping_protocolsXlocations', FALSE, CURRENT_TIMESTAMP);
 256 CREATE MATERIALIZED VIEW public.genotyping_protocolsXplots AS
 257 SELECT public.materialized_fullview.genotyping_protocol_id,
 258     public.materialized_fullview.plot_id
 259    FROM public.materialized_fullview
 260   GROUP BY public.materialized_fullview.genotyping_protocol_id, public.materialized_fullview.plot_id;
 261 CREATE UNIQUE INDEX genotyping_protocolsXplots_idx ON public.genotyping_protocolsXplots(genotyping_protocol_id, plot_id) WITH (fillfactor=100);
 262 GRANT SELECT ON genotyping_protocolsXplots to web_usr;
 263 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('genotyping_protocolsXplots', FALSE, CURRENT_TIMESTAMP);
 264 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtraits AS
 265 SELECT public.materialized_fullview.genotyping_protocol_id,
 266     public.materialized_fullview.trait_id
 267    FROM public.materialized_fullview
 268   GROUP BY public.materialized_fullview.genotyping_protocol_id, public.materialized_fullview.trait_id;
 269 CREATE UNIQUE INDEX genotyping_protocolsXtraits_idx ON public.genotyping_protocolsXtraits(genotyping_protocol_id, trait_id) WITH (fillfactor=100);
 270 GRANT SELECT ON genotyping_protocolsXtraits to web_usr;
 271 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('genotyping_protocolsXtraits', FALSE, CURRENT_TIMESTAMP);
 272 CREATE MATERIALIZED VIEW public.genotyping_protocolsXtrials AS
 273 SELECT public.materialized_fullview.genotyping_protocol_id,
 274     public.materialized_fullview.trial_id
 275    FROM public.materialized_fullview
 276   GROUP BY public.materialized_fullview.genotyping_protocol_id, public.materialized_fullview.trial_id;
 277 CREATE UNIQUE INDEX genotyping_protocolsXtrials_idx ON public.genotyping_protocolsXtrials(genotyping_protocol_id, trial_id) WITH (fillfactor=100);
 278 GRANT SELECT ON genotyping_protocolsXtrials to web_usr;
 279 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('genotyping_protocolsXtrials', FALSE, CURRENT_TIMESTAMP);
 280 CREATE MATERIALIZED VIEW public.genotyping_protocolsXyears AS
 281 SELECT public.materialized_fullview.genotyping_protocol_id,
 282     public.materialized_fullview.year_id
 283    FROM public.materialized_fullview
 284   GROUP BY public.materialized_fullview.genotyping_protocol_id, public.materialized_fullview.year_id;
 285 CREATE UNIQUE INDEX genotyping_protocolsXyears_idx ON public.genotyping_protocolsXyears(genotyping_protocol_id, year_id) WITH (fillfactor=100);
 286 GRANT SELECT ON genotyping_protocolsXyears to web_usr;
 287 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('genotyping_protocolsXyears', FALSE, CURRENT_TIMESTAMP);
 288
 289 CREATE MATERIALIZED VIEW public.locations AS
 290 SELECT public.materialized_fullview.location_id,
 291     public.materialized_fullview.location_name
 292    FROM public.materialized_fullview
 293   GROUP BY public.materialized_fullview.location_id, public.materialized_fullview.location_name;
 294 CREATE UNIQUE INDEX locations_idx ON public.locations(location_id) WITH (fillfactor=100);
 295 GRANT SELECT ON locations to web_usr;
 296 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('locations', FALSE, CURRENT_TIMESTAMP);
 297 CREATE MATERIALIZED VIEW public.locationsXplots AS
 298 SELECT public.materialized_fullview.location_id,
 299     public.materialized_fullview.plot_id
 300    FROM public.materialized_fullview
 301   GROUP BY public.materialized_fullview.location_id, public.materialized_fullview.plot_id;
 302 CREATE UNIQUE INDEX locationsXplots_idx ON public.locationsXplots(location_id, plot_id) WITH (fillfactor=100);
 303 GRANT SELECT ON locationsXplots to web_usr;
 304 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('locationsXplots', FALSE, CURRENT_TIMESTAMP);
 305 CREATE MATERIALIZED VIEW public.locationsXtraits AS
 306 SELECT public.materialized_fullview.location_id,
 307     public.materialized_fullview.trait_id
 308    FROM public.materialized_fullview
 309   GROUP BY public.materialized_fullview.location_id, public.materialized_fullview.trait_id;
 310 CREATE UNIQUE INDEX locationsXtraits_idx ON public.locationsXtraits(location_id, trait_id) WITH (fillfactor=100);
 311 GRANT SELECT ON locationsXtraits to web_usr;
 312 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('locationsXtraits', FALSE, CURRENT_TIMESTAMP);
 313 CREATE MATERIALIZED VIEW public.locationsXtrials AS
 314 SELECT public.materialized_fullview.location_id,
 315     public.materialized_fullview.trial_id
 316    FROM public.materialized_fullview
 317   GROUP BY public.materialized_fullview.location_id, public.materialized_fullview.trial_id;
 318 CREATE UNIQUE INDEX locationsXtrials_idx ON public.locationsXtrials(location_id, trial_id) WITH (fillfactor=100);
 319 GRANT SELECT ON locationsXtrials to web_usr;
 320 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('locationsXtrials', FALSE, CURRENT_TIMESTAMP);
 321 CREATE MATERIALIZED VIEW public.locationsXyears AS
 322 SELECT public.materialized_fullview.location_id,
 323     public.materialized_fullview.year_id
 324    FROM public.materialized_fullview
 325   GROUP BY public.materialized_fullview.location_id, public.materialized_fullview.year_id;
 326 CREATE UNIQUE INDEX locationsXyears_idx ON public.locationsXyears(location_id, year_id) WITH (fillfactor=100);
 327 GRANT SELECT ON locationsXyears to web_usr;
 328 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('locationsXyears', FALSE, CURRENT_TIMESTAMP);
 329
 330 CREATE MATERIALIZED VIEW public.plots AS
 331 SELECT public.materialized_fullview.plot_id,
 332     public.materialized_fullview.plot_name
 333    FROM public.materialized_fullview
 334   GROUP BY public.materialized_fullview.plot_id, public.materialized_fullview.plot_name;
 335 CREATE UNIQUE INDEX plots_idx ON public.plots(plot_id) WITH (fillfactor=100);
 336 GRANT SELECT ON plots to web_usr;
 337 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plots', FALSE, CURRENT_TIMESTAMP);
 338 CREATE MATERIALIZED VIEW public.plotsXtraits AS
 339 SELECT public.materialized_fullview.plot_id,
 340     public.materialized_fullview.trait_id
 341    FROM public.materialized_fullview
 342   GROUP BY public.materialized_fullview.plot_id, public.materialized_fullview.trait_id;
 343 CREATE UNIQUE INDEX plotsXtraits_idx ON public.plotsXtraits(plot_id, trait_id) WITH (fillfactor=100);
 344 GRANT SELECT ON plotsXtraits to web_usr;
 345 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plotsXtraits', FALSE, CURRENT_TIMESTAMP);
 346 CREATE MATERIALIZED VIEW public.plotsXtrials AS
 347 SELECT public.materialized_fullview.plot_id,
 348     public.materialized_fullview.trial_id
 349    FROM public.materialized_fullview
 350   GROUP BY public.materialized_fullview.plot_id, public.materialized_fullview.trial_id;
 351 CREATE UNIQUE INDEX plotsXtrials_idx ON public.plotsXtrials(plot_id, trial_id) WITH (fillfactor=100);
 352 GRANT SELECT ON plotsXtrials to web_usr;
 353 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plotsXtrials', FALSE, CURRENT_TIMESTAMP);
 354 CREATE MATERIALIZED VIEW public.plotsXyears AS
 355 SELECT public.materialized_fullview.plot_id,
 356     public.materialized_fullview.year_id
 357    FROM public.materialized_fullview
 358   GROUP BY public.materialized_fullview.plot_id, public.materialized_fullview.year_id;
 359 CREATE UNIQUE INDEX plotsXyears_idx ON public.plotsXyears(plot_id, year_id) WITH (fillfactor=100);
 360 GRANT SELECT ON plotsXyears to web_usr;
 361 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('plotsXyears', FALSE, CURRENT_TIMESTAMP);
 362
 363 CREATE MATERIALIZED VIEW public.traits AS
 364 SELECT public.materialized_fullview.trait_id,
 365     public.materialized_fullview.trait_name
 366    FROM public.materialized_fullview
 367   GROUP BY public.materialized_fullview.trait_id, public.materialized_fullview.trait_name;
 368 CREATE UNIQUE INDEX traits_idx ON public.traits(trait_id) WITH (fillfactor=100);
 369 GRANT SELECT ON traits to web_usr;
 370 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('traits', FALSE, CURRENT_TIMESTAMP);
 371 CREATE MATERIALIZED VIEW public.traitsXtrials AS
 372 SELECT public.materialized_fullview.trait_id,
 373     public.materialized_fullview.trial_id
 374    FROM public.materialized_fullview
 375   GROUP BY public.materialized_fullview.trait_id, public.materialized_fullview.trial_id;
 376 CREATE UNIQUE INDEX traitsXtrials_idx ON public.traitsXtrials(trait_id, trial_id) WITH (fillfactor=100);
 377 GRANT SELECT ON traitsXtrials to web_usr;
 378 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('traitsXtrials', FALSE, CURRENT_TIMESTAMP);
 379 CREATE MATERIALIZED VIEW public.traitsXyears AS
 380 SELECT public.materialized_fullview.trait_id,
 381     public.materialized_fullview.year_id
 382    FROM public.materialized_fullview
 383   GROUP BY public.materialized_fullview.trait_id, public.materialized_fullview.year_id;
 384 CREATE UNIQUE INDEX traitsXyears_idx ON public.traitsXyears(trait_id, year_id) WITH (fillfactor=100);
 385 GRANT SELECT ON traitsXyears to web_usr;
 386 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('traitsXyears', FALSE, CURRENT_TIMESTAMP);
 387
 388 CREATE MATERIALIZED VIEW public.trials AS
 389 SELECT public.materialized_fullview.trial_id,
 390     public.materialized_fullview.trial_name
 391    FROM public.materialized_fullview
 392   GROUP BY public.materialized_fullview.trial_id, public.materialized_fullview.trial_name;
 393 CREATE UNIQUE INDEX trials_idx ON public.trials(trial_id) WITH (fillfactor=100);
 394 GRANT SELECT ON trials to web_usr;
 395 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trials', FALSE, CURRENT_TIMESTAMP);
 396 CREATE MATERIALIZED VIEW public.trialsXyears AS
 397 SELECT public.materialized_fullview.trial_id,
 398     public.materialized_fullview.year_id
 399    FROM public.materialized_fullview
 400   GROUP BY public.materialized_fullview.trial_id, public.materialized_fullview.year_id;
 401 CREATE UNIQUE INDEX trialsXyears_idx ON public.trialsXyears(trial_id, year_id) WITH (fillfactor=100);
 402 GRANT SELECT ON trialsXyears to web_usr;
 403 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('trialsXyears', FALSE, CURRENT_TIMESTAMP);
 404
 405 CREATE MATERIALIZED VIEW public.years AS
 406 SELECT public.materialized_fullview.year_id,
 407     public.materialized_fullview.year_name
 408    FROM public.materialized_fullview
 409   GROUP BY public.materialized_fullview.year_id, public.materialized_fullview.year_name;
 410 CREATE UNIQUE INDEX years_idx ON public.years(year_id) WITH (fillfactor=100);
 411 GRANT SELECT ON years to web_usr;
 412 INSERT INTO matviews (mv_name, currently_refreshing, last_refresh) VALUES ('years', FALSE, CURRENT_TIMESTAMP);
 413
 414 CREATE OR REPLACE FUNCTION public.refresh_materialized_views() RETURNS VOID AS '
 415 REFRESH MATERIALIZED VIEW CONCURRENTLY public.materialized_fullview;
 416 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessions;
 417 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXbreeding_programs;
 418 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXlocations;
 419 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXgenotyping_protocols;
 420 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXplots;
 421 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXtraits;
 422 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXtrials;
 423 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXyears;
 424 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programs;
 425 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXlocations;
 426 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXgenotyping_protocols;
 427 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXplots;
 428 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXtraits;
 429 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXtrials;
 430 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXyears;
 431 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocols;
 432 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXlocations;
 433 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXplots;
 434 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXtraits;
 435 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXtrials;
 436 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXyears;
 437 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locations;
 438 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXplots;
 439 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXtraits;
 440 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXtrials;
 441 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXyears;
 442 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plots;
 443 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXtraits;
 444 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXtrials;
 445 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXyears;
 446 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traits;
 447 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traitsXtrials;
 448 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traitsXyears;
 449 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trials;
 450 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trialsXyears;
 451 REFRESH MATERIALIZED VIEW CONCURRENTLY public.years;
 452 UPDATE public.matviews SET currently_refreshing=FALSE, last_refresh=CURRENT_TIMESTAMP;'
 453     LANGUAGE SQL;
 454
 455
 456 CREATE EXTENSION dblink WITH SCHEMA public;
 457 GRANT EXECUTE ON FUNCTION public.dblink_connect_u(text) TO web_usr;
 458 GRANT EXECUTE ON FUNCTION public.dblink_connect_u(text, text) TO web_usr;
 459
 460 --
 461 SELECT * from public.stock;
 462
 463 EOSQL
 464
 465 print "You're done!\n";
 466 }
 467
 468
 469 ####
 470 1; #
 471 ####