Debian Codegraph @

modules/cv/functions/detect_cycle.plpgsql

raw
1
2 CREATE OR REPLACE FUNCTION _fill_cvtermpath4node2detect_cycle(BIGINT, BIGINT, BIGINT, BIGINT, INTEGER) RETURNS BIGINT AS
3 '
4 DECLARE
5 origin alias for $1;
6 child_id alias for $2;
7 cvid alias for $3;
8 typeid alias for $4;
9 depth alias for $5;
10 cterm cvterm_relationship%ROWTYPE;
11 exist_c int;
12 ccount int;
13 ecount int;
14 rtn bigint;
15 BEGIN
16
17 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'';
18 GET DIAGNOSTICS ccount = ROW_COUNT;
19 IF (ccount > 0) THEN
20 --RAISE EXCEPTION ''FOUND CYCLE: node % on cycle path'',origin;
21 RETURN origin;
22 END IF;
23
24 EXECUTE ''SELECT * FROM tmpcvtermpath WHERE cv_id = '' || cvid || '' AND object_id = '' || origin || '' AND subject_id = '' || child_id || '' AND '' || origin || ''<>'' || child_id;
25 GET DIAGNOSTICS ecount = ROW_COUNT;
26 IF (ecount > 0) THEN
27 --RAISE NOTICE ''FOUND TWICE (node), will check root obj % subj %'',origin, child_id;
28 SELECT INTO rtn _fill_cvtermpath4root2detect_cycle(child_id, cvid);
29 IF (rtn > 0) THEN
30 RETURN rtn;
31 END IF;
32 END IF;
33
34 EXECUTE ''SELECT * FROM tmpcvtermpath WHERE cv_id = '' || cvid || '' AND object_id = '' || origin || '' AND subject_id = '' || child_id || '' AND pathdistance = '' || depth;
35 GET DIAGNOSTICS exist_c = ROW_COUNT;
36 IF (exist_c = 0) THEN
37 EXECUTE ''INSERT INTO tmpcvtermpath (object_id, subject_id, cv_id, type_id, pathdistance) VALUES('' || origin || '', '' || child_id || '', '' || cvid || '', '' || typeid || '', '' || depth || '')'';
38 END IF;
39
40 FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = child_id LOOP
41 --RAISE NOTICE ''DOING for node, % %'', origin, cterm.subject_id;
42 SELECT INTO rtn _fill_cvtermpath4node2detect_cycle(origin, cterm.subject_id, cvid, cterm.type_id, depth+1);
43 IF (rtn > 0) THEN
44 RETURN rtn;
45 END IF;
46 END LOOP;
47 RETURN 0;
48 END;
49 '
50 LANGUAGE 'plpgsql';
51
52
53 CREATE OR REPLACE FUNCTION _fill_cvtermpath4root2detect_cycle(BIGINT, BIGINT) RETURNS BIGINT AS
54 '
55 DECLARE
56 rootid alias for $1;
57 cvid alias for $2;
58 ttype bigint;
59 ccount int;
60 cterm cvterm_relationship%ROWTYPE;
61 child cvterm_relationship%ROWTYPE;
62 rtn bigint;
63 BEGIN
64
65 SELECT INTO ttype cvterm_id FROM cvterm WHERE (name = ''isa'' OR name = ''is_a'');
66 SELECT INTO rtn _fill_cvtermpath4node2detect_cycle(rootid, rootid, cvid, ttype, 0);
67 IF (rtn > 0) THEN
68 RETURN rtn;
69 END IF;
70 FOR cterm IN SELECT * FROM cvterm_relationship WHERE object_id = rootid LOOP
71 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;
72 GET DIAGNOSTICS ccount = ROW_COUNT;
73 IF (ccount > 0) THEN
74 --RAISE NOTICE ''FOUND TWICE (root), will check root obj % subj %'',rootid,cterm.subject_id;
75 SELECT INTO rtn _fill_cvtermpath4node2detect_cycle(rootid, cterm.subject_id, cvid, ttype, 0);
76 IF (rtn > 0) THEN
77 RETURN rtn;
78 END IF;
79 ELSE
80 SELECT INTO rtn _fill_cvtermpath4root2detect_cycle(cterm.subject_id, cvid);
81 IF (rtn > 0) THEN
82 RETURN rtn;
83 END IF;
84 END IF;
85 END LOOP;
86 RETURN 0;
87 END;
88 '
89 LANGUAGE 'plpgsql';
90
91 CREATE OR REPLACE FUNCTION get_cycle_cvterm_id(BIGINT, BIGINT) RETURNS BIGINT AS
92 '
93 DECLARE
94 cvid alias for $1;
95 rootid alias for $2;
96 rtn bigint;
97 BEGIN
98
99 CREATE TEMP TABLE tmpcvtermpath(object_id bigint, subject_id bigint, cv_id bigint, type_id bigint, pathdistance int);
100 CREATE INDEX tmp_cvtpath1 ON tmpcvtermpath(object_id, subject_id);
101
102 SELECT INTO rtn _fill_cvtermpath4root2detect_cycle(rootid, cvid);
103 IF (rtn > 0) THEN
104 DROP TABLE tmpcvtermpath;
105 RETURN rtn;
106 END IF;
107 DROP TABLE tmpcvtermpath;
108 RETURN 0;
109 END;
110 '
111 LANGUAGE 'plpgsql';
112
113 CREATE OR REPLACE FUNCTION get_cycle_cvterm_ids(BIGINT) RETURNS SETOF BIGINT AS
114 '
115 DECLARE
116 cvid alias for $1;
117 root cvterm%ROWTYPE;
118 rtn bigint;
119 BEGIN
120
121
122 FOR root IN SELECT DISTINCT t.* from cvterm t WHERE cv_id = cvid LOOP
123 SELECT INTO rtn get_cycle_cvterm_id(cvid,root.cvterm_id);
124 IF (rtn > 0) THEN
125 RETURN NEXT rtn;
126 END IF;
127 END LOOP;
128 RETURN;
129 END;
130 '
131 LANGUAGE 'plpgsql';
132
133 CREATE OR REPLACE FUNCTION get_cycle_cvterm_id(BIGINT) RETURNS BIGINT AS
134 '
135 DECLARE
136 cvid alias for $1;
137 root cvterm%ROWTYPE;
138 rtn bigint;
139 BEGIN
140
141 CREATE TEMP TABLE tmpcvtermpath(object_id bigint, subject_id bigint, cv_id bigint, type_id bigint, pathdistance int);
142 CREATE INDEX tmp_cvtpath1 ON tmpcvtermpath(object_id, subject_id);
143
144 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
145 SELECT INTO rtn _fill_cvtermpath4root2detect_cycle(root.cvterm_id, root.cv_id);
146 IF (rtn > 0) THEN
147 DROP TABLE tmpcvtermpath;
148 RETURN rtn;
149 END IF;
150 END LOOP;
151 DROP TABLE tmpcvtermpath;
152 RETURN 0;
153 END;
154 '
155 LANGUAGE 'plpgsql';
156
157 CREATE OR REPLACE FUNCTION get_cycle_cvterm_id(cv.name%TYPE) RETURNS BIGINT AS
158 '
159 DECLARE
160 cvname alias for $1;
161 cv_id bigint;
162 rtn bigint;
163 BEGIN
164
165 SELECT INTO cv_id cv.cv_id from cv WHERE cv.name = cvname;
166 SELECT INTO rtn get_cycle_cvterm_id(cv_id);
167
168 RETURN rtn;
169 END;
170 '
171 LANGUAGE 'plpgsql';