slash1toslash2

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

use strict;
use File::Basename;
use FindBin '$Bin';
use Getopt::Std;
use DBIx::Password;
use Digest::MD5 'md5_hex';
use vars qw(%my_conf);

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

my %opts;
# Remember to doublecheck these match usage()!
usage('Options used incorrectly') unless getopts('hvu:', \%opts);
usage() if ($opts{'h'} || !keys %opts);
version() if $opts{'v'};
usage('Need virtual user') unless $opts{'u'};
usage("Need slashdotrc.pl file") unless my $rcfile = $ARGV[0];

####################################
# disclaimer
{
	my $answer = ask(<<'EOT');
By running this I realize that there is no warranty, expressed or implied.
Any data loss as a result of running this program is my responsibility.
I have read the documentation for this program, I understand it, and I
have taken the necessary precautions and done the require preparation.
[yes/no]
EOT
	exit unless $answer eq 'yes';
}

####################################
# setup
require $rcfile;
*my_conf = $Slash::conf{DEFAULT} = $Slash::conf{DEFAULT};

my $dbh_old = DBI->connect(@my_conf{qw[dsn dbuser dbpass]});
my $dbh_new = DBIx::Password->connect($opts{'u'});

END {
	$dbh_old->disconnect if $dbh_old;
	$dbh_new->disconnect if $dbh_new;
}


####################################
# fix AC UID
my($ac_uid, $del_uid2);
{
	$ac_uid = ask(<<'EOT');
Please select a UID for the anonymous user of the site.  It is probably
best if it is the lowest positive integer that is unused on the old
Slash 1.0 site.  Enter the integer here (enter "1" to do no change):
EOT

	die "[$ac_uid] is not an integer.\n" unless $ac_uid =~ /^\d+$/;

	$del_uid2 = ask(<<'EOT') =~ /^y/i;
Should we delete uid 2, the new admin user created by the installation?
In theory, your other admin accounts will be copied from the old site,
and you can use those.  If you choose not to do this, make sure uid 2
is not a conflict from your other site.  [yes/no]
EOT

	if ($ac_uid != 1) {
		$dbh_new->do("UPDATE vars SET value=$ac_uid WHERE name='anonymous_coward_uid'");
		for (qw(users users_comments users_info users_index
			users_prefs users_param)) {
			$dbh_new->do("DELETE FROM $_ WHERE uid=$ac_uid");
			$dbh_new->do("UPDATE $_ SET uid=$ac_uid WHERE uid=1");
		}
	}

	if ($del_uid2) {
		for (qw(users users_comments users_info users_index
			users_prefs users_param)) {
			$dbh_new->do("delete from $_ where uid=2");
		}
	}
}

####################################
# main body
{
	# just copy
	for (qw(abusers content_filters discussions pollanswers
		pollquestions sections storiestuff topics)) {
		duplicate($_, 1);
	}


	# offset -> off_set
	duplicate('tzcodes', 'tz', sub {
		my($data) = @_;
		$data->{off_set} = $data->{offset};
		delete $data->{offset};
		$data->{tz} = uc $data->{tz};
		return $data;
	});

	# no more uid == -1
	my $usersub = sub {
		my($data) = @_;
		return if $data->{uid} == -1 || $data->{uid} == $ac_uid
		      || (!$del_uid2 && $data->{uid} == 2);

		$data->{seclev} = 1
			if exists $data->{seclev} && $data->{seclev} == 0;

		$data->{tzcode} = uc($data->{tzcode})
			if exists $data->{tzcode};

		$data->{passwd} = md5_hex($data->{passwd})
			if exists $data->{passwd};

		return $data;
	};
	for (qw(users users_comments users_index users_info users_prefs)) {
		duplicate($_, 0, $usersub);
	}

	# put keys into users_param
	users_keys();

	# copy straight, but fix uid == -1
	my $fixac_sub = sub {
		my($data) = @_;
		$data->{uid} = $ac_uid if $data->{uid} == -1;
		return $data;
	};
	for (qw(comments submissions accesslog formkeys metamodlog
		moderatorlog pollvoters)) {
		duplicate($_, 1, $fixac_sub);
	}

	# update aid's etc.
	my $story_authors = fix_authors();

	# find null stories
	my $null_uid = null_stories($story_authors);

	# aid -> uid
	my $fixauthor_sub = sub {
		my($data, $story_authors, $null_uid) = @_;
		$data->{uid} = $data->{aid}
			? $story_authors->{$data->{aid}}
			: $null_uid;
		delete $data->{aid};
		return $data;
	};
	for (qw(stories sessions)) {
		duplicate($_, 1, $fixauthor_sub, $story_authors, $null_uid);
	}

	# populate newstories
	fill_newstories();

	# do all the blocks stuff
	copy_blocks();
	fix_sectionblocks();

	fix_vars();
}

