DELETE avec contre-verification

Version imprimable

20/08/2009, 17h02
sabotage

DELETE avec contre-verification
Bonjour,

J'ai une table de liaison entre des sociétés et des utilisateur structurée ainsi :

t_compte
---
id, societe, utilisateur, status

status indique si l'utilisateur est "propriétaire" (1) ou "délégué" (0).
seul le "propriétaire" d'une société peut supprimer des utilisateurs "délégué" de cette société.

L'interface HTML/PHP transmet uniquement l'id à supprimer et l'utilisateur demandant la suppression.
L'idée est donc de vérifier que l'utilisateur supprimant est bien "proprietaire" de la societé.

J'ai écris une requete de suppression de "delegué" ainsi avec une sous-requete :
Code:

1 2 3 4 5 6 7 8 DELETE FROM t_compte WHERE id=12345 AND societe IN ( SELECT societe FROM t_compte WHERE status = 1 AND utilisateur=toto )
Est-ce que cela vous parrait bon ou completement nul ?

Y a t'il une meilleur maniere de l'écrire en SQL ?

L'alternative que j'avais étant de découper et utiliser le langage de programmation :
1 - trouver la société correspondant à l'id
2 - vérifier si l'utilisateur est "propriétaire" de cette société
3 - si oui faire la suppression.

Merci de votre aide.
20/08/2009, 22h47
ced

Salut ;),

Est-ce que tu as testé la requête (dans une transaction, pour pouvoir revenir en arrière en cas de pépin, si tu es en InnoDB, bien sûr).
Parce que je crois me souvenir d'un vieux bug MySQL toujours pas résolu qui empêche le DELETE (et l'UPDATE) avec une sous-requête sur la table dans laquelle on fait la mise à jour. Ca va générer un message d'erreur...

Ensuite, il y a certaines choses que je ne comprends pas dans la requête. Est-ce que le champ id est la clé primaire de ta table ?
Si oui, alors la sous-requête ne sert à rien, vu que tu limites la suppression à un seul id... :?

ced

Autre idée, autre code :)

Code:

1
2
3
4
5
6
7
 
SELECT IF(
  SELECT COUNT(societe) 
    FROM t_compte 
    WHERE STATUS = 1 
      AND utilisateur=toto > 0 ,DELETE FROM t_compte 
WHERE id=12345 ,SELECT NULL )

21/08/2009, 10h30
sabotage
Citation:

Est-ce que tu as testé la requête

:aie: est-ce que j'etais trop pressé de rentrer chez moi ?

Citation:

empêche le DELETE (et l'UPDATE) avec une sous-requête sur la table dans laquelle on fait la mise à jour

:oops: effectivement
La solution pour mysql est d'utiliser une jointure :
Code:

1 2 3 DELETE t1 FROM t_compte t1 JOIN t_compte t2 USING (societe) WHERE t1.id=12345 AND t2.utilisateur='toto' AND t2.status=1
mais je ne sais pas si on doit qualifier ça d'astucieux ou d'ignoble.

Citation:

Est-ce que le champ id est la clé primaire de ta table ?
Si oui, alors la sous-requête ne sert à rien, vu que tu limites la suppression à un seul id...

oui c'est bien la clé primaire.
le but de la sous requete n'est pas de limité les lignes concernés mais de garantir directement par la requete que l'utilisateur a le droit nécessaire pour effectuer la suppression.

Pour la proposition de Maitre Pylos, visiblement mysql ne veut pas d'un DELETE en resultat d'un SELECT IF (la requete fonctionne si je mets un SELECT a la place du DELETE).

Au grand maux les grand moyens.

Avec une store pro, pour éviter de faire la vérif dans PHP

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
DELIMITER $$
 
DROP FUNCTION IF EXISTS `ta_base`.`prop_del_delegue`$$
CREATE DEFINER=`root`@`localhost` FUNCTION `prop_del_delegue`(proprietaire varchar,id_delegue int) RETURNS TINYINT
BEGIN
   DECLARE resultat TINYINT ;
   DECLARE compteur INT;
 
SELECT COUNT(societe) 
	FROM t_compte
	 INTO compteur  
WHERE STATUS = 1 
AND utilisateur = proprietaire;
 
    IF (compteur > 0) THEN
		DELETE FROM t_compte WHERE id = id_delegue
        SET resultat = 1;
    ELSE
        SET resultat = 0;
 
    END IF;
 
    RETURN resultat;
 
END$$
 
DELIMITER ;

31/08/2009, 10h52
sabotage

:ccool:

Merci pour votre aide.

Petite info.

Effectivement MySQL n'accepte pas ce genre de requêtes :
Code:

1 2 3 4 5 6 7 8 DELETE FROM t_compte WHERE id=12345 AND societe IN ( SELECT societe FROM t_compte WHERE STATUS = 1 AND utilisateur=toto )
Impossible d'avoir une sous-requête portant sur la table concernée par le DELETE/UPDATE.
Il y a une astuce qui est d'utiliser une table dérivée :
Code:

1 2 3 4 5 6 7 8 DELETE FROM t_compte WHERE id=12345 AND societe IN ( SELECT societe FROM (SELECT * FROM t_compte) AS td WHERE STATUS = 1 AND utilisateur=toto )