Auditando eventos de inserción, modificación y eliminación en tablas de FoxPro

Título original: Auditing FoxPro Table Add, Edit, and Delete Events
Autor: Nancy Folsom
Traducido por: Luis María Guayán

---

Hace casi un año escribí un artículo que mostró cómo crear y utilizar una herramienta sencilla para registrar los cambios de estructura a las vistas locales y a las tablas. Este mes escribo sobre una herramienta sencilla que audite los cambios del usuario (inserciones, actualizaciones y eliminaciones) a las tablas de FoxPro usando desencadenantes (triggers) de la tabla. Antes de zambullirse en el ejemplo, vale la pena la observación de que, en mi caso, no utilizo desencadenantes de relaciones de integridad (RI) de FoxPro. Si usted lo hace, entonces necesitará considerar cómo envolvería el código automáticamente generado por RI dentro del código de auditoria. También confío en el hecho de que mis tablas auditadas están en un contenedor de base de datos.

Mantengo un sistema que ahora requiere un seguimiento a los cambios a una tabla. Puesto que el sistema utiliza las tablas contenidas en un DBC, los desencadenantes y los procedimientos almacenados son herramientas naturales (los procedimientos almacenados son sólo código de programas almacenados en el contenedor de base de datos). Otro buen lugar para colocar la implementación de la auditoria, es a través de la capa de datos. No elegí esta opción puesto que estos datos se pueden acceder por lo menos de dos interfaces (front ends) distintas, una Web y una aplicación de escritorio, y porque yo estoy utilizando procedimientos almacenados en vez de servicios Web.

Si usted no ha utilizado desencadenantes de tabla antes, estos son los eventos que se pueden disparar para las tablas contenidas en una DBC siempre que ocurra un INSERT (o APPEND), un UPDATE (o TableUpdate), o DELETE. Hay algunas limitaciones de lo que usted puede hacer en los desencadenantes. Por ejemplo, no intente cambiar ningún campo en la tabla que ha generado el desencadenante. Observe también que las tablas libres no pueden tener desencadenantes, así que usted tendría que utilizar sus clases de datos (o de negocio) para auditar.

Aunque mi cliente requiere solamente la auditoria para una tabla (por lo menos hoy), he pensado con frecuencia acerca de escribir (algún día) una rutina de auditoria que pueda habilitar y deshabilitar si deseo hacer un seguimiento de uso o un cierto problema. Ésta era la oportunidad perfecta de escribirla. Usando desencadenantes de DBC, puedo habilitar y deshabilitar la auditoria usando los comandos CREATE TRIGGER y DELETE TRIGGER.

Este artículo viene con el código de los programas AuditTableStoredProc.PRG que es el código debe agregar a los procedimientos almacenados de cualquier DBC. El otro programa, AuditTrailExample.PRG que instala un ejemplo, así usted puede ver cómo éste trabaja. Para utilizarlos, cree los archivos PRGs (con los nombres mencionados) en cualquier carpeta vacía y ejecute:

DO AuditTrailExample 

desde la ventana de comandos.

AuditTrailExample.PRG

CLOSE DATABASES ALL
* Creo la tabla libre que auditará las acciones
* de inserción, modificación y eliminación
CreateAuditStorage()
* Creo algunos datos de ejemplo
CreateExampleData()
* Ahora que configuramos, ponemos algunos datos.
INSERT INTO GuineaPigTbl (;
  cSomeField) VALUES ( "Dark chocolate" )
INSERT INTO GuineaPigTbl (;
  cSomeField) VALUES ( "Milk chocolate" )
INSERT INTO GuineaPigTbl (;
  cSomeField) VALUES ( "White chocolate" )
INSERT INTO GuineaPigTbl (;
  cSomeField) VALUES ( "Truffles" )
INSERT INTO GuineaPigTbl (;
  cSomeField) VALUES ( "Hot chocolate" )
GO 2
REPLACE cSomeField WITH "Chocolate milk"
GO BOTTOM
DELETE
DELETE TRIGGER ON GuineaPigTbl FOR DELETE
USE IN GuineaPigTbl
USE TableAudit
BROWSE NOWAIT
RETURN .T.

**************************************************
* Function CreateAuditStorage()
* Este es un PRG de instalación como propósito de
* este artículo. Esto crea la tabla que almacenará
* el seguimiento de la auditoria
**************************************************
FUNCTION CreateAuditStorage()
  *
  * Elimino los datos de ejemplos anteriores
  *
  IF FILE(FULLPATH('TableAudit.DBF'))
    DELETE FILE FULLPATH('TableAudit.DBF') Recycle
  ENDIF
  *
  * Creo una tabla libre para almacenar
  * los datos auditados...
  *
  CREATE TABLE TableAudit FREE (;
    AuditId I NOT NULL AUTOINC NEXTVALUE 115 STEP 1, ;
    Entity C(64) NOT NULL, ;
    Action C(16) NOT NULL, ;
    SOURCE M NOT NULL, ;
    OldValues M NOT NULL, ;
    NewValues M NOT NULL, ;
    cUser C(64) NOT NULL, ;
    Pk C(16) NOT NULL, ;
    PkValue C(16) NOT NULL, ;
    TIMESTAMP T NOT NULL)
  *
  * Creo los índices...
  *
  INDEX ON AuditId TAG AuditId CANDIDATE
  *...
ENDFUNC

**************************************************
* Function CreateExampleData()
* Este es un PRG de instalación como propósito de
* este artículo. Esto crea una base de datos y
* tabla de ejemplo
**************************************************
FUNCTION CreateExampleData()
  * Elimino los datos de ejemplos anteriores
  IF FILE(FULLPATH('GuineaPigDBC.DBC'))
    DELETE FILE FULLPATH('GuineaPigDBC.DBC') Recycle
  ENDIF
  IF FILE(FULLPATH('GuineaPigTbl.DBF'))
    DELETE FILE FULLPATH('GuineaPigTbl.DBF') Recycle
  ENDIF
  * Creo una DBC y tabla en la cual pueda hacer
  * inserciones, modificaciones y eliminaciones
  CREATE DATABASE GuineaPigDBC
  SET DATABASE TO GuineaPigDBC
  CREATE TABLE GuineaPigTbl ;
    (iID I AUTOINC, cSomeField C(32), PRIMARY KEY iID )
  * Creo el procedimiento almacenado en la
  * base de datos GuineaPigDBC
  IF CreateStoredProc()
    * Creo un desencadenante para la tabla GuiniaPigTbl
    * usando nuestro procedimiento almacenado
    CREATE TRIGGER ON GuineaPigTbl FOR INSERT AS ;
      AuditTable()
    CREATE TRIGGER ON GuineaPigTbl FOR DELETE AS ;
      AuditTable()
    CREATE TRIGGER ON GuineaPigTbl FOR UPDATE AS ;
      AuditTable()
  ENDIF
ENDFUNC

**************************************************
* Function CreateStoredProc()
* Este es un PRG de instalación como propósito de
* este artículo. Esto agrega el procedimiento almacenado
* de auditoria a la base de datos GuineaPigDBC.
* Puede tambien simplemente cortar y pegar el código
* desde el PRG AuditTableStoredProc.Prg que está
* incluido en este artículo.
**************************************************
FUNCTION CreateStoredProc()
  IF EMPTY(DBC())
    MESSAGEBOX("Rats! I was expecting a Dbc()!")
    RETURN .F.
  ENDIF
  APPEND PROCEDURES FROM AuditTableStoredProc.Prg
  RETURN .T.
ENDFUNC

**************************************************

AuditTableStoredProc.PRG

**************************************************
* Comienzo del código de seguimiento de la auditoria
* Seguimiento de la auditoria para uso general de
* cualquier tabla (DBC)
**************************************************
FUNCTION AuditTable()
  LOCAL ;
    lcOldSetDbcTo, lcFldState,lcAlias, ;
    lcSource, lcAction, lcUser, lcPk, ;
    lcPkValue, lcNew, lcOld
  STORE "" TO lcOld, lcNew
  *
  * La DBC de la tabla debe ser la actual para DbGetProp()
  *
  lcOldSetDbcTo = SET("Database")
  IF SetDBC() && Esto no trabaja para tablas libres
    *
    * Inicializo variables que dependen de la
    * tabla seleccionada con ALIAS()
    *
    lcFldState = NVL( GETFLDSTATE(-1), "" )
    lcUser = SYS(0) && Quien lo hace...
    lcAction = GetAction( lcFldState ) && Que hace...
    lcAlias = ALIAS() && A quien...
    lcSource = GetCursorSource()
    *
    * Almaceno la clave primaria y el valor
    *
    lcPk = DBGETPROP( lcAlias, "Table", "PrimaryKey" )
    lcPkValue = FieldNameToStringValue( lcPk )
    * Colecciono los valores de los campos que han cambiado
    * GetValuesAsString( lcFldState, @lcOld, @lcNew )
    ValuesToString(lcFldState, @lcOld, @lcNew)
    *
    * Creo el registro de auditoria
    *
    INSERT INTO TableAudit (;
      Entity, Action, NewValues, OldValues, cUser, ;
      Pk, PkValue, SOURCE, TIMESTAMP ) VALUES ( ;
      lcAlias, lcAction, lcNew, lcOld, lcUser, ;
      lcPk, lcPkValue, lcSource, DATETIME() )
    *
    * Limpio
    *
    USE IN SELECT('TableAudit')
    SET DATABASE TO (lcOldSetDbcTo)
    SELECT SELECT(lcAlias)
  ENDIF
  RETURN .T.
ENDFUNC

**************************************************
* Function ValuesToString( tcFldState, tcOld, tcNew )
* Pasar tcOld y tcNew por referencia.
* Por cada campo en el registro, verifico si ha cambiado.
* Si cambio, concateno los valores de los campos
* a los parametros tcOld y tcNew pasados.
**************************************************
FUNCTION ValuesToString( tcFldState, tcOld, tcNew )
  LOCAL lcFldState, lvOld, lvNew, lni, lcField
  *
  * Quito la bandera de eliminación de la cadena GetFldState
  *
  lcFldState = SUBSTR( tcFldState, 2 )
  tcOld = ""
  tcNew = ""
  FOR lni = 1 TO LEN( lcFldState )
    IF INLIST( SUBSTR(lcFldState, lni, 1), "2", "4" )
      *
      * Algo ha cambiado
      *
      lcField = FIELD(lni)
      lvOld = OLDVAL( lcField )
      lvNew = EVALUATE( lcField )
      *
      * Almaceno los valores anteriores
      *
      IF !ISNULL( lvOld ) && Ignore if .null.
        tcOld = tcOld + ;
          lcField + " = " + Stringify( lvOld ) + CHR(13)
      ENDIF
      *
      * Almaceno los nuevos valores
      *
      IF !ISNULL( lvNew ) && Ignoro si es nulo.
        tcNew = tcNew + ;
          lcField + " = " + Stringify( lvNew ) + CHR(13)
      ENDIF
    ENDIF && Si el registro ha cambiado
  ENDFOR && Por cada campo en el registro
  RETURN .T.
ENDFUNC

**************************************************
* Function Stringify(tvValue)
* Tomo un tipo de valor y lo convierto a un valor
* pueda ser evaluado nuevamente a su tipo original.
**************************************************
*
* Definiciones para las partes de fechas
*
#DEFINE sYear STR( YEAR( tvValue ), 4, 0)
#DEFINE sMonth STR( MONTH( tvValue ), 2, 0)
#DEFINE sDay STR( DAY( tvValue ), 2, 0)
#DEFINE sHour STR( HOUR( tvValue ), 2, 0)
#DEFINE sMinute STR( MINUTE(tvValue ), 2, 0)
#DEFINE sSecond STR( SEC( tvValue ), 2, 0)
*
FUNCTION Stringify(tvValue)
  *
  LOCAL lcType, lcReturn
  lcType = VARTYPE(tvValue)
  DO CASE
    CASE lcType = "Y" && Currency
      lcReturn = ALLTRIM( STR( tvValue, 12, 4 ) )
    CASE lcType = "C" && Character
      lcReturn = "[" + ALLTRIM(tvValue) + "]"
    CASE lcType = "D" && Date
      lcReturn = "Date"
      * Set("Mark") no es soportado en el proveedor OLEDB
      * lcMark = Set("Mark") && Date separator
      lcMark = "/"
      lcReturn = "{^" + ;
        sYear + lcMark + ;
        sMonth + lcMark + ;
        sDay + "}"
    CASE lcType = "T" && DateTime
      * Set("Mark") no es soportado en el proveedor OLEDB
      * lcMark = Set("Mark") && Date separator
      lcMark = "/"
      lcReturn = "{^" + ;
        sYear + lcMark + ;
        sMonth + lcMark + ;
        sDay + " " + ;
        sHour + ":" + ;
        sMinute + ":" + ;
        sSecond + "}"
    OTHERWISE
      lcReturn = ALLTRIM( TRANSFORM(tvValue) )
  ENDCASE
  RETURN lcReturn
ENDFUNC

**************************************************
* Function GetAction
* ¿Se añadio el registro insertado, modificado o eliminado?
**************************************************
FUNCTION GetAction( tcFldState)
  LOCAL lcReturn
  DO CASE
    CASE EMPTY( CHRTRAN( tcFldState, "1", "" ) )
      lcReturn = ""
    CASE LEFT( tcFldState, 1 ) = "2"
      lcReturn = IIF( DELETED(), "Deleted", "Recalled" )
    CASE AT( "2", tcFldState ) > 0
      lcReturn = "Updated"
    CASE !EMPTY( CHRTRAN( tcFldState, "12", "" ) )
      lcReturn = "Inserted"
    OTHERWISE
      lcReturn = tcFldState
  ENDCASE
  RETURN lcReturn
ENDFUNC

**************************************************
* Function GetCursorSource()
* Retorna la(s) tabla(s) subyacente para ALIAS()
**************************************************
FUNCTION GetCursorSource()
  LOCAL lcReturn, lni
  lni = CURSORGETPROP("SourceType")
  DO CASE
    CASE lni = 1 && Vista Local
      lcReturn = CURSORGETPROP("Tables")
    CASE lni = 3 && Tabla VFP
      lcReturn = FULLPATH( DBF() )
    OTHERWISE
      lcReturn = ""
  ENDCASE
  RETURN lcReturn
ENDFUNC

**************************************************
* Function FieldNameToStringValue( tcField )
* Toma el nombre de campo y retorna el valor del
* campo como una cadena
**************************************************
FUNCTION FieldNameToStringValue( tcField )
  LOCAL lcField
  lcField = FIELD( tcField)
  IF EMPTY( lcField )
    RETURN ""
  ENDIF
  RETURN Stringify( EVALUATE( lcField ) )
ENDFUNC

**************************************************
* Function SetDBC()
**************************************************
FUNCTION SetDBC()
  LOCAL lcDBC
  lcDBC = CURSORGETPROP("Database")
  IF EMPTY(lcDBC)
    RETURN .F. && Esto no trabaja para tablas libres
  ENDIF
  SET DATABASE TO (lcDBC)
  RETURN .T.
ENDFUNC

**************************************************
* Final del código de seguimiento de la auditoria
**************************************************

Notará que el procedimiento almacenado del seguimiento de la auditoria incluye varias funciones. Éstas son las funciones de economía doméstica que procesan la información que va a ser auditada. No entraré en detalle para ambos códigos mostrados aquí. Todo el código está comentado, sin embargo, siéntase libre de probarlo. Si tiene cualquier pregunta, puede enviarme un correo electrónico a: nfolsomNOSPAM@NOSPAMpixeldustindustries.com

Comencé por decidir que quería una función que auditara cualquier desencadenante. Si necesito una auditoria especializada para alguna tabla o vista, cambiaré simplemente los desencadenadores para las tablas afectadas. También pretendí almacenar los viejos y nuevos valores pero solamente para los campos modificados. Los campos se guardan como expresiones que se puedan evaluar nuevamente a sus tipos de campo y valores originales.

La auditaría del usuario que está realizando los cambios es también útil. En esta implementación, obviamente sencilla, estoy utilizando SYS(0) que retorna el nombre de la máquina. Esto identifica que el cambio vino del sitio Web (donde un usuario tiene que ser registrado) o la estación de trabajo, si el cambio se realiza desde la interfaz de escritorio. Auditando la llave primaria del registro modificado, puedo encontrar la cuenta de los usuarios Web. Sin embargo, éste no es un sistema financiero que necesita un fuerte seguimiento de la actividad del usuario.

Mi siguiente paso en esto será una prueba con tablas muy grandes y con un gran seguimiento de auditoria. También agregaré una opción para que los usuarios limpien la tabla de seguimientos de auditoria periódicamente.

Por ahora, es solo esto. Espero que lo encuentre útil. Lo invito a contacterme por correo electrónico a nfolsomNOSPAM@NOSPAMpixeldustindustries.com con cualquier comentario, pregunta o crítica. Sus comentarios serán bienvenidos.

Nancy Folsom