From: Shengqiang S. <ss...@us...> - 2005-02-25 04:47:26
|
Update of /cvsroot/gmod/schema/chado/modules/sequence/functions In directory sc8-pr-cvs1.sourceforge.net:/tmp/cvs-serv12492 Added Files: create_soi.plpgsql Log Message: creating soi ontology --- NEW FILE: create_soi.plpgsql --- --- create ontology that has instantiated located_sequence_feature part of SO --- way as it is written, the function can not be execute more than once in one connection --- when you get error like ERROR: relation with OID NNNNN does not exist --- as this is not meant to execute >1 time in one session so it should never happen --- except at testing and test failed --- disconnect and try again, in other words, it can NOT be executed >1 time in one connection --- if using EXECUTE, we can avoid this problem but code is hard to write and read (lots of ', escape char) --NOTE: private, don't call directly as relying on having temp table tmpcvtr DROP TYPE soi_type CASCADE; CREATE TYPE soi_type AS ( type_id INT, subject_id INT, object_id INT ); CREATE OR REPLACE FUNCTION _fill_cvtermpath4soinode(INTEGER, INTEGER, INTEGER, INTEGER, INTEGER) RETURNS INTEGER AS ' DECLARE origin alias for $1; child_id alias for $2; cvid alias for $3; typeid alias for $4; depth alias for $5; cterm soi_type%ROWTYPE; exist_c int; BEGIN RAISE NOTICE ''depth=% o=%, root=%, cv=%, t=%'', depth,origin,child_id,cvid,typeid; SELECT INTO exist_c count(*) FROM cvtermpath WHERE cv_id = cvid AND object_id = origin AND subject_id = child_id AND pathdistance = depth; --- longest path IF (exist_c > 0) THEN UPDATE cvtermpath SET pathdistance = depth WHERE cv_id = cvid AND object_id = origin AND subject_id = child_id; ELSE INSERT INTO cvtermpath (object_id, subject_id, cv_id, type_id, pathdistance) VALUES(origin, child_id, cvid, typeid, depth); END IF; FOR cterm IN SELECT tmp_type AS type_id, subject_id FROM tmpcvtr WHERE object_id = child_id LOOP PERFORM _fill_cvtermpath4soinode(origin, cterm.subject_id, cvid, cterm.type_id, depth+1); END LOOP; RETURN 1; END; ' LANGUAGE 'plpgsql'; CREATE OR REPLACE FUNCTION _fill_cvtermpath4soi(INTEGER, INTEGER) RETURNS INTEGER AS ' DECLARE rootid alias for $1; cvid alias for $2; ttype int; cterm soi_type%ROWTYPE; BEGIN SELECT INTO ttype cvterm_id FROM cvterm WHERE name = ''isa''; RAISE NOTICE ''got ttype %'',ttype; PERFORM _fill_cvtermpath4soinode(rootid, rootid, cvid, ttype, 0); FOR cterm IN SELECT tmp_type AS type_id, subject_id FROM tmpcvtr WHERE object_id = rootid LOOP PERFORM _fill_cvtermpath4soi(cterm.subject_id, cvid); END LOOP; RETURN 1; END; ' LANGUAGE 'plpgsql'; --- use tmpcvtr to temp store soi (virtural ontology) --- using tmp tables is faster than using recursive function to create feature type relationship --- since it gets feature type rel set by set instead of one by one --- and getting feature type rel is very expensive --- call _fillcvtermpath4soi to create path for the virtual ontology CREATE OR REPLACE FUNCTION create_soi() RETURNS INTEGER AS ' DECLARE parent soi_type%ROWTYPE; isa_id cvterm.cvterm_id%TYPE; so_term TEXT := ''so''; soi_term TEXT := ''soi''; soi_def TEXT := ''ontology of SO feature instantiated in database''; soi_cvid INTEGER; soiterm_id INTEGER; pcount INTEGER; count INTEGER := 0; cquery TEXT; BEGIN SELECT INTO isa_id cvterm_id FROM cvterm WHERE name = ''isa''; SELECT INTO soi_cvid cv_id FROM cv WHERE name = soi_term; IF (soi_cvid > 0) THEN DELETE FROM cvtermpath WHERE cv_id = soi_cvid; DELETE FROM cvterm WHERE cv_id = soi_cvid; ELSE INSERT INTO cv (name, definition) VALUES(soi_term, soi_def); END IF; SELECT INTO soi_cvid cv_id FROM cv WHERE name = soi_term; INSERT INTO cvterm (name, cv_id) VALUES(soi_term, soi_cvid); SELECT INTO soiterm_id cvterm_id FROM cvterm WHERE name = soi_term; CREATE TEMP TABLE tmpcvtr (tmp_type INT, type_id INT, subject_id INT, object_id INT); CREATE UNIQUE INDEX u_tmpcvtr ON tmpcvtr(subject_id, object_id); INSERT INTO tmpcvtr (tmp_type, type_id, subject_id, object_id) SELECT DISTINCT isa_id, soiterm_id, f.type_id, soiterm_id FROM feature f, cvterm t WHERE f.type_id = t.cvterm_id AND f.type_id > 0; EXECUTE ''select * from tmpcvtr where type_id = '' || soiterm_id || '';''; get diagnostics pcount = row_count; raise notice ''all types in feature %'',pcount; --- do it hard way, delete any child feature type from above (NOT IN clause did not work) FOR parent IN SELECT DISTINCT 0, t.cvterm_id, 0 FROM feature c, feature_relationship fr, cvterm t WHERE t.cvterm_id = c.type_id AND c.feature_id = fr.subject_id LOOP DELETE FROM tmpcvtr WHERE type_id = soiterm_id and object_id = soiterm_id AND subject_id = parent.subject_id; END LOOP; EXECUTE ''select * from tmpcvtr where type_id = '' || soiterm_id || '';''; get diagnostics pcount = row_count; raise notice ''all types in feature after delete child %'',pcount; --- create feature type relationship (store in tmpcvtr) CREATE TEMP TABLE tmproot (cv_id INTEGER not null, cvterm_id INTEGER not null, status INTEGER DEFAULT 0); cquery := ''SELECT * FROM tmproot tmp WHERE tmp.status = 0;''; ---temp use tmpcvtr to hold instantiated SO relationship for speed ---use soterm_id as type_id, will delete from tmpcvtr ---us tmproot for this as well INSERT INTO tmproot (cv_id, cvterm_id, status) SELECT DISTINCT soi_cvid, c.subject_id, 0 FROM tmpcvtr c WHERE c.object_id = soiterm_id; EXECUTE cquery; GET DIAGNOSTICS pcount = ROW_COUNT; WHILE (pcount > 0) LOOP RAISE NOTICE ''num child temp (to be inserted) in tmpcvtr: %'',pcount; INSERT INTO tmpcvtr (tmp_type, type_id, subject_id, object_id) SELECT DISTINCT fr.type_id, soiterm_id, c.type_id, p.cvterm_id FROM feature c, feature_relationship fr, tmproot p, feature pf, cvterm t WHERE c.feature_id = fr.subject_id AND fr.object_id = pf.feature_id AND p.cvterm_id = pf.type_id AND t.cvterm_id = c.type_id AND p.status = 0; UPDATE tmproot SET status = 1 WHERE status = 0; INSERT INTO tmproot (cv_id, cvterm_id, status) SELECT DISTINCT soi_cvid, c.type_id, 0 FROM feature c, feature_relationship fr, tmproot tmp, feature p, cvterm t WHERE c.feature_id = fr.subject_id AND fr.object_id = p.feature_id AND tmp.cvterm_id = p.type_id AND t.cvterm_id = c.type_id AND tmp.status = 1; UPDATE tmproot SET status = 2 WHERE status = 1; EXECUTE cquery; GET DIAGNOSTICS pcount = ROW_COUNT; END LOOP; DELETE FROM tmproot; ---get transitive closure for soi PERFORM _fill_cvtermpath4soi(soiterm_id, soi_cvid); DROP TABLE tmpcvtr; DROP TABLE tmproot; RETURN 1; END; ' LANGUAGE 'plpgsql'; |