####################################
sub ask {
	local $| = 1;

	chomp(my $question = $_[0]);
	print "\n", $question, " ";

	chomp(my $answer = <STDIN>);
	print "\n";
	return $answer;
}

####################################
sub fill_newstories {
	print "Processing newstories\n";

	$dbh_new->do('DROP TABLE newstories');
	$dbh_new->do('CREATE TABLE newstories SELECT * FROM stories');
	$dbh_new->do(<<'EOT');
ALTER TABLE	newstories
ADD PRIMARY KEY	(sid),
ADD KEY 	time (time),
ADD KEY		searchform (displaystatus, time)
EOT
}

####################################
# get vars out of slashdotrc.pl
sub fix_vars {
	print "Processing vars\n";

	for (qw(mailfrom siteadmin siteadmin_name smtp_server
		sitename slogan mainfontface updatemin
		archive_delay submiss_ts articles_only
		allow_anonymous use_dept max_depth
		defaultsection http_proxy fancyboxwidth
		story_expire titlebar_width run_ads
		authors_unlimited m2_comments m2_maxunfair
		m2_toomanyunfair m2_bonus m2_penalty
		m2_userpercentage comment_minscore
		comment_maxscore submission_bonus goodkarma
		badkarma maxkarma metamod_sum maxtokens
		tokensperpoint maxpoints stir tokenspercomment
		down_moderations post_limit max_posts_allowed
		max_submissions_allowed submission_speed_limit
		formkey_timeframe m2_mincheck m2_maxbonus
	)) {
		my $value = $dbh_new->quote($my_conf{$_});
		$dbh_new->do("UPDATE vars SET value=$value WHERE name='$_'");
	}

	# don't overwrite new vars descriptions with old ones
	my $vars = $dbh_old->selectall_arrayref("SELECT name,value FROM vars");
	for my $var (@$vars) {
		my @data = map { $dbh_new->quote($_) } @$var;
		$dbh_new->do("UPDATE vars SET value=$data[1] WHERE name=$data[0]");
	}
}

####################################
# copy blocks, skipping certain blocks and excluding some fields,
# fixing some data
sub copy_blocks {
	my $nc = '[^,]+,?';
	my %skip_blocks = map { ($_, 1) } qw(
		admin_footer admin_header comment commentswarning
		edit_filter emailsponsor fancybox footer header
		index index2 light_comment light_fancybox
		light_footer light_header light_index light_story
		light_story_link light_story_trailer light_titlebar
		list_filters_footer list_filters_header mainmenu
		menu motd newusermsg organisation pollitem
		portalmap postvote story story_link story_trailer
		storymore submit_after submit_before titlebar
		userlogin
	);

	duplicate('blocks', 'bid', sub {
		my($data, $skip_blocks) = @_;
		return if exists $skip_blocks->{$data->{bid}};

		for (qw(aid blockbak)) {
			delete $data->{$_};
		}

		if ($data->{bid} eq 'colors' || $data->{bid} =~ /_colors$/) {
			$data->{block} =~ s/^($nc$nc$nc$nc)($nc$nc$nc$nc)$/$1#CCCCCC,$2,#CCCCCC/;
		}

		return $data;
	}, \%skip_blocks);

	$dbh_new->do('DROP TABLE backup_blocks');
	$dbh_new->do('CREATE TABLE backup_blocks SELECT bid, block FROM blocks');
}

