[Gmod-schema-cmts] schema/chado/modules/sequence/functions subsequence.plpgsql,NONE,1.1 subsequence.sqlapi,NONE,1.1 sequtil.plpgsql,1.1,1.2

[Chris M. <cmu...@us...>]

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

Modified Files:
	sequtil.plpgsql 
Added Files:
	subsequence.plpgsql subsequence.sqlapi 
Log Message:
more sqlapi components - NOT YET TESTED

--- NEW FILE: subsequence.plpgsql ---
-- dependency_on: [sequtil]

CREATE OR REPLACE FUNCTION subsequence(INT,INT,INT,INT)
 RETURNS TEXT AS
 'SELECT 
  CASE WHEN $4<0 
   THEN reverse_complement(substring(srcf.residues,$2+1,($3-$2)))
   ELSE substring(residues,$2+1,($3-$2))
  END AS residues
  FROM feature AS srcf
  WHERE
   srcf.feature_id=$1'
LANGUAGE 'sql';

CREATE OR REPLACE FUNCTION subsequence_by_featureloc(INT)
 RETURNS TEXT AS
 'SELECT 
  CASE WHEN strand<0 
   THEN reverse_complement(substring(srcf.residues,fmin+1,(fmax-fmin)))
   ELSE substring(srcf.residues,fmin+1,(fmax-fmin))
  END AS residues
  FROM feature AS srcf
   INNER JOIN featureloc ON (srcf.feature_id=featureloc.srcfeature_id)
  WHERE
   featureloc_id=$1'
LANGUAGE 'sql';

CREATE OR REPLACE FUNCTION subsequence_by_feature(INT,INT,INT)
 RETURNS TEXT AS
 'SELECT 
  CASE WHEN strand<0 
   THEN reverse_complement(substring(srcf.residues,fmin+1,(fmax-fmin)))
   ELSE substring(srcf.residues,fmin+1,(fmax-fmin))
  END AS residues
  FROM feature AS srcf
   INNER JOIN featureloc ON (srcf.feature_id=featureloc.srcfeature_id)
  WHERE
   featureloc.feature_id=$1 AND
   featureloc.rank=$2 AND
   featureloc.locgroup=$3'
LANGUAGE 'sql';

CREATE OR REPLACE FUNCTION subsequence_by_feature(INT)
 RETURNS TEXT AS 'SELECT residues FROM subsequence_by_feature($1,0,0)'
LANGUAGE 'sql';

-- constrained by feature_relationship.type_id
--   (allows user to construct queries that only get subsequences of
--    part_of subfeatures)
CREATE OR REPLACE FUNCTION subsequence_set_by_subfeatures(INT,INT,INT,INT)
 RETURNS setof TEXT AS
 'SELECT 
  CASE WHEN strand<0 
   THEN reverse_complement(substring(srcf.residues,fmin+1,(fmax-fmin)))
   ELSE substring(srcf.residues,fmin+1,(fmax-fmin))
  END AS residues
  FROM feature AS srcf
   INNER JOIN featureloc ON (srcf.feature_id=featureloc.srcfeature_id)
   INNER JOIN feature_relationship AS fr
     ON (fr.subject_id=featureloc.feature_id)
  WHERE
   fr.object_id=$1 AND
   fr.type_id=$2 AND
   featureloc.rank=$3 AND
   featureloc.locgroup=$4
  ORDER BY fr.rank
'
LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION subsequence_set_by_subfeatures(INT,INT,INT,INT)
 RETURNS SETOF TEXT AS
 'SELECT * FROM subsequence_set_by_subfeatures($1,$2,0,0)'
LANGUAGE 'sql';

CREATE OR REPLACE FUNCTION subsequence_by_subfeatures(INT,INT,INT,INT)
 RETURNS TEXT AS
 'SELECT concat(residues) FROM subsequence_set_by_subfeatures($1,$2,$3,$4)'
LANGUAGE 'sql';

CREATE OR REPLACE FUNCTION subsequence_by_subfeatures(INT,INT)
 RETURNS TEXT AS
 'SELECT concat(residues) FROM subsequence_set_by_subfeatures($1,$2,0,0)'
LANGUAGE 'sql';


-- constrained by subfeature.type_id (eg exons of a transcript)
CREATE OR REPLACE FUNCTION subsequence_set_by_typed_subfeatures(INT,INT,INT,INT)
 RETURNS setof TEXT AS
 'SELECT 
  CASE WHEN strand<0 
   THEN reverse_complement(substring(srcf.residues,fmin+1,(fmax-fmin)))
   ELSE substring(srcf.residues,fmin+1,(fmax-fmin))
  END AS residues
  FROM feature AS srcf
   INNER JOIN featureloc ON (srcf.feature_id=featureloc.srcfeature_id)
   INNER JOIN feature AS subf ON (subf.feature_id=featureloc.feature_id)
   INNER JOIN feature_relationship AS fr ON (fr.subject_id=subf.feature_id)
  WHERE
   fr.object_id=$1 AND
   subf.type_id=$2 AND
   featureloc.rank=$3 AND
   featureloc.locgroup=$4
  ORDER BY fr.rank
'
LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION subsequence_set_by_typed_subfeatures(INT,INT,INT,INT)
 RETURNS SETOF TEXT AS
 'SELECT * FROM subsequence_set_by_typed_subfeatures($1,$2,0,0)'
LANGUAGE 'sql';

CREATE OR REPLACE FUNCTION subsequence_by_typed_subfeatures(INT,INT,INT,INT)
 RETURNS TEXT AS
 'SELECT concat(residues) FROM subsequence_set_by_typed_subfeatures($1,$2,$3,$4)'
LANGUAGE 'sql';

CREATE OR REPLACE FUNCTION subsequence_by_typed_subfeatures(INT,INT)
 RETURNS TEXT AS
 'SELECT concat(residues) FROM subsequence_set_by_typed_subfeatures($1,$2,0,0)'
LANGUAGE 'sql';

 



--- NEW FILE: subsequence.sqlapi ---
-- this API imports sequtil.sqlapi

-----------------------------------
-- basic subsequencing functions --
-----------------------------------

DECLARE FUNCTION subsequence(
   srcfeature_id        INT REFERENCES feature(feature_id),
   fmin                 INT,
   fmax                 INT,
   strand               INT
)
 RETURNS TEXT;
COMMENT ON FUNCTION subsequence(INT,INT,INT,INT) IS
 'extracts a subsequence, reverse complementing if range <1';

DECLARE FUNCTION subsequence_by_feature(
   feature_id           INT REFERENCES feature(feature_id)
)
 RETURNS TEXT;
COMMENT ON FUNCTION subsequence_by_feature(INT) IS
 'extracts a subsequence for a feature, using featureloc (rank,group=0)';

DECLARE FUNCTION subsequence_by_feature(
   feature_id           INT REFERENCES feature(feature_id),
   rank                 INT,
   locgroup             INT
)
 RETURNS TEXT;
COMMENT ON FUNCTION subsequence_by_feature(INT) IS
 'extracts a subsequence for a feature, using featureloc (rank,group=0)';

-------------------------------------------------------
-- subsequences based on discontiguous featurelocs  ---
-------------------------------------------------------

-- a feature can contain subfeatures (eg transcripts 
-- containing exons and polypeptides)
-- these functions automatically extract the concatenated
-- sequence of the multiple featurelocs defined by the
-- subfeatures of any containing feature

-- ** typed subfeatures **
--  here subfeatures are constrained by their type; eg exon

DECLARE FUNCTION subsequence_set_by_typed_subfeatures(
   feature_id           INT REFERENCES feature(feature_id),
   type_id              INT REFERENCES cvterm(cvterm_id)
)
 RETURNS SETOF TEXT;

COMMENT ON FUNCTION subsequence_set_by_typed_subfeatures(INT,INT) IS
'extracts a subsequence for a featureset. a featureset contains
subfeatures, as defined by feature_relationship (the set is the object
of the relation, the subfeature is the subject). the subfeatures are
constrained by a type_id (this allows you to get the subsequence of a
transcript based on exon featurelocs only). The rank and locgroup are
set to 0';

DECLARE FUNCTION subsequence_set_by_typed_subfeatures(
   feature_id           INT REFERENCES feature(feature_id),
   type_id              INT REFERENCES cvterm(cvterm_id),
   rank                 INT,
   locgroup             INT
)
 RETURNS SETOF TEXT;

COMMENT ON FUNCTION subsequence_set_by_typed_subfeatures(INT,INT,INT,INT) IS
'See the 2-arg version of this function; this allows the featurelocs
to be constrained by rank and locgroup';

-- ** typed relations **
--  here subfeatures are constrained by the type of the relationship
--  between subfeature and container feature

DECLARE FUNCTION subsequence_set_by_subfeatures(
   feature_id           INT REFERENCES feature(feature_id),
   type_id              INT REFERENCES cvterm(cvterm_id)
)
 RETURNS SETOF TEXT;

COMMENT ON FUNCTION subsequence_set_by_subfeatures(INT,INT) IS
'extracts a subsequence for a featureset. a featureset contains
subfeatures, as defined by feature_relationship (the set is the object
of the relation, the subfeature is the subject). the subfeatures are
constrained by a type_id (this allows you to get the subsequence of a
transcript based on exon featurelocs only). The rank and locgroup are
set to 0';

DECLARE FUNCTION subsequence_set_by_subfeatures(
   feature_id           INT REFERENCES feature(feature_id),
   type_id              INT REFERENCES cvterm(cvterm_id),
   rank                 INT,
   locgroup             INT
)
 RETURNS SETOF TEXT;

COMMENT ON FUNCTION subsequence_set_by_subfeatures(INT,INT,INT,INT) IS
'See the 2-arg version of this function; this allows the featurelocs
to be constrained by rank and locgroup';

Index: sequtil.plpgsql
===================================================================
RCS file: /cvsroot/gmod/schema/chado/modules/sequence/functions/sequtil.plpgsql,v
retrieving revision 1.1
retrieving revision 1.2
diff -C2 -d -r1.1 -r1.2
*** sequtil.plpgsql	25 Jul 2005 16:26:52 -0000	1.1
--- sequtil.plpgsql	25 Jul 2005 21:19:35 -0000	1.2
***************
*** 63,64 ****
--- 63,74 ----
  
  
+ CREATE OR REPLACE FUNCTION concat_pair (text, text) RETURNS text AS
+  'SELECT $1 || $2'
+ LANGUAGE 'sql';
+ 
+ CREATE AGGREGATE concat (
+ sfunc = concat_pair,
+ basetype = text,
+ stype = text,
+ initcond = ''
+ );