Project

General

Profile

1 10778 aaronmk
--
2
-- PostgreSQL database dump
3
--
4 10737 aaronmk
5 10778 aaronmk
SET statement_timeout = 0;
6 11708 aaronmk
SET lock_timeout = 0;
7 10778 aaronmk
SET client_encoding = 'UTF8';
8
SET standard_conforming_strings = on;
9
SET check_function_bodies = false;
10
SET client_min_messages = warning;
11
12 10737 aaronmk
--
13 10778 aaronmk
-- Name: TNRS; Type: SCHEMA; Schema: -; Owner: -
14
--
15 10737 aaronmk
16 10778 aaronmk
--CREATE SCHEMA "TNRS";
17 10725 aaronmk
18
19 11614 aaronmk
--
20
-- Name: SCHEMA "TNRS"; Type: COMMENT; Schema: -; Owner: -
21
--
22
23 13575 aaronmk
COMMENT ON SCHEMA "TNRS" IS '
24
IMPORTANT: when changing this schema, you must also make the same changes on vegbiendev:
25 11617 aaronmk
on vegbiendev:
26
# back up existing TNRS schema (in case of an accidental incorrect change):
27 11614 aaronmk
$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")
28 11616 aaronmk
$ svn up
29
$ svn di
30
# make the changes shown in the diff
31
## to change column types:
32 11614 aaronmk
SELECT util.set_col_types(''"TNRS".tnrs'', ARRAY[
33
  (''col'', ''new_type'')
34 11616 aaronmk
]::util.col_cast[]); -- runtime: 9 min ("531282 ms")
35 11617 aaronmk
$ rm=1 inputs/.TNRS/schema.sql.run
36
# repeat until `svn di` shows no diff
37
# back up new TNRS schema:
38 13575 aaronmk
$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")
39
';
40 11614 aaronmk
41
42 10778 aaronmk
SET search_path = "TNRS", pg_catalog;
43
44
--
45 13506 aaronmk
-- Name: MatchedTaxon_modify(text); Type: FUNCTION; Schema: TNRS; Owner: -
46
--
47
48 13607 aaronmk
CREATE FUNCTION "MatchedTaxon_modify"(view_query text DEFAULT NULL::text) RETURNS void
49 13506 aaronmk
    LANGUAGE sql
50
    AS $_$
51 13646 aaronmk
SELECT util.recreate_view('"TNRS"."MatchedTaxon"', $1);
52 13506 aaronmk
$_$;
53
54
55
--
56 13526 aaronmk
-- Name: FUNCTION "MatchedTaxon_modify"(view_query text); Type: COMMENT; Schema: TNRS; Owner: -
57
--
58
59
COMMENT ON FUNCTION "MatchedTaxon_modify"(view_query text) IS '
60
usage:
61
SELECT "TNRS"."MatchedTaxon_modify"($$
62 13646 aaronmk
SELECT __
63 13526 aaronmk
$$);
64
65
idempotent
66
';
67
68
69
--
70 10778 aaronmk
-- Name: batch__fill(); Type: FUNCTION; Schema: TNRS; Owner: -
71
--
72
73
CREATE FUNCTION batch__fill() RETURNS trigger
74
    LANGUAGE plpgsql
75
    AS $$
76 10728 aaronmk
BEGIN
77
	new.id_by_time = new.time_submitted;
78
	new.id = COALESCE(new.id, new.id_by_time);
79
	RETURN new;
80
END;
81 10778 aaronmk
$$;
82 10728 aaronmk
83
84
--
85 10778 aaronmk
-- Name: family_is_homonym(text); Type: FUNCTION; Schema: TNRS; Owner: -
86
--
87 10728 aaronmk
88 10778 aaronmk
CREATE FUNCTION family_is_homonym(family text) RETURNS boolean
89
    LANGUAGE sql STABLE STRICT
90
    AS $_$
91
SELECT EXISTS(SELECT * FROM "IRMNG".family_homonym_epithet WHERE "taxonNameOrEpithet" = $1)
92
$_$;
93 10736 aaronmk
94 10778 aaronmk
95 10736 aaronmk
--
96 10778 aaronmk
-- Name: genus_is_homonym(text); Type: FUNCTION; Schema: TNRS; Owner: -
97
--
98 10736 aaronmk
99 10778 aaronmk
CREATE FUNCTION genus_is_homonym(genus text) RETURNS boolean
100
    LANGUAGE sql STABLE STRICT
101
    AS $_$
102
SELECT EXISTS(SELECT * FROM "IRMNG".genus_homonym_epithet WHERE "taxonNameOrEpithet" = $1)
103
$_$;
104 7844 aaronmk
105 9985 aaronmk
106 10778 aaronmk
--
107 11709 aaronmk
-- Name: map_taxonomic_status(text, text); Type: FUNCTION; Schema: TNRS; Owner: -
108
--
109
110 13503 aaronmk
CREATE FUNCTION map_taxonomic_status(taxonomic_status_verbatim text, accepted_taxon_name_no_author text) RETURNS text
111 11709 aaronmk
    LANGUAGE sql IMMUTABLE
112
    AS $_$
113 13503 aaronmk
/* from http://wiki.vegpath.org/2013-11-14_conference_call#taxonomic-fields:
114
"taxonomic_status should be accepted instead of synonym when an accepted name is
115
available (this is not always the case when a name is marked as a synonym)" */
116 11709 aaronmk
SELECT CASE WHEN $2 IS NOT NULL THEN 'accepted' ELSE lower($1) END
117
$_$;
118
119
120
--
121 13632 aaronmk
-- Name: taxon_name_is_safe(text); Type: FUNCTION; Schema: TNRS; Owner: -
122
--
123
124
CREATE FUNCTION taxon_name_is_safe(taxon_name text) RETURNS boolean
125
    LANGUAGE sql IMMUTABLE
126
    AS $_$
127
SELECT $1 != ANY("TNRS".unsafe_taxon_names())
128
$_$;
129
130
131
--
132 13528 aaronmk
-- Name: taxon_scrub_modify(text); Type: FUNCTION; Schema: TNRS; Owner: -
133
--
134
135 13607 aaronmk
CREATE FUNCTION taxon_scrub_modify(view_query text DEFAULT NULL::text) RETURNS void
136 13528 aaronmk
    LANGUAGE sql
137
    AS $_$
138
SELECT util.recreate_view('"TNRS".taxon_scrub', $1);
139
$_$;
140
141
142
--
143
-- Name: FUNCTION taxon_scrub_modify(view_query text); Type: COMMENT; Schema: TNRS; Owner: -
144
--
145
146
COMMENT ON FUNCTION taxon_scrub_modify(view_query text) IS '
147
usage:
148
SELECT "TNRS".taxon_scrub_modify($$
149 13646 aaronmk
SELECT __
150 13528 aaronmk
$$);
151
152
idempotent
153
';
154
155
156
--
157 13567 aaronmk
-- Name: tnrs__batch_begin(); Type: FUNCTION; Schema: TNRS; Owner: -
158
--
159
160
CREATE FUNCTION tnrs__batch_begin() RETURNS trigger
161
    LANGUAGE plpgsql
162
    AS $$
163
BEGIN
164
	PERFORM util.seq__reset('pg_temp.tnrs__match_num__seq', 0);
165
	RETURN NULL;
166
END;
167
$$;
168
169
170
--
171 13578 aaronmk
-- Name: tnrs__match_num__fill(); Type: FUNCTION; Schema: TNRS; Owner: -
172
--
173
174
CREATE FUNCTION tnrs__match_num__fill() RETURNS trigger
175
    LANGUAGE plpgsql
176
    AS $$
177
BEGIN
178 13585 aaronmk
	IF new.match_num IS NULL THEN
179
		new.match_num = "TNRS".tnrs__match_num__next();
180
	END IF;
181 13578 aaronmk
	RETURN new;
182
END;
183
$$;
184
185
186
--
187 13570 aaronmk
-- Name: tnrs__match_num__next(); Type: FUNCTION; Schema: TNRS; Owner: -
188
--
189
190
CREATE FUNCTION tnrs__match_num__next() RETURNS bigint
191
    LANGUAGE sql
192
    AS $$
193
SELECT nextval('pg_temp.tnrs__match_num__seq');
194
$$;
195
196
197
--
198 10778 aaronmk
-- Name: tnrs_populate_fields(); Type: FUNCTION; Schema: TNRS; Owner: -
199
--
200
201
CREATE FUNCTION tnrs_populate_fields() RETURNS trigger
202
    LANGUAGE plpgsql
203
    AS $$
204 9763 aaronmk
DECLARE
205 11628 aaronmk
	"Specific_epithet_is_plant" boolean :=
206
		(CASE
207
		WHEN   new."Infraspecific_epithet_matched"	 IS NOT NULL
208
			OR new."Infraspecific_epithet_2_matched" IS NOT NULL
209
			OR new."Specific_epithet_score" >= 0.9 -- fuzzy match
210
			THEN true
211
		ELSE NULL -- ambiguous
212
		END);
213
	never_homonym boolean = COALESCE(new."Author_score" >= 0.6, false);
214
		-- author disambiguates
215
	family_is_homonym boolean = NOT never_homonym
216
		AND "TNRS".family_is_homonym(new."Family_matched");
217
	genus_is_homonym  boolean = NOT never_homonym
218
		AND "TNRS".genus_is_homonym(new."Genus_matched");
219
	matched_taxon_name_with_author text = NULLIF(concat_ws(' '
220
		, NULLIF(NULLIF(new."Name_matched_accepted_family", 'Unknown'),
221
			new."Name_matched")
222
		, NULLIF(new."Name_matched", 'No suitable matches found.')
223
		, new."Name_matched_author"
224
		), '');
225 11643 aaronmk
	accepted_taxon_name_with_author text = NULLIF(concat_ws(' '
226 11628 aaronmk
		, NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'),
227
			new."Accepted_name")
228
		, new."Accepted_name"
229
		, new."Accepted_name_author"
230
		), '');
231 11643 aaronmk
BEGIN
232 11629 aaronmk
	/* exclude homonyms because these are not valid matches (i.e. TNRS provides
233
	a name, but the name is not meaningful because it is not unambiguous). */
234 11711 aaronmk
	new.is_valid_match = new."Taxonomic_status" != 'Invalid'
235
		AND COALESCE(CASE
236 11628 aaronmk
		WHEN new."Family_score" = 1 AND NOT family_is_homonym -- exact match
237
			THEN true
238
		ELSE -- consider genus
239
			(CASE
240
			WHEN new."Genus_score" =  1	   -- exact match
241
				THEN
242
				(CASE
243
				WHEN NOT genus_is_homonym THEN true
244
				ELSE "Specific_epithet_is_plant"
245
				END)
246
			WHEN new."Genus_score" >= 0.85 -- fuzzy match
247
				THEN "Specific_epithet_is_plant"
248
			ELSE NULL -- ambiguous
249
			END)
250 11629 aaronmk
		END, false);
251 11632 aaronmk
	new.scrubbed_unique_taxon_name = COALESCE(
252 11643 aaronmk
		accepted_taxon_name_with_author, matched_taxon_name_with_author);
253 11628 aaronmk
254
	RETURN new;
255 7134 aaronmk
END;
256 10778 aaronmk
$$;
257
258
259
--
260
-- Name: FUNCTION tnrs_populate_fields(); Type: COMMENT; Schema: TNRS; Owner: -
261
--
262
263 13575 aaronmk
COMMENT ON FUNCTION tnrs_populate_fields() IS '
264
IMPORTANT: when changing this function, you must regenerate the derived cols:
265 10754 aaronmk
UPDATE "TNRS".tnrs SET "Name_submitted" = "Name_submitted"
266 11715 aaronmk
runtime: 30 min ("5564082 rows affected, 1918900 ms execution time")
267 10754 aaronmk
VACUUM ANALYZE "TNRS".tnrs -- to remove previous rows
268 13575 aaronmk
runtime: 1.5 min ("92633 ms")
269
';
270 7134 aaronmk
271 7251 aaronmk
272 13631 aaronmk
--
273
-- Name: unsafe_taxon_names(); Type: FUNCTION; Schema: TNRS; Owner: -
274
--
275
276
CREATE FUNCTION unsafe_taxon_names() RETURNS text[]
277
    LANGUAGE sql IMMUTABLE
278
    AS $$
279
SELECT ARRAY[
280
'Lamiaceae Agastache pallidiflora (Heller) Rydb. spp. pallidiflora var. gilensis R.W. Sanders'
281
]
282
$$;
283
284
285 10778 aaronmk
SET default_tablespace = '';
286
287
SET default_with_oids = false;
288
289 10728 aaronmk
--
290 10778 aaronmk
-- Name: tnrs; Type: TABLE; Schema: TNRS; Owner: -; Tablespace:
291
--
292 7251 aaronmk
293 10778 aaronmk
CREATE TABLE tnrs (
294
    batch text DEFAULT now() NOT NULL,
295 13580 aaronmk
    match_num integer NOT NULL,
296 10778 aaronmk
    "Name_number" integer NOT NULL,
297
    "Name_submitted" text NOT NULL,
298
    "Overall_score" double precision,
299
    "Name_matched" text,
300
    "Name_matched_rank" text,
301
    "Name_score" double precision,
302
    "Name_matched_author" text,
303
    "Name_matched_url" text,
304
    "Author_matched" text,
305
    "Author_score" double precision,
306
    "Family_matched" text,
307
    "Family_score" double precision,
308
    "Name_matched_accepted_family" text,
309
    "Genus_matched" text,
310
    "Genus_score" double precision,
311
    "Specific_epithet_matched" text,
312
    "Specific_epithet_score" double precision,
313
    "Infraspecific_rank" text,
314
    "Infraspecific_epithet_matched" text,
315
    "Infraspecific_epithet_score" double precision,
316
    "Infraspecific_rank_2" text,
317
    "Infraspecific_epithet_2_matched" text,
318
    "Infraspecific_epithet_2_score" double precision,
319
    "Annotations" text,
320
    "Unmatched_terms" text,
321
    "Taxonomic_status" text,
322
    "Accepted_name" text,
323
    "Accepted_name_author" text,
324
    "Accepted_name_rank" text,
325
    "Accepted_name_url" text,
326
    "Accepted_name_species" text,
327
    "Accepted_name_family" text,
328
    "Selected" text,
329
    "Source" text,
330
    "Warnings" text,
331
    "Accepted_name_lsid" text,
332 11628 aaronmk
    is_valid_match boolean NOT NULL,
333 11632 aaronmk
    scrubbed_unique_taxon_name text
334 10778 aaronmk
);
335 7823 aaronmk
336 9759 aaronmk
337 10778 aaronmk
--
338
-- Name: TABLE tnrs; Type: COMMENT; Schema: TNRS; Owner: -
339
--
340 7823 aaronmk
341 13575 aaronmk
COMMENT ON TABLE tnrs IS '
342 13577 aaronmk
to remove columns or add columns at the end:
343
$ rm=1 inputs/.TNRS/data.sql.run
344
$ make schemas/remake
345
346
to add columns in the middle:
347
make the changes in inputs/.TNRS/schema.sql
348
$ inputs/.TNRS/data.sql.run refresh # re-run TNRS
349
$ make schemas/remake
350 13582 aaronmk
351
to populate a new column:
352
ALTER TABLE "TNRS".tnrs DISABLE TRIGGER tnrs_populate_fields; --speeds up update
353
UPDATE "TNRS".tnrs SET "col" = value;
354
	-- runtime: 30 min ("5564201 rows affected, 1624829 ms execution time")