####################################
# add old sectionblocks data to blocks table
sub fix_sectionblocks {
	print "Processing sectionblocks\n";

	my $sth_s = $dbh_old->prepare("SELECT * FROM sectionblocks");
	$sth_s->execute;
	
	while (my $data = $sth_s->fetchrow_hashref) {
		my $bid = $dbh_new->quote($data->{bid});
		delete $data->{bid};

		my $insert = sprintf("UPDATE blocks SET %s WHERE bid=$bid",
			join ', ',
			map { "$_=" . $dbh_new->quote($data->{$_}) }
			keys %$data);

		$dbh_new->do($insert);
		my $err = $dbh_new->errstr;
		die $err if $err;
	}
}

####################################
# get users keys into users_param table
sub users_keys {
	print "Processing users_keys\n";

	my $users = $dbh_old->selectall_arrayref("SELECT uid,pubkey FROM users_key");
	for my $user (@$users) {
		my $string = $dbh_new->quote($user->[1]);
		$dbh_new->do("INSERT INTO users_param (uid,name,value) VALUES ($user->[0], 'pubkey', $string)");
	}
}

####################################
# bunch of things to get users fixed up
sub fix_authors {
	my %story_authors;
	my $authors = $dbh_old->selectall_arrayref(<<'EOT');
SELECT aid,seclev,lasttitle,section,deletedsubmissions
FROM   authors
WHERE  name != 'All Authors'
EOT

	for my $author (@$authors) {
		(my $matchname = lc $author->[0]) =~ s/[^a-zA-Z0-9]//g;
		my $uid = $dbh_old->selectrow_array(<<EOT);
SELECT uid
FROM   users
WHERE  matchname='$matchname'
EOT
		if ($uid) {
			$story_authors{$author->[0]} = $uid;
			my @data = map { $dbh_new->quote($_) } @$author;
			$dbh_new->do("UPDATE users SET seclev=$data[1] WHERE uid=$uid");
			$dbh_new->do("INSERT INTO users_param (uid,name,value) VALUES ($uid, 'author', '1')");
			$dbh_new->do("INSERT INTO users_param (uid,name,value) VALUES ($uid, 'lasttitle', $data[2])")
				if $data[2] && $data[2] ne "NULL";
			$dbh_new->do("INSERT INTO users_param (uid,name,value) VALUES ($uid, 'section', $data[3])")
				if $data[3] && $data[3] ne "NULL";
			$dbh_new->do("INSERT INTO users_param (uid,name,value) VALUES ($uid, 'deletedsubmissions', $data[4])")
				if $data[4];
		}
		

	}

	return \%story_authors;
}

####################################
# find any orphaned stories
sub null_stories {
	my($story_authors) = @_;
	my $null_sids = $dbh_new->selectall_arrayref('SELECT sid FROM stories WHERE uid is NULL');
	if (@$null_sids) {
		print "Found stories lacking authors, select a uid to assign them to:\n";
		for (keys %$story_authors) {
			print "Authors $_ : $story_authors->{$_}\n";
		}
		chomp(my $uid = <STDIN>);
		return $uid;
	}
}

