These forums have been archived and are now read-only.

The new forums are live and can be found at https://forums.eveonline.com/

EVE Technology Lab

 
  • Topic is locked indefinitely.
 

static data dump sql string help

First post
Author
Previous Topic Next Topic
Tuborg Uitoh
#1 - 2014-06-10 12:47:41 UTC  |  Edited by: Tuborg Uitoh
Hey

so I’m working a bit with the sql version off the static data dump I’m downloading the dump from fuzzwork but i'm not realy good at using SQL commands ...

So what I’m looking for is a SQL command That can list the following things

only t1 ships with type id ,name,some kinde off image identifier and if plosibel sortet bay market gruop larges ship first


i been traying to edit this sql command but can't get it workingSad
Hel O'Ween
Men On A Mission
#2 - 2014-06-10 16:37:21 UTC  |  Edited by: Hel O'Ween
The "sort by largest ship" is too hard for me. Not sure if this is even possible without some hefty subqueries ..
Quote:

SELECT
t.typeID,
t.groupID,
t.typeName,
t.raceID,
t.marketGroupID,
t.graphicID,
g.groupName,
r.raceName,
m.marketGroupName marketGroupName,
mp.marketGroupName marketParentGroupName
FROM
chrRaces AS r INNER JOIN
invTypes AS t ON r.raceID = t.raceID INNER JOIN
invGroups AS g ON t.groupID = g.groupID LEFT OUTER JOIN
invMarketGroups AS m ON t.marketGroupID = m.marketGroupID LEFT JOIN
invMarketGroups AS mp ON m.parentGroupID = mp.marketGroupID
WHERE
g.categoryID = 6 AND t.published = 1
ORDER BY
mp.marketGroupName, m.marketGroupName, r.raceName, t.typeName


[Added]
Quote:

i been traying to edit this sql command but can't get it working


For programmers, it's good practice to post what you've tried and possible error messages you've received. This spares those who are willing to help to go through all the failed tries you've already did. It's not that we know everything by heart ...

EVEWalletAware - an offline wallet manager.
Tuborg Uitoh
#3 - 2014-06-10 16:55:52 UTC
Thanks for the replay the sql command you send dosent work :( the colum t.graphicID dos not exist :(
so I trayed removing that from the command witch gives me a list off ships that includes t2 witch is what i need to remove to the list so it only containes t1 ships
Rob Crowley
State War Academy
#4 - 2014-06-10 22:12:16 UTC
Adding to Hel's query:

Quote:
SELECT
t.typeID,
t.groupID,
t.typeName,
t.raceID,
t.marketGroupID,
g.groupName,
r.raceName,
m.marketGroupName marketGroupName,
mp.marketGroupName marketParentGroupName
FROM
chrRaces AS r INNER JOIN
invTypes AS t ON r.raceID = t.raceID INNER JOIN
invGroups AS g ON t.groupID = g.groupID LEFT OUTER JOIN
invMarketGroups AS m ON t.marketGroupID = m.marketGroupID LEFT JOIN
invMarketGroups AS mp ON m.parentGroupID = mp.marketGroupID JOIN
dgmTypeAttributes AS ta ON t.typeID=ta.typeID
WHERE
g.categoryID = 6 AND t.published = 1 AND ta.attributeID=422 AND COALESCE(ta.valueInt,ta.valueFloat)=1
ORDER BY
mp.marketGroupName, m.marketGroupName, r.raceName, t.typeName

graphicID has been moved out of the SQL part of the SDE into the YAML part, so it's not supposed to be in there unless you add it back.
Hel O'Ween
Men On A Mission
#5 - 2014-06-11 15:57:27 UTC
Adding to Rob's answer: use Desmond's import tool to import those missing columns/tables back into your database.

EVEWalletAware - an offline wallet manager.
Tuborg Uitoh
#6 - 2014-06-11 16:58:13 UTC
thanks :P

im traying to use the sql commands but i still se faction and priate ships in the thing is that i need only standart t1 ships to be listet
Rob Crowley
State War Academy
#7 - 2014-06-12 14:31:01 UTC
Well, that's because faction ships (and modules) are technically T1. If you don't want those you have to filter by meta level:

Quote:
SELECT
t.typeID,
t.groupID,
t.typeName,
t.raceID,
t.marketGroupID,
g.groupName,
r.raceName,
m.marketGroupName marketGroupName,
mp.marketGroupName marketParentGroupName
FROM
chrRaces AS r INNER JOIN
invTypes AS t ON r.raceID = t.raceID INNER JOIN
invGroups AS g ON t.groupID = g.groupID LEFT OUTER JOIN
invMarketGroups AS m ON t.marketGroupID = m.marketGroupID LEFT JOIN
invMarketGroups AS mp ON m.parentGroupID = mp.marketGroupID JOIN
dgmTypeAttributes AS ta ON t.typeID=ta.typeID
WHERE
g.categoryID = 6 AND t.published = 1 AND ta.attributeID=633 AND COALESCE(ta.valueInt,ta.valueFloat)=0
ORDER BY
mp.marketGroupName, m.marketGroupName, r.raceName, t.typeName

As a bonus you also get capital ships now, which for some silly reason don't have a tech level.
Tuborg Uitoh
#8 - 2014-06-13 14:35:29 UTC
thanks alot goth it working :P

but will traying to get it working i ran into a few othere sql related questions ;)

is there any way i can get the bp typeid from the item typeid??
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#9 - 2014-06-13 16:54:30 UTC
Tuborg Uitoh wrote:
thanks alot goth it working :P

but will traying to get it working i ran into a few othere sql related questions ;)

is there any way i can get the bp typeid from the item typeid??



It's the producttypeid in invBlueprintTypes (for now, at least)

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Tuborg Uitoh
#10 - 2014-06-16 19:30:34 UTC  |  Edited by: Tuborg Uitoh
thanks alot for the replayed worked perfekt :P

any one that can tell me how i can determin if it's plosibel to see if an item is manufactureable

and i been looking at the tool to adding the ymal into db agien witch i can't get working :( any where i can download a sql version off the static data dump with every thing in it??
Tuborg Uitoh
#11 - 2014-06-19 10:50:35 UTC
bump

still looking for a way to get grapichs detail into the sqllite db
and a way to determin if an item is manufacture able
Hel O'Ween
Men On A Mission
#12 - 2014-06-19 11:34:53 UTC
Tuborg Uitoh wrote:
thanks alot for the replayed worked perfekt :P

any one that can tell me how i can determin if it's plosibel to see if an item is manufactureable

and i been looking at the tool to adding the ymal into db agien witch i can't get working :( any where i can download a sql version off the static data dump with every thing in it??


What didn't work? It's actually pretty straight forward. Off the top of my head (haven't installed it on this machine): edit the *,config with the credentials for your SQL server, copy the YAML and sqlite files to the appropriate folders (there are predefined folders, each holding a "put yaml/sqlite files here" kinde a file).

EVEWalletAware - an offline wallet manager.
Tuborg Uitoh
#13 - 2014-06-19 13:42:46 UTC
well i can't get the sql server installed that ccp recoment :(
second i need the end result to be sqllite like i can download form fuzzworks
and second i been traying to change the EVEMonSDEExternalsToSql.exe.config witch makes no sens but i been told that its this part i need to change

link to pastbin



just that i have no idear what to change and to what