Code Snipets

A collection of code snippets as reference for various projects.  For SQL queries, I prefer mySQL only because it's easy to stick to one standard.

Blueprint Queries

Decryptors and their attributes:

SELECT invTypes.typeID, invTypes.typeName, invTypes.groupID,
GROUP_CONCAT(IF(attr.attributeID = 1112,coalesce(attr.valueInt,attr.valueFloat),NULL)) as `Success Modifier`,
GROUP_CONCAT(IF(attr.attributeID = 1113,coalesce(attr.valueInt,attr.valueFloat),NULL)) as `ME Modifier`,
GROUP_CONCAT(IF(attr.attributeID = 1114,coalesce(attr.valueInt,attr.valueFloat),NULL)) as `PE Modifier`,
GROUP_CONCAT(IF(attr.attributeID = 1124,coalesce(attr.valueInt,attr.valueFloat),NULL)) as `Runs Modifier(+)`
FROM invTypes
LEFT JOIN dgmtypeattributes attr ON attr.typeID=invTypes.typeID
WHERE (attr.attributeID =1112 OR attr.attributeID =1113 OR attr.attributeID =1114 OR attr.attributeID =1124)
-- attributeID: 1112=probabilityMod 1113=MEmod 1114=PEmod 1124=Runsmod
AND (invTypes.groupID >=728 AND invTypes.groupID <=731) 
-- Decryptor groups: 728=Amarr, 729=Minmatar, 730=Gallente, 731=Caldari
GROUP BY invTypes.typeID

@Fuzzysteve queries:

• Matching T1 items with their relevant decryptors
• Blueprint Bill of Materials

Querying Map Data

Gathering system data

SELECT region.regionName,constellation.constellationName,system.solarSystemName, system.security

FROM mapsolarsystems system

JOIN mapregions region ON region.regionID = system.regionID

JOIN mapconstellations constellation ON constellation.constellationID=system.constellationID

Note: this includes both K-Space and W-Space.  To filter K-space/W-space:
WHERE region.regionName NOT LIKE '_-R00%' --Filter out w-space region names
-OR-
WHERE system.security <> -0.99 --All W-space systems have -0.99 security value

Query used to set up moon-map table

SELECT system.solarSystemName AS "system", region.regionName,system.x,system.y,system.z, system.security,
SUM(IF(moonMaterial='Atmospheric Gases',1,0)) AS `Atmospheric Gasses`,
SUM(IF(moonMaterial='Cadmium',1,0)) AS Cadmium,
SUM(IF(moonMaterial='Ceasium',1,0)) AS Ceasium,
SUM(IF(moonMaterial='Chromium',1,0)) AS Chromium,
SUM(IF(moonMaterial='Cobalt',1,0)) AS Cobalt,
SUM(IF(moonMaterial='Dysprosium',1,0)) AS Dysprosium,
SUM(IF(moonMaterial='Hafnium',1,0)) AS Hafnium,
SUM(IF(moonMaterial='Hydrocarbons',1,0)) AS Hydrocarbons,
SUM(IF(moonMaterial='Mercury',1,0)) AS Mercury,
SUM(IF(moonMaterial='Neodymium',1,0)) AS Neodymium,
SUM(IF(moonMaterial='Platinum',1,0)) AS Platinum,
SUM(IF(moonMaterial='Promethium',1,0)) AS Promethium,
SUM(IF(moonMaterial='Scandium',1,0)) AS Scandium,
SUM(IF(moonMaterial='Silicates',1,0)) AS Silicates,
SUM(IF(moonMaterial='Technetium',1,0)) AS Technetium,
SUM(IF(moonMaterial='Thulium',1,0)) AS Thulium,
SUM(IF(moonMaterial='Tungsten',1,0)) AS Tungsten,
SUM(IF(moonMaterial='Titanium',1,0)) AS Titanium,
SUM(IF(moonMaterial='Vanadium',1,0)) AS Vanadium

FROM mapsolarsystems system
LEFT JOIN mapregions region ON system.regionID=region.regionID
LEFT JOIN moonData moons ON system.solarSystemName=moons.SOLARSYSTEMNAME
WHERE system.security <= 0.4 AND system.security <> -0.99 

GROUP BY system.solarSystemName

Technically, any GROUP BY can be used to build custom queries.  I like system.systemName or region.regionName

Note: 2D map uses (X,Z) coordinates, not (X,Y)

All the Market Items (Where You Care About Prices)

This one is thanks to Fuzzysteve

SELECT invTypes.typeid, typename, categoryname, COALESCE(valueint, valuefloat, 0) meta 

FROM invTypes 

JOIN invGroups ON (invTypes.groupid=invGroups.groupid) 

JOIN invCategories ON (invGroups.categoryid = invCategories.categoryid) 

LEFT JOIN dgmTypeAttributes ON (invTypes.typeid = dgmTypeAttributes.typeid AND attributeid = 633) 

WHERE COALESCE(valueint, valuefloat, 0) <7 

AND invGroups.categoryid IN (4,5,6,7,8,10,17,18,20,22,23,32,34,35,41,42,43) 

AND invTypes.published != 0 AND marketGroupID IS NOT NULL