db/LoadChadoStock.pm

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6  LoadChadoStock.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 test dummy patch.
  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 LoadChadoStock;
  35
  36 use Moose;
  37 extends 'CXGN::Metadata::Dbpatch';
  38
  39
  40 sub init_patch {
  41     my $self=shift;
  42     my $name = __PACKAGE__;
  43     print "dbpatch name is ':" .  $name . "\n\n";
  44     my $description = 'Loading the chado stock module';
  45     my @previous_requested_patches = ('LoadChadoGenotype', 'LoadChadoContact'); #ADD HERE
  46
  47     $self->name($name);
  48     $self->description($description);
  49     $self->prereq(\@previous_requested_patches);
  50
  51 }
  52
  53 sub patch {
  54     my $self=shift;
  55
  56
  57     print STDOUT "Executing the patch:\n " .   $self->name . ".\n\nDescription:\n  ".  $self->description . ".\n\nExecuted by:\n " .  $self->username . " .";
  58
  59     print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
  60
  61
  62
  63     print STDOUT "\nExecuting the SQL commands.\n";
  64
  65     my @tables = (
  66         qw /
  67 stock
  68 stock_pub stockprop
  69 stockprop_pub
  70 stock_relationship
  71 stock_relationship_pub
  72 stock_dbxref
  73 stock_cvterm
  74 stock_genotype
  75 stockcollection
  76 stockcollectionprop
  77 stockcollection_stock
  78 / );
  79
  80     $self->dbh->do(<<EOSQL);
  81 --do your SQL here
  82 --
  83
  84 SET SEARCH_PATH TO public;
  85
  86 -- Id: stock.sql,v 1.7 2007-03-23 15:18:03 scottcain Exp $
  87 -- ==========================================
  88 -- Chado stock module
  89 --
  90 -- DEPENDENCIES
  91 -- ============
  92 -- :import cvterm from cv
  93 -- :import pub from pub
  94 -- :import dbxref from general
  95 -- :import organism from organism
  96 -- :import genotype from genetic
  97 -- :import contact from contact
  98
  99 -- ================================================
 100 -- TABLE: stock
 101 -- ================================================
 102
 103 create table stock (
 104        stock_id serial not null,
 105        primary key (stock_id),
 106        dbxref_id int,
 107        foreign key (dbxref_id) references dbxref (dbxref_id) on delete set null INITIALLY DEFERRED,
 108        organism_id int,
 109        foreign key (organism_id) references organism (organism_id) on delete cascade INITIALLY DEFERRED,
 110        name varchar(255),
 111        uniquename text not null,
 112        description text,
 113        type_id int not null,
 114        foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
 115        is_obsolete boolean not null default 'false',
 116        constraint stock_c1 unique (organism_id,uniquename,type_id)
 117 );
 118 create index stock_name_ind1 on stock (name);
 119 create index stock_idx1 on stock (dbxref_id);
 120 create index stock_idx2 on stock (organism_id);
 121 create index stock_idx3 on stock (type_id);
 122 create index stock_idx4 on stock (uniquename);
 123
 124 COMMENT ON TABLE stock IS 'Any stock can be globally identified by the
 125 combination of organism, uniquename and stock type. A stock is the physical entities, either living or preserved, held by collections. Stocks belong to a collection; they have IDs, type, organism, description and may have a genotype.';
 126 COMMENT ON COLUMN stock.dbxref_id IS 'The dbxref_id is an optional primary stable identifier for this stock. Secondary indentifiers and external dbxrefs go in table: stock_dbxref.';
 127 COMMENT ON COLUMN stock.organism_id IS 'The organism_id is the organism to which the stock belongs. This column should only be left blank if the organism cannot be determined.';
 128 COMMENT ON COLUMN stock.type_id IS 'The type_id foreign key links to a controlled vocabulary of stock types. The would include living stock, genomic DNA, preserved specimen. Secondary cvterms for stocks would go in stock_cvterm.';
 129 COMMENT ON COLUMN stock.description IS 'The description is the genetic description provided in the stock list.';
 130 COMMENT ON COLUMN stock.name IS 'The name is a human-readable local name for a stock.';
 131
 132
 133 -- ================================================
 134 -- TABLE: stock_pub
 135 -- ================================================
 136
 137 create table stock_pub (
 138        stock_pub_id serial not null,
 139        primary key (stock_pub_id),
 140        stock_id int not null,
 141        foreign key (stock_id) references stock (stock_id)  on delete cascade INITIALLY DEFERRED,
 142        pub_id int not null,
 143        foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
 144        constraint stock_pub_c1 unique (stock_id,pub_id)
 145 );
 146 create index stock_pub_idx1 on stock_pub (stock_id);
 147 create index stock_pub_idx2 on stock_pub (pub_id);
 148
 149 COMMENT ON TABLE stock_pub IS 'Provenance. Linking table between stocks and, for example, a stocklist computer file.';
 150
 151
 152 -- ================================================
 153 -- TABLE: stockprop
 154 -- ================================================
 155
 156 create table stockprop (
 157        stockprop_id serial not null,
 158        primary key (stockprop_id),
 159        stock_id int not null,
 160        foreign key (stock_id) references stock (stock_id) on delete cascade INITIALLY DEFERRED,
 161        type_id int not null,
 162        foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
 163        value text null,
 164        rank int not null default 0,
 165        constraint stockprop_c1 unique (stock_id,type_id,rank)
 166 );
 167 create index stockprop_idx1 on stockprop (stock_id);
 168 create index stockprop_idx2 on stockprop (type_id);
 169
 170 COMMENT ON TABLE stockprop IS 'A stock can have any number of
 171 slot-value property tags attached to it. This is an alternative to
 172 hardcoding a list of columns in the relational schema, and is
 173 completely extensible. There is a unique constraint, stockprop_c1, for
 174 the combination of stock_id, rank, and type_id. Multivalued property-value pairs must be differentiated by rank.';
 175
 176
 177 -- ================================================
 178 -- TABLE: stockprop_pub
 179 -- ================================================
 180
 181 create table stockprop_pub (
 182      stockprop_pub_id serial not null,
 183      primary key (stockprop_pub_id),
 184      stockprop_id int not null,
 185      foreign key (stockprop_id) references stockprop (stockprop_id) on delete cascade INITIALLY DEFERRED,
 186      pub_id int not null,
 187      foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
 188      constraint stockprop_pub_c1 unique (stockprop_id,pub_id)
 189 );
 190 create index stockprop_pub_idx1 on stockprop_pub (stockprop_id);
 191 create index stockprop_pub_idx2 on stockprop_pub (pub_id);
 192
 193 COMMENT ON TABLE stockprop_pub IS 'Provenance. Any stockprop assignment can optionally be supported by a publication.';
 194
 195
 196 -- ================================================
 197 -- TABLE: stock_relationship
 198 -- ================================================
 199
 200 create table stock_relationship (
 201        stock_relationship_id serial not null,
 202        primary key (stock_relationship_id),
 203        subject_id int not null,
 204        foreign key (subject_id) references stock (stock_id) on delete cascade INITIALLY DEFERRED,
 205        object_id int not null,
 206        foreign key (object_id) references stock (stock_id) on delete cascade INITIALLY DEFERRED,
 207        type_id int not null,
 208        foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
 209        value text null,
 210        rank int not null default 0,
 211        constraint stock_relationship_c1 unique (subject_id,object_id,type_id,rank)
 212 );
 213 create index stock_relationship_idx1 on stock_relationship (subject_id);
 214 create index stock_relationship_idx2 on stock_relationship (object_id);
 215 create index stock_relationship_idx3 on stock_relationship (type_id);
 216
 217 COMMENT ON COLUMN stock_relationship.subject_id IS 'stock_relationship.subject_id is the subject of the subj-predicate-obj sentence. This is typically the substock.';
 218 COMMENT ON COLUMN stock_relationship.object_id IS 'stock_relationship.object_id is the object of the subj-predicate-obj sentence. This is typically the container stock.';
 219 COMMENT ON COLUMN stock_relationship.type_id IS 'stock_relationship.type_id is relationship type between subject and object. This is a cvterm, typically from the OBO relationship ontology, although other relationship types are allowed.';
 220 COMMENT ON COLUMN stock_relationship.rank IS 'stock_relationship.rank is the ordering of subject stocks with respect to the object stock may be important where rank is used to order these; starts from zero.';
 221 COMMENT ON COLUMN stock_relationship.value IS 'stock_relationship.value is for additional notes or comments.';
 222
 223
 224
 225 -- ================================================
 226 -- TABLE: stock_relationship_cvterm
 227 -- ================================================
 228
 229 CREATE TABLE stock_relationship_cvterm (
 230         stock_relationship_cvterm_id SERIAL NOT NULL,
 231         PRIMARY KEY (stock_relationship_cvterm_id),
 232         stock_relatiohship_id integer NOT NULL,
 233         --FOREIGN KEY (stock_relationship_id) references stock_relationship (stock_relationship_id) ON DELETE CASCADE INITIALLY DEFERRED,
 234         cvterm_id integer NOT NULL,
 235         FOREIGN KEY (cvterm_id) REFERENCES cvterm (cvterm_id) ON DELETE RESTRICT,
 236         pub_id integer,
 237         FOREIGN KEY (pub_id) REFERENCES pub (pub_id) ON DELETE RESTRICT
 238 );
 239 COMMENT ON TABLE stock_relationship_cvterm is 'For germplasm maintenance and pedigree data, stock_relationship. type_id will record cvterms such as "is a female parent of", "a parent for mutation", "is a group_id of", "is a source_id of", etc The cvterms for higher categories such as "generative", "derivative" or "maintenance" can be stored in table stock_relationship_cvterm';
 240
 241
 242 -- ================================================
 243 -- TABLE: stock_relationship_pub
 244 -- ================================================
 245
 246 create table stock_relationship_pub (
 247       stock_relationship_pub_id serial not null,
 248       primary key (stock_relationship_pub_id),
 249       stock_relationship_id integer not null,
 250       foreign key (stock_relationship_id) references stock_relationship (stock_relationship_id) on delete cascade INITIALLY DEFERRED,
 251       pub_id int not null,
 252       foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
 253       constraint stock_relationship_pub_c1 unique (stock_relationship_id,pub_id)
 254 );
 255 create index stock_relationship_pub_idx1 on stock_relationship_pub (stock_relationship_id);
 256 create index stock_relationship_pub_idx2 on stock_relationship_pub (pub_id);
 257
 258 COMMENT ON TABLE stock_relationship_pub IS 'Provenance. Attach optional evidence to a stock_relationship in the form of a publication.';
 259
 260
 261 -- ================================================
 262 -- TABLE: stock_dbxref
 263 -- ================================================
 264
 265 create table stock_dbxref (
 266      stock_dbxref_id serial not null,
 267      primary key (stock_dbxref_id),
 268      stock_id int not null,
 269      foreign key (stock_id) references stock (stock_id) on delete cascade INITIALLY DEFERRED,
 270      dbxref_id int not null,
 271      foreign key (dbxref_id) references dbxref (dbxref_id) on delete cascade INITIALLY DEFERRED,
 272      is_current boolean not null default 'true',
 273      constraint stock_dbxref_c1 unique (stock_id,dbxref_id)
 274 );
 275 create index stock_dbxref_idx1 on stock_dbxref (stock_id);
 276 create index stock_dbxref_idx2 on stock_dbxref (dbxref_id);
 277
 278 COMMENT ON TABLE stock_dbxref IS 'stock_dbxref links a stock to dbxrefs. This is for secondary identifiers; primary identifiers should use stock.dbxref_id.';
 279 COMMENT ON COLUMN stock_dbxref.is_current IS 'The is_current boolean indicates whether the linked dbxref is the current -official- dbxref for the linked stock.';
 280
 281
 282 -- ================================================
 283 -- TABLE: stock_cvterm
 284 -- ================================================
 285
 286 create table stock_cvterm (
 287      stock_cvterm_id serial not null,
 288      primary key (stock_cvterm_id),
 289      stock_id int not null,
 290      foreign key (stock_id) references stock (stock_id) on delete cascade INITIALLY DEFERRED,
 291      cvterm_id int not null,
 292      foreign key (cvterm_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
 293      pub_id int not null,
 294      foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
 295      constraint stock_cvterm_c1 unique (stock_id,cvterm_id,pub_id)
 296 );
 297 create index stock_cvterm_idx1 on stock_cvterm (stock_id);
 298 create index stock_cvterm_idx2 on stock_cvterm (cvterm_id);
 299 create index stock_cvterm_idx3 on stock_cvterm (pub_id);
 300
 301 COMMENT ON TABLE stock_cvterm IS 'stock_cvterm links a stock to cvterms. This is for secondary cvterms; primary cvterms should use stock.type_id.';
 302
 303
 304 -- ================================================
 305 -- TABLE: stock_genotype
 306 -- ================================================
 307
 308 create table stock_genotype (
 309        stock_genotype_id serial not null,
 310        primary key (stock_genotype_id),
 311        stock_id int not null,
 312        foreign key (stock_id) references stock (stock_id) on delete cascade,
 313        genotype_id int not null,
 314        foreign key (genotype_id) references genotype (genotype_id) on delete cascade,
 315        constraint stock_genotype_c1 unique (stock_id, genotype_id)
 316 );
 317 create index stock_genotype_idx1 on stock_genotype (stock_id);
 318 create index stock_genotype_idx2 on stock_genotype (genotype_id);
 319
 320 COMMENT ON TABLE stock_genotype IS 'Simple table linking a stock to
 321 a genotype. Features with genotypes can be linked to stocks thru feature_genotype -> genotype -> stock_genotype -> stock.';
 322
 323
 324 -- ================================================
 325 -- TABLE: stockcollection
 326 -- ================================================
 327
 328 create table stockcollection (
 329         stockcollection_id serial not null,
 330         primary key (stockcollection_id),
 331         type_id int not null,
 332         foreign key (type_id) references cvterm (cvterm_id) on delete cascade,
 333         contact_id int null,
 334         foreign key (contact_id) references contact (contact_id) on delete set null INITIALLY DEFERRED,
 335         name varchar(255),
 336         uniquename text not null,
 337         constraint stockcollection_c1 unique (uniquename,type_id)
 338 );
 339 create index stockcollection_name_ind1 on stockcollection (name);
 340 create index stockcollection_idx1 on stockcollection (contact_id);
 341 create index stockcollection_idx2 on stockcollection (type_id);
 342 create index stockcollection_idx3 on stockcollection (uniquename);
 343
 344 COMMENT ON TABLE stockcollection IS 'The lab or stock center distributing the stocks in their collection.';
 345 COMMENT ON COLUMN stockcollection.uniquename IS 'uniqename is the value of the collection cv.';
 346 COMMENT ON COLUMN stockcollection.type_id IS 'type_id is the collection type cv.';
 347 COMMENT ON COLUMN stockcollection.name IS 'name is the collection.';
 348 COMMENT ON COLUMN stockcollection.contact_id IS 'contact_id links to the contact information for the collection.';
 349
 350
 351 -- ================================================
 352 -- TABLE: stockcollectionprop
 353 -- ================================================
 354
 355 create table stockcollectionprop (
 356     stockcollectionprop_id serial not null,
 357     primary key (stockcollectionprop_id),
 358     stockcollection_id int not null,
 359     foreign key (stockcollection_id) references stockcollection (stockcollection_id) on delete cascade INITIALLY DEFERRED,
 360     type_id int not null,
 361     foreign key (type_id) references cvterm (cvterm_id),
 362     value text null,
 363     rank int not null default 0,
 364     constraint stockcollectionprop_c1 unique (stockcollection_id,type_id,rank)
 365 );
 366 create index stockcollectionprop_idx1 on stockcollectionprop (stockcollection_id);
 367 create index stockcollectionprop_idx2 on stockcollectionprop (type_id);
 368
 369 COMMENT ON TABLE stockcollectionprop IS 'The table stockcollectionprop
 370 contains the value of the stock collection such as website/email URLs;
 371 the value of the stock collection order URLs.';
 372 COMMENT ON COLUMN stockcollectionprop.type_id IS 'The cv for the type_id is "stockcollection property type".';
 373
 374
 375 -- ================================================
 376 -- TABLE: stockcollection_stock
 377 -- ================================================
 378
 379 create table stockcollection_stock (
 380     stockcollection_stock_id serial not null,
 381     primary key (stockcollection_stock_id),
 382     stockcollection_id int not null,
 383     foreign key (stockcollection_id) references stockcollection (stockcollection_id) on delete cascade INITIALLY DEFERRED,
 384     stock_id int not null,
 385     foreign key (stock_id) references stock (stock_id) on delete cascade INITIALLY DEFERRED,
 386     constraint stockcollection_stock_c1 unique (stockcollection_id,stock_id)
 387 );
 388 create index stockcollection_stock_idx1 on stockcollection_stock (stockcollection_id);
 389 create index stockcollection_stock_idx2 on stockcollection_stock (stock_id);
 390
 391 COMMENT ON TABLE stockcollection_stock IS 'stockcollection_stock links
 392 a stock collection to the stocks which are contained in the collection.';
 393
 394 -- ================================================
 395 -- TABLE: stock_dbxrefprop
 396 -- ================================================
 397
 398 create table stock_dbxrefprop (
 399        stock_dbxrefprop_id serial not null,
 400        primary key (stock_dbxrefprop_id),
 401        stock_dbxref_id int not null,
 402        foreign key (stock_dbxref_id) references stock_dbxref (stock_dbxref_id) on delete cascade INITIALLY DEFERRED,
 403        type_id int not null,
 404        foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
 405        value text null,
 406        rank int not null default 0,
 407        constraint stock_dbxrefprop_c1 unique (stock_dbxref_id,type_id,rank)
 408 );
 409     create index stock_dbxrefprop_idx1 on stock_dbxrefprop (stock_dbxref_id);
 410     create index stock_dbxrefprop_idx2 on stock_dbxrefprop (type_id);
 411
 412 COMMENT ON TABLE stock_dbxrefprop IS 'A stock_dbxref can have any number of
 413 slot-value property tags attached to it. This is useful for storing properties related to dbxref annotations of stocks, such as evidence codes, and references, and metadata, such as create/modify dates. This is an alternative to
 414 hardcoding a list of columns in the relational schema, and is
 415 completely extensible. There is a unique constraint, stock_dbxrefprop_c1, for
 416 the combination of stock_dbxref_id, rank, and type_id. Multivalued property-value pairs must be differentiated by rank.';
 417
 418
 419 EOSQL
 420
 421     print "Granting permissions to web_user...";
 422     foreach my $table (@tables) {
 423         my $seq = $table . "_" . $table . "_id_seq";
 424
 425         $self->dbh->do("GRANT SELECT, INSERT, UPDATE ON $table to web_usr;");
 426         $self->dbh->do("GRANT SELECT, USAGE ON $seq to web_usr;");
 427     }
 428     print "You're done!\n";
 429
 430 }
 431
 432
 433 ####
 434 1; #
 435 ####