Working with Eve Static Data
Posted: 2012-04-13 Filed under: eveonline, industry | Tags: api, drake, eve, itemID, mysql, PI, sql, typeID 14 CommentsMore 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/.
Install Procedure
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.
Example Tables
| agt | all about agents both mission and research |
| chr | character information such as bloodlines, factions, and races |
| crp | NPC corporation divisions |
| crt | certificates |
| 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 |
| trn | multilingual stuff |
| war | faction warfare |
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
Drake Details
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
Moar!
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.
Inferno Escalation Test Server Changes
Posted: 2012-04-12 Filed under: eveonline, industry, market, ships | Tags: bomber, deadspace, drone region, engine trails, etherium reach, ewar, faction, hud, market, officer, overview, sisi, titan 7 CommentsBuild 362741 has been pushed to the test server, with a branch name of Tranquility. I will attempt to keep a list of changes here so bookmark this post.
Preliminary Size: 306 MB
Launcher update to 1.202
I was prompted for a DirectX install after the Launcher updated.
[Update April 12 13:12]
CCP Goliath non-comprehensive feature list post.
Team Five-0
Security status of drone regions has been rebalanced
Rogue drones no longer drop loot, but have bounties
Incursion site rebalancing
Behind-the-scenes work on aggression flagging, killmails, etc.
Loot-rights UI – container colouring should be more consistent when owners log off/jump/dock
Current iteration of titan changesTeam Gridlock
New file format for objects, with faster loading timesTeam Avatar
Buff bar notification panel implemented beside HUD
Chat channels can now be locally cleared and the MOTD reloaded
Your own market orders can now be highlighted. To enable this, a setting checkbox needs to be checked in the market settings tab. The orders marked as yours have “modify order” option added to their menu
Quickbar entries can have text added to them
Your active ship will no longer show as available in Contracts
Deselect/Select all function added when arranging items in contracts
There is now a “Client” submenu in the transaction logs
A few improvements have been made to the quickbar in the market. Now items can be dropped on the quickbar tab, it’s possible to multiselect items in the quickbar, items in the quickbar can be “copied” over to another folder by CTRL dragging them to the folder, and now asset items can be added to the quickbar.
There is a new hairstyle available for female characters.
The ‘Pose’ icons within character creation and re-customization now display a clearer representation of the actual pose.
The “leaf” groups in the market can now be collapsed like all the other groups.
The most common user interface elements used to represent players can now be dragged over to various edit fields to add the characters name.
‘Select Station’ window for remote market orders now has 2 columns, station name and number jumps, and it’s possible to sort the list by either one of columns
Entries in most table like scrolls can be copied with CTRL+c. If entries are selected, only those will be copied, but if nothing is selected and the focus is on the scroll, all the entries are copied.
Hints that only repeated the text in the scroll entry been removed.
There is now a right click option on the group/category trace in market details which allows you to find that group in the browse tabTeam Superfriends
Market now shows Faction/Officer/Deadspace loot
Confirmed Changes
1. Officer/Deadspace/Faction on Market
2. Stealth Bomber V3 Update
Not on Test server yet.
3. Drone Rat Bounties
Drone Queen 1.85 M
Patriach 1.55 M
Drone Creator 1.4 M
Drone Ruler 1.25 M
Drone Controller 500k
Domination Drone 950k
Spearhead Drone 800k
Swarm Preserver 650k
Exterminator Drone 225k
Viral Infector 80k
Disintegrator 95k
Defeator 185k
Atomizer 110k
Violator 87.5k
Strain Splinter 25.5k
Crippler 195k
Striker Drone 205k
Nuker 117.5k
Siege Drone 215k
Dismantler 22.5k
Marauder 21k
Barracuda 13k
Devilfish 12k
Kalestra Cable link
4. Status Effects Notification Panel
Note: Remote Armor/Shield/Hull/Energy do not show up. Only E-war at this time.
Due to resource constraints we did a minimal implementation that is mean to be iterated on based on your feedback. Currently it is only tracking negative effects directed against you (debuffs). The plan is to include more things if this goes well and include assist modules and possibly even damage dealt to you.
The immediate goal is to make aggression against you visible and help you react to those that do. There is a lot we want to do more and we have scheduled time to iterate on this so please keep the comments coming
CCP Frellicus link
Additionally you can right click on the effect and get options for that pilot.
5. Text Trail-off Shading on Overview
It’s intentional and not… the fading is intentional, but it was not really supposed to go out in Escalation but Inferno and the change has been reverted for now. So soon you will see it change back to the way it’s on TQ.
CCP karkur link
FYI: This topic is being discussed by our UI designers and it seems like the amount of fading will be reduced a bit to avoid reducing the displayed information.
CCP Habakuk link
6. Rumored Drake Rebalance
No change.
7. Missile Launchers
Not on Test server yet.
8. Reload MOTD and Clear options on Chat channels
9. Drone Region Security Status Adjustment
Details in Carebearing 2.0 Devblog.
…worked out on by how close your are to empire, here is a sample of them
Kalestra Cable link
Etherium Reach got hit particularly hard because it was one of the best truesec regions in the game (-0.60 vs -0.64 for The Spire and Perrigen Falls, and just behind Deklein at -0.61, with the next-nearest non-drone region being Branch at -0.51), despite having a direct connection to Empire. It doesn’t have a hisec connection, so it’s more of a Tribute than a Pure Blind, but that’s balanced to some degree by having a connection to both Molden Heath and The Forge. It got the biggest adjustment because its sec values were the most out of line from what you’d expect anywhere else in EVE.
CCP Greyscale link further clarification
10. Engine Trail Consistency
Deimos now has blue trails.
11. Titan Adjustments
Current / Proposed
12. Market Order Highlighting
CCP karkur has confimed that this will be in soon(tm).
“Hmmm… no, the option is not there. It was checked in very recently and was integrated later. You should see it soon :P”
link
Looks like this made it into build 360692:
Looks a little rough. The highlight is a bar around your order. No editing done to the image below, just a crop.
Looks like there will be a change coming:
2012-04-16 Updated:
13. Bug Reporting Bug
…there was a bug in the SSL initialization that made the client lock up for 30 seconds or so. It’s been fixed so now you can bug report like the wind 🙂
CCP Explorer link
14. Contract Select
New buttons for Select All / Deselect All.
15. Rename Quickbar Item
16. Incursion Balancing
Yes, but I need to get more details on this.
Update: Find something new? Comment here or @k162space on twitter.
The ‘Maelstrom Hour’ Unit
Posted: 2012-04-05 Filed under: eveonline | Tags: branch, dominion, hp, maelstrom, sov, tenal 3 CommentsRaiden. has stood down in Tenal and the CFC is currently working through structure grinding in order to conquer it. According to my intel channels, Tenal will be back in RAZOR Alliance‘s hands in 2 weeks.
I find the current Tenal situation reminicent of the CFC Branch campaign. After a few major battles, White Noise. stood down and the only thing left to do was to structure grind. Hundreds of Maelstroms and Supers were used to blob and grind.
In order to express the magnitude of work that needed to be done, a unit of measurement was coined.
Maelstrom Hour – noun.
The amount of damage a T2 gun, perfect skill, and always in optimal Maelstrom produces in an hour.
20 Billion HPs were required to conquer Branch, which comes out to about 7,407 Maelstrom Hours. Given an average fleet size of 100 Maelstroms, that equals 148 hours of fleet time — or over 6 days of non-stop shooting (credit for this post goes to Lake on Kugutsumen.com for the research).
With sovereignty being looked at in the upcoming expansions, there cannot be more higher HP structures. The answer to this mechanic has always been n+1, bring more of the things.
What are the alternatives?
I’m not an expert on Dominion-style sovereignty warfare so any links to constructive threads would be welcome.
Jita Implant Manipulation
Posted: 2012-04-04 Filed under: eveonline, market | Tags: basic, cybernetic, implant, memory, neural, ocular, social, standard 2 CommentsSome trader or group of traders have been driving up the cost of implants in Jita. All 5 implant types (Cybernetic, Memory, Neural, Ocular, and Social) for all three tiers (Basic, Standard, and Improved) have been heavily manipulated since the weekend.
If you look at the price history, it seems that a lot of people have noticed and are trying to hold out. No movement was recorded for April 03 across the board for all 5 implant types AND for all three tiers. Wow, this upward pressure takes some serious capital and diligence.
Across the board, the trend is quite visible. Right now there are only 31 Memory Basic implants on the Jita market at 13.89 M.
I wonder how long this attempt will last as implants are easy to acquire from LP.
Tech 2 BPO Returns
Posted: 2012-03-08 Filed under: eveonline, industry, market | Tags: bpo, invention, t2 16 CommentsStirring the Hornet’s Nest
The allure of Tech2 BPOs is high and the history surrounding them is very controversial. If you don’t believe me, perform a search on the forums and you will feast on delicious tears. Industry people have been forum raging about this topic since the introduction and CCP has not dared to make any adjustments to them.
Short refresher for non-industrialists: T2 items are manufactured from an invented blueprint copy that, based on success, comes from a copy of the T1 BPO + datacores + sometimes a decryptor to modify attributes of the end T2 BPC.
Owning a T2 BPO means you can bypass the invention process and manufacture with solely the T2 BPO that has unlimited runs.
Why the bitching? The majority of forum posts about T2 BPOs complain that the individuals who won them during the lottery phase print ISK.
Yes, they were very lucky and didn’t have to put down multiple billions for the BPO. Yes, they had a monopoly on T2 production. Had. The invention system, which was introduced after the lottery system, allows many people to participate in the T2 process. More competition, lower prices.
What’s the allure? For you PVP people, think of a T2 BPOs as a Titan — a giant prize that you can show off, protect, and use to enhance your infamy. When I first started getting into industry in 2008, I thought these were the win button of Eve.
Now the only way to get one is to buy one from another player, which brings me to my research. I want to show everyone that even though they do make money, the return on the investment is very poor in the majority of cases.
The Research
Below is a list of T2 BPOs that have sold on the public Eve Online forums. I used a market program to figure out the yearly profit and divided that by the sold cost to get a return.
Though you may be drawn in by the yearly profit numbers, consider the amount of ISK used to achieve that number.
A few of the BPOs seem to have a reasonable return and might even be a slightly reasonable investment if you can keep it producing 24×7. It looks like some Ammo, Mining Crystals, and Torpedo BPOs will start to turn you a profit beyond the original investment in 2-3 years. I would definitively stay away from the 5+ year items as I think a change to the invention, research, or nerfing of the T2 BPOs will most likely happen soon(tm).
A Loss?
Yes, I have found that a few (maybe 3-4%) of T2 BPOs return a loss. The ability of players to out manufacture with the Invention process sinks the profits below that of a researched T2 BPO. Keep in mind that if you own a T2 BPO, you only produce one item. During the build phase you can’t do anything else with the BPO.
The beauty of the invention framework is that you can be more flexible and can react to changing market conditions. The drawback is that there is an increase in the complexity to produce an item.
Better Methods
If you have piles of cash and want to sit idle in a manufacturing station making sure your T2 BPO is constantly in production, you might want to consider buying some. For the average industry person or corporation, it is a very poor investment.
To compare performance numbers, I am making around 8 B a month turning over 31 B. A T2 BPO, equal to around the same amount of capital, will make you 3.5-7 B a year.
Not a solid investment in my book. Put your money elsewhere.
Rage Against the Machine
I haven’t seen an idea to resolve the imbalance that I have liked yet. More on this later.
It does seem that the market impact isn’t as big as the hype indicates according to CCP Diagoras.
[update] April 4 2012
The Good
93.95% of T2 Gyrostabilizers produced in March 2012 were from invention.
In March 2012, 90.23% of Hulks and 84.17% of Mackinaws produced were from invention.
89.77% of 1400mm II, 82.00% of Tachyon II, 87.34% of 425mm Rail II, 74.23% of Torpedo Launcher II produced in March were from invention.
55.25% of Improved Cloaks and 91.93% of Covert Ops Cloaks were produced via invention in March 2012.
86.81% of 220mm Vulcan Autocannons produced in March were produced through invention.
The Eh
67.85% of Sabres and 65.01% of Wolves produced in March 2012 were the from invention.
72.27% of the 2,005 Falcons produced in March 2012 were produced through invention.
66.13% of Ishtars and 63.53% of Zealots produced in March 2012 were produced via invention.
The Ugly
27.60% of Curses and 22.16% of Pilgrims produced in March 2012 were from invention.
Only 7.07% of Absolutions and 23.62% of Sleipnirs produced in March 2012 were produced through invention.
44.58% of Cerberus and 6.00% of Eagles produced in March 2012 were procuded through invention.
tl;dr
Post lottery, T2 BPOs do make very passive income for a very large price. For the time it takes to get a return on your investment, the capital could better be spent in other areas.
k162space 