root/rita/tools/generate-migration-from-postgres-dump.pl

#!/usr/bin/perl

use strict;
use warnings;

open FILE, "< $ARGV[0]" or die "Usage: $0 <file>\nFailed: $!";

my %table_pk;
my %table_fk;
my %table_unique;

while (my $line = <FILE>)
{
  next unless $line =~ /ALTER TABLE ONLY (\S+)/;
  my $table = $1;
  
  my $constraint = <FILE>; # read next line

  if ($constraint =~ /^ {4}ADD CONSTRAINT (\S+) (.*);$/)
  {
    my $name = $1;
    my $body = $2;
    
    if ($body =~ /^UNIQUE \((.*)\)$/)
    {
      my @unique_fields = split m{, }, $1;
      $table_unique{$table}{$name} = \@unique_fields;
    }
    elsif ($body =~ /PRIMARY KEY \((.*)\)$/)
    {
      my @pk_fields = split m{, }, $1;
      $table_pk{$table} and die "duplicate pk for table $table";
      $table_pk{$table} = { map {$_ => 1} @pk_fields };
    }
    elsif ($body =~ /FOREIGN KEY \(([^)]+)\) REFERENCES (.+)\(([^)]+)\)/)
    {
      # ON UPDATE RESTRICT ON DELETE RESTRICT;
      $table_fk{$table}{$name} = [$1,$2,$3];
    }
    else
    {
      warn "Ignored unknown constraint on $table: $body";
    }
  }
  else
  {
    warn "Ignored unknown ALTER TABLE on $table: $constraint";
  }
}

close FILE;
open FILE, "< $ARGV[0]" or die;     

sub create_index ($$$)
{
  my ($index_name, $table, $fields) = @_;
  return "addIndex(uniqueIndex(\"$index_name\", \"$table\", ".
    join(",", map {"\"$_\""} @$fields) .
    "));\n";
}

my ($table, $pk, $fk, $unique, $first_column);

while (<FILE>)
{  
  if (/CREATE TABLE (.*) \(/)
  {
    $table = $1;
    print "createTable(table(\"$table\",\n";
    $pk = $table_pk{$table};
    $fk = $table_fk{$table};
    $unique = $table_unique{$table};
    $first_column = 1;
  }
  elsif (/CREATE UNIQUE INDEX (\S+) ON (\S+) .* \((.*)\);/)
  {
    my $name = $1;
    my $table = $2;
    my @unique_fields = split m{, }, $3;
    print create_index($name, $table, \@unique_fields);
  }
  elsif (m{ # extended regular expression, whitespace and comments ignored
    ^ # start of line
    \s{4} # four space indent
    (\S+) # one word, column name, group 1
    \s # space
    ( # start of group 2 (the entire type)
      [^ ,(]+ # a word without a comma (not "SMALLINT,") or an open parenthesis
      | # or
      ( # start of group 3 (the entire type if it contains parentheses)
        ([^(]+) # anything that doesn't contain parentheses (type name), group 4
        \( # followed by an open para
        (\d+) # followed by a number, group 5 (length or precision)
        ( # optionally followed by, group 6
          , # a comma
          (\d+) # and another number, group 7 (scale)
        )? # group 6 is optional
        \) # end parenthesis
      ) # end group 3 (the entire type if it contains parentheses)
      | # or
      timestamp \s without \s time \s zone # exactly "timestamp without time zone"
    ) # end of group 2
    (\s DEFAULT \s # group 8, optional DEFAULT clause
      (\S+) # group 9, default value
    )? # end of optional group 8
    (\s NOT \s NULL)? # group 10, optional "NOT NULL" option
    (,)? # optional comma at end of line (not present for last column), group 11
    $ # end of line/newline
  }x)
  {
    # start a column, include the quoted name
    $_ = "\tcolumn(\"$1\", ";
    
    # convert type
    if ($4 and lc($4) eq "character varying")
    {
      $_ .= "VARCHAR";
    }
    elsif ($4 and lc($4) eq "text")
    {
      $_ .= "CLOB";
    }
    elsif ($4 and lc($4) eq "character")
    {
      $_ .= "CHAR";
    }
    elsif (lc($2) eq "timestamp without time zone")
    {
      $_ .= "TIMESTAMP";
    }
    elsif ($4)
    {
      $_ .= uc($4); # wild guess
    }
    else
    {
      $_ .= uc($2); # wild guess
    }
    
    # length/precision
    if ($4 and ($4 eq "decimal" or $4 eq "numeric")) # only types that have precision?
    {
      $_ .= ", precision($5)";
    }
    elsif ($5)
    {
      $_ .= ", length($5)";
    }
    
    # scale, optional
    if ($7)
    {
      $_ .= ", scale($7)";
    }
    
    # default option
    if ($8)
    {
      $_ .= ", defaultValue($9)";
    }
    
    # notnull option
    if ($10)
    {
      $_ .= ", notnull()";
    }
    
    if ($pk and $pk->{$1})
    {
      $_ .= ", primarykey()";
    }
    
    # end of line and column definition
    $_ .= ")";
    
    if ($first_column)
    {
      $first_column = 0;
    }
    else
    {
      print ",\n";
    }
    
    print $_;
  }
  elsif (/^ {4}CONSTRAINT /)
  {
    # migrate4j doesn't support this, ignore it
  }
  elsif (/^ {4}ADD CONSTRAINT /)
  {
    # already parsed and processed
  }
  elsif (/^ {4}/) # looks like a column
  {
    warn "Ignored line: $_";
  }
  elsif (/^\);$/)
  {
    print "\n\t));\n";

    if ($unique)
    {
      foreach my $index_name (keys %$unique)
      {
        my $fields = $unique->{$index_name};
        print create_index($index_name, $table, $fields);
      }
    }

    if ($fk)
    {
      foreach my $fk_name (keys %$fk)
      {
        my $fk_info = $fk->{$fk_name};
        my ($parent_field, $child_table, $child_field) = @$fk_info;
        my @parent_fields = split ', *', $parent_field;
        my @child_fields  = split ', *', $child_field;
        
        if (@parent_fields > 1 or @child_fields > 1)
        {
          print "addForeignKey(new ForeignKey(\"$fk_name\",".
            "\n\t\"$table\", ".
            "\n\tnew String[]{".join(',', map {"\"$_\""} @parent_fields)."}, ".
            "\n\t\"$child_table\", ".
            "\n\tnew String[]{".join(',', map {"\"$_\""} @child_fields)."}".
            "));\n";
        }
        else
        {
          print "addForeignKey(new ForeignKey(\"$fk_name\", ".
            "\n\t\"$table\", \"$parent_field\"," .
            "\n\t\"$child_table\", \"$child_field\"));\n";
        }
      }
    }
    
    print "\n";
  }
}

exit 0;