Générer un WBS

Version imprimable

Générer un WBS

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:

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:

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

04/11/2009, 21h43
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 ...

@++ ;)
04/11/2009, 21h49
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.

Finalement, on ne s'en sort pas trop mal :D

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

Code:

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:

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

@++ ;)

05/11/2009, 17h24
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:

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:

^[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.
05/11/2009, 20h05
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:

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é.

Bon on va voir pour ce problème, moi j'ai une violation de contrainte de clé primaire ...

En attendant, je viens de modifier la procédure pour qu'elle gère les trous :

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
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
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
ALTER PROCEDURE PsManageWBS
	@_wbs VARCHAR(40)
AS
BEGIN
	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 + '.%'
 
		-- Recherche s'il existe des valeurs dans la plage du WBS qui ne sont pas contigües
		-- et récupère le plus petit WBS consécutif à la plus petite valeur existante pour ce WBS
		DECLARE @jeton VARCHAR(40)
 
		;WITH
			CTE_TROU AS
			(
					SELECT CAST(@_wbs + '.' + @newWBS AS VARCHAR(MAX)) AS wbs,
							CAST(@newWBS AS TINYINT) AS indice
				UNION ALL
					SELECT @_wbs + '.' + CAST(indice - 1 AS VARCHAR(MAX)),
							CAST(indice - 1 AS TINYINT)
					FROM CTE_TROU
					WHERE indice > 1 -- Pas de 1.0 ?
			)
		SELECT TOP 1 @jeton = CTE_TROU.wbs
		FROM CTE_TROU
		LEFT JOIN dbo.TbWBS AS WBS
			ON WBS.WBS = CTE_TROU.wbs
		WHERE WBS.WBS IS NULL
		ORDER BY CTE_TROU.indice
 
		IF @jeton IS NULL
		BEGIN
			INSERT INTO dbo.TbWBS
			(
				WBS
			)
			SELECT @_wbs + '.' + @newWBS
		END
		ELSE
		BEGIN
			INSERT INTO dbo.TbWBS
			(
				WBS
			)
			SELECT @jeton
		END
	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
GO

@++ ;)

05/11/2009, 21h54
Erakis
Wow, de mieux en mieux vous dites ! C'est plus que ce que je m'attendais.

J'ai consulté votre code et ça me semble super bien. Il y a juste un petit bout que je ne comprend pas.
Code:

1 2 3 4 5 SELECT @newWBS = CAST(MAX(CAST(RIGHT(actWBS, 1) AS TINYINT)) + 1 AS VARCHAR(40)) FROM Activite WHERE LEN(actWBS) = LEN(@_wbs) + 2 AND actWBS LIKE @_wbs + '.%'
Ici, vous prenez le dernier caractère du WBS, or que cela pourrait tout aussi bien être '1.999'

Est-ce voulu ? Si oui, alors je vais devoir relire le code, car je ne comprend pas ce bout.

Bon, voilà la version finale, toute fraîche :D

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
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
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
ALTER 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, CHARINDEX('.', REVERSE(WBS)) - 1) AS TINYINT)) + 1 AS VARCHAR(40))
		FROM dbo.TbWBS
		WHERE LEN(WBS) >= LEN(@_wbs) + 2
		AND WBS LIKE @_wbs + '.%'
 
		SELECT @newWBS
 
		-- Recherche s'il existe des valeurs dans la plage du WBS qui ne sont pas contigües
		-- et récupère le plus petit WBS consécutif à la plus petite valeur existante pour ce WBS
		DECLARE @jeton VARCHAR(40)
 
		;WITH
			CTE_TROU AS
			(
					SELECT CAST(@_wbs + '.' + @newWBS AS VARCHAR(MAX)) AS wbs,
							CAST(@newWBS AS TINYINT) AS indice
				UNION ALL
					SELECT @_wbs + '.' + CAST(indice - 1 AS VARCHAR(MAX)),
							CAST(indice - 1 AS TINYINT)
					FROM CTE_TROU
					WHERE indice > 1 -- Pas de 1.0 ?
			)
		SELECT TOP 1 @jeton = CTE_TROU.wbs
		FROM CTE_TROU
		LEFT JOIN dbo.TbWBS AS WBS
			ON WBS.WBS = CTE_TROU.wbs
		WHERE WBS.WBS IS NULL
		ORDER BY CTE_TROU.indice
 
		IF @jeton IS NULL
		BEGIN			
			INSERT INTO dbo.TbWBS
			(
				WBS
			)
			SELECT @_wbs + '.' + @newWBS
		END
		ELSE
		BEGIN		
			INSERT INTO dbo.TbWBS
			(
				WBS
			)
			SELECT @jeton
		END
	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

Citation:

Code:

RIGHT(actWBS, 1)

Vous voulez dire le "act" ? je ne sais pas d'où cela provient, je viens de regarder mes précédents posts et je n'ai pas trouvé ...
En tout état de cause, changez le par @.

@++ ;)

05/11/2009, 22h05
Erakis
Désolé, le actWBS est le vrai nom de la colonne dans la table où je réalise mes tests. La table en question est beaucoup plus complexe et liée à plusieurs autres tables.

Je vois que vous avez déjà trouvé la solution à la question que je vous ai posée précédemment.
Code:

1 2 SELECT @newWBS = CAST(MAX(CAST(RIGHT(WBS, CHARINDEX('.', REVERSE(WBS)) - 1) AS TINYINT)) + 1 AS VARCHAR(40))
Vous faites dans le 'assez' rapide. Je suis impressionné !
Je ferai quelques derniers tests ce soir, mais je crois que tout est correct à présent. Un vrai chef d'oeuvre :ccool:

Merci
05/11/2009, 22h09
elsuket

le challenge m'a plu ;)

J'ai fait tous mes tests avec la table que je vous ai proposé dans ma seconde réponse ;)

@++ ;)

Bonjour elsuket,

J'ai finalement eu le temps de mettre à l'épreuve votre requête de génération de wbs. Cependant, le résultat est malheureusement innexacte :(

Voilà la table du dépars mise à jour avec de nouveaux troues :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 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.3' ) INSERT INTO TbWBS VALUES( '1.1.5' ) INSERT INTO TbWBS VALUES( '1.1.7' ) INSERT INTO TbWBS VALUES( '1.2' ) INSERT INTO TbWBS VALUES( '2' ) INSERT INTO TbWBS VALUES( '4' )
Maintenant, j'aimerais ajouter un WBS sous l'activitée "1.1".

Si l'on considère que dans la table, la numérotation commence déjà à "1.1.3", alors il y a "1.1.1", "1.1.2" qui sont libres. Suivis du "1.1.4" et "1.1.6" qui sont aussi libre. Alors, avant de géréré le "1.1.8", ceux-ci devraient être utilisés.

Donc si j''appel la procédure comme ceci :
(Ce qui signifie que je veux créer un WBS sous le wbs "1.1")
Code:

1 2 EXECUTE PsManageWBS '1.1'
J'ai "1.1.8" comme résultat, mais j'aurais dû avoir "1.1.1".

Bref, la fonction doit trouver entre le MIN et le MAX de la numérotation actuel, s'il existe des troues. Si oui, alors elle retourne le plus petit des troues. S'il non, elle ajoute + 1 au MAX.

Êtes-vous encore intéresser par le défi ?:D

Ce n'est pas obligatoire, mais j'aimerais éviter l'utilisation de la commande "WITH", car ma base de données devra être migrée vers un SGBD qui ne la supporte pas encore. Toutefois, vous pouvez quand meme l'utilisé et j'essaierai de convertir le bout de code en utilisant une variable table.

Merci beaucoup.

Si vous êtes sous SQL Server 2008 ceci est fait automatiquement à l'aide du type HierarchyId.

Exemple :

Code:

1
2
3
4
5
DECLARE @TABLE TABLE
(
     WBS_ID INT IDENTITY PRIMARY KEY,
     WBS hierarchyid
)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
DECLARE @node hierarchyid
-- insertion racine
INSERT INTO @TABLE VALUES (hierarchyid::GetRoot())
-- insertion de fils, petit fils et arrière petits fils de racine
INSERT INTO @TABLE SELECT WBS.GetDescendant(NULL, NULL) FROM @TABLE WHERE  WBS_ID = 1
INSERT INTO @TABLE SELECT WBS.GetDescendant(NULL, NULL) FROM @TABLE WHERE  WBS_ID = 2
INSERT INTO @TABLE SELECT WBS.GetDescendant(NULL, NULL) FROM @TABLE WHERE  WBS_ID = 3
-- insertion d'un frère de racine
SELECT @node = WBS FROM @TABLE WHERE  WBS_ID = 2
INSERT INTO @TABLE SELECT WBS.GetDescendant(@node, NULL) FROM @TABLE WHERE  WBS_ID = 1
-- insertion d'un autre frère de racine
SELECT @node = WBS FROM @TABLE WHERE  WBS_ID = 5
INSERT INTO @TABLE SELECT WBS.GetDescendant(@node, NULL) FROM @TABLE WHERE  WBS_ID = 1

Code:

1
2
3
4
5
6
7
8
9
10
11
12
-- résultat :
SELECT WBS_ID, WBS, CAST(WBS AS VARCHAR(32)) WBS_CLEAR
FROM @TABLE
 
WBS_ID      WBS            WBS_CLEAR
----------- -------------- --------------------------------
1           0x             /
2           0x58           /1/
3           0x5AC0         /1/1/
4           0x5AD6         /1/1/1/
5           0x68           /2/
6           0x78           /3/

A +

21/11/2009, 21h43
Erakis

Malhereusement, je n'utilise pas SQL Server 2008 et même que ce projet sera probablement migré vers un autre SGBD gratuit. Peut-être Firebird ou MySQL.

Donc, j'aimerais que la solution soit le plus général possible.

Toutefois, merci de m'avoir fait connaître ce nouveau type, c'est génial !
23/11/2009, 17h19
Erakis

Bonjour à tous,

Finalement, à partir de l'exemple de elsuket, j'ai trouvé la solution. Encore merci.
24/11/2009, 11h01
elsuket

Ce serait bien de la partager ...

@++ ;)
24/11/2009, 14h40
Erakis

En fait, c'est exactement la solution que vous m'aviez donnée ici.

Toutefois, dans ma solution finale (basé sur la votre), il y a plusieurs autres tables impliquées dans le processus. La procédure stockée finale tient à présent sur 4 pages et le code supplémentaire ne fait pas vraiment partie du problème posé.

Donc, après plusieurs tests supplémentaires, je me suis rendu compte que votre solution fontionnait à merveille et c'était exactement ce que j'avais besion.

Encore une fois *MERCI* elsuket :ccool:
24/11/2009, 14h43
elsuket

Avec plaisir et à bientôt sur le forum ;)

@++ ;)