class PgAuditLog::Function

Constants

DISABLED_USER

Public Class Methods

install() click to toggle source
# File lib/pg_audit_log/function.rb, line 46
      def install
        execute <<-SQL
        CREATE OR REPLACE PROCEDURAL LANGUAGE plpgsql;
        CREATE OR REPLACE FUNCTION #{name}() RETURNS trigger
        LANGUAGE plpgsql
        AS $_$
            DECLARE
              col information_schema.columns %ROWTYPE;
              new_value text;
              old_value text;
              primary_key_column varchar;
              primary_key_value varchar;
              user_identifier integer;
              unique_name varchar;
              column_name varchar;
            BEGIN
              user_identifier := #{pg_audit_log_old_style_user_id ? %q(current_setting('audit.user_id')) : 'pg_temp.pg_audit_log_user_identifier()'};
              IF user_identifier = #{DISABLED_USER} THEN
                RETURN NULL;
              END IF;
              unique_name := #{pg_audit_log_old_style_user_id ? %q(current_setting('audit.user_unique_name')) : 'pg_temp.pg_audit_log_user_unique_name()'};
              primary_key_column := NULL;
              EXECUTE 'SELECT pg_attribute.attname
                       FROM pg_index, pg_class, pg_attribute
                       WHERE pg_class.oid = $1::regclass
                       AND indrelid = pg_class.oid
                       AND pg_attribute.attrelid = pg_class.oid
                       AND pg_attribute.attnum = any(pg_index.indkey)
                       AND indisprimary'
              INTO primary_key_column USING TG_RELNAME;
              primary_key_value := NULL;

              FOR col IN SELECT * FROM information_schema.columns WHERE table_name = TG_RELNAME LOOP
                new_value := NULL;
                old_value := NULL;
                column_name := col.column_name;
                IF TG_RELNAME = '#{users_table_name}' AND column_name = '#{users_access_column}' THEN
                  NULL;
                ELSE
                  IF TG_OP = 'INSERT' OR TG_OP = 'UPDATE' THEN
                    EXECUTE 'SELECT CAST($1 . '|| column_name ||' AS TEXT)' INTO new_value USING NEW;
                    IF primary_key_value IS NULL AND primary_key_column IS NOT NULL THEN
                      EXECUTE 'SELECT CAST($1 . '|| primary_key_column ||' AS VARCHAR)' INTO primary_key_value USING NEW;
                    END IF;
                  END IF;
                  IF TG_OP = 'DELETE' OR TG_OP = 'UPDATE' THEN
                    EXECUTE 'SELECT CAST($1 . '|| column_name ||' AS TEXT)' INTO old_value USING OLD;
                    IF primary_key_value IS NULL AND primary_key_column IS NOT NULL THEN
                      EXECUTE 'SELECT CAST($1 . '|| primary_key_column ||' AS VARCHAR)' INTO primary_key_value USING OLD;
                    END IF;
                  END IF;

                  IF TG_OP != 'UPDATE' OR new_value != old_value OR (TG_OP = 'UPDATE' AND ( (new_value IS NULL AND old_value IS NOT NULL) OR (new_value IS NOT NULL AND old_value IS NULL))) THEN
                    INSERT INTO audit_log("operation",
                                          "table_name",
                                          "primary_key",
                                          "field_name",
                                          "field_value_old",
                                          "field_value_new",
                                          "user_id",
                                          "user_unique_name",
                                          "occurred_at"
                                         )
                    VALUES(TG_OP,
                          TG_RELNAME,
                          primary_key_value,
                          column_name,
                          old_value,
                          new_value,
                          user_identifier,
                          unique_name,
                          current_timestamp);
                  END IF;
                END IF;
              END LOOP;
              RETURN NULL;
            END
            $_$;
        SQL
      end
installed?() click to toggle source
# File lib/pg_audit_log/function.rb, line 131
      def installed?
        connection.select_values(<<-SQL).first.to_i == 1
          SELECT COUNT(pg_proc.proname)
          FROM pg_proc
          WHERE pg_proc.proname = '#{name}'
        SQL
      end
name() click to toggle source
# File lib/pg_audit_log/function.rb, line 6
def name
  'audit_changes'
end
pg_audit_log_old_style_user_id() click to toggle source
# File lib/pg_audit_log/function.rb, line 26
def pg_audit_log_old_style_user_id
  defined?(Rails) && Rails.configuration.pg_audit_log_old_style_user_id rescue false
end
uninstall() click to toggle source
# File lib/pg_audit_log/function.rb, line 127
def uninstall
  execute "DROP FUNCTION IF EXISTS #{name}() CASCADE"
end
user_id_field() click to toggle source
# File lib/pg_audit_log/function.rb, line 14
def user_id_field
  'user_id'
end
user_identifier_temporary_function(user_id) click to toggle source
# File lib/pg_audit_log/function.rb, line 30
def user_identifier_temporary_function(user_id)
  if pg_audit_log_old_style_user_id
    "SET audit.user_id = #{user_id || -1};"
  else
    "CREATE OR REPLACE FUNCTION pg_temp.pg_audit_log_user_identifier() RETURNS integer AS 'SELECT #{user_id}' LANGUAGE SQL STABLE;"
  end
end
user_name_field() click to toggle source
# File lib/pg_audit_log/function.rb, line 18
def user_name_field
  'user_unique_name'
end
user_unique_name_temporary_function(username) click to toggle source
# File lib/pg_audit_log/function.rb, line 38
def user_unique_name_temporary_function(username)
  if pg_audit_log_old_style_user_id
    "SET audit.user_unique_name = '#{PGconn.escape_bytea(username)}';"
  else
    "CREATE OR REPLACE FUNCTION pg_temp.pg_audit_log_user_unique_name() RETURNS varchar AS $_$ SELECT '#{PGconn.escape_bytea(username)}'::varchar $_$ LANGUAGE SQL STABLE;"
  end
end
users_access_column() click to toggle source
# File lib/pg_audit_log/function.rb, line 22
def users_access_column
  'last_accessed_at'
end
users_table_name() click to toggle source
# File lib/pg_audit_log/function.rb, line 10
def users_table_name
  'users'
end