Revision 1337
Added by Aaron Marcuse-Kubitza almost 13 years ago
| vegbien.sql | ||
|---|---|---|
| 61 | 61 |
|
| 62 | 62 |
|
| 63 | 63 |
-- |
| 64 |
-- Name: role; Type: TYPE; Schema: public; Owner: - |
|
| 65 |
-- |
|
| 66 |
|
|
| 67 |
CREATE TYPE role AS ENUM ( |
|
| 68 |
'collector', |
|
| 69 |
'identifier', |
|
| 70 |
'computer', |
|
| 71 |
'contributor' |
|
| 72 |
); |
|
| 73 |
|
|
| 74 |
|
|
| 75 |
-- |
|
| 64 | 76 |
-- Name: taxonclass; Type: TYPE; Schema: public; Owner: - |
| 65 | 77 |
-- |
| 66 | 78 |
|
| ... | ... | |
| 258 | 270 |
classcontributor_id integer NOT NULL, |
| 259 | 271 |
commclass_id integer NOT NULL, |
| 260 | 272 |
party_id integer NOT NULL, |
| 261 |
role_id integer
|
|
| 273 |
role role
|
|
| 262 | 274 |
); |
| 263 | 275 |
|
| 264 | 276 |
|
| ... | ... | |
| 1004 | 1016 |
locationeventcontributor_id integer NOT NULL, |
| 1005 | 1017 |
locationevent_id integer NOT NULL, |
| 1006 | 1018 |
party_id integer NOT NULL, |
| 1007 |
role_id integer NOT NULL,
|
|
| 1019 |
role role NOT NULL,
|
|
| 1008 | 1020 |
contributiondate timestamp with time zone |
| 1009 | 1021 |
); |
| 1010 | 1022 |
|
| ... | ... | |
| 1037 | 1049 |
synonymlocationevent_id integer NOT NULL, |
| 1038 | 1050 |
primarylocationevent_id integer NOT NULL, |
| 1039 | 1051 |
party_id integer NOT NULL, |
| 1040 |
role_id integer NOT NULL,
|
|
| 1052 |
role role NOT NULL,
|
|
| 1041 | 1053 |
synonymcomment text, |
| 1042 | 1054 |
classstartdate timestamp with time zone DEFAULT now() NOT NULL, |
| 1043 | 1055 |
classstopdate timestamp with time zone, |
| ... | ... | |
| 1427 | 1439 |
note_id integer NOT NULL, |
| 1428 | 1440 |
notelink_id integer NOT NULL, |
| 1429 | 1441 |
party_id integer NOT NULL, |
| 1430 |
role_id integer NOT NULL,
|
|
| 1442 |
role role NOT NULL,
|
|
| 1431 | 1443 |
notetype text NOT NULL, |
| 1432 | 1444 |
notetext text NOT NULL, |
| 1433 | 1445 |
notedate timestamp with time zone, |
| ... | ... | |
| 1533 | 1545 |
partymember_id integer NOT NULL, |
| 1534 | 1546 |
parentparty_id integer NOT NULL, |
| 1535 | 1547 |
childparty_id integer NOT NULL, |
| 1536 |
role_id integer,
|
|
| 1548 |
role role,
|
|
| 1537 | 1549 |
memberstart timestamp with time zone DEFAULT now() NOT NULL, |
| 1538 | 1550 |
memberstop timestamp with time zone |
| 1539 | 1551 |
); |
| ... | ... | |
| 1920 | 1932 |
projectcontributor_id integer NOT NULL, |
| 1921 | 1933 |
project_id integer NOT NULL, |
| 1922 | 1934 |
party_id integer NOT NULL, |
| 1923 |
role_id integer,
|
|
| 1935 |
role role,
|
|
| 1924 | 1936 |
surname text, |
| 1925 | 1937 |
cheatrole text |
| 1926 | 1938 |
); |
| ... | ... | |
| 2167 | 2179 |
|
| 2168 | 2180 |
|
| 2169 | 2181 |
-- |
| 2170 |
-- Name: role; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 2171 |
-- |
|
| 2172 |
|
|
| 2173 |
CREATE TABLE role ( |
|
| 2174 |
role_id integer NOT NULL, |
|
| 2175 |
rolecode text NOT NULL, |
|
| 2176 |
roledescription text, |
|
| 2177 |
roleproject integer, |
|
| 2178 |
roleobservation integer, |
|
| 2179 |
roletaxonint integer, |
|
| 2180 |
roleclassint integer, |
|
| 2181 |
accessioncode text |
|
| 2182 |
); |
|
| 2183 |
|
|
| 2184 |
|
|
| 2185 |
-- |
|
| 2186 |
-- Name: role_role_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
|
| 2187 |
-- |
|
| 2188 |
|
|
| 2189 |
CREATE SEQUENCE role_role_id_seq |
|
| 2190 |
START WITH 1 |
|
| 2191 |
INCREMENT BY 1 |
|
| 2192 |
NO MINVALUE |
|
| 2193 |
NO MAXVALUE |
|
| 2194 |
CACHE 1; |
|
| 2195 |
|
|
| 2196 |
|
|
| 2197 |
-- |
|
| 2198 |
-- Name: role_role_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - |
|
| 2199 |
-- |
|
| 2200 |
|
|
| 2201 |
ALTER SEQUENCE role_role_id_seq OWNED BY role.role_id; |
|
| 2202 |
|
|
| 2203 |
|
|
| 2204 |
-- |
|
| 2205 | 2182 |
-- Name: soilobs; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 2206 | 2183 |
-- |
| 2207 | 2184 |
|
| ... | ... | |
| 2645 | 2622 |
taxonoccurrence_id integer NOT NULL, |
| 2646 | 2623 |
plantconcept_id integer NOT NULL, |
| 2647 | 2624 |
party_id integer, |
| 2648 |
role_id integer NOT NULL,
|
|
| 2625 |
role role NOT NULL,
|
|
| 2649 | 2626 |
determinationtype text, |
| 2650 | 2627 |
reference_id integer, |
| 2651 | 2628 |
isoriginal boolean DEFAULT false NOT NULL, |
| ... | ... | |
| 3181 | 3158 |
|
| 3182 | 3159 |
|
| 3183 | 3160 |
-- |
| 3184 |
-- Name: role_id; Type: DEFAULT; Schema: public; Owner: - |
|
| 3185 |
-- |
|
| 3186 |
|
|
| 3187 |
ALTER TABLE role ALTER COLUMN role_id SET DEFAULT nextval('role_role_id_seq'::regclass);
|
|
| 3188 |
|
|
| 3189 |
|
|
| 3190 |
-- |
|
| 3191 | 3161 |
-- Name: soilobs_id; Type: DEFAULT; Schema: public; Owner: - |
| 3192 | 3162 |
-- |
| 3193 | 3163 |
|
| ... | ... | |
| 3806 | 3776 |
|
| 3807 | 3777 |
|
| 3808 | 3778 |
-- |
| 3809 |
-- Name: role_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3810 |
-- |
|
| 3811 |
|
|
| 3812 |
ALTER TABLE ONLY role |
|
| 3813 |
ADD CONSTRAINT role_pkey PRIMARY KEY (role_id); |
|
| 3814 |
|
|
| 3815 |
|
|
| 3816 |
-- |
|
| 3817 | 3779 |
-- Name: soilobs_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 3818 | 3780 |
-- |
| 3819 | 3781 |
|
| ... | ... | |
| 4028 | 3990 |
-- Name: classcontributor_role_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4029 | 3991 |
-- |
| 4030 | 3992 |
|
| 4031 |
CREATE INDEX classcontributor_role_id_x ON classcontributor USING btree (role_id);
|
|
| 3993 |
CREATE INDEX classcontributor_role_id_x ON classcontributor USING btree (role); |
|
| 4032 | 3994 |
|
| 4033 | 3995 |
|
| 4034 | 3996 |
-- |
| ... | ... | |
| 4546 | 4508 |
-- Name: locationeventcontributor_role_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4547 | 4509 |
-- |
| 4548 | 4510 |
|
| 4549 |
CREATE INDEX locationeventcontributor_role_id_x ON locationeventcontributor USING btree (role_id);
|
|
| 4511 |
CREATE INDEX locationeventcontributor_role_id_x ON locationeventcontributor USING btree (role); |
|
| 4550 | 4512 |
|
| 4551 | 4513 |
|
| 4552 | 4514 |
-- |
| ... | ... | |
| 4574 | 4536 |
-- Name: locationeventsynonym_role_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4575 | 4537 |
-- |
| 4576 | 4538 |
|
| 4577 |
CREATE INDEX locationeventsynonym_role_id_x ON locationeventsynonym USING btree (role_id);
|
|
| 4539 |
CREATE INDEX locationeventsynonym_role_id_x ON locationeventsynonym USING btree (role); |
|
| 4578 | 4540 |
|
| 4579 | 4541 |
|
| 4580 | 4542 |
-- |
| ... | ... | |
| 4630 | 4592 |
-- Name: note_role_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4631 | 4593 |
-- |
| 4632 | 4594 |
|
| 4633 |
CREATE INDEX note_role_id_x ON note USING btree (role_id);
|
|
| 4595 |
CREATE INDEX note_role_id_x ON note USING btree (role); |
|
| 4634 | 4596 |
|
| 4635 | 4597 |
|
| 4636 | 4598 |
-- |
| ... | ... | |
| 4707 | 4669 |
-- Name: partymember_role_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4708 | 4670 |
-- |
| 4709 | 4671 |
|
| 4710 |
CREATE INDEX partymember_role_id_x ON partymember USING btree (role_id);
|
|
| 4672 |
CREATE INDEX partymember_role_id_x ON partymember USING btree (role); |
|
| 4711 | 4673 |
|
| 4712 | 4674 |
|
| 4713 | 4675 |
-- |
| ... | ... | |
| 4875 | 4837 |
-- Name: projectcontributor_role_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4876 | 4838 |
-- |
| 4877 | 4839 |
|
| 4878 |
CREATE INDEX projectcontributor_role_id_x ON projectcontributor USING btree (role_id);
|
|
| 4840 |
CREATE INDEX projectcontributor_role_id_x ON projectcontributor USING btree (role); |
|
| 4879 | 4841 |
|
| 4880 | 4842 |
|
| 4881 | 4843 |
-- |
| ... | ... | |
| 4942 | 4904 |
|
| 4943 | 4905 |
|
| 4944 | 4906 |
-- |
| 4945 |
-- Name: role_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4946 |
-- |
|
| 4947 |
|
|
| 4948 |
CREATE UNIQUE INDEX role_accessioncode_index ON role USING btree (accessioncode); |
|
| 4949 |
|
|
| 4950 |
|
|
| 4951 |
-- |
|
| 4952 | 4907 |
-- Name: soilobs_locationevent_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4953 | 4908 |
-- |
| 4954 | 4909 |
|
| ... | ... | |
| 5036 | 4991 |
-- Name: taxondetermination_role_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 5037 | 4992 |
-- |
| 5038 | 4993 |
|
| 5039 |
CREATE INDEX taxondetermination_role_id_x ON taxondetermination USING btree (role_id);
|
|
| 4994 |
CREATE INDEX taxondetermination_role_id_x ON taxondetermination USING btree (role); |
|
| 5040 | 4995 |
|
| 5041 | 4996 |
|
| 5042 | 4997 |
-- |
| ... | ... | |
| 5050 | 5005 |
-- Name: taxondetermination_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 5051 | 5006 |
-- |
| 5052 | 5007 |
|
| 5053 |
CREATE UNIQUE INDEX taxondetermination_unique ON taxondetermination USING btree (taxonoccurrence_id, role_id, (COALESCE(party_id, 0)), plantconcept_id);
|
|
| 5008 |
CREATE UNIQUE INDEX taxondetermination_unique ON taxondetermination USING btree (taxonoccurrence_id, role, (COALESCE(party_id, 0)), plantconcept_id); |
|
| 5054 | 5009 |
|
| 5055 | 5010 |
|
| 5056 | 5011 |
-- |
| ... | ... | |
| 5161 | 5116 |
|
| 5162 | 5117 |
|
| 5163 | 5118 |
-- |
| 5164 |
-- Name: classcontributor_role_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5165 |
-- |
|
| 5166 |
|
|
| 5167 |
ALTER TABLE ONLY classcontributor |
|
| 5168 |
ADD CONSTRAINT classcontributor_role_id FOREIGN KEY (role_id) REFERENCES role(role_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5169 |
|
|
| 5170 |
|
|
| 5171 |
-- |
|
| 5172 | 5119 |
-- Name: commclass_classpublication_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5173 | 5120 |
-- |
| 5174 | 5121 |
|
| ... | ... | |
| 5473 | 5420 |
|
| 5474 | 5421 |
|
| 5475 | 5422 |
-- |
| 5476 |
-- Name: locationeventcontributor_role_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5477 |
-- |
|
| 5478 |
|
|
| 5479 |
ALTER TABLE ONLY locationeventcontributor |
|
| 5480 |
ADD CONSTRAINT locationeventcontributor_role_id FOREIGN KEY (role_id) REFERENCES role(role_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5481 |
|
|
| 5482 |
|
|
| 5483 |
-- |
|
| 5484 | 5423 |
-- Name: locationeventsynonym_party_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5485 | 5424 |
-- |
| 5486 | 5425 |
|
| ... | ... | |
| 5497 | 5436 |
|
| 5498 | 5437 |
|
| 5499 | 5438 |
-- |
| 5500 |
-- Name: locationeventsynonym_role_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5501 |
-- |
|
| 5502 |
|
|
| 5503 |
ALTER TABLE ONLY locationeventsynonym |
|
| 5504 |
ADD CONSTRAINT locationeventsynonym_role_id FOREIGN KEY (role_id) REFERENCES role(role_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5505 |
|
|
| 5506 |
|
|
| 5507 |
-- |
|
| 5508 | 5439 |
-- Name: locationeventsynonym_synonymlocationevent_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5509 | 5440 |
-- |
| 5510 | 5441 |
|
| ... | ... | |
| 5601 | 5532 |
|
| 5602 | 5533 |
|
| 5603 | 5534 |
-- |
| 5604 |
-- Name: note_role_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5605 |
-- |
|
| 5606 |
|
|
| 5607 |
ALTER TABLE ONLY note |
|
| 5608 |
ADD CONSTRAINT note_role_id FOREIGN KEY (role_id) REFERENCES role(role_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5609 |
|
|
| 5610 |
|
|
| 5611 |
-- |
|
| 5612 | 5535 |
-- Name: party_currentname_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5613 | 5536 |
-- |
| 5614 | 5537 |
|
| ... | ... | |
| 5633 | 5556 |
|
| 5634 | 5557 |
|
| 5635 | 5558 |
-- |
| 5636 |
-- Name: partymember_role_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5637 |
-- |
|
| 5638 |
|
|
| 5639 |
ALTER TABLE ONLY partymember |
|
| 5640 |
ADD CONSTRAINT partymember_role_id FOREIGN KEY (role_id) REFERENCES role(role_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5641 |
|
|
| 5642 |
|
|
| 5643 |
-- |
|
| 5644 | 5559 |
-- Name: plantconcept_plantname_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5645 | 5560 |
-- |
| 5646 | 5561 |
|
| ... | ... | |
| 5809 | 5724 |
|
| 5810 | 5725 |
|
| 5811 | 5726 |
-- |
| 5812 |
-- Name: projectcontributor_role_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5813 |
-- |
|
| 5814 |
|
|
| 5815 |
ALTER TABLE ONLY projectcontributor |
|
| 5816 |
ADD CONSTRAINT projectcontributor_role_id FOREIGN KEY (role_id) REFERENCES role(role_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5817 |
|
|
| 5818 |
|
|
| 5819 |
-- |
|
| 5820 | 5727 |
-- Name: reference_referencejournal_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5821 | 5728 |
-- |
| 5822 | 5729 |
|
| ... | ... | |
| 5985 | 5892 |
|
| 5986 | 5893 |
|
| 5987 | 5894 |
-- |
| 5988 |
-- Name: taxondetermination_role_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5989 |
-- |
|
| 5990 |
|
|
| 5991 |
ALTER TABLE ONLY taxondetermination |
|
| 5992 |
ADD CONSTRAINT taxondetermination_role_id FOREIGN KEY (role_id) REFERENCES role(role_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5993 |
|
|
| 5994 |
|
|
| 5995 |
-- |
|
| 5996 | 5895 |
-- Name: taxondetermination_taxonoccurrence_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5997 | 5896 |
-- |
| 5998 | 5897 |
|
Also available in: Unified diff
VegBIEN: Changed role table to a closed list