Revision 5465
Added by Aaron Marcuse-Kubitza over 11 years ago
| vegbien.sql | ||
|---|---|---|
| 250 | 250 |
|
| 251 | 251 |
|
| 252 | 252 |
-- |
| 253 |
-- Name: _set_canon_taxonconcept(integer, integer, double precision); Type: FUNCTION; Schema: public; Owner: -
|
|
| 253 |
-- Name: _set_matched_taxonconcept(integer, integer, double precision); Type: FUNCTION; Schema: public; Owner: -
|
|
| 254 | 254 |
-- |
| 255 | 255 |
|
| 256 |
CREATE FUNCTION _set_canon_taxonconcept(taxonconcept_id integer, canon_concept_id integer, canon_concept_fit_fraction double precision DEFAULT NULL::double precision) RETURNS integer
|
|
| 256 |
CREATE FUNCTION _set_matched_taxonconcept(taxonconcept_id integer, matched_concept_id integer, matched_concept_fit_fraction double precision DEFAULT NULL::double precision) RETURNS integer
|
|
| 257 | 257 |
LANGUAGE sql |
| 258 | 258 |
AS $_$ |
| 259 | 259 |
UPDATE taxonconcept SET |
| 260 |
canon_concept_id = $2
|
|
| 261 |
, canon_concept_fit_fraction = $3
|
|
| 260 |
matched_concept_id = $2
|
|
| 261 |
, matched_concept_fit_fraction = $3
|
|
| 262 | 262 |
WHERE taxonconcept_id = $1 |
| 263 | 263 |
RETURNING taxonconcept_id |
| 264 | 264 |
$_$; |
| ... | ... | |
| 344 | 344 |
|
| 345 | 345 |
|
| 346 | 346 |
-- |
| 347 |
-- Name: placepath_canon_placepath_id_self_ref(); Type: FUNCTION; Schema: public; Owner: -
|
|
| 347 |
-- Name: placepath_matched_placepath_id_self_ref(); Type: FUNCTION; Schema: public; Owner: -
|
|
| 348 | 348 |
-- |
| 349 | 349 |
|
| 350 |
CREATE FUNCTION placepath_canon_placepath_id_self_ref() RETURNS trigger
|
|
| 350 |
CREATE FUNCTION placepath_matched_placepath_id_self_ref() RETURNS trigger
|
|
| 351 | 351 |
LANGUAGE plpgsql |
| 352 | 352 |
AS $$ |
| 353 | 353 |
BEGIN |
| 354 | 354 |
IF new.placepath_id IS NULL THEN -- prepopulate placepath_id |
| 355 | 355 |
new.placepath_id = nextval('placepath_placepath_id_seq'::regclass);
|
| 356 | 356 |
END IF; |
| 357 |
IF new.canon_placepath_id = 0 THEN -- make self-reference
|
|
| 358 |
new.canon_placepath_id = new.placepath_id;
|
|
| 357 |
IF new.matched_placepath_id = 0 THEN -- make self-reference
|
|
| 358 |
new.matched_placepath_id = new.placepath_id;
|
|
| 359 | 359 |
END IF; |
| 360 | 360 |
RETURN new; |
| 361 | 361 |
END; |
| ... | ... | |
| 380 | 380 |
|
| 381 | 381 |
|
| 382 | 382 |
-- |
| 383 |
-- Name: taxonconcept_0_canon_concept_id_self_ref(); Type: FUNCTION; Schema: public; Owner: -
|
|
| 383 |
-- Name: taxonconcept_0_matched_concept_id_self_ref(); Type: FUNCTION; Schema: public; Owner: -
|
|
| 384 | 384 |
-- |
| 385 | 385 |
|
| 386 |
CREATE FUNCTION taxonconcept_0_canon_concept_id_self_ref() RETURNS trigger
|
|
| 386 |
CREATE FUNCTION taxonconcept_0_matched_concept_id_self_ref() RETURNS trigger
|
|
| 387 | 387 |
LANGUAGE plpgsql |
| 388 | 388 |
AS $$ |
| 389 | 389 |
BEGIN |
| 390 | 390 |
IF new.taxonconcept_id IS NULL THEN -- prepopulate taxonconcept_id |
| 391 | 391 |
new.taxonconcept_id = nextval('taxonconcept_taxonconcept_id_seq'::regclass);
|
| 392 | 392 |
END IF; |
| 393 |
IF new.canon_concept_id = 0 THEN -- make self-reference
|
|
| 394 |
new.canon_concept_id = new.taxonconcept_id;
|
|
| 393 |
IF new.matched_concept_id = 0 THEN -- make self-reference
|
|
| 394 |
new.matched_concept_id = new.taxonconcept_id;
|
|
| 395 | 395 |
END IF; |
| 396 | 396 |
RETURN new; |
| 397 | 397 |
END; |
| ... | ... | |
| 399 | 399 |
|
| 400 | 400 |
|
| 401 | 401 |
-- |
| 402 |
-- Name: taxonconcept_1_canon_concept_min_fit(); Type: FUNCTION; Schema: public; Owner: -
|
|
| 402 |
-- Name: taxonconcept_1_matched_concept_min_fit(); Type: FUNCTION; Schema: public; Owner: -
|
|
| 403 | 403 |
-- |
| 404 | 404 |
|
| 405 |
CREATE FUNCTION taxonconcept_1_canon_concept_min_fit() RETURNS trigger
|
|
| 405 |
CREATE FUNCTION taxonconcept_1_matched_concept_min_fit() RETURNS trigger
|
|
| 406 | 406 |
LANGUAGE plpgsql |
| 407 | 407 |
AS $$ |
| 408 | 408 |
BEGIN |
| 409 |
IF new.canon_concept_id IS NOT NULL
|
|
| 410 |
AND new.canon_concept_fit_fraction < 0.8 THEN -- insufficient match
|
|
| 411 |
new.canon_concept_id = NULL;
|
|
| 412 |
new.canon_concept_fit_fraction = NULL;
|
|
| 409 |
IF new.matched_concept_id IS NOT NULL
|
|
| 410 |
AND new.matched_concept_fit_fraction < 0.8 THEN -- insufficient match
|
|
| 411 |
new.matched_concept_id = NULL;
|
|
| 412 |
new.matched_concept_fit_fraction = NULL;
|
|
| 413 | 413 |
END IF; |
| 414 | 414 |
RETURN new; |
| 415 | 415 |
END; |
| ... | ... | |
| 1019 | 1019 |
placepath_id integer NOT NULL, |
| 1020 | 1020 |
creator_id integer NOT NULL, |
| 1021 | 1021 |
placecode text, |
| 1022 |
canon_placepath_id integer,
|
|
| 1022 |
matched_placepath_id integer,
|
|
| 1023 | 1023 |
place_id integer, |
| 1024 | 1024 |
continent text, |
| 1025 | 1025 |
country text, |
| ... | ... | |
| 1042 | 1042 |
|
| 1043 | 1043 |
|
| 1044 | 1044 |
-- |
| 1045 |
-- Name: COLUMN placepath.canon_placepath_id; Type: COMMENT; Schema: public; Owner: -
|
|
| 1045 |
-- Name: COLUMN placepath.matched_placepath_id; Type: COMMENT; Schema: public; Owner: -
|
|
| 1046 | 1046 |
-- |
| 1047 | 1047 |
|
| 1048 |
COMMENT ON COLUMN placepath.canon_placepath_id IS 'The placepath containing the accepted name of this verbatim place path. placepaths should be linked in a two-level hierarchy of datasource name -> accepted name.
|
|
| 1048 |
COMMENT ON COLUMN placepath.matched_placepath_id IS 'The placepath containing the accepted name of this verbatim place path. placepaths should be linked in a two-level hierarchy of datasource name -> accepted name.
|
|
| 1049 | 1049 |
|
| 1050 | 1050 |
A accepted name should point to itself in this field. This will happen automatically by setting it to the special value 0.'; |
| 1051 | 1051 |
|
| ... | ... | |
| 1138 | 1138 |
creator_id integer NOT NULL, |
| 1139 | 1139 |
creationdate timestamp with time zone, |
| 1140 | 1140 |
accepted_concept_id integer, |
| 1141 |
canon_concept_id integer,
|
|
| 1142 |
canon_concept_fit_fraction double precision,
|
|
| 1141 |
matched_concept_id integer,
|
|
| 1142 |
matched_concept_fit_fraction double precision,
|
|
| 1143 | 1143 |
parent_id integer, |
| 1144 | 1144 |
taxonname text, |
| 1145 | 1145 |
rank taxonrank, |
| ... | ... | |
| 1153 | 1153 |
species text, |
| 1154 | 1154 |
description text, |
| 1155 | 1155 |
accessioncode text, |
| 1156 |
CONSTRAINT taxonconcept_canon_concept_fit_fraction_range CHECK (((canon_concept_fit_fraction >= (0)::double precision) AND (canon_concept_fit_fraction <= (1)::double precision))),
|
|
| 1156 |
CONSTRAINT taxonconcept_matched_concept_fit_fraction_range CHECK (((matched_concept_fit_fraction >= (0)::double precision) AND (matched_concept_fit_fraction <= (1)::double precision))),
|
|
| 1157 | 1157 |
CONSTRAINT taxonconcept_required_key CHECK ((((((taxonname IS NOT NULL) OR (identifyingtaxonomicname IS NOT NULL)) OR (taxonomicname IS NOT NULL)) OR (taxonomicnamewithauthor IS NOT NULL)) OR ((parent_id IS NOT NULL) AND ((creationdate IS NOT NULL) OR (author IS NOT NULL))))) |
| 1158 | 1158 |
); |
| 1159 | 1159 |
|
| ... | ... | |
| 1188 | 1188 |
|
| 1189 | 1189 |
|
| 1190 | 1190 |
-- |
| 1191 |
-- Name: COLUMN taxonconcept.canon_concept_id; Type: COMMENT; Schema: public; Owner: -
|
|
| 1191 |
-- Name: COLUMN taxonconcept.matched_concept_id; Type: COMMENT; Schema: public; Owner: -
|
|
| 1192 | 1192 |
-- |
| 1193 | 1193 |
|
| 1194 |
COMMENT ON COLUMN taxonconcept.canon_concept_id IS 'The taxonconcept containing the closest match to this taxonconcept. taxonconcepts should be linked in a four-level hierarchy of datasource concept -> parsed concept -> matched concept -> accepted concept. A previously-accepted name''s concept should be further linked to the synonym that has replaced it.
|
|
| 1194 |
COMMENT ON COLUMN taxonconcept.matched_concept_id IS 'The taxonconcept containing the closest match to this taxonconcept. taxonconcepts should be linked in a four-level hierarchy of datasource concept -> parsed concept -> matched concept -> accepted concept. A previously-accepted name''s concept should be further linked to the synonym that has replaced it.
|
|
| 1195 | 1195 |
|
| 1196 | 1196 |
To indicate a synonym between taxonconcepts of different sources, choose one taxonconcept to be authoritative and point the other taxonconcept to it using this field. |
| 1197 | 1197 |
|
| ... | ... | |
| 1199 | 1199 |
|
| 1200 | 1200 |
|
| 1201 | 1201 |
-- |
| 1202 |
-- Name: COLUMN taxonconcept.canon_concept_fit_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 1202 |
-- Name: COLUMN taxonconcept.matched_concept_fit_fraction; Type: COMMENT; Schema: public; Owner: -
|
|
| 1203 | 1203 |
-- |
| 1204 | 1204 |
|
| 1205 |
COMMENT ON COLUMN taxonconcept.canon_concept_fit_fraction IS 'The closeness of fit of the canon_concept.';
|
|
| 1205 |
COMMENT ON COLUMN taxonconcept.matched_concept_fit_fraction IS 'The closeness of fit of the matched_concept.';
|
|
| 1206 | 1206 |
|
| 1207 | 1207 |
|
| 1208 | 1208 |
-- |
| ... | ... | |
| 4684 | 4684 |
|
| 4685 | 4685 |
|
| 4686 | 4686 |
-- |
| 4687 |
-- Name: placepath_canon_placepath_id_self_ref; Type: TRIGGER; Schema: public; Owner: -
|
|
| 4687 |
-- Name: placepath_matched_placepath_id_self_ref; Type: TRIGGER; Schema: public; Owner: -
|
|
| 4688 | 4688 |
-- |
| 4689 | 4689 |
|
| 4690 |
CREATE TRIGGER placepath_canon_placepath_id_self_ref BEFORE INSERT OR UPDATE ON placepath FOR EACH ROW EXECUTE PROCEDURE placepath_canon_placepath_id_self_ref();
|
|
| 4690 |
CREATE TRIGGER placepath_matched_placepath_id_self_ref BEFORE INSERT OR UPDATE ON placepath FOR EACH ROW EXECUTE PROCEDURE placepath_matched_placepath_id_self_ref();
|
|
| 4691 | 4691 |
|
| 4692 | 4692 |
|
| 4693 | 4693 |
-- |
| ... | ... | |
| 4698 | 4698 |
|
| 4699 | 4699 |
|
| 4700 | 4700 |
-- |
| 4701 |
-- Name: taxonconcept_0_canon_concept_id_self_ref; Type: TRIGGER; Schema: public; Owner: -
|
|
| 4701 |
-- Name: taxonconcept_0_matched_concept_id_self_ref; Type: TRIGGER; Schema: public; Owner: -
|
|
| 4702 | 4702 |
-- |
| 4703 | 4703 |
|
| 4704 |
CREATE TRIGGER taxonconcept_0_canon_concept_id_self_ref BEFORE INSERT OR UPDATE ON taxonconcept FOR EACH ROW EXECUTE PROCEDURE taxonconcept_0_canon_concept_id_self_ref();
|
|
| 4704 |
CREATE TRIGGER taxonconcept_0_matched_concept_id_self_ref BEFORE INSERT OR UPDATE ON taxonconcept FOR EACH ROW EXECUTE PROCEDURE taxonconcept_0_matched_concept_id_self_ref();
|
|
| 4705 | 4705 |
|
| 4706 | 4706 |
|
| 4707 | 4707 |
-- |
| 4708 |
-- Name: taxonconcept_1_canon_concept_min_fit; Type: TRIGGER; Schema: public; Owner: -
|
|
| 4708 |
-- Name: taxonconcept_1_matched_concept_min_fit; Type: TRIGGER; Schema: public; Owner: -
|
|
| 4709 | 4709 |
-- |
| 4710 | 4710 |
|
| 4711 |
CREATE TRIGGER taxonconcept_1_canon_concept_min_fit BEFORE INSERT OR UPDATE ON taxonconcept FOR EACH ROW EXECUTE PROCEDURE taxonconcept_1_canon_concept_min_fit();
|
|
| 4711 |
CREATE TRIGGER taxonconcept_1_matched_concept_min_fit BEFORE INSERT OR UPDATE ON taxonconcept FOR EACH ROW EXECUTE PROCEDURE taxonconcept_1_matched_concept_min_fit();
|
|
| 4712 | 4712 |
|
| 4713 | 4713 |
|
| 4714 | 4714 |
-- |
| ... | ... | |
| 5279 | 5279 |
|
| 5280 | 5280 |
|
| 5281 | 5281 |
-- |
| 5282 |
-- Name: placepath_canon_placepath_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5282 |
-- Name: placepath_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5283 | 5283 |
-- |
| 5284 | 5284 |
|
| 5285 | 5285 |
ALTER TABLE ONLY placepath |
| 5286 |
ADD CONSTRAINT placepath_canon_placepath_id_fkey FOREIGN KEY (canon_placepath_id) REFERENCES placepath(placepath_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5286 |
ADD CONSTRAINT placepath_creator_id_fkey FOREIGN KEY (creator_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5287 | 5287 |
|
| 5288 | 5288 |
|
| 5289 | 5289 |
-- |
| 5290 |
-- Name: placepath_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5290 |
-- Name: placepath_matched_placepath_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5291 | 5291 |
-- |
| 5292 | 5292 |
|
| 5293 | 5293 |
ALTER TABLE ONLY placepath |
| 5294 |
ADD CONSTRAINT placepath_creator_id_fkey FOREIGN KEY (creator_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5294 |
ADD CONSTRAINT placepath_matched_placepath_id_fkey FOREIGN KEY (matched_placepath_id) REFERENCES placepath(placepath_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5295 | 5295 |
|
| 5296 | 5296 |
|
| 5297 | 5297 |
-- |
| ... | ... | |
| 5519 | 5519 |
|
| 5520 | 5520 |
|
| 5521 | 5521 |
-- |
| 5522 |
-- Name: taxonconcept_canon_concept_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5522 |
-- Name: taxonconcept_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5523 | 5523 |
-- |
| 5524 | 5524 |
|
| 5525 | 5525 |
ALTER TABLE ONLY taxonconcept |
| 5526 |
ADD CONSTRAINT taxonconcept_canon_concept_id_fkey FOREIGN KEY (canon_concept_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5526 |
ADD CONSTRAINT taxonconcept_creator_id_fkey FOREIGN KEY (creator_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5527 | 5527 |
|
| 5528 | 5528 |
|
| 5529 | 5529 |
-- |
| 5530 |
-- Name: taxonconcept_creator_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5530 |
-- Name: taxonconcept_matched_concept_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 5531 | 5531 |
-- |
| 5532 | 5532 |
|
| 5533 | 5533 |
ALTER TABLE ONLY taxonconcept |
| 5534 |
ADD CONSTRAINT taxonconcept_creator_id_fkey FOREIGN KEY (creator_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5534 |
ADD CONSTRAINT taxonconcept_matched_concept_id_fkey FOREIGN KEY (matched_concept_id) REFERENCES taxonconcept(taxonconcept_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 5535 | 5535 |
|
| 5536 | 5536 |
|
| 5537 | 5537 |
-- |
Also available in: Unified diff
schemas/vegbien.sql: taxonconcept: Renamed canon_concept_id to matched_concept_id, because this is actually the closest-match taxonconcept in the match hierarchy (datasource concept -> parsed concept -> matched concept -> accepted concept) rather than the accepted synonym, which goes in accepted_concept_id