/inputs/ARIZ/MySQL.schema.sql - Annotate - BIEN 3 - NCEAS Projects

7545

aaronmk

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

Project

General

Profile

root/inputs/ARIZ/MySQL.schema.sql @ 8606