Eve SDE SQL – Blueprint Details – Part 2

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.

Perfect Blueprints

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.

Production Times

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.

Materials to invent off of something

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;

Skills to manufacture something

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.

Skills to invent something

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.

The skills

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.

The skill levels

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.

Research times

researchMaterialTime and researchProductivityTime are straight out of invBlueprintTypes.

Material Efficency research

Per level:

Productivity Level research

Per level:

Times are all in seconds.

Getting Basic Blueprint Information

select productionTime,wasteFactor,productivityModifier,researchProductivityTime,researchMaterialTime
from invBlueprintTypes
where productTypeID=?;

This is most of the information needed in previous equations.

Is this obtainable through invention?

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.

Chance to invent

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.