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

ort-sqldiff
produce ort SQL schema differences

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

ort-sqldiff [-d] oldconfig [config]

The ort-sqldiff utility 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:
Apply and allow some destructive changes. See Delete mode.

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. In Delete mode, 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 they contain destructive differences: dropped objects (structures or fields) or different fields (types, references, attributes).

If fields are added to a structure (columns to a table), the 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.kwbp new.kwbp ; \
    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).

When -d has been specified, ort-sqldiff destructive changes are allowed. These consist of the following:
  • dropped enumerations and bitfields
  • dropped enumeration and bitfield items
  • dropped tables
  • dropped table columns

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

Let an existing configuration, old.kwbp, consist of a “client” structure.
struct client {
  field name text limit gt 0;
  field id int rowid;
};

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

struct client {
  field name text limit gt 0;
  field dob epoch;
  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.kwbp new.kwbp
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.kwbp new.kwbp | sqlite3 foo.db

ort-sql(1), sqlite3(1), ort(5)
July 17, 2019 OpenBSD 6.5