#!/usr/bin/perl

# LGPL, Alex Pilosov

$out="";
while (<>)
{
# poor man's parser
  if (/^[^']*#/) {$_=$`;}
  if (/^\s*$/) {next; }
  s#'(\d\d\d\d)-(\d\d)-(\d\d)#'$2/$3/$1#g;

  if (/LOCK TABLES/) {} #matches UNLOCK too
  elsif (/CREATE TABLE\s(\S+)/) { 
    $current_table=$1;
    $in_table=1; $s=$_; $table_pre=$table_post=""; @fields=();
    $pre.="DROP TABLE ".$1.";\n";
  }
  elsif ($in_table && /\;/) {
    $pre.=$table_pre;
    $pre.=$s;
    $pre.=join(",\n",@fields);
    $pre.=$_;
    $pre.=$table_post;
    $in_table=0;
  }
  elsif ($in_table) {
#this is cheesy but as long as it works
# length int(11) DEFAULT '0' NOT NULL,
s/\s*,\s+$//; # cut off trailing comma
    if (/^\s*(\S+)\s+(\S+)\s*(DEFAULT\s+(\S+))?\s*(NULL)?(NOT NULL)?\s*(auto_increment)?(.*)/) { 
      $field=$1;
      $datatype=$2;
      $default_all=$3 or "";
      $default=$4 or "";
      $null=$5 or "";
      $notnull=$6 or "";
      $autoincr=$7 or "";
      $tail=$8 or "";
      if ($datatype eq q|enum('true','false')|) {$datatype="boolean";}
      elsif ($field eq "KEY") {next;} # no indices,just ignore that, for now
      elsif ($field eq "UNIQUE") {
	s/^\s*(\S+)\s+(\S+)/$1 /;
        push @fields,$_; next;
      }
      elsif ($field eq "PRIMARY") {
        push @fields,$_; next;
      } # just ignore key fields
      elsif ($datatype=~/int/) { 
        ($length)=($datatype=~/\S\((\d+)\)/);
	if ($autoincr) {
          $datatype="serial";$notnull="";$null="";
	  $default_all="";
	  $seq_name=$current_table."_".$field;
	  $table_pre.="DROP SEQUENCE $seq_name"."_seq;\n";
	  $table_pre.="DROP FUNCTION $seq_name"."_max();\n";
	  $post.="
CREATE FUNCTION $seq_name"."_max() RETURNS INT4
    AS 'SELECT max($field) FROM $current_table' 
    LANGUAGE 'sql';
SELECT setval('$seq_name"."_seq',$seq_name"."_max());"
	}
        elsif ($length<=4) { $datatype="int2";}
        elsif ($length<=11) { $datatype="int4";}
        else { $datatype="numeric($length)";}
      }
      elsif ($datatype=~/timestamp/) { $datatype='timestamp'; }
      elsif ($datatype=~/blob/) { $datatype='text'; }
      elsif ($datatype=~/enum/) { $datatype='CHAR(5)'; }
      elsif ($datatype=~/text/) { $datatype='text'; }
      push @fields,"$field $datatype $default_all $null $notnull";
    }
    else { warn "Cannot parse field definition: $_"; $s.=$_; }
  }
  else { 
  $out.=$_; 
}
}
print $pre;
print "BEGIN;";
print $out;
print "END;";
print $post;