open All Channels
seplocked EVE Technology Lab
blankseplocked SQL View for Weapons
 
This thread is older than 90 days and has been locked due to inactivity.


 
Author Topic

Princess Corrino
Amarr
Hinata Inn
Posted - 2011.02.04 15:00:00 - [1]
 

I'm trying to create a SQL view that can give me a table or weapon statistics (optimal, falloff, fitting (s/m/l), damage type, tracking speed) and other 'genera' stats about a turret weapon.

I'm not used to mySQL and not sure the best way to create one using such a normalized database as this.

Does anyone have a good example for the above to start as a basis?

Matalino
Posted - 2011.02.04 20:05:00 - [2]
 

The attribute names that you are looking for are stored in the dgmAttributeType table. The values of these attributes are stored in the dgmTypeAttributes table. You can then join these tables with the invTypes table and filter for the results that you are interested in.

The following code will get you the information you were looking for plus a little bit extra.

SELECT
[invTypes].[typeID],
[invTypes].[typeName],
ISNULL([R].[valueFloat], [R].[valueInt]) AS [rateOfFire],
ISNULL([D].[valueFloat], [D].[valueInt]) AS [damageMultiplier],
ISNULL([O].[valueFloat], [O].[valueInt]) AS [optimalRange],
ISNULL([F].[valueFloat], [F].[valueInt]) AS [falloffRange],
ISNULL([T].[valueFloat], [T].[valueInt]) AS [trackingSpeed],
ISNULL([S].[valueFloat], [S].[valueInt]) AS [signatureResolution]
FROM [invTypes]
JOIN [dgmTypeAttributes] AS [R]
ON [R].[typeID] = [invTypes].[typeID] AND [R].[attributeID] = 51
JOIN [dgmTypeAttributes] AS [D]
ON [D].[typeID] = [invTypes].[typeID] AND [D].[attributeID] = 64
JOIN [dgmTypeAttributes] AS [O]
ON [O].[typeID] = [invTypes].[typeID] AND [O].[attributeID] = 54
JOIN [dgmTypeAttributes] AS [F]
ON [F].[typeID] = [invTypes].[typeID] AND [F].[attributeID] = 158
JOIN [dgmTypeAttributes] AS [T]
ON [T].[typeID] = [invTypes].[typeID] AND [T].[attributeID] = 160
JOIN [dgmTypeAttributes] AS [S]
ON [S].[typeID] = [invTypes].[typeID] AND [S].[attributeID] = 620

Princess Corrino
Amarr
Hinata Inn
Posted - 2011.02.07 11:07:00 - [3]
 

Thanks for the help, I've done okay so far. I've now tried to get more information, such as Weapon Type (E/H/P), Sub-type (Blaster/Railgun... etc) and it's Size (S/M/L/XL)

Which all works fine for T1 and T2... but Storyline, Faction and Officer I can't get those extra details for.

Do you know how to get them?

My code so far:

SELECT
[invTypes].[marketGroupID]
,ISNULL([MT].[metaGroupID],1)
,[MG].[metaGroupName] [Meta Group]
,[GR].[groupName] [Type]
,[MGR_SUBTYPE].[marketGroupName] [Sub-type]
,[MGR_SIZE].[marketGroupName] [Size]
,[invTypes].[typeName] [Name]
,ISNULL([R].[valueFloat], [R].[valueInt]) AS [rateOfFire]
,ISNULL([D].[valueFloat], [D].[valueInt]) AS [damageMultiplier]
,ISNULL([O].[valueFloat], [O].[valueInt]) AS [optimalRange]
,ISNULL([F].[valueFloat], [F].[valueInt]) AS [falloffRange]
,ISNULL([T].[valueFloat], [T].[valueInt]) AS [trackingSpeed]
FROM [invTypes]
LEFT OUTER JOIN [invMetaTypes] AS [MT]
ON [MT].[typeID] = [invTypes].[typeID]
LEFT OUTER JOIN [invMetaGroups] AS [MG]
ON ISNULL([MT].[metaGroupID],1) = [MG].[metaGroupID]
INNER JOIN [dgmTypeAttributes] AS [R]
ON [R].[typeID] = [invTypes].[typeID] AND [R].[attributeID] = 51
INNER JOIN [dgmTypeAttributes] AS [D]
ON [D].[typeID] = [invTypes].[typeID] AND [D].[attributeID] = 64
INNER JOIN [dgmTypeAttributes] AS [O]
ON [O].[typeID] = [invTypes].[typeID] AND [O].[attributeID] = 54
INNER JOIN [dgmTypeAttributes] AS [F]
ON [F].[typeID] = [invTypes].[typeID] AND [F].[attributeID] = 158
INNER JOIN [dgmTypeAttributes] AS [T]
ON [T].[typeID] = [invTypes].[typeID] AND [T].[attributeID] = 160
LEFT OUTER JOIN [invGroups] AS [GR]
ON [GR].[groupID] = [invTypes].[groupID]
LEFT OUTER JOIN [invMarketGroups] AS [MGR_SIZE]
ON [MGR_SIZE].[marketGroupID] = [invTypes].[marketGroupID]
LEFT OUTER JOIN [invMarketGroups] AS [MGR_SUBTYPE]
ON [MGR_SUBTYPE].[marketGroupID] = [MGR_SIZE].[parentGroupID]
LEFT OUTER JOIN [invMarketGroups] AS [MGR_TYPE]
ON [MGR_TYPE].[marketGroupID] = [MGR_SUBTYPE].[parentGroupID]
WHERE [invTypes].[groupID] NOT IN ('100','549','426','430','449') -- Ignore Drones / Fighters / POS Turrets
ORDER BY [Type] ASC, [Sub-type] ASC, [MGR_SIZE].[marketGroupID] ASC, ISNULL([MT].[metaGroupID],1) ASC

Lutz Major
Posted - 2011.02.07 11:33:00 - [4]
 

You select the chargeSize from the marketGroupName Sad
Storyline, Faction and Officer are not available on the market.

Use the attributeID 128 to check for sizes.

Princess Corrino
Amarr
Hinata Inn
Posted - 2011.02.07 14:24:00 - [5]
 

Thanks!

The only missing bit now, is which category (blaster,railgun,pulse,beam,arty,autocannon) that they fall under.

Matalino
Posted - 2011.02.07 16:06:00 - [6]
 

I included attribute 160 (Signature Radius) in the original query because it indicated the weapon size: Small = 40, Medium = 125, Large = 400, XLarge = 1000.

You can lookup the type of ammon used with attributes 604, 605, 606, 609, and 610. Cross reference those fields with the groupID field in invTypes.

Princess Corrino
Amarr
Hinata Inn
Posted - 2011.02.09 11:47:00 - [7]
 

Thanks

I got it in the end.

To find out if "True Sansha Tachyon Beam Laser" or typeID of 13834 (for example), is a Beam or Pulse laser I had to cross-reference the graphicID with the invTypes table (filtering only on marketGroupID's of '567','568','569','570','572','573','561','562','563','564','565','566','577','578','579','574','575','576')

This gave me a normal market ID which I could then look up in invMarketGroups.

Now I can list it as an Energy Weapon / Beam Lasers / Large


 

This thread is older than 90 days and has been locked due to inactivity.


 


The new forums are live

Please adjust your bookmarks to https://forums.eveonline.com

These forums are archived and read-only