EVE Technology Lab

 
2 Pages12Next page
 

EVE SDE Database Builder

First post
Author
#1 - 2016-11-20 20:43:28 UTC  |  Edited by: Zifrian
I was working on getting the YAML import done for EVE IPH and basically ended up writing a new program anyway. So, I added a few features and expanded it to build different types of databases and now I have an app I’m sharing with you all.

EVE SDE Database Builder is a Windows app that basically lets anyone run the SDE builds for 6 different Database types whenever they want and they can customize the import by language type and selecting the data they want or do not want (does anyone use charBloodlines?). Users just need to download the SDE from the dev site and save the folders for use.

I know there are other uploaders out there but I figured I’d make my own and share it. I’d appreciate some testing to make sure it’s working properly and feedback on its value.

Main links for the application:

Key features:
  • Imports all YAML files in about 30 minutes (the universe files take about 23 minutes alone).
  • Allows the selection of specific YAML files so users don’t have to import or build the entire database each time.
  • Imports the SDE YAML files into Microsoft Access, SQLite, and CSV files with the option to save CSV to SSV – Semi-colon separated values with European decimal format (10.000,00) for use in Excel by non-US decimal format users.
  • Imports the SDE YAML files into local servers for Microsoft SQL Server, PostgreSQL, and MySQL. To use these import types, you need to have a local server installed on your machine to connect to.
  • Settings to import tables with translated fields for English, French, German, Japanese, Chinese, and Russian. When a field is translatable, the program will import the selected language.
  • Threaded processing allows for increasing import times. Users can select to use maximum threads (no limit) or a number of 1-24 threads depending on their system. Users can set the threads in the File menu.
  • Contains an updater function to update the program when changes are uploaded to GitHub.

