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.
 

Complete "SQL" database, how?

First post
Author
Previous Topic Next Topic
SabuMaru ICE
DLM Enterprises
The Serenity Initiative
#1 - 2015-04-23 13:02:51 UTC
Hi All,

I am trying to create a database with all the items and the relations between the tables
i notice that a lot of tables are hard or almost impossible to find / create

fuzzyworks has helped me a lot so far, but i am still missing a lot of the tables and their content

is there any way someone has a full BAK of the complete database


thanx in advance,
salacious necrosis
Garoun Investment Bank
Gallente Federation
#2 - 2015-04-23 13:26:49 UTC
SabuMaru ICE wrote:
Hi All,

I am trying to create a database with all the items and the relations between the tables
i notice that a lot of tables are hard or almost impossible to find / create

fuzzyworks has helped me a lot so far, but i am still missing a lot of the tables and their content

is there any way someone has a full BAK of the complete database


thanx in advance,


Steve should comment here, but I don't believe he's deleting any foreign key relations that already exist in the raw SDE dump. So if it's not in Steve's version, it's not in the original, which means you'd have to add it yourself.

A year or so ago I implemented most of these in a Hibernate layer. I can dig that up if you're interested. I didn't spend a lot of time optimizing by adding the appropriate foreign keys in the DB, so some of the joins may perform poorly.

For example, here's what the header of my InvTypes class looks like:

Quote:
@Entity
@Table(name = "invtypes")
public class InvTypes implements IInvTypes {
@Id
private int typeID;
private Integer groupID;
private String typeName;
private String description;
private double mass;
private double volume;
private double capacity;
private int portionSize;
private Integer raceID;
private double basePrice;
private boolean published;
private Integer marketGroupID;
private double chanceOfDuplicating;
private Integer graphicID;
private Double radius;
private Integer iconID;
private Integer soundID;
private Integer factionID;
private String sofDnaAddition;
private String sofFactionName;

// Referrals to other tables
@ManyToOne(optional = true, targetEntity = InvGroups.class)
@JoinColumn(name = "groupID", insertable = false, updatable = false)
private InvGroups groupIDObject;
@ManyToOne(optional = true, targetEntity = EveGraphics.class)
@JoinColumn(name = "graphicID", insertable = false, updatable = false)
private EveGraphics graphicIDObject;
@ManyToOne(optional = true, targetEntity = InvMarketGroups.class)
@JoinColumn(name = "marketGroupID", insertable = false, updatable = false)
private InvMarketGroups marketGroupIDObject;
@ManyToOne(optional = true, targetEntity = EveIcons.class)
@JoinColumn(name = "iconID", insertable = false, updatable = false)
private EveIcons iconIDObject;

...


Not sure if this is what you're looking for.

Use EveKit ! - Tools for EVE Online 3rd party development
SabuMaru ICE
DLM Enterprises
The Serenity Initiative
#3 - 2015-04-23 13:53:02 UTC
Thanks for the quick response

bellow a list of the tables i have so far...
missing invBlueprintTypes forinstance
and can't figure out how to get it

Quote:

TABLE NAME # Records
agtAgentTypes 12
agtAgents 10975
agtResearchAgents 797
chrAncestries 42
chrAttributes 5
chrBloodlines 15
chrFactions 20
chrRaces 8
crpActivities 20
crpNPCCorporationDivisions 380
crpNPCCorporationResearchFields 48
crpNPCCorporationTrades 17923
crpNPCCorporations 234
crpNPCDivisions 29
dgmAttributeCategories 27
dgmAttributeTypes 1799
dgmEffects 3580
dgmExpressions 17235
dgmTypeAttributes 156071
dgmTypeEffects 36515
eveUnits 57
industryActivity 13428
industryActivityMaterials 22280
industryActivityProbabilities 1034
industryActivityProducts 4163
industryActivitySkills 16314
industryBlueprints 3219
invCategories 39
invContrabandTypes 426
invControlTowerResourcePurposes 4
invControlTowerResources 339
invFlags 133
invGroups 998
invItems 531412
invMarketGroups 1774
invMetaGroups 14
invMetaTypes 4171
invNames 519863
invPositions 508331
invTypeMaterials 33364
invTypeReactions 372
invTypes 22531
invUniqueNames 365405
mapRegions 99
mapSolarSystems 0
mapUniverse 2
planetSchematics 68
planetSchematicsPinMap 496
planetSchematicsTypeMap 203
ramActivities 9
ramAssemblyLineStations 4393
ramAssemblyLineTypeDetailPerCategory 666
ramAssemblyLineTypeDetailPerGroup 1844
ramAssemblyLineTypes 134
ramInstallationTypeContents 362
staOperationServices 795
staOperations 55
staServices 27
staStationTypes 69
staStations 5185
translationTables 32
trnTranslationColumns 32
trnTranslationLanguages 8
trnTranslations 293088
warCombatZoneSystems 171
warCombatZones 4
Mr Mac
Dark Goliath
#4 - 2015-04-23 14:08:24 UTC
SabuMaru ICE wrote:
Thanks for the quick response

bellow a list of the tables i have so far...
missing invBlueprintTypes forinstance
and can't figure out how to get it



No such table in Scylla SDE
but there is blueprints.yaml
SabuMaru ICE
DLM Enterprises
The Serenity Initiative
#5 - 2015-04-23 14:22:57 UTC
/
Mr Mac wrote:
SabuMaru ICE wrote:
Thanks for the quick response

bellow a list of the tables i have so far...
missing invBlueprintTypes forinstance
and can't figure out how to get it



No such table in Scylla SDE
but there is blueprints.yaml


indeed..but i have no experience in changing into an MS-SQL acceptable format
noone have a link to the converted YAML files so i can just use it ?
Zifrian
The Frog Pond
Ribbit.
#6 - 2015-04-24 11:25:30 UTC
SabuMaru ICE wrote:
/
Mr Mac wrote:
SabuMaru ICE wrote:
Thanks for the quick response

bellow a list of the tables i have so far...
missing invBlueprintTypes forinstance
and can't figure out how to get it



No such table in Scylla SDE
but there is blueprints.yaml


indeed..but i have no experience in changing into an MS-SQL acceptable format
noone have a link to the converted YAML files so i can just use it ?

Steve does all of these conversions. I built my own importer. Desmont McCallock has built a tool to build the full ms-sql version from the SDE files if you want that.

Also, the universe tables are in the SQLite file that comes with the SDE download. Again, Steve/Desmont has those in their conversions as well.

Maximze your Industry Potential! - Download EVE Isk per Hour!

Import CCP's SDE - EVE SDE Database Builder
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
#7 - 2015-04-24 12:01:45 UTC
First:

CCP don't have the foreign key relationships. They don't actually use them on static data. (after all, they're there for consistency. On static data, that's not a concern. There's no benefit to having them, and they just take up space(minimal, but still some))

The database exports from my site do not have anything removed from them. The process is:
Convert the sqlite universe data to mysql
convert the MS-SQL data to mysql
create a few new tables and import some of the data from the yaml into them. (Not all, because the importing requires me to write a script to manage each bit (and possibly new tables. I prefer not to change the ones from CCP themselves). If I haven't written one yet, I've not needed it, and I've not been specifically asked for that data yet)

invBlueprintTypes, for example, is an old table. It no longer exists, as CCP have dropped it. However, all the blueprint data has been loaded into the industry* tables.


If there are specific things you're looking for, feel free to ask here, or send me an evemail.

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter
Hel O'Ween
Men On A Mission
#8 - 2015-04-24 16:24:44 UTC
Zifrian wrote:

Desmont McCallock has built a tool to build the full ms-sql version from the SDE files if you want that.


Here's the link to Desmont's much appreciated tool: https://forums.eveonline.com/default.aspx?g=posts&t=324435

EVEWalletAware - an offline wallet manager.
SabuMaru ICE
DLM Enterprises
The Serenity Initiative
#9 - 2015-04-29 09:53:01 UTC
Thanx All

some of the info out there is outdated.. especially the "landscape" picture of how the relations are