# Moorea Biocode Project # Feb 2006 - 2009 # Specimen table # DROP table biocode; CREATE TABLE biocode ( seq_num varchar(40), ## changed from INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY on 8/12/2008 to accommodate guid ## GENERATED FIELD -- not editable by user DateFirstEntered date, ## GENERATED FIELD -- not editable by user EnteredBy varchar(128), DateLastModified date, ## GENERATED FIELD -- not editable by user ModifiedBy varchar(128), ModifyReason varchar(255), ProjectCode char(4), ## MBIO # changed from InstitutionCode on Dec 2, 2009 OrigInstitution varchar(255), ## for info about where the specimen came from/who collected it originally 4/30/2008 Specimen_Num_Collector varchar(128), ## modified 5/5/2008 (formerly "CollectionCode") CatalogNumberNumeric int unsigned, ## Darwin Core= "CatalogNumber" (not unique) ## GENERATED FIELD -- not editable by user AccessionNumber varchar(100), ## type 10/8/2004 GO "2004.510" (biocode_accessions) VerbatimCollectingLabel varchar(255), VerbatimIDLabel varchar(255), CollectingLabelNotes varchar(255), specimen_ElevationMeters decimal (10,4), ## added April 2009 when coll event fields mostly moved to coll event table specimen_MinDepthMeters decimal (10,4), ## added as specimen_DepthMeters in Apr 2009, then changed to specimen_MinDepthMeters in May 2009 per GP specimen_MaxDepthMeters decimal (10,4), ## added May 2009 per GP ScientificName varchar(255), ## Darwin Core="TaxonomicName" # GENERATED FIELD -- not editable by user ColloquialName varchar(255), Kingdom varchar(50), Phylum varchar(50), Subphylum varchar(50), ## added 6/25/2007 Superclass varchar(50), ## added 6/25/2007 Class varchar(50), ## Darwin Advised="Class", modified Subclass varchar(50), Infraclass varchar(50), ## added 6/26/2007 Superorder varchar(50), ## added 6/25/2007 Ordr varchar(50), ## Darwin Advised="Order", modified, SQL conflict Suborder varchar(50), Infraorder varchar(50), ## added 6/25/2007 Superfamily varchar(50), ## added 8/31/05 Family varchar(50), Subfamily varchar(50), Tribe varchar(50), ## added 8/22/04 Subtribe varchar(50), ## added 8/31/05 Genus varchar(50), Subgenus varchar(50), SpecificEpithet varchar(50), SubspecificEpithet varchar(128), ScientificNameAuthor varchar(255), ## this includes the year also, apparently... MorphoSpecies_Match varchar(255), ## BIOCODE -- new 3/5/06 # name changed from MorphoSpecies to MorphoSpecies_Match 3/30/2009 MorphoSpecies_Description varchar(255), ## BIOCODE -- new 2/25/09 LowestTaxon varchar(255), ## new 5/13/2008 LowestTaxonLevel varchar(128), ## new 5/13/2008 IdentifiedBy varchar(255), IdentifiedInstitution varchar(255), ## associated with IdentifiedBy 4/30/08 BasisOfID varchar(255), ## BIOCODE -- new 3/5/06 YearIdentified mediumint unsigned, MonthIdentified tinyint unsigned, DayIdentified tinyint unsigned, PreviousID varchar(255), ## renamed 8/27/04 (was IdentificationText) TypeStatus varchar(255), SexCaste varchar(50), ## split out Sex_Stage 8/22/04, renamed LifeStage varchar(50), ## split out Sex_Stage 8/22/04, renamed Parts varchar(255), Weight decimal (10,4), WeightUnits varchar(10), Length decimal (10,4), LengthUnits varchar(10), PreparationType varchar(255), preservative varchar(128), ## BIOCODE -- new 3/5/06 fixative varchar(128), ## added 9/27/2006 JG relaxant varchar(128), ## BIOCODE -- new 3/5/06 IndividualCount varchar(50), ## changed from mediumint unsigned 8/22/04 specimen_Habitat varchar(255), ## changed from Habitat in April 2009 specimen_MicroHabitat varchar(255), ## BIOCODE -- new 3/5/06 # changed from MicroHabitat May 28 2009 Associated_Taxon varchar(255), ## BIOCODE -- new 3/5/06 # May 28 2009: renamed; was Host field Cultivated varchar(3), ## yes/no/null (for UCJeps) 4/30/2008 Association_Type varchar(255), ## added 5/4/2009 - using the pollinator terminology, etc. - this is being developed - by Discover Life group and can be adopted for other ecologic - associations - will be controlled pick list (CMeyer will send list) Color varchar(128), ## added 5/4/2009 - for plant flowers VoucherCatalogNumber varchar(128), ## 4/15/2009 discussion with CMeyer: changed from OtherCatalogNumbers ## This is the voucher number of the specimen in the institution where it physically resides. Voucher_URI varchar(255), ## 6/12/2009 for linking to specimens at the Holding Institution. Should be a URL/URI. RelatedCatalogItem varchar(128), ## 4/20/2009 discussion with CMeyer: an existing Specimen_Num_Collector for ## associated specimen should go in this field. ## Other info can go in notes field or associated_taxon field (or other appropriate field). ## Any Specimen_Num_Collector entered into this field has to be in database, otherwise won't add. PublicAccess varchar(50), ## "no" or NULL notes text, ## changed to text Oct 2008 pic varchar(255), ## 1 = photo in CalPhotos bnhm_id char(24) unique, ## MBIO . CatalogNumberNumeric (unique) # GENERATED FIELD -- not editable by usr record_source varchar(255), ## i.e., CalMoth Database, EME dl_notes varchar(255), ## misc. processing notes # only editable by database staff DNASequenceNo varchar(255), ## added 10/8/2004 GO RecheckFlag tinyint unsigned, ## 1 or NULL added 10/8/2004 GO HoldingInstitution varchar(255), ## added 6/12/05 coll_eventid int(11) NOT NULL, ## added 10/8/05 key for biocode_coll_events Collection_Method varchar(64), ## added 10/18/05 Malaise Trap Taxon_Certainty varchar(48), ## added 6/21/06 "cf." or "aff." Tissue tinyint unsigned, ## max tissue_num (NOT # tissues!) namesoup varchar(255), ## added 3/28/07 concat all taxonomy (see below) LowestTaxon_Generated varchar(255), ## new 1/4/2011 -- generated from cron only parent_record char(24), ## added 4/19/07 GO child_exists tinyint unsigned, ## 1 or NULL added 11/13/07 GO batch_id char(24) ## for batch upload 2008-05-16_12:22:28 GO # GENERATED FIELD -- not editable by user ); create index biocode_bnhm_id on biocode (bnhm_id); create index biocode_CatalogNumberNumeric on biocode (CatalogNumberNumeric); create index biocode_Class on biocode (Class); create index biocode_Specimen_Num_Collector on biocode (Specimen_Num_Collector); create index biocode_ColloquialName on biocode (ColloquialName); create index biocode_EnteredBy on biocode (EnteredBy); create index biocode_Family on biocode (Family); create index biocode_Genus on biocode (Genus); create index biocode_Habitat on biocode (specimen_Habitat); create index biocode_MicroHabitat on biocode (specimen_MicroHabitat); create index biocode_Associated_Taxon on biocode (Associated_Taxon); create index biocode_IdentifiedBy on biocode (IdentifiedBy); create index biocode_IndividualCount on biocode (IndividualCount); create index biocode_InstitutionCode on biocode (ProjectCode); create index biocode_IslandGroup on biocode (IslandGroup); create index biocode_namesoup on biocode (namesoup); create index biocode_Notes on biocode (notes); create index biocode_Ordr on biocode (Ordr); create index biocode_Parts on biocode (Parts); create index biocode_PreparationType on biocode (PreparationType); create index biocode_PreviousID on biocode (PreviousID); create index biocode_ScientificName on biocode (ScientificName); create index biocode_SexCaste on biocode (SexCaste); create index biocode_SpecificEpithet on biocode (SpecificEpithet); create index biocode_Subclass on biocode (Subclass); create index biocode_Subfamily on biocode (Subfamily); create index biocode_Suborder on biocode (Suborder); create index biocode_SubspecificEpithet on biocode (SubspecificEpithet); create index biocode_Tribe on biocode (Tribe); create index biocode_TypeStatus on biocode (TypeStatus); create index biocode_YearIdentified on biocode (YearIdentified); create index biocode_HoldingInstitution on biocode (HoldingInstitution); create index biocode_LowestTaxon_Generated on biocode (LowestTaxon_Generated); # set namesoup to a concat of all taxonomy update biocode set namesoup = concat(ifnull(Class,"")," ", ifnull(Subclass,"")," ",ifnull(Ordr,"")," ", ifnull(Suborder,"")," ", ifnull(Family,"")," ",ifnull(Subfamily,"")," ", ifnull(Tribe,"")," ", ifnull(Genus,"")," ", ifnull(Subgenus,"")," ", ifnull(SpecificEpithet,"")," ", ifnull(SubspecificEpithet,"")," ", ifnull(MorphoSpecies,"")," ",ifnull(Subtribe,"")," ",ifnull(Superfamily,"")) where bnhm_id="EMEC41089";