db/00114/AddGenotypeProjectSearchTables.pm

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6  AddGenotypeProjectSearchTables.pm
   7
   8 =head1 SYNOPSIS
   9
  10 mx-run AddGenotypeProjectSearchTables [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 adds materialized views related to genotyping projects for the search wizard.
  18
  19 =head1 AUTHOR
  20
  21 David Waring<djw64@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 AddGenotypeProjectSearchTables;
  34
  35 use Moose;
  36 extends 'CXGN::Metadata::Dbpatch';
  37
  38
  39 has '+description' => ( default => <<'' );
  40 This patch adds materialized views related to genotyping projects for the search wizard
  41
  42 sub patch {
  43     my $self=shift;
  44
  45     print STDOUT "Executing the patch:\n " .   $self->name . ".\n\nDescription:\n  ".  $self->description . ".\n\nExecuted by:\n " .  $self->username . " .";
  46
  47     print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
  48
  49     print STDOUT "\nExecuting the SQL commands.\n";
  50
  51     $self->dbh->do(<<EOSQL);
  52
  53 -- ADD genotyping_projects
  54
  55 DROP MATERIALIZED VIEW IF EXISTS public.genotyping_projects CASCADE;
  56 CREATE MATERIALIZED VIEW public.genotyping_projects AS
  57  SELECT project.project_id AS genotyping_project_id,
  58     project.name AS genotyping_project_name
  59    FROM (project
  60      JOIN projectprop USING (project_id))
  61   WHERE ((projectprop.type_id = ( SELECT cvterm.cvterm_id
  62            FROM cvterm
  63           WHERE ((cvterm.name)::text = 'design'::text))) AND (projectprop.value = 'genotype_data_project'::text))
  64   GROUP BY project.project_id, project.name
  65  WITH DATA;
  66 CREATE UNIQUE INDEX genotyping_projects_idx ON public.genotyping_projects(genotyping_project_id, genotyping_project_name) WITH (fillfactor=100);
  67 ALTER MATERIALIZED VIEW public.genotyping_projects OWNER TO web_usr;
  68
  69
  70 -- ADD accessionsxgenotyping_projects
  71
  72 DROP MATERIALIZED VIEW IF EXISTS public.accessionsxgenotyping_projects CASCADE;
  73 CREATE MATERIALIZED VIEW public.accessionsxgenotyping_projects AS
  74  SELECT accessions.accession_id,
  75     nd_experiment_project.project_id AS genotyping_project_id
  76    FROM (((accessions
  77      JOIN materialized_genoview ON ((accessions.accession_id = materialized_genoview.accession_id)))
  78      JOIN nd_experiment_genotype ON ((materialized_genoview.genotype_id = nd_experiment_genotype.genotype_id)))
  79      JOIN nd_experiment_project ON ((nd_experiment_genotype.nd_experiment_id = nd_experiment_project.nd_experiment_id)))
  80   WHERE (nd_experiment_project.project_id IN ( SELECT projectprop.project_id
  81            FROM projectprop
  82           WHERE ((projectprop.type_id = ( SELECT cvterm.cvterm_id
  83                    FROM cvterm
  84                   WHERE ((cvterm.name)::text = 'design'::text))) AND (projectprop.value = 'genotype_data_project'::text))))
  85   GROUP BY accessions.accession_id, genotyping_project_id
  86  WITH DATA;
  87 CREATE UNIQUE INDEX accessionsxgenotyping_projects_idx ON public.accessionsxgenotyping_projects(accession_id, genotyping_project_id) WITH (fillfactor=100);
  88 ALTER MATERIALIZED VIEW public.accessionsxgenotyping_projects OWNER TO web_usr;
  89
  90
  91 -- ADD breeding_programsxgenotyping_projects
  92
  93 DROP MATERIALIZED VIEW IF EXISTS public.breeding_programsxgenotyping_projects CASCADE;
  94 CREATE MATERIALIZED VIEW public.breeding_programsxgenotyping_projects AS
  95  SELECT breeding_programs.breeding_program_id,
  96     project_relationship.subject_project_id AS genotyping_project_id
  97    FROM (breeding_programs
  98      JOIN project_relationship ON ((breeding_programs.breeding_program_id = project_relationship.object_project_id)))
  99   WHERE ((project_relationship.type_id = ( SELECT cvterm.cvterm_id
 100            FROM cvterm
 101           WHERE ((cvterm.name)::text = 'breeding_program_trial_relationship'::text))) AND (project_relationship.subject_project_id IN ( SELECT genotyping_projects.genotyping_project_id
 102            FROM genotyping_projects)))
 103  WITH DATA;
 104 CREATE UNIQUE INDEX breeding_programsxgenotyping_projects_idx ON public.breeding_programsxgenotyping_projects(breeding_program_id, genotyping_project_id) WITH (fillfactor=100);
 105 ALTER MATERIALIZED VIEW public.breeding_programsxgenotyping_projects OWNER TO web_usr;
 106
 107
 108 -- ADD genotyping_protocolsxgenotyping_projects
 109
 110 DROP MATERIALIZED VIEW IF EXISTS public.genotyping_protocolsxgenotyping_projects CASCADE;
 111 CREATE MATERIALIZED VIEW public.genotyping_protocolsxgenotyping_projects AS
 112  SELECT genotyping_protocols.genotyping_protocol_id,
 113     nd_experiment_project.project_id AS genotyping_project_id
 114    FROM ((genotyping_protocols
 115      JOIN nd_experiment_protocol ON ((genotyping_protocols.genotyping_protocol_id = nd_experiment_protocol.nd_protocol_id)))
 116      JOIN nd_experiment_project ON ((nd_experiment_protocol.nd_experiment_id = nd_experiment_project.nd_experiment_id)))
 117   GROUP BY genotyping_protocols.genotyping_protocol_id, nd_experiment_project.project_id
 118  WITH DATA;
 119 CREATE UNIQUE INDEX genotyping_protocolsxgenotyping_projects_idx ON public.genotyping_protocolsxgenotyping_projects(genotyping_protocol_id, genotyping_project_id) WITH (fillfactor=100);
 120 ALTER MATERIALIZED VIEW public.genotyping_protocolsxgenotyping_projects OWNER TO web_usr;
 121
 122
 123 -- ADD locationsxgenotyping_projects
 124
 125 DROP MATERIALIZED VIEW IF EXISTS public.locationsxgenotyping_projects CASCADE;
 126 CREATE MATERIALIZED VIEW public.locationsxgenotyping_projects AS
 127  SELECT projectprop.value AS location_id,
 128     projectprop.project_id AS genotyping_project_id
 129    FROM projectprop
 130   WHERE ((projectprop.type_id = ( SELECT cvterm.cvterm_id
 131            FROM cvterm
 132           WHERE ((cvterm.name)::text = 'project location'::text))) AND (projectprop.value IN ( SELECT (locations.location_id)::text AS location_id
 133            FROM locations)) AND (projectprop.project_id IN ( SELECT project.project_id
 134            FROM (project
 135              JOIN projectprop projectprop_1 USING (project_id))
 136           WHERE ((projectprop_1.type_id = ( SELECT cvterm.cvterm_id
 137                    FROM cvterm
 138                   WHERE ((cvterm.name)::text = 'design'::text))) AND (projectprop_1.value = 'genotype_data_project'::text)))))
 139  WITH DATA;
 140 CREATE UNIQUE INDEX locationsxgenotyping_projects_idx ON public.locationsxgenotyping_projects(location_id, genotyping_project_id) WITH (fillfactor=100);
 141 ALTER MATERIALIZED VIEW public.locationsxgenotyping_projects OWNER TO web_usr;
 142
 143
 144 -- ADD trialsxgenotyping_projects
 145
 146 DROP MATERIALIZED VIEW IF EXISTS public.trialsxgenotyping_projects CASCADE;
 147 CREATE MATERIALIZED VIEW public.trialsxgenotyping_projects AS
 148  SELECT trials.trial_id,
 149     nd_experiment_project.project_id AS genotyping_project_id
 150    FROM ((((trials
 151      JOIN materialized_phenoview ON ((trials.trial_id = materialized_phenoview.trial_id)))
 152      JOIN materialized_genoview ON ((materialized_phenoview.accession_id = materialized_genoview.accession_id)))
 153      JOIN nd_experiment_genotype ON ((materialized_genoview.genotype_id = nd_experiment_genotype.genotype_id)))
 154      JOIN nd_experiment_project ON ((nd_experiment_genotype.nd_experiment_id = nd_experiment_project.nd_experiment_id)))
 155   WHERE (nd_experiment_project.project_id IN ( SELECT projectprop.project_id
 156            FROM projectprop
 157           WHERE ((projectprop.type_id IN ( SELECT cvterm.cvterm_id
 158                    FROM cvterm
 159                   WHERE ((cvterm.name)::text = 'design'::text))) AND (projectprop.value = 'genotype_data_project'::text))))
 160   GROUP BY trials.trial_id, nd_experiment_project.project_id
 161  WITH DATA;
 162 CREATE UNIQUE INDEX trialsxgenotyping_projects_idx ON public.trialsxgenotyping_projects(trial_id, genotyping_project_id) WITH (fillfactor=100);
 163 ALTER MATERIALIZED VIEW public.trialsxgenotyping_projects OWNER TO web_usr;
 164
 165
 166 -- ADD genotyping_projectsxaccessions
 167
 168 DROP MATERIALIZED VIEW IF EXISTS public.genotyping_projectsxaccessions CASCADE;
 169 CREATE MATERIALIZED VIEW public.genotyping_projectsxaccessions AS
 170  SELECT nd_experiment_project.project_id AS genotyping_project_id,
 171     materialized_genoview.accession_id
 172    FROM ((nd_experiment_project
 173      JOIN nd_experiment_genotype ON ((nd_experiment_project.nd_experiment_id = nd_experiment_genotype.nd_experiment_id)))
 174      JOIN materialized_genoview ON ((nd_experiment_genotype.genotype_id = materialized_genoview.genotype_id)))
 175   WHERE (nd_experiment_project.project_id IN ( SELECT genotyping_projects.genotyping_project_id
 176            FROM genotyping_projects))
 177   GROUP BY genotyping_project_id, materialized_genoview.accession_id
 178  WITH DATA;
 179 CREATE UNIQUE INDEX genotyping_projectsxaccessions_idx ON public.genotyping_projectsxaccessions(genotyping_project_id, accession_id) WITH (fillfactor=100);
 180 ALTER MATERIALIZED VIEW public.genotyping_projectsxaccessions OWNER TO web_usr;
 181
 182
 183 -- ADD genotyping_projectsxbreeding_programs
 184
 185 DROP MATERIALIZED VIEW IF EXISTS public.genotyping_projectsxbreeding_programs CASCADE;
 186 CREATE MATERIALIZED VIEW public.genotyping_projectsxbreeding_programs AS
 187  SELECT project_relationship.subject_project_id AS genotyping_project_id,
 188     project_relationship.object_project_id AS breeding_program_id
 189    FROM project_relationship
 190   WHERE ((project_relationship.type_id = ( SELECT cvterm.cvterm_id
 191            FROM cvterm
 192           WHERE ((cvterm.name)::text = 'breeding_program_trial_relationship'::text))) AND (project_relationship.subject_project_id IN ( SELECT genotyping_projects.genotyping_project_id
 193            FROM genotyping_projects)))
 194  WITH DATA;
 195 CREATE UNIQUE INDEX genotyping_projectsxbreeding_programs_idx ON public.genotyping_projectsxbreeding_programs(genotyping_project_id, breeding_program_id) WITH (fillfactor=100);
 196 ALTER MATERIALIZED VIEW public.genotyping_projectsxbreeding_programs OWNER TO web_usr;
 197
 198
 199 -- ADD genotyping_projectsxgenotyping_protocols
 200
 201 DROP MATERIALIZED VIEW IF EXISTS public.genotyping_projectsxgenotyping_protocols CASCADE;
 202 CREATE MATERIALIZED VIEW public.genotyping_projectsxgenotyping_protocols AS
 203  SELECT genotyping_projects.genotyping_project_id,
 204     nd_experiment_protocol.nd_protocol_id AS genotyping_protocol_id
 205    FROM ((genotyping_projects
 206      JOIN nd_experiment_project ON ((genotyping_projects.genotyping_project_id = nd_experiment_project.project_id)))
 207      JOIN nd_experiment_protocol ON ((nd_experiment_project.nd_experiment_id = nd_experiment_protocol.nd_experiment_id)))
 208      GROUP BY genotyping_projects.genotyping_project_id, nd_experiment_protocol.nd_protocol_id
 209  WITH DATA;
 210 CREATE UNIQUE INDEX genotyping_projectsxgenotyping_protocols_idx ON public.genotyping_projectsxgenotyping_protocols(genotyping_project_id, genotyping_protocol_id) WITH (fillfactor=100);
 211 ALTER MATERIALIZED VIEW public.genotyping_projectsxgenotyping_protocols OWNER TO web_usr;
 212
 213
 214 -- ADD genotyping_projectsxlocations
 215
 216 DROP MATERIALIZED VIEW IF EXISTS public.genotyping_projectsxlocations CASCADE;
 217 CREATE MATERIALIZED VIEW public.genotyping_projectsxlocations AS
 218  SELECT projectprop.project_id AS genotyping_project_id,
 219     (projectprop.value)::integer AS location_id
 220    FROM projectprop
 221   WHERE ((projectprop.type_id = ( SELECT cvterm.cvterm_id
 222            FROM cvterm
 223           WHERE ((cvterm.name)::text = 'project location'::text))) AND (projectprop.project_id IN ( SELECT genotyping_projects.genotyping_project_id
 224            FROM genotyping_projects)))
 225  WITH DATA;
 226 CREATE UNIQUE INDEX genotyping_projectsxlocations_idx ON public.genotyping_projectsxlocations(genotyping_project_id, location_id) WITH (fillfactor=100);
 227 ALTER MATERIALIZED VIEW public.genotyping_projectsxlocations OWNER TO web_usr;
 228
 229
 230 -- ADD genotyping_projectsxtraits
 231
 232 DROP MATERIALIZED VIEW IF EXISTS public.genotyping_projectsxtraits CASCADE;
 233 CREATE MATERIALIZED VIEW public.genotyping_projectsxtraits AS
 234  SELECT nd_experiment_project.project_id AS genotyping_project_id,
 235     materialized_phenoview.trait_id
 236    FROM (((nd_experiment_project
 237      JOIN nd_experiment_genotype ON ((nd_experiment_genotype.nd_experiment_id = nd_experiment_project.nd_experiment_id)))
 238      JOIN materialized_genoview ON ((materialized_genoview.genotype_id = nd_experiment_genotype.genotype_id)))
 239      JOIN materialized_phenoview ON ((materialized_genoview.accession_id = materialized_phenoview.accession_id)))
 240   WHERE (nd_experiment_project.project_id IN ( SELECT projectprop.project_id
 241            FROM projectprop
 242           WHERE ((projectprop.type_id IN ( SELECT cvterm.cvterm_id
 243                    FROM cvterm
 244                   WHERE ((cvterm.name)::text = 'design'::text))) AND (projectprop.value = 'genotype_data_project'::text))))
 245   GROUP BY nd_experiment_project.project_id, materialized_phenoview.trait_id
 246  WITH DATA;
 247 CREATE UNIQUE INDEX genotyping_projectsxtraits_idx ON public.genotyping_projectsxtraits(genotyping_project_id, trait_id) WITH (fillfactor=100);
 248 ALTER MATERIALIZED VIEW public.genotyping_projectsxtraits OWNER TO web_usr;
 249
 250
 251 -- ADD genotyping_projectsxtrials
 252
 253 DROP MATERIALIZED VIEW IF EXISTS public.genotyping_projectsxtrials CASCADE;
 254 CREATE MATERIALIZED VIEW public.genotyping_projectsxtrials AS
 255  SELECT nd_experiment_project.project_id AS genotyping_project_id,
 256     materialized_phenoview.trial_id
 257    FROM (((nd_experiment_project
 258      JOIN nd_experiment_genotype ON ((nd_experiment_genotype.nd_experiment_id = nd_experiment_project.nd_experiment_id)))
 259      JOIN materialized_genoview ON ((materialized_genoview.genotype_id = nd_experiment_genotype.genotype_id)))
 260      JOIN materialized_phenoview ON ((materialized_phenoview.accession_id = materialized_genoview.accession_id)))
 261   WHERE (nd_experiment_project.project_id IN ( SELECT projectprop.project_id
 262            FROM projectprop
 263           WHERE ((projectprop.type_id IN ( SELECT cvterm.cvterm_id
 264                    FROM cvterm
 265                   WHERE ((cvterm.name)::text = 'design'::text))) AND (projectprop.value = 'genotype_data_project'::text))))
 266   GROUP BY nd_experiment_project.project_id, materialized_phenoview.trial_id
 267  WITH DATA;
 268 CREATE UNIQUE INDEX genotyping_projectsxtrials_idx ON public.genotyping_projectsxtrials(genotyping_project_id, trial_id) WITH (fillfactor=100);
 269 ALTER MATERIALIZED VIEW public.genotyping_projectsxtrials OWNER TO web_usr;
 270
 271
 272 -- ADD genotyping_projectsxyears
 273
 274 DROP MATERIALIZED VIEW IF EXISTS public.genotyping_projectsxyears CASCADE;
 275 CREATE MATERIALIZED VIEW public.genotyping_projectsxyears AS
 276  SELECT projectprop.project_id AS genotyping_project_id,
 277     projectprop.value AS year_id
 278    FROM projectprop
 279   WHERE ((projectprop.project_id IN ( SELECT project.project_id
 280            FROM (project
 281              JOIN projectprop projectprop_1 USING (project_id))
 282           WHERE ((projectprop_1.type_id = ( SELECT cvterm.cvterm_id
 283                    FROM cvterm
 284                   WHERE ((cvterm.name)::text = 'design'::text))) AND (projectprop_1.value = 'genotype_data_project'::text)))) AND (projectprop.type_id = ( SELECT cvterm.cvterm_id
 285            FROM cvterm
 286           WHERE ((cvterm.name)::text = 'project year'::text))))
 287  WITH DATA;
 288 CREATE UNIQUE INDEX genotyping_projectsxyears_idx ON public.genotyping_projectsxyears(genotyping_project_id, year_id) WITH (fillfactor=100);
 289 ALTER MATERIALIZED VIEW public.genotyping_projectsxyears OWNER TO web_usr;
 290
 291
 292 -- UPDATE refresh_materialized_views function
 293
 294 CREATE OR REPLACE FUNCTION public.refresh_materialized_views()
 295  RETURNS void AS '
 296 REFRESH MATERIALIZED VIEW public.materialized_phenoview;
 297 REFRESH MATERIALIZED VIEW public.materialized_genoview;
 298 REFRESH MATERIALIZED VIEW public.accessions;
 299 REFRESH MATERIALIZED VIEW public.breeding_programs;
 300 REFRESH MATERIALIZED VIEW public.genotyping_projects;
 301 REFRESH MATERIALIZED VIEW public.genotyping_protocols;
 302 REFRESH MATERIALIZED VIEW public.locations;
 303 REFRESH MATERIALIZED VIEW public.plants;
 304 REFRESH MATERIALIZED VIEW public.plots;
 305 REFRESH MATERIALIZED VIEW public.seedlots;
 306 REFRESH MATERIALIZED VIEW public.trait_components;
 307 REFRESH MATERIALIZED VIEW public.traits;
 308 REFRESH MATERIALIZED VIEW public.trial_designs;
 309 REFRESH MATERIALIZED VIEW public.trial_types;
 310 REFRESH MATERIALIZED VIEW public.trials;
 311 REFRESH MATERIALIZED VIEW public.years;
 312 REFRESH MATERIALIZED VIEW public.accessionsXbreeding_programs;
 313 REFRESH MATERIALIZED VIEW public.accessionsXlocations;
 314 REFRESH MATERIALIZED VIEW public.accessionsxgenotyping_projects;
 315 REFRESH MATERIALIZED VIEW public.accessionsXgenotyping_protocols;
 316 REFRESH MATERIALIZED VIEW public.accessionsXplants;
 317 REFRESH MATERIALIZED VIEW public.accessionsXplots;
 318 REFRESH MATERIALIZED VIEW public.accessionsXseedlots;
 319 REFRESH MATERIALIZED VIEW public.accessionsXtrait_components;
 320 REFRESH MATERIALIZED VIEW public.accessionsXtraits;
 321 REFRESH MATERIALIZED VIEW public.accessionsXtrial_designs;
 322 REFRESH MATERIALIZED VIEW public.accessionsXtrial_types;
 323 REFRESH MATERIALIZED VIEW public.accessionsXtrials;
 324 REFRESH MATERIALIZED VIEW public.accessionsXyears;
 325 REFRESH MATERIALIZED VIEW public.breeding_programsXlocations;
 326 REFRESH MATERIALIZED VIEW public.breeding_programsxgenotyping_projects;
 327 REFRESH MATERIALIZED VIEW public.breeding_programsXgenotyping_protocols;
 328 REFRESH MATERIALIZED VIEW public.breeding_programsXplants;
 329 REFRESH MATERIALIZED VIEW public.breeding_programsXplots;
 330 REFRESH MATERIALIZED VIEW public.breeding_programsXseedlots;
 331 REFRESH MATERIALIZED VIEW public.breeding_programsXtrait_components;
 332 REFRESH MATERIALIZED VIEW public.breeding_programsXtraits;
 333 REFRESH MATERIALIZED VIEW public.breeding_programsXtrial_designs;
 334 REFRESH MATERIALIZED VIEW public.breeding_programsXtrial_types;
 335 REFRESH MATERIALIZED VIEW public.breeding_programsXtrials;
 336 REFRESH MATERIALIZED VIEW public.breeding_programsXyears;
 337 REFRESH MATERIALIZED VIEW public.genotyping_projectsxaccessions;
 338 REFRESH MATERIALIZED VIEW public.genotyping_projectsxbreeding_programs;
 339 REFRESH MATERIALIZED VIEW public.genotyping_projectsxgenotyping_protocols;
 340 REFRESH MATERIALIZED VIEW public.genotyping_projectsxtraits;
 341 REFRESH MATERIALIZED VIEW public.genotyping_projectsxtrials;
 342 REFRESH MATERIALIZED VIEW public.genotyping_projectsxyears;
 343 REFRESH MATERIALIZED VIEW public.genotyping_protocolsxgenotyping_projects;
 344 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXlocations;
 345 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXplants;
 346 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXplots;
 347 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXseedlots;
 348 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXtrait_components;
 349 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXtraits;
 350 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXtrial_designs;
 351 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXtrial_types;
 352 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXtrials;
 353 REFRESH MATERIALIZED VIEW public.genotyping_protocolsXyears;
 354 REFRESH MATERIALIZED VIEW public.locationsxgenotyping_projects;
 355 REFRESH MATERIALIZED VIEW public.locationsXplants;
 356 REFRESH MATERIALIZED VIEW public.locationsXplots;
 357 REFRESH MATERIALIZED VIEW public.locationsXseedlots;
 358 REFRESH MATERIALIZED VIEW public.locationsXtrait_components;
 359 REFRESH MATERIALIZED VIEW public.locationsXtraits;
 360 REFRESH MATERIALIZED VIEW public.locationsXtrial_designs;
 361 REFRESH MATERIALIZED VIEW public.locationsXtrial_types;
 362 REFRESH MATERIALIZED VIEW public.locationsXtrials;
 363 REFRESH MATERIALIZED VIEW public.locationsXyears;
 364 REFRESH MATERIALIZED VIEW public.plantsXplots;
 365 REFRESH MATERIALIZED VIEW public.plantsXseedlots;
 366 REFRESH MATERIALIZED VIEW public.plantsXtrait_components;
 367 REFRESH MATERIALIZED VIEW public.plantsXtraits;
 368 REFRESH MATERIALIZED VIEW public.plantsXtrial_designs;
 369 REFRESH MATERIALIZED VIEW public.plantsXtrial_types;
 370 REFRESH MATERIALIZED VIEW public.plantsXtrials;
 371 REFRESH MATERIALIZED VIEW public.plantsXyears;
 372 REFRESH MATERIALIZED VIEW public.plotsXseedlots;
 373 REFRESH MATERIALIZED VIEW public.plotsXtrait_components;
 374 REFRESH MATERIALIZED VIEW public.plotsXtraits;
 375 REFRESH MATERIALIZED VIEW public.plotsXtrial_designs;
 376 REFRESH MATERIALIZED VIEW public.plotsXtrial_types;
 377 REFRESH MATERIALIZED VIEW public.plotsXtrials;
 378 REFRESH MATERIALIZED VIEW public.plotsXyears;
 379 REFRESH MATERIALIZED VIEW public.seedlotsXtrait_components;
 380 REFRESH MATERIALIZED VIEW public.seedlotsXtraits;
 381 REFRESH MATERIALIZED VIEW public.seedlotsXtrial_designs;
 382 REFRESH MATERIALIZED VIEW public.seedlotsXtrial_types;
 383 REFRESH MATERIALIZED VIEW public.seedlotsXtrials;
 384 REFRESH MATERIALIZED VIEW public.seedlotsXyears;
 385 REFRESH MATERIALIZED VIEW public.trait_componentsXtraits;
 386 REFRESH MATERIALIZED VIEW public.trait_componentsXtrial_designs;
 387 REFRESH MATERIALIZED VIEW public.trait_componentsXtrial_types;
 388 REFRESH MATERIALIZED VIEW public.trait_componentsXtrials;
 389 REFRESH MATERIALIZED VIEW public.trait_componentsXyears;
 390 REFRESH MATERIALIZED VIEW public.traitsXtrial_designs;
 391 REFRESH MATERIALIZED VIEW public.traitsXtrial_types;
 392 REFRESH MATERIALIZED VIEW public.traitsXtrials;
 393 REFRESH MATERIALIZED VIEW public.traitsXyears;
 394 REFRESH MATERIALIZED VIEW public.trial_designsXtrial_types;
 395 REFRESH MATERIALIZED VIEW public.trial_designsXtrials;
 396 REFRESH MATERIALIZED VIEW public.trial_designsXyears;
 397 REFRESH MATERIALIZED VIEW public.trial_typesXtrials;
 398 REFRESH MATERIALIZED VIEW public.trial_typesXyears;
 399 REFRESH MATERIALIZED VIEW public.trialsxgenotyping_projects;
 400 REFRESH MATERIALIZED VIEW public.trialsXyears;
 401 UPDATE public.matviews SET currently_refreshing=FALSE, last_refresh=CURRENT_TIMESTAMP;'
 402     LANGUAGE SQL;
 403
 404
 405 -- UPDATE refresh_materialized_views_concurrently function
 406
 407 CREATE OR REPLACE FUNCTION public.refresh_materialized_views_concurrently()
 408  RETURNS void AS '
 409 REFRESH MATERIALIZED VIEW CONCURRENTLY public.materialized_phenoview;
 410 REFRESH MATERIALIZED VIEW CONCURRENTLY public.materialized_genoview;
 411 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessions;
 412 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programs;
 413 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_projects;
 414 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocols;
 415 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locations;
 416 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plants;
 417 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plots;
 418 REFRESH MATERIALIZED VIEW CONCURRENTLY public.seedlots;
 419 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trait_components;
 420 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traits;
 421 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_designs;
 422 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_types;
 423 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trials;
 424 REFRESH MATERIALIZED VIEW CONCURRENTLY public.years;
 425 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXbreeding_programs;
 426 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXlocations;
 427 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsxgenotyping_projects;
 428 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXgenotyping_protocols;
 429 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXplants;
 430 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXplots;
 431 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXseedlots;
 432 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXtrait_components;
 433 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXtraits;
 434 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXtrial_designs;
 435 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXtrial_types;
 436 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXtrials;
 437 REFRESH MATERIALIZED VIEW CONCURRENTLY public.accessionsXyears;
 438 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXlocations;
 439 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsxgenotyping_projects;
 440 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXgenotyping_protocols;
 441 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXplants;
 442 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXplots;
 443 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXseedlots;
 444 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXtrait_components;
 445 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXtraits;
 446 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXtrial_designs;
 447 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXtrial_types;
 448 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXtrials;
 449 REFRESH MATERIALIZED VIEW CONCURRENTLY public.breeding_programsXyears;
 450 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_projectsxaccessions;
 451 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_projectsxbreeding_programs;
 452 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_projectsxgenotyping_protocols;
 453 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_projectsxtraits;
 454 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_projectsxtrials;
 455 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_projectsxyears;
 456 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsxgenotyping_projects;
 457 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXlocations;
 458 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXplants;
 459 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXplots;
 460 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXseedlots;
 461 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXtrait_components;
 462 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXtraits;
 463 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXtrial_designs;
 464 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXtrial_types;
 465 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXtrials;
 466 REFRESH MATERIALIZED VIEW CONCURRENTLY public.genotyping_protocolsXyears;
 467 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsxgenotyping_projects;
 468 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXplants;
 469 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXplots;
 470 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXseedlots;
 471 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXtrait_components;
 472 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXtraits;
 473 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXtrial_designs;
 474 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXtrial_types;
 475 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXtrials;
 476 REFRESH MATERIALIZED VIEW CONCURRENTLY public.locationsXyears;
 477 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXplots;
 478 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXseedlots;
 479 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXtrait_components;
 480 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXtraits;
 481 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXtrial_designs;
 482 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXtrial_types;
 483 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXtrials;
 484 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plantsXyears;
 485 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXseedlots;
 486 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXtrait_components;
 487 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXtraits;
 488 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXtrial_designs;
 489 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXtrial_types;
 490 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXtrials;
 491 REFRESH MATERIALIZED VIEW CONCURRENTLY public.plotsXyears;
 492 REFRESH MATERIALIZED VIEW CONCURRENTLY public.seedlotsXtrait_components;
 493 REFRESH MATERIALIZED VIEW CONCURRENTLY public.seedlotsXtraits;
 494 REFRESH MATERIALIZED VIEW CONCURRENTLY public.seedlotsXtrial_designs;
 495 REFRESH MATERIALIZED VIEW CONCURRENTLY public.seedlotsXtrial_types;
 496 REFRESH MATERIALIZED VIEW CONCURRENTLY public.seedlotsXtrials;
 497 REFRESH MATERIALIZED VIEW CONCURRENTLY public.seedlotsXyears;
 498 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trait_componentsXtraits;
 499 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trait_componentsXtrial_designs;
 500 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trait_componentsXtrial_types;
 501 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trait_componentsXtrials;
 502 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trait_componentsXyears;
 503 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traitsXtrial_designs;
 504 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traitsXtrial_types;
 505 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traitsXtrials;
 506 REFRESH MATERIALIZED VIEW CONCURRENTLY public.traitsXyears;
 507 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_designsXtrial_types;
 508 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_designsXtrials;
 509 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_designsXyears;
 510 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_typesXtrials;
 511 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trial_typesXyears;
 512 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trialsxgenotyping_projects;
 513 REFRESH MATERIALIZED VIEW CONCURRENTLY public.trialsXyears;
 514 UPDATE public.matviews SET currently_refreshing=FALSE, last_refresh=CURRENT_TIMESTAMP;'
 515     LANGUAGE SQL;
 516
 517
 518
 519 EOSQL
 520
 521 print "You're done!\n";
 522 }
 523
 524
 525 ####
 526 1; #
 527 ####