####################################
# the main function to copy data from old DB to new DB
# if $opt is 1, delete table contents before inserting
# if $opt is 0, do nothing with table before inserting
# if $opt is a string, delete that specific column before inserting
sub duplicate {
	my($table, $opt, $filter, @extra) = @_;

	print "Processing $table\n";

	my $sth_s = $dbh_old->prepare("SELECT * FROM $table");
	$sth_s->execute;

	if ($opt eq 1) {
		$dbh_new->do("DELETE FROM $table");
	}

	while (my $data = $sth_s->fetchrow_hashref) {
		$data = $filter->($data, @extra) if $filter;
		next unless $data;
		
		my $insert = sprintf("INSERT INTO $table (%s) VALUES (%s)",
			join(', ', keys %$data),
			join(', ', map { $dbh_new->quote($_) } values %$data)
		);

		if ($opt =~ /[a-zA-Z]/) {
			my $val = $dbh_new->quote($data->{$opt});
			$dbh_new->do("DELETE FROM $table WHERE $opt = $val");
		}

		$dbh_new->do($insert);
		my $err = $dbh_new->errstr;
		die $err if $err;
	}
}

sub usage {
	print "*** $_[0]\n" if $_[0];
	# Remember to doublecheck these match getopts()!
	print <<EOT;

Usage: $PROGNAME [OPTIONS] RCFILE

Converts Slash 1.0 data to Slash 1.1/2.0 data.  Type
"perldoc $PROGNAME" or "perldoc `which $PROGNAME`
for instructions on use.

Main options:
		slashdotrc.pl file from 1.0 installation
	-h	Help (this message)
	-v	Version
	-u	Virtual user (default is "slash") [REQUIRED]

EOT
	exit;
}

sub version {
	print <<EOT;

$PROGNAME $VERSION

This code is a part of Slash, and is released under the GPL.
Copyright 1997-2001 by Open Source Development Network. See README
and COPYING for more information, or see http://slashcode.com/.

EOT
	exit;
}

1;

__END__


=head1 NAME

slash1toslash2 - Convert Slash 1.0 database to Slash 2.0

=head1 SYNOPSIS

	install-slashsite -u slash
	slash1toslash2 -u slash slashdotrc.pl

=head1 DESCRIPTION

Please read these instructions before starting a new Slash 2.0
site.  They are designed to convert a site from scratch; they
will not work with a Slash 2.0 site that's been used.

This program will copy data from your old Slash 1.0 database
to your new Slash 2.0 database, making direct connections to
both databases and copying the data directly between them.

It will copy over your data, but if you've done any customizations to
display blocks, or code, it will not copy that over.

A detailed description of the work done is below, L<"DETAILS">.
You might want to read this section before running the program.

Note that this is designed for converting a Slash 1.0.9 database;
any schema changes you've made, or incompatible changes from
earlier versions of Slash, may break this program.

Please follow these instructions precisely to convert your Slash 1.0
site to Slash 2.0.


=head2 Requirements

=over 4

=item *

You will need a new database to copy the new data to.  This could be
on the same database server as the Slash 1.0 data, but because
the installed modules (specifically, Slash.pm) are not compatible
between the sites, you will most likely want to have the installed
code for 1.0 and 2.0 on separate boxes.

=item *

You will need Slash 2.0 installed on some box.

=item *

You will need access to both the 1.0 and 2.0 databases from the same
box.  The program will be run from the box that has Slash 2.0 installed,
so if that box does not have access to the Slash 1.0 database, you will
need to either temporarily edit the mysql.user table of the 1.0 server
to grant access, or copy the database from the 1.0 server to the 2.0
server.

=back


=head2 BACK UP YOUR DATA

If you lose your data, it is your problem, not ours.  I deleted all of
the data on http://use.perl.org/ while preparing this program.  However,
I had a backup ready to go (although I still lost about 12 hours of
data, and I feel like an idiot).  Back up your data on your Slash 1.0
database.  You have been warned.

Also, consider what happens if you have two Slash sites on one machine;
what if you give this program the wrong virtual user?  Perhaps you
just deleted a working site!  Back up any existing data on the target
database server, too.  See, even after writing this warning, I did this,
too, and typed "slash" as my virtual user instead of "useperl", and
I overwrote some of the existing database, and didn't have a backup
for some of it.  After that, I felt like a total moron.

