Prerequisite Skilltree

From EVEDev

I had a need to find all skills that needed to be trained for various items and other skills, but I didn't want to go the easy way and do PHP loops to find the child skills for each prereq and build the tree like that. Instead, I created a stored function that did it automatically. Please note that this has only been tested on MySQL v5.5. Oracle has native support for hierarchical queries, so this isn't needed.

CREATE FUNCTION prereqs(value INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
	DECLARE _id INT;
	DECLARE _parent INT;
	DECLARE _next INT;
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET @id = NULL;

	SET _parent = @id;
	SET _id = -1;
	
	IF @id IS NULL THEN
			RETURN NULL;
	END IF;
	
	LOOP
		SELECT 
			MIN(COALESCE(valueFloat, valueInt)) AS id, IF(MIN(COALESCE(valueFloat, valueInt)), CONCAT(@path, ',', _parent), @path)
		INTO @id, @path
		FROM `dgmtypeattributes` 
		WHERE 
			typeID = _parent AND attributeID IN (182,183,184,1285,1289,1290)
			AND COALESCE(valueFloat, valueInt)	> _id;

			IF @id IS NOT NULL OR _parent = @start_with THEN
				SET @level = @level + 1;
				SET @parent = _parent;
				RETURN @id;
			END IF;

			IF @path = '' THEN
				RETURN NULL;
			END IF;

			SET @level := @level - 1;

			SELECT  _parent, SUBSTRING_INDEX(@path, ',', -1), SUBSTRING(@path, 1, (LENGTH(@path)-(LENGTH(SUBSTRING_INDEX(@path, ',', -1)) +1)))
			INTO    _id, _parent, @path;
	END LOOP;
END

Use (get all prereq skills for the minmatar titan, typeID=23773):

SELECT CONCAT(REPEAT('    ', treeLevel - 1), CAST(invtypes.typeName AS CHAR)) AS skill, COALESCE(skillLevel.valueFloat, skillLevel.valueInt) AS requiredLevel, output.*
FROM(
SELECT prereqs(dgmtypeattributes.typeID) AS id, @level AS treeLevel, @parent AS parent, SUBSTR(@path,2) AS path
FROM    (
    SELECT  
        @start_with := 23773,
        @id := @start_with,
        @level := 0,
    @parent := 0, 
    @path := ''
) vars, dgmtypeattributes

WHERE @id IS NOT NULL
) output
INNER JOIN invtypes ON output.id = invtypes.typeID
INNER JOIN dgmtypeattributes AS attr ON attr.typeID = output.parent AND attr.attributeID IN (182,183,184,1285,1289,1290) AND COALESCE(attr.valueFloat, attr.valueInt) = output.id
INNER JOIN dgmtypeattributes AS skillLevel ON skillLevel.typeID = output.parent AND skillLevel.attributeID IN (277,278,279,1286,1287,1288)
WHERE
(
	(attr.attributeID = 182 AND skillLevel.attributeID = 277) OR
	(attr.attributeID = 183 AND skillLevel.attributeID = 278) OR
	(attr.attributeID = 184 AND skillLevel.attributeID = 279) OR
	(attr.attributeID = 1285 AND skillLevel.attributeID = 1286) OR
	(attr.attributeID = 1289 AND skillLevel.attributeID = 1287) OR
	(attr.attributeID = 1290 AND skillLevel.attributeID = 1288)
)

Result:

+------------------------------------+---------------+-------+-----------+--------+---------------------------+
| skill                              | requiredLevel | id    | treeLevel | parent | path                      |
+------------------------------------+---------------+-------+-----------+--------+---------------------------+
| Minmatar Titan                     |             1 |  3345 |         1 |  23773 | 23773                     |
|     Minmatar Battleship            |             5 |  3337 |         2 |   3345 | 23773,3345                |
|         Spaceship Command          |             4 |  3327 |         3 |   3337 | 23773,3345,3337           |
|         Minmatar Cruiser           |             4 |  3333 |         3 |   3337 | 23773,3345,3337           |
|             Spaceship Command      |             3 |  3327 |         4 |   3333 | 23773,3345,3337,3333      |
|             Minmatar Frigate       |             4 |  3329 |         4 |   3333 | 23773,3345,3337,3333      |
|                 Spaceship Command  |             1 |  3327 |         5 |   3329 | 23773,3345,3337,3333,3329 |
|     Leadership                     |             5 |  3348 |         2 |   3345 | 23773,3345                |
|     Capital Ships                  |             5 | 20533 |         2 |   3345 | 23773,3345                |
|         Advanced Spaceship Command |             5 | 20342 |         3 |  20533 | 23773,3345,20533          |
|             Spaceship Command      |             5 |  3327 |         4 |  20342 | 23773,3345,20533,20342    |
| Jump Drive Operation               |             1 |  3456 |         1 |  23773 | 23773                     |
|     Science                        |             5 |  3402 |         2 |   3456 | 23773,3456                |
|     Navigation                     |             5 |  3449 |         2 |   3456 | 23773,3456                |
|     Warp Drive Operation           |             5 |  3455 |         2 |   3456 | 23773,3456                |
|         Navigation                 |             1 |  3449 |         3 |   3455 | 23773,3456,3455           |
| Capital Ships                      |             5 | 20533 |         1 |  23773 | 23773                     |
|     Advanced Spaceship Command     |             5 | 20342 |         2 |  20533 | 23773,20533               |
|         Spaceship Command          |             5 |  3327 |         3 |  20342 | 23773,20533,20342         |
+------------------------------------+---------------+-------+-----------+--------+---------------------------+
19 rows in set (0.11 sec)

There may still be a few minor issues, but it works for the most part. ^_^ I'll update this page in the coming days to fix minor issues/clarify