355
ALTER TABLE "TNRS".tnrs ENABLE TRIGGER tnrs_populate_fields;
356
VACUUM ANALYZE "TNRS".tnrs --remove previous rows; runtime: 1.5 min ("92633 ms")
357 13584 aaronmk
358
to add a constraint: runtime: 3 min ("173620 ms")
359 13575 aaronmk
';
360 10778 aaronmk
361
362
--
363
-- Name: MatchedTaxon; Type: VIEW; Schema: TNRS; Owner: -
364
--
365
366
CREATE VIEW "MatchedTaxon" AS
367 13498 aaronmk
 SELECT s."*Name_matched.batch",
368
    s."concatenatedScientificName",
369
    s."matchedTaxonName",
370
    s."matchedTaxonRank",
371
    s."*Name_matched.Name_score",
372
    s."matchedScientificNameAuthorship",
373
    s."matchedScientificNameID",
374
    s."*Name_matched.Author_score",
375
    s."matchedFamilyConfidence_fraction",
376
    s."matchedFamily",
377
    s."matchedGenus",
378
    s."matchedGenusConfidence_fraction",
379
    s."matchedSpecificEpithet",
380
    s."matchedSpeciesConfidence_fraction",
381
    s."matchedInfraspecificEpithet",
382
    s."*Name_matched.Infraspecific_epithet_score",
383
    s."identificationQualifier",
384
    s."morphospeciesSuffix",
385
    s."taxonomicStatus",
386
    s.accepted_taxon_name_no_author,
387
    s.accepted_author,
388
    s.accepted_taxon_rank,
389
    s."acceptedScientificNameID",
390
    s.accepted_species_binomial,
391
    s.accepted_family,
392
    s."*Name_matched.Selected",
393
    s."*Name_matched.Source",
394
    s."*Name_matched.Warnings",
395
    s."*Name_matched.Accepted_name_lsid",
396
    s.taxon_scrub__is_valid_match,
397
    s.scrubbed_unique_taxon_name,
398
        CASE
399
            WHEN (s.accepted_taxon_rank = 'family'::text) THEN concat_ws(' '::text, s.accepted_family, s."morphospeciesSuffix")
400
            WHEN (s.accepted_taxon_rank = 'genus'::text) THEN concat_ws(' '::text, s.accepted_taxon_name_no_author, s."morphospeciesSuffix")
401
            ELSE s.accepted_species_binomial
402
        END AS accepted_morphospecies_binomial
403
   FROM ( SELECT tnrs.batch AS "*Name_matched.batch",
404
            tnrs."Name_submitted" AS "concatenatedScientificName",
405
            tnrs."Name_matched" AS "matchedTaxonName",
406
            tnrs."Name_matched_rank" AS "matchedTaxonRank",
407
            tnrs."Name_score" AS "*Name_matched.Name_score",
408
            tnrs."Name_matched_author" AS "matchedScientificNameAuthorship",
409
            tnrs."Name_matched_url" AS "matchedScientificNameID",
410
            tnrs."Author_score" AS "*Name_matched.Author_score",
411
            tnrs."Family_score" AS "matchedFamilyConfidence_fraction",
412
            COALESCE(tnrs."Name_matched_accepted_family", tnrs."Accepted_name_family") AS "matchedFamily",
413
            tnrs."Genus_matched" AS "matchedGenus",
414
            tnrs."Genus_score" AS "matchedGenusConfidence_fraction",
415
            tnrs."Specific_epithet_matched" AS "matchedSpecificEpithet",
416
            tnrs."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction",
417
            tnrs."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet",
418
            tnrs."Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score",
419
            tnrs."Annotations" AS "identificationQualifier",
420
            tnrs."Unmatched_terms" AS "morphospeciesSuffix",
421 13508 aaronmk
            map_taxonomic_status(tnrs."Taxonomic_status", tnrs."Accepted_name") AS "taxonomicStatus",
422 13498 aaronmk
            tnrs."Accepted_name" AS accepted_taxon_name_no_author,
423
            tnrs."Accepted_name_author" AS accepted_author,
424
            tnrs."Accepted_name_rank" AS accepted_taxon_rank,
425
            tnrs."Accepted_name_url" AS "acceptedScientificNameID",
426
            tnrs."Accepted_name_species" AS accepted_species_binomial,
427
            tnrs."Accepted_name_family" AS accepted_family,
428
            tnrs."Selected" AS "*Name_matched.Selected",
429
            tnrs."Source" AS "*Name_matched.Source",
430
            tnrs."Warnings" AS "*Name_matched.Warnings",
431
            tnrs."Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid",
432
            tnrs.is_valid_match AS taxon_scrub__is_valid_match,
433
            tnrs.scrubbed_unique_taxon_name
434
           FROM tnrs) s;