This program does not write to your Slash 1.0 database, so you should
be fine, but there are no warranties, expressed or implied.  If you
are running a Slash site, you should be backing up your database
nightly anyway, right?

So backup all your data on both boxes, so you don't feel like a moron,
like me.


=head2 Database Preparation

You probably won't need to change any data.  But there are three things
to check before starting.

=over 4

=item *

Make sure that there are no author/user nickname conflicts.  In
Slash 1.0, users (nicknames) and authors (AID) were separate,
so you could have a user named timothy and an author named timothy,
who were not the same person.  This is not allowed in Slash 2.0,
as those tables have been combined.

Also, every author must have a matching user, or this will fail.
If you still have a story owned by "God", for example, you need
a user with nickname "God" and matchname "god".

Most sites won't have this problem, but if yours does, it is sufficient
to change the nickname/matchname fields of any conflicting user, and
then have the author create a new account with that name.  Of course,
make sure the other user knows you are making the change, and try
to compensate him in some way, perhaps by giving him extra karma.

If you decide you would rather change the author's name, then
you will need to go through the database, in every field named
"aid" (except for the poll tables, where "aid" is "answer id",
not "author id") and change the name of that author.

Changing the name of the user is easier.

=item *

Select a user ID (UID) to be your "anonymous coward" (AC).  This
should probably be the lowest positive integer not in use on the
Slash 1.0 site.  It could be one of the six default users, or
a test user you created.

Be careful to pick a user that does not have any comments or anything
else in the system.  If there is no UID not in use, create a new test
user (as you would create any user, through users.pl) and use that UID.

=item *

Decide whether or not you want to keep the new admin account created
in your Slash 2.0 database.  Chances are, you won't want to or need
to.

However, if you do want to keep it, then you need to delete/modify all
references to that user's uid in your Slash 1.0 database.  It is easier
if you just allow this program to delete that uid from your Slash 2.0
database, though, so there is no conflict.

=back


=head2 Install Slash 2.0 Slash Site

Run the C<install-slashsite> program as described in that program's
documentation.  Remember which I<virtual user> you used to install
the site.  Do not make any changes to the database.


=head2 Copy Slash 1.0 RC File

Get the F<slashdotrc.pl> file from your Slash 1.0 site and copy it
to some directory on your Slash 2.0 box.  At this point, make sure
you can access the Slash 1.0 database from the Slash 2.0 box.

You may need to modify the slashdotrc.pl file's dbhost, dbuser,
and dbpass variables to make sure the database can be accessed
properly.  Also, make sure "$Slash::conf{DEFAULT}" is I<not>
commented out in slashdotrc.pl.


=head2 Run It

Run the program, using the proper value for virtual_user and
the proper path to the slashdotrc.pl file:

	slash1toslash2 -u virtual_user slashdotrc.pl

You will be asked three questions: do you agree to the disclaimer,
what UID do you want for AC (default is 1), and do you want to
delete the new admin user created by C<install-slashsite>
(probably yes).


=head2 Add Final Touches

Copy over any images or static files you have, and adjust the
site's templates, blocks, and variables as needed.


=head1 DETAILS

This is just a detailed run-down of what the program does, in the
order it does it.

=over 4

=item *

If you chose an AC UID of other than 1, then the new UID is
deleted from each of the users* tables, and the old AC UID (1)
is changed to the new UID in each of those tables.  Also,
the variable "anonymous_coward_uid" in the vars table is updated
to the new UID.

=item *

If you chose to delete the new admin user account created by
C<install-slashsite>, it is deleted from each of the users*
tables.  If you choose not to, then UID 2 will not be copied
from the 1.0 database; however, all stories, comments, etc.
assigned to UID 2 I<will> still be copied.

We do not bother deleting comments and stories, because
those will be overwritten later.

=item *

For the following tables, the contents are copied directly from
the 1.0 database to the 2.0 database:

	abusers content_filters discussions pollanswers
	pollquestions sections storiestuff topics

