MAIN2bender

#!/usr/bin/perl -w
# This code is a part of Slash, and is released under the GPL.
# Copyright 1997-2005 by Open Source Technology Group. See README
# and COPYING for more information, or see http://slashcode.com/.
# $Id$

use strict;
use Getopt::Std;
use File::Basename;

(my $PROGNAME) = basename($0);
(my $VERSION) = ' $Revision$ ' =~ /\$Revision:\s+([^\s]+)/;

my %opts;
# Remember to doublecheck these match usage()!
usage('Options used incorrectly')
	unless getopts('Y:Z:f:F:w:W:x:X:H:?kKhv', \%opts);
usage() if ($opts{'h'} || !keys %opts);
version() if $opts{'v'};

use DBI;
use Digest::MD5 'md5_hex';
use vars qw(%my_conf);

# Handle possibility of unexpected interruption.
$SIG{INT} = sub { die "Interrupted.\n"; };

my(%aidCache) = ( '' => 0 );
# Show usage under the necessary conditions.
usage() if 	$opts{'?'} || $opts{h} || !keys(%opts) ||
			($opts{Z} && ($opts{x} || $opts{X} || $opts{H}));
{
	my @options = qw[x X w W];
	my ($a, $b);

	do {
		($a, $b) = (shift @options, shift @options);

		if 	((exists $opts{$a} && !exists $opts{$b}) ||
			 (!exists $opts{$a} && exists $opts{$b})) 
		{
			print "Improper use of $a/$b! Both must be specified.\n\n";
			usage();
		}
		if ($opts{$a} && !length($opts{$b})) {
			printf "Enter %s password: ", ($b eq 'x') ? 'slave':'master';
			chomp($opts{$b} = <STDIN>);
		}
	} until $b eq 'P';
}

# Perform sensible tilde expansion.
my(@fileopts) = qw[Y f Z F];
map {
	$opts{$_} =~ s/^\~(\w+)?/(($1) ? getpwnam($1):getpwuid($<))[7]/e
		if $opts{$_}; 
} @fileopts;

my ($tempDBname, $g_dbh, $dbh, @rtables, @otables);
my ($prefix, $dumpfile, $rcfile, $outfile, $g_user, $g_pwd, $t_user, $t_pwd,
	$dbhost) = @opts{qw[Y f Z F w W x X H]};
$prefix 	||= '/usr/local/slash';
$dumpfile	||= './site.dump';
$rcfile 	||= './slashdotrc.pl';
$outfile	||= './bender.dump';
$dbhost		||= 'localhost';

