/ - Diff - BIEN 3 - NCEAS Projects

« Previous | Next »

Revision 1559

Added by Aaron Marcuse-Kubitza almost 13 years ago

vegbien.sql: Fixed bug in tree cross-link algorithm to also insert ancestors for top-level nodes, because they now need an ancestor entry for themselves

       RETURNS trigger AS
     $BODY$
     BEGIN
         -- Delete existing ancestors
         DELETE FROM plantname_ancestor WHERE plantname_id = new.plantname_id;
         IF new.parent_id IS NOT NULL THEN
             -- Delete existing ancestors
             DELETE FROM plantname_ancestor WHERE plantname_id = new.plantname_id;
             -- Copy parent's ancestors to this node's ancestors
             INSERT
             INTO plantname_ancestor
-...
             FROM plantname_ancestor
             WHERE plantname_id = new.parent_id
+            ;
             -- Add "ancestor" for this node
             /* This is useful for queries, because you don't have to separately test
             if the leaf node is the one you're looking for, in addition to that leaf
             node's ancestors. */
             INSERT
             INTO plantname_ancestor
             (plantname_id, ancestor_id)
             VALUES (new.plantname_id, new.plantname_id)
+            ;
             -- Tell immediate children to update their ancestors lists, which will
             -- recursively tell all descendants
             UPDATE plantname
             SET plantname_id = plantname.plantname_id -- set to itself
             WHERE parent_id = plantname_id -- the plantname_id passed as param
+            ;
             /*
             Note: We don't need an ON DELETE trigger to update the descendants'
             ancestors when a node is deleted, because the
             plantname.plantname_parent_id foreign key is set to ON DELETE CASCADE,
             which just removes all the descendants anyway.
             */
         END IF;
         -- Add "ancestor" for this node
         /* This is useful for queries, because you don't have to separately test if
         the leaf node is the one you're looking for, in addition to that leaf node's
         ancestors. */
         INSERT
         INTO plantname_ancestor
         (plantname_id, ancestor_id)
         VALUES (new.plantname_id, new.plantname_id)
+        ;
         -- Tell immediate children to update their ancestors lists, which will
         -- recursively tell all descendants
         UPDATE plantname
         SET plantname_id = plantname.plantname_id -- set to itself
         WHERE parent_id = plantname_id -- the plantname_id passed as param
+        ;
         /* Note: We don't need an ON DELETE trigger to update the descendants'
         ancestors when a node is deleted, because the plantname.plantname_parent_id
         foreign key is set to ON DELETE CASCADE, which just removes all the
         descendants anyway. */
         RETURN new;
     END;
     $BODY$

         LANGUAGE plpgsql
         AS $$
     BEGIN
         -- Delete existing ancestors
         DELETE FROM namedplace_ancestor WHERE namedplace_id = new.namedplace_id;
         IF new.parent_id IS NOT NULL THEN
             -- Delete existing ancestors
             DELETE FROM namedplace_ancestor WHERE namedplace_id = new.namedplace_id;
             -- Copy parent's ancestors to this node's ancestors
             INSERT
             INTO namedplace_ancestor
-...
             FROM namedplace_ancestor
             WHERE namedplace_id = new.parent_id
+            ;
             -- Add "ancestor" for this node
             /* This is useful for queries, because you don't have to separately test
             if the leaf node is the one you're looking for, in addition to that leaf
             node's ancestors. */
             INSERT
             INTO namedplace_ancestor
             (namedplace_id, ancestor_id)
             VALUES (new.namedplace_id, new.namedplace_id)
+            ;
             -- Tell immediate children to update their ancestors lists, which will
             -- recursively tell all descendants
             UPDATE namedplace
             SET namedplace_id = namedplace.namedplace_id -- set to itself
             WHERE parent_id = namedplace_id -- the namedplace_id passed as param
+            ;
             /*
             Note: We don't need an ON DELETE trigger to update the descendants'
             ancestors when a node is deleted, because the
             namedplace.namedplace_parent_id foreign key is set to ON DELETE CASCADE,
             which just removes all the descendants anyway.
             */
         END IF;
         -- Add "ancestor" for this node
         /* This is useful for queries, because you don't have to separately test if
         the leaf node is the one you're looking for, in addition to that leaf node's
         ancestors. */
         INSERT
         INTO namedplace_ancestor
         (namedplace_id, ancestor_id)
         VALUES (new.namedplace_id, new.namedplace_id)
+        ;
         -- Tell immediate children to update their ancestors lists, which will
         -- recursively tell all descendants
         UPDATE namedplace
         SET namedplace_id = namedplace.namedplace_id -- set to itself
         WHERE parent_id = namedplace_id -- the namedplace_id passed as param
+        ;
         /* Note: We don't need an ON DELETE trigger to update the descendants'
         ancestors when a node is deleted, because the namedplace.namedplace_parent_id
         foreign key is set to ON DELETE CASCADE, which just removes all the
         descendants anyway. */
         RETURN new;
     END;
     $$;
-...
         LANGUAGE plpgsql
         AS $$
     BEGIN
         -- Delete existing ancestors
         DELETE FROM plantname_ancestor WHERE plantname_id = new.plantname_id;
         IF new.parent_id IS NOT NULL THEN
             -- Delete existing ancestors
             DELETE FROM plantname_ancestor WHERE plantname_id = new.plantname_id;
             -- Copy parent's ancestors to this node's ancestors
             INSERT
             INTO plantname_ancestor
-...
             FROM plantname_ancestor
             WHERE plantname_id = new.parent_id
+            ;
             -- Add "ancestor" for this node
             /* This is useful for queries, because you don't have to separately test
             if the leaf node is the one you're looking for, in addition to that leaf
             node's ancestors. */
             INSERT
             INTO plantname_ancestor
             (plantname_id, ancestor_id)
             VALUES (new.plantname_id, new.plantname_id)
+            ;
             -- Tell immediate children to update their ancestors lists, which will
             -- recursively tell all descendants
             UPDATE plantname
             SET plantname_id = plantname.plantname_id -- set to itself
             WHERE parent_id = plantname_id -- the plantname_id passed as param
+            ;
             /*
             Note: We don't need an ON DELETE trigger to update the descendants'
             ancestors when a node is deleted, because the
             plantname.plantname_parent_id foreign key is set to ON DELETE CASCADE,
             which just removes all the descendants anyway.
             */
         END IF;
         -- Add "ancestor" for this node
         /* This is useful for queries, because you don't have to separately test if
         the leaf node is the one you're looking for, in addition to that leaf node's
         ancestors. */
         INSERT
         INTO plantname_ancestor
         (plantname_id, ancestor_id)
         VALUES (new.plantname_id, new.plantname_id)
+        ;
         -- Tell immediate children to update their ancestors lists, which will
         -- recursively tell all descendants
         UPDATE plantname
         SET plantname_id = plantname.plantname_id -- set to itself
         WHERE parent_id = plantname_id -- the plantname_id passed as param
+        ;
         /* Note: We don't need an ON DELETE trigger to update the descendants'
         ancestors when a node is deleted, because the plantname.plantname_parent_id
         foreign key is set to ON DELETE CASCADE, which just removes all the
         descendants anyway. */
         RETURN new;
     END;
     $$;

Also available in: Unified diff

Project

General

Profile

Revision 1559

Added by Aaron Marcuse-Kubitza almost 13 years ago