Project

General

Profile

1
/* To get the place trigger, replace taxonlabel with place and remove references
2
to matched_label_id. */
3

    
4
----- Functions
5

    
6
CREATE OR REPLACE FUNCTION taxonlabel_update_ancestors(new taxonlabel, parent_id_ integer, old_parent_id integer DEFAULT NULL::integer)
7
  RETURNS void AS
8
$BODY$
9
BEGIN
10
    IF parent_id_ IS DISTINCT FROM old_parent_id THEN
11
        DECLARE
12
            -- These include the parent itself
13
            old_ancestors integer[] := (
14
                SELECT array_agg(ancestor_id) FROM taxonlabel_relationship
15
                WHERE descendant_id = old_parent_id
16
            );
17
            new_ancestors integer[] := (
18
                SELECT array_agg(ancestor_id) FROM taxonlabel_relationship
19
                WHERE descendant_id = parent_id_
20
            );
21
            descendant_id_ taxonlabel_relationship.descendant_id%TYPE;
22
            ancestor_id_ taxonlabel_relationship.ancestor_id%TYPE;
23
        BEGIN
24
            FOR descendant_id_ IN -- also includes self
25
                SELECT descendant_id
26
                FROM taxonlabel_relationship
27
                WHERE ancestor_id = new.taxonlabel_id
28
            LOOP
29
                -- Delete old parent's ancestors
30
                DELETE FROM taxonlabel_relationship
31
                WHERE descendant_id = descendant_id_
32
                AND ancestor_id = ANY (old_ancestors)
33
                ;
34
                
35
                -- Add new parent's ancestors
36
                FOR ancestor_id_ IN
37
                    SELECT unnest(new_ancestors)
38
                LOOP
39
                    BEGIN
40
                        INSERT INTO taxonlabel_relationship
41
                        (descendant_id, ancestor_id)
42
                        SELECT descendant_id_, ancestor_id_
43
                        ;
44
                    EXCEPTION
45
                        WHEN unique_violation THEN
46
                            RAISE WARNING '%', SQLERRM;
47
                    END;
48
                END LOOP;
49
            END LOOP;
50
        END;
51
    END IF;
52
    
53
    /* Note: We don't need an ON DELETE trigger to update the descendants'
54
    ancestors when a node is deleted, because the
55
    taxonlabel_relationship.ancestor_id foreign key is ON DELETE CASCADE. */
56
END;
57
$BODY$
58
  LANGUAGE plpgsql VOLATILE
59
  COST 100;
60

    
61
CREATE OR REPLACE FUNCTION taxonlabel_update_ancestors_on_insert()
62
  RETURNS trigger AS
63
$BODY$
64
BEGIN
65
    /* Add "ancestor" for this node. This is useful for queries, because you
66
    don't have to separately test if the leaf node is the one you're looking
67
    for, in addition to that leaf node's ancestors. */
68
    INSERT
69
    INTO taxonlabel_relationship
70
    (descendant_id, ancestor_id)
71
    VALUES (new.taxonlabel_id, new.taxonlabel_id)
72
    ;
73
    
74
    PERFORM taxonlabel_update_ancestors(new, new.parent_id);
75
    PERFORM taxonlabel_update_ancestors(new,
76
        NULLIF(new.matched_label_id, new.taxonlabel_id));
77
    
78
    RETURN new;
79
END;
80
$BODY$
81
  LANGUAGE plpgsql VOLATILE
82
  COST 100;
83

    
84
CREATE OR REPLACE FUNCTION taxonlabel_update_ancestors_on_update()
85
  RETURNS trigger AS
86
$BODY$
87
BEGIN
88
    PERFORM taxonlabel_update_ancestors(new, new.parent_id, old.parent_id);
89
    PERFORM taxonlabel_update_ancestors(new,
90
        NULLIF(new.matched_label_id, new.taxonlabel_id),
91
        NULLIF(old.matched_label_id, old.taxonlabel_id));
92
    
93
    RETURN new;
94
END;
95
$BODY$
96
  LANGUAGE plpgsql VOLATILE
97
  COST 100;
98

    
99
----- Ancestors table
100

    
101
CREATE TABLE taxonlabel_relationship
102
(
103
  descendant_id integer NOT NULL,
104
  ancestor_id integer NOT NULL,
105
  relationship relationship NOT NULL DEFAULT 'is included in'::relationship, -- The type of relationship.
106
  CONSTRAINT taxonlabel_relationship_pkey PRIMARY KEY (descendant_id , ancestor_id ),
107
  CONSTRAINT taxonlabel_relationship_ancestor_id_fkey FOREIGN KEY (ancestor_id)
108
      REFERENCES taxonlabel (taxonlabel_id) MATCH SIMPLE
109
      ON UPDATE CASCADE ON DELETE CASCADE,
110
  CONSTRAINT taxonlabel_relationship_descendant_id_fkey FOREIGN KEY (descendant_id)
111
      REFERENCES taxonlabel (taxonlabel_id) MATCH SIMPLE
112
      ON UPDATE CASCADE ON DELETE CASCADE
113
)
114
WITH (
115
  OIDS=FALSE
116
);
117
ALTER TABLE taxonlabel_relationship
118
  OWNER TO bien;
119
COMMENT ON TABLE taxonlabel_relationship
120
  IS 'Stores the accepted ancestors of a taxonlabel. Auto-populated, so should not be manually modified.';
121
COMMENT ON COLUMN taxonlabel_relationship.relationship IS 'The type of relationship.';
122

    
123
CREATE INDEX taxonlabel_relationship_descendants
124
  ON taxonlabel_relationship
125
  USING btree
126
  (ancestor_id , descendant_id );
127

    
128
----- Triggers
129

    
130
CREATE TRIGGER taxonlabel_update_ancestors_on_insert
131
  AFTER INSERT
132
  ON taxonlabel
133
  FOR EACH ROW
134
  EXECUTE PROCEDURE taxonlabel_update_ancestors_on_insert();
135

    
136
CREATE TRIGGER taxonlabel_update_ancestors_on_update
137
  AFTER UPDATE
138
  ON taxonlabel
139
  FOR EACH ROW
140
  EXECUTE PROCEDURE taxonlabel_update_ancestors_on_update();
(12-12/22)