db/00157/AlterDescriptionToText.pm

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6  AlterDescriptionToText.pm
   7
   8 =head1 SYNOPSIS
   9
  10 mx-run AlterDescriptionToText [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 Alters project table description column from varchar(255) to text
  18
  19 This subclass uses L<Moose>. The parent class uses L<MooseX::Runnable>
  20
  21 =head1 AUTHOR
  22
  23 Bryan Ellerbrock<bje24@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 AlterDescriptionToText;
  36
  37 use Moose;
  38 extends 'CXGN::Metadata::Dbpatch';
  39
  40
  41 has '+description' => ( default => <<'' );
  42 Alters project table description column from varchar(255) to text
  43 has '+prereq' => (
  44     default => sub {
  45         [''],
  46     },
  47   );
  48
  49 sub patch {
  50     my $self=shift;
  51
  52     print STDOUT "Executing the patch:\n " .   $self->name . ".\n\nDescription:\n  ".  $self->description . ".\n\nExecuted by:\n " .  $self->username . " .";
  53
  54     print STDOUT "\nChecking if this db_patch was executed before or if previous db_patches have been executed.\n";
  55
  56     print STDOUT "\nExecuting the SQL commands.\n";
  57
  58     $self->dbh->do(<<EOSQL);
  59 --do your SQL here
  60 /*
  61 Running just "ALTER TABLE project ALTER COLUMN description TYPE text"
  62 produces
  63
  64 ERROR:  cannot alter type of a column used by a view or rule
  65 DETAIL:  rule _RETURN on materialized view materialized_phenotype_jsonb_table depends on column "description"
  66
  67 so the following function drops the view, does the edit, then restores the view
  68 */
  69
  70 do \$\$
  71     DECLARE phenotype_jsonb_def text;
  72     DECLARE exec_text text;
  73 begin
  74     phenotype_jsonb_def = pg_get_viewdef('materialized_phenotype_jsonb_table');
  75     DROP MATERIALIZED VIEW public.materialized_phenotype_jsonb_table;
  76
  77     ALTER TABLE project ALTER COLUMN description TYPE text;
  78
  79     exec_text = FORMAT('CREATE MATERIALIZED VIEW public.materialized_phenotype_jsonb_table AS %s',
  80       phenotype_jsonb_def);
  81     EXECUTE exec_text;
  82     CREATE UNIQUE INDEX materialized_phenotype_jsonb_table_obsunit_stock_idx ON public.materialized_phenotype_jsonb_table(observationunit_stock_id) WITH (fillfactor=100);
  83     CREATE INDEX materialized_phenotype_jsonb_table_obsunit_uniquename_idx ON public.materialized_phenotype_jsonb_table(observationunit_uniquename) WITH (fillfactor=100);
  84     CREATE INDEX materialized_phenotype_jsonb_table_germplasm_stock_idx ON public.materialized_phenotype_jsonb_table(germplasm_stock_id) WITH (fillfactor=100);
  85     CREATE INDEX materialized_phenotype_jsonb_table_germplasm_uniquename_idx ON public.materialized_phenotype_jsonb_table(germplasm_uniquename) WITH (fillfactor=100);
  86     CREATE INDEX materialized_phenotype_jsonb_table_trial_idx ON public.materialized_phenotype_jsonb_table(trial_id) WITH (fillfactor=100);
  87     CREATE INDEX materialized_phenotype_jsonb_table_trial_name_idx ON public.materialized_phenotype_jsonb_table(trial_name) WITH (fillfactor=100);
  88     ALTER MATERIALIZED VIEW public.materialized_phenotype_jsonb_table OWNER TO web_usr;
  89 end \$\$;
  90
  91
  92 EOSQL
  93
  94 print "You're done!\n";
  95 }
  96
  97
  98 ####
  99 1; #
 100 ####