EVE Technology Lab

Hi,

I successfully downloaded and restored the eve static dump database file. Now i would like to know if there is a way to write a query that returns something like every modules in the game, with their unique id , name and description.

Is this even possible ? I tried looking on evedev wiki, but found nothing concerning queries for items.

sorry if this is a repost. If there is any good tutorial around, please refer me to it. ( or if you can write the query itself, it would be even better) I have basic SQL knowledge.

Thank you so much :) from there i should be able to figure out the rest.

i'm more used to oracle and sql, and i didn't learn any LEFT or INNER or such terms. usually it was only WHERE Table1.col=Table2.col AND Table1.id= "value" so yeah i guess i'll check it out !

For those who really dont like SQL, you can always use Evemon's binaries. there are useful xml files in there too.

Anyone want to work with the SDD needs to spend some time to really learn about database normalization as well as how to make optimized joins etc as it'll pay off. As pointed out you'll be using a lot of joins and they can be either of the WHERE Table1.col=Table2.col or the full join syntax. Once you get use to the full join syntax it can be easier to use when you join 3 or more tables and you should end up with fewer queries that don't seem to work like you expected. Outer joins can be especially useful when working with optional data results.

I get what your trying to do.. and I don't think there is a very easy way to do it. Recursion is a complicated subject. I did find this page online;

http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html

Although that's for MySQL, your SQL looks different from what I'm using.. !

In their example, they gave this;
SELECT
CONCAT( SPACE(2*COUNT(parent.nodeid)-2), PersonName(child.nodeid) )
AS 'The Shakespeare Family Tree'
FROM nestedsettree AS parent
INNER JOIN nestedsettree AS child
ON child.leftedge BETWEEN parent.leftedge AND parent.rightedge
GROUP BY child.nodeid
ORDER BY child.leftedge;
+-----------------------------+
| The Shakespeare Family Tree |
+-----------------------------+
| (indented list of nodes) |
+-----------------------------+

Ahh, this guy sorta cheated in a way, with the ordering stored manually.. The best way I can think of this without being at home would be to add in so many INNER JOINS that at some point you return null sets. The majority of methods involve either setting up database views, or stored procedures. If you use a scripting language, like php, you just write either a recursive function, or loop till you can't pull anymore data. (IE; you reach parentGroupID = NULL in invMarketGroups)

Fascinating, apparently MySQL 5.x is supposed to have a connect by equivalent, but due to bugs, they disabled it's functionality.