[SQL] aide pour une requête "récursive"

[Ter Rowan]

coucou, j'ai un sujet qui m'embête, et je ne sais pas comment (et si c'est possible) la réaliser via une requête unique. Je saurais le faire via php, avec récursivité ou autre, mais j'aimerais être en mesure de le faire via une seule requête (si c'est pertinent)

Voici le besoin : calculer l'impôt que récolte un suzerain sur ces vassaux :

supposons

le duc D1 a 2 comtes C1.1 C1.2 et un baron B1.0.1 comme vassaux

le comte C1.1 a pour vassaux deux barons B1.1.1 et B1.1.2
le comte C1.2 a pour vassaux deux barons B1.2.1 et B1.2.2

chacun a deux sources de revenus :
- les revenus propres de ses terres que je calcule par ailleurs
- les impôts sur les revenus (propres + impots) de ses vassaux

Evidemment les impôts varient pour chaque couple (suzerain, vassal)
si on raisonne en table,ça donnerait (décision du joueur) :

suzerain / vassal / taux d'impot
D1 / C1.1 / 10%
D1 / C1.2 / 15%
D1 / B1.0.1 / 20%
C1.1 / B1.1.1 / 15%
C1.1 / B1.1.2 / 10%
C1.2 / B1.2.1 / 10%
C1.2 / B1.2.2 / 10%

Si on compte en revenu propre on aurait (via d autres calculs) :

seigneur / revenu propre
D1 / 1000
C1.1 / 300
C1.2 / 400
B1.0.1 / 100
B1.1.1 / 100
B1.1.2 / 250
B1.2.1 / 100
B1.2.2 / 100


et ce que je cherche à obtenir :

seigneur / revenu total
B1.0.1 / 100 * 0.8 --> on suppose pas d'impot pour les barons donc total = propre - impot au suzerain
B1.1.1 / 100 * 0.9
B1.1.2 / 250 * 0.9
B1.2.1 / 100 * 0.9
B1.2.2 / 100 * 0.9
C1.1 / ( 300 + 0.15*100 + 0.1 * 250 ) = 340
C1.2 / (400 + 0.1 * 100 + 0.1 * 100) *0.85 = 420 *0.85
D1 / 1000 + 0.1 * 340 + 0.15 * 420 + 100 * 0.2

Pour les volumétries, imaginons qu'il y ait 10000 vassaux dans une hiérarchie complètement désarticulée (j'ai mis là un baron et deux comtes sous D1, mais on peut imaginer des vassaux sous B1.1.2, plusieurs duc, etc...)

Qu'en pensez vous ? Merci de votre aide !

[niahoo]

Alors je peux absolument pas t'aider en SQL vu que je suis pas très doué, mais voici une remarque :

Tu peux simplement, à chaque fois qu'un seigneur gagne quelque chose, lire dans ta DB quel pourcentage il doit reverser à son suzerain, puis soit lui verser directement, soit mettre ça dans un buffer qui sera relevé au moment où les impôts sont récoltés.

Pour le buffer, tu peux prélever l'argent du vassal pour le mettre dedans, ou bien simplement enregistrer le montant pour le prélever plus tard quitte à ce que le vassal n'ait pas assez pour payer à ce moment là et se fasse péter les rotules à coup d'arquebuse.

[Ter Rowan]

Alors je peux absolument pas t'aider en SQL vu que je suis pas très doué, mais voici une remarque :

Tu peux simplement, à chaque fois qu'un seigneur gagne quelque chose, lire dans ta DB quel pourcentage il doit reverser à son suzerain, puis soit lui verser directement, soit mettre ça dans un buffer qui sera relevé au moment où les impôts sont récoltés.

Pour le buffer, tu peux prélever l'argent du vassal pour le mettre dedans, ou bien simplement enregistrer le montant pour le prélever plus tard quitte à ce que le vassal n'ait pas assez pour payer à ce moment là et se fasse péter les rotules à coup d'arquebuse.

j'avais pensé au buffer, mais ca revient à faire une boucle avec pas mal d'instructions : "tant qu'il y a un enregistrement dans le buffer :
- reverser les impots au dessus (update de la somme),
- supprimer les enregistrements du buffer (delete/update du buffer fonction d'une suppression logique ou pas)
- créer des lignes dans le buffer pour le suzerain du suzerain (s'il existe)

soit n*3 requêtes avec n le nombre d'intermédiaire entre le seigneur sans suzerain (roi par exemple) et le seigneur sans vassal (banneret par exemple)

on arrivera certainement à 20 ou 30 requêtes (c'est mon scenario php)

[niahoo]

Eh ben oui j'avais pas pensé à la répercussion en chaine. Bon perso moi ça me paraît sympa : facile à coder, pratique parce que ça revient à créer un event "gagnage de thune" lors duquel tu peux déclencher d'autres trucs que refiler un pourcentage à ton boss.

les super requêtes SQL c'est bien pour la performance (vu comment mes 30 requêtes seront simples et basées que sur des ID ça doit rouler à toute vitesse), mais faut s'en faire une pour chaque truc complexe à gérer. Je sais pas faire

As-tu pensé aux arbres par intervalles ? C'est assez sympa pour traiter tout ce qui peut-être classé en arbre, parfait pour les suzerains et les vassaux.

http://sqlpro.developpez.com/cours/arborescence/

Avec ça et en stockant le pourcentage reversé dans la table des seigneurs sur le vassal ça doit simplifier pas mal. Après dans ma tête si tu écris une requête récursive, mysql l'exécutera autant de fois qu'il y a de récursion. Certes tu n'auras pas la perte de vitesse due à l'IPC

Bon alors, avec mes intervalles là, dès qu'un vassal gagne quelques PO, je récup en une seule requête toute la hiérarchie au dessus du mec sans récursion, avec les thunes actuelles de chacun et le pourcentage qu'ils doivent reverser à leur boss. (je crois qu'il faut une racine unique obligatoirement, t'as qu'à mettre Dieu en haut et lui il prend 0%, basta) (sympa le mec !)

Ensuite ben côté code je calcule mes nouvelles valeurs pour savoir combien chacun va gagner, et je me fais un update avec des additions et pas des remplacements : "thune = thune + $gain" au lieu de "thune = $new_thune", comme ça si une autre requête d'un joueur met à jour une partie de suzerains entre le moment ou j'ai fetch leur thune actuelle et celui ou je leur mets leurs gains, ils ne eprdent rien dû à l'update. Le mieux étant encore de mettre ça dans une transaction comme ça t'es sûr que personne n'est floué.

Et si je compte bien ça fait 2 requêtes. J'ai peut être oublié un truc.

---

pour récupérer tous les suzerains d'un vassal : http://sqlpro.developpez.com/cours/arborescence/#L2.7
leur exemple n'inclut pas le vassal le plus bas, pour l'avoir tu fais ça (mais c'est tellement trivial que tu avais compris)

SELECT id, nom, thune, pourcentage_reversé

FROM   seigneurs

WHERE  NFM_BG <= 29

   AND NFM_BD => 34

---

Non en plus je crois que je dis de la merde, parce que quand un vassal gagne des thunes, il en reverse un certain pourcentage à son suzerain, qui fera pareil et ainsi de suite, mais ces pourcentages s'appliquent sur les gains de chacun et pas leur cagnotte, donc à aucun moment il n'est utile de connaître les thunes actuelles de chaque protagoniste. Donc pas besoin de transaction (sauf si deux updates en même temps sur une ligne mais ça je suppose que sans transaction un SGBDR est pas con au point de télescoper les deux updates -- j'en sais rien)

Ensuites si tu veux faire un buffer qui ne stocke que le montant et qui va ponctionner chaque X jours, quitte à ce que certains ne puissent pas payer, alors ton problème est toujours là puisque il faudra itérer sur chaque type pour voir s'il l'a déjà. Mais bon ça devient vraiment compliqué alors, parce qu'il faudra bien produire une sortie pour informer un joueur qu'un de ses vassaux n'a pas cottisé.

Moi, je ferais plutôt du paiement direct. à l'époque ils n'avaient pas ça mais bon ça simplifie pas mal les choses et au final ça reviendra au même.

[Ter Rowan]

merci pour ton aide, plus je creuse plus je me dis aussi que ça va être compliqué de passer en une requête : en rajoutant des fonctionnalités et autres, vaut mieux passer côté code pour traiter des trucs qu'on imagine pas au début.

J'avais regardé aussi les intervalles mais je ne me sens pas encore très à l'aise dessus.

A voir comment traiter tout ça

sinon :

Moi, je ferais plutôt du paiement direct. à l'époque ils n'avaient pas ça mais bon ça simplifie pas mal les choses et au final ça reviendra au même.

==> ca tombe bien : 1 tour = 1 saison (hiver, printemps ...) donc le paiement est pas si direct que cela (RP parlant)

ces pourcentages s'appliquent sur les gains de chacun et pas leur cagnotte, donc à aucun moment il n'est utile de connaître les thunes actuelles de chaque protagoniste.

==> et j'ai prévu de faire le calcul à la fin du tour pour tout le monde (genre à 3h du mat, avec le jeu en maintenance) donc aucun risque de 2 update en parallèle


en tout cas merci pour ta contrib !

[Roworll]

En une requête, je pense que ce sera difficile car tu as une notion de précédence (il faut commencer par le niveau le plus bas d'imposition puis remonter). Attention, je dis difficile mais pas impossible.

Cependant, à vue de nez, vu que ça fonctionne avec un système de tours, le plus simple me semble de passer par des tables temporaires. Je ne connais pas l'architecture exacte de tes tables mais en travaillant principalement au niveau du moteur SQL, tu vas sans doutes gagner en performance et en temps.

Tu peux par exemple
- Faire une première requête pour consolider toutes tes données dans une table
- Faire une ou plusieurs requêtes en fonction de ta structure pour mettre à jour les impôts/revenus sur chaque niveau de profondeur
- Une dernière requête pour mettre tout cela à jour/faire un rapport.

J'avais donné quelques pistes sur la récursivité sur ce sujet

[Ter Rowan]

Merci Roworll, je vais regarder cela

A noter, je n'ai pas encore le modèle c'est un projet en gestation, et je cherche à voir quel système mettre en oeuvre avant de travailler sur le modèle précis