435 10778 aaronmk
436
437
--
438 13498 aaronmk
-- Name: VIEW "MatchedTaxon"; Type: COMMENT; Schema: TNRS; Owner: -
439
--
440
441
COMMENT ON VIEW "MatchedTaxon" IS '
442 13501 aaronmk
to modify:
443 13507 aaronmk
SELECT "TNRS"."MatchedTaxon_modify"($$
444
 SELECT *, __ AS accepted_morphospecies_binomial
445
   FROM __
446 13501 aaronmk
$$);
447 13498 aaronmk
';
448
449
450
--
451 10778 aaronmk
-- Name: ValidMatchedTaxon; Type: VIEW; Schema: TNRS; Owner: -
452
--
453
454
CREATE VIEW "ValidMatchedTaxon" AS
455 11708 aaronmk
 SELECT "MatchedTaxon"."*Name_matched.batch",
456
    "MatchedTaxon"."concatenatedScientificName",
457
    "MatchedTaxon"."matchedTaxonName",
458
    "MatchedTaxon"."matchedTaxonRank",
459
    "MatchedTaxon"."*Name_matched.Name_score",
460
    "MatchedTaxon"."matchedScientificNameAuthorship",
461
    "MatchedTaxon"."matchedScientificNameID",
462
    "MatchedTaxon"."*Name_matched.Author_score",
463
    "MatchedTaxon"."matchedFamilyConfidence_fraction",
464
    "MatchedTaxon"."matchedFamily",
465
    "MatchedTaxon"."matchedGenus",
466
    "MatchedTaxon"."matchedGenusConfidence_fraction",
467
    "MatchedTaxon"."matchedSpecificEpithet",
468
    "MatchedTaxon"."matchedSpeciesConfidence_fraction",
469
    "MatchedTaxon"."matchedInfraspecificEpithet",
470
    "MatchedTaxon"."*Name_matched.Infraspecific_epithet_score",
471
    "MatchedTaxon"."identificationQualifier",
472
    "MatchedTaxon"."morphospeciesSuffix",
473
    "MatchedTaxon"."taxonomicStatus",
474 13441 aaronmk
    "MatchedTaxon".accepted_taxon_name_no_author,
475
    "MatchedTaxon".accepted_author,
476
    "MatchedTaxon".accepted_taxon_rank,
477 11708 aaronmk
    "MatchedTaxon"."acceptedScientificNameID",
478 13444 aaronmk
    "MatchedTaxon".accepted_species_binomial,
479 13441 aaronmk
    "MatchedTaxon".accepted_family,
480 11708 aaronmk
    "MatchedTaxon"."*Name_matched.Selected",
481
    "MatchedTaxon"."*Name_matched.Source",
482
    "MatchedTaxon"."*Name_matched.Warnings",
483
    "MatchedTaxon"."*Name_matched.Accepted_name_lsid",
484
    "MatchedTaxon".taxon_scrub__is_valid_match,
485 13498 aaronmk
    "MatchedTaxon".scrubbed_unique_taxon_name,
486
    "MatchedTaxon".accepted_morphospecies_binomial
487 11708 aaronmk
   FROM "MatchedTaxon"
488
  WHERE "MatchedTaxon".taxon_scrub__is_valid_match;
489 10778 aaronmk
490
491
--
492
-- Name: VIEW "ValidMatchedTaxon"; Type: COMMENT; Schema: TNRS; Owner: -
493
--
494
495 13443 aaronmk
COMMENT ON VIEW "ValidMatchedTaxon" IS '
496
to update, use * as the column list
497
';
498 10778 aaronmk
499
500
--
501
-- Name: batch; Type: TABLE; Schema: TNRS; Owner: -; Tablespace:
502
--
503
504
CREATE TABLE batch (
505
    id text NOT NULL,
506
    id_by_time text,
507
    time_submitted timestamp with time zone DEFAULT now(),
508
    client_version text
509
);
510
511
512
--
513
-- Name: batch_download_settings; Type: TABLE; Schema: TNRS; Owner: -; Tablespace:
514
--
515
516
CREATE TABLE batch_download_settings (
517
    id text NOT NULL,
518
    "E-mail" text,
519
    "Id" text,
520
    "Job type" text,
521
    "Contains Id" boolean,
522
    "Start time" text,
523
    "Finish time" text,
524
    "TNRS version" text,
525
    "Sources selected" text,
526
    "Match threshold" double precision,
527
    "Classification" text,
528
    "Allow partial matches?" boolean,
529
    "Sort by source" boolean,
530
    "Constrain by higher taxonomy" boolean
531
);
532
533
534
--
535
-- Name: TABLE batch_download_settings; Type: COMMENT; Schema: TNRS; Owner: -
536
--
537
538 13575 aaronmk
COMMENT ON TABLE batch_download_settings IS '
539
stores data from http://tnrs.iplantcollaborative.org/TNRSapp.html > Submit List > results section > Download settings > settings.txt
540
';
541 10778 aaronmk
542
543
--
544
-- Name: client_version; Type: TABLE; Schema: TNRS; Owner: -; Tablespace:
545
--
546
547
CREATE TABLE client_version (
548
    id text NOT NULL,
549
    global_rev integer NOT NULL,
550
    "/lib/tnrs.py rev" integer,
551
    "/bin/tnrs_db rev" integer
552
);
553
554
555
--
556
-- Name: TABLE client_version; Type: COMMENT; Schema: TNRS; Owner: -
557
--
558
559 13575 aaronmk
COMMENT ON TABLE client_version IS '
560
contains svn revisions
561
';
562 10778 aaronmk
563
564
--
565
-- Name: COLUMN client_version.global_rev; Type: COMMENT; Schema: TNRS; Owner: -
566
--
567
568 13575 aaronmk
COMMENT ON COLUMN client_version.global_rev IS '
569
from `svn info .` > Last Changed Rev
570
';
571 10778 aaronmk
572
573
--
574
-- Name: COLUMN client_version."/lib/tnrs.py rev"; Type: COMMENT; Schema: TNRS; Owner: -
575
--
576
577 13575 aaronmk
COMMENT ON COLUMN client_version."/lib/tnrs.py rev" IS '
578
from `svn info lib/tnrs.py` > Last Changed Rev
579
';
580 10778 aaronmk
581
582
--
583
-- Name: COLUMN client_version."/bin/tnrs_db rev"; Type: COMMENT; Schema: TNRS; Owner: -
584
--
585
586 13575 aaronmk
COMMENT ON COLUMN client_version."/bin/tnrs_db rev" IS '
587
from `svn info bin/tnrs_db` > Last Changed Rev
588
';
589 10778 aaronmk
590
591
--
592 11964 aaronmk
-- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: VIEW; Schema: TNRS; Owner: -
593
--
594
595
CREATE VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" AS
596
 SELECT tnrs."Name_submitted" AS scrubbed_unique_taxon_name,
597 13441 aaronmk
    tnrs."Name_matched_rank" AS scrubbed_taxon_rank,
598
    COALESCE(tnrs."Name_matched_accepted_family", tnrs."Family_matched") AS scrubbed_family,
599 11964 aaronmk
    tnrs."Genus_matched" AS scrubbed_genus,
600
    tnrs."Specific_epithet_matched" AS scrubbed_specific_epithet,
601
    tnrs."Infraspecific_rank" AS scrubbed_infraspecific_rank,
602
    tnrs."Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet,
603 13441 aaronmk
    tnrs."Name_matched_author" AS scrubbed_author,
604 13540 aaronmk
    tnrs."Name_matched" AS scrubbed_taxon_name_no_author,
605
    (tnrs."Name_matched" || COALESCE((' '::text || tnrs."Name_matched_author"), ''::text)) AS scrubbed_taxon_name_with_author
606 11964 aaronmk
   FROM tnrs;
607
608
609
--
610 11965 aaronmk
-- Name: VIEW "taxon_scrub.scrubbed_unique_taxon_name.*"; Type: COMMENT; Schema: TNRS; Owner: -
611
--
612
613 13575 aaronmk
COMMENT ON VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" IS '
614
scrubbed_family: Name_matched_accepted_family was missing from the TNRS results at one point, so Family_matched is used as a workaround to populate this. the workaround is for *accepted names only*, as no opinion names do not have an Accepted_name_family to prepend to the scrubbed name to parse.
615
';
616 11965 aaronmk
617
618
--
619 11964 aaronmk
-- Name: taxon_scrub; Type: VIEW; Schema: TNRS; Owner: -
620
--
621
622
CREATE VIEW taxon_scrub AS
623
 SELECT "ValidMatchedTaxon".scrubbed_unique_taxon_name,
624
    "ValidMatchedTaxon"."*Name_matched.batch",
625
    "ValidMatchedTaxon"."concatenatedScientificName",
626
    "ValidMatchedTaxon"."matchedTaxonName",
627
    "ValidMatchedTaxon"."matchedTaxonRank",
628
    "ValidMatchedTaxon"."*Name_matched.Name_score",
629
    "ValidMatchedTaxon"."matchedScientificNameAuthorship",
630
    "ValidMatchedTaxon"."matchedScientificNameID",
631
    "ValidMatchedTaxon"."*Name_matched.Author_score",
632
    "ValidMatchedTaxon"."matchedFamilyConfidence_fraction",
633
    "ValidMatchedTaxon"."matchedFamily",
634
    "ValidMatchedTaxon"."matchedGenus",
635
    "ValidMatchedTaxon"."matchedGenusConfidence_fraction",
636
    "ValidMatchedTaxon"."matchedSpecificEpithet",
637
    "ValidMatchedTaxon"."matchedSpeciesConfidence_fraction",
