-- eme Oct 2004 -- Essig Museum of Entomology - Elib database CREATE TABLE eme ( seq_num INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, DateFirstEntered date, ## was "entry_date" (mod 10/8/2004 GO) EnteredBy varchar(128), ## was "entry_by" (mod 10/8/2004 GO) DateLastModified date, ModifiedBy varchar(128), ## added 10/8/2004 GO ModifyReason varchar(255), ## added 10/8/2004 GO InstitutionCode char(10), ## "EMEC" CollectionCode char(15), ## (phased out as of 11/16/04) CatalogNumberNumeric int unsigned, ## Darwin Core= "CatalogNumber" (not unique) AccessionNumber varchar(100), ## type 10/8/2004 GO "2004.510" (eme_accessions) Collector varchar(255), Collector2 varchar(128), ## Collectors2-5 added 10/14/04 GO Collector3 varchar(128), Collector4 varchar(128), Collector5 varchar(128), Collector_List varchar(255), ## Col1, Col2, Col3, Col4, Col5 CollectorNumber varchar(35), YearCollected mediumint unsigned, ## Darwin Core= "Year" MonthCollected tinyint unsigned, ## Darwin Core= "Month" DayCollected tinyint unsigned, ## Darwin Core= "Day" VerbatimCollectingLabel varchar(255), VerbatimIDLabel varchar(255), YearCollected2 mediumint unsigned, MonthCollected2 tinyint unsigned, DayCollected2 tinyint unsigned, CollectingLabelNotes varchar(255), TimeofDay varchar(128), ContinentOcean varchar(128), IslandGroup varchar(128), Island varchar(128), Country varchar(128), StateProvince varchar(128), County varchar(128), Locality varchar(255), DecimalLongitude decimal (10,5), DecimalLatitude decimal (10,5), HorizontalDatum varchar(50), OriginalCoordSystem varchar(255), VerbatimLongitude varchar(255), ## switched order w/VLat 8/27/04 VerbatimLatitude varchar(255), MaxErrorInMeters decimal (10,5), MinElevationMeters decimal (10,5), MaxElevationMeters decimal (10,4), ElevationText varchar(255), MinDepthMeters decimal (10,4), MaxDepthMeters decimal (10,4), DepthText varchar(255), GeorefReference text, ## added March 28, 2011 Lat_Long_Determined_By varchar(255), ## added March 28, 2011 Lat_Long_Determined_Date date, ## added March 28, 2011 GeorefRemarks text, ## added March 28, 2011 ScientificName varchar(255), ## Darwin Core="TaxonomicName" ColloquialName varchar(255), Kingdom varchar(50), Phylum varchar(50), Class varchar(50), ## Darwin Advised="Class", modified Subclass varchar(50), Ordr varchar(50), Suborder varchar(50), # Superfamily is lower down... Family varchar(50), Subfamily varchar(50), Tribe varchar(50), ## added 8/22/04 Genus varchar(50), Subgenus varchar(50), SpecificEpithet varchar(50), SubspecificEpithet varchar(128), IdentificationQualifier varchar(48), ## Field renamed from Taxon_Certainty, 202?. Taxon_Certainty originally added in 2010. ScientificNameAuthor varchar(255), ## this includes the year also IdentifiedBy varchar(255), YearIdentified mediumint unsigned, MonthIdentified tinyint unsigned, DayIdentified tinyint unsigned, PreviousID varchar(255), ## renamed 8/27/04 (was IdentificationText) TypeStatus varchar(255), Caste varchar(50), ## split out Sex_Stage 8/22/04, renamed # removed Sex (was SexCaste) Nov 1 2010 LifeStage varchar(50), ## split out Sex_Stage 8/22/04, renamed NumMales tinyint unsigned, ## added Nov 2010 NumFemales tinyint unsigned, ## added Nov 2010 NumOther tinyint unsigned, ## added Nov 2010 Parts varchar(255), PreparationType varchar(255), IndividualCount varchar(50), ## changed from mediumint unsigned 8/22/04 BasisOfRecord varchar(64), ## added Nov 11, 2012. CV: specimen, observation Habitat varchar(255), # was Habitat_Host field; changed Nov 30, 2011 MicroHabitat varchar(255), ## added Nov 30, 2011 Associated_Taxon varchar(255), ## added Nov 30, 2011 Association_Type varchar(255), ## added Nov 30, 2011 VerbatimIdentification varchar(255), ## Added MorphoSpecies Nov 30, 2011 # Nov 2012: rename from MorphoSpecies to IdentificationQualifier ## Then changed from IdentificationQualifier to VerbatimIdentification (by 202?). OtherCatalogNumbers varchar(128), RelatedCatalogItem varchar(128), LoanNumber varchar(128), ## added Nov 30, 2011 PublicAccess varchar(50), ## "no" or NULL Notes varchar(255), pic char(19), ## kwid for photo label_image tinyint unsigned, ## 1 if there is a label image. Script populates this field based on eme_label_images table. Added 2015-08-19. bnhm_id char(24) unique, ## EMEC . CatalogNumberNumeric (unique) record_source varchar(255), ## i.e., CalMoth Database, EME dl_notes varchar(255), ## misc. processing notes DNASequenceNo varchar(255), ## added 10/8/2004 GO RecheckFlag tinyint unsigned, ## 1 or NULL added 10/8/2004 GO Collectors_Loc varchar(50), ## added 12/10/04 aka Field Loc Num Collectors_ID varchar(50), ## added 12/10/04 aka Field ID Num HoldingInstitution varchar(255), ## added 6/12/05 Subtribe varchar(50), ## added 8/31/05 Superfamily varchar(50), ## added 8/31/05 Coll_EventID integer, ## added 10/8/05 key eme_coll_events Collection_Method varchar(64), ## added 10/18/05 Malaise Trap namesoup varchar(255), ## added 3/2007 concat all taxonomy parent_record char(24), ## added 4/07 bnhm_id this was created from child_exists tinyint unsigned, ## 1 or NULL added 11/13/07 GO ProjectName varchar(128), ## added 11/14/2007 GO TranscriptionIDs varchar(255), ## added 10/29/2013 for NfN data - example: 51ddcddbf6ad773d03016c5e/51ef1e3912af0462990092f9 ## this is a transcription record level id TranscriptionUserNames varchar(255), ## added 10/29/2013 for NfN data - example: katherinebradley/kshaffer/ntamblyn/lavernl TranscriptionCodeVersion char(50), ## added 01/16/2014 for NfN data TranscriptionConfidence char(50), ## added 01/16/2014 for NfN data TranscriptionSubjectIDs varchar(255), ## added 01/16/2014 for NfN data TranscriptionCollectors varchar(255), ## added 01/16/2014 for NfN data TranscriptionNotes varchar(255) ## added 01/16/2014 for NfN data ); grant select on eme to public; create index eme_bnhm_id on eme (bnhm_id); create index eme_CatalogNumberNumeric on eme (CatalogNumberNumeric); create index eme_Class on eme (Class); create index eme_Coll_EventID on eme (Coll_EventID); create index eme_CollectionCode on eme (CollectionCode); create index eme_Collector on eme (Collector); create index eme_Collector2 on eme (Collector2); create index eme_Collector3 on eme (Collector3); create index eme_Collector4 on eme (Collector4); create index eme_Collector5 on eme (Collector5); create index eme_Collector_List on eme (Collector_List); create index eme_Collectors_ID on eme (Collectors_ID); create index eme_Collectors_Loc on eme (Collectors_Loc); create index eme_ColloquialName on eme (ColloquialName); create index eme_ContinentOcean on eme (ContinentOcean); create index eme_Country on eme (Country); create index eme_County on eme (County); create index eme_DecimalLatitude on eme (DecimalLatitude); create index eme_DecimalLongitude on eme (DecimalLongitude); create index eme_EnteredBy on eme (EnteredBy); create index eme_Family on eme (Family); create index eme_Genus on eme (Genus); create index eme_Habitat on eme (Habitat); create index eme_HorizontalDatum on eme (HorizontalDatum); create index eme_IdentifiedBy on eme (IdentifiedBy); create index eme_IndividualCount on eme (IndividualCount); create index eme_InstitutionCode on eme (InstitutionCode); create index eme_Island on eme (Island); create index eme_IslandGroup on eme (IslandGroup); create index eme_LifeStage on eme (LifeStage); create index eme_Locality on eme (Locality); create index eme_MaxElevationMeters on eme (MaxElevationMeters); create index eme_MaxErrorInMeters on eme (MaxErrorInMeters); create index eme_MinElevationMeters on eme (MinElevationMeters); create index eme_Notes on eme (Notes); create index eme_Ordr on eme (Ordr); create index eme_Parts on eme (Parts); create index eme_PreparationType on eme (PreparationType); create index eme_PreviousID on eme (PreviousID); create index eme_ScientificName on eme (ScientificName); create index eme_SexCaste on eme (SexCaste); create index eme_SpecificEpithet on eme (SpecificEpithet); create index eme_StateProvince on eme (StateProvince); create index eme_Subclass on eme (Subclass); create index eme_Subfamily on eme (Subfamily); create index eme_Suborder on eme (Suborder); create index eme_SubspecificEpithet on eme (SubspecificEpithet); create index eme_Tribe on eme (Tribe); create index eme_TypeStatus on eme (TypeStatus); create index eme_YearCollected on eme (YearCollected); create index eme_YearIdentified on eme (YearIdentified); create index eme_HoldingInstitution on eme (HoldingInstitution); create fulltext index eme_namesoup on eme (namesoup); create index eme_ProjectName on eme (ProjectName); create index eme_TranscriptionIDs on eme (TranscriptionIDs); create index eme_TranscriptionUserNames on eme (TranscriptionUserNames); -- LOAD DATA INFILE "/data1/mysql_cron_dumps/mysql_backups_current/eme" INTO TABLE eme; # set namesoup to a concat of all taxonomy update eme 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,"")) where bnhm_id="EMEC41089";