Database Professionals 2010. Pruebas Unitarias de Bases de datos y materiales de la charla.

El otro día tuve el placer de compartir una charla sobre Visual Studio 2010 organizada por Juan Carlos Gonzalez del CIIN con Rodrigo Corral e Ibon Landa. Os dejo los materiales de la charla y un pequeño artículo sobre las diferentes pruebas de calidad en Base de datos comentado durante la presentación. Realice tres ejemplos, basados en un procedimiento almacenado que actualizaba un registro en la tabla Employee de AdventureWorks2008. Su definición es la siguiente:

 
CREATE PROCEDURE [HumanResources].[uspUpdateEmployeePersonalInfo]
    @BusinessEntityID [int], 
    @NationalIDNumber [nvarchar](15), 
    @BirthDate [datetime], 
    @MaritalStatus [nchar](1), 
    @Gender [nchar](1)
WITH EXECUTE AS CALLER
AS
BEGIN
    SET NOCOUNT ON;
 
    BEGIN TRY
        UPDATE [HumanResources].[Employee] 
        SET [NationalIDNumber] = @NationalIDNumber 
            ,[BirthDate] = @BirthDate 
            ,[MaritalStatus] = @MaritalStatus 
            ,[Gender] = @Gender 
        WHERE [BusinessEntityID] = @BusinessEntityID;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

El ejemplo básico consistía simplemente en llamar al SP con unos datos ficticios que no realizaba ninguna actualización, esta prueba solo cubría que la llamada al SP se realizase de forma correcta.

El segundo ejemplo consistía en insertar un determinado registro antes de la prueba, llamar al SP para que realizase la actualización sobre ese registro y finalmente eliminar el registro de prueba insertado, esta prueba cubría que la llamada al SP se realizase de forma correcta y además permitía conocer si el sp funcionaba correctamente actualizando un registro de la BD.

El tercero y más interesante consistía en blindar el SP frente a cualquier cambio, uno de los problemas más habituales que sufrimos es que los SP, se suelen modificar a menudo y muchos de los fallos no detectados vienen derivados al cambiar las longitudes de los campos, el orden, tipo de dato y otros aspectos del procedimiento.

Para testar el SP, realice la consulta siguiente para averiguar la configuración completa procedimiento:

SELECT DISTINCT dbo.sysobjects.name, dbo.sysobjects.xtype AS type, dbo.syscolumns.name AS param, dbo.syscolumns.colorder AS corder, 
dbo.syscolumns.length, dbo.syscolumns.colstat AS keyc, dbo.syscolumns.isoutparam AS colisout, dbo.systypes.xtype, dbo.syscolumns.prec as precision, 
dbo.syscolumns.scale, dbo.syscolumns.isnullable, dbo.syscolumns.iscomputed, dbo.syscolumns.number 
FROM dbo.syscolumns 
INNER JOIN dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id 
INNER JOIN dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype 
WHERE (dbo.sysobjects.name = 'uspUpdateEmployeePersonalInfo') AND (dbo.systypes.status <> 1) 

image

Con los datos obtenidos podemos desarrollar una consulta que utilize los valores mostrados para comprobar que el procedimiento almacenado tiene la configuración mostrada por la consulta.

SELECT COUNT(DISTINCT dbo.syscolumns.name) 
FROM dbo.syscolumns 
INNER JOIN dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id 
INNER JOIN dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype 
WHERE (dbo.sysobjects.name = 'uspUpdateEmployeePersonalInfo') AND (dbo.systypes.status <> 1) 
AND ((dbo.syscolumns.name = '@BirthDate' AND dbo.syscolumns.colorder = 3 AND dbo.systypes.xtype = 61 AND dbo.syscolumns.length = 8 and dbo.syscolumns.prec = 23 and dbo.syscolumns.scale = 3)
OR (dbo.syscolumns.name = '@BusinessEntityID' AND dbo.syscolumns.colorder = 1 AND dbo.systypes.xtype = 56 AND dbo.syscolumns.length = 4 and dbo.syscolumns.prec = 10 and dbo.syscolumns.scale = 0)
OR (dbo.syscolumns.name = '@Gender' AND dbo.syscolumns.colorder = 5 AND dbo.systypes.xtype = 239 AND dbo.syscolumns.length = 2)
OR (dbo.syscolumns.name = '@MaritalStatus' AND dbo.syscolumns.colorder = 4 AND dbo.systypes.xtype = 239 AND dbo.syscolumns.length = 2)
OR (dbo.syscolumns.name = '@NationalIDNumber' AND dbo.syscolumns.colorder = 2 AND dbo.systypes.xtype = 231 AND dbo.syscolumns.length = 30))

La consulta devuelve (5), el numero de campos del procedimiento uspUpdateEmployeePersonalInfo que cumplen las condiciones especificadas. Si el valor retornado por la consulta no es 5, supondra que hemos alterado algun aspecto de configuración del procedimiento almacenado, longitud, tipo de valor, orden del campo en el store procedure, etc.

La prueba completa quedaría de esta forma:

PreTest

