Thomas York's Random Wiki

# 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";