db/00018/UpdateChadoSchema.pm

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6 UpdateChadoSchema
   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 Patch for bringing our Chado schema up to date with BCS version 0.09010
  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 2011 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 UpdateChadoSchema;
  35
  36 use Moose;
  37 extends 'CXGN::Metadata::Dbpatch';
  38
  39
  40 has '+description' => ( default => <<'' );
  41 Patch for bringing our Chado schema up to date with BCS version 0.09010
  42
  43
  44
  45 sub patch {
  46     my $self=shift;
  47
  48     print STDOUT "Executing the patch:\n " .   $self->name . ".\n\nDescription:\n  ".  $self->description . ".\n\nExecuted by:\n " .  $self->username . " .";
  49
  50     print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
  51
  52     print STDOUT "\nExecuting the SQL commands.\n";
  53
  54     $self->dbh->do(<<EOSQL);
  55 --do your SQL here
  56 --
  57
  58 ALTER TABLE stock_relationship_cvterm RENAME COLUMN stock_relatiohship_id TO stock_relationship_id ;
  59
  60 ALTER TABLE stock_relationship_cvterm ADD FOREIGN KEY (stock_relationship_id) REFERENCES stock_relationship ON DELETE CASCADE INITIALLY DEFERRED;
  61
  62 ALTER TABLE phenotype ADD COLUMN name text DEFAULT NULL;
  63
  64 ALTER TABLE nd_experimentprop ALTER COLUMN value TYPE text ;
  65
  66 ALTER TABLE nd_geolocationprop ALTER COLUMN value TYPE text ;
  67
  68 ALTER TABLE nd_protocolprop ALTER COLUMN value TYPE text ;
  69
  70 ALTER TABLE nd_reagentprop ALTER COLUMN value TYPE text ;
  71
  72 ALTER TABLE nd_experiment_stockprop ALTER COLUMN value TYPE text ;
  73
  74
  75 ALTER TABLE genotype ADD COLUMN type_id INT NOT NULL REFERENCES cvterm(cvterm_id) ON DELETE CASCADE;
  76
  77 create table public.genotypeprop (
  78 genotypeprop_id serial not null,
  79 primary key (genotypeprop_id),
  80 genotype_id int not null,
  81 foreign key (genotype_id) references genotype (genotype_id) on delete cascade INITIALLY DEFERRED,
  82 type_id int not null,
  83 foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
  84 value text null,
  85 rank int not null default 0,
  86 constraint genotypeprop_c1 unique (genotype_id,type_id,rank)
  87 );
  88 create index genotypeprop_idx1 on genotypeprop (genotype_id);
  89 create index genotypeprop_idx2 on genotypeprop (type_id);
  90
  91 create table public.cvprop (
  92     cvprop_id serial not null,
  93     primary key (cvprop_id),
  94     cv_id int not null,
  95     foreign key (cv_id) references cv (cv_id) INITIALLY DEFERRED,
  96     type_id int not null,
  97     foreign key (type_id) references cvterm (cvterm_id) INITIALLY DEFERRED,
  98     value text,
  99     rank int not null default 0,
 100     constraint cvprop_c1 unique (cv_id,type_id,rank)
 101 );
 102
 103 COMMENT ON TABLE cvprop IS 'Additional extensible properties can be attached to a cv using this table.  A notable example would be the cv version';
 104
 105 COMMENT ON COLUMN cvprop.type_id IS 'The name of the property or slot is a cvterm. The meaning of the property is defined in that cvterm.';
 106 COMMENT ON COLUMN cvprop.value IS 'The value of the property, represented as text. Numeric values are converted to their text representation.';
 107
 108 COMMENT ON COLUMN cvprop.rank IS 'Property-Value ordering. Any
 109 cv can have multiple values for any particular property type -
 110 these are ordered in a list using rank, counting from zero. For
 111 properties that are single-valued rather than multi-valued, the
 112 default 0 value should be used.';
 113
 114 create table public.chadoprop (
 115     chadoprop_id serial not null,
 116     primary key (chadoprop_id),
 117     type_id int not null,
 118     foreign key (type_id) references cvterm (cvterm_id) INITIALLY DEFERRED,
 119     value text,
 120     rank int not null default 0,
 121     constraint chadoprop_c1 unique (type_id,rank)
 122 );
 123
 124 COMMENT ON TABLE chadoprop IS 'This table is different from other prop tables in the database, as it is for storing information about the database itself, like schema version';
 125
 126 COMMENT ON COLUMN chadoprop.type_id IS 'The name of the property or slot is a cvterm. The meaning of the property is defined in that cvterm.';
 127 COMMENT ON COLUMN chadoprop.value IS 'The value of the property, represented as text. Numeric values are converted to their text representation.';
 128
 129 COMMENT ON COLUMN chadoprop.rank IS 'Property-Value ordering. Any
 130 cv can have multiple values for any particular property type -
 131 these are ordered in a list using rank, counting from zero. For
 132 properties that are single-valued rather than multi-valued, the
 133 default 0 value should be used.';
 134
 135
 136 EOSQL
 137
 138 print "You're done!\n";
 139 }
 140
 141
 142 ####
 143 1; #
 144 ####