sql - recursivas - jerarquicas

clonando datos jerárquicos

Question

supongamos que tengo una tabla jerárquica de referencia automática construida de la manera clásica como esta:

CREATE TABLE test
(name text,id serial primary key,parent_id integer
references test);

insert into test (name,id,parent_id) values
(''root1'',1,NULL),(''root2'',2,NULL),(''root1sub1'',3,1),(''root1sub2'',4,1),(''root
2sub1'',5,2),(''root2sub2'',6,2);

testdb=# select * from test;

   name    | id | parent_id
-----------+----+-----------
 root1     |  1 |  
 root2     |  2 |  
 root1sub1 |  3 |         1
 root1sub2 |  4 |         1
 root2sub1 |  5 |         2
 root2sub2 |  6 |         2

Lo que necesito ahora es una función (preferiblemente en sql simple) que tomaría la identificación de un registro de prueba y clonaría todos los registros adjuntos (incluido el dado). Los registros clonados deben tener nuevos identificadores, por supuesto. El resultado deseado le gustaría, por ejemplo:

Select * from cloningfunction(2);

   name    | id | parent_id    
-----------+----+-----------
 root2     |  7 |  
 root2sub1 |  8 |         7
 root2sub2 |  9 |         7

¿Alguna sugerencia? Estoy usando PostgreSQL 8.3.

Answer 1

Esto suena como un ejercicio de "SQL For Smarties" de Joe Celko ...

No tengo mi copia a mano, pero creo que es un libro que te ayudará bastante si este es el tipo de problemas que necesitas resolver.

Answer 2

Tratar este resultado recursivamente es complicado (aunque posible). Sin embargo, generalmente no es muy eficiente y hay una forma mucho mejor de resolver este problema.

Básicamente, aumentas la tabla con una columna extra que rastrea el árbol hasta la parte superior. Lo llamaré "Upchain". Es solo una cadena larga que se ve así:

name | id | parent_id | upchain
root1 | 1 | NULL | 1:
root2 | 2 | NULL | 2:
root1sub1 | 3 | 1 | 1:3:
root1sub2 | 4 | 1 | 1:4:
root2sub1 | 5 | 2 | 2:5:
root2sub2 | 6 | 2 | 2:6:
root1sub1sub1 | 7 | 3 | 1:3:7:

Es muy fácil mantener este campo actualizado utilizando un disparador sobre la mesa. (Disculpas por la terminología, pero siempre he hecho esto con SQL Server). Cada vez que agrega o elimina un registro, o actualiza el campo parent_id, solo necesita actualizar el campo upchain en esa parte del árbol. Es un trabajo trivial porque simplemente toma la cadena ascendente del registro principal y agrega la identificación del registro actual. Todos los registros secundarios se identifican fácilmente utilizando LIKE para verificar los registros con la cadena de inicio en su cadena ascendente.

Lo que está haciendo de manera efectiva es cambiar un poco de actividad de escritura adicional para obtener un gran ahorro cuando llegue a leer los datos.

Cuando quiere seleccionar una rama completa en el árbol, es trivial. Supongamos que quiere la rama bajo el nodo 1. El nodo 1 tiene una cadena ascendente ''1:'' por lo que sabe que cualquier nodo en la rama del árbol debajo de ese nodo debe tener una cadena ascendente que comience ''1: ...''. Entonces haces esto:

SELECT *
FROM table
WHERE upchain LIKE ''1:%''

Esto es extremadamente rápido (indice el campo upchain por supuesto). Como beneficio adicional, también hace que muchas actividades sean extremadamente simples, como encontrar árboles parciales, nivelar dentro del árbol, etc.

Lo he usado en aplicaciones que rastrean grandes jerarquías de informes de empleados, pero puedes usarlo para casi cualquier estructura de árbol (desglose de partes, etc.)

Notas (para cualquiera que esté interesado):

• No he dado un paso a paso del código SQL, pero una vez que obtiene el principio, es bastante simple de implementar. No soy un gran programador, así que hablo por experiencia.
• Si ya tiene datos en la tabla, necesita hacer una actualización única para sincronizar inicialmente las cadenas ascendentes. Nuevamente, esto no es difícil ya que el código es muy similar al código de ACTUALIZACIÓN en los desencadenadores.
• Esta técnica también es una buena manera de identificar referencias circulares que, de otro modo, podrían ser difíciles de detectar.

Answer 3

El método de Joe Celko que es similar a la respuesta de Njreed pero es más genérico se puede encontrar aquí:

• Conjunto de árboles anidados de árboles (en el medio del artículo)
• Conjunto de árboles anidados de árboles, parte 2
• Árboles en SQL - Parte III

Answer 4

@Maximilian : Tiene razón, olvidamos su requisito real. ¿Qué tal un procedimiento almacenado recursivo? No estoy seguro de si esto es posible en PostgreSQL, pero aquí está una versión de SQL Server en funcionamiento:

CREATE PROCEDURE CloneNode
    @to_clone_id int, @parent_id int
AS
    SET NOCOUNT ON
    DECLARE @new_node_id int, @child_id int

    INSERT INTO test (name, parent_id) 
        SELECT name, @parent_id FROM test WHERE id = @to_clone_id
    SET @new_node_id = @@IDENTITY

    DECLARE @children_cursor CURSOR
    SET @children_cursor = CURSOR FOR 
        SELECT id FROM test WHERE parent_id = @to_clone_id

    OPEN @children_cursor
    FETCH NEXT FROM @children_cursor INTO @child_id
    WHILE @@FETCH_STATUS = 0
    BEGIN
        EXECUTE CloneNode @child_id, @new_node_id
        FETCH NEXT FROM @children_cursor INTO @child_id
    END
    CLOSE @children_cursor
    DEALLOCATE @children_cursor

Su ejemplo se logra mediante EXECUTE CloneNode 2, null (el segundo parámetro es el nuevo nodo padre).