Revision 4933
Added by Aaron Marcuse-Kubitza about 12 years ago
| schemas/vegbien.my.sql | ||
|---|---|---|
| 54 | 54 |
|
| 55 | 55 |
|
| 56 | 56 |
-- |
| 57 |
-- Name: role; Type: TYPE; Schema: public; Owner: -
|
|
| 57 |
-- Name: taxonrank; Type: TYPE; Schema: public; Owner: -
|
|
| 58 | 58 |
-- |
| 59 | 59 |
|
| 60 | 60 |
|
| 61 | 61 |
|
| 62 | 62 |
|
| 63 | 63 |
-- |
| 64 |
-- Name: taxonclass; Type: TYPE; Schema: public; Owner: -
|
|
| 64 |
-- Name: TYPE taxonrank; Type: COMMENT; Schema: public; Owner: -
|
|
| 65 | 65 |
-- |
| 66 | 66 |
|
| 67 | 67 |
|
| 68 | 68 |
|
| 69 | 69 |
|
| 70 | 70 |
-- |
| 71 |
-- Name: TYPE taxonclass; Type: COMMENT; Schema: public; Owner: -
|
|
| 71 |
-- Name: rankedtaxonname; Type: TYPE; Schema: public; Owner: -
|
|
| 72 | 72 |
-- |
| 73 | 73 |
|
| 74 | 74 |
|
| 75 | 75 |
|
| 76 | 76 |
|
| 77 | 77 |
-- |
| 78 |
-- Name: taxonrank; Type: TYPE; Schema: public; Owner: -
|
|
| 78 |
-- Name: role; Type: TYPE; Schema: public; Owner: -
|
|
| 79 | 79 |
-- |
| 80 | 80 |
|
| 81 | 81 |
|
| 82 | 82 |
|
| 83 | 83 |
|
| 84 | 84 |
-- |
| 85 |
-- Name: TYPE taxonrank; Type: COMMENT; Schema: public; Owner: -
|
|
| 85 |
-- Name: taxonclass; Type: TYPE; Schema: public; Owner: -
|
|
| 86 | 86 |
-- |
| 87 | 87 |
|
| 88 | 88 |
|
| 89 | 89 |
|
| 90 | 90 |
|
| 91 | 91 |
-- |
| 92 |
-- Name: TYPE taxonclass; Type: COMMENT; Schema: public; Owner: - |
|
| 93 |
-- |
|
| 94 |
|
|
| 95 |
|
|
| 96 |
|
|
| 97 |
|
|
| 98 |
-- |
|
| 92 | 99 |
-- Name: make_analytical_db(); Type: FUNCTION; Schema: public; Owner: - |
| 93 | 100 |
-- |
| 94 | 101 |
|
| ... | ... | |
| 840 | 847 |
variety text, |
| 841 | 848 |
forma text, |
| 842 | 849 |
cultivar text, |
| 850 |
otherranks text, |
|
| 843 | 851 |
accessioncode text |
| 844 | 852 |
); |
| 845 | 853 |
|
| ... | ... | |
| 859 | 867 |
|
| 860 | 868 |
|
| 861 | 869 |
-- |
| 870 |
-- Name: COLUMN taxonpath.otherranks; Type: COMMENT; Schema: public; Owner: - |
|
| 871 |
-- |
|
| 872 |
|
|
| 873 |
|
|
| 874 |
|
|
| 875 |
|
|
| 876 |
-- |
|
| 862 | 877 |
-- Name: analytical_db_view; Type: VIEW; Schema: public; Owner: - |
| 863 | 878 |
-- |
| 864 | 879 |
|
| schemas/vegbien.sql | ||
|---|---|---|
| 98 | 98 |
|
| 99 | 99 |
|
| 100 | 100 |
-- |
| 101 |
-- Name: role; Type: TYPE; Schema: public; Owner: - |
|
| 102 |
-- |
|
| 103 |
|
|
| 104 |
CREATE TYPE role AS ENUM ( |
|
| 105 |
'unknown', |
|
| 106 |
'collector', |
|
| 107 |
'identifier', |
|
| 108 |
'computer', |
|
| 109 |
'contributor' |
|
| 110 |
); |
|
| 111 |
|
|
| 112 |
|
|
| 113 |
-- |
|
| 114 |
-- Name: taxonclass; Type: TYPE; Schema: public; Owner: - |
|
| 115 |
-- |
|
| 116 |
|
|
| 117 |
CREATE TYPE taxonclass AS ENUM ( |
|
| 118 |
'tree', |
|
| 119 |
'shrub', |
|
| 120 |
'liana', |
|
| 121 |
'vine', |
|
| 122 |
'herb', |
|
| 123 |
'hemiepiphyte', |
|
| 124 |
'epiphyte', |
|
| 125 |
'grass', |
|
| 126 |
'forb', |
|
| 127 |
'moss', |
|
| 128 |
'lichen', |
|
| 129 |
'fungus', |
|
| 130 |
'floating aquatic', |
|
| 131 |
'submerged aquatic', |
|
| 132 |
'cultivated', |
|
| 133 |
'wild', |
|
| 134 |
'native', |
|
| 135 |
'exotic', |
|
| 136 |
'invasive', |
|
| 137 |
'escaped from captivity', |
|
| 138 |
'dominant', |
|
| 139 |
'vascular', |
|
| 140 |
'woody' |
|
| 141 |
); |
|
| 142 |
|
|
| 143 |
|
|
| 144 |
-- |
|
| 145 |
-- Name: TYPE taxonclass; Type: COMMENT; Schema: public; Owner: - |
|
| 146 |
-- |
|
| 147 |
|
|
| 148 |
COMMENT ON TYPE taxonclass IS '= growthform + establishmentmeans_dwc + some others'; |
|
| 149 |
|
|
| 150 |
|
|
| 151 |
-- |
|
| 152 | 101 |
-- Name: taxonrank; Type: TYPE; Schema: public; Owner: - |
| 153 | 102 |
-- |
| 154 | 103 |
|
| ... | ... | |
| 227 | 176 |
|
| 228 | 177 |
|
| 229 | 178 |
-- |
| 179 |
-- Name: rankedtaxonname; Type: TYPE; Schema: public; Owner: - |
|
| 180 |
-- |
|
| 181 |
|
|
| 182 |
CREATE TYPE rankedtaxonname AS ( |
|
| 183 |
rank taxonrank, |
|
| 184 |
verbatimrank text, |
|
| 185 |
taxonname text |
|
| 186 |
); |
|
| 187 |
|
|
| 188 |
|
|
| 189 |
-- |
|
| 190 |
-- Name: role; Type: TYPE; Schema: public; Owner: - |
|
| 191 |
-- |
|
| 192 |
|
|
| 193 |
CREATE TYPE role AS ENUM ( |
|
| 194 |
'unknown', |
|
| 195 |
'collector', |
|
| 196 |
'identifier', |
|
| 197 |
'computer', |
|
| 198 |
'contributor' |
|
| 199 |
); |
|
| 200 |
|
|
| 201 |
|
|
| 202 |
-- |
|
| 203 |
-- Name: taxonclass; Type: TYPE; Schema: public; Owner: - |
|
| 204 |
-- |
|
| 205 |
|
|
| 206 |
CREATE TYPE taxonclass AS ENUM ( |
|
| 207 |
'tree', |
|
| 208 |
'shrub', |
|
| 209 |
'liana', |
|
| 210 |
'vine', |
|
| 211 |
'herb', |
|
| 212 |
'hemiepiphyte', |
|
| 213 |
'epiphyte', |
|
| 214 |
'grass', |
|
| 215 |
'forb', |
|
| 216 |
'moss', |
|
| 217 |
'lichen', |
|
| 218 |
'fungus', |
|
| 219 |
'floating aquatic', |
|
| 220 |
'submerged aquatic', |
|
| 221 |
'cultivated', |
|
| 222 |
'wild', |
|
| 223 |
'native', |
|
| 224 |
'exotic', |
|
| 225 |
'invasive', |
|
| 226 |
'escaped from captivity', |
|
| 227 |
'dominant', |
|
| 228 |
'vascular', |
|
| 229 |
'woody' |
|
| 230 |
); |
|
| 231 |
|
|
| 232 |
|
|
| 233 |
-- |
|
| 234 |
-- Name: TYPE taxonclass; Type: COMMENT; Schema: public; Owner: - |
|
| 235 |
-- |
|
| 236 |
|
|
| 237 |
COMMENT ON TYPE taxonclass IS '= growthform + establishmentmeans_dwc + some others'; |
|
| 238 |
|
|
| 239 |
|
|
| 240 |
-- |
|
| 230 | 241 |
-- Name: make_analytical_db(); Type: FUNCTION; Schema: public; Owner: - |
| 231 | 242 |
-- |
| 232 | 243 |
|
| ... | ... | |
| 1120 | 1131 |
variety text, |
| 1121 | 1132 |
forma text, |
| 1122 | 1133 |
cultivar text, |
| 1134 |
otherranks rankedtaxonname[], |
|
| 1123 | 1135 |
accessioncode text |
| 1124 | 1136 |
); |
| 1125 | 1137 |
|
| ... | ... | |
| 1128 | 1140 |
-- Name: TABLE taxonpath; Type: COMMENT; Schema: public; Owner: - |
| 1129 | 1141 |
-- |
| 1130 | 1142 |
|
| 1131 |
COMMENT ON TABLE taxonpath IS 'To include a taxon name at a rank with no explicit column, create a taxon for it and point to it using taxon_id. To include multiple such names, chain the taxons together using parent_id, as a form of ordered linked list. Note that lower-level taxa should point to higher-level taxa.';
|
|
| 1143 |
COMMENT ON TABLE taxonpath IS 'To include a taxon name at a rank with no explicit column, add it to the otherranks array.';
|
|
| 1132 | 1144 |
|
| 1133 | 1145 |
|
| 1134 | 1146 |
-- |
| ... | ... | |
| 1139 | 1151 |
|
| 1140 | 1152 |
|
| 1141 | 1153 |
-- |
| 1154 |
-- Name: COLUMN taxonpath.otherranks; Type: COMMENT; Schema: public; Owner: - |
|
| 1155 |
-- |
|
| 1156 |
|
|
| 1157 |
COMMENT ON COLUMN taxonpath.otherranks IS 'Put ranks in path order, so that lower-level taxa come after higher-level taxa.'; |
|
| 1158 |
|
|
| 1159 |
|
|
| 1160 |
-- |
|
| 1142 | 1161 |
-- Name: analytical_db_view; Type: VIEW; Schema: public; Owner: - |
| 1143 | 1162 |
-- |
| 1144 | 1163 |
|
Also available in: Unified diff
schemas/vegbien.sql: taxonpath: Added otherranks array column to store ranked names without a named column. Documented that ranks with no named column should be stored in this new field instead of in a chain of taxons pointed to by taxon_id. This ensures that only the tree of life uses the taxon table.