text: -- -*-Sql-*- mode (to keep my emacs happy)
  --
  -- API Package Body for Dbxref.
  --
  -- Scaffold auto-generated by gen-api.pl. gen-api.pl is
  -- 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 Dbx IS
  
  Dbx_cached	SG_DBXREF.OID%TYPE DEFAULT NULL;
  cache_key		VARCHAR2(128) DEFAULT NULL;
  
  CURSOR Dbx_c (
  		Dbx_ACCESSION	IN SG_DBXREF.ACCESSION%TYPE,
  		Dbx_DBNAME	IN SG_DBXREF.DBNAME%TYPE,
  		Dbx_VERSION	IN SG_DBXREF.VERSION%TYPE)
  RETURN SG_DBXREF%ROWTYPE IS
  	SELECT t.* FROM SG_DBXREF t
  	WHERE
  		t.ACCESSION = Dbx_ACCESSION
  	AND	t.DBNAME = Dbx_DBNAME
  	AND	t.VERSION = NVL(Dbx_VERSION,0)
  	;
  
  FUNCTION get_oid(
  		Dbx_OID	IN SG_DBXREF.OID%TYPE DEFAULT NULL,
  		Dbx_DBNAME	IN SG_DBXREF.DBNAME%TYPE,
  		Dbx_ACCESSION	IN SG_DBXREF.ACCESSION%TYPE,
  		Dbx_VERSION	IN SG_DBXREF.VERSION%TYPE,
  		do_DML		IN NUMBER DEFAULT BSStd.DML_NO)
  RETURN SG_DBXREF.OID%TYPE
  IS
  	pk	SG_DBXREF.OID%TYPE DEFAULT NULL;
  	Dbx_row Dbx_c%ROWTYPE;
  	key_str	VARCHAR2(128) DEFAULT Dbx_DBNAME || '|' || Dbx_ACCESSION || '|' || Dbx_VERSION;
  BEGIN
  	-- initialize
  	IF (do_DML > BSStd.DML_NO) THEN
  		pk := Dbx_OID;
  	END IF;
  	-- look up
  	IF pk IS NULL THEN
  		IF (key_str = cache_key) THEN
  			pk := Dbx_cached;
  		ELSE
  			-- reset cache
  			cache_key := NULL;
  			Dbx_cached := NULL;
  			-- do the look up
  			FOR Dbx_row IN Dbx_c(Dbx_ACCESSION, Dbx_DBNAME, Dbx_VERSION) LOOP
  		        	pk := Dbx_row.OID;
  				-- cache result
  			    	cache_key := key_str;
  			    	Dbx_cached := pk;
  			END LOOP;
  		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
  	    	--
  	    	-- insert the record and obtain the primary key
  	    	pk := do_insert(
  		        DBNAME => Dbx_DBNAME,
  			ACCESSION => Dbx_ACCESSION,
  			VERSION => Dbx_VERSION);
  	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(
  			Dbx_OID	=> pk,
  		        Dbx_DBNAME => Dbx_DBNAME,
  			Dbx_ACCESSION => Dbx_ACCESSION,
  			Dbx_VERSION => Dbx_VERSION);
  	END IF;
  	-- return the primary key
  	RETURN pk;
  END;
  
  FUNCTION do_insert(
  		DBNAME	IN SG_DBXREF.DBNAME%TYPE,
  		ACCESSION	IN SG_DBXREF.ACCESSION%TYPE,
  		VERSION	IN SG_DBXREF.VERSION%TYPE)
  RETURN SG_DBXREF.OID%TYPE 
  IS
  	pk	SG_DBXREF.OID%TYPE;
  BEGIN
  	-- pre-generate the primary key value
  	SELECT SG_Sequence.nextval INTO pk FROM DUAL;
  	-- insert the record
  	INSERT INTO SG_DBXREF (
  		OID,
  		DBNAME,
  		ACCESSION,
  		VERSION)
  	VALUES (pk,
  		DBNAME,
  		ACCESSION,
  		VERSION)
  	;
  	-- return the new pk value
  	RETURN pk;
  END;
  
  PROCEDURE do_update(
  		Dbx_OID	IN SG_DBXREF.OID%TYPE,
  		Dbx_DBNAME	IN SG_DBXREF.DBNAME%TYPE,
  		Dbx_ACCESSION	IN SG_DBXREF.ACCESSION%TYPE,
  		Dbx_VERSION	IN SG_DBXREF.VERSION%TYPE)
  IS
  BEGIN
  	-- update the record (and leave attributes passed as NULL untouched)
  	UPDATE SG_DBXREF
  	SET
  		DBNAME = NVL(Dbx_DBNAME, DBNAME),
  		ACCESSION = NVL(Dbx_ACCESSION, ACCESSION),
  		VERSION = NVL(Dbx_VERSION, VERSION)
  	WHERE OID = Dbx_OID
  	;
  END;
  
  END Dbx;
  /
  
  