Materialized view of RHN's Satellite rhnchecksum

extensions.sql

-- 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