db/00149/AddMissingSeedlotViews.pm

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6 AddMissingSeedlotViews.pm
   7
   8 =head1 SYNOPSIS
   9
  10 mx-run AddMissingSeedlotViews [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:
  18  - adds the seedlots and locationsXseedlots views
  19
  20 =head1 AUTHOR
  21
  22 Bryan Ellerbrock - original matviews and views
  23 David Waring <djw64@cornell.edu> - missing seedlot views
  24
  25 =head1 COPYRIGHT & LICENSE
  26
  27 Copyright 2010 Boyce Thompson Institute for Plant Research
  28
  29 This program is free software; you can redistribute it and/or modify
  30 it under the same terms as Perl itself.
  31
  32 =cut
  33
  34
  35 package AddMissingSeedlotViews;
  36
  37 use Moose;
  38 extends 'CXGN::Metadata::Dbpatch';
  39
  40
  41 has '+description' => ( default => <<'' );
  42 This patch adds the missing seedlots and locationsXseedlots views
  43
  44
  45 sub patch {
  46     my $self=shift;
  47
  48     print STDOUT "Executing the patch:\n " .   $self->name . ".\n\nDescription:\n  ".  $self->description . ".\n\nExecuted by:\n " .  $self->username . " .";
  49
  50     print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
  51
  52     print STDOUT "\nExecuting the SQL commands.\n";
  53
  54     $self->dbh->do(<<EOSQL);
  55 --do your SQL here
  56
  57
  58
  59 -- drop and recreate phenoview with single unique index and no joining through nd_experiment
  60
  61 /* ======= Included in the SpeedUpMatviews DB Patch =========
  62
  63 DROP MATERIALIZED VIEW IF EXISTS public.materialized_phenoview CASCADE;
  64 CREATE MATERIALIZED VIEW public.materialized_phenoview AS
  65 SELECT
  66   breeding_program.project_id AS breeding_program_id,
  67   location.value::int AS location_id,
  68   year.value AS year_id,
  69   trial.project_id AS trial_id,
  70   accession.stock_id AS accession_id,
  71   seedlot.stock_id AS seedlot_id,
  72   stock.stock_id AS stock_id,
  73   phenotype.phenotype_id as phenotype_id,
  74   phenotype.cvalue_id as trait_id
  75   FROM stock accession
  76      LEFT JOIN stock_relationship ON accession.stock_id = stock_relationship.object_id AND stock_relationship.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'plot_of' OR cvterm.name = 'plant_of' OR cvterm.name = 'analysis_of')
  77      LEFT JOIN stock ON stock_relationship.subject_id = stock.stock_id AND stock.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'plot' OR cvterm.name = 'plant' OR cvterm.name = 'analysis_instance')
  78      LEFT JOIN stock_relationship seedlot_relationship ON stock.stock_id = seedlot_relationship.subject_id AND seedlot_relationship.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'seed transaction')
  79      LEFT JOIN stock seedlot ON seedlot_relationship.object_id = seedlot.stock_id AND seedlot.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'seedlot')
  80      LEFT JOIN nd_experiment_stock ON(stock.stock_id = nd_experiment_stock.stock_id AND nd_experiment_stock.type_id IN (SELECT cvterm_id from cvterm where cvterm.name IN ('phenotyping_experiment', 'field_layout', 'analysis_experiment')))
  81      LEFT JOIN nd_experiment_project ON nd_experiment_stock.nd_experiment_id = nd_experiment_project.nd_experiment_id
  82      FULL OUTER JOIN project trial ON nd_experiment_project.project_id = trial.project_id
  83      LEFT JOIN project_relationship ON trial.project_id = project_relationship.subject_project_id AND project_relationship.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'breeding_program_trial_relationship' )
  84      FULL OUTER JOIN project breeding_program ON project_relationship.object_project_id = breeding_program.project_id
  85      LEFT JOIN projectprop location ON trial.project_id = location.project_id AND location.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'project location' )
  86      LEFT JOIN projectprop year ON trial.project_id = year.project_id AND year.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'project year' )
  87      LEFT JOIN nd_experiment_phenotype ON(nd_experiment_stock.nd_experiment_id = nd_experiment_phenotype.nd_experiment_id)
  88      LEFT JOIN phenotype ON nd_experiment_phenotype.phenotype_id = phenotype.phenotype_id
  89   WHERE accession.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'accession')
  90   ORDER BY breeding_program_id, location_id, trial_id, accession_id, seedlot_id, stock.stock_id, phenotype_id, trait_id
  91 WITH DATA;
  92 CREATE UNIQUE INDEX unq_pheno_idx ON public.materialized_phenoview(stock_id,phenotype_id,trait_id) WITH (fillfactor=100);
  93 ALTER MATERIALIZED VIEW materialized_phenoview OWNER TO web_usr;
  94
  95 */
  96
  97
  98 -- drop and recreate genoview with new column for genotype project id
  99
 100 /* ======= Included in the AddGenotypeProjectViews DB Patch =========
 101
 102 DROP MATERIALIZED VIEW IF EXISTS public.materialized_genoview CASCADE;
 103 CREATE MATERIALIZED VIEW public.materialized_genoview AS
 104 SELECT stock.stock_id AS accession_id,
 105      nd_experiment_protocol.nd_protocol_id AS genotyping_protocol_id,
 106      nd_experiment_project.project_id AS genotyping_project_id,
 107      genotype.genotype_id AS genotype_id,
 108      stock_type.name AS stock_type
 109     FROM stock
 110       JOIN cvterm AS stock_type ON (stock_type.cvterm_id = stock.type_id AND stock_type.name = 'accession')
 111       JOIN nd_experiment_stock ON stock.stock_id = nd_experiment_stock.stock_id
 112       JOIN nd_experiment_protocol ON nd_experiment_stock.nd_experiment_id = nd_experiment_protocol.nd_experiment_id
 113       LEFT JOIN nd_experiment_project ON nd_experiment_stock.nd_experiment_id = nd_experiment_project.nd_experiment_id
 114       JOIN nd_protocol ON nd_experiment_protocol.nd_protocol_id = nd_protocol.nd_protocol_id
 115       JOIN nd_experiment_genotype ON nd_experiment_stock.nd_experiment_id = nd_experiment_genotype.nd_experiment_id
 116       JOIN genotype ON genotype.genotype_id = nd_experiment_genotype.genotype_id
 117    GROUP BY 1,2,3,4,5
 118 UNION
 119 SELECT accession.stock_id AS accession_id,
 120     nd_experiment_protocol.nd_protocol_id AS genotyping_protocol_id,
 121     nd_experiment_project.project_id AS genotyping_project_id,
 122     nd_experiment_genotype.genotype_id AS genotype_id,
 123     stock_type.name AS stock_type
 124     FROM stock AS accession
 125       JOIN stock_relationship ON accession.stock_id = stock_relationship.object_id AND stock_relationship.type_id IN (SELECT cvterm_id from cvterm where cvterm.name IN ('tissue_sample_of', 'plant_of', 'plot_of') )
 126       JOIN stock ON stock_relationship.subject_id = stock.stock_id AND stock.type_id IN (SELECT cvterm_id from cvterm where cvterm.name IN ('tissue_sample', 'plant', 'plot') )
 127       JOIN cvterm AS stock_type ON (stock_type.cvterm_id = stock.type_id)
 128      JOIN nd_experiment_stock ON stock.stock_id = nd_experiment_stock.stock_id
 129      JOIN nd_experiment_protocol ON nd_experiment_stock.nd_experiment_id = nd_experiment_protocol.nd_experiment_id
 130      LEFT JOIN nd_experiment_project ON nd_experiment_stock.nd_experiment_id = nd_experiment_project.nd_experiment_id
 131      JOIN nd_protocol ON nd_experiment_protocol.nd_protocol_id = nd_protocol.nd_protocol_id
 132      JOIN nd_experiment_genotype ON nd_experiment_stock.nd_experiment_id = nd_experiment_genotype.nd_experiment_id
 133   GROUP BY 1,2,3,4,5 ORDER BY 1,2,3,4
 134 WITH DATA;
 135 CREATE UNIQUE INDEX unq_geno_idx ON public.materialized_genoview(accession_id,genotype_id) WITH (fillfactor=100);
 136 ALTER MATERIALIZED VIEW materialized_genoview OWNER TO web_usr;
 137
 138 */
 139
 140 -- EXISTING VIEWS --
 141
 142 -- drop and recreate all the single category matviews as just views
 143
 144 /* ======= Included in the SpeedUpMatviews DB Patch =========
 145
 146 DROP VIEW IF EXISTS public.accessions CASCADE;
 147 CREATE VIEW public.accessions AS
 148   SELECT stock.stock_id AS accession_id,
 149   stock.uniquename AS accession_name
 150   FROM stock
 151   WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'accession') AND is_obsolete = 'f'
 152   GROUP BY stock.stock_id, stock.uniquename;
 153 ALTER VIEW accessions OWNER TO web_usr;
 154
 155 DROP VIEW IF EXISTS public.breeding_programs CASCADE;
 156 CREATE VIEW public.breeding_programs AS
 157 SELECT project.project_id AS breeding_program_id,
 158     project.name AS breeding_program_name
 159    FROM project join projectprop USING (project_id)
 160    WHERE projectprop.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'breeding_program')
 161   GROUP BY project.project_id, project.name;
 162 ALTER VIEW breeding_programs OWNER TO web_usr;
 163
 164 DROP VIEW IF EXISTS public.genotyping_protocols CASCADE;
 165 CREATE VIEW public.genotyping_protocols AS
 166 SELECT nd_protocol.nd_protocol_id AS genotyping_protocol_id,
 167     nd_protocol.name AS genotyping_protocol_name
 168    FROM nd_protocol
 169    WHERE nd_protocol.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'genotyping_experiment')
 170   GROUP BY public.nd_protocol.nd_protocol_id, public.nd_protocol.name;
 171 ALTER VIEW genotyping_protocols OWNER TO web_usr;
 172
 173 DROP VIEW IF EXISTS public.locations CASCADE;
 174 CREATE VIEW public.locations AS
 175 SELECT nd_geolocation.nd_geolocation_id AS location_id,
 176   nd_geolocation.description AS location_name
 177    FROM nd_geolocation
 178   GROUP BY public.nd_geolocation.nd_geolocation_id, public.nd_geolocation.description;
 179 ALTER VIEW locations OWNER TO web_usr;
 180
 181 DROP VIEW IF EXISTS public.plants CASCADE;
 182 CREATE VIEW public.plants AS
 183 SELECT stock.stock_id AS plant_id,
 184     stock.uniquename AS plant_name
 185    FROM stock
 186    WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plant') AND is_obsolete = 'f'
 187   GROUP BY public.stock.stock_id, public.stock.uniquename;
 188 ALTER VIEW plants OWNER TO web_usr;
 189
 190 DROP VIEW IF EXISTS public.plots CASCADE;
 191 CREATE VIEW public.plots AS
 192 SELECT stock.stock_id AS plot_id,
 193     stock.uniquename AS plot_name
 194    FROM stock
 195    WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plot') AND is_obsolete = 'f'
 196   GROUP BY public.stock.stock_id, public.stock.uniquename;
 197 ALTER VIEW plots OWNER TO web_usr;
 198
 199 DROP VIEW IF EXISTS public.trait_components CASCADE;
 200 CREATE VIEW public.trait_components AS
 201 SELECT cvterm.cvterm_id AS trait_component_id,
 202 (((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text AS trait_component_name
 203     FROM cv
 204     JOIN cvprop ON(cv.cv_id = cvprop.cv_id AND cvprop.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = ANY ('{object_ontology,attribute_ontology,method_ontology,unit_ontology,time_ontology}')))
 205     JOIN cvterm ON(cvprop.cv_id = cvterm.cv_id)
 206     JOIN dbxref USING(dbxref_id)
 207     JOIN db ON(dbxref.db_id = db.db_id)
 208     LEFT JOIN cvterm_relationship is_subject ON cvterm.cvterm_id = is_subject.subject_id
 209     LEFT JOIN cvterm_relationship is_object ON cvterm.cvterm_id = is_object.object_id
 210     WHERE is_object.object_id IS NULL AND is_subject.subject_id IS NOT NULL
 211     GROUP BY 2,1 ORDER BY 2,1;
 212 ALTER VIEW trait_components OWNER TO web_usr;
 213
 214 DROP VIEW IF EXISTS public.traits CASCADE;
 215 CREATE VIEW public.traits AS
 216   SELECT cvterm.cvterm_id AS trait_id,
 217   (((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text AS trait_name
 218         FROM cv
 219     JOIN cvprop ON(cv.cv_id = cvprop.cv_id AND cvprop.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'trait_ontology'))
 220     JOIN cvterm ON(cvprop.cv_id = cvterm.cv_id)
 221           JOIN dbxref USING(dbxref_id)
 222     JOIN db ON(dbxref.db_id = db.db_id)
 223     LEFT JOIN cvterm_relationship is_variable ON cvterm.cvterm_id = is_variable.subject_id AND is_variable.type_id = (SELECT cvterm_id FROM cvterm WHERE name = 'VARIABLE_OF')
 224     WHERE is_variable.subject_id IS NOT NULL
 225     GROUP BY 1,2
 226   UNION
 227   SELECT cvterm.cvterm_id AS trait_id,
 228   (((cvterm.name::text || '|'::text) || db.name::text) || ':'::text) || dbxref.accession::text AS trait_name
 229   FROM cv
 230     JOIN cvprop ON(cv.cv_id = cvprop.cv_id AND cvprop.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'composed_trait_ontology'))
 231     JOIN cvterm ON(cvprop.cv_id = cvterm.cv_id)
 232     JOIN dbxref USING(dbxref_id)
 233     JOIN db ON(dbxref.db_id = db.db_id)
 234     LEFT JOIN cvterm_relationship is_subject ON cvterm.cvterm_id = is_subject.subject_id
 235     WHERE is_subject.subject_id IS NOT NULL
 236     GROUP BY 1,2 ORDER BY 2;
 237     ALTER VIEW traits OWNER TO web_usr;
 238
 239 DROP VIEW IF EXISTS public.trials CASCADE;
 240 CREATE VIEW public.trials AS
 241 SELECT trial.project_id AS trial_id,
 242     trial.name AS trial_name
 243    FROM project breeding_program
 244    JOIN project_relationship ON(breeding_program.project_id = object_project_id AND project_relationship.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'breeding_program_trial_relationship'))
 245    JOIN project trial ON(subject_project_id = trial.project_id)
 246    JOIN projectprop on(trial.project_id = projectprop.project_id)
 247    WHERE projectprop.type_id NOT IN (SELECT cvterm.cvterm_id FROM cvterm WHERE cvterm.name::text = 'cross'::text OR cvterm.name::text = 'trial_folder'::text OR cvterm.name::text = 'folder_for_trials'::text OR cvterm.name::text = 'folder_for_crosses'::text OR cvterm.name::text = 'folder_for_genotyping_trials'::text)
 248    GROUP BY trial.project_id, trial.name;
 249 ALTER VIEW trials OWNER TO web_usr;
 250
 251 DROP VIEW IF EXISTS public.trial_designs CASCADE;
 252 CREATE VIEW public.trial_designs AS
 253 SELECT projectprop.value AS trial_design_id,
 254   projectprop.value AS trial_design_name
 255    FROM projectprop
 256    JOIN cvterm ON(projectprop.type_id = cvterm.cvterm_id)
 257    WHERE cvterm.name = 'design'
 258    GROUP BY projectprop.value;
 259 ALTER VIEW trial_designs OWNER TO web_usr;
 260
 261 DROP VIEW IF EXISTS public.trial_types CASCADE;
 262 CREATE VIEW public.trial_types AS
 263 SELECT cvterm.cvterm_id AS trial_type_id,
 264   cvterm.name AS trial_type_name
 265    FROM cvterm
 266    JOIN cv USING(cv_id)
 267    WHERE cv.name = 'project_type'
 268    GROUP BY cvterm.cvterm_id;
 269 ALTER VIEW trial_types OWNER TO web_usr;
 270
 271 DROP VIEW IF EXISTS public.years CASCADE;
 272 CREATE VIEW public.years AS
 273 SELECT projectprop.value AS year_id,
 274   projectprop.value AS year_name
 275    FROM projectprop
 276    WHERE projectprop.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'project year')
 277   GROUP BY public.projectprop.value;
 278 ALTER VIEW years OWNER TO web_usr;
 279
 280 */
 281
 282 -- add missing seedlots view
 283
 284 DROP MATERIALIZED VIEW IF EXISTS public.seedlots CASCADE;
 285 CREATE VIEW public.seedlots AS
 286 SELECT stock.stock_id AS seedlot_id,
 287    stock.uniquename AS seedlot_name
 288    FROM stock
 289    WHERE stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'seedlot') AND is_obsolete = 'f'
 290    GROUP BY public.stock.stock_id, public.stock.uniquename;
 291 ALTER VIEW plants OWNER TO web_usr;
 292
 293
 294 -- drop and recreate all the binary matviews as just views
 295
 296 /* ======= Included in the SpeedUpMatviews DB Patch =========
 297
 298 DROP VIEW IF EXISTS public.accessionsXseedlots CASCADE;
 299 CREATE VIEW public.accessionsXseedlots AS
 300 SELECT public.materialized_phenoview.accession_id,
 301     public.stock.stock_id AS seedlot_id
 302    FROM public.materialized_phenoview
 303    LEFT JOIN stock_relationship seedlot_relationship ON materialized_phenoview.accession_id = seedlot_relationship.subject_id AND seedlot_relationship.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'collection_of')
 304    LEFT JOIN stock ON seedlot_relationship.object_id = stock.stock_id AND stock.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'seedlot')
 305   GROUP BY public.materialized_phenoview.accession_id,public.stock.stock_id;
 306 ALTER VIEW accessionsXseedlots OWNER TO web_usr;
 307
 308 DROP VIEW IF EXISTS public.breeding_programsXseedlots CASCADE;
 309 CREATE VIEW public.breeding_programsXseedlots AS
 310 SELECT public.materialized_phenoview.breeding_program_id,
 311     public.nd_experiment_stock.stock_id AS seedlot_id
 312    FROM public.materialized_phenoview
 313    LEFT JOIN nd_experiment_project ON materialized_phenoview.breeding_program_id = nd_experiment_project.project_id
 314    LEFT JOIN nd_experiment ON nd_experiment_project.nd_experiment_id = nd_experiment.nd_experiment_id AND nd_experiment.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'seedlot_experiment')
 315    LEFT JOIN nd_experiment_stock ON nd_experiment.nd_experiment_id = nd_experiment_stock.nd_experiment_id
 316   GROUP BY 1,2;
 317 ALTER VIEW breeding_programsXseedlots OWNER TO web_usr;
 318
 319 DROP VIEW IF EXISTS public.genotyping_protocolsXseedlots CASCADE;
 320 CREATE VIEW public.genotyping_protocolsXseedlots AS
 321 SELECT public.materialized_genoview.genotyping_protocol_id,
 322 public.stock.stock_id AS seedlot_id
 323 FROM public.materialized_genoview
 324 LEFT JOIN stock_relationship seedlot_relationship ON materialized_genoview.accession_id = seedlot_relationship.subject_id AND seedlot_relationship.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'collection_of')
 325 LEFT JOIN stock ON seedlot_relationship.object_id = stock.stock_id AND stock.type_id IN (SELECT cvterm_id from cvterm where cvterm.name = 'seedlot')
 326   GROUP BY 1,2;
 327 ALTER VIEW genotyping_protocolsXseedlots OWNER TO web_usr;
 328
 329 */
 330
 331 -- Add missing locationsXseedlots
 332
 333 DROP MATERIALIZED VIEW IF EXISTS public.locationsXseedlots CASCADE;
 334 CREATE VIEW public.locationsXseedlots AS
 335 SELECT nd_experiment.nd_geolocation_id AS location_id,nd_experiment_stock.stock_id AS seedlot_id
 336         FROM nd_experiment
 337         LEFT JOIN nd_experiment_stock ON nd_experiment.nd_experiment_id = nd_experiment_stock.nd_experiment_id
 338         WHERE nd_experiment.type_id IN (SELECT cvterm.cvterm_id FROM cvterm WHERE cvterm.name = 'seedlot_experiment')
 339         GROUP BY 1,2;
 340 ALTER VIEW public.locationsXseedlots OWNER TO web_usr;
 341
 342 /* ======= Included in the SpeedUpMatviews DB Patch =========
 343
 344 DROP VIEW IF EXISTS public.plantsXseedlots CASCADE;
 345 CREATE VIEW public.plantsXseedlots AS
 346 SELECT public.stock.stock_id AS plant_id,
 347     public.materialized_phenoview.seedlot_id
 348    FROM public.materialized_phenoview
 349    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plant'))
 350   GROUP BY 1,2;
 351 ALTER VIEW plantsXseedlots OWNER TO web_usr;
 352
 353 DROP VIEW IF EXISTS public.plotsXseedlots CASCADE;
 354 CREATE VIEW public.plotsXseedlots AS
 355 SELECT public.stock.stock_id AS plot_id,
 356     public.materialized_phenoview.seedlot_id
 357    FROM public.materialized_phenoview
 358    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plot'))
 359   GROUP BY 1,2;
 360 ALTER VIEW plotsXseedlots OWNER TO web_usr;
 361
 362 DROP VIEW IF EXISTS public.seedlotsXtrait_components CASCADE;
 363 CREATE VIEW public.seedlotsXtrait_components AS
 364 SELECT public.materialized_phenoview.seedlot_id,
 365 trait_component.cvterm_id AS trait_component_id
 366 FROM public.materialized_phenoview
 367 JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 368 JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 369 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 370 GROUP BY 1,2;
 371 ALTER VIEW seedlotsXtrait_components OWNER TO web_usr;
 372
 373 DROP VIEW IF EXISTS public.seedlotsXtraits CASCADE;
 374 CREATE VIEW public.seedlotsXtraits AS
 375 SELECT public.materialized_phenoview.seedlot_id,
 376     public.materialized_phenoview.trait_id
 377    FROM public.materialized_phenoview
 378   GROUP BY 1,2;
 379 ALTER VIEW seedlotsXtraits OWNER TO web_usr;
 380
 381 DROP VIEW IF EXISTS public.seedlotsXtrials CASCADE;
 382 CREATE VIEW public.seedlotsXtrials AS
 383 SELECT public.materialized_phenoview.seedlot_id,
 384     public.materialized_phenoview.trial_id
 385    FROM public.materialized_phenoview
 386   GROUP BY 1,2;
 387 ALTER VIEW seedlotsXtrials OWNER TO web_usr;
 388
 389 DROP VIEW IF EXISTS public.seedlotsXtrial_designs CASCADE;
 390 CREATE VIEW public.seedlotsXtrial_designs AS
 391 SELECT public.materialized_phenoview.seedlot_id,
 392     trialdesign.value AS trial_design_id
 393    FROM public.materialized_phenoview
 394    JOIN public.projectprop trialdesign ON materialized_phenoview.trial_id = trialdesign.project_id AND trialdesign.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'design' )
 395   GROUP BY 1,2;
 396 ALTER VIEW seedlotsXtrial_designs OWNER TO web_usr;
 397
 398 DROP VIEW IF EXISTS public.seedlotsXtrial_types CASCADE;
 399 CREATE VIEW public.seedlotsXtrial_types AS
 400 SELECT public.materialized_phenoview.seedlot_id,
 401     trialterm.cvterm_id AS trial_type_id
 402    FROM public.materialized_phenoview
 403    JOIN projectprop trialprop ON materialized_phenoview.trial_id = trialprop.project_id AND trialprop.type_id IN (SELECT cvterm_id from cvterm JOIN cv USING(cv_id) WHERE cv.name = 'project_type' )
 404    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 405   GROUP BY 1,2;
 406 ALTER VIEW seedlotsXtrial_types OWNER TO web_usr;
 407
 408 DROP VIEW IF EXISTS public.seedlotsXyears CASCADE;
 409 CREATE VIEW public.seedlotsXyears AS
 410 SELECT public.materialized_phenoview.seedlot_id,
 411     public.materialized_phenoview.year_id
 412    FROM public.materialized_phenoview
 413   GROUP BY 1,2;
 414 ALTER VIEW seedlotsXyears OWNER TO web_usr;
 415
 416 DROP VIEW IF EXISTS public.accessionsXtraits CASCADE;
 417 CREATE VIEW public.accessionsXtraits AS
 418 SELECT public.materialized_phenoview.accession_id,
 419     public.materialized_phenoview.trait_id
 420    FROM public.materialized_phenoview
 421   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.trait_id;
 422 ALTER VIEW accessionsXtraits OWNER TO web_usr;
 423
 424 DROP VIEW IF EXISTS public.breeding_programsXtraits CASCADE;
 425 CREATE VIEW public.breeding_programsXtraits AS
 426 SELECT public.materialized_phenoview.breeding_program_id,
 427     public.materialized_phenoview.trait_id
 428    FROM public.materialized_phenoview
 429   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_phenoview.trait_id;
 430 ALTER VIEW breeding_programsXtraits OWNER TO web_usr;
 431
 432 DROP VIEW IF EXISTS public.genotyping_protocolsXtraits CASCADE;
 433 CREATE VIEW public.genotyping_protocolsXtraits AS
 434 SELECT public.materialized_genoview.genotyping_protocol_id,
 435     public.materialized_phenoview.trait_id
 436    FROM public.materialized_genoview
 437    JOIN public.materialized_phenoview USING(accession_id)
 438   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.trait_id;
 439   ALTER VIEW genotyping_protocolsXtraits OWNER TO web_usr;
 440
 441 DROP VIEW IF EXISTS public.locationsXtraits CASCADE;
 442 CREATE VIEW public.locationsXtraits AS
 443 SELECT public.materialized_phenoview.location_id,
 444     public.materialized_phenoview.trait_id
 445    FROM public.materialized_phenoview
 446   GROUP BY public.materialized_phenoview.location_id, public.materialized_phenoview.trait_id;
 447 ALTER VIEW locationsXtraits OWNER TO web_usr;
 448
 449 DROP VIEW IF EXISTS public.plantsXtraits CASCADE;
 450 CREATE VIEW public.plantsXtraits AS
 451 SELECT public.stock.stock_id AS plant_id,
 452     public.materialized_phenoview.trait_id
 453    FROM public.materialized_phenoview
 454    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plant'))
 455   GROUP BY public.stock.stock_id, public.materialized_phenoview.trait_id;
 456 ALTER VIEW plantsXtraits OWNER TO web_usr;
 457
 458 DROP VIEW IF EXISTS public.plotsXtraits CASCADE;
 459 CREATE VIEW public.plotsXtraits AS
 460 SELECT public.stock.stock_id AS plot_id,
 461     public.materialized_phenoview.trait_id
 462    FROM public.materialized_phenoview
 463    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plot'))
 464   GROUP BY public.stock.stock_id, public.materialized_phenoview.trait_id;
 465 ALTER VIEW plotsXtraits OWNER TO web_usr;
 466
 467 DROP VIEW IF EXISTS public.traitsXtrials CASCADE;
 468 CREATE VIEW public.traitsXtrials AS
 469 SELECT public.materialized_phenoview.trait_id,
 470     public.materialized_phenoview.trial_id
 471    FROM public.materialized_phenoview
 472   GROUP BY public.materialized_phenoview.trait_id, public.materialized_phenoview.trial_id;
 473 ALTER VIEW traitsXtrials OWNER TO web_usr;
 474
 475 DROP VIEW IF EXISTS public.traitsXtrial_designs CASCADE;
 476 CREATE VIEW public.traitsXtrial_designs AS
 477 SELECT public.materialized_phenoview.trait_id,
 478     trialdesign.value AS trial_design_id
 479    FROM public.materialized_phenoview
 480    JOIN public.projectprop trialdesign ON materialized_phenoview.trial_id = trialdesign.project_id AND trialdesign.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'design' )
 481   GROUP BY public.materialized_phenoview.trait_id, trialdesign.value;
 482 ALTER VIEW traitsXtrial_designs OWNER TO web_usr;
 483
 484 DROP VIEW IF EXISTS public.traitsXtrial_types CASCADE;
 485 CREATE VIEW public.traitsXtrial_types AS
 486 SELECT public.materialized_phenoview.trait_id,
 487     trialterm.cvterm_id AS trial_type_id
 488    FROM public.materialized_phenoview
 489    JOIN projectprop trialprop ON materialized_phenoview.trial_id = trialprop.project_id AND trialprop.type_id IN (SELECT cvterm_id from cvterm JOIN cv USING(cv_id) WHERE cv.name = 'project_type' )
 490    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 491   GROUP BY public.materialized_phenoview.trait_id, trialterm.cvterm_id;
 492 ALTER VIEW traitsXtrial_types OWNER TO web_usr;
 493
 494 DROP VIEW IF EXISTS public.traitsXyears CASCADE;
 495 CREATE VIEW public.traitsXyears AS
 496 SELECT public.materialized_phenoview.trait_id,
 497     public.materialized_phenoview.year_id
 498    FROM public.materialized_phenoview
 499   GROUP BY public.materialized_phenoview.trait_id, public.materialized_phenoview.year_id;
 500 ALTER VIEW traitsXyears OWNER TO web_usr;
 501
 502 DROP VIEW IF EXISTS public.accessionsXtrait_components CASCADE;
 503 CREATE VIEW public.accessionsXtrait_components AS
 504 SELECT public.materialized_phenoview.accession_id,
 505     trait_component.cvterm_id AS trait_component_id
 506    FROM public.materialized_phenoview
 507    JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 508    JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 509    JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 510   GROUP BY 1,2;
 511 ALTER VIEW accessionsXtrait_components OWNER TO web_usr;
 512
 513 DROP VIEW IF EXISTS public.breeding_programsXtrait_components CASCADE;
 514 CREATE VIEW public.breeding_programsXtrait_components AS
 515 SELECT public.materialized_phenoview.breeding_program_id,
 516 trait_component.cvterm_id AS trait_component_id
 517 FROM public.materialized_phenoview
 518 JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 519 JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 520 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 521 GROUP BY 1,2;
 522 ALTER VIEW breeding_programsXtrait_components OWNER TO web_usr;
 523
 524 DROP VIEW IF EXISTS public.genotyping_protocolsXtrait_components CASCADE;
 525 CREATE VIEW public.genotyping_protocolsXtrait_components AS
 526 SELECT public.materialized_genoview.genotyping_protocol_id,
 527 trait_component.cvterm_id AS trait_component_id
 528 FROM public.materialized_genoview
 529 JOIN public.materialized_phenoview USING(accession_id)
 530 JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 531 JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 532 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 533 GROUP BY 1,2;
 534 ALTER VIEW genotyping_protocolsXtrait_components OWNER TO web_usr;
 535
 536 DROP VIEW IF EXISTS public.locationsXtrait_components CASCADE;
 537 CREATE VIEW public.locationsXtrait_components AS
 538 SELECT public.materialized_phenoview.location_id,
 539 trait_component.cvterm_id AS trait_component_id
 540 FROM public.materialized_phenoview
 541 JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 542 JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 543 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 544 GROUP BY 1,2;
 545 ALTER VIEW locationsXtrait_components OWNER TO web_usr;
 546
 547 DROP VIEW IF EXISTS public.plantsXtrait_components CASCADE;
 548 CREATE VIEW public.plantsXtrait_components AS
 549 SELECT public.stock.stock_id AS plant_id,
 550 trait_component.cvterm_id AS trait_component_id
 551 FROM public.materialized_phenoview
 552 JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plant'))
 553 JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 554 JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 555 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 556 GROUP BY 1,2;
 557 ALTER VIEW plantsXtrait_components OWNER TO web_usr;
 558
 559 DROP VIEW IF EXISTS public.plotsXtrait_components CASCADE;
 560 CREATE VIEW public.plotsXtrait_components AS
 561 SELECT public.stock.stock_id AS plot_id,
 562 trait_component.cvterm_id AS trait_component_id
 563 FROM public.materialized_phenoview
 564 JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plot'))
 565 JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 566 JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 567 JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 568 GROUP BY 1,2;
 569 ALTER VIEW plotsXtrait_components OWNER TO web_usr;
 570
 571 DROP VIEW IF EXISTS public.trait_componentsXtrials CASCADE;
 572 CREATE VIEW public.trait_componentsXtrials AS
 573 SELECT trait_component.cvterm_id AS trait_component_id,
 574     public.materialized_phenoview.trial_id
 575     FROM public.materialized_phenoview
 576     JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 577     JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 578     JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 579     GROUP BY 1,2;
 580 ALTER VIEW trait_componentsXtrials OWNER TO web_usr;
 581
 582 DROP VIEW IF EXISTS public.trait_componentsXtrial_designs CASCADE;
 583 CREATE VIEW public.trait_componentsXtrial_designs AS
 584 SELECT trait_component.cvterm_id AS trait_component_id,
 585     trialdesign.value AS trial_design_id
 586    FROM public.materialized_phenoview
 587    JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 588    JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 589    JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 590    JOIN public.projectprop trialdesign ON materialized_phenoview.trial_id = trialdesign.project_id AND trialdesign.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'design' )
 591   GROUP BY 1,2;
 592 ALTER VIEW trait_componentsXtrial_designs OWNER TO web_usr;
 593
 594 DROP VIEW IF EXISTS public.trait_componentsXtrial_types CASCADE;
 595 CREATE VIEW public.trait_componentsXtrial_types AS
 596 SELECT trait_component.cvterm_id AS trait_component_id,
 597     trialterm.cvterm_id AS trial_type_id
 598    FROM public.materialized_phenoview
 599    JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 600    JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 601    JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 602    JOIN projectprop trialprop ON materialized_phenoview.trial_id = trialprop.project_id AND trialprop.type_id IN (SELECT cvterm_id from cvterm JOIN cv USING(cv_id) WHERE cv.name = 'project_type' )
 603    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 604   GROUP BY 1,2;
 605 ALTER VIEW trait_componentsXtrial_types OWNER TO web_usr;
 606
 607 DROP VIEW IF EXISTS public.trait_componentsXyears CASCADE;
 608 CREATE VIEW public.trait_componentsXyears AS
 609 SELECT trait_component.cvterm_id AS trait_component_id,
 610     public.materialized_phenoview.year_id
 611    FROM public.materialized_phenoview
 612    JOIN cvterm trait ON(materialized_phenoview.trait_id = trait.cvterm_id)
 613    JOIN cvterm_relationship ON(trait.cvterm_id = cvterm_relationship.object_id AND cvterm_relationship.type_id = (SELECT cvterm_id from cvterm where name = 'contains'))
 614    JOIN cvterm trait_component ON(cvterm_relationship.subject_id = trait_component.cvterm_id)
 615   GROUP BY 1,2;
 616 ALTER VIEW trait_componentsXyears OWNER TO web_usr;
 617
 618 -- FIX VIEWS FOR PLANTS, PLOTS, TRIAL DESIGNS AND TRIAL TYPES
 619
 620 DROP VIEW IF EXISTS public.accessionsXbreeding_programs CASCADE;
 621 CREATE VIEW public.accessionsXbreeding_programs AS
 622 SELECT public.materialized_phenoview.accession_id,
 623     public.materialized_phenoview.breeding_program_id
 624    FROM public.materialized_phenoview
 625   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.breeding_program_id;
 626   ALTER VIEW accessionsXbreeding_programs OWNER TO web_usr;
 627
 628 DROP VIEW IF EXISTS public.accessionsXgenotyping_protocols CASCADE;
 629 CREATE VIEW public.accessionsXgenotyping_protocols AS
 630 SELECT public.materialized_genoview.accession_id,
 631     public.materialized_genoview.genotyping_protocol_id
 632    FROM public.materialized_genoview
 633   GROUP BY public.materialized_genoview.accession_id, public.materialized_genoview.genotyping_protocol_id;
 634   ALTER VIEW accessionsXgenotyping_protocols OWNER TO web_usr;
 635
 636 DROP VIEW IF EXISTS public.accessionsXlocations CASCADE;
 637 CREATE VIEW public.accessionsXlocations AS
 638 SELECT public.materialized_phenoview.accession_id,
 639     public.materialized_phenoview.location_id
 640    FROM public.materialized_phenoview
 641   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.location_id;
 642   ALTER VIEW accessionsXlocations OWNER TO web_usr;
 643
 644 DROP VIEW IF EXISTS public.accessionsXplants CASCADE;
 645 CREATE VIEW public.accessionsXplants AS
 646 SELECT public.materialized_phenoview.accession_id,
 647     public.stock.stock_id AS plant_id
 648    FROM public.materialized_phenoview
 649    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plant'))
 650   GROUP BY public.materialized_phenoview.accession_id, public.stock.stock_id;
 651   ALTER VIEW accessionsXplants OWNER TO web_usr;
 652
 653 DROP VIEW IF EXISTS public.accessionsXplots CASCADE;
 654 CREATE VIEW public.accessionsXplots AS
 655 SELECT public.materialized_phenoview.accession_id,
 656     public.stock.stock_id AS plot_id
 657    FROM public.materialized_phenoview
 658    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plot'))
 659   GROUP BY public.materialized_phenoview.accession_id, public.stock.stock_id;
 660 ALTER VIEW accessionsXplots OWNER TO web_usr;
 661
 662 DROP VIEW IF EXISTS public.accessionsXtrial_designs CASCADE;
 663 CREATE VIEW public.accessionsXtrial_designs AS
 664 SELECT public.materialized_phenoview.accession_id,
 665     trialdesign.value AS trial_design_id
 666    FROM public.materialized_phenoview
 667    JOIN public.projectprop trialdesign ON materialized_phenoview.trial_id = trialdesign.project_id AND trialdesign.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'design' )
 668   GROUP BY public.materialized_phenoview.accession_id, trialdesign.value;
 669 ALTER VIEW accessionsXtrial_designs OWNER TO web_usr;
 670
 671 DROP VIEW IF EXISTS public.accessionsXtrial_types CASCADE;
 672 CREATE VIEW public.accessionsXtrial_types AS
 673 SELECT public.materialized_phenoview.accession_id,
 674     trialterm.cvterm_id AS trial_type_id
 675    FROM public.materialized_phenoview
 676    JOIN projectprop trialprop ON materialized_phenoview.trial_id = trialprop.project_id AND trialprop.type_id IN (SELECT cvterm_id from cvterm JOIN cv USING(cv_id) WHERE cv.name = 'project_type' )
 677    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 678   GROUP BY public.materialized_phenoview.accession_id, trialterm.cvterm_id;
 679 ALTER VIEW accessionsXtrial_types OWNER TO web_usr;
 680
 681 DROP VIEW IF EXISTS public.accessionsXtrials CASCADE;
 682 CREATE VIEW public.accessionsXtrials AS
 683 SELECT public.materialized_phenoview.accession_id,
 684     public.materialized_phenoview.trial_id
 685    FROM public.materialized_phenoview
 686   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.trial_id;
 687 ALTER VIEW accessionsXtrials OWNER TO web_usr;
 688
 689 DROP VIEW IF EXISTS public.accessionsXyears CASCADE;
 690 CREATE VIEW public.accessionsXyears AS
 691 SELECT public.materialized_phenoview.accession_id,
 692     public.materialized_phenoview.year_id
 693    FROM public.materialized_phenoview
 694   GROUP BY public.materialized_phenoview.accession_id, public.materialized_phenoview.year_id;
 695 ALTER VIEW accessionsXyears OWNER TO web_usr;
 696
 697 DROP VIEW IF EXISTS public.breeding_programsXgenotyping_protocols CASCADE;
 698 CREATE VIEW public.breeding_programsXgenotyping_protocols AS
 699 SELECT public.materialized_phenoview.breeding_program_id,
 700     public.materialized_genoview.genotyping_protocol_id
 701    FROM public.materialized_phenoview
 702    JOIN public.materialized_genoview USING(accession_id)
 703   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_genoview.genotyping_protocol_id;
 704 ALTER VIEW breeding_programsXgenotyping_protocols OWNER TO web_usr;
 705
 706 DROP VIEW IF EXISTS public.breeding_programsXlocations CASCADE;
 707 CREATE VIEW public.breeding_programsXlocations AS
 708 SELECT public.materialized_phenoview.breeding_program_id,
 709     public.materialized_phenoview.location_id
 710    FROM public.materialized_phenoview
 711   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_phenoview.location_id;
 712 ALTER VIEW breeding_programsXlocations OWNER TO web_usr;
 713
 714 DROP VIEW IF EXISTS public.breeding_programsXplants CASCADE;
 715 CREATE VIEW public.breeding_programsXplants AS
 716 SELECT public.materialized_phenoview.breeding_program_id,
 717     public.stock.stock_id AS plant_id
 718    FROM public.materialized_phenoview
 719    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plant'))
 720   GROUP BY public.materialized_phenoview.breeding_program_id, public.stock.stock_id;
 721   ALTER VIEW breeding_programsXplants OWNER TO web_usr;
 722
 723 DROP VIEW IF EXISTS public.breeding_programsXplots CASCADE;
 724 CREATE VIEW public.breeding_programsXplots AS
 725 SELECT public.materialized_phenoview.breeding_program_id,
 726     public.stock.stock_id AS plot_id
 727    FROM public.materialized_phenoview
 728    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plot'))
 729   GROUP BY public.materialized_phenoview.breeding_program_id, public.stock.stock_id;
 730 ALTER VIEW breeding_programsXplots OWNER TO web_usr;
 731
 732 DROP VIEW IF EXISTS public.breeding_programsXtrial_designs CASCADE;
 733 CREATE VIEW public.breeding_programsXtrial_designs AS
 734 SELECT public.materialized_phenoview.breeding_program_id,
 735     trialdesign.value AS trial_design_id
 736    FROM public.materialized_phenoview
 737    JOIN public.projectprop trialdesign ON materialized_phenoview.trial_id = trialdesign.project_id AND trialdesign.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'design' )
 738   GROUP BY public.materialized_phenoview.breeding_program_id, trialdesign.value;
 739 ALTER VIEW breeding_programsXtrial_designs OWNER TO web_usr;
 740
 741 DROP VIEW IF EXISTS public.breeding_programsXtrial_types CASCADE;
 742 CREATE VIEW public.breeding_programsXtrial_types AS
 743 SELECT public.materialized_phenoview.breeding_program_id,
 744     trialterm.cvterm_id AS trial_type_id
 745    FROM public.materialized_phenoview
 746    JOIN projectprop trialprop ON materialized_phenoview.trial_id = trialprop.project_id AND trialprop.type_id IN (SELECT cvterm_id from cvterm JOIN cv USING(cv_id) WHERE cv.name = 'project_type' )
 747    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 748   GROUP BY public.materialized_phenoview.breeding_program_id, trialterm.cvterm_id;
 749 ALTER VIEW breeding_programsXtrial_types OWNER TO web_usr;
 750
 751 DROP VIEW IF EXISTS public.breeding_programsXtrials CASCADE;
 752 CREATE VIEW public.breeding_programsXtrials AS
 753 SELECT public.materialized_phenoview.breeding_program_id,
 754     public.materialized_phenoview.trial_id
 755    FROM public.materialized_phenoview
 756   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_phenoview.trial_id;
 757 ALTER VIEW breeding_programsXtrials OWNER TO web_usr;
 758
 759 DROP VIEW IF EXISTS public.breeding_programsXyears CASCADE;
 760 CREATE VIEW public.breeding_programsXyears AS
 761 SELECT public.materialized_phenoview.breeding_program_id,
 762     public.materialized_phenoview.year_id
 763    FROM public.materialized_phenoview
 764   GROUP BY public.materialized_phenoview.breeding_program_id, public.materialized_phenoview.year_id;
 765 ALTER VIEW breeding_programsXyears OWNER TO web_usr;
 766
 767 DROP VIEW IF EXISTS public.genotyping_protocolsXlocations CASCADE;
 768 CREATE VIEW public.genotyping_protocolsXlocations AS
 769 SELECT public.materialized_genoview.genotyping_protocol_id,
 770     public.materialized_phenoview.location_id
 771    FROM public.materialized_genoview
 772    JOIN public.materialized_phenoview USING(accession_id)
 773   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.location_id;
 774 ALTER VIEW genotyping_protocolsXlocations OWNER TO web_usr;
 775
 776 DROP VIEW IF EXISTS public.genotyping_protocolsXplants CASCADE;
 777 CREATE VIEW public.genotyping_protocolsXplants AS
 778 SELECT public.materialized_genoview.genotyping_protocol_id,
 779     public.stock.stock_id AS plant_id
 780    FROM public.materialized_genoview
 781    JOIN public.materialized_phenoview USING(accession_id)
 782    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plant'))
 783   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.stock.stock_id;
 784   ALTER VIEW genotyping_protocolsXplants OWNER TO web_usr;
 785
 786 DROP VIEW IF EXISTS public.genotyping_protocolsXplots CASCADE;
 787 CREATE VIEW public.genotyping_protocolsXplots AS
 788 SELECT public.materialized_genoview.genotyping_protocol_id,
 789     public.stock.stock_id AS plot_id
 790     FROM public.materialized_genoview
 791     JOIN public.materialized_phenoview USING(accession_id)
 792    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plot'))
 793   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.stock.stock_id;
 794 ALTER VIEW genotyping_protocolsXplots OWNER TO web_usr;
 795
 796 DROP VIEW IF EXISTS public.genotyping_protocolsXtrial_designs CASCADE;
 797 CREATE VIEW public.genotyping_protocolsXtrial_designs AS
 798 SELECT public.materialized_genoview.genotyping_protocol_id,
 799     trialdesign.value AS trial_design_id
 800     FROM public.materialized_genoview
 801     JOIN public.materialized_phenoview USING(accession_id)
 802    JOIN public.projectprop trialdesign ON materialized_phenoview.trial_id = trialdesign.project_id AND trialdesign.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'design' )
 803   GROUP BY public.materialized_genoview.genotyping_protocol_id, trialdesign.value;
 804 ALTER VIEW genotyping_protocolsXtrial_designs OWNER TO web_usr;
 805
 806 DROP VIEW IF EXISTS public.genotyping_protocolsXtrial_types CASCADE;
 807 CREATE VIEW public.genotyping_protocolsXtrial_types AS
 808 SELECT public.materialized_genoview.genotyping_protocol_id,
 809     trialterm.cvterm_id AS trial_type_id
 810     FROM public.materialized_genoview
 811     JOIN public.materialized_phenoview USING(accession_id)
 812    JOIN projectprop trialprop ON materialized_phenoview.trial_id = trialprop.project_id AND trialprop.type_id IN (SELECT cvterm_id from cvterm JOIN cv USING(cv_id) WHERE cv.name = 'project_type' )
 813    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 814   GROUP BY public.materialized_genoview.genotyping_protocol_id, trialterm.cvterm_id;
 815 ALTER VIEW genotyping_protocolsXtrial_types OWNER TO web_usr;
 816
 817 DROP VIEW IF EXISTS public.genotyping_protocolsXtrials CASCADE;
 818 CREATE VIEW public.genotyping_protocolsXtrials AS
 819 SELECT public.materialized_genoview.genotyping_protocol_id,
 820     public.materialized_phenoview.trial_id
 821    FROM public.materialized_genoview
 822    JOIN public.materialized_phenoview USING(accession_id)
 823   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.trial_id;
 824 ALTER VIEW genotyping_protocolsXtrials OWNER TO web_usr;
 825
 826 DROP VIEW IF EXISTS public.genotyping_protocolsXyears CASCADE;
 827 CREATE VIEW public.genotyping_protocolsXyears AS
 828 SELECT public.materialized_genoview.genotyping_protocol_id,
 829     public.materialized_phenoview.year_id
 830    FROM public.materialized_genoview
 831    JOIN public.materialized_phenoview USING(accession_id)
 832   GROUP BY public.materialized_genoview.genotyping_protocol_id, public.materialized_phenoview.year_id;
 833 ALTER VIEW genotyping_protocolsXyears OWNER TO web_usr;
 834
 835 DROP VIEW IF EXISTS public.locationsXplants CASCADE;
 836 CREATE VIEW public.locationsXplants AS
 837 SELECT public.materialized_phenoview.location_id,
 838     public.stock.stock_id AS plant_id
 839    FROM public.materialized_phenoview
 840    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plant'))
 841   GROUP BY public.materialized_phenoview.location_id, public.stock.stock_id;
 842   ALTER VIEW locationsXplants OWNER TO web_usr;
 843
 844 DROP VIEW IF EXISTS public.locationsXplots CASCADE;
 845 CREATE VIEW public.locationsXplots AS
 846 SELECT public.materialized_phenoview.location_id,
 847     public.stock.stock_id AS plot_id
 848    FROM public.materialized_phenoview
 849    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plot'))
 850   GROUP BY public.materialized_phenoview.location_id, public.stock.stock_id;
 851 ALTER VIEW locationsXplots OWNER TO web_usr;
 852
 853 DROP VIEW IF EXISTS public.locationsXtrial_designs CASCADE;
 854 CREATE VIEW public.locationsXtrial_designs AS
 855 SELECT public.materialized_phenoview.location_id,
 856     trialdesign.value AS trial_design_id
 857    FROM public.materialized_phenoview
 858    JOIN public.projectprop trialdesign ON materialized_phenoview.trial_id = trialdesign.project_id AND trialdesign.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'design' )
 859   GROUP BY public.materialized_phenoview.location_id, trialdesign.value;
 860 ALTER VIEW locationsXtrial_designs OWNER TO web_usr;
 861
 862 DROP VIEW IF EXISTS public.locationsXtrial_types CASCADE;
 863 CREATE VIEW public.locationsXtrial_types AS
 864 SELECT public.materialized_phenoview.location_id,
 865     trialterm.cvterm_id AS trial_type_id
 866    FROM public.materialized_phenoview
 867    JOIN projectprop trialprop ON materialized_phenoview.trial_id = trialprop.project_id AND trialprop.type_id IN (SELECT cvterm_id from cvterm JOIN cv USING(cv_id) WHERE cv.name = 'project_type' )
 868    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 869   GROUP BY public.materialized_phenoview.location_id, trialterm.cvterm_id;
 870 ALTER VIEW locationsXtrial_types OWNER TO web_usr;
 871
 872 DROP VIEW IF EXISTS public.locationsXtrials CASCADE;
 873 CREATE VIEW public.locationsXtrials AS
 874 SELECT public.materialized_phenoview.location_id,
 875     public.materialized_phenoview.trial_id
 876    FROM public.materialized_phenoview
 877   GROUP BY public.materialized_phenoview.location_id, public.materialized_phenoview.trial_id;
 878 ALTER VIEW locationsXtrials OWNER TO web_usr;
 879
 880 DROP VIEW IF EXISTS public.locationsXyears CASCADE;
 881 CREATE VIEW public.locationsXyears AS
 882 SELECT public.materialized_phenoview.location_id,
 883     public.materialized_phenoview.year_id
 884    FROM public.materialized_phenoview
 885   GROUP BY public.materialized_phenoview.location_id, public.materialized_phenoview.year_id;
 886 ALTER VIEW locationsXyears OWNER TO web_usr;
 887
 888 DROP VIEW IF EXISTS public.plantsXplots CASCADE;
 889 CREATE VIEW public.plantsXplots AS
 890 SELECT plant.stock_id AS plant_id,
 891     plot.stock_id AS plot_id
 892    FROM public.materialized_phenoview
 893    JOIN stock plot ON(public.materialized_phenoview.stock_id = plot.stock_id AND plot.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plot'))
 894    JOIN stock_relationship plant_relationship ON plot.stock_id = plant_relationship.subject_id
 895    JOIN stock plant ON plant_relationship.object_id = plant.stock_id AND plant.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plant')
 896   GROUP BY plant.stock_id, plot.stock_id;
 897 ALTER VIEW plantsXplots OWNER TO web_usr;
 898
 899 DROP VIEW IF EXISTS public.plantsXtrials CASCADE;
 900 CREATE VIEW public.plantsXtrials AS
 901 SELECT public.stock.stock_id AS plant_id,
 902     public.materialized_phenoview.trial_id
 903    FROM public.materialized_phenoview
 904    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plant'))
 905   GROUP BY public.stock.stock_id, public.materialized_phenoview.trial_id;
 906 ALTER VIEW plantsXtrials OWNER TO web_usr;
 907
 908 DROP VIEW IF EXISTS public.plantsXtrial_designs CASCADE;
 909 CREATE VIEW public.plantsXtrial_designs AS
 910 SELECT public.stock.stock_id AS plant_id,
 911     trialdesign.value AS trial_design_id
 912    FROM public.materialized_phenoview
 913    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plant'))
 914    JOIN public.projectprop trialdesign ON materialized_phenoview.trial_id = trialdesign.project_id AND trialdesign.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'design' )
 915   GROUP BY stock.stock_id, trialdesign.value;
 916 ALTER VIEW plantsXtrial_designs OWNER TO web_usr;
 917
 918 DROP VIEW IF EXISTS public.plantsXtrial_types CASCADE;
 919 CREATE VIEW public.plantsXtrial_types AS
 920 SELECT public.stock.stock_id AS plant_id,
 921     trialterm.cvterm_id AS trial_type_id
 922    FROM public.materialized_phenoview
 923    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plant'))
 924    JOIN projectprop trialprop ON materialized_phenoview.trial_id = trialprop.project_id AND trialprop.type_id IN (SELECT cvterm_id from cvterm JOIN cv USING(cv_id) WHERE cv.name = 'project_type' )
 925    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 926   GROUP BY public.stock.stock_id, trialterm.cvterm_id;
 927 ALTER VIEW plantsXtrial_types OWNER TO web_usr;
 928
 929 DROP VIEW IF EXISTS public.plantsXyears CASCADE;
 930 CREATE VIEW public.plantsXyears AS
 931 SELECT public.stock.stock_id AS plant_id,
 932     public.materialized_phenoview.year_id
 933    FROM public.materialized_phenoview
 934    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plant'))
 935   GROUP BY public.stock.stock_id, public.materialized_phenoview.year_id;
 936 ALTER VIEW plantsXyears OWNER TO web_usr;
 937
 938 DROP VIEW IF EXISTS public.plotsXtrials CASCADE;
 939 CREATE VIEW public.plotsXtrials AS
 940 SELECT public.stock.stock_id AS plot_id,
 941     public.materialized_phenoview.trial_id
 942    FROM public.materialized_phenoview
 943    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plot'))
 944   GROUP BY public.stock.stock_id, public.materialized_phenoview.trial_id;
 945 ALTER VIEW plotsXtrials OWNER TO web_usr;
 946
 947 DROP VIEW IF EXISTS public.plotsXtrial_designs CASCADE;
 948 CREATE VIEW public.plotsXtrial_designs AS
 949 SELECT public.stock.stock_id AS plot_id,
 950     trialdesign.value AS trial_design_id
 951    FROM public.materialized_phenoview
 952    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plot'))
 953    JOIN public.projectprop trialdesign ON materialized_phenoview.trial_id = trialdesign.project_id AND trialdesign.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'design' )
 954   GROUP BY stock.stock_id, trialdesign.value;
 955 ALTER VIEW plotsXtrial_designs OWNER TO web_usr;
 956
 957 DROP VIEW IF EXISTS public.plotsXtrial_types CASCADE;
 958 CREATE VIEW public.plotsXtrial_types AS
 959 SELECT public.stock.stock_id AS plot_id,
 960     trialterm.cvterm_id AS trial_type_id
 961    FROM public.materialized_phenoview
 962    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plot'))
 963    JOIN projectprop trialprop ON materialized_phenoview.trial_id = trialprop.project_id AND trialprop.type_id IN (SELECT cvterm_id from cvterm JOIN cv USING(cv_id) WHERE cv.name = 'project_type' )
 964    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 965   GROUP BY public.stock.stock_id, trialterm.cvterm_id;
 966 ALTER VIEW plotsXtrial_types OWNER TO web_usr;
 967
 968 DROP VIEW IF EXISTS public.plotsXyears CASCADE;
 969 CREATE VIEW public.plotsXyears AS
 970 SELECT public.stock.stock_id AS plot_id,
 971     public.materialized_phenoview.year_id
 972    FROM public.materialized_phenoview
 973    JOIN public.stock ON(public.materialized_phenoview.stock_id = public.stock.stock_id AND public.stock.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'plot'))
 974   GROUP BY public.stock.stock_id, public.materialized_phenoview.year_id;
 975 ALTER VIEW plotsXyears OWNER TO web_usr;
 976
 977 DROP VIEW IF EXISTS public.trial_designsXtrial_types CASCADE;
 978 CREATE VIEW public.trial_designsXtrial_types AS
 979 SELECT trialdesign.value AS trial_design_id,
 980     trialterm.cvterm_id AS trial_type_id
 981    FROM public.materialized_phenoview
 982    JOIN public.projectprop trialdesign ON materialized_phenoview.trial_id = trialdesign.project_id AND trialdesign.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'design' )
 983    JOIN projectprop trialprop ON materialized_phenoview.trial_id = trialprop.project_id AND trialprop.type_id IN (SELECT cvterm_id from cvterm JOIN cv USING(cv_id) WHERE cv.name = 'project_type' )
 984    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
 985   GROUP BY trialdesign.value, trialterm.cvterm_id;
 986 ALTER VIEW trial_designsXtrial_types OWNER TO web_usr;
 987
 988 DROP VIEW IF EXISTS public.trial_designsXtrials CASCADE;
 989 CREATE VIEW public.trial_designsXtrials AS
 990 SELECT trialdesign.value AS trial_design_id,
 991     public.materialized_phenoview.trial_id
 992    FROM public.materialized_phenoview
 993    JOIN public.projectprop trialdesign ON materialized_phenoview.trial_id = trialdesign.project_id AND trialdesign.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'design' )
 994   GROUP BY trialdesign.value, public.materialized_phenoview.trial_id;
 995 ALTER VIEW trial_designsXtrials OWNER TO web_usr;
 996
 997 DROP VIEW IF EXISTS public.trial_designsXyears CASCADE;
 998 CREATE VIEW public.trial_designsXyears AS
 999 SELECT trialdesign.value AS trial_design_id,
1000     public.materialized_phenoview.year_id
1001    FROM public.materialized_phenoview
1002    JOIN public.projectprop trialdesign ON materialized_phenoview.trial_id = trialdesign.project_id AND trialdesign.type_id = (SELECT cvterm_id from cvterm where cvterm.name = 'design' )
1003   GROUP BY trialdesign.value, public.materialized_phenoview.year_id;
1004 ALTER VIEW trial_designsXyears OWNER TO web_usr;
1005
1006 DROP VIEW IF EXISTS public.trial_typesXtrials CASCADE;
1007 CREATE VIEW public.trial_typesXtrials AS
1008 SELECT trialterm.cvterm_id AS trial_type_id,
1009     public.materialized_phenoview.trial_id
1010    FROM public.materialized_phenoview
1011    JOIN projectprop trialprop ON materialized_phenoview.trial_id = trialprop.project_id AND trialprop.type_id IN (SELECT cvterm_id from cvterm JOIN cv USING(cv_id) WHERE cv.name = 'project_type' )
1012    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
1013   GROUP BY trialterm.cvterm_id, public.materialized_phenoview.trial_id;
1014 ALTER VIEW trial_typesXtrials OWNER TO web_usr;
1015
1016 DROP VIEW IF EXISTS public.trial_typesXyears CASCADE;
1017 CREATE VIEW public.trial_typesXyears AS
1018 SELECT trialterm.cvterm_id AS trial_type_id,
1019     public.materialized_phenoview.year_id
1020    FROM public.materialized_phenoview
1021    JOIN projectprop trialprop ON materialized_phenoview.trial_id = trialprop.project_id AND trialprop.type_id IN (SELECT cvterm_id from cvterm JOIN cv USING(cv_id) WHERE cv.name = 'project_type' )
1022    JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
1023   GROUP BY trialterm.cvterm_id, public.materialized_phenoview.year_id;
1024 ALTER VIEW trial_typesXyears OWNER TO web_usr;
1025
1026 DROP VIEW IF EXISTS public.trialsXyears CASCADE;
1027 CREATE VIEW public.trialsXyears AS
1028 SELECT public.materialized_phenoview.trial_id,
1029     public.materialized_phenoview.year_id
1030    FROM public.materialized_phenoview
1031   GROUP BY public.materialized_phenoview.trial_id, public.materialized_phenoview.year_id;
1032 ALTER VIEW trialsXyears OWNER TO web_usr;
1033
1034 */
1035
1036
1037 -- NEW GENOTYPE PROJECT VIEWS --
1038
1039 /* ======= Included in the AddGenotypeProjectViews DB Patch =========
1040
1041 -- Drop any remaining genotype project matviews
1042 DROP MATERIALIZED VIEW IF EXISTS public.genotyping_projects CASCADE;
1043 DROP MATERIALIZED VIEW IF EXISTS public.accessionsXgenotyping_projects CASCADE;
1044 DROP MATERIALIZED VIEW IF EXISTS public.breeding_programsXgenotyping_projects CASCADE;
1045 DROP MATERIALIZED VIEW IF EXISTS public.genotyping_protocolsXgenotyping_projects CASCADE;
1046 DROP MATERIALIZED VIEW IF EXISTS public.locationsXgenotyping_projects CASCADE;
1047 DROP MATERIALIZED VIEW IF EXISTS public.trialsXgenotyping_projects CASCADE;
1048 DROP MATERIALIZED VIEW IF EXISTS public.genotyping_projectsXtraits CASCADE;
1049 DROP MATERIALIZED VIEW IF EXISTS public.genotyping_projectsXyears CASCADE;
1050 DROP MATERIALIZED VIEW IF EXISTS public.genotyping_projectsXaccessions CASCADE;
1051 DROP MATERIALIZED VIEW IF EXISTS public.genotyping_projectsXbreeding_programs CASCADE;
1052 DROP MATERIALIZED VIEW IF EXISTS public.genotyping_projectsXgenotyping_protocols CASCADE;
1053 DROP MATERIALIZED VIEW IF EXISTS public.genotyping_projectsXlocations CASCADE;
1054 DROP MATERIALIZED VIEW IF EXISTS public.genotyping_projectsXtrials CASCADE;
1055
1056 -- Add genotyping_projects view
1057 CREATE VIEW public.genotyping_projects AS
1058     SELECT project.project_id AS genotyping_project_id, project.name AS genotyping_project_name
1059     FROM project
1060     JOIN projectprop USING (project_id)
1061     WHERE projectprop.type_id = (SELECT cvterm_id FROM public.cvterm WHERE name = 'design')
1062         AND projectprop.value = 'genotype_data_project';
1063 ALTER VIEW public.genotyping_projects OWNER TO web_usr;
1064
1065 -- Add accessionsXgenotyping_projects view
1066 CREATE VIEW public.accessionsXgenotyping_projects AS
1067     SELECT accession_id, genotyping_project_id
1068     FROM materialized_genoview
1069     GROUP BY 1,2;
1070 ALTER VIEW public.accessionsXgenotyping_projects OWNER TO web_usr;
1071
1072 -- Add breeding_programsXgenotyping_projects view
1073 CREATE VIEW public.breeding_programsXgenotyping_projects AS
1074     SELECT project_relationship.object_project_id AS breeding_program_id,
1075         project.project_id AS genotyping_project_id
1076     FROM public.project
1077     LEFT JOIN public.projectprop ON (project.project_id = projectprop.project_id)
1078     LEFT JOIN public.project_relationship ON (project.project_id = project_relationship.subject_project_id)
1079     WHERE projectprop.type_id = (SELECT cvterm_id FROM public.cvterm WHERE name = 'design')
1080         AND projectprop.value = 'genotype_data_project';
1081 ALTER VIEW public.breeding_programsXgenotyping_projects OWNER TO web_usr;
1082
1083 -- Add genotyping_projectsXgenotyping_protocols view
1084 CREATE VIEW public.genotyping_projectsXgenotyping_protocols AS
1085     SELECT genotyping_project_id, genotyping_protocol_id
1086     FROM materialized_genoview
1087     GROUP BY 1,2;
1088 ALTER VIEW public.genotyping_projectsXgenotyping_protocols OWNER TO web_usr;
1089
1090 -- Add genotyping_projectsXlocations view
1091 CREATE VIEW public.genotyping_projectsXlocations AS
1092     SELECT materialized_genoview.genotyping_project_id, materialized_phenoview.location_id
1093     FROM materialized_genoview
1094     JOIN materialized_phenoview USING (accession_id)
1095     GROUP BY 1,2;
1096 ALTER VIEW public.genotyping_projectsXlocations OWNER TO web_usr;
1097
1098 -- Add genotyping_projectsXtrials view
1099 CREATE VIEW public.genotyping_projectsXtrials AS
1100     SELECT materialized_genoview.genotyping_project_id, materialized_phenoview.trial_id
1101     FROM materialized_genoview
1102     JOIN materialized_phenoview USING (accession_id)
1103     GROUP BY 1,2;
1104 ALTER VIEW public.genotyping_projectsXtrials OWNER TO web_usr;
1105
1106 -- Add genotyping_projectsXtraits view
1107 CREATE VIEW public.genotyping_projectsXtraits AS
1108     SELECT materialized_genoview.genotyping_project_id, materialized_phenoview.trait_id
1109     FROM materialized_genoview
1110     JOIN materialized_phenoview USING (accession_id)
1111     GROUP BY 1,2;
1112 ALTER VIEW public.genotyping_projectsXtraits OWNER TO web_usr;
1113
1114 -- Add genotyping_projectsXyears view
1115 CREATE VIEW public.genotyping_projectsXyears AS
1116     SELECT projectprop.project_id AS genotyping_project_id, projectprop.value AS year_id
1117     FROM projectprop
1118     WHERE projectprop.project_id IN (
1119         SELECT projectprop.project_id
1120         FROM projectprop
1121         WHERE projectprop.type_id = (SELECT cvterm_id FROM public.cvterm WHERE name = 'design')
1122             AND projectprop.value = 'genotype_data_project'
1123     )
1124     AND projectprop.type_id = (SELECT cvterm_id FROM public.cvterm WHERE name = 'project year');
1125 ALTER VIEW public.genotyping_projectsXyears OWNER TO web_usr;
1126
1127 -- Add genotyping_projectsXplants view
1128 CREATE VIEW public.genotyping_projectsXplants AS
1129     SELECT materialized_genoview.genotyping_project_id, stock.stock_id AS plant_id
1130     FROM materialized_genoview
1131     JOIN materialized_phenoview USING (accession_id)
1132     JOIN stock ON materialized_phenoview.stock_id = stock.stock_id AND stock.type_id = (
1133         SELECT cvterm.cvterm_id
1134         FROM cvterm
1135         WHERE cvterm.name = 'plant'
1136     )
1137     GROUP BY 1,2;
1138 ALTER VIEW public.genotyping_projectsXplants OWNER TO web_usr;
1139
1140 -- Add genotyping_projectsXplots view
1141 CREATE VIEW public.genotyping_projectsXplots AS
1142     SELECT materialized_genoview.genotyping_project_id, stock.stock_id AS plot_id
1143     FROM materialized_genoview
1144     JOIN materialized_phenoview USING (accession_id)
1145     JOIN stock ON materialized_phenoview.stock_id = stock.stock_id AND stock.type_id = (
1146         SELECT cvterm.cvterm_id
1147         FROM cvterm
1148         WHERE cvterm.name = 'plot'
1149     )
1150     GROUP BY 1,2;
1151 ALTER VIEW public.genotyping_projectsXplots OWNER TO web_usr;
1152
1153 -- Add genotyping_projectsXseedlots view
1154 CREATE VIEW public.genotyping_projectsXseedlots AS
1155     SELECT materialized_genoview.genotyping_project_id, stock.stock_id AS seedlot_id
1156     FROM materialized_genoview
1157     LEFT JOIN stock_relationship seedlot_relationship ON materialized_genoview.accession_id = seedlot_relationship.subject_id
1158         AND seedlot_relationship.type_id IN (SELECT cvterm.cvterm_id FROM cvterm WHERE cvterm.name = 'collection_of')
1159     LEFT JOIN stock ON seedlot_relationship.object_id = stock.stock_id
1160         AND stock.type_id IN (SELECT cvterm.cvterm_id FROM cvterm WHERE cvterm.name = 'seedlot')
1161     GROUP BY 1,2;
1162 ALTER VIEW public.genotyping_projectsXseedlots OWNER TO web_usr;
1163
1164 -- Add genotyping_projectsXtrait_components view
1165 CREATE VIEW public.genotyping_projectsXtrait_components AS
1166     SELECT materialized_genoview.genotyping_project_id, trait_component.cvterm_id AS trait_component_id
1167     FROM materialized_genoview
1168     JOIN materialized_phenoview USING (accession_id)
1169     JOIN cvterm trait ON materialized_phenoview.trait_id = trait.cvterm_id
1170     JOIN cvterm_relationship ON trait.cvterm_id = cvterm_relationship.object_id
1171         AND cvterm_relationship.type_id = (SELECT cvterm.cvterm_id FROM cvterm WHERE cvterm.name = 'contains')
1172     JOIN cvterm trait_component ON cvterm_relationship.subject_id = trait_component.cvterm_id
1173     GROUP BY 1,2;
1174 ALTER VIEW public.genotyping_projectsXtrait_components OWNER TO web_usr;
1175
1176 -- Add genotyping_projectsXtrial_designs view
1177 CREATE VIEW public.genotyping_projectsXtrial_designs AS
1178     SELECT materialized_genoview.genotyping_project_id, trialdesign.value AS trial_design_id
1179     FROM materialized_genoview
1180     JOIN materialized_phenoview USING (accession_id)
1181     JOIN projectprop trialdesign ON materialized_phenoview.trial_id = trialdesign.project_id
1182         AND trialdesign.type_id = (SELECT cvterm.cvterm_id FROM cvterm WHERE cvterm.name = 'design')
1183     GROUP BY 1,2;
1184 ALTER VIEW public.genotyping_projectsXtrial_designs OWNER TO web_usr;
1185
1186 -- Add genotyping_projectsXtrial_types view
1187 CREATE VIEW public.genotyping_projectsXtrial_types AS
1188     SELECT materialized_genoview.genotyping_project_id, trialterm.cvterm_id AS trial_type_id
1189     FROM materialized_genoview
1190     JOIN materialized_phenoview USING (accession_id)
1191     JOIN projectprop trialprop ON materialized_phenoview.trial_id = trialprop.project_id
1192         AND trialprop.type_id IN (SELECT cvterm.cvterm_id FROM cvterm JOIN cv USING (cv_id) WHERE cv.name = 'project_type')
1193     JOIN cvterm trialterm ON trialprop.type_id = trialterm.cvterm_id
1194     GROUP BY 1,2;
1195 ALTER VIEW genotyping_projectsXtrial_types OWNER TO web_usr;
1196
1197 */
1198
1199 EOSQL
1200
1201 print "You're done!\n";
1202 }
1203
1204
1205 ####
1206 1; #
1207 ####