# BioCode Moorea Project
# JG - Feb 2006 - based on Essig table


DROP TABLE biocode_species;


CREATE TABLE biocode_species (
#  seq_num            int NOT NULL,
  seq_num            INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, # changed 1/08 GO
  kingdom            varchar(40),
  phylum             varchar(40),
  subphylum          varchar(50),   # new 6/25/2007
  superclass         varchar(50),   # new 6/25/2007
  class              varchar(40),
  subclass           varchar(40),
  infraclass         varchar(50),   # new 6/26/2007
  superorder         varchar(50),   # new 6/25/2007
  ordr               varchar(35),   # not null  (enforced in app)
  suborder           varchar(50),
  infraorder         varchar(50),   # new 6/25/2007
  superfamily        varchar(50),
  family             varchar(50),  # not null  (enforced in app)
  subfamily          varchar(50),
  tribe              varchar(50),
  subtribe           varchar(50),
  genus              varchar(50),  # not null  (enforced in app)
  subgenus           varchar(50),
  species            varchar(50),  # not null  (enforced in app)
  subspecies         varchar(50),
  author             varchar(50),
  year               int,          # 1995
  species_notes      text,
  lter_num           int,          # for linking to http://mcr.lternet.edu/education/categories.php?refID=3&sRefId=12
  cabinet            varchar(40),
  drawer             varchar(40),
  quantity           varchar(40),
  pinned             char(3),
  alcohol            char(3),
  slides             char(3),
  papered            char(3),
  disposition_notes  varchar(50),
  type               varchar(128),   # example: 1 holotype, 3 paratype
  photo              bool,
  date_added         datetime,
  edit_name_date     text,           # example: Gordon Nishida (2004-12-15);; Pete Oboyski (2004-12-17) 
  checkflag          int(1),         # if a record needs to be checked by someone
  source             varchar(40),    # "specimen" (entered from specimen upload)
  collection         tinyint         # 1 if species is in local collection; 0 if not

);




CREATE INDEX idz001_aaa_ix on biocode_species ( seq_num );
CREATE INDEX id001_aaa_ix on biocode_species ( ordr );
CREATE INDEX id002_aaa_ix on biocode_species ( suborder );
CREATE INDEX id003_aaa_ix on biocode_species ( superfamily );
CREATE INDEX id004_aaa_ix on biocode_species ( family );
CREATE INDEX id005_aaa_ix on biocode_species ( subfamily );
CREATE INDEX id006_aaa_ix on biocode_species ( tribe );
CREATE INDEX id007_aaa_ix on biocode_species ( genus );
CREATE INDEX id008_aaa_ix on biocode_species ( species );
CREATE INDEX id009_aaa_ix on biocode_species ( subspecies );
CREATE INDEX id010_aaa_ix on biocode_species ( author );
CREATE INDEX id011_aaa_ix on biocode_species ( year );
CREATE INDEX id012_aaa_ix on biocode_species ( collection );