Revision 673
Added by Aaron Marcuse-Kubitza almost 13 years ago
| vegbien.sql | ||
|---|---|---|
| 2357 | 2357 |
SET default_with_oids = true; |
| 2358 | 2358 |
|
| 2359 | 2359 |
-- |
| 2360 |
-- Name: stem; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 2360 |
-- Name: stemobservation; Type: TABLE; Schema: public; Owner: -; Tablespace:
|
|
| 2361 | 2361 |
-- |
| 2362 | 2362 |
|
| 2363 |
CREATE TABLE stem ( |
|
| 2364 |
stem_id integer NOT NULL, |
|
| 2363 |
CREATE TABLE stemobservation (
|
|
| 2364 |
stemobservation_id integer NOT NULL,
|
|
| 2365 | 2365 |
plantobservation_id integer NOT NULL, |
| 2366 | 2366 |
authorstemcode character varying(20), |
| 2367 | 2367 |
xposition double precision, |
| 2368 | 2368 |
yposition double precision, |
| 2369 | 2369 |
health character varying(50), |
| 2370 |
emb_stem integer, |
|
| 2370 |
emb_stemobservation integer,
|
|
| 2371 | 2371 |
diameter double precision, |
| 2372 | 2372 |
height double precision, |
| 2373 | 2373 |
heightaccuracy double precision, |
| ... | ... | |
| 2379 | 2379 |
|
| 2380 | 2380 |
|
| 2381 | 2381 |
-- |
| 2382 |
-- Name: TABLE stem; Type: COMMENT; Schema: public; Owner: - |
|
| 2382 |
-- Name: TABLE stemobservation; Type: COMMENT; Schema: public; Owner: -
|
|
| 2383 | 2383 |
-- |
| 2384 | 2384 |
|
| 2385 |
COMMENT ON TABLE stem IS 'VegBank''s stemlocation table.'; |
|
| 2385 |
COMMENT ON TABLE stemobservation IS 'VegBank''s stemlocation table.';
|
|
| 2386 | 2386 |
|
| 2387 | 2387 |
|
| 2388 | 2388 |
-- |
| 2389 |
-- Name: stem_stem_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
|
| 2389 |
-- Name: stemobservation_stemobservation_id_seq; Type: SEQUENCE; Schema: public; Owner: -
|
|
| 2390 | 2390 |
-- |
| 2391 | 2391 |
|
| 2392 |
CREATE SEQUENCE stem_stem_id_seq
|
|
| 2392 |
CREATE SEQUENCE stemobservation_stemobservation_id_seq
|
|
| 2393 | 2393 |
START WITH 1 |
| 2394 | 2394 |
INCREMENT BY 1 |
| 2395 | 2395 |
NO MINVALUE |
| ... | ... | |
| 2398 | 2398 |
|
| 2399 | 2399 |
|
| 2400 | 2400 |
-- |
| 2401 |
-- Name: stem_stem_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
|
| 2401 |
-- Name: stemobservation_stemobservation_id_seq; Type: SEQUENCE OWNED BY; Schema: public; Owner: -
|
|
| 2402 | 2402 |
-- |
| 2403 | 2403 |
|
| 2404 |
ALTER SEQUENCE stem_stem_id_seq OWNED BY stem.stem_id;
|
|
| 2404 |
ALTER SEQUENCE stemobservation_stemobservation_id_seq OWNED BY stemobservation.stemobservation_id;
|
|
| 2405 | 2405 |
|
| 2406 | 2406 |
|
| 2407 | 2407 |
-- |
| ... | ... | |
| 2767 | 2767 |
|
| 2768 | 2768 |
CREATE TABLE trait ( |
| 2769 | 2769 |
trait_id integer NOT NULL, |
| 2770 |
stem_id integer NOT NULL, |
|
| 2770 |
stemobservation_id integer NOT NULL,
|
|
| 2771 | 2771 |
type character varying(255) NOT NULL, |
| 2772 | 2772 |
value character varying(255) |
| 2773 | 2773 |
); |
| ... | ... | |
| 3661 | 3661 |
|
| 3662 | 3662 |
|
| 3663 | 3663 |
-- |
| 3664 |
-- Name: stem_id; Type: DEFAULT; Schema: public; Owner: - |
|
| 3664 |
-- Name: stemobservation_id; Type: DEFAULT; Schema: public; Owner: -
|
|
| 3665 | 3665 |
-- |
| 3666 | 3666 |
|
| 3667 |
ALTER TABLE stem ALTER COLUMN stem_id SET DEFAULT nextval('stem_stem_id_seq'::regclass);
|
|
| 3667 |
ALTER TABLE stemobservation ALTER COLUMN stemobservation_id SET DEFAULT nextval('stemobservation_stemobservation_id_seq'::regclass);
|
|
| 3668 | 3668 |
|
| 3669 | 3669 |
|
| 3670 | 3670 |
-- |
| ... | ... | |
| 4470 | 4470 |
|
| 4471 | 4471 |
|
| 4472 | 4472 |
-- |
| 4473 |
-- Name: stem_keys_accessioncode; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 4473 |
-- Name: stemobservation_keys_accessioncode; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
| 4474 | 4474 |
-- |
| 4475 | 4475 |
|
| 4476 |
ALTER TABLE ONLY stem |
|
| 4477 |
ADD CONSTRAINT stem_keys_accessioncode UNIQUE (plantobservation_id, sourceaccessioncode); |
|
| 4476 |
ALTER TABLE ONLY stemobservation
|
|
| 4477 |
ADD CONSTRAINT stemobservation_keys_accessioncode UNIQUE (plantobservation_id, sourceaccessioncode);
|
|
| 4478 | 4478 |
|
| 4479 | 4479 |
|
| 4480 | 4480 |
-- |
| 4481 |
-- Name: stem_keys_code; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 4481 |
-- Name: stemobservation_keys_code; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
| 4482 | 4482 |
-- |
| 4483 | 4483 |
|
| 4484 |
ALTER TABLE ONLY stem |
|
| 4485 |
ADD CONSTRAINT stem_keys_code UNIQUE (plantobservation_id, authorstemcode); |
|
| 4484 |
ALTER TABLE ONLY stemobservation
|
|
| 4485 |
ADD CONSTRAINT stemobservation_keys_code UNIQUE (plantobservation_id, authorstemcode);
|
|
| 4486 | 4486 |
|
| 4487 | 4487 |
|
| 4488 | 4488 |
-- |
| 4489 |
-- Name: stem_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 4489 |
-- Name: stemobservation_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace:
|
|
| 4490 | 4490 |
-- |
| 4491 | 4491 |
|
| 4492 |
ALTER TABLE ONLY stem |
|
| 4493 |
ADD CONSTRAINT stem_pkey PRIMARY KEY (stem_id);
|
|
| 4492 |
ALTER TABLE ONLY stemobservation
|
|
| 4493 |
ADD CONSTRAINT stemobservation_pkey PRIMARY KEY (stemobservation_id);
|
|
| 4494 | 4494 |
|
| 4495 | 4495 |
|
| 4496 | 4496 |
-- |
| ... | ... | |
| 5037 | 5037 |
|
| 5038 | 5038 |
|
| 5039 | 5039 |
-- |
| 5040 |
-- Name: emb_stem_idx; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 5040 |
-- Name: emb_stemobservation_idx; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 5041 | 5041 |
-- |
| 5042 | 5042 |
|
| 5043 |
CREATE INDEX emb_stem_idx ON stem USING btree (emb_stem);
|
|
| 5043 |
CREATE INDEX emb_stemobservation_idx ON stemobservation USING btree (emb_stemobservation);
|
|
| 5044 | 5044 |
|
| 5045 | 5045 |
|
| 5046 | 5046 |
-- |
| ... | ... | |
| 5646 | 5646 |
|
| 5647 | 5647 |
|
| 5648 | 5648 |
-- |
| 5649 |
-- Name: stem_plantobservation_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 5649 |
-- Name: stemobservation_plantobservation_id_x; Type: INDEX; Schema: public; Owner: -; Tablespace:
|
|
| 5650 | 5650 |
-- |
| 5651 | 5651 |
|
| 5652 |
CREATE INDEX stem_plantobservation_id_x ON stem USING btree (plantobservation_id);
|
|
| 5652 |
CREATE INDEX stemobservation_plantobservation_id_x ON stemobservation USING btree (plantobservation_id);
|
|
| 5653 | 5653 |
|
| 5654 | 5654 |
|
| 5655 | 5655 |
-- |
| ... | ... | |
| 6656 | 6656 |
|
| 6657 | 6657 |
|
| 6658 | 6658 |
-- |
| 6659 |
-- Name: stem_plantobservation_id; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 6659 |
-- Name: stemobservation_plantobservation_id; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 6660 | 6660 |
-- |
| 6661 | 6661 |
|
| 6662 |
ALTER TABLE ONLY stem |
|
| 6663 |
ADD CONSTRAINT stem_plantobservation_id FOREIGN KEY (plantobservation_id) REFERENCES plantobservation(plantobservation_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 6662 |
ALTER TABLE ONLY stemobservation
|
|
| 6663 |
ADD CONSTRAINT stemobservation_plantobservation_id FOREIGN KEY (plantobservation_id) REFERENCES plantobservation(plantobservation_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 6664 | 6664 |
|
| 6665 | 6665 |
|
| 6666 | 6666 |
-- |
| ... | ... | |
| 6792 | 6792 |
|
| 6793 | 6793 |
|
| 6794 | 6794 |
-- |
| 6795 |
-- Name: trait_stem_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 6795 |
-- Name: trait_stemobservation_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: -
|
|
| 6796 | 6796 |
-- |
| 6797 | 6797 |
|
| 6798 | 6798 |
ALTER TABLE ONLY trait |
| 6799 |
ADD CONSTRAINT trait_stem_id_fkey FOREIGN KEY (stem_id) REFERENCES stem(stem_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 6799 |
ADD CONSTRAINT trait_stemobservation_id_fkey FOREIGN KEY (stemobservation_id) REFERENCES stemobservation(stemobservation_id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
| 6800 | 6800 |
|
| 6801 | 6801 |
|
| 6802 | 6802 |
-- |
Also available in: Unified diff
VegBIEN: Renamed stem to stemobservation