/* To get the place trigger, replace taxonlabel with place and remove references

to matched_label_id. */

----- Functions

CREATE OR REPLACE FUNCTION taxonlabel_update_ancestors(new taxonlabel, parent_id_ integer, old_parent_id integer DEFAULT NULL::integer)

  RETURNS void AS

$BODY$

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 taxonlabel_relationship

                WHERE descendant_id = old_parent_id

            );

            new_ancestors integer[] := (

                SELECT array_agg(ancestor_id) FROM taxonlabel_relationship

                WHERE descendant_id = parent_id_

            );

            descendant_id_ taxonlabel_relationship.descendant_id%TYPE;

            ancestor_id_ taxonlabel_relationship.ancestor_id%TYPE;

        BEGIN

            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 taxonlabel_relationship

                WHERE descendant_id = descendant_id_

                AND ancestor_id = ANY (old_ancestors)

                ;

                -- Add new parent's 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

    taxonlabel_relationship.ancestor_id foreign key is ON DELETE CASCADE. */

END;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

CREATE OR REPLACE FUNCTION taxonlabel_update_ancestors_on_insert()

  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. */

    INSERT

    INTO taxonlabel_relationship

    (descendant_id, ancestor_id)

    VALUES (new.taxonlabel_id, new.taxonlabel_id)

    ;

    PERFORM taxonlabel_update_ancestors(new, new.parent_id);

    PERFORM taxonlabel_update_ancestors(new,

        NULLIF(new.matched_label_id, new.taxonlabel_id));

    RETURN new;

END;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

CREATE OR REPLACE FUNCTION taxonlabel_update_ancestors_on_update()

  RETURNS trigger AS

$BODY$

BEGIN

    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;

$BODY$

  LANGUAGE plpgsql VOLATILE

  COST 100;

----- Ancestors table

CREATE TABLE taxonlabel_relationship

(

  descendant_id integer NOT NULL,

  ancestor_id integer NOT NULL,

  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 taxonlabel_relationship_descendant_id_fkey FOREIGN KEY (descendant_id)

      REFERENCES taxonlabel (taxonlabel_id) MATCH SIMPLE

      ON UPDATE CASCADE ON DELETE CASCADE

)

WITH (

  OIDS=FALSE

);

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 taxonlabel_relationship_descendants

  ON taxonlabel_relationship

  USING btree

  (ancestor_id , descendant_id );

----- Triggers

CREATE TRIGGER taxonlabel_update_ancestors_on_insert

  AFTER INSERT

  ON taxonlabel

  FOR EACH ROW

  EXECUTE PROCEDURE taxonlabel_update_ancestors_on_insert();

CREATE TRIGGER taxonlabel_update_ancestors_on_update

  AFTER UPDATE

  ON taxonlabel

  FOR EACH ROW

  EXECUTE PROCEDURE taxonlabel_update_ancestors_on_update();