-- --------------------------------------------------------
-- Server Version:               5.5.50-0+deb8u1 - (Debian)
-- Server Betriebssystem:        debian-linux-gnu
-- HeidiSQL Version:             9.3.0.5124
-- --------------------------------------------------------


-- Exportiere Struktur von Tabelle wfo.association
CREATE TABLE IF NOT EXISTS `association` (
  `associationid` int(11) NOT NULL AUTO_INCREMENT,
  `fk_tripleidstoreid1` int(11) DEFAULT NULL,
  `fk_tripleidstoreid2` int(11) DEFAULT NULL,
  `associationtype` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`associationid`),
  KEY `assoIdx1` (`fk_tripleidstoreid1`),
  KEY `assoIdx2` (`fk_tripleidstoreid2`),
  CONSTRAINT `fk_association_1` FOREIGN KEY (`fk_tripleidstoreid1`) REFERENCES `tripleidstore` (`tripleidstoreid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_association_2` FOREIGN KEY (`fk_tripleidstoreid2`) REFERENCES `tripleidstore` (`tripleidstoreid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.backboneinfo
CREATE TABLE IF NOT EXISTS `backboneinfo` (
  `idbackboneinfo` int(11) NOT NULL AUTO_INCREMENT,
  `backbonefullname` varchar(355) DEFAULT NULL,
  `backboneaccepted` varchar(355) DEFAULT NULL,
  `backboneref` varchar(355) DEFAULT NULL,
  `fk_identificationid` int(11) DEFAULT NULL,
  PRIMARY KEY (`idbackboneinfo`),
  KEY `fk_backboneinfo_1_idx` (`fk_identificationid`),
  CONSTRAINT `fk_backboneinfo_1` FOREIGN KEY (`fk_identificationid`) REFERENCES `identification` (`identificationid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.bio_datasource
CREATE TABLE IF NOT EXISTS `bio_datasource` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `country` varchar(255) DEFAULT NULL,
  `harvested_count` int(11) NOT NULL DEFAULT '0',
  `harvester_factory` varchar(255) DEFAULT NULL,
  `last_harvested` datetime DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `parameters_as_json` text,
  `provider_name` varchar(255) DEFAULT NULL,
  `started` datetime DEFAULT NULL,
  `hidden` datetime DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `target_count` int(11) NOT NULL DEFAULT '0',
  `uddi_key` varchar(255) DEFAULT NULL,
  `url` varchar(255) DEFAULT NULL,
  `fk_providerid` int(11) DEFAULT NULL,
  `last_inventory` datetime DEFAULT NULL,
  `last_inventory_processing` datetime DEFAULT NULL,
  `last_harvested_processing` datetime DEFAULT NULL,
  `isAssociated` int(11) DEFAULT NULL,
  `isPrincipal` int(11) DEFAULT NULL,
  `copyrights` text,
  `termsofuse` text,
  `citation` text,
  `hasAssociatedUnits` int(11) DEFAULT NULL,
  `datasetowner` varchar(255) DEFAULT NULL,
  `datasetownerabbrev` varchar(45) DEFAULT NULL,
  `hasInternalAssociatedUnits` int(11) DEFAULT NULL,
  `basedirectory` varchar(255) DEFAULT NULL,
  `parentInstitution` varchar(45) DEFAULT NULL,
  `fk_schemaid` int(11) DEFAULT '-1',
  `fk_protocolid` int(11) DEFAULT '-1',
  `institution` varchar(45) DEFAULT NULL,
  `city` varchar(45) DEFAULT NULL,
  `indexHerbarorium` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_bio_datasource_1` (`fk_providerid`),
  KEY `idx1` (`started`),
  KEY `idx2` (`last_harvested`),
  KEY `isasso` (`isAssociated`),
  KEY `fk_bio_datasource_3_idx` (`fk_protocolid`),
  KEY `fk_bio_datasource_2_idx` (`fk_schemaid`),
  CONSTRAINT `fk_bio_datasource_1` FOREIGN KEY (`fk_providerid`) REFERENCES `provider` (`providerid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_bio_datasource_2` FOREIGN KEY (`fk_schemaid`) REFERENCES `standardschema` (`schemaid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_bio_datasource_3` FOREIGN KEY (`fk_protocolid`) REFERENCES `protocol` (`protocolid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.collectors
CREATE TABLE IF NOT EXISTS `collectors` (
  `collectorid` int(11) NOT NULL AUTO_INCREMENT,
  `collectorname` varchar(512) DEFAULT NULL,
  `collectorsequence` int(11) DEFAULT NULL,
  `fk_occurrenceid` int(11) DEFAULT NULL,
  `collectornames` varchar(512) DEFAULT NULL,
  PRIMARY KEY (`collectorid`),
  KEY `fk_collectors_1` (`fk_occurrenceid`),
  CONSTRAINT `fk_collectors_1` FOREIGN KEY (`fk_occurrenceid`) REFERENCES `rawoccurrence` (`occurrenceid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.coordinates
CREATE TABLE IF NOT EXISTS `coordinates` (
  `coordinatesid` int(11) NOT NULL,
  `longitude` float DEFAULT NULL,
  `latitude` float DEFAULT NULL,
  `coordinatesMethod` varchar(255) DEFAULT NULL,
  `coordinateErrorDistanceInMeters` int(11) DEFAULT NULL,
  `fk_occurrenceid` int(11) DEFAULT NULL,
  `spatialDatum` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`coordinatesid`),
  KEY `fk_coord_1` (`fk_occurrenceid`),
  CONSTRAINT `fk_coordinates_raw` FOREIGN KEY (`coordinatesid`) REFERENCES `rawcoordinates` (`rawcoordinatesid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_coord_1` FOREIGN KEY (`fk_occurrenceid`) REFERENCES `rawoccurrence` (`occurrenceid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.coordinatestest
CREATE TABLE IF NOT EXISTS `coordinatestest` (
  `fk_coordinatesid` int(11) NOT NULL,
  `test` varchar(155) NOT NULL DEFAULT '',
  `result` varchar(155) NOT NULL DEFAULT '',
  `log` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`fk_coordinatesid`,`test`),
  CONSTRAINT `fk_coordinatestest_1` FOREIGN KEY (`fk_coordinatesid`) REFERENCES `rawcoordinates` (`rawcoordinatesid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.counts
CREATE TABLE IF NOT EXISTS `counts` (
  `nbOcccurrences` int(11) DEFAULT NULL,
  `nbRawOccurrences` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.datetest
CREATE TABLE IF NOT EXISTS `datetest` (
  `fk_occurrenceid` int(11) NOT NULL,
  `test` varchar(155) NOT NULL DEFAULT '',
  `result` varchar(45) DEFAULT NULL,
  `log` varchar(155) DEFAULT NULL,
  PRIMARY KEY (`fk_occurrenceid`,`test`),
  CONSTRAINT `fk_datetest_1` FOREIGN KEY (`fk_occurrenceid`) REFERENCES `rawoccurrence` (`occurrenceid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Funktion wfo.EXTRACT_YEAR
DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `EXTRACT_YEAR`(datestring VARCHAR(32)) RETURNS char(4) CHARSET utf8
    DETERMINISTIC
BEGIN

  DECLARE resultyear INTEGER DEFAULT NULL;
  DECLARE x INT DEFAULT 1;

  mainloop: WHILE x < CHAR_LENGTH(datestring) - 2 DO

    IF SUBSTRING(datestring,x,4) REGEXP "[0-9]{4}" THEN

      IF x = CHAR_LENGTH(datestring)-3 THEN

        SET resultyear = CAST(SUBSTRING(datestring,x,4) AS UNSIGNED INTEGER);

        IF resultyear > 1000 AND resultyear < 2050 THEN

          LEAVE mainloop;

        END IF;

      ELSEIF NOT SUBSTRING(datestring,x+4,1) REGEXP "[0-9]" THEN

        SET resultyear = CAST(SUBSTRING(datestring,x,4) AS UNSIGNED INTEGER);

        IF resultyear > 1000 AND resultyear < 2050 THEN

          LEAVE mainloop;

        END IF;

      END IF;

    END IF;

    SET x = x + 1;

  END WHILE;

  IF resultyear > 1000 AND resultyear < 2050 THEN

    RETURN resultyear;

  END IF;

  RETURN NULL;
END//
DELIMITER ;

-- Exportiere Struktur von Tabelle wfo.ggbn
CREATE TABLE IF NOT EXISTS `ggbn` (
  `ggbnid` int(11) NOT NULL AUTO_INCREMENT,
  `absorbance230` varchar(45) DEFAULT NULL,
  `absorbance280` varchar(45) DEFAULT NULL,
  `preparationDate` date DEFAULT NULL,
  `concentration` varchar(45) DEFAULT NULL,
  `qualityCheckDate` date DEFAULT NULL,
  `blockedUntil` date DEFAULT NULL,
  `tissueABCD206only` varchar(255) DEFAULT NULL,
  `preparationStaff` varchar(255) DEFAULT NULL,
  `preparationMethod` varchar(255) DEFAULT NULL,
  `fk_tripleidstoreid` int(11) DEFAULT NULL,
  `quality` varchar(155) DEFAULT NULL,
  PRIMARY KEY (`ggbnid`),
  KEY `fk_ggbn_1_idx` (`tissueABCD206only`),
  KEY `fk_ggbn_1_idx1` (`fk_tripleidstoreid`),
  CONSTRAINT `fk_ggbn_1` FOREIGN KEY (`fk_tripleidstoreid`) REFERENCES `tripleidstore` (`tripleidstoreid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.hierarchy
CREATE TABLE IF NOT EXISTS `hierarchy` (
  `fk_identificationid` int(11) NOT NULL,
  `domain` varchar(45) DEFAULT NULL,
  `kingdom` varchar(45) DEFAULT NULL,
  `phylum` varchar(45) DEFAULT NULL,
  `classis` varchar(45) DEFAULT NULL,
  `ordo` varchar(45) DEFAULT NULL,
  `family` varchar(45) DEFAULT NULL,
  `genus` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`fk_identificationid`),
  CONSTRAINT `fk_hierarchy_1` FOREIGN KEY (`fk_identificationid`) REFERENCES `identification` (`identificationid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.identification
CREATE TABLE IF NOT EXISTS `identification` (
  `identificationid` int(11) NOT NULL,
  `fk_occurrenceid` int(11) DEFAULT NULL,
  `created_when` datetime DEFAULT NULL,
  `fullScientificName` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `preferred` tinyint(1) DEFAULT NULL,
  `mineral` int(11) DEFAULT '0',
  `ipniKey` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`identificationid`),
  KEY `fk_identification_1` (`fk_occurrenceid`),
  KEY `fullScientificName` (`fullScientificName`),
  CONSTRAINT `fk_identification_1` FOREIGN KEY (`fk_occurrenceid`) REFERENCES `rawoccurrence` (`occurrenceid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_identification_2` FOREIGN KEY (`identificationid`) REFERENCES `rawidentification` (`rawidentificationid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.identificationtohigher
CREATE TABLE IF NOT EXISTS `identificationtohigher` (
  `identificationtohigherid` int(11) NOT NULL AUTO_INCREMENT,
  `fk_rawidentificationid` int(11) NOT NULL DEFAULT '0',
  `fk_highertaxaid` int(11) NOT NULL DEFAULT '0',
  `newid` int(11) DEFAULT NULL,
  PRIMARY KEY (`fk_rawidentificationid`,`fk_highertaxaid`),
  UNIQUE KEY `identificationtohigherid_UNIQUE` (`identificationtohigherid`),
  KEY `fk_identificationtohigher_1` (`fk_highertaxaid`),
  CONSTRAINT `fk_identificationtohigher_1` FOREIGN KEY (`fk_highertaxaid`) REFERENCES `rawhigher` (`highertaxaid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_identificationtohigher_2` FOREIGN KEY (`fk_rawidentificationid`) REFERENCES `rawidentification` (`rawidentificationid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.identifiers
CREATE TABLE IF NOT EXISTS `identifiers` (
  `identifierid` int(11) NOT NULL AUTO_INCREMENT,
  `identifiername` varchar(255) DEFAULT NULL,
  `fk_rawidentificationid` int(11) DEFAULT NULL,
  PRIMARY KEY (`identifierid`),
  KEY `fk_identifiers_1` (`fk_rawidentificationid`),
  CONSTRAINT `fk_identifiers_1` FOREIGN KEY (`fk_rawidentificationid`) REFERENCES `rawidentification` (`rawidentificationid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.job
CREATE TABLE IF NOT EXISTS `job` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created` datetime DEFAULT NULL,
  `data_as_json` text,
  `description` varchar(255) DEFAULT NULL,
  `instance_id` varchar(255) DEFAULT NULL,
  `job_class_name` varchar(255) DEFAULT NULL,
  `job_group` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `next_fire_time` datetime DEFAULT NULL,
  `running_group` varchar(255) DEFAULT NULL,
  `started` datetime DEFAULT NULL,
  `thread_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.loan
CREATE TABLE IF NOT EXISTS `loan` (
  `loanid` int(11) NOT NULL AUTO_INCREMENT,
  `blockedUntil` varchar(45) DEFAULT NULL,
  `blocked` varchar(45) DEFAULT NULL,
  `disposition` varchar(45) DEFAULT NULL,
  `conditions` varchar(255) DEFAULT NULL,
  `fk_tripleidstoreid` int(11) DEFAULT NULL,
  `blockedUntilDate` date DEFAULT NULL,
  `disposition_clean` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`loanid`),
  KEY `fk_loan_1_idx` (`fk_tripleidstoreid`),
  CONSTRAINT `fk_loan_1` FOREIGN KEY (`fk_tripleidstoreid`) REFERENCES `tripleidstore` (`tripleidstoreid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.log_event
CREATE TABLE IF NOT EXISTS `log_event` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `group_id` int(11) DEFAULT NULL,
  `info_as_json` text,
  `instance_id` varchar(255) DEFAULT NULL,
  `level` int(11) NOT NULL,
  `message` text,
  `message_params_as_json` text,
  `timestamp` datetime DEFAULT NULL,
  `bio_datasource_fk` int(11) NOT NULL DEFAULT '0',
  `user_fk` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK8805F5DF20F6AF5F` (`bio_datasource_fk`),
  KEY `FK8805F5DF20F6ALLL` (`user_fk`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.measurementorfacts
CREATE TABLE IF NOT EXISTS `measurementorfacts` (
  `measurementorfactsid` int(11) NOT NULL AUTO_INCREMENT,
  `measurementtext` varchar(255) DEFAULT NULL,
  `measuredBy` varchar(75) DEFAULT NULL,
  `measurementDateTime` datetime DEFAULT NULL,
  `duration` varchar(45) DEFAULT NULL,
  `method` varchar(255) DEFAULT NULL,
  `parameter` varchar(455) DEFAULT NULL,
  `appliesTo` varchar(255) DEFAULT NULL,
  `lowerValue` varchar(45) DEFAULT NULL,
  `upperValue` varchar(45) DEFAULT NULL,
  `unitOfMeasurement` varchar(45) DEFAULT NULL,
  `accuracy` varchar(45) DEFAULT NULL,
  `reference` varchar(255) DEFAULT NULL,
  `isQuantitative` int(11) DEFAULT NULL,
  `fk_occurrenceid` int(11) DEFAULT NULL,
  `usedBy` varchar(45) DEFAULT 'Unit',
  PRIMARY KEY (`measurementorfactsid`),
  KEY `fk_measurementorfacts_1_idx` (`fk_occurrenceid`),
  CONSTRAINT `fk_measurementorfacts_1` FOREIGN KEY (`fk_occurrenceid`) REFERENCES `rawoccurrence` (`occurrenceid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.multimedia
CREATE TABLE IF NOT EXISTS `multimedia` (
  `multimediaid` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(45) DEFAULT NULL,
  `format` varchar(45) DEFAULT NULL,
  `sizeunit` varchar(45) DEFAULT NULL,
  `size` varchar(45) DEFAULT NULL,
  `created_when` date DEFAULT NULL,
  `fk_tripleidstoreid` int(11) DEFAULT NULL,
  `url` varchar(2048) DEFAULT NULL,
  `copyrights` text,
  `termsofuse` text,
  `licenses` text,
  `relatedResource` varchar(355) DEFAULT NULL,
  `comment` varchar(4096) DEFAULT NULL,
  `context` varchar(512) DEFAULT NULL,
  `creator` varchar(255) DEFAULT NULL,
  `createdDate` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`multimediaid`),
  KEY `fk_multimedia_1` (`fk_tripleidstoreid`),
  CONSTRAINT `fk_mm_1` FOREIGN KEY (`fk_tripleidstoreid`) REFERENCES `tripleidstore` (`tripleidstoreid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- New table for named areas
CREATE TABLE IF NOT EXISTS namedarea(
	namedareaid int AUTO_INCREMENT primary key,
	fk_occurrenceid int,
	sequence tinyint,
	class varchar(255),
	name varchar(255),
	constraint fk_namedareas1 FOREIGN KEY (fk_occurrenceid) REFERENCES rawoccurrence (occurrenceid) ON DELETE CASCADE ON UPDATE CASCADE
);
-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.occurrence
CREATE TABLE IF NOT EXISTS `occurrence` (
  `occurrenceid` int(11) NOT NULL,
  `created_when` datetime DEFAULT NULL,
  `gatheringdate` varchar(45) DEFAULT NULL,
  `gatheringdatebegin` date DEFAULT NULL,
  `gatheringdateend` date DEFAULT NULL,
  `country` varchar(155) DEFAULT NULL,
  `isocountrycode` varchar(5) DEFAULT NULL,
  `locality` text,
  `biotope` text,
  `fk_recordbasisid` int(11) DEFAULT NULL,
  `fk_kindofunitid` int(11) DEFAULT NULL,
  `collectornumber` varchar(255) DEFAULT NULL,
  `fieldnumber` varchar(255) DEFAULT NULL,
  `accessionnumber` varchar(255) DEFAULT NULL,
  `fk_datasourceid` int(11) DEFAULT NULL,
  `minDepth` varchar(45) DEFAULT NULL,
  `maxDepth` varchar(45) DEFAULT NULL,
  `minAltitude` varchar(45) DEFAULT NULL,
  `maxAltitude` varchar(45) DEFAULT NULL,
  `fk_sha1sumid` int(11) DEFAULT NULL,
  `fk_tripleidstoreid` int(11) DEFAULT NULL,
  `gatheringareas` text,
  `establishmentMeans` varchar(255) DEFAULT NULL,
  `recordURI` varchar(455) DEFAULT NULL,
  `gatheringyear` int(4) DEFAULT NULL,
  `water` varchar(255) DEFAULT NULL,
  `ipen` varchar(255) DEFAULT NULL,
  `hardiness` char(1) DEFAULT NULL,
  PRIMARY KEY (`occurrenceid`),
  KEY `fk_occurrence_1` (`fk_datasourceid`),
  KEY `fk_occurrence_2_idx` (`fk_recordbasisid`),
  KEY `fk_occurrence_3_idx` (`fk_kindofunitid`),
  KEY `fk_occurrence_4_idx` (`fk_sha1sumid`),
  KEY `fk_occurrence_8_idx` (`fk_tripleidstoreid`),
  CONSTRAINT `fk_mainOccurrence` FOREIGN KEY (`occurrenceid`) REFERENCES `rawoccurrence` (`occurrenceid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_occurrence_1` FOREIGN KEY (`fk_datasourceid`) REFERENCES `bio_datasource` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_occurrence_2` FOREIGN KEY (`fk_recordbasisid`) REFERENCES `recordbasis` (`recordbasisid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_occurrence_3` FOREIGN KEY (`fk_kindofunitid`) REFERENCES `unitkind` (`unitkindid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_occurrence_4` FOREIGN KEY (`fk_sha1sumid`) REFERENCES `sha1responses` (`shaid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.occurrencetest
CREATE TABLE IF NOT EXISTS `occurrencetest` (
  `fk_occurrenceid` int(11) NOT NULL,
  `test` varchar(155) NOT NULL DEFAULT '',
  `result` varchar(45) NOT NULL DEFAULT '',
  `log` varchar(255) NOT NULL DEFAULT '',
  PRIMARY KEY (`fk_occurrenceid`,`test`),
  CONSTRAINT `fk_occurrencetest_1` FOREIGN KEY (`fk_occurrenceid`) REFERENCES `rawoccurrence` (`occurrenceid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.occurrenceToPreservation
CREATE TABLE IF NOT EXISTS `occurrenceToPreservation` (
  `fk_occurrenceid` int(11) NOT NULL,
  `fk_preservationtypeid` int(11) NOT NULL,
  PRIMARY KEY (`fk_occurrenceid`,`fk_preservationtypeid`),
  KEY `fk_occurrenceToPreservation_2_idx` (`fk_preservationtypeid`),
  CONSTRAINT `fk_occurrenceToPreservation_1` FOREIGN KEY (`fk_occurrenceid`) REFERENCES `rawoccurrence` (`occurrenceid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_occurrenceToPreservation_2` FOREIGN KEY (`fk_preservationtypeid`) REFERENCES `rawpreservationtype` (`rawpreservationtypeid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.occurrenceToWater
CREATE TABLE IF NOT EXISTS `occurrenceToWater` (
  `fk_occurrenceid` int(11) NOT NULL,
  `fk_oceanid` int(11) NOT NULL,
  PRIMARY KEY (`fk_occurrenceid`,`fk_oceanid`),
  KEY `fk_occurrenceToWater_2_idx` (`fk_oceanid`),
  CONSTRAINT `fk_occurrenceToWater_1` FOREIGN KEY (`fk_occurrenceid`) REFERENCES `rawoccurrence` (`occurrenceid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_occurrenceToWater_2` FOREIGN KEY (`fk_oceanid`) REFERENCES `oceanOrContinent` (`oceanOrContinentid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.permit
CREATE TABLE IF NOT EXISTS `permit` (
  `permitid` int(11) NOT NULL AUTO_INCREMENT,
  `permittype` varchar(255) DEFAULT NULL,
  `permitstatus` varchar(255) DEFAULT NULL,
  `permitURI` varchar(255) DEFAULT NULL,
  `permittext` varchar(255) DEFAULT NULL,
  `fk_tripleidstoreid` int(11) DEFAULT NULL,
  PRIMARY KEY (`permitid`),
  KEY `fk_permit_1_idx` (`fk_tripleidstoreid`),
  CONSTRAINT `fk_permit_1` FOREIGN KEY (`fk_tripleidstoreid`) REFERENCES `tripleidstore` (`tripleidstoreid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.preparation
CREATE TABLE IF NOT EXISTS `preparation` (
  `preparationid` int(11) NOT NULL AUTO_INCREMENT,
  `preparationdate` varchar(45) DEFAULT NULL,
  `preparationType` varchar(255) DEFAULT NULL,
  `preparationMaterials` varchar(455) DEFAULT NULL,
  `preparationStaff` varchar(255) DEFAULT NULL,
  `fk_tripleidstoreid` int(11) DEFAULT NULL,
  PRIMARY KEY (`preparationid`),
  KEY `fk_prepa_1_idx` (`fk_tripleidstoreid`),
  CONSTRAINT `fk_prepa_1` FOREIGN KEY (`fk_tripleidstoreid`) REFERENCES `tripleidstore` (`tripleidstoreid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.provider
CREATE TABLE IF NOT EXISTS `provider` (
  `providerid` int(11) NOT NULL AUTO_INCREMENT,
  `providername` varchar(255) DEFAULT NULL,
  `providerurl` varchar(255) DEFAULT NULL,
  `provideraddress` varchar(255) DEFAULT NULL,
  `providercountry` varchar(255) DEFAULT NULL,
  `created_when` datetime DEFAULT NULL,
  PRIMARY KEY (`providerid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.rawcoordinates
CREATE TABLE IF NOT EXISTS `rawcoordinates` (
  `rawcoordinatesid` int(11) NOT NULL AUTO_INCREMENT,
  `longitude` varchar(45) DEFAULT NULL,
  `latitude` varchar(45) DEFAULT NULL,
  `coordinatesMethod` varchar(255) DEFAULT NULL,
  `coordinateErrorDistanceInMeters` varchar(45) DEFAULT NULL,
  `fk_occurrenceid` int(11) DEFAULT NULL,
  `spatialDatum` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`rawcoordinatesid`),
  KEY `fk_coordinates_1` (`fk_occurrenceid`),
  CONSTRAINT `fk_coordinates_1` FOREIGN KEY (`fk_occurrenceid`) REFERENCES `rawoccurrence` (`occurrenceid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.rawhigher
CREATE TABLE IF NOT EXISTS `rawhigher` (
  `highertaxaid` int(11) NOT NULL AUTO_INCREMENT,
  `higherrank` varchar(45) NOT NULL DEFAULT '0',
  `highertaxon` varchar(255) NOT NULL DEFAULT '0',
  PRIMARY KEY (`higherrank`,`highertaxon`),
  UNIQUE KEY `highertaxaid_UNIQUE` (`highertaxaid`),
  KEY `rank` (`higherrank`),
  KEY `taxon` (`highertaxon`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.rawidentification
CREATE TABLE IF NOT EXISTS `rawidentification` (
  `rawidentificationid` int(11) NOT NULL AUTO_INCREMENT,
  `fk_occurrenceid` int(11) DEFAULT NULL,
  `created_when` datetime DEFAULT NULL,
  `fullScientificName` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `preferred` tinyint(1) DEFAULT NULL,
  `isoBegin` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `isoEnd` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `dateText` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `genusOrMonomial` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `specificepithet` varchar(155) COLLATE utf8_bin DEFAULT NULL,
  `infraspecificepithet` varchar(155) COLLATE utf8_bin DEFAULT NULL,
  `infragenericepithet` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `mineral` int(11) DEFAULT '0',
  PRIMARY KEY (`rawidentificationid`),
  KEY `occurrenceid_index` (`fk_occurrenceid`),
  CONSTRAINT `fk_rawidentification_1` FOREIGN KEY (`fk_occurrenceid`) REFERENCES `rawoccurrence` (`occurrenceid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.rawoccurrence
CREATE TABLE IF NOT EXISTS `rawoccurrence` (
  `occurrenceid` int(11) NOT NULL AUTO_INCREMENT,
  `created_when` datetime DEFAULT NULL,
  `gatheringdate` varchar(45) DEFAULT NULL,
  `gatheringdatebegin` varchar(45) DEFAULT NULL,
  `gatheringdateend` varchar(45) DEFAULT NULL,
  `country` varchar(255) DEFAULT NULL,
  `isocountrycode` varchar(40) DEFAULT NULL,
  `locality` text,
  `biotope` text,
  `fk_recordbasisid` int(11) DEFAULT NULL,
  `fk_kindofunitid` int(11) DEFAULT NULL,
  `collectornumber` varchar(255) DEFAULT NULL,
  `fieldnumber` varchar(255) DEFAULT NULL,
  `accessionnumber` varchar(255) DEFAULT NULL,
  `fk_datasourceid` int(11) DEFAULT NULL,
  `minDepth` varchar(45) DEFAULT NULL,
  `maxDepth` varchar(45) DEFAULT NULL,
  `minAltitude` varchar(45) DEFAULT NULL,
  `maxAltitude` varchar(45) DEFAULT NULL,
  `fk_sha1sumid` int(11) DEFAULT NULL,
  `fk_tripleidstoreid` int(11) DEFAULT NULL,
  `gatheringareas` text,
  `establishmentMeans` varchar(255) DEFAULT NULL,
  `recordURI` varchar(455) DEFAULT NULL,
  `water` varchar(255) DEFAULT NULL,
  `ipen` varchar(255) DEFAULT NULL,
  `hardiness` char(1) DEFAULT NULL,
  `acquiredFrom` VARCHAR(255) DEFAULT NULL,
  `accessionStatus` CHAR(1) DEFAULT NULL,
  `donorCategory` CHAR(1) DEFAULT NULL,
  PRIMARY KEY (`occurrenceid`),
  KEY `fk_occurrences_4` (`fk_datasourceid`),
  KEY `fk_occurrence_2` (`fk_recordbasisid`),
  KEY `fk_occurrence_3` (`fk_sha1sumid`),
  KEY `fk_occurrence_4` (`fk_kindofunitid`),
  KEY `tripleid_index` (`fk_tripleidstoreid`),
  CONSTRAINT `fk_rawoccurrence_1` FOREIGN KEY (`fk_kindofunitid`) REFERENCES `unitkind` (`unitkindid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_rawoccurrence_2` FOREIGN KEY (`fk_datasourceid`) REFERENCES `bio_datasource` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_rawoccurrence_3` FOREIGN KEY (`fk_recordbasisid`) REFERENCES `recordbasis` (`recordbasisid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_rawoccurrence_4` FOREIGN KEY (`fk_tripleidstoreid`) REFERENCES `tripleidstore` (`tripleidstoreid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `fk_rawoccurrence_5` FOREIGN KEY (`fk_sha1sumid`) REFERENCES `sha1responses` (`shaid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.rawpreservationtype
CREATE TABLE IF NOT EXISTS `rawpreservationtype` (
  `rawpreservationtypeid` int(11) NOT NULL AUTO_INCREMENT,
  `preservationtype` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`rawpreservationtypeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.rawstratigraphy
CREATE TABLE IF NOT EXISTS `rawstratigraphy` (
  `rawstratigraphyid` int(11) NOT NULL AUTO_INCREMENT,
  `domain` varchar(255) DEFAULT NULL,
  `term` varchar(255) DEFAULT NULL,
  `sourcename` varchar(255) DEFAULT NULL,
  `sourcenameversion` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `stratigraphytext` varchar(255) DEFAULT NULL,
  `stratigraphytype` varchar(45) DEFAULT NULL,
  `fk_occurrenceid` int(11) DEFAULT NULL,
  PRIMARY KEY (`rawstratigraphyid`),
  KEY `fk_rawstratigraphy_1_idx` (`fk_occurrenceid`),
  CONSTRAINT `fk_rawstratigraphy_1` FOREIGN KEY (`fk_occurrenceid`) REFERENCES `rawoccurrence` (`occurrenceid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.recordbasis
CREATE TABLE IF NOT EXISTS `recordbasis` (
  `recordbasisid` int(11) NOT NULL AUTO_INCREMENT,
  `recordbasis` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`recordbasisid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Funktion wfo.REGEXP_EXTRACT
DELIMITER //
CREATE DEFINER=`root`@`localhost` FUNCTION `REGEXP_EXTRACT`(string TEXT, exp TEXT, now TEXT) RETURNS text CHARSET utf8
    DETERMINISTIC
BEGIN
  DECLARE s INT DEFAULT 1;
  DECLARE e INT;
  DECLARE adjustStart TINYINT DEFAULT 1;
  DECLARE adjustEnd TINYINT DEFAULT 1;

  
  

  IF LEFT(exp, 1) = '^' THEN 
    SET adjustStart = 0;
  ELSE
    SET exp = CONCAT('^', exp);
  END IF;

  IF RIGHT(exp, 1) = '$' THEN
    SET adjustEnd = 0;
  ELSE
    SET exp = CONCAT(exp, '$');
  END IF;

  
  
  WHILE (s <= LENGTH(string)) DO
    SET e = LENGTH(string);
    WHILE (e >= 1) DO
      IF SUBSTRING(string, s, e) REGEXP exp THEN
			IF SUBSTRING(string, s, e) > 1000 AND SUBSTRING(string, s, e) < now THEN
				RETURN SUBSTRING(string, s, e);
		  END IF;
      END IF;
      IF adjustEnd THEN
        SET e = e - 1;
      ELSE
        SET e = s - 1; 
      END IF;
    END WHILE;
    IF adjustStart THEN
      SET s = s + 1;
    ELSE
      SET s = LENGTH(string) + 1; 
    END IF;
  END WHILE;

  RETURN NULL;

END//
DELIMITER ;

-- Exportiere Struktur von Tabelle wfo.sequenceaccession
CREATE TABLE IF NOT EXISTS `sequenceaccession` (
  `sequenceaccessionid` int(11) NOT NULL AUTO_INCREMENT,
  `created_when` datetime DEFAULT NULL,
  `fk_tripleidstoreid` int(11) DEFAULT NULL,
  `geneticlocus` varchar(45) DEFAULT NULL,
  `accessionUrl` varchar(255) DEFAULT NULL,
  `accessionIdentifier` varchar(45) DEFAULT NULL,
  `boldnumber` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`sequenceaccessionid`),
  KEY `fk_genbank_1` (`fk_tripleidstoreid`),
  CONSTRAINT `fk_sequenceaccession_1` FOREIGN KEY (`fk_tripleidstoreid`) REFERENCES `tripleidstore` (`tripleidstoreid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.sha1responses
CREATE TABLE IF NOT EXISTS `sha1responses` (
  `shaid` int(11) NOT NULL AUTO_INCREMENT,
  `filename` varchar(155) CHARACTER SET latin1 DEFAULT NULL,
  `dataset` varchar(5000) CHARACTER SET latin1 DEFAULT NULL,
  `provider` text CHARACTER SET latin1,
  `sha1sum` varchar(255) CHARACTER SET latin1 DEFAULT NULL,
  `created` datetime DEFAULT NULL,
  `modified` datetime DEFAULT NULL,
  `isUpToDate` tinyint(4) DEFAULT NULL,
  `harvested` tinyint(4) DEFAULT NULL,
  `elementtype` varchar(45) DEFAULT NULL,
  `datasetdir` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`shaid`),
  KEY `shaindex` (`filename`),
  KEY `shaidx` (`sha1sum`),
  KEY `elttypeIdx` (`elementtype`),
  KEY `datasetIdx` (`dataset`(767))
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.taxonAtomised
CREATE TABLE IF NOT EXISTS `taxonAtomised` (
  `originalname` varchar(255) COLLATE utf8_bin NOT NULL,
  `canonical` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `author` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `year` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `bracketAuthorship` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `bracketYear` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `genus` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `subgenus` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `species` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `subspecies` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `rank` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `status` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `problem` int(11) DEFAULT '0',
  `canonicalNameComplete` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `debugged` int(11) DEFAULT '0',
  `identificationQualifier` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `nameAddendum` varchar(45) COLLATE utf8_bin DEFAULT NULL,
  `qualifierPosition` int(11) DEFAULT NULL,
  `cultivarOrBreed` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `fullscientificnamegenerated` varchar(455) COLLATE utf8_bin DEFAULT NULL,
  `reg` varchar(455) COLLATE utf8_bin DEFAULT NULL,
  `canonicalNameChecklist` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`originalname`),
  KEY `fullscientificnamegenerated` (`fullscientificnamegenerated`(255))
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.treeoflife
CREATE TABLE IF NOT EXISTS `treeoflife` (
  `fullscientificname` varchar(255) CHARACTER SET utf8 DEFAULT NULL,
  `canonicalNameChecklist` varchar(255) COLLATE utf8_bin DEFAULT NULL,
  `occurrenceid` int(11) NOT NULL,
  `kingdom` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `phylum` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `ordo` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `classis` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `family` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `genus` varchar(45) CHARACTER SET utf8 DEFAULT NULL,
  `institution` varchar(512) CHARACTER SET utf8 NOT NULL DEFAULT '',
  KEY `tof1` (`fullscientificname`),
  KEY `fk_treeoflife_1_idx` (`occurrenceid`),
  KEY `tof2` (`kingdom`),
  KEY `tof3` (`phylum`),
  KEY `tof4` (`ordo`),
  KEY `tof5` (`classis`),
  KEY `tof6` (`family`),
  KEY `tof7` (`genus`),
  KEY `tof8` (`institution`(255)),
  CONSTRAINT `fk_treeoflife_1` FOREIGN KEY (`occurrenceid`) REFERENCES `occurrence` (`occurrenceid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.tripleidstore
CREATE TABLE IF NOT EXISTS `tripleidstore` (
  `tripleidstoreid` int(11) NOT NULL AUTO_INCREMENT,
  guid varchar(255) DEFAULT NULL,
  `collectioncode` varchar(255) DEFAULT NULL,
  `institutioncode` varchar(255) DEFAULT NULL,
  `unitid` varchar(255) DEFAULT NULL,
  `accesspoint` varchar(255) DEFAULT NULL,
  `digirresourcename` varchar(255) DEFAULT NULL,
  `digirsourceurl` varchar(255) DEFAULT NULL,
  `unitidtext` varchar(255) DEFAULT NULL,
  `unitidnumeric` varchar(255) DEFAULT NULL,
  `unitidnumber` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`tripleidstoreid`),
  KEY `index4` (`unitid`),
  KEY idxTripleidstoreGUID(guid),
  KEY idxAccesspoint(accesspoint)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.typedesignation
CREATE TABLE IF NOT EXISTS `typedesignation` (
  `typedesignationid` int(11) NOT NULL AUTO_INCREMENT,
  `typestatus` varchar(255) DEFAULT NULL,
  `ts_fullscientificname` varchar(255) DEFAULT NULL,
  `citation` text,
  `fk_occurrenceid` int(11) DEFAULT NULL,
  `verificationNotes` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`typedesignationid`),
  KEY `fk_typedesignation_1` (`fk_occurrenceid`),
  CONSTRAINT `fk_typedesignation_1` FOREIGN KEY (`fk_occurrenceid`) REFERENCES `rawoccurrence` (`occurrenceid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.unitaccessionnumber
CREATE TABLE IF NOT EXISTS `unitaccessionnumber` (
  `accessionnumberid` int(11) NOT NULL AUTO_INCREMENT,
  `accessionnumber` varchar(65) DEFAULT NULL,
  `fk_occurrenceid` int(11) DEFAULT NULL,
  PRIMARY KEY (`accessionnumberid`),
  KEY `fk_accessionnumber_1_idx` (`fk_occurrenceid`),
  CONSTRAINT `fk_accessionnumber_1` FOREIGN KEY (`fk_occurrenceid`) REFERENCES `rawoccurrence` (`occurrenceid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.unitkind
CREATE TABLE IF NOT EXISTS `unitkind` (
  `unitkindid` int(11) NOT NULL AUTO_INCREMENT,
  `kindofunit` varchar(155) DEFAULT NULL,
  `kindofunit_Clean` varchar(155) DEFAULT NULL,
  PRIMARY KEY (`unitkindid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.user
CREATE TABLE IF NOT EXISTS `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) COLLATE utf8_bin NOT NULL,
  `password_hash` text COLLATE utf8_bin NOT NULL,
  `password_reset_token` text COLLATE utf8_bin,
  `email` varchar(50) COLLATE utf8_bin NOT NULL,
  `auth_key` text COLLATE utf8_bin NOT NULL,
  `status` int(11) NOT NULL DEFAULT '0',
  `created_at` date NOT NULL,
  `updated_at` date NULL DEFAULT NULL,
  `password` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `form_of_address` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `title` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `name` varchar(50) COLLATE utf8_bin NOT NULL,
  `first_name` varchar(50) COLLATE utf8_bin NOT NULL,
  `institution` varchar(100) COLLATE utf8_bin NOT NULL,
  `division` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `street_pobox` varchar(50) COLLATE utf8_bin NOT NULL,
  `postal_code` varchar(50) COLLATE utf8_bin NOT NULL,
  `location_city` varchar(50) COLLATE utf8_bin NOT NULL,
  `state_province` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `country` varchar(50) COLLATE utf8_bin NOT NULL,
  `delivery_institution` varchar(100) COLLATE utf8_bin DEFAULT NULL,
  `delivery_division` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `delivery_street` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `delivery_postal_code` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `delivery_location` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `delivery_state` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `delivery_country` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `cites_code` varchar(50) COLLATE utf8_bin DEFAULT NULL,
  `activation_key` text COLLATE utf8_bin NOT NULL,
  `comment` text COLLATE utf8_bin,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.user_searches
CREATE TABLE IF NOT EXISTS `user_searches` (
  `search_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `search_url` text COLLATE utf8_bin,
  `nb_records_0` int(11) DEFAULT NULL COMMENT 'Number of record of first search',
  `subscription` tinyint(1) NOT NULL,
  PRIMARY KEY (`search_id`),
  KEY `FK_user_searches_user` (`user_id`),
  CONSTRAINT `FK_user_searches_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.user_settings
CREATE TABLE IF NOT EXISTS `user_settings` (
  `user_id` int(11) NOT NULL,
  `default_hits_per_page` int(11) NOT NULL,
  `default_sort` varchar(50) COLLATE utf8_bin NOT NULL,
  `order` varchar(50) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`user_id`),
  CONSTRAINT `FK__user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.user_shopping_cart
CREATE TABLE IF NOT EXISTS `user_shopping_cart` (
  `item_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `unit_id` varchar(50) COLLATE utf8_bin NOT NULL,
  `order_date` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `ordered` tinyint(4) NOT NULL DEFAULT '0',
  `collection_code` varchar(455) COLLATE utf8_bin DEFAULT NULL,
  `institution_code` varchar(455) COLLATE utf8_bin DEFAULT NULL,
  PRIMARY KEY (`item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo.user_shopping_statistics
CREATE TABLE IF NOT EXISTS `user_shopping_statistics` (
  `stats_id` int(11) NOT NULL AUTO_INCREMENT,
  `order_date` date NOT NULL,
  `institution` int(11) DEFAULT NULL,
  `samples_dna` int(11) DEFAULT '0',
  `samples_tissue` int(11) DEFAULT '0',
  `samples_request_dna` int(11) DEFAULT '0',
  PRIMARY KEY (`stats_id`),
  KEY `fk_user_shopping_statistics_1_idx` (`institution`),
  CONSTRAINT `fk_user_shopping_statistics_1` FOREIGN KEY (`institution`) REFERENCES `parentInstitution` (`parentInstitutionID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- Daten Export vom Benutzer nicht ausgewählt
-- Exportiere Struktur von Tabelle wfo._taxon_tree
CREATE TABLE IF NOT EXISTS `_taxon_tree` (
  `taxon_id` int(10) NOT NULL,
  `name` varchar(255) NOT NULL,
  `rank` varchar(25) NOT NULL,
  `parent_id` int(10) NOT NULL,
  `lsid` varchar(255) NOT NULL,
  `number_of_children` int(10) NOT NULL,
  `number_of_children_extant` int(10) NOT NULL,
  `total_species_estimation` int(10) NOT NULL,
  `total_species` int(10) NOT NULL,
  `total_species_extant` int(10) NOT NULL,
  `estimate_source` text NOT NULL,
  `has_preholocene` smallint(1) NOT NULL DEFAULT '0',
  `has_modern` smallint(1) NOT NULL DEFAULT '1',
  `is_extinct` smallint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`taxon_id`),
  KEY `parent_id` (`parent_id`,`is_extinct`),
  KEY `name` (`name`(76)),
  KEY `rank` (`rank`(12)),
  KEY `pid` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Daten Export vom Benutzer nicht ausgewählt
