Revision 592
Added by Aaron Marcuse-Kubitza almost 13 years ago
| schemas/vegbien.for_ERD.my.sql | ||
|---|---|---|
| 58 | 58 |
ON UPDATE CASCADE ON DELETE CASCADE |
| 59 | 59 |
); |
| 60 | 60 |
|
| 61 |
CREATE TABLE stratummethod |
|
| 62 |
( |
|
| 63 |
stratummethod_id int(11) NOT NULL AUTO_INCREMENT, |
|
| 64 |
reference_id int(11), |
|
| 65 |
stratummethodname character varying(30) NOT NULL, |
|
| 66 |
stratummethoddescription text, |
|
| 67 |
stratumassignment character varying(50), |
|
| 68 |
accessioncode character varying(255), |
|
| 69 |
CONSTRAINT stratummethod_pkey PRIMARY KEY (stratummethod_id ), |
|
| 70 |
CONSTRAINT stratummethod_reference_id FOREIGN KEY (reference_id) |
|
| 71 |
REFERENCES reference (reference_id) MATCH SIMPLE |
|
| 72 |
ON UPDATE CASCADE ON DELETE CASCADE |
|
| 73 |
); |
|
| 74 |
|
|
| 75 |
CREATE TABLE stratumtype |
|
| 76 |
( |
|
| 77 |
stratumtype_id int(11) NOT NULL AUTO_INCREMENT, |
|
| 78 |
stratummethod_id int(11) NOT NULL, |
|
| 79 |
stratumindex character varying(10), |
|
| 80 |
stratumname character varying(30), |
|
| 81 |
stratumdescription text, |
|
| 82 |
CONSTRAINT stratumtype_pkey PRIMARY KEY (stratumtype_id ), |
|
| 83 |
CONSTRAINT stratumtype_stratummethod_id FOREIGN KEY (stratummethod_id) |
|
| 84 |
REFERENCES stratummethod (stratummethod_id) MATCH SIMPLE |
|
| 85 |
ON UPDATE CASCADE ON DELETE CASCADE |
|
| 86 |
); |
|
| 87 |
|
|
| 61 | 88 |
CREATE TABLE namedplace |
| 62 | 89 |
( |
| 63 | 90 |
namedplace_id int(11) NOT NULL AUTO_INCREMENT, |
| ... | ... | |
| 353 | 380 |
CONSTRAINT stratum_pkey PRIMARY KEY (stratum_id ), |
| 354 | 381 |
CONSTRAINT stratum_locationevent_id FOREIGN KEY (locationevent_id) |
| 355 | 382 |
REFERENCES locationevent (locationevent_id) MATCH SIMPLE |
| 383 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 384 |
CONSTRAINT stratum_stratumtype_id FOREIGN KEY (stratumtype_id) |
|
| 385 |
REFERENCES stratumtype (stratumtype_id) MATCH SIMPLE |
|
| 356 | 386 |
ON UPDATE CASCADE ON DELETE CASCADE |
| 357 | 387 |
); |
| 358 | 388 |
|
| ... | ... | |
| 367 | 397 |
CONSTRAINT sizeclass_pkey PRIMARY KEY (sizeclass_id ) |
| 368 | 398 |
); |
| 369 | 399 |
|
| 370 |
CREATE TABLE taxonbinmethod -- Defines a set of aggregate measurements. Used by aggregateoccurrence to define how measurements were aggregated.
|
|
| 400 |
CREATE TABLE taxonbinmethod |
|
| 371 | 401 |
( |
| 372 | 402 |
taxonbinmethod_id int(11) NOT NULL, |
| 373 |
label character varying(255) NOT NULL DEFAULT '',
|
|
| 374 |
stratum_id int(11), |
|
| 403 |
label character varying(255), |
|
| 404 |
stratumtype_id int(11),
|
|
| 375 | 405 |
sizeclass_id int(11), |
| 376 | 406 |
coverindex_id int(11), |
| 377 | 407 |
accessioncode character varying(255), |
| ... | ... | |
| 379 | 409 |
CONSTRAINT taxonbinmethod_sizeclass_id FOREIGN KEY (sizeclass_id) |
| 380 | 410 |
REFERENCES sizeclass (sizeclass_id) MATCH SIMPLE |
| 381 | 411 |
ON UPDATE CASCADE ON DELETE CASCADE, |
| 382 |
CONSTRAINT taxonbinmethod_keys UNIQUE (label , stratum_id , sizeclass_id , coverindex_id ) |
|
| 412 |
CONSTRAINT taxonbinmethod_stratumtype_id FOREIGN KEY (stratumtype_id) |
|
| 413 |
REFERENCES stratumtype (stratumtype_id) MATCH SIMPLE |
|
| 414 |
ON UPDATE CASCADE ON DELETE CASCADE |
|
| 383 | 415 |
); |
| schemas/vegbien.for_wiki.sql | ||
|---|---|---|
| 161 | 161 |
accessioncode character varying(255), |
| 162 | 162 |
); |
| 163 | 163 |
|
| 164 |
CREATE TABLE taxonbinmethod -- Defines a set of aggregate measurements. Used by aggregateoccurrence to define how measurements were aggregated.
|
|
| 164 |
CREATE TABLE taxonbinmethod |
|
| 165 | 165 |
( |
| 166 | 166 |
taxonbinmethod_id integer NOT NULL, |
| 167 |
label character varying(255) NOT NULL DEFAULT ''::character varying,
|
|
| 168 |
stratum_id integer, |
|
| 167 |
label character varying(255), |
|
| 168 |
stratumtype_id integer,
|
|
| 169 | 169 |
sizeclass_id integer, |
| 170 | 170 |
coverindex_id integer, |
| 171 | 171 |
accessioncode character varying(255), |
| schemas/vegbien.for_ERD.sql | ||
|---|---|---|
| 58 | 58 |
ON UPDATE CASCADE ON DELETE CASCADE |
| 59 | 59 |
); |
| 60 | 60 |
|
| 61 |
CREATE TABLE stratummethod |
|
| 62 |
( |
|
| 63 |
stratummethod_id serial NOT NULL, |
|
| 64 |
reference_id integer, |
|
| 65 |
stratummethodname character varying(30) NOT NULL, |
|
| 66 |
stratummethoddescription text, |
|
| 67 |
stratumassignment character varying(50), |
|
| 68 |
accessioncode character varying(255), |
|
| 69 |
CONSTRAINT stratummethod_pkey PRIMARY KEY (stratummethod_id ), |
|
| 70 |
CONSTRAINT stratummethod_reference_id FOREIGN KEY (reference_id) |
|
| 71 |
REFERENCES reference (reference_id) MATCH SIMPLE |
|
| 72 |
ON UPDATE CASCADE ON DELETE CASCADE |
|
| 73 |
); |
|
| 74 |
|
|
| 75 |
CREATE TABLE stratumtype |
|
| 76 |
( |
|
| 77 |
stratumtype_id serial NOT NULL, |
|
| 78 |
stratummethod_id integer NOT NULL, |
|
| 79 |
stratumindex character varying(10), |
|
| 80 |
stratumname character varying(30), |
|
| 81 |
stratumdescription text, |
|
| 82 |
CONSTRAINT stratumtype_pkey PRIMARY KEY (stratumtype_id ), |
|
| 83 |
CONSTRAINT stratumtype_stratummethod_id FOREIGN KEY (stratummethod_id) |
|
| 84 |
REFERENCES stratummethod (stratummethod_id) MATCH SIMPLE |
|
| 85 |
ON UPDATE CASCADE ON DELETE CASCADE |
|
| 86 |
); |
|
| 87 |
|
|
| 61 | 88 |
CREATE TABLE namedplace |
| 62 | 89 |
( |
| 63 | 90 |
namedplace_id serial NOT NULL, |
| ... | ... | |
| 353 | 380 |
CONSTRAINT stratum_pkey PRIMARY KEY (stratum_id ), |
| 354 | 381 |
CONSTRAINT stratum_locationevent_id FOREIGN KEY (locationevent_id) |
| 355 | 382 |
REFERENCES locationevent (locationevent_id) MATCH SIMPLE |
| 383 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 384 |
CONSTRAINT stratum_stratumtype_id FOREIGN KEY (stratumtype_id) |
|
| 385 |
REFERENCES stratumtype (stratumtype_id) MATCH SIMPLE |
|
| 356 | 386 |
ON UPDATE CASCADE ON DELETE CASCADE |
| 357 | 387 |
); |
| 358 | 388 |
|
| ... | ... | |
| 367 | 397 |
CONSTRAINT sizeclass_pkey PRIMARY KEY (sizeclass_id ) |
| 368 | 398 |
); |
| 369 | 399 |
|
| 370 |
CREATE TABLE taxonbinmethod -- Defines a set of aggregate measurements. Used by aggregateoccurrence to define how measurements were aggregated.
|
|
| 400 |
CREATE TABLE taxonbinmethod |
|
| 371 | 401 |
( |
| 372 | 402 |
taxonbinmethod_id integer NOT NULL, |
| 373 |
label character varying(255) NOT NULL DEFAULT ''::character varying,
|
|
| 374 |
stratum_id integer, |
|
| 403 |
label character varying(255), |
|
| 404 |
stratumtype_id integer,
|
|
| 375 | 405 |
sizeclass_id integer, |
| 376 | 406 |
coverindex_id integer, |
| 377 | 407 |
accessioncode character varying(255), |
| ... | ... | |
| 379 | 409 |
CONSTRAINT taxonbinmethod_sizeclass_id FOREIGN KEY (sizeclass_id) |
| 380 | 410 |
REFERENCES sizeclass (sizeclass_id) MATCH SIMPLE |
| 381 | 411 |
ON UPDATE CASCADE ON DELETE CASCADE, |
| 382 |
CONSTRAINT taxonbinmethod_keys UNIQUE (label , stratum_id , sizeclass_id , coverindex_id ) |
|
| 412 |
CONSTRAINT taxonbinmethod_stratumtype_id FOREIGN KEY (stratumtype_id) |
|
| 413 |
REFERENCES stratumtype (stratumtype_id) MATCH SIMPLE |
|
| 414 |
ON UPDATE CASCADE ON DELETE CASCADE |
|
| 383 | 415 |
); |
| schemas/vegbien.sql | ||
|---|---|---|
| 2363 | 2363 |
stratum_id integer NOT NULL, |
| 2364 | 2364 |
locationevent_id integer NOT NULL, |
| 2365 | 2365 |
stratumtype_id integer NOT NULL, |
| 2366 |
stratummethod_id integer, |
|
| 2367 | 2366 |
stratumname character varying(30), |
| 2368 | 2367 |
stratumheight double precision, |
| 2369 | 2368 |
stratumbase double precision, |
| ... | ... | |
| 2497 | 2496 |
CREATE TABLE taxonbinmethod ( |
| 2498 | 2497 |
taxonbinmethod_id integer NOT NULL, |
| 2499 | 2498 |
label character varying(255), |
| 2500 |
stratum_id integer, |
|
| 2499 |
stratumtype_id integer,
|
|
| 2501 | 2500 |
sizeclass_id integer, |
| 2502 | 2501 |
coverindex_id integer, |
| 2503 | 2502 |
accessioncode character varying(255) |
| ... | ... | |
| 5059 | 5058 |
|
| 5060 | 5059 |
|
| 5061 | 5060 |
-- |
| 5061 |
-- Name: fki_taxonbinmethod_stratumtype_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 5062 |
-- |
|
| 5063 |
|
|
| 5064 |
CREATE INDEX fki_taxonbinmethod_stratumtype_id ON taxonbinmethod USING btree (stratumtype_id); |
|
| 5065 |
|
|
| 5066 |
|
|
| 5067 |
-- |
|
| 5062 | 5068 |
-- Name: graphic_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 5063 | 5069 |
-- |
| 5064 | 5070 |
|
| ... | ... | |
| 5584 | 5590 |
|
| 5585 | 5591 |
|
| 5586 | 5592 |
-- |
| 5587 |
-- Name: stratum_stratummethod_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 5588 |
-- |
|
| 5589 |
|
|
| 5590 |
CREATE INDEX stratum_stratummethod_id_x ON stratum USING btree (stratummethod_id); |
|
| 5591 |
|
|
| 5592 |
|
|
| 5593 |
-- |
|
| 5594 | 5593 |
-- Name: stratum_stratumtype_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 5595 | 5594 |
-- |
| 5596 | 5595 |
|
| ... | ... | |
| 5636 | 5635 |
-- Name: taxonbinmethod_keys; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 5637 | 5636 |
-- |
| 5638 | 5637 |
|
| 5639 |
CREATE UNIQUE INDEX taxonbinmethod_keys ON taxonbinmethod USING btree ((COALESCE(label, ''::character varying)), stratum_id, sizeclass_id, coverindex_id); |
|
| 5638 |
CREATE UNIQUE INDEX taxonbinmethod_keys ON taxonbinmethod USING btree ((COALESCE(label, ''::character varying)), stratumtype_id, sizeclass_id, coverindex_id);
|
|
| 5640 | 5639 |
|
| 5641 | 5640 |
|
| 5642 | 5641 |
-- |
| ... | ... | |
| 6588 | 6587 |
|
| 6589 | 6588 |
|
| 6590 | 6589 |
-- |
| 6591 |
-- Name: stratum_stratummethod_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 6592 |
-- |
|
| 6593 |
|
|
| 6594 |
ALTER TABLE ONLY stratum |
|
| 6595 |
ADD CONSTRAINT stratum_stratummethod_id FOREIGN KEY (stratummethod_id) REFERENCES stratummethod(stratummethod_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 6596 |
|
|
| 6597 |
|
|
| 6598 |
-- |
|
| 6599 | 6590 |
-- Name: stratum_stratumtype_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 6600 | 6591 |
-- |
| 6601 | 6592 |
|
| ... | ... | |
| 6644 | 6635 |
|
| 6645 | 6636 |
|
| 6646 | 6637 |
-- |
| 6638 |
-- Name: taxonbinmethod_stratumtype_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 6639 |
-- |
|
| 6640 |
|
|
| 6641 |
ALTER TABLE ONLY taxonbinmethod |
|
| 6642 |
ADD CONSTRAINT taxonbinmethod_stratumtype_id FOREIGN KEY (stratumtype_id) REFERENCES stratumtype(stratumtype_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 6643 |
|
|
| 6644 |
|
|
| 6645 |
-- |
|
| 6647 | 6646 |
-- Name: taxondetermination_party_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 6648 | 6647 |
-- |
| 6649 | 6648 |
|
Also available in: Unified diff
vegbien.sql: taxonbinmethod points to stratumtype instead of stratum because stratumtype is a method table, but stratum is a measurements table. stratum does not point directly to stratummethod because it points to it via stratumtype.