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

Forum Index

EVE Forums » EVE Technology and Research Center » EVE Technology Lab » SDE MySQL - trying to establish all table relations

Topic is locked indefinitely.

SDE MySQL - trying to establish all table relations
Author

Previous Topic Next Topic

Pulsahr Elenfea

Native Freshfood

Minmatar Republic

Likes received: 1

#1 - 2016-01-21 11:29:37 UTC

Hello,
I'm trying to make a reliable DB Schema, starting from the full mysql dump linked here.

Processing them alphabetically, I'm currently stuck on the dgmExpressions table.

A simple problem (solved)
Here is an example of what I had to guess : I had to set null an expressionAttributeID in the dgmExpressions table, because otherwise the constraint to dgmAttributeTypes.attributeID fails.
That previous example is the easiest problem I encountered during my fastidious work.

A bigger problem (not solved)
Now I'm stuck, and here is a why (if you're not familiar with SQL stuff, don't bother to continue reading).
Currently, I don't know the relation between dgmTypeEffects and dgmTypeAttributes. They both have a PK including a "typeID" int(11) field coupled with a smallint(6) field, which looks like FK to respectivly dgmEffects and dgmAttributeTypes.
But trying to consider FK from one typeID to another fails miserably in both ways : 9k missing records in one case, 11k in another.
I didn't even considered invTypes.typeID, doesn't make sense to me. Maybe it's because the data is partial.

Halp !
So here is my question : is there somewhere something that show table relations for the SDE database ? Or better, is there somewhere a sql script that show indexes and constraints for all the tables ?

Any help would be greatly appreciated.

Thank you o7

Found in CCP developper License Agreement :

Force Majeure. If either Party should fail in the performance of any obligation under this Agreement by reason of [...], orbital bombardment, or other cause [...]

They're prepared.

Desmont McCallock

Likes received: 655

#2 - 2016-01-21 12:48:03 UTC | Edited by: Desmont McCallock

First let me specify that I'm using the MSSQL DB the EVSDEToSQL tool generates with the latest SDE (YC-118-1).

If you do:

select * from dgmTypeEffects
left join invTypes on dgmTypeEffects.typeID = invTypes.typeID
where invTypes.typeID is null

you won't get any results meaning that all FK of dgmTypeEffects match a type in invTypes.

If you do:

select * from dgmTypeAttributes
left join invTypes on dgmTypeAttributes.typeID = invTypes.typeID
where invTypes.typeID is null

you will get 12 rows results meaning that some FK of dgmTypeAttributes don't match a type in invTypes.

Usually typeID refers to the typeID of InvTypes.

367
Aineko Macx Likes received: 367	#3 - 2016-01-21 12:55:49 UTC 1 I had put together the foreign keys for the Crucible SDE back in 2013. It will surely need to be adapted to the current schema, but it might serve as a start: http://pastebin.com/5dS84J8X iveeCore 3.0: The PHP engine for industrial activities and CREST library

Pulsahr Elenfea

Native Freshfood

Minmatar Republic

Likes received: 1

#4 - 2016-01-21 13:02:53 UTC | Edited by: Pulsahr Elenfea

Indeed, typeID was used before to reference invTypes.typeID, but I thought that had no sense here, my mistake, this connects perfectly for both.
Thank you for pointing this out.

The question remains for dgmExpressions.expressionTypeID which can't reference invTypes (17k missing). Any hint on this ?

I still have no clue about dgmExpressions.expressionGroupID, which can't reference invGroups (smallint 6 vs int 11, and even if ignoring this, 17k missing).

I'm pretty sure I'll encounter other problems like these. Is the SDE data partial ? That would explain some FK fails (like the problem solved I used as an example).

Aineko Macx wrote:

I had put together the foreign keys for the Crucible SDE back in 2013. It will surely need to be adapted to the current schema, but it might serve as a start: http://pastebin.com/5dS84J8X

Oh that is some good starting point, you're saving me a lot of time. Thanks !

Found in CCP developper License Agreement :

Force Majeure. If either Party should fail in the performance of any obligation under this Agreement by reason of [...], orbital bombardment, or other cause [...]

They're prepared.

Aineko Macx

Likes received: 367

#5 - 2016-01-21 13:16:18 UTC

CCP doesn't use foreign keys on their DB, thus it's no surprise there are cases of missing relations and even datatype mismatches due to changes over the years. Also the SDE is a subset of CCPs universe DB made with some scripted tools AFAIK, so sometimes these have not been updated to export/allow new or changed data, resulting in incomplete relations.

iveeCore 3.0: The PHP engine for industrial activities and CREST library

Pulsahr Elenfea

Native Freshfood

Minmatar Republic

Likes received: 1

#6 - 2016-01-21 13:25:37 UTC

Aineko Macx wrote:

Yep, I guessed so. I'm still trying to do a visual representation of the DB, because that's the kind of thing I need, to work properly. I'll share it here once finished if interested.

Found in CCP developper License Agreement :

Force Majeure. If either Party should fail in the performance of any obligation under this Agreement by reason of [...], orbital bombardment, or other cause [...]

They're prepared.

Dragonaire

Here there be Dragons

Likes received: 92

#7 - 2016-01-21 13:31:46 UTC

My question is why you even think you need to have a complete schema with all the FKs when you're not going to be making changes to the data which is the only time when FKs have any benefit? Everything you should be doing with the SDE is going to be some kind of select with joins that get no benefit from the FK at all. Depending on your queries it 'might' be beneficial to add a few secondary indexes but since most of your joins should be on the primary keys already they probably are of limited use. If you just are wanting to know the relationships to better understand the tables it's probably better just to look at past posts about the same thing or if you don't find one for what you are trying to do just ask as there are many people that have been working with the SDE for years now through it's many versions that can fill in the missing 'piece' usually.

Finds camping stations from the inside much easier. Designer of Yapeal for the Eve API. Check out the Yapeal PHP API Library thread.


Aineko Macx Likes received: 367	#8 - 2016-01-21 13:31:54 UTC Very old, but might help you as well: http://games.chruker.dk/eve_online/files/dbo_002.pdf iveeCore 3.0: The PHP engine for industrial activities and CREST library

Pulsahr Elenfea

Native Freshfood

Minmatar Republic

Likes received: 1

#9 - 2016-01-21 15:32:50 UTC

Dragonaire wrote:

My question is why you even think you need to have a complete schema with all the FKs.

Because I like things done correctly, and as I'm not limited by work deadlines, I want to make it the best I can ;)

Aineko Macx wrote:

Very old, but might help you as well: http://games.chruker.dk/eve_online/files/dbo_002.pdf

Thanks, I'll keep an eye on it.

Found in CCP developper License Agreement :

Force Majeure. If either Party should fail in the performance of any obligation under this Agreement by reason of [...], orbital bombardment, or other cause [...]

They're prepared.


Desmont McCallock Likes received: 655	#10 - 2016-01-21 17:48:53 UTC 1 Pulsahr Elenfea wrote: Because I like things done correctly, and as I'm not limited by work deadlines, I want to make it the best I can ;) And I thought I was the only one with OCD in this Universe.