Open-ILS/src/sql/Pg/upgrade/XXXX.auditor_boost.sql

   1 -- These three functions are for capturing, getting, and clearing user and workstation information
   2
   3 -- Set the User AND workstation in one call. Tis faster. And less calls.
   4 -- First argument is user, second is workstation
   5 CREATE OR REPLACE FUNCTION auditor.set_audit_info(INT, INT) RETURNS VOID AS $$
   6     $_SHARED{"eg_audit_user"} = $_[0];
   7     $_SHARED{"eg_audit_ws"} = $_[1];
   8 $$ LANGUAGE plperl;
   9
  10 -- Get the User AND workstation in one call. Less calls, useful for joins ;)
  11 CREATE OR REPLACE FUNCTION auditor.get_audit_info() RETURNS TABLE (eg_user INT, eg_ws INT) AS $$
  12     return [{eg_user => $_SHARED{"eg_audit_user"}, eg_ws => $_SHARED{"eg_audit_ws"}}];
  13 $$ LANGUAGE plperl;
  14
  15 -- Clear the audit info, for whatever reason
  16 CREATE OR REPLACE FUNCTION auditor.clear_audit_info() RETURNS VOID AS $$
  17     delete($_SHARED{"eg_audit_user"});
  18     delete($_SHARED{"eg_audit_ws"});
  19 $$ LANGUAGE plperl;
  20
  21 CREATE FUNCTION auditor.create_auditor_history ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
  22 BEGIN
  23     EXECUTE $$
  24         CREATE TABLE auditor.$$ || sch || $$_$$ || tbl || $$_history (
  25             audit_id    BIGINT                          PRIMARY KEY,
  26             audit_time  TIMESTAMP WITH TIME ZONE        NOT NULL,
  27             audit_action        TEXT                            NOT NULL,
  28             audit_user  INT,
  29             audit_ws    INT,
  30             LIKE $$ || sch || $$.$$ || tbl || $$
  31         );
  32     $$;
  33         RETURN TRUE;
  34 END;
  35 $creator$ LANGUAGE 'plpgsql';
  36
  37 CREATE OR REPLACE FUNCTION auditor.create_auditor_func    ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
  38 DECLARE
  39     column_list TEXT[];
  40 BEGIN
  41     SELECT INTO column_list array_agg(a.attname)
  42         FROM pg_catalog.pg_attribute a
  43             JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
  44             JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  45         WHERE relkind = 'r' AND n.nspname = sch AND c.relname = tbl AND a.attnum > 0 AND NOT a.attisdropped;
  46
  47     EXECUTE $$
  48         CREATE OR REPLACE FUNCTION auditor.audit_$$ || sch || $$_$$ || tbl || $$_func ()
  49         RETURNS TRIGGER AS $func$
  50         BEGIN
  51             INSERT INTO auditor.$$ || sch || $$_$$ || tbl || $$_history ( audit_id, audit_time, audit_action, audit_user, audit_ws, $$
  52             || array_to_string(column_list, ', ') || $$ )
  53                 SELECT  nextval('auditor.$$ || sch || $$_$$ || tbl || $$_pkey_seq'),
  54                     now(),
  55                     SUBSTR(TG_OP,1,1),
  56                     eg_user,
  57                     eg_ws,
  58                     OLD.$$ || array_to_string(column_list, ', OLD.') || $$
  59                 FROM auditor.get_audit_info();
  60             RETURN NULL;
  61         END;
  62         $func$ LANGUAGE 'plpgsql';
  63     $$;
  64     RETURN TRUE;
  65 END;
  66 $creator$ LANGUAGE 'plpgsql';
  67
  68 CREATE OR REPLACE FUNCTION auditor.create_auditor_lifecycle     ( sch TEXT, tbl TEXT ) RETURNS BOOL AS $creator$
  69 DECLARE
  70     column_list TEXT[];
  71 BEGIN
  72     SELECT INTO column_list array_agg(a.attname)
  73         FROM pg_catalog.pg_attribute a
  74             JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
  75             JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
  76         WHERE relkind = 'r' AND n.nspname = sch AND c.relname = tbl AND a.attnum > 0 AND NOT a.attisdropped;
  77
  78     EXECUTE $$
  79         CREATE VIEW auditor.$$ || sch || $$_$$ || tbl || $$_lifecycle AS
  80             SELECT -1 AS audit_id,
  81                    now() AS audit_time,
  82                    '-' AS audit_action,
  83                    -1 AS audit_user,
  84                    -1 AS audit_ws,
  85                    $$ || array_to_string(column_list, ', ') || $$
  86               FROM $$ || sch || $$.$$ || tbl || $$
  87                 UNION ALL
  88             SELECT audit_id, audit_time, audit_action, audit_user, audit_ws,
  89             $$ || array_to_string(column_list, ', ') || $$
  90               FROM auditor.$$ || sch || $$_$$ || tbl || $$_history;
  91     $$;
  92     RETURN TRUE;
  93 END;
  94 $creator$ LANGUAGE 'plpgsql';
  95
  96 -- Corrects all column discrepencies between audit table and core table:
  97 -- Adds missing columns
  98 -- Removes leftover columns
  99 -- Updates types
 100 -- Also, ensures all core auditor columns exist.
 101 CREATE OR REPLACE FUNCTION auditor.fix_columns() RETURNS VOID AS $BODY$
 102 DECLARE
 103     current_table TEXT = ''; -- Storage for post-loop main table name
 104     current_audit_table TEXT = ''; -- Storage for post-loop audit table name
 105     query TEXT = ''; -- Storage for built query
 106     cr RECORD; -- column record object
 107     alter_t BOOL = false; -- Has the alter table command been appended yet
 108     auditor_cores TEXT[] = ARRAY[]::TEXT[]; -- Core auditor function list (filled inside of loop)
 109     core_column TEXT; -- The current core column we are adding
 110 BEGIN
 111     FOR cr IN
 112         WITH audit_tables AS ( -- Basic grab of auditor tables. Anything in the auditor namespace, basically. With oids.
 113             SELECT c.oid AS audit_oid, c.relname AS audit_table
 114             FROM pg_catalog.pg_class c
 115             JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 116             WHERE relkind='r' AND nspname = 'auditor'
 117         ),
 118         table_set AS ( -- Union of auditor tables with their "main" tables. With oids.
 119             SELECT a.audit_oid, a.audit_table, c.oid AS main_oid, n.nspname as main_namespace, c.relname as main_table
 120             FROM pg_catalog.pg_class c
 121             JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 122             JOIN audit_tables a ON a.audit_table = n.nspname || '_' || c.relname || '_history'
 123             WHERE relkind = 'r'
 124         ),
 125         column_lists AS ( -- All columns associated with the auditor or main table, grouped by the main table's oid.
 126             SELECT DISTINCT ON (main_oid, attname) t.main_oid, a.attname
 127             FROM table_set t
 128             JOIN pg_catalog.pg_attribute a ON a.attrelid IN (t.main_oid, t.audit_oid)
 129             WHERE attnum > 0 AND NOT attisdropped
 130         ),
 131         column_defs AS ( -- The motherload, every audit table and main table plus column names and defs.
 132             SELECT audit_table,
 133                    main_namespace,
 134                    main_table,
 135                    a.attname AS main_column, -- These two will be null for columns that have since been deleted, or for auditor core columns
 136                    pg_catalog.format_type(a.atttypid, a.atttypmod) AS main_column_def,
 137                    b.attname AS audit_column, -- These two will be null for columns that have since been added
 138                    pg_catalog.format_type(b.atttypid, b.atttypmod) AS audit_column_def
 139             FROM table_set t
 140             JOIN column_lists c USING (main_oid)
 141             LEFT JOIN pg_catalog.pg_attribute a ON a.attname = c.attname AND a.attrelid = t.main_oid AND a.attnum > 0 AND NOT a.attisdropped
 142             LEFT JOIN pg_catalog.pg_attribute b ON b.attname = c.attname AND b.attrelid = t.audit_oid AND b.attnum > 0 AND NOT b.attisdropped
 143         )
 144         -- Nice sorted output from the above
 145         SELECT * FROM column_defs WHERE main_column_def IS DISTINCT FROM audit_column_def ORDER BY main_namespace, main_table, main_column, audit_column
 146     LOOP
 147         IF current_table <> (cr.main_namespace || '.' || cr.main_table) THEN -- New table?
 148             FOR core_column IN SELECT DISTINCT unnest(auditor_cores) LOOP -- Update missing core auditor columns
 149                 IF NOT alter_t THEN -- Add ALTER TABLE if we haven't already
 150                     query:=query || $$ALTER TABLE auditor.$$ || current_audit_table;
 151                     alter_t:=TRUE;
 152                 ELSE
 153                     query:=query || $$,$$;
 154                 END IF;
 155                 -- Bit of a sneaky bit here. Create audit_id as a bigserial so it gets automatic values and doesn't complain about nulls when becoming a PRIMARY KEY.
 156                 query:=query || $$ ADD COLUMN $$ || CASE WHEN core_column = 'audit_id bigint' THEN $$audit_id bigserial PRIMARY KEY$$ ELSE core_column END;
 157             END LOOP;
 158             IF alter_t THEN -- Open alter table = needs a semicolon
 159                 query:=query || $$; $$;
 160                 alter_t:=FALSE;
 161                 IF 'audit_id bigint' = ANY(auditor_cores) THEN -- We added a primary key...
 162                     -- Fun! Drop the default on audit_id, drop the auto-created sequence, create a new one, and set the current value
 163                     -- For added fun, we have to execute in chunks due to the parser checking setval/currval arguments at parse time.
 164                     EXECUTE query;
 165                     EXECUTE $$ALTER TABLE auditor.$$ || current_audit_table || $$ ALTER COLUMN audit_id DROP DEFAULT; $$ ||
 166                         $$CREATE SEQUENCE auditor.$$ || current_audit_table || $$_pkey_seq;$$;
 167                     EXECUTE $$SELECT setval('auditor.$$ || current_audit_table || $$_pkey_seq', currval('auditor.$$ || current_audit_table || $$_audit_id_seq')); $$ ||
 168                         $$DROP SEQUENCE auditor.$$ || current_audit_table || $$_audit_id_seq;$$;
 169                     query:='';
 170                 END IF;
 171             END IF;
 172             -- New table means we reset the list of needed auditor core columns
 173             auditor_cores = ARRAY['audit_id bigint', 'audit_time timestamp with time zone', 'audit_action text', 'audit_user integer', 'audit_ws integer'];
 174             -- And store some values for use later, because we can't rely on cr in all places.
 175             current_table:=cr.main_namespace || '.' || cr.main_table;
 176             current_audit_table:=cr.audit_table;
 177         END IF;
 178         IF cr.main_column IS NULL AND cr.audit_column LIKE 'audit_%' THEN -- Core auditor column?
 179             -- Remove core from list of cores
 180             SELECT INTO auditor_cores array_agg(core) FROM unnest(auditor_cores) AS core WHERE core != (cr.audit_column || ' ' || cr.audit_column_def);
 181         ELSIF cr.main_column IS NULL THEN -- Main column doesn't exist, and it isn't an auditor column. Needs dropping from the auditor.
 182             IF NOT alter_t THEN
 183                 query:=query || $$ALTER TABLE auditor.$$ || current_audit_table;
 184                 alter_t:=TRUE;
 185             ELSE
 186                 query:=query || $$,$$;
 187             END IF;
 188             query:=query || $$ DROP COLUMN $$ || cr.audit_column;
 189         ELSIF cr.audit_column IS NULL AND cr.main_column IS NOT NULL THEN -- New column auditor doesn't have. Add it.
 190             IF NOT alter_t THEN
 191                 query:=query || $$ALTER TABLE auditor.$$ || current_audit_table;
 192                 alter_t:=TRUE;
 193             ELSE
 194                 query:=query || $$,$$;
 195             END IF;
 196             query:=query || $$ ADD COLUMN $$ || cr.main_column || $$ $$ || cr.main_column_def;
 197         ELSIF cr.main_column IS NOT NULL AND cr.audit_column IS NOT NULL THEN -- Both sides have this column, but types differ. Fix that.
 198             IF NOT alter_t THEN
 199                 query:=query || $$ALTER TABLE auditor.$$ || current_audit_table;
 200                 alter_t:=TRUE;
 201             ELSE
 202                 query:=query || $$,$$;
 203             END IF;
 204             query:=query || $$ ALTER COLUMN $$ || cr.audit_column || $$ TYPE $$ || cr.main_column_def;
 205         END IF;
 206     END LOOP;
 207     FOR core_column IN SELECT DISTINCT unnest(auditor_cores) LOOP -- Repeat this outside of the loop to catch the last table
 208         IF NOT alter_t THEN
 209             query:=query || $$ALTER TABLE auditor.$$ || current_audit_table;
 210             alter_t:=TRUE;
 211         ELSE
 212             query:=query || $$,$$;
 213         END IF;
 214         -- Bit of a sneaky bit here. Create audit_id as a bigserial so it gets automatic values and doesn't complain about nulls when becoming a PRIMARY KEY.
 215         query:=query || $$ ADD COLUMN $$ || CASE WHEN core_column = 'audit_id bigint' THEN $$audit_id bigserial PRIMARY KEY$$ ELSE core_column END;
 216     END LOOP;
 217     IF alter_t THEN -- Open alter table = needs a semicolon
 218         query:=query || $$;$$;
 219         IF 'audit_id bigint' = ANY(auditor_cores) THEN -- We added a primary key...
 220             -- Fun! Drop the default on audit_id, drop the auto-created sequence, create a new one, and set the current value
 221             -- For added fun, we have to execute in chunks due to the parser checking setval/currval arguments at parse time.
 222             EXECUTE query;
 223             EXECUTE $$ALTER TABLE auditor.$$ || current_audit_table || $$ ALTER COLUMN audit_id DROP DEFAULT; $$ ||
 224                 $$CREATE SEQUENCE auditor.$$ || current_audit_table || $$_pkey_seq;$$;
 225             EXECUTE $$SELECT setval('auditor.$$ || current_audit_table || $$_pkey_seq', currval('auditor.$$ || current_audit_table || $$_audit_id_seq')); $$ ||
 226                 $$DROP SEQUENCE auditor.$$ || current_audit_table || $$_audit_id_seq;$$;
 227             query:='';
 228         END IF;
 229     END IF;
 230     EXECUTE query;
 231 END;
 232 $BODY$ LANGUAGE plpgsql;
 233
 234 -- Update it all routine
 235 CREATE OR REPLACE FUNCTION auditor.update_auditors() RETURNS boolean AS $BODY$
 236 DECLARE
 237     auditor_name TEXT;
 238     table_schema TEXT;
 239     table_name TEXT;
 240 BEGIN
 241     -- Drop Lifecycle view(s) before potential column changes
 242     FOR auditor_name IN
 243         SELECT c.relname
 244             FROM pg_catalog.pg_class c
 245                 JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 246             WHERE relkind = 'v' AND n.nspname = 'auditor' LOOP
 247         EXECUTE $$ DROP VIEW auditor.$$ || auditor_name || $$;$$;
 248     END LOOP;
 249     -- Fix all column discrepencies
 250     PERFORM auditor.fix_columns();
 251     -- Re-create trigger functions and lifecycle views
 252     FOR table_schema, table_name IN
 253         WITH audit_tables AS (
 254             SELECT c.oid AS audit_oid, c.relname AS audit_table
 255             FROM pg_catalog.pg_class c
 256             JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 257             WHERE relkind='r' AND nspname = 'auditor'
 258         ),
 259         table_set AS (
 260             SELECT a.audit_oid, a.audit_table, c.oid AS main_oid, n.nspname as main_namespace, c.relname as main_table
 261             FROM pg_catalog.pg_class c
 262             JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
 263             JOIN audit_tables a ON a.audit_table = n.nspname || '_' || c.relname || '_history'
 264             WHERE relkind = 'r'
 265         )
 266         SELECT main_namespace, main_table FROM table_set LOOP
 267
 268         PERFORM auditor.create_auditor_func(table_schema, table_name);
 269         PERFORM auditor.create_auditor_lifecycle(table_schema, table_name);
 270     END LOOP;
 271     RETURN TRUE;
 272 END;
 273 $BODY$ LANGUAGE plpgsql;
 274
 275 -- Go ahead and update them all now
 276 SELECT auditor.update_auditors();