emig.d/bin/mig-quicksheet

   1 #!/usr/bin/perl -w
   2 ###############################################################################
   3 =pod
   4
   5 =head1 NAME
   6
   7 mig-quicksheet
   8
   9 By default:
  10
  11 Quickly produces an Excel spreadsheet based on the tracked file suitable
  12 for simple end-user mapping.  The new file is named after the tracked file, but
  13 ends in .mapping.xls
  14
  15 Multiple files may be specified, in which case all of the results are
  16 concatenated into one spreadsheet named <migration_schema>.mapping.xls
  17
  18 If using --outfile:
  19
  20 This specifies the exact name to use for the Excel file.  If not specified, and
  21 there is also no --outtable, then the naming convention will be as specified
  22 above.
  23
  24 If using --outtable:
  25
  26 This specifies a summary table and prefix to use within the migration schema for
  27 recording the output either in addition to or instead of the Excel file.  Unless
  28 --force is specified, it will not overwrite existing tables.
  29
  30 If using --drop with --outable:
  31
  32 This will delete the summary table specified and all related sub-tables.
  33
  34 =head1 SYNOPSIS
  35
  36 B<mig-quicksheet> [--force|--drop|--outfile <file>|--outtable <table_name>] <file> [<file>...]
  37
  38 =cut
  39
  40 ###############################################################################
  41
  42 use strict;
  43 use Switch;
  44 use Env qw(
  45     HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
  46     MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
  47 );
  48 use Try::Tiny;
  49 use Pod::Usage;
  50 use Getopt::Long;
  51 use DBI;
  52 use Spreadsheet::WriteExcel;
  53 use Cwd 'abs_path';
  54 use FindBin;
  55 my $mig_bin = "$FindBin::Bin/";
  56 use lib "$FindBin::Bin/";
  57 use EMig;
  58
  59 my $outtable = '';
  60 my $outfile = '';
  61 my $force;
  62 my $drop;
  63 my $help;
  64
  65 GetOptions(
  66         'outtable=s' => \$outtable,
  67         'outfile=s' => \$outfile,
  68         'force' => \$force,
  69         'drop' => \$drop,
  70         'help|?' => \$help
  71 );
  72 pod2usage(-verbose => 2) if $help || ! $ARGV[0];
  73
  74 if (! $outtable && ! $outfile) {
  75     if (scalar(@ARGV) > 1) {
  76         $outfile = $MIGSCHEMA . '.mapping.xls';
  77     } else {
  78         $outfile = abs_path($ARGV[0]) . '.mapping.xls';
  79     }
  80 }
  81
  82 EMig::die_if_no_env_migschema();
  83 EMig::die_if_mig_tracking_table_does_not_exist();
  84
  85 my $workbook;
  86 my @worksheets = ();
  87 my $first_sheet;
  88 my $first_table;
  89 my $toc;
  90 my $sheet_row_offset = 0;
  91 my $sheet_row_start = 4;
  92 my $table;
  93 my $file;
  94 my $fdata;
  95 my $has_x_source = 0;
  96 my $bold;
  97 my $left;
  98 my $counter = 0;
  99
 100 if (!$drop) {
 101     init_workbook();
 102     foreach my $f (@ARGV) {
 103         $file = abs_path($f);
 104         $counter++;
 105         if ($toc) {
 106             $toc->write($counter,0,$counter);
 107             $toc->write($counter,1,$f);
 108         }
 109         handle_file();
 110         write_worksheets();
 111     }
 112     close_workbook();
 113 } else {
 114     if (EMig::check_db_migschema_for_specific_table($outtable)) {
 115         drop_existing_outtable();
 116     }
 117 }
 118
 119 sub handle_file {
 120     my $tracked_file_id = EMig::check_for_tracked_file($file);
 121     if ($tracked_file_id) {
 122         $fdata = EMig::status_this_file($file);
 123     } else {
 124         die "File not currently tracked: $file\n";
 125     }
 126     $table = $fdata->{staged_table};
 127     if (!$table) {
 128         die "No staged staged table for file: $file\n";
 129     }
 130 }
 131
 132 sub init_workbook {
 133     if ($outfile) {
 134         print "Writing $outfile\n";
 135         $workbook = Spreadsheet::WriteExcel->new( $outfile );
 136         $bold = $workbook->add_format();
 137         $bold->set_bold();
 138         $bold->set_align('left');
 139         $left = $workbook->add_format();
 140         $left->set_align('left');
 141         if (scalar(@ARGV) > 1) {
 142             $toc = $workbook->add_worksheet('Files');
 143         }
 144     }
 145     if ($outtable) {
 146         if (EMig::check_db_migschema_for_specific_table($outtable)) {
 147             if ($force) {
 148                 drop_existing_outtable();
 149             } else {
 150                 die "$outtable already exists.  Use --force to wipe and redo tables.\n";
 151             }
 152         }
 153         create_new_outtable();
 154     }
 155 }
 156
 157 sub drop_existing_outtable {
 158
 159     # we want a transaction for this one
 160     my $dbh = EMig::db_connect();
 161     $dbh->{AutoCommit} = 0;
 162     $dbh->{RaiseError} = 1;
 163
 164     try {
 165         # gather subordinate tables
 166
 167         my @tables = ();
 168         my $sth = $dbh->prepare("
 169             SELECT summary_table
 170             FROM $MIGSCHEMA.$outtable
 171             ORDER BY 1;"
 172         );
 173         my $rv = $sth->execute();
 174         my $rows = $sth->fetchall_arrayref;
 175         for my $row ( @$rows ) {
 176             push @tables, $row->[0]
 177         }
 178
 179         # drop them
 180
 181         foreach my $table (@tables) {
 182             print "Dropping $MIGSCHEMA.$table\n";
 183             $dbh->do("DROP TABLE $MIGSCHEMA.\"$table\";");
 184         }
 185
 186         # drop master table
 187
 188         print "Dropping $MIGSCHEMA.$outtable\n";
 189         $dbh->do("DROP TABLE $MIGSCHEMA.$outtable;");
 190
 191         $dbh->commit;
 192     } catch {
 193         warn "Transaction aborted because $_\n";
 194         eval { $dbh->rollback };
 195         die "Aborting mig-quicksheet\n";
 196     };
 197
 198     EMig::db_disconnect($dbh);
 199 }
 200
 201 sub create_new_outtable {
 202     my $dbh = EMig::db_connect();
 203     print "Creating table $MIGSCHEMA.$outtable\n";
 204     my $rv = $dbh->do("
 205         CREATE UNLOGGED TABLE $MIGSCHEMA.$outtable (
 206             file TEXT,
 207             summary_table TEXT UNIQUE
 208         );
 209     ") || die "Error creating outtable ($MIGSCHEMA.$outtable): $!\n";
 210     EMig::db_disconnect($dbh);
 211 }
 212
 213 sub create_new_subtable {
 214     my $subtable = shift;
 215     my $dbh = EMig::db_connect();
 216     $dbh->{AutoCommit} = 0;
 217     $dbh->{RaiseError} = 1;
 218
 219     try {
 220         print "Creating table $MIGSCHEMA.\"$subtable\"\n";
 221         my $rv = $dbh->do("
 222             CREATE UNLOGGED TABLE $MIGSCHEMA.\"$subtable\" ();
 223         ") || die "Error creating subtable ($MIGSCHEMA.\"$subtable\"): $!\n";
 224         $rv = $dbh->do("
 225             INSERT INTO $MIGSCHEMA.$outtable (file,summary_table) VALUES (" . $dbh->quote($file) . ',' . $dbh->quote($subtable) . ");
 226         ") || die "Error inserting into outtable ($MIGSCHEMA.$outtable): $!\n";
 227         $dbh->commit;
 228     } catch {
 229         warn "Transaction aborted because $_\n";
 230         eval { $dbh->rollback };
 231         die "Aborting mig-quicksheet\n";
 232     };
 233
 234     EMig::db_disconnect($dbh);
 235 }
 236
 237 sub write_worksheets {
 238     print 'File #' . $counter . "\n";
 239     print "Sheet: Field Summary\n";
 240     my $tab_name = (scalar(@ARGV) > 1 ? $counter . ') ' : '') . 'Field Summary';
 241     $tab_name = substr($tab_name,0,31); # truncate for WriteExcel
 242     if ($outfile) {
 243         $first_sheet = $workbook->add_worksheet( substr($tab_name,0,31) );
 244         $first_sheet->set_column(0,6,30);
 245     }
 246     if ($outtable) {
 247         $first_table = "$outtable $tab_name";
 248         create_new_subtable( $first_table );
 249     }
 250
 251     my $dbh = EMig::db_connect();
 252     my $sth = $dbh->prepare("
 253         SELECT COUNT(*)
 254         FROM $MIGSCHEMA.$table
 255         LIMIT 1;
 256     ");
 257     my $rv = $sth->execute()
 258         || die "Error retrieving data from information_schema: $!";
 259
 260     my @cols = $sth->fetchrow_array;
 261     $sth->finish;
 262     my $count = $cols[0];
 263
 264     $sheet_row_start = 0;
 265
 266     if ($outfile) {
 267         $first_sheet->write($sheet_row_start,0,'Legacy Column',$bold);
 268         $first_sheet->write($sheet_row_start,1,'Non-Empty Rows',$bold);
 269         $first_sheet->write($sheet_row_start,2,'Distinct Non-NULL Values',$bold);
 270         $first_sheet->write($sheet_row_start,3,'Min Value',$bold);
 271         $first_sheet->write($sheet_row_start,4,'Min Length',$bold);
 272         $first_sheet->write($sheet_row_start,5,'Max Value',$bold);
 273         $first_sheet->write($sheet_row_start,6,'Max Length',$bold);
 274     }
 275     if ($outtable) {
 276         try {
 277             $rv = $dbh->do('ALTER TABLE ' . qq^$MIGSCHEMA."$first_table"^ . ' ADD COLUMN "Legacy Column" TEXT;');
 278             $rv = $dbh->do('ALTER TABLE ' . qq^$MIGSCHEMA."$first_table"^ . ' ADD COLUMN "Non-Empty Rows" TEXT;');
 279             $rv = $dbh->do('ALTER TABLE ' . qq^$MIGSCHEMA."$first_table"^ . ' ADD COLUMN "Distinct Non-NULL Values" TEXT;');
 280             $rv = $dbh->do('ALTER TABLE ' . qq^$MIGSCHEMA."$first_table"^ . ' ADD COLUMN "Min Value" TEXT;');
 281             $rv = $dbh->do('ALTER TABLE ' . qq^$MIGSCHEMA."$first_table"^ . ' ADD COLUMN "Min Length" TEXT;');
 282             $rv = $dbh->do('ALTER TABLE ' . qq^$MIGSCHEMA."$first_table"^ . ' ADD COLUMN "Max Value" TEXT;');
 283             $rv = $dbh->do('ALTER TABLE ' . qq^$MIGSCHEMA."$first_table"^ . ' ADD COLUMN "Max Length" TEXT;');
 284         } catch {
 285             die "Error modifying subtable ($MIGSCHEMA.$first_table): $_\n";
 286         };
 287     }
 288
 289     handle_list();
 290     handle_columns();
 291
 292     if ($outfile) {
 293         $first_sheet->write($count + 3,0,'Source File:',$bold);
 294         $first_sheet->write($count + 3,1,$file,$left);
 295         $first_sheet->write($count + 4,0,'Number of Rows:',$bold);
 296         $first_sheet->write($count + 4,1,$count,$left);
 297     }
 298     if ($outtable) {
 299         try {
 300             $rv = $dbh->do('INSERT INTO ' . qq^$MIGSCHEMA."$first_table"^ . ' ("Legacy Column") VALUES (NULL);');
 301             $rv = $dbh->do('INSERT INTO ' . qq^$MIGSCHEMA."$first_table"^ .
 302                 ' ("Legacy Column","Non-Empty Rows") ' . "VALUES ('Source File:'," . $dbh->quote($file) . ");");
 303             $rv = $dbh->do('INSERT INTO ' . qq^$MIGSCHEMA."$first_table"^ .
 304                 ' ("Legacy Column","Non-Empty Rows") ' . "VALUES ('Number of Rows:',$count);");
 305         } catch {
 306             die "Error inserting into subtable ($MIGSCHEMA.$first_table): $_\n";
 307         };
 308     }
 309
 310     EMig::db_disconnect($dbh);
 311 }
 312
 313 sub close_workbook {
 314     if ($outfile) {
 315         $workbook->close();
 316     }
 317 }
 318
 319 exit 0;
 320
 321 ###############################################################################
 322
 323 sub handle_list {
 324     my $dbh = EMig::db_connect();
 325     my $sth = $dbh->prepare("
 326         SELECT *
 327         FROM " . $MIGSCHEMA. "." . $table . "
 328         LIMIT 65530;
 329     ");
 330     my $rv = $sth->execute()
 331         || die "Error retrieving data from staging table: $!";
 332     my $list_sheet;
 333
 334     $sheet_row_offset = 0;
 335     $has_x_source = 0;
 336     if ($outfile) {
 337         print "Sheet: $table\n";
 338         $list_sheet = $workbook->add_worksheet( substr($table,0,31) );
 339     }
 340
 341     my $handle_headers = 1;
 342
 343     while (my $data = $sth->fetchrow_hashref) {
 344         if ($handle_headers) {
 345             my $_idx = 0;
 346             foreach my $col (sort keys %{ $data }) {
 347                 $list_sheet->write($sheet_row_start + $sheet_row_offset,$_idx++,$col,$bold);
 348             }
 349             $handle_headers = 0;
 350         }
 351         $sheet_row_offset++;
 352         my $idx = 0;
 353         foreach my $col (sort keys %{ $data }) {
 354             my $cdata = $$data{$col};
 355             if (!defined $cdata) { $cdata = '\N'; }
 356             if ($outfile) {
 357                 $list_sheet->write($sheet_row_start + $sheet_row_offset,$idx++,$cdata,$left);
 358             }
 359         }
 360     }
 361 }
 362
 363 sub handle_columns {
 364     my $dbh = EMig::db_connect();
 365     my $sth = $dbh->prepare("
 366         SELECT *
 367         FROM information_schema.columns
 368         WHERE table_schema = " . $dbh->quote($MIGSCHEMA) . "
 369         AND table_name = " . $dbh->quote($table) . "
 370         ORDER BY dtd_identifier::INTEGER ASC;
 371     ");
 372     my $rv = $sth->execute()
 373         || die "Error retrieving data from information_schema: $!";
 374
 375     $sheet_row_offset = 0;
 376     $has_x_source = 0;
 377
 378     while (my $data = $sth->fetchrow_hashref) {
 379         my $column = $data->{column_name};
 380         if ($column eq 'x_source') {
 381             $has_x_source = 1;
 382         }
 383         if ($column =~ /^l_/
 384             || ($column =~ /^x_/
 385                 && (   $column ne 'x_migrate'
 386                     && $column ne 'x_source'
 387                     && $column ne 'x_egid'
 388                     && $column ne 'x_hseq'
 389                 )
 390             )
 391         ) {
 392             $sheet_row_offset++;
 393             my $cdata = column_summary($column);
 394             if ($outfile) {
 395                 $first_sheet->write($sheet_row_start + $sheet_row_offset,0,$column,$left);
 396                 $first_sheet->write($sheet_row_start + $sheet_row_offset,1,$cdata->{non_empty_count},$left);
 397                 $first_sheet->write($sheet_row_start + $sheet_row_offset,2,$cdata->{distinct_value_count},$left);
 398                 $first_sheet->write($sheet_row_start + $sheet_row_offset,3,$cdata->{min_value},$left);
 399                 $first_sheet->write($sheet_row_start + $sheet_row_offset,4,$cdata->{min_length},$left);
 400                 $first_sheet->write($sheet_row_start + $sheet_row_offset,5,$cdata->{max_value},$left);
 401                 $first_sheet->write($sheet_row_start + $sheet_row_offset,6,$cdata->{max_length},$left);
 402             }
 403             if ($outtable) {
 404                 $rv = $dbh->do(qq^INSERT INTO $MIGSCHEMA."$first_table" VALUES (^ . join(','
 405                     ,$cdata->{non_empty_count}
 406                     ,$cdata->{distinct_value_count}
 407                     ,$dbh->quote($cdata->{min_value})
 408                     ,$cdata->{min_length}
 409                     ,$dbh->quote($cdata->{max_value})
 410                     ,$cdata->{max_length}
 411                 ) . ');') || die "Error inserting into subtable $MIGSCHEMA.\"$first_table\": $!";
 412             }
 413             if ($cdata->{distinct_value_count} > 1 && $cdata->{distinct_value_count} <= 500) {
 414                 group_by($column);
 415             }
 416         }
 417     }
 418     $sth->finish;
 419     EMig::db_disconnect($dbh);
 420 }
 421
 422 sub column_summary {
 423
 424     my $column = shift;
 425
 426     my $dbh = EMig::db_connect();
 427
 428     ### non_empty_count
 429     my $sth = $dbh->prepare("
 430         SELECT COUNT(*)
 431         FROM $MIGSCHEMA.$table
 432         WHERE $column IS NOT NULL AND BTRIM($column) <> '';
 433     ");
 434     my $rv = $sth->execute()
 435         || die "Error retrieving data from $MIGSCHEMA.$table: $!";
 436     my @cols = $sth->fetchrow_array;
 437     $sth->finish;
 438     my $non_empty_count = $cols[0];
 439
 440     ### distinct_value_count
 441     $sth = $dbh->prepare("
 442         SELECT COUNT(DISTINCT $column)
 443         FROM $MIGSCHEMA.$table;
 444     ");
 445     $rv = $sth->execute()
 446         || die "Error retrieving data from $MIGSCHEMA.$table: $!";
 447     @cols = $sth->fetchrow_array;
 448     $sth->finish;
 449     my $distinct_value_count = $cols[0];
 450
 451     ### min_value
 452     $sth = $dbh->prepare("
 453         SELECT MIN($column)
 454         FROM $MIGSCHEMA.$table;
 455     ");
 456     $rv = $sth->execute()
 457         || die "Error retrieving data from $MIGSCHEMA.$table: $!";
 458     @cols = $sth->fetchrow_array;
 459     $sth->finish;
 460     my $min_value = $cols[0];
 461
 462     ### min_length
 463     $sth = $dbh->prepare("
 464         SELECT MIN(LENGTH($column))
 465         FROM $MIGSCHEMA.$table;
 466     ");
 467     $rv = $sth->execute()
 468         || die "Error retrieving data from $MIGSCHEMA.$table: $!";
 469     @cols = $sth->fetchrow_array;
 470     $sth->finish;
 471     my $min_length = $cols[0];
 472
 473     ### max_value
 474     $sth = $dbh->prepare("
 475         SELECT MAX($column)
 476         FROM $MIGSCHEMA.$table;
 477     ");
 478     $rv = $sth->execute()
 479         || die "Error retrieving data from $MIGSCHEMA.$table: $!";
 480     @cols = $sth->fetchrow_array;
 481     $sth->finish;
 482     my $max_value = $cols[0];
 483
 484     ### max_length
 485     $sth = $dbh->prepare("
 486         SELECT MAX(LENGTH($column))
 487         FROM $MIGSCHEMA.$table;
 488     ");
 489     $rv = $sth->execute()
 490         || die "Error retrieving data from $MIGSCHEMA.$table: $!";
 491     @cols = $sth->fetchrow_array;
 492     $sth->finish;
 493     my $max_length = $cols[0];
 494
 495     return {
 496          non_empty_count        => $non_empty_count
 497         ,distinct_value_count   => $distinct_value_count
 498         ,min_value              => defined $min_value ? $min_value : '<NULL>'
 499         ,min_length             => defined $min_length ? $min_length : '<NULL>'
 500         ,max_value              => defined $max_value ? $max_value : '<NULL>'
 501         ,max_length             => defined $max_length ? $max_length : '<NULL>'
 502     };
 503 }
 504
 505 sub group_by {
 506     my ($column,$option) = (shift,"GROUP BY 2 ORDER BY 2");
 507
 508     my $dbh = EMig::db_connect();
 509     my $sth;
 510     my $rv;
 511
 512     my $col_sheet_row_start = 0;
 513     my $col_sheet_row_offset = 0;
 514     my $col_sheet;
 515     my $col_table;
 516
 517     my $sheet_name = (scalar(@ARGV) > 1 ? $counter . ') ' : '') . $column;
 518     $sheet_name = substr($sheet_name,0,31);
 519
 520     print "Sheet: $sheet_name\n";
 521     if ($has_x_source) {
 522         $option = "GROUP BY 2,3 ORDER BY 2,3";
 523     }
 524
 525     if ($outfile) {
 526         $col_sheet = $workbook->add_worksheet( substr($sheet_name,0,31) );
 527         push @worksheets, $col_sheet;
 528         $col_sheet->set_column(0,6,30);
 529         $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,0,'Count',$bold);
 530         if ($has_x_source) {
 531             $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,1,'Source',$bold);
 532         }
 533         $col_sheet->write(
 534             $col_sheet_row_start + $col_sheet_row_offset
 535             ,$has_x_source ? 2 : 1
 536             ,"Legacy Value for $column"
 537             ,$bold
 538         );
 539     }
 540
 541     if ($outtable) {
 542         $col_table = "$outtable $sheet_name";
 543         create_new_subtable( $col_table );
 544         $rv = $dbh->do('ALTER TABLE ' . qq^$MIGSCHEMA."$col_table"^ . ' ADD COLUMN "Count" TEXT;')
 545             || die qq^Error altering subtable $MIGSCHEMA."$col_table": $!\n^;
 546         if ($has_x_source) {
 547             $rv = $dbh->do('ALTER TABLE ' . qq^$MIGSCHEMA."$col_table"^ . ' ADD COLUMN "Source" TEXT;')
 548                 || die qq^Error altering subtable $MIGSCHEMA."$col_table": $!\n^;
 549         }
 550         $rv = $dbh->do('ALTER TABLE ' . qq^$MIGSCHEMA."$col_table"^ . ' ADD COLUMN "' . $dbh->quote("Legacy value for $column") . '" TEXT;')
 551             || die qq^Error altering subtable $MIGSCHEMA."$col_table": $!\n^;
 552     }
 553
 554     $sth = $dbh->prepare("
 555         SELECT COUNT(*), " . ($has_x_source ? 'x_source, ' : '') . "$column
 556         FROM $MIGSCHEMA.$table
 557         $option;
 558     ");
 559     $rv = $sth->execute()
 560         || die "Error retrieving data from $MIGSCHEMA.$table: $!";
 561
 562     while (my @cols = $sth->fetchrow_array) {
 563         $col_sheet_row_offset++;
 564         my $count = $cols[0];
 565         $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,0,$count,$left) if $outfile;
 566         my $value;
 567         my $source;
 568         if ($has_x_source) {
 569             $source = defined $cols[1] ? $cols[1] : '<NULL>';
 570             $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,1,$source,$left) if $outfile;
 571             $value = defined $cols[2] ? $cols[2] : '<NULL>';
 572             $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,2,$value,$left) if $outfile;
 573         } else {
 574             $value = defined $cols[1] ? $cols[1] : '<NULL>';
 575             $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,1,$value,$left) if $outfile;
 576         }
 577         if ($outtable) {
 578             if ($has_x_source) {
 579                 $rv = $dbh->do(qq^INSERT INTO $MIGSCHEMA."$col_table" VALUES (^ . join(','
 580                     ,$count
 581                     ,$dbh->quote($source)
 582                     ,$dbh->quote($value)
 583                 ) . ');') || die "Error inserting into subtable $MIGSCHEMA.\"$col_table\": $!";
 584             } else {
 585                 $rv = $dbh->do(qq^INSERT INTO $MIGSCHEMA."$col_table" VALUES (^ . join(','
 586                     ,$count
 587                     ,$dbh->quote($value)
 588                 ) . ');') || die "Error inserting into subtable $MIGSCHEMA.\"$col_table\": $!";
 589             }
 590         }
 591     }
 592     $sth->finish;
 593 }
 594