-- updated 040423 DDH
-- 29Nov2004 NL changed probeprimer.ampconditions from varchar to text to avoid truncation
-- 29Nov2004 NL changed probeexternaldb.name from varchar(25) to varchar(75) to avoid truncation
 
-- Locus: probe->locusprobe->locus
-- Sequence: probe->sequenceprobe->sequence
-- Source_gene: probe->geneclone->gene
-- Source_gene_class: probe->geneclassclone->geneclass
-- Source_allele: probe->geneclone->allelegene->allele
-- Barley_rating: removed
-- Barley_polymorphism: removed
-- Hybridizes_to: removed
-- Positive_probe: removed
-- Positive_pool_probe: removed
-- In_pool: removed
-- Gridded: removed
-- Subcloned_in: removed
-- 5-prime and 3-prime insert and excision enzyme references removed NL 27Oct2004
 
create table probe (
	id integer auto_increment primary key,
	name varchar(255),
	-- Gel
	gelid integer,
	-- DNA_library
	dnalibrary_libraryid integer,
	-- Source_germplasm
	sourcegermplasm_germplasmid integer,
	-- Vector_enzyme
	vectorenzyme_restrictionenzymeid integer,
	-- Vector_amplificaton
	vectoramplification text,
	-- Bacterial_strain
	bacterialstrain varchar(100),
	-- Antibiotic
	antibiotic varchar(100),
	-- Subclone_of
	subcloneof_probeid integer
) ENGINE=InnoDB;
create index name_index on probe (name);
create index gelid_index on probe (gelid);
create index dnalibrary_libraryid_index on probe (dnalibrary_libraryid);
create index sourcegermplasm_germplasmid_index on probe (sourcegermplasm_germplasmid);
create index vectorenzyme_restrictionenzymeid_index on probe (vectorenzyme_restrictionenzymeid);
create index subcloneof_probeid_index on probe (subcloneof_probeid);

create table probesynonym (
	id integer auto_increment primary key,
	probeid integer,
	-- Other_name, Please_see, Synonym
	type varchar(100),
	name varchar(100),
	referenceid integer
) ENGINE=InnoDB;
create index probeid_index on probesynonym (probeid);
create index referenceid_index on probesynonym (referenceid);

create table proberelatedprobe (
	id integer auto_increment primary key,
	probeid integer,
	-- Related_probe[1]
	relatedprobe_probeid integer,
	-- Related_probe[0]
	helpid integer
) ENGINE=InnoDB;
create index probeid_index on proberelatedprobe (probeid);
create index relatedprobe_probeid_index on proberelatedprobe (relatedprobe_probeid);
create index helpid_index on proberelatedprobe (helpid);

create table probecluster (
	id integer auto_increment primary key,
	name varchar(50),
	-- Similar_probes
	probeid integer
) ENGINE=InnoDB;
create index probeid_index on probecluster (probeid);

create table probereference (
	id integer auto_increment primary key,
	probeid integer,
	-- Reference
	referenceid integer
) ENGINE=InnoDB;
create index probeid_index on probereference (probeid);
create index referenceid_index on probereference (referenceid);

create table probesourcespecies (
	id integer auto_increment primary key,
	probeid integer,
	-- Source_species
	speciesid integer
) ENGINE=InnoDB;
create index probeid_index on probesourcespecies (probeid);
create index speciesid_index on probesourcespecies (speciesid);

create table proberemark (
	id integer auto_increment primary key,
	probeid integer,
	-- General_remark, Specificity, Copy_number, Background
	-- Linkage_Group, Source_tissue, DNA_Origin
	type varchar(50),
	remark text
) ENGINE=InnoDB;
create index probeid_index on proberemark (probeid);

create table probenote (
	id integer auto_increment primary key,
	probeid integer,
	-- Note
	preformattextid integer
) ENGINE=InnoDB;
create index probeid_index on probenote (probeid);
create index preformattextid_index on probenote (preformattextid);

create table probewheatpolymorphism (
	id integer auto_increment primary key,
	probeid integer,
	-- Wheat_polymorphism[1]
	restrictionenzymeid integer,
	-- Wheat_polymorphism[0,2]
	polymorphism varchar(100)
) ENGINE=InnoDB;
create index probeid_index on probewheatpolymorphism (probeid);
create index restrictionenzymeid_index on probewheatpolymorphism (restrictionenzymeid);

create table probetype (
        id integer auto_increment primary key,
        probeid integer,
	-- Type
	type varchar(100)
) ENGINE=InnoDB;
create index probeid_index on probetype (probeid);

create table probeprimer (
        id integer auto_increment primary key,
	probeid integer,
	type varchar(50),
	-- PCR_primers, AFLP_primers, STS_primers
	primeronesequence varchar(100),
	primertwosequence varchar(100),
	-- STS_size, SSR_size, PCR_size
	sizetype varchar(50),
	size varchar(50),
	-- Amplification_conditions
	ampconditions text
) ENGINE=InnoDB;
create index probeid_index on probeprimer (probeid);

-- create table probeprimer (
-- 	id integer auto_increment primary key,
-- 	probeprimersetid integer,
-- 	-- PCR_primers, AFLP_primers, STS_primers
-- 	sequence varchar(100)
-- );

create table probeexcisionenzyme (
	id integer auto_increment primary key,
	probeid integer,
	-- Excision_enzyme[0], Excision_enzyme[1]
	restrictionenzymeid integer
) ENGINE=InnoDB;
create index probeid_index on probeexcisionenzyme (probeid);
create index restrictionenzymeid_index on probeexcisionenzyme (restrictionenzymeid);

create table probehybridizesto (
	id integer auto_increment primary key,
	probeid integer,
	-- Cross_hybridizes_to
	speciesid integer,
	quality varchar(50)
) ENGINE=InnoDB;
create index probeid_index on probehybridizesto (probeid);
create index speciesid_index on probehybridizesto (speciesid);

create table probepolymorphism (
	id integer auto_increment primary key,
	probeid integer,
	-- Polymorphism
	polymorphismid integer,
	summaryscore varchar(50)
) ENGINE=InnoDB;
create index probeid_index on probepolymorphism (probeid);
create index polymorphismid_index on probepolymorphism (polymorphismid);

create table probeinsertenzyme (
	id integer auto_increment primary key,
	probeid integer,
	-- Insert_enzyme[0], Insert_enzyme[1]
	restrictionenzymeid integer
) ENGINE=InnoDB;
create index probeid_index on probeinsertenzyme (probeid);
create index restrictionenzymeid_index on probeinsertenzyme (restrictionenzymeid);

create table probeimage (
	id integer auto_increment primary key,
	probeid integer,
	-- Image
	imageid integer
) ENGINE=InnoDB;
create index probeid_index on probeimage (probeid);
create index imageid_index on probeimage (imageid);

create table probedatasource (
	id integer auto_increment primary key,
	probeid integer,
	-- Data_source
	colleagueid integer,
	date varchar(25)
) ENGINE=InnoDB;
create index probeid_index on probedatasource (probeid);
create index colleagueid_index on probedatasource (colleagueid);

create table probeinfosource (
        id integer auto_increment primary key,
	probeid integer,
	-- Information_source
	referenceid integer
) ENGINE=InnoDB;
create index probeid_index on probeinfosource (probeid);
create index referenceid_index on probeinfosource (referenceid);

create table probeinsertsize (
	id integer auto_increment primary key,
	probeid integer,
	-- Size
	insertsize float
) ENGINE=InnoDB;
create index probeid_index on probeinsertsize (probeid);

create table probevector (
	id integer auto_increment primary key,
	probeid integer,
	-- Vector
	vector varchar(100)
) ENGINE=InnoDB;
create index probeid_index on probevector (probeid);

create table probevectorpcrprimers (
	id integer auto_increment primary key,
	probeid integer,
	-- Vector_PCR_primers
	vectorpcrprimers varchar(100)
) ENGINE=InnoDB;
create index probeid_index on probevectorpcrprimers (probeid);

create table probelocation (
	id integer auto_increment primary key,
	probeid integer,
	-- Location
	colleagueid integer
) ENGINE=InnoDB;
create index probeid_index on probelocation (probeid);
create index colleagueid_index on probelocation (colleagueid);

create table probeauthority (
	id integer auto_increment primary key,
	probeid integer,
	-- Authority
	colleagueid integer
) ENGINE=InnoDB;
create index probeid_index on probeauthority (probeid);
create index colleagueid_index on probeauthority (colleagueid);

create table probeexternaldb (
        id integer auto_increment primary key,
        probeid integer,
	-- External_DB[0]
        name varchar(75),
	-- External_DB[1]
        accession varchar(25),
        url varchar(255)
) ENGINE=InnoDB;
create index probeid_index on probeexternaldb (probeid);