Revision 5609
Added by Aaron Marcuse-Kubitza over 12 years ago
| schemas/vegbien.my.sql | ||
|---|---|---|
| 2415 | 2415 |
|
| 2416 | 2416 |
|
| 2417 | 2417 |
-- |
| 2418 |
-- Name: taxonlabel_ancestor; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 2418 |
-- Name: taxonlabel_relationship; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 2419 | 2419 |
-- |
| 2420 | 2420 |
|
| 2421 |
CREATE TABLE taxonlabel_ancestor (
|
|
| 2421 |
CREATE TABLE taxonlabel_relationship (
|
|
| 2422 | 2422 |
descendant_id int(11) NOT NULL, |
| 2423 | 2423 |
ancestor_id int(11) NOT NULL |
| 2424 | 2424 |
); |
| 2425 | 2425 |
|
| 2426 | 2426 |
|
| 2427 | 2427 |
-- |
| 2428 |
-- Name: TABLE taxonlabel_ancestor; Type: COMMENT; Schema: public; Owner: -
|
|
| 2428 |
-- Name: TABLE taxonlabel_relationship; Type: COMMENT; Schema: public; Owner: -
|
|
| 2429 | 2429 |
-- |
| 2430 | 2430 |
|
| 2431 | 2431 |
|
| ... | ... | |
| 3539 | 3539 |
|
| 3540 | 3540 |
|
| 3541 | 3541 |
-- |
| 3542 |
-- Name: taxonlabel_ancestor_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
| 3542 |
-- Name: taxonlabel_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3543 | 3543 |
-- |
| 3544 | 3544 |
|
| 3545 |
ALTER TABLE taxonlabel_ancestor
|
|
| 3546 |
ADD CONSTRAINT taxonlabel_ancestor_pkey PRIMARY KEY (descendant_id, ancestor_id);
|
|
| 3545 |
ALTER TABLE taxonlabel |
|
| 3546 |
ADD CONSTRAINT taxonlabel_pkey PRIMARY KEY (taxonlabel_id);
|
|
| 3547 | 3547 |
|
| 3548 | 3548 |
|
| 3549 | 3549 |
-- |
| 3550 |
-- Name: taxonlabel_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3550 |
-- Name: taxonlabel_relationship_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
| 3551 | 3551 |
-- |
| 3552 | 3552 |
|
| 3553 |
ALTER TABLE taxonlabel |
|
| 3554 |
ADD CONSTRAINT taxonlabel_pkey PRIMARY KEY (taxonlabel_id);
|
|
| 3553 |
ALTER TABLE taxonlabel_relationship
|
|
| 3554 |
ADD CONSTRAINT taxonlabel_relationship_pkey PRIMARY KEY (descendant_id, ancestor_id);
|
|
| 3555 | 3555 |
|
| 3556 | 3556 |
|
| 3557 | 3557 |
-- |
| ... | ... | |
| 4021 | 4021 |
|
| 4022 | 4022 |
|
| 4023 | 4023 |
-- |
| 4024 |
-- Name: taxonlabel_ancestor_descendants; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4024 |
-- Name: taxonlabel_matched_label_id_idx; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4025 | 4025 |
-- |
| 4026 | 4026 |
|
| 4027 |
CREATE INDEX taxonlabel_ancestor_descendants ON taxonlabel_ancestor (ancestor_id, descendant_id);
|
|
| 4027 |
CREATE INDEX taxonlabel_matched_label_id_idx ON taxonlabel (matched_label_id);
|
|
| 4028 | 4028 |
|
| 4029 | 4029 |
|
| 4030 | 4030 |
-- |
| 4031 |
-- Name: taxonlabel_matched_label_id_idx; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4031 |
-- Name: taxonlabel_relationship_descendants; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4032 | 4032 |
-- |
| 4033 | 4033 |
|
| 4034 |
CREATE INDEX taxonlabel_matched_label_id_idx ON taxonlabel (matched_label_id);
|
|
| 4034 |
CREATE INDEX taxonlabel_relationship_descendants ON taxonlabel_relationship (ancestor_id, descendant_id);
|
|
| 4035 | 4035 |
|
| 4036 | 4036 |
|
| 4037 | 4037 |
-- |
| ... | ... | |
| 4953 | 4953 |
|
| 4954 | 4954 |
|
| 4955 | 4955 |
-- |
| 4956 |
-- Name: taxonlabel_ancestor_ancestor_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4956 |
-- Name: taxonlabel_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4957 | 4957 |
-- |
| 4958 | 4958 |
|
| 4959 |
ALTER TABLE taxonlabel_ancestor |
|
| 4960 |
ADD CONSTRAINT taxonlabel_ancestor_ancestor_id_fkey FOREIGN KEY (ancestor_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 4961 | 4959 |
|
| 4962 | 4960 |
|
| 4961 |
|
|
| 4963 | 4962 |
-- |
| 4964 |
-- Name: taxonlabel_ancestor_descendant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4963 |
-- Name: taxonlabel_matched_label_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4965 | 4964 |
-- |
| 4966 | 4965 |
|
| 4967 |
ALTER TABLE taxonlabel_ancestor
|
|
| 4968 |
ADD CONSTRAINT taxonlabel_ancestor_descendant_id_fkey FOREIGN KEY (descendant_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4966 |
ALTER TABLE taxonlabel |
|
| 4967 |
ADD CONSTRAINT taxonlabel_matched_label_id_fkey FOREIGN KEY (matched_label_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4969 | 4968 |
|
| 4970 | 4969 |
|
| 4971 | 4970 |
-- |
| 4972 |
-- Name: taxonlabel_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4971 |
-- Name: taxonlabel_parent_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4973 | 4972 |
-- |
| 4974 | 4973 |
|
| 4974 |
ALTER TABLE taxonlabel |
|
| 4975 |
ADD CONSTRAINT taxonlabel_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 4975 | 4976 |
|
| 4976 | 4977 |
|
| 4977 |
|
|
| 4978 | 4978 |
-- |
| 4979 |
-- Name: taxonlabel_matched_label_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4979 |
-- Name: taxonlabel_relationship_ancestor_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4980 | 4980 |
-- |
| 4981 | 4981 |
|
| 4982 |
ALTER TABLE taxonlabel |
|
| 4983 |
ADD CONSTRAINT taxonlabel_matched_label_id_fkey FOREIGN KEY (matched_label_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4982 |
ALTER TABLE taxonlabel_relationship
|
|
| 4983 |
ADD CONSTRAINT taxonlabel_relationship_ancestor_id_fkey FOREIGN KEY (ancestor_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4984 | 4984 |
|
| 4985 | 4985 |
|
| 4986 | 4986 |
-- |
| 4987 |
-- Name: taxonlabel_parent_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4987 |
-- Name: taxonlabel_relationship_descendant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4988 | 4988 |
-- |
| 4989 | 4989 |
|
| 4990 |
ALTER TABLE taxonlabel |
|
| 4991 |
ADD CONSTRAINT taxonlabel_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4990 |
ALTER TABLE taxonlabel_relationship
|
|
| 4991 |
ADD CONSTRAINT taxonlabel_relationship_descendant_id_fkey FOREIGN KEY (descendant_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4992 | 4992 |
|
| 4993 | 4993 |
|
| 4994 | 4994 |
-- |
| schemas/vegbien.sql | ||
|---|---|---|
| 684 | 684 |
DECLARE |
| 685 | 685 |
-- These include the parent itself |
| 686 | 686 |
old_ancestors integer[] := ( |
| 687 |
SELECT array_agg(ancestor_id) FROM taxonlabel_ancestor
|
|
| 687 |
SELECT array_agg(ancestor_id) FROM taxonlabel_relationship
|
|
| 688 | 688 |
WHERE descendant_id = old_parent_id |
| 689 | 689 |
); |
| 690 | 690 |
new_ancestors integer[] := ( |
| 691 |
SELECT array_agg(ancestor_id) FROM taxonlabel_ancestor
|
|
| 691 |
SELECT array_agg(ancestor_id) FROM taxonlabel_relationship
|
|
| 692 | 692 |
WHERE descendant_id = parent_id_ |
| 693 | 693 |
); |
| 694 | 694 |
descendant_id_ integer; |
| 695 | 695 |
BEGIN |
| 696 | 696 |
FOR descendant_id_ IN -- also includes self |
| 697 | 697 |
SELECT descendant_id |
| 698 |
FROM taxonlabel_ancestor
|
|
| 698 |
FROM taxonlabel_relationship
|
|
| 699 | 699 |
WHERE ancestor_id = new.taxonlabel_id |
| 700 | 700 |
LOOP |
| 701 | 701 |
-- Delete old parent's ancestors |
| 702 |
DELETE FROM taxonlabel_ancestor
|
|
| 702 |
DELETE FROM taxonlabel_relationship
|
|
| 703 | 703 |
WHERE descendant_id = descendant_id_ |
| 704 | 704 |
AND ancestor_id = ANY (old_ancestors) |
| 705 | 705 |
; |
| 706 | 706 |
|
| 707 | 707 |
-- Add new parent's ancestors |
| 708 |
INSERT INTO taxonlabel_ancestor
|
|
| 708 |
INSERT INTO taxonlabel_relationship
|
|
| 709 | 709 |
(descendant_id, ancestor_id) |
| 710 | 710 |
SELECT descendant_id_, unnest(new_ancestors) |
| 711 | 711 |
; |
| ... | ... | |
| 715 | 715 |
|
| 716 | 716 |
/* Note: We don't need an ON DELETE trigger to update the descendants' |
| 717 | 717 |
ancestors when a node is deleted, because the |
| 718 |
taxonlabel_ancestor.ancestor_id foreign key is ON DELETE CASCADE. */
|
|
| 718 |
taxonlabel_relationship.ancestor_id foreign key is ON DELETE CASCADE. */
|
|
| 719 | 719 |
END; |
| 720 | 720 |
$$; |
| 721 | 721 |
|
| ... | ... | |
| 732 | 732 |
don't have to separately test if the leaf node is the one you're looking |
| 733 | 733 |
for, in addition to that leaf node's ancestors. */ |
| 734 | 734 |
INSERT |
| 735 |
INTO taxonlabel_ancestor
|
|
| 735 |
INTO taxonlabel_relationship
|
|
| 736 | 736 |
(descendant_id, ancestor_id) |
| 737 | 737 |
VALUES (new.taxonlabel_id, new.taxonlabel_id) |
| 738 | 738 |
; |
| ... | ... | |
| 3105 | 3105 |
|
| 3106 | 3106 |
|
| 3107 | 3107 |
-- |
| 3108 |
-- Name: taxonlabel_ancestor; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 3108 |
-- Name: taxonlabel_relationship; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 3109 | 3109 |
-- |
| 3110 | 3110 |
|
| 3111 |
CREATE TABLE taxonlabel_ancestor (
|
|
| 3111 |
CREATE TABLE taxonlabel_relationship (
|
|
| 3112 | 3112 |
descendant_id integer NOT NULL, |
| 3113 | 3113 |
ancestor_id integer NOT NULL |
| 3114 | 3114 |
); |
| 3115 | 3115 |
|
| 3116 | 3116 |
|
| 3117 | 3117 |
-- |
| 3118 |
-- Name: TABLE taxonlabel_ancestor; Type: COMMENT; Schema: public; Owner: -
|
|
| 3118 |
-- Name: TABLE taxonlabel_relationship; Type: COMMENT; Schema: public; Owner: -
|
|
| 3119 | 3119 |
-- |
| 3120 | 3120 |
|
| 3121 |
COMMENT ON TABLE taxonlabel_ancestor IS 'Stores the accepted ancestors of a taxonlabel. Auto-populated, so should not be manually modified.';
|
|
| 3121 |
COMMENT ON TABLE taxonlabel_relationship IS 'Stores the accepted ancestors of a taxonlabel. Auto-populated, so should not be manually modified.';
|
|
| 3122 | 3122 |
|
| 3123 | 3123 |
|
| 3124 | 3124 |
-- |
| ... | ... | |
| 4274 | 4274 |
|
| 4275 | 4275 |
|
| 4276 | 4276 |
-- |
| 4277 |
-- Name: taxonlabel_ancestor_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
| 4277 |
-- Name: taxonlabel_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 4278 | 4278 |
-- |
| 4279 | 4279 |
|
| 4280 |
ALTER TABLE ONLY taxonlabel_ancestor
|
|
| 4281 |
ADD CONSTRAINT taxonlabel_ancestor_pkey PRIMARY KEY (descendant_id, ancestor_id);
|
|
| 4280 |
ALTER TABLE ONLY taxonlabel |
|
| 4281 |
ADD CONSTRAINT taxonlabel_pkey PRIMARY KEY (taxonlabel_id);
|
|
| 4282 | 4282 |
|
| 4283 | 4283 |
|
| 4284 | 4284 |
-- |
| 4285 |
-- Name: taxonlabel_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 4285 |
-- Name: taxonlabel_relationship_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
| 4286 | 4286 |
-- |
| 4287 | 4287 |
|
| 4288 |
ALTER TABLE ONLY taxonlabel |
|
| 4289 |
ADD CONSTRAINT taxonlabel_pkey PRIMARY KEY (taxonlabel_id);
|
|
| 4288 |
ALTER TABLE ONLY taxonlabel_relationship
|
|
| 4289 |
ADD CONSTRAINT taxonlabel_relationship_pkey PRIMARY KEY (descendant_id, ancestor_id);
|
|
| 4290 | 4290 |
|
| 4291 | 4291 |
|
| 4292 | 4292 |
-- |
| ... | ... | |
| 4756 | 4756 |
|
| 4757 | 4757 |
|
| 4758 | 4758 |
-- |
| 4759 |
-- Name: taxonlabel_ancestor_descendants; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4759 |
-- Name: taxonlabel_matched_label_id_idx; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4760 | 4760 |
-- |
| 4761 | 4761 |
|
| 4762 |
CREATE INDEX taxonlabel_ancestor_descendants ON taxonlabel_ancestor USING btree (ancestor_id, descendant_id);
|
|
| 4762 |
CREATE INDEX taxonlabel_matched_label_id_idx ON taxonlabel USING btree (matched_label_id);
|
|
| 4763 | 4763 |
|
| 4764 | 4764 |
|
| 4765 | 4765 |
-- |
| 4766 |
-- Name: taxonlabel_matched_label_id_idx; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4766 |
-- Name: taxonlabel_relationship_descendants; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 4767 | 4767 |
-- |
| 4768 | 4768 |
|
| 4769 |
CREATE INDEX taxonlabel_matched_label_id_idx ON taxonlabel USING btree (matched_label_id);
|
|
| 4769 |
CREATE INDEX taxonlabel_relationship_descendants ON taxonlabel_relationship USING btree (ancestor_id, descendant_id);
|
|
| 4770 | 4770 |
|
| 4771 | 4771 |
|
| 4772 | 4772 |
-- |
| ... | ... | |
| 5721 | 5721 |
|
| 5722 | 5722 |
|
| 5723 | 5723 |
-- |
| 5724 |
-- Name: taxonlabel_ancestor_ancestor_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5724 |
-- Name: taxonlabel_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5725 | 5725 |
-- |
| 5726 | 5726 |
|
| 5727 |
ALTER TABLE ONLY taxonlabel_ancestor
|
|
| 5728 |
ADD CONSTRAINT taxonlabel_ancestor_ancestor_id_fkey FOREIGN KEY (ancestor_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5727 |
ALTER TABLE ONLY taxonlabel |
|
| 5728 |
ADD CONSTRAINT taxonlabel_creator_id_fkey FOREIGN KEY (creator_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5729 | 5729 |
|
| 5730 | 5730 |
|
| 5731 | 5731 |
-- |
| 5732 |
-- Name: taxonlabel_ancestor_descendant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5732 |
-- Name: taxonlabel_matched_label_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5733 | 5733 |
-- |
| 5734 | 5734 |
|
| 5735 |
ALTER TABLE ONLY taxonlabel_ancestor
|
|
| 5736 |
ADD CONSTRAINT taxonlabel_ancestor_descendant_id_fkey FOREIGN KEY (descendant_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5735 |
ALTER TABLE ONLY taxonlabel |
|
| 5736 |
ADD CONSTRAINT taxonlabel_matched_label_id_fkey FOREIGN KEY (matched_label_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5737 | 5737 |
|
| 5738 | 5738 |
|
| 5739 | 5739 |
-- |
| 5740 |
-- Name: taxonlabel_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5740 |
-- Name: taxonlabel_parent_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5741 | 5741 |
-- |
| 5742 | 5742 |
|
| 5743 | 5743 |
ALTER TABLE ONLY taxonlabel |
| 5744 |
ADD CONSTRAINT taxonlabel_creator_id_fkey FOREIGN KEY (creator_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5744 |
ADD CONSTRAINT taxonlabel_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5745 | 5745 |
|
| 5746 | 5746 |
|
| 5747 | 5747 |
-- |
| 5748 |
-- Name: taxonlabel_matched_label_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5748 |
-- Name: taxonlabel_relationship_ancestor_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5749 | 5749 |
-- |
| 5750 | 5750 |
|
| 5751 |
ALTER TABLE ONLY taxonlabel |
|
| 5752 |
ADD CONSTRAINT taxonlabel_matched_label_id_fkey FOREIGN KEY (matched_label_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5751 |
ALTER TABLE ONLY taxonlabel_relationship
|
|
| 5752 |
ADD CONSTRAINT taxonlabel_relationship_ancestor_id_fkey FOREIGN KEY (ancestor_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5753 | 5753 |
|
| 5754 | 5754 |
|
| 5755 | 5755 |
-- |
| 5756 |
-- Name: taxonlabel_parent_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5756 |
-- Name: taxonlabel_relationship_descendant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5757 | 5757 |
-- |
| 5758 | 5758 |
|
| 5759 |
ALTER TABLE ONLY taxonlabel |
|
| 5760 |
ADD CONSTRAINT taxonlabel_parent_id_fkey FOREIGN KEY (parent_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5759 |
ALTER TABLE ONLY taxonlabel_relationship
|
|
| 5760 |
ADD CONSTRAINT taxonlabel_relationship_descendant_id_fkey FOREIGN KEY (descendant_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5761 | 5761 |
|
| 5762 | 5762 |
|
| 5763 | 5763 |
-- |
Also available in: Unified diff
schemas/vegbien.sql: Renamed taxonlabel_ancestor to taxonlabel_relationship per today's conference call, where it was decided that it would eventually contain asserted relationships (such as synonym and parent) in addition to autopopulated ancestor relationships