Project

General

Profile

1 10778 aaronmk
--
2
-- PostgreSQL database dump
3
--
4 10737 aaronmk
5 10778 aaronmk
SET statement_timeout = 0;
6 11708 aaronmk
SET lock_timeout = 0;
7 10778 aaronmk
SET client_encoding = 'UTF8';
8
SET standard_conforming_strings = on;
9
SET check_function_bodies = false;
10
SET client_min_messages = warning;
11
12 10737 aaronmk
--
13 10778 aaronmk
-- Name: TNRS; Type: SCHEMA; Schema: -; Owner: -
14
--
15 10737 aaronmk
16 10778 aaronmk
--CREATE SCHEMA "TNRS";
17 10725 aaronmk
18
19 11614 aaronmk
--
20
-- Name: SCHEMA "TNRS"; Type: COMMENT; Schema: -; Owner: -
21
--
22
23
COMMENT ON SCHEMA "TNRS" IS 'IMPORTANT: when changing this schema, you must also make the same changes on vegbiendev:
24 11617 aaronmk
on vegbiendev:
25
# back up existing TNRS schema (in case of an accidental incorrect change):
26 11614 aaronmk
$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")
27 11616 aaronmk
$ svn up
28
$ svn di
29
# make the changes shown in the diff
30
## to change column types:
31 11614 aaronmk
SELECT util.set_col_types(''"TNRS".tnrs'', ARRAY[
32
  (''col'', ''new_type'')
33 11616 aaronmk
]::util.col_cast[]); -- runtime: 9 min ("531282 ms")
34 11617 aaronmk
$ rm=1 inputs/.TNRS/schema.sql.run
35
# repeat until `svn di` shows no diff
36
# back up new TNRS schema:
37
$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")';
38 11614 aaronmk
39
40 10778 aaronmk
SET search_path = "TNRS", pg_catalog;
41
42
--
43
-- Name: batch__fill(); Type: FUNCTION; Schema: TNRS; Owner: -
44
--
45
46
CREATE FUNCTION batch__fill() RETURNS trigger
47
    LANGUAGE plpgsql
48
    AS $$
49 10728 aaronmk
BEGIN
50
	new.id_by_time = new.time_submitted;
51
	new.id = COALESCE(new.id, new.id_by_time);
52
	RETURN new;
53
END;
54 10778 aaronmk
$$;
55 10728 aaronmk
56
57
--
58 10778 aaronmk
-- Name: family_is_homonym(text); Type: FUNCTION; Schema: TNRS; Owner: -
59
--
60 10728 aaronmk
61 10778 aaronmk
CREATE FUNCTION family_is_homonym(family text) RETURNS boolean
62
    LANGUAGE sql STABLE STRICT
63
    AS $_$
64
SELECT EXISTS(SELECT * FROM "IRMNG".family_homonym_epithet WHERE "taxonNameOrEpithet" = $1)
65
$_$;
66 10736 aaronmk
67 10778 aaronmk
68 10736 aaronmk
--
69 10778 aaronmk
-- Name: genus_is_homonym(text); Type: FUNCTION; Schema: TNRS; Owner: -
70
--
71 10736 aaronmk
72 10778 aaronmk
CREATE FUNCTION genus_is_homonym(genus text) RETURNS boolean
73
    LANGUAGE sql STABLE STRICT
74
    AS $_$
75
SELECT EXISTS(SELECT * FROM "IRMNG".genus_homonym_epithet WHERE "taxonNameOrEpithet" = $1)
76
$_$;
77 7844 aaronmk
78 9985 aaronmk
79 10778 aaronmk
--
80 11709 aaronmk
-- Name: map_taxonomic_status(text, text); Type: FUNCTION; Schema: TNRS; Owner: -
81
--
82
83
CREATE FUNCTION map_taxonomic_status(taxonomic_status_verbatim text, scrubbed_unique_taxon_name text) RETURNS text
84
    LANGUAGE sql IMMUTABLE
85
    AS $_$
86
SELECT CASE WHEN $2 IS NOT NULL THEN 'accepted' ELSE lower($1) END
87
$_$;
88
89
90
--
91 10778 aaronmk
-- Name: tnrs_populate_fields(); Type: FUNCTION; Schema: TNRS; Owner: -
92
--
93
94
CREATE FUNCTION tnrs_populate_fields() RETURNS trigger
95
    LANGUAGE plpgsql
