Mise à jour massive multi-tables MySQL utilisant une procédure stockée

Ce billet est un pur mémo, un quasi copier-coller. Et je n’ai pas trouver un autre titre, aussi générique et banal soit-il.

Pour l’un de mes projets principaux (un système de gestion de pièces de rechange/entrepôt), un gros changement m’a été demandé. Celui-ci, parmi d’autres choses, implique une modification du format du code barre des biens dans la base de données, et ce champ est une clé primaire, donc point délicat.

Le code barre était auto-généré en utilisant les premiers 6 caractères du code fournisseur – si existant – complétés par des zéros, puis un entier incrémenté, toujours complété par des zéros. Il existe donc différentes « typographies » de codes barres, avec et sans caractères alphabétiques : 0000002598 et DMP0000666 peuvent exister.
Maintenant ce code barre est plus simple, il s’agit juste d’un entier incrémenté.

Continue reading « Mise à jour massive multi-tables MySQL utilisant une procédure stockée »

Utiliser Zend_Db_Profiler pour capturer une requête SQL

Il est parfois bien utile de savoir précisément la requête SQL exécutée lorsque l’on utilise Zend_Db_Adapter. Le profiler est alors d’une grande aide. Comme exemple, considérons cet update :

$data = array(
	'option_value' => 'Ma valeur'
);
$db->update('ma_table', $data, 'option_name = "un_nom_bien_brave"');

Pour avoir une visualisation de la requête brute effectuée:

// Activation du profiler
$db->getProfiler()->setEnabled(true);

$data = array(
	'option_value' => 'Ma valeur'
);
$db->update('ma_table', $data, 'option_name = "un_nom_bien_brave"');

// Affichage de la requête
var_dump($db->getProfiler()->getLastQueryProfile()->getQuery());
var_dump($db->getProfiler()->getLastQueryProfile()->getQueryParams());

// Il est bon de désactiver le profiler lorsque l'on a terminé
$db->getProfiler()->setEnabled(false);

Ce qui renvoit :

string 'UPDATE `ma_table` SET `option_value` = ? WHERE (option_name = "un_nom_bien_brave")' (length=79)
array
  1 => string 'Ma valeur' (length=9)

L’objet Zend_Db_Select

L’objet Zend_Db_Select du framework Zend (ZF1) sert à représenter une requête SQL de type SELECT incluant des méthodes orientées objets permettant de construire une requête morceau par morceau. Ce court billet détaille un peu la construction d’une requête incluant sous-requête et jointure.

La requête SQL

Voici le SQL que nous voulons représenter à la façon Zend_Db_Select. Pour avoir une idée du schéma, on dispose d’une base de données représentant une gestion de biens, certains biens pouvant être groupés (par exemple, on choisit de grouper des biens individuels CPU, RAM et carte mère dans un bien ordinateur). Cette requête rapatrie ces groupes. On sélectionne les biens de type group d’une table goods (ayant un champ group égal à GRP), le détail des articles sont extraits de la table articles (JOIN) et on compte le nombre d’articles dans le groupe :

SELECT
	aa.*,
	(SELECT COUNT(barcode) FROM goods WHERE group = aa.barcode) AS items
	FROM (
		SELECT a.description, g.* FROM goods AS g
		JOIN articles AS a ON a.code = g.code
			WHERE g.group = "GRP"
	) aa

Rien de bien compliqué SQLement parlant, mais la traduction en utilisant Zend_Db_Select peut être un peu délicate, au début.

Objets Select

L’opération consiste à découper toutes chaque sous-requête et de les assembler à la fin. Voici le code :

// Première requête avec JOIN
$s1 = $db->select()
		->from(
			array('g' => 'goods'),
			array(
				'barcode',
				'quantity',
				'price'
			)
		)
		->join(
			array('a' => 'articles'),
			'a.code = g.code'
		)
		->where('g.group = ?', 'GRP');

// Requête contenant le COUNT
$s2 = $db->select()
		->from(
			'goods',
			array('COUNT(barcode)')
		)
		->where('group = ?', new Zend_Db_Expr('aa.barcode'));

// On assemble le tout
$select = $db->select()
		->from(
			array('aa' => new Zend_Db_Expr('(' . $s1 . ')')),
			array(
				'aa.*',
				'items' => new Zend_Db_Expr('(' . $s2 . ')')
			)
		);

$resultSet = $db->fetchAll($select);

On voit que $s1 représente le SELECT qui liste les groupes, contenu dans le SELECT principal. $2 est celui qui compte le nombre d’article(s) dans un groupe. Et enfin on assemble tout cela dans le $select final que l’on peut exécuter.
A noter l’utilisation de Zend_Db_Expr pour injecter une simple expression.

Conclusion

Manipuler des requêtes un peu complexes avec ZF peut faire peur de prime abord et on peut vouloir se rabattre sur des requête brutes (en utilisant directement Zend_Db_Statement par exemple avec query()), mais lorsque l’on met un peu les mains dedans, c’est un outil tout à fait avancé et puissant qui permet de faire beaucoup de choses tout en restant à manipuler des objets.

Zend : Utiliser UNION dans une requête

Petit mémo directement prit de la documentation officielle du framework Zend pour combiner les résultats de deux requêtes en utilisant UNION avec Zend_Db_Select :

$sql1 = $db->select();
$sql2 = "SELECT ...";

$select = $db->select()
	->union(array($sql1, $sql2))
	->order("id");

Pour un UNION ALL, il suffit de passer la constante Zend_Db_Select::SQL_UNION_ALL en second paramètre :

$select = $db->select()
	->union(array($sql1, $sql2), Zend_Db_Select::SQL_UNION_ALL)
	->order("id");

Et en passant, pour aider à débugguer un peu les requêtes Zend_Db_Select, il est possible d’utiliser la méthode __toString() pour afficher la requête exécutée. En exemple, il suffit d’ajouter ceci en fin du code ci-dessus :

echo $select->__toString();