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.
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.
Hold on a second
Posted: 2011-11-19 Filed under: eveonline | Tags: AHARM, Botting, crucible, eve, Exploit, RMT, russian, supercarriers 8 CommentsCrucible has been announced with the long awaited supercapital nerf, which makes them mostly tools for fighting other supercapitals and/or things that hold still.
A while back, AHARM was caught using a kinda-sorta-obviously-exploit in a certain type of wormhole. For those who don’t know, some wormholes have special effects, like increasing RR power but decreasing local rep power, increasing armor resists but lowering shield, ect ect. These effects go from barely noticable in a C1 class wormhole to holy-shit-this-3xRR-domi-is-stable when you’re in a C6.
One of these wormholes increased damage from all ships(Sleepers included) by 100%, as well as boosting the strength of all types of EWAR, including tracking disruptors. Some bright spark figured out that if you tracking disrupted someone so hard their midslots swapped places their tracking would go into the negative or go below -1 or something similar that wasn’t supposed to be doable. The result of this was that the ship being disrupted gained LITERALLY perfect tracking and optimal range(250k), if you happened to be sensor dampening them at the same time or something.
Rooks and Kings, some nullsec or wormhole or whatever group whose name I only know because they attacked Split Infinity Radio, attacked AHARM in their home wormhole and were a bit supprised by being hit by blaster boats from 250k away perfectly each time. They petitioned CCP, CCP changed the way that wormhole worked and didn’t ban anyone, everyone went their separate ways.
Now some large group has gained an advantage over another MAYBE through an exploit(bots). They are almost impossible to kill and everyone is still trying to work on ways to counter and/or welcoming their new overlords. And since CCP either can’t or won’t detect and remove the bots, they have gained this power “fair and square”.
(Incidentally I use the term maybe because they might have used the bot money for something other than supers. Like titans or candy.)
Supercapitals aren’t being nerfed because the Russians gained critical mass of them through exploits, they’re being nerfed because nullsec stood on the edge of becoming carebearland and/or Russianland. The game has almost been “broken” through some means because one value grew too big using CCP-Approved methods.
So they’re nerfing supercapitals. If I were the DRF CEO or Director that said “hey, we have all this ISK, let’s sink it into SUPERCAPITALS”, I’d feel a bit cheated right now.
Supercarriers are expensive. Right now, on my “list of shit to do on Eve”, “own a supercarrier” is pretty far down on the list, mostly because of the 18 bil cost and that I’m probably going to lose the damn thing the moment I board it. For me, flying the second biggest ship in Eve defines the “endgame”.
My point is, what if instead of supercarriers RUSEve spent it on, say, making accounts or hiring people to fly with them? Okay, they did that with PL and THEN got the supercarriers, but what if they had taken it even farther? What if fighting RUSEve didn’t mean one unstoppable fleet, it meant THREE fightable fleets attacking separate targets with huge subcap numbers, reps and such. You could fight one, and win, but it would be instantly replaced while the other two wrecked whatever they were attacking. Controlling multiple accounts is not hard, and I could probably write a script or program to control an alpha fleet BS(pleasedon’taskmeto pleasedon’tbanmeCCP).
And also, now that the context of nullsec and supercarriers has defined “Overpowered”, can we please have the old wormhole exploit back? If everyone has it it isn’t an unfair advantage, and it would make for extremely weird/fun fleet fights.
TTP: Time to Pony
Posted: 2011-09-27 Filed under: screenshot | Tags: cache, client, cq, devblog, eve, lasers, magic, ponies, test 3 CommentsTTP, abbreviation for time to pony
1. The time it takes for items in game to degrade into Ponies.
Following the directions in the latest devblog, which officially states that we can add videos to our Captain’s Quarters, I converted a highres version of a Test Alliance’s “Lasers are Magic” video into a .BIK file and placed in my cache folder.
28 minutes for my TTP. This number included time to enable my Captain’s Quarters, which has been disabled because I can’t run my three clients without my GPU fan going into overdrive. ಠ_ಠ
Transcoding the video increased the file size by 64.5% so make room for all your new videos.
POS Defense in Wormholes C1-6
Posted: 2010-04-24 Filed under: eveonline | Tags: carrier, die, eve, eve online, k162space, orca, pos defence, pvp, wormhole 16 CommentsNot so recently, CCRES came into our wormhole, noticed an orca floating in the shields, killed all our defences and reinforced our tower in case we had forgotten to put strontium in a tower in a dread-accepting enviroment.
The fact that they felt that the lack of strontium was a possibility makes me lose a little faith in the foresight of the Eve wormhole community.
Recently, I was contacted by a CCRES member who told me that they put the PoS into reinforced mostly for fun. Acting on various word cues in his description, I managed to decypher THIS message:
“Our reason was far grander than a petty Orca and what we had in the corp hangar, for the legendary tales of your pvp prowess and twisted cunning in use of cloaky ships was so utterly terrifying that we could not just close the connecting WH but attacked your tower in hopes that having their ships destroyed by your considerable defenses, THEN closing the connecting WH with what few ships remained would be the only way to appease your unquantifiable wrath. Unfortunately, the tower’s defenses had been mostly offlined and unanchored into the corp hangar due to a spy, who after his two year infiltration campaign only now had the courage to act on his orders to mildly inconvenience you, then immediately killed himself out of fear and shame. We were thus forced to put the inconvenienced tower into full reinforced, to better reflect it’s former armed glory until a point in the future where you could reanchor your powerful and majestic defenses.”
Anyway, we had the full compliment so they settled for sending us a mail essentially saying:
“Hey, we put your pos in reinforced looking for free ships, you should probably try this configuration next time”
(For those interested they recommended dickstar)
After the reinforced timer ended we repped the PoS in a rorqual, orca and a few BSs which got MUCH faster once we realized the ending of reinforced meant that we could access the cap rechargers, power relays and energy xfer arrays in the corp hangar.
Anyway, this shite got me thinking. Wormhole territory is fast becoming a shifting battlefield. PoS takedowns, once the product of Amarr BS fleets that passed out at their keyboards firing at abandoned structures, are becoming more frequent and frankly, kinda alarming. Between a friend of mine getting attacked by two dreads w/ BS support fleet, Planet Risk owning then being owned by R&K, and CCRES’s “Stront check” of our PoS, I’d like to give an overview of PoS defense in a wormhole:
You’re screwed.
Here’s a little more detailed and less cynical view:
C1:
Size restrictions allow only battlecruiser hulls or smaller, but know that haulers, which can hold cap parts, large towers, fuel, x-large ship assembly arrays and everything else required to build a dread to kill your PoSes, can.
Recommended defenses:
Small guns, some medium guns anchored but not online. BC hulls or smaller usually means your only attackers will have poor dps, so hardeners are recommended. By the time they’ve built a dread you should already be gone >.>
C2-C3
Battleships are allowed in here, good for sites, bad when someone hates you, has a route to you and has a BS fleet handy can be a significant threat to your well-being. Build a carrier and rent/own/know someone who has a carrier pilot handy to scare off/murder/ambush attackers.
C4-C6
Carriers are “allowed” to jump in here, hence you should be prepared. Don’t ask me how, we still haven’t come up with a a counter to a carrier/dread with support fleet.
Essentially, the deeper you go, the more likley you are to be screwed, because the less time attackers have to invest in beating you into the ground. A battlecruiser takedown of a PoS is torture, a BS takedown isn’t much fun, and a dread-assisted takedown is about less than an hour, so an attacker’s time invested in making your life a living hell is much less than what it took for you to get two capitals, orcas, and the other thirty ships you’ve amassed over several months into the wormhole.
Point is, if someone wants you out of a specific hole, they can motivate you to get out. If you fit guns, they’ll wait for your offtime and have RR gangs. If you fit ECM, they’ll bring a dread and sensor boosters. If you go full hardeners, they’ll bring a dread again. If you find the most amazing awesome pos configuration ever, they’ll just starve you of fuel. They can activate all your sites with a scan ship every morning, camp your static, build a dread, any number of things in mind to make you think “boy, I wish they weren’t here or I wasn’t here”. The best defence, IMO, is two fold:
The first and more reasonable method is to have several friends and/or piles of isk to hire several friends. Eve is a spreadsheet game, but is refreshingly simple in combat calculations, which usually amount to “Whoever has the most experienced friends wins”.
The method that, us lacking large groups of friends and a general sense of comradery, is to be ready to be gone. Write the PoS off as already dead, and have the expensive gear in capital ships/industrials. When, not if, WHEN you get attacked by a superior force move to a deep safe you’ve created before deep safes were nerfed(before May 18), and/or make a safe in system and logoff in it. Then, scan your static, which, since you’ve been preparing for this exact situation for a while, goes to a C4 or higher WH and will close exactly after you pass your entire operation through it, once it’s been shoved into cap ships/orcas. If said static has a static C4 or higher, move in, setup the backup pos and either rebuild there or go through the next static.
The third method, that dedicated people of eve will probably do, and do better than me, is to want the hole more. Have an Orca/carrier cloak in system w/ lots of SB hulls, scan ships hulls, and fits. Get 5-10 people in. Proceed to activate every site after downtime, bomb the static periodically and generally make whoever tried to take your hole/lives in the hole you want wish they’d never heard of a local-lacking environment. Take 200 combat scan probes, launch seven, cloak. Repeat every 2 hours or so. Eventually, they will leave. Wait a week. If they come back, do it all again, if they don’t, setup a small pos and put a maint array/hangar in it. Have one big ship and one small ship for each pilot. Pilots logoff in big ships w/ scan ships/SBs in orca and CHEAP haulers/other ships in the small tower’s maint array. Don’t bother putting anything in the small tower’s corp hangar, as anyone who finds it will try to put it into reinforced, making removal of the stuff difficult. Don’t worry about defenses, again, if someone wants the tower dead, it’s dead. Make sure to have stront, and if the dudes come back lookin for trouble, unanchor the pos (using a cheap hauler w/ SB support), stuff it in the Orca and go back to step one. Repeat as necessary until they stop coming back and you feel safe enough to anchor a large pos and/or keep living out of orcas/capitals. Use any spare orcas/carriers to collapse the static at inoppertune times, and restock your equipment (using cheap haulers) at 5 am their time and/or downtime. Bubble the static and cloak SBs nearby.
People attack your wormhole because they’ve either been hired to do it, they want your sites, or they’re just bored. The bored will leave, the hired will eventually stop working for the hirer, and the people that want your sites won’t get your sites because you activated all the sites every downtime. An Orca, a few scan ships and SBs w/ fits is not expensive at all compared to WH sites, so you can make their lives irritating w/ very little expense on your end. The person who wins will eventually either be the person/people who get bored first. Or, someone runs out of money. Who’s gonna run out of money first, the people w/ covops cloaks or the people who can’t cloak while they fight sleepers or mine?
The not-so-great deep safe analysis.
Posted: 2010-04-22 Filed under: eveonline | Tags: ccp, consipracy, conspiracy theory, deep safe, eve, eve online, nerf, silly 5 CommentsBefore I start, if anyone reading this sees TeaDaze, tell him I said “I told you so”.
Early on, when we were still drafting plans for mining in the static C5, one of the major problems was that hauling the ore through the connecting WH would slowly whittle down the mass to the point that we might not be able to fit the Hulks, Orca, ectera back through, condeming them to a lifetime of trying to build a scan probe launcher out of spare parts, and when that failed, crashing each ship into a separate planet and re-creating civilization, prospering on individual planets until inter-planet contact is established, at which point a war is sparked. This commits each population to the destruction of the other, draining each planet of resources to construct vessels of war, which are launched into space only to be loaded onto my Orca, and shipped to Jita while holoreels of old spaceship battles are shipped back to the population, with all news networks carefully manipulated to keep the war going indefinitely…..
Wait, sorry, that’s my Tyrannis plan. Anyway, an alternate solution of making a deep, deep safespot and having the rorqual compress ore on site in safety was one of our “really good ideas”, which, unfortunately, will soon be impossible since to the disabling of said exploit, which is at the top of CCP’s “fix now” list, instead of some other things that I remember being upset about but apparently I’m not passionate enough about to remember. The one legitimate reason for nerfing deep safes I’ve heard so far are some people in null are putting their Dominion shoot-me-to-take-system thingys way out there making warping to them difficult. Oh, and it’s an exploit.
The more enjoyable explanation, in my opinion, is that CCP is trying to discourage blob warfare by making it unfeasible from a lag standpoint, encouraging hit-and-run groups attacking several strategic areas instead of 300 man cap fleets eliminating everything so fast they redefine the word “superior firepower”. But hey, that’s just the ramblings of someone who thinks a bit too much into these things.
The 20 AU limit seems fair, as the only non-exploiting way to get that far now is flying an interceptor in the preferred direction for several years, the major point being that there is no longer anywhere convenient to stuff a large cap fleet while their grid loads. See aforementioned conspiracy theory.
Anyway, cataclysmic variable did a much more serious analysis, but I disagree on his conclusion that “We have the power to change this game”.
The end result, deep safes unusable, is still going to happen. We’ve managed to achieve “we’ll move the ships” instead of “we’ll blow up the ships”, but there is still no-where to park a supercapital and there still will be no way to exit out mid-warp when you realize you’re warping into a gatecamp/death. You can say “we changed things”, but really, what’s different? The five people who quit Eve while in a safespot and didn’t leave any contact information with friends lose a ship that they apparently don’t care about.
It will still be impossible to fight on a grandiose scale without lag occurring, and now grid loading issues will be crippling because when a cyno is lit in a system with a defending fleet, the defending fleet will warp to that cyno, the attacking fleet will have grid load issues, ectera.
So, the three options are either have the attacking fleet in system BEFORE the defending fleet, or attack several points at once, or don’t use blobs. See CCP Conspiracy theory. Also, it’s probably worth noting that on all the forum’s I’ve read, the devs only respond to questions not including the phrase “Fix the lag before you fix deep safes”. COINCIDENCE?
Also, CCP has THREE letters, but only TWO Cs. COINCIDENCE? I think NOT!
The Icelandic volcano has shut down European air travel the same day deep safe nerfs were announced. COINCIDENCE?
I’m hungry, so I shall go make a sandwich. COINCIDENCE?