-- updated 040502 DDH
-- 13Aug2004 NL added linkedqtl_qtlid (was locus->qtl, but ACEDB XREF removed)
-- 29Sep2004 NL added locusbgsphoto
-- 29Nov2004 NL changed locuschromosome.chromosome from varchar(25) to varchar(50) to avoid truncation
-- 29Nov2004 NL changed locuschromosomearm.chromosomearm from varchar(25) to varchar(50) to avoid truncation
-- 22nov05 dem Added locus.sequenceid.

-- Gene_class: locus->genelocus->genegeneclass->geneclass
-- In_QTL: locus->qtlsignificantmarker->qtl
-- Map: locus->maplocus->map
-- Data: locus->maplocus->map->mapdata
--       locus->mapdatalocus->mapdata
-- Possible_orthologues: locus->locusorthologygroup
-- Homology: locus->locusprobe->sequenceprobe->sequenceblasthits 26Aug2004/NL revised:
         --  locus->locusprobe->sequenceprobe->sequence (bestpep_proteintid)
-- Linkage_data: removed
-- Main_Marker: removed
-- Chrom_Band: removed
-- Interval: removed
-- Bin: removed
-- Bin_marker_for: removed
-- Background_marker: removed
-- Well_ordered: removed
-- TABLE: removed
-- Mapped_bands: removed
-- Gale_orthology_group: removed
-- Gale_orthologues: removed
-- Not_in_QTL: removed
-- Species[1]: removed

create table locus (
	id integer auto_increment primary key,
	name varchar(255),
	-- Sequence.  Only when the Sequence is  _identical_ 
	-- to the Locus, e.g. BAC loci on a physical map.
	sequenceid integer,
	-- Candidate_gene
	candidategene_geneid integer,
	-- Candidate_orthology_group
	locusorthologygroupid integer,
        -- Linked_QTL
        linkedqtl_qtlid integer
) ENGINE=InnoDB;
create index name_index on locus (name);
create index sequenceid_index on locus (sequenceid);
create index candidategene_geneid_index on locus (candidategene_geneid);
create index locusorthologygroupid_index on locus (locusorthologygroupid);
create index linkedqtl_qtlid_index on locus (linkedqtl_qtlid);

create table locusassociatedgene (
	id integer auto_increment primary key,
	locusid integer,
	-- Associated_gene
	geneid integer
) ENGINE=InnoDB;
create index locusid_index on locusassociatedgene (locusid);
create index geneid_index on locusassociatedgene (geneid);

create table locustype (
        id integer auto_increment primary key,
        locusid integer,
	-- Type
	type varchar(100)
) ENGINE=InnoDB;
create index locusid_index on locustype (locusid);

create table locussynonym (
	id integer auto_increment primary key,
	locusid integer,
	-- Correct_name, Other_name, Previous_name, Current_name, Synonym
	type varchar(100),
	name varchar(100),
	referenceid integer
) ENGINE=InnoDB;
create index locusid_index on locussynonym (locusid);
create index referenceid_index on locussynonym (referenceid);

create table locuschromosome (
        id integer auto_increment primary key,
        locusid integer,
	-- Chromosome
	chromosome varchar(50)
) ENGINE=InnoDB;
create index locusid_index on locuschromosome (locusid);

create table locuschromosomearm (
        id integer auto_increment primary key,
        locusid integer,
	-- Chromosome_arm
	chromosomearm varchar(50)
) ENGINE=InnoDB;
create index locusid_index on locuschromosomearm (locusid);

create table locusinsegment (
	id integer auto_increment primary key,
	locusid integer,
	-- In_segment, Not_in_segment
	type varchar(100),
	rearrangementid integer
) ENGINE=InnoDB;
create index locusid_index on locusinsegment (locusid);
create index rearrangementid_index on locusinsegment (rearrangementid);

create table locusininterval (
	id integer auto_increment primary key,
	locusid integer,
	-- In_interval, Not_in_interval
	-- type varchar(100),
	breakpointintervalid integer
) ENGINE=InnoDB;
create index locusid_index on locusininterval (locusid);
create index breakpointintervalid_index on locusininterval (breakpointintervalid);

create table locustwopointdata (
	id integer auto_increment primary key,
	locusid integer,
	-- 2_point
	twopointdataid integer
) ENGINE=InnoDB;
create index locusid_index on locustwopointdata (locusid);
create index twopointdataid_index on locustwopointdata (twopointdataid);

create table locusspecies (
	id integer auto_increment primary key,
	locusid integer,
	-- Species[0]
	speciesid integer
) ENGINE=InnoDB;
create index locusid_index on locusspecies (locusid);
create index speciesid_index on locusspecies (speciesid);

create table locusprobe (
	id integer auto_increment primary key,
	locusid integer,
	-- Probe
	probeid integer,
	referenceid integer
) ENGINE=InnoDB;
create index locusid_index on locusprobe (locusid);
create index probeid_index on locusprobe (probeid);
create index referenceid_index on locusprobe (referenceid);

create table locusimage (
	id integer auto_increment primary key,
	locusid integer,
	-- Image
	imageid integer
) ENGINE=InnoDB;
create index locusid_index on locusimage (locusid);
create index imageid_index on locusimage (imageid);

create table locusreference (
	id integer auto_increment primary key,
	locusid integer,
	-- Reference
	referenceid integer
) ENGINE=InnoDB;
create index locusid_index on locusreference (locusid);
create index referenceid_index on locusreference (referenceid);

create table locusdatasource (
	id integer auto_increment primary key,
	locusid integer,
	-- Data_source
	colleagueid integer,
	date varchar(25)
) ENGINE=InnoDB;
create index locusid_index on locusdatasource (locusid);
create index colleagueid_index on locusdatasource (colleagueid);

create table locusremark (
	id integer auto_increment primary key,
	locusid integer,
	-- Remarks
	type varchar(50),
	remark text
) ENGINE=InnoDB;
create index locusid_index on locusremark (locusid);

create table locusorthologygroup (
	id integer auto_increment primary key,
	name varchar(50)
) ENGINE=InnoDB;
create index name_index on locusorthologygroup (name);

create table locusbgsphoto (
        id integer auto_increment primary key,
        locusid integer,
        -- BGS_photo
        name varchar(100)
) ENGINE=InnoDB;
create index locusid_index on locusbgsphoto (locusid);