Migrate SPIP 2 and 3 to Wordpress with MySQL

Migrate SPIP 2 to Wordpress with SQL.md

##Imports terms

REPLACE INTO wp_terms(term_id, name, slug, term_group)
SELECT    id_rubrique, titre, CONCAT("rub",id_rubrique), 1 FROM spip_rubriques;

Update urls

UPDATE wp_terms, spip_urls
SET slug = spip_urls.url
WHERE spip_urls.id_objet = term_id
AND spip_urls.type = "rubrique"

Import posts

REPLACE INTO wp_posts(
  ID
  , post_author
  , post_date, post_date_gmt
  , post_content
  , post_title
  , to_ping , pinged
  , post_modified, post_modified_gmt

)

SELECT
  p.id_article
  , u.id_auteur
  , p.date, p.date
  , concat(p.chapo, p.descriptif, p.texte)
  , titre
  , '', ''
  , p.date_modif, p.date_modif
FROM
  spip_articles AS p
  LEFT JOIN spip_auteurs_articles AS u ON u.id_article = p.id_article;

Link posts to terms

REPLACE INTO   wp_term_relationships(object_id, term_taxonomy_id)
SELECT  p.id_article, p.id_rubrique FROM spip_articles AS p;

Import comments

REPLACE INTO wp_comments(
          comment_ID
        , comment_post_ID
        , comment_author
        , comment_author_email
        ,comment_author_url
        , comment_date
        , comment_date_gmt
        , comment_content
        , comment_parent
        , comment_approved
)

SELECT
          id_forum
        , id_article
        , auteur
        , email_auteur
        , url_site
        , date_heure
        , date_heure
        , texte
        , id_parent
        , 0
FROM  spip_forum;

Approve comments

update wp_comments, spip_forum
SET comment_approved = 1
WHERE wp_comments .comment_ID = spip_forum.id_article
AND spip_forum.statut = "publie"

Update comments numbers per post

UPDATE wp_posts
SET comment_count = (SELECT COUNT( * )
from wp_comments WHERE comment_post_ID = ID and comment_approved = 1);

Update the syntax

 update wp_posts set post_content = replace(post_content, '{{', ' <b> ') where instr(post_content, '{{') > 0; 
 update wp_posts set post_content = replace(post_content, '}}', ' </b> ') where instr(post_content, '}}') > 0; 
 update wp_posts set post_content = replace(post_content, '{', ' <i> ') where instr(post_content, '{') > 0; 
 update wp_posts set post_content = replace(post_content, '}', ' </i> ') where instr(post_content, '}') > 0; 
 update wp_posts set post_content = replace(post_content, '{{{', ' <h1> ') where instr(post_content, '{{{') > 0; 
 update wp_posts set post_content = replace(post_content, '}}}', ' </h1> ') where instr(post_content, '}}}') > 0; 
 update wp_posts set post_content = replace(post_content, '[[', ' <blockquote> ') where instr(post_content, '[[') > 0; 
 update wp_posts set post_content = replace(post_content, ']]', ' </blockquote> ') where instr(post_content, ']]') > 0;
 update wp_posts set post_content = replace(post_content, '*]', ' </strong></i> ') where instr(post_content, '*]') > 0; 
 update wp_posts set post_content = replace(post_content, '[*', ' </strong></i> ') where instr(post_content, '*]') > 0;

Migrate SPIP 3 to Wordpress with SQL.md

Imports terms

REPLACE INTO wp_terms(term_id, name, slug, term_group)
SELECT    id_rubrique, titre, CONCAT("rub",id_rubrique), 1 FROM spip_rubriques;

Update urls

UPDATE wp_terms, spip_urls
SET slug = spip_urls.url
WHERE spip_urls.id_objet = term_id
AND spip_urls.type = "rubrique"

Import posts

  REPLACE INTO wp_posts(
    ID
    , post_author
    , post_date, post_date_gmt
    , post_content
    , post_title
    , to_ping , pinged
    , post_modified, post_modified_gmt
  
  )
  
  SELECT
    p.id_article
    , u.id_auteur
    , p.date, p.date
    , concat(p.chapo, p.descriptif, p.texte)
    , titre
    , '', ''
    , p.date_modif, p.date_modif
  FROM
    spip_articles AS p
    LEFT JOIN spip_auteurs_liens AS u ON u.id_objet = p.id_article;

Link posts to terms

REPLACE INTO   wp_term_relationships(object_id, term_taxonomy_id)
SELECT  p.id_article, p.id_rubrique FROM spip_articles AS p;

Update taxonomy count

UPDATE `wp_term_taxonomy` TT SET TT.count = ( SELECT count( object_id )
FROM `wp_term_relationships` TR
WHERE TR.term_taxonomy_id = TT.term_taxonomy_id )

Import comments

REPLACE INTO wp_comments(
          comment_ID
        , comment_post_ID
        , comment_author
        , comment_author_email
        ,comment_author_url
        , comment_date
        , comment_date_gmt
        , comment_content
        , comment_parent
        , comment_approved
)

SELECT
          id_forum
        , id_objet
        , auteur
        , email_auteur
        , url_site
        , date_heure
        , date_heure
        , texte
        , id_parent
        , 0
FROM  spip_forum;

Approve comments

update wp_comments, spip_forum
SET comment_approved = 1
WHERE wp_comments .comment_ID = spip_forum.id_objet
AND spip_forum.statut = "publie"

Update comments numbers per post

UPDATE wp_posts
SET comment_count = (SELECT COUNT( * )
from wp_comments WHERE comment_post_ID = ID and comment_approved = 1);

Update the syntax

 update wp_posts set post_content = replace(post_content, '{{', ' <b> ') where instr(post_content, '{{') > 0; 
 update wp_posts set post_content = replace(post_content, '}}', ' </b> ') where instr(post_content, '}}') > 0; 
 update wp_posts set post_content = replace(post_content, '{', ' <i> ') where instr(post_content, '{') > 0; 
 update wp_posts set post_content = replace(post_content, '}', ' </i> ') where instr(post_content, '}') > 0; 
 update wp_posts set post_content = replace(post_content, '{{{', ' <h1> ') where instr(post_content, '{{{') > 0; 
 update wp_posts set post_content = replace(post_content, '}}}', ' </h1> ') where instr(post_content, '}}}') > 0; 
 update wp_posts set post_content = replace(post_content, '[[', ' <blockquote> ') where instr(post_content, '[[') > 0; 
 update wp_posts set post_content = replace(post_content, ']]', ' </blockquote> ') where instr(post_content, ']]') > 0;
 update wp_posts set post_content = replace(post_content, '*]', ' </strong></i> ') where instr(post_content, '*]') > 0; 
 update wp_posts set post_content = replace(post_content, '[*', ' </strong></i> ') where instr(post_content, '*]') > 0;

Update taxonomy

UPDATE `wp_term_taxonomy` TT SET TT.count = ( SELECT count( object_id )
FROM `wp_term_relationships` TR
WHERE TR.term_taxonomy_id = TT.term_taxonomy_id )