Originally by: Somahai Visidious

---

Originally by: Quutar

---

EVE-SQL To MySQL. It is a text file saying what I did to massage the eve sql files to fit inside of MySQL... and a sql file with primary keys and foreign keys... I think I got all of them.

---

The first thing needed on this new file to do is convert all of the empty commas pairs into nulls...
do a global replace of:
",," to ",null"

Should be:
",," to ",null,"

Apart from that, I haven't had any other issues (yet!). Great stuff. That "annoying text file" was appreciated (certainly saved some time on my end). :)

---

Originally by: Saldun Zexu

---

I've downloaded last updates. seems CCP forgot to put CREATE TABLE (); in files:
staOperations,
staOperationsServices,
staServices,
staStations,
and staStationTypes.

I'm using MySQL 5.0.21 and I imported all files successful but I don't know how to set p.key and/or f.key. I saw diagram in here but not enough.

thanks

---

Originally by: DaOpa

---

Hmmm can anyone provide the relationships between the tables ?

Theres way to many .. and my head went boom just importing basic csv into a mdb :(

---

Quote:

---

SELECT (
SELECT typeName
FROM invTypes
WHERE typeID = T.requiredTypeID
) "typeName", ROUND( T.quantity * 1.10 ) "required", C.typeQuantity "gathered"
FROM TL2MaterialsForTypeWithActivity T
LEFT OUTER JOIN chrMinerals C ON ( T.requiredTypeID = C.typeID )
AND (
C.charID = '243976002'
)
JOIN invTypes iT ON iT.typeID = T.requiredTypeID
WHERE T.typeID = '650'
AND T.activity = 6
ORDER BY iT.typeID;

---

Quote:

---

Tritanium
Pyerite
Mexallon
Isogen
Nocxium
Zydrine
Megacyte
Industrial Construction
Terran Molecular Sequencer
R.A.M.- Cruiser Tech

---

Quote:

---

Tritanium
Pyerite
Mexallon
Isogen
Nocxium
Zydrine
Megacyte
Criminal Connections
Hacker Deck - Hermes
Hyper Net Uplink

---

Originally by: Quutar

---

http://utena.otakuvideo.com/~quutar/uploads/Main/EVE-SQL_To_MySQL.zip

I guess its still a work inprogress... but i posted maybe 4-5 links above you what i did to convert it to mysql

---

Originally by: Quutar

---

i am already doing that for my self...
but i wanted ti to be formated a bit easier... with the relationships between the entries more obvious...

for instance... when you click a agent, the agent page will bring up links to the corp, quality, level, faction, station, etc... each of wich would allow you to explore that relationship

when you click on a module... if you click one of the attributes, it will bring up that attribute, which as a list of all the modules that have that attribute, so you could start with a cap recharger, click the cap recharge atribute, and see a list of modules that do that, sort it, pick a cap power relay from the list, go into that view, see a linked blue print, go into that view, click a mineral, see all the bpos that use that minerals... etc

basically a relationship explorer... something for learning... not for "quick answers"

---

Originally by: Narusegawa Naru

---

Iteron productID = 650

My SQL query (this is from mySQL but is pretty generic SQL anyway). chrMinerals is a table containing my minerals, using the same typeID as minerals.

Quote:

---

SELECT (
SELECT typeName
FROM invTypes
WHERE typeID = T.requiredTypeID
) "typeName", ROUND( T.quantity * 1.10 ) "required", C.typeQuantity "gathered"
FROM TL2MaterialsForTypeWithActivity T
LEFT OUTER JOIN chrMinerals C ON ( T.requiredTypeID = C.typeID )
AND (
C.charID = '243976002'
)
JOIN invTypes iT ON iT.typeID = T.requiredTypeID
WHERE T.typeID = '650'
AND T.activity = 6
ORDER BY iT.typeID;

---

If I use activity = 1 I get nothing.

Activity 6

Quote:

---

Tritanium
Pyerite
Mexallon
Isogen
Nocxium
Zydrine
Megacyte
Industrial Construction
Terran Molecular Sequencer
R.A.M.- Cruiser Tech

---

Activity 7

Quote:

---

Tritanium
Pyerite
Mexallon
Isogen
Nocxium
Zydrine
Megacyte
Criminal Connections
Hacker Deck - Hermes
Hyper Net Uplink

---

Why would 2 skill books be in there as constuction components? I'm just trying to get a list of manufacturing components of whats required to build a product.

---

Originally by: Quutar

---

http://utena.otakuvideo.com/~quutar/uploads/Main/EVE-SQL_To_MySQL.zip

I guess its still a work inprogress... but i posted maybe 4-5 links above you what i did to convert it to mysql

---

Originally by: Bombasy

---

Edited by: Bombasy on 07/07/2006 19:07:11

Originally by: Quutar

---

http://utena.otakuvideo.com/~quutar/uploads/Main/EVE-SQL_To_MySQL.zip

I guess its still a work inprogress... but i posted maybe 4-5 links above you what i did to convert it to mysql

---

Hey Quu, here's a few sed commands for your howto:
's/CHAR([0-9]*[03-9][0-9][0-9])/TEXT/g'
's/dbo[.]//g'
's/UNKNOWN/INTEGER/g'
's/\([,(]\)[[:space:]]*\([,)]\)/\1null\2/g' -e 's/\([,(]\)[[:space:]]*\([,)]\)/\1null\2/g'


(the last one has to be done twice (thus the -e) to prevent having to fiddle around with the hold/pattern buffer and overly complicating the damn thing just to take care of 2x blank fields in a row.)

These work for translating the CHAR parts and removing the "dbo." from the tables, if CCP decides to make a 256-299 char field then the regex will have to change, but until then it works. It also converts the empties to nulls and allows for some spacing flexibility, just in case.

---

Originally by: Inanna Sumer

---

Edited by: Inanna Sumer on 10/07/2006 11:38:31

Originally by: Bombasy

---

Edited by: Bombasy on 07/07/2006 19:07:11

Originally by: Quutar

---

http://utena.otakuvideo.com/~quutar/uploads/Main/EVE-SQL_To_MySQL.zip

I guess its still a work inprogress... but i posted maybe 4-5 links above you what i did to convert it to mysql

---

Hey Quu, here's a few sed commands for your howto:
's/CHAR([0-9]*[03-9][0-9][0-9])/TEXT/g'
's/dbo[.]//g'
's/UNKNOWN/INTEGER/g'
's/\([,(]\)[[:space:]]*\([,)]\)/\1null\2/g' -e 's/\([,(]\)[[:space:]]*\([,)]\)/\1null\2/g'


(the last one has to be done twice (thus the -e) to prevent having to fiddle around with the hold/pattern buffer and overly complicating the damn thing just to take care of 2x blank fields in a row.)

These work for translating the CHAR parts and removing the "dbo." from the tables, if CCP decides to make a 256-299 char field then the regex will have to change, but until then it works. It also converts the empties to nulls and allows for some spacing flexibility, just in case.

---

To fully "MySQLize" the CHAR's you may want to add
's/CHAR([0-9]\{1,3\})/TINYTEXT/g'

to the list of sed patterns. might also reduce storage size a bit (since text/tinytext use varchar).

edit: forgot to escape the curly braces.

---

Originally by: Larshus Magrus

---

I just had to manage the data into mysql for a project im tinkering with.

I completely forgot how craptastic M$Sql is. What a fing mess. It violates so many ansi conventions.. well lets just put it this way... I'm sure the only thing holding that db running over there is ducktape and twine at this point.

---

Originally by: Meccano

---

I've been banging my head against these csv file for a few days now... can any recomend a program that will actually open it without all the descriptions messing up the rows?

---

Quote:

---

CREATE TABLE dbo.agtConfig(
agentID INTEGER,
k CHAR(50),
v CHAR(4000));

---

Quote:

---

CREATE TABLE dbo.agtConfig(
agentID INTEGER,
k CHAR(50),
v CHAR(4));

---