db/LoadChadoProject.pm

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6  LoadChadoProject.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 ptch for loadin chado's contact module, which is required by the stock 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 LoadChadoProject;
  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 project linking tables';
  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 projectprop
  69 project_relationship
  70 project_contact
  71 project_pub
  72 / );
  73
  74         $self->dbh->do(<<EOSQL);
  75
  76 SET SEARCH_PATH  TO public;
  77 --do your SQL here
  78
  79
  80 -- ================================================
  81 -- TABLE: projectprop
  82 -- ================================================
  83
  84 CREATE TABLE projectprop (
  85         projectprop_id serial NOT NULL,
  86         PRIMARY KEY (projectprop_id),
  87         project_id integer NOT NULL,
  88         FOREIGN KEY (project_id) REFERENCES project (project_id) ON DELETE CASCADE,
  89         type_id integer NOT NULL,
  90         FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id) ON DELETE CASCADE,
  91         value text,
  92         rank integer not null default 0,
  93         CONSTRAINT projectprop_c1 UNIQUE (project_id, type_id, rank)
  94 );
  95
  96 -- ================================================
  97 -- TABLE: project_relationship
  98 -- ================================================
  99
 100 CREATE TABLE project_relationship (
 101         project_relationship_id serial NOT NULL,
 102         PRIMARY KEY (project_relationship_id),
 103         subject_project_id integer NOT NULL,
 104         FOREIGN KEY (subject_project_id) REFERENCES project (project_id) ON DELETE CASCADE,
 105         object_project_id integer NOT NULL,
 106         FOREIGN KEY (object_project_id) REFERENCES project (project_id) ON DELETE CASCADE,
 107         type_id integer NOT NULL,
 108         FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id) ON DELETE RESTRICT,
 109         CONSTRAINT project_relationship_c1 UNIQUE (subject_project_id, object_project_id, type_id)
 110 );
 111 COMMENT ON TABLE project_relationship IS 'A project can be composed of several smaller scale projects';
 112 COMMENT ON COLUMN project_relationship.type_id IS 'The type of relationship being stated, such as "is part of".';
 113
 114
 115 create table project_pub (
 116        project_pub_id serial not null,
 117        primary key (project_pub_id),
 118        project_id int not null,
 119        foreign key (project_id) references project (project_id) on delete cascade INITIALLY DEFERRED,
 120        pub_id int not null,
 121        foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
 122        constraint project_pub_c1 unique (project_id,pub_id)
 123 );
 124 create index project_pub_idx1 on project_pub (project_id);
 125 create index project_pub_idx2 on project_pub (pub_id);
 126
 127 COMMENT ON TABLE project_pub IS 'Linking project(s) to publication(s)';
 128
 129
 130 create table project_contact (
 131        project_contact_id serial not null,
 132        primary key (project_contact_id),
 133        project_id int not null,
 134        foreign key (project_id) references project (project_id) on delete cascade INITIALLY DEFERRED,
 135        contact_id int not null,
 136        foreign key (contact_id) references contact (contact_id) on delete cascade INITIALLY DEFERRED,
 137        constraint project_contact_c1 unique (project_id,contact_id)
 138 );
 139 create index project_contact_idx1 on project_contact (project_id);
 140 create index project_contact_idx2 on project_contact (contact_id);
 141
 142 COMMENT ON TABLE project_contact IS 'Linking project(s) to contact(s)';
 143
 144
 145 EOSQL
 146
 147
 148     print "Granting permissions to web_user...\n";
 149     foreach my $table (@tables) {
 150         my $seq = $table . "_" . $table . "_id_seq";
 151
 152         $self->dbh->do("GRANT SELECT, INSERT, UPDATE ON $table to web_usr;");
 153         $self->dbh->do("GRANT SELECT, USAGE ON $seq to web_usr;");
 154
 155     }
 156     print "You're done!\n";
 157
 158 }
 159
 160
 161 return 1;