utils/mssql_psql_conversion.pl

   1 #!/usr/bin/perl
   2
   3 #require perl5.10;
   4
   5 use warnings;
   6 use strict;
   7
   8 my $temp = '';
   9 while (<>) {
  10     # only add "" if we actually need them
  11     s/\[([^]]+)\]/$1 =~ m{\W}?qq("$1"):$1/eg;
  12     s/^\s*GO$/;/;
  13     s/tinyint/smallint/;
  14     s/binary(?:\s*\([^)]+\))?/bytea/ig;
  15     s/smalldatetime/TIMESTAMP/ig;
  16     s/DATETIME/TIMESTAMP/ig;
  17     s/(?:NON)?CLUSTERED\s*//g;
  18     s/\s*ASC\s*//g;
  19     s/int\s*IDENTITY\s*\(\d+,\s*\d+\)/SERIAL/ig;
  20     # mssql uses stupid names for indexes apparently; ditch them and
  21     # let pgsql choose
  22     s/(CREATE\s+(?:UNIQUE\s+)?INDEX\s+)\S+\s+(ON\s+)/$1$2/gi;
  23     # set defaults properly
  24 #    use re 'debug';
  25     s/(?<altertable>ALTER\s+TABLE\s+\S+) # table name
  26       \s+ADD\s+CONSTRAINT\s+
  27       \S+\s+DEFAULT\s+
  28       (?<function>\((?<unbraceddef>(?:[^\(\)]++|(?&function))*)\)) # default value
  29       \s+FOR\s+
  30       (?<column>\S+) # column
  31       /$+{altertable} ALTER COLUMN $+{column} SET DEFAULT $+{unbraceddef};/gix;
  32     s/GETDATE\(\)/NOW()/gix;
  33     $temp .= $_;
  34 }
  35 $temp =~ s/\r//g;
  36
  37 # cleanup \n; madness
  38 $temp =~ s/\n(;)/$1/g;
  39 $temp =~ s/\n\n+/\n/g;
  40 print $temp;