SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';

CREATE SCHEMA IF NOT EXISTS `default_schema` ;
USE `default_schema` ;

-- -----------------------------------------------------
-- Table `default_schema`.`bien_genus_family`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `default_schema`.`bien_genus_family` (
  `family` VARCHAR(250) CHARACTER SET 'utf8' NOT NULL ,
  `genus` VARCHAR(150) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `tot_families` INT(1) NULL DEFAULT NULL ,
  INDEX `genus` (`genus` ASC) ,
  INDEX `family` (`family` ASC) ,
  INDEX `tot_families` (`tot_families` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;


-- -----------------------------------------------------
-- Table `default_schema`.`clade`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `default_schema`.`clade` (
  `cladeID` INT(11) UNSIGNED NOT NULL ,
  `parentCladeID` INT(11) UNSIGNED NULL DEFAULT NULL ,
  `cladeNameStd` VARCHAR(250) NOT NULL ,
  `rank` VARCHAR(50) NULL DEFAULT NULL ,
  `isHybrid` INT(1) UNSIGNED NOT NULL ,
  `family` VARCHAR(100) NULL DEFAULT NULL ,
  `leftIndex` INT(11) UNSIGNED NULL DEFAULT NULL ,
  `rightIndex` INT(11) UNSIGNED NULL DEFAULT NULL ,
  `source` VARCHAR(25) NULL DEFAULT NULL ,
  `majorPlantGroup` VARCHAR(45) NULL DEFAULT NULL ,
  `embryophytes` VARCHAR(45) NULL DEFAULT NULL ,
  `vascularPlants` VARCHAR(45) NULL DEFAULT NULL ,
  `seedPlants` VARCHAR(45) NULL DEFAULT NULL ,
  `floweringPlants` VARCHAR(45) NULL DEFAULT NULL ,
  PRIMARY KEY USING BTREE (`cladeID`) ,
  INDEX `nameStaging_isHybrid` (`isHybrid` ASC) ,
  INDEX `nameStaging_family` (`family` ASC) ,
  INDEX `nameStaging_leftIndex` (`leftIndex` ASC) ,
  INDEX `nameStaging_rightIndex` (`rightIndex` ASC) ,
  INDEX `idx_source` (`source` ASC) ,
  INDEX `nameStaging_rank` USING BTREE (`rank` ASC) ,
  INDEX `Index_majorPlantGroup` USING BTREE (`majorPlantGroup` ASC) ,
  INDEX `Index_embryophytes` USING BTREE (`embryophytes` ASC) ,
  INDEX `Index_seedPlants` USING BTREE (`seedPlants` ASC) ,
  INDEX `Index_vascularPlants` USING BTREE (`vascularPlants` ASC) ,
  INDEX `Index_floweringPlants` USING BTREE (`floweringPlants` ASC) ,
  INDEX `nameStaging_parentCladeID` USING BTREE (`parentCladeID` ASC) ,
  INDEX `nameStaging_cladeNameStd` USING BTREE (`cladeNameStd` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `default_schema`.`cladeName`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `default_schema`.`cladeName` (
  `cladeNameID` INT(11) NOT NULL AUTO_INCREMENT ,
  `cladeID` INT(10) UNSIGNED NOT NULL ,
  `cladeName` VARCHAR(250) NOT NULL ,
  `cladeNameUnique` VARCHAR(300) NULL DEFAULT NULL ,
  `nameStatus` VARCHAR(50) NULL DEFAULT NULL ,
  `source` VARCHAR(45) NULL DEFAULT NULL ,
  PRIMARY KEY USING BTREE (`cladeNameID`) ,
  INDEX `idx_cladeID` USING BTREE (`cladeID` ASC) ,
  INDEX `Index_source` (`source` ASC) ,
  INDEX `idx_name` USING BTREE (`cladeName` ASC) ,
  INDEX `idx_nameStatus` USING BTREE (`nameStatus` ASC) ,
  CONSTRAINT `cladeName_ibfk_1`
    FOREIGN KEY (`cladeID` )
    REFERENCES `default_schema`.`clade` (`cladeID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `default_schema`.`DataSource`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `default_schema`.`DataSource` (
  `DataSourceID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `DataSourceName` VARCHAR(255) NOT NULL ,
  `DataSourceType` VARCHAR(255) NOT NULL ,
  PRIMARY KEY (`DataSourceID`) ,
  INDEX `DataSourceName` (`DataSourceName` ASC) ,
  INDEX `DataSourceType` (`DataSourceType` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8, 
COMMENT = 'Data sources for plots or specimens' ;


-- -----------------------------------------------------
-- Table `default_schema`.`PlotMetaDataDimension`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `default_schema`.`PlotMetaDataDimension` (
  `DBPlotID` INT(11) NOT NULL AUTO_INCREMENT ,
  `DBSourceName` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `ContactName` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `ContactEmail` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `ContactTelephone` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `Continent` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `Country` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `StateProvince` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `verbatimCountry` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `County` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `Locality` VARCHAR(256) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `VegetationCategory` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `PlotCD` BIGINT(32) NULL DEFAULT NULL ,
  `PlotName` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `PlotCensusMethod` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `PlotArea` VARCHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `PlotAreaUnits` VARCHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `PlotShape` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `PlotDecimalLatitude` DECIMAL(10,5) NULL DEFAULT NULL ,
  `PlotDecimalLongitude` DECIMAL(10,5) NULL DEFAULT NULL ,
  `CoordinateUncertainty` DECIMAL(10,5) NULL DEFAULT NULL ,
  `CoordinateUncertaintyUnits` VARCHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `PlotReferenceX` DECIMAL(10,5) NULL DEFAULT NULL ,
  `PlotReferenceY` DECIMAL(10,5) NULL DEFAULT NULL ,
  `PlotMaxDBH` DECIMAL(10,5) NULL DEFAULT NULL ,
  `PlotMinDBH` DECIMAL(10,5) NULL DEFAULT NULL ,
  `test` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`DBPlotID`) ,
  INDEX `DBSourceName` (`DBSourceName` ASC) ,
  INDEX `PlotName_2` (`PlotName` ASC, `DBSourceName` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;


-- -----------------------------------------------------
-- Table `default_schema`.`geoIndividualObservation`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `default_schema`.`geoIndividualObservation` (
  `ID` INT(11) NOT NULL COMMENT 'Links 1:1 with DBPlotID' ,
  `countryISO` VARCHAR(25) NULL DEFAULT NULL ,
  `countryStd` VARCHAR(150) NULL DEFAULT NULL ,
  `isInCountry` INT(1) NULL DEFAULT NULL ,
  `countryDistError` DOUBLE NULL DEFAULT NULL ,
  `stateProvinceStd` VARCHAR(150) NULL DEFAULT NULL ,
  `isInStateProvince` INT(1) NULL DEFAULT NULL ,
  `stateProvinceDistError` DOUBLE NULL DEFAULT NULL ,
  `countyParishStd` VARCHAR(150) NULL DEFAULT NULL ,
  `isInCountyParish` INT(1) NULL DEFAULT NULL ,
  `countyParishDistError` DOUBLE NULL DEFAULT NULL ,
  `isValidLatLong` INT(1) NULL DEFAULT NULL ,
  `isCultivated` INT(1) NULL DEFAULT '0' ,
  `isCultivatedReason` VARCHAR(255) NULL DEFAULT NULL ,
  PRIMARY KEY (`ID`) ,
  INDEX `geoI_countryISO_NDX` (`countryISO` ASC) ,
  INDEX `geoI_countryStd_NDX` (`countryStd` ASC) ,
  INDEX `geoI_isInCountry_NDX` (`isInCountry` ASC) ,
  INDEX `geoI_countryDistError_NDX` (`countryDistError` ASC) ,
  INDEX `geoI_stateProvinceStd_NDX` (`stateProvinceStd` ASC) ,
  INDEX `geoI_isInStateProvince_NDX` (`isInStateProvince` ASC) ,
  INDEX `geoI_stateProvinceDistError_NDX` (`stateProvinceDistError` ASC) ,
  INDEX `geoI_countyParishStd_NDX` (`countyParishStd` ASC) ,
  INDEX `geoI_isInCountyParish_NDX` (`isInCountyParish` ASC) ,
  INDEX `geoI_countyParishDistError_NDX` (`countyParishDistError` ASC) ,
  INDEX `geoI_isValidLatLong_NDX` (`isValidLatLong` ASC) ,
  INDEX `geoI_isCultivated` (`isCultivated` ASC) ,
  INDEX `geoI_isCultivatedReason` (`isCultivatedReason` ASC) ,
  CONSTRAINT `geoindividualobservation_ibfk_1`
    FOREIGN KEY (`ID` )
    REFERENCES `default_schema`.`PlotMetaDataDimension` (`DBPlotID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `default_schema`.`geoPlotMetaDataDimension`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `default_schema`.`geoPlotMetaDataDimension` (
  `ID` INT(11) NOT NULL COMMENT 'Links 1:1 with DBPlotID' ,
  `countryISO` VARCHAR(25) NULL DEFAULT NULL ,
  `countryStd` VARCHAR(150) NULL DEFAULT NULL ,
  `isInCountry` INT(1) NULL DEFAULT NULL ,
  `countryDistError` DOUBLE NULL DEFAULT NULL ,
  `stateProvinceStd` VARCHAR(150) NULL DEFAULT NULL ,
  `isInStateProvince` INT(1) NULL DEFAULT NULL ,
  `stateProvinceDistError` DOUBLE NULL DEFAULT NULL ,
  `countyParishStd` VARCHAR(150) NULL DEFAULT NULL ,
  `isInCountyParish` INT(1) NULL DEFAULT NULL ,
  `countyParishDistError` DOUBLE NULL DEFAULT NULL ,
  `isValidLatLong` INT(1) NULL DEFAULT NULL ,
  `isCultivated` INT(1) NULL DEFAULT '0' ,
  `isCultivatedReason` VARCHAR(255) NULL DEFAULT NULL ,
  PRIMARY KEY (`ID`) ,
  INDEX `geoP_countryISO_NDX` (`countryISO` ASC) ,
  INDEX `geoP_countryStd_NDX` (`countryStd` ASC) ,
  INDEX `geoP_isInCountry_NDX` (`isInCountry` ASC) ,
  INDEX `geoP_countryDistError_NDX` (`countryDistError` ASC) ,
  INDEX `geoP_stateProvinceStd_NDX` (`stateProvinceStd` ASC) ,
  INDEX `geoP_isInStateProvince_NDX` (`isInStateProvince` ASC) ,
  INDEX `geoP_stateProvinceDistError_NDX` (`stateProvinceDistError` ASC) ,
  INDEX `geoP_countyParishStd_NDX` (`countyParishStd` ASC) ,
  INDEX `geoP_isInCountyParish_NDX` (`isInCountyParish` ASC) ,
  INDEX `geoP_countyParishDistError_NDX` (`countyParishDistError` ASC) ,
  INDEX `geoP_isValidLatLong_NDX` (`isValidLatLong` ASC) ,
  INDEX `geoP_isCultivated` (`isCultivated` ASC) ,
  INDEX `geo_isCultivatedReason` (`isCultivatedReason` ASC) ,
  CONSTRAINT `geoplotmetadatadimension_ibfk_1`
    FOREIGN KEY (`ID` )
    REFERENCES `default_schema`.`PlotMetaDataDimension` (`DBPlotID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


-- -----------------------------------------------------
-- Table `default_schema`.`TaxonDimension`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `default_schema`.`TaxonDimension` (
  `TaxonomyID` INT(11) NOT NULL AUTO_INCREMENT ,
  `FamilyName` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `GenusName` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `SpecificEpithet` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `InfraspecificEpithet` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `InfraspecificRank` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `Authority` VARCHAR(128) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `is_plotName` INT(1) UNSIGNED NULL DEFAULT '0' ,
  `taxonAuthorityVerbatim` VARCHAR(250) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `taxonMatched` VARCHAR(250) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `taxonMatchedAuthority` VARCHAR(250) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `unmatchedTerms` VARCHAR(250) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `matchScore` DECIMAL(3,2) NULL DEFAULT NULL ,
  `taxonMatchedAcceptance` VARCHAR(1) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `familyCorrected` VARCHAR(50) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `genusCorrected` VARCHAR(150) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `speciesCorrected` VARCHAR(255) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `taxonCorrected` VARCHAR(250) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `authorityCorrected` VARCHAR(250) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `taxonCorrectedTnrsID` INT(10) UNSIGNED NULL DEFAULT NULL ,
  `taxonCorrectedRank` VARCHAR(25) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `taxonCorrectedAcceptance` VARCHAR(1) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `morphospecies` VARCHAR(250) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `taxonMorphospecies` VARCHAR(250) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `is_scrubbed` INT(1) UNSIGNED NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`TaxonomyID`) ,
  INDEX `FamilyName` (`FamilyName` ASC) ,
  INDEX `GenusName` (`GenusName` ASC) ,
  INDEX `SpecificEpithet` (`SpecificEpithet` ASC) ,
  INDEX `InfraspecificRank` (`InfraspecificRank` ASC) ,
  INDEX `FamilyName_2` (`FamilyName` ASC, `GenusName` ASC, `SpecificEpithet` ASC, `InfraspecificEpithet` ASC, `InfraspecificRank` ASC, `Authority` ASC) ,
  INDEX `InfraspecificEpithet` (`InfraspecificEpithet` ASC) ,
  INDEX `taxonAuthorityVerbatim` (`taxonAuthorityVerbatim` ASC) ,
  INDEX `taxonCorrected` (`taxonCorrected` ASC) ,
  INDEX `taxonMorphospecies` (`taxonMorphospecies` ASC) ,
  INDEX `is_scrubbed` (`is_scrubbed` ASC) ,
  INDEX `TD_familyCorrected` (`familyCorrected` ASC) ,
  INDEX `TD_taxonMatchedAcceptance` (`taxonMatchedAcceptance` ASC) ,
  INDEX `TD_taxonCorrectedAcceptance` (`taxonCorrectedAcceptance` ASC) ,
  INDEX `TD_taxonCorrectedRank` (`taxonCorrectedRank` ASC) ,
  INDEX `taxonCorrectedTnrsID` (`taxonCorrectedTnrsID` ASC) ,
  INDEX `TD_is_plotName` (`is_plotName` ASC) ,
  INDEX `genusCorrected_idx` (`genusCorrected` ASC) ,
  INDEX `speciesCorrected` (`speciesCorrected` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin
ROW_FORMAT = FIXED;


-- -----------------------------------------------------
-- Table `default_schema`.`SpecimenSourceData`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `default_schema`.`SpecimenSourceData` (
  `DBSourceID` INT(11) NOT NULL AUTO_INCREMENT ,
  `DBSourceName` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `ContactName` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `ContactEmail` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `ContactTelephone` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `SpecimenInstitutionCD` VARCHAR(128) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `SpecimenCollectionCD` VARCHAR(128) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `test` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`DBSourceID`) ,
  INDEX `DBSourceName` (`DBSourceName` ASC) ,
  INDEX `SpecimenInstitutionCD` (`SpecimenInstitutionCD` ASC, `SpecimenCollectionCD` ASC, `DBSourceName` ASC) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;


-- -----------------------------------------------------
-- Table `default_schema`.`ObservationSpecimen`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `default_schema`.`ObservationSpecimen` (
  `ObservationSpecimenID` INT(11) NOT NULL AUTO_INCREMENT ,
  `DBSourceID` INT(11) NOT NULL ,
  `Continent` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `Country` VARCHAR(100) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `StateProvince` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `verbatimCountry` VARCHAR(100) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `County` VARCHAR(100) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `Locality` VARCHAR(1024) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `Collector` VARCHAR(256) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `IdentifiedBy` VARCHAR(128) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `IdentifiedDate` DATE NULL DEFAULT NULL ,
  `CoordinateUncertainty` DECIMAL(10,5) NULL DEFAULT NULL ,
  `CoordinateUncertaintyUnits` VARCHAR(10) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `VegetationCategory` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `test` TINYINT(3) UNSIGNED NOT NULL ,
  `Cultivated` INT(1) NOT NULL ,
  `CollectionNumber` VARCHAR(24) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  PRIMARY KEY (`ObservationSpecimenID`) ,
  INDEX `RefSpecimenSourceData421` (`DBSourceID` ASC) ,
  INDEX `DBSourceID` (`DBSourceID` ASC, `Continent` ASC, `Country` ASC, `StateProvince` ASC, `County` ASC, `Locality`(255) ASC, `Collector`(255) ASC, `IdentifiedBy` ASC, `IdentifiedDate` ASC, `CoordinateUncertainty` ASC, `CoordinateUncertaintyUnits` ASC, `VegetationCategory` ASC) ,
  CONSTRAINT `observationspecimen_ibfk_1`
    FOREIGN KEY (`DBSourceID` )
    REFERENCES `default_schema`.`SpecimenSourceData` (`DBSourceID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;


-- -----------------------------------------------------
-- Table `default_schema`.`IndividualObservation`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `default_schema`.`IndividualObservation` (
  `ObservationID` INT(11) NOT NULL AUTO_INCREMENT ,
  `ObservationType` INT(11) NULL DEFAULT NULL ,
  `PlotTreeTag` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `DBPlotID` INT(11) NULL DEFAULT NULL ,
  `SpecimenCatalogNO` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `ObservationSpecimenID` INT(11) NULL DEFAULT NULL ,
  `LatestDateCollected` DATE NULL DEFAULT NULL ,
  `DecimalLatitude` DECIMAL(10,5) NULL DEFAULT NULL ,
  `DecimalLongitude` DECIMAL(10,5) NULL DEFAULT NULL ,
  `NumberOfStems` INT(11) NULL DEFAULT NULL ,
  `BasalDiameter` DECIMAL(10,5) NULL DEFAULT NULL ,
  `Height` DECIMAL(10,5) NULL DEFAULT NULL ,
  `TaxonomyID` INT(11) NOT NULL ,
  `RawTreeID` INT(10) NULL DEFAULT NULL ,
  `test` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`ObservationID`) ,
  INDEX `RefPlotMetaDataDimension411` (`DBPlotID` ASC) ,
  INDEX `RefTaxonDimension381` (`TaxonomyID` ASC) ,
  INDEX `ObservationSpecimenID` (`ObservationSpecimenID` ASC) ,
  CONSTRAINT `individualobservation_ibfk_3`
    FOREIGN KEY (`TaxonomyID` )
    REFERENCES `default_schema`.`TaxonDimension` (`TaxonomyID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `individualobservation_ibfk_1`
    FOREIGN KEY (`ObservationSpecimenID` )
    REFERENCES `default_schema`.`ObservationSpecimen` (`ObservationSpecimenID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `individualobservation_ibfk_2`
    FOREIGN KEY (`DBPlotID` )
    REFERENCES `default_schema`.`PlotMetaDataDimension` (`DBPlotID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;


-- -----------------------------------------------------
-- Table `default_schema`.`IndividualObservationMeasurement`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `default_schema`.`IndividualObservationMeasurement` (
  `ObservationID` INT(11) NOT NULL ,
  `StemNO` INT(11) NOT NULL AUTO_INCREMENT ,
  `ObservationNO` INT(11) NOT NULL ,
  `PrimaryStem` INT(11) NULL DEFAULT NULL ,
  `DBH` DECIMAL(10,5) NULL DEFAULT NULL ,
  `HOM` DECIMAL(10,5) NULL DEFAULT NULL ,
  `test` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`StemNO`) ,
  UNIQUE INDEX `ObservationID` (`ObservationID` ASC, `StemNO` ASC, `ObservationNO` ASC) ,
  INDEX `RefIndividualObservation11` (`ObservationID` ASC) ,
  CONSTRAINT `IndividualObservationMeasurement_ibfk_1`
    FOREIGN KEY (`ObservationID` )
    REFERENCES `default_schema`.`IndividualObservation` (`ObservationID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;


-- -----------------------------------------------------
-- Table `default_schema`.`PlotTimeDimension`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `default_schema`.`PlotTimeDimension` (
  `PlotTimeID` INT(11) NOT NULL AUTO_INCREMENT ,
  `CensusID` INT(11) NULL DEFAULT NULL ,
  `CensusName` VARCHAR(32) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `CensusStartDate` DATE NULL DEFAULT NULL ,
  `CensusEndDate` DATE NULL DEFAULT NULL ,
  `DBPlotID` INT(11) NOT NULL ,
  `test` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`PlotTimeID`) ,
  INDEX `Ref613` (`DBPlotID` ASC) ,
  INDEX `CensusName_2` (`CensusName` ASC, `DBPlotID` ASC, `CensusID` ASC) ,
  INDEX `CensusName_5` (`CensusName` ASC, `CensusID` ASC) ,
  CONSTRAINT `plottimedimension_ibfk_1`
    FOREIGN KEY (`DBPlotID` )
    REFERENCES `default_schema`.`PlotMetaDataDimension` (`DBPlotID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;


-- -----------------------------------------------------
-- Table `default_schema`.`PlotAggregateFact`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `default_schema`.`PlotAggregateFact` (
  `DBPlotID` INT(11) NOT NULL ,
  `PlotTimeID` INT(11) NOT NULL ,
  `TotalIndividuals` INT(11) NULL DEFAULT NULL ,
  `[REDACTED]` INT(11) NULL DEFAULT NULL ,
  `[REDACTED].5cm` INT(11) NULL DEFAULT NULL ,
  `[REDACTED]` INT(11) NULL DEFAULT NULL ,
  `PercentCover` DECIMAL(10,5) NULL DEFAULT NULL ,
  `TaxonomyID` INT(11) NOT NULL ,
  `del` INT(1) UNSIGNED NULL DEFAULT NULL ,
  `test` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`DBPlotID`, `PlotTimeID`, `TaxonomyID`) ,
  INDEX `RefPlotMetaDataDimension61` (`DBPlotID` ASC) ,
  INDEX `RefPlotTimeDimension371` (`PlotTimeID` ASC) ,
  INDEX `RefTaxonDimension181` (`TaxonomyID` ASC) ,
  CONSTRAINT `plotaggregatefact_ibfk_3`
    FOREIGN KEY (`TaxonomyID` )
    REFERENCES `default_schema`.`TaxonDimension` (`TaxonomyID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `plotaggregatefact_ibfk_1`
    FOREIGN KEY (`DBPlotID` )
    REFERENCES `default_schema`.`PlotMetaDataDimension` (`DBPlotID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `plotaggregatefact_ibfk_2`
    FOREIGN KEY (`PlotTimeID` )
    REFERENCES `default_schema`.`PlotTimeDimension` (`PlotTimeID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;


-- -----------------------------------------------------
-- Table `default_schema`.`TaxonDataSource`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `default_schema`.`TaxonDataSource` (
  `TaxonDataSourceID` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `DataSourceID` INT(10) UNSIGNED NOT NULL ,
  `TaxonomyID` INT(11) NOT NULL ,
  `TotalObservations` INT(10) UNSIGNED NULL DEFAULT NULL ,
  `GeoreferencedObservations` INT(10) UNSIGNED NULL DEFAULT NULL ,
  PRIMARY KEY (`TaxonDataSourceID`) ,
  INDEX `DataSourceID` (`DataSourceID` ASC) ,
  INDEX `TaxonomyID` (`TaxonomyID` ASC) ,
  CONSTRAINT `TaxonDataSource_ibfk_2`
    FOREIGN KEY (`TaxonomyID` )
    REFERENCES `default_schema`.`TaxonDimension` (`TaxonomyID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `TaxonDataSource_ibfk_1`
    FOREIGN KEY (`DataSourceID` )
    REFERENCES `default_schema`.`DataSource` (`DataSourceID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8, 
COMMENT = 'Links taxa to data sources in which they are used' ;


-- -----------------------------------------------------
-- Table `default_schema`.`TaxonObservation`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `default_schema`.`TaxonObservation` (
  `TaxonomyObservationID` INT(11) NOT NULL AUTO_INCREMENT ,
  `TaxonomyID` INT(11) NULL DEFAULT NULL ,
  `PlotOrSpecimenFlag` INT(11) NULL DEFAULT NULL ,
  `DBPlotID` INT(11) NOT NULL ,
  `DBSourceID` INT(11) NOT NULL ,
  `GBIFScientificNameOriginal` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `GBIFFamilyOriginal` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `GBIFGenusOriginal` VARCHAR(64) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL ,
  `test` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0' ,
  PRIMARY KEY (`TaxonomyObservationID`) ,
  INDEX `RefTaxonDimension301` (`TaxonomyID` ASC) ,
  INDEX `Ref614` (`DBPlotID` ASC) ,
  INDEX `TaxonomyID_2` (`TaxonomyID` ASC, `DBPlotID` ASC) ,
  INDEX `DBSourceID` (`DBSourceID` ASC) ,
  CONSTRAINT `taxonobservation_ibfk_3`
    FOREIGN KEY (`TaxonomyID` )
    REFERENCES `default_schema`.`TaxonDimension` (`TaxonomyID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `taxonobservation_ibfk_1`
    FOREIGN KEY (`DBPlotID` )
    REFERENCES `default_schema`.`PlotMetaDataDimension` (`DBPlotID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `taxonobservation_ibfk_2`
    FOREIGN KEY (`DBSourceID` )
    REFERENCES `default_schema`.`SpecimenSourceData` (`DBSourceID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;


-- -----------------------------------------------------
-- Table `default_schema`.`TraitObservation`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `default_schema`.`TraitObservation` (
  `TraitObservationID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT ,
  `TaxonomyID` INT(11) NOT NULL ,
  `Family` VARCHAR(100) NULL DEFAULT NULL ,
  `Genus` VARCHAR(100) NULL DEFAULT NULL ,
  `SpecificEpithet` VARCHAR(100) NULL DEFAULT NULL ,
  `InfraspecificRank` VARCHAR(25) NULL DEFAULT NULL ,
  `InfraspecificEpithet` VARCHAR(100) NULL DEFAULT NULL ,
  `Taxon` VARCHAR(255) NULL DEFAULT NULL ,
  `AuthorOfScientificName` VARCHAR(255) NULL DEFAULT NULL ,
  `TaxonAuthor` VARCHAR(255) NULL DEFAULT NULL ,
  `Habit` VARCHAR(50) NULL DEFAULT NULL ,
  `TraitName` VARCHAR(200) NULL DEFAULT NULL ,
  `TraitValue` VARCHAR(100) NULL DEFAULT NULL ,
  `Unit` VARCHAR(100) NULL DEFAULT NULL ,
  `Method` VARCHAR(255) NULL DEFAULT NULL ,
  `Region` VARCHAR(100) NULL DEFAULT NULL ,
  `Country` VARCHAR(100) NULL DEFAULT NULL ,
  `StateProvince` VARCHAR(100) NULL DEFAULT NULL ,
  `LowerPolitical` VARCHAR(100) NULL DEFAULT NULL ,
  `LocalityDescription` VARCHAR(100) NULL DEFAULT NULL ,
  `VerbatimLatitude` VARCHAR(150) NULL DEFAULT NULL ,
  `VerbatimLongitude` VARCHAR(150) NULL DEFAULT NULL ,
  `VerbatimElevation` VARCHAR(150) NULL DEFAULT NULL ,
  `Source` VARCHAR(100) NULL DEFAULT NULL ,
  `URLSource` VARCHAR(100) NULL DEFAULT NULL ,
  `SourceCitation` VARCHAR(1000) NULL DEFAULT NULL ,
  `SourceID` VARCHAR(255) NULL DEFAULT NULL ,
  `VisitingDate` VARCHAR(100) NULL DEFAULT NULL ,
  `ReferenceID` INT(11) UNSIGNED NULL DEFAULT NULL ,
  `Access` VARCHAR(100) NULL DEFAULT NULL ,
  `Project_PI` VARCHAR(255) NULL DEFAULT NULL ,
  `Project_PI_contact` VARCHAR(100) NULL DEFAULT NULL ,
  `Observation` VARCHAR(255) NULL DEFAULT NULL ,
  `Authorship` VARCHAR(255) NULL DEFAULT NULL ,
  `AuthorshipContact` VARCHAR(100) NULL DEFAULT NULL ,
  PRIMARY KEY (`TraitObservationID`) ,
  INDEX `TaxonomyID` (`TaxonomyID` ASC) ,
  INDEX `Family` (`Family` ASC) ,
  INDEX `Taxon` (`Taxon` ASC) ,
  INDEX `TraitName` (`TraitName` ASC) ,
  INDEX `ReferenceID` (`ReferenceID` ASC) ,
  INDEX `TaxonAuthor` (`TaxonAuthor` ASC) ,
  CONSTRAINT `traitobservation_ibfk_1`
    FOREIGN KEY (`TaxonomyID` )
    REFERENCES `default_schema`.`TaxonDimension` (`TaxonomyID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8, 
COMMENT = 'Species trait observations' ;


-- -----------------------------------------------------
-- Table `default_schema`.`viewFullOccurrence`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `default_schema`.`viewFullOccurrence` (
  `OccurID` INT(11) NOT NULL AUTO_INCREMENT ,
  `DBPlotID` INT(11) NOT NULL ,
  `ObservationID` INT(11) NULL DEFAULT NULL ,
  `Family` VARCHAR(64) NULL DEFAULT NULL ,
  `TaxonomyID` INT(11) NOT NULL ,
  `OrigGenus` VARCHAR(64) NULL DEFAULT NULL ,
  `OrigSpecies` VARCHAR(64) NULL DEFAULT NULL ,
  `TaxonCorrected` VARCHAR(250) NULL DEFAULT NULL ,
  `Genus` VARCHAR(64) NULL DEFAULT NULL ,
  `Species` VARCHAR(64) NULL DEFAULT NULL ,
  `Latin` VARCHAR(64) NULL DEFAULT NULL ,
  `Rank` VARCHAR(16) NULL DEFAULT NULL ,
  `Accepted` VARCHAR(2) NULL DEFAULT NULL ,
  `TaxonMorphoSpecies` VARCHAR(255) NULL DEFAULT NULL ,
  `isCultivated` INT(1) NULL DEFAULT '0' ,
  `isCultivatedReason` VARCHAR(255) NULL DEFAULT NULL ,
  `CountryOrig` VARCHAR(100) NULL DEFAULT NULL ,
  `CountryStd` VARCHAR(150) NULL DEFAULT NULL ,
  `CountryError` DOUBLE NULL DEFAULT NULL ,
  `ProvinceStd` VARCHAR(64) NULL DEFAULT NULL ,
  `ProvinceError` DOUBLE NULL DEFAULT NULL ,
  `County` VARCHAR(64) NULL DEFAULT NULL ,
  `Plot` VARCHAR(32) NULL DEFAULT NULL ,
  `PlotArea` VARCHAR(10) NULL DEFAULT NULL ,
  `Latitude` DECIMAL(10,5) NULL DEFAULT NULL ,
  `Longitude` DECIMAL(10,5) NULL DEFAULT NULL ,
  `isValidLatLong` INT(1) NULL DEFAULT NULL ,
  `Date` DATE NULL DEFAULT NULL ,
  `SurveyType` VARCHAR(10) NULL DEFAULT NULL ,
  `Abund` INT(11) NULL DEFAULT NULL ,
  `Abund1` INT(11) NULL DEFAULT NULL ,
  `Abund2.5` INT(11) NULL DEFAULT NULL ,
  `Abund10` INT(11) NULL DEFAULT NULL ,
  `PctCover` DECIMAL(10,5) NULL DEFAULT NULL ,
  `DataSource` VARCHAR(128) NULL DEFAULT NULL ,
  PRIMARY KEY (`OccurID`) ,
  INDEX `DBPlotID` (`DBPlotID` ASC) ,
  INDEX `ObservationID` (`ObservationID` ASC) ,
  INDEX `Family` (`Family`(16) ASC) ,
  INDEX `TaxonCorrected` (`TaxonCorrected`(25) ASC) ,
  INDEX `Latin` (`Latin`(25) ASC) ,
  INDEX `TaxonomyID` (`TaxonomyID` ASC) ,
  INDEX `Genus` (`Genus`(16) ASC) ,
  INDEX `Species` (`Species`(20) ASC) ,
  INDEX `ORIGLATIN` (`OrigGenus`(16) ASC, `OrigSpecies`(20) ASC) ,
  INDEX `CountryStd` (`CountryStd` ASC) ,
  INDEX `SurveyType` (`SurveyType` ASC) ,
  INDEX `DataSource` (`DataSource` ASC) ,
  INDEX `Rank` (`Rank` ASC) ,
  INDEX `Accepted` (`Accepted` ASC) ,
  INDEX `isCultivated` (`isCultivated` ASC) ,
  INDEX `isCultivatedReason` (`isCultivatedReason` ASC) ,
  INDEX `isValidLatLong` (`isValidLatLong` ASC) ,
  INDEX `CountryError` (`CountryError` ASC) ,
  INDEX `ProvinceError` (`ProvinceError` ASC) ,
  CONSTRAINT `viewFullOccurrence_ibfk_1`
    FOREIGN KEY (`ObservationID` )
    REFERENCES `default_schema`.`IndividualObservation` (`ObservationID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `viewFullOccurrence_ibfk_3`
    FOREIGN KEY (`DBPlotID` )
    REFERENCES `default_schema`.`PlotMetaDataDimension` (`DBPlotID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `viewFullOccurrence_ibfk_2`
    FOREIGN KEY (`TaxonomyID` )
    REFERENCES `default_schema`.`TaxonDimension` (`TaxonomyID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
