/* This table is used to support the sequence feature, which is not supported
   by OLAP V1R6C10 & OLTP V1R5C20.
   CYCLE default FALSE;
   If increment_by < 0, by default max_value = -1, min_value = -99999999999999999999999999
   start_with = min_value if increment_by > 0 else start_with = max_value
*/   
\echo *** CREATION OF "MIG_SEQ_TABLE" TABLE ***
CREATE TABLE MIG_ORA_EXT.mig_seq_table
	 ( schema_name			VARCHAR(32)			DEFAULT UPPER(current_schema())			NOT NULL
	 , sequence_name		VARCHAR(32)												NOT NULL
	 , start_with			NUMBER(28,0)		DEFAULT 1							NOT NULL
	 , increment_by			NUMBER(28,0)		DEFAULT 1							NOT NULL
	 , min_value			NUMBER(28,0)		DEFAULT 1							NOT NULL
	 , max_value			NUMBER(28,0)		DEFAULT 999999999999999999999999999	NOT NULL
	 , cycle_i				BOOLEAN				DEFAULT FALSE						NOT NULL
	 , cache				NUMBER(28,0)
	 , order_i				BOOLEAN 			DEFAULT FALSE
	 , current_value		NUMBER(28,0)
	 , PRIMARY KEY			(schema_name, sequence_name)
	 );


\echo *** CREATION OF "NEXTVAL" FUNCTION ***
CREATE OR REPLACE FUNCTION MIG_ORA_EXT.nextval 
/* This function is used to generate the next value for a given sequence.
   This function will be used only for OLAP V1R6C10 & OLTP V1R5C20.
   CYCLE default FALSE;
*/
	( i_seq_name 		IN		VARCHAR ) 
RETURNS NUMBER(28,0)
AS
$$
DECLARE
     l_next_value 		NUMBER(28,0) 		:= 0; 
	 l_seq_name			VARCHAR(65);
	 l_dot_pos			SMALLINT;
	 l_schema_name		VARCHAR(32);
	 l_seq_exists		NUMBER(1)			:= 0;
BEGIN
	
   l_seq_name		:= UPPER(i_seq_name);
   l_dot_pos		:= POSITION('.' IN l_seq_name);

   /* Schema name is not given */
	IF l_dot_pos    = 0
	THEN
       l_schema_name	:= UPPER(current_schema());
       l_seq_name		:= l_seq_name;
	ELSE
       l_schema_name	:= SUBSTR(l_seq_name, 1, l_dot_pos - 1);
       l_seq_name		:= SUBSTR(l_seq_name, l_dot_pos + 1);
	END IF;

	-- EXCEPTION header is not used since it is not used by V1R5.
	-- FOR LOOP is used to avoid NO_DATA_FOUND exception.
	FOR seq IN ( SELECT start_with
					  , increment_by
					  , min_value
					  , max_value
					  , cycle_i
					  , current_value
				   FROM mig_seq_table
				  WHERE schema_name		= l_schema_name
				    AND sequence_name	= l_seq_name
				    FOR UPDATE
			  )
    LOOP
		l_seq_exists	:= 1;

		IF seq.current_value IS NULL
		THEN
			l_next_value	:= seq.start_with;
		ELSE
			l_next_value	:= seq.current_value + seq.increment_by;
		END IF;

		IF seq.increment_by > 0
		THEN
			IF l_next_value > seq.max_value
			THEN
				IF seq.cycle_i = TRUE
				THEN
					l_next_value	:= seq.min_value;
				ELSE
					RAISE EXCEPTION 'Sequence % exceeds MAXVALUE and cannot be instantiated', i_seq_name;
					RETURN NULL;
				END IF;
			END IF;
		ELSE
			IF l_next_value < seq.min_value
			THEN
				IF seq.cycle_i = TRUE
				THEN
					l_next_value	:= seq.max_value;
				ELSE
					RAISE EXCEPTION 'Sequence % NEXTVAL goes below MINVALUE and cannot be instantiated', i_seq_name;
					RETURN NULL;
				END IF;
			END IF;
		END IF;
		
	END LOOP;

	-- if invalid sequence name is provided
	IF l_seq_exists = 0
	THEN
		RAISE EXCEPTION 'Sequence % does not exist', i_seq_name;
		RETURN NULL;
	END IF;

	UPDATE mig_seq_table
	   SET current_value	= l_next_value
	 WHERE schema_name		= l_schema_name
	   AND sequence_name	= l_seq_name;

	RETURN l_next_value;
END;
$$ 
LANGUAGE plpgsql;



\echo *** CREATION OF "CURRVAL" FUNCTION ***
CREATE OR REPLACE FUNCTION MIG_ORA_EXT.currval 
/* This function is used to get the current value for a given sequence.
   This function will be used only for OLAP V1R6C10 & OLTP V1R5C20.
*/
	( i_seq_name 		IN		VARCHAR ) 
RETURNS NUMBER(28,0)
AS
$$
DECLARE
	 l_seq_name			VARCHAR(65);
	 l_dot_pos			SMALLINT;
	 l_schema_name		VARCHAR(32);
	 l_current_value	NUMBER(28,0);
	 l_seq_exists		NUMBER(1)	:= 0;
BEGIN
	
   l_seq_name		:= UPPER(i_seq_name);
   l_dot_pos		:= POSITION('.' IN l_seq_name);

   /* Schema name is not given */
   IF l_dot_pos    = 0
   THEN
       l_schema_name	:= UPPER(current_schema());
       l_seq_name		:= l_seq_name;
   ELSE
       l_schema_name	:= SUBSTR(l_seq_name, 1, l_dot_pos - 1);
       l_seq_name		:= SUBSTR(l_seq_name, l_dot_pos + 1);
   END IF;

	-- 	MAX is added to avoid NO_DATA_FOUND exception
	SELECT MAX(current_value)
		 , COUNT(*)
	  INTO l_current_value
	     , l_seq_exists
      FROM mig_seq_table
	 WHERE schema_name		= l_schema_name
	   AND sequence_name	= l_seq_name;
	
	IF l_seq_exists = 0
	THEN
		-- if invalid sequence name is provided
		RAISE EXCEPTION 'Sequence % does not exist', i_seq_name;
		RETURN NULL;
	ELSE
		-- if valid sequence name is provided
		RETURN l_current_value;	
	END IF;

END;
$$ 
LANGUAGE plpgsql;
