m150719_043333_audit.php

<?php

class m150719_043333_audit extends \yii\db\Migration
{
    public function safeUp()
    {
        $query = <<<SQL
CREATE SCHEMA audits
SQL;
        $this->execute($query);
        $query = <<<SQL
CREATE TYPE audits.action_type AS ENUM ('INSERT', 'SELECT', 'UPDATE', 'DELETE', 'TRUNCATE')
SQL;
        $this->execute($query);
        $query = <<<SQL
CREATE TABLE "audits"."changesets" (
	"id" serial NOT NULL PRIMARY KEY,
	"transaction_id" bigint,
	"user_id" integer,
	"session_id" text,
	"request_date" timestamp(0) with time zone NOT NULL,
	"request_url" text,
	"request_addr" inet
)
SQL;
        $this->execute($query);
        $query = <<<SQL
CREATE INDEX ON audits.changesets (transaction_id)
SQL;
        $this->execute($query);
        $query = <<<SQL
CREATE INDEX ON audits.changesets (User_id)
SQL;
        $this->execute($query);
        $query = <<<SQL
CREATE INDEX ON audits.changesets (session_id)
SQL;
        $this->execute($query);
        $query = <<<SQL
CREATE INDEX ON audits.changesets (request_url)
SQL;
        $this->execute($query);
        $query = <<<SQL
CREATE INDEX ON audits.changesets (request_addr)
SQL;
        $this->execute($query);
        $query = <<<SQL
CREATE TABLE "audits"."logged_actions" (
	"action_id" bigserial NOT NULL PRIMARY KEY,
	"schema_name" text NOT NULL,
	"table_name" text NOT NULL,
	"relation_id" oid NOT NULL,
	"transaction_date" timestamp(0) with time zone NOT NULL,
	"statement_date" timestamp(0) with time zone NOT NULL,
	"action_date" timestamp(0) with time zone NOT NULL,
	"transaction_id" bigint,
	"session_user_name" text,
	"application_name" text,
	"client_addr" inet,
	"client_port" integer,
	"query" text,
	"action_type" audits.action_type NOT NULL,
	"row_data" jsonb,
	"changed_fields" jsonb,
	"statement_only" boolean NOT NULL DEFAULT FALSE,
	"key_type" char(1) NOT NULL CHECK (key_type IN ('c', 't', 'a')),
	"changeset_id" integer REFERENCES audits.changesets (id) ON UPDATE CASCADE ON DELETE CASCADE
)
SQL;
        $this->execute($query);
        $query = <<<SQL
CREATE INDEX ON audits.logged_actions (schema_name, table_name)
SQL;
        $this->execute($query);
        $query = <<<SQL
CREATE INDEX ON audits.logged_actions (relation_id)
SQL;
        $this->execute($query);
        $query = <<<SQL
CREATE INDEX ON audits.logged_actions (statement_date)
SQL;
        $this->execute($query);
        $query = <<<SQL
CREATE INDEX ON audits.logged_actions (action_type)
SQL;
        $this->execute($query);
        $query = <<<SQL
CREATE INDEX ON audits.logged_actions (key_type)
SQL;
        $this->execute($query);
        $query = <<<SQL
CREATE INDEX ON audits.logged_actions (statement_only)
SQL;
        $this->execute($query);
        $query = <<<SQL
CREATE INDEX ON audits.logged_actions USING GIN (row_data jsonb_path_ops)
SQL;
        $this->execute($query);
        $query = <<<SQL
CREATE INDEX ON audits.logged_actions (changeset_id)
SQL;
        $this->execute($query);
        $query = <<<SQL
CREATE OR REPLACE FUNCTION audits.json_object_delete_keys(_json json, VARIADIC _keys TEXT[]) RETURNS json AS \$BODY$
SELECT json_object_agg(key, value) AS json
FROM json_each(_json)
WHERE key != ALL (_keys)
\$BODY$
LANGUAGE sql
IMMUTABLE STRICT
SQL;
        $this->execute($query);
        $query = <<<SQL
CREATE OR REPLACE FUNCTION audits.json_object_delete_values(_json json, _values json) RETURNS json AS \$BODY$
SELECT json_object_agg(a.key, a.value) AS json
FROM json_each_text(_json) a
JOIN json_each_text(_values) b ON a.key = b.key AND a.value IS DISTINCT FROM b.value
\$BODY$
LANGUAGE sql
IMMUTABLE STRICT
SQL;
        $this->execute($query);
        $query = <<<SQL
CREATE OR REPLACE FUNCTION audits.log_action() RETURNS trigger AS \$BODY$
DECLARE
    audit_row audits.logged_actions;
    include_values boolean;
    log_diffs boolean;
    h_old jsonb;
    h_new jsonb;
    excluded_cols text[] = ARRAY[]::text[];
BEGIN
    IF TG_WHEN <> 'AFTER' THEN
        RAISE EXCEPTION 'audits.log_action() may only run as an AFTER trigger';
    END IF;

    audit_row = ROW(
        nextval('audits.logged_actions_action_id_seq') -- action_id
        ,TG_TABLE_SCHEMA::text  -- schema_name
        ,TG_TABLE_NAME::text    -- table_name
        ,TG_RELID               -- relation OID for much quicker searches
        ,current_timestamp      -- transaction_date
        ,statement_timestamp()  -- statement_date
        ,clock_timestamp()      -- action_date
        ,txid_current()         -- transaction_id
        ,NULL::text             -- session_user_name
        ,NULL::text             -- application_name
        ,NULL::inet             -- client_addr
        ,NULL::integer          -- client_port
        ,NULL::text             -- top-level query or queries (if multistatement) from client
        ,TG_OP                  -- action_type
        ,NULL::jsonb            -- row_data
        ,NULL::jsonb            -- changed_fields
        ,FALSE                  -- statement_only
        ,NULL                   -- key_type
        ,NULL
    );

    IF TG_ARGV[0]::boolean IS NOT DISTINCT FROM TRUE THEN
        audit_row.query = current_query();
    END IF;

    IF TG_ARGV[1] IS NOT NULL THEN
        excluded_cols = TG_ARGV[1]::text[];
    END IF;

    IF TG_ARGV[2]::boolean IS NOT DISTINCT FROM TRUE THEN
        audit_row.session_user_name = session_user::text;
        audit_row.application_name = current_setting('application_name');
        audit_row.client_addr = inet_client_addr();
        audit_row.client_port = inet_client_port();
    END IF;

    BEGIN
        SELECT NULLIF(current_setting('audit.changeset_id'), '') INTO audit_row.changeset_id;
        EXCEPTION
            WHEN undefined_object THEN audit_row.changeset_id = NULL;
            WHEN data_exception THEN audit_row.changeset_id = NULL;
    END;

    audit_row.key_type = CASE
        WHEN audit_row.changeset_id IS NOT NULL THEN 'c'
        WHEN audit_row.key_type IS NOT NULL THEN 't'
        ELSE 'a'
    END;

    IF (TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW') THEN
        audit_row.row_data = audits.json_object_delete_keys(row_to_json(OLD), VARIADIC excluded_cols)::jsonb;
        audit_row.changed_fields = audits.json_object_delete_keys(audits.json_object_delete_values(row_to_json(NEW), row_to_json(OLD)), VARIADIC excluded_cols)::jsonb;
        IF audit_row.changed_fields IS NULL THEN
            -- All changed fields are ignored. Skip this update.
            RETURN NULL;
        END IF;
    ELSIF (TG_OP = 'DELETE' AND TG_LEVEL = 'ROW') THEN
        audit_row.row_data = audits.json_object_delete_keys(row_to_json(OLD), VARIADIC excluded_cols)::jsonb;
    ELSIF (TG_OP = 'INSERT' AND TG_LEVEL = 'ROW') THEN
        audit_row.row_data = audits.json_object_delete_keys(row_to_json(NEW), VARIADIC excluded_cols)::jsonb;
    ELSIF (TG_LEVEL = 'STATEMENT' AND TG_OP IN ('INSERT','UPDATE','DELETE','TRUNCATE')) THEN
        audit_row.statement_only = TRUE;
    ELSE
        RAISE EXCEPTION '[audits.log_action] - Trigger func added as trigger for unhandled case: %, %', TG_OP, TG_LEVEL;
        RETURN NULL;
    END IF;
    INSERT INTO audits.logged_actions VALUES (audit_row.*);
    RETURN NULL;
END;
\$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
SQL;
        $this->execute($query);
    }

    public function safeDown()
    {
        $query = <<<SQL
DROP FUNCTION audits.json_object_delete_keys(json, text[])
SQL;
        $this->execute($query);
        $query = <<<SQL
DROP FUNCTION audits.json_object_delete_values(json, json)
SQL;
        $this->execute($query);
        $query = <<<SQL
DROP FUNCTION audits.log_action()
SQL;
        $this->execute($query);
        $query = <<<SQL
DROP TABLE "audits"."logged_actions"
SQL;
        $this->execute($query);
        $query = <<<SQL
DROP TABLE "audits"."changesets"
SQL;
        $this->execute($query);
    }
}