Revision 1337
Added by Aaron Marcuse-Kubitza over 12 years ago
| vegbien.my.sql | ||
|---|---|---|
| 39 | 39 |
|
| 40 | 40 |
|
| 41 | 41 |
-- |
| 42 |
-- Name: role; Type: TYPE; Schema: public; Owner: - |
|
| 43 |
-- |
|
| 44 |
|
|
| 45 |
|
|
| 46 |
|
|
| 47 |
|
|
| 48 |
-- |
|
| 42 | 49 |
-- Name: taxonclass; Type: TYPE; Schema: public; Owner: - |
| 43 | 50 |
-- |
| 44 | 51 |
|
| ... | ... | |
| 172 | 179 |
classcontributor_id int(11) NOT NULL, |
| 173 | 180 |
commclass_id int(11) NOT NULL, |
| 174 | 181 |
party_id int(11) NOT NULL, |
| 175 |
role_id int(11)
|
|
| 182 |
role text
|
|
| 176 | 183 |
); |
| 177 | 184 |
|
| 178 | 185 |
|
| ... | ... | |
| 828 | 835 |
locationeventcontributor_id int(11) NOT NULL, |
| 829 | 836 |
locationevent_id int(11) NOT NULL, |
| 830 | 837 |
party_id int(11) NOT NULL, |
| 831 |
role_id int(11) NOT NULL,
|
|
| 838 |
role text NOT NULL,
|
|
| 832 | 839 |
contributiondate timestamp NULL |
| 833 | 840 |
); |
| 834 | 841 |
|
| ... | ... | |
| 856 | 863 |
synonymlocationevent_id int(11) NOT NULL, |
| 857 | 864 |
primarylocationevent_id int(11) NOT NULL, |
| 858 | 865 |
party_id int(11) NOT NULL, |
| 859 |
role_id int(11) NOT NULL,
|
|
| 866 |
role text NOT NULL,
|
|
| 860 | 867 |
synonymcomment text, |
| 861 | 868 |
classstartdate timestamp NULL NOT NULL, |
| 862 | 869 |
classstopdate timestamp NULL, |
| ... | ... | |
| 1195 | 1202 |
note_id int(11) NOT NULL, |
| 1196 | 1203 |
notelink_id int(11) NOT NULL, |
| 1197 | 1204 |
party_id int(11) NOT NULL, |
| 1198 |
role_id int(11) NOT NULL,
|
|
| 1205 |
role text NOT NULL,
|
|
| 1199 | 1206 |
notetype text NOT NULL, |
| 1200 | 1207 |
notetext text NOT NULL, |
| 1201 | 1208 |
notedate timestamp NULL, |
| ... | ... | |
| 1286 | 1293 |
partymember_id int(11) NOT NULL, |
| 1287 | 1294 |
parentparty_id int(11) NOT NULL, |
| 1288 | 1295 |
childparty_id int(11) NOT NULL, |
| 1289 |
role_id int(11),
|
|
| 1296 |
role text,
|
|
| 1290 | 1297 |
memberstart timestamp NULL NOT NULL, |
| 1291 | 1298 |
memberstop timestamp NULL |
| 1292 | 1299 |
); |
| ... | ... | |
| 1618 | 1625 |
projectcontributor_id int(11) NOT NULL, |
| 1619 | 1626 |
project_id int(11) NOT NULL, |
| 1620 | 1627 |
party_id int(11) NOT NULL, |
| 1621 |
role_id int(11),
|
|
| 1628 |
role text,
|
|
| 1622 | 1629 |
surname text, |
| 1623 | 1630 |
cheatrole text |
| 1624 | 1631 |
); |
| ... | ... | |
| 1830 | 1837 |
|
| 1831 | 1838 |
|
| 1832 | 1839 |
-- |
| 1833 |
-- Name: role; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 1834 |
-- |
|
| 1835 |
|
|
| 1836 |
CREATE TABLE role ( |
|
| 1837 |
role_id int(11) NOT NULL, |
|
| 1838 |
rolecode text NOT NULL, |
|
| 1839 |
roledescription text, |
|
| 1840 |
roleproject int(11), |
|
| 1841 |
roleobservation int(11), |
|
| 1842 |
roletaxonint int(11), |
|
| 1843 |
roleclassint int(11), |
|
| 1844 |
accessioncode text |
|
| 1845 |
); |
|
| 1846 |
|
|
| 1847 |
|
|
| 1848 |
-- |
|
| 1849 |
-- Name: role_role_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
|
| 1850 |
-- |
|
| 1851 |
|
|
| 1852 |
|
|
| 1853 |
|
|
| 1854 |
|
|
| 1855 |
-- |
|
| 1856 |
-- Name: role_role_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - |
|
| 1857 |
-- |
|
| 1858 |
|
|
| 1859 |
|
|
| 1860 |
|
|
| 1861 |
|
|
| 1862 |
-- |
|
| 1863 | 1840 |
-- Name: soilobs; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 1864 | 1841 |
-- |
| 1865 | 1842 |
|
| ... | ... | |
| 2263 | 2240 |
taxonoccurrence_id int(11) NOT NULL, |
| 2264 | 2241 |
plantconcept_id int(11) NOT NULL, |
| 2265 | 2242 |
party_id int(11), |
| 2266 |
role_id int(11) NOT NULL,
|
|
| 2243 |
role text NOT NULL,
|
|
| 2267 | 2244 |
determinationtype text, |
| 2268 | 2245 |
reference_id int(11), |
| 2269 | 2246 |
isoriginal int(1) DEFAULT false NOT NULL, |
| ... | ... | |
| 2769 | 2746 |
|
| 2770 | 2747 |
|
| 2771 | 2748 |
-- |
| 2772 |
-- Name: role_id; Type: DEFAULT; Schema: public; Owner: - |
|
| 2773 |
-- |
|
| 2774 |
|
|
| 2775 |
|
|
| 2776 |
|
|
| 2777 |
|
|
| 2778 |
-- |
|
| 2779 | 2749 |
-- Name: soilobs_id; Type: DEFAULT; Schema: public; Owner: - |
| 2780 | 2750 |
-- |
| 2781 | 2751 |
|
| ... | ... | |
| 3394 | 3364 |
|
| 3395 | 3365 |
|
| 3396 | 3366 |
-- |
| 3397 |
-- Name: role_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3398 |
-- |
|
| 3399 |
|
|
| 3400 |
ALTER TABLE role |
|
| 3401 |
ADD CONSTRAINT role_pkey PRIMARY KEY (role_id); |
|
| 3402 |
|
|
| 3403 |
|
|
| 3404 |
-- |
|
| 3405 | 3367 |
-- Name: soilobs_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 3406 | 3368 |
-- |
| 3407 | 3369 |
|
| ... | ... | |
| 3616 | 3578 |
-- Name: classcontributor_role_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 3617 | 3579 |
-- |
| 3618 | 3580 |
|
| 3619 |
CREATE INDEX classcontributor_role_id_x ON classcontributor (role_id);
|
|
| 3581 |
CREATE INDEX classcontributor_role_id_x ON classcontributor (role); |
|
| 3620 | 3582 |
|
| 3621 | 3583 |
|
| 3622 | 3584 |
-- |
| ... | ... | |
| 4134 | 4096 |
-- Name: locationeventcontributor_role_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4135 | 4097 |
-- |
| 4136 | 4098 |
|
| 4137 |
CREATE INDEX locationeventcontributor_role_id_x ON locationeventcontributor (role_id);
|
|
| 4099 |
CREATE INDEX locationeventcontributor_role_id_x ON locationeventcontributor (role); |
|
| 4138 | 4100 |
|
| 4139 | 4101 |
|
| 4140 | 4102 |
-- |
| ... | ... | |
| 4162 | 4124 |
-- Name: locationeventsynonym_role_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4163 | 4125 |
-- |
| 4164 | 4126 |
|
| 4165 |
CREATE INDEX locationeventsynonym_role_id_x ON locationeventsynonym (role_id);
|
|
| 4127 |
CREATE INDEX locationeventsynonym_role_id_x ON locationeventsynonym (role); |
|
| 4166 | 4128 |
|
| 4167 | 4129 |
|
| 4168 | 4130 |
-- |
| ... | ... | |
| 4218 | 4180 |
-- Name: note_role_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4219 | 4181 |
-- |
| 4220 | 4182 |
|
| 4221 |
CREATE INDEX note_role_id_x ON note (role_id);
|
|
| 4183 |
CREATE INDEX note_role_id_x ON note (role); |
|
| 4222 | 4184 |
|
| 4223 | 4185 |
|
| 4224 | 4186 |
-- |
| ... | ... | |
| 4295 | 4257 |
-- Name: partymember_role_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4296 | 4258 |
-- |
| 4297 | 4259 |
|
| 4298 |
CREATE INDEX partymember_role_id_x ON partymember (role_id);
|
|
| 4260 |
CREATE INDEX partymember_role_id_x ON partymember (role); |
|
| 4299 | 4261 |
|
| 4300 | 4262 |
|
| 4301 | 4263 |
-- |
| ... | ... | |
| 4463 | 4425 |
-- Name: projectcontributor_role_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4464 | 4426 |
-- |
| 4465 | 4427 |
|
| 4466 |
CREATE INDEX projectcontributor_role_id_x ON projectcontributor (role_id);
|
|
| 4428 |
CREATE INDEX projectcontributor_role_id_x ON projectcontributor (role); |
|
| 4467 | 4429 |
|
| 4468 | 4430 |
|
| 4469 | 4431 |
-- |
| ... | ... | |
| 4530 | 4492 |
|
| 4531 | 4493 |
|
| 4532 | 4494 |
-- |
| 4533 |
-- Name: role_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4534 |
-- |
|
| 4535 |
|
|
| 4536 |
CREATE UNIQUE INDEX role_accessioncode_index ON role (accessioncode); |
|
| 4537 |
|
|
| 4538 |
|
|
| 4539 |
-- |
|
| 4540 | 4495 |
-- Name: soilobs_locationevent_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4541 | 4496 |
-- |
| 4542 | 4497 |
|
| ... | ... | |
| 4624 | 4579 |
-- Name: taxondetermination_role_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4625 | 4580 |
-- |
| 4626 | 4581 |
|
| 4627 |
CREATE INDEX taxondetermination_role_id_x ON taxondetermination (role_id);
|
|
| 4582 |
CREATE INDEX taxondetermination_role_id_x ON taxondetermination (role); |
|
| 4628 | 4583 |
|
| 4629 | 4584 |
|
| 4630 | 4585 |
-- |
| ... | ... | |
| 4748 | 4703 |
|
| 4749 | 4704 |
|
| 4750 | 4705 |
-- |
| 4751 |
-- Name: classcontributor_role_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 4752 |
-- |
|
| 4753 |
|
|
| 4754 |
|
|
| 4755 |
|
|
| 4756 |
|
|
| 4757 |
-- |
|
| 4758 | 4706 |
-- Name: commclass_classpublication_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 4759 | 4707 |
-- |
| 4760 | 4708 |
|
| ... | ... | |
| 5048 | 4996 |
|
| 5049 | 4997 |
|
| 5050 | 4998 |
-- |
| 5051 |
-- Name: locationeventcontributor_role_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5052 |
-- |
|
| 5053 |
|
|
| 5054 |
|
|
| 5055 |
|
|
| 5056 |
|
|
| 5057 |
-- |
|
| 5058 | 4999 |
-- Name: locationeventsynonym_party_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5059 | 5000 |
-- |
| 5060 | 5001 |
|
| ... | ... | |
| 5070 | 5011 |
|
| 5071 | 5012 |
|
| 5072 | 5013 |
-- |
| 5073 |
-- Name: locationeventsynonym_role_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5074 |
-- |
|
| 5075 |
|
|
| 5076 |
|
|
| 5077 |
|
|
| 5078 |
|
|
| 5079 |
-- |
|
| 5080 | 5014 |
-- Name: locationeventsynonym_synonymlocationevent_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5081 | 5015 |
-- |
| 5082 | 5016 |
|
| ... | ... | |
| 5171 | 5105 |
|
| 5172 | 5106 |
|
| 5173 | 5107 |
-- |
| 5174 |
-- Name: note_role_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5175 |
-- |
|
| 5176 |
|
|
| 5177 |
ALTER TABLE note |
|
| 5178 |
ADD CONSTRAINT note_role_id FOREIGN KEY (role_id) REFERENCES role(role_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5179 |
|
|
| 5180 |
|
|
| 5181 |
-- |
|
| 5182 | 5108 |
-- Name: party_currentname_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5183 | 5109 |
-- |
| 5184 | 5110 |
|
| ... | ... | |
| 5203 | 5129 |
|
| 5204 | 5130 |
|
| 5205 | 5131 |
-- |
| 5206 |
-- Name: partymember_role_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5207 |
-- |
|
| 5208 |
|
|
| 5209 |
ALTER TABLE partymember |
|
| 5210 |
ADD CONSTRAINT partymember_role_id FOREIGN KEY (role_id) REFERENCES role(role_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5211 |
|
|
| 5212 |
|
|
| 5213 |
-- |
|
| 5214 | 5132 |
-- Name: plantconcept_plantname_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5215 | 5133 |
-- |
| 5216 | 5134 |
|
| ... | ... | |
| 5374 | 5292 |
|
| 5375 | 5293 |
|
| 5376 | 5294 |
-- |
| 5377 |
-- Name: projectcontributor_role_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5378 |
-- |
|
| 5379 |
|
|
| 5380 |
|
|
| 5381 |
|
|
| 5382 |
|
|
| 5383 |
-- |
|
| 5384 | 5295 |
-- Name: reference_referencejournal_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5385 | 5296 |
-- |
| 5386 | 5297 |
|
| ... | ... | |
| 5545 | 5456 |
|
| 5546 | 5457 |
|
| 5547 | 5458 |
-- |
| 5548 |
-- Name: taxondetermination_role_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5549 |
-- |
|
| 5550 |
|
|
| 5551 |
|
|
| 5552 |
|
|
| 5553 |
|
|
| 5554 |
-- |
|
| 5555 | 5459 |
-- Name: taxondetermination_taxonoccurrence_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5556 | 5460 |
-- |
| 5557 | 5461 |
|
Also available in: Unified diff
VegBIEN: Changed role table to a closed list