Bind French posts translations to French categories.sql

-- Get the list of French posts translations
SELECT group_concat(p.id) 
FROM wp_posts p 
INNER JOIN wp_icl_translations tr ON tr.element_id=p.id 
WHERE tr.element_type='post_post' 
AND tr.language_code='fr' 
GROUP BY element_type;

-- this produces this kind of output:
-- 9,13,14,22,24,34,35,42,47,52

-- Bind french translations to french categories
UPDATE wp_term_relationships tr
INNER JOIN wp_term_taxonomy tax ON tax.term_taxonomy_id=tr.term_taxonomy_id
INNER JOIN wp_terms t ON t.term_id = tax.term_id
INNER JOIN wp_icl_translations tra ON tra.element_id = tax.term_taxonomy_id
INNER JOIN wp_icl_translations tra2 ON tra.trid = tra2.trid AND tra2.language_code = 'fr'
INNER JOIN wp_term_taxonomy tax2 ON tax2.term_taxonomy_id=tra2.element_id
INNER JOIN wp_terms t2 ON t2.term_id = tax2.term_id
SET tr.term_taxonomy_id=tax2.term_taxonomy_id, tax2.count=tax2.count+1
WHERE tra.element_type='tax_category' 
AND tax.taxonomy = 'category' 
AND tr.object_id in (9,13,14,22,24,34,35,42,47,52);

Convert Pages from Gengo to WPML.sql

-- add page translations
UPDATE wp_icl_translations wpml
LEFT JOIN wp_post2lang gengo ON gengo.post_id = wpml.element_id
LEFT JOIN wp_languages gengoLang ON gengoLang.language_id = gengo.language_id
SET wpml.language_code = gengoLang.code
WHERE wpml.element_type ='post_page';

-- link translation to the primary page
CREATE TEMPORARY TABLE wpml3 SELECT * FROM wp_icl_translations WHERE element_type = 'post_page';

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

Convert Posts from Gengo to WPML.sql

-- update translations of posts
UPDATE wp_icl_translations wpml
LEFT JOIN wp_post2lang gengo ON gengo.post_id = wpml.element_id
LEFT JOIN wp_languages gengoLang ON gengoLang.language_id = gengo.language_id
SET wpml.language_code = gengoLang.code
WHERE wpml.element_type ='post_post';

-- Link translations to their primary posts
CREATE TEMPORARY TABLE wpml2 SELECT * FROM wp_icl_translations WHERE element_type = 'post_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_post'
   AND (SELECT translation_group
            FROM wp_post2lang AS gengo
            WHERE gengo.post_id = element_id) > 0;

Convert some categories.sql

-- make sure we use utf8
set names 'utf8';

-- let's insert French categories

-- let's insert the 'Wordpress' category which is a translation of the 'Wordpress' english one:
call transcat("Wordpress @fr", "wordpress-fr", "fr", "Wordpress", NULL);

-- let's insert a translation of a category
call transcat("Programmation", "programmation", "fr", "Programming", NULL);

-- Get the id of this translation to translate child categories
SELECT @progid:=term_id FROM wp_terms WHERE name='Programmation';

-- Insert a child translation
call transcat("Tests Unitaires", "test-unitaires", "fr", "Unit Testing", @progid);

SQL Procedure to create a translated category.sql

-- note: you might have to increase MySQL thread stack
-- to allow this procedure to run

delimiter //
CREATE PROCEDURE transcat(cat VARCHAR(255), slug VARCHAR(255), lang CHAR(2), orig VARCHAR(255), parentid INT)
BEGIN

  -- insert a new term
  INSERT INTO `wp_terms` (`name`,`slug`,`term_group`) VALUES (cat,slug,'0');
  SELECT @lid:=LAST_INSERT_ID();

  -- insert a new taxonomy
  IF parentid THEN
    INSERT INTO `wp_term_taxonomy` (`term_id`,`taxonomy`,`description`,`parent`,`count`) VALUES (@lid,'category','',parentid,'0');
  ELSE
    INSERT INTO `wp_term_taxonomy` (`term_id`,`taxonomy`,`description`,`parent`,`count`) VALUES (@lid,'category','','0','0');
  END IF;

  SELECT @taxid:=LAST_INSERT_ID();

  -- find the category in the primary language (orig)
  SELECT @groupid:=trid 
  FROM wp_icl_translations tr 
  INNER JOIN wp_term_taxonomy tax ON tax.term_taxonomy_id=tr.element_id 
  INNER JOIN wp_terms t ON t.term_id = tax.term_id 
  WHERE tr.element_type='tax_category' and t.name=orig;

  -- finally insert the translation
  INSERT INTO 
  `wp_icl_translations`(`trid`,`element_type`,`element_id`,`language_code`,`source_language_code`)
   VALUES (@groupid,'tax_category',@taxid,lang,'en');

END
//
-- back to normal delimiter
delimiter ;