db/LoadChadoContact.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 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 LoadChadoContact;
  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 = (); #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 contact
  69 contact_relationship
  70 / );
  71
  72         $self->dbh->do(<<EOSQL);
  73
  74 SET SEARCH_PATH  TO public;
  75 --do your SQL here
  76
  77 -- Id: contact.sql,v 1.5 2007-02-25 17:00:17 briano Exp $
  78 -- ==========================================
  79 -- Chado contact module
  80 --
  81 -- =================================================================
  82 -- Dependencies:
  83 --
  84 -- :import cvterm from cv
  85 -- =================================================================
  86
  87 -- ================================================
  88 -- TABLE: contact
  89 -- ================================================
  90
  91 create table contact (
  92     contact_id serial not null,
  93     primary key (contact_id),
  94     type_id int null,
  95     foreign key (type_id) references cvterm (cvterm_id),
  96     name varchar(255) not null,
  97     description varchar(255) null,
  98     constraint contact_c1 unique (name)
  99 );
 100
 101 COMMENT ON TABLE contact IS 'Model persons, institutes, groups, organizations, etc.';
 102 COMMENT ON COLUMN contact.type_id IS 'What type of contact is this?  E.g. "person", "lab".';
 103
 104 -- ================================================
 105 -- TABLE: contact_relationship
 106 -- ================================================
 107
 108 create table contact_relationship (
 109     contact_relationship_id serial not null,
 110     primary key (contact_relationship_id),
 111     type_id int not null,
 112     foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
 113     subject_id int not null,
 114     foreign key (subject_id) references contact (contact_id) on delete cascade INITIALLY DEFERRED,
 115     object_id int not null,
 116     foreign key (object_id) references contact (contact_id) on delete cascade INITIALLY DEFERRED,
 117     constraint contact_relationship_c1 unique (subject_id,object_id,type_id)
 118 );
 119 create index contact_relationship_idx1 on contact_relationship (type_id);
 120 create index contact_relationship_idx2 on contact_relationship (subject_id);
 121 create index contact_relationship_idx3 on contact_relationship (object_id);
 122
 123 COMMENT ON TABLE contact_relationship IS 'Model relationships between contacts';
 124 COMMENT ON COLUMN contact_relationship.subject_id IS 'The subject of the subj-predicate-obj sentence. In a DAG, this corresponds to the child node.';
 125 COMMENT ON COLUMN contact_relationship.object_id IS 'The object of the subj-predicate-obj sentence. In a DAG, this corresponds to the parent node.';
 126
 127
 128 EOSQL
 129
 130
 131     print "Granting permissions to web_user...\n";
 132     foreach my $table (@tables) {
 133         my $seq = $table . "_" . $table . "_id_seq";
 134
 135         $self->dbh->do("GRANT SELECT, INSERT, UPDATE ON $table to web_usr;");
 136         $self->dbh->do("GRANT SELECT, USAGE ON $seq to web_usr;");
 137
 138     }
 139     print "You're done!\n";
 140
 141 }
 142
 143
 144 return 1;