domingo, 1 de junio de 2008

RECURSIVIDAD EN ACCESS (SQL + VBA)


Podríamos definir la recursividad, como la capacidad de una instrucción de código, para llamarse a si misma, en busca de resultados más detallados. En las bases de datos, a veces puede ser necesario emplear esta técnica para explorar estructuras jerárquicas (en árbol). Si crees que esto es algo muy raro y alejado de la cotidianeidad, es conveniente que sepas que el propio Access utiliza este tipo de estructura en las tablas del sistema (MSysObjects, MSysQueries, etc). Sin embargo, el lenguaje sql de Access no admite recursividad, es decir, desde una consulta no podemos llamar a la propia consulta.

Bien, pues en este artículo veremos cómo podemos explorar estructuras en árbol, empleando recursividad, o algo parecido ...

Antes que nada, puesto que he afirmado que las tablas del sistema de Access almacenan la información de forma jerárquica, vamos a comprobarlo : Abrimos la tabla MSysObjects y veremos, entre otros, los siguientes campos : Id, Name, ParentId, ... si investigamos un poco la relación que existe entre los valores del primer y el último campo, comprobaremos que por ejemplo : del registro cuyo valor en el campo Name es 'Tables', cuelgan todas las tablas y consultas de la base de datos (ParentId = Id).

¿ Y qué utilidad tiene disponer la información de esta forma ?, ¿ No seria más útil emplear el modelo relac¡onal ?. En primer lugar, las tablas del sistema (en Access), solo tienen un nivel de anidamiento, es decir, que cada registro 'Padre' solo puede tener un grupo de registros subordinados, truncándose a este nivel la descendencia, por tanto, al realizar una exploración, el coste del proceso es mínimo ya que sabemos de antemano que solo hay que explorar un nivel. Y por otra parte, al reunir todos los nombres de 'objetos' en un único campo : 'Name', mediante un solo índice compuesto por los campos 'Name' y 'ParentId' podemos controlar que no haya nombres repetidos para el mismo tipo de objetos, de forma mucho más rápida que si tuviéramos dicha información repartida en varias tablas. De hecho, si en la vista diseño de la tabla MSysObjects, abrimos la pantalla que nos muestra los índices de la tabla, veremos que este índice sobre los dos campos efectivamente existe.

Para facilitar la creación de ejemplos, emplearemos una sola tabla : 'Empleados' de Neptuno.mdb, ya que a pesar de tener muy pocos registros, oculta una estructura jerárquica. Si la abrimos en vista diseño, podremos comprobar que cada registro, además de su correspondiente campo 'Id', dispone de un campo 'Jefe', que corresponde a un 'Id' de otro registro.

Supongamos que la empresa 'Importadores Neptuno', necesita mejorar la comercialización de sus productos, y entre otras medidas, ha decidido que todo el personal de ventas debe realizar un curso de márketing. El gerente de la sociedad, así se lo ha comunicado al Sr. Andrew Fuller, vicepresidente comercial, y este a su vez, ha encargado a la Srta. Laura Callahan, Coordinador de ventas interno, la elaboración de un listado del personal adscrito a su departamento. Naturalmente la Srta Callahan está dispuesta a emplear la información de la tabla 'Empleados' para obtener el susodicho listado, sin embargo, rápidamente se ha percatado de una dificultad : el campo 'Jefe' de cada empleado, define el superior inmediato de cada uno de ellos, pero dicho superior, puede a su vez estar subordinado a uno o varios responsables, ¿ cómo asegurarse de ir siguiendo todas las líneas ascendentes hasta llegar al Sr Fuller, sin olvidarse a nadie ?.
Hace varios años, leí una aportación de Marius Puig, en el foro de Access y vba : http://www.mvp-access.com/foro/default.asp, sobre consultas recursivas. Lamentablemente, puesto que los mensajes con cierta antigüedad se van borrando, dudo mucho que se pueda encontrar el hilo original. Sin embargo, he aquí la técnica que él empleaba :

Crear una función (pública) en un módulo. Dicho procedimiento, se encargará de devolver una cadena delimitada por comas (csv) de todos los 'hijos' de un determinado nodo. La función la utilizaremos en la cláusula 'Where' de una consulta.
Estas son la función y la consulta :

'Marius Puig
Public Function AWniveles(Tabla, Hijo, Padre, Valor)
Dim Dato, strRst, rst As DAO.Recordset

    strRst = "select * from " & Tabla & " where " & Padre & "=" & Valor
    Set rst = CurrentDb.OpenRecordset(strRst)
    With rst
        Do While Not .EOF
            Dato = Dato & AWniveles(Tabla, Hijo, Padre, .Fields(Hijo))
            .MoveNext
        Loop
    End With

    AWniveles = Valor & ";" & Dato

End Function

SELECT Empleados.IdEmpleado AS IdJefe,
            Empleados.Nombre AS NombreJefe,
            Duplicado.IdEmpleado AS IdEmpleado,
            Duplicado.Nombre AS NombreEmpleado
FROM Empleados, Empleados AS Duplicado
WHERE InStr(AWniveles("Empleados", "IdEmpleado","Jefe", Empleados.IdEmpleado), Duplicado.IdEmpleado & ";")>0
            And
            Empleados.IdEmpleado <> Duplicado.IdEmpleado;

... ¡ Genial !, no solo va recorriendo todas las ramas del árbol, sino que además la función AWniveles se llama a sí misma de forma recursiva.
Para obtener 'limpios' los empleados que dependen del Sr. Fuller, tan solo tenemos que añadir este criterio a la consulta :
... And Empleados.IdEmpleado = 2 ...
Sin embargo, esta estructura tiene un inconveniente : es necesario generar más registros de los que devuelve la tabla original, por lo que en la consulta empleamos un 'Cross Join' (no relacionar las tablas), de la tabla consigo misma, lo que nos devuelve una cantidad de registros equivalente a elevar al cuadrado la cantidad de líneas de la tabla. En este caso, 'Empleados' tiene 9 registros por lo que la consulta, antes de filtrarla en la cláusula 'Where' devuelve 81 items. Naturalmente, a poco que 'abulte' la tabla implicada, el rendimiento decae rápidamente (una tabla de 1.000 registros, devuelve 1.000.000).

Una alternativa para evitar el 'Cross Join' : Emplear una tabla temporal para insertar recursivamente los registros :

'Ramon Poch, raipon. Terrassa a 24/05/2008
'El parámetro 'Sql_Orígen', puede ser el nombre de una tabla
'o consulta (emplear corchetes para delimitarlo si contiene
'caracteres extraños o espacios), o una instrucción sql, en
'cuyo caso, delimitaremos la instrucción sql entre paréntesis.
Sub InsertRecursivo(Padre As String, Hijo As String, Sql_Orígen As String)
On Error GoTo Err_InsertRecursivo
Dim qdf As DAO.QueryDef
Dim contador As Long
Dim control As Long: control = 1

    
'Creamos la tabla para guadar la información
    CurrentProject.Connection.Execute _
    "Create Table Recursiva " & _
    "(" & _
    "Nivel Integer, Id_Parent Long, Id_Fill Long " & _
    ")"

    '
Insertamos un primer nivel de exploración del árbol en la tabla temporal.
    CurrentDb.Execute _
    "Insert Into Recursiva "
& _
    "Select 0 As Nivel, Temp.["
& Hijo & "] As Id_Parent, tbl_origen.[" & Hijo & "] As Id_Fill " & _
    "From (Select * From " & Sql_Orígen & ") As Temp " & _
    "Inner Join (Select * From " & Sql_Orígen & ") As tbl_origen " & _
    "On Temp.[" & Hijo & "] = tbl_origen.[" & Padre & "]"

    Set qdf = CurrentDb.CreateQueryDef(
"", _
    "Parameters p_Nivel Long; " & _
    "Insert Into Recursiva " & _
    "Select p_Nivel + 1 As Nivel, Temp.Id_Parent, Recursiva.Id_Fill " & _
    "From Recursiva As Temp Inner Join Recursiva " & _
    "On Temp.Id_Fill = Recursiva.Id_Parent " & _
    "Where Recursiva.Nivel = P_Nivel")

    'Iniciamos un insert recursivo, filtrando su alcance a los registros 
    'agregados en la última acción. Cuando la propiedad RecordsAffected
    'sea igual a cero, abandonamos el bucle ...
    Do While control > 0
        qdf.Parameters("p_Nivel") = contador
        contador = contador + 1
        qdf.Execute
        control = qdf.RecordsAffected
    Loop

Exit_InsertRecursivo:
    Set qdf = Nothing
    Exit Sub

Err_InsertRecursivo:
    MsgBox Err.Description

    On Error Resume Next
    If DCount("*", "MSysObjects", "Name = 'Recursiva' And Type = 1") > 0 Then 
        CurrentProject.Connection.Execute _
        "Drop Table Recursiva"
    End If
    Resume Exit_InsertRecursivo

End
Sub

'Procedimiento de llamada
Sub llamada()
    InsertRecursivo "Jefe", "IdEmpleado", "Empleados"
End Sub

En este punto, si queremos recuperar la información de forma más legible, solo tenemos que realizar una consulta sobre los valores guardados en la tabla temporal : 'Recursiva'
 
Select (Select Nombre From Empleados Where IdEmpleado = Id_Parent) As Jefe,
          (Select Nombre From Empleados Where IdEmpleado = Id_Fill) As Empleado
From Recursiva

O mejor aún, puesto que no utilizaremos subconsultas :

Select E1.Nombre + ' ' + E1.Apellidos As Jefe,
          E2.Nombre + ' ' + E2.Apellidos As Empleado
From (
         Recursiva 
         Inner Join 
         Empleados As E1 On Recursiva.Id_Parent = E1.IdEmpleado
         )
         Inner Join 
         Empleados As E2 On Recursiva.Id_Fill = E2.IdEmpleado

El procedimiento 'InsertRecursivo' es verdaderamente rápido, permitiéndonos incluso obtener cientos de miles de registros en escasos segundos. La desventaja es que debemos controlar la posterior eliminación de la tabla temporal 'Recursiva', o si queremos mantenerla en la base de datos : vaciarla antes o después de cada uso.

Ramon Poch. Terrassa a 24/05/2008.