+++ /dev/null
-#!/usr/bin/perl -w
-###############################################################################
-=pod
-
-=head1 NAME
-
-mig-mapper
-
-Interactive session for analyzing, flagging, and mapping legacy field data to
-Evergreen fields.
-
-Upon exit, generate either [file].clean.map.sql or <parent table>_map.sql. The
-SQL generated will be UPDATE's for setting the Evergreen-specific columns for a
-given file's staging tables, and TRUNCATE's and INSERT's for auxilary tables.
-The files will have \include hooks for pulling in additional mapping files
-(for example, end-user mappings for circ modifiers, etc.)
-
-=head1 SYNOPSIS
-
-B<mig-mapper> <file>
-
-=cut
-
-###############################################################################
-
-use strict;
-use Term::ReadLine;
-use Switch;
-use Env qw(
- HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
- MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
-);
-use Pod::Usage;
-use DBI;
-use Cwd 'abs_path';
-use FindBin;
-my $mig_bin = "$FindBin::Bin/";
-use lib "$FindBin::Bin/";
-use Mig;
-
-pod2usage(-verbose => 2) if ! $ARGV[0] || $ARGV[0] eq '--help';
-
-Mig::die_if_no_env_migschema();
-Mig::die_if_mig_tracking_table_does_not_exist();
-
-my $column_filter = 1; # show all fields
-my $file = abs_path($ARGV[0]);
-my $fdata;
-my $tracked_file_id = Mig::check_for_tracked_file($file);
-if ($tracked_file_id) {
- $fdata = Mig::status_this_file($file);
-} else {
- die "File not currently tracked: $file\n";
-}
-
-my $table = $fdata->{staged_table};
-if (!$table) {
- die "No staged staged table for file: $file\n";
-}
-
-my $loop = 1;
-my $term = Term::ReadLine->new('mapper');
-my $prompt;
-my $OUT = $term->OUT || \*STDOUT;
-my @dtd_identifiers;
-
-table_menu();
-$prompt = "$fdata->{staged_table}: ";
-while ( $loop && defined (my $cmd = $term->readline($prompt)) ) {
-top:
- $cmd =~ s/^\s+//;
- $cmd =~ s/\s+$//;
- $term->addhistory($cmd) if $cmd =~ /\S/;
- if ($cmd =~ /^\d+$/) {
- my $ret = column_menu($cmd);
- if ($ret) {
- $cmd = $ret;
- goto top;
- }
- } else {
- switch($cmd) {
- case /^(ls|\?|\.|;)$/ {
- table_menu();
- }
- case '' {
- table_menu();
- }
- case 'l' {
- list_ten();
- }
- case 'f1' {
- $column_filter = 1;
- table_menu();
- }
- case 'f2' {
- $column_filter = 2;
- table_menu();
- }
- case 'f3' {
- $column_filter = 3;
- table_menu();
- }
- }
- }
- $loop = 0 if $cmd =~ /^q/io;
-}
-
-exit 0;
-
-###############################################################################
-
-sub table_menu {
- print "\n=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=\n";
- print "$table";
- print "\n=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=\n";
- print "\n";
- print " l) SELECT * FROM $fdata->{staged_table} LIMIT 10;\n";
- print "f1) show all fields (default)\n";
- print "f2) show legacy fields\n";
- print "f3) show EG fields\n";
- print " q) quit\n\n";
- printf "%-36s", "Columns (* for required)";
- printf "%-30s", "Target";
- printf "%-30s", "Transform";
- printf "%-30s", "First Row";
- printf "%-30s", "Migration Note";
- print "\n";
- printf "%-36s", "-------";
- printf "%-30s", "------";
- printf "%-30s", "---------";
- printf "%-30s", "---------";
- printf "%-30s", "--------------";
- print "\n";
- my $dbh = Mig::db_connect();
- my $sth = $dbh->prepare("
- SELECT *
- FROM information_schema.columns
- WHERE table_schema = " . $dbh->quote($MIGSCHEMA) . "
- AND table_name = " . $dbh->quote($table) . "
- ORDER BY dtd_identifier::INTEGER ASC;
- ");
- my $rv = $sth->execute()
- || die "Error retrieving data from information_schema: $!";
- my $sth2 = $dbh->prepare("
- SELECT *
- FROM $MIGSCHEMA.$table
- LIMIT 1;
- ");
- my $rv2 = $sth2->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- my $row = $sth2->fetchrow_hashref;
-
- open LESS, "|less -F";
- @dtd_identifiers = ();
- while (my $data = $sth->fetchrow_hashref) {
- my $column = $data->{column_name};
- if ($column_filter == 2 && !($column =~ /^[xl]_/)) {
- next;
- }
- if ($column_filter == 3 && ($column =~ /^[xl]_/)) {
- next;
- }
- my $cdata = status_this_column($column);
- printf LESS $cdata->{required} ? '*' : ' ';
- printf LESS "%3s) ", $data->{dtd_identifier};
- push @dtd_identifiers, $data->{dtd_identifier};
- printf LESS "%-30s", $column;
- printf LESS "%-30s", defined $cdata->{target_table}
- ? ( $cdata->{target_table} ne $table ? $cdata->{target_table} . '.' : '') . $cdata->{target_column}
- : '';
- printf LESS "%-30s", defined $cdata->{transform} ? $cdata->{transform} : '';
- printf LESS "%-30s", defined $$row{$column} ? $$row{$column} : '';
- printf LESS "%-30s", defined $cdata->{comment} ? $cdata->{comment} : '';
- print LESS "\n";
- }
- close LESS;
- print "\n";
- $sth->finish;
- $sth2->finish;
- Mig::db_disconnect($dbh);
-}
-
-sub column_menu {
- my $dtd_identifier = shift;
- my $dbh = Mig::db_connect();
- my $sth = $dbh->prepare("
- SELECT *
- FROM information_schema.columns
- WHERE table_schema = " . $dbh->quote($MIGSCHEMA) . "
- AND table_name = " . $dbh->quote($table) . "
- AND dtd_identifier = " . $dbh->quote($dtd_identifier) . ";
- ");
- my $rv = $sth->execute()
- || die "Error retrieving data from information_schema: $!";
- my $data = $sth->fetchrow_hashref;
- $sth->finish;
- Mig::db_disconnect($dbh);
-
- my $column = $data->{column_name};
-
- my $prompt = "$table.$column: ";
-
- sub print_menu {
- my $column = shift;
- my $cdata = status_this_column($column);
- print "\n=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=\n";
- print "$column";
- print "\n-------------------------------------------------------------------------------------------------\n";
- print " target: " . (
- defined $cdata->{target_table}
- ? ( $cdata->{target_table} ne $table ? $cdata->{target_table} . '.' : '') . $cdata->{target_column}
- : ''
- ) . "\n";
- print "transform: " . (defined $cdata->{transform} ? $cdata->{transform} : '') . "\n";
- print " comment: " . (defined $cdata->{comment} ? $cdata->{comment} : '') . "\n";
- print "\n=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=\n";
- print "\n";
- print " l) SELECT $column FROM $fdata->{staged_table} LIMIT 10;\n";
- print " s) summarize\n" if $column ne 'x_migrate';
- print " g) group browse\n";
- print "g2) group browse (order by count desc)\n";
- print " c) comment\n";
- print " f) flag for end-user mapping\n";
- print " t) target\n";
- print " e) eval/transform\n";
- print " n) next column\n";
- print " p) prev column\n";
- print " q) quit back to table menu\n";
- print "\n";
- }
- print_menu($column);
-
- my $loop = 1;
- while ( $loop && defined (my $cmd = $term->readline($prompt)) ) {
- $cmd =~ s/^\s+//;
- $cmd =~ s/\s+$//;
- $term->addhistory($cmd) if $cmd =~ /\S/;
- $loop = 0 if $cmd =~ /^q/io;
- switch($cmd) {
- case /^(ls|\?|\.|;)$/ {
- print_menu($column);
- }
- case '' {
- print_menu($column);
- }
- case 'l' {
- list_ten($column);
- }
- case 's' {
- summarize($column);
- }
- case 'g' {
- group_browse($column);
- }
- case 'g2' {
- group_browse($column,'GROUP BY 1 ORDER BY 2 DESC');
- }
- case /^c/io {
- if ($cmd =~ /^c\s+(.+)$/) {
- set_comment($column,$1);
- }
- }
- case /^t/io {
- if ($cmd =~ /^t\s+(.+)$/) {
- set_target($column,$1);
- }
- }
- case /^e/io {
- if ($cmd =~ /^e\s+(.+)$/) {
- set_transform($column,$1);
- }
- }
- case 'n' {
- my( $index )= grep { $dtd_identifiers[$_] eq $dtd_identifier } 0..$#dtd_identifiers;
- return $dtd_identifiers[$index + 1];
- }
- case 'p' {
- my( $index )= grep { $dtd_identifiers[$_] eq $dtd_identifier } 0..$#dtd_identifiers;
- return $dtd_identifiers[$index - 1];
- }
- }
- }
-}
-
-sub list_ten {
- my $column = shift;
-
- my $dbh = Mig::db_connect();
- my $sth;
- my $rv;
- my @cols;
-
- $sth = $dbh->prepare(Mig::sql("
- SELECT " . (defined $column ? $column : '*') . "
- FROM $MIGSCHEMA.$table
- LIMIT 10;
- "));
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- print "\n";
- while (@cols = $sth->fetchrow_array) {
- print "\t" . join(',',map {defined $_ ? $_ : ''} @cols) . "\n";
- }
- print "\n";
- $sth->finish;
-}
-
-sub summarize {
- my $column = shift;
-
- my $count;
- my $non_empty_count;
- my $distinct_value_count;
- my $distinct_integer_value_count;
- my $distinct_money6_value_count;
- my $distinct_money8_value_count;
- my $distinct_date_value_count;
- my $distinct_timestamptz_value_count;
-
- my $min_value;
- my $min_length;
- my $min_length_min_value;
- my $max_value;
- my $max_length;
- my $max_length_max_value;
-
- my $min_value_as_integer;
- my $max_value_as_integer;
-
- my $min_value_as_money6;
- my $max_value_as_money6;
-
- my $min_value_as_money8;
- my $max_value_as_money8;
-
- my $min_value_as_date;
- my $max_value_as_date;
-
- my $min_value_as_timestamptz;
- my $max_value_as_timestamptz;
-
- my $dbh = Mig::db_connect();
- my $sth;
- my $rv;
- my @cols;
-
- ### count
- $sth = $dbh->prepare("
- SELECT COUNT(*)
- FROM $MIGSCHEMA.$table;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $count = $cols[0];
-
- ### non_empty_count
- $sth = $dbh->prepare("
- SELECT COUNT(*)
- FROM $MIGSCHEMA.$table
- WHERE $column IS NOT NULL AND BTRIM($column) <> '';
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $non_empty_count = $cols[0];
-
- ### distinct_value_count
- $sth = $dbh->prepare("
- SELECT COUNT(DISTINCT $column)
- FROM $MIGSCHEMA.$table;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $distinct_value_count = $cols[0];
-
- ### distinct_integer_value_count
- $sth = $dbh->prepare("
- SELECT COUNT(DISTINCT migration_tools.attempt_cast($column,'INTEGER')::INTEGER)
- FROM $MIGSCHEMA.$table;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $distinct_integer_value_count = $cols[0];
-
- ### distinct_money6_value_count
- $sth = $dbh->prepare("
- SELECT COUNT(DISTINCT NULLIF(migration_tools.attempt_money6($column,'-0.01'),-0.01))
- FROM $MIGSCHEMA.$table;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $distinct_money6_value_count = $cols[0];
-
- ### distinct_money8_value_count
- $sth = $dbh->prepare("
- SELECT COUNT(DISTINCT NULLIF(migration_tools.attempt_money($column,'-0.01'),-0.01))
- FROM $MIGSCHEMA.$table;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $distinct_money8_value_count = $cols[0];
-
- ### distinct_date_value_count
- $sth = $dbh->prepare("
- SELECT COUNT(DISTINCT NULLIF(migration_tools.attempt_date($column,'1969-06-09'),'1969-06-09'::DATE))
- FROM $MIGSCHEMA.$table;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $distinct_date_value_count = $cols[0];
-
- ### distinct_timestamptz_value_count
- $sth = $dbh->prepare("
- SELECT COUNT(DISTINCT NULLIF(migration_tools.attempt_timestamptz($column,'1969-06-09'),'1969-06-09'::TIMESTAMPTZ))
- FROM $MIGSCHEMA.$table;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $distinct_timestamptz_value_count = $cols[0];
-
- ### min_value
- $sth = $dbh->prepare("
- SELECT MIN($column)
- FROM $MIGSCHEMA.$table;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $min_value = $cols[0];
-
- ### min_length
- $sth = $dbh->prepare("
- SELECT MIN(LENGTH($column))
- FROM $MIGSCHEMA.$table;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $min_length = $cols[0];
-
- ### min_length_min_value
- $sth = $dbh->prepare("
- SELECT MIN($column)
- FROM $MIGSCHEMA.$table
- WHERE LENGTH($column) = $min_length;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $min_length_min_value = $cols[0];
-
- ### min_value_as_integer
- $sth = $dbh->prepare("
- SELECT MIN(migration_tools.attempt_cast($column,'INTEGER')::INTEGER)
- FROM $MIGSCHEMA.$table;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $min_value_as_integer = $cols[0];
-
- ### min_value_as_money6
- $sth = $dbh->prepare("
- SELECT MIN(NULLIF(migration_tools.attempt_money6($column,'-0.01'),-0.01))
- FROM $MIGSCHEMA.$table;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $min_value_as_money6 = $cols[0];
-
- ### min_value_as_money8
- $sth = $dbh->prepare("
- SELECT MIN(NULLIF(migration_tools.attempt_money($column,'-0.01'),-0.01))
- FROM $MIGSCHEMA.$table;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $min_value_as_money8 = $cols[0];
-
- ### min_value_as_date
- $sth = $dbh->prepare("
- SELECT MIN(NULLIF(migration_tools.attempt_date($column,'1969-06-09'),'1969-06-09'::DATE))
- FROM $MIGSCHEMA.$table;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $min_value_as_date = $cols[0];
-
- ### min_value_as_timestamptz
- $sth = $dbh->prepare("
- SELECT MIN(NULLIF(migration_tools.attempt_timestamptz($column,'1969-06-09'),'1969-06-09'::TIMESTAMPTZ))
- FROM $MIGSCHEMA.$table;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $min_value_as_timestamptz = $cols[0];
-
- ### max_value
- $sth = $dbh->prepare("
- SELECT MAX($column)
- FROM $MIGSCHEMA.$table;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $max_value = $cols[0];
-
- ### max_length
- $sth = $dbh->prepare("
- SELECT MAX(LENGTH($column))
- FROM $MIGSCHEMA.$table;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $max_length = $cols[0];
-
- ### max_length_max_value
- $sth = $dbh->prepare("
- SELECT MAX($column)
- FROM $MIGSCHEMA.$table
- WHERE LENGTH($column) = $max_length;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $max_length_max_value = $cols[0];
-
- ### max_value_as_integer
- $sth = $dbh->prepare("
- SELECT MAX(migration_tools.attempt_cast($column,'INTEGER')::INTEGER)
- FROM $MIGSCHEMA.$table;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $max_value_as_integer = $cols[0];
-
- ### max_value_as_money6
- $sth = $dbh->prepare("
- SELECT MAX(NULLIF(migration_tools.attempt_money6($column,'-0.01'),-0.01))
- FROM $MIGSCHEMA.$table;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $max_value_as_money6 = $cols[0];
-
- ### max_value_as_money8
- $sth = $dbh->prepare("
- SELECT MAX(NULLIF(migration_tools.attempt_money($column,'-0.01'),-0.01))
- FROM $MIGSCHEMA.$table;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $max_value_as_money8 = $cols[0];
-
- ### max_value_as_date
- $sth = $dbh->prepare("
- SELECT MAX(NULLIF(migration_tools.attempt_date($column,'1969-06-09'),'1969-06-09'::DATE))
- FROM $MIGSCHEMA.$table;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $max_value_as_date = $cols[0];
-
- ### max_value_as_timestamptz
- $sth = $dbh->prepare("
- SELECT MAX(NULLIF(migration_tools.attempt_timestamptz($column,'1969-06-09'),'1969-06-09'::TIMESTAMPTZ))
- FROM $MIGSCHEMA.$table;
- ");
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
- @cols = $sth->fetchrow_array;
- $sth->finish;
- $max_value_as_timestamptz = $cols[0];
-
- Mig::db_disconnect($dbh);
-
- print "\n";
- print "# of rows = $count\n";
- print "# of non-empty rows = $non_empty_count\n";
- print "# of distinct values (as text) = $distinct_value_count\n";
- print "# of distinct values (as integer) = $distinct_integer_value_count\n";
- print "# of distinct values (as money6) = $distinct_money6_value_count\n";
- print "# of distinct values (as money8) = $distinct_money8_value_count\n";
- print "# of distinct values (as date) = $distinct_date_value_count\n";
- print "# of distinct values (as timestamptz) = $distinct_timestamptz_value_count\n";
- print "\n";
- print "minimum value (as text) = $min_value\n";
- print "maximum value (as text) = $max_value\n";
- print "\n";
- print "minimum value length (as text) = $min_length (min value: $min_length_min_value)\n";
- print "maximum value length (as text) = $max_length (max value: $max_length_max_value)\n";
- print "\n";
- print "minimum value (as integer) = " . ($min_value_as_integer ? $min_value_as_integer : '') . "\n";
- print "maximum value (as integer) = " . ($max_value_as_integer ? $max_value_as_integer : '') . "\n";
- print "\n";
- print "minimum value (as money6) = " . ($min_value_as_money6 ? $min_value_as_money6 : '') . "\n";
- print "maximum value (as money6) = " . ($max_value_as_money6 ? $max_value_as_money6 : '') . "\n";
- print "\n";
- print "minimum value (as money8) = " . ($min_value_as_money8 ? $min_value_as_money8 : '') . "\n";
- print "maximum value (as money8) = " . ($max_value_as_money8 ? $max_value_as_money8 : '') . "\n";
- print "\n";
- print "minimum value (as date) = " . ($min_value_as_date ? $min_value_as_date : '') . "\n";
- print "maximum value (as date) = " . ($max_value_as_date ? $max_value_as_date : '') . "\n";
- print "\n";
- print "minimum value (as timestamptz) = " . ($min_value_as_timestamptz ? $min_value_as_timestamptz : '') . "\n";
- print "maximum value (as timestamptz) = " . ($max_value_as_timestamptz ? $max_value_as_timestamptz : '') . "\n";
- print "\n";
-}
-
-sub group_browse {
- my ($column,$option) = (shift,shift||"GROUP BY 1 ORDER BY 1");
-
- my $dbh = Mig::db_connect();
- my $sth;
- my $rv;
-
- $sth = $dbh->prepare(Mig::sql("
- SELECT $column, COUNT(*)
- FROM $MIGSCHEMA.$table
- $option;
- "));
- $rv = $sth->execute()
- || die "Error retrieving data from $MIGSCHEMA.$table: $!";
-
- print "\n";
- open LESS, "|less -F";
- printf LESS "%-30s", "Value:";
- print LESS "Count:\n\n";
- while (my @cols = $sth->fetchrow_array) {
- my $value = $cols[0];
- my $count = $cols[1];
- printf LESS "%-30s", defined $value ? $value : '<NULL>';
- print LESS "$count\n";
- }
- close LESS;
- print "\n";
- $sth->finish;
-}
-
-###############################################################################
-
-sub add_this_column {
- my $column = shift;
- if (!Mig::check_for_tracked_column($table,$column)) {
- my $dbh = Mig::db_connect();
- my $rv = $dbh->do("
- INSERT INTO $MIGSCHEMA.tracked_column (
- base_filename
- ,parent_table
- ,staged_table
- ,staged_column
- ) VALUES (
- " . $dbh->quote($file) . "
- ," . $dbh->quote($fdata->{parent_table}) . "
- ," . $dbh->quote($table) . "
- ," . $dbh->quote($column) . "
- );
- ") || die "Error inserting into table $MIGSCHEMA.tracked_column: $!\n";
- Mig::db_disconnect($dbh);
- }
-}
-
-sub status_this_column {
- my $column = shift;
- my $data = Mig::status_this_column($table,$column);
- if (!$data) {
- add_this_column($column);
- $data = Mig::status_this_column($table,$column);
- }
- if ($$data{parent_table}) {
- my $dbh = Mig::db_connect();
- my $sth = $dbh->prepare("
- SELECT *
- FROM $MIGSCHEMA.fields_requiring_mapping
- WHERE table_name = " . $dbh->quote( $$data{parent_table} ) . "
- AND column_name = " . $dbh->quote( $column ) . ";"
- );
- my $rv = $sth->execute()
- || die "Error checking table (tracked_column) for $table.$column: $!";
- my $data2 = $sth->fetchrow_hashref;
- if ($data2) {
- $$data{required} = 1;
- } else {
- $$data{required} = 0;
- }
- $sth->finish;
- Mig::db_disconnect($dbh);
- }
- return $data;
-}
-
-sub set_comment {
- my ($column,$comment) = (shift,shift);
- if ($comment) {
- my $data = status_this_column($column);
- my $dbh = Mig::db_connect();
- my $rv = $dbh->do("
- UPDATE $MIGSCHEMA.tracked_column
- SET comment = " . $dbh->quote($comment) . "
- WHERE id = " . $dbh->quote($data->{id}) . ";
- ") || die "Error updating table $MIGSCHEMA.tracked_column: $!\n";
- Mig::db_disconnect($dbh);
- }
-}
-
-sub set_transform {
- my ($column,$transform) = (shift,shift);
- if ($transform) {
- my $data = status_this_column($column);
- my $dbh = Mig::db_connect();
- my $rv = $dbh->do("
- UPDATE $MIGSCHEMA.tracked_column
- SET transform = " . $dbh->quote($transform) . "
- WHERE id = " . $dbh->quote($data->{id}) . ";
- ") || die "Error updating table $MIGSCHEMA.tracked_column: $!\n";
- Mig::db_disconnect($dbh);
- }
-}
-
-sub set_target {
- my ($column,$target) = (shift,shift);
- my $target_table = $table;
- my $target_column = $target;
- if ($target) {
- if ($target =~ /^(.+)\.(.+)$/) {
- $target_table = $1;
- $target_column = $2;
- }
- my $data = status_this_column($column);
- my $dbh = Mig::db_connect();
- my $rv = $dbh->do("
- UPDATE $MIGSCHEMA.tracked_column
- SET target_table = " . $dbh->quote($target_table) . "
- ,target_column = " . $dbh->quote($target_column) . "
- WHERE id = " . $dbh->quote($data->{id}) . ";
- ") || die "Error updating table $MIGSCHEMA.tracked_column: $!\n";
- Mig::db_disconnect($dbh);
- }
-}
projects / migration-tools.git / blobdiff