Eve SQL of note: Difference between revisions
From Federal Burro of Information
Jump to navigationJump to search
(New page: show Power Grid and Cpu requirements for station bits: <pre> SELECT t.typeName, a30.valueInt AS PG, a50.valueInt AS CPU FROM invTypes t LEFT JOIN dgmTypeAttributes a30 ON t.typeID = a30.t...) |
|||
(21 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
show Power Grid and Cpu requirements for station bits: | ==Attributes== | ||
===show Power Grid and Cpu requirements for station bits:=== | |||
<pre> | <pre> | ||
SELECT t.typeName, a30.valueInt AS PG, a50.valueInt AS CPU | SELECT | ||
FROM invTypes t | t.typeName, | ||
LEFT JOIN dgmTypeAttributes a30 ON t.typeID = a30.typeID | a30.valueInt AS PG, | ||
LEFT JOIN dgmTypeAttributes a50 ON t.typeID = a50.typeID | a50.valueInt AS CPU | ||
INNER JOIN invGroups g ON t.groupID = g.groupID | 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 | WHERE g.categoryID =23 | ||
AND a30.attributeID =30 | AND a30.attributeID =30 | ||
AND a50.attributeID =50 | AND a50.attributeID =50 | ||
AND t.published =1 | AND t.published =1 | ||
ORDER BY a30.valueInt DESC | ORDER BY a30.valueInt DESC | ||
LIMIT 0 , 30 | LIMIT 0 , 30 | ||
</pre> | </pre> | ||
taken / adjusted from http://wiki.eve-id.net/Category:Database_Example_Queries | taken / adjusted from http://wiki.eve-id.net/Category:Database_Example_Queries | ||
=== All attributes for an item === | |||
<pre> | |||
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 | |||
</pre> | |||
(published) | |||
<pre> | |||
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 | |||
</pre> | |||
(all) | |||
One line: | |||
select attributeName,description,coalesce(valueInt,valueFloat) from dgmTypeAttributes join dgmAttributeTypes on (dgmTypeAttributes.attributeID=dgmAttributeTypes.attributeID) where typeid=587; | |||
<pre> | |||
select | |||
attributeName,description,coalesce(valueInt,valueFloat) | |||
from | |||
dgmTypeAttributes join dgmAttributeTypes on (dgmTypeAttributes.attributeID=dgmAttributeTypes.attributeID) | |||
where | |||
typeid=587; | |||
</pre> | |||
=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. | |||
<pre> | |||
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 | |||
</pre> | |||
a variation on showing variations: | |||
* faster | |||
* no attributes | |||
* need to know the meta zero item before. | |||
<pre> | |||
SELECT | |||
invMetaTypes.typeID, | |||
invMetaTypes.parentTypeID, | |||
invMetaTypes.metaGroupID, | |||
invTypes.typeName | |||
FROM | |||
`invMetaTypes` | |||
LEFT JOIN invTypes ON invMetaTypes.typeID = invTypes.typeID | |||
WHERE `parentTypeID` =568 | |||
</pre> | |||
=T2 bill of materials= | |||
altered from http://wiki.eve-id.net/Bill_of_Materials | |||
<pre> | |||
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 | |||
</pre> | |||
=material refine= | |||
show what you get if you refine an aferburner: | |||
<pre> | |||
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 | |||
</pre> | |||
as of dominion: | |||
[[/Denormalizing invTypeMaterials and ramTypeRequirements ]] | |||
== Star Base Requirements == | |||
<pre> | |||
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 | |||
</pre> | |||
== invTypeReactions == | |||
<pre> | |||
select | |||
reactionTypeID, | |||
input, | |||
typeID, | |||
quantity | |||
from invTypeReactions | |||
</pre> | |||
== 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? === | |||
<pre> | |||
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 | |||
</pre> | |||
{| class="wikitable sortable" border="1" | |||
|- | |||
| align="center" style="background:#f0f0f0;"|'''schematicID''' | |||
| align="center" style="background:#f0f0f0;"|'''schematicName''' | |||
| align="center" style="background:#f0f0f0;"|'''typeID''' | |||
| align="center" style="background:#f0f0f0;"|'''typeName''' | |||
| align="center" style="background:#f0f0f0;"|'''quantity''' | |||
| align="center" style="background:#f0f0f0;"|'''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 === | |||
<pre> | |||
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 { | |||
} | |||
</pre> | |||
[[Category:eve-online]] |
Latest revision as of 12:32, 5 July 2021
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 { }