Converting WordPress from Gengo to WPML – part 1

As I described in the posts about upgrading WordPress I am investigating automatic conversion from Gengo to WPML. Of course I searched in the WPML site and I found a few threads in the forum.

[NOTE: This post needs to be updated for the newest version of WPML. The SQL is out of date.]

One topic mentions a partial solution by Marco Bernasocchi. I started with that. In summary:

  • disable gengo
  • install wpml
  • choose the default language and create the languages you had in gengo.
  • update the post language with the following SQL statement:
UPDATE wp_icl_translations wpml
LEFT JOIN wp_posts post ON wpml.element_id = post.ID
LEFT JOIN wp_post2lang gengo ON gengo.post_id = post.ID
LEFT JOIN wp_languages gengoLang ON gengoLang.language_id = gengo.language_id
SET wpml.language_code = gengoLang.code
WHERE wpml.element_type ='post'

This will give each post and page in WPML the language that it had in Gengo. However, it does not tie together posts and pages that are translations of each other. Neither does it deal with the translations of categories and tags.

The above SQL statement contains an unnecessary join, that can be removed to make it simpler:

UPDATE wp_icl_translations wpml
LEFT JOIN wp_post2lang gengo ON gengo.post_id = post.ID
LEFT JOIN wp_languages gengoLang ON gengoLang.language_id = gengo.language_id
SET wpml.language_code = gengoLang.code
WHERE wpml.element_type ='post'

This just extracts the language information for each post from the Gengo tables wp_post2lang and wp_languages and puts this information in the WPML table wp_icl_translations.

For connecting the posts that are translations of each other I came up with the following SQL statement, thanks to my ex-colleage Lennart Herlaar:

CREATE TEMPORARY TABLE wpml2 AS
     SELECT * FROM wp_icl_translations WHERE element_type = 'post';

UPDATE wp_icl_translations AS wpml
SET trid = (
    SELECT MIN(wpml2.trid)
    FROM wpml2, wp_post2lang AS gengo, wp_post2lang AS gengo2
    WHERE wpml.element_id = gengo.post_id
    AND gengo.translation_group = gengo2.translation_group
    AND wpml2.element_id = gengo2.post_id
   )
WHERE element_type = 'post'
   AND (SELECT translation_group
            FROM wp_post2lang AS gengo
            WHERE gengo.post_id = element_id) > 0;

This takes the translation information from Gengo’s translation_groups (a number) and gives all posts that have the same translation group in Gengo, also the same translation group in WPML (here it is the trid attribute). From each group it just selects one of the existing trids in the group (in this case the smallest but that’s not important) and gives that trid to all members of the group. As MySQL doesn’t allow to use the table that is to be updated in the nested query I had to make a temporary copy first.

OK, now I have the basic translation structure set up. Next to come is the translation of categories and tags. There is a difference between the structure that Gengo uses and what WPML has. In WPML each translated category or tag is a separate one, and the translation just ties them together just like posts and pages. In Gengo the translated categories and tags are just different names for the same category. So next I am going to investigate how to do this conversion automatically.

Read more in part 2.