A New Website, Part Thirteen - The Conversion

I've completed the script transferring the data from my old site to the new one. Once I get the mailing lists set up I will be technically ready to switch over (haven't heard any updates from CSoft yet).

The contents mostly copy over OK - all of the data ends up in the database in the right place, which is good. No additional discoveries about the database to report; I just needed to get the conversion script to work.

So, without further ado, here's the script. My data converts to Drupal. Now I need to figure out how to view it (there being utterly no instruction on how to actually view the newly created content types). The dearth of discoverable Drupal documentation continues to amaze me.



#!/usr/bin/perl

$|++; # Turn off print buffering
use DBI;



print "Content-type: text/html\n\n";
print "Test";

my $dbh = &db_open("DBI:mysql:xxxxxx:localhost","xxxxxx","xxxxxx") or print "Database connect error: $!";


print "Database is open";
my $rec_num = 0;



print "Preparing Node records:";

print "Articles: ";
my $sth = $dbh->prepare("SELECT * FROM post");
$sth->execute();
while (my $ref = $sth -> fetchrow_hashref()) {

next unless ($ref->{post_type} eq "article");
unless ($ref->{post_crdate}) { $ref->{post_crdate} = time; }

$node->{nid} = $rec_num;
$node->{vid} = $rec_num;
$node->{type} = "content_article";
$node->{title} = $ref->{post_title};
$node->{uid} = 1;
$node->{status} = 1;
$node->{created} = $ref->{post_crdate};
$node->{changed} = $ref->{post_crdate};
$node->{promote} = 1;
$node->{moderate} = 0;
$node->{sticky} = 0;
$vars->{idfield} = &db_insert($dbh,"node",$node);

$node_content_article->{vid} = $rec_num;
$node_content_article->{nid} = $rec_num;
$node_content_article->{field_body_value} = $ref->{post_content};
$vars->{idfield} = &db_insert($dbh,"node_content_article",$node_content_article);

$node_revisions->{vid} = $rec_num;
$node_revisions->{nid} = $rec_num;
$node_revisions->{uid} = 1;
$node_revisions->{title} = $ref->{post_title};
$node_revisions->{timestamp} = $ref->{post_crdate};
$node_revisions->{format} = 0;
$vars->{idfield} = &db_insert($dbh,"node_revisions",$node_revisions);
#print "Inserting record number $rec_num. Title: ",$ref->{post_title},"
";
exit unless ($node_revisions->{title});
$postcodes->{$ref->{post_id}} = $rec_num;

print ". ";

$rec_num++;
}

print "Done Articles";


print "Authors: ";
my $sth = $dbh->prepare("SELECT * FROM author");
$sth->execute();
while (my $ref = $sth -> fetchrow_hashref()) {

unless ($ref->{author_crdate}) { $ref->{author_crdate} = time; }

$node->{nid} = $rec_num;
$node->{vid} = $rec_num;
$node->{type} = "content_author";
$node->{title} = $ref->{author_name};
$node->{uid} = 1;
$node->{status} = 1;
$node->{created} = $ref->{author_crdate};
$node->{changed} = $ref->{author_crdate};
$node->{promote} = 1;
$node->{moderate} = 0;
$node->{sticky} = 0;
$vars->{idfield} = &db_insert($dbh,"node",$node);

$node_content_author->{vid} = $rec_num;
$node_content_author->{nid} = $rec_num;
$vars->{idfield} = &db_insert($dbh,"node_content_author",$node_content_author);

$node_revisions->{vid} = $rec_num;
$node_revisions->{nid} = $rec_num;
$node_revisions->{uid} = 1;
$node_revisions->{title} = $ref->{author_name};
$node_revisions->{timestamp} = $ref->{author_crdate};
$node_revisions->{format} = 0;
$vars->{idfield} = &db_insert($dbh,"node_revisions",$node_revisions);

$authorcodes->{$ref->{author_id}} = $rec_num;

print ". ";

$rec_num++;
}

print "Done Authors";


