db/00005/CreateSnpTable.pm

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6  CreateSnpTable.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 patch for creating a new sgn.snp table for storing snps ...
  18 Motivation is the new genotyping data from the SolCAP project
  19
  20 This subclass uses L<Moose>. The parent class uses L<MooseX::Runnable>
  21
  22 =head1 AUTHOR
  23
  24  Naama Menda<nm249@cornell.edu>
  25
  26 =head1 COPYRIGHT & LICENSE
  27
  28     Copyright 2010 Boyce Thompson Institute for Plant Research
  29
  30 This program is free software; you can redistribute it and/or modify
  31 it under the same terms as Perl itself.
  32
  33 =cut
  34
  35
  36 package CreateSnpTable;
  37
  38 use Moose;
  39 extends 'CXGN::Metadata::Dbpatch';
  40
  41 use Bio::Chado::Schema;
  42
  43 sub init_patch {
  44     my $self=shift;
  45     my $name = __PACKAGE__;
  46     print "dbpatch name is ':" .  $name . "\n\n";
  47     my $description = 'Adding new sgn snp table';
  48     my @previous_requested_patches = (); #ADD HERE
  49     $self->name($name);
  50     $self->description($description);
  51     $self->prereq(\@previous_requested_patches);
  52 }
  53
  54 sub patch {
  55     my $self=shift;
  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     print STDOUT "\nExecuting the SQL commands.\n";
  62
  63     $self->dbh->do(<<EOSQL);
  64 --do your SQL here
  65 --
  66 CREATE TABLE sgn.snp (
  67     snp_id serial PRIMARY KEY,
  68     marker_id integer REFERENCES sgn.marker(marker_id),
  69     reference_nucleotide varchar(4),
  70     snp_nucleotide varchar(4) NOT NULL,
  71     confirmed boolean DEFAULT false,
  72     sequence_left_id integer REFERENCES sgn.sequence(sequence_id),
  73     sequence_right_id integer REFERENCES sgn.sequence(sequence_id),
  74     reference_stock_id integer  REFERENCES public.stock(stock_id),
  75     stock_id integer NOT NULL REFERENCES public.stock(stock_id),
  76     metadata_id integer REFERENCES metadata.md_metadata(metadata_id)
  77 );
  78 CREATE TABLE sgn.snpprop (
  79     snpprop_id serial PRIMARY KEY,
  80     snp_id integer REFERENCES sgn.snp(snp_id),
  81     value varchar(255) NOT NULL,
  82     rank integer,
  83     type_id integer REFERENCES public.cvterm(cvterm_id)
  84 );
  85
  86 --table snp_file
  87 CREATE TABLE sgn.snp_file (
  88     snp_file_id serial PRIMARY KEY,
  89     snp_id integer NOT NULL REFERENCES sgn.snp(snp_id),
  90     file_id integer NOT NULL REFERENCES metadata.md_files(file_id)
  91 );
  92
  93 --grant permissions to web_usr
  94  grant SELECT  on sgn.snp to web_usr ;
  95  grant SELECT  on sgn.snp_snp_id_seq TO  web_usr ;
  96
  97  grant SELECT  on sgn.snpprop to web_usr ;
  98  grant SELECT  on sgn.snpprop_snpprop_id_seq TO  web_usr ;
  99
 100  grant SELECT  on sgn.snp_file to web_usr ;
 101  grant SELECT  on sgn.snp_file_snp_file_id_seq TO  web_usr ;
 102
 103 EOSQL
 104
 105     print "You're done!\n";
 106
 107 }
 108
 109
 110 ####
 111 1; #
 112 ####
 113