-- phpMyAdmin SQL Dump
-- version 2.11.3deb1ubuntu1.3
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Oct 21, 2011 at 04:24 PM
-- Server version: 5.0.51
-- PHP Version: 5.2.4-2ubuntu5.18

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";

--
-- Database: `bien_web`
--

-- --------------------------------------------------------

--
-- Table structure for table `bien_results`
--

CREATE TABLE IF NOT EXISTS `bien_results` (
  `resultID` int(11) unsigned NOT NULL auto_increment,
  `queryName` varchar(150) NOT NULL COMMENT 'unique descriptive name for query',
  `queryHeader` varchar(25) NOT NULL COMMENT 'Short column header, not unique',
  `queryDescription` varchar(255) default NULL,
  `queryDate` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  `querySql` varchar(500) default NULL,
  `queryResult` varchar(50) NOT NULL,
  PRIMARY KEY  (`resultID`),
  UNIQUE KEY `queryName` (`queryName`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `web_datasource`
--

CREATE TABLE IF NOT EXISTS `web_datasource` (
  `dataSourceID` int(11) unsigned NOT NULL auto_increment,
  `dataSourceName` varchar(100) default NULL,
  `aggregatorOrPrimary` varchar(25) default NULL,
  `sourceType` varchar(25) default NULL,
  `accessLevel` varchar(50) default NULL,
  `authorshipContactEmail` varchar(150) default NULL,
  PRIMARY KEY  (`dataSourceID`),
  UNIQUE KEY `dataSourceName` (`dataSourceName`),
  KEY `aggregatorOrPrimary` (`aggregatorOrPrimary`),
  KEY `sourceType` (`sourceType`),
  KEY `accessLevel` (`accessLevel`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `web_datasource_party`
--

CREATE TABLE IF NOT EXISTS `web_datasource_party` (
  `dataSourceID` int(11) unsigned NOT NULL auto_increment,
  `partyID` int(11) unsigned default NULL,
  `userRole` varchar(50) default NULL,
  `isPI` int(1) default NULL,
  PRIMARY KEY  (`dataSourceID`),
  UNIQUE KEY `user_datasource` (`partyID`,`dataSourceID`),
  KEY `userRole` (`userRole`),
  KEY `isPI` (`isPI`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `web_file`
--

CREATE TABLE IF NOT EXISTS `web_file` (
  `fileID` int(11) unsigned NOT NULL auto_increment,
  `fileName` varchar(255) default NULL,
  `filePath` varchar(255) default NULL,
  `isUrl` int(1) NOT NULL default '0',
  `fileType` varchar(25) NOT NULL,
  `objectType` varchar(25) default NULL,
  PRIMARY KEY  (`fileID`),
  KEY `fileName` (`fileName`),
  KEY `filePath` (`filePath`),
  KEY `fileType` (`fileType`),
  KEY `objectType` (`objectType`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `web_observation`
--

CREATE TABLE IF NOT EXISTS `web_observation` (
  `observationID` int(11) unsigned NOT NULL auto_increment,
  `dataSourceID` int(11) unsigned default NULL,
  `dataSourceName` varchar(100) default NULL,
  `dataSourceNamePrimary` varchar(100) default NULL,
  `plotID` int(11) unsigned default NULL,
  `observationType` varchar(10) default NULL,
  `bien2_taxonomyID` int(11) unsigned default NULL,
  `bien2_DBPlotID` int(11) unsigned default NULL,
  `bien2_ObservationID` int(11) unsigned default NULL,
  `bien2_OccurID` int(11) unsigned default NULL,
  `taxonAuthorityVerbatim` varchar(255) default NULL,
  `family` varchar(100) default NULL,
  `genus` varchar(100) default NULL,
  `species` varchar(150) default NULL,
  `taxon` varchar(200) default NULL,
  `taxonAuthor` varchar(255) default NULL,
  `taxonMorphospecies` varchar(255) default NULL,
  `rank` varchar(50) default NULL,
  `acceptance` varchar(2) default NULL,
  `country` varchar(50) default NULL,
  `stateProvince` varchar(100) default NULL,
  `countyParish` varchar(100) default NULL,
  `countryError` double default NULL,
  `stateProvinceError` double default NULL,
  `localityDescription` varchar(500) default NULL,
  `collector` varchar(150) default NULL,
  `collectionNumber` varchar(50) default NULL,
  `identifiedBy` varchar(150) default NULL,
  `observationDate` date default NULL,
  `plotCode` varchar(100) default NULL,
  `plotAreaHa` double default NULL,
  `plotMinDbh` decimal(5,1) default NULL,
  `plotMethod` varchar(100) default NULL,
  `latitude` decimal(10,5) default NULL,
  `longitude` decimal(10,5) default NULL,
  `isValidLatLong` int(1) default NULL,
  `isGeovalid` int(1) default NULL,
  `isCultivated` int(1) default NULL,
  `isCultivatedReason` varchar(255) default NULL,
  `abund` int(11) default NULL,
  `abund1` int(11) default NULL,
  `abund2.5` int(11) default NULL,
  `abund10` int(11) default NULL,
  `pctCover` decimal(10,5) default NULL,
  `bien2_TraitObservationID` int(11) unsigned default NULL,
  `traitName` varchar(200) default NULL,
  `traitValue` varchar(100) default NULL,
  `traitUnit` varchar(100) default NULL,
  `traitMethod` varchar(255) default NULL,
  `higherPlantGroup` varchar(25) default NULL,
  PRIMARY KEY  (`observationID`),
  KEY `observationID` (`observationID`),
  KEY `dataSourceID` (`dataSourceID`),
  KEY `dataSourceName` (`dataSourceName`),
  KEY `dataSourceNamePrimary` (`dataSourceNamePrimary`),
  KEY `plotID` (`plotID`),
  KEY `observationType` (`observationType`),
  KEY `bien2_taxonomyID` (`bien2_taxonomyID`),
  KEY `bien2_DBPlotID` (`bien2_DBPlotID`),
  KEY `bien2_ObservationID` (`bien2_ObservationID`),
  KEY `bien2_OccurID` (`bien2_OccurID`),
  KEY `taxonAuthorityVerbatim` (`taxonAuthorityVerbatim`),
  KEY `family` (`family`),
  KEY `genus` (`genus`),
  KEY `species` (`species`),
  KEY `taxon` (`taxon`),
  KEY `taxonAuthor` (`taxonAuthor`),
  KEY `taxonMorphospecies` (`taxonMorphospecies`),
  KEY `rank` (`rank`),
  KEY `acceptance` (`acceptance`),
  KEY `country` (`country`),
  KEY `stateProvince` (`stateProvince`),
  KEY `collector` (`collector`),
  KEY `identifiedBy` (`identifiedBy`),
  KEY `plotCode` (`plotCode`),
  KEY `plotAreaHa` (`plotAreaHa`),
  KEY `plotMinDbh` (`plotMinDbh`),
  KEY `plotMethod` (`plotMethod`),
  KEY `isValidLatLong` (`isValidLatLong`),
  KEY `isGeovalid` (`isGeovalid`),
  KEY `isCultivated` (`isCultivated`),
  KEY `isCultivatedReason` (`isCultivatedReason`),
  KEY `bien2_TraitObservationID` (`bien2_TraitObservationID`),
  KEY `traitName` (`traitName`),
  KEY `traitMethod` (`traitMethod`),
  KEY `traitUnit` (`traitUnit`),
  KEY `higherPlantGroup` (`higherPlantGroup`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `web_observation_file`
--

CREATE TABLE IF NOT EXISTS `web_observation_file` (
  `observationID` int(11) unsigned NOT NULL,
  `fileID` int(11) unsigned NOT NULL,
  UNIQUE KEY `observation_file` (`fileID`,`observationID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `web_party`
--

CREATE TABLE IF NOT EXISTS `web_party` (
  `partyID` int(11) unsigned NOT NULL auto_increment,
  `partyType` varchar(20) default 'person',
  `userName` varchar(50) default NULL,
  `lastName` varchar(50) default NULL,
  `firstName` varchar(50) default NULL,
  `fullName` varchar(100) default NULL,
  `email` varchar(150) default NULL,
  `primaryInstitution` varchar(255) default NULL,
  PRIMARY KEY  (`partyID`),
  KEY `lastName` (`lastName`),
  KEY `firstName` (`firstName`),
  KEY `fullName` (`fullName`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `web_taxon`
--

CREATE TABLE IF NOT EXISTS `web_taxon` (
  `taxonID` int(10) unsigned NOT NULL auto_increment,
  `parentTaxonID` int(10) unsigned default NULL,
  `taxon` varchar(255) NOT NULL,
  `rank` varchar(255) default NULL,
  `nameClass` varchar(50) default NULL,
  `taxonAuthor` varchar(255) default NULL,
  `family` varchar(150) default NULL,
  `genus` varchar(100) default NULL,
  `species` varchar(100) default NULL,
  `leftIndex` int(11) default NULL,
  `rightIndex` int(11) default NULL,
  `isAccepted` int(1) default NULL,
  `isHybrid` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`taxonID`),
  UNIQUE KEY `leftIndex` (`leftIndex`),
  UNIQUE KEY `rightIndex` (`rightIndex`),
  KEY `taxon` (`taxon`),
  KEY `taxonAuthor` (`taxonAuthor`),
  KEY `rank` (`rank`),
  KEY `nameClass` (`nameClass`),
  KEY `family` (`family`),
  KEY `genus` (`genus`),
  KEY `species` (`species`),
  KEY `isHybrid` (`isHybrid`),
  KEY `parentTaxonID` (`parentTaxonID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Table structure for table `web_taxon_file`
--

CREATE TABLE IF NOT EXISTS `web_taxon_file` (
  `taxonID` int(11) unsigned NOT NULL,
  `fileID` int(11) unsigned NOT NULL,
  UNIQUE KEY `taxon_file` (`taxonID`,`fileID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;