ORT-SQLDIFF(1) General Commands Manual ORT-SQLDIFF(1)

ort-sqldiffproduce ort SQL schema differences

ort-sqldiff [-d] [oldconfig...] -f [config...]

ort-sqldiff [-d] oldconfig [config]

Accepts ort(5) config and oldconfig files, the latter defaulting to standard input, and produces SQL commands updating the old to new schema. Its arguments are as follows:

Allow some destructive changes.

The new configuration is preceded by -f. In a special case for simplicity, a single old and new configuration may be specified together without -f. Either oldconfig or config may be empty, and thus read from standard input, but not both.

ort-sqldiff outputs a series of CREATE TABLE and ALTER TABLE SQL commands to update the configuration oldconfig to the new configuration config. With -d, DROP TABLE SQL commands are also emitted. (Dropped columns are noted, but not actually dropped. This is a limitation of sqlite3(1).)

The configuration files are considered incompatible if any of the following are true:

When -d has been specified, ort-sqldiff the following destructive changes are allowed:

If fields are added to a structure (columns to a table), the configuration's default statement is used for generating an SQL default value. If a default statement is not provided for columns NOT NULL, the update will fail as-is and must be manually edited with default values.

It's good practise, but not enforced by ort-sqldiff, to wrap the edit script in a transaction.

% ( echo "BEGIN EXCLUSIVE TRANSACTION ;" \
    ort-sqldiff old.ort new.ort ; \
    echo "COMMIT TRANSACTION ;" ) | sqlite3 foo.db

There is no additional processing in the database edit script: if the script is run twice, it will attempt to create tables and modify columns twice.

The SQL generated is designed for sqlite3(1).

The ort-sqldiff utility exits 0 on success, and >0 if an error occurs.

Let an existing configuration, old.ort, consist of a "client" structure.

struct client {
  field name text limit gt 0;
  field id int rowid;
};

A new configuration, new.ort, augments this structure by adding a new field, and also adds a structure.

struct client {
  field name text limit gt 0;
  field dob epoch default 0;
  field id int rowid;
};
struct admin {
  field name text limit gt 0;
  field id int rowid;
};

This generates the following update script.

% ort-sqldiff old.ort new.ort
PRAGMA foreign_keys=ON;
CREATE TABLE admin (
  name TEXT NOT NULL,
  id INTEGER PRIMARY KEY
);
ALTER TABLE client ADD COLUMN dob INTEGER NOT NULL DEFAULT 0;

Since edit scripts are always non-destructive unless -d was specified, the results may be piped directly into the database.

% ort-sqldiff old.ort new.ort | sqlite3 foo.db

ort-sql(1), sqlite3(1), ort(5)

March 18, 2021 OpenBSD 6.7