Please select UID: EOT print ``\n\tCurrent Authors\n\t---------------\n''; for (keys %story_authors) { printf ``\t%-30s: #$story_authors{$_}\n'', $_; } if (keys %other_authors) { print <<EOT;
Current Author Assigns
----------------------
EOT
for (keys %other_authors) {
printf "\t%-30s-> #$other_authors{$_}\n", $_;
}
}
my $uid;
while (!$uid || $uid !~ /^\d+$/) {
$uid = ask("UID for AUTHOR '$nf': ");
chomp($uid);
}
$other_authors{$nf} = $uid;
}
map { $story_authors{lc($_)} = $other_authors{$_} }
keys %other_authors;
}
| sub load_storystuff { | |
| my ($hitlist) = @_; |
print "Loading hitcounts into story metadata...\n";
my $sth_s = $dbh_old->prepare('SELECT sid, hits FROM storiestuff');
$sth_s->execute;
while (my $data = $sth_s->fetchrow_hashref) {
$hitlist->{$data->{sid}}{hits} = $data->{hits};
}
$sth_s->finish;
}
| sub copy_comments { | |
| my %dead_discussions; | |
| my($lastdate, $cond) = ('', ''); |
# Is this sufficient or should this be SID based?
my $datecond = <<EOT;
date_format(date_sub(now(), interval 15 day), '%Y-%m-%d 00:00')
EOT
my $sth_d = $dbh_new->prepare("SELECT $datecond");
$sth_d->execute;
my($display) = $sth_d->fetchrow_array;
if ($opts{I}) {
$cond = "WHERE date > $datecond";
print "Deleting stale comments...\n";
my(@delete_sids) = @{$dbh_new->selectall_arrayref(<<EOT)};
SELECT cid FROM comments $cond
EOT
# Flatten resulting list into a list of CIDs to delete.
@delete_sids = map { $_ = $_->[0] } @delete_sids;
my $deleted_comm = $#delete_sids + 1;
# We delete in batches to prevent the SQL buffer from
# overflowing.
my $batchsize = 100;
LOOP: { do {
my @batch = grep { defined($_) }
@delete_sids[0 .. $batchsize-1];
local $" = ',';
$dbh_new->do(<<EOT) if @batch;
DELETE FROM comment_text WHERE cid IN (@batch)
EOT
last LOOP if $#delete_sids < $batchsize - 1;
@delete_sids = @delete_sids[$batchsize..$#delete_sids];
} while (@delete_sids) };
$dbh_new->do("DELETE FROM comments $cond");
print "Copying comment data and texts from $display...\n";
} else {
print "Copying comment data and texts...\n";
}
my $sth_s = $dbh_old->prepare("SELECT * FROM comments $cond");
$sth_s->execute;
LOOP:
while (my $data = $sth_s->fetchrow_hashref) {
next if exists $dead_discussions{$data->{sid}};
# Fix UID.
$data->{uid} = $ac_uid{$data->{uid}}
if exists $ac_uid{$data->{uid}};
# Fix SID.
my $insert_q;
my $sid = $discussions{$data->{sid}};
SIDTEST: { if (!$sid) {
# If the discussion is attached to a POLL (has the same
# name as a poll, then it IS a valid discussion and
# a record should be created for it.
if (exists $polls{$data->{sid}}) {
my $ques = $poll_data{$data->{sid}}->{question};
my $poll_name = "Poll: '$ques'";
my $poll_url = sprintf "%s/pollBooth.pl?qid=%s",
$vars->{rootdir}, $polls{$data->{sid}};
$insert_q = <<EOT;
INSERT INTO discussions (sid, title, url, topic, ts, flags)
VALUES (
@{[$dbh_new->quote($data->{sid})]},
@{[$dbh_new->quote($poll_name)]},
@{[$dbh_new->quote($poll_url)]},
$vars->{defaulttopic},
@{[$dbh_new->quote($poll_data{$data->{sid}}->{date})]},
'dirty')
EOT
$dbh_new->do($insert_q);
die "SQL: $insert_q\n" if $dbh_new->errstr;
$discussions{$data->{sid}} =
($sid = getLastInsertID());
# Now have to update the poll!
$dbh_new->do(<<EOT);
UPDATE pollquestions SET discussion=$discussions{$data->{sid}} WHERE
sid='$data->{sid}'
EOT
print <<EOT;
+ Created discussion for poll "$ques" ($data->{sid}/$sid)
EOT
}
last SIDTEST if $sid;
$dead_discussions{$data->{sid}}++;
print <<EOT;
Skipping deleted discussion '$data->{sid}'
EOT
next LOOP;
} }
$insert_q = sprintf
qq[
INSERT INTO comments
(sid, pid, date, subject, uid, points, lastmod, reason)
VALUES ($sid, %s)
],
scalar join(', ',
map { $dbh_new->quote($data->{$_}) }
qw [pid date subject uid points lastmod reason]
);
$dbh_new->do($insert_q);
die "SQL: $insert_q\n" if $dbh_new->errstr;
my $new_cid = getLastInsertID();
# This only needs to be done for comments that are "live".
$comments{$data->{sid}}->{$data->{cid}} = $new_cid
if $data->{date} ge $display;
$data->{comment} = $dbh_new->quote($data->{comment});
my $sql = <<EOT;
INSERT INTO comment_text (cid, comment)
VALUES ($new_cid, $data->{comment})
EOT
$dbh_new->do($sql);
}
$sth_s->finish;
# Now update PIDs.
print "Updating comment heirarchy.\n";
$sth_s = $dbh_old->prepare(<<EOT);
SELECT DISTINCT sid,pid FROM comments $cond
EOT
$sth_s->execute;
while (my $data = $sth_s->fetchrow_hashref) {
next if !$data->{pid} || exists $dead_discussions{$data->{sid}};
my $pid = $comments{$data->{sid}}->{$data->{pid}} || 0;
my $SQL = <<EOT;
UPDATE comments SET
pid=$pid
WHERE sid=$discussions{$data->{sid}} AND
pid=$data->{pid}
EOT
$dbh_new->do($SQL);
print STDERR <<EOT if $dbh_new->errstr;
($data->{sid}|$data->{pid}) SQL: $SQL
EOT
}
# We need to grab the lowest discussion ID that we are INSERTING.
# circular dependencies again (and the fact that there may be
# dirty hitparade columns from the EXISTING data.
$sth_s = $dbh_new->prepare(<<EOT);
SELECT min(id) FROM discussions WHERE ts >= $datecond
EOT
$sth_s->execute;
my($min_did) = $sth_s->fetchrow_array;
# Now update discussion_hitparade...
print "Updating story comment counts...";
$sth_d = $dbh_new->prepare(<<EOT);
SELECT id,sid FROM discussions WHERE flags='dirty'
EOT
$sth_d->execute();
printf "%s\n", ($sth_d->rows) ?
(sprintf "%d rows", $sth_d->rows) : '';
while (my($id, $s_id) = $sth_d->fetchrow_array) {
# We COULD take the hitparade from the story, but instead
# we take it from COMMENTS because the data from the
# story is probably dirty.
my $sth_d1 = $dbh_new->prepare(<<EOT);
SELECT points, count(*) FROM comments WHERE sid=$id GROUP BY points
ORDER BY points DESC
EOT
my $total = 0;
my(@hitparade);
# Set upper index and initialize.
$hitparade[$vars->{comment_maxscore} -
$vars->{comment_minscore}] = 0;
$_ = 0 for (@hitparade);
$sth_d1->execute;
while (my($val, $count) = $sth_d1->fetchrow_array) {
$total += $count;
$hitparade[$val - $vars->{comment_minscore}] = $total;
#
# THIS CODE SEVERELY DEPRECATED, PLEASE DO NOT USE.
#
# Handle the possibility that there may be records
# that need an UPDATE.
# my $sql;
# if ($id >= $min_did) {
# $sql = <<EOT;
#INSERT INTO discussion_hitparade (discussion, threshold, count)
# VALUES ($id, $val, $total)
#EOT
# } else {
# $sql = <<EOT;
#UPDATE discussion_hitparade SET
# count=$total
#WHERE discussion=$id AND threshold=$val
#EOT
# }
#
# $dbh_new->do($sql);
# die "SQL: $sql\n" if $dbh_new->errstr;
}
$sth_d1->finish;
for (qw(discussions stories)) {
my $field = (/^stories$/) ? 'sid' : 'id';
my $insert_id = ($_ eq 'stories') ?
$dbh_new->quote($s_id) : $id;
my $hp = '';
$hp = ", hitparade='" . join(',', @hitparade) . "'"
if $_ eq 'stories';
my $sql = <<EOT;
UPDATE $_ SET
commentcount=$total $hp
WHERE $field=$insert_id
EOT
$dbh_new->do($sql);
die "SQL $sql\n" if $dbh_new->errstr;
}
}
# Make sure we update discussion type.
$dbh_new->do(<<EOT);
UPDATE discussions SET type='archived'
WHERE ts < date_sub(now(), interval $vars->{archive_delay} day)
EOT
# Make sure we clean our flags.
$dbh_new->do(
"UPDATE discussions SET flags='ok' WHERE flags='dirty'"
);
}
sub reload_keys { my($table, $key_tbl, $f1, $f2, $f3) = @_; $f3 ||= $f1; my(%old, %new); my $sth_s = $dbh_old->prepare(``SELECT $f1, $f2 FROM $table''); $sth_s->execute; while (my $data = $sth_s->fetchrow_hashref) { $old{$data->{$f1}} = $data->{$f2}; }; $sth_s->finish; my $sth_d = $dbh_new->prepare(``SELECT $f3, $f2 FROM $table''); $sth_d->execute; while (my $data = $sth_d->fetchrow_hashref) { $new{$data->{$f2}} = $data->{$f3}; } $sth_d->finish; for (keys %old) { $key_tbl->{$_} = $new{$old{$_}}; } }
# The difference here is that we are retrieving this list from the IMPORTED # database, not the original. sub get_authors { my $sql = <<EOT; SELECT users.uid, nickname FROM users, users_param WHERE name='author' AND value=1 AND users.uid=users_param.uid EOT
my $sth_d = $dbh_new->prepare($sql);
$sth_d->execute;
my $err = $dbh_new->errstr;
die "SQL: $sql\n" if $err;
while (my($uid, $auth) = $sth_d->fetchrow_array) {
$story_authors{lc($auth)} = $uid;
}
}
| sub update { | |
| my($table, @opt) = @_; | |
| my($where, $cond); |
return if !$table;
if (!exists $conditions{$table}) {
print "X == SKIPPING update on '$table': no conditions\n";
return;
}
my $sql = <<EOT;
SELECT max($conditions{$table}->{field}) FROM $table
EOT
my $sth_d = $dbh_new->prepare($sql);
$sth_d->execute();
my $err = $dbh_new->errstr;
die "SQL ($table): $sql\n" if $err;
my($max_id)= $sth_d->fetchrow_array();
$cond = sprintf $conditions{$table}->{cond}, $max_id
if $conditions{$table}->{type} eq 'int';
$cond = sprintf $conditions{$table}->{cond}, $dbh_old->quote($max_id)
if $conditions{$table}->{type} eq 'date';
my $sth_s = $dbh_old->prepare(<<EOT);
SELECT * FROM $table WHERE $cond
EOT
$sth_s->execute();
print "Updating $table...\n" if $sth_s->rows;
do_handle($table, $sth_s, 0, @opt);
}
#################################### # 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, @extra) = @_; my $filter = $conversions{$table};
print "Processing $table\n";
my $sth_s = $dbh_old->prepare("SELECT * FROM $table");
$sth_s->execute;
do_handle($table, $sth_s, $opt, @extra);
}
| sub do_handle { | |
| my ($table, $sth, $opt, @extra) = @_; |
die "No conversion filter for table '$table'!"
unless exists $conversions{$table};
if ($opt eq 1) {
$dbh_new->do("DELETE FROM $table");
}
my $filter = $conversions{$table};
while (my $data = $sth->fetchrow_hashref) {
$data = $filter->($data, @extra) if ref $filter eq 'CODE';
next unless $data;
# Remove any potential extraneous fields if specified.
if (exists $data->{ALLOWED_FIELDS}) {
my @fieldlist;
push @fieldlist, @{$data->{ALLOWED_FIELDS}};
delete $data->{ALLOWED_FIELDS};
my $fl = join ('|', @fieldlist);
map { delete $data->{$_} }
grep { ! /^($fl)$/ } keys %{$data};
}
map {
$data->{$_} = (/^-/) ? $_:$dbh_new->quote($data->{$_});
} keys %$data;
my $insert = sprintf("INSERT INTO $table (%s) VALUES (%s)",
join(', ', map { s/^\-//; $_ } keys %$data),
join(', ', values %$data)
);
if ($opt =~ /[a-zA-Z]/) {
$dbh_new->do(<<EOT);
DELETE FROM $table WHERE $opt = $data->{$opt}
EOT
}
$dbh_new->do($insert);
my $err = $dbh_new->errstr;
die "$table: $err" if $err;
# This hack might be a better way to do what reload_keys()
# does and is required in a few places.
# [unused, but let's leave it for now. It shouldn't hurt
# anything as long as nothing refers to it]
if (exists $data->{KEY_HASH}) {
($data->{KEY_HASH}{$data->{$data->{KEY_OLD}}}) =
getLastInsertID();
}
}
}
| sub getLastInsertID { | |
my($ret) = $dbh_new->selectall_arrayref(``SELECT LAST_INSERT_ID()''); |
return $ret->[0][0];
}
| 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 2.2 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] | |
| -I | Perform INCREMENTAL conversion |
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__
slash1toslash2.2 - Convert Slash 1.0 database to Slash 2.2
install-slashsite -u slash
slash1toslash2.2 -u slash slashdotrc.pl
Please read these instructions before starting a new Slash 2.2 site. They are designed to convert a site from scratch; they will not work with a Slash 2.2 site that's been used.
This program will copy data from your old Slash 1.0 database to your new Slash 2.2 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, 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.2.
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.
You probably won't need to change any data. But there are three things to check before starting.
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.
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.
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.2 database, though, so there is no conflict.
Run the install-slashsite program as described in that program's
documentation. Remember which virtual user you used to install
the site. Do not make any changes to the database.
Get the slashdotrc.pl file from your Slash 1.0 site and copy it to some directory on your Slash 2.2 box. At this point, make sure you can access the Slash 1.0 database from the Slash 2.2 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 not commented out in slashdotrc.pl.
Run the program, using the proper value for virtual_user and the proper path to the slashdotrc.pl file:
slash1toslash2.2 -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 install-slashsite
(probably yes).
Copy over any images or static files you have, and adjust the site's templates, blocks, and variables as needed.
This is just a detailed run-down of what the program does, in the order it does it.
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 will still be copied.
We do not bother deleting comments and stories, because those will be overwritten later.
abusers content_filters discussions pollanswers
pollquestions sections storiestuff topics
users users_comments users_index users_info users_prefs
comments submissions accesslog formkeys metamodlog
moderatorlog pollvoters
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.
stories sessions
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.2 database as a block _and_ a template, and as such the block is not copied from 1.0 to 2.2, 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.
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.
If you invoke this program using the -I switch, an incremental conversion will be performed. The name implies exactly what it means, each table has a condition that will allow for incremental updates to be performed between a Beast site and a Fry site, which is useful for sites attempting to upgrade to Fry by using another system in tandem.
An incremental update assumes that a previous conversion (or a working Slash site already exists at the database referred to by the specified virtual user.
| The following tables will remain UNTOUCHED in an incremental update: | |
| blocks | |
| code_param | |
| commentmodes | |
| content_filters | |
| dateformats | |
| menus | |
| sections | |
| templates | |
| topics | |
| tzcodes |
The above tables are generally part of a theme, and this should allow for a site\ admin to CHANGE the given theme without needing to reimport the entire dataset, if they so desire.
$Id: slash1toslash2.2,v 1.1.2.23 2001/10/10 16:06:26 pudge Exp $