Revision 1099
Added by Aaron Marcuse-Kubitza almost 13 years ago
| vegbien.my.sql | ||
|---|---|---|
| 730 | 730 |
sourceaccessioncode text, |
| 731 | 731 |
previous_id int(11), |
| 732 | 732 |
dateaccuracy text, |
| 733 |
plotmethod_id int(11),
|
|
| 733 |
method_id int(11), |
|
| 734 | 734 |
autotaxoncover int(1), |
| 735 | 735 |
originaldata text, |
| 736 | 736 |
effortlevel text, |
| ... | ... | |
| 900 | 900 |
covermethod_id int(11), |
| 901 | 901 |
samplingfactor double precision DEFAULT 1 NOT NULL, |
| 902 | 902 |
coverbasis text, |
| 903 |
stemsamplemethod text |
|
| 903 |
stemsamplemethod text, |
|
| 904 |
shape text, |
|
| 905 |
length double precision, |
|
| 906 |
width double precision, |
|
| 907 |
radius double precision, |
|
| 908 |
area double precision, |
|
| 909 |
samplearea double precision |
|
| 904 | 910 |
); |
| 905 | 911 |
|
| 906 | 912 |
|
| ... | ... | |
| 1017 | 1023 |
|
| 1018 | 1024 |
|
| 1019 | 1025 |
-- |
| 1026 |
-- Name: COLUMN method.shape; Type: COMMENT; Schema: public; Owner: - |
|
| 1027 |
-- |
|
| 1028 |
|
|
| 1029 |
|
|
| 1030 |
|
|
| 1031 |
|
|
| 1032 |
-- |
|
| 1033 |
-- Name: COLUMN method.length; Type: COMMENT; Schema: public; Owner: - |
|
| 1034 |
-- |
|
| 1035 |
|
|
| 1036 |
|
|
| 1037 |
|
|
| 1038 |
|
|
| 1039 |
-- |
|
| 1040 |
-- Name: COLUMN method.width; Type: COMMENT; Schema: public; Owner: - |
|
| 1041 |
-- |
|
| 1042 |
|
|
| 1043 |
|
|
| 1044 |
|
|
| 1045 |
|
|
| 1046 |
-- |
|
| 1047 |
-- Name: COLUMN method.radius; Type: COMMENT; Schema: public; Owner: - |
|
| 1048 |
-- |
|
| 1049 |
|
|
| 1050 |
|
|
| 1051 |
|
|
| 1052 |
|
|
| 1053 |
-- |
|
| 1054 |
-- Name: COLUMN method.area; Type: COMMENT; Schema: public; Owner: - |
|
| 1055 |
-- |
|
| 1056 |
|
|
| 1057 |
|
|
| 1058 |
|
|
| 1059 |
|
|
| 1060 |
-- |
|
| 1061 |
-- Name: COLUMN method.samplearea; Type: COMMENT; Schema: public; Owner: - |
|
| 1062 |
-- |
|
| 1063 |
|
|
| 1064 |
|
|
| 1065 |
|
|
| 1066 |
|
|
| 1067 |
-- |
|
| 1020 | 1068 |
-- Name: method_method_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
| 1021 | 1069 |
-- |
| 1022 | 1070 |
|
| ... | ... | |
| 1486 | 1534 |
|
| 1487 | 1535 |
|
| 1488 | 1536 |
-- |
| 1489 |
-- Name: plotmethod; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 1490 |
-- |
|
| 1491 |
|
|
| 1492 |
CREATE TABLE plotmethod ( |
|
| 1493 |
plotmethod_id int(11) NOT NULL, |
|
| 1494 |
reference_id int(11), |
|
| 1495 |
name text NOT NULL, |
|
| 1496 |
description text, |
|
| 1497 |
accessioncode text, |
|
| 1498 |
method_id int(11), |
|
| 1499 |
shape text, |
|
| 1500 |
length double precision, |
|
| 1501 |
width double precision, |
|
| 1502 |
radius double precision, |
|
| 1503 |
area double precision, |
|
| 1504 |
samplearea double precision |
|
| 1505 |
); |
|
| 1506 |
|
|
| 1507 |
|
|
| 1508 |
-- |
|
| 1509 |
-- Name: TABLE plotmethod; Type: COMMENT; Schema: public; Owner: - |
|
| 1510 |
-- |
|
| 1511 |
|
|
| 1512 |
|
|
| 1513 |
|
|
| 1514 |
|
|
| 1515 |
-- |
|
| 1516 |
-- Name: COLUMN plotmethod.name; Type: COMMENT; Schema: public; Owner: - |
|
| 1517 |
-- |
|
| 1518 |
|
|
| 1519 |
|
|
| 1520 |
|
|
| 1521 |
|
|
| 1522 |
-- |
|
| 1523 |
-- Name: COLUMN plotmethod.description; Type: COMMENT; Schema: public; Owner: - |
|
| 1524 |
-- |
|
| 1525 |
|
|
| 1526 |
|
|
| 1527 |
|
|
| 1528 |
|
|
| 1529 |
-- |
|
| 1530 |
-- Name: COLUMN plotmethod.shape; Type: COMMENT; Schema: public; Owner: - |
|
| 1531 |
-- |
|
| 1532 |
|
|
| 1533 |
|
|
| 1534 |
|
|
| 1535 |
|
|
| 1536 |
-- |
|
| 1537 |
-- Name: COLUMN plotmethod.length; Type: COMMENT; Schema: public; Owner: - |
|
| 1538 |
-- |
|
| 1539 |
|
|
| 1540 |
|
|
| 1541 |
|
|
| 1542 |
|
|
| 1543 |
-- |
|
| 1544 |
-- Name: COLUMN plotmethod.width; Type: COMMENT; Schema: public; Owner: - |
|
| 1545 |
-- |
|
| 1546 |
|
|
| 1547 |
|
|
| 1548 |
|
|
| 1549 |
|
|
| 1550 |
-- |
|
| 1551 |
-- Name: COLUMN plotmethod.radius; Type: COMMENT; Schema: public; Owner: - |
|
| 1552 |
-- |
|
| 1553 |
|
|
| 1554 |
|
|
| 1555 |
|
|
| 1556 |
|
|
| 1557 |
-- |
|
| 1558 |
-- Name: COLUMN plotmethod.area; Type: COMMENT; Schema: public; Owner: - |
|
| 1559 |
-- |
|
| 1560 |
|
|
| 1561 |
|
|
| 1562 |
|
|
| 1563 |
|
|
| 1564 |
-- |
|
| 1565 |
-- Name: COLUMN plotmethod.samplearea; Type: COMMENT; Schema: public; Owner: - |
|
| 1566 |
-- |
|
| 1567 |
|
|
| 1568 |
|
|
| 1569 |
|
|
| 1570 |
|
|
| 1571 |
-- |
|
| 1572 |
-- Name: plotmethod_plotmethod_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
|
| 1573 |
-- |
|
| 1574 |
|
|
| 1575 |
|
|
| 1576 |
|
|
| 1577 |
|
|
| 1578 |
-- |
|
| 1579 |
-- Name: plotmethod_plotmethod_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - |
|
| 1580 |
-- |
|
| 1581 |
|
|
| 1582 |
|
|
| 1583 |
|
|
| 1584 |
|
|
| 1585 |
-- |
|
| 1586 | 1537 |
-- Name: project; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 1587 | 1538 |
-- |
| 1588 | 1539 |
|
| ... | ... | |
| 2612 | 2563 |
|
| 2613 | 2564 |
|
| 2614 | 2565 |
-- |
| 2615 |
-- Name: plotmethod_id; Type: DEFAULT; Schema: public; Owner: - |
|
| 2616 |
-- |
|
| 2617 |
|
|
| 2618 |
|
|
| 2619 |
|
|
| 2620 |
|
|
| 2621 |
-- |
|
| 2622 | 2566 |
-- Name: project_id; Type: DEFAULT; Schema: public; Owner: - |
| 2623 | 2567 |
-- |
| 2624 | 2568 |
|
| ... | ... | |
| 3180 | 3124 |
|
| 3181 | 3125 |
|
| 3182 | 3126 |
-- |
| 3183 |
-- Name: plotmethod_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3184 |
-- |
|
| 3185 |
|
|
| 3186 |
ALTER TABLE plotmethod |
|
| 3187 |
ADD CONSTRAINT plotmethod_pkey PRIMARY KEY (plotmethod_id); |
|
| 3188 |
|
|
| 3189 |
|
|
| 3190 |
-- |
|
| 3191 | 3127 |
-- Name: project_keys; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 3192 | 3128 |
-- |
| 3193 | 3129 |
|
| ... | ... | |
| 3304 | 3240 |
-- |
| 3305 | 3241 |
|
| 3306 | 3242 |
ALTER TABLE specimenreplicate |
| 3307 |
ADD CONSTRAINT specimenreplicate_keys_accessioncode UNIQUE (datasource_id, collectioncode_dwc, sourceaccessioncode);
|
|
| 3243 |
ADD CONSTRAINT specimenreplicate_keys_accessioncode UNIQUE (datasource_id, sourceaccessioncode); |
|
| 3308 | 3244 |
|
| 3309 | 3245 |
|
| 3310 | 3246 |
-- |
| ... | ... | |
| 3320 | 3256 |
-- |
| 3321 | 3257 |
|
| 3322 | 3258 |
ALTER TABLE specimenreplicate |
| 3323 |
ADD CONSTRAINT specimenreplicate_keys_collectionnumber UNIQUE (datasource_id, collectioncode_dwc, collectionnumber);
|
|
| 3259 |
ADD CONSTRAINT specimenreplicate_keys_collectionnumber UNIQUE (datasource_id, collectionnumber); |
|
| 3324 | 3260 |
|
| 3325 | 3261 |
|
| 3326 | 3262 |
-- |
| ... | ... | |
| 3847 | 3783 |
-- Name: fki_locationevent_method_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 3848 | 3784 |
-- |
| 3849 | 3785 |
|
| 3850 |
CREATE INDEX fki_locationevent_method_id ON locationevent (plotmethod_id);
|
|
| 3786 |
CREATE INDEX fki_locationevent_method_id ON locationevent (method_id); |
|
| 3851 | 3787 |
|
| 3852 | 3788 |
|
| 3853 | 3789 |
-- |
| ... | ... | |
| 3900 | 3836 |
|
| 3901 | 3837 |
|
| 3902 | 3838 |
-- |
| 3903 |
-- Name: fki_plotmethod_reference_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 3904 |
-- |
|
| 3905 |
|
|
| 3906 |
CREATE INDEX fki_plotmethod_reference_id ON plotmethod (reference_id); |
|
| 3907 |
|
|
| 3908 |
|
|
| 3909 |
-- |
|
| 3910 | 3839 |
-- Name: fki_project_datasource_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 3911 | 3840 |
-- |
| 3912 | 3841 |
|
| ... | ... | |
| 4397 | 4326 |
|
| 4398 | 4327 |
|
| 4399 | 4328 |
-- |
| 4400 |
-- Name: plotmethod_accessioncode; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4401 |
-- |
|
| 4402 |
|
|
| 4403 |
CREATE UNIQUE INDEX plotmethod_accessioncode ON plotmethod (accessioncode); |
|
| 4404 |
|
|
| 4405 |
|
|
| 4406 |
-- |
|
| 4407 |
-- Name: plotmethod_keys; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4408 |
-- |
|
| 4409 |
|
|
| 4410 |
|
|
| 4411 |
|
|
| 4412 |
|
|
| 4413 |
-- |
|
| 4414 | 4329 |
-- Name: project_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4415 | 4330 |
-- |
| 4416 | 4331 |
|
| ... | ... | |
| 4959 | 4874 |
|
| 4960 | 4875 |
|
| 4961 | 4876 |
-- |
| 4962 |
-- Name: locationevent_parent_id; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4877 |
-- Name: locationevent_method_id; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4963 | 4878 |
-- |
| 4964 | 4879 |
|
| 4965 | 4880 |
ALTER TABLE locationevent |
| 4966 |
ADD CONSTRAINT locationevent_parent_id FOREIGN KEY (parent_id) REFERENCES locationevent(locationevent_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4881 |
ADD CONSTRAINT locationevent_method_id FOREIGN KEY (method_id) REFERENCES method(method_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4967 | 4882 |
|
| 4968 | 4883 |
|
| 4969 | 4884 |
-- |
| 4970 |
-- Name: locationevent_plotmethod_id; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4885 |
-- Name: locationevent_parent_id; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 4971 | 4886 |
-- |
| 4972 | 4887 |
|
| 4973 | 4888 |
ALTER TABLE locationevent |
| 4974 |
ADD CONSTRAINT locationevent_plotmethod_id FOREIGN KEY (plotmethod_id) REFERENCES plotmethod(plotmethod_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4889 |
ADD CONSTRAINT locationevent_parent_id FOREIGN KEY (parent_id) REFERENCES locationevent(locationevent_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 4975 | 4890 |
|
| 4976 | 4891 |
|
| 4977 | 4892 |
-- |
| ... | ... | |
| 5301 | 5216 |
|
| 5302 | 5217 |
|
| 5303 | 5218 |
-- |
| 5304 |
-- Name: plotmethod_method_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5305 |
-- |
|
| 5306 |
|
|
| 5307 |
ALTER TABLE plotmethod |
|
| 5308 |
ADD CONSTRAINT plotmethod_method_id FOREIGN KEY (method_id) REFERENCES method(method_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5309 |
|
|
| 5310 |
|
|
| 5311 |
-- |
|
| 5312 |
-- Name: plotmethod_reference_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5313 |
-- |
|
| 5314 |
|
|
| 5315 |
|
|
| 5316 |
|
|
| 5317 |
|
|
| 5318 |
-- |
|
| 5319 | 5219 |
-- Name: project_datasource_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5320 | 5220 |
-- |
| 5321 | 5221 |
|
Also available in: Unified diff
VegBIEN: Moved plotmethod fields to method because they can also apply to strata. Removed no longer used plotmethod table.