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 , `TotalIndividualsAbove1cm` INT(11) NULL DEFAULT NULL , `TotalIndividualsAbove2.5cm` INT(11) NULL DEFAULT NULL , `TotalIndividualsAbove10cm` 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;