Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- <?php
- class m150704_234851_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 USING GIN (row_data jsonb_path_ops)
- 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);
- }
- }
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement