diff --git a/sql/upgrades/128.sql b/sql/upgrades/128.sql new file mode 100644 --- /dev/null +++ b/sql/upgrades/128.sql @@ -0,0 +1,23 @@ +-- SWH DB schema upgrade +-- from_version: 127 +-- to_version: 128 +-- description: Add snapshot trigger event on insertion + +insert into dbversion(version, release, description) + values(128, now(), 'Work In Progress'); + +-- Asynchronous notification of new snapshot insertions +create function notify_new_snapshot() + returns trigger + language plpgsql +as $$ + begin + perform pg_notify('new_snapshot', json_build_object('id', encode(new.id, 'hex'))::text); + return null; + end; +$$; + +create trigger notify_new_snapshot + after insert on snapshot + for each row + execute procedure notify_new_snapshot(); diff --git a/swh/storage/sql/30-swh-schema.sql b/swh/storage/sql/30-swh-schema.sql --- a/swh/storage/sql/30-swh-schema.sql +++ b/swh/storage/sql/30-swh-schema.sql @@ -12,7 +12,7 @@ -- latest schema version insert into dbversion(version, release, description) - values(127, now(), 'Work In Progress'); + values(128, now(), 'Work In Progress'); -- a SHA1 checksum create domain sha1 as bytea check (length(value) = 20); diff --git a/swh/storage/sql/70-swh-triggers.sql b/swh/storage/sql/70-swh-triggers.sql --- a/swh/storage/sql/70-swh-triggers.sql +++ b/swh/storage/sql/70-swh-triggers.sql @@ -128,3 +128,20 @@ after insert on release for each row execute procedure notify_new_release(); + + +-- Asynchronous notification of new snapshot insertions +create function notify_new_snapshot() + returns trigger + language plpgsql +as $$ + begin + perform pg_notify('new_snapshot', json_build_object('id', encode(new.id, 'hex'))::text); + return null; + end; +$$; + +create trigger notify_new_snapshot + after insert on snapshot + for each row + execute procedure notify_new_snapshot();