Título original:
Auditing FoxPro Table Add, Edit, and Delete EventsAutor:
Nancy FolsomTraducido 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
