Doing the SDE Conversions

2 Replies

I’m not planning on stopping doing these any time soon, as I need them for my own porpoises, but I thought it might be worth having a few instructions out there, so people can run their own conversions of the EVE SDE. Just  in case I get hit by a bus, or mauled by crazed Cetaceans.

The process is now a /lot/ simpler than it was before. Not as simple as I’d like, but pretty close.

With the first draft of this, there will be no screenshots, and it’s entirely from memory. I may update it when I do the next run.

Environment setup

This should only need to be done the once, rather than each time you want to do the conversion.

On your Windows box:

  1. Install SQL Server Express Edition 2012 with Tools.  Find it here. You want ENU\x64\SQLEXPRWT_x64_ENU.exe or ENU\x86\SQLEXPRWT_x86_ENU.exe
  2. Install Mysql Server. Find it here.
  3. Install the Mysql Workbench. Find it here.
  4. Install openDBCopy. Find it here. You’ll also want a copy of Java 7 JDK installed, and the JAVA_HOME environment variable set to point at it.
  5. drop of copy of the jTDS and mysql jdbc drivers into the opendbcopy.copy plugin lib directory. The path should be something like C:\Program Files\openDBcopy\plugins\opendbcopy.copy\lib
  6. Configure SQL Server to allow for TCP connections. Something like this guide should help.
  7. In mysql, you want to define lower_case_table_names=2 in the my.ini, for [mysqld]. Mine had to go into c:/windows/my.ini This may be useful.
  8. Create a database in mysql for doing the conversions into. Creating a user is good practice too, though you could just use the root user. Make sure the database is set for utf8.

If you’re wanting to do a postgres or sqlite conversion, you’ll want a linux box too. Probably can do it on windows, but I’ve never done it there. If you have a linux box on the same network as your windows box, you could just use that for the mysql server, rather than doing it all on windows. Just update the connections below in an appropriate fashion.

Linux Box

  1. Install Mysql server
  2. Install Postgres
  3. Install SQLite
  4. Grab a copy of db_converter.py
  5. Grab a copy of mysql2sqlite.sh.

Actually doing the conversion

Mysql

  1. Download and extract the SDE.
  2. Fire up the SQL server management studio. log in.
  3. Right click on the databases tree item, and select ‘restore backup’. Pick the file option, browse to the file you’ve extracted, select it, then restore it. This post should be of use.
  4. Make sure you have a user in sql server who has rights to the database. I just created one, then set it as the owner of the restored ebs_DATADUMP
  5. Fire up the Mysql Workbench. Select the Migration option.
  6. Go through the wizard. The first database is the SQL server one. The second is the mysql one. Once it’s copied the schema, quit out. It doesn’t (as of 6.0.7) work right for all the data in the tables; it’s also slow.
  7. Fire up opendbcopy
  8. Select the ‘copy data from a source database to a destination database’ plugin.
  9. on plugin configuration, double click the dir() option, and give it a directory. Hit next.
  10. On database connections, fill in everything as needed. breaking my screenshot rule:

 

  1. hit apply and test for each. you’ll need to fix any problems at this stage.
  2. On the source model, select the proper schema. as my sql server user owns the imported database, dbo was appropriate. hit ‘capture source model’ it will take a little time.
  3. capture destination model.
  4. Hit next
  5. hit next again, unless you don’t want to move everything.
  6. hit next again, unless you’ve run into trouble before and want to adjust the mapping
  7. hit next again
  8. hit execute

At this point, you should have a converted mysql database. Export it, and then import to where ever you want to put it. For the SQLite and Postgres conversions, that’ll be onto your linux box, for the next stages.

Postgres

  1. import the converted mysql database onto your linux box.
  2. mysqldump –compatible=postgresql –default-character-set=utf8 -r eve.mysql -u root -p eve
  3. Update the username and database name appropriately in the above command.
  4. ./db_converter.py  eve.mysql eve.postgres
  5. I needed to edit the eve.mysql file in a couple of places, as db_converter didn’t deal right with the tinyints. Just converted them to ints.
  6. create a postgres database to load the postgres version into.
  7.  psql eve -f eve.postgres

SQLite

  1. mysql2sqlite.sh -u root -pMySecretPassWord eve | sqlite3 eve.sqlite

Excel/csv

  1. Run a script (after making sure you have all the modules installed right 😉 )

Individual SQL

  1. Run a script