-------------------------------------------------------------------------------
--	Implementación de una extension simbolica para Oracle
--  Osymbolic (v.01) - 2014
--
--  Autores: 
--		Johnny Villalobos Murillo
--	 	Steven R. Brenes Chavarría
--
--
-- http://www.slinfo.una.ac.cr
-------------------------------------------------------------------------------

/*
	Instrucciones de instalación:
	
	Ejecute el siguiente script utilizando la consola de Oracle
	Para ello se va crear un usuario nuevo llamado simbolico con clave root
*/

CONNECT SYSTEM/ROOT;
DROP USER SIMBOLICO CASCADE;
CREATE USER SIMBOLICO IDENTIFIED BY root DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS;
GRANT dba, create any table TO SIMBOLICO;
CONNECT SIMBOLICO/root;


----------------------------------------------------------------------------------
-- CREACION DEL DATO SIMBOLICO (INTERVALO)
----------------------------------------------------------------------------------

CREATE OR REPLACE FUNCTION minimum(a NUMBER, b NUMBER)
RETURN NUMBER
AS
BEGIN
	IF a > b THEN
		return b;
	END IF;
	return a;
END minimum;
/

CREATE OR REPLACE FUNCTION maximum(a NUMBER, b NUMBER)
RETURN NUMBER
AS
BEGIN
	IF a > b THEN
		return a;
	END IF;
	return b;
END maximum;
/

CREATE OR REPLACE TYPE Intervalo AS OBJECT (
  min NUMBER,
  max NUMBER,  
  constructor function Intervalo(a int) return self as result,
  member function toString return varchar2,
  member function toString(l int) return varchar2,
  member function w   return number,
  member function m   return number,
  member function plus(a intervalo) return intervalo,
  member function less(a intervalo) return intervalo,
  member function less				return intervalo,
  member function mvl(a intervalo) 	return intervalo,
  member function div(a intervalo) 	return intervalo
);
/

CREATE OR REPLACE TYPE BODY Intervalo
IS
	CONSTRUCTOR FUNCTION Intervalo(a int)
    RETURN SELF AS RESULT
	IS
	BEGIN
		SELF.min := a;
		SELF.max := a;		
		RETURN;
	END;
	------------------------------------------------
	member function toString return varchar2
	is
		s varchar2(100);
	begin		
		s := '[' || self.min || ',' || self.max || ']';
		RETURN s;
	end;
	------------------------------------------------
	member function toString(l int) return varchar2
	is
		s varchar2(100);
	begin		
		s := '[' || round(self.min, l) || ',' || round(self.max, l) || ']';
		RETURN s;
	end;
	------------------------------------------------
	member function w return number
	is
	begin		
		RETURN self.max - self.min;
	end;
	------------------------------------------------
	member function m return number
	is
	begin		
		RETURN (self.min + self.max)/2.0;
	end;
	------------------------------------------------
	member function plus(a intervalo) return intervalo
	is
	begin		
		return new intervalo(self.min + a.min, self.max + a.max);
	end;
	------------------------------------------------
	member function less(a intervalo) return intervalo
	is
	begin		
		return new intervalo(self.min - a.max, self.max - a.min);
	end;
	------------------------------------------------
	member function less return intervalo
	is
	begin		
		return new intervalo(-self.max, -self.min);
	end;
	------------------------------------------------
	member function mvl(a intervalo) return intervalo
	is
		p1 NUMBER;
		p2 NUMBER;
		p3 NUMBER;
		p4 NUMBER;
	begin
		 p1 := self.min * a.min;
		 p2 := self.min * a.max;
		 p3 := self.max * a.min;
		 p4 := self.max * a.max;
		return new intervalo(
			minimum(p1, minimum(p2, minimum(p3, p4))),
			maximum(p1, maximum(p2, maximum(p3, p4))));
	end;
	------------------------------------------------
	member function div(a intervalo) return intervalo
	is
		p1 NUMBER;
		p2 NUMBER;
		p3 NUMBER;
		p4 NUMBER;
	begin		
		p1 := self.min * (1.0 / a.min);
		p2 := self.min * (1.0 / a.max);
		p3 := self.max * (1.0 / a.min);
		p4 := self.max * (1.0 / a.max);
		--
		return new intervalo(
			minimum(p1, minimum(p2, minimum(p3, p4))),
			maximum(p1, maximum(p2, maximum(p3, p4))));
	end;
END;
/