638
    "ValidMatchedTaxon"."matchedInfraspecificEpithet",
639
    "ValidMatchedTaxon"."*Name_matched.Infraspecific_epithet_score",
640
    "ValidMatchedTaxon"."identificationQualifier",
641
    "ValidMatchedTaxon"."morphospeciesSuffix",
642
    "ValidMatchedTaxon"."taxonomicStatus",
643 13441 aaronmk
    "ValidMatchedTaxon".accepted_taxon_name_no_author,
644
    "ValidMatchedTaxon".accepted_author,
645
    "ValidMatchedTaxon".accepted_taxon_rank,
646 11964 aaronmk
    "ValidMatchedTaxon"."acceptedScientificNameID",
647 13444 aaronmk
    "ValidMatchedTaxon".accepted_species_binomial,
648 13441 aaronmk
    "ValidMatchedTaxon".accepted_family,
649 11964 aaronmk
    "ValidMatchedTaxon"."*Name_matched.Selected",
650
    "ValidMatchedTaxon"."*Name_matched.Source",
651
    "ValidMatchedTaxon"."*Name_matched.Warnings",
652
    "ValidMatchedTaxon"."*Name_matched.Accepted_name_lsid",
653
    "ValidMatchedTaxon".taxon_scrub__is_valid_match,
654 13498 aaronmk
    "ValidMatchedTaxon".accepted_morphospecies_binomial,
655 13441 aaronmk
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank,
656
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family,
657 11964 aaronmk
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus,
658
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet,
659
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_rank,
660
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_epithet,
661 13441 aaronmk
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_author,
662 13532 aaronmk
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author,
663 13540 aaronmk
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_with_author,
664 13532 aaronmk
        CASE
665
            WHEN ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank = 'family'::text) THEN concat_ws(' '::text, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family, "ValidMatchedTaxon"."morphospeciesSuffix")
666
            WHEN ("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank = 'genus'::text) THEN concat_ws(' '::text, "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author, "ValidMatchedTaxon"."morphospeciesSuffix")
667
            ELSE (("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus || ' '::text) || "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet)
668
        END AS scrubbed_morphospecies_binomial
669 11964 aaronmk
   FROM ("ValidMatchedTaxon"
670
   LEFT JOIN "taxon_scrub.scrubbed_unique_taxon_name.*" USING (scrubbed_unique_taxon_name));
671
672
673
--
674
-- Name: VIEW taxon_scrub; Type: COMMENT; Schema: TNRS; Owner: -
675
--
676
677 13443 aaronmk
COMMENT ON VIEW taxon_scrub IS '
678 13531 aaronmk
to modify:
679
SELECT "TNRS".taxon_scrub_modify($$
680 13533 aaronmk
 SELECT *, __ AS scrubbed_morphospecies_binomial
681 13531 aaronmk
   FROM "TNRS"."ValidMatchedTaxon"
682
   LEFT JOIN "TNRS"."taxon_scrub.scrubbed_unique_taxon_name.*" USING (scrubbed_unique_taxon_name);
683
$$);
684 13443 aaronmk
';
685 11964 aaronmk
686
687
--
688 10778 aaronmk
-- Name: batch_download_settings_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
689
--
690
691
ALTER TABLE ONLY batch_download_settings
692
    ADD CONSTRAINT batch_download_settings_pkey PRIMARY KEY (id);
693
694
695
--
696
-- Name: batch_id_by_time_key; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
697
--
698
699
ALTER TABLE ONLY batch
700
    ADD CONSTRAINT batch_id_by_time_key UNIQUE (id_by_time);
701
702
703
--
704
-- Name: batch_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
705
--
706
707
ALTER TABLE ONLY batch
708
    ADD CONSTRAINT batch_pkey PRIMARY KEY (id);
709
710
711
--
712
-- Name: client_version_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
713
--
714
715
ALTER TABLE ONLY client_version
716
    ADD CONSTRAINT client_version_pkey PRIMARY KEY (id);
717
718
719
--
720
-- Name: tnrs_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
721
--
722
723
ALTER TABLE ONLY tnrs
724 13581 aaronmk
    ADD CONSTRAINT tnrs_pkey PRIMARY KEY (batch, match_num);
725 10778 aaronmk
726
727
--
728 10793 aaronmk
-- Name: batch_client_version_idx; Type: INDEX; Schema: TNRS; Owner: -; Tablespace:
729
--
730
731
CREATE INDEX batch_client_version_idx ON batch USING btree (client_version);
732
733
734
--
735 13589 aaronmk
-- Name: tnrs_Name_submitted_idx; Type: INDEX; Schema: TNRS; Owner: -; Tablespace:
736
--
737
738
CREATE INDEX "tnrs_Name_submitted_idx" ON tnrs USING btree ("Name_submitted");
739
740
741
--
742 11607 aaronmk
-- Name: tnrs__valid_match; Type: INDEX; Schema: TNRS; Owner: -; Tablespace:
743
--
744
745 13586 aaronmk
CREATE INDEX tnrs__valid_match ON tnrs USING btree ("Name_submitted") WHERE is_valid_match;
746 11607 aaronmk
747
748
--
749 10778 aaronmk
-- Name: batch__fill; Type: TRIGGER; Schema: TNRS; Owner: -
750
--
751
752
CREATE TRIGGER batch__fill BEFORE INSERT OR UPDATE ON batch FOR EACH ROW EXECUTE PROCEDURE batch__fill();
753
754
755
--
756 13567 aaronmk
-- Name: tnrs__batch_begin; Type: TRIGGER; Schema: TNRS; Owner: -
757
--
758
759
CREATE TRIGGER tnrs__batch_begin BEFORE INSERT ON tnrs FOR EACH STATEMENT EXECUTE PROCEDURE tnrs__batch_begin();
760
761
762
--
763 13578 aaronmk
-- Name: tnrs__match_num__fill; Type: TRIGGER; Schema: TNRS; Owner: -
764
--
765
766
CREATE TRIGGER tnrs__match_num__fill BEFORE INSERT ON tnrs FOR EACH ROW EXECUTE PROCEDURE tnrs__match_num__fill();
767
768
769
--
770 10778 aaronmk
-- Name: tnrs_populate_fields; Type: TRIGGER; Schema: TNRS; Owner: -
771
--
772
773
CREATE TRIGGER tnrs_populate_fields BEFORE INSERT OR UPDATE ON tnrs FOR EACH ROW EXECUTE PROCEDURE tnrs_populate_fields();
774
775
776
--
777
-- Name: batch_client_version_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: -
778
--
779
780
ALTER TABLE ONLY batch
781
    ADD CONSTRAINT batch_client_version_fkey FOREIGN KEY (client_version) REFERENCES client_version(id) ON UPDATE CASCADE ON DELETE CASCADE;
782
783
784
--
785
-- Name: batch_download_settings_id_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: -
786
--
787
788
ALTER TABLE ONLY batch_download_settings
789
    ADD CONSTRAINT batch_download_settings_id_fkey FOREIGN KEY (id) REFERENCES batch(id) ON UPDATE CASCADE ON DELETE CASCADE;
790
791
792
--
793
-- Name: tnrs_batch_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: -
794
--
795
796
ALTER TABLE ONLY tnrs
797
    ADD CONSTRAINT tnrs_batch_fkey FOREIGN KEY (batch) REFERENCES batch(id) ON UPDATE CASCADE ON DELETE CASCADE;
798
799
800
--
801
-- Name: TNRS; Type: ACL; Schema: -; Owner: -
802
--
803
804
REVOKE ALL ON SCHEMA "TNRS" FROM PUBLIC;
805
REVOKE ALL ON SCHEMA "TNRS" FROM bien;
806
GRANT ALL ON SCHEMA "TNRS" TO bien;
807
GRANT USAGE ON SCHEMA "TNRS" TO bien_read;
808
809
810
--
811
-- Name: tnrs; Type: ACL; Schema: TNRS; Owner: -
812
--
813
814
REVOKE ALL ON TABLE tnrs FROM PUBLIC;
815
REVOKE ALL ON TABLE tnrs FROM bien;
816
GRANT ALL ON TABLE tnrs TO bien;
817
GRANT SELECT ON TABLE tnrs TO bien_read;
818
819
820
--
821 11912 aaronmk
-- Name: MatchedTaxon; Type: ACL; Schema: TNRS; Owner: -
822
--
823
824
REVOKE ALL ON TABLE "MatchedTaxon" FROM PUBLIC;
825
REVOKE ALL ON TABLE "MatchedTaxon" FROM bien;
826
GRANT ALL ON TABLE "MatchedTaxon" TO bien;
827
GRANT SELECT ON TABLE "MatchedTaxon" TO bien_read;
828
829
830
--
831
-- Name: ValidMatchedTaxon; Type: ACL; Schema: TNRS; Owner: -
832
--
833
834
REVOKE ALL ON TABLE "ValidMatchedTaxon" FROM PUBLIC;
835
REVOKE ALL ON TABLE "ValidMatchedTaxon" FROM bien;
836
GRANT ALL ON TABLE "ValidMatchedTaxon" TO bien;
837
GRANT SELECT ON TABLE "ValidMatchedTaxon" TO bien_read;
838
839
840
--
841
-- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: ACL; Schema: TNRS; Owner: -
842
--
843
844
REVOKE ALL ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" FROM PUBLIC;
845
REVOKE ALL ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" FROM bien;
846
GRANT ALL ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" TO bien;
847
GRANT SELECT ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" TO bien_read;
848
849
850
--
851
-- Name: taxon_scrub; Type: ACL; Schema: TNRS; Owner: -
852
--
853
854
REVOKE ALL ON TABLE taxon_scrub FROM PUBLIC;
855
REVOKE ALL ON TABLE taxon_scrub FROM bien;
856
GRANT ALL ON TABLE taxon_scrub TO bien;
857
GRANT SELECT ON TABLE taxon_scrub TO bien_read;
858
859
860
--
861 10778 aaronmk
-- PostgreSQL database dump complete
862
--