96
    AS $$
97 9763 aaronmk
DECLARE
98 11628 aaronmk
	"Specific_epithet_is_plant" boolean :=
99
		(CASE
100
		WHEN   new."Infraspecific_epithet_matched"	 IS NOT NULL
101
			OR new."Infraspecific_epithet_2_matched" IS NOT NULL
102
			OR new."Specific_epithet_score" >= 0.9 -- fuzzy match
103
			THEN true
104
		ELSE NULL -- ambiguous
105
		END);
106
	never_homonym boolean = COALESCE(new."Author_score" >= 0.6, false);
107
		-- author disambiguates
108
	family_is_homonym boolean = NOT never_homonym
109
		AND "TNRS".family_is_homonym(new."Family_matched");
110
	genus_is_homonym  boolean = NOT never_homonym
111
		AND "TNRS".genus_is_homonym(new."Genus_matched");
112
	matched_taxon_name_with_author text = NULLIF(concat_ws(' '
113
		, NULLIF(NULLIF(new."Name_matched_accepted_family", 'Unknown'),
114
			new."Name_matched")
115
		, NULLIF(new."Name_matched", 'No suitable matches found.')
116
		, new."Name_matched_author"
117
		), '');
118 11643 aaronmk
	accepted_taxon_name_with_author text = NULLIF(concat_ws(' '
119 11628 aaronmk
		, NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'),
120
			new."Accepted_name")
121
		, new."Accepted_name"
122
		, new."Accepted_name_author"
123
		), '');
124 11643 aaronmk
BEGIN
125 11629 aaronmk
	/* exclude homonyms because these are not valid matches (i.e. TNRS provides
126
	a name, but the name is not meaningful because it is not unambiguous). */
127 11711 aaronmk
	new.is_valid_match = new."Taxonomic_status" != 'Invalid'
128
		AND COALESCE(CASE
129 11628 aaronmk
		WHEN new."Family_score" = 1 AND NOT family_is_homonym -- exact match
130
			THEN true
131
		ELSE -- consider genus
132
			(CASE
133
			WHEN new."Genus_score" =  1	   -- exact match
134
				THEN
135
				(CASE
136
				WHEN NOT genus_is_homonym THEN true
137
				ELSE "Specific_epithet_is_plant"
138
				END)
139
			WHEN new."Genus_score" >= 0.85 -- fuzzy match
140
				THEN "Specific_epithet_is_plant"
141
			ELSE NULL -- ambiguous
142
			END)
143 11629 aaronmk
		END, false);
144 11632 aaronmk
	new.scrubbed_unique_taxon_name = COALESCE(
145 11643 aaronmk
		accepted_taxon_name_with_author, matched_taxon_name_with_author);
146 11628 aaronmk
147
	RETURN new;
