# Database: morphbank
#-----------------------------------------------------------
# server version 4.1.1a-alpha-max-degug

DROP TABLE species;
DROP TABLE classification;
DROP TABLE specimen;
DROP TABLE image;
DROP TABLE viewtable;
DROP TABLE imageannotation;
DROP TABLE phylogeneticcharacter;
DROP TABLE phylogeneticcharacterset;
DROP TABLE phylogeneticcharacterstatetable;
DROP TABLE publicationtable;
DROP TABLE usertable; 
DROP TABLE grouptable;

#
# Table structure for table 'species'
#
CREATE TABLE species(
 SpeciesID int(8) NOT NULL auto_increment,
 GenusID int(32) NOT NULL,
 FamilyName varchar(128) NOT NULL,
 GenusName varchar(128) NOT NULL,
 Variety varchar(128), 
 SpeciesEpithet varchar(128),
 SpeciesNameAuthors varchar (128),
 SpeciesDescribedYear char(4),
 DateIdentified date DEFAULT '0000-00-00', 
 IdentifiedBy varchar(128) NOT NULL,
 PRIMARY KEY (SpeciesID));

# Table Structure for table 'classification'

 CREATE TABLE classification(
  GenusID     int(32)   NOT NULL auto_increment,
  GenusName   varchar(128) NOT NULL,
  FamilyName  varchar(128) NOT NULL,
  OrderName   varchar(128) NOT NULL,
  ClassName   varchar(128),
  PhylumName  varchar(128) NOT NULL,
  KingdomName varchar(128) NOT NULL,
  PRIMARY KEY (GenusID));
#
# Table structure for table 'specimen'
#
CREATE TABLE specimen(
 MorphBankSpecimenID int(32) NOT NULL,
 SpeciesID int(8) NOT NULL,
 CatalogNumber int (32) NOT NULL AUTO_INCREMENT,
 DateLastModified date NOT NULL default '0000-00-00',
 InstitutionCode varchar(128),
 CollectionCode varchar(128),
 ScientificName varchar(128),
 BasisOfRecord char(1),
 SubSpecies varchar (128),
 TypeStatus varchar (255),
 TypeName varchar (128),
 CollectionNumber varchar (128),
 FieldNumber varchar (128),
 CollectorName varchar (128),
 DateCollected date NOT NULL DEFAULT '0000-00-00',
 TimeofDate time,
 ContinentOcean varchar(128),
 Country varchar(56),
 StateProvince varchar(56),
 County varchar(56),
 Locality varchar(56),
 Latitude double,
 Longitude double,
 CoordinatePrecision int(8),
 MinimumElevation int(32),
 MaximumElevation int(32),
 MinimumDepth int(32),
 MaximumDepth int(32),
 Sex varchar(8),
 PreparationType varchar(255),
 IndividualCount int(32),
 PreviousCatalogNumber varchar(128),
 RelationshipType varchar(128),
 RelatedCatalogItem varchar (128),
 DevelopmentalStage varchar (128),
 Notes varchar(255),
  PRIMARY KEY(CatalogNumber));

#
#Table Structure for Table 'image'
#
CREATE TABLE image (
 AccessionNumber int(32) NOT NULL AUTO_INCREMENT,
 MorphBankSpecimenID int(32),
 ViewNumber int(8),
 ImageScale varchar(64),
 XDimensionPixels int(32) NOT NULL,
 YDimensionPixels int(32) NOT NULL,
 ResolutionInPixelsPerInch int(32) NOT NULL,
 OriginalFileName varchar (255) NOT NULL,
 Magnification varchar(128),
 ImageFileType varchar(128),
 PRIMARY KEY (AccessionNumber));


#
#Table Structure for Table 'viewtable'
#
CREATE TABLE viewtable (
 ViewNumber int(32) NOT NULL,
 ImagingTechnique varchar (128),
 ImagingPreparationTechnique varchar (128),
 SpecimenPart varchar (128),
 ViewAngle varchar (128),
 Sex varchar(8),
 DevelopmentalStage varchar (128),
 PRIMARY KEY (ViewNumber));


#
#Table Structure for Table 'imageannotation'
#
CREATE TABLE imageannotation (
 AcessionNumber int(32) ,
 ImageAnnotationSeqNo int(32) NOT NULL AUTO_INCREMENT,
 CatalogNumber varchar(128) NOT NULL,
 AnnotationLocX int(32),
 AnnotationLocy int(32),
 AnnotationRadius int(23),
 AnnotationType char(1),
 PhylogeneticCharacterID int(32),
 PhylogeneticCharacterStateID int(32),
 AnnotationAuthor varchar(128),
 AnnotationDate date DEFAULT '0000-00-00',
 PRIMARY KEY (ImageAnnotationSeqNo));

#
#Table Structure for Table 'phylogeneticcharacter'
#
CREATE TABLE phylogeneticcharacter (
 CharacterID int(32) NOT NULL AUTO_INCREMENT,
 CharacterDescription varchar (128), 
 ViewID int(32),
 Sex varchar(8),
 Stage varchar (128),
 SimilarEntries varchar (128),
 RelatedCharacterID int (32),
 RelationType varchar (128),
 SuggestedTaxonRange varchar (128),
 PRIMARY KEY (CharacterID));

#
# Table Structure for Table 'phylogeneticcharacterset'
#
CREATE TABLE phylogeneticcharacterset (
 CharacterSetID int(32) NOT NULL,
 CharacterID int(32),
 PublicationID int(32),
 UserID varchar (128),
 PublicationDate date DEFAULT '0000-00-00',
 PRIMARY KEY (CharacterSetID));

#
# Table Structure for Table 'phylogeneticcharacterstatetable'
#

CREATE TABLE phylogeneticcharacterstatetable (
  CharacterID int(32),
  CharacterStateID  int(32) NOT NULL,
  CharacterStateDescription varchar(128),
  AcessionNumber int(32),
  ImageAnnotationSeqNo int(32),
  PRIMARY KEY (CharacterStateID));

#
# Table Structure for Table 'publicationtable'
#

CREATE TABLE publicationtable (
   PublicationID int(32) NOT NULL AUTO_INCREMENT,
   PublicationAuthor varchar (128),
   PublicationYear char(4),
   PublicationJournal varchar (128),
   PublicationTitle varchar (128),
   PublicationPagesFrom int(32),
   PublicationPagesto int(32),
   PRIMARY KEY (PublicationID)); 

#
# Table Structure for Table 'usertable'
#

CREATE TABLE usertable (
   UserID int (32) NOT NULL,
   Level int (8),
   UIN int (8),
   PIN int (16),
   Name varchar (128),
   Email varchar (128),
   Affiliation varchar (128),
   Address varchar (255),
   Country varchar (128),
   GroupID int(32),
   PRIMARY KEY (UserID));

#
# Table Structure for Table 'grouptable'
#

CREATE TABLE grouptable (
  GroupID int (32) NOT NULL,
  GroupName varchar (128) NOT NULL,
  PRIMARY KEY (GroupID)); 
