--
--

CREATE TABLE ANAD_RELATIONSHIP_TRANSITIVE (
  RTR_OID int(10) unsigned NOT NULL auto_increment,
  RTR_RELATIONSHIP_TYPE_FK varchar(20) NOT NULL,
  RTR_DESCENDENT_FK int(10) unsigned NOT NULL,
  RTR_ANCESTOR_FK int(10) unsigned NOT NULL,
  PRIMARY KEY  (RTR_OID),
  UNIQUE KEY RTR_AK_INDEX (RTR_ANCESTOR_FK,RTR_DESCENDENT_FK,RTR_RELATIONSHIP_TYPE_FK),
  KEY RTR_RELATIONSHIP_TYPE_FK (RTR_RELATIONSHIP_TYPE_FK),
  KEY RTR_DESCENDENT_FK (RTR_DESCENDENT_FK)
);

--
--

CREATE TABLE ANA_RELATIONSHIP (
  REL_OID int(10) unsigned NOT NULL,
  REL_RELATIONSHIP_TYPE_FK varchar(20) NOT NULL,
  REL_CHILD_FK int(10) unsigned NOT NULL,
  REL_PARENT_FK int(10) unsigned NOT NULL,
  REL_SEQUENCE int(10) unsigned default NULL COMMENT 'Order to display the child item underneath the parent item, relative to the parent''s other children.  Ties are broken alphabetically.  If this is NULL, then order is up to application, but general rule is display NULLs in alphabetical order, after all items with a specified order.',
  PRIMARY KEY  (REL_OID),
  KEY REL_AK_IINDEX (REL_PARENT_FK,REL_CHILD_FK,REL_RELATIONSHIP_TYPE_FK),
  KEY REL_RELATIONSHIP_TYPE_FK (REL_RELATIONSHIP_TYPE_FK),
  KEY REL_CHILD_FK (REL_CHILD_FK)
);

--
--

CREATE TABLE ANA_RELATIONSHIP_TYPE (
  RTY_NAME varchar(20) NOT NULL,
  RTY_CHILD_TO_PARENT_DISPLAY varchar(40) NOT NULL,
  RTY_PARENT_TO_CHILD_DISPLAY varchar(40) NOT NULL,
  PRIMARY KEY  (RTY_NAME)
);

--
--

CREATE TABLE ANA_TIMED_NODE (
  ATN_OID int(10) unsigned NOT NULL,
  ATN_NODE_FK int(10) unsigned NOT NULL,
  ATN_STAGE_FK int(10) unsigned NOT NULL,
  ATN_STAGE_MODIFIER_FK varchar(20) default NULL,
  ATN_PUBLIC_ID varchar(20) NOT NULL,
  PRIMARY KEY  (ATN_OID),
  UNIQUE KEY atn_public_id_index (ATN_PUBLIC_ID),
  UNIQUE KEY ATN_AK2_INDEX (ATN_NODE_FK,ATN_STAGE_FK),
  KEY ATN_STAGE_FK (ATN_STAGE_FK),
  KEY ATN_STAGE_MODIFIER_FK (ATN_STAGE_MODIFIER_FK)
);

--
--

CREATE TABLE ANA_STAGE_MODIFIER (
  SMO_NAME varchar(20) NOT NULL,
  PRIMARY KEY  (SMO_NAME)
);

--
--

CREATE TABLE ANAD_PART_OF_PERSPECTIVE (
  POP_PERSPECTIVE_FK varchar(25) NOT NULL COMMENT 'Perspective that APO record is a part of',
  POP_APO_FK int(10) unsigned NOT NULL COMMENT 'ANAD_PART_OF record that belongs to perspective',
  POP_IS_ANCESTOR tinyint(1) NOT NULL COMMENT 'True if this APO is not part of the perspective and is included here only to give context for nodes that are in the perspective.  If your application wants to see only APOs that are contained in the perspective then exclude ancestor records.',
  POP_NODE_FK int(10) unsigned NOT NULL COMMENT 'Node the ANAD_PART_OF record is for.',
  PRIMARY KEY  (POP_PERSPECTIVE_FK,POP_APO_FK),
  KEY POP_APO_FK_INDEX (POP_APO_FK),
  KEY POP_PERSPECTIVE_FK_INDEX (POP_PERSPECTIVE_FK),
  KEY POP_APO_NODE_FK (POP_NODE_FK)
);

--
--

