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.
123Next pageLast page
 

EVESDEToSQL (v3.5.0)
Author
Previous Topic Next Topic
Desmont McCallock
#1 - 2015-11-15 13:09:42 UTC  |  Edited by: Desmont McCallock
Disclaimer: As of May 16th 2016 anyone feel free to fork the repo and continue the development. Probably, there will be no more releases from me.


Introducing the next generation of EVE SDE handling tool (formely known as EVEMonSDEExternalsToSql).

I'm presenting to you EVESDEToSQL v3.
Version 3 is the evolution of the previous version, containing all the goodies of v2, plus the ability to export the EVE SDE to the most popular SQL formats and more.

The tool source code has been separated from EVEMon and now resides in its own repository.

The compiled version can be found for downloading here.

The latest version is: 3.5.0

Direct dl link: EVESDEToSQL-3.5.0.zip

Notice: The SQL data dump, yaml and SQLite db files are not distributed along with the tool. You will have to get them from the EVE Developers Resources.

--Features--

-Importation

* Restores the data dump, yaml and sqlite db files back into an SQL Server (no need to restore the data dump via SSMS).
* Imports any SDE (literally any, but in case you find an SDE with issues please let me known)
* Supports command line arguments (use 'help' to see the list)
* Cleaner config file (it only contains the needed connection info to your SQL Server, and it's the only thing that you may need to modify)
* Ultra-High Speed (it takes less than 2 minutes to import the entire SDE, depending on your machine's CPU power) * applies only for legacy SDE
* One place for the SDE file(s) (just drop the SDE zip file as is or the files contained in the SDE zip file, into the 'SDEFiles' folder)

-Exportation

* Exports an SQL Server DATADUMP backup file
* Exports an SQLite database file
* Exports an MS Access database file ('mdb' as default, use '/ace' switch for 'accdb')
* Exports an MySQL dump file
* Exports an PostgreSQL dump file
* Exports individual CSV (semicolon seperated version) files
* Ability to export specific tables in any dump format

Exportation Usage Examples

SQL Server dump: EVESDEToSQL export -sql [/et: listOfTables]
MySQL dump: EVESDEToSQL export -mysql [/et: listOfTables]
PostgreSQL dump: EVESDEToSQL export -postgresql [/et: listOfTables]
SQLite dump: EVESDEToSQL export -sqlite [/et: listOfTables]
Ms Access dump: EVESDEToSQL export -access [/ace] [/et: listOfTables]
CSV dump: EVESDEToSQL export -csv [/et: listOfTables]

Requirements

* .NET 4.0 or greater
* SQL Server (v2014+) instance (any edition, even Express)

Supported OS: Windows XP, Vista, 7, 8, 8.1, 10

Yaml files mapping:

blueprints.yaml -> invBlueprintTypes, ramTypeRequirements
categoryIDs.yaml -> invCategories, translationTables, trnTranslationColumns, trnTranslations
certificates.yaml -> crtClasses, crtCertificates, crtRecommendations, crtRelationships
graphicIDs.yaml -> eveGraphics
groupIDs.yaml -> invGroups, translationTables, trnTranslationColumns, trnTranslations
iconIDs.yaml -> eveIcons
skinLicenses.yaml -> sknLicenses
skinMaterials.yaml -> sknMaterials
skins.yaml -> sknSkins
tournamentRuleSets.yaml -> ignored
typeIDs.yaml -> invTypes, dgmMasteries, dgmTypeMasteries, dgmTraits, dgmTypeTraits, translationTables, trnTranslationColumns, trnTranslations

Sqlite files mapping:

universeDataDx.db -> mapCelestialStatistics, mapConstellationJumps, mapConstellations, mapDenormalize, mapJumps, mapLandmarks, mapLocationScenes, mapLocationWormholeClasses, mapRegionJumps, mapRegions, mapSolarSystemJumps, mapSolarSystems

Known Issues:

None (so far)


Enjoy responsibly.
Desmont McCallock
#2 - 2015-11-20 09:27:45 UTC  |  Edited by: Desmont McCallock
FAQ

Q:I don't want the name of the database to be 'EveStaticData'.

A: Edit the EVESDEToSQL.exe.config file and change the value of connection's string 'Initial Catalog' to name the database differently.

Q:I'm getting the error message
Connection to SQL Server 'EveStaticData' database: Failed
Reason was: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

A: Edit the EVESDEToSQL.exe.config file and adjust the value of connection's string 'Data Source' to point to your SQL Server instance (the tool assumes that the SQL Server instance is named SQLEXPRESS).

Q:I'm getting the error message
Connection to SQL Server 'EveStaticData' database: Failed
Reason was: Cannot open database "EveStaticData" requested by the login. The login failed.
Login failed for user 'computerName\user'.

A: This message appears when you try to export to a dump format. Do an import first (EVESDEToSQL import).
Desmont McCallock
#3 - 2015-11-20 10:56:45 UTC
Releasing version 3.0.1.

- Includes several bug fixes and improved error handling.
Golden Gnu
Lobach Inc.
#4 - 2015-11-23 14:21:23 UTC
Thanks Desmont! :)
I'm still only using the import feature, but, it's amazing how this software always works without any problem.
You must have a talent for writing high quality code o7

Creator of jEveAssets - the asset manager

"Download is the meaning of life, upload is the meaning of intelligent life"
Desmont McCallock
#5 - 2015-11-23 16:48:34 UTC
Golden Gnu wrote:
Thanks Desmont! :)
I'm still only using the import feature, but, it's amazing how this software always works without any problem.
You must have a talent for writing high quality code o7
Thank you for the compliment. Big smile
GrayLensman
Galactic Protectorate
#6 - 2015-12-01 01:52:41 UTC
Desmont,

I have attempted to run your utility several times but am clearly missing something. I have attached two images:

(1) showing the location of EVESDEToSQL and the contents of the SDEFiles folder first...

Picture 1

(2) Then showing the results and that EVESDEToSQL is not able to locate the SDE files...

Picture 2

What am I missing? Thanks.

Gray Lensman
Desmont McCallock
#7 - 2015-12-01 08:10:51 UTC  |  Edited by: Desmont McCallock
It looks like the unzipping is failing. I'll have a look at it.
Meanwhile don't use the zip file inside the folder but just unzip the files in it and remove the zip file afterwards.

Edit: Found it.
Desmont McCallock
#8 - 2015-12-01 08:37:31 UTC  |  Edited by: Desmont McCallock
Released version 3.1.0.

Change log

* Added support for using SQL Server Authentication.
* Added database re-indexing and shrinking.
* Fixed a bug when trying to unzip the SDE zip file.
GrayLensman
Galactic Protectorate
#9 - 2015-12-01 16:04:51 UTC
3.1 tested and confirmed, results of import below provided in 57.38 seconds... this really is excellent work, nice job...

Considering the amount of labor you saved me I have contributed 50m isk to the Desmont McCallock Software Services Fund... I encourage other pilots to do the same.



D:\Dev\Data\EVESDEToSQL-3.1.0>evesdetosql import
Unzipping SDE zip file: Successful

Importing SQL data dump to 'EveStaticData' database... 100 % in 0:00:02.2645004

Connection to SQL Server 'EveStaticData' database: Successful

Importing categoryIDs.yaml... 100 % in 0:00:00.1473313
Importing groupIDs.yaml... 100 % in 0:00:00.2944629
Importing graphicIDs.yaml... 100 % in 0:00:00.3124175
Importing iconIDs.yaml... 100 % in 0:00:00.1020952
Importing skins.yaml... 100 % in 0:00:00.0881721
Importing skinMaterials.yaml... 100 % in 0:00:00.0217246
Importing skinLicenses.yaml... 100 % in 0:00:00.0708117
Importing typeIDs.yaml... 100 % in 0:00:16.3830429
Importing certificates.yaml... 100 % in 0:00:00.2589833
Importing blueprints.yaml... 100 % in 0:00:02.8515145

Connection to SQLite 'main' database: Successful

Importing mapCelestialStatistics... 100 % in 0:00:09.2347243
Importing mapConstellationJumps... 100 % in 0:00:00.0475246
Importing mapConstellations... 100 % in 0:00:00.0408733
Importing mapDenormalize... 100 % in 0:00:19.1871954
Importing mapJumps... 100 % in 0:00:00.0954077
Importing mapLandmarks... 100 % in 0:00:00.0177922
Importing mapLocationScenes... 100 % in 0:00:00.0150901
Importing mapLocationWormholeClasses... 100 % in 0:00:00.0198079
Importing mapRegionJumps... 100 % in 0:00:00.0171859
Importing mapRegions... 100 % in 0:00:00.0250435
Importing mapSolarSystemJumps... 100 % in 0:00:00.22791
Importing mapSolarSystems... 100 % in 0:00:00.2612589

Disconnection from SQLite 'main' database: Successful

Reorganizing indexes of database 'EveStaticData'... 100 % in 0:00:05.085969

Shrinking database 'EveStaticData'... in 0:00:00.1521892

Disconnection from SQL Server 'EveStaticData' database: Successful


Importing files completed in 0:00:57.3862157

Press any key to exit.
D:\Dev\Data\EVESDEToSQL-3.1.0>
Desmont McCallock
#10 - 2015-12-01 19:23:50 UTC
@GrayLensman Very much appreciated.
Desmont McCallock
#11 - 2015-12-07 10:23:25 UTC
For those having issue with using the SDE zip file, make sure that the file is named according to CCP's naming convention 'ReleaseName_VersionMajor.VersionMinor_VersionBuild_db.zip' (which they don't seem to honor in some cases).
Hel O'Ween
Men On A Mission
#12 - 2015-12-10 09:51:28 UTC
Continuing from here

Running the tool with ...

EVESDEToSQL import -norestore -noyaml

... still fails with the already mentioned timeout and produces the below content in trace.txt:

Quote:

EVESDEToSQL: Started
Native library pre-loader is trying to load native SQLite library "C:\DATEN\x86\SQLite.Interop.dll"...
Deleting file C:\DATEN\SDEFiles\universeDataDx.db
System.IO.IOException: Der Prozess kann nicht auf die Datei "C:\DATEN\SDEFiles\universeDataDx.db" zugreifen, da sie von einem anderen Prozess verwendet wird.
bei System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
bei System.IO.File.InternalDelete(String path, Boolean checkHost)
bei System.IO.File.Delete(String path)
bei EVESDEToSQL.Utils.Util.DeleteSDEFilesIfZipExists() in EVESDEToSQL\Utils\Util.cs:Zeile 184.


Der Prozess kann nicht auf die Datei "C:\DATEN\SDEFiles\universeDataDx.db" zugreifen, da sie von einem anderen Prozess verwendet wird.

translates to

The process can't access file "C:\DATEN\SDEFiles\universeDataDx.db", becuase it is in use by another process.

I peeked at Util.cs and it seems it tries to delete the SQLite DB while it's still in use somewhere. My C# is too weak to determine the real cause, but I suspect this is a follow-up error of the import issue, which leaves the SQLite DB still connected/open, and this deletion exception happens during the "normal" cleanup operation when closing the application.

EVEWalletAware - an offline wallet manager.
Desmont McCallock
#13 - 2015-12-10 13:42:59 UTC  |  Edited by: Desmont McCallock
Hel O'Ween wrote:
I peeked at Util.cs and it seems it tries to delete the SQLite DB while it's still in use somewhere. My C# is too weak to determine the real cause, but I suspect this is a follow-up error of the import issue, which leaves the SQLite DB still connected/open, and this deletion exception happens during the "normal" cleanup operation when closing the application.
This is a normal error as the tool tries to delete the files it had unzipped once you close it.
The fact that it's still failing to import only the sqilte DB is troublesome. Just for the record, I can't reproduce it on my machine but I'll investigate this behavior further.

Edit: While we are at it, do you want me to produce an EWA mdb dump for you?
Edit2: I did it anyways Frostline_1.0_EWA_201512101549.zip
Hel O'Ween
Men On A Mission
#14 - 2015-12-10 18:06:20 UTC  |  Edited by: Hel O'Ween
Desmont McCallock wrote:

The fact that it's still failing to import only the sqilte DB is troublesome. Just for the record, I can't reproduce it on my machine but I'll investigate this behavior further.


It does import the sqlite quite find, it just chokes on that single mapDenormalize table after the counter goes up to 100% for it.

I just dropped mapDenormalize from the sqlite and tried the import again: worked like a charm. Yeah, mapDenomralize is the biggest table, but it's still "peanuts" for any really RDBMS.

Quote:

Edit: While we are at it, do you want me to produce an EWA mdb dump for you?
Edit2: I did it anyways Frostline_1.0_EWA_201512101549.zip


Thanks, but the good ol' EVEMonSDEExternalsToSql.exe already did the job for me. Big smile

EVEWalletAware - an offline wallet manager.
Desmont McCallock
#15 - 2015-12-19 10:23:34 UTC  |  Edited by: Desmont McCallock
Iterating again on your problem here is what I found so far:

Fact: Memory consumption is maximum 500MB during the entire operation.
Conclusion: Therefore it's not a memory consumption issue.
Action: No action will be taken.

Fact: The bulk insert timeout is fixed at 30 seconds and is not linked to the connection timeout setting in the connection string.
Conclusion: Most likely the problem lies there, as from looking at the times it takes on your machine to insert the other tables, MapDenormalize exceeds the timeout and any offset it might have.
Action: I will add some code to link the connection timeout set in the connection string to the bulk insert timeout.
Desmont McCallock
#16 - 2015-12-19 10:32:15 UTC  |  Edited by: Desmont McCallock
Released version 3.1.1.

Change Log
* Improved search algorithm for the SDE zip file.
* Linked the bulk insert operation timeout to the connection timeout if it's set and exceeds the default value.
Desmont McCallock
#17 - 2015-12-19 10:39:26 UTC
@Hel O'Ween
Try again setting 'Connection Timeout=60' in the connection string.
Hel O'Ween
Men On A Mission
#18 - 2015-12-19 10:51:00 UTC
I'm not having access to my dev machine right now, will try that on monday.

Regardless of the outcome - many thanks for your ongoing efforts. Much appreciated, Desmont!

EVEWalletAware - an offline wallet manager.
Desmont McCallock
#19 - 2015-12-19 11:20:30 UTC
Hel O'Ween wrote:
I'm not having access to my dev machine right now, will try that on monday.

Regardless of the outcome - many thanks for your ongoing efforts. Much appreciated, Desmont!

Sure thing. Thanks for your kind words. Big smile
Hel O'Ween
Men On A Mission
#20 - 2015-12-21 12:12:04 UTC
And surely enough, that fixed it. Big smile

Thanks, Desmont.

EVEWalletAware - an offline wallet manager.
123Next pageLast page