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.
 

SDEExternalsToSql (v2.0.1)

Author
Desmont McCallock
#1 - 2015-09-07 20:08:02 UTC  |  Edited by: Desmont McCallock
This version is deprecated. Use version 3.

Many of you know the first version of the SDEExternalsToSql tool.
With the amount of changes that are being made to the SDE and the knowledge gained by building the first version and handling the SDE, the need to improve the tool arose. As a result of those circumstances I rewrote it, optimized and generalized it, so it can handle more use cases, much faster.

As usual the source code is available at EVEMon's repository as the tool is embedded in it (look into the Tools folder).

The compiled version can be found for downloading here.

The latest version is: 2.0.1

Direct dl link: EVEMonSDEExternalsToSql-2.0.1.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 community toolkit page.

-- Features --

* 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 now only contains the needed connection info to your SQL Server, and it's the only thing that you may need to modify)
* Ulta-High Speed (it now takes less than 5 minutes to import the entire SDE, depending on your machine's cpu power)
* 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)

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

Requirements

* .NET 4.0 or greater
* SQL Server instance

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.
Darkblad
Doomheim
#2 - 2015-09-07 20:34:19 UTC
Christmas in summer!

Thank you so much for your continued support of the 3rd party developer community, being one yourself Blink

NPEISDRIP

Hel O'Ween
Men On A Mission
#3 - 2015-09-09 11:44:40 UTC
Darkblad wrote:
Thank you so much for your continued support of the 3rd party developer community, being one yourself Blink


Not empty quoting.

Big smile

EVEWalletAware - an offline wallet manager.

Golden Gnu
Garoun Investment Bank
Gallente Federation
#4 - 2015-09-20 12:07:32 UTC
Amazing tool Desmont!

Creator of jEveAssets - the asset manager

"Download is the meaning of life, upload is the meaning of intelligent life"

Golden Gnu
Garoun Investment Bank
Gallente Federation
#5 - 2015-09-22 14:26:22 UTC
Minor bug (if you can call it a bug):
If you run the tool with a working directory that is not the one with the exe file, it will fail to import universeDataDx.db, but, everything else works. It's super easy to work around ofc. :)

Creator of jEveAssets - the asset manager

"Download is the meaning of life, upload is the meaning of intelligent life"

Desmont McCallock
#6 - 2015-09-22 17:14:08 UTC
Golden Gnu wrote:
Minor bug (if you can call it a bug):
If you run the tool with a working directory that is not the one with the exe file, it will fail to import universeDataDx.db, but, everything else works. It's super easy to work around ofc. :)

Can you give an example?
Golden Gnu
Garoun Investment Bank
Gallente Federation
#7 - 2015-09-23 10:28:16 UTC  |  Edited by: Golden Gnu
Run the tool with a batch file (win):
---
C:
CD \
C:\EVEMonSDEExternalsToSql-2.0.0\EVEMonSDEExternalsToSql.exe
---
That will make the working directory c: and it will fail to import universeDataDx.db (but, everything else works)

if you do:
---
C:
CD \
CD EVEMonSDEExternalsToSql-2.0.0
EVEMonSDEExternalsToSql.exe
---
It works without problems.

Creator of jEveAssets - the asset manager

"Download is the meaning of life, upload is the meaning of intelligent life"

Desmont McCallock
#8 - 2015-09-23 12:42:25 UTC
Golden Gnu wrote:
Run the tool with a batch file (win):
---
C:
CD \
C:\EVEMonSDEExternalsToSql-2.0.0\EVEMonSDEExternalsToSql.exe
---
That will make the working directory c: and it will fail to import universeDataDx.db (but, everything else works)

if you do:
---
C:
CD \
CD EVEMonSDEExternalsToSql-2.0.0
EVEMonSDEExternalsToSql.exe
---
It works without problems.
I'll look into it. I think it's easy to support that.
Desmont McCallock
#9 - 2015-09-23 14:08:20 UTC
Release of EVEMonSDEExternalsToSql 2.0.1 that fixes the above mentioned behavior.
Desmont McCallock
#10 - 2015-09-29 15:36:58 UTC
Tools still valid for Vanguard 1.0.
Lost Hamster
Hamster Holding Corp
#11 - 2015-10-01 12:16:34 UTC
Hi Desmont,

Great tool. Unfortunately with the new Vanguard database I have a problem. During "import mapDenormalize" it runs to timeout.
The process is at 99%, CPU usage is still high, so the system works, it's doing somehing, just the program throws a timeout.
Can you increase the timeout in the program? Or put the timeout value in the config file, so it's possible to increase it manually?

Thanks,
Hamster
Desmont McCallock
#12 - 2015-10-01 15:47:42 UTC  |  Edited by: Desmont McCallock
Lost Hamster wrote:
Hi Desmont,

Great tool. Unfortunately with the new Vanguard database I have a problem. During "import mapDenormalize" it runs to timeout.
The process is at 99%, CPU usage is still high, so the system works, it's doing somehing, just the program throws a timeout.
Can you increase the timeout in the program? Or put the timeout value in the config file, so it's possible to increase it manually?

Thanks,
Hamster
You can add the 'Connection Timeout' parameter in the connection string yourself. The tool will recognize it automatically.

If this still doesn't work, as a workaround you can import the sde in phases.
Use:
1. SDEExternalsToSql -noyaml - nosqlite (restores only the datadump)
2. SDEExternalsToSql -norestore - nosqlite (restores only the yamls)
3. SDEExternalsToSql -norestore - noyaml (restores only the sqlite)
Lost Hamster
Hamster Holding Corp
#13 - 2015-10-02 08:00:04 UTC
I tried to setup both
connection timeout
and
timeout

values. Tried to enter high number like 50000, and 0 (as I read 0 means infinite) the program ignored it. it took the same amount of time to timeout. (it takes about 100 sec)
Hel O'Ween
Men On A Mission
#14 - 2015-10-02 14:37:10 UTC
There seems to some issues with your SQL server, I'd say.

I've used the tool with SQL Express on my local machine (a couple of years old, 4GB of RAM) and it not only worked like a charm, but compared to the previous 1.x release, it even finished within "seconds". Major speed improvements there!

EVEWalletAware - an offline wallet manager.

Lost Hamster
Hamster Holding Corp
#15 - 2015-10-03 05:14:41 UTC
Hel O'Ween wrote:
There seems to some issues with your SQL server, I'd say.

I've used the tool with SQL Express on my local machine (a couple of years old, 4GB of RAM) and it not only worked like a charm, but compared to the previous 1.x release, it even finished within "seconds". Major speed improvements there!

There is nothing wrong with the SQL server, it's just not a normal PC, it's a VM, where I have an SQL 2012 installed. As in the end I need the database in SQL 2008 format. (Which is on my normal PC, with SSD, 8gb ram etc) There the SQLite import works like a charm.

So in the end I could put it together, on the VM importing the DB / yaml, and on the physical machine doing the import for SQLite.
Desmont McCallock
#16 - 2015-10-03 09:25:14 UTC  |  Edited by: Desmont McCallock
If you could post the timeout message or post the info from the trace.txt file when it times out, it could give me an insight on what might go wrong.

Just FYI I'm dev the tool on a Hyper V instance but using SQL 2014, and the timeout appears rarely. Until now I haven't been able to reproduce it but I'll try to dig more into it.

Have you tried to import the SDE in phases?

Additionally have a look at the SQL server settings, Compare the two instance, the one on the VM and the other on the physical PC, to spot any diffs. Maybe you need to increase the timeout on the instance instead (it might be dropping the connection).
Lost Hamster
Hamster Holding Corp
#17 - 2015-10-05 07:59:33 UTC
Here is the console output:

Connection to SQLite 'main' Database: Successful
Importing mapCelestialStatistics... 100% in 0:01:17.7587737
Importing mapConstellationJumps... 100% in 0:00:00.3244939
Importing mapConstellations... 100% in 0:00:00.3488424

Unable to import mapDenormalize
Reason was: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.


In the trace file, there is nothing interesting:

Native library pre-loader is trying to load native SQLite library "d:\Import2.01\x64\SQLite.Interop.dll"...
SQLite error (1): no such table: __MigrationHistory
SQLite error (1): no such table: __MigrationHistory
SQLite error (1): no such table: EdmMetadata


However I have restarted the server, (and added 2gb ram (sum: 6gb) and now it works.
Desmont McCallock
#18 - 2015-10-05 12:43:12 UTC  |  Edited by: Desmont McCallock
Lost Hamster wrote:
Here is the console output:

Connection to SQLite 'main' Database: Successful
Importing mapCelestialStatistics... 100% in 0:01:17.7587737
Importing mapConstellationJumps... 100% in 0:00:00.3244939
Importing mapConstellations... 100% in 0:00:00.3488424

Unable to import mapDenormalize
Reason was: Timeout expired. The timeout period elapsed prior to completion of
the operation or the server is not responding.


In the trace file, there is nothing interesting:

Native library pre-loader is trying to load native SQLite library "d:\Import2.01\x64\SQLite.Interop.dll"...
SQLite error (1): no such table: __MigrationHistory
SQLite error (1): no such table: __MigrationHistory
SQLite error (1): no such table: EdmMetadata


However I have restarted the server, (and added 2gb ram (sum: 6gb) and now it works.
Ah, yes, the importer is heavily memory consuming, espesially if you try to execute the importation all at ones, as the entire sqlite db is loaded into memory while writing to sql.

You see, the tool doesn't create a connection per table but opens only one connection to the db's to perform the import.
01Shady10
C0NC0RD Branch Office
The Dead Parrots
#19 - 2015-10-16 23:21:27 UTC
Holy spiced unicorn balls this tool is amazing Big smile

I just spent most of today pondering the best way to parse those abominable YAML files!

Very much appreciate both the work you've put in and the time you've saved me, will be perusing the source to see how you managed to get this thing to run so fast.

-Shady-
Tek Sharax
Aborted Launch Association
#20 - 2015-10-18 18:34:18 UTC
This tool is indeed a great piece of work.

A quick change of the connection string in the config file was all it took to get everything loaded in under 2 minutes.

With much gratitude,

- Tek