Discussion :

[Piwaï[INSA]]

Bonjour !!

Je débarque assez fraichement dans le monde des requêtes SQL...
Ca fait un bail que je sais faire des requètes de bases, qui ne posent de problème à personne, mais dès qu'on commence à vouloir faire des trucs fun, c'est une autre paire de manches.

J'aimerai simplement que vous m'indiquiez si ma démarche est correcte, et comment je pourrai éventuellement améliorer ma requête .


Pour la petite histoire (zappez si vous êtes pressés) : je développe un jeu php (encore un !..) open source (ça c'est moins fréquent) avec Symfony (ça c'est beaucoup plus rare...).

Je simplifie le problème pour garder l'essentiel (->j'enlève les FK etc...) :

J'ai une table buildings (des bâtiments), qui sont caractérisés par une id, un type et un niveau. Le type est un entier, ça correspond par exemple à une ferme, une caserne, une mine... Le niveau représente la taille de ce bâtiment.

Il est possible d'avoir plusieurs bâtiments du même type.

Un bâtiment ne peut être construit que si certains bâtiments ont été construits et ont atteint un certain niveau. C'est le principe d'un arbre des technologies (-> un graphe valué orienté non cyclique).

Par exemple, une caserne ne peut être construite que si l'on a un marché de niveau supérieur ou égal à 2 et une forge de niveau supérieur ou égal à 3.

Ces relations ne sont pas stockées en BD, mais dans des fichiers de config externes.

Le but de ma requête est de savoir si je peux construire un nouveau bâtiment. Je fournis donc mes conditions à la requête, et elle doit me renvoyer 1 si les conditions sont remplies, et 0 sinon.

Voici la table pour l'exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
DROP TABLE IF EXISTS `testBuilding`;
CREATE TABLE `testBuilding` (
  `id` int(11) NOT NULL auto_increment,
  `type` int(11) NOT NULL,
  `level` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
 
 
 
INSERT INTO `testBuilding` (`id`, `type`, `level`) VALUES 
(1, 0, 2),
(2, 0, 3),
(3, 1, 4),
(4, 2, 5),
(5, 1, 3),
(6, 0, 2);

Et voici une requête avec comme conditions : je veux savoir si j'ai
au moins un bâtiment de type 0 dont le niveau est supérieur ou égal à 2
ET
au moins un bâtiment de type 1 dont le niveau est supérieur ou égal à 3.

Donc pour résumer, je ne veux pas récupérer ces enregistrements, juste savoir si je les ai bien.


Voici la requête que j'ai créé :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
SELECT 1
FROM(
  SELECT COUNT(type) AS total
  FROM (
    SELECT type, MAX(level) as levelMax
    FROM `testBuilding` 
    WHERE type=0 or type=1 Group by type)
  AS temp
  WHERE (type=0 and levelMax>=2) or (type=1 and levelMax>=3))
AS temp2 
WHERE total=2

En fait, je récupère, pour les types qui m'intéressent, les rangées ayant le niveau le plus élevé (je m'assure ainsi de l'unicité des types). Je compte ensuite combien j'en ai qui vérifient mes conditions, et je vérifie que ce total vaut bien le nombre de conditions.


J'ai l'impression que je me suis pris le choux pour pas grand chose, et qu'il y a moyen de faire beaucoup plus simple. Mais je ne sais pas trop comment...

J'espère que vous pourrez m'aider .

Bonne journée !!

@++

Piwaï

[vmolines]

Avant de considérer la partie SQL, je trouve qu'avoir des fichiers de description de l'arbre et générer, pour chaque demande, une requête dynamiquement avec les bonnes combinaisons types/niveaux est fastidieux.

As tu considéré la création de l'arbre technologique au sein de la base directement ? Comme ça tu pourrais faire des requêtes qui pourront directement lier un bâtiment et l'arbre technologique nécessaire pour sa construction. Ici tu dois interroger ton fichier de config avec ton langage et créer une requête.

C'est juste une remarque générale, je ne dis pas que ce serait facile ou le bon choix.

[Piwaï[INSA]]

Bonjour,

Merci pour cette prompte réponse .

J'ai pris une habitude, je ne sais pas si elle est bonne : les données variables vont dans une bd, les données de configurations vont dans des fichiers de configuration.

Pour moi, un arbre technologique est clairement une donnée de configuration : même si les choses sont amenées à changer lors des phases de test et d'équilibrage du jeu, par la suite l'arbre ne bouge plus.

D'autres part, Symfony manipule particulièrement bien les fichiers YAML (c'est comme du xml, mais c'est plus simple à lire et plus rapide à écrire), et la modification de la config est dès lors relativement aisée.

Toutefois, je ne suis pas contre la possibilité d'intégrer l'arbre des bâtiments à la base de données, si cela permet d'accélérer les calculs et de simplifier les requêtes (j'en profite pour préciser que j'utilise mysql... pour le moment, et probablement passage en postgres plus tard -> il faut donc que mon code sql soit relativement portable)


Voici donc la table tel que je la vois :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
CREATE TABLE `condition_buildings` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`type_cible` INT NOT NULL ,
`type_requis` INT NOT NULL ,
`level_requis` INT NOT NULL
) ENGINE = MYISAM ;

