Eve SQL of note

Attributes

show Power Grid and Cpu requirements for station bits:

SELECT
 t.typeName,
 a30.valueInt AS PG,
 a50.valueInt AS CPU
FROM
 invTypes t
 LEFT JOIN dgmTypeAttributes a30 ON t.typeID = a30.typeID
 LEFT JOIN dgmTypeAttributes a50 ON t.typeID = a50.typeID
 INNER JOIN invGroups g ON t.groupID = g.groupID
WHERE g.categoryID =23
 AND a30.attributeID =30
 AND a50.attributeID =50
 AND t.published =1
ORDER BY a30.valueInt DESC 
LIMIT 0 , 30

taken / adjusted from http://wiki.eve-id.net/Category:Database_Example_Queries

All attributes for an item

SELECT dgmAttributeTypes.attributeID AS attributeID, dgmAttributeTypes.attributeName, dgmAttributeTypes.displayName, dgmTypeAttributes.valueInt, dgmTypeAttributes.valueFloat
FROM dgmAttributeTypes, dgmTypeAttributes
WHERE dgmAttributeTypes.published =1
AND dgmTypeAttributes.typeID =23009
AND dgmTypeAttributes.attributeID = dgmAttributeTypes.attributeID
ORDER BY dgmAttributeTypes.attributeCategory, dgmAttributeTypes.attributeId
LIMIT 0 , 30

(published)

SELECT dgmAttributeTypes.attributeID AS attributeID, dgmAttributeTypes.attributeName, dgmAttributeTypes.displayName, dgmTypeAttributes.valueInt, dgmTypeAttributes.valueFloat
FROM dgmAttributeTypes, dgmTypeAttributes
WHERE dgmTypeAttributes.typeID =23009
AND dgmTypeAttributes.attributeID = dgmAttributeTypes.attributeID
ORDER BY dgmAttributeTypes.attributeCategory, dgmAttributeTypes.attributeId
LIMIT 0 , 30

(all)

One line:

select attributeName,description,coalesce(valueInt,valueFloat) from dgmTypeAttributes join dgmAttributeTypes on (dgmTypeAttributes.attributeID=dgmAttributeTypes.attributeID) where typeid=587;

select
 attributeName,description,coalesce(valueInt,valueFloat)
from
 dgmTypeAttributes join dgmAttributeTypes on (dgmTypeAttributes.attributeID=dgmAttributeTypes.attributeID)
where
 typeid=587;

Variations

show variations and meta levels:

• no meta zero included.
• need to know the meta zero item before hand.
• meta zero and parent are the same thing.

SELECT
 theitem.typeID AS typeID,
 theitem.typeName AS Name,
 a633.valueInt AS MetaLevel
FROM
 invMetaTypes
 INNER JOIN invTypes AS theitem ON invMetaTypes.typeID = theitem.typeID
 LEFT JOIN dgmTypeAttributes AS a633 ON a633.typeID = theitem.typeID
WHERE
 invMetaTypes.parentTypeID =230 AND
 a633.attributeID =633 
ORDER BY a633.valueInt
LIMIT 0 , 30 

a variation on showing variations:

• faster
• no attributes
• need to know the meta zero item before.

SELECT
 invMetaTypes.typeID,
 invMetaTypes.parentTypeID,
 invMetaTypes.metaGroupID,
 invTypes.typeName
FROM
 `invMetaTypes`
 LEFT JOIN invTypes ON invMetaTypes.typeID = invTypes.typeID
WHERE `parentTypeID` =568

T2 bill of materials

altered from http://wiki.eve-id.net/Bill_of_Materials

SELECT typeReq.typeName, graphics.icon, CEIL(materials.quantity * (1 + bluePrint.wasteFactor / 100) ) AS quantity, materials.damagePerJob
FROM TL2MaterialsForTypeWithActivity AS materials
INNER JOIN invTypes AS typeReq
ON materials.requiredtypeID = typeReq.typeID
INNER JOIN invBlueprintTypes AS bluePrint
ON materials.typeID = bluePrint.blueprintTypeID
INNER JOIN eveGraphics AS graphics
ON typeReq.graphicID = graphics.graphicID
WHERE materials.typeID = 20187 AND materials.activity = 1
ORDER BY typeReq.typeName

material refine

show what you get if you refine an aferburner:

select
       t.typeName as 'Refine',
       components.quantity,
       components.requiredTypeID,
       r.typeName as 'Get'
from
       invTypes t
       left join TL2MaterialsForTypeWithActivity components on
t.typeID=components.typeID
       inner join invTypes r on r.typeID=components.requiredTypeID
where
       t.typeID=439
       and t.published=1
       and r.published=1
order by components.requiredTypeID

as of dominion: /Denormalizing invTypeMaterials and ramTypeRequirements

Star Base Requirements

select
 tower.typeName,
 resource.typeName,
 quantity,
 minSecurityLevel,
 factionID,
 invControlTowerResourcePurposes.purposeText
from invControlTowerResources
 LEFT JOIN invControlTowerResourcePurposes ON invControlTowerResources.purpose            = invControlTowerResourcePurposes.purpose
 LEFT JOIN invTypes as tower               ON invControlTowerResources.controlTowerTypeID = tower.typeID
 LEFT JOIN invTypes as resource            ON invControlTowerResources.resourceTypeID = resource.typeID

invTypeReactions

select 
 reactionTypeID, 
 input, 
 typeID, 
 quantity
from invTypeReactions

Planetary interactions

Three tables:

• planetSchematics
• planetSchematicsPinMap
• planetSchematicsTypeMap

planetSchematics

pretty dumb, list of schematics:

schematicID	schematicName	cycleTime
89	Ukomi Super Conductor	3600

planetSchematicsPinMap

What facilities makes what stuff?

schematicID	pinTypeID
65	2470

planetSchematicsTypeMap

This is where all the good data is, mapping inputs to outputs.

schematicID typeID	quantity	isInput
131         2073	3000	1
131         2393	20	0

If I have X what can I make?

select
 planetSchematicsTypeMap.schematicID,
 planetSchematics.schematicName	as schematicName,
 planetSchematicsTypeMap.typeID,
 invTypes.typeName,
 quantity,
 isInput
from
 planetSchematicsTypeMap
 LEFT JOIN planetSchematics ON planetSchematicsTypeMap.schematicID = planetSchematics.schematicID
 inner join invTypes        on planetSchematicsTypeMap.typeID      = invTypes.typeID
where
 planetSchematicsTypeMap.typeID = 2397

schematicID	schematicName	typeID	typeName	quantity	isInput
80	Microfiber Shielding	2397	Industrial Fibers	40	1
85	Polytextiles	2397	Industrial Fibers	40	1
88	Polyaramids	2397	Industrial Fibers	40	1
135	Industrial Fibers	2397	Industrial Fibers	20	0

Breif Algorythm

18 "broadcast node"
$finalqty = 18;

2867 = get_id(broadcast node)
( $id , $qty ) ( 117 , 1 ) = get_schematic_id( 2867 )
$numberofruns = $finalqty / $qty ;

$ingredients = get_ingredients(117)
$ingredients => [
	$item , $qty
	$item, qty
]
foreach $ingredient
 add_to_bom( $bom , $itemname, $parentitem, $itemid , $numberofruns * $qty )
}
foreach $item in bom {
  
}