Debian Codegraph @

modules/sequence/functions/create_soi.plpgsql

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