META-INF.table.sql Maven / Gradle / Ivy

Go to download
CREATE TABLE sprtool_record_event_processor (
   	id serial primary key,
	code VARCHAR(60) UNIQUE,
	tab VARCHAR(60) text
);

CREATE TABLE sprtool_record_event_partition (
   	id serial primary key,
	proc       INT not null,
	part	  INT not null,
	UNIQUE (proc,part)
);

CREATE TABLE sprtool_record_event_record (
   id serial8 primary key,
   proc     INT not null,
   part     INT not null default 0,
   typ      char(1) not null  default 'I',
   creat    timestamp (0) with time zone not null default current_timestamp,
   execut   timestamp (0) with time zone null,
   schedule timestamp (0) with time zone not null default current_timestamp,
   complete timestamp (0) with time zone null,  
   purge    timestamp (0) with time zone null,
   cost     int null,
   retries  int default 0,
   state    char(1) not null default 'N',
   payload  text not null,
   cause    text null
);

CREATE OR REPLACE FUNCTION sprtool_record_event_partition_tr() RETURNS trigger AS $$
    DECLARE
    	json text;
    	rec RECORD;
    BEGIN
	    SELECT json_build_object('@type','partition','id',new.id,'proc',new.proc,'part',new.part)::text INTO json;
	    PERFORM pg_notify('sprtool_record_event',json);
		RETURN NEW;
    END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS sprtool_record_event_partition_tr ON sprtool_record_event_partition;
CREATE TRIGGER sprtool_record_event_partition_tr AFTER INSERT ON sprtool_record_event_partition
    FOR EACH ROW EXECUTE PROCEDURE sprtool_record_event_partition_tr();
    
CREATE OR REPLACE FUNCTION sprtool_record_event_record_tr() RETURNS trigger AS $$
    DECLARE
    	json text;
    	rec RECORD;
    BEGIN
	    SELECT json_build_object('@type','event','id',new.id,'proc',new.proc,'part',new.part,'typ',new.typ,
	    	'creat',to_char(new.creat, 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM'),'schedule',to_char(new.schedule, 'YYYY-MM-DD"T"HH24:MI:SSTZH:TZM'),
	    	'payload',new.payload,'retries',new.retries)::text INTO json;
	    PERFORM pg_notify('sprtool_record_event',json);
		RETURN NEW;
    END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS sprtool_record_event_record_tr ON sprtool_record_event_record;
CREATE TRIGGER sprtool_record_event_record_tr AFTER INSERT ON sprtool_record_event_record
    FOR EACH ROW EXECUTE PROCEDURE sprtool_record_event_record_tr();

CREATE OR REPLACE FUNCTION XXXXXX_tr() RETURNS trigger AS $$
    DECLARE
    BEGIN
	    INSERT INTO sprtool_record_event_record(proc,typ,part,payload)
	    	SELECT id,CASE WHEN TG_OP='INSERT' THEN 'I' WHEN TG_OP='UPDATE' THEN 'U' ELSE 'D' END,
	    		new.some_column%10,json_build_object('key',new.fieldValue)::text
	    	FROM sprtool_record_event_processor WHERE tab=lower(TG_TABLE_NAME);
	    RETURN NEW;
    END;
$$ LANGUAGE plpgsql;
DROP TRIGGER IF EXISTS XXXXXX_tr ON xxxxxx_some_table;
CREATE TRIGGER XXXXXX_tr AFTER INSERT ON xxxxxx_some_table
    FOR EACH ROW EXECUTE PROCEDURE XXXXXX_tr();