NAME

ort-sqldiff — produce ort SQL schema differences

SYNOPSIS

DESCRIPTION

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:

-d

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, ALTER TABLE, CREATE INDEX, and DROP INDEX 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:

• dropped enumerations
• dropped enumeration items
• changed enumeration item values
• dropped bitfields
• dropped bitfield items
• changed bitfield item values
• dropped structures
• dropped fields
• changed field types
• changed foreign key references
• changed SQL attributes (rowid, null)

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

• dropped enumerations
• dropped enumeration items
• dropped bitfields
• dropped bitfield items
• dropped tables
• dropped table columns

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.

Unique field constraints and struct statements are handled by SQL unique indexes. These are named by the unique fields (sorted), separated by underscores if applicable, and prefixed with “unique”. These are created or dropped when applicable.

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

EXIT STATUS

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

EXAMPLES

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

SEE ALSO

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