/* Table name */                                         /* description                                                         */

CREATE TABLE `_brendaactivatingcompound` (               /* related activating compounds                                        */
  `ECID` varchar(50) NOT NULL default '',                /* ec number                                                           */
  `ACTIVATINGCOMPOUND` varchar(200) NOT NULL default '', /* name of activating compound                                         */
  PRIMARY KEY  (`ECID`,`ACTIVATINGCOMPOUND`),
  KEY `ACTIVATINGCOMPOUND` (`ACTIVATINGCOMPOUND`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `_brendacofactor` (                         /* related cofactor                                                    */
  `ECID` varchar(50) NOT NULL default '',                /* ec number                                                           */
  `COFACTOR` varchar(200) NOT NULL default '',           /* cofactor name                                                       */
  PRIMARY KEY  (`ECID`,`COFACTOR`),
  KEY `ECID` (`ECID`),
  KEY `COFACTOR` (`COFACTOR`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `_brendainhibitor` (                        /* related inhibitor                                                   */
  `ECID` varchar(50) NOT NULL default '',                /* ec number                                                           */
  `INHIBITOR` varchar(200) NOT NULL default '',          /* inhibitor name                                                      */
  PRIMARY KEY  (`ECID`,`INHIBITOR`),
  KEY `ECID` (`ECID`),
  KEY `INHIBITOR` (`INHIBITOR`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `_brendametalion` (                         /* related metal ion                                                   */
  `ECID` varchar(50) NOT NULL default '',                /* ec number                                                           */
  `METALION` varchar(200) NOT NULL default '',           /* metal ion name                                                      */
  PRIMARY KEY  (`ECID`,`METALION`),
  KEY `METALION` (`METALION`),
  KEY `ECID` (`ECID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `_brendaproduct` (                          /* enzyme product                                                      */
  `ECID` varchar(50) NOT NULL default '',                /* ec number                                                           */
  `PRODUCT` varchar(200) NOT NULL default '',            /* product of the reaction                                             */
  PRIMARY KEY  (`ECID`,`PRODUCT`),
  KEY `ECID` (`ECID`),
  KEY `PRODUCT` (`PRODUCT`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `_brendarecommendname` (                    /* enzyme recomment name                                               */
  `ECID` varchar(50) NOT NULL default '',                /* ec number                                                           */
  `RECOMMENDNAME` varchar(200) NOT NULL default '',      /* recommend name                                                      */
  PRIMARY KEY  (`ECID`),
  KEY `ECID` (`ECID`),
  KEY `RECOMMENDNAME` (`RECOMMENDNAME`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `_brendaref` (                              /* enzyme reference                                                    */
  `ECID` varchar(50) NOT NULL default '',                /* ec number                                                           */
  `YEAR` varchar(10) NOT NULL default '',                /* publish time                                                        */
  `JOURNAL` varchar(200) NOT NULL default '',            /* journal name                                                        */
  `PAGES` varchar(200) NOT NULL default '',              /* page                                                                */
  `VOL` varchar(10) NOT NULL default '',                 /* volume                                                              */
  `NO` varchar(10) NOT NULL default '',                  /* number                                                              */
  `TITLE` varchar(200) NOT NULL default '',              /* title of citation                                                   */
  `ORGANISM` varchar(50) NOT NULL default '',            /* organism                                                            */
  `AUTHORS` varchar(200) NOT NULL default '',            /* author                                                              */
  KEY `ECID` (`ECID`),
  KEY `TITLE` (`TITLE`),
  KEY `AUTHORS` (`AUTHORS`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `_brendasubstrate` (                        /* enzyme substrate                                                    */
  `ECID` varchar(50) NOT NULL default '',                /* ec number                                                           */
  `SUBSTRATE` varchar(200) NOT NULL default '',          /* substrate of reactions                                              */
  PRIMARY KEY  (`ECID`,`SUBSTRATE`),
  KEY `ECID` (`ECID`),
  KEY `SUBSTRATE` (`SUBSTRATE`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `_brendasynonyms` (                         /* enzyme synonym                                                      */
  `ECID` varchar(50) NOT NULL default '',                /* ec number                                                           */
  `SYNONYMS` varchar(200) NOT NULL default '',           /* synonym of enzymes                                                  */
  PRIMARY KEY  (`ECID`,`SYNONYMS`),
  KEY `SYNONYMS` (`SYNONYMS`),
  KEY `ECID` (`ECID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `abbreviationrules` (                       /* abbreviation rules used to generate new synonyms                    */
  `type` int(11) unsigned NOT NULL default '0',          /* foreign key of coderule.id                                          */
  `ruleid` int(11) NOT NULL auto_increment,              /* GUID for internal use                                               */
  `shortterm` varchar(255) NOT NULL default '',          /* short term for substitution                                         */
  `longterm` varchar(255) NOT NULL default '',           /* long term for substitution                                          */
  PRIMARY KEY  (`ruleid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `blockunit` (                               /* entitywithlocation, interaction and pathway                         */
  `BlockID` int(11) NOT NULL auto_increment,             /* GUID for internal use                                               */
  `Name` varchar(200) default NULL,                      /* name                                                                */
  `Type` varchar(50) default NULL,                       /* can be entitywithcontext, interaction or pathway                    */
  `Organism` varchar(50) default NULL,                   /* Arabidopsis or other plants                                         */
  `Location` varchar(50) default NULL,                   /* subcellular location                                                */
  `Username` varchar(50) default NULL,                   /* who provide the data, foreign key of user.username                  */
  `Date` date default NULL,                              /* input date                                                          */
  `AracycID` varchar(200) default NULL,                  /* unique id if the data comes from Aracyc                             */
  `Source` varchar(50) default NULL,                     /* data source, can be Expert User, or online databases                */
  PRIMARY KEY  (`BlockID`),
  KEY `Name` (`Name`),
  KEY `Type` (`Type`),
  KEY `location` (`Location`),
  KEY `source` (`Source`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `blockunitabbreviation` (                   /* blockunit abbreviation                                              */
  `BlockID` int(11) NOT NULL default '0',                /* foreign key of blockunit.blockid                                    */
  `Abbreviation` varchar(200) NOT NULL default '',       /* abbreviation                                                        */
  PRIMARY KEY  (`BlockID`),
  KEY `abbreviation` (`Abbreviation`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `blockunitcomment` (                        /* blockunit comment                                                   */
  `BlockID` int(11) NOT NULL default '0',                /* foreign key of blockunit.blockid                                    */
  `Comment` text NOT NULL,                               /* comment                                                             */
  `Username` varchar(50) NOT NULL default 'NA',          /* who provide the comment, foreign key of user.username               */
  PRIMARY KEY  (`BlockID`,`Comment`(255),`Username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `blockunitconfidence` (                     /* blockunit confidence                                                */
  `BlockID` int(11) NOT NULL default '0',                /* foreign key of blockunit.blockid                                    */
  `LocationConfidence` int(11) default '0',              /* confidence of subcellular location                                  */
  `FunctionConfidence` int(11) default '0',              /* confidence of function                                              */
  PRIMARY KEY  (`BlockID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `blockunitref` (                            /* blockunit reference                                                 */
  `BlockID` int(11) NOT NULL default '0',                /* foreign key of blockunit.blockid                                    */
  `RefType` varchar(50) NOT NULL default '',             /* reference type, can be PubMedID, literature                         */
  `RefValue` varchar(255) NOT NULL default '',           /* citation text or pubmed id                                          */
  `Username` varchar(50) NOT NULL default 'NA',          /* who provide the data, foreign key of user.username                  */
  PRIMARY KEY  (`BlockID`,`RefType`,`RefValue`,`Username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `blockunitsynonym` (                        /* blockunit synonym                                                   */
  `BlockID` int(11) NOT NULL default '0',                /* foreign key of blockunit.blockid                                    */
  `Synonym` varchar(200) NOT NULL default '',            /* synonym                                                             */
  PRIMARY KEY  (`BlockID`,`Synonym`),
  KEY `Synonym` (`Synonym`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `blockunitsynonymcandidate` (               /* new synonym candidate generated from abbreviation rules             */
  `ID` int(11) NOT NULL auto_increment,                  /* GUID for internal use                                               */
  `blockID` int(11) default NULL,                        /* foreign key of blockunit.blockid                                    */
  `synonym` varchar(255) default NULL,                   /* synonym candidate                                                   */
  PRIMARY KEY  (`ID`),
  KEY `blockid` (`blockID`),
  KEY `synonym` (`synonym`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `chebi_chemical_data` (                     /* directly from ChEBI                                                 */
  `id` int(15) NOT NULL default '0',
  `compound_id` int(15) NOT NULL default '0',
  `formula` text NOT NULL,
  `source` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `compound_id` (`compound_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `chebi_comments` (                          /* directly from ChEBI                                                 */
  `id` int(15) NOT NULL default '0',
  `compound_id` int(15) NOT NULL default '0',
  `text` text NOT NULL,
  `created_on` date NOT NULL default '0000-00-00',
  `datatype` varchar(80) default NULL,
  `datatype_id` int(15) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `compound_id` (`compound_id`),
  KEY `datatype_id` (`datatype_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `chebi_compounds` (                         /* directly from ChEBI                                                 */
  `id` int(15) NOT NULL default '0',
  `name` text,
  `source` varchar(32) NOT NULL default '',
  `parent_id` int(15) default NULL,
  `chebi_accession` varchar(30) NOT NULL default '',
  `status` char(1) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `parent_id` (`parent_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `chebi_database_accession` (                /* directly from ChEBI                                                 */
  `id` int(15) NOT NULL default '0',
  `compound_id` int(15) NOT NULL default '0',
  `accession_int` varchar(255) NOT NULL default '',
  `type` text NOT NULL,
  `source` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `compound_id` (`compound_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `chebi_names` (                             /* directly from ChEBI                                                 */
  `id` int(15) NOT NULL default '0',
  `compound_id` int(15) NOT NULL default '0',
  `name` text NOT NULL,
  `type` text NOT NULL,
  `source` text NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `compound_id` (`compound_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `chebi_ontology` (                          /* directly from ChEBI                                                 */
  `id` int(15) NOT NULL default '0',
  `title` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `chebi_reference` (                         /* directly from ChEBI                                                 */
  `id` int(15) NOT NULL default '0',
  `compound_id` int(15) NOT NULL default '0',
  `reference_id` varchar(60) NOT NULL default '',
  `reference_db_name` varchar(60) NOT NULL default '',
  `location_in_ref` varchar(90) default NULL,
  PRIMARY KEY  (`id`),
  KEY `compound_id` (`compound_id`),
  KEY `reference_id` (`reference_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `chebi_relation` (                          /* directly from ChEBI                                                 */
  `id` int(15) NOT NULL default '0',
  `type` text NOT NULL,
  `init_id` int(15) NOT NULL default '0',
  `final_id` int(15) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `init_id` (`init_id`),
  KEY `final_id` (`final_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `chebi_vertices` (                          /* directly from ChEBI                                                 */
  `id` int(15) NOT NULL default '0',
  `vertice_ref` varchar(60) NOT NULL default '',
  `compound_child_id` int(15) default NULL,
  `ontology_id` int(15) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `unique_ontology_ref` (`vertice_ref`,`ontology_id`),
  KEY `ontology_id` (`ontology_id`),
  KEY `compound_child_id` (`compound_child_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `codeconfidence` (                          /* confidence list                                                     */
  `Code` int(11) NOT NULL auto_increment,                /* GUID for internal use                                               */
  `Value` double default '0',                            /* confidence value,                                                   */
  `Description` varchar(255) default '',                 /* description of the confidence level                                 */
  `Presentation` varchar(255) default '',                /* description of the confidence level, for internal use               */
  PRIMARY KEY  (`Code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `codedatasource` (                          /* data source list                                                    */
  `description` varchar(255) NOT NULL default '',        /* descption of data source                                            */
  `code` int(11) NOT NULL auto_increment,                /* GUID for internal use                                               */
  `name` varchar(50) NOT NULL default '',                /* name of database online databases                                   */
  PRIMARY KEY  (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `codeentitytype` (                          /* entity type list                                                    */
  `code` int(11) NOT NULL auto_increment,                /* GUID for internal use                                               */
  `presentation` varchar(255) default '',                /* description of entity type, for internal use                        */
  `description` varchar(255) default '',                 /* description of entity type                                          */
  `entitytype` varchar(255) NOT NULL default '',         /* entity type, can be gene, protein complex, metabolite etc.          */
  PRIMARY KEY  (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `codeinteractiontype` (                     /* interaction type list                                               */
  `code` int(11) NOT NULL auto_increment,                /* GUID for internal use                                               */
  `description` varchar(255) NOT NULL default '',        /* description of interaction type                                     */
  `name` varchar(255) NOT NULL default '',               /* interaction type, can be catalysis, regulation etc.                 */
  `presentation` varchar(255) NOT NULL default '',       /* description of interaction type, for internal use                   */
  PRIMARY KEY  (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `codelocation` (                            /* subcellular location list                                           */
  `description` varchar(255) default '',                 /* description of subcellular location                                 */
  `code` int(11) NOT NULL auto_increment,                /* GUID for internal use                                               */
  `name` varchar(255) default '',                        /* subcellular location, can be cytosol, plastid etc.                  */
  `presentation` varchar(255) default '',                /* subcellular location, for internal use                              */
  PRIMARY KEY  (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `codeorganism` (                            /* organism list                                                       */
  `code` int(11) NOT NULL auto_increment,                /* GUID for internal use                                               */
  `description` varchar(255) default '',                 /* description of organism                                             */
  `presentation` varchar(255) default '',                /* organism, for internal use                                          */
  `organism` varchar(255) NOT NULL default '',           /* organism, can be Arabidopsis or other plants                        */
  PRIMARY KEY  (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `codereferencetype` (                       /* reference type list                                                 */
  `description` varchar(100) NOT NULL default '',        /* description of reference type                                       */
  `code` int(11) NOT NULL auto_increment,                /* GUID for internal use                                               */
  `name` varchar(255) NOT NULL default '',               /* reference type, can be Literature, PubMed, URL                      */
  PRIMARY KEY  (`code`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `coderule` (                                /* abbrevation generation rule list                                    */
  `id` int(11) NOT NULL auto_increment,                  /* GUID for internal use                                               */
  `description` varchar(255) NOT NULL default '',        /* abbreviation generation rule                                        */
  `presentation` varchar(255) NOT NULL default '',       /* abbreviation generation rule, for internal use                      */
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `compound` (                                /* chemical compound                                                   */
  `EntityID` int(11) NOT NULL default '0',               /* foreign key of entity.entityid                                      */
  `Formula` varchar(50) default NULL,                    /* formula                                                             */
  `MW` double default NULL,                              /* molecular weight                                                    */
  `CAS` varchar(50) default NULL,                        /* CAS id                                                              */
  PRIMARY KEY  (`EntityID`),
  KEY `Formula` (`Formula`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `entity` (                                  /* biological entities                                                 */
  `EntityID` int(11) NOT NULL auto_increment,            /* GUID for biological entity, for internal use                        */
  `Name` varchar(200) default NULL,                      /* name                                                                */
  `Type` varchar(50) default NULL,                       /* entity type, can metabolite, protein complex etc.                   */
  `Organism` varchar(50) default NULL,                   /* Arabidopsis or other plants                                         */
  `Username` varchar(50) default NULL,                   /* who provide the data, foreign key of user.username                  */
  `Date` date default NULL,                              /* when the data is input                                              */
  `Source` varchar(200) default NULL,                    /* the data source, can be Expert User or other online database        */
  PRIMARY KEY  (`EntityID`),
  KEY `Name` (`Name`),
  KEY `Type` (`Type`),
  KEY `source` (`Source`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `entity_ontology` (                         /* mapping between metnetdb and ChEBI                                  */
  `rowid` int(11) NOT NULL auto_increment,               /* GUID for internal use                                               */
  `entityid` int(11) NOT NULL default '0',               /* foreign key of entity.entityid                                      */
  `ontology_id` int(11) NOT NULL default '0',            /* foreign key of chebi_compounds.id                                   */
  `ontology_type` varchar(255) NOT NULL default '',      /* CHEBI'                                                              */
  PRIMARY KEY  (`rowid`),
  KEY `entityid` (`entityid`),
  KEY `ontology_id` (`ontology_id`),
  KEY `ontology_type` (`ontology_type`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `entity_pubchem` (                          /* mapping between metnetdb and pubchem by name or synonym             */
  `rowid` int(11) NOT NULL auto_increment,               /* GUID for internal use                                               */
  `entityid` int(11) NOT NULL default '0',               /* foreign key of entity.entityid                                      */
  `pubchemid` int(11) NOT NULL default '0',              /* foreign key of pubchem_name.row_id                                  */
  PRIMARY KEY  (`rowid`),
  KEY `entityid` (`entityid`),
  KEY `pubchemid` (`pubchemid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `entity_pubchem_name` (                     /* mapping between metnetdb and pubchem by name only                   */
  `rowid` int(11) NOT NULL auto_increment,               /* GUID for internal use                                               */
  `entityid` int(11) NOT NULL default '0',               /* foreign key of entity.entityid                                      */
  `pubchemid` int(11) NOT NULL default '0',              /* foreign key of pubchem_name.row_id                                  */
  PRIMARY KEY  (`rowid`),
  KEY `entityid` (`entityid`),
  KEY `pubchemid` (`pubchemid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `entityabbreviation` (                      /* entity abbreviation                                                 */
  `EntityID` int(11) NOT NULL default '0',               /* foreign key of entity.entityid                                      */
  `Abbreviation` varchar(200) NOT NULL default '',       /* abbreviation                                                        */
  PRIMARY KEY  (`EntityID`,`Abbreviation`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `entitycomment` (                           /* entity comments                                                     */
  `EntityID` int(11) NOT NULL default '0',               /* foreign key of entity.entityid                                      */
  `Comment` text NOT NULL,                               /* comment                                                             */
  `Username` varchar(50) default NULL,                   /* who provide the comment, foreign key of user.username               */
  PRIMARY KEY  (`EntityID`,`Comment`(255))
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `entityref` (                               /* entity reference                                                    */
  `EntityID` int(11) NOT NULL default '0',               /* foreign key of entirty.entityid                                     */
  `RefType` varchar(50) NOT NULL default '',             /* reference type, see also codereferencetype                          */
  `RefValue` varchar(255) NOT NULL default '',           /* reference value, can be citation text or pubmed id                  */
  `Username` varchar(50) NOT NULL default 'NA',          /* who provide the data, foreign key of user.username                  */
  PRIMARY KEY  (`EntityID`,`RefType`,`RefValue`,`Username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `entitysynonym` (                           /* entity synonym                                                      */
  `entityid` int(11) NOT NULL default '0',               /* foreign key of entity.entityid                                      */
  `synonym` varchar(255) NOT NULL default '',            /* synonym                                                             */
  PRIMARY KEY  (`entityid`,`synonym`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `entitywithcontext` (                       /* relation between entity and entitywithlocation                      */
  `BlockID` int(11) NOT NULL default '0',                /* foreign key of blockunit.blockid, for type='EntityWithContext' only */
  `EntityID` int(11) NOT NULL default '0',               /* foreign key of entity.entityid                                      */
  PRIMARY KEY  (`BlockID`,`EntityID`),
  KEY `EntityID` (`EntityID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `externaldb` (                              /* external database link for entities                                 */
  `EntityID` int(11) NOT NULL default '0',               /* foreign key of entity.entityid                                      */
  `DB` varchar(50) NOT NULL default '',                  /* data source, see also codedatasource                                */
  `AC` varchar(50) NOT NULL default '',                  /* accession string for the online database                            */
  PRIMARY KEY  (`EntityID`,`DB`,`AC`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `group_list` (                              /* group list                                                          */
  `groupid` int(11) NOT NULL auto_increment,             /* GUID for internal use                                               */
  `groupname` varchar(255) NOT NULL default '',          /* any name for the group                                              */
  `owner` varchar(255) NOT NULL default '',              /* who create the group, foreign key of user.username                  */
  PRIMARY KEY  (`groupid`),
  KEY `owner` (`owner`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `group_member` (                            /* define the group memebers                                           */
  `groupid` int(11) NOT NULL default '0',                /* foreign key of group_list.groupid                                   */
  `member` varchar(255) NOT NULL default '',             /* which user belongs to the group, foreign key of user.username       */
  PRIMARY KEY  (`groupid`,`member`),
  KEY `groupid` (`groupid`),
  KEY `member` (`member`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `group_share` (                             /* define which blockunit can be shared to which group                 */
  `blockid` int(11) NOT NULL default '0',                /* foreign key of blockunit.blockid                                    */
  `groupid` int(11) NOT NULL default '0',                /* foreign key of group_list.groupid                                   */
  PRIMARY KEY  (`blockid`,`groupid`),
  KEY `groupid` (`groupid`),
  KEY `blockid` (`blockid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `interaction` (                             /* more interaction information                                        */
  `BlockID` int(11) NOT NULL default '0',                /* foreign key of blockunit.blockid, for type='interaction' only       */
  `Type` varchar(255) default NULL,                      /* interaction type, see also codeinteractiontype                      */
  `EC` varchar(50) default NULL,                         /* ec number                                                           */
  `Confidence` double default NULL,                      /* confidence of the interaction                                       */
  `TimeScale` double default NULL,                       /* timescale                                                           */
  `ProReversal` double default NULL,                     /* probability of reversal                                             */
  `Strength` double default NULL,                        /* strength of the interaction                                         */
  PRIMARY KEY  (`BlockID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `interactionparts` (                        /* relation between interaction and entitywithlocation or interaction  */
  `BlockID` int(11) NOT NULL default '0',                /* foreign key of blockunit.blockid, for type='interaction' only       */
  `Role` varchar(10) NOT NULL default '',                /* from' represents the left part, 'to' represent the right part       */
  `Part` int(11) NOT NULL default '0',                   /* foreign key of blockunit.blockid                                    */
  `Coefficient` int(11) default NULL,                    /* coefficient                                                         */
  PRIMARY KEY  (`BlockID`,`Role`,`Part`),
  KEY `Part` (`Part`),
  KEY `Role` (`Role`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `interactionproperties` (                   /* more interaction information                                        */
  `BlockID` int(11) NOT NULL default '0',                /* foreign key of blockunit.blockid, for type='interaction' only       */
  `Enzyme` varchar(200) default NULL,                    /* enzyme name                                                         */
  `Subtype` varchar(200) default NULL,                   /* subtype of interaction, not used                                    */
  `Vmax` double default '0',                             /* V_max                                                               */
  `Km` double default '0',                               /* K_m                                                                 */
  `Ki` double default '0',                               /* K_i                                                                 */
  PRIMARY KEY  (`BlockID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `pathwayparts` (                            /* relation between pathway and interaction                            */
  `BlockID` int(11) NOT NULL default '0',                /* foreign key of blockunit.blockid, for type='pathway' only           */
  `Part` int(11) NOT NULL default '0',                   /* foreign key of blockunit.blockid, for type='interaction' only       */
  PRIMARY KEY  (`BlockID`,`Part`),
  KEY `Part` (`Part`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `pubchem_name` (                            /* directly from PubChem                                               */
  `rowid` int(11) NOT NULL auto_increment,
  `sid` int(11) NOT NULL default '0',
  `name` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`rowid`),
  KEY `name` (`name`),
  KEY `sid` (`sid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `pubchem_synonym` (                         /* directly from PubChem                                               */
  `rowid` int(11) NOT NULL auto_increment,
  `sid` int(11) NOT NULL default '0',
  `synonym` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`rowid`),
  KEY `synonym` (`synonym`),
  KEY `sid` (`sid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tair_affy25k_array_elements` (             /* directly from TAIR                                                  */
  `record_id` int(11) NOT NULL auto_increment,
  `array_element_name` varchar(255) default '',
  `array_element_type` varchar(255) default '',
  `organism` varchar(255) default '',
  `is_control` varchar(50) default '',
  `locus_id` varchar(255) default '',
  `description` text,
  `is_ambiguous` varchar(50) default '',
  `chromosome` varchar(10) default '',
  `start` int(11) default '0',
  `stop` int(11) default '0',
  PRIMARY KEY  (`record_id`),
  KEY `array_element_name` (`array_element_name`),
  KEY `locus_id` (`locus_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tair_affy8k_array_elements` (              /* directly from TAIR                                                  */
  `record_id` int(11) NOT NULL auto_increment,
  `array_element_name` varchar(255) default '',
  `array_element_type` varchar(255) default '',
  `organism` varchar(255) default '',
  `is_control` varchar(50) default '',
  `locus_id` varchar(255) default '',
  `description` text,
  `is_ambiguous` varchar(50) default '',
  `chromosome` varchar(10) default '',
  `start` int(11) default '0',
  `stop` int(11) default '0',
  PRIMARY KEY  (`record_id`),
  KEY `array_element_name` (`array_element_name`),
  KEY `locus_id` (`locus_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tair_afgc_array_elements` (                /* directly from TAIR                                                  */
  `record_id` int(11) NOT NULL auto_increment,
  `suid` varchar(50) default '0',
  `array_element_name` varchar(255) default '',
  `genbank_accession` varchar(255) default '',
  `array_element_type` varchar(50) default '',
  `organism` varchar(255) default '',
  `is_control` varchar(50) default '',
  `locus_id` varchar(255) default '',
  `description` text,
  `is_ambiguous` varchar(50) default '',
  `chromosome` varchar(10) default '',
  `start` int(11) default '0',
  `stop` int(11) default '0',
  `avg_intensity` double default '0',
  `avg_intensity_std_err` double default '0',
  `avg_log_ratio` double default '0',
  `avg_log_ratio_std_err` double default '0',
  PRIMARY KEY  (`record_id`),
  KEY `suid` (`suid`),
  KEY `array_element_name` (`array_element_name`),
  KEY `genebank_accession` (`genbank_accession`),
  KEY `locus_id` (`locus_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tair_agi_to_uniprot` (                     /* directly from TAIR                                                  */
  `record_id` int(11) NOT NULL auto_increment,
  `locus_id` varchar(50) default '',
  `uniprot_id` varchar(255) default '',
  PRIMARY KEY  (`record_id`),
  KEY `locus_id` (`locus_id`),
  KEY `uniprot_id` (`uniprot_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tair_aracyc_dump` (                        /* directly from TAIR                                                  */
  `record_id` int(11) NOT NULL auto_increment,
  `pathway_name` varchar(255) default '',
  `reaction_name` varchar(255) default '',
  `enzyme_name` varchar(255) default '',
  `locus_id` varchar(50) default '',
  PRIMARY KEY  (`record_id`),
  KEY `pathway_name` (`pathway_name`),
  KEY `locus_id` (`locus_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tair_ath_go_goslim` (                      /* directly from TAIR                                                  */
  `record_id` int(11) NOT NULL auto_increment,
  `locus_id` varchar(255) default '',
  `tair_accession` int(11) default '0',
  `object_name` varchar(50) default '',
  `go_term` text,
  `go_id` varchar(50) default '',
  `tair_keyword_id` int(11) default '0',
  `aspect` varchar(50) default '',
  `goslim_term` text,
  `evidence_code` varchar(50) default '',
  `reference` varchar(255) default '',
  `annotation_database` varchar(50) default '',
  `date_annotated` date default '0000-00-00',
  PRIMARY KEY  (`record_id`),
  KEY `locus_id` (`locus_id`),
  KEY `go_id` (`go_id`),
  KEY `tair_keyword_id` (`tair_keyword_id`),
  FULLTEXT KEY `go_term` (`go_term`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tair_sequenced_genes` (                    /* directly from TAIR                                                  */
  `record_id` int(11) NOT NULL auto_increment,
  `locus_id` varchar(20) default '',
  `gene_name` varchar(50) default '',
  `chromosome` varchar(10) default '',
  `description` text,
  PRIMARY KEY  (`record_id`),
  KEY `locus_id` (`locus_id`),
  KEY `gene_name` (`gene_name`),
  FULLTEXT KEY `description` (`description`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `tair_targetp_analysis` (                   /* directly from TAIR                                                  */
  `record_id` int(11) NOT NULL auto_increment,
  `locus_id` varchar(50) default '',
  `target_location` varchar(20) default '',
  `chloroplast_transit_peptide_score` double default '0',
  `mitochondrial_targeting_peptide_score` double default '0',
  `signal_peptide_score` double default '0',
  `other_score` double default '0',
  PRIMARY KEY  (`record_id`),
  KEY `locus_id` (`locus_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `user` (                                    /* user account information                                            */
  `Username` varchar(50) NOT NULL default '',            /* login name, global unique                                           */
  `Firstname` varchar(50) default NULL,                  /* first name                                                          */
  `Lastname` varchar(50) default NULL,                   /* last name                                                           */
  `Email` varchar(100) default NULL,                     /* email                                                               */
  `Affiliation` text,                                    /* affilication                                                        */
  `Classification` varchar(50) default NULL,             /* expert user, normal user, guest                                     */
  `Password` varchar(50) default NULL,                   /* login password                                                      */
  `BMID` int(11) default NULL,                           /* not used                                                            */
  `Date` date default NULL,                              /* creation date                                                       */
  PRIMARY KEY  (`Username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;