solo 标签

从wordpress迁移到b3log - solo 有更新!

  |   4 评论   |   808 浏览

前提条件

  1. 首先安装好solo,创建用户保证可以访问。
  2. 确保wordpress数据库与solo数据库可以同时访问。

执行脚本

以solo数据库为访问库,wordpress数据库被我命名为wp了,如果命名不一致,将wp改为你的wordpress数据库名即可。

导入文章表数据

INSERT INTO b3_solo_article

SELECT
	posts.id AS oId
	,posts.post_title AS articleTitle
	,'' AS articleAbstract
	,'' AS articleAbstractText
	,(SELECT
			group_concat(terms.name)
		FROM
			wp.wp_terms terms,
			wp.wp_term_relationships rs
		WHERE
			terms.term_id = rs.term_taxonomy_id
		AND rs.object_id = posts.ID
		GROUP BY rs.object_id ) AS articleTags
	,(select oId from b3_solo_user LIMIT 1) AS articleAuthorId
	,0 AS articleCommentCount
	,0 AS articleViewCount
	,posts.post_content AS articleContent
	,concat('/articles/',year(posts.post_date),'/',LPAD(month(posts.post_date), 2, 0),'/',LPAD(DAY(posts.post_date),2,0),'/',posts.id,'.html') AS articlePermalink
	,0 AS articlePutTop
	,concat(unix_timestamp(posts.post_date),'000') AS articleCreated
	,concat(unix_timestamp(posts.post_date),'000') AS articleUpdated
	,RAND() AS articleRandomDouble
	,1 AS articleSignId
	,1 AS articleCommentable
	,'' AS articleViewPwd
	,'https://img.hacpai.com/bing/20180320.jpg?imageView2/1/w/1280/h/720/interlace/1/q/100' AS articleImg1URL
	,(CASE WHEN posts.post_status = 'publish' THEN 0 ELSE 1 END) AS articleStatus
FROM
	wp.wp_posts posts
WHERE posts.post_parent = 0
AND posts.post_type != 'nav_menu_item'
AND posts.post_type != 'page'

导入存档日期表数据

INSERT INTO b3_solo_archivedate
SELECT DISTINCT
	concat(
		unix_timestamp(
			str_to_date(
				concat(
					YEAR (posts.post_date),
					'-',
					LPAD(MONTH(posts.post_date), 2, 0),
					'-01'
				),
				'%Y-%m-%d'
			)
		),
		'000'
	) AS oId
	,concat(
		unix_timestamp(
			str_to_date(
				concat(
					YEAR (posts.post_date),
					'-',
					LPAD(MONTH(posts.post_date), 2, 0),
					'-01'
				),
				'%Y-%m-%d'
			)
		),
		'000'
	) AS archiveDate_oId
FROM
	wp.wp_posts posts
WHERE
	posts.post_parent = 0
AND posts.post_type != 'nav_menu_item'
AND posts.post_type != 'page'

GROUP BY concat(
		unix_timestamp(
			str_to_date(
				concat(
					YEAR (posts.post_date),
					'-',
					LPAD(MONTH(posts.post_date), 2, 0),
					'-01'
				),
				'%Y-%m-%d'
			)
		),
		'000'
	)

导入存档-文章关联表数据

INSERT INTO b3_solo_archivedate_article
SELECT
	posts.id AS oId,
	concat(
		unix_timestamp(
			str_to_date(
				concat(
					YEAR (posts.post_date),
					'-',
					LPAD(MONTH(posts.post_date), 2, 0),
					'-01'
				),
				'%Y-%m-%d'
			)
		),
		'000'
	) AS archiveDate_oId,
	posts.id AS article_oId
FROM
	wp.wp_posts posts
WHERE
	posts.post_parent = 0
AND posts.post_type != 'nav_menu_item'
AND posts.post_type != 'page'

导入标签表数据

INSERT INTO b3_solo_tag
SELECT
	term_id AS oId,
	name AS tagTitle
FROM
	wp.wp_terms

导入标签-文章关联表数据

INSERT INTO b3_solo_tag_article
SELECT
	CONCAT(object_id,term_taxonomy_id) AS oId,
	object_id AS article_oId,
	term_taxonomy_id AS tag_oId
FROM
	wp.wp_term_relationships

完成