Back in February, I defined the film table version 0.1 with just three columns:
CREATE TABLE film ( id INTEGER NOT NULL PRIMARY KEY, title VARCHAR(32) NOT NULL, release_year INTEGER NOT NULL CHECK (release_year >= 1888) );
It was unclear then how far I’d go in supporting all of PostgreSQL data definition features. Now, Pyrseas is making progress towards covering all those capabilities.
Consequently, I’d like to add one column to the above and tag it as version 0.1.1. This is the column to be added (from the Pagila sample database):
last_update timestamp with time zone NOT NULL DEFAULT now()
With this new column, I’ll be able to explore another kind of field in the database user interface: one that will not be present on new records (because the DEFAULT will take care of filling in that data), but will be displayed, in readonly mode, for updates. Note: In Pagila, last_update is defined as timestamp without time zone, but that seems too ambiguous for my taste.
To complete this, we need an ON UPDATE trigger together with a function to be called by the trigger. These are also provided by Pagila, but to highlight some of the capabilities of Pyrseas, I’ll first show the output of dbtoyaml against a database with all the necessary objects:
language plpgsql: trusted: true schema public: description: standard public schema function last_updated(): language: plpgsql returns: trigger source: "\nBEGIN\n NEW.last_update = CURRENT_TIMESTAMP;\n RETURN NEW;\n\ END " table film: check_constraints: film_release_year_check: columns: - release_year expression: (release_year >= 1888) columns: - id: not_null: true type: integer - title: not_null: true type: character varying(32) - release_year: not_null: true type: integer - last_update: default: now() not_null: true type: timestamp with time zone primary_key: film_pkey: access_method: btree columns: - id triggers: last_updated: events: - update level: row procedure: last_updated() timing: before
If you feed this YAML to yamltodb against a database that already has the film table version 0.1, it generates the following (against a PostgreSQL 8.4 DBMS—vs. 9.0 the creation of PL/pgSQL is bypassed):
BEGIN; CREATE LANGUAGE plpgsql; SET check_function_bodies = false; CREATE FUNCTION last_updated() RETURNS trigger LANGUAGE plpgsql AS $_$ BEGIN NEW.last_update = CURRENT_TIMESTAMP; RETURN NEW; END $_$; ALTER TABLE film ADD COLUMN last_update timestamp with time zone NOT NULL DEFAULT now(); CREATE TRIGGER last_updated BEFORE UPDATE ON film FOR EACH ROW EXECUTE PROCEDURE last_updated(); COMMIT;
Thus we can successfully upgrade the version 0.1 database to version 0.1.1, with our audit trail column.
Happy trails to you, ’till we meet again …