[[updated feb 17 to include info on redirecting old slashcode urls to the new drupal urls]]

Started as part of a workshop I did at the 3rd NYC Drupal Camp on converting to/from drupal, I have finally finished converting the Autonomedia/Interactivist Info Exchange site from slash to drupal. It will relaunch soon at http://info.interactivist.net

While it might have been somewhat more "correct" for me to have written a php script that bootstrapped drupal and then just grab the data and pass it through drupal's user and node api's, I was stubbornly determined to convert the site by only writing sql queries between the databases.

Part of my reasoning for doing this sql only was that converting thousands of nodes and users via the drupal api can take forever, where direct database manipulation is fast.

While I will write this up better later and post it to drupal.org, I wanted to get it up before I got distracted onto other tasks.

so, without further delay: here are the step by step notes I took as I went through the process.
[much thanks to Blake Carver of LIS News http://www.lisnews.org/ for the motivation to finish and the queries that populate the node and node revisions tables]

first step:
setup two databases; one for drupal one for slash
in this example they are
autono_slash and autono_drupal

install drupal

go to modules config page and make sure all the modules you need are on:
in my case I had to turn on
aggrigator
blog
path
poll
profile

----------------
TAXONOMY

Create taxonomy vocabularies for the topics and sections of slashcode

make note of the vid's for those vocabularies
in my case:
Section=1
Topic=2

In order to make tagging of old content easier at a later step, we add in a column to the term_data table here that we will delete at the end of the process

ALTER TABLE `term_data` ADD `oldid` VARCHAR(5) NULL;

Now we move over terms for Sections (vid1)

INSERT INTO autono_drupal.term_data (vid,name,oldid) SELECT "1",title,id from autono_slash.sections;

And the same for Topics (vid2)

INSERT INTO autono_drupal.term_data (vid,name,oldid) SELECT "2",alttext,tid from autono_slash.topics;

Next we set all terms at having no parent terms

INSERT INTO autono_drupal.term_hierarchy (tid,parent) SELECT tid,"0" from autono_drupal.term_data

At this point you can check in the taxonomy admin page to see that your terms have been moved over.

----------------
USERS

This is the simple part.

drupal uses uid 0 for anon, slashcode uses uid 1 for anon

so, the primary admin of a slashcode site is usually uid2

this allows you to setup your drupal site, create a primary admin (uid 1) and not have any problems moving over your user data (no collision of ids)

later we'll set content and comments that should be anon from uid 1 to uid 0.

INSERT INTO autono_drupal.users (uid,name,mail,pass,status) SELECT uid, nickname, realemail, passwd, "1" FROM autono_slash.users WHERE uid >1;

Set user roles

create new user roles manually in drupal. you might need more roles depending on the various seclev settings you are using in slashcode. for my use I need the following in addition to the standard drupal roles:
blog user
site admin
full admin

make note of the rid's for those roles
in my case:
blog user=3
site admin=4
full admin=5

for my site, I'm using those with slashcode seclev of 99 or higher = blog user, 200 or higher = site admin, 800 or higher = full admin

INSERT INTO autono_drupal.users_roles (uid,rid) SELECT uid,"3" from autono_slash.users where seclev > 99;

INSERT INTO autono_drupal.users_roles (uid,rid) SELECT uid,"4" from autono_slash.users where seclev > 200;

INSERT INTO autono_drupal.users_roles (uid,rid) SELECT uid,"5" from autono_slash.users where seclev > 800;

-------------------

CONTENT!

now we create a temporary table in the database that will be used to create all content.

it is a merger of the node table and the slash story table

CREATE TABLE autono_drupal.slash_content_convert (
nid int( 10 ) NOT NULL auto_increment,
sid varchar( 16 ),
tid smallint( 5 ),
newtid smallint(5),
uid mediumint( 8 ),
title varchar( 100 ),
dept varchar( 100 ) default NULL ,
time datetime,
hits mediumint( 8 ),
section varchar( 30 ),
discussion mediumint( 8 ),
submitter mediumint( 8 ),
day_published date,
last_update timestamp,
body_length mediumint( 8 ),
word_count mediumint( 8 ),
vid int( 10 ),
type varchar( 32 ),
status int( 4 ),
comment int( 2 ),
promote int( 2 ),
moderate int( 2 ),
sticky int( 2 ),
body longtext,
teaser longtext,
log longtext,
timestamp int( 11 ),
format int( 4 ),
PRIMARY KEY ( nid )
);

now we move over the content!
the main idea here is to move the stories over and get a nid/sid relationship created by the auto increment column

first standard slash "stories", the primary content type for slashcode become story nodes (note the type of "story" in the select/insert)

INSERT INTO autono_drupal.slash_content_convert (sid,tid,uid,title,time,section,discussion,submitter,day_published,last_update,teaser,body,type)
SELECT autono_slash.stories.sid,autono_slash.stories.tid,autono_slash.stories.uid,autono_slash.stories.title,autono_slash.stories.time,autono_slash.stories.section,autono_slash.stories.discussion,autono_slash.stories.submitter,autono_slash.stories.day_published,autono_slash.stories.last_update,autono_slash.story_text.introtext,autono_slash.story_text.bodytext,"story" from autono_slash.stories join autono_slash.story_text on autono_slash.stories.sid = autono_slash.story_text.sid;

next we move over the slashcode journals preparing it to become blog content.

INSERT INTO autono_drupal.slash_content_convert (tid,uid,title,time,discussion,body,type)
SELECT autono_slash.journals.tid,autono_slash.journals.uid,autono_slash.journals.description,autono_slash.journals.date,autono_slash.journals.discussion,autono_slash.journals_text.article, "blog" from autono_slash.journals join autono_slash.journals_text on autono_slash.journals.id = autono_slash.journals_text.id;

soon we'll come back to content and move things to the drupal node/revisions tables, but first we prepare comments in the same way as we did content

create a unified table combining critical elements of both schema, essentially this is a subset of the drupal comment table with the sid of slashcode. the important thing is that this will be used to combine journal "discussions" with story comments, as drupal does not make the same distinctions that slash does. (we will add name and email fields for comments after comments are converted to make the queries more simple to follow (fewer joins)

CREATE TABLE autono_drupal.slash_comment_convert (
cid int(11) NOT NULL auto_increment,
pid int(11),
nid int(11),
uid int(11),
subject varchar(64),
comment longtext,
timestamp int(11),
comment_date datetime,
format int(11),
thread varchar(255),
name varchar(60),
mail varchar(64),
discussion int(11),
oldcid int(11),
oldpid int(11),
PRIMARY KEY ( cid )
);

INSERT INTO autono_drupal.slash_comment_convert (uid,subject,comment,comment_date,oldcid,oldpid,discussion)
SELECT autono_slash.comments.uid,autono_slash.comments.subject,autono_slash.comment_text.comment,autono_slash.comments.date,autono_slash.comments.cid,autono_slash.comments.pid,autono_slash.comments.sid from autono_slash.comments join autono_slash.comment_text on autono_slash.comments.cid=autono_slash.comment_text.cid;

now... at this point in my work, I realize that we might just need to use the slashcode discussionID as nodeID to make it all easier; as well we might just need to keep the old cid and pid's as well.

so for that we alter the node and comment table
ALTER TABLE `node` CHANGE `nid` `nid` INT( 10 ) UNSIGNED NOT NULL
ALTER TABLE `comments` CHANGE `cid` `cid` INT( 11 ) NOT NULL

-------------------------------

clean up and sync of meta data (taxonomy, etc)

we need to set the newtid to the drupal term id instead of the slash topic id

update autono_drupal.slash_content_convert set newtid = (select tid from term_data where oldid=slash_content_convert.tid and vid=2);

next we need to map the slash section column with the drupal tid
mysql> select distinct section from slash_content_convert;
+---------------+
| section |
+---------------+
| articles |
| features |
| Events |
| BookReviews |
| news |
| the_new_war |
| analysis |
| announcements |
| books |
| |
| rnc |
| NULL |
+---------------+

+-----+-----+---------------------+-------------+--------+-------+
| tid | vid | name | description | weight | oldid |
+-----+-----+---------------------+-------------+--------+-------+
| 1 | 1 | Articles | NULL | 0 | 1 |
| 2 | 1 | Announcements | NULL | 0 | 2 |
| 3 | 1 | Events | NULL | 0 | 3 |
| 4 | 1 | All Sections | NULL | 0 | 4 |
| 5 | 1 | Analysis & Polemic | NULL | 0 | 5 |
| 6 | 1 | Reviews | NULL | 0 | 6 |
| 7 | 1 | News | NULL | 0 | 7 |
| 8 | 1 | At the Brink | NULL | 0 | 8 |
| 9 | 1 | Books | NULL | 0 | 9 |
| 10 | 1 | Index | NULL | 0 | 10 |
| 11 | 1 | RNC 2004 | NULL | 0 | 11 |

update autono_drupal.slash_content_convert set section=1 where section="articles";
update autono_drupal.slash_content_convert set section=2 where section="announcements";
update autono_drupal.slash_content_convert set section=3 where section="Events";
update autono_drupal.slash_content_convert set section=1 where section="";
update autono_drupal.slash_content_convert set section=5 where section="analysis";
update autono_drupal.slash_content_convert set section=6 where section="BookReviews";
update autono_drupal.slash_content_convert set section=7 where section="news";
update autono_drupal.slash_content_convert set section=8 where section="the_new_war";
update autono_drupal.slash_content_convert set section=9 where section="books";
update autono_drupal.slash_content_convert set section=1 where section is NULL;
update autono_drupal.slash_content_convert set section=11 where section="rnc";

--
move all comments and content tagged as uid1 to uid0

update slash_comment_convert set uid=0 where uid=1;
update slash_content_convert set uid=0 where uid=1;

turns out that some journal entries don't have discussion id's. so to fix this and make sure I did not use a nid already in use

update slash_content_convert set discussion=(nid+5000) where discussion is NULL;

move nodes to node table and revisions table

insert into node (nid,vid,type,title,uid,status,created,changed,comment,promote,moderate,sticky) SELECT
autono_drupal.slash_content_convert.discussion,
autono_drupal.slash_content_convert.discussion,
autono_drupal.slash_content_convert.type,
autono_drupal.slash_content_convert.title,
autono_drupal.slash_content_convert.uid,"1",
UNIX_TIMESTAMP(slash_content_convert.time),
UNIX_TIMESTAMP(slash_content_convert.time),
"2","0","0","0" from slash_content_convert;

insert into node_revisions (nid,vid,uid,title,body,teaser,log,timestamp,format) SELECT
autono_drupal.slash_content_convert.discussion,
autono_drupal.slash_content_convert.discussion,
autono_drupal.slash_content_convert.uid,
autono_drupal.slash_content_convert.title,
concat(autono_drupal.slash_content_convert.teaser,autono_drupal.slash_content_convert.body),
autono_drupal.slash_content_convert.teaser,
"Imported From Slashcode",
UNIX_TIMESTAMP(slash_content_convert.time),
"1" from slash_content_convert;

move comments to comments table

insert into comments (cid,pid,nid,uid,subject,comment,timestamp,status,format,thread) SELECT oldcid,oldpid,discussion,uid,subject,comment,UNIX_TIMESTAMP(slash_comment_convert.comment_date),0,1,'01/' from slash_comment_convert;

update node_revisions set teaser = body where teaser ="";

set taxonomy terms for nodes based on section and tid

insert into term_node (nid,tid) select discussion,tid from slash_content_convert;

insert into term_node (nid,tid) select discussion,section from slash_content_convert;

some nodes came over with out body copy

update node_revisions set body = teaser where body ="";

reset nid and cid as auto_increment

set sequences, for no real logical reason I wanted to add 10 to the highest node/comment/user id when I set the sequences table.

update sequences set id=(select uid from users order by uid desc limit 1)+10 where name="users_uid";

update sequences set id=(select nid from node order by nid desc limit 1)+10 where name="node_nid";

update sequences set id=(select vid from node_revisions order by vid desc limit 1)+10 where name="node_revisions_vid";

and with that, we're done

I still have some more modules to put in.

I could put in userpoints and move over old user's karma

I should put in captcha and recaptcha for preventing spam

I need to put in the event module and start using event nodes instead of stories for the events section.

[UPDATE]

i screwed something up, so I had to do the following to get the blog content in the node_revisions table

UPDATE node_revisions LEFT JOIN slash_content_convert ON node_revisions.nid=slash_content_convert.discussion SET node_revisions.body= slash_content_convert.body WHERE slash_content_convert.type="blog";

UPDATE node_revisions LEFT JOIN slash_content_convert ON node_revisions.nid=slash_content_convert.discussion SET node_revisions.teaser= slash_content_convert.body WHERE slash_content_convert.type="blog";

then I realized I did not move over the account creation date and last access dates. this confused the initial test users so I added it in

update autono_drupal.users left join autono_slash.users_info on autono_drupal.users.uid=autono_slash.users_info.uid set autono_drupal.users.created = UNIX_TIMESTAMP(autono_slash.users_info.created_at);

---

ok, now it's time to launch the site and get the old slash urls redirecting to new urls

make a table for this in the drupal db
CREATE TABLE `slash_redirects` (
`sid` VARCHAR( 36 ) NOT NULL ,
`nid` SMALLINT NOT NULL ,
PRIMARY KEY ( `sid` )
) TYPE = MYISAM ;

then populate that from the slash_content_convert table
insert into slash_redirects (sid,nid) select sid,discussion from slash_content_convert where sid !="";

now, we need to write a simple perl script to handle taking calls to article.pl, parsing out the sid and sending a 301 redirect to the browser. this hopefully will keep the damage to search engine listings to a minimum and preserve people's bookmarks to specific old content

here is my article.pl script

#!/usr/bin/perl -w
use strict;
use CGI;
use DBI;
my $query = CGI->new();
my $sid=$query->param('sid');
my $nidquery='SELECT nid FROM slash_redirects WHERE sid ="'.$sid.'"';
my %varinfo = do 'hide/vars.txt';
my $dsn = "dbi:mysql:$varinfo{sqldbname}:localhost:3306";
my $dbh = DBI->connect($dsn, $varinfo{sqldbuser}, $varinfo{sqldbpass});
my $sth= $dbh->prepare($nidquery);
$sth->execute();
my $nid;
my @nidresult=$sth->fetchrow_array();
my $nodetogoto="http://info.interactivist.net/node/".@nidresult[0];
$sth->finish();
$dbh->disconnect();
my $q = CGI->new();
print $q->redirect(
-location => $nodetogoto,
-status => 301,
);

-----
the script calls in db user/pass info from an external file that is protected from download/access by .htaccess

the var file is in the following format

sqldbserver => 'mysql',
sqldbname => 'database_name',
sqldbuser => 'database_user',
sqldbpass => 'db_password',
sqldbhost => 'localhost',

-----