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: bien
|
14
|
--
|
15
|
|
16
|
CREATE SCHEMA "TNRS";
|
17
|
|
18
|
|
19
|
ALTER SCHEMA "TNRS" OWNER TO bien;
|
20
|
|
21
|
--
|
22
|
-- Name: SCHEMA "TNRS"; Type: COMMENT; Schema: -; Owner: bien
|
23
|
--
|
24
|
|
25
|
COMMENT ON SCHEMA "TNRS" IS '
|
26
|
IMPORTANT: when changing this schema, you must also make the same changes on vegbiendev:
|
27
|
on vegbiendev:
|
28
|
# back up existing TNRS schema (in case of an accidental incorrect change):
|
29
|
$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")
|
30
|
$ svn up
|
31
|
$ svn di
|
32
|
# make the changes shown in the diff
|
33
|
## to change column types:
|
34
|
SELECT util.set_col_types(''"TNRS".taxon_match'', ARRAY[
|
35
|
(''col'', ''new_type'')
|
36
|
]::util.col_cast[]); -- runtime: 9 min ("531282 ms")
|
37
|
$ rm=1 inputs/.TNRS/schema.sql.run
|
38
|
# repeat until `svn di` shows no diff
|
39
|
# back up new TNRS schema:
|
40
|
$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")
|
41
|
';
|
42
|
|
43
|
|
44
|
SET search_path = "TNRS", pg_catalog;
|
45
|
|
46
|
--
|
47
|
-- Name: batch__fill(); Type: FUNCTION; Schema: TNRS; Owner: bien
|
48
|
--
|
49
|
|
50
|
CREATE FUNCTION batch__fill() RETURNS trigger
|
51
|
LANGUAGE plpgsql
|
52
|
AS $$
|
53
|
BEGIN
|
54
|
new.id_by_time = new.time_submitted;
|
55
|
new.id = COALESCE(new.id, new.id_by_time);
|
56
|
RETURN new;
|
57
|
END;
|
58
|
$$;
|
59
|
|
60
|
|
61
|
ALTER FUNCTION "TNRS".batch__fill() OWNER TO bien;
|
62
|
|
63
|
--
|
64
|
-- Name: family_is_homonym(text); Type: FUNCTION; Schema: TNRS; Owner: bien
|
65
|
--
|
66
|
|
67
|
CREATE FUNCTION family_is_homonym(family text) RETURNS boolean
|
68
|
LANGUAGE sql STABLE STRICT
|
69
|
AS $_$
|
70
|
SELECT EXISTS(SELECT * FROM "IRMNG".family_homonym_epithet WHERE "taxonNameOrEpithet" = $1)
|
71
|
$_$;
|
72
|
|
73
|
|
74
|
ALTER FUNCTION "TNRS".family_is_homonym(family text) OWNER TO bien;
|
75
|
|
76
|
--
|
77
|
-- Name: genus_is_homonym(text); Type: FUNCTION; Schema: TNRS; Owner: bien
|
78
|
--
|
79
|
|
80
|
CREATE FUNCTION genus_is_homonym(genus text) RETURNS boolean
|
81
|
LANGUAGE sql STABLE STRICT
|
82
|
AS $_$
|
83
|
SELECT EXISTS(SELECT * FROM "IRMNG".genus_homonym_epithet WHERE "taxonNameOrEpithet" = $1)
|
84
|
$_$;
|
85
|
|
86
|
|
87
|
ALTER FUNCTION "TNRS".genus_is_homonym(genus text) OWNER TO bien;
|
88
|
|
89
|
--
|
90
|
-- Name: map_taxonomic_status(text, text); Type: FUNCTION; Schema: TNRS; Owner: bien
|
91
|
--
|
92
|
|
93
|
CREATE FUNCTION map_taxonomic_status(taxonomic_status_verbatim text, accepted_taxon_name_no_author text) RETURNS text
|
94
|
LANGUAGE sql IMMUTABLE
|
95
|
AS $_$
|
96
|
/* from http://wiki.vegpath.org/2013-11-14_conference_call#taxonomic-fields:
|
97
|
"taxonomic_status should be accepted instead of synonym when an accepted name is
|
98
|
available (this is not always the case when a name is marked as a synonym)" */
|
99
|
SELECT CASE WHEN $2 IS NOT NULL THEN 'accepted' ELSE lower($1) END
|
100
|
$_$;
|
101
|
|
102
|
|
103
|
ALTER FUNCTION "TNRS".map_taxonomic_status(taxonomic_status_verbatim text, accepted_taxon_name_no_author text) OWNER TO bien;
|
104
|
|
105
|
--
|
106
|
-- Name: remove_prefix(text, text, boolean, boolean); Type: FUNCTION; Schema: TNRS; Owner: bien
|
107
|
--
|
108
|
|
109
|
CREATE FUNCTION remove_prefix(prefix text, str text, require boolean DEFAULT true, case_sensitive boolean DEFAULT true) RETURNS text
|
110
|
LANGUAGE sql IMMUTABLE
|
111
|
AS $$
|
112
|
SELECT util.remove_prefix(prefix, str, require, case_sensitive)
|
113
|
$$;
|
114
|
|
115
|
|
116
|
ALTER FUNCTION "TNRS".remove_prefix(prefix text, str text, require boolean, case_sensitive boolean) OWNER TO bien;
|
117
|
|
118
|
--
|
119
|
-- Name: FUNCTION remove_prefix(prefix text, str text, require boolean, case_sensitive boolean); Type: COMMENT; Schema: TNRS; Owner: bien
|
120
|
--
|
121
|
|
122
|
COMMENT ON FUNCTION remove_prefix(prefix text, str text, require boolean, case_sensitive boolean) IS '
|
123
|
wrapper that prevents views from getting dropped when the util schema is reinstalled
|
124
|
';
|
125
|
|
126
|
|
127
|
--
|
128
|
-- Name: taxon_match__batch_begin(); Type: FUNCTION; Schema: TNRS; Owner: bien
|
129
|
--
|
130
|
|
131
|
CREATE FUNCTION taxon_match__batch_begin() RETURNS trigger
|
132
|
LANGUAGE plpgsql
|
133
|
AS $$
|
134
|
BEGIN
|
135
|
PERFORM util.seq__reset('pg_temp.taxon_match__match_num__seq', 0);
|
136
|
RETURN NULL;
|
137
|
END;
|
138
|
$$;
|
139
|
|
140
|
|
141
|
ALTER FUNCTION "TNRS".taxon_match__batch_begin() OWNER TO bien;
|
142
|
|
143
|
--
|
144
|
-- Name: taxon_match__fill(); Type: FUNCTION; Schema: TNRS; Owner: bien
|
145
|
--
|
146
|
|
147
|
CREATE FUNCTION taxon_match__fill() RETURNS trigger
|
148
|
LANGUAGE plpgsql
|
149
|
AS $$
|
150
|
BEGIN
|
151
|
DECLARE
|
152
|
"Specific_epithet_is_plant" boolean :=
|
153
|
(CASE
|
154
|
WHEN new."*Infraspecific_epithet_matched" IS NOT NULL
|
155
|
OR new."*Infraspecific_epithet_2_matched" IS NOT NULL
|
156
|
OR new."*Specific_epithet_score" >= 0.9 -- fuzzy match
|
157
|
THEN true
|
158
|
ELSE NULL -- ambiguous
|
159
|
END);
|
160
|
never_homonym boolean = COALESCE(new."*Author_score" >= 0.6, false);
|
161
|
-- author disambiguates
|
162
|
family_is_homonym boolean = NOT never_homonym
|
163
|
AND "TNRS".family_is_homonym(new."*Family_matched");
|
164
|
genus_is_homonym boolean = NOT never_homonym
|
165
|
AND "TNRS".genus_is_homonym(new."*Genus_matched");
|
166
|
BEGIN
|
167
|
/* exclude homonyms because these are not valid matches (TNRS provides a
|
168
|
name, but the name is not meaningful because it is not unambiguous) */
|
169
|
new.is_valid_match = new."*Taxonomic_status" != 'Invalid'
|
170
|
AND COALESCE(CASE
|
171
|
WHEN new."*Family_score" = 1 AND NOT family_is_homonym -- exact match
|
172
|
THEN true
|
173
|
ELSE -- consider genus
|
174
|
(CASE
|
175
|
WHEN new."*Genus_score" = 1 -- exact match
|
176
|
THEN
|
177
|
(CASE
|
178
|
WHEN NOT genus_is_homonym THEN true
|
179
|
ELSE "Specific_epithet_is_plant"
|
180
|
END)
|
181
|
WHEN new."*Genus_score" >= 0.85 -- fuzzy match
|
182
|
THEN "Specific_epithet_is_plant"
|
183
|
ELSE NULL -- ambiguous
|
184
|
END)
|
185
|
END, false);
|
186
|
END;
|
187
|
|
188
|
DECLARE
|
189
|
matched_taxon_name_with_author text = NULLIF(concat_ws(' '
|
190
|
, NULLIF(NULLIF(new."*Name_matched_accepted_family", 'Unknown'),
|
191
|
new."*Name_matched")
|
192
|
, NULLIF(new."*Name_matched", 'No suitable matches found.')
|
193
|
, new."*Name_matched_author"
|
194
|
), '');
|
195
|
accepted_taxon_name_with_author text = NULLIF(concat_ws(' '
|
196
|
, NULLIF(NULLIF(new."*Accepted_name_family", 'Unknown'),
|
197
|
new."*Accepted_name")
|
198
|
, new."*Accepted_name"
|
199
|
, new."*Accepted_name_author"
|
200
|
), '');
|
201
|
BEGIN
|
202
|
new.scrubbed_unique_taxon_name = COALESCE(
|
203
|
accepted_taxon_name_with_author, matched_taxon_name_with_author);
|
204
|
END;
|
205
|
|
206
|
RETURN new;
|
207
|
END;
|
208
|
$$;
|
209
|
|
210
|
|
211
|
ALTER FUNCTION "TNRS".taxon_match__fill() OWNER TO bien;
|
212
|
|
213
|
--
|
214
|
-- Name: FUNCTION taxon_match__fill(); Type: COMMENT; Schema: TNRS; Owner: bien
|
215
|
--
|
216
|
|
217
|
COMMENT ON FUNCTION taxon_match__fill() IS '
|
218
|
IMPORTANT: when changing this function, you must regenerate the derived cols:
|
219
|
UPDATE "TNRS".taxon_match SET "Name_submitted" = "Name_submitted"
|
220
|
runtime: 30 min ("5564082 rows affected, 1918900 ms execution time")
|
221
|
VACUUM ANALYZE "TNRS".taxon_match -- to remove previous rows
|
222
|
runtime: 1.5 min ("92633 ms")
|
223
|
';
|
224
|
|
225
|
|
226
|
--
|
227
|
-- Name: taxon_match__fill_derived(); Type: FUNCTION; Schema: TNRS; Owner: bien
|
228
|
--
|
229
|
|
230
|
CREATE FUNCTION taxon_match__fill_derived() RETURNS trigger
|
231
|
LANGUAGE plpgsql
|
232
|
AS $$
|
233
|
BEGIN
|
234
|
-- clear derived cols so old values won't be used in calculations
|
235
|
new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = NULL;
|
236
|
new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
|
237
|
new."matched~Name[_no_author]___@TNRS__@vegpath.org" = NULL;
|
238
|
new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL;
|
239
|
new.matched_has_accepted = NULL;
|
240
|
new."Accepted_family__@TNRS__@vegpath.org" = NULL;
|
241
|
new."__accepted_{genus,specific_epithet}" = NULL;
|
242
|
new."[accepted_]genus__@DwC__@vegpath.org" = NULL;
|
243
|
new."[accepted_]specificEpithet__@DwC__@vegpath.org" = NULL;
|
244
|
new."Accepted_species[_binomial]__@TNRS__@vegpath.org" = NULL;
|
245
|
new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = NULL;
|
246
|
new.__accepted_infraspecific_label = NULL;
|
247
|
new."__accepted_infraspecific_{rank,epithet}" = NULL;
|
248
|
new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = NULL;
|
249
|
new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = NULL;
|
250
|
new."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL;
|
251
|
new."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" = NULL;
|
252
|
new."[scrubbed_]taxonRank__@DwC__@vegpath.org" = NULL;
|
253
|
new."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
|
254
|
new."[scrubbed_]genus__@DwC__@vegpath.org" = NULL;
|
255
|
new."[scrubbed_]specificEpithet__@DwC__@vegpath.org" = NULL;
|
256
|
new."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
|
257
|
new."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = NULL;
|
258
|
new."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = NULL;
|
259
|
new."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" = NULL;
|
260
|
new."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
|
261
|
new."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" = NULL;
|
262
|
new."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" = NULL;
|
263
|
|
264
|
-- populate derived cols
|
265
|
new."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" = (SELECT "*Unmatched_terms" FROM (SELECT new.*) new);
|
266
|
new."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT ("*Genus_matched" || ' '::text) || "*Specific_epithet_matched" FROM (SELECT new.*) new);
|
267
|
new."matched~Name[_no_author]___@TNRS__@vegpath.org" = (SELECT NULLIF("*Name_matched", 'No suitable matches found.'::text) FROM (SELECT new.*) new);
|
268
|
new."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "matched~Name[_no_author]___@TNRS__@vegpath.org" || COALESCE((' '::text || "*Name_matched_author"), ''::text) FROM (SELECT new.*) new);
|
269
|
new.matched_has_accepted = (SELECT "*Accepted_name" IS NOT NULL FROM (SELECT new.*) new);
|
270
|
new."Accepted_family__@TNRS__@vegpath.org" = (SELECT COALESCE("*Accepted_name_family",
|
271
|
CASE
|
272
|
WHEN ("*Accepted_name_rank" = 'family'::text) THEN "*Accepted_name"
|
273
|
ELSE NULL::text
|
274
|
END) FROM (SELECT new.*) new);
|
275
|
new."__accepted_{genus,specific_epithet}" = (SELECT regexp_split_to_array("*Accepted_name_species", ' '::text) FROM (SELECT new.*) new);
|
276
|
new."[accepted_]genus__@DwC__@vegpath.org" = (SELECT COALESCE("__accepted_{genus,specific_epithet}"[1],
|
277
|
CASE
|
278
|
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN "*Accepted_name"
|
279
|
ELSE NULL::text
|
280
|
END) FROM (SELECT new.*) new);
|
281
|
new."[accepted_]specificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_{genus,specific_epithet}"[2] FROM (SELECT new.*) new);
|
282
|
new."Accepted_species[_binomial]__@TNRS__@vegpath.org" = (SELECT rtrim("*Accepted_name_species", ' '::text) FROM (SELECT new.*) new);
|
283
|
new."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE
|
284
|
WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
285
|
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
286
|
ELSE "*Accepted_name_species"
|
287
|
END FROM (SELECT new.*) new);
|
288
|
new.__accepted_infraspecific_label = (SELECT ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true, case_sensitive := false), ''::text), ' '::text) FROM (SELECT new.*) new);
|
289
|
new."__accepted_infraspecific_{rank,epithet}" = (SELECT regexp_split_to_array(__accepted_infraspecific_label, ' '::text) FROM (SELECT new.*) new);
|
290
|
new."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[1] FROM (SELECT new.*) new);
|
291
|
new."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT "__accepted_infraspecific_{rank,epithet}"[2] FROM (SELECT new.*) new);
|
292
|
new."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT "*Accepted_name" || COALESCE((' '::text || "*Accepted_name_author"), ''::text) FROM (SELECT new.*) new);
|
293
|
new."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" = (SELECT "TNRS".map_taxonomic_status("*Taxonomic_status", "*Accepted_name") FROM (SELECT new.*) new);
|
294
|
new."[scrubbed_]taxonRank__@DwC__@vegpath.org" = (SELECT CASE
|
295
|
WHEN matched_has_accepted THEN "*Accepted_name_rank"
|
296
|
ELSE "*Name_matched_rank"
|
297
|
END FROM (SELECT new.*) new);
|
298
|
new."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE
|
299
|
WHEN matched_has_accepted THEN "Accepted_family__@TNRS__@vegpath.org"
|
300
|
ELSE "*Name_matched_accepted_family"
|
301
|
END FROM (SELECT new.*) new);
|
302
|
new."[scrubbed_]genus__@DwC__@vegpath.org" = (SELECT CASE
|
303
|
WHEN matched_has_accepted THEN "[accepted_]genus__@DwC__@vegpath.org"
|
304
|
ELSE "*Genus_matched"
|
305
|
END FROM (SELECT new.*) new);
|
306
|
new."[scrubbed_]specificEpithet__@DwC__@vegpath.org" = (SELECT CASE
|
307
|
WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org"
|
308
|
ELSE "*Specific_epithet_matched"
|
309
|
END FROM (SELECT new.*) new);
|
310
|
new."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE
|
311
|
WHEN matched_has_accepted THEN "*Accepted_name_species"
|
312
|
ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org"
|
313
|
END FROM (SELECT new.*) new);
|
314
|
new."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" = (SELECT CASE
|
315
|
WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = 'family'::text) THEN concat_ws(' '::text, "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
316
|
WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = 'genus'::text) THEN concat_ws(' '::text, "[scrubbed_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
317
|
ELSE "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org"
|
318
|
END FROM (SELECT new.*) new);
|
319
|
new."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" = (SELECT CASE
|
320
|
WHEN matched_has_accepted THEN "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org"
|
321
|
ELSE "*Infraspecific_rank"
|
322
|
END FROM (SELECT new.*) new);
|
323
|
new."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" = (SELECT CASE
|
324
|
WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org"
|
325
|
ELSE "*Infraspecific_epithet_matched"
|
326
|
END FROM (SELECT new.*) new);
|
327
|
new."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE
|
328
|
WHEN matched_has_accepted THEN "*Accepted_name"
|
329
|
ELSE "matched~Name[_no_author]___@TNRS__@vegpath.org"
|
330
|
END FROM (SELECT new.*) new);
|
331
|
new."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" = (SELECT CASE
|
332
|
WHEN matched_has_accepted THEN "*Accepted_name_author"
|
333
|
ELSE "*Name_matched_author"
|
334
|
END FROM (SELECT new.*) new);
|
335
|
new."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" = (SELECT CASE
|
336
|
WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org"
|
337
|
ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org"
|
338
|
END FROM (SELECT new.*) new);
|
339
|
|
340
|
RETURN new;
|
341
|
END;
|
342
|
$$;
|
343
|
|
344
|
|
345
|
ALTER FUNCTION "TNRS".taxon_match__fill_derived() OWNER TO bien;
|
346
|
|
347
|
--
|
348
|
-- Name: FUNCTION taxon_match__fill_derived(); Type: COMMENT; Schema: TNRS; Owner: bien
|
349
|
--
|
350
|
|
351
|
COMMENT ON FUNCTION taxon_match__fill_derived() IS '
|
352
|
autogenerated, do not edit
|
353
|
|
354
|
to regenerate:
|
355
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
356
|
';
|
357
|
|
358
|
|
359
|
--
|
360
|
-- Name: taxon_match__match_num__fill(); Type: FUNCTION; Schema: TNRS; Owner: bien
|
361
|
--
|
362
|
|
363
|
CREATE FUNCTION taxon_match__match_num__fill() RETURNS trigger
|
364
|
LANGUAGE plpgsql
|
365
|
AS $$
|
366
|
BEGIN
|
367
|
IF new.match_num IS NULL THEN
|
368
|
new.match_num = "TNRS".taxon_match__match_num__next();
|
369
|
END IF;
|
370
|
RETURN new;
|
371
|
END;
|
372
|
$$;
|
373
|
|
374
|
|
375
|
ALTER FUNCTION "TNRS".taxon_match__match_num__fill() OWNER TO bien;
|
376
|
|
377
|
--
|
378
|
-- Name: taxon_match__match_num__next(); Type: FUNCTION; Schema: TNRS; Owner: bien
|
379
|
--
|
380
|
|
381
|
CREATE FUNCTION taxon_match__match_num__next() RETURNS bigint
|
382
|
LANGUAGE sql
|
383
|
AS $$
|
384
|
SELECT nextval('pg_temp.taxon_match__match_num__seq');
|
385
|
$$;
|
386
|
|
387
|
|
388
|
ALTER FUNCTION "TNRS".taxon_match__match_num__next() OWNER TO bien;
|
389
|
|
390
|
--
|
391
|
-- Name: taxon_match_input__copy_to__insert(); Type: FUNCTION; Schema: TNRS; Owner: bien
|
392
|
--
|
393
|
|
394
|
CREATE FUNCTION taxon_match_input__copy_to__insert() RETURNS trigger
|
395
|
LANGUAGE plpgsql
|
396
|
AS $$
|
397
|
BEGIN
|
398
|
INSERT INTO "TNRS".taxon_match_input SELECT new.*;
|
399
|
RETURN NULL;
|
400
|
END;
|
401
|
$$;
|
402
|
|
403
|
|
404
|
ALTER FUNCTION "TNRS".taxon_match_input__copy_to__insert() OWNER TO bien;
|
405
|
|
406
|
--
|
407
|
-- Name: taxon_name_is_safe(text); Type: FUNCTION; Schema: TNRS; Owner: bien
|
408
|
--
|
409
|
|
410
|
CREATE FUNCTION taxon_name_is_safe(taxon_name text) RETURNS boolean
|
411
|
LANGUAGE sql IMMUTABLE
|
412
|
AS $_$
|
413
|
SELECT NOT ($1 = ANY("TNRS".unsafe_taxon_names()))
|
414
|
$_$;
|
415
|
|
416
|
|
417
|
ALTER FUNCTION "TNRS".taxon_name_is_safe(taxon_name text) OWNER TO bien;
|
418
|
|
419
|
--
|
420
|
-- Name: unsafe_taxon_names(); Type: FUNCTION; Schema: TNRS; Owner: bien
|
421
|
--
|
422
|
|
423
|
CREATE FUNCTION unsafe_taxon_names() RETURNS text[]
|
424
|
LANGUAGE sql IMMUTABLE
|
425
|
AS $$
|
426
|
SELECT ARRAY[
|
427
|
]::text[]
|
428
|
$$;
|
429
|
|
430
|
|
431
|
ALTER FUNCTION "TNRS".unsafe_taxon_names() OWNER TO bien;
|
432
|
|
433
|
SET default_tablespace = '';
|
434
|
|
435
|
SET default_with_oids = false;
|
436
|
|
437
|
--
|
438
|
-- Name: taxon_match; Type: TABLE; Schema: TNRS; Owner: bien; Tablespace:
|
439
|
--
|
440
|
|
441
|
CREATE TABLE taxon_match (
|
442
|
batch text DEFAULT now() NOT NULL,
|
443
|
match_num integer NOT NULL,
|
444
|
"*Name_number" integer NOT NULL,
|
445
|
"*Name_submitted" text NOT NULL,
|
446
|
"*Overall_score" double precision,
|
447
|
"*Name_matched" text,
|
448
|
"*Name_matched_rank" text,
|
449
|
"*Name_score" double precision,
|
450
|
"*Name_matched_author" text,
|
451
|
"*Name_matched_url" text,
|
452
|
"*Author_matched" text,
|
453
|
"*Author_score" double precision,
|
454
|
"*Family_matched" text,
|
455
|
"*Family_score" double precision,
|
456
|
"*Name_matched_accepted_family" text,
|
457
|
"*Genus_matched" text,
|
458
|
"*Genus_score" double precision,
|
459
|
"*Specific_epithet_matched" text,
|
460
|
"*Specific_epithet_score" double precision,
|
461
|
"*Infraspecific_rank" text,
|
462
|
"*Infraspecific_epithet_matched" text,
|
463
|
"*Infraspecific_epithet_score" double precision,
|
464
|
"*Infraspecific_rank_2" text,
|
465
|
"*Infraspecific_epithet_2_matched" text,
|
466
|
"*Infraspecific_epithet_2_score" double precision,
|
467
|
"*Annotations" text,
|
468
|
"*Unmatched_terms" text,
|
469
|
"*Taxonomic_status" text,
|
470
|
"*Accepted_name" text,
|
471
|
"*Accepted_name_author" text,
|
472
|
"*Accepted_name_rank" text,
|
473
|
"*Accepted_name_url" text,
|
474
|
"*Accepted_name_species" text,
|
475
|
"*Accepted_name_family" text,
|
476
|
"*Selected" text,
|
477
|
"*Source" text,
|
478
|
"*Warnings" text,
|
479
|
"*Accepted_name_lsid" text,
|
480
|
is_valid_match boolean NOT NULL,
|
481
|
scrubbed_unique_taxon_name text,
|
482
|
"[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" text,
|
483
|
"[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" text,
|
484
|
"matched~Name[_no_author]___@TNRS__@vegpath.org" text,
|
485
|
"[matched_]scientificName[_with_author]__@DwC__@vegpath.org" text,
|
486
|
matched_has_accepted boolean,
|
487
|
"Accepted_family__@TNRS__@vegpath.org" text,
|
488
|
"Accepted_species[_binomial]__@TNRS__@vegpath.org" text,
|
489
|
"__accepted_{genus,specific_epithet}" text[],
|
490
|
"[accepted_]genus__@DwC__@vegpath.org" text,
|
491
|
"[accepted_]specificEpithet__@DwC__@vegpath.org" text,
|
492
|
"[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" text,
|
493
|
__accepted_infraspecific_label text,
|
494
|
"__accepted_infraspecific_{rank,epithet}" text[],
|
495
|
"[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" text,
|
496
|
"[accepted_]infraspecificEpithet__@DwC__@vegpath.org" text,
|
497
|
"[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" text,
|
498
|
"[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" text,
|
499
|
"[scrubbed_]taxonRank__@DwC__@vegpath.org" text,
|
500
|
"[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" text,
|
501
|
"[scrubbed_]genus__@DwC__@vegpath.org" text,
|
502
|
"[scrubbed_]specificEpithet__@DwC__@vegpath.org" text,
|
503
|
"[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" text,
|
504
|
"[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" text,
|
505
|
"[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" text,
|
506
|
"[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" text,
|
507
|
"[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" text,
|
508
|
"[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" text,
|
509
|
"[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" text,
|
510
|
CONSTRAINT "Accepted_family__@TNRS__@vegpath.org" CHECK ((NOT ("Accepted_family__@TNRS__@vegpath.org" IS DISTINCT FROM COALESCE("*Accepted_name_family",
|
511
|
CASE
|
512
|
WHEN ("*Accepted_name_rank" = 'family'::text) THEN "*Accepted_name"
|
513
|
ELSE NULL::text
|
514
|
END)))),
|
515
|
CONSTRAINT "Accepted_species[_binomial]__@TNRS__@vegpath.org" CHECK ((NOT ("Accepted_species[_binomial]__@TNRS__@vegpath.org" IS DISTINCT FROM rtrim("*Accepted_name_species", ' '::text)))),
|
516
|
CONSTRAINT "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" CHECK ((NOT ("[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS DISTINCT FROM "__accepted_infraspecific_{rank,epithet}"[1]))),
|
517
|
CONSTRAINT "[accepted_]genus__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]genus__@DwC__@vegpath.org" IS DISTINCT FROM COALESCE("__accepted_{genus,specific_epithet}"[1],
|
518
|
CASE
|
519
|
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN "*Accepted_name"
|
520
|
ELSE NULL::text
|
521
|
END)))),
|
522
|
CONSTRAINT "[accepted_]infraspecificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]infraspecificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_infraspecific_{rank,epithet}"[2]))),
|
523
|
CONSTRAINT "[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" CHECK ((NOT ("[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS DISTINCT FROM
|
524
|
CASE
|
525
|
WHEN ("*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
526
|
WHEN ("*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
527
|
ELSE "*Accepted_name_species"
|
528
|
END))),
|
529
|
CONSTRAINT "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" IS DISTINCT FROM ("*Accepted_name" || COALESCE((' '::text || "*Accepted_name_author"), ''::text))))),
|
530
|
CONSTRAINT "[accepted_]specificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[accepted_]specificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM "__accepted_{genus,specific_epithet}"[2]))),
|
531
|
CONSTRAINT "[matched_]scientificName[_with_author]__@DwC__@vegpath.org" CHECK ((NOT ("[matched_]scientificName[_with_author]__@DwC__@vegpath.org" IS DISTINCT FROM ("matched~Name[_no_author]___@TNRS__@vegpath.org" || COALESCE((' '::text || "*Name_matched_author"), ''::text))))),
|
532
|
CONSTRAINT "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM (("*Genus_matched" || ' '::text) || "*Specific_epithet_matched")))),
|
533
|
CONSTRAINT "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" CHECK ((NOT ("[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" IS DISTINCT FROM "*Unmatched_terms"))),
|
534
|
CONSTRAINT "[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS DISTINCT FROM
|
535
|
CASE
|
536
|
WHEN matched_has_accepted THEN "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org"
|
537
|
ELSE "*Infraspecific_rank"
|
538
|
END))),
|
539
|
CONSTRAINT "[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM
|
540
|
CASE
|
541
|
WHEN matched_has_accepted THEN "*Accepted_name_author"
|
542
|
ELSE "*Name_matched_author"
|
543
|
END))),
|
544
|
CONSTRAINT "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM
|
545
|
CASE
|
546
|
WHEN matched_has_accepted THEN "Accepted_family__@TNRS__@vegpath.org"
|
547
|
ELSE "*Name_matched_accepted_family"
|
548
|
END))),
|
549
|
CONSTRAINT "[scrubbed_]genus__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]genus__@DwC__@vegpath.org" IS DISTINCT FROM
|
550
|
CASE
|
551
|
WHEN matched_has_accepted THEN "[accepted_]genus__@DwC__@vegpath.org"
|
552
|
ELSE "*Genus_matched"
|
553
|
END))),
|
554
|
CONSTRAINT "[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM
|
555
|
CASE
|
556
|
WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org"
|
557
|
ELSE "*Infraspecific_epithet_matched"
|
558
|
END))),
|
559
|
CONSTRAINT "[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" CHECK ((NOT ("[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS DISTINCT FROM
|
560
|
CASE
|
561
|
WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = 'family'::text) THEN concat_ws(' '::text, "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
562
|
WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = 'genus'::text) THEN concat_ws(' '::text, "[scrubbed_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
563
|
ELSE "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org"
|
564
|
END))),
|
565
|
CONSTRAINT "[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM
|
566
|
CASE
|
567
|
WHEN matched_has_accepted THEN "*Accepted_name"
|
568
|
ELSE "matched~Name[_no_author]___@TNRS__@vegpath.org"
|
569
|
END))),
|
570
|
CONSTRAINT "[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" IS DISTINCT FROM
|
571
|
CASE
|
572
|
WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org"
|
573
|
ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org"
|
574
|
END))),
|
575
|
CONSTRAINT "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" CHECK ((NOT ("[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" IS DISTINCT FROM
|
576
|
CASE
|
577
|
WHEN matched_has_accepted THEN "*Accepted_name_species"
|
578
|
ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org"
|
579
|
END))),
|
580
|
CONSTRAINT "[scrubbed_]specificEpithet__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]specificEpithet__@DwC__@vegpath.org" IS DISTINCT FROM
|
581
|
CASE
|
582
|
WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org"
|
583
|
ELSE "*Specific_epithet_matched"
|
584
|
END))),
|
585
|
CONSTRAINT "[scrubbed_]taxonRank__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]taxonRank__@DwC__@vegpath.org" IS DISTINCT FROM
|
586
|
CASE
|
587
|
WHEN matched_has_accepted THEN "*Accepted_name_rank"
|
588
|
ELSE "*Name_matched_rank"
|
589
|
END))),
|
590
|
CONSTRAINT "[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" CHECK ((NOT ("[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" IS DISTINCT FROM map_taxonomic_status("*Taxonomic_status", "*Accepted_name")))),
|
591
|
CONSTRAINT __accepted_infraspecific_label CHECK ((NOT (__accepted_infraspecific_label IS DISTINCT FROM ltrim(NULLIF(remove_prefix("*Accepted_name_species", "*Accepted_name", require := true, case_sensitive := false), ''::text), ' '::text)))),
|
592
|
CONSTRAINT "__accepted_infraspecific_{rank,epithet}" CHECK ((NOT ("__accepted_infraspecific_{rank,epithet}" IS DISTINCT FROM regexp_split_to_array(__accepted_infraspecific_label, ' '::text)))),
|
593
|
CONSTRAINT "__accepted_{genus,specific_epithet}" CHECK ((NOT ("__accepted_{genus,specific_epithet}" IS DISTINCT FROM regexp_split_to_array("*Accepted_name_species", ' '::text)))),
|
594
|
CONSTRAINT matched_has_accepted CHECK ((NOT (matched_has_accepted IS DISTINCT FROM ("*Accepted_name" IS NOT NULL)))),
|
595
|
CONSTRAINT "matched~Name[_no_author]___@TNRS__@vegpath.org" CHECK ((NOT ("matched~Name[_no_author]___@TNRS__@vegpath.org" IS DISTINCT FROM NULLIF("*Name_matched", 'No suitable matches found.'::text))))
|
596
|
);
|
597
|
|
598
|
|
599
|
ALTER TABLE "TNRS".taxon_match OWNER TO bien;
|
600
|
|
601
|
--
|
602
|
-- Name: TABLE taxon_match; Type: COMMENT; Schema: TNRS; Owner: bien
|
603
|
--
|
604
|
|
605
|
COMMENT ON TABLE taxon_match IS '
|
606
|
whenever columns are renamed:
|
607
|
SELECT util.derived_cols_update(''"TNRS".taxon_match'');
|
608
|
|
609
|
to port derived column changes to vegbiendev:
|
610
|
SELECT util.derived_cols_export(''"TNRS".taxon_match'');
|
611
|
# run the returned SQL on vegbiendev
|
612
|
-- runtime: 30 min ("5564201 rows affected, 1624829 ms execution time")
|
613
|
SELECT util.recreate_view(''"TNRS".taxon_best_match'');
|
614
|
|
615
|
to add a new derived column:
|
616
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''col''), $$
|
617
|
expr
|
618
|
$$));
|
619
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match'');
|
620
|
SELECT util.recreate_view(''"TNRS".taxon_best_match'');
|
621
|
$ make schemas/remake
|
622
|
|
623
|
to remove a column:
|
624
|
SELECT util.recreate_view(''"TNRS".taxon_best_match'');
|
625
|
$ make schemas/remake
|
626
|
|
627
|
to move a derived column to the middle or to add a non-derived column:
|
628
|
make the changes in inputs/.TNRS/schema.sql
|
629
|
$ inputs/.TNRS/data.sql.run refresh # re-run TNRS
|
630
|
SELECT util.derived_cols_update(''"TNRS".taxon_match'');
|
631
|
SELECT util.derived_cols_repopulate(''"TNRS".taxon_match'');
|
632
|
SELECT util.recreate_view(''"TNRS".taxon_best_match'');
|
633
|
$ make schemas/remake
|
634
|
|
635
|
to add a constraint: runtime: 3 min ("173620 ms")
|
636
|
';
|
637
|
|
638
|
|
639
|
--
|
640
|
-- Name: COLUMN taxon_match."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
641
|
--
|
642
|
|
643
|
COMMENT ON COLUMN taxon_match."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org" IS '
|
644
|
= "*Unmatched_terms"
|
645
|
|
646
|
derived column
|
647
|
|
648
|
to modify expr:
|
649
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org'')::util.col, $$"*Unmatched_terms"$$)::util.derived_col_def);
|
650
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
651
|
|
652
|
to rename:
|
653
|
# rename column
|
654
|
# rename CHECK constraint
|
655
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
656
|
';
|
657
|
|
658
|
|
659
|
--
|
660
|
-- Name: COLUMN taxon_match."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
661
|
--
|
662
|
|
663
|
COMMENT ON COLUMN taxon_match."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org" IS '
|
664
|
= ("*Genus_matched" || '' ''::text) || "*Specific_epithet_matched"
|
665
|
|
666
|
derived column
|
667
|
|
668
|
to modify expr:
|
669
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$("*Genus_matched" || '' ''::text) || "*Specific_epithet_matched"$$)::util.derived_col_def);
|
670
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
671
|
|
672
|
to rename:
|
673
|
# rename column
|
674
|
# rename CHECK constraint
|
675
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
676
|
';
|
677
|
|
678
|
|
679
|
--
|
680
|
-- Name: COLUMN taxon_match."matched~Name[_no_author]___@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
681
|
--
|
682
|
|
683
|
COMMENT ON COLUMN taxon_match."matched~Name[_no_author]___@TNRS__@vegpath.org" IS '
|
684
|
= NULLIF("*Name_matched", ''No suitable matches found.''::text)
|
685
|
|
686
|
derived column
|
687
|
|
688
|
to modify expr:
|
689
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''matched~Name[_no_author]___@TNRS__@vegpath.org'')::util.col, $$NULLIF("*Name_matched", ''No suitable matches found.''::text)$$)::util.derived_col_def);
|
690
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
691
|
|
692
|
to rename:
|
693
|
# rename column
|
694
|
# rename CHECK constraint
|
695
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
696
|
';
|
697
|
|
698
|
|
699
|
--
|
700
|
-- Name: COLUMN taxon_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
701
|
--
|
702
|
|
703
|
COMMENT ON COLUMN taxon_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org" IS '
|
704
|
= "matched~Name[_no_author]___@TNRS__@vegpath.org" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)
|
705
|
|
706
|
derived column
|
707
|
|
708
|
to modify expr:
|
709
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[matched_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$"matched~Name[_no_author]___@TNRS__@vegpath.org" || COALESCE(('' ''::text || "*Name_matched_author"), ''''::text)$$)::util.derived_col_def);
|
710
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
711
|
|
712
|
to rename:
|
713
|
# rename column
|
714
|
# rename CHECK constraint
|
715
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
716
|
';
|
717
|
|
718
|
|
719
|
--
|
720
|
-- Name: COLUMN taxon_match.matched_has_accepted; Type: COMMENT; Schema: TNRS; Owner: bien
|
721
|
--
|
722
|
|
723
|
COMMENT ON COLUMN taxon_match.matched_has_accepted IS '
|
724
|
= "*Accepted_name" IS NOT NULL
|
725
|
|
726
|
derived column
|
727
|
|
728
|
to modify expr:
|
729
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''matched_has_accepted'')::util.col, $$"*Accepted_name" IS NOT NULL$$)::util.derived_col_def);
|
730
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
731
|
|
732
|
to rename:
|
733
|
# rename column
|
734
|
# rename CHECK constraint
|
735
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
736
|
';
|
737
|
|
738
|
|
739
|
--
|
740
|
-- Name: COLUMN taxon_match."Accepted_family__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
741
|
--
|
742
|
|
743
|
COMMENT ON COLUMN taxon_match."Accepted_family__@TNRS__@vegpath.org" IS '
|
744
|
= COALESCE("*Accepted_name_family",
|
745
|
CASE
|
746
|
WHEN ("*Accepted_name_rank" = ''family''::text) THEN "*Accepted_name"
|
747
|
ELSE NULL::text
|
748
|
END)
|
749
|
|
750
|
derived column
|
751
|
|
752
|
to modify expr:
|
753
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''Accepted_family__@TNRS__@vegpath.org'')::util.col, $$COALESCE("*Accepted_name_family",
|
754
|
CASE
|
755
|
WHEN ("*Accepted_name_rank" = ''family''::text) THEN "*Accepted_name"
|
756
|
ELSE NULL::text
|
757
|
END)$$)::util.derived_col_def);
|
758
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
759
|
|
760
|
to rename:
|
761
|
# rename column
|
762
|
# rename CHECK constraint
|
763
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
764
|
';
|
765
|
|
766
|
|
767
|
--
|
768
|
-- Name: COLUMN taxon_match."Accepted_species[_binomial]__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
769
|
--
|
770
|
|
771
|
COMMENT ON COLUMN taxon_match."Accepted_species[_binomial]__@TNRS__@vegpath.org" IS '
|
772
|
= rtrim("*Accepted_name_species", '' ''::text)
|
773
|
|
774
|
derived column
|
775
|
|
776
|
to modify expr:
|
777
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''Accepted_species[_binomial]__@TNRS__@vegpath.org'')::util.col, $$rtrim("*Accepted_name_species", '' ''::text)$$)::util.derived_col_def);
|
778
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
779
|
|
780
|
to rename:
|
781
|
# rename column
|
782
|
# rename CHECK constraint
|
783
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
784
|
';
|
785
|
|
786
|
|
787
|
--
|
788
|
-- Name: COLUMN taxon_match."__accepted_{genus,specific_epithet}"; Type: COMMENT; Schema: TNRS; Owner: bien
|
789
|
--
|
790
|
|
791
|
COMMENT ON COLUMN taxon_match."__accepted_{genus,specific_epithet}" IS '
|
792
|
= regexp_split_to_array("*Accepted_name_species", '' ''::text)
|
793
|
|
794
|
derived column
|
795
|
|
796
|
to modify expr:
|
797
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_{genus,specific_epithet}'')::util.col, $$regexp_split_to_array("*Accepted_name_species", '' ''::text)$$)::util.derived_col_def);
|
798
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
799
|
|
800
|
to rename:
|
801
|
# rename column
|
802
|
# rename CHECK constraint
|
803
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
804
|
';
|
805
|
|
806
|
|
807
|
--
|
808
|
-- Name: COLUMN taxon_match."[accepted_]genus__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
809
|
--
|
810
|
|
811
|
COMMENT ON COLUMN taxon_match."[accepted_]genus__@DwC__@vegpath.org" IS '
|
812
|
= COALESCE("__accepted_{genus,specific_epithet}"[1],
|
813
|
CASE
|
814
|
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN "*Accepted_name"
|
815
|
ELSE NULL::text
|
816
|
END)
|
817
|
|
818
|
derived column
|
819
|
|
820
|
to modify expr:
|
821
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]genus__@DwC__@vegpath.org'')::util.col, $$COALESCE("__accepted_{genus,specific_epithet}"[1],
|
822
|
CASE
|
823
|
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN "*Accepted_name"
|
824
|
ELSE NULL::text
|
825
|
END)$$)::util.derived_col_def);
|
826
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
827
|
|
828
|
to rename:
|
829
|
# rename column
|
830
|
# rename CHECK constraint
|
831
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
832
|
';
|
833
|
|
834
|
|
835
|
--
|
836
|
-- Name: COLUMN taxon_match."[accepted_]specificEpithet__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
837
|
--
|
838
|
|
839
|
COMMENT ON COLUMN taxon_match."[accepted_]specificEpithet__@DwC__@vegpath.org" IS '
|
840
|
= "__accepted_{genus,specific_epithet}"[2]
|
841
|
|
842
|
derived column
|
843
|
|
844
|
to modify expr:
|
845
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]specificEpithet__@DwC__@vegpath.org'')::util.col, $$"__accepted_{genus,specific_epithet}"[2]$$)::util.derived_col_def);
|
846
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
847
|
|
848
|
to rename:
|
849
|
# rename column
|
850
|
# rename CHECK constraint
|
851
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
852
|
';
|
853
|
|
854
|
|
855
|
--
|
856
|
-- Name: COLUMN taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
857
|
--
|
858
|
|
859
|
COMMENT ON COLUMN taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS '
|
860
|
= CASE
|
861
|
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
862
|
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
863
|
ELSE "*Accepted_name_species"
|
864
|
END
|
865
|
|
866
|
derived column
|
867
|
|
868
|
to modify expr:
|
869
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org'')::util.col, $$CASE
|
870
|
WHEN ("*Accepted_name_rank" = ''family''::text) THEN concat_ws('' ''::text, "Accepted_family__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
871
|
WHEN ("*Accepted_name_rank" = ''genus''::text) THEN concat_ws('' ''::text, "[accepted_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
872
|
ELSE "*Accepted_name_species"
|
873
|
END$$)::util.derived_col_def);
|
874
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
875
|
|
876
|
to rename:
|
877
|
# rename column
|
878
|
# rename CHECK constraint
|
879
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
880
|
';
|
881
|
|
882
|
|
883
|
--
|
884
|
-- Name: COLUMN taxon_match.__accepted_infraspecific_label; Type: COMMENT; Schema: TNRS; Owner: bien
|
885
|
--
|
886
|
|
887
|
COMMENT ON COLUMN taxon_match.__accepted_infraspecific_label IS '
|
888
|
= ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true, case_sensitive := false), ''''::text), '' ''::text)
|
889
|
|
890
|
derived column
|
891
|
|
892
|
to modify expr:
|
893
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_label'')::util.col, $$ltrim(NULLIF("TNRS".remove_prefix("*Accepted_name_species", "*Accepted_name", require := true, case_sensitive := false), ''''::text), '' ''::text)$$)::util.derived_col_def);
|
894
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
895
|
|
896
|
to rename:
|
897
|
# rename column
|
898
|
# rename CHECK constraint
|
899
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
900
|
';
|
901
|
|
902
|
|
903
|
--
|
904
|
-- Name: COLUMN taxon_match."__accepted_infraspecific_{rank,epithet}"; Type: COMMENT; Schema: TNRS; Owner: bien
|
905
|
--
|
906
|
|
907
|
COMMENT ON COLUMN taxon_match."__accepted_infraspecific_{rank,epithet}" IS '
|
908
|
= regexp_split_to_array(__accepted_infraspecific_label, '' ''::text)
|
909
|
|
910
|
derived column
|
911
|
|
912
|
to modify expr:
|
913
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''__accepted_infraspecific_{rank,epithet}'')::util.col, $$regexp_split_to_array(__accepted_infraspecific_label, '' ''::text)$$)::util.derived_col_def);
|
914
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
915
|
|
916
|
to rename:
|
917
|
# rename column
|
918
|
# rename CHECK constraint
|
919
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
920
|
';
|
921
|
|
922
|
|
923
|
--
|
924
|
-- Name: COLUMN taxon_match."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
925
|
--
|
926
|
|
927
|
COMMENT ON COLUMN taxon_match."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS '
|
928
|
= "__accepted_infraspecific_{rank,epithet}"[1]
|
929
|
|
930
|
derived column
|
931
|
|
932
|
to modify expr:
|
933
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org'')::util.col, $$"__accepted_infraspecific_{rank,epithet}"[1]$$)::util.derived_col_def);
|
934
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
935
|
|
936
|
to rename:
|
937
|
# rename column
|
938
|
# rename CHECK constraint
|
939
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
940
|
';
|
941
|
|
942
|
|
943
|
--
|
944
|
-- Name: COLUMN taxon_match."[accepted_]infraspecificEpithet__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
945
|
--
|
946
|
|
947
|
COMMENT ON COLUMN taxon_match."[accepted_]infraspecificEpithet__@DwC__@vegpath.org" IS '
|
948
|
= "__accepted_infraspecific_{rank,epithet}"[2]
|
949
|
|
950
|
derived column
|
951
|
|
952
|
to modify expr:
|
953
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]infraspecificEpithet__@DwC__@vegpath.org'')::util.col, $$"__accepted_infraspecific_{rank,epithet}"[2]$$)::util.derived_col_def);
|
954
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
955
|
|
956
|
to rename:
|
957
|
# rename column
|
958
|
# rename CHECK constraint
|
959
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
960
|
';
|
961
|
|
962
|
|
963
|
--
|
964
|
-- Name: COLUMN taxon_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
965
|
--
|
966
|
|
967
|
COMMENT ON COLUMN taxon_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org" IS '
|
968
|
= "*Accepted_name" || COALESCE(('' ''::text || "*Accepted_name_author"), ''''::text)
|
969
|
|
970
|
derived column
|
971
|
|
972
|
to modify expr:
|
973
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[accepted_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$"*Accepted_name" || COALESCE(('' ''::text || "*Accepted_name_author"), ''''::text)$$)::util.derived_col_def);
|
974
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
975
|
|
976
|
to rename:
|
977
|
# rename column
|
978
|
# rename CHECK constraint
|
979
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
980
|
';
|
981
|
|
982
|
|
983
|
--
|
984
|
-- Name: COLUMN taxon_match."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
985
|
--
|
986
|
|
987
|
COMMENT ON COLUMN taxon_match."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org" IS '
|
988
|
= "TNRS".map_taxonomic_status("*Taxonomic_status", "*Accepted_name")
|
989
|
|
990
|
derived column
|
991
|
|
992
|
to modify expr:
|
993
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]taxonomicStatus__@DwC__@vegpath.org'')::util.col, $$"TNRS".map_taxonomic_status("*Taxonomic_status", "*Accepted_name")$$)::util.derived_col_def);
|
994
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
995
|
|
996
|
to rename:
|
997
|
# rename column
|
998
|
# rename CHECK constraint
|
999
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
1000
|
';
|
1001
|
|
1002
|
|
1003
|
--
|
1004
|
-- Name: COLUMN taxon_match."[scrubbed_]taxonRank__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
1005
|
--
|
1006
|
|
1007
|
COMMENT ON COLUMN taxon_match."[scrubbed_]taxonRank__@DwC__@vegpath.org" IS '
|
1008
|
= CASE
|
1009
|
WHEN matched_has_accepted THEN "*Accepted_name_rank"
|
1010
|
ELSE "*Name_matched_rank"
|
1011
|
END
|
1012
|
|
1013
|
derived column
|
1014
|
|
1015
|
to modify expr:
|
1016
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]taxonRank__@DwC__@vegpath.org'')::util.col, $$CASE
|
1017
|
WHEN matched_has_accepted THEN "*Accepted_name_rank"
|
1018
|
ELSE "*Name_matched_rank"
|
1019
|
END$$)::util.derived_col_def);
|
1020
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
1021
|
|
1022
|
to rename:
|
1023
|
# rename column
|
1024
|
# rename CHECK constraint
|
1025
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
1026
|
';
|
1027
|
|
1028
|
|
1029
|
--
|
1030
|
-- Name: COLUMN taxon_match."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
1031
|
--
|
1032
|
|
1033
|
COMMENT ON COLUMN taxon_match."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org" IS '
|
1034
|
= CASE
|
1035
|
WHEN matched_has_accepted THEN "Accepted_family__@TNRS__@vegpath.org"
|
1036
|
ELSE "*Name_matched_accepted_family"
|
1037
|
END
|
1038
|
|
1039
|
derived column
|
1040
|
|
1041
|
to modify expr:
|
1042
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE
|
1043
|
WHEN matched_has_accepted THEN "Accepted_family__@TNRS__@vegpath.org"
|
1044
|
ELSE "*Name_matched_accepted_family"
|
1045
|
END$$)::util.derived_col_def);
|
1046
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
1047
|
|
1048
|
to rename:
|
1049
|
# rename column
|
1050
|
# rename CHECK constraint
|
1051
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
1052
|
';
|
1053
|
|
1054
|
|
1055
|
--
|
1056
|
-- Name: COLUMN taxon_match."[scrubbed_]genus__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
1057
|
--
|
1058
|
|
1059
|
COMMENT ON COLUMN taxon_match."[scrubbed_]genus__@DwC__@vegpath.org" IS '
|
1060
|
= CASE
|
1061
|
WHEN matched_has_accepted THEN "[accepted_]genus__@DwC__@vegpath.org"
|
1062
|
ELSE "*Genus_matched"
|
1063
|
END
|
1064
|
|
1065
|
derived column
|
1066
|
|
1067
|
to modify expr:
|
1068
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]genus__@DwC__@vegpath.org'')::util.col, $$CASE
|
1069
|
WHEN matched_has_accepted THEN "[accepted_]genus__@DwC__@vegpath.org"
|
1070
|
ELSE "*Genus_matched"
|
1071
|
END$$)::util.derived_col_def);
|
1072
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
1073
|
|
1074
|
to rename:
|
1075
|
# rename column
|
1076
|
# rename CHECK constraint
|
1077
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
1078
|
';
|
1079
|
|
1080
|
|
1081
|
--
|
1082
|
-- Name: COLUMN taxon_match."[scrubbed_]specificEpithet__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
1083
|
--
|
1084
|
|
1085
|
COMMENT ON COLUMN taxon_match."[scrubbed_]specificEpithet__@DwC__@vegpath.org" IS '
|
1086
|
= CASE
|
1087
|
WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org"
|
1088
|
ELSE "*Specific_epithet_matched"
|
1089
|
END
|
1090
|
|
1091
|
derived column
|
1092
|
|
1093
|
to modify expr:
|
1094
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]specificEpithet__@DwC__@vegpath.org'')::util.col, $$CASE
|
1095
|
WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org"
|
1096
|
ELSE "*Specific_epithet_matched"
|
1097
|
END$$)::util.derived_col_def);
|
1098
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
1099
|
|
1100
|
to rename:
|
1101
|
# rename column
|
1102
|
# rename CHECK constraint
|
1103
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
1104
|
';
|
1105
|
|
1106
|
|
1107
|
--
|
1108
|
-- Name: COLUMN taxon_match."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
1109
|
--
|
1110
|
|
1111
|
COMMENT ON COLUMN taxon_match."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org" IS '
|
1112
|
= CASE
|
1113
|
WHEN matched_has_accepted THEN "*Accepted_name_species"
|
1114
|
ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org"
|
1115
|
END
|
1116
|
|
1117
|
derived column
|
1118
|
|
1119
|
to modify expr:
|
1120
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE
|
1121
|
WHEN matched_has_accepted THEN "*Accepted_name_species"
|
1122
|
ELSE "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org"
|
1123
|
END$$)::util.derived_col_def);
|
1124
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
1125
|
|
1126
|
to rename:
|
1127
|
# rename column
|
1128
|
# rename CHECK constraint
|
1129
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
1130
|
';
|
1131
|
|
1132
|
|
1133
|
--
|
1134
|
-- Name: COLUMN taxon_match."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
1135
|
--
|
1136
|
|
1137
|
COMMENT ON COLUMN taxon_match."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org" IS '
|
1138
|
= CASE
|
1139
|
WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = ''family''::text) THEN concat_ws('' ''::text, "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
1140
|
WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = ''genus''::text) THEN concat_ws('' ''::text, "[scrubbed_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
1141
|
ELSE "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org"
|
1142
|
END
|
1143
|
|
1144
|
derived column
|
1145
|
|
1146
|
to modify expr:
|
1147
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org'')::util.col, $$CASE
|
1148
|
WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = ''family''::text) THEN concat_ws('' ''::text, "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
1149
|
WHEN ("[scrubbed_]taxonRank__@DwC__@vegpath.org" = ''genus''::text) THEN concat_ws('' ''::text, "[scrubbed_]genus__@DwC__@vegpath.org", "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org")
|
1150
|
ELSE "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org"
|
1151
|
END$$)::util.derived_col_def);
|
1152
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
1153
|
|
1154
|
to rename:
|
1155
|
# rename column
|
1156
|
# rename CHECK constraint
|
1157
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
1158
|
';
|
1159
|
|
1160
|
|
1161
|
--
|
1162
|
-- Name: COLUMN taxon_match."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
1163
|
--
|
1164
|
|
1165
|
COMMENT ON COLUMN taxon_match."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org" IS '
|
1166
|
= CASE
|
1167
|
WHEN matched_has_accepted THEN "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org"
|
1168
|
ELSE "*Infraspecific_rank"
|
1169
|
END
|
1170
|
|
1171
|
derived column
|
1172
|
|
1173
|
to modify expr:
|
1174
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org'')::util.col, $$CASE
|
1175
|
WHEN matched_has_accepted THEN "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org"
|
1176
|
ELSE "*Infraspecific_rank"
|
1177
|
END$$)::util.derived_col_def);
|
1178
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
1179
|
|
1180
|
to rename:
|
1181
|
# rename column
|
1182
|
# rename CHECK constraint
|
1183
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
1184
|
';
|
1185
|
|
1186
|
|
1187
|
--
|
1188
|
-- Name: COLUMN taxon_match."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
1189
|
--
|
1190
|
|
1191
|
COMMENT ON COLUMN taxon_match."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org" IS '
|
1192
|
= CASE
|
1193
|
WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org"
|
1194
|
ELSE "*Infraspecific_epithet_matched"
|
1195
|
END
|
1196
|
|
1197
|
derived column
|
1198
|
|
1199
|
to modify expr:
|
1200
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org'')::util.col, $$CASE
|
1201
|
WHEN matched_has_accepted THEN "[accepted_]specificEpithet__@DwC__@vegpath.org"
|
1202
|
ELSE "*Infraspecific_epithet_matched"
|
1203
|
END$$)::util.derived_col_def);
|
1204
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
1205
|
|
1206
|
to rename:
|
1207
|
# rename column
|
1208
|
# rename CHECK constraint
|
1209
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
1210
|
';
|
1211
|
|
1212
|
|
1213
|
--
|
1214
|
-- Name: COLUMN taxon_match."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
1215
|
--
|
1216
|
|
1217
|
COMMENT ON COLUMN taxon_match."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org" IS '
|
1218
|
= CASE
|
1219
|
WHEN matched_has_accepted THEN "*Accepted_name"
|
1220
|
ELSE "matched~Name[_no_author]___@TNRS__@vegpath.org"
|
1221
|
END
|
1222
|
|
1223
|
derived column
|
1224
|
|
1225
|
to modify expr:
|
1226
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE
|
1227
|
WHEN matched_has_accepted THEN "*Accepted_name"
|
1228
|
ELSE "matched~Name[_no_author]___@TNRS__@vegpath.org"
|
1229
|
END$$)::util.derived_col_def);
|
1230
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
1231
|
|
1232
|
to rename:
|
1233
|
# rename column
|
1234
|
# rename CHECK constraint
|
1235
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
1236
|
';
|
1237
|
|
1238
|
|
1239
|
--
|
1240
|
-- Name: COLUMN taxon_match."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
1241
|
--
|
1242
|
|
1243
|
COMMENT ON COLUMN taxon_match."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org" IS '
|
1244
|
= CASE
|
1245
|
WHEN matched_has_accepted THEN "*Accepted_name_author"
|
1246
|
ELSE "*Name_matched_author"
|
1247
|
END
|
1248
|
|
1249
|
derived column
|
1250
|
|
1251
|
to modify expr:
|
1252
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org'')::util.col, $$CASE
|
1253
|
WHEN matched_has_accepted THEN "*Accepted_name_author"
|
1254
|
ELSE "*Name_matched_author"
|
1255
|
END$$)::util.derived_col_def);
|
1256
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
1257
|
|
1258
|
to rename:
|
1259
|
# rename column
|
1260
|
# rename CHECK constraint
|
1261
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
1262
|
';
|
1263
|
|
1264
|
|
1265
|
--
|
1266
|
-- Name: COLUMN taxon_match."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org"; Type: COMMENT; Schema: TNRS; Owner: bien
|
1267
|
--
|
1268
|
|
1269
|
COMMENT ON COLUMN taxon_match."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org" IS '
|
1270
|
= CASE
|
1271
|
WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org"
|
1272
|
ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org"
|
1273
|
END
|
1274
|
|
1275
|
derived column
|
1276
|
|
1277
|
to modify expr:
|
1278
|
SELECT util.derived_col_update(((''"TNRS".taxon_match'', ''[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org'')::util.col, $$CASE
|
1279
|
WHEN matched_has_accepted THEN "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org"
|
1280
|
ELSE "[matched_]scientificName[_with_author]__@DwC__@vegpath.org"
|
1281
|
END$$)::util.derived_col_def);
|
1282
|
SELECT util.derived_cols_populate(''"TNRS".taxon_match''::regclass);
|
1283
|
|
1284
|
to rename:
|
1285
|
# rename column
|
1286
|
# rename CHECK constraint
|
1287
|
SELECT util.derived_cols_update(''"TNRS".taxon_match''::regclass);
|
1288
|
';
|
1289
|
|
1290
|
|
1291
|
--
|
1292
|
-- Name: taxon_best_match; Type: VIEW; Schema: TNRS; Owner: bien
|
1293
|
--
|
1294
|
|
1295
|
CREATE VIEW taxon_best_match AS
|
1296
|
SELECT taxon_match.batch,
|
1297
|
taxon_match.match_num,
|
1298
|
taxon_match."*Name_number",
|
1299
|
taxon_match."*Name_submitted",
|
1300
|
taxon_match."*Overall_score",
|
1301
|
taxon_match."*Name_matched",
|
1302
|
taxon_match."*Name_matched_rank",
|
1303
|
taxon_match."*Name_score",
|
1304
|
taxon_match."*Name_matched_author",
|
1305
|
taxon_match."*Name_matched_url",
|
1306
|
taxon_match."*Author_matched",
|
1307
|
taxon_match."*Author_score",
|
1308
|
taxon_match."*Family_matched",
|
1309
|
taxon_match."*Family_score",
|
1310
|
taxon_match."*Name_matched_accepted_family",
|
1311
|
taxon_match."*Genus_matched",
|
1312
|
taxon_match."*Genus_score",
|
1313
|
taxon_match."*Specific_epithet_matched",
|
1314
|
taxon_match."*Specific_epithet_score",
|
1315
|
taxon_match."*Infraspecific_rank",
|
1316
|
taxon_match."*Infraspecific_epithet_matched",
|
1317
|
taxon_match."*Infraspecific_epithet_score",
|
1318
|
taxon_match."*Infraspecific_rank_2",
|
1319
|
taxon_match."*Infraspecific_epithet_2_matched",
|
1320
|
taxon_match."*Infraspecific_epithet_2_score",
|
1321
|
taxon_match."*Annotations",
|
1322
|
taxon_match."*Unmatched_terms",
|
1323
|
taxon_match."*Taxonomic_status",
|
1324
|
taxon_match."*Accepted_name",
|
1325
|
taxon_match."*Accepted_name_author",
|
1326
|
taxon_match."*Accepted_name_rank",
|
1327
|
taxon_match."*Accepted_name_url",
|
1328
|
taxon_match."*Accepted_name_species",
|
1329
|
taxon_match."*Accepted_name_family",
|
1330
|
taxon_match."*Selected",
|
1331
|
taxon_match."*Source",
|
1332
|
taxon_match."*Warnings",
|
1333
|
taxon_match."*Accepted_name_lsid",
|
1334
|
taxon_match.is_valid_match,
|
1335
|
taxon_match.scrubbed_unique_taxon_name,
|
1336
|
taxon_match."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org",
|
1337
|
taxon_match."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org",
|
1338
|
taxon_match."matched~Name[_no_author]___@TNRS__@vegpath.org",
|
1339
|
taxon_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org",
|
1340
|
taxon_match.matched_has_accepted,
|
1341
|
taxon_match."Accepted_family__@TNRS__@vegpath.org",
|
1342
|
taxon_match."__accepted_{genus,specific_epithet}",
|
1343
|
taxon_match."[accepted_]genus__@DwC__@vegpath.org",
|
1344
|
taxon_match."[accepted_]specificEpithet__@DwC__@vegpath.org",
|
1345
|
taxon_match."Accepted_species[_binomial]__@TNRS__@vegpath.org",
|
1346
|
taxon_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org",
|
1347
|
taxon_match.__accepted_infraspecific_label,
|
1348
|
taxon_match."__accepted_infraspecific_{rank,epithet}",
|
1349
|
taxon_match."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org",
|
1350
|
taxon_match."[accepted_]infraspecificEpithet__@DwC__@vegpath.org",
|
1351
|
taxon_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org",
|
1352
|
taxon_match."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org",
|
1353
|
taxon_match."[scrubbed_]taxonRank__@DwC__@vegpath.org",
|
1354
|
taxon_match."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org",
|
1355
|
taxon_match."[scrubbed_]genus__@DwC__@vegpath.org",
|
1356
|
taxon_match."[scrubbed_]specificEpithet__@DwC__@vegpath.org",
|
1357
|
taxon_match."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org",
|
1358
|
taxon_match."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org",
|
1359
|
taxon_match."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org",
|
1360
|
taxon_match."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org",
|
1361
|
taxon_match."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org",
|
1362
|
taxon_match."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org",
|
1363
|
taxon_match."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org"
|
1364
|
FROM taxon_match
|
1365
|
WHERE (taxon_match."*Selected" = 'true'::text);
|
1366
|
|
1367
|
|
1368
|
ALTER TABLE "TNRS".taxon_best_match OWNER TO bien;
|
1369
|
|
1370
|
--
|
1371
|
-- Name: VIEW taxon_best_match; Type: COMMENT; Schema: TNRS; Owner: bien
|
1372
|
--
|
1373
|
|
1374
|
COMMENT ON VIEW taxon_best_match IS '
|
1375
|
to modify:
|
1376
|
SELECT util.recreate_view(''"TNRS"."taxon_best_match"'', $$
|
1377
|
SELECT __
|
1378
|
$$);
|
1379
|
';
|
1380
|
|
1381
|
|
1382
|
--
|
1383
|
-- Name: MatchedTaxon; Type: VIEW; Schema: TNRS; Owner: bien
|
1384
|
--
|
1385
|
|
1386
|
CREATE VIEW "MatchedTaxon" AS
|
1387
|
SELECT taxon_best_match.batch,
|
1388
|
taxon_best_match.match_num,
|
1389
|
taxon_best_match."*Name_number",
|
1390
|
taxon_best_match."*Name_submitted",
|
1391
|
taxon_best_match."*Overall_score",
|
1392
|
taxon_best_match."*Name_matched",
|
1393
|
taxon_best_match."*Name_matched_rank",
|
1394
|
taxon_best_match."*Name_score",
|
1395
|
taxon_best_match."*Name_matched_author",
|
1396
|
taxon_best_match."*Name_matched_url",
|
1397
|
taxon_best_match."*Author_matched",
|
1398
|
taxon_best_match."*Author_score",
|
1399
|
taxon_best_match."*Family_matched",
|
1400
|
taxon_best_match."*Family_score",
|
1401
|
taxon_best_match."*Name_matched_accepted_family",
|
1402
|
taxon_best_match."*Genus_matched",
|
1403
|
taxon_best_match."*Genus_score",
|
1404
|
taxon_best_match."*Specific_epithet_matched",
|
1405
|
taxon_best_match."*Specific_epithet_score",
|
1406
|
taxon_best_match."*Infraspecific_rank",
|
1407
|
taxon_best_match."*Infraspecific_epithet_matched",
|
1408
|
taxon_best_match."*Infraspecific_epithet_score",
|
1409
|
taxon_best_match."*Infraspecific_rank_2",
|
1410
|
taxon_best_match."*Infraspecific_epithet_2_matched",
|
1411
|
taxon_best_match."*Infraspecific_epithet_2_score",
|
1412
|
taxon_best_match."*Annotations",
|
1413
|
taxon_best_match."*Unmatched_terms",
|
1414
|
taxon_best_match."*Taxonomic_status",
|
1415
|
taxon_best_match."*Accepted_name",
|
1416
|
taxon_best_match."*Accepted_name_author",
|
1417
|
taxon_best_match."*Accepted_name_rank",
|
1418
|
taxon_best_match."*Accepted_name_url",
|
1419
|
taxon_best_match."*Accepted_name_species",
|
1420
|
taxon_best_match."*Accepted_name_family",
|
1421
|
taxon_best_match."*Selected",
|
1422
|
taxon_best_match."*Source",
|
1423
|
taxon_best_match."*Warnings",
|
1424
|
taxon_best_match."*Accepted_name_lsid",
|
1425
|
taxon_best_match.is_valid_match,
|
1426
|
taxon_best_match.scrubbed_unique_taxon_name,
|
1427
|
taxon_best_match."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org",
|
1428
|
taxon_best_match."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org",
|
1429
|
taxon_best_match."matched~Name[_no_author]___@TNRS__@vegpath.org",
|
1430
|
taxon_best_match."[matched_]scientificName[_with_author]__@DwC__@vegpath.org",
|
1431
|
taxon_best_match.matched_has_accepted,
|
1432
|
taxon_best_match."Accepted_family__@TNRS__@vegpath.org",
|
1433
|
taxon_best_match."__accepted_{genus,specific_epithet}",
|
1434
|
taxon_best_match."[accepted_]genus__@DwC__@vegpath.org",
|
1435
|
taxon_best_match."[accepted_]specificEpithet__@DwC__@vegpath.org",
|
1436
|
taxon_best_match."Accepted_species[_binomial]__@TNRS__@vegpath.org",
|
1437
|
taxon_best_match."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org",
|
1438
|
taxon_best_match.__accepted_infraspecific_label,
|
1439
|
taxon_best_match."__accepted_infraspecific_{rank,epithet}",
|
1440
|
taxon_best_match."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org",
|
1441
|
taxon_best_match."[accepted_]infraspecificEpithet__@DwC__@vegpath.org",
|
1442
|
taxon_best_match."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org",
|
1443
|
taxon_best_match."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org",
|
1444
|
taxon_best_match."[scrubbed_]taxonRank__@DwC__@vegpath.org",
|
1445
|
taxon_best_match."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org",
|
1446
|
taxon_best_match."[scrubbed_]genus__@DwC__@vegpath.org",
|
1447
|
taxon_best_match."[scrubbed_]specificEpithet__@DwC__@vegpath.org",
|
1448
|
taxon_best_match."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org",
|
1449
|
taxon_best_match."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org",
|
1450
|
taxon_best_match."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org",
|
1451
|
taxon_best_match."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org",
|
1452
|
taxon_best_match."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org",
|
1453
|
taxon_best_match."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org",
|
1454
|
taxon_best_match."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org",
|
1455
|
map_taxonomic_status(taxon_best_match."*Taxonomic_status", taxon_best_match."*Accepted_name") AS "taxonomicStatus",
|
1456
|
CASE
|
1457
|
WHEN (taxon_best_match."*Accepted_name_rank" = 'family'::text) THEN concat_ws(' '::text, taxon_best_match."*Accepted_name_family", taxon_best_match."*Unmatched_terms")
|
1458
|
WHEN (taxon_best_match."*Accepted_name_rank" = 'genus'::text) THEN concat_ws(' '::text, taxon_best_match."*Accepted_name", taxon_best_match."*Unmatched_terms")
|
1459
|
ELSE taxon_best_match."*Accepted_name_species"
|
1460
|
END AS accepted_morphospecies_binomial
|
1461
|
FROM taxon_best_match;
|
1462
|
|
1463
|
|
1464
|
ALTER TABLE "TNRS"."MatchedTaxon" OWNER TO bien;
|
1465
|
|
1466
|
--
|
1467
|
-- Name: VIEW "MatchedTaxon"; Type: COMMENT; Schema: TNRS; Owner: bien
|
1468
|
--
|
1469
|
|
1470
|
COMMENT ON VIEW "MatchedTaxon" IS '
|
1471
|
to modify:
|
1472
|
SELECT util.recreate_view(''"TNRS"."MatchedTaxon"'', $$
|
1473
|
SELECT __
|
1474
|
$$);
|
1475
|
';
|
1476
|
|
1477
|
|
1478
|
--
|
1479
|
-- Name: Source; Type: TABLE; Schema: TNRS; Owner: bien; Tablespace:
|
1480
|
--
|
1481
|
|
1482
|
CREATE TABLE "Source" (
|
1483
|
"*row_num" integer NOT NULL,
|
1484
|
"sourceType" text DEFAULT 'aggregator'::text NOT NULL,
|
1485
|
"datasetURL" text DEFAULT 'http://[TNRS_dev_server].iplantcollaborative.org/TNRSapp.html'::text NOT NULL,
|
1486
|
CONSTRAINT nulls_mapped CHECK (true)
|
1487
|
);
|
1488
|
|
1489
|
|
1490
|
ALTER TABLE "TNRS"."Source" OWNER TO bien;
|
1491
|
|
1492
|
--
|
1493
|
-- Name: COLUMN "Source"."sourceType"; Type: COMMENT; Schema: TNRS; Owner: bien
|
1494
|
--
|
1495
|
|
1496
|
COMMENT ON COLUMN "Source"."sourceType" IS '
|
1497
|
constant
|
1498
|
';
|
1499
|
|
1500
|
|
1501
|
--
|
1502
|
-- Name: COLUMN "Source"."datasetURL"; Type: COMMENT; Schema: TNRS; Owner: bien
|
1503
|
--
|
1504
|
|
1505
|
COMMENT ON COLUMN "Source"."datasetURL" IS '
|
1506
|
constant
|
1507
|
';
|
1508
|
|
1509
|
|
1510
|
--
|
1511
|
-- Name: ValidMatchedTaxon; Type: VIEW; Schema: TNRS; Owner: bien
|
1512
|
--
|
1513
|
|
1514
|
CREATE VIEW "ValidMatchedTaxon" AS
|
1515
|
SELECT "MatchedTaxon".batch,
|
1516
|
"MatchedTaxon".match_num,
|
1517
|
"MatchedTaxon"."*Name_number",
|
1518
|
"MatchedTaxon"."*Name_submitted",
|
1519
|
"MatchedTaxon"."*Overall_score",
|
1520
|
"MatchedTaxon"."*Name_matched",
|
1521
|
"MatchedTaxon"."*Name_matched_rank",
|
1522
|
"MatchedTaxon"."*Name_score",
|
1523
|
"MatchedTaxon"."*Name_matched_author",
|
1524
|
"MatchedTaxon"."*Name_matched_url",
|
1525
|
"MatchedTaxon"."*Author_matched",
|
1526
|
"MatchedTaxon"."*Author_score",
|
1527
|
"MatchedTaxon"."*Family_matched",
|
1528
|
"MatchedTaxon"."*Family_score",
|
1529
|
"MatchedTaxon"."*Name_matched_accepted_family",
|
1530
|
"MatchedTaxon"."*Genus_matched",
|
1531
|
"MatchedTaxon"."*Genus_score",
|
1532
|
"MatchedTaxon"."*Specific_epithet_matched",
|
1533
|
"MatchedTaxon"."*Specific_epithet_score",
|
1534
|
"MatchedTaxon"."*Infraspecific_rank",
|
1535
|
"MatchedTaxon"."*Infraspecific_epithet_matched",
|
1536
|
"MatchedTaxon"."*Infraspecific_epithet_score",
|
1537
|
"MatchedTaxon"."*Infraspecific_rank_2",
|
1538
|
"MatchedTaxon"."*Infraspecific_epithet_2_matched",
|
1539
|
"MatchedTaxon"."*Infraspecific_epithet_2_score",
|
1540
|
"MatchedTaxon"."*Annotations",
|
1541
|
"MatchedTaxon"."*Unmatched_terms",
|
1542
|
"MatchedTaxon"."*Taxonomic_status",
|
1543
|
"MatchedTaxon"."*Accepted_name",
|
1544
|
"MatchedTaxon"."*Accepted_name_author",
|
1545
|
"MatchedTaxon"."*Accepted_name_rank",
|
1546
|
"MatchedTaxon"."*Accepted_name_url",
|
1547
|
"MatchedTaxon"."*Accepted_name_species",
|
1548
|
"MatchedTaxon"."*Accepted_name_family",
|
1549
|
"MatchedTaxon"."*Selected",
|
1550
|
"MatchedTaxon"."*Source",
|
1551
|
"MatchedTaxon"."*Warnings",
|
1552
|
"MatchedTaxon"."*Accepted_name_lsid",
|
1553
|
"MatchedTaxon".is_valid_match,
|
1554
|
"MatchedTaxon".scrubbed_unique_taxon_name,
|
1555
|
"MatchedTaxon"."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org",
|
1556
|
"MatchedTaxon"."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org",
|
1557
|
"MatchedTaxon"."matched~Name[_no_author]___@TNRS__@vegpath.org",
|
1558
|
"MatchedTaxon"."[matched_]scientificName[_with_author]__@DwC__@vegpath.org",
|
1559
|
"MatchedTaxon".matched_has_accepted,
|
1560
|
"MatchedTaxon"."Accepted_family__@TNRS__@vegpath.org",
|
1561
|
"MatchedTaxon"."__accepted_{genus,specific_epithet}",
|
1562
|
"MatchedTaxon"."[accepted_]genus__@DwC__@vegpath.org",
|
1563
|
"MatchedTaxon"."[accepted_]specificEpithet__@DwC__@vegpath.org",
|
1564
|
"MatchedTaxon"."Accepted_species[_binomial]__@TNRS__@vegpath.org",
|
1565
|
"MatchedTaxon"."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org",
|
1566
|
"MatchedTaxon".__accepted_infraspecific_label,
|
1567
|
"MatchedTaxon"."__accepted_infraspecific_{rank,epithet}",
|
1568
|
"MatchedTaxon"."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org",
|
1569
|
"MatchedTaxon"."[accepted_]infraspecificEpithet__@DwC__@vegpath.org",
|
1570
|
"MatchedTaxon"."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org",
|
1571
|
"MatchedTaxon"."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org",
|
1572
|
"MatchedTaxon"."[scrubbed_]taxonRank__@DwC__@vegpath.org",
|
1573
|
"MatchedTaxon"."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org",
|
1574
|
"MatchedTaxon"."[scrubbed_]genus__@DwC__@vegpath.org",
|
1575
|
"MatchedTaxon"."[scrubbed_]specificEpithet__@DwC__@vegpath.org",
|
1576
|
"MatchedTaxon"."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org",
|
1577
|
"MatchedTaxon"."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org",
|
1578
|
"MatchedTaxon"."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org",
|
1579
|
"MatchedTaxon"."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org",
|
1580
|
"MatchedTaxon"."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org",
|
1581
|
"MatchedTaxon"."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org",
|
1582
|
"MatchedTaxon"."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org",
|
1583
|
"MatchedTaxon"."taxonomicStatus",
|
1584
|
"MatchedTaxon".accepted_morphospecies_binomial
|
1585
|
FROM "MatchedTaxon"
|
1586
|
WHERE "MatchedTaxon".is_valid_match;
|
1587
|
|
1588
|
|
1589
|
ALTER TABLE "TNRS"."ValidMatchedTaxon" OWNER TO bien;
|
1590
|
|
1591
|
--
|
1592
|
-- Name: VIEW "ValidMatchedTaxon"; Type: COMMENT; Schema: TNRS; Owner: bien
|
1593
|
--
|
1594
|
|
1595
|
COMMENT ON VIEW "ValidMatchedTaxon" IS '
|
1596
|
to update, use * as the column list
|
1597
|
';
|
1598
|
|
1599
|
|
1600
|
--
|
1601
|
-- Name: batch; Type: TABLE; Schema: TNRS; Owner: bien; Tablespace:
|
1602
|
--
|
1603
|
|
1604
|
CREATE TABLE batch (
|
1605
|
id text NOT NULL,
|
1606
|
id_by_time text,
|
1607
|
time_submitted timestamp with time zone DEFAULT now(),
|
1608
|
client_version text
|
1609
|
);
|
1610
|
|
1611
|
|
1612
|
ALTER TABLE "TNRS".batch OWNER TO bien;
|
1613
|
|
1614
|
--
|
1615
|
-- Name: batch_download_settings; Type: TABLE; Schema: TNRS; Owner: bien; Tablespace:
|
1616
|
--
|
1617
|
|
1618
|
CREATE TABLE batch_download_settings (
|
1619
|
id text NOT NULL,
|
1620
|
"E-mail" text,
|
1621
|
"Id" text,
|
1622
|
"Job type" text,
|
1623
|
"Contains Id" boolean,
|
1624
|
"Start time" text,
|
1625
|
"Finish time" text,
|
1626
|
"TNRS version" text,
|
1627
|
"Sources selected" text,
|
1628
|
"Match threshold" double precision,
|
1629
|
"Classification" text,
|
1630
|
"Allow partial matches?" boolean,
|
1631
|
"Sort by source" boolean,
|
1632
|
"Constrain by higher taxonomy" boolean
|
1633
|
);
|
1634
|
|
1635
|
|
1636
|
ALTER TABLE "TNRS".batch_download_settings OWNER TO bien;
|
1637
|
|
1638
|
--
|
1639
|
-- Name: TABLE batch_download_settings; Type: COMMENT; Schema: TNRS; Owner: bien
|
1640
|
--
|
1641
|
|
1642
|
COMMENT ON TABLE batch_download_settings IS '
|
1643
|
stores data from http://tnrs.iplantcollaborative.org/TNRSapp.html > Submit List > results section > Download settings > settings.txt
|
1644
|
';
|
1645
|
|
1646
|
|
1647
|
--
|
1648
|
-- Name: client_version; Type: TABLE; Schema: TNRS; Owner: bien; Tablespace:
|
1649
|
--
|
1650
|
|
1651
|
CREATE TABLE client_version (
|
1652
|
id text NOT NULL,
|
1653
|
global_rev integer NOT NULL,
|
1654
|
"/lib/tnrs.py rev" integer,
|
1655
|
"/bin/tnrs_db rev" integer
|
1656
|
);
|
1657
|
|
1658
|
|
1659
|
ALTER TABLE "TNRS".client_version OWNER TO bien;
|
1660
|
|
1661
|
--
|
1662
|
-- Name: TABLE client_version; Type: COMMENT; Schema: TNRS; Owner: bien
|
1663
|
--
|
1664
|
|
1665
|
COMMENT ON TABLE client_version IS '
|
1666
|
contains svn revisions
|
1667
|
';
|
1668
|
|
1669
|
|
1670
|
--
|
1671
|
-- Name: COLUMN client_version.global_rev; Type: COMMENT; Schema: TNRS; Owner: bien
|
1672
|
--
|
1673
|
|
1674
|
COMMENT ON COLUMN client_version.global_rev IS '
|
1675
|
from `svn info .` > Last Changed Rev
|
1676
|
';
|
1677
|
|
1678
|
|
1679
|
--
|
1680
|
-- Name: COLUMN client_version."/lib/tnrs.py rev"; Type: COMMENT; Schema: TNRS; Owner: bien
|
1681
|
--
|
1682
|
|
1683
|
COMMENT ON COLUMN client_version."/lib/tnrs.py rev" IS '
|
1684
|
from `svn info lib/tnrs.py` > Last Changed Rev
|
1685
|
';
|
1686
|
|
1687
|
|
1688
|
--
|
1689
|
-- Name: COLUMN client_version."/bin/tnrs_db rev"; Type: COMMENT; Schema: TNRS; Owner: bien
|
1690
|
--
|
1691
|
|
1692
|
COMMENT ON COLUMN client_version."/bin/tnrs_db rev" IS '
|
1693
|
from `svn info bin/tnrs_db` > Last Changed Rev
|
1694
|
';
|
1695
|
|
1696
|
|
1697
|
--
|
1698
|
-- Name: taxon_match_input; Type: VIEW; Schema: TNRS; Owner: bien
|
1699
|
--
|
1700
|
|
1701
|
CREATE VIEW taxon_match_input AS
|
1702
|
SELECT taxon_match."*Name_number" AS "Name_number",
|
1703
|
taxon_match."*Name_submitted" AS "Name_submitted",
|
1704
|
taxon_match."*Overall_score" AS "Overall_score",
|
1705
|
taxon_match."*Name_matched" AS "Name_matched",
|
1706
|
taxon_match."*Name_matched_rank" AS "Name_matched_rank",
|
1707
|
taxon_match."*Name_score" AS "Name_score",
|
1708
|
taxon_match."*Name_matched_author" AS "Name_matched_author",
|
1709
|
taxon_match."*Name_matched_url" AS "Name_matched_url",
|
1710
|
taxon_match."*Author_matched" AS "Author_matched",
|
1711
|
taxon_match."*Author_score" AS "Author_score",
|
1712
|
taxon_match."*Family_matched" AS "Family_matched",
|
1713
|
taxon_match."*Family_score" AS "Family_score",
|
1714
|
taxon_match."*Name_matched_accepted_family" AS "Name_matched_accepted_family",
|
1715
|
taxon_match."*Genus_matched" AS "Genus_matched",
|
1716
|
taxon_match."*Genus_score" AS "Genus_score",
|
1717
|
taxon_match."*Specific_epithet_matched" AS "Specific_epithet_matched",
|
1718
|
taxon_match."*Specific_epithet_score" AS "Specific_epithet_score",
|
1719
|
taxon_match."*Infraspecific_rank" AS "Infraspecific_rank",
|
1720
|
taxon_match."*Infraspecific_epithet_matched" AS "Infraspecific_epithet_matched",
|
1721
|
taxon_match."*Infraspecific_epithet_score" AS "Infraspecific_epithet_score",
|
1722
|
taxon_match."*Infraspecific_rank_2" AS "Infraspecific_rank_2",
|
1723
|
taxon_match."*Infraspecific_epithet_2_matched" AS "Infraspecific_epithet_2_matched",
|
1724
|
taxon_match."*Infraspecific_epithet_2_score" AS "Infraspecific_epithet_2_score",
|
1725
|
taxon_match."*Annotations" AS "Annotations",
|
1726
|
taxon_match."*Unmatched_terms" AS "Unmatched_terms",
|
1727
|
taxon_match."*Taxonomic_status" AS "Taxonomic_status",
|
1728
|
taxon_match."*Accepted_name" AS "Accepted_name",
|
1729
|
taxon_match."*Accepted_name_author" AS "Accepted_name_author",
|
1730
|
taxon_match."*Accepted_name_rank" AS "Accepted_name_rank",
|
1731
|
taxon_match."*Accepted_name_url" AS "Accepted_name_url",
|
1732
|
taxon_match."*Accepted_name_species" AS "Accepted_name_species",
|
1733
|
taxon_match."*Accepted_name_family" AS "Accepted_name_family",
|
1734
|
taxon_match."*Selected" AS "Selected",
|
1735
|
taxon_match."*Source" AS "Source",
|
1736
|
taxon_match."*Warnings" AS "Warnings",
|
1737
|
taxon_match."*Accepted_name_lsid" AS "Accepted_name_lsid"
|
1738
|
FROM taxon_match;
|
1739
|
|
1740
|
|
1741
|
ALTER TABLE "TNRS".taxon_match_input OWNER TO bien;
|
1742
|
|
1743
|
--
|
1744
|
-- Name: taxon_match_input__copy_to; Type: TABLE; Schema: TNRS; Owner: bien; Tablespace:
|
1745
|
--
|
1746
|
|
1747
|
CREATE TABLE taxon_match_input__copy_to (
|
1748
|
"Name_number" integer,
|
1749
|
"Name_submitted" text,
|
1750
|
"Overall_score" double precision,
|
1751
|
"Name_matched" text,
|
1752
|
"Name_matched_rank" text,
|
1753
|
"Name_score" double precision,
|
1754
|
"Name_matched_author" text,
|
1755
|
"Name_matched_url" text,
|
1756
|
"Author_matched" text,
|
1757
|
"Author_score" double precision,
|
1758
|
"Family_matched" text,
|
1759
|
"Family_score" double precision,
|
1760
|
"Name_matched_accepted_family" text,
|
1761
|
"Genus_matched" text,
|
1762
|
"Genus_score" double precision,
|
1763
|
"Specific_epithet_matched" text,
|
1764
|
"Specific_epithet_score" double precision,
|
1765
|
"Infraspecific_rank" text,
|
1766
|
"Infraspecific_epithet_matched" text,
|
1767
|
"Infraspecific_epithet_score" double precision,
|
1768
|
"Infraspecific_rank_2" text,
|
1769
|
"Infraspecific_epithet_2_matched" text,
|
1770
|
"Infraspecific_epithet_2_score" double precision,
|
1771
|
"Annotations" text,
|
1772
|
"Unmatched_terms" text,
|
1773
|
"Taxonomic_status" text,
|
1774
|
"Accepted_name" text,
|
1775
|
"Accepted_name_author" text,
|
1776
|
"Accepted_name_rank" text,
|
1777
|
"Accepted_name_url" text,
|
1778
|
"Accepted_name_species" text,
|
1779
|
"Accepted_name_family" text,
|
1780
|
"Selected" text,
|
1781
|
"Source" text,
|
1782
|
"Warnings" text,
|
1783
|
"Accepted_name_lsid" text
|
1784
|
);
|
1785
|
|
1786
|
|
1787
|
ALTER TABLE "TNRS".taxon_match_input__copy_to OWNER TO bien;
|
1788
|
|
1789
|
--
|
1790
|
-- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: VIEW; Schema: TNRS; Owner: bien
|
1791
|
--
|
1792
|
|
1793
|
CREATE VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" AS
|
1794
|
SELECT taxon_match."*Name_submitted" AS scrubbed_unique_taxon_name,
|
1795
|
taxon_match."*Name_matched_rank" AS scrubbed_taxon_rank,
|
1796
|
COALESCE(taxon_match."*Name_matched_accepted_family", taxon_match."*Family_matched") AS scrubbed_family,
|
1797
|
taxon_match."*Genus_matched" AS scrubbed_genus,
|
1798
|
taxon_match."*Specific_epithet_matched" AS scrubbed_specific_epithet,
|
1799
|
taxon_match."*Infraspecific_rank" AS scrubbed_infraspecific_rank,
|
1800
|
taxon_match."*Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet,
|
1801
|
taxon_match."*Name_matched_author" AS scrubbed_author,
|
1802
|
taxon_match."*Name_matched" AS scrubbed_taxon_name_no_author,
|
1803
|
(taxon_match."*Name_matched" || COALESCE((' '::text || taxon_match."*Name_matched_author"), ''::text)) AS scrubbed_taxon_name_with_author
|
1804
|
FROM taxon_match;
|
1805
|
|
1806
|
|
1807
|
ALTER TABLE "TNRS"."taxon_scrub.scrubbed_unique_taxon_name.*" OWNER TO bien;
|
1808
|
|
1809
|
--
|
1810
|
-- Name: VIEW "taxon_scrub.scrubbed_unique_taxon_name.*"; Type: COMMENT; Schema: TNRS; Owner: bien
|
1811
|
--
|
1812
|
|
1813
|
COMMENT ON VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" IS '
|
1814
|
to modify:
|
1815
|
SELECT util.recreate_view(''"TNRS"."taxon_scrub.scrubbed_unique_taxon_name.*"'', $$
|
1816
|
SELECT __
|
1817
|
$$);
|
1818
|
|
1819
|
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.
|
1820
|
';
|
1821
|
|
1822
|
|
1823
|
--
|
1824
|
-- Name: taxon_scrub; Type: VIEW; Schema: TNRS; Owner: bien
|
1825
|
--
|
1826
|
|
1827
|
CREATE VIEW taxon_scrub AS
|
1828
|
SELECT "ValidMatchedTaxon".scrubbed_unique_taxon_name,
|
1829
|
"ValidMatchedTaxon".batch,
|
1830
|
"ValidMatchedTaxon".match_num,
|
1831
|
"ValidMatchedTaxon"."*Name_number",
|
1832
|
"ValidMatchedTaxon"."*Name_submitted",
|
1833
|
"ValidMatchedTaxon"."*Overall_score",
|
1834
|
"ValidMatchedTaxon"."*Name_matched",
|
1835
|
"ValidMatchedTaxon"."*Name_matched_rank",
|
1836
|
"ValidMatchedTaxon"."*Name_score",
|
1837
|
"ValidMatchedTaxon"."*Name_matched_author",
|
1838
|
"ValidMatchedTaxon"."*Name_matched_url",
|
1839
|
"ValidMatchedTaxon"."*Author_matched",
|
1840
|
"ValidMatchedTaxon"."*Author_score",
|
1841
|
"ValidMatchedTaxon"."*Family_matched",
|
1842
|
"ValidMatchedTaxon"."*Family_score",
|
1843
|
"ValidMatchedTaxon"."*Name_matched_accepted_family",
|
1844
|
"ValidMatchedTaxon"."*Genus_matched",
|
1845
|
"ValidMatchedTaxon"."*Genus_score",
|
1846
|
"ValidMatchedTaxon"."*Specific_epithet_matched",
|
1847
|
"ValidMatchedTaxon"."*Specific_epithet_score",
|
1848
|
"ValidMatchedTaxon"."*Infraspecific_rank",
|
1849
|
"ValidMatchedTaxon"."*Infraspecific_epithet_matched",
|
1850
|
"ValidMatchedTaxon"."*Infraspecific_epithet_score",
|
1851
|
"ValidMatchedTaxon"."*Infraspecific_rank_2",
|
1852
|
"ValidMatchedTaxon"."*Infraspecific_epithet_2_matched",
|
1853
|
"ValidMatchedTaxon"."*Infraspecific_epithet_2_score",
|
1854
|
"ValidMatchedTaxon"."*Annotations",
|
1855
|
"ValidMatchedTaxon"."*Unmatched_terms",
|
1856
|
"ValidMatchedTaxon"."*Taxonomic_status",
|
1857
|
"ValidMatchedTaxon"."*Accepted_name",
|
1858
|
"ValidMatchedTaxon"."*Accepted_name_author",
|
1859
|
"ValidMatchedTaxon"."*Accepted_name_rank",
|
1860
|
"ValidMatchedTaxon"."*Accepted_name_url",
|
1861
|
"ValidMatchedTaxon"."*Accepted_name_species",
|
1862
|
"ValidMatchedTaxon"."*Accepted_name_family",
|
1863
|
"ValidMatchedTaxon"."*Selected",
|
1864
|
"ValidMatchedTaxon"."*Source",
|
1865
|
"ValidMatchedTaxon"."*Warnings",
|
1866
|
"ValidMatchedTaxon"."*Accepted_name_lsid",
|
1867
|
"ValidMatchedTaxon".is_valid_match,
|
1868
|
"ValidMatchedTaxon"."[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org",
|
1869
|
"ValidMatchedTaxon"."[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org",
|
1870
|
"ValidMatchedTaxon"."matched~Name[_no_author]___@TNRS__@vegpath.org",
|
1871
|
"ValidMatchedTaxon"."[matched_]scientificName[_with_author]__@DwC__@vegpath.org",
|
1872
|
"ValidMatchedTaxon".matched_has_accepted,
|
1873
|
"ValidMatchedTaxon"."Accepted_family__@TNRS__@vegpath.org",
|
1874
|
"ValidMatchedTaxon"."__accepted_{genus,specific_epithet}",
|
1875
|
"ValidMatchedTaxon"."[accepted_]genus__@DwC__@vegpath.org",
|
1876
|
"ValidMatchedTaxon"."[accepted_]specificEpithet__@DwC__@vegpath.org",
|
1877
|
"ValidMatchedTaxon"."Accepted_species[_binomial]__@TNRS__@vegpath.org",
|
1878
|
"ValidMatchedTaxon"."[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org",
|
1879
|
"ValidMatchedTaxon".__accepted_infraspecific_label,
|
1880
|
"ValidMatchedTaxon"."__accepted_infraspecific_{rank,epithet}",
|
1881
|
"ValidMatchedTaxon"."[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org",
|
1882
|
"ValidMatchedTaxon"."[accepted_]infraspecificEpithet__@DwC__@vegpath.org",
|
1883
|
"ValidMatchedTaxon"."[accepted_]scientificName[_with_author]__@DwC__@vegpath.org",
|
1884
|
"ValidMatchedTaxon"."[scrubbed_]taxonomicStatus__@DwC__@vegpath.org",
|
1885
|
"ValidMatchedTaxon"."[scrubbed_]taxonRank__@DwC__@vegpath.org",
|
1886
|
"ValidMatchedTaxon"."[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org",
|
1887
|
"ValidMatchedTaxon"."[scrubbed_]genus__@DwC__@vegpath.org",
|
1888
|
"ValidMatchedTaxon"."[scrubbed_]specificEpithet__@DwC__@vegpath.org",
|
1889
|
"ValidMatchedTaxon"."[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org",
|
1890
|
"ValidMatchedTaxon"."[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org",
|
1891
|
"ValidMatchedTaxon"."[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org",
|
1892
|
"ValidMatchedTaxon"."[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org",
|
1893
|
"ValidMatchedTaxon"."[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org",
|
1894
|
"ValidMatchedTaxon"."[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org",
|
1895
|
"ValidMatchedTaxon"."[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org",
|
1896
|
"ValidMatchedTaxon"."taxonomicStatus",
|
1897
|
"ValidMatchedTaxon".accepted_morphospecies_binomial,
|
1898
|
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank,
|
1899
|
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family,
|
1900
|
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus,
|
1901
|
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet,
|
1902
|
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_rank,
|
1903
|
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_epithet,
|
1904
|
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_author,
|
1905
|
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author,
|
1906
|
"taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_with_author,
|
1907
|
CASE
|
1908
|
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"."*Unmatched_terms")
|
1909
|
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"."*Unmatched_terms")
|
1910
|
ELSE (("taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus || ' '::text) || "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet)
|
1911
|
END AS scrubbed_morphospecies_binomial
|
1912
|
FROM ("ValidMatchedTaxon"
|
1913
|
LEFT JOIN "taxon_scrub.scrubbed_unique_taxon_name.*" USING (scrubbed_unique_taxon_name));
|
1914
|
|
1915
|
|
1916
|
ALTER TABLE "TNRS".taxon_scrub OWNER TO bien;
|
1917
|
|
1918
|
--
|
1919
|
-- Name: VIEW taxon_scrub; Type: COMMENT; Schema: TNRS; Owner: bien
|
1920
|
--
|
1921
|
|
1922
|
COMMENT ON VIEW taxon_scrub IS '
|
1923
|
to modify:
|
1924
|
SELECT util.recreate_view(''"TNRS".taxon_scrub'', $$
|
1925
|
SELECT __
|
1926
|
$$);
|
1927
|
';
|
1928
|
|
1929
|
|
1930
|
--
|
1931
|
-- Name: ~Source.map; Type: TABLE; Schema: TNRS; Owner: bien; Tablespace:
|
1932
|
--
|
1933
|
|
1934
|
CREATE TABLE "~Source.map" (
|
1935
|
"from" text NOT NULL,
|
1936
|
"to" text,
|
1937
|
filter text,
|
1938
|
notes text
|
1939
|
);
|
1940
|
|
1941
|
|
1942
|
ALTER TABLE "TNRS"."~Source.map" OWNER TO bien;
|
1943
|
|
1944
|
--
|
1945
|
-- Data for Name: Source; Type: TABLE DATA; Schema: TNRS; Owner: bien
|
1946
|
--
|
1947
|
|
1948
|
COPY "Source" ("*row_num", "sourceType", "datasetURL") FROM stdin;
|
1949
|
1 aggregator http://[TNRS_dev_server].iplantcollaborative.org/TNRSapp.html
|
1950
|
\.
|
1951
|
|
1952
|
|
1953
|
--
|
1954
|
-- Data for Name: batch; Type: TABLE DATA; Schema: TNRS; Owner: bien
|
1955
|
--
|
1956
|
|
1957
|
COPY batch (id, id_by_time, time_submitted, client_version) FROM stdin;
|
1958
|
2014-07-26 21:26:21.824011-07 2014-07-26 21:26:21.824011-07 2014-07-26 21:26:21.824011-07 \N
|
1959
|
\.
|
1960
|
|
1961
|
|
1962
|
--
|
1963
|
-- Data for Name: batch_download_settings; Type: TABLE DATA; Schema: TNRS; Owner: bien
|
1964
|
--
|
1965
|
|
1966
|
COPY batch_download_settings (id, "E-mail", "Id", "Job type", "Contains Id", "Start time", "Finish time", "TNRS version", "Sources selected", "Match threshold", "Classification", "Allow partial matches?", "Sort by source", "Constrain by higher taxonomy") FROM stdin;
|
1967
|
\.
|
1968
|
|
1969
|
|
1970
|
--
|
1971
|
-- Data for Name: client_version; Type: TABLE DATA; Schema: TNRS; Owner: bien
|
1972
|
--
|
1973
|
|
1974
|
COPY client_version (id, global_rev, "/lib/tnrs.py rev", "/bin/tnrs_db rev") FROM stdin;
|
1975
|
\.
|
1976
|
|
1977
|
|
1978
|
--
|
1979
|
-- Data for Name: taxon_match; Type: TABLE DATA; Schema: TNRS; Owner: bien
|
1980
|
--
|
1981
|
|
1982
|
COPY taxon_match (batch, match_num, "*Name_number", "*Name_submitted", "*Overall_score", "*Name_matched", "*Name_matched_rank", "*Name_score", "*Name_matched_author", "*Name_matched_url", "*Author_matched", "*Author_score", "*Family_matched", "*Family_score", "*Name_matched_accepted_family", "*Genus_matched", "*Genus_score", "*Specific_epithet_matched", "*Specific_epithet_score", "*Infraspecific_rank", "*Infraspecific_epithet_matched", "*Infraspecific_epithet_score", "*Infraspecific_rank_2", "*Infraspecific_epithet_2_matched", "*Infraspecific_epithet_2_score", "*Annotations", "*Unmatched_terms", "*Taxonomic_status", "*Accepted_name", "*Accepted_name_author", "*Accepted_name_rank", "*Accepted_name_url", "*Accepted_name_species", "*Accepted_name_family", "*Selected", "*Source", "*Warnings", "*Accepted_name_lsid", is_valid_match, scrubbed_unique_taxon_name, "[parsed_]morphospecies[_suffix]__@Brad__.morphosp@vegpath.org", "[matched_]species[_binomial]~(-Accepted_-)__@TNRS__@vegpath.org", "matched~Name[_no_author]___@TNRS__@vegpath.org", "[matched_]scientificName[_with_author]__@DwC__@vegpath.org", matched_has_accepted, "Accepted_family__@TNRS__@vegpath.org", "Accepted_species[_binomial]__@TNRS__@vegpath.org", "__accepted_{genus,specific_epithet}", "[accepted_]genus__@DwC__@vegpath.org", "[accepted_]specificEpithet__@DwC__@vegpath.org", "[accepted_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org", __accepted_infraspecific_label, "__accepted_infraspecific_{rank,epithet}", "[accepted_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org", "[accepted_]infraspecificEpithet__@DwC__@vegpath.org", "[accepted_]scientificName[_with_author]__@DwC__@vegpath.org", "[scrubbed_]taxonomicStatus__@DwC__@vegpath.org", "[scrubbed_]taxonRank__@DwC__@vegpath.org", "[scrubbed_]family~(-Accepted_-)__@TNRS__@vegpath.org", "[scrubbed_]genus__@DwC__@vegpath.org", "[scrubbed_]specificEpithet__@DwC__@vegpath.org", "[scrubbed_]species[_binom]~(-Accepted_-)__@TNRS__@vegpath.org", "[scrubbed_]morphospecies[_binomial]__@Brad__.TNRS@vegpath.org", "[scrubbed_]Infraspecific_rank[_abbr]__@TNRS__@vegpath.org", "[scrubbed_]infraspecificEpithet__@DwC__@vegpath.org", "[scrubbed_]name[_no_author]~(-Accepted_-)__@TNRS__@vegpath.org", "[scrubbed_]author~(-Accepted_-)__@TNRS__@vegpath.org", "[scrubbed_]scientificName[_with_author]__@DwC__@vegpath.org") FROM stdin;
|
1983
|
2014-07-26 21:26:21.824011-07 0 0 Compositae indet. sp.1 0.900000000000000022 Compositae family 1 \N http://www.theplantlist.org/1.1/browse/A/Compositae/ \N \N Compositae 1 \N \N \N \N \N \N \N \N \N \N \N \N indet. sp.1 Accepted Compositae \N family http://www.theplantlist.org/1.1/browse/A/Compositae/ \N \N true tpl [Ambiguous match] \N t Compositae indet. sp.1 \N Compositae Compositae t Compositae \N \N \N \N Compositae indet. sp.1 \N \N \N \N Compositae accepted family Compositae \N \N \N Compositae indet. sp.1 \N \N Compositae \N Compositae
|
1984
|
2014-07-26 21:26:21.824011-07 1 0 Compositae indet. sp.1 0.900000000000000022 Compositae family 1 \N \N \N \N Compositae 1 \N \N \N \N \N \N \N \N \N \N \N \N indet. sp.1 Accepted Compositae \N family \N \N \N false tpl [Ambiguous match] \N t Compositae indet. sp.1 \N Compositae Compositae t Compositae \N \N \N \N Compositae indet. sp.1 \N \N \N \N Compositae accepted family Compositae \N \N \N Compositae indet. sp.1 \N \N Compositae \N Compositae
|
1985
|
2014-07-26 21:26:21.824011-07 2 0 Compositae indet. sp.1 0.900000000000000022 Compositae family 1 Giseke http://www.tropicos.org/Name/50255940 \N \N Compositae 1 Compositae \N \N \N \N \N \N \N \N \N \N \N indet. sp.1 Synonym Asteraceae Bercht. & J. Presl family http://www.tropicos.org/Name/50307371 \N Asteraceae false tropicos \N t Asteraceae Bercht. & J. Presl indet. sp.1 \N Compositae Compositae Giseke t Asteraceae \N \N \N \N Asteraceae indet. sp.1 \N \N \N \N Asteraceae Bercht. & J. Presl accepted family Asteraceae \N \N \N Asteraceae indet. sp.1 \N \N Asteraceae Bercht. & J. Presl Asteraceae Bercht. & J. Presl
|
1986
|
2014-07-26 21:26:21.824011-07 3 1 Fabaceae Boyle#6500 0.839999999999999969 Fagaceae family 0.939999999999999947 \N ;http://plants.usda.gov/java/nameSearch \N \N Fagaceae 0.880000000000000004 \N \N \N \N \N \N \N \N \N \N \N \N Boyle#6500 Accepted Fagaceae \N family ;http://plants.usda.gov/java/nameSearch \N \N true tpl;usda \N f Fagaceae Boyle#6500 \N Fagaceae Fagaceae t Fagaceae \N \N \N \N Fagaceae Boyle#6500 \N \N \N \N Fagaceae accepted family Fagaceae \N \N \N Fagaceae Boyle#6500 \N \N Fagaceae \N Fagaceae
|
1987
|
2014-07-26 21:26:21.824011-07 4 1 Fabaceae Boyle#6500 0.900000000000000022 Fabaceae family 1 Lindl. http://www.tropicos.org/Name/42000184 \N \N Fabaceae 1 Fabaceae \N \N \N \N \N \N \N \N \N \N \N Boyle#6500 Accepted Fabaceae Lindl. family http://www.tropicos.org/Name/42000184 \N Fabaceae false tropicos \N t Fabaceae Lindl. Boyle#6500 \N Fabaceae Fabaceae Lindl. t Fabaceae \N \N \N \N Fabaceae Boyle#6500 \N \N \N \N Fabaceae Lindl. accepted family Fabaceae \N \N \N Fabaceae Boyle#6500 \N \N Fabaceae Lindl. Fabaceae Lindl.
|
1988
|
2014-07-26 21:26:21.824011-07 5 1 Fabaceae Boyle#6500 0.839999999999999969 Fagaceae family 0.939999999999999947 Dumort. http://www.tropicos.org/Name/42000062 \N \N Fagaceae 0.880000000000000004 Fagaceae \N \N \N \N \N \N \N \N \N \N \N Boyle#6500 Accepted Fagaceae Dumort. family http://www.tropicos.org/Name/42000062 \N Fagaceae false tropicos \N f Fagaceae Dumort. Boyle#6500 \N Fagaceae Fagaceae Dumort. t Fagaceae \N \N \N \N Fagaceae Boyle#6500 \N \N \N \N Fagaceae Dumort. accepted family Fagaceae \N \N \N Fagaceae Boyle#6500 \N \N Fagaceae Dumort. Fagaceae Dumort.
|
1989
|
2014-07-26 21:26:21.824011-07 6 1 Fabaceae Boyle#6500 0.67000000000000004 Ficaceae family 0.770000000000000018 Bercht. & J. Presl http://www.tropicos.org/Name/100353631 \N \N Ficaceae 0.75 Ficaceae \N \N \N \N \N \N \N \N \N \N \N Boyle#6500 No opinion \N \N \N \N \N \N false tropicos \N f Ficaceae Bercht. & J. Presl Boyle#6500 \N Ficaceae Ficaceae Bercht. & J. Presl f \N \N \N \N \N \N \N \N \N \N \N no opinion family Ficaceae \N \N \N Ficaceae Boyle#6500 \N \N Ficaceae Bercht. & J. Presl Ficaceae Bercht. & J. Presl
|
1990
|
2014-07-26 21:26:21.824011-07 7 1 Fabaceae Boyle#6500 0.67000000000000004 Fucaceae family 0.770000000000000018 \N http://www.tropicos.org/Name/100371040 \N \N Fucaceae 0.75 Fucaceae \N \N \N \N \N \N \N \N \N \N \N Boyle#6500 No opinion \N \N \N \N \N \N false tropicos \N f Fucaceae Boyle#6500 \N Fucaceae Fucaceae f \N \N \N \N \N \N \N \N \N \N \N no opinion family Fucaceae \N \N \N Fucaceae Boyle#6500 \N \N Fucaceae \N Fucaceae
|
1991
|
2014-07-26 21:26:21.824011-07 8 1 Fabaceae Boyle#6500 0.900000000000000022 Fabaceae family 1 \N http://plants.usda.gov/java/nameSearch \N \N Fabaceae 1 \N \N \N \N \N \N \N \N \N \N \N \N Boyle#6500 Accepted Fabaceae \N family http://plants.usda.gov/java/nameSearch \N \N false usda \N t Fabaceae Boyle#6500 \N Fabaceae Fabaceae t Fabaceae \N \N \N \N Fabaceae Boyle#6500 \N \N \N \N Fabaceae accepted family Fabaceae \N \N \N Fabaceae Boyle#6500 \N \N Fabaceae \N Fabaceae
|
1992
|
2014-07-26 21:26:21.824011-07 9 2 Fabaceae Inga "fuzzy leaf" 0.900000000000000022 Inga genus 1 \N http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA \N \N Fabaceae 1 Fabaceae Inga 1 \N \N \N \N \N \N \N \N \N "fuzzy leaf" Accepted Inga \N genus http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA \N Fabaceae true tpl;usda \N t Fabaceae Inga "fuzzy leaf" \N Inga Inga t Fabaceae \N \N Inga \N Inga "fuzzy leaf" \N \N \N \N Inga accepted genus Fabaceae Inga \N \N Inga "fuzzy leaf" \N \N Inga \N Inga
|
1993
|
2014-07-26 21:26:21.824011-07 10 2 Fabaceae Inga "fuzzy leaf" 0.900000000000000022 Inga genus 1 Mill. http://www.tropicos.org/Name/40031040 \N \N Fabaceae 1 Fabaceae Inga 1 \N \N \N \N \N \N \N \N \N "fuzzy leaf" Accepted Inga Mill. genus http://www.tropicos.org/Name/40031040 \N Fabaceae false tropicos \N t Fabaceae Inga Mill. "fuzzy leaf" \N Inga Inga Mill. t Fabaceae \N \N Inga \N Inga "fuzzy leaf" \N \N \N \N Inga Mill. accepted genus Fabaceae Inga \N \N Inga "fuzzy leaf" \N \N Inga Mill. Inga Mill.
|
1994
|
2014-07-26 21:26:21.824011-07 11 2 Fabaceae Inga "fuzzy leaf" 0.900000000000000022 Inga genus 1 Scop. http://www.tropicos.org/Name/50215121 \N \N Fabaceae 1 Fabaceae Inga 1 \N \N \N \N \N \N \N \N \N "fuzzy leaf" Illegitimate \N \N \N \N \N \N false tropicos \N t Fabaceae Inga Scop. "fuzzy leaf" \N Inga Inga Scop. f \N \N \N \N \N \N \N \N \N \N \N illegitimate genus Fabaceae Inga \N \N Inga "fuzzy leaf" \N \N Inga Scop. Inga Scop.
|
1995
|
2014-07-26 21:26:21.824011-07 12 3 Fabaceae Inga sp.3 0.900000000000000022 Inga genus 1 \N http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA \N \N Fabaceae 1 Fabaceae Inga 1 \N \N \N \N \N \N \N \N \N sp.3 Accepted Inga \N genus http://www.theplantlist.org/1.1/browse/A/Leguminosae/Inga/;http://plants.usda.gov/java/profile?symbol=INGA \N Fabaceae true tpl;usda \N t Fabaceae Inga sp.3 \N Inga Inga t Fabaceae \N \N Inga \N Inga sp.3 \N \N \N \N Inga accepted genus Fabaceae Inga \N \N Inga sp.3 \N \N Inga \N Inga
|
1996
|
2014-07-26 21:26:21.824011-07 13 3 Fabaceae Inga sp.3 0.900000000000000022 Inga genus 1 Mill. http://www.tropicos.org/Name/40031040 \N \N Fabaceae 1 Fabaceae Inga 1 \N \N \N \N \N \N \N \N \N sp.3 Accepted Inga Mill. genus http://www.tropicos.org/Name/40031040 \N Fabaceae false tropicos \N t Fabaceae Inga Mill. sp.3 \N Inga Inga Mill. t Fabaceae \N \N Inga \N Inga sp.3 \N \N \N \N Inga Mill. accepted genus Fabaceae Inga \N \N Inga sp.3 \N \N Inga Mill. Inga Mill.
|
1997
|
2014-07-26 21:26:21.824011-07 14 3 Fabaceae Inga sp.3 0.900000000000000022 Inga genus 1 Scop. http://www.tropicos.org/Name/50215121 \N \N Fabaceae 1 Fabaceae Inga 1 \N \N \N \N \N \N \N \N \N sp.3 Illegitimate \N \N \N \N \N \N false tropicos \N t Fabaceae Inga Scop. sp.3 \N Inga Inga Scop. f \N \N \N \N \N \N \N \N \N \N \N illegitimate genus Fabaceae Inga \N \N Inga sp.3 \N \N Inga Scop. Inga Scop.
|
1998
|
2014-07-26 21:26:21.824011-07 15 4 Fabaceae unknown #2 0.839999999999999969 Fagaceae family 0.939999999999999947 \N ;http://plants.usda.gov/java/nameSearch \N \N Fagaceae 0.880000000000000004 \N \N \N \N \N \N \N \N \N \N \N \N unknown #2 Accepted Fagaceae \N family ;http://plants.usda.gov/java/nameSearch \N \N true tpl;usda \N f Fagaceae unknown #2 \N Fagaceae Fagaceae t Fagaceae \N \N \N \N Fagaceae unknown #2 \N \N \N \N Fagaceae accepted family Fagaceae \N \N \N Fagaceae unknown #2 \N \N Fagaceae \N Fagaceae
|
1999
|
2014-07-26 21:26:21.824011-07 16 4 Fabaceae unknown #2 0.900000000000000022 Fabaceae family 1 Lindl. http://www.tropicos.org/Name/42000184 \N \N Fabaceae 1 Fabaceae \N \N \N \N \N \N \N \N \N \N \N unknown #2 Accepted Fabaceae Lindl. family http://www.tropicos.org/Name/42000184 \N Fabaceae false tropicos \N t Fabaceae Lindl. unknown #2 \N Fabaceae Fabaceae Lindl. t Fabaceae \N \N \N \N Fabaceae unknown #2 \N \N \N \N Fabaceae Lindl. accepted family Fabaceae \N \N \N Fabaceae unknown #2 \N \N Fabaceae Lindl. Fabaceae Lindl.
|
2000
|
2014-07-26 21:26:21.824011-07 17 4 Fabaceae unknown #2 0.839999999999999969 Fagaceae family 0.939999999999999947 Dumort. http://www.tropicos.org/Name/42000062 \N \N Fagaceae 0.880000000000000004 Fagaceae \N \N \N \N \N \N \N \N \N \N \N unknown #2 Accepted Fagaceae Dumort. family http://www.tropicos.org/Name/42000062 \N Fagaceae false tropicos \N f Fagaceae Dumort. unknown #2 \N Fagaceae Fagaceae Dumort. t Fagaceae \N \N \N \N Fagaceae unknown #2 \N \N \N \N Fagaceae Dumort. accepted family Fagaceae \N \N \N Fagaceae unknown #2 \N \N Fagaceae Dumort. Fagaceae Dumort.
|
2001
|
2014-07-26 21:26:21.824011-07 18 4 Fabaceae unknown #2 0.67000000000000004 Ficaceae family 0.770000000000000018 Bercht. & J. Presl http://www.tropicos.org/Name/100353631 \N \N Ficaceae 0.75 Ficaceae \N \N \N \N \N \N \N \N \N \N \N unknown #2 No opinion \N \N \N \N \N \N false tropicos \N f Ficaceae Bercht. & J. Presl unknown #2 \N Ficaceae Ficaceae Bercht. & J. Presl f \N \N \N \N \N \N \N \N \N \N \N no opinion family Ficaceae \N \N \N Ficaceae unknown #2 \N \N Ficaceae Bercht. & J. Presl Ficaceae Bercht. & J. Presl
|
2002
|
2014-07-26 21:26:21.824011-07 19 4 Fabaceae unknown #2 0.67000000000000004 Fucaceae family 0.770000000000000018 \N http://www.tropicos.org/Name/100371040 \N \N Fucaceae 0.75 Fucaceae \N \N \N \N \N \N \N \N \N \N \N unknown #2 No opinion \N \N \N \N \N \N false tropicos \N f Fucaceae unknown #2 \N Fucaceae Fucaceae f \N \N \N \N \N \N \N \N \N \N \N no opinion family Fucaceae \N \N \N Fucaceae unknown #2 \N \N Fucaceae \N Fucaceae
|
2003
|
2014-07-26 21:26:21.824011-07 20 4 Fabaceae unknown #2 0.900000000000000022 Fabaceae family 1 \N http://plants.usda.gov/java/nameSearch \N \N Fabaceae 1 \N \N \N \N \N \N \N \N \N \N \N \N unknown #2 Accepted Fabaceae \N family http://plants.usda.gov/java/nameSearch \N \N false usda \N t Fabaceae unknown #2 \N Fabaceae Fabaceae t Fabaceae \N \N \N \N Fabaceae unknown #2 \N \N \N \N Fabaceae accepted family Fabaceae \N \N \N Fabaceae unknown #2 \N \N Fabaceae \N Fabaceae
|
2004
|
2014-07-26 21:26:21.824011-07 21 5 Fam_indet. Boyle#6501 0 No suitable matches found. \N 0 \N \N \N 0 \N 0 \N \N 0 \N 0 \N \N 0 \N \N 0 \N \N \N \N \N \N \N \N \N true \N \N f \N \N \N \N \N f \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N \N
|
2005
|
2014-07-26 21:26:21.824011-07 22 6 Poa annua 1 Poa annua species 1 L. http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN \N \N \N \N Poaceae Poa 1 annua 1 \N \N \N \N \N \N \N \N Accepted Poa annua L. species http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN Poa annua Poaceae true tpl;tropicos;usda \N t Poaceae Poa annua L. \N Poa annua Poa annua Poa annua L. t Poaceae Poa annua {Poa,annua} Poa annua Poa annua \N \N \N \N Poa annua L. accepted species Poaceae Poa annua Poa annua Poa annua \N annua Poa annua L. Poa annua L.
|
2006
|
2014-07-26 21:26:21.824011-07 23 6 Poa annua 1 Poa annua species 1 Cham. & Schltdl. http://www.theplantlist.org/tpl1.1/record/kew-435195 \N \N \N \N Poaceae Poa 1 annua 1 \N \N \N \N \N \N \N \N Synonym Poa infirma Kunth species http://www.theplantlist.org/tpl1.1/record/kew-436189 Poa infirma Poaceae false tpl \N t Poaceae Poa infirma Kunth \N Poa annua Poa annua Poa annua Cham. & Schltdl. t Poaceae Poa infirma {Poa,infirma} Poa infirma Poa infirma \N \N \N \N Poa infirma Kunth accepted species Poaceae Poa infirma Poa infirma Poa infirma \N infirma Poa infirma Kunth Poa infirma Kunth
|
2007
|
2014-07-26 21:26:21.824011-07 24 7 Poa annua L. 1 Poa annua species 1 L. http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN L. 1 \N \N Poaceae Poa 1 annua 1 \N \N \N \N \N \N \N \N Accepted Poa annua L. species http://www.theplantlist.org/tpl1.1/record/kew-435194;http://www.tropicos.org/Name/25509881;http://plants.usda.gov/java/profile?symbol=POAN Poa annua Poaceae true tpl;tropicos;usda \N t Poaceae Poa annua L. \N Poa annua Poa annua Poa annua L. t Poaceae Poa annua {Poa,annua} Poa annua Poa annua \N \N \N \N Poa annua L. accepted species Poaceae Poa annua Poa annua Poa annua \N annua Poa annua L. Poa annua L.
|
2008
|
2014-07-26 21:26:21.824011-07 25 7 Poa annua L. 0.800000000000000044 Poa annua species 1 Cham. & Schltdl. http://www.theplantlist.org/tpl1.1/record/kew-435195 Cham. & Schltdl. 0 \N \N Poaceae Poa 1 annua 1 \N \N \N \N \N \N \N \N Synonym Poa infirma Kunth species http://www.theplantlist.org/tpl1.1/record/kew-436189 Poa infirma Poaceae false tpl \N t Poaceae Poa infirma Kunth \N Poa annua Poa annua Poa annua Cham. & Schltdl. t Poaceae Poa infirma {Poa,infirma} Poa infirma Poa infirma \N \N \N \N Poa infirma Kunth accepted species Poaceae Poa infirma Poa infirma Poa infirma \N infirma Poa infirma Kunth Poa infirma Kunth
|
2009
|
2014-07-26 21:26:21.824011-07 26 8 Poa annua fo. lanuginosa 1 Poa annua fo. lanuginosa fo. 1 Sennen http://www.theplantlist.org/tpl1.1/record/tro-50267771 \N \N \N \N \N Poa 1 annua 1 fo. lanuginosa 1 \N \N \N \N \N Synonym Poa annua L. species http://www.theplantlist.org/tpl1.1/record/kew-435194 Poa annua Poaceae true tpl \N t Poaceae Poa annua L. \N Poa annua Poa annua fo. lanuginosa Poa annua fo. lanuginosa Sennen t Poaceae Poa annua {Poa,annua} Poa annua Poa annua \N \N \N \N Poa annua L. accepted species Poaceae Poa annua Poa annua Poa annua \N annua Poa annua L. Poa annua L.
|
2010
|
2014-07-26 21:26:21.824011-07 27 8 Poa annua fo. lanuginosa 1 Poa annua fo. lanuginosa forma 1 Sennen http://www.tropicos.org/Name/50267771 \N \N \N \N Poaceae Poa 1 annua 1 fo. lanuginosa 1 \N \N \N \N \N Synonym Poa annua var. annua \N variety http://www.tropicos.org/Name/25517736 Poa annua Poaceae false tropicos \N t Poaceae Poa annua var. annua \N Poa annua Poa annua fo. lanuginosa Poa annua fo. lanuginosa Sennen t Poaceae Poa annua {Poa,annua} Poa annua Poa annua var. annua {var.,annua} var. annua Poa annua var. annua accepted variety Poaceae Poa annua Poa annua Poa annua var. annua Poa annua var. annua \N Poa annua var. annua
|
2011
|
2014-07-26 21:26:21.824011-07 28 9 Poa annua ssp. exilis 1 Poa annua subsp. exilis subspecies 1 (Tomm. ex Freyn) Asch. & Graebn. http://www.tropicos.org/Name/50063800 \N \N \N \N Poaceae Poa 1 annua 1 subsp. exilis 1 \N \N \N \N \N Synonym Poa infirma Kunth species http://www.tropicos.org/Name/25514158 Poa infirma Poaceae true tropicos \N t Poaceae Poa infirma Kunth \N Poa annua Poa annua subsp. exilis Poa annua subsp. exilis (Tomm. ex Freyn) Asch. & Graebn. t Poaceae Poa infirma {Poa,infirma} Poa infirma Poa infirma \N \N \N \N Poa infirma Kunth accepted species Poaceae Poa infirma Poa infirma Poa infirma \N infirma Poa infirma Kunth Poa infirma Kunth
|
2012
|
2014-07-26 21:26:21.824011-07 29 9 Poa annua ssp. exilis 0.959999999999999964 Poa annua var. exilis variety 0.959999999999999964 Tomm. ex Freyn http://www.tropicos.org/Name/25547854 \N \N \N \N Poaceae Poa 1 annua 1 var. exilis 0.699999999999999956 \N \N \N \N \N Synonym Poa infirma Kunth species http://www.tropicos.org/Name/25514158 Poa infirma Poaceae false tropicos \N t Poaceae Poa infirma Kunth \N Poa annua Poa annua var. exilis Poa annua var. exilis Tomm. ex Freyn t Poaceae Poa infirma {Poa,infirma} Poa infirma Poa infirma \N \N \N \N Poa infirma Kunth accepted species Poaceae Poa infirma Poa infirma Poa infirma \N infirma Poa infirma Kunth Poa infirma Kunth
|
2013
|
2014-07-26 21:26:21.824011-07 30 10 Poa annua subsp. exilis 1 Poa annua subsp. exilis subsp. 1 (Tomm. ex Freyn.) Asch. & Graebn. http://www.theplantlist.org/tpl1.1/record/kew-435202 \N \N \N \N \N Poa 1 annua 1 subsp. exilis 1 \N \N \N \N \N Synonym Poa infirma Kunth species http://www.theplantlist.org/tpl1.1/record/kew-436189 Poa infirma Poaceae true tpl \N t Poaceae Poa infirma Kunth \N Poa annua Poa annua subsp. exilis Poa annua subsp. exilis (Tomm. ex Freyn.) Asch. & Graebn. t Poaceae Poa infirma {Poa,infirma} Poa infirma Poa infirma \N \N \N \N Poa infirma Kunth accepted species Poaceae Poa infirma Poa infirma Poa infirma \N infirma Poa infirma Kunth Poa infirma Kunth
|
2014
|
2014-07-26 21:26:21.824011-07 31 10 Poa annua subsp. exilis 1 Poa annua subsp. exilis subspecies 1 (Tomm. ex Freyn) Asch. & Graebn. http://www.tropicos.org/Name/50063800 \N \N \N \N Poaceae Poa 1 annua 1 subsp. exilis 1 \N \N \N \N \N Synonym Poa infirma Kunth species http://www.tropicos.org/Name/25514158 Poa infirma Poaceae false tropicos \N t Poaceae Poa infirma Kunth \N Poa annua Poa annua subsp. exilis Poa annua subsp. exilis (Tomm. ex Freyn) Asch. & Graebn. t Poaceae Poa infirma {Poa,infirma} Poa infirma Poa infirma \N \N \N \N Poa infirma Kunth accepted species Poaceae Poa infirma Poa infirma Poa infirma \N infirma Poa infirma Kunth Poa infirma Kunth
|
2015
|
2014-07-26 21:26:21.824011-07 32 11 Poa annua subvar. minima 1 Poa annua subvar. minima subvariety 1 (Schur) Asch. & Graebn. http://www.tropicos.org/Name/50158097 \N \N \N \N Poaceae Poa 1 annua 1 subvar. minima 1 \N \N \N \N \N Accepted Poa annua subvar. minima (Schur) Asch. & Graebn. subvariety http://www.tropicos.org/Name/50158097 Poa annua Poaceae true tropicos \N t Poaceae Poa annua subvar. minima (Schur) Asch. & Graebn. \N Poa annua Poa annua subvar. minima Poa annua subvar. minima (Schur) Asch. & Graebn. t Poaceae Poa annua {Poa,annua} Poa annua Poa annua subvar. minima {subvar.,minima} subvar. minima Poa annua subvar. minima (Schur) Asch. & Graebn. accepted subvariety Poaceae Poa annua Poa annua Poa annua subvar. annua Poa annua subvar. minima (Schur) Asch. & Graebn. Poa annua subvar. minima (Schur) Asch. & Graebn.
|
2016
|
2014-07-26 21:26:21.824011-07 33 12 Poa annua var. eriolepis 1 Poa annua var. eriolepis var. 1 ̉ۡ.Desv. http://www.theplantlist.org/tpl1.1/record/kew-435206 \N \N \N \N \N Poa 1 annua 1 var. eriolepis 1 \N \N \N \N \N Synonym Poa annua L. species http://www.theplantlist.org/tpl1.1/record/kew-435194 Poa annua Poaceae true tpl \N t Poaceae Poa annua L. \N Poa annua Poa annua var. eriolepis Poa annua var. eriolepis ̉ۡ.Desv. t Poaceae Poa annua {Poa,annua} Poa annua Poa annua \N \N \N \N Poa annua L. accepted species Poaceae Poa annua Poa annua Poa annua \N annua Poa annua L. Poa annua L.
|
2017
|
2014-07-26 21:26:21.824011-07 34 12 Poa annua var. eriolepis 1 Poa annua var. eriolepis variety 1 E. Desv. http://www.tropicos.org/Name/50119145 \N \N \N \N Poaceae Poa 1 annua 1 var. eriolepis 1 \N \N \N \N \N Synonym Poa annua L. species http://www.tropicos.org/Name/25509881 Poa annua Poaceae false tropicos \N t Poaceae Poa annua L. \N Poa annua Poa annua var. eriolepis Poa annua var. eriolepis E. Desv. t Poaceae Poa annua {Poa,annua} Poa annua Poa annua \N \N \N \N Poa annua L. accepted species Poaceae Poa annua Poa annua Poa annua \N annua Poa annua L. Poa annua L.
|
2018
|
2014-07-26 21:26:21.824011-07 35 13 Silene scouleri Hook. subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea C.L. Hitchc. & Maguire 0.770000000000000018 Silene scouleri subsp. pringlei subsp. 0.770000000000000018 (S. Watson) C.L. Hitchc. & Maguire http://www.theplantlist.org/tpl1.1/record/tro-6303627 \N \N \N \N Caryophyllaceae Silene 1 scouleri 1 subsp. pringlei 1 \N \N \N \N var. grisea Accepted Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire subsp. http://www.theplantlist.org/tpl1.1/record/tro-6303627 Silene scouleri Caryophyllaceae true tpl [Partial match] \N t Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea Silene scouleri Silene scouleri subsp. pringlei Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire t Caryophyllaceae Silene scouleri {Silene,scouleri} Silene scouleri Silene scouleri subsp. pringlei {subsp.,pringlei} subsp. pringlei Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire accepted subsp. Caryophyllaceae Silene scouleri Silene scouleri Silene scouleri subsp. scouleri Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire
|
2019
|
2014-07-26 21:26:21.824011-07 36 13 Silene scouleri Hook. subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea C.L. Hitchc. & Maguire 0.770000000000000018 Silene scouleri subsp. pringlei subspecies 0.770000000000000018 (S. Watson) C.L. Hitchc. & Maguire http://www.tropicos.org/Name/6303627 \N \N \N \N Caryophyllaceae Silene 1 scouleri 1 subsp. pringlei 1 \N \N \N \N var. grisea Accepted Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire subspecies http://www.tropicos.org/Name/6303627 Silene scouleri Caryophyllaceae false tropicos [Partial match] \N t Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea Silene scouleri Silene scouleri subsp. pringlei Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire t Caryophyllaceae Silene scouleri {Silene,scouleri} Silene scouleri Silene scouleri subsp. pringlei {subsp.,pringlei} subsp. pringlei Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire accepted subspecies Caryophyllaceae Silene scouleri Silene scouleri Silene scouleri subsp. scouleri Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire
|
2020
|
2014-07-26 21:26:21.824011-07 37 13 Silene scouleri Hook. subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea C.L. Hitchc. & Maguire 0.770000000000000018 Silene scouleri subsp. pringlei subspecies 0.770000000000000018 (S. Watson) C.L. Hitchc. & Maguire http://plants.usda.gov/java/profile?symbol=SISCP \N \N \N \N Caryophyllaceae Silene 1 scouleri 1 subsp. pringlei 1 \N \N \N \N var. grisea Accepted Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire subspecies http://plants.usda.gov/java/profile?symbol=SISCP Silene scouleri Caryophyllaceae false usda [Partial match] \N t Caryophyllaceae Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea Silene scouleri Silene scouleri subsp. pringlei Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire t Caryophyllaceae Silene scouleri {Silene,scouleri} Silene scouleri Silene scouleri ssp. pringlei {ssp.,pringlei} ssp. pringlei Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire accepted subspecies Caryophyllaceae Silene scouleri Silene scouleri Silene scouleri ssp. scouleri Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire
|
2021
|
2014-07-26 21:26:21.824011-07 38 14 Silene scouleri subsp. pringlei var. grisea C.L. Hitchc. & Maguire 0.770000000000000018 Silene scouleri subsp. pringlei subsp. 0.770000000000000018 (S. Watson) C.L. Hitchc. & Maguire http://www.theplantlist.org/tpl1.1/record/tro-6303627 \N \N \N \N Caryophyllaceae Silene 1 scouleri 1 subsp. pringlei 1 \N \N \N \N var. grisea Accepted Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire subsp. http://www.theplantlist.org/tpl1.1/record/tro-6303627 Silene scouleri Caryophyllaceae true tpl [Partial match] \N t Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea Silene scouleri Silene scouleri subsp. pringlei Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire t Caryophyllaceae Silene scouleri {Silene,scouleri} Silene scouleri Silene scouleri subsp. pringlei {subsp.,pringlei} subsp. pringlei Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire accepted subsp. Caryophyllaceae Silene scouleri Silene scouleri Silene scouleri subsp. scouleri Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire
|
2022
|
2014-07-26 21:26:21.824011-07 39 14 Silene scouleri subsp. pringlei var. grisea C.L. Hitchc. & Maguire 0.770000000000000018 Silene scouleri subsp. pringlei subspecies 0.770000000000000018 (S. Watson) C.L. Hitchc. & Maguire http://www.tropicos.org/Name/6303627 \N \N \N \N Caryophyllaceae Silene 1 scouleri 1 subsp. pringlei 1 \N \N \N \N var. grisea Accepted Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire subspecies http://www.tropicos.org/Name/6303627 Silene scouleri Caryophyllaceae false tropicos [Partial match] \N t Caryophyllaceae Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea Silene scouleri Silene scouleri subsp. pringlei Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire t Caryophyllaceae Silene scouleri {Silene,scouleri} Silene scouleri Silene scouleri subsp. pringlei {subsp.,pringlei} subsp. pringlei Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire accepted subspecies Caryophyllaceae Silene scouleri Silene scouleri Silene scouleri subsp. scouleri Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire
|
2023
|
2014-07-26 21:26:21.824011-07 40 14 Silene scouleri subsp. pringlei var. grisea C.L. Hitchc. & Maguire 0.770000000000000018 Silene scouleri subsp. pringlei subspecies 0.770000000000000018 (S. Watson) C.L. Hitchc. & Maguire http://plants.usda.gov/java/profile?symbol=SISCP \N \N \N \N Caryophyllaceae Silene 1 scouleri 1 subsp. pringlei 1 \N \N \N \N var. grisea Accepted Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire subspecies http://plants.usda.gov/java/profile?symbol=SISCP Silene scouleri Caryophyllaceae false usda [Partial match] \N t Caryophyllaceae Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire var. grisea Silene scouleri Silene scouleri subsp. pringlei Silene scouleri subsp. pringlei (S. Watson) C.L. Hitchc. & Maguire t Caryophyllaceae Silene scouleri {Silene,scouleri} Silene scouleri Silene scouleri ssp. pringlei {ssp.,pringlei} ssp. pringlei Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire accepted subspecies Caryophyllaceae Silene scouleri Silene scouleri Silene scouleri ssp. scouleri Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire Silene scouleri ssp. pringlei (S. Watson) C.L. Hitchc. & Maguire
|
2024
|
\.
|
2025
|
|
2026
|
|
2027
|
--
|
2028
|
-- Data for Name: taxon_match_input__copy_to; Type: TABLE DATA; Schema: TNRS; Owner: bien
|
2029
|
--
|
2030
|
|
2031
|
COPY taxon_match_input__copy_to ("Name_number", "Name_submitted", "Overall_score", "Name_matched", "Name_matched_rank", "Name_score", "Name_matched_author", "Name_matched_url", "Author_matched", "Author_score", "Family_matched", "Family_score", "Name_matched_accepted_family", "Genus_matched", "Genus_score", "Specific_epithet_matched", "Specific_epithet_score", "Infraspecific_rank", "Infraspecific_epithet_matched", "Infraspecific_epithet_score", "Infraspecific_rank_2", "Infraspecific_epithet_2_matched", "Infraspecific_epithet_2_score", "Annotations", "Unmatched_terms", "Taxonomic_status", "Accepted_name", "Accepted_name_author", "Accepted_name_rank", "Accepted_name_url", "Accepted_name_species", "Accepted_name_family", "Selected", "Source", "Warnings", "Accepted_name_lsid") FROM stdin;
|
2032
|
\.
|
2033
|
|
2034
|
|
2035
|
--
|
2036
|
-- Data for Name: ~Source.map; Type: TABLE DATA; Schema: TNRS; Owner: bien
|
2037
|
--
|
2038
|
|
2039
|
COPY "~Source.map" ("from", "to", filter, notes) FROM stdin;
|
2040
|
row_num *row_num \N \N
|
2041
|
:aggregator sourceType \N \N
|
2042
|
:http://[TNRS_dev_server].iplantcollaborative.org/TNRSapp.html datasetURL \N \N
|
2043
|
\.
|
2044
|
|
2045
|
|
2046
|
--
|
2047
|
-- Name: Source_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace:
|
2048
|
--
|
2049
|
|
2050
|
ALTER TABLE ONLY "Source"
|
2051
|
ADD CONSTRAINT "Source_pkey" PRIMARY KEY ("*row_num");
|
2052
|
|
2053
|
|
2054
|
--
|
2055
|
-- Name: batch_download_settings_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace:
|
2056
|
--
|
2057
|
|
2058
|
ALTER TABLE ONLY batch_download_settings
|
2059
|
ADD CONSTRAINT batch_download_settings_pkey PRIMARY KEY (id);
|
2060
|
|
2061
|
|
2062
|
--
|
2063
|
-- Name: batch_id_by_time_key; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace:
|
2064
|
--
|
2065
|
|
2066
|
ALTER TABLE ONLY batch
|
2067
|
ADD CONSTRAINT batch_id_by_time_key UNIQUE (id_by_time);
|
2068
|
|
2069
|
|
2070
|
--
|
2071
|
-- Name: batch_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace:
|
2072
|
--
|
2073
|
|
2074
|
ALTER TABLE ONLY batch
|
2075
|
ADD CONSTRAINT batch_pkey PRIMARY KEY (id);
|
2076
|
|
2077
|
|
2078
|
--
|
2079
|
-- Name: client_version_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace:
|
2080
|
--
|
2081
|
|
2082
|
ALTER TABLE ONLY client_version
|
2083
|
ADD CONSTRAINT client_version_pkey PRIMARY KEY (id);
|
2084
|
|
2085
|
|
2086
|
--
|
2087
|
-- Name: taxon_match_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace:
|
2088
|
--
|
2089
|
|
2090
|
ALTER TABLE ONLY taxon_match
|
2091
|
ADD CONSTRAINT taxon_match_pkey PRIMARY KEY (batch, match_num);
|
2092
|
|
2093
|
ALTER TABLE taxon_match CLUSTER ON taxon_match_pkey;
|
2094
|
|
2095
|
|
2096
|
--
|
2097
|
-- Name: ~Source.map_from_key; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace:
|
2098
|
--
|
2099
|
|
2100
|
ALTER TABLE ONLY "~Source.map"
|
2101
|
ADD CONSTRAINT "~Source.map_from_key" UNIQUE ("from");
|
2102
|
|
2103
|
|
2104
|
--
|
2105
|
-- Name: ~Source.map_to_key; Type: CONSTRAINT; Schema: TNRS; Owner: bien; Tablespace:
|
2106
|
--
|
2107
|
|
2108
|
ALTER TABLE ONLY "~Source.map"
|
2109
|
ADD CONSTRAINT "~Source.map_to_key" UNIQUE ("to");
|
2110
|
|
2111
|
|
2112
|
--
|
2113
|
-- Name: batch_client_version_idx; Type: INDEX; Schema: TNRS; Owner: bien; Tablespace:
|
2114
|
--
|
2115
|
|
2116
|
CREATE INDEX batch_client_version_idx ON batch USING btree (client_version);
|
2117
|
|
2118
|
|
2119
|
--
|
2120
|
-- Name: taxon_best_match__valid_match; Type: INDEX; Schema: TNRS; Owner: bien; Tablespace:
|
2121
|
--
|
2122
|
|
2123
|
CREATE INDEX taxon_best_match__valid_match ON taxon_match USING btree ("*Name_submitted") WHERE (("*Selected" = 'true'::text) AND is_valid_match);
|
2124
|
|
2125
|
|
2126
|
--
|
2127
|
-- Name: taxon_match_Name_submitted_idx; Type: INDEX; Schema: TNRS; Owner: bien; Tablespace:
|
2128
|
--
|
2129
|
|
2130
|
CREATE INDEX "taxon_match_Name_submitted_idx" ON taxon_match USING btree ("*Name_submitted");
|
2131
|
|
2132
|
|
2133
|
--
|
2134
|
-- Name: taxon_match__one_selected_match; Type: INDEX; Schema: TNRS; Owner: bien; Tablespace:
|
2135
|
--
|
2136
|
|
2137
|
CREATE UNIQUE INDEX taxon_match__one_selected_match ON taxon_match USING btree (batch, "*Name_number") WHERE ("*Selected" = 'true'::text);
|
2138
|
|
2139
|
|
2140
|
--
|
2141
|
-- Name: batch__fill; Type: TRIGGER; Schema: TNRS; Owner: bien
|
2142
|
--
|
2143
|
|
2144
|
CREATE TRIGGER batch__fill BEFORE INSERT OR UPDATE ON batch FOR EACH ROW EXECUTE PROCEDURE batch__fill();
|
2145
|
|
2146
|
|
2147
|
--
|
2148
|
-- Name: map_filter_insert; Type: TRIGGER; Schema: TNRS; Owner: bien
|
2149
|
--
|
2150
|
|
2151
|
CREATE TRIGGER map_filter_insert BEFORE INSERT ON "~Source.map" FOR EACH ROW EXECUTE PROCEDURE util.map_filter_insert();
|
2152
|
|
2153
|
ALTER TABLE "~Source.map" DISABLE TRIGGER map_filter_insert;
|
2154
|
|
2155
|
|
2156
|
--
|
2157
|
-- Name: taxon_match__batch_begin; Type: TRIGGER; Schema: TNRS; Owner: bien
|
2158
|
--
|
2159
|
|
2160
|
CREATE TRIGGER taxon_match__batch_begin BEFORE INSERT ON taxon_match_input__copy_to FOR EACH STATEMENT EXECUTE PROCEDURE taxon_match__batch_begin();
|
2161
|
|
2162
|
|
2163
|
--
|
2164
|
-- Name: taxon_match__fill; Type: TRIGGER; Schema: TNRS; Owner: bien
|
2165
|
--
|
2166
|
|
2167
|
CREATE TRIGGER taxon_match__fill BEFORE INSERT OR UPDATE ON taxon_match FOR EACH ROW EXECUTE PROCEDURE taxon_match__fill();
|
2168
|
|
2169
|
|
2170
|
--
|
2171
|
-- Name: taxon_match__fill_derived; Type: TRIGGER; Schema: TNRS; Owner: bien
|
2172
|
--
|
2173
|
|
2174
|
CREATE TRIGGER taxon_match__fill_derived BEFORE INSERT OR UPDATE ON taxon_match FOR EACH ROW EXECUTE PROCEDURE taxon_match__fill_derived();
|
2175
|
|
2176
|
|
2177
|
--
|
2178
|
-- Name: taxon_match__match_num__fill; Type: TRIGGER; Schema: TNRS; Owner: bien
|
2179
|
--
|
2180
|
|
2181
|
CREATE TRIGGER taxon_match__match_num__fill BEFORE INSERT ON taxon_match FOR EACH ROW EXECUTE PROCEDURE taxon_match__match_num__fill();
|
2182
|
|
2183
|
|
2184
|
--
|
2185
|
-- Name: taxon_match_input__copy_to__insert; Type: TRIGGER; Schema: TNRS; Owner: bien
|
2186
|
--
|
2187
|
|
2188
|
CREATE TRIGGER taxon_match_input__copy_to__insert BEFORE INSERT ON taxon_match_input__copy_to FOR EACH ROW EXECUTE PROCEDURE taxon_match_input__copy_to__insert();
|
2189
|
|
2190
|
|
2191
|
--
|
2192
|
-- Name: batch_client_version_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: bien
|
2193
|
--
|
2194
|
|
2195
|
ALTER TABLE ONLY batch
|
2196
|
ADD CONSTRAINT batch_client_version_fkey FOREIGN KEY (client_version) REFERENCES client_version(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
2197
|
|
2198
|
|
2199
|
--
|
2200
|
-- Name: batch_download_settings_id_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: bien
|
2201
|
--
|
2202
|
|
2203
|
ALTER TABLE ONLY batch_download_settings
|
2204
|
ADD CONSTRAINT batch_download_settings_id_fkey FOREIGN KEY (id) REFERENCES batch(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
2205
|
|
2206
|
|
2207
|
--
|
2208
|
-- Name: taxon_match_batch_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: bien
|
2209
|
--
|
2210
|
|
2211
|
ALTER TABLE ONLY taxon_match
|
2212
|
ADD CONSTRAINT taxon_match_batch_fkey FOREIGN KEY (batch) REFERENCES batch(id) ON UPDATE CASCADE ON DELETE CASCADE;
|
2213
|
|
2214
|
|
2215
|
--
|
2216
|
-- Name: TNRS; Type: ACL; Schema: -; Owner: bien
|
2217
|
--
|
2218
|
|
2219
|
REVOKE ALL ON SCHEMA "TNRS" FROM PUBLIC;
|
2220
|
REVOKE ALL ON SCHEMA "TNRS" FROM bien;
|
2221
|
GRANT ALL ON SCHEMA "TNRS" TO bien;
|
2222
|
GRANT USAGE ON SCHEMA "TNRS" TO bien_read;
|
2223
|
|
2224
|
|
2225
|
--
|
2226
|
-- Name: taxon_match; Type: ACL; Schema: TNRS; Owner: bien
|
2227
|
--
|
2228
|
|
2229
|
REVOKE ALL ON TABLE taxon_match FROM PUBLIC;
|
2230
|
REVOKE ALL ON TABLE taxon_match FROM bien;
|
2231
|
GRANT ALL ON TABLE taxon_match TO bien;
|
2232
|
GRANT SELECT ON TABLE taxon_match TO bien_read;
|
2233
|
|
2234
|
|
2235
|
--
|
2236
|
-- Name: taxon_best_match; Type: ACL; Schema: TNRS; Owner: bien
|
2237
|
--
|
2238
|
|
2239
|
REVOKE ALL ON TABLE taxon_best_match FROM PUBLIC;
|
2240
|
REVOKE ALL ON TABLE taxon_best_match FROM bien;
|
2241
|
GRANT ALL ON TABLE taxon_best_match TO bien;
|
2242
|
GRANT SELECT ON TABLE taxon_best_match TO bien_read;
|
2243
|
|
2244
|
|
2245
|
--
|
2246
|
-- Name: MatchedTaxon; Type: ACL; Schema: TNRS; Owner: bien
|
2247
|
--
|
2248
|
|
2249
|
REVOKE ALL ON TABLE "MatchedTaxon" FROM PUBLIC;
|
2250
|
REVOKE ALL ON TABLE "MatchedTaxon" FROM bien;
|
2251
|
GRANT ALL ON TABLE "MatchedTaxon" TO bien;
|
2252
|
GRANT SELECT ON TABLE "MatchedTaxon" TO bien_read;
|
2253
|
|
2254
|
|
2255
|
--
|
2256
|
-- Name: ValidMatchedTaxon; Type: ACL; Schema: TNRS; Owner: bien
|
2257
|
--
|
2258
|
|
2259
|
REVOKE ALL ON TABLE "ValidMatchedTaxon" FROM PUBLIC;
|
2260
|
REVOKE ALL ON TABLE "ValidMatchedTaxon" FROM bien;
|
2261
|
GRANT ALL ON TABLE "ValidMatchedTaxon" TO bien;
|
2262
|
GRANT SELECT ON TABLE "ValidMatchedTaxon" TO bien_read;
|
2263
|
|
2264
|
|
2265
|
--
|
2266
|
-- Name: taxon_match_input; Type: ACL; Schema: TNRS; Owner: bien
|
2267
|
--
|
2268
|
|
2269
|
REVOKE ALL ON TABLE taxon_match_input FROM PUBLIC;
|
2270
|
REVOKE ALL ON TABLE taxon_match_input FROM bien;
|
2271
|
GRANT ALL ON TABLE taxon_match_input TO bien;
|
2272
|
GRANT SELECT ON TABLE taxon_match_input TO bien_read;
|
2273
|
|
2274
|
|
2275
|
--
|
2276
|
-- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: ACL; Schema: TNRS; Owner: bien
|
2277
|
--
|
2278
|
|
2279
|
REVOKE ALL ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" FROM PUBLIC;
|
2280
|
REVOKE ALL ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" FROM bien;
|
2281
|
GRANT ALL ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" TO bien;
|
2282
|
GRANT SELECT ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" TO bien_read;
|
2283
|
|
2284
|
|
2285
|
--
|
2286
|
-- Name: taxon_scrub; Type: ACL; Schema: TNRS; Owner: bien
|
2287
|
--
|
2288
|
|
2289
|
REVOKE ALL ON TABLE taxon_scrub FROM PUBLIC;
|
2290
|
REVOKE ALL ON TABLE taxon_scrub FROM bien;
|
2291
|
GRANT ALL ON TABLE taxon_scrub TO bien;
|
2292
|
GRANT SELECT ON TABLE taxon_scrub TO bien_read;
|
2293
|
|
2294
|
|
2295
|
--
|
2296
|
-- PostgreSQL database dump complete
|
2297
|
--
|
2298
|
|