Archives mensuelles : juin 2013

MySQL – update et delete avec des jointures

Le but de ce billet est de réaliser des opérations de mise à jour ou de suppression à l’aide d’une jointure, externe ou interne.
Voici le schéma qui nous sert de base de travail :

  • une table client
  • une table produit
  • une table commande, avec deux contraintes de clé étrangère partant vers chacune des tables

Hoan_Bridge

Le SQL du schéma

create table client(
id tinyint unsigned primary key auto_increment,
nom varchar(30) not null,
prenom varchar(20) not null);

insert into client (nom, prenom) values 
('Ferrandez', 'Sébastien'), ('Jambon', 'Paul');

create table produit(
id tinyint unsigned primary key auto_increment,
designation varchar(30) not null,
prix float (6,2) unsigned not null);

insert into produit (designation, prix) values 
('Mousse à raser', 7.99), ('Stylo BIC', 4.99);

create table commande (
id tinyint unsigned primary key auto_increment, 
id_client tinyint unsigned , 
id_produit tinyint unsigned, 
quantite tinyint unsigned default 1,
date timestamp);

alter table commande add constraint foreign key (id_client) 
references client(id);

alter table commande add constraint foreign key (id_produit)
references produit(id);

insert into commande(id_client,id_produit, quantite )
values (1,1,2), (1,2,1), (2,2,1), (2,1,2);

Les jointures, externes et internes

Le principe de la jointure est trivial, on crée comme une sorte de « pont » entre au moins 2 tables selon une condition qu’on va très simplement appeler « condition de jointure ». Une condition de jointure n’est pas forcément une clause d’égalité !

Prenons l’exemple de deux tables sans rapport quelconque :

create table eleve (nom varchar(20), age tinyint unsigned);
insert into eleve values ('Ferrandez', 10), ('Lucas', 30);
create table produit (nom varchar(20), prix float(6,2) unsigned);
insert into produit values ('iPhone5', 499), ('Jambon', 10);

Qu’est-ce qui nous empêche de faire une jointure entre un élève et un produit basé sur l’égalité de valeur de l’âge de l’élève et du prix du produit ?

SELECT * 
FROM  eleve 
INNER JOIN produit ON ( eleve.age = produit.prix ) 

Nous obtenons

+-----------+------+--------+-------+
| nom       | age  | nom    | prix  |
+-----------+------+--------+-------+
| Ferrandez |   10 | Jambon | 10.00 |
+-----------+------+--------+-------+
1 row in set (0.00 sec)

Une jointure ne se fait pas uniquement sur la base d’une égalité de valeur, on peut utiliser d’autres opérateurs arithmétiques :

SELECT * 
FROM  eleve
INNER JOIN produit ON ( eleve.age <= produit.prix ) 

Celle-ci remontera les tuples suivants :

+-----------+------+---------+--------+
| nom       | age  | nom     | prix   |
+-----------+------+---------+--------+
| Ferrandez |   10 | iPhone5 | 499.00 |
| Lucas     |   30 | iPhone5 | 499.00 |
| Ferrandez |   10 | Jambon  |  10.00 |
+-----------+------+---------+--------+
3 rows in set (0.00 sec)   

Et pire, qu’est-ce qui nous empêche de faire une jointure sur des champs qui n’ont pas le même type de données (horreur !) :

SELECT * 
FROM  eleve
INNER JOIN produit ON ( eleve.age <= produit.nom ) 

Si on a le droit de faire ça, c’est que l’optimiseur MySQL effectue des conversions de type pour effectuer des comparaisons (différence, égalité etc.). Mais recentrons-nous sur le sujet du billet ! Une requête de jointure externe, contrairement aux internes, retourne à la fois les correspondances (c’est à dire, les enregistrements satisfaisant la clause de jointure) et les non-correspondances (celles qui ne la satisfont pas). Voyons ce que donne :

