Revision 4699
Added by Aaron Marcuse-Kubitza about 12 years ago
| schemas/vegbien.my.sql | ||
|---|---|---|
| 668 | 668 |
|
| 669 | 669 |
CREATE TABLE plantconcept ( |
| 670 | 670 |
plantconcept_id int(11) NOT NULL, |
| 671 |
datasource_id int(11) NOT NULL, |
|
| 672 |
plantcode text, |
|
| 671 | 673 |
plantname_id int(11) NOT NULL, |
| 672 | 674 |
scientificname text, |
| 673 | 675 |
scientificnameauthor text, |
| ... | ... | |
| 684 | 686 |
variety text, |
| 685 | 687 |
forma text, |
| 686 | 688 |
cultivar text, |
| 687 |
plantcode text, |
|
| 688 | 689 |
accessioncode text |
| 689 | 690 |
); |
| 690 | 691 |
|
| ... | ... | |
| 3273 | 3274 |
|
| 3274 | 3275 |
|
| 3275 | 3276 |
-- |
| 3276 |
-- Name: plantconcept_unique; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3277 |
-- |
|
| 3278 |
|
|
| 3279 |
ALTER TABLE plantconcept |
|
| 3280 |
ADD CONSTRAINT plantconcept_unique UNIQUE (plantname_id); |
|
| 3281 |
|
|
| 3282 |
|
|
| 3283 |
-- |
|
| 3284 | 3277 |
-- Name: plantcorrelation_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 3285 | 3278 |
-- |
| 3286 | 3279 |
|
| ... | ... | |
| 3766 | 3759 |
|
| 3767 | 3760 |
|
| 3768 | 3761 |
-- |
| 3762 |
-- Name: plantconcept_unique_within_datasource_by_code; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 3763 |
-- |
|
| 3764 |
|
|
| 3765 |
|
|
| 3766 |
|
|
| 3767 |
|
|
| 3768 |
-- |
|
| 3769 |
-- Name: plantconcept_unique_within_datasource_by_name; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 3770 |
-- |
|
| 3771 |
|
|
| 3772 |
|
|
| 3773 |
|
|
| 3774 |
|
|
| 3775 |
-- |
|
| 3769 | 3776 |
-- Name: plantname_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 3770 | 3777 |
-- |
| 3771 | 3778 |
|
| ... | ... | |
| 4470 | 4477 |
|
| 4471 | 4478 |
|
| 4472 | 4479 |
-- |
| 4480 |
-- Name: plantconcept_datasource_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 4481 |
-- |
|
| 4482 |
|
|
| 4483 |
|
|
| 4484 |
|
|
| 4485 |
|
|
| 4486 |
-- |
|
| 4473 | 4487 |
-- Name: plantconcept_plantname_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 4474 | 4488 |
-- |
| 4475 | 4489 |
|
| schemas/vegbien.sql | ||
|---|---|---|
| 906 | 906 |
|
| 907 | 907 |
CREATE TABLE plantconcept ( |
| 908 | 908 |
plantconcept_id integer NOT NULL, |
| 909 |
datasource_id integer NOT NULL, |
|
| 910 |
plantcode text, |
|
| 909 | 911 |
plantname_id integer NOT NULL, |
| 910 | 912 |
scientificname text, |
| 911 | 913 |
scientificnameauthor text, |
| ... | ... | |
| 922 | 924 |
variety text, |
| 923 | 925 |
forma text, |
| 924 | 926 |
cultivar text, |
| 925 |
plantcode text, |
|
| 926 | 927 |
accessioncode text |
| 927 | 928 |
); |
| 928 | 929 |
|
| ... | ... | |
| 3833 | 3834 |
|
| 3834 | 3835 |
|
| 3835 | 3836 |
-- |
| 3836 |
-- Name: plantconcept_unique; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 3837 |
-- |
|
| 3838 |
|
|
| 3839 |
ALTER TABLE ONLY plantconcept |
|
| 3840 |
ADD CONSTRAINT plantconcept_unique UNIQUE (plantname_id); |
|
| 3841 |
|
|
| 3842 |
|
|
| 3843 |
-- |
|
| 3844 | 3837 |
-- Name: plantcorrelation_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 3845 | 3838 |
-- |
| 3846 | 3839 |
|
| ... | ... | |
| 4326 | 4319 |
|
| 4327 | 4320 |
|
| 4328 | 4321 |
-- |
| 4322 |
-- Name: plantconcept_unique_within_datasource_by_code; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4323 |
-- |
|
| 4324 |
|
|
| 4325 |
CREATE UNIQUE INDEX plantconcept_unique_within_datasource_by_code ON plantconcept USING btree (datasource_id, (COALESCE(plantcode, '\\N'::text))) WHERE (plantcode IS NOT NULL); |
|
| 4326 |
|
|
| 4327 |
|
|
| 4328 |
-- |
|
| 4329 |
-- Name: plantconcept_unique_within_datasource_by_name; Type: INDEX; Schema: public; Owner: -; Tablespace: |
|
| 4330 |
-- |
|
| 4331 |
|
|
| 4332 |
CREATE UNIQUE INDEX plantconcept_unique_within_datasource_by_name ON plantconcept USING btree (datasource_id, (COALESCE(plantname_id, 2147483647)), (COALESCE(scientificname, '\\N'::text)), (COALESCE(scientificnameauthor, '\\N'::text)), (COALESCE(scientificnamewithauthor, '\\N'::text)), (COALESCE(domain, '\\N'::text)), (COALESCE(kingdom, '\\N'::text)), (COALESCE(phylum, '\\N'::text)), (COALESCE(class, '\\N'::text)), (COALESCE("order", '\\N'::text)), (COALESCE(family, '\\N'::text)), (COALESCE(genus, '\\N'::text)), (COALESCE(species, '\\N'::text)), (COALESCE(subspecies, '\\N'::text)), (COALESCE(variety, '\\N'::text)), (COALESCE(forma, '\\N'::text)), (COALESCE(cultivar, '\\N'::text))) WHERE (plantcode IS NULL);
|
|
| 4333 |
|
|
| 4334 |
|
|
| 4335 |
-- |
|
| 4329 | 4336 |
-- Name: plantname_unique; Type: INDEX; Schema: public; Owner: -; Tablespace: |
| 4330 | 4337 |
-- |
| 4331 | 4338 |
|
| ... | ... | |
| 5051 | 5058 |
|
| 5052 | 5059 |
|
| 5053 | 5060 |
-- |
| 5061 |
-- Name: plantconcept_datasource_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 5062 |
-- |
|
| 5063 |
|
|
| 5064 |
ALTER TABLE ONLY plantconcept |
|
| 5065 |
ADD CONSTRAINT plantconcept_datasource_id_fkey FOREIGN KEY (datasource_id) REFERENCES party(party_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 5066 |
|
|
| 5067 |
|
|
| 5068 |
-- |
|
| 5054 | 5069 |
-- Name: plantconcept_plantname_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 5055 | 5070 |
-- |
| 5056 | 5071 |
|
Also available in: Unified diff
schemas/vegbien.sql: plantconcept: Added datasource_id and appropriate unique indexes to enable scoping by datasource. Moved plantcode right after datasource_id because it will be used for the sourceaccessioncode (if any).