Project

General

Profile

1 7545 aaronmk
SET standard_conforming_strings = off;
2
SET escape_string_warning = off;
3
-- MySQL dump 10.13  Distrib 5.1.66, for debian-linux-gnu (x86_64)
4
--
5
-- Host: localhost    Database: ua_herbarium
6
-- ------------------------------------------------------
7
-- Server version	5.1.66-0+squeeze1
8
9
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
10
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
11
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
12
SET NAMES 'latin1';
13
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
14
/*!40103 SET TIME_ZONE='+00:00' */;
15
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
16
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
17
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO,POSTGRESQL' */;
18
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
19
20
--
21
-- Table structure for table "NPS_records_b4_20120628"
22
--
23
24
DROP TABLE IF EXISTS "NPS_records_b4_20120628";
25
/*!40101 SET @saved_cs_client     = @@character_set_client */;
26
/*!40101 SET character_set_client = utf8 */;
27
CREATE TABLE "NPS_records_b4_20120628" (
28
  "dbsn" integer NOT NULL DEFAULT '0',
29
  "accession_number" integer DEFAULT NULL,
30
  "taxa_id" integer DEFAULT NULL,
31
  "cf" varchar(10) DEFAULT NULL,
32
  "determiner_annotator" varchar(100) DEFAULT NULL,
33
  "initial_taxonomy" varchar(200) DEFAULT NULL,
34
  "specimen_notes" varchar(250) DEFAULT NULL,
35
  "plant_description" varchar(1000) DEFAULT NULL,
36
  "phenology" varchar(50) DEFAULT NULL,
37
  "chromosome_number" varchar(50) DEFAULT NULL,
38
  "habitat" varchar(600) DEFAULT NULL,
39
  "assoc_species" varchar(1300) DEFAULT NULL,
40
  "first_collector" varchar(50) DEFAULT NULL,
41
  "collnumprefix" varchar(10) DEFAULT NULL,
42
  "collnumber" integer DEFAULT NULL,
43
  "collnumsuffix" varchar(10) DEFAULT NULL,
44
  "collnumber_full" varchar(100) DEFAULT NULL,
45
  "other_collectors" varchar(200) DEFAULT NULL,
46 10443 aaronmk
  "date_collected" text/*date*/ DEFAULT NULL,
47 7545 aaronmk
  "month_collected" varchar(15) DEFAULT NULL,
48
  "day_collected" integer DEFAULT NULL,
49
  "year_collected" integer DEFAULT NULL,
50
  "country" varchar(50) DEFAULT NULL,
51
  "state_province" varchar(50) DEFAULT NULL,
52
  "county_parish" varchar(50) DEFAULT NULL,
53
  "national_forest" varchar(50) DEFAULT NULL,
54
  "locality" varchar(1000) DEFAULT NULL,
55
  "latdeg" integer DEFAULT NULL,
56
  "latmin" decimal(12,8) DEFAULT NULL,
57
  "latsec" decimal(12,8) DEFAULT NULL,
58
  "latns" varchar(2) DEFAULT NULL,
59
  "lat_decimal" decimal(12,8) DEFAULT NULL,
60
  "longdeg" integer DEFAULT NULL,
61
  "longmin" decimal(12,8) DEFAULT NULL,
62
  "longsec" decimal(12,8) DEFAULT NULL,
63
  "longew" varchar(2) DEFAULT NULL,
64
  "long_decimal" decimal(12,8) DEFAULT NULL,
65
  "utm_zoning" varchar(5) DEFAULT NULL,
66
  "utm_easting" integer DEFAULT NULL,
67
  "utm_northing" integer DEFAULT NULL,
68
  "datum" varchar(20) DEFAULT NULL,
69
  "coordinates_est" varchar(100) DEFAULT NULL,
70
  "bbounding" varchar(10) DEFAULT NULL,
71
  "township" varchar(50) DEFAULT NULL,
72
  "range" varchar(50) DEFAULT NULL,
73
  "section" varchar(50) DEFAULT NULL,
74
  "sec_details" varchar(50) DEFAULT NULL,
75
  "elevation_m" integer DEFAULT NULL,
76
  "elev_max_m" integer DEFAULT NULL,
77
  "elevation_ft" integer DEFAULT NULL,
78
  "elev_max_ft" integer DEFAULT NULL,
79
  "elev_est" varchar(20) DEFAULT NULL,
80
  "cultivated" varchar(10) DEFAULT NULL,
81
  "label_quantity" integer DEFAULT NULL,
82
  "project" varchar(50) DEFAULT NULL,
83
  "dups_to" varchar(180) DEFAULT NULL,
84
  "herb_acro" varchar(50) DEFAULT NULL,
85 10443 aaronmk
  "datelastmodified" text/*datetime*/ NOT NULL,
86 7545 aaronmk
  "entered_by" varchar(60) DEFAULT NULL,
87
  "download_source" varchar(50) DEFAULT NULL,
88
  "old_dbsn" integer DEFAULT NULL,
89
  "verified" varchar(50) DEFAULT NULL,
90
  "coord_err" integer DEFAULT NULL,
91
  "coord_confidence" integer DEFAULT NULL,
92
  "land_ownership" varchar(100) DEFAULT NULL,
93
  "coord_source" varchar(100) DEFAULT NULL,
94
  "georeferenceverificationstatus" varchar(150) DEFAULT NULL,
95 10443 aaronmk
  "date_entered" text/*timestamp*/ NOT NULL DEFAULT '0000-00-00 00:00:00',
96 10444 aaronmk
  "is_duplicate" integer DEFAULT NULL /*COMMENT '=1 if possible duplicate record; otherwise 0 or null'*/,
97
  "deleted" integer NOT NULL DEFAULT '0' /*COMMENT '=1 if deleted; otherwise 0'*/,
98
  "deleted_reason" varchar(250) DEFAULT NULL /*COMMENT 'Reason for flagging for deletion'*/,
99
  "determination_date" text/*date*/ DEFAULT NULL /*COMMENT 'Keep this field DATE not DATETIME'*/,
100 7545 aaronmk
  "det_day" integer DEFAULT NULL,
101
  "det_mo" integer DEFAULT NULL,
102
  "det_yr" integer DEFAULT NULL,
103
  "deaccessioned" integer NOT NULL DEFAULT '0',
104 10444 aaronmk
  "dupes" integer DEFAULT '1' /*COMMENT 'Total duplicates per specimen'*/,
105
  "sheets" integer DEFAULT '1' /*COMMENT 'Total sheets per specimen'*/,
106
  "collnum_numeric" double precision DEFAULT NULL /*COMMENT 'Artificial decimal value based on collnumprefix, collnumber & collnumsuffix; for natural sorting'*/,
107
  "needs_label" integer DEFAULT '0' /*COMMENT '1=Yes, needs label; 0=No, already labelled'*/,
108 7545 aaronmk
  "is_arboretum" integer DEFAULT '0',
109
  "arboretum_id" integer DEFAULT NULL,
110
  "notes_display" integer DEFAULT '0',
111
  "elev_est_display" integer DEFAULT '0',
112 10444 aaronmk
  "security" integer NOT NULL DEFAULT '1' /*COMMENT '1=no security, 2=hide locality info'*/,
113
  "temp_dbsn" integer DEFAULT NULL /*COMMENT 'used during bulk record transfer to update FKs; afterwards reset to NULL'*/
114 7545 aaronmk
);
115
/*!40101 SET character_set_client = @saved_cs_client */;
116
117
--
118
-- Table structure for table "chotomouskey"
119
--
120
121
DROP TABLE IF EXISTS "chotomouskey";
122
/*!40101 SET @saved_cs_client     = @@character_set_client */;
123
/*!40101 SET character_set_client = utf8 */;
124
CREATE TABLE "chotomouskey" (
125
  "stmtid" integer NOT NULL,
126
  "statement" varchar(300) NOT NULL,
127
  "nodeid" integer NOT NULL,
128
  "parentid" integer NOT NULL,
129
  "tid" integer DEFAULT NULL,
130
  "notes" varchar(250) DEFAULT NULL,
131 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
132 7545 aaronmk
  PRIMARY KEY ("stmtid"),
133
  /*KEY "FK_chotomouskey_taxa" ("tid")*/CHECK (true),
134
  /*CONSTRAINT "FK_chotomouskey_taxa" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID")*/CHECK (true)
135
);
136
/*!40101 SET character_set_client = @saved_cs_client */;
137
138
--
139
-- Table structure for table "fmchecklists"
140
--
141
142
DROP TABLE IF EXISTS "fmchecklists";
143
/*!40101 SET @saved_cs_client     = @@character_set_client */;
144
/*!40101 SET character_set_client = utf8 */;
145
CREATE TABLE "fmchecklists" (
146
  "CLID" integer NOT NULL,
147
  "Name" varchar(100) NOT NULL,
148
  "Title" varchar(150) DEFAULT NULL,
149
  "Locality" varchar(500) DEFAULT NULL,
150
  "Publication" varchar(500) DEFAULT NULL,
151
  "Abstract" text,
152
  "Authors" varchar(250) DEFAULT NULL,
153
  "Type" varchar(50) DEFAULT 'static',
154
  "dynamicsql" varchar(250) DEFAULT NULL,
155
  "Parent" varchar(50) DEFAULT NULL,
156
  "parentclid" integer DEFAULT NULL,
157
  "Notes" varchar(500) DEFAULT NULL,
158
  "LatCentroid" double precision DEFAULT NULL,
159
  "LongCentroid" double precision DEFAULT NULL,
160
  "pointradiusmeters" integer DEFAULT NULL,
161
  "footprintWKT" text,
162
  "percenteffort" integer DEFAULT NULL,
163
  "Access" varchar(45) DEFAULT 'private',
164
  "uid" integer DEFAULT NULL,
165
  "SortSequence" integer NOT NULL DEFAULT '50',
166
  "expiration" integer DEFAULT NULL,
167 10443 aaronmk
  "DateLastModified" text/*datetime*/ DEFAULT NULL,
168
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
169 7545 aaronmk
  PRIMARY KEY ("CLID"),
170
  /*KEY "FK_checklists_uid" ("uid")*/CHECK (true),
171
  /*KEY "name" ("Name","Type")*/CHECK (true),
172
  /*CONSTRAINT "FK_checklists_uid" FOREIGN KEY ("uid") REFERENCES "users" ("uid")*/CHECK (true)
173
);
174
/*!40101 SET character_set_client = @saved_cs_client */;
175
176
--
177
-- Table structure for table "fmchklstcoordinates"
178
--
179
180
DROP TABLE IF EXISTS "fmchklstcoordinates";
181
/*!40101 SET @saved_cs_client     = @@character_set_client */;
182
/*!40101 SET character_set_client = utf8 */;
183
CREATE TABLE "fmchklstcoordinates" (
184
  "chklstcoordid" integer NOT NULL,
185
  "clid" integer NOT NULL,
186
  "tid" integer NOT NULL,
187
  "decimallatitude" double precision NOT NULL,
188
  "decimallongitude" double precision NOT NULL,
189
  "notes" varchar(250) DEFAULT NULL,
190 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
191 7545 aaronmk
  PRIMARY KEY ("chklstcoordid"),
192
  /*CONSTRAINT "IndexUnique" */UNIQUE ("clid","tid","decimallatitude","decimallongitude"),
193
  /*KEY "FKchklsttaxalink" ("clid","tid")*/CHECK (true),
194
  /*CONSTRAINT "FKchklsttaxalink" FOREIGN KEY ("clid", "tid") REFERENCES "fmchklsttaxalink" ("CLID", "TID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
195
);
196
/*!40101 SET character_set_client = @saved_cs_client */;
197
198
--
199
-- Table structure for table "fmchklstprojlink"
200
--
201
202
DROP TABLE IF EXISTS "fmchklstprojlink";
203
/*!40101 SET @saved_cs_client     = @@character_set_client */;
204
/*!40101 SET character_set_client = utf8 */;
205
CREATE TABLE "fmchklstprojlink" (
206
  "pid" integer NOT NULL,
207
  "clid" integer NOT NULL,
208 10443 aaronmk
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
209 7545 aaronmk
  PRIMARY KEY ("pid","clid"),
210
  /*KEY "FK_chklst" ("clid")*/CHECK (true),
211
  /*CONSTRAINT "FK_chklstprojlink_clid" FOREIGN KEY ("clid") REFERENCES "fmchecklists" ("CLID")*/CHECK (true),
212
  /*CONSTRAINT "FK_chklstprojlink_proj" FOREIGN KEY ("pid") REFERENCES "fmprojects" ("pid")*/CHECK (true)
213
);
214
/*!40101 SET character_set_client = @saved_cs_client */;
215
216
--
217
-- Table structure for table "fmchklsttaxalink"
218
--
219
220
DROP TABLE IF EXISTS "fmchklsttaxalink";
221
/*!40101 SET @saved_cs_client     = @@character_set_client */;
222
/*!40101 SET character_set_client = utf8 */;
223
CREATE TABLE "fmchklsttaxalink" (
224
  "TID" integer NOT NULL DEFAULT '0',
225
  "CLID" integer NOT NULL DEFAULT '0',
226
  "morphospecies" varchar(45) NOT NULL DEFAULT '',
227
  "familyoverride" varchar(50) DEFAULT NULL,
228
  "Habitat" varchar(250) DEFAULT NULL,
229
  "Abundance" varchar(50) DEFAULT NULL,
230
  "Notes" varchar(2000) DEFAULT NULL,
231
  "explicitExclude" integer DEFAULT NULL,
232
  "source" varchar(250) DEFAULT NULL,
233 10444 aaronmk
  "Nativity" varchar(50) DEFAULT NULL /*COMMENT 'native, introducted'*/,
234 7545 aaronmk
  "Endemic" varchar(25) DEFAULT NULL,
235
  "internalnotes" varchar(250) DEFAULT NULL,
236 10443 aaronmk
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
237 7545 aaronmk
  PRIMARY KEY ("TID","CLID","morphospecies"),
238
  /*KEY "FK_chklsttaxalink_cid" ("CLID")*/CHECK (true),
239
  /*CONSTRAINT "FK_chklsttaxalink_cid" FOREIGN KEY ("CLID") REFERENCES "fmchecklists" ("CLID")*/CHECK (true),
240
  /*CONSTRAINT "FK_chklsttaxalink_tid" FOREIGN KEY ("TID") REFERENCES "taxa" ("TID")*/CHECK (true)
241
);
242
/*!40101 SET character_set_client = @saved_cs_client */;
243
244
--
245
-- Table structure for table "fmcltaxacomments"
246
--
247
248
DROP TABLE IF EXISTS "fmcltaxacomments";
249
/*!40101 SET @saved_cs_client     = @@character_set_client */;
250
/*!40101 SET character_set_client = utf8 */;
251
CREATE TABLE "fmcltaxacomments" (
252
  "cltaxacommentsid" integer NOT NULL,
253
  "clid" integer NOT NULL,
254
  "tid" integer NOT NULL,
255
  "comment" text NOT NULL,
256
  "uid" integer NOT NULL,
257
  "ispublic" integer NOT NULL DEFAULT '1',
258
  "parentid" integer DEFAULT NULL,
259 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
260 7545 aaronmk
  PRIMARY KEY ("cltaxacommentsid"),
261
  /*KEY "FK_clcomment_users" ("uid")*/CHECK (true),
262
  /*KEY "FK_clcomment_cltaxa" ("clid","tid")*/CHECK (true),
263
  /*CONSTRAINT "FK_clcomment_cltaxa" FOREIGN KEY ("clid", "tid") REFERENCES "fmchklsttaxalink" ("CLID", "TID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
264
  /*CONSTRAINT "FK_clcomment_users" FOREIGN KEY ("uid") REFERENCES "users" ("uid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
265
);
266
/*!40101 SET character_set_client = @saved_cs_client */;
267
268
--
269
-- Table structure for table "fmdynamicchecklists"
270
--
271
272
DROP TABLE IF EXISTS "fmdynamicchecklists";
273
/*!40101 SET @saved_cs_client     = @@character_set_client */;
274
/*!40101 SET character_set_client = utf8 */;
275
CREATE TABLE "fmdynamicchecklists" (
276
  "dynclid" integer NOT NULL,
277
  "name" varchar(50) DEFAULT NULL,
278
  "details" varchar(250) DEFAULT NULL,
279
  "uid" varchar(45) DEFAULT NULL,
280
  "type" varchar(45) NOT NULL DEFAULT 'DynamicList',
281
  "notes" varchar(250) DEFAULT NULL,
282 10443 aaronmk
  "expiration" text/*datetime*/ NOT NULL,
283
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
284 7545 aaronmk
  PRIMARY KEY ("dynclid")
285
);
286
/*!40101 SET character_set_client = @saved_cs_client */;
287
288
--
289
-- Table structure for table "fmdyncltaxalink"
290
--
291
292
DROP TABLE IF EXISTS "fmdyncltaxalink";
293
/*!40101 SET @saved_cs_client     = @@character_set_client */;
294
/*!40101 SET character_set_client = utf8 */;
295
CREATE TABLE "fmdyncltaxalink" (
296
  "dynclid" integer NOT NULL,
297
  "tid" integer NOT NULL,
298 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
299 7545 aaronmk
  PRIMARY KEY ("dynclid","tid"),
300
  /*KEY "FK_dyncltaxalink_taxa" ("tid")*/CHECK (true),
301
  /*CONSTRAINT "FK_dyncltaxalink_dynclid" FOREIGN KEY ("dynclid") REFERENCES "fmdynamicchecklists" ("dynclid") ON DELETE CASCADE*/CHECK (true),
302
  /*CONSTRAINT "FK_dyncltaxalink_taxa" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID") ON DELETE CASCADE*/CHECK (true)
303
);
304
/*!40101 SET character_set_client = @saved_cs_client */;
305
306
--
307
-- Table structure for table "fmprojects"
308
--
309
310
DROP TABLE IF EXISTS "fmprojects";
311
/*!40101 SET @saved_cs_client     = @@character_set_client */;
312
/*!40101 SET character_set_client = utf8 */;
313
CREATE TABLE "fmprojects" (
314
  "pid" integer NOT NULL,
315
  "projname" varchar(45) NOT NULL,
316
  "displayname" varchar(150) DEFAULT NULL,
317
  "managers" varchar(150) DEFAULT NULL,
318
  "briefdescription" varchar(300) DEFAULT NULL,
319
  "fulldescription" varchar(2000) DEFAULT NULL,
320
  "notes" varchar(250) DEFAULT NULL,
321
  "occurrencesearch" integer NOT NULL DEFAULT '0',
322
  "ispublic" integer NOT NULL DEFAULT '0',
323
  "parentpid" integer DEFAULT NULL,
324
  "SortSequence" integer NOT NULL DEFAULT '50',
325 10443 aaronmk
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
326 7545 aaronmk
  PRIMARY KEY ("pid"),
327
  /*KEY "FK_parentpid_proj" ("parentpid")*/CHECK (true),
328
  /*CONSTRAINT "FK_parentpid_proj" FOREIGN KEY ("parentpid") REFERENCES "fmprojects" ("pid") ON DELETE NO ACTION ON UPDATE NO ACTION*/CHECK (true)
329
);
330
/*!40101 SET character_set_client = @saved_cs_client */;
331
332
--
333
-- Table structure for table "fmvouchers"
334
--
335
336
DROP TABLE IF EXISTS "fmvouchers";
337
/*!40101 SET @saved_cs_client     = @@character_set_client */;
338
/*!40101 SET character_set_client = utf8 */;
339
CREATE TABLE "fmvouchers" (
340
  "TID" integer NOT NULL,
341
  "CLID" integer NOT NULL,
342
  "occid" integer NOT NULL,
343
  "Collector" varchar(100) NOT NULL,
344
  "editornotes" varchar(50) DEFAULT NULL,
345
  "Notes" varchar(250) DEFAULT NULL,
346 10443 aaronmk
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
347 7545 aaronmk
  PRIMARY KEY ("occid","CLID","TID"),
348
  /*KEY "chklst_taxavouchers" ("TID","CLID")*/CHECK (true),
349
  /*CONSTRAINT "FK_fmvouchers_occ" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid")*/CHECK (true),
350
  /*CONSTRAINT "FK_vouchers_cl" FOREIGN KEY ("TID", "CLID") REFERENCES "fmchklsttaxalink" ("TID", "CLID") ON UPDATE CASCADE*/CHECK (true)
351
);
352
/*!40101 SET character_set_client = @saved_cs_client */;
353
354
--
355
-- Table structure for table "geothescontinent"
356
--
357
358
DROP TABLE IF EXISTS "geothescontinent";
359
/*!40101 SET @saved_cs_client     = @@character_set_client */;
360
/*!40101 SET character_set_client = utf8 */;
361
CREATE TABLE "geothescontinent" (
362
  "gtcid" integer NOT NULL,
363
  "continentterm" varchar(45) NOT NULL,
364
  "abbreviation" varchar(45) DEFAULT NULL,
365
  "code" varchar(45) DEFAULT NULL,
366
  "lookupterm" integer NOT NULL DEFAULT '1',
367
  "acceptedid" integer DEFAULT NULL,
368 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP,
369 7545 aaronmk
  PRIMARY KEY ("gtcid"),
370
  /*KEY "FK_geothescontinent_accepted_idx" ("acceptedid")*/CHECK (true),
371
  /*CONSTRAINT "FK_geothescontinent_accepted" FOREIGN KEY ("acceptedid") REFERENCES "geothescontinent" ("gtcid")*/CHECK (true)
372
);
373
/*!40101 SET character_set_client = @saved_cs_client */;
374
375
--
376
-- Table structure for table "geothescountry"
377
--
378
379
DROP TABLE IF EXISTS "geothescountry";
380
/*!40101 SET @saved_cs_client     = @@character_set_client */;
381
/*!40101 SET character_set_client = utf8 */;
382
CREATE TABLE "geothescountry" (
383
  "gtcid" integer NOT NULL,
384
  "countryterm" varchar(45) NOT NULL,
385
  "abbreviation" varchar(45) DEFAULT NULL,
386
  "iso" varchar(2) DEFAULT NULL,
387
  "iso3" varchar(3) DEFAULT NULL,
388
  "numcode" integer DEFAULT NULL,
389
  "lookupterm" integer NOT NULL DEFAULT '1',
390
  "acceptedid" integer DEFAULT NULL,
391
  "continentid" integer DEFAULT NULL,
392 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP,
393 7545 aaronmk
  PRIMARY KEY ("gtcid"),
394
  /*KEY "FK_geothescountry__idx" ("continentid")*/CHECK (true),
395
  /*KEY "FK_geothescountry_parent_idx" ("acceptedid")*/CHECK (true),
396
  /*CONSTRAINT "FK_geothescountry_accepted" FOREIGN KEY ("acceptedid") REFERENCES "geothescountry" ("gtcid")*/CHECK (true),
397
  /*CONSTRAINT "FK_geothescountry_gtcid" FOREIGN KEY ("continentid") REFERENCES "geothescontinent" ("gtcid")*/CHECK (true)
398
);
399
/*!40101 SET character_set_client = @saved_cs_client */;
400
401
--
402
-- Table structure for table "geothescounty"
403
--
404
405
DROP TABLE IF EXISTS "geothescounty";
406
/*!40101 SET @saved_cs_client     = @@character_set_client */;
407
/*!40101 SET character_set_client = utf8 */;
408
CREATE TABLE "geothescounty" (
409
  "gtcoid" integer NOT NULL,
410
  "countyterm" varchar(45) NOT NULL,
411
  "abbreviation" varchar(45) DEFAULT NULL,
412
  "code" varchar(45) DEFAULT NULL,
413
  "lookupterm" integer NOT NULL DEFAULT '1',
414
  "acceptedid" integer DEFAULT NULL,
415
  "stateid" integer DEFAULT NULL,
416 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP,
417 7545 aaronmk
  PRIMARY KEY ("gtcoid"),
418
  /*KEY "FK_geothescounty_state_idx" ("stateid")*/CHECK (true),
419
  /*KEY "FK_geothescounty_accepted_idx" ("acceptedid")*/CHECK (true),
420
  /*CONSTRAINT "FK_geothescounty_accepted" FOREIGN KEY ("acceptedid") REFERENCES "geothescounty" ("gtcoid")*/CHECK (true),
421
  /*CONSTRAINT "FK_geothescounty_state" FOREIGN KEY ("stateid") REFERENCES "geothesstateprovince" ("gtspid")*/CHECK (true)
422
);
423
/*!40101 SET character_set_client = @saved_cs_client */;
424
425
--
426
-- Table structure for table "geothesmunicipality"
427
--
428
429
DROP TABLE IF EXISTS "geothesmunicipality";
430
/*!40101 SET @saved_cs_client     = @@character_set_client */;
431
/*!40101 SET character_set_client = utf8 */;
432
CREATE TABLE "geothesmunicipality" (
433
  "gtmid" integer NOT NULL,
434
  "municipalityterm" varchar(45) NOT NULL,
435
  "abbreviation" varchar(45) DEFAULT NULL,
436
  "code" varchar(45) DEFAULT NULL,
437
  "lookupterm" integer NOT NULL DEFAULT '1',
438
  "acceptedid" integer DEFAULT NULL,
439
  "countyid" integer DEFAULT NULL,
440 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP,
441 7545 aaronmk
  PRIMARY KEY ("gtmid"),
442
  /*KEY "FK_geothesmunicipality_county_idx" ("countyid")*/CHECK (true),
443
  /*KEY "FK_geothesmunicipality_accepted_idx" ("acceptedid")*/CHECK (true),
444
  /*CONSTRAINT "FK_geothesmunicipality_accepted" FOREIGN KEY ("acceptedid") REFERENCES "geothescounty" ("gtcoid")*/CHECK (true),
445
  /*CONSTRAINT "FK_geothesmunicipality_county" FOREIGN KEY ("countyid") REFERENCES "geothescounty" ("gtcoid")*/CHECK (true)
446
);
447
/*!40101 SET character_set_client = @saved_cs_client */;
448
449
--
450
-- Table structure for table "geothesstateprovince"
451
--
452
453
DROP TABLE IF EXISTS "geothesstateprovince";
454
/*!40101 SET @saved_cs_client     = @@character_set_client */;
455
/*!40101 SET character_set_client = utf8 */;
456
CREATE TABLE "geothesstateprovince" (
457
  "gtspid" integer NOT NULL,
458
  "stateterm" varchar(45) NOT NULL,
459
  "abbreviation" varchar(45) DEFAULT NULL,
460
  "code" varchar(45) DEFAULT NULL,
461
  "lookupterm" integer NOT NULL DEFAULT '1',
462
  "acceptedid" integer DEFAULT NULL,
463
  "countryid" integer DEFAULT NULL,
464 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP,
465 7545 aaronmk
  PRIMARY KEY ("gtspid"),
466
  /*KEY "FK_geothesstate_country_idx" ("countryid")*/CHECK (true),
467
  /*KEY "FK_geothesstate_accepted_idx" ("acceptedid")*/CHECK (true),
468
  /*CONSTRAINT "FK_geothesstate_accepted" FOREIGN KEY ("acceptedid") REFERENCES "geothesstateprovince" ("gtspid")*/CHECK (true),
469
  /*CONSTRAINT "FK_geothesstate_country" FOREIGN KEY ("countryid") REFERENCES "geothescountry" ("gtcid")*/CHECK (true)
470
);
471
/*!40101 SET character_set_client = @saved_cs_client */;
472
473
--
474
-- Table structure for table "glossary"
475
--
476
477
DROP TABLE IF EXISTS "glossary";
478
/*!40101 SET @saved_cs_client     = @@character_set_client */;
479
/*!40101 SET character_set_client = utf8 */;
480
CREATE TABLE "glossary" (
481
  "glossid" integer NOT NULL,
482
  "term" varchar(45) NOT NULL,
483
  "definition" varchar(600) NOT NULL,
484
  "language" varchar(45) NOT NULL DEFAULT 'English',
485
  "source" varchar(45) DEFAULT NULL,
486
  "notes" varchar(250) DEFAULT NULL,
487
  "uid" integer DEFAULT NULL,
488 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
489 7545 aaronmk
  PRIMARY KEY ("glossid"),
490
  /*CONSTRAINT "Index_term" */UNIQUE ("term")
491
);
492
/*!40101 SET character_set_client = @saved_cs_client */;
493
494
--
495
-- Table structure for table "glossarycatagories"
496
--
497
498
DROP TABLE IF EXISTS "glossarycatagories";
499
/*!40101 SET @saved_cs_client     = @@character_set_client */;
500
/*!40101 SET character_set_client = utf8 */;
501
CREATE TABLE "glossarycatagories" (
502
  "catid" integer NOT NULL,
503
  "catagory" varchar(45) NOT NULL,
504
  "language" varchar(45) NOT NULL DEFAULT 'English',
505 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
506 7545 aaronmk
  PRIMARY KEY ("catid")
507
);
508
/*!40101 SET character_set_client = @saved_cs_client */;
509
510
--
511
-- Table structure for table "glossarycatlink"
512
--
513
514
DROP TABLE IF EXISTS "glossarycatlink";
515
/*!40101 SET @saved_cs_client     = @@character_set_client */;
516
/*!40101 SET character_set_client = utf8 */;
517
CREATE TABLE "glossarycatlink" (
518
  "glossid" integer NOT NULL,
519
  "catid" integer NOT NULL,
520 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
521 7545 aaronmk
  PRIMARY KEY ("glossid","catid"),
522
  /*KEY "FK_glossarycatlink_cat" ("catid")*/CHECK (true),
523
  /*CONSTRAINT "glossarycatlink_ibfk_1" FOREIGN KEY ("catid") REFERENCES "glossarycatagories" ("catid")*/CHECK (true),
524
  /*CONSTRAINT "glossarycatlink_ibfk_2" FOREIGN KEY ("glossid") REFERENCES "glossary" ("glossid")*/CHECK (true)
525
);
526
/*!40101 SET character_set_client = @saved_cs_client */;
527
528
--
529
-- Table structure for table "glossaryimages"
530
--
531
532
DROP TABLE IF EXISTS "glossaryimages";
533
/*!40101 SET @saved_cs_client     = @@character_set_client */;
534
/*!40101 SET character_set_client = utf8 */;
535
CREATE TABLE "glossaryimages" (
536
  "glimgid" integer NOT NULL,
537
  "glossid" integer NOT NULL,
538
  "url" varchar(45) NOT NULL,
539
  "structures" varchar(150) DEFAULT NULL,
540
  "notes" varchar(250) DEFAULT NULL,
541
  "uid" integer DEFAULT NULL,
542 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
543 7545 aaronmk
  PRIMARY KEY ("glimgid"),
544
  /*KEY "FK_glossaryimages_gloss" ("glossid")*/CHECK (true),
545
  /*CONSTRAINT "glossaryimages_ibfk_1" FOREIGN KEY ("glossid") REFERENCES "glossary" ("glossid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
546
);
547
/*!40101 SET character_set_client = @saved_cs_client */;
548
549
--
550
-- Table structure for table "imageannotations"
551
--
552
553
DROP TABLE IF EXISTS "imageannotations";
554
/*!40101 SET @saved_cs_client     = @@character_set_client */;
555
/*!40101 SET character_set_client = utf8 */;
556
CREATE TABLE "imageannotations" (
557
  "tid" integer DEFAULT NULL,
558
  "imgid" integer NOT NULL DEFAULT '0',
559 10443 aaronmk
  "AnnDate" text/*datetime*/ NOT NULL DEFAULT '0000-00-00 00:00:00',
560 7545 aaronmk
  "Annotator" varchar(100) DEFAULT NULL,
561 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
562 7545 aaronmk
  PRIMARY KEY ("imgid","AnnDate"),
563
  /*KEY "TID" ("tid")*/CHECK (true),
564
  /*CONSTRAINT "FK_resourceannotations_imgid" FOREIGN KEY ("imgid") REFERENCES "images" ("imgid")*/CHECK (true),
565
  /*CONSTRAINT "FK_resourceannotations_tid" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID")*/CHECK (true)
566
);
567
/*!40101 SET character_set_client = @saved_cs_client */;
568
569
--
570
-- Table structure for table "images"
571
--
572
573
DROP TABLE IF EXISTS "images";
574
/*!40101 SET @saved_cs_client     = @@character_set_client */;
575
/*!40101 SET character_set_client = utf8 */;
576
CREATE TABLE "images" (
577
  "imgid" integer NOT NULL,
578
  "tid" integer DEFAULT NULL,
579
  "url" varchar(255) NOT NULL,
580
  "thumbnailurl" varchar(255) DEFAULT NULL,
581
  "originalurl" varchar(255) DEFAULT NULL,
582
  "photographer" varchar(100) DEFAULT NULL,
583
  "photographeruid" integer DEFAULT NULL,
584
  "imagetype" varchar(50) DEFAULT NULL,
585
  "caption" varchar(100) DEFAULT NULL,
586
  "owner" varchar(250) DEFAULT NULL,
587
  "sourceurl" varchar(255) DEFAULT NULL,
588
  "copyright" varchar(255) DEFAULT NULL,
589
  "locality" varchar(250) DEFAULT NULL,
590
  "occid" integer DEFAULT NULL,
591
  "notes" varchar(350) DEFAULT NULL,
592
  "anatomy" varchar(100) DEFAULT NULL,
593
  "username" varchar(45) DEFAULT NULL,
594
  "sortsequence" integer NOT NULL DEFAULT '50',
595 10443 aaronmk
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
596 7545 aaronmk
  PRIMARY KEY ("imgid"),
597
  /*KEY "Index_tid" ("tid")*/CHECK (true),
598
  /*KEY "FK_images_occ" ("occid")*/CHECK (true),
599
  /*KEY "FK_photographeruid" ("photographeruid")*/CHECK (true),
600
  /*CONSTRAINT "FK_images_occ" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid")*/CHECK (true),
601
  /*CONSTRAINT "FK_photographeruid" FOREIGN KEY ("photographeruid") REFERENCES "users" ("uid") ON DELETE SET NULL ON UPDATE CASCADE*/CHECK (true),
602
  /*CONSTRAINT "FK_taxaimagestid" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID")*/CHECK (true)
603
);
604
/*!40101 SET character_set_client = @saved_cs_client */;
605
606
--
607
-- Table structure for table "institutions"
608
--
609
610
DROP TABLE IF EXISTS "institutions";
611
/*!40101 SET @saved_cs_client     = @@character_set_client */;
612
/*!40101 SET character_set_client = utf8 */;
613
CREATE TABLE "institutions" (
614
  "iid" integer NOT NULL,
615
  "InstitutionCode" varchar(45) NOT NULL,
616
  "InstitutionName" varchar(150) NOT NULL,
617
  "InstitutionName2" varchar(150) DEFAULT NULL,
618
  "Address1" varchar(150) DEFAULT NULL,
619
  "Address2" varchar(150) DEFAULT NULL,
620
  "City" varchar(45) DEFAULT NULL,
621
  "StateProvince" varchar(45) DEFAULT NULL,
622
  "PostalCode" varchar(45) DEFAULT NULL,
623
  "Country" varchar(45) DEFAULT NULL,
624
  "Phone" varchar(45) DEFAULT NULL,
625
  "Contact" varchar(65) DEFAULT NULL,
626
  "Email" varchar(45) DEFAULT NULL,
627
  "Url" varchar(250) DEFAULT NULL,
628
  "Notes" varchar(250) DEFAULT NULL,
629 10443 aaronmk
  "IntialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
630 7545 aaronmk
  PRIMARY KEY ("iid"),
631
  /*CONSTRAINT "Index_instcode" */UNIQUE ("InstitutionCode")
632
);
633
/*!40101 SET character_set_client = @saved_cs_client */;
634
635
--
636
-- Table structure for table "kmcharacterlang"
637
--
638
639
DROP TABLE IF EXISTS "kmcharacterlang";
640
/*!40101 SET @saved_cs_client     = @@character_set_client */;
641
/*!40101 SET character_set_client = utf8 */;
642
CREATE TABLE "kmcharacterlang" (
643
  "cid" integer NOT NULL,
644
  "charname" varchar(150) NOT NULL,
645
  "language" varchar(45) NOT NULL,
646
  "notes" varchar(255) DEFAULT NULL,
647
  "description" varchar(255) DEFAULT NULL,
648
  "helpurl" varchar(500) DEFAULT NULL,
649 10443 aaronmk
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
650 7545 aaronmk
  PRIMARY KEY ("cid","language"),
651
  /*CONSTRAINT "kmcharacterlang_ibfk_1" FOREIGN KEY ("cid") REFERENCES "kmcharacters" ("cid")*/CHECK (true)
652
);
653
/*!40101 SET character_set_client = @saved_cs_client */;
654
655
--
656
-- Table structure for table "kmcharacters"
657
--
658
659
DROP TABLE IF EXISTS "kmcharacters";
660
/*!40101 SET @saved_cs_client     = @@character_set_client */;
661
/*!40101 SET character_set_client = utf8 */;
662
CREATE TABLE "kmcharacters" (
663
  "cid" integer NOT NULL,
664
  "charname" varchar(150) NOT NULL,
665
  "chartype" varchar(2) NOT NULL DEFAULT 'UM',
666
  "defaultlang" varchar(45) NOT NULL DEFAULT 'English',
667
  "difficultyrank" integer NOT NULL DEFAULT '1',
668
  "hid" integer NOT NULL,
669
  "units" varchar(45) DEFAULT NULL,
670
  "description" varchar(255) DEFAULT NULL,
671
  "notes" varchar(255) DEFAULT NULL,
672
  "helpurl" varchar(500) DEFAULT NULL,
673
  "enteredby" varchar(45) DEFAULT NULL,
674
  "sortsequence" integer DEFAULT NULL,
675 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
676 7545 aaronmk
  PRIMARY KEY ("cid"),
677
  /*KEY "Index_charname" ("charname")*/CHECK (true),
678
  /*KEY "Index_sort" ("sortsequence")*/CHECK (true)
679
);
680
/*!40101 SET character_set_client = @saved_cs_client */;
681
682
--
683
-- Table structure for table "kmchardependance"
684
--
685
686
DROP TABLE IF EXISTS "kmchardependance";
687
/*!40101 SET @saved_cs_client     = @@character_set_client */;
688
/*!40101 SET character_set_client = utf8 */;
689
CREATE TABLE "kmchardependance" (
690
  "CID" integer NOT NULL,
691
  "CIDDependance" integer NOT NULL,
692
  "CSDependance" varchar(16) NOT NULL,
693 10443 aaronmk
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
694 7545 aaronmk
  PRIMARY KEY ("CSDependance","CIDDependance","CID"),
695
  /*KEY "FK_chardependance_cid" ("CID")*/CHECK (true),
696
  /*KEY "FK_chardependance_2" ("CIDDependance")*/CHECK (true),
697
  /*CONSTRAINT "kmchardependance_ibfk_1" FOREIGN KEY ("CID") REFERENCES "kmcharacters" ("cid")*/CHECK (true),
698
  /*CONSTRAINT "kmchardependance_ibfk_2" FOREIGN KEY ("CID") REFERENCES "kmcharacters" ("cid")*/CHECK (true)
699
);
700
/*!40101 SET character_set_client = @saved_cs_client */;
701
702
--
703
-- Table structure for table "kmcharheading"
704
--
705
706
DROP TABLE IF EXISTS "kmcharheading";
707
/*!40101 SET @saved_cs_client     = @@character_set_client */;
708
/*!40101 SET character_set_client = utf8 */;
709
CREATE TABLE "kmcharheading" (
710
  "hid" integer NOT NULL,
711
  "headingname" varchar(255) NOT NULL,
712
  "language" varchar(45) NOT NULL DEFAULT 'English',
713
  "notes" text,
714
  "sortsequence" integer DEFAULT NULL,
715 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
716 7545 aaronmk
  PRIMARY KEY ("hid","language"),
717
  /*KEY "HeadingName" ("headingname")*/CHECK (true)
718
);
719
/*!40101 SET character_set_client = @saved_cs_client */;
720
721
--
722
-- Table structure for table "kmcharheadinglink"
723
--
724
725
DROP TABLE IF EXISTS "kmcharheadinglink";
726
/*!40101 SET @saved_cs_client     = @@character_set_client */;
727
/*!40101 SET character_set_client = utf8 */;
728
CREATE TABLE "kmcharheadinglink" (
729
  "HID" integer NOT NULL DEFAULT '0',
730
  "CID" integer NOT NULL DEFAULT '0',
731
  "Notes" varchar(255) DEFAULT NULL,
732 10443 aaronmk
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
733 7545 aaronmk
  PRIMARY KEY ("HID","CID"),
734
  /*KEY "CharactersCharHeadingLinker" ("CID")*/CHECK (true),
735
  /*KEY "CharHeadingCharHeadingLinker" ("HID")*/CHECK (true),
736
  /*KEY "CID" ("CID")*/CHECK (true),
737
  /*CONSTRAINT "kmcharheadinglink_ibfk_1" FOREIGN KEY ("CID") REFERENCES "kmcharacters" ("cid")*/CHECK (true),
738
  /*CONSTRAINT "kmcharheadinglink_ibfk_2" FOREIGN KEY ("HID") REFERENCES "kmcharheading" ("hid") ON UPDATE CASCADE*/CHECK (true)
739
);
740
/*!40101 SET character_set_client = @saved_cs_client */;
741
742
--
743
-- Table structure for table "kmchartaxalink"
744
--
745
746
DROP TABLE IF EXISTS "kmchartaxalink";
747
/*!40101 SET @saved_cs_client     = @@character_set_client */;
748
/*!40101 SET character_set_client = utf8 */;
749
CREATE TABLE "kmchartaxalink" (
750
  "CID" integer NOT NULL DEFAULT '0',
751
  "TID" integer NOT NULL DEFAULT '0',
752
  "Status" varchar(50) DEFAULT NULL,
753
  "Notes" varchar(255) DEFAULT NULL,
754
  "Relation" varchar(45) NOT NULL DEFAULT 'include',
755
  "EditabilityInherited" bit(1) DEFAULT NULL,
756 10443 aaronmk
  "timestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
757 7545 aaronmk
  PRIMARY KEY ("CID","TID"),
758
  /*KEY "FK_CharTaxaLink-TID" ("TID")*/CHECK (true),
759
  /*CONSTRAINT "kmchartaxalink_ibfk_1" FOREIGN KEY ("CID") REFERENCES "kmcharacters" ("cid")*/CHECK (true),
760
  /*CONSTRAINT "kmchartaxalink_ibfk_2" FOREIGN KEY ("TID") REFERENCES "taxa" ("TID")*/CHECK (true)
761
);
762
/*!40101 SET character_set_client = @saved_cs_client */;
763
764
--
765
-- Table structure for table "kmcs"
766
--
767
768
DROP TABLE IF EXISTS "kmcs";
769
/*!40101 SET @saved_cs_client     = @@character_set_client */;
770
/*!40101 SET character_set_client = utf8 */;
771
CREATE TABLE "kmcs" (
772
  "cid" integer NOT NULL DEFAULT '0',
773
  "cs" varchar(16) NOT NULL,
774
  "CharStateName" varchar(255) DEFAULT NULL,
775
  "Implicit" integer NOT NULL DEFAULT '0',
776
  "Notes" text,
777
  "Description" varchar(255) DEFAULT NULL,
778
  "IllustrationUrl" varchar(250) DEFAULT NULL,
779
  "StateID" integer DEFAULT NULL,
780
  "Language" varchar(45) NOT NULL DEFAULT 'English',
781
  "SortSequence" integer DEFAULT NULL,
782 10443 aaronmk
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
783 7545 aaronmk
  "EnteredBy" varchar(45) DEFAULT NULL,
784
  PRIMARY KEY ("cs","cid"),
785
  /*KEY "FK_cs_chars" ("cid")*/CHECK (true),
786
  /*CONSTRAINT "kmcs_ibfk_1" FOREIGN KEY ("cid") REFERENCES "kmcharacters" ("cid")*/CHECK (true)
787
);
788
/*!40101 SET character_set_client = @saved_cs_client */;
789
790
--
791
-- Table structure for table "kmcsimages"
792
--
793
794
DROP TABLE IF EXISTS "kmcsimages";
795
/*!40101 SET @saved_cs_client     = @@character_set_client */;
796
/*!40101 SET character_set_client = utf8 */;
797
CREATE TABLE "kmcsimages" (
798
  "csimgid" integer NOT NULL,
799
  "cid" integer NOT NULL,
800
  "cs" varchar(16) NOT NULL,
801
  "url" varchar(45) NOT NULL,
802
  "notes" varchar(250) DEFAULT NULL,
803
  "sortsequence" varchar(45) NOT NULL DEFAULT '50',
804
  "username" varchar(45) DEFAULT NULL,
805 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
806 7545 aaronmk
  PRIMARY KEY ("csimgid")
807
);
808
/*!40101 SET character_set_client = @saved_cs_client */;
809
810
--
811
-- Table structure for table "kmcslang"
812
--
813
814
DROP TABLE IF EXISTS "kmcslang";
815
/*!40101 SET @saved_cs_client     = @@character_set_client */;
816
/*!40101 SET character_set_client = utf8 */;
817
CREATE TABLE "kmcslang" (
818
  "cid" integer NOT NULL,
819
  "cs" varchar(16) NOT NULL,
820
  "charstatename" varchar(150) NOT NULL,
821
  "language" varchar(45) NOT NULL,
822
  "description" varchar(255) DEFAULT NULL,
823
  "notes" varchar(255) DEFAULT NULL,
824 10443 aaronmk
  "intialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
825 7545 aaronmk
  PRIMARY KEY ("cid","cs","language"),
826
  /*CONSTRAINT "kmcslang_ibfk_1" FOREIGN KEY ("cid", "cs") REFERENCES "kmcs" ("cid", "cs")*/CHECK (true)
827
);
828
/*!40101 SET character_set_client = @saved_cs_client */;
829
830
--
831
-- Table structure for table "kmdescr"
832
--
833
834
DROP TABLE IF EXISTS "kmdescr";
835
/*!40101 SET @saved_cs_client     = @@character_set_client */;
836
/*!40101 SET character_set_client = utf8 */;
837
CREATE TABLE "kmdescr" (
838
  "TID" integer NOT NULL DEFAULT '0',
839
  "CID" integer NOT NULL DEFAULT '0',
840
  "Modifier" varchar(255) DEFAULT NULL,
841
  "CS" varchar(16) NOT NULL,
842
  "X" double precision DEFAULT NULL,
843
  "TXT" text,
844
  "PseudoTrait" integer DEFAULT '0',
845 10444 aaronmk
  "Frequency" integer NOT NULL DEFAULT '5' /*COMMENT 'Frequency of occurrence; 1 = rare... 5 = common'*/,
846 7545 aaronmk
  "Inherited" varchar(50) DEFAULT NULL,
847
  "Source" varchar(100) DEFAULT NULL,
848
  "Seq" integer DEFAULT NULL,
849
  "Notes" text,
850 10443 aaronmk
  "DateEntered" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
851 7545 aaronmk
  PRIMARY KEY ("TID","CID","CS"),
852
  /*KEY "CSDescr" ("CID","CS")*/CHECK (true),
853
  /*CONSTRAINT "kmdescr_ibfk_1" FOREIGN KEY ("CID", "CS") REFERENCES "kmcs" ("cid", "cs")*/CHECK (true),
854
  /*CONSTRAINT "kmdescr_ibfk_2" FOREIGN KEY ("TID") REFERENCES "taxa" ("TID")*/CHECK (true)
855
);
856
/*!40101 SET character_set_client = @saved_cs_client */;
857
858
--
859
-- Table structure for table "kmdescrdeletions"
860
--
861
862
DROP TABLE IF EXISTS "kmdescrdeletions";
863
/*!40101 SET @saved_cs_client     = @@character_set_client */;
864
/*!40101 SET character_set_client = utf8 */;
865
CREATE TABLE "kmdescrdeletions" (
866
  "TID" integer NOT NULL,
867
  "CID" integer NOT NULL,
868
  "CS" varchar(16) NOT NULL,
869
  "Modifier" varchar(255) DEFAULT NULL,
870
  "X" double precision DEFAULT NULL,
871
  "TXT" text,
872
  "Inherited" varchar(50) DEFAULT NULL,
873
  "Source" varchar(100) DEFAULT NULL,
874
  "Seq" integer DEFAULT NULL,
875
  "Notes" text,
876 10443 aaronmk
  "InitialTimeStamp" text/*datetime*/ DEFAULT NULL,
877 7545 aaronmk
  "DeletedBy" varchar(100) NOT NULL,
878 10443 aaronmk
  "DeletedTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
879 7545 aaronmk
  "PK" integer NOT NULL,
880
  PRIMARY KEY ("PK")
881
);
882
/*!40101 SET character_set_client = @saved_cs_client */;
883
884
--
885
-- Table structure for table "lkupcountry"
886
--
887
888
DROP TABLE IF EXISTS "lkupcountry";
889
/*!40101 SET @saved_cs_client     = @@character_set_client */;
890
/*!40101 SET character_set_client = utf8 */;
891
CREATE TABLE "lkupcountry" (
892
  "countryId" integer NOT NULL,
893
  "countryName" varchar(100) NOT NULL,
894
  "iso" varchar(2) DEFAULT NULL,
895
  "iso3" varchar(3) DEFAULT NULL,
896
  "numcode" integer DEFAULT NULL,
897 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
898 7545 aaronmk
  PRIMARY KEY ("countryId"),
899
  /*CONSTRAINT "country_unique" */UNIQUE ("countryName")
900
);
901
/*!40101 SET character_set_client = @saved_cs_client */;
902
903
--
904
-- Table structure for table "lkupcounty"
905
--
906
907
DROP TABLE IF EXISTS "lkupcounty";
908
/*!40101 SET @saved_cs_client     = @@character_set_client */;
909
/*!40101 SET character_set_client = utf8 */;
910
CREATE TABLE "lkupcounty" (
911
  "countyId" integer NOT NULL,
912
  "stateId" integer NOT NULL,
913
  "countyName" varchar(100) NOT NULL,
914 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
915 7545 aaronmk
  PRIMARY KEY ("countyId"),
916
  /*CONSTRAINT "unique_county" */UNIQUE ("stateId","countyName"),
917
  /*KEY "fk_stateprovince" ("stateId")*/CHECK (true),
918
  /*KEY "index_countyname" ("countyName")*/CHECK (true),
919
  /*CONSTRAINT "fk_stateprovince" FOREIGN KEY ("stateId") REFERENCES "lkupstateprovince" ("stateId") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
920
);
921
/*!40101 SET character_set_client = @saved_cs_client */;
922
923
--
924
-- Table structure for table "lkupstateprovince"
925
--
926
927
DROP TABLE IF EXISTS "lkupstateprovince";
928
/*!40101 SET @saved_cs_client     = @@character_set_client */;
929
/*!40101 SET character_set_client = utf8 */;
930
CREATE TABLE "lkupstateprovince" (
931
  "stateId" integer NOT NULL,
932
  "countryId" integer NOT NULL,
933
  "stateName" varchar(100) NOT NULL,
934
  "abbrev" varchar(2) DEFAULT NULL,
935 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
936 7545 aaronmk
  PRIMARY KEY ("stateId"),
937
  /*CONSTRAINT "state_index" */UNIQUE ("stateName","countryId"),
938
  /*KEY "fk_country" ("countryId")*/CHECK (true),
939
  /*KEY "index_statename" ("stateName")*/CHECK (true),
940
  /*CONSTRAINT "fk_country" FOREIGN KEY ("countryId") REFERENCES "lkupcountry" ("countryId") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
941
);
942
/*!40101 SET character_set_client = @saved_cs_client */;
943
944
--
945
-- Table structure for table "omassociatedoccurrences"
946
--
947
948
DROP TABLE IF EXISTS "omassociatedoccurrences";
949
/*!40101 SET @saved_cs_client     = @@character_set_client */;
950
/*!40101 SET character_set_client = utf8 */;
951
CREATE TABLE "omassociatedoccurrences" (
952
  "aoid" integer NOT NULL,
953
  "occid" integer NOT NULL,
954
  "occidassociate" integer DEFAULT NULL,
955
  "relationship" varchar(150) NOT NULL,
956 10444 aaronmk
  "identifier" varchar(250) DEFAULT NULL /*COMMENT 'e.g. GUID'*/,
957 7545 aaronmk
  "resourceurl" varchar(250) DEFAULT NULL,
958
  "notes" varchar(250) DEFAULT NULL,
959 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
960 7545 aaronmk
  PRIMARY KEY ("aoid")
961
);
962
/*!40101 SET character_set_client = @saved_cs_client */;
963
964
--
965
-- Table structure for table "omcollcatagories"
966
--
967
968
DROP TABLE IF EXISTS "omcollcatagories";
969
/*!40101 SET @saved_cs_client     = @@character_set_client */;
970
/*!40101 SET character_set_client = utf8 */;
971
CREATE TABLE "omcollcatagories" (
972
  "ccpk" integer NOT NULL,
973
  "catagory" varchar(45) NOT NULL,
974 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
975 7545 aaronmk
  PRIMARY KEY ("ccpk")
976
);
977
/*!40101 SET character_set_client = @saved_cs_client */;
978
979
--
980
-- Table structure for table "omcollcatlink"
981
--
982
983
DROP TABLE IF EXISTS "omcollcatlink";
984
/*!40101 SET @saved_cs_client     = @@character_set_client */;
985
/*!40101 SET character_set_client = utf8 */;
986
CREATE TABLE "omcollcatlink" (
987
  "ccpk" integer NOT NULL,
988
  "collid" integer NOT NULL,
989
  "sortsequence" integer DEFAULT NULL,
990 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
991 7545 aaronmk
  PRIMARY KEY ("ccpk","collid"),
992
  /*KEY "FK_collcatlink_coll" ("collid")*/CHECK (true),
993
  /*CONSTRAINT "FK_collcatlink_cat" FOREIGN KEY ("ccpk") REFERENCES "omcollcatagories" ("ccpk") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
994
  /*CONSTRAINT "FK_collcatlink_coll" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
995
);
996
/*!40101 SET character_set_client = @saved_cs_client */;
997
998
--
999
-- Table structure for table "omcollections"
1000
--
1001
1002
DROP TABLE IF EXISTS "omcollections";
1003
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1004
/*!40101 SET character_set_client = utf8 */;
1005
CREATE TABLE "omcollections" (
1006
  "CollID" integer NOT NULL,
1007
  "InstitutionCode" varchar(45) NOT NULL,
1008
  "CollectionCode" varchar(45) DEFAULT NULL,
1009
  "CollectionName" varchar(150) NOT NULL,
1010
  "iid" integer DEFAULT NULL,
1011
  "briefdescription" varchar(300) DEFAULT NULL,
1012
  "fulldescription" varchar(2000) DEFAULT NULL,
1013
  "Homepage" varchar(250) DEFAULT NULL,
1014
  "IndividualUrl" varchar(500) DEFAULT NULL,
1015
  "Contact" varchar(45) DEFAULT NULL,
1016
  "email" varchar(45) DEFAULT NULL,
1017
  "latitudedecimal" double precision DEFAULT NULL,
1018
  "longitudedecimal" double precision DEFAULT NULL,
1019
  "icon" varchar(250) DEFAULT NULL,
1020 10444 aaronmk
  "CollType" varchar(45) NOT NULL DEFAULT 'Preserved Specimens' /*COMMENT 'Preserved Specimens, General Observations, Observations'*/,
1021
  "ManagementType" varchar(45) DEFAULT 'Snapshot' /*COMMENT 'Snapshot, Live Data'*/,
1022 7545 aaronmk
  "PublicEdits" integer NOT NULL DEFAULT '1',
1023
  "guidtarget" varchar(45) DEFAULT NULL,
1024
  "rightsHolder" varchar(250) DEFAULT NULL,
1025
  "rights" varchar(250) DEFAULT NULL,
1026
  "bibliographicCitation" varchar(1000) DEFAULT NULL,
1027
  "accessrights" varchar(250) DEFAULT NULL,
1028
  "SortSeq" integer DEFAULT NULL,
1029 10443 aaronmk
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1030 7545 aaronmk
  PRIMARY KEY ("CollID"),
1031
  /*KEY "Index_inst" ("InstitutionCode")*/CHECK (true)
1032
);
1033
/*!40101 SET character_set_client = @saved_cs_client */;
1034
1035
--
1036
-- Table structure for table "omcollectionstats"
1037
--
1038
1039
DROP TABLE IF EXISTS "omcollectionstats";
1040
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1041
/*!40101 SET character_set_client = utf8 */;
1042
CREATE TABLE "omcollectionstats" (
1043
  "collid" integer NOT NULL,
1044
  "recordcnt" integer NOT NULL DEFAULT '0',
1045
  "georefcnt" integer DEFAULT NULL,
1046
  "familycnt" integer DEFAULT NULL,
1047
  "genuscnt" integer DEFAULT NULL,
1048
  "speciescnt" integer DEFAULT NULL,
1049 10443 aaronmk
  "uploaddate" text/*datetime*/ DEFAULT NULL,
1050 7545 aaronmk
  "uploadedby" varchar(45) DEFAULT NULL,
1051
  "dbtype" varchar(45) DEFAULT NULL,
1052
  "dburl" varchar(250) DEFAULT NULL,
1053
  "dbport" varchar(45) DEFAULT NULL,
1054
  "dblogin" varchar(45) DEFAULT NULL,
1055
  "dbpassword" varchar(45) DEFAULT NULL,
1056 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1057 7545 aaronmk
  PRIMARY KEY ("collid"),
1058
  /*CONSTRAINT "omcollectionstats_ibfk_1" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID")*/CHECK (true)
1059
);
1060
/*!40101 SET character_set_client = @saved_cs_client */;
1061
1062
--
1063
-- Table structure for table "omcollectors"
1064
--
1065
1066
DROP TABLE IF EXISTS "omcollectors";
1067
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1068
/*!40101 SET character_set_client = utf8 */;
1069
CREATE TABLE "omcollectors" (
1070
  "recordedById" integer NOT NULL,
1071
  "familyname" varchar(45) NOT NULL,
1072
  "firstname" varchar(45) DEFAULT NULL,
1073
  "middleinitial" varchar(45) DEFAULT NULL,
1074
  "startyearactive" integer DEFAULT NULL,
1075
  "endyearactive" integer DEFAULT NULL,
1076
  "notes" varchar(255) DEFAULT NULL,
1077
  "rating" integer DEFAULT '10',
1078 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP,
1079 7545 aaronmk
  PRIMARY KEY ("recordedById"),
1080
  /*KEY "fullname" ("familyname","firstname")*/CHECK (true)
1081
);
1082
/*!40101 SET character_set_client = @saved_cs_client */;
1083
1084
--
1085
-- Table structure for table "omcollsecondary"
1086
--
1087
1088
DROP TABLE IF EXISTS "omcollsecondary";
1089
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1090
/*!40101 SET character_set_client = utf8 */;
1091
CREATE TABLE "omcollsecondary" (
1092
  "ocsid" integer NOT NULL,
1093
  "collid" integer NOT NULL,
1094
  "InstitutionCode" varchar(45) NOT NULL,
1095
  "CollectionCode" varchar(45) DEFAULT NULL,
1096
  "CollectionName" varchar(150) NOT NULL,
1097
  "BriefDescription" varchar(300) DEFAULT NULL,
1098
  "FullDescription" varchar(1000) DEFAULT NULL,
1099
  "Homepage" varchar(250) DEFAULT NULL,
1100
  "IndividualUrl" varchar(500) DEFAULT NULL,
1101
  "Contact" varchar(45) DEFAULT NULL,
1102
  "Email" varchar(45) DEFAULT NULL,
1103
  "LatitudeDecimal" double precision DEFAULT NULL,
1104
  "LongitudeDecimal" double precision DEFAULT NULL,
1105
  "icon" varchar(250) DEFAULT NULL,
1106
  "CollType" varchar(45) DEFAULT NULL,
1107
  "SortSeq" integer DEFAULT NULL,
1108 10443 aaronmk
  "InitialTimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1109 7545 aaronmk
  PRIMARY KEY ("ocsid"),
1110
  /*KEY "FK_omcollsecondary_coll" ("collid")*/CHECK (true),
1111
  /*CONSTRAINT "FK_omcollsecondary_coll" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
1112
);
1113
/*!40101 SET character_set_client = @saved_cs_client */;
1114
1115
--
1116
-- Table structure for table "omcrowdsourcecentral"
1117
--
1118
1119
DROP TABLE IF EXISTS "omcrowdsourcecentral";
1120
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1121
/*!40101 SET character_set_client = utf8 */;
1122
CREATE TABLE "omcrowdsourcecentral" (
1123
  "omcsid" integer NOT NULL,
1124
  "collid" integer NOT NULL,
1125
  "instructions" text,
1126
  "trainingurl" varchar(500) DEFAULT NULL,
1127 10444 aaronmk
  "editorlevel" integer NOT NULL DEFAULT '0' /*COMMENT '0=public, 1=public limited, 2=private'*/,
1128 7545 aaronmk
  "notes" varchar(250) DEFAULT NULL,
1129 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1130 7545 aaronmk
  PRIMARY KEY ("omcsid"),
1131
  /*CONSTRAINT "Index_omcrowdsourcecentral_collid" */UNIQUE ("collid"),
1132
  /*KEY "FK_omcrowdsourcecentral_collid" ("collid")*/CHECK (true),
1133
  /*CONSTRAINT "FK_omcrowdsourcecentral_collid" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID")*/CHECK (true)
1134
);
1135
/*!40101 SET character_set_client = @saved_cs_client */;
1136
1137
--
1138
-- Table structure for table "omcrowdsourcequeue"
1139
--
1140
1141
DROP TABLE IF EXISTS "omcrowdsourcequeue";
1142
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1143
/*!40101 SET character_set_client = utf8 */;
1144
CREATE TABLE "omcrowdsourcequeue" (
1145
  "idomcrowdsourcequeue" integer NOT NULL,
1146
  "omcsid" integer NOT NULL,
1147
  "occid" integer NOT NULL,
1148 10444 aaronmk
  "reviewstatus" integer NOT NULL DEFAULT '0' /*COMMENT '0=open,5=pending review, 10=closed'*/,
1149 7545 aaronmk
  "uidprocessor" integer DEFAULT NULL,
1150 10444 aaronmk
  "points" integer DEFAULT NULL /*COMMENT '0=fail, 1=minor edits, 2=no edits <default>, 3=excelled'*/,
1151 7545 aaronmk
  "notes" varchar(250) DEFAULT NULL,
1152 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1153 7545 aaronmk
  PRIMARY KEY ("idomcrowdsourcequeue"),
1154
  /*CONSTRAINT "Index_omcrowdsource_occid" */UNIQUE ("occid"),
1155
  /*KEY "FK_omcrowdsourcequeue_occid" ("occid")*/CHECK (true),
1156
  /*KEY "FK_omcrowdsourcequeue_uid" ("uidprocessor")*/CHECK (true),
1157
  /*CONSTRAINT "FK_omcrowdsourcequeue_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
1158
  /*CONSTRAINT "FK_omcrowdsourcequeue_uid" FOREIGN KEY ("uidprocessor") REFERENCES "users" ("uid") ON DELETE NO ACTION ON UPDATE CASCADE*/CHECK (true)
1159
);
1160
/*!40101 SET character_set_client = @saved_cs_client */;
1161
1162
--
1163
-- Table structure for table "omexsiccatinumbers"
1164
--
1165
1166
DROP TABLE IF EXISTS "omexsiccatinumbers";
1167
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1168
/*!40101 SET character_set_client = utf8 */;
1169
CREATE TABLE "omexsiccatinumbers" (
1170
  "omenid" integer NOT NULL,
1171
  "exsnumber" varchar(45) NOT NULL,
1172
  "ometid" integer NOT NULL,
1173
  "notes" varchar(250) DEFAULT NULL,
1174 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1175 7545 aaronmk
  PRIMARY KEY ("omenid"),
1176
  /*CONSTRAINT "Index_omexsiccatinumbers_unique" */UNIQUE ("exsnumber","ometid"),
1177
  /*KEY "FK_exsiccatiTitleNumber" ("ometid")*/CHECK (true),
1178
  /*CONSTRAINT "FK_exsiccatiTitleNumber" FOREIGN KEY ("ometid") REFERENCES "omexsiccatititles" ("ometid")*/CHECK (true)
1179
);
1180
/*!40101 SET character_set_client = @saved_cs_client */;
1181
1182
--
1183
-- Table structure for table "omexsiccatiocclink"
1184
--
1185
1186
DROP TABLE IF EXISTS "omexsiccatiocclink";
1187
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1188
/*!40101 SET character_set_client = utf8 */;
1189
CREATE TABLE "omexsiccatiocclink" (
1190
  "omenid" integer NOT NULL,
1191
  "occid" integer NOT NULL,
1192
  "ranking" integer NOT NULL DEFAULT '50',
1193
  "notes" varchar(250) DEFAULT NULL,
1194 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1195 7545 aaronmk
  PRIMARY KEY ("omenid","occid"),
1196
  /*CONSTRAINT "UniqueOmexsiccatiOccLink" */UNIQUE ("occid"),
1197
  /*KEY "FKExsiccatiNumOccLink1" ("omenid")*/CHECK (true),
1198
  /*KEY "FKExsiccatiNumOccLink2" ("occid")*/CHECK (true),
1199
  /*CONSTRAINT "FKExsiccatiNumOccLink1" FOREIGN KEY ("omenid") REFERENCES "omexsiccatinumbers" ("omenid") ON DELETE CASCADE*/CHECK (true),
1200
  /*CONSTRAINT "FKExsiccatiNumOccLink2" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE*/CHECK (true)
1201
);
1202
/*!40101 SET character_set_client = @saved_cs_client */;
1203
1204
--
1205
-- Table structure for table "omexsiccatititles"
1206
--
1207
1208
DROP TABLE IF EXISTS "omexsiccatititles";
1209
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1210
/*!40101 SET character_set_client = utf8 */;
1211
CREATE TABLE "omexsiccatititles" (
1212
  "ometid" integer NOT NULL,
1213
  "title" varchar(150) NOT NULL,
1214
  "abbreviation" varchar(100) DEFAULT NULL,
1215
  "editor" varchar(150) DEFAULT NULL,
1216
  "exsrange" varchar(45) DEFAULT NULL,
1217
  "startdate" varchar(45) DEFAULT NULL,
1218
  "enddate" varchar(45) DEFAULT NULL,
1219
  "source" varchar(45) DEFAULT NULL,
1220
  "notes" varchar(2000) DEFAULT NULL,
1221
  "lasteditedby" varchar(45) DEFAULT NULL,
1222 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1223 7545 aaronmk
  PRIMARY KEY ("ometid"),
1224
  /*KEY "index_exsiccatiTitle" ("title")*/CHECK (true)
1225
);
1226
/*!40101 SET character_set_client = @saved_cs_client */;
1227
1228
--
1229
-- Table structure for table "omoccurcomments"
1230
--
1231
1232
DROP TABLE IF EXISTS "omoccurcomments";
1233
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1234
/*!40101 SET character_set_client = utf8 */;
1235
CREATE TABLE "omoccurcomments" (
1236
  "comid" integer NOT NULL,
1237
  "occid" integer NOT NULL,
1238
  "comment" text NOT NULL,
1239
  "uid" integer NOT NULL,
1240
  "reviewstatus" integer NOT NULL DEFAULT '0',
1241
  "parentcomid" integer DEFAULT NULL,
1242 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1243 7545 aaronmk
  PRIMARY KEY ("comid"),
1244
  /*KEY "fk_omoccurcomments_occid" ("occid")*/CHECK (true),
1245
  /*KEY "fk_omoccurcomments_uid" ("uid")*/CHECK (true),
1246
  /*CONSTRAINT "fk_omoccurcomments_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
1247
  /*CONSTRAINT "fk_omoccurcomments_uid" FOREIGN KEY ("uid") REFERENCES "users" ("uid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
1248
);
1249
/*!40101 SET character_set_client = @saved_cs_client */;
1250
1251
--
1252
-- Table structure for table "omoccurdatasetlink"
1253
--
1254
1255
DROP TABLE IF EXISTS "omoccurdatasetlink";
1256
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1257
/*!40101 SET character_set_client = utf8 */;
1258
CREATE TABLE "omoccurdatasetlink" (
1259
  "occid" integer NOT NULL,
1260
  "datasetid" integer NOT NULL,
1261
  "notes" varchar(250) DEFAULT NULL,
1262 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1263 7545 aaronmk
  PRIMARY KEY ("occid","datasetid"),
1264
  /*KEY "FK_omoccurdatasetlink_datasetid" ("datasetid")*/CHECK (true),
1265
  /*KEY "FK_omoccurdatasetlink_occid" ("occid")*/CHECK (true),
1266
  /*CONSTRAINT "FK_omoccurdatasetlink_datasetid" FOREIGN KEY ("datasetid") REFERENCES "omoccurdatasets" ("datasetid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
1267
  /*CONSTRAINT "FK_omoccurdatasetlink_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
1268
);
1269
/*!40101 SET character_set_client = @saved_cs_client */;
1270
1271
--
1272
-- Table structure for table "omoccurdatasets"
1273
--
1274
1275
DROP TABLE IF EXISTS "omoccurdatasets";
1276
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1277
/*!40101 SET character_set_client = utf8 */;
1278
CREATE TABLE "omoccurdatasets" (
1279
  "datasetid" integer NOT NULL,
1280
  "name" varchar(100) NOT NULL,
1281
  "notes" varchar(250) DEFAULT NULL,
1282
  "sortsequence" integer DEFAULT NULL,
1283
  "uid" integer NOT NULL,
1284 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1285 7545 aaronmk
  PRIMARY KEY ("datasetid")
1286
);
1287
/*!40101 SET character_set_client = @saved_cs_client */;
1288
1289
--
1290
-- Table structure for table "omoccurdeterminations"
1291
--
1292
1293
DROP TABLE IF EXISTS "omoccurdeterminations";
1294
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1295
/*!40101 SET character_set_client = utf8 */;
1296
CREATE TABLE "omoccurdeterminations" (
1297
  "detid" integer NOT NULL,
1298
  "occid" integer NOT NULL,
1299
  "identifiedBy" varchar(60) NOT NULL,
1300
  "idbyid" integer DEFAULT NULL,
1301
  "dateIdentified" varchar(45) NOT NULL,
1302
  "sciname" varchar(100) NOT NULL,
1303
  "tidinterpreted" integer DEFAULT NULL,
1304
  "scientificNameAuthorship" varchar(100) DEFAULT NULL,
1305
  "identificationQualifier" varchar(45) DEFAULT NULL,
1306
  "iscurrent" integer DEFAULT '0',
1307
  "identificationReferences" varchar(255) DEFAULT NULL,
1308
  "identificationRemarks" varchar(255) DEFAULT NULL,
1309
  "sortsequence" integer DEFAULT '10',
1310 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1311 7545 aaronmk
  PRIMARY KEY ("detid"),
1312
  /*CONSTRAINT "Index_unique" */UNIQUE ("occid","dateIdentified","identifiedBy"),
1313
  /*KEY "FK_omoccurdets_tid" ("tidinterpreted")*/CHECK (true),
1314
  /*KEY "FK_omoccurdets_idby_idx" ("idbyid")*/CHECK (true),
1315
  /*CONSTRAINT "FK_omoccurdets_idby" FOREIGN KEY ("idbyid") REFERENCES "omcollectors" ("recordedById") ON DELETE SET NULL ON UPDATE SET NULL*/CHECK (true),
1316
  /*CONSTRAINT "FK_omoccurdets_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
1317
  /*CONSTRAINT "FK_omoccurdets_tid" FOREIGN KEY ("tidinterpreted") REFERENCES "taxa" ("TID")*/CHECK (true)
1318
);
1319
/*!40101 SET character_set_client = @saved_cs_client */;
1320
1321
--
1322
-- Table structure for table "omoccurduplicates"
1323
--
1324
1325
DROP TABLE IF EXISTS "omoccurduplicates";
1326
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1327
/*!40101 SET character_set_client = utf8 */;
1328
CREATE TABLE "omoccurduplicates" (
1329
  "duplicateid" integer NOT NULL,
1330
  "projIdentifier" varchar(30) NOT NULL,
1331
  "projName" varchar(255) NOT NULL,
1332
  "isExsiccata" integer NOT NULL DEFAULT '0',
1333
  "exsiccataEditors" varchar(150) DEFAULT NULL,
1334
  "notes" varchar(255) DEFAULT NULL,
1335 10443 aaronmk
  "initialTimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1336 7545 aaronmk
  PRIMARY KEY ("duplicateid")
1337
);
1338
/*!40101 SET character_set_client = @saved_cs_client */;
1339
1340
--
1341
-- Table structure for table "omoccureditlocks"
1342
--
1343
1344
DROP TABLE IF EXISTS "omoccureditlocks";
1345
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1346
/*!40101 SET character_set_client = utf8 */;
1347
CREATE TABLE "omoccureditlocks" (
1348
  "occid" integer NOT NULL,
1349
  "uid" integer NOT NULL,
1350
  "ts" integer NOT NULL,
1351 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1352 7545 aaronmk
  PRIMARY KEY ("occid")
1353
);
1354
/*!40101 SET character_set_client = @saved_cs_client */;
1355
1356
--
1357
-- Table structure for table "omoccuredits"
1358
--
1359
1360
DROP TABLE IF EXISTS "omoccuredits";
1361
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1362
/*!40101 SET character_set_client = utf8 */;
1363
CREATE TABLE "omoccuredits" (
1364
  "ocedid" integer NOT NULL,
1365
  "occid" integer NOT NULL,
1366
  "FieldName" varchar(45) NOT NULL,
1367
  "FieldValueNew" text NOT NULL,
1368
  "FieldValueOld" text NOT NULL,
1369 10444 aaronmk
  "ReviewStatus" integer NOT NULL DEFAULT '1' /*COMMENT '1=Open;2=Pending;3=Closed'*/,
1370
  "AppliedStatus" integer NOT NULL DEFAULT '0' /*COMMENT '0=Not Applied;1=Applied'*/,
1371 7545 aaronmk
  "uid" integer NOT NULL,
1372 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1373 7545 aaronmk
  PRIMARY KEY ("ocedid"),
1374
  /*KEY "fk_omoccuredits_uid" ("uid")*/CHECK (true),
1375
  /*KEY "fk_omoccuredits_occid" ("occid")*/CHECK (true),
1376
  /*CONSTRAINT "fk_omoccuredits_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
1377
  /*CONSTRAINT "fk_omoccuredits_uid" FOREIGN KEY ("uid") REFERENCES "users" ("uid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
1378
);
1379
/*!40101 SET character_set_client = @saved_cs_client */;
1380
1381
--
1382
-- Table structure for table "omoccurexchange"
1383
--
1384
1385
DROP TABLE IF EXISTS "omoccurexchange";
1386
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1387
/*!40101 SET character_set_client = utf8 */;
1388
CREATE TABLE "omoccurexchange" (
1389
  "exchangeid" integer NOT NULL,
1390
  "identifier" varchar(30) DEFAULT NULL,
1391
  "collid" integer DEFAULT NULL,
1392
  "iid" integer DEFAULT NULL,
1393
  "transactionType" varchar(10) DEFAULT NULL,
1394
  "in_out" varchar(3) DEFAULT NULL,
1395 10443 aaronmk
  "dateSent" text/*date*/ DEFAULT NULL,
1396
  "dateReceived" text/*date*/ DEFAULT NULL,
1397 7545 aaronmk
  "totalBoxes" integer DEFAULT NULL,
1398
  "shippingMethod" varchar(50) DEFAULT NULL,
1399
  "totalExMounted" integer DEFAULT NULL,
1400
  "totalExUnmounted" integer DEFAULT NULL,
1401
  "totalGift" integer DEFAULT NULL,
1402
  "totalGiftDet" integer DEFAULT NULL,
1403
  "adjustment" integer DEFAULT NULL,
1404
  "invoiceBalance" integer DEFAULT NULL,
1405
  "invoiceMessage" varchar(500) DEFAULT NULL,
1406
  "description" varchar(1000) DEFAULT NULL,
1407
  "notes" varchar(500) DEFAULT NULL,
1408
  "createdBy" varchar(20) DEFAULT NULL,
1409 10443 aaronmk
  "initialTimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1410 7545 aaronmk
  PRIMARY KEY ("exchangeid"),
1411
  /*KEY "FK_occexch_coll" ("collid")*/CHECK (true),
1412
  /*CONSTRAINT "FK_occexch_coll" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
1413
);
1414
/*!40101 SET character_set_client = @saved_cs_client */;
1415
1416
--
1417
-- Table structure for table "omoccurgenetic"
1418
--
1419
1420
DROP TABLE IF EXISTS "omoccurgenetic";
1421
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1422
/*!40101 SET character_set_client = utf8 */;
1423
CREATE TABLE "omoccurgenetic" (
1424
  "idoccurgenetic" integer NOT NULL,
1425
  "occid" integer NOT NULL,
1426
  "identifier" varchar(150) DEFAULT NULL,
1427
  "resourcename" varchar(150) NOT NULL,
1428
  "locus" varchar(45) DEFAULT NULL,
1429
  "resourceurl" varchar(500) DEFAULT NULL,
1430
  "notes" varchar(45) DEFAULT NULL,
1431
  "initialtimestamp" varchar(45) DEFAULT NULL,
1432
  PRIMARY KEY ("idoccurgenetic"),
1433
  /*KEY "FK_omoccurgenetic" ("occid")*/CHECK (true),
1434
  /*CONSTRAINT "FK_omoccurgenetic" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
1435
);
1436
/*!40101 SET character_set_client = @saved_cs_client */;
1437
1438
--
1439
-- Table structure for table "omoccurgeoindex"
1440
--
1441
1442
DROP TABLE IF EXISTS "omoccurgeoindex";
1443
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1444
/*!40101 SET character_set_client = utf8 */;
1445
CREATE TABLE "omoccurgeoindex" (
1446
  "tid" integer NOT NULL,
1447
  "decimallatitude" double precision NOT NULL,
1448
  "decimallongitude" double precision NOT NULL,
1449 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1450 7545 aaronmk
  PRIMARY KEY ("tid","decimallatitude","decimallongitude"),
1451
  /*CONSTRAINT "FK_specgeoindex_taxa" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
1452
);
1453
/*!40101 SET character_set_client = @saved_cs_client */;
1454
1455
--
1456
-- Table structure for table "omoccurloans"
1457
--
1458
1459
DROP TABLE IF EXISTS "omoccurloans";
1460
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1461
/*!40101 SET character_set_client = utf8 */;
1462
CREATE TABLE "omoccurloans" (
1463
  "loanid" integer NOT NULL,
1464
  "loanIdentifierOwn" varchar(30) DEFAULT NULL,
1465
  "loanIdentifierBorr" varchar(30) DEFAULT NULL,
1466
  "collidOwn" integer DEFAULT NULL,
1467
  "collidBorr" integer DEFAULT NULL,
1468
  "iidOwner" integer DEFAULT NULL,
1469
  "iidBorrower" integer DEFAULT NULL,
1470 10443 aaronmk
  "dateSent" text/*date*/ DEFAULT NULL,
1471
  "dateSentReturn" text/*date*/ DEFAULT NULL,
1472 7545 aaronmk
  "receivedStatus" varchar(250) DEFAULT NULL,
1473
  "totalBoxes" integer DEFAULT NULL,
1474
  "totalBoxesReturned" integer DEFAULT NULL,
1475
  "numSpecimens" integer DEFAULT NULL,
1476
  "shippingMethod" varchar(50) DEFAULT NULL,
1477
  "shippingMethodReturn" varchar(50) DEFAULT NULL,
1478 10443 aaronmk
  "dateDue" text/*date*/ DEFAULT NULL,
1479
  "dateReceivedOwn" text/*date*/ DEFAULT NULL,
1480
  "dateReceivedBorr" text/*date*/ DEFAULT NULL,
1481
  "dateClosed" text/*date*/ DEFAULT NULL,
1482 7545 aaronmk
  "forWhom" varchar(50) DEFAULT NULL,
1483
  "description" varchar(1000) DEFAULT NULL,
1484
  "invoiceMessageOwn" varchar(500) DEFAULT NULL,
1485
  "invoiceMessageBorr" varchar(500) DEFAULT NULL,
1486
  "notes" varchar(500) DEFAULT NULL,
1487
  "createdByOwn" varchar(30) DEFAULT NULL,
1488
  "createdByBorr" varchar(30) DEFAULT NULL,
1489
  "processingStatus" integer DEFAULT '1',
1490
  "processedByOwn" varchar(30) DEFAULT NULL,
1491
  "processedByBorr" varchar(30) DEFAULT NULL,
1492
  "processedByReturnOwn" varchar(30) DEFAULT NULL,
1493
  "processedByReturnBorr" varchar(30) DEFAULT NULL,
1494 10443 aaronmk
  "initialTimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1495 7545 aaronmk
  PRIMARY KEY ("loanid"),
1496
  /*KEY "FK_occurloans_owninst" ("iidOwner")*/CHECK (true),
1497
  /*KEY "FK_occurloans_borrinst" ("iidBorrower")*/CHECK (true),
1498
  /*KEY "FK_occurloans_owncoll" ("collidOwn")*/CHECK (true),
1499
  /*KEY "FK_occurloans_borrcoll" ("collidBorr")*/CHECK (true),
1500
  /*CONSTRAINT "FK_occurloans_borrcoll" FOREIGN KEY ("collidBorr") REFERENCES "omcollections" ("CollID") ON DELETE NO ACTION ON UPDATE NO ACTION*/CHECK (true),
1501
  /*CONSTRAINT "FK_occurloans_borrinst" FOREIGN KEY ("iidBorrower") REFERENCES "institutions" ("iid")*/CHECK (true),
1502
  /*CONSTRAINT "FK_occurloans_owncoll" FOREIGN KEY ("collidOwn") REFERENCES "omcollections" ("CollID") ON DELETE NO ACTION ON UPDATE NO ACTION*/CHECK (true),
1503
  /*CONSTRAINT "FK_occurloans_owninst" FOREIGN KEY ("iidOwner") REFERENCES "institutions" ("iid")*/CHECK (true)
1504
);
1505
/*!40101 SET character_set_client = @saved_cs_client */;
1506
1507
--
1508
-- Table structure for table "omoccurloanslink"
1509
--
1510
1511
DROP TABLE IF EXISTS "omoccurloanslink";
1512
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1513
/*!40101 SET character_set_client = utf8 */;
1514
CREATE TABLE "omoccurloanslink" (
1515
  "loanid" integer NOT NULL,
1516
  "occid" integer NOT NULL,
1517 10443 aaronmk
  "returndate" text/*date*/ DEFAULT NULL,
1518 7545 aaronmk
  "notes" varchar(255) DEFAULT NULL,
1519 10443 aaronmk
  "initialTimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1520 7545 aaronmk
  PRIMARY KEY ("loanid","occid"),
1521
  /*KEY "FK_occurloanlink_occid" ("occid")*/CHECK (true),
1522
  /*KEY "FK_occurloanlink_loanid" ("loanid")*/CHECK (true),
1523
  /*CONSTRAINT "FK_occurloanlink_loanid" FOREIGN KEY ("loanid") REFERENCES "omoccurloans" ("loanid") ON UPDATE CASCADE*/CHECK (true),
1524
  /*CONSTRAINT "FK_occurloanlink_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON UPDATE CASCADE*/CHECK (true)
1525
);
1526
/*!40101 SET character_set_client = @saved_cs_client */;
1527
1528
--
1529
-- Table structure for table "omoccurrences"
1530
--
1531
1532
DROP TABLE IF EXISTS "omoccurrences";
1533
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1534
/*!40101 SET character_set_client = utf8 */;
1535
CREATE TABLE "omoccurrences" (
1536
  "occid" integer NOT NULL,
1537
  "collid" integer NOT NULL,
1538
  "dbpk" varchar(45) DEFAULT NULL,
1539 10444 aaronmk
  "basisOfRecord" varchar(32) DEFAULT 'PreservedSpecimen' /*COMMENT 'PreservedSpecimen, LivingSpecimen, HumanObservation'*/,
1540
  "occurrenceID" varchar(255) DEFAULT NULL /*COMMENT 'UniqueGlobalIdentifier'*/,
1541 7545 aaronmk
  "catalogNumber" varchar(32) DEFAULT NULL,
1542
  "institutionID" varchar(255) DEFAULT NULL,
1543
  "collectionID" varchar(255) DEFAULT NULL,
1544
  "institutionCode" varchar(64) DEFAULT NULL,
1545
  "collectionCode" varchar(64) DEFAULT NULL,
1546
  "datasetID" varchar(255) DEFAULT NULL,
1547
  "otherCatalogNumbers" varchar(255) DEFAULT NULL,
1548
  "ownerInstitutionCode" varchar(32) DEFAULT NULL,
1549
  "family" varchar(255) DEFAULT NULL,
1550
  "scientificName" varchar(255) DEFAULT NULL,
1551
  "sciname" varchar(255) DEFAULT NULL,
1552
  "tidinterpreted" integer DEFAULT NULL,
1553
  "genus" varchar(255) DEFAULT NULL,
1554
  "specificEpithet" varchar(255) DEFAULT NULL,
1555
  "taxonRank" varchar(32) DEFAULT NULL,
1556
  "infraspecificEpithet" varchar(255) DEFAULT NULL,
1557
  "scientificNameAuthorship" varchar(255) DEFAULT NULL,
1558
  "taxonRemarks" text,
1559
  "identifiedBy" varchar(255) DEFAULT NULL,
1560
  "dateIdentified" varchar(45) DEFAULT NULL,
1561
  "identificationReferences" text,
1562
  "identificationRemarks" text,
1563 10444 aaronmk
  "identificationQualifier" varchar(255) DEFAULT NULL /*COMMENT 'cf, aff, etc'*/,
1564 7545 aaronmk
  "typeStatus" varchar(255) DEFAULT NULL,
1565 10444 aaronmk
  "recordedBy" varchar(255) DEFAULT NULL /*COMMENT 'Collector(s)'*/,
1566
  "recordNumber" varchar(45) DEFAULT NULL /*COMMENT 'Collector Number'*/,
1567 7545 aaronmk
  "recordedById" integer DEFAULT NULL,
1568 10444 aaronmk
  "associatedCollectors" varchar(255) DEFAULT NULL /*COMMENT 'not DwC'*/,
1569 10443 aaronmk
  "eventDate" text/*date*/ DEFAULT NULL,
1570 7545 aaronmk
  "year" integer DEFAULT NULL,
1571
  "month" integer DEFAULT NULL,
1572
  "day" integer DEFAULT NULL,
1573
  "startDayOfYear" integer DEFAULT NULL,
1574
  "endDayOfYear" integer DEFAULT NULL,
1575
  "verbatimEventDate" varchar(255) DEFAULT NULL,
1576 10444 aaronmk
  "habitat" text /*COMMENT 'Habitat, substrait, etc'*/,
1577 7545 aaronmk
  "substrate" varchar(500) DEFAULT NULL,
1578
  "fieldNotes" text,
1579
  "fieldnumber" varchar(45) DEFAULT NULL,
1580 10444 aaronmk
  "occurrenceRemarks" text /*COMMENT 'General Notes'*/,
1581 7545 aaronmk
  "informationWithheld" varchar(250) DEFAULT NULL,
1582
  "dataGeneralizations" varchar(250) DEFAULT NULL,
1583
  "associatedOccurrences" text,
1584 10444 aaronmk
  "associatedTaxa" text /*COMMENT 'Associated Species'*/,
1585 7545 aaronmk
  "dynamicProperties" text,
1586
  "verbatimAttributes" text,
1587 10444 aaronmk
  "attributes" text /*COMMENT 'Plant Description?'*/,
1588
  "reproductiveCondition" varchar(255) DEFAULT NULL /*COMMENT 'Phenology: flowers, fruit, sterile'*/,
1589
  "cultivationStatus" integer DEFAULT NULL /*COMMENT '0 = wild, 1 = cultivated'*/,
1590
  "establishmentMeans" varchar(45) DEFAULT NULL /*COMMENT 'cultivated, invasive, escaped from captivity, wild, native'*/,
1591 7545 aaronmk
  "lifeStage" varchar(45) DEFAULT NULL,
1592
  "sex" varchar(45) DEFAULT NULL,
1593
  "individualCount" varchar(45) DEFAULT NULL,
1594
  "samplingProtocol" varchar(100) DEFAULT NULL,
1595
  "preparations" varchar(100) DEFAULT NULL,
1596
  "country" varchar(64) DEFAULT NULL,
1597
  "stateProvince" varchar(255) DEFAULT NULL,
1598
  "county" varchar(255) DEFAULT NULL,
1599
  "municipality" varchar(255) DEFAULT NULL,
1600
  "locality" text,
1601 10444 aaronmk
  "localitySecurity" integer DEFAULT '0' /*COMMENT '0 = no security; 1 = hidden locality'*/,
1602 7545 aaronmk
  "localitySecurityReason" varchar(100) DEFAULT NULL,
1603
  "decimalLatitude" double precision DEFAULT NULL,
1604
  "decimalLongitude" double precision DEFAULT NULL,
1605
  "geodeticDatum" varchar(255) DEFAULT NULL,
1606
  "coordinateUncertaintyInMeters" integer DEFAULT NULL,
1607
  "footprintWKT" text,
1608
  "coordinatePrecision" decimal(9,7) DEFAULT NULL,
1609
  "locationRemarks" text,
1610
  "verbatimCoordinates" varchar(255) DEFAULT NULL,
1611
  "verbatimCoordinateSystem" varchar(255) DEFAULT NULL,
1612
  "georeferencedBy" varchar(255) DEFAULT NULL,
1613
  "georeferenceProtocol" varchar(255) DEFAULT NULL,
1614
  "georeferenceSources" varchar(255) DEFAULT NULL,
1615
  "georeferenceVerificationStatus" varchar(32) DEFAULT NULL,
1616
  "georeferenceRemarks" varchar(255) DEFAULT NULL,
1617
  "minimumElevationInMeters" integer DEFAULT NULL,
1618
  "maximumElevationInMeters" integer DEFAULT NULL,
1619
  "verbatimElevation" varchar(255) DEFAULT NULL,
1620
  "previousIdentifications" text,
1621
  "disposition" varchar(100) DEFAULT NULL,
1622
  "genericcolumn1" varchar(100) DEFAULT NULL,
1623
  "genericcolumn2" varchar(100) DEFAULT NULL,
1624 10444 aaronmk
  "modified" text/*datetime*/ DEFAULT NULL /*COMMENT 'DateLastModified'*/,
1625 7545 aaronmk
  "language" varchar(20) DEFAULT NULL,
1626
  "duplicateid" integer DEFAULT NULL,
1627
  "observeruid" integer DEFAULT NULL,
1628
  "processingstatus" varchar(45) DEFAULT NULL,
1629
  "deaccessioned" integer NOT NULL DEFAULT '0',
1630
  "recordEnteredBy" varchar(250) DEFAULT NULL,
1631
  "duplicateQuantity" integer DEFAULT NULL,
1632
  "labelProject" varchar(50) DEFAULT NULL,
1633 10443 aaronmk
  "dateLastModified" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1634 7545 aaronmk
  PRIMARY KEY ("occid"),
1635
  /*CONSTRAINT "Index_collid" */UNIQUE ("collid","dbpk"),
1636
  /*KEY "Index_sciname" ("sciname")*/CHECK (true),
1637
  /*KEY "Index_family" ("family")*/CHECK (true),
1638
  /*KEY "Index_country" ("country")*/CHECK (true),
1639
  /*KEY "Index_state" ("stateProvince")*/CHECK (true),
1640
  /*KEY "Index_county" ("county")*/CHECK (true),
1641
  /*KEY "Index_collector" ("recordedBy")*/CHECK (true),
1642
  /*KEY "Index_gui" ("occurrenceID")*/CHECK (true),
1643
  /*KEY "Index_ownerInst" ("ownerInstitutionCode")*/CHECK (true),
1644
  /*KEY "FK_omoccurrences_tid" ("tidinterpreted")*/CHECK (true),
1645
  /*KEY "FK_omoccurrences_uid" ("observeruid")*/CHECK (true),
1646
  /*KEY "Index_municipality" ("municipality")*/CHECK (true),
1647
  /*KEY "Index_collnum" ("recordNumber")*/CHECK (true),
1648
  /*KEY "Index_catalognumber" ("catalogNumber")*/CHECK (true),
1649
  /*KEY "FK_recordedbyid" ("recordedById")*/CHECK (true),
1650
  /*KEY "FK_omoccurrences_dupes" ("duplicateid")*/CHECK (true),
1651
  /*KEY "Index_eventDate" ("eventDate")*/CHECK (true),
1652
  /*KEY "Index_occurrences_procstatus" ("processingstatus")*/CHECK (true),
1653
  /*CONSTRAINT "FK_omoccurrences_collid" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
1654
  /*CONSTRAINT "FK_omoccurrences_dupes" FOREIGN KEY ("duplicateid") REFERENCES "omoccurduplicates" ("duplicateid") ON DELETE SET NULL ON UPDATE CASCADE*/CHECK (true),
1655
  /*CONSTRAINT "FK_omoccurrences_recbyid" FOREIGN KEY ("recordedById") REFERENCES "omcollectors" ("recordedById") ON DELETE SET NULL ON UPDATE CASCADE*/CHECK (true),
1656
  /*CONSTRAINT "FK_omoccurrences_tid" FOREIGN KEY ("tidinterpreted") REFERENCES "taxa" ("TID") ON DELETE SET NULL ON UPDATE CASCADE*/CHECK (true),
1657
  /*CONSTRAINT "FK_omoccurrences_uid" FOREIGN KEY ("observeruid") REFERENCES "users" ("uid")*/CHECK (true)
1658
);
1659
/*!40101 SET character_set_client = @saved_cs_client */;
1660
1661
--
1662
-- Table structure for table "omoccurverification"
1663
--
1664
1665
DROP TABLE IF EXISTS "omoccurverification";
1666
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1667
/*!40101 SET character_set_client = utf8 */;
1668
CREATE TABLE "omoccurverification" (
1669
  "ovsid" integer NOT NULL,
1670
  "occid" integer NOT NULL,
1671
  "category" varchar(45) NOT NULL,
1672
  "ranking" integer NOT NULL,
1673
  "protocol" varchar(100) DEFAULT NULL,
1674
  "source" varchar(45) DEFAULT NULL,
1675
  "uid" integer DEFAULT NULL,
1676
  "notes" varchar(250) DEFAULT NULL,
1677 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP,
1678 7545 aaronmk
  PRIMARY KEY ("ovsid"),
1679
  /*KEY "FK_omoccurverification_occid_idx" ("occid")*/CHECK (true),
1680
  /*KEY "FK_omoccurverification_uid_idx" ("uid")*/CHECK (true),
1681
  /*CONSTRAINT "FK_omoccurverification_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
1682
  /*CONSTRAINT "FK_omoccurverification_uid" FOREIGN KEY ("uid") REFERENCES "users" ("uid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
1683
);
1684
/*!40101 SET character_set_client = @saved_cs_client */;
1685
1686
--
1687
-- Table structure for table "omsurveyoccurlink"
1688
--
1689
1690
DROP TABLE IF EXISTS "omsurveyoccurlink";
1691
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1692
/*!40101 SET character_set_client = utf8 */;
1693
CREATE TABLE "omsurveyoccurlink" (
1694
  "occid" integer NOT NULL,
1695
  "surveyid" integer NOT NULL,
1696
  "notes" varchar(250) DEFAULT NULL,
1697 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1698 7545 aaronmk
  PRIMARY KEY ("occid","surveyid"),
1699
  /*KEY "FK_omsurveyoccurlink_sur" ("surveyid")*/CHECK (true),
1700
  /*CONSTRAINT "FK_omsurveyoccurlink_occ" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid")*/CHECK (true),
1701
  /*CONSTRAINT "FK_omsurveyoccurlink_sur" FOREIGN KEY ("surveyid") REFERENCES "omsurveys" ("surveyid")*/CHECK (true)
1702
);
1703
/*!40101 SET character_set_client = @saved_cs_client */;
1704
1705
--
1706
-- Table structure for table "omsurveyprojlink"
1707
--
1708
1709
DROP TABLE IF EXISTS "omsurveyprojlink";
1710
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1711
/*!40101 SET character_set_client = utf8 */;
1712
CREATE TABLE "omsurveyprojlink" (
1713
  "surveyid" integer NOT NULL,
1714
  "pid" integer NOT NULL,
1715 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1716 7545 aaronmk
  PRIMARY KEY ("surveyid","pid"),
1717
  /*KEY "FK_specprojcatlink_cat" ("pid")*/CHECK (true),
1718
  /*CONSTRAINT "FK_omsurveyprojlink_proj" FOREIGN KEY ("pid") REFERENCES "fmprojects" ("pid")*/CHECK (true),
1719
  /*CONSTRAINT "FK_omsurveyprojlink_sur" FOREIGN KEY ("surveyid") REFERENCES "omsurveys" ("surveyid")*/CHECK (true)
1720
);
1721
/*!40101 SET character_set_client = @saved_cs_client */;
1722
1723
--
1724
-- Table structure for table "omsurveys"
1725
--
1726
1727
DROP TABLE IF EXISTS "omsurveys";
1728
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1729
/*!40101 SET character_set_client = utf8 */;
1730
CREATE TABLE "omsurveys" (
1731
  "surveyid" integer NOT NULL,
1732
  "projectname" varchar(75) NOT NULL,
1733
  "locality" varchar(1000) DEFAULT NULL,
1734
  "managers" varchar(150) DEFAULT NULL,
1735
  "latcentroid" double precision DEFAULT NULL,
1736
  "longcentroid" double precision DEFAULT NULL,
1737
  "notes" varchar(250) DEFAULT NULL,
1738
  "ispublic" integer NOT NULL DEFAULT '0',
1739
  "sortsequence" integer NOT NULL DEFAULT '50',
1740 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1741 7545 aaronmk
  PRIMARY KEY ("surveyid")
1742
);
1743
/*!40101 SET character_set_client = @saved_cs_client */;
1744
1745
--
1746
-- Table structure for table "specprocessorprojects"
1747
--
1748
1749
DROP TABLE IF EXISTS "specprocessorprojects";
1750
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1751
/*!40101 SET character_set_client = utf8 */;
1752
CREATE TABLE "specprocessorprojects" (
1753
  "spprid" integer NOT NULL,
1754
  "collid" integer NOT NULL,
1755
  "title" varchar(100) NOT NULL,
1756
  "specKeyPattern" varchar(45) DEFAULT NULL,
1757
  "speckeyretrieval" varchar(45) DEFAULT NULL,
1758
  "coordX1" integer DEFAULT NULL,
1759
  "coordX2" integer DEFAULT NULL,
1760
  "coordY1" integer DEFAULT NULL,
1761
  "coordY2" integer DEFAULT NULL,
1762
  "sourcePath" varchar(250) DEFAULT NULL,
1763
  "targetPath" varchar(250) DEFAULT NULL,
1764
  "imgUrl" varchar(250) DEFAULT NULL,
1765
  "webPixWidth" integer DEFAULT '1200',
1766
  "tnPixWidth" integer DEFAULT '130',
1767
  "lgPixWidth" integer DEFAULT '2400',
1768
  "jpgcompression" integer DEFAULT '70',
1769
  "createTnImg" integer DEFAULT '1',
1770
  "createLgImg" integer DEFAULT '1',
1771 10443 aaronmk
  "initialTimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1772 7545 aaronmk
  PRIMARY KEY ("spprid"),
1773
  /*KEY "FK_specprocessorprojects_coll" ("collid")*/CHECK (true),
1774
  /*CONSTRAINT "FK_specprocessorprojects_coll" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
1775
);
1776
/*!40101 SET character_set_client = @saved_cs_client */;
1777
1778
--
1779
-- Table structure for table "specprocessorrawlabels"
1780
--
1781
1782
DROP TABLE IF EXISTS "specprocessorrawlabels";
1783
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1784
/*!40101 SET character_set_client = utf8 */;
1785
CREATE TABLE "specprocessorrawlabels" (
1786
  "prlid" integer NOT NULL,
1787
  "imgid" integer DEFAULT NULL,
1788
  "occid" integer DEFAULT NULL,
1789
  "rawstr" text NOT NULL,
1790
  "processingvariables" varchar(250) DEFAULT NULL,
1791
  "score" integer DEFAULT NULL,
1792
  "notes" varchar(255) DEFAULT NULL,
1793
  "source" varchar(150) DEFAULT NULL,
1794
  "sortsequence" integer DEFAULT NULL,
1795 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1796 7545 aaronmk
  PRIMARY KEY ("prlid"),
1797
  /*KEY "FK_specproc_images" ("imgid")*/CHECK (true),
1798
  /*KEY "FK_specproc_occid" ("occid")*/CHECK (true),
1799
  /*CONSTRAINT "FK_specproc_images" FOREIGN KEY ("imgid") REFERENCES "images" ("imgid") ON UPDATE CASCADE*/CHECK (true),
1800
  /*CONSTRAINT "FK_specproc_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
1801
);
1802
/*!40101 SET character_set_client = @saved_cs_client */;
1803
1804
--
1805
-- Table structure for table "specprocnlp"
1806
--
1807
1808
DROP TABLE IF EXISTS "specprocnlp";
1809
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1810
/*!40101 SET character_set_client = utf8 */;
1811
CREATE TABLE "specprocnlp" (
1812
  "spnlpid" integer NOT NULL,
1813
  "title" varchar(45) NOT NULL,
1814
  "sqlfrag" varchar(250) NOT NULL,
1815
  "patternmatch" varchar(250) DEFAULT NULL,
1816
  "notes" varchar(250) DEFAULT NULL,
1817
  "collid" integer NOT NULL,
1818 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1819 7545 aaronmk
  PRIMARY KEY ("spnlpid"),
1820
  /*KEY "FK_specprocnlp_collid" ("collid")*/CHECK (true),
1821
  /*CONSTRAINT "FK_specprocnlp_collid" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
1822
);
1823
/*!40101 SET character_set_client = @saved_cs_client */;
1824
1825
--
1826
-- Table structure for table "specprocnlpfrag"
1827
--
1828
1829
DROP TABLE IF EXISTS "specprocnlpfrag";
1830
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1831
/*!40101 SET character_set_client = utf8 */;
1832
CREATE TABLE "specprocnlpfrag" (
1833
  "spnlpfragid" integer NOT NULL,
1834
  "spnlpid" integer NOT NULL,
1835
  "fieldname" varchar(45) NOT NULL,
1836
  "patternmatch" varchar(250) NOT NULL,
1837
  "notes" varchar(250) DEFAULT NULL,
1838
  "sortseq" integer DEFAULT '50',
1839 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP,
1840 7545 aaronmk
  PRIMARY KEY ("spnlpfragid"),
1841
  /*KEY "FK_specprocnlpfrag_spnlpid" ("spnlpid")*/CHECK (true),
1842
  /*CONSTRAINT "FK_specprocnlpfrag_spnlpid" FOREIGN KEY ("spnlpid") REFERENCES "specprocnlp" ("spnlpid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
1843
);
1844
/*!40101 SET character_set_client = @saved_cs_client */;
1845
1846
--
1847
-- Table structure for table "taxa"
1848
--
1849
1850
DROP TABLE IF EXISTS "taxa";
1851
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1852
/*!40101 SET character_set_client = utf8 */;
1853
CREATE TABLE "taxa" (
1854
  "TID" integer NOT NULL,
1855
  "KingdomID" integer NOT NULL DEFAULT '3',
1856
  "RankId" integer NOT NULL DEFAULT '220',
1857
  "SciName" varchar(250) NOT NULL,
1858
  "UnitInd1" varchar(1) DEFAULT NULL,
1859
  "UnitName1" varchar(50) NOT NULL,
1860
  "UnitInd2" varchar(1) DEFAULT NULL,
1861
  "UnitName2" varchar(50) DEFAULT NULL,
1862
  "UnitInd3" varchar(7) DEFAULT NULL,
1863
  "UnitName3" varchar(35) DEFAULT NULL,
1864
  "Author" varchar(100) DEFAULT NULL,
1865
  "PhyloSortSequence" integer DEFAULT NULL,
1866 10443 aaronmk
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1867 7545 aaronmk
  "Status" varchar(50) DEFAULT NULL,
1868
  "Source" varchar(250) DEFAULT NULL,
1869
  "Notes" varchar(250) DEFAULT NULL,
1870
  "Hybrid" varchar(50) DEFAULT NULL,
1871 10444 aaronmk
  "fnaprikey" integer DEFAULT NULL /*COMMENT 'Primary key for FNA project'*/,
1872 7545 aaronmk
  "UsdaSymbol" varchar(50) DEFAULT NULL,
1873 10444 aaronmk
  "SecurityStatus" integer NOT NULL DEFAULT '0' /*COMMENT '0 = no security; 1 = hidden locality'*/,
1874 7545 aaronmk
  PRIMARY KEY ("TID"),
1875
  /*CONSTRAINT "sciname_unique" */UNIQUE ("SciName"),
1876
  /*KEY "rankid_index" ("RankId")*/CHECK (true),
1877
  /*KEY "unitname1_index" ("UnitName1","UnitName2")*/CHECK (true),
1878
  /*KEY "FK_taxa_taxonunits" ("KingdomID","RankId")*/CHECK (true),
1879
  /*CONSTRAINT "taxa_ibfk_1" FOREIGN KEY ("KingdomID", "RankId") REFERENCES "taxonunits" ("kingdomid", "rankid")*/CHECK (true)
1880
);
1881
/*!40101 SET character_set_client = @saved_cs_client */;
1882
1883
--
1884
-- Table structure for table "taxadescrblock"
1885
--
1886
1887
DROP TABLE IF EXISTS "taxadescrblock";
1888
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1889
/*!40101 SET character_set_client = utf8 */;
1890
CREATE TABLE "taxadescrblock" (
1891
  "tdbid" integer NOT NULL,
1892
  "tid" integer NOT NULL,
1893
  "caption" varchar(30) DEFAULT NULL,
1894
  "source" varchar(250) DEFAULT NULL,
1895
  "sourceurl" varchar(250) DEFAULT NULL,
1896
  "language" varchar(45) DEFAULT 'English',
1897 10444 aaronmk
  "displaylevel" integer NOT NULL DEFAULT '1' /*COMMENT '1 = short descr, 2 = intermediate descr'*/,
1898 7545 aaronmk
  "uid" integer NOT NULL,
1899
  "notes" varchar(250) DEFAULT NULL,
1900 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1901 7545 aaronmk
  PRIMARY KEY ("tdbid"),
1902
  /*CONSTRAINT "Index_unique" */UNIQUE ("tid","displaylevel","language"),
1903
  /*CONSTRAINT "taxadescrblock_ibfk_1" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID")*/CHECK (true)
1904
);
1905
/*!40101 SET character_set_client = @saved_cs_client */;
1906
1907
--
1908
-- Table structure for table "taxadescrstmts"
1909
--
1910
1911
DROP TABLE IF EXISTS "taxadescrstmts";
1912
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1913
/*!40101 SET character_set_client = utf8 */;
1914
CREATE TABLE "taxadescrstmts" (
1915
  "tdsid" integer NOT NULL,
1916
  "tdbid" integer NOT NULL,
1917
  "heading" varchar(75) NOT NULL,
1918
  "statement" text NOT NULL,
1919
  "displayheader" integer NOT NULL DEFAULT '1',
1920
  "notes" varchar(250) DEFAULT NULL,
1921
  "sortsequence" integer NOT NULL DEFAULT '89',
1922 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1923 7545 aaronmk
  PRIMARY KEY ("tdsid"),
1924
  /*KEY "FK_taxadescrstmts_tblock" ("tdbid")*/CHECK (true),
1925
  /*CONSTRAINT "taxadescrstmts_ibfk_1" FOREIGN KEY ("tdbid") REFERENCES "taxadescrblock" ("tdbid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
1926
);
1927
/*!40101 SET character_set_client = @saved_cs_client */;
1928
1929
--
1930
-- Table structure for table "taxaenumtree"
1931
--
1932
1933
DROP TABLE IF EXISTS "taxaenumtree";
1934
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1935
/*!40101 SET character_set_client = utf8 */;
1936
CREATE TABLE "taxaenumtree" (
1937
  "tid" integer NOT NULL,
1938
  "taxauthid" integer NOT NULL,
1939
  "parenttid" integer NOT NULL,
1940 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1941 7545 aaronmk
  PRIMARY KEY ("tid","taxauthid","parenttid"),
1942
  /*KEY "FK_tet_taxa" ("tid")*/CHECK (true),
1943
  /*KEY "FK_tet_taxauth" ("taxauthid")*/CHECK (true),
1944
  /*KEY "FK_tet_taxa2" ("parenttid")*/CHECK (true),
1945
  /*CONSTRAINT "FK_tet_taxa" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
1946
  /*CONSTRAINT "FK_tet_taxa2" FOREIGN KEY ("parenttid") REFERENCES "taxa" ("TID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
1947
  /*CONSTRAINT "FK_tet_taxauth" FOREIGN KEY ("taxauthid") REFERENCES "taxauthority" ("taxauthid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
1948
);
1949
/*!40101 SET character_set_client = @saved_cs_client */;
1950
1951
--
1952
-- Table structure for table "taxalinks"
1953
--
1954
1955
DROP TABLE IF EXISTS "taxalinks";
1956
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1957
/*!40101 SET character_set_client = utf8 */;
1958
CREATE TABLE "taxalinks" (
1959
  "tlid" integer NOT NULL,
1960
  "tid" integer NOT NULL,
1961
  "url" varchar(500) NOT NULL,
1962
  "title" varchar(100) NOT NULL,
1963
  "sourceIdentifier" varchar(45) DEFAULT NULL,
1964
  "owner" varchar(100) DEFAULT NULL,
1965
  "icon" varchar(45) DEFAULT NULL,
1966
  "notes" varchar(250) DEFAULT NULL,
1967
  "sortsequence" integer NOT NULL DEFAULT '50',
1968 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1969 7545 aaronmk
  PRIMARY KEY ("tlid"),
1970
  /*KEY "Index_unique" ("tid","url")*/CHECK (true),
1971
  /*CONSTRAINT "taxalinks_ibfk_1" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID")*/CHECK (true)
1972
);
1973
/*!40101 SET character_set_client = @saved_cs_client */;
1974
1975
--
1976
-- Table structure for table "taxamapparams"
1977
--
1978
1979
DROP TABLE IF EXISTS "taxamapparams";
1980
/*!40101 SET @saved_cs_client     = @@character_set_client */;
1981
/*!40101 SET character_set_client = utf8 */;
1982
CREATE TABLE "taxamapparams" (
1983
  "dmid" integer NOT NULL,
1984
  "name" varchar(45) NOT NULL,
1985 10444 aaronmk
  "maptype" varchar(45) NOT NULL /*COMMENT 'custom; google dynamic'*/,
1986 7545 aaronmk
  "regionofinterest" varchar(45) DEFAULT NULL,
1987
  "basefilepath" varchar(250) DEFAULT NULL,
1988
  "maptargetpath" varchar(250) NOT NULL,
1989
  "mapurlpath" varchar(250) NOT NULL,
1990
  "latnorth" double precision DEFAULT NULL,
1991
  "latsouth" double precision DEFAULT NULL,
1992
  "longwest" double precision DEFAULT NULL,
1993
  "longeast" double precision DEFAULT NULL,
1994
  "pointsize" integer DEFAULT NULL,
1995
  "red" integer DEFAULT NULL,
1996
  "green" integer DEFAULT NULL,
1997
  "blue" integer DEFAULT NULL,
1998
  "latadjustfactor" double precision NOT NULL DEFAULT '0',
1999 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2000 7545 aaronmk
  PRIMARY KEY ("dmid")
2001
);
2002
/*!40101 SET character_set_client = @saved_cs_client */;
2003
2004
--
2005
-- Table structure for table "taxamaps"
2006
--
2007
2008
DROP TABLE IF EXISTS "taxamaps";
2009
/*!40101 SET @saved_cs_client     = @@character_set_client */;
2010
/*!40101 SET character_set_client = utf8 */;
2011
CREATE TABLE "taxamaps" (
2012
  "mid" integer NOT NULL,
2013
  "tid" integer NOT NULL,
2014
  "url" varchar(255) NOT NULL,
2015
  "title" varchar(100) DEFAULT NULL,
2016
  "dmid" integer DEFAULT NULL,
2017 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2018 7545 aaronmk
  PRIMARY KEY ("mid"),
2019
  /*CONSTRAINT "Index_unique" */UNIQUE ("tid","dmid"),
2020
  /*KEY "FK_taxamaps_dmid" ("dmid")*/CHECK (true),
2021
  /*CONSTRAINT "taxamaps_ibfk_1" FOREIGN KEY ("dmid") REFERENCES "taxamapparams" ("dmid") ON UPDATE CASCADE*/CHECK (true),
2022
  /*CONSTRAINT "taxamaps_ibfk_2" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID")*/CHECK (true)
2023
);
2024
/*!40101 SET character_set_client = @saved_cs_client */;
2025
2026
--
2027
-- Table structure for table "taxanestedtree"
2028
--
2029
2030
DROP TABLE IF EXISTS "taxanestedtree";
2031
/*!40101 SET @saved_cs_client     = @@character_set_client */;
2032
/*!40101 SET character_set_client = utf8 */;
2033
CREATE TABLE "taxanestedtree" (
2034
  "tid" integer NOT NULL,
2035
  "taxauthid" integer NOT NULL,
2036
  "leftindex" integer NOT NULL,
2037
  "rightindex" integer NOT NULL,
2038 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2039 7545 aaronmk
  PRIMARY KEY ("tid","taxauthid"),
2040
  /*KEY "leftindex" ("leftindex")*/CHECK (true),
2041
  /*KEY "rightindex" ("rightindex")*/CHECK (true),
2042
  /*KEY "FK_tnt_taxa" ("tid")*/CHECK (true),
2043
  /*KEY "FK_tnt_taxauth" ("taxauthid")*/CHECK (true),
2044
  /*CONSTRAINT "FK_tnt_taxa" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true),
2045
  /*CONSTRAINT "FK_tnt_taxauth" FOREIGN KEY ("taxauthid") REFERENCES "taxauthority" ("taxauthid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
2046
);
2047
/*!40101 SET character_set_client = @saved_cs_client */;
2048
2049
--
2050
-- Table structure for table "taxauthority"
2051
--
2052
2053
DROP TABLE IF EXISTS "taxauthority";
2054
/*!40101 SET @saved_cs_client     = @@character_set_client */;
2055
/*!40101 SET character_set_client = utf8 */;
2056
CREATE TABLE "taxauthority" (
2057
  "taxauthid" integer NOT NULL,
2058
  "isprimary" integer NOT NULL DEFAULT '0',
2059
  "name" varchar(45) NOT NULL,
2060
  "description" varchar(250) DEFAULT NULL,
2061
  "editors" varchar(150) DEFAULT NULL,
2062
  "contact" varchar(45) DEFAULT NULL,
2063
  "email" varchar(100) DEFAULT NULL,
2064
  "notes" varchar(250) DEFAULT NULL,
2065
  "isactive" integer NOT NULL DEFAULT '1',
2066 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2067 7545 aaronmk
  PRIMARY KEY ("taxauthid")
2068
);
2069
/*!40101 SET character_set_client = @saved_cs_client */;
2070
2071
--
2072
-- Table structure for table "taxavernaculars"
2073
--
2074
2075
DROP TABLE IF EXISTS "taxavernaculars";
2076
/*!40101 SET @saved_cs_client     = @@character_set_client */;
2077
/*!40101 SET character_set_client = utf8 */;
2078
CREATE TABLE "taxavernaculars" (
2079
  "TID" integer NOT NULL DEFAULT '0',
2080
  "VernacularName" varchar(80) NOT NULL,
2081
  "Language" varchar(15) NOT NULL DEFAULT 'English',
2082
  "Source" varchar(50) DEFAULT NULL,
2083
  "notes" varchar(250) DEFAULT NULL,
2084
  "username" varchar(45) DEFAULT NULL,
2085
  "isupperterm" integer DEFAULT '0',
2086
  "SortSequence" integer DEFAULT '50',
2087
  "VID" integer NOT NULL,
2088 10443 aaronmk
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2089 7545 aaronmk
  PRIMARY KEY ("VID"),
2090
  /*CONSTRAINT "unique-key" */UNIQUE ("Language","VernacularName","TID"),
2091
  /*KEY "tid1" ("TID")*/CHECK (true),
2092
  /*KEY "vernacularsnames" ("VernacularName")*/CHECK (true),
2093
  /*CONSTRAINT "taxavernaculars_ibfk_1" FOREIGN KEY ("TID") REFERENCES "taxa" ("TID")*/CHECK (true)
2094
);
2095
/*!40101 SET character_set_client = @saved_cs_client */;
2096
2097
--
2098
-- Table structure for table "taxonunits"
2099
--
2100
2101
DROP TABLE IF EXISTS "taxonunits";
2102
/*!40101 SET @saved_cs_client     = @@character_set_client */;
2103
/*!40101 SET character_set_client = utf8 */;
2104
CREATE TABLE "taxonunits" (
2105
  "kingdomid" integer NOT NULL,
2106
  "rankid" integer NOT NULL,
2107
  "rankname" varchar(15) NOT NULL,
2108
  "suffix" varchar(45) DEFAULT NULL,
2109
  "dirparentrankid" integer NOT NULL,
2110
  "reqparentrankid" integer DEFAULT NULL,
2111 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2112 7545 aaronmk
  PRIMARY KEY ("kingdomid","rankid")
2113
);
2114
/*!40101 SET character_set_client = @saved_cs_client */;
2115
2116
--
2117
-- Table structure for table "taxstatus"
2118
--
2119
2120
DROP TABLE IF EXISTS "taxstatus";
2121
/*!40101 SET @saved_cs_client     = @@character_set_client */;
2122
/*!40101 SET character_set_client = utf8 */;
2123
CREATE TABLE "taxstatus" (
2124
  "tid" integer NOT NULL,
2125
  "tidaccepted" integer NOT NULL,
2126 10444 aaronmk
  "taxauthid" integer NOT NULL /*COMMENT 'taxon authority id'*/,
2127 7545 aaronmk
  "parenttid" integer DEFAULT NULL,
2128
  "hierarchystr" varchar(200) DEFAULT NULL,
2129
  "uppertaxonomy" varchar(50) DEFAULT NULL,
2130
  "family" varchar(50) DEFAULT NULL,
2131
  "UnacceptabilityReason" varchar(45) DEFAULT NULL,
2132
  "notes" varchar(250) DEFAULT NULL,
2133
  "SortSequence" integer DEFAULT '50',
2134 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2135 7545 aaronmk
  PRIMARY KEY ("tid","tidaccepted","taxauthid"),
2136
  /*KEY "FK_taxstatus_tidacc" ("tidaccepted")*/CHECK (true),
2137
  /*KEY "FK_taxstatus_taid" ("taxauthid")*/CHECK (true),
2138
  /*KEY "Index_ts_family" ("family")*/CHECK (true),
2139
  /*KEY "Index_ts_upper" ("uppertaxonomy")*/CHECK (true),
2140
  /*KEY "Index_parenttid" ("parenttid")*/CHECK (true),
2141
  /*KEY "Index_hierarchy" ("hierarchystr")*/CHECK (true),
2142
  /*CONSTRAINT "taxstatus_ibfk_1" FOREIGN KEY ("parenttid") REFERENCES "taxa" ("TID")*/CHECK (true),
2143
  /*CONSTRAINT "taxstatus_ibfk_2" FOREIGN KEY ("taxauthid") REFERENCES "taxauthority" ("taxauthid") ON UPDATE CASCADE*/CHECK (true),
2144
  /*CONSTRAINT "taxstatus_ibfk_3" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID")*/CHECK (true),
2145
  /*CONSTRAINT "taxstatus_ibfk_4" FOREIGN KEY ("tidaccepted") REFERENCES "taxa" ("TID")*/CHECK (true)
2146
);
2147
/*!40101 SET character_set_client = @saved_cs_client */;
2148
2149
--
2150
-- Table structure for table "temp_NPS_Legacy_Results"
2151
--
2152
2153
DROP TABLE IF EXISTS "temp_NPS_Legacy_Results";
2154
/*!40101 SET @saved_cs_client     = @@character_set_client */;
2155
/*!40101 SET character_set_client = utf8 */;
2156
CREATE TABLE "temp_NPS_Legacy_Results" (
2157
  "CatNum" varchar(32) DEFAULT NULL,
2158
  "Family" varchar(255) DEFAULT NULL,
2159
  "ARIZ_Taxon_name" varchar(255) DEFAULT NULL,
2160
  "itis_taxon_name" varchar(250) DEFAULT NULL,
2161
  "TSN" varchar(255) DEFAULT NULL,
2162
  "Determiner" varchar(255) DEFAULT NULL,
2163
  "DateDetermined" varchar(45) DEFAULT NULL,
2164 10444 aaronmk
  "SpecNotes" text /*COMMENT 'General Notes'*/,
2165
  "PlantDesc" text /*COMMENT 'Plant Description?'*/,
2166
  "Habitat" text /*COMMENT 'Habitat, substrait, etc'*/,
2167
  "AssocSpp" text /*COMMENT 'Associated Species'*/,
2168
  "FirstColl" varchar(255) DEFAULT NULL /*COMMENT 'Collector(s)'*/,
2169
  "CollNum" varchar(45) DEFAULT NULL /*COMMENT 'Collector Number'*/,
2170 10443 aaronmk
  "DateColl" text/*date*/ DEFAULT NULL,
2171 7545 aaronmk
  "Country" varchar(64) DEFAULT NULL,
2172
  "State" varchar(255) DEFAULT NULL,
2173
  "County" varchar(255) DEFAULT NULL,
2174
  "Locality" text,
2175
  "OtherCords" varchar(255) DEFAULT NULL,
2176
  "Datum" varchar(255) DEFAULT NULL,
2177
  "LatDec" double precision DEFAULT NULL,
2178
  "LongDec" double precision DEFAULT NULL,
2179
  "Elev" varchar(255) DEFAULT NULL,
2180
  "Project" varchar(50) DEFAULT NULL,
2181
  "Cataloger" varchar(250) DEFAULT NULL,
2182 10443 aaronmk
  "DateModified" text/*timestamp*/ NOT NULL DEFAULT '0000-00-00 00:00:00',
2183 7545 aaronmk
  "occid" integer NOT NULL DEFAULT '0'
2184
);
2185
/*!40101 SET character_set_client = @saved_cs_client */;
2186
2187
--
2188
-- Table structure for table "temp_tbl_taxa_tsn"
2189
--
2190
2191
DROP TABLE IF EXISTS "temp_tbl_taxa_tsn";
2192
/*!40101 SET @saved_cs_client     = @@character_set_client */;
2193
/*!40101 SET character_set_client = utf8 */;
2194
CREATE TABLE "temp_tbl_taxa_tsn" (
2195
  "taxa_id" integer NOT NULL DEFAULT '0',
2196
  "family" varchar(35) DEFAULT NULL,
2197
  "unit_ind1" varchar(1) DEFAULT NULL,
2198
  "unit_name1" varchar(35) DEFAULT NULL,
2199
  "unit_ind2" varchar(1) DEFAULT NULL,
2200
  "unit_name2" varchar(35) DEFAULT NULL,
2201
  "author" varchar(100) DEFAULT NULL,
2202
  "unit_ind3" varchar(7) DEFAULT NULL,
2203
  "unit_name3" varchar(35) DEFAULT NULL,
2204
  "infrasp_author" varchar(100) DEFAULT NULL,
2205
  "unit_ind4" varchar(7) DEFAULT NULL,
2206
  "unit_name4" varchar(35) DEFAULT NULL,
2207
  "infrasp_author2" varchar(100) DEFAULT NULL,
2208
  "az_itis" integer DEFAULT NULL,
2209
  "status" varchar(50) DEFAULT NULL,
2210
  "acronym" varchar(10) DEFAULT NULL,
2211
  "security" integer DEFAULT NULL,
2212
  "notes" varchar(255) DEFAULT NULL,
2213
  "security_source" varchar(255) DEFAULT NULL,
2214
  "old_taxa_id" integer DEFAULT NULL,
2215
  "scientific_name" varchar(200) NOT NULL,
2216
  "taxonomic_group" varchar(150) DEFAULT NULL,
2217 10443 aaronmk
  "date_created" text/*timestamp*/ NOT NULL DEFAULT '0000-00-00 00:00:00',
2218 7545 aaronmk
  "rank" varchar(6) DEFAULT NULL,
2219
  "is_accepted" integer DEFAULT NULL,
2220
  "entered_by" varchar(60) DEFAULT NULL,
2221
  "type_publication_id" integer DEFAULT NULL,
2222
  "ariz_usda" varchar(50) DEFAULT NULL,
2223
  "ariz_tsn" varchar(255) DEFAULT NULL,
2224
  "itis_sciname" varchar(250) DEFAULT NULL,
2225
  "itis_usda" varchar(50) DEFAULT NULL,
2226
  "itis_tsn" varchar(255) DEFAULT NULL,
2227
  /*KEY "taxa_id" ("taxa_id")*/CHECK (true),
2228
  /*KEY "family" ("family")*/CHECK (true),
2229
  /*KEY "scientific_name" ("scientific_name")*/CHECK (true),
2230
  /*KEY "itis_sciname" ("itis_sciname")*/CHECK (true),
2231
  /*KEY "itis_tsn" ("itis_tsn")*/CHECK (true)
2232
);
2233
/*!40101 SET character_set_client = @saved_cs_client */;
2234
2235
--
2236
-- Table structure for table "test_duplicates"
2237
--
2238
2239
DROP TABLE IF EXISTS "test_duplicates";
2240
/*!40101 SET @saved_cs_client     = @@character_set_client */;
2241
/*!40101 SET character_set_client = utf8 */;
2242
CREATE TABLE "test_duplicates" (
2243
  "Accession" varchar(32) DEFAULT NULL,
2244
  "Duplicates" bigint NOT NULL DEFAULT '0'
2245
);
2246
/*!40101 SET character_set_client = @saved_cs_client */;
2247
2248
--
2249
-- Table structure for table "unknowncomments"
2250
--
2251
2252
DROP TABLE IF EXISTS "unknowncomments";
2253
/*!40101 SET @saved_cs_client     = @@character_set_client */;
2254
/*!40101 SET character_set_client = utf8 */;
2255
CREATE TABLE "unknowncomments" (
2256
  "unkcomid" integer NOT NULL,
2257
  "unkid" integer NOT NULL,
2258
  "comment" varchar(500) NOT NULL,
2259
  "username" varchar(45) NOT NULL,
2260 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2261 7545 aaronmk
  PRIMARY KEY ("unkcomid"),
2262
  /*KEY "FK_unknowncomments" ("unkid")*/CHECK (true),
2263
  /*CONSTRAINT "FK_unknowncomments" FOREIGN KEY ("unkid") REFERENCES "unknowns" ("unkid")*/CHECK (true)
2264
);
2265
/*!40101 SET character_set_client = @saved_cs_client */;
2266
2267
--
2268
-- Table structure for table "unknownimages"
2269
--
2270
2271
DROP TABLE IF EXISTS "unknownimages";
2272
/*!40101 SET @saved_cs_client     = @@character_set_client */;
2273
/*!40101 SET character_set_client = utf8 */;
2274
CREATE TABLE "unknownimages" (
2275
  "unkimgid" integer NOT NULL,
2276
  "unkid" integer NOT NULL,
2277
  "url" varchar(255) NOT NULL,
2278
  "notes" varchar(250) DEFAULT NULL,
2279 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2280 7545 aaronmk
  PRIMARY KEY ("unkimgid"),
2281
  /*KEY "FK_unknowns" ("unkid")*/CHECK (true),
2282
  /*CONSTRAINT "FK_unknowns" FOREIGN KEY ("unkid") REFERENCES "unknowns" ("unkid") ON DELETE CASCADE*/CHECK (true)
2283
);
2284
/*!40101 SET character_set_client = @saved_cs_client */;
2285
2286
--
2287
-- Table structure for table "unknowns"
2288
--
2289
2290
DROP TABLE IF EXISTS "unknowns";
2291
/*!40101 SET @saved_cs_client     = @@character_set_client */;
2292
/*!40101 SET character_set_client = utf8 */;
2293
CREATE TABLE "unknowns" (
2294
  "unkid" integer NOT NULL,
2295
  "tid" integer DEFAULT NULL,
2296
  "photographer" varchar(100) DEFAULT NULL,
2297
  "owner" varchar(100) DEFAULT NULL,
2298
  "locality" varchar(250) DEFAULT NULL,
2299
  "latdecimal" double precision DEFAULT NULL,
2300
  "longdecimal" double precision DEFAULT NULL,
2301
  "notes" varchar(250) DEFAULT NULL,
2302
  "username" varchar(45) NOT NULL,
2303
  "idstatus" varchar(45) NOT NULL DEFAULT 'ID pending',
2304 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2305 7545 aaronmk
  PRIMARY KEY ("unkid"),
2306
  /*KEY "FK_unknowns_username" ("username")*/CHECK (true),
2307
  /*KEY "FK_unknowns_tid" ("tid")*/CHECK (true),
2308
  /*CONSTRAINT "FK_unknowns_tid" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID")*/CHECK (true),
2309
  /*CONSTRAINT "FK_unknowns_username" FOREIGN KEY ("username") REFERENCES "userlogin" ("username")*/CHECK (true)
2310
);
2311
/*!40101 SET character_set_client = @saved_cs_client */;
2312
2313
--
2314
-- Table structure for table "uploadimagetemp"
2315
--
2316
2317
DROP TABLE IF EXISTS "uploadimagetemp";
2318
/*!40101 SET @saved_cs_client     = @@character_set_client */;
2319
/*!40101 SET character_set_client = utf8 */;
2320
CREATE TABLE "uploadimagetemp" (
2321
  "tid" integer DEFAULT NULL,
2322
  "url" varchar(255) NOT NULL,
2323
  "thumbnailurl" varchar(255) DEFAULT NULL,
2324
  "originalurl" varchar(255) DEFAULT NULL,
2325
  "photographer" varchar(100) DEFAULT NULL,
2326
  "photographeruid" integer DEFAULT NULL,
2327
  "imagetype" varchar(50) DEFAULT NULL,
2328
  "caption" varchar(100) DEFAULT NULL,
2329
  "owner" varchar(100) DEFAULT NULL,
2330
  "occid" integer DEFAULT NULL,
2331
  "collid" integer DEFAULT NULL,
2332
  "dbpk" varchar(45) DEFAULT NULL,
2333
  "specimengui" varchar(45) DEFAULT NULL,
2334
  "notes" varchar(255) DEFAULT NULL,
2335
  "username" varchar(45) DEFAULT NULL,
2336
  "sortsequence" integer DEFAULT NULL,
2337 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP
2338 7545 aaronmk
);
2339
/*!40101 SET character_set_client = @saved_cs_client */;
2340
2341
--
2342
-- Table structure for table "uploadspecmap"
2343
--
2344
2345
DROP TABLE IF EXISTS "uploadspecmap";
2346
/*!40101 SET @saved_cs_client     = @@character_set_client */;
2347
/*!40101 SET character_set_client = utf8 */;
2348
CREATE TABLE "uploadspecmap" (
2349
  "usmid" integer NOT NULL,
2350
  "uspid" integer NOT NULL,
2351
  "sourcefield" varchar(45) NOT NULL,
2352 10444 aaronmk
  "symbdatatype" varchar(45) NOT NULL DEFAULT 'string' /*COMMENT 'string, numeric, text/*datetime*/'*/,
2353 7545 aaronmk
  "symbspecfield" varchar(45) NOT NULL,
2354 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2355 7545 aaronmk
  PRIMARY KEY ("usmid"),
2356
  /*CONSTRAINT "Index_unique" */UNIQUE ("uspid","symbspecfield"),
2357
  /*CONSTRAINT "FK_uploadspecmap_usp" FOREIGN KEY ("uspid") REFERENCES "uploadspecparameters" ("uspid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
2358
);
2359
/*!40101 SET character_set_client = @saved_cs_client */;
2360
2361
--
2362
-- Table structure for table "uploadspecparameters"
2363
--
2364
2365
DROP TABLE IF EXISTS "uploadspecparameters";
2366
/*!40101 SET @saved_cs_client     = @@character_set_client */;
2367
/*!40101 SET character_set_client = utf8 */;
2368
CREATE TABLE "uploadspecparameters" (
2369
  "uspid" integer NOT NULL,
2370
  "CollID" integer NOT NULL,
2371 10444 aaronmk
  "UploadType" integer NOT NULL DEFAULT '1' /*COMMENT '1 = Direct; 2 = DiGIR; 3 = File'*/,
2372 7545 aaronmk
  "title" varchar(45) NOT NULL,
2373 10444 aaronmk
  "Platform" varchar(45) DEFAULT '1' /*COMMENT '1 = MySQL; 2 = MSSQL; 3 = ORACLE; 11 = MS Access; 12 = FileMaker'*/,
2374 7545 aaronmk
  "server" varchar(150) DEFAULT NULL,
2375
  "port" integer DEFAULT NULL,
2376
  "driver" varchar(45) DEFAULT NULL,
2377
  "DigirCode" varchar(45) DEFAULT NULL,
2378
  "DigirPath" varchar(150) DEFAULT NULL,
2379
  "DigirPKField" varchar(45) DEFAULT NULL,
2380
  "Username" varchar(45) DEFAULT NULL,
2381
  "Password" varchar(45) DEFAULT NULL,
2382
  "SchemaName" varchar(150) DEFAULT NULL,
2383
  "QueryStr" varchar(2000) DEFAULT NULL,
2384
  "cleanupsp" varchar(45) DEFAULT NULL,
2385
  "dlmisvalid" integer DEFAULT '0',
2386 10443 aaronmk
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2387 7545 aaronmk
  PRIMARY KEY ("uspid"),
2388
  /*KEY "FK_uploadspecparameters_coll" ("CollID")*/CHECK (true),
2389
  /*CONSTRAINT "FK_uploadspecparameters_coll" FOREIGN KEY ("CollID") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
2390
);
2391
/*!40101 SET character_set_client = @saved_cs_client */;
2392
2393
--
2394
-- Table structure for table "uploadspectemp"
2395
--
2396
2397
DROP TABLE IF EXISTS "uploadspectemp";
2398
/*!40101 SET @saved_cs_client     = @@character_set_client */;
2399
/*!40101 SET character_set_client = utf8 */;
2400
CREATE TABLE "uploadspectemp" (
2401
  "collid" integer NOT NULL,
2402
  "dbpk" varchar(45) DEFAULT NULL,
2403
  "occid" integer DEFAULT NULL,
2404 10444 aaronmk
  "basisOfRecord" varchar(32) DEFAULT 'PreservedSpecimen' /*COMMENT 'PreservedSpecimen, LivingSpecimen, HumanObservation'*/,
2405
  "occurrenceID" varchar(255) DEFAULT NULL /*COMMENT 'UniqueGlobalIdentifier'*/,
2406 7545 aaronmk
  "catalogNumber" varchar(32) DEFAULT NULL,
2407
  "otherCatalogNumbers" varchar(255) DEFAULT NULL,
2408
  "ownerInstitutionCode" varchar(32) DEFAULT NULL,
2409
  "institutionID" varchar(255) DEFAULT NULL,
2410
  "collectionID" varchar(255) DEFAULT NULL,
2411
  "institutionCode" varchar(64) DEFAULT NULL,
2412
  "collectionCode" varchar(64) DEFAULT NULL,
2413
  "datasetID" varchar(255) DEFAULT NULL,
2414
  "family" varchar(255) DEFAULT NULL,
2415
  "scientificName" varchar(255) DEFAULT NULL,
2416
  "sciname" varchar(255) DEFAULT NULL,
2417
  "tidinterpreted" integer DEFAULT NULL,
2418
  "genus" varchar(255) DEFAULT NULL,
2419
  "specificEpithet" varchar(255) DEFAULT NULL,
2420
  "taxonRank" varchar(32) DEFAULT NULL,
2421
  "infraspecificEpithet" varchar(255) DEFAULT NULL,
2422
  "scientificNameAuthorship" varchar(255) DEFAULT NULL,
2423
  "taxonRemarks" text,
2424
  "identifiedBy" varchar(255) DEFAULT NULL,
2425
  "dateIdentified" varchar(45) DEFAULT NULL,
2426
  "identificationReferences" text,
2427
  "identificationRemarks" text,
2428 10444 aaronmk
  "identificationQualifier" varchar(255) DEFAULT NULL /*COMMENT 'cf, aff, etc'*/,
2429 7545 aaronmk
  "typeStatus" varchar(255) DEFAULT NULL,
2430 10444 aaronmk
  "recordedBy" varchar(255) DEFAULT NULL /*COMMENT 'Collector(s)'*/,
2431
  "recordNumber" varchar(32) DEFAULT NULL /*COMMENT 'Collector Number'*/,
2432
  "CollectorFamilyName" varchar(255) DEFAULT NULL /*COMMENT 'not DwC'*/,
2433
  "CollectorInitials" varchar(255) DEFAULT NULL /*COMMENT 'not DwC'*/,
2434
  "associatedCollectors" varchar(255) DEFAULT NULL /*COMMENT 'not DwC'*/,
2435 10443 aaronmk
  "eventDate" text/*date*/ DEFAULT NULL,
2436 7545 aaronmk
  "year" integer DEFAULT NULL,
2437
  "month" integer DEFAULT NULL,
2438
  "day" integer DEFAULT NULL,
2439
  "startDayOfYear" integer DEFAULT NULL,
2440
  "endDayOfYear" integer DEFAULT NULL,
2441 10443 aaronmk
  "LatestDateCollected" text/*date*/ DEFAULT NULL,
2442 7545 aaronmk
  "verbatimEventDate" varchar(255) DEFAULT NULL,
2443 10444 aaronmk
  "habitat" text /*COMMENT 'Habitat, substrait, etc'*/,
2444 7545 aaronmk
  "substrate" varchar(500) DEFAULT NULL,
2445
  "fieldNotes" text,
2446
  "fieldnumber" varchar(45) DEFAULT NULL,
2447 10444 aaronmk
  "occurrenceRemarks" text /*COMMENT 'General Notes'*/,
2448 7545 aaronmk
  "informationWithheld" varchar(250) DEFAULT NULL,
2449
  "dataGeneralizations" varchar(250) DEFAULT NULL,
2450
  "associatedOccurrences" text,
2451 10444 aaronmk
  "associatedTaxa" text /*COMMENT 'Associated Species'*/,
2452
  "dynamicProperties" text /*COMMENT 'Plant Description?'*/,
2453 7545 aaronmk
  "verbatimAttributes" text,
2454
  "attributes" text,
2455 10444 aaronmk
  "reproductiveCondition" varchar(255) DEFAULT NULL /*COMMENT 'Phenology: flowers, fruit, sterile'*/,
2456
  "cultivationStatus" integer DEFAULT NULL /*COMMENT '0 = wild, 1 = cultivated'*/,
2457
  "establishmentMeans" varchar(32) DEFAULT NULL /*COMMENT 'cultivated, invasive, escaped from captivity, wild, native'*/,
2458 7545 aaronmk
  "lifeStage" varchar(45) DEFAULT NULL,
2459
  "sex" varchar(45) DEFAULT NULL,
2460
  "individualCount" varchar(45) DEFAULT NULL,
2461
  "samplingProtocol" varchar(100) DEFAULT NULL,
2462
  "preparations" varchar(100) DEFAULT NULL,
2463
  "country" varchar(64) DEFAULT NULL,
2464
  "stateProvince" varchar(255) DEFAULT NULL,
2465
  "county" varchar(255) DEFAULT NULL,
2466
  "municipality" varchar(255) DEFAULT NULL,
2467
  "locality" text,
2468 10444 aaronmk
  "localitySecurity" integer DEFAULT '0' /*COMMENT '0 = display locality, 1 = hide locality'*/,
2469 7545 aaronmk
  "localitySecurityReason" varchar(100) DEFAULT NULL,
2470
  "decimalLatitude" double precision DEFAULT NULL,
2471
  "decimalLongitude" double precision DEFAULT NULL,
2472
  "geodeticDatum" varchar(255) DEFAULT NULL,
2473
  "coordinateUncertaintyInMeters" integer DEFAULT NULL,
2474
  "footprintWKT" text,
2475
  "coordinatePrecision" decimal(9,7) DEFAULT NULL,
2476
  "locationRemarks" text,
2477
  "verbatimCoordinates" varchar(255) DEFAULT NULL,
2478
  "verbatimCoordinateSystem" varchar(255) DEFAULT NULL,
2479
  "latDeg" integer DEFAULT NULL,
2480
  "latMin" double precision DEFAULT NULL,
2481
  "latSec" double precision DEFAULT NULL,
2482
  "latNS" varchar(3) DEFAULT NULL,
2483
  "lngDeg" integer DEFAULT NULL,
2484
  "lngMin" double precision DEFAULT NULL,
2485
  "lngSec" double precision DEFAULT NULL,
2486
  "lngEW" varchar(3) DEFAULT NULL,
2487
  "UtmNorthing" varchar(45) DEFAULT NULL,
2488
  "UtmEasting" varchar(45) DEFAULT NULL,
2489
  "UtmZoning" varchar(45) DEFAULT NULL,
2490
  "georeferencedBy" varchar(255) DEFAULT NULL,
2491
  "georeferenceProtocol" varchar(255) DEFAULT NULL,
2492
  "georeferenceSources" varchar(255) DEFAULT NULL,
2493
  "georeferenceVerificationStatus" varchar(32) DEFAULT NULL,
2494
  "georeferenceRemarks" varchar(255) DEFAULT NULL,
2495
  "minimumElevationInMeters" integer DEFAULT NULL,
2496
  "maximumElevationInMeters" integer DEFAULT NULL,
2497
  "verbatimElevation" varchar(255) DEFAULT NULL,
2498
  "previousIdentifications" text,
2499 10444 aaronmk
  "disposition" varchar(32) DEFAULT NULL /*COMMENT 'Dups to'*/,
2500 7545 aaronmk
  "genericcolumn1" varchar(100) DEFAULT NULL,
2501
  "genericcolumn2" varchar(100) DEFAULT NULL,
2502 10444 aaronmk
  "modified" text/*datetime*/ DEFAULT NULL /*COMMENT 'DateLastModified'*/,
2503 7545 aaronmk
  "language" varchar(2) DEFAULT NULL,
2504
  "recordEnteredBy" varchar(250) DEFAULT NULL,
2505
  "duplicateQuantity" integer DEFAULT NULL,
2506
  "labelProject" varchar(45) DEFAULT NULL,
2507
  "tempfield01" text,
2508
  "tempfield02" text,
2509
  "tempfield03" text,
2510
  "tempfield04" text,
2511
  "tempfield05" text,
2512
  "tempfield06" text,
2513
  "tempfield07" text,
2514
  "tempfield08" text,
2515
  "tempfield09" text,
2516
  "tempfield10" text,
2517
  "tempfield11" text,
2518
  "tempfield12" text,
2519
  "tempfield13" text,
2520
  "tempfield14" text,
2521
  "tempfield15" text,
2522 10443 aaronmk
  "initialTimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP,
2523 7545 aaronmk
  /*KEY "FK_uploadspectemp_coll" ("collid")*/CHECK (true),
2524
  /*KEY "Index_uploadspectemp_occid" ("occid")*/CHECK (true),
2525
  /*KEY "Index_uploadspectemp_dbpk" ("dbpk")*/CHECK (true),
2526
  /*CONSTRAINT "FK_uploadspectemp_coll" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
2527
);
2528
/*!40101 SET character_set_client = @saved_cs_client */;
2529
2530
--
2531
-- Table structure for table "uploadtaxa"
2532
--
2533
2534
DROP TABLE IF EXISTS "uploadtaxa";
2535
/*!40101 SET @saved_cs_client     = @@character_set_client */;
2536
/*!40101 SET character_set_client = utf8 */;
2537
CREATE TABLE "uploadtaxa" (
2538
  "TID" integer DEFAULT NULL,
2539
  "SourceId" integer DEFAULT NULL,
2540
  "KingdomID" integer DEFAULT '3',
2541
  "UpperTaxonomy" varchar(50) DEFAULT NULL,
2542
  "Family" varchar(50) DEFAULT NULL,
2543
  "RankId" integer DEFAULT NULL,
2544
  "scinameinput" varchar(250) NOT NULL,
2545
  "SciName" varchar(250) DEFAULT NULL,
2546
  "UnitInd1" varchar(1) DEFAULT NULL,
2547
  "UnitName1" varchar(50) DEFAULT NULL,
2548
  "UnitInd2" varchar(1) DEFAULT NULL,
2549
  "UnitName2" varchar(50) DEFAULT NULL,
2550
  "UnitInd3" varchar(7) DEFAULT NULL,
2551
  "UnitName3" varchar(35) DEFAULT NULL,
2552
  "Author" varchar(100) DEFAULT NULL,
2553 10444 aaronmk
  "Acceptance" integer DEFAULT '1' /*COMMENT '0 = not accepted; 1 = accepted'*/,
2554 7545 aaronmk
  "TidAccepted" integer DEFAULT NULL,
2555
  "AcceptedStr" varchar(250) DEFAULT NULL,
2556
  "SourceAcceptedId" integer DEFAULT NULL,
2557
  "UnacceptabilityReason" varchar(24) DEFAULT NULL,
2558
  "ParentTid" integer DEFAULT NULL,
2559
  "ParentStr" varchar(250) DEFAULT NULL,
2560
  "SourceParentId" integer DEFAULT NULL,
2561 10444 aaronmk
  "SecurityStatus" integer NOT NULL DEFAULT '0' /*COMMENT '0 = no security; 1 = hidden locality'*/,
2562 7545 aaronmk
  "Source" varchar(250) DEFAULT NULL,
2563
  "Notes" varchar(250) DEFAULT NULL,
2564
  "vernacular" varchar(80) DEFAULT NULL,
2565
  "vernlang" varchar(15) DEFAULT NULL,
2566
  "Hybrid" varchar(50) DEFAULT NULL,
2567 10443 aaronmk
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2568 7545 aaronmk
  PRIMARY KEY ("scinameinput"),
2569
  /*CONSTRAINT "sciname_index" */UNIQUE ("SciName"),
2570
  /*KEY "sourceID_index" ("SourceId")*/CHECK (true),
2571
  /*KEY "sourceAcceptedId_index" ("SourceAcceptedId")*/CHECK (true)
2572
);
2573
/*!40101 SET character_set_client = @saved_cs_client */;
2574
2575
--
2576
-- Table structure for table "userlogin"
2577
--
2578
2579
DROP TABLE IF EXISTS "userlogin";
2580
/*!40101 SET @saved_cs_client     = @@character_set_client */;
2581
/*!40101 SET character_set_client = utf8 */;
2582
CREATE TABLE "userlogin" (
2583
  "uid" integer NOT NULL,
2584
  "username" varchar(45) NOT NULL,
2585
  "password" varchar(45) NOT NULL,
2586
  "alias" varchar(45) DEFAULT NULL,
2587 10443 aaronmk
  "lastlogindate" text/*datetime*/ DEFAULT NULL,
2588
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2589 7545 aaronmk
  PRIMARY KEY ("username"),
2590
  /*CONSTRAINT "Index_userlogin_unique" */UNIQUE ("alias"),
2591
  /*KEY "FK_login_user" ("uid")*/CHECK (true),
2592
  /*CONSTRAINT "userlogin_ibfk_1" FOREIGN KEY ("uid") REFERENCES "users" ("uid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
2593
);
2594
/*!40101 SET character_set_client = @saved_cs_client */;
2595
2596
--
2597
-- Table structure for table "userpermissions"
2598
--
2599
2600
DROP TABLE IF EXISTS "userpermissions";
2601
/*!40101 SET @saved_cs_client     = @@character_set_client */;
2602
/*!40101 SET character_set_client = utf8 */;
2603
CREATE TABLE "userpermissions" (
2604
  "uid" integer NOT NULL,
2605 10444 aaronmk
  "pname" varchar(45) NOT NULL /*COMMENT 'SuperAdmin, TaxonProfile, IdentKey, RareSpecies, coll-1, cl-1, proj-1'*/,
2606 7545 aaronmk
  "assignedby" varchar(45) DEFAULT NULL,
2607 10443 aaronmk
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2608 7545 aaronmk
  PRIMARY KEY ("uid","pname"),
2609
  /*CONSTRAINT "userpermissions_ibfk_1" FOREIGN KEY ("uid") REFERENCES "users" ("uid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true)
2610
);
2611
/*!40101 SET character_set_client = @saved_cs_client */;
2612
2613
--
2614
-- Table structure for table "users"
2615
--
2616
2617
DROP TABLE IF EXISTS "users";
2618
/*!40101 SET @saved_cs_client     = @@character_set_client */;
2619
/*!40101 SET character_set_client = utf8 */;
2620
CREATE TABLE "users" (
2621
  "uid" integer NOT NULL,
2622
  "firstname" varchar(45) DEFAULT NULL,
2623
  "lastname" varchar(45) NOT NULL,
2624
  "title" varchar(150) DEFAULT NULL,
2625
  "institution" varchar(200) DEFAULT NULL,
2626
  "department" varchar(200) DEFAULT NULL,
2627
  "address" varchar(255) DEFAULT NULL,
2628
  "city" varchar(100) DEFAULT NULL,
2629
  "state" varchar(50) NOT NULL,
2630
  "zip" varchar(15) DEFAULT NULL,
2631
  "country" varchar(50) NOT NULL,
2632
  "phone" varchar(45) DEFAULT NULL,
2633
  "email" varchar(100) NOT NULL,
2634
  "RegionOfInterest" varchar(45) DEFAULT NULL,
2635
  "url" varchar(400) DEFAULT NULL,
2636
  "Biography" varchar(1500) DEFAULT NULL,
2637
  "notes" varchar(255) DEFAULT NULL,
2638
  "ispublic" integer NOT NULL DEFAULT '0',
2639
  "defaultrights" varchar(250) DEFAULT NULL,
2640
  "rightsholder" varchar(250) DEFAULT NULL,
2641
  "validated" varchar(45) NOT NULL DEFAULT '0',
2642
  "usergroups" varchar(100) DEFAULT NULL,
2643 10443 aaronmk
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2644 7545 aaronmk
  PRIMARY KEY ("uid"),
2645
  /*CONSTRAINT "Index_email" */UNIQUE ("email","lastname")
2646
);
2647
/*!40101 SET character_set_client = @saved_cs_client */;
2648
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
2649
2650
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
2651
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
2652
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
2653
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
2654
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
2655
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
2656
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
2657
2658
-- Dump completed on 2013-02-14  3:07:06