Revision 5829
Added by Aaron Marcuse-Kubitza almost 12 years ago
schemas/tree_cross-links.sql | ||
---|---|---|
1 |
/* To get the place trigger, replace taxonconcept with place and remove
|
|
2 |
references to matched_concept_id. */
|
|
1 |
/* To get the place trigger, replace taxonlabel with place and remove references
|
|
2 |
to matched_label_id. */
|
|
3 | 3 |
|
4 | 4 |
----- Functions |
5 | 5 |
|
6 |
CREATE OR REPLACE FUNCTION taxonconcept_update_ancestors(new taxonconcept, old_parent_id integer DEFAULT NULL::integer)
|
|
6 |
CREATE OR REPLACE FUNCTION taxonlabel_update_ancestors(new taxonlabel, parent_id_ integer, old_parent_id integer DEFAULT NULL::integer)
|
|
7 | 7 |
RETURNS void AS |
8 | 8 |
$BODY$ |
9 |
DECLARE |
|
10 |
-- Use matched_concept_id's ancestors instead if available |
|
11 |
parent_id_ taxonconcept.taxonconcept_id%TYPE := COALESCE( |
|
12 |
NULLIF(new.matched_concept_id, new.taxonconcept_id), new.parent_id); |
|
13 | 9 |
BEGIN |
14 | 10 |
IF parent_id_ IS DISTINCT FROM old_parent_id THEN |
15 | 11 |
DECLARE |
16 | 12 |
-- These include the parent itself |
17 | 13 |
old_ancestors integer[] := ( |
18 |
SELECT array_agg(ancestor_id) FROM taxonconcept_ancestor
|
|
19 |
WHERE taxonconcept_id = old_parent_id
|
|
14 |
SELECT array_agg(ancestor_id) FROM taxonlabel_relationship
|
|
15 |
WHERE descendant_id = old_parent_id
|
|
20 | 16 |
); |
21 | 17 |
new_ancestors integer[] := ( |
22 |
SELECT array_agg(ancestor_id) FROM taxonconcept_ancestor
|
|
23 |
WHERE taxonconcept_id = parent_id_
|
|
18 |
SELECT array_agg(ancestor_id) FROM taxonlabel_relationship
|
|
19 |
WHERE descendant_id = parent_id_
|
|
24 | 20 |
); |
25 |
descendant_id integer; |
|
21 |
descendant_id_ taxonlabel_relationship.descendant_id%TYPE; |
|
22 |
ancestor_id_ taxonlabel_relationship.ancestor_id%TYPE; |
|
26 | 23 |
BEGIN |
27 |
FOR descendant_id IN -- also includes self |
|
28 |
SELECT taxonconcept_id
|
|
29 |
FROM taxonconcept_ancestor
|
|
30 |
WHERE ancestor_id = new.taxonconcept_id
|
|
24 |
FOR descendant_id_ IN -- also includes self
|
|
25 |
SELECT descendant_id
|
|
26 |
FROM taxonlabel_relationship
|
|
27 |
WHERE ancestor_id = new.taxonlabel_id
|
|
31 | 28 |
LOOP |
32 | 29 |
-- Delete old parent's ancestors |
33 |
DELETE FROM taxonconcept_ancestor
|
|
34 |
WHERE taxonconcept_id = descendant_id
|
|
30 |
DELETE FROM taxonlabel_relationship
|
|
31 |
WHERE descendant_id = descendant_id_
|
|
35 | 32 |
AND ancestor_id = ANY (old_ancestors) |
36 | 33 |
; |
37 | 34 |
|
38 | 35 |
-- Add new parent's ancestors |
39 |
INSERT INTO taxonconcept_ancestor |
|
40 |
(taxonconcept_id, ancestor_id) |
|
41 |
SELECT descendant_id, unnest(new_ancestors) |
|
42 |
; |
|
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; |
|
43 | 49 |
END LOOP; |
44 | 50 |
END; |
45 | 51 |
END IF; |
46 | 52 |
|
47 | 53 |
/* Note: We don't need an ON DELETE trigger to update the descendants' |
48 | 54 |
ancestors when a node is deleted, because the |
49 |
taxonconcept_ancestor.ancestor_id foreign key is ON DELETE CASCADE. */
|
|
55 |
taxonlabel_relationship.ancestor_id foreign key is ON DELETE CASCADE. */
|
|
50 | 56 |
END; |
51 | 57 |
$BODY$ |
52 | 58 |
LANGUAGE plpgsql VOLATILE |
53 | 59 |
COST 100; |
54 | 60 |
|
55 |
CREATE OR REPLACE FUNCTION taxonconcept_update_ancestors_on_insert()
|
|
61 |
CREATE OR REPLACE FUNCTION taxonlabel_update_ancestors_on_insert()
|
|
56 | 62 |
RETURNS trigger AS |
57 | 63 |
$BODY$ |
58 | 64 |
BEGIN |
... | ... | |
60 | 66 |
don't have to separately test if the leaf node is the one you're looking |
61 | 67 |
for, in addition to that leaf node's ancestors. */ |
62 | 68 |
INSERT |
63 |
INTO taxonconcept_ancestor
|
|
64 |
(taxonconcept_id, ancestor_id)
|
|
65 |
VALUES (new.taxonconcept_id, new.taxonconcept_id)
|
|
69 |
INTO taxonlabel_relationship
|
|
70 |
(descendant_id, ancestor_id)
|
|
71 |
VALUES (new.taxonlabel_id, new.taxonlabel_id)
|
|
66 | 72 |
; |
67 | 73 |
|
68 |
PERFORM taxonconcept_update_ancestors(new); |
|
74 |
PERFORM taxonlabel_update_ancestors(new, new.parent_id); |
|
75 |
PERFORM taxonlabel_update_ancestors(new, |
|
76 |
NULLIF(new.matched_label_id, new.taxonlabel_id)); |
|
69 | 77 |
|
70 | 78 |
RETURN new; |
71 | 79 |
END; |
... | ... | |
73 | 81 |
LANGUAGE plpgsql VOLATILE |
74 | 82 |
COST 100; |
75 | 83 |
|
76 |
CREATE OR REPLACE FUNCTION taxonconcept_update_ancestors_on_update()
|
|
84 |
CREATE OR REPLACE FUNCTION taxonlabel_update_ancestors_on_update()
|
|
77 | 85 |
RETURNS trigger AS |
78 | 86 |
$BODY$ |
79 | 87 |
BEGIN |
80 |
PERFORM taxonconcept_update_ancestors(new, COALESCE( |
|
81 |
NULLIF(old.matched_concept_id, old.taxonconcept_id), old.parent_id)); |
|
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)); |
|
82 | 92 |
|
83 | 93 |
RETURN new; |
84 | 94 |
END; |
... | ... | |
88 | 98 |
|
89 | 99 |
----- Ancestors table |
90 | 100 |
|
91 |
CREATE TABLE taxonconcept_ancestor
|
|
101 |
CREATE TABLE taxonlabel_relationship
|
|
92 | 102 |
( |
93 |
taxonconcept_id integer NOT NULL,
|
|
103 |
descendant_id integer NOT NULL,
|
|
94 | 104 |
ancestor_id integer NOT NULL, |
95 |
CONSTRAINT taxonconcept_ancestor_pkey PRIMARY KEY (taxonconcept_id , ancestor_id ), |
|
96 |
CONSTRAINT taxonconcept_ancestor_ancestor_id_fkey FOREIGN KEY (ancestor_id) |
|
97 |
REFERENCES taxonconcept (taxonconcept_id) MATCH SIMPLE |
|
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 |
|
98 | 109 |
ON UPDATE CASCADE ON DELETE CASCADE, |
99 |
CONSTRAINT taxonconcept_ancestor_taxonconcept_id_fkey FOREIGN KEY (taxonconcept_id)
|
|
100 |
REFERENCES taxonconcept (taxonconcept_id) MATCH SIMPLE
|
|
110 |
CONSTRAINT taxonlabel_relationship_descendant_id_fkey FOREIGN KEY (descendant_id)
|
|
111 |
REFERENCES taxonlabel (taxonlabel_id) MATCH SIMPLE
|
|
101 | 112 |
ON UPDATE CASCADE ON DELETE CASCADE |
102 | 113 |
) |
103 | 114 |
WITH ( |
104 | 115 |
OIDS=FALSE |
105 | 116 |
); |
106 |
COMMENT ON TABLE taxonconcept_ancestor |
|
107 |
IS 'Stores the accepted ancestors of a taxonconcept. Auto-populated, so should not be manually modified.'; |
|
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.'; |
|
108 | 122 |
|
109 |
CREATE INDEX taxonconcept_ancestor_descendants
|
|
110 |
ON taxonconcept_ancestor
|
|
123 |
CREATE INDEX taxonlabel_relationship_descendants
|
|
124 |
ON taxonlabel_relationship
|
|
111 | 125 |
USING btree |
112 |
(ancestor_id , taxonconcept_id );
|
|
126 |
(ancestor_id , descendant_id );
|
|
113 | 127 |
|
114 | 128 |
----- Triggers |
115 | 129 |
|
116 |
CREATE TRIGGER taxonconcept_update_ancestors_on_insert
|
|
130 |
CREATE TRIGGER taxonlabel_update_ancestors_on_insert
|
|
117 | 131 |
AFTER INSERT |
118 |
ON taxonconcept
|
|
132 |
ON taxonlabel
|
|
119 | 133 |
FOR EACH ROW |
120 |
EXECUTE PROCEDURE taxonconcept_update_ancestors_on_insert();
|
|
134 |
EXECUTE PROCEDURE taxonlabel_update_ancestors_on_insert();
|
|
121 | 135 |
|
122 |
CREATE TRIGGER taxonconcept_update_ancestors_on_update
|
|
136 |
CREATE TRIGGER taxonlabel_update_ancestors_on_update
|
|
123 | 137 |
AFTER UPDATE |
124 |
ON taxonconcept
|
|
138 |
ON taxonlabel
|
|
125 | 139 |
FOR EACH ROW |
126 |
EXECUTE PROCEDURE taxonconcept_update_ancestors_on_update(); |
|
140 |
EXECUTE PROCEDURE taxonlabel_update_ancestors_on_update(); |
Also available in: Unified diff
schemas/tree_cross-links.sql: Updated for schema changes