# Main program logic.
{
	if (!$t_user) {
		die "FATAL ERROR: Can't read $rcfile: $!\n" if ! -r $rcfile;
		$rcfile =~ s!^(.+)/!!;
		unshift(@INC, $1 || '.');
		require $rcfile;
		*my_conf = $Slash::conf{DEFAULT};
		$Slash::conf{DEFAULT} = undef;

		# Grab any necessary defaults from the rc file.
		$g_user	||= $my_conf{dbuser};
		$g_pwd 	||= $my_conf{dbpass};
		$my_conf{dsn} =~ /host(?:name)?=(\w+)\b/;
		$dbhost = $1;
	}

	die "FATAL ERROR: Can't read input dump file: $!\n"
		if ! -r $dumpfile;

	my(@updateFile);
	open(INFILE, "<$prefix/sql/updates/$PROGNAME")
		or die "Can't read update file $prefix/sql/updates/$PROGNAME: $!\n";
	@updateFile = <INFILE>;
	map { chomp } @updateFile;
	close(INFILE);

	# Process table status directives from update file.
	for (@updateFile) {
		/^#### \%REQUIRED TABLES: (.+)$/ && do {
			push (@rtables, split(/\s*,\s*/, $1));
			next;
		};

		/^#### \%OBSOLETE TABLES: (.+)$/ && do {
			push (@otables, split(/\s*,\s*/, $1));
			next;
		};
	}

	# Sanitize array of all unnecessary entries.
	@updateFile = grep { ! /^(#.+|\s+)?$/ } @updateFile;

	# Create temporary database.
	$tempDBname = tempDBname();
	# We can just exit if the global handle has errors because we're being 
	# wordy in the connect.
	$g_dbh =
		DBI->connect("DBI:mysql:database=;host=$dbhost", $g_user, $g_pwd, {
			PrintError => 1, 
		}) or exit 1;
	$g_dbh->do("CREATE DATABASE $tempDBname") or
		print "\n\nCREATE DATABASE failed for '$tempDBname'\n" && exit 1;
	# Can't forget the grant...
	$g_dbh->do("GRANT ALL on $tempDBname.* TO $t_user\@$dbhost") or
		print "\n\nGRANT failed!\n" && exit 1;
	$dbh = DBI->connect("DBI:mysql:database=$tempDBname;host=$dbhost", 
						$t_user, $t_pwd, {PrintError=>0});
	die "\n\nCan't connect to temporary conversion database!\n" if ! $dbh;

	# Perform selective import of given dump.
	importDump();

	# Perform schema updates on loaded tables (this should probably be a sub).
	my $cmd;
	for (@updateFile) {
		$cmd = "${cmd}${_}";
		if ($cmd =~ /;(\s)*$/) {
			$dbh->do($cmd) or
				printf STDERR "Error executing '$_': %s\n", $dbh->errstr;
			$cmd = '';
		}
	}

	# Perform version specific conversion process.
	#ditchAC();
	authors2Users();
	aid2uid();
	# Don't do codes, leave that to the new dump --Brian
	#
	# But what if users have already edited some of these tables? Said 
	# information is lost. Is there something REALLY wrong with the routine? 
	# ...aside from the fact that it was broken? ;) - Cliff
	codes2CodeParam();
	md5Users();
	userskey2Param();

	# Drop obsolete tables.
	unless ($opts{k}) {
		for (@otables) {
			print "-- Dropping obsolete table '$_'\n";
			$dbh->do("DROP TABLE $_");
		}
	}

	# Make dump of temporary database.
	print "-- Creating output dump '$outfile'\n";
	system("mysqldump -u $t_user -p$t_pwd $tempDBname > $outfile");
	print "DONE!\n";
}

# End Main program code.


# Subroutine definitions.

END {
	# Destroy temporary database.
	if ($g_dbh && !$opts{'K'}) {
		# Destroy the one we've create.
		$g_dbh->do("DROP DATABASE $tempDBname") or
			print "\n\nDatabase drop should be performed manually!\n";
		$g_dbh->disconnect;
	}
	print STDERR
		"(Not deleting temporary database, '$tempDBname', upon request)\n"
		if $opts{K};
};


sub tempDBname {
	my $size = shift || 6;
	my $ret = $PROGNAME;
	my @chars = ('A'..'Z', 'a'..'z', '0'..'9');

	while ($size) {
		$ret .= $chars[int(rand($#chars + 1))];
		$size--;
	}

	$ret;
}


sub importDump {
	my(@vtables)= (@rtables, @otables);
	my($regexp);
	
	{
		local $" = "|";
		$regexp = "^(@vtables)\$";
	}

	print "-- Opening $dumpfile\n";
	open(DUMPFILE, "<$dumpfile") or 
		die "FATAL ERROR: Odd! Can't open dumpfile for reading: $!\n";
	while (! eof DUMPFILE) {
		my $cmd = '';
		do {
			my $c = <DUMPFILE>;
			chomp($c);
			$c =~ s/^\s+//;
			$cmd .= "$c " if length($c);
		} until ($cmd =~ /\;\s*$/) || ($cmd =~ /^\#/) || eof DUMPFILE;
		#print "CMD: '$cmd'\n";
		next if $cmd =~ /^(#.+|[\n\s]+|)$/s;
		$cmd =~ s/\;\s*$//;

		# We only deal with CREATE TABLE and INSERT statements for now.
		my ($op, $tablename);
		if ($cmd=~/^(CREATE|INSERT) (?:TABLE|INTO) (\w+)\b/i) {
			($op, $tablename) = ($1, $2);
			my $valid = $tablename =~ /$regexp/;
			if (! $valid) {
				#print "---- ($op|$tablename) Skipping command '$cmd'\n";
				next;
			}
		}
		
		print "-- Creating table '$tablename'\n" if $op eq 'CREATE';
		$dbh->do($cmd) or 
			print STDERR 	"Error executing statement on import: " . 
							$dbh->errstr . "\n";
			printf STDERR	"(%d) '$cmd'", length($cmd)
				if (my $a = $dbh->errstr || '') =~ /SQL syntax/,
	}
	print "-- Closing $dumpfile\n";
}


sub authors2Users {
	my $sth = $dbh->prepare('SELECT * FROM authors WHERE aid != ""');
	if (! $sth->execute) {
		err("Can't execute on statement handle: ".$dbh->errstr);
		return;
	}
	my $data = $sth->fetchall_arrayref({});
	$sth->finish;

	for (@{$data}) {
		# Search users table for a matching nickname for the AID.
		my $l_sth = $dbh->prepare('SELECT * FROM users WHERE nickname=' .
			$dbh->quote($_->{aid}));
		if (! $l_sth->execute) {
			err("Can't exacute on USER statement handle: ".$dbh->errstr);
			next;
		}
		my $user = $l_sth->fetchrow_hashref;
		$l_sth->finish;
		
		my ($uid, $user_info);
		my $qn = $dbh->quote(($user) ? $user->{nickname} : $_->{aid});
		if (! $user) {
			# Insert author as a new user into the database.
			print STDERR "-- Creating user account for author $qn\n";
			$dbh->do(<<EOQ);
INSERT INTO users (uid, nickname) VALUES (null, $qn)
EOQ

			# Get full user record back from database.
		 	$l_sth = $dbh->prepare("SELECT uid FROM users WHERE nickname=$qn");
			if (! $l_sth->execute) {
				err("Can't exacute on USER statement handle: $dbh->errstr\n");
				next;
			}
			($uid) = $l_sth->fetchrow_array;
			$l_sth->finish;

			$dbh->do(<<EOQ);
INSERT INTO users_info (uid) VALUES ($uid)
EOQ
			$dbh->do(<<EOQ);
INSERT INTO users_index (uid) VALUES ($uid)
EOQ
			$dbh->do(<<EOQ);
INSERT INTO users_comments (uid) VALUES ($uid)
EOQ
			$dbh->do(<<EOQ);
INSERT INTO users_prefs (uid) VALUES ($uid)
EOQ
		} else {
			print STDERR "-- Promoting user $qn.\n";
			$uid = $user->{uid};
		}
		
		# Retrieve necessary user information to use as defaults.
		$aidCache{$_->{aid}} = $uid;	# Used in aid2Uid().
		$l_sth = $dbh->prepare("SELECT bio FROM users_info WHERE uid=$uid");
		if ($l_sth && $l_sth->execute) {
			$user_info = $l_sth->fetchrow_hashref();
			$l_sth->finish;
		}

		# Change/Set the proper seclev.
		$user->{seclev} = $_->{seclev};

		# Add in author information for anything that isn't currently set.
		$user_info->{bio}	||= $_->{copy} if $user_info;
		$user->{realname}	||= $_->{name};
		$user->{realemail}	||= $_->{email};
		$user->{homepage}	||= $_->{url};
		$user->{sig} 		||= $_->{quote};

		# Update regular tables.
		$dbh->do(<<EOQ);
UPDATE users SET	
	seclev 		= $user->{seclev},
	realname 	= @{[$dbh->quote($user->{realname})]},
	realemail	= @{[$dbh->quote($user->{realemail})]},
	homepage	= @{[$dbh->quote($user->{homepage})]},
	sig			= @{[$dbh->quote($user->{sig})]}
WHERE uid = $uid
EOQ

		$dbh->do(<<EOQ);
UPDATE users_info SET bio = @{[$dbh->quote($user_info->{bio})]}
EOQ

		# There should be no defaults for these.
		$dbh->do(<<EOQ) if $_->{section};
INSERT INTO users_param (param_id, uid, name, value)
	VALUES (null, $uid, 'section', @{[$dbh->quote($_->{section})]})
EOQ

		$dbh->do(<<EOQ) if $_->{deletedsubmissions};
INSERT INTO users_param (param_id, uid, name, value)
	VALUES (null, $uid, 'deletedsubmissions', $_->{deletedsubmissions})
EOQ

		# And this flag must be set.
		$dbh->do(<<EOQ);
INSERT INTO users_param (param_id, uid, name, value)
	VALUES (null, $uid, 'author', 1);
EOQ
	}
}


sub aid2uid {
	my @tables = qw[stories newstories];

	for (@tables) {
		my $sth = $dbh->prepare("SELECT distinct aid FROM $_");
		my @authorList;
		$sth->execute or
			die "Can't execute MASTER SELECT on AID from table '$_'\n";
		for (@{$sth->fetchall_arrayref}) {
			push @authorList, $_->[0];
		}

		$dbh->do("ALTER TABLE $_ ADD uid int");

		for (@authorList) {
			my $qn = $dbh->quote($_) || '';
			$dbh->do("UPDATE $_ SET uid=$aidCache{$_} WHERE aid=$qn");
		}

		$dbh->do("ALTER TABLE $_ DROP aid");
	}
}


sub codes2CodeParam {
	my @tables = qw[commentcodes displaycodes isolatemodes issuemodes
					maillist statuscodes postmodes];

	for (@tables) {
		print STDERR "-- Processing table $_ into CODE_PARAM\n";

		my $sth = $dbh->prepare("SELECT * FROM $_");
		$sth->execute or
			die "Can't execute MASTER SELECT from '$_'\n";
		my $table = $dbh->quote($_);
		for (@{$sth->fetchall_arrayref({})}) {
			my $code = $dbh->quote($_->{code});
			my $name = $dbh->quote($_->{name});

			$dbh->do(<<EOQ);
INSERT INTO code_param (param_id, type, code, name)	
	VALUES(null, $table, $code, $name)
EOQ
		}
	}
}


sub md5Users {
	my $sth = $dbh->prepare("SELECT uid, passwd FROM users");
	my $i_sth = $dbh->prepare('UPDATE users SET passwd=? WHERE uid=?');
	$sth->execute or
		die "Can't execute MASTER SELECT on 'uid,passwd' on table users!\n";
	print STDERR "-- Converting user passwords from plaintext to MD5...";
	for (@{$sth->fetchall_arrayref({})}) {
		$i_sth->execute(md5_hex($_->{passwd}), $_->{uid})
	}
	$i_sth->finish;
	print STDERR "Done!\n";
}	


sub userskey2Param {
	my $sth = $dbh->prepare("SELECT * FROM users_key");
	$sth->execute or
		die "Can't execute MASTER SELECT on table users_key!\n";
	print STDERR "-- Processing table USERS_KEY into table USERS_PARAM\n";
	for (@{$sth->fetchall_arrayref({})}) {
		next if !$_->{pubkey};
		my $pubkey = $dbh->quote($_->{pubkey});

		printf STDERR "-- Found key size %d on uid #$_->{uid}\n", length($pubkey);

		$dbh->do(<<EOQ);
INSERT INTO users_param (param_id, uid, name, value)
	VALUES (null, $_->{uid}, 'key', $pubkey)
EOQ
	}
}


sub err {
	my $msg = shift;
	chomp $msg;
	printf STDERR "%s - %s\n", (caller(1))[3], $msg;
}


sub usage {

	print <<EOT;

usage: $PROGNAME {-YZfFwWxXkKH?hv}

	-Y Prefix for Slash install 		[default: /usr/local/slash]
	-f Location of source MySQL dump	[default: ./site.dump]
	-F Location of output dump 		[default: ./bendersite.dump]

	-w [MASTER] User with CREATE DATABASE/DROP DATABASE/GRANT privileges
	-W Password for user given by -w

	-Z Location of existing slashdotrc.pl	[default: ./slashdotrc.pl]

	-x [SLAVE] User which will perform conversions
	-X Password for user given by -x

	-H Database hostname

	-h Displays this help screen (also -?)
	-v Displays version information.

	-K Do NOT delete temporary database after performing conversion.
	-k Do NOT delete obsolete tables after performing conversion.

Notes: 
	User given in slashdotrc.pl should have global CREATE/DROP privileges
	when this program is run. See INSTALL file for more details.	

	If you specify "-w ''" (ie use the null string as the password) then the
	script will prompt you for the appropriate password. Just hit return
	here if the database user password is NULL. These caveats also apply
	to the -x/-X option pair.

	Use of the -H, -x and -X options are not allowed with -Z.

EOT

	exit 0;
}


sub version {
	print <<EOT;
$PROGNAME $VERSION

This code is a part of Slash, and is released under the GPL.
Copyright 1997-2005 by Open Source Technology Group. See README
and COPYING for more information, or see http://slashcode.com/.
EOT
	exit;
}

__END__