-> 1 enregistrement par condition pour un bâtiment donné.


Mais dès lors... que devient ma requête pour savoir si je peux construire un bâtiment ?? Je suis un peu perdu .

Je suppose qu'il faut créer une fonction sql qui prend un type de bâtiment en paramètre et renvoie 1 si je peux construire le bâtiment, et rien ou 0 sinon.
Je n'aurai plus qu'à appeller cette fonction...

Mais je vois mal ce que doit contenir cette fonction .

[Piwaï[INSA]]

Je sais pourquoi je l'ai pas fait en SQL au départ ...

Parce que c'est galère !!

Je suis en train de me tordre la tête à imaginer la requête... Mais cha veut pas ..

Pourtant, le principe est vraiment pas compliqué.

Auriez-vous sous la main des exemples de fonctionnement d'arbre technologiques, histoire que je m'inspire ? Merci d'avance .

Je suis aussi très friand de toute idée ou solution à mon problème .

Merci

@++

Piwaï

[vmolines]

La table que tu proposes pour décrire les types de batiment requis pour la création d'un nouveau type est pas mal. Du plus ça sera facile à interroger.

Si on considère une table T_InstanceBatiment qui recense les bâtiments construits dans ton univers (je néglige les informations d'appartenance, de position, ... probablement nécessaires à ton jeu) :

T_InstanceBatiment :
Id
IdType
Niveau

ta table de conditions

T_ConditionTypeBatiment :
IdTypeCible
IdTypeRequis
NiveauRequis

une table de description des types de batiments :

T_TypeBatiment
IdType


Voici la requête qui donne tous les types constructibles à partir des batiments que tu as dans ton univers.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT *
FROM T_TypeBatiment
WHERE NOT EXISTS 
	(SELECT * FROM T_InstanceBatiment 
	WHERE NOT EXISTS 
		(SELECT * FROM T_ConditionTypeBatiment
		WHERE T_InstanceBatiment.IdType = T_ConditionTypeBatiment.IdTypeRequis 
		AND T_InstanceBatiment.Niveau = T_ConditionTypeBatiment.NiveauRequis
		AND T_TypeBatiment = T_ConditionTypeBatiment.IdTypeCible))

Du moins l'esprit de la requête car ça tombe jamais bien du premier coup sans test. Je l'énonce en français :

Retourner tous les types de bâtiment pour lesquels il n'existe pas d'instance de batiment qui ne soit pas dans les types/niveau de batiment requis pour le type.

Comme je le dis c'est pour l'esprit. Il y a sûrement des erreurs car je suis pas assez bon pour pondre des requêtes bonnes d'un coup sur des trucs un peu compliqués. Il manque aussi la notion de joueur qui dirait pour un joueur donné quels types il peut construire. Mais la difficulté n'est pas là, je me suis concentré sur ta demande.

[Piwaï[INSA]]

Merci pour cette réponse, j'apprécie le temps que tu m'accordes.

J'ai bien pris le temps de réfléchir avant de te répondre..

La requête ne fonctionne pas, elle ne renvoie rien (après une petite correction : T_TypeBatiment devient T_TypeBatiment.IdType dans la dernière ligne).

C'est peut-être un problème purement technique, mais il me semble qu'il y a aussi un problème d'ordre logique :

Tout d'abord, il s'agit de vérifier qu'on est >= et non = à un niveau (m'enfin ça change pas grand chose).
D'autres part, il faut savoir qu'un bâtiment n'est pas forcement construit. Donc il est possible que T_InstanceBatiment ne renvoie aucune instance de bâtiment, ce qui ne veut pas dire qu'il n'y aucun bâtiment qui ne remplie pas les critères, mais simplement que certains bâtiments n'existent pas encore (donc ils ne remplissent pas les critères).