=item *

The tzcodes table is copied over directly, after converting the
column name "offset" to "off_set", and upper-casing the "tz" column
data.

=item *

For the following tables, the data is copied directly, excluding
UID -1 (old AC) and whatever the new AC UID is, and changing
seclev to be 1 (where it was 0), changing tzcode to be upper case,
and encrypting the passwd:

	users users_comments users_index users_info users_prefs

=item *

Data is taken from the old users_key field and copied into the
new users_param field.

=item *

For the following tables, the data is copied directly, after
changing UID -1 to be whatever the new AC UID is.

	comments submissions accesslog formkeys metamodlog
	moderatorlog pollvoters

=item *

As mentioned above, the authors table no longer exists, having been
rolled into the new users table.

Authors are selected from the old database, and matched to UIDs
from the new database.  Then for each UID, the seclev is changed
to the proper value, and the lasttitle, section, and
deletedsubmissions columns are taken from the old authors table
and added to the new users_param table.  Also, a flag of
"author" with value of "1" is added to the users_param table
for that UID.

A hash of author -> uid is created and saved for use in the next
step.

=item *

For the following tables, the column "aid" is replaced by the
column "uid", and the value is changed from the old author's
AID to their UID:

	stories sessions

=item *

If there are any stories that have a NULL UID, the program
asks for a UID to assign to the orphaned stories, printing
a list of available authors.

=item *

The newstories table is dropped and created using the data
from the stories table.

=item *

The blocks table is copied over, excluding the blocks that
were converted to templates (like header, footer, etc.).
Note that any blocks that are now templates, that were
customized in the 1.0 site, need to be re-customized in
the 2.0 site.

The "aid" and "blockbak" columns are skipped.

The skipped blocks, that are now templates, are:

	admin_footer admin_header comment commentswarning
	edit_filter emailsponsor fancybox footer header
	index index2 light_comment light_fancybox
	light_footer light_header light_index light_story
	light_story_link light_story_trailer light_titlebar
	list_filters_footer list_filters_header mainmenu
	menu motd newusermsg organisation pollitem
	portalmap postvote story story_link story_trailer
	storymore submit_after submit_before titlebar
	
Also, the "userlogin" block is skipped.  It is still in
the Slash 2.0 database as a block _and_ a template, and
as such the block is not copied from 1.0 to 2.0, since
it has changed substantially.  See the code for details.

The colors blocks are fixed; there were previously
eight colors per block, now there are ten.  The
additional colors ($fg[4] and $bg[4]) are both "#CCCCCC".
This is done for the "colors" block and any block
ending in "_colors".

The backup_blocks table is populated from the blocks table.

=item *

The data from the Slash 1.0 sectionblocks table, which
does not exist in Slash 2.0, is copied to the blocks
table.

=item *

Selected variables from slashdotrc.pl are copied to the
vars table of the Slash 2.0 database.  These are:

	mailfrom siteadmin siteadmin_name smtp_server
	sitename slogan mainfontface updatemin
	archive_delay submiss_ts articles_only
	allow_anonymous use_dept max_depth
	defaultsection http_proxy fancyboxwidth
	story_expire titlebar_width run_ads
	authors_unlimited m2_comments m2_maxunfair
	m2_toomanyunfair m2_bonus m2_penalty
	m2_userpercentage comment_minscore
	comment_maxscore submission_bonus goodkarma
	badkarma maxkarma metamod_sum maxtokens
	tokensperpoint maxpoints stir tokenspercomment
	down_moderations post_limit max_posts_allowed
	max_submissions_allowed submission_speed_limit
	formkey_timeframe m2_mincheck m2_maxbonus

Any values you don't want copied, or want to be additionally
copied, can be taken care of either by hand, or by editing
the source of this program.

=item *

The existing values from the 1.0 database vars table are copied
to the 2.0 database.

=back

=head1 VERSION

$Id$