# eme_species - Essig Museum of Entomology - Elib database # 2004-09-10 J.G. DROP TABLE eme_species; CREATE TABLE eme_species ( # seq_num int NOT NULL, seq_num INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, # changed 1/08 GO class varchar(40), subclass varchar(40), ordr varchar(35), # not null (enforced in app) suborder varchar(50), 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, # Pete made all values ints. Was varchar(10), # has some values that are not years, mainly "ms name" referred_to varchar(128), # current name -- added Nov 11, 2012 species_notes text, cat_num varchar(40), # catalog number ?? is this for real? not all values are numbers cabinet varchar(40), drawer varchar(40), # quantity varchar(40), # removed 2013-02-05 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, # 1 = yes, there is a photo; 0 = no photo genus_photo bool, # 1 = yes, there is a photo; 0 = no photo family_photo bool, # 1 = yes, there is a photo; 0 = no photo 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 Gordon or someone source varchar(255), # "specimen" (entered from specimen upload) # changed 2013-02-05 to longer field; disconnected from specimen form collection tinyint, # 1 if species is in Essig collection; 0 if not valid tinyint # 0 if valid; 1 if invalid ); # insert into eme_species (class,subclass,ordr,family,genus,species,date_added,edit_name_date) values # ("Malacostraca","Eumalacostraca","Amphipoda","Undetermined","Undetermined","sp.","2008-01-29","GO (2008-01-29)"); LOAD DATA INFILE "/usr/local/mysql/create_table/eme/Data/Joyce/out" INTO TABLE eme_species fields terminated by '|'; CREATE INDEX idz001_aaa_ix on eme_species ( seq_num ); CREATE INDEX id001_aaa_ix on eme_species ( ordr ); CREATE INDEX id002_aaa_ix on eme_species ( suborder ); CREATE INDEX id003_aaa_ix on eme_species ( superfamily ); CREATE INDEX id004_aaa_ix on eme_species ( family ); CREATE INDEX id005_aaa_ix on eme_species ( subfamily ); CREATE INDEX id006_aaa_ix on eme_species ( tribe ); CREATE INDEX id007_aaa_ix on eme_species ( genus ); CREATE INDEX id008_aaa_ix on eme_species ( species ); CREATE INDEX id009_aaa_ix on eme_species ( subspecies ); CREATE INDEX id010_aaa_ix on eme_species ( author ); CREATE INDEX id011_aaa_ix on eme_species ( year ); CREATE INDEX id012_aaa_ix on eme_species ( collection ); CREATE FULLTEXT INDEX id013_aaa_ix on eme_species ( species_notes ); CREATE INDEX id014_aaa_ix on eme_species ( valid ); CREATE INDEX id015_aaa_ix on eme_species ( checkflag );