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
|
# File 'lib/pg_audit_log/entry.rb', line 20
def install
sql = " CREATE SEQUENCE \#{self.table_name}_id_seq\n START WITH 1\n INCREMENT BY 1;\n\n CREATE TABLE \#{self.table_name} (\n id bigint PRIMARY KEY DEFAULT nextval('\#{self.table_name}_id_seq'),\n user_id integer,\n user_unique_name character varying(255),\n operation character varying(255),\n table_name character varying(255),\n field_name character varying(255),\n field_value_new text,\n field_value_old text,\n occurred_at timestamp without time zone,\n primary_key character varying(255)\n );\n\n ALTER SEQUENCE \#{self.table_name}_id_seq OWNED BY \#{self.table_name}.id;\n\n CREATE OR REPLACE FUNCTION audit_log_insert_trigger()\n RETURNS TRIGGER AS $$\n DECLARE\n tablename TEXT;\n insert_sql TEXT;\n create_table_sql TEXT;\n month_start DATE;\n month_end DATE;\n BEGIN\n tablename := '\#{self.table_name}_' || to_char(NEW.occurred_at, 'YYYYMM');\n insert_sql := 'INSERT INTO ' || tablename || ' VALUES($1.*)';\n EXECUTE insert_sql USING NEW;\n RETURN NULL;\n EXCEPTION\n WHEN null_value_not_allowed THEN\n RETURN NULL;\n WHEN undefined_table THEN\n EXECUTE 'SELECT to_char($1, ''YYYY-MM-01'')::DATE' INTO month_start USING NEW.occurred_at;\n EXECUTE 'SELECT ($1 + INTERVAL ''1 MONTH'')' INTO month_end USING month_start;\n create_table_sql := 'CREATE TABLE ' || tablename || ' ( CHECK ( date(occurred_at) >= DATE ''' || month_start || ''' AND date(occurred_at) < DATE ''' ||\n month_end || ''' ) ) INHERITS (\#{self.table_name})';\n EXECUTE create_table_sql;\n EXECUTE 'CREATE INDEX ' || tablename || '_occurred_at ON ' || tablename || ' (date(occurred_at))';\n EXECUTE insert_sql USING NEW;\n RETURN NULL;\n END;\n $$\n LANGUAGE plpgsql;\n\n CREATE TRIGGER insert_audit_log_trigger\n BEFORE INSERT ON audit_log\n FOR EACH ROW EXECUTE PROCEDURE audit_log_insert_trigger();\n SQL\n connection.execute(sql)\nend\n"
|