#!/usr/bin/python # coding=UTF-8 # # Copyright (c) 2010, Ingo Juergensmann # # This program is free software; you can redistribute it and/or modify # it under the terms of the GNU General Public License as published by # the Free Software Foundation; either version 2 of the License, or # (at your option) any later version. # # This program is distributed in the hope that it will be useful, # but WITHOUT ANY WARRANTY; without even the implied warranty of # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the # GNU General Public License for more details. # You should have received a copy of the GNU General Public License # along with this program; if not, write to the Free Software # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA # # s9y2drupal.py comes with ABSOLUTELY NO WARRANTY # # some database settings like username, host and database names # for serendipity s9yuser="ij" s9yhost="192.168.254.1" s9ydb="serendipity" #for drupal6 d6user="ij" d6host="192.168.254.1" d6db="drupal6_blogij" # we need some modules import psycopg2 from string import * import sys, os s9ydsn="dbname=serendipity user=ij host=192.168.254.1" s9ydb=psycopg2.connect(s9ydsn) s9y=s9ydb.cursor() d6dsn="dbname=drupal6_blogij user=ij host=192.168.254.1" d6db=psycopg2.connect(d6dsn) d6=d6db.cursor() sql="select id from s9y_entries order by id" s9y.execute(sql) s9y_ids=s9y.fetchall() # a simple counter i=0 # iterate over all IDs from s9y for id in s9y_ids: sid=id[0] i=i+1 print str(i)+"/"+str(len(s9y_ids)), str(sid), ": ", sql="select title, body || extended, timestamp from s9y_entries where id='%s'" % sid s9y.execute(sql) res=s9y.fetchone() title = replace(res[0], "'", "\\'") body = replace(res[1], "'", "\\'") timestamp = res[2] sql="select categoryid from s9y_entrycat where entryid='%s'" % sid s9y.execute(sql) res=s9y.fetchone() # here you can find my sample database. # You need to adopt the IF statements below to match tags/taxonomy terms # term_data term_node # tid, vid, name nid, vid, tid # 1 1 Allgemeines # 2 1 Debian # 3 1 Foto # 4 1 Job # 5 1 Privat # s9y_category # cid name # 1 ij # 2 Debian # 3 privat # 4 Job # 5 Allgemeines # 6 Foto cid=res[0] if cid==1: tid=0 elif cid==2: tid=2 elif cid==3: tid=5 elif cid==4: tid=4 elif cid==5: tid=1 elif cid==6: tid=3 else: tid=0 # some output print title #print body #print timestamp # insert s9y entries into drupal6 database sql="insert into node_revisions (uid, body, teaser, log, timestamp, format, nid, title) values ('3', '%s', '%s', '', '%s', '1', '%s', '%s')" % (body, body, timestamp, i, title) d6.execute(sql) # we need the vid to proceed sql="select vid from node_revisions where nid='%s'" % i d6.execute(sql) try: vid=d6.fetchone()[0] except: print "No valid vid returned!" sys.exit() # we need the node id. For this we ask for last serial number (node_nid_seq) sql="select last_value from node_nid_seq" d6.execute(sql) try: nid=int(d6.fetchone()[0])+1 except: print "No valid nid returned!" sys.exit() # now inserting the node sql="insert into node (vid, type, uid, status, created, changed, comment, promote, moderate, sticky, language, tnid, translate, title) values ('%s', 'blog', '3', '1', '%s', '%s', '2', '1', '0', '0', '', '0', '0', '%s')" % (vid, timestamp, timestamp, title) d6.execute(sql) # getting the s9y permalink for.... sql="select permalink from s9y_permalinks where entry_id='%s' and type='entry'" % sid s9y.execute(sql) permalink=s9y.fetchone()[0] # ... inserting into d6 DB as url_alias. No need for URL rewrites in Apache. sql="insert into url_alias (src, dst, language) values ('node/%s', '%s', '')" % (nid, permalink) d6.execute(sql) # we could set the counter as well, when we knew this information from s9y sql="insert into node_counter (nid,totalcount,daycount,timestamp) values ('%s', '100', '0','%s')" % (nid, timestamp) d6.execute(sql) # now we want to deal with the s9y tags sql="select tag from s9y_entrytags where entryid='%s'" % sid s9y.execute(sql) tags=s9y.fetchall() # interating over tags for t in tags: tag=t[0] try: # when there is already a tag in term_data, get the id and add the node/term to term_node table # this will fail when there's no term in term_data. An exception will happen then... sql="select tid from term_data where name='%s' and vid='3'" % tag d6.execute(sql) tagtid=d6.fetchone()[0] sql="insert into term_node (nid, vid, tid) values ('%s', '%s', '%s')" % (nid, vid, tagtid) d6.execute(sql) # TODO: we need to insert into term_hierarchy as well, but this seems tricky at this place # because of the exception handling #if len(str(tagtid)): #try: # sql="insert into term_hierarchy (tid, parent) values ('%s', '0')" % (tagtid) # d6.execute(sql) #except Exception, e: # print "Term_hierarchy: ", e except Exception, e: #print "Exception: ", e # When there's no term found, insert it and proceed as above sql="insert into term_data (vid, name) values ('3', '%s')" % tag d6.execute(sql) sql="select tid from term_data where name='%s' and vid='3'" % tag d6.execute(sql) tagtid=d6.fetchone()[0] sql="insert into term_node (nid, vid, tid) values ('%s', '%s', '%s')" % (nid, vid, tagtid) d6.execute(sql) sql="insert into term_hierarchy (tid, parent) values ('%s', '0')" % (tagtid) d6.execute(sql) # now proceeding with comments from s9y sql="select id from s9y_comments where entry_id='%s' order by timestamp" % sid s9y.execute(sql) comments=s9y.fetchall() # another helping var as counter z=0 # iterate over comments for comm in comments: comment=comm[0] sql="select parent_id, title, author, email, url, body, ip, timestamp from s9y_comments where id='%s'" % comment s9y.execute(sql) res=s9y.fetchone() z=z+1 parentid = res[0] ctitle = replace(res[1], "'", "'") cauthor = replace(res[2], "'", "'") cemail = res[3] curl = res[4] cbody = replace(res[5], "'", "'") cip = res[6] ctimestamp = res[7] # limit for title is 64 in d6, whereas it is 150 in s9y ctitle=ctitle[0:63] # is there a strange/incomplete URL? try: if len(curl)<6: curl="" except: pass # inserting into d6 comments sql="insert into comments (pid, nid, uid, subject, comment, hostname, timestamp, status, format, thread, name, mail, homepage) values ('%s', '%s', '0', '%s', '%s', '%s', '%s', '0', '1', '01', '%s', '%s', '%s')" % (parentid, nid, ctitle, cbody, cip, ctimestamp, cauthor, cemail, curl) d6.execute(sql) # if there's a comment, insert it into node_comment_statistics, else proceed try: sql="insert into node_comment_statistics (last_comment_timestamp, last_comment_uid, comment_count, nid) values ('%s', '0', '%s', '%s')" % (ctimestamp, z, nid) d6.execute(sql) except: pass # add term to term_node sql="insert into term_node (nid, vid, tid) values ('%s', '%s', '%s')" % (nid, vid, tid) d6.execute(sql) # everything went well, so we can now commit() and write the data to disk/db d6db.commit() # close the DB connections s9y.close() d6.close()