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