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, "deleted" integer NOT NULL DEFAULT '0', "deleted_reason" varchar(250) DEFAULT NULL, "determination_date" text/*date*/ DEFAULT NULL, "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', "sheets" integer DEFAULT '1', "collnum_numeric" double precision DEFAULT NULL, "needs_label" integer DEFAULT '0', "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', "temp_dbsn" integer DEFAULT 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, "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', "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, "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', "ManagementType" varchar(45) DEFAULT 'Snapshot', "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', "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', "uidprocessor" integer DEFAULT NULL, "points" integer DEFAULT NULL, "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', "AppliedStatus" integer NOT NULL DEFAULT '0', "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', "occurrenceID" varchar(255) DEFAULT NULL, "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, "typeStatus" varchar(255) DEFAULT NULL, "recordedBy" varchar(255) DEFAULT NULL, "recordNumber" varchar(45) DEFAULT NULL, "recordedById" integer DEFAULT NULL, "associatedCollectors" varchar(255) DEFAULT NULL, "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, "substrate" varchar(500) DEFAULT NULL, "fieldNotes" text, "fieldnumber" varchar(45) DEFAULT NULL, "occurrenceRemarks" text, "informationWithheld" varchar(250) DEFAULT NULL, "dataGeneralizations" varchar(250) DEFAULT NULL, "associatedOccurrences" text, "associatedTaxa" text, "dynamicProperties" text, "verbatimAttributes" text, "attributes" text, "reproductiveCondition" varchar(255) DEFAULT NULL, "cultivationStatus" integer DEFAULT NULL, "establishmentMeans" varchar(45) DEFAULT NULL, "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', "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, "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, "UsdaSymbol" varchar(50) DEFAULT NULL, "SecurityStatus" integer NOT NULL DEFAULT '0', 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', "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, "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, "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, "PlantDesc" text, "Habitat" text, "AssocSpp" text, "FirstColl" varchar(255) DEFAULT NULL, "CollNum" varchar(45) DEFAULT NULL, "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', "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', "title" varchar(45) NOT NULL, "Platform" varchar(45) DEFAULT '1', "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', "occurrenceID" varchar(255) DEFAULT NULL, "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, "typeStatus" varchar(255) DEFAULT NULL, "recordedBy" varchar(255) DEFAULT NULL, "recordNumber" varchar(32) DEFAULT NULL, "CollectorFamilyName" varchar(255) DEFAULT NULL, "CollectorInitials" varchar(255) DEFAULT NULL, "associatedCollectors" varchar(255) DEFAULT NULL, "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, "substrate" varchar(500) DEFAULT NULL, "fieldNotes" text, "fieldnumber" varchar(45) DEFAULT NULL, "occurrenceRemarks" text, "informationWithheld" varchar(250) DEFAULT NULL, "dataGeneralizations" varchar(250) DEFAULT NULL, "associatedOccurrences" text, "associatedTaxa" text, "dynamicProperties" text, "verbatimAttributes" text, "attributes" text, "reproductiveCondition" varchar(255) DEFAULT NULL, "cultivationStatus" integer DEFAULT NULL, "establishmentMeans" varchar(32) DEFAULT NULL, "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', "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, "genericcolumn1" varchar(100) DEFAULT NULL, "genericcolumn2" varchar(100) DEFAULT NULL, "modified" text/*datetime*/ DEFAULT NULL, "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', "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', "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, "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