mig-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 Mig;
  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 Mig::die_if_no_env_migschema();
  83 Mig::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 (Mig::check_db_migschema_for_specific_table($outtable)) {
 115         drop_existing_outtable();
 116     }
 117 }
 118
 119 sub handle_file {
 120     my $tracked_file_id = Mig::check_for_tracked_file($file);
 121     if ($tracked_file_id) {
 122         $fdata = Mig::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 (Mig::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 = Mig::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     Mig::db_disconnect($dbh);
 199 }
 200
 201 sub create_new_outtable {
 202     my $dbh = Mig::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     Mig::db_disconnect($dbh);
 211 }
 212
 213 sub create_new_subtable {
 214     my $subtable = shift;
 215     my $dbh = Mig::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     Mig::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( $tab_name );
 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 = Mig::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_columns();
 290
 291     if ($outfile) {
 292         $first_sheet->write($count + 3,0,'Source File:',$bold);
 293         $first_sheet->write($count + 3,1,$file,$left);
 294         $first_sheet->write($count + 4,0,'Number of Rows:',$bold);
 295         $first_sheet->write($count + 4,1,$count,$left);
 296     }
 297     if ($outtable) {
 298         try {
 299             $rv = $dbh->do('INSERT INTO ' . qq^$MIGSCHEMA."$first_table"^ . ' ("Legacy Column") VALUES (NULL);');
 300             $rv = $dbh->do('INSERT INTO ' . qq^$MIGSCHEMA."$first_table"^ .
 301                 ' ("Legacy Column","Non-Empty Rows") ' . "VALUES ('Source File:'," . $dbh->quote($file) . ");");
 302             $rv = $dbh->do('INSERT INTO ' . qq^$MIGSCHEMA."$first_table"^ .
 303                 ' ("Legacy Column","Non-Empty Rows") ' . "VALUES ('Number of Rows:',$count);");
 304         } catch {
 305             die "Error inserting into subtable ($MIGSCHEMA.$first_table): $_\n";
 306         };
 307     }
 308
 309     Mig::db_disconnect($dbh);
 310 }
 311
 312 sub close_workbook {
 313     if ($outfile) {
 314         $workbook->close();
 315     }
 316 }
 317
 318 exit 0;
 319
 320 ###############################################################################
 321
 322 sub handle_columns {
 323     my $dbh = Mig::db_connect();
 324     my $sth = $dbh->prepare("
 325         SELECT *
 326         FROM information_schema.columns
 327         WHERE table_schema = " . $dbh->quote($MIGSCHEMA) . "
 328         AND table_name = " . $dbh->quote($table) . "
 329         ORDER BY dtd_identifier::INTEGER ASC;
 330     ");
 331     my $rv = $sth->execute()
 332         || die "Error retrieving data from information_schema: $!";
 333
 334     $sheet_row_offset = 0;
 335     $has_x_source = 0;
 336
 337     while (my $data = $sth->fetchrow_hashref) {
 338         my $column = $data->{column_name};
 339         if ($column eq 'x_source') {
 340             $has_x_source = 1;
 341         }
 342         if ($column =~ /^l_/
 343             || ($column =~ /^x_/
 344                 && (   $column ne 'x_migrate'
 345                     && $column ne 'x_source'
 346                     && $column ne 'x_egid'
 347                     && $column ne 'x_hseq'
 348                 )
 349             )
 350         ) {
 351             $sheet_row_offset++;
 352             my $cdata = column_summary($column);
 353             if ($outfile) {
 354                 $first_sheet->write($sheet_row_start + $sheet_row_offset,0,$column,$left);
 355                 $first_sheet->write($sheet_row_start + $sheet_row_offset,1,$cdata->{non_empty_count},$left);
 356                 $first_sheet->write($sheet_row_start + $sheet_row_offset,2,$cdata->{distinct_value_count},$left);
 357                 $first_sheet->write($sheet_row_start + $sheet_row_offset,3,$cdata->{min_value},$left);
 358                 $first_sheet->write($sheet_row_start + $sheet_row_offset,4,$cdata->{min_length},$left);
 359                 $first_sheet->write($sheet_row_start + $sheet_row_offset,5,$cdata->{max_value},$left);
 360                 $first_sheet->write($sheet_row_start + $sheet_row_offset,6,$cdata->{max_length},$left);
 361             }
 362             if ($outtable) {
 363                 $rv = $dbh->do(qq^INSERT INTO $MIGSCHEMA."$first_table" VALUES (^ . join(','
 364                     ,$cdata->{non_empty_count}
 365                     ,$cdata->{distinct_value_count}
 366                     ,$dbh->quote($cdata->{min_value})
 367                     ,$cdata->{min_length}
 368                     ,$dbh->quote($cdata->{max_value})
 369                     ,$cdata->{max_length}
 370                 ) . ');') || die "Error inserting into subtable $MIGSCHEMA.\"$first_table\": $!";
 371             }
 372             if ($cdata->{distinct_value_count} > 1 && $cdata->{distinct_value_count} <= 500) {
 373                 group_by($column);
 374             }
 375         }
 376     }
 377     $sth->finish;
 378     Mig::db_disconnect($dbh);
 379 }
 380
 381 sub column_summary {
 382
 383     my $column = shift;
 384
 385     my $dbh = Mig::db_connect();
 386
 387     ### non_empty_count
 388     my $sth = $dbh->prepare("
 389         SELECT COUNT(*)
 390         FROM $MIGSCHEMA.$table
 391         WHERE $column IS NOT NULL AND BTRIM($column) <> '';
 392     ");
 393     my $rv = $sth->execute()
 394         || die "Error retrieving data from $MIGSCHEMA.$table: $!";
 395     my @cols = $sth->fetchrow_array;
 396     $sth->finish;
 397     my $non_empty_count = $cols[0];
 398
 399     ### distinct_value_count
 400     $sth = $dbh->prepare("
 401         SELECT COUNT(DISTINCT $column)
 402         FROM $MIGSCHEMA.$table;
 403     ");
 404     $rv = $sth->execute()
 405         || die "Error retrieving data from $MIGSCHEMA.$table: $!";
 406     @cols = $sth->fetchrow_array;
 407     $sth->finish;
 408     my $distinct_value_count = $cols[0];
 409
 410     ### min_value
 411     $sth = $dbh->prepare("
 412         SELECT MIN($column)
 413         FROM $MIGSCHEMA.$table;
 414     ");
 415     $rv = $sth->execute()
 416         || die "Error retrieving data from $MIGSCHEMA.$table: $!";
 417     @cols = $sth->fetchrow_array;
 418     $sth->finish;
 419     my $min_value = $cols[0];
 420
 421     ### min_length
 422     $sth = $dbh->prepare("
 423         SELECT MIN(LENGTH($column))
 424         FROM $MIGSCHEMA.$table;
 425     ");
 426     $rv = $sth->execute()
 427         || die "Error retrieving data from $MIGSCHEMA.$table: $!";
 428     @cols = $sth->fetchrow_array;
 429     $sth->finish;
 430     my $min_length = $cols[0];
 431
 432     ### max_value
 433     $sth = $dbh->prepare("
 434         SELECT MAX($column)
 435         FROM $MIGSCHEMA.$table;
 436     ");
 437     $rv = $sth->execute()
 438         || die "Error retrieving data from $MIGSCHEMA.$table: $!";
 439     @cols = $sth->fetchrow_array;
 440     $sth->finish;
 441     my $max_value = $cols[0];
 442
 443     ### max_length
 444     $sth = $dbh->prepare("
 445         SELECT MAX(LENGTH($column))
 446         FROM $MIGSCHEMA.$table;
 447     ");
 448     $rv = $sth->execute()
 449         || die "Error retrieving data from $MIGSCHEMA.$table: $!";
 450     @cols = $sth->fetchrow_array;
 451     $sth->finish;
 452     my $max_length = $cols[0];
 453
 454     return {
 455          non_empty_count        => $non_empty_count
 456         ,distinct_value_count   => $distinct_value_count
 457         ,min_value              => defined $min_value ? $min_value : '<NULL>'
 458         ,min_length             => defined $min_length ? $min_length : '<NULL>'
 459         ,max_value              => defined $max_value ? $max_value : '<NULL>'
 460         ,max_length             => defined $max_length ? $max_length : '<NULL>'
 461     };
 462 }
 463
 464 sub group_by {
 465     my ($column,$option) = (shift,"GROUP BY 2 ORDER BY 2");
 466
 467     my $dbh = Mig::db_connect();
 468     my $sth;
 469     my $rv;
 470
 471     my $col_sheet_row_start = 0;
 472     my $col_sheet_row_offset = 0;
 473     my $col_sheet;
 474     my $col_table;
 475
 476     my $sheet_name = (scalar(@ARGV) > 1 ? $counter . ') ' : '') . $column;
 477     $sheet_name = substr($sheet_name,0,31);
 478
 479     print "Sheet: $sheet_name\n";
 480     if ($has_x_source) {
 481         $option = "GROUP BY 2,3 ORDER BY 2,3";
 482     }
 483
 484     if ($outfile) {
 485         $col_sheet = $workbook->add_worksheet( $sheet_name );
 486         push @worksheets, $col_sheet;
 487         $col_sheet->set_column(0,6,30);
 488         $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,0,'Count',$bold);
 489         if ($has_x_source) {
 490             $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,1,'Source',$bold);
 491         }
 492         $col_sheet->write(
 493             $col_sheet_row_start + $col_sheet_row_offset
 494             ,$has_x_source ? 2 : 1
 495             ,"Legacy Value for $column"
 496             ,$bold
 497         );
 498     }
 499
 500     if ($outtable) {
 501         $col_table = "$outtable $sheet_name";
 502         create_new_subtable( $col_table );
 503         $rv = $dbh->do('ALTER TABLE ' . qq^$MIGSCHEMA."$col_table"^ . ' ADD COLUMN "Count" TEXT;')
 504             || die qq^Error altering subtable $MIGSCHEMA."$col_table": $!\n^;
 505         if ($has_x_source) {
 506             $rv = $dbh->do('ALTER TABLE ' . qq^$MIGSCHEMA."$col_table"^ . ' ADD COLUMN "Source" TEXT;')
 507                 || die qq^Error altering subtable $MIGSCHEMA."$col_table": $!\n^;
 508         }
 509         $rv = $dbh->do('ALTER TABLE ' . qq^$MIGSCHEMA."$col_table"^ . ' ADD COLUMN "' . $dbh->quote("Legacy value for $column") . '" TEXT;')
 510             || die qq^Error altering subtable $MIGSCHEMA."$col_table": $!\n^;
 511     }
 512
 513     $sth = $dbh->prepare("
 514         SELECT COUNT(*), " . ($has_x_source ? 'x_source, ' : '') . "$column
 515         FROM $MIGSCHEMA.$table
 516         $option;
 517     ");
 518     $rv = $sth->execute()
 519         || die "Error retrieving data from $MIGSCHEMA.$table: $!";
 520
 521     while (my @cols = $sth->fetchrow_array) {
 522         $col_sheet_row_offset++;
 523         my $count = $cols[0];
 524         $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,0,$count,$left) if $outfile;
 525         my $value;
 526         my $source;
 527         if ($has_x_source) {
 528             $source = defined $cols[1] ? $cols[1] : '<NULL>';
 529             $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,1,$source,$left) if $outfile;
 530             $value = defined $cols[2] ? $cols[2] : '<NULL>';
 531             $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,2,$value,$left) if $outfile;
 532         } else {
 533             $value = defined $cols[1] ? $cols[1] : '<NULL>';
 534             $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,1,$value,$left) if $outfile;
 535         }
 536         if ($outtable) {
 537             if ($has_x_source) {
 538                 $rv = $dbh->do(qq^INSERT INTO $MIGSCHEMA."$col_table" VALUES (^ . join(','
 539                     ,$count
 540                     ,$dbh->quote($source)
 541                     ,$dbh->quote($value)
 542                 ) . ');') || die "Error inserting into subtable $MIGSCHEMA.\"$col_table\": $!";
 543             } else {
 544                 $rv = $dbh->do(qq^INSERT INTO $MIGSCHEMA."$col_table" VALUES (^ . join(','
 545                     ,$count
 546                     ,$dbh->quote($value)
 547                 ) . ');') || die "Error inserting into subtable $MIGSCHEMA.\"$col_table\": $!";
 548             }
 549         }
 550     }
 551     $sth->finish;
 552 }
 553