Class: AddSaseoTriggerFunction

Inherits:
ActiveRecord::Migration
  • Object
show all
Defined in:
lib/generators/saseo/templates/add_saseo_trigger_function.rb

Instance Method Summary collapse

Instance Method Details

#down ⇒ Object 



99
100
101
102
103
104
105
 
# File 'lib/generators/saseo/templates/add_saseo_trigger_function.rb', line 99

def down
  execute('DROP FUNCTION IF EXISTS saseo.audit_func() CASCADE')
  execute('DROP FUNCTION IF EXISTS saseo.whodunnit() CASCADE')
  execute('DROP TABLE IF EXISTS saseo.saseo_source_versions CASCADE')
  execute('DROP EXTENSION IF EXISTS "uuid-ossp" CASCADE')

end

#up ⇒ Object 



2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
 
# File 'lib/generators/saseo/templates/add_saseo_trigger_function.rb', line 2

def up

  uuid_extension_sql = 'CREATE EXTENSION IF NOT EXISTS "uuid-ossp"'

  audit_table_sql = "CREATE schema saseo;\n\nCREATE TABLE saseo.saseo_source_versions (\nid uuid PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),\ntransaction_id bigint NOT NULL,\ntable_name text NOT NULL,\naction_timestamp timestamp WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,\naction text NOT NULL CHECK (action IN ('INSERT','DELETE','UPDATE')),\nwhodunnit text NOT NULL,\nold_data jsonb,\nnew_data jsonb\n);\n\n"
  # originally copied from:
  # https://wiki.postgresql.org/wiki/Audit_trigger
  #
  # modified to use NOTIFY
  #
  # a trigger must be added to every table
  # CREATE TRIGGER identities_saseo_trigger AFTER INSERT OR UPDATE OR DELETE ON identities FOR EACH ROW EXECUTE PROCEDURE saseo.audit_func();
  whodunnit_sql = "CREATE OR REPLACE FUNCTION saseo.whodunnit() RETURNS TEXT AS $body$\nBEGIN\nreturn current_setting('saseo.whodunnit');\nEXCEPTION WHEN undefined_object THEN\nreturn current_user;\nEND;\n$body$\nLANGUAGE plpgsql\n"
  audit_sql = "CREATE OR REPLACE FUNCTION saseo.audit_func() RETURNS TRIGGER AS $body$\nDECLARE\nv_old_data json;\nv_new_data json;\nv_whodunnit text;\nv_channel text;\nv_audit_uuid uuid;\nBEGIN\n v_whodunnit := saseo.whodunnit();\n v_audit_uuid := uuid_generate_v4();\n\nv_channel := upper('SASEO' || '_' || TG_TABLE_NAME::TEXT || '_' || TG_OP || '_AUDIT');\n\nIF (TG_OP = 'UPDATE') THEN\n  v_old_data := row_to_json(OLD);\n  v_new_data := row_to_json(NEW);\n\n  INSERT INTO saseo.saseo_source_versions VALUES(v_audit_uuid, txid_current(), TG_TABLE_NAME, DEFAULT, TG_OP, v_whodunnit, v_old_data::JSONB, v_new_data::JSONB);\n  PERFORM pg_notify(v_channel, v_audit_uuid::TEXT);\n\n  RETURN NEW;\nELSIF (TG_OP = 'DELETE') THEN\n  v_old_data := row_to_json(OLD);\n\n  INSERT INTO saseo.saseo_source_versions VALUES(v_audit_uuid, txid_current(), TG_TABLE_NAME, DEFAULT, TG_OP, v_whodunnit, v_old_data::JSONB, NULL);\n  PERFORM pg_notify(v_channel, v_audit_uuid::TEXT);\n\n  RETURN OLD;\nELSIF (TG_OP = 'INSERT') THEN\n  v_new_data := row_to_json(NEW);\n\n  INSERT INTO saseo.saseo_source_versions VALUES(v_audit_uuid, txid_current(), TG_TABLE_NAME, DEFAULT, TG_OP, v_whodunnit, NULL, v_new_data::JSONB);\n  PERFORM pg_notify(v_channel, v_audit_uuid::TEXT);\n\n  RETURN NEW;\nELSE\n    RAISE WARNING '[SASEO.AUDIT_FUNC] - Other action occurred: %, at %',TG_OP,now();\n    RETURN NULL;\nEND IF;\n\nEXCEPTION\nWHEN data_exception THEN\n  RAISE WARNING '[SASEO.AUDIT_FUNC] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;\n  RETURN NULL;\nWHEN unique_violation THEN\n  RAISE WARNING '[SASEO.AUDIT_FUNC] - UDF ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;\n  RETURN NULL;\nWHEN OTHERS THEN\n  RAISE WARNING '[SASEO.AUDIT_FUNC] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;\n  RETURN NULL;\nEND;\n$body$\nLANGUAGE plpgsql\n"
  execute(uuid_extension_sql)
  execute(audit_table_sql)
  execute(whodunnit_sql)
  execute(audit_sql)
end