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('%s',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('%s',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('%s',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('%Y/%m/%d',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'
