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 



94
95
96
97
98
99
100
101
 
# File 'lib/generators/saseo/templates/add_saseo_trigger_function.rb', line 94

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 SCHEMA IF EXISTS saseo 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
 
# 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 = <<SQL
CREATE schema IF NOT EXISTS saseo;
DROP TABLE IF EXISTS saseo.saseo_source_versions;

CREATE TABLE saseo.saseo_source_versions (
id uuid PRIMARY KEY NOT NULL DEFAULT uuid_generate_v4(),
transaction_id bigint NOT NULL,
table_name text NOT NULL,
action_timestamp timestamp WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
action text NOT NULL CHECK (action IN ('INSERT','DELETE','UPDATE')),
whodunnit text NOT NULL,
old_data jsonb,
new_data jsonb
);

SQL
  # 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 = <<SQL
CREATE OR REPLACE FUNCTION saseo.whodunnit() RETURNS TEXT AS $body$
BEGIN
return current_setting('saseo.whodunnit');
EXCEPTION WHEN undefined_object THEN
return current_user;
END;
$body$
LANGUAGE plpgsql
SQL
  audit_sql = <<SQL
CREATE OR REPLACE FUNCTION saseo.audit_func() RETURNS TRIGGER AS $body$
DECLARE
v_old_data json;
v_new_data json;
v_whodunnit text;
v_audit_uuid uuid;
BEGIN
 v_whodunnit := saseo.whodunnit();
 v_audit_uuid := uuid_generate_v4();

IF (TG_OP = 'UPDATE') THEN
  v_old_data := row_to_json(OLD);
  v_new_data := row_to_json(NEW);

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

  RETURN NEW;
ELSIF (TG_OP = 'DELETE') THEN
  v_old_data := row_to_json(OLD);

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

  RETURN OLD;
ELSIF (TG_OP = 'INSERT') THEN
  v_new_data := row_to_json(NEW);

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

  RETURN NEW;
ELSE
    RAISE WARNING '[SASEO.AUDIT_FUNC] - Other action occurred: %, at %',TG_OP,now();
    RETURN NULL;
END IF;

EXCEPTION
WHEN data_exception THEN
  RAISE WARNING '[SASEO.AUDIT_FUNC] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
  RETURN NULL;
WHEN unique_violation THEN
  RAISE WARNING '[SASEO.AUDIT_FUNC] - UDF ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
  RETURN NULL;
WHEN OTHERS THEN
  RAISE WARNING '[SASEO.AUDIT_FUNC] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %',SQLSTATE,SQLERRM;
  RETURN NULL;
END;
$body$
LANGUAGE plpgsql
SQL
  execute(uuid_extension_sql)
  execute(audit_table_sql)
  execute(whodunnit_sql)
  execute(audit_sql)
end