Revision 679
Added by Aaron Marcuse-Kubitza almost 13 years ago
| schemas/vegbien.for_ERD.my.sql | ||
|---|---|---|
| 212 | 212 |
( |
| 213 | 213 |
aggregateoccurrence_id int(11) NOT NULL AUTO_INCREMENT, |
| 214 | 214 |
taxonoccurrence_id int(11) NOT NULL, |
| 215 |
taxonbinmethod_id int(11), |
|
| 216 | 215 |
cover double precision, |
| 217 | 216 |
basalarea double precision, |
| 218 | 217 |
biomass double precision, |
| ... | ... | |
| 238 | 237 |
CONSTRAINT aggregateoccurrence_stratum_id FOREIGN KEY (stratum_id) |
| 239 | 238 |
REFERENCES stratum (stratum_id) MATCH SIMPLE |
| 240 | 239 |
ON UPDATE CASCADE ON DELETE CASCADE, |
| 241 |
CONSTRAINT aggregateoccurrence_taxonbinmethod_id FOREIGN KEY (taxonbinmethod_id) |
|
| 242 |
REFERENCES taxonbinmethod (taxonbinmethod_id) MATCH SIMPLE |
|
| 243 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 244 | 240 |
CONSTRAINT aggregateoccurrence_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id) |
| 245 | 241 |
REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE |
| 246 | 242 |
ON UPDATE CASCADE ON DELETE CASCADE, |
| 247 |
CONSTRAINT aggregateoccurrence_keys_accessioncode UNIQUE (taxonoccurrence_id , sourceaccessioncode ), |
|
| 248 |
CONSTRAINT aggregateoccurrence_keys_method UNIQUE (taxonoccurrence_id , taxonbinmethod_id ) |
|
| 243 |
CONSTRAINT aggregateoccurrence_keys_accessioncode UNIQUE (taxonoccurrence_id , sourceaccessioncode ) |
|
| 249 | 244 |
); |
| 250 | 245 |
|
| 251 | 246 |
CREATE TABLE plant -- A physical, tagged plant. |
| ... | ... | |
| 433 | 428 |
accessioncode character varying(255), |
| 434 | 429 |
CONSTRAINT sizeclass_pkey PRIMARY KEY (sizeclass_id ) |
| 435 | 430 |
); |
| 436 |
|
|
| 437 |
CREATE TABLE taxonbinmethod |
|
| 438 |
( |
|
| 439 |
taxonbinmethod_id int(11) NOT NULL, |
|
| 440 |
label character varying(255), |
|
| 441 |
stratumtype_id int(11), |
|
| 442 |
sizeclass_id int(11), |
|
| 443 |
coverindex_id int(11), |
|
| 444 |
accessioncode character varying(255), |
|
| 445 |
CONSTRAINT taxonbinmethod_pkey PRIMARY KEY (taxonbinmethod_id ), |
|
| 446 |
CONSTRAINT taxonbinmethod_sizeclass_id FOREIGN KEY (sizeclass_id) |
|
| 447 |
REFERENCES sizeclass (sizeclass_id) MATCH SIMPLE |
|
| 448 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 449 |
CONSTRAINT taxonbinmethod_stratumtype_id FOREIGN KEY (stratumtype_id) |
|
| 450 |
REFERENCES stratumtype (stratumtype_id) MATCH SIMPLE |
|
| 451 |
ON UPDATE CASCADE ON DELETE CASCADE |
|
| 452 |
); |
|
| schemas/vegbien.for_wiki.sql | ||
|---|---|---|
| 46 | 46 |
( |
| 47 | 47 |
aggregateoccurrence_id serial NOT NULL, |
| 48 | 48 |
taxonoccurrence_id integer NOT NULL, |
| 49 |
taxonbinmethod_id integer, |
|
| 50 | 49 |
cover double precision, |
| 51 | 50 |
basalarea double precision, |
| 52 | 51 |
biomass double precision, |
| ... | ... | |
| 180 | 179 |
maxheight double precision, |
| 181 | 180 |
accessioncode character varying(255), |
| 182 | 181 |
); |
| 183 |
|
|
| 184 |
CREATE TABLE taxonbinmethod |
|
| 185 |
( |
|
| 186 |
taxonbinmethod_id integer NOT NULL, |
|
| 187 |
label character varying(255), |
|
| 188 |
stratumtype_id integer, |
|
| 189 |
sizeclass_id integer, |
|
| 190 |
coverindex_id integer, |
|
| 191 |
accessioncode character varying(255), |
|
| 192 |
); |
|
| schemas/vegbien_empty.sql | ||
|---|---|---|
| 68 | 68 |
TRUNCATE stratummethod CASCADE; |
| 69 | 69 |
TRUNCATE stratumtype CASCADE; |
| 70 | 70 |
TRUNCATE taxonalt CASCADE; |
| 71 |
TRUNCATE taxonbinmethod CASCADE; |
|
| 72 | 71 |
TRUNCATE taxondetermination CASCADE; |
| 73 | 72 |
TRUNCATE taxonoccurrence CASCADE; |
| 74 | 73 |
TRUNCATE telephone CASCADE; |
| schemas/vegbien.for_ERD.sql | ||
|---|---|---|
| 212 | 212 |
( |
| 213 | 213 |
aggregateoccurrence_id serial NOT NULL, |
| 214 | 214 |
taxonoccurrence_id integer NOT NULL, |
| 215 |
taxonbinmethod_id integer, |
|
| 216 | 215 |
cover double precision, |
| 217 | 216 |
basalarea double precision, |
| 218 | 217 |
biomass double precision, |
| ... | ... | |
| 238 | 237 |
CONSTRAINT aggregateoccurrence_stratum_id FOREIGN KEY (stratum_id) |
| 239 | 238 |
REFERENCES stratum (stratum_id) MATCH SIMPLE |
| 240 | 239 |
ON UPDATE CASCADE ON DELETE CASCADE, |
| 241 |
CONSTRAINT aggregateoccurrence_taxonbinmethod_id FOREIGN KEY (taxonbinmethod_id) |
|
| 242 |
REFERENCES taxonbinmethod (taxonbinmethod_id) MATCH SIMPLE |
|
| 243 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 244 | 240 |
CONSTRAINT aggregateoccurrence_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id) |
| 245 | 241 |
REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE |
| 246 | 242 |
ON UPDATE CASCADE ON DELETE CASCADE, |
| 247 |
CONSTRAINT aggregateoccurrence_keys_accessioncode UNIQUE (taxonoccurrence_id , sourceaccessioncode ), |
|
| 248 |
CONSTRAINT aggregateoccurrence_keys_method UNIQUE (taxonoccurrence_id , taxonbinmethod_id ) |
|
| 243 |
CONSTRAINT aggregateoccurrence_keys_accessioncode UNIQUE (taxonoccurrence_id , sourceaccessioncode ) |
|
| 249 | 244 |
); |
| 250 | 245 |
|
| 251 | 246 |
CREATE TABLE plant -- A physical, tagged plant. |
| ... | ... | |
| 433 | 428 |
accessioncode character varying(255), |
| 434 | 429 |
CONSTRAINT sizeclass_pkey PRIMARY KEY (sizeclass_id ) |
| 435 | 430 |
); |
| 436 |
|
|
| 437 |
CREATE TABLE taxonbinmethod |
|
| 438 |
( |
|
| 439 |
taxonbinmethod_id integer NOT NULL, |
|
| 440 |
label character varying(255), |
|
| 441 |
stratumtype_id integer, |
|
| 442 |
sizeclass_id integer, |
|
| 443 |
coverindex_id integer, |
|
| 444 |
accessioncode character varying(255), |
|
| 445 |
CONSTRAINT taxonbinmethod_pkey PRIMARY KEY (taxonbinmethod_id ), |
|
| 446 |
CONSTRAINT taxonbinmethod_sizeclass_id FOREIGN KEY (sizeclass_id) |
|
| 447 |
REFERENCES sizeclass (sizeclass_id) MATCH SIMPLE |
|
| 448 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 449 |
CONSTRAINT taxonbinmethod_stratumtype_id FOREIGN KEY (stratumtype_id) |
|
| 450 |
REFERENCES stratumtype (stratumtype_id) MATCH SIMPLE |
|
| 451 |
ON UPDATE CASCADE ON DELETE CASCADE |
|
| 452 |
); |
|
| schemas/vegbien.sql | ||
|---|---|---|
| 78 | 78 |
CREATE TABLE aggregateoccurrence ( |
| 79 | 79 |
aggregateoccurrence_id integer NOT NULL, |
| 80 | 80 |
taxonoccurrence_id integer NOT NULL, |
| 81 |
taxonbinmethod_id integer, |
|
| 82 | 81 |
cover double precision, |
| 83 | 82 |
basalarea double precision, |
| 84 | 83 |
biomass double precision, |
| ... | ... | |
| 2615 | 2614 |
|
| 2616 | 2615 |
|
| 2617 | 2616 |
-- |
| 2618 |
-- Name: taxonbinmethod; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 2619 |
-- |
|
| 2620 |
|
|
| 2621 |
CREATE TABLE taxonbinmethod ( |
|
| 2622 |
taxonbinmethod_id integer NOT NULL, |
|
| 2623 |
label character varying(255), |
|
| 2624 |
stratumtype_id integer, |
|
| 2625 |
sizeclass_id integer, |
|
| 2626 |
coverindex_id integer, |
|
| 2627 |
accessioncode character varying(255) |
|
| 2628 |
); |
|
| 2629 |
|
|
| 2630 |
|
|
| 2631 |
-- |
|
| 2632 |
-- Name: TABLE taxonbinmethod; Type: COMMENT; Schema: public; Owner: - |
|
| 2633 |
-- |
|
| 2634 |
|
|
| 2635 |
COMMENT ON TABLE taxonbinmethod IS 'Defines a set of aggregate measurements. Used by aggregateoccurrence to define how measurements were aggregated.'; |
|
| 2636 |
|
|
| 2637 |
|
|
| 2638 |
-- |
|
| 2639 | 2617 |
-- Name: taxondetermination; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 2640 | 2618 |
-- |
| 2641 | 2619 |
|
| ... | ... | |
| 3928 | 3906 |
|
| 3929 | 3907 |
|
| 3930 | 3908 |
-- |
| 3931 |
-- Name: aggregateoccurrence_keys_method; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3932 |
-- |
|
| 3933 |
|
|
| 3934 |
ALTER TABLE ONLY aggregateoccurrence |
|
| 3935 |
ADD CONSTRAINT aggregateoccurrence_keys_method UNIQUE (taxonoccurrence_id, taxonbinmethod_id); |
|
| 3936 |
|
|
| 3937 |
|
|
| 3938 |
-- |
|
| 3939 | 3909 |
-- Name: aggregateoccurrence_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 3940 | 3910 |
-- |
| 3941 | 3911 |
|
| ... | ... | |
| 4640 | 4610 |
|
| 4641 | 4611 |
|
| 4642 | 4612 |
-- |
| 4643 |
-- Name: taxonbinmethod_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 4644 |
-- |
|
| 4645 |
|
|
| 4646 |
ALTER TABLE ONLY taxonbinmethod |
|
| 4647 |
ADD CONSTRAINT taxonbinmethod_pkey PRIMARY KEY (taxonbinmethod_id); |
|
| 4648 |
|
|
| 4649 |
|
|
| 4650 |
-- |
|
| 4651 | 4613 |
-- Name: taxondetermination_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 4652 | 4614 |
-- |
| 4653 | 4615 |
|
| ... | ... | |
| 4822 | 4784 |
|
| 4823 | 4785 |
|
| 4824 | 4786 |
-- |
| 4825 |
-- Name: aggregateoccurrence_taxonbinmethod_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4826 |
-- |
|
| 4827 |
|
|
| 4828 |
CREATE INDEX aggregateoccurrence_taxonbinmethod_id_x ON aggregateoccurrence USING btree (taxonbinmethod_id); |
|
| 4829 |
|
|
| 4830 |
|
|
| 4831 |
-- |
|
| 4832 | 4787 |
-- Name: aggregateoccurrence_taxonoccurrence_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4833 | 4788 |
-- |
| 4834 | 4789 |
|
| ... | ... | |
| 5242 | 5197 |
|
| 5243 | 5198 |
|
| 5244 | 5199 |
-- |
| 5245 |
-- Name: fki_taxonbinmethod_sizeclass_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 5246 |
-- |
|
| 5247 |
|
|
| 5248 |
CREATE INDEX fki_taxonbinmethod_sizeclass_id ON taxonbinmethod USING btree (sizeclass_id); |
|
| 5249 |
|
|
| 5250 |
|
|
| 5251 |
-- |
|
| 5252 |
-- Name: fki_taxonbinmethod_stratumtype_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 5253 |
-- |
|
| 5254 |
|
|
| 5255 |
CREATE INDEX fki_taxonbinmethod_stratumtype_id ON taxonbinmethod USING btree (stratumtype_id); |
|
| 5256 |
|
|
| 5257 |
|
|
| 5258 |
-- |
|
| 5259 | 5200 |
-- Name: graphic_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 5260 | 5201 |
-- |
| 5261 | 5202 |
|
| ... | ... | |
| 5837 | 5778 |
|
| 5838 | 5779 |
|
| 5839 | 5780 |
-- |
| 5840 |
-- Name: taxonbinmethod_keys; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 5841 |
-- |
|
| 5842 |
|
|
| 5843 |
CREATE UNIQUE INDEX taxonbinmethod_keys ON taxonbinmethod USING btree ((COALESCE(label, ''::character varying)), stratumtype_id, sizeclass_id, coverindex_id); |
|
| 5844 |
|
|
| 5845 |
|
|
| 5846 |
-- |
|
| 5847 | 5781 |
-- Name: taxondetermination_accessioncode_index; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 5848 | 5782 |
-- |
| 5849 | 5783 |
|
| ... | ... | |
| 6087 | 6021 |
|
| 6088 | 6022 |
|
| 6089 | 6023 |
-- |
| 6090 |
-- Name: aggregateoccurrence_taxonbinmethod_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 6091 |
-- |
|
| 6092 |
|
|
| 6093 |
ALTER TABLE ONLY aggregateoccurrence |
|
| 6094 |
ADD CONSTRAINT aggregateoccurrence_taxonbinmethod_id FOREIGN KEY (taxonbinmethod_id) REFERENCES taxonbinmethod(taxonbinmethod_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 6095 |
|
|
| 6096 |
|
|
| 6097 |
-- |
|
| 6098 | 6024 |
-- Name: aggregateoccurrence_taxonoccurrence_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 6099 | 6025 |
-- |
| 6100 | 6026 |
|
| ... | ... | |
| 6879 | 6805 |
|
| 6880 | 6806 |
|
| 6881 | 6807 |
-- |
| 6882 |
-- Name: taxonbinmethod_sizeclass_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 6883 |
-- |
|
| 6884 |
|
|
| 6885 |
ALTER TABLE ONLY taxonbinmethod |
|
| 6886 |
ADD CONSTRAINT taxonbinmethod_sizeclass_id FOREIGN KEY (sizeclass_id) REFERENCES sizeclass(sizeclass_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 6887 |
|
|
| 6888 |
|
|
| 6889 |
-- |
|
| 6890 |
-- Name: taxonbinmethod_stratumtype_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 6891 |
-- |
|
| 6892 |
|
|
| 6893 |
ALTER TABLE ONLY taxonbinmethod |
|
| 6894 |
ADD CONSTRAINT taxonbinmethod_stratumtype_id FOREIGN KEY (stratumtype_id) REFERENCES stratumtype(stratumtype_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 6895 |
|
|
| 6896 |
|
|
| 6897 |
-- |
|
| 6898 | 6808 |
-- Name: taxondetermination_party_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 6899 | 6809 |
-- |
| 6900 | 6810 |
|
Also available in: Unified diff
vegbien.sql: Removed taxonbinmethod table since its fields are now in aggregateoccurrence