Jay Tuley

So my blog is back online finally, gave up on Typo and Rails. It was just too hard to keep running, so I’m back to good old PHP.

The blog software I chose to replace typo is Serendipity, mainly because it supports sqlite and since i was using sqilte with typo to make the migration easier.

The first hurdle faced was that php’s sqlite is sqlite version 2 instead of 3 and it’s hard to find a program that will version 2 let alone both, however looking through Serendipity’s source and found that it had sqlite3 support and googling a function call in that source let me to the php module used for sqlite3 support, php-sqlite3.

Unfortunately there isn’t a gentoo package for it, and I am not familar with php modules so it took a little bit of effort to get it installed, but i did.

Finally i used sqliteman to map the columns from the old blog engine to the new.

Here is the sql I used below.

Content:

INSERT INTO 
		s9y_entries
	SELECT
		contents.id as id,
		contents.title as title,
		STRFTIME('%25s',contents.published_at) AS timestamp,
		contents.body AS body,
		COUNT(feedback.type) AS comments,
		contents.extended AS extended,
		0 AS trackbacks,
		0 AS exflag,
		contents.author AS author,
		contents.user_id AS authorid,
		CASE
			WHEN contents.state='draft'
			THEN 'true' 
			ELSE 'false' 
			END AS isdraft,
		'true' AS allow_comments,
		STRFTIME('%25s',contents.updated_at) AS last_modified,
		'false' AS moderate_comments
	FROM
			contents
		LEFT JOIN
				feedback
			ON feedback.article_id = contents.id
	GROUP BY contents.id

Feedback:

INSERT INTO
		s9y_comments 
	SELECT
		id,
		article_id AS entry_id,
		0,
		STRFTIME('%25s',published_at) AS timestamp,
		title AS title,
		author AS author,
		email AS email,
		url AS url,
		ip AS ip,
		body AS body,
		"NORMAL" AS type,
		0 AS subscribed,
		"approved" AS status,
		null
	FROM
		feedback

Categories (note, I cheated on categories because i didn’t like how i did them originally and planned on manually tagging articles, so I made them all 0, i am not sure if that was necessary but i did it.):

INSERT INTO
		s9y_entrycat 
	SELECT 
		id,
		0 AS categoryid
	FROM
		contents
	WHERE
		state <> 'draft'

The last thing i did was permalinks, i was hoping if I did this i could map the old link scheme, however this didn’t work so I wouldn’t recommend doing this last sql command but I ran it so I’m posting it as well anyway.

INSERT INTO 
  s9y_permalinks(permalink,entry_id,type) 
 SELECT
  "articles/"
  ||strftime('%25Y/%25m/%25d',published_at)
  ||"/"
  ||trim(
    replace(
     replace(
      replace(
       replace(
        replace(
         replace(
          replace(
	      replace(
		  replace(
		   replace(
		    replace(
			replace(
			 replace(
			  replace(
			   replace(
                  lower(title)
				,' ','-')
			   ,',','-')
               ,'!','-')
              ,"'",'-')
             ,'/','-')
		  ,'?','-')
		,':','-')
        ,'(','-')
       ,')','-')
	 ,'"','-')
	,'>','-')
    ,'*','-')
   ,'----','-')
  ,'---','-')
,'--','-')
,'-')
    AS permalink,
  id,
  'entry' as type
   FROM contents
   WHERE state <> 'draft'