This page describes Calíope 1.5, the version I am building right now. 1.4 is on the App Store for the Mac, iPad, iPhone, Apple Watch, Apple TV and Apple Vision Pro. The changelog says which version each feature landed in.

Adds an “Open in Calíope” button to every topic. It only works with the app installed.

Data Migration

Migrate Objects Between Servers

Copy tables, views, routines, triggers and events to another server, or to another database on the same one.

Where it is: Workspace › Tools › Data Migration

Open Data Migration from the workspace sidebar. It copies tables, views, routines, triggers and events from this connection to another server, or to another database on the same one.

Choose what to copy in the tree on the left. Its header has a filter by name: type part of a database or object name to narrow the list without expanding one database at a time. Objects are grouped by kind (Tables, Views, Routines, Triggers and Events); clicking an object ticks it, and each database checkbox selects or clears everything under it, with the count of selected objects out of the total beside it. Clear empties the selection. Calíope fixes the order — tables, views, routines, triggers and events — so that every object finds whatever it depends on already created.

Choose where it goes. Next to Destination:, open Select target server, pick one of the connections saved in the Connection Manager and click Connect. In Into database, type the database the objects will land in; left empty, each object keeps the database it has in the source. Calíope creates that database if it does not exist, and rewrites the references of a view so it reads from there.

Options: Replace at destination drops everything selected before creating anything —first what depends on the tables, and child tables before their parents—, so running the same migration again is safe; Export data copies the rows of every table; Skip FK checks turns off foreign key checks while each object loads, so tables can arrive in any order.

Click Migrate. The log gives one line per object, with the database it went to and, for a table, the rows copied. Stop halts between one object and the next.

The foreign keys of the copied tables are created at the end, once they are all there with their rows: the order in which they arrive no longer matters.

Between different engine families a notice next to Migrate says so before anything runs: one engine's DDL isn't understood by the other, so nothing is created. Only rows are copied, into tables with the same name that already exist in the target, matching columns by name; a table the target doesn't have, or an object that isn't a table, is skipped and the log says so.

SQL Server

An IDENTITY column keeps its values (Calíope turns IDENTITY_INSERT on while copying), but not its counter: in the target the next row continues from the highest value copied. Each object lands in its own schema, which is created if it doesn't exist. With only a database name in Into database, each object keeps its schema: demo.dbo goes to copia.dbo, and demo.rrhh to copia.rrhh.

PostgreSQL

Here a database is a schema of the connected database: Into database names the schema the objects go to, and it's created if it doesn't exist. The types the copied tables use —an ENUM, a domain— are created first; one that already exists in the target is kept as it is, because rebuilding it would mean dropping every column that uses it. A copied function's search_path points to the target schema, so its triggers write there and not in the source.

> If the destination is the same server as the source and an object would land in its own database, Migrate stays disabled and a warning says why: with Replace at destination, the copy would drop the original before reading its rows.

> If the server you need does not appear in the selector, add it first in the Connection Manager and reopen Data Migration.

Keywords: migrate, migration, copy, move, target server, target connection, selector

Preview Migration SQL

Review the SQL that would be executed before migrating.

Where it is: Data Migration › Preview

Tick Preview and the button becomes Generate SQL: instead of running anything, Calíope writes in the output panel the SQL the migration would execute, so you can review or copy it. It is the same script the migration runs: CREATE DATABASE IF NOT EXISTS, the switch to that database, foreign key checks off when Skip FK checks is on, with Replace at destination, the DROP of everything selected first —what depends on the tables, then child tables before their parents—, then each object, and the INSERT statements with Export data. Routines and triggers come between DELIMITER // and DELIMITER ;, so the script also runs as is in the SQL Editor with Run Script. In Preview mode no destination connection is needed.

The foreign keys go at the end, after all the tables and their rows.

SQL Server

In SQL Server the script creates the database and the schema if they're missing (IF DB_ID(…) IS NULL), has no DELIMITER —each routine goes in its own EXEC— and rows with an identity value go between SET IDENTITY_INSERT … ON and OFF.

PostgreSQL

In PostgreSQL the script creates the schema if it's missing (CREATE SCHEMA IF NOT EXISTS), turns off foreign key checks with SET session_replication_role, creates the types its tables use before them, and has no DELIMITER: a function's body goes between $function$ quotes.

Keywords: preview, SQL preview, review before executing