/schemas/tree_cross-links.sql - Diff - BIEN 3 - NCEAS Projects

« Previous | Next »

Revision 5829

Added by Aaron Marcuse-Kubitza over 11 years ago

schemas/tree_cross-links.sql: Updated for schema changes

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

Also available in: Unified diff

Project

General

Profile

Revision 5829

Added by Aaron Marcuse-Kubitza over 11 years ago