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:

Querying Map Data

Gathering system data
SELECT region.regionName,constellation.constellationName,system.solarSystemName,
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
WHERE <> -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,,
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 <= 0.4 AND <> -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

No comments:

Post a Comment