These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

Forum Index

EVE Forums » EVE Technology and Research Center » EVE Technology Lab » [SOLVED] EVE Database: how to get prereq skill tree for...

Topic is locked indefinitely.

[SOLVED] EVE Database: how to get prereq skill tree for typeID?
Author

Previous Topic Next Topic

Sable Blitzmann

24th Imperial Crusade

Amarr Empire

Likes received: 254

#1 - 2011-10-12 19:26:45 UTC | Edited by: Sable Blitzmann

Hello all,

I'm trying to put together a SQL query that will give me all the skill prereqs for any item. I'm not really sure how to got about doing this...

I have it partially working: it gives me the immediate skills required (primary, secondary, etc), but not the prereqs of those skills. I guess I can loop through the results and get the prereqs for them recursively, but I'd rather do it all in one query if possible.

This is what I have so far (to pull the prereqs for the Torpedo skill):
SELECT * FROM dgmtypeattributes WHERE typeID = 3325 AND ((attributeID > 181 AND attributeID < 185) OR (attributeID > 276 AND attributeID < 280))

which results in:
+--------+-------------+----------+------------+
| typeID | attributeID | valueInt | valueFloat |
+--------+-------------+----------+------------+
| 3325 | 182 | 3319 | NULL |
| 3325 | 183 | 3324 | NULL |
| 3325 | 277 | 4 | NULL |
| 3325 | 278 | 3 | NULL |
+--------+-------------+----------+------------+

Two problems with it:
1) I would like to get all the skills, not just Missile Launcher Op and Heavy Missiles. For example, Heavy Missiles also has prereqs, and I would like those included in the result
2) The level required for the skills (4 and 3 in the results). Is there anyway to include them on the row with the skills, so as to look like this:
+--------+-------------+----------+------------+------------+
| typeID | attributeID | valueInt | level | valueFloat |
+--------+-------------+----------+------------+------------+
| 3325 | 182 | 3319 | 4 | NULL |
| 3325 | 183 | 3324 | 3 | NULL |
+--------+-------------+----------+------------+

Any help would be much appreciated. =)

655
Desmont McCallock Likes received: 655	#2 - 2011-10-12 19:35:13 UTC https://forums.eveonline.com/default.aspx?g=posts&m=49803#post49803

Steve Ronuken

Fuzzwork Enterprises

Vote Steve Ronuken for CSM

Likes received: 6,759

#3 - 2011-10-12 19:35:49 UTC

Unfortunately, it's a recursive or hierarchical query that you need. Which mysql doesn't natively support. Oracle does, but the express edition's a trifle heavy in comparison. Though I'm seriously considering getting a local copy for this kind of thing.

What I'd suggest is trying it out with a custom function to do the recursive bits.

Take a look at http://explainextended.com/2009/03/17/hierarchical-queries-in-mysql/ which might show you what you need to do.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter


Steve Ronuken Fuzzwork Enterprises Vote Steve Ronuken for CSM Likes received: 6,759	#4 - 2011-10-12 19:38:50 UTC Desmont McCallock wrote: https://forums.eveonline.com/default.aspx?g=posts&m=49803#post49803 Might be that you've already seen it, but you can probably use COALESCE() to deal with the whole valueint and valuefloat issue. It returns the first value that's not null, from the values you hand in. Woo! CSM XI! Fuzzwork Enterprises Twitter: @fuzzysteve on Twitter

Sable Blitzmann

24th Imperial Crusade

Amarr Empire

Likes received: 254

#5 - 2011-10-12 19:40:21 UTC

Steve Ronuken wrote:

Desmont McCallock wrote:

https://forums.eveonline.com/default.aspx?g=posts&m=49803#post49803

Might be that you've already seen it, but you can probably use COALESCE() to deal with the whole valueint and valuefloat issue. It returns the first value that's not null, from the values you hand in.

Actually, I use IFNULL(valueInt,valueFloat) AS value. =)

Thanks for the info guys, I'll look into both of those solutions

Desmont McCallock

Likes received: 655

#6 - 2011-10-12 19:53:07 UTC | Edited by: Desmont McCallock

Of course the syntax can be improved. I don't expect any credits for my SQL syntax skills. Actually I'm a novice in that. Have much more to learn.

Edit: ~~I have updated the query using ISNULL() (MSSQL Syntax). For MySQL syntax use IFNULL() instead.~~
Edit 2: Looks like COALESCE() works for both MSSQL and MySQL, so query has been updated to use that.

Sable Blitzmann

24th Imperial Crusade

Amarr Empire

Likes received: 254

#7 - 2011-10-12 20:00:59 UTC

Desmont McCallock wrote:

Of course the syntax can be improved. I don't expect any credits for my SQL syntax skills. Actually I'm a novice in that. Have much more to learn.

Heh, same can be said for me.