print "Events: ";
my $sth = $dbh->prepare("SELECT * FROM event");
$sth->execute();
while (my $ref = $sth -> fetchrow_hashref()) {

unless ($ref->{event_crdate}) { $ref->{event_crdate} = time; }

$node->{nid} = $rec_num;
$node->{vid} = $rec_num;
$node->{type} = "content_event";
$node->{title} = $ref->{event_title};
$node->{uid} = 1;
$node->{status} = 1;
$node->{created} = $ref->{event_crdate};
$node->{changed} = $ref->{event_crdate};
$node->{promote} = 1;
$node->{moderate} = 0;
$node->{sticky} = 0;
$vars->{idfield} = &db_insert($dbh,"node",$node);

$node_content_event->{vid} = $rec_num;
$node_content_event->{nid} = $rec_num;
$node_content_event->{field_event_type_value} = $ref->{event_type};
$node_content_event->{field_location_value} = $ref->{event_location};
$node_content_event->{field_start_date_value} = $ref->{event_start};
$node_content_event->{field_end_date_value} = $ref->{event_end};
$vars->{idfield} = &db_insert($dbh,"node_content_event",$node_content_event);

$node_revisions->{vid} = $rec_num;
$node_revisions->{nid} = $rec_num;
$node_revisions->{uid} = 1;
$node_revisions->{title} = $ref->{event_title};
$node_revisions->{timestamp} = $ref->{event_crdate};
$node_revisions->{format} = 0;
$vars->{idfield} = &db_insert($dbh,"node_revisions",$node_revisions);

$eventcodes->{$ref->{author_id}} = $rec_num;

print ". ";

$rec_num++;
}

print "Done Events";



my $sth = $dbh->prepare("SELECT * FROM journal");
$sth->execute();
while (my $ref = $sth -> fetchrow_hashref()) {

unless ($ref->{journal_crdate}) { $ref->{journal_crdate} = time; }

$node->{nid} = $rec_num;
$node->{vid} = $rec_num;
$node->{type} = "content_journal";
$node->{title} = $ref->{journal_title};
$node->{uid} = 1;
$node->{status} = 1;
$node->{created} = $ref->{journal_crdate};
$node->{changed} = $ref->{journal_crdate};
$node->{promote} = 1;
$node->{moderate} = 0;
$node->{sticky} = 0;
$vars->{idfield} = &db_insert($dbh,"node",$node);

$node_content_journal->{vid} = $rec_num;
$node_content_journal->{nid} = $rec_num;
$vars->{idfield} = &db_insert($dbh,"node_content_journal",$node_content_journal);

$node_revisions->{vid} = $rec_num;
$node_revisions->{nid} = $rec_num;
$node_revisions->{uid} = 1;
$node_revisions->{title} = $ref->{journal_title};
$node_revisions->{timestamp} = $ref->{journal_crdate};
$node_revisions->{format} = 0;
$vars->{idfield} = &db_insert($dbh,"node_revisions",$node_revisions);

$journalcodes->{$ref->{journal_id}} = $rec_num;

print ". ";

$rec_num++;
}

print "Done Journals";



my $sth = $dbh->prepare("SELECT * FROM person");
$sth->execute();
$unum = 10;
while (my $ref = $sth -> fetchrow_hashref()) {

unless ($ref->{person_crdate}) { $ref->{person_crdate} = time; }

$users->{uid} = $unum;
$users->{name} = $ref->{person_title};
$users->{pass} = "6a5d8b78b0ac67c4396d56875aaf438c";
$users->{mail} = $ref->{person_email};
$users->{created} = $ref->{person_crdate};
$users->{status} = 1;
$users->{init} = $ref->{person_email};
$vars->{idfield} = &db_insert($dbh,"users",$users);

if ($ref->{person_city}) {
$profs1->{fid} = 1;
$profs1->{uid} = $unum;
$profs1->{value} = $ref->{person_city};
$vars->{idfield} = &db_insert($dbh,"profile_values",$profs1);
}

if ($ref->{person_province}) {
$profs2->{fid} = 2;
$profs2->{uid} = $unum;
$profs2->{value} = $ref->{person_province};
$vars->{idfield} = &db_insert($dbh,"profile_values",$profs2);
}

if ($ref->{person_country}) {
$profs3->{fid} = 3;
$profs3->{uid} = $unum;
$profs3->{value} = $ref->{person_country};
$vars->{idfield} = &db_insert($dbh,"profile_values",$profs3);
}

if ($ref->{person_organization}) {
$profs4->{fid} = 4;
$profs4->{uid} = $unum;
$profs4->{value} = $ref->{person_organization};
$vars->{idfield} = &db_insert($dbh,"profile_values",$profs4);
}

if ($ref->{person_html}) {
$profs5->{fid} = 5;
$profs5->{uid} = $unum;
$profs5->{value} = $ref->{person_html};
$vars->{idfield} = &db_insert($dbh,"profile_values",$profs5);
}

if ($ref->{person_weblog}) {
$profs6->{fid} = 6;
$profs6->{uid} = $unum;
$profs6->{value} = $ref->{person_weblog};
$vars->{idfield} = &db_insert($dbh,"profile_values",$profs6);
}

if ($ref->{person_xml}) {
$profs7->{fid} = 7;
$profs7->{uid} = $unum;
$profs7->{value} = $ref->{person_xml};
$vars->{idfield} = &db_insert($dbh,"profile_values",$profs7);
}

if ($ref->{person_foaf}) {
$profs8->{fid} = 8;
$profs8->{uid} = $unum;
$profs8->{value} = $ref->{person_foaf};
$vars->{idfield} = &db_insert($dbh,"profile_values",$profs8);
}

$usercodes->{$ref->{person_id}} = $unum;
$usernames->{$ref->{person_id}} = $ref->{person_title};
print ". ";

$unum++;
}

print "Done Persons";



print "Posts: ";
my $sth = $dbh->prepare("SELECT * FROM post");
$sth->execute();
while (my $ref = $sth -> fetchrow_hashref()) {

next unless ($ref->{post_type} eq "link");
unless ($ref->{post_crdate}) { $ref->{post_crdate} = time; }

$node->{nid} = $rec_num;
$node->{vid} = $rec_num;
$node->{type} = "content_post";
$node->{title} = $ref->{post_title};
$node->{uid} = 1;
$node->{status} = 1;
$node->{created} = $ref->{post_crdate};
$node->{changed} = $ref->{post_crdate};
$node->{promote} = 1;
$node->{moderate} = 0;
$node->{sticky} = 0;
$vars->{idfield} = &db_insert($dbh,"node",$node);

$node_content_post->{vid} = $rec_num;
$node_content_post->{nid} = $rec_num;
$vars->{idfield} = &db_insert($dbh,"node_content_post",$node_content_post);

$node_revisions->{vid} = $rec_num;
$node_revisions->{nid} = $rec_num;
$node_revisions->{uid} = 1;
$node_revisions->{title} = $ref->{post_title};
$node_revisions->{timestamp} = $ref->{post_crdate};
$node_revisions->{format} = 0;
$vars->{idfield} = &db_insert($dbh,"node_revisions",$node_revisions);

$postcodes->{$ref->{post_id}} = $rec_num;

print ". ";

$rec_num++;
}

print "Done Posts";


print "Publications: ";
my $sth = $dbh->prepare("SELECT * FROM publication");
$sth->execute();
while (my $ref = $sth -> fetchrow_hashref()) {

unless ($ref->{publication_crdate}) { $ref->{publication_crdate} = time; }

$node->{nid} = $rec_num;
$node->{vid} = $rec_num;
$node->{type} = "content_publication";
$node->{title} = $ref->{publication_title};
$node->{uid} = 1;
$node->{status} = 1;
$node->{created} = $ref->{publication_crdate};
$node->{changed} = $ref->{publication_crdate};
$node->{promote} = 1;
$node->{moderate} = 0;
$node->{sticky} = 0;
$vars->{idfield} = &db_insert($dbh,"node",$node);

$node_content_publication->{nid} = $rec_num;
$node_content_publication->{vid} = $rec_num;
$node_content_publication->{field_category_value} = $ref->{publication_category};
$node_content_publication->{field_details_value} = $ref->{publication_catdetails};
$node_content_publication->{field_volume_value} = $ref->{publication_volume};
$node_content_publication->{field_pages_value} = $ref->{publication_pages};
$node_content_publication->{field_pub_type_value} = $ref->{publication_type};
$vars_content_publication->{idfield} = &db_insert($dbh,"node_content_publication",$node_content_publication);

$node_revisions->{vid} = $rec_num;
$node_revisions->{nid} = $rec_num;
$node_revisions->{uid} = 1;
$node_revisions->{title} = $ref->{publication_title};
$node_revisions->{timestamp} = $ref->{publication_crdate};
$node_revisions->{format} = 0;
$vars->{idfield} = &db_insert($dbh,"node_revisions",$node_revisions);

$publicationcodes->{$ref->{post_id}} = $rec_num;

print ". ";

$rec_num++;
}

print "Done Publications";

print "Topics: ";
my $sth = $dbh->prepare("SELECT * FROM topic");
$sth->execute();
while (my $ref = $sth -> fetchrow_hashref()) {

unless ($ref->{topic_crdate}) { $ref->{topic_crdate} = time; }

$node->{nid} = $rec_num;
$node->{vid} = $rec_num;
$node->{type} = "content_topic";
$node->{title} = $ref->{topic_title};
$node->{uid} = 1;
$node->{status} = 1;
$node->{created} = $ref->{topic_crdate};
$node->{changed} = $ref->{topic_crdate};
$node->{promote} = 1;
$node->{moderate} = 0;
$node->{sticky} = 0;
$vars->{idfield} = &db_insert($dbh,"node",$node);

$node_content_topic->{vid} = $rec_num;
$node_content_topic->{nid} = $rec_num;
$node_content_topic->{field_where_value} = $ref->{topic_where};
$node_content_topic->{field_topic_type_value} = $ref->{topic_type};
$vars_content_topic->{idfield} = &db_insert($dbh,"node_content_topic",$node_content_topic);

$node_revisions->{vid} = $rec_num;
$node_revisions->{nid} = $rec_num;
$node_revisions->{uid} = 1;
$node_revisions->{title} = $ref->{topic_title};
$node_revisions->{timestamp} = $ref->{topic_crdate};
$node_revisions->{format} = 0;
$vars->{idfield} = &db_insert($dbh,"node_revisions",$node_revisions);

$topiccodes->{$ref->{post_id}} = $rec_num;

print ". ";

$rec_num++;
}

print "Done Topics";




print "Preparing Comments";

print "Comments: ";
$cid = 1;
my $sth = $dbh->prepare("SELECT * FROM post");
$sth->execute();
while (my $ref = $sth -> fetchrow_hashref()) {

next unless ($ref->{post_type} eq "comment");
unless ($ref->{post_crdate}) { $ref->{post_crdate} = time; }

$comment->{cid} = $cid;
$comment->{nid} = $postcodes->{$ref->{post_thread}};
$comment->{uid} = $usercodes->{$ref->{post_creator}};
$comment->{subject} = $ref->{post_title};
$comment->{comment} = $ref->{post_description};
$comment->{hostname} = $ref->{post_crip};
$comment->{timestamp} = $ref->{post_crdate};
$comment->{name} = $usernames->{$ref->{post_creator}};
$vars->{idfield} = &db_insert($dbh,"comments",$comment);

print ". ";

$cid++;
}

print "Done Comments";




print "Preparing Data Type Contents:";

print "Articles: ";
my $sth = $dbh->prepare("SELECT * FROM post");
$sth->execute();
while (my $ref = $sth -> fetchrow_hashref()) {
next unless ($ref->{post_type} eq "article");
$rec_num = $postcodes->{$ref->{post_id}};
#print "Post ID is $ref->{post_id}and record number is $rec_num
";

&do_contents("post","article",$rec_num,$ref);
print ". ";
}

print "Done Articles";



print "Authors: ";
my $sth = $dbh->prepare("SELECT * FROM author");
$sth->execute();
while (my $ref = $sth -> fetchrow_hashref()) {
$rec_num = $authorcodes->{$ref->{author_id}};
&do_contents("author","author",$rec_num,$ref);
print ". ";
}

print "Done Authors";


print "Events: ";
my $sth = $dbh->prepare("SELECT * FROM event");
$sth->execute();
while (my $ref = $sth -> fetchrow_hashref()) {
$rec_num = $eventcodes->{$ref->{event_id}};
&do_contents("event","event",$rec_num,$ref);
print ". ";
}

print "Done Events";


print "Journals: ";
my $sth = $dbh->prepare("SELECT * FROM journal");
$sth->execute();
while (my $ref = $sth -> fetchrow_hashref()) {
$rec_num = $journalcodes->{$ref->{journal_id}};
&do_contents("journal","journal",$rec_num,$ref);
print ". ";
}

print "Done Journals";

print "Posts: ";
my $sth = $dbh->prepare("SELECT * FROM post");
$sth->execute();
while (my $ref = $sth -> fetchrow_hashref()) {
next unless ($ref->{post_type} eq "link");
$rec_num = $postcodes->{$ref->{post_id}};
&do_contents("post","post",$rec_num,$ref);
print ". ";
}

print "Done Posts";

print "Publicationss: ";
my $sth = $dbh->prepare("SELECT * FROM publication");
$sth->execute();
while (my $ref = $sth -> fetchrow_hashref()) {
$rec_num = $publicationcodes->{$ref->{publication_id}};
&do_contents("publication","publication",$rec_num,$ref);
print ". ";
}

print "Done Publications";

print "Topics: ";
my $sth = $dbh->prepare("SELECT * FROM topic");
$sth->execute();
while (my $ref = $sth -> fetchrow_hashref()) {
$rec_num = $topiccodes->{$ref->{topic_id}};
&do_contents("topic","topic",$rec_num,$ref);
print ". ";
}

print "Done Topics";

print "Updating Sequences";
$sth = $dbh->prepare("UPDATE sequences SET id = $rec_num WHERE name = 'node_nid'");
$sth->execute();
$sth = $dbh->prepare("UPDATE sequences SET id = $rec_num WHERE name = 'node_revisions_vid'");
$sth->execute();
$sth = $dbh->prepare("UPDATE sequences SET id = $cid WHERE name = 'comments_cid'");
$sth->execute();
$sth = $dbh->prepare("UPDATE sequences SET id = $unum WHERE name = 'users_uid'");
$sth->execute();


if ($dbh) { $dbh->disconnect; }


exit;

sub do_contents {

my $tbl = shift;
my $tbout = shift;
my $rec_num = shift;
my $ref = shift;

# $dc++; if ($dc > 100) { exit; }
#print "do_contents: $tbl $rec_num
";

$field = $tbl."_author";
if ($ref->{$field}) {
$f0->{vid} = $rec_num;
$f0->{nid} = $rec_num;
$f0->{field_aut_value} = $ref->{$field};
$vars->{idfield} = &db_insert($dbh,"node_data_field_aut",$f0);
}

$field = $tbl."_journal";
if ($ref->{$field}) {
$f10->{vid} = $rec_num;
$f10->{nid} = $rec_num;
$f10->{field_jou_value} = $ref->{$field};
$vars->{idfield} = &db_insert($dbh,"node_data_field_jou",$f10);
}

$field = $tbl."_authorid";
if ($ref->{$field}) {
$author_nid = $authorcodes->{$ref->{$field}};
#print "$field - $ref->{$field} - $author_nid
";
$f1->{vid} = $rec_num;
$f1->{nid} = $rec_num;
$f1->{field_author_nid} = $author_nid;
$vars->{idfield} = &db_insert($dbh,"node_data_field_author",$f1);
}

$field = $tbl."_crdate";
if ($ref->{$field}) {
$f2->{vid} = $rec_num;
$f2->{nid} = $rec_num;
$f2->{field_crdate_value} = $ref->{$field};
$vars->{idfield} = &db_insert($dbh,"node_data_field_crdate",$f2);
}

$field = $tbl."_description";
if ($ref->{$field}) {
$f3->{vid} = $rec_num;
$f3->{nid} = $rec_num;
$f3->{field_description_value} = $ref->{$field};
$f3->{field_description_format} = 1;
$vars->{idfield} = &db_insert($dbh,"node_data_field_description",$f3);
}

$field = $tbl."_email";
if ($ref->{$field}) {
$f4->{vid} = $rec_num;
$f4->{nid} = $rec_num;
$f4->{field_email_email} = $ref->{$field};
$vars->{idfield} = &db_insert($dbh,"node_data_field_email",$f4);
}

$field = $tbl."_event";
if ($ref->{$field}) {
$event_nid = $eventcodes->{$ref->{$field}};
$f5->{vid} = $rec_num;
$f5->{nid} = $rec_num;
$f5->{field_event_nid} = $event_nid;
$vars->{idfield} = &db_insert($dbh,"node_data_field_event",$f5);
}

$field = $tbl."_id";
if ($ref->{$field}) {
$f6->{vid} = $rec_num;
$f6->{nid} = $rec_num;
$f6->{field_id_value} = $ref->{$field};
$vars->{idfield} = &db_insert($dbh,"node_data_field_id",$f6);
}

$field = $tbl."_link";
if ($ref->{$field}) {
$f7->{vid} = $rec_num;
$f7->{nid} = $rec_num;
$f7->{field_link_value} = $ref->{$field};
$vars->{idfield} = &db_insert($dbh,"node_data_field_link",$f7);
} else {

$field = $tbl."_url";
if ($ref->{$field}) {
$f7->{vid} = $rec_num;
$f7->{nid} = $rec_num;
$f7->{field_link_value} = $ref->{$field};
$vars->{idfield} = &db_insert($dbh,"node_data_field_link",$f7);
}
}

$field = $tbl."_journalid";
if ($ref->{$field}) {
$journal_nid = $journalcodes->{$ref->{$field}};
$f8->{vid} = $rec_num;
$f8->{nid} = $rec_num;
$f8->{field_journal_nid} = $journal_nid;
$vars->{idfield} = &db_insert($dbh,"node_data_field_journal",$f8);
}

$field = $tbl."_status";
if ($ref->{$field}) {
$f9->{vid} = $rec_num;
$f9->{nid} = $rec_num;
$f9->{field_link_value} = $ref->{$field};
$vars->{idfield} = &db_insert($dbh,"node_data_field_status",$f9);
}

return;
}

sub db_open {

my ($dsn,$user,$password) = @_;
my $dbh = DBI->connect($dsn, $user, $password);
unless ($dbh) { print "Database connect error: $! \n"; return; }
# if ($dbh) { $dbh->trace(2,"dberror.txt"); }
return $dbh;
}

sub db_insert { # Inserts record into table from hash

my $dbh = shift;
unless ($dbh) { print "Database handler not initiated"; return; }
my $table = shift;
unless ($table) { print "table not specified for input"; return; }
my $input = shift;
unless ($input) { print "No data provided on insert in $table"; return; }

# die "Unsupported data type specified to insert" unless (ref $input eq 'HASH' || ref $input eq 'Link' || ref $input eq 'Feed');

my $data= &db_prepare_input($dbh,$table,$input);

my $sql = "INSERT INTO $table ";
my(@sqlf, @sqlv, @sqlq) = ();

for my $k (sort keys %$data) {
push @sqlf, $k;
push @sqlq, '?';
push @sqlv, $data->{$k};
}
$sql .= '(' . join(', ', @sqlf) .') VALUES ('. join(', ', @sqlq) .')';
#if ($table eq "node_data_field_author") { print "$sql -- ",@sqlv,"

"; }

my $sth = $dbh->prepare($sql);
$sth->execute(@sqlv);

return $dbh->{'mysql_insertid'};

# Adapted from SQL::Abstract by Nathan Wiger
}


sub db_prepare_input { # Filters input hash to contain only columns in given table

my ($dbh,$table,$input) = @_;
my $data = ();

my @columns = &db_columns($dbh,$table); # Get a list of columns safeguard data input
foreach my $ikeys (keys %$input) { # Clean input for save
next unless ($input->{$ikeys}); # - no blank fields
next if ($ikeys =~ /_id$/i); # - do not change ID
next unless (&index_of($ikeys,\@columns) >= 0); # - input column must exist
$data->{$ikeys} = $input->{$ikeys}; # Transfer to input hash
#$data->{$ikeys} = &demoronise($data->{$ikeys}); # Fix non-standard character input
}

return $data;

}

sub db_columns {

my ($dbh,$table) = @_;
my @columns = ();
my $showstmt = "SHOW COLUMNS FROM $table";
my $sth = $dbh -> prepare($showstmt);
$sth -> execute();
while (my $showref = $sth -> fetchrow_hashref()) { push @columns,$showref->{Field}; }

unless (@columns) { print "Can't find any columns for $table"; return; }
return @columns;
}

sub index_of {

# Get item and array from input
my ($item,$array) = @_;

# Initialize counter
my $index_count = 0;
# For each item in the array
foreach my $i (@$array) {
# Return the counter value if it matches item
if ($item eq $i) { return $index_count; }

# Increment the counter
$index_count++;
}

# Return -1 if no match is returned
return "-1";
}