| 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'; |