2008-03-01 9:09 pm
jay.tuley.name back online! by Jay
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'
