-- eme_Oct2004 -- mySql version EME - Essig Museum of Entomology - Elib database -- 2004-01-06 G.O. 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(4), -- "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), -- renamed 4/26/04 (was LocationAccuracy) MinElevationMeters decimal (10,5), MaxElevationMeters decimal (10,4), ElevationText varchar(255), MinDepthMeters decimal (10,4), MaxDepthMeters decimal (10,4), DepthText varchar(255), 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), 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), ScientificNameAuthor varchar(255), -- this includes the year also, apparently... IdentifiedBy varchar(255), 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), PreparationType varchar(255), IndividualCount varchar(50), -- changed from mediumint unsigned 8/22/04 Habitat_Host varchar(255), OtherCatalogNumbers varchar(128), RelatedCatalogItem varchar(128), PublicAccess varchar(50), ## "no" or NULL Notes varchar(255), pic char(19), -- kwid for photo 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 ); 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_Host on eme (Habitat_Host); 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); -- 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";