Pour faire plus clair(edit : jme demande si c'est vraiment plus clair..), voici la requête tel que je la vois en français :

Donner tous les types de bâtiments tel que pour chaque type, toutes les conditions soient remplies. Une condition est remplie lorsque le type_requis existe dans la table des instances de bâtiment (sachant qu'il peut y avoir plusieurs plusieurs bâtiments de même type), et que son level est supérieur ou égal au level_requis.

Ou encore : donner les types de bâtiment pour lequels il n'existe pas de condition non remplie.

Reste plus qu'à trouver comment obtenir des conditions non remplies pour un type donné...

Une condition est non remplie si le batiment type_requis n'existe pas, ou s'il existe et que son niveau est inférieur au niveau requis

Ca carbure à fond je suis en train de trouver je pense..

(je fais plein d'édits successifs).

[vmolines]

...Ou encore : donner les types de bâtiment pour lequels il n'existe pas de condition non remplie.
...

C'est exactement ce que je voulais dire dans :

Retourner tous les types de bâtiment pour lesquels il n'existe pas d'instance de batiment qui ne soit pas dans les types/niveau de batiment requis pour le type.

sauf que je détaille le "pas de condition remplie" et que je ne m'exprime pas clairement .


Ma requête visait cet énoncé mais du premier coup sans avoir de base et de données pour tester, c'est tendu.

[Piwaï[INSA]]

Bon, j'ai pris le temps de bien faire les choses.

Il ne me manque plus qu'une petite réponse pour réussir à faire ce que je veux .

Voici donc mes tables :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
 
CREATE TABLE `type_building` (
  `type` int(11) NOT NULL,
  PRIMARY KEY  (`type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
 
-- 
-- Contenu de la table `type_building`
-- 
 
INSERT INTO `type_building` (`type`) VALUES 
(0),
(1),
(2),
(3),
(4),
(5),
(6);
 
CREATE TABLE `test_building` (
  `id` int(11) NOT NULL auto_increment,
  `type` int(11) NOT NULL,
  `level` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;
 
 
INSERT INTO `test_building` (`id`, `type`, `level`) VALUES 
(1, 0, 2),
(2, 0, 3),
(3, 1, 4),
(4, 2, 5),
(5, 1, 3),
(6, 0, 2);
 
CREATE TABLE `condition_building` (
  `id` int(11) NOT NULL auto_increment,
  `type_cible` int(11) NOT NULL,
  `type_requis` int(11) NOT NULL,
  `level_requis` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;
 
 
INSERT INTO `condition_building` (`id`, `type_cible`, `type_requis`, `level_requis`) VALUES 
(1, 2, 0, 2),
(2, 2, 1, 6),
(3, 4, 3, 1);
(4, 3, 0, 1);

Le but est désormais une requête qui renvoie la liste des types de bâtiments que l'on peut construire.

Dans l'exemple présent, je résume les données :

Le bâtiment de type 2 peut être construit si on a un bâtiment de type 2 et de niveau >=2 ET un bâtiment de type 1 et de niveau>=6 (ce n'est pas le cas)
La bâtiment de type 4 peut être construit si on a un bâtiment de type 3 et de niveau >=1 (ce n'est pas le cas)
La bâtiment de type 3 peut être construit si on a un bâtiment de type 0 et de niveau >=1 (c'est le cas)

Lorsqu'il n'y a pas de conditions le concernant, un type de bâtiment est constructible.

Donc, la requête doit renvoyer comme types disponibles :

0, 1, 3, 5, 6

Voici ma requête à l'heure actuelle :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
SELECT *
FROM type_building as t
WHERE NOT EXISTS
(SELECT * FROM condition_building as c, test_building as b 
		WHERE  c.type_cible = t.type AND (b.type=c.type_requis and b.level<c.level_requis))

Le problème, c'est que j'ai comme résultat :
0, 1, 3, 4, 5, 6

Le 4 est en trop, car on n'a aucun bâtiment de type 3. Il faut donc que je modifie la requête, pour qu'une condition soit sélectionnée si le niveau n'est pas bon OU SI le bâtiment n'existe pas.

Ca devrait ressembler à quelque chose comme ça

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
 
SELECT *
FROM type_building as t
WHERE NOT EXISTS
(SELECT * FROM condition_building as c, test_building as b 
		WHERE  c.type_cible = t.type AND ((b.type=c.type_requis and b.level<c.level_requis) OR (NOT 
 
EXISTS(??))))

La question est : que mettre dans les ?? . Ya ptet moyen de faire plus simple non ?

Merci d'avance . En tout cas, les choses sont beaucoup plus claires pour moi maintenant, merci !!

@++

Piwaï

[vmolines]

En relisant ma requête pour voir si elle se conformait bien à l'énoncé, je me suis rendu compte qu'il était erroné. Donc sa traduction SQL était vaine.

Voilà un nouvel énoncé détaillé :

Les Types de batiment pour lesquels il n'existe aucun type/niveau requis qui ne soit pas pas dans les instances de batiment

Simple, efficace et facile à traduire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
SELECT *
FROM T_TypeBatiment
WHERE NOT EXISTS (
		SELECT * 
		FROM T_ConditionTypeBatiment 
		WHERE T_ConditionTypeBatiment.IdTypeCible = T_TypeBatiment.IdType 
		AND NOT EXISTS (
				SELECT * 
				FROM T_InstanceBatiment 
				WHERE T_InstanceBatiment.IdType = T_ConditionTypeBatiment.IdTypeRequis AND T_INSTANCEBATIMENT.niveau >= T_ConditionTypeBatiment.NiveauRequis
				)
		)

Désolé de pas avoir monté ta base, pour te fournir les résultats avec tes jeux d'essai mais j'ai pas de server MySQL sous la main.

Si tout à l'heure, j'étais dans l'approximatif, là, par contre, c'est garanti.

[vmolines]

Maintenant qu'on a fini avec la partie difficile et technique, je voulais revenir sur la partie philosophique :

J'ai pris une habitude, je ne sais pas si elle est bonne : les données variables vont dans une bd, les données de configurations vont dans des fichiers de configuration.

C'est une façon de faire. Mais il faut voir une base données comme un outil pour stocker des informations au même titre qu'un fichier xml. A la différence près (et quelle différence !), que c'est étudier pour stocker des données relationnelles (sgdR).

Or dans ton cas, tu as des données, que tu qualifies de configuration peut être, qui doivent être mises en relations avec les données que tu qualifies de variable.

Moi je ne qualifie pas les données. Qu'elles soient mise à jour souvent ou une fois dans leur vie, si elles nécessitent d'être mises en relation avec d'autres, elles doivent figurer à leur côté.

L'exemple était parfait ici et ça tombe bien.



Pour aller jusqu'au concret, tu devras un moment, proposer au joueur uniquement les types de bâtiments constructibles. La solution "tout en bdd" fournit ça directement. Une solution avec des fichiers externes oblige à jongleravec différents méthodes d'accès (SQL et accès fichier xml ou pas). De surcroit, la base de données t'offre l'optimisation des relations entre tables alors qu'un accès fichier n'apporte rien.


Je ne critique pas du tout ta première idée pour la critiquer évidemment. Juste pour expliquer le pourquoi du choix "tout en bdd".

[Piwaï[INSA]]

Merci

Effectivement, ça fonctionne niquel , j'ai bien le résultat escompté.

(après changement des noms)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT *
FROM type_building AS t
WHERE NOT EXISTS (
  SELECT *
  FROM condition_building AS c
  WHERE  c.type_cible = t.type
  AND NOT EXISTS (
    SELECT * 
    FROM test_building as b
    WHERE b.type=c.type_requis
    AND b.level>=c.level_requis
 )  
)

Au niveau de l'énoncé, c'est ainsi que je le concevais. Contrairement à la première requête, je comprends parfaitement celle-ci .

Bon, bah c'est super tout ça !! Enfin... il me reste encore à modifier pas mal de fichiers pour sortir l'arbre de la conf et le mettre en bd... Et puis à inclure tout un tas de FK de partout.. mais le plus dur est fait, le reste n'est que broutilles .

Quand à la partie philo, je vais donc modifier ma propre philo : les données relationnelles vont en bd, les données non relationnelles et constantes vont en fichiers (j'énonce une évidence.. que je n'avais pas vu). Effectivement, c'était un parfait exemple de ce qu'il ne fallait pas faire. L'essentiel est d'apprendre de ses erreurs...

L'idéal serait de pouvoir faire ce genre de requêtes magiques avec Propel (un ORM cool en php), mais je crois pas qu'il soit possible d'utiliser la clause EXIST ainsi que les sous-requètes.

Je tiens encore à te remercier pour tes réponses, elles m'ont permis de mieux d'avancer dans mon projet et de mieux comprendre les NOT EXIST avec les sous-requêtes.

Merci encore, et bonne journée

@++

Piwaï

[vmolines]

Aucun problème, c'est avec plaisir. Et moi ça me fait réviser

Pour la suite veille à bien ficeler ton modèle conceptuel car il y a de nombreuses possiblités pour modéliser tout ce qui va graviter dans ton jeu. Et beaucoup d'entre elles seront mauvaises et rendront difficile l'exploitation des informations.

[Piwaï[INSA]]

Pour le modèle conceptuel, la première chose que j'ai faite, c'est un bon gros diagramme de classes uml (+ un diagramme de navigabilité..).

Bien entendu, il n'est plus tout à fait à jour, et il faut que je modifie certaines choses .


Maintenant que j'y pense : en fait, ya juste une toute petite chose qu'il me manque pour que ça soit parfait :

En fait, pour construire un bâtiment, il faut aussi connaitre certaines sciences.

Le fonctionnement est exactement identique, donc en gros, il faudrait que j'intègre deux SELECT dans le NOT EXIST. En reprenant la requête, ça ferait genre :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
SELECT *
FROM type_building AS t
WHERE NOT EXISTS (
    SELECT *
    FROM condition_building AS c
    WHERE  c.type_cible = t.type
    AND NOT EXISTS (
        SELECT * 
        FROM test_building AS b
        WHERE b.type=c.type_requis
        AND b.level>=c.level_requis
 
ET LA UN TRUC POUR LIER LES DEUX SELECT
 
    SELECT *
    FROM condition_science AS cs
    WHERE  cs.type_cible = t.type
    AND NOT EXISTS (
        SELECT * 
        FROM test_science AS s
        WHERE s.type=cs.type_requis
        AND s.level>=cs.level_requis
 )  
)

Sauf que SQL ne fonctionne pas comme ça... Une idée ? J'ai pas parlé des sciences dès le départ, pour ne pas rendre le truc trop complexe .

On peut envisager de prendre l'intersection entre les types qui valident les conditions de bâtiments et les types qui valident les conditions de science.

Qu'en penses-tu ?

[vmolines]

Un truc pour lier deux résultats booléens ? un opérateur logique AND par exemple

Enoncé :

Les Types de batiment pour lesquels
- il n'existe aucun type/niveau requis qui ne soit pas pas dans les instances de batiment
ET
- il n'existe aucune science requise par le type de bâtiment qui ne soit pas dans les sciences connues par le joueur

[Piwaï[INSA]]

Lol désolé : fin de journée = fatigué...

J'ai honte de moi

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
SELECT *
FROM type_building AS t
WHERE NOT EXISTS (
    SELECT *
    FROM condition_building AS c
    WHERE  c.type_cible = t.type
    AND NOT EXISTS (
        SELECT * 
        FROM test_building AS b
        WHERE b.type=c.type_requis
        AND b.level>=c.level_requis
    )
  )
AND NOT EXISTS (
    SELECT *
    FROM condition_science AS cs
    WHERE  cs.type_cible = t.type
    AND NOT EXISTS (
        SELECT * 
        FROM test_science AS s
        WHERE s.type=cs.type_requis
        AND s.level>=cs.level_requis
    )
  )

-> Blamez-moi...

Lol... ce genre de questions ça fait vraiment pas sérieux.. Comment peut-on prétendre développer un jeu et bloquer sur des trucs aussi débiles ? Moi même je me l'explique pas... ptet la chaleur.

[vmolines]

Je te rassure, ça m'arrive plus que souvent ! On s'enferme souvent sur des reflexions qui nous font perdre de vue la simplicité de certaines choses.

D'où l'intérêt de prendre du recul parfois ou d'avoir un oeil nouveau sur le sujet.