IT4GH : conversion utility for Itis Informix SQL definition file into MySQL format<br />
Output from the December 11, 2006 ITIS database definition.<br />
<hr />************* start cut *************<br />create table change_comments( change_track_id integer not null , chg_cmt_id int not null , change_detail varchar(250) not null , update_date date not null);<br />
load data infile '/tmp/chang00100.unl' into table change_comments fields terminated by '|';<br />
create table change_operations( change_track_id integer not null , chg_op_id integer not null , update_date date not null);<br />
load data infile '/tmp/chang00101.unl' into table change_operations fields terminated by '|';<br />
create table chg_operation_lkp( chg_op_id int not null , change_operation varchar(25) not null , update_date date not null);<br />
load data infile '/tmp/chg_o00102.unl' into table chg_operation_lkp fields terminated by '|';<br />
create table change_tracks( change_track_id int not null , old_tsn integer, change_reason varchar(40) not null , change_initiator varchar(100) not null , change_reviewer varchar(100) not null , change_certifier varchar(100) not null , change_time_stamp datetime not null , tsn integer not null , update_date date not null);<br />
load data infile '/tmp/chang00103.unl' into table change_tracks fields terminated by '|';<br />
create table kingdoms( kingdom_id int not null , kingdom_name char(10) not null , update_date date not null);<br />
load data infile '/tmp/kingd00104.unl' into table kingdoms fields terminated by '|';<br />
create table taxon_unit_types( kingdom_id integer not null , rank_id smallint not null , rank_name char(15) not null , dir_parent_rank_id smallint not null , req_parent_rank_id smallint not null , update_date date not null);<br />
load data infile '/tmp/taxon00105.unl' into table taxon_unit_types fields terminated by '|';<br />
create table geographic_div( tsn integer not null , geographic_value varchar(45) not null , update_date date not null);<br />
load data infile '/tmp/geogr00106.unl' into table geographic_div fields terminated by '|';<br />
create table jurisdiction( tsn integer not null , jurisdiction_value varchar(30) not null , origin varchar(19) not null , update_date date not null);<br />
load data infile '/tmp/juris00107.unl' into table jurisdiction fields terminated by '|';<br />
create table nodc_ids( nodc_id char(12) not null , update_date date not null , tsn integer not null , primary key (nodc_id,tsn));<br />
load data infile '/tmp/nodc_00108.unl' into table nodc_ids fields terminated by '|';<br />
create table taxon_authors_lkp( taxon_author_id int not null , taxon_author varchar(100) not null , update_date date not null , kingdom_id smallint not null);<br />
load data infile '/tmp/taxon00109.unl' into table taxon_authors_lkp fields terminated by '|';<br />
create table synonym_links( tsn integer not null , tsn_accepted integer not null , update_date date not null);<br />
load data infile '/tmp/synon00110.unl' into table synonym_links fields terminated by '|';<br />
create table reference_links( tsn integer not null , doc_id_prefix char(3) not null , documentation_id integer not null , original_desc_ind char(1), init_itis_desc_ind char(1), change_track_id integer, vernacular_name varchar(80), update_date date not null);<br />
load data infile '/tmp/refer00111.unl' into table reference_links fields terminated by '|';<br />
create table experts( expert_id_prefix char(3) not null , expert_id int not null , expert varchar(100) not null , exp_comment varchar(255), update_date date not null);<br />
load data infile '/tmp/exper00112.unl' into table experts fields terminated by '|';<br />
create table other_sources( source_id_prefix char(3) not null , source_id int not null , source_type char(10) not null , source varchar(64) not null , version char(10) not null , acquisition_date date not null , source_comment varchar(255), update_date date not null);<br />
load data infile '/tmp/other00113.unl' into table other_sources fields terminated by '|';<br />
create table publications( pub_id_prefix char(3) not null , publication_id int not null , reference_author varchar(100) not null , title varchar(255), publication_name varchar(255) not null , listed_pub_date date, actual_pub_date date not null , publisher varchar(80), pub_place varchar(40), isbn varchar(16), issn varchar(16), pages varchar(15), pub_comment varchar(255), update_date date not null);<br />
load data infile '/tmp/publi00114.unl' into table publications fields terminated by '|';<br />
create table reviews( tsn integer not null , review_start_date date not null , review_end_date date, review_reason varchar(25) not null , reviewer varchar(100) not null , review_comment varchar(255), update_date date not null);<br />
load data infile '/tmp/revie00115.unl' into table reviews fields terminated by '|';<br />
create table tu_comments_links( tsn integer not null , comment_id integer not null , update_date date not null);<br />
load data infile '/tmp/tu_co00116.unl' into table tu_comments_links fields terminated by '|';<br />
create table comments( comment_id int not null , commentator varchar(100), comment_detail text not null , comment_time_stamp datetime not null , update_date date not null);<br />
load data infile '/tmp/comme00117.unl' into table comments fields terminated by '|';<br />
create table taxonomic_units( tsn int not null , unit_ind1 char(1), unit_name1 char(35) not null , unit_ind2 char(1), unit_name2 varchar(35), unit_ind3 varchar(7), unit_name3 varchar(35), unit_ind4 varchar(7), unit_name4 varchar(35), unnamed_taxon_ind char(1), usagex varchar(12) not null , unaccept_reason varchar(50), credibility_rtng varchar(40) not null , completeness_rtng char(10), currency_rating char(7), phylo_sort_seq smallint, initial_time_stamp datetime not null , parent_tsn integer, taxon_author_id integer, hybrid_author_id integer, kingdom_id smallint not null , rank_id smallint not null , update_date date not null , uncertain_prnt_ind char(3) );<br />
load data infile '/tmp/taxon00118.unl' into table taxonomic_units fields terminated by '|';<br />
create table vernaculars( tsn integer not null , vernacular_name varchar(80) not null , language varchar(15) not null , approved_ind char(1), update_date date not null , vern_id int not null , index (tsn,vernacular_name,language), primary key (vern_id));<br />
load data infile '/tmp/verna00119.unl' into table vernaculars fields terminated by '|';<br />
create table vern_ref_links( tsn integer not null , doc_id_prefix char(3) not null , documentation_id integer not null , update_date date not null , vern_id integer not null , primary key (tsn,doc_id_prefix,documentation_id,vern_id));<br />
load data infile '/tmp/vern_00120.unl' into table vern_ref_links fields terminated by '|';<br />
create unique index chg_oper_lkp_ky on chg_operation_lkp(chg_op_id)  ;<br />
create unique index chg_operations_key on change_operations(change_track_id,chg_op_id)  ;<br />
create unique index kingdoms_key on kingdoms (kingdom_id) ;<br />
create unique index change_tracks_key on change_tracks(change_track_id)  ;<br />
create unique index geographic_div_key on geographic_div(tsn,geographic_value)  ;<br />
create unique index taxon_unit_type_ky on taxon_unit_types(kingdom_id,rank_id)  ;<br />
create index nodc_ids1 on nodc_ids (tsn) ;<br />
create unique index jurisdiction_key on jurisdiction(tsn,jurisdiction_value)  ;<br />
create unique index synonym_links_key on synonym_links(tsn,tsn_accepted)  ;<br />
create unique index txn_authors_lkp_ky on taxon_authors_lkp(taxon_author_id,kingdom_id)  ;<br />
create unique index experts_key on experts (expert_id_prefix, expert_id)  ;<br />
create unique index reference_links_ky on reference_links(tsn,doc_id_prefix,documentation_id)  ;<br />
create unique index publications_key on publications(pub_id_prefix,publication_id)  ;<br />
create unique index other_sources_key on other_sources(source_id_prefix,source_id)  ;<br />
create unique index tu_comments_lnk_ky on tu_comments_links(tsn,comment_id)  ;<br />
create unique index reviews_key on reviews (tsn, review_start_date)  ;<br />
create index taxon_unit1 on taxonomic_units (parent_tsn) ;<br />
create unique index comments_key on comments (comment_id) ;<br />
create index taxon_unit3 on taxonomic_units (kingdom_id, rank_id)  ;<br />
create index taxon_unit2 on taxonomic_units (unit_name1) ;<br />
create unique index taxonomic_units_ky on taxonomic_units(tsn)  ;<br />
create index taxon_unit4 on taxonomic_units (taxon_author_id) ;<br />
************* end cut *************<br /><span style='color: blue'>Finished, status ok</span><hr />Colin Beckingham 2005 <a href='mailto:colbec@start.ca'>(email)</a><hr />