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
158
159
160
161
162
163
164
165
166
167
168
169
170
171
|
CREATE OR REPLACE FUNCTION _fill_cvtermpath4node2detect_cycle(BIGINT, BIGINT, BIGINT, BIGINT, INTEGER) RETURNS BIGINT 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 bigint;
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(BIGINT, BIGINT) RETURNS BIGINT AS
'
DECLARE
rootid alias for $1;
cvid alias for $2;
ttype bigint;
ccount int;
cterm cvterm_relationship%ROWTYPE;
child cvterm_relationship%ROWTYPE;
rtn bigint;
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(BIGINT, BIGINT) RETURNS BIGINT AS
'
DECLARE
cvid alias for $1;
rootid alias for $2;
rtn bigint;
BEGIN
CREATE TEMP TABLE tmpcvtermpath(object_id bigint, subject_id bigint, cv_id bigint, type_id bigint, 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(BIGINT) RETURNS SETOF BIGINT AS
'
DECLARE
cvid alias for $1;
root cvterm%ROWTYPE;
rtn bigint;
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(BIGINT) RETURNS BIGINT AS
'
DECLARE
cvid alias for $1;
root cvterm%ROWTYPE;
rtn bigint;
BEGIN
CREATE TEMP TABLE tmpcvtermpath(object_id bigint, subject_id bigint, cv_id bigint, type_id bigint, 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 BIGINT AS
'
DECLARE
cvname alias for $1;
cv_id bigint;
rtn bigint;
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';
|
