Project

General

Profile

1
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
  "date_collected" text/*date*/ DEFAULT NULL,
47
  "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
  "datelastmodified" text/*datetime*/ NOT NULL,
86
  "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
  "date_entered" text/*timestamp*/ NOT NULL DEFAULT '0000-00-00 00:00:00',
96
  "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
  "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
  "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
  "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
  "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
);
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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
132
  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
  "DateLastModified" text/*datetime*/ DEFAULT NULL,
168
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
169
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
191
  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
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
209
  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
  "Nativity" varchar(50) DEFAULT NULL /*COMMENT 'native, introducted'*/,
234
  "Endemic" varchar(25) DEFAULT NULL,
235
  "internalnotes" varchar(250) DEFAULT NULL,
236
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
237
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
260
  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
  "expiration" text/*datetime*/ NOT NULL,
283
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
284
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
299
  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
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
326
  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
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
347
  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
  "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP,
369
  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
  "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP,
393
  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
  "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP,
417
  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
  "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP,
441
  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
  "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP,
465
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
489
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
506
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
521
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
543
  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
  "AnnDate" text/*datetime*/ NOT NULL DEFAULT '0000-00-00 00:00:00',
560
  "Annotator" varchar(100) DEFAULT NULL,
561
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
562
  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
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
596
  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
  "IntialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
630
  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
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
650
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
676
  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
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
694
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
716
  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
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
733
  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
  "timestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
757
  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
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
783
  "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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
806
  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
  "intialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
825
  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
  "Frequency" integer NOT NULL DEFAULT '5' /*COMMENT 'Frequency of occurrence; 1 = rare... 5 = common'*/,
846
  "Inherited" varchar(50) DEFAULT NULL,
847
  "Source" varchar(100) DEFAULT NULL,
848
  "Seq" integer DEFAULT NULL,
849
  "Notes" text,
850
  "DateEntered" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
851
  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
  "InitialTimeStamp" text/*datetime*/ DEFAULT NULL,
877
  "DeletedBy" varchar(100) NOT NULL,
878
  "DeletedTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
879
  "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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
898
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
915
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
936
  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
  "identifier" varchar(250) DEFAULT NULL /*COMMENT 'e.g. GUID'*/,
957
  "resourceurl" varchar(250) DEFAULT NULL,
958
  "notes" varchar(250) DEFAULT NULL,
959
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
960
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
975
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
991
  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
  "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
  "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
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1030
  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
  "uploaddate" text/*datetime*/ DEFAULT NULL,
1050
  "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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1057
  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
  "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP,
1079
  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
  "InitialTimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1109
  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
  "editorlevel" integer NOT NULL DEFAULT '0' /*COMMENT '0=public, 1=public limited, 2=private'*/,
1128
  "notes" varchar(250) DEFAULT NULL,
1129
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1130
  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
  "reviewstatus" integer NOT NULL DEFAULT '0' /*COMMENT '0=open,5=pending review, 10=closed'*/,
1149
  "uidprocessor" integer DEFAULT NULL,
1150
  "points" integer DEFAULT NULL /*COMMENT '0=fail, 1=minor edits, 2=no edits <default>, 3=excelled'*/,
1151
  "notes" varchar(250) DEFAULT NULL,
1152
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1153
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1175
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1195
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1223
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1243
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1263
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1285
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1311
  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
  "initialTimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1336
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1352
  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
  "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
  "uid" integer NOT NULL,
1372
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1373
  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
  "dateSent" text/*date*/ DEFAULT NULL,
1396
  "dateReceived" text/*date*/ DEFAULT NULL,
1397
  "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
  "initialTimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1410
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1450
  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
  "dateSent" text/*date*/ DEFAULT NULL,
1471
  "dateSentReturn" text/*date*/ DEFAULT NULL,
1472
  "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
  "dateDue" text/*date*/ DEFAULT NULL,
1479
  "dateReceivedOwn" text/*date*/ DEFAULT NULL,
