-- MySQL dump 10.13 Distrib 5.1.66, for debian-linux-gnu (x86_64) -- -- Host: localhost Database: ua_herbarium -- ------------------------------------------------------ -- Server version 5.1.66-0+squeeze1 /*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES latin1 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO,POSTGRESQL' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */; -- -- Table structure for table "NPS_records_b4_20120628" -- DROP TABLE IF EXISTS "NPS_records_b4_20120628"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "NPS_records_b4_20120628" ( "dbsn" int(10) unsigned NOT NULL DEFAULT '0', "accession_number" int(10) unsigned DEFAULT NULL, "taxa_id" int(10) unsigned DEFAULT NULL, "cf" varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "determiner_annotator" varchar(100) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "initial_taxonomy" varchar(200) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "specimen_notes" varchar(250) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "plant_description" varchar(1000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "phenology" varchar(50) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "chromosome_number" varchar(50) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "habitat" varchar(600) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "assoc_species" varchar(1300) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "first_collector" varchar(50) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "collnumprefix" varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "collnumber" int(10) unsigned DEFAULT NULL, "collnumsuffix" varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "collnumber_full" varchar(100) DEFAULT NULL, "other_collectors" varchar(200) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "date_collected" date DEFAULT NULL, "month_collected" varchar(15) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "day_collected" int(10) unsigned DEFAULT NULL, "year_collected" int(10) unsigned DEFAULT NULL, "country" varchar(50) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "state_province" varchar(50) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "county_parish" varchar(50) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "national_forest" varchar(50) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "locality" varchar(1000) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "latdeg" int(10) unsigned DEFAULT NULL, "latmin" decimal(12,8) DEFAULT NULL, "latsec" decimal(12,8) DEFAULT NULL, "latns" varchar(2) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "lat_decimal" decimal(12,8) DEFAULT NULL, "longdeg" int(10) unsigned DEFAULT NULL, "longmin" decimal(12,8) DEFAULT NULL, "longsec" decimal(12,8) DEFAULT NULL, "longew" varchar(2) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "long_decimal" decimal(12,8) DEFAULT NULL, "utm_zoning" varchar(5) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "utm_easting" int(10) DEFAULT NULL, "utm_northing" int(10) DEFAULT NULL, "datum" varchar(20) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "coordinates_est" varchar(100) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "bbounding" varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "township" varchar(50) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "range" varchar(50) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "section" varchar(50) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "sec_details" varchar(50) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "elevation_m" int(10) DEFAULT NULL, "elev_max_m" int(10) DEFAULT NULL, "elevation_ft" int(10) DEFAULT NULL, "elev_max_ft" int(10) DEFAULT NULL, "elev_est" varchar(20) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "cultivated" varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "label_quantity" int(10) unsigned DEFAULT NULL, "project" varchar(50) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "dups_to" varchar(180) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "herb_acro" varchar(50) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "datelastmodified" datetime NOT NULL, "entered_by" varchar(60) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "download_source" varchar(50) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "old_dbsn" int(10) unsigned DEFAULT NULL, "verified" varchar(50) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "coord_err" int(10) DEFAULT NULL, "coord_confidence" int(10) DEFAULT NULL, "land_ownership" varchar(100) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "coord_source" varchar(100) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "georeferenceverificationstatus" varchar(150) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "date_entered" timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', "is_duplicate" int(1) unsigned DEFAULT NULL COMMENT '=1 if possible duplicate record; otherwise 0 or null', "deleted" int(1) unsigned NOT NULL DEFAULT '0' COMMENT '=1 if deleted; otherwise 0', "deleted_reason" varchar(250) DEFAULT NULL COMMENT 'Reason for flagging for deletion', "determination_date" date DEFAULT NULL COMMENT 'Keep this field DATE not DATETIME', "det_day" int(2) unsigned DEFAULT NULL, "det_mo" int(2) unsigned DEFAULT NULL, "det_yr" int(4) unsigned DEFAULT NULL, "deaccessioned" int(1) unsigned NOT NULL DEFAULT '0', "dupes" int(3) unsigned DEFAULT '1' COMMENT 'Total duplicates per specimen', "sheets" int(3) unsigned DEFAULT '1' COMMENT 'Total sheets per specimen', "collnum_numeric" double DEFAULT NULL COMMENT 'Artificial decimal value based on collnumprefix, collnumber & collnumsuffix; for natural sorting', "needs_label" int(1) unsigned DEFAULT '0' COMMENT '1=Yes, needs label; 0=No, already labelled', "is_arboretum" int(1) unsigned DEFAULT '0', "arboretum_id" int(10) unsigned DEFAULT NULL, "notes_display" int(1) unsigned DEFAULT '0', "elev_est_display" int(1) unsigned DEFAULT '0', "security" int(1) unsigned NOT NULL DEFAULT '1' COMMENT '1=no security, 2=hide locality info', "temp_dbsn" int(10) unsigned DEFAULT NULL COMMENT 'used during bulk record transfer to update FKs; afterwards reset to NULL' ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "chotomouskey" -- DROP TABLE IF EXISTS "chotomouskey"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "chotomouskey" ( "stmtid" int(10) unsigned NOT NULL, "statement" varchar(300) NOT NULL, "nodeid" int(10) unsigned NOT NULL, "parentid" int(10) unsigned NOT NULL, "tid" int(10) unsigned DEFAULT NULL, "notes" varchar(250) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("stmtid"), KEY "FK_chotomouskey_taxa" ("tid"), CONSTRAINT "FK_chotomouskey_taxa" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "fmchecklists" -- DROP TABLE IF EXISTS "fmchecklists"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "fmchecklists" ( "CLID" int(10) unsigned NOT NULL, "Name" varchar(100) NOT NULL, "Title" varchar(150) DEFAULT NULL, "Locality" varchar(500) DEFAULT NULL, "Publication" varchar(500) DEFAULT NULL, "Abstract" text, "Authors" varchar(250) DEFAULT NULL, "Type" varchar(50) DEFAULT 'static', "dynamicsql" varchar(250) DEFAULT NULL, "Parent" varchar(50) DEFAULT NULL, "parentclid" int(10) unsigned DEFAULT NULL, "Notes" varchar(500) DEFAULT NULL, "LatCentroid" double(9,6) DEFAULT NULL, "LongCentroid" double(9,6) DEFAULT NULL, "pointradiusmeters" int(10) unsigned DEFAULT NULL, "footprintWKT" text, "percenteffort" int(11) DEFAULT NULL, "Access" varchar(45) DEFAULT 'private', "uid" int(10) unsigned DEFAULT NULL, "SortSequence" int(10) unsigned NOT NULL DEFAULT '50', "expiration" int(10) unsigned DEFAULT NULL, "DateLastModified" datetime DEFAULT NULL, "InitialTimeStamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("CLID"), KEY "FK_checklists_uid" ("uid"), KEY "name" ("Name","Type"), CONSTRAINT "FK_checklists_uid" FOREIGN KEY ("uid") REFERENCES "users" ("uid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "fmchklstcoordinates" -- DROP TABLE IF EXISTS "fmchklstcoordinates"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "fmchklstcoordinates" ( "chklstcoordid" int(11) NOT NULL, "clid" int(10) unsigned NOT NULL, "tid" int(10) unsigned NOT NULL, "decimallatitude" double NOT NULL, "decimallongitude" double NOT NULL, "notes" varchar(250) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("chklstcoordid"), UNIQUE KEY "IndexUnique" ("clid","tid","decimallatitude","decimallongitude"), KEY "FKchklsttaxalink" ("clid","tid"), CONSTRAINT "FKchklsttaxalink" FOREIGN KEY ("clid", "tid") REFERENCES "fmchklsttaxalink" ("CLID", "TID") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "fmchklstprojlink" -- DROP TABLE IF EXISTS "fmchklstprojlink"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "fmchklstprojlink" ( "pid" int(10) unsigned NOT NULL, "clid" int(10) unsigned NOT NULL, "InitialTimeStamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("pid","clid"), KEY "FK_chklst" ("clid"), CONSTRAINT "FK_chklstprojlink_clid" FOREIGN KEY ("clid") REFERENCES "fmchecklists" ("CLID"), CONSTRAINT "FK_chklstprojlink_proj" FOREIGN KEY ("pid") REFERENCES "fmprojects" ("pid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "fmchklsttaxalink" -- DROP TABLE IF EXISTS "fmchklsttaxalink"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "fmchklsttaxalink" ( "TID" int(10) unsigned NOT NULL DEFAULT '0', "CLID" int(10) unsigned NOT NULL DEFAULT '0', "morphospecies" varchar(45) NOT NULL DEFAULT '', "familyoverride" varchar(50) DEFAULT NULL, "Habitat" varchar(250) DEFAULT NULL, "Abundance" varchar(50) DEFAULT NULL, "Notes" varchar(2000) DEFAULT NULL, "explicitExclude" smallint(6) DEFAULT NULL, "source" varchar(250) DEFAULT NULL, "Nativity" varchar(50) DEFAULT NULL COMMENT 'native, introducted', "Endemic" varchar(25) DEFAULT NULL, "internalnotes" varchar(250) DEFAULT NULL, "InitialTimeStamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("TID","CLID","morphospecies"), KEY "FK_chklsttaxalink_cid" ("CLID"), CONSTRAINT "FK_chklsttaxalink_cid" FOREIGN KEY ("CLID") REFERENCES "fmchecklists" ("CLID"), CONSTRAINT "FK_chklsttaxalink_tid" FOREIGN KEY ("TID") REFERENCES "taxa" ("TID") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "fmcltaxacomments" -- DROP TABLE IF EXISTS "fmcltaxacomments"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "fmcltaxacomments" ( "cltaxacommentsid" int(11) NOT NULL, "clid" int(10) unsigned NOT NULL, "tid" int(10) unsigned NOT NULL, "comment" text NOT NULL, "uid" int(10) unsigned NOT NULL, "ispublic" int(11) NOT NULL DEFAULT '1', "parentid" int(11) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("cltaxacommentsid"), KEY "FK_clcomment_users" ("uid"), KEY "FK_clcomment_cltaxa" ("clid","tid"), CONSTRAINT "FK_clcomment_cltaxa" FOREIGN KEY ("clid", "tid") REFERENCES "fmchklsttaxalink" ("CLID", "TID") ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT "FK_clcomment_users" FOREIGN KEY ("uid") REFERENCES "users" ("uid") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "fmdynamicchecklists" -- DROP TABLE IF EXISTS "fmdynamicchecklists"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "fmdynamicchecklists" ( "dynclid" int(10) unsigned NOT NULL, "name" varchar(50) DEFAULT NULL, "details" varchar(250) DEFAULT NULL, "uid" varchar(45) DEFAULT NULL, "type" varchar(45) NOT NULL DEFAULT 'DynamicList', "notes" varchar(250) DEFAULT NULL, "expiration" datetime NOT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("dynclid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "fmdyncltaxalink" -- DROP TABLE IF EXISTS "fmdyncltaxalink"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "fmdyncltaxalink" ( "dynclid" int(10) unsigned NOT NULL, "tid" int(10) unsigned NOT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("dynclid","tid"), KEY "FK_dyncltaxalink_taxa" ("tid"), CONSTRAINT "FK_dyncltaxalink_dynclid" FOREIGN KEY ("dynclid") REFERENCES "fmdynamicchecklists" ("dynclid") ON DELETE CASCADE, CONSTRAINT "FK_dyncltaxalink_taxa" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID") ON DELETE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "fmprojects" -- DROP TABLE IF EXISTS "fmprojects"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "fmprojects" ( "pid" int(10) unsigned NOT NULL, "projname" varchar(45) NOT NULL, "displayname" varchar(150) DEFAULT NULL, "managers" varchar(150) DEFAULT NULL, "briefdescription" varchar(300) DEFAULT NULL, "fulldescription" varchar(2000) DEFAULT NULL, "notes" varchar(250) DEFAULT NULL, "occurrencesearch" int(10) unsigned NOT NULL DEFAULT '0', "ispublic" int(10) unsigned NOT NULL DEFAULT '0', "parentpid" int(10) unsigned DEFAULT NULL, "SortSequence" int(10) unsigned NOT NULL DEFAULT '50', "InitialTimeStamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("pid"), KEY "FK_parentpid_proj" ("parentpid"), CONSTRAINT "FK_parentpid_proj" FOREIGN KEY ("parentpid") REFERENCES "fmprojects" ("pid") ON DELETE NO ACTION ON UPDATE NO ACTION ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "fmvouchers" -- DROP TABLE IF EXISTS "fmvouchers"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "fmvouchers" ( "TID" int(10) unsigned NOT NULL, "CLID" int(10) unsigned NOT NULL, "occid" int(10) unsigned NOT NULL, "Collector" varchar(100) NOT NULL, "editornotes" varchar(50) DEFAULT NULL, "Notes" varchar(250) DEFAULT NULL, "InitialTimeStamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("occid","CLID","TID"), KEY "chklst_taxavouchers" ("TID","CLID"), CONSTRAINT "FK_fmvouchers_occ" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid"), CONSTRAINT "FK_vouchers_cl" FOREIGN KEY ("TID", "CLID") REFERENCES "fmchklsttaxalink" ("TID", "CLID") ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "geothescontinent" -- DROP TABLE IF EXISTS "geothescontinent"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "geothescontinent" ( "gtcid" int(11) NOT NULL, "continentterm" varchar(45) NOT NULL, "abbreviation" varchar(45) DEFAULT NULL, "code" varchar(45) DEFAULT NULL, "lookupterm" int(11) NOT NULL DEFAULT '1', "acceptedid" int(11) DEFAULT NULL, "initialtimestamp" timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("gtcid"), KEY "FK_geothescontinent_accepted_idx" ("acceptedid"), CONSTRAINT "FK_geothescontinent_accepted" FOREIGN KEY ("acceptedid") REFERENCES "geothescontinent" ("gtcid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "geothescountry" -- DROP TABLE IF EXISTS "geothescountry"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "geothescountry" ( "gtcid" int(11) NOT NULL, "countryterm" varchar(45) NOT NULL, "abbreviation" varchar(45) DEFAULT NULL, "iso" varchar(2) DEFAULT NULL, "iso3" varchar(3) DEFAULT NULL, "numcode" int(11) DEFAULT NULL, "lookupterm" int(11) NOT NULL DEFAULT '1', "acceptedid" int(11) DEFAULT NULL, "continentid" int(11) DEFAULT NULL, "initialtimestamp" timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("gtcid"), KEY "FK_geothescountry__idx" ("continentid"), KEY "FK_geothescountry_parent_idx" ("acceptedid"), CONSTRAINT "FK_geothescountry_accepted" FOREIGN KEY ("acceptedid") REFERENCES "geothescountry" ("gtcid"), CONSTRAINT "FK_geothescountry_gtcid" FOREIGN KEY ("continentid") REFERENCES "geothescontinent" ("gtcid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "geothescounty" -- DROP TABLE IF EXISTS "geothescounty"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "geothescounty" ( "gtcoid" int(11) NOT NULL, "countyterm" varchar(45) NOT NULL, "abbreviation" varchar(45) DEFAULT NULL, "code" varchar(45) DEFAULT NULL, "lookupterm" int(11) NOT NULL DEFAULT '1', "acceptedid" int(11) DEFAULT NULL, "stateid" int(11) DEFAULT NULL, "initialtimestamp" timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("gtcoid"), KEY "FK_geothescounty_state_idx" ("stateid"), KEY "FK_geothescounty_accepted_idx" ("acceptedid"), CONSTRAINT "FK_geothescounty_accepted" FOREIGN KEY ("acceptedid") REFERENCES "geothescounty" ("gtcoid"), CONSTRAINT "FK_geothescounty_state" FOREIGN KEY ("stateid") REFERENCES "geothesstateprovince" ("gtspid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "geothesmunicipality" -- DROP TABLE IF EXISTS "geothesmunicipality"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "geothesmunicipality" ( "gtmid" int(11) NOT NULL, "municipalityterm" varchar(45) NOT NULL, "abbreviation" varchar(45) DEFAULT NULL, "code" varchar(45) DEFAULT NULL, "lookupterm" int(11) NOT NULL DEFAULT '1', "acceptedid" int(11) DEFAULT NULL, "countyid" int(11) DEFAULT NULL, "initialtimestamp" timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("gtmid"), KEY "FK_geothesmunicipality_county_idx" ("countyid"), KEY "FK_geothesmunicipality_accepted_idx" ("acceptedid"), CONSTRAINT "FK_geothesmunicipality_accepted" FOREIGN KEY ("acceptedid") REFERENCES "geothescounty" ("gtcoid"), CONSTRAINT "FK_geothesmunicipality_county" FOREIGN KEY ("countyid") REFERENCES "geothescounty" ("gtcoid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "geothesstateprovince" -- DROP TABLE IF EXISTS "geothesstateprovince"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "geothesstateprovince" ( "gtspid" int(11) NOT NULL, "stateterm" varchar(45) NOT NULL, "abbreviation" varchar(45) DEFAULT NULL, "code" varchar(45) DEFAULT NULL, "lookupterm" int(11) NOT NULL DEFAULT '1', "acceptedid" int(11) DEFAULT NULL, "countryid" int(11) DEFAULT NULL, "initialtimestamp" timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("gtspid"), KEY "FK_geothesstate_country_idx" ("countryid"), KEY "FK_geothesstate_accepted_idx" ("acceptedid"), CONSTRAINT "FK_geothesstate_accepted" FOREIGN KEY ("acceptedid") REFERENCES "geothesstateprovince" ("gtspid"), CONSTRAINT "FK_geothesstate_country" FOREIGN KEY ("countryid") REFERENCES "geothescountry" ("gtcid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "glossary" -- DROP TABLE IF EXISTS "glossary"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "glossary" ( "glossid" int(10) unsigned NOT NULL, "term" varchar(45) NOT NULL, "definition" varchar(600) NOT NULL, "language" varchar(45) NOT NULL DEFAULT 'English', "source" varchar(45) DEFAULT NULL, "notes" varchar(250) DEFAULT NULL, "uid" int(10) unsigned DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("glossid"), UNIQUE KEY "Index_term" ("term") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "glossarycatagories" -- DROP TABLE IF EXISTS "glossarycatagories"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "glossarycatagories" ( "catid" int(10) unsigned NOT NULL, "catagory" varchar(45) NOT NULL, "language" varchar(45) NOT NULL DEFAULT 'English', "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("catid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "glossarycatlink" -- DROP TABLE IF EXISTS "glossarycatlink"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "glossarycatlink" ( "glossid" int(10) unsigned NOT NULL, "catid" int(10) unsigned NOT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("glossid","catid"), KEY "FK_glossarycatlink_cat" ("catid"), CONSTRAINT "glossarycatlink_ibfk_1" FOREIGN KEY ("catid") REFERENCES "glossarycatagories" ("catid"), CONSTRAINT "glossarycatlink_ibfk_2" FOREIGN KEY ("glossid") REFERENCES "glossary" ("glossid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "glossaryimages" -- DROP TABLE IF EXISTS "glossaryimages"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "glossaryimages" ( "glimgid" int(10) unsigned NOT NULL, "glossid" int(10) unsigned NOT NULL, "url" varchar(45) NOT NULL, "structures" varchar(150) DEFAULT NULL, "notes" varchar(250) DEFAULT NULL, "uid" int(10) unsigned DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("glimgid"), KEY "FK_glossaryimages_gloss" ("glossid"), CONSTRAINT "glossaryimages_ibfk_1" FOREIGN KEY ("glossid") REFERENCES "glossary" ("glossid") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "imageannotations" -- DROP TABLE IF EXISTS "imageannotations"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "imageannotations" ( "tid" int(10) unsigned DEFAULT NULL, "imgid" int(10) unsigned NOT NULL DEFAULT '0', "AnnDate" datetime NOT NULL DEFAULT '0000-00-00 00:00:00', "Annotator" varchar(100) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("imgid","AnnDate"), KEY "TID" ("tid"), CONSTRAINT "FK_resourceannotations_imgid" FOREIGN KEY ("imgid") REFERENCES "images" ("imgid"), CONSTRAINT "FK_resourceannotations_tid" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "images" -- DROP TABLE IF EXISTS "images"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "images" ( "imgid" int(10) unsigned NOT NULL, "tid" int(10) unsigned DEFAULT NULL, "url" varchar(255) NOT NULL, "thumbnailurl" varchar(255) DEFAULT NULL, "originalurl" varchar(255) DEFAULT NULL, "photographer" varchar(100) DEFAULT NULL, "photographeruid" int(10) unsigned DEFAULT NULL, "imagetype" varchar(50) DEFAULT NULL, "caption" varchar(100) DEFAULT NULL, "owner" varchar(250) DEFAULT NULL, "sourceurl" varchar(255) DEFAULT NULL, "copyright" varchar(255) DEFAULT NULL, "locality" varchar(250) DEFAULT NULL, "occid" int(10) unsigned DEFAULT NULL, "notes" varchar(350) DEFAULT NULL, "anatomy" varchar(100) DEFAULT NULL, "username" varchar(45) DEFAULT NULL, "sortsequence" int(10) unsigned NOT NULL DEFAULT '50', "InitialTimeStamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("imgid"), KEY "Index_tid" ("tid"), KEY "FK_images_occ" ("occid"), KEY "FK_photographeruid" ("photographeruid"), CONSTRAINT "FK_images_occ" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid"), CONSTRAINT "FK_photographeruid" FOREIGN KEY ("photographeruid") REFERENCES "users" ("uid") ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT "FK_taxaimagestid" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "institutions" -- DROP TABLE IF EXISTS "institutions"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "institutions" ( "iid" int(10) unsigned NOT NULL, "InstitutionCode" varchar(45) NOT NULL, "InstitutionName" varchar(150) NOT NULL, "InstitutionName2" varchar(150) DEFAULT NULL, "Address1" varchar(150) DEFAULT NULL, "Address2" varchar(150) DEFAULT NULL, "City" varchar(45) DEFAULT NULL, "StateProvince" varchar(45) DEFAULT NULL, "PostalCode" varchar(45) DEFAULT NULL, "Country" varchar(45) DEFAULT NULL, "Phone" varchar(45) DEFAULT NULL, "Contact" varchar(65) DEFAULT NULL, "Email" varchar(45) DEFAULT NULL, "Url" varchar(250) DEFAULT NULL, "Notes" varchar(250) DEFAULT NULL, "IntialTimeStamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("iid"), UNIQUE KEY "Index_instcode" ("InstitutionCode") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "kmcharacterlang" -- DROP TABLE IF EXISTS "kmcharacterlang"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "kmcharacterlang" ( "cid" int(10) unsigned NOT NULL, "charname" varchar(150) CHARACTER SET utf8 NOT NULL, "language" varchar(45) CHARACTER SET utf8 NOT NULL, "notes" varchar(255) CHARACTER SET utf8 DEFAULT NULL, "description" varchar(255) CHARACTER SET utf8 DEFAULT NULL, "helpurl" varchar(500) CHARACTER SET utf8 DEFAULT NULL, "InitialTimeStamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("cid","language"), CONSTRAINT "kmcharacterlang_ibfk_1" FOREIGN KEY ("cid") REFERENCES "kmcharacters" ("cid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "kmcharacters" -- DROP TABLE IF EXISTS "kmcharacters"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "kmcharacters" ( "cid" int(10) unsigned NOT NULL, "charname" varchar(150) NOT NULL, "chartype" varchar(2) NOT NULL DEFAULT 'UM', "defaultlang" varchar(45) NOT NULL DEFAULT 'English', "difficultyrank" smallint(5) unsigned NOT NULL DEFAULT '1', "hid" int(10) unsigned NOT NULL, "units" varchar(45) DEFAULT NULL, "description" varchar(255) DEFAULT NULL, "notes" varchar(255) DEFAULT NULL, "helpurl" varchar(500) DEFAULT NULL, "enteredby" varchar(45) DEFAULT NULL, "sortsequence" int(10) unsigned DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("cid"), KEY "Index_charname" ("charname"), KEY "Index_sort" ("sortsequence") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "kmchardependance" -- DROP TABLE IF EXISTS "kmchardependance"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "kmchardependance" ( "CID" int(10) unsigned NOT NULL, "CIDDependance" int(10) unsigned NOT NULL, "CSDependance" varchar(16) NOT NULL, "InitialTimeStamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("CSDependance","CIDDependance","CID"), KEY "FK_chardependance_cid" ("CID"), KEY "FK_chardependance_2" ("CIDDependance"), CONSTRAINT "kmchardependance_ibfk_1" FOREIGN KEY ("CID") REFERENCES "kmcharacters" ("cid"), CONSTRAINT "kmchardependance_ibfk_2" FOREIGN KEY ("CID") REFERENCES "kmcharacters" ("cid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "kmcharheading" -- DROP TABLE IF EXISTS "kmcharheading"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "kmcharheading" ( "hid" int(10) unsigned NOT NULL, "headingname" varchar(255) CHARACTER SET utf8 NOT NULL, "language" varchar(45) CHARACTER SET utf8 NOT NULL DEFAULT 'English', "notes" longtext CHARACTER SET utf8, "sortsequence" int(11) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("hid","language"), KEY "HeadingName" ("headingname") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "kmcharheadinglink" -- DROP TABLE IF EXISTS "kmcharheadinglink"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "kmcharheadinglink" ( "HID" int(10) unsigned NOT NULL DEFAULT '0', "CID" int(10) unsigned NOT NULL DEFAULT '0', "Notes" varchar(255) CHARACTER SET utf8 DEFAULT NULL, "InitialTimeStamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("HID","CID"), KEY "CharactersCharHeadingLinker" ("CID"), KEY "CharHeadingCharHeadingLinker" ("HID"), KEY "CID" ("CID"), CONSTRAINT "kmcharheadinglink_ibfk_1" FOREIGN KEY ("CID") REFERENCES "kmcharacters" ("cid"), CONSTRAINT "kmcharheadinglink_ibfk_2" FOREIGN KEY ("HID") REFERENCES "kmcharheading" ("hid") ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "kmchartaxalink" -- DROP TABLE IF EXISTS "kmchartaxalink"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "kmchartaxalink" ( "CID" int(10) unsigned NOT NULL DEFAULT '0', "TID" int(10) unsigned NOT NULL DEFAULT '0', "Status" varchar(50) CHARACTER SET utf8 DEFAULT NULL, "Notes" varchar(255) CHARACTER SET utf8 DEFAULT NULL, "Relation" varchar(45) CHARACTER SET utf8 NOT NULL DEFAULT 'include', "EditabilityInherited" bit(1) DEFAULT NULL, "timestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("CID","TID"), KEY "FK_CharTaxaLink-TID" ("TID"), CONSTRAINT "kmchartaxalink_ibfk_1" FOREIGN KEY ("CID") REFERENCES "kmcharacters" ("cid"), CONSTRAINT "kmchartaxalink_ibfk_2" FOREIGN KEY ("TID") REFERENCES "taxa" ("TID") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "kmcs" -- DROP TABLE IF EXISTS "kmcs"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "kmcs" ( "cid" int(10) unsigned NOT NULL DEFAULT '0', "cs" varchar(16) NOT NULL, "CharStateName" varchar(255) CHARACTER SET utf8 DEFAULT NULL, "Implicit" tinyint(1) NOT NULL DEFAULT '0', "Notes" longtext CHARACTER SET utf8, "Description" varchar(255) CHARACTER SET utf8 DEFAULT NULL, "IllustrationUrl" varchar(250) DEFAULT NULL, "StateID" int(10) unsigned DEFAULT NULL, "Language" varchar(45) CHARACTER SET utf8 NOT NULL DEFAULT 'English', "SortSequence" int(10) unsigned DEFAULT NULL, "InitialTimeStamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, "EnteredBy" varchar(45) CHARACTER SET utf8 DEFAULT NULL, PRIMARY KEY ("cs","cid"), KEY "FK_cs_chars" ("cid"), CONSTRAINT "kmcs_ibfk_1" FOREIGN KEY ("cid") REFERENCES "kmcharacters" ("cid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "kmcsimages" -- DROP TABLE IF EXISTS "kmcsimages"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "kmcsimages" ( "csimgid" int(10) unsigned NOT NULL, "cid" int(10) unsigned NOT NULL, "cs" varchar(16) NOT NULL, "url" varchar(45) NOT NULL, "notes" varchar(250) DEFAULT NULL, "sortsequence" varchar(45) NOT NULL DEFAULT '50', "username" varchar(45) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("csimgid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "kmcslang" -- DROP TABLE IF EXISTS "kmcslang"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "kmcslang" ( "cid" int(10) unsigned NOT NULL, "cs" varchar(16) NOT NULL, "charstatename" varchar(150) NOT NULL, "language" varchar(45) NOT NULL, "description" varchar(255) DEFAULT NULL, "notes" varchar(255) DEFAULT NULL, "intialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("cid","cs","language"), CONSTRAINT "kmcslang_ibfk_1" FOREIGN KEY ("cid", "cs") REFERENCES "kmcs" ("cid", "cs") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "kmdescr" -- DROP TABLE IF EXISTS "kmdescr"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "kmdescr" ( "TID" int(10) unsigned NOT NULL DEFAULT '0', "CID" int(10) unsigned NOT NULL DEFAULT '0', "Modifier" varchar(255) CHARACTER SET utf8 DEFAULT NULL, "CS" varchar(16) NOT NULL, "X" double(15,5) DEFAULT NULL, "TXT" longtext CHARACTER SET utf8, "PseudoTrait" int(5) unsigned DEFAULT '0', "Frequency" int(5) unsigned NOT NULL DEFAULT '5' COMMENT 'Frequency of occurrence; 1 = rare... 5 = common', "Inherited" varchar(50) CHARACTER SET utf8 DEFAULT NULL, "Source" varchar(100) CHARACTER SET utf8 DEFAULT NULL, "Seq" int(10) DEFAULT NULL, "Notes" longtext CHARACTER SET utf8, "DateEntered" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("TID","CID","CS"), KEY "CSDescr" ("CID","CS"), CONSTRAINT "kmdescr_ibfk_1" FOREIGN KEY ("CID", "CS") REFERENCES "kmcs" ("cid", "cs"), CONSTRAINT "kmdescr_ibfk_2" FOREIGN KEY ("TID") REFERENCES "taxa" ("TID") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "kmdescrdeletions" -- DROP TABLE IF EXISTS "kmdescrdeletions"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "kmdescrdeletions" ( "TID" int(10) unsigned NOT NULL, "CID" int(10) unsigned NOT NULL, "CS" varchar(16) NOT NULL, "Modifier" varchar(255) DEFAULT NULL, "X" double(15,5) DEFAULT NULL, "TXT" longtext, "Inherited" varchar(50) DEFAULT NULL, "Source" varchar(100) DEFAULT NULL, "Seq" int(10) unsigned DEFAULT NULL, "Notes" longtext, "InitialTimeStamp" datetime DEFAULT NULL, "DeletedBy" varchar(100) NOT NULL, "DeletedTimeStamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, "PK" int(10) unsigned NOT NULL, PRIMARY KEY ("PK") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "lkupcountry" -- DROP TABLE IF EXISTS "lkupcountry"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "lkupcountry" ( "countryId" int(11) NOT NULL, "countryName" varchar(100) NOT NULL, "iso" varchar(2) DEFAULT NULL, "iso3" varchar(3) DEFAULT NULL, "numcode" int(11) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("countryId"), UNIQUE KEY "country_unique" ("countryName") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "lkupcounty" -- DROP TABLE IF EXISTS "lkupcounty"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "lkupcounty" ( "countyId" int(11) NOT NULL, "stateId" int(11) NOT NULL, "countyName" varchar(100) NOT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("countyId"), UNIQUE KEY "unique_county" ("stateId","countyName"), KEY "fk_stateprovince" ("stateId"), KEY "index_countyname" ("countyName"), CONSTRAINT "fk_stateprovince" FOREIGN KEY ("stateId") REFERENCES "lkupstateprovince" ("stateId") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "lkupstateprovince" -- DROP TABLE IF EXISTS "lkupstateprovince"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "lkupstateprovince" ( "stateId" int(11) NOT NULL, "countryId" int(11) NOT NULL, "stateName" varchar(100) NOT NULL, "abbrev" varchar(2) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("stateId"), UNIQUE KEY "state_index" ("stateName","countryId"), KEY "fk_country" ("countryId"), KEY "index_statename" ("stateName"), CONSTRAINT "fk_country" FOREIGN KEY ("countryId") REFERENCES "lkupcountry" ("countryId") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omassociatedoccurrences" -- DROP TABLE IF EXISTS "omassociatedoccurrences"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omassociatedoccurrences" ( "aoid" int(10) unsigned NOT NULL, "occid" int(10) unsigned NOT NULL, "occidassociate" int(10) unsigned DEFAULT NULL, "relationship" varchar(150) NOT NULL, "identifier" varchar(250) DEFAULT NULL COMMENT 'e.g. GUID', "resourceurl" varchar(250) DEFAULT NULL, "notes" varchar(250) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("aoid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omcollcatagories" -- DROP TABLE IF EXISTS "omcollcatagories"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omcollcatagories" ( "ccpk" int(10) unsigned NOT NULL, "catagory" varchar(45) NOT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("ccpk") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omcollcatlink" -- DROP TABLE IF EXISTS "omcollcatlink"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omcollcatlink" ( "ccpk" int(10) unsigned NOT NULL, "collid" int(10) unsigned NOT NULL, "sortsequence" int(11) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("ccpk","collid"), KEY "FK_collcatlink_coll" ("collid"), CONSTRAINT "FK_collcatlink_cat" FOREIGN KEY ("ccpk") REFERENCES "omcollcatagories" ("ccpk") ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT "FK_collcatlink_coll" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omcollections" -- DROP TABLE IF EXISTS "omcollections"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omcollections" ( "CollID" int(10) unsigned NOT NULL, "InstitutionCode" varchar(45) NOT NULL, "CollectionCode" varchar(45) DEFAULT NULL, "CollectionName" varchar(150) NOT NULL, "iid" int(10) unsigned DEFAULT NULL, "briefdescription" varchar(300) DEFAULT NULL, "fulldescription" varchar(2000) DEFAULT NULL, "Homepage" varchar(250) DEFAULT NULL, "IndividualUrl" varchar(500) DEFAULT NULL, "Contact" varchar(45) DEFAULT NULL, "email" varchar(45) DEFAULT NULL, "latitudedecimal" double(8,6) DEFAULT NULL, "longitudedecimal" double(9,6) DEFAULT NULL, "icon" varchar(250) DEFAULT NULL, "CollType" varchar(45) NOT NULL DEFAULT 'Preserved Specimens' COMMENT 'Preserved Specimens, General Observations, Observations', "ManagementType" varchar(45) DEFAULT 'Snapshot' COMMENT 'Snapshot, Live Data', "PublicEdits" int(1) unsigned NOT NULL DEFAULT '1', "guidtarget" varchar(45) DEFAULT NULL, "rightsHolder" varchar(250) DEFAULT NULL, "rights" varchar(250) DEFAULT NULL, "bibliographicCitation" varchar(1000) DEFAULT NULL, "accessrights" varchar(250) DEFAULT NULL, "SortSeq" int(10) unsigned DEFAULT NULL, "InitialTimeStamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("CollID"), KEY "Index_inst" ("InstitutionCode") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omcollectionstats" -- DROP TABLE IF EXISTS "omcollectionstats"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omcollectionstats" ( "collid" int(10) unsigned NOT NULL, "recordcnt" int(10) unsigned NOT NULL DEFAULT '0', "georefcnt" int(10) unsigned DEFAULT NULL, "familycnt" int(10) unsigned DEFAULT NULL, "genuscnt" int(10) unsigned DEFAULT NULL, "speciescnt" int(10) unsigned DEFAULT NULL, "uploaddate" datetime DEFAULT NULL, "uploadedby" varchar(45) DEFAULT NULL, "dbtype" varchar(45) DEFAULT NULL, "dburl" varchar(250) DEFAULT NULL, "dbport" varchar(45) DEFAULT NULL, "dblogin" varchar(45) DEFAULT NULL, "dbpassword" varchar(45) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("collid"), CONSTRAINT "omcollectionstats_ibfk_1" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omcollectors" -- DROP TABLE IF EXISTS "omcollectors"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omcollectors" ( "recordedById" int(10) unsigned NOT NULL, "familyname" varchar(45) NOT NULL, "firstname" varchar(45) DEFAULT NULL, "middleinitial" varchar(45) DEFAULT NULL, "startyearactive" int(11) DEFAULT NULL, "endyearactive" int(11) DEFAULT NULL, "notes" varchar(255) DEFAULT NULL, "rating" int(11) DEFAULT '10', "initialtimestamp" timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("recordedById"), KEY "fullname" ("familyname","firstname") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omcollsecondary" -- DROP TABLE IF EXISTS "omcollsecondary"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omcollsecondary" ( "ocsid" int(10) unsigned NOT NULL, "collid" int(10) unsigned NOT NULL, "InstitutionCode" varchar(45) NOT NULL, "CollectionCode" varchar(45) DEFAULT NULL, "CollectionName" varchar(150) NOT NULL, "BriefDescription" varchar(300) DEFAULT NULL, "FullDescription" varchar(1000) DEFAULT NULL, "Homepage" varchar(250) DEFAULT NULL, "IndividualUrl" varchar(500) DEFAULT NULL, "Contact" varchar(45) DEFAULT NULL, "Email" varchar(45) DEFAULT NULL, "LatitudeDecimal" double DEFAULT NULL, "LongitudeDecimal" double DEFAULT NULL, "icon" varchar(250) DEFAULT NULL, "CollType" varchar(45) DEFAULT NULL, "SortSeq" int(10) unsigned DEFAULT NULL, "InitialTimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("ocsid"), KEY "FK_omcollsecondary_coll" ("collid"), CONSTRAINT "FK_omcollsecondary_coll" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omcrowdsourcecentral" -- DROP TABLE IF EXISTS "omcrowdsourcecentral"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omcrowdsourcecentral" ( "omcsid" int(11) NOT NULL, "collid" int(10) unsigned NOT NULL, "instructions" text, "trainingurl" varchar(500) DEFAULT NULL, "editorlevel" int(11) NOT NULL DEFAULT '0' COMMENT '0=public, 1=public limited, 2=private', "notes" varchar(250) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("omcsid"), UNIQUE KEY "Index_omcrowdsourcecentral_collid" ("collid"), KEY "FK_omcrowdsourcecentral_collid" ("collid"), CONSTRAINT "FK_omcrowdsourcecentral_collid" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omcrowdsourcequeue" -- DROP TABLE IF EXISTS "omcrowdsourcequeue"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omcrowdsourcequeue" ( "idomcrowdsourcequeue" int(11) NOT NULL, "omcsid" int(11) NOT NULL, "occid" int(10) unsigned NOT NULL, "reviewstatus" int(11) NOT NULL DEFAULT '0' COMMENT '0=open,5=pending review, 10=closed', "uidprocessor" int(10) unsigned DEFAULT NULL, "points" int(11) DEFAULT NULL COMMENT '0=fail, 1=minor edits, 2=no edits , 3=excelled', "notes" varchar(250) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("idomcrowdsourcequeue"), UNIQUE KEY "Index_omcrowdsource_occid" ("occid"), KEY "FK_omcrowdsourcequeue_occid" ("occid"), KEY "FK_omcrowdsourcequeue_uid" ("uidprocessor"), CONSTRAINT "FK_omcrowdsourcequeue_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT "FK_omcrowdsourcequeue_uid" FOREIGN KEY ("uidprocessor") REFERENCES "users" ("uid") ON DELETE NO ACTION ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omexsiccatinumbers" -- DROP TABLE IF EXISTS "omexsiccatinumbers"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omexsiccatinumbers" ( "omenid" int(10) unsigned NOT NULL, "exsnumber" varchar(45) NOT NULL, "ometid" int(10) unsigned NOT NULL, "notes" varchar(250) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("omenid"), UNIQUE KEY "Index_omexsiccatinumbers_unique" ("exsnumber","ometid"), KEY "FK_exsiccatiTitleNumber" ("ometid"), CONSTRAINT "FK_exsiccatiTitleNumber" FOREIGN KEY ("ometid") REFERENCES "omexsiccatititles" ("ometid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omexsiccatiocclink" -- DROP TABLE IF EXISTS "omexsiccatiocclink"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omexsiccatiocclink" ( "omenid" int(10) unsigned NOT NULL, "occid" int(10) unsigned NOT NULL, "ranking" int(11) NOT NULL DEFAULT '50', "notes" varchar(250) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("omenid","occid"), UNIQUE KEY "UniqueOmexsiccatiOccLink" ("occid"), KEY "FKExsiccatiNumOccLink1" ("omenid"), KEY "FKExsiccatiNumOccLink2" ("occid"), CONSTRAINT "FKExsiccatiNumOccLink1" FOREIGN KEY ("omenid") REFERENCES "omexsiccatinumbers" ("omenid") ON DELETE CASCADE, CONSTRAINT "FKExsiccatiNumOccLink2" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omexsiccatititles" -- DROP TABLE IF EXISTS "omexsiccatititles"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omexsiccatititles" ( "ometid" int(10) unsigned NOT NULL, "title" varchar(150) NOT NULL, "abbreviation" varchar(100) DEFAULT NULL, "editor" varchar(150) DEFAULT NULL, "exsrange" varchar(45) DEFAULT NULL, "startdate" varchar(45) DEFAULT NULL, "enddate" varchar(45) DEFAULT NULL, "source" varchar(45) DEFAULT NULL, "notes" varchar(2000) DEFAULT NULL, "lasteditedby" varchar(45) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("ometid"), KEY "index_exsiccatiTitle" ("title") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omoccurcomments" -- DROP TABLE IF EXISTS "omoccurcomments"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omoccurcomments" ( "comid" int(11) NOT NULL, "occid" int(10) unsigned NOT NULL, "comment" text NOT NULL, "uid" int(10) unsigned NOT NULL, "reviewstatus" int(10) unsigned NOT NULL DEFAULT '0', "parentcomid" int(10) unsigned DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("comid"), KEY "fk_omoccurcomments_occid" ("occid"), KEY "fk_omoccurcomments_uid" ("uid"), CONSTRAINT "fk_omoccurcomments_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT "fk_omoccurcomments_uid" FOREIGN KEY ("uid") REFERENCES "users" ("uid") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omoccurdatasetlink" -- DROP TABLE IF EXISTS "omoccurdatasetlink"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omoccurdatasetlink" ( "occid" int(10) unsigned NOT NULL, "datasetid" int(10) unsigned NOT NULL, "notes" varchar(250) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("occid","datasetid"), KEY "FK_omoccurdatasetlink_datasetid" ("datasetid"), KEY "FK_omoccurdatasetlink_occid" ("occid"), CONSTRAINT "FK_omoccurdatasetlink_datasetid" FOREIGN KEY ("datasetid") REFERENCES "omoccurdatasets" ("datasetid") ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT "FK_omoccurdatasetlink_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omoccurdatasets" -- DROP TABLE IF EXISTS "omoccurdatasets"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omoccurdatasets" ( "datasetid" int(10) unsigned NOT NULL, "name" varchar(100) NOT NULL, "notes" varchar(250) DEFAULT NULL, "sortsequence" int(11) DEFAULT NULL, "uid" int(11) NOT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("datasetid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omoccurdeterminations" -- DROP TABLE IF EXISTS "omoccurdeterminations"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omoccurdeterminations" ( "detid" int(10) unsigned NOT NULL, "occid" int(10) unsigned NOT NULL, "identifiedBy" varchar(60) NOT NULL, "idbyid" int(10) unsigned DEFAULT NULL, "dateIdentified" varchar(45) NOT NULL, "sciname" varchar(100) NOT NULL, "tidinterpreted" int(10) unsigned DEFAULT NULL, "scientificNameAuthorship" varchar(100) DEFAULT NULL, "identificationQualifier" varchar(45) DEFAULT NULL, "iscurrent" int(2) DEFAULT '0', "identificationReferences" varchar(255) DEFAULT NULL, "identificationRemarks" varchar(255) DEFAULT NULL, "sortsequence" int(10) unsigned DEFAULT '10', "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("detid"), UNIQUE KEY "Index_unique" ("occid","dateIdentified","identifiedBy"), KEY "FK_omoccurdets_tid" ("tidinterpreted"), KEY "FK_omoccurdets_idby_idx" ("idbyid"), CONSTRAINT "FK_omoccurdets_idby" FOREIGN KEY ("idbyid") REFERENCES "omcollectors" ("recordedById") ON DELETE SET NULL ON UPDATE SET NULL, CONSTRAINT "FK_omoccurdets_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT "FK_omoccurdets_tid" FOREIGN KEY ("tidinterpreted") REFERENCES "taxa" ("TID") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omoccurduplicates" -- DROP TABLE IF EXISTS "omoccurduplicates"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omoccurduplicates" ( "duplicateid" int(11) NOT NULL, "projIdentifier" varchar(30) NOT NULL, "projName" varchar(255) NOT NULL, "isExsiccata" int(11) NOT NULL DEFAULT '0', "exsiccataEditors" varchar(150) DEFAULT NULL, "notes" varchar(255) DEFAULT NULL, "initialTimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("duplicateid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omoccureditlocks" -- DROP TABLE IF EXISTS "omoccureditlocks"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omoccureditlocks" ( "occid" int(10) unsigned NOT NULL, "uid" int(11) NOT NULL, "ts" int(11) NOT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("occid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omoccuredits" -- DROP TABLE IF EXISTS "omoccuredits"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omoccuredits" ( "ocedid" int(11) NOT NULL, "occid" int(10) unsigned NOT NULL, "FieldName" varchar(45) NOT NULL, "FieldValueNew" text NOT NULL, "FieldValueOld" text NOT NULL, "ReviewStatus" int(1) NOT NULL DEFAULT '1' COMMENT '1=Open;2=Pending;3=Closed', "AppliedStatus" int(1) NOT NULL DEFAULT '0' COMMENT '0=Not Applied;1=Applied', "uid" int(10) unsigned NOT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("ocedid"), KEY "fk_omoccuredits_uid" ("uid"), KEY "fk_omoccuredits_occid" ("occid"), CONSTRAINT "fk_omoccuredits_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT "fk_omoccuredits_uid" FOREIGN KEY ("uid") REFERENCES "users" ("uid") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omoccurexchange" -- DROP TABLE IF EXISTS "omoccurexchange"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omoccurexchange" ( "exchangeid" int(10) unsigned NOT NULL, "identifier" varchar(30) DEFAULT NULL, "collid" int(10) unsigned DEFAULT NULL, "iid" int(10) unsigned DEFAULT NULL, "transactionType" varchar(10) DEFAULT NULL, "in_out" varchar(3) DEFAULT NULL, "dateSent" date DEFAULT NULL, "dateReceived" date DEFAULT NULL, "totalBoxes" int(5) DEFAULT NULL, "shippingMethod" varchar(50) DEFAULT NULL, "totalExMounted" int(5) DEFAULT NULL, "totalExUnmounted" int(5) DEFAULT NULL, "totalGift" int(5) DEFAULT NULL, "totalGiftDet" int(5) DEFAULT NULL, "adjustment" int(5) DEFAULT NULL, "invoiceBalance" int(6) DEFAULT NULL, "invoiceMessage" varchar(500) DEFAULT NULL, "description" varchar(1000) DEFAULT NULL, "notes" varchar(500) DEFAULT NULL, "createdBy" varchar(20) DEFAULT NULL, "initialTimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("exchangeid"), KEY "FK_occexch_coll" ("collid"), CONSTRAINT "FK_occexch_coll" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omoccurgenetic" -- DROP TABLE IF EXISTS "omoccurgenetic"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omoccurgenetic" ( "idoccurgenetic" int(11) NOT NULL, "occid" int(10) unsigned NOT NULL, "identifier" varchar(150) DEFAULT NULL, "resourcename" varchar(150) NOT NULL, "locus" varchar(45) DEFAULT NULL, "resourceurl" varchar(500) DEFAULT NULL, "notes" varchar(45) DEFAULT NULL, "initialtimestamp" varchar(45) DEFAULT NULL, PRIMARY KEY ("idoccurgenetic"), KEY "FK_omoccurgenetic" ("occid"), CONSTRAINT "FK_omoccurgenetic" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omoccurgeoindex" -- DROP TABLE IF EXISTS "omoccurgeoindex"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omoccurgeoindex" ( "tid" int(10) unsigned NOT NULL, "decimallatitude" double NOT NULL, "decimallongitude" double NOT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("tid","decimallatitude","decimallongitude"), CONSTRAINT "FK_specgeoindex_taxa" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omoccurloans" -- DROP TABLE IF EXISTS "omoccurloans"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omoccurloans" ( "loanid" int(10) unsigned NOT NULL, "loanIdentifierOwn" varchar(30) DEFAULT NULL, "loanIdentifierBorr" varchar(30) DEFAULT NULL, "collidOwn" int(10) unsigned DEFAULT NULL, "collidBorr" int(10) unsigned DEFAULT NULL, "iidOwner" int(10) unsigned DEFAULT NULL, "iidBorrower" int(10) unsigned DEFAULT NULL, "dateSent" date DEFAULT NULL, "dateSentReturn" date DEFAULT NULL, "receivedStatus" varchar(250) DEFAULT NULL, "totalBoxes" int(5) DEFAULT NULL, "totalBoxesReturned" int(5) DEFAULT NULL, "numSpecimens" int(5) DEFAULT NULL, "shippingMethod" varchar(50) DEFAULT NULL, "shippingMethodReturn" varchar(50) DEFAULT NULL, "dateDue" date DEFAULT NULL, "dateReceivedOwn" date DEFAULT NULL, "dateReceivedBorr" date DEFAULT NULL, "dateClosed" date DEFAULT NULL, "forWhom" varchar(50) DEFAULT NULL, "description" varchar(1000) DEFAULT NULL, "invoiceMessageOwn" varchar(500) DEFAULT NULL, "invoiceMessageBorr" varchar(500) DEFAULT NULL, "notes" varchar(500) DEFAULT NULL, "createdByOwn" varchar(30) DEFAULT NULL, "createdByBorr" varchar(30) DEFAULT NULL, "processingStatus" int(5) unsigned DEFAULT '1', "processedByOwn" varchar(30) DEFAULT NULL, "processedByBorr" varchar(30) DEFAULT NULL, "processedByReturnOwn" varchar(30) DEFAULT NULL, "processedByReturnBorr" varchar(30) DEFAULT NULL, "initialTimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("loanid"), KEY "FK_occurloans_owninst" ("iidOwner"), KEY "FK_occurloans_borrinst" ("iidBorrower"), KEY "FK_occurloans_owncoll" ("collidOwn"), KEY "FK_occurloans_borrcoll" ("collidBorr"), CONSTRAINT "FK_occurloans_borrcoll" FOREIGN KEY ("collidBorr") REFERENCES "omcollections" ("CollID") ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT "FK_occurloans_borrinst" FOREIGN KEY ("iidBorrower") REFERENCES "institutions" ("iid"), CONSTRAINT "FK_occurloans_owncoll" FOREIGN KEY ("collidOwn") REFERENCES "omcollections" ("CollID") ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT "FK_occurloans_owninst" FOREIGN KEY ("iidOwner") REFERENCES "institutions" ("iid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omoccurloanslink" -- DROP TABLE IF EXISTS "omoccurloanslink"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omoccurloanslink" ( "loanid" int(10) unsigned NOT NULL, "occid" int(10) unsigned NOT NULL, "returndate" date DEFAULT NULL, "notes" varchar(255) DEFAULT NULL, "initialTimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("loanid","occid"), KEY "FK_occurloanlink_occid" ("occid"), KEY "FK_occurloanlink_loanid" ("loanid"), CONSTRAINT "FK_occurloanlink_loanid" FOREIGN KEY ("loanid") REFERENCES "omoccurloans" ("loanid") ON UPDATE CASCADE, CONSTRAINT "FK_occurloanlink_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omoccurrences" -- DROP TABLE IF EXISTS "omoccurrences"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omoccurrences" ( "occid" int(10) unsigned NOT NULL, "collid" int(10) unsigned NOT NULL, "dbpk" varchar(45) DEFAULT NULL, "basisOfRecord" varchar(32) DEFAULT 'PreservedSpecimen' COMMENT 'PreservedSpecimen, LivingSpecimen, HumanObservation', "occurrenceID" varchar(255) DEFAULT NULL COMMENT 'UniqueGlobalIdentifier', "catalogNumber" varchar(32) DEFAULT NULL, "institutionID" varchar(255) DEFAULT NULL, "collectionID" varchar(255) DEFAULT NULL, "institutionCode" varchar(64) DEFAULT NULL, "collectionCode" varchar(64) DEFAULT NULL, "datasetID" varchar(255) DEFAULT NULL, "otherCatalogNumbers" varchar(255) DEFAULT NULL, "ownerInstitutionCode" varchar(32) DEFAULT NULL, "family" varchar(255) DEFAULT NULL, "scientificName" varchar(255) DEFAULT NULL, "sciname" varchar(255) DEFAULT NULL, "tidinterpreted" int(10) unsigned DEFAULT NULL, "genus" varchar(255) DEFAULT NULL, "specificEpithet" varchar(255) DEFAULT NULL, "taxonRank" varchar(32) DEFAULT NULL, "infraspecificEpithet" varchar(255) DEFAULT NULL, "scientificNameAuthorship" varchar(255) DEFAULT NULL, "taxonRemarks" text, "identifiedBy" varchar(255) DEFAULT NULL, "dateIdentified" varchar(45) DEFAULT NULL, "identificationReferences" text, "identificationRemarks" text, "identificationQualifier" varchar(255) DEFAULT NULL COMMENT 'cf, aff, etc', "typeStatus" varchar(255) DEFAULT NULL, "recordedBy" varchar(255) DEFAULT NULL COMMENT 'Collector(s)', "recordNumber" varchar(45) DEFAULT NULL COMMENT 'Collector Number', "recordedById" int(10) unsigned DEFAULT NULL, "associatedCollectors" varchar(255) DEFAULT NULL COMMENT 'not DwC', "eventDate" date DEFAULT NULL, "year" int(10) DEFAULT NULL, "month" int(10) DEFAULT NULL, "day" int(10) DEFAULT NULL, "startDayOfYear" int(10) DEFAULT NULL, "endDayOfYear" int(10) DEFAULT NULL, "verbatimEventDate" varchar(255) DEFAULT NULL, "habitat" text COMMENT 'Habitat, substrait, etc', "substrate" varchar(500) DEFAULT NULL, "fieldNotes" text, "fieldnumber" varchar(45) DEFAULT NULL, "occurrenceRemarks" text COMMENT 'General Notes', "informationWithheld" varchar(250) DEFAULT NULL, "dataGeneralizations" varchar(250) DEFAULT NULL, "associatedOccurrences" text, "associatedTaxa" text COMMENT 'Associated Species', "dynamicProperties" text, "verbatimAttributes" text, "attributes" text COMMENT 'Plant Description?', "reproductiveCondition" varchar(255) DEFAULT NULL COMMENT 'Phenology: flowers, fruit, sterile', "cultivationStatus" int(10) DEFAULT NULL COMMENT '0 = wild, 1 = cultivated', "establishmentMeans" varchar(45) DEFAULT NULL COMMENT 'cultivated, invasive, escaped from captivity, wild, native', "lifeStage" varchar(45) DEFAULT NULL, "sex" varchar(45) DEFAULT NULL, "individualCount" varchar(45) DEFAULT NULL, "samplingProtocol" varchar(100) DEFAULT NULL, "preparations" varchar(100) DEFAULT NULL, "country" varchar(64) DEFAULT NULL, "stateProvince" varchar(255) DEFAULT NULL, "county" varchar(255) DEFAULT NULL, "municipality" varchar(255) DEFAULT NULL, "locality" text, "localitySecurity" int(10) DEFAULT '0' COMMENT '0 = no security; 1 = hidden locality', "localitySecurityReason" varchar(100) DEFAULT NULL, "decimalLatitude" double DEFAULT NULL, "decimalLongitude" double DEFAULT NULL, "geodeticDatum" varchar(255) DEFAULT NULL, "coordinateUncertaintyInMeters" int(10) unsigned DEFAULT NULL, "footprintWKT" text, "coordinatePrecision" decimal(9,7) DEFAULT NULL, "locationRemarks" text, "verbatimCoordinates" varchar(255) DEFAULT NULL, "verbatimCoordinateSystem" varchar(255) DEFAULT NULL, "georeferencedBy" varchar(255) DEFAULT NULL, "georeferenceProtocol" varchar(255) DEFAULT NULL, "georeferenceSources" varchar(255) DEFAULT NULL, "georeferenceVerificationStatus" varchar(32) DEFAULT NULL, "georeferenceRemarks" varchar(255) DEFAULT NULL, "minimumElevationInMeters" int(6) DEFAULT NULL, "maximumElevationInMeters" int(6) DEFAULT NULL, "verbatimElevation" varchar(255) DEFAULT NULL, "previousIdentifications" text, "disposition" varchar(100) DEFAULT NULL, "genericcolumn1" varchar(100) DEFAULT NULL, "genericcolumn2" varchar(100) DEFAULT NULL, "modified" datetime DEFAULT NULL COMMENT 'DateLastModified', "language" varchar(20) DEFAULT NULL, "duplicateid" int(11) DEFAULT NULL, "observeruid" int(10) unsigned DEFAULT NULL, "processingstatus" varchar(45) DEFAULT NULL, "deaccessioned" int(1) NOT NULL DEFAULT '0', "recordEnteredBy" varchar(250) DEFAULT NULL, "duplicateQuantity" int(10) unsigned DEFAULT NULL, "labelProject" varchar(50) DEFAULT NULL, "dateLastModified" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("occid"), UNIQUE KEY "Index_collid" ("collid","dbpk"), KEY "Index_sciname" ("sciname"), KEY "Index_family" ("family"), KEY "Index_country" ("country"), KEY "Index_state" ("stateProvince"), KEY "Index_county" ("county"), KEY "Index_collector" ("recordedBy"), KEY "Index_gui" ("occurrenceID"), KEY "Index_ownerInst" ("ownerInstitutionCode"), KEY "FK_omoccurrences_tid" ("tidinterpreted"), KEY "FK_omoccurrences_uid" ("observeruid"), KEY "Index_municipality" ("municipality"), KEY "Index_collnum" ("recordNumber"), KEY "Index_catalognumber" ("catalogNumber"), KEY "FK_recordedbyid" ("recordedById"), KEY "FK_omoccurrences_dupes" ("duplicateid"), KEY "Index_eventDate" ("eventDate"), KEY "Index_occurrences_procstatus" ("processingstatus"), CONSTRAINT "FK_omoccurrences_collid" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT "FK_omoccurrences_dupes" FOREIGN KEY ("duplicateid") REFERENCES "omoccurduplicates" ("duplicateid") ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT "FK_omoccurrences_recbyid" FOREIGN KEY ("recordedById") REFERENCES "omcollectors" ("recordedById") ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT "FK_omoccurrences_tid" FOREIGN KEY ("tidinterpreted") REFERENCES "taxa" ("TID") ON DELETE SET NULL ON UPDATE CASCADE, CONSTRAINT "FK_omoccurrences_uid" FOREIGN KEY ("observeruid") REFERENCES "users" ("uid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omoccurverification" -- DROP TABLE IF EXISTS "omoccurverification"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omoccurverification" ( "ovsid" int(11) NOT NULL, "occid" int(10) unsigned NOT NULL, "category" varchar(45) NOT NULL, "ranking" int(11) NOT NULL, "protocol" varchar(100) DEFAULT NULL, "source" varchar(45) DEFAULT NULL, "uid" int(10) unsigned DEFAULT NULL, "notes" varchar(250) DEFAULT NULL, "initialtimestamp" timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("ovsid"), KEY "FK_omoccurverification_occid_idx" ("occid"), KEY "FK_omoccurverification_uid_idx" ("uid"), CONSTRAINT "FK_omoccurverification_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT "FK_omoccurverification_uid" FOREIGN KEY ("uid") REFERENCES "users" ("uid") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omsurveyoccurlink" -- DROP TABLE IF EXISTS "omsurveyoccurlink"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omsurveyoccurlink" ( "occid" int(10) unsigned NOT NULL, "surveyid" int(10) unsigned NOT NULL, "notes" varchar(250) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("occid","surveyid"), KEY "FK_omsurveyoccurlink_sur" ("surveyid"), CONSTRAINT "FK_omsurveyoccurlink_occ" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid"), CONSTRAINT "FK_omsurveyoccurlink_sur" FOREIGN KEY ("surveyid") REFERENCES "omsurveys" ("surveyid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omsurveyprojlink" -- DROP TABLE IF EXISTS "omsurveyprojlink"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omsurveyprojlink" ( "surveyid" int(10) unsigned NOT NULL, "pid" int(10) unsigned NOT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("surveyid","pid"), KEY "FK_specprojcatlink_cat" ("pid"), CONSTRAINT "FK_omsurveyprojlink_proj" FOREIGN KEY ("pid") REFERENCES "fmprojects" ("pid"), CONSTRAINT "FK_omsurveyprojlink_sur" FOREIGN KEY ("surveyid") REFERENCES "omsurveys" ("surveyid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "omsurveys" -- DROP TABLE IF EXISTS "omsurveys"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "omsurveys" ( "surveyid" int(10) unsigned NOT NULL, "projectname" varchar(75) NOT NULL, "locality" varchar(1000) DEFAULT NULL, "managers" varchar(150) DEFAULT NULL, "latcentroid" double(9,6) DEFAULT NULL, "longcentroid" double(9,6) DEFAULT NULL, "notes" varchar(250) DEFAULT NULL, "ispublic" int(10) unsigned NOT NULL DEFAULT '0', "sortsequence" int(10) unsigned NOT NULL DEFAULT '50', "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("surveyid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "specprocessorprojects" -- DROP TABLE IF EXISTS "specprocessorprojects"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "specprocessorprojects" ( "spprid" int(10) unsigned NOT NULL, "collid" int(10) unsigned NOT NULL, "title" varchar(100) NOT NULL, "specKeyPattern" varchar(45) DEFAULT NULL, "speckeyretrieval" varchar(45) DEFAULT NULL, "coordX1" int(10) unsigned DEFAULT NULL, "coordX2" int(10) unsigned DEFAULT NULL, "coordY1" int(10) unsigned DEFAULT NULL, "coordY2" int(10) unsigned DEFAULT NULL, "sourcePath" varchar(250) DEFAULT NULL, "targetPath" varchar(250) DEFAULT NULL, "imgUrl" varchar(250) DEFAULT NULL, "webPixWidth" int(10) unsigned DEFAULT '1200', "tnPixWidth" int(10) unsigned DEFAULT '130', "lgPixWidth" int(10) unsigned DEFAULT '2400', "jpgcompression" int(11) DEFAULT '70', "createTnImg" int(10) unsigned DEFAULT '1', "createLgImg" int(10) unsigned DEFAULT '1', "initialTimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("spprid"), KEY "FK_specprocessorprojects_coll" ("collid"), CONSTRAINT "FK_specprocessorprojects_coll" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "specprocessorrawlabels" -- DROP TABLE IF EXISTS "specprocessorrawlabels"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "specprocessorrawlabels" ( "prlid" int(10) unsigned NOT NULL, "imgid" int(10) unsigned DEFAULT NULL, "occid" int(10) unsigned DEFAULT NULL, "rawstr" text NOT NULL, "processingvariables" varchar(250) DEFAULT NULL, "score" int(11) DEFAULT NULL, "notes" varchar(255) DEFAULT NULL, "source" varchar(150) DEFAULT NULL, "sortsequence" int(11) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("prlid"), KEY "FK_specproc_images" ("imgid"), KEY "FK_specproc_occid" ("occid"), CONSTRAINT "FK_specproc_images" FOREIGN KEY ("imgid") REFERENCES "images" ("imgid") ON UPDATE CASCADE, CONSTRAINT "FK_specproc_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "specprocnlp" -- DROP TABLE IF EXISTS "specprocnlp"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "specprocnlp" ( "spnlpid" int(10) NOT NULL, "title" varchar(45) NOT NULL, "sqlfrag" varchar(250) NOT NULL, "patternmatch" varchar(250) DEFAULT NULL, "notes" varchar(250) DEFAULT NULL, "collid" int(10) unsigned NOT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("spnlpid"), KEY "FK_specprocnlp_collid" ("collid"), CONSTRAINT "FK_specprocnlp_collid" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "specprocnlpfrag" -- DROP TABLE IF EXISTS "specprocnlpfrag"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "specprocnlpfrag" ( "spnlpfragid" int(10) NOT NULL, "spnlpid" int(10) NOT NULL, "fieldname" varchar(45) NOT NULL, "patternmatch" varchar(250) NOT NULL, "notes" varchar(250) DEFAULT NULL, "sortseq" int(5) DEFAULT '50', "initialtimestamp" timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("spnlpfragid"), KEY "FK_specprocnlpfrag_spnlpid" ("spnlpid"), CONSTRAINT "FK_specprocnlpfrag_spnlpid" FOREIGN KEY ("spnlpid") REFERENCES "specprocnlp" ("spnlpid") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "taxa" -- DROP TABLE IF EXISTS "taxa"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "taxa" ( "TID" int(10) unsigned NOT NULL, "KingdomID" tinyint(3) unsigned NOT NULL DEFAULT '3', "RankId" smallint(5) unsigned NOT NULL DEFAULT '220', "SciName" varchar(250) NOT NULL, "UnitInd1" varchar(1) DEFAULT NULL, "UnitName1" varchar(50) NOT NULL, "UnitInd2" varchar(1) DEFAULT NULL, "UnitName2" varchar(50) DEFAULT NULL, "UnitInd3" varchar(7) DEFAULT NULL, "UnitName3" varchar(35) DEFAULT NULL, "Author" varchar(100) DEFAULT NULL, "PhyloSortSequence" tinyint(3) unsigned DEFAULT NULL, "InitialTimeStamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, "Status" varchar(50) DEFAULT NULL, "Source" varchar(250) DEFAULT NULL, "Notes" varchar(250) DEFAULT NULL, "Hybrid" varchar(50) DEFAULT NULL, "fnaprikey" int(10) unsigned DEFAULT NULL COMMENT 'Primary key for FNA project', "UsdaSymbol" varchar(50) DEFAULT NULL, "SecurityStatus" int(10) unsigned NOT NULL DEFAULT '0' COMMENT '0 = no security; 1 = hidden locality', PRIMARY KEY ("TID"), UNIQUE KEY "sciname_unique" ("SciName"), KEY "rankid_index" ("RankId"), KEY "unitname1_index" ("UnitName1","UnitName2"), KEY "FK_taxa_taxonunits" ("KingdomID","RankId"), CONSTRAINT "taxa_ibfk_1" FOREIGN KEY ("KingdomID", "RankId") REFERENCES "taxonunits" ("kingdomid", "rankid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "taxadescrblock" -- DROP TABLE IF EXISTS "taxadescrblock"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "taxadescrblock" ( "tdbid" int(10) unsigned NOT NULL, "tid" int(10) unsigned NOT NULL, "caption" varchar(30) DEFAULT NULL, "source" varchar(250) DEFAULT NULL, "sourceurl" varchar(250) DEFAULT NULL, "language" varchar(45) DEFAULT 'English', "displaylevel" int(10) unsigned NOT NULL DEFAULT '1' COMMENT '1 = short descr, 2 = intermediate descr', "uid" int(10) unsigned NOT NULL, "notes" varchar(250) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("tdbid"), UNIQUE KEY "Index_unique" ("tid","displaylevel","language"), CONSTRAINT "taxadescrblock_ibfk_1" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "taxadescrstmts" -- DROP TABLE IF EXISTS "taxadescrstmts"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "taxadescrstmts" ( "tdsid" int(10) unsigned NOT NULL, "tdbid" int(10) unsigned NOT NULL, "heading" varchar(75) NOT NULL, "statement" text NOT NULL, "displayheader" int(10) unsigned NOT NULL DEFAULT '1', "notes" varchar(250) DEFAULT NULL, "sortsequence" int(10) unsigned NOT NULL DEFAULT '89', "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("tdsid"), KEY "FK_taxadescrstmts_tblock" ("tdbid"), CONSTRAINT "taxadescrstmts_ibfk_1" FOREIGN KEY ("tdbid") REFERENCES "taxadescrblock" ("tdbid") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "taxaenumtree" -- DROP TABLE IF EXISTS "taxaenumtree"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "taxaenumtree" ( "tid" int(10) unsigned NOT NULL, "taxauthid" int(10) unsigned NOT NULL, "parenttid" int(10) unsigned NOT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("tid","taxauthid","parenttid"), KEY "FK_tet_taxa" ("tid"), KEY "FK_tet_taxauth" ("taxauthid"), KEY "FK_tet_taxa2" ("parenttid"), CONSTRAINT "FK_tet_taxa" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID") ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT "FK_tet_taxa2" FOREIGN KEY ("parenttid") REFERENCES "taxa" ("TID") ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT "FK_tet_taxauth" FOREIGN KEY ("taxauthid") REFERENCES "taxauthority" ("taxauthid") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "taxalinks" -- DROP TABLE IF EXISTS "taxalinks"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "taxalinks" ( "tlid" int(10) unsigned NOT NULL, "tid" int(10) unsigned NOT NULL, "url" varchar(500) NOT NULL, "title" varchar(100) NOT NULL, "sourceIdentifier" varchar(45) DEFAULT NULL, "owner" varchar(100) DEFAULT NULL, "icon" varchar(45) DEFAULT NULL, "notes" varchar(250) DEFAULT NULL, "sortsequence" int(10) unsigned NOT NULL DEFAULT '50', "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("tlid"), KEY "Index_unique" ("tid","url"), CONSTRAINT "taxalinks_ibfk_1" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "taxamapparams" -- DROP TABLE IF EXISTS "taxamapparams"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "taxamapparams" ( "dmid" int(10) unsigned NOT NULL, "name" varchar(45) NOT NULL, "maptype" varchar(45) NOT NULL COMMENT 'custom; google dynamic', "regionofinterest" varchar(45) DEFAULT NULL, "basefilepath" varchar(250) DEFAULT NULL, "maptargetpath" varchar(250) NOT NULL, "mapurlpath" varchar(250) NOT NULL, "latnorth" double DEFAULT NULL, "latsouth" double DEFAULT NULL, "longwest" double DEFAULT NULL, "longeast" double DEFAULT NULL, "pointsize" int(10) unsigned DEFAULT NULL, "red" int(10) unsigned DEFAULT NULL, "green" int(10) unsigned DEFAULT NULL, "blue" int(10) unsigned DEFAULT NULL, "latadjustfactor" double NOT NULL DEFAULT '0', "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("dmid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "taxamaps" -- DROP TABLE IF EXISTS "taxamaps"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "taxamaps" ( "mid" int(10) unsigned NOT NULL, "tid" int(10) unsigned NOT NULL, "url" varchar(255) NOT NULL, "title" varchar(100) DEFAULT NULL, "dmid" int(10) unsigned DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("mid"), UNIQUE KEY "Index_unique" ("tid","dmid"), KEY "FK_taxamaps_dmid" ("dmid"), CONSTRAINT "taxamaps_ibfk_1" FOREIGN KEY ("dmid") REFERENCES "taxamapparams" ("dmid") ON UPDATE CASCADE, CONSTRAINT "taxamaps_ibfk_2" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "taxanestedtree" -- DROP TABLE IF EXISTS "taxanestedtree"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "taxanestedtree" ( "tid" int(10) unsigned NOT NULL, "taxauthid" int(10) unsigned NOT NULL, "leftindex" int(10) unsigned NOT NULL, "rightindex" int(10) unsigned NOT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("tid","taxauthid"), KEY "leftindex" ("leftindex"), KEY "rightindex" ("rightindex"), KEY "FK_tnt_taxa" ("tid"), KEY "FK_tnt_taxauth" ("taxauthid"), CONSTRAINT "FK_tnt_taxa" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID") ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT "FK_tnt_taxauth" FOREIGN KEY ("taxauthid") REFERENCES "taxauthority" ("taxauthid") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "taxauthority" -- DROP TABLE IF EXISTS "taxauthority"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "taxauthority" ( "taxauthid" int(10) unsigned NOT NULL, "isprimary" int(1) unsigned NOT NULL DEFAULT '0', "name" varchar(45) NOT NULL, "description" varchar(250) DEFAULT NULL, "editors" varchar(150) DEFAULT NULL, "contact" varchar(45) DEFAULT NULL, "email" varchar(100) DEFAULT NULL, "notes" varchar(250) DEFAULT NULL, "isactive" int(1) unsigned NOT NULL DEFAULT '1', "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("taxauthid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "taxavernaculars" -- DROP TABLE IF EXISTS "taxavernaculars"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "taxavernaculars" ( "TID" int(10) unsigned NOT NULL DEFAULT '0', "VernacularName" varchar(80) CHARACTER SET utf8 NOT NULL, "Language" varchar(15) CHARACTER SET utf8 NOT NULL DEFAULT 'English', "Source" varchar(50) CHARACTER SET utf8 DEFAULT NULL, "notes" varchar(250) CHARACTER SET utf8 DEFAULT NULL, "username" varchar(45) CHARACTER SET utf8 DEFAULT NULL, "isupperterm" int(2) DEFAULT '0', "SortSequence" int(10) DEFAULT '50', "VID" int(10) NOT NULL, "InitialTimeStamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("VID"), UNIQUE KEY "unique-key" ("Language","VernacularName","TID"), KEY "tid1" ("TID"), KEY "vernacularsnames" ("VernacularName"), CONSTRAINT "taxavernaculars_ibfk_1" FOREIGN KEY ("TID") REFERENCES "taxa" ("TID") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "taxonunits" -- DROP TABLE IF EXISTS "taxonunits"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "taxonunits" ( "kingdomid" tinyint(3) unsigned NOT NULL, "rankid" smallint(5) unsigned NOT NULL, "rankname" varchar(15) NOT NULL, "suffix" varchar(45) DEFAULT NULL, "dirparentrankid" smallint(6) NOT NULL, "reqparentrankid" smallint(6) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("kingdomid","rankid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "taxstatus" -- DROP TABLE IF EXISTS "taxstatus"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "taxstatus" ( "tid" int(10) unsigned NOT NULL, "tidaccepted" int(10) unsigned NOT NULL, "taxauthid" int(10) unsigned NOT NULL COMMENT 'taxon authority id', "parenttid" int(10) unsigned DEFAULT NULL, "hierarchystr" varchar(200) DEFAULT NULL, "uppertaxonomy" varchar(50) DEFAULT NULL, "family" varchar(50) DEFAULT NULL, "UnacceptabilityReason" varchar(45) DEFAULT NULL, "notes" varchar(250) DEFAULT NULL, "SortSequence" int(10) unsigned DEFAULT '50', "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("tid","tidaccepted","taxauthid"), KEY "FK_taxstatus_tidacc" ("tidaccepted"), KEY "FK_taxstatus_taid" ("taxauthid"), KEY "Index_ts_family" ("family"), KEY "Index_ts_upper" ("uppertaxonomy"), KEY "Index_parenttid" ("parenttid"), KEY "Index_hierarchy" ("hierarchystr"), CONSTRAINT "taxstatus_ibfk_1" FOREIGN KEY ("parenttid") REFERENCES "taxa" ("TID"), CONSTRAINT "taxstatus_ibfk_2" FOREIGN KEY ("taxauthid") REFERENCES "taxauthority" ("taxauthid") ON UPDATE CASCADE, CONSTRAINT "taxstatus_ibfk_3" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID"), CONSTRAINT "taxstatus_ibfk_4" FOREIGN KEY ("tidaccepted") REFERENCES "taxa" ("TID") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "temp_NPS_Legacy_Results" -- DROP TABLE IF EXISTS "temp_NPS_Legacy_Results"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "temp_NPS_Legacy_Results" ( "CatNum" varchar(32) DEFAULT NULL, "Family" varchar(255) DEFAULT NULL, "ARIZ_Taxon_name" varchar(255) DEFAULT NULL, "itis_taxon_name" varchar(250) DEFAULT NULL, "TSN" varchar(255) DEFAULT NULL, "Determiner" varchar(255) DEFAULT NULL, "DateDetermined" varchar(45) DEFAULT NULL, "SpecNotes" text COMMENT 'General Notes', "PlantDesc" text COMMENT 'Plant Description?', "Habitat" text COMMENT 'Habitat, substrait, etc', "AssocSpp" text COMMENT 'Associated Species', "FirstColl" varchar(255) DEFAULT NULL COMMENT 'Collector(s)', "CollNum" varchar(45) DEFAULT NULL COMMENT 'Collector Number', "DateColl" date DEFAULT NULL, "Country" varchar(64) DEFAULT NULL, "State" varchar(255) DEFAULT NULL, "County" varchar(255) DEFAULT NULL, "Locality" text, "OtherCords" varchar(255) DEFAULT NULL, "Datum" varchar(255) DEFAULT NULL, "LatDec" double DEFAULT NULL, "LongDec" double DEFAULT NULL, "Elev" varchar(255) DEFAULT NULL, "Project" varchar(50) DEFAULT NULL, "Cataloger" varchar(250) DEFAULT NULL, "DateModified" timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', "occid" int(10) unsigned NOT NULL DEFAULT '0' ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "temp_tbl_taxa_tsn" -- DROP TABLE IF EXISTS "temp_tbl_taxa_tsn"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "temp_tbl_taxa_tsn" ( "taxa_id" int(10) unsigned NOT NULL DEFAULT '0', "family" varchar(35) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "unit_ind1" varchar(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "unit_name1" varchar(35) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "unit_ind2" varchar(1) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "unit_name2" varchar(35) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "author" varchar(100) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "unit_ind3" varchar(7) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "unit_name3" varchar(35) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "infrasp_author" varchar(100) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "unit_ind4" varchar(7) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "unit_name4" varchar(35) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "infrasp_author2" varchar(100) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "az_itis" int(10) unsigned DEFAULT NULL, "status" varchar(50) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "acronym" varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "security" int(5) DEFAULT NULL, "notes" varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "security_source" varchar(255) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "old_taxa_id" int(10) unsigned DEFAULT NULL, "scientific_name" varchar(200) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL, "taxonomic_group" varchar(150) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL, "date_created" timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', "rank" varchar(6) CHARACTER SET latin1 DEFAULT NULL, "is_accepted" int(1) unsigned DEFAULT NULL, "entered_by" varchar(60) CHARACTER SET latin1 DEFAULT NULL, "type_publication_id" int(10) unsigned DEFAULT NULL, "ariz_usda" varchar(50) CHARACTER SET latin1 DEFAULT NULL, "ariz_tsn" varchar(255) CHARACTER SET latin1 DEFAULT NULL, "itis_sciname" varchar(250) CHARACTER SET latin1 DEFAULT NULL, "itis_usda" varchar(50) CHARACTER SET latin1 DEFAULT NULL, "itis_tsn" varchar(255) CHARACTER SET latin1 DEFAULT NULL, KEY "taxa_id" ("taxa_id"), KEY "family" ("family"), KEY "scientific_name" ("scientific_name"), KEY "itis_sciname" ("itis_sciname"), KEY "itis_tsn" ("itis_tsn") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "test_duplicates" -- DROP TABLE IF EXISTS "test_duplicates"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "test_duplicates" ( "Accession" varchar(32) DEFAULT NULL, "Duplicates" bigint(21) NOT NULL DEFAULT '0' ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "unknowncomments" -- DROP TABLE IF EXISTS "unknowncomments"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "unknowncomments" ( "unkcomid" int(10) unsigned NOT NULL, "unkid" int(10) unsigned NOT NULL, "comment" varchar(500) NOT NULL, "username" varchar(45) NOT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("unkcomid"), KEY "FK_unknowncomments" ("unkid"), CONSTRAINT "FK_unknowncomments" FOREIGN KEY ("unkid") REFERENCES "unknowns" ("unkid") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "unknownimages" -- DROP TABLE IF EXISTS "unknownimages"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "unknownimages" ( "unkimgid" int(10) unsigned NOT NULL, "unkid" int(10) unsigned NOT NULL, "url" varchar(255) NOT NULL, "notes" varchar(250) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("unkimgid"), KEY "FK_unknowns" ("unkid"), CONSTRAINT "FK_unknowns" FOREIGN KEY ("unkid") REFERENCES "unknowns" ("unkid") ON DELETE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "unknowns" -- DROP TABLE IF EXISTS "unknowns"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "unknowns" ( "unkid" int(10) unsigned NOT NULL, "tid" int(10) unsigned DEFAULT NULL, "photographer" varchar(100) DEFAULT NULL, "owner" varchar(100) DEFAULT NULL, "locality" varchar(250) DEFAULT NULL, "latdecimal" double DEFAULT NULL, "longdecimal" double DEFAULT NULL, "notes" varchar(250) DEFAULT NULL, "username" varchar(45) NOT NULL, "idstatus" varchar(45) NOT NULL DEFAULT 'ID pending', "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("unkid"), KEY "FK_unknowns_username" ("username"), KEY "FK_unknowns_tid" ("tid"), CONSTRAINT "FK_unknowns_tid" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID"), CONSTRAINT "FK_unknowns_username" FOREIGN KEY ("username") REFERENCES "userlogin" ("username") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "uploadimagetemp" -- DROP TABLE IF EXISTS "uploadimagetemp"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "uploadimagetemp" ( "tid" int(10) unsigned DEFAULT NULL, "url" varchar(255) NOT NULL, "thumbnailurl" varchar(255) DEFAULT NULL, "originalurl" varchar(255) DEFAULT NULL, "photographer" varchar(100) DEFAULT NULL, "photographeruid" int(10) unsigned DEFAULT NULL, "imagetype" varchar(50) DEFAULT NULL, "caption" varchar(100) DEFAULT NULL, "owner" varchar(100) DEFAULT NULL, "occid" int(10) unsigned DEFAULT NULL, "collid" int(10) unsigned DEFAULT NULL, "dbpk" varchar(45) DEFAULT NULL, "specimengui" varchar(45) DEFAULT NULL, "notes" varchar(255) DEFAULT NULL, "username" varchar(45) DEFAULT NULL, "sortsequence" int(10) unsigned DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "uploadspecmap" -- DROP TABLE IF EXISTS "uploadspecmap"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "uploadspecmap" ( "usmid" int(10) unsigned NOT NULL, "uspid" int(10) unsigned NOT NULL, "sourcefield" varchar(45) NOT NULL, "symbdatatype" varchar(45) NOT NULL DEFAULT 'string' COMMENT 'string, numeric, datetime', "symbspecfield" varchar(45) NOT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("usmid"), UNIQUE KEY "Index_unique" ("uspid","symbspecfield"), CONSTRAINT "FK_uploadspecmap_usp" FOREIGN KEY ("uspid") REFERENCES "uploadspecparameters" ("uspid") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "uploadspecparameters" -- DROP TABLE IF EXISTS "uploadspecparameters"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "uploadspecparameters" ( "uspid" int(10) unsigned NOT NULL, "CollID" int(10) unsigned NOT NULL, "UploadType" int(10) unsigned NOT NULL DEFAULT '1' COMMENT '1 = Direct; 2 = DiGIR; 3 = File', "title" varchar(45) NOT NULL, "Platform" varchar(45) DEFAULT '1' COMMENT '1 = MySQL; 2 = MSSQL; 3 = ORACLE; 11 = MS Access; 12 = FileMaker', "server" varchar(150) DEFAULT NULL, "port" int(10) unsigned DEFAULT NULL, "driver" varchar(45) DEFAULT NULL, "DigirCode" varchar(45) DEFAULT NULL, "DigirPath" varchar(150) DEFAULT NULL, "DigirPKField" varchar(45) DEFAULT NULL, "Username" varchar(45) DEFAULT NULL, "Password" varchar(45) DEFAULT NULL, "SchemaName" varchar(150) DEFAULT NULL, "QueryStr" varchar(2000) DEFAULT NULL, "cleanupsp" varchar(45) DEFAULT NULL, "dlmisvalid" int(10) unsigned DEFAULT '0', "InitialTimeStamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("uspid"), KEY "FK_uploadspecparameters_coll" ("CollID"), CONSTRAINT "FK_uploadspecparameters_coll" FOREIGN KEY ("CollID") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "uploadspectemp" -- DROP TABLE IF EXISTS "uploadspectemp"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "uploadspectemp" ( "collid" int(10) unsigned NOT NULL, "dbpk" varchar(45) DEFAULT NULL, "occid" int(10) unsigned DEFAULT NULL, "basisOfRecord" varchar(32) DEFAULT 'PreservedSpecimen' COMMENT 'PreservedSpecimen, LivingSpecimen, HumanObservation', "occurrenceID" varchar(255) DEFAULT NULL COMMENT 'UniqueGlobalIdentifier', "catalogNumber" varchar(32) DEFAULT NULL, "otherCatalogNumbers" varchar(255) DEFAULT NULL, "ownerInstitutionCode" varchar(32) DEFAULT NULL, "institutionID" varchar(255) DEFAULT NULL, "collectionID" varchar(255) DEFAULT NULL, "institutionCode" varchar(64) DEFAULT NULL, "collectionCode" varchar(64) DEFAULT NULL, "datasetID" varchar(255) DEFAULT NULL, "family" varchar(255) DEFAULT NULL, "scientificName" varchar(255) DEFAULT NULL, "sciname" varchar(255) DEFAULT NULL, "tidinterpreted" int(10) unsigned DEFAULT NULL, "genus" varchar(255) DEFAULT NULL, "specificEpithet" varchar(255) DEFAULT NULL, "taxonRank" varchar(32) DEFAULT NULL, "infraspecificEpithet" varchar(255) DEFAULT NULL, "scientificNameAuthorship" varchar(255) DEFAULT NULL, "taxonRemarks" text, "identifiedBy" varchar(255) DEFAULT NULL, "dateIdentified" varchar(45) DEFAULT NULL, "identificationReferences" text, "identificationRemarks" text, "identificationQualifier" varchar(255) DEFAULT NULL COMMENT 'cf, aff, etc', "typeStatus" varchar(255) DEFAULT NULL, "recordedBy" varchar(255) DEFAULT NULL COMMENT 'Collector(s)', "recordNumber" varchar(32) DEFAULT NULL COMMENT 'Collector Number', "CollectorFamilyName" varchar(255) DEFAULT NULL COMMENT 'not DwC', "CollectorInitials" varchar(255) DEFAULT NULL COMMENT 'not DwC', "associatedCollectors" varchar(255) DEFAULT NULL COMMENT 'not DwC', "eventDate" date DEFAULT NULL, "year" int(10) DEFAULT NULL, "month" int(10) DEFAULT NULL, "day" int(10) DEFAULT NULL, "startDayOfYear" int(10) DEFAULT NULL, "endDayOfYear" int(10) DEFAULT NULL, "LatestDateCollected" date DEFAULT NULL, "verbatimEventDate" varchar(255) DEFAULT NULL, "habitat" text COMMENT 'Habitat, substrait, etc', "substrate" varchar(500) DEFAULT NULL, "fieldNotes" text, "fieldnumber" varchar(45) DEFAULT NULL, "occurrenceRemarks" text COMMENT 'General Notes', "informationWithheld" varchar(250) DEFAULT NULL, "dataGeneralizations" varchar(250) DEFAULT NULL, "associatedOccurrences" text, "associatedTaxa" text COMMENT 'Associated Species', "dynamicProperties" text COMMENT 'Plant Description?', "verbatimAttributes" text, "attributes" text, "reproductiveCondition" varchar(255) DEFAULT NULL COMMENT 'Phenology: flowers, fruit, sterile', "cultivationStatus" int(10) DEFAULT NULL COMMENT '0 = wild, 1 = cultivated', "establishmentMeans" varchar(32) DEFAULT NULL COMMENT 'cultivated, invasive, escaped from captivity, wild, native', "lifeStage" varchar(45) DEFAULT NULL, "sex" varchar(45) DEFAULT NULL, "individualCount" varchar(45) DEFAULT NULL, "samplingProtocol" varchar(100) DEFAULT NULL, "preparations" varchar(100) DEFAULT NULL, "country" varchar(64) DEFAULT NULL, "stateProvince" varchar(255) DEFAULT NULL, "county" varchar(255) DEFAULT NULL, "municipality" varchar(255) DEFAULT NULL, "locality" text, "localitySecurity" int(10) DEFAULT '0' COMMENT '0 = display locality, 1 = hide locality', "localitySecurityReason" varchar(100) DEFAULT NULL, "decimalLatitude" double DEFAULT NULL, "decimalLongitude" double DEFAULT NULL, "geodeticDatum" varchar(255) DEFAULT NULL, "coordinateUncertaintyInMeters" int(10) unsigned DEFAULT NULL, "footprintWKT" text, "coordinatePrecision" decimal(9,7) DEFAULT NULL, "locationRemarks" text, "verbatimCoordinates" varchar(255) DEFAULT NULL, "verbatimCoordinateSystem" varchar(255) DEFAULT NULL, "latDeg" int(11) DEFAULT NULL, "latMin" double DEFAULT NULL, "latSec" double DEFAULT NULL, "latNS" varchar(3) DEFAULT NULL, "lngDeg" int(11) DEFAULT NULL, "lngMin" double DEFAULT NULL, "lngSec" double DEFAULT NULL, "lngEW" varchar(3) DEFAULT NULL, "UtmNorthing" varchar(45) DEFAULT NULL, "UtmEasting" varchar(45) DEFAULT NULL, "UtmZoning" varchar(45) DEFAULT NULL, "georeferencedBy" varchar(255) DEFAULT NULL, "georeferenceProtocol" varchar(255) DEFAULT NULL, "georeferenceSources" varchar(255) DEFAULT NULL, "georeferenceVerificationStatus" varchar(32) DEFAULT NULL, "georeferenceRemarks" varchar(255) DEFAULT NULL, "minimumElevationInMeters" int(6) DEFAULT NULL, "maximumElevationInMeters" int(6) DEFAULT NULL, "verbatimElevation" varchar(255) DEFAULT NULL, "previousIdentifications" text, "disposition" varchar(32) DEFAULT NULL COMMENT 'Dups to', "genericcolumn1" varchar(100) DEFAULT NULL, "genericcolumn2" varchar(100) DEFAULT NULL, "modified" datetime DEFAULT NULL COMMENT 'DateLastModified', "language" varchar(2) DEFAULT NULL, "recordEnteredBy" varchar(250) DEFAULT NULL, "duplicateQuantity" int(10) unsigned DEFAULT NULL, "labelProject" varchar(45) DEFAULT NULL, "tempfield01" text, "tempfield02" text, "tempfield03" text, "tempfield04" text, "tempfield05" text, "tempfield06" text, "tempfield07" text, "tempfield08" text, "tempfield09" text, "tempfield10" text, "tempfield11" text, "tempfield12" text, "tempfield13" text, "tempfield14" text, "tempfield15" text, "initialTimestamp" timestamp NULL DEFAULT CURRENT_TIMESTAMP, KEY "FK_uploadspectemp_coll" ("collid"), KEY "Index_uploadspectemp_occid" ("occid"), KEY "Index_uploadspectemp_dbpk" ("dbpk"), CONSTRAINT "FK_uploadspectemp_coll" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "uploadtaxa" -- DROP TABLE IF EXISTS "uploadtaxa"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "uploadtaxa" ( "TID" int(10) unsigned DEFAULT NULL, "SourceId" int(10) unsigned DEFAULT NULL, "KingdomID" tinyint(3) unsigned DEFAULT '3', "UpperTaxonomy" varchar(50) DEFAULT NULL, "Family" varchar(50) DEFAULT NULL, "RankId" smallint(5) DEFAULT NULL, "scinameinput" varchar(250) NOT NULL, "SciName" varchar(250) DEFAULT NULL, "UnitInd1" varchar(1) DEFAULT NULL, "UnitName1" varchar(50) DEFAULT NULL, "UnitInd2" varchar(1) DEFAULT NULL, "UnitName2" varchar(50) DEFAULT NULL, "UnitInd3" varchar(7) DEFAULT NULL, "UnitName3" varchar(35) DEFAULT NULL, "Author" varchar(100) DEFAULT NULL, "Acceptance" int(10) unsigned DEFAULT '1' COMMENT '0 = not accepted; 1 = accepted', "TidAccepted" int(10) unsigned DEFAULT NULL, "AcceptedStr" varchar(250) DEFAULT NULL, "SourceAcceptedId" int(10) unsigned DEFAULT NULL, "UnacceptabilityReason" varchar(24) DEFAULT NULL, "ParentTid" int(10) DEFAULT NULL, "ParentStr" varchar(250) DEFAULT NULL, "SourceParentId" int(10) unsigned DEFAULT NULL, "SecurityStatus" int(10) unsigned NOT NULL DEFAULT '0' COMMENT '0 = no security; 1 = hidden locality', "Source" varchar(250) DEFAULT NULL, "Notes" varchar(250) DEFAULT NULL, "vernacular" varchar(80) DEFAULT NULL, "vernlang" varchar(15) DEFAULT NULL, "Hybrid" varchar(50) DEFAULT NULL, "InitialTimeStamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("scinameinput"), UNIQUE KEY "sciname_index" ("SciName"), KEY "sourceID_index" ("SourceId"), KEY "sourceAcceptedId_index" ("SourceAcceptedId") ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "userlogin" -- DROP TABLE IF EXISTS "userlogin"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "userlogin" ( "uid" int(10) unsigned NOT NULL, "username" varchar(45) NOT NULL, "password" varchar(45) NOT NULL, "alias" varchar(45) DEFAULT NULL, "lastlogindate" datetime DEFAULT NULL, "InitialTimeStamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("username"), UNIQUE KEY "Index_userlogin_unique" ("alias"), KEY "FK_login_user" ("uid"), CONSTRAINT "userlogin_ibfk_1" FOREIGN KEY ("uid") REFERENCES "users" ("uid") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "userpermissions" -- DROP TABLE IF EXISTS "userpermissions"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "userpermissions" ( "uid" int(10) unsigned NOT NULL, "pname" varchar(45) NOT NULL COMMENT 'SuperAdmin, TaxonProfile, IdentKey, RareSpecies, coll-1, cl-1, proj-1', "assignedby" varchar(45) DEFAULT NULL, "initialtimestamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("uid","pname"), CONSTRAINT "userpermissions_ibfk_1" FOREIGN KEY ("uid") REFERENCES "users" ("uid") ON DELETE CASCADE ON UPDATE CASCADE ); /*!40101 SET character_set_client = @saved_cs_client */; -- -- Table structure for table "users" -- DROP TABLE IF EXISTS "users"; /*!40101 SET @saved_cs_client = @@character_set_client */; /*!40101 SET character_set_client = utf8 */; CREATE TABLE "users" ( "uid" int(10) unsigned NOT NULL, "firstname" varchar(45) DEFAULT NULL, "lastname" varchar(45) NOT NULL, "title" varchar(150) DEFAULT NULL, "institution" varchar(200) DEFAULT NULL, "department" varchar(200) DEFAULT NULL, "address" varchar(255) DEFAULT NULL, "city" varchar(100) DEFAULT NULL, "state" varchar(50) NOT NULL, "zip" varchar(15) DEFAULT NULL, "country" varchar(50) NOT NULL, "phone" varchar(45) DEFAULT NULL, "email" varchar(100) NOT NULL, "RegionOfInterest" varchar(45) DEFAULT NULL, "url" varchar(400) DEFAULT NULL, "Biography" varchar(1500) DEFAULT NULL, "notes" varchar(255) DEFAULT NULL, "ispublic" int(10) unsigned NOT NULL DEFAULT '0', "defaultrights" varchar(250) DEFAULT NULL, "rightsholder" varchar(250) DEFAULT NULL, "validated" varchar(45) NOT NULL DEFAULT '0', "usergroups" varchar(100) DEFAULT NULL, "InitialTimeStamp" timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("uid"), UNIQUE KEY "Index_email" ("email","lastname") ); /*!40101 SET character_set_client = @saved_cs_client */; /*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */; /*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */; -- Dump completed on 2013-02-14 3:07:06