CREATE TYPE SIntAggregateConstructor AS OBJECT (  
  min NUMBER,
  max NUMBER, 
  first NUMBER(1),
  --
  -- Funciones para construir Intervalo  
  static function ODCIAggregateInitialize(sctx IN OUT SIntAggregateConstructor) return number,  
  member function ODCIAggregateIterate(self IN OUT SIntAggregateConstructor, value IN number) return number,
  member function ODCIAggregateTerminate(self IN SIntAggregateConstructor, returnValue OUT Intervalo, flags IN number) return number,
  member function ODCIAggregateMerge(self IN OUT SIntAggregateConstructor, ctx2 IN SIntAggregateConstructor) return number
);
/

CREATE OR REPLACE TYPE BODY SIntAggregateConstructor IS
	static function ODCIAggregateInitialize(sctx IN OUT SIntAggregateConstructor) return number is 
	begin
		sctx := SIntAggregateConstructor(0, 0, 1);
		return ODCIConst.Success;
	end;
	
	member function ODCIAggregateIterate(self IN OUT SIntAggregateConstructor, value IN number) return number is
	begin
		if self.first = 1 then
			self.first := 0;
			self.max := value;
			self.min := value;
		end if;
		--
		--
		if value > self.max then			
			self.max := value;
		end if;
		--
		if value < self.min then
			self.min := value;
		end if;
		return ODCIConst.Success;
	end;
	
	member function ODCIAggregateTerminate(self IN SIntAggregateConstructor, returnValue OUT Intervalo, flags IN number) return number is
	begin
		returnValue := Intervalo(self.min, self.max);
		return ODCIConst.Success;
	end;

	member function ODCIAggregateMerge(self IN OUT SIntAggregateConstructor, ctx2 IN SIntAggregateConstructor) return number is
	begin	
		return ODCIConst.Success;
	end;
end;
/

CREATE FUNCTION sInt(input NUMBER) RETURN Intervalo 
AGGREGATE USING SIntAggregateConstructor;
/

----------------------------------------------------------------------------------
-- CREACION DEL DATO SIMBOLICO (CONTINUO)
----------------------------------------------------------------------------------

CREATE OR REPLACE TYPE Continuo AS OBJECT (
  valor NUMBER, 
  member function toString return varchar2
);
/

CREATE OR REPLACE TYPE BODY Continuo
IS
	member function toString return varchar2
	is		
	begin				
		RETURN valor;
	end;	
END;
/

CREATE TYPE SConAggregateConstructor AS OBJECT (  
  suma number,
  cantidad number,
  --
  -- Funciones para construir Histograma  
  static function ODCIAggregateInitialize(sctx IN OUT SConAggregateConstructor) return number,  
  member function ODCIAggregateIterate(self IN OUT SConAggregateConstructor, value IN number) return number,
  member function ODCIAggregateTerminate(self IN SConAggregateConstructor, returnValue OUT Continuo, flags IN number) return number,
  member function ODCIAggregateMerge(self IN OUT SConAggregateConstructor, ctx2 IN SConAggregateConstructor) return number
);
/

CREATE OR REPLACE TYPE BODY SConAggregateConstructor IS

	-- FUNCION COMPROBADA
	static function ODCIAggregateInitialize(sctx IN OUT SConAggregateConstructor) return number is 
	begin
		sctx := SConAggregateConstructor(0,0);
		return ODCIConst.Success;
	end;
	
	member function ODCIAggregateIterate(self IN OUT SConAggregateConstructor, value IN number) return number is
	begin
		self.suma := self.suma +  value;
		self.cantidad := self.cantidad + 1;
		return ODCIConst.Success;
	end;
	
	member function ODCIAggregateTerminate(self IN SConAggregateConstructor, returnValue OUT Continuo, flags IN number) return number is
	begin
		returnValue := new Continuo(self.suma / self.cantidad);
		return ODCIConst.Success;
	end;

	-- FUNCION COMPROBADA
	member function ODCIAggregateMerge(self IN OUT SConAggregateConstructor, ctx2 IN SConAggregateConstructor) return number is
	begin	
		return ODCIConst.Success;
	end;
end;
/

CREATE FUNCTION sCon(input NUMBER) RETURN Continuo AGGREGATE USING SConAggregateConstructor;
/


----------------------------------------------------------------------------------
-- CREACION DEL DATO SIMBOLICO (SET)
----------------------------------------------------------------------------------

CREATE OR REPLACE TYPE Observacion AS OBJECT (
  valor    VARCHAR(100),
  cantidad NUMBER, 
  constructor function Observacion(a VARCHAR) return self as result,
  member function toString return varchar2,
  member procedure aumentar
);
/

CREATE OR REPLACE TYPE BODY Observacion
IS
	CONSTRUCTOR FUNCTION Observacion(a VARCHAR)
    RETURN SELF AS RESULT
	IS
	BEGIN
		SELF.valor := a;
		SELF.cantidad := 1;		
		RETURN;
	END;
	------------------------------------------------
	member function toString return varchar2
	is
		s varchar2(100);
	begin		
		s := '[' || self.valor || ',' || self.cantidad || ')';
		RETURN s;
	end;
	------------------------------------------------
	member procedure aumentar
	is
	begin
		self.cantidad := self.cantidad + 1;
	end;
