2 ###############################################################################
9 Quickly produces a simple Excel spreadsheet based on the tracked file suitable
10 for simple end-user mapping. The new file is named after the tracked file, but
13 Multiple files may be specified, in which case all of the results are
14 concatenated into one spreadsheet named <migration_schema>.mapping.xls
18 B<mig-quicksheet> <file> [<file>...]
22 ###############################################################################
27 HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
28 MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
32 use Spreadsheet::WriteExcel;
35 my $mig_bin = "$FindBin::Bin/";
36 use lib "$FindBin::Bin/";
39 pod2usage(-verbose => 2) if ! $ARGV[0] || $ARGV[0] eq '--help';
41 Mig::die_if_no_env_migschema();
42 Mig::die_if_mig_tracking_table_does_not_exist();
48 my $sheet_row_offset = 0;
49 my $sheet_row_start = 4;
60 foreach my $f (@ARGV) {
64 $toc->write($counter,0,$counter);
65 $toc->write($counter,1,$f);
73 my $tracked_file_id = Mig::check_for_tracked_file($file);
74 if ($tracked_file_id) {
75 $fdata = Mig::status_this_file($file);
77 die "File not currently tracked: $file\n";
79 $table = $fdata->{staged_table};
81 die "No staged staged table for file: $file\n";
86 if (scalar(@ARGV) > 1) {
87 $outfile = $MIGSCHEMA . '.mapping.xls';
89 $outfile = abs_path($ARGV[0]) . '.mapping.xls';
91 print "Writing $outfile\n";
92 $workbook = Spreadsheet::WriteExcel->new( $outfile );
93 $bold = $workbook->add_format();
95 $bold->set_align('left');
96 $left = $workbook->add_format();
97 $left->set_align('left');
98 if (scalar(@ARGV) > 1) {
99 $toc = $workbook->add_worksheet('Files');
103 sub write_worksheets {
105 print 'File #' . $counter . "\n";
107 print "Sheet: Field Summary\n";
108 $first_sheet = $workbook->add_worksheet( ($toc ? $counter . ') ' : '') . 'Field Summary');
109 $first_sheet->set_column(0,6,30);
111 my $dbh = Mig::db_connect();
112 my $sth = $dbh->prepare("
114 FROM $MIGSCHEMA.$table
117 my $rv = $sth->execute()
118 || die "Error retrieving data from information_schema: $!";
120 my @cols = $sth->fetchrow_array;
122 my $count = $cols[0];
123 Mig::db_disconnect($dbh);
125 $first_sheet->write(0,0,'Source File:',$bold);
126 $first_sheet->write(0,1,$file,$left);
127 $first_sheet->write(1,0,'Number of Rows:',$bold);
128 $first_sheet->write(1,1,$count,$left);
130 $sheet_row_start = 4;
132 $first_sheet->write($sheet_row_start,0,'Legacy Column',$bold);
133 $first_sheet->write($sheet_row_start,1,'Non-Empty Rows',$bold);
134 $first_sheet->write($sheet_row_start,2,'Distinct Non-NULL Values',$bold);
135 $first_sheet->write($sheet_row_start,3,'Min Value',$bold);
136 $first_sheet->write($sheet_row_start,4,'Min Length',$bold);
137 $first_sheet->write($sheet_row_start,5,'Max Value',$bold);
138 $first_sheet->write($sheet_row_start,6,'Max Length',$bold);
149 ###############################################################################
152 my $dbh = Mig::db_connect();
153 my $sth = $dbh->prepare("
155 FROM information_schema.columns
156 WHERE table_schema = " . $dbh->quote($MIGSCHEMA) . "
157 AND table_name = " . $dbh->quote($table) . "
158 ORDER BY dtd_identifier::INTEGER ASC;
160 my $rv = $sth->execute()
161 || die "Error retrieving data from information_schema: $!";
163 $sheet_row_offset = 0;
165 while (my $data = $sth->fetchrow_hashref) {
166 my $column = $data->{column_name};
167 if ($column eq 'x_source') {
172 && ( $column ne 'x_migrate'
173 && $column ne 'x_source'
174 && $column ne 'x_egid'
175 && $column ne 'x_hseq'
180 my $cdata = column_summary($column);
181 $first_sheet->write($sheet_row_start + $sheet_row_offset,0,$column,$left);
182 $first_sheet->write($sheet_row_start + $sheet_row_offset,1,$cdata->{non_empty_count},$left);
183 $first_sheet->write($sheet_row_start + $sheet_row_offset,2,$cdata->{distinct_value_count},$left);
184 $first_sheet->write($sheet_row_start + $sheet_row_offset,3,$cdata->{min_value},$left);
185 $first_sheet->write($sheet_row_start + $sheet_row_offset,4,$cdata->{min_length},$left);
186 $first_sheet->write($sheet_row_start + $sheet_row_offset,5,$cdata->{max_value},$left);
187 $first_sheet->write($sheet_row_start + $sheet_row_offset,6,$cdata->{max_length},$left);
188 if ($cdata->{distinct_value_count} > 1 && $cdata->{distinct_value_count} <= 500) {
194 Mig::db_disconnect($dbh);
201 my $dbh = Mig::db_connect();
204 my $sth = $dbh->prepare("
206 FROM $MIGSCHEMA.$table
207 WHERE $column IS NOT NULL AND BTRIM($column) <> '';
209 my $rv = $sth->execute()
210 || die "Error retrieving data from $MIGSCHEMA.$table: $!";
211 my @cols = $sth->fetchrow_array;
213 my $non_empty_count = $cols[0];
215 ### distinct_value_count
216 $sth = $dbh->prepare("
217 SELECT COUNT(DISTINCT $column)
218 FROM $MIGSCHEMA.$table;
220 $rv = $sth->execute()
221 || die "Error retrieving data from $MIGSCHEMA.$table: $!";
222 @cols = $sth->fetchrow_array;
224 my $distinct_value_count = $cols[0];
227 $sth = $dbh->prepare("
229 FROM $MIGSCHEMA.$table;
231 $rv = $sth->execute()
232 || die "Error retrieving data from $MIGSCHEMA.$table: $!";
233 @cols = $sth->fetchrow_array;
235 my $min_value = $cols[0];
238 $sth = $dbh->prepare("
239 SELECT MIN(LENGTH($column))
240 FROM $MIGSCHEMA.$table;
242 $rv = $sth->execute()
243 || die "Error retrieving data from $MIGSCHEMA.$table: $!";
244 @cols = $sth->fetchrow_array;
246 my $min_length = $cols[0];
249 $sth = $dbh->prepare("
251 FROM $MIGSCHEMA.$table;
253 $rv = $sth->execute()
254 || die "Error retrieving data from $MIGSCHEMA.$table: $!";
255 @cols = $sth->fetchrow_array;
257 my $max_value = $cols[0];
260 $sth = $dbh->prepare("
261 SELECT MAX(LENGTH($column))
262 FROM $MIGSCHEMA.$table;
264 $rv = $sth->execute()
265 || die "Error retrieving data from $MIGSCHEMA.$table: $!";
266 @cols = $sth->fetchrow_array;
268 my $max_length = $cols[0];
271 non_empty_count => $non_empty_count
272 ,distinct_value_count => $distinct_value_count
273 ,min_value => defined $min_value ? $min_value : '<NULL>'
274 ,min_length => defined $min_length ? $min_length : '<NULL>'
275 ,max_value => defined $max_value ? $max_value : '<NULL>'
276 ,max_length => defined $max_length ? $max_length : '<NULL>'
281 my ($column,$option) = (shift,"GROUP BY 2 ORDER BY 2");
283 print "Sheet: $column\n";
284 my $col_sheet = $workbook->add_worksheet( ($toc ? $counter . ') ' : '') . substr($column,0,31));
285 push @worksheets, $col_sheet;
286 $col_sheet->set_column(0,6,30);
288 my $col_sheet_row_start = 0;
289 my $col_sheet_row_offset = 0;
291 $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,0,'Count',$bold);
293 $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,1,'Source',$bold);
294 $option = "GROUP BY 2,3 ORDER BY 2,3";
297 $col_sheet_row_start + $col_sheet_row_offset
298 ,$has_x_source ? 2 : 1
299 ,"Legacy Value for $column"
303 my $dbh = Mig::db_connect();
307 $sth = $dbh->prepare("
308 SELECT COUNT(*), " . ($has_x_source ? 'x_source, ' : '') . "$column
309 FROM $MIGSCHEMA.$table
312 $rv = $sth->execute()
313 || die "Error retrieving data from $MIGSCHEMA.$table: $!";
315 while (my @cols = $sth->fetchrow_array) {
316 $col_sheet_row_offset++;
317 my $count = $cols[0];
318 $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,0,$count,$left);
321 my $source = defined $cols[1] ? $cols[1] : '<NULL>';
322 $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,1,$source,$left);
323 $value = defined $cols[2] ? $cols[2] : '<NULL>';
324 $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,2,$value,$left);
326 $value = defined $cols[1] ? $cols[1] : '<NULL>';
327 $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,1,$value,$left);