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
15 B<mig-quicksheet> <file>
19 ###############################################################################
24 HOME PGHOST PGPORT PGUSER PGDATABASE MIGSCHEMA
25 MIGBASEWORKDIR MIGBASEGITDIR MIGGITDIR MIGWORKDIR
29 use Spreadsheet::WriteExcel;
32 my $mig_bin = "$FindBin::Bin/";
33 use lib "$FindBin::Bin/";
36 pod2usage(-verbose => 2) if ! $ARGV[0] || $ARGV[0] eq '--help';
38 Mig::die_if_no_env_migschema();
39 Mig::die_if_mig_tracking_table_does_not_exist();
42 my $file = abs_path($ARGV[0]);
44 my $tracked_file_id = Mig::check_for_tracked_file($file);
45 if ($tracked_file_id) {
46 $fdata = Mig::status_this_file($file);
48 die "File not currently tracked: $file\n";
51 my $table = $fdata->{staged_table};
53 die "No staged staged table for file: $file\n";
56 my $workbook = Spreadsheet::WriteExcel->new($file . '.mapping.xls');
58 print "Writing $file.mapping.xls\n";
59 print "Sheet: Field Summary\n";
60 my $first_sheet = $workbook->add_worksheet('Field Summary');
61 my $bold = $workbook->add_format();
63 $bold->set_align('left');
64 my $left = $workbook->add_format();
65 $left->set_align('left');
66 $first_sheet->set_column(0,6,30);
68 my $dbh = Mig::db_connect();
69 my $sth = $dbh->prepare("
71 FROM $MIGSCHEMA.$table
74 my $rv = $sth->execute()
75 || die "Error retrieving data from information_schema: $!";
77 my @cols = $sth->fetchrow_array;
80 Mig::db_disconnect($dbh);
82 $first_sheet->write(0,0,'Source File:',$bold);
83 $first_sheet->write(0,1,$file,$left);
84 $first_sheet->write(1,0,'Number of Rows:',$bold);
85 $first_sheet->write(1,1,$count,$left);
87 my $sheet_row_start = 4;
89 $first_sheet->write($sheet_row_start,0,'Legacy Column',$bold);
90 $first_sheet->write($sheet_row_start,1,'Non-Empty Rows',$bold);
91 $first_sheet->write($sheet_row_start,2,'Distinct Non-NULL Values',$bold);
92 $first_sheet->write($sheet_row_start,3,'Min Value',$bold);
93 $first_sheet->write($sheet_row_start,4,'Min Length',$bold);
94 $first_sheet->write($sheet_row_start,5,'Max Value',$bold);
95 $first_sheet->write($sheet_row_start,6,'Max Length',$bold);
103 ###############################################################################
106 my $dbh = Mig::db_connect();
107 my $sth = $dbh->prepare("
109 FROM information_schema.columns
110 WHERE table_schema = " . $dbh->quote($MIGSCHEMA) . "
111 AND table_name = " . $dbh->quote($table) . "
112 ORDER BY dtd_identifier::INTEGER ASC;
114 my $rv = $sth->execute()
115 || die "Error retrieving data from information_schema: $!";
117 my $sheet_row_offset = 0;
119 while (my $data = $sth->fetchrow_hashref) {
120 my $column = $data->{column_name};
121 if ($column eq 'x_source') {
126 && ( $column ne 'x_migrate'
127 && $column ne 'x_source'
128 && $column ne 'x_egid'
129 && $column ne 'x_hseq'
134 my $cdata = column_summary($column);
135 $first_sheet->write($sheet_row_start + $sheet_row_offset,0,$column,$left);
136 $first_sheet->write($sheet_row_start + $sheet_row_offset,1,$cdata->{non_empty_count},$left);
137 $first_sheet->write($sheet_row_start + $sheet_row_offset,2,$cdata->{distinct_value_count},$left);
138 $first_sheet->write($sheet_row_start + $sheet_row_offset,3,$cdata->{min_value},$left);
139 $first_sheet->write($sheet_row_start + $sheet_row_offset,4,$cdata->{min_length},$left);
140 $first_sheet->write($sheet_row_start + $sheet_row_offset,5,$cdata->{max_value},$left);
141 $first_sheet->write($sheet_row_start + $sheet_row_offset,6,$cdata->{max_length},$left);
142 if ($cdata->{distinct_value_count} > 1 && $cdata->{distinct_value_count} <= 500) {
148 Mig::db_disconnect($dbh);
155 my $dbh = Mig::db_connect();
158 my $sth = $dbh->prepare("
160 FROM $MIGSCHEMA.$table
161 WHERE $column IS NOT NULL AND BTRIM($column) <> '';
163 my $rv = $sth->execute()
164 || die "Error retrieving data from $MIGSCHEMA.$table: $!";
165 my @cols = $sth->fetchrow_array;
167 my $non_empty_count = $cols[0];
169 ### distinct_value_count
170 $sth = $dbh->prepare("
171 SELECT COUNT(DISTINCT $column)
172 FROM $MIGSCHEMA.$table;
174 $rv = $sth->execute()
175 || die "Error retrieving data from $MIGSCHEMA.$table: $!";
176 @cols = $sth->fetchrow_array;
178 my $distinct_value_count = $cols[0];
181 $sth = $dbh->prepare("
183 FROM $MIGSCHEMA.$table;
185 $rv = $sth->execute()
186 || die "Error retrieving data from $MIGSCHEMA.$table: $!";
187 @cols = $sth->fetchrow_array;
189 my $min_value = $cols[0];
192 $sth = $dbh->prepare("
193 SELECT MIN(LENGTH($column))
194 FROM $MIGSCHEMA.$table;
196 $rv = $sth->execute()
197 || die "Error retrieving data from $MIGSCHEMA.$table: $!";
198 @cols = $sth->fetchrow_array;
200 my $min_length = $cols[0];
203 $sth = $dbh->prepare("
205 FROM $MIGSCHEMA.$table;
207 $rv = $sth->execute()
208 || die "Error retrieving data from $MIGSCHEMA.$table: $!";
209 @cols = $sth->fetchrow_array;
211 my $max_value = $cols[0];
214 $sth = $dbh->prepare("
215 SELECT MAX(LENGTH($column))
216 FROM $MIGSCHEMA.$table;
218 $rv = $sth->execute()
219 || die "Error retrieving data from $MIGSCHEMA.$table: $!";
220 @cols = $sth->fetchrow_array;
222 my $max_length = $cols[0];
225 non_empty_count => $non_empty_count
226 ,distinct_value_count => $distinct_value_count
227 ,min_value => defined $min_value ? $min_value : '<NULL>'
228 ,min_length => defined $min_length ? $min_length : '<NULL>'
229 ,max_value => defined $max_value ? $max_value : '<NULL>'
230 ,max_length => defined $max_length ? $max_length : '<NULL>'
235 my ($column,$option) = (shift,"GROUP BY 2 ORDER BY 2");
237 print "Sheet: $column\n";
238 my $col_sheet = $workbook->add_worksheet(substr($column,0,31));
239 push @worksheets, $col_sheet;
240 $col_sheet->set_column(0,6,30);
242 my $col_sheet_row_start = 0;
243 my $col_sheet_row_offset = 0;
245 $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,0,'Count',$bold);
247 $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,1,'Source',$bold);
248 $option = "GROUP BY 2,3 ORDER BY 2,3";
251 $col_sheet_row_start + $col_sheet_row_offset
252 ,$has_x_source ? 2 : 1
253 ,"Legacy Value for $column"
257 my $dbh = Mig::db_connect();
261 $sth = $dbh->prepare("
262 SELECT COUNT(*), " . ($has_x_source ? 'x_source, ' : '') . "$column
263 FROM $MIGSCHEMA.$table
266 $rv = $sth->execute()
267 || die "Error retrieving data from $MIGSCHEMA.$table: $!";
269 while (my @cols = $sth->fetchrow_array) {
270 $col_sheet_row_offset++;
271 my $count = $cols[0];
272 $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,0,$count,$left);
275 my $source = defined $cols[1] ? $cols[1] : '<NULL>';
276 $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,1,$source,$left);
277 $value = defined $cols[2] ? $cols[2] : '<NULL>';
278 $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,2,$value,$left);
280 $value = defined $cols[1] ? $cols[1] : '<NULL>';
281 $col_sheet->write($col_sheet_row_start + $col_sheet_row_offset,1,$value,$left);