#!/usr/bin/perl -w # NAME: extractflts.pl # AIM: VERY SPECIFIC - extract a set of transactions from a database dump use strict; use warnings; use File::Basename; # split path ($name,$dir,$ext) = fileparse($file [, qr/\.[^.]*/] ) use Cwd; my $os = $^O; my $perl_dir = '/home/geoff/bin'; my $PATH_SEP = '/'; my $temp_dir = '/tmp'; if ($os =~ /win/i) { $perl_dir = 'C:\GTools\perl'; $temp_dir = $perl_dir; $PATH_SEP = "\\"; } unshift(@INC, $perl_dir); require 'lib_utils.pl' or die "Unable to load 'lib_utils.pl' Check paths in \@INC...\n"; # log file stuff our ($LF); my $pgmname = $0; if ($pgmname =~ /(\\|\/)/) { my @tmpsp = split(/(\\|\/)/,$pgmname); $pgmname = $tmpsp[-1]; } my $outfile = $temp_dir.$PATH_SEP."temp.$pgmname.txt"; open_log($outfile); # user variables my $VERS = "0.0.1 2012-12-13"; my $load_log = 0; my $in_file = ''; my $verbosity = 0; my $out_file = ''; # ### DEBUG ### my $debug_on = 1; my $def_file = 'C:\Users\user\Downloads\temp\tempd'; ### program variables my @warnings = (); my $cwd = cwd(); sub VERB1() { return $verbosity >= 1; } sub VERB2() { return $verbosity >= 2; } sub VERB5() { return $verbosity >= 5; } sub VERB9() { return $verbosity >= 9; } sub show_warnings($) { my ($val) = @_; if (@warnings) { prt( "\nGot ".scalar @warnings." WARNINGS...\n" ); foreach my $itm (@warnings) { prt("$itm\n"); } prt("\n"); } else { prt( "\nNo warnings issued.\n\n" ) if (VERB9()); } } sub pgm_exit($$) { my ($val,$msg) = @_; if (length($msg)) { $msg .= "\n" if (!($msg =~ /\n$/)); prt($msg); } show_warnings($val); close_log($outfile,$load_log); exit($val); } sub prtw($) { my ($tx) = shift; $tx =~ s/\n$//; prt("$tx\n"); push(@warnings,$tx); } sub strip_qts($) { my $txt = shift; if ($txt =~ /^'(.+)'$/) { $txt = substr($txt,1,length($txt)-2); } return $txt; } # INSERT INTO "flights" VALUES(1,'1355164926000','AVA0476','il-96-400','OPEN'); # and # INSERT INTO "positions" VALUES(1,'1355164926000','2012-12-10 18:42:06','46.1595902','5.5831001',493,26503,136); # sprintf(psb->stmt[2],"INSERT INTO 'positions' " # " (fid,ts,lat,lon,alt_ft,spd_kts,hdg) " # " VALUES ('%" PFX64 "','%s','%s','%s',%d,%d,%d);", sub process_in_file($) { my ($inf) = @_; if (! open INF, "<$inf") { pgm_exit(1,"ERROR: Unable to open file [$inf]\n"); } my @lines = ; close INF; my $lncnt = scalar @lines; prt("Processing $lncnt lines, from [$inf]...\n"); my ($line,$itm,$lnn,$prev1,$prev2,@arr,$itm2,$cnt,$i); my ($id,$cs,$ac,$status,$tmp); my ($fid,$tm,$lat,$lon,$alt,$spd,$hdg); my (@narr,@narr2,$cnt2,$tmp2,$j); $lnn = 0; $prev1 = ''; $prev2 = ''; my @flights = (); my @positions = (); foreach $line (@lines) { chomp $line; $lnn++; if ($line =~ /^\s*INSERT INTO "flights" VALUES\((.+)\);\s*$/) { $itm = $1; if ($itm ne $prev1) { @arr = split(',',$itm); $cnt = scalar @arr; $itm2 = ''; for ($i = 1; $i < $cnt; $i++) { $itm2 .= "," if (length($itm2)); $itm2 .= $arr[$i]; $narr[$i - 1] = strip_qts($arr[$i]); } if ($itm2 ne $prev1) { #prt("$id $cs $itm2\n"); #prt("$id $cs ".join(" ",@narr)."\n"); $tmp = join(",",@narr); #push(@flights,[$tmp]); push(@flights,[@narr]); #prt("$tmp\n"); } $prev1 = $itm2; } } elsif ($line =~ /^\s*INSERT INTO "positions" VALUES\((.+)\);\s*$/) { $itm = $1; if ($itm ne $prev2) { @arr = split(',',$itm); $cnt = scalar @arr; $itm2 = ''; for ($i = 1; $i < $cnt; $i++) { $itm2 .= "," if (length($itm2)); $itm2 .= $arr[$i]; $narr2[$i - 1] = strip_qts($arr[$i]); } if ($itm2 ne $prev2) { $tmp = join(",",@narr2); #push(@positions,[$tmp]); push(@positions,[@narr2]); #prt("$itm2\n"); } $prev2 = $itm2; } } } $cnt = scalar @flights; $cnt2 = scalar @positions; my @flts = (); my @posn = (); my ($m1,$m2); prt("Have $cnt flights, with $cnt2 positions...\n"); for ($i = 0; $i < $cnt; $i++) { $tmp = $flights[$i]; $id = ${$tmp}[0]; $m1 = '{"'.join('","',@{$tmp}).'"},'; prt("$m1\n"); push(@flts,$m1); for ($j = 0; $j < $cnt2; $j++) { $tmp2 = $positions[$j]; $fid = ${$tmp2}[0]; if ($id == $fid) { $m2 = '{"'.join('","',@{$tmp2}).'"},'; #prt(" ".join(" ",@{$tmp2})."\n"); prt(" $m2\n"); push(@posn,$m2); } } } $cnt = scalar @flts; prt("const char *sFlights[$cnt][4] = {\n"); prt(join("\n",@flts)."\n"); prt("};\n"); $cnt = scalar @posn; prt("const char *sPosns[$cnt][7] = {\n"); prt(join("\n",@posn)."\n"); prt("};\n"); } ######################################### ### MAIN ### parse_args(@ARGV); process_in_file($in_file); pgm_exit(0,""); ######################################## sub need_arg { my ($arg,@av) = @_; pgm_exit(1,"ERROR: [$arg] must have a following argument!\n") if (!@av); } sub parse_args { my (@av) = @_; my ($arg,$sarg); while (@av) { $arg = $av[0]; if ($arg =~ /^-/) { $sarg = substr($arg,1); $sarg = substr($sarg,1) while ($sarg =~ /^-/); if (($sarg =~ /^h/i)||($sarg eq '?')) { give_help(); pgm_exit(0,"Help exit(0)"); } elsif ($sarg =~ /^v/) { if ($sarg =~ /^v.*(\d+)$/) { $verbosity = $1; } else { while ($sarg =~ /^v/) { $verbosity++; $sarg = substr($sarg,1); } } prt("Verbosity = $verbosity\n") if (VERB1()); } elsif ($sarg =~ /^l/) { if ($sarg =~ /^ll/) { $load_log = 2; } else { $load_log = 1; } prt("Set to load log at end. ($load_log)\n") if (VERB1()); } elsif ($sarg =~ /^o/) { need_arg(@av); shift @av; $sarg = $av[0]; $out_file = $sarg; prt("Set out file to [$out_file].\n") if (VERB1()); } else { pgm_exit(1,"ERROR: Invalid argument [$arg]! Try -?\n"); } } else { $in_file = $arg; prt("Set input to [$in_file]\n") if (VERB1()); } shift @av; } if ((length($in_file) == 0) && $debug_on) { $in_file = $def_file; prt("Set DEFAULT input to [$in_file]\n"); $load_log = 1; } if (length($in_file) == 0) { pgm_exit(1,"ERROR: No input files found in command!\n"); } if (! -f $in_file) { pgm_exit(1,"ERROR: Unable to find in file [$in_file]! Check name, location...\n"); } } sub give_help { prt("$pgmname: version $VERS\n"); prt("Usage: $pgmname [options] in-file\n"); prt("Options:\n"); prt(" --help (-h or -?) = This help, and exit 0.\n"); prt(" --verb[n] (-v) = Bump [or set] verbosity. def=$verbosity\n"); prt(" --load (-l) = Load LOG at end. ($outfile)\n"); prt(" --out (-o) = Write output to this file.\n"); } # eof - template.pl