This page describes Calíope 1.8, the version I am building right now. 1.7 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.
Create or modify tables with a visual interface without writing DDL manually.
Where it is: Catalogs › context menu › New table / Design table
The Table Builder opens from Catalogs: press New table in the toolbar, or open the context menu of an existing table — right-click on macOS, press and hold on iPad — and choose Design table. Catalogs opens in read-only mode, so turn that off first. Available on macOS, iPad, iPhone and Apple Vision Pro, and on all four it opens as a persistent Workspace tab: you can switch to another tab (for example Catalogs to check another table) and come back without losing your work. Each table opens in a tab of its own.
The editor has its tabs: Columns, Indexes, Foreign keys —where the engine has them— and Options. Below them, the SQL Preview shows the CREATE TABLE —or, for an existing table, the ALTER TABLE— that your changes produce, and follows every edit; Copy puts it on the clipboard. When you are done, press Create table (or Apply changes for an existing table) to run it on the server. Reset discards what you changed: back to the defaults for a new table, or to the server's definition for an existing one.
In the Columns tab, press + to add a column; the buttons beside it duplicate the selected column, move it up or down, and delete it. Press a column in the list to edit it underneath: the name, the data type —the list is the one of the server's engine—, the length when the type takes one (e.g. 500 for VARCHAR), and the constraints Allow NULL, Primary key (PRIMARY KEY) and Unique (UNIQUE), plus the ones only some types take. Only the ones the chosen type supports appear, and marking a column as primary key turns NULL off.
MySQLMariaDBAuroraTiDB
On integer types, Auto increment (AUTO_INCREMENT) numbers the rows by itself and Unsigned (UNSIGNED) leaves out negative values.
TiDB
On a BIGINT primary key, Random ID (AUTO_RANDOM) generates scattered values instead of consecutive ones, so inserts don't pile up in a single region. It's chosen when the column is created: TiDB doesn't let you add or remove it later, so on an existing table the checkbox shows ticked and disabled. There are no spatial types.
SQL ServerPostgreSQL
On integer types, Auto increment (IDENTITY) numbers the rows by itself. There is no Unsigned.
The default value can be none, a literal value, NULL, CURRENT_TIMESTAMP or an expression. Each column can also carry a comment.
MySQLMariaDBAuroraTiDB
Text columns can have their own character set and collation; left as they are, they inherit the table's.
SQL Server
Text columns (nvarchar, nchar, varchar, char) offer Collation, with the ones in the language of the database’s own —the Latin1_General_… ones for a Latin1_General_100_CI_AS_SC_UTF8 database, not the server’s 5,500—; left empty, the column inherits the database’s. There is no separate character set: it comes from the collation.
PostgreSQL
Text types (varchar, char and text) take a per-column Collation, from the ones the server has for the database encoding; the character set belongs to the whole database and is not chosen.
SQL Server
The comment is written as the MS_Description extended property.
PostgreSQL
The comment is written with COMMENT ON COLUMN.
A column that already existed keeps its original name, so renaming it becomes a rename in the ALTER TABLE instead of dropping the column and adding a new one.
SQL ServerPostgreSQL
A new column always goes at the end of the table: the engine has no AFTER. Moving a new column up or down changes nothing on the server, and the SQL Preview says so in a comment.
Oracle
On NUMBER, Identity (IDENTITY) numbers the rows by itself (GENERATED BY DEFAULT AS IDENTITY). There is no Unsigned. The length of VARCHAR2 and CHAR counts characters: 50 is VARCHAR2(50 CHAR); to count bytes, type 50 BYTE. NUMBER takes its precision and scale in the same field (10,2), and TIMESTAMP the precision of its fractional seconds (6). The builder offers no character set or collation per column, and the comment is written with COMMENT ON COLUMN. A new column always goes at the end of the table. On an existing table, the ALTER TABLE states only what you changed in each column; an identity can be removed but not added to a column that already exists, and the SQL Preview says so in a comment.
Keywords: column, data type, INT, VARCHAR, PRIMARY KEY, AUTO_INCREMENT, nullable
Manage the table's indexes: INDEX and UNIQUE, and FULLTEXT and SPATIAL where the engine has them.
Where it is: Table Builder › Indexes Tab
In the Indexes tab, press + to add an index. Give it a name and choose the type; the list offers only the types the server's engine has: INDEX (standard) and UNIQUE (no duplicates).
MySQLMariaDBAurora
Also FULLTEXT (full-text search on CHAR/VARCHAR/TEXT columns) and SPATIAL (geospatial data).
TiDB
The builder has no FULLTEXT or SPATIAL: TiDB accepts the first with a warning and doesn't create it, and has no spatial types.
SQL Server
SPATIAL is available: for a geometry column you write the box that bounds its data (xmin, ymin, xmax, ymax), because the plane has no edges; for a geography column you leave it empty. The table needs a primary key. FULLTEXT is not offered: it needs the server’s Full-Text Search component.
PostgreSQL
Also FULLTEXT —a gin index over to_tsvector('simple', …) of the chosen columns— and SPATIAL —a gist index, over core geometry or PostGIS geometry.
Tick the columns it covers: the order in which you tick them is the order of the index, and the number beside each one shows it. An index can span several columns (composite index).
Right-click an index in the list (on iPad, touch and hold it) to edit or delete it. Editing one that already existed drops it and creates it again.
The primary key is defined per column in the Columns tab (Primary key (PRIMARY KEY)); it does not appear here as a separate index.
ClickHouse
With ClickHouse the types are Skip index and, if the server creates text indexes, FULLTEXT: you choose its type (minmax, set(100) or bloom_filter; for text, ngrambf_v1, which stores three-letter fragments and serves LIKE '%x%', and tokenbf_v1, which stores words and serves hasToken; and text if the server creates text indexes) and its granularity, and it skips blocks of data instead of looking up a row. There is no UNIQUE. It can also be added and removed on an existing table, with ALTER TABLE … ADD INDEX and DROP INDEX. FULLTEXT writes that same skip index of type text(tokenizer = 'splitByNonAlpha'), which splits the text into words and serves hasToken; there is no spatial index.
Oracle
FULLTEXT and SPATIAL are single-column domain indexes (CTXSYS.CONTEXT and MDSYS.SPATIAL_INDEX_V2), and the spatial one first registers its metadata row. A UNIQUE index is created as a UNIQUE constraint, and deleting it drops the constraint together with its index.
Apple Vision Pro: the context menu opens by looking at the row and pinching and holding.
Define referential integrity relationships between tables.
Where it is: Table Builder › Foreign Keys Tab
In the Foreign Keys tab, press + to add a FK. Give it a name, choose the local column, the database —the same one unless you pick another— and the referenced table, and type the referenced column.
Choose the ON DELETE and ON UPDATE actions: RESTRICT (error if integrity is violated), CASCADE (propagates the change), SET NULL (sets the FK to NULL) or NO ACTION.
MySQLMariaDBAurora
NO ACTION is equivalent to RESTRICT. Both tables must use the InnoDB storage engine.
TiDB
NO ACTION is equivalent to RESTRICT. TiDB enforces foreign keys since 6.6 (tidb_enable_foreign_key, on by default): deleting a parent row that still has children fails with error 1451.
SQL Server
There is no RESTRICT: the builder writes NO ACTION, which in this engine does the same. A foreign key cannot point at another database, and the SQL Preview says so in a comment.
Right-click a key in the list (on iPad, touch and hold it) to edit or delete it; editing one that already existed drops it and creates it again. The builder generates the CONSTRAINT with an explicit name, so it can be dropped later by that name.
ClickHouse
With ClickHouse there is no Foreign Keys tab: the engine doesn't have them.
Oracle
Oracle only has ON DELETE CASCADE and ON DELETE SET NULL: with RESTRICT or NO ACTION the builder writes no clause, which is what the engine does by default. ON UPDATE does not exist, and the tab does not offer it. A foreign key can point at a table in another schema if you hold the REFERENCES privilege on it.
Apple Vision Pro: the context menu opens by looking at the row and pinching and holding.
What belongs to the whole table: its comment and, where the engine has them, storage engine, row format, character set and collation.
Where it is: Table Builder › Options Tab
The Options tab holds what belongs to the table as a whole.
MySQLMariaDBAurora
The storage Engine (ENGINE), the Row format (ROW_FORMAT), the table's Character set and Collation —changing the character set selects the first collation of that set— and the Table comment. Text columns left as “(inherits table)” take the table's character set.
MySQLMariaDBAurora
The row format is chosen when the table is created: for an existing table the tab does not offer it, because the builder does not change it. The lists of character sets and collations come from the server; the storage engine list is fixed, with InnoDB first.
TiDB
The table's Character set and Collation, and the Table comment. There's no Engine (ENGINE) or Row format (ROW_FORMAT): TiDB accepts them and does nothing with them.
SQL ServerPostgreSQL
The engine has no storage engine, row format or character set per table, so the tab holds only the Table comment.
SQL Server
The comment is written as the MS_Description extended property.
PostgreSQL
The comment is written with COMMENT ON TABLE.
ClickHouse
With ClickHouse, when you create the table, the tab has the Keys section with ORDER BY, PARTITION BY, PRIMARY KEY, TTL and SETTINGS, depending on the chosen engine: the MergeTree family has them, and Memory or Log don't. With ORDER BY empty, the columns marked as primary key are used; with neither, Create table turns off and the footer says why. When you edit an existing table the section doesn't appear, because the engine and the keys can't change afterwards. A note at the end says what the engine doesn't have: foreign keys, auto-increment, unique indexes and character sets.
Oracle
The engine has no storage engine, row format or character set per table, so the tab holds only the Table comment, written with COMMENT ON TABLE.