Monitoritzar canvis en les taules PostgreSQL de OpenERP
Contingut
Introducció
Imaginem que tinc una aplicació de comerç electrònic. Hi ha un formulari per entrar les dades, i la gent paga per Paypal. Vull registrar el nou client a dins la base de dades, vull generar una factura, vull generar un pagament, els assentaments corresponents,...
Quan es clica el botó gravar nou client, què implica a nivell de base de dades? És només un nou registre en la taula de clients, o té d'altres implicacions en altres taules.
El que es busca és un mètode genèric per fer una alta de client manual en OpenERP, i poder estudiar a posteriori quins canvis s'han produït, per poder-los implementar manualment en d'altres processos, per exemple en PHP.
Procediment
Segueixo l'enllaç, i ha funcionat perfectament en una nova instal.lació de PostgreSQL 9.1.
El codi que es proposa és: (es copia del post)
CREATE SCHEMA actividad;
create or replace function creartablaactividad(tabla TEXT) returns void as
$$
DECLARE
newtable text;
tabla text;
txtquery text;
begin
tabla:=$1;
select tabla || '_actividad' into newtable;
if not exists(select * from information_schema.tables where table_name = newtable and table_schema = 'actividad') then
select 'create table actividad.' ||newtable || ' as select text(''1'') as usuario, current_timestamp as tspam, text(''I'') as operacion, * from ' || tabla ||' limit 1;' into txtquery;
EXECUTE txtquery;
select 'delete from actividad.' ||newtable || ';' into txtquery;
EXECUTE txtquery;
end if;
end;
$$
language 'plpgsql';
CREATE OR REPLACE FUNCTION process_audit()
RETURNS trigger AS
$BODY$
DECLARE
newtable text;
col information_schema.columns %ROWTYPE;
txtquery text;
line_old TEXT;
tmpquery text;
i int;
columns_old text[];
BEGIN
select TG_RELNAME || '_actividad' into newtable;
PERFORM creartablaactividad(TG_RELNAME);
IF (TG_OP = 'DELETE') THEN
line_old := TRIM( substr(OLD::text,2,(select length(OLD::text)-2)) );
columns_old := STRING_TO_ARRAY( line_old, ',' );
i := 0;
tmpquery := '''' || array_to_string(columns_old, ''',''') || '''';
tmpquery := replace(tmpquery,','''',',',NULL,');
SELECT 'INSERT INTO actividad.' || newtable ||' SELECT user, now (), ''D'',' || replace(tmpquery, ',''''',',NULL') into txtquery;
EXECUTE txtquery;
RETURN OLD;
ELSIF (TG_OP = 'UPDATE') THEN
line_old := TRIM( substr(OLD::text,2,(select length(OLD::text)-2)) );
columns_old := STRING_TO_ARRAY( line_old, ',' );
i := 0;
tmpquery := '''' || array_to_string(columns_old, ''',''') || '''';
tmpquery := replace(tmpquery,','''',',',NULL,');
tmpquery := replace(tmpquery,','''',',',NULL,');
SELECT 'INSERT INTO actividad.' || newtable ||' SELECT user, now (), ''ANT'',' || replace(tmpquery, ',''''',',NULL') into txtquery;
EXECUTE txtquery;
line_old := TRIM( substr(NEW::text,2,(select length(NEW::text)-2)) );
columns_old := STRING_TO_ARRAY( line_old, ',' );
i := 0;
tmpquery := '''' || array_to_string(columns_old, ''',''') || '''';
tmpquery := replace(tmpquery,','''',',',NULL,');
SELECT 'INSERT INTO actividad.' || newtable ||' SELECT user, now (), ''U'',' || replace(tmpquery, ',''''',',NULL') into txtquery;
EXECUTE txtquery;
RETURN NEW;
ELSIF (TG_OP = 'INSERT') THEN
line_old := TRIM( substr(NEW::text,2,(select length(NEW::text)-2)) );
columns_old := STRING_TO_ARRAY( line_old, ',' );
i := 0;
tmpquery := '''' || array_to_string(columns_old, ''',''') || '''';
tmpquery := replace(tmpquery,','''',',',NULL,');
SELECT 'INSERT INTO actividad.' || newtable ||' SELECT user, now (), ''I'',' || replace(tmpquery, ',''''',',NULL') into txtquery;
EXECUTE txtquery;
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
CREATE TRIGGER prueba_audit
AFTER INSERT OR UPDATE OR DELETE ON prueba
FOR EACH ROW EXECUTE PROCEDURE process_audit();
Aquest codi funciona per a la taula prueba. Quan faig una select, un insert, un update o un delete es crea la taula actividad.prueba_actividad, que conté els canvis generats (en el cas del update es creen dues files per emmagatzemar el valor antic)
select * from actividad.prueba_actividad ;
En el cas de què tingui 100 taules (prueba1 ... prueba100) s'han de crear 100 triggers, i quan s'utilitzi qualsevol d'aquestes taules es crearan les corresponents taules d'activitat: actividad.prueba1_actividad ... actividad.prueba100_actividad.
Per sort tenim el timestamp. Per tant, el que es podria fer per veure de manera ràpida tota la informació, es crear una pàgina PHP que volqui el contingut de totes les taules d'activitat major que un cert timestamp. Creo un nou client de OpenERP, i executo el script PHP, i ja es pot estudiar de forma entenedora què ha passat.
Part PHP
tinc dues taules, empleat i empleat2, i les taules de monitorització són actividad.empleat_actividad i actividad.empleat2_actividad, creades dinàmicament tal com s'acaba d'explicar.
En el codi hem de posar un array amb totes les taules de la bd. La manera de procedir és gravar un registre a OpenERP (per exemple fer una factura), i fer una consulta de veure els canvis que hi ha hagut en totes les taules en els darrers, posem per cas, 10 segons. Finalment,
<?php
header("Content-Type: text/html; charset=utf-8");
//postgresql connection variables
$pg_user = 'profe'; //username
$pg_pass = 'profe'; //user password
$pg_host = 'localhost'; //host name or server ip address
$pg_db = 'empleat'; //database name
$conn_string = "host=$pg_host port=5432 dbname=$pg_db user=$pg_user password=$pg_pass";
$conn = pg_connect($conn_string) or die('connection failed');
if (!$conn) {
echo "Hi ha hagut un error en la connexió.\n";
exit;
}
?>
<html>
<head>
<title>Canvis a la base de dades PostgreSQL</title>
</head>
</title>
<body>
<?php
$taules = array("empleat", "empleat2");
echo "<h1>Canvis a la BD</h1>";
foreach ($taules as $value) {
echo "<h2>Taula $value</h2>";
$query = "select * from actividad.".$value."_actividad where tspam > (timestamp 'now()'-interval '10 second') order by tspam";
//echo $query."<br />";
$result = pg_query($conn, $query);
if (!$result) {
echo "Hi ha hagut un error en fer la query\n";
exit;
}
echo '<table>';
echo '<tr>';
$i = pg_num_fields($result);
for ($j = 0; $j < $i; $j++) {
$fieldname = pg_field_name($result, $j);
echo "<td>$fieldname</td>";
}
echo '</tr>';
while($row=pg_fetch_assoc($result)) echo '<tr><td>'.join('</td><td>',$row).'</td></tr>';
echo '</table>';
}
pg_close($conn); //de fet, no cal
?>
</body>
</html>
creat per Joan Quintana Compte, gener 2013
