ALTARM: Windows + Postgres + VB + ASP

De wikijoan
Salta a la navegació Salta a la cerca

Introducció

El projecte ALTARM (Audit Logical Transactions And Recover Mistakes) neix com un exemple didàctic per ensenyar disparadors-triggers amb postgres. La idea és que amb disparadors es pot auditar tota l'activitat que hi ha en una base de dades, i es guarda en taules tota la informació per poder tirar endarrera transaccions, i així desfer canvis i poder tirar la base de dades a un temps pretèrit.

El projecte el vaig desenvolupar el curs 2007-2008 per ensenyar Postgres i pgpsql (programació de bases de dades). La base de dades sobre la qual es treballa són les taules d'EMPLEAT, CONCEPTE, CLIENT, FACTURA, DETALL, taules sobre les que també existeix un projecte, simulacio.exe, que genera de forma desatesa factures.

La idea per al curs 2008-2009 és migrar tot aquest projecte a Ubuntu + Postgres + PHP, i com a llenguatge de programació que substitueixi VB, un llenguatge de shell, Pyton o Java, ja veurem.

simulació activitat empresarial (Visual Basic)

Simulacio.jpg

El projecte de Visual Basic simulacio.vbp té una petita interfície gràfica, per veure com es van generant les factures, però de fet no caldria.

model relacional (postgres)

-- EMPLEAT (id_empleat,nom,cognom, funcio, mail,sexe)
-- CONCEPTE (id_concepte,nom,categoria,preu,preu_hora)
-- CLIENT (id_client, NIF, empresa, direccio, cp, poblacio, contacte, mail, sexe)
-- FACTURA (id_factura, id_client, id_empleat, data, preu)
-- DETALL (id_factura, id_concepte, preu, quantitat)


DROP TABLE DETALL cascade constraints;
DROP TABLE FACTURA cascade constraints;
DROP TABLE CLIENT cascade constraints;
DROP TABLE CONCEPTE cascade constraints;
DROP TABLE EMPLEAT cascade constraints;
DROP SEQUENCE SEQ_FACT;


CREATE SEQUENCE SEQ_FACT START WITH 1;

CREATE TABLE EMPLEAT(
ID_EMPLEAT NUMERIC(2) PRIMARY KEY,
NOM VARCHAR(25) NOT NULL,
COGNOM VARCHAR(25) NOT NULL,
FUNCIO VARCHAR(25) NOT NULL,
MAIL VARCHAR(25) NOT NULL,
SEXE CHAR(1) NOT NULL);

CREATE TABLE CONCEPTE(
ID_CONCEPTE NUMERIC(2) PRIMARY KEY,
NOM VARCHAR(50) NOT NULL,
CATEGORIA VARCHAR(25) NOT NULL,
PREU NUMERIC(4) NULL,
PREU_HORA NUMERIC(4) NULL);

CREATE TABLE CLIENT(
ID_CLIENT NUMERIC(3) PRIMARY KEY,
NIF VARCHAR(9) NOT NULL,
EMPRESA VARCHAR(50) NOT NULL,
DIRECCIO VARCHAR(50) NOT NULL,
CP CHAR(5) NOT NULL,
POBLACIO VARCHAR(50) NOT NULL,
CONTACTE VARCHAR(25) NOT NULL,
MAIL VARCHAR(25) NOT NULL,
SEXE CHAR(1) NOT NULL
);

CREATE TABLE FACTURA(
ID_FACTURA NUMERIC(5) PRIMARY KEY,
ID_CLIENT NUMERIC(3) REFERENCES CLIENT,
ID_EMPLEAT NUMERIC(2) REFERENCES EMPLEAT,
DIA_HORA DATE,
PREU NUMERIC(6));

CREATE TABLE DETALL(
ID_FACTURA NUMERIC(4) REFERENCES FACTURA,
ID_CONCEPTE NUMERIC(2) REFERENCES CONCEPTE,
PREU NUMERIC(4) NULL,
QUANTITAT NUMERIC(3),
PRIMARY KEY (ID_FACTURA,ID_CONCEPTE));

INSERT INTO EMPLEAT VALUES(1,'Pere','Batista','Administrador Sistemes','pbatista@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(2,'Maria','Cirera','Administrador Sistemes','mcirera@jbalmes.net','D');
INSERT INTO EMPLEAT VALUES(3,'Carme','Puig','Administrador Sistemes','cpuig@jbalmes.net','D');
INSERT INTO EMPLEAT VALUES(4,'Joan','Pujol','Programador Java','jpujol@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(5,'Rita','Vilarmau','Programador VB.NET','rvilarmau@jbalmes.net','D');
INSERT INTO EMPLEAT VALUES(6,'Anna','Puigdollers','Programador Web','apuigdollers@jbalmes.net','D');
INSERT INTO EMPLEAT VALUES(7,'Marta','Barceló','Programador Web','mbarcelo@jbalmes.net','D');
INSERT INTO EMPLEAT VALUES(8,'Jordi','Basses','Dissenyador web','jbasses@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(9,'Núria','Perelló','Dissenyador web','nperello@jbalmes.net','D');
INSERT INTO EMPLEAT VALUES(10,'Guillem','Raspall','HelpDesk','graspall@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(11,'Esteve','Llorens','HelpDesk','ellorens@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(12,'Raimon','Compte','Administratiu','rcompte@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(13,'Josep','Vicenç','Administratiu','jvicenc@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(14,'Toni','Cirici','Comercial','tcirici@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(15,'Claudi','Pellicer','Comercial','cpellicer@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(16,'Raquel','Valldemossa','Directiu','rvalldemossa@jbalmes.net','D');
INSERT INTO EMPLEAT VALUES(17,'Bruna','E.','Directiu','bruna_e@jbalmes.net','D');
INSERT INTO EMPLEAT VALUES(18,'Clara','D.','Directiu','clara_d@jbalmes.net','D');
INSERT INTO EMPLEAT VALUES(19,'Marieta','C.','Directiu','marieta_c@jbalmes.net','D');
INSERT INTO EMPLEAT VALUES(20,'M. Carme','B.','Directiu','carme_b@jbalmes.net','D');
INSERT INTO EMPLEAT VALUES(21,'M. Assumpta','A.','Administrador Sistemes','assumpta_a@jbalmes.net','D');
INSERT INTO EMPLEAT VALUES(22,'Queralt','Z.','Administrador Sistemes','queralt_z@jbalmes.net','D');
INSERT INTO EMPLEAT VALUES(23,'Núria','Y.','Administrador Sistemes','nuria_y@jbalmes.net','D');
INSERT INTO EMPLEAT VALUES(24,'Patrícia','X.','Administrador Sistemes','patricia_x@jbalmes.net','D');
INSERT INTO EMPLEAT VALUES(25,'Sumpta','W.','Administrador Sistemes','sumpta_w@jbalmes.net','D');
INSERT INTO EMPLEAT VALUES(26,'Clara','V.','Administrador Sistemes','clara_v@jbalmes.net','D');
INSERT INTO EMPLEAT VALUES(27,'Quim','U.','Administrador Sistemes','quim_u@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(28,'Quimet','T.','Administrador Sistemes','quimet_t@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(29,'Xavi','S.','Programador Java','xavi_s@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(30,'Xavier','R.','Programador Java','xavier_r@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(31,'Roc','Q.','Programador Java','roc_q@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(32,'Pere','P.','Programador Java','pere_p@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(33,'Perot','O.','Programador Java','perot_o@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(34,'Galdrich','N.','Programador VB.NET','galdrich_n@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(35,'Guifré','M.','Programador VB.NET','guifre_m@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(36,'Jofre','L.','Programador VB.NET','jofre_l@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(37,'Tomeu','K.','Programador VB.NET','tomeu_k@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(38,'Tomàs','J.','Programador VB.NET','tomas_j@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(39,'Tània','I.','Dissenyador web','tania_i@jbalmes.net','D');
INSERT INTO EMPLEAT VALUES(40,'Guim','H.','Dissenyador web','guim_h@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(41,'Andreu','G.','Dissenyador web','andreu_g@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(42,'Santi','F.','Dissenyador web','santi_f@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(43,'Santiago','E.','Dissenyador web','santiago_e@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(44,'Jaume','D.','HelpDesk','jaume_d@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(45,'Ramon','C.','HelpDesk','ramon_c@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(46,'Llorenç','B.','Comercial','llorenc_b@jbalmes.net','H');
INSERT INTO EMPLEAT VALUES(47,'Esteve','A.','Comercial','esteve_a@jbalmes.net','H');

INSERT INTO CONCEPTE VALUES(1,'Allotjament Pla Personal','Hosting',15,0);
INSERT INTO CONCEPTE VALUES(2,'Allotjament Pla Professional','Hosting',30,0);
INSERT INTO CONCEPTE VALUES(3,'Allotjament Pla Empresarial','Hosting',45,0);
INSERT INTO CONCEPTE VALUES(4,'Excés espai 100Mb','Hosting',10,0);
INSERT INTO CONCEPTE VALUES(5,'Excés espai 300Mb','Hosting',20,0);
INSERT INTO CONCEPTE VALUES(6,'Excés transferència 1Gb','Hosting',12,0);
INSERT INTO CONCEPTE VALUES(7,'Excés transferència 2Gb','Hosting',20,0);
INSERT INTO CONCEPTE VALUES(8,'Paquet 50 mails','Hosting',20,0);
INSERT INTO CONCEPTE VALUES(9,'Canvi DNS','Hosting',40,0);
INSERT INTO CONCEPTE VALUES(10,'Ampliació SQL Server','Hosting',50,0);
INSERT INTO CONCEPTE VALUES(11,'Activació filtre antispam','Hosting',30,0);
INSERT INTO CONCEPTE VALUES(12,'Còpia seguretat extra','Hosting',20,0);
INSERT INTO CONCEPTE VALUES(13,'Administració --ota','Hosting',35,0);
INSERT INTO CONCEPTE VALUES(14,'Renovació domini Internet','Hosting',45,0);
INSERT INTO CONCEPTE VALUES(15,'Instal.lació LAN','Manteniment',0,32);
INSERT INTO CONCEPTE VALUES(16,'Instal.lació Wireless','Manteniment',0,35);
INSERT INTO CONCEPTE VALUES(17,'Manteniment empresa tipus I','Manteniment',0,25);
INSERT INTO CONCEPTE VALUES(18,'Manteniment empresa tipus II','Manteniment',0,30);
INSERT INTO CONCEPTE VALUES(19,'Manteniment empresa tipus III','Manteniment',0,35);
INSERT INTO CONCEPTE VALUES(20,'Suport usuari','Manteniment',0,15);
INSERT INTO CONCEPTE VALUES(21,'Còpia de seguretat','Manteniment',50,0);
INSERT INTO CONCEPTE VALUES(22,'Manteniment web','Manteniment',0,18);
INSERT INTO CONCEPTE VALUES(23,'Ordinador tipus I','Venda equip',850,0);
INSERT INTO CONCEPTE VALUES(24,'Ordinador tipus II','Venda equip',1000,0);
INSERT INTO CONCEPTE VALUES(25,'Ordinador tipus III','Venda equip',1250,0);
INSERT INTO CONCEPTE VALUES(26,'IMpressora Làser','Venda equip',230,0);
INSERT INTO CONCEPTE VALUES(27,'IMpressora Tinta','Venda equip',180,0);
INSERT INTO CONCEPTE VALUES(28,'Monitor TFT 15p','Venda equip',280,0);
INSERT INTO CONCEPTE VALUES(29,'Monitor TFT 17p','Venda equip',320,0);
INSERT INTO CONCEPTE VALUES(30,'Reparació ordinador nivell I','Reparació',0,20);
INSERT INTO CONCEPTE VALUES(31,'Reparació ordinador nivell II','Reparació',0,25);
INSERT INTO CONCEPTE VALUES(32,'Eliminació Virus','Reparació',0,25);
INSERT INTO CONCEPTE VALUES(33,'Reparació xarxes','Reparació',0,22);
INSERT INTO CONCEPTE VALUES(34,'Recuperar dades','Reparació',0,35);
INSERT INTO CONCEPTE VALUES(35,'Projecte VB','Projecte programació',0,26);
INSERT INTO CONCEPTE VALUES(36,'Projecte VB.NET','Projecte programació',0,30);
INSERT INTO CONCEPTE VALUES(37,'Projecte Java','Projecte programació',0,26);
INSERT INTO CONCEPTE VALUES(38,'Projecte ASP','Projecte programació',0,30);
INSERT INTO CONCEPTE VALUES(39,'programador PHP','Projecte programació',0,30);
INSERT INTO CONCEPTE VALUES(40,'Disseny pàgina Web bàsic','Projecte programació',300,0);
INSERT INTO CONCEPTE VALUES(41,'Disseny pàgina Web avançada','Projecte programació',400,0);
INSERT INTO CONCEPTE VALUES(42,'Disseny pàgina Web Flash','Projecte programació',500,0);


INSERT INTO CLIENT VALUES(1,'B35105018','Comercial Ricard','C/Comerç, 22 Ent 3a','08025','Barcelona','Laura Renau','lrenau@jbalmes.net','D');
INSERT INTO CLIENT VALUES(2,'B24423425','Materials i Vidres, SL','Poligon Can Mateu','09345','Cornellà (Barcelona)','Maria Vilaró','mvilaro@jbalmes.net','D');
INSERT INTO CLIENT VALUES(3,'C23423423','A. VIDAL PORTA SL','C/Claró, 34','08234','Cardedeu (Barcelona)','Clara codorniu','ccodorniu@jbalmes.net','D');
INSERT INTO CLIENT VALUES(4,'C33786812','ABELLAIRES','C/ de la Pau, 15','08034','Alella (Barcelona)','Antònia Freixenet','afreixenet@jbalmes.net','D');
INSERT INTO CLIENT VALUES(5,'B23545564','ACCIALT BCN SL','C/Santandreu, 20 3r 3a','08245','Sta Perpètua (Barcelona)','Josep M. Cava','jcava@jbalmes.net','H');
INSERT INTO CLIENT VALUES(6,'B16546565','ACEITES MILLÀS SA','C/Manlleu, 15','08452','El Masnou (Barcelona)','Montserrat Cardona','mcardona@jbalmes.net','D');
INSERT INTO CLIENT VALUES(7,'B42389873','Clariant Ibérica','C/Vich, 40 3r','04568','Girona','Ramon Cabestany','rcabestany@jbalmes.net','H');
INSERT INTO CLIENT VALUES(8,'A66342347','Esteve Química','C/de les Heures, 40','08024','Barcelona','Quim Serra','qserra@jbalmes.net','H');
INSERT INTO CLIENT VALUES(9,'A24645898','Natura Selection','C/de la Salut, 30','08245','Barcelona','Guim Serrateix','gserrateix@jbalmes.net','H');
INSERT INTO CLIENT VALUES(10,'A53453465','Net Translations','C/Colom, 44','06644','Berga (Barcelona)','Núria Surera','nsurera@jbalmes.net','D');
INSERT INTO CLIENT VALUES(11,'B34453434','Barceló Gestión Hotelera, S.A.','C/Alegre de Dalt, 40 2n 2a','08025','Barcelona','Clàudia Saladriga','csaladriga@jbalmes.net','D');
INSERT INTO CLIENT VALUES(12,'B39857387','BFM y Asociados, S.C.','C/de la Cera, 20 baixos','08245','Barcelona','Esperança Sabés','esabes@jbalmes.net','D');
INSERT INTO CLIENT VALUES(13,'A26548974','Can Domenge , S.L.','C/Verntallat, 20','08865','Sant Hilari Sacalm (Girona)','M. Pau Santaló','mpsantalo@jbalmes.net','D');
INSERT INTO CLIENT VALUES(14,'B34245344','Clínica Planas','C/Berga, 33 baixos','08034','Barcelona','Pau Vidiella','pvidiella@jbalmes.net','H');
INSERT INTO CLIENT VALUES(15,'C15439879','Consultors G.Q.F., S.L.','C/Joan Blanques, 33 baixos','08634','Barcelona','M. Paller Vinaròs','mpvinaros@jbalmes.net','D');
INSERT INTO CLIENT VALUES(16,'A33424908','D.G. Programació i Ordenació Econòmica','C/de la Ciutat, 30','08567','Mataró (Barcelona)','Queralt Vilalta','qvilalta@jbalmes.net','D');
INSERT INTO CLIENT VALUES(17,'B24389787','Esperanza Hoteles','Ctra de Barcelona, 30 baixos','08844','Badalona (Barcelona)','Montserrat Vila','mvila@jbalmes.net','D');
INSERT INTO CLIENT VALUES(18,'C03427867','Fisioteràpia a domicili, S.L.','C/ 11 de setembre, 34','06644','Palau Solità i Plegamans (Barcelona)','Cinta Bescanó','cbescano@jbalmes.net','D');
INSERT INTO CLIENT VALUES(19,'C04228768','Forma Studio Centre, S.L.','Plaça Catalunya, 1','08468','Bagà (Barcelona)','M. Teresa Bes','mtbes@jbalmes.net','D');
INSERT INTO CLIENT VALUES(20,'B33453453','Galeria Maior','Plaça del General Prim, 12','08844','Montornès del Vallès (Barcelona)','Josep Bellera','jbellera@jbalmes.net','H');
INSERT INTO CLIENT VALUES(21,'A03428768','Global Red, S.L.','C/Rafael Casanovas, 34 3r 1a','08033','Sta Coloma de Gramenet (Barcelona)','M. Salut Darnius','msdarnius@jbalmes.net','D');
INSERT INTO CLIENT VALUES(22,'A34523498','Guía Formación y Asesoramiento, S.L.','C/General Moragues, 20 Local','08844','La Pobla de Segur (Lleida)','Llorenç Domènech','ldomenech@jbalmes.net','H');
INSERT INTO CLIENT VALUES(23,'B25498749','Industrial de Elevación, S.A.','C/el Timbaler del Bruc, 30','08834','El Bruch (Barcelona)','Màrius Piera','mpiera@jbalmes.net','H');
INSERT INTO CLIENT VALUES(24,'C34234789','IXIS Solutions, S.L.','C/de Sant Fèlix','08045','Vilafranca del Penedès (Barcelona)','Ramir Peguera','rpeguera@jbalmes.net','H');
INSERT INTO CLIENT VALUES(25,'B42342563','MK, Grupo de Comunicación','Rambla de solei, 30','08045','Tarragona','Josep Santacana','jsantacana@jbalmes.net','H');
INSERT INTO CLIENT VALUES(26,'C34533562','Marítima del Mediterráneo','Passeig de la Marina, 20 local','08034','Cambrils (Tarragona)','Maria Pérez','mperez@jbalmes.net','D');
INSERT INTO CLIENT VALUES(27,'A47376876','Mare Nostrum Excursions, S.L.','C/del Priorat, 20','08844','Falset (Tarragona)','Miquel Galofré','mgalofre@jbalmes.net','H');
INSERT INTO CLIENT VALUES(28,'D23487876','Montaner i Associats','Avinguda de la Generalitat, 20','08034','Sort (Lleida)','Vicenç Andreu','vandreu@jbalmes.net','H');
INSERT INTO CLIENT VALUES(29,'E47368976','Esport Total','Plaça del Corpus de Sang, 10','08034','Bagà (Barcelona)','Montse Pellicer','mpellicer@jbalmes.net','D');
INSERT INTO CLIENT VALUES(30,'A45375389','Cirera Sports','C/de la Palla, 2','08765','Sant Andreu de la Barca (Barcelona)','M. Teresa Pifarré','mtpifarre@jbalmes.net','D');
INSERT INTO CLIENT VALUES(31,'A54368797','Promoció Esportiva','C/ dels Almogàvers, 20','08834','Barcelona','Queralt Ginebrer','qginebrer@jbalmes.net','D');
INSERT INTO CLIENT VALUES(32,'B34723786','Esportiu Garriga','Passeig de la Muntanya, 20 baixos','06644','Balaguer (Lleida)','Pere Matas','pmatas@jbalmes.net','H');
INSERT INTO CLIENT VALUES(33,'A34534567','Comercial Tomàs','C/prat, 2','08012','Barcelona','M. Capdevila','m_capdevila@jbalmes.net','D');
INSERT INTO CLIENT VALUES(34,'B76543543','Subm. Puig','C/prat, 4','08012','Barcelona','C. Llorenç','c_llorenc@jbalmes.net','D');
INSERT INTO CLIENT VALUES(35,'C34536547','Construccions Pujol','C/prat, 6','08012','Barcelona','A. Soler','a_soler@jbalmes.net','D');
INSERT INTO CLIENT VALUES(36,'D75345675','Carnisseria Porc','C/prat, 8','08012','Barcelona','P. Perot','p_perot@jbalmes.net','D');
INSERT INTO CLIENT VALUES(37,'A46786878','Embotits Ca la Masa','C/prat, 10','08012','Barcelona','A. Cisco','a_cisco@jbalmes.net','D');
INSERT INTO CLIENT VALUES(38,'B09438544','Floristeria Coll','C/prat, 12','08012','Barcelona','S. Santaló','s_santalo@jbalmes.net','D');
INSERT INTO CLIENT VALUES(39,'C34537676','Legums Cuits, SCP','C/prat, 14','08012','Barcelona','X. Bartomeu','x_bartomeu@jbalmes.net','D');
INSERT INTO CLIENT VALUES(40,'34537676D','Flors Seques, coop','C/prat, 16','08012','Barcelona','A. Xicoy','a_xicoy@jbalmes.net','D');
INSERT INTO CLIENT VALUES(41,'56756734A','Xocolata Ametller','C/prat, 1','08012','Barcelona','J. Joanpere','j_joanpere@jbalmes.net','H');
INSERT INTO CLIENT VALUES(42,'34547676B','Gasoses Quim SL','C/prat, 3','08012','Barcelona','M. Martí','m_marti@jbalmes.net','H');
INSERT INTO CLIENT VALUES(43,'87656456C','Esportiu Vilanova','C/prat, 5','08012','Barcelona','D. Arnau','d_arnau@jbalmes.net','H');
INSERT INTO CLIENT VALUES(44,'45437765D','Esportiu Vilavella','C/prat, 7','08012','Barcelona','G. Gavina','g_gavina@jbalmes.net','H');
INSERT INTO CLIENT VALUES(45,'75685656A','Elàstics Blaus SA','C/prat, 9','08012','Barcelona','L. Liró','l_liro@jbalmes.net','H');
INSERT INTO CLIENT VALUES(46,'35564576B','Betes i Fils, SL','C/prat, 11','08012','Barcelona','T. Talòs','t_talos@jbalmes.net','H');
INSERT INTO CLIENT VALUES(47,'34566756C','La Baldufa, SCP','C/prat, 13','08012','Barcelona','O. Vilarrubia','o_vilarrubia@jbalmes.net','H');
INSERT INTO CLIENT VALUES(48,'35105018A','Profes Associats SCCP','C/Pau Claris, 121','08012','Barcelona','Joan Quintana','jquintana@jbalmes.net','H');

Projecte simulacio.vbp, formulari simulacio.frm

'simulació. Versió 1
Dim cadena_conn As String
Public connConnection As ADODB.Connection
Public rs As ADODB.Recordset
Public cmd As ADODB.Command
Dim nom(30) As String


Private Sub simular()
On Error GoTo control_error


id_client = escollir_client()
id_empleat = escollir_empleat()

connConnection.BeginTrans

Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
rs.ActiveConnection = connConnection

'la transaccio 'INSERIR FACTURA' són 3 passos:
'1. INSERT INTO FACTURA
'2. INSERT INTO DETALL X N
'3. UPDATE FACTURA SET PREU=XXX
'inserim la nova transacció

'la funció nova_transaccio()
rs.Open "select nova_transaccio()"
rs.Close

'rs.Open "select NEXTVAL('seq_trans') as id_trans"
'id_trans = rs("id_trans")
'rs.Close

'rs.Open "select setval('seq_trans_detall', 1)"
'rs.Close

rs.Open "select CURRVAL('seq_trans') as id_trans"
id_trans = rs("id_trans")
rs.Close

rs.Open "select NEXTVAL('seq_fact') as num_fact"
num_fact = rs("num_fact")
rs.Close

'calculem aleatòriament el dia que es va produir la factura
Randomize
Dim num_dies
num_dies = Int(Rnd * 85)  'número d'hores
Dim dia_hora As Date
dia_hora = CDate("1/1/2008") + num_dies
dia_hora2 = Year(dia_hora) & "/" & Month(dia_hora) & "/" & Day(dia_hora)

'inserim la transacció
cmd.ActiveConnection = connConnection
cmd.CommandText = "insert into TRANSACCIO(id_trans,descripcio,dia_hora,usuari,login_pg,ip,estat) values(" & CStr(id_trans) & ",'INSERCIO FACTURA num " & CStr(num_fact) & "','" & dia_hora2 & "','joan','sgbd','192.168.0.9','o')"
'MsgBox cmd.CommandText
cmd.Execute

'inserim la factura
cmd.CommandText = "insert into factura(id_factura,id_client,id_empleat,dia_hora) values(" & CStr(num_fact) & "," & id_client & "," & id_empleat & ",'" & dia_hora2 & "')"
'MsgBox cmd.CommandText
cmd.Execute

lbl_factura.Caption = "Factura " & CStr(num_fact)
Me.Refresh

'inserim els conceptes corresponents a aquesta factura
num = Int(Rnd * 5) + 1
Dim conceptes() As Integer
Dim tipus_concepte As String 'tipus_concepte="per_hora"; tipus_concepte="per_servei"
ReDim conceptes(num)
Dim compatdor_conceptes As Integer
comptador_conceptes = 0
Dim ja_existeix As Boolean

Do While comptador_conceptes <= num
    quantitat = Int(Rnd * 20) + 8 'número d'hores
    id_concepte = escollir_concepte()

    'he de controlar que aquest concepte no hagi aparegut, doncs si no donaria un error de clau primària
    ja_existeix = False

    For j = 0 To num
    If conceptes(j) = id_concepte Then ja_existeix = True
    Next j
    
    If ja_existeix = False Then
        conceptes(comptador_conceptes) = id_concepte
        rs.Open "select preu,preu_hora from concepte where concepte.id_concepte=" + CStr(id_concepte)
        If rs("preu_hora") = 0 Then tipus_concepte = "per_servei" Else tipus_concepte = "per_hora"

        If tipus_concepte = "per_servei" Then
            cmd.CommandText = "insert into detall(id_factura,id_concepte,preu,quantitat) values(" + CStr(num_fact) + "," + CStr(id_concepte) + "," + CStr(rs("preu")) + ",1)"
        Else
            cmd.CommandText = "insert into detall(id_factura,id_concepte,preu,quantitat) values(" + CStr(num_fact) + "," + CStr(id_concepte) + "," + CStr(rs("preu_hora")) + "," + CStr(quantitat) + ")"
        End If
        cmd.Execute
        rs.Close
        comptador_conceptes = comptador_conceptes + 1
    End If
    
Loop


    rs.Open "select sum(detall.preu*quantitat) as total from concepte,detall where concepte.id_concepte=detall.id_concepte and id_factura=" + CStr(num_fact)
    total = rs("total")
    cmd.CommandText = "update factura set preu=" + CStr(total) + " where id_factura=" + CStr(num_fact)
    rs.Close
    cmd.Execute
    connConnection.CommitTrans

    Set rs = Nothing

Exit Sub
control_error:
    MsgBox CStr(Err.Number) & " " & Err.Description & " cridar el professor"
    connConnection.RollbackTrans
    Set rs = Nothing
End Sub

Private Function escollir_client() As Integer
'hi ha 32 clients
Randomize
escollir_client = Int(Rnd() * 48) + 1 '48 clients
End Function
Private Function escollir_empleat() As Integer
'hi ha 16 empleats
Randomize
escollir_empleat = Int(Rnd() * 47) + 1 '47 conceptes
End Function
Private Function escollir_concepte() As Integer
'hi ha 40 conceptes
Randomize
escollir_concepte = Int(Rnd() * 42) + 1
End Function


Private Sub bt_simular_Click()
If bt_simular.Caption = "Simular" Then
    Timer1.Enabled = True
    bt_simular.Caption = "Stop"
Else
    Timer1.Enabled = False
    bt_simular.Caption = "Simular"
End If
End Sub



Private Sub Form_Load()
    txt_cadena = "Provider=PostgreSQL.1;Password=postgres;User ID=postgres;Data Source=localhost;Location=postgres;Extended Properties="""""
    'Ens connectem a la BD
    frm_simulacio.MousePointer = 13
    Set connConnection = New ADODB.Connection
    connConnection.CursorLocation = adUseServer
    connConnection.Open txt_cadena
    frm_simulacio.MousePointer = 0
    Set cmd = New ADODB.Command
End Sub

Private Sub Form_Unload(Cancel As Integer)
Set cmd = Nothing
connConnection.Close
Set connConnection = Nothing
End Sub

Private Sub Timer1_Timer()
    simular
End Sub

Taules d'auditació: TRANSACCIO i TRANSACCIO_DETALL

La informació d'auditació s'emmagatzema en aquestes dues taules. ALTARM significa transacció lògica. Per a mi una transacció lògica, en l'exemple que ens ocupa, és per exemple la creació d'una factura amb totes les línies de detall que conté. L'inici i la finalització d'una transacció queda marcada en el codi del projecte simulacio.exe.

Per exemple, en l'inici d'una nova factura s'executa el codi: 

rs.Open "select nova_transaccio()"
rs.Close

rs.Open "select CURRVAL('seq_trans') as id_trans"
id_trans = rs("id_trans")
rs.Close

script postgres

--TRANSACCIO(id_trans,descripcio,dia_hora)
--TRANSACCIO_DETALL(id_trans,ordre,tipus,tname,cname,ctype,clong,oldv,newv)

--inicialitzem
DROP FUNCTION save_changes(vid numeric, vid_detall numeric, vordre numeric, vtipus character varying, p_tname character varying, p_cname character varying, p_oldv character varying, p_newv character varying, p_datatype1 character varying, p_datatype2 numeric, ves_pk boolean);
DROP TABLE TRANSACCIO_DETALL;
DROP TABLE TRANSACCIO;
DROP SEQUENCE SEQ_TRANS;
DROP SEQUENCE SEQ_TRANS_DETALL;
DROP SEQUENCE SEQ_FACT;

CREATE SEQUENCE SEQ_FACT START WITH 1;
CREATE SEQUENCE SEQ_TRANS START WITH 1;
CREATE SEQUENCE SEQ_TRANS_DETALL START WITH 1;  

CREATE TABLE TRANSACCIO(
id_trans NUMERIC(5) PRIMARY KEY,
descripcio varchar(50),
dia_hora date,
usuari varchar(20),
login_pg varchar(20),
ip varchar(15),
estat "char" 
);
--estat: o original, intacte; r rollback, s'ha desfet el canvi; u undefined (no ho sabem, s'ha fet un rollback sobre un rollback)

CREATE TABLE TRANSACCIO_DETALL(
id_trans numeric(5),
id_trans_detall numeric(5),
ordre NUMERIC(2),
tipus varchar(10),
tname varchar(30),
cname varchar(30),
oldv varchar(255), 
newv varchar(255),
cdatatype1 varchar(25),
cdatatype2 numeric(4),
es_pk boolean,
PRIMARY KEY(id_trans,id_trans_detall,ordre)
);


CREATE LANGUAGE plpgsql; --si és que cal

create or replace function save_changes( 
vid in numeric,
vid_detall in numeric,
vordre numeric,
vtipus varchar,
p_tname in varchar, 
p_cname in varchar,
p_oldv in varchar, 
p_newv in varchar,
p_datatype1 in varchar,
p_datatype2 in numeric,
ves_pk in boolean
) returns int
as ' 
begin 
	if ( p_oldv <> p_newv or ( p_oldv is not null and p_newv is null ) or ( p_oldv is null and p_newv is not null ) or ves_pk ) then 
		INSERT INTO TRANSACCIO_DETALL (id_trans,id_trans_detall,ordre,tipus,tname,cname,oldv,newv,cdatatype1,cdatatype2,es_pk) values (vid,vid_detall,vordre,vtipus,p_tname,p_cname,p_oldv,p_newv,p_datatype1,p_datatype2,ves_pk); 
	end if; 
	RETURN 1;
end;
' language 'plpgsql'

--veure fitxer taula_prova_audit.txt
--en la versió 8.2 i anteriors, no es pot fer una conversió de bool a varchar. És possible que per a la versió 8.3 ja estigui resolt. En la versió 8.3 ja està resolt, però retorna 'true' i 'false' en comptes de 't' i 'f'.
create or replace function bool_to_varchar (boolean) returns varchar as $bool_to_varchar$ 
begin
if $1 then 
return 't';
else 
return 'f';
end if;
end;
$bool_to_varchar$ LANGUAGE plpgsql;

create cast (boolean as varchar)
    with function bool_to_varchar(boolean)
    as assignment;


--nova_transacció
create or replace function nova_transaccio() returns int as $$                                                                  
declare
	vid NUMERIC(5);	
	vid_detall NUMERIC(5);	                                                                                                  
begin
select NEXTVAL('seq_trans') into vid;
select SETVAL('seq_trans_detall', 1) into vid_detall;
RETURN 1;
end;
$$ language 'plpgsql'

Joc de proves

A mode d'exemple, es mostra el joc de proves i així puc practicar el funcionament del projecte 

SELECT MAX(ID_FACTURA) FROM FACTURA;
--802
--select NEXTVAL('seq_trans') as num_trans"

--Esborrem una factura:
--insert into TRANSACCIO(id_trans,descripcio,dia_hora,usuari,login_pg,ip,estat) values(NEXTVAL('seq_trans'),'ESBORRAR FACTURA num 802',NOW(),'joan','sgbd','192.168.0.9','o')
--delete from detall where id_factura=802;
--delete from factura where id_factura=802;

--inserim una factura:
--insert into TRANSACCIO(id_trans,descripcio,dia_hora,usuari,login_pg,ip,estat) values(NEXTVAL('seq_trans'),'INSERIR FACTURA num 802',NOW(),'joan','sgbd','192.168.0.9','o')
--insert into factura(id_factura,id_client,id_empleat,dia_hora) values(802,2,2,now());
--insert into detall(id_factura,id_concepte,preu,quantitat) values(802,1,10,2);
--insert into detall(id_factura,id_concepte,preu,quantitat) values(802,2,12,3);
--insert into detall(id_factura,id_concepte,preu,quantitat) values(802,5,13,4);
--update factura set preu=108 where id_factura=802;

--per fer canvis individuals, he d'actualitzar ed id de la transaccio i transaccio_detall:
--select NEXTVAL('seq_trans');
--select setval('seq_trans_detall', 1);
--update factura set preu=40 where id_factura=8;

create or replace function nova_transaccio() returns int as $$                                                                  
declare
	vid NUMERIC(5);	
	vid_detall NUMERIC(5);	                                                                                                  
begin
select NEXTVAL('seq_trans') into vid;
select SETVAL('seq_trans_detall', 1) into vid_detall;
RETURN 1;
end;
$$ language 'plpgsql'

select nova_transaccio();
update factura set preu=40 where id_factura=8;

select nova_transaccio();
delete from detall where id_factura=8;
delete from factura where id_factura=8;

select nova_transaccio();
insert into transaccio values (CURRVAL('SEQ_TRANS'),'modificacio preu','2008-4-28','joan','sgbd','192.168.0.9','o');
update factura set preu=40 where id_factura=7;
update factura set preu=41 where id_factura=7;
update factura set preu=42 where id_factura=7;

Generació audits

generador_audits.vbp és un projecte Visual Basic que conté un mòdul (generador_audits.bas, no té interfície gràfica) que té per missió crear triggers d'auditació de les taules que ens interessa auditar.

Les taules que volem auditar estan en el fitxer taules.txt: 

empleat
client
concepte
factura
detall

i el fitxer resultant és el script script.txt, que conté el codi que executarem en la consola de Postgres i que ens crearà els disparadors.

generador_audits.bas

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command

Dim pks() As String 'necessari que sigui global, la necessita la funció es_pk

Sub Main()
'1. obrir el fitxer que conte les taules a auditar
'2. crear el fitxer de sortida, amb el codi per generar els audits
Dim taula As String
obrir_connexio
Open App.Path & "/script.txt" For Output As #1
'llegim totes les taules que conté el fitxer taules.txt
Open App.Path & "/taules.txt" For Input As #2
Do While Not EOF(2)
'I'm getting an error here
Input #2, taula
generar taula
Loop

Close #2
Close #1
tancar_connexio

End Sub
Sub obrir_connexio()
    'txt_cadena = "Provider=PostgreSQL.1;Password=sgbd;User ID=sgbd;Data Source=192.168.0.9;Location=dai;Extended Properties="""""
    'Ens connectem a la BD
    Set conn = New ADODB.Connection
    conn.CursorLocation = adUseServer
    'conn.Open txt_cadena
    conn.Open "DSN=postgresql30"
    Set cmd = New ADODB.Command
    Set rs = New ADODB.Recordset
    rs.ActiveConnection = conn
    cmd.ActiveConnection = conn

End Sub
Sub tancar_connexio()
conn.Close
Set rs = Nothing
Set cmd = Nothing
Set conn = Nothing
End Sub

Sub generar(vtaula As String)
'majúscules i minúscules importen en el nom de la taula!!

'fico en una matriu els camps que formen la clau primària de la taula
cadsql = "SELECT a.attname AS pk_name FROM pg_class c, pg_attribute a, pg_index i " & _
 "WHERE c.relname = '" & vtaula & "' " & _
 "AND c.oid=i.indrelid " & _
 "AND a.attnum > 0 " & _
 "AND a.attrelid = i.indexrelid " & _
 "AND i.indisprimary='t'"
rs.Open cadsql, conn, 3, 3
'MsgBox cadsql
ReDim pks(rs.RecordCount)
i = 0
Do While Not rs.EOF
pks(i) = rs("pk_name")
rs.MoveNext
i = i + 1
Loop
rs.Close

Print #1, ""
Print #1, "-- ##### taula " & vtaula & " ###################################################"
Print #1, ""
'Print #1, "DROP TRIGGER AUDIT_" & vtaula & " ON " & vtaula & ";"
Print #1, ""
Print #1, "create or replace function audit_" & vtaula & "() returns trigger as $audit_" & vtaula & "$"
Print #1, "declare"
Print #1, "    vid NUMERIC(5);"
Print #1, "    vid_detall NUMERIC(5);"
Print #1, "    vdescripcio VARCHAR(50); --descripcio de la transaccio"
Print #1, "    vret numeric(1); --en aquesta variable fiquem el que retorna la funció save_changes"
Print #1, "begin"
Print #1, "    select CURRVAL('SEQ_TRANS') into vid;"
Print #1, "    select NEXTVAL('SEQ_TRANS_DETALL')-1 into vid_detall;"
Print #1, ""

'cadsql = "SELECT attname,attstorage FROM pg_class,pg_attribute WHERE pg_class.relname='" & vtaula & "' AND pg_attribute.attrelid=pg_class.oid AND attnum > 0 ORDER BY attnum"
cadsql = "SELECT attname,attstorage,atttypid,typname FROM pg_class, pg_attribute, pg_type WHERE pg_class.relname='" & vtaula & "' AND pg_attribute.attrelid=pg_class.oid and pg_attribute.atttypid=pg_type.typelem and pg_type.typname like '\\_%' AND attnum > 0 ORDER BY attnum"
rs.Open cadsql
'QUERY RETURNED UNHANDLED TYPE 18 " - suso
'dóna error. Potser és de la versió del driver oledb. Funciona amb driber ODBC (DSN)

'insert
Print #1, ""
Print #1, "    IF (TG_OP = 'INSERT') THEN"
i = 1
Do While Not rs.EOF
    cad = "        vret = save_changes(vid,vid_detall," & CStr(i) & ",TG_OP,'" & vtaula & "','" & rs("attname") & "',NULL,CAST(NEW." & rs("attname") & " AS varchar),'" & rs("typname") & "'," & CStr(rs("atttypid")) & ","
    If es_pk(rs("attname")) Then cad = cad & "true);" Else cad = cad & "false);"
    Print #1, cad
    rs.MoveNext
    i = i + 1
Loop

'update
Print #1, ""
Print #1, "    ELSIF (TG_OP = 'UPDATE') THEN"
rs.MoveFirst
i = 1
Do While Not rs.EOF
    'm: numèric, x: varchar, p: date
    cad = "        vret = save_changes(vid,vid_detall," & CStr(i) & ",TG_OP,'" & vtaula & "','" & rs("attname") & "',CAST(OLD." & rs("attname") & " AS varchar),CAST(NEW." & rs("attname") & " AS varchar),'" & rs("typname") & "'," & CStr(rs("atttypid")) & ","
    If es_pk(rs("attname")) Then cad = cad & "true);" Else cad = cad & "false);"
    Print #1, cad
    rs.MoveNext
    i = i + 1
Loop

'delete
Print #1, ""
Print #1, "    ELSIF (TG_OP = 'DELETE') THEN"
rs.MoveFirst
i = 1
Do While Not rs.EOF
    cad = "        vret = save_changes(vid,vid_detall," & CStr(i) & ",TG_OP,'" & vtaula & "','" & rs("attname") & "',CAST(OLD." & rs("attname") & " AS varchar),NULL,'" & rs("typname") & "'," & CStr(rs("atttypid")) & ","
    If es_pk(rs("attname")) Then cad = cad & "true);" Else cad = cad & "false);"
    Print #1, cad
    rs.MoveNext
    i = i + 1
Loop
rs.Close

Print #1, ""
Print #1, "END IF;"
Print #1, "    RETURN NULL;"
Print #1, "    end;"
Print #1, "$audit_" & vtaula & "$ LANGUAGE plpgsql;"
Print #1, ""
Print #1, "CREATE TRIGGER audit_" & vtaula & " after INSERT OR UPDATE or DELETE ON " & vtaula
Print #1, "    FOR EACH ROW EXECUTE PROCEDURE audit_" & vtaula & "();"


End Sub
Private Function es_pk(vcamp) As Boolean
Dim res As Boolean
res = False
For i = 0 To UBound(pks) - 1
If pks(i) = vcamp Then res = res Or True
Next i
es_pk = res
End Function

script.txt


-- ##### taula empleat ###################################################


create or replace function audit_empleat() returns trigger as $audit_empleat$
declare
    vid NUMERIC(5);
    vid_detall NUMERIC(5);
    vdescripcio VARCHAR(50); --descripcio de la transaccio
    vret numeric(1); --en aquesta variable fiquem el que retorna la funció save_changes
begin
    select CURRVAL('SEQ_TRANS') into vid;
    select NEXTVAL('SEQ_TRANS_DETALL')-1 into vid_detall;


    IF (TG_OP = 'INSERT') THEN
        vret = save_changes(vid,vid_detall,1,TG_OP,'empleat','id_empleat',NULL,CAST(NEW.id_empleat AS varchar),'_numeric',1700,true);
        vret = save_changes(vid,vid_detall,2,TG_OP,'empleat','nom',NULL,CAST(NEW.nom AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,3,TG_OP,'empleat','cognom',NULL,CAST(NEW.cognom AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,4,TG_OP,'empleat','funcio',NULL,CAST(NEW.funcio AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,5,TG_OP,'empleat','mail',NULL,CAST(NEW.mail AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,6,TG_OP,'empleat','sexe',NULL,CAST(NEW.sexe AS varchar),'_bpchar',1042,false);

    ELSIF (TG_OP = 'UPDATE') THEN
        vret = save_changes(vid,vid_detall,1,TG_OP,'empleat','id_empleat',CAST(OLD.id_empleat AS varchar),CAST(NEW.id_empleat AS varchar),'_numeric',1700,true);
        vret = save_changes(vid,vid_detall,2,TG_OP,'empleat','nom',CAST(OLD.nom AS varchar),CAST(NEW.nom AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,3,TG_OP,'empleat','cognom',CAST(OLD.cognom AS varchar),CAST(NEW.cognom AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,4,TG_OP,'empleat','funcio',CAST(OLD.funcio AS varchar),CAST(NEW.funcio AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,5,TG_OP,'empleat','mail',CAST(OLD.mail AS varchar),CAST(NEW.mail AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,6,TG_OP,'empleat','sexe',CAST(OLD.sexe AS varchar),CAST(NEW.sexe AS varchar),'_bpchar',1042,false);

    ELSIF (TG_OP = 'DELETE') THEN
        vret = save_changes(vid,vid_detall,1,TG_OP,'empleat','id_empleat',CAST(OLD.id_empleat AS varchar),NULL,'_numeric',1700,true);
        vret = save_changes(vid,vid_detall,2,TG_OP,'empleat','nom',CAST(OLD.nom AS varchar),NULL,'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,3,TG_OP,'empleat','cognom',CAST(OLD.cognom AS varchar),NULL,'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,4,TG_OP,'empleat','funcio',CAST(OLD.funcio AS varchar),NULL,'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,5,TG_OP,'empleat','mail',CAST(OLD.mail AS varchar),NULL,'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,6,TG_OP,'empleat','sexe',CAST(OLD.sexe AS varchar),NULL,'_bpchar',1042,false);

END IF;
    RETURN NULL;
    end;
$audit_empleat$ LANGUAGE plpgsql;

CREATE TRIGGER audit_empleat after INSERT OR UPDATE or DELETE ON empleat
    FOR EACH ROW EXECUTE PROCEDURE audit_empleat();

-- ##### taula client ###################################################


create or replace function audit_client() returns trigger as $audit_client$
declare
    vid NUMERIC(5);
    vid_detall NUMERIC(5);
    vdescripcio VARCHAR(50); --descripcio de la transaccio
    vret numeric(1); --en aquesta variable fiquem el que retorna la funció save_changes
begin
    select CURRVAL('SEQ_TRANS') into vid;
    select NEXTVAL('SEQ_TRANS_DETALL')-1 into vid_detall;


    IF (TG_OP = 'INSERT') THEN
        vret = save_changes(vid,vid_detall,1,TG_OP,'client','id_client',NULL,CAST(NEW.id_client AS varchar),'_numeric',1700,true);
        vret = save_changes(vid,vid_detall,2,TG_OP,'client','nif',NULL,CAST(NEW.nif AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,3,TG_OP,'client','empresa',NULL,CAST(NEW.empresa AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,4,TG_OP,'client','direccio',NULL,CAST(NEW.direccio AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,5,TG_OP,'client','cp',NULL,CAST(NEW.cp AS varchar),'_bpchar',1042,false);
        vret = save_changes(vid,vid_detall,6,TG_OP,'client','poblacio',NULL,CAST(NEW.poblacio AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,7,TG_OP,'client','contacte',NULL,CAST(NEW.contacte AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,8,TG_OP,'client','mail',NULL,CAST(NEW.mail AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,9,TG_OP,'client','sexe',NULL,CAST(NEW.sexe AS varchar),'_bpchar',1042,false);

    ELSIF (TG_OP = 'UPDATE') THEN
        vret = save_changes(vid,vid_detall,1,TG_OP,'client','id_client',CAST(OLD.id_client AS varchar),CAST(NEW.id_client AS varchar),'_numeric',1700,true);
        vret = save_changes(vid,vid_detall,2,TG_OP,'client','nif',CAST(OLD.nif AS varchar),CAST(NEW.nif AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,3,TG_OP,'client','empresa',CAST(OLD.empresa AS varchar),CAST(NEW.empresa AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,4,TG_OP,'client','direccio',CAST(OLD.direccio AS varchar),CAST(NEW.direccio AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,5,TG_OP,'client','cp',CAST(OLD.cp AS varchar),CAST(NEW.cp AS varchar),'_bpchar',1042,false);
        vret = save_changes(vid,vid_detall,6,TG_OP,'client','poblacio',CAST(OLD.poblacio AS varchar),CAST(NEW.poblacio AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,7,TG_OP,'client','contacte',CAST(OLD.contacte AS varchar),CAST(NEW.contacte AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,8,TG_OP,'client','mail',CAST(OLD.mail AS varchar),CAST(NEW.mail AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,9,TG_OP,'client','sexe',CAST(OLD.sexe AS varchar),CAST(NEW.sexe AS varchar),'_bpchar',1042,false);

    ELSIF (TG_OP = 'DELETE') THEN
        vret = save_changes(vid,vid_detall,1,TG_OP,'client','id_client',CAST(OLD.id_client AS varchar),NULL,'_numeric',1700,true);
        vret = save_changes(vid,vid_detall,2,TG_OP,'client','nif',CAST(OLD.nif AS varchar),NULL,'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,3,TG_OP,'client','empresa',CAST(OLD.empresa AS varchar),NULL,'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,4,TG_OP,'client','direccio',CAST(OLD.direccio AS varchar),NULL,'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,5,TG_OP,'client','cp',CAST(OLD.cp AS varchar),NULL,'_bpchar',1042,false);
        vret = save_changes(vid,vid_detall,6,TG_OP,'client','poblacio',CAST(OLD.poblacio AS varchar),NULL,'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,7,TG_OP,'client','contacte',CAST(OLD.contacte AS varchar),NULL,'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,8,TG_OP,'client','mail',CAST(OLD.mail AS varchar),NULL,'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,9,TG_OP,'client','sexe',CAST(OLD.sexe AS varchar),NULL,'_bpchar',1042,false);

END IF;
    RETURN NULL;
    end;
$audit_client$ LANGUAGE plpgsql;

CREATE TRIGGER audit_client after INSERT OR UPDATE or DELETE ON client
    FOR EACH ROW EXECUTE PROCEDURE audit_client();

-- ##### taula concepte ###################################################


create or replace function audit_concepte() returns trigger as $audit_concepte$
declare
    vid NUMERIC(5);
    vid_detall NUMERIC(5);
    vdescripcio VARCHAR(50); --descripcio de la transaccio
    vret numeric(1); --en aquesta variable fiquem el que retorna la funció save_changes
begin
    select CURRVAL('SEQ_TRANS') into vid;
    select NEXTVAL('SEQ_TRANS_DETALL')-1 into vid_detall;


    IF (TG_OP = 'INSERT') THEN
        vret = save_changes(vid,vid_detall,1,TG_OP,'concepte','id_concepte',NULL,CAST(NEW.id_concepte AS varchar),'_numeric',1700,true);
        vret = save_changes(vid,vid_detall,2,TG_OP,'concepte','nom',NULL,CAST(NEW.nom AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,3,TG_OP,'concepte','categoria',NULL,CAST(NEW.categoria AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,4,TG_OP,'concepte','preu',NULL,CAST(NEW.preu AS varchar),'_numeric',1700,false);
        vret = save_changes(vid,vid_detall,5,TG_OP,'concepte','preu_hora',NULL,CAST(NEW.preu_hora AS varchar),'_numeric',1700,false);

    ELSIF (TG_OP = 'UPDATE') THEN
        vret = save_changes(vid,vid_detall,1,TG_OP,'concepte','id_concepte',CAST(OLD.id_concepte AS varchar),CAST(NEW.id_concepte AS varchar),'_numeric',1700,true);
        vret = save_changes(vid,vid_detall,2,TG_OP,'concepte','nom',CAST(OLD.nom AS varchar),CAST(NEW.nom AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,3,TG_OP,'concepte','categoria',CAST(OLD.categoria AS varchar),CAST(NEW.categoria AS varchar),'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,4,TG_OP,'concepte','preu',CAST(OLD.preu AS varchar),CAST(NEW.preu AS varchar),'_numeric',1700,false);
        vret = save_changes(vid,vid_detall,5,TG_OP,'concepte','preu_hora',CAST(OLD.preu_hora AS varchar),CAST(NEW.preu_hora AS varchar),'_numeric',1700,false);

    ELSIF (TG_OP = 'DELETE') THEN
        vret = save_changes(vid,vid_detall,1,TG_OP,'concepte','id_concepte',CAST(OLD.id_concepte AS varchar),NULL,'_numeric',1700,true);
        vret = save_changes(vid,vid_detall,2,TG_OP,'concepte','nom',CAST(OLD.nom AS varchar),NULL,'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,3,TG_OP,'concepte','categoria',CAST(OLD.categoria AS varchar),NULL,'_varchar',1043,false);
        vret = save_changes(vid,vid_detall,4,TG_OP,'concepte','preu',CAST(OLD.preu AS varchar),NULL,'_numeric',1700,false);
        vret = save_changes(vid,vid_detall,5,TG_OP,'concepte','preu_hora',CAST(OLD.preu_hora AS varchar),NULL,'_numeric',1700,false);

END IF;
    RETURN NULL;
    end;
$audit_concepte$ LANGUAGE plpgsql;

CREATE TRIGGER audit_concepte after INSERT OR UPDATE or DELETE ON concepte
    FOR EACH ROW EXECUTE PROCEDURE audit_concepte();

-- ##### taula factura ###################################################


create or replace function audit_factura() returns trigger as $audit_factura$
declare
    vid NUMERIC(5);
    vid_detall NUMERIC(5);
    vdescripcio VARCHAR(50); --descripcio de la transaccio
    vret numeric(1); --en aquesta variable fiquem el que retorna la funció save_changes
begin
    select CURRVAL('SEQ_TRANS') into vid;
    select NEXTVAL('SEQ_TRANS_DETALL')-1 into vid_detall;


    IF (TG_OP = 'INSERT') THEN
        vret = save_changes(vid,vid_detall,1,TG_OP,'factura','id_factura',NULL,CAST(NEW.id_factura AS varchar),'_numeric',1700,true);
        vret = save_changes(vid,vid_detall,2,TG_OP,'factura','id_client',NULL,CAST(NEW.id_client AS varchar),'_numeric',1700,false);
        vret = save_changes(vid,vid_detall,3,TG_OP,'factura','id_empleat',NULL,CAST(NEW.id_empleat AS varchar),'_numeric',1700,false);
        vret = save_changes(vid,vid_detall,4,TG_OP,'factura','dia_hora',NULL,CAST(NEW.dia_hora AS varchar),'_date',1082,false);
        vret = save_changes(vid,vid_detall,5,TG_OP,'factura','preu',NULL,CAST(NEW.preu AS varchar),'_numeric',1700,false);

    ELSIF (TG_OP = 'UPDATE') THEN
        vret = save_changes(vid,vid_detall,1,TG_OP,'factura','id_factura',CAST(OLD.id_factura AS varchar),CAST(NEW.id_factura AS varchar),'_numeric',1700,true);
        vret = save_changes(vid,vid_detall,2,TG_OP,'factura','id_client',CAST(OLD.id_client AS varchar),CAST(NEW.id_client AS varchar),'_numeric',1700,false);
        vret = save_changes(vid,vid_detall,3,TG_OP,'factura','id_empleat',CAST(OLD.id_empleat AS varchar),CAST(NEW.id_empleat AS varchar),'_numeric',1700,false);
        vret = save_changes(vid,vid_detall,4,TG_OP,'factura','dia_hora',CAST(OLD.dia_hora AS varchar),CAST(NEW.dia_hora AS varchar),'_date',1082,false);
        vret = save_changes(vid,vid_detall,5,TG_OP,'factura','preu',CAST(OLD.preu AS varchar),CAST(NEW.preu AS varchar),'_numeric',1700,false);

    ELSIF (TG_OP = 'DELETE') THEN
        vret = save_changes(vid,vid_detall,1,TG_OP,'factura','id_factura',CAST(OLD.id_factura AS varchar),NULL,'_numeric',1700,true);
        vret = save_changes(vid,vid_detall,2,TG_OP,'factura','id_client',CAST(OLD.id_client AS varchar),NULL,'_numeric',1700,false);
        vret = save_changes(vid,vid_detall,3,TG_OP,'factura','id_empleat',CAST(OLD.id_empleat AS varchar),NULL,'_numeric',1700,false);
        vret = save_changes(vid,vid_detall,4,TG_OP,'factura','dia_hora',CAST(OLD.dia_hora AS varchar),NULL,'_date',1082,false);
        vret = save_changes(vid,vid_detall,5,TG_OP,'factura','preu',CAST(OLD.preu AS varchar),NULL,'_numeric',1700,false);

END IF;
    RETURN NULL;
    end;
$audit_factura$ LANGUAGE plpgsql;

CREATE TRIGGER audit_factura after INSERT OR UPDATE or DELETE ON factura
    FOR EACH ROW EXECUTE PROCEDURE audit_factura();

-- ##### taula detall ###################################################


create or replace function audit_detall() returns trigger as $audit_detall$
declare
    vid NUMERIC(5);
    vid_detall NUMERIC(5);
    vdescripcio VARCHAR(50); --descripcio de la transaccio
    vret numeric(1); --en aquesta variable fiquem el que retorna la funció save_changes
begin
    select CURRVAL('SEQ_TRANS') into vid;
    select NEXTVAL('SEQ_TRANS_DETALL')-1 into vid_detall;


    IF (TG_OP = 'INSERT') THEN
        vret = save_changes(vid,vid_detall,1,TG_OP,'detall','id_factura',NULL,CAST(NEW.id_factura AS varchar),'_numeric',1700,true);
        vret = save_changes(vid,vid_detall,2,TG_OP,'detall','id_concepte',NULL,CAST(NEW.id_concepte AS varchar),'_numeric',1700,true);
        vret = save_changes(vid,vid_detall,3,TG_OP,'detall','preu',NULL,CAST(NEW.preu AS varchar),'_numeric',1700,false);
        vret = save_changes(vid,vid_detall,4,TG_OP,'detall','quantitat',NULL,CAST(NEW.quantitat AS varchar),'_numeric',1700,false);

    ELSIF (TG_OP = 'UPDATE') THEN
        vret = save_changes(vid,vid_detall,1,TG_OP,'detall','id_factura',CAST(OLD.id_factura AS varchar),CAST(NEW.id_factura AS varchar),'_numeric',1700,true);
        vret = save_changes(vid,vid_detall,2,TG_OP,'detall','id_concepte',CAST(OLD.id_concepte AS varchar),CAST(NEW.id_concepte AS varchar),'_numeric',1700,true);
        vret = save_changes(vid,vid_detall,3,TG_OP,'detall','preu',CAST(OLD.preu AS varchar),CAST(NEW.preu AS varchar),'_numeric',1700,false);
        vret = save_changes(vid,vid_detall,4,TG_OP,'detall','quantitat',CAST(OLD.quantitat AS varchar),CAST(NEW.quantitat AS varchar),'_numeric',1700,false);

    ELSIF (TG_OP = 'DELETE') THEN
        vret = save_changes(vid,vid_detall,1,TG_OP,'detall','id_factura',CAST(OLD.id_factura AS varchar),NULL,'_numeric',1700,true);
        vret = save_changes(vid,vid_detall,2,TG_OP,'detall','id_concepte',CAST(OLD.id_concepte AS varchar),NULL,'_numeric',1700,true);
        vret = save_changes(vid,vid_detall,3,TG_OP,'detall','preu',CAST(OLD.preu AS varchar),NULL,'_numeric',1700,false);
        vret = save_changes(vid,vid_detall,4,TG_OP,'detall','quantitat',CAST(OLD.quantitat AS varchar),NULL,'_numeric',1700,false);

END IF;
    RETURN NULL;
    end;
$audit_detall$ LANGUAGE plpgsql;

CREATE TRIGGER audit_detall after INSERT OR UPDATE or DELETE ON detall
    FOR EACH ROW EXECUTE PROCEDURE audit_detall();

Projecte Recovery

El projecte recovery és la interfície final que ens permet controlar i tirar endarrera les transaccions lògiques. Es presenta tres interfícies: com a aplicació VB, com a aplicació web ASP, i com a aplicació web PHP.

Hi ha dos modes: mode usuari i mode administrador

En el mode usuari:

  • només es pot desfer la última transacció.
  • No es mostren les transaccions 'ROLLBACK'.
  • En cas de desfer una TL (transacció lògica), aquesta ja no apareixerà en l'historial de transaccions, doncs haurà quedat marcada com a desfeta.

En el mode administrador:

  • Puc desfer una transacció ROLLBACK (desfaig una transacció que ja havia desfet)
  • puc desfer una TL que no és la última (compte perquè estic desfent un canvi en el passat i potser pot tenir implicacions difícils de preveure en el present)

-*es TL desfetes apareixen com a marcades, però apareixen, i les puc tornar a seleccionar.

VB: recovery.vbp i recovery.frm

Altarm vb.jpg 
Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim vmode As Integer '0: usuari; 1: administrador

Private Sub bt_mode_Click(Index As Integer)
Dim vestat As String

lst_transaccions.Clear
vmode = Index '0: usuari; 1: administrador

Select Case Index
Case 0 'usuari
    cadsql = "select * from transaccio where descripcio not like 'ROLLBACK TRANS%' and estat='o' order by id_trans desc"
    rs.Open cadsql, conn
    Do While Not rs.EOF
    lst_transaccions.AddItem rs("id_trans") & ". " & rs("descripcio")
    rs.MoveNext
    Loop
    rs.Close

Case 1 'administrador
    cadsql = "select * from transaccio order by id_trans desc"
    rs.Open cadsql, conn
    Do While Not rs.EOF
    vestat = ""
    If rs("estat") <> "o" Then vestat = "(" & rs("estat") & ") "
    lst_transaccions.AddItem vestat & rs("id_trans") & ". " & rs("descripcio")
    rs.MoveNext
    Loop
    rs.Close

End Select
End Sub

Private Sub bt_rollback_logic_Click()

Dim vid_trans As Integer
Dim bloc As Integer
Dim cad_pk As String
Dim cad_sql As String
Dim voldv As String
Dim desc_transaccio As String

If lst_transaccions.ListIndex > 0 And vmode = 0 Then
    MsgBox "En mode usuari, només es pot desfer la última transacció"
    Exit Sub
End If

If InStr(lst_transaccions.Text, "(") Then
    desc_transaccio = Right(lst_transaccions.Text, Len(lst_transaccions.Text) - 4)
Else
    desc_transaccio = lst_transaccions.Text
End If

vid_trans = CInt(Left(desc_transaccio, InStr(desc_transaccio, ".")))

nova_transaccio vid_trans

cadsql = "select * from transaccio_detall where id_trans=" & CStr(vid_trans) & " order by id_trans desc, id_trans_detall desc, ordre"
rs.Open cadsql, conn

bloc = 0
cad_pk = ""
cad_sql = ""
cad_sql2 = ""
cad_camps = ""
Do While Not rs.EOF
If bloc <> rs("id_trans_detall") Then 'canvi de bloc dins de la transacció
    If cad_sql <> "" Then
        'MsgBox cad_pk
        'MsgBox cad_sql2
        If InStr(cad_sql, "INSERT") > 0 Then cad_sql = cad_sql & cad_camps & ") VALUES(" & cad_sql2 & ")"
        If InStr(cad_sql, "UPDATE") > 0 Then cad_sql = cad_sql & cad_sql2 & cad_pk
        If InStr(cad_sql, "DELETE") > 0 Then cad_sql = cad_sql & cad_pk
        MsgBox cad_sql
        cmd.CommandText = cad_sql
        cmd.Execute
    End If
    bloc = rs("id_trans_detall")
    cad_pk = " WHERE "
    cad_sql2 = ""
    cad_camps = ""
    Select Case rs("tipus")
    Case "INSERT"
        cad_sql = "DELETE FROM " & rs("tname")
    Case "UPDATE"
        cad_sql = "UPDATE " & rs("tname") & " SET "
    Case "DELETE"
        cad_sql = "INSERT INTO " & rs("tname") & "("
    End Select

End If
If rs("es_pk") = True Then
    If cad_pk = " WHERE " Then
        If VarType(rs("newv")) <> 1 Then
            'cad_pk = cad_pk & rs("cname") & "=" & CStr(rs("newv"))
            cad_pk = cad_pk & rs("cname") & "=" & valor_antic(rs("cdatatype2"), CStr(rs("newv")))
        Else
            cad_pk = cad_pk & rs("cname") & "=NULL"
        End If
    Else
        If VarType(rs("newv")) <> 1 Then
            'cad_pk = cad_pk & " and " & rs("cname") & "=" & CStr(rs("newv"))
            cad_pk = cad_pk & rs("cname") & "=" & valor_antic(rs("cdatatype2"), CStr(rs("newv")))
        Else
            cad_pk = cad_pk & " and " & rs("cname") & "=NULL"
        End If
    End If
End If

Select Case rs("tipus")
Case "INSERT"
'
Case "UPDATE"
    
    If cad_sql2 <> "" Then cad_sql2 = cad_sql2 & ", "
    
    If VarType(rs("oldv")) <> 1 Or VarType(rs("newv")) <> 1 Then
    If VarType(rs("oldv")) <> 1 Then
        cad_sql2 = cad_sql2 & rs("cname") & "=" & valor_antic(rs("cdatatype2"), CStr(rs("oldv")))
    Else
        cad_sql2 = cad_sql2 & rs("cname") & "=NULL"
    End If
    End If
    
Case "DELETE"
    If cad_camps <> "" Then cad_camps = cad_camps & ", "
    If cad_sql2 <> "" Then cad_sql2 = cad_sql2 & ", "
    cad_camps = cad_camps & rs("cname")
    If VarType(rs("oldv")) <> 1 Then
        cad_sql2 = cad_sql2 & valor_antic(rs("cdatatype2"), CStr(rs("oldv")))
    End If
End Select

rs.MoveNext
Loop
rs.Close

    'hem sortit del bucle, falta desfer l'últim bloc
    If cad_sql <> "" Then
        'MsgBox cad_pk
        'MsgBox cad_sql2
        If InStr(cad_sql, "INSERT") > 0 Then cad_sql = cad_sql & cad_camps & ") VALUES(" & cad_sql2 & ")"
        If InStr(cad_sql, "UPDATE") > 0 Then cad_sql = cad_sql & cad_sql2 & cad_pk
        If InStr(cad_sql, "DELETE") > 0 Then cad_sql = cad_sql & cad_pk
        MsgBox cad_sql
        cmd.CommandText = cad_sql
        cmd.Execute
    End If
    
bt_mode_Click vmode

End Sub


Private Sub Form_Load()
obrir_connexio

End Sub

Private Sub Form_Unload(Cancel As Integer)
    tancar_connexio
End Sub
Sub obrir_connexio()
    'txt_cadena = "Provider=PostgreSQL.1;Password=postgres;User ID=postgres;Data Source=localhost;Location=postgres;Extended Properties="""""
    'Ens connectem a la BD
    Set conn = New ADODB.Connection
    conn.CursorLocation = adUseServer
    'conn.Open txt_cadena
    conn.Open "DSN=postgresql30"
    Set cmd = New ADODB.Command
    Set rs = New ADODB.Recordset
    rs.ActiveConnection = conn
    cmd.ActiveConnection = conn

End Sub
Sub tancar_connexio()
conn.Close
Set rs = Nothing
Set cmd = Nothing
Set conn = Nothing
End Sub

Private Sub nova_transaccio(vid_trans As Integer)
cmd.CommandText = "select nova_transaccio()"
cmd.Execute
rs.Open "select estat from TRANSACCIO where id_trans=" & CStr(vid_trans), conn
If rs("estat") = "o" Then
    cmd.CommandText = "update TRANSACCIO set estat='r' where id_trans=" & CStr(vid_trans)
    cmd.Execute
    cmd.CommandText = "insert into TRANSACCIO(id_trans,descripcio,dia_hora,usuari,login_pg,ip,estat) values(CURRVAL('seq_trans'),'ROLLBACK TRANS num " & CStr(vid_trans) & "',CURRENT_TIMESTAMP,'joan','sgbd','192.168.0.9','o')"
    cmd.Execute
Else
    cmd.CommandText = "update TRANSACCIO set estat='u' where id_trans=" & CStr(vid_trans)
    cmd.Execute
    cmd.CommandText = "insert into TRANSACCIO(id_trans,descripcio,dia_hora,usuari,login_pg,ip,estat) values(CURRVAL('seq_trans'),'ROLLBACK TRANS num " & CStr(vid_trans) & "',CURRENT_TIMESTAMP,'joan','sgbd','192.168.0.9','u')"
    cmd.Execute
End If
rs.Close

End Sub
Private Function valor_antic(vtipus As Integer, voldv As String)
        Select Case vtipus
        Case 16 '_bool
            If LCase(voldv) = "false" Then valor_antic = "false" Else valor_antic = "true"
        Case 18 '_char
            valor_antic = "'" & Replace(voldv, "'", "''") & "'"
        Case 21 '_int2 (smallint)
            valor_antic = voldv
        Case 23 '_int4
            valor_antic = voldv
        Case 25 '_text
            valor_antic = "'" & Replace(voldv, "'", "''") & "'"
        Case 701 '_float8
            valor_antic = voldv
        Case 1042 '_bpchar character(10)
            valor_antic = "'" & Replace(voldv, "'", "''") & "'"
        Case 1043 '_varchar
            valor_antic = "'" & Replace(voldv, "'", "''") & "'"
        Case 1082 '_date
            valor_antic = "'" & Replace(voldv, "'", "''") & "'" 'yyyy-mm-dd, hi ha diversos formats que funcionen
        Case 1700 '_numeric
            valor_antic = voldv
        End Select
End Function

Recovery amb ASP

Altarm asp.jpg

altarm_usuari.asp

<html>
<head>
<title>C8. Eines CASE i Entorns de 4a Generació</title>
<link href="fulla_estils.css" rel="stylesheet" type="text/css">
</head>
<body>
<h1>ALTARM - Audit Logical Transactions And Recover Mistakes</h1>
<h2>Versió usuari</h2>
<%
Set con=Server.CreateObject("ADODB.Connection")
con.Open "postgresql30"
Set rs=Server.CreateObject("ADODB.Recordset")
rs.Activeconnection=con
i=1
cadsql = "select * from transaccio where descripcio not like 'ROLLBACK TRANS%' and estat='o' order by id_trans desc"
rs.Open cadsql
Do While Not rs.EOF
	if i=1 then
		response.write "<a href=""altarm_recovery.asp?id_trans=" & rs("id_trans") & """>" & rs("id_trans") & "</a>. " & rs("descripcio") & "<br />"
	else
		response.write rs("id_trans") & "</a>. " & rs("descripcio") & "<br />"
	end if
	rs.MoveNext
	i=i+1
Loop
rs.Close
con.Close
%>
</body>
</html>

altarm_administrador.asp

<html>
<head>
<title>C8. Eines CASE i Entorns de 4a Generació</title>
<link href="fulla_estils.css" rel="stylesheet" type="text/css">
</head>
<body>
<h1>ALTARM - Audit Logical Transactions And Recover Mistakes</h1>
<h2>Versió administrador</h2>
<%
Set con=Server.CreateObject("ADODB.Connection")
con.Open "postgresql30"
Set rs=Server.CreateObject("ADODB.Recordset")
rs.Activeconnection=con
i=1
cadsql = "select * from transaccio order by id_trans desc"
rs.Open cadsql
Do While Not rs.EOF
	If rs("estat") <> "o" Then response.write "(" & rs("estat") & ") "
	response.write "<a href=""altarm_recovery.asp?id_trans=" & rs("id_trans") & """>" & rs("id_trans") & "</a>. " & rs("descripcio") & "<br />"
	rs.MoveNext
	i=i+1
Loop
rs.Close
con.Close
%>
</body>
</html>

altarm_recovery.asp

<html>
<head>
<title>C8. Eines CASE i Entorns de 4a Generació</title>
<link href="fulla_estils.css" rel="stylesheet" type="text/css">
</head>
<body>
<h1>ALTARM - Audit Logical Transactions And Recover Mistakes</h1>
<%
vid_trans=Request.QueryString("id_trans")

Set con=Server.CreateObject("ADODB.Connection")
con.Open "postgresql30"
Set cmd=Server.CreateObject("ADODB.Command")
Set rs=Server.CreateObject("ADODB.Recordset")
cmd.Activeconnection=con
rs.Activeconnection=con

nova_transaccio vid_trans

cadsql = "select * from transaccio_detall where id_trans=" & CStr(vid_trans) & " order by id_trans desc, id_trans_detall desc, ordre"
rs.Open cadsql

dim bloc,cad_pk,cad_sql,cad_sql2,cad_camps

bloc = 0
cad_pk = ""
cad_sql = ""
cad_sql2 = ""
cad_camps = ""
Do While Not rs.EOF

If bloc <> CInt(rs("id_trans_detall")) Then 'canvi de bloc dins de la transacció
	If cad_sql <> "" Then
		If InStr(cad_sql, "INSERT") > 0 Then cad_sql = cad_sql & cad_camps & ") VALUES(" & cad_sql2 & ")"
		If InStr(cad_sql, "UPDATE") > 0 Then cad_sql = cad_sql & cad_sql2 & cad_pk
		If InStr(cad_sql, "DELETE") > 0 Then cad_sql = cad_sql & cad_pk
		cmd.CommandText = cad_sql
		cmd.Execute
	End If
	bloc = CInt(rs("id_trans_detall"))
	cad_pk = " WHERE "
	cad_sql2 = ""
	cad_camps = ""
	Select Case rs("tipus")
	Case "INSERT"
		cad_sql = "DELETE FROM " & rs("tname")
	Case "UPDATE"
		cad_sql = "UPDATE " & rs("tname") & " SET "
	Case "DELETE"
		cad_sql = "INSERT INTO " & rs("tname") & "("
	End Select
End If

If rs("es_pk") = 1 Then 'True
	If cad_pk = " WHERE " Then
		If VarType(rs("newv")) <> 1 Then
			cad_pk = cad_pk & rs("cname") & "=" & valor_antic(rs("cdatatype2"), CStr(rs("newv")))
		Else
			cad_pk = cad_pk & rs("cname") & "=NULL"
		End If
	Else
		If VarType(rs("newv")) <> 1 Then
			cad_pk = cad_pk & rs("cname") & "=" & valor_antic(rs("cdatatype2"), CStr(rs("newv")))
		Else
			cad_pk = cad_pk & " and " & rs("cname") & "=NULL"
		End If
	End If
End If

Select Case rs("tipus")
Case "INSERT"
'
Case "UPDATE"
    If cad_sql2 <> "" Then cad_sql2 = cad_sql2 & ", "
    
	If VarType(rs("oldv")) <> 1 Or VarType(rs("newv")) <> 1 Then
		If VarType(rs("oldv")) <> 1 Then
			cad_sql2 = cad_sql2 & rs("cname") & "=" & valor_antic(rs("cdatatype2"), CStr(rs("oldv")))
		Else
			cad_sql2 = cad_sql2 & rs("cname") & "=NULL"
		End If
	End If
    
Case "DELETE"
	If cad_camps <> "" Then cad_camps = cad_camps & ", "
	If cad_sql2 <> "" Then cad_sql2 = cad_sql2 & ", "
	cad_camps = cad_camps & rs("cname")
	If VarType(rs("oldv")) <> 1 Then
		cad_sql2 = cad_sql2 & valor_antic(rs("cdatatype2"), CStr(rs("oldv")))
	End If
End Select

rs.MoveNext
Loop
rs.Close

    
'hem sortit del bucle, falta desfer l'últim bloc
If cad_sql <> "" Then
	If InStr(cad_sql, "INSERT") > 0 Then cad_sql = cad_sql & cad_camps & ") VALUES(" & cad_sql2 & ")"
	If InStr(cad_sql, "UPDATE") > 0 Then cad_sql = cad_sql & cad_sql2 & cad_pk
	If InStr(cad_sql, "DELETE") > 0 Then cad_sql = cad_sql & cad_pk
	cmd.CommandText = cad_sql
	cmd.Execute
End If
	
con.Close
Set con=Nothing
'response.write Request.ServerVariables ("SCRIPT_NAME")
response.redirect Request.ServerVariables ("HTTP_REFERER")
%>
</body>
</html>

<%
sub nova_transaccio(vid_trans)
	cmd.CommandText = "select nova_transaccio()"
	cmd.Execute
	rs.Open "select estat from TRANSACCIO where id_trans=" & CStr(vid_trans)
	If rs("estat") = "o" Then
	    cmd.CommandText = "update TRANSACCIO set estat='r' where id_trans=" & CStr(vid_trans)
		cmd.Execute
	    cmd.CommandText = "insert into TRANSACCIO(id_trans,descripcio,dia_hora,usuari,login_pg,ip,estat) values(CURRVAL('seq_trans'),'ROLLBACK TRANS num " & CStr(vid_trans) & "',CURRENT_TIMESTAMP,'joan','sgbd','192.168.0.9','o')"
		cmd.Execute
	Else
	    cmd.CommandText = "update TRANSACCIO set estat='u' where id_trans=" & CStr(vid_trans)
		cmd.Execute
	    cmd.CommandText = "insert into TRANSACCIO(id_trans,descripcio,dia_hora,usuari,login_pg,ip,estat) values(CURRVAL('seq_trans'),'ROLLBACK TRANS num " & CStr(vid_trans) & "',CURRENT_TIMESTAMP,'joan','sgbd','192.168.0.9','u')"
		cmd.Execute
	End If
	rs.Close
end sub
Function valor_antic(vtipus, voldv)
	Select Case CInt(vtipus)
    Case 16 '_bool
        If LCase(voldv) = "false" Then valor_antic = "false" Else valor_antic = "true"
    Case 18 '_char
        valor_antic = "'" & Replace(voldv, "'", "''") & "'"
    Case 21 '_int2 (smallint)
        valor_antic = voldv
    Case 23 '_int4
        valor_antic = voldv
    Case 25 '_text
        valor_antic = "'" & Replace(voldv, "'", "''") & "'"
    Case 701 '_float8
        valor_antic = voldv
    Case 1042 '_bpchar character(10)
        valor_antic = "'" & Replace(voldv, "'", "''") & "'"
    Case 1043 '_varchar
        valor_antic = "'" & Replace(voldv, "'", "''") & "'"
    Case 1082 '_date
        valor_antic = "'" & Replace(voldv, "'", "''") & "'" 'yyyy-mm-dd, hi ha diversos formats que funcionen
    Case 1700 '_numeric
        valor_antic = voldv
    End Select
End Function

%>

Recovery amb PHP

altarm_usuari.php

<html>
<head>
<title>C8. Eines CASE i Entorns de 4a Generació</title>
<link href="fulla_estils.css" rel="stylesheet" type="text/css">
</head>
<body>
<h1>ALTARM - Audit Logical Transactions And Recover Mistakes</h1>
<h2>Versió usuari</h2>

<?php
//$conn = pg_connect("host=192.168.0.9 dbname=dai user=sgbd password=sgbd");
$conn = pg_connect("host=localhost dbname=postgres user=postgres password=postgres");
$sql = "select * from transaccio where descripcio not like 'ROLLBACK TRANS%' and estat='o' order by id_trans desc";
$rs = pg_query($conn, $sql);
$i=1;

while($row = pg_fetch_assoc($rs)) {
	if ($i==1){
	echo "<a href=\"altarm_recovery.php?id_trans=".$row['id_trans']."\">".$row['id_trans']."</a> ".$row['descripcio']."<br />";
	} else {
	echo $row['id_trans']." ".$row['descripcio']."<br />";	
	}
	$i=$i+1;
}
pg_free_result($rs);

pg_close($conn);
?>

</body>
</html>

altarm_administrador.php

<html>
<head>
<title>C8. Eines CASE i Entorns de 4a Generació</title>
<link href="fulla_estils.css" rel="stylesheet" type="text/css">
</head>
<body>
<h1>ALTARM - Audit Logical Transactions And Recover Mistakes</h1>
<h2>Versió administrador</h2>

<?php
//$conn = pg_connect("host=192.168.0.9 dbname=dai user=sgbd password=sgbd");
$conn = pg_connect("host=localhost dbname=postgres user=postgres password=postgres");
$sql = "select * from transaccio order by id_trans desc";
$rs = pg_query($conn, $sql);
$i=1;

while($row = pg_fetch_assoc($rs)) {
	if ($row['estat'] <> "o") echo "(".$row['estat'].") ";
	echo "<a href=\"altarm_recovery.php?id_trans=".$row['id_trans']."\">".$row['id_trans']."</a> ".$row['descripcio']."<br />";
	$i=$i+1;
}
pg_free_result($rs);

pg_close($conn);
?>

</body>
</html>

altarm_recovery.php

<?php
$vid_trans=trim($_GET['id_trans']);
//$conn = pg_connect("host=192.168.0.9 dbname=dai user=sgbd password=sgbd");
$conn = pg_connect("host=localhost dbname=postgres user=postgres password=postgres");
$res=nova_transaccio($vid_trans);
$cadsql = "select * from transaccio_detall where id_trans=".$vid_trans." order by id_trans desc, id_trans_detall desc, ordre";
$rs = pg_query($conn, $cadsql);

$bloc = 0;
$cad_pk = "";
$cad_sql = "";
$cad_sql2 = "";
$cad_camps = "";

while($row = pg_fetch_assoc($rs)) {
	if ($bloc <> $row['id_trans_detall']) { //canvi de bloc dins de la transacció
		if ($cad_sql <> ""){
			if (strpos($cad_sql, "INSERT") === 0) $cad_sql = $cad_sql.$cad_camps.") VALUES(".$cad_sql2.")";
			if (strpos($cad_sql, "UPDATE") === 0) $cad_sql = $cad_sql.$cad_sql2.$cad_pk;
			if (strpos($cad_sql, "DELETE") === 0) $cad_sql = $cad_sql.$cad_pk;
			$rs = pg_query($conn, $cad_sql);
		}
		$bloc = $row['id_trans_detall'];
		$cad_pk = " WHERE ";
		$cad_sql2 = "";
		$cad_camps = "";
		
		switch($row['tipus']){
			case "INSERT":
			$cad_sql = "DELETE FROM ".$row['tname'];
			break;
			case "UPDATE":
			$cad_sql = "UPDATE ".$row['tname']." SET ";
			break;
			case "DELETE":
			$cad_sql = "INSERT INTO ".$row['tname']."(";
			break;
		}
	}

	if ($row['es_pk'] == "t"){ //True
		if ($cad_pk == " WHERE ") {
			if (pg_num_rows($rs)>0) {
				$cad_pk = $cad_pk.$row['cname']."=".valor_antic($row['cdatatype2'],$row['newv']);
			} else  {
				$cad_pk = $cad_pk.$row['cname']."=NULL";
			}
		} else {
			if (pg_num_rows($rs)>0) {
				$cad_pk = $cad_pk.$row['cname']."=".valor_antic($row['cdatatype2'],$row['newv']);
			} else  {
				$cad_pk = $cad_pk." and ".$row['cname']."=NULL";
			}		
		}
	}

	switch($row['tipus']){
		case "INSERT":
			break;
		case "UPDATE":
			if ($cad_sql2 <> "") $cad_sql2 = $cad_sql2.", ";
			if (isset($row['oldv']) || isset($row['newv'])){
				if (isset($row['oldv'])){
					$cad_sql2 = $cad_sql2.$row['cname']."=".valor_antic($row['cdatatype2'],$row['oldv']);
				} else {
					$cad_sql2 = $cad_sql2.$row['cname']."=NULL";
				}		
			}
			break;
		case "DELETE":
			if ($cad_camps <> "") $cad_camps = $cad_camps.", ";
			if ($cad_sql2 <> "") $cad_sql2 = $cad_sql2.", ";
			$cad_camps = $cad_camps.$row['cname'];
			if (isset($row['oldv'])){
				$cad_sql2 = $cad_sql2.valor_antic($row['cdatatype2'],$row['oldv']);
			}
			break;
	}
}


pg_free_result($rs);

//hem sortit del bucle, falta desfer l'últim bloc
if ($cad_sql <> "") {

	if (strpos($cad_sql, "INSERT") === 0) $cad_sql = $cad_sql.$cad_camps.") VALUES(".$cad_sql2.")";
	if (strpos($cad_sql, "UPDATE") === 0) $cad_sql = $cad_sql.$cad_sql2.$cad_pk;
	if (strpos($cad_sql, "DELETE") === 0) $cad_sql = $cad_sql.$cad_pk;
	//echo $cad_sql."<br />";
	$rs = pg_query($conn, $cad_sql);
}

pg_close($conn);
header("Location: ".$_SERVER["HTTP_REFERER"]);
//echo $_SERVER["REMOTE_ADDR"];  // sé la IP des de la qual es fa la consulta

?>

</body>
</html>

<?php
function nova_transaccio($vid_trans)
{
global $conn;
	$sql = "select nova_transaccio()";
    $rs = pg_query($conn, $sql);

	$sql = "select estat from TRANSACCIO where id_trans=".$vid_trans;
    $rs = pg_query($conn, $sql);
	$row = pg_fetch_assoc($rs);
	if ($row['estat']=="o"){
		$sql = "update TRANSACCIO set estat='r' where id_trans=".$vid_trans;
	    $rs = pg_query($conn, $sql);
		$sql = "insert into TRANSACCIO(id_trans,descripcio,dia_hora,usuari,login_pg,ip,estat) values(CURRVAL('seq_trans'),'ROLLBACK TRANS num ".$vid_trans."',CURRENT_TIMESTAMP,'joan','sgbd','192.168.0.9','o')";
	    $rs = pg_query($conn, $sql);
	} else {
		$sql = "update TRANSACCIO set estat='u' where id_trans=".$vid_trans;
	    $rs = pg_query($conn, $sql);
		$sql = "insert into TRANSACCIO(id_trans,descripcio,dia_hora,usuari,login_pg,ip,estat) values(CURRVAL('seq_trans'),'ROLLBACK TRANS num ".$vid_trans."',CURRENT_TIMESTAMP,'joan','sgbd','192.168.0.9','u')";
	    $rs = pg_query($conn, $sql);
	}

pg_free_result($rs);
return 0;
};


function valor_antic($vtipus,$voldv)
{
switch($vtipus){
	case 16: //_bool
		strtolower($voldv)=="false" ? $valor_antic = "false":$valor_antic = "true";
		break;
	case 18: //_char
		$valor_antic = "'".str_replace("'", "''",$voldv)."'";
		break;
	case 21: //_int2 (smallint)
		$valor_antic = $voldv;
		break;
	case 23: //_int4
		$valor_antic = $voldv;
		break;
	case 25: //_text
		$valor_antic = "'".str_replace("'", "''",$voldv)."'";
		break;
	case 701: //_float8
		$valor_antic = $voldv;
		break;
	case 1042: //_bpchar character(10)
		$valor_antic = "'".str_replace("'", "''",$voldv)."'";
		break;
	case 1043: //_varchar
		$valor_antic = "'".str_replace("'", "''",$voldv)."'";
		break;
	case 1082: //_date
		$valor_antic = "'".str_replace("'", "''",$voldv)."'"; //yyyy-mm-dd, hi ha diversos formats que funcionen
		break;
	case 1700: //_numeric
		$valor_antic = $voldv;
		break;
}  
return $valor_antic;
};
?>

creat per Joan Quintana Compte, abril 2009

Obtingut de «http://wiki.joanillo.org/index.php?title=ALTARM:_Windows_%2B_Postgres_%2B_VB_%2B_ASP&oldid=2357»