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

NAME

kwebapp-sqldiffgenerate SQL web application schema differences

SYNOPSIS

kwebapp-sqldiff [oldconfig...] -f [config...]

kwebapp-sqldiff oldconfig [config]

DESCRIPTION

The kwebapp-sqldiff utility accepts kwebapp(5) configurations config and oldconfig and produces an SQL schema describing the configuration. 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.
kwebapp-sqldiff outputs a series of CREATE TABLE and ALTER TABLE SQL commands to update the configuration oldconfig to the new configuration config.
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 kwebapp-sqldiff, to wrap the edit script in a transaction.
% ( echo "BEGIN EXCLUSIVE TRANSACTION ;" \ 
    kwebapp-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).

EXIT STATUS

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

EXAMPLES

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.
% kwebapp-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, the results may be piped directly into the database.
% kwebapp-sqldiff old.kwbp new.kwbp | sqlite3 foo.db

SEE ALSO

kwebapp-sql(1), sqlite3(1), kwebapp(5)
September 7, 2018 OpenBSD 6.3