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

 
  • Topic is locked indefinitely.
 

My limited knowledge of the dump structure...
Author
Previous Topic Next Topic
Nathan WAKE
Deep Core Mining Inc.
Caldari State
#1 - 2011-10-13 13:21:30 UTC
Hello again ,

I'm getting "strange results" with my queries, and I think it is because I don't know enough about the dump structure and where to find the information.

For example, this query returns a list of 256 ships (invgroups.categoryID = 6) available (invtypes.published = 1), but the techlevels are strange :

Quote:

SELECT
invgroups.categoryID,
invgroups.groupID,
invtypes.typeID,
invgroups.groupName,
invtypes.typeName,
invmetatypes.metaGroupID,
invmetagroups.metaGroupName
FROM
invgroups
LEFT JOIN invtypes ON invgroups.groupID = invtypes.groupID
LEFT JOIN invmetatypes ON invmetatypes.typeID = invtypes.typeID
LEFT JOIN invmetagroups ON invmetagroups.metaGroupID = invmetatypes.metaGroupID
WHERE
invgroups.categoryID = 6 AND
invtypes.published = 1


I only get faction, tech 2 and 1 Tech 1 (noctis). Do I have to assume that NULL = 1 and that all the rest of the ships are tech 1 ?

Well then, what about typeIDs 29984, 29986, 29988 and 29990 (the 4 strategic cruisers) that are Tech 3 (metaGroupID 14 and metaGroupName tech 3 in the invmetagroups table) ?

I had a look at the invmetatypes tables, there is no 29984 (tengu) typeID in it. Then, I looked for my first love ship, typeID 24698 (which is the Drake) and guess what ? It's not in there as well !! (big discovery Big smile)

But my query gives some results right ? So that means information regarding ships tech levels are splited into separate tables right ? Or do I have to stop "mysqling" and switch back to gardening as a hobby (please, don't answer yes) ?

To make it short, I am not looking for someone to write me the correct query (well, I'm not asking at least Big smile), but I remember I saw a pdf file displaying all the relationships between tables in the dump. Can't find it back....

Maybe that could help me sort my "little mystery", find my tech levels back and make sure once and for all that the drake is definitly a tech 1 ship.

Thanks


Nathan

"I'm a very good housekeeper. Each time I get a divorce, I keep the house"

Zaza Gabor
Lutz Major
Austriae Est Imperare Orbi Universo
#2 - 2011-10-13 14:46:28 UTC
techLevel != metaLevel Big smile
Trenker
#3 - 2011-10-13 15:50:13 UTC
Small hint:

SELECT *
FROM `dgmattributetypes`
WHERE `attributeName` = 'techLevel'
Nathan WAKE
Deep Core Mining Inc.
Caldari State
#4 - 2011-10-13 16:33:06 UTC
Holly crap !! I feel so close to it, and then I can not spit it !!

Why in hell is the techlevel for the Loki in valueFloat and the techlevel of the Mackinaw in valueInt ?

And why are both those fields empty for the Omen Navy Issue and I have to go to invemetatypes.metagroupID to find the 4/Faction info ?

That sure is done on purpose to loose beginners like me Big smile

Now, that said, this is what I end up with :

Quote:

SELECT
invtypes.typeID,
invtypes.typeName,
dgmtypeattributes.valueFloat,
dgmtypeattributes.valueInt,
invmetatypes.metaGroupID,
invmetagroups.metaGroupName
FROM
invtypes
LEFT JOIN invgroups ON invgroups.groupID = invtypes.groupID
LEFT JOIN dgmtypeattributes ON dgmtypeattributes.typeID = invtypes.typeID
LEFT JOIN dgmattributetypes ON dgmattributetypes.attributeID = dgmtypeattributes.attributeID
LEFT JOIN invmetatypes ON invmetatypes.typeID = invtypes.typeID
LEFT JOIN invmetagroups ON invmetagroups.metaGroupID = invmetatypes.metaGroupID
WHERE
invgroups.categoryID = 6 AND
invtypes.published = 1 AND
dgmtypeattributes.attributeID = 422
ORDER BY typeName


And I am down to 179 ships instead of 256. I'm sorry, but... I should go back gardening I think. Oops

By the way, forgive my english, I'm not native ;-)

"I'm a very good housekeeper. Each time I get a divorce, I keep the house"

Zaza Gabor
Lutz Major
Austriae Est Imperare Orbi Universo
#5 - 2011-10-13 17:31:23 UTC  |  Edited by: Lutz Major
Nathan WAKE wrote:
And I am down to 179 ships instead of 256. I'm sorry, but... I should go back gardening I think. Oops

Yes, because you selected only those ships, that have an attribute with the ID = 422

try

...
LEFT JOIN dgmtypeattributes ON dgmtypeattributes.typeID = invtypes.typeID AND dgmtypeattributes.attributeID = 422
LEFT JOIN dgmattributetypes ON dgmattributetypes.attributeID = dgmtypeattributes.attributeID <--- unnecessary
...
WHERE
...
dgmtypeattributes.attributeID = 422


edit: Oh ... and if you do your gardening like SQL ... poor mother nature *LOL* ;-)
Nathan WAKE
Deep Core Mining Inc.
Caldari State
#6 - 2011-10-13 20:46:47 UTC
Hey Lutz and Trenker. Thanks for your help (again and again).

Listen... I've been breaking my head over that problem for nearly a day now, and I am still not satisfied with what I get, so finally, I ask What?

What would be your SQL request if you wanted to extract the full 256 ships with their respective tech levels ?

I don't even dare to post mine anymore Lol

I have the full list, but lots of ships have no techlevel mentioned at all, whatever the way I turn my request.

Thanks in advance for your time


Nathan

"I'm a very good housekeeper. Each time I get a divorce, I keep the house"

Zaza Gabor
TorTorden
Tors shibari party
#7 - 2011-10-14 22:43:04 UTC  |  Edited by: TorTorden
Edit: NVM. reading for the win

I would have guessed something like this?

Quote:
select t.typeid,t.typename,g.groupname,ifnull(dta.valueint,dta.valuefloat) AS 'Tech level' from invtypes as t
left join invgroups as g
ON t.groupid=g.groupid
left join dgmtypeattributes as dta
ON t.typeid=dta.typeid
where g.categoryid=6
AND dta.attributeid=422


But this also gives me only 175 results, even common ships like the Abaddon is missing.

An ugly way around could also be to look in the invblueprinttypes table.
Still doesn't return all 256 values but I do get 246 Straight
Quote:
SELECT
t1.typename as 'blueprint'
,t2.typename as 'product'
,b.techlevel
FROM invblueprinttypes AS b
LEFT JOIN invtypes AS t1
on b.blueprinttypeid=t1.typeid
LEFT JOIN invtypes AS t2
ON b.producttypeid=t2.typeid
LEFT JOIN invgroups AS g
ON t2.groupid=g.groupid
WHERE g.categoryid=6
AND t2.published=1