Once you get comfortable with the CCP way of doing things, the SDE isn’t too bad to work with. You’ll have to dig around in it to get some of the numbers you want, and sometimes it’s the opposite way round from what you’d expect, but it’s not too bad, most of the time. Here are a few more useful snippets for getting information on things.
Continue reading
tl;dr: I’m a suspicious bastard. Gimme a personal POS I can let specific other people use.
I make stuff in Eve. Mostly T2 stuff. I run three accounts to do this. I’d like to be part of a larger corporation, but instead I keep my alts in a separate Corp. This is an attempt to explain why.
It’s because of 2 things.
The two are tightly linked, as you might guess. Either I join a Corp where they’re willing to grant me the ability to anchor stuff (and un-anchor other people’s stuff. And have other people un-anchor my stuff) or I keep it all in a single Corp.
I can’t imagine that I’m the only person in EVE that thinks this way.
What would be ideal for me is the ability to launch a POS that I can limit access to, to a very specific list of people. So only those people I authorize can use its resources, un-anchor it and so on (ideally a separate list for each)
I might trust my CEO to manage the corporation’s ISK. I don’t trust them to not take my stuff and kick me out the Corp. Only time I’d trust them that much, is if I know them, and can take it out of their hide in person.
My fist disclaimer: I don’t run a big corporation. It’s entirely possible, perhaps even probable, that I’m missing things, or possibly making things harder. I hope not, but that’s what comments are for.
I’ve heard many problems for how corp role assignment is painful, and how that can make management of POS painful too. It mostly comes down to things not being fine grained enough. Either you can cancel every corp industry job, or you can cancel none, for example.
So what I was thinking was the following. If it’s familiar, you may have worked with POSIX user management before. It’s pretty much lifted from there.
The first step is to allow for the creation of ad hoc user groups within the corporation. I’d recommend that you can nest them at least once as well, allowing for groups within groups. Multilevel nesting would be nice, but complicates matters a little.
Ideally these are parsed at logon, and then stored, to reduce the workload. Recursion makes databases cry. Stored in the ‘brain’ for the character. This would be easier once the ‘brain in a box’ work that’s been mentioned in the past is repeated. To allow for updates without needing a log off, I’d suggest a button on the corp pages or character pages, allowing someone to get a group update. This always annoyed me in Windows 😉
In an ideal world, you could also create groups of locations/hangers. So you could have a ‘The Forge’ group, which would contain all the hangers in the Forge, and a ‘Jita’ group which is just the hanger in jita 4-4 CNAP. A seperate group for a singular POS, or multiple POS would also be handy.
At that point, you could assign management roles for a location group, to a user group. Or for a user group, to a user group.
Some roles would need to be split apart, so you could have an anchor role that can be assigned globally, but split the un-anchor function away from it. So the user who anchors something can un-anchor it, but not everyone else. Just the people that user (or someone with a global management role) specifies. Ditto with arrays and labs (or jobs run from a corp hanger) A role to start them, and a role to manage them.
Ideally such groups would be global, capable of holding people who aren’t actually in your corporation (automatic removal when you kick someone or they leave) so you could share the ability to run jobs in your POS with people outside your corp (granted at corp, pos or array level)
It’s probably over complicated, but if you set things up right in the beginning (grant roles to groups, not people, then add people to groups) then it’s actually pretty simple to work with.
I covered the basics of Blueprint details from the SDE back in my last post, which covers most of what people actually want to know. There are a few extra bits which the more dedicated coders will want. They’re simple enough to work out, but it’s time you might not want to have to look at. Consider this a round up post.
Perfecting blueprints is, in many cases, a waste of time. You want ‘good enough’, rather than perfect. It’s also unattainable for some. A Raven would take you over 4 million days to perfect. And half of that time (or so) would be to save a single Trit.
I don’t have a formula for a ‘good enough’ level. It’s very dependent on the types of materials being used. The Formula for perfect, for each material, is:
If you want the perfect for an entire blueprint, pick the material with the highest quantity.
As before, the Waste Factor comes out of invBlueprintTypes.
More details from invTypeBlueprints.
If the PE of the blueprint is 0 or above, the total time in seconds is:
If the PE of the blueprint below 0, the total time in seconds is:
To then adjust it for your industry level
So to recap, the time in the database is the maximum time it’ll take to manufacture something (assuming it’s not a negative PE), with everything else reducing the time. The changes also fall off rapidly. PE beyond 5 or so is often worthless.
select t.typeid,t.typeName, r.quantity from ramTypeRequirements r,invTypes t,invBlueprintTypes bt where r.requiredTypeID = t.typeID and r.typeID = bt.blueprintTypeID and r.activityID = 8 and bt.productTypeID=?;
The bind variable is what you’re making. You could simplify the query if you had the blueprint ID already, eliminating the join to invBlueprintTypes, going straight to ramTypeRequirements. It’s possible to pull the damage too, but the SDE currently says the Interface is used up.
If you want to eliminate the interface from the pulled materials, use this sql. It’s eliminating interface, by ignoring the group it belongs to. To pull just Interface, then turn the != into a =
select invTypes.typeid,invTypes.typename,ramTypeRequirements.quantity from ramTypeRequirements,invBlueprintTypes,invTypes where producttypeid=? and ramTypeRequirements.typeid=invBlueprintTypes.blueprintTypeID and activityid=8 and invTypes.typeid=requiredTypeID and groupid !=716;
select t.typeName Name, r.quantity Quantity from ramTypeRequirements r,invTypes t,invBlueprintTypes bt,invGroups g where r.requiredTypeID = t.typeID and r.typeID = bt.blueprintTypeID and r.activityID = 1 and bt.productTypeID=? and g.categoryID = 16 and t.groupID = g.groupID
The bind variable is what you’re making. You could simplify the query if you had the blueprint ID already, eliminating the join to invBlueprintTypes, going straight to ramTypeRequirements.
This is a little bit of a pain. The skills are tied to the datacores and interface, rather than the job. Specifically they’re in dgmTypeAttributes, under the attributeIDs of 182 for the skill, and 277 for the level of the skill. As they’re done that way, pulling it with a single sql query is somewhat difficult, with a pivot table required (use if conditions, along with a max, then group them). When I’ve done it, I’ve just used 2 queries, and handled the pivoting in the code.
select t.typeid,t.typeName skill,r.requiredTypeID from ramTypeRequirements r,invTypes t,invBlueprintTypes bt, dgmTypeAttributes dta where coalesce(dta.valueInt,dta.valueFloat)= t.typeID and r.typeID = bt.blueprintTypeID and r.activityID = 8 and bt.productTypeID=? and dta.typeid=r.requiredTypeID and attributeID=182
The requiredTypeID refers to material you need the skill for. In this case, datacores and interfaces. Everything else is the skill.
The bind is what you’re making.
select r.requiredTypeID,coalesce(dta.valueInt,dta.valueFloat) level from ramTypeRequirements r,invBlueprintTypes bt,dgmTypeAttributes dta where r.typeID = bt.blueprintTypeID and r.activityID = 8 and bt.productTypeID=587 and dta.typeid=r.requiredTypeID and attributeID=277
The requiredTypeID refers to material you need the skill for. Level is the minimum level for the skill.
In the previous 2 queries you’ll have seen the use of coalesce. That’s because CCP seem to have had trouble deciding which to store the value in. It’s normally the valueInt, but not always.
researchMaterialTime and researchProductivityTime are straight out of invBlueprintTypes.
Per level:
Per level:
Times are all in seconds.
select productionTime,wasteFactor,productivityModifier,researchProductivityTime,researchMaterialTime from invBlueprintTypes where productTypeID=?;
This is most of the information needed in previous equations.
select parentTypeID from invMetaTypes where typeid=? and metaGroupID=2
This will only return a result if you can invent the blueprint from another one. The parentTypeID is the typeid of the original type. Such as a rifter, if you’re looking at a wolf.
This isn’t actually in the SDE. However, I found some handy sql to stick it into an table you can use for lookups. The type id is that of the product, rather than the blueprint.
create table inventionChance(typeid int,chance float); insert into inventionChance (typeid,chance) select typeid,CASE WHEN t.groupID IN (419,27) OR t.typeID = 17476 THEN 0.20 WHEN t.groupID IN (26,28) OR t.typeID = 17478 THEN 0.25 WHEN t.groupID IN (25,420,513) OR t.typeID = 17480 THEN 0.30 WHEN EXISTS (SELECT * FROM eve.invMetaTypes WHERE parentTypeID = t.typeID AND metaGroupID = 2) THEN 0.40 ELSE 0.00 end from eve.invTypes t,eve.invBlueprintTypes b where b.producttypeid=t.typeid;
That’s everything for now. I’ll cover how I deal with price data in a later post, but it’s a little more involved, as I’m taking it all from EMDR‘s firehose, and depending on memcache.
As you may know, I’m the ‘author’ of one of the blueprint calculators that some people use in EVE Online. The one at http://www.fuzzwork.co.uk/blueprints/
If you didn’t know that, how did you get here? 😉
Anyway, it’s not uncommon to see people asking how to get the materials for building something out of the Static Data Export; and as such, I thought I’d write up a post here that I can direct people to. Oh, and if you’re looking for the code for the blueprint calculator, take a look at https://github.com/fuzzysteve/eve-blueprint-calc It’s mostly up to date, just missing a few twiddly bits.
Now for the meat of this post. Being able to run this SQL is dependent on having a copy of the SDE up and running. If you’re using anything other than mysql, postgresql or oracle, you may have problems with the greatest function, in the first bit of sql. replace it with a case statement. You’re wanting to return 0, if the value is negative.
Fuzzwork
select typeid,name,greatest(0,sum(quantity)) quantity from (
select invTypes.typeid typeid,invTypes.typeName name,quantity
from invTypes,invTypeMaterials
where invTypeMaterials.materialTypeID=invTypes.typeID
and invTypeMaterials.TypeID=?
union
select invTypes.typeid typeid,invTypes.typeName name,
invTypeMaterials.quantity*r.quantity*-1 quantity
from invTypes,invTypeMaterials,ramTypeRequirements r,invBlueprintTypes bt
where invTypeMaterials.materialTypeID=invTypes.typeID
and invTypeMaterials.TypeID =r.requiredTypeID
and r.typeID = bt.blueprintTypeID
and r.activityID = 1 and bt.productTypeID=? and r.recycle=1
) t group by typeid,name
SELECT t.typeName tn, r.quantity qn, r.damagePerJob dmg,t.typeID typeid FROM ramTypeRequirements r,invTypes t,invBlueprintTypes bt,invGroups g where r.requiredTypeID = t.typeID and r.typeID = bt.blueprintTypeID and r.activityID = 1 and bt.productTypeID=? and g.categoryID != 16 and t.groupID = g.groupID
There are three bind variables in total, two in the first, one in the second. These are all the same value, the typeid of the item being manufactured. You could just replace them, but bind variables are your friend. Learn how to use them, and love them. People that do string replacement are asking for trouble. PHP has PDO, Perl has DBI, Python has DB-API, Java has JDBC. They all support bind variables.
The reason for the two queries is that CCP have split the materials into those affected by ME, which are also recovered when you recycle it (the base materials), and those which aren’t affected (the extra materials). The base materials query here doesn’t return the materials recovered, but that’s due to the removal of materials which are marked as recyclable in the extra materials.
The queries above only give you the materials for a perfect blueprint. It’s rare that you actually have one, so you’ll need to take ME and Production Efficiency into account as well. The waste factor referred to in the formulas is that one that’s defined in invBlueprintTypes. Perfect is the number of the materials defined by the above queries.
If your ME is 0 or greater, then for each material in your base materials:
If your ME is less than 0, then for each material in your base materials:
If your production efficiency is less than 5, then you also need to add 
If it’s 5, you’re adding a number that’s been multiplied by 0. You may wish to do this for completeness, or so that your code works for more than just your main producer.
That’s it for now. If you have any questions, feel free to ask here, or in Eve.
