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

SQL Server

SQLite

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.