1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
|
-- FUNCTION gfffeatureatts (integer) is a function to get
-- data in the same format as the gffatts view so that
-- it can be easily converted to GFF attributes.
CREATE FUNCTION gfffeatureatts (integer)
RETURNS SETOF gffatts
AS
'
SELECT feature_id, ''Ontology_term'' AS type, s.name AS attribute
FROM cvterm s, feature_cvterm fs
WHERE fs.feature_id= $1 AND fs.cvterm_id = s.cvterm_id
UNION
SELECT feature_id, ''Dbxref'' AS type, d.name || '':'' || s.accession AS attribute
FROM dbxref s, feature_dbxref fs, db d
WHERE fs.feature_id= $1 AND fs.dbxref_id = s.dbxref_id AND s.db_id = d.db_id
UNION
SELECT feature_id, ''Alias'' AS type, s.name AS attribute
FROM synonym s, feature_synonym fs
WHERE fs.feature_id= $1 AND fs.synonym_id = s.synonym_id
UNION
SELECT fp.feature_id,cv.name,fp.value
FROM featureprop fp, cvterm cv
WHERE fp.feature_id= $1 AND fp.type_id = cv.cvterm_id
UNION
SELECT feature_id, ''pub'' AS type, s.series_name || '':'' || s.title AS attribute
FROM pub s, feature_pub fs
WHERE fs.feature_id= $1 AND fs.pub_id = s.pub_id
'
LANGUAGE SQL;
--
-- functions for creating coordinate based functions
--
-- create a point
CREATE OR REPLACE FUNCTION featureslice(int, int) RETURNS setof featureloc AS
'SELECT * from featureloc where boxquery($1, $2) @ boxrange(fmin,fmax)'
LANGUAGE 'sql';
--uses the gff3atts to create a GFF3 compliant attribute string
CREATE OR REPLACE FUNCTION gffattstring (integer) RETURNS varchar AS
'DECLARE
return_string varchar;
f_id ALIAS FOR $1;
atts_view gffatts%ROWTYPE;
feature_row feature%ROWTYPE;
name varchar;
uniquename varchar;
parent varchar;
escape_loc int;
BEGIN
--Get name from feature.name
--Get ID from feature.uniquename
SELECT INTO feature_row * FROM feature WHERE feature_id = f_id;
name = feature_row.name;
return_string = ''ID='' || feature_row.uniquename;
IF name IS NOT NULL AND name != ''''
THEN
return_string = return_string ||'';'' || ''Name='' || name;
END IF;
--Get Parent from feature_relationship
SELECT INTO feature_row * FROM feature f, feature_relationship fr
WHERE fr.subject_id = f_id AND fr.object_id = f.feature_id;
IF FOUND
THEN
return_string = return_string||'';''||''Parent=''||feature_row.uniquename;
END IF;
FOR atts_view IN SELECT * FROM gff3atts WHERE feature_id = f_id LOOP
escape_loc = position('';'' in atts_view.attribute);
IF escape_loc > 0 THEN
atts_view.attribute = replace(atts_view.attribute, '';'', ''%3B'');
END IF;
return_string = return_string || '';''
|| atts_view.type || ''=''
|| atts_view.attribute;
END LOOP;
RETURN return_string;
END;
'
LANGUAGE plpgsql;
--creates a view that is suitable for creating a GFF3 string
--CREATE OR REPLACE VIEW gff3view (
--REMOVED and RECREATED in sequence-gff-views.sql to avoid
--using the function above
|
