-- SWH DB schema upgrade
-- from_version: 73
-- to_version: 74
-- description: Add notifications for object creations in the whole database
insert into dbversion(version, release, description)
values(74, now(), 'Work In Progress');
CREATE OR REPLACE FUNCTION notify_new_content() RETURNS trigger
LANGUAGE plpgsql
AS $$
begin
perform pg_notify('new_content', encode(new.sha1, 'hex'));
return null;
end;
$$;
CREATE OR REPLACE FUNCTION notify_new_directory() RETURNS trigger
LANGUAGE plpgsql
AS $$
begin
perform pg_notify('new_directory', encode(new.id, 'hex'));
return null;
end;
$$;
CREATE OR REPLACE FUNCTION notify_new_origin() RETURNS trigger
LANGUAGE plpgsql
AS $$
begin
perform pg_notify('new_origin', new.id::text);
return null;
end;
$$;
CREATE OR REPLACE FUNCTION notify_new_origin_visit() RETURNS trigger
LANGUAGE plpgsql
AS $$
begin
perform pg_notify('new_origin_visit', json_build_object(
'origin', new.origin,
'visit', new.visit
)::text);
return null;
end;
$$;
CREATE OR REPLACE FUNCTION notify_new_release() RETURNS trigger
LANGUAGE plpgsql
AS $$
begin
perform pg_notify('new_release', encode(new.id, 'hex'));
return null;
end;
$$;
CREATE OR REPLACE FUNCTION notify_new_revision() RETURNS trigger
LANGUAGE plpgsql
AS $$
begin
perform pg_notify('new_revision', encode(new.id, 'hex'));
return null;
end;
$$;
CREATE OR REPLACE FUNCTION notify_new_skipped_content() RETURNS trigger
LANGUAGE plpgsql
AS $$
begin
perform pg_notify('new_skipped_content', json_build_object(
'sha1', encode(new.sha1, 'hex'),
'sha1_git', encode(new.sha1_git, 'hex'),
'sha256', encode(new.sha256, 'hex')
)::text);
return null;
end;
$$;
CREATE TRIGGER notify_new_content
AFTER INSERT ON content
FOR EACH ROW
EXECUTE PROCEDURE notify_new_content();
CREATE TRIGGER notify_new_directory
AFTER INSERT ON directory
FOR EACH ROW
EXECUTE PROCEDURE notify_new_directory();
CREATE TRIGGER notify_new_origin_visit
AFTER INSERT ON origin_visit
FOR EACH ROW
EXECUTE PROCEDURE notify_new_origin_visit();
CREATE TRIGGER notify_new_origin
AFTER INSERT ON origin
FOR EACH ROW
EXECUTE PROCEDURE notify_new_origin();
CREATE TRIGGER notify_new_release
AFTER INSERT ON "release"
FOR EACH ROW
EXECUTE PROCEDURE notify_new_release();
CREATE TRIGGER notify_new_revision
AFTER INSERT ON revision
FOR EACH ROW
EXECUTE PROCEDURE notify_new_revision();
CREATE TRIGGER notify_new_skipped_content
AFTER INSERT ON skipped_content
FOR EACH ROW
EXECUTE PROCEDURE notify_new_skipped_content();