#!/usr/bin/perl -s
use strict;

our($t,$div);
my $file=shift || "<-" ;
if (!$t and $file =~ m/(.*):(.*)/){ $file = $1; $t = $2}

my %SCK=();
my %KEYS=();
my $table;
my $sqe= [];
my $args;
my $ar;
#undef $/;
$/=";\n";

my $INTO=qr{(?:IGNORE\s+)?INTO};

print STDERR "##...$file,'$t' \n";

open(RRR,">" , "_err");
open(F,$file) or die("cant open $file\n");
my $fimes=0;
my $curtableid="";
while(<F>){

   $args="";
   s/(^|\n)--.*//g;
   if(/INSERT\s+$INTO\s*[`'"](.+?)[`'"]\s*\((.+?)\)\s+VALUES(.*)/s){
       $table = $1;
	   next if($t and $t ne $table);
       $sqe = $2;
       $args= $3;
       $sqe = [ $sqe =~ m{`([^`]*)`}g ];
       if($table ne $curtableid){
          ppnewtable($table,$sqe);
          $curtableid = $table;
       }
   }
   elsif(/INSERT\s+$INTO\s*(\S+?)\s*\((.+?)\)\s+VALUES(.*)/s){
       $table = $1;
	   next if($t and $t ne $table);
       $sqe = $2;
       $args= $3;
       $sqe = [ $sqe =~ m{`([^`]*)`}g ];
##       print "# SCK= ",join('::',@$sqe);
       if($table ne $curtableid){
          ppnewtable($table,$sqe);
          $curtableid = $table;
       }
   }

   elsif(/INSERT\s+$INTO\s*[`'"](.+?)[`'"]\s*VALUES(.*)/s){
       $table = $1;
	   next if($t and $t ne $table);
       $args= $2;
       if($table ne $curtableid){
          ppnewtable($table);
          $curtableid = $table;
       }
   }

   elsif(/INSERT\s+$INTO\s*(\S+?)\s*VALUES(.*)/s){
       $table = $1;
	   next if($t and $t ne $table);
       $args= $2;
       if($table ne $curtableid){
          ppnewtable($table);
          $curtableid = $table;
       }
   }

   elsif(/CREATE\s+TABLE(?:\s+IF\s+NOT\s*EXISTS)?\s*[`'"](.+?)[`'"]\s*\((.*)\)(.*)/s){
     parse_sck($1,$2,$3); 
   }

   elsif(/CREATE\s+TABLE(?:\s+IF\s+NOT\s*EXISTS)?\s+(\w+)\s*\((.*)\)(.*)/s){
     parse_sck($1,$2,$3); 
   }
   else { print RRR "??$_??\n"}


   if($args){
       $ar = parse_tup($args);
       for my $tup(@$ar){
          print join(":: ",@$tup),"\n";
       }
   }

}

close F;

sub parse_tup{
  my $a=shift;
  my @r=();
  my @g=();
  $a =~ s/\\'/\cA/g;
  $a =~ s/\\"/\cB/g;
  $a =~ s/\),\s*\(/)\cC(/g;

  for my $b (split(/\cC/,$a,-1)){
    while($b){
      if(   $b =~ s/^\s*\(// ){ @r=() }
      elsif($b =~ s/^\s*\)// ){
         for(@r){s/\cA/'/g; s/\cB/"/g}
         push(@g,[@r]); 
         @r=(); 
      }

      elsif($b =~ s/^\s*NULL\s*(,|(?=\)))//       ){ push(@r,"")  }
      elsif($b =~ s/^\s*'([^']*)'\s*(,|(?=\)))//  ){ push(@r,$1)  }
      elsif($b =~ s/^\s*"([^"]*)"\s*(,|(?=\)))//  ){ push(@r,$1)  }
      elsif($b =~ s/^\s*([^,)]+?)\s*(,|(?=\)))//  ){ push(@r,$1)  }
         else { $b =~ s/(.)//s ; } ##  print "#???$1\n"; }
    }
  }
  return \@g;
}

sub parse_sck{ my($tableid,$decls,$pos)=@_;
  if($decls=~/^(.*?),\s*(?:PRIMARY\s+)?KEY(.*)$/s){
    my ($a,$b)=($1,$2);
    my @atr=();
    for my $d (split(/\s*,\s*/,$a) ){
       if   ($d =~ /^\s*[`'"](.+?)[`'"]/) {push (@atr,$1);}
       elsif($d =~ /^\s*(\w+)/)            {push (@atr,$1);}
    }
    $SCK{$tableid}=[@atr] if @atr;
    my @ks = ( $b =~ /\((.*?)\)/g);
    $KEYS{$tableid}=[@ks];
 ## print "==",join("::",@atr),"\n#table: $tableid\n# keys:",join(" ",@ks),"\n";
  }
  else{
    my ($a)=($decls);
    my @atr=();
    for my $d (split(/\s*,\s*/,$a) ){
       if   ($d =~ /^\s*[`'"](.+?)[`'"]/) {push (@atr,$1);}
       elsif($d =~ /^\s*(\w+)/)            {push (@atr,$1);}
    }
    $SCK{$tableid}=[@atr] if @atr;
 ## print join("::",@atr),"\n#$tableid\n";
  }
}

sub ppnewtable{ my ($tab,$s)=@_;
  print "\n\n#####################( $tab )\n\n" unless ($t or $div);
  if($div){ open(my $F,">","$file--$table") or die;
    select $F;
  }
  if($s)         { print join('::',@$s),"\n";}
  elsif($SCK{$tab}){ print join('::',@{$SCK{$tab}}),"\n";}
  else           { print "\n";}
  print "### $tab\n"  ;
}

__END__



__END__

=encoding utf8

=head1 NAME

sql2tab - convert sql-text-dumps to textual tables

=head1 SYNOPSIS

 sql2tab [option] file.sql
 sql2tab [option] file.sql:tabid
 options:
   -div                 creates a "file--tabid" for each table
   -t=tabid

=head1 DESCRIPTION

=head2 EXPORT

=head1 AUTHOR

J.Joao Almeida, jj@di.uminho.pt

=head1 SEE ALSO

perl(1).

=cut