SELECT * 
FROM  eleve 
LEFT JOIN produit ON ( eleve.age = produit.prix ) 
+-----------+------+--------+-------+
| nom       | age  | nom    | prix  |
+-----------+------+--------+-------+
| Ferrandez |   10 | Jambon | 10.00 |
| Lucas     |   30 | NULL   |  NULL |
+-----------+------+--------+-------+
2 rows in set (0.00 sec)

Les non-correspondances sont signalées par la présence du marqueur NULL. Si vous choisissez de les remonter, rien de plus facile :

SELECT eleve.*
FROM  eleve 
LEFT JOIN produit ON ( eleve.age = produit.prix ) 
WHERE produit.prix IS NULL

Notez bien que le même test, mais sur produit.nom IS NULL aurait également fonctionné !
Bref, nous savons maintenant ce qu’est une jointure interne (que ce qui correspond) et une jointure externe (ce qui correspond + ce qui ne correspond pas).

La mise à jour

Nous allons mettre à jour les commandes du client Sébastien Ferrandez en disant qu’elles ont eu lieu le 24 Décembre 2013 à 23:59:59 :

UPDATE commande INNER JOIN client
ON (commande.id_client = client.id)
SET commande.date = '2013-12-24 23:59:59'
WHERE client. nom = 'ferrandez'
AND client.prenom = 'sebastien'

Notez que nous sommes en collation latin1_general_ci et que nous ne tenons donc compte ni des accents ni des majuscules…
Si d’aventure vous souhaitez modifier d’autres champs (y compris dans la table client, pas que commande !), séparez les dans SET avec des virgules !

Vous pouvez très bien utiliser la jointure externe pour mettre a jour des enregistrements ne se trouvant pas dans la table commande (nous aurions par exemple dans la table client un champ booléen A_DEJA_COMMANDE que nous mettrions à 0 si aucune commande n’était trouvée pour celui-ci).

La suppression

Insérons un nouveau tuple dans client :

insert into client (nom, prenom) values ('Diego', 'Miguel');

Et effectuons sa suppression immédiate en disant « J’efface les clients qui n’ont pas passé de commande »

DELETE client.* FROM client
LEFT OUTER JOIN commande 
ON ( client.id = commande.id_client )
WHERE commande.id_client IS NULL

N’oubliez pas que vous n’êtes absolument pas limités à UNE jointure et que vous pouvez très bien alterner jointures externes et internes dans la même requête ! Entraînez-vous sur des petits schémas tout simples du même genre que celui que nous avons vu dans ce billet et tout ça n’aura bientôt plus de secret pour vous !

PHP : classes abstraites et interfaces

Les interfaces, comme les classes abstraites, sont des unités de code dont la raison d’être est de contraindre une classe à implémenter des méthodes, c’est à dire à les concrétiser ! Nous allons voir en détail ces différentes manières de contraindre les comportements de nos classes.
connect

La classe abstraite

Une classe abstraite est avant tout une classe. Rien ne l’oblige à posséder des méthodes abstraites ! Elle peut même être vide :

abstract class EtreVivant {
}

Par contre, dès lors que vous mettez une méthode abstraite dans une classe, vous devez déclarer votre classe comme étant abstraite !

abstract class EtreVivant {
    abstract public function respirer();
}

Seules les fonctions membres de votre classe abstraite peuvent être abstraites, jamais les propriétés ! Une classe abstraite ne s’instancie pas ! Si d’aventure vous tentez d’instancier EtreVivant, voici ce qui va vous arriver :

PHP Fatal error:  Cannot instantiate abstract class EtreVivant

Si ma classe abstraite ne peut pas s’instancier, c’est donc que pour l’utiliser il ne me reste plus qu’une option : la dériver !

abstract class EtreVivant {
    abstract public function respirer();
}

class EtreHumain extends EtreVivant {
}

En l’état actuel de notre hiérarchie d’héritage, une erreur va se produire car EtreVivant nous impose d’écrire respirer(), même vide, ce que nous ne faisons pas !

PHP Fatal error:  Class EtreHumain contains 1 abstract method and must therefore be declared abstract or implement the remaining methods (EtreVivant::respirer)
abstract class EtreVivant {
    abstract public function respirer();
}

class EtreHumain extends EtreVivant {
    public function respirer() {
        //TODO...
    }
}

Une classe abstraite peut forcer les classes qui la dérivent à implémenter des fonctions aux modes d’accès autre que public…protégé par exemple :

abstract class EtreVivant {
    abstract protected function _respirer();
}

class EtreHumain extends EtreVivant {
    protected function _respirer() {
        //TODO...
    }
}

Evidemment, aucun intérêt à mettre une méthode privée ET abstraite dans la classe abstraite…Une méthode privée n’est pas transmise dans les classes filles, elle ne peut être appelée que dans cette classe même, ce serait un grave contre-sens ! Si vous tentiez de le faire, vous seriez bien déçu(e)s :

PHP Fatal error:  Abstract function EtreVivant::_respirer() cannot be declared private

Nous avons dit précédemment qu’une classe abstraite ne pouvait pas être instanciée. Mais cependant, rien ne l’empêche d’avoir un constructeur ! C’est évidemment la classe concrète qui la dérive qui pourra l’utiliser :

abstract class Maman {
    public function __construct() {
        echo "Vous êtes chez Maman";
    }
}

class Fiston extends Maman {}
$fiston = new Fiston;

Quel intérêt ?

Vous utilisez des classes abstraites dès lors qu’un comportement est susceptible de varier selon la classe concrète dans laquelle il se trouve…Regardez notre classe EtreVivant : elle est générique, c’est ce que l’on nomme un super-type ! Sa méthode respirer() est elle aussi bien trop « vague », les êtres vivants respirent de bien des façons différentes ! Nous laissons donc le soin aux classes qui vont nous dériver de donner un corps à cette méthode, pire que ça, même: nous l’imposons !

abstract class EtreVivant {
    abstract public function respirer();
}

class EtreHumain extends EtreVivant {
    public function respirer() {
        echo 'Par le nez et/ou la bouche';
    }
}

class Plante extends EtreVivant {
    public function respirer() {
        echo 'Par la photo-synthèse';
    }
}

Il existe bel et bien une notion de contrainte entre une classe abstraite contenant des méthodes abstraites et sa descendance :

Si tu choisis d’être un EtreVivant, tu dois savoir respirer, peu m’importe comment !

Une classe abstraite peut constituer un début d’implémentation : elle possède des propriétés (un état) factorisables à son niveau et des méthodes (un comportement) qui amorcent une implémentation. Par exemple, nous avons rajouté dans la classe abstraite une méthode mourir qui fait appel à une sous-méthode _cesserFonctionsVitales qui sera implémentée dans les classes dérivées :

abstract class EtreVivant {
    protected $_age = 0;
	protected $_poids = 0;

	public function mourir() {
		$this->_cesserFonctionsVitales();
	}

    abstract public function respirer();
    abstract protected function _cesserFonctionsVitales();
}

class EtreHumain extends EtreVivant {
    protected $_nationalite;

    public function respirer() {
        echo 'Par le nez et/ou la bouche';
    }

    protected function _cesserFonctionsVitales() {
        echo 'le coeur s\'arrête de battre...AAAARGH';
    }
}

class HumainFrancais extends EtreHumain {
    public function __construct() {
        $this->_nationalite = 'français';
    }
}

$francais = new HumainFrancais;
$francais->mourir();

Une classe abstraite dérivant une autre classe abstraite n’est pas forcée d’implémenter les méthodes abstraites de sa mère, vu qu’elle est elle-même abstraite…Par contre la première classe concrète rencontrée dans l’arbre d’héritage en supportera les conséquences ! Ceci va fonctionner car les deux classes sont abstraites (attention aux longues lignées d’héritage et aux trop nombreux niveaux d’abstractions – à partir de 3 – qui sont en général le signe d’une conception de piètre qualité) :

abstract class EtreVivant {
    protected $_age = 0;
	protected $_poids = 0;

	public function mourir() {
		$this->_cesserFonctionsVitales();
	}

    abstract public function respirer();
    abstract protected function _cesserFonctionsVitales();
}

abstract class EtreHumain extends EtreVivant {
}

