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 {
  
}