1480
  "dateReceivedBorr" text/*date*/ DEFAULT NULL,
1481
  "dateClosed" text/*date*/ DEFAULT NULL,
1482
  "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
  "initialTimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1495
  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
  "returndate" text/*date*/ DEFAULT NULL,
1518
  "notes" varchar(255) DEFAULT NULL,
1519
  "initialTimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1520
  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
  "basisOfRecord" varchar(32) DEFAULT 'PreservedSpecimen' /*COMMENT 'PreservedSpecimen, LivingSpecimen, HumanObservation'*/,
1540
  "occurrenceID" varchar(255) DEFAULT NULL /*COMMENT 'UniqueGlobalIdentifier'*/,
1541
  "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
  "identificationQualifier" varchar(255) DEFAULT NULL /*COMMENT 'cf, aff, etc'*/,
1564
  "typeStatus" varchar(255) DEFAULT NULL,
1565
  "recordedBy" varchar(255) DEFAULT NULL /*COMMENT 'Collector(s)'*/,
1566
  "recordNumber" varchar(45) DEFAULT NULL /*COMMENT 'Collector Number'*/,
1567
  "recordedById" integer DEFAULT NULL,
1568
  "associatedCollectors" varchar(255) DEFAULT NULL /*COMMENT 'not DwC'*/,
1569
  "eventDate" text/*date*/ DEFAULT NULL,
1570
  "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
  "habitat" text /*COMMENT 'Habitat, substrait, etc'*/,
1577
  "substrate" varchar(500) DEFAULT NULL,
1578
  "fieldNotes" text,
1579
  "fieldnumber" varchar(45) DEFAULT NULL,
1580
  "occurrenceRemarks" text /*COMMENT 'General Notes'*/,
1581
  "informationWithheld" varchar(250) DEFAULT NULL,
1582
  "dataGeneralizations" varchar(250) DEFAULT NULL,
1583
  "associatedOccurrences" text,
1584
  "associatedTaxa" text /*COMMENT 'Associated Species'*/,
1585
  "dynamicProperties" text,
1586
  "verbatimAttributes" text,
1587
  "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
  "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
  "localitySecurity" integer DEFAULT '0' /*COMMENT '0 = no security; 1 = hidden locality'*/,
1602
  "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
  "modified" text/*datetime*/ DEFAULT NULL /*COMMENT 'DateLastModified'*/,
1625
  "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
  "dateLastModified" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1634
  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
  "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP,
1678
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1698
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1716
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1741
  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
  "initialTimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1772
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1796
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1819
  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
  "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP,
1840
  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
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1867
  "Status" varchar(50) DEFAULT NULL,
1868
  "Source" varchar(250) DEFAULT NULL,
1869
  "Notes" varchar(250) DEFAULT NULL,
1870
  "Hybrid" varchar(50) DEFAULT NULL,
1871
  "fnaprikey" integer DEFAULT NULL /*COMMENT 'Primary key for FNA project'*/,
1872
  "UsdaSymbol" varchar(50) DEFAULT NULL,
1873
  "SecurityStatus" integer NOT NULL DEFAULT '0' /*COMMENT '0 = no security; 1 = hidden locality'*/,
1874
  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
  "displaylevel" integer NOT NULL DEFAULT '1' /*COMMENT '1 = short descr, 2 = intermediate descr'*/,
1898
  "uid" integer NOT NULL,
1899
  "notes" varchar(250) DEFAULT NULL,
1900
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1901
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1923
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1941
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
1969
  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
  "maptype" varchar(45) NOT NULL /*COMMENT 'custom; google dynamic'*/,
1986
  "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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2000
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2018
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2039
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2067
  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
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2089
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2112
  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
  "taxauthid" integer NOT NULL /*COMMENT 'taxon authority id'*/,
2127
  "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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2135
  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
  "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
  "DateColl" text/*date*/ DEFAULT NULL,
