Discussion :

[Erakis]

Bonjour à tous,

Je cherche à déterminer un WBS *disponible* (Work Breakdown structure) à partir d'une procédure stockée.

Déclaration de la table contenant déjà des séquences de WBS

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
DECLARE @Table TABLE
(
     WBS VARCHAR(40)
)
 
INSERT INTO @Table VALUES( '1' )
INSERT INTO @Table VALUES( '1.1' )
INSERT INTO @Table VALUES( '1.1.1' )
INSERT INTO @Table VALUES( '1.2' )
INSERT INTO @Table VALUES( '2' )
INSERT INTO @Table VALUES( '4' )

Voilà à quoi ressemblerait l'entête de la procédure dont j'ai besion :

Code :

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

1
2
3
4
5
6
7
8
CREATE PROCEDURE spGenererWBS
( 
   @WBSParent  VARCHAR(40)
)
AS
BEGIN
   ...
END

Donc, imaginons que j'aimerais créer une nouvelle entrée sous le parent '1', il faudrait que la procédure me retourne '1.3.' Car 1.1 et 1.2 existe déjà.

Si je voudrais créer une nouvelle entrée sous le parent '1.1.1', il faudrait que la procédure me retourne '1.1.1.1' Du fait que sous 1.1.1 il n'y a pas encore d'entrée.

Si je passe NULL pour le paramètre WBSParent, alors la fonction devrait me retourner '3', du fait que le WBS à insérer n'a pas de parent. Donc il est générer à partir de la racine

Jusqu'ici, je n'ai pas trouver d'autre solution mise à part utiliser un curseur et tester les WBS en incrémentant de 1 à chaque saut pour déterminer un WBS libre. Toutefois, j'ai lu à plusieurs endroits que les cursors doivent être utilisés qu'en extrême cas. Sinon, garre aux performances.

Avez-vous une meilleure idée ?

Merci

[elsuket]

Bonjour,

En effet, comme le langage SQL est conçu pour traiter des ensembles de données, et non pas chaque tuple de cet ensemble un à un, les curseurs et boucle de type WHILE sont à proscrire.

Vous pouvez remplacer ce traitement itératif par une procédure stockée (ou une fonction) d'assembly ...

@++

[Erakis]

En fait, c'est exactement ce que je désire faire (procédure stockée).

Toutefois, je ne sais pas comment m'y prendre à l'aide de requêtes. Je crois que je réfléchie trop comme un programmeur.

J'essaie d'écrire une requête qui ajoutera un nombre après le WBS du parent , ensuite testera si ce WBS existe. S'il n'existe pas alors le tour est joué. Dans le cas contraire, je dois incrémenter le nombre de + 1 et retester jusqu'à ce que la valeur testé n'existe pas dans la table.

PS : J'aimerais éviter l'utilisation d'assembly, puisque la base de données risque de migré vers FireBird ou un autre SGBD gratuit.

[elsuket]

Finalement, on ne s'en sort pas trop mal

Pour faire les tests, j'ai considéré la table suivante :

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
CREATE TABLE TbWBS
(
     WBS VARCHAR(40) CONSTRAINT PK_TbWBS PRIMARY KEY
)
GO
 
ALTER TABLE dbo.TbWBS
ADD CONSTRAINT CHK_TbWBS_WBS
	CHECK (WBS NOT LIKE '.%' AND WBS NOT LIKE '%.')
 
INSERT INTO TbWBS VALUES( '1' )
INSERT INTO TbWBS VALUES( '1.1' )
INSERT INTO TbWBS VALUES( '1.1.1' )
INSERT INTO TbWBS VALUES( '1.2' )
INSERT INTO TbWBS VALUES( '2' )
INSERT INTO TbWBS VALUES( '4' )
GO

Voici le code de la procédure :

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
50
51
52
53
 
CREATE PROCEDURE PsManageWBS
	@_wbs VARCHAR(40)
AS
BEGIN
	SET NOCOUNT ON
 
	IF EXISTS
	(
		SELECT *
		FROM dbo.TbWBS
		WHERE LEN(WBS) = LEN(@_wbs) + 2
		AND WBS LIKE @_wbs + '.%'
	)
	BEGIN
		DECLARE @newWBS VARCHAR(40)
 
		SELECT @newWBS = CAST(MAX(CAST(RIGHT(WBS, 1) AS TINYINT)) + 1 AS VARCHAR(40))
		FROM dbo.TbWBS
		WHERE LEN(WBS) = LEN(@_wbs) + 2
		AND WBS LIKE @_wbs + '.%'
 
		INSERT INTO dbo.TbWBS
		(
			WBS
		)
		SELECT @_wbs + '.' + @newWBS
	END
	ELSE
	BEGIN
		IF EXISTS
		(
			SELECT *
			FROM dbo.TbWBS
			WHERE WBS = @_wbs
		)
		BEGIN
			INSERT INTO dbo.TbWBS
			(
				WBS
			)
			SELECT @_wbs + '.1'
		END
		ELSE
		BEGIN
			INSERT INTO dbo.TbWBS
			(
				WBS
			)
			SELECT @_wbs
		END
	END
END

@++

[Erakis]

Wow, merci c'est pas mal ce que je voulais.

De mon côté, j'ai créé quelque chose de similaire, toutefois je démarre au WBS parent plus '.1' et je boucle en incrémentant de 1 jusqu'à ce que je trouve un numéro disponible.

Prenons le cas suivant :

Je veux ajouter un WBS sous le parent 1

Code :

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

1
2
3
4
5
1
  1.1
  1.3
  1.4
2

Votre procédure retournera 1.5 car le MAX est 4 et donc 4 + 1 = 5.

En revanche, ma procédure retournera 1.2, car il y a un troue entre 1.1et 1.3. Cependant, il est à noté que ma procédure est assez gourmande puisqu'elle doit itérer (à partir du début du parent) et ce plusieurs fois jusqu'à ce qu'elle trouve un numéro libre. Je voulais utiliser ce principe pour remplir les troues...

Toutefois, je vais plutôt pencher vers VOTRE solution, puisqu'elle est plus performante. Il me restera qu'à bien écrire les procédures stockées d'insertion afin d'éviter d'avoir des troues.


De plus, j'ai remarqué votre contrainte CHECK sur la table. Avec les outils présent (LIKE) et PATHINDEX et MS-SQL, je n'ai pas réussi à créer un pattern pour des WBS. Donc, j'ai pensé à une expression régulière du genre :

Code :

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

^[0-9]+(\.[0-9]+)+

Mais j'ai fin par comprendre que MS-SQL ne les gère pas À moins d'utiliser un assembly .NET. Ce qui ne m'intéresse pas, car la base de données risque de migrer vers un autre SGBD éventuellement.

[Erakis]

Je viens de me rendre compte qu'il y a un petit problème avec votre méthode. Elle fonctionne qu'avec les WBS plus petit que 9.

Donc si j'ai les WBS suivante :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
1
  1.1
  1.2
  1.3
  1.4
  1.5
  1.6
  1.7
  1.8
  1.9
  1.10

Et que je demande l'insertion d'un nouveau WBS dans '1'. Votre fonction retournera 1.9 au lieu de 1.11.

Je la modifierai en conséquence
Encore un gros merci pour votre aide, c'est apprécié.