CREATE TABLE PubSource (
  pub_source_id int(11) NOT NULL auto_increment,
  name varchar(255) default NULL,
  type varchar(50) default NULL,
  publisher varchar(100) default NULL,
  editors varchar(255) default NULL,
  isbn_number varchar(13) default NULL,
  issn_number varchar(10) default NULL,
  tair_pub_source_id int(12) default NULL,
  is_obsolete enum('y','n') NOT NULL default 'n',
  updated_by int(12) default NULL,
  date_updated date default NULL,
  date_entered date default NULL,
  date_last_synchronized date default NULL,
  essn_number varchar(10) default NULL,
  is_textbook enum('y','n') NOT NULL default 'n',
  PRIMARY KEY  (pub_source_id),
  KEY name (name),
  KEY PubSourceIsTextbook_idx (is_textbook)
) ;

--
-- Table structure for table 'PubSourceAlias'
--

CREATE TABLE PubSourceAlias (
  pub_source_alias_id int(12) NOT NULL auto_increment,
  pub_source_id int(12) default NULL,
  tair_pub_source_id int(12) default NULL,
  tair_pub_source_alias_id int(12) default NULL,
  alias varchar(250) default NULL,
  is_standard enum('y','n') default NULL,
  is_obsolete enum('y','n') NOT NULL default 'n',
  date_updated date default NULL,
  updated_by int(12) default NULL,
  date_last_synchronized date default NULL,
  date_entered date default NULL,
  PRIMARY KEY  (pub_source_alias_id)
) ;

--
-- Table structure for table 'URL'
--

CREATE TABLE URL (
  pub_url_id int(12) NOT NULL auto_increment,
  tair_url_id int(12) default NULL,
  url varchar(255) default NULL,
  web_site_name varchar(100) default NULL,
  date_entered date default NULL,
  date_updated date default NULL,
  updated_by int(12) default NULL,
  entered_by int(12) default NULL,
  date_last_synchronized date default NULL,
  is_obsolete enum('y','n') default 'n',
  PRIMARY KEY  (pub_url_id)
) ;

--
-- Table structure for table 'URL_linking'
--

CREATE TABLE URL_linking (
  pub_url_id int(12) default NULL,
  table_name varchar(20) default NULL,
  table_id int(12) default NULL,
  linking_id int(12) NOT NULL auto_increment,
  date_entered date default NULL,
  date_last_synchronized date default NULL,
  entered_by int(12) default NULL,
  updated_by int(12) default NULL,
  date_updated date default NULL,
  PRIMARY KEY  (linking_id)
) ;

--
-- Table structure for table 'annotation_task'
--

CREATE TABLE annotation_task (
  annotation_task_id int(12) NOT NULL auto_increment,
  term_id int(12) NOT NULL default '0',
  assigned_to int(12) NOT NULL default '0',
  status varchar(80) NOT NULL default '',
  date_entered date NOT NULL default '0000-00-00',
  date_updated date default NULL,
  is_closed enum('y','n') default 'n',
  PRIMARY KEY  (annotation_task_id),
  KEY annotation_task_term_id_idx (term_id),
  KEY annotation_task_assigned_to_idx (assigned_to),
  KEY annotation_task_status_idx (status)
) ;

--
-- Table structure for table 'article'
--

CREATE TABLE article (
  article_id int(12) NOT NULL auto_increment,
  publication_id int(12) default NULL,
  reference_id int(12) default NULL,
  pub_source_id int(12) default NULL,
  title text,
  type varchar(20) NOT NULL default 'unknown',
  authors text,
  year smallint(6) default NULL,
  issue varchar(10) default NULL,
  volume varchar(20) default NULL,
  medline_id varchar(10) default NULL,
  biosis_id varchar(10) default NULL,
  agricola_id varchar(20) default NULL,
  pubmed_id varchar(10) default NULL,
  pubmed_central_id varchar(12) default NULL,
  page_start varchar(6) default NULL,
  abstract text,
  is_peer_reviewed enum('y','n') default NULL,
  is_print_reference enum('y','n') default NULL,
  is_electronic_reference enum('y','n') default NULL,
  link varchar(255) default NULL,
  ref_text text,
  is_local_hard enum('y','n') default NULL,
  is_downloaded enum('y','n') default NULL,
  is_scanned enum('y','n') default NULL,
  scanned_date date default NULL,
  scanned_by int(12) default NULL,
  is_obsolete enum('y','n') NOT NULL default 'n',
  date_entered date default NULL,
  replaced_by int(12) default NULL,
  date_updated date default NULL,
  updated_by int(12) default NULL,
  abstract_number varchar(10) default NULL,
  full_text longtext,
  date_last_synchronized date default NULL,
  PRIMARY KEY  (article_id),
  KEY article_reference_id_idx (reference_id),
  KEY article_title_idx (title(200)),
  KEY article_publication_id_idx (publication_id),
  KEY article_pub_source_id_idx (pub_source_id),
  KEY article_year_idx (year),
  KEY article_volume_idx (volume),
  KEY article_page_start_idx (page_start),
  KEY article_pubmed_id_idx (pubmed_id),
  KEY article_is_obsolete_idx (is_obsolete)
) ;

--
-- Table structure for table 'comment'
--

CREATE TABLE comment (
  comment varchar(255) NOT NULL default '',
  comment_type varchar(20) default NULL,
  date_entered date default NULL,
  entered_by int(12) default NULL,
  table_name varchar(30) default NULL,
  table_row_id int(12) default NULL,
  comment_id int(12) NOT NULL auto_increment,
  date_updated date default NULL,
  updated_by int(11) default NULL,
  PRIMARY KEY  (comment_id),
  KEY comment_table_name_idx (table_name),
  KEY comment_table_row_id_idx (table_row_id)
) ;

--
-- Table structure for table 'curators'
--

CREATE TABLE curators (
  id int(11) NOT NULL auto_increment,
  fullname varchar(100) NOT NULL default '',
  username varchar(10) NOT NULL default '',
  password varchar(20) NOT NULL default '',
  initials varchar(4) NOT NULL default '',
  PRIMARY KEY  (id)
) ;

--
-- Table structure for table 'evidence_description'
--

CREATE TABLE evidence_description (
  evidence_description_id int(12) NOT NULL auto_increment,
  evidence_type_id int(12) default NULL,
  description varchar(255) default NULL,
  date_entered date default NULL,
  entered_by int(12) default NULL,
  date_last_synchronized date default NULL,
  date_updated date default NULL,
  tair_id int(12) default NULL,
  is_obsolete enum('y','n') NOT NULL default 'n',
  PRIMARY KEY  (evidence_description_id)
) ;

--
-- Table structure for table 'evidence_type'
--

CREATE TABLE evidence_type (
  evidence_type_id int(12) NOT NULL auto_increment,
  evidence_code varchar(5) default NULL,
  evidence_long varchar(250) default NULL,
  PRIMARY KEY  (evidence_type_id)
) ;

--
-- Table structure for table 'gene_model_type'
--

CREATE TABLE gene_model_type (
  gene_model_type_id int(12) NOT NULL default '0',
  gene_model_type varchar(25) NOT NULL default '',
  PRIMARY KEY  (gene_model_type_id)
) ;

--
-- Table structure for table 'hit'
--

CREATE TABLE hit (
  match_id int(12) NOT NULL auto_increment,
  term_id int(12) NOT NULL default '0',
  match_nr int(11) default NULL,
  article_id int(12) NOT NULL default '0',
  in_title enum('y','n') default NULL,
  in_abstract enum('y','n') default NULL,
  is_valid enum('y','n','m') default NULL,
  validated_by int(12) default NULL,
  search_date date default NULL,
  date_validated date default NULL,
  date_entered date default NULL,
  entered_by int(12) default NULL,
  is_obsolete enum('y','n') NOT NULL default 'n',
  date_updated date default NULL,
  updated_by int(12) default NULL,
  date_last_synchronized date default NULL,
  relationship_type_id int(12) NOT NULL default '37',
  PRIMARY KEY  (match_id),
  KEY term_id (term_id),
  KEY hit_article_id_idx (article_id),
  KEY hit_search_date_idx (search_date)
) ;

--
-- Table structure for table 'name'
--

CREATE TABLE name (
  name varchar(10) default NULL,
  age int(12) default NULL
) ;

--
-- Table structure for table 'pub_allele'
--

CREATE TABLE pub_allele (
  pub_allele_id int(12) NOT NULL auto_increment,
  tair_poly_id int(12) default NULL,
  pub_gene_id int(12) NOT NULL default '0',
  name varchar(20) default NULL,
  type varchar(20) default NULL,
  inheritance varchar(50) default NULL,
  polymorphic_sequence varchar(50) default NULL,
  insertion_type varchar(50) default NULL,
  construct_type varchar(50) default NULL,
  tair_clone_id int(12) default NULL,
  allele_mode varchar(100) default NULL,
  mutation_site varchar(30) default NULL,
  mutagen varchar(255) default NULL,
  tair_genetic_marker_id int(12) default NULL,
  date_updated date default NULL,
  updated_by int(12) default NULL,
  date_entered date default NULL,
  entered_by int(12) default NULL,
  is_obsolete enum('y','n') NOT NULL default 'n',
  replaced_by int(12) default NULL,
  PRIMARY KEY  (pub_allele_id),
  UNIQUE KEY allele_id_index (pub_allele_id),
  KEY allele_name_index (name)
) ;

--
-- Table structure for table 'pub_allele_pub_germplasm'
--

CREATE TABLE pub_allele_pub_germplasm (
  id int(12) NOT NULL auto_increment,
  pub_germplasm_id int(12) NOT NULL default '0',
  pub_allele_id int(12) NOT NULL default '0',
  genotype varchar(20) default NULL,
  segregation_ratio varchar(200) default NULL,
  date_updated date default NULL,
  updated_by int(12) default NULL,
  date_entered date default NULL,
  entered_by int(12) default NULL,
  is_obsolete enum('y','n') NOT NULL default 'n',
  PRIMARY KEY  (id),
  UNIQUE KEY allele_germplasm_id_index (id)
) ;

--
-- Table structure for table 'pub_analysis_reference'
--

CREATE TABLE pub_analysis_reference (
  pub_analysis_ref_id int(12) NOT NULL auto_increment,
  tair_ref_id int(12) default NULL,
  tair_analysis_ref_id int(12) default NULL,
  analysis_software_id int(12) default NULL,
  name varchar(100) default NULL,
  parameters text,
  output_def_criteria text,
  date_run date default NULL,
  is_nucleotide enum('y','n') default NULL,
  is_peptide enum('y','n') default NULL,
  date_last_synchronized date default NULL,
  PRIMARY KEY  (pub_analysis_ref_id)
) ;

--
-- Table structure for table 'pub_communication'
--

CREATE TABLE pub_communication (
  pub_communication_id int(12) NOT NULL auto_increment,
  tair_communication_id int(12) default NULL,
  author varchar(255) NOT NULL default '',
  name varchar(100) default NULL,
  communication text,
  date_entered date default NULL,
  reference_id int(12) default NULL,
  date_last_synchronized date default NULL,
  date_updated date default NULL,
  is_obsolete enum('y','n') NOT NULL default 'n',
  entered_by int(12) default NULL,
  updated_by int(12) default NULL,
  PRIMARY KEY  (pub_communication_id),
  KEY pub_communication_tair_communication_id_idx (tair_communication_id)
) ;

--
-- Table structure for table 'pub_database_reference'
--

CREATE TABLE pub_database_reference (
  pub_database_reference_id int(11) NOT NULL default '0',
  tair_reference_id int(11) default NULL,
  name varchar(100) default NULL,
  description text,
  extdb_table varchar(50) default NULL,
  extdb_field varchar(50) default NULL,
  extdb_id varchar(100) default NULL,
  date_last_updated date default NULL,
  updated_by int(12) default NULL,
  date_last_synchronized date default NULL,
  tair_database_reference_id int(11) default NULL,
  PRIMARY KEY  (pub_database_reference_id)
) ;

--
-- Table structure for table 'pub_gene'
--

CREATE TABLE pub_gene (
  pub_gene_id int(12) NOT NULL auto_increment,
  gene_tair_obj_id int(12) default NULL,
  name varchar(50) default NULL,
  gene_model_type_id int(12) default NULL,
  is_verified enum('Y','N') default NULL,
  is_predicted enum('Y','N') default NULL,
  verification_method varchar(255) default NULL,
  summary text,
  name_type varchar(12) default NULL,
  date_entered date default NULL,
  updated_by int(12) default NULL,
  date_updated date default NULL,
  entered_by int(12) default NULL,
  tair_map_element_id int(12) default NULL,
  tair_gene_id int(12) default NULL,
  is_obsolete enum('Y','N') NOT NULL default 'N',
  replaced_by int(12) default NULL,
  description text,
  is_sequenced enum('Y','N') default NULL,
  chromosome varchar(10) default NULL,
  length float default NULL,
  unit char(2) default NULL,
  validated_by int(12) default NULL,
  submitted_by int(12) default NULL,
  taxonomy varchar(50) default NULL,
  object_type varchar(20) NOT NULL default 'gene',
  refseq_mrna_id varchar(20) default NULL,
  refseq_protein_id varchar(20) default NULL,
  date_last_synchronized date default NULL,
  is_temp enum('y','n') NOT NULL default 'n',
  cached_locus_has_annotations enum('y','n') NOT NULL default 'n',
  is_splice_variant enum('y','n') NOT NULL default 'n',
  PRIMARY KEY  (pub_gene_id),
  KEY pub_gene_gene_tair_obj_id_idx (gene_tair_obj_id),
  KEY pub_gene_name_idx (name),
  KEY pub_gene_tair_gene_id_idx (tair_gene_id),
  KEY pub_gene_tair_map_element_id_idx (tair_map_element_id),
  KEY pub_gene_is_obsolete_idx (is_obsolete),
  KEY pub_gene_name_type_idx (name_type),
  KEY pub_gene_is_temp_idx (is_temp)
) ;

--
-- Table structure for table 'pub_gene_alias'
--

CREATE TABLE pub_gene_alias (
  pub_gene_id int(12) NOT NULL default '0',
  alias varchar(100) NOT NULL default '',
  original_alias varchar(100) NOT NULL default '',
  alias_type varchar(20) default NULL,
  reference_id int(12) default NULL,
  community_id int(12) default NULL,
  pub_gene_alias_id int(12) NOT NULL auto_increment,
  is_obsolete enum('Y','N') NOT NULL default 'N',
  tair_gene_alias_id int(12) default NULL,
  updated_by int(12) default NULL,
  date_updated date default NULL,
  pub_reference_id int(12) default NULL,
  date_last_synchronized date default NULL,
  PRIMARY KEY  (pub_gene_alias_id),
  KEY pub_gene_alias_pub_gene_id_idx (pub_gene_id),
  KEY pub_gene_alias_alias_idx (alias),
  KEY pub_gene_alias_reference_id_idx (reference_id),
  KEY pub_gene_alias_alias_type_index (alias_type),
  KEY pub_gene_alias_is_obsolete_idx (is_obsolete),
  KEY pub_gene_alias_tair_gene_alias_id (tair_gene_alias_id)
) ;

--
-- Table structure for table 'pub_gene_locus'
--

CREATE TABLE pub_gene_locus (
  pub_gene_locus_id int(12) NOT NULL auto_increment,
  pub_locus_id int(12) NOT NULL default '0',
  pub_gene_id int(12) NOT NULL default '0',
  date_updated date default NULL,
  updated_by int(12) default NULL,
  is_obsolete enum('y','n') NOT NULL default 'n',
  date_last_synchronized date default NULL,
  PRIMARY KEY  (pub_gene_locus_id),
  KEY pub_gene_locus_pub_locus_id_inx (pub_locus_id),
  KEY pub_gene_locus_pub_gene_id_inx (pub_gene_id)
) ;

--
-- Table structure for table 'pub_gene_pub_allele'
--

CREATE TABLE pub_gene_pub_allele (
  id int(12) NOT NULL auto_increment,
  pub_gene_id int(12) NOT NULL default '0',
  pub_allele_id int(12) NOT NULL default '0',
  relationship_type varchar(20) default NULL,
  gene_feature_site varchar(30) default NULL,
  date_updated date default NULL,
  updated_by int(12) default NULL,
  date_entered date default NULL,
  entered_by int(12) default NULL,
  is_obsolete enum('y','n') NOT NULL default 'n',
  PRIMARY KEY  (id)
) ;

--
-- Table structure for table 'pub_germplasm'
--

CREATE TABLE pub_germplasm (
  pub_germplasm_id int(11) NOT NULL auto_increment,
  tair_germplasm_id int(12) default NULL,
  species_variant_id int(12) default NULL,
  name varchar(100) default NULL,
  sp_growth_conditions varchar(255) default NULL,
  ploidy varchar(4) default NULL,
  is_aneuploid enum('y','n') default NULL,
  is_mutant enum('y','n') default NULL,
  aneuploid_chromosome varchar(10) default NULL,
  date_updated date default NULL,
  updated_by int(12) default NULL,
  date_entered date default NULL,
  entered_by int(12) default NULL,
  is_obsolete enum('y','n') NOT NULL default 'n',
  replaced_by int(12) default NULL,
  PRIMARY KEY  (pub_germplasm_id),
  UNIQUE KEY germplasm_id_index (pub_germplasm_id),
  KEY germplasm_name_index (name)
) ;

--
-- Table structure for table 'pub_germplasm_pub_phenotype'
--

CREATE TABLE pub_germplasm_pub_phenotype (
  id int(12) NOT NULL auto_increment,
  pub_germplasm_id int(12) NOT NULL default '0',
  pub_phenotype_id int(12) NOT NULL default '0',
  date_updated date default NULL,
  updated_by int(12) default NULL,
  date_entered date default NULL,
  entered_by int(12) default NULL,
  is_obsolete enum('y','n') NOT NULL default 'n',
  PRIMARY KEY  (id),
  UNIQUE KEY germplasm_phenotype_id_index (id)
) ;

--
-- Table structure for table 'pub_locus'
--

CREATE TABLE pub_locus (
  pub_locus_id int(12) NOT NULL auto_increment,
  tair_object_id int(12) default NULL,
  tair_locus_id int(12) default NULL,
  tair_me_id int(12) default NULL,
  taxon_id int(12) default NULL,
  species_variant_id int(12) default NULL,
  name varchar(50) default NULL,
  rep_gene_model_id int(12) default NULL,
  tmp_name varchar(20) default NULL,
  tmp_full_name varchar(100) default NULL,
  is_sequenced enum('y','n') default NULL,
  date_updated date default NULL,
  chromosome varchar(10) default NULL,
  updated_by int(12) default NULL,
  is_obsolete enum('y','n') NOT NULL default 'n',
  community_id int(12) default NULL,
  replaced_by int(12) default NULL,
  bac_name varchar(20) default NULL,
  refseq_locus_id varchar(20) default NULL,
  date_last_synchronized date default NULL,
  date_entered date default NULL,
  PRIMARY KEY  (pub_locus_id),
  KEY pub_locus_name_idx (name),
  KEY pub_locus_tair_locus_id_idx (tair_locus_id),
  KEY pub_locus_rep_gene_model_id_idx (rep_gene_model_id)
) ;

--
-- Table structure for table 'pub_phenotype'
--

CREATE TABLE pub_phenotype (
  pub_phenotype_id int(12) NOT NULL auto_increment,
  tair_phenotype_id int(12) default NULL,
  phenotype varchar(255) default NULL,
  phenotype_text text,
  article_id int(12) default NULL,
  community_id int(12) default NULL,
  date_updated date default NULL,
  updated_by int(12) default NULL,
  date_entered date default NULL,
  entered_by int(12) default NULL,
  is_obsolete enum('y','n') NOT NULL default 'n',
  replaced_by int(12) default NULL,
  PRIMARY KEY  (pub_phenotype_id),
  UNIQUE KEY phenotype_id_index (pub_phenotype_id),
  KEY phenotype_index (phenotype)
) ;

--
-- Table structure for table 'pub_protocol'
--

CREATE TABLE pub_protocol (
  pub_protocol_id int(12) NOT NULL auto_increment,
  pub_reference_id int(12) default NULL,
  title text,
  authors text,
  description text,
  temp_protocol_source text,
  date_entered date default NULL,
  entered_by int(12) default NULL,
  date_updated date default NULL,
  updated_by int(12) default NULL,
  is_obsolete enum('y','n') NOT NULL default 'n',
  date_last_sychronized date default NULL,
  PRIMARY KEY  (pub_protocol_id)
) ;

--
-- Table structure for table 'pub_reference'
--

CREATE TABLE pub_reference (
  pub_reference_id int(12) NOT NULL auto_increment,
  tair_reference_id int(12) default NULL,
  table_name varchar(30) default NULL,
  source_table_id int(12) default NULL,
  date_entered date default NULL,
  date_updated date default NULL,
  entered_by int(12) default NULL,
  updated_by int(12) default NULL,
  date_last_synchronized date default NULL,
  PRIMARY KEY  (pub_reference_id),
  UNIQUE KEY pub_reference_composite_idx (table_name,source_table_id),
  KEY pub_reference_tair_reference_id_idx (tair_reference_id),
  KEY pub_reference_table_name_idx (table_name),
  KEY pub_reference_source_table_id_idx (source_table_id)
) ;

--
-- Table structure for table 'pub_sequence'
--

CREATE TABLE pub_sequence (
  sequence_type varchar(20) default NULL,
  db_name varchar(20) default NULL,
  db_accession varchar(20) default NULL,
  genbank_accession varchar(20) default NULL,
  nucleotide_sequence_type enum('genomic','mRNA') default NULL,
  aa_sequence_type varchar(20) default NULL,
  is_cds enum('Y','N') NOT NULL default 'N',
  is_full_length enum('y','n') default NULL,
  sequence_id int(12) NOT NULL auto_increment,
  tair_object_id int(12) default NULL,
  is_obsolete enum('Y','N') NOT NULL default 'N',
  genbank_gi varchar(20) default NULL,
  community_id int(12) default NULL,
  organization varchar(20) default NULL,
  definition varchar(255) default NULL,
  sequence longtext,
  date_updated date default NULL,
  updated_by int(12) default NULL,
  name varchar(50) default NULL,
  tair_sequence_id int(12) default NULL,
  species_variant_id int(12) default NULL,
  replaced_by int(12) default NULL,
  genome_type varchar(20) default NULL,
  calc_pi double default NULL,
  calc_mw double default NULL,
  swissprot_id varchar(20) default NULL,
  ec_number varchar(20) default NULL,
  date_last_synchronized date default NULL,
  md5sum varchar(32) default NULL,
  date_entered date default NULL,
  sequence_class varchar(30) NOT NULL default 'unknown',
  PRIMARY KEY  (sequence_id),
  KEY sequence_id (sequence_id),
  KEY pub_sequence_nucleotide_sequence_type_idx (nucleotide_sequence_type),
  KEY pub_sequence_db_name_idx (db_name),
  KEY pub_sequence_db_accession_idx (db_accession),
  KEY pub_sequence_tair_sequence_id_idx (tair_sequence_id),
  KEY pub_sequence_aa_sequence_type_idx (aa_sequence_type),
  KEY pub_sequence_name_idx (name),
  KEY pub_sequence_sequence_type_idx (sequence_type),
  KEY pub_sequence_genbank_gi_idx (genbank_gi),
  KEY pub_sequence_md5sum_idx (md5sum),
  KEY pub_sequence_sequence_class_idx (sequence_class),
  KEY pub_sequence_tair_object_id_idx (tair_object_id)
) ;

--
-- Table structure for table 'pub_sequence_pub_gene'
--

CREATE TABLE pub_sequence_pub_gene (
  id int(12) NOT NULL auto_increment,
  pub_sequence_id int(12) NOT NULL default '0',
  pub_gene_id int(12) NOT NULL default '0',
  is_obsolete enum('Y','N') NOT NULL default 'N',
  date_updated date default NULL,
  updated_by int(12) default NULL,
  date_last_synchronized date default NULL,
  PRIMARY KEY  (id),
  KEY pub_sequence_id (pub_sequence_id,pub_gene_id),
  KEY pub_sequence_pub_gene_pub_gene_id_idx (pub_gene_id)
) ;

--
-- Table structure for table 'pubmed_journal'
--

CREATE TABLE pubmed_journal (
  pubmed_journal_id int(12) NOT NULL default '0',
  title varchar(200) default NULL,
  medabbr varchar(200) default NULL,
  isoabbr varchar(200) default NULL,
  issn_number varchar(20) default NULL,
  essn_number varchar(20) default NULL,
  nlmid varchar(20) default NULL,
  date_entered date default NULL,
  PRIMARY KEY  (pubmed_journal_id)
) ;

--
-- Table structure for table 'reference'
--

CREATE TABLE reference (
  reference_id int(12) NOT NULL default '0',
  type varchar(30) default NULL,
  PRIMARY KEY  (reference_id)
) ;

--
-- Table structure for table 'reference2reference'
--

CREATE TABLE reference2reference (
  id int(12) NOT NULL auto_increment,
  subject_pub_reference_id int(12) NOT NULL default '0',
  object_pub_reference_id int(12) NOT NULL default '0',
  relationship_type_id int(12) NOT NULL default '0',
  date_entered date default NULL,
  entered_by int(12) default NULL,
  date_updated date default NULL,
  updated_by int(12) default NULL,
  PRIMARY KEY  (id)
) ;

--
-- Table structure for table 'relationship_type'
--

CREATE TABLE relationship_type (
  relationship_type_id int(11) NOT NULL auto_increment,
  description varchar(255) NOT NULL default '',
  long_description varchar(255) default NULL,
  category varchar(20) NOT NULL default 'gene2term',
  is_obsolete enum('y','n') default 'n',
  date_updated date default NULL,
  updated_by int(12) default NULL,
  entered_by int(12) default NULL,
  date_entered int(12) default NULL,
  date_last_synchronized date default NULL,
  tair_relationship_type_id int(11) default NULL,
  PRIMARY KEY  (relationship_type_id)
) ;

--
-- Table structure for table 'sequence_accession'
--

CREATE TABLE sequence_accession (
  id int(12) NOT NULL auto_increment,
  db_name varchar(20) NOT NULL default '',
  db_accession varchar(20) NOT NULL default '',
  pub_sequence_id int(12) NOT NULL default '0',
  PRIMARY KEY  (id),
  UNIQUE KEY sequence_accession_constraint_idx (db_name,db_accession)
) ;

--
-- Table structure for table 'stop_words'
--

CREATE TABLE stop_words (
  stop_word_id int(12) NOT NULL auto_increment,
  stop_word varchar(200) NOT NULL default '',
  PRIMARY KEY  (stop_word_id),
  UNIQUE KEY stop_words_stop_word_idx (stop_word)
) ;

--
-- Table structure for table 'term'
--

CREATE TABLE term (
  term_id int(12) NOT NULL auto_increment,
  external_id varchar(12) default NULL,
  name varchar(200) default NULL,
  type varchar(4) default NULL,
  date_entered date default NULL,
  entered_by int(12) default NULL,
  definition text,
  reference varchar(100) default NULL,
  pub_gene_id int(12) default NULL,
  is_temp enum('y','n') NOT NULL default 'n',
  date_updated date default NULL,
  updated_by int(12) default NULL,
  is_obsolete enum('y','n') NOT NULL default 'n',
  replaced_by int(12) default NULL,
  parentage text,
  tair_keyword_id int(12) default NULL,
  date_last_synchronized date default NULL,
  PRIMARY KEY  (term_id),
  KEY name (name),
  KEY term_pub_gene_id_idx (pub_gene_id),
  KEY term_external_id_idx (external_id)
) ;

--
-- Table structure for table 'term2term'
--

CREATE TABLE term2term (
  id int(12) NOT NULL auto_increment,
  term1_id int(12) NOT NULL default '0',
  term2_id int(12) NOT NULL default '0',
  relationship_type_id int(12) NOT NULL default '0',
  PRIMARY KEY  (id),
  KEY term2term_term1_id_idx (term1_id),
  KEY term2term_term2_id_idx (term2_id),
  KEY term2term_relationship_type_id_idx (relationship_type_id)
) ;

--
-- Table structure for table 'term_alias'
--

CREATE TABLE term_alias (
  term_alias_id int(12) NOT NULL auto_increment,
  term_id int(12) default NULL,
  alias varchar(100) default NULL,
  original_alias varchar(100) default NULL,
  alias_type varchar(20) default NULL,
  reference_id int(12) default NULL,
  community_id int(12) default NULL,
  is_obsolete enum('y','n') default NULL,
  date_updated date default NULL,
  updated_by int(12) default NULL,
  date_last_synchronized date default NULL,
  tair_synonym_id int(12) default NULL,
  PRIMARY KEY  (term_alias_id),
  KEY term_alias_term_id_idx (term_id)
) ;

--
-- Table structure for table 'term_annotation'
--

CREATE TABLE term_annotation (
  term_annotation_id int(12) NOT NULL auto_increment,
  subject_term_id int(12) NOT NULL default '0',
  object_term_id int(12) NOT NULL default '0',
  relationship_type_id int(12) NOT NULL default '0',
  evidence_with text,
  evidence_description text,
  entered_date date NOT NULL default '0000-00-00',
  entered_by int(12) NOT NULL default '0',
  is_obsolete enum('y','n') NOT NULL default 'n',
  obsoleted_by int(12) default NULL,
  is_temporary enum('y','n') NOT NULL default 'n',
  evidence_type_id int(12) NOT NULL default '0',
  annotated_date date default NULL,
  updated_by int(12) default NULL,
  date_updated date default NULL,
  pub_reference_id int(12) default NULL,
  date_last_synchronized date default NULL,
  PRIMARY KEY  (term_annotation_id),
  KEY term_annotation_object_term_id_idx (object_term_id),
  KEY term_annotation_subject_term_id_idx (subject_term_id),
  KEY term_annotation_pub_reference_id_idx (pub_reference_id)
) ;

--
-- Table structure for table 'term_type'
--

CREATE TABLE term_type (
  id varchar(4) NOT NULL default '',
  title varchar(20) NOT NULL default '',
  description text NOT NULL,
  date_updated date default NULL,
  is_obsolete enum('y','n') NOT NULL default 'n',
  tair_keyword_id int(12) default NULL,
  date_last_synchronized date default NULL,
  PRIMARY KEY  (id)
) ;

--
-- Table structure for table 'update_history'
--

CREATE TABLE update_history (
  update_history_id int(12) NOT NULL auto_increment,
  table_name varchar(20) NOT NULL default '',
  table_id_1 int(12) NOT NULL default '0',
  table_id_2 int(12) default NULL,
  type enum('insert','update','replacement','obsolete','delete') NOT NULL default 'insert',
  comment varchar(255) default NULL,
  old_value varchar(255) default NULL,
  new_value varchar(255) default NULL,
  date_updated date NOT NULL default '0000-00-00',
  updated_by int(12) NOT NULL default '0',
  date_last_synchronized date default NULL,
  PRIMARY KEY  (update_history_id),
  KEY update_history_table_name_idx (table_name),
  KEY update_history_table_id_1_idx (table_id_1),
  KEY update_history_table_id_2_idx (table_id_2),
  KEY update_history_date_synchronized_idx (date_last_synchronized)
) ;

--
-- Table structure for table 'user'
--

CREATE TABLE user (
  user_id int(11) NOT NULL auto_increment,
  community_id int(12) NOT NULL default '0',
  can_login enum('y','n') NOT NULL default 'y',
  passwd varchar(20) default NULL,
  initials char(3) default NULL,
  fullname varchar(80) default NULL,
  name varchar(20) NOT NULL default '',
  auto_annotate enum('y','n') NOT NULL default 'n',
  email varchar(80) default NULL,
  is_editor enum('y','n') default 'n',
  can_access_pdfs enum('y','n') NOT NULL default 'y',
  PRIMARY KEY  (user_id)
) ;

--
-- Table structure for table 'word'
--

CREATE TABLE word (
  word_id int(12) NOT NULL auto_increment,
  name varchar(200) default NULL,
  PRIMARY KEY  (word_id),
  KEY name (name)
) ;

--
-- Table structure for table 'word_index'
--

CREATE TABLE word_index (
  match_id int(12) NOT NULL default '0',
  word_id int(12) default NULL,
  article_id int(12) default NULL,
  match_nr int(11) default NULL,
  PRIMARY KEY  (match_id),
  KEY word_id (word_id)
) ;

