/trunk/schemas/tree_cross-links.sql - Annotate - BIEN 3 - NCEAS Projects

root/trunk/schemas/tree_cross-links.sql @ 13951

-            aaronmk
+/* To get the place trigger, replace taxonlabel with place and remove references
 to matched_label_id. */
             aaronmk
-            aaronmk
+----- Functions
             aaronmk
-            aaronmk
+CREATE OR REPLACE FUNCTION taxonlabel_update_ancestors(new taxonlabel, parent_id_ integer, old_parent_id integer DEFAULT NULL::integer)
-            aaronmk
+  RETURNS void AS
-            aaronmk
+$BODY$
 BEGIN
-            aaronmk
+    IF parent_id_ IS DISTINCT FROM old_parent_id THEN
         DECLARE
             -- These include the parent itself
             old_ancestors integer[] := (
-            aaronmk
+                SELECT array_agg(ancestor_id) FROM taxonlabel_relationship
                 WHERE descendant_id = old_parent_id
-            aaronmk
+            );
             new_ancestors integer[] := (
-            aaronmk
+                SELECT array_agg(ancestor_id) FROM taxonlabel_relationship
                 WHERE descendant_id = parent_id_
-            aaronmk
+            );
-            aaronmk
+            descendant_id_ taxonlabel_relationship.descendant_id%TYPE;
             ancestor_id_ taxonlabel_relationship.ancestor_id%TYPE;
-            aaronmk
+        BEGIN
-            aaronmk
+            FOR descendant_id_ IN -- also includes self
                 SELECT descendant_id
                 FROM taxonlabel_relationship
                 WHERE ancestor_id = new.taxonlabel_id
-            aaronmk
+            LOOP
                 -- Delete old parent's ancestors
-            aaronmk
+                DELETE FROM taxonlabel_relationship
                 WHERE descendant_id = descendant_id_
-            aaronmk
+                AND ancestor_id = ANY (old_ancestors)
+                ;
                 -- Add new parent's ancestors
-            aaronmk
+                FOR ancestor_id_ IN
                     SELECT unnest(new_ancestors)
                 LOOP
                     BEGIN
                         INSERT INTO taxonlabel_relationship
                         (descendant_id, ancestor_id)
                         SELECT descendant_id_, ancestor_id_
+                        ;
                     EXCEPTION
                         WHEN unique_violation THEN
                             RAISE WARNING '%', SQLERRM;
                     END;
                 END LOOP;
-            aaronmk
+            END LOOP;
         END;
-            aaronmk
+    END IF;
             aaronmk
-            aaronmk
+    /* Note: We don't need an ON DELETE trigger to update the descendants'
-            aaronmk
+    ancestors when a node is deleted, because the
-            aaronmk
+    taxonlabel_relationship.ancestor_id foreign key is ON DELETE CASCADE. */
-            aaronmk
+END;
 $BODY$
   LANGUAGE plpgsql VOLATILE
   COST 100;
-            aaronmk
+CREATE OR REPLACE FUNCTION taxonlabel_update_ancestors_on_insert()
-            aaronmk
+  RETURNS trigger AS
 $BODY$
 BEGIN
     /* 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. */
-            aaronmk
+    INSERT
-            aaronmk
+    INTO taxonlabel_relationship
     (descendant_id, ancestor_id)
     VALUES (new.taxonlabel_id, new.taxonlabel_id)
             aaronmk
-            aaronmk
+    PERFORM taxonlabel_update_ancestors(new, new.parent_id);
     PERFORM taxonlabel_update_ancestors(new,
         NULLIF(new.matched_label_id, new.taxonlabel_id));
             aaronmk
-            aaronmk
+    RETURN new;
 END;
 $BODY$
   LANGUAGE plpgsql VOLATILE
   COST 100;
-            aaronmk
+CREATE OR REPLACE FUNCTION taxonlabel_update_ancestors_on_update()
-            aaronmk
+  RETURNS trigger AS
 $BODY$
 BEGIN
-            aaronmk
+    PERFORM taxonlabel_update_ancestors(new, new.parent_id, old.parent_id);
     PERFORM taxonlabel_update_ancestors(new,
         NULLIF(new.matched_label_id, new.taxonlabel_id),
         NULLIF(old.matched_label_id, old.taxonlabel_id));
             aaronmk
-            aaronmk
+    RETURN new;
 END;
 $BODY$
   LANGUAGE plpgsql VOLATILE
   COST 100;
 ----- Ancestors table
-            aaronmk
+CREATE TABLE taxonlabel_relationship
             aaronmk
-            aaronmk
+  descendant_id integer NOT NULL,
-            aaronmk
+  ancestor_id integer NOT NULL,
-            aaronmk
+  relationship relationship NOT NULL DEFAULT 'is included in'::relationship, -- The type of relationship.
   CONSTRAINT taxonlabel_relationship_pkey PRIMARY KEY (descendant_id , ancestor_id ),
   CONSTRAINT taxonlabel_relationship_ancestor_id_fkey FOREIGN KEY (ancestor_id)
       REFERENCES taxonlabel (taxonlabel_id) MATCH SIMPLE
-            aaronmk
+      ON UPDATE CASCADE ON DELETE CASCADE,
-            aaronmk
+  CONSTRAINT taxonlabel_relationship_descendant_id_fkey FOREIGN KEY (descendant_id)
       REFERENCES taxonlabel (taxonlabel_id) MATCH SIMPLE
-            aaronmk
+      ON UPDATE CASCADE ON DELETE CASCADE
+)
 WITH (
   OIDS=FALSE
 );
-            aaronmk
+ALTER TABLE taxonlabel_relationship
   OWNER TO bien;
 COMMENT ON TABLE taxonlabel_relationship
   IS 'Stores the accepted ancestors of a taxonlabel. Auto-populated, so should not be manually modified.';
 COMMENT ON COLUMN taxonlabel_relationship.relationship IS 'The type of relationship.';
             aaronmk
-            aaronmk
+CREATE INDEX taxonlabel_relationship_descendants
   ON taxonlabel_relationship
-            aaronmk
+  USING btree
-            aaronmk
+  (ancestor_id , descendant_id );
             aaronmk
-            aaronmk
+----- Triggers
-            aaronmk
+CREATE TRIGGER taxonlabel_update_ancestors_on_insert
-            aaronmk
+  AFTER INSERT
-            aaronmk
+  ON taxonlabel
-            aaronmk
+  FOR EACH ROW
-            aaronmk
+  EXECUTE PROCEDURE taxonlabel_update_ancestors_on_insert();
             aaronmk
-            aaronmk
+CREATE TRIGGER taxonlabel_update_ancestors_on_update
-            aaronmk
+  AFTER UPDATE
-            aaronmk
+  ON taxonlabel
-            aaronmk
+  FOR EACH ROW
-            aaronmk
+  EXECUTE PROCEDURE taxonlabel_update_ancestors_on_update();

Project

General

Profile