I don't think your query is what I'm looking for tho. It does show how to do problem 2), but doesn't show the prereqs of the prereqs. I'll continue looking into things, but I think a stored procedure is the way to go (that is, if I don't just simply loop it via PHP and do a few extra queries =P)

I'll post any solution I come up with.

Desmont McCallock

Likes received: 655

#8 - 2011-10-12 20:25:28 UTC

Sable Blitzmann wrote:

Desmont McCallock wrote:

Of course the syntax can be improved. I don't expect any credits for my SQL syntax skills. Actually I'm a novice in that. Have much more to learn.

In your shoes I would create two queries for each problem and then link the queries.
Creating one for both will end up messy. Just my 0.02 cents.

Sable Blitzmann

24th Imperial Crusade

Amarr Empire

Likes received: 254

#9 - 2011-10-14 22:19:07 UTC

Seemed to have figured it out with the extreme help from the guy who wrote that article on hierarchical queries linked above.

Solution can be found here: http://stackoverflow.com/questions/7746667/how-to-do-this-mysql-stored-procedure-for-hierarchical-query

This is the query that I'm currently using (finding the prereqs for the Minmatar Carrier skillbook):

Quote:

SELECT output.*, invtypes.typeName
FROM(
SELECT test2(dgmtypeattributes.typeID) AS id, @level AS level, @parent AS parent, @path AS path
FROM (
SELECT
@start_with := 24314,
@id := @start_with,
@level := 0,
@parent := 0,
@path := ''
) vars, dgmtypeattributes
WHERE @id IS NOT NULL
) output
INNER JOIN invtypes ON output.id = invtypes.typeID

And the result:

Quote:

+-------+-------+--------+-----------------------+----------------------------+
| id | level | parent | path | typeName |
+-------+-------+--------+-----------------------+----------------------------+
| 3337 | 1 | 24314 | ,24314 | Minmatar Battleship |
| 3327 | 2 | 3337 | ,24314,3337 | Spaceship Command |
| 3333 | 2 | 3337 | ,24314,3337 | Minmatar Cruiser |
| 3327 | 3 | 3333 | ,24314,3337,3333 | Spaceship Command |
| 3329 | 3 | 3333 | ,24314,3337,3333 | Minmatar Frigate |
| 3327 | 4 | 3329 | ,24314,3337,3333,3329 | Spaceship Command |
| 3442 | 1 | 24314 | ,24314 | Drone Interfacing |
| 3436 | 2 | 3442 | ,24314,3442 | Drones |
| 20533 | 1 | 24314 | ,24314 | Capital Ships |
| 20342 | 2 | 20533 | ,24314,20533 | Advanced Spaceship Command |
| 3327 | 3 | 20342 | ,24314,20533,20342 | Spaceship Command |
+-------+-------+--------+-----------------------+----------------------------+

There's still a few quirks here and there. Most notably for some newer ships/items that have more than 3 immediate prereqs. Such items include the t3 cruiser skillbooks, which have 6 immediate subsystem skills (and thus don't follow the 812, 813, and 814 attribute id rule to the tee). Should be minor fixes from here, and I'll look into them more after a few hours of rest. ^_^ I'll post any interesting results I may find here and update the StackOverflow post to mirror any updates.

Leska Helios

Imperial Academy

Amarr Empire

Likes received: 0

#10 - 2012-01-09 14:42:16 UTC

Could you post the corresponding function test2 pls? As it seems the one you posted on stackoverflow doesnt work too well with this latest query you posted here. It always runs into a infinity and kills my server it seems. The shorter one you posted on stackoverflow seems to work fine though, so i guess you must have made changes to the function for that final SQL...

Would be awesome as this would be exactly what i could use atm...

Leska Helios

Imperial Academy

Amarr Empire

Likes received: 0

#11 - 2012-01-09 16:32:38 UTC | Edited by: Leska Helios

Nvm was a small mistake on my end. Now it works fine. Many thanks as this is a great help.

PS.: if you ever got around to updating it and stuff i would be interested in those updates.

Anyway thx alot

EDIT: I modfied it to also return the required level:

Quote:

CREATE FUNCTION test(value INT) RETURNS INT
NOT DETERMINISTIC
READS SQL DATA
BEGIN

DECLARE _id INT;
DECLARE _parent INT;
DECLARE _attid 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(valueInt) AS id, IF(MIN(valueInt), CONCAT(@path, ',', _parent), @path), attributeID
INTO @id, @path, _attid
FROM `dgmTypeAttributes`
WHERE
typeID = _parent
AND attributeID > 181
AND attributeID < 185
AND valueInt > _id;

SET _attid = _attid +95;

SELECT
MIN(valueInt) AS req
INTO @req
FROM `dgmTypeAttributes`
WHERE
typeID = _parent
AND attributeID = _attid;

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//

Quote:

SELECT output.*, invTypes.typeName
FROM(
SELECT test(dgmTypeAttributes.typeID) AS id, @level AS level, @parent AS parent, @path AS path, @req AS req
FROM (
SELECT
@start_with := 642,
@id := @start_with,
@level := 0,
@parent := 0,
@path := '',
@req := ''
) vars, dgmTypeAttributes
WHERE @id IS NOT NULL
) output
INNER JOIN invTypes ON output.id = invTypes.typeID