si vous déplacez la responsabilité de l’implémentation dans la première classe rencontrée dans l’arbre d’héritage, vous obtiendrez le code suivant :

abstract class EtreVivant {
    protected $_age = 0;
	protected $_poids = 0;

	public function mourir() {
		$this->_cesserFonctionsVitales();
	}

    abstract public function respirer();
    abstract protected function _cesserFonctionsVitales();
}

abstract class EtreHumain extends EtreVivant {
    protected $_nationalite;

}

class HumainFrancais extends EtreHumain {

    public function respirer() {
        echo 'Par le nez et/ou la bouche';
    }

    protected function _cesserFonctionsVitales() {
        echo 'le coeur s\'arrête de battre...AAAARGH';
    }

    public function __construct() {
        $this->_nationalite = 'français';
    }
}

$francais = new HumainFrancais;
$francais->mourir();

Mais quelle abomination serait un code pareil ! Tout est fourré à grands coups de pied dans la classe concrète HumainFrancais…Si demain nous devions créer HumainCroate, nous devrions dupliquer bêtement (pléonasme) le code contenu dans cette classe.

L’interface

Les interfaces servent à passer des contrats avec des classes, elles impliquent la même notion de contrainte que les classes abstraites (« Si tu veux être comme moi, tu dois faire comme moi ! »). Mais l’interface est un mécanisme plus simple : ce n’est pas une classe, donc inutile de l’instancier ou d’en hériter ! Puis, même si elle peut contenir des constantes comme dans une classe abstraite (ou pas), les fonctions qu’elle impose sont TOUJOURS en mode d’accès public. Le mot clé pour utiliser une interface est implements.

interface AnimalNageur {
	public function nager();
}

abstract class EtreVivant {
    protected $_age = 0;
	protected $_poids = 0;

	public function mourir() {
		$this->_cesserFonctionsVitales();
	}

    abstract public function respirer();
    abstract protected function _cesserFonctionsVitales();
}

class EtreHumain extends EtreVivant implements AnimalNageur {
    protected $_nationalite;

    public function respirer() {
        echo 'Par le nez et/ou la bouche';
    }

    protected function _cesserFonctionsVitales() {
        echo 'le coeur s\'arrête de battre...AAAARGH';
    }

	public function nager() {
		echo 'Je peux aussi nager si on m\'apprend';
	}
}

class HumainFrancais extends EtreHumain {
    public function __construct() {
        $this->_nationalite = 'français';
    }
}

$francais = new HumainFrancais;
$francais->nager();
$francais->mourir();

Dans cet exemple, la classe EtreHumain implémente l’interface AnimalNageur, car un être humain peut nager (je ne dis pas qu’il sait nager…). Si elle implémente cette interface alors elle doit honorer le contrat qui la lie à présent avec AnimalNageur : donner « vie » à la méthode publique nager.

Une classe peut implémenter plusieurs interfaces :

interface AnimalNageur {
	public function nager();
}

interface AnimalCoureur {
	public function courir();
}

abstract class EtreVivant {
    protected $_age = 0;
	protected $_poids = 0;

	public function mourir() {
		$this->_cesserFonctionsVitales();
	}

    abstract public function respirer();
    abstract protected function _cesserFonctionsVitales();
}

class EtreHumain extends EtreVivant
                 implements AnimalNageur, AnimalCoureur {
    protected $_nationalite;

    public function respirer() {
        echo 'Par le nez et/ou la bouche';
    }

    protected function _cesserFonctionsVitales() {
        echo 'le coeur s\'arrête de battre...AAAARGH';
    }

	public function nager() {
		echo 'Je peux aussi nager si on m\'apprends';
	}

	public function courir() {
		echo 'Je passe une jambe devant l\'autre, très vite';
	}
}

class HumainFrancais extends EtreHumain {
    public function __construct() {
        $this->_nationalite = 'français';
    }
}

$francais = new HumainFrancais;
$francais->nager();
$francais->courir();
$francais->mourir();