/* Desabilita el trigger que impide el borrado de registro en la tabla .[HumanResources].[Employee] */
DISABLE Trigger [HumanResources].[dEmployee] ON [HumanResources].[Employee] 
 
/* Borra el registro de pruebas si por alguna razon estuviera ya insertado */
DELETE [AdventureWorks2008].[HumanResources].[Employee]
WHERE [BusinessEntityID] = 2292
 
/* Realiza la inserción del registro de pruebas, hay que analizar los datos de la insercción ya que hay campos que tienen restricciones a nivel de tabla
como MartitalStatus que slo acepta M (Male)  F (Female) */
 
INSERT INTO [AdventureWorks2008].[HumanResources].[Employee]
           ([BusinessEntityID]
           ,[NationalIDNumber]
           ,[LoginID]
           ,[OrganizationNode]
           ,[JobTitle]
           ,[BirthDate]
           ,[MaritalStatus]
           ,[Gender]
           ,[HireDate]
           ,[SalariedFlag]
           ,[VacationHours]
           ,[SickLeaveHours]
           ,[CurrentFlag]
           ,[rowguid]
           ,[ModifiedDate])
     VALUES (2292,'1231231398','adventure-workslyxnr',0x95EF,'Sales Representative',cast('1965-10-31' as date),'M','F',cast('1996-10-31' as date),1,34,37,1,newid(),getdate())
 
/* Comprueba que el registro de pruebas ha sido insertado correctamente */
SELECT [BusinessEntityID] FROM [AdventureWorks2008].[HumanResources].[Employee] WHERE [BusinessEntityID] = 2292 

Test

DECLARE @return_value int
 
EXEC    @return_value = [HumanResources].[uspUpdateEmployeePersonalInfo]
        @BusinessEntityID = 2292,
        @NationalIDNumber = N'13412343ZN',
        @BirthDate = N'01/01/1934',
        @MaritalStatus = N'S',
        @Gender = N'F'
 
SELECT @return_value, COUNT(DISTINCT dbo.syscolumns.name) 
FROM dbo.syscolumns 
INNER JOIN dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id 
INNER JOIN dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype 
WHERE (dbo.sysobjects.name = 'uspUpdateEmployeePersonalInfo') AND (dbo.systypes.status <> 1) 
AND ((dbo.syscolumns.name = '@BirthDate' AND dbo.syscolumns.colorder = 3 AND dbo.systypes.xtype = 61 AND dbo.syscolumns.length = 8 and dbo.syscolumns.prec = 23 and dbo.syscolumns.scale = 3)
OR (dbo.syscolumns.name = '@BusinessEntityID' AND dbo.syscolumns.colorder = 1 AND dbo.systypes.xtype = 56 AND dbo.syscolumns.length = 4 and dbo.syscolumns.prec = 10 and dbo.syscolumns.scale = 0)
OR (dbo.syscolumns.name = '@Gender' AND dbo.syscolumns.colorder = 5 AND dbo.systypes.xtype = 239 AND dbo.syscolumns.length = 2)
OR (dbo.syscolumns.name = '@MaritalStatus' AND dbo.syscolumns.colorder = 4 AND dbo.systypes.xtype = 239 AND dbo.syscolumns.length = 2)
OR (dbo.syscolumns.name = '@NationalIDNumber' AND dbo.syscolumns.colorder = 2 AND dbo.systypes.xtype = 231 AND dbo.syscolumns.length = 30))

De esta forma la condición de prueba del segundo valor de la consulta sera 5 (Numero campos que cumplen las condiciones especificadas), si en cualquier momento el valor no es 5, supondra que hemos alterado algun valor de configuración del procedimiento almacenado, longitud, tipo de valor, orden del campo en el store procedure, etc.

PostTest

/* Elimina el registro de pruebas insertado */
DELETE [AdventureWorks2008].[HumanResources].[Employee]
WHERE [BusinessEntityID] = 2292
 
/* Habilita el trigger, hay que realizarlo sobre la clausula EXEC porque la prueba no acepta 'GO', despues del borrado */
EXEC('ENABLE TRIGGER [HumanResources].[dEmployee] ON [HumanResources].[Employee]')

Comprobamos que al alterar la longitud de un campo la prueba fallaba, mientras que las dos primeras pasaban sin problemas. Se garantiza el correcto funcionamiento del SP, ya que inserta un registro de pruebas, testea el SP eliminando el registro de pruebas al finalizar y además blinda este sobre cualquier cambio en la configuración del SP.

Quiero agradecer a los asistentes su presencia, a Juan Carlos Gonzalez por la organización y porque gracias a el tenemos un evento cada poco tiempo en Santander y como no, a mis compañeros Rodrigo Corral e Ibon Landa también conocidos como Grupo Pimpinela, (haber si adivináis ¿quién es quién?…), que animaron la presentación con sus charlas y desparpajo, debido a la emoción los primeros minutos sufrí un pequeño colapso, me gusto especialmente la última parte, pude recuperar el video que hizo uno de los presentes, solo recordalo me emociono… 🙂

Adjunto la PPT de la charla:

Presentación en formato Powerpoint 2003.

Presentación en formato Powerpoint 2007.

Presentación en Adobe Acrobat PDF.