This page describes Calíope 1.7, the version I am building right now. 1.6 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.
Navigate tables, views, indexes, columns, routines, triggers, and events.
Where it is: Workspace › Tools › Catalogs
Open Catalogs from the workspace navigation bar. Select a database in the top selector. The sub-tabs display the different categories of objects.
ClickHouse
With ClickHouse there are no Triggers or Events tabs, and a note under the tabs says what takes their place. Indexes is called Keys: for each table, the sorting key, the partition key, the primary key if it differs, the sampling key, the skip indexes with their type and granularity, and the projections, with no cardinality and no New index. Between Views and Keys sits Materialized views: the list says what each one writes to —Internal, if the server created the target— and when it refreshes, and below it goes the sheet of the selected one: which table it reads from, which table it writes to and the name to query it by, how much space the target takes and how many rows it has, when the last row was written and, if it refreshes on its own, its schedule, its status and the last and next refresh, with the time zone written out. Routines lists the user's functions.
Oracle
With Oracle the selector lists schemas: those with some object your account can see, plus your own even when empty, Oracle’s included (SYS, MDSYS…). Tables shows the user’s tables —without the internal ones of a spatial index or a materialized view—, with the rows from the last statistics (NUM_ROWS) and the types as Oracle writes them (NUMBER(10,0), VARCHAR2(100 CHAR)). Views includes materialized views. Routines lists procedures, functions and packages —with their specification and body in the DDL panel— and puts INVALID after the type of one that does not compile; Triggers puts DISABLED after one that is disabled. In Indexes, a function-based index shows its expression, and a domain or anomalous one, its type (DOMAIN · MDSYS.SPATIAL_INDEX_V2, NORMAL · INVISIBLE, UNUSABLE). There is one more tab, Sequences, with minimum, maximum, step, cycle, cache and Next on disk: the next number the server will write, not the last one it handed out, which with a cache can be up to a block behind. There is no Events tab.
Display the CREATE TABLE, CREATE VIEW, or CREATE PROCEDURE and edit it interactively.
Where it is: Catalogs › right-click › View DDL
Open the context menu —right-click on macOS, long press on iPad— of any object in the catalogs list and select View DDL. The bottom panel shows the full DDL with syntax highlighting and text selection on every platform.
Editing the DDL:
Press Open in ItemEditor (button at the top of the DDL panel) to open the editor in a modal sheet with the DDL pre-loaded. It is the same editor on every platform, with syntax highlighting.
Modify the DDL as needed and press Apply to send it to the server. The Reset button discards your changes and reverts to the original DDL. If the ALTER or CREATE fails, a red message appears in the bottom bar with the server's error.
Available on macOS, iPad, iPhone and Apple Vision Pro.
Apple Vision Pro: the context menu opens by looking at the row and pinching and holding.
Oracle
With Oracle the panel shows what the server writes with DBMS_METADATA.GET_DDL: quoted and qualified names, EDITIONABLE where it applies, the whole identity and, for a table, its indexes, its comments and its foreign keys at the end, as ALTER TABLE. If your account cannot read the DDL of an object in another schema —it lacks SELECT_CATALOG_ROLE—, a comment says so and Open in ItemEditor is not offered. Sequences also have View DDL.
Create a table directly from the Catalogs toolbar.
Where it is: Catalogs › New table
In the Catalogs toolbar, use the New table button to open the Table Builder in a new tab. You can also open the context menu on an existing table — right-click on macOS, press and hold on iPad — and choose Design table to edit it.
The builder splits the work across the tabs of its bar: Columns, Indexes, Foreign keys —where the engine has them— and Options. What each one offers with your session's engine is covered in the Table Builder topics.
The SQL Preview section at the bottom shows the DDL that will be executed in real time. Click Create table (or Apply changes in edit mode) to send the DDL to the server.
Apple Vision Pro: the context menu opens by looking at the row and pinching and holding.
Access the visual editors for each object type from the toolbar.
Where it is: Catalogs › New view / New index / New routine / New trigger / New event
The Catalogs toolbar includes dedicated buttons to create each object type:
- New view — opens the view editor
- New index — opens the index editor
- New routine — opens the function and stored procedure editor
- New trigger — opens the trigger editor
MySQLMariaDBAurora
- New event — in the Events tab, opens the event scheduler editor
SQL ServerPostgreSQL
There is no Events tab and no New event: the server has no event scheduler.
Each editor generates the corresponding DDL (CREATE VIEW, CREATE INDEX, CREATE PROCEDURE, etc.) in the dialect of the server you are connected to, and executes it on the server upon confirmation. Each one only offers what that server can write: the next topics say what changes from one engine to another.
ClickHouse
With ClickHouse there is only New view: no New index —the tab is Keys, and a skip index is added from the Table Builder—, no New routine, no New trigger and no Events tab.
Oracle
With Oracle there are New view and New index. There is no New routine nor New trigger: Catalogs shows the PL/SQL code of procedures, functions, packages and triggers, but does not edit it. There is no Events tab either.
Modify existing objects with the visual editor or directly in the SQL editor.
Where it is: Catalogs › right-click › Edit (visual form) / Open in ItemEditor
Open the context menu on a view, routine, trigger, or event in its corresponding Catalogs tab — right-click on macOS, press and hold on iPad. Besides View DDL, it offers two ways to edit:
- Edit (visual form) — opens the graphical editor with pre-filled fields
- Open in ItemEditor — opens the full DDL of the object in the SQL editor for manual modification
Changes made in the visual form generate an ALTER or DROP + CREATE depending on the object type.
Oracle
With Oracle, procedures, functions, packages and triggers can be read but not edited from Catalogs: Edit (visual form) does not appear. View DDL shows their full PL/SQL —a package’s specification and body together—, and Open in ItemEditor takes it to the SQL editor, where you change it and run it yourself. There is no Events tab: Oracle’s scheduler is left out.
ClickHouse
With ClickHouse, user-defined functions can be read but not edited from Catalogs: Edit (visual form) does not appear, and Open in ItemEditor takes their DDL to the SQL editor.
Apple Vision Pro: the context menu opens by looking at the row and pinching and holding.
Where it is: Catalogs › right-click › Populate with dummy data
Open the context menu on a table in the Tables tab of Catalogs — right-click on macOS, press and hold on iPad — and select Populate with dummy data. Calíope will generate sample INSERT statements adapted to the table's column types.
Apple Vision Pro: the context menu opens by looking at the row and pinching and holding.
Keywords: dummy data, seed, test data, populate, fake data
Prevents accidental modifications to catalog objects.
Where it is: Catalogs › Read only
Catalogs opens in Read-only: the toggle in its toolbar is on and the create and delete buttons are disabled. Turn it off to create or drop objects, and turn it back on when you explore a production database. If the connection carries a read-only environment tag, the switch isn’t there, and neither are the create and delete buttons: that session can’t write, so there is nothing to unlock.
Keywords: read only, read-only, protect, production
Create or modify a view; the options it offers depend on the engine.
Where it is: Catalogs › New view / Edit view
The View Editor opens when you press New view, or from the context menu of an existing view — right-click on macOS, press and hold on iPad — with Edit (visual form). It automatically generates the CREATE VIEW DDL.
Available fields:
- Name — name of the view in the selected database.
- The replace toggle — when enabled, the generated DDL replaces the view if it already exists, without dropping it first. The toggle is named after the clause it writes:
MySQLMariaDBAuroraPostgreSQLOracle
OR REPLACE — CREATE OR REPLACE VIEW.
SQL Server
OR ALTER — CREATE OR ALTER VIEW.
MySQLMariaDBAurora
- Algorithm — controls how the server processes the view:
- UNDEFINED (default) — the server chooses the optimal algorithm.
- MERGE — merges the view query with the outer query. More efficient.
- TEMPTABLE — materializes the view into a temporary table before applying external filters.
- SQL Security — defines the privileges under which the view executes:
- DEFINER (default) — executes with the privileges of the user who created it.
- INVOKER — executes with the privileges of the user querying it.
- CHECK OPTION — validates that rows modified through the view remain visible in it:
- LOCAL — only checks the condition of this view.
- CASCADED — also checks conditions of underlying views.
- SQL SELECT — the text editor with the SELECT query that defines the view's content.
Press Apply to execute the DDL on the server.
SQL ServerPostgreSQLOracle
There is no Algorithm or SQL Security: the sheet doesn't show them.
SQL ServerOracle
WITH CHECK OPTION has a single form, the one CASCADED describes: choosing LOCAL writes that one, and the sheet says so.
Apple Vision Pro: the context menu opens by looking at the row and pinching and holding.
Create or modify stored procedures and functions with a visual interface.
Where it is: Catalogs › New routine / Edit routine
The Routine Editor opens when you press New routine or edit an existing routine from the Routines tab of Catalogs. It generates the DDL that creates the procedure or function, or replaces the one with that name:
MySQLMariaDBAurora
Two statements, run one after the other: DROP PROCEDURE IF EXISTS and CREATE PROCEDURE (or DROP FUNCTION IF EXISTS and CREATE FUNCTION). MySQL has no CREATE OR REPLACE for routines, so the existing routine is dropped first: if the creation fails, it is no longer there.
PostgreSQL
CREATE OR REPLACE PROCEDURE or CREATE OR REPLACE FUNCTION.
SQL Server
CREATE OR ALTER PROCEDURE or CREATE OR ALTER FUNCTION.
Available fields:
- Name — name of the procedure or function.
- Type — select between PROCEDURE (procedure) or FUNCTION (function).
- Parameters — add parameters with the + button. Each parameter has:
- Mode: IN (input), OUT (output), INOUT (input and output). Only available for procedures.
- Name of the parameter.
- Data type (e.g. INT, VARCHAR(100), DATETIME).
- Return type (functions only) — the data type returned by the function (e.g. INT, VARCHAR(255)).
- Body — text editor with the code of the routine. It starts as an empty BEGIN … END block to write inside.
The Options section only shows what the server can write:
MySQLMariaDBAurora
- Deterministic — writes DETERMINISTIC if the routine always returns the same result for the same input parameters. Important for replication.
- Data access — declares the routine's behavior regarding the database:
- CONTAINS SQL — may contain SQL but does not read or write data.
- NO SQL — contains no SQL statements.
- READS SQL DATA — reads data but does not modify it.
- MODIFIES SQL DATA — may insert, update, or delete data.
PostgreSQL
Only Deterministic, and only for functions: it writes IMMUTABLE —the function always returns the same result for the same arguments and doesn't read the database—. A procedure has no such option, and there is no Data access: PostgreSQL has no such clause. The body is written in PL/pgSQL (LANGUAGE plpgsql).
SQL Server
There is no Options section: SQL Server works out by itself whether a function is deterministic, and has no data access clause. A parameter that is not IN is written @name type OUTPUT, and the @ is added if you leave it out.
Press Apply to execute the DDL on the server.
Oracle
With Oracle this editor does not open: there is no New routine nor editing of an existing routine. Its code is shown in the Catalogs DDL panel; to change it, take its CREATE OR REPLACE to the SQL Editor.
Keywords: routine, procedure, function, PROCEDURE, FUNCTION, parameters, IN, OUT, INOUT, deterministic, CONTAINS SQL, CREATE PROCEDURE, CREATE FUNCTION
Where it is: Catalogs › New trigger / Edit trigger
The Trigger Editor opens when you press New trigger in the Catalogs toolbar, or from the context menu of an existing trigger — right-click on macOS, press and hold on iPad — with Edit (visual form). The schema tree does not open it: its context menu opens none of the Catalogs editors.
Available fields:
- Name — name of the trigger.
- Timing — when the trigger fires relative to the operation:
MySQLMariaDBAuroraPostgreSQL
- BEFORE — executes before the operation. Allows modifying NEW values before they are written.
- AFTER — executes after the operation.
SQL Server
- AFTER — executes after the operation.
- INSTEAD OF — replaces the operation with the trigger's code.
There is no BEFORE.
- Event — the operation that fires the trigger:
- INSERT — when a row is inserted.
- UPDATE — when a row is updated.
- DELETE — when a row is deleted.
- Body — text editor with the trigger code. The starter body says, in comments, where the new and previous rows are.
Press Apply to execute the DDL on the server.
MySQLMariaDBAuroraPostgreSQL
The trigger fires once per row: NEW.column is the new value (in INSERT and UPDATE) and OLD.column the previous one (in UPDATE and DELETE).
PostgreSQL
A trigger's code lives in a function: Calíope creates the function <name>_fn with the body and the trigger that calls it, FOR EACH ROW. The body ends with RETURN: in a BEFORE trigger the row it returns is the one written, and NULL skips the row. The starter body returns COALESCE(NEW, OLD), which is right for the three events.
SQL Server
The trigger fires once per statement, not once per row, and the new and previous rows are in the inserted and deleted tables, not in NEW and OLD: the starter body says so.
Apple Vision Pro: the context menu opens by looking at the row and pinching and holding.
Oracle
With Oracle this editor does not open: there is no New trigger nor editing of an existing one. Its code is shown in the Catalogs DDL panel; to change it, take its CREATE OR REPLACE TRIGGER to the SQL Editor.
The Event Editor opens when you press New event in the Catalogs toolbar or edit an existing event. Requires the MySQL Event Scheduler to be enabled (SET GLOBAL event_scheduler = ON).
Available fields:
- Name — name of the event.
- Status — controls whether the event is active:
- ENABLE — the event executes according to its schedule.
- DISABLE — the event is paused.
- SLAVESIDE_DISABLED — disabled on a replication replica.
- Schedule type:
- One-time (AT) — executes at a specific date and time. Use the date/time picker to set the moment.
- Recurring (EVERY) — repeats every N time units. Configure the number and unit (SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, YEAR).
- Start (recurring) — when the recurring event begins executing.
- Dates and times are server time, with no time zone: the picker shows what the server has stored, not your device's clock.
- End date (recurring, optional) — enable the toggle to define when it stops executing.
- Body — SQL code executed by the event. Can be a BEGIN … END block with multiple statements.
Create an index on a table with configurable type, method, and columns.
Where it is: Catalogs › New index / Create index
The Index Editor opens from the Indexes tab of Catalogs: with a table selected, the New index button in the toolbar; on an index that already exists, the context menu — right-click on macOS, press and hold on iPad — with Edit (visual form). The schema tree does not open it.
Available fields:
- Name — name of the index on the table.
- Type:
- INDEX — standard index, allows duplicates.
- UNIQUE — unique index, rejects duplicate values in the indexed column or column combination.
MySQLMariaDBAurora
- FULLTEXT — full-text search. Only for CHAR, VARCHAR, or TEXT columns and the MyISAM/InnoDB engine (MySQL 5.6+).
- SPATIAL — geospatial data. Only for spatial-type columns, with MyISAM or InnoDB (MySQL 5.7+, MariaDB 10.2+).
MySQLMariaDBAuroraPostgreSQL
- Method (for INDEX and UNIQUE):
- BTREE — B-tree, the most common method. Supports range comparisons, LIKE 'abc%', ORDER BY.
- HASH — hash table, only supports exact equality comparisons.
MySQLMariaDBAurora
HASH is only honoured by the MEMORY engine.
PostgreSQL
A UNIQUE index can't be HASH, so with UNIQUE the method is BTREE. Other methods (gin, gist, brin) are not offered.
SQL ServerOracle
There is no Method: CREATE INDEX has no USING clause.
- Columns — add the columns that make up the index with the name field and, optionally, the prefix length (number of characters to index; useful for long TEXT or VARCHAR columns).
SQL ServerPostgreSQLOracle
There is no index prefix: the whole column is indexed, and the generated DDL says so in a comment.
The primary key is managed from the Columns tab of the Table Builder, not from this editor.
Press Apply to execute CREATE INDEX on the server.
ClickHouse
With ClickHouse this editor doesn't open: the tab is Keys and it has no New index. A skip index is added or removed in the Table Builder, on its Indexes tab.
Apple Vision Pro: the context menu opens by looking at the row and pinching and holding.
Oracle
With Oracle the type is INDEX or UNIQUE, and the index is created in its table’s schema. Dropping an index that backs a UNIQUE constraint drops the constraint, and the index with it.
Keywords: index, CREATE INDEX, UNIQUE, FULLTEXT, SPATIAL, BTREE, HASH, columns, prefix, ALTER TABLE, index editor
The search field filters by name the list of the open tab and empties when you switch tabs; refreshing reads the database again, and the list reloads by itself when another Calíope tool changes it.
Where it is: Workspace › Tools › Catalogs
The search field in the Catalogs bar filters by name the list of the tab that is open —tables, views, indexes, routines and the rest— and empties itself when you switch tabs.
The Refresh button reads the database again. You will rarely need it: when another Calíope tool creates or drops an object in that database, the list reloads by itself. Changes made from outside Calíope appear after refreshing.
The context menu of a table, an index, a routine or an event offers to delete it; the confirmation shows the DROP the engine will run.
Where it is: Workspace › Tools › Catalogs
The context menu of a table, an index, a routine or an event offers to delete it. Before doing anything, Calíope shows the DROP statement the engine will run and asks you to confirm; if the connection carries an environment tag that asks for confirmation, the dialog names it.
Views, triggers and sequences are not deleted from Catalogs: drop them from the SQL Editor.
With the padlock on, the option appears disabled; in a session that can only read, it is not drawn at all.