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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
|
--- create ontology that has instantiated located_sequence_feature part of SO
--- way as it is written, the function can not be execute more than once in one connection
--- when you get error like ERROR: relation with OID NNNNN does not exist
--- as this is not meant to execute >1 times in one session so it should never happen
--- except at testing and test failed
--- disconnect and try again, in other words, it can NOT be executed >1 time in one connection
--- if using EXECUTE, we can avoid this problem but code is hard to write and read (lots of ', escape char)
--NOTE: private, don't call directly as relying on having temp table tmpcvtr
--DROP TYPE soi_type CASCADE;
CREATE TYPE soi_type AS (
type_id INT,
subject_id INT,
object_id INT
);
CREATE OR REPLACE FUNCTION _fill_cvtermpath4soinode(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 soi_type%ROWTYPE;
exist_c int;
BEGIN
--RAISE NOTICE ''depth=% o=%, root=%, cv=%, t=%'', depth,origin,child_id,cvid,typeid;
SELECT INTO exist_c count(*) FROM cvtermpath WHERE cv_id = cvid AND object_id = origin AND subject_id = child_id AND pathdistance = depth;
--- longest path
IF (exist_c > 0) THEN
UPDATE cvtermpath SET pathdistance = depth WHERE cv_id = cvid AND object_id = origin AND subject_id = child_id;
ELSE
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 tmp_type AS type_id, subject_id FROM tmpcvtr WHERE object_id = child_id LOOP
PERFORM _fill_cvtermpath4soinode(origin, cterm.subject_id, cvid, cterm.type_id, depth+1);
END LOOP;
RETURN 1;
END;
'
LANGUAGE 'plpgsql';
CREATE OR REPLACE FUNCTION _fill_cvtermpath4soi(INTEGER, INTEGER) RETURNS INTEGER AS
'
DECLARE
rootid alias for $1;
cvid alias for $2;
ttype int;
cterm soi_type%ROWTYPE;
BEGIN
SELECT INTO ttype cvterm_id FROM cvterm WHERE name = ''isa'';
--RAISE NOTICE ''got ttype %'',ttype;
PERFORM _fill_cvtermpath4soinode(rootid, rootid, cvid, ttype, 0);
FOR cterm IN SELECT tmp_type AS type_id, subject_id FROM tmpcvtr WHERE object_id = rootid LOOP
PERFORM _fill_cvtermpath4soi(cterm.subject_id, cvid);
END LOOP;
RETURN 1;
END;
'
LANGUAGE 'plpgsql';
--- use tmpcvtr to temp store soi (virtural ontology)
--- using tmp tables is faster than using recursive function to create feature type relationship
--- since it gets feature type rel set by set instead of one by one
--- and getting feature type rel is very expensive
--- call _fillcvtermpath4soi to create path for the virtual ontology
CREATE OR REPLACE FUNCTION create_soi() RETURNS INTEGER AS
'
DECLARE
parent soi_type%ROWTYPE;
isa_id cvterm.cvterm_id%TYPE;
soi_term TEXT := ''soi'';
soi_def TEXT := ''ontology of SO feature instantiated in database'';
soi_cvid INTEGER;
soiterm_id INTEGER;
pcount INTEGER;
count INTEGER := 0;
cquery TEXT;
BEGIN
SELECT INTO isa_id cvterm_id FROM cvterm WHERE name = ''isa'';
SELECT INTO soi_cvid cv_id FROM cv WHERE name = soi_term;
IF (soi_cvid > 0) THEN
DELETE FROM cvtermpath WHERE cv_id = soi_cvid;
DELETE FROM cvterm WHERE cv_id = soi_cvid;
ELSE
INSERT INTO cv (name, definition) VALUES(soi_term, soi_def);
END IF;
SELECT INTO soi_cvid cv_id FROM cv WHERE name = soi_term;
INSERT INTO cvterm (name, cv_id) VALUES(soi_term, soi_cvid);
SELECT INTO soiterm_id cvterm_id FROM cvterm WHERE name = soi_term;
CREATE TEMP TABLE tmpcvtr (tmp_type INT, type_id INT, subject_id INT, object_id INT);
CREATE UNIQUE INDEX u_tmpcvtr ON tmpcvtr(subject_id, object_id);
INSERT INTO tmpcvtr (tmp_type, type_id, subject_id, object_id)
SELECT DISTINCT isa_id, soiterm_id, f.type_id, soiterm_id FROM feature f, cvterm t
WHERE f.type_id = t.cvterm_id AND f.type_id > 0;
EXECUTE ''select * from tmpcvtr where type_id = '' || soiterm_id || '';'';
get diagnostics pcount = row_count;
raise notice ''all types in feature %'',pcount;
--- do it hard way, delete any child feature type from above (NOT IN clause did not work)
FOR parent IN SELECT DISTINCT 0, t.cvterm_id, 0 FROM feature c, feature_relationship fr, cvterm t
WHERE t.cvterm_id = c.type_id AND c.feature_id = fr.subject_id LOOP
DELETE FROM tmpcvtr WHERE type_id = soiterm_id and object_id = soiterm_id
AND subject_id = parent.subject_id;
END LOOP;
EXECUTE ''select * from tmpcvtr where type_id = '' || soiterm_id || '';'';
get diagnostics pcount = row_count;
raise notice ''all types in feature after delete child %'',pcount;
--- create feature type relationship (store in tmpcvtr)
CREATE TEMP TABLE tmproot (cv_id INTEGER not null, cvterm_id INTEGER not null, status INTEGER DEFAULT 0);
cquery := ''SELECT * FROM tmproot tmp WHERE tmp.status = 0;'';
---temp use tmpcvtr to hold instantiated SO relationship for speed
---use soterm_id as type_id, will delete from tmpcvtr
---us tmproot for this as well
INSERT INTO tmproot (cv_id, cvterm_id, status) SELECT DISTINCT soi_cvid, c.subject_id, 0 FROM tmpcvtr c
WHERE c.object_id = soiterm_id;
EXECUTE cquery;
GET DIAGNOSTICS pcount = ROW_COUNT;
WHILE (pcount > 0) LOOP
RAISE NOTICE ''num child temp (to be inserted) in tmpcvtr: %'',pcount;
INSERT INTO tmpcvtr (tmp_type, type_id, subject_id, object_id)
SELECT DISTINCT fr.type_id, soiterm_id, c.type_id, p.cvterm_id FROM feature c, feature_relationship fr,
tmproot p, feature pf, cvterm t WHERE c.feature_id = fr.subject_id AND fr.object_id = pf.feature_id
AND p.cvterm_id = pf.type_id AND t.cvterm_id = c.type_id AND p.status = 0;
UPDATE tmproot SET status = 1 WHERE status = 0;
INSERT INTO tmproot (cv_id, cvterm_id, status)
SELECT DISTINCT soi_cvid, c.type_id, 0 FROM feature c, feature_relationship fr,
tmproot tmp, feature p, cvterm t WHERE c.feature_id = fr.subject_id AND fr.object_id = p.feature_id
AND tmp.cvterm_id = p.type_id AND t.cvterm_id = c.type_id AND tmp.status = 1;
UPDATE tmproot SET status = 2 WHERE status = 1;
EXECUTE cquery;
GET DIAGNOSTICS pcount = ROW_COUNT;
END LOOP;
DELETE FROM tmproot;
---get transitive closure for soi
PERFORM _fill_cvtermpath4soi(soiterm_id, soi_cvid);
DROP TABLE tmpcvtr;
DROP TABLE tmproot;
RETURN 1;
END;
'
LANGUAGE 'plpgsql';
|
