SET standard_conforming_strings = off; SET escape_string_warning = off; -- 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 */; 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" integer NOT NULL DEFAULT '0', "accession_number" integer DEFAULT NULL, "taxa_id" integer DEFAULT NULL, "cf" varchar(10) DEFAULT NULL, "determiner_annotator" varchar(100) DEFAULT NULL, "initial_taxonomy" varchar(200) DEFAULT NULL, "specimen_notes" varchar(250) DEFAULT NULL, "plant_description" varchar(1000) DEFAULT NULL, "phenology" varchar(50) DEFAULT NULL, "chromosome_number" varchar(50) DEFAULT NULL, "habitat" varchar(600) DEFAULT NULL, "assoc_species" varchar(1300) DEFAULT NULL, "first_collector" varchar(50) DEFAULT NULL, "collnumprefix" varchar(10) DEFAULT NULL, "collnumber" integer DEFAULT NULL, "collnumsuffix" varchar(10) DEFAULT NULL, "collnumber_full" varchar(100) DEFAULT NULL, "other_collectors" varchar(200) DEFAULT NULL, "date_collected" text/*date*/ DEFAULT NULL, "month_collected" varchar(15) DEFAULT NULL, "day_collected" integer DEFAULT NULL, "year_collected" integer DEFAULT NULL, "country" varchar(50) DEFAULT NULL, "state_province" varchar(50) DEFAULT NULL, "county_parish" varchar(50) DEFAULT NULL, "national_forest" varchar(50) DEFAULT NULL, "locality" varchar(1000) DEFAULT NULL, "latdeg" integer DEFAULT NULL, "latmin" decimal(12,8) DEFAULT NULL, "latsec" decimal(12,8) DEFAULT NULL, "latns" varchar(2) DEFAULT NULL, "lat_decimal" decimal(12,8) DEFAULT NULL, "longdeg" integer DEFAULT NULL, "longmin" decimal(12,8) DEFAULT NULL, "longsec" decimal(12,8) DEFAULT NULL, "longew" varchar(2) DEFAULT NULL, "long_decimal" decimal(12,8) DEFAULT NULL, "utm_zoning" varchar(5) DEFAULT NULL, "utm_easting" integer DEFAULT NULL, "utm_northing" integer DEFAULT NULL, "datum" varchar(20) DEFAULT NULL, "coordinates_est" varchar(100) DEFAULT NULL, "bbounding" varchar(10) DEFAULT NULL, "township" varchar(50) DEFAULT NULL, "range" varchar(50) DEFAULT NULL, "section" varchar(50) DEFAULT NULL, "sec_details" varchar(50) DEFAULT NULL, "elevation_m" integer DEFAULT NULL, "elev_max_m" integer DEFAULT NULL, "elevation_ft" integer DEFAULT NULL, "elev_max_ft" integer DEFAULT NULL, "elev_est" varchar(20) DEFAULT NULL, "cultivated" varchar(10) DEFAULT NULL, "label_quantity" integer DEFAULT NULL, "project" varchar(50) DEFAULT NULL, "dups_to" varchar(180) DEFAULT NULL, "herb_acro" varchar(50) DEFAULT NULL, "datelastmodified" text/*datetime*/ NOT NULL, "entered_by" varchar(60) DEFAULT NULL, "download_source" varchar(50) DEFAULT NULL, "old_dbsn" integer DEFAULT NULL, "verified" varchar(50) DEFAULT NULL, "coord_err" integer DEFAULT NULL, "coord_confidence" integer DEFAULT NULL, "land_ownership" varchar(100) DEFAULT NULL, "coord_source" varchar(100) DEFAULT NULL, "georeferenceverificationstatus" varchar(150) DEFAULT NULL, "date_entered" text/*timestamp*/ NOT NULL DEFAULT '0000-00-00 00:00:00', "is_duplicate" integer DEFAULT NULL /*COMMENT '=1 if possible duplicate record; otherwise 0 or null'*/, "deleted" integer NOT NULL DEFAULT '0' /*COMMENT '=1 if deleted; otherwise 0'*/, "deleted_reason" varchar(250) DEFAULT NULL /*COMMENT 'Reason for flagging for deletion'*/, "determination_date" text/*date*/ DEFAULT NULL /*COMMENT 'Keep this field DATE not DATETIME'*/, "det_day" integer DEFAULT NULL, "det_mo" integer DEFAULT NULL, "det_yr" integer DEFAULT NULL, "deaccessioned" integer NOT NULL DEFAULT '0', "dupes" integer DEFAULT '1' /*COMMENT 'Total duplicates per specimen'*/, "sheets" integer DEFAULT '1' /*COMMENT 'Total sheets per specimen'*/, "collnum_numeric" double precision DEFAULT NULL /*COMMENT 'Artificial decimal value based on collnumprefix, collnumber & collnumsuffix; for natural sorting'*/, "needs_label" integer DEFAULT '0' /*COMMENT '1=Yes, needs label; 0=No, already labelled'*/, "is_arboretum" integer DEFAULT '0', "arboretum_id" integer DEFAULT NULL, "notes_display" integer DEFAULT '0', "elev_est_display" integer DEFAULT '0', "security" integer NOT NULL DEFAULT '1' /*COMMENT '1=no security, 2=hide locality info'*/, "temp_dbsn" integer 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" integer NOT NULL, "statement" varchar(300) NOT NULL, "nodeid" integer NOT NULL, "parentid" integer NOT NULL, "tid" integer DEFAULT NULL, "notes" varchar(250) DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("stmtid"), /*KEY "FK_chotomouskey_taxa" ("tid")*/CHECK (true), /*CONSTRAINT "FK_chotomouskey_taxa" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID")*/CHECK (true) ); /*!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" integer 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" integer DEFAULT NULL, "Notes" varchar(500) DEFAULT NULL, "LatCentroid" double precision DEFAULT NULL, "LongCentroid" double precision DEFAULT NULL, "pointradiusmeters" integer DEFAULT NULL, "footprintWKT" text, "percenteffort" integer DEFAULT NULL, "Access" varchar(45) DEFAULT 'private', "uid" integer DEFAULT NULL, "SortSequence" integer NOT NULL DEFAULT '50', "expiration" integer DEFAULT NULL, "DateLastModified" text/*datetime*/ DEFAULT NULL, "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("CLID"), /*KEY "FK_checklists_uid" ("uid")*/CHECK (true), /*KEY "name" ("Name","Type")*/CHECK (true), /*CONSTRAINT "FK_checklists_uid" FOREIGN KEY ("uid") REFERENCES "users" ("uid")*/CHECK (true) ); /*!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" integer NOT NULL, "clid" integer NOT NULL, "tid" integer NOT NULL, "decimallatitude" double precision NOT NULL, "decimallongitude" double precision NOT NULL, "notes" varchar(250) DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("chklstcoordid"), /*CONSTRAINT "IndexUnique" */UNIQUE ("clid","tid","decimallatitude","decimallongitude"), /*KEY "FKchklsttaxalink" ("clid","tid")*/CHECK (true), /*CONSTRAINT "FKchklsttaxalink" FOREIGN KEY ("clid", "tid") REFERENCES "fmchklsttaxalink" ("CLID", "TID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "clid" integer NOT NULL, "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("pid","clid"), /*KEY "FK_chklst" ("clid")*/CHECK (true), /*CONSTRAINT "FK_chklstprojlink_clid" FOREIGN KEY ("clid") REFERENCES "fmchecklists" ("CLID")*/CHECK (true), /*CONSTRAINT "FK_chklstprojlink_proj" FOREIGN KEY ("pid") REFERENCES "fmprojects" ("pid")*/CHECK (true) ); /*!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" integer NOT NULL DEFAULT '0', "CLID" integer 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" integer 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" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("TID","CLID","morphospecies"), /*KEY "FK_chklsttaxalink_cid" ("CLID")*/CHECK (true), /*CONSTRAINT "FK_chklsttaxalink_cid" FOREIGN KEY ("CLID") REFERENCES "fmchecklists" ("CLID")*/CHECK (true), /*CONSTRAINT "FK_chklsttaxalink_tid" FOREIGN KEY ("TID") REFERENCES "taxa" ("TID")*/CHECK (true) ); /*!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" integer NOT NULL, "clid" integer NOT NULL, "tid" integer NOT NULL, "comment" text NOT NULL, "uid" integer NOT NULL, "ispublic" integer NOT NULL DEFAULT '1', "parentid" integer DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("cltaxacommentsid"), /*KEY "FK_clcomment_users" ("uid")*/CHECK (true), /*KEY "FK_clcomment_cltaxa" ("clid","tid")*/CHECK (true), /*CONSTRAINT "FK_clcomment_cltaxa" FOREIGN KEY ("clid", "tid") REFERENCES "fmchklsttaxalink" ("CLID", "TID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true), /*CONSTRAINT "FK_clcomment_users" FOREIGN KEY ("uid") REFERENCES "users" ("uid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer 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" text/*datetime*/ NOT NULL, "initialtimestamp" text/*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" integer NOT NULL, "tid" integer NOT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("dynclid","tid"), /*KEY "FK_dyncltaxalink_taxa" ("tid")*/CHECK (true), /*CONSTRAINT "FK_dyncltaxalink_dynclid" FOREIGN KEY ("dynclid") REFERENCES "fmdynamicchecklists" ("dynclid") ON DELETE CASCADE*/CHECK (true), /*CONSTRAINT "FK_dyncltaxalink_taxa" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID") ON DELETE CASCADE*/CHECK (true) ); /*!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" integer 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" integer NOT NULL DEFAULT '0', "ispublic" integer NOT NULL DEFAULT '0', "parentpid" integer DEFAULT NULL, "SortSequence" integer NOT NULL DEFAULT '50', "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("pid"), /*KEY "FK_parentpid_proj" ("parentpid")*/CHECK (true), /*CONSTRAINT "FK_parentpid_proj" FOREIGN KEY ("parentpid") REFERENCES "fmprojects" ("pid") ON DELETE NO ACTION ON UPDATE NO ACTION*/CHECK (true) ); /*!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" integer NOT NULL, "CLID" integer NOT NULL, "occid" integer NOT NULL, "Collector" varchar(100) NOT NULL, "editornotes" varchar(50) DEFAULT NULL, "Notes" varchar(250) DEFAULT NULL, "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("occid","CLID","TID"), /*KEY "chklst_taxavouchers" ("TID","CLID")*/CHECK (true), /*CONSTRAINT "FK_fmvouchers_occ" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid")*/CHECK (true), /*CONSTRAINT "FK_vouchers_cl" FOREIGN KEY ("TID", "CLID") REFERENCES "fmchklsttaxalink" ("TID", "CLID") ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "continentterm" varchar(45) NOT NULL, "abbreviation" varchar(45) DEFAULT NULL, "code" varchar(45) DEFAULT NULL, "lookupterm" integer NOT NULL DEFAULT '1', "acceptedid" integer DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("gtcid"), /*KEY "FK_geothescontinent_accepted_idx" ("acceptedid")*/CHECK (true), /*CONSTRAINT "FK_geothescontinent_accepted" FOREIGN KEY ("acceptedid") REFERENCES "geothescontinent" ("gtcid")*/CHECK (true) ); /*!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" integer NOT NULL, "countryterm" varchar(45) NOT NULL, "abbreviation" varchar(45) DEFAULT NULL, "iso" varchar(2) DEFAULT NULL, "iso3" varchar(3) DEFAULT NULL, "numcode" integer DEFAULT NULL, "lookupterm" integer NOT NULL DEFAULT '1', "acceptedid" integer DEFAULT NULL, "continentid" integer DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("gtcid"), /*KEY "FK_geothescountry__idx" ("continentid")*/CHECK (true), /*KEY "FK_geothescountry_parent_idx" ("acceptedid")*/CHECK (true), /*CONSTRAINT "FK_geothescountry_accepted" FOREIGN KEY ("acceptedid") REFERENCES "geothescountry" ("gtcid")*/CHECK (true), /*CONSTRAINT "FK_geothescountry_gtcid" FOREIGN KEY ("continentid") REFERENCES "geothescontinent" ("gtcid")*/CHECK (true) ); /*!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" integer NOT NULL, "countyterm" varchar(45) NOT NULL, "abbreviation" varchar(45) DEFAULT NULL, "code" varchar(45) DEFAULT NULL, "lookupterm" integer NOT NULL DEFAULT '1', "acceptedid" integer DEFAULT NULL, "stateid" integer DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("gtcoid"), /*KEY "FK_geothescounty_state_idx" ("stateid")*/CHECK (true), /*KEY "FK_geothescounty_accepted_idx" ("acceptedid")*/CHECK (true), /*CONSTRAINT "FK_geothescounty_accepted" FOREIGN KEY ("acceptedid") REFERENCES "geothescounty" ("gtcoid")*/CHECK (true), /*CONSTRAINT "FK_geothescounty_state" FOREIGN KEY ("stateid") REFERENCES "geothesstateprovince" ("gtspid")*/CHECK (true) ); /*!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" integer NOT NULL, "municipalityterm" varchar(45) NOT NULL, "abbreviation" varchar(45) DEFAULT NULL, "code" varchar(45) DEFAULT NULL, "lookupterm" integer NOT NULL DEFAULT '1', "acceptedid" integer DEFAULT NULL, "countyid" integer DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("gtmid"), /*KEY "FK_geothesmunicipality_county_idx" ("countyid")*/CHECK (true), /*KEY "FK_geothesmunicipality_accepted_idx" ("acceptedid")*/CHECK (true), /*CONSTRAINT "FK_geothesmunicipality_accepted" FOREIGN KEY ("acceptedid") REFERENCES "geothescounty" ("gtcoid")*/CHECK (true), /*CONSTRAINT "FK_geothesmunicipality_county" FOREIGN KEY ("countyid") REFERENCES "geothescounty" ("gtcoid")*/CHECK (true) ); /*!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" integer NOT NULL, "stateterm" varchar(45) NOT NULL, "abbreviation" varchar(45) DEFAULT NULL, "code" varchar(45) DEFAULT NULL, "lookupterm" integer NOT NULL DEFAULT '1', "acceptedid" integer DEFAULT NULL, "countryid" integer DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("gtspid"), /*KEY "FK_geothesstate_country_idx" ("countryid")*/CHECK (true), /*KEY "FK_geothesstate_accepted_idx" ("acceptedid")*/CHECK (true), /*CONSTRAINT "FK_geothesstate_accepted" FOREIGN KEY ("acceptedid") REFERENCES "geothesstateprovince" ("gtspid")*/CHECK (true), /*CONSTRAINT "FK_geothesstate_country" FOREIGN KEY ("countryid") REFERENCES "geothescountry" ("gtcid")*/CHECK (true) ); /*!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" integer 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" integer DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("glossid"), /*CONSTRAINT "Index_term" */UNIQUE ("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" integer NOT NULL, "catagory" varchar(45) NOT NULL, "language" varchar(45) NOT NULL DEFAULT 'English', "initialtimestamp" text/*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" integer NOT NULL, "catid" integer NOT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("glossid","catid"), /*KEY "FK_glossarycatlink_cat" ("catid")*/CHECK (true), /*CONSTRAINT "glossarycatlink_ibfk_1" FOREIGN KEY ("catid") REFERENCES "glossarycatagories" ("catid")*/CHECK (true), /*CONSTRAINT "glossarycatlink_ibfk_2" FOREIGN KEY ("glossid") REFERENCES "glossary" ("glossid")*/CHECK (true) ); /*!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" integer NOT NULL, "glossid" integer NOT NULL, "url" varchar(45) NOT NULL, "structures" varchar(150) DEFAULT NULL, "notes" varchar(250) DEFAULT NULL, "uid" integer DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("glimgid"), /*KEY "FK_glossaryimages_gloss" ("glossid")*/CHECK (true), /*CONSTRAINT "glossaryimages_ibfk_1" FOREIGN KEY ("glossid") REFERENCES "glossary" ("glossid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer DEFAULT NULL, "imgid" integer NOT NULL DEFAULT '0', "AnnDate" text/*datetime*/ NOT NULL DEFAULT '0000-00-00 00:00:00', "Annotator" varchar(100) DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("imgid","AnnDate"), /*KEY "TID" ("tid")*/CHECK (true), /*CONSTRAINT "FK_resourceannotations_imgid" FOREIGN KEY ("imgid") REFERENCES "images" ("imgid")*/CHECK (true), /*CONSTRAINT "FK_resourceannotations_tid" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID")*/CHECK (true) ); /*!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" integer NOT NULL, "tid" integer DEFAULT NULL, "url" varchar(255) NOT NULL, "thumbnailurl" varchar(255) DEFAULT NULL, "originalurl" varchar(255) DEFAULT NULL, "photographer" varchar(100) DEFAULT NULL, "photographeruid" integer 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" integer DEFAULT NULL, "notes" varchar(350) DEFAULT NULL, "anatomy" varchar(100) DEFAULT NULL, "username" varchar(45) DEFAULT NULL, "sortsequence" integer NOT NULL DEFAULT '50', "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("imgid"), /*KEY "Index_tid" ("tid")*/CHECK (true), /*KEY "FK_images_occ" ("occid")*/CHECK (true), /*KEY "FK_photographeruid" ("photographeruid")*/CHECK (true), /*CONSTRAINT "FK_images_occ" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid")*/CHECK (true), /*CONSTRAINT "FK_photographeruid" FOREIGN KEY ("photographeruid") REFERENCES "users" ("uid") ON DELETE SET NULL ON UPDATE CASCADE*/CHECK (true), /*CONSTRAINT "FK_taxaimagestid" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID")*/CHECK (true) ); /*!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" integer 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" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("iid"), /*CONSTRAINT "Index_instcode" */UNIQUE ("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" integer NOT NULL, "charname" varchar(150) NOT NULL, "language" varchar(45) NOT NULL, "notes" varchar(255) DEFAULT NULL, "description" varchar(255) DEFAULT NULL, "helpurl" varchar(500) DEFAULT NULL, "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("cid","language"), /*CONSTRAINT "kmcharacterlang_ibfk_1" FOREIGN KEY ("cid") REFERENCES "kmcharacters" ("cid")*/CHECK (true) ); /*!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" integer NOT NULL, "charname" varchar(150) NOT NULL, "chartype" varchar(2) NOT NULL DEFAULT 'UM', "defaultlang" varchar(45) NOT NULL DEFAULT 'English', "difficultyrank" integer NOT NULL DEFAULT '1', "hid" integer 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" integer DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("cid"), /*KEY "Index_charname" ("charname")*/CHECK (true), /*KEY "Index_sort" ("sortsequence")*/CHECK (true) ); /*!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" integer NOT NULL, "CIDDependance" integer NOT NULL, "CSDependance" varchar(16) NOT NULL, "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("CSDependance","CIDDependance","CID"), /*KEY "FK_chardependance_cid" ("CID")*/CHECK (true), /*KEY "FK_chardependance_2" ("CIDDependance")*/CHECK (true), /*CONSTRAINT "kmchardependance_ibfk_1" FOREIGN KEY ("CID") REFERENCES "kmcharacters" ("cid")*/CHECK (true), /*CONSTRAINT "kmchardependance_ibfk_2" FOREIGN KEY ("CID") REFERENCES "kmcharacters" ("cid")*/CHECK (true) ); /*!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" integer NOT NULL, "headingname" varchar(255) NOT NULL, "language" varchar(45) NOT NULL DEFAULT 'English', "notes" text, "sortsequence" integer DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("hid","language"), /*KEY "HeadingName" ("headingname")*/CHECK (true) ); /*!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" integer NOT NULL DEFAULT '0', "CID" integer NOT NULL DEFAULT '0', "Notes" varchar(255) DEFAULT NULL, "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("HID","CID"), /*KEY "CharactersCharHeadingLinker" ("CID")*/CHECK (true), /*KEY "CharHeadingCharHeadingLinker" ("HID")*/CHECK (true), /*KEY "CID" ("CID")*/CHECK (true), /*CONSTRAINT "kmcharheadinglink_ibfk_1" FOREIGN KEY ("CID") REFERENCES "kmcharacters" ("cid")*/CHECK (true), /*CONSTRAINT "kmcharheadinglink_ibfk_2" FOREIGN KEY ("HID") REFERENCES "kmcharheading" ("hid") ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL DEFAULT '0', "TID" integer NOT NULL DEFAULT '0', "Status" varchar(50) DEFAULT NULL, "Notes" varchar(255) DEFAULT NULL, "Relation" varchar(45) NOT NULL DEFAULT 'include', "EditabilityInherited" bit(1) DEFAULT NULL, "timestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("CID","TID"), /*KEY "FK_CharTaxaLink-TID" ("TID")*/CHECK (true), /*CONSTRAINT "kmchartaxalink_ibfk_1" FOREIGN KEY ("CID") REFERENCES "kmcharacters" ("cid")*/CHECK (true), /*CONSTRAINT "kmchartaxalink_ibfk_2" FOREIGN KEY ("TID") REFERENCES "taxa" ("TID")*/CHECK (true) ); /*!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" integer NOT NULL DEFAULT '0', "cs" varchar(16) NOT NULL, "CharStateName" varchar(255) DEFAULT NULL, "Implicit" integer NOT NULL DEFAULT '0', "Notes" text, "Description" varchar(255) DEFAULT NULL, "IllustrationUrl" varchar(250) DEFAULT NULL, "StateID" integer DEFAULT NULL, "Language" varchar(45) NOT NULL DEFAULT 'English', "SortSequence" integer DEFAULT NULL, "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, "EnteredBy" varchar(45) DEFAULT NULL, PRIMARY KEY ("cs","cid"), /*KEY "FK_cs_chars" ("cid")*/CHECK (true), /*CONSTRAINT "kmcs_ibfk_1" FOREIGN KEY ("cid") REFERENCES "kmcharacters" ("cid")*/CHECK (true) ); /*!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" integer NOT NULL, "cid" integer 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" text/*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" integer 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" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("cid","cs","language"), /*CONSTRAINT "kmcslang_ibfk_1" FOREIGN KEY ("cid", "cs") REFERENCES "kmcs" ("cid", "cs")*/CHECK (true) ); /*!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" integer NOT NULL DEFAULT '0', "CID" integer NOT NULL DEFAULT '0', "Modifier" varchar(255) DEFAULT NULL, "CS" varchar(16) NOT NULL, "X" double precision DEFAULT NULL, "TXT" text, "PseudoTrait" integer DEFAULT '0', "Frequency" integer NOT NULL DEFAULT '5' /*COMMENT 'Frequency of occurrence; 1 = rare... 5 = common'*/, "Inherited" varchar(50) DEFAULT NULL, "Source" varchar(100) DEFAULT NULL, "Seq" integer DEFAULT NULL, "Notes" text, "DateEntered" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("TID","CID","CS"), /*KEY "CSDescr" ("CID","CS")*/CHECK (true), /*CONSTRAINT "kmdescr_ibfk_1" FOREIGN KEY ("CID", "CS") REFERENCES "kmcs" ("cid", "cs")*/CHECK (true), /*CONSTRAINT "kmdescr_ibfk_2" FOREIGN KEY ("TID") REFERENCES "taxa" ("TID")*/CHECK (true) ); /*!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" integer NOT NULL, "CID" integer NOT NULL, "CS" varchar(16) NOT NULL, "Modifier" varchar(255) DEFAULT NULL, "X" double precision DEFAULT NULL, "TXT" text, "Inherited" varchar(50) DEFAULT NULL, "Source" varchar(100) DEFAULT NULL, "Seq" integer DEFAULT NULL, "Notes" text, "InitialTimeStamp" text/*datetime*/ DEFAULT NULL, "DeletedBy" varchar(100) NOT NULL, "DeletedTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, "PK" integer 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" integer NOT NULL, "countryName" varchar(100) NOT NULL, "iso" varchar(2) DEFAULT NULL, "iso3" varchar(3) DEFAULT NULL, "numcode" integer DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("countryId"), /*CONSTRAINT "country_unique" */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" integer NOT NULL, "stateId" integer NOT NULL, "countyName" varchar(100) NOT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("countyId"), /*CONSTRAINT "unique_county" */UNIQUE ("stateId","countyName"), /*KEY "fk_stateprovince" ("stateId")*/CHECK (true), /*KEY "index_countyname" ("countyName")*/CHECK (true), /*CONSTRAINT "fk_stateprovince" FOREIGN KEY ("stateId") REFERENCES "lkupstateprovince" ("stateId") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "countryId" integer NOT NULL, "stateName" varchar(100) NOT NULL, "abbrev" varchar(2) DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("stateId"), /*CONSTRAINT "state_index" */UNIQUE ("stateName","countryId"), /*KEY "fk_country" ("countryId")*/CHECK (true), /*KEY "index_statename" ("stateName")*/CHECK (true), /*CONSTRAINT "fk_country" FOREIGN KEY ("countryId") REFERENCES "lkupcountry" ("countryId") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "occid" integer NOT NULL, "occidassociate" integer 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" text/*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" integer NOT NULL, "catagory" varchar(45) NOT NULL, "initialtimestamp" text/*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" integer NOT NULL, "collid" integer NOT NULL, "sortsequence" integer DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("ccpk","collid"), /*KEY "FK_collcatlink_coll" ("collid")*/CHECK (true), /*CONSTRAINT "FK_collcatlink_cat" FOREIGN KEY ("ccpk") REFERENCES "omcollcatagories" ("ccpk") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true), /*CONSTRAINT "FK_collcatlink_coll" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "InstitutionCode" varchar(45) NOT NULL, "CollectionCode" varchar(45) DEFAULT NULL, "CollectionName" varchar(150) NOT NULL, "iid" integer 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 precision DEFAULT NULL, "longitudedecimal" double precision 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" integer 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" integer DEFAULT NULL, "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("CollID"), /*KEY "Index_inst" ("InstitutionCode")*/CHECK (true) ); /*!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" integer NOT NULL, "recordcnt" integer NOT NULL DEFAULT '0', "georefcnt" integer DEFAULT NULL, "familycnt" integer DEFAULT NULL, "genuscnt" integer DEFAULT NULL, "speciescnt" integer DEFAULT NULL, "uploaddate" text/*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" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("collid"), /*CONSTRAINT "omcollectionstats_ibfk_1" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID")*/CHECK (true) ); /*!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" integer NOT NULL, "familyname" varchar(45) NOT NULL, "firstname" varchar(45) DEFAULT NULL, "middleinitial" varchar(45) DEFAULT NULL, "startyearactive" integer DEFAULT NULL, "endyearactive" integer DEFAULT NULL, "notes" varchar(255) DEFAULT NULL, "rating" integer DEFAULT '10', "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("recordedById"), /*KEY "fullname" ("familyname","firstname")*/CHECK (true) ); /*!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" integer NOT NULL, "collid" integer 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 precision DEFAULT NULL, "LongitudeDecimal" double precision DEFAULT NULL, "icon" varchar(250) DEFAULT NULL, "CollType" varchar(45) DEFAULT NULL, "SortSeq" integer DEFAULT NULL, "InitialTimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("ocsid"), /*KEY "FK_omcollsecondary_coll" ("collid")*/CHECK (true), /*CONSTRAINT "FK_omcollsecondary_coll" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "collid" integer NOT NULL, "instructions" text, "trainingurl" varchar(500) DEFAULT NULL, "editorlevel" integer NOT NULL DEFAULT '0' /*COMMENT '0=public, 1=public limited, 2=private'*/, "notes" varchar(250) DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("omcsid"), /*CONSTRAINT "Index_omcrowdsourcecentral_collid" */UNIQUE ("collid"), /*KEY "FK_omcrowdsourcecentral_collid" ("collid")*/CHECK (true), /*CONSTRAINT "FK_omcrowdsourcecentral_collid" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID")*/CHECK (true) ); /*!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" integer NOT NULL, "omcsid" integer NOT NULL, "occid" integer NOT NULL, "reviewstatus" integer NOT NULL DEFAULT '0' /*COMMENT '0=open,5=pending review, 10=closed'*/, "uidprocessor" integer DEFAULT NULL, "points" integer DEFAULT NULL /*COMMENT '0=fail, 1=minor edits, 2=no edits , 3=excelled'*/, "notes" varchar(250) DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("idomcrowdsourcequeue"), /*CONSTRAINT "Index_omcrowdsource_occid" */UNIQUE ("occid"), /*KEY "FK_omcrowdsourcequeue_occid" ("occid")*/CHECK (true), /*KEY "FK_omcrowdsourcequeue_uid" ("uidprocessor")*/CHECK (true), /*CONSTRAINT "FK_omcrowdsourcequeue_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true), /*CONSTRAINT "FK_omcrowdsourcequeue_uid" FOREIGN KEY ("uidprocessor") REFERENCES "users" ("uid") ON DELETE NO ACTION ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "exsnumber" varchar(45) NOT NULL, "ometid" integer NOT NULL, "notes" varchar(250) DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("omenid"), /*CONSTRAINT "Index_omexsiccatinumbers_unique" */UNIQUE ("exsnumber","ometid"), /*KEY "FK_exsiccatiTitleNumber" ("ometid")*/CHECK (true), /*CONSTRAINT "FK_exsiccatiTitleNumber" FOREIGN KEY ("ometid") REFERENCES "omexsiccatititles" ("ometid")*/CHECK (true) ); /*!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" integer NOT NULL, "occid" integer NOT NULL, "ranking" integer NOT NULL DEFAULT '50', "notes" varchar(250) DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("omenid","occid"), /*CONSTRAINT "UniqueOmexsiccatiOccLink" */UNIQUE ("occid"), /*KEY "FKExsiccatiNumOccLink1" ("omenid")*/CHECK (true), /*KEY "FKExsiccatiNumOccLink2" ("occid")*/CHECK (true), /*CONSTRAINT "FKExsiccatiNumOccLink1" FOREIGN KEY ("omenid") REFERENCES "omexsiccatinumbers" ("omenid") ON DELETE CASCADE*/CHECK (true), /*CONSTRAINT "FKExsiccatiNumOccLink2" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE*/CHECK (true) ); /*!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" integer 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" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("ometid"), /*KEY "index_exsiccatiTitle" ("title")*/CHECK (true) ); /*!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" integer NOT NULL, "occid" integer NOT NULL, "comment" text NOT NULL, "uid" integer NOT NULL, "reviewstatus" integer NOT NULL DEFAULT '0', "parentcomid" integer DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("comid"), /*KEY "fk_omoccurcomments_occid" ("occid")*/CHECK (true), /*KEY "fk_omoccurcomments_uid" ("uid")*/CHECK (true), /*CONSTRAINT "fk_omoccurcomments_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true), /*CONSTRAINT "fk_omoccurcomments_uid" FOREIGN KEY ("uid") REFERENCES "users" ("uid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "datasetid" integer NOT NULL, "notes" varchar(250) DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("occid","datasetid"), /*KEY "FK_omoccurdatasetlink_datasetid" ("datasetid")*/CHECK (true), /*KEY "FK_omoccurdatasetlink_occid" ("occid")*/CHECK (true), /*CONSTRAINT "FK_omoccurdatasetlink_datasetid" FOREIGN KEY ("datasetid") REFERENCES "omoccurdatasets" ("datasetid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true), /*CONSTRAINT "FK_omoccurdatasetlink_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "name" varchar(100) NOT NULL, "notes" varchar(250) DEFAULT NULL, "sortsequence" integer DEFAULT NULL, "uid" integer NOT NULL, "initialtimestamp" text/*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" integer NOT NULL, "occid" integer NOT NULL, "identifiedBy" varchar(60) NOT NULL, "idbyid" integer DEFAULT NULL, "dateIdentified" varchar(45) NOT NULL, "sciname" varchar(100) NOT NULL, "tidinterpreted" integer DEFAULT NULL, "scientificNameAuthorship" varchar(100) DEFAULT NULL, "identificationQualifier" varchar(45) DEFAULT NULL, "iscurrent" integer DEFAULT '0', "identificationReferences" varchar(255) DEFAULT NULL, "identificationRemarks" varchar(255) DEFAULT NULL, "sortsequence" integer DEFAULT '10', "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("detid"), /*CONSTRAINT "Index_unique" */UNIQUE ("occid","dateIdentified","identifiedBy"), /*KEY "FK_omoccurdets_tid" ("tidinterpreted")*/CHECK (true), /*KEY "FK_omoccurdets_idby_idx" ("idbyid")*/CHECK (true), /*CONSTRAINT "FK_omoccurdets_idby" FOREIGN KEY ("idbyid") REFERENCES "omcollectors" ("recordedById") ON DELETE SET NULL ON UPDATE SET NULL*/CHECK (true), /*CONSTRAINT "FK_omoccurdets_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true), /*CONSTRAINT "FK_omoccurdets_tid" FOREIGN KEY ("tidinterpreted") REFERENCES "taxa" ("TID")*/CHECK (true) ); /*!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" integer NOT NULL, "projIdentifier" varchar(30) NOT NULL, "projName" varchar(255) NOT NULL, "isExsiccata" integer NOT NULL DEFAULT '0', "exsiccataEditors" varchar(150) DEFAULT NULL, "notes" varchar(255) DEFAULT NULL, "initialTimestamp" text/*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" integer NOT NULL, "uid" integer NOT NULL, "ts" integer NOT NULL, "initialtimestamp" text/*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" integer NOT NULL, "occid" integer NOT NULL, "FieldName" varchar(45) NOT NULL, "FieldValueNew" text NOT NULL, "FieldValueOld" text NOT NULL, "ReviewStatus" integer NOT NULL DEFAULT '1' /*COMMENT '1=Open;2=Pending;3=Closed'*/, "AppliedStatus" integer NOT NULL DEFAULT '0' /*COMMENT '0=Not Applied;1=Applied'*/, "uid" integer NOT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("ocedid"), /*KEY "fk_omoccuredits_uid" ("uid")*/CHECK (true), /*KEY "fk_omoccuredits_occid" ("occid")*/CHECK (true), /*CONSTRAINT "fk_omoccuredits_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true), /*CONSTRAINT "fk_omoccuredits_uid" FOREIGN KEY ("uid") REFERENCES "users" ("uid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "identifier" varchar(30) DEFAULT NULL, "collid" integer DEFAULT NULL, "iid" integer DEFAULT NULL, "transactionType" varchar(10) DEFAULT NULL, "in_out" varchar(3) DEFAULT NULL, "dateSent" text/*date*/ DEFAULT NULL, "dateReceived" text/*date*/ DEFAULT NULL, "totalBoxes" integer DEFAULT NULL, "shippingMethod" varchar(50) DEFAULT NULL, "totalExMounted" integer DEFAULT NULL, "totalExUnmounted" integer DEFAULT NULL, "totalGift" integer DEFAULT NULL, "totalGiftDet" integer DEFAULT NULL, "adjustment" integer DEFAULT NULL, "invoiceBalance" integer DEFAULT NULL, "invoiceMessage" varchar(500) DEFAULT NULL, "description" varchar(1000) DEFAULT NULL, "notes" varchar(500) DEFAULT NULL, "createdBy" varchar(20) DEFAULT NULL, "initialTimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("exchangeid"), /*KEY "FK_occexch_coll" ("collid")*/CHECK (true), /*CONSTRAINT "FK_occexch_coll" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "occid" integer 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")*/CHECK (true), /*CONSTRAINT "FK_omoccurgenetic" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "decimallatitude" double precision NOT NULL, "decimallongitude" double precision NOT NULL, "initialtimestamp" text/*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*/CHECK (true) ); /*!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" integer NOT NULL, "loanIdentifierOwn" varchar(30) DEFAULT NULL, "loanIdentifierBorr" varchar(30) DEFAULT NULL, "collidOwn" integer DEFAULT NULL, "collidBorr" integer DEFAULT NULL, "iidOwner" integer DEFAULT NULL, "iidBorrower" integer DEFAULT NULL, "dateSent" text/*date*/ DEFAULT NULL, "dateSentReturn" text/*date*/ DEFAULT NULL, "receivedStatus" varchar(250) DEFAULT NULL, "totalBoxes" integer DEFAULT NULL, "totalBoxesReturned" integer DEFAULT NULL, "numSpecimens" integer DEFAULT NULL, "shippingMethod" varchar(50) DEFAULT NULL, "shippingMethodReturn" varchar(50) DEFAULT NULL, "dateDue" text/*date*/ DEFAULT NULL, "dateReceivedOwn" text/*date*/ DEFAULT NULL, "dateReceivedBorr" text/*date*/ DEFAULT NULL, "dateClosed" text/*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" integer DEFAULT '1', "processedByOwn" varchar(30) DEFAULT NULL, "processedByBorr" varchar(30) DEFAULT NULL, "processedByReturnOwn" varchar(30) DEFAULT NULL, "processedByReturnBorr" varchar(30) DEFAULT NULL, "initialTimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("loanid"), /*KEY "FK_occurloans_owninst" ("iidOwner")*/CHECK (true), /*KEY "FK_occurloans_borrinst" ("iidBorrower")*/CHECK (true), /*KEY "FK_occurloans_owncoll" ("collidOwn")*/CHECK (true), /*KEY "FK_occurloans_borrcoll" ("collidBorr")*/CHECK (true), /*CONSTRAINT "FK_occurloans_borrcoll" FOREIGN KEY ("collidBorr") REFERENCES "omcollections" ("CollID") ON DELETE NO ACTION ON UPDATE NO ACTION*/CHECK (true), /*CONSTRAINT "FK_occurloans_borrinst" FOREIGN KEY ("iidBorrower") REFERENCES "institutions" ("iid")*/CHECK (true), /*CONSTRAINT "FK_occurloans_owncoll" FOREIGN KEY ("collidOwn") REFERENCES "omcollections" ("CollID") ON DELETE NO ACTION ON UPDATE NO ACTION*/CHECK (true), /*CONSTRAINT "FK_occurloans_owninst" FOREIGN KEY ("iidOwner") REFERENCES "institutions" ("iid")*/CHECK (true) ); /*!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" integer NOT NULL, "occid" integer NOT NULL, "returndate" text/*date*/ DEFAULT NULL, "notes" varchar(255) DEFAULT NULL, "initialTimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("loanid","occid"), /*KEY "FK_occurloanlink_occid" ("occid")*/CHECK (true), /*KEY "FK_occurloanlink_loanid" ("loanid")*/CHECK (true), /*CONSTRAINT "FK_occurloanlink_loanid" FOREIGN KEY ("loanid") REFERENCES "omoccurloans" ("loanid") ON UPDATE CASCADE*/CHECK (true), /*CONSTRAINT "FK_occurloanlink_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "collid" integer 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" integer 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" integer DEFAULT NULL, "associatedCollectors" varchar(255) DEFAULT NULL /*COMMENT 'not DwC'*/, "eventDate" text/*date*/ DEFAULT NULL, "year" integer DEFAULT NULL, "month" integer DEFAULT NULL, "day" integer DEFAULT NULL, "startDayOfYear" integer DEFAULT NULL, "endDayOfYear" integer 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" integer 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" integer DEFAULT '0' /*COMMENT '0 = no security; 1 = hidden locality'*/, "localitySecurityReason" varchar(100) DEFAULT NULL, "decimalLatitude" double precision DEFAULT NULL, "decimalLongitude" double precision DEFAULT NULL, "geodeticDatum" varchar(255) DEFAULT NULL, "coordinateUncertaintyInMeters" integer 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" integer DEFAULT NULL, "maximumElevationInMeters" integer 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" text/*datetime*/ DEFAULT NULL /*COMMENT 'DateLastModified'*/, "language" varchar(20) DEFAULT NULL, "duplicateid" integer DEFAULT NULL, "observeruid" integer DEFAULT NULL, "processingstatus" varchar(45) DEFAULT NULL, "deaccessioned" integer NOT NULL DEFAULT '0', "recordEnteredBy" varchar(250) DEFAULT NULL, "duplicateQuantity" integer DEFAULT NULL, "labelProject" varchar(50) DEFAULT NULL, "dateLastModified" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("occid"), /*CONSTRAINT "Index_collid" */UNIQUE ("collid","dbpk"), /*KEY "Index_sciname" ("sciname")*/CHECK (true), /*KEY "Index_family" ("family")*/CHECK (true), /*KEY "Index_country" ("country")*/CHECK (true), /*KEY "Index_state" ("stateProvince")*/CHECK (true), /*KEY "Index_county" ("county")*/CHECK (true), /*KEY "Index_collector" ("recordedBy")*/CHECK (true), /*KEY "Index_gui" ("occurrenceID")*/CHECK (true), /*KEY "Index_ownerInst" ("ownerInstitutionCode")*/CHECK (true), /*KEY "FK_omoccurrences_tid" ("tidinterpreted")*/CHECK (true), /*KEY "FK_omoccurrences_uid" ("observeruid")*/CHECK (true), /*KEY "Index_municipality" ("municipality")*/CHECK (true), /*KEY "Index_collnum" ("recordNumber")*/CHECK (true), /*KEY "Index_catalognumber" ("catalogNumber")*/CHECK (true), /*KEY "FK_recordedbyid" ("recordedById")*/CHECK (true), /*KEY "FK_omoccurrences_dupes" ("duplicateid")*/CHECK (true), /*KEY "Index_eventDate" ("eventDate")*/CHECK (true), /*KEY "Index_occurrences_procstatus" ("processingstatus")*/CHECK (true), /*CONSTRAINT "FK_omoccurrences_collid" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true), /*CONSTRAINT "FK_omoccurrences_dupes" FOREIGN KEY ("duplicateid") REFERENCES "omoccurduplicates" ("duplicateid") ON DELETE SET NULL ON UPDATE CASCADE*/CHECK (true), /*CONSTRAINT "FK_omoccurrences_recbyid" FOREIGN KEY ("recordedById") REFERENCES "omcollectors" ("recordedById") ON DELETE SET NULL ON UPDATE CASCADE*/CHECK (true), /*CONSTRAINT "FK_omoccurrences_tid" FOREIGN KEY ("tidinterpreted") REFERENCES "taxa" ("TID") ON DELETE SET NULL ON UPDATE CASCADE*/CHECK (true), /*CONSTRAINT "FK_omoccurrences_uid" FOREIGN KEY ("observeruid") REFERENCES "users" ("uid")*/CHECK (true) ); /*!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" integer NOT NULL, "occid" integer NOT NULL, "category" varchar(45) NOT NULL, "ranking" integer NOT NULL, "protocol" varchar(100) DEFAULT NULL, "source" varchar(45) DEFAULT NULL, "uid" integer DEFAULT NULL, "notes" varchar(250) DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("ovsid"), /*KEY "FK_omoccurverification_occid_idx" ("occid")*/CHECK (true), /*KEY "FK_omoccurverification_uid_idx" ("uid")*/CHECK (true), /*CONSTRAINT "FK_omoccurverification_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true), /*CONSTRAINT "FK_omoccurverification_uid" FOREIGN KEY ("uid") REFERENCES "users" ("uid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "surveyid" integer NOT NULL, "notes" varchar(250) DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("occid","surveyid"), /*KEY "FK_omsurveyoccurlink_sur" ("surveyid")*/CHECK (true), /*CONSTRAINT "FK_omsurveyoccurlink_occ" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid")*/CHECK (true), /*CONSTRAINT "FK_omsurveyoccurlink_sur" FOREIGN KEY ("surveyid") REFERENCES "omsurveys" ("surveyid")*/CHECK (true) ); /*!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" integer NOT NULL, "pid" integer NOT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("surveyid","pid"), /*KEY "FK_specprojcatlink_cat" ("pid")*/CHECK (true), /*CONSTRAINT "FK_omsurveyprojlink_proj" FOREIGN KEY ("pid") REFERENCES "fmprojects" ("pid")*/CHECK (true), /*CONSTRAINT "FK_omsurveyprojlink_sur" FOREIGN KEY ("surveyid") REFERENCES "omsurveys" ("surveyid")*/CHECK (true) ); /*!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" integer NOT NULL, "projectname" varchar(75) NOT NULL, "locality" varchar(1000) DEFAULT NULL, "managers" varchar(150) DEFAULT NULL, "latcentroid" double precision DEFAULT NULL, "longcentroid" double precision DEFAULT NULL, "notes" varchar(250) DEFAULT NULL, "ispublic" integer NOT NULL DEFAULT '0', "sortsequence" integer NOT NULL DEFAULT '50', "initialtimestamp" text/*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" integer NOT NULL, "collid" integer NOT NULL, "title" varchar(100) NOT NULL, "specKeyPattern" varchar(45) DEFAULT NULL, "speckeyretrieval" varchar(45) DEFAULT NULL, "coordX1" integer DEFAULT NULL, "coordX2" integer DEFAULT NULL, "coordY1" integer DEFAULT NULL, "coordY2" integer DEFAULT NULL, "sourcePath" varchar(250) DEFAULT NULL, "targetPath" varchar(250) DEFAULT NULL, "imgUrl" varchar(250) DEFAULT NULL, "webPixWidth" integer DEFAULT '1200', "tnPixWidth" integer DEFAULT '130', "lgPixWidth" integer DEFAULT '2400', "jpgcompression" integer DEFAULT '70', "createTnImg" integer DEFAULT '1', "createLgImg" integer DEFAULT '1', "initialTimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("spprid"), /*KEY "FK_specprocessorprojects_coll" ("collid")*/CHECK (true), /*CONSTRAINT "FK_specprocessorprojects_coll" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "imgid" integer DEFAULT NULL, "occid" integer DEFAULT NULL, "rawstr" text NOT NULL, "processingvariables" varchar(250) DEFAULT NULL, "score" integer DEFAULT NULL, "notes" varchar(255) DEFAULT NULL, "source" varchar(150) DEFAULT NULL, "sortsequence" integer DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("prlid"), /*KEY "FK_specproc_images" ("imgid")*/CHECK (true), /*KEY "FK_specproc_occid" ("occid")*/CHECK (true), /*CONSTRAINT "FK_specproc_images" FOREIGN KEY ("imgid") REFERENCES "images" ("imgid") ON UPDATE CASCADE*/CHECK (true), /*CONSTRAINT "FK_specproc_occid" FOREIGN KEY ("occid") REFERENCES "omoccurrences" ("occid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "title" varchar(45) NOT NULL, "sqlfrag" varchar(250) NOT NULL, "patternmatch" varchar(250) DEFAULT NULL, "notes" varchar(250) DEFAULT NULL, "collid" integer NOT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("spnlpid"), /*KEY "FK_specprocnlp_collid" ("collid")*/CHECK (true), /*CONSTRAINT "FK_specprocnlp_collid" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "spnlpid" integer NOT NULL, "fieldname" varchar(45) NOT NULL, "patternmatch" varchar(250) NOT NULL, "notes" varchar(250) DEFAULT NULL, "sortseq" integer DEFAULT '50', "initialtimestamp" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("spnlpfragid"), /*KEY "FK_specprocnlpfrag_spnlpid" ("spnlpid")*/CHECK (true), /*CONSTRAINT "FK_specprocnlpfrag_spnlpid" FOREIGN KEY ("spnlpid") REFERENCES "specprocnlp" ("spnlpid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "KingdomID" integer NOT NULL DEFAULT '3', "RankId" integer 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" integer DEFAULT NULL, "InitialTimeStamp" text/*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" integer DEFAULT NULL /*COMMENT 'Primary key for FNA project'*/, "UsdaSymbol" varchar(50) DEFAULT NULL, "SecurityStatus" integer NOT NULL DEFAULT '0' /*COMMENT '0 = no security; 1 = hidden locality'*/, PRIMARY KEY ("TID"), /*CONSTRAINT "sciname_unique" */UNIQUE ("SciName"), /*KEY "rankid_index" ("RankId")*/CHECK (true), /*KEY "unitname1_index" ("UnitName1","UnitName2")*/CHECK (true), /*KEY "FK_taxa_taxonunits" ("KingdomID","RankId")*/CHECK (true), /*CONSTRAINT "taxa_ibfk_1" FOREIGN KEY ("KingdomID", "RankId") REFERENCES "taxonunits" ("kingdomid", "rankid")*/CHECK (true) ); /*!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" integer NOT NULL, "tid" integer NOT NULL, "caption" varchar(30) DEFAULT NULL, "source" varchar(250) DEFAULT NULL, "sourceurl" varchar(250) DEFAULT NULL, "language" varchar(45) DEFAULT 'English', "displaylevel" integer NOT NULL DEFAULT '1' /*COMMENT '1 = short descr, 2 = intermediate descr'*/, "uid" integer NOT NULL, "notes" varchar(250) DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("tdbid"), /*CONSTRAINT "Index_unique" */UNIQUE ("tid","displaylevel","language"), /*CONSTRAINT "taxadescrblock_ibfk_1" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID")*/CHECK (true) ); /*!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" integer NOT NULL, "tdbid" integer NOT NULL, "heading" varchar(75) NOT NULL, "statement" text NOT NULL, "displayheader" integer NOT NULL DEFAULT '1', "notes" varchar(250) DEFAULT NULL, "sortsequence" integer NOT NULL DEFAULT '89', "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("tdsid"), /*KEY "FK_taxadescrstmts_tblock" ("tdbid")*/CHECK (true), /*CONSTRAINT "taxadescrstmts_ibfk_1" FOREIGN KEY ("tdbid") REFERENCES "taxadescrblock" ("tdbid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "taxauthid" integer NOT NULL, "parenttid" integer NOT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("tid","taxauthid","parenttid"), /*KEY "FK_tet_taxa" ("tid")*/CHECK (true), /*KEY "FK_tet_taxauth" ("taxauthid")*/CHECK (true), /*KEY "FK_tet_taxa2" ("parenttid")*/CHECK (true), /*CONSTRAINT "FK_tet_taxa" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true), /*CONSTRAINT "FK_tet_taxa2" FOREIGN KEY ("parenttid") REFERENCES "taxa" ("TID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true), /*CONSTRAINT "FK_tet_taxauth" FOREIGN KEY ("taxauthid") REFERENCES "taxauthority" ("taxauthid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "tid" integer 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" integer NOT NULL DEFAULT '50', "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("tlid"), /*KEY "Index_unique" ("tid","url")*/CHECK (true), /*CONSTRAINT "taxalinks_ibfk_1" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID")*/CHECK (true) ); /*!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" integer 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 precision DEFAULT NULL, "latsouth" double precision DEFAULT NULL, "longwest" double precision DEFAULT NULL, "longeast" double precision DEFAULT NULL, "pointsize" integer DEFAULT NULL, "red" integer DEFAULT NULL, "green" integer DEFAULT NULL, "blue" integer DEFAULT NULL, "latadjustfactor" double precision NOT NULL DEFAULT '0', "initialtimestamp" text/*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" integer NOT NULL, "tid" integer NOT NULL, "url" varchar(255) NOT NULL, "title" varchar(100) DEFAULT NULL, "dmid" integer DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("mid"), /*CONSTRAINT "Index_unique" */UNIQUE ("tid","dmid"), /*KEY "FK_taxamaps_dmid" ("dmid")*/CHECK (true), /*CONSTRAINT "taxamaps_ibfk_1" FOREIGN KEY ("dmid") REFERENCES "taxamapparams" ("dmid") ON UPDATE CASCADE*/CHECK (true), /*CONSTRAINT "taxamaps_ibfk_2" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID")*/CHECK (true) ); /*!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" integer NOT NULL, "taxauthid" integer NOT NULL, "leftindex" integer NOT NULL, "rightindex" integer NOT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("tid","taxauthid"), /*KEY "leftindex" ("leftindex")*/CHECK (true), /*KEY "rightindex" ("rightindex")*/CHECK (true), /*KEY "FK_tnt_taxa" ("tid")*/CHECK (true), /*KEY "FK_tnt_taxauth" ("taxauthid")*/CHECK (true), /*CONSTRAINT "FK_tnt_taxa" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true), /*CONSTRAINT "FK_tnt_taxauth" FOREIGN KEY ("taxauthid") REFERENCES "taxauthority" ("taxauthid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "isprimary" integer 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" integer NOT NULL DEFAULT '1', "initialtimestamp" text/*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" integer NOT NULL DEFAULT '0', "VernacularName" varchar(80) NOT NULL, "Language" varchar(15) NOT NULL DEFAULT 'English', "Source" varchar(50) DEFAULT NULL, "notes" varchar(250) DEFAULT NULL, "username" varchar(45) DEFAULT NULL, "isupperterm" integer DEFAULT '0', "SortSequence" integer DEFAULT '50', "VID" integer NOT NULL, "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("VID"), /*CONSTRAINT "unique-key" */UNIQUE ("Language","VernacularName","TID"), /*KEY "tid1" ("TID")*/CHECK (true), /*KEY "vernacularsnames" ("VernacularName")*/CHECK (true), /*CONSTRAINT "taxavernaculars_ibfk_1" FOREIGN KEY ("TID") REFERENCES "taxa" ("TID")*/CHECK (true) ); /*!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" integer NOT NULL, "rankid" integer NOT NULL, "rankname" varchar(15) NOT NULL, "suffix" varchar(45) DEFAULT NULL, "dirparentrankid" integer NOT NULL, "reqparentrankid" integer DEFAULT NULL, "initialtimestamp" text/*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" integer NOT NULL, "tidaccepted" integer NOT NULL, "taxauthid" integer NOT NULL /*COMMENT 'taxon authority id'*/, "parenttid" integer 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" integer DEFAULT '50', "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("tid","tidaccepted","taxauthid"), /*KEY "FK_taxstatus_tidacc" ("tidaccepted")*/CHECK (true), /*KEY "FK_taxstatus_taid" ("taxauthid")*/CHECK (true), /*KEY "Index_ts_family" ("family")*/CHECK (true), /*KEY "Index_ts_upper" ("uppertaxonomy")*/CHECK (true), /*KEY "Index_parenttid" ("parenttid")*/CHECK (true), /*KEY "Index_hierarchy" ("hierarchystr")*/CHECK (true), /*CONSTRAINT "taxstatus_ibfk_1" FOREIGN KEY ("parenttid") REFERENCES "taxa" ("TID")*/CHECK (true), /*CONSTRAINT "taxstatus_ibfk_2" FOREIGN KEY ("taxauthid") REFERENCES "taxauthority" ("taxauthid") ON UPDATE CASCADE*/CHECK (true), /*CONSTRAINT "taxstatus_ibfk_3" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID")*/CHECK (true), /*CONSTRAINT "taxstatus_ibfk_4" FOREIGN KEY ("tidaccepted") REFERENCES "taxa" ("TID")*/CHECK (true) ); /*!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" text/*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 precision DEFAULT NULL, "LongDec" double precision DEFAULT NULL, "Elev" varchar(255) DEFAULT NULL, "Project" varchar(50) DEFAULT NULL, "Cataloger" varchar(250) DEFAULT NULL, "DateModified" text/*timestamp*/ NOT NULL DEFAULT '0000-00-00 00:00:00', "occid" integer 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" integer NOT NULL DEFAULT '0', "family" varchar(35) DEFAULT NULL, "unit_ind1" varchar(1) DEFAULT NULL, "unit_name1" varchar(35) DEFAULT NULL, "unit_ind2" varchar(1) DEFAULT NULL, "unit_name2" varchar(35) DEFAULT NULL, "author" varchar(100) DEFAULT NULL, "unit_ind3" varchar(7) DEFAULT NULL, "unit_name3" varchar(35) DEFAULT NULL, "infrasp_author" varchar(100) DEFAULT NULL, "unit_ind4" varchar(7) DEFAULT NULL, "unit_name4" varchar(35) DEFAULT NULL, "infrasp_author2" varchar(100) DEFAULT NULL, "az_itis" integer DEFAULT NULL, "status" varchar(50) DEFAULT NULL, "acronym" varchar(10) DEFAULT NULL, "security" integer DEFAULT NULL, "notes" varchar(255) DEFAULT NULL, "security_source" varchar(255) DEFAULT NULL, "old_taxa_id" integer DEFAULT NULL, "scientific_name" varchar(200) NOT NULL, "taxonomic_group" varchar(150) DEFAULT NULL, "date_created" text/*timestamp*/ NOT NULL DEFAULT '0000-00-00 00:00:00', "rank" varchar(6) DEFAULT NULL, "is_accepted" integer DEFAULT NULL, "entered_by" varchar(60) DEFAULT NULL, "type_publication_id" integer DEFAULT NULL, "ariz_usda" varchar(50) DEFAULT NULL, "ariz_tsn" varchar(255) DEFAULT NULL, "itis_sciname" varchar(250) DEFAULT NULL, "itis_usda" varchar(50) DEFAULT NULL, "itis_tsn" varchar(255) DEFAULT NULL, /*KEY "taxa_id" ("taxa_id")*/CHECK (true), /*KEY "family" ("family")*/CHECK (true), /*KEY "scientific_name" ("scientific_name")*/CHECK (true), /*KEY "itis_sciname" ("itis_sciname")*/CHECK (true), /*KEY "itis_tsn" ("itis_tsn")*/CHECK (true) ); /*!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 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" integer NOT NULL, "unkid" integer NOT NULL, "comment" varchar(500) NOT NULL, "username" varchar(45) NOT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("unkcomid"), /*KEY "FK_unknowncomments" ("unkid")*/CHECK (true), /*CONSTRAINT "FK_unknowncomments" FOREIGN KEY ("unkid") REFERENCES "unknowns" ("unkid")*/CHECK (true) ); /*!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" integer NOT NULL, "unkid" integer NOT NULL, "url" varchar(255) NOT NULL, "notes" varchar(250) DEFAULT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("unkimgid"), /*KEY "FK_unknowns" ("unkid")*/CHECK (true), /*CONSTRAINT "FK_unknowns" FOREIGN KEY ("unkid") REFERENCES "unknowns" ("unkid") ON DELETE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "tid" integer DEFAULT NULL, "photographer" varchar(100) DEFAULT NULL, "owner" varchar(100) DEFAULT NULL, "locality" varchar(250) DEFAULT NULL, "latdecimal" double precision DEFAULT NULL, "longdecimal" double precision DEFAULT NULL, "notes" varchar(250) DEFAULT NULL, "username" varchar(45) NOT NULL, "idstatus" varchar(45) NOT NULL DEFAULT 'ID pending', "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("unkid"), /*KEY "FK_unknowns_username" ("username")*/CHECK (true), /*KEY "FK_unknowns_tid" ("tid")*/CHECK (true), /*CONSTRAINT "FK_unknowns_tid" FOREIGN KEY ("tid") REFERENCES "taxa" ("TID")*/CHECK (true), /*CONSTRAINT "FK_unknowns_username" FOREIGN KEY ("username") REFERENCES "userlogin" ("username")*/CHECK (true) ); /*!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" integer DEFAULT NULL, "url" varchar(255) NOT NULL, "thumbnailurl" varchar(255) DEFAULT NULL, "originalurl" varchar(255) DEFAULT NULL, "photographer" varchar(100) DEFAULT NULL, "photographeruid" integer DEFAULT NULL, "imagetype" varchar(50) DEFAULT NULL, "caption" varchar(100) DEFAULT NULL, "owner" varchar(100) DEFAULT NULL, "occid" integer DEFAULT NULL, "collid" integer DEFAULT NULL, "dbpk" varchar(45) DEFAULT NULL, "specimengui" varchar(45) DEFAULT NULL, "notes" varchar(255) DEFAULT NULL, "username" varchar(45) DEFAULT NULL, "sortsequence" integer DEFAULT NULL, "initialtimestamp" text/*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" integer NOT NULL, "uspid" integer NOT NULL, "sourcefield" varchar(45) NOT NULL, "symbdatatype" varchar(45) NOT NULL DEFAULT 'string' /*COMMENT 'string, numeric, text/*datetime*/'*/, "symbspecfield" varchar(45) NOT NULL, "initialtimestamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("usmid"), /*CONSTRAINT "Index_unique" */UNIQUE ("uspid","symbspecfield"), /*CONSTRAINT "FK_uploadspecmap_usp" FOREIGN KEY ("uspid") REFERENCES "uploadspecparameters" ("uspid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "CollID" integer NOT NULL, "UploadType" integer 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" integer 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" integer DEFAULT '0', "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("uspid"), /*KEY "FK_uploadspecparameters_coll" ("CollID")*/CHECK (true), /*CONSTRAINT "FK_uploadspecparameters_coll" FOREIGN KEY ("CollID") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "dbpk" varchar(45) DEFAULT NULL, "occid" integer 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" integer 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" text/*date*/ DEFAULT NULL, "year" integer DEFAULT NULL, "month" integer DEFAULT NULL, "day" integer DEFAULT NULL, "startDayOfYear" integer DEFAULT NULL, "endDayOfYear" integer DEFAULT NULL, "LatestDateCollected" text/*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" integer 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" integer DEFAULT '0' /*COMMENT '0 = display locality, 1 = hide locality'*/, "localitySecurityReason" varchar(100) DEFAULT NULL, "decimalLatitude" double precision DEFAULT NULL, "decimalLongitude" double precision DEFAULT NULL, "geodeticDatum" varchar(255) DEFAULT NULL, "coordinateUncertaintyInMeters" integer DEFAULT NULL, "footprintWKT" text, "coordinatePrecision" decimal(9,7) DEFAULT NULL, "locationRemarks" text, "verbatimCoordinates" varchar(255) DEFAULT NULL, "verbatimCoordinateSystem" varchar(255) DEFAULT NULL, "latDeg" integer DEFAULT NULL, "latMin" double precision DEFAULT NULL, "latSec" double precision DEFAULT NULL, "latNS" varchar(3) DEFAULT NULL, "lngDeg" integer DEFAULT NULL, "lngMin" double precision DEFAULT NULL, "lngSec" double precision 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" integer DEFAULT NULL, "maximumElevationInMeters" integer 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" text/*datetime*/ DEFAULT NULL /*COMMENT 'DateLastModified'*/, "language" varchar(2) DEFAULT NULL, "recordEnteredBy" varchar(250) DEFAULT NULL, "duplicateQuantity" integer 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" text/*timestamp*/ NULL DEFAULT CURRENT_TIMESTAMP, /*KEY "FK_uploadspectemp_coll" ("collid")*/CHECK (true), /*KEY "Index_uploadspectemp_occid" ("occid")*/CHECK (true), /*KEY "Index_uploadspectemp_dbpk" ("dbpk")*/CHECK (true), /*CONSTRAINT "FK_uploadspectemp_coll" FOREIGN KEY ("collid") REFERENCES "omcollections" ("CollID") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer DEFAULT NULL, "SourceId" integer DEFAULT NULL, "KingdomID" integer DEFAULT '3', "UpperTaxonomy" varchar(50) DEFAULT NULL, "Family" varchar(50) DEFAULT NULL, "RankId" integer 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" integer DEFAULT '1' /*COMMENT '0 = not accepted; 1 = accepted'*/, "TidAccepted" integer DEFAULT NULL, "AcceptedStr" varchar(250) DEFAULT NULL, "SourceAcceptedId" integer DEFAULT NULL, "UnacceptabilityReason" varchar(24) DEFAULT NULL, "ParentTid" integer DEFAULT NULL, "ParentStr" varchar(250) DEFAULT NULL, "SourceParentId" integer DEFAULT NULL, "SecurityStatus" integer 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" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("scinameinput"), /*CONSTRAINT "sciname_index" */UNIQUE ("SciName"), /*KEY "sourceID_index" ("SourceId")*/CHECK (true), /*KEY "sourceAcceptedId_index" ("SourceAcceptedId")*/CHECK (true) ); /*!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" integer NOT NULL, "username" varchar(45) NOT NULL, "password" varchar(45) NOT NULL, "alias" varchar(45) DEFAULT NULL, "lastlogindate" text/*datetime*/ DEFAULT NULL, "InitialTimeStamp" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("username"), /*CONSTRAINT "Index_userlogin_unique" */UNIQUE ("alias"), /*KEY "FK_login_user" ("uid")*/CHECK (true), /*CONSTRAINT "userlogin_ibfk_1" FOREIGN KEY ("uid") REFERENCES "users" ("uid") ON DELETE CASCADE ON UPDATE CASCADE*/CHECK (true) ); /*!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" integer NOT NULL, "pname" varchar(45) NOT NULL /*COMMENT 'SuperAdmin, TaxonProfile, IdentKey, RareSpecies, coll-1, cl-1, proj-1'*/, "assignedby" varchar(45) DEFAULT NULL, "initialtimestamp" text/*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*/CHECK (true) ); /*!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" integer 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" integer 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" text/*timestamp*/ NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY ("uid"), /*CONSTRAINT "Index_email" */UNIQUE ("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