MySql Procédures et Fonctions.
#1
Bonjour à tous.

J'apprends depuis peut à me servir des fonctions "avancées" de MySQL et je me heurte à un petit problème. J'ai une solution qui se profile, mais elle me parait un peu "sale", je m'explique :

À la base, je voulais faire quelque chose dans ce genre-là :

SELECT (CALL calcul_passager(trajet.id)) FROM trajet WHERE ...

Et la procédure calcul_passager procédait à tout les calculs nécessaires et remplissait les tables concernées.
Le but de se code étant donc d'exécuter la procédure calcul_passager pour chaque ligne de la table trajet sélectionné.

Mais MySQL en à voulu autrement et me signifie très rudement que ma façon de procéder n'est pas convenable. 3
Il semblerai qu'il ne soit pas possible d'appeler directement une procédure dans un SELECT.

J'ai donc demandé à Google s'il n'y avait pas une autre manière de procéder, mais c'est en faisant des essais de code sur phpmyadmin que j'ai découvert la possibilité de créer des fonctions SQL à peu près comme des procédures.
Mais, je trouve peut de documentation à ce propos sur le web, et même si les premiers tests avec ma procédure transformée en fonction sont concluant, j'ai peur de faire un bidouillage immonde qui me serait préjudiciable pour le reste du développement de mon jeu.

Je suis donc ici pour vous demander votre avis sur cette solution ou si vous avez une manière plus "évidente" de régler mon problème originel.

Merci et bonne journée.
Répondre
#2
Salut,

ce n'est pas l'usage d'une procédure. Une procédure effectue un traitement, et retournera 0, 1 ou plusieurs "resultsets". Il ne fait donc pas sens de mettre la procédure dans un SELECT.

Comme tu l'as découvert, il existe d'autres moyens de faire ce que tu souhaites:

