Revision 6085
Added by Aaron Marcuse-Kubitza over 11 years ago
| schemas/vegbien.my.sql | ||
|---|---|---|
| 3251 | 3251 |
|
| 3252 | 3252 |
|
| 3253 | 3253 |
-- |
| 3254 |
-- Name: threatened_taxonlabel; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 3255 |
-- |
|
| 3256 |
|
|
| 3257 |
CREATE TABLE threatened_taxonlabel ( |
|
| 3258 |
taxonlabel_id int(11) NOT NULL |
|
| 3259 |
); |
|
| 3260 |
|
|
| 3261 |
|
|
| 3262 |
-- |
|
| 3263 |
-- Name: threatened_taxonlabel_view; Type: VIEW; Schema: public; Owner: - |
|
| 3264 |
-- |
|
| 3265 |
|
|
| 3266 |
|
|
| 3267 |
|
|
| 3268 |
|
|
| 3269 |
-- |
|
| 3254 | 3270 |
-- Name: tnrs_input_name; Type: VIEW; Schema: public; Owner: - |
| 3255 | 3271 |
-- |
| 3256 | 3272 |
|
| ... | ... | |
| 4165 | 4181 |
|
| 4166 | 4182 |
|
| 4167 | 4183 |
-- |
| 4184 |
-- Data for Name: threatened_taxonlabel; Type: TABLE DATA; Schema: public; Owner: - |
|
| 4185 |
-- |
|
| 4186 |
|
|
| 4187 |
|
|
| 4188 |
|
|
| 4189 |
-- |
|
| 4168 | 4190 |
-- Data for Name: trait; Type: TABLE DATA; Schema: public; Owner: - |
| 4169 | 4191 |
-- |
| 4170 | 4192 |
|
| ... | ... | |
| 4751 | 4773 |
|
| 4752 | 4774 |
|
| 4753 | 4775 |
-- |
| 4776 |
-- Name: threatened_taxonlabel_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 4777 |
-- |
|
| 4778 |
|
|
| 4779 |
ALTER TABLE threatened_taxonlabel |
|
| 4780 |
ADD CONSTRAINT threatened_taxonlabel_pkey PRIMARY KEY (taxonlabel_id); |
|
| 4781 |
|
|
| 4782 |
|
|
| 4783 |
-- |
|
| 4754 | 4784 |
-- Name: trait_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 4755 | 4785 |
-- |
| 4756 | 4786 |
|
| ... | ... | |
| 6270 | 6300 |
|
| 6271 | 6301 |
|
| 6272 | 6302 |
-- |
| 6303 |
-- Name: threatened_taxonlabel_taxonlabel_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 6304 |
-- |
|
| 6305 |
|
|
| 6306 |
|
|
| 6307 |
|
|
| 6308 |
|
|
| 6309 |
-- |
|
| 6273 | 6310 |
-- Name: trait_stemobservation_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 6274 | 6311 |
-- |
| 6275 | 6312 |
|
| schemas/filter_ERD.csv | ||
|---|---|---|
| 1 | 1 |
"PostgreSQL","MySQL","Comments" |
| 2 | 2 |
,,"fkeys to heavily-linked tables" |
| 3 | 3 |
"^ALTER TABLE (?:commclass)\b[^;]*\bFOREIGN KEY\b[^;]*\bREFERENCES \b[^;]*;",,outward |
| 4 |
"^ALTER TABLE (?:taxoncorrelation|taxonlineage)\b[^;]*\bFOREIGN KEY\b[^;]*\bREFERENCES taxonlabel\b[^;]*;",,inward |
|
| 4 |
"^ALTER TABLE (?:taxoncorrelation|taxonlineage|threatened_taxonlabel)\b[^;]*\bFOREIGN KEY\b[^;]*\bREFERENCES taxonlabel\b[^;]*;",,inward
|
|
| 5 | 5 |
"^ALTER TABLE (?:place)\b[^;]*\bFOREIGN KEY\b[^;]*\(canon_place_id\) REFERENCES place\b[^;]*;",,inward |
| 6 | 6 |
"^ALTER TABLE (?:taxonlabel)\b[^;]*\bFOREIGN KEY\b[^;]*\(canon_label_id\) REFERENCES taxonlabel\b[^;]*;",,inward |
| 7 | 7 |
"^ALTER TABLE \b[^;]*\bFOREIGN KEY\b[^;]*\bREFERENCES coverindex\b[^;]*;",,inward |
| schemas/vegbien.sql | ||
|---|---|---|
| 4201 | 4201 |
|
| 4202 | 4202 |
|
| 4203 | 4203 |
-- |
| 4204 |
-- Name: threatened_taxonlabel; Type: TABLE; Schema: public; Owner: -; Tablespace: |
|
| 4205 |
-- |
|
| 4206 |
|
|
| 4207 |
CREATE TABLE threatened_taxonlabel ( |
|
| 4208 |
taxonlabel_id integer NOT NULL |
|
| 4209 |
); |
|
| 4210 |
|
|
| 4211 |
|
|
| 4212 |
-- |
|
| 4213 |
-- Name: threatened_taxonlabel_view; Type: VIEW; Schema: public; Owner: - |
|
| 4214 |
-- |
|
| 4215 |
|
|
| 4216 |
CREATE VIEW threatened_taxonlabel_view AS |
|
| 4217 |
SELECT accepted_taxonlabel_descendant.descendant_id AS taxonlabel_id FROM ((taxonlabel datasource_taxonlabel JOIN taxonlabel accepted_taxonlabel ON ((accepted_taxonlabel.taxonlabel_id = datasource_taxonlabel.canon_label_id))) JOIN taxonlabel_relationship accepted_taxonlabel_descendant ON ((accepted_taxonlabel_descendant.ancestor_id = accepted_taxonlabel.taxonlabel_id))) WHERE (((datasource_taxonlabel.reference_id = (SELECT reference.reference_id FROM reference WHERE (reference.shortname = 'IUCN'::text))) AND (datasource_taxonlabel.taxonomicname IS NOT NULL)) AND (accepted_taxonlabel.rank >= 'species'::taxonrank)); |
|
| 4218 |
|
|
| 4219 |
|
|
| 4220 |
-- |
|
| 4204 | 4221 |
-- Name: tnrs_input_name; Type: VIEW; Schema: public; Owner: - |
| 4205 | 4222 |
-- |
| 4206 | 4223 |
|
| ... | ... | |
| 5131 | 5148 |
|
| 5132 | 5149 |
|
| 5133 | 5150 |
-- |
| 5151 |
-- Data for Name: threatened_taxonlabel; Type: TABLE DATA; Schema: public; Owner: - |
|
| 5152 |
-- |
|
| 5153 |
|
|
| 5154 |
|
|
| 5155 |
|
|
| 5156 |
-- |
|
| 5134 | 5157 |
-- Data for Name: trait; Type: TABLE DATA; Schema: public; Owner: - |
| 5135 | 5158 |
-- |
| 5136 | 5159 |
|
| ... | ... | |
| 5717 | 5740 |
|
| 5718 | 5741 |
|
| 5719 | 5742 |
-- |
| 5743 |
-- Name: threatened_taxonlabel_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
|
| 5744 |
-- |
|
| 5745 |
|
|
| 5746 |
ALTER TABLE ONLY threatened_taxonlabel |
|
| 5747 |
ADD CONSTRAINT threatened_taxonlabel_pkey PRIMARY KEY (taxonlabel_id); |
|
| 5748 |
|
|
| 5749 |
|
|
| 5750 |
-- |
|
| 5720 | 5751 |
-- Name: trait_pkey; Type: CONSTRAINT; Schema: public; Owner: -; Tablespace: |
| 5721 | 5752 |
-- |
| 5722 | 5753 |
|
| ... | ... | |
| 7278 | 7309 |
|
| 7279 | 7310 |
|
| 7280 | 7311 |
-- |
| 7312 |
-- Name: threatened_taxonlabel_taxonlabel_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
|
| 7313 |
-- |
|
| 7314 |
|
|
| 7315 |
ALTER TABLE ONLY threatened_taxonlabel |
|
| 7316 |
ADD CONSTRAINT threatened_taxonlabel_taxonlabel_id_fkey FOREIGN KEY (taxonlabel_id) REFERENCES taxonlabel(taxonlabel_id) ON UPDATE CASCADE ON DELETE CASCADE; |
|
| 7317 |
|
|
| 7318 |
|
|
| 7319 |
-- |
|
| 7281 | 7320 |
-- Name: trait_stemobservation_id_fkey; Type: FK CONSTRAINT; Schema: public; Owner: - |
| 7282 | 7321 |
-- |
| 7283 | 7322 |
|
| bin/make_analytical_db | ||
|---|---|---|
| 12 | 12 |
INSERT INTO geoscrub_input SELECT * FROM geoscrub_input_view; |
| 13 | 13 |
|
| 14 | 14 |
SELECT make_family_higher_plant_group(); |
| 15 |
TRUNCATE threatened_taxonlabel; |
|
| 16 |
INSERT INTO threatened_taxonlabel SELECT * FROM threatened_taxonlabel_view; |
|
| 15 | 17 |
|
| 16 | 18 |
TRUNCATE analytical_stem; |
| 17 | 19 |
INSERT INTO analytical_stem SELECT * FROM analytical_stem_view; |
Also available in: Unified diff
schemas/vegbien.sql: Added threatened_taxonlabel derived table with generating view threatened_taxonlabel_view