-- Ucmp2 table - Elib database -- 1/26/2006 G.O. ### REMINDER ### any changes to this table should also be made ### to ./ucmp2_deleted.sql CREATE TABLE ucmp2 ( # ID numbers seq_num serial, ucmp_coll char(1) not null, spec_id varchar(64) unique not null, # ucmp_coll . specno specno varchar(64) not null, other_specno varchar(128), institution_code varchar(4), # default UCMP spec_archives varchar(255), # Archon record ids # added Aug 15, 2011 # Taxonomy phylum varchar(64), class varchar(64), subclass varchar(64), ordr varchar(64), suborder varchar(64), superfamily varchar(64), family varchar(64), subfamily varchar(64), genus varchar(64), subgenus varchar(64), species varchar(64), subspecies varchar(64), modifiers varchar(64), other_id varchar(128), taxon_author varchar(255), taxon_author_date varchar(16), dc_scientificname varchar(128), # 9/2013 not in use # Specimen Info identified_by varchar(128), identified_date varchar(32), type_status varchar(64), publications text, pub_author varchar(128), pub_date varchar(16), num_individuals varchar(64), element varchar(128), duplicate varchar(64), fate varchar(64), provenance varchar(64), donor varchar(255), loan varchar(64), accn_no varchar(32), # Specimen location rack_num varchar(32), # incr. from 8 7/21/2006 bay_num varchar(32), # incr. from 8 7/21/2006 other_storage varchar(128), slide_num varchar(12), slide_x varchar(12), slide_y varchar(12), slide_type varchar(32), orig_slide_num varchar(64), # Collector remarks text, collector varchar(128), coll_day varchar(2), coll_mon varchar(24), coll_yr varchar(24), field_num varchar(255), pic varchar(19), db_notes varchar(255), index_date datetime, creation_date datetime, created_by varchar(64), last_modified_date datetime, last_modified_by varchar(64), mod_history varchar(255), access varchar(24), # yes/no namesoup text, # added Aug 15, 2011 # Locality table loc_ID_num varchar(24), loc_name varchar(128), cont_ocean varchar(64), # "Africa" country_std varchar(64), # "Myanmar" state_prov_std varchar(64), # "Kwangsi Province" island_group varchar(64), # "Hawaiian Islands" island varchar(64), # "Oahu" county_std varchar(64), # "Nevada County" era_eon varchar(64), period varchar(64), epoch varchar(64), stage varchar(64), storage_age varchar(64), flora_fauna varchar(64), formation varchar(64), formation_marine varchar(64), # added May 10 2018 lat_decimal decimal(12,8), long_decimal decimal(12,8), ll_variance_m decimal(12,8), # coordinateUncertaintyInMeters datum varchar(50), # maxerrorinmeters decimal(10,4), # error in depth bore_depth_m varchar(16), # added 3/28/2013 GO bore_depth_ft varchar(16), # added 3/28/2013 GO tmp_last char(3) # prevent Excel from truncating ); create index ucmp2_accn_no on ucmp2 (accn_no); create index ucmp2_class on ucmp2 (class); create index ucmp2_collector on ucmp2 (collector); create index ucmp2_cont_ocean on ucmp2 (cont_ocean); create index ucmp2_county_std on ucmp2 (county_std); create index ucmp2_country_std on ucmp2 (country_std); create index ucmp2_db_notes on ucmp2 (db_notes); # for batchid create index ucmp2_epoch on ucmp2 (epoch); create index ucmp2_era_eon on ucmp2 (era_eon); create index ucmp2_family on ucmp2 (family); create index ucmp2_field_num on ucmp2 (field_num); create index ucmp2_formation on ucmp2 (formation); create index ucmp2_formation_marine on ucmp2 (formation_marine); create index ucmp2_genus on ucmp2 (genus); create index ucmp2_last_modified_date on ucmp2 (last_modified_date); create index ucmp2_loc_ID_num on ucmp2 (loc_ID_num); create index ucmp2_loc_name on ucmp2 (loc_name); create index ucmp2_ordr on ucmp2 (ordr); create index ucmp2_other_id on ucmp2 (other_id); create index ucmp2_other_specno on ucmp2 (other_specno); create index ucmp2_period on ucmp2 (period); create index ucmp2_phylum on ucmp2 (phylum); create index ucmp2_pic on ucmp2 (pic); create index ucmp2_spec_id on ucmp2 (spec_id); create index ucmp2_species on ucmp2 (species); create index ucmp2_specno on ucmp2 (specno); create index ucmp2_state_prov_std on ucmp2 (state_prov_std); create index ucmp2_storage_age on ucmp2 (storage_age); create index ucmp2_subclass on ucmp2 (subclass); create index ucmp2_suborder on ucmp2 (suborder); create index ucmp2_subspecies on ucmp2 (subspecies); create index ucmp2_ucmp_coll on ucmp2 (ucmp_coll); create fulltext index ucmp2_namesoup on ucmp2 (namesoup); # SELECT * INTO OUTFILE '/data1/mysql_out/ucmp2.dump' from ucmp2; # see ./update_spec_from_loc.sql # get loc stuff from ucmp_loc2 for Micro matching on l.loc_num (hyphen suffix) # select s.loc_id_num,l.loc_prefix,l.loc_num,l.loc_suffix from ucmp2 as s,ucmp_loc2 as l where s.loc_id_num=l.loc_num and s.ucmp_coll='M' and l.ucmp_coll='M'; # and correct loc_id_nums in ucmp2 # update ucmp2 set loc_id_num=concat(loc_id_num,"--") where ucmp_coll='M';