END;
/

CREATE OR REPLACE TYPE observaciones IS VARRAY(1000000000) OF Observacion;
/

CREATE OR REPLACE TYPE Conjunto AS OBJECT (
  categorias observaciones,
  cantidad NUMBER, 
  constructor function Conjunto return self as result,
  member procedure insertar(v VARCHAR),
  member function toString return varchar2
);
/

CREATE OR REPLACE TYPE BODY Conjunto
IS
	CONSTRUCTOR FUNCTION Conjunto
    RETURN SELF AS RESULT
	IS
	BEGIN
		SELF.categorias := observaciones();
		SELF.cantidad := 0;
		RETURN;
	END;
	------------------------------------------------
	member function toString return varchar2
	is
		s varchar2(100);
	begin		
		s := '{';
		FOR i IN 1..cantidad LOOP
			s := s || categorias(i).valor;
			IF i < cantidad THEN
				s := s ||', ';
			END IF;
		END LOOP;
		s := s || '}';
		RETURN s;
	end;
	-------------------------------------------------
	member procedure insertar(v VARCHAR)
	IS
	BEGIN
		FOR i IN 1..cantidad LOOP
			IF categorias(i).valor = v THEN				
				return;				
			END IF;
		END LOOP;
		cantidad := cantidad + 1;
		categorias.extend;
		categorias(cantidad) := new Observacion(v);
	END;
END;
/

CREATE TYPE SSetAggregateConstructor AS OBJECT (  
  histo Conjunto,
  --
  -- Funciones para construir Histograma  
  static function ODCIAggregateInitialize(sctx IN OUT SSetAggregateConstructor) return number,  
  member function ODCIAggregateIterate(self IN OUT SSetAggregateConstructor, value IN number) return number,
  member function ODCIAggregateTerminate(self IN SSetAggregateConstructor, returnValue OUT Conjunto, flags IN number) return number,
  member function ODCIAggregateMerge(self IN OUT SSetAggregateConstructor, ctx2 IN SSetAggregateConstructor) return number
);
/

CREATE OR REPLACE TYPE BODY SSetAggregateConstructor IS

	-- FUNCION COMPROBADA
	static function ODCIAggregateInitialize(sctx IN OUT SSetAggregateConstructor) return number is 
	begin
		sctx := SSetAggregateConstructor(new Conjunto());
		return ODCIConst.Success;
	end;
	
	member function ODCIAggregateIterate(self IN OUT SSetAggregateConstructor, value IN number) return number is
	begin
		self.histo.insertar(value);
		return ODCIConst.Success;
	end;
	
	member function ODCIAggregateTerminate(self IN SSetAggregateConstructor, returnValue OUT Conjunto, flags IN number) return number is
	begin
		returnValue := histo;
		return ODCIConst.Success;
	end;

	-- FUNCION COMPROBADA
	member function ODCIAggregateMerge(self IN OUT SSetAggregateConstructor, ctx2 IN SSetAggregateConstructor) return number is
	begin	
		return ODCIConst.Success;
	end;
end;
/

CREATE FUNCTION sSet(input NUMBER) RETURN Conjunto AGGREGATE USING SSetAggregateConstructor;
/


----------------------------------------------------------------------------------
-- CREACION DEL DATO SIMBOLICO (HISTOGRAMA)
----------------------------------------------------------------------------------

CREATE OR REPLACE TYPE Histograma AS OBJECT (
  categorias observaciones,
  cantidad NUMBER, 
  constructor function Histograma return self as result,
  member procedure insertar(v VARCHAR),
  member procedure aumentar(v VARCHAR),
  member function toString return varchar2
);
/

CREATE OR REPLACE TYPE BODY Histograma
IS
	CONSTRUCTOR FUNCTION Histograma
    RETURN SELF AS RESULT
	IS
	BEGIN
		SELF.categorias := observaciones();
		SELF.cantidad := 0;
		RETURN;
	END;
	------------------------------------------------
	member function toString return varchar2
	is
		s varchar2(100);
	begin		
		s := '{';
		FOR i IN 1..cantidad LOOP
			s := s || categorias(i).toString();
			IF i < cantidad THEN
				s := s ||', ';
			END IF;
		END LOOP;
		s := s || '}';
		RETURN s;
	end;
	-------------------------------------------------
	member procedure insertar(v VARCHAR)
	IS
	BEGIN
		cantidad := cantidad + 1;
		categorias.extend;
		categorias(cantidad) := new Observacion(v);
	END;	
	--------------------------------------------------
	member procedure aumentar(v VARCHAR)
	IS
	BEGIN
		FOR i IN 1..cantidad LOOP
			IF categorias(i).valor = v THEN
				categorias(i).aumentar();
				return;				
			END IF;
		END LOOP;
		insertar(v);
	END;
END;
/

CREATE TYPE SHisAggregateConstructor AS OBJECT (  
  histo Histograma,
  --
  -- Funciones para construir Histograma  
  static function ODCIAggregateInitialize(sctx IN OUT SHisAggregateConstructor) return number,  
  member function ODCIAggregateIterate(self IN OUT SHisAggregateConstructor, value IN number) return number,
  member function ODCIAggregateTerminate(self IN SHisAggregateConstructor, returnValue OUT Histograma, flags IN number) return number,
  member function ODCIAggregateMerge(self IN OUT SHisAggregateConstructor, ctx2 IN SHisAggregateConstructor) return number
);
/

CREATE OR REPLACE TYPE BODY SHisAggregateConstructor IS

	-- FUNCION COMPROBADA
	static function ODCIAggregateInitialize(sctx IN OUT SHisAggregateConstructor) return number is 
	begin
		sctx := SHisAggregateConstructor(new Histograma());
		return ODCIConst.Success;
	end;
	
	member function ODCIAggregateIterate(self IN OUT SHisAggregateConstructor, value IN number) return number is
	begin
		self.histo.aumentar(value);
		return ODCIConst.Success;
	end;
	
	member function ODCIAggregateTerminate(self IN SHisAggregateConstructor, returnValue OUT Histograma, flags IN number) return number is
	begin
		returnValue := histo;
		return ODCIConst.Success;
	end;

	-- FUNCION COMPROBADA
	member function ODCIAggregateMerge(self IN OUT SHisAggregateConstructor, ctx2 IN SHisAggregateConstructor) return number is
	begin	
		return ODCIConst.Success;
	end;
end;
/

CREATE FUNCTION sHis(input NUMBER) RETURN Histograma AGGREGATE USING SHisAggregateConstructor;
/

---------------------------------------------------------------------------------------------
-- Funciones constructuras auxiliares
---------------------------------------------------------------------------------------------

CREATE OR REPLACE PROCEDURE CreateSymbolicTable(Xconcept varchar2, Xvaribles varchar2, XtablaOrigen varchar2, XtablaDestino varchar2)
IS
	Xconsulta          varchar2(1000) := '';
	XconceptoGenerado  varchar2(1000) := '';
	XvariableSimbolica varchar2(1000) := '';
	XcreateTable       varchar2(1000) := '';
	XprimerIngreso     smallint       := 1;
	Xcontador          int 			  := 0;
	XdatoSimbolico     varchar2(100) := '';
BEGIN
	FOR element IN (select regexp_substr(Xvaribles,'[^,]+', 1, level) a from dual connect by regexp_substr(Xvaribles, '[^,]+', 1, level) is not null)
	LOOP
		XdatoSimbolico := substr(element.a, 6);
		XdatoSimbolico := substr(XdatoSimbolico, 0, length(XdatoSimbolico) - 1);
		IF XprimerIngreso = 1 THEN
			XvariableSimbolica := element.a || ' ' || XdatoSimbolico;
			XprimerIngreso     := 0;
		ELSE
			XvariableSimbolica := XvariableSimbolica || ',' || element.a || ' ' || XdatoSimbolico;
		END IF;	
		Xcontador := Xcontador + 1;
	END LOOP;
	XprimerIngreso := 1;
	---------
	FOR element IN (select regexp_substr(Xconcept,'[^,]+', 1, level) a from dual connect by regexp_substr(Xconcept, '[^,]+', 1, level) is not null)
	LOOP		
		IF XprimerIngreso = 1 THEN
			XconceptoGenerado := '''{''||' || element.a;
			XprimerIngreso    := 0;
		ELSE 
			XconceptoGenerado := XconceptoGenerado || '||'',''||' || element.a;
		END IF;		
	END LOOP;
	XconceptoGenerado := XconceptoGenerado || '||''}''';
	Xconsulta := 'SELECT ' || XconceptoGenerado || ' concept,' || XvariableSimbolica || ' FROM ' || XtablaOrigen || ' GROUP BY ' || Xconcept || ' ORDER BY CONCEPT';
	XcreateTable := 'CREATE TABLE ' || XtablaDestino || ' AS ' || Xconsulta;
	dbms_output.put_line(Xconsulta);
	EXECUTE IMMEDIATE XcreateTable;	
END CreateSymbolicTable;
/
-- EXEC CreateSymbolicTable('a,b','sint(a),sint(b)','','medical','destino');
