db/00148/AddGenotypeProjectViews.pm

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