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