incubator-lokahi-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From tob...@apache.org
Subject svn commit: r392982 [5/25] - in /incubator/lokahi/lokahi/trunk: ./ conf/ database/ docs/ lib/ src/ src/java/ src/java/lokahi/ src/java/lokahi/core/ src/java/lokahi/core/agent/ src/java/lokahi/core/agent/callable/ src/java/lokahi/core/agent/callable/con...
Date Mon, 10 Apr 2006 16:20:11 GMT
Added: incubator/lokahi/lokahi/trunk/database/functions.sql
URL: http://svn.apache.org/viewcvs/incubator/lokahi/lokahi/trunk/database/functions.sql?rev=392982&view=auto
==============================================================================
--- incubator/lokahi/lokahi/trunk/database/functions.sql (added)
+++ incubator/lokahi/lokahi/trunk/database/functions.sql Mon Apr 10 09:19:33 2006
@@ -0,0 +1,1469 @@
+CREATE OR REPLACE FUNCTION F_AM_GET_ALL_APACHE
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE ap.CONTAINER_ID, ap.CONTAINER_NAME, ap.LOG_LOCATION, ap.ENV_VARIBLES, ap.FORK_WAIT,
ap.PIDFILE_LOCATION, ap.RESTART_COMMAND, ap.START_COMMAND, ap.STOP_COMMAND, ap.SYS_GROUP,
ap.SYS_USER, ap.SERVER_ROOT, ap.main_ID
+		 FROM AM_CONTAINER ap;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_ALL_APACHEW
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM AM_WORKER_VIEW;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_ALL_VHOST
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM AM_ENTITY_VIEW;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_APACHEW_BY_ID(v_id IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM AM_WORKER_VIEW aw
+		 where aw.WORKER_ID=v_id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_APACHE_BY_ID(v_id IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT ap.CONTAINER_ID, ap.CONTAINER_NAME, ap.LOG_LOCATION, ap.ENV_VARIBLES, ap.FORK_WAIT,
ap.PIDFILE_LOCATION, ap.RESTART_COMMAND, ap.START_COMMAND, ap.STOP_COMMAND, ap.SYS_GROUP,
ap.SYS_USER, ap.SERVER_ROOT, ap.MAIN_ID
+		 FROM AM_CONTAINER ap
+		 WHERE ap.CONTAINER_ID = v_id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_APACHE_BY_name(v_id IN VARCHAR2)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT ap.CONTAINER_ID, ap.CONTAINER_NAME, ap.LOG_LOCATION, ap.ENV_VARIBLES, ap.FORK_WAIT,
ap.PIDFILE_LOCATION, ap.RESTART_COMMAND, ap.START_COMMAND, ap.STOP_COMMAND, ap.SYS_GROUP,
ap.SYS_USER, ap.SERVER_ROOT, ap.MAIN_ID
+		 FROM AM_CONTAINER ap
+		 WHERE lower(ap.CONTAINER_name) like lower(v_id);
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_AW_BY_AID(v_aId IN NUMBER
+
+												  )
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM AM_WORKER_VIEW aw
+		 where aw.CONTAINER_ID=v_aId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_AW_BY_HID_AID(v_aId IN NUMBER,
+	   	  		  		   						  v_hId	IN NUMBER
+												  )
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM AM_WORKER_VIEW aw
+		 where aw.CONTAINER_ID=v_aId
+		 AND   aw.HARDWARE_ID=v_hId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_AW_BY_HW(v_hId IN NUMBER
+												  )
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM AM_WORKER_VIEW aw
+		 where aw.HARDWARE_ID=v_hId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_AW_BY_name(v_name IN varchar2)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM AM_WORKER_VIEW aw
+		 where lower(hardware_name) like lower(v_name);
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_AW_BY_ni(v_name IN varchar2,
+	   	  		  		   					 v_iid	IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM AM_WORKER_VIEW aw
+		 where hardware_name like v_name
+		 AND instance_id = v_iid;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_AW_BY_POOLID(v_pId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT worker_id, aw.container_id, aw.state_id, aw.hardware_id, aw.container_name, aw.server_root,
aw.log_location, aw.sys_user,
+  aw.sys_group, aw.start_command, aw.stop_command, aw.restart_command, aw.fork_wait, aw.pidfile_location,
aw.env_varibles, aw.main_id,
+  aw.hardware_name, aw.physical_location, aw.comments, aw.instance_id, aw.env_id, aw.hardware_state_id,
+  aw.default_ip_id
+		 FROM (AM_WORKER_VIEW aw JOIN AM_REL_POOL_WORKER using (WORKER_ID)) where pool_id=v_pId;
+		     RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_HPOOLS_BY_APID(v_Id IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE hp.HOSTING_POOL_ID, hp.HOSTING_POOL_NAME, hp.ENV_ID, rel.AM_POOL_ID, rel.TM_POOL_ID
+		 FROM TMC_Hosting_Pool hp,
+		 	  TMC_REL_HOSTING_MODULES rel,
+			  AM_REL_POOL_WORKER rpw
+		 Where rel.HOSTING_POOL_ID = hp.HOSTING_POOL_ID
+		 AND rel.AM_POOL_ID=v_id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_HPOOLS_BY_AWID(v_Id IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE hp.HOSTING_POOL_ID, hp.HOSTING_POOL_NAME, hp.ENV_ID, rel.AM_POOL_ID, rel.TM_POOL_ID
+		 FROM TMC_Hosting_Pool hp,
+		 	  TMC_REL_HOSTING_MODULES rel,
+			  AM_REL_POOL_WORKER rpw
+		 Where rel.HOSTING_POOL_ID = hp.HOSTING_POOL_ID
+		 AND rel.AM_POOL_ID=rpw.POOL_ID
+		 AND rpw.WORKER_ID=v_id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_POOLS
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE p.POOL_ID, p.POOL_NAME, p.ENV_ID
+		 FROM AM_POOL p;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_POOLs_BY_name(v_name IN varchar2)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE p.POOL_ID, p.POOL_NAME, p.ENV_ID
+		 FROM AM_POOL p
+		 WHERE lower(p.POOL_name) like lower(v_name);
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_POOL_BY_AWID(v_id IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE p.POOL_ID, p.POOL_NAME, p.ENV_ID
+		 FROM AM_POOL p, AM_REL_POOL_WORKER rel
+		 WHERE p.POOL_ID=rel.POOL_ID
+		 and rel.WORKER_ID=v_id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_POOL_BY_ID(v_id IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE p.POOL_ID, p.POOL_NAME, p.ENV_ID
+		 FROM AM_POOL p
+		 WHERE p.POOL_ID=v_id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_POOL_BY_vhost(v_vhId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE p.POOL_ID, p.POOL_NAME, p.ENV_ID
+		 FROM AM_POOL p,
+		 	  TMC_REL_HOSTING_MODULES rhp,
+			  AM_VHOST vh
+		 WHERE p.POOL_ID=rhp.AM_POOL_ID
+		 AND   rhp.HOSTING_POOL_ID=vh.HOSTING_POOL_ID
+		 AND   vh.VHOST_ID=v_vhId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_vhosts_by_project(v_pid IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM AM_ENTITY_VIEW join AM_REL_ENTITY_PROJECT USING (VHOST_ID) where project_id = v_pid;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_VHOST_BY_AW(v_awId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT vh.VHOST_ID, vh.ENV_ID, vh.STATE_ID, vh.VHOST_NAME, vh.HOSTING_POOL_ID, vh.NAMEBASED,
vh.CATCHALL, vh.VHOST_ENTRY, vh.EX_PROJECT_ID, vh.POOL_ID, vh.POOL_NAME, vh.POOL_ENV_ID
+		 FROM AM_ENTITY_VIEW vh, AM_REL_POOL_WORKER aw
+		 where vh.pool_id = aw.pool_ID
+		 AND   aw.WORKER_ID=v_awId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_VHOST_BY_HP(v_hpId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM AM_ENTITY_VIEW vh
+		 where vh.hosting_POOL_ID=v_hpId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_VHOST_BY_ID(v_id IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM AM_ENTITY_VIEW vh
+		 where vh.vhost_ID=v_id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_AM_GET_VHOST_BY_NAME(name IN varchar2)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM AM_ENTITY_VIEW vh
+		 where lower(vh.vhost_name)  like lower(name);
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE function f_get_admin_info(v_inst IN NUMBER)
+return bi_list is
+v_table bi_list:=bi_list();
+begin
+declare
+	   v_prim TMC_ADMIN.PRIMARY_JVM%TYPE;
+	   v_back TMC_ADMIN.BACKUP_JVM%TYPE;
+	   v_alt TMC_ADMIN.ALTERNATE_JVM%TYPE;
+	   begin
+	   		select unique primary_jvm, backup_jvm, alternate_jvm
+			into v_prim, v_back, v_alt from TMC_ADMIN where INSTANCE_ID=v_inst;
+			v_table.extend;
+			v_table(1) := bi_type('PRIMARY', v_prim);
+			v_table.extend;
+			v_table(2) := bi_type('BACKUP', v_back);
+			v_table.extend;
+			v_table(3) := bi_type('ALTERNATE', v_alt);
+	   end;
+return v_table;
+end;
+/
+CREATE OR REPLACE FUNCTION f_last_sequence_number( v_seqname IN VARCHAR2)
+RETURN NUMBER IS
+v_lastnum NUMBER:=-1;
+BEGIN
+	 DECLARE
+	 	BEGIN
+	 		 SELECT last_number INTO v_lastnum FROM user_sequences
+			 WHERE LOWER(sequence_name)=LOWER(v_seqname);
+			 -- but the last number is actually this -1
+			 v_lastnum:=v_lastnum-1;
+	 	EXCEPTION WHEN NO_DATA_FOUND THEN v_lastnum:=-1;
+	 END;
+RETURN v_lastnum;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_ACTIVE_ATS_FOR_ALL
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT a.ALERT_ID, a.SUBMISION_DATE, a.SUBMITTER_ID, a.ALERT_TITLE, a.ALERT_LINK, a.alert_contents
+		 FROM TMC_ALERTS a, TMC_REL_ALERT_PROJECT rel
+		 where rel.project_id=-1
+		 and rel.alert_ID=a.Alert_ID;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_ALERT_BY_ID(v_id IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT a.ALERT_ID, a.SUBMISION_DATE, a.SUBMITTER_ID, a.ALERT_TITLE, a.ALERT_LINK, a.alert_contents
+		 FROM TMC_ALERTS a
+		 where a.ALERT_ID=v_id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_ALL_FILES
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT f.FILE_ID, f.FILE_NAME, f.FILE_DESCRIPTOR, f.FILE_PATH, f.file_content
+		 FROM TMC_FILES f;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_ALL_FUNCTIONS
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE f.FUNCTION_ID, f.FUNCTION_COMMAND, f.FUNCTION_NAME
+		 FROM TMC_FUNCTION f;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_ALL_HARDWARE
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE h.HARDWARE_ID, h.INSTANCE_ID, h.ENV_ID, h.PHYSICAL_LOCATION, h.HARDWARE_NAME,
h.STATUS_ID, h.COMMENTS, h.DEFAULT_IP_ID
+		 FROM TMC_HARDWARE h;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_CONTEXTS_BY_VHOST(v_vId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT tc.*
+		 FROM TM_ENTITY_view tc,
+		 	  TMC_REL_VHOST_CONTEXT rel
+		 WHERE tc.ENTITY_ID=rel.CONTEXT_ID
+		 AND   rel.VHOST_ID=v_vId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_ENVIROMENTS
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE e.ENV_ID, e.ENV_NAME, e.COMMENTS
+		 FROM TMC_ENV e;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_ENVIROMENTS_BY_Proj(v_pId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+			 SELECT unique e.env_id, e.env_name, e.comments
+		 FROM TMC_ENV e,
+			  AM_REL_ENTITY_Project ag,
+			  AM_VHOST v
+
+		 where ag.vhost_id = v.vhost_id
+		 and   v.env_id	   = e.env_id
+		 and   ag.project_id = v_pId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_ENVIROMENT_BY_ID(v_Id IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE e.ENV_ID, e.ENV_NAME, e.COMMENTS
+		 FROM TMC_ENV e
+		 WHERE e.ENV_ID = v_Id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_EX_PROJECTS
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT ex.EX_PROJECT_ID, ex.EX_PROJECT_NAME
+		 FROM TMC_EX_PROJECT ex;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_EX_PROJECTS_BY_User(v_Uid IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT EX_PROJECT_ID, ex.EX_PROJECT_NAME
+		 FROM (TMC_EX_PROJECT ex join TMC_REL_USER_EXPROJ uex using (ex_PROJECT_ID)) where uex.USER_ID=v_Uid;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_EX_PROJECT_BY_ID(v_exPid IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT ex.EX_PROJECT_ID, ex.EX_PROJECT_NAME
+		 FROM TMC_EX_PROJECT ex WHERE ex.EX_PROJECT_ID = v_exPid;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_EX_PROJECT_BY_NAME(v_exPName IN VARCHAR2)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT ex.EX_PROJECT_ID, ex.EX_PROJECT_NAME
+		 FROM TMC_EX_PROJECT ex WHERE ex.EX_PROJECT_NAME = v_exPName;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_FILE_BY_ID(v_id IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT f.FILE_ID, f.FILE_NAME, f.FILE_DESCRIPTOR, f.FILE_PATH, f.file_content
+		 FROM TMC_FILES f
+		 WHERE f.FILE_ID=v_id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_FUNCTIONS_FOR_USER(v_uId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE USER_ID, PROJECT_ID, OBJECT_TYPE, OBJECT_ID, FUNCTION_ID, FUNCTION_NAME,
FUNCTION_COMMAND
+		 FROM TMC_rel_function_user join tmc_function using (Function_ID) WHERE USER_ID = v_uId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_FUNCTION_BY_COMMAND(v_command VARCHAR2)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE f.FUNCTION_COMMAND, f.FUNCTION_ID, f.FUNCTION_NAME
+		 FROM TMC_FUNCTION f WHERE f.FUNCTION_COMMAND = v_command;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_FUNCTION_BY_ID(v_functionId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE f.FUNCTION_ID, f.FUNCTION_COMMAND, f.FUNCTION_NAME
+		 FROM TMC_FUNCTION f WHERE f.FUNCTION_ID = v_functionId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_FUNC_FOR_USER(v_uId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE USER_ID, PROJECT_ID, OBJECT_TYPE, OBJECT_ID, FUNCTION_ID
+		 FROM TMC_rel_function_user WHERE USER_ID = v_uId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_HARDWARE_BY_ID(v_hardwareId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE h.HARDWARE_ID, h.INSTANCE_ID, h.ENV_ID, h.PHYSICAL_LOCATION, h.HARDWARE_NAME,
h.STATUS_ID, h.COMMENTS, h.DEFAULT_IP_ID
+		 FROM TMC_HARDWARE h WHERE h.HARDWARE_ID = v_hardwareId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_HARDWARE_BY_NAME(v_hardwareName IN VARCHAR2)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE h.HARDWARE_ID, h.INSTANCE_ID, h.ENV_ID, h.PHYSICAL_LOCATION, h.HARDWARE_NAME,
h.STATUS_ID, h.COMMENTS, h.DEFAULT_IP_ID
+		 FROM TMC_HARDWARE h WHERE lower(h.HARDWARE_NAME) like lower(v_hardwareName);
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_HARDWARE_BY_NAME_i(v_hardwareName IN VARCHAR2,
+	   	  		  		   								v_instanceId   IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE h.HARDWARE_ID, h.INSTANCE_ID, h.ENV_ID, h.PHYSICAL_LOCATION, h.HARDWARE_NAME,
h.STATUS_ID, h.COMMENTS, h.DEFAULT_IP_ID
+		 FROM TMC_HARDWARE h WHERE h.HARDWARE_NAME = v_hardwareName
+		 AND h.INSTANCE_ID=v_instanceId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_HELP_ITEMS
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT hi.HELP_ITEM_ID, hi.HELP_ITEM_TITLE, hi.VALID, hi.SUMMARY, hi.content, top.TOPIC_NAME,
hi.FUNCTION_ID, st.TOPIC_NAME as SUBTOPIC_NAME
+		 FROM TMC_HELP_ITEM hi,
+		      TMC_TOPIC top,
+			  TMC_TOPIC st,
+		      TMC_REL_HELP_TOPIC th
+		      WHERE th.HELP_ITEM_ID=hi.HELP_ITEM_ID AND
+		      top.TOPIC_ID = th.TOPIC_ID AND
+		      st.TOPIC_ID = th.SUBTOPIC_ID;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_HELP_ITEM_BY_FID(f_Id IN NUMBER,
+	   	  		  		   								valid IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE  hi.HELP_ITEM_ID, hi.HELP_ITEM_TITLE, hi.VALID, hi.content, hi.SUMMARY,
top.TOPIC_NAME, hi.FUNCTION_ID, st.TOPIC_NAME as SUBTOPIC_NAME
+		 FROM TMC_HELP_ITEM hi,
+		      TMC_TOPIC top,
+			  TMC_TOPIC st,
+		      TMC_REL_HELP_TOPIC th
+		      WHERE hi.FUNCTION_ID=f_Id  AND
+			  hi.valid = valid AND
+		      th.HELP_ITEM_ID=hi.HELP_ITEM_ID AND
+		      top.TOPIC_ID = th.TOPIC_ID AND
+		      st.TOPIC_ID = th.SUBTOPIC_ID;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_HELP_ITEM_BY_ID(h_Id IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT hi.HELP_ITEM_ID, hi.HELP_ITEM_TITLE, hi.VALID, hi.SUMMARY, hi.content, top.TOPIC_NAME,
hi.FUNCTION_ID, st.TOPIC_NAME as SUBTOPIC_NAME
+		 FROM TMC_HELP_ITEM hi,
+		      TMC_TOPIC top,
+			  TMC_TOPIC st,
+		      TMC_REL_HELP_TOPIC th
+		      WHERE hi.HELP_ITEM_ID=h_Id  AND
+		      th.HELP_ITEM_ID=hi.HELP_ITEM_ID AND
+		      top.TOPIC_ID = th.TOPIC_ID AND
+		      st.TOPIC_ID = th.SUBTOPIC_ID;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_HELP_ITEM_BY_KEYWORD(keyword IN VARCHAR2,
+	   	  		  		   								valid IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE  hi.HELP_ITEM_ID, hi.HELP_ITEM_TITLE, hi.VALID, hi.content,hi.SUMMARY, top.TOPIC_NAME,
hi.FUNCTION_ID, st.TOPIC_NAME as SUBTOPIC_NAME
+		 FROM TMC_HELP_ITEM hi,
+		      TMC_TOPIC top,
+			  TMC_TOPIC st,
+		      TMC_REL_HELP_TOPIC th,
+			  TMC_REL_HELP_keyword tk,
+			  TMC_KEYWORD kw
+		      WHERE   kw.KEYWORD_NAME like '%'|| keyword ||'%' AND
+			  tk.KEYWORD_ID = kw.KEYWORD_ID AND
+			  hi.HELP_ITEM_ID = tk.HELP_ITEM_ID  AND
+			  hi.valid = valid AND
+		      th.HELP_ITEM_ID = hi.HELP_ITEM_ID AND
+		      top.TOPIC_ID = th.TOPIC_ID AND
+		      st.TOPIC_ID = th.SUBTOPIC_ID;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_HELP_ITEM_BY_TITLE(h_title IN VARCHAR2)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE hi.HELP_ITEM_ID, hi.HELP_ITEM_TITLE, hi.VALID, hi.SUMMARY, hi.content, top.TOPIC_NAME,
hi.FUNCTION_ID, st.TOPIC_NAME as SUBTOPIC_NAME
+		 FROM TMC_HELP_ITEM hi,
+		      TMC_TOPIC top,
+			  TMC_TOPIC st,
+		      TMC_REL_HELP_TOPIC th
+		      WHERE hi.HELP_ITEM_TITLE=h_title  AND
+		      th.HELP_ITEM_ID=hi.HELP_ITEM_ID AND
+		      top.TOPIC_ID = th.TOPIC_ID AND
+		      st.TOPIC_ID = th.SUBTOPIC_ID;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_HOSTINGPOOLS
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE hp.HOSTING_POOL_ID, hp.HOSTING_POOL_NAME, hp.ENV_ID, rel.AM_POOL_ID, rel.TM_POOL_ID
+		 FROM TMC_Hosting_Pool hp,
+		 	  TMC_REL_HOSTING_MODULES rel
+		 Where rel.HOSTING_POOL_ID = hp.HOSTING_POOL_ID;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_HOSTINGPOOL_BY_ID(v_Id IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE hp.HOSTING_POOL_ID, hp.HOSTING_POOL_NAME, hp.ENV_ID, rel.AM_POOL_ID, rel.TM_POOL_ID
+		 FROM TMC_Hosting_Pool hp,
+		 	  TMC_REL_HOSTING_MODULES rel
+		 Where rel.HOSTING_POOL_ID = hp.HOSTING_POOL_ID
+		 AND hp.HOSTING_POOL_ID=v_Id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_HPOOLS_BY_name(v_name IN varchar2)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE hp.HOSTING_POOL_ID, hp.HOSTING_POOL_NAME, hp.ENV_ID, rel.AM_POOL_ID, rel.TM_POOL_ID
+		 FROM TMC_Hosting_Pool hp,
+		 	  TMC_REL_HOSTING_MODULES rel
+		 Where rel.HOSTING_POOL_ID = hp.HOSTING_POOL_ID
+		 AND lower(hp.HOSTING_POOL_name) like lower(v_name);
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_INSTANCES
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE i.INSTANCE_NAME, i.INSTANCE_ID, i.COMMENTS
+		 FROM TMC_INSTANCE i;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_INSTANCE_BY_ID(v_Id IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE i.INSTANCE_NAME, i.INSTANCE_ID, i.COMMENTS
+		 FROM TMC_INSTANCE i WHERE i.INSTANCE_ID = v_Id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_INSTANCE_BY_NAME(v_Id IN VARCHAR2)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE i.INSTANCE_NAME, i.INSTANCE_ID, i.COMMENTS
+		 FROM TMC_INSTANCE i WHERE i.INSTANCE_NAME = v_Id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_IPS
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE i.IP_ID, i.IP
+		 FROM TMC_IP i;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_IPS_BY_HARDWARE_ID(v_hardwareId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE hr.HARDWARE_ID, i.IP_ID, i.IP
+		 FROM TMC_REL_HARDWARE_IP hr, TMC_IP i
+		 WHERE hr.HARDWARE_ID = v_hardwareId
+		 AND i.IP_ID = hr.IP_ID;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_IPS_BY_VHOST(v_vId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE i.IP_ID, i.IP
+		 FROM TMC_IP i, AM_REL_VHOST_IP rel
+		 WHERE i.IP_ID = rel.IP_ID
+		 AND   rel.VHOST_ID=v_vId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_IP_BY_ID(v_Id IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE i.IP_ID, i.IP
+		 FROM TMC_IP i
+		 WHERE i.IP_ID = v_Id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_IP_BY_NAME(v_Ip IN VARCHAR2)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE i.IP_ID, i.IP
+		 FROM TMC_IP i
+		 WHERE i.IP = v_Ip;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_JOBPOOLS_BY_ID(v_userId IN NUMBER)
+RETURN tmctypes.cursorType
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		SELECT JOB_POOL_ID, JOB_POOL_NAME, PROJECT_ID, USER_ID, START_TIME, FINISH_TIME, STATE_ID,
OPTIONS, FUNCTION_ID
+		FROM TMC_JOB_POOL tjp
+		WHERE USER_ID = v_userId ORDER BY JOB_POOL_ID DESC;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_JOBPOOLS_BY_Project(v_Id IN NUMBER)
+RETURN tmctypes.cursorType
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		SELECT JOB_POOL_ID, JOB_POOL_NAME, PROJECT_ID, USER_ID, START_TIME, FINISH_TIME, STATE_ID,
OPTIONS, FUNCTION_ID
+		FROM TMC_JOB_POOL tjp
+		WHERE Project_ID = v_Id ORDER BY JOB_POOL_ID DESC;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_JOBPOOLS_BY_State(v_Id IN NUMBER)
+RETURN tmctypes.cursorType
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		SELECT JOB_POOL_ID, JOB_POOL_NAME, PROJECT_ID, USER_ID, START_TIME, FINISH_TIME, STATE_ID,
OPTIONS, FUNCTION_ID
+		FROM TMC_JOB_POOL tjp
+		WHERE State_ID = v_Id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_JOBPOOL_BY_ID(v_poolId IN NUMBER)
+RETURN tmctypes.cursorType
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		SELECT JOB_POOL_ID, JOB_POOL_NAME, PROJECT_ID, USER_ID, START_TIME, FINISH_TIME, STATE_ID,
OPTIONS, FUNCTION_ID
+		FROM TMC_JOB_POOL tjp
+		WHERE JOB_POOL_ID = v_poolId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_JOBRESULT_BY_ID(v_jobid IN NUMBER)
+RETURN BLOB IS
+v_result BLOB;
+BEGIN
+DECLARE
+	   BEGIN
+	   	   SELECT j.RESULT INTO v_result FROM TMC_JOB j
+		   WHERE j.JOB_ID=v_jobid;
+	   END; -- of begin
+RETURN v_result;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_JOBs
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT j.JOB_ID, j.POOL_ID, j.JOB_NAME, j.SERVER_ID, j.START_TIME, j.FINISH_TIME,
+		 j.PARENT_ID, j.STATE_ID, j.OPTIONS, j.FUNCTION_ID, j.INSTANCE_ID, j.RESULT
+		 FROM TMC_JOB j;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION f_tmc_get_jobs_by_jobpool(v_poolId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT j.JOB_ID, j.POOL_ID, j.JOB_NAME, j.SERVER_ID, j.START_TIME, j.FINISH_TIME,
+		 j.PARENT_ID, j.STATE_ID, j.OPTIONS, j.FUNCTION_ID, j.INSTANCE_ID, j.RESULT
+		 FROM TMC_JOB j WHERE j.POOL_ID = v_poolId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_JOBS_STATE_INSTANCE(
+	   	  		  		   								 v_instId IN NUMBER,
+														 v_statId IN NUMBER
+														)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT j.JOB_ID, j.POOL_ID, j.JOB_NAME, j.SERVER_ID, j.START_TIME, j.FINISH_TIME,
+		 j.PARENT_ID, j.STATE_ID, j.OPTIONS, j.FUNCTION_ID, j.INSTANCE_ID, j.RESULT
+		 FROM TMC_JOB j,
+		 	  TMC_HARDWARE h
+		 WHERE j.INSTANCE_ID = v_instId
+		 AND   j.STATE_ID=v_statId
+		 AND   h.hardware_id=j.SERVER_ID
+		 AND   h.status_id=8;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_JOB_BY_ID(v_jobId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT j.JOB_ID, j.POOL_ID, j.JOB_NAME, j.SERVER_ID, j.START_TIME, j.FINISH_TIME,
+		 j.PARENT_ID, j.STATE_ID, j.OPTIONS, j.FUNCTION_ID, j.INSTANCE_ID, j.RESULT
+		 FROM TMC_JOB j WHERE j.JOB_ID = v_jobId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_JOB_BY_NAME(v_jobName IN VARCHAR2)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT j.JOB_ID, j.POOL_ID, j.JOB_NAME, j.SERVER_ID, j.START_TIME, j.FINISH_TIME,
+		 j.PARENT_ID, j.STATE_ID, j.OPTIONS, j.FUNCTION_ID, j.INSTANCE_ID, j.RESULT
+		 FROM TMC_JOB j WHERE j.JOB_NAME = v_jobName;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_JP_BY_State_limit(v_Id IN NUMBER)
+RETURN tmctypes.cursorType
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		SELECT JOB_POOL_ID, JOB_POOL_NAME, PROJECT_ID, USER_ID, START_TIME, FINISH_TIME, STATE_ID,
OPTIONS, FUNCTION_ID
+		FROM TMC_JOB_POOL tjp
+		WHERE State_ID = v_Id
+		and rownum < 21
+		order by job_pool_id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_PROJECTS
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		SELECT proj.PROJECT_ID, proj.PROJECT_NAME
+
+		FROM TMC_PROJECT proj;
+
+	RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_PROJECTS_BY_USER_ID(v_userId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		SELECT proj.PROJECT_ID, proj.PROJECT_NAME
+
+		FROM TMC_USER tu, TMC_REL_USER_PROJECT rel, TMC_PROJECT proj
+
+		WHERE tu.USER_ID = v_userId
+		  AND tu.USER_ID = rel.USER_ID
+		  AND rel.PROJECT_ID = proj.PROJECT_ID;
+	RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_PROJECTS_SEARCH_NAME(name IN VARCHAR2)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		SELECT proj.PROJECT_ID, proj.PROJECT_NAME
+
+		FROM TMC_PROJECT proj
+
+		WHERE lower(proj.PROJECT_NAME) like lower(name);
+	RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_PROJ_BY_ID(v_projId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+SELECT proj.PROJECT_ID, proj.PROJECT_NAME
+
+FROM TMC_PROJECT proj
+WHERE proj.PROJECT_ID = v_projId;
+RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_RECENT_PROJECTS(v_userId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+select project_id, project_name
+from
+(select max(job_pool_id), project_id  from tmc_job_pool  where user_id = v_userId  group
by project_id order by 1 desc)
+ join tmc_project using (project_id) where rownum <11;
+	RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_STATES
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE s.STATE_NAME, s.STATE_ID, s.STATE_COLOR
+		 FROM TMC_STATE s;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_STATE_BY_ID(v_stateId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE s.STATE_NAME, s.STATE_ID, s.STATE_COLOR
+		 FROM TMC_STATE s WHERE s.STATE_ID = v_stateId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION f_tmc_get_template_info(v_inst IN VARCHAR2, v_id IN NUMBER)
+RETURN BLOB IS
+v_result BLOB;
+BEGIN
+DECLARE
+	   BEGIN
+	   	   SELECT template_block INTO v_result FROM TMC_TEMPLATE_TABLE
+		   WHERE tmc_instance=v_inst AND template_id=v_id;
+	   END; -- of begin
+RETURN v_result;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_USERS
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE u.USER_ID, u.COMMENTS, u.JOB_TITLE, u.FIRST_NAME, u.LAST_NAME, u.EMAIL,
u.USER_NAME, U.STATE_ID
+		 FROM TMC_USER u;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_USERS_FROM_PROJ(v_pId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE u.USER_ID, u.COMMENTS, u.JOB_TITLE, u.FIRST_NAME, u.LAST_NAME, u.EMAIL,
u.USER_NAME, u.State_id
+		 FROM TMC_USER u, TMC_REL_USER_PROJECT rel
+		 WHERE u.USER_ID = rel.user_ID
+		 AND rel.project_ID = v_pId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_USER_BY_ID(v_userId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE u.USER_ID, u.COMMENTS, u.JOB_TITLE, u.FIRST_NAME, u.LAST_NAME, u.EMAIL,
u.USER_NAME, U.State_id
+		 FROM TMC_USER u WHERE u.USER_ID = v_userId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_GET_USER_BY_NAME(v_username IN VARCHAR2)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE u.USER_ID, u.COMMENTS, u.JOB_TITLE, u.FIRST_NAME, u.LAST_NAME, u.EMAIL,
u.USER_NAME, U.State_id
+		 FROM TMC_USER u WHERE u.USER_NAME = v_username;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TMC_JOBPOOLS_FOR_REPORTS(functionId IN NUMBER,statusId IN NUMBER,startTime
IN varchar2,endTime IN varchar2,daysOlder IN NUMBER)
+ RETURN tmctypes.cursortype
+ AS
+     l_cursor    tmctypes.cursorType;
+ BEGIN
+      IF(functionId < 0) AND (statusId < 0) THEN
+ 	OPEN l_cursor FOR
+	select JOB_POOL_ID, JOB_POOL_NAME, PROJECT_ID, USER_ID, START_TIME, FINISH_TIME, STATE_ID,
OPTIONS, FUNCTION_ID  from (
+ 	  select unique JOB_POOL_ID, JOB_POOL_NAME, PROJECT_ID, USER_ID, START_TIME, FINISH_TIME,
NVL(FINISH_TIME, CURRENT_DATE) AS F_TIME, STATE_ID, OPTIONS, FUNCTION_ID  from tmc_job_pool
where
+          start_time >= to_date(startTime, 'MM/DD/YYYY') ) where
+ 	  to_date(to_char(F_TIME,'MM/DD/YYYY'),'MM/DD/YYYY') <= to_date(endTime,'MM/DD/YYYY')
and
+          ceil(to_number(substr((f_time-start_time),instr((f_time-start_time),' ')+1,3)))
>= daysOlder;
+     END IF;
+     IF((functionId >= 0) AND (statusId < 0)) THEN
+           OPEN l_cursor FOR
+	select JOB_POOL_ID, JOB_POOL_NAME, PROJECT_ID, USER_ID, START_TIME, FINISH_TIME, STATE_ID,
OPTIONS, FUNCTION_ID  from (
+ 		   select unique JOB_POOL_ID, JOB_POOL_NAME, PROJECT_ID, USER_ID, START_TIME, FINISH_TIME,
NVL(FINISH_TIME, CURRENT_DATE) AS F_TIME, STATE_ID, OPTIONS, FUNCTION_ID  from tmc_job_pool
where
+          function_id = functionId and
+          start_time >= to_date(startTime, 'MM/DD/YYYY') ) where
+ 	  to_date(to_char(F_TIME,'MM/DD/YYYY'),'MM/DD/YYYY') <= to_date(endTime,'MM/DD/YYYY')
and
+          ceil(to_number(substr((f_time-start_time),instr((f_time-start_time),' ')+1,3)))
>= daysOlder;
+     END IF;
+ 	 IF(functionId < 0) AND (statusId >= 0) THEN
+ 	  	  OPEN l_cursor FOR
+ 	select JOB_POOL_ID, JOB_POOL_NAME, PROJECT_ID, USER_ID, START_TIME, FINISH_TIME, STATE_ID,
OPTIONS, FUNCTION_ID  from (
+ 		  select unique JOB_POOL_ID, JOB_POOL_NAME, PROJECT_ID, USER_ID, START_TIME, FINISH_TIME,
NVL(FINISH_TIME, CURRENT_DATE) AS F_TIME, STATE_ID, OPTIONS, FUNCTION_ID  from tmc_job_pool
where
+          state_id = statusId and
+          start_time >= to_date(startTime, 'MM/DD/YYYY') ) where
+ 	  to_date(to_char(F_TIME,'MM/DD/YYYY'),'MM/DD/YYYY') <= to_date(endTime,'MM/DD/YYYY')
and
+          ceil(to_number(substr((f_time-start_time),instr((f_time-start_time),' ')+1,3)))
>= daysOlder;
+     END IF;
+ 	 IF  (functionId >= 0) AND (statusId >= 0) THEN
+ 	  	  OPEN l_cursor FOR
+   	select JOB_POOL_ID, JOB_POOL_NAME, PROJECT_ID, USER_ID, START_TIME, FINISH_TIME, STATE_ID,
OPTIONS, FUNCTION_ID  from(
+ 	 	  select unique JOB_POOL_ID, JOB_POOL_NAME, PROJECT_ID, USER_ID, START_TIME, FINISH_TIME,
NVL(FINISH_TIME, CURRENT_DATE) AS F_TIME, STATE_ID, OPTIONS, FUNCTION_ID  from tmc_job_pool
where
+          function_id = functionId and
+          state_id = statusId and
+          start_time >= to_date(startTime, 'MM/DD/YYYY') ) where
+ 	  to_date(to_char(F_TIME,'MM/DD/YYYY'),'MM/DD/YYYY') <= to_date(endTime,'MM/DD/YYYY')
and
+          ceil(to_number(substr((f_time-start_time),instr((f_time-start_time),' ')+1,3)))
>= daysOlder;
+ 	 END IF;
+ 	 RETURN l_cursor;
+ END;
+/
+CREATE OR REPLACE function f_tmc_time_modified(v_tmc_instance varchar2,
+	   	  		  		   					v_table varchar2)
+return date is
+v_result date;
+begin
+	 declare
+	 v_count int:=0;
+	 begin
+	 	select count(*) into v_count from tmc_timestamp_table
+		where tmc_instance=v_tmc_instance and lower(table_name)=lower(v_table);
+		if v_count>0 then
+		    select time_modified into v_result from tmc_timestamp_table
+ 	    	where tmc_instance=v_tmc_instance and lower(table_name)=lower(v_table);
+		else
+		   insert into tmc_timestamp_table (tmc_instance, table_name, time_modified)
+		   values (v_tmc_instance, v_table, sysdate);
+		   v_result:=sysdate;
+		end if;
+	 end;
+   return v_result;
+end;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_ALL_TM4
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM TM_CONTAINER tc;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_ALL_TM4SER
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM TM_WORKER_VIEW;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION f_tm_get_containerEntry(v_id IN NUMBER)
+RETURN BLOB IS
+v_result BLOB;
+BEGIN
+DECLARE
+	   BEGIN
+	   	   SELECT tm.conf_ENTRY INTO v_result FROM TM_CONTAINER tm
+		   WHERE tm.CONTAINER_ID=v_id;
+	   END; -- of begin
+RETURN v_result;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_CONTEXTS
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM TM_ENTITY_VIEW tc;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_CONTEXTS_by_project(v_pid IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM TM_ENTITY_VIEW join TM_REL_ENTITY_PROJECT USING (ENTITY_ID) where project_id = v_pid;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_CONTEXTS_BY_TPool(v_tpId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM TM_ENTITY_VIEW tc
+		 WHERE tc.TOMCAT_POOL_ID=v_tpId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_CONTEXTS_BY_TWorker(v_twId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE tc.*
+		 FROM TM_ENTITY_view tc, TM_REL_POOL_WORKER rel
+		 WHERE rel.WORKER_ID=v_twId
+		 AND   rel.POOL_ID=tc.TOMCAT_POOL_ID;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_CONTEXT_BY_ID(v_Id IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT tc.*
+		 FROM TM_ENTITY_view tc
+		 WHERE tc.ENTITY_ID=v_Id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_CONTEXT_BY_NAME(name IN varchar2)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+			 SELECT *
+             FROM TM_ENTITY_view  where lower(entity_name)  like lower('%'||name||'%');
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_HPOOLS_BY_TPID(v_Id IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE hp.HOSTING_POOL_ID, hp.HOSTING_POOL_NAME, hp.ENV_ID, rel.AM_POOL_ID, rel.TM_POOL_ID
+		 FROM TMC_Hosting_Pool hp,
+		 	  TMC_REL_HOSTING_MODULES rel
+		 Where rel.HOSTING_POOL_ID = hp.HOSTING_POOL_ID
+		 AND rel.TM_POOL_ID=v_id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_POOLS
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE p.POOL_ID, p.POOL_NAME, p.ENV_ID
+		 FROM TM_POOL p;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_POOLS_by_name(v_name IN VARCHAR2)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE p.POOL_ID, p.POOL_NAME, p.ENV_ID
+		 FROM TM_POOL p
+		 WHERE lower(p.pool_name) like lower(v_name);
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_POOL_BY_Context(v_id IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE p.POOL_ID, p.POOL_NAME, p.ENV_ID
+		 FROM TM_POOL p,
+		 TM_ENTITY e
+		 WHERE p.POOL_ID=e.TOMCAT_POOL_ID
+		 AND   e.ENTITY_ID=v_id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_POOL_BY_ID(v_id IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE p.POOL_ID, p.POOL_NAME, p.ENV_ID
+		 FROM TM_POOL p
+		 WHERE p.POOL_ID=v_id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_POOL_BY_TWID(v_id IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT UNIQUE p.POOL_ID, p.POOL_NAME, p.ENV_ID
+		 FROM TM_POOL p, TM_REL_POOL_WORKER rel
+		 WHERE p.POOL_ID=rel.POOL_ID
+		 AND rel.WORKER_ID=v_id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_server_by_name(v_name IN VARCHAR2)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM TM_CONTAINER tc
+		 WHERE lower(tc.CONTAINER_NAME) like lower(v_name);
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_TM4_BY_ID(v_id IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM TM_CONTAINER tc
+		 WHERE tc.CONTAINER_ID = v_id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_TW_BY_HA(v_hId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM TM_WORKER_VIEW tw
+		 WHERE tw.HARDWARE_ID = v_hId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_TW_BY_HA_TM(v_hId IN NUMBER, v_cId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM TM_WORKER_VIEW tw
+		 WHERE tw.CONTAINER_ID = v_cId
+		 AND   tw.HARDWARE_ID = v_hId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_TW_BY_ID(v_Id IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM TM_WORKER_VIEW tw
+		 WHERE tw.WORKER_ID = v_Id;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_TW_BY_name(v_name IN varchar2)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM TM_WORKER_VIEW tw
+		 WHERE lower(hardware_name) like lower(v_name);
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_TW_BY_ni(v_name IN varchar2,
+	   	  		  		   					   v_iid  IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM TM_WORKER_VIEW tw
+		 WHERE hardware_name like v_name
+		 and instance_id = v_iid;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_TW_BY_POOLID(v_pId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT  *
+		 FROM (TM_WORKER_VIEW tw JOIN TM_REL_POOL_WORKER using (WORKER_ID))
+		 WHERE POOL_ID= v_pId;
+    RETURN l_cursor;
+END;
+/
+CREATE OR REPLACE FUNCTION F_TM_GET_TW_BY_TM(v_cId IN NUMBER)
+RETURN tmctypes.cursortype
+AS
+    l_cursor    tmctypes.cursorType;
+BEGIN
+    OPEN l_cursor FOR
+		 SELECT *
+		 FROM TM_WORKER_VIEW tw
+		 WHERE tw.CONTAINER_ID = v_cId;
+    RETURN l_cursor;
+END;
+/

Added: incubator/lokahi/lokahi/trunk/database/packages.sql
URL: http://svn.apache.org/viewcvs/incubator/lokahi/lokahi/trunk/database/packages.sql?rev=392982&view=auto
==============================================================================
--- incubator/lokahi/lokahi/trunk/database/packages.sql (added)
+++ incubator/lokahi/lokahi/trunk/database/packages.sql Mon Apr 10 09:19:33 2006
@@ -0,0 +1,5 @@
+CREATE OR REPLACE PACKAGE tmctypes
+AS
+    TYPE cursorType IS REF CURSOR;
+END;
+/



Mime
View raw message