| 1 |
--- create ontology that has instantiated located_sequence_feature part of SO |
| 2 |
--- way as it is written, the function can not be execute more than once in one connection |
| 3 |
--- when you get error like ERROR: relation with OID NNNNN does not exist |
| 4 |
--- as this is not meant to execute >1 times in one session so it should never happen |
| 5 |
--- except at testing and test failed |
| 6 |
--- disconnect and try again, in other words, it can NOT be executed >1 time in one connection |
| 7 |
--- if using EXECUTE, we can avoid this problem but code is hard to write and read (lots of ', escape char) |
| 8 |
|
| 9 |
--NOTE: private, don't call directly as relying on having temp table tmpcvtr |
| 10 |
|
| 11 |
--DROP TYPE soi_type CASCADE; |
| 12 |
CREATE TYPE soi_type AS ( |
| 13 |
type_id bigint, |
| 14 |
subject_id bigint, |
| 15 |
object_id bigint |
| 16 |
); |
| 17 |
|
| 18 |
CREATE OR REPLACE FUNCTION _fill_cvtermpath4soinode(BIGINT, BIGINT, BIGINT, BIGINT, INTEGER) RETURNS INTEGER AS |
| 19 |
' |
| 20 |
DECLARE |
| 21 |
origin alias for $1; |
| 22 |
child_id alias for $2; |
| 23 |
cvid alias for $3; |
| 24 |
typeid alias for $4; |
| 25 |
depth alias for $5; |
| 26 |
cterm soi_type%ROWTYPE; |
| 27 |
exist_c int; |
| 28 |
|
| 29 |
BEGIN |
| 30 |
|
| 31 |
--RAISE NOTICE ''depth=% o=%, root=%, cv=%, t=%'', depth,origin,child_id,cvid,typeid; |
| 32 |
SELECT INTO exist_c count(*) FROM cvtermpath WHERE cv_id = cvid AND object_id = origin AND subject_id = child_id AND pathdistance = depth; |
| 33 |
--- longest path |
| 34 |
IF (exist_c > 0) THEN |
| 35 |
UPDATE cvtermpath SET pathdistance = depth WHERE cv_id = cvid AND object_id = origin AND subject_id = child_id; |
| 36 |
ELSE |
| 37 |
INSERT INTO cvtermpath (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 tmp_type AS type_id, subject_id FROM tmpcvtr WHERE object_id = child_id LOOP |
| 41 |
PERFORM _fill_cvtermpath4soinode(origin, cterm.subject_id, cvid, cterm.type_id, depth+1); |
| 42 |
END LOOP; |
| 43 |
RETURN 1; |
| 44 |
END; |
| 45 |
' |
| 46 |
LANGUAGE 'plpgsql'; |
| 47 |
|
| 48 |
CREATE OR REPLACE FUNCTION _fill_cvtermpath4soi(BIGINT, BIGINT) RETURNS INTEGER AS |
| 49 |
' |
| 50 |
DECLARE |
| 51 |
rootid alias for $1; |
| 52 |
cvid alias for $2; |
| 53 |
ttype bigint; |
| 54 |
cterm soi_type%ROWTYPE; |
| 55 |
|
| 56 |
BEGIN |
| 57 |
|
| 58 |
SELECT INTO ttype cvterm_id FROM cvterm WHERE name = ''isa''; |
| 59 |
--RAISE NOTICE ''got ttype %'',ttype; |
| 60 |
PERFORM _fill_cvtermpath4soinode(rootid, rootid, cvid, ttype, 0); |
| 61 |
FOR cterm IN SELECT tmp_type AS type_id, subject_id FROM tmpcvtr WHERE object_id = rootid LOOP |
| 62 |
PERFORM _fill_cvtermpath4soi(cterm.subject_id, cvid); |
| 63 |
END LOOP; |
| 64 |
RETURN 1; |
| 65 |
END; |
| 66 |
' |
| 67 |
LANGUAGE 'plpgsql'; |
| 68 |
|
| 69 |
--- use tmpcvtr to temp store soi (virtural ontology) |
| 70 |
--- using tmp tables is faster than using recursive function to create feature type relationship |
| 71 |
--- since it gets feature type rel set by set instead of one by one |
| 72 |
--- and getting feature type rel is very expensive |
| 73 |
--- call _fillcvtermpath4soi to create path for the virtual ontology |
| 74 |
|
| 75 |
CREATE OR REPLACE FUNCTION create_soi() RETURNS INTEGER AS |
| 76 |
' |
| 77 |
DECLARE |
| 78 |
parent soi_type%ROWTYPE; |
| 79 |
isa_id cvterm.cvterm_id%TYPE; |
| 80 |
soi_term TEXT := ''soi''; |
| 81 |
soi_def TEXT := ''ontology of SO feature instantiated in database''; |
| 82 |
soi_cvid bigint; |
| 83 |
soiterm_id bigint; |
| 84 |
pcount INTEGER; |
| 85 |
count INTEGER := 0; |
| 86 |
cquery TEXT; |
| 87 |
BEGIN |
| 88 |
|
| 89 |
SELECT INTO isa_id cvterm_id FROM cvterm WHERE name = ''isa''; |
| 90 |
|
| 91 |
SELECT INTO soi_cvid cv_id FROM cv WHERE name = soi_term; |
| 92 |
IF (soi_cvid > 0) THEN |
| 93 |
DELETE FROM cvtermpath WHERE cv_id = soi_cvid; |
| 94 |
DELETE FROM cvterm WHERE cv_id = soi_cvid; |
| 95 |
ELSE |
| 96 |
INSERT INTO cv (name, definition) VALUES(soi_term, soi_def); |
| 97 |
END IF; |
| 98 |
SELECT INTO soi_cvid cv_id FROM cv WHERE name = soi_term; |
| 99 |
INSERT INTO cvterm (name, cv_id) VALUES(soi_term, soi_cvid); |
| 100 |
SELECT INTO soiterm_id cvterm_id FROM cvterm WHERE name = soi_term; |
| 101 |
|
| 102 |
CREATE TEMP TABLE tmpcvtr (tmp_type BIGINT, type_id bigint, subject_id bigint, object_id bigint); |
| 103 |
CREATE UNIQUE INDEX u_tmpcvtr ON tmpcvtr(subject_id, object_id); |
| 104 |
|
| 105 |
INSERT INTO tmpcvtr (tmp_type, type_id, subject_id, object_id) |
| 106 |
SELECT DISTINCT isa_id, soiterm_id, f.type_id, soiterm_id FROM feature f, cvterm t |
| 107 |
WHERE f.type_id = t.cvterm_id AND f.type_id > 0; |
| 108 |
EXECUTE ''select * from tmpcvtr where type_id = '' || soiterm_id || '';''; |
| 109 |
get diagnostics pcount = row_count; |
| 110 |
raise notice ''all types in feature %'',pcount; |
| 111 |
--- do it hard way, delete any child feature type from above (NOT IN clause did not work) |
| 112 |
FOR parent IN SELECT DISTINCT 0, t.cvterm_id, 0 FROM feature c, feature_relationship fr, cvterm t |
| 113 |
WHERE t.cvterm_id = c.type_id AND c.feature_id = fr.subject_id LOOP |
| 114 |
DELETE FROM tmpcvtr WHERE type_id = soiterm_id and object_id = soiterm_id |
| 115 |
AND subject_id = parent.subject_id; |
| 116 |
END LOOP; |
| 117 |
EXECUTE ''select * from tmpcvtr where type_id = '' || soiterm_id || '';''; |
| 118 |
get diagnostics pcount = row_count; |
| 119 |
raise notice ''all types in feature after delete child %'',pcount; |
| 120 |
|
| 121 |
--- create feature type relationship (store in tmpcvtr) |
| 122 |
CREATE TEMP TABLE tmproot (cv_id bigint not null, cvterm_id bigint not null, status INTEGER DEFAULT 0); |
| 123 |
cquery := ''SELECT * FROM tmproot tmp WHERE tmp.status = 0;''; |
| 124 |
---temp use tmpcvtr to hold instantiated SO relationship for speed |
| 125 |
---use soterm_id as type_id, will delete from tmpcvtr |
| 126 |
---us tmproot for this as well |
| 127 |
INSERT INTO tmproot (cv_id, cvterm_id, status) SELECT DISTINCT soi_cvid, c.subject_id, 0 FROM tmpcvtr c |
| 128 |
WHERE c.object_id = soiterm_id; |
| 129 |
EXECUTE cquery; |
| 130 |
GET DIAGNOSTICS pcount = ROW_COUNT; |
| 131 |
WHILE (pcount > 0) LOOP |
| 132 |
RAISE NOTICE ''num child temp (to be inserted) in tmpcvtr: %'',pcount; |
| 133 |
INSERT INTO tmpcvtr (tmp_type, type_id, subject_id, object_id) |
| 134 |
SELECT DISTINCT fr.type_id, soiterm_id, c.type_id, p.cvterm_id FROM feature c, feature_relationship fr, |
| 135 |
tmproot p, feature pf, cvterm t WHERE c.feature_id = fr.subject_id AND fr.object_id = pf.feature_id |
| 136 |
AND p.cvterm_id = pf.type_id AND t.cvterm_id = c.type_id AND p.status = 0; |
| 137 |
UPDATE tmproot SET status = 1 WHERE status = 0; |
| 138 |
INSERT INTO tmproot (cv_id, cvterm_id, status) |
| 139 |
SELECT DISTINCT soi_cvid, c.type_id, 0 FROM feature c, feature_relationship fr, |
| 140 |
tmproot tmp, feature p, cvterm t WHERE c.feature_id = fr.subject_id AND fr.object_id = p.feature_id |
| 141 |
AND tmp.cvterm_id = p.type_id AND t.cvterm_id = c.type_id AND tmp.status = 1; |
| 142 |
UPDATE tmproot SET status = 2 WHERE status = 1; |
| 143 |
EXECUTE cquery; |
| 144 |
GET DIAGNOSTICS pcount = ROW_COUNT; |
| 145 |
END LOOP; |
| 146 |
DELETE FROM tmproot; |
| 147 |
|
| 148 |
---get transitive closure for soi |
| 149 |
PERFORM _fill_cvtermpath4soi(soiterm_id, soi_cvid); |
| 150 |
|
| 151 |
DROP TABLE tmpcvtr; |
| 152 |
DROP TABLE tmproot; |
| 153 |
|
| 154 |
RETURN 1; |
| 155 |
END; |
| 156 |
' |
| 157 |
LANGUAGE 'plpgsql'; |