Catalogs

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, which are lambdas and are created and edited from here.

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)). Materialized views have their own tab, Materialized views: the table that stores their rows, which tables they read, when and how they refresh, the last refresh and whether they are up to date, with Refresh now. 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. Events lists the schema’s DBMS_SCHEDULER jobs.

SQL Server

With SQL Server, Indexed views sits between Views and Indexes: views with a unique clustered index that stores their rows. The card of the selected one says which index, how much it takes, how many rows it has and which tables it reads from, and its DDL carries the indexes after it; they no longer appear under Views, although the editor tree, backups and the comparator still treat them as views. Sequences lists the schema’s ones with minimum, maximum, step, cycle, cache and Last handed out: the last number NEXT VALUE FOR gave.

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.

MySQLMariaDBAurora

- New event — in the Events tab, opens the event scheduler editor

SQL Server

With SQL Server, Events are the Agent jobs whose step runs in this database, listed under its dbo, and New event creates one. In Express, which has no Agent, the tab says why it is empty.

PostgreSQL

Events are the jobs of the pg_cron extension, listed in its schema, cron: New event schedules them with cron.schedule. Without the extension, the Events tab says what is missing.

ClickHouse

With ClickHouse there are New view and New routine, which creates a lambda function: there is no New index —the tab is Keys, and a skip index is added from the Table Builder—, no New trigger and no Events tab.

Oracle

With Oracle you get New view, New index, New routine —a procedure or a function—, New trigger and New event, which is a DBMS_SCHEDULER job. A package is edited, but not created from here.

TiDB

With TiDB there are New view and New index. There is no New routine, no New trigger and no Events tab: TiDB has no stored routines, triggers or event scheduler, and Catalogs says so in a note. Its sequences are listed in the Sequences tab, with their DDL.

MariaDB

With MariaDB, routines, triggers and indexes are replaced in one statement (CREATE OR REPLACE): if it fails, the object you had is still there.

Oracle

With Oracle you can edit procedures, functions, packages, triggers and the DBMS_SCHEDULER jobs listed under Events. A trigger with several events, UPDATE OF columns, its own REFERENCING or COMPOUND, and a routine whose header the form cannot write, open but are not saved from the sheet: Open in ItemEditor takes them to the SQL editor.

ClickHouse

With ClickHouse, user-defined functions are edited with Edit (visual form), which opens the Routine editor with their parameters and their expression, and Open in ItemEditor takes their DDL to the SQL editor.

MySQLMariaDBAuroraTiDB

An ENUM or SET column gets values from its own list. During the load the foreign key check is turned off, so you can end up with rows that point to a parent that doesn't exist: they're useful for testing a JOIN with no match. A row with a repeated key or a CHECK that fails is discarded without an error, so fewer rows than you asked for may go in. A value out of range isn't discarded: it goes in clipped to the type's limit — a TINYINT of 300 ends up as 127 — and leaves a warning.

PostgreSQL

During the load the table's triggers don't fire, including the ones that check foreign keys, so you can end up with rows that point to a parent that doesn't exist. A row that repeats a unique key is discarded without an error. Turning the triggers off requires a superuser: without one, the load goes on with foreign keys checked, the final message says so, and a batch with a value that doesn't exist in the parent table stops with its warning.

SQL ServerOracle

The foreign key check stays on: a batch with a value that doesn't exist in the parent table stops with its warning, and so does one with a repeated key.

MySQLMariaDBAuroraPostgreSQLOracleTiDB

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.

TiDB

TiDB accepts Algorithm and SQL Security and keeps them in its SHOW CREATE VIEW. No TiDB view can be written to (IS_UPDATABLE is NO): rows are changed in its tables.

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.

TiDB

With TiDB this editor does not open: TiDB has no stored routines (it rejects CREATE PROCEDURE and CREATE FUNCTION), and Catalogs has no Routines tab.

MySQLAurora

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.

MariaDB

One statement: CREATE OR REPLACE PROCEDURE or CREATE OR REPLACE FUNCTION. If it fails, the routine you had is still there.

PostgreSQL

CREATE OR REPLACE PROCEDURE or CREATE OR REPLACE FUNCTION.

SQL Server

CREATE OR ALTER PROCEDURE or CREATE OR ALTER FUNCTION.

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. Language picks the body’s language among those the server has: plpgsql by default, sql and any others installed; editing a routine keeps its own. Two functions with the same name and different arguments —an overload— appear in the list each with its signature, precio(integer) and precio(text), and are edited and dropped separately.

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.

Oracle

With Oracle the editor writes CREATE OR REPLACE PROCEDURE or FUNCTION with the parameters as they were written —with their DEFAULT and %TYPE—, RETURN and DETERMINISTIC, and keeps what the header carries and the form does not show (AUTHID, RESULT_CACHE…). The body is what follows AS: the declarations and the block. A package is edited whole: its specification and its body, separated by a line with /. If the server stores it with compilation errors, the sheet shows each ALL_ERRORS line and does not report it as created.

ClickHouse

With ClickHouse routines are lambda functions: the editor only offers FUNCTION, parameters have a name and no type, there is no return type and no Options, and the body is an expression that names the parameters (k * x + b). It writes CREATE OR REPLACE FUNCTION name AS (x, k, b) -> k * x + b. The function belongs to the server, not to a database: its name carries no database in front. When you edit it, the body is the expression as the server stores it, with its parentheses.

TiDB

With TiDB this editor does not open: TiDB has no triggers (it rejects CREATE TRIGGER), and Catalogs has no Triggers tab.

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.

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. Editing a trigger without renaming it replaces it in one statement, CREATE OR REPLACE TRIGGER, from PostgreSQL 14; before that, it is dropped and created again.

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. Editing a trigger without renaming it replaces it in one statement, CREATE OR ALTER TRIGGER, from SQL Server 2016 SP1: there is no moment without the trigger, and if the new body does not compile the previous one stays; before that, it is dropped and created again.

Oracle

With Oracle the timing is BEFORE, AFTER or INSTEAD OF —the last one, on a view—, and there are two more switches: For each row (FOR EACH ROW) and Enabled (ENABLE or DISABLE). Saving is CREATE OR REPLACE TRIGGER, and any WHEN condition it had is kept. One with several events, UPDATE OF columns, its own REFERENCING or COMPOUND opens, but is not saved from here. If the server stores it with compilation errors, the sheet shows each ALL_ERRORS line.

PostgreSQL

With PostgreSQL the event is a pg_cron job: the editor writes cron.schedule(name, expression, body), which replaces a job that already has that name, and DISABLE leaves it inactive with cron.alter_job. There are only recurring ones: every N seconds (up to 59), minutes or hours that divide the hour and the day, and every day, week, month or year, at the start time in UTC. A one-time run or an end date do not exist in pg_cron, and the SQL preview says so. A job with any other cron expression shows in its DDL but does not open the editor.

SQL Server

With SQL Server the event is an Agent job with one T-SQL step that runs in this database: the editor writes sp_add_job, sp_add_jobstep, sp_add_jobschedule and sp_add_jobserver, and when editing, sp_update_job, sp_update_jobstep and sp_update_schedule, which keep its history. Jobs belong to the server, not to a schema: they are listed in the dbo of the database their step runs in, and in any other schema the tab says where they are. A job with several steps, or with a schedule the editor does not write, shows in its DDL but does not open the editor. Express and Azure SQL Database have no Agent, and there the tab doesn't appear.

Oracle

With Oracle the event is a DBMS_SCHEDULER job of the schema, a PL/SQL block: the editor writes DBMS_SCHEDULER.CREATE_JOB with job_type => 'PLSQL_BLOCK', and when editing, SET_ATTRIBUTE, ENABLE and DISABLE, which keep its history; a new name drops the job and creates it again. The schedule is FREQ=…;INTERVAL=n, with times in UTC. A job with another calendar, with a program or that is not a block shows in its DDL, but does not open the editor. Creating requires the CREATE JOB privilege.

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+).

SQL Server

- SPATIAL — geospatial data, on a geometry or geography column of a table with a primary key. For geometry you write the Box that bounds the data (xmin, ymin, xmax, ymax); for geography you leave it empty.

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. Besides BTREE and HASH it offers GIN —for jsonb, arrays and full text—, GIST and SPGIST —geometry and ranges— and BRIN, for very large tables ordered by insertion; editing an index keeps its own. The Build without blocking writes (CONCURRENTLY) checkbox creates the index without closing the table to writes, at the cost of taking longer.

SQL ServerOracle

There is no Method: CREATE INDEX has no USING clause.

SQL ServerPostgreSQLOracle

There is no index prefix: the whole column is indexed, and the generated DDL says so in a comment.

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.

Oracle

With Oracle the type is INDEX, UNIQUE, FULLTEXT —an Oracle Text index, INDEXTYPE IS CTXSYS.CONTEXT— or SPATIAL —MDSYS.SPATIAL_INDEX_V2, which first registers its USER_SDO_GEOM_METADATA row if missing, with the SRID of the existing geometries, or 4326 on an empty table—; both cover one column, and they only appear if the server has Oracle Text and Spatial. 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.

TiDB

With TiDB the types are INDEX and UNIQUE, and the method is BTREE: TiDB accepts FULLTEXT with a warning without creating the index, and has no spatial types to index.