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.
 

[QUESTION] - EVE DB dump and SQL statements
Author
Previous Topic Next Topic
Capt'n Crunnch
Skrillix
#1 - 2014-01-02 11:37:52 UTC
Hi there!

I am working on a small project for which I need to extract data from the EVE DB dump. So far I did this manually by exporting certain tables into csv and then work through the data that I need via Excel. I realize that this is not very efficient (and also I was not able to get all the data that I need).

The data I am looking for is the following:
- Item ID
- Item Name (e.g. Gyrostabilizer II)
- Item Category (e.g. Ammo, Implant, Module, Rig, etc.)
- Meta Level

For some of you this might seem like a very simple task, however I am struggling, so I was hoping you might be able to help me understand the SQL statements needed to extract the data I want directly via MySql (or a similar program).

Many thanks in advance and all the best,
Capt'n
Hel O'Ween
Men On A Mission
#2 - 2014-01-02 12:35:07 UTC
This is a modification of this posting:


SELECT invTypes.typeID AS ID,
invTypes.groupID AS ID_Group,
invTypes.typeName AS Name,
-- invTypes.[description] AS [Description],
(SELECT
COALESCE(dgmTypeAttributes.valueFloat , dgmTypeAttributes.valueInt)
FROM
dgmTypeAttributes
WHERE
dgmTypeAttributes.typeID = invTypes.typeID
AND
dgmTypeAttributes.attributeID = 633
) AS MetaLevel,
(SELECT
invGroups.groupName
FROM
invGroups
WHERE
invGroups.groupID = invTypes.groupID
) AS [Group Name]
FROM
invTypes
WHERE
invTypes.published = 1
AND
invTypes.typeID = 16359 -- Prototpe Armor EM Hardener I

Returns:
ID ID_Group Name MetaLevel Group Name
16359 328 Prototype Armor EM Hardener I 4 Armor Hardener

EVEWalletAware - an offline wallet manager.
Dimitrie
Dixie Chicks
Crazy Chicks
#3 - 2014-01-02 12:39:05 UTC
SELECT
`invTypes`.`typeID`,
`invTypes`.`typeName`,
`invCategories`.`categoryName`,
ifnull(`dgmTypeAttributes`.`valueInt`, `dgmTypeAttributes`.`valueFloat`) as metaLevel
FROM
`invTypes`
LEFT JOIN `invGroups` ON `invTypes`.`groupID` = `invGroups`.`groupID`
LEFT JOIN `invCategories` ON `invGroups`.`categoryID` =
`invCategories`.`categoryID`
LEFT JOIN `dgmTypeAttributes` ON `invTypes`.`typeID` =
`dgmTypeAttributes`.`typeID` AND `dgmTypeAttributes`.`attributeID` = 633;
Rob Crowley
State War Academy
#4 - 2014-01-02 12:40:27 UTC
This query is for MSSQL (the format in which the SDE is released) but it would look very similar in MySQL.

select it.typeID, it.typeName, ig.groupName, ic.categoryName, coalesce(ta.valueFloat,ta.valueInt) As value from invTypes it join invGroups ig on it.groupID=ig.groupID join invCategories ic on ic.categoryID=ig.categoryID join dgmTypeAttributes ta on it.typeID=ta.typeID where ta.attributeID=633

I included group names cause the categories are very broad, e.g. rigs just fall into the module category. AttributeID 633 is the meta level attribute which can be in valueFloat or valueInt of dgmTypeAttributes, that's why the coalesce function is used to basically combine those 2 fields.
Capt'n Crunnch
Skrillix
#5 - 2014-01-03 11:05:39 UTC
Thanks to all of you!! This is extremely helpful and does exactly what I wanted to achieve.

For anyone else who might look at the same issue, this is what worked for me:

Quote:
SELECT
`invTypes`.`typeID`,
`invTypes`.`typeName`,
`invCategories`.`categoryName`,
`invGroups`.`groupName`,
ifnull(`dgmTypeAttributes`.`valueInt`, `dgmTypeAttributes`.`valueFloat`) as metaLevel
FROM
`invTypes`
LEFT JOIN `invGroups` ON `invTypes`.`groupID` = `invGroups`.`groupID`
LEFT JOIN `invCategories` ON `invGroups`.`categoryID` =
`invCategories`.`categoryID`
LEFT JOIN `dgmTypeAttributes` ON `invTypes`.`typeID` =
`dgmTypeAttributes`.`typeID` AND `dgmTypeAttributes`.`attributeID` = 633
Capt'n Crunnch
Skrillix
#6 - 2014-02-01 00:20:19 UTC
Adding this will filter out most of the unneeded junk:

Quote:
WHERE `invCategories`.`categoryName` <> "#System" AND
`invCategories`.`categoryName` <> "Abstract" AND
`invCategories`.`categoryName` <> "Accessories" AND
`invCategories`.`categoryName` <> "Apparel" AND
`invCategories`.`categoryName` <> "Bonus" AND
`invCategories`.`categoryName` <> "Celestial" AND
`invCategories`.`categoryName` <> "Cells" AND
`invCategories`.`categoryName` <> "Effects" AND
`invCategories`.`categoryName` <> "Entity" AND
`invCategories`.`categoryName` <> "Infantry" AND
`invCategories`.`categoryName` <> "Lights" AND
`invCategories`.`categoryName` <> "Orbitals" AND
`invCategories`.`categoryName` <> "Owner" AND
`invCategories`.`categoryName` <> "Placeables" AND
`invCategories`.`categoryName` <> "Sovereignty Structures" AND
`invCategories`.`categoryName` <> "Special Edition Assets" AND
`invCategories`.`categoryName` <> "Station" AND
`invCategories`.`categoryName` <> "World Space" AND
`invGroups`.`groupName` <> "Money" AND
`invGroups`.`groupName` <> "Capsule" AND
`invGroups`.`groupName` <> "Acceleration Gate Keys" AND
`invGroups`.`groupName` <> "Commodities" AND
`invGroups`.`groupName` <> "Identification" AND
`invGroups`.`groupName` <> "Lease" AND
`invGroups`.`groupName` <> "Livestock" AND
`invGroups`.`groupName` <> "Miscellaneous" AND
`invGroups`.`groupName` <> "Obsolete Books" AND
`invGroups`.`groupName` <> "QA Module" AND
`invGroups`.`groupName` <> "Rookie ship" AND
`invGroups`.`groupName` <> "Ship Logs" AND
`invGroups`.`groupName` <> "Shuttle" AND
`invGroups`.`groupName` <> "Shuttle Blueprint" AND
`invGroups`.`groupName` <> "Signature Scrambling" AND
`invGroups`.`groupName` <> "Spaceports" AND
`invGroups`.`groupName` <> "Special Edition Commodity Blueprints" AND
`invGroups`.`groupName` <> "Trade Session" AND
`invGroups`.`groupName` <> "Trading" AND
`invGroups`.`groupName` <> "Unanchoring Drone" AND
`invGroups`.`groupName` <> "WorldSpace" AND
`invGroups`.`groupName` <> ""
Desmont McCallock
#7 - 2014-02-01 07:59:11 UTC  |  Edited by: Desmont McCallock
Make you query sorter by using the "IN" statement e.g. WHERE `invCategories`.`categoryID` IN (Id1,Id2,Id3,...) AND `invGroups`.`groupID` IN (Id1,Id2,Id3,...) instead of excluding names.