Working with Eve Static Data

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/.

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.

About these ads

Hold on a second

Crucible 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

TTP, 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

Not 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.

Before 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?


I hate lowsec

I wouldn’t call myself a carebear. I live in W-Space, have tried null, tend to take the risky shot over the safe one and usually end up killed for it. Repeatedly.

Anyway, here’s today’s misadventure:
I’m scanning, after the fifth C5 wh, which has a probable C5 static, It’s about 2 am. I plan to go to bed immediately if not sooner, so I launch five probes at 4 AU and two at two AU, overlapping the two twos and a four with the remaining four fours (Say that five times fast). I go round the system, scanning at each planet. Whenever I get a red dot or circle at 10% strength or more, I center the two 2 AU probes on top and rescan, which usually gives me the type, ranging from Ladar(damnit), Radar(argh), Magnetometric(curses!) and Unknown(Yay!).

Anyway, I find a WH on the first sweep and warp to it, K162 from XXdeathXX space, making this our seventh encounter with the Russian kind and our third with XXdeath in particular. The silver lining to this particular vodka-fueled cloud of cryillic-spewing death was that the WH was end of lifetime. Good thing about the end of lifetime, but that usually means that they’ve been here and activated the static some point in the last 24 hours, which will make any route I scan through this system unstable closing-time wise.

More scanning, I find the C5 static and decide to stay in system to check the last planet, partially for a sense of completion and partially because the XXdeath WH had had its stability disrupted a tad, meaning that it had been used rather heavily. Lo and behold, highsec WH.

Inwardly cheering, I grab a skillbook or two, log my freighter alt in and scurry home, updating my alt’s train queues (logistical domi FTW). I then, using the passive income profits, buy about…450 mil worth, which gets me two fitted gang domis, two fitted gang myrms and a dps apocalypse.

(You can’t tell, but I was using the word “DPS ironically, since as of this writing I just barely break 300 dps with a gank-fit apoch. I need to git me sum supportin skills!).

Anyway, three A.M. now, I shove all 300.3k m3 into the freighter, set destination to the highsec system, undock, stop ship, warp to the first gate and start cursing like I’ve walked into a door while simultaniously developing Tourette’s syndrome.
This highsec WH is on a highsec island, making it impossible to get to without going through three lowsec systems and all the pirates contained wherein. And I have to get a freighter, one of the biggest, slowest ships in Eve through, or wait the 4-5 days it will take to find a BS-supporting unoccupied route again. Feeling the pangs of sleep, I go to bed with an alarm set for just after downtime, five hours from now and set the freighter to autopilot to the last highsec system.
Dreamless night, wake up, get my main outside the WH at the first lowsec, freighter jumps in, starts warping to the next lowsec gate, main char(in Buzzard) get in web range, webs, warning pops up, I ignore it, and my Buzzard starts taking gate fire as the freighter warps off.
Apparently, I had forgotten that the freighter alt was in the altcorp I had created to protect my C1 and/or hauling shenanigans from wardecs, so, panicking, I mash the “warp” button on whatever I have selected at the time, which turns out to be the next gate.
Next local has a dude in it, I warp the freighter off the gate, unassisted, lands on next gate and jumps through without incident, main char docks and buys a frig w/ webs, and after the 15 minute jerk timer, I dock the freighter in one station, undock in pod, dock in another to get an actual SHIP, undock, jettison the tritanium gratuity gift, main steals it, freighter steals it back and BAM, I can web through the last lowsec. Which I do.

Dock in highsec island, move one Dominix through before realizing that three out of the five wormholes in the chain have gone end of lifetime, and if they collapse either my freighter will be stuck on the wrong side of several lowsec gates from jita or my main, albeit buzzard-less, will be stuck far, far away from where I need his talents. I move one more ship, web the freighter through the lowsecs again and nip back home before the WH closes, which it does, fifteen minutes later.


Operation Passive Income update.

For those of you watching the market on reactions, you all know that it’s wobbly as hell right now. For those who don’t, fullerides, my reaction of choice, has crashed, burned, died, been buried, exhumed, used as draino and forgotten in an unmarked septic tank. And by that I mean it dropped from 1600 per unit to 800 per unit, breaking my expected profit over its knee.

So, having moved to a significantly more stable reactant, things have been going well. Said reactant uses much less isk in moon goo per hour, so I can haul around weeks of reaction inputs without fear. Course, I can now haul everything without fear up to a bil thanks to “Krayt Dragon Bait”:

Awesome freighter, my origonal plan to keep her in the newbie corp disintigrated when I initiated warp to a gate, had a pleasant conversation, made a sandwich, met a girl, took a sailing trip around the world, lived life as I wanted and met death as an old friend as I entered warp. So, now she’s in my altcorp so I can web through the 40j round trips my exits tend to be.

On that note:

Awwww yeah.

Anyway, since I’ve talked to a few people who have fallen right off my “How rich are you” scale, loaning me bils and saying “pay it back whenever, with however much you think is reasonable, yes, of course I trust you, it’s only a bil or two”, my PoS fields have expanded to eight, soon upgrading to ten.

No, I’m not giving you their names.

http://www.staticmapper.com (shameless link again) has been invaluable for me to determine who or what’s in my hole: If there are two jumps, I’ve usually got a k162, if there are ten jumps and sleeper kills, someone did my sites while I was asleep, and if there are 40 jumps, then a null WH opened and they used my hole as a shortcut >.>

Essentially, if I don’t activate my static wormhole I see little to no traffic, but when I do open the wormhole, traffic comes in, tries to run a site, messes up a spawn and leaves after killing one sleeper.

For fear and intimidation purposes, I’ve anchored a large ship assembly array and called it “wut r siz restrictionz lulz?” For those who don’t get the joke, the array will let me build battleships in an area that does not allow battleships to enter usually, giving me a leg up on any would-be attackers.

Having eight poses has opened up an opportunity for me, and by that I’m not referring to using Nightmares as a throwaway ship; I mean spelling out amusing words and phrases with my towers :P

So far, I’ve tried Princess Bride quotes, Monty Python quotes, and various other humerous suggestions by my friends and/or people waiting for a sliver of an opportunity to back-stab me ad take all that I have. The crowning suggestion has been, so far:

For those who, again, don’t get the joke, it’s fairly funny :P


Follow

Get every new post delivered to your Inbox.

Join 92 other followers