Cloud Connected

Disons que vous soyez en charge d’une application web écrite en PHP/MySQL, par exemple l’intranet de votre entreprise, et que vous ayez soudainement besoin de l’internationaliser parce que votre entreprise installe des bureaux à l’étranger et que, malheureusement, notre merveilleuse langue française n’est pas parlée dans tous les pays du monde.

Pour les templates, pas de problème, gettext est là pour ça (je reviendrais peut-être dessus dans un futur article, si je suis motivé). Si vous n’avez pas envie de mettre les mains dans le système, votre framework propose sûrement une émulation plus ou moins performante en pur PHP, ou, au pire, une solution “maison”. Bref, ça c’est facile.

Ce qui pose plus de problèmes, ce sont les données présentes en base. Par exemple : la liste de catégories pour les tickets d’incidents des clients. Elle est stockée en base dans une table qui contient notamment l’intitulé de cette catégorie. Oui, mais cet intitulé doit être traduit. Et comme les catégories sont gérées dynamiquement, ce n’est pas envisage d’utiliser la méthode gettext qui repose sur des fichiers statiques.

Transformer le modèle

Nous allons commencer par ajouter une table pour stocker les traductions. Si on envisage la catégorie comme un élément identifié par un id uniquement, les traductions sont liées à une catégorie par une relation “1-N” : 1 catégorie possède N traductions. Chaque traduction est identifiée par un code de langue. L’idée est de retirer les champs contenant du texte à traduire de la catégorie, et de les stocker dans la table contenant les traductions

Schéma de la base

On crée donc les tables suivantes :

CREATE TABLE `category` (
	`id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
	`created_at` DATETIME NOT NULL,
	`deleted_at` DATETIME NULL,
	PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CREATE TABLE `category_i18n` (
	`category_id` INT UNSIGNED NOT NULL,
	`lang` VARCHAR(6) NOT NULL,
	`name` VARCHAR(45) NOT NULL,
	PRIMARY KEY (`lang`, `category_id`),
	INDEX `category_i18n_category_fk` (`category_id` ASC),
	CONSTRAINT `category_i18n_category_fk`
		FOREIGN KEY (`category_id` )
		REFERENCES `category` (`id` )
		ON DELETE CASCADE
) ENGINE = InnoDB;

Insertion

Lors de l’insertion, il faut d’abord insérer dans category, récupérer l’id puis insérer dans category_i18n. Puisqu’on travaille avec plusieurs tables en même temps, l’utilisation d’une transaction (avec le moteur de stockage InnoDB) est fortement recommandée.

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO category (created_at) VALUES (SYSDATE());
Query OK, 1 row affected (0.00 sec)

mysql> SET @category_id = LAST_INSERT_ID();
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO category_i18n (category_id, lang, name) VALUES
    -> (@category_id, 'fr', 'Incident réseau'),
    -> (@category_id, 'en', 'The network is fucked up');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

Et ça donne :

mysql> SELECT * FROM category;
+----+---------------------+------------+
| id | created_at          | deleted_at |
+----+---------------------+------------+
|  3 | 2009-03-06 15:58:44 | NULL       |
+----+---------------------+------------+
1 row in set (0.00 sec)

mysql> SELECT * FROM category_i18n;
+-------------+------+--------------------------+
| category_id | lang | name                     |
+-------------+------+--------------------------+
|           3 | en   | The network is fucked up |
|           3 | fr   | Incident réseau          |
+-------------+------+--------------------------+
2 rows in set (0.00 sec)

Exemple d’implémentation en utilisant PDO

// $names contient un tableau associatif "langue => valeur"
function newCategory($dbh, $names = array())
{
	if ( empty($names) )
		return false;

	try {
		$dbh->beginTransaction();

		$sql =
			'INSERT INTO category
			(created_at)
			VALUES (SYSDATE())';

		$dbh->exec($sql);
		$id = $dbh->lastInsertId();

		$sql = array();
		foreach ( $names as $lang => $name ) {
			$sql[] = sprintf(
				'(%d, %s, %s)',
				$id,
				$dbh->quote($lang),
				$dbh->quote($name)
			);
		}
		$sql =
			'INSERT INTO category_i18n
			(category_id, lang, name)
			VALUES '.implode(', ',$sql);
		$dbh->exec($sql);

		$dbh->commit();
	} catch (Exception $e) {
		$dbh->rollback();
		throw $e;
	}
}

// appel de la fonction
$dbh = new PDO('mysql:host=localhost;dbname=test', 'test', 'test');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

newCategory($dbh, array(
	'fr' => 'Incident réseau',
	'en' => 'The network is fucked up'
));
newCategory($dbh, array(
	'fr' => 'Incident système',
	'en' => 'The system screwed up'
));
newCategory($dbh, array(
	'fr' => 'Commande de matériel',
	'en' => 'I need some stuffs'
));
newCategory($dbh, array(
	'fr' => 'Problème typiquement français'
));

Sélection

Pour récupérer la liste des catégories, il faut savoir dans quelle langue on les veut. Pour cela, on peut se baser sur les préférences indiquée par le navigateur de l’utilisateur, ou sur ses options s’il est inscrit, etc. La requête de sélection se fait ensuite par une simple jointure.

Dans l’exemple suivant, on sélectionne d’abord les catégories en français et ensuite les catégories en anglais. La catégorie non-traduite n’apparaît pas. On pourrait utiliser un LEFT JOIN pour avoir un libellé NULL (ça peut servir dans certains cas).

mysql> SELECT c.id, ci.name
    -> FROM category c
    -> JOIN category_i18n ci ON (ci.category_id = c.id AND ci.lang = "fr")
    -> WHERE c.deleted_at IS NULL;
+----+-------------------------------+
| id | name                          |
+----+-------------------------------+
|  1 | Incident réseau               |
|  2 | Incident système              |
|  3 | Commande de matériel          |
|  4 | Problème typiquement français |
+----+-------------------------------+
4 rows in set (0.00 sec)

mysql> SELECT c.id, ci.name
    -> FROM category c
    -> JOIN category_i18n ci ON (ci.category_id = c.id AND ci.lang = "en")
    -> WHERE c.deleted_at IS NULL;
+----+--------------------------+
| id | name                     |
+----+--------------------------+
|  1 | The network is fucked up |
|  2 | The system screwed up    |
|  3 | I need some stuffs       |
+----+--------------------------+
3 rows in set (0.00 sec)

mysql> SELECT c.id, ci.name
    -> FROM category c
    -> LEFT JOIN category_i18n ci ON (ci.category_id = c.id AND ci.lang = "en")
    -> WHERE c.deleted_at IS NULL;
+----+--------------------------+
| id | name                     |
+----+--------------------------+
|  1 | The network is fucked up |
|  2 | The system screwed up    |
|  3 | I need some stuffs       |
|  4 | NULL                     |
+----+--------------------------+
4 rows in set (0.00 sec)

Modification

La modification peut être triviale, ou un petit peu complexe, selon le type d’interface utilisateur mise en place. Quelques astuces : si un simple UPDATE ne suffit pas, vous devriez regarder du côté de INSERT ... ON DUPLICATE KEY UPDATE ..., ou implémenter une suppression complète immédiatement suivi de nouvelles insertions (toujours avec une transaction).

Suppression

Grâce à la clause ON DELETE CASCADE il suffit de supprimer l’entrée dans catégorie pour que les traductions correspondantes soient également supprimées. Personnellement j’utilise le champ deleted_at pour effectuer une suppression logique et ne pas casser toutes les clés étrangères des tables qui font référence à la catégorie supprimée.