db/00108/AddMdJsonAndLinkingTable.pm

   1 #!/usr/bin/env perl
   2
   3
   4 =head1 NAME
   5
   6  AddMdJsonAndLinkingTable.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 subclass uses L<Moose>. The parent class uses L<MooseX::Runnable>
  18
  19 =head1 COPYRIGHT & LICENSE
  20
  21 Copyright 2010 Boyce Thompson Institute for Plant Research
  22
  23 This program is free software; you can redistribute it and/or modify
  24 it under the same terms as Perl itself.
  25
  26 =cut
  27
  28
  29 package AddMdJsonAndLinkingTable;
  30
  31 use Moose;
  32 extends 'CXGN::Metadata::Dbpatch';
  33
  34
  35 has '+description' => ( default => <<'' );
  36 Adds md_json table for storing jsonb to Metadata schema and a linking table between md_json and nd_experiment to Phenome schema
  37
  38 has '+prereq' => (
  39     default => sub {
  40         [],
  41     },
  42   );
  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     $self->dbh->do(<<EOSQL);
  54 --do your SQL here
  55 --
  56
  57 CREATE TABLE metadata.md_json (
  58     json_id SERIAL PRIMARY KEY,
  59     json_type character varying(250),
  60     json jsonb
  61 );
  62 ALTER TABLE metadata.md_json OWNER TO postgres;
  63 COMMENT ON TABLE md_json IS 'md_json is a table for storing variable json datasets and linking them to related data in other tables. For example storing nirs spectra (wavelength:value pairs) and linking to the relevant nd_experiment which in turn links to the plot and derived phenotype values.';
  64 GRANT SELECT,UPDATE,INSERT,DELETE ON metadata.md_json TO web_usr;
  65 GRANT USAGE ON md_json_json_id_seq TO web_usr;
  66
  67 CREATE TABLE phenome.nd_experiment_md_json (
  68     nd_experiment_md_json_id SERIAL PRIMARY KEY,
  69     nd_experiment_id integer REFERENCES public.nd_experiment (nd_experiment_id),
  70     json_id integer REFERENCES metadata.md_json (json_id)
  71 );
  72 ALTER TABLE phenome.nd_experiment_md_json OWNER TO postgres;
  73 GRANT SELECT,UPDATE,INSERT,DELETE ON phenome.nd_experiment_md_json TO web_usr;
  74 GRANT USAGE ON phenome.nd_experiment_md_json_nd_experiment_md_json_id_seq TO web_usr;
  75
  76
  77 EOSQL
  78
  79 print "You're done!\n";
  80 }
  81
  82
  83 ####
  84 1; #
  85 ####