db/00003/CreateSolcapMarkerTables.pm

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6  CreateSolcapMarkerTables.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 the necessary tables and rows to accommodate the solcap marker data. Since the SolCap markers have 2-5 primers, we cannot use pcr_experiment.primer_id (which holds only 2 primers), and need instead pcr_experiment_sequence.
  18
  19 This subclass uses L<Moose>. The parent class uses L<MooseX::Runnable>
  20
  21 =head1 AUTHOR
  22
  23  Naama Menda<nm249@cornell.edu>
  24
  25 =head1 COPYRIGHT & LICENSE
  26
  27 Copyright 2010 Boyce Thompson Institute for Plant Research
  28
  29 This program is free software; you can redistribute it and/or modify
  30 it under the same terms as Perl itself.
  31
  32 =cut
  33
  34
  35 package CreateSolcapMarkerTables;
  36
  37 use Moose;
  38 extends 'CXGN::Metadata::Dbpatch';
  39
  40 use Bio::Chado::Schema;
  41
  42 sub init_patch {
  43     my $self=shift;
  44     my $name = __PACKAGE__;
  45     print "dbpatch name is ':" .  $name . "\n\n";
  46     my $description = 'altering marker schema to accommodate SolCap markers';
  47     my @previous_requested_patches = (); #ADD HERE
  48     print "This patch requires version 1.280 of Sequence Ontology";
  49     $self->name($name);
  50     $self->description($description);
  51     $self->prereq(\@previous_requested_patches);
  52
  53 }
  54
  55 sub patch {
  56     my $self=shift;
  57
  58
  59     print STDOUT "Executing the patch:\n " .   $self->name . ".\n\nDescription:\n  ".  $self->description . ".\n\nExecuted by:\n " .  $self->username . " .";
  60
  61     print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
  62
  63
  64     print STDOUT "\nExecuting the SQL commands.\n";
  65
  66     $self->dbh->do(<<EOSQL);
  67 --do your SQL here
  68 --
  69 ALTER TABLE sgn.pcr_experiment ADD COLUMN stock_id integer REFERENCES public.stock;
  70
  71 CREATE TABLE sgn.pcr_experiment_sequence (
  72     pcr_experiment_sequence_id serial PRIMARY KEY,
  73     pcr_experiment_id integer NOT NULL REFERENCES sgn.pcr_experiment ON DELETE CASCADE,
  74     sequence_id integer NOT NULL REFERENCES sgn.sequence ON DELETE CASCADE,
  75     type_id integer REFERENCES public.cvterm(cvterm_id)
  76     );
  77
  78 --copying the pcr_experimet.primer_id columns to pcr_experiment_sequence
  79 --first create a tmp table
  80 create TEMP TABLE tmp_pcr_rev as SELECT pcr_experiment_id, primer_id_rev FROM sgn.pcr_experiment WHERE primer_id_rev is not null;
  81
  82 --populate the new table
  83 INSERT INTO sgn.pcr_experiment_sequence (pcr_experiment_id, sequence_id ) SELECT tmp_pcr_rev.* FROM tmp_pcr_rev ;
  84
  85 --update the type_id
  86  UPDATE sgn.pcr_experiment_sequence SET type_id = (SELECT cvterm_id FROM public.cvterm where name = 'reverse_primer');
  87
  88     CREATE TEMP TABLE tmp_pcr_fwd as SELECT pcr_experiment_id, primer_id_fwd FROM sgn.pcr_experiment WHERE primer_id_fwd is not null;
  89
  90     INSERT INTO sgn.pcr_experiment_sequence (pcr_experiment_id, sequence_id) SELECT tmp_pcr_fwd.* FROM tmp_pcr_fwd;
  91
  92     UPDATE sgn.pcr_experiment_sequence SET type_id =  (SELECT cvterm_id FROM public.cvterm where name = 'forward_primer') WHERE type_id is null;
  93
  94    CREATE TEMP TABLE tmp_pcr_pd as SELECT pcr_experiment_id, primer_id_fwd FROM sgn.pcr_experiment WHERE primer_id_pd is not null;
  95
  96     INSERT INTO sgn.pcr_experiment_sequence (pcr_experiment_id, sequence_id) SELECT tmp_pcr_pd.* FROM tmp_pcr_pd;
  97
  98     UPDATE sgn.pcr_experiment_sequence SET type_id =  (SELECT cvterm_id FROM public.cvterm where name ilike 'dcaps_primer') WHERE type_id is null;
  99
 100
 101 --need to refactor the code that uses pcr_experiment.primer_id_fwd/rev/pd (dCAPs?). This will be done in the topic/solcap_marker branch
 102
 103
 104 --add SNP to the trigger
 105
 106 alter table sgn.marker_experiment drop constraint marker_experiment_protocol_check;
 107
 108     alter table sgn.marker_experiment add constraint marker_experiment_protocol_check CHECK (protocol = 'AFLP'::text OR protocol = 'CAPS'::text OR protocol = 'RAPD'::text OR protocol = 'SNP'::text OR protocol = 'SSR'::text OR protocol = 'RFLP'::text OR protocol = 'PCR'::text OR protocol = 'dCAPS'::text OR protocol = 'DART'::text OR protocol = 'OPA'::text OR protocol = 'unknown'::text  OR protocol = 'ASPE'::text  OR protocol = 'INDEL'::text);
 109
 110 --grant permissions to web_usr
 111  grant SELECT  on sgn.pcr_experiment_sequence to web_usr ;
 112  grant SELECT  on sgn.pcr_experiment_sequence_pcr_experiment_sequence_id_seq TO  web_usr ;
 113
 114 EOSQL
 115
 116     print "You're done!\n";
 117
 118 }
 119
 120
 121 ####
 122 1; #
 123 ####
 124