| 1 |
CREATE TYPE closure_result AS (cvterm_id bigint, pathdistance INTEGER); |
| 2 |
|
| 3 |
-- results may not be distinct |
| 4 |
CREATE OR REPLACE FUNCTION _closure_over_IS_A(BIGINT, BIGINT) RETURNS SETOF BIGINT AS |
| 5 |
$$ |
| 6 |
DECLARE |
| 7 |
term ALIAS FOR $1; |
| 8 |
is_a_id ALIAS FOR $2; |
| 9 |
inner_row RECORD; |
| 10 |
outer_row RECORD; |
| 11 |
BEGIN |
| 12 |
-- get direct is_a parents |
| 13 |
FOR inner_row IN SELECT object_id AS cvterm_id |
| 14 |
FROM cvterm_relationship WHERE subject_id = term AND type_id=is_a_id LOOP |
| 15 |
-- return direct is_a parents |
| 16 |
RETURN NEXT inner_row.cvterm_id; |
| 17 |
|
| 18 |
-- recursive, self-call |
| 19 |
FOR outer_row IN SELECT * FROM _closure_over_IS_A(inner_row.cvterm_id, is_a_id) LOOP |
| 20 |
RETURN NEXT outer_row._closure_over_IS_A; |
| 21 |
END LOOP; |
| 22 |
END LOOP; |
| 23 |
RETURN; |
| 24 |
END; |
| 25 |
$$ |
| 26 |
LANGUAGE 'plpgsql'; |
| 27 |
|
| 28 |
CREATE OR REPLACE FUNCTION closure_over_IS_A(BIGINT) RETURNS SETOF BIGINT AS |
| 29 |
$$ |
| 30 |
DECLARE |
| 31 |
is_a_id BIGINT; |
| 32 |
r RECORD; |
| 33 |
BEGIN |
| 34 |
SELECT INTO is_a_id get_cvterm_id_for_is_a(); |
| 35 |
FOR r IN SELECT * FROM _closure_over_IS_A($1, is_a_id) LOOP |
| 36 |
RETURN NEXT r._closure_over_IS_A; |
| 37 |
END LOOP; |
| 38 |
RETURN; |
| 39 |
END; |
| 40 |
$$ |
| 41 |
LANGUAGE 'plpgsql'; |
| 42 |
|
| 43 |
CREATE OR REPLACE FUNCTION closure_over_reflexive_IS_A(BIGINT) RETURNS SETOF BIGINT AS |
| 44 |
$$ |
| 45 |
DECLARE |
| 46 |
is_a_id BIGINT; |
| 47 |
r RECORD; |
| 48 |
BEGIN |
| 49 |
SELECT INTO is_a_id get_cvterm_id_for_is_a(); |
| 50 |
FOR r IN SELECT * FROM _closure_over_IS_A($1, is_a_id) |
| 51 |
UNION SELECT $1 |
| 52 |
LOOP |
| 53 |
RETURN NEXT r._closure_over_IS_A; |
| 54 |
END LOOP; |
| 55 |
RETURN; |
| 56 |
END; |
| 57 |
$$ |
| 58 |
LANGUAGE 'plpgsql'; |
| 59 |
|
| 60 |
-- implements: |
| 61 |
-- X R Z <= X is_a* Y, Y R Z |
| 62 |
-- but not: |
| 63 |
-- X R Z <= X R Y, Y is_a+ Z |
| 64 |
CREATE OR REPLACE FUNCTION _closure_over_relation_with_dist_partial(BIGINT,BIGINT,BIGINT,INTEGER) RETURNS SETOF closure_result AS |
| 65 |
$$ |
| 66 |
DECLARE |
| 67 |
term ALIAS FOR $1; |
| 68 |
relation_id ALIAS FOR $2; |
| 69 |
is_a_id ALIAS FOR $3; |
| 70 |
depth ALIAS FOR $4; |
| 71 |
depth2 INTEGER; |
| 72 |
isa_plus_r RECORD; |
| 73 |
direct_link_r closure_result%ROWTYPE; |
| 74 |
trans_link_r closure_result%ROWTYPE; |
| 75 |
BEGIN |
| 76 |
depth2 := depth+1; |
| 77 |
-- get ALL is_a* parents |
| 78 |
FOR isa_plus_r IN SELECT * FROM _closure_over_is_a(term,is_a_id) AS cvterm_id UNION SELECT term AS cvterm_id LOOP |
| 79 |
|
| 80 |
RAISE NOTICE ''is_a %'', isa_plus_r.cvterm_id; |
| 81 |
FOR direct_link_r IN SELECT object_id AS cvterm_id, depth2 AS pathdistance |
| 82 |
FROM cvterm_relationship WHERE subject_id = isa_plus_r.cvterm_id AND type_id=relation_id LOOP |
| 83 |
RAISE NOTICE ''obj % dist % / % '', direct_link_r.cvterm_id, depth2, direct_link_r.pathdistance; |
| 84 |
-- return direct R parents |
| 85 |
RETURN NEXT direct_link_r; |
| 86 |
-- recursive, self-call |
| 87 |
FOR trans_link_r IN SELECT * |
| 88 |
FROM _closure_over_relation_with_dist_partial(direct_link_r.cvterm_id, relation_id, is_a_id, depth2) LOOP |
| 89 |
RETURN NEXT trans_link_r; |
| 90 |
END LOOP; |
| 91 |
END LOOP; |
| 92 |
END LOOP; |
| 93 |
RETURN; |
| 94 |
END; |
| 95 |
$$ |
| 96 |
LANGUAGE 'plpgsql'; |
| 97 |
|
| 98 |
CREATE OR REPLACE FUNCTION closure_over_relation_with_dist(BIGINT,BIGINT) RETURNS SETOF closure_result AS |
| 99 |
$$ |
| 100 |
DECLARE |
| 101 |
is_a_id BIGINT; |
| 102 |
r closure_result%ROWTYPE; |
| 103 |
r_plus_is_a closure_result%ROWTYPE; |
| 104 |
BEGIN |
| 105 |
SELECT INTO is_a_id get_cvterm_id_for_is_a(); |
| 106 |
FOR r IN SELECT * FROM _closure_over_relation_with_dist_partial($1,$2,is_a_id,0) AS cvterm_id LOOP |
| 107 |
FOR r_plus_is_a IN |
| 108 |
SELECT cvterm_id, r.pathdistance FROM _closure_over_is_a(r.cvterm_id,is_a_id) AS cvterm_id |
| 109 |
UNION SELECT r.* LOOP |
| 110 |
RETURN NEXT r_plus_is_a; |
| 111 |
END LOOP; |
| 112 |
END LOOP; |
| 113 |
RETURN; |
| 114 |
END; |
| 115 |
$$ |
| 116 |
LANGUAGE 'plpgsql'; |
| 117 |
|
| 118 |
CREATE OR REPLACE FUNCTION forward_chain() RETURNS INT AS |
| 119 |
$$ |
| 120 |
DECLARE |
| 121 |
num_assertions INTEGER; |
| 122 |
total_assertions INTEGER; |
| 123 |
BEGIN |
| 124 |
total_assertions := 0; |
| 125 |
|
| 126 |
PERFORM seed_cvtermpath(); |
| 127 |
|
| 128 |
-- keep adding new facts until exhausted |
| 129 |
LOOP |
| 130 |
SELECT INTO num_assertions forward_chain_iteration(); |
| 131 |
total_assertions := total_assertions + num_assertions; |
| 132 |
|
| 133 |
RAISE LOG 'Asserted % new facts; current_total=%', num_assertions, total_assertions; |
| 134 |
EXIT WHEN num_assertions > 0; |
| 135 |
END LOOP; |
| 136 |
RETURN total_assertions; |
| 137 |
END; |
| 138 |
$$ LANGUAGE 'plpgsql'; |
| 139 |
|
| 140 |
CREATE OR REPLACE FUNCTION seed_cvtermpath() RETURNS INT AS |
| 141 |
$$ |
| 142 |
DECLARE |
| 143 |
num_assertions INTEGER; |
| 144 |
is_a_id BIGINT; |
| 145 |
BEGIN |
| 146 |
RAISE LOG 'Seeding cvtermpath'; |
| 147 |
|
| 148 |
-- seed cvtermpath with direct asserted links |
| 149 |
INSERT INTO cvtermpath |
| 150 |
(subject_id,type_id,object_id,pathdistance) |
| 151 |
SELECT DISTINCT |
| 152 |
link.subject_id, |
| 153 |
link.type_id, |
| 154 |
link.object_id, |
| 155 |
1 |
| 156 |
FROM |
| 157 |
cvterm_relationship AS link |
| 158 |
LEFT JOIN cvtermpath AS p1 USING (subject_id,type_id,object_id) -- exclude pre-populated links |
| 159 |
WHERE p1.cvtermpath_id IS NULL; -- this is how we do a SELECT MINUS |
| 160 |
GET DIAGNOSTICS num_assertions = ROW_COUNT; |
| 161 |
RAISE LOG 'Seeded cvtermpath with % links', num_assertions; |
| 162 |
|
| 163 |
SELECT INTO is_a_id get_cvterm_id_for_is_a(); |
| 164 |
-- seed cvtermpath with reflexive is_a |
| 165 |
INSERT INTO cvtermpath |
| 166 |
(subject_id,type_id,object_id,pathdistance) |
| 167 |
SELECT DISTINCT |
| 168 |
t.cvterm_id, |
| 169 |
is_a_id, |
| 170 |
t.cvterm_id, |
| 171 |
0 |
| 172 |
FROM |
| 173 |
cvterm AS t |
| 174 |
LEFT JOIN cvtermpath AS p1 ON |
| 175 |
(p1.subject_id=t.cvterm_id,p1.type_id=t.is_a_id,p1.object_id=t.cvterm_id) -- exclude pre-populated links |
| 176 |
WHERE p1.cvtermpath_id IS NULL; -- this is how we do a SELECT MINUS |
| 177 |
GET DIAGNOSTICS num_assertions = ROW_COUNT; |
| 178 |
RAISE LOG 'Seeded cvtermpath with % links', num_assertions; |
| 179 |
|
| 180 |
RETURN num_assertions; |
| 181 |
END; |
| 182 |
$$ LANGUAGE 'plpgsql'; |
| 183 |
|
| 184 |
CREATE OR REPLACE FUNCTION forward_chain_iteration() RETURNS INT AS |
| 185 |
$$ |
| 186 |
DECLARE |
| 187 |
num_assertions INTEGER; |
| 188 |
total_assertions INTEGER; |
| 189 |
BEGIN |
| 190 |
total_assertions := 0; |
| 191 |
|
| 192 |
-- add your own rules here: |
| 193 |
SELECT INTO num_assertions forward_chain_iteration_rule_is_a(); |
| 194 |
total_assertions := total_assertions + num_assertions; |
| 195 |
-- end of rules section |
| 196 |
|
| 197 |
RETURN total_assertions; |
| 198 |
END; |
| 199 |
$$ LANGUAGE 'plpgsql'; |
| 200 |
|
| 201 |
-- IS_A rule |
| 202 |
-- X is_a Y, Y is_a Z => X is_a Z |
| 203 |
CREATE OR REPLACE FUNCTION forward_chain_iteration_rule_is_a() RETURNS INT AS |
| 204 |
$$ |
| 205 |
DECLARE |
| 206 |
is_a_id BIGINT; |
| 207 |
num_assertions INTEGER; |
| 208 |
BEGIN |
| 209 |
RAISE LOG 'Applying rule: IS_A'; |
| 210 |
SELECT INTO is_a_id get_cvterm_id_for_is_a(); |
| 211 |
INSERT INTO cvtermpath |
| 212 |
(subject_id,type_id,object_id,pathdistance) |
| 213 |
SELECT DISTINCT |
| 214 |
p.subject_id, |
| 215 |
is_a_id, |
| 216 |
link.object_id, |
| 217 |
p.pathdistance+1 |
| 218 |
FROM |
| 219 |
cvtermpath AS p -- extend this |
| 220 |
INNER JOIN cvterm_relationship AS link ON (p.object_id = link.subject_id) -- using this |
| 221 |
LEFT JOIN cvtermpath AS p1 ON (p1.subject_id=p.subject_id AND p1.object_id=link.object_id AND p1.type_id=is_a_id) -- excluding this [pathdist??] |
| 222 |
WHERE link.type_id=is_a_id |
| 223 |
AND p.type_id=is_a_id |
| 224 |
AND p1.cvtermpath_id IS NULL; -- exclude existing paths |
| 225 |
|
| 226 |
GET DIAGNOSTICS num_assertions = ROW_COUNT; |
| 227 |
RAISE LOG 'RULE:IS_A iteration produced % links', num_assertions; |
| 228 |
RETURN num_assertions; |
| 229 |
END; |
| 230 |
$$ LANGUAGE 'plpgsql'; |
| 231 |
|
| 232 |
-- TRANSITIVE-OVER-IS_A rule |
| 233 |
-- X is_a+ Y, Y R Z => X R Z |
| 234 |
CREATE OR REPLACE FUNCTION forward_chain_iteration_rule_trans_over_is_a() RETURNS INT AS |
| 235 |
$$ |
| 236 |
DECLARE |
| 237 |
is_a_id BIGINT; |
| 238 |
num_assertions INTEGER; |
| 239 |
BEGIN |
| 240 |
RAISE LOG 'Applying rule: TRANS_OVER_IS_A'; |
| 241 |
SELECT INTO is_a_id get_cvterm_id_for_is_a(); |
| 242 |
INSERT INTO cvtermpath |
| 243 |
(subject_id,type_id,object_id,pathdistance) |
| 244 |
SELECT DISTINCT |
| 245 |
p_is_a.subject_id, |
| 246 |
p_rel.type_id |
| 247 |
p_rel.object_id, |
| 248 |
p_rel.pathdistance+1 |
| 249 |
FROM |
| 250 |
cvtermpath AS p_is_a -- extend this |
| 251 |
INNER JOIN cvtermpath AS p_rel ON (p_is_a.object_id = p_rel.subject_id) -- using this |
| 252 |
LEFT JOIN cvtermpath AS p1 ON (p1.subject_id=p_is_a.subject_id AND p1.object_id=p_rel.object_id AND p1.type_id=p_rel.type_id) -- excluding this [pathdist??] |
| 253 |
WHERE p_is_a.type_id=is_a_id |
| 254 |
AND p1.cvtermpath_id IS NULL; -- exclude existing paths |
| 255 |
|
| 256 |
GET DIAGNOSTICS num_assertions = ROW_COUNT; |
| 257 |
RAISE LOG 'Applied rule:TRANS_OVER_IS_A iteration produced % links', num_assertions; |
| 258 |
RETURN num_assertions; |
| 259 |
END; |
| 260 |
$$ LANGUAGE 'plpgsql'; |
| 261 |
|
| 262 |
|