Project

General

Profile

1
--
2
-- PostgreSQL database dump
3
--
4

    
5
SET statement_timeout = 0;
6
SET lock_timeout = 0;
7
SET client_encoding = 'UTF8';
8
SET standard_conforming_strings = on;
9
SET check_function_bodies = false;
10
SET client_min_messages = warning;
11

    
12
--
13
-- Name: TNRS; Type: SCHEMA; Schema: -; Owner: -
14
--
15

    
16
--CREATE SCHEMA "TNRS";
17

    
18

    
19
--
20
-- Name: SCHEMA "TNRS"; Type: COMMENT; Schema: -; Owner: -
21
--
22

    
23
COMMENT ON SCHEMA "TNRS" IS '
24
IMPORTANT: when changing this schema, you must also make the same changes on vegbiendev:
25
on vegbiendev:
26
# back up existing TNRS schema (in case of an accidental incorrect change):
27
$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")
28
$ svn up
29
$ svn di
30
# make the changes shown in the diff
31
## to change column types:
32
SELECT util.set_col_types(''"TNRS".tnrs'', ARRAY[
33
  (''col'', ''new_type'')
34
]::util.col_cast[]); -- runtime: 9 min ("531282 ms")
35
$ rm=1 inputs/.TNRS/schema.sql.run
36
# repeat until `svn di` shows no diff
37
# back up new TNRS schema:
38
$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")
39
';
40

    
41

    
42
SET search_path = "TNRS", pg_catalog;
43

    
44
--
45
-- Name: MatchedTaxon_modify(text); Type: FUNCTION; Schema: TNRS; Owner: -
46
--
47

    
48
CREATE FUNCTION "MatchedTaxon_modify"(view_query text DEFAULT NULL::text) RETURNS void
49
    LANGUAGE sql
50
    AS $_$
51
SELECT util.recreate_view('"TNRS"."MatchedTaxon"', $1);
52
$_$;
53

    
54

    
55
--
56
-- 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
SELECT __
63
$$);
64

    
65
idempotent
66
';
67

    
68

    
69
--
70
-- Name: batch__fill(); Type: FUNCTION; Schema: TNRS; Owner: -
71
--
72

    
73
CREATE FUNCTION batch__fill() RETURNS trigger
74
    LANGUAGE plpgsql
75
    AS $$
76
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
$$;
82

    
83

    
84
--
85
-- Name: family_is_homonym(text); Type: FUNCTION; Schema: TNRS; Owner: -
86
--
87

    
88
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

    
94

    
95
--
96
-- Name: genus_is_homonym(text); Type: FUNCTION; Schema: TNRS; Owner: -
97
--
98

    
99
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

    
105

    
106
--
107
-- Name: map_taxonomic_status(text, text); Type: FUNCTION; Schema: TNRS; Owner: -
108
--
109

    
110
CREATE FUNCTION map_taxonomic_status(taxonomic_status_verbatim text, accepted_taxon_name_no_author text) RETURNS text
111
    LANGUAGE sql IMMUTABLE
112
    AS $_$
113
/* 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
SELECT CASE WHEN $2 IS NOT NULL THEN 'accepted' ELSE lower($1) END
117
$_$;
118

    
119

    
120
--
121
-- 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
-- Name: taxon_scrub_modify(text); Type: FUNCTION; Schema: TNRS; Owner: -
133
--
134

    
135
CREATE FUNCTION taxon_scrub_modify(view_query text DEFAULT NULL::text) RETURNS void
136
    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
SELECT __
150
$$);
151

    
152
idempotent
153
';
154

    
155

    
156
--
157
-- 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
-- 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
	IF new.match_num IS NULL THEN
179
		new.match_num = "TNRS".tnrs__match_num__next();
180
	END IF;
181
	RETURN new;
182
END;
183
$$;
184

    
185

    
186
--
187
-- 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
-- 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
DECLARE
205
	"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
	accepted_taxon_name_with_author text = NULLIF(concat_ws(' '
226
		, NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'),
227
			new."Accepted_name")
228
		, new."Accepted_name"
229
		, new."Accepted_name_author"
230
		), '');
231
BEGIN
232
	/* 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
	new.is_valid_match = new."Taxonomic_status" != 'Invalid'
235
		AND COALESCE(CASE
236
		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
		END, false);
251
	new.scrubbed_unique_taxon_name = COALESCE(
252
		accepted_taxon_name_with_author, matched_taxon_name_with_author);
253
	
254
	RETURN new;
255
END;
256
$$;
257

    
258

    
259
--
260
-- Name: FUNCTION tnrs_populate_fields(); Type: COMMENT; Schema: TNRS; Owner: -
261
--
262

    
263
COMMENT ON FUNCTION tnrs_populate_fields() IS '
264
IMPORTANT: when changing this function, you must regenerate the derived cols:
265
UPDATE "TNRS".tnrs SET "Name_submitted" = "Name_submitted"
266
runtime: 30 min ("5564082 rows affected, 1918900 ms execution time")
267
VACUUM ANALYZE "TNRS".tnrs -- to remove previous rows
268
runtime: 1.5 min ("92633 ms")
269
';
270

    
271

    
272
--
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
SET default_tablespace = '';
286

    
287
SET default_with_oids = false;
288

    
289
--
290
-- Name: tnrs; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: 
291
--
292

    
293
CREATE TABLE tnrs (
294
    batch text DEFAULT now() NOT NULL,
295
    match_num integer NOT NULL,
296
    "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
    is_valid_match boolean NOT NULL,
333
    scrubbed_unique_taxon_name text
334
);
335

    
336

    
337
--
338
-- Name: TABLE tnrs; Type: COMMENT; Schema: TNRS; Owner: -
339
--
340

    
341
COMMENT ON TABLE tnrs IS '
342
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

    
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

    
358
to add a constraint: runtime: 3 min ("173620 ms")
359
';
360

    
361

    
362
--
363
-- Name: MatchedTaxon; Type: VIEW; Schema: TNRS; Owner: -
364
--
365

    
366
CREATE VIEW "MatchedTaxon" AS
367
 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
            map_taxonomic_status(tnrs."Taxonomic_status", tnrs."Accepted_name") AS "taxonomicStatus", 
422
            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

    
436

    
437
--
438
-- Name: VIEW "MatchedTaxon"; Type: COMMENT; Schema: TNRS; Owner: -
439
--
440

    
441
COMMENT ON VIEW "MatchedTaxon" IS '
442
to modify:
443
SELECT "TNRS"."MatchedTaxon_modify"($$
444
 SELECT *, __ AS accepted_morphospecies_binomial
445
   FROM __
446
$$);
447
';
448

    
449

    
450
--
451
-- Name: ValidMatchedTaxon; Type: VIEW; Schema: TNRS; Owner: -
452
--
453

    
454
CREATE VIEW "ValidMatchedTaxon" AS
455
 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
    "MatchedTaxon".accepted_taxon_name_no_author, 
475
    "MatchedTaxon".accepted_author, 
476
    "MatchedTaxon".accepted_taxon_rank, 
477
    "MatchedTaxon"."acceptedScientificNameID", 
478
    "MatchedTaxon".accepted_species_binomial, 
479
    "MatchedTaxon".accepted_family, 
480
    "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
    "MatchedTaxon".scrubbed_unique_taxon_name, 
486
    "MatchedTaxon".accepted_morphospecies_binomial
487
   FROM "MatchedTaxon"
488
  WHERE "MatchedTaxon".taxon_scrub__is_valid_match;
489

    
490

    
491
--
492
-- Name: VIEW "ValidMatchedTaxon"; Type: COMMENT; Schema: TNRS; Owner: -
493
--
494

    
495
COMMENT ON VIEW "ValidMatchedTaxon" IS '
496
to update, use * as the column list
497
';
498

    
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
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

    
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
COMMENT ON TABLE client_version IS '
560
contains svn revisions
561
';
562

    
563

    
564
--
565
-- Name: COLUMN client_version.global_rev; Type: COMMENT; Schema: TNRS; Owner: -
566
--
567

    
568
COMMENT ON COLUMN client_version.global_rev IS '
569
from `svn info .` > Last Changed Rev
570
';
571

    
572

    
573
--
574
-- Name: COLUMN client_version."/lib/tnrs.py rev"; Type: COMMENT; Schema: TNRS; Owner: -
575
--
576

    
577
COMMENT ON COLUMN client_version."/lib/tnrs.py rev" IS '
578
from `svn info lib/tnrs.py` > Last Changed Rev
579
';
580

    
581

    
582
--
583
-- Name: COLUMN client_version."/bin/tnrs_db rev"; Type: COMMENT; Schema: TNRS; Owner: -
584
--
585

    
586
COMMENT ON COLUMN client_version."/bin/tnrs_db rev" IS '
587
from `svn info bin/tnrs_db` > Last Changed Rev
588
';
589

    
590

    
591
--
592
-- 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
    tnrs."Name_matched_rank" AS scrubbed_taxon_rank, 
598
    COALESCE(tnrs."Name_matched_accepted_family", tnrs."Family_matched") AS scrubbed_family, 
599
    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
    tnrs."Name_matched_author" AS scrubbed_author, 
604
    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
   FROM tnrs;
607

    
608

    
609
--
610
-- Name: VIEW "taxon_scrub.scrubbed_unique_taxon_name.*"; Type: COMMENT; Schema: TNRS; Owner: -
611
--
612

    
613
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

    
617

    
618
--
619
-- 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
    "ValidMatchedTaxon".accepted_taxon_name_no_author, 
644
    "ValidMatchedTaxon".accepted_author, 
645
    "ValidMatchedTaxon".accepted_taxon_rank, 
646
    "ValidMatchedTaxon"."acceptedScientificNameID", 
647
    "ValidMatchedTaxon".accepted_species_binomial, 
648
    "ValidMatchedTaxon".accepted_family, 
649
    "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
    "ValidMatchedTaxon".accepted_morphospecies_binomial, 
655
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank, 
656
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family, 
657
    "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
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_author, 
662
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author, 
663
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_with_author, 
664
        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
   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
COMMENT ON VIEW taxon_scrub IS '
678
to modify:
679
SELECT "TNRS".taxon_scrub_modify($$
680
 SELECT *, __ AS scrubbed_morphospecies_binomial
681
   FROM "TNRS"."ValidMatchedTaxon"
682
   LEFT JOIN "TNRS"."taxon_scrub.scrubbed_unique_taxon_name.*" USING (scrubbed_unique_taxon_name);
683
$$);
684
';
685

    
686

    
687
--
688
-- 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
    ADD CONSTRAINT tnrs_pkey PRIMARY KEY (batch, match_num);
725

    
726

    
727
--
728
-- 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
-- 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
-- Name: tnrs__valid_match; Type: INDEX; Schema: TNRS; Owner: -; Tablespace: 
743
--
744

    
745
CREATE INDEX tnrs__valid_match ON tnrs USING btree ("Name_submitted") WHERE is_valid_match;
746

    
747

    
748
--
749
-- 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
-- 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
-- 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
-- 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
-- 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
-- PostgreSQL database dump complete
862
--
863

    
(8-8/10)