Re: Auditoría genérico de tablas usando hstore

El vie, 13-09-2013 a las 01:15 -0430, Jairo Graterón escribió:
> Saludos,
>
>
> Me gustaria saber que opinion tienen sobre usar hstore para llevar una
> auditoria de las tablas de la base de datos de la compañia de trabajo.
>

Buenas Jairo,

Hace unas semanas pude ver en el blog de Dimitri Fontaine, una solución
muy parecida a la que comentas para implementar auditorías usando hstore
( http://tapoueh.org/blog/2013/08/27-auditing-changes-with-hstore.html )
me resulta una solución creativa, al poder usar el operador - de hstore.

> --CREATE EXTENSION hstore;
>
> DROP TABLE IF EXISTS auditoria;
>
> create table auditoria
> (
> fechahora timestamp not null,
> usuario text not null,
> operacion text not null,
> nombre_esquema text not null,
> nombre_tabla text not null,
> antes hstore,
> despues hstore
> );
>
> CREATE OR REPLACE FUNCTION f_auditoria()
> RETURNS trigger AS
> $$
> begin
> IF TG_OP = 'INSERT' THEN
> INSERT INTO auditoria(fechahora, usuario, operacion,
> nombre_esquema, nombre_tabla, antes, despues)
> SELECT CURRENT_TIMESTAMP, user, TG_OP,
> TG_TABLE_SCHEMA,TG_TABLE_NAME,NULL, hstore(new);
> RETURN NEW;
> ELSIF TG_OP = 'UPDATE' THEN
> INSERT INTO auditoria(fechahora, usuario, operacion,
> nombre_esquema, nombre_tabla, antes, despues)
> SELECT CURRENT_TIMESTAMP, user, TG_OP,
> TG_TABLE_SCHEMA,TG_TABLE_NAME,hstore(old), hstore(new);
> RETURN NEW;
> ELSIF TG_OP = 'DELETE' THEN
> INSERT INTO auditoria(fechahora, usuario, operacion,
> nombre_esquema, nombre_tabla, antes, despues)
> SELECT CURRENT_TIMESTAMP, user, TG_OP,
> TG_TABLE_SCHEMA,TG_TABLE_NAME,hstore(old), NULL;
> RETURN OLD;
> END IF;
> end;
> $$
> LANGUAGE plpgsql VOLATILE
> COST 100;
>
>
> CREATE OR REPLACE FUNCTION f_colocar_auditoria(text)
> RETURNS void AS
> $$
> DECLARE
> tabla ALIAS FOR $1;
> nombretrigger text;
> sql text;
> BEGIN
> nombretrigger = 'trigger_auditoria_' || replace (tabla,'.','_');
> sql = 'CREATE TRIGGER ' || nombretrigger || ' AFTER INSERT OR
> UPDATE OR DELETE ON '|| tabla || ' FOR EACH ROW EXECUTE PROCEDURE
> f_auditoria()';
> EXECUTE sql;
> END;
> $$
> LANGUAGE plpgsql VOLATILE
> COST 100;
>
>
> ------------------------------------------------- ejemplo
> -------------------------------------------
>
> DROP TABLE IF EXISTS producto;
>
> CREATE TABLE producto
> (
> idproducto serial NOT NULL,
> nombre text NOT NULL,
> costo numeric (10,2) NOT NULL,
> stock integer NOT NULL,
> PRIMARY KEY (idproducto)
> );
>
> select f_colocar_auditoria('producto');
>
> DROP TABLE IF EXISTS cliente;
>
> CREATE TABLE cliente
> (
> idcliente serial not null,
> nombre text not null,
> direccion text not null,
> primary key(idcliente)
> );
>
> select f_colocar_auditoria('cliente');
>
> insert into producto (nombre, costo, stock) values ('Monitor', 100.23,
> 10);
> insert into producto (nombre, costo, stock) values ('Teclado', 30, 5);
> insert into producto (nombre, costo, stock) values ('Mouse', 25, 50);
>
> update producto set costo=99 where idproducto=1;
> update producto set stock=30 where idproducto=3;
> update producto set costo=90, stock=8 where idproducto=1;
> delete from producto where idproducto=2;
>
>
>
> insert into cliente (nombre, direccion) values ('Pepe', 'Bogota');
> insert into cliente (nombre, direccion) values ('María', 'Caracas');
> insert into cliente (nombre, direccion) values ('Jose', 'Buenos
> Aires');
>
> update cliente set Nombre='José' where idcliente=3;
> update cliente set direccion='Quito' where idcliente=1;
> delete from cliente where idcliente=3;
>
>
> -- pregunta nro 1, listar la auditoria del producto codigo 1
> select * from auditoria where nombre_tabla = 'producto'
> and (antes -> 'idproducto' = '1' or despues -> 'idproducto' = '1')
> order by fechahora desc;
>
> -- pregunta nro 2, listar la auditoria del producto codigo 2
> select * from auditoria where nombre_tabla = 'producto'
> and (antes -> 'idproducto' = '2' or despues -> 'idproducto' = '2')
> order by fechahora desc;
>
> -- pregunta nro 3 listar la auditoria del cliente codigo 3
> select * from auditoria where nombre_tabla = 'cliente'
> and (antes -> 'idcliente' = '3' or despues -> 'idcliente' = '3') order
> by fechahora desc;
>
> --pregunta nro 4. cuales fueron los campos actualizados en el producto
> nro 1 y cuales eran sus valores antiguos y nuevos
> select fechahora, akeys(antes-despues) as campos, avals(antes-despues)
> as antiguo, avals(despues-antes) as nuevo from auditoria
> where operacion='UPDATE' AND nombre_tabla = 'producto'
> and (antes -> 'idproducto' = '1' or despues -> 'idproducto' = '1')
> order by fechahora desc;
>
>
>
>
>

-
Enviado a la lista de correo pgsql-es-ayuda (pgsql-es-ayuda(at)postgresql(dot)org)
Para cambiar tu suscripcin:
http://www.postgresql.org/mailpref/pgsql-es-ayuda