[Gmod-schema-cmts] schema/chado/modules default_schema.sql,1.4,1.5

[Scott C. <sco...@us...>]

Update of /cvsroot/gmod/schema/chado/modules
In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv25786/modules

Modified Files:
	default_schema.sql 
Log Message:
updating AutoDBI.PL and default_schema thinking about a freeze.


Index: default_schema.sql
===================================================================
RCS file: /cvsroot/gmod/schema/chado/modules/default_schema.sql,v
retrieving revision 1.4
retrieving revision 1.5
diff -C2 -d -r1.4 -r1.5
*** default_schema.sql	12 Jul 2004 20:51:39 -0000	1.4
--- default_schema.sql	16 Jul 2004 03:41:43 -0000	1.5
***************
*** 454,458 ****
         timelastmodified timestamp not null default current_timestamp,
  
!        unique(organism_id,uniquename, type_id)
  );
  -- dbxref_id here is intended for the primary dbxref for this feature.   
--- 454,458 ----
         timelastmodified timestamp not null default current_timestamp,
  
!        unique(organism_id, uniquename, type_id)
  );
  -- dbxref_id here is intended for the primary dbxref for this feature.   
***************
*** 603,607 ****
         value text not null default '',
         rank int not null default 0,
- 
         unique(feature_id, type_id, value, rank)
  );
--- 603,606 ----
***************
*** 672,678 ****
         type_id int not null,
         foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
!        rank int,
! 
!        unique(subject_id, object_id, type_id)
  );
  create index feature_relationship_idx1 on feature_relationship (subject_id);
--- 671,677 ----
         type_id int not null,
         foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
!        value text null,
!        rank int not null default 0,
!        unique(subject_id, object_id, type_id, rank)
  );
  create index feature_relationship_idx1 on feature_relationship (subject_id);
***************
*** 682,685 ****
--- 681,701 ----
  
  -- ================================================
+ -- TABLE: feature_relationship_pub
+ -- ================================================
+  
+ create table feature_relationship_pub (
+ 	feature_relationship_pub_id serial not null,
+ 	primary key (feature_relationship_pub_id),
+ 	feature_relationship_id int not null,
+ 	foreign key (feature_relationship_id) references feature_relationship (feature_relationship_id) on delete cascade INITIALLY DEFERRED,
+ 	pub_id int not null,
+ 	foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
+  
+ 	unique(feature_relationship_id, pub_id)
+  );
+  create index feature_relationship_pub_idx1 on feature_relationship_pub (feature_relationship_id);
+  create index feature_relationship_pub_idx2 on feature_relationship_pub (pub_id);
+  
+ -- ================================================
  -- TABLE: feature_cvterm
  -- ================================================
***************
*** 760,767 ****
  --
  -- changes 2003-11-10:
! --   incorporating suggestions to make everything a gcontext; use gcontext_relationship
! --   to make some gcontexts derivable from others. we would incorporate environment
! --   this way - just add the environment descriptors as properties of the child
! --   gcontext
  --
  -- for modeling simple or complex genetic screens
--- 776,783 ----
  --
  -- changes 2003-11-10:
! --   incorporating suggestions to make everything a gcontext; use 
! --   gcontext_relationship to make some gcontexts derivable from others. we 
! --   would incorporate environment this way - just add the environment 
! --   descriptors as properties of the child gcontext
  --
  -- for modeling simple or complex genetic screens
***************
*** 781,785 ****
  -- [id:FBgn001, type:gene] <-- [id:FBal001, type:gene]
  --
! -- with the genetic module, phenotypes can either be attached
  -- to features of type sequence_variation, or to features of
  -- type 'gene' (in the case of mutant alleles).
--- 797,801 ----
  -- [id:FBgn001, type:gene] <-- [id:FBal001, type:gene]
  --
! -- with the genetic module, features can either be attached
  -- to features of type sequence_variation, or to features of
  -- type 'gene' (in the case of mutant alleles).
***************
*** 947,956 ****
  -- uniquename          : a human-readable unique identifier
  --
! CREATE TABLE gcontext (
!     gcontext_id          SERIAL PRIMARY KEY NOT NULL,
!     uniquename           VARCHAR(255) NOT NULL,
!     description          TEXT,
!     pub_id               INTEGER      NOT NULL REFERENCES pub(pub_id)
  );
  -- ****************************************
  
--- 963,978 ----
  -- uniquename          : a human-readable unique identifier
  --
! 
! create table gcontext (
! 	gcontext_id	serial not null,
! 	primary key (gcontext_id),
! 	uniquename	varchar(255) not null,
! 	description	text,
! 	pub_id	int not null,
! 	foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
! 
! 	unique(uniquename)
  );
+ create index gcontext_idx1 on gcontext(uniquename);
  -- ****************************************
  
***************
*** 964,973 ****
  -- 
  --
! CREATE TABLE gcontext_relationship (
!     gcontext_relationship_id SERIAL PRIMARY KEY NOT NULL,
!     subjectgc_id         INTEGER      NOT NULL REFERENCES gcontext(gcontext_id),
!     objectgc_id          INTEGER      NOT NULL REFERENCES gcontext(gcontext_id),
!     type_id              INTEGER      NOT NULL REFERENCES cvterm(cvterm_id)
  );
  -- ****************************************
  
--- 986,1004 ----
  -- 
  --
! create table gcontext_relationship (
! 	gcontext_relationship_id	serial not null,
! 	primary key (gcontext_relationship_id),
! 	subjectgc_id	int not null,
! 	foreign key (subjectgc_id) references gcontext (gcontext_id) on delete cascade INITIALLY DEFERRED,
! 	objectgc_id	int not null, 
! 	foreign key (objectgc_id) references gcontext (gcontext_id) on delete cascade INITIALLY DEFERRED,
!  	type_id	int not null,
! 	foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
! 
! 	unique(subjectgc_id, objectgc_id, type_id)
  );
+ create index gcontext_relationship_idx1 on gcontext_relationship (subjectgc_id);
+ create index gcontext_relationship_idx2 on gcontext_relationship (objectgc_id);
+ create index gcontext_relationship_idx3 on gcontext_relationship (type_id);
  -- ****************************************
  
***************
*** 993,1005 ****
  -- group               : spatially distinguishable group
  --
! CREATE TABLE feature_gcontext (
!     feature_gcontext_id               SERIAL PRIMARY KEY NOT NULL,
!     feature_id           INTEGER      NOT NULL REFERENCES feature(feature_id),
!     gcontext_id          INTEGER      NOT NULL REFERENCES gcontext(gcontext_id),
!     chromosome_id        INTEGER      REFERENCES feature(feature_id),
!     rank                 INTEGER      NOT NULL,
!     cgroup               INTEGER      NOT NULL,
!     cvterm_id            INTEGER      NOT NULL REFERENCES cvterm(cvterm_id)
  );
  -- ****************************************
  
--- 1024,1045 ----
  -- group               : spatially distinguishable group
  --
! create table feature_gcontext (
! 	feature_gcontext_id	serial not null,
! 	primary key (feature_gcontext_id),
! 	feature_id	int not null,
! 	foreign key (feature_id) references feature (feature_id) on delete cascade INITIALLY DEFERRED,
! 	gcontext_id	int not null,
! 	foreign key (gcontext_id) references gcontext (gcontext_id) on delete cascade INITIALLY DEFERRED,
! 	chromosome_id	int,
! 	foreign key (chromosome_id) references feature(feature_id) on delete set null INITIALLY DEFERRED,
! 	rank	int not null,
! 	cgroup	int not null,
! 	cvterm_id	int not null,
! 	foreign key (cvterm_id) references cvterm(cvterm_id) on delete cascade INITIALLY DEFERRED,
! 
! 	unique(feature_id, gcontext_id, cvterm_id)
  );
+ create index feature_gcontext_idx1 on feature_gcontext (feature_id);
+ create index feature_gcontext_idx2 on feature_gcontext (gcontext_id);
  -- ****************************************
  
***************
*** 1012,1021 ****
  -- value               : unconstrained free text value
  --
! CREATE TABLE gcontextprop (
!     gcontextprop_id                   SERIAL PRIMARY KEY NOT NULL,
!     gcontext_id          INTEGER      NOT NULL REFERENCES gcontext(gcontext_id),
!     type_id              INTEGER      NOT NULL REFERENCES cvterm(cvterm_id),
!     value                TEXT         NOT NULL
  );
  -- ****************************************
  
--- 1052,1068 ----
  -- value               : unconstrained free text value
  --
! create table gcontextprop (
! 	gcontextprop_id	serial not null,
! 	primary key (gcontextprop_id),
! 	gcontext_id	int not null,
! 	foreign key (gcontext_id) references gcontext (gcontext_id) on delete cascade INITIALLY DEFERRED,
! 	type_id	int not null,
! 	foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
! 	value	text not null,
! 
! 	unique(gcontext_id, type_id, value)
  );
+ create index gcontextprop_idx1 on gcontextprop (gcontext_id);
+ create index gcontextprop_idx2 on gcontextprop (type_id);
  -- ****************************************
  
***************
*** 1038,1051 ****
  -- assay_id            : e.g. name of specific test
  --
! CREATE TABLE phenstatement (
!     phenstatement_id     SERIAL PRIMARY KEY NOT NULL,
!     gcontext_id          INTEGER      NOT NULL REFERENCES gcontext(gcontext_id),
!     dbxref_id            INTEGER      NOT NULL REFERENCES dbxref(dbxref_id),
!     observable_id        INTEGER      NOT NULL REFERENCES cvterm(cvterm_id),
!     attr_id              INTEGER      REFERENCES cvterm(cvterm_id),
!     value                TEXT,
!     cvalue_id            INTEGER      REFERENCES cvterm(cvterm_id),
!     assay_id             INTEGER      REFERENCES cvterm(cvterm_id)
  );
  -- ****************************************
  
--- 1085,1110 ----
  -- assay_id            : e.g. name of specific test
  --
! create table phenstatement (
! 	phenstatement_id	serial not null,
! 	primary key (phenstatement_id),
! 	gcontext_id int not null,
! 	foreign key (gcontext_id) references gcontext (gcontext_id) on delete cascade INITIALLY DEFERRED,
! 	dbxref_id	int not null,
! 	foreign key (dbxref_id) references dbxref (dbxref_id) on delete cascade INITIALLY DEFERRED,
! 	observable_id	int not null,
! 	foreign key (observable_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
! 	attr_id	int,
! 	foreign key (attr_id) references cvterm (cvterm_id) on delete set null INITIALLY DEFERRED,
! 	value	text,
! 	cvalue_id	int,
! 	foreign key (cvalue_id) references cvterm (cvterm_id) on delete set null INITIALLY DEFERRED,
! 	assay_id	int,
! 	foreign key (assay_id) references cvterm (cvterm_id) on delete set null INITIALLY DEFERRED,
! 
! 	unique(gcontext_id, dbxref_id, observable_id)	
  );
+ create index phenstatement_idx1 on phenstatement (gcontext_id);
+ create index phenstatement_idx2 on phenstatement (observable_id);
+ create index phenstatement_idx3 on phenstatement (attr_id);
  -- ****************************************
  
***************
*** 1058,1066 ****
  -- 
  --
! CREATE TABLE phendesc (
!     phendesc_id                       SERIAL PRIMARY KEY NOT NULL,
!     gcontext_id          INTEGER      NOT NULL REFERENCES gcontext(gcontext_id),
!     description          TEXT         NOT NULL
  );
  -- ****************************************
  
--- 1117,1130 ----
  -- 
  --
! create table phendesc (
! 	phendesc_id	serial not null,
! 	primary key (phendesc_id),
! 	gcontext_id	int not null,
! 	foreign key (gcontext_id) references gcontext (gcontext_id) on delete cascade INITIALLY DEFERRED,
! 	description	text not null,
! 
! 	unique(gcontext_id, description)
  );
+ create index phendesc_idx1 on phendesc (gcontext_id);
  -- ****************************************
  
***************
*** 1072,1082 ****
  -- 
  --
! CREATE TABLE phenstatement_relationship (
!     phenstatement_relationship_id SERIAL PRIMARY KEY NOT NULL,
!     subject_id           INTEGER      NOT NULL REFERENCES phenstatement(phenstatement_id),
!     object_id            INTEGER      NOT NULL REFERENCES phenstatement(phenstatement_id),
!     type_id              INTEGER      NOT NULL REFERENCES cvterm(cvterm_id),
!     comment_id           INTEGER      NOT NULL REFERENCES cvterm(cvterm_id)
  );
  -- ****************************************
  
--- 1136,1156 ----
  -- 
  --
! create table phenstatement_relationship (
! 	phenstatement_relationship_id serial not null,
! 	primary key (phenstatement_relationship_id),
! 	subject_id	int not null,
! 	foreign key (subject_id) references phenstatement (phenstatement_id) on delete cascade INITIALLY DEFERRED,
! 	object_id	int not null,
! 	foreign key (object_id) references phenstatement (phenstatement_id) on delete cascade INITIALLY DEFERRED,
! 	type_id	int not null,
! 	foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
! 	comment_id	int not null,
! 	foreign key (comment_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
! 
! 	unique(subject_id, object_id, type_id)
  );
+ create index phenstatement_relationship_idx1 on phenstatement_relationship (subject_id);
+ create index phenstatement_relationship_idx2 on phenstatement_relationship (subject_id);
+ create index phenstatement_relationship_idx3 on phenstatement_relationship (type_id);
  -- ****************************************
  
***************
*** 1088,1100 ****
  -- 
  --
! CREATE TABLE phenstatement_cvterm (
!     phenstatement_cvterm_id           SERIAL PRIMARY KEY NOT NULL,
!     phenstatement_id     INTEGER      NOT NULL REFERENCES phenstatement(phenstatement_id),
!     cvterm_id            INTEGER      NOT NULL REFERENCES cvterm(cvterm_id)
  );
  -- ****************************************
  
  
! -- RELATION: phenstatement_prop
  --
  -- arbitrary key=value pairs
--- 1162,1181 ----
  -- 
  --
! create table phenstatement_cvterm (
! 	phenstatement_cvterm_id	serial not null,
! 	primary key (phenstatement_cvterm_id),
! 	phenstatement_id	int not null,
! 	foreign key (phenstatement_id) references phenstatement (phenstatement_id) on delete cascade INITIALLY DEFERRED,
! 	cvterm_id	int not null,
! 	foreign key (cvterm_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
! 
! 	unique(phenstatement_id, cvterm_id)
  );
+ create index phenstatement_cvterm_idx1 on phenstatement_cvterm (phenstatement_id);	
+ create index phenstatement_cvterm_idx2 on phenstatement_cvterm (cvterm_id);	
  -- ****************************************
  
  
! -- RELATION: phenstatementprop
  --
  -- arbitrary key=value pairs
***************
*** 1103,1112 ****
  -- value               : unconstrained free text value
  --
! CREATE TABLE phenstatement_prop (
!     phenstatement_prop_id             SERIAL PRIMARY KEY NOT NULL,
!     phenstatement_id     INTEGER      NOT NULL REFERENCES phenstatement(phenstatement_id),
!     type_id              INTEGER      NOT NULL REFERENCES cvterm(cvterm_id),
!     value                TEXT         NOT NULL
  );
  -- ****************************************
  -- ================================================
--- 1184,1200 ----
  -- value               : unconstrained free text value
  --
! create table phenstatementprop (
! 	phenstatementprop_id	serial not null,
! 	primary key (phenstatementprop_id),
! 	phenstatement_id	int not null,
! 	foreign key (phenstatement_id) references phenstatement (phenstatement_id) on delete cascade INITIALLY DEFERRED,
! 	type_id	int not null,
! 	foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
! 	value	text not null,
! 
! 	unique(phenstatement_id, type_id, value)
  );
+ create index phenstatementprop_idx1 on phenstatementprop (phenstatement_id);
+ create index phenstatementprop_idx2 on phenstatementprop (type_id);
  -- ****************************************
  -- ================================================
***************
*** 1422,1427 ****
      mageml_id serial not null,
  	primary key (mageml_id),
!     mage_package varchar(100) not null,
!     mage_ml varchar not null
  );
  
--- 1510,1515 ----
      mageml_id serial not null,
  	primary key (mageml_id),
!     mage_package text not null,
!     mage_ml text not null
  );
  
***************
*** 1436,1440 ****
  	foreign key (tableinfo_id) references tableinfo (tableinfo_id) on delete cascade INITIALLY DEFERRED,
      row_id int not null,
!     mageidentifier varchar(100) not null
  );
  create index magedocumentation_idx1 on magedocumentation (mageml_id);
--- 1524,1528 ----
  	foreign key (tableinfo_id) references tableinfo (tableinfo_id) on delete cascade INITIALLY DEFERRED,
      row_id int not null,
!     mageidentifier text not null
  );
  create index magedocumentation_idx1 on magedocumentation (mageml_id);
***************
*** 1453,1461 ****
      dbxref_id int null,
  	foreign key (dbxref_id) references dbxref (dbxref_id) on delete set null INITIALLY DEFERRED,
!     name varchar(100) not null,
!     uri varchar(100) null,
!     protocoldescription varchar(4000) null,
!     hardwaredescription varchar(500) null,
!     softwaredescription varchar(500) null,
      unique(name)
  );
--- 1541,1549 ----
      dbxref_id int null,
  	foreign key (dbxref_id) references dbxref (dbxref_id) on delete set null INITIALLY DEFERRED,
!     name text not null,
!     uri text null,
!     protocoldescription text null,
!     hardwaredescription text null,
!     softwaredescription text null,
      unique(name)
  );
***************
*** 1471,1480 ****
      protocol_id int not null,
  	foreign key (protocol_id) references protocol (protocol_id) on delete cascade INITIALLY DEFERRED,
!     name varchar(100) not null,
      datatype_id int null,
  	foreign key (datatype_id) references cvterm (cvterm_id) on delete set null INITIALLY DEFERRED,
      unittype_id int null,
  	foreign key (unittype_id) references cvterm (cvterm_id) on delete set null INITIALLY DEFERRED,
!     value varchar(100) null
  );
  create index protocolparam_idx1 on protocolparam (protocol_id);
--- 1559,1569 ----
      protocol_id int not null,
  	foreign key (protocol_id) references protocol (protocol_id) on delete cascade INITIALLY DEFERRED,
!     name text not null,
      datatype_id int null,
  	foreign key (datatype_id) references cvterm (cvterm_id) on delete set null INITIALLY DEFERRED,
      unittype_id int null,
  	foreign key (unittype_id) references cvterm (cvterm_id) on delete set null INITIALLY DEFERRED,
!     value text null,
!     rank int not null default 0
  );
  create index protocolparam_idx1 on protocolparam (protocol_id);
***************
*** 1487,1492 ****
      channel_id serial not null,
  	primary key (channel_id),
!     name varchar(100) not null,
!     definition varchar(500) not null,
      unique(name)
  );
--- 1576,1581 ----
      channel_id serial not null,
  	primary key (channel_id),
!     name text not null,
!     definition text not null,
      unique(name)
  );
***************
*** 1507,1515 ****
      dbxref_id int null,
  	foreign key (dbxref_id) references dbxref (dbxref_id) on delete set null INITIALLY DEFERRED,
!     name varchar(100) not null,
!     version varchar(50) null,
!     description varchar(500) null,
!     array_dimensions varchar(50) null,
!     element_dimensions varchar(50) null,
      num_of_elements int null,
      num_array_columns int null,
--- 1596,1604 ----
      dbxref_id int null,
  	foreign key (dbxref_id) references dbxref (dbxref_id) on delete set null INITIALLY DEFERRED,
!     name text not null,
!     version text null,
!     description text null,
!     array_dimensions text null,
!     element_dimensions text null,
      num_of_elements int null,
      num_array_columns int null,
***************
*** 1536,1540 ****
      type_id int not null,
  	foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
!     value varchar(100) not null
  );
  create index arrayprop_idx1 on arrayprop (arraydesign_id);
--- 1625,1631 ----
      type_id int not null,
  	foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
!     value text not null,
!     rank int not null default 0,
!     unique(arraydesign_id, type_id, rank)
  );
  create index arrayprop_idx1 on arrayprop (arraydesign_id);
***************
*** 1551,1562 ****
  	foreign key (protocol_id) references protocol (protocol_id) on delete set null INITIALLY DEFERRED,
      assaydate timestamp null default current_timestamp,
!     arrayidentifier varchar(100) null,
!     arraybatchidentifier varchar(100) null,
      operator_id int not null,
  	foreign key (operator_id) references contact (contact_id) on delete cascade INITIALLY DEFERRED,
      dbxref_id int null,
  	foreign key (dbxref_id) references dbxref (dbxref_id) on delete set null INITIALLY DEFERRED,
!     name varchar(100) null,
!     description varchar(500) null,
      unique(name)
  );
--- 1642,1653 ----
  	foreign key (protocol_id) references protocol (protocol_id) on delete set null INITIALLY DEFERRED,
      assaydate timestamp null default current_timestamp,
!     arrayidentifier text null,
!     arraybatchidentifier text null,
      operator_id int not null,
  	foreign key (operator_id) references contact (contact_id) on delete cascade INITIALLY DEFERRED,
      dbxref_id int null,
  	foreign key (dbxref_id) references dbxref (dbxref_id) on delete set null INITIALLY DEFERRED,
!     name text null,
!     description text null,
      unique(name)
  );
***************
*** 1575,1579 ****
      type_id int not null,
  	foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
!     value varchar(100) not null
  );
  create index assayprop_idx1 on assayprop (assay_id);
--- 1666,1672 ----
      type_id int not null,
  	foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
!     value text not null,
!     rank int not null default 0,
!     unique(assay_id, type_id, rank)
  );
  create index assayprop_idx1 on assayprop (assay_id);
***************
*** 1603,1610 ****
      biosourceprovider_id int null,
  	foreign key (biosourceprovider_id) references contact (contact_id) on delete set null INITIALLY DEFERRED,
!     dbxref_id varchar(50) null,
  	foreign key (dbxref_id) references dbxref (dbxref_id) on delete set null INITIALLY DEFERRED,
!     name varchar(100) null,
!     description varchar(500) null,
      unique(name)
  );
--- 1696,1703 ----
      biosourceprovider_id int null,
  	foreign key (biosourceprovider_id) references contact (contact_id) on delete set null INITIALLY DEFERRED,
!     dbxref_id int null,
  	foreign key (dbxref_id) references dbxref (dbxref_id) on delete set null INITIALLY DEFERRED,
!     name text null,
!     description text null,
      unique(name)
  );
***************
*** 1639,1644 ****
      type_id int not null,
  	foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
!     value varchar(100) null,
!     unique(biomaterial_id,type_id,value)
  );
  create index biomaterialprop_idx1 on biomaterialprop (biomaterial_id);
--- 1732,1738 ----
      type_id int not null,
  	foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
!     value text null,
!     rank int not null,
!     unique(biomaterial_id,type_id,rank)
  );
  create index biomaterialprop_idx1 on biomaterialprop (biomaterial_id);
***************
*** 1657,1661 ****
      protocol_id int null,
  	foreign key (protocol_id) references protocol (protocol_id) on delete set null INITIALLY DEFERRED,
!     name varchar(100) null
  );
  create index treatment_idx1 on treatment (biomaterial_id);
--- 1751,1755 ----
      protocol_id int null,
  	foreign key (protocol_id) references protocol (protocol_id) on delete set null INITIALLY DEFERRED,
!     name text null
  );
  create index treatment_idx1 on treatment (biomaterial_id);
***************
*** 1710,1715 ****
  	foreign key (channel_id) references channel (channel_id) on delete set null INITIALLY DEFERRED,
      acquisitiondate timestamp null default current_timestamp,
!     name varchar(100) null,
!     uri varchar(255) null,
      unique(name)
  );
--- 1804,1809 ----
  	foreign key (channel_id) references channel (channel_id) on delete set null INITIALLY DEFERRED,
      acquisitiondate timestamp null default current_timestamp,
!     name text null,
!     uri text null,
      unique(name)
  );
***************
*** 1727,1731 ****
      type_id int not null,
          foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
!     value varchar(50) not null
  );
  create index acquisitionprop_idx1 on acquisitionprop (acquisition_id);
--- 1821,1827 ----
      type_id int not null,
          foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
!     value text null,
!     rank int not null default 0,
!     unique(acquisition_id, type_id, rank)
  );
  create index acquisitionprop_idx1 on acquisitionprop (acquisition_id);
***************
*** 1743,1747 ****
      object_id int not null,
  	foreign key (object_id) references acquisition (acquisition_id) on delete cascade INITIALLY DEFERRED,
!     unique(subject_id,type_id,object_id)
  );
  create index acquisition_relationship_idx1 on acquisition_relationship (subject_id);
--- 1839,1845 ----
      object_id int not null,
  	foreign key (object_id) references acquisition (acquisition_id) on delete cascade INITIALLY DEFERRED,
!     value text null,
!     rank int not null default 0,
!     unique(subject_id, type_id, object_id, rank)
  );
  create index acquisition_relationship_idx1 on acquisition_relationship (subject_id);
***************
*** 1763,1768 ****
  	foreign key (analysis_id) references analysis (analysis_id) on delete cascade INITIALLY DEFERRED,
      quantificationdate timestamp null default current_timestamp,
!     name varchar(100) null,
!     uri varchar(500) null,
      unique(name,analysis_id)
  );
--- 1861,1866 ----
  	foreign key (analysis_id) references analysis (analysis_id) on delete cascade INITIALLY DEFERRED,
      quantificationdate timestamp null default current_timestamp,
!     name text null,
!     uri text null,
      unique(name,analysis_id)
  );
***************
*** 1781,1785 ****
      type_id int not null,
  	foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
!     value varchar(50) not null
  );
  create index quantificationprop_idx1 on quantificationprop (quantification_id);
--- 1879,1885 ----
      type_id int not null,
  	foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
!     value text null,
!     rank int not null default 0,
!     unique(quantification_id, type_id, rank)
  );
  create index quantificationprop_idx1 on quantificationprop (quantification_id);
***************
*** 1815,1820 ****
  	foreign key (tableinfo_id) references tableinfo (tableinfo_id) on delete cascade INITIALLY DEFERRED,
      row_id int not null,
!     name varchar(100) null,
!     value varchar(255) null
  );
  create index control_idx1 on control (type_id);
--- 1915,1921 ----
  	foreign key (tableinfo_id) references tableinfo (tableinfo_id) on delete cascade INITIALLY DEFERRED,
      row_id int not null,
!     name text null,
!     value text null,
!     rank int not null default 0
  );
  create index control_idx1 on control (type_id);
***************
*** 1911,1914 ****
--- 2012,2016 ----
  create index elementresult_idx2 on elementresult (quantification_id);
  create index elementresult_idx3 on elementresult (subclass_view);
+ create unique index elementresult_idx4 on elementresult (element_id,quantification_id,subclass_view);
  
  COMMENT ON TABLE elementresult IS 'an element on an array produces a measurement when hybridized to a biomaterial (traceable through quantification_id).  this is the "real" data from the microarray hybridization.  the fields of this table are intentionally generic so that many different platforms can be stored in a common table.  each platform should have a corresponding view onto this table, mapping specific parameters of the platform to generic columns';
***************
*** 1923,1928 ****
      object_id int not null,
          foreign key (object_id) references elementresult (elementresult_id) INITIALLY DEFERRED,
!     value varchar(255),
!     unique(subject_id,type_id,object_id,value)
  );
  create index elementresult_relationship_idx1 on elementresult_relationship (subject_id);
--- 2025,2031 ----
      object_id int not null,
          foreign key (object_id) references elementresult (elementresult_id) INITIALLY DEFERRED,
!     value text null,
!     rank int not null default 0,
!     unique(subject_id,type_id,object_id,rank)
  );
  create index elementresult_relationship_idx1 on elementresult_relationship (subject_id);
***************
*** 1942,1947 ****
      dbxref_id int null,
  	foreign key (dbxref_id) references dbxref (dbxref_id) on delete set null INITIALLY DEFERRED,
!     name varchar(100) not null,
!     description varchar(4000) null,
      unique(name)
  );
--- 2045,2050 ----
      dbxref_id int null,
  	foreign key (dbxref_id) references dbxref (dbxref_id) on delete set null INITIALLY DEFERRED,
!     name text not null,
!     description text null,
      unique(name)
  );
***************
*** 1971,1975 ****
      study_id int not null,
  	foreign key (study_id) references study (study_id) on delete cascade INITIALLY DEFERRED,
!     description varchar(4000) null
  );
  create index studydesign_idx1 on studydesign (study_id);
--- 2074,2078 ----
      study_id int not null,
  	foreign key (study_id) references study (study_id) on delete cascade INITIALLY DEFERRED,
!     description text null
  );
  create index studydesign_idx1 on studydesign (study_id);
***************
*** 1984,1988 ****
      type_id int not null,
  	foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
!     value varchar(500) not null
  );
  create index studydesignprop_idx1 on studydesignprop (studydesign_id);
--- 2087,2093 ----
      type_id int not null,
  	foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
!     value text null,
!     rank int not null default 0,
!     unique(studydesign_id, type_id, rank)
  );
  create index studydesignprop_idx1 on studydesignprop (studydesign_id);
***************
*** 1998,2003 ****
      type_id int null,
  	foreign key (type_id) references cvterm (cvterm_id) on delete set null INITIALLY DEFERRED,
!     name varchar(100) not null,
!     description varchar(500) null
  );
  create index studyfactor_idx1 on studyfactor (studydesign_id);
--- 2103,2108 ----
      type_id int null,
  	foreign key (type_id) references cvterm (cvterm_id) on delete set null INITIALLY DEFERRED,
!     name text not null,
!     description text null
  );
  create index studyfactor_idx1 on studyfactor (studydesign_id);
***************
*** 2013,2018 ****
      assay_id int not null,
  	foreign key (assay_id) references assay (assay_id) on delete cascade INITIALLY DEFERRED,
!     factorvalue varchar(100) not null,
!     name varchar(100) null
  );
  create index studyfactorvalue_idx1 on studyfactorvalue (studyfactor_id);
--- 2118,2124 ----
      assay_id int not null,
  	foreign key (assay_id) references assay (assay_id) on delete cascade INITIALLY DEFERRED,
!     factorvalue text null,
!     name text null,
!     rank int not null default 0
  );
  create index studyfactorvalue_idx1 on studyfactorvalue (studyfactor_id);
***************
*** 2020,2024 ****
--- 2126,2255 ----
  
  COMMENT ON TABLE studyfactorvalue IS NULL;
+ -- This module is totally dependant on the sequence module.  Objects in the
+ -- genetic module cannot connect to expression data except by going via the
+ -- sequence module
+ 
+ -- We assume that we'll *always* have a controlled vocabulary for expression 
+ -- data.   If an experiment used a set of cv terms different from the ones
+ -- FlyBase uses (bodypart cv, bodypart qualifier cv, and the temporal cv
+ -- (which is stored in the curaton.doc under GAT6 btw)), they'd have to give
+ -- us the cv terms, which we could load into the cv module
+ 
+ -- ================================================
+ -- TABLE: expression
+ -- ================================================
  
+ create table expression (
+        expression_id serial not null,
+        primary key (expression_id),
+        description text
+ );
+ 
+ -- ================================================
+ -- TABLE: feature_expression
+ -- ================================================
+ 
+ create table feature_expression (
+        feature_expression_id serial not null,
+        primary key (feature_expression_id),
+        expression_id int not null,
+        foreign key (expression_id) references expression (expression_id) on delete cascade INITIALLY DEFERRED,
+        feature_id int not null,
+        foreign key (feature_id) references feature (feature_id) on delete cascade INITIALLY DEFERRED,
+ 
+        unique(expression_id,feature_id)       
+ );
+ create index feature_expression_idx1 on feature_expression (expression_id);
+ create index feature_expression_idx2 on feature_expression (feature_id);
+ 
+ 
+ -- ================================================
+ -- TABLE: expression_cvterm
+ -- ================================================
+ 
+ -- What are the possibities of combination when more than one cvterm is used
+ -- in a field?   
+ --
+ -- For eg (in <p> here):   <t> E | early <a> <p> anterior & dorsal
+ -- If the two terms used in a particular field are co-equal (both from the
+ -- same CV, is the relation always "&"?   May we find "or"?
+ --
+ -- Obviously another case is when a bodypart term and a bodypart qualifier
+ -- term are used in a specific field, eg:
+ --   <t> L | third instar <a> larval antennal segment sensilla | subset <p  
+ --
+ -- WRT the three-part <t><a><p> statements, are the values in the different 
+ -- parts *always* from different vocabularies in proforma.CV?   If not,
+ -- we'll need to have some kind of type qualifier telling us whether the
+ -- cvterm used is <t>, <a>, or <p>
+ -- yes we should have a type qualifier as a cv term can be from diff vocab
+ -- e.g. blastoderm can be body part and stage terms in dros anatomy
+ -- but cvterm_type needs to be a cv instead of a free text type here?
+ 
+ create table expression_cvterm (
+        expression_cvterm_id serial not null,
+        primary key (expression_cvterm_id),
+        expression_id int not null,
+        foreign key (expression_id) references expression (expression_id) on delete cascade INITIALLY DEFERRED,
+        cvterm_id int not null,
+        foreign key (cvterm_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
+        rank int not null,
+ 	   cvterm_type varchar(255),
+ 
+        unique(expression_id,cvterm_id)
+ );
+ create index expression_cvterm_idx1 on expression_cvterm (expression_id);
+ create index expression_cvterm_idx2 on expression_cvterm (cvterm_id);
+ 
+ 
+ -- ================================================
+ -- TABLE: expression_pub
+ -- ================================================
+ 
+ create table expression_pub (
+        expression_pub_id serial not null,
+        primary key (expression_pub_id),
+        expression_id int not null,
+        foreign key (expression_id) references expression (expression_id) on delete cascade INITIALLY DEFERRED,
+        pub_id int not null,
+        foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
+ 
+        unique(expression_id,pub_id)       
+ );
+ create index expression_pub_idx1 on expression_pub (expression_id);
+ create index expression_pub_idx2 on expression_pub (pub_id);
+ 
+ 
+ -- ================================================
+ -- TABLE: eimage
+ -- ================================================
+ 
+ create table eimage (
+        eimage_id serial not null,
+        primary key (eimage_id),
+        eimage_data text,
+        eimage_type varchar(255) not null,
+        image_uri varchar(255)
+ );
+ -- we expect images in eimage_data (eg jpegs) to be uuencoded
+ -- describes the type of data in eimage_data
+ 
+ 
+ -- ================================================
+ -- TABLE: expression_image
+ -- ================================================
+ 
+ create table expression_image (
+        expression_image_id serial not null,
+        primary key (expression_image_id),
+        expression_id int not null,
+        foreign key (expression_id) references expression (expression_id) on delete cascade INITIALLY DEFERRED,
+        eimage_id int not null,
+        foreign key (eimage_id) references eimage (eimage_id) on delete cascade INITIALLY DEFERRED,
+ 
+        unique(expression_id,eimage_id)
+ );
+ create index expression_image_idx1 on expression_image (expression_id);
+ create index expression_image_idx2 on expression_image (eimage_id);
  -- FUNCTION gfffeatureatts (integer) is a function to get 
  -- data in the same format as the gffatts view so that