db/00008/AddStockCvtermProp.pm

   1 package AddStockCvtermProp;
   2
   3 use Moose;
   4 extends 'CXGN::Metadata::Dbpatch';
   5
   6 use Bio::Chado::Schema;
   7
   8 sub init_patch {
   9
  10     my $self=shift;
  11     my $name = __PACKAGE__;
  12     print "dbpatch name is ':" .  $name . "\n\n";
  13     my $description = 'Adding rank column to stock_cvterm table, and a new stocl_cvtermprop tables';
  14     my @previous_requested_patches = (); #ADD HERE
  15     $self->name($name);
  16     $self->description($description);
  17     $self->prereq(\@previous_requested_patches);
  18 }
  19
  20 sub patch {
  21     my $self=shift;
  22
  23     print STDOUT "Executing the patch:\n " .   $self->name . ".\n\nDescription:\n  ".  $self->description . ".\n\nExecuted by:\n " .  $self->username . " .";
  24
  25     print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
  26
  27     print STDOUT "\nExecuting the SQL commands.\n";
  28
  29     $self->dbh->do( <<EOF );
  30 alter table public.stock_cvterm add rank integer not null default 0;
  31 alter table public.stock_cvterm add is_not boolean not null default false;
  32 alter table public.stock_cvterm drop constraint stock_cvterm_c1;
  33 alter table public.stock_cvterm add constraint stock_cvterm_c1 unique ( stock_id, cvterm_id, pub_id, rank );
  34
  35 create table public.stock_cvtermprop (
  36     stock_cvtermprop_id serial not null,
  37     primary key (stock_cvtermprop_id),
  38     stock_cvterm_id int not null,
  39     foreign key (stock_cvterm_id) references public.stock_cvterm (stock_cvterm_id) on delete cascade,
  40     type_id int not null,
  41     foreign key (type_id) references public.cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
  42     value text null,
  43     rank int not null default 0,
  44     constraint stock_cvtermprop_c1 unique (stock_cvterm_id,type_id,rank)
  45 );
  46 create index stock_cvtermprop_idx1 on public.stock_cvtermprop (stock_cvterm_id);
  47 create index stock_cvtermprop_idx2 on public.stock_cvtermprop (type_id);
  48
  49 COMMENT ON TABLE public.stock_cvtermprop IS 'Extensible properties for
  50 stock to cvterm associations. Examples: GO evidence codes;
  51 qualifiers; metadata such as the date on which the entry was curated
  52 and the source of the association. See the stockprop table for
  53 meanings of type_id, value and rank.';
  54
  55 COMMENT ON COLUMN public.stock_cvtermprop.type_id IS 'The name of the
  56 property/slot is a cvterm. The meaning of the property is defined in
  57 that cvterm. cvterms may come from the OBO evidence code cv.';
  58
  59 COMMENT ON COLUMN public.stock_cvtermprop.value IS 'The value of the
  60 property, represented as text. Numeric values are converted to their
  61 text representation. This is less efficient than using native database
  62 types, but is easier to query.';
  63
  64 COMMENT ON COLUMN public.stock_cvtermprop.rank IS 'Property-Value
  65 ordering. Any stock_cvterm can have multiple values for any particular
  66 property type - these are ordered in a list using rank, counting from
  67 zero. For properties that are single-valued rather than multi-valued,
  68 the default 0 value should be used.';
  69
  70     GRANT ALL on public.stock_cvtermprop to web_usr;
  71     GRANT usage on public.stock_cvtermprop_stock_cvtermprop_id_seq to web_usr;
  72 EOF
  73
  74     print "You're done!\n";
  75 }
  76
  77
  78 ####
  79 1; #
  80 ####
  81