Skip to content

Instantly share code, notes, and snippets.

@otheus
Created September 25, 2017 18:51
Show Gist options
  • Save otheus/0d7f8263ade0270ef4b05936b2bded13 to your computer and use it in GitHub Desktop.
Save otheus/0d7f8263ade0270ef4b05936b2bded13 to your computer and use it in GitHub Desktop.
Materialized view of RHN's Satellite rhnchecksum
-- See http://tech.jonathangardner.net/wiki/PostgreSQL/Materialized_Views
drop function if exists rhncs_mv_refresh();
create function rhncs_mv_refresh() returns void
security definer language 'plpgsql' as $BODY$
begin
truncate rhnchecksumview;
insert into rhnchecksumview select c.id,ct.label as checksum_type,c.checksum from rhnchecksum c left join rhnchecksumtype ct on (c.checksum_type_id = ct.id);
return;
end
$BODY$;
drop function if exists rhncs_mv_trigger_id() CASCADE;
create function rhncs_mv_trigger_id() returns TRIGGER
security definer language 'plpgsql' as $BODY$
begin
if (TG_OP = 'DELETE') THEN
DELETE FROM rhnchecksumview
WHERE id = OLD.id;
RETURN OLD;
ELSIF (TG_OP = 'INSERT') THEN
INSERT INTO rhnchecksumview (id,checksum,checksum_type) VALUES (NEW.id,NEW.checksum,
(select label FROM rhnchecksumtype where id = NEW.checksum_type_id)
);
RETURN NEW;
ELSIF (TG_OP = 'UPDATE') THEN
UPDATE rhnchecksumview SET id = NEW.id,checksum = NEW.checksum, checksum_type =
(select label FROM rhnchecksumtype where id = NEW.checksum_type_id)
WHERE id = OLD.id;
RETURN NEW;
END IF;
RETURN NULL;
end
$BODY$;
DROP TRIGGER IF EXISTS rhncs_mv_dt_id ON rhnchecksum;
CREATE TRIGGER rhncs_mv_dt_id AFTER DELETE ON rhnchecksum
FOR EACH ROW EXECUTE PROCEDURE rhncs_mv_trigger_id();
DROP TRIGGER IF EXISTS rhncs_mv_ut_id ON rhnchecksum;
CREATE TRIGGER rhncs_mv_ut_id AFTER UPDATE ON rhnchecksum
FOR EACH ROW EXECUTE PROCEDURE rhncs_mv_trigger_id();
DROP TRIGGER IF EXISTS rhncs_mv_it_id ON rhnchecksum;
CREATE TRIGGER rhncs_mv_it_id AFTER INSERT ON rhnchecksum
FOR EACH ROW EXECUTE PROCEDURE rhncs_mv_trigger_id();
-- "b" triggers (rhnchecksumtype)
drop function if exists rhncs_mv_trigger_type() CASCADE;
create function rhncs_mv_trigger_type() RETURNS TRIGGER
SECURITY DEFINER LANGUAGE 'plpgsql' AS $BODY$
BEGIN
if (TG_OP = 'DELETE') THEN
RETURN NULL;
ELSIF (TG_OP = 'INSERT') THEN
RETURN NULL;
ELSIF (TG_OP = 'UPDATE') THEN
UPDATE rhnchecksumview SET checksum_type = NEW.label where checksum_type = OLD.label ;
END IF;
RETURN NULL;
END
$BODY$;
DROP TRIGGER IF EXISTS rhncs_mv_ut_type ON rhnchecksumtype;
CREATE TRIGGER rhncs_mv_ut_type AFTER UPDATE ON rhnchecksumtype
FOR EACH ROW EXECUTE PROCEDURE rhncs_mv_trigger_type();
-- _it_ is not necessary, and _dt_ is undefined behavior
-- TABLE DEFINITONS --
drop view if exists rhnchecksumview;
drop table if exists rhnchecksumview_mat;
drop table if exists rhnchecksumview;
create table rhnchecksumview as
select c.id,ct.label as checksum_type,c.checksum
from rhnchecksum c,rhnchecksumtype ct
where c.checksum_type_id = ct.id ;
alter table rhnchecksumview add primary key (id) ;
-- Test cases
-- Test new checksum:
-- insert into rhnchecksum values ( nextval('rhnchecksum_seq'),5,'blahblahblah');
-- select * from rhnchecksumview where id = currval('rhnchecksum_seq');
-- >> 20029681 | sha512 | blahblahblah
-- Test update checksum_type:
-- select * from rhnchecksumview where checksum_type = 'sha512' ;
-- >> 20029681 | sha512 | blahblahblah
-- update rhnchecksumtype set label = 'shasux' where label = 'sha512' ;
-- select * from rhnchecksumview where checksum_type = 'shasux' ;
-- >> 20029681 | shasux | blahblahblah
-- update rhnchecksumtype set label = 'sha512' where label = 'shasux' ;
-- Test update-checksum:
-- update rhnchecksum set checksum = 'hello' where id = currval('rhnchecksum_seq') ;
-- select * from rhnchecksumview where id = currval('rhnchecksum_seq') ;
-- >> 20029681 | sha512 | 'hellow'
-- Test remove checksum:
-- delete from rhnchecksumview where id = currval('rhnchecksum_seq');
-- select * from rhnchecksumview where id = currval('rhnchecksum_seq');
-- >> 0 rows
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment