Revision 537
Added by Aaron Marcuse-Kubitza almost 13 years ago
| vegbien.for_ERD.my.sql | ||
|---|---|---|
| 1 |
-- Existing tables |
|
| 2 |
|
|
| 3 |
CREATE TABLE aux_role |
|
| 4 |
( |
|
| 5 |
role_id int(11) NOT NULL AUTO_INCREMENT, |
|
| 6 |
rolecode character varying(30) NOT NULL, |
|
| 7 |
roledescription character varying(200), |
|
| 8 |
CONSTRAINT aux_role_pkey PRIMARY KEY (role_id ) |
|
| 9 |
); |
|
| 10 |
|
|
| 11 |
CREATE TABLE reference |
|
| 12 |
( |
|
| 13 |
reference_id int(11) NOT NULL AUTO_INCREMENT, |
|
| 14 |
shortname character varying(250), |
|
| 15 |
fulltext__ text, |
|
| 16 |
referencetype character varying(250) |
|
| 17 |
); |
|
| 18 |
|
|
| 19 |
CREATE TABLE party |
|
| 20 |
( |
|
| 21 |
party_id int(11) NOT NULL AUTO_INCREMENT, |
|
| 22 |
salutation character varying(20), |
|
| 23 |
givenname character varying(50), |
|
| 24 |
middlename character varying(50), |
|
| 25 |
surname character varying(50), |
|
| 26 |
organizationname character varying(100) |
|
| 27 |
); |
|
| 28 |
|
|
| 29 |
CREATE TABLE plantname |
|
| 30 |
( |
|
| 31 |
plantname_id int(11) NOT NULL AUTO_INCREMENT, |
|
| 32 |
plantname character varying(255) NOT NULL, |
|
| 33 |
reference_id int(11), |
|
| 34 |
dateentered timestamp DEFAULT now(), |
|
| 35 |
CONSTRAINT plantname_pkey PRIMARY KEY (plantname_id ), |
|
| 36 |
CONSTRAINT plantname_reference_id FOREIGN KEY (reference_id) |
|
| 37 |
REFERENCES reference (reference_id) MATCH SIMPLE |
|
| 38 |
ON UPDATE CASCADE ON DELETE CASCADE |
|
| 39 |
); |
|
| 40 |
|
|
| 41 |
CREATE TABLE plantconcept |
|
| 42 |
( |
|
| 43 |
plantconcept_id int(11) NOT NULL AUTO_INCREMENT, |
|
| 44 |
plantname_id int(11) NOT NULL, |
|
| 45 |
reference_id int(11) NOT NULL, |
|
| 46 |
plantname character varying(200), |
|
| 47 |
plantcode character varying(23), |
|
| 48 |
plantdescription text, |
|
| 49 |
d_obscount int(11), |
|
| 50 |
d_currentaccepted int(1), |
|
| 51 |
accessioncode character varying(255), |
|
| 52 |
CONSTRAINT plantconcept_pkey PRIMARY KEY (plantconcept_id ), |
|
| 53 |
CONSTRAINT plantconcept_plantname_id FOREIGN KEY (plantname_id) |
|
| 54 |
REFERENCES plantname (plantname_id) MATCH SIMPLE |
|
| 55 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 56 |
CONSTRAINT plantconcept_reference_id FOREIGN KEY (reference_id) |
|
| 57 |
REFERENCES reference (reference_id) MATCH SIMPLE |
|
| 58 |
ON UPDATE CASCADE ON DELETE CASCADE |
|
| 59 |
); |
|
| 60 |
|
|
| 61 |
CREATE TABLE namedplace |
|
| 62 |
( |
|
| 63 |
namedplace_id int(11) NOT NULL AUTO_INCREMENT, |
|
| 64 |
placesystem character varying(50), |
|
| 65 |
placename character varying(100) NOT NULL, |
|
| 66 |
placedescription text, |
|
| 67 |
placecode character varying(15), |
|
| 68 |
owner character varying(100), |
|
| 69 |
reference_id int(11), |
|
| 70 |
d_obscount int(11), |
|
| 71 |
accessioncode character varying(255), |
|
| 72 |
CONSTRAINT namedplace_pkey PRIMARY KEY (namedplace_id ), |
|
| 73 |
CONSTRAINT namedplace_reference_id FOREIGN KEY (reference_id) |
|
| 74 |
REFERENCES reference (reference_id) MATCH SIMPLE |
|
| 75 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 76 |
CONSTRAINT namedplace_keys UNIQUE (placesystem , placename ) |
|
| 77 |
); |
|
| 78 |
|
|
| 79 |
CREATE TABLE project |
|
| 80 |
( |
|
| 81 |
project_id int(11) NOT NULL AUTO_INCREMENT, |
|
| 82 |
projectname character varying(150) NOT NULL, |
|
| 83 |
projectdescription text, |
|
| 84 |
startdate timestamp, |
|
| 85 |
stopdate timestamp, |
|
| 86 |
d_obscount int(11), |
|
| 87 |
d_lastlocationaddeddate timestamp, |
|
| 88 |
accessioncode character varying(255), |
|
| 89 |
CONSTRAINT project_pkey PRIMARY KEY (project_id ), |
|
| 90 |
CONSTRAINT project_keys UNIQUE (projectname , startdate , stopdate ) |
|
| 91 |
); |
|
| 92 |
|
|
| 93 |
-- New tables |
|
| 94 |
|
|
| 1 | 95 |
CREATE TABLE location |
| 2 | 96 |
( |
| 3 | 97 |
location_id int(11) NOT NULL AUTO_INCREMENT, |
| ... | ... | |
| 139 | 233 |
CONSTRAINT stem_keys UNIQUE (individualplant_id , sourceid ) |
| 140 | 234 |
); |
| 141 | 235 |
|
| 236 |
CREATE TABLE specimen -- A herbarium specimen. Contains Darwin Core specimen data. |
|
| 237 |
( |
|
| 238 |
specimen_id int(11) NOT NULL AUTO_INCREMENT, |
|
| 239 |
individualplant_id int(11), |
|
| 240 |
reference_id int(11) NOT NULL, |
|
| 241 |
collectioncode_dwc character varying(255), |
|
| 242 |
catalognumber_dwc character varying(255), |
|
| 243 |
collectiondate timestamp, |
|
| 244 |
collector_id int(11), |
|
| 245 |
museum_id int(11), |
|
| 246 |
sourceaccessionnumber character varying(100), |
|
| 247 |
accessioncode character varying(255), |
|
| 248 |
taxonoccurrence_id int(11), |
|
| 249 |
CONSTRAINT specimen_pkey PRIMARY KEY (specimen_id ), |
|
| 250 |
CONSTRAINT specimen_collector_id FOREIGN KEY (collector_id) |
|
| 251 |
REFERENCES party (party_id) MATCH SIMPLE |
|
| 252 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 253 |
CONSTRAINT specimen_individualplant_id_fkey FOREIGN KEY (individualplant_id) |
|
| 254 |
REFERENCES individualplant (individualplant_id) MATCH SIMPLE |
|
| 255 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 256 |
CONSTRAINT specimen_museum_id FOREIGN KEY (museum_id) |
|
| 257 |
REFERENCES party (party_id) MATCH SIMPLE |
|
| 258 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 259 |
CONSTRAINT specimen_reference_id_fkey FOREIGN KEY (reference_id) |
|
| 260 |
REFERENCES reference (reference_id) MATCH SIMPLE |
|
| 261 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 262 |
CONSTRAINT specimen_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id) |
|
| 263 |
REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE |
|
| 264 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 265 |
CONSTRAINT specimen_keys UNIQUE (reference_id , collectioncode_dwc , catalognumber_dwc ) |
|
| 266 |
); |
|
| 267 |
|
|
| 142 | 268 |
CREATE TABLE taxondetermination -- VegBank's taxoninterpretation table. |
| 143 | 269 |
( |
| 144 | 270 |
taxondetermination_id int(11) NOT NULL AUTO_INCREMENT, |
| ... | ... | |
| 157 | 283 |
notespublic int(1), |
| 158 | 284 |
notesmgt int(1), |
| 159 | 285 |
revisions int(1), |
| 160 |
determinationdate timestamp with time zone NOT NULL,
|
|
| 286 |
determinationdate timestamp NOT NULL, |
|
| 161 | 287 |
emb_taxondetermination int(11), |
| 162 | 288 |
accessioncode character varying(255), |
| 163 | 289 |
CONSTRAINT taxondetermination_pkey PRIMARY KEY (taxondetermination_id ), |
| ... | ... | |
| 178 | 304 |
ON UPDATE CASCADE ON DELETE CASCADE |
| 179 | 305 |
); |
| 180 | 306 |
|
| 181 |
CREATE TABLE specimen -- A herbarium specimen. Contains Darwin Core specimen data.
|
|
| 307 |
CREATE TABLE sizeclass -- A range of size measurements used to aggregate organisms.
|
|
| 182 | 308 |
( |
| 183 |
specimen_id int(11) NOT NULL AUTO_INCREMENT, |
|
| 184 |
individualplant_id int(11), |
|
| 185 |
reference_id int(11) NOT NULL, |
|
| 186 |
collectioncode_dwc character varying(255), |
|
| 187 |
catalognumber_dwc character varying(255), |
|
| 188 |
collectiondate timestamp with time zone, |
|
| 189 |
collector_id int(11), |
|
| 190 |
museum_id int(11), |
|
| 191 |
sourceaccessionnumber character varying(100), |
|
| 309 |
sizeclass_id int(11) NOT NULL AUTO_INCREMENT, |
|
| 310 |
mindiameter double precision, |
|
| 311 |
diameteraccuracy double precision, |
|
| 312 |
minheight double precision, |
|
| 313 |
heightaccuracy double precision, |
|
| 314 |
maxdiameter double precision, |
|
| 315 |
maxheight double precision, |
|
| 192 | 316 |
accessioncode character varying(255), |
| 193 |
taxonoccurrence_id int(11), |
|
| 194 |
CONSTRAINT specimen_pkey PRIMARY KEY (specimen_id ), |
|
| 195 |
CONSTRAINT specimen_collector_id FOREIGN KEY (collector_id) |
|
| 196 |
REFERENCES party (party_id) MATCH SIMPLE |
|
| 197 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 198 |
CONSTRAINT specimen_individualplant_id_fkey FOREIGN KEY (individualplant_id) |
|
| 199 |
REFERENCES individualplant (individualplant_id) MATCH SIMPLE |
|
| 200 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 201 |
CONSTRAINT specimen_museum_id FOREIGN KEY (museum_id) |
|
| 202 |
REFERENCES party (party_id) MATCH SIMPLE |
|
| 203 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 204 |
CONSTRAINT specimen_reference_id_fkey FOREIGN KEY (reference_id) |
|
| 205 |
REFERENCES reference (reference_id) MATCH SIMPLE |
|
| 206 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 207 |
CONSTRAINT specimen_taxonoccurrence_id FOREIGN KEY (taxonoccurrence_id) |
|
| 208 |
REFERENCES taxonoccurrence (taxonoccurrence_id) MATCH SIMPLE |
|
| 209 |
ON UPDATE CASCADE ON DELETE CASCADE, |
|
| 210 |
CONSTRAINT specimen_keys UNIQUE (reference_id , collectioncode_dwc , catalognumber_dwc ) |
|
| 317 |
CONSTRAINT sizeclass_pkey PRIMARY KEY (sizeclass_id ) |
|
| 211 | 318 |
); |
| 212 | 319 |
|
| 213 | 320 |
CREATE TABLE taxonbin -- Defines a set of aggregate measurements. Used by aggregateoccurrence to define how measurements were aggregated. |
| ... | ... | |
| 223 | 330 |
REFERENCES sizeclass (sizeclass_id) MATCH SIMPLE |
| 224 | 331 |
ON UPDATE CASCADE ON DELETE CASCADE |
| 225 | 332 |
); |
| 226 |
|
|
| 227 |
CREATE TABLE sizeclass -- A range of size measurements used to aggregate organisms. |
|
| 228 |
( |
|
| 229 |
sizeclass_id int(11) NOT NULL AUTO_INCREMENT, |
|
| 230 |
mindiameter double precision, |
|
| 231 |
diameteraccuracy double precision, |
|
| 232 |
minheight double precision, |
|
| 233 |
heightaccuracy double precision, |
|
| 234 |
maxdiameter double precision, |
|
| 235 |
maxheight double precision, |
|
| 236 |
accessioncode character varying(255), |
|
| 237 |
CONSTRAINT sizeclass_pkey PRIMARY KEY (sizeclass_id ) |
|
| 238 |
); |
|
Also available in: Unified diff
PostgreSQL-MySQL.csv: Use \g<0> instead of \0 in replacements because \0 not supported. Replace fulltext with fulltext__ (note fulltext_ doesn't work).