2171
  "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
  "DateModified" text/*timestamp*/ NOT NULL DEFAULT '0000-00-00 00:00:00',
2183
  "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
  "date_created" text/*timestamp*/ NOT NULL DEFAULT '0000-00-00 00:00:00',
2218
  "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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2261
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2280
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2305
  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
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP
2338
);
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
  "symbdatatype" varchar(45) NOT NULL DEFAULT 'string' /*COMMENT 'string, numeric, text/*datetime*/'*/,
2353
  "symbspecfield" varchar(45) NOT NULL,
2354
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2355
  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
  "UploadType" integer NOT NULL DEFAULT '1' /*COMMENT '1 = Direct; 2 = DiGIR; 3 = File'*/,
2372
  "title" varchar(45) NOT NULL,
2373
  "Platform" varchar(45) DEFAULT '1' /*COMMENT '1 = MySQL; 2 = MSSQL; 3 = ORACLE; 11 = MS Access; 12 = FileMaker'*/,
2374
  "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
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2387
  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
  "basisOfRecord" varchar(32) DEFAULT 'PreservedSpecimen' /*COMMENT 'PreservedSpecimen, LivingSpecimen, HumanObservation'*/,
2405
  "occurrenceID" varchar(255) DEFAULT NULL /*COMMENT 'UniqueGlobalIdentifier'*/,
2406
  "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
  "identificationQualifier" varchar(255) DEFAULT NULL /*COMMENT 'cf, aff, etc'*/,
2429
  "typeStatus" varchar(255) DEFAULT NULL,
2430
  "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
  "eventDate" text/*date*/ DEFAULT NULL,
2436
  "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
  "LatestDateCollected" text/*date*/ DEFAULT NULL,
2442
  "verbatimEventDate" varchar(255) DEFAULT NULL,
2443
  "habitat" text /*COMMENT 'Habitat, substrait, etc'*/,
2444
  "substrate" varchar(500) DEFAULT NULL,
2445
  "fieldNotes" text,
2446
  "fieldnumber" varchar(45) DEFAULT NULL,
2447
  "occurrenceRemarks" text /*COMMENT 'General Notes'*/,
2448
  "informationWithheld" varchar(250) DEFAULT NULL,
2449
  "dataGeneralizations" varchar(250) DEFAULT NULL,
2450
  "associatedOccurrences" text,
2451
  "associatedTaxa" text /*COMMENT 'Associated Species'*/,
2452
  "dynamicProperties" text /*COMMENT 'Plant Description?'*/,
2453
  "verbatimAttributes" text,
2454
  "attributes" text,
2455
  "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
  "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
  "localitySecurity" integer DEFAULT '0' /*COMMENT '0 = display locality, 1 = hide locality'*/,
2469
  "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
  "disposition" varchar(32) DEFAULT NULL /*COMMENT 'Dups to'*/,
2500
  "genericcolumn1" varchar(100) DEFAULT NULL,
2501
  "genericcolumn2" varchar(100) DEFAULT NULL,
2502
  "modified" text/*datetime*/ DEFAULT NULL /*COMMENT 'DateLastModified'*/,
2503
  "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
  "initialTimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP,
2523
  /*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
  "Acceptance" integer DEFAULT '1' /*COMMENT '0 = not accepted; 1 = accepted'*/,
2554
  "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
  "SecurityStatus" integer NOT NULL DEFAULT '0' /*COMMENT '0 = no security; 1 = hidden locality'*/,
2562
  "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
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2568
  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
  "lastlogindate" text/*datetime*/ DEFAULT NULL,
2588
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2589
  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
  "pname" varchar(45) NOT NULL /*COMMENT 'SuperAdmin, TaxonProfile, IdentKey, RareSpecies, coll-1, cl-1, proj-1'*/,
2606
  "assignedby" varchar(45) DEFAULT NULL,
2607
  "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2608
  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
  "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP,
2644
  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
(4-4/9)