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@postgresql.org) Para cambiar tu suscripci�n: http://www.postgresql.org/mailpref/pgsql-es-ayuda
