/ - Diff - BIEN 3 - NCEAS Projects

« Previous | Next »

Revision 5603

Added by Aaron Marcuse-Kubitza about 12 years ago

schemas/vegbien.sql: taxonconcept_ancestor: Renamed taxonconcept_id to descendant_id to emphasize the direction of the relationship between the two taxonconcepts

     --
     CREATE TABLE taxonconcept_ancestor (
         taxonconcept_id int(11) NOT NULL,
         descendant_id int(11) NOT NULL,
         ancestor_id int(11) NOT NULL
     );
-...
     --
     ALTER TABLE taxonconcept_ancestor
         ADD CONSTRAINT taxonconcept_ancestor_pkey PRIMARY KEY (taxonconcept_id, ancestor_id);
         ADD CONSTRAINT taxonconcept_ancestor_pkey PRIMARY KEY (descendant_id, ancestor_id);
     --
-...
     -- Name: taxonconcept_ancestor_descendants; Type: INDEX; Schema: public; Owner: -; Tablespace:
     --
     CREATE INDEX taxonconcept_ancestor_descendants ON taxonconcept_ancestor  (ancestor_id, taxonconcept_id);
     CREATE INDEX taxonconcept_ancestor_descendants ON taxonconcept_ancestor  (ancestor_id, descendant_id);
     --
-...
     --
     -- Name: taxonconcept_ancestor_taxonconcept_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
     -- Name: taxonconcept_ancestor_descendant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
     --
     ALTER TABLE taxonconcept_ancestor
         ADD CONSTRAINT taxonconcept_ancestor_taxonconcept_id_fkey FOREIGN KEY (taxonconcept_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;
         ADD CONSTRAINT taxonconcept_ancestor_descendant_id_fkey FOREIGN KEY (descendant_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;
     --

                 -- These include the parent itself
                 old_ancestors integer[] := (
                     SELECT array_agg(ancestor_id) FROM taxonconcept_ancestor
                     WHERE taxonconcept_id = old_parent_id
                     WHERE descendant_id = old_parent_id
                 );
                 new_ancestors integer[] := (
                     SELECT array_agg(ancestor_id) FROM taxonconcept_ancestor
                     WHERE taxonconcept_id = parent_id_
                     WHERE descendant_id = parent_id_
                 );
                 descendant_id integer;
                 descendant_id_ integer;
             BEGIN
                 FOR descendant_id IN -- also includes self
                     SELECT taxonconcept_id
                 FOR descendant_id_ IN -- also includes self
                     SELECT descendant_id
                     FROM taxonconcept_ancestor
                     WHERE ancestor_id = new.taxonconcept_id
                 LOOP
                     -- Delete old parent's ancestors
                     DELETE FROM taxonconcept_ancestor
                     WHERE taxonconcept_id = descendant_id
                     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)
                     (descendant_id, ancestor_id)
                     SELECT descendant_id_, unnest(new_ancestors)
+                    ;
                 END LOOP;
             END;
-...
         for, in addition to that leaf node's ancestors. */
         INSERT
         INTO taxonconcept_ancestor
         (taxonconcept_id, ancestor_id)
         (descendant_id, ancestor_id)
         VALUES (new.taxonconcept_id, new.taxonconcept_id)
+        ;
-...
     --
     CREATE TABLE taxonconcept_ancestor (
         taxonconcept_id integer NOT NULL,
         descendant_id integer NOT NULL,
         ancestor_id integer NOT NULL
     );
-...
     --
     ALTER TABLE ONLY taxonconcept_ancestor
         ADD CONSTRAINT taxonconcept_ancestor_pkey PRIMARY KEY (taxonconcept_id, ancestor_id);
         ADD CONSTRAINT taxonconcept_ancestor_pkey PRIMARY KEY (descendant_id, ancestor_id);
     --
-...
     -- Name: taxonconcept_ancestor_descendants; Type: INDEX; Schema: public; Owner: -; Tablespace:
     --
     CREATE INDEX taxonconcept_ancestor_descendants ON taxonconcept_ancestor USING btree (ancestor_id, taxonconcept_id);
     CREATE INDEX taxonconcept_ancestor_descendants ON taxonconcept_ancestor USING btree (ancestor_id, descendant_id);
     --
-...
     --
     -- Name: taxonconcept_ancestor_taxonconcept_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
     -- Name: taxonconcept_ancestor_descendant_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
     --
     ALTER TABLE ONLY taxonconcept_ancestor
         ADD CONSTRAINT taxonconcept_ancestor_taxonconcept_id_fkey FOREIGN KEY (taxonconcept_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;
         ADD CONSTRAINT taxonconcept_ancestor_descendant_id_fkey FOREIGN KEY (descendant_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;
     --

Also available in: Unified diff

Project

General

Profile

Revision 5603

Added by Aaron Marcuse-Kubitza about 12 years ago