EtreVivant implémente dorénavant AnimalNageur et AnimalCoureur, il implémente donc l’ensemble des fonctions imposées par ces deux interfaces (qui ont une méthode chacune).

Les interfaces et l’héritage

Les interfaces peuvent hériter d’autres interfaces et la grande différence par rapport aux classes c’est qu’elles peuvent hériter « en losange » :

interface A {
    public function a();
}

interface B {
    public function b();
}

interface C extends A, B {
    public function c();
}

class D implements C {
    public function a() {
    }
    public function b() {
    }
    public function c() {
    }
}

L’interface C « dérive » les interfaces A et B, c’est à dire qu’une classe qui implémente l’interface C va devoir écrire a(),b() et c().

Faut-il privilégier les interfaces ou les classes abstraites ?

Une fois de plus, il n’y a pas de méthode toute prête de conception, c’est à vous de voir, mais tâchons tout de même de voir les différences fondamentales :

  • une classe abstraite oblige à la dériver pour bénéficier de ses fonctionnalités: les méthodes « enfermées » dans une classe abstraite forcent l’héritage, ce qui n’est pas nécessairement bon
  • les méthodes « sorties » dans une ou des interfaces favorisent la ré-utilisabilité (si on enferme la méthode courir dans EtreHumain, seuls les êtres humains pourront courir…Si nous écrivons une interface AnimalCoureur avec cette méthode, une autruche pourra l’implémenter et courir à son tour, sans aboutir à l’ineptie qui consisterait pour Autruche à dériver EtreHumain pour pouvoir courir)

Résumé

  • Une classe abstraite peut contenir du code, pas une interface. Vue sous cet angle,
    une interface est 100% abstraite, elle ne contient que des prototypes (ou signatures)
    de fonctions publiques
  • Une interface, comme une classe, peut posséder des constantes
  • Une interface s’implémente, une classe s’instancie ou se dérive
  • Une classe peut implémenter plusieurs interfaces mais ne peut spécialiser qu’une seule classe (en PHP)
  • Une interface peut être implémentée par plusieurs classes
  • Les méthodes d’une interface sont forcément publiques, celles d’une classe abstraite peuvent être de tout type, comme dans une classe normale et uniquement publiques ou protégées si elles sont abstraites

MySQL MAX : la/les ligne(s) contenant la plus grande valeur d’un champ

Si vous lisez régulièrement les billets de ce blog (ce que j’espère secrètement), vous savez que j’essaie dans la mesure du possible de parler de choses qui servent au quotidien à la fois pour les développeurs Web utilisant PHP mais aussi pour les personnes qui travaillent avec des bases de données (relationnelles ou pas). C’est ainsi qu’aujourd’hui nous allons nous focaliser sur un type de requête relativement souvent utilisé et qui a pour but de faire « remonter » la ligne (ou les lignes) contenant la plus grande valeur d’un champ ! C’est parti ! Amusons-nous avec nos joyeux élèves !

Voilà un élève joyeux !

Voilà un élève joyeux (j’aimerais qu’ils soient tous ainsi) !

Notre jeu de données

Soit la table eleves :

mysql> select * from eleves;
+----+-----------+------------+------+
| id | nom       | prenom     | note |
+----+-----------+------------+------+
|  1 | Ferrandez | Sébastien  |   10 |
|  2 | Ferrandez | Christophe |   12 |
|  3 | Gérard    | Olivier    |    9 |
|  4 | Gérard    | olivier    |   15 |
|  5 | Ferrandez | Christophe |   17 |
|  6 | Ferrandez | Sébastien  |    8 |
|  7 | Gérard    | Jean       |    4 |
|  8 | Django    | Marina     |   17 |
+----+-----------+------------+------+

Le but

Je souhaite connaitre la ou les personne(s) qui ont obtenu la note la plus haute.

Les façons de faire

A l’instinct…

Je sais qu’il est question de MAX quelque part, je serais tenté de faire :

SELECT max(note), nom, prenom FROM eleves;

Mais là, deux problèmes se posent…

  • 1 : je n’ai qu’un enregistrement alors que deux élèves ont la meilleure note
  • 2 : en face de cette meilleure note, je n’ai pas le bon couple nom/prénom

La jointure externe

Ici, nous allons nous baser sur la présence d’un marqueur NULL pour remonter les enregistrements qui nous intéressent. Nous disons en substance « Je veux le nom et le prénom des élèves pour lesquels il n’existe pas de note qui soit plus grande ».

SELECT s1.note, s1.nom, s1.prenom
FROM eleves s1
LEFT JOIN eleves s2 ON (s1.note < s2.note)
WHERE s2.id IS NULL

Cette solution là ramène bien les bons résultats :

mysql> SELECT s1.note, s1.nom, s1.prenom
    -> FROM eleves s1
    -> LEFT JOIN eleves s2 ON (s1.note < s2.note)
    -> WHERE s2.id IS NULL;
+------+-----------+------------+
| note | nom       | prenom     |
+------+-----------+------------+
|   17 | Ferrandez | Christophe |
|   17 | Django    | Marina     |
+------+-----------+------------+
2 rows in set (0.00 sec)

Le temps d’exécution est de l’ordre de la milliseconde (inférieur à une dizaine de millisecondes), nous n’avons pas indexé quelque champ que ce soit donc tout semble correct. Mais votre jeu de données grossissant, vous allez vite vous rendre compte que cette solution n’est pas viable ! Sur une table de 100000 enregistrements, une telle requête (avec le champ note indexé !) prend plus de 17 minutes à s’exécuter…Nous serions bons pour le peloton d’exécution avec un tel temps d’exécution !

La sous-requête

Il nous reste la solution de la sous-requête, qui va s’avérer dans notre cas la plus performante :

SELECT  note, nom, prenom
FROM   eleves
WHERE  note=(SELECT MAX(note)
              FROM eleves)

Sur notre set de 100000 enregistrements, avec le champ note indexé, elle met 0.04 secondes à s’exécuter, nous sommes loin des 17 minutes de la jointure externe, qui semblait bien fonctionner sur un petit nombre d’enregistrements. Un EXPLAIN nous montre que

+----+-------------+--------+------+---------------+------+---------+-------+------+------------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref   | rows | Extra                        |
+----+-------------+--------+------+---------------+------+---------+-------+------+------------------------------+
|  1 | PRIMARY     | eleves | ref  | note          | note | 1       | const |    2 | Using where                  |
|  2 | SUBQUERY    | NULL   | NULL | NULL          | NULL | NULL    | NULL  | NULL | Select tables optimized away |
+----+-------------+--------+------+---------------+------+---------+-------+------+------------------------------+
2 rows in set (0.00 sec)

« Select tables optimized away » signifie que notre sous-requête ne contient qu’une fonction agrégative (sans groupement) et que l’optimiseur MySQL a déterminé qu’il ne renverra qu’une seule et unique valeur.

En conclusion, nous voyons que la sous-requête est la plus adaptée à notre cas ici. Pourquoi ne pas tenter un petit GROUP_CONCAT ordonné, également ? La requête reste performante et les résultats sont présentés de manière « agréable »…

mysql> SELECT note, GROUP_CONCAT( nom,  ' ', prenom
    -> ORDER BY nom ) AS  "Eleves"
    -> FROM eleves
    -> WHERE note = ( 
    -> SELECT MAX( note ) 
    -> FROM eleves );
+------+------------------------------------+
| note | Eleves                             |
+------+------------------------------------+
|   17 | Django Marina,Ferrandez Christophe |
+------+------------------------------------+

N’écoutez pas les oiseaux de malheur qui vitupèrent constamment les sous-requêtes, souvent ils n’en n’ont jamais fait une seule de leur vie et propagent des racontars qu’ils lisent ça et là sur des forums généralistes où prolifèrent leurs congénères aux avis biaisés ou erronés…Dans certains cas, ces sous-requêtes se révéleront bien plus efficaces que des jointures internes ou externes hasardeuses…Votre seule jauge quand vous écrivez une requête c’est EXPLAIN et pas le collègue ignare qui propage des légendes urbaines informatiques !