# Access2PSQL Create file Syntax Converter # Version 0.1 # Written by Thomas York # # Variables $| = 0; use strict; use warnings; use File::Slurp; my $appname = "Access2PSQL-Perl"; my $version = "0.1"; my $createinfile = "esql_create.txt"; my $createoutfile = "esql_create_fixed.sql"; my $delinfile = "esql_del.txt"; my $deloutfile = "esql_del_fixed.sql"; my $addinfile = "esql_add.txt"; my $addoutfile = "esql_add_fixed.sql"; my $schema = "public"; my $line = 0; my $mod = 2; my @createsqlfile; my @createmodsqlfile; my @delsqlfile; my @delmodsqlfile; my @addsqlfile; my @addmodsqlfile; my $key; ### CREATE FILE # Read data in to memory print "Loading $createinfile in to memory\n"; @createsqlfile = read_file($createinfile) or die "Unable to open $createinfile\n"; print "Starting data manipulation on $createinfile\n"; # Manipulate the create data, line by line foreach $key (@createsqlfile) { $line++; # Remove ALL lines that start with comments if ($key =~ m/^--.+/) { print "Removing comment from line $line\n"; $mod++; next; } # Remove comments from the middle/end of the line if ($key =~ /\-\-/) { print "Removing comment from line $line\n"; if ($key =~/,/) { # If the line has a comma.. $key =~ m/^(.*?)--.*/; push(@createmodsqlfile, $1 . ",\n"); } else { $key =~ m/^(.*?)--.*/; push(@createmodsqlfile, $1 . "\n"); } $mod++; next; } # Convert Access booleans to Postgres booleans if ($key =~ /^.+bool DEFAULT.+/) { print "Fixing boolean on line $line\n"; $key =~ s/bool DEFAULT 1/bool DEFAULT TRUE/; push(@createmodsqlfile, $key); $mod++; next; } # Convert Date/Time field if ($key =~ /DATETIME DEFAULT Now\(\)/) { print "Fixing Datetime field on line $line\n"; $key =~ s/DATETIME DEFAULT Now\(\)/timestamp/; $mod++; if ($key =~ / NOT NULL/) { # If NOT NULL exists.. print "Removing NOT NULL from Datetime field on line $line\n"; $key =~ s/ NOT NULL//; # REMOVE IT! (Well..replace it with nothing) $mod++; } push(@createmodsqlfile, $key); next; } elsif ($key =~ /DATETIME/) { print "Fixing Datetime field on line $line\n"; $key =~ s/DATETIME/timestamp/; $mod++; if ($key =~ / NOT NULL/) { # If NOT NULL exists.. print "Removing NOT NULL from Datetime field on line $line\n"; $key =~ s/ NOT NULL//; # REMOVE IT! (Well..replace it with nothing) $mod++; } push(@createmodsqlfile, $key); next; } # Remove Unique index fields if ($key =~ /UNIQUE/) { print "Removing Unique Index on line $line\n"; $mod++; if ($createsqlfile[$line] =~ / \)/) { # If the next line is the end of the SQL statement, we need to DELETE the comma from the PREVIOUS line print "Fixing SQL statement due to Unique Index removal on line $line\n"; my $lenmodsqlfile = scalar(@createmodsqlfile) - 1; # Length of the rebuilt array, with an adjustment for a index of 0 $createmodsqlfile[$lenmodsqlfile] =~ s/,//; # Comma replacement $mod++; } next; } # Fix fields with the name User # We CANNOT use this as User is a built in Postgres type # We convert User to UserField if ($key =~ m/^\s+User\s+\S+/) { print "Converting User field to UserField on line $line\n"; $key =~ s/User /UserField/; push(@createmodsqlfile, $key); $mod++; next; } # If the line doesn't need modification, pass it along # This line needs to be LAST push(@createmodsqlfile, $key); } # Add schema selection to the output files unshift(@createmodsqlfile, "\nSET search_path TO $schema;\n"); # Add some comment headers to the output files unshift(@createmodsqlfile, "-- File cleaned up by $appname v$version ($mod modifications were made)\n-- Written by Thomas York\n"); # Save output files print "Opening $createoutfile for file saving\n"; write_file($createoutfile, @createmodsqlfile) or die "Unable to open $addoutfile\n"; ### DELETE FILE print "Loading $delinfile in to memory\n"; @delsqlfile = read_file($delinfile) or die "Unable to open $delinfile\n"; $line = 0; $mod = 2; # Manipulate the del data, line by line foreach $key (@delsqlfile) { $line++; # Remove ALL lines that start with comments if ($key =~ m/^--.+/) { print "Removing comment from line $line\n"; $mod++; next; } # Remove comments from the middle/end of the line if ($key =~ /\-\-/) { print "Removing comment from line $line\n"; if ($key =~/,/) { # If the line has a comma.. $key =~ m/^(.*?)--.*/; push(@delmodsqlfile, $1 . ",\n"); } else { $key =~ m/^(.*?)--.*/; push(@delmodsqlfile, $1 . "\n"); } $mod++; next; } # If the line doesn't need modification, pass it along # This line needs to be LAST push(@delmodsqlfile, $key); } # Add schema selection to the output files unshift(@delmodsqlfile, "\nSET search_path TO $schema;\n"); # Add some comment headers to the output files unshift(@delmodsqlfile, "-- File cleaned up by $appname v$version ($mod modifications were made)\n-- Written by Thomas York\n"); # Save output files print "Opening $deloutfile for file saving\n"; write_file($deloutfile, @delmodsqlfile) or die "Unable to open $deloutfile\n"; ### ADD FILE print "Starting modifications to $addinfile on the fly\n"; open (my $rfh, '<', $addinfile) or die "Unable to open $addinfile\n"; open (my $wfh, '>', $addoutfile) or die "Unable to open $addoutfile\n"; $line = 0; $mod = 2; # Add some comment headers to the output file print $wfh "-- File cleaned up by $appname v$version ($mod modifications were made)\n-- Written by Thomas York\n"; # Add schema selection to the output file print $wfh "\nSET search_path TO $schema;\n"; # Manipulate the add data, line by line while (<$rfh>) { $key = $_; $line++; # Remove ALL lines that start with comments if ($key =~ m/^--.+/) { print "Removing comment from line $line\n"; $mod++; next; } # If the line doesn't need modification, pass it along # This line needs to be LAST print $wfh $key; } # Close files close ($rfh); close ($wfh); print "\nProgram run is complete\nHave a nice day!\n";