Basic functions:
  • Download the SDE files under ‘Static Data Export’ here and extract to a folder on your computer.
  • Select the SDE folder (the ‘sde’ folder part of whatever folder you extracted the data into).
  • If you plan to make an SQLite, Access, or CSV/SSV database, select the final folder where the database will be saved (CSV ‘database’ will be a new folder.
  • Enter the name of the database in the text box provided and enter other information to connect to your particular database type as necessary.
  • Select import language
  • Save Settings – you should see a list of yaml files in the file list. If not, you didn’t save the correct yaml SDE file folder with the base ‘sde’ folder as the root.
  • Select the files you want to import (save settings will save what you checked as well)
  • Press ‘Build Database’ to begin.

Database table updates:
  • mapDisallowedAnchorCategories (new) and mapDisallowedAnchorGroups (new) will contain the solarSystemID and the categoryID and groupID respectively.
  • mapItemEffectBeacons table (new) contains the itemID and the effectBeaconTypeID associated with the itemID (used for secondary suns in wormholes).
  • invTypes includes the field [packagedVolumes], which for ships and containers is the packaged volume of the item. For all other items, it is the volume. Information from this thread.
  • The mapDenormalized table no longer has the groupID field (because it’s not in the YAML and it’s a pain to look up for duplicate data). The groupID field can be looked up by linking it to the typeID in invTypes.
  • The program may import translation tables for internal use in importing tables with translation data however, they will not be imported into the final database unless checked.

I’d appreciate feedback from testing or looking through the code for improvements. I’m keeping this open source (MIT license) and I’m happy to let the community support it if it is valuable. I know it’s in VB.net, which many people loathe, but it’s simple and updates should be relatively easy when necessary. If you have any questions or bug reports, please post them here.

Thanks

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

Import CCP's SDE - EVE SDE Database Builder

#2 - 2016-11-20 20:44:58 UTC  |  Edited by: Zifrian
Known Issues
  • Users may get an ‘Exception of type System.OutOfMemoryException was thrown.’ message while running the program using the max threads option. The program still continues to run but it is unclear whether the error caused the program to miss importing data. Recommend running the import with a limit on the number of threads, which you can select in the File menu.

Updates

  • 1.0.0.0 Fixed an issue where French import would fail on groupIDs for insufficient field length.
  • 1.0.6182.31830
  • - Renamed 'Server Name' to 'Instance Name' to clarify the data required to connect to an MsSQL Server database on the local host.
    - Added a new table: dgmEffectsModifierInfo This table will have data parsed from the dgmEffects.ModifierInfo field, which was stored as a yaml string. The fields in the table are effectID (link to dgmEffects), func, modifiedAttributeID, modifyingAttributeID, and operator.
    - Removed ModifierInfo from the dgmEffects table
  • 1.0.6207.23573
  • - Fixed bug with dgmEffects table not loading for CSV where the string
    searched wasn't correct for the local db (datatable).
    - Added the ability to log into sql server with a password and user id.
    - The password text box now shows *'s for text instead of the password.

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

Import CCP's SDE - EVE SDE Database Builder

#3 - 2016-11-21 20:29:38 UTC
Looks good so far. A couple of points:

There doesn't seem to be a vbproj file in EVE-SDE-Database-Builder-master\EVE-SDE-Database-Builder-master\EVE SDE Database Builder so the sln file can't load the project.

Any chance of enabling username / password for MSSQL?
Vote Steve Ronuken for CSM
#4 - 2016-11-21 20:48:25 UTC
Quick question:

How are you handling the blueprint information? ramTypeRequirements?

(mostly asking so, if questions come up while I'm around, I can answer sensibly :D more options is a very good thing)

Woo! CSM XI!

Fuzzwork Enterprises

Twitter: @fuzzysteve on Twitter

#5 - 2016-11-22 02:28:34 UTC
EveMart Representative wrote:
Looks good so far. A couple of points:

There doesn't seem to be a vbproj file in EVE-SDE-Database-Builder-master\EVE-SDE-Database-Builder-master\EVE SDE Database Builder so the sln file can't load the project.

Any chance of enabling username / password for MSSQL?

OK I added the file. I think I hacked it on another project and caused it to not update since I set it to my local settings for debug folders, etc. If you use it, it will fail on first run until you change it. I'm sure git has a way to allow for this but I'm not sure how to do it. I ignore it in another app but not sure why it still works.

On username and pass, I can probably do that. I didn't have a server with user/pass to connect to to try it. Let me see if I can get it updated tomorrow to test.

Steve Ronuken wrote:
Quick question:

How are you handling the blueprint information? ramTypeRequirements?

(mostly asking so, if questions come up while I'm around, I can answer sensibly :D more options is a very good thing)

I basically handle the blueprint information like you do with one small change. I don't have a separate table for probabilities. I just save that information in industryActivityProducts - if it's activityID 1, then the probability is one. All the other tables match yours. I don't use ramTypeRequirements.

One thing I was wondering about is if we could combine these tables a bit more so it's not all confusing. In the past I combined the skills and the materials into one column and put a 'consume' flag with 1 for all mats, and 0 for skills, which is similar to what it was in the past. Although maybe people want skills and they've used your conversions for a while probably. I'm open to whatever though, as long as we are consistent.

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

Import CCP's SDE - EVE SDE Database Builder

#6 - 2016-11-22 20:14:24 UTC
You don't need to "combine tables", what you need is to define views that make sense for specific purposes.
Many people would still need to get their own views going, you can't possible satisfy everybody at once.
But you can show the way.

Two most common elements in the universe are hydrogen and stupidity. -- Harlan Ellison

#7 - 2016-11-23 00:22:42 UTC
Tonto Auri wrote:
You don't need to "combine tables", what you need is to define views that make sense for specific purposes.
Many people would still need to get their own views going, you can't possible satisfy everybody at once.
But you can show the way.

That's true, but the data we are talking about in blueprints.yaml had no pre-defined tables except in the old schema. CCP removed a lot of fields from that in the new yaml file. So Steve and I are creating what we think works best to store the data from the yaml file. I don't think it's necessary to recreate invBlueprints and ramTypeRequirements but having consistency in what data we combine or not combine to make it easier for 3rd party devs to understand is.

The lack of a consistent database schema has always been a lingering issue with the SDE after they started the switch to yaml. There is data duplication all over the place and there isn't really a change log (that I can find anyway) for the yaml files. So we make due with what we figure out here in the tech forum.

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

Import CCP's SDE - EVE SDE Database Builder

#8 - 2016-12-04 15:21:15 UTC
Hi Zifrian,

first off, major thanks for this! I was dreading having to figure out how to import these yaml files myself :)

I am having a problem though... The builder keeps failing with unable to initialize database and gives an error about not being able to connect to the DB Server.

I have a SQL 2014 server where the files are located and I can telnet on port 1433 to the sql instance on the same name that i'm entering into the builder app (i've tried the server's IP as well).

I have an existing web app on the same sql server vm that can connect without any issues so i'm not sure why the builder is failing to connect/find the server.

I've even restarted but still no luck getting the app to connect/build the DB

Any thoughts?

Cheers,

Cilegon
#9 - 2016-12-04 17:06:57 UTC
So i downloaded the source code and took a look at how you connect to SQL Server:

Public Sub New(ByVal DatabaseName As String, ByVal InstanceName As String, ByRef Success As Boolean)

Conn = New SqlConnection(String.Format("Server={0}\{1};Trusted_Connection=True; Initial Catalog=master; Integrated Security=True;Connection Timeout=60;",

Can I assume that Server={0}\{1} = Server=DatabaseName\InstanceName

Does {0} = the passed in DatabaseName variable
Does {1} = the passed in InstanceName variable

If so, i'm surprised this works for anyone :)

I thought Server= should only be the InstanceName, or possibly different if it's a named instance, in which case it would be Servername\InstanceName

for a test, I changed it to be: Server=localhost;

And that seemed to work - it created the DB at least

Sadly I'm getting wife aggro and no more time to test until later this evening, but will report back with findings :)

#10 - 2016-12-04 21:54:35 UTC
Home now... And the DB has been built and fully loaded, woohoo!

Again, huge thanks for this Zifrian, so much time saved!

So i made 2 changes to the msSQLDB.vb file

2 instances of this: Server={0}\{1}

were changed to simply: Server={1}

So now it will connect to the Server Name you enter in the GUI
#11 - 2016-12-04 22:22:40 UTC
Cilegon wrote:
Home now... And the DB has been built and fully loaded, woohoo!

Again, huge thanks for this Zifrian, so much time saved!

So i made 2 changes to the msSQLDB.vb file

2 instances of this: Server={0}\{1}

were changed to simply: Server={1}

So now it will connect to the Server Name you enter in the GUI


Awesome. Thanks for testing that out.

I was curious if people could connect since it's a bit funky. The way I tried to connect it is to use the "server name" format from the SQL Management Studio, and for me it's HostName\InstanceName.

So perhaps I'm just labeling it wrong. The Environment.MachineName works for the HostName and then the instance is just what you put in the box. Is there another way to do it? Did you try putting in just the instance name? I'll change the name of the label if that's the case. I can see how it's confusing since you need to strip the host from the connection string but it says "server".

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

Import CCP's SDE - EVE SDE Database Builder

#12 - 2016-12-05 12:26:20 UTC
So the problem is what if there is no instance name?

In almost all cases SQL instances are installed as the default instance vs. a named instance.

In this case, the instance name is just the machine name. So by adding anything in the Instance Name, it will fail to connect to a default instance.

I would suggest skipping the part about automatically adding the Environment.MachineName, and just let people add the full name to the Instance box.

maybe rename that to SQL Server Name or something like that.

In this way, if it is a default instance, they would just add the machineName, or if its a named instance, they could add MachineName\InstanceName

or a checkbox for 'default instance', which would grey out the instance name and your passed in MachineName would work fine then.

hope this makes sense!

Cheers,

Cil
Almost Awesome.
#13 - 2016-12-06 01:36:25 UTC
I've started work on an Access database for Eve industry as a class project. For the project, I'm just using CSV imports, but after it's turned in I was planning on developing it into something for release. Would you mind if I get in touch with you after I've submitted it?
#14 - 2016-12-09 02:20:40 UTC
Cilegon wrote:
So the problem is what if there is no instance name?

In almost all cases SQL instances are installed as the default instance vs. a named instance.

In this case, the instance name is just the machine name. So by adding anything in the Instance Name, it will fail to connect to a default instance.

I would suggest skipping the part about automatically adding the Environment.MachineName, and just let people add the full name to the Instance box.

maybe rename that to SQL Server Name or something like that.

In this way, if it is a default instance, they would just add the machineName, or if its a named instance, they could add MachineName\InstanceName

or a checkbox for 'default instance', which would grey out the instance name and your passed in MachineName would work fine then.

hope this makes sense!

Cheers,

Cil

Yeah, that makes sense. I've updated the program so it now just takes the full connection string.

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

Import CCP's SDE - EVE SDE Database Builder

Gallente Federation
#15 - 2016-12-09 14:23:02 UTC
Hi,

I'm trying to use this tool to create a mysql database, but running into problems.

i downloaded the sde files and the tool binaries.

I'm on windows 7.

i selected the mysql option, got the sde folder.

then i hit " build database" and it says no files selected for import.

I figured out i needed to click on save settings. Then a list of checkbox appears, and i can chose what tables i want to convert.

i picked categoryID for a test, then i hit "build database"

i then get a non-critical error : " The Path cannot be all whitespace or empty"

then the programs loads translations.yaml, and proceeds to make the mySQL database.

The table is created but is empty of any data.

at the end, i get the following error :

An Unhandled Exception has occured and the program will now close.
Copy the data below and send to developer.

Source: Microsoft.VisualBasic
Message: File 'C:\xampp\mysql\data\eve\invCategories.csv' not found (Errcode: 2 "No such file or directory")
Raw Error Text: MySql.Data.MySqlClient.MySqlException (0x80004005): File 'C:\xampp\mysql\data\eve\invCategories.csv' not found (Errcode: 2 "No such file or directory")
at Microsoft.VisualBasic.CompilerServices.Symbols.Container.InvokeMethod(Method TargetProcedure, Object[] Arguments, Boolean[] CopyBack, BindingFlags Flags)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.CallMethod(Container BaseReference, String MethodName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, BindingFlags InvocationFlags, Boolean ReportErrors, ResolutionFailure& Failure)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.ObjectLateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)
at EVE_SDE_Database_Builder.frmMain.BuildEVEDatabase(Object UpdateDatabase, DatabaseType DatabaseType)
at EVE_SDE_Database_Builder.frmMain.btnBuildDatabase_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
#16 - 2016-12-10 02:48:18 UTC
Optionyst wrote:
Hi,

I'm trying to use this tool to create a mysql database, but running into problems.

i downloaded the sde files and the tool binaries.

I'm on windows 7.

i selected the mysql option, got the sde folder.

then i hit " build database" and it says no files selected for import.

I figured out i needed to click on save settings. Then a list of checkbox appears, and i can chose what tables i want to convert.

i picked categoryID for a test, then i hit "build database"

i then get a non-critical error : " The Path cannot be all whitespace or empty"

then the programs loads translations.yaml, and proceeds to make the mySQL database.

The table is created but is empty of any data.

at the end, i get the following error :

An Unhandled Exception has occured and the program will now close.
Copy the data below and send to developer.

Source: Microsoft.VisualBasic
Message: File 'C:\xampp\mysql\data\eve\invCategories.csv' not found (Errcode: 2 "No such file or directory")
Raw Error Text: MySql.Data.MySqlClient.MySqlException (0x80004005): File 'C:\xampp\mysql\data\eve\invCategories.csv' not found (Errcode: 2 "No such file or directory")
at Microsoft.VisualBasic.CompilerServices.Symbols.Container.InvokeMethod(Method TargetProcedure, Object[] Arguments, Boolean[] CopyBack, BindingFlags Flags)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.CallMethod(Container BaseReference, String MethodName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, BindingFlags InvocationFlags, Boolean ReportErrors, ResolutionFailure& Failure)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.ObjectLateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)
at Microsoft.VisualBasic.CompilerServices.NewLateBinding.LateCall(Object Instance, Type Type, String MemberName, Object[] Arguments, String[] ArgumentNames, Type[] TypeArguments, Boolean[] CopyBack, Boolean IgnoreReturn)
at EVE_SDE_Database_Builder.frmMain.BuildEVEDatabase(Object UpdateDatabase, DatabaseType DatabaseType)
at EVE_SDE_Database_Builder.frmMain.btnBuildDatabase_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)

Looks like it couldn't find the import directory ('C:\xampp\mysql\data\eve\invCategories.csv). I copy the data into csv and then import with bulk imports into MySQL, PostgreSQL, and Access because it's faster. MySQL seemed to have a fixed directory for importing bulk data. It looks like it's not finding it? I run this query to get the path: SELECT @@global.secure_file_priv My folder is: C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\ Do you have one like this and is invCategories.csv in it? I'm not sure why it would matter where it is as long as MySQL loads from that folder.

