Menu
▾
▴
[Gmod-schema-cmts] SF.net SVN: gmod:[25279] schema/trunk/chado/modules
|
From: <em...@us...> - 2013-01-15 15:11:03
|
Revision: 25279
http://gmod.svn.sourceforge.net/gmod/?rev=25279&view=rev
Author: emmert
Date: 2013-01-15 15:10:50 +0000 (Tue, 15 Jan 2013)
Log Message:
-----------
Extensions to interaction, expression, and organism modules; extension to library module to link to these. All changes are back-compatible.
Modified Paths:
--------------
schema/trunk/chado/modules/expression/expression.sql
schema/trunk/chado/modules/interaction/interaction.sql
schema/trunk/chado/modules/library/library.sql
schema/trunk/chado/modules/organism/organism.sql
Modified: schema/trunk/chado/modules/expression/expression.sql
===================================================================
--- schema/trunk/chado/modules/expression/expression.sql 2012-12-13 19:59:18 UTC (rev 25278)
+++ schema/trunk/chado/modules/expression/expression.sql 2013-01-15 15:10:50 UTC (rev 25279)
@@ -21,11 +21,12 @@
uniquename text not null,
md5checksum character(32),
description text,
- constraint expression_c1 unique(uniquename)
+ constraint expression_c1 unique (uniquename)
);
COMMENT ON TABLE expression IS 'The expression table is essentially a bridge table.';
+
-- ================================================
-- TABLE: expression_cvterm
-- ================================================
@@ -40,12 +41,13 @@
rank int not null default 0,
cvterm_type_id int not null,
foreign key (cvterm_type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
- constraint expression_cvterm_c1 unique(expression_id,cvterm_id,cvterm_type_id)
+ constraint expression_cvterm_c1 unique (expression_id,cvterm_id,rank,cvterm_type_id)
);
create index expression_cvterm_idx1 on expression_cvterm (expression_id);
create index expression_cvterm_idx2 on expression_cvterm (cvterm_id);
create index expression_cvterm_idx3 on expression_cvterm (cvterm_type_id);
+
--================================================
-- TABLE: expression_cvtermprop
-- ================================================
@@ -112,7 +114,7 @@
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,
- constraint expression_pub_c1 unique(expression_id,pub_id)
+ constraint expression_pub_c1 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);
@@ -131,7 +133,7 @@
foreign key (feature_id) references feature (feature_id) on delete cascade INITIALLY DEFERRED,
pub_id int not null,
foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
- constraint feature_expression_c1 unique(expression_id,feature_id,pub_id)
+ constraint feature_expression_c1 unique (expression_id,feature_id,pub_id)
);
create index feature_expression_idx1 on feature_expression (expression_id);
create index feature_expression_idx2 on feature_expression (feature_id);
Modified: schema/trunk/chado/modules/interaction/interaction.sql
===================================================================
--- schema/trunk/chado/modules/interaction/interaction.sql 2012-12-13 19:59:18 UTC (rev 25278)
+++ schema/trunk/chado/modules/interaction/interaction.sql 2013-01-15 15:10:50 UTC (rev 25279)
@@ -24,6 +24,7 @@
-- ================================================
-- TABLE: interaction
-- ================================================
+
drop table interaction cascade;
create table interaction (
interaction_id serial NOT NULL,
@@ -34,13 +35,11 @@
on delete cascade INITIALLY DEFERRED,
description text,
is_obsolete boolean not null default false,
- constraint interaction_c1 unique(uniquename,type_id)
+ constraint interaction_c1 unique (uniquename,type_id)
);
create index interaction_idx1 on interaction (uniquename);
create index interaction_idx2 on interaction (type_id);
-grant all on interaction to public;
-grant all on interaction_interaction_id_seq to public;
-- ================================================
-- TABLE: interactionprop
@@ -61,12 +60,12 @@
create index interactionprop_idx1 on interactionprop (interaction_id);
create index interactionprop_idx2 on interactionprop (type_id);
-grant all on interactionprop to public;
-grant all on interactionprop_interactionprop_id_seq to public;
-- ================================================
-- TABLE: interactionprop_pub
-- ================================================
+
+drop table interactionprop_pub cascade;
create table interactionprop_pub (
interactionprop_pub_id serial not null,
primary key (interactionprop_pub_id),
@@ -80,9 +79,6 @@
create index interactionprop_pub_idx2 on interactionprop_pub (pub_id);
-grant all on interactionprop_pub to public;
-grant all on interactionprop_pub_interactionprop_pub_id_seq to public;
-
-- ================================================
-- TABLE: interaction_pub
-- ================================================
@@ -100,15 +96,12 @@
create index interaction_pub_idx1 on interaction_pub (interaction_id);
create index interaction_pub_idx2 on interaction_pub (pub_id);
-grant all on interaction_pub to public;
-grant all on interaction_pub_interaction_pub_id_seq to public;
-- ================================================
-- TABLE: interaction_expression
-- ================================================
drop table interaction_expression cascade;
-
create table interaction_expression (
interaction_expression_id serial not null,
primary key (interaction_expression_id),
@@ -118,14 +111,12 @@
foreign key (interaction_id) references interaction (interaction_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,interaction_id,pub_id)
+ constraint interaction_expression_c1 unique (expression_id,interaction_id,pub_id)
);
create index interaction_expression_idx1 on interaction_expression (expression_id);
create index interaction_expression_idx2 on interaction_expression (interaction_id);
create index interaction_expression_idx3 on interaction_expression (pub_id);
-grant all on interaction_expression to public;
-grant all on interaction_expression_interaction_expression_id_seq to public;
--================================================
-- TABLE: interaction_expressionprop
@@ -141,22 +132,17 @@
foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
value text null,
rank int not null default 0,
- unique (interaction_expression_id,type_id,rank)
+ constraint interaction_expressionprop_c1 unique (interaction_expression_id,type_id,rank)
);
-
-
create index interaction_expressionprop_idx1 on interaction_expressionprop (interaction_expression_id);
create index interaction_expressionprop_idx2 on interaction_expressionprop (type_id);
-grant all on interaction_expressionprop to public;
-grant all on interaction_expressionprop_interaction_expressionprop_id_seq to public;
-- ================================================
-- TABLE: interaction_cvterm
-- ================================================
drop table interaction_cvterm cascade;
-
create table interaction_cvterm (
interaction_cvterm_id serial not null,
primary key (interaction_cvterm_id),
@@ -164,20 +150,17 @@
foreign key (interaction_id) references interaction (interaction_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(interaction_id,cvterm_id)
+ constraint interaction_cvterm_c1 unique (interaction_id,cvterm_id)
);
create index interaction_cvterm_idx1 on interaction_cvterm (interaction_id);
create index interaction_cvterm_idx2 on interaction_cvterm (cvterm_id);
-create index interaction_cvterm_idx3 on interaction_cvterm (pub_id);
-grant all on interaction_cvterm to public;
-grant all on interaction_cvterm_interaction_cvterm_id_seq to public;
--================================================
-- TABLE: interaction_cvtermprop
-- ================================================
-drop table interaction_cvtermprop;
+drop table interaction_cvtermprop cascade;
create table interaction_cvtermprop (
interaction_cvtermprop_id serial not null,
primary key (interaction_cvtermprop_id),
@@ -187,42 +170,13 @@
foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
value text null,
rank int not null default 0,
- unique (interaction_cvterm_id,type_id,rank)
+ constraint interaction_cvtermprop_c1 unique (interaction_cvterm_id,type_id,rank)
);
-
-
create index interaction_cvtermprop_idx1 on interaction_cvtermprop (interaction_cvterm_id);
create index interaction_cvtermprop_idx2 on interaction_cvtermprop (type_id);
-grant all on interaction_cvtermprop to public;
-grant all on interaction_cvtermprop_interaction_cvtermprop_id_seq to public;
-
-- ================================================
--- TABLE: library_interaction
--- ================================================
-
-drop table library_interaction cascade;
-
-create table library_interaction (
- library_interaction_id serial not null,
- primary key (library_interaction_id),
- interaction_id int not null,
- foreign key (interaction_id) references interaction (interaction_id) on delete cascade INITIALLY DEFERRED,
- library_id int not null,
- foreign key (library_id) references library (library_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(interaction_id,library_id,pub_id)
-);
-create index library_interaction_idx1 on library_interaction (interaction_id);
-create index library_interaction_idx2 on library_interaction (library_id);
-create index library_interaction_idx3 on library_interaction (pub_id);
-
-grant all on library_interaction to public;
-grant all on library_interaction_library_interaction_id_seq to public;
-
--- ================================================
-- TABLE: feature_interaction
-- ================================================
@@ -237,18 +191,16 @@
role_id int not null,
foreign key (role_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
rank int not null default 0,
- unique(feature_id,interaction_id, role_id)
+ constraint feature_interaction_c1 unique (feature_id,interaction_id, role_id)
);
create index feature_interaction_idx1 on feature_interaction (feature_id);
create index feature_interaction_idx2 on feature_interaction (interaction_id);
create index feature_interaction_idx3 on feature_interaction (role_id);
-grant all on feature_interaction to public;
-grant all on feature_interaction_feature_interaction_id_seq to public;
-
-- ? do we want to add rank to the unique key ? thinking stochiometry issues
-- and might we have one form modified and not another ? may be too much
+
-- ================================================
-- TABLE: feature_interactionprop
-- ================================================
@@ -268,8 +220,6 @@
create index feature_interactionprop_idx1 on feature_interactionprop (feature_interaction_id);
create index feature_interactionprop_idx2 on feature_interactionprop (type_id);
-grant all on feature_interactionprop to public;
-grant all on feature_interactionprop_feature_interactionprop_id_seq to public;
-- ================================================
-- TABLE: feature_interaction_pub
@@ -288,15 +238,12 @@
create index feature_interaction_pub_idx1 on feature_interaction_pub (feature_interaction_id);
create index feature_interaction_pub_idx2 on feature_interaction_pub (pub_id);
-grant all on feature_interaction_pub to public;
-grant all on feature_interaction_pub_feature_interaction_pub_id_seq to public;
-- ================================================
-- TABLE: interaction_cell_line
-- ================================================
drop table interaction_cell_line cascade;
-
create table interaction_cell_line (
interaction_cell_line_id serial not null,
primary key (interaction_cell_line_id),
@@ -306,11 +253,43 @@
foreign key (interaction_id) references interaction (interaction_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(cell_line_id,interaction_id,pub_id)
+ constraint interaction_cell_line_c1 unique (cell_line_id,interaction_id,pub_id)
);
create index interaction_cell_line_idx1 on interaction_cell_line (cell_line_id);
create index interaction_cell_line_idx2 on interaction_cell_line (interaction_id);
create index interaction_cell_line_idx3 on interaction_cell_line (pub_id);
-grant all on interaction_cell_line to public;
-grant all on interaction_cell_line_interaction_cell_line_id_seq to public;
+
+-- ================================================
+-- TABLE: interaction_group
+-- ================================================
+
+drop table interaction_group cascade;
+create table interaction_group (
+ interaction_group_id serial not null,
+ primary key (interaction_group_id),
+ uniquename text NOT NULL,
+ is_obsolete boolean not null default false,
+ description text,
+ constraint interaction_group_c1 unique (uniquename)
+);
+create index interaction_group_idx1 on interaction_group (uniquename);
+
+
+-- ================================================
+-- TABLE: interaction_group_feature_interaction
+-- ================================================
+
+drop table interaction_group_feature_interaction cascade;
+create table interaction_group_feature_interaction (
+ interaction_group_feature_interaction_id serial not null,
+ primary key (interaction_group_feature_interaction_id),
+ interaction_group_id int not null,
+ foreign key (interaction_group_id) references interaction_group (interaction_group_id) on delete cascade INITIALLY DEFERRED,
+ feature_interaction_id int not null,
+ foreign key (feature_interaction_id) references feature_interaction (feature_interaction_id) on delete cascade INITIALLY DEFERRED,
+ rank int not null default 0,
+ constraint interaction_group_feature_interaction_c1 unique (interaction_group_id,feature_interaction_id,rank)
+);
+create index interaction_group_feature_interaction_idx1 on interaction_group_feature_interaction (interaction_group_id);
+create index interaction_group_feature_interaction_idx2 on interaction_group_feature_interaction (feature_interaction_id);
Modified: schema/trunk/chado/modules/library/library.sql
===================================================================
--- schema/trunk/chado/modules/library/library.sql 2012-12-13 19:59:18 UTC (rev 25278)
+++ schema/trunk/chado/modules/library/library.sql 2013-01-15 15:10:50 UTC (rev 25279)
@@ -7,6 +7,9 @@
-- :import cvterm from cv
-- :import pub from pub
-- :import organism from organism
+-- :import expression from expression
+-- :import interaction from interaction
+-- :import strain from strain
-- =================================================================
-- ================================================
@@ -178,6 +181,133 @@
create index library_dbxref_idx2 on library_dbxref (dbxref_id);
+-- ================================================
+-- TABLE: library_expression
+-- ================================================
+create table library_expression (
+ library_expression_id serial not null,
+ primary key (library_expression_id),
+ library_id int not null,
+ foreign key (library_id) references library (library_id) on delete cascade INITIALLY DEFERRED,
+ 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),
+ constraint library_expression_c1 unique (library_id,expression_id)
+);
+create index library_expression_idx1 on library_expression (library_id);
+create index library_expression_idx2 on library_expression (expression_id);
+create index library_expression_idx3 on library_expression (pub_id);
+-- ================================================
+-- TABLE: library_expressionprop
+-- ================================================
+
+create table library_expressionprop (
+ library_expressionprop_id serial not null,
+ primary key (library_expressionprop_id),
+ library_expression_id int not null,
+ foreign key (library_expression_id) references library_expression (library_expression_id) on delete cascade INITIALLY DEFERRED,
+ type_id int not null,
+ foreign key (type_id) references cvterm (cvterm_id),
+ value text null,
+ rank int not null default 0,
+ constraint library_expressionprop_c1 unique (library_expression_id,type_id,rank)
+);
+create index library_expressionprop_idx1 on library_expressionprop (library_expression_id);
+create index library_expressionprop_idx2 on library_expressionprop (type_id);
+
+
+-- ================================================
+-- TABLE: library_featureprop
+-- ================================================
+
+create table library_featureprop (
+ library_featureprop_id serial not null,
+ primary key (library_featureprop_id),
+ library_feature_id int not null,
+ foreign key (library_feature_id) references library_feature (library_feature_id) on delete cascade INITIALLY DEFERRED,
+ type_id int not null,
+ foreign key (type_id) references cvterm (cvterm_id),
+ value text null,
+ rank int not null default 0,
+ constraint library_featureprop_c1 unique (library_feature_id,type_id,rank)
+);
+create index library_featureprop_idx1 on library_featureprop (library_feature_id);
+create index library_featureprop_idx2 on library_featureprop (type_id);
+
+
+-- ================================================
+-- TABLE: library_interaction
+-- ================================================
+
+create table library_interaction (
+ library_interaction_id serial not null,
+ primary key (library_interaction_id),
+ library_id int not null,
+ foreign key (library_id) references library (library_id) on delete cascade INITIALLY DEFERRED,
+ interaction_id int not null,
+ foreign key (interaction_id) references interaction (interaction_id) on delete cascade INITIALLY DEFERRED,
+ pub_id int not null,
+ foreign key (pub_id) references pub (pub_id),
+ constraint library_interaction_c1 unique (interaction_id,library_id,pub_id)
+);
+create index library_interaction_idx1 on library_interaction (interaction_id);
+create index library_interaction_idx2 on library_interaction (library_id);
+create index library_interaction_idx3 on library_interaction (pub_id);
+
+
+-- ================================================
+-- TABLE: library_relationship
+-- ================================================
+
+create table library_relationship (
+ library_relationship_id serial not null,
+ primary key (library_relationship_id),
+ subject_id int not null,
+ foreign key (subject_id) references library (library_id) on delete cascade INITIALLY DEFERRED,
+ object_id int not null,
+ foreign key (object_id) references library (library_id) on delete cascade INITIALLY DEFERRED,
+ type_id int not null,
+ foreign key (type_id) references cvterm (cvterm_id),
+ constraint library_relationship_c1 unique (subject_id,object_id,type_id)
+);
+create index library_relationship_idx1 on library_relationship (subject_id);
+create index library_relationship_idx2 on library_relationship (object_id);
+create index library_relationship_idx3 on library_relationship (type_id);
+
+
+-- ================================================
+-- TABLE: library_relationship_pub
+-- ================================================
+
+create table library_relationship_pub (
+ library_relationship_pub_id serial not null,
+ primary key (library_relationship_pub_id),
+ library_relationship_id int not null,
+ foreign key (library_relationship_id) references library_relationship (library_relationship_id) on delete cascade INITIALLY DEFERRED,
+ pub_id int not null,
+ foreign key (pub_id) references pub (pub_id),
+ constraint library_relationship_pub_c1 unique (library_relationship_id,pub_id)
+);
+create index library_relationship_pub_idx1 on library_relationship_pub (library_relationship_id);
+create index library_relationship_pub_idx2 on library_relationship_pub (pub_id);
+
+
+-- ================================================
+-- TABLE: library_strain
+-- ================================================
+
+create table library_strain (
+ library_strain_id serial not null,
+ primary key (library_strain_id),
+ library_id int not null,
+ foreign key (library_id) references library (library_id) on delete cascade INITIALLY DEFERRED,
+ strain_id int not null,
+ foreign key (strain_id) references strain (strain_id) on delete cascade INITIALLY DEFERRED,
+ constraint library_strain_c1 unique (library_id,strain_id)
+);
+create index library_strain_idx1 on library_strain (library_id);
+create index library_strain_idx2 on library_strain (strain_id);
Modified: schema/trunk/chado/modules/organism/organism.sql
===================================================================
--- schema/trunk/chado/modules/organism/organism.sql 2012-12-13 19:59:18 UTC (rev 25278)
+++ schema/trunk/chado/modules/organism/organism.sql 2013-01-15 15:10:50 UTC (rev 25279)
@@ -1,4 +1,4 @@
--- $Id: organism.sql,v 1.19 2007-04-01 18:45:41 briano Exp $
+-- $Id: organism.sql,v 1.19 2007/04/01 18:45:41 briano Exp $
-- ==========================================
-- Chado organism module
--
@@ -13,6 +13,7 @@
-- TABLE: organism
-- ================================================
+drop table organism cascade;
create table organism (
organism_id serial not null,
primary key (organism_id),
@@ -37,10 +38,12 @@
this is appended onto the species name. Follows standard NCBI taxonomy
pattern.';
+
-- ================================================
-- TABLE: organism_dbxref
-- ================================================
+drop table organism_dbxref cascade;
create table organism_dbxref (
organism_dbxref_id serial not null,
primary key (organism_dbxref_id),
@@ -53,10 +56,12 @@
create index organism_dbxref_idx1 on organism_dbxref (organism_id);
create index organism_dbxref_idx2 on organism_dbxref (dbxref_id);
+
-- ================================================
-- TABLE: organismprop
-- ================================================
+drop table organismprop cascade;
create table organismprop (
organismprop_id serial not null,
primary key (organismprop_id),
@@ -73,3 +78,370 @@
COMMENT ON TABLE organismprop IS 'Tag-value properties - follows standard chado model.';
+
+-- ================================================
+-- TABLE: organismprop_pub
+-- ================================================
+
+drop table organismprop_pub cascade;
+create table organismprop_pub (
+ organismprop_pub_id serial not null,
+ primary key (organismprop_pub_id),
+ organismprop_id int not null,
+ foreign key (organismprop_id) references organismprop (organismprop_id) on delete cascade INITIALLY DEFERRED,
+ pub_id int not null,
+ foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
+ value text null,
+ rank int not null default 0,
+ constraint organismprop_pub_c1 unique (organismprop_id,pub_id)
+);
+create index organismprop_pub_idx1 on organismprop_pub (organismprop_id);
+create index organismprop_pub_idx2 on organismprop_pub (pub_id);
+
+COMMENT ON TABLE organismprop_pub IS 'Attribution for organismprop.';
+
+
+-- ================================================
+-- TABLE: organism_pub
+-- ================================================
+
+drop table organism_pub cascade;
+create table organism_pub (
+ organism_pub_id serial not null,
+ primary key (organism_pub_id),
+ organism_id int not null,
+ foreign key (organism_id) references organism (organism_id) on delete cascade INITIALLY DEFERRED,
+ pub_id int not null,
+ foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
+ constraint organism_pub_c1 unique (organism_id,pub_id)
+);
+create index organism_pub_idx1 on organism_pub (organism_id);
+create index organism_pub_idx2 on organism_pub (pub_id);
+
+
+-- ================================================
+-- TABLE: organism_cvterm
+-- ================================================
+
+drop table organism_cvterm cascade;
+create table organism_cvterm (
+ organism_cvterm_id serial not null,
+ primary key (organism_cvterm_id),
+ organism_id int not null,
+ foreign key (organism_id) references organism (organism_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 default 0,
+ pub_id int not null,
+ foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
+ constraint organism_cvterm_c1 unique(organism_id,cvterm_id,pub_id)
+);
+create index organism_cvterm_idx1 on organism_cvterm (organism_id);
+create index organism_cvterm_idx2 on organism_cvterm (cvterm_id);
+
+COMMENT ON TABLE organism_cvterm IS 'organism to cvterm associations. Examples: taxonomic name';
+
+COMMENT ON COLUMN organism_cvterm.rank IS 'Property-Value
+ordering. Any organism_cvterm can have multiple values for any particular
+property type - these are ordered in a list using rank, counting from
+zero. For properties that are single-valued rather than multi-valued,
+the default 0 value should be used';
+
+
+--================================================
+-- TABLE: organism_cvtermprop
+-- ================================================
+
+drop table organism_cvtermprop cascade;
+create table organism_cvtermprop (
+ organism_cvtermprop_id serial not null,
+ primary key (organism_cvtermprop_id),
+ organism_cvterm_id int not null,
+ foreign key (organism_cvterm_id) references organism_cvterm (organism_cvterm_id) on delete cascade,
+ 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,
+ constraint organism_cvtermprop_c1 unique (organism_cvterm_id,type_id,rank)
+);
+create index organism_cvtermprop_idx1 on organism_cvtermprop (organism_cvterm_id);
+create index organism_cvtermprop_idx2 on organism_cvtermprop (type_id);
+
+COMMENT ON TABLE organism_cvtermprop IS 'Extensible properties for
+organism to cvterm associations. Examples: qualifiers';
+
+COMMENT ON COLUMN organism_cvtermprop.type_id IS 'The name of the
+property/slot is a cvterm. The meaning of the property is defined in
+that cvterm. ';
+
+COMMENT ON COLUMN organism_cvtermprop.value IS 'The value of the
+property, represented as text. Numeric values are converted to their
+text representation. This is less efficient than using native database
+types, but is easier to query.';
+
+COMMENT ON COLUMN organism_cvtermprop.rank IS 'Property-Value
+ordering. Any organism_cvterm can have multiple values for any particular
+property type - these are ordered in a list using rank, counting from
+zero. For properties that are single-valued rather than multi-valued,
+the default 0 value should be used';
+
+
+-- ================================================
+-- TABLE: strain
+-- ================================================
+
+drop table strain cascade;
+create table strain (
+ strain_id serial not null,
+ primary key (strain_id),
+ name varchar(255) null,
+ uniquename text not null,
+ organism_id int not null,
+ foreign key (organism_id) references organism (organism_id) on delete cascade INITIALLY DEFERRED,
+ dbxref_id int null,
+ foreign key (dbxref_id) references dbxref (dbxref_id) on delete cascade INITIALLY DEFERRED,
+ is_obsolete boolean not null default 'false',
+ constraint strain_c1 unique (organism_id, uniquename)
+);
+
+create index strain_idx1 on strain (uniquename);
+create index strain_idx2 on strain (name);
+
+COMMENT ON TABLE strain IS 'A characterized strain of a given organism.';
+
+
+-- ================================================
+-- TABLE: strain_cvterm
+-- ================================================
+
+drop table strain_cvterm cascade;
+create table strain_cvterm (
+ strain_cvterm_id serial not null,
+ primary key (strain_cvterm_id),
+ strain_id int not null,
+ foreign key (strain_id) references strain (strain_id) on delete cascade INITIALLY DEFERRED,
+ cvterm_id int not null,
+ foreign key (cvterm_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED,
+ pub_id int not null,
+ foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
+ constraint strain_cvterm_c1 unique(strain_id,cvterm_id,pub_id)
+);
+create index strain_cvterm_idx1 on strain_cvterm (strain_id);
+create index strain_cvterm_idx2 on strain_cvterm (cvterm_id);
+
+COMMENT ON TABLE strain_cvterm IS 'strain to cvterm associations. Examples: GOid';
+
+--================================================
+-- TABLE: strain_cvtermprop
+-- ================================================
+
+drop table strain_cvtermprop cascade;
+create table strain_cvtermprop (
+ strain_cvtermprop_id serial not null,
+ primary key (strain_cvtermprop_id),
+ strain_cvterm_id int not null,
+ foreign key (strain_cvterm_id) references strain_cvterm (strain_cvterm_id) on delete cascade,
+ 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,
+ constraint strain_cvtermprop_c1 unique (strain_cvterm_id,type_id,rank)
+);
+create index strain_cvtermprop_idx1 on strain_cvtermprop (strain_cvterm_id);
+create index strain_cvtermprop_idx2 on strain_cvtermprop (type_id);
+
+COMMENT ON TABLE strain_cvtermprop IS 'Extensible properties for
+strain to cvterm associations. Examples: qualifiers';
+
+COMMENT ON COLUMN strain_cvtermprop.type_id IS 'The name of the
+property/slot is a cvterm. The meaning of the property is defined in
+that cvterm. ';
+
+COMMENT ON COLUMN strain_cvtermprop.value IS 'The value of the
+property, represented as text. Numeric values are converted to their
+text representation. This is less efficient than using native database
+types, but is easier to query.';
+
+COMMENT ON COLUMN strain_cvtermprop.rank IS 'Property-Value
+ordering. Any strain_cvterm can have multiple values for any particular
+property type - these are ordered in a list using rank, counting from
+zero. For properties that are single-valued rather than multi-valued,
+the default 0 value should be used';
+
+
+-- ================================================
+-- TABLE: strain_relationship
+-- ================================================
+
+drop table strain_relationship cascade;
+create table strain_relationship (
+ strain_relationship_id serial not null,
+ primary key (strain_relationship_id),
+ subject_id int not null,
+ foreign key (subject_id) references strain (strain_id) on delete cascade INITIALLY DEFERRED,
+ object_id int not null,
+ foreign key (object_id) references strain (strain_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 null,
+ rank int not null default 0,
+ constraint strain_relationship_c1 unique (subject_id, object_id, type_id, rank)
+);
+create index strain_relationship_idx1 on strain_relationship (subject_id);
+create index strain_relationship_idx2 on strain_relationship (object_id);
+
+COMMENT ON TABLE strain_relationship IS 'Relationships between strains, eg, progenitor.';
+
+
+-- ================================================
+-- TABLE: strainprop
+-- ================================================
+
+drop table strainprop cascade;
+create table strainprop (
+ strainprop_id serial not null,
+ primary key (strainprop_id),
+ strain_id int not null,
+ foreign key (strain_id) references strain (strain_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 null,
+ rank int not null default 0,
+ constraint strainprop_c1 unique (strain_id, type_id, rank)
+);
+create index strainprop_idx1 on strainprop (strain_id);
+create index strainprop_idx2 on strainprop (type_id);
+
+COMMENT ON TABLE strainprop IS 'Attributes of a given strain';
+
+
+-- ================================================
+-- TABLE: strainprop_pub
+-- ================================================
+
+drop table strainprop_pub cascade;
+create table strainprop_pub (
+ strainprop_pub_id serial not null,
+ primary key (strainprop_pub_id),
+ strainprop_id int not null,
+ foreign key (strainprop_id) references strainprop (strainprop_id) on delete cascade INITIALLY DEFERRED,
+ pub_id int not null,
+ foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
+ constraint strainprop_pub_c1 unique (strainprop_id,pub_id)
+);
+create index strainprop_pub_idx1 on strainprop_pub (strainprop_id);
+create index strainprop_pub_idx2 on strainprop_pub (pub_id);
+
+COMMENT ON TABLE strainprop_pub IS 'Provenance. Any strainprop assignment can optionally be supported by a publication.';
+
+
+-- ================================================
+-- TABLE: strain_dbxref
+-- ================================================
+
+drop table strain_dbxref cascade;
+create table strain_dbxref (
+ strain_dbxref_id serial not null,
+ primary key (strain_dbxref_id),
+ strain_id int not null,
+ foreign key (strain_id) references strain (strain_id) on delete cascade INITIALLY DEFERRED,
+ dbxref_id int not null,
+ foreign key (dbxref_id) references dbxref (dbxref_id) on delete cascade INITIALLY DEFERRED,
+ is_current boolean not null default 'true',
+ constraint strain_dbxref_c1 unique (strain_id,dbxref_id)
+);
+create index strain_dbxref_idx1 on strain_dbxref (strain_id);
+create index strain_dbxref_idx2 on strain_dbxref (dbxref_id);
+
+
+-- ================================================
+-- TABLE: strain_synonym
+-- ================================================
+
+drop table strain_synonym cascade;
+create table strain_synonym (
+ strain_synonym_id serial not null,
+ primary key (strain_synonym_id),
+ strain_id int not null,
+ foreign key (strain_id) references strain (strain_id) on delete cascade INITIALLY DEFERRED,
+ synonym_id int not null,
+ foreign key (synonym_id) references synonym (synonym_id) on delete cascade INITIALLY DEFERRED,
+ pub_id int not null,
+ foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
+ is_current boolean not null default 'false',
+ is_internal boolean not null default 'false',
+ constraint strain_synonym_c1 unique (synonym_id,strain_id,pub_id)
+);
+create index strain_synonym_idx1 on strain_synonym (synonym_id);
+create index strain_synonym_idx2 on strain_synonym (strain_id);
+create index strain_synonym_idx3 on strain_synonym (pub_id);
+
+COMMENT ON TABLE strain_synonym IS 'Linking table between strain and synonym.';
+
+
+-- ================================================
+-- TABLE: strain_feature
+-- ================================================
+
+drop table strain_feature cascade;
+create table strain_feature (
+ strain_feature_id serial not null,
+ primary key (strain_feature_id),
+ strain_id int not null,
+ foreign key (strain_id) references strain (strain_id) on delete cascade INITIALLY DEFERRED,
+ feature_id int not null,
+ foreign key (feature_id) references feature (feature_id) on delete cascade INITIALLY DEFERRED,
+ pub_id int not null,
+ foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
+ constraint strain_feature_c1 unique (strain_id,feature_id,pub_id)
+);
+create index strain_feature_idx1 on strain_feature (strain_id);
+create index strain_feature_idx2 on strain_feature (feature_id);
+
+COMMENT ON TABLE strain_feature IS 'strain_feature links a strain to features associated with the strain. Type may
+be, eg, "homozygous" or "heterozygous".';
+
+
+-- ================================================
+-- TABLE: strain_featureprop
+-- ================================================
+
+drop table strain_featureprop cascade;
+create table strain_featureprop (
+ strain_featureprop_id serial not null,
+ primary key (strain_featureprop_id),
+ strain_feature_id int not null,
+ foreign key (strain_feature_id) references strain_feature (strain_feature_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 null,
+ rank int not null default 0,
+ constraint strain_featureprop_c1 unique (strain_feature_id,type_id,rank)
+);
+create index strain_featureprop_idx1 on strain_featureprop (strain_feature_id);
+create index strain_featureprop_idx2 on strain_featureprop (type_id);
+
+COMMENT ON TABLE strain_featureprop IS 'Attributes of a strain_feature relationship. Eg, a comment';
+
+
+-- ================================================
+-- TABLE: strain_phenotype
+-- ================================================
+
+drop table strain_phenotype cascade;
+create table strain_phenotype (
+ strain_phenotype_id SERIAL NOT NULL,
+ primary key (strain_phenotype_id),
+ strain_id INT NOT NULL,
+ foreign key (strain_id) references strain (strain_id) on delete cascade,
+ phenotype_id INT NOT NULL,
+ foreign key (phenotype_id) references phenotype (phenotype_id) on delete cascade,
+ pub_id int not null,
+ foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED,
+ constraint strain_phenotype_c1 unique (strain_id,phenotype_id,pub_id)
+);
+create index strain_phenotype_idx1 ON strain_phenotype (strain_id);
+create index strain_phenotype_idx2 ON strain_phenotype (phenotype_id);
+
+COMMENT on table strain_phenotype IS 'Links phenotype(s) associated with a given strain. Types may be, eg, "selected" or "unassigned".';
This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site.
|
