#!/usr/bin/perl

=head1 DESCRIPTION

This program filters a Bacula SQLite3 database dump as produced by the /etc/bacula/scripts/make_catalog_backup.pl script and turns
it into a format suitable for importing into a newly created PostgreSQL Bacula database instance.
The data is then loaded into the PostgreSQL database.
The SEQUENCE settings are updated to reflect where the SERIAL type columns are up to.
Any existing Bacula data in the PostgreSQL database will be removed, so this is only suitable for use on a clean install of Bacula.

The dump filename is to be passed as a command line parameter, or its contents piped to this program.

All DDL is thrown away, so the PostgreSQL Bacula database must be created before the results from this script are imported into it.
Also some Bacula tables are ignored since their contents should have been created correctly when the Bacula DB was created.

At this stage the "Log" table is also skipped because it can contain multi-line entries and the code doesn't cater for that.
Old log messages are probably not that important anyway?

Files generated by this script are created in the current directory, so make sure it has enough room.
You can clean up all generated table.* files once this script is complete.

This script was tested on a Debian system with Bacula version 5.0.2; PostgreSQL 9.0.3; and a dump from SQLite3 3.7.5.

=cut

use warnings qw(all);
use strict;

# Flush output immediately.
#
$| = 1;

# Subroutine to open a file for a table if it isn't open already.
# It takes a file handle and table name as parameters.
# It'll add some statements to the top of the file to begin with.
#
sub open_table_file
{
	my ($fh, $table) = @_;

	# Return the existing file handle if it is already open and ready for use.
	#
	return $fh if defined $fh and fileno $fh;

	# Open the file.
	#
	die "No table name supplied" if not defined $table or $table eq "";
	my $filename = "table.$table";
	die "Couldn't open $filename for writing: $!" if not open $fh, "> $filename";

	# Write out statements to the start of the file.
	# - The 1st statement tells PostgreSQL to take backslashes in strings literally.
	# - The 2nd statement starts a transaction.
	# - The 3rd statement will clear out any entries in the table.
	#
	print $fh "set standard_conforming_strings=on;\n";
	print $fh "BEGIN;\n";
	print $fh "TRUNCATE $table;\n";

	return $fh;
}

#-----------------------------------------------------------------------
# Mainline

die "Must be run by the bacula user." if "bacula" ne (getpwuid($>))[0];

# Hashes to keep track of file handles and maximum sequence numbers for tables.
#
my %filehandle;
my %sequence;

print "Generating SQL files for each table...";
while(<>)
{
	chomp;

	# We are only interested in INSERT INTO statements.
	#
	next if ! /^\s*INSERT\s+INTO\s+/i;

	# Get the table name from the statement and convert to lowercase.
	# Skip some tables.
	#
	(my $table = $_) =~ s/^\s*INSERT\s+INTO\s+"?([^"\s]+)"?\s+.*/\L$1/i;
	next if $table =~ /^(log|nextid|status|version)$/;

	# Open the table SQL file if necessary.
	#
	$filehandle{$table} = &open_table_file($filehandle{$table}, $table);
	my $fh = $filehandle{$table};

	# Fix up the statement so that the table name is not surrounded by quotes.
	#
	s/^\s*(INSERT\s+INTO)\s+"$table"\s+(.*)$/$1 $table $2/i;

	# Fix up badly formatted data.
	#
	if ($table eq "job")
	{
		# TIMESTAMP type columns cannot accept 0, so change to NULL instead.
		#
		s/^\s*(INSERT\s+INTO\s+Job\s+VALUES\s*\(([^,]+,){7})0,/$1NULL,/i;
		s/^\s*(INSERT\s+INTO\s+Job\s+VALUES\s*\(([^,]+,){8})0,/$1NULL,/i;
		s/^\s*(INSERT\s+INTO\s+Job\s+VALUES\s*\(([^,]+,){9})0,/$1NULL,/i;
		s/^\s*(INSERT\s+INTO\s+Job\s+VALUES\s*\(([^,]+,){10})0,/$1NULL,/i;
	}
	elsif ($table eq "jobmedia")
	{
		# The JobMedia table in the SQLite3 database contains a 'Copy' column on the end while the PostgreSQL one doesn't.
		# If there are too many columns being inserted remove the last one.
		#
		s/^\s*(INSERT\s+INTO\s+JobMedia\s+VALUES\s*\(([^,]+,){9}[^,]+)(,[^\)]+)?\s*(\)\s*;)\s*$/$1$4/i;
	}
	elsif ($table eq "media")
	{
		# TIMESTAMP type columns cannot accept 0, so change to NULL instead.
		#
		s/^\s*(INSERT\s+INTO\s+Media\s+VALUES\s*\(([^,]+,){7})0,/$1NULL,/i;
		s/^\s*(INSERT\s+INTO\s+Media\s+VALUES\s*\(([^,]+,){8})0,/$1NULL,/i;
		s/^\s*(INSERT\s+INTO\s+Media\s+VALUES\s*\(([^,]+,){9})0,/$1NULL,/i;
		s/^\s*(INSERT\s+INTO\s+Media\s+VALUES\s*\(([^,]+,){38})0,/$1NULL,/i;

		# Empty volume status needs to be fixed.
		# I'm not sure what to set this to, but Append seems like the best choice?
		# That way the tape will try to be written to and if it is Full it will be marked so,
		# or if there is a problem then hopefully it'll be marked in Error.
		#
		s/^\s*(INSERT\s+INTO\s+Media\s+VALUES\s*\(([^,]+,){19})'',/$1'Append',/i;
	}

	# Write the statement to the table file.
	#
	print $fh "$_\n";

	# Keep track of the highest sequence number we've encountered for certain tables.
	# For Bacula, the serial type column is always the first one in the table.
	#
	next if $table !~ /^(basefiles|client|file|filename|fileset|job|jobmedia|location|log|media|path|pool)$/;
	(my $seq = $_) =~ s/^\s*INSERT\s+INTO\s+\S+\s+VALUES\s*\(\s*([^,]+),.*$/$1/i;
	$sequence{$table} = 0 if not defined $sequence{$table};
	$sequence{$table} = $seq if $seq > $sequence{$table};
}
print "\n";

# Some tables contain a serial type column.
# For these, add a SQL statement to make sure new rows don't duplicate the serial number of existing rows.
#
print "Adding SQL to update SEQUENCE numbers...";
foreach my $table (keys %sequence)
{
	# Get the file handle for the table and the sequence number to be used for the next row inserted into the table.
	#
	my $fh = $filehandle{$table};
	my $seq = $sequence{$table} + 1;

	# The serial type column in all the tables is called ${table}id except for the basefiles table.
	#
	my $column = $table . "id";
	$column = "baseid" if $table eq "basefiles";

	# The serial sequences are stored in ${table}_${column}_seq.
	# Add SQL to update the values.
	#
	print $fh "ALTER SEQUENCE $table" . "_" . "$column" . "_seq RESTART WITH $seq;\n";
}
print "\n";

# Add a COMMIT statement to the end of each file to finish the transaction; close them off; and process them as we go.
#
foreach my $table (sort keys %filehandle)
{
	my $fh = $filehandle{$table};
	print $fh "COMMIT;\n";
	close $filehandle{$table};

	print "Processing the $table table...";
	system "psql -q -d bacula -f table.$table";
	print "\n";
}

exit 0;