1) L'usage d'une fonction
Ce principe est simple à mettre en place, et consiste à créer "un truc" similaire à une procédure, mais qui ne renverra non pas 0, 1 ou plusieurs resultsets mais uniquement 1 seule valeur scalaire. Tu peux alors utiliser cette fonction partout où une valeur scalaire peut apparaitre, à condition de passer à cette fonction les paramètres dont elle a besoin. Tu peux donc faire un SELECT myFunction(1234, t.x, t.y) FROM table AS t, ce qui aura pour effet de prendre toutes les lignes de la table, de calculer myFunction(...) pour chaque ligne, et de retourner la liste de ces résultats (sous la forme d'un resultset). Tu peux également l'utiliser dans un WHERE, façon SELECT * FROM table AS t WHERE myFunction(1234, t.x, t.y) = 2. Attention dans ce cas aux performances, car l'utilisation d'un index ne sera pas possible (sauf en passant par les colonnes virtuelles du dernier MySQL, éventuellement)

2) L'usage d'une vue
C'est très très similaire comme résultat, mais complètement différent comme façon de penser: une vue est simplement une table SQL, qui n'est pas stockée sur le serveur (en tous cas, pas chez MySQL, Oracle a une notion de "vues stockées", identique au système de vue à la seule différence que le SGBD se permet de stocker la vue sur le disque s'il estime que ce sera plus rapide), et qui est donc calculée à la volée, lors de la requête. Elle se présente comme donc comme une table, dont le contenu est le résultat d'un SELECT.... Par exemple, tu peux créer la vue myView correspondant à la requête SELECT (t.x+t.y)/1234 FROM table AS t. Faire un SELECT sur cette vue sera alors comme faire le SELECT correspondant à cette vue (ie: SELECT * FROM myView revient à faire SELECT (t.x+t.y)/1234 FROM table AS t ).

Du coup, les deux cas répondent à deux besoins différents:
• La fonction est pratique si le traitement est complexe, et utilisé dans des contextes totalement différents (par exemple, si le "1234" n'est pas constant, mais dépend d'autre choses). Par exemple, une fonction distance2d(x,y) retournant RETURN SQRT(x*x+y*y) est une bonne utilisation des fonctions, si celle-ci est réutilisée un peu partout dans différentes tables. C'est aussi utilisable (je crois, à vérifier en pratique) s'il y a des sous-requêtes à faire dans ladite fonction (mais là, les perfs, ouch, ça peut faire mal: tu verras à l'usage)
• La vue est pratique si le traitement est simple et n'est pas réutilisé dans d'autres contextes. Par exemple, SELECT *, (c.x*c.x + c.y*c.y) AS dist2d FROM mapcases AS c est une vue parfaitement acceptable. Note que la vue peut elle-même se servir de la fonction (SELECT *, distance2d(c.x, c.y) AS dist2d FROM mapcases).

Dans ton cas, je pense que la vue pourrait être plus adaptée: je doute que tu réutilises ta fonction ailleurs qu'ici, et je pense que tu apprécieras le fait de pouvoir visualiser directement les données de cette vue dans ton IDE (Netbeans, HeidiSQL, MySQL workbench, et même phpMyAdmin permettent de voir les vues comme si elles étaient des tables, ce qui peut être pratique pour comprendre ce qui se passe dans ton modèle de données). Si ta fonction a des sous-requêtes, tente de passer par une vue avec des jointures: tu auras sans doute de bien meilleures perfs.
Note que dans ton cas, peut-être, une colonne virtuelle pourrait aussi être une solution: les colonnes virtuelles ont la possibilité d'être stockées pour ne pas être ré-évaluée constamment, donc, si ta fonction renvoie des résultat constants dans le temps (sauf si un joueur fait une action) et si cette colonne est plus souvent lue "qu'écrite", alors la colonne virtuelle sera une bonne solution (attention à ce que ton hébergeur le prenne bien en charge).
Répondre
#3
Merci de ta réponse.

Dans mon cas, le traitement est complexe, mais n'est pas réutilisé ailleurs, car le morceau de code que j'ai montré au-dessus fait parti du "moteur" du jeu qui sera exécuté périodiquement.
À l’intérieur de calcul_passager se cache effectivement des sous-requête et d'autres fonctions cette fois plus classique (qui était autrefois des procédures).
Pour donner plus de précisions :
Le but de se code est de calculer le nombre de passagers embarquant sur un trajet en fonction des autres trajet similaires (même point de départ, même point d'arriver, plus il y a de trajets similaire moins il y aura de demande) et de la distance parcourue. Elle inscrit en suite sur une autre table event le nombre de passagers transporté et l'heure de fin du trajet. Tout ça pour chaque trajet sélectionné.
Il n'y a aucune valeur à retourné à la fin de ce processus (car exécuté par le serveur).

Dans l'état actuel des choses, je ne vois pas vraiment comme je pourrais utiliser une vue pour réaliser ces calculs, sachant que c'est une version volontairement simple pour que je puisse sortir un prototype dans un délai raisonnable et l'étoffer par la suite.

À la lumière de tes explications donc, voici quelques nouvelles questions :
- Une sous-requête effectuée dans une fonction est-elle plus gourmande que si elle était faite dans une requête principale ?
- Est-il possible d'avoir à des requêtes INSERT dans une fonction ? ->édit: Possible de le sens est-ce rationnel? acceptable?
Répondre
#4
Effectivement, dans ton cas, une vue sera mal adaptée. En revanche, pourquoi "SELECT"-tu ta procédure? Selon ce que tu décris, tu as en fait une procédure de calcul des trajets, et tu n'as qu'à l'appeler? En lui passant éventuellement en paramètre le "filtre des lignes" sur lesquelles elle porte.

Schématiquement:
CREATE PROCEDURE calcul_passagers(IN idPlayer INT UNSIGNED) BEGIN
calculs bla bla ... WHERE (player.id = idPlayer OR idPlayer IS NULL)
END

(mais je ne sais pas si c'est très clair?)

• La fonction va être appelée pour chaque ligne du resultset renvoyé par le SELECT, donc si la fonction contient des sous-requêtes, elle seront exécutées pour chacune de ces lignes, ce qui peut vite être trèèèès lent (mais test d'abord: on se fait souvent une fausse idée de cette lenteur, parce qu'on parle en fait de qq ms à peine, donc si t'as quelques milliers de lignes, pour une requête lancée par un CRON, c'est amplement raisonnable). Du coup, entre une fonction faisant des sous-requêtes et une reformulation qui évite cela, la reformulation sera nettement plus rapide (à confirmer quand même). Une idée d'optimisation consiste alors à utiliser un LEFT JOIN pour récupérer les infos nécessaires, et passer les valeurs de ce LEFT à cette fonction: SELECT myFunc(t.x, u.x) FROM u LEFT JOIN t ON t.id_u = u.id au lieu de SELECT myFunc(t.x) FROM u avec myFunc(t.id AS tid, t.x AS tx) { SELECT u.x FROM u WHERE u.id = tid } (pseudo-code)

• Techniquement, les INSERT dans la fonction devraient être faisable, mais tu te diriges vers un bordel sans nom, car le fait d'appeler la fonction va altérer l'état des données du SQL. Perso, je trouve bien plus clair d'avoir des fonctions qui ne font *que* de la lecture (jamais d'écriture SQL) et laisser les écritures au procédures uniquement.


Edit: Pour info, c'est cadeau (vu qu'il n'est plus actif), voici la procédure de simulation d'ECLERD. Elle était lancée périodiquement, toutes les 24H je crois, et peut-être bien à chaque page en y réfléchissant... 0 temps de latence pour un jeu qui devait compter 100-200 cases

Code :
DROP PROCEDURE IF EXISTS `simulation`;/*$$*/

CREATE PROCEDURE `simulation`()
LANGUAGE SQL
NOT DETERMINISTIC
MODIFIES SQL DATA
SQL SECURITY INVOKER
COMMENT ''
BEGIN
    DECLARE idCase INT UNSIGNED;
    DECLARE elect CURSOR FOR
        SELECT m.id
        FROM map AS m
        WHERE m.`date_election` <= NOW()
    ;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET @cursorEnd := TRUE;

    -- Player's popularity, by laws
    -- @TODO This is the main part of the game, so these equations must be well thought and done
    UPDATE map m
    INNER JOIN laws AS l ON l.id_player = m.id_player
    SET
        m.insecurite = LEAST(1, GREATEST(0, 1.0*achat_armes_autorise-0.4*etat_urgence-0.15*((1+censure)*(1+propagande))+1.0*(1-m.popularite))),
        m.environnement = LEAST(1, GREATEST(0, 0.5-0.25*(1-CAST(plan_ville_verte AS SIGNED)*2))),
        m.education = LEAST(1, GREATEST(0, 0.8*scolarisation_obligatoire*(1+0.2*etudes_superieures_obligatoires))),
        m.popularite = LEAST(1, GREATEST(0, 0.4*m.environnement+0.6*m.education-0.5*m.insecurite))
    ;

    -- Election's score
    UPDATE election_candidat AS ec
    SET ec.score = IF(
        ec.id_player IS NULL,
        0.5,
        IFNULL(
            (SELECT m.popularite
                FROM map AS m
                WHERE m.id = ec.id_case AND m.id_player = ec.id_player),
            IFNULL(
                (SELECT SUM(m.popularite)/SUM(1.5)
                    FROM map AS m
                    WHERE m.id_player = ec.id_player),
                0.25)))
    ;

    -- Proceed the elections
    OPEN elect;
    electLoop: LOOP
        FETCH elect INTO idCase;
        IF (@cursorEnd) THEN
            LEAVE electLoop;
        END IF;

        SET @vainqueur := (SELECT ec.id_player FROM election_candidat AS ec WHERE ec.id_case = idCase ORDER BY ec.score DESC LIMIT 1);

        -- Add 1 day to the date election untill we're in the future
        -- @TODO Find better (there certainly is, like by counting number of days between today and this election's date)
        SET @dateElection := (SELECT m.`date_election` FROM map AS m WHERE m.id = idCase);
        WHILE (@dateElection < NOW()) DO
            SET @dateElection := DATE_ADD(@dateElection, INTERVAL 1 DAY);
        END WHILE;
        
        -- Save that next election date in the DB
        UPDATE map AS m
        SET m.id_player = @vainqueur,
            m.`date_election` = @dateElection
        WHERE m.id = idCase;

        DELETE FROM election_candidat
        WHERE id_case = idCase AND id_player IS NOT NULL AND (@vainqueur IS NULL OR id_player != @vainqueur)
        ;
    
    END LOOP;

END$$

Ca peut te permettre de visualiser comment faire la tienne.
Répondre
#5
Merci pour ce cadeau, spécialement pour le CURSOR, ça m'a beaucoup aidé.
Voila un premier jet:

Code :
DELIMITER |
CREATE PROCEDURE calcul_trajet (IN int_heure_1 INT, IN int_heure_2 INT)
BEGIN
DECLARE int_id_trajet INT;
DECLARE c_trajet CURSOR FOR SELECT id FROM trajet WHERE trajet.heured >= int_heure_1 AND trajet.heurea < int_heure_2;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

OPEN c_trajet;
read_loop: LOOP

FETCH c_trajet INTO int_id_trajet;
IF done THEN
LEAVE read_loop;
END IF;

INSERT INTO event( type, heure, val1 ) SELECT 'FinTrajet', trajet.heurea, calcul_passager(trajet.id) FROM trajet WHERE trajet.id = int_id_trajet;

END LOOP;

END |

Je sait que pour le moment je pourrait seulement utiliser la ligne INSERT mais je vais ajouter d'autres insertions dans le LOOP à l'avenir.
ça me parait plus claire comme ça.
Répondre




Utilisateur(s) parcourant ce sujet : 1 visiteur(s)