db/LoadChadoGenotype.pm

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6  LoadChadoGenotype.pm
   7
   8 =head1 SYNOPSIS
   9
  10 mx-run ThisPackageName [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
  18 This subclass uses L<Moose>. The parent class uses L<MooseX::Runnable>
  19
  20 =head1 AUTHOR
  21
  22  Naama Menda<nm249@cornell.edu>
  23
  24 =head1 COPYRIGHT & LICENSE
  25
  26 Copyright 2010 Boyce Thompson Institute for Plant Research
  27
  28 This program is free software; you can redistribute it and/or modify
  29 it under the same terms as Perl itself.
  30
  31 =cut
  32
  33
  34 package LoadChadoGenotype;
  35
  36 use Try::Tiny;
  37 use Moose;
  38 extends 'CXGN::Metadata::Dbpatch';
  39
  40
  41 sub init_patch {
  42     my $self=shift;
  43     my $name = __PACKAGE__;
  44     print "dbpatch name is : '" .  $name . "'\n\n";
  45     my $description = 'Loading the chado stock module';
  46     my @previous_requested_patches = (); #ADD HERE
  47
  48     $self->name($name);
  49     $self->description($description);
  50     $self->prereq(\@previous_requested_patches);
  51
  52 }
  53
  54 sub patch {
  55     my $self=shift;
  56
  57
  58     print STDOUT "Executing the patch:\n " .   $self->name . ".\n\nDescription:\n  ".  $self->description . ".\n\nExecuted by:\n " .  $self->username . " .";
  59
  60     print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
  61
  62
  63
  64     print STDOUT "\nExecuting the SQL commands.\n";
  65
  66     my @tables = (
  67         qw /
  68 genotype
  69 feature_genotype
  70 environment
  71 environment_cvterm
  72 phenstatement
  73 phendesc
  74 phenotype_comparison
  75 phenotype_comparison_cvterm
  76 / );
  77
  78         $self->dbh->do(<<EOSQL);
  79
  80 SET SEARCH_PATH  TO public;
  81 --do your SQL here
  82 --
  83 -- Id: genetic.sql,v 1.31 2008-08-25 19:53:14 scottcain Exp $
  84 -- ==========================================
  85 -- Chado genetics module
  86 --
  87 -- 2006-04-11
  88 --   split out phenotype tables into phenotype module
  89 --
  90 -- redesigned 2003-10-28
  91 --
  92 -- changes 2003-11-10:
  93 --   incorporating suggestions to make everything a gcontext; use
  94 --   gcontext_relationship to make some gcontexts derivable from others. we
  95 --   would incorporate environment this way - just add the environment
  96 --   descriptors as properties of the child gcontext
  97 --
  98 -- changes 2004-06 (Documented by DE: 10-MAR-2005):
  99 --   Many, including rename of gcontext to genotype,  split
 100 --   phenstatement into phenstatement & phenotype, created environment
 101 --
 102 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 103 -- ============
 104 -- DEPENDENCIES
 105 -- ============
 106 -- :import feature from sequence
 107 -- :import phenotype from phenotype
 108 -- :import cvterm from cv
 109 -- :import pub from pub
 110 -- :import dbxref from general
 111 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 112
 113 -- ================================================
 114 -- TABLE: genotype
 115 -- ================================================
 116 create table genotype (
 117     genotype_id serial not null,
 118     primary key (genotype_id),
 119     name text,
 120     uniquename text not null,
 121     description varchar(255),
 122     constraint genotype_c1 unique (uniquename)
 123 );
 124 create index genotype_idx1 on genotype(uniquename);
 125 create index genotype_idx2 on genotype(name);
 126
 127 COMMENT ON TABLE genotype IS 'Genetic context. A genotype is defined by a collection of features, mutations, balancers, deficiencies, haplotype blocks, or engineered constructs.';
 128
 129 COMMENT ON COLUMN genotype.uniquename IS 'The unique name for a genotype;
 130 typically derived from the features making up the genotype.';
 131
 132 COMMENT ON COLUMN genotype.name IS 'Optional alternative name for a genotype,
 133 for display purposes.';
 134
 135 -- ===============================================
 136 -- TABLE: feature_genotype
 137 -- ================================================
 138 create table feature_genotype (
 139     feature_genotype_id serial not null,
 140     primary key (feature_genotype_id),
 141     feature_id int not null,
 142     foreign key (feature_id) references feature (feature_id) on delete cascade,
 143     genotype_id int not null,
 144     foreign key (genotype_id) references genotype (genotype_id) on delete cascade,
 145     chromosome_id int,
 146     foreign key (chromosome_id) references feature (feature_id) on delete set null,
 147     rank int not null,
 148     cgroup    int not null,
 149     cvterm_id int not null,
 150     foreign key (cvterm_id) references cvterm (cvterm_id) on delete cascade,
 151     constraint feature_genotype_c1 unique (feature_id, genotype_id, cvterm_id, chromosome_id, rank, cgroup)
 152 );
 153 create index feature_genotype_idx1 on feature_genotype (feature_id);
 154 create index feature_genotype_idx2 on feature_genotype (genotype_id);
 155
 156 COMMENT ON TABLE feature_genotype IS NULL;
 157 COMMENT ON COLUMN feature_genotype.rank IS 'rank can be used for
 158 n-ploid organisms or to preserve order.';
 159 COMMENT ON COLUMN feature_genotype.cgroup IS 'Spatially distinguishable
 160 group. group can be used for distinguishing the chromosomal groups,
 161 for example (RNAi products and so on can be treated as different
 162 groups, as they do not fall on a particular chromosome).';
 163 COMMENT ON COLUMN feature_genotype.chromosome_id IS 'A feature of SO type "chromosome".';
 164
 165 -- ================================================
 166 -- TABLE: environment
 167 -- ================================================
 168 create table environment (
 169     environment_id serial not NULL,
 170     primary key  (environment_id),
 171     uniquename text not null,
 172     description text,
 173     constraint environment_c1 unique (uniquename)
 174 );
 175 create index environment_idx1 on environment(uniquename);
 176
 177 COMMENT ON TABLE environment IS 'The environmental component of a phenotype description.';
 178
 179
 180 -- ================================================
 181 -- TABLE: environment_cvterm
 182 -- ================================================
 183 create table environment_cvterm (
 184     environment_cvterm_id serial not null,
 185     primary key  (environment_cvterm_id),
 186     environment_id int not null,
 187     foreign key (environment_id) references environment (environment_id) on delete cascade,
 188     cvterm_id int not null,
 189     foreign key (cvterm_id) references cvterm (cvterm_id) on delete cascade,
 190     constraint environment_cvterm_c1 unique (environment_id, cvterm_id)
 191 );
 192 create index environment_cvterm_idx1 on environment_cvterm (environment_id);
 193 create index environment_cvterm_idx2 on environment_cvterm (cvterm_id);
 194
 195 COMMENT ON TABLE environment_cvterm IS NULL;
 196
 197 -- ================================================
 198 -- TABLE: phenstatement
 199 -- ================================================
 200 CREATE TABLE phenstatement (
 201     phenstatement_id SERIAL NOT NULL,
 202     primary key (phenstatement_id),
 203     genotype_id INT NOT NULL,
 204     FOREIGN KEY (genotype_id) REFERENCES genotype (genotype_id) ON DELETE CASCADE,
 205     environment_id INT NOT NULL,
 206     FOREIGN KEY (environment_id) REFERENCES environment (environment_id) ON DELETE CASCADE,
 207     phenotype_id INT NOT NULL,
 208     FOREIGN KEY (phenotype_id) REFERENCES phenotype (phenotype_id) ON DELETE CASCADE,
 209     type_id INT NOT NULL,
 210     FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id) ON DELETE CASCADE,
 211     pub_id INT NOT NULL,
 212     FOREIGN KEY (pub_id) REFERENCES pub (pub_id) ON DELETE CASCADE,
 213     CONSTRAINT phenstatement_c1 UNIQUE (genotype_id,phenotype_id,environment_id,type_id,pub_id)
 214 );
 215 CREATE INDEX phenstatement_idx1 ON phenstatement (genotype_id);
 216 CREATE INDEX phenstatement_idx2 ON phenstatement (phenotype_id);
 217
 218 COMMENT ON TABLE phenstatement IS 'Phenotypes are things like "larval lethal".  Phenstatements are things like "dpp-1 is recessive larval lethal". So essentially phenstatement is a linking table expressing the relationship between genotype, environment, and phenotype.';
 219
 220 -- ================================================
 221 -- TABLE: phendesc
 222 -- ================================================
 223 CREATE TABLE phendesc (
 224     phendesc_id SERIAL NOT NULL,
 225     primary key (phendesc_id),
 226     genotype_id INT NOT NULL,
 227     FOREIGN KEY (genotype_id) REFERENCES genotype (genotype_id) ON DELETE CASCADE,
 228     environment_id INT NOT NULL,
 229     FOREIGN KEY (environment_id) REFERENCES environment ( environment_id) ON DELETE CASCADE,
 230     description TEXT NOT NULL,
 231     type_id INT NOT NULL,
 232         FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id) ON DELETE CASCADE,
 233     pub_id INT NOT NULL,
 234     FOREIGN KEY (pub_id) REFERENCES pub (pub_id) ON DELETE CASCADE,
 235     CONSTRAINT phendesc_c1 UNIQUE (genotype_id,environment_id,type_id,pub_id)
 236 );
 237 CREATE INDEX phendesc_idx1 ON phendesc (genotype_id);
 238 CREATE INDEX phendesc_idx2 ON phendesc (environment_id);
 239 CREATE INDEX phendesc_idx3 ON phendesc (pub_id);
 240
 241 COMMENT ON TABLE phendesc IS 'A summary of a _set_ of phenotypic statements for any one gcontext made in any one publication.';
 242
 243 -- ================================================
 244 -- TABLE: phenotype_comparison
 245 -- ================================================
 246 CREATE TABLE phenotype_comparison (
 247     phenotype_comparison_id SERIAL NOT NULL,
 248     primary key (phenotype_comparison_id),
 249     genotype1_id INT NOT NULL,
 250         FOREIGN KEY (genotype1_id) REFERENCES genotype (genotype_id) ON DELETE CASCADE,
 251     environment1_id INT NOT NULL,
 252         FOREIGN KEY (environment1_id) REFERENCES environment (environment_id) ON DELETE CASCADE,
 253     genotype2_id INT NOT NULL,
 254         FOREIGN KEY (genotype2_id) REFERENCES genotype (genotype_id) ON DELETE CASCADE,
 255     environment2_id INT NOT NULL,
 256         FOREIGN KEY (environment2_id) REFERENCES environment (environment_id) ON DELETE CASCADE,
 257     phenotype1_id INT NOT NULL,
 258         FOREIGN KEY (phenotype1_id) REFERENCES phenotype (phenotype_id) ON DELETE CASCADE,
 259     phenotype2_id INT,
 260         FOREIGN KEY (phenotype2_id) REFERENCES phenotype (phenotype_id) ON DELETE CASCADE,
 261     pub_id INT NOT NULL,
 262     FOREIGN KEY (pub_id) REFERENCES pub (pub_id) ON DELETE CASCADE,
 263     organism_id INT NOT NULL,
 264     FOREIGN KEY (organism_id) REFERENCES organism (organism_id) ON DELETE CASCADE,
 265     CONSTRAINT phenotype_comparison_c1 UNIQUE (genotype1_id,environment1_id,genotype2_id,environment2_id,phenotype1_id,pub_id)
 266 );
 267 CREATE INDEX phenotype_comparison_idx1 on phenotype_comparison (genotype1_id);
 268 CREATE INDEX phenotype_comparison_idx2 on phenotype_comparison (genotype2_id);
 269 CREATE INDEX phenotype_comparison_idx4 on phenotype_comparison (pub_id);
 270
 271 COMMENT ON TABLE phenotype_comparison IS 'Comparison of phenotypes e.g., genotype1/environment1/phenotype1 "non-suppressible" with respect to genotype2/environment2/phenotype2.';
 272
 273 -- ================================================
 274 -- TABLE: phenotype_comparison_cvterm
 275 -- ================================================
 276 CREATE TABLE phenotype_comparison_cvterm (
 277     phenotype_comparison_cvterm_id serial not null,
 278     primary key (phenotype_comparison_cvterm_id),
 279     phenotype_comparison_id int not null,
 280     FOREIGN KEY (phenotype_comparison_id) references phenotype_comparison (phenotype_comparison_id) on delete cascade,
 281     cvterm_id int not null,
 282     FOREIGN KEY (cvterm_id) references cvterm (cvterm_id) on delete cascade,
 283     pub_id INT not null,
 284     FOREIGN KEY (pub_id) references pub (pub_id) on delete cascade,
 285     rank int not null default 0,
 286     CONSTRAINT phenotype_comparison_cvterm_c1 unique (phenotype_comparison_id, cvterm_id)
 287 );
 288 CREATE INDEX phenotype_comparison_cvterm_idx1 on phenotype_comparison_cvterm (phenotype_comparison_id);
 289 CREATE INDEX  phenotype_comparison_cvterm_idx2 on phenotype_comparison_cvterm (cvterm_id);
 290
 291
 292 EOSQL
 293
 294
 295     print "Granting permissions to web_user...\n";
 296     foreach my $table (@tables) {
 297         my $seq = $table . "_" . $table . "_id_seq";
 298
 299         $self->dbh->do("GRANT SELECT, INSERT, UPDATE ON $table to web_usr;");
 300         $self->dbh->do("GRANT SELECT, USAGE ON $seq to web_usr;");
 301
 302     }
 303     print "You're done!\n";
 304
 305 }
 306
 307
 308 return 1;