Working with Eve Static DataPosted: 2012-04-13 Filed under: eveonline, industry | Tags: api, drake, eve, itemID, mysql, PI, sql, typeID 14 Comments
More Power to the People
The propose of this short guide is to empower a person that has limited or no experience working with the Eve dataset to perform queries and extract useful information.
Eve has two main data sets. The first is the static data that is published by CCP and updated with each expansion. This set contains information that doesn’t change such as stargate coordinates, number of Moons in VFK-IV, type of research agents in the Forge, or the minerals requirements for a Drake.
The other set is the dynamic data, which is exposed through the API. This data contains the types of assets you own, wallet transactions, or kill statics for a particular system — things that change and things that go boom.
In this post I won’t be covering the dymanic data, as the Eve Development Network as an excellent introduction guide and full list of everything exposed.
More Data, More Problems
With every new release, CCP publishes a Microsoft SQL database backup file of their static data. You can find the most up to date file on the Community Toolkit website.
Along with the database backup, you will find other useful downloads such as images for every item in Eve — very important when trying to create something presentable out of boring database tables if you are designing a 3rd Party application.
Some people do not use Microsoft SQL and opt to run their database with MySQL, the most popular opensource database package. This format is not released by CCP and thus enterprising players routinely convert the data from Microsoft SQL to MySQL format. Nothing is lost, just converted.
Usually a few hours after a new file has been posted on the Community Toolkit site, people have conversions up for download. If you hang out in the Eve Technology Lab sub-forum, people will usually post links once available. I have been getting my releases from this site: http://zofu.no-ip.de/.
Noise. has written an excellent guide on how to install the free version of Microsoft SQL Server and work with CCP’s database backup file in order to get to a point where you can run a database query.
Table Prefix Nomenclature
The naming format of the tables is very intuitive and can be picked up fairly quickly.
|agt||all about agents both mission and research|
|chr||character information such as bloodlines, factions, and races|
|crp||NPC corporation divisions|
|dmg||damage attributes, effects, dogma (attribute system)|
|eve||basic information for icons and units|
|inv||market groups, item details, item reactions. I do a lot of work with these tables|
|map||xyz data, landmarks, regions, solar systems connections, and general universe data|
|planet||planetary interaction schematics|
|ram||industry, manufacturing, refine|
|sta||station services, xyz docking locations|
Tips and Tricks
An item’s typeID is a very important thing to know. This is a unique number that identifies a type of object in Eve.
Keep in mind that referring to a Drake (typeID 24698 in the static dataset) will be different than referring to your Drake (unique itemID 1002615059065 in the API feed). The itemID value is a unique number that identifies every object in Eve (1).
Eve Queries 101
SELECT * FROM [dbo].[invTypes] WHERE typeID = 24698 GO
List all Battlecruisers
SELECT * FROM [dbo].[invTypes] WHERE groupID = 419 GO
Eve Queries 201
PI Schematics are contained in the planetSchematicsTypeMap self referencing table. This means that the build requirements for P0, P1, P2, and P3 are all contained in one table. The isInput column is used to list build requirements when set to 1.
1. Listing all P0 items using the isInput value set to 0.
SELECT planetSchematicsTypeMap.*, invTypes.typeName FROM planetSchematicsTypeMap JOIN invTypes ON (planetSchematicsTypeMap.typeID = invTypes.typeID) WHERE planetSchematicsTypeMap.isInput = 0 AND invTypes.marketGroupID = 1335 ORDER BY invTypes.typeName
2. Listing items that go into Construction Blocks, a P1 with typeID 3828 and schematicID 74.
SELECT planetSchematicsTypeMap.*, invTypes.typeName FROM planetSchematicsTypeMap, invTypes WHERE planetSchematicsTypeMap.typeID = invTypes.typeID AND schematicID = 74 AND isInput = 1
Eve Queries 301
CCP’s dataset does not contain a table with Tech2 build requirements. We have to create a new table that combines information from a number of tables and then work off the new table.
1. Run this pastebin query once to create your new typeBuildReqs table. If successful, you will get a message like this:
(15791 row(s) affected).
2. Find the build requirements for a Cap Recharge II with input typeID of 2033 (Cap Recharge II Blueprint). The NOT IN statement excludes the skills required to build the item.
SELECT typeBuildReqs.*, invTypes.typeID, invTypes.typeName FROM typeBuildReqs JOIN invTypes ON (invTypes.typeID = typeBuildReqs.requiredTypeID) WHERE typeBuildReqs.typeID = 2033 AND invTypes.groupID NOT IN (268,269,270) AND activityID = 1
If you want some more example queries, comment or find me on twitter.
(1) If you remember that old Devblog post about Drake missile lag, one major component to the lag was that every missile fired had to have a unique itemID created and destroyed after it exploded. This database operation ate up a lot of time, which is critical to a simulation trying to play through a battle.
Mmmmm sexy SQL statements, good job!
crt = Certificates
dgm is a lot more than just damage attributes, and stands for dogma, the attribute system.
war = faction warfare
Other than that, good info!
Thanks for the info. I’ve updated the post.
[…] Posted: April 13, 2012 | Author: Blake | Filed under: eveonline, industry | Tags: api, drake, eve, itemID, mysql, PI, sql, typeID |3 Comments » […]
FYI, I personally find the sqlite dump much easier to work with – no need to import anything, you can work directly with the dump files. You can those dumps at http://zofu.no-ip.de/
Just for giggles: I once wrote a quickie program using the static dump to get a list of the distance (in jumps) of all systems measured from Jita, both safest and shortest, at the behest of a history student doing a class paper about the Gough Map (http://en.wikipedia.org/wiki/Gough_Map) . I believe GIS software was also involved at some point.
How’s that for cross-discipline!
still trying to find a decent market site to intergrate with producing propper pricing calculation, eve-central is good, but the outliers kill the data integraty.
cant wait for the day we can get at least a daily average from ccp for a specified region.
[…] my custom typeBuildReqs table (creation details here in section 301), the following query will take the typeID of the Subsystem blueprint and give you a […]
Hmm… When I run http://pastebin.com/6hypXsrS SQL Query you mentioned on the Inferno_1.2_76477_db datadump I get the following error:
Msg 220, Level 16, State 1, Line 2
Arithmetic overflow error for data type smallint, value = 32810.
The statement has been terminated.
Any ideas on what might be causing it?
Ah – figured it out.
You need to update typeID type definition to bigint
Updated SQL -> http://pastebin.com/2uzKnhU1
[…] Nowadays, I discovered the beauty of SQL together with the EVE data dump, and I am proudly presenting you with the latest analysis, this time including all items. I must admit that spreadsheets were still employed to carry out the sorting and comparison functions, as I am still in the process of learning how to do that directly in SQL. I tell you, if ever you need to learn new software and techniques, do that on the data dump of your favourite MMO! In this regard, a big thank you goes to the authors of this and this post. […]
[…] did a great piece last Sunday on how to set this up. When you marry this up with Blake’s wealth of knowledge over on K162 you can do just about anything. Unfortunately I have to say “for now” […]
[…] I wrote a guide on the basics of the Eve Static Dataset and some sample queries here. […]
Thanks for the guide, its got me started. I’m atempting to develop and iphone industry blueprints application, the one thing I can’t seem to find is the icons. The EveIcon tables have links to the files, the market table has an iconID column, yet the invItems does not have any IconID, so how do you find the iconID for things that are not Market Groups? Just wondering if anyone has any idea