CREATE TABLE ANA_NODE_IN_PERSPECTIVE (
  NIP_OID int(10) unsigned NOT NULL auto_increment COMMENT 'Unique ID of this row.  This is not unique across the database.',
  NIP_PERSPECTIVE_FK varchar(25) NOT NULL COMMENT 'Perspective the node is a part of.',
  NIP_NODE_FK int(10) unsigned NOT NULL COMMENT 'Node that is part of the perspective.',
  PRIMARY KEY  (NIP_OID),
  KEY ANA_NODE_IN_PERSPECTIVE_FKIndex1 (NIP_NODE_FK),
  KEY ANA_NODE_IN_PERSPECTIVE_FKIndex2 (NIP_PERSPECTIVE_FK)
);

--
--

CREATE TABLE ANA_PERSPECTIVE_AMBIT (
  PAM_OID int(10) unsigned NOT NULL,
  PAM_PERSPECTIVE_FK varchar(25) NOT NULL,
  PAM_NODE_FK int(10) unsigned NOT NULL,
  PAM_IS_START tinyint(1) NOT NULL,
  PAM_IS_STOP tinyint(1) NOT NULL,
  PAM_COMMENTS varchar(255) default NULL,
  PRIMARY KEY  (PAM_OID),
  UNIQUE KEY PAM_OID (PAM_OID),
  KEY ANA_PERSPECTIVE_AMBIT_FKIndex1 (PAM_OID),
  KEY ANA_PERSPECTIVE_AMBIT_FKIndex2 (PAM_PERSPECTIVE_FK),
  KEY ANA_PERSPECTIVE_AMBIT_FKIndex3 (PAM_NODE_FK),
  KEY PAM_AK_INDEX (PAM_PERSPECTIVE_FK,PAM_NODE_FK)
);

--
--

CREATE TABLE ANA_ATTRIBUTION (
  ATR_OID int(10) unsigned NOT NULL,
  ATR_OBJECT_FK int(10) unsigned NOT NULL,
  ATR_SOURCE_FK int(10) unsigned NOT NULL,
  ATR_EVIDENCE_FK varchar(50) NOT NULL,
  ATR_COMMENTS varchar(255) default NULL,
  PRIMARY KEY  (ATR_OID),
  UNIQUE KEY ATR_AK_INDEX (ATR_OBJECT_FK,ATR_SOURCE_FK,ATR_EVIDENCE_FK),
  KEY ATR_SOURCE_FK (ATR_SOURCE_FK),
  KEY ATR_EVIDENCE_FK (ATR_EVIDENCE_FK)
);

--
--

CREATE TABLE ANA_EVIDENCE (
  EVI_NAME varchar(50) NOT NULL,
  PRIMARY KEY  (EVI_NAME)
);

--
--

CREATE TABLE ANA_SOURCE (
  SRC_OID int(10) unsigned NOT NULL,
  SRC_NAME varchar(255) NOT NULL,
  SRC_AUTHORS varchar(255) NOT NULL,
  SRC_FORMAT_FK varchar(30) NOT NULL,
  SRC_YEAR year(4) default NULL,
  PRIMARY KEY  (SRC_OID),
  UNIQUE KEY ANA_SOURCE_AK_INDEX (SRC_NAME,SRC_AUTHORS),
  KEY SRC_FORMAT_FK (SRC_FORMAT_FK)
);

--
--

CREATE TABLE ANA_SOURCE_FORMAT (
  SFM_NAME varchar(30) NOT NULL,
  PRIMARY KEY  (SFM_NAME)
);

--
--

CREATE TABLE ANA_LOG (
  LOG_OID int(10) unsigned NOT NULL auto_increment COMMENT 'Unique, persistent ID for this log entry.  This is not unique across the database.',
  LOG_VERSION_FK int(10) unsigned NOT NULL COMMENT 'What version this update became visible in.',
  LOG_COLUMN_NAME varchar(64) NOT NULL COMMENT 'Name of database column that was updated.  Maximum 64 characters in a MySQL column name.',
  LOG_OLD_VALUE varchar(255) default NULL COMMENT 'Old value of column expressed as a character string.',
  LOG_COMMENTS varchar(255) default NULL COMMENT 'Comments on this update.',
  PRIMARY KEY  (LOG_OID),
  UNIQUE KEY ALOG_LOGGED_OID_INDEX (LOG_OID,LOG_VERSION_FK,LOG_COLUMN_NAME),
  KEY ANA_LOG_FKIndex2 (LOG_VERSION_FK)
);

--
--

CREATE TABLE ANA_VERSION (
  VER_OID int(10) unsigned NOT NULL auto_increment,
  VER_NUMBER int(10) unsigned NOT NULL,
  VER_DATE datetime NOT NULL,
  VER_COMMENTS varchar(2000) NOT NULL,
  PRIMARY KEY  (VER_OID),
  UNIQUE KEY AVERS_NUMBER_INDEX (VER_NUMBER),
  UNIQUE KEY AVERS_DATE (VER_DATE)
);

--
--

CREATE TABLE ANA_REPLACED_BY (
  RPL_NEW_PUBLIC_ID varchar(20) NOT NULL,
  RPL_OLD_PUBLIC_ID varchar(20) NOT NULL,
  PRIMARY KEY  (RPL_NEW_PUBLIC_ID,RPL_OLD_PUBLIC_ID),
  KEY RPL_OLD_PUBLIC_ID (RPL_OLD_PUBLIC_ID)
);

--
--

CREATE TABLE ANA_DELETED_PUBLIC_ID (
  DPI_DELETED_PUBLIC_ID varchar(20) NOT NULL,
  DPI_EDITOR_FK int(10) unsigned NOT NULL,
  DPI_REASON_FK varchar(20) NOT NULL,
  DPI_DATETIME datetime NOT NULL,
  DPI_COMMENTS varchar(255) default NULL,
  PRIMARY KEY  (DPI_DELETED_PUBLIC_ID),
  KEY DPI_REASON_FK (DPI_REASON_FK),
  KEY DPI_EDITOR_FK (DPI_EDITOR_FK)
);

--
--

CREATE TABLE ANA_DELETE_REASON (
  DRE_NAME varchar(20) NOT NULL,
  DRE_COMMENTS varchar(255) default NULL,
  PRIMARY KEY  (DRE_NAME)
);

--
--

CREATE TABLE ANA_EDITOR (
  EDI_OID int(10) unsigned NOT NULL,
  EDI_NAME varchar(50) NOT NULL,
  PRIMARY KEY  (EDI_OID),
  UNIQUE KEY ANA_EDITOR_NAME_INDEX (EDI_NAME)
);

--
--

CREATE TABLE ANAD_PART_OF (
  APO_OID int(10) unsigned NOT NULL COMMENT 'OID for this record. Since this table is derived, this does not persist across different generations of table.',
  APO_SPECIES_FK varchar(20) NOT NULL,
  APO_NODE_START_STAGE_FK int(10) unsigned NOT NULL COMMENT 'Start stage for node.',
  APO_NODE_END_STAGE_FK int(10) unsigned NOT NULL COMMENT 'End stage for node.',
  APO_PATH_START_STAGE_FK int(10) unsigned NOT NULL,
  APO_PATH_END_STAGE_FK int(10) unsigned NOT NULL,
  APO_NODE_FK int(10) unsigned NOT NULL,
  APO_SEQUENCE int(10) unsigned NOT NULL,
  APO_DEPTH int(10) unsigned NOT NULL,
  APO_FULL_PATH varchar(255) NOT NULL,
  APO_IS_PRIMARY tinyint(1) NOT NULL,
  APO_IS_PRIMARY_PATH tinyint(1) NOT NULL COMMENT 'True if this is the primary path to this node; false if the path to this node contains a group node.  Every node, including group nodes has exactly 1 primary path to it.',
  APO_PARENT_APO_FK int(10) unsigned default NULL COMMENT 'OID of the ANAD_PART_OF row that is the parent of this row.',
  PRIMARY KEY  (APO_OID),
  UNIQUE KEY APOD_AK_INDEX (APO_SPECIES_FK,APO_SEQUENCE),
  KEY APO_NODE_FK (APO_NODE_FK),
  KEY APO_NODE_START_STAGE_FK_INDEX (APO_NODE_START_STAGE_FK),
  KEY APO_NODE_END_STAGE_FK_INDEX (APO_NODE_END_STAGE_FK),
  KEY APO_PATH_START_STAGE_FOREIGN_KEY (APO_PATH_START_STAGE_FK),
  KEY APO_PATH_END_STAGE_FOREIGN_KEY (APO_PATH_END_STAGE_FK),
  KEY APO_PARENT_APO_FOREIGN_KEY (APO_PARENT_APO_FK)
);

--
--

CREATE TABLE ANA_NODE (
  ANO_OID int(10) unsigned NOT NULL,
  ANO_SPECIES_FK varchar(20) NOT NULL,
  ANO_COMPONENT_NAME varchar(255) NOT NULL,
  ANO_IS_PRIMARY tinyint(1) NOT NULL,
  ANO_IS_GROUP tinyint(1) NOT NULL COMMENT 'True if the node is a group node, False if it is not.',
  ANO_PUBLIC_ID varchar(20) NOT NULL,
  ANO_DESCRIPTION varchar(2000) default NULL COMMENT 'Description of this component.  Can be NULL.',
  PRIMARY KEY  (ANO_OID),
  UNIQUE KEY anode_public_id_index (ANO_PUBLIC_ID),
  KEY anode_component_name_index (ANO_COMPONENT_NAME),
  KEY ANO_SPECIES_FK (ANO_SPECIES_FK)
);

--
--

CREATE TABLE ANA_STAGE (
  STG_OID int(10) unsigned NOT NULL,
  STG_SPECIES_FK varchar(20) NOT NULL,
  STG_NAME varchar(20) NOT NULL,
  STG_SEQUENCE int(10) unsigned NOT NULL,
  STG_DESCRIPTION varchar(2000) default NULL COMMENT 'Description of stage.  Alternatively, could replace this with a URL.',
  STG_SHORT_EXTRA_TEXT varchar(25) default NULL COMMENT 'Very short additional text describing the stage.  This is useful when real estate is tight but you still have enough space to give the user some additional information besides just the somtimes uninformative stage name.  For mouse, this will likely be an equivalent DPC stage range.  For Xenopus, this will likely be the short term for the stage, e.g., blastula.',
  STG_PUBLIC_ID varchar(20) default NULL COMMENT 'Public ID of stage.  Null if stage does not have a public ID.',
  PRIMARY KEY  (STG_OID),
  UNIQUE KEY STG_NAME_INDEX (STG_NAME,STG_SPECIES_FK),
  UNIQUE KEY STG_SEQUENCE_INDEX (STG_SEQUENCE,STG_SPECIES_FK),
  KEY STG_SPECIES_FK (STG_SPECIES_FK),
  KEY STG_PUBLIC_ID_INDEX (STG_PUBLIC_ID)
);

--
--

CREATE TABLE REF_SPECIES (
  RSP_NAME varchar(20) NOT NULL,
  RSP_LATIN_NAME varchar(30) NOT NULL,
  RSP_TIMED_NODE_ID_PREFIX varchar(20) NOT NULL COMMENT 'For species with timed component ontologies (mouse), this string is at the front of all timed component IDs for this species (''EMAP:'').  For species that don''t distinguish timed from untimed components (Xenopus), the prefix is the same for both (''XAO:'').',
  RSP_NODE_ID_PREFIX varchar(20) NOT NULL COMMENT 'This string is at the front of all abstract (untimed) component IDs in this species'' ontology.  For example, ''EMAPA:'' or ''XAO:''.',
  PRIMARY KEY  (RSP_NAME),
  UNIQUE KEY RSP_LATIN_IDX (RSP_LATIN_NAME)
);

--
--

CREATE TABLE ANA_PERSPECTIVE (
  PSP_NAME varchar(25) NOT NULL COMMENT 'Name of perspective',
  PSP_COMMENTS varchar(1024) default NULL COMMENT 'Description of perspective',
  PRIMARY KEY  (PSP_NAME)
) COMMENT='Perspectives are particular views of subsets of the anatomy';

--
--

CREATE TABLE ANA_SYNONYM (
  SYN_OID int(10) unsigned NOT NULL,
  SYN_OBJECT_FK int(10) unsigned NOT NULL,
  SYN_SYNONYM varchar(100) NOT NULL,
  PRIMARY KEY  (SYN_OID),
  UNIQUE KEY SYN_AK_INDEX (SYN_OBJECT_FK,SYN_SYNONYM),
  KEY SYN_SYNONYM_INDEX (SYN_SYNONYM)
);

--
--

CREATE TABLE ANA_OBJECT (
  OBJ_OID int(10) unsigned NOT NULL,
  OBJ_CREATION_DATETIME datetime default NULL,
  OBJ_CREATOR_FK int(10) unsigned default NULL,
  PRIMARY KEY  (OBJ_OID)
);