Project

General

Profile

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

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

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

    
16
--CREATE SCHEMA "TNRS";
17

    
18

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

    
23
COMMENT ON SCHEMA "TNRS" IS 'IMPORTANT: when changing this schema, you must also make the same changes on vegbiendev:
24
on vegbiendev:
25
# back up existing TNRS schema (in case of an accidental incorrect change):
26
$ make backups/TNRS.backup-remake # runtime: 3 min ("real 2m44.277s")
27
$ svn up
28
$ svn di
29
# make the changes shown in the diff
30
## to change column types:
31
SELECT util.set_col_types(''"TNRS".tnrs'', ARRAY[
32
  (''col'', ''new_type'')
33
]::util.col_cast[]); -- runtime: 9 min ("531282 ms")
34
$ 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

    
39

    
40
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
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
$$;
55

    
56

    
57
--
58
-- Name: family_is_homonym(text); Type: FUNCTION; Schema: TNRS; Owner: -
59
--
60

    
61
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

    
67

    
68
--
69
-- Name: genus_is_homonym(text); Type: FUNCTION; Schema: TNRS; Owner: -
70
--
71

    
72
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

    
78

    
79
--
80
-- 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
-- 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
DECLARE
98
	"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
	accepted_taxon_name_with_author text = NULLIF(concat_ws(' '
119
		, NULLIF(NULLIF(new."Accepted_name_family", 'Unknown'),
120
			new."Accepted_name")
121
		, new."Accepted_name"
122
		, new."Accepted_name_author"
123
		), '');
124
BEGIN
125
	/* 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
	new.is_valid_match = new."Taxonomic_status" != 'Invalid'
128
		AND COALESCE(CASE
129
		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
		END, false);
144
	new.scrubbed_unique_taxon_name = COALESCE(
145
		accepted_taxon_name_with_author, matched_taxon_name_with_author);
146
	
147
	RETURN new;
148
END;
149
$$;
150

    
151

    
152
--
153
-- Name: FUNCTION tnrs_populate_fields(); Type: COMMENT; Schema: TNRS; Owner: -
154
--
155

    
156
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
runtime: 30 min ("5564082 rows affected, 1918900 ms execution time")
159
VACUUM ANALYZE "TNRS".tnrs -- to remove previous rows
160
runtime: 1.5 min ("92633 ms")';
161

    
162

    
163
SET default_tablespace = '';
164

    
165
SET default_with_oids = false;
166

    
167
--
168
-- Name: tnrs; Type: TABLE; Schema: TNRS; Owner: -; Tablespace: 
169
--
170

    
171
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
    is_valid_match boolean NOT NULL,
210
    scrubbed_unique_taxon_name text
211
);
212

    
213

    
214
--
215
-- Name: TABLE tnrs; Type: COMMENT; Schema: TNRS; Owner: -
216
--
217

    
218
COMMENT ON TABLE tnrs IS 'IMPORTANT: when changing this table''s schema, you may need to re-run TNRS:
219
$ inputs/.TNRS/data.sql.run refresh';
220

    
221

    
222
--
223
-- Name: MatchedTaxon; Type: VIEW; Schema: TNRS; Owner: -
224
--
225

    
226
CREATE VIEW "MatchedTaxon" AS
227
 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

    
260

    
261
--
262
-- Name: ValidMatchedTaxon; Type: VIEW; Schema: TNRS; Owner: -
263
--
264

    
265
CREATE VIEW "ValidMatchedTaxon" AS
266
 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

    
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
-- 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
-- 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
-- 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
-- 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
-- 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
-- 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
-- PostgreSQL database dump complete
623
--
624

    
(8-8/10)