148 7134 aaronmk
END;
149 10778 aaronmk
$$;
150
151
152
--
153
-- Name: FUNCTION tnrs_populate_fields(); Type: COMMENT; Schema: TNRS; Owner: -
154
--
155
156 10754 aaronmk
COMMENT ON FUNCTION tnrs_populate_fields() IS 'IMPORTANT: when changing this function, you must regenerate the derived cols:
157
UPDATE "TNRS".tnrs SET "Name_submitted" = "Name_submitted"
158 11715 aaronmk
runtime: 30 min ("5564082 rows affected, 1918900 ms execution time")
159 10754 aaronmk
VACUUM ANALYZE "TNRS".tnrs -- to remove previous rows
160 11615 aaronmk
runtime: 1.5 min ("92633 ms")';
161 7134 aaronmk
162 7251 aaronmk
163 10778 aaronmk
SET default_tablespace = '';
164
165
SET default_with_oids = false;
166
167 10728 aaronmk
--
168 10778 aaronmk
-- Name: tnrs; Type: TABLE; Schema: TNRS; Owner: -; Tablespace:
169
--
170 7251 aaronmk
171 10778 aaronmk
CREATE TABLE tnrs (
172
    batch text DEFAULT now() NOT NULL,
173
    "Name_number" integer NOT NULL,
174
    "Name_submitted" text NOT NULL,
175
    "Overall_score" double precision,
176
    "Name_matched" text,
177
    "Name_matched_rank" text,
178
    "Name_score" double precision,
179
    "Name_matched_author" text,
180
    "Name_matched_url" text,
181
    "Author_matched" text,
182
    "Author_score" double precision,
183
    "Family_matched" text,
184
    "Family_score" double precision,
185
    "Name_matched_accepted_family" text,
186
    "Genus_matched" text,
187
    "Genus_score" double precision,
188
    "Specific_epithet_matched" text,
189
    "Specific_epithet_score" double precision,
190
    "Infraspecific_rank" text,
191
    "Infraspecific_epithet_matched" text,
192
    "Infraspecific_epithet_score" double precision,
193
    "Infraspecific_rank_2" text,
194
    "Infraspecific_epithet_2_matched" text,
195
    "Infraspecific_epithet_2_score" double precision,
196
    "Annotations" text,
197
    "Unmatched_terms" text,
198
    "Taxonomic_status" text,
199
    "Accepted_name" text,
200
    "Accepted_name_author" text,
201
    "Accepted_name_rank" text,
202
    "Accepted_name_url" text,
203
    "Accepted_name_species" text,
204
    "Accepted_name_family" text,
205
    "Selected" text,
206
    "Source" text,
207
    "Warnings" text,
208
    "Accepted_name_lsid" text,
209 11628 aaronmk
    is_valid_match boolean NOT NULL,
210 11632 aaronmk
    scrubbed_unique_taxon_name text
211 10778 aaronmk
);
212 7823 aaronmk
213 9759 aaronmk
214 10778 aaronmk
--
215
-- Name: TABLE tnrs; Type: COMMENT; Schema: TNRS; Owner: -
216
--
217 7823 aaronmk
218 10787 aaronmk
COMMENT ON TABLE tnrs IS 'IMPORTANT: when changing this table''s schema, you may need to re-run TNRS:
219 11614 aaronmk
$ inputs/.TNRS/data.sql.run refresh';
220 10778 aaronmk
221
222
--
223
-- Name: MatchedTaxon; Type: VIEW; Schema: TNRS; Owner: -
224
--
225
226
CREATE VIEW "MatchedTaxon" AS
227 11708 aaronmk
 SELECT tnrs.batch AS "*Name_matched.batch",
228
    tnrs."Name_submitted" AS "concatenatedScientificName",
229
    tnrs."Name_matched" AS "matchedTaxonName",
230
    tnrs."Name_matched_rank" AS "matchedTaxonRank",
231
    tnrs."Name_score" AS "*Name_matched.Name_score",
232
    tnrs."Name_matched_author" AS "matchedScientificNameAuthorship",
233
    tnrs."Name_matched_url" AS "matchedScientificNameID",
234
    tnrs."Author_score" AS "*Name_matched.Author_score",
235
    tnrs."Family_score" AS "matchedFamilyConfidence_fraction",
236
    COALESCE(tnrs."Name_matched_accepted_family", tnrs."Accepted_name_family") AS "matchedFamily",
237
    tnrs."Genus_matched" AS "matchedGenus",
238
    tnrs."Genus_score" AS "matchedGenusConfidence_fraction",
239
    tnrs."Specific_epithet_matched" AS "matchedSpecificEpithet",
240
    tnrs."Specific_epithet_score" AS "matchedSpeciesConfidence_fraction",
241
    tnrs."Infraspecific_epithet_matched" AS "matchedInfraspecificEpithet",
242
    tnrs."Infraspecific_epithet_score" AS "*Name_matched.Infraspecific_epithet_score",
243
    tnrs."Annotations" AS "identificationQualifier",
244
    tnrs."Unmatched_terms" AS "morphospeciesSuffix",
245
    tnrs."Taxonomic_status" AS "taxonomicStatus",
246
    tnrs."Accepted_name" AS "acceptedTaxonName",
247
    tnrs."Accepted_name_author" AS "acceptedScientificNameAuthorship",
248
    tnrs."Accepted_name_rank" AS "acceptedTaxonRank",
249
    tnrs."Accepted_name_url" AS "acceptedScientificNameID",
250
    tnrs."Accepted_name_species" AS "*Name_matched.Accepted_name_species",
251
    tnrs."Accepted_name_family" AS "acceptedFamily",
252
    tnrs."Selected" AS "*Name_matched.Selected",
253
    tnrs."Source" AS "*Name_matched.Source",
254
    tnrs."Warnings" AS "*Name_matched.Warnings",
255
    tnrs."Accepted_name_lsid" AS "*Name_matched.Accepted_name_lsid",
256
    tnrs.is_valid_match AS taxon_scrub__is_valid_match,
257
    tnrs.scrubbed_unique_taxon_name
258
   FROM tnrs;
259 10778 aaronmk
260
261
--
262
-- Name: ValidMatchedTaxon; Type: VIEW; Schema: TNRS; Owner: -
263
--
264
265
CREATE VIEW "ValidMatchedTaxon" AS
266 11708 aaronmk
 SELECT "MatchedTaxon"."*Name_matched.batch",
267
    "MatchedTaxon"."concatenatedScientificName",
268
    "MatchedTaxon"."matchedTaxonName",
269
    "MatchedTaxon"."matchedTaxonRank",
270
    "MatchedTaxon"."*Name_matched.Name_score",
271
    "MatchedTaxon"."matchedScientificNameAuthorship",
272
    "MatchedTaxon"."matchedScientificNameID",
273
    "MatchedTaxon"."*Name_matched.Author_score",
274
    "MatchedTaxon"."matchedFamilyConfidence_fraction",
275
    "MatchedTaxon"."matchedFamily",
276
    "MatchedTaxon"."matchedGenus",
277
    "MatchedTaxon"."matchedGenusConfidence_fraction",
278
    "MatchedTaxon"."matchedSpecificEpithet",
279
    "MatchedTaxon"."matchedSpeciesConfidence_fraction",
280
    "MatchedTaxon"."matchedInfraspecificEpithet",
281
    "MatchedTaxon"."*Name_matched.Infraspecific_epithet_score",
282
    "MatchedTaxon"."identificationQualifier",
283
    "MatchedTaxon"."morphospeciesSuffix",
284
    "MatchedTaxon"."taxonomicStatus",
285
    "MatchedTaxon"."acceptedTaxonName",
286
    "MatchedTaxon"."acceptedScientificNameAuthorship",
287
    "MatchedTaxon"."acceptedTaxonRank",
288
    "MatchedTaxon"."acceptedScientificNameID",
289
    "MatchedTaxon"."*Name_matched.Accepted_name_species",
290
    "MatchedTaxon"."acceptedFamily",
291
    "MatchedTaxon"."*Name_matched.Selected",
292
    "MatchedTaxon"."*Name_matched.Source",
293
    "MatchedTaxon"."*Name_matched.Warnings",
294
    "MatchedTaxon"."*Name_matched.Accepted_name_lsid",
295
    "MatchedTaxon".taxon_scrub__is_valid_match,
296
    "MatchedTaxon".scrubbed_unique_taxon_name
297
   FROM "MatchedTaxon"
298
  WHERE "MatchedTaxon".taxon_scrub__is_valid_match;
299 10778 aaronmk
300
301
--
302
-- Name: VIEW "ValidMatchedTaxon"; Type: COMMENT; Schema: TNRS; Owner: -
303
--
304
305
COMMENT ON VIEW "ValidMatchedTaxon" IS 'to update, use * as the column list';
306
307
308
--
309 11912 aaronmk
-- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: VIEW; Schema: TNRS; Owner: -
310
--
311
312
CREATE VIEW "taxon_scrub.scrubbed_unique_taxon_name.*" AS
313
 SELECT tnrs."Name_submitted" AS scrubbed_unique_taxon_name,
314
    tnrs."Name_matched_rank" AS scrubbed_taxon_rank,
315
    tnrs."Name_matched_accepted_family" AS scrubbed_family,
316
    tnrs."Genus_matched" AS scrubbed_genus,
317
    tnrs."Specific_epithet_matched" AS scrubbed_specific_epithet,
318
    tnrs."Infraspecific_rank" AS scrubbed_infraspecific_rank,
319
    tnrs."Infraspecific_epithet_matched" AS scrubbed_infraspecific_epithet,
320
    tnrs."Name_matched_author" AS scrubbed_author,
321
    tnrs."Name_matched" AS scrubbed_taxon_name_no_author
322
   FROM tnrs;
323
324
325
--
326
-- Name: taxon_scrub; Type: VIEW; Schema: TNRS; Owner: -
327
--
328
329
CREATE VIEW taxon_scrub AS
330
 SELECT "ValidMatchedTaxon".scrubbed_unique_taxon_name,
331
    "ValidMatchedTaxon"."*Name_matched.batch",
332
    "ValidMatchedTaxon"."concatenatedScientificName",
333
    "ValidMatchedTaxon"."matchedTaxonName",
334
    "ValidMatchedTaxon"."matchedTaxonRank",
335
    "ValidMatchedTaxon"."*Name_matched.Name_score",
336
    "ValidMatchedTaxon"."matchedScientificNameAuthorship",
337
    "ValidMatchedTaxon"."matchedScientificNameID",
338
    "ValidMatchedTaxon"."*Name_matched.Author_score",
339
    "ValidMatchedTaxon"."matchedFamilyConfidence_fraction",
340
    "ValidMatchedTaxon"."matchedFamily",
341
    "ValidMatchedTaxon"."matchedGenus",
342
    "ValidMatchedTaxon"."matchedGenusConfidence_fraction",
343
    "ValidMatchedTaxon"."matchedSpecificEpithet",
344
    "ValidMatchedTaxon"."matchedSpeciesConfidence_fraction",
345
    "ValidMatchedTaxon"."matchedInfraspecificEpithet",
346
    "ValidMatchedTaxon"."*Name_matched.Infraspecific_epithet_score",
347
    "ValidMatchedTaxon"."identificationQualifier",
348
    "ValidMatchedTaxon"."morphospeciesSuffix",
349
    "ValidMatchedTaxon"."taxonomicStatus",
350
    "ValidMatchedTaxon"."acceptedTaxonName",
351
    "ValidMatchedTaxon"."acceptedScientificNameAuthorship",
352
    "ValidMatchedTaxon"."acceptedTaxonRank",
353
    "ValidMatchedTaxon"."acceptedScientificNameID",
354
    "ValidMatchedTaxon"."*Name_matched.Accepted_name_species",
355
    "ValidMatchedTaxon"."acceptedFamily",
356
    "ValidMatchedTaxon"."*Name_matched.Selected",
357
    "ValidMatchedTaxon"."*Name_matched.Source",
358
    "ValidMatchedTaxon"."*Name_matched.Warnings",
359
    "ValidMatchedTaxon"."*Name_matched.Accepted_name_lsid",
360
    "ValidMatchedTaxon".taxon_scrub__is_valid_match,
361
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_rank,
362
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_family,
363
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_genus,
364
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_specific_epithet,
365
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_rank,
366
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_infraspecific_epithet,
367
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_author,
368
    "taxon_scrub.scrubbed_unique_taxon_name.*".scrubbed_taxon_name_no_author
369
   FROM ("ValidMatchedTaxon"
370
   LEFT JOIN "taxon_scrub.scrubbed_unique_taxon_name.*" USING (scrubbed_unique_taxon_name));
371
372
373
--
374
-- Name: VIEW taxon_scrub; Type: COMMENT; Schema: TNRS; Owner: -
375
--
376
377
COMMENT ON VIEW taxon_scrub IS 'to update, use * as the column list';
378
379
380
--
381 10778 aaronmk
-- Name: batch; Type: TABLE; Schema: TNRS; Owner: -; Tablespace:
382
--
383
384
CREATE TABLE batch (
385
    id text NOT NULL,
386
    id_by_time text,
387
    time_submitted timestamp with time zone DEFAULT now(),
388
    client_version text
389
);
390
391
392
--
393
-- Name: batch_download_settings; Type: TABLE; Schema: TNRS; Owner: -; Tablespace:
394
--
395
396
CREATE TABLE batch_download_settings (
397
    id text NOT NULL,
398
    "E-mail" text,
399
    "Id" text,
400
    "Job type" text,
401
    "Contains Id" boolean,
402
    "Start time" text,
403
    "Finish time" text,
404
    "TNRS version" text,
405
    "Sources selected" text,
406
    "Match threshold" double precision,
407
    "Classification" text,
408
    "Allow partial matches?" boolean,
409
    "Sort by source" boolean,
410
    "Constrain by higher taxonomy" boolean
411
);
412
413
414
--
415
-- Name: TABLE batch_download_settings; Type: COMMENT; Schema: TNRS; Owner: -
416
--
417
418
COMMENT ON TABLE batch_download_settings IS 'stores data from http://tnrs.iplantcollaborative.org/TNRSapp.html > Submit List > results section > Download settings > settings.txt';
419
420
421
--
422
-- Name: client_version; Type: TABLE; Schema: TNRS; Owner: -; Tablespace:
423
--
424
425
CREATE TABLE client_version (
426
    id text NOT NULL,
427
    global_rev integer NOT NULL,
428
    "/lib/tnrs.py rev" integer,
429
    "/bin/tnrs_db rev" integer
430
);
431
432
433
--
434
-- Name: TABLE client_version; Type: COMMENT; Schema: TNRS; Owner: -
435
--
436
437
COMMENT ON TABLE client_version IS 'contains svn revisions';
438
439
440
--
441
-- Name: COLUMN client_version.global_rev; Type: COMMENT; Schema: TNRS; Owner: -
442
--
443
444
COMMENT ON COLUMN client_version.global_rev IS 'from `svn info .` > Last Changed Rev';
445
446
447
--
448
-- Name: COLUMN client_version."/lib/tnrs.py rev"; Type: COMMENT; Schema: TNRS; Owner: -
449
--
450
451
COMMENT ON COLUMN client_version."/lib/tnrs.py rev" IS 'from `svn info lib/tnrs.py` > Last Changed Rev';
452
453
454
--
455
-- Name: COLUMN client_version."/bin/tnrs_db rev"; Type: COMMENT; Schema: TNRS; Owner: -
456
--
457
458
COMMENT ON COLUMN client_version."/bin/tnrs_db rev" IS 'from `svn info bin/tnrs_db` > Last Changed Rev';
459
460
461
--
462
-- Name: batch_download_settings_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
463
--
464
465
ALTER TABLE ONLY batch_download_settings
466
    ADD CONSTRAINT batch_download_settings_pkey PRIMARY KEY (id);
467
468
469
--
470
-- Name: batch_id_by_time_key; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
471
--
472
473
ALTER TABLE ONLY batch
474
    ADD CONSTRAINT batch_id_by_time_key UNIQUE (id_by_time);
475
476
477
--
478
-- Name: batch_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
479
--
480
481
ALTER TABLE ONLY batch
482
    ADD CONSTRAINT batch_pkey PRIMARY KEY (id);
483
484
485
--
486
-- Name: client_version_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
487
--
488
489
ALTER TABLE ONLY client_version
490
    ADD CONSTRAINT client_version_pkey PRIMARY KEY (id);
491
492
493
--
494
-- Name: tnrs_Name_submitted_key; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
495
--
496
497
ALTER TABLE ONLY tnrs
498
    ADD CONSTRAINT "tnrs_Name_submitted_key" UNIQUE ("Name_submitted");
499
500
501
--
502
-- Name: tnrs_pkey; Type: CONSTRAINT; Schema: TNRS; Owner: -; Tablespace:
503
--
504
505
ALTER TABLE ONLY tnrs
506
    ADD CONSTRAINT tnrs_pkey PRIMARY KEY (batch, "Name_number");
507
508
509
--
510 10793 aaronmk
-- Name: batch_client_version_idx; Type: INDEX; Schema: TNRS; Owner: -; Tablespace:
511
--
512
513
CREATE INDEX batch_client_version_idx ON batch USING btree (client_version);
514
515
516
--
517 11607 aaronmk
-- Name: tnrs__valid_match; Type: INDEX; Schema: TNRS; Owner: -; Tablespace:
518
--
519
520
CREATE UNIQUE INDEX tnrs__valid_match ON tnrs USING btree ("Name_submitted") WHERE is_valid_match;
521
522
523
--
524 10778 aaronmk
-- Name: batch__fill; Type: TRIGGER; Schema: TNRS; Owner: -
525
--
526
527
CREATE TRIGGER batch__fill BEFORE INSERT OR UPDATE ON batch FOR EACH ROW EXECUTE PROCEDURE batch__fill();
528
529
530
--
531
-- Name: tnrs_populate_fields; Type: TRIGGER; Schema: TNRS; Owner: -
532
--
533
534
CREATE TRIGGER tnrs_populate_fields BEFORE INSERT OR UPDATE ON tnrs FOR EACH ROW EXECUTE PROCEDURE tnrs_populate_fields();
535
536
537
--
538
-- Name: batch_client_version_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: -
539
--
540
541
ALTER TABLE ONLY batch
542
    ADD CONSTRAINT batch_client_version_fkey FOREIGN KEY (client_version) REFERENCES client_version(id) ON UPDATE CASCADE ON DELETE CASCADE;
543
544
545
--
546
-- Name: batch_download_settings_id_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: -
547
--
548
549
ALTER TABLE ONLY batch_download_settings
550
    ADD CONSTRAINT batch_download_settings_id_fkey FOREIGN KEY (id) REFERENCES batch(id) ON UPDATE CASCADE ON DELETE CASCADE;
551
552
553
--
554
-- Name: tnrs_batch_fkey; Type: FK CONSTRAINT; Schema: TNRS; Owner: -
555
--
556
557
ALTER TABLE ONLY tnrs
558
    ADD CONSTRAINT tnrs_batch_fkey FOREIGN KEY (batch) REFERENCES batch(id) ON UPDATE CASCADE ON DELETE CASCADE;
559
560
561
--
562
-- Name: TNRS; Type: ACL; Schema: -; Owner: -
563
--
564
565
REVOKE ALL ON SCHEMA "TNRS" FROM PUBLIC;
566
REVOKE ALL ON SCHEMA "TNRS" FROM bien;
567
GRANT ALL ON SCHEMA "TNRS" TO bien;
568
GRANT USAGE ON SCHEMA "TNRS" TO bien_read;
569
570
571
--
572
-- Name: tnrs; Type: ACL; Schema: TNRS; Owner: -
573
--
574
575
REVOKE ALL ON TABLE tnrs FROM PUBLIC;
576
REVOKE ALL ON TABLE tnrs FROM bien;
577
GRANT ALL ON TABLE tnrs TO bien;
578
GRANT SELECT ON TABLE tnrs TO bien_read;
579
580
581
--
582 11912 aaronmk
-- Name: MatchedTaxon; Type: ACL; Schema: TNRS; Owner: -
583
--
584
585
REVOKE ALL ON TABLE "MatchedTaxon" FROM PUBLIC;
586
REVOKE ALL ON TABLE "MatchedTaxon" FROM bien;
587
GRANT ALL ON TABLE "MatchedTaxon" TO bien;
588
GRANT SELECT ON TABLE "MatchedTaxon" TO bien_read;
589
590
591
--
592
-- Name: ValidMatchedTaxon; Type: ACL; Schema: TNRS; Owner: -
593
--
594
595
REVOKE ALL ON TABLE "ValidMatchedTaxon" FROM PUBLIC;
596
REVOKE ALL ON TABLE "ValidMatchedTaxon" FROM bien;
597
GRANT ALL ON TABLE "ValidMatchedTaxon" TO bien;
598
GRANT SELECT ON TABLE "ValidMatchedTaxon" TO bien_read;
599
600
601
--
602
-- Name: taxon_scrub.scrubbed_unique_taxon_name.*; Type: ACL; Schema: TNRS; Owner: -
603
--
604
605
REVOKE ALL ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" FROM PUBLIC;
606
REVOKE ALL ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" FROM bien;
607
GRANT ALL ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" TO bien;
608
GRANT SELECT ON TABLE "taxon_scrub.scrubbed_unique_taxon_name.*" TO bien_read;
609
610
611
--
612
-- Name: taxon_scrub; Type: ACL; Schema: TNRS; Owner: -
613
--
614
615
REVOKE ALL ON TABLE taxon_scrub FROM PUBLIC;
616
REVOKE ALL ON TABLE taxon_scrub FROM bien;
617
GRANT ALL ON TABLE taxon_scrub TO bien;
618
GRANT SELECT ON TABLE taxon_scrub TO bien_read;
619
620
621
--
622 10778 aaronmk
-- PostgreSQL database dump complete
623
--