Revision 674
Added by Aaron Marcuse-Kubitza almost 13 years ago
| schemas/vegbien.for_ERD.my.sql | ||
|---|---|---|
| 239 | 239 |
CONSTRAINT aggregateoccurrence_keys_method UNIQUE (taxonoccurrence_id , taxonbinmethod_id ) |
| 240 | 240 |
); |
| 241 | 241 |
|
| 242 |
CREATE TABLE plant -- A physical, tagged plant. |
|
| 243 |
( |
|
| 244 |
plant_id int(11) NOT NULL AUTO_INCREMENT, |
|
| 245 |
CONSTRAINT plant_pkey PRIMARY KEY (plant_id ) |
|
| 246 |
); |
|
| 247 |
|
|
| 248 |
CREATE TABLE planttag |
|
| 249 |
( |
|
| 250 |
planttag_id int(11) NOT NULL AUTO_INCREMENT, |
|
| 251 |
plant_id int(11) NOT NULL, |
|
| 252 |
tag character varying(255) NOT NULL, |
|
| 253 |
CONSTRAINT planttag_pkey PRIMARY KEY (planttag_id ), |
|
| 254 |
CONSTRAINT planttag_plant_id FOREIGN KEY (plant_id) |
|
| 255 |
REFERENCES plant (plant_id) MATCH SIMPLE |
|
| 256 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 257 |
CONSTRAINT planttag_keys UNIQUE (plant_id , tag ) |
|
| 258 |
); |
|
| 259 |
|
|
| 242 | 260 |
CREATE TABLE plantobservation -- VegBank's stemcount table. |
| 243 | 261 |
( |
| 244 | 262 |
plantobservation_id int(11) NOT NULL AUTO_INCREMENT, |
| ... | ... | |
| 250 | 268 |
accessioncode character varying(255), |
| 251 | 269 |
stemcount int(11), |
| 252 | 270 |
sourceaccessioncode character varying(100), |
| 271 |
plant_id int(11), |
|
| 253 | 272 |
CONSTRAINT plantobservation_pkey PRIMARY KEY (plantobservation_id ), |
| 254 | 273 |
CONSTRAINT plantobservation_aggregateoccurrence_id FOREIGN KEY (aggregateoccurrence_id) |
| 255 | 274 |
REFERENCES aggregateoccurrence (aggregateoccurrence_id) MATCH SIMPLE |
| 256 | 275 |
ON UPDATE CASCADE ON DELETE CASCADE, |
| 276 |
CONSTRAINT plantobservation_plant_id FOREIGN KEY (plant_id) |
|
| 277 |
REFERENCES plant (plant_id) MATCH SIMPLE |
|
| 278 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 257 | 279 |
CONSTRAINT plantobservation_aggregateoccurrence_id_1_to_1 UNIQUE (aggregateoccurrence_id ) |
| 258 | 280 |
); |
| 259 | 281 |
|
| schemas/vegbien.for_wiki.sql | ||
|---|---|---|
| 61 | 61 |
plantobservation_id integer, |
| 62 | 62 |
); |
| 63 | 63 |
|
| 64 |
CREATE TABLE plant -- A physical, tagged plant. |
|
| 65 |
( |
|
| 66 |
plant_id serial NOT NULL, |
|
| 67 |
); |
|
| 68 |
|
|
| 69 |
CREATE TABLE planttag |
|
| 70 |
( |
|
| 71 |
planttag_id serial NOT NULL, |
|
| 72 |
plant_id integer NOT NULL, |
|
| 73 |
tag character varying(255) NOT NULL, |
|
| 74 |
); |
|
| 75 |
|
|
| 64 | 76 |
CREATE TABLE plantobservation -- VegBank's stemcount table. |
| 65 | 77 |
( |
| 66 | 78 |
plantobservation_id serial NOT NULL, |
| ... | ... | |
| 72 | 84 |
accessioncode character varying(255), |
| 73 | 85 |
stemcount integer, |
| 74 | 86 |
sourceaccessioncode character varying(100), |
| 87 |
plant_id integer, |
|
| 75 | 88 |
); |
| 76 | 89 |
|
| 77 | 90 |
CREATE TABLE stemobservation -- VegBank's stemlocation table. |
| schemas/vegbien_empty.sql | ||
|---|---|---|
| 40 | 40 |
TRUNCATE notelink CASCADE; |
| 41 | 41 |
TRUNCATE party CASCADE; |
| 42 | 42 |
TRUNCATE partymember CASCADE; |
| 43 |
TRUNCATE plant CASCADE; |
|
| 43 | 44 |
TRUNCATE plantconcept CASCADE; |
| 44 | 45 |
TRUNCATE plantcorrelation CASCADE; |
| 45 | 46 |
TRUNCATE plantlineage CASCADE; |
| 46 | 47 |
TRUNCATE plantname CASCADE; |
| 47 | 48 |
TRUNCATE plantobservation CASCADE; |
| 48 | 49 |
TRUNCATE plantstatus CASCADE; |
| 50 |
TRUNCATE planttag CASCADE; |
|
| 49 | 51 |
TRUNCATE plantusage CASCADE; |
| 50 | 52 |
TRUNCATE project CASCADE; |
| 51 | 53 |
TRUNCATE projectcontributor CASCADE; |
| schemas/vegbien.for_ERD.sql | ||
|---|---|---|
| 239 | 239 |
CONSTRAINT aggregateoccurrence_keys_method UNIQUE (taxonoccurrence_id , taxonbinmethod_id ) |
| 240 | 240 |
); |
| 241 | 241 |
|
| 242 |
CREATE TABLE plant -- A physical, tagged plant. |
|
| 243 |
( |
|
| 244 |
plant_id serial NOT NULL, |
|
| 245 |
CONSTRAINT plant_pkey PRIMARY KEY (plant_id ) |
|
| 246 |
); |
|
| 247 |
|
|
| 248 |
CREATE TABLE planttag |
|
| 249 |
( |
|
| 250 |
planttag_id serial NOT NULL, |
|
| 251 |
plant_id integer NOT NULL, |
|
| 252 |
tag character varying(255) NOT NULL, |
|
| 253 |
CONSTRAINT planttag_pkey PRIMARY KEY (planttag_id ), |
|
| 254 |
CONSTRAINT planttag_plant_id FOREIGN KEY (plant_id) |
|
| 255 |
REFERENCES plant (plant_id) MATCH SIMPLE |
|
| 256 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 257 |
CONSTRAINT planttag_keys UNIQUE (plant_id , tag ) |
|
| 258 |
); |
|
| 259 |
|
|
| 242 | 260 |
CREATE TABLE plantobservation -- VegBank's stemcount table. |
| 243 | 261 |
( |
| 244 | 262 |
plantobservation_id serial NOT NULL, |
| ... | ... | |
| 250 | 268 |
accessioncode character varying(255), |
| 251 | 269 |
stemcount integer, |
| 252 | 270 |
sourceaccessioncode character varying(100), |
| 271 |
plant_id integer, |
|
| 253 | 272 |
CONSTRAINT plantobservation_pkey PRIMARY KEY (plantobservation_id ), |
| 254 | 273 |
CONSTRAINT plantobservation_aggregateoccurrence_id FOREIGN KEY (aggregateoccurrence_id) |
| 255 | 274 |
REFERENCES aggregateoccurrence (aggregateoccurrence_id) MATCH SIMPLE |
| 256 | 275 |
ON UPDATE CASCADE ON DELETE CASCADE, |
| 276 |
CONSTRAINT plantobservation_plant_id FOREIGN KEY (plant_id) |
|
| 277 |
REFERENCES plant (plant_id) MATCH SIMPLE |
|
| 278 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 257 | 279 |
CONSTRAINT plantobservation_aggregateoccurrence_id_1_to_1 UNIQUE (aggregateoccurrence_id ) |
| 258 | 280 |
); |
| 259 | 281 |
|
| schemas/vegbien.sql | ||
|---|---|---|
| 1561 | 1561 |
ALTER SEQUENCE partymember_partymember_id_seq OWNED BY partymember.partymember_id; |
| 1562 | 1562 |
|
| 1563 | 1563 |
|
| 1564 |
SET default_with_oids = false; |
|
| 1565 |
|
|
| 1564 | 1566 |
-- |
| 1567 |
-- Name: plant; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 1568 |
-- |
|
| 1569 |
|
|
| 1570 |
CREATE TABLE plant ( |
|
| 1571 |
plant_id integer NOT NULL |
|
| 1572 |
); |
|
| 1573 |
|
|
| 1574 |
|
|
| 1575 |
-- |
|
| 1576 |
-- Name: TABLE plant; Type: COMMENT; Schema: public; Owner: - |
|
| 1577 |
-- |
|
| 1578 |
|
|
| 1579 |
COMMENT ON TABLE plant IS 'A physical, tagged plant.'; |
|
| 1580 |
|
|
| 1581 |
|
|
| 1582 |
-- |
|
| 1583 |
-- Name: plant_plant_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
|
| 1584 |
-- |
|
| 1585 |
|
|
| 1586 |
CREATE SEQUENCE plant_plant_id_seq |
|
| 1587 |
START WITH 1 |
|
| 1588 |
INCREMENT BY 1 |
|
| 1589 |
NO MINVALUE |
|
| 1590 |
NO MAXVALUE |
|
| 1591 |
CACHE 1; |
|
| 1592 |
|
|
| 1593 |
|
|
| 1594 |
-- |
|
| 1595 |
-- Name: plant_plant_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - |
|
| 1596 |
-- |
|
| 1597 |
|
|
| 1598 |
ALTER SEQUENCE plant_plant_id_seq OWNED BY plant.plant_id; |
|
| 1599 |
|
|
| 1600 |
|
|
| 1601 |
SET default_with_oids = true; |
|
| 1602 |
|
|
| 1603 |
-- |
|
| 1565 | 1604 |
-- Name: plantconcept; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 1566 | 1605 |
-- |
| 1567 | 1606 |
|
| ... | ... | |
| 1703 | 1742 |
authorplantcode character varying(20), |
| 1704 | 1743 |
accessioncode character varying(255), |
| 1705 | 1744 |
stemcount integer, |
| 1706 |
sourceaccessioncode character varying(100) |
|
| 1745 |
sourceaccessioncode character varying(100), |
|
| 1746 |
plant_id integer |
|
| 1707 | 1747 |
); |
| 1708 | 1748 |
|
| 1709 | 1749 |
|
| ... | ... | |
| 1773 | 1813 |
ALTER SEQUENCE plantstatus_plantstatus_id_seq OWNED BY plantstatus.plantstatus_id; |
| 1774 | 1814 |
|
| 1775 | 1815 |
|
| 1816 |
SET default_with_oids = false; |
|
| 1817 |
|
|
| 1776 | 1818 |
-- |
| 1819 |
-- Name: planttag; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 1820 |
-- |
|
| 1821 |
|
|
| 1822 |
CREATE TABLE planttag ( |
|
| 1823 |
planttag_id integer NOT NULL, |
|
| 1824 |
plant_id integer NOT NULL, |
|
| 1825 |
tag character varying(255) NOT NULL |
|
| 1826 |
); |
|
| 1827 |
|
|
| 1828 |
|
|
| 1829 |
-- |
|
| 1830 |
-- Name: planttag_planttag_id_seq; Type: SEQUENCE; Schema: public; Owner: - |
|
| 1831 |
-- |
|
| 1832 |
|
|
| 1833 |
CREATE SEQUENCE planttag_planttag_id_seq |
|
| 1834 |
START WITH 1 |
|
| 1835 |
INCREMENT BY 1 |
|
| 1836 |
NO MINVALUE |
|
| 1837 |
NO MAXVALUE |
|
| 1838 |
CACHE 1; |
|
| 1839 |
|
|
| 1840 |
|
|
| 1841 |
-- |
|
| 1842 |
-- Name: planttag_planttag_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: - |
|
| 1843 |
-- |
|
| 1844 |
|
|
| 1845 |
ALTER SEQUENCE planttag_planttag_id_seq OWNED BY planttag.planttag_id; |
|
| 1846 |
|
|
| 1847 |
|
|
| 1848 |
SET default_with_oids = true; |
|
| 1849 |
|
|
| 1850 |
-- |
|
| 1777 | 1851 |
-- Name: plantusage; Type: TABLE; Schema: public; Owner: -; Tablespace: |
| 1778 | 1852 |
-- |
| 1779 | 1853 |
|
| ... | ... | |
| 3514 | 3588 |
|
| 3515 | 3589 |
|
| 3516 | 3590 |
-- |
| 3591 |
-- Name: plant_id; Type: DEFAULT; Schema: public; Owner: - |
|
| 3592 |
-- |
|
| 3593 |
|
|
| 3594 |
ALTER TABLE plant ALTER COLUMN plant_id SET DEFAULT nextval('plant_plant_id_seq'::regclass);
|
|
| 3595 |
|
|
| 3596 |
|
|
| 3597 |
-- |
|
| 3517 | 3598 |
-- Name: plantconcept_id; Type: DEFAULT; Schema: public; Owner: - |
| 3518 | 3599 |
-- |
| 3519 | 3600 |
|
| ... | ... | |
| 3556 | 3637 |
|
| 3557 | 3638 |
|
| 3558 | 3639 |
-- |
| 3640 |
-- Name: planttag_id; Type: DEFAULT; Schema: public; Owner: - |
|
| 3641 |
-- |
|
| 3642 |
|
|
| 3643 |
ALTER TABLE planttag ALTER COLUMN planttag_id SET DEFAULT nextval('planttag_planttag_id_seq'::regclass);
|
|
| 3644 |
|
|
| 3645 |
|
|
| 3646 |
-- |
|
| 3559 | 3647 |
-- Name: plantusage_id; Type: DEFAULT; Schema: public; Owner: - |
| 3560 | 3648 |
-- |
| 3561 | 3649 |
|
| ... | ... | |
| 4254 | 4342 |
|
| 4255 | 4343 |
|
| 4256 | 4344 |
-- |
| 4345 |
-- Name: plant_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 4346 |
-- |
|
| 4347 |
|
|
| 4348 |
ALTER TABLE ONLY plant |
|
| 4349 |
ADD CONSTRAINT plant_pkey PRIMARY KEY (plant_id); |
|
| 4350 |
|
|
| 4351 |
|
|
| 4352 |
-- |
|
| 4257 | 4353 |
-- Name: plantconcept_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 4258 | 4354 |
-- |
| 4259 | 4355 |
|
| ... | ... | |
| 4310 | 4406 |
|
| 4311 | 4407 |
|
| 4312 | 4408 |
-- |
| 4409 |
-- Name: planttag_keys; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 4410 |
-- |
|
| 4411 |
|
|
| 4412 |
ALTER TABLE ONLY planttag |
|
| 4413 |
ADD CONSTRAINT planttag_keys UNIQUE (plant_id, tag); |
|
| 4414 |
|
|
| 4415 |
|
|
| 4416 |
-- |
|
| 4417 |
-- Name: planttag_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 4418 |
-- |
|
| 4419 |
|
|
| 4420 |
ALTER TABLE ONLY planttag |
|
| 4421 |
ADD CONSTRAINT planttag_pkey PRIMARY KEY (planttag_id); |
|
| 4422 |
|
|
| 4423 |
|
|
| 4424 |
-- |
|
| 4313 | 4425 |
-- Name: plantusage_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 4314 | 4426 |
-- |
| 4315 | 4427 |
|
| ... | ... | |
| 5086 | 5198 |
|
| 5087 | 5199 |
|
| 5088 | 5200 |
-- |
| 5201 |
-- Name: fki_plantobservation_plant_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 5202 |
-- |
|
| 5203 |
|
|
| 5204 |
CREATE INDEX fki_plantobservation_plant_id ON plantobservation USING btree (plant_id); |
|
| 5205 |
|
|
| 5206 |
|
|
| 5207 |
-- |
|
| 5208 |
-- Name: fki_planttag_plant_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 5209 |
-- |
|
| 5210 |
|
|
| 5211 |
CREATE INDEX fki_planttag_plant_id ON planttag USING btree (plant_id); |
|
| 5212 |
|
|
| 5213 |
|
|
| 5214 |
-- |
|
| 5089 | 5215 |
-- Name: fki_specimenreplicate_museum_id; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 5090 | 5216 |
-- |
| 5091 | 5217 |
|
| ... | ... | |
| 6472 | 6598 |
|
| 6473 | 6599 |
|
| 6474 | 6600 |
-- |
| 6601 |
-- Name: plantobservation_plant_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 6602 |
-- |
|
| 6603 |
|
|
| 6604 |
ALTER TABLE ONLY plantobservation |
|
| 6605 |
ADD CONSTRAINT plantobservation_plant_id FOREIGN KEY (plant_id) REFERENCES plant(plant_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 6606 |
|
|
| 6607 |
|
|
| 6608 |
-- |
|
| 6475 | 6609 |
-- Name: plantstatus_party_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 6476 | 6610 |
-- |
| 6477 | 6611 |
|
| ... | ... | |
| 6504 | 6638 |
|
| 6505 | 6639 |
|
| 6506 | 6640 |
-- |
| 6641 |
-- Name: planttag_plant_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 6642 |
-- |
|
| 6643 |
|
|
| 6644 |
ALTER TABLE ONLY planttag |
|
| 6645 |
ADD CONSTRAINT planttag_plant_id FOREIGN KEY (plant_id) REFERENCES plant(plant_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 6646 |
|
|
| 6647 |
|
|
| 6648 |
-- |
|
| 6507 | 6649 |
-- Name: plantusage_party_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 6508 | 6650 |
-- |
| 6509 | 6651 |
|
Also available in: Unified diff
vegbien.sql: Added plant and planttag tables