Skip to content

Instantly share code, notes, and snippets.

/table_log-0_4_4_snaga.diff

Created December 15, 2012 04:42
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save anonymous/4291369 to your computer and use it in GitHub Desktop.
Save anonymous/4291369 to your computer and use it in GitHub Desktop.
Download ZIP
table_log-0_4_4_snaga.diff
Raw
table_log-0_4_4_snaga.diff
diff -rcN table_log-0.4.4.orig/Makefile table_log-0.4.4/Makefile
*** table_log-0.4.4.orig/Makefile 2007-05-17 07:15:04.000000000 +0900
--- table_log-0.4.4/Makefile 2012-11-26 13:20:53.000000000 +0900
***************
*** 1,5 ****
MODULES = table_log
! DATA_built = table_log.sql
DOCS = README.table_log
ifdef USE_PGXS
--- 1,5 ----
MODULES = table_log
! DATA_built = table_log.sql table_log_uninstall.sql
DOCS = README.table_log
ifdef USE_PGXS
diff -rcN table_log-0.4.4.orig/table_log.sql.in table_log-0.4.4/table_log.sql.in
*** table_log-0.4.4.orig/table_log.sql.in 2007-04-28 09:17:08.000000000 +0900
--- table_log-0.4.4/table_log.sql.in 2012-11-26 13:17:06.000000000 +0900
***************
*** 1,72 ****
! --
! -- table_log () -- log changes to another table
! --
! --
! -- see README.table_log for details
! --
! --
! -- written by Andreas ' ads' Scherbaum (ads@pgug.de)
! --
! --
!
! -- drop old trigger
! DROP TRIGGER test_log_chg ON test; -- ignore any error
!
! -- create demo table
! DROP TABLE test; -- ignore any error
! CREATE TABLE test (
! id INT NOT NULL
! PRIMARY KEY,
! name VARCHAR(20) NOT NULL
! );
!
! -- create the table without data from demo table
! DROP TABLE test_log; -- ignore any error
! SELECT * INTO test_log FROM test LIMIT 0;
! ALTER TABLE test_log ADD COLUMN trigger_mode VARCHAR(10);
! ALTER TABLE test_log ADD COLUMN trigger_tuple VARCHAR(5);
! ALTER TABLE test_log ADD COLUMN trigger_changed TIMESTAMPTZ;
! ALTER TABLE test_log ADD COLUMN trigger_id BIGINT;
! CREATE SEQUENCE test_log_id;
! SELECT SETVAL('test_log_id', 1, FALSE);
! ALTER TABLE test_log ALTER COLUMN trigger_id SET DEFAULT NEXTVAL('test_log_id');
!
! -- drop old function
! DROP FUNCTION table_log (); -- ignore any error
! DROP FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR, INT, INT);
! DROP FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR, INT);
! DROP FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR);
! DROP FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ);
! -- create function
! CREATE FUNCTION table_log ()
RETURNS TRIGGER
AS 'MODULE_PATHNAME' LANGUAGE 'C';
! CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR, INT, INT)
RETURNS VARCHAR
AS 'MODULE_PATHNAME', 'table_log_restore_table' LANGUAGE 'C';
! CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR, INT)
RETURNS VARCHAR
AS 'MODULE_PATHNAME', 'table_log_restore_table' LANGUAGE 'C';
! CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR)
RETURNS VARCHAR
AS 'MODULE_PATHNAME', 'table_log_restore_table' LANGUAGE 'C';
! CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ)
RETURNS VARCHAR
AS 'MODULE_PATHNAME', 'table_log_restore_table' LANGUAGE 'C';
! -- create trigger
! CREATE TRIGGER test_log_chg AFTER UPDATE OR INSERT OR DELETE ON test FOR EACH ROW
! EXECUTE PROCEDURE table_log();
!
! -- test trigger
! INSERT INTO test VALUES (1, 'name');
! SELECT * FROM test;
! SELECT * FROM test_log;
! UPDATE test SET name='other name' WHERE id=1;
! SELECT * FROM test;
! SELECT * FROM test_log;
!
! -- create restore table
! SELECT table_log_restore_table('test', 'id', 'test_log', 'trigger_id', 'test_recover', NOW());
! SELECT * FROM test_recover;
--- 1,160 ----
! SET search_path TO public;
! CREATE OR REPLACE FUNCTION table_log ()
RETURNS TRIGGER
AS 'MODULE_PATHNAME' LANGUAGE 'C';
! CREATE OR REPLACE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR, INT, INT)
RETURNS VARCHAR
AS 'MODULE_PATHNAME', 'table_log_restore_table' LANGUAGE 'C';
! CREATE OR REPLACE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR, INT)
RETURNS VARCHAR
AS 'MODULE_PATHNAME', 'table_log_restore_table' LANGUAGE 'C';
! CREATE OR REPLACE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR)
RETURNS VARCHAR
AS 'MODULE_PATHNAME', 'table_log_restore_table' LANGUAGE 'C';
! CREATE OR REPLACE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ)
RETURNS VARCHAR
AS 'MODULE_PATHNAME', 'table_log_restore_table' LANGUAGE 'C';
! CREATE OR REPLACE FUNCTION table_log_init(int, text, text, text, text) RETURNS void AS '
! DECLARE
! level ALIAS FOR $1;
! orig_schema ALIAS FOR $2;
! orig_name ALIAS FOR $3;
! log_schema ALIAS FOR $4;
! log_name ALIAS FOR $5;
! do_log_user int = 0;
! level_create text = '''';
! orig_qq text;
! log_qq text;
! BEGIN
! -- Quoted qualified names
! orig_qq := quote_ident(orig_schema)||''.''||quote_ident(orig_name);
! log_qq := quote_ident(log_schema)||''.''||quote_ident(log_name);
!
! IF level <> 3 THEN
! level_create := level_create
! ||'', trigger_id BIGSERIAL NOT NULL PRIMARY KEY'';
! IF level <> 4 THEN
! level_create := level_create
! ||'', trigger_user VARCHAR(32) NOT NULL'';
! do_log_user := 1;
! IF level <> 5 THEN
! RAISE EXCEPTION
! ''table_log_init: First arg has to be 3, 4 or 5.'';
! END IF;
! END IF;
! END IF;
!
! EXECUTE ''CREATE TABLE ''||log_qq
! ||''(LIKE ''||orig_qq
! ||'', trigger_mode VARCHAR(10) NOT NULL''
! ||'', trigger_tuple VARCHAR(5) NOT NULL''
! ||'', trigger_changed TIMESTAMPTZ NOT NULL''
! ||level_create
! ||'')'';
!
! EXECUTE ''CREATE TRIGGER "table_log_trigger" AFTER UPDATE OR INSERT OR DELETE ON ''
! ||orig_qq||'' FOR EACH ROW EXECUTE PROCEDURE table_log(''
! ||quote_literal(log_name)||'',''
! ||do_log_user||'',''
! ||quote_literal(log_schema)||'')'';
!
! RETURN;
! END;
! ' LANGUAGE plpgsql;
!
!
! CREATE OR REPLACE FUNCTION table_log_init(int, text) RETURNS void AS '
! DECLARE
! level ALIAS FOR $1;
! orig_name ALIAS FOR $2;
! BEGIN
! PERFORM table_log_init(level, orig_name, current_schema());
! RETURN;
! END;
! ' LANGUAGE plpgsql;
!
!
! CREATE OR REPLACE FUNCTION table_log_init(int, text, text) RETURNS void AS '
! DECLARE
! level ALIAS FOR $1;
! orig_name ALIAS FOR $2;
! log_schema ALIAS FOR $3;
! BEGIN
! PERFORM table_log_init(level, current_schema(), orig_name, log_schema);
! RETURN;
! END;
! ' LANGUAGE plpgsql;
!
!
! CREATE OR REPLACE FUNCTION table_log_init(int, text, text, text) RETURNS void AS '
! DECLARE
! level ALIAS FOR $1;
! orig_schema ALIAS FOR $2;
! orig_name ALIAS FOR $3;
! log_schema ALIAS FOR $4;
! BEGIN
! PERFORM table_log_init(level, orig_schema, orig_name, log_schema,
! CASE WHEN orig_schema=log_schema
! THEN orig_name||''_log'' ELSE orig_name END);
! RETURN;
! END;
! ' LANGUAGE plpgsql;
!
! CREATE OR REPLACE FUNCTION disable_table_log(text, text, text, text) RETURNS void AS '
! DECLARE
! orig_schema ALIAS FOR $1;
! orig_name ALIAS FOR $2;
! log_schema ALIAS FOR $3;
! log_name ALIAS FOR $4;
! orig_qq text;
! log_qq text;
! BEGIN
! -- Quoted qualified names
! orig_qq := quote_ident(orig_schema)||''.''||quote_ident(orig_name);
! log_qq := quote_ident(log_schema)||''.''||quote_ident(log_name);
!
! -- EXECUTE ''DROP TABLE ''||log_qq;
!
! EXECUTE ''DROP TRIGGER "table_log_trigger" ON ''||orig_qq;
!
! RETURN;
! END;
! ' LANGUAGE plpgsql;
!
!
! CREATE OR REPLACE FUNCTION disable_table_log(text) RETURNS void AS '
! DECLARE
! orig_name ALIAS FOR $1;
! BEGIN
! PERFORM disable_table_log(orig_name, current_schema());
! RETURN;
! END;
! ' LANGUAGE plpgsql;
!
!
! CREATE OR REPLACE FUNCTION disable_table_log(text, text) RETURNS void AS '
! DECLARE
! orig_name ALIAS FOR $1;
! log_schema ALIAS FOR $2;
! BEGIN
! PERFORM disable_table_log(current_schema(), orig_name, log_schema);
! RETURN;
! END;
! ' LANGUAGE plpgsql;
!
!
! CREATE OR REPLACE FUNCTION disable_table_log(text, text, text) RETURNS void AS '
! DECLARE
! orig_schema ALIAS FOR $1;
! orig_name ALIAS FOR $2;
! log_schema ALIAS FOR $3;
! BEGIN
! PERFORM disable_table_log(orig_schema, orig_name, log_schema,
! CASE WHEN orig_schema=log_schema
! THEN orig_name||''_log'' ELSE orig_name END);
! RETURN;
! END;
! ' LANGUAGE plpgsql;
!
diff -rcN table_log-0.4.4.orig/table_log_uninstall.sql.in table_log-0.4.4/table_log_uninstall.sql.in
*** table_log-0.4.4.orig/table_log_uninstall.sql.in 1970-01-01 09:00:00.000000000 +0900
--- table_log-0.4.4/table_log_uninstall.sql.in 2012-11-26 13:17:06.000000000 +0900
***************
*** 0 ****
--- 1,17 ----
+ SET search_path TO public;
+
+ DROP FUNCTION table_log ();
+ DROP FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR, INT, INT);
+ DROP FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR, INT);
+ DROP FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR);
+ DROP FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ);
+
+ DROP FUNCTION table_log_init(int, text, text, text, text);
+ DROP FUNCTION table_log_init(int, text);
+ DROP FUNCTION table_log_init(int, text, text);
+ DROP FUNCTION table_log_init(int, text, text, text);
+
+ DROP FUNCTION disable_table_log(text, text, text, text);
+ DROP FUNCTION disable_table_log(text);
+ DROP FUNCTION disable_table_log(text, text);
+ DROP FUNCTION disable_table_log(text, text, text);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment