db/00086/AddTimestampToNdTables.pm

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6  AddTimestampToNdTables.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 Adds a column called 'create_date' to the stock, project, genotype, phenotype, and nd_protocol tables. this column is set to DEFAULT NOW() when an entry is created. This is useful for creating reports of which project, stocks, phenotypes, etc were added over different time periods.
  18
  19 This subclass uses L<Moose>. The parent class uses L<MooseX::Runnable>
  20
  21 =head1 AUTHOR
  22
  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 AddTimestampToNdTables;
  35
  36 use Moose;
  37 use Try::Tiny;
  38 extends 'CXGN::Metadata::Dbpatch';
  39
  40
  41 has '+description' => ( default => <<'' );
  42 Adds a column called 'create_date' to the nd_experiment, stock, project, genotype, phenotype, and nd_protocol tables. this column is set to DEFAULT NOW() when an entry is created. This is useful for creating reports of which project, stocks, phenotypes, etc were added over different time periods.
  43
  44 sub patch {
  45     my $self=shift;
  46
  47     print STDOUT "Executing the patch:\n " .   $self->name . ".\n\nDescription:\n  ".  $self->description . ".\n\nExecuted by:\n " .  $self->username . " .";
  48
  49     print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
  50
  51     print STDOUT "\nExecuting the SQL commands.\n";
  52
  53
  54     try {
  55         $self->dbh->do(<<EOSQL);
  56 ALTER TABLE nd_experiment ADD COLUMN create_date TIMESTAMP;
  57 ALTER TABLE nd_experiment ALTER COLUMN create_date SET DEFAULT now();
  58 EOSQL
  59     }
  60     catch {
  61         print STDOUT "nd_experiment already had create_date\n";
  62     };
  63
  64     try {
  65         $self->dbh->do(<<EOSQL);
  66 ALTER TABLE stock ADD COLUMN create_date TIMESTAMP;
  67 ALTER TABLE stock ALTER COLUMN create_date SET DEFAULT now();
  68 EOSQL
  69     }
  70     catch {
  71         print STDOUT "stock already had create_date\n";
  72     };
  73
  74     try {
  75         $self->dbh->do(<<EOSQL);
  76 ALTER TABLE project ADD COLUMN create_date TIMESTAMP;
  77 ALTER TABLE project ALTER COLUMN create_date SET DEFAULT now();
  78 EOSQL
  79     }
  80     catch {
  81         print STDOUT "project already had create_date\n";
  82     };
  83
  84     try {
  85         $self->dbh->do(<<EOSQL);
  86 ALTER TABLE phenotype ADD COLUMN create_date TIMESTAMP;
  87 ALTER TABLE phenotype ALTER COLUMN create_date SET DEFAULT now();
  88 EOSQL
  89     }
  90     catch {
  91         print STDOUT "phenotype already had create_date\n";
  92     };
  93
  94     try {
  95         $self->dbh->do(<<EOSQL);
  96 ALTER TABLE genotype ADD COLUMN create_date TIMESTAMP;
  97 ALTER TABLE genotype ALTER COLUMN create_date SET DEFAULT now();
  98 EOSQL
  99     }
 100     catch {
 101         print STDOUT "genotype already had create_date\n";
 102     };
 103
 104     try {
 105         $self->dbh->do(<<EOSQL);
 106 ALTER TABLE nd_protocol ADD COLUMN create_date TIMESTAMP;
 107 ALTER TABLE nd_protocol ALTER COLUMN create_date SET DEFAULT now();
 108 EOSQL
 109     }
 110     catch {
 111         print STDOUT "nd_protocol already had create_date\n";
 112     };
 113
 114     try {
 115         $self->dbh->do(<<EOSQL);
 116 ALTER TABLE stock_relationship ADD COLUMN create_date TIMESTAMP;
 117 ALTER TABLE stock_relationship ALTER COLUMN create_date SET DEFAULT now();
 118 EOSQL
 119     }
 120     catch {
 121         print STDOUT "stock_relationship already had create_date\n";
 122     };
 123
 124     try {
 125         $self->dbh->do(<<EOSQL);
 126 ALTER TABLE project_relationship ADD COLUMN create_date TIMESTAMP;
 127 ALTER TABLE project_relationship ALTER COLUMN create_date SET DEFAULT now();
 128 EOSQL
 129     }
 130     catch {
 131         print STDOUT "project_relationship already had create_date\n";
 132     };
 133
 134 print "You're done!\n";
 135 }
 136
 137
 138 ####
 139 1; #
 140 ####