db/LoadChadoND.pm

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6  LoadChadoContact.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 This is a patch for loadin chado's Natural diversity module.
  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 LoadChadoND;
  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 contact module';
  46     my @previous_requested_patches = ('LoadChadoGenotype' , 'LoadChadoContact', 'LoadChadoProject' , 'LoadChadoStock'); #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 nd_geolocation
  69 nd_experiment
  70 nd_experiment_project
  71 nd_experimentprop
  72 nd_experiment_pub
  73 nd_geolocationprop
  74 nd_protocol
  75 nd_reagent
  76  nd_protocol_reagent
  77 nd_protocolprop
  78 nd_experiment_stock
  79 nd_experiment_protocol
  80 nd_experiment_phenotype
  81  nd_experiment_genotype
  82 nd_reagent_relationship
  83 nd_reagentprop
  84 nd_experiment_stockprop
  85 nd_experiment_stock_dbxref
  86 nd_experiment_dbxref
  87 nd_experiment_contact
  88 / );
  89
  90         $self->dbh->do(<<EOSQL);
  91
  92 SET SEARCH_PATH  TO public;
  93 --do your SQL here
  94
  95 -- =================================================================
  96 -- Dependencies:
  97 --
  98 -- :import feature from sequence
  99 -- :import cvterm from cv
 100 -- :import pub from pub
 101 -- :import phenotype from phenotype
 102 -- :import organism from organism
 103 -- :import genotype from genetic
 104 -- :import contact from contact
 105 -- :import project from project
 106 -- :import stock from stock
 107 -- :import synonym
 108 -- =================================================================
 109
 110
 111 -- this probably needs some work, depending on how cross-database we
 112 -- want to be.  In Postgres, at least, there are much better ways to
 113 -- represent geo information.
 114
 115 CREATE TABLE nd_geolocation (
 116     nd_geolocation_id serial PRIMARY KEY NOT NULL,
 117     description character varying(255),
 118     latitude real,
 119     longitude real,
 120     geodetic_datum character varying(32),
 121     altitude real
 122 );
 123
 124 COMMENT ON TABLE nd_geolocation IS 'The geo-referencable location of the stock. NOTE: This entity is subject to change as a more general and possibly more OpenGIS-compliant geolocation module may be introduced into Chado.';
 125
 126 COMMENT ON COLUMN nd_geolocation.description IS 'A textual representation of the location, if this is the original georeference. Optional if the original georeference is available in lat/long coordinates.';
 127
 128
 129 COMMENT ON COLUMN nd_geolocation.latitude IS 'The decimal latitude coordinate of the georeference, using positive and negative sign to indicate N and S, respectively.';
 130
 131 COMMENT ON COLUMN nd_geolocation.longitude IS 'The decimal longitude coordinate of the georeference, using positive and negative sign to indicate E and W, respectively.';
 132
 133 COMMENT ON COLUMN nd_geolocation.geodetic_datum IS 'The geodetic system on which the geo-reference coordinates are based. For geo-references measured between 1984 and 2010, this will typically be WGS84.';
 134
 135 COMMENT ON COLUMN nd_geolocation.altitude IS 'The altitude (elevation) of the location in meters. If the altitude is only known as a range, this is the average, and altitude_dev will hold half of the width of the range.';
 136
 137
 138
 139 CREATE TABLE nd_experiment (
 140     nd_experiment_id serial PRIMARY KEY NOT NULL,
 141     nd_geolocation_id integer NOT NULL references nd_geolocation (nd_geolocation_id) on delete cascade INITIALLY DEFERRED,
 142     type_id integer NOT NULL references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED
 143 );
 144
 145 --
 146 --used to be nd_diversityexperiment_project
 147 --then was nd_assay_project
 148 CREATE TABLE nd_experiment_project (
 149     nd_experiment_project_id serial PRIMARY KEY NOT NULL,
 150     project_id integer not null references project (project_id) on delete cascade INITIALLY DEFERRED,
 151     nd_experiment_id integer NOT NULL references nd_experiment (nd_experiment_id) on delete cascade INITIALLY DEFERRED
 152 );
 153
 154
 155
 156 CREATE TABLE nd_experimentprop (
 157     nd_experimentprop_id serial PRIMARY KEY NOT NULL,
 158     nd_experiment_id integer NOT NULL references nd_experiment (nd_experiment_id) on delete cascade INITIALLY DEFERRED,
 159     type_id integer NOT NULL references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED ,
 160     value character varying(255) NOT NULL,
 161     rank integer NOT NULL default 0,
 162     constraint nd_experimentprop_c1 unique (nd_experiment_id,type_id,rank)
 163 );
 164
 165 create table nd_experiment_pub (
 166        nd_experiment_pub_id serial PRIMARY KEY not null,
 167        nd_experiment_id int not null,
 168        foreign key (nd_experiment_id) references nd_experiment (nd_experiment_id) on delete cascade INITIALLY DEFERRED,
 169        pub_id int not null,
 170        foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
 171        constraint nd_experiment_pub_c1 unique (nd_experiment_id,pub_id)
 172 );
 173 create index nd_experiment_pub_idx1 on nd_experiment_pub (nd_experiment_id);
 174 create index nd_experiment_pub_idx2 on nd_experiment_pub (pub_id);
 175
 176 COMMENT ON TABLE nd_experiment_pub IS 'Linking nd_experiment(s) to publication(s)';
 177
 178
 179
 180
 181 CREATE TABLE nd_geolocationprop (
 182     nd_geolocationprop_id serial PRIMARY KEY NOT NULL,
 183     nd_geolocation_id integer NOT NULL references nd_geolocation (nd_geolocation_id) on delete cascade INITIALLY DEFERRED,
 184     type_id integer NOT NULL references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
 185     value character varying(250),
 186     rank integer NOT NULL DEFAULT 0,
 187     constraint nd_geolocationprop_c1 unique (nd_geolocation_id,type_id,rank)
 188 );
 189
 190 COMMENT ON TABLE nd_geolocationprop IS 'Property/value associations for geolocations. This table can store the properties such as location and environment';
 191
 192 COMMENT ON COLUMN nd_geolocationprop.type_id IS 'The name of the property as a reference to a controlled vocabulary term.';
 193
 194 COMMENT ON COLUMN nd_geolocationprop.value IS 'The value of the property.';
 195
 196 COMMENT ON COLUMN nd_geolocationprop.rank IS 'The rank of the property value, if the property has an array of values.';
 197
 198
 199 CREATE TABLE nd_protocol (
 200     nd_protocol_id serial PRIMARY KEY  NOT NULL,
 201     name character varying(255) NOT NULL unique
 202 );
 203
 204 COMMENT ON TABLE nd_protocol IS 'A protocol can be anything that is done as part of the experiment.';
 205
 206 COMMENT ON COLUMN nd_protocol.name IS 'The protocol name.';
 207
 208 CREATE TABLE nd_reagent (
 209     nd_reagent_id serial PRIMARY KEY NOT NULL,
 210     name character varying(80) NOT NULL,
 211     type_id integer NOT NULL references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
 212     feature_id integer
 213 );
 214
 215 COMMENT ON TABLE nd_reagent IS 'A reagent such as a primer, an enzyme, an adapter oligo, a linker oligo. Reagents are used in genotyping experiments, or in any other kind of experiment.';
 216
 217 COMMENT ON COLUMN nd_reagent.name IS 'The name of the reagent. The name should be unique for a given type.';
 218
 219 COMMENT ON COLUMN nd_reagent.type_id IS 'The type of the reagent, for example linker oligomer, or forward primer.';
 220
 221 COMMENT ON COLUMN nd_reagent.feature_id IS 'If the reagent is a primer, the feature that it corresponds to. More generally, the corresponding feature for any reagent that has a sequence that maps to another sequence.';
 222
 223
 224
 225 CREATE TABLE nd_protocol_reagent (
 226     nd_protocol_reagent_id serial PRIMARY KEY NOT NULL,
 227     nd_protocol_id integer NOT NULL references nd_protocol (nd_protocol_id) on delete cascade INITIALLY DEFERRED,
 228     reagent_id integer NOT NULL references nd_reagent (nd_reagent_id) on delete cascade INITIALLY DEFERRED,
 229     type_id integer NOT NULL references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED
 230 );
 231
 232
 233 CREATE TABLE nd_protocolprop (
 234     nd_protocolprop_id serial PRIMARY KEY NOT NULL,
 235     nd_protocol_id integer NOT NULL references nd_protocol (nd_protocol_id) on delete cascade INITIALLY DEFERRED,
 236     type_id integer NOT NULL references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
 237     value character varying(255),
 238     rank integer DEFAULT 0 NOT NULL,
 239     constraint nd_protocolprop_c1 unique (nd_protocol_id,type_id,rank)
 240 );
 241
 242 COMMENT ON TABLE nd_protocolprop IS 'Property/value associations for protocol.';
 243
 244 COMMENT ON COLUMN nd_protocolprop.nd_protocol_id IS 'The protocol to which the property applies.';
 245
 246 COMMENT ON COLUMN nd_protocolprop.type_id IS 'The name of the property as a reference to a controlled vocabulary term.';
 247
 248 COMMENT ON COLUMN nd_protocolprop.value IS 'The value of the property.';
 249
 250 COMMENT ON COLUMN nd_protocolprop.rank IS 'The rank of the property value, if the property has an array of values.';
 251
 252
 253
 254 CREATE TABLE nd_experiment_stock (
 255     nd_experiment_stock_id serial PRIMARY KEY NOT NULL,
 256     nd_experiment_id integer NOT NULL references nd_experiment (nd_experiment_id) on delete cascade INITIALLY DEFERRED,
 257     stock_id integer NOT NULL references stock (stock_id)  on delete cascade INITIALLY DEFERRED,
 258     type_id integer NOT NULL references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED
 259 );
 260
 261 COMMENT ON TABLE nd_experiment_stock IS 'Part of a stock or a clone of a stock that is used in an experiment';
 262
 263
 264 COMMENT ON COLUMN nd_experiment_stock.stock_id IS 'stock used in the extraction or the corresponding stock for the clone';
 265
 266
 267 CREATE TABLE nd_experiment_protocol (
 268     nd_experiment_protocol_id serial PRIMARY KEY NOT NULL,
 269     nd_experiment_id integer NOT NULL references nd_experiment (nd_experiment_id) on delete cascade INITIALLY DEFERRED,
 270     nd_protocol_id integer NOT NULL references nd_protocol (nd_protocol_id) on delete cascade INITIALLY DEFERRED
 271 );
 272
 273 COMMENT ON TABLE nd_experiment_protocol IS 'Linking table: experiments to the protocols they involve.';
 274
 275
 276
 277
 278 CREATE TABLE nd_experiment_phenotype (
 279     nd_experiment_phenotype_id serial PRIMARY KEY NOT NULL,
 280     nd_experiment_id integer NOT NULL REFERENCES nd_experiment (nd_experiment_id) on delete cascade INITIALLY DEFERRED,
 281     phenotype_id integer NOT NULL references phenotype (phenotype_id) on delete cascade INITIALLY DEFERRED,
 282    constraint nd_experiment_phenotype_c1 unique (nd_experiment_id,phenotype_id)
 283 );
 284
 285 COMMENT ON TABLE nd_experiment_phenotype IS 'Linking table: experiments to the phenotypes they produce. There is a one-to-one relationship between an experiment and a phenotype since each phenotype record should point to one experiment. Add a new experiment_id for each phenotype record.';
 286
 287 CREATE TABLE nd_experiment_genotype (
 288     nd_experiment_genotype_id serial PRIMARY KEY NOT NULL,
 289     nd_experiment_id integer NOT NULL references nd_experiment (nd_experiment_id) on delete cascade INITIALLY DEFERRED,
 290     genotype_id integer NOT NULL references genotype (genotype_id) on delete cascade INITIALLY DEFERRED ,
 291     constraint nd_experiment_genotype_c1 unique (nd_experiment_id,genotype_id)
 292 );
 293
 294 COMMENT ON TABLE nd_experiment_genotype IS 'Linking table: experiments to the genotypes they produce. There is a one-to-one relationship between an experiment and a genotype since each genotype record should point to one experiment. Add a new experiment_id for each genotype record.';
 295
 296
 297 CREATE TABLE nd_reagent_relationship (
 298     nd_reagent_relationship_id serial PRIMARY KEY NOT NULL,
 299     subject_reagent_id integer NOT NULL references nd_reagent (nd_reagent_id) on delete cascade INITIALLY DEFERRED,
 300     object_reagent_id integer NOT NULL  references nd_reagent (nd_reagent_id) on delete cascade INITIALLY DEFERRED,
 301     type_id integer NOT NULL  references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED
 302 );
 303
 304 COMMENT ON TABLE nd_reagent_relationship IS 'Relationships between reagents. Some reagents form a group. i.e., they are used all together or not at all. Examples are adapter/linker/enzyme experiment reagents.';
 305
 306 COMMENT ON COLUMN nd_reagent_relationship.subject_reagent_id IS 'The subject reagent in the relationship. In parent/child terminology, the subject is the child. For example, in "linkerA 3prime-overhang-linker enzymeA" linkerA is the subject, 3prime-overhand-linker is the type, and enzymeA is the object.';
 307
 308 COMMENT ON COLUMN nd_reagent_relationship.object_reagent_id IS 'The object reagent in the relationship. In parent/child terminology, the object is the parent. For example, in "linkerA 3prime-overhang-linker enzymeA" linkerA is the subject, 3prime-overhand-linker is the type, and enzymeA is the object.';
 309
 310 COMMENT ON COLUMN nd_reagent_relationship.type_id IS 'The type (or predicate) of the relationship. For example, in "linkerA 3prime-overhang-linker enzymeA" linkerA is the subject, 3prime-overhand-linker is the type, and enzymeA is the object.';
 311
 312
 313 CREATE TABLE nd_reagentprop (
 314     nd_reagentprop_id serial PRIMARY KEY NOT NULL,
 315     nd_reagent_id integer NOT NULL references nd_reagent (nd_reagent_id) on delete cascade INITIALLY DEFERRED,
 316     type_id integer NOT NULL references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
 317     value character varying(255),
 318     rank integer DEFAULT 0 NOT NULL,
 319     constraint nd_reagentprop_c1 unique (nd_reagent_id,type_id,rank)
 320 );
 321
 322 CREATE TABLE nd_experiment_stockprop (
 323     nd_experiment_stockprop_id serial PRIMARY KEY NOT NULL,
 324     nd_experiment_stock_id integer NOT NULL references nd_experiment_stock (nd_experiment_stock_id) on delete cascade INITIALLY DEFERRED,
 325     type_id integer NOT NULL references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
 326     value character varying(255),
 327     rank integer DEFAULT 0 NOT NULL,
 328     constraint nd_experiment_stockprop_c1 unique (nd_experiment_stock_id,type_id,rank)
 329 );
 330
 331 COMMENT ON TABLE nd_experiment_stockprop IS 'Property/value associations for experiment_stocks. This table can store the properties such as treatment';
 332
 333 COMMENT ON COLUMN nd_experiment_stockprop.nd_experiment_stock_id IS 'The experiment_stock to which the property applies.';
 334
 335 COMMENT ON COLUMN nd_experiment_stockprop.type_id IS 'The name of the property as a reference to a controlled vocabulary term.';
 336
 337 COMMENT ON COLUMN nd_experiment_stockprop.value IS 'The value of the property.';
 338
 339 COMMENT ON COLUMN nd_experiment_stockprop.rank IS 'The rank of the property value, if the property has an array of values.';
 340
 341
 342 CREATE TABLE nd_experiment_stock_dbxref (
 343     nd_experiment_stock_dbxref_id serial PRIMARY KEY NOT NULL,
 344     nd_experiment_stock_id integer NOT NULL references nd_experiment_stock (nd_experiment_stock_id) on delete cascade INITIALLY DEFERRED,
 345     dbxref_id integer NOT NULL references dbxref (dbxref_id) on delete cascade INITIALLY DEFERRED
 346 );
 347
 348 COMMENT ON TABLE nd_experiment_stock_dbxref IS 'Cross-reference experiment_stock to accessions, images, etc';
 349
 350
 351
 352 CREATE TABLE nd_experiment_dbxref (
 353     nd_experiment_dbxref_id serial PRIMARY KEY NOT NULL,
 354     nd_experiment_id integer NOT NULL references nd_experiment (nd_experiment_id) on delete cascade INITIALLY DEFERRED,
 355     dbxref_id integer NOT NULL references dbxref (dbxref_id) on delete cascade INITIALLY DEFERRED
 356 );
 357
 358 COMMENT ON TABLE nd_experiment_dbxref IS 'Cross-reference experiment to accessions, images, etc';
 359
 360
 361 CREATE TABLE nd_experiment_contact (
 362     nd_experiment_contact_id serial PRIMARY KEY NOT NULL,
 363     nd_experiment_id integer NOT NULL references nd_experiment (nd_experiment_id) on delete cascade INITIALLY DEFERRED,
 364     contact_id integer NOT NULL references contact (contact_id) on delete cascade INITIALLY DEFERRED
 365 );
 366
 367
 368 EOSQL
 369
 370
 371     print "Granting permissions to web_user...\n";
 372     foreach my $table (@tables) {
 373         my $seq = $table . "_" . $table . "_id_seq";
 374
 375         $self->dbh->do("GRANT SELECT, INSERT, UPDATE ON $table to web_usr;");
 376         $self->dbh->do("GRANT SELECT, USAGE ON $seq to web_usr;");
 377
 378     }
 379     print "You're done!\n";
 380
 381 }
 382
 383
 384 return 1;