From: <sco...@us...> - 2011-08-03 16:49:01
|
Revision: 25108 http://gmod.svn.sourceforge.net/gmod/?rev=25108&view=rev Author: scottcain Date: 2011-08-03 16:48:48 +0000 (Wed, 03 Aug 2011) Log Message: ----------- updating schema for a minor omission, updating the schema diffs Modified Paths: -------------- schema/trunk/chado/Makefile.PL schema/trunk/chado/lib/Bio/Chado/Builder.pm schema/trunk/chado/modules/default_nofuncs.sql schema/trunk/chado/modules/default_schema.sql schema/trunk/chado/modules/stock/stock.sql Added Paths: ----------- schema/trunk/chado/schemas/1.2/ schema/trunk/chado/schemas/1.2/default_schema.sql schema/trunk/chado/schemas/1.2-1.0/ schema/trunk/chado/schemas/1.2-1.1/ schema/trunk/chado/schemas/1.2-1.11/ schema/trunk/chado/schemas/1.2-1.11/diff schema/trunk/chado/schemas/1.2-1.11/diff.sql Modified: schema/trunk/chado/Makefile.PL =================================================================== --- schema/trunk/chado/Makefile.PL 2011-08-03 16:32:25 UTC (rev 25107) +++ schema/trunk/chado/Makefile.PL 2011-08-03 16:48:48 UTC (rev 25108) @@ -46,6 +46,8 @@ use Template; use Module::Build; +my $VERSION = 1.2; + my %args = ( DBDRIVER => { default => 'PostgreSQL' @@ -385,6 +387,7 @@ " LOCAL_TMP=$LOCAL_TMP", " DBORGANISM=$DBORGANISM", " DEFAULT=$DEFAULT", + " VERSION=$VERSION", ),"\n\n"; # @@ -402,6 +405,7 @@ $conf{'PREFIX'} = $opts{'PREFIX'}; $conf{'LIB'} = $opts{'LIB'}; $conf{'DEFAULT'} = $DEFAULT; + $conf{'VERSION'} = $VERSION; print CONF map { "$_=$conf{ $_ }\n" } keys %conf; close CONF or die "Can't write file '$build_config': $!\n"; @@ -502,7 +506,7 @@ # 'INSTALLSITELIB' => '$(INSTALLSITEARCH)', 'NAME' => 'chado', # 'NAME' => 'gmod', - 'VERSION' => '1.2', + 'VERSION' => $VERSION, 'PREREQ_PM' => { #'SQL::Translator' => 0.05, #'Class::DBI' => 0.94, Modified: schema/trunk/chado/lib/Bio/Chado/Builder.pm =================================================================== --- schema/trunk/chado/lib/Bio/Chado/Builder.pm 2011-08-03 16:32:25 UTC (rev 25107) +++ schema/trunk/chado/lib/Bio/Chado/Builder.pm 2011-08-03 16:48:48 UTC (rev 25108) @@ -74,6 +74,7 @@ my $db_port = $conf->{'database'}{'db_port'} || ''; my $db_user = $conf->{'database'}{'db_username'} || ''; my $build_dir = $conf->{'build'}{'working_dir'} || ''; + my $schema_version = $conf->{'build'}{'version'}; my $init_sql = catfile( $build_dir, 'load', 'etc', 'initialize.sql' ); my $sys_call = "psql -h $db_host -p $db_port -U $db_user -f $init_sql $db_name"; Modified: schema/trunk/chado/modules/default_nofuncs.sql =================================================================== --- schema/trunk/chado/modules/default_nofuncs.sql 2011-08-03 16:32:25 UTC (rev 25107) +++ schema/trunk/chado/modules/default_nofuncs.sql 2011-08-03 16:48:48 UTC (rev 25108) @@ -3314,7 +3314,7 @@ stock_relationship_cvterm_id SERIAL NOT NULL, PRIMARY KEY (stock_relationship_cvterm_id), stock_relatiohship_id integer NOT NULL, - --FOREIGN KEY (stock_relationship_id) references stock_relationship (stock_relationship_id) ON DELETE CASCADE INITIALLY DEFERRED, + FOREIGN KEY (stock_relationship_id) references stock_relationship (stock_relationship_id) ON DELETE CASCADE INITIALLY DEFERRED, cvterm_id integer NOT NULL, FOREIGN KEY (cvterm_id) REFERENCES cvterm (cvterm_id) ON DELETE RESTRICT, pub_id integer, Modified: schema/trunk/chado/modules/default_schema.sql =================================================================== --- schema/trunk/chado/modules/default_schema.sql 2011-08-03 16:32:25 UTC (rev 25107) +++ schema/trunk/chado/modules/default_schema.sql 2011-08-03 16:48:48 UTC (rev 25108) @@ -40403,7 +40403,7 @@ stock_relationship_cvterm_id SERIAL NOT NULL, PRIMARY KEY (stock_relationship_cvterm_id), stock_relatiohship_id integer NOT NULL, - --FOREIGN KEY (stock_relationship_id) references stock_relationship (stock_relationship_id) ON DELETE CASCADE INITIALLY DEFERRED, + FOREIGN KEY (stock_relationship_id) references stock_relationship (stock_relationship_id) ON DELETE CASCADE INITIALLY DEFERRED, cvterm_id integer NOT NULL, FOREIGN KEY (cvterm_id) REFERENCES cvterm (cvterm_id) ON DELETE RESTRICT, pub_id integer, Modified: schema/trunk/chado/modules/stock/stock.sql =================================================================== --- schema/trunk/chado/modules/stock/stock.sql 2011-08-03 16:32:25 UTC (rev 25107) +++ schema/trunk/chado/modules/stock/stock.sql 2011-08-03 16:48:48 UTC (rev 25108) @@ -145,7 +145,7 @@ stock_relationship_cvterm_id SERIAL NOT NULL, PRIMARY KEY (stock_relationship_cvterm_id), stock_relatiohship_id integer NOT NULL, - --FOREIGN KEY (stock_relationship_id) references stock_relationship (stock_relationship_id) ON DELETE CASCADE INITIALLY DEFERRED, + FOREIGN KEY (stock_relationship_id) references stock_relationship (stock_relationship_id) ON DELETE CASCADE INITIALLY DEFERRED, cvterm_id integer NOT NULL, FOREIGN KEY (cvterm_id) REFERENCES cvterm (cvterm_id) ON DELETE RESTRICT, pub_id integer, Added: schema/trunk/chado/schemas/1.2/default_schema.sql =================================================================== --- schema/trunk/chado/schemas/1.2/default_schema.sql (rev 0) +++ schema/trunk/chado/schemas/1.2/default_schema.sql 2011-08-03 16:48:48 UTC (rev 25108) @@ -0,0 +1,41889 @@ +-- $Id: general.sql,v 1.31 2007-03-01 02:45:54 briano Exp $ +-- ========================================== +-- Chado general module +-- +-- ================================================ +-- TABLE: tableinfo +-- ================================================ + +create table tableinfo ( + tableinfo_id serial not null, + primary key (tableinfo_id), + name varchar(30) not null, + primary_key_column varchar(30) null, + is_view int not null default 0, + view_on_table_id int null, + superclass_table_id int null, + is_updateable int not null default 1, + modification_date date not null default now(), + constraint tableinfo_c1 unique (name) +); + +COMMENT ON TABLE tableinfo IS NULL; + +-- ================================================ +-- TABLE: db +-- ================================================ + +create table db ( + db_id serial not null, + primary key (db_id), + name varchar(255) not null, +-- contact_id int, +-- foreign key (contact_id) references contact (contact_id) on delete cascade INITIALLY DEFERRED, + description varchar(255) null, + urlprefix varchar(255) null, + url varchar(255) null, + constraint db_c1 unique (name) +); + +COMMENT ON TABLE db IS 'A database authority. Typical databases in +bioinformatics are FlyBase, GO, UniProt, NCBI, MGI, etc. The authority +is generally known by this shortened form, which is unique within the +bioinformatics and biomedical realm. To Do - add support for URIs, +URNs (e.g. LSIDs). We can do this by treating the URL as a URI - +however, some applications may expect this to be resolvable - to be +decided.'; + +-- ================================================ +-- TABLE: dbxref +-- ================================================ + +create table dbxref ( + dbxref_id serial not null, + primary key (dbxref_id), + db_id int not null, + foreign key (db_id) references db (db_id) on delete cascade INITIALLY DEFERRED, + accession varchar(255) not null, + version varchar(255) not null default '', + description text, + constraint dbxref_c1 unique (db_id,accession,version) +); +create index dbxref_idx1 on dbxref (db_id); +create index dbxref_idx2 on dbxref (accession); +create index dbxref_idx3 on dbxref (version); + +COMMENT ON TABLE dbxref IS 'A unique, global, public, stable identifier. Not necessarily an external reference - can reference data items inside the particular chado instance being used. Typically a row in a table can be uniquely identified with a primary identifier (called dbxref_id); a table may also have secondary identifiers (in a linking table <T>_dbxref). A dbxref is generally written as <DB>:<ACCESSION> or as <DB>:<ACCESSION>:<VERSION>.'; + +COMMENT ON COLUMN dbxref.accession IS 'The local part of the identifier. Guaranteed by the db authority to be unique for that db.'; + +CREATE VIEW db_dbxref_count AS + SELECT db.name,count(*) AS num_dbxrefs FROM db INNER JOIN dbxref USING (db_id) GROUP BY db.name; +COMMENT ON VIEW db_dbxref_count IS 'per-db dbxref counts'; + +CREATE OR REPLACE FUNCTION store_db (VARCHAR) + RETURNS INT AS +'DECLARE + v_name ALIAS FOR $1; + + v_db_id INTEGER; + BEGIN + SELECT INTO v_db_id db_id + FROM db + WHERE name=v_name; + IF NOT FOUND THEN + INSERT INTO db + (name) + VALUES + (v_name); + RETURN currval(''db_db_id_seq''); + END IF; + RETURN v_db_id; + END; +' LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION store_dbxref (VARCHAR,VARCHAR) + RETURNS INT AS +'DECLARE + v_dbname ALIAS FOR $1; + v_accession ALIAS FOR $1; + + v_db_id INTEGER; + v_dbxref_id INTEGER; + BEGIN + SELECT INTO v_db_id + store_db(v_dbname); + SELECT INTO v_dbxref_id dbxref_id + FROM dbxref + WHERE db_id=v_db_id AND + accession=v_accession; + IF NOT FOUND THEN + INSERT INTO dbxref + (db_id,accession) + VALUES + (v_db_id,v_accession); + RETURN currval(''dbxref_dbxref_id_seq''); + END IF; + RETURN v_dbxref_id; + END; +' LANGUAGE 'plpgsql'; + +-- $Id: cv.sql,v 1.37 2007-02-28 15:08:48 briano Exp $ +-- ========================================== +-- Chado cv module +-- +-- ================================================================= +-- Dependencies: +-- +-- :import dbxref from general +-- ================================================================= + +-- ================================================ +-- TABLE: cv +-- ================================================ +create table cv ( + cv_id serial not null, + primary key (cv_id), + name varchar(255) not null, + definition text, + constraint cv_c1 unique (name) +); + +COMMENT ON TABLE cv IS 'A controlled vocabulary or ontology. A cv is +composed of cvterms (AKA terms, classes, types, universals - relations +and properties are also stored in cvterm) and the relationships +between them.'; + +COMMENT ON COLUMN cv.name IS 'The name of the ontology. This +corresponds to the obo-format -namespace-. cv names uniquely identify +the cv. In OBO file format, the cv.name is known as the namespace.'; + +COMMENT ON COLUMN cv.definition IS 'A text description of the criteria for +membership of this ontology.'; + +-- ================================================ +-- TABLE: cvterm +-- ================================================ +create table cvterm ( + cvterm_id serial not null, + primary key (cvterm_id), + cv_id int not null, + foreign key (cv_id) references cv (cv_id) on delete cascade INITIALLY DEFERRED, + name varchar(1024) not null, + definition text, + dbxref_id int not null, + foreign key (dbxref_id) references dbxref (dbxref_id) on delete set null INITIALLY DEFERRED, + is_obsolete int not null default 0, + is_relationshiptype int not null default 0, + constraint cvterm_c1 unique (name,cv_id,is_obsolete), + constraint cvterm_c2 unique (dbxref_id) +); +create index cvterm_idx1 on cvterm (cv_id); +create index cvterm_idx2 on cvterm (name); +create index cvterm_idx3 on cvterm (dbxref_id); + +COMMENT ON TABLE cvterm IS 'A term, class, universal or type within an +ontology or controlled vocabulary. This table is also used for +relations and properties. cvterms constitute nodes in the graph +defined by the collection of cvterms and cvterm_relationships.'; + +COMMENT ON COLUMN cvterm.cv_id IS 'The cv or ontology or namespace to which +this cvterm belongs.'; + +COMMENT ON COLUMN cvterm.name IS 'A concise human-readable name or +label for the cvterm. Uniquely identifies a cvterm within a cv.'; + +COMMENT ON COLUMN cvterm.definition IS 'A human-readable text +definition.'; + +COMMENT ON COLUMN cvterm.dbxref_id IS 'Primary identifier dbxref - The +unique global OBO identifier for this cvterm. Note that a cvterm may +have multiple secondary dbxrefs - see also table: cvterm_dbxref.'; + +COMMENT ON COLUMN cvterm.is_obsolete IS 'Boolean 0=false,1=true; see +GO documentation for details of obsoletion. Note that two terms with +different primary dbxrefs may exist if one is obsolete.'; + +COMMENT ON COLUMN cvterm.is_relationshiptype IS 'Boolean +0=false,1=true relations or relationship types (also known as Typedefs +in OBO format, or as properties or slots) form a cv/ontology in +themselves. We use this flag to indicate whether this cvterm is an +actual term/class/universal or a relation. Relations may be drawn from +the OBO Relations ontology, but are not exclusively drawn from there.'; + +COMMENT ON INDEX cvterm_c1 IS 'A name can mean different things in +different contexts; for example "chromosome" in SO and GO. A name +should be unique within an ontology or cv. A name may exist twice in a +cv, in both obsolete and non-obsolete forms - these will be for +different cvterms with different OBO identifiers; so GO documentation +for more details on obsoletion. Note that occasionally multiple +obsolete terms with the same name will exist in the same cv. If this +is a possibility for the ontology under consideration (e.g. GO) then the +ID should be appended to the name to ensure uniqueness.'; + +COMMENT ON INDEX cvterm_c2 IS 'The OBO identifier is globally unique.'; + +-- ================================================ +-- TABLE: cvterm_relationship +-- ================================================ +create table cvterm_relationship ( + cvterm_relationship_id serial not null, + primary key (cvterm_relationship_id), + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + subject_id int not null, + foreign key (subject_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + object_id int not null, + foreign key (object_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + constraint cvterm_relationship_c1 unique (subject_id,object_id,type_id) +); +create index cvterm_relationship_idx1 on cvterm_relationship (type_id); +create index cvterm_relationship_idx2 on cvterm_relationship (subject_id); +create index cvterm_relationship_idx3 on cvterm_relationship (object_id); + +COMMENT ON TABLE cvterm_relationship IS 'A relationship linking two +cvterms. Each cvterm_relationship constitutes an edge in the graph +defined by the collection of cvterms and cvterm_relationships. The +meaning of the cvterm_relationship depends on the definition of the +cvterm R refered to by type_id. However, in general the definitions +are such that the statement "all SUBJs REL some OBJ" is true. The +cvterm_relationship statement is about the subject, not the +object. For example "insect wing part_of thorax".'; + +COMMENT ON COLUMN cvterm_relationship.subject_id IS 'The subject of +the subj-predicate-obj sentence. The cvterm_relationship is about the +subject. In a graph, this typically corresponds to the child node.'; + +COMMENT ON COLUMN cvterm_relationship.object_id IS 'The object of the +subj-predicate-obj sentence. The cvterm_relationship refers to the +object. In a graph, this typically corresponds to the parent node.'; + +COMMENT ON COLUMN cvterm_relationship.type_id IS 'The nature of the +relationship between subject and object. Note that relations are also +housed in the cvterm table, typically from the OBO relationship +ontology, although other relationship types are allowed.'; + +-- ================================================ +-- TABLE: cvtermpath +-- ================================================ +create table cvtermpath ( + cvtermpath_id serial not null, + primary key (cvtermpath_id), + type_id int, + foreign key (type_id) references cvterm (cvterm_id) on delete set null INITIALLY DEFERRED, + subject_id int not null, + foreign key (subject_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + object_id int not null, + foreign key (object_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + cv_id int not null, + foreign key (cv_id) references cv (cv_id) on delete cascade INITIALLY DEFERRED, + pathdistance int, + constraint cvtermpath_c1 unique (subject_id,object_id,type_id,pathdistance) +); +create index cvtermpath_idx1 on cvtermpath (type_id); +create index cvtermpath_idx2 on cvtermpath (subject_id); +create index cvtermpath_idx3 on cvtermpath (object_id); +create index cvtermpath_idx4 on cvtermpath (cv_id); + +COMMENT ON TABLE cvtermpath IS 'The reflexive transitive closure of +the cvterm_relationship relation.'; + +COMMENT ON COLUMN cvtermpath.type_id IS 'The relationship type that +this is a closure over. If null, then this is a closure over ALL +relationship types. If non-null, then this references a relationship +cvterm - note that the closure will apply to both this relationship +AND the OBO_REL:is_a (subclass) relationship.'; + +COMMENT ON COLUMN cvtermpath.cv_id IS 'Closures will mostly be within +one cv. If the closure of a relationship traverses a cv, then this +refers to the cv of the object_id cvterm.'; + +COMMENT ON COLUMN cvtermpath.pathdistance IS 'The number of steps +required to get from the subject cvterm to the object cvterm, counting +from zero (reflexive relationship).'; + +-- ================================================ +-- TABLE: cvtermsynonym +-- ================================================ +create table cvtermsynonym ( + cvtermsynonym_id serial not null, + primary key (cvtermsynonym_id), + cvterm_id int not null, + foreign key (cvterm_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + synonym varchar(1024) not null, + type_id int, + foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + constraint cvtermsynonym_c1 unique (cvterm_id,synonym) +); +create index cvtermsynonym_idx1 on cvtermsynonym (cvterm_id); + +COMMENT ON TABLE cvtermsynonym IS 'A cvterm actually represents a +distinct class or concept. A concept can be refered to by different +phrases or names. In addition to the primary name (cvterm.name) there +can be a number of alternative aliases or synonyms. For example, "T +cell" as a synonym for "T lymphocyte".'; + +COMMENT ON COLUMN cvtermsynonym.type_id IS 'A synonym can be exact, +narrower, or broader than.'; + + +-- ================================================ +-- TABLE: cvterm_dbxref +-- ================================================ +create table cvterm_dbxref ( + cvterm_dbxref_id serial not null, + primary key (cvterm_dbxref_id), + cvterm_id int not null, + foreign key (cvterm_id) references cvterm (cvterm_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_for_definition int not null default 0, + constraint cvterm_dbxref_c1 unique (cvterm_id,dbxref_id) +); +create index cvterm_dbxref_idx1 on cvterm_dbxref (cvterm_id); +create index cvterm_dbxref_idx2 on cvterm_dbxref (dbxref_id); + +COMMENT ON TABLE cvterm_dbxref IS 'In addition to the primary +identifier (cvterm.dbxref_id) a cvterm can have zero or more secondary +identifiers/dbxrefs, which may refer to records in external +databases. The exact semantics of cvterm_dbxref are not fixed. For +example: the dbxref could be a pubmed ID that is pertinent to the +cvterm, or it could be an equivalent or similar term in another +ontology. For example, GO cvterms are typically linked to InterPro +IDs, even though the nature of the relationship between them is +largely one of statistical association. The dbxref may be have data +records attached in the same database instance, or it could be a +"hanging" dbxref pointing to some external database. NOTE: If the +desired objective is to link two cvterms together, and the nature of +the relation is known and holds for all instances of the subject +cvterm then consider instead using cvterm_relationship together with a +well-defined relation.'; + +COMMENT ON COLUMN cvterm_dbxref.is_for_definition IS 'A +cvterm.definition should be supported by one or more references. If +this column is true, the dbxref is not for a term in an external database - +it is a dbxref for provenance information for the definition.'; + + +-- ================================================ +-- TABLE: cvtermprop +-- ================================================ +create table cvtermprop ( + cvtermprop_id serial not null, + primary key (cvtermprop_id), + cvterm_id int not null, + foreign key (cvterm_id) references cvterm (cvterm_id) on delete cascade, + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) on delete cascade, + value text not null default '', + rank int not null default 0, + + unique(cvterm_id, type_id, value, rank) +); +create index cvtermprop_idx1 on cvtermprop (cvterm_id); +create index cvtermprop_idx2 on cvtermprop (type_id); + +COMMENT ON TABLE cvtermprop IS 'Additional extensible properties can be attached to a cvterm using this table. Corresponds to -AnnotationProperty- in W3C OWL format.'; + +COMMENT ON COLUMN cvtermprop.type_id IS 'The name of the property or slot is a cvterm. The meaning of the property is defined in that cvterm.'; + +COMMENT ON COLUMN cvtermprop.value IS 'The value of the property, represented as text. Numeric values are converted to their text representation.'; + +COMMENT ON COLUMN cvtermprop.rank IS 'Property-Value ordering. Any +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: dbxrefprop +-- ================================================ +create table dbxrefprop ( + dbxrefprop_id serial not null, + primary key (dbxrefprop_id), + dbxref_id int not null, + foreign key (dbxref_id) references dbxref (dbxref_id) INITIALLY DEFERRED, + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) INITIALLY DEFERRED, + value text not null default '', + rank int not null default 0, + constraint dbxrefprop_c1 unique (dbxref_id,type_id,rank) +); +create index dbxrefprop_idx1 on dbxrefprop (dbxref_id); +create index dbxrefprop_idx2 on dbxrefprop (type_id); + +COMMENT ON TABLE dbxrefprop IS 'Metadata about a dbxref. Note that this is not defined in the dbxref module, as it depends on the cvterm table. This table has a structure analagous to cvtermprop.'; + + +-- ================================================ +-- TABLE: cvprop +-- ================================================ +create table cvprop ( + cvprop_id serial not null, + primary key (cvprop_id), + cv_id int not null, + foreign key (cv_id) references cv (cv_id) INITIALLY DEFERRED, + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) INITIALLY DEFERRED, + value text, + rank int not null default 0, + constraint cvprop_c1 unique (cv_id,type_id,rank) +); + +COMMENT ON TABLE cvprop IS 'Additional extensible properties can be attached to a cv using this table. A notable example would be the cv version'; + +COMMENT ON COLUMN cvprop.type_id IS 'The name of the property or slot is a cvterm. The meaning of the property is defined in that cvterm.'; +COMMENT ON COLUMN cvprop.value IS 'The value of the property, represented as text. Numeric values are converted to their text representation.'; + +COMMENT ON COLUMN cvprop.rank IS 'Property-Value ordering. Any +cv 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: chadoprop +-- ================================================ +create table chadoprop ( + chadoprop_id serial not null, + primary key (chadoprop_id), + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) INITIALLY DEFERRED, + value text, + rank int not null default 0, + constraint chadoprop_c1 unique (type_id,rank) +); + +COMMENT ON TABLE chadoprop IS 'This table is different from other prop tables in the database, as it is for storing information about the database itself, like schema version'; + +COMMENT ON COLUMN chadoprop.type_id IS 'The name of the property or slot is a cvterm. The meaning of the property is defined in that cvterm.'; +COMMENT ON COLUMN chadoprop.value IS 'The value of the property, represented as text. Numeric values are converted to their text representation.'; + +COMMENT ON COLUMN chadoprop.rank IS 'Property-Value ordering. Any +cv 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.'; + +CREATE OR REPLACE VIEW cv_root AS + SELECT + cv_id, + cvterm_id AS root_cvterm_id + FROM cvterm + WHERE + cvterm_id NOT IN ( SELECT subject_id FROM cvterm_relationship) AND + is_obsolete=0; + +COMMENT ON VIEW cv_root IS 'the roots of a cv are the set of terms +which have no parents (terms that are not the subject of a +relation). Most cvs will have a single root, some may have >1. All +will have at least 1'; + +CREATE OR REPLACE VIEW cv_leaf AS + SELECT + cv_id, + cvterm_id + FROM cvterm + WHERE + cvterm_id NOT IN ( SELECT object_id FROM cvterm_relationship); + +COMMENT ON VIEW cv_leaf IS 'the leaves of a cv are the set of terms +which have no children (terms that are not the object of a +relation). All cvs will have at least 1 leaf'; + +CREATE OR REPLACE VIEW common_ancestor_cvterm AS + SELECT + p1.subject_id AS cvterm1_id, + p2.subject_id AS cvterm2_id, + p1.object_id AS ancestor_cvterm_id, + p1.pathdistance AS pathdistance1, + p2.pathdistance AS pathdistance2, + p1.pathdistance + p2.pathdistance + AS total_pathdistance + FROM + cvtermpath AS p1, + cvtermpath AS p2 + WHERE + p1.object_id = p2.object_id; + +COMMENT ON VIEW common_ancestor_cvterm IS 'The common ancestor of any +two terms is the intersection of both terms ancestors. Two terms can +have multiple common ancestors. Use total_pathdistance to get the +least common ancestor'; + +CREATE OR REPLACE VIEW common_descendant_cvterm AS + SELECT + p1.object_id AS cvterm1_id, + p2.object_id AS cvterm2_id, + p1.subject_id AS ancestor_cvterm_id, + p1.pathdistance AS pathdistance1, + p2.pathdistance AS pathdistance2, + p1.pathdistance + p2.pathdistance + AS total_pathdistance + FROM + cvtermpath AS p1, + cvtermpath AS p2 + WHERE + p1.subject_id = p2.subject_id; + +COMMENT ON VIEW common_descendant_cvterm IS 'The common descendant of +any two terms is the intersection of both terms descendants. Two terms +can have multiple common descendants. Use total_pathdistance to get +the least common ancestor'; + +CREATE OR REPLACE VIEW stats_paths_to_root AS + SELECT + subject_id AS cvterm_id, + count(DISTINCT cvtermpath_id) AS total_paths, + avg(pathdistance) AS avg_distance, + min(pathdistance) AS min_distance, + max(pathdistance) AS max_distance + FROM cvtermpath INNER JOIN cv_root ON (object_id=root_cvterm_id) + GROUP BY cvterm_id; + +COMMENT ON VIEW stats_paths_to_root IS 'per-cvterm statistics on its +placement in the DAG relative to the root. There may be multiple paths +from any term to the root. This gives the total number of paths, and +the average minimum and maximum distances. Here distance is defined by +cvtermpath.pathdistance'; +CREATE VIEW cv_cvterm_count AS + SELECT cv.name,count(*) AS num_terms_excl_obs FROM cv INNER JOIN cvterm USING (cv_id) WHERE is_obsolete=0 GROUP BY cv.name; +COMMENT ON VIEW cv_cvterm_count IS 'per-cv terms counts (excludes obsoletes)'; + +CREATE VIEW cv_cvterm_count_with_obs AS + SELECT cv.name,count(*) AS num_terms_incl_obs FROM cv INNER JOIN cvterm USING (cv_id) GROUP BY cv.name; +COMMENT ON VIEW cv_cvterm_count_with_obs IS 'per-cv terms counts (includes obsoletes)'; + +CREATE VIEW cv_link_count AS + SELECT cv.name AS cv_name, + relation.name AS relation_name, + relation_cv.name AS relation_cv_name, + count(*) AS num_links + FROM cv + INNER JOIN cvterm ON (cvterm.cv_id=cv.cv_id) + INNER JOIN cvterm_relationship ON (cvterm.cvterm_id=subject_id) + INNER JOIN cvterm AS relation ON (type_id=relation.cvterm_id) + INNER JOIN cv AS relation_cv ON (relation.cv_id=relation_cv.cv_id) + GROUP BY cv.name,relation.name,relation_cv.name; + +COMMENT ON VIEW cv_link_count IS 'per-cv summary of number of +links (cvterm_relationships) broken down by +relationship_type. num_links is the total # of links of the specified +type in which the subject_id of the link is in the named cv'; + +CREATE VIEW cv_path_count AS + SELECT cv.name AS cv_name, + relation.name AS relation_name, + relation_cv.name AS relation_cv_name, + count(*) AS num_paths + FROM cv + INNER JOIN cvterm ON (cvterm.cv_id=cv.cv_id) + INNER JOIN cvtermpath ON (cvterm.cvterm_id=subject_id) + INNER JOIN cvterm AS relation ON (type_id=relation.cvterm_id) + INNER JOIN cv AS relation_cv ON (relation.cv_id=relation_cv.cv_id) + GROUP BY cv.name,relation.name,relation_cv.name; + +COMMENT ON VIEW cv_path_count IS 'per-cv summary of number of +paths (cvtermpaths) broken down by relationship_type. num_paths is the +total # of paths of the specified type in which the subject_id of the +path is in the named cv. See also: cv_distinct_relations'; + +CREATE OR REPLACE FUNCTION _get_all_subject_ids(integer) RETURNS SETOF cvtermpath AS +' +DECLARE + root alias for $1; + cterm cvtermpath%ROWTYPE; + cterm2 cvtermpath%ROWTYPE; +BEGIN + + FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = root LOOP + RETURN NEXT cterm; + FOR cterm2 IN SELECT * FROM _get_all_subject_ids(cterm.subject_id) LOOP + RETURN NEXT cterm2; + END LOOP; + END LOOP; + RETURN; +END; +' +LANGUAGE 'plpgsql'; + +---arg: parent term id +---return: all children term id and their parent term id with relationship type id +CREATE OR REPLACE FUNCTION get_all_subject_ids(integer) RETURNS SETOF cvtermpath AS +' +DECLARE + root alias for $1; + cterm cvtermpath%ROWTYPE; + exist_c int; +BEGIN + + SELECT INTO exist_c count(*) FROM cvtermpath WHERE object_id = root and pathdistance <= 0; + IF (exist_c > 0) THEN + FOR cterm IN SELECT * FROM cvtermpath WHERE object_id = root and pathdistance > 0 LOOP + RETURN NEXT cterm; + END LOOP; + ELSE + FOR cterm IN SELECT * FROM _get_all_subject_ids(root) LOOP + RETURN NEXT cterm; + END LOOP; + END IF; + RETURN; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION get_graph_below(integer) RETURNS SETOF cvtermpath AS +' +DECLARE + root alias for $1; + cterm cvtermpath%ROWTYPE; + cterm2 cvtermpath%ROWTYPE; + +BEGIN + + FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = root LOOP + RETURN NEXT cterm; + FOR cterm2 IN SELECT * FROM get_all_subject_ids(cterm.subject_id) LOOP + RETURN NEXT cterm2; + END LOOP; + END LOOP; + RETURN; +END; +' +LANGUAGE 'plpgsql'; + + +CREATE OR REPLACE FUNCTION get_graph_above(integer) RETURNS SETOF cvtermpath AS +' +DECLARE + leaf alias for $1; + cterm cvtermpath%ROWTYPE; + cterm2 cvtermpath%ROWTYPE; + +BEGIN + + FOR cterm IN SELECT * FROM cvterm_relationship WHERE subject_id = leaf LOOP + RETURN NEXT cterm; + FOR cterm2 IN SELECT * FROM get_all_object_ids(cterm.object_id) LOOP + RETURN NEXT cterm2; + END LOOP; + END LOOP; + RETURN; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION _get_all_object_ids(integer) RETURNS SETOF cvtermpath AS +' +DECLARE + leaf alias for $1; + cterm cvtermpath%ROWTYPE; + cterm2 cvtermpath%ROWTYPE; +BEGIN + + FOR cterm IN SELECT * FROM cvterm_relationship WHERE subject_id = leaf LOOP + RETURN NEXT cterm; + FOR cterm2 IN SELECT * FROM _get_all_object_ids(cterm.object_id) LOOP + RETURN NEXT cterm2; + END LOOP; + END LOOP; + RETURN; +END; +' +LANGUAGE 'plpgsql'; + +---arg: child term id +---return: all parent term id and their childrent term id with relationship type id +CREATE OR REPLACE FUNCTION get_all_object_ids(integer) RETURNS SETOF cvtermpath AS +' +DECLARE + leaf alias for $1; + cterm cvtermpath%ROWTYPE; + exist_c int; +BEGIN + + + SELECT INTO exist_c count(*) FROM cvtermpath WHERE object_id = leaf and pathdistance <= 0; + IF (exist_c > 0) THEN + FOR cterm IN SELECT * FROM cvtermpath WHERE subject_id = leaf AND pathdistance > 0 LOOP + RETURN NEXT cterm; + END LOOP; + ELSE + FOR cterm IN SELECT * FROM _get_all_object_ids(leaf) LOOP + RETURN NEXT cterm; + END LOOP; + END IF; + RETURN; +END; +' +LANGUAGE 'plpgsql'; + +---arg: sql statement which must be in the form of select cvterm_id from ... +---return: a set of cvterm ids that includes what is in sql statement and their children (subject ids) +CREATE OR REPLACE FUNCTION get_it_sub_cvterm_ids(text) RETURNS SETOF cvterm AS +' +DECLARE + query alias for $1; + cterm cvterm%ROWTYPE; + cterm2 cvterm%ROWTYPE; +BEGIN + FOR cterm IN EXECUTE query LOOP + RETURN NEXT cterm; + FOR cterm2 IN SELECT subject_id as cvterm_id FROM get_all_subject_ids(cterm.cvterm_id) LOOP + RETURN NEXT cterm2; + END LOOP; + END LOOP; + RETURN; +END; +' +LANGUAGE 'plpgsql'; +--- example: select * from fill_cvtermpath(7); where 7 is cv_id for an ontology +--- fill path from the node to its children and their children +CREATE OR REPLACE FUNCTION _fill_cvtermpath4node(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) RETURNS INTEGER AS +' +DECLARE + origin alias for $1; + child_id alias for $2; + cvid alias for $3; + typeid alias for $4; + depth alias for $5; + cterm cvterm_relationship%ROWTYPE; + exist_c int; + +BEGIN + + --- RAISE NOTICE ''depth=% root=%'', depth,child_id; + --- not check type_id as it may be null and not very meaningful in cvtermpath when pathdistance > 1 + SELECT INTO exist_c count(*) FROM cvtermpath WHERE cv_id = cvid AND object_id = origin AND subject_id = child_id AND pathdistance = depth; + + IF (exist_c = 0) THEN + INSERT INTO cvtermpath (object_id, subject_id, cv_id, type_id, pathdistance) VALUES(origin, child_id, cvid, typeid, depth); + END IF; + FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = child_id LOOP + PERFORM _fill_cvtermpath4node(origin, cterm.subject_id, cvid, cterm.type_id, depth+1); + END LOOP; + RETURN 1; +END; +' +LANGUAGE 'plpgsql'; + + +CREATE OR REPLACE FUNCTION _fill_cvtermpath4root(INTEGER, INTEGER) RETURNS INTEGER AS +' +DECLARE + rootid alias for $1; + cvid alias for $2; + ttype int; + cterm cvterm_relationship%ROWTYPE; + child cvterm_relationship%ROWTYPE; + +BEGIN + + SELECT INTO ttype cvterm_id FROM cvterm WHERE (name = ''isa'' OR name = ''is_a''); + PERFORM _fill_cvtermpath4node(rootid, rootid, cvid, ttype, 0); + FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = rootid LOOP + PERFORM _fill_cvtermpath4root(cterm.subject_id, cvid); + -- RAISE NOTICE ''DONE for term, %'', cterm.subject_id; + END LOOP; + RETURN 1; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION fill_cvtermpath(INTEGER) RETURNS INTEGER AS +' +DECLARE + cvid alias for $1; + root cvterm%ROWTYPE; + +BEGIN + + DELETE FROM cvtermpath WHERE cv_id = cvid; + + FOR root IN SELECT DISTINCT t.* from cvterm t LEFT JOIN cvterm_relationship r ON (t.cvterm_id = r.subject_id) INNER JOIN cvterm_relationship r2 ON (t.cvterm_id = r2.object_id) WHERE t.cv_id = cvid AND r.subject_id is null LOOP + PERFORM _fill_cvtermpath4root(root.cvterm_id, root.cv_id); + END LOOP; + RETURN 1; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION fill_cvtermpath(cv.name%TYPE) RETURNS INTEGER AS +' +DECLARE + cvname alias for $1; + cv_id int; + rtn int; +BEGIN + + SELECT INTO cv_id cv.cv_id from cv WHERE cv.name = cvname; + SELECT INTO rtn fill_cvtermpath(cv_id); + RETURN rtn; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION _fill_cvtermpath4node2detect_cycle(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) RETURNS INTEGER AS +' +DECLARE + origin alias for $1; + child_id alias for $2; + cvid alias for $3; + typeid alias for $4; + depth alias for $5; + cterm cvterm_relationship%ROWTYPE; + exist_c int; + ccount int; + ecount int; + rtn int; +BEGIN + + EXECUTE ''SELECT * FROM tmpcvtermpath p1, tmpcvtermpath p2 WHERE p1.subject_id=p2.object_id AND p1.object_id=p2.subject_id AND p1.object_id = ''|| origin || '' AND p2.subject_id = '' || child_id || ''AND '' || depth || ''> 0''; + GET DIAGNOSTICS ccount = ROW_COUNT; + IF (ccount > 0) THEN + --RAISE EXCEPTION ''FOUND CYCLE: node % on cycle path'',origin; + RETURN origin; + END IF; + + EXECUTE ''SELECT * FROM tmpcvtermpath WHERE cv_id = '' || cvid || '' AND object_id = '' || origin || '' AND subject_id = '' || child_id || '' AND '' || origin || ''<>'' || child_id; + GET DIAGNOSTICS ecount = ROW_COUNT; + IF (ecount > 0) THEN + --RAISE NOTICE ''FOUND TWICE (node), will check root obj % subj %'',origin, child_id; + SELECT INTO rtn _fill_cvtermpath4root2detect_cycle(child_id, cvid); + IF (rtn > 0) THEN + RETURN rtn; + END IF; + END IF; + + EXECUTE ''SELECT * FROM tmpcvtermpath WHERE cv_id = '' || cvid || '' AND object_id = '' || origin || '' AND subject_id = '' || child_id || '' AND pathdistance = '' || depth; + GET DIAGNOSTICS exist_c = ROW_COUNT; + IF (exist_c = 0) THEN + EXECUTE ''INSERT INTO tmpcvtermpath (object_id, subject_id, cv_id, type_id, pathdistance) VALUES('' || origin || '', '' || child_id || '', '' || cvid || '', '' || typeid || '', '' || depth || '')''; + END IF; + + FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = child_id LOOP + --RAISE NOTICE ''DOING for node, % %'', origin, cterm.subject_id; + SELECT INTO rtn _fill_cvtermpath4node2detect_cycle(origin, cterm.subject_id, cvid, cterm.type_id, depth+1); + IF (rtn > 0) THEN + RETURN rtn; + END IF; + END LOOP; + RETURN 0; +END; +' +LANGUAGE 'plpgsql'; + + +CREATE OR REPLACE FUNCTION _fill_cvtermpath4root2detect_cycle(INTEGER, INTEGER) RETURNS INTEGER AS +' +DECLARE + rootid alias for $1; + cvid alias for $2; + ttype int; + ccount int; + cterm cvterm_relationship%ROWTYPE; + child cvterm_relationship%ROWTYPE; + rtn int; +BEGIN + + SELECT INTO ttype cvterm_id FROM cvterm WHERE (name = ''isa'' OR name = ''is_a''); + SELECT INTO rtn _fill_cvtermpath4node2detect_cycle(rootid, rootid, cvid, ttype, 0); + IF (rtn > 0) THEN + RETURN rtn; + END IF; + FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = rootid LOOP + EXECUTE ''SELECT * FROM tmpcvtermpath p1, tmpcvtermpath p2 WHERE p1.subject_id=p2.object_id AND p1.object_id=p2.subject_id AND p1.object_id='' || rootid || '' AND p1.subject_id='' || cterm.subject_id; + GET DIAGNOSTICS ccount = ROW_COUNT; + IF (ccount > 0) THEN + --RAISE NOTICE ''FOUND TWICE (root), will check root obj % subj %'',rootid,cterm.subject_id; + SELECT INTO rtn _fill_cvtermpath4node2detect_cycle(rootid, cterm.subject_id, cvid, ttype, 0); + IF (rtn > 0) THEN + RETURN rtn; + END IF; + ELSE + SELECT INTO rtn _fill_cvtermpath4root2detect_cycle(cterm.subject_id, cvid); + IF (rtn > 0) THEN + RETURN rtn; + END IF; + END IF; + END LOOP; + RETURN 0; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION get_cycle_cvterm_id(INTEGER, INTEGER) RETURNS INTEGER AS +' +DECLARE + cvid alias for $1; + rootid alias for $2; + rtn int; +BEGIN + + CREATE TEMP TABLE tmpcvtermpath(object_id int, subject_id int, cv_id int, type_id int, pathdistance int); + CREATE INDEX tmp_cvtpath1 ON tmpcvtermpath(object_id, subject_id); + + SELECT INTO rtn _fill_cvtermpath4root2detect_cycle(rootid, cvid); + IF (rtn > 0) THEN + DROP TABLE tmpcvtermpath; + RETURN rtn; + END IF; + DROP TABLE tmpcvtermpath; + RETURN 0; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION get_cycle_cvterm_ids(INTEGER) RETURNS SETOF INTEGER AS +' +DECLARE + cvid alias for $1; + root cvterm%ROWTYPE; + rtn int; +BEGIN + + + FOR root IN SELECT DISTINCT t.* from cvterm t WHERE cv_id = cvid LOOP + SELECT INTO rtn get_cycle_cvterm_id(cvid,root.cvterm_id); + IF (rtn > 0) THEN + RETURN NEXT rtn; + END IF; + END LOOP; + RETURN; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION get_cycle_cvterm_id(INTEGER) RETURNS INTEGER AS +' +DECLARE + cvid alias for $1; + root cvterm%ROWTYPE; + rtn int; +BEGIN + + CREATE TEMP TABLE tmpcvtermpath(object_id int, subject_id int, cv_id int, type_id int, pathdistance int); + CREATE INDEX tmp_cvtpath1 ON tmpcvtermpath(object_id, subject_id); + + FOR root IN SELECT DISTINCT t.* from cvterm t LEFT JOIN cvterm_relationship r ON (t.cvterm_id = r.subject_id) INNER JOIN cvterm_relationship r2 ON (t.cvterm_id = r2.object_id) WHERE t.cv_id = cvid AND r.subject_id is null LOOP + SELECT INTO rtn _fill_cvtermpath4root2detect_cycle(root.cvterm_id, root.cv_id); + IF (rtn > 0) THEN + DROP TABLE tmpcvtermpath; + RETURN rtn; + END IF; + END LOOP; + DROP TABLE tmpcvtermpath; + RETURN 0; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION get_cycle_cvterm_id(cv.name%TYPE) RETURNS INTEGER AS +' +DECLARE + cvname alias for $1; + cv_id int; + rtn int; +BEGIN + + SELECT INTO cv_id cv.cv_id from cv WHERE cv.name = cvname; + SELECT INTO rtn get_cycle_cvterm_id(cv_id); + + RETURN rtn; +END; +' +LANGUAGE 'plpgsql'; +-- $Id: pub.sql,v 1.27 2007-02-19 20:50:44 briano Exp $ +-- ========================================== +-- Chado pub module +-- +-- ================================================================= +-- Dependencies: +-- +-- :import cvterm from cv +-- :import dbxref from general +-- ================================================================= + +-- ================================================ +-- TABLE: pub +-- ================================================ + +create table pub ( + pub_id serial not null, + primary key (pub_id), + title text, + volumetitle text, + volume varchar(255), + series_name varchar(255), + issue varchar(255), + pyear varchar(255), + pages varchar(255), + miniref varchar(255), + uniquename text not null, + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + is_obsolete boolean default 'false', + publisher varchar(255), + pubplace varchar(255), + constraint pub_c1 unique (uniquename) +); +CREATE INDEX pub_idx1 ON pub (type_id); + +COMMENT ON TABLE pub IS 'A documented provenance artefact - publications, +documents, personal communication.'; +COMMENT ON COLUMN pub.title IS 'Descriptive general heading.'; +COMMENT ON COLUMN pub.volumetitle IS 'Title of part if one of a series.'; +COMMENT ON COLUMN pub.series_name IS 'Full name of (journal) series.'; +COMMENT ON COLUMN pub.pages IS 'Page number range[s], e.g. 457--459, viii + 664pp, lv--lvii.'; +COMMENT ON COLUMN pub.type_id IS 'The type of the publication (book, journal, poem, graffiti, etc). Uses pub cv.'; + +-- ================================================ +-- TABLE: pub_relationship +-- ================================================ + +create table pub_relationship ( + pub_relationship_id serial not null, + primary key (pub_relationship_id), + subject_id int not null, + foreign key (subject_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED, + object_id int not null, + foreign key (object_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED, + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + + constraint pub_relationship_c1 unique (subject_id,object_id,type_id) +); +create index pub_relationship_idx1 on pub_relationship (subject_id); +create index pub_relationship_idx2 on pub_relationship (object_id); +create index pub_relationship_idx3 on pub_relationship (type_id); + +COMMENT ON TABLE pub_relationship IS 'Handle relationships between +publications, e.g. when one publication makes others obsolete, when one +publication contains errata with respect to other publication(s), or +when one publication also appears in another pub.'; + +-- ================================================ +-- TABLE: pub_dbxref +-- ================================================ + +create table pub_dbxref ( + pub_dbxref_id serial not null, + primary key (pub_dbxref_id), + pub_id int not null, + foreign key (pub_id) references pub (pub_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 pub_dbxref_c1 unique (pub_id,dbxref_id) +); +create index pub_dbxref_idx1 on pub_dbxref (pub_id); +create index pub_dbxref_idx2 on pub_dbxref (dbxref_id); + +COMMENT ON TABLE pub_dbxref IS 'Handle links to repositories, +e.g. Pubmed, Biosis, zoorec, OCLC, Medline, ISSN, coden...'; + + +-- ================================================ +-- TABLE: pubauthor +-- ================================================ + +create table pubauthor ( + pubauthor_id serial not null, + primary key (pubauthor_id), + pub_id int not null, + foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED, + rank int not null, + editor boolean default 'false', + surname varchar(100) not null, + givennames varchar(100), + suffix varchar(100), + + constraint pubauthor_c1 unique (pub_id, rank) +); +create index pubauthor_idx2 on pubauthor (pub_id); + +COMMENT ON TABLE pubauthor IS 'An author for a publication. Note the denormalisation (hence lack of _ in table name) - this is deliberate as it is in general too hard to assign IDs to authors.'; +COMMENT ON COLUMN pubauthor.givennames IS 'First name, initials'; +COMMENT ON COLUMN pubauthor.suffix IS 'Jr., Sr., etc'; +COMMENT ON COLUMN pubauthor.rank IS 'Order of author in author list for this pub - order is important.'; +COMMENT ON COLUMN pubauthor.editor IS 'Indicates whether the author is an editor for linked publication. Note: this is a boolean field but does not follow the normal chado convention for naming booleans.'; + + +-- ================================================ +-- TABLE: pubprop +-- ================================================ + +create table pubprop ( + pubprop_id serial not null, + primary key (pubprop_id), + pub_id int not null, + foreign key (pub_id) references pub (pub_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, + rank integer, + + constraint pubprop_c1 unique (pub_id,type_id,rank) +); +create index pubprop_idx1 on pubprop (pub_id); +create index pubprop_idx2 on pubprop (type_id); + +COMMENT ON TABLE pubprop IS 'Property-value pairs for a pub. Follows standard chado pattern.'; +-- $Id: organism.sql,v 1.19 2007-04-01 18:45:41 briano Exp $ +-- ========================================== +-- Chado organism module +-- +-- ============ +-- DEPENDENCIES +-- ============ +-- :import cvterm from cv +-- :import dbxref from general +-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +-- ================================================ +-- TABLE: organism +-- ================================================ + +create table organism ( + organism_id serial not null, + primary key (organism_id), + abbreviation varchar(255) null, + genus varchar(255) not null, + species varchar(255) not null, + common_name varchar(255) null, + comment text null, + constraint organism_c1 unique (genus,species) +); + +COMMENT ON TABLE organism IS 'The organismal taxonomic +classification. Note that phylogenies are represented using the +phylogeny module, and taxonomies can be represented using the cvterm +module or the phylogeny module.'; + +COMMENT ON COLUMN organism.species IS 'A type of organism is always +uniquely identified by genus and species. When mapping from the NCBI +taxonomy names.dmp file, this column must be used where it +is present, as the common_name column is not always unique (e.g. environmental +samples). If a particular strain or subspecies is to be represented, +this is appended onto the species name. Follows standard NCBI taxonomy +pattern.'; + +-- ================================================ +-- TABLE: organism_dbxref +-- ================================================ + +create table organism_dbxref ( + organism_dbxref_id serial not null, + primary key (organism_dbxref_id), + organism_id int not null, + foreign key (organism_id) references organism (organism_id) on delete cascade INITIALLY DEFERRED, + dbxref_id int not null, + foreign key (dbxref_id) references dbxref (dbxref_id) on delete cascade INITIALLY DEFERRED, + constraint organism_dbxref_c1 unique (organism_id,dbxref_id) +); +create index organism_dbxref_idx1 on organism_dbxref (organism_id); +create index organism_dbxref_idx2 on organism_dbxref (dbxref_id); + +-- ================================================ +-- TABLE: organismprop +-- ================================================ + +create table organismprop ( + organismprop_id serial not null, + primary key (organismprop_id), + organism_id int not null, + foreign key (organism_id) references organism (organism_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 organismprop_c1 unique (organism_id,type_id,rank) +); +create index organismprop_idx1 on organismprop (organism_id); +create index organismprop_idx2 on organismprop (type_id); + +COMMENT ON TABLE organismprop IS 'Tag-value properties - follows standard chado model.'; + + +CREATE OR REPLACE FUNCTION get_organism_id(VARCHAR,VARCHAR) RETURNS INT + AS ' + SELECT organism_id + FROM organism + WHERE genus=$1 + AND species=$2 + ' LANGUAGE 'sql'; + +CREATE OR REPLACE FUNCTION get_organism_id(VARCHAR) RETURNS INT + AS ' +SELECT organism_id + FROM organism + WHERE genus=substring($1,1,position('' '' IN $1)-1) + AND species=substring($1,position('' '' IN $1)+1) + ' LANGUAGE 'sql'; + +CREATE OR REPLACE FUNCTION get_organism_id_abbrev(VARCHAR) RETURNS INT + AS ' +SELECT organism_id + FROM organism + WHERE substr(genus,1,1)=substring($1,1,1) + AND species=substring($1,position('' '' IN $1)+1) + ' LANGUAGE 'sql'; + +CREATE OR REPLACE FUNCTION store_organism (VARCHAR,VARCHAR,VARCHAR) + RETURNS INT AS +'DECLARE + v_genus ALIAS FOR $1; + v_species ALIAS FOR $2; + v_common_name ALIAS FOR $3; + + v_organism_id INTEGER; + BEGIN + SELECT INTO v_organism_id organism_id + FROM organism + WHERE genus=v_genus AND + species=v_species; + IF NOT FOUND THEN + INSERT INTO organism + (genus,species,common_name) + VALUES + (v_genus,v_species,v_common_name); + RETURN currval(''organism_organism_id_seq''); + ELSE + UPDATE organism + SET common_name=v_common_name + WHERE organism_id = v_organism_id; + END IF; + RETURN v_organism_id; + END; +' LANGUAGE 'plpgsql'; + +-- $Id: sequence.sql,v 1.69 2009-05-14 02:44:23 scottcain Exp $ +-- ========================================== +-- Chado sequence module +-- +-- ================================================================= +-- Dependencies: +-- +-- :import cvterm from cv +-- :import pub from pub +-- :import organism from organism +-- :import dbxref from general +-- ================================================================= + +-- ================================================ +-- TABLE: feature +-- ================================================ + +create table feature ( + feature_id serial not null, + primary key (feature_id), + dbxref_id int, + foreign key (dbxref_id) references dbxref (dbxref_id) on delete set null INITIALLY DEFERRED, + organism_id int not null, + foreign key (organism_id) references organism (organism_id) on delete cascade INITIALLY DEFERRED, + name varchar(255), + uniquename text not null, + residues text, + seqlen int, + md5checksum char(32), + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + is_analysis boolean not null default 'false', + is_obsolete boolean not null default 'false', + timeaccessioned timestamp not null default current_timestamp, + timelastmodified timestamp not null default current_timestamp, + constraint feature_c1 unique (organism_id,uniquename,type_id) +); +create sequence feature_uniquename_seq; +create index feature_name_ind1 on feature(name); +create index feature_idx1 on feature (dbxref_id); +create index feature_idx2 on feature (organism_id); +create index feature_idx3 on feature (type_id); +create index feature_idx4 on feature (uniquename); +create index feature_idx5 on feature (lower(name)); + +ALTER TABLE feature ALTER residues SET STORAGE EXTERNAL; + +COMMENT ON TABLE feature IS 'A feature is a biological sequence or a +section of a biological sequence, or a collection of such +sections. Examples include genes, exons, transcripts, regulatory +regions, polypeptides, protein domains, chromosome sequences, sequence +variations, cross-genome match regions such as hits and HSPs and so +on; see the Sequence Ontology for more. The combination of +organism_id, uniquename and type_id should be unique.'; + +COMMENT ON COLUMN feature.dbxref_id IS 'An optional primary public stable +identifier for this feature. Secondary identifiers and external +dbxrefs go in the table feature_dbxref.'; + +COMMENT ON COLUMN feature.organism_id IS 'The organism to which this feature +belongs. This column is mandatory.'; + +COMMENT ON COLUMN feature.name IS 'The optional human-readable common name for +a feature, for display purposes.'; + +COMMENT ON COLUMN feature.uniquename IS 'The unique name for a feature; may +not be necessarily be particularly human-readable, although this is +preferred. This name must be unique for this type of feature within +this organism.'; + +COMMENT ON COLUMN feature.residues IS 'A sequence of alphabetic characters +representing biological residues (nucleic acids, amino acids). This +column does not need to be manifested for all features; it is optional +for features such as exons where the residues can be derived from the +featureloc. It is recommended that the value for this column be +manifested for features which may may non-contiguous sublocations (e.g. +transcripts), since derivation at query time is non-trivial. For +expressed sequence, the DNA sequence should be used rather than the +RNA sequence. The default storage method for the residues column is +EXTERNAL, which will store it uncompressed to make substring operations +faster.'; + +COMMENT ON COLUMN feature.seqlen IS 'The length of the residue feature. See +column:residues. This column is partially redundant with the residues +column, and also with featureloc. This column is required because the +location may be unknown and the residue sequence may not be +manifested, yet it may be desirable to store and query the length of +the feature. The seqlen should always be manifested where the length +of the sequence is known.'; + +COMMENT ON COLUMN feature.md5checksum IS 'The 32-character checksum of the sequence, +calculated using the MD5 algorithm. This is practically guaranteed to +be unique for any feature. This column thus acts as a unique +identifier on the mathematical sequence.'; + +COMMENT ON COLUMN feature.type_id IS 'A required reference to a table:cvterm +giving the feature type. This will typically be a Sequence Ontology +identifier. This column is thus used to subclass the feature table.'; + +COMMENT ON COLUMN feature.is_analysis IS 'Boolean indicating whether this +feature is annotated or the result of an automated analysis. Analysis +results also use the companalysis module. Note that the dividing line +between analysis and annotation may be fuzzy, this should be determined on +a per-project basis in a consistent manner. One requirement is that +there should only be one non-analysis version of each wild-type gene +feature in a genome, whereas the same gene feature can be predicted +multiple times in different analyses.'; + +COMMENT ON COLUMN feature.is_obsolete IS 'Boolean indicating whether this +feature has been obsoleted. Some chado instances may choose to simply +remove the feature altogether, others may choose to keep an obsolete +row in the table.'; + +COMMENT ON COLUMN feature.timeaccessioned IS 'For handling object +accession or modification timestamps (as opposed to database auditing data, +handled elsewhere). The expectation is that these fields would be +available to software interacting with chado.'; + +COMMENT ON COLUMN feature.timelastmodified IS 'For handling object +accession or modification timestamps (as opposed to database auditing data, +handled elsewhere). The expectation is that these fields would be +available to software interacting with chado.'; + +--- COMMENT ON INDEX feature_c1 IS 'Any feature can be globally identified +--- by the combination of organism, uniquename and feature type'; + +-- ================================================ +-- TABLE: featureloc +-- ================================================ + +create table featureloc ( + featureloc_id serial not null, + primary key (featureloc_id), + feature_id int not null, + foreign key (feature_id) references feature (feature_id) on delete cascade INITIALLY DEFERRED, + srcfeature_id int, + foreign key (srcfeature_id) references feature (feature_id) on delete set null INITIALLY DEFERRED, + fmin int, + is_fmin_partial boolean not null default 'false', + fmax int, + is_fmax_partial boolean not null default 'false', + strand smallint, + phase int, + residue_info text, + locgroup int not null default 0, + rank int not null default 0, + constraint featureloc_c1 unique (feature_id,locgroup,rank), + constraint featureloc_c2 check (fmin <= fmax) +); +create index featureloc_idx1 on featureloc (feature_id); +create index featureloc_idx2 on featureloc (srcfeature_id); +create index featureloc_idx3 on featureloc (srcfeature_id,fmin,fmax); + +COMMENT ON TABLE featureloc IS 'The location of a feature relative to +another feature. Important: interbase coordinates are used. This is +vital as it allows us to represent zero-length features e.g. splice +sites, insertion points without an awkward fuzzy system. Features +typically... [truncated message content] |
From: <sco...@us...> - 2011-08-03 17:50:47
|
Revision: 25109 http://gmod.svn.sourceforge.net/gmod/?rev=25109&view=rev Author: scottcain Date: 2011-08-03 17:50:36 +0000 (Wed, 03 Aug 2011) Log Message: ----------- building make-based update mechanism; scaffolding there, but it doesn't do anything yet Modified Paths: -------------- schema/trunk/chado/Makefile.PL schema/trunk/chado/lib/Bio/Chado/Builder.pm Modified: schema/trunk/chado/Makefile.PL =================================================================== --- schema/trunk/chado/Makefile.PL 2011-08-03 16:48:48 UTC (rev 25108) +++ schema/trunk/chado/Makefile.PL 2011-08-03 17:50:36 UTC (rev 25109) @@ -494,6 +494,7 @@ 'bin/gmod_bulk_load_pubmed.pl', 'bin/gmod_make_cvtermpath.pl', 'bin/gmod_add_organism.pl', + 'bin/gmod_chado_properties.pl', # 'bin/gmod_bulkfiles.pl', # 'bin/gmod_gff2biomart5.pl', ); @@ -739,6 +740,9 @@ ontologies :: ./Build ontologies +update :: + ./Build update + rm_locks :: `find $LOCAL_TMP -name "*$DBNAME" -exec rm -f '{}' ';'` Modified: schema/trunk/chado/lib/Bio/Chado/Builder.pm =================================================================== --- schema/trunk/chado/lib/Bio/Chado/Builder.pm 2011-08-03 16:48:48 UTC (rev 25108) +++ schema/trunk/chado/lib/Bio/Chado/Builder.pm 2011-08-03 17:50:36 UTC (rev 25109) @@ -16,14 +16,20 @@ use LWP::Simple qw(mirror is_success status_message); use DBI; use IPC::Cmd (); +use Scalar::Util qw(looks_like_number); my $DEBUG = 0; my $go2fmt = IPC::Cmd::can_run('go2fmt') ? 'go2fmt' : 'go2fmt.pl'; #< detect new version of go2fmt =head1 ACTIONS -= item prepdb() +=item update() +Checks an existing default Chado schema to determine its +version and attempts to update it to the current release. + +=item prepdb() + Calls the psql command and pipes in the contents of the load/etc/initialize.sql file. Put any insert statements that your data load needs here. Also executes gmod_add_organism.pl @@ -74,7 +80,7 @@ my $db_port = $conf->{'database'}{'db_port'} || ''; my $db_user = $conf->{'database'}{'db_username'} || ''; my $build_dir = $conf->{'build'}{'working_dir'} || ''; - my $schema_version = $conf->{'build'}{'version'}; + my $schema_version = $m->dist_version; my $init_sql = catfile( $build_dir, 'load', 'etc', 'initialize.sql' ); my $sys_call = "psql -h $db_host -p $db_port -U $db_user -f $init_sql $db_name"; @@ -96,6 +102,43 @@ } +=head2 ACTION_update + + Title : ACTION_update + Usage : + Function: Attempts to update an existing Chado schema + Example : + Returns : + Args : + +=cut + +sub ACTION_update { # the build object $m + my $m = shift; + # the XML config object + my $conf = $m->conf; + + my $db_name = $conf->{'database'}{'db_name'} || ''; + my $db_host = $conf->{'database'}{'db_host'} || ''; + my $db_port = $conf->{'database'}{'db_port'} || ''; + my $db_user = $conf->{'database'}{'db_username'} || ''; + my $build_dir = $conf->{'build'}{'working_dir'} || ''; + my $schema_version = $m->dist_version; + + my $version = `gmod_chado_properties.pl --dbprofile $db_name --version`; + chomp $version; + + if (looks_like_number($version) and $version < $schema_version) { + print "Attempting schema update.\n"; + } + elsif (looks_like_number($version) and $version >= $schema_version) { + print "No update necessary.\n"; + } + else { + print "Unable to determine schema version; exiting...\n"; + } +} + =head2 ACTION_ncbi Title : ACTION_ncbi This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <sco...@us...> - 2011-08-31 20:18:04
|
Revision: 25164 http://gmod.svn.sourceforge.net/gmod/?rev=25164&view=rev Author: scottcain Date: 2011-08-31 20:17:50 +0000 (Wed, 31 Aug 2011) Log Message: ----------- multiple changes to make the schema updater work Modified Paths: -------------- schema/trunk/chado/Makefile.PL schema/trunk/chado/install_util/conf_install.PLS schema/trunk/chado/install_util/src_install.PLS schema/trunk/chado/lib/Bio/GMOD/Config.pm schema/trunk/chado/schemas/1.1-1.2/diff.sql schema/trunk/chado/schemas/1.11-1.2/diff.sql Added Paths: ----------- schema/trunk/chado/bin/gmod_update_chado.pl Modified: schema/trunk/chado/Makefile.PL =================================================================== --- schema/trunk/chado/Makefile.PL 2011-08-31 00:01:22 UTC (rev 25163) +++ schema/trunk/chado/Makefile.PL 2011-08-31 20:17:50 UTC (rev 25164) @@ -580,9 +580,10 @@ # TODO: this should be rewritten to use the metadata xml sub MY::postamble { my $root = "GMOD_ROOT=$O{GMOD_ROOT}"; + my $version = "VERSION=$VERSION"; qq{ conf_install :: - \$(PERL) \"$working_dir/install_util/conf_install.pl\" '$root' + \$(PERL) \"$working_dir/install_util/conf_install.pl\" '$root' '$version' src_install :: \$(PERL) \"$working_dir/install_util/src_install.pl\" '$root' Added: schema/trunk/chado/bin/gmod_update_chado.pl =================================================================== --- schema/trunk/chado/bin/gmod_update_chado.pl (rev 0) +++ schema/trunk/chado/bin/gmod_update_chado.pl 2011-08-31 20:17:50 UTC (rev 25164) @@ -0,0 +1,76 @@ +#!/usr/bin/perl -w +use strict; + +use Getopt::Long; +use lib '/home/cain/cvs_stuff/schema/chado/lib'; +use lib '/home/scott/cvs_stuff/schema/chado/lib'; +use Bio::GMOD::Config; +use Bio::GMOD::DB::Config; +use ExtUtils::MakeMaker; #to get prompt + +=head1 NAME + +$0 - updates the schema of a Chado database if necessary + +=head1 SYNOPSIS + + % gmod_update_chado.pl [options] + +=head1 COMMAND-LINE OPTIONS + + --force Update the schema without prompt + --dbprofile Which database profile to use for updating + +=head1 DESCRIPTION + +=head1 AUTHOR + +Scott Cain E<lt>sc...@cp...E<gt> + +Copyright (c) 2011 + +This library is free software; you can redistribute it and/or modify +it under the same terms as Perl itself. + +=cut + +my ($FORCE, $DBPROFILE, ); + +GetOptions( + 'force' => \$FORCE, + 'dbprofile=s' => \$DBPROFILE, +) or ( system( 'pod2text', $0 ), exit -1 ); + +$DBPROFILE ||= 'default'; + +my $gmod_conf = Bio::GMOD::Config->new(); +my $version = $gmod_conf->version(); +my $gmod_root = $gmod_conf->gmod_root(); +my $db_conf = Bio::GMOD::DB::Config->new($gmod_conf, $DBPROFILE); +my $dbh = $db_conf->dbh(); + +my $current_version = `gmod_chado_properties.pl --version --dbprof $DBPROFILE`; +chomp $current_version; + +if ($current_version >= $version) { + print "This instance of the Chado schema does not need updating.\n"; + exit(0); +} + +#build path to get updates from +my $path = "$gmod_root/src/chado/schemas/$current_version-$version/diff.sql"; +warn $path; + +my $dbuser = $db_conf->user; +my $dbport = $db_conf->port; +my $dbhost = $db_conf->host; +my $dbname = $db_conf->name; + +my $syscommand = "cat $path | psql -U $dbuser -p $dbport -h $dbhost $dbname"; +warn $syscommand; +system($syscommand) == 0 or die "failed updating database"; + +print "Updating $dbname complete.\n"; + +exit(0); + Property changes on: schema/trunk/chado/bin/gmod_update_chado.pl ___________________________________________________________________ Added: svn:executable + * Modified: schema/trunk/chado/install_util/conf_install.PLS =================================================================== --- schema/trunk/chado/install_util/conf_install.PLS 2011-08-31 00:01:22 UTC (rev 25163) +++ schema/trunk/chado/install_util/conf_install.PLS 2011-08-31 20:17:50 UTC (rev 25164) @@ -25,6 +25,7 @@ my %options = map {split /=/} @ARGV; my $gmod_root = $options{GMOD_ROOT}; +my $version = $options{VERSION}; my $confdir = "$gmod_root/conf"; my $tmpdir = "$gmod_root/tmp"; @@ -66,6 +67,7 @@ open GMODCONF, ">$gmodconf" or die "unable to open $gmodconf: $!"; print GMODCONF "CONF=$gmod_root/conf\n"; print GMODCONF "TMP=$gmod_root/tmp\n"; +print GMODCONF "VERSION=$version\n"; close GMODCONF; open DBCONF, ">$dbconf" or die "unable to open $dbconf: $!"; Modified: schema/trunk/chado/install_util/src_install.PLS =================================================================== --- schema/trunk/chado/install_util/src_install.PLS 2011-08-31 00:01:22 UTC (rev 25163) +++ schema/trunk/chado/install_util/src_install.PLS 2011-08-31 20:17:50 UTC (rev 25164) @@ -49,6 +49,7 @@ } copy_tree('modules',$src_dir); +copy_tree('schemas',$src_dir); chdir $origdir; !NO!SUBS! Modified: schema/trunk/chado/lib/Bio/GMOD/Config.pm =================================================================== --- schema/trunk/chado/lib/Bio/GMOD/Config.pm 2011-08-31 00:01:22 UTC (rev 25163) +++ schema/trunk/chado/lib/Bio/GMOD/Config.pm 2011-08-31 20:17:50 UTC (rev 25164) @@ -12,6 +12,7 @@ my $conf = Bio::GMOD::Config->new(); my $tmpdir = $conf->tmpdir(); my $confdir = $conf->confdir(); + my $version = $conf->version(); my @dbnames = $conf->available_dbs(); @@ -201,6 +202,24 @@ shift->{'confdir'}; } +=head2 version + + Title : version + Usage : $version = $config->version(); + Function: returns the version of the currently installed Chado software + Returns : see above + Args : none + Status : public + + +=cut + + +sub version { + shift->get_tag_value('VERSION');; +} + + =head2 tmp Title : tmp @@ -240,9 +259,9 @@ =head1 AUTHOR -Scott Cain E<lt>ca...@cs...E<gt>. +Scott Cain E<lt>sc...@cp...E<gt>. -Copyright (c) 2004 Cold Spring Harbor Laboratory +Copyright (c) 2011 Cold Spring Harbor Laboratory This library is free software; you can redistribute it and/or modify it under the same terms as Perl itself. Modified: schema/trunk/chado/schemas/1.1-1.2/diff.sql =================================================================== --- schema/trunk/chado/schemas/1.1-1.2/diff.sql 2011-08-31 00:01:22 UTC (rev 25163) +++ schema/trunk/chado/schemas/1.1-1.2/diff.sql 2011-08-31 20:17:50 UTC (rev 25164) @@ -42,12 +42,12 @@ properties that are single-valued rather than multi-valued, the default 0 value should be used.'; -ALTER TABLE gencode_startcodon ADD CONSTRAINT gencode_startcodon_unique UNIQUE( gencode_id, codon ); +ALTER TABLE genetic_code.gencode_startcodon ADD CONSTRAINT gencode_startcodon_unique UNIQUE( gencode_id, codon ); ALTER TABLE phenotype ADD COLUMN name TEXT default null; ALTER TABLE genotype ADD COLUMN type_id INT NOT NULL; -ALTER TABLE genotype ADD CONSTRAINT FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id) ON DELETE CASCADE; +ALTER TABLE genotype ADD CONSTRAINT genotype_type_id_fkey FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id) ON DELETE CASCADE; create table genotypeprop ( @@ -138,9 +138,7 @@ pub_id integer, FOREIGN KEY (pub_id) REFERENCES pub (pub_id) ON DELETE RESTRICT ); -< COMMENT ON TABLE stock_relationship_cvterm is 'For germplasm maintenance and pedigree data, stock_relationship. type_id will record cvterms such as "is a female parent of", "a parent for mutation", "is a group_id of", "is a source_id of", etc The cvterms for higher categories such as "generative", "derivative" or "maintenance" can be stored in table stock_relationship_cvterm'; -< -< +COMMENT ON TABLE stock_relationship_cvterm is 'For germplasm maintenance and pedigree data, stock_relationship. type_id will record cvterms such as "is a female parent of", "a parent for mutation", "is a group_id of", "is a source_id of", etc The cvterms for higher categories such as "generative", "derivative" or "maintenance" can be stored in table stock_relationship_cvterm'; alter table stock_cvterm add column is_not boolean not null default false; Modified: schema/trunk/chado/schemas/1.11-1.2/diff.sql =================================================================== --- schema/trunk/chado/schemas/1.11-1.2/diff.sql 2011-08-31 00:01:22 UTC (rev 25163) +++ schema/trunk/chado/schemas/1.11-1.2/diff.sql 2011-08-31 20:17:50 UTC (rev 25164) @@ -42,12 +42,12 @@ properties that are single-valued rather than multi-valued, the default 0 value should be used.'; -ALTER TABLE gencode_startcodon ADD CONSTRAINT gencode_startcodon_unique UNIQUE( gencode_id, codon ); +ALTER TABLE genetic_code.gencode_startcodon ADD CONSTRAINT gencode_startcodon_unique UNIQUE( gencode_id, codon ); ALTER TABLE phenotype ADD COLUMN name TEXT default null; ALTER TABLE genotype ADD COLUMN type_id INT NOT NULL; -ALTER TABLE genotype ADD CONSTRAINT FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id) ON DELETE CASCADE; +ALTER TABLE genotype ADD CONSTRAINT genotype_type_id_fkey FOREIGN KEY (type_id) REFERENCES cvterm (cvterm_id) ON DELETE CASCADE; create table genotypeprop ( @@ -138,9 +138,7 @@ pub_id integer, FOREIGN KEY (pub_id) REFERENCES pub (pub_id) ON DELETE RESTRICT ); -< COMMENT ON TABLE stock_relationship_cvterm is 'For germplasm maintenance and pedigree data, stock_relationship. type_id will record cvterms such as "is a female parent of", "a parent for mutation", "is a group_id of", "is a source_id of", etc The cvterms for higher categories such as "generative", "derivative" or "maintenance" can be stored in table stock_relationship_cvterm'; -< -< +COMMENT ON TABLE stock_relationship_cvterm is 'For germplasm maintenance and pedigree data, stock_relationship. type_id will record cvterms such as "is a female parent of", "a parent for mutation", "is a group_id of", "is a source_id of", etc The cvterms for higher categories such as "generative", "derivative" or "maintenance" can be stored in table stock_relationship_cvterm'; alter table stock_cvterm add column is_not boolean not null default false; This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <sco...@us...> - 2011-09-14 15:53:48
|
Revision: 25182 http://gmod.svn.sourceforge.net/gmod/?rev=25182&view=rev Author: scottcain Date: 2011-09-14 15:53:34 +0000 (Wed, 14 Sep 2011) Log Message: ----------- fixing the stock_relatiohship_id typo (pretty sure I caught all of them this time) Modified Paths: -------------- schema/trunk/chado/modules/audit/audit_tables.sql schema/trunk/chado/modules/default_nofuncs.sql schema/trunk/chado/modules/default_schema.sql schema/trunk/chado/modules/nofuncs.html schema/trunk/chado/modules/stock/stock.sql schema/trunk/chado/schemas/1.2/default_schema.sql Modified: schema/trunk/chado/modules/audit/audit_tables.sql =================================================================== --- schema/trunk/chado/modules/audit/audit_tables.sql 2011-09-08 16:45:33 UTC (rev 25181) +++ schema/trunk/chado/modules/audit/audit_tables.sql 2011-09-14 15:53:34 UTC (rev 25182) @@ -8609,7 +8609,7 @@ DROP TABLE audit_stock_relationship_cvterm; CREATE TABLE audit_stock_relationship_cvterm ( stock_relationship_cvterm_id integer, - stock_relatiohship_id integer, + stock_relationship_id integer, cvterm_id integer, pub_id integer, transaction_date timestamp not null default now(), @@ -8621,14 +8621,14 @@ ' DECLARE stock_relationship_cvterm_id_var integer; - stock_relatiohship_id_var integer; + stock_relationship_id_var integer; cvterm_id_var integer; pub_id_var integer; transaction_type_var char; BEGIN stock_relationship_cvterm_id_var = OLD.stock_relationship_cvterm_id; - stock_relatiohship_id_var = OLD.stock_relatiohship_id; + stock_relationship_id_var = OLD.stock_relationship_id; cvterm_id_var = OLD.cvterm_id; pub_id_var = OLD.pub_id; @@ -8640,13 +8640,13 @@ INSERT INTO audit_stock_relationship_cvterm ( stock_relationship_cvterm_id, - stock_relatiohship_id, + stock_relationship_id, cvterm_id, pub_id, transaction_type ) VALUES ( stock_relationship_cvterm_id_var, - stock_relatiohship_id_var, + stock_relationship_id_var, cvterm_id_var, pub_id_var, transaction_type_var Modified: schema/trunk/chado/modules/default_nofuncs.sql =================================================================== --- schema/trunk/chado/modules/default_nofuncs.sql 2011-09-08 16:45:33 UTC (rev 25181) +++ schema/trunk/chado/modules/default_nofuncs.sql 2011-09-14 15:53:34 UTC (rev 25182) @@ -3313,7 +3313,7 @@ CREATE TABLE stock_relationship_cvterm ( stock_relationship_cvterm_id SERIAL NOT NULL, PRIMARY KEY (stock_relationship_cvterm_id), - stock_relatiohship_id integer NOT NULL, + stock_relationship_id integer NOT NULL, FOREIGN KEY (stock_relationship_id) references stock_relationship (stock_relationship_id) ON DELETE CASCADE INITIALLY DEFERRED, cvterm_id integer NOT NULL, FOREIGN KEY (cvterm_id) REFERENCES cvterm (cvterm_id) ON DELETE RESTRICT, Modified: schema/trunk/chado/modules/default_schema.sql =================================================================== --- schema/trunk/chado/modules/default_schema.sql 2011-09-08 16:45:33 UTC (rev 25181) +++ schema/trunk/chado/modules/default_schema.sql 2011-09-14 15:53:34 UTC (rev 25182) @@ -40402,7 +40402,7 @@ CREATE TABLE stock_relationship_cvterm ( stock_relationship_cvterm_id SERIAL NOT NULL, PRIMARY KEY (stock_relationship_cvterm_id), - stock_relatiohship_id integer NOT NULL, + stock_relationship_id integer NOT NULL, FOREIGN KEY (stock_relationship_id) references stock_relationship (stock_relationship_id) ON DELETE CASCADE INITIALLY DEFERRED, cvterm_id integer NOT NULL, FOREIGN KEY (cvterm_id) REFERENCES cvterm (cvterm_id) ON DELETE RESTRICT, Modified: schema/trunk/chado/modules/nofuncs.html =================================================================== --- schema/trunk/chado/modules/nofuncs.html 2011-09-08 16:45:33 UTC (rev 25181) +++ schema/trunk/chado/modules/nofuncs.html 2011-09-14 15:53:34 UTC (rev 25182) @@ -24937,12 +24937,12 @@ </tr> <tr class="tr-even"> - <td class="FieldCellName"><a name="stock_relationship_cvterm-stock_relatiohship_id">stock_relatiohship_id</a></td> + <td class="FieldCellName"><a name="stock_relationship_cvterm-stock_relationship_id">stock_relationship_id</a></td> <td class="FieldCellType">integer</td> <td class="FieldCellSize">10</td> <td class="FieldCellDefault"></td> <td class="FieldCellOther">NOT NULL</td> - <td class="FieldCellFK"></td> + <td class="FieldCellFK"><a href="#stock_relationship-stock_relationship_id">stock_relationship.stock_relationship_id</a></td> </tr> <tr class="tr-odd"> @@ -24950,7 +24950,7 @@ <td class="FieldCellType">integer</td> <td class="FieldCellSize">10</td> <td class="FieldCellDefault"></td> - <td class="FieldCellOther">NOT NULL, FOREIGN KEY (stock_relationship_id) references stock_relationship (stock_relationship_id) ON DELETE CASCADE INITIALLY DEFERRED,</td> + <td class="FieldCellOther">NOT NULL</td> <td class="FieldCellFK"><a href="#cvterm-cvterm_id">cvterm.cvterm_id</a></td> </tr> @@ -24987,10 +24987,15 @@ <tr class="IndexCell"> <td>NOT NULL</td> - <td>stock_relatiohship_id</td> + <td>stock_relationship_id</td> </tr> <tr class="IndexCell"> + <td>FOREIGN KEY</td> + <td>stock_relationship_id</td> +</tr> + +<tr class="IndexCell"> <td>NOT NULL</td> <td>cvterm_id</td> </tr> Modified: schema/trunk/chado/modules/stock/stock.sql =================================================================== --- schema/trunk/chado/modules/stock/stock.sql 2011-09-08 16:45:33 UTC (rev 25181) +++ schema/trunk/chado/modules/stock/stock.sql 2011-09-14 15:53:34 UTC (rev 25182) @@ -144,7 +144,7 @@ CREATE TABLE stock_relationship_cvterm ( stock_relationship_cvterm_id SERIAL NOT NULL, PRIMARY KEY (stock_relationship_cvterm_id), - stock_relatiohship_id integer NOT NULL, + stock_relationship_id integer NOT NULL, FOREIGN KEY (stock_relationship_id) references stock_relationship (stock_relationship_id) ON DELETE CASCADE INITIALLY DEFERRED, cvterm_id integer NOT NULL, FOREIGN KEY (cvterm_id) REFERENCES cvterm (cvterm_id) ON DELETE RESTRICT, Modified: schema/trunk/chado/schemas/1.2/default_schema.sql =================================================================== --- schema/trunk/chado/schemas/1.2/default_schema.sql 2011-09-08 16:45:33 UTC (rev 25181) +++ schema/trunk/chado/schemas/1.2/default_schema.sql 2011-09-14 15:53:34 UTC (rev 25182) @@ -40402,7 +40402,7 @@ CREATE TABLE stock_relationship_cvterm ( stock_relationship_cvterm_id SERIAL NOT NULL, PRIMARY KEY (stock_relationship_cvterm_id), - stock_relatiohship_id integer NOT NULL, + stock_relationship_id integer NOT NULL, FOREIGN KEY (stock_relationship_id) references stock_relationship (stock_relationship_id) ON DELETE CASCADE INITIALLY DEFERRED, cvterm_id integer NOT NULL, FOREIGN KEY (cvterm_id) REFERENCES cvterm (cvterm_id) ON DELETE RESTRICT, This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <sco...@us...> - 2011-09-20 03:42:41
|
Revision: 25191 http://gmod.svn.sourceforge.net/gmod/?rev=25191&view=rev Author: scottcain Date: 2011-09-20 03:42:32 +0000 (Tue, 20 Sep 2011) Log Message: ----------- updating release materials Modified Paths: -------------- schema/trunk/chado/LICENSE schema/trunk/chado/MANIFEST schema/trunk/chado/MANIFEST.SKIP Modified: schema/trunk/chado/LICENSE =================================================================== --- schema/trunk/chado/LICENSE 2011-09-20 03:29:56 UTC (rev 25190) +++ schema/trunk/chado/LICENSE 2011-09-20 03:42:32 UTC (rev 25191) @@ -1,6 +1,6 @@ Artistic License 2.0 -Copyright (c) 2000-2006, The Perl Foundation. +Copyright (c) 2000-2011, The Perl Foundation. Everyone is permitted to copy and distribute verbatim copies of this license document, but changing it is not allowed. Modified: schema/trunk/chado/MANIFEST =================================================================== --- schema/trunk/chado/MANIFEST 2011-09-20 03:29:56 UTC (rev 25190) +++ schema/trunk/chado/MANIFEST 2011-09-20 03:42:32 UTC (rev 25191) @@ -5,24 +5,28 @@ bin/config.PLS bin/create-bridge-sql.pl bin/create-so-bridge.pl +bin/gencode2sql.pl bin/gmod_add_organism.pl +bin/gmod_apollo_triggers.pl bin/gmod_bulk_load_pubmed.pl -bin/gmod_load_cvterms.pl -bin/gmod_make_cvtermpath.pl -bin/load_ncbi_taxonomy.pl -bin/load_taxonomy_cvterms.pl -bin/dbi.tt2 -bin/ddltrans -bin/gencode2sql.pl -bin/gmod_apollo_triggers.pl +bin/gmod_bulkfiles.pl +bin/gmod_chado_properties.pl bin/gmod_dump_gff3.pl bin/gmod_extract_dbxref_from_gff.pl bin/gmod_fasta2gff3.pl +bin/gmod_gff2biomart5.pl bin/gmod_gff3_preprocessor.pl +bin/gmod_load_cvterms.pl +bin/gmod_make_cvtermpath.pl bin/gmod_make_gff_from_dbxref.pl bin/gmod_materialized_view_tool.pl bin/gmod_sort_gff3.pl +bin/gmod_update_chado.pl bin/interactions2SIF.pl +bin/load_ncbi_taxonomy.pl +bin/load_taxonomy_cvterms.pl +bin/make_cvtermpath.pl +bin/make_cvtermpath.sh bin/make_partial_indexes.pl bin/pg2cdbi.pl bin/pg2cdbi_viaTT.pl @@ -51,36 +55,47 @@ Changes Changes.GMOD Changes.sql -#chaos-xml/bin/cx-chadoxml2chaos.pl -#chaos-xml/bin/cx-chaos-report.pl -#chaos-xml/bin/cx-download-enscore.pl -#chaos-xml/bin/cx-enscore2chaos.pl -#chaos-xml/bin/cx-genbank2chaos.pl -#chaos-xml/create-manifest.sh -#chaos-xml/dtd/chaos.dtd -#chaos-xml/INSTALL -#chaos-xml/lib/Bio/Chaos/ChaosGraph.pm -#chaos-xml/lib/Bio/Chaos/FeatureUtil.pm -#chaos-xml/lib/Bio/Chaos/Root.pm -#chaos-xml/lib/Bio/Chaos/XSLTHelper.pm -#chaos-xml/Makefile.PL -#chaos-xml/MANIFEST -#chaos-xml/README -#chaos-xml/sample-data/AE003734.chaos.xml -#chaos-xml/sample-data/AE003734.gbk -##chaos-xml/sample-data/Rab1.chaos-xml -#chaos-xml/t/00parse_gb_to_chaos.t -#chaos-xml/t/02roundtrip.t -#chaos-xml/t/data/AE003734_modmdg4.gb -#chaos-xml/t/data/CG10833.with-macros.chado-xml -#chaos-xml/xsl/chado-expand-macros.xsl -#chaos-xml/xsl/chado-insert-macros.xsl -#chaos-xml/xsl/cx-chado-to-chaos.xsl -#chaos-xml/xsl/cx-chaos-to-dbxref-tbl.xsl -#chaos-xml/xsl/cx-chaos-to-feature-summary-tbl.xsl -#chaos-xml/xsl/cx-chaos-to-gff3.xsl -#chaos-xml/xsl/README -Class-DBI-Pg-0.06-multiple-schemata.patch +conf/bulkfiles/anogam.xml +conf/bulkfiles/blastfiles.xml +conf/bulkfiles/bulkfiles_template.xml +conf/bulkfiles/chadofeatconv.xml +conf/bulkfiles/chadofeatsql.xml +conf/bulkfiles/chadogenepagesql.xml +conf/bulkfiles/dmelhetfeatconv.xml +conf/bulkfiles/dmelr420.xml +conf/bulkfiles/dmelr430.xml +conf/bulkfiles/dpsebulk-p4.xml +conf/bulkfiles/dpsebulk-p5.xml +conf/bulkfiles/dpsebulk-r2.xml +conf/bulkfiles/drosmelgb.xml +conf/bulkfiles/fastawriter.xml +conf/bulkfiles/fbbulk-hetr3.xml +conf/bulkfiles/fbbulk-r3.xml +conf/bulkfiles/fbbulk-r3h.xml +conf/bulkfiles/fbbulk-r4.xml +conf/bulkfiles/fbbulk-r41.xml +conf/bulkfiles/fbbulk-r411.xml +conf/bulkfiles/fbreleases.xml +conf/bulkfiles/featuresets.xml +conf/bulkfiles/filesets.xml +conf/bulkfiles/gbrowseconf.xml +conf/bulkfiles/gbrowseconf_fb.xml +conf/bulkfiles/genbanksubmit.xml +conf/bulkfiles/genomeweb.xml +conf/bulkfiles/organisms.xml +conf/bulkfiles/sgdbulk.xml +conf/bulkfiles/sgdbulk1.xml +conf/bulkfiles/sgdfeatconf.xml +conf/bulkfiles/site_defaults.xml +conf/bulkfiles/site_eugenes_defaults.xml +conf/bulkfiles/spbase.xml +conf/bulkfiles/spbasefeatconf.xml +conf/bulkfiles/tablewriter.xml +conf/bulkfiles/toacode.xml +conf/bulkfiles/tognomap.xml +conf/chado2apollo-apache.conf +doc/about-gff2biomart.pod +doc/COPYRIGHT doc/css/chado.css doc/examples/dicistronic-gene-example.chado doc/examples/dicistronic-gene-example.chaos @@ -90,7 +105,11 @@ doc/examples/dmel_NPFR1.chaos doc/examples/dmel_NPFR1.game doc/examples/dmel_NPFR1.png +doc/gff2biomart-update.note +doc/gmod-tools-readme.pod doc/index.html +doc/wiki/generateChadoWikiTables.py +doc/wiki/wiki.tmpl doc/xsl/scenario-to-html-inline.xsl doc/xsl/scenario-to-html.xsl INSTALL.Chado @@ -100,8 +119,22 @@ install_util/src_install.PLS lib/Bio/Chado/Builder.pm lib/Bio/Chado/Config.pm -lib/Bio/Chado/LoadDBI.pm +lib/Bio/FeatureIO/chado.pm +lib/Bio/FeatureIO/chadobulk.pm +lib/Bio/GMOD/Bulkfiles.pm +lib/Bio/GMOD/Bulkfiles/AcodeWriter.pm +lib/Bio/GMOD/Bulkfiles/BlastWriter.pm +lib/Bio/GMOD/Bulkfiles/BulkWriter.pm +lib/Bio/GMOD/Bulkfiles/FastaWriter.pm +lib/Bio/GMOD/Bulkfiles/FeatureWriter.pm +lib/Bio/GMOD/Bulkfiles/GenbankSubmitWriter.pm +lib/Bio/GMOD/Bulkfiles/GnomapWriter.pm +lib/Bio/GMOD/Bulkfiles/MyLargePrimarySeq.pm +lib/Bio/GMOD/Bulkfiles/MySplitLocation.pm +lib/Bio/GMOD/Bulkfiles/SWISS_CRC64.pm +lib/Bio/GMOD/Bulkfiles/TableWriter.pm lib/Bio/GMOD/Config.pm +lib/Bio/GMOD/Config2.pm lib/Bio/GMOD/DB/Adapter.pm lib/Bio/GMOD/DB/Adapter/FeatureIterator.pm lib/Bio/GMOD/DB/Adapter/Wormbase.pm @@ -109,11 +142,13 @@ lib/Bio/GMOD/DB/Tools/ETA.pm lib/Bio/GMOD/Load.pm lib/Bio/GMOD/Load/GFF.pm +lib/Bio/GMOD/SeqUtils.pm LICENSE load/bin/bulk_load_gff3.PLS -#load/bin/load_gff3.PLS +load/bin/load_gff3.PLS load/Build.PL load/etc/chap.obo +load/etc/cv_property.obo load/etc/extra.sql load/etc/feature_property.obo load/etc/genbank_feature_property.obo @@ -121,7 +156,6 @@ load/etc/log.conf load/etc/README load/logs/load.log -load/tt2/load.conf.tt2 Makefile.PL MANIFEST This list of files MANIFEST.SKIP @@ -130,6 +164,7 @@ modules/audit/make_audit_ddl modules/bin/makedep.pl modules/bin/mk.pl +modules/cell_line/cell_line.html modules/cell_line/cell_line.sql modules/companalysis/companalysis.html modules/companalysis/companalysis.sql @@ -177,6 +212,9 @@ modules/mage/mage.views modules/map/map.html modules/map/map.sql +modules/module-tables.json +modules/natural_diversity/natural_diversity.html +modules/natural_diversity/natural_diversity.sql modules/nofuncs.html modules/nofuncs.sql modules/organism/examples/examples-orgs.chado-xml @@ -196,6 +234,8 @@ modules/phylogeny/functions/phylo.sqlapi modules/phylogeny/phylogeny.html modules/phylogeny/phylogeny.sql +modules/project/project.html +modules/project/project.sql modules/pub/pub.html modules/pub/pub.sql modules/sequence/apollo-bridge/ad_hoc_cv @@ -251,9 +291,19 @@ modules/sequence/views/range-views.sql modules/sequence/views/sequence-cv-views.sql modules/sequence/views/sequence-report.sql +modules/stock/stock.html modules/stock/stock.sql README README.Apollo +schemas/1-1.1/diff.sql +schemas/1-1.11/diff.sql +schemas/1-1.2/diff.sql +schemas/1.1-1.2/diff.sql +schemas/1.1/default_schema.sql +schemas/1.11-1.2/diff.sql +schemas/1.11/default_schema.sql +schemas/1.2/default_schema.sql +schemas/1/default_schema.sql soi/cgi/get_xml.pl soi/README soi/scripts/dump_fasta.pl @@ -295,3 +345,4 @@ stag-templates/README t/01_okay.t TODO +UPGRADE.txt Modified: schema/trunk/chado/MANIFEST.SKIP =================================================================== --- schema/trunk/chado/MANIFEST.SKIP 2011-09-20 03:29:56 UTC (rev 25190) +++ schema/trunk/chado/MANIFEST.SKIP 2011-09-20 03:42:32 UTC (rev 25191) @@ -7,6 +7,7 @@ \.old$ \.tmp$ \.svn +^tmp .*modules/.*\.png$ .*modules/.*\.svg$ Makefile$ This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <sco...@us...> - 2011-09-21 03:04:53
|
Revision: 25200 http://gmod.svn.sourceforge.net/gmod/?rev=25200&view=rev Author: scottcain Date: 2011-09-21 03:04:43 +0000 (Wed, 21 Sep 2011) Log Message: ----------- removing cell_type ontology from the auto list because it wasn't loading via stag_storenode.pl Modified Paths: -------------- schema/trunk/chado/Changes schema/trunk/chado/load/tt2/load.conf.tt2 Modified: schema/trunk/chado/Changes =================================================================== --- schema/trunk/chado/Changes 2011-09-20 19:34:21 UTC (rev 25199) +++ schema/trunk/chado/Changes 2011-09-21 03:04:43 UTC (rev 25200) @@ -21,6 +21,8 @@ values on the command line (like the schema version) (Scott) * Added gmod_update_chado.pl to update the schema automatically, and added a make target (make update) to make updating easier. (Scott) +* Removed cell_type ontology from 'make ontologies' list as it wasn't loading +via stag_storenode.pl at release time (Scott) Version 1.11 Tue May 25 11:10:47 EDT 2010 * fixed an issue with accessing the primary_id of Modified: schema/trunk/chado/load/tt2/load.conf.tt2 =================================================================== --- schema/trunk/chado/load/tt2/load.conf.tt2 2011-09-20 19:34:21 UTC (rev 25199) +++ schema/trunk/chado/load/tt2/load.conf.tt2 2011-09-21 03:04:43 UTC (rev 25200) @@ -80,12 +80,13 @@ </ontology> --> - +<!-- <ontology name="Cell Ontology" order="5"> <file type="obo" local="obo/anatomy/cell_type/cell.obo" remote="http://obo.cvs.sourceforge.net/obo/obo/ontology/anatomy/cell_type/cell.obo?rev=HEAD" method="lwp-mirror"/> </ontology> +--> - <ontology name="Plant Ontology" order="6"> + <ontology name="Plant Ontology" order="5"> <file type="obo" local="po/po_anatomy.obo" remote="http://palea.cgrb.oregonstate.edu/viewsvn/Poc/trunk/ontology/OBO_format/po_anatomy.obo?view=co" method="lwp-mirror"/> </ontology> This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <sco...@us...> - 2011-10-20 14:52:42
|
Revision: 25226 http://gmod.svn.sourceforge.net/gmod/?rev=25226&view=rev Author: scottcain Date: 2011-10-20 14:52:33 +0000 (Thu, 20 Oct 2011) Log Message: ----------- adding rudimentary support for non-public schema (really only good for Tripal at the moment) Modified Paths: -------------- schema/trunk/chado/Makefile.PL schema/trunk/chado/install_util/conf_install.PLS Modified: schema/trunk/chado/Makefile.PL =================================================================== --- schema/trunk/chado/Makefile.PL 2011-10-20 13:23:05 UTC (rev 25225) +++ schema/trunk/chado/Makefile.PL 2011-10-20 14:52:33 UTC (rev 25226) @@ -50,7 +50,7 @@ use Template; use Module::Build; -my $VERSION = 1.2; +my $VERSION = 1.21; my %args = ( DBDRIVER => { @@ -84,6 +84,9 @@ DEFAULT => { default => 'y' }, + SCHEMA => { + default => 'public' + }, ); my %env2arg = ( @@ -237,7 +240,7 @@ # Now figure out where the values are coming from -- build.conf, # command-line, or prompt(). # -my ( $DBDRIVER, $DBNAME, $DBUSER, $DBPASS, $DBHOST, $DBPORT, +my ( $DBDRIVER, $DBNAME, $DBUSER, $DBPASS, $DBHOST, $DBPORT,$SCHEMA, $SQLFILES, $EXTERNAL_DDL, $LOCAL_TMP, $DBORGANISM, $APOLLO, $DEFAULT); $O{'SIMPLE'} = prompt( @@ -263,6 +266,7 @@ $DBORGANISM=$conf{'DBORGANISM'}|| ''; $APOLLO = $conf{'APOLLP'} || ''; $DEFAULT = $conf{'DEFAULT'} || ''; + $SCHEMA = $conf{'SCHEMA'} || ''; } elsif ( $opts{'DEFAULTS'} ) { $DBDRIVER = $args{'DBDRIVER'}{'default'}; @@ -275,6 +279,7 @@ $DBORGANISM=$args{'DBORGANISM'}{'default'}; $APOLLO = $args{'APOLLO'}{'default'}; $DEFAULT = $args{'DEFAULT'}{'default'}; + $SCHEMA = $args{'SCHEMA'}{'default'}; } elsif ( %opts ) { $DBDRIVER = $opts{'DBDRIVER'} || ''; @@ -287,6 +292,7 @@ $DBORGANISM=$opts{'DBORGANISM'}|| ''; $APOLLO = $opts{'APOLLO'} || ''; $DEFAULT = $opts{'DEFAULT'} || ''; + $SCHEMA = $opts{'SCHEMA'} || ''; } if ( !$opts{'DEFAULTS'} && !$opts{'RECONFIGURE'} ) { @@ -329,6 +335,10 @@ $conf{'DBPORT'} || $args{'DBPORT'}{'default'} ); + $SCHEMA ||= prompt( 'What schema will Chado reside in?', + $conf{'SCHEMA'} || $args{'SCHEMA'}{'default'} + ); + $LOCAL_TMP ||= prompt( 'Where shall downloaded ontologies go?', $conf{'LOCAL_TMP'} || $args{'LOCAL_TMP'}{'default'} ); @@ -388,6 +398,7 @@ " DBPASS=$DBPASS", " DBHOST=$DBHOST", " DBPORT=$DBPORT", + " SCHEMA=$SCHEMA", " LOCAL_TMP=$LOCAL_TMP", " DBORGANISM=$DBORGANISM", " DEFAULT=$DEFAULT", @@ -410,6 +421,7 @@ $conf{'LIB'} = $opts{'LIB'}; $conf{'DEFAULT'} = $DEFAULT; $conf{'VERSION'} = $VERSION; + $conf{'SCHEMA'} = $SCHEMA; print CONF map { "$_=$conf{ $_ }\n" } keys %conf; close CONF or die "Can't write file '$build_config': $!\n"; Modified: schema/trunk/chado/install_util/conf_install.PLS =================================================================== --- schema/trunk/chado/install_util/conf_install.PLS 2011-10-20 13:23:05 UTC (rev 25225) +++ schema/trunk/chado/install_util/conf_install.PLS 2011-10-20 14:52:33 UTC (rev 25226) @@ -54,7 +54,7 @@ while (<LOCALCONF>) { chomp; my ($key, $value) = split /=/; - if ($key =~ /^DB/ or $key eq 'DEFAULT') { + if ($key =~ /^DB/ or $key eq 'DEFAULT' or $key eq 'SCHEMA') { $confitems{$key} = $value; } } This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <sco...@us...> - 2011-11-14 19:53:38
|
Revision: 25237 http://gmod.svn.sourceforge.net/gmod/?rev=25237&view=rev Author: scottcain Date: 2011-11-14 19:53:26 +0000 (Mon, 14 Nov 2011) Log Message: ----------- Fixed database initialization step "make prepdb" so that the version of the schema gets set in the chadoprop table. Modified Paths: -------------- schema/trunk/chado/Changes schema/trunk/chado/lib/Bio/Chado/Builder.pm Modified: schema/trunk/chado/Changes =================================================================== --- schema/trunk/chado/Changes 2011-11-14 17:07:28 UTC (rev 25236) +++ schema/trunk/chado/Changes 2011-11-14 19:53:26 UTC (rev 25237) @@ -1,6 +1,8 @@ Version 1.21 Thu Oct 20 10:52:55 EDT 2011 * Added rudimentary support for non-public schemas in Postgres, though it's only good for Tripal at the moment. +* Fixed database initialization step "make prepdb" so that the version of the +schema gets set in the chadoprop table. Version 1.2 Tue Aug 10 13:59:05 EDT 2010 * Added some error checking to gmod_fasta2gff3.pl (Rob) Modified: schema/trunk/chado/lib/Bio/Chado/Builder.pm =================================================================== --- schema/trunk/chado/lib/Bio/Chado/Builder.pm 2011-11-14 17:07:28 UTC (rev 25236) +++ schema/trunk/chado/lib/Bio/Chado/Builder.pm 2011-11-14 19:53:26 UTC (rev 25237) @@ -100,6 +100,9 @@ } } + #set the chado schema version in the database + system("gmod_chado_properties.pl --dbprofile $db_name --force --version $schema_version"); + } =head2 ACTION_update This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <sco...@us...> - 2011-11-15 18:23:14
|
Revision: 25238 http://gmod.svn.sourceforge.net/gmod/?rev=25238&view=rev Author: scottcain Date: 2011-11-15 18:22:56 +0000 (Tue, 15 Nov 2011) Log Message: ----------- removing "use lib" lines that refer to my home directories Modified Paths: -------------- schema/trunk/chado/bin/gmod_add_organism.pl schema/trunk/chado/bin/gmod_chado_properties.pl schema/trunk/chado/bin/gmod_gff3_preprocessor.pl schema/trunk/chado/bin/gmod_update_chado.pl schema/trunk/chado/load/bin/bulk_load_gff3.PLS schema/trunk/chado/load/bin/load_gff3.PLS Modified: schema/trunk/chado/bin/gmod_add_organism.pl =================================================================== --- schema/trunk/chado/bin/gmod_add_organism.pl 2011-11-14 19:53:26 UTC (rev 25237) +++ schema/trunk/chado/bin/gmod_add_organism.pl 2011-11-15 18:22:56 UTC (rev 25238) @@ -2,8 +2,8 @@ use strict; use Getopt::Long; -use lib '/home/cain/cvs_stuff/schema/chado/lib'; -use lib '/home/scott/cvs_stuff/schema/chado/lib'; +#use lib '/home/cain/cvs_stuff/schema/chado/lib'; +#use lib '/home/scott/cvs_stuff/schema/chado/lib'; use Bio::GMOD::Config; use Bio::GMOD::DB::Config; use Bio::Chado::Schema; Modified: schema/trunk/chado/bin/gmod_chado_properties.pl =================================================================== --- schema/trunk/chado/bin/gmod_chado_properties.pl 2011-11-14 19:53:26 UTC (rev 25237) +++ schema/trunk/chado/bin/gmod_chado_properties.pl 2011-11-15 18:22:56 UTC (rev 25238) @@ -2,8 +2,8 @@ use strict; use Getopt::Long; -use lib '/home/cain/cvs_stuff/schema/chado/lib'; -use lib '/home/scott/cvs_stuff/schema/chado/lib'; +#use lib '/home/cain/cvs_stuff/schema/chado/lib'; +#use lib '/home/scott/cvs_stuff/schema/chado/lib'; use Bio::GMOD::Config; use Bio::GMOD::DB::Config; use ExtUtils::MakeMaker; #to get prompt Modified: schema/trunk/chado/bin/gmod_gff3_preprocessor.pl =================================================================== --- schema/trunk/chado/bin/gmod_gff3_preprocessor.pl 2011-11-14 19:53:26 UTC (rev 25237) +++ schema/trunk/chado/bin/gmod_gff3_preprocessor.pl 2011-11-15 18:22:56 UTC (rev 25238) @@ -4,8 +4,8 @@ use Bio::FeatureIO; use Getopt::Long; use FileHandle; -use lib '/home/cain/cvs_stuff/schema/chado/lib'; -use lib '/home/scott/cvs_stuff/schema/chado/lib'; +#use lib '/home/cain/cvs_stuff/schema/chado/lib'; +#use lib '/home/scott/cvs_stuff/schema/chado/lib'; use Bio::GMOD::DB::Adapter; use Bio::GMOD::Config; use Bio::GMOD::DB::Config; Modified: schema/trunk/chado/bin/gmod_update_chado.pl =================================================================== --- schema/trunk/chado/bin/gmod_update_chado.pl 2011-11-14 19:53:26 UTC (rev 25237) +++ schema/trunk/chado/bin/gmod_update_chado.pl 2011-11-15 18:22:56 UTC (rev 25238) @@ -2,8 +2,8 @@ use strict; use Getopt::Long; -use lib '/home/cain/cvs_stuff/schema/chado/lib'; -use lib '/home/scott/cvs_stuff/schema/chado/lib'; +#use lib '/home/cain/cvs_stuff/schema/chado/lib'; +#use lib '/home/scott/cvs_stuff/schema/chado/lib'; use Bio::GMOD::Config; use Bio::GMOD::DB::Config; use ExtUtils::MakeMaker; #to get prompt Modified: schema/trunk/chado/load/bin/bulk_load_gff3.PLS =================================================================== --- schema/trunk/chado/load/bin/bulk_load_gff3.PLS 2011-11-14 19:53:26 UTC (rev 25237) +++ schema/trunk/chado/load/bin/bulk_load_gff3.PLS 2011-11-15 18:22:56 UTC (rev 25238) @@ -34,7 +34,7 @@ use strict; use warnings; -use lib '/Users/cain/cvs_stuff/schema/trunk/chado/lib'; +#use lib '/Users/cain/cvs_stuff/schema/trunk/chado/lib'; use Bio::FeatureIO; use Bio::SeqIO; use Getopt::Long; Modified: schema/trunk/chado/load/bin/load_gff3.PLS =================================================================== --- schema/trunk/chado/load/bin/load_gff3.PLS 2011-11-14 19:53:26 UTC (rev 25237) +++ schema/trunk/chado/load/bin/load_gff3.PLS 2011-11-15 18:22:56 UTC (rev 25238) @@ -34,7 +34,7 @@ print OUT <<'!NO!SUBS!'; use strict; use warnings; -use lib '/Users/cain/cvs_stuff/schema/trunk/chado/lib'; +#use lib '/Users/cain/cvs_stuff/schema/trunk/chado/lib'; use Bio::Tools::GFF; use Bio::SeqIO; use Bio::Chado::AutoDBI; This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <sco...@us...> - 2011-11-17 17:48:05
|
Revision: 25248 http://gmod.svn.sourceforge.net/gmod/?rev=25248&view=rev Author: scottcain Date: 2011-11-17 17:47:56 +0000 (Thu, 17 Nov 2011) Log Message: ----------- bumping the version to 1.22 Modified Paths: -------------- schema/trunk/chado/Makefile.PL schema/trunk/chado/load/Build.PL Modified: schema/trunk/chado/Makefile.PL =================================================================== --- schema/trunk/chado/Makefile.PL 2011-11-17 17:47:08 UTC (rev 25247) +++ schema/trunk/chado/Makefile.PL 2011-11-17 17:47:56 UTC (rev 25248) @@ -50,7 +50,7 @@ use Template; use Module::Build; -my $VERSION = 1.21; +my $VERSION = 1.22; my %args = ( DBDRIVER => { Modified: schema/trunk/chado/load/Build.PL =================================================================== --- schema/trunk/chado/load/Build.PL 2011-11-17 17:47:08 UTC (rev 25247) +++ schema/trunk/chado/load/Build.PL 2011-11-17 17:47:56 UTC (rev 25248) @@ -3,7 +3,7 @@ use Bio::Chado::Builder; use Data::Dumper; -my $VERSION = 1.21; +my $VERSION = 1.22; my $conf = shift; my $m = Bio::Chado::Builder->new( dist_name => 'Chado', This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <sco...@us...> - 2011-11-17 17:47:27
|
Revision: 25247 http://gmod.svn.sourceforge.net/gmod/?rev=25247&view=rev Author: scottcain Date: 2011-11-17 17:47:08 +0000 (Thu, 17 Nov 2011) Log Message: ----------- created a script to automatically create schmema diff dirs Modified Paths: -------------- schema/trunk/chado/MANIFEST Added Paths: ----------- schema/trunk/chado/schemas/1-1.22/ schema/trunk/chado/schemas/1-1.22/diff.sql schema/trunk/chado/schemas/1.1-1.22/ schema/trunk/chado/schemas/1.1-1.22/diff.sql schema/trunk/chado/schemas/1.11-1.22/ schema/trunk/chado/schemas/1.11-1.22/diff.sql schema/trunk/chado/schemas/1.2-1.22/ schema/trunk/chado/schemas/1.2-1.22/diff.sql schema/trunk/chado/schemas/1.21-1.22/ schema/trunk/chado/schemas/1.21-1.22/diff.sql schema/trunk/chado/schemas/1.22/ schema/trunk/chado/schemas/1.22/default_schema.sql schema/trunk/chado/schemas/generate_diff_dirs.pl Modified: schema/trunk/chado/MANIFEST =================================================================== --- schema/trunk/chado/MANIFEST 2011-11-16 18:52:59 UTC (rev 25246) +++ schema/trunk/chado/MANIFEST 2011-11-17 17:47:08 UTC (rev 25247) @@ -343,3 +343,22 @@ stag-templates/README TODO UPGRADE.txt +schemas/1-1.21 +schemas/1-1.21/diff.sql +schemas/1.1-1.21 +schemas/1.1-1.21/diff.sql +schemas/1.11-1.21 +schemas/1.11-1.21/diff.sql +schemas/1.2-1.21 +schemas/1.2-1.21/diff.sql +#the following added by generate_diff_dirs.pl +schemas/1-1.22 +schemas/1-1.22/diff.sql +schemas/1.1-1.22 +schemas/1.1-1.22/diff.sql +schemas/1.11-1.22 +schemas/1.11-1.22/diff.sql +schemas/1.2-1.22 +schemas/1.2-1.22/diff.sql +schemas/1.21-1.22 +schemas/1.21-1.22/diff.sql Added: schema/trunk/chado/schemas/1-1.22/diff.sql =================================================================== Added: schema/trunk/chado/schemas/1.1-1.22/diff.sql =================================================================== Added: schema/trunk/chado/schemas/1.11-1.22/diff.sql =================================================================== Added: schema/trunk/chado/schemas/1.2-1.22/diff.sql =================================================================== Added: schema/trunk/chado/schemas/1.21-1.22/diff.sql =================================================================== Added: schema/trunk/chado/schemas/1.22/default_schema.sql =================================================================== --- schema/trunk/chado/schemas/1.22/default_schema.sql (rev 0) +++ schema/trunk/chado/schemas/1.22/default_schema.sql 2011-11-17 17:47:08 UTC (rev 25247) @@ -0,0 +1,41889 @@ +-- $Id: general.sql,v 1.31 2007-03-01 02:45:54 briano Exp $ +-- ========================================== +-- Chado general module +-- +-- ================================================ +-- TABLE: tableinfo +-- ================================================ + +create table tableinfo ( + tableinfo_id serial not null, + primary key (tableinfo_id), + name varchar(30) not null, + primary_key_column varchar(30) null, + is_view int not null default 0, + view_on_table_id int null, + superclass_table_id int null, + is_updateable int not null default 1, + modification_date date not null default now(), + constraint tableinfo_c1 unique (name) +); + +COMMENT ON TABLE tableinfo IS NULL; + +-- ================================================ +-- TABLE: db +-- ================================================ + +create table db ( + db_id serial not null, + primary key (db_id), + name varchar(255) not null, +-- contact_id int, +-- foreign key (contact_id) references contact (contact_id) on delete cascade INITIALLY DEFERRED, + description varchar(255) null, + urlprefix varchar(255) null, + url varchar(255) null, + constraint db_c1 unique (name) +); + +COMMENT ON TABLE db IS 'A database authority. Typical databases in +bioinformatics are FlyBase, GO, UniProt, NCBI, MGI, etc. The authority +is generally known by this shortened form, which is unique within the +bioinformatics and biomedical realm. To Do - add support for URIs, +URNs (e.g. LSIDs). We can do this by treating the URL as a URI - +however, some applications may expect this to be resolvable - to be +decided.'; + +-- ================================================ +-- TABLE: dbxref +-- ================================================ + +create table dbxref ( + dbxref_id serial not null, + primary key (dbxref_id), + db_id int not null, + foreign key (db_id) references db (db_id) on delete cascade INITIALLY DEFERRED, + accession varchar(255) not null, + version varchar(255) not null default '', + description text, + constraint dbxref_c1 unique (db_id,accession,version) +); +create index dbxref_idx1 on dbxref (db_id); +create index dbxref_idx2 on dbxref (accession); +create index dbxref_idx3 on dbxref (version); + +COMMENT ON TABLE dbxref IS 'A unique, global, public, stable identifier. Not necessarily an external reference - can reference data items inside the particular chado instance being used. Typically a row in a table can be uniquely identified with a primary identifier (called dbxref_id); a table may also have secondary identifiers (in a linking table <T>_dbxref). A dbxref is generally written as <DB>:<ACCESSION> or as <DB>:<ACCESSION>:<VERSION>.'; + +COMMENT ON COLUMN dbxref.accession IS 'The local part of the identifier. Guaranteed by the db authority to be unique for that db.'; + +CREATE VIEW db_dbxref_count AS + SELECT db.name,count(*) AS num_dbxrefs FROM db INNER JOIN dbxref USING (db_id) GROUP BY db.name; +COMMENT ON VIEW db_dbxref_count IS 'per-db dbxref counts'; + +CREATE OR REPLACE FUNCTION store_db (VARCHAR) + RETURNS INT AS +'DECLARE + v_name ALIAS FOR $1; + + v_db_id INTEGER; + BEGIN + SELECT INTO v_db_id db_id + FROM db + WHERE name=v_name; + IF NOT FOUND THEN + INSERT INTO db + (name) + VALUES + (v_name); + RETURN currval(''db_db_id_seq''); + END IF; + RETURN v_db_id; + END; +' LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION store_dbxref (VARCHAR,VARCHAR) + RETURNS INT AS +'DECLARE + v_dbname ALIAS FOR $1; + v_accession ALIAS FOR $2; + + v_db_id INTEGER; + v_dbxref_id INTEGER; + BEGIN + SELECT INTO v_db_id + store_db(v_dbname); + SELECT INTO v_dbxref_id dbxref_id + FROM dbxref + WHERE db_id=v_db_id AND + accession=v_accession; + IF NOT FOUND THEN + INSERT INTO dbxref + (db_id,accession) + VALUES + (v_db_id,v_accession); + RETURN currval(''dbxref_dbxref_id_seq''); + END IF; + RETURN v_dbxref_id; + END; +' LANGUAGE 'plpgsql'; + +-- $Id: cv.sql,v 1.37 2007-02-28 15:08:48 briano Exp $ +-- ========================================== +-- Chado cv module +-- +-- ================================================================= +-- Dependencies: +-- +-- :import dbxref from general +-- ================================================================= + +-- ================================================ +-- TABLE: cv +-- ================================================ +create table cv ( + cv_id serial not null, + primary key (cv_id), + name varchar(255) not null, + definition text, + constraint cv_c1 unique (name) +); + +COMMENT ON TABLE cv IS 'A controlled vocabulary or ontology. A cv is +composed of cvterms (AKA terms, classes, types, universals - relations +and properties are also stored in cvterm) and the relationships +between them.'; + +COMMENT ON COLUMN cv.name IS 'The name of the ontology. This +corresponds to the obo-format -namespace-. cv names uniquely identify +the cv. In OBO file format, the cv.name is known as the namespace.'; + +COMMENT ON COLUMN cv.definition IS 'A text description of the criteria for +membership of this ontology.'; + +-- ================================================ +-- TABLE: cvterm +-- ================================================ +create table cvterm ( + cvterm_id serial not null, + primary key (cvterm_id), + cv_id int not null, + foreign key (cv_id) references cv (cv_id) on delete cascade INITIALLY DEFERRED, + name varchar(1024) not null, + definition text, + dbxref_id int not null, + foreign key (dbxref_id) references dbxref (dbxref_id) on delete set null INITIALLY DEFERRED, + is_obsolete int not null default 0, + is_relationshiptype int not null default 0, + constraint cvterm_c1 unique (name,cv_id,is_obsolete), + constraint cvterm_c2 unique (dbxref_id) +); +create index cvterm_idx1 on cvterm (cv_id); +create index cvterm_idx2 on cvterm (name); +create index cvterm_idx3 on cvterm (dbxref_id); + +COMMENT ON TABLE cvterm IS 'A term, class, universal or type within an +ontology or controlled vocabulary. This table is also used for +relations and properties. cvterms constitute nodes in the graph +defined by the collection of cvterms and cvterm_relationships.'; + +COMMENT ON COLUMN cvterm.cv_id IS 'The cv or ontology or namespace to which +this cvterm belongs.'; + +COMMENT ON COLUMN cvterm.name IS 'A concise human-readable name or +label for the cvterm. Uniquely identifies a cvterm within a cv.'; + +COMMENT ON COLUMN cvterm.definition IS 'A human-readable text +definition.'; + +COMMENT ON COLUMN cvterm.dbxref_id IS 'Primary identifier dbxref - The +unique global OBO identifier for this cvterm. Note that a cvterm may +have multiple secondary dbxrefs - see also table: cvterm_dbxref.'; + +COMMENT ON COLUMN cvterm.is_obsolete IS 'Boolean 0=false,1=true; see +GO documentation for details of obsoletion. Note that two terms with +different primary dbxrefs may exist if one is obsolete.'; + +COMMENT ON COLUMN cvterm.is_relationshiptype IS 'Boolean +0=false,1=true relations or relationship types (also known as Typedefs +in OBO format, or as properties or slots) form a cv/ontology in +themselves. We use this flag to indicate whether this cvterm is an +actual term/class/universal or a relation. Relations may be drawn from +the OBO Relations ontology, but are not exclusively drawn from there.'; + +COMMENT ON INDEX cvterm_c1 IS 'A name can mean different things in +different contexts; for example "chromosome" in SO and GO. A name +should be unique within an ontology or cv. A name may exist twice in a +cv, in both obsolete and non-obsolete forms - these will be for +different cvterms with different OBO identifiers; so GO documentation +for more details on obsoletion. Note that occasionally multiple +obsolete terms with the same name will exist in the same cv. If this +is a possibility for the ontology under consideration (e.g. GO) then the +ID should be appended to the name to ensure uniqueness.'; + +COMMENT ON INDEX cvterm_c2 IS 'The OBO identifier is globally unique.'; + +-- ================================================ +-- TABLE: cvterm_relationship +-- ================================================ +create table cvterm_relationship ( + cvterm_relationship_id serial not null, + primary key (cvterm_relationship_id), + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + subject_id int not null, + foreign key (subject_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + object_id int not null, + foreign key (object_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + constraint cvterm_relationship_c1 unique (subject_id,object_id,type_id) +); +create index cvterm_relationship_idx1 on cvterm_relationship (type_id); +create index cvterm_relationship_idx2 on cvterm_relationship (subject_id); +create index cvterm_relationship_idx3 on cvterm_relationship (object_id); + +COMMENT ON TABLE cvterm_relationship IS 'A relationship linking two +cvterms. Each cvterm_relationship constitutes an edge in the graph +defined by the collection of cvterms and cvterm_relationships. The +meaning of the cvterm_relationship depends on the definition of the +cvterm R refered to by type_id. However, in general the definitions +are such that the statement "all SUBJs REL some OBJ" is true. The +cvterm_relationship statement is about the subject, not the +object. For example "insect wing part_of thorax".'; + +COMMENT ON COLUMN cvterm_relationship.subject_id IS 'The subject of +the subj-predicate-obj sentence. The cvterm_relationship is about the +subject. In a graph, this typically corresponds to the child node.'; + +COMMENT ON COLUMN cvterm_relationship.object_id IS 'The object of the +subj-predicate-obj sentence. The cvterm_relationship refers to the +object. In a graph, this typically corresponds to the parent node.'; + +COMMENT ON COLUMN cvterm_relationship.type_id IS 'The nature of the +relationship between subject and object. Note that relations are also +housed in the cvterm table, typically from the OBO relationship +ontology, although other relationship types are allowed.'; + +-- ================================================ +-- TABLE: cvtermpath +-- ================================================ +create table cvtermpath ( + cvtermpath_id serial not null, + primary key (cvtermpath_id), + type_id int, + foreign key (type_id) references cvterm (cvterm_id) on delete set null INITIALLY DEFERRED, + subject_id int not null, + foreign key (subject_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + object_id int not null, + foreign key (object_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + cv_id int not null, + foreign key (cv_id) references cv (cv_id) on delete cascade INITIALLY DEFERRED, + pathdistance int, + constraint cvtermpath_c1 unique (subject_id,object_id,type_id,pathdistance) +); +create index cvtermpath_idx1 on cvtermpath (type_id); +create index cvtermpath_idx2 on cvtermpath (subject_id); +create index cvtermpath_idx3 on cvtermpath (object_id); +create index cvtermpath_idx4 on cvtermpath (cv_id); + +COMMENT ON TABLE cvtermpath IS 'The reflexive transitive closure of +the cvterm_relationship relation.'; + +COMMENT ON COLUMN cvtermpath.type_id IS 'The relationship type that +this is a closure over. If null, then this is a closure over ALL +relationship types. If non-null, then this references a relationship +cvterm - note that the closure will apply to both this relationship +AND the OBO_REL:is_a (subclass) relationship.'; + +COMMENT ON COLUMN cvtermpath.cv_id IS 'Closures will mostly be within +one cv. If the closure of a relationship traverses a cv, then this +refers to the cv of the object_id cvterm.'; + +COMMENT ON COLUMN cvtermpath.pathdistance IS 'The number of steps +required to get from the subject cvterm to the object cvterm, counting +from zero (reflexive relationship).'; + +-- ================================================ +-- TABLE: cvtermsynonym +-- ================================================ +create table cvtermsynonym ( + cvtermsynonym_id serial not null, + primary key (cvtermsynonym_id), + cvterm_id int not null, + foreign key (cvterm_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + synonym varchar(1024) not null, + type_id int, + foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + constraint cvtermsynonym_c1 unique (cvterm_id,synonym) +); +create index cvtermsynonym_idx1 on cvtermsynonym (cvterm_id); + +COMMENT ON TABLE cvtermsynonym IS 'A cvterm actually represents a +distinct class or concept. A concept can be refered to by different +phrases or names. In addition to the primary name (cvterm.name) there +can be a number of alternative aliases or synonyms. For example, "T +cell" as a synonym for "T lymphocyte".'; + +COMMENT ON COLUMN cvtermsynonym.type_id IS 'A synonym can be exact, +narrower, or broader than.'; + + +-- ================================================ +-- TABLE: cvterm_dbxref +-- ================================================ +create table cvterm_dbxref ( + cvterm_dbxref_id serial not null, + primary key (cvterm_dbxref_id), + cvterm_id int not null, + foreign key (cvterm_id) references cvterm (cvterm_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_for_definition int not null default 0, + constraint cvterm_dbxref_c1 unique (cvterm_id,dbxref_id) +); +create index cvterm_dbxref_idx1 on cvterm_dbxref (cvterm_id); +create index cvterm_dbxref_idx2 on cvterm_dbxref (dbxref_id); + +COMMENT ON TABLE cvterm_dbxref IS 'In addition to the primary +identifier (cvterm.dbxref_id) a cvterm can have zero or more secondary +identifiers/dbxrefs, which may refer to records in external +databases. The exact semantics of cvterm_dbxref are not fixed. For +example: the dbxref could be a pubmed ID that is pertinent to the +cvterm, or it could be an equivalent or similar term in another +ontology. For example, GO cvterms are typically linked to InterPro +IDs, even though the nature of the relationship between them is +largely one of statistical association. The dbxref may be have data +records attached in the same database instance, or it could be a +"hanging" dbxref pointing to some external database. NOTE: If the +desired objective is to link two cvterms together, and the nature of +the relation is known and holds for all instances of the subject +cvterm then consider instead using cvterm_relationship together with a +well-defined relation.'; + +COMMENT ON COLUMN cvterm_dbxref.is_for_definition IS 'A +cvterm.definition should be supported by one or more references. If +this column is true, the dbxref is not for a term in an external database - +it is a dbxref for provenance information for the definition.'; + + +-- ================================================ +-- TABLE: cvtermprop +-- ================================================ +create table cvtermprop ( + cvtermprop_id serial not null, + primary key (cvtermprop_id), + cvterm_id int not null, + foreign key (cvterm_id) references cvterm (cvterm_id) on delete cascade, + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) on delete cascade, + value text not null default '', + rank int not null default 0, + + unique(cvterm_id, type_id, value, rank) +); +create index cvtermprop_idx1 on cvtermprop (cvterm_id); +create index cvtermprop_idx2 on cvtermprop (type_id); + +COMMENT ON TABLE cvtermprop IS 'Additional extensible properties can be attached to a cvterm using this table. Corresponds to -AnnotationProperty- in W3C OWL format.'; + +COMMENT ON COLUMN cvtermprop.type_id IS 'The name of the property or slot is a cvterm. The meaning of the property is defined in that cvterm.'; + +COMMENT ON COLUMN cvtermprop.value IS 'The value of the property, represented as text. Numeric values are converted to their text representation.'; + +COMMENT ON COLUMN cvtermprop.rank IS 'Property-Value ordering. Any +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: dbxrefprop +-- ================================================ +create table dbxrefprop ( + dbxrefprop_id serial not null, + primary key (dbxrefprop_id), + dbxref_id int not null, + foreign key (dbxref_id) references dbxref (dbxref_id) INITIALLY DEFERRED, + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) INITIALLY DEFERRED, + value text not null default '', + rank int not null default 0, + constraint dbxrefprop_c1 unique (dbxref_id,type_id,rank) +); +create index dbxrefprop_idx1 on dbxrefprop (dbxref_id); +create index dbxrefprop_idx2 on dbxrefprop (type_id); + +COMMENT ON TABLE dbxrefprop IS 'Metadata about a dbxref. Note that this is not defined in the dbxref module, as it depends on the cvterm table. This table has a structure analagous to cvtermprop.'; + + +-- ================================================ +-- TABLE: cvprop +-- ================================================ +create table cvprop ( + cvprop_id serial not null, + primary key (cvprop_id), + cv_id int not null, + foreign key (cv_id) references cv (cv_id) INITIALLY DEFERRED, + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) INITIALLY DEFERRED, + value text, + rank int not null default 0, + constraint cvprop_c1 unique (cv_id,type_id,rank) +); + +COMMENT ON TABLE cvprop IS 'Additional extensible properties can be attached to a cv using this table. A notable example would be the cv version'; + +COMMENT ON COLUMN cvprop.type_id IS 'The name of the property or slot is a cvterm. The meaning of the property is defined in that cvterm.'; +COMMENT ON COLUMN cvprop.value IS 'The value of the property, represented as text. Numeric values are converted to their text representation.'; + +COMMENT ON COLUMN cvprop.rank IS 'Property-Value ordering. Any +cv 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: chadoprop +-- ================================================ +create table chadoprop ( + chadoprop_id serial not null, + primary key (chadoprop_id), + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) INITIALLY DEFERRED, + value text, + rank int not null default 0, + constraint chadoprop_c1 unique (type_id,rank) +); + +COMMENT ON TABLE chadoprop IS 'This table is different from other prop tables in the database, as it is for storing information about the database itself, like schema version'; + +COMMENT ON COLUMN chadoprop.type_id IS 'The name of the property or slot is a cvterm. The meaning of the property is defined in that cvterm.'; +COMMENT ON COLUMN chadoprop.value IS 'The value of the property, represented as text. Numeric values are converted to their text representation.'; + +COMMENT ON COLUMN chadoprop.rank IS 'Property-Value ordering. Any +cv 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.'; + +CREATE OR REPLACE VIEW cv_root AS + SELECT + cv_id, + cvterm_id AS root_cvterm_id + FROM cvterm + WHERE + cvterm_id NOT IN ( SELECT subject_id FROM cvterm_relationship) AND + is_obsolete=0; + +COMMENT ON VIEW cv_root IS 'the roots of a cv are the set of terms +which have no parents (terms that are not the subject of a +relation). Most cvs will have a single root, some may have >1. All +will have at least 1'; + +CREATE OR REPLACE VIEW cv_leaf AS + SELECT + cv_id, + cvterm_id + FROM cvterm + WHERE + cvterm_id NOT IN ( SELECT object_id FROM cvterm_relationship); + +COMMENT ON VIEW cv_leaf IS 'the leaves of a cv are the set of terms +which have no children (terms that are not the object of a +relation). All cvs will have at least 1 leaf'; + +CREATE OR REPLACE VIEW common_ancestor_cvterm AS + SELECT + p1.subject_id AS cvterm1_id, + p2.subject_id AS cvterm2_id, + p1.object_id AS ancestor_cvterm_id, + p1.pathdistance AS pathdistance1, + p2.pathdistance AS pathdistance2, + p1.pathdistance + p2.pathdistance + AS total_pathdistance + FROM + cvtermpath AS p1, + cvtermpath AS p2 + WHERE + p1.object_id = p2.object_id; + +COMMENT ON VIEW common_ancestor_cvterm IS 'The common ancestor of any +two terms is the intersection of both terms ancestors. Two terms can +have multiple common ancestors. Use total_pathdistance to get the +least common ancestor'; + +CREATE OR REPLACE VIEW common_descendant_cvterm AS + SELECT + p1.object_id AS cvterm1_id, + p2.object_id AS cvterm2_id, + p1.subject_id AS ancestor_cvterm_id, + p1.pathdistance AS pathdistance1, + p2.pathdistance AS pathdistance2, + p1.pathdistance + p2.pathdistance + AS total_pathdistance + FROM + cvtermpath AS p1, + cvtermpath AS p2 + WHERE + p1.subject_id = p2.subject_id; + +COMMENT ON VIEW common_descendant_cvterm IS 'The common descendant of +any two terms is the intersection of both terms descendants. Two terms +can have multiple common descendants. Use total_pathdistance to get +the least common ancestor'; + +CREATE OR REPLACE VIEW stats_paths_to_root AS + SELECT + subject_id AS cvterm_id, + count(DISTINCT cvtermpath_id) AS total_paths, + avg(pathdistance) AS avg_distance, + min(pathdistance) AS min_distance, + max(pathdistance) AS max_distance + FROM cvtermpath INNER JOIN cv_root ON (object_id=root_cvterm_id) + GROUP BY cvterm_id; + +COMMENT ON VIEW stats_paths_to_root IS 'per-cvterm statistics on its +placement in the DAG relative to the root. There may be multiple paths +from any term to the root. This gives the total number of paths, and +the average minimum and maximum distances. Here distance is defined by +cvtermpath.pathdistance'; +CREATE VIEW cv_cvterm_count AS + SELECT cv.name,count(*) AS num_terms_excl_obs FROM cv INNER JOIN cvterm USING (cv_id) WHERE is_obsolete=0 GROUP BY cv.name; +COMMENT ON VIEW cv_cvterm_count IS 'per-cv terms counts (excludes obsoletes)'; + +CREATE VIEW cv_cvterm_count_with_obs AS + SELECT cv.name,count(*) AS num_terms_incl_obs FROM cv INNER JOIN cvterm USING (cv_id) GROUP BY cv.name; +COMMENT ON VIEW cv_cvterm_count_with_obs IS 'per-cv terms counts (includes obsoletes)'; + +CREATE VIEW cv_link_count AS + SELECT cv.name AS cv_name, + relation.name AS relation_name, + relation_cv.name AS relation_cv_name, + count(*) AS num_links + FROM cv + INNER JOIN cvterm ON (cvterm.cv_id=cv.cv_id) + INNER JOIN cvterm_relationship ON (cvterm.cvterm_id=subject_id) + INNER JOIN cvterm AS relation ON (type_id=relation.cvterm_id) + INNER JOIN cv AS relation_cv ON (relation.cv_id=relation_cv.cv_id) + GROUP BY cv.name,relation.name,relation_cv.name; + +COMMENT ON VIEW cv_link_count IS 'per-cv summary of number of +links (cvterm_relationships) broken down by +relationship_type. num_links is the total # of links of the specified +type in which the subject_id of the link is in the named cv'; + +CREATE VIEW cv_path_count AS + SELECT cv.name AS cv_name, + relation.name AS relation_name, + relation_cv.name AS relation_cv_name, + count(*) AS num_paths + FROM cv + INNER JOIN cvterm ON (cvterm.cv_id=cv.cv_id) + INNER JOIN cvtermpath ON (cvterm.cvterm_id=subject_id) + INNER JOIN cvterm AS relation ON (type_id=relation.cvterm_id) + INNER JOIN cv AS relation_cv ON (relation.cv_id=relation_cv.cv_id) + GROUP BY cv.name,relation.name,relation_cv.name; + +COMMENT ON VIEW cv_path_count IS 'per-cv summary of number of +paths (cvtermpaths) broken down by relationship_type. num_paths is the +total # of paths of the specified type in which the subject_id of the +path is in the named cv. See also: cv_distinct_relations'; + +CREATE OR REPLACE FUNCTION _get_all_subject_ids(integer) RETURNS SETOF cvtermpath AS +' +DECLARE + root alias for $1; + cterm cvtermpath%ROWTYPE; + cterm2 cvtermpath%ROWTYPE; +BEGIN + + FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = root LOOP + RETURN NEXT cterm; + FOR cterm2 IN SELECT * FROM _get_all_subject_ids(cterm.subject_id) LOOP + RETURN NEXT cterm2; + END LOOP; + END LOOP; + RETURN; +END; +' +LANGUAGE 'plpgsql'; + +---arg: parent term id +---return: all children term id and their parent term id with relationship type id +CREATE OR REPLACE FUNCTION get_all_subject_ids(integer) RETURNS SETOF cvtermpath AS +' +DECLARE + root alias for $1; + cterm cvtermpath%ROWTYPE; + exist_c int; +BEGIN + + SELECT INTO exist_c count(*) FROM cvtermpath WHERE object_id = root and pathdistance <= 0; + IF (exist_c > 0) THEN + FOR cterm IN SELECT * FROM cvtermpath WHERE object_id = root and pathdistance > 0 LOOP + RETURN NEXT cterm; + END LOOP; + ELSE + FOR cterm IN SELECT * FROM _get_all_subject_ids(root) LOOP + RETURN NEXT cterm; + END LOOP; + END IF; + RETURN; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION get_graph_below(integer) RETURNS SETOF cvtermpath AS +' +DECLARE + root alias for $1; + cterm cvtermpath%ROWTYPE; + cterm2 cvtermpath%ROWTYPE; + +BEGIN + + FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = root LOOP + RETURN NEXT cterm; + FOR cterm2 IN SELECT * FROM get_all_subject_ids(cterm.subject_id) LOOP + RETURN NEXT cterm2; + END LOOP; + END LOOP; + RETURN; +END; +' +LANGUAGE 'plpgsql'; + + +CREATE OR REPLACE FUNCTION get_graph_above(integer) RETURNS SETOF cvtermpath AS +' +DECLARE + leaf alias for $1; + cterm cvtermpath%ROWTYPE; + cterm2 cvtermpath%ROWTYPE; + +BEGIN + + FOR cterm IN SELECT * FROM cvterm_relationship WHERE subject_id = leaf LOOP + RETURN NEXT cterm; + FOR cterm2 IN SELECT * FROM get_all_object_ids(cterm.object_id) LOOP + RETURN NEXT cterm2; + END LOOP; + END LOOP; + RETURN; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION _get_all_object_ids(integer) RETURNS SETOF cvtermpath AS +' +DECLARE + leaf alias for $1; + cterm cvtermpath%ROWTYPE; + cterm2 cvtermpath%ROWTYPE; +BEGIN + + FOR cterm IN SELECT * FROM cvterm_relationship WHERE subject_id = leaf LOOP + RETURN NEXT cterm; + FOR cterm2 IN SELECT * FROM _get_all_object_ids(cterm.object_id) LOOP + RETURN NEXT cterm2; + END LOOP; + END LOOP; + RETURN; +END; +' +LANGUAGE 'plpgsql'; + +---arg: child term id +---return: all parent term id and their childrent term id with relationship type id +CREATE OR REPLACE FUNCTION get_all_object_ids(integer) RETURNS SETOF cvtermpath AS +' +DECLARE + leaf alias for $1; + cterm cvtermpath%ROWTYPE; + exist_c int; +BEGIN + + + SELECT INTO exist_c count(*) FROM cvtermpath WHERE object_id = leaf and pathdistance <= 0; + IF (exist_c > 0) THEN + FOR cterm IN SELECT * FROM cvtermpath WHERE subject_id = leaf AND pathdistance > 0 LOOP + RETURN NEXT cterm; + END LOOP; + ELSE + FOR cterm IN SELECT * FROM _get_all_object_ids(leaf) LOOP + RETURN NEXT cterm; + END LOOP; + END IF; + RETURN; +END; +' +LANGUAGE 'plpgsql'; + +---arg: sql statement which must be in the form of select cvterm_id from ... +---return: a set of cvterm ids that includes what is in sql statement and their children (subject ids) +CREATE OR REPLACE FUNCTION get_it_sub_cvterm_ids(text) RETURNS SETOF cvterm AS +' +DECLARE + query alias for $1; + cterm cvterm%ROWTYPE; + cterm2 cvterm%ROWTYPE; +BEGIN + FOR cterm IN EXECUTE query LOOP + RETURN NEXT cterm; + FOR cterm2 IN SELECT subject_id as cvterm_id FROM get_all_subject_ids(cterm.cvterm_id) LOOP + RETURN NEXT cterm2; + END LOOP; + END LOOP; + RETURN; +END; +' +LANGUAGE 'plpgsql'; +--- example: select * from fill_cvtermpath(7); where 7 is cv_id for an ontology +--- fill path from the node to its children and their children +CREATE OR REPLACE FUNCTION _fill_cvtermpath4node(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) RETURNS INTEGER AS +' +DECLARE + origin alias for $1; + child_id alias for $2; + cvid alias for $3; + typeid alias for $4; + depth alias for $5; + cterm cvterm_relationship%ROWTYPE; + exist_c int; + +BEGIN + + --- RAISE NOTICE ''depth=% root=%'', depth,child_id; + --- not check type_id as it may be null and not very meaningful in cvtermpath when pathdistance > 1 + SELECT INTO exist_c count(*) FROM cvtermpath WHERE cv_id = cvid AND object_id = origin AND subject_id = child_id AND pathdistance = depth; + + IF (exist_c = 0) THEN + INSERT INTO cvtermpath (object_id, subject_id, cv_id, type_id, pathdistance) VALUES(origin, child_id, cvid, typeid, depth); + END IF; + FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = child_id LOOP + PERFORM _fill_cvtermpath4node(origin, cterm.subject_id, cvid, cterm.type_id, depth+1); + END LOOP; + RETURN 1; +END; +' +LANGUAGE 'plpgsql'; + + +CREATE OR REPLACE FUNCTION _fill_cvtermpath4root(INTEGER, INTEGER) RETURNS INTEGER AS +' +DECLARE + rootid alias for $1; + cvid alias for $2; + ttype int; + cterm cvterm_relationship%ROWTYPE; + child cvterm_relationship%ROWTYPE; + +BEGIN + + SELECT INTO ttype cvterm_id FROM cvterm WHERE (name = ''isa'' OR name = ''is_a''); + PERFORM _fill_cvtermpath4node(rootid, rootid, cvid, ttype, 0); + FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = rootid LOOP + PERFORM _fill_cvtermpath4root(cterm.subject_id, cvid); + -- RAISE NOTICE ''DONE for term, %'', cterm.subject_id; + END LOOP; + RETURN 1; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION fill_cvtermpath(INTEGER) RETURNS INTEGER AS +' +DECLARE + cvid alias for $1; + root cvterm%ROWTYPE; + +BEGIN + + DELETE FROM cvtermpath WHERE cv_id = cvid; + + FOR root IN SELECT DISTINCT t.* from cvterm t LEFT JOIN cvterm_relationship r ON (t.cvterm_id = r.subject_id) INNER JOIN cvterm_relationship r2 ON (t.cvterm_id = r2.object_id) WHERE t.cv_id = cvid AND r.subject_id is null LOOP + PERFORM _fill_cvtermpath4root(root.cvterm_id, root.cv_id); + END LOOP; + RETURN 1; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION fill_cvtermpath(cv.name%TYPE) RETURNS INTEGER AS +' +DECLARE + cvname alias for $1; + cv_id int; + rtn int; +BEGIN + + SELECT INTO cv_id cv.cv_id from cv WHERE cv.name = cvname; + SELECT INTO rtn fill_cvtermpath(cv_id); + RETURN rtn; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION _fill_cvtermpath4node2detect_cycle(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) RETURNS INTEGER AS +' +DECLARE + origin alias for $1; + child_id alias for $2; + cvid alias for $3; + typeid alias for $4; + depth alias for $5; + cterm cvterm_relationship%ROWTYPE; + exist_c int; + ccount int; + ecount int; + rtn int; +BEGIN + + EXECUTE ''SELECT * FROM tmpcvtermpath p1, tmpcvtermpath p2 WHERE p1.subject_id=p2.object_id AND p1.object_id=p2.subject_id AND p1.object_id = ''|| origin || '' AND p2.subject_id = '' || child_id || ''AND '' || depth || ''> 0''; + GET DIAGNOSTICS ccount = ROW_COUNT; + IF (ccount > 0) THEN + --RAISE EXCEPTION ''FOUND CYCLE: node % on cycle path'',origin; + RETURN origin; + END IF; + + EXECUTE ''SELECT * FROM tmpcvtermpath WHERE cv_id = '' || cvid || '' AND object_id = '' || origin || '' AND subject_id = '' || child_id || '' AND '' || origin || ''<>'' || child_id; + GET DIAGNOSTICS ecount = ROW_COUNT; + IF (ecount > 0) THEN + --RAISE NOTICE ''FOUND TWICE (node), will check root obj % subj %'',origin, child_id; + SELECT INTO rtn _fill_cvtermpath4root2detect_cycle(child_id, cvid); + IF (rtn > 0) THEN + RETURN rtn; + END IF; + END IF; + + EXECUTE ''SELECT * FROM tmpcvtermpath WHERE cv_id = '' || cvid || '' AND object_id = '' || origin || '' AND subject_id = '' || child_id || '' AND pathdistance = '' || depth; + GET DIAGNOSTICS exist_c = ROW_COUNT; + IF (exist_c = 0) THEN + EXECUTE ''INSERT INTO tmpcvtermpath (object_id, subject_id, cv_id, type_id, pathdistance) VALUES('' || origin || '', '' || child_id || '', '' || cvid || '', '' || typeid || '', '' || depth || '')''; + END IF; + + FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = child_id LOOP + --RAISE NOTICE ''DOING for node, % %'', origin, cterm.subject_id; + SELECT INTO rtn _fill_cvtermpath4node2detect_cycle(origin, cterm.subject_id, cvid, cterm.type_id, depth+1); + IF (rtn > 0) THEN + RETURN rtn; + END IF; + END LOOP; + RETURN 0; +END; +' +LANGUAGE 'plpgsql'; + + +CREATE OR REPLACE FUNCTION _fill_cvtermpath4root2detect_cycle(INTEGER, INTEGER) RETURNS INTEGER AS +' +DECLARE + rootid alias for $1; + cvid alias for $2; + ttype int; + ccount int; + cterm cvterm_relationship%ROWTYPE; + child cvterm_relationship%ROWTYPE; + rtn int; +BEGIN + + SELECT INTO ttype cvterm_id FROM cvterm WHERE (name = ''isa'' OR name = ''is_a''); + SELECT INTO rtn _fill_cvtermpath4node2detect_cycle(rootid, rootid, cvid, ttype, 0); + IF (rtn > 0) THEN + RETURN rtn; + END IF; + FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = rootid LOOP + EXECUTE ''SELECT * FROM tmpcvtermpath p1, tmpcvtermpath p2 WHERE p1.subject_id=p2.object_id AND p1.object_id=p2.subject_id AND p1.object_id='' || rootid || '' AND p1.subject_id='' || cterm.subject_id; + GET DIAGNOSTICS ccount = ROW_COUNT; + IF (ccount > 0) THEN + --RAISE NOTICE ''FOUND TWICE (root), will check root obj % subj %'',rootid,cterm.subject_id; + SELECT INTO rtn _fill_cvtermpath4node2detect_cycle(rootid, cterm.subject_id, cvid, ttype, 0); + IF (rtn > 0) THEN + RETURN rtn; + END IF; + ELSE + SELECT INTO rtn _fill_cvtermpath4root2detect_cycle(cterm.subject_id, cvid); + IF (rtn > 0) THEN + RETURN rtn; + END IF; + END IF; + END LOOP; + RETURN 0; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION get_cycle_cvterm_id(INTEGER, INTEGER) RETURNS INTEGER AS +' +DECLARE + cvid alias for $1; + rootid alias for $2; + rtn int; +BEGIN + + CREATE TEMP TABLE tmpcvtermpath(object_id int, subject_id int, cv_id int, type_id int, pathdistance int); + CREATE INDEX tmp_cvtpath1 ON tmpcvtermpath(object_id, subject_id); + + SELECT INTO rtn _fill_cvtermpath4root2detect_cycle(rootid, cvid); + IF (rtn > 0) THEN + DROP TABLE tmpcvtermpath; + RETURN rtn; + END IF; + DROP TABLE tmpcvtermpath; + RETURN 0; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION get_cycle_cvterm_ids(INTEGER) RETURNS SETOF INTEGER AS +' +DECLARE + cvid alias for $1; + root cvterm%ROWTYPE; + rtn int; +BEGIN + + + FOR root IN SELECT DISTINCT t.* from cvterm t WHERE cv_id = cvid LOOP + SELECT INTO rtn get_cycle_cvterm_id(cvid,root.cvterm_id); + IF (rtn > 0) THEN + RETURN NEXT rtn; + END IF; + END LOOP; + RETURN; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION get_cycle_cvterm_id(INTEGER) RETURNS INTEGER AS +' +DECLARE + cvid alias for $1; + root cvterm%ROWTYPE; + rtn int; +BEGIN + + CREATE TEMP TABLE tmpcvtermpath(object_id int, subject_id int, cv_id int, type_id int, pathdistance int); + CREATE INDEX tmp_cvtpath1 ON tmpcvtermpath(object_id, subject_id); + + FOR root IN SELECT DISTINCT t.* from cvterm t LEFT JOIN cvterm_relationship r ON (t.cvterm_id = r.subject_id) INNER JOIN cvterm_relationship r2 ON (t.cvterm_id = r2.object_id) WHERE t.cv_id = cvid AND r.subject_id is null LOOP + SELECT INTO rtn _fill_cvtermpath4root2detect_cycle(root.cvterm_id, root.cv_id); + IF (rtn > 0) THEN + DROP TABLE tmpcvtermpath; + RETURN rtn; + END IF; + END LOOP; + DROP TABLE tmpcvtermpath; + RETURN 0; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION get_cycle_cvterm_id(cv.name%TYPE) RETURNS INTEGER AS +' +DECLARE + cvname alias for $1; + cv_id int; + rtn int; +BEGIN + + SELECT INTO cv_id cv.cv_id from cv WHERE cv.name = cvname; + SELECT INTO rtn get_cycle_cvterm_id(cv_id); + + RETURN rtn; +END; +' +LANGUAGE 'plpgsql'; +-- $Id: pub.sql,v 1.27 2007-02-19 20:50:44 briano Exp $ +-- ========================================== +-- Chado pub module +-- +-- ================================================================= +-- Dependencies: +-- +-- :import cvterm from cv +-- :import dbxref from general +-- ================================================================= + +-- ================================================ +-- TABLE: pub +-- ================================================ + +create table pub ( + pub_id serial not null, + primary key (pub_id), + title text, + volumetitle text, + volume varchar(255), + series_name varchar(255), + issue varchar(255), + pyear varchar(255), + pages varchar(255), + miniref varchar(255), + uniquename text not null, + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + is_obsolete boolean default 'false', + publisher varchar(255), + pubplace varchar(255), + constraint pub_c1 unique (uniquename) +); +CREATE INDEX pub_idx1 ON pub (type_id); + +COMMENT ON TABLE pub IS 'A documented provenance artefact - publications, +documents, personal communication.'; +COMMENT ON COLUMN pub.title IS 'Descriptive general heading.'; +COMMENT ON COLUMN pub.volumetitle IS 'Title of part if one of a series.'; +COMMENT ON COLUMN pub.series_name IS 'Full name of (journal) series.'; +COMMENT ON COLUMN pub.pages IS 'Page number range[s], e.g. 457--459, viii + 664pp, lv--lvii.'; +COMMENT ON COLUMN pub.type_id IS 'The type of the publication (book, journal, poem, graffiti, etc). Uses pub cv.'; + +-- ================================================ +-- TABLE: pub_relationship +-- ================================================ + +create table pub_relationship ( + pub_relationship_id serial not null, + primary key (pub_relationship_id), + subject_id int not null, + foreign key (subject_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED, + object_id int not null, + foreign key (object_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED, + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + + constraint pub_relationship_c1 unique (subject_id,object_id,type_id) +); +create index pub_relationship_idx1 on pub_relationship (subject_id); +create index pub_relationship_idx2 on pub_relationship (object_id); +create index pub_relationship_idx3 on pub_relationship (type_id); + +COMMENT ON TABLE pub_relationship IS 'Handle relationships between +publications, e.g. when one publication makes others obsolete, when one +publication contains errata with respect to other publication(s), or +when one publication also appears in another pub.'; + +-- ================================================ +-- TABLE: pub_dbxref +-- ================================================ + +create table pub_dbxref ( + pub_dbxref_id serial not null, + primary key (pub_dbxref_id), + pub_id int not null, + foreign key (pub_id) references pub (pub_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 pub_dbxref_c1 unique (pub_id,dbxref_id) +); +create index pub_dbxref_idx1 on pub_dbxref (pub_id); +create index pub_dbxref_idx2 on pub_dbxref (dbxref_id); + +COMMENT ON TABLE pub_dbxref IS 'Handle links to repositories, +e.g. Pubmed, Biosis, zoorec, OCLC, Medline, ISSN, coden...'; + + +-- ================================================ +-- TABLE: pubauthor +-- ================================================ + +create table pubauthor ( + pubauthor_id serial not null, + primary key (pubauthor_id), + pub_id int not null, + foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED, + rank int not null, + editor boolean default 'false', + surname varchar(100) not null, + givennames varchar(100), + suffix varchar(100), + + constraint pubauthor_c1 unique (pub_id, rank) +); +create index pubauthor_idx2 on pubauthor (pub_id); + +COMMENT ON TABLE pubauthor IS 'An author for a publication. Note the denormalisation (hence lack of _ in table name) - this is deliberate as it is in general too hard to assign IDs to authors.'; +COMMENT ON COLUMN pubauthor.givennames IS 'First name, initials'; +COMMENT ON COLUMN pubauthor.suffix IS 'Jr., Sr., etc'; +COMMENT ON COLUMN pubauthor.rank IS 'Order of author in author list for this pub - order is important.'; +COMMENT ON COLUMN pubauthor.editor IS 'Indicates whether the author is an editor for linked publication. Note: this is a boolean field but does not follow the normal chado convention for naming booleans.'; + + +-- ================================================ +-- TABLE: pubprop +-- ================================================ + +create table pubprop ( + pubprop_id serial not null, + primary key (pubprop_id), + pub_id int not null, + foreign key (pub_id) references pub (pub_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, + rank integer, + + constraint pubprop_c1 unique (pub_id,type_id,rank) +); +create index pubprop_idx1 on pubprop (pub_id); +create index pubprop_idx2 on pubprop (type_id); + +COMMENT ON TABLE pubprop IS 'Property-value pairs for a pub. Follows standard chado pattern.'; +-- $Id: organism.sql,v 1.19 2007-04-01 18:45:41 briano Exp $ +-- ========================================== +-- Chado organism module +-- +-- ============ +-- DEPENDENCIES +-- ============ +-- :import cvterm from cv +-- :import dbxref from general +-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +-- ================================================ +-- TABLE: organism +-- ================================================ + +create table organism ( + organism_id serial not null, + primary key (organism_id), + abbreviation varchar(255) null, + genus varchar(255) not null, + species varchar(255) not null, + common_name varchar(255) null, + comment text null, + constraint organism_c1 unique (genus,species) +); + +COMMENT ON TABLE organism IS 'The organismal taxonomic +classification. Note that phylogenies are represented using the +phylogeny module, and taxonomies can be represented using the cvterm +module or the phylogeny module.'; + +COMMENT ON COLUMN organism.species IS 'A type of organism is always +uniquely identified by genus and species. When mapping from the NCBI +taxonomy names.dmp file, this column must be used where it +is present, as the common_name column is not always unique (e.g. environmental +samples). If a particular strain or subspecies is to be represented, +this is appended onto the species name. Follows standard NCBI taxonomy +pattern.'; + +-- ================================================ +-- TABLE: organism_dbxref +-- ================================================ + +create table organism_dbxref ( + organism_dbxref_id serial not null, + primary key (organism_dbxref_id), + organism_id int not null, + foreign key (organism_id) references organism (organism_id) on delete cascade INITIALLY DEFERRED, + dbxref_id int not null, + foreign key (dbxref_id) references dbxref (dbxref_id) on delete cascade INITIALLY DEFERRED, + constraint organism_dbxref_c1 unique (organism_id,dbxref_id) +); +create index organism_dbxref_idx1 on organism_dbxref (organism_id); +create index organism_dbxref_idx2 on organism_dbxref (dbxref_id); + +-- ================================================ +-- TABLE: organismprop +-- ================================================ + +create table organismprop ( + organismprop_id serial not null, + primary key (organismprop_id), + organism_id int not null, + foreign key (organism_id) references organism (organism_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 organismprop_c1 unique (organism_id,type_id,rank) +); +create index organismprop_idx1 on organismprop (organism_id); +create index organismprop_idx2 on organismprop (type_id); + +COMMENT ON TABLE organismprop IS 'Tag-value properties - follows standard chado model.'; + + +CREATE OR REPLACE FUNCTION get_organism_id(VARCHAR,VARCHAR) RETURNS INT + AS ' + SELECT organism_id + FROM organism + WHERE genus=$1 + AND species=$2 + ' LANGUAGE 'sql'; + +CREATE OR REPLACE FUNCTION get_organism_id(VARCHAR) RETURNS INT + AS ' +SELECT organism_id + FROM organism + WHERE genus=substring($1,1,position('' '' IN $1)-1) + AND species=substring($1,position('' '' IN $1)+1) + ' LANGUAGE 'sql'; + +CREATE OR REPLACE FUNCTION get_organism_id_abbrev(VARCHAR) RETURNS INT + AS ' +SELECT organism_id + FROM organism + WHERE substr(genus,1,1)=substring($1,1,1) + AND species=substring($1,position('' '' IN $1)+1) + ' LANGUAGE 'sql'; + +CREATE OR REPLACE FUNCTION store_organism (VARCHAR,VARCHAR,VARCHAR) + RETURNS INT AS +'DECLARE + v_genus ALIAS FOR $1; + v_species ALIAS FOR $2; + v_common_name ALIAS FOR $3; + + v_organism_id INTEGER; + BEGIN + SELECT INTO v_organism_id organism_id + FROM organism + WHERE genus=v_genus AND + species=v_species; + IF NOT FOUND THEN + INSERT INTO organism + (genus,species,common_name) + VALUES + (v_genus,v_species,v_common_name); + RETURN currval(''organism_organism_id_seq''); + ELSE + UPDATE organism + SET common_name=v_common_name + WHERE organism_id = v_organism_id; + END IF; + RETURN v_organism_id; + END; +' LANGUAGE 'plpgsql'; + +-- $Id: sequence.sql,v 1.69 2009-05-14 02:44:23 scottcain Exp $ +-- ========================================== +-- Chado sequence module +-- +-- ================================================================= +-- Dependencies: +-- +-- :import cvterm from cv +-- :import pub from pub +-- :import organism from organism +-- :import dbxref from general +-- ================================================================= + +-- ================================================ +-- TABLE: feature +-- ================================================ + +create table feature ( + feature_id serial not null, + primary key (feature_id), + dbxref_id int, + foreign key (dbxref_id) references dbxref (dbxref_id) on delete set null INITIALLY DEFERRED, + organism_id int not null, + foreign key (organism_id) references organism (organism_id) on delete cascade INITIALLY DEFERRED, + name varchar(255), + uniquename text not null, + residues text, + seqlen int, + md5checksum char(32), + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + is_analysis boolean not null default 'false', + is_obsolete boolean not null default 'false', + timeaccessioned timestamp not null default current_timestamp, + timelastmodified timestamp not null default current_timestamp, + constraint feature_c1 unique (organism_id,uniquename,type_id) +); +create sequence feature_uniquename_seq; +create index feature_name_ind1 on feature(name); +create index feature_idx1 on feature (dbxref_id); +create index feature_idx2 on feature (organism_id); +create index feature_idx3 on feature (type_id); +create index feature_idx4 on feature (uniquename); +create index feature_idx5 on feature (lower(name)); + +ALTER TABLE feature ALTER residues SET STORAGE EXTERNAL; + +COMMENT ON TABLE feature IS 'A feature is a biological sequence or a +section of a biological sequence, or a collection of such +sections. Examples include genes, exons, transcripts, regulatory +regions, polypeptides, protein domains, chromosome sequences, sequence +variations, cross-genome match regions such as hits and HSPs and so +on; see the Sequence Ontology for more. The combination of +organism_id, uniquename and type_id should be unique.'; + +COMMENT ON COLUMN feature.dbxref_id IS 'An optional primary public stable +identifier for this feature. Secondary identifiers and external +dbxrefs go in the table feature_dbxref.'; + +COMMENT ON COLUMN feature.organism_id IS 'The organism to which this feature +belongs. This column is mandatory.'; + +COMMENT ON COLUMN feature.name IS 'The optional human-readable common name for +a feature, for display purposes.'; + +COMMENT ON COLUMN feature.uniquename IS 'The unique name for a feature; may +not be necessarily be particularly human-readable, although this is +preferred. This name must be unique for this type of feature within +this organism.'; + +COMMENT ON COLUMN feature.residues IS 'A sequence of alphabetic characters +representing biological residues (nucleic acids, amino acids). This +column does not need to be manifested for all features; it is optional +for features such as exons where the residues can be derived from the +featureloc. It is recommended that the value for this column be +manifested for features which may may non-contiguous sublocations (e.g. +transcripts), since derivation at query time is non-trivial. For +expressed sequence, the DNA sequence should be used rather than the +RNA sequence. The default storage method for the residues column is +EXTERNAL, which will store it uncompressed to make substring operations +faster.'; + +COMMENT ON COLUMN feature.seqlen IS 'The length of the residue feature. See +column:residues. This column is partially redundant with the residues +column, and also with featureloc. This column is required because the +location may be unknown and the residue sequence may not be +manifested, yet it may be desirable to store and query the length of +the feature. The seqlen should always be manifested where the length +of the sequence is known.'; + +COMMENT ON COLUMN feature.md5checksum IS 'The 32-character checksum of the sequence, +calculated using the MD5 algorithm. This is practically guaranteed to +be unique for any feature. This column thus acts as a unique +identifier on the mathematical sequence.'; + +COMMENT ON COLUMN feature.type_id IS 'A required reference to a table:cvterm +giving the feature type. This will typically be a Sequence Ontology +identifier. This column is thus used to subclass the feature table.'; + +COMMENT ON COLUMN feature.is_analysis IS 'Boolean indicating whether this +feature is annotated or the result of an automated analysis. Analysis +results also use the companalysis module. Note that the dividing line +between analysis and annotation may be fuzzy, this should be determined on +a per-project basis in a consistent manner. One requirement is that +there should only be one non-analysis version of each wild-type gene +feature in a genome, whereas the same gene feature can be predicted +multiple times in different analyses.'; + +COMMENT ON COLUMN feature.is_obsolete IS 'Boolean indicating whether this +feature has been obsoleted. Some chado instances may choose to simply +remove the feature altogether, others may choose to keep an obsolete +row in the table.'; + +COMMENT ON COLUMN feature.timeaccessioned IS 'For handling object +accession or modification timestamps (as opposed to database auditing data, +handled elsewhere). The expectation is that these fields would be +available to software interacting with chado.'; + +COMMENT ON COLUMN feature.timelastmodified IS 'For handling object +accession or modification timestamps (as opposed to database auditing data, +handled elsewhere). The expectation is that these fields would be +available to software interacting with chado.'; + +--- COMMENT ON INDEX feature_c1 IS 'Any feature can be globally identified +--- by the combination of organism, uniquename and feature type'; + +-- ================================================ +-- TABLE: featureloc +-- ================================================ + +create table featureloc ( + featureloc_id serial not null, + primary key (featureloc_id), + feature_id int not null, + foreign key (feature_id) references feature (feature_id) on delete cascade INITIALLY DEFERRED, + srcfeature_id int, + foreign key (srcfeature_id) references feature (feature_id) on delete set null INITIALLY DEFERRED, + fmin int, + is_fmin_partial boolean not null default 'false', + fmax int, + is_fmax_partial boolean not null default 'false', + strand smallint, + phase int, + residue_info text, + locgroup int not null default 0, + rank int not null default 0, + constraint featureloc_c1 unique (feature_id,locgroup,rank), + constraint featureloc_c2 check (fmin <= fmax) +); +create index featureloc_idx1 on featureloc (feature_id); +create index featureloc_idx2 on featureloc (srcfeature_id); +create index featureloc_idx3 on featureloc (srcfeature_id,fmin,fmax); + +COMMENT ON TABLE featureloc IS 'The location of a feature relative to +another feature. Important: interbase coordinates are used. This is +vital as it allows us to represent zero-length features e.g. splice +sites, insertion points without an awkward fuzzy system. Features +typically have exactly ONE location, but this need not be the +case. Some features may not be localized (e.g. a gene that has been +characterized genetically but no sequence or molecular information is +available). Note on multiple locations: Each feature can have 0 or +more locations. Multiple locations do NOT indicate non-contiguous +locations (if a feature such as a transcript has a non-contiguous +location, then the subfeatures such as exons should always be +manifested). Instead, multiple featurelocs for a feature designate +alternate locations or grouped locations; for instance, a feature +designating a blast hit or hsp will have two locations, one on the +query feature, one on the subject feature. Features representing +sequence variation could have alternate locations instantiated on a +feature on the mutant strain. The column:rank is used to +differentiate these different locations. Reflexive locations should +never be stored - this is for -proper- (i.e. non-self) locations only; nothing should be located relative to itself.'; + +COMMENT ON COLUMN featureloc.feature_id IS 'The feature that is being located. Any feature can have zero or more featurelocs.'; + +COMMENT ON COLUMN featureloc.srcfeature_id IS 'The source feature which this location is relative to. Every location is relative to another feature (however, this column is nullable, because the srcfeature may not be known). All locations are -proper- that is, nothing should be located relative to itself. No cycles are allowed in the featureloc graph.'; + +COMMENT ON COLUMN featureloc.fmin IS 'The leftmost/minimal boundary in the linear range represented by the featureloc. Sometimes (e.g. in Bioperl) this is called -start- although this is confusing because it does not necessarily represent the 5-prime coordinate. Important: This is space-based (interbase) coordinates, counting from zero. To convert this to the leftmost position in a base-oriented system (eg GFF, Bioperl), add 1 to fmin.'; + +COMMENT ON COLUMN featureloc.fmax IS 'The rightmost/maximal boundary in the linear range represented by the featureloc. Sometimes (e.g. in bioperl) this is called -end- although this is confusing because it does not necessarily represent the 3-prime coordinate. Important: This is space-based (interbase) coordinates, counting from zero. No conversion is required to go from fmax to the rightmost coordinate in a base-oriented system that counts from 1 (e.g. GFF, Bioperl).'; + +COMMENT ON COLUMN featureloc.strand IS 'The orientation/directionality of the +location. Should be 0, -1 or +1.'; + +COMMENT ON COLUMN featureloc.rank IS 'Used when a feature has >1 +location, otherwise the default rank 0 is used. Some features (e.g. +blast hits and HSPs) have two locations - one on the query and one on +the subject. Rank is used to differentiate these. Rank=0 is always +used for the query, Rank=1 for the subject. For multiple alignments, +assi... [truncated message content] |
From: <sco...@us...> - 2011-11-17 19:19:50
|
Revision: 25250 http://gmod.svn.sourceforge.net/gmod/?rev=25250&view=rev Author: scottcain Date: 2011-11-17 19:19:40 +0000 (Thu, 17 Nov 2011) Log Message: ----------- fixing the manifest and the diff dir generator: diretories don't go in manifest, only files do. Modified Paths: -------------- schema/trunk/chado/MANIFEST schema/trunk/chado/schemas/generate_diff_dirs.pl Modified: schema/trunk/chado/MANIFEST =================================================================== --- schema/trunk/chado/MANIFEST 2011-11-17 17:48:58 UTC (rev 25249) +++ schema/trunk/chado/MANIFEST 2011-11-17 19:19:40 UTC (rev 25250) @@ -343,22 +343,13 @@ stag-templates/README TODO UPGRADE.txt -schemas/1-1.21 schemas/1-1.21/diff.sql -schemas/1.1-1.21 schemas/1.1-1.21/diff.sql -schemas/1.11-1.21 schemas/1.11-1.21/diff.sql -schemas/1.2-1.21 schemas/1.2-1.21/diff.sql #the following added by generate_diff_dirs.pl -schemas/1-1.22 schemas/1-1.22/diff.sql -schemas/1.1-1.22 schemas/1.1-1.22/diff.sql -schemas/1.11-1.22 schemas/1.11-1.22/diff.sql -schemas/1.2-1.22 schemas/1.2-1.22/diff.sql -schemas/1.21-1.22 schemas/1.21-1.22/diff.sql Modified: schema/trunk/chado/schemas/generate_diff_dirs.pl =================================================================== --- schema/trunk/chado/schemas/generate_diff_dirs.pl 2011-11-17 17:48:58 UTC (rev 25249) +++ schema/trunk/chado/schemas/generate_diff_dirs.pl 2011-11-17 19:19:40 UTC (rev 25250) @@ -25,7 +25,7 @@ my $newdir = $dir.'-'.$VERSION; mkdir $newdir; system("touch $newdir/diff.sql"); - push @add_to_manifest, "schemas/$newdir", "schemas/$newdir/diff.sql"; + push @add_to_manifest, "schemas/$newdir/diff.sql"; system("svn add $newdir"); } } This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <sco...@us...> - 2011-12-01 19:47:10
|
Revision: 25252 http://gmod.svn.sourceforge.net/gmod/?rev=25252&view=rev Author: scottcain Date: 2011-12-01 19:46:55 +0000 (Thu, 01 Dec 2011) Log Message: ----------- bumping the version and adding $VERSION to Bio::GMOD::DB::Config. Modified Paths: -------------- schema/trunk/chado/Changes schema/trunk/chado/MANIFEST schema/trunk/chado/Makefile.PL schema/trunk/chado/lib/Bio/GMOD/DB/Config.pm schema/trunk/chado/load/Build.PL Added Paths: ----------- schema/trunk/chado/schemas/1-1.23/ schema/trunk/chado/schemas/1-1.23/diff.sql schema/trunk/chado/schemas/1.1-1.23/ schema/trunk/chado/schemas/1.1-1.23/diff.sql schema/trunk/chado/schemas/1.11-1.23/ schema/trunk/chado/schemas/1.11-1.23/diff.sql schema/trunk/chado/schemas/1.2-1.23/ schema/trunk/chado/schemas/1.2-1.23/diff.sql schema/trunk/chado/schemas/1.21-1.23/ schema/trunk/chado/schemas/1.21-1.23/diff.sql schema/trunk/chado/schemas/1.22-1.23/ schema/trunk/chado/schemas/1.22-1.23/diff.sql schema/trunk/chado/schemas/1.23/ schema/trunk/chado/schemas/1.23/default_schema.sql Modified: schema/trunk/chado/Changes =================================================================== --- schema/trunk/chado/Changes 2011-11-17 19:25:12 UTC (rev 25251) +++ schema/trunk/chado/Changes 2011-12-01 19:46:55 UTC (rev 25252) @@ -1,4 +1,8 @@ -Version 1.11 Thu Nov 17 12:48:10 EST 2011 +Version 1.23 Thu Dec 1 14:45:22 EST 2011 +* Added $VERSION to Bio::GMOD::DB::Config so that when installing +Bio::DB::Das::Chado it would be happy. + +Version 1.22 Thu Nov 17 12:48:10 EST 2011 * Created a script to automatically create the schemas dirs for a release and add them to the manifest. Modified: schema/trunk/chado/MANIFEST =================================================================== --- schema/trunk/chado/MANIFEST 2011-11-17 19:25:12 UTC (rev 25251) +++ schema/trunk/chado/MANIFEST 2011-12-01 19:46:55 UTC (rev 25252) @@ -353,3 +353,10 @@ schemas/1.11-1.22/diff.sql schemas/1.2-1.22/diff.sql schemas/1.21-1.22/diff.sql +#the following added by generate_diff_dirs.pl +schemas/1-1.23/diff.sql +schemas/1.1-1.23/diff.sql +schemas/1.11-1.23/diff.sql +schemas/1.2-1.23/diff.sql +schemas/1.21-1.23/diff.sql +schemas/1.22-1.23/diff.sql Modified: schema/trunk/chado/Makefile.PL =================================================================== --- schema/trunk/chado/Makefile.PL 2011-11-17 19:25:12 UTC (rev 25251) +++ schema/trunk/chado/Makefile.PL 2011-12-01 19:46:55 UTC (rev 25252) @@ -50,7 +50,7 @@ use Template; use Module::Build; -my $VERSION = 1.22; +my $VERSION = 1.23; my %args = ( DBDRIVER => { Modified: schema/trunk/chado/lib/Bio/GMOD/DB/Config.pm =================================================================== --- schema/trunk/chado/lib/Bio/GMOD/DB/Config.pm 2011-11-17 19:25:12 UTC (rev 25251) +++ schema/trunk/chado/lib/Bio/GMOD/DB/Config.pm 2011-12-01 19:46:55 UTC (rev 25252) @@ -33,6 +33,7 @@ use vars '@ISA'; use base qw/ Bio::GMOD::Config /; +my $VERSION = 1.23; =head2 new Modified: schema/trunk/chado/load/Build.PL =================================================================== --- schema/trunk/chado/load/Build.PL 2011-11-17 19:25:12 UTC (rev 25251) +++ schema/trunk/chado/load/Build.PL 2011-12-01 19:46:55 UTC (rev 25252) @@ -3,7 +3,7 @@ use Bio::Chado::Builder; use Data::Dumper; -my $VERSION = 1.22; +my $VERSION = 1.23; my $conf = shift; my $m = Bio::Chado::Builder->new( dist_name => 'Chado', Added: schema/trunk/chado/schemas/1-1.23/diff.sql =================================================================== Added: schema/trunk/chado/schemas/1.1-1.23/diff.sql =================================================================== Added: schema/trunk/chado/schemas/1.11-1.23/diff.sql =================================================================== Added: schema/trunk/chado/schemas/1.2-1.23/diff.sql =================================================================== Added: schema/trunk/chado/schemas/1.21-1.23/diff.sql =================================================================== Added: schema/trunk/chado/schemas/1.22-1.23/diff.sql =================================================================== Added: schema/trunk/chado/schemas/1.23/default_schema.sql =================================================================== --- schema/trunk/chado/schemas/1.23/default_schema.sql (rev 0) +++ schema/trunk/chado/schemas/1.23/default_schema.sql 2011-12-01 19:46:55 UTC (rev 25252) @@ -0,0 +1,41889 @@ +-- $Id: general.sql,v 1.31 2007-03-01 02:45:54 briano Exp $ +-- ========================================== +-- Chado general module +-- +-- ================================================ +-- TABLE: tableinfo +-- ================================================ + +create table tableinfo ( + tableinfo_id serial not null, + primary key (tableinfo_id), + name varchar(30) not null, + primary_key_column varchar(30) null, + is_view int not null default 0, + view_on_table_id int null, + superclass_table_id int null, + is_updateable int not null default 1, + modification_date date not null default now(), + constraint tableinfo_c1 unique (name) +); + +COMMENT ON TABLE tableinfo IS NULL; + +-- ================================================ +-- TABLE: db +-- ================================================ + +create table db ( + db_id serial not null, + primary key (db_id), + name varchar(255) not null, +-- contact_id int, +-- foreign key (contact_id) references contact (contact_id) on delete cascade INITIALLY DEFERRED, + description varchar(255) null, + urlprefix varchar(255) null, + url varchar(255) null, + constraint db_c1 unique (name) +); + +COMMENT ON TABLE db IS 'A database authority. Typical databases in +bioinformatics are FlyBase, GO, UniProt, NCBI, MGI, etc. The authority +is generally known by this shortened form, which is unique within the +bioinformatics and biomedical realm. To Do - add support for URIs, +URNs (e.g. LSIDs). We can do this by treating the URL as a URI - +however, some applications may expect this to be resolvable - to be +decided.'; + +-- ================================================ +-- TABLE: dbxref +-- ================================================ + +create table dbxref ( + dbxref_id serial not null, + primary key (dbxref_id), + db_id int not null, + foreign key (db_id) references db (db_id) on delete cascade INITIALLY DEFERRED, + accession varchar(255) not null, + version varchar(255) not null default '', + description text, + constraint dbxref_c1 unique (db_id,accession,version) +); +create index dbxref_idx1 on dbxref (db_id); +create index dbxref_idx2 on dbxref (accession); +create index dbxref_idx3 on dbxref (version); + +COMMENT ON TABLE dbxref IS 'A unique, global, public, stable identifier. Not necessarily an external reference - can reference data items inside the particular chado instance being used. Typically a row in a table can be uniquely identified with a primary identifier (called dbxref_id); a table may also have secondary identifiers (in a linking table <T>_dbxref). A dbxref is generally written as <DB>:<ACCESSION> or as <DB>:<ACCESSION>:<VERSION>.'; + +COMMENT ON COLUMN dbxref.accession IS 'The local part of the identifier. Guaranteed by the db authority to be unique for that db.'; + +CREATE VIEW db_dbxref_count AS + SELECT db.name,count(*) AS num_dbxrefs FROM db INNER JOIN dbxref USING (db_id) GROUP BY db.name; +COMMENT ON VIEW db_dbxref_count IS 'per-db dbxref counts'; + +CREATE OR REPLACE FUNCTION store_db (VARCHAR) + RETURNS INT AS +'DECLARE + v_name ALIAS FOR $1; + + v_db_id INTEGER; + BEGIN + SELECT INTO v_db_id db_id + FROM db + WHERE name=v_name; + IF NOT FOUND THEN + INSERT INTO db + (name) + VALUES + (v_name); + RETURN currval(''db_db_id_seq''); + END IF; + RETURN v_db_id; + END; +' LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION store_dbxref (VARCHAR,VARCHAR) + RETURNS INT AS +'DECLARE + v_dbname ALIAS FOR $1; + v_accession ALIAS FOR $2; + + v_db_id INTEGER; + v_dbxref_id INTEGER; + BEGIN + SELECT INTO v_db_id + store_db(v_dbname); + SELECT INTO v_dbxref_id dbxref_id + FROM dbxref + WHERE db_id=v_db_id AND + accession=v_accession; + IF NOT FOUND THEN + INSERT INTO dbxref + (db_id,accession) + VALUES + (v_db_id,v_accession); + RETURN currval(''dbxref_dbxref_id_seq''); + END IF; + RETURN v_dbxref_id; + END; +' LANGUAGE 'plpgsql'; + +-- $Id: cv.sql,v 1.37 2007-02-28 15:08:48 briano Exp $ +-- ========================================== +-- Chado cv module +-- +-- ================================================================= +-- Dependencies: +-- +-- :import dbxref from general +-- ================================================================= + +-- ================================================ +-- TABLE: cv +-- ================================================ +create table cv ( + cv_id serial not null, + primary key (cv_id), + name varchar(255) not null, + definition text, + constraint cv_c1 unique (name) +); + +COMMENT ON TABLE cv IS 'A controlled vocabulary or ontology. A cv is +composed of cvterms (AKA terms, classes, types, universals - relations +and properties are also stored in cvterm) and the relationships +between them.'; + +COMMENT ON COLUMN cv.name IS 'The name of the ontology. This +corresponds to the obo-format -namespace-. cv names uniquely identify +the cv. In OBO file format, the cv.name is known as the namespace.'; + +COMMENT ON COLUMN cv.definition IS 'A text description of the criteria for +membership of this ontology.'; + +-- ================================================ +-- TABLE: cvterm +-- ================================================ +create table cvterm ( + cvterm_id serial not null, + primary key (cvterm_id), + cv_id int not null, + foreign key (cv_id) references cv (cv_id) on delete cascade INITIALLY DEFERRED, + name varchar(1024) not null, + definition text, + dbxref_id int not null, + foreign key (dbxref_id) references dbxref (dbxref_id) on delete set null INITIALLY DEFERRED, + is_obsolete int not null default 0, + is_relationshiptype int not null default 0, + constraint cvterm_c1 unique (name,cv_id,is_obsolete), + constraint cvterm_c2 unique (dbxref_id) +); +create index cvterm_idx1 on cvterm (cv_id); +create index cvterm_idx2 on cvterm (name); +create index cvterm_idx3 on cvterm (dbxref_id); + +COMMENT ON TABLE cvterm IS 'A term, class, universal or type within an +ontology or controlled vocabulary. This table is also used for +relations and properties. cvterms constitute nodes in the graph +defined by the collection of cvterms and cvterm_relationships.'; + +COMMENT ON COLUMN cvterm.cv_id IS 'The cv or ontology or namespace to which +this cvterm belongs.'; + +COMMENT ON COLUMN cvterm.name IS 'A concise human-readable name or +label for the cvterm. Uniquely identifies a cvterm within a cv.'; + +COMMENT ON COLUMN cvterm.definition IS 'A human-readable text +definition.'; + +COMMENT ON COLUMN cvterm.dbxref_id IS 'Primary identifier dbxref - The +unique global OBO identifier for this cvterm. Note that a cvterm may +have multiple secondary dbxrefs - see also table: cvterm_dbxref.'; + +COMMENT ON COLUMN cvterm.is_obsolete IS 'Boolean 0=false,1=true; see +GO documentation for details of obsoletion. Note that two terms with +different primary dbxrefs may exist if one is obsolete.'; + +COMMENT ON COLUMN cvterm.is_relationshiptype IS 'Boolean +0=false,1=true relations or relationship types (also known as Typedefs +in OBO format, or as properties or slots) form a cv/ontology in +themselves. We use this flag to indicate whether this cvterm is an +actual term/class/universal or a relation. Relations may be drawn from +the OBO Relations ontology, but are not exclusively drawn from there.'; + +COMMENT ON INDEX cvterm_c1 IS 'A name can mean different things in +different contexts; for example "chromosome" in SO and GO. A name +should be unique within an ontology or cv. A name may exist twice in a +cv, in both obsolete and non-obsolete forms - these will be for +different cvterms with different OBO identifiers; so GO documentation +for more details on obsoletion. Note that occasionally multiple +obsolete terms with the same name will exist in the same cv. If this +is a possibility for the ontology under consideration (e.g. GO) then the +ID should be appended to the name to ensure uniqueness.'; + +COMMENT ON INDEX cvterm_c2 IS 'The OBO identifier is globally unique.'; + +-- ================================================ +-- TABLE: cvterm_relationship +-- ================================================ +create table cvterm_relationship ( + cvterm_relationship_id serial not null, + primary key (cvterm_relationship_id), + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + subject_id int not null, + foreign key (subject_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + object_id int not null, + foreign key (object_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + constraint cvterm_relationship_c1 unique (subject_id,object_id,type_id) +); +create index cvterm_relationship_idx1 on cvterm_relationship (type_id); +create index cvterm_relationship_idx2 on cvterm_relationship (subject_id); +create index cvterm_relationship_idx3 on cvterm_relationship (object_id); + +COMMENT ON TABLE cvterm_relationship IS 'A relationship linking two +cvterms. Each cvterm_relationship constitutes an edge in the graph +defined by the collection of cvterms and cvterm_relationships. The +meaning of the cvterm_relationship depends on the definition of the +cvterm R refered to by type_id. However, in general the definitions +are such that the statement "all SUBJs REL some OBJ" is true. The +cvterm_relationship statement is about the subject, not the +object. For example "insect wing part_of thorax".'; + +COMMENT ON COLUMN cvterm_relationship.subject_id IS 'The subject of +the subj-predicate-obj sentence. The cvterm_relationship is about the +subject. In a graph, this typically corresponds to the child node.'; + +COMMENT ON COLUMN cvterm_relationship.object_id IS 'The object of the +subj-predicate-obj sentence. The cvterm_relationship refers to the +object. In a graph, this typically corresponds to the parent node.'; + +COMMENT ON COLUMN cvterm_relationship.type_id IS 'The nature of the +relationship between subject and object. Note that relations are also +housed in the cvterm table, typically from the OBO relationship +ontology, although other relationship types are allowed.'; + +-- ================================================ +-- TABLE: cvtermpath +-- ================================================ +create table cvtermpath ( + cvtermpath_id serial not null, + primary key (cvtermpath_id), + type_id int, + foreign key (type_id) references cvterm (cvterm_id) on delete set null INITIALLY DEFERRED, + subject_id int not null, + foreign key (subject_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + object_id int not null, + foreign key (object_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + cv_id int not null, + foreign key (cv_id) references cv (cv_id) on delete cascade INITIALLY DEFERRED, + pathdistance int, + constraint cvtermpath_c1 unique (subject_id,object_id,type_id,pathdistance) +); +create index cvtermpath_idx1 on cvtermpath (type_id); +create index cvtermpath_idx2 on cvtermpath (subject_id); +create index cvtermpath_idx3 on cvtermpath (object_id); +create index cvtermpath_idx4 on cvtermpath (cv_id); + +COMMENT ON TABLE cvtermpath IS 'The reflexive transitive closure of +the cvterm_relationship relation.'; + +COMMENT ON COLUMN cvtermpath.type_id IS 'The relationship type that +this is a closure over. If null, then this is a closure over ALL +relationship types. If non-null, then this references a relationship +cvterm - note that the closure will apply to both this relationship +AND the OBO_REL:is_a (subclass) relationship.'; + +COMMENT ON COLUMN cvtermpath.cv_id IS 'Closures will mostly be within +one cv. If the closure of a relationship traverses a cv, then this +refers to the cv of the object_id cvterm.'; + +COMMENT ON COLUMN cvtermpath.pathdistance IS 'The number of steps +required to get from the subject cvterm to the object cvterm, counting +from zero (reflexive relationship).'; + +-- ================================================ +-- TABLE: cvtermsynonym +-- ================================================ +create table cvtermsynonym ( + cvtermsynonym_id serial not null, + primary key (cvtermsynonym_id), + cvterm_id int not null, + foreign key (cvterm_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + synonym varchar(1024) not null, + type_id int, + foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + constraint cvtermsynonym_c1 unique (cvterm_id,synonym) +); +create index cvtermsynonym_idx1 on cvtermsynonym (cvterm_id); + +COMMENT ON TABLE cvtermsynonym IS 'A cvterm actually represents a +distinct class or concept. A concept can be refered to by different +phrases or names. In addition to the primary name (cvterm.name) there +can be a number of alternative aliases or synonyms. For example, "T +cell" as a synonym for "T lymphocyte".'; + +COMMENT ON COLUMN cvtermsynonym.type_id IS 'A synonym can be exact, +narrower, or broader than.'; + + +-- ================================================ +-- TABLE: cvterm_dbxref +-- ================================================ +create table cvterm_dbxref ( + cvterm_dbxref_id serial not null, + primary key (cvterm_dbxref_id), + cvterm_id int not null, + foreign key (cvterm_id) references cvterm (cvterm_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_for_definition int not null default 0, + constraint cvterm_dbxref_c1 unique (cvterm_id,dbxref_id) +); +create index cvterm_dbxref_idx1 on cvterm_dbxref (cvterm_id); +create index cvterm_dbxref_idx2 on cvterm_dbxref (dbxref_id); + +COMMENT ON TABLE cvterm_dbxref IS 'In addition to the primary +identifier (cvterm.dbxref_id) a cvterm can have zero or more secondary +identifiers/dbxrefs, which may refer to records in external +databases. The exact semantics of cvterm_dbxref are not fixed. For +example: the dbxref could be a pubmed ID that is pertinent to the +cvterm, or it could be an equivalent or similar term in another +ontology. For example, GO cvterms are typically linked to InterPro +IDs, even though the nature of the relationship between them is +largely one of statistical association. The dbxref may be have data +records attached in the same database instance, or it could be a +"hanging" dbxref pointing to some external database. NOTE: If the +desired objective is to link two cvterms together, and the nature of +the relation is known and holds for all instances of the subject +cvterm then consider instead using cvterm_relationship together with a +well-defined relation.'; + +COMMENT ON COLUMN cvterm_dbxref.is_for_definition IS 'A +cvterm.definition should be supported by one or more references. If +this column is true, the dbxref is not for a term in an external database - +it is a dbxref for provenance information for the definition.'; + + +-- ================================================ +-- TABLE: cvtermprop +-- ================================================ +create table cvtermprop ( + cvtermprop_id serial not null, + primary key (cvtermprop_id), + cvterm_id int not null, + foreign key (cvterm_id) references cvterm (cvterm_id) on delete cascade, + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) on delete cascade, + value text not null default '', + rank int not null default 0, + + unique(cvterm_id, type_id, value, rank) +); +create index cvtermprop_idx1 on cvtermprop (cvterm_id); +create index cvtermprop_idx2 on cvtermprop (type_id); + +COMMENT ON TABLE cvtermprop IS 'Additional extensible properties can be attached to a cvterm using this table. Corresponds to -AnnotationProperty- in W3C OWL format.'; + +COMMENT ON COLUMN cvtermprop.type_id IS 'The name of the property or slot is a cvterm. The meaning of the property is defined in that cvterm.'; + +COMMENT ON COLUMN cvtermprop.value IS 'The value of the property, represented as text. Numeric values are converted to their text representation.'; + +COMMENT ON COLUMN cvtermprop.rank IS 'Property-Value ordering. Any +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: dbxrefprop +-- ================================================ +create table dbxrefprop ( + dbxrefprop_id serial not null, + primary key (dbxrefprop_id), + dbxref_id int not null, + foreign key (dbxref_id) references dbxref (dbxref_id) INITIALLY DEFERRED, + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) INITIALLY DEFERRED, + value text not null default '', + rank int not null default 0, + constraint dbxrefprop_c1 unique (dbxref_id,type_id,rank) +); +create index dbxrefprop_idx1 on dbxrefprop (dbxref_id); +create index dbxrefprop_idx2 on dbxrefprop (type_id); + +COMMENT ON TABLE dbxrefprop IS 'Metadata about a dbxref. Note that this is not defined in the dbxref module, as it depends on the cvterm table. This table has a structure analagous to cvtermprop.'; + + +-- ================================================ +-- TABLE: cvprop +-- ================================================ +create table cvprop ( + cvprop_id serial not null, + primary key (cvprop_id), + cv_id int not null, + foreign key (cv_id) references cv (cv_id) INITIALLY DEFERRED, + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) INITIALLY DEFERRED, + value text, + rank int not null default 0, + constraint cvprop_c1 unique (cv_id,type_id,rank) +); + +COMMENT ON TABLE cvprop IS 'Additional extensible properties can be attached to a cv using this table. A notable example would be the cv version'; + +COMMENT ON COLUMN cvprop.type_id IS 'The name of the property or slot is a cvterm. The meaning of the property is defined in that cvterm.'; +COMMENT ON COLUMN cvprop.value IS 'The value of the property, represented as text. Numeric values are converted to their text representation.'; + +COMMENT ON COLUMN cvprop.rank IS 'Property-Value ordering. Any +cv 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: chadoprop +-- ================================================ +create table chadoprop ( + chadoprop_id serial not null, + primary key (chadoprop_id), + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) INITIALLY DEFERRED, + value text, + rank int not null default 0, + constraint chadoprop_c1 unique (type_id,rank) +); + +COMMENT ON TABLE chadoprop IS 'This table is different from other prop tables in the database, as it is for storing information about the database itself, like schema version'; + +COMMENT ON COLUMN chadoprop.type_id IS 'The name of the property or slot is a cvterm. The meaning of the property is defined in that cvterm.'; +COMMENT ON COLUMN chadoprop.value IS 'The value of the property, represented as text. Numeric values are converted to their text representation.'; + +COMMENT ON COLUMN chadoprop.rank IS 'Property-Value ordering. Any +cv 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.'; + +CREATE OR REPLACE VIEW cv_root AS + SELECT + cv_id, + cvterm_id AS root_cvterm_id + FROM cvterm + WHERE + cvterm_id NOT IN ( SELECT subject_id FROM cvterm_relationship) AND + is_obsolete=0; + +COMMENT ON VIEW cv_root IS 'the roots of a cv are the set of terms +which have no parents (terms that are not the subject of a +relation). Most cvs will have a single root, some may have >1. All +will have at least 1'; + +CREATE OR REPLACE VIEW cv_leaf AS + SELECT + cv_id, + cvterm_id + FROM cvterm + WHERE + cvterm_id NOT IN ( SELECT object_id FROM cvterm_relationship); + +COMMENT ON VIEW cv_leaf IS 'the leaves of a cv are the set of terms +which have no children (terms that are not the object of a +relation). All cvs will have at least 1 leaf'; + +CREATE OR REPLACE VIEW common_ancestor_cvterm AS + SELECT + p1.subject_id AS cvterm1_id, + p2.subject_id AS cvterm2_id, + p1.object_id AS ancestor_cvterm_id, + p1.pathdistance AS pathdistance1, + p2.pathdistance AS pathdistance2, + p1.pathdistance + p2.pathdistance + AS total_pathdistance + FROM + cvtermpath AS p1, + cvtermpath AS p2 + WHERE + p1.object_id = p2.object_id; + +COMMENT ON VIEW common_ancestor_cvterm IS 'The common ancestor of any +two terms is the intersection of both terms ancestors. Two terms can +have multiple common ancestors. Use total_pathdistance to get the +least common ancestor'; + +CREATE OR REPLACE VIEW common_descendant_cvterm AS + SELECT + p1.object_id AS cvterm1_id, + p2.object_id AS cvterm2_id, + p1.subject_id AS ancestor_cvterm_id, + p1.pathdistance AS pathdistance1, + p2.pathdistance AS pathdistance2, + p1.pathdistance + p2.pathdistance + AS total_pathdistance + FROM + cvtermpath AS p1, + cvtermpath AS p2 + WHERE + p1.subject_id = p2.subject_id; + +COMMENT ON VIEW common_descendant_cvterm IS 'The common descendant of +any two terms is the intersection of both terms descendants. Two terms +can have multiple common descendants. Use total_pathdistance to get +the least common ancestor'; + +CREATE OR REPLACE VIEW stats_paths_to_root AS + SELECT + subject_id AS cvterm_id, + count(DISTINCT cvtermpath_id) AS total_paths, + avg(pathdistance) AS avg_distance, + min(pathdistance) AS min_distance, + max(pathdistance) AS max_distance + FROM cvtermpath INNER JOIN cv_root ON (object_id=root_cvterm_id) + GROUP BY cvterm_id; + +COMMENT ON VIEW stats_paths_to_root IS 'per-cvterm statistics on its +placement in the DAG relative to the root. There may be multiple paths +from any term to the root. This gives the total number of paths, and +the average minimum and maximum distances. Here distance is defined by +cvtermpath.pathdistance'; +CREATE VIEW cv_cvterm_count AS + SELECT cv.name,count(*) AS num_terms_excl_obs FROM cv INNER JOIN cvterm USING (cv_id) WHERE is_obsolete=0 GROUP BY cv.name; +COMMENT ON VIEW cv_cvterm_count IS 'per-cv terms counts (excludes obsoletes)'; + +CREATE VIEW cv_cvterm_count_with_obs AS + SELECT cv.name,count(*) AS num_terms_incl_obs FROM cv INNER JOIN cvterm USING (cv_id) GROUP BY cv.name; +COMMENT ON VIEW cv_cvterm_count_with_obs IS 'per-cv terms counts (includes obsoletes)'; + +CREATE VIEW cv_link_count AS + SELECT cv.name AS cv_name, + relation.name AS relation_name, + relation_cv.name AS relation_cv_name, + count(*) AS num_links + FROM cv + INNER JOIN cvterm ON (cvterm.cv_id=cv.cv_id) + INNER JOIN cvterm_relationship ON (cvterm.cvterm_id=subject_id) + INNER JOIN cvterm AS relation ON (type_id=relation.cvterm_id) + INNER JOIN cv AS relation_cv ON (relation.cv_id=relation_cv.cv_id) + GROUP BY cv.name,relation.name,relation_cv.name; + +COMMENT ON VIEW cv_link_count IS 'per-cv summary of number of +links (cvterm_relationships) broken down by +relationship_type. num_links is the total # of links of the specified +type in which the subject_id of the link is in the named cv'; + +CREATE VIEW cv_path_count AS + SELECT cv.name AS cv_name, + relation.name AS relation_name, + relation_cv.name AS relation_cv_name, + count(*) AS num_paths + FROM cv + INNER JOIN cvterm ON (cvterm.cv_id=cv.cv_id) + INNER JOIN cvtermpath ON (cvterm.cvterm_id=subject_id) + INNER JOIN cvterm AS relation ON (type_id=relation.cvterm_id) + INNER JOIN cv AS relation_cv ON (relation.cv_id=relation_cv.cv_id) + GROUP BY cv.name,relation.name,relation_cv.name; + +COMMENT ON VIEW cv_path_count IS 'per-cv summary of number of +paths (cvtermpaths) broken down by relationship_type. num_paths is the +total # of paths of the specified type in which the subject_id of the +path is in the named cv. See also: cv_distinct_relations'; + +CREATE OR REPLACE FUNCTION _get_all_subject_ids(integer) RETURNS SETOF cvtermpath AS +' +DECLARE + root alias for $1; + cterm cvtermpath%ROWTYPE; + cterm2 cvtermpath%ROWTYPE; +BEGIN + + FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = root LOOP + RETURN NEXT cterm; + FOR cterm2 IN SELECT * FROM _get_all_subject_ids(cterm.subject_id) LOOP + RETURN NEXT cterm2; + END LOOP; + END LOOP; + RETURN; +END; +' +LANGUAGE 'plpgsql'; + +---arg: parent term id +---return: all children term id and their parent term id with relationship type id +CREATE OR REPLACE FUNCTION get_all_subject_ids(integer) RETURNS SETOF cvtermpath AS +' +DECLARE + root alias for $1; + cterm cvtermpath%ROWTYPE; + exist_c int; +BEGIN + + SELECT INTO exist_c count(*) FROM cvtermpath WHERE object_id = root and pathdistance <= 0; + IF (exist_c > 0) THEN + FOR cterm IN SELECT * FROM cvtermpath WHERE object_id = root and pathdistance > 0 LOOP + RETURN NEXT cterm; + END LOOP; + ELSE + FOR cterm IN SELECT * FROM _get_all_subject_ids(root) LOOP + RETURN NEXT cterm; + END LOOP; + END IF; + RETURN; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION get_graph_below(integer) RETURNS SETOF cvtermpath AS +' +DECLARE + root alias for $1; + cterm cvtermpath%ROWTYPE; + cterm2 cvtermpath%ROWTYPE; + +BEGIN + + FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = root LOOP + RETURN NEXT cterm; + FOR cterm2 IN SELECT * FROM get_all_subject_ids(cterm.subject_id) LOOP + RETURN NEXT cterm2; + END LOOP; + END LOOP; + RETURN; +END; +' +LANGUAGE 'plpgsql'; + + +CREATE OR REPLACE FUNCTION get_graph_above(integer) RETURNS SETOF cvtermpath AS +' +DECLARE + leaf alias for $1; + cterm cvtermpath%ROWTYPE; + cterm2 cvtermpath%ROWTYPE; + +BEGIN + + FOR cterm IN SELECT * FROM cvterm_relationship WHERE subject_id = leaf LOOP + RETURN NEXT cterm; + FOR cterm2 IN SELECT * FROM get_all_object_ids(cterm.object_id) LOOP + RETURN NEXT cterm2; + END LOOP; + END LOOP; + RETURN; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION _get_all_object_ids(integer) RETURNS SETOF cvtermpath AS +' +DECLARE + leaf alias for $1; + cterm cvtermpath%ROWTYPE; + cterm2 cvtermpath%ROWTYPE; +BEGIN + + FOR cterm IN SELECT * FROM cvterm_relationship WHERE subject_id = leaf LOOP + RETURN NEXT cterm; + FOR cterm2 IN SELECT * FROM _get_all_object_ids(cterm.object_id) LOOP + RETURN NEXT cterm2; + END LOOP; + END LOOP; + RETURN; +END; +' +LANGUAGE 'plpgsql'; + +---arg: child term id +---return: all parent term id and their childrent term id with relationship type id +CREATE OR REPLACE FUNCTION get_all_object_ids(integer) RETURNS SETOF cvtermpath AS +' +DECLARE + leaf alias for $1; + cterm cvtermpath%ROWTYPE; + exist_c int; +BEGIN + + + SELECT INTO exist_c count(*) FROM cvtermpath WHERE object_id = leaf and pathdistance <= 0; + IF (exist_c > 0) THEN + FOR cterm IN SELECT * FROM cvtermpath WHERE subject_id = leaf AND pathdistance > 0 LOOP + RETURN NEXT cterm; + END LOOP; + ELSE + FOR cterm IN SELECT * FROM _get_all_object_ids(leaf) LOOP + RETURN NEXT cterm; + END LOOP; + END IF; + RETURN; +END; +' +LANGUAGE 'plpgsql'; + +---arg: sql statement which must be in the form of select cvterm_id from ... +---return: a set of cvterm ids that includes what is in sql statement and their children (subject ids) +CREATE OR REPLACE FUNCTION get_it_sub_cvterm_ids(text) RETURNS SETOF cvterm AS +' +DECLARE + query alias for $1; + cterm cvterm%ROWTYPE; + cterm2 cvterm%ROWTYPE; +BEGIN + FOR cterm IN EXECUTE query LOOP + RETURN NEXT cterm; + FOR cterm2 IN SELECT subject_id as cvterm_id FROM get_all_subject_ids(cterm.cvterm_id) LOOP + RETURN NEXT cterm2; + END LOOP; + END LOOP; + RETURN; +END; +' +LANGUAGE 'plpgsql'; +--- example: select * from fill_cvtermpath(7); where 7 is cv_id for an ontology +--- fill path from the node to its children and their children +CREATE OR REPLACE FUNCTION _fill_cvtermpath4node(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) RETURNS INTEGER AS +' +DECLARE + origin alias for $1; + child_id alias for $2; + cvid alias for $3; + typeid alias for $4; + depth alias for $5; + cterm cvterm_relationship%ROWTYPE; + exist_c int; + +BEGIN + + --- RAISE NOTICE ''depth=% root=%'', depth,child_id; + --- not check type_id as it may be null and not very meaningful in cvtermpath when pathdistance > 1 + SELECT INTO exist_c count(*) FROM cvtermpath WHERE cv_id = cvid AND object_id = origin AND subject_id = child_id AND pathdistance = depth; + + IF (exist_c = 0) THEN + INSERT INTO cvtermpath (object_id, subject_id, cv_id, type_id, pathdistance) VALUES(origin, child_id, cvid, typeid, depth); + END IF; + FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = child_id LOOP + PERFORM _fill_cvtermpath4node(origin, cterm.subject_id, cvid, cterm.type_id, depth+1); + END LOOP; + RETURN 1; +END; +' +LANGUAGE 'plpgsql'; + + +CREATE OR REPLACE FUNCTION _fill_cvtermpath4root(INTEGER, INTEGER) RETURNS INTEGER AS +' +DECLARE + rootid alias for $1; + cvid alias for $2; + ttype int; + cterm cvterm_relationship%ROWTYPE; + child cvterm_relationship%ROWTYPE; + +BEGIN + + SELECT INTO ttype cvterm_id FROM cvterm WHERE (name = ''isa'' OR name = ''is_a''); + PERFORM _fill_cvtermpath4node(rootid, rootid, cvid, ttype, 0); + FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = rootid LOOP + PERFORM _fill_cvtermpath4root(cterm.subject_id, cvid); + -- RAISE NOTICE ''DONE for term, %'', cterm.subject_id; + END LOOP; + RETURN 1; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION fill_cvtermpath(INTEGER) RETURNS INTEGER AS +' +DECLARE + cvid alias for $1; + root cvterm%ROWTYPE; + +BEGIN + + DELETE FROM cvtermpath WHERE cv_id = cvid; + + FOR root IN SELECT DISTINCT t.* from cvterm t LEFT JOIN cvterm_relationship r ON (t.cvterm_id = r.subject_id) INNER JOIN cvterm_relationship r2 ON (t.cvterm_id = r2.object_id) WHERE t.cv_id = cvid AND r.subject_id is null LOOP + PERFORM _fill_cvtermpath4root(root.cvterm_id, root.cv_id); + END LOOP; + RETURN 1; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION fill_cvtermpath(cv.name%TYPE) RETURNS INTEGER AS +' +DECLARE + cvname alias for $1; + cv_id int; + rtn int; +BEGIN + + SELECT INTO cv_id cv.cv_id from cv WHERE cv.name = cvname; + SELECT INTO rtn fill_cvtermpath(cv_id); + RETURN rtn; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION _fill_cvtermpath4node2detect_cycle(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) RETURNS INTEGER AS +' +DECLARE + origin alias for $1; + child_id alias for $2; + cvid alias for $3; + typeid alias for $4; + depth alias for $5; + cterm cvterm_relationship%ROWTYPE; + exist_c int; + ccount int; + ecount int; + rtn int; +BEGIN + + EXECUTE ''SELECT * FROM tmpcvtermpath p1, tmpcvtermpath p2 WHERE p1.subject_id=p2.object_id AND p1.object_id=p2.subject_id AND p1.object_id = ''|| origin || '' AND p2.subject_id = '' || child_id || ''AND '' || depth || ''> 0''; + GET DIAGNOSTICS ccount = ROW_COUNT; + IF (ccount > 0) THEN + --RAISE EXCEPTION ''FOUND CYCLE: node % on cycle path'',origin; + RETURN origin; + END IF; + + EXECUTE ''SELECT * FROM tmpcvtermpath WHERE cv_id = '' || cvid || '' AND object_id = '' || origin || '' AND subject_id = '' || child_id || '' AND '' || origin || ''<>'' || child_id; + GET DIAGNOSTICS ecount = ROW_COUNT; + IF (ecount > 0) THEN + --RAISE NOTICE ''FOUND TWICE (node), will check root obj % subj %'',origin, child_id; + SELECT INTO rtn _fill_cvtermpath4root2detect_cycle(child_id, cvid); + IF (rtn > 0) THEN + RETURN rtn; + END IF; + END IF; + + EXECUTE ''SELECT * FROM tmpcvtermpath WHERE cv_id = '' || cvid || '' AND object_id = '' || origin || '' AND subject_id = '' || child_id || '' AND pathdistance = '' || depth; + GET DIAGNOSTICS exist_c = ROW_COUNT; + IF (exist_c = 0) THEN + EXECUTE ''INSERT INTO tmpcvtermpath (object_id, subject_id, cv_id, type_id, pathdistance) VALUES('' || origin || '', '' || child_id || '', '' || cvid || '', '' || typeid || '', '' || depth || '')''; + END IF; + + FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = child_id LOOP + --RAISE NOTICE ''DOING for node, % %'', origin, cterm.subject_id; + SELECT INTO rtn _fill_cvtermpath4node2detect_cycle(origin, cterm.subject_id, cvid, cterm.type_id, depth+1); + IF (rtn > 0) THEN + RETURN rtn; + END IF; + END LOOP; + RETURN 0; +END; +' +LANGUAGE 'plpgsql'; + + +CREATE OR REPLACE FUNCTION _fill_cvtermpath4root2detect_cycle(INTEGER, INTEGER) RETURNS INTEGER AS +' +DECLARE + rootid alias for $1; + cvid alias for $2; + ttype int; + ccount int; + cterm cvterm_relationship%ROWTYPE; + child cvterm_relationship%ROWTYPE; + rtn int; +BEGIN + + SELECT INTO ttype cvterm_id FROM cvterm WHERE (name = ''isa'' OR name = ''is_a''); + SELECT INTO rtn _fill_cvtermpath4node2detect_cycle(rootid, rootid, cvid, ttype, 0); + IF (rtn > 0) THEN + RETURN rtn; + END IF; + FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = rootid LOOP + EXECUTE ''SELECT * FROM tmpcvtermpath p1, tmpcvtermpath p2 WHERE p1.subject_id=p2.object_id AND p1.object_id=p2.subject_id AND p1.object_id='' || rootid || '' AND p1.subject_id='' || cterm.subject_id; + GET DIAGNOSTICS ccount = ROW_COUNT; + IF (ccount > 0) THEN + --RAISE NOTICE ''FOUND TWICE (root), will check root obj % subj %'',rootid,cterm.subject_id; + SELECT INTO rtn _fill_cvtermpath4node2detect_cycle(rootid, cterm.subject_id, cvid, ttype, 0); + IF (rtn > 0) THEN + RETURN rtn; + END IF; + ELSE + SELECT INTO rtn _fill_cvtermpath4root2detect_cycle(cterm.subject_id, cvid); + IF (rtn > 0) THEN + RETURN rtn; + END IF; + END IF; + END LOOP; + RETURN 0; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION get_cycle_cvterm_id(INTEGER, INTEGER) RETURNS INTEGER AS +' +DECLARE + cvid alias for $1; + rootid alias for $2; + rtn int; +BEGIN + + CREATE TEMP TABLE tmpcvtermpath(object_id int, subject_id int, cv_id int, type_id int, pathdistance int); + CREATE INDEX tmp_cvtpath1 ON tmpcvtermpath(object_id, subject_id); + + SELECT INTO rtn _fill_cvtermpath4root2detect_cycle(rootid, cvid); + IF (rtn > 0) THEN + DROP TABLE tmpcvtermpath; + RETURN rtn; + END IF; + DROP TABLE tmpcvtermpath; + RETURN 0; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION get_cycle_cvterm_ids(INTEGER) RETURNS SETOF INTEGER AS +' +DECLARE + cvid alias for $1; + root cvterm%ROWTYPE; + rtn int; +BEGIN + + + FOR root IN SELECT DISTINCT t.* from cvterm t WHERE cv_id = cvid LOOP + SELECT INTO rtn get_cycle_cvterm_id(cvid,root.cvterm_id); + IF (rtn > 0) THEN + RETURN NEXT rtn; + END IF; + END LOOP; + RETURN; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION get_cycle_cvterm_id(INTEGER) RETURNS INTEGER AS +' +DECLARE + cvid alias for $1; + root cvterm%ROWTYPE; + rtn int; +BEGIN + + CREATE TEMP TABLE tmpcvtermpath(object_id int, subject_id int, cv_id int, type_id int, pathdistance int); + CREATE INDEX tmp_cvtpath1 ON tmpcvtermpath(object_id, subject_id); + + FOR root IN SELECT DISTINCT t.* from cvterm t LEFT JOIN cvterm_relationship r ON (t.cvterm_id = r.subject_id) INNER JOIN cvterm_relationship r2 ON (t.cvterm_id = r2.object_id) WHERE t.cv_id = cvid AND r.subject_id is null LOOP + SELECT INTO rtn _fill_cvtermpath4root2detect_cycle(root.cvterm_id, root.cv_id); + IF (rtn > 0) THEN + DROP TABLE tmpcvtermpath; + RETURN rtn; + END IF; + END LOOP; + DROP TABLE tmpcvtermpath; + RETURN 0; +END; +' +LANGUAGE 'plpgsql'; + +CREATE OR REPLACE FUNCTION get_cycle_cvterm_id(cv.name%TYPE) RETURNS INTEGER AS +' +DECLARE + cvname alias for $1; + cv_id int; + rtn int; +BEGIN + + SELECT INTO cv_id cv.cv_id from cv WHERE cv.name = cvname; + SELECT INTO rtn get_cycle_cvterm_id(cv_id); + + RETURN rtn; +END; +' +LANGUAGE 'plpgsql'; +-- $Id: pub.sql,v 1.27 2007-02-19 20:50:44 briano Exp $ +-- ========================================== +-- Chado pub module +-- +-- ================================================================= +-- Dependencies: +-- +-- :import cvterm from cv +-- :import dbxref from general +-- ================================================================= + +-- ================================================ +-- TABLE: pub +-- ================================================ + +create table pub ( + pub_id serial not null, + primary key (pub_id), + title text, + volumetitle text, + volume varchar(255), + series_name varchar(255), + issue varchar(255), + pyear varchar(255), + pages varchar(255), + miniref varchar(255), + uniquename text not null, + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + is_obsolete boolean default 'false', + publisher varchar(255), + pubplace varchar(255), + constraint pub_c1 unique (uniquename) +); +CREATE INDEX pub_idx1 ON pub (type_id); + +COMMENT ON TABLE pub IS 'A documented provenance artefact - publications, +documents, personal communication.'; +COMMENT ON COLUMN pub.title IS 'Descriptive general heading.'; +COMMENT ON COLUMN pub.volumetitle IS 'Title of part if one of a series.'; +COMMENT ON COLUMN pub.series_name IS 'Full name of (journal) series.'; +COMMENT ON COLUMN pub.pages IS 'Page number range[s], e.g. 457--459, viii + 664pp, lv--lvii.'; +COMMENT ON COLUMN pub.type_id IS 'The type of the publication (book, journal, poem, graffiti, etc). Uses pub cv.'; + +-- ================================================ +-- TABLE: pub_relationship +-- ================================================ + +create table pub_relationship ( + pub_relationship_id serial not null, + primary key (pub_relationship_id), + subject_id int not null, + foreign key (subject_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED, + object_id int not null, + foreign key (object_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED, + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + + constraint pub_relationship_c1 unique (subject_id,object_id,type_id) +); +create index pub_relationship_idx1 on pub_relationship (subject_id); +create index pub_relationship_idx2 on pub_relationship (object_id); +create index pub_relationship_idx3 on pub_relationship (type_id); + +COMMENT ON TABLE pub_relationship IS 'Handle relationships between +publications, e.g. when one publication makes others obsolete, when one +publication contains errata with respect to other publication(s), or +when one publication also appears in another pub.'; + +-- ================================================ +-- TABLE: pub_dbxref +-- ================================================ + +create table pub_dbxref ( + pub_dbxref_id serial not null, + primary key (pub_dbxref_id), + pub_id int not null, + foreign key (pub_id) references pub (pub_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 pub_dbxref_c1 unique (pub_id,dbxref_id) +); +create index pub_dbxref_idx1 on pub_dbxref (pub_id); +create index pub_dbxref_idx2 on pub_dbxref (dbxref_id); + +COMMENT ON TABLE pub_dbxref IS 'Handle links to repositories, +e.g. Pubmed, Biosis, zoorec, OCLC, Medline, ISSN, coden...'; + + +-- ================================================ +-- TABLE: pubauthor +-- ================================================ + +create table pubauthor ( + pubauthor_id serial not null, + primary key (pubauthor_id), + pub_id int not null, + foreign key (pub_id) references pub (pub_id) on delete cascade INITIALLY DEFERRED, + rank int not null, + editor boolean default 'false', + surname varchar(100) not null, + givennames varchar(100), + suffix varchar(100), + + constraint pubauthor_c1 unique (pub_id, rank) +); +create index pubauthor_idx2 on pubauthor (pub_id); + +COMMENT ON TABLE pubauthor IS 'An author for a publication. Note the denormalisation (hence lack of _ in table name) - this is deliberate as it is in general too hard to assign IDs to authors.'; +COMMENT ON COLUMN pubauthor.givennames IS 'First name, initials'; +COMMENT ON COLUMN pubauthor.suffix IS 'Jr., Sr., etc'; +COMMENT ON COLUMN pubauthor.rank IS 'Order of author in author list for this pub - order is important.'; +COMMENT ON COLUMN pubauthor.editor IS 'Indicates whether the author is an editor for linked publication. Note: this is a boolean field but does not follow the normal chado convention for naming booleans.'; + + +-- ================================================ +-- TABLE: pubprop +-- ================================================ + +create table pubprop ( + pubprop_id serial not null, + primary key (pubprop_id), + pub_id int not null, + foreign key (pub_id) references pub (pub_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, + rank integer, + + constraint pubprop_c1 unique (pub_id,type_id,rank) +); +create index pubprop_idx1 on pubprop (pub_id); +create index pubprop_idx2 on pubprop (type_id); + +COMMENT ON TABLE pubprop IS 'Property-value pairs for a pub. Follows standard chado pattern.'; +-- $Id: organism.sql,v 1.19 2007-04-01 18:45:41 briano Exp $ +-- ========================================== +-- Chado organism module +-- +-- ============ +-- DEPENDENCIES +-- ============ +-- :import cvterm from cv +-- :import dbxref from general +-- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + +-- ================================================ +-- TABLE: organism +-- ================================================ + +create table organism ( + organism_id serial not null, + primary key (organism_id), + abbreviation varchar(255) null, + genus varchar(255) not null, + species varchar(255) not null, + common_name varchar(255) null, + comment text null, + constraint organism_c1 unique (genus,species) +); + +COMMENT ON TABLE organism IS 'The organismal taxonomic +classification. Note that phylogenies are represented using the +phylogeny module, and taxonomies can be represented using the cvterm +module or the phylogeny module.'; + +COMMENT ON COLUMN organism.species IS 'A type of organism is always +uniquely identified by genus and species. When mapping from the NCBI +taxonomy names.dmp file, this column must be used where it +is present, as the common_name column is not always unique (e.g. environmental +samples). If a particular strain or subspecies is to be represented, +this is appended onto the species name. Follows standard NCBI taxonomy +pattern.'; + +-- ================================================ +-- TABLE: organism_dbxref +-- ================================================ + +create table organism_dbxref ( + organism_dbxref_id serial not null, + primary key (organism_dbxref_id), + organism_id int not null, + foreign key (organism_id) references organism (organism_id) on delete cascade INITIALLY DEFERRED, + dbxref_id int not null, + foreign key (dbxref_id) references dbxref (dbxref_id) on delete cascade INITIALLY DEFERRED, + constraint organism_dbxref_c1 unique (organism_id,dbxref_id) +); +create index organism_dbxref_idx1 on organism_dbxref (organism_id); +create index organism_dbxref_idx2 on organism_dbxref (dbxref_id); + +-- ================================================ +-- TABLE: organismprop +-- ================================================ + +create table organismprop ( + organismprop_id serial not null, + primary key (organismprop_id), + organism_id int not null, + foreign key (organism_id) references organism (organism_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 organismprop_c1 unique (organism_id,type_id,rank) +); +create index organismprop_idx1 on organismprop (organism_id); +create index organismprop_idx2 on organismprop (type_id); + +COMMENT ON TABLE organismprop IS 'Tag-value properties - follows standard chado model.'; + + +CREATE OR REPLACE FUNCTION get_organism_id(VARCHAR,VARCHAR) RETURNS INT + AS ' + SELECT organism_id + FROM organism + WHERE genus=$1 + AND species=$2 + ' LANGUAGE 'sql'; + +CREATE OR REPLACE FUNCTION get_organism_id(VARCHAR) RETURNS INT + AS ' +SELECT organism_id + FROM organism + WHERE genus=substring($1,1,position('' '' IN $1)-1) + AND species=substring($1,position('' '' IN $1)+1) + ' LANGUAGE 'sql'; + +CREATE OR REPLACE FUNCTION get_organism_id_abbrev(VARCHAR) RETURNS INT + AS ' +SELECT organism_id + FROM organism + WHERE substr(genus,1,1)=substring($1,1,1) + AND species=substring($1,position('' '' IN $1)+1) + ' LANGUAGE 'sql'; + +CREATE OR REPLACE FUNCTION store_organism (VARCHAR,VARCHAR,VARCHAR) + RETURNS INT AS +'DECLARE + v_genus ALIAS FOR $1; + v_species ALIAS FOR $2; + v_common_name ALIAS FOR $3; + + v_organism_id INTEGER; + BEGIN + SELECT INTO v_organism_id organism_id + FROM organism + WHERE genus=v_genus AND + species=v_species; + IF NOT FOUND THEN + INSERT INTO organism + (genus,species,common_name) + VALUES + (v_genus,v_species,v_common_name); + RETURN currval(''organism_organism_id_seq''); + ELSE + UPDATE organism + SET common_name=v_common_name + WHERE organism_id = v_organism_id; + END IF; + RETURN v_organism_id; + END; +' LANGUAGE 'plpgsql'; + +-- $Id: sequence.sql,v 1.69 2009-05-14 02:44:23 scottcain Exp $ +-- ========================================== +-- Chado sequence module +-- +-- ================================================================= +-- Dependencies: +-- +-- :import cvterm from cv +-- :import pub from pub +-- :import organism from organism +-- :import dbxref from general +-- ================================================================= + +-- ================================================ +-- TABLE: feature +-- ================================================ + +create table feature ( + feature_id serial not null, + primary key (feature_id), + dbxref_id int, + foreign key (dbxref_id) references dbxref (dbxref_id) on delete set null INITIALLY DEFERRED, + organism_id int not null, + foreign key (organism_id) references organism (organism_id) on delete cascade INITIALLY DEFERRED, + name varchar(255), + uniquename text not null, + residues text, + seqlen int, + md5checksum char(32), + type_id int not null, + foreign key (type_id) references cvterm (cvterm_id) on delete cascade INITIALLY DEFERRED, + is_analysis boolean not null default 'false', + is_obsolete boolean not null default 'false', + timeaccessioned timestamp not null default current_timestamp, + timelastmodified timestamp not null default current_timestamp, + constraint feature_c1 unique (organism_id,uniquename,type_id) +); +create sequence feature_uniquename_seq; +create index feature_name_ind1 on feature(name); +create index feature_idx1 on feature (dbxref_id); +create index feature_idx2 on feature (organism_id); +create index feature_idx3 on feature (type_id); +create index feature_idx4 on feature (uniquename); +create index feature_idx5 on feature (lower(name)); + +ALTER TABLE feature ALTER residues SET STORAGE EXTERNAL; + +COMMENT ON TABLE feature IS 'A feature is a biological sequence or a +section of a biological sequence, or a collection of such +sections. Examples include genes, exons, transcripts, regulatory +regions, polypeptides, protein domains, chromosome sequences, sequence +variations, cross-genome match regions such as hits and HSPs and so +on; see the Sequence Ontology for more. The combination of +organism_id, uniquename and type_id should be unique.'; + +COMMENT ON COLUMN feature.dbxref_id IS 'An optional primary public stable +identifier for this feature. Secondary identifiers and external +dbxrefs go in the table feature_dbxref.'; + +COMMENT ON COLUMN feature.organism_id IS 'The organism to which this feature +belongs. This column is mandatory.'; + +COMMENT ON COLUMN feature.name IS 'The optional human-readable common name for +a feature, for display purposes.'; + +COMMENT ON COLUMN feature.uniquename IS 'The unique name for a feature; may +not be necessarily be particularly human-readable, although this is +preferred. This name must be unique for this type of feature within +this organism.'; + +COMMENT ON COLUMN feature.residues IS 'A sequence of alphabetic characters +representing biological residues (nucleic acids, amino acids). This +column does not need to be manifested for all features; it is optional +for features such as exons where the residues can be derived from the +featureloc. It is recommended that the value for this column be +manifested for features which may may non-contiguous sublocations (e.g. +transcripts), since derivation at query time is non-trivial. For +expressed sequence, the DNA sequence should be used rather than the +RNA sequence. The default storage method for the residues column is +EXTERNAL, which will store it uncompressed to make substring operations +faster.'; + +COMMENT ON COLUMN feature.seqlen IS 'The length of the residue feature. See +column:residues. This column is partially redundant with the residues +column, and also with featureloc. This column is required because the +location may be unknown and the residue sequence may not be +manifested, yet it may be desirable to store and query the length of +the feature. The seqlen should always be manifested where the length +of the sequence is known.'; + +COMMENT ON COLUMN feature.md5checksum IS 'The 32-character checksum of the sequence, +calculated using the MD5 algorithm. This is practically guaranteed to +be unique for any feature. This column thus acts as a unique +identifier on the mathematical sequence.'; + +COMMENT ON COLUMN feature.type_id IS 'A required reference to a table:cvterm +giving the feature type. This will typically be a Sequence Ontology +identifier. This column is thus used to subclass the feature table.'; + +COMMENT ON COLUMN feature.is_analysis IS 'Boolean indicating whether this +feature is annotated or the result of an automated analysis. Analysis +results also use the companalysis module. Note that the dividing line +between analysis and annotation may be fuzzy, this should be determined on +a per-project basis in a consistent manner. One requirement is that +there should only be one non-analysis version of each wild-type gene +feature in a genome, whereas the same gene feature can be predicted +multiple times in different analyses.'; + +COMMENT ON COLUMN feature.is_obsolete IS 'Boolean indicating whether this +feature has been obsoleted. Some chado instances may choose to simply +remove the feature altogether, others may choose to keep an obsolete +row in the table.'; + +COMMENT ON COLUMN feature.timeaccessioned IS 'For handling object +accession or modification timestamps (as opposed to database auditing data, +handled elsewhere). The expectation is that these fields would be +available to software interacting with chado.'; + +COMMENT ON COLUMN feature.timelastmodified IS 'For handling object +accession or modification timestamps (as opposed to database auditing data, +handled elsewhere). The expectation is that these fields would be +available to software interacting with chado.'; + +--- COMMENT ON INDEX feature_c1 IS 'Any feature can be globally identified +--- by the combination of organism, uniquename and feature type'; + +-- ================================================ +-- TABLE: featureloc +-- ================================================ + +create table featureloc ( + featureloc_id serial not null, + primary key (featureloc_id), + feature_id int not null, + foreign key (feature_id) references feature (feature_id) on delete cascade INITIALLY DEFERRED, + srcfeature_id int, + foreign key (srcfeature_id) references feature (feature_id) on delete set null INITIALLY DEFERRED, + fmin int, + is_fmin_partial boolean not null default 'false', + fmax int, + is_fmax_partial boolean not null default 'false', + strand smallint, + phase int, + residue_info text, + locgroup int not null default 0, + rank int not null default 0, + constraint featureloc_c1 unique (feature_id,locgroup,rank), + constraint featureloc_c2 check (fmin <= fmax) +); +create index featureloc_idx1 on featureloc (feature_id); +create index featureloc_idx2 on featureloc (srcfeature_id); +create index featureloc_idx3 on featureloc (srcfeature_id,fmin,fmax); + +COMMENT ON TABLE featureloc IS 'The location of a feature relative to +another feature. Important: interbase coordinates are used. This is +vital as it allows us to represent zero-length features e.g. splice +sites, insertion points without an awkward fuzzy system. Features +typically have exactly ONE location, but this need not be the +case. Some features may not be localized (e.g. a gene that has been +characterized genetically but no sequence or molecular information is +available). Note on multiple locations: Each feature can have 0 or +more locations. Multiple locations do NOT indicate non-contiguous +locations (if a feature such as a transcript has a non-contiguous +location, then the subfeatures such as exons should always be +manifested). Instead, multiple featurelocs for a feature designate +alternate locations or grouped locations; for instance, a feature +designating a blast hit or hsp will have two locations, one on the +query feature, one on the subject feature. Features representing +sequence variation could have alternate locations instantiated on a +feature on the mutant strain. The column:rank is used to +differentiate these different locations. Reflexive locations should +never be stored - th... [truncated message content] |
From: <sco...@us...> - 2012-02-28 21:26:33
|
Revision: 25255 http://gmod.svn.sourceforge.net/gmod/?rev=25255&view=rev Author: scottcain Date: 2012-02-28 21:26:20 +0000 (Tue, 28 Feb 2012) Log Message: ----------- Changed all perl script #! lines to use "/usr/bin/env perl" instead of the mishmash that was being used in various scripts. Modified Paths: -------------- schema/trunk/chado/Changes schema/trunk/chado/bin/chado-build-schema.pl schema/trunk/chado/bin/chado-fix-flybase-instance.pl schema/trunk/chado/bin/create-bridge-sql.pl schema/trunk/chado/bin/create-so-bridge.pl schema/trunk/chado/bin/ddltrans schema/trunk/chado/bin/gencode2sql.pl schema/trunk/chado/bin/gmod_add_organism.pl schema/trunk/chado/bin/gmod_apollo_triggers.pl schema/trunk/chado/bin/gmod_bulk_load_pubmed.pl schema/trunk/chado/bin/gmod_chado_properties.pl schema/trunk/chado/bin/gmod_dump_gff3.pl schema/trunk/chado/bin/gmod_extract_dbxref_from_gff.pl schema/trunk/chado/bin/gmod_fasta2gff3.pl schema/trunk/chado/bin/gmod_gff3_preprocessor.pl schema/trunk/chado/bin/gmod_make_cvtermpath.pl schema/trunk/chado/bin/gmod_make_gff_from_dbxref.pl schema/trunk/chado/bin/gmod_materialize_gbrowse_views.pl schema/trunk/chado/bin/gmod_materialized_view_tool.pl schema/trunk/chado/bin/gmod_sort_gff3.pl schema/trunk/chado/bin/gmod_update_chado.pl schema/trunk/chado/bin/interactions2SIF.pl schema/trunk/chado/bin/load_ncbi_taxonomy.pl schema/trunk/chado/bin/load_taxonomy_cvterms.pl schema/trunk/chado/bin/make_cvtermpath.pl schema/trunk/chado/bin/make_partial_indexes.pl schema/trunk/chado/bin/pg2cdbi.pl schema/trunk/chado/bin/pg2cdbi_viaTT.pl schema/trunk/chado/bin/pg2diagram.pl schema/trunk/chado/bin/pg2graphviz.pl schema/trunk/chado/bin/pg2graphviz_svg.pl schema/trunk/chado/bin/pg2html.pl schema/trunk/chado/bin/test_load.pl schema/trunk/chado/bin/ucsc_genes2gff.pl schema/trunk/chado/bin/ucsc_snp2gff.pl schema/trunk/chado/cas-utils/cgi-bin/apollo_request_region.pl schema/trunk/chado/cas-utils/cgi-bin/upload_game.pl schema/trunk/chado/chaos-xml/bin/cx-chadoxml2chaos.pl schema/trunk/chado/chaos-xml/bin/cx-chaos-report.pl schema/trunk/chado/chaos-xml/bin/cx-download-enscore.pl schema/trunk/chado/chaos-xml/bin/cx-enscore2chaos.pl schema/trunk/chado/chaos-xml/bin/cx-genbank2chaos.pl schema/trunk/chado/load/bin/load_affymetrix.pl schema/trunk/chado/load/bin/load_affyxls.pl schema/trunk/chado/load/bin/load_taxonomy.pl schema/trunk/chado/load/bin/new_gff_loader.pl schema/trunk/chado/modules/audit/make_audit_ddl schema/trunk/chado/modules/bin/makedep.pl schema/trunk/chado/modules/bin/mk.pl schema/trunk/chado/modules/cv/bridges/bin/create-bridge-sql.pl schema/trunk/chado/modules/sequence/apollo-bridge/insert_ad_doc_cv.pl schema/trunk/chado/modules/sequence/bdgp/bin/create-so-layer.pl schema/trunk/chado/modules/sequence/bridges/bin/create-sofa-bridge.pl schema/trunk/chado/schemas/generate_diff_dirs.pl schema/trunk/chado/soi/cgi/get_xml.pl schema/trunk/chado/soi/scripts/dump_fasta.pl schema/trunk/chado/soi/scripts/dump_segment.pl schema/trunk/chado/soi/scripts/generegion.pl schema/trunk/chado/soi/t/t_soi_parse_intersect schema/trunk/chado/src/test/perl/scripts/add-implicit-loc.pl schema/trunk/chado/src/test/perl/scripts/fgraph.pl Modified: schema/trunk/chado/Changes =================================================================== --- schema/trunk/chado/Changes 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/Changes 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,6 +1,8 @@ Version 1.23 Thu Dec 1 14:45:22 EST 2011 * Added $VERSION to Bio::GMOD::DB::Config so that when installing Bio::DB::Das::Chado it would be happy. +* Changed all perl script #! lines to use /usr/bin/env perl instead of the +mishmash that was being used in various scripts. Version 1.22 Thu Nov 17 12:48:10 EST 2011 * Created a script to automatically create the schemas dirs for a release and Modified: schema/trunk/chado/bin/chado-build-schema.pl =================================================================== --- schema/trunk/chado/bin/chado-build-schema.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/chado-build-schema.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,6 +1,7 @@ -#!/usr/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use Data::Stag; use FileHandle; use Getopt::Long; Modified: schema/trunk/chado/bin/chado-fix-flybase-instance.pl =================================================================== --- schema/trunk/chado/bin/chado-fix-flybase-instance.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/chado-fix-flybase-instance.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,6 +1,7 @@ -#!/usr/local/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use DBIx::DBStag; use Getopt::Long; Modified: schema/trunk/chado/bin/create-bridge-sql.pl =================================================================== --- schema/trunk/chado/bin/create-bridge-sql.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/create-bridge-sql.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,8 +1,9 @@ -#!/usr/local/bin/perl -w +#!/usr/bin/env perl # usage docs at end of file use strict; +use warnings; use Data::Stag; my %relation_type_h = (); Modified: schema/trunk/chado/bin/create-so-bridge.pl =================================================================== --- schema/trunk/chado/bin/create-so-bridge.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/create-so-bridge.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,6 +1,7 @@ -#!/usr/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use Carp; use DBI; Modified: schema/trunk/chado/bin/ddltrans =================================================================== --- schema/trunk/chado/bin/ddltrans 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/ddltrans 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/local/bin/perl +#!/usr/bin/env perl # DDLTRANS: Transform sql schema definitions into various useful formats. # (DDL=data definition language, the schema defining subset of SQL -- create table, etc.) Modified: schema/trunk/chado/bin/gencode2sql.pl =================================================================== --- schema/trunk/chado/bin/gencode2sql.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/gencode2sql.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,6 +1,7 @@ -#!/usr/bin/perl -w +#!/usr/bin/env perl #This is old code and hasn't been tested! use strict; +use warnings; my @nucs = qw(T C A G); my $x = 0; Modified: schema/trunk/chado/bin/gmod_add_organism.pl =================================================================== --- schema/trunk/chado/bin/gmod_add_organism.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/gmod_add_organism.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,5 +1,6 @@ -#!/usr/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use Getopt::Long; #use lib '/home/cain/cvs_stuff/schema/chado/lib'; Modified: schema/trunk/chado/bin/gmod_apollo_triggers.pl =================================================================== --- schema/trunk/chado/bin/gmod_apollo_triggers.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/gmod_apollo_triggers.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,5 +1,6 @@ -#!/usr/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use Bio::GMOD::Config; use Bio::GMOD::DB::Config; Modified: schema/trunk/chado/bin/gmod_bulk_load_pubmed.pl =================================================================== --- schema/trunk/chado/bin/gmod_bulk_load_pubmed.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/gmod_bulk_load_pubmed.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -91,8 +91,9 @@ =cut -#! /usr/bin/perl +#! /usr/bin/env perl use strict; +use warnings; use Bio::GMOD::Config; Modified: schema/trunk/chado/bin/gmod_chado_properties.pl =================================================================== --- schema/trunk/chado/bin/gmod_chado_properties.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/gmod_chado_properties.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,5 +1,6 @@ -#!/usr/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use Getopt::Long; #use lib '/home/cain/cvs_stuff/schema/chado/lib'; Modified: schema/trunk/chado/bin/gmod_dump_gff3.pl =================================================================== --- schema/trunk/chado/bin/gmod_dump_gff3.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/gmod_dump_gff3.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,5 +1,6 @@ -#!/usr/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use DBI; use Bio::GMOD::Config; Modified: schema/trunk/chado/bin/gmod_extract_dbxref_from_gff.pl =================================================================== --- schema/trunk/chado/bin/gmod_extract_dbxref_from_gff.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/gmod_extract_dbxref_from_gff.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl use strict; use warnings; use URI::Escape; Modified: schema/trunk/chado/bin/gmod_fasta2gff3.pl =================================================================== --- schema/trunk/chado/bin/gmod_fasta2gff3.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/gmod_fasta2gff3.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,5 +1,6 @@ -#!/usr/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use Getopt::Long; use Bio::DB::Fasta; Modified: schema/trunk/chado/bin/gmod_gff3_preprocessor.pl =================================================================== --- schema/trunk/chado/bin/gmod_gff3_preprocessor.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/gmod_gff3_preprocessor.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,5 +1,6 @@ -#!/usr/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use Bio::FeatureIO; use Getopt::Long; Modified: schema/trunk/chado/bin/gmod_make_cvtermpath.pl =================================================================== --- schema/trunk/chado/bin/gmod_make_cvtermpath.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/gmod_make_cvtermpath.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl =pod Modified: schema/trunk/chado/bin/gmod_make_gff_from_dbxref.pl =================================================================== --- schema/trunk/chado/bin/gmod_make_gff_from_dbxref.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/gmod_make_gff_from_dbxref.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl use strict; use warnings; Modified: schema/trunk/chado/bin/gmod_materialize_gbrowse_views.pl =================================================================== --- schema/trunk/chado/bin/gmod_materialize_gbrowse_views.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/gmod_materialize_gbrowse_views.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,5 +1,6 @@ -#!/usr/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use Getopt::Long; use Bio::GMOD::Config; use Bio::GMOD::DB::Config; Modified: schema/trunk/chado/bin/gmod_materialized_view_tool.pl =================================================================== --- schema/trunk/chado/bin/gmod_materialized_view_tool.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/gmod_materialized_view_tool.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,6 +1,7 @@ -#!/usr/bin/perl +#!/usr/bin/env perl use strict; +use warnings; use Bio::GMOD::Config; use Bio::GMOD::DB::Config; Modified: schema/trunk/chado/bin/gmod_sort_gff3.pl =================================================================== --- schema/trunk/chado/bin/gmod_sort_gff3.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/gmod_sort_gff3.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,5 +1,6 @@ -#!/usr/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use Getopt::Long; Modified: schema/trunk/chado/bin/gmod_update_chado.pl =================================================================== --- schema/trunk/chado/bin/gmod_update_chado.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/gmod_update_chado.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,5 +1,6 @@ -#!/usr/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use Getopt::Long; #use lib '/home/cain/cvs_stuff/schema/chado/lib'; Modified: schema/trunk/chado/bin/interactions2SIF.pl =================================================================== --- schema/trunk/chado/bin/interactions2SIF.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/interactions2SIF.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,5 +1,6 @@ -#!/usr/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use DBI; use Bio::GMOD::Config; Modified: schema/trunk/chado/bin/load_ncbi_taxonomy.pl =================================================================== --- schema/trunk/chado/bin/load_ncbi_taxonomy.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/load_ncbi_taxonomy.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -138,7 +138,7 @@ =cut -#! /usr/bin/perl +#! /usr/bin/env perl use strict; Modified: schema/trunk/chado/bin/load_taxonomy_cvterms.pl =================================================================== --- schema/trunk/chado/bin/load_taxonomy_cvterms.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/load_taxonomy_cvterms.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -49,7 +49,7 @@ =cut -#!/usr/bin/perl +#!/usr/bin/env perl use strict; use Bio::GMOD::Config; Modified: schema/trunk/chado/bin/make_cvtermpath.pl =================================================================== --- schema/trunk/chado/bin/make_cvtermpath.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/make_cvtermpath.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl # assumes empty password--that should be fixed Modified: schema/trunk/chado/bin/make_partial_indexes.pl =================================================================== --- schema/trunk/chado/bin/make_partial_indexes.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/make_partial_indexes.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,5 +1,6 @@ -#!/usr/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use DBI; Modified: schema/trunk/chado/bin/pg2cdbi.pl =================================================================== --- schema/trunk/chado/bin/pg2cdbi.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/pg2cdbi.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl # vim: set ft=perl ts=2 expandtab: use strict; Modified: schema/trunk/chado/bin/pg2cdbi_viaTT.pl =================================================================== --- schema/trunk/chado/bin/pg2cdbi_viaTT.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/pg2cdbi_viaTT.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl # vim: set ft=perl ts=2 expandtab: use strict; Modified: schema/trunk/chado/bin/pg2diagram.pl =================================================================== --- schema/trunk/chado/bin/pg2diagram.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/pg2diagram.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl use SQL::Translator; use Data::Dumper; Modified: schema/trunk/chado/bin/pg2graphviz.pl =================================================================== --- schema/trunk/chado/bin/pg2graphviz.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/pg2graphviz.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl use SQL::Translator; use Data::Dumper; Modified: schema/trunk/chado/bin/pg2graphviz_svg.pl =================================================================== --- schema/trunk/chado/bin/pg2graphviz_svg.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/pg2graphviz_svg.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl use SQL::Translator; use Data::Dumper; Modified: schema/trunk/chado/bin/pg2html.pl =================================================================== --- schema/trunk/chado/bin/pg2html.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/pg2html.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl use SQL::Translator; use Data::Dumper; Modified: schema/trunk/chado/bin/test_load.pl =================================================================== --- schema/trunk/chado/bin/test_load.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/test_load.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl # # A simple script to create the Chado database and tables. Modified: schema/trunk/chado/bin/ucsc_genes2gff.pl =================================================================== --- schema/trunk/chado/bin/ucsc_genes2gff.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/ucsc_genes2gff.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl # convert UCSC gene files into GFF3 data Modified: schema/trunk/chado/bin/ucsc_snp2gff.pl =================================================================== --- schema/trunk/chado/bin/ucsc_snp2gff.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/bin/ucsc_snp2gff.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl # convert UCSC gene files into GFF3 data Modified: schema/trunk/chado/cas-utils/cgi-bin/apollo_request_region.pl =================================================================== --- schema/trunk/chado/cas-utils/cgi-bin/apollo_request_region.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/cas-utils/cgi-bin/apollo_request_region.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl use warnings; use strict; Modified: schema/trunk/chado/cas-utils/cgi-bin/upload_game.pl =================================================================== --- schema/trunk/chado/cas-utils/cgi-bin/upload_game.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/cas-utils/cgi-bin/upload_game.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl use strict; use warnings; use Time::HiRes qw/gettimeofday/; Modified: schema/trunk/chado/chaos-xml/bin/cx-chadoxml2chaos.pl =================================================================== --- schema/trunk/chado/chaos-xml/bin/cx-chadoxml2chaos.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/chaos-xml/bin/cx-chadoxml2chaos.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,5 +1,6 @@ -#!/usr/local/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use Getopt::Long; use FileHandle; Modified: schema/trunk/chado/chaos-xml/bin/cx-chaos-report.pl =================================================================== --- schema/trunk/chado/chaos-xml/bin/cx-chaos-report.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/chaos-xml/bin/cx-chaos-report.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,5 +1,6 @@ -#!/usr/local/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use Getopt::Long; use Bio::Chaos::ChaosGraph; Modified: schema/trunk/chado/chaos-xml/bin/cx-download-enscore.pl =================================================================== --- schema/trunk/chado/chaos-xml/bin/cx-download-enscore.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/chaos-xml/bin/cx-download-enscore.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,6 +1,7 @@ -#!/usr/local/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use Getopt::Long; use File::Glob ':glob'; Modified: schema/trunk/chado/chaos-xml/bin/cx-enscore2chaos.pl =================================================================== --- schema/trunk/chado/chaos-xml/bin/cx-enscore2chaos.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/chaos-xml/bin/cx-enscore2chaos.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,5 +1,6 @@ -#!/usr/local/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use Bio::Chaos::ChaosGraph; use Bio::Chaos::FeatureUtil qw(:all); Modified: schema/trunk/chado/chaos-xml/bin/cx-genbank2chaos.pl =================================================================== --- schema/trunk/chado/chaos-xml/bin/cx-genbank2chaos.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/chaos-xml/bin/cx-genbank2chaos.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,5 +1,6 @@ -#!/usr/local/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use Bio::Chaos::ChaosGraph; use Bio::SeqIO; Modified: schema/trunk/chado/load/bin/load_affymetrix.pl =================================================================== --- schema/trunk/chado/load/bin/load_affymetrix.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/load/bin/load_affymetrix.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl use strict; use lib 'lib'; Modified: schema/trunk/chado/load/bin/load_affyxls.pl =================================================================== --- schema/trunk/chado/load/bin/load_affyxls.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/load/bin/load_affyxls.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl package Bio::Chado::CDBI::Affymetrixdchip; use lib 'lib'; Modified: schema/trunk/chado/load/bin/load_taxonomy.pl =================================================================== --- schema/trunk/chado/load/bin/load_taxonomy.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/load/bin/load_taxonomy.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl #$Id: load_taxonomy.pl,v 1.1 2006-04-17 05:22:22 allenday Exp $ #download from ftp://ftp.ncbi.nih.gov/pub/taxonomy/ use strict; Modified: schema/trunk/chado/load/bin/new_gff_loader.pl =================================================================== --- schema/trunk/chado/load/bin/new_gff_loader.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/load/bin/new_gff_loader.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,5 +1,6 @@ -#!/usr/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use Bio::Tools::GFF; use Bio::SeqIO; use Getopt::Long; Modified: schema/trunk/chado/modules/audit/make_audit_ddl =================================================================== --- schema/trunk/chado/modules/audit/make_audit_ddl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/modules/audit/make_audit_ddl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl # # make_audit_ddl # Modified: schema/trunk/chado/modules/bin/makedep.pl =================================================================== --- schema/trunk/chado/modules/bin/makedep.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/modules/bin/makedep.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/local/bin/perl -w +#!/usr/bin/env perl # # (c) Hilmar Lapp, hlapp at gmx.net, 2007. # @@ -6,6 +6,7 @@ # as Perl itself. Consult the Perl Artistic License. use strict; +use warnings; use Getopt::Long; ################################################################# Modified: schema/trunk/chado/modules/bin/mk.pl =================================================================== --- schema/trunk/chado/modules/bin/mk.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/modules/bin/mk.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/local/bin/perl +#!/usr/bin/env perl foreach $f (@ARGV) { open(F,$f) || die("no $f"); Modified: schema/trunk/chado/modules/cv/bridges/bin/create-bridge-sql.pl =================================================================== --- schema/trunk/chado/modules/cv/bridges/bin/create-bridge-sql.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/modules/cv/bridges/bin/create-bridge-sql.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,6 +1,7 @@ -#!/usr/local/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use Data::Stag; my %relation_type_h = (); Modified: schema/trunk/chado/modules/sequence/apollo-bridge/insert_ad_doc_cv.pl =================================================================== --- schema/trunk/chado/modules/sequence/apollo-bridge/insert_ad_doc_cv.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/modules/sequence/apollo-bridge/insert_ad_doc_cv.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,5 +1,6 @@ -#!/usr/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; open ONTO, "ad_hoc_cv" or die "couldn't open ad_hoc_cv:$!\n"; open INSERTS, ">cv_inserts.sql" or die "couldn't open cv_inserts.sql: $!\n"; Modified: schema/trunk/chado/modules/sequence/bdgp/bin/create-so-layer.pl =================================================================== --- schema/trunk/chado/modules/sequence/bdgp/bin/create-so-layer.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/modules/sequence/bdgp/bin/create-so-layer.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,6 +1,7 @@ -#!/usr/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use Carp; use DBI; Modified: schema/trunk/chado/modules/sequence/bridges/bin/create-sofa-bridge.pl =================================================================== --- schema/trunk/chado/modules/sequence/bridges/bin/create-sofa-bridge.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/modules/sequence/bridges/bin/create-sofa-bridge.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,6 +1,7 @@ -#!/usr/local/bin/perl -w +#!/usr/bin/env perl use strict; +use warnings; use Carp; use DBI; Modified: schema/trunk/chado/schemas/generate_diff_dirs.pl =================================================================== --- schema/trunk/chado/schemas/generate_diff_dirs.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/schemas/generate_diff_dirs.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/bin/perl +#!/usr/bin/env perl use strict; use warnings; use Scalar::Util qw(looks_like_number); Modified: schema/trunk/chado/soi/cgi/get_xml.pl =================================================================== --- schema/trunk/chado/soi/cgi/get_xml.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/soi/cgi/get_xml.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/local/bin/perl +#!/usr/bin/env perl BEGIN{ eval{do "config.pl"}; Modified: schema/trunk/chado/soi/scripts/dump_fasta.pl =================================================================== --- schema/trunk/chado/soi/scripts/dump_fasta.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/soi/scripts/dump_fasta.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/local/bin/perl +#!/usr/bin/env perl # generegion_soi.pl Modified: schema/trunk/chado/soi/scripts/dump_segment.pl =================================================================== --- schema/trunk/chado/soi/scripts/dump_segment.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/soi/scripts/dump_segment.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/local/bin/perl +#!/usr/bin/env perl use FindBin qw($RealBin); use lib (($ENV{SOI_ROOT}) || Modified: schema/trunk/chado/soi/scripts/generegion.pl =================================================================== --- schema/trunk/chado/soi/scripts/generegion.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/soi/scripts/generegion.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/local/bin/perl +#!/usr/bin/env perl # generegion_soi.pl Modified: schema/trunk/chado/soi/t/t_soi_parse_intersect =================================================================== --- schema/trunk/chado/soi/t/t_soi_parse_intersect 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/soi/t/t_soi_parse_intersect 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/local/bin/perl -w +#!/usr/bin/env perl use lib "../"; BEGIN { @@ -11,6 +11,7 @@ } use strict; +use warnings; use XML::Parser::PerlSAX; use SOI::SOIHandler; use IO; Modified: schema/trunk/chado/src/test/perl/scripts/add-implicit-loc.pl =================================================================== --- schema/trunk/chado/src/test/perl/scripts/add-implicit-loc.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/src/test/perl/scripts/add-implicit-loc.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/local/bin/perl -w +#!/usr/bin/env perl use XML::NestArray qw(:all); use XML::NestArray::ITextParser; use XML::NestArray::Base; @@ -6,6 +6,7 @@ use FileHandle; use strict; +use warnings; use Data::Dumper; my $nudata = Node([]); Modified: schema/trunk/chado/src/test/perl/scripts/fgraph.pl =================================================================== --- schema/trunk/chado/src/test/perl/scripts/fgraph.pl 2012-02-28 16:47:44 UTC (rev 25254) +++ schema/trunk/chado/src/test/perl/scripts/fgraph.pl 2012-02-28 21:26:20 UTC (rev 25255) @@ -1,4 +1,4 @@ -#!/usr/local/bin/perl +#!/usr/bin/env perl use GraphViz; use XML::NestArray qw(:all); @@ -7,6 +7,7 @@ use FileHandle; use strict; +use warnings; use Data::Dumper; my @valid_fmts = qw(png gd pic ps gd mif pcl gd2 jpeg vrml svg); This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <sco...@us...> - 2012-12-11 17:25:49
|
Revision: 25277 http://gmod.svn.sourceforge.net/gmod/?rev=25277&view=rev Author: scottcain Date: 2012-12-11 17:25:38 +0000 (Tue, 11 Dec 2012) Log Message: ----------- Modified Makefile.PL so that if using PREFIX it won't stomp on the GMOD_ROOT envirnment variable (that is, everything goes in PREFIX, except what goes in GMOD_ROOT). Modified Paths: -------------- schema/trunk/chado/Changes schema/trunk/chado/Makefile.PL Modified: schema/trunk/chado/Changes =================================================================== --- schema/trunk/chado/Changes 2012-11-08 09:48:15 UTC (rev 25276) +++ schema/trunk/chado/Changes 2012-12-11 17:25:38 UTC (rev 25277) @@ -5,6 +5,9 @@ mishmash that was being used in various scripts. * Fixed the GFF3 preprocessor so that if an insert into the sorting table fails, the script will die to avoid losing GFF lines in the resulting file. +* Modified Makefile.PL so that if using PREFIX it won't stomp on the +GMOD_ROOT envirnment variable (that is, everything goes in PREFIX, except what +goes in GMOD_ROOT). Version 1.22 Thu Nov 17 12:48:10 EST 2011 * Created a script to automatically create the schemas dirs for a release and Modified: schema/trunk/chado/Makefile.PL =================================================================== --- schema/trunk/chado/Makefile.PL 2012-11-08 09:48:15 UTC (rev 25276) +++ schema/trunk/chado/Makefile.PL 2012-12-11 17:25:38 UTC (rev 25277) @@ -206,7 +206,7 @@ @ARGV = (); push @ARGV, "PREFIX=$opts{PREFIX}" if $opts{PREFIX}; push @ARGV, "INSTALL_BASE=$opts{INSTALL_BASE}" if $opts{INSTALL_BASE}; -$O{'GMOD_ROOT'} = $opts{PREFIX} if $opts{PREFIX}; +$O{'GMOD_ROOT'} = $opts{PREFIX} if ($opts{PREFIX} and !$ENV{GMOD_ROOT}); push @ARGV, "INSTALLSITEMAN1DIR=$opts{INSTALLSITEMAN1DIR}" if $opts{INSTALLSITEMAN1DIR}; push @ARGV, "INSTALLSITEMAN3DIR=$opts{INSTALLSITEMAN3DIR}" if $opts{INSTALLSITEMAN3DIR}; push @ARGV, "LIB=$opts{LIB}" if $opts{LIB}; This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |
From: <sco...@us...> - 2013-07-26 17:24:10
|
Revision: 25290 http://sourceforge.net/p/gmod/svn/25290 Author: scottcain Date: 2013-07-26 17:23:56 +0000 (Fri, 26 Jul 2013) Log Message: ----------- added a warning message for the GFF bulk loader if part_of isn't found and a minor doc fix for UPGRADE.txt. Modified Paths: -------------- schema/trunk/chado/Changes schema/trunk/chado/UPGRADE.txt schema/trunk/chado/lib/Bio/GMOD/DB/Adapter.pm Modified: schema/trunk/chado/Changes =================================================================== --- schema/trunk/chado/Changes 2013-07-16 17:49:23 UTC (rev 25289) +++ schema/trunk/chado/Changes 2013-07-26 17:23:56 UTC (rev 25290) @@ -1,4 +1,5 @@ Version 1.24 Mon Mar 4 17:18:47 EST 2013 +* Added a warning message for the GFF bulk loader if part_of isn't found. * Fixed ncbi_taxonomy loader to load entire NCBI taxonomy (Naama) * Patched gmod_bulk_load_gff3.pl (provided by Alexie P) to fix the "orgainism from data" option. Modified: schema/trunk/chado/UPGRADE.txt =================================================================== --- schema/trunk/chado/UPGRADE.txt 2013-07-16 17:49:23 UTC (rev 25289) +++ schema/trunk/chado/UPGRADE.txt 2013-07-26 17:23:56 UTC (rev 25290) @@ -16,7 +16,7 @@ perl Makefile.PL make - make install + sudo make install and then, rather than doing the next step (make load_schema) which would wipe out the current database, do Modified: schema/trunk/chado/lib/Bio/GMOD/DB/Adapter.pm =================================================================== --- schema/trunk/chado/lib/Bio/GMOD/DB/Adapter.pm 2013-07-16 17:49:23 UTC (rev 25289) +++ schema/trunk/chado/lib/Bio/GMOD/DB/Adapter.pm 2013-07-26 17:23:56 UTC (rev 25290) @@ -1506,6 +1506,9 @@ $sth->execute; ($part_of) = $sth->fetchrow_array(); + warn "\n\nWARNING:\nUnable to find a 'part_of' term in the relationship ontology;\nIt's absense indicates that there is something really wrong with the database.\nConsider stopping and checking the state of your cvterm table.\n\n\n"; + + $sth = $self->dbh->prepare( "select cvterm_id from cvterm where name = 'derives_from' and cv_id in ( SELECT cv_id FROM cv WHERE name='relationship' @@ -3432,6 +3435,7 @@ $tuniquename = $target_id; $name = $target_id; } +###FIXME: put my $tuniquename = $target_id.'_'.$self->nextfeature; in else here $self->print_f($self->nextfeature, $self->organism_id(), $name,$tuniquename , $type, '\N','\N'); This was sent by the SourceForge.net collaborative development platform, the world's largest Open Source development site. |