12.11. Server: migrating and merging databases

As always, back up your data first!. Here are some suggestions for useful database tools and a summary of automatic migration tools in CamCOPS.

12.11.1. Database-specific tools

MySQL

• MySQL provides MySQL Workbench, which uses ODBC to migrate other databases to MySQL.

• The mysqldump tool produces database backups in SQL format.

• The camcops_backup_mysql_database tool simplifies the use of mysqldump a little.

SQL Server

• SQL Server provides the SQL Server Migration Assistant (SSMA), in addition to SQL Server Management Studio (SSMS) and SQL Server Integration Services (SSIS). For specimen use, see e.g. https://www.sqlshack.com/migrate-mysql-tables-sql-server-using-sql-server-migration-assistant-ssma-ssis/.

SQLite

• A good one is SQLiteStudio.

12.11.2. Generic tools

See https://en.wikipedia.org/wiki/Comparison_of_database_tools.

These include:

• DataGrip: SQL and some schema management tools. Didn’t leap out as superb.

• DBeaver: I think this is good. Includes export/import support. Uses JDBC.

• Navicat: commercial product to support many databases that includes data migration as well as an SQL client. The Premium version supports lots of databases simultaneously (but as of 2018-09-30, it’s $1,299).

• SQuirreL SQL: this tool uses JDBC to connect to databases. I remember it as being quite good, but as of 2018-09-30 it doesn’t seem to work under Ubuntu 18.04:

  1. Download the .jar file and launch it with

     java -jar squirrel-sql-3.8.1-standard.jar
     

  2. Then fix any resulting “Assistive Technology Not Found” error like this: https://askubuntu.com/questions/695560/assistive-technology-not-found-awterror, e.g. by running

     sudo pico /etc/java-11-openjdk/accessibility.properties
     

     and commenting out the line

     assistive_technologies=org.GNOME.Accessibility.AtkWrapper
     

     Then re-run the installer.

  3. Then launch SQuirreL.

  4. Then, if it incorrectly complains about your Java JVM version, hack the launch script as per https://sourceforge.net/p/squirrel-sql/bugs/1019/. Note that its version detection (in JavaVersionChecker.java) is extremely primitive, being based on string comparison. Just hack this line in squirrel-sql.sh (for Linux systems):

     $JAVACMD -cp "$UNIX_STYLE_HOME/lib/versioncheck.jar" JavaVersionChecker 1.8 9
     

     so that your JVM version is mentioned (e.g. add 10 to support JVM 10.0.2.

  5. Then launch SQuirreL.

  6. Still fails, silently, with SQuirreL 3.8.1 under Ubuntu 18.04.

12.11.3. Merging old CamCOPS databases

If you have several old CamCOPS databases and you want to merge them, so that each old database is represented by a distinct group (or groups) in the new database, see the camcops_server merge_db command, described in CamCOPS command-line tools.