On the populating of the list of files, I'll auto-load when you select the folder instead of after you save.

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

Import CCP's SDE - EVE SDE Database Builder

Aurora Foundation
#17 - 2016-12-20 19:47:32 UTC
Thank you for sharing this very useful tool. Everything works great except for when i try to import "Universe Data (multiple files)". I receive an "Object reference not set to an instance of an object" error. I am using the 2016-12-13 SDE and all the files are located in the input directory. It does work for all the other yaml data just not the universe data.

Quote:
An Unhandled Exception has occured and the program will now close.
Copy the data below and send to developer.

Source: EVE SDE Database Builder
Message: Object reference not set to an instance of an object.
Raw Error Text: System.NullReferenceException: Object reference not set to an instance of an object.
at EVE_SDE_Database_Builder.YAMLUniverse..ctor(String YAMLFileName, String YAMLFilePath, Object& DatabaseRef, YAMLTranslations& TranslationRef)
at EVE_SDE_Database_Builder.frmMain.BuildEVEDatabase(Object UpdateDatabase, DatabaseType DatabaseType)
at EVE_SDE_Database_Builder.frmMain.btnBuildDatabase_Click(Object sender, EventArgs e)
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)


Any suggestion would be greatly appreciated.

Thanks again!
#18 - 2016-12-27 20:53:35 UTC
Trying to export to CSV crashes on dgmEffects
#19 - 2016-12-29 18:14:11 UTC
EveMart Representative wrote:
Any chance of enabling username / password for MSSQL?

Added this option, if the user name/id is blank, it will use the windows auth to log in. If the user name/id is there, then it will use the entry with password to try and log in. Let me know if you have issues.

Stunt Thumper wrote:
Trying to export to CSV crashes on dgmEffects

Thanks, this has been fixed.

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

Import CCP's SDE - EVE SDE Database Builder

Caldari State
#20 - 2017-02-12 19:21:12 UTC
Thank you very much!!

I was pumping the crest interface for data that (I knew) was in the SDE, out of laziness at trying to figure out how to import those damn yaml files (I'm pretty sure it is nothing, but hell, the data comes from a sqlserver database anyways, so why in the hell are we supposed to jump through hoops to redo what was already done (SQLServer => Yaml files => SQLServer back again ! nonsense!)...

(you start your project, counting on what you find on their documentation site to be correct - here, in our case, that the sde exists in sqlserver format- you install all your tools and whatnot, sql server etc, you go on the target site supposed to host the database dump, and bang, you end up with every database type in the universe, EXCEPT sql server! and yaml files to boot! it makes absolutely _no_ sense...

CCP might have a gold nugget with eve online, but they sure suck at keeping their documentation up to date... Even the Crest interface isn't quite like it should be - some links in there just point to _nothing_, or suddenly, out of nowhere, you need to get a token, and a website, just to get some basic data...

Sorry, I'm venting my stress, I know... that's your tools' fault, it relieved my tension, I guess ;) )

Anyways, your tool is a great tool... thanks for giving it to all of us, it saved me a tons of time. *\o/*
2 Pages12Next page
Forum Jump