text: --
  -- API Package Body for Ontology_Term.
  --
  -- Scaffold auto-generated by gen-api.pl (H.Lapp, 2002).
  --
  -- $Id: Ontology_Term.pkb,v 1.1.1.2 2003-01-29 08:54:39 lapp Exp $
  --
  
  --
  -- Copyright 2002-2003 Genomics Institute of the Novartis Research Foundation
  -- Copyright 2002-2008 Hilmar Lapp
  -- 
  --  This file is part of BioSQL.
  --
  --  BioSQL is free software: you can redistribute it and/or modify it
  --  under the terms of the GNU Lesser General Public License as
  --  published by the Free Software Foundation, either version 3 of the
  --  License, or (at your option) any later version.
  --
  --  BioSQL is distributed in the hope that it will be useful,
  --  but WITHOUT ANY WARRANTY; without even the implied warranty of
  --  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  --  GNU Lesser General Public License for more details.
  --
  --  You should have received a copy of the GNU Lesser General Public License
  --  along with BioSQL. If not, see <http://www.gnu.org/licenses/>.
  --
  
  CREATE OR REPLACE
  PACKAGE BODY Ont IS
  
  ont_cached SG_ONTOLOGY_TERM.OID%TYPE DEFAULT NULL;
  cache_key VARCHAR2(128) DEFAULT NULL;
  
  CURSOR Ont_ID_c (
  		Ont_IDENTIFIER	IN SG_ONTOLOGY_TERM.IDENTIFIER%TYPE)
  RETURN SG_ONTOLOGY_TERM%ROWTYPE IS
  	SELECT t.* FROM SG_ONTOLOGY_TERM t
  	WHERE
  		t.IDENTIFIER = Ont_IDENTIFIER
  	;
  
  CURSOR Ont_c (
  		Ont_NAME	IN SG_ONTOLOGY_TERM.NAME%TYPE,
  		Ont_Cat_OID	IN SG_ONTOLOGY_TERM.ONT_OID%TYPE)
  RETURN SG_ONTOLOGY_TERM%ROWTYPE IS
  	SELECT t.* FROM SG_ONTOLOGY_TERM t
  	WHERE
  		t.NAME = Ont_NAME
  	AND	NVL(t.Ont_Oid, 0) = NVL(Ont_Cat_Oid, 0)
  	;
  
  
  FUNCTION get_oid(
  		Ont_OID	IN SG_ONTOLOGY_TERM.OID%TYPE DEFAULT NULL,
  		Ont_NAME	IN SG_ONTOLOGY_TERM.NAME%TYPE,
  		Ont_IDENTIFIER	IN SG_ONTOLOGY_TERM.IDENTIFIER%TYPE DEFAULT NULL,
  		Ont_DEFINITION	IN SG_ONTOLOGY_TERM.DEFINITION%TYPE DEFAULT NULL,
  		ONT_CAT_OID	IN SG_ONTOLOGY_TERM.ONT_OID%TYPE DEFAULT NULL,
  		Cat_NAME	IN SG_ONTOLOGY_TERM.NAME%TYPE DEFAULT NULL,
  		Cat_Cat_OID	IN SG_ONTOLOGY_TERM.ONT_OID%TYPE DEFAULT NULL,
  		Cat_IDENTIFIER	IN SG_ONTOLOGY_TERM.IDENTIFIER%TYPE DEFAULT NULL,
  		do_DML		IN NUMBER DEFAULT BSStd.DML_NO)
  RETURN SG_ONTOLOGY_TERM.OID%TYPE
  IS
  	pk	SG_ONTOLOGY_TERM.OID%TYPE DEFAULT NULL;
  	Ont_row Ont_c%ROWTYPE;
  	key_str VARCHAR2(128) DEFAULT Ont_NAME || '|' || Ont_Cat_Oid || '|' || Ont_Identifier;
  	ONT_OID_	SG_ONTOLOGY_TERM.ONT_OID%TYPE DEFAULT ONT_CAT_OID;
  BEGIN
  	-- initialize
  	IF (do_DML > BSStd.DML_NO) THEN
  		pk := Ont_OID;
  	END IF;
  	-- look up
  	IF pk IS NULL THEN
  		IF (key_str = cache_key) THEN
  		        pk := ont_cached;
  		ELSE
  			-- look up category SG_ONTOLOGY_TERM
  			IF (ONT_OID_ IS NULL) AND
  			   ((Cat_Name IS NOT NULL) OR
  			    (Cat_Identifier IS NOT NULL)) THEN
  				ONT_OID_ := Ont.get_oid(
  					Ont_NAME => Cat_NAME,
  					Ont_IDENTIFIER => Cat_IDENTIFIER,						do_DML => do_DML);
  			END IF;
  			-- reset cache
  			cache_key := NULL;
  			Ont_cached := NULL;
  			-- do the look up
  			IF (ONT_Identifier IS NOT NULL) THEN
  				FOR Ont_row IN Ont_ID_c(Ont_Identifier) LOOP
  		        		pk := Ont_row.OID;
  				END LOOP;
  			ELSE
  				FOR Ont_row IN Ont_c(Ont_NAME, Ont_OID_) LOOP
  		        		pk := Ont_row.OID;
  				END LOOP;
  			END IF;
  			-- cache result if found
  			IF (pk IS NOT NULL) THEN
  		    		cache_key := key_str;
  		    		Ont_cached := pk;
  			END IF;
  		END IF;
  	END IF;
  	-- insert/update if requested
  	IF (pk IS NULL) AND 
  	   ((do_DML = BSStd.DML_I) OR (do_DML = BSStd.DML_UI)) THEN
  	    	-- look up foreign keys if not provided:
  		-- look up SG_ONTOLOGY_TERM category successful?
  		IF (ONT_OID_ IS NULL) AND
  		   ((Cat_Name IS NOT NULL) OR
  		    (Cat_Identifier IS NOT NULL)) THEN
  			raise_application_error(-20101,
  				'failed to look up Ont <' || Cat_NAME || '|' || Cat_Cat_OID || '|' || Cat_IDENTIFIER || '>');
  		END IF;
  	    	-- insert the record and obtain the primary key
  	    	pk := do_insert(
  		        NAME => Ont_NAME,
  			IDENTIFIER => Ont_IDENTIFIER,
  			DEFINITION => Ont_DEFINITION,
  			ONT_OID => ONT_OID_);
  	ELSIF (do_DML = BSStd.DML_U) OR (do_DML = BSStd.DML_UI) THEN
  	        -- update the record (note that not provided FKs will not
  		-- be changed nor looked up)
  		do_update(
  			Ont_OID_	=> pk,
  		        Ont_NAME => Ont_NAME,
  			Ont_IDENTIFIER => Ont_IDENTIFIER,
  			Ont_DEFINITION => Ont_DEFINITION,
  			Ont_CAT_OID => ONT_OID_);
  	END IF;
  	-- return the primary key
  	RETURN pk;
  END;
  
  FUNCTION do_insert(
  		NAME	IN SG_ONTOLOGY_TERM.NAME%TYPE,
  		IDENTIFIER	IN SG_ONTOLOGY_TERM.IDENTIFIER%TYPE,
  		DEFINITION	IN SG_ONTOLOGY_TERM.DEFINITION%TYPE,
  		ONT_OID	IN SG_ONTOLOGY_TERM.ONT_OID%TYPE)
  RETURN SG_ONTOLOGY_TERM.OID%TYPE 
  IS
  	pk	SG_ONTOLOGY_TERM.OID%TYPE;
  BEGIN
  	-- pre-generate the primary key value
  	SELECT SG_Sequence.nextval INTO pk FROM DUAL;
  	-- insert the record
  	INSERT INTO SG_ONTOLOGY_TERM (
  		OID,
  		NAME,
  		IDENTIFIER,
  		DEFINITION,
  		ONT_OID)
  	VALUES (pk,
  		NAME,
  		IDENTIFIER,
  		DEFINITION,
  		ONT_OID)
  	;
  	-- return the new pk value
  	RETURN pk;
  END;
  
  PROCEDURE do_update(
  		Ont_OID_	IN SG_ONTOLOGY_TERM.OID%TYPE,
  		Ont_NAME	IN SG_ONTOLOGY_TERM.NAME%TYPE,
  		Ont_IDENTIFIER	IN SG_ONTOLOGY_TERM.IDENTIFIER%TYPE,
  		Ont_DEFINITION	IN SG_ONTOLOGY_TERM.DEFINITION%TYPE,
  		Ont_CAT_OID	IN SG_ONTOLOGY_TERM.ONT_OID%TYPE)
  IS
  BEGIN
  	-- update the record (and leave attributes passed as NULL untouched)
  	UPDATE SG_ONTOLOGY_TERM
  	SET
  		NAME = NVL(Ont_NAME, NAME),
  		IDENTIFIER = NVL(Ont_IDENTIFIER, IDENTIFIER),
  		DEFINITION = NVL(Ont_DEFINITION, DEFINITION),
  		ONT_OID = NVL(Ont_CAT_OID, ONT_OID)
  	WHERE OID = Ont_OID_
  	;
  END;
  
  END Ont;
  /
  
  