You are viewing a plain text version of this content. The canonical link for it is here.
Posted to lokahi-commits@incubator.apache.org by to...@apache.org on 2006/09/29 15:45:57 UTC
svn commit: r451328 - in
/incubator/lokahi/lokahi/branches/mysql/database/mysql: ./ README-MYSQL.txt
procs.sql tables.sql tmc_instance_data.sql tmc_rel_function_user_data.sql
tmc_user_data.sql views.sql
Author: toback
Date: Fri Sep 29 08:45:56 2006
New Revision: 451328
URL: http://svn.apache.org/viewvc?view=rev&rev=451328
Log:
Re:
http://mail-archives.apache.org/mod_mbox/incubator-lokahi-dev/200609.mbox/%3c451AD6A1.1060309@free.fr%3e
Added:
incubator/lokahi/lokahi/branches/mysql/database/mysql/
incubator/lokahi/lokahi/branches/mysql/database/mysql/README-MYSQL.txt
incubator/lokahi/lokahi/branches/mysql/database/mysql/procs.sql
incubator/lokahi/lokahi/branches/mysql/database/mysql/tables.sql
incubator/lokahi/lokahi/branches/mysql/database/mysql/tmc_instance_data.sql
incubator/lokahi/lokahi/branches/mysql/database/mysql/tmc_rel_function_user_data.sql
incubator/lokahi/lokahi/branches/mysql/database/mysql/tmc_user_data.sql
incubator/lokahi/lokahi/branches/mysql/database/mysql/views.sql
Added: incubator/lokahi/lokahi/branches/mysql/database/mysql/README-MYSQL.txt
URL: http://svn.apache.org/viewvc/incubator/lokahi/lokahi/branches/mysql/database/mysql/README-MYSQL.txt?view=auto&rev=451328
==============================================================================
--- incubator/lokahi/lokahi/branches/mysql/database/mysql/README-MYSQL.txt (added)
+++ incubator/lokahi/lokahi/branches/mysql/database/mysql/README-MYSQL.txt Fri Sep 29 08:45:56 2006
@@ -0,0 +1,12 @@
+This is the readme file for the MySQL support for Lokahi.
+
+= How To =
+
+1. Create the database on the MySQL server
+2. Load the file tables.sql
+3. Load the file views.sql
+4. Load the file procs.sql
+5. Customize and load the file tmc_instance_data.sql
+6. Customize and load the file tmc_user_data.sql
+7. Load the file tmc_rel_function_user_data.sql
+8. Create user for the Lokahi webapp
Added: incubator/lokahi/lokahi/branches/mysql/database/mysql/procs.sql
URL: http://svn.apache.org/viewvc/incubator/lokahi/lokahi/branches/mysql/database/mysql/procs.sql?view=auto&rev=451328
==============================================================================
--- incubator/lokahi/lokahi/branches/mysql/database/mysql/procs.sql (added)
+++ incubator/lokahi/lokahi/branches/mysql/database/mysql/procs.sql Fri Sep 29 08:45:56 2006
@@ -0,0 +1,2207 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+--
+-- <ScriptOptions statementTerminator="/" />
+-- SQL99
+DROP PROCEDURE IF EXISTS SP_TMC_UPDATE_HELP_REL_TOPIC;
+/
+CREATE PROCEDURE SP_TMC_UPDATE_HELP_REL_TOPIC (IN H_ID INT,
+ IN H_TOPIC_ID INT,
+ IN H_SUBTOPIC_ID INT)
+
+
+BEGIN
+
+ UPDATE TMC_REL_HELP_TOPIC SET TOPIC_ID = H_TOPIC_ID, SUBTOPIC_ID = H_SUBTOPIC_ID
+ WHERE HELP_ITEM_ID = H_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_UPDATE_HELP_REL_KEYWORD;
+/
+CREATE PROCEDURE SP_TMC_UPDATE_HELP_REL_KEYWORD (IN H_ID INT,
+ IN K_ID INT)
+
+
+BEGIN
+
+ UPDATE TMC_REL_HELP_KEYWORD SET KEYWORD_ID = K_ID
+ WHERE HELP_ITEM_ID = H_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DROP_FILE;
+/
+CREATE PROCEDURE SP_TMC_DROP_FILE
+ (
+ OUT V_RETURN INT,
+ IN V_ID INT
+
+ )
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+ DELETE FROM TMC_FILES
+ WHERE FILE_ID=V_ID;
+
+ END;
+
+/
+DROP PROCEDURE IF EXISTS SP_AM_DELETE_APACHE;
+/
+CREATE PROCEDURE SP_AM_DELETE_APACHE (IN V_AID INT,
+ OUT V_RETURN INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM AM_CONTAINER
+ WHERE CONTAINER_ID=V_AID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_AM_DELETE_POOL;
+/
+CREATE PROCEDURE SP_AM_DELETE_POOL (IN V_PID INT,
+ OUT V_RETURN INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM AM_POOL
+ WHERE POOL_ID=V_PID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_AM_DELETE_VHOST;
+/
+CREATE PROCEDURE SP_AM_DELETE_VHOST (IN V_VHID INT,
+ OUT V_RETURN INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM AM_VHOST
+ WHERE VHOST_ID=V_VHID;
+
+ DELETE FROM TMC_REL_VHOST_CONTEXT
+ WHERE VHOST_ID=V_VHID;
+ DELETE FROM AM_REL_VHOST_IP
+ WHERE VHOST_ID=V_VHID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_AM_DELETE_WORKER;
+/
+CREATE PROCEDURE SP_AM_DELETE_WORKER (IN V_WID INT,
+ OUT V_RETURN INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM AM_WORKER
+ WHERE WORKER_ID=V_WID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_AM_DELETE_WORKER_POOL;
+/
+CREATE PROCEDURE SP_AM_DELETE_WORKER_POOL
+ ( IN V_POOLID INT,
+ IN V_WORKERID INT
+ )
+
+BEGIN
+
+
+ DELETE FROM AM_REL_POOL_WORKER
+ WHERE POOL_ID=V_POOLID
+ AND WORKER_ID=V_WORKERID;
+ END;
+
+/
+DROP PROCEDURE IF EXISTS SP_AM_DROP_APACHE;
+/
+CREATE PROCEDURE SP_AM_DROP_APACHE (
+ OUT V_RETURN INT,
+ IN V_AID INT
+ )
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM AM_CONTAINER
+ WHERE CONTAINER_ID=V_AID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_AM_DROP_ENTITY;
+/
+CREATE PROCEDURE SP_AM_DROP_ENTITY (
+ OUT V_RETURN INT,
+ IN V_VHID INT
+ )
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM AM_VHOST
+ WHERE VHOST_ID=V_VHID;
+
+ DELETE FROM TMC_REL_VHOST_CONTEXT
+ WHERE VHOST_ID=V_VHID;
+ DELETE FROM AM_REL_VHOST_IP
+ WHERE VHOST_ID=V_VHID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_AM_DROP_POOL;
+/
+CREATE PROCEDURE SP_AM_DROP_POOL (OUT V_RETURN INT,
+ IN V_PID INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM AM_POOL
+ WHERE POOL_ID=V_PID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_AM_DROP_VHOSTS_REL_PROJECT;
+/
+CREATE PROCEDURE SP_AM_DROP_VHOSTS_REL_PROJECT
+ ( IN V_PID INT,
+ IN V_VID INT
+ )
+
+BEGIN
+
+
+ DELETE FROM AM_REL_ENTITY_PROJECT
+ WHERE VHOST_ID = V_VID
+ AND PROJECT_ID = V_PID;
+ END;
+
+/
+DROP PROCEDURE IF EXISTS SP_AM_DROP_VHOST_REL_CTXT;
+/
+CREATE PROCEDURE SP_AM_DROP_VHOST_REL_CTXT
+ (
+ OUT V_RETURN INT,
+ IN V_VID INT,
+ IN V_CID INT
+ )
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_REL_VHOST_CONTEXT
+ WHERE CONTEXT_ID=V_CID
+ AND VHOST_ID=V_VID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_AM_DROP_VHOST_REL_IP;
+/
+CREATE PROCEDURE SP_AM_DROP_VHOST_REL_IP
+ (
+ OUT V_RETURN INT,
+ IN V_VID INT,
+ IN V_IPID INT
+ )
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM AM_REL_VHOST_IP
+ WHERE IP_ID=V_IPID
+ AND VHOST_ID=V_VID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_AM_DROP_WORKER;
+/
+CREATE PROCEDURE SP_AM_DROP_WORKER (OUT V_RETURN INT,
+ IN V_WID INT )
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM AM_WORKER
+ WHERE WORKER_ID=V_WID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_AM_STORE_FILE_BACKUP;
+/
+CREATE PROCEDURE SP_AM_STORE_FILE_BACKUP
+ ( IN V_ID INT
+ )
+
+BEGIN
+
+ INSERT INTO TMC_FILE_ARCHIVE (JOB_ID)
+ VALUES (V_ID);
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_AM_STORE_POOL;
+/
+CREATE PROCEDURE SP_AM_STORE_POOL
+ ( OUT V_ID INT,
+ IN V_NAME VARCHAR(40),
+ IN V_EID INT
+ )
+BEGIN
+ INSERT INTO AM_POOL (POOL_NAME, ENV_ID)
+ VALUES ( V_NAME, V_EID);
+ -- get the newly added jobid
+ SELECT LAST_INSERT_ID() INTO V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_AM_STORE_VHOSTS_REL_CONTEXT;
+/
+CREATE PROCEDURE SP_AM_STORE_VHOSTS_REL_CONTEXT
+ ( IN V_VID INT,
+ IN V_CID INT
+ )
+
+BEGIN
+
+
+ INSERT INTO TMC_REL_VHOST_CONTEXT (VHOST_ID, CONTEXT_ID)
+ VALUES ( V_VID, V_CID);
+ END;
+
+/
+DROP PROCEDURE IF EXISTS SP_AM_STORE_VHOSTS_REL_IP;
+/
+CREATE PROCEDURE SP_AM_STORE_VHOSTS_REL_IP
+ ( IN V_VID INT,
+ IN V_IPID INT
+ )
+
+BEGIN
+
+
+ INSERT INTO AM_REL_VHOST_IP (VHOST_ID, IP_ID)
+ VALUES ( V_VID, V_IPID);
+ END;
+
+/
+DROP PROCEDURE IF EXISTS SP_AM_STORE_VHOSTS_REL_PROJECT;
+/
+CREATE PROCEDURE SP_AM_STORE_VHOSTS_REL_PROJECT
+ ( IN V_PID INT,
+ IN V_VID INT
+ )
+
+BEGIN
+
+
+ INSERT INTO AM_REL_ENTITY_PROJECT (VHOST_ID, PROJECT_ID)
+ VALUES ( V_VID, V_PID);
+ END;
+
+/
+DROP PROCEDURE IF EXISTS SP_AM_STORE_WORKER_POOL;
+/
+CREATE PROCEDURE SP_AM_STORE_WORKER_POOL
+ ( IN V_POOLID INT,
+ IN V_WORKERID INT
+ )
+
+BEGIN
+
+
+ INSERT INTO AM_REL_POOL_WORKER (POOL_ID, WORKER_ID)
+ VALUES (V_POOLID, V_WORKERID);
+ END;
+
+/
+DROP PROCEDURE IF EXISTS SP_AM_STORE_APACHE;
+/
+CREATE PROCEDURE SP_AM_STORE_APACHE
+ ( OUT V_ID INT,
+ IN V_NAME VARCHAR(100),
+ IN V_SYSU VARCHAR(8),
+ IN V_SYSG VARCHAR(8),
+ IN V_START VARCHAR(4000),
+ IN V_STOP VARCHAR(4000),
+ IN V_RESTART VARCHAR(4000),
+ IN V_FORK INT,
+ IN V_ENVVAR VARCHAR(4000),
+ IN V_PIDFILE VARCHAR(300),
+ IN V_SROOT VARCHAR(300),
+ IN V_LOGLOC VARCHAR(300),
+ IN V_MAINID INT
+ )
+BEGIN
+ INSERT INTO AM_CONTAINER (CONTAINER_NAME, SYS_USER, SYS_GROUP, START_COMMAND, STOP_COMMAND, RESTART_COMMAND, FORK_WAIT, ENV_VARIBLES, PIDFILE_LOCATION, SERVER_ROOT, LOG_LOCATION, MAIN_ID)
+ VALUES ( V_NAME, V_SYSU, V_SYSG, V_START, V_STOP, V_RESTART, V_FORK, V_ENVVAR, V_PIDFILE, V_SROOT, V_LOGLOC, V_MAINID);
+ -- get the newly added id
+ SELECT LAST_INSERT_ID() INTO V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_AM_UPDATE_APACHE;
+/
+CREATE PROCEDURE SP_AM_UPDATE_APACHE
+ ( IN V_ID INT,
+ IN V_NAME VARCHAR(100),
+ IN V_SYSU VARCHAR(8),
+ IN V_SYSG VARCHAR(8),
+ IN V_START VARCHAR(4000),
+ IN V_STOP VARCHAR(4000),
+ IN V_RESTART VARCHAR(4000),
+ IN V_FORK INT,
+ IN V_ENVVAR VARCHAR(4000),
+ IN V_PIDFILE VARCHAR(300),
+ IN V_SROOT VARCHAR(300),
+ IN V_LOGLOC VARCHAR(300),
+ IN V_MAINID INT
+ )
+
+BEGIN
+
+ UPDATE AM_CONTAINER AM
+ SET AM.CONTAINER_NAME=V_NAME,
+ AM.SYS_USER=V_SYSU,
+ AM.SYS_GROUP=V_SYSG,
+ AM.START_COMMAND=V_START,
+ AM.STOP_COMMAND=V_STOP,
+ AM.RESTART_COMMAND=V_RESTART,
+ AM.FORK_WAIT=V_FORK,
+ AM.ENV_VARIBLES=V_ENVVAR,
+ AM.PIDFILE_LOCATION=V_PIDFILE,
+ AM.SERVER_ROOT=V_SROOT,
+ AM.LOG_LOCATION=V_LOGLOC,
+ AM.MAIN_ID=V_MAINID
+ WHERE CONTAINER_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_AM_STORE_POOL;
+/
+CREATE PROCEDURE SP_AM_STORE_POOL
+ ( OUT V_ID INT,
+ IN V_NAME VARCHAR(40),
+ IN V_EID INT
+ )
+BEGIN
+ INSERT INTO AM_POOL (POOL_NAME, ENV_ID)
+ VALUES ( V_NAME, V_EID);
+ -- get the newly added jobid
+ SELECT LAST_INSERT_ID() INTO V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_AM_UPDATE_POOL;
+/
+CREATE PROCEDURE SP_AM_UPDATE_POOL
+ ( IN V_ID INT,
+ IN V_NAME VARCHAR(40),
+ IN V_EID INT
+ )
+BEGIN
+ UPDATE AM_POOL
+ SET POOL_NAME=V_NAME, ENV_ID=V_EID
+ WHERE POOL_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_AM_UPDATE_VHOST;
+/
+CREATE PROCEDURE SP_AM_UPDATE_VHOST
+ ( IN V_ID INT,
+ IN V_NAME VARCHAR(60),
+ IN V_STATEID INT,
+ IN V_ENVID INT,
+ IN V_HPID INT,
+ IN V_CATCH INT,
+ IN V_NAMEB INT,
+ IN V_EXPID INT,
+ IN V_ENTRY BLOB
+ )
+BEGIN
+ UPDATE AM_VHOST
+ SET VHOST_NAME=V_NAME, STATE_ID=V_STATEID, ENV_ID=V_ENVID, EX_PROJECT_ID=V_EXPID,
+ HOSTING_POOL_ID=V_HPID, CATCHALL=V_CATCH, NAMEBASED=V_NAMEB, VHOST_ENTRY=V_ENTRY
+ WHERE VHOST_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_AM_UPDATE_WORKER;
+/
+CREATE PROCEDURE SP_AM_UPDATE_WORKER
+ ( IN V_ID INT,
+ IN V_AID INT,
+ IN V_HID INT,
+ IN V_SID INT
+ )
+
+BEGIN
+ UPDATE AM_WORKER AW
+ SET AW.CONTAINER_ID=V_AID,
+ AW.HARDWARE_ID=V_HID,
+ AW.STATE_ID=V_SID
+ WHERE AW.WORKER_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_ADD_HARDWARE_IP;
+/
+CREATE PROCEDURE SP_TMC_ADD_HARDWARE_IP (
+ IN V_HARDID INT,
+ IN V_IPID INT)
+BEGIN
+ INSERT INTO TMC_REL_HARDWARE_IP (HARDWARE_ID, IP_ID)
+ VALUES (V_HARDID, V_IPID);
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_ADD_JOB;
+/
+CREATE PROCEDURE SP_TMC_ADD_JOB (
+ IN V_INSTANCEID INT,
+ IN V_FUNCTIONID INT,
+ IN V_POOLID INT,
+ IN V_SERVERID INT,
+ IN V_PARENTID INT,
+ IN V_STATE INT,
+ IN V_OPTIONS VARCHAR(4000),
+ OUT V_PK INT,
+ IN V_RESULT BLOB)
+
+BEGIN
+ INSERT INTO TMC_JOB (POOL_ID, SERVER_ID, PARENT_ID, START_TIME,
+ STATE_ID, OPTIONS, INSTANCE_ID, FUNCTION_ID, RESULT)
+ VALUES (V_POOLID, V_SERVERID, V_PARENTID, NOW(),
+ V_STATE, V_OPTIONS, V_INSTANCEID, V_FUNCTIONID, V_RESULT);
+ -- get the newly added job ID
+ SET V_PK = LAST_INSERT_ID();
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_ADD_JOB2;
+/
+CREATE PROCEDURE SP_TMC_ADD_JOB2 (
+ IN V_PK INT,
+ IN V_NAME VARCHAR(400),
+ IN V_STATE INT
+ )
+BEGIN
+ UPDATE TMC_JOB J SET J.JOB_NAME=V_NAME, J.STATE_ID=V_STATE WHERE J.JOB_ID=V_PK;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_ADD_TEMPLATE;
+/
+CREATE PROCEDURE SP_TMC_ADD_TEMPLATE (
+ IN V_INST VARCHAR(30),
+ IN V_NAME VARCHAR(100),
+ OUT V_ID INT,
+ IN V_BLOB BLOB)
+BEGIN
+ INSERT INTO TMC_TEMPLATE_TABLE (TMC_INSTANCE, TEMPLATE_NAME, TEMPLATE_BLOCK, STATUS)
+ VALUES (V_INST, V_NAME, V_BLOB, 'adding');
+ SELECT TEMPLATE_ID INTO V_ID FROM TMC_TEMPLATE_TABLE WHERE TMC_INSTANCE=V_INST AND
+ TEMPLATE_NAME=V_NAME AND STATUS='adding';
+ -- reset status column
+ UPDATE TMC_TEMPLATE_TABLE SET STATUS='' WHERE STATUS='adding';
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DELETE_ALERT;
+/
+CREATE PROCEDURE SP_TMC_DELETE_ALERT
+ (IN V_ID INT,
+ OUT V_RETURN INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+ SET V_RETURN = 0;
+ DELETE FROM TMC_ALERTS
+ WHERE ALERT_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DELETE_ALERT_REL;
+/
+CREATE PROCEDURE SP_TMC_DELETE_ALERT_REL
+ (IN V_ID INT,
+ OUT V_RETURN INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_REL_ALERT_PROJECT
+ WHERE ALERT_ID=V_ID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DELETE_ENVIROMENT;
+/
+CREATE PROCEDURE SP_TMC_DELETE_ENVIROMENT
+ (IN V_ID INT,
+ OUT V_RETURN INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_ENV
+ WHERE ENV_ID=V_ID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DELETE_EX_PROJECT;
+/
+CREATE PROCEDURE SP_TMC_DELETE_EX_PROJECT
+ (IN V_ID INT,
+ OUT V_RETURN INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_EX_PROJECT
+ WHERE EX_PROJECT_ID=V_ID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DELETE_FUNCTION;
+/
+CREATE PROCEDURE SP_TMC_DELETE_FUNCTION
+ (IN V_ID INT,
+ OUT V_RETURN INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_FUNCTION
+ WHERE FUNCTION_ID=V_ID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DELETE_HARDWARE;
+/
+CREATE PROCEDURE SP_TMC_DELETE_HARDWARE (IN V_HARDID INT,
+ OUT V_RETURN INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_HARDWARE
+ WHERE HARDWARE_ID=V_HARDID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DELETE_HARDWARE_IP;
+/
+CREATE PROCEDURE SP_TMC_DELETE_HARDWARE_IP
+ (IN V_HARDID INT,
+ IN V_IPID INT,
+ OUT V_RETURN INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_REL_HARDWARE_IP
+ WHERE HARDWARE_ID=V_HARDID AND IP_ID=V_IPID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DELETE_HELP_ITEM;
+/
+CREATE PROCEDURE SP_TMC_DELETE_HELP_ITEM
+ ( IN V_ID INT,
+ OUT V_RETURN INT
+ )
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+
+ DELETE FROM TMC_HELP_ITEM
+ WHERE HELP_ITEM_ID=V_ID;
+
+ END;
+
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DELETE_HOSTINGPOOL;
+/
+CREATE PROCEDURE SP_TMC_DELETE_HOSTINGPOOL (IN V_HPID INT,
+ OUT V_RETURN INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_HOSTING_POOL
+ WHERE HOSTING_POOL_ID=V_HPID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DELETE_INSTANCE;
+/
+CREATE PROCEDURE SP_TMC_DELETE_INSTANCE (IN V_INSTANCEID INT,
+ OUT V_RETURN INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_INSTANCE
+ WHERE INSTANCE_ID=V_INSTANCEID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DELETE_IP;
+/
+CREATE PROCEDURE SP_TMC_DELETE_IP
+ (IN V_ID INT,
+ OUT V_RETURN INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_IP
+ WHERE IP_ID=V_ID;
+
+ DELETE FROM AM_REL_VHOST_IP
+ WHERE IP_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DELETE_PROJECT;
+/
+CREATE PROCEDURE SP_TMC_DELETE_PROJECT
+ (
+ OUT V_RETURN INT,
+ IN V_ID INT
+ )
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_PROJECT
+ WHERE PROJECT_ID=V_ID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DELETE_STATE;
+/
+CREATE PROCEDURE SP_TMC_DELETE_STATE (OUT V_RETURN INT,
+ IN V_STATEID INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_STATE
+ WHERE STATE_ID=V_STATEID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DELETE_USER;
+/
+CREATE PROCEDURE SP_TMC_DELETE_USER
+ (IN V_ID INT,
+ OUT V_RETURN INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_USER
+ WHERE USER_ID=V_ID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DELETE_USER_ABIL;
+/
+CREATE PROCEDURE SP_TMC_DELETE_USER_ABIL
+ (IN V_ID INT,
+ IN V_PROJECTID INT)
+
+
+BEGIN
+
+ DELETE FROM TMC_REL_FUNCTION_USER
+ WHERE USER_ID=V_ID
+ AND PROJECT_ID=V_PROJECTID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DELETE_USER_PROJ;
+/
+CREATE PROCEDURE SP_TMC_DELETE_USER_PROJ
+ ( IN V_UID INT,
+ IN V_PID INT
+ )
+
+BEGIN
+
+
+ DELETE FROM TMC_REL_USER_PROJECT
+ WHERE USER_ID=V_UID
+ AND PROJECT_ID=V_PID;
+ END;
+
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DELETE_VHOST_REL_CTXT;
+/
+CREATE PROCEDURE SP_TMC_DELETE_VHOST_REL_CTXT
+ (IN V_VID INT,
+ IN V_CID INT,
+ OUT V_RETURN INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_REL_VHOST_CONTEXT
+ WHERE CONTEXT_ID=V_CID
+ AND VHOST_ID=V_VID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DELETE_VHOST_REL_IP;
+/
+CREATE PROCEDURE SP_TMC_DELETE_VHOST_REL_IP
+ (IN V_VID INT,
+ IN V_IPID INT,
+ OUT V_RETURN INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM AM_REL_VHOST_IP
+ WHERE IP_ID=V_IPID
+ AND VHOST_ID=V_VID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DROP_ALERT;
+/
+CREATE PROCEDURE SP_TMC_DROP_ALERT
+ (OUT V_RETURN INT,
+ IN V_ID INT
+ )
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_ALERTS
+ WHERE ALERT_ID=V_ID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DROP_ALERT_REL;
+/
+CREATE PROCEDURE SP_TMC_DROP_ALERT_REL
+ (
+ OUT V_RETURN INT,
+ IN V_ID INT
+ )
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_REL_ALERT_PROJECT
+ WHERE ALERT_ID=V_ID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DROP_ENVIRONMENT;
+/
+CREATE PROCEDURE SP_TMC_DROP_ENVIRONMENT
+ (OUT V_RETURN INT,
+ IN V_ID INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_ENV
+ WHERE ENV_ID=V_ID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DROP_FUNCTION;
+/
+CREATE PROCEDURE SP_TMC_DROP_FUNCTION
+ (OUT V_RETURN INT,
+ IN V_ID INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_FUNCTION
+ WHERE FUNCTION_ID=V_ID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DROP_HELP_ITEM;
+/
+CREATE PROCEDURE SP_TMC_DROP_HELP_ITEM
+ (
+ OUT V_RETURN INT,
+ IN V_ID INT
+
+ )
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+
+ DELETE FROM TMC_HELP_ITEM
+ WHERE HELP_ITEM_ID=V_ID;
+
+ END;
+
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DROP_HOSTINGPOOL;
+/
+CREATE PROCEDURE SP_TMC_DROP_HOSTINGPOOL (
+ OUT V_RETURN INT,
+ IN V_HPID INT
+ )
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_HOSTING_POOL
+ WHERE HOSTING_POOL_ID=V_HPID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DROP_INSTANCE;
+/
+CREATE PROCEDURE SP_TMC_DROP_INSTANCE (
+ OUT V_RETURN INT,
+ IN V_INSTANCEID INT
+ )
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_INSTANCE
+ WHERE INSTANCE_ID=V_INSTANCEID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DROP_IP;
+/
+CREATE PROCEDURE SP_TMC_DROP_IP
+ (
+ OUT V_RETURN INT,
+ IN V_ID INT
+ )
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_IP
+ WHERE IP_ID=V_ID;
+
+ DELETE FROM AM_REL_VHOST_IP
+ WHERE IP_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DROP_SERVER;
+/
+CREATE PROCEDURE SP_TMC_DROP_SERVER (
+ OUT V_RETURN INT,
+ IN V_HARDID INT
+ )
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_HARDWARE
+ WHERE HARDWARE_ID=V_HARDID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DROP_STATE;
+/
+CREATE PROCEDURE SP_TMC_DROP_STATE (OUT V_RETURN INT,
+ IN V_STATEID INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_STATE
+ WHERE STATE_ID=V_STATEID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_DROP_USER;
+/
+CREATE PROCEDURE SP_TMC_DROP_USER
+ (OUT V_RETURN INT,
+ IN V_ID INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TMC_USER
+ WHERE USER_ID=V_ID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_FINALIZE_JOBPOOL;
+/
+CREATE PROCEDURE SP_TMC_FINALIZE_JOBPOOL (
+ IN V_PK INT,
+ IN V_STATEID INT)
+
+
+BEGIN
+
+ UPDATE TMC_JOB_POOL
+ SET FINISH_TIME=NOW(), STATE_ID=V_STATEID
+ WHERE JOB_POOL_ID=V_PK;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_PREP_ALERT_ENTRY;
+/
+CREATE PROCEDURE SP_TMC_PREP_ALERT_ENTRY (
+ IN V_ID INT,
+ IN V_RESULT BLOB)
+
+BEGIN
+
+ UPDATE TMC_ALERTS
+ SET ALERT_CONTENTS=V_RESULT
+ WHERE ALERT_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_PREP_FILE_BACKUP;
+/
+CREATE PROCEDURE SP_TMC_PREP_FILE_BACKUP (
+ IN V_ID INT,
+ IN V_RESULT BLOB)
+
+BEGIN
+
+ UPDATE TMC_FILE_ARCHIVE
+ SET FILE_CONTENTS=V_RESULT
+ WHERE JOB_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_PREP_FILE_CONTENT;
+/
+CREATE PROCEDURE SP_TMC_PREP_FILE_CONTENT (
+ IN V_ID INT,
+ IN V_RESULT BLOB)
+
+BEGIN
+
+ UPDATE TMC_FILES
+ SET FILE_CONTENT=V_RESULT
+ WHERE FILE_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_PREP_JOB;
+/
+CREATE PROCEDURE SP_TMC_PREP_JOB (
+ IN V_ID INT,
+ IN V_RESULT BLOB)
+
+BEGIN
+
+ UPDATE TMC_JOB
+ SET RESULT=V_RESULT
+ WHERE JOB_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_ALERT;
+/
+CREATE PROCEDURE SP_TMC_STORE_ALERT
+ ( OUT V_ID INT,
+ IN V_TITLE VARCHAR(200),
+ IN V_SUBID INT,
+ IN V_LINK VARCHAR(4000),
+ IN V_CONTENT BLOB
+ )
+
+BEGIN
+ INSERT INTO TMC_ALERTS (ALERT_TITLE, SUBMITTER_ID, SUBMISION_DATE, ALERT_LINK, ALERT_CONTENTS)
+ VALUES ( V_TITLE, V_SUBID, NOW(), V_LINK, V_CONTENT);
+ -- get the newly added id
+ SELECT LAST_INSERT_ID() INTO V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_ALERT_REL;
+/
+CREATE PROCEDURE SP_TMC_STORE_ALERT_REL
+ (
+ IN V_ID INT,
+ IN V_PROJID INT
+ )
+
+BEGIN
+ INSERT INTO TMC_REL_ALERT_PROJECT (ALERT_ID, PROJECT_ID)
+ VALUES (V_ID, V_PROJID);
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_EX_PROJECT;
+/
+CREATE PROCEDURE SP_TMC_STORE_EX_PROJECT
+ ( OUT V_SEQ INT,
+ IN V_NAME VARCHAR(40)
+ )
+
+BEGIN
+ INSERT INTO TMC_EX_PROJECT (EX_PROJECT_NAME)
+ VALUES ( V_NAME);
+ -- get the newly added id
+ SET V_SEQ = LAST_INSERT_ID();
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_FILE;
+/
+CREATE PROCEDURE SP_TMC_STORE_FILE
+ ( OUT V_ID INT,
+ IN V_NAME VARCHAR(100),
+ IN V_DESCRIP VARCHAR(100),
+ IN V_PATH VARCHAR(2000)
+ )
+BEGIN
+ INSERT INTO TMC_FILES (FILE_NAME, FILE_DESCRIPTOR, FILE_PATH)
+ VALUES ( V_NAME, V_DESCRIP, V_PATH);
+ -- get the newly added id
+ SELECT LAST_INSERT_ID() INTO V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_FILE_BACKUP;
+/
+CREATE PROCEDURE SP_TMC_STORE_FILE_BACKUP
+ ( IN V_ID INT
+ )
+
+BEGIN
+ INSERT INTO TMC_FILE_ARCHIVE (JOB_ID)
+ VALUES (V_ID);
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_FUNCTION;
+/
+CREATE PROCEDURE SP_TMC_STORE_FUNCTION
+ ( OUT V_SEQ INT,
+ IN V_NAME VARCHAR(50),
+ IN V_COMMAND VARCHAR(50)
+ )
+
+BEGIN
+ INSERT INTO TMC_FUNCTION (FUNCTION_NAME, FUNCTION_COMMAND)
+ VALUES ( V_NAME, V_COMMAND);
+ -- get the newly added id
+ SET V_SEQ = LAST_INSERT_ID();
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_HELP_ITEM;
+/
+CREATE PROCEDURE SP_TMC_STORE_HELP_ITEM
+ ( OUT H_ID INT,
+ IN H_TITLE VARCHAR(50),
+ IN H_VALID INT,
+ IN H_FUNCTION INT,
+ IN H_SUMMARY VARCHAR(250),
+ IN H_CONTENT BLOB
+ )
+BEGIN
+ INSERT INTO TMC_HELP_ITEM(HELP_ITEM_TITLE,VALID,FUNCTION_ID,SUMMARY,CONTENT)
+ VALUES ( H_TITLE ,H_VALID, H_FUNCTION, H_SUMMARY,H_CONTENT);
+ SET H_ID = LAST_INSERT_ID();
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_HELP_REL_KEYWORD;
+/
+CREATE PROCEDURE SP_TMC_STORE_HELP_REL_KEYWORD
+ ( IN H_ID INT,
+ IN K_ID INT
+ )
+
+BEGIN
+ INSERT INTO TMC_REL_HELP_KEYWORD (HELP_ITEM_ID, KEYWORD_ID)
+ VALUES ( H_ID, K_ID);
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_HELP_REL_TOPIC;
+/
+CREATE PROCEDURE SP_TMC_STORE_HELP_REL_TOPIC
+ ( IN H_ID INT,
+ IN T_ID INT,
+ IN S_ID INT
+ )
+BEGIN
+ INSERT INTO TMC_REL_HELP_TOPIC (HELP_ITEM_ID, TOPIC_ID, SUBTOPIC_ID)
+ VALUES ( H_ID, T_ID, S_ID);
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_HOSTINGPOOL;
+/
+CREATE PROCEDURE SP_TMC_STORE_HOSTINGPOOL
+ ( OUT V_SEQ INT,
+ IN V_NAME VARCHAR(60),
+ IN V_ENVID INT,
+ IN V_AMPID INT,
+ IN V_TMPID INT
+ )
+
+BEGIN
+ INSERT INTO TMC_HOSTING_POOL ( HOSTING_POOL_NAME, ENV_ID)
+ VALUES ( V_NAME, V_ENVID);
+ -- get the newly added id
+ SET V_SEQ = LAST_INSERT_ID();
+ INSERT INTO TMC_REL_HOSTING_MODULES (AM_POOL_ID, TM_POOL_ID)
+ VALUES (V_SEQ, V_AMPID, V_TMPID);
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_INSTANCE;
+/
+CREATE PROCEDURE SP_TMC_STORE_INSTANCE
+ ( OUT V_SEQ INT,
+ IN V_NAME VARCHAR(100),
+ IN V_COMMENTS VARCHAR(4000)
+ )
+
+BEGIN
+ INSERT INTO TMC_INSTANCE (INSTANCE_NAME, COMMENTS)
+ VALUES ( V_NAME, V_COMMENTS);
+ -- get the newly added id
+ SET V_SEQ = LAST_INSERT_ID();
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_HARDWARE;
+/
+CREATE PROCEDURE SP_TMC_STORE_HARDWARE
+ ( OUT V_SEQ INT,
+ IN V_NAME VARCHAR(60),
+ IN V_LOCATION VARCHAR(500),
+ IN V_COMMENTS VARCHAR(4000),
+ IN V_INSTANCE INT,
+ IN V_ENV INT,
+ IN V_STATUS INT,
+ IN V_DEFAULTIP INT
+ )
+
+BEGIN
+ INSERT INTO TMC_HARDWARE (HARDWARE_NAME, PHYSICAL_LOCATION, COMMENTS, INSTANCE_ID, ENV_ID, STATUS_ID, DEFAULT_IP_ID)
+ VALUES ( V_NAME, V_LOCATION, V_COMMENTS, V_INSTANCE, V_ENV, V_STATUS, V_DEFAULTIP);
+ -- get the newly added id
+ SET V_SEQ = LAST_INSERT_ID();
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_IP;
+/
+CREATE PROCEDURE SP_TMC_STORE_IP
+ ( OUT V_SEQ INT,
+ IN V_NAME VARCHAR(15)
+ )
+BEGIN
+ INSERT INTO TMC_IP (IP)
+ VALUES ( V_NAME);
+ -- get the newly added id
+ SET V_SEQ = LAST_INSERT_ID();
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_PROJECT;
+/
+CREATE PROCEDURE SP_TMC_STORE_PROJECT
+ (
+ OUT V_SEQ INT,
+ IN V_NAME VARCHAR(500)
+ )
+BEGIN
+ INSERT INTO TMC_PROJECT (PROJECT_NAME)
+ VALUES ( V_NAME);
+ -- get the newly added id
+ SELECT LAST_INSERT_ID() INTO V_SEQ;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_ENVIROMENT;
+/
+CREATE PROCEDURE SP_TMC_STORE_ENVIROMENT
+ ( OUT V_SEQ INT,
+ IN V_NAME VARCHAR(400),
+ IN V_COMMENTS VARCHAR(400)
+ )
+
+BEGIN
+ INSERT INTO TMC_ENV (ENV_NAME, COMMENTS)
+ VALUES ( V_NAME, V_COMMENTS);
+ -- get the newly added id
+ SET V_SEQ = LAST_INSERT_ID();
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_UPDATE_JOBPOOL;
+/
+CREATE PROCEDURE SP_TMC_UPDATE_JOBPOOL (
+ IN V_PK INT,
+ IN V_NAME VARCHAR(400),
+ IN V_PROJECTID INT,
+ IN V_USERID INT,
+ IN V_STATEID INT,
+ IN V_OPTIONS VARCHAR(4000),
+ IN V_FUNCTIONID INT)
+
+
+BEGIN
+ UPDATE TMC_JOB_POOL
+ SET JOB_POOL_NAME=V_NAME,
+ PROJECT_ID=V_PROJECTID,
+ USER_ID=V_USERID,
+ STATE_ID=V_STATEID,
+ OPTIONS=V_OPTIONS,
+ FUNCTION_ID=V_FUNCTIONID
+ WHERE JOB_POOL_ID=V_PK;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_ADD_JOBPOOL;
+/
+CREATE PROCEDURE SP_TMC_ADD_JOBPOOL
+ (
+ OUT V_SEQ INT,
+ IN V_PROJECTID INT,
+ IN V_USERID INT,
+ IN V_STATEID INT,
+ IN V_OPTIONS VARCHAR(4000),
+ IN V_FUNCTIONID INT
+ )
+
+BEGIN
+ INSERT INTO TMC_JOB_POOL (PROJECT_ID, USER_ID, START_TIME, STATE_ID, OPTIONS, FUNCTION_ID)
+ VALUES ( V_PROJECTID, V_USERID, NOW(), V_STATEID, V_OPTIONS, V_FUNCTIONID);
+ -- get the newly added jobid
+ SELECT LAST_INSERT_ID() INTO V_SEQ;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_UPDATE_ADMIN_JVM_ROLE;
+/
+CREATE PROCEDURE SP_UPDATE_ADMIN_JVM_ROLE ( IN V_PRIM VARCHAR(60),
+ IN V_BACK VARCHAR(60),
+ IN V_ALT VARCHAR(2000),
+ IN V_INST INT)
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND
+ BEGIN
+ INSERT INTO TMC_ADMIN (PRIMARY_JVM, BACKUP_JVM, ALTERNATE_JVM, INSTANCE_ID)
+ VALUES (V_PRIM, V_BACK, V_ALT, V_INST);
+ END;
+
+ UPDATE TMC_ADMIN
+ SET PRIMARY_JVM=V_PRIM,
+ BACKUP_JVM=V_BACK,
+ ALTERNATE_JVM=V_ALT
+ WHERE INSTANCE_ID=V_INST;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_JOB;
+/
+CREATE PROCEDURE SP_TMC_STORE_JOB (
+ OUT V_PK INT,
+ IN V_INSTANCEID INT,
+ IN V_FUNCTIONID INT,
+ IN V_POOLID INT,
+ IN V_SERVERID INT,
+ IN V_PARENTID INT,
+ IN V_STATE INT,
+ IN V_OPTIONS VARCHAR(4000)
+ )
+BEGIN
+ INSERT INTO TMC_JOB (POOL_ID, SERVER_ID, PARENT_ID, START_TIME,
+ STATE_ID, OPTIONS, INSTANCE_ID, FUNCTION_ID)
+ VALUES (V_POOLID, V_SERVERID, V_PARENTID, NOW(),
+ V_STATE, V_OPTIONS, V_INSTANCEID, V_FUNCTIONID);
+ -- get the newly added job ID
+ SET V_PK = LAST_INSERT_ID();
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_JOBPOOL;
+/
+CREATE PROCEDURE SP_TMC_STORE_JOBPOOL
+ ( IN V_PROJECTID INT,
+ IN V_USERID INT,
+ IN V_STATEID INT,
+ IN V_OPTIONS VARCHAR(4000),
+ IN V_FUNCTIONID INT,
+ OUT V_SEQ INT)
+
+BEGIN
+ INSERT INTO TMC_JOB_POOL (PROJECT_ID, USER_ID, START_TIME, STATE_ID, OPTIONS, FUNCTION_ID)
+ VALUES ( V_PROJECTID, V_USERID, NOW(), V_STATEID, V_OPTIONS, V_FUNCTIONID);
+ -- get the newly added jobid
+ SELECT LAST_INSERT_ID() INTO V_SEQ;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_STATE;
+/
+CREATE PROCEDURE SP_TMC_STORE_STATE
+ ( OUT V_SEQ INT,
+ IN V_NAME VARCHAR(100),
+ IN V_COLOR VARCHAR(30)
+ )
+BEGIN
+ INSERT INTO TMC_STATE (STATE_NAME, STATE_COLOR)
+ VALUES ( V_NAME, V_COLOR);
+ -- get the newly added id
+ SELECT LAST_INSERT_ID() INTO V_SEQ;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_USER;
+/
+CREATE PROCEDURE SP_TMC_STORE_USER
+ ( OUT V_SEQ INT,
+ IN V_NAME VARCHAR(30),
+ IN V_FN VARCHAR(30),
+ IN V_LN VARCHAR(30),
+ IN V_EMAIL VARCHAR(60),
+ IN V_COMMENTS VARCHAR(2000),
+ IN V_JOBTITLE VARCHAR(100),
+ IN V_STATE INT
+ )
+BEGIN
+ INSERT INTO TMC_USER ( USER_NAME, FIRST_NAME, LAST_NAME, EMAIL, COMMENTS, JOB_TITLE, STATE_ID)
+ VALUES ( V_NAME, V_FN, V_LN, V_EMAIL, V_COMMENTS, V_JOBTITLE, V_STATE);
+ -- get the newly added id
+ SELECT LAST_INSERT_ID() INTO V_SEQ;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_USER_ABIL;
+/
+CREATE PROCEDURE SP_TMC_STORE_USER_ABIL
+ ( IN V_USERID INT,
+ IN V_OBJECTID INT,
+ IN V_OBJECTTYPE INT,
+ IN V_PROJECTID INT,
+ IN V_FUNCTIONID INT
+ )
+BEGIN
+ INSERT INTO TMC_REL_FUNCTION_USER (USER_ID, FUNCTION_ID, OBJECT_ID, OBJECT_TYPE, PROJECT_ID)
+ VALUES ( V_USERID, V_FUNCTIONID, V_OBJECTID, V_OBJECTTYPE, V_PROJECTID);
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_USER_PROJ;
+/
+CREATE PROCEDURE SP_TMC_STORE_USER_PROJ
+ ( IN V_UID INT,
+ IN V_PID INT
+ )
+
+BEGIN
+ INSERT INTO TMC_REL_USER_PROJECT (USER_ID, PROJECT_ID)
+ VALUES ( V_UID, V_PID);
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_UPDATE_ALERT;
+/
+CREATE PROCEDURE SP_TMC_UPDATE_ALERT
+ ( IN V_ID INT,
+ IN V_TITLE VARCHAR(200),
+ IN V_LINK VARCHAR(4000),
+ IN V_SUBID INT,
+ IN V_CONTENT BLOB
+ )
+BEGIN
+ UPDATE TMC_ALERTS
+ SET ALERT_TITLE=V_TITLE,
+ ALERT_LINK=V_LINK,
+ SUBMITTER_ID=V_SUBID,
+ ALERT_CONTENTS=V_CONTENT,
+ SUBMISION_DATE=NOW()
+ WHERE ALERT_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_UPDATE_FILE;
+/
+CREATE PROCEDURE SP_TMC_UPDATE_FILE
+ ( IN V_ID INT,
+ IN V_NAME VARCHAR(100),
+ IN V_DESCRIP VARCHAR(100),
+ IN V_PATH VARCHAR(2000)
+ )
+
+BEGIN
+ UPDATE TMC_FILES
+ SET FILE_NAME=V_NAME, FILE_DESCRIPTOR=V_DESCRIP, FILE_PATH=V_PATH
+ WHERE FILE_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_UPDATE_FUNCTION;
+/
+CREATE PROCEDURE SP_TMC_UPDATE_FUNCTION
+ ( IN V_ID INT,
+ IN V_NAME VARCHAR(50),
+ IN V_COMMAND VARCHAR(50)
+ )
+BEGIN
+ UPDATE TMC_FUNCTION F
+ SET F.FUNCTION_NAME=V_NAME, F.FUNCTION_COMMAND=V_COMMAND
+ WHERE FUNCTION_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_UPDATE_HARDWARE;
+/
+CREATE PROCEDURE SP_TMC_UPDATE_HARDWARE
+ ( IN V_ID INT,
+ IN V_NAME VARCHAR(60),
+ IN V_LOCATION VARCHAR(500),
+ IN V_COMMENTS VARCHAR(4000),
+ IN V_INSTANCE INT,
+ IN V_ENV INT,
+ IN V_STATUS INT,
+ IN V_DEFAULTIP INT
+ )
+BEGIN
+ UPDATE TMC_HARDWARE
+ SET HARDWARE_NAME=V_NAME, PHYSICAL_LOCATION=V_LOCATION, COMMENTS=V_COMMENTS,
+ INSTANCE_ID=V_INSTANCE, ENV_ID=V_ENV, STATUS_ID=V_STATUS, DEFAULT_IP_ID=V_DEFAULTIP
+ WHERE HARDWARE_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_UPDATE_HELP_ITEM;
+/
+CREATE PROCEDURE SP_TMC_UPDATE_HELP_ITEM
+ ( IN H_ID INT,
+ IN H_TITLE VARCHAR(50),
+ IN H_VALID INT,
+ IN H_FUNCTION INT,
+ IN H_SUMMARY VARCHAR(250),
+ IN H_CONTENT BLOB
+ )
+
+ BEGIN
+
+ UPDATE TMC_HELP_ITEM
+ SET HELP_ITEM_TITLE=H_TITLE,
+ VALID=H_VALID,
+ FUNCTION_ID=H_FUNCTION,
+ SUMMARY=H_SUMMARY,
+ CONTENT=H_CONTENT
+ WHERE HELP_ITEM_ID=H_ID;
+ END;
+
+/
+DROP PROCEDURE IF EXISTS SP_TMC_UPDATE_HOSTINGPOOL;
+/
+CREATE PROCEDURE SP_TMC_UPDATE_HOSTINGPOOL
+ ( IN V_ID INT,
+ IN V_NAME VARCHAR(60),
+ IN V_ENVID INT,
+ IN V_AMPID INT,
+ IN V_TMPID INT
+ )
+
+BEGIN
+ UPDATE TMC_HOSTING_POOL
+ SET HOSTING_POOL_NAME=V_NAME,
+ ENV_ID=V_ENVID
+ WHERE HOSTING_POOL_ID=V_ID;
+ UPDATE TMC_REL_HOSTING_MODULES
+ SET AM_POOL_ID=V_AMPID,
+ TM_POOL_ID=V_TMPID
+ WHERE HOSTING_POOL_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_UPDATE_INSTANCE;
+/
+CREATE PROCEDURE SP_TMC_UPDATE_INSTANCE
+ ( IN V_ID INT,
+ IN V_NAME VARCHAR(100),
+ IN V_COMMENTS VARCHAR(4000)
+ )
+
+BEGIN
+
+ UPDATE TMC_INSTANCE
+ SET INSTANCE_NAME=V_NAME, COMMENTS=V_COMMENTS
+ WHERE INSTANCE_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_UPDATE_IP;
+/
+CREATE PROCEDURE SP_TMC_UPDATE_IP
+ ( IN V_ID INT,
+ IN V_NAME VARCHAR(15)
+ )
+
+BEGIN
+
+ UPDATE TMC_IP
+ SET IP=V_NAME
+ WHERE IP_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_UPDATE_PROJECT;
+/
+CREATE PROCEDURE SP_TMC_UPDATE_PROJECT
+ ( IN V_ID INT,
+ IN V_NAME VARCHAR(500)
+ )
+
+BEGIN
+
+ UPDATE TMC_PROJECT
+ SET PROJECT_NAME=V_NAME
+ WHERE PROJECT_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_UPDATE_STATE;
+/
+CREATE PROCEDURE SP_TMC_UPDATE_STATE
+ ( IN V_ID INT,
+ IN V_NAME VARCHAR(100),
+ IN V_COLOR VARCHAR(30)
+ )
+
+BEGIN
+
+ UPDATE TMC_STATE
+ SET STATE_NAME=V_NAME, STATE_COLOR=V_COLOR
+ WHERE STATE_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_UPDATE_USER;
+/
+CREATE PROCEDURE SP_TMC_UPDATE_USER
+ ( IN V_ID INT,
+ IN V_NAME VARCHAR(30),
+ IN V_FN VARCHAR(30),
+ IN V_LN VARCHAR(30),
+ IN V_EMAIL VARCHAR(60),
+ IN V_COMMENTS VARCHAR(2000),
+ IN V_JOBTITLE VARCHAR(100),
+ IN V_STATE INT
+ )
+
+BEGIN
+
+
+ UPDATE TMC_USER
+ SET USER_NAME=V_NAME, FIRST_NAME=V_FN, LAST_NAME=V_LN, EMAIL=V_EMAIL, COMMENTS=V_COMMENTS, JOB_TITLE=V_JOBTITLE, STATE_ID=V_STATE
+ WHERE USER_ID=V_ID;
+ END;
+
+/
+DROP PROCEDURE IF EXISTS SP_TM_DELETE_ENTITY;
+/
+CREATE PROCEDURE SP_TM_DELETE_ENTITY
+ ( IN V_ID INT,
+ OUT V_RETURN INT
+ )
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+
+ DELETE FROM TM_ENTITY
+ WHERE ENTITY_ID=V_ID;
+
+ DELETE FROM TMC_REL_VHOST_CONTEXT
+ WHERE CONTEXT_ID=V_ID;
+END;
+
+/
+DROP PROCEDURE IF EXISTS SP_TM_DELETE_POOL;
+/
+CREATE PROCEDURE SP_TM_DELETE_POOL
+ ( IN V_ID INT,
+ OUT V_RETURN INT
+ )
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+
+ DELETE FROM TM_POOL
+ WHERE POOL_ID=V_ID;
+
+ DELETE FROM TM_REL_POOL_WORKER
+ WHERE POOL_ID=V_ID;
+ END;
+
+/
+DROP PROCEDURE IF EXISTS SP_TM_DELETE_WORKER_POOL;
+/
+CREATE PROCEDURE SP_TM_DELETE_WORKER_POOL
+ ( IN V_POOLID INT,
+ IN V_WORKERID INT
+ )
+
+BEGIN
+
+
+ DELETE FROM TM_REL_POOL_WORKER
+ WHERE POOL_ID=V_POOLID
+ AND WORKER_ID=V_WORKERID;
+ END;
+
+/
+DROP PROCEDURE IF EXISTS SP_TM_DROP_CTXT_REL_PROJECT;
+/
+CREATE PROCEDURE SP_TM_DROP_CTXT_REL_PROJECT
+ ( IN V_PID INT,
+ IN V_CID INT
+ )
+
+BEGIN
+
+
+ DELETE FROM TM_REL_ENTITY_PROJECT
+ WHERE ENTITY_ID = V_CID
+ AND PROJECT_ID = V_PID;
+ END;
+
+/
+DROP PROCEDURE IF EXISTS SP_TM_DROP_ENTITY;
+/
+CREATE PROCEDURE SP_TM_DROP_ENTITY
+ ( OUT V_RETURN INT,
+ IN V_ID INT
+ )
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+
+ DELETE FROM TM_ENTITY
+ WHERE ENTITY_ID=V_ID;
+
+ DELETE FROM TMC_REL_VHOST_CONTEXT
+ WHERE CONTEXT_ID=V_ID;
+ END;
+
+/
+DROP PROCEDURE IF EXISTS SP_TM_DROP_POOL;
+/
+CREATE PROCEDURE SP_TM_DROP_POOL
+ ( OUT V_RETURN INT,
+ IN V_ID INT
+ )
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+
+ DELETE FROM TM_POOL
+ WHERE POOL_ID=V_ID;
+
+ DELETE FROM TM_REL_POOL_WORKER
+ WHERE POOL_ID=V_ID;
+ END;
+
+/
+DROP PROCEDURE IF EXISTS SP_TM_DROP_SERVER;
+/
+CREATE PROCEDURE SP_TM_DROP_SERVER (OUT V_RETURN INT,
+ IN V_TID INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TM_CONTAINER
+ WHERE CONTAINER_ID=V_TID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TM_DROP_WORKER;
+/
+CREATE PROCEDURE SP_TM_DROP_WORKER (OUT V_RETURN INT,
+ IN V_WID INT)
+
+
+BEGIN
+ DECLARE CONTINUE HANDLER FOR NOT FOUND SET V_RETURN = 1;
+
+ SET V_RETURN = 0;
+ DELETE FROM TM_WORKER
+ WHERE WORKER_ID=V_WID;
+
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TM_PREP_CONTAINER_ENTRY;
+/
+CREATE PROCEDURE SP_TM_PREP_CONTAINER_ENTRY (
+ IN V_ID INT,
+ IN V_RESULT BLOB)
+
+BEGIN
+
+ UPDATE TM_CONTAINER
+ SET CONF_ENTRY=V_RESULT
+ WHERE CONTAINER_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TM_STORE_CTXT_REL_PROJECT;
+/
+CREATE PROCEDURE SP_TM_STORE_CTXT_REL_PROJECT
+ ( IN V_PID INT,
+ IN V_CID INT
+ )
+
+BEGIN
+
+
+ INSERT INTO TM_REL_ENTITY_PROJECT (ENTITY_ID, PROJECT_ID)
+ VALUES ( V_CID, V_PID);
+ END;
+
+/
+DROP PROCEDURE IF EXISTS SP_TM_STORE_ENTITY;
+/
+CREATE PROCEDURE SP_TM_STORE_ENTITY
+ ( OUT V_SEQ INT,
+ IN V_ENITIYNAME VARCHAR(60),
+ IN V_DOCBASE VARCHAR(500),
+ IN V_ENVID INT,
+ IN V_STATEID INT,
+ IN V_POOLID INT,
+ IN V_XML VARCHAR(4000),
+ IN V_EXPID INT
+ )
+BEGIN
+ INSERT INTO
+ TM_ENTITY ( ENTITY_NAME, DOC_BASE, ENTITY_STATE_ID, ENV_ID, TOMCAT_POOL_ID, ENTITY_DEFIN, EX_PROJECT_ID)
+ VALUES ( V_ENITIYNAME, V_DOCBASE, V_STATEID,V_ENVID, V_POOLID, V_XML, V_EXPID);
+ -- get the newly added jobid
+ SELECT LAST_INSERT_ID() INTO V_SEQ;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TM_STORE_WORKER_POOL;
+/
+CREATE PROCEDURE SP_TM_STORE_WORKER_POOL
+ ( IN V_POOLID INT,
+ IN V_WORKERID INT
+ )
+
+BEGIN
+ INSERT INTO TM_REL_POOL_WORKER (POOL_ID, WORKER_ID)
+ VALUES (V_POOLID, V_WORKERID);
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TM_UPDATE_ENTITY;
+/
+CREATE PROCEDURE SP_TM_UPDATE_ENTITY
+ ( IN V_ID INT,
+ IN V_NAME VARCHAR(60),
+ IN V_DOCBASE VARCHAR(500),
+ IN V_ENVID INT,
+ IN V_STATEID INT,
+ IN V_TPID INT,
+ IN V_XML VARCHAR(4000),
+ IN V_EXPID INT
+ )
+BEGIN
+ UPDATE TM_ENTITY
+ SET ENTITY_NAME=V_NAME, ENV_ID=V_ENVID, DOC_BASE=V_DOCBASE, EX_PROJECT_ID=V_EXPID,
+ ENTITY_STATE_ID=V_STATEID, TOMCAT_POOL_ID=V_TPID, ENTITY_DEFIN=V_XML
+ WHERE ENTITY_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TM_UPDATE_POOL;
+/
+CREATE PROCEDURE SP_TM_UPDATE_POOL
+ ( IN V_ID INT,
+ IN V_NAME VARCHAR(40),
+ IN V_EID INT
+ )
+BEGIN
+ UPDATE TM_POOL
+ SET POOL_NAME=V_NAME, ENV_ID=V_EID
+ WHERE POOL_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TM_UPDATE_TOMCAT4;
+/
+CREATE PROCEDURE SP_TM_UPDATE_TOMCAT4
+ ( IN V_ID INT,
+ IN V_NAME VARCHAR(40),
+ IN V_BASELOCATION VARCHAR(100),
+ IN V_TOMCATHOME VARCHAR(100),
+ IN V_HTTPPORT INT,
+ IN V_SYSUSER VARCHAR(8),
+ IN V_SYSGROUP VARCHAR(8),
+ IN V_DESCRIP VARCHAR(100),
+ IN V_TT INT,
+ IN V_JAVAHOME VARCHAR(40),
+ IN V_STARTCLASS VARCHAR(40),
+ IN V_OUTLOG VARCHAR(40),
+ IN V_ENTRY BLOB
+ )
+
+BEGIN
+
+ UPDATE TM_CONTAINER
+ SET CONTAINER_NAME=V_NAME,
+ BASE_LOCATION=V_BASELOCATION,
+ TOMCAT_HOME=V_TOMCATHOME,
+ HTTP_PORT=V_HTTPPORT,
+ SYS_USER=V_SYSUSER,
+ SYS_GROUP=V_SYSGROUP,
+ DESCRIPTION=V_DESCRIP,
+ TOMCAT_TYPE=V_TT,
+ JAVA_HOME=V_JAVAHOME,
+ START_CLASS=V_STARTCLASS,
+ STD_OUT_LOG=V_STDOUTLOG,
+ CONF_ENTRY=V_ENTRY
+
+ WHERE CONTAINER_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TM_UPDATE_WORKER;
+/
+CREATE PROCEDURE SP_TM_UPDATE_WORKER
+ ( IN V_ID INT,
+ IN V_HID INT,
+ IN V_SID INT,
+ IN V_TID INT
+ )
+
+BEGIN
+
+
+ UPDATE TM_WORKER
+ SET HARDWARE_ID=V_HID,
+ STATE_ID=V_SID,
+ CONTAINER_ID=V_TID
+ WHERE WORKER_ID=V_ID;
+ END;
+
+/
+DROP PROCEDURE IF EXISTS SP_TM_STORE_TOMCAT4;
+/
+CREATE PROCEDURE SP_TM_STORE_TOMCAT4
+ ( OUT V_ID INT,
+ IN V_NAME VARCHAR(40),
+ IN V_BASELOCATION VARCHAR(100),
+ IN V_TOMCATHOME VARCHAR(100),
+ IN V_HTTPPORT INT,
+ IN V_SYSUSER VARCHAR(8),
+ IN V_SYSGROUP VARCHAR(8),
+ IN V_DESCRIP VARCHAR(100),
+ IN V_TT INT,
+ IN V_JAVAHOME VARCHAR(40),
+ IN V_STARTCLASS VARCHAR(40),
+ IN V_OUTLOG VARCHAR(40),
+ IN V_ENTRY BLOB
+ )
+BEGIN
+ INSERT INTO TM_CONTAINER (CONTAINER_NAME, BASE_LOCATION, TOMCAT_HOME, HTTP_PORT, SYS_USER, SYS_GROUP, TOMCAT_TYPE, DESCRIPTION,STD_OUT_LOG, START_CLASS, JAVA_HOME, CONF_ENTRY)
+ VALUES ( V_NAME, V_BASELOCATION, V_TOMCATHOME, V_HTTPPORT, V_SYSUSER, V_SYSGROUP, V_TT, V_DESCRIP, V_OUTLOG, V_STARTCLASS, V_JAVAHOME, V_ENTRY);
+ SELECT LAST_INSERT_ID() INTO V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TM_STORE_WORKER;
+/
+CREATE PROCEDURE SP_TM_STORE_WORKER
+ ( OUT V_ID INT,
+ IN V_HID INT,
+ IN V_SID INT,
+ IN V_TID INT
+ )
+
+
+
+
+ BEGIN
+DECLARE W_ID INT;
+DECLARE CV CURSOR FOR SELECT WORKER_ID FROM TM_WORKER WHERE
+ HARDWARE_ID = V_HID AND
+ CONTAINER_ID = V_TID;
+DECLARE CONTINUE HANDLER FOR NOT FOUND
+BEGIN
+ INSERT INTO TM_WORKER (HARDWARE_ID, STATE_ID, CONTAINER_ID)
+ VALUES ( V_HID, V_SID, V_TID);
+ SELECT LAST_INSERT_ID() INTO V_ID;
+END;
+
+ OPEN CV ;
+ FETCH CV INTO W_ID;
+ SET V_ID = W_ID;
+ CLOSE CV;
+ END;
+ /
+DROP PROCEDURE IF EXISTS SP_TMC_UPDATE_JOB;
+/
+CREATE PROCEDURE SP_TMC_UPDATE_JOB (
+ OUT RETURNVAL INT,
+ IN V_PK INT,
+ IN V_STATEID INT,
+ IN V_RESULT BLOB)
+
+BEGIN
+DECLARE CURRENTSTATE INT;
+ SELECT TMC_JOB.STATE_ID INTO CURRENTSTATE FROM TMC_JOB WHERE TMC_JOB.JOB_ID=V_PK;
+ IF V_STATEID >= CURRENTSTATE THEN
+ UPDATE TMC_JOB
+ SET STATE_ID=V_STATEID, FINISH_TIME=NOW(), RESULT=V_RESULT
+ WHERE JOB_ID=V_PK;
+ SET RETURNVAL = 1;
+ ELSE
+ SET RETURNVAL = 0;
+ END IF;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_UPDATE_HELP_ITEM;
+/
+CREATE PROCEDURE SP_TMC_UPDATE_HELP_ITEM
+ ( IN H_ID INT,
+ IN H_TITLE VARCHAR(50),
+ IN H_VALID INT,
+ IN H_FUNCTION INT,
+ IN H_SUMMARY VARCHAR(250),
+ IN H_CONTENT BLOB
+ )
+
+ BEGIN
+
+ UPDATE TMC_HELP_ITEM
+ SET HELP_ITEM_TITLE=H_TITLE,
+ VALID=H_VALID,
+ FUNCTION_ID=H_FUNCTION,
+ SUMMARY=H_SUMMARY,
+ CONTENT=H_CONTENT
+ WHERE HELP_ITEM_ID=H_ID;
+ END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_UPDATE_ENVIROMENT;
+/
+CREATE PROCEDURE SP_TMC_UPDATE_ENVIROMENT
+ ( IN V_ID INT,
+ IN V_NAME VARCHAR(400),
+ IN V_COMMENTS VARCHAR(400)
+ )
+
+BEGIN
+ UPDATE TMC_ENV
+ SET ENV_NAME=V_NAME, COMMENTS=V_COMMENTS
+ WHERE ENV_ID=V_ID;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_UPDATE_ALERT;
+/
+CREATE PROCEDURE SP_TMC_UPDATE_ALERT
+ ( IN V_ID INT,
+ IN V_TITLE VARCHAR(200),
+ IN V_LINK VARCHAR(4000),
+ IN V_SUBID INT,
+ IN V_CONTENT BLOB
+ )
+
+ BEGIN
+
+ UPDATE TMC_ALERTS
+ SET ALERT_TITLE=V_TITLE,
+ ALERT_LINK=V_LINK,
+ SUBMITTER_ID=V_SUBID,
+ ALERT_CONTENTS=V_CONTENT,
+ SUBMISION_DATE=NOW()
+ WHERE ALERT_ID=V_ID;
+ END;
+
+/
+DROP PROCEDURE IF EXISTS SP_TMC_TEST_UPDATE_JOB;
+/
+CREATE PROCEDURE SP_TMC_TEST_UPDATE_JOB (
+ OUT RETURNVAL INT,
+ IN V_PK INT,
+ IN V_STATEID INT,
+ IN V_RESULT BLOB)
+
+BEGIN
+DECLARE CURRENTSTATE INT;
+ SET RETURNVAL = -1;
+ SELECT TMC_JOB.STATE_ID INTO CURRENTSTATE FROM TMC_JOB WHERE TMC_JOB.JOB_ID=V_PK;
+ IF V_STATEID >= CURRENTSTATE THEN
+ BEGIN
+ UPDATE TMC_JOB
+ SET STATE_ID=V_STATEID, FINISH_TIME=NOW(), RESULT=V_RESULT
+ WHERE JOB_ID=V_PK;
+ SET RETURNVAL = 1;
+ END;
+ ELSE
+ SET RETURNVAL = 0;
+ END IF;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_TOPIC;
+/
+CREATE PROCEDURE SP_TMC_STORE_TOPIC
+ ( OUT T_ID INT,
+ IN T_NAME VARCHAR(50)
+ )
+
+BEGIN
+ DECLARE W_ID INT;
+ DECLARE CV CURSOR FOR SELECT TOPIC_ID FROM TMC_TOPIC WHERE
+ TOPIC_NAME LIKE T_NAME;
+ DECLARE CONTINUE HANDLER FOR NOT FOUND
+ BEGIN
+ INSERT INTO TMC_TOPIC(TOPIC_NAME)
+ VALUES ( T_NAME);
+ SET T_ID = LAST_INSERT_ID();
+ END;
+ OPEN CV ;
+ FETCH CV INTO W_ID;
+ SET T_ID = W_ID;
+ CLOSE CV;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_KEYWORD;
+/
+CREATE PROCEDURE SP_TMC_STORE_KEYWORD
+ ( OUT K_KID INT,
+ IN K_WRD VARCHAR(50)
+ )
+BEGIN
+ DECLARE W_ID INT;
+ DECLARE CV CURSOR FOR SELECT KEYWORD_ID FROM TMC_KEYWORD WHERE KEYWORD_NAME LIKE K_WRD;
+ DECLARE CONTINUE HANDLER FOR NOT FOUND
+ BEGIN
+ INSERT INTO TMC_KEYWORD(KEYWORD_NAME)
+ VALUES ( K_WRD);
+ SET K_KID = LAST_INSERT_ID();
+ END;
+
+ OPEN CV ;
+ FETCH CV INTO W_ID;
+ SET K_KID = W_ID;
+ CLOSE CV;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_STORE_ALERT_REL;
+/
+CREATE PROCEDURE SP_TMC_STORE_ALERT_REL
+ (
+ IN V_ID INT,
+ IN V_PROJID INT
+ )
+
+BEGIN
+ INSERT INTO TMC_REL_ALERT_PROJECT (ALERT_ID, PROJECT_ID)
+ VALUES (V_ID, V_PROJID);
+END;
+/
+DROP PROCEDURE IF EXISTS SP_TMC_JOB_UPDATE;
+/
+CREATE PROCEDURE SP_TMC_JOB_UPDATE (
+ OUT RETURNVAL INT,
+ IN V_PK INT,
+ IN V_STATEID INT
+ )
+
+BEGIN
+ DECLARE CURRENTSTATE INT;
+ SELECT TMC_JOB.STATE_ID INTO CURRENTSTATE FROM TMC_JOB WHERE TMC_JOB.JOB_ID=V_PK;
+ IF V_STATEID >= CURRENTSTATE THEN
+ BEGIN
+ UPDATE TMC_JOB
+ SET STATE_ID=V_STATEID, FINISH_TIME=SYSDATE
+ WHERE JOB_ID=V_PK;
+ SET RETURNVAL = 1;
+ END;
+ ELSE
+ SET RETURNVAL = 0;
+ END IF;
+END;
+/
+DROP PROCEDURE IF EXISTS SP_AM_STORE_WORKER;
+/
+CREATE PROCEDURE SP_AM_STORE_WORKER
+ ( OUT V_ID INT,
+ IN V_AID INT,
+ IN V_HID INT,
+ IN V_SID INT
+ )
+
+
+
+BEGIN
+DECLARE W_ID INT;
+DECLARE CV CURSOR FOR SELECT WORKER_ID FROM AM_WORKER WHERE
+ HARDWARE_ID = V_HID AND
+ CONTAINER_ID =V_AID;
+DECLARE CONTINUE HANDLER FOR NOT FOUND
+BEGIN
+ --dbms_output.put_line('No Rows with the value of that type.. now inserting');
+ INSERT INTO AM_WORKER (CONTAINER_ID, HARDWARE_ID, STATE_ID)
+ VALUES ( V_AID,V_HID,V_SID);
+ SELECT LAST_INSERT_ID() INTO V_ID;
+ --dbms_output.put_line('Inserted Values' || v_id ||'-' || v_aid || '-' || v_hid ||'-' || v_sid);
+END;
+ OPEN CV ;
+ FETCH CV INTO W_ID;
+ SET V_ID = W_ID;
+ -- dbms_output.put_line('Found worker Id ' || w_id );
+ CLOSE CV;
+END;
+/
Added: incubator/lokahi/lokahi/branches/mysql/database/mysql/tables.sql
URL: http://svn.apache.org/viewvc/incubator/lokahi/lokahi/branches/mysql/database/mysql/tables.sql?view=auto&rev=451328
==============================================================================
--- incubator/lokahi/lokahi/branches/mysql/database/mysql/tables.sql (added)
+++ incubator/lokahi/lokahi/branches/mysql/database/mysql/tables.sql Fri Sep 29 08:45:56 2006
@@ -0,0 +1,378 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+--
+
+DROP TABLE IF EXISTS AM_CONTAINER;
+CREATE TABLE AM_CONTAINER (CONTAINER_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (CONTAINER_ID),CONTAINER_NAME VARCHAR(100) NOT NULL,SERVER_ROOT VARCHAR(300) ,LOG_LOCATION VARCHAR(300) ,SYS_USER VARCHAR(8) ,SYS_GROUP VARCHAR(8) ,START_COMMAND VARCHAR(4000) ,STOP_COMMAND VARCHAR(4000) ,RESTART_COMMAND VARCHAR(4000) ,FORK_WAIT INT(11) ,PIDFILE_LOCATION VARCHAR(300) ,ENV_VARIBLES VARCHAR(4000) ,MAIN_ID INT(11) , KEY (MAIN_ID));
+
+
+DROP TABLE IF EXISTS AM_POOL;
+CREATE TABLE AM_POOL (POOL_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (POOL_ID),POOL_NAME VARCHAR(40) NOT NULL,ENV_ID INT(11) NOT NULL, KEY (ENV_ID));
+
+
+DROP TABLE IF EXISTS AM_REL_CONTAINER_FILE;
+CREATE TABLE AM_REL_CONTAINER_FILE (CONTAINER_ID INT(11) NOT NULL, KEY (CONTAINER_ID),FILE_ID INT(11) NOT NULL, KEY (FILE_ID));
+
+
+DROP TABLE IF EXISTS AM_REL_ENTITY_PROJECT;
+CREATE TABLE AM_REL_ENTITY_PROJECT (PROJECT_ID INT(11) NOT NULL, KEY (PROJECT_ID),VHOST_ID INT(11) NOT NULL, KEY (VHOST_ID));
+
+
+DROP TABLE IF EXISTS AM_REL_POOL_WORKER;
+CREATE TABLE AM_REL_POOL_WORKER (POOL_ID INT(11) NOT NULL, KEY (POOL_ID),WORKER_ID INT(11) NOT NULL, KEY (WORKER_ID));
+
+
+DROP TABLE IF EXISTS AM_REL_VHOST_IP;
+CREATE TABLE AM_REL_VHOST_IP (VHOST_ID INT(11) NOT NULL, KEY (VHOST_ID),IP_ID INT(11) NOT NULL, KEY (IP_ID));
+
+
+DROP TABLE IF EXISTS AM_VHOST;
+CREATE TABLE AM_VHOST (VHOST_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (VHOST_ID),VHOST_ENTRY BLOB ,VHOST_NAME VARCHAR(60) NOT NULL,ENV_ID INT(11) , KEY (ENV_ID),STATE_ID INT(11) , KEY (STATE_ID),HOSTING_POOL_ID INT(11) , KEY (HOSTING_POOL_ID),NAMEBASED INT(11) DEFAULT 1,CATCHALL INT(11) DEFAULT 0,EX_PROJECT_ID INT(11) DEFAULT 0, KEY (EX_PROJECT_ID));
+
+
+DROP TABLE IF EXISTS AM_WORKER;
+CREATE TABLE AM_WORKER (WORKER_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (WORKER_ID),STATE_ID INT(11) , KEY (STATE_ID),HARDWARE_ID INT(11) NOT NULL, KEY (HARDWARE_ID),CONTAINER_ID INT(11) NOT NULL, KEY (CONTAINER_ID));
+
+
+DROP TABLE IF EXISTS TMC_ADMIN;
+CREATE TABLE TMC_ADMIN (LOCK_USERS INT(11) ,PRIMARY_JVM VARCHAR(60) ,BACKUP_JVM VARCHAR(60) ,ALTERNATE_JVM VARCHAR(2000) ,OLD_KEY VARCHAR(30) ,CURRENT_KEY VARCHAR(30) ,KEY_LAST_ROTATED DATETIME ,INSTANCE_ID INT(11) , PRIMARY KEY (INSTANCE_ID));
+
+
+DROP TABLE IF EXISTS TMC_ALERTS;
+CREATE TABLE TMC_ALERTS (ALERT_ID INT(11) NOT NULL, KEY (ALERT_ID),ALERT_CONTENTS BLOB ,SUBMISION_DATE DATETIME ,SUBMITTER_ID INT(11) NOT NULL, KEY (SUBMITTER_ID),ALERT_TITLE VARCHAR(200) ,ALERT_LINK VARCHAR(4000) );
+
+
+DROP TABLE IF EXISTS TMC_CONTROLLER;
+CREATE TABLE TMC_CONTROLLER (INSTANCE_ID INT(11) NOT NULL, KEY (INSTANCE_ID),CONTROLLER_NAME VARCHAR(400) NOT NULL,CONTROLLER_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (CONTROLLER_ID),LAST_CHECK_IN DATETIME NOT NULL);
+
+
+DROP TABLE IF EXISTS TMC_ENV;
+CREATE TABLE TMC_ENV (ENV_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (ENV_ID),ENV_NAME VARCHAR(400) NOT NULL,COMMENTS VARCHAR(400) );
+
+
+DROP TABLE IF EXISTS TMC_EX_PROJECT;
+CREATE TABLE TMC_EX_PROJECT (EX_PROJECT_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (EX_PROJECT_ID),EX_PROJECT_NAME VARCHAR(40) NOT NULL);
+
+
+DROP TABLE IF EXISTS TMC_FILES;
+CREATE TABLE TMC_FILES (FILE_ID INT(11) NOT NULL, KEY (FILE_ID),FILE_NAME VARCHAR(100) NOT NULL,FILE_CONTENT BLOB ,FILE_DESCRIPTOR VARCHAR(100) ,FILE_PATH VARCHAR(2000) );
+
+
+DROP TABLE IF EXISTS TMC_FILE_ARCHIVE;
+CREATE TABLE TMC_FILE_ARCHIVE (JOB_ID INT(11) NOT NULL, KEY (JOB_ID),FILE_CONTENTS BLOB );
+
+
+DROP TABLE IF EXISTS TMC_FUNCTION;
+CREATE TABLE TMC_FUNCTION (FUNCTION_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (FUNCTION_ID),FUNCTION_NAME VARCHAR(50) NOT NULL,FUNCTION_COMMAND VARCHAR(50) ,MODULE_ID INT(11) , KEY (MODULE_ID));
+
+
+DROP TABLE IF EXISTS TMC_HARDWARE;
+CREATE TABLE TMC_HARDWARE (HARDWARE_NAME VARCHAR(60) NOT NULL,PHYSICAL_LOCATION VARCHAR(500) ,COMMENTS VARCHAR(4000) ,HARDWARE_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (HARDWARE_ID),INSTANCE_ID INT(11) , KEY (INSTANCE_ID),ENV_ID INT(11) , KEY (ENV_ID),STATUS_ID INT(11) , KEY (STATUS_ID),DEFAULT_IP_ID INT(11) , KEY (DEFAULT_IP_ID));
+
+
+DROP TABLE IF EXISTS TMC_HARDWARE_POOL;
+CREATE TABLE TMC_HARDWARE_POOL (HARDWARE_POOL_NAME VARCHAR(60) NOT NULL,HARDWARE_POOL_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (HARDWARE_POOL_ID));
+
+
+DROP TABLE IF EXISTS TMC_HELP_ITEM;
+CREATE TABLE TMC_HELP_ITEM (HELP_ITEM_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (HELP_ITEM_ID),HELP_ITEM_TITLE VARCHAR(50) NOT NULL,VALID INT(11) ,FUNCTION_ID INT(11) DEFAULT 0 NOT NULL, KEY (FUNCTION_ID),CONTENT BLOB NOT NULL,SUMMARY VARCHAR(250) NOT NULL);
+
+
+DROP TABLE IF EXISTS TMC_HOSTING_POOL;
+CREATE TABLE TMC_HOSTING_POOL (HOSTING_POOL_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (HOSTING_POOL_ID),HOSTING_POOL_NAME VARCHAR(60) NOT NULL,ENV_ID INT(11) NOT NULL, KEY (ENV_ID));
+
+
+DROP TABLE IF EXISTS TMC_INSTANCE;
+CREATE TABLE TMC_INSTANCE (INSTANCE_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (INSTANCE_ID),INSTANCE_NAME VARCHAR(100) NOT NULL,COMMENTS VARCHAR(4000) );
+
+
+DROP TABLE IF EXISTS TMC_IP;
+CREATE TABLE TMC_IP (IP VARCHAR(15) NOT NULL,IP_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (IP_ID));
+
+
+DROP TABLE IF EXISTS TMC_JOB;
+CREATE TABLE TMC_JOB (JOB_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (JOB_ID),POOL_ID INT(11) NOT NULL, KEY (POOL_ID),JOB_NAME VARCHAR(400) ,SERVER_ID INT(11) NOT NULL, KEY (SERVER_ID),PARENT_ID INT(11) DEFAULT -1, KEY (PARENT_ID),START_TIME DATETIME ,FINISH_TIME DATETIME ,STATE_ID INT(11) DEFAULT 0, KEY (STATE_ID),OPTIONS VARCHAR(4000) ,INSTANCE_ID INT(11) NOT NULL, KEY (INSTANCE_ID),FUNCTION_ID INT(11) NOT NULL, KEY (FUNCTION_ID),RESULT BLOB );
+
+
+DROP TABLE IF EXISTS TMC_JOB_POOL;
+CREATE TABLE TMC_JOB_POOL (JOB_POOL_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (JOB_POOL_ID),JOB_POOL_NAME VARCHAR(400) ,PROJECT_ID INT(11) DEFAULT 0, KEY (PROJECT_ID),USER_ID INT(11) DEFAULT 0, KEY (USER_ID),START_TIME DATETIME ,FINISH_TIME DATETIME ,STATE_ID INT(11) DEFAULT 0, KEY (STATE_ID),FUNCTION_ID INT(11) NOT NULL, KEY (FUNCTION_ID),OPTIONS VARCHAR(4000) );
+
+
+DROP TABLE IF EXISTS TMC_KEYWORD;
+CREATE TABLE TMC_KEYWORD (KEYWORD_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (KEYWORD_ID),KEYWORD_NAME VARCHAR(50) NOT NULL);
+
+
+DROP TABLE IF EXISTS TMC_PROJECT;
+CREATE TABLE TMC_PROJECT (PROJECT_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (PROJECT_ID),PROJECT_NAME VARCHAR(500) NOT NULL);
+
+
+DROP TABLE IF EXISTS TMC_REL_ALERT_PROJECT;
+CREATE TABLE TMC_REL_ALERT_PROJECT (ALERT_ID INT(11) NOT NULL, KEY (ALERT_ID),PROJECT_ID INT(11) NOT NULL, KEY (PROJECT_ID));
+
+
+DROP TABLE IF EXISTS TMC_REL_FUNCTION_USER;
+CREATE TABLE TMC_REL_FUNCTION_USER (USER_ID INT(11) NOT NULL, KEY (USER_ID),FUNCTION_ID INT(11) NOT NULL, KEY (FUNCTION_ID),OBJECT_ID INT(11) NOT NULL, KEY (OBJECT_ID),OBJECT_TYPE INT(11) NOT NULL,PROJECT_ID INT(11) NOT NULL, KEY (PROJECT_ID));
+
+
+DROP TABLE IF EXISTS TMC_REL_HARDWARE_FILE;
+CREATE TABLE TMC_REL_HARDWARE_FILE (HARDWARE_ID INT(11) NOT NULL, KEY (HARDWARE_ID),FILE_ID INT(11) NOT NULL, KEY (FILE_ID));
+
+
+DROP TABLE IF EXISTS TMC_REL_HARDWARE_IP;
+CREATE TABLE TMC_REL_HARDWARE_IP (HARDWARE_ID INT(11) NOT NULL, KEY (HARDWARE_ID),IP_ID INT(11) NOT NULL, KEY (IP_ID));
+
+
+DROP TABLE IF EXISTS TMC_REL_HARDWARE_POOL;
+CREATE TABLE TMC_REL_HARDWARE_POOL (HARDWARE_ID INT(11) NOT NULL, KEY (HARDWARE_ID),POOL_ID INT(11) NOT NULL, KEY (POOL_ID));
+
+
+DROP TABLE IF EXISTS TMC_REL_HELP_KEYWORD;
+CREATE TABLE TMC_REL_HELP_KEYWORD (HELP_ITEM_ID INT(11) NOT NULL, KEY (HELP_ITEM_ID),KEYWORD_ID INT(11) NOT NULL, KEY (KEYWORD_ID));
+
+
+DROP TABLE IF EXISTS TMC_REL_HELP_TOPIC;
+CREATE TABLE TMC_REL_HELP_TOPIC (HELP_ITEM_ID INT(11) NOT NULL, KEY (HELP_ITEM_ID),TOPIC_ID INT(11) NOT NULL, KEY (TOPIC_ID),SUBTOPIC_ID INT(11) , KEY (SUBTOPIC_ID));
+
+
+DROP TABLE IF EXISTS TMC_REL_HOSTING_MODULES;
+CREATE TABLE TMC_REL_HOSTING_MODULES (HOSTING_POOL_ID INT(11) NOT NULL, KEY (HOSTING_POOL_ID),TM_POOL_ID INT(11) DEFAULT -1, KEY (TM_POOL_ID),AM_POOL_ID INT(11) DEFAULT -1, KEY (AM_POOL_ID));
+
+
+DROP TABLE IF EXISTS TMC_REL_USER_EXPROJ;
+CREATE TABLE TMC_REL_USER_EXPROJ (USER_ID INTEGER NOT NULL, KEY (USER_ID),EX_PROJECT_ID INTEGER NOT NULL, KEY (EX_PROJECT_ID));
+
+
+DROP TABLE IF EXISTS TMC_REL_USER_PROJECT;
+CREATE TABLE TMC_REL_USER_PROJECT (PROJECT_ID INT(11) NOT NULL, KEY (PROJECT_ID),USER_ID INT(11) NOT NULL, KEY (USER_ID));
+
+
+DROP TABLE IF EXISTS TMC_REL_VHOST_CONTEXT;
+CREATE TABLE TMC_REL_VHOST_CONTEXT (CONTEXT_ID INT(11) NOT NULL, KEY (CONTEXT_ID),VHOST_ID INT(11) NOT NULL, KEY (VHOST_ID));
+
+
+DROP TABLE IF EXISTS TMC_STATE;
+CREATE TABLE TMC_STATE (STATE_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (STATE_ID),STATE_NAME VARCHAR(100) NOT NULL,STATE_COLOR VARCHAR(30) );
+
+
+DROP TABLE IF EXISTS TMC_TEMPLATE_TABLE;
+CREATE TABLE TMC_TEMPLATE_TABLE (TMC_INSTANCE VARCHAR(30) NOT NULL,TEMPLATE_ID INT(11) NOT NULL, KEY (TEMPLATE_ID),TEMPLATE_NAME VARCHAR(100) NOT NULL,TEMPLATE_BLOCK BLOB ,STATUS VARCHAR(15) );
+
+
+DROP TABLE IF EXISTS TMC_TIMESTAMP_TABLE;
+CREATE TABLE TMC_TIMESTAMP_TABLE (TABLE_NAME VARCHAR(30) ,TIME_MODIFIED DATETIME ,TMC_INSTANCE VARCHAR(30) );
+
+
+DROP TABLE IF EXISTS TMC_TOPIC;
+CREATE TABLE TMC_TOPIC (TOPIC_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (TOPIC_ID),TOPIC_NAME VARCHAR(50) );
+
+
+DROP TABLE IF EXISTS TMC_USER;
+CREATE TABLE TMC_USER (FIRST_NAME VARCHAR(30) ,LAST_NAME VARCHAR(30) ,JOB_TITLE VARCHAR(100) ,COMMENTS VARCHAR(2000) ,EMAIL VARCHAR(60) ,USER_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (USER_ID),USER_NAME VARCHAR(30) NOT NULL,STATE_ID INT(11) DEFAULT 8, KEY (STATE_ID),PASSWORD VARCHAR(32) );
+
+
+DROP TABLE IF EXISTS TM_CONTAINER;
+CREATE TABLE TM_CONTAINER (CONTAINER_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (CONTAINER_ID),CONTAINER_NAME VARCHAR(40) ,BASE_LOCATION VARCHAR(100) ,TOMCAT_HOME VARCHAR(100) ,HTTP_PORT INT(11) ,SYS_USER VARCHAR(8) ,SYS_GROUP VARCHAR(8) ,CONF_ENTRY BLOB ,TOMCAT_TYPE INT(11) DEFAULT 4,DESCRIPTION VARCHAR(100) ,STD_OUT_LOG VARCHAR(40) ,START_CLASS VARCHAR(40) ,JAVA_HOME VARCHAR(40) );
+
+
+DROP TABLE IF EXISTS TM_ENTITY;
+CREATE TABLE TM_ENTITY (ENTITY_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (ENTITY_ID),ENTITY_NAME VARCHAR(60) NOT NULL,DOC_BASE VARCHAR(500) NOT NULL,ENTITY_STATE_ID INT(11) NOT NULL, KEY (ENTITY_STATE_ID),ENV_ID INT(11) NOT NULL, KEY (ENV_ID),TOMCAT_POOL_ID INT(11) , KEY (TOMCAT_POOL_ID),ENTITY_DEFIN VARCHAR(4000) ,EX_PROJECT_ID INT(11) DEFAULT 0, KEY (EX_PROJECT_ID));
+
+
+DROP TABLE IF EXISTS TM_POOL;
+CREATE TABLE TM_POOL (POOL_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (POOL_ID),POOL_NAME VARCHAR(40) NOT NULL,ENV_ID INT(11) NOT NULL, KEY (ENV_ID));
+
+
+DROP TABLE IF EXISTS TM_REL_CONTAINER_FILE;
+CREATE TABLE TM_REL_CONTAINER_FILE (CONTAINER_ID INT(11) NOT NULL, KEY (CONTAINER_ID),FILE_ID INT(11) NOT NULL, KEY (FILE_ID));
+
+
+DROP TABLE IF EXISTS TM_REL_ENTITY_PROJECT;
+CREATE TABLE TM_REL_ENTITY_PROJECT (PROJECT_ID INT(11) NOT NULL, KEY (PROJECT_ID),ENTITY_ID INT(11) NOT NULL, KEY (ENTITY_ID));
+
+
+DROP TABLE IF EXISTS TM_REL_POOL_WORKER;
+CREATE TABLE TM_REL_POOL_WORKER (POOL_ID INT(11) NOT NULL, KEY (POOL_ID),WORKER_ID INT(11) NOT NULL, KEY (WORKER_ID));
+
+
+DROP TABLE IF EXISTS TM_WORKER;
+CREATE TABLE TM_WORKER (WORKER_ID INT(11) NOT NULL auto_increment, PRIMARY KEY (WORKER_ID),CONTAINER_ID INT(11) NOT NULL, KEY (CONTAINER_ID),HARDWARE_ID INT(11) NOT NULL, KEY (HARDWARE_ID),STATE_ID INT(11) , KEY (STATE_ID));
+
+
+CREATE UNIQUE INDEX AM_VHOSTID_PK ON AM_VHOST (VHOST_ID) ;
+
+
+CREATE INDEX FK_FUNCTION_ID ON TMC_HELP_ITEM (FUNCTION_ID) ;
+
+
+CREATE INDEX FK_SUBTOPIC_ID ON TMC_REL_HELP_TOPIC (SUBTOPIC_ID) ;
+
+
+CREATE INDEX FK_TOPIC_ID ON TMC_REL_HELP_TOPIC (TOPIC_ID) ;
+
+
+CREATE UNIQUE INDEX PK_AM_CONTAINER ON AM_CONTAINER (CONTAINER_ID) ;
+
+
+CREATE UNIQUE INDEX PK_AM_POOL ON AM_POOL (POOL_ID) ;
+
+
+CREATE UNIQUE INDEX PK_AM_WORKER ON AM_WORKER (WORKER_ID) ;
+
+
+CREATE UNIQUE INDEX PK_FILE_ID ON TMC_FILES (FILE_ID) ;
+
+
+CREATE UNIQUE INDEX PK_PROJECT ON TMC_PROJECT (PROJECT_ID) ;
+
+
+CREATE UNIQUE INDEX PK_TMC_ALERTS ON TMC_ALERTS (ALERT_ID) ;
+
+
+CREATE UNIQUE INDEX PK_TMC_CONTROLLER ON TMC_CONTROLLER (CONTROLLER_ID) ;
+
+
+CREATE UNIQUE INDEX PK_TMC_ENV ON TMC_ENV (ENV_ID) ;
+
+
+CREATE UNIQUE INDEX PK_TMC_EX_PROJECT ON TMC_EX_PROJECT (EX_PROJECT_ID) ;
+
+
+CREATE UNIQUE INDEX PK_TMC_HELP_ITEM ON TMC_HELP_ITEM (HELP_ITEM_ID) ;
+
+
+CREATE UNIQUE INDEX PK_TMC_HOSTING_POOL ON TMC_HOSTING_POOL (HOSTING_POOL_ID) ;
+
+
+CREATE UNIQUE INDEX PK_TMC_INSTANCE ON TMC_INSTANCE (INSTANCE_ID) ;
+
+
+CREATE UNIQUE INDEX PK_TMC_IP ON TMC_IP (IP_ID) ;
+
+
+CREATE UNIQUE INDEX PK_TMC_JOB ON TMC_JOB (JOB_ID) ;
+
+
+CREATE UNIQUE INDEX PK_TMC_JOB_POOL ON TMC_JOB_POOL (JOB_POOL_ID) ;
+
+
+CREATE UNIQUE INDEX PK_TMC_KEYWORD ON TMC_KEYWORD (KEYWORD_ID) ;
+
+
+CREATE UNIQUE INDEX PK_TMC_REL_HELP_KEYWORD ON TMC_REL_HELP_KEYWORD (HELP_ITEM_ID, KEYWORD_ID) ;
+
+
+CREATE UNIQUE INDEX PK_TMC_STATE ON TMC_STATE (STATE_ID) ;
+
+
+CREATE UNIQUE INDEX PK_TMC_TOPIC ON TMC_TOPIC (TOPIC_ID) ;
+
+
+CREATE UNIQUE INDEX PK_TM_CONTAINER ON TM_CONTAINER (CONTAINER_ID) ;
+
+
+CREATE UNIQUE INDEX PK_TM_POOL ON TM_POOL (POOL_ID) ;
+
+
+CREATE UNIQUE INDEX PK_TM_WORKER ON TM_WORKER (WORKER_ID) ;
+
+
+CREATE UNIQUE INDEX PK_USERID ON TMC_USER (USER_ID) ;
+
+
+CREATE UNIQUE INDEX TMC_SERVERID_PK ON TMC_HARDWARE (HARDWARE_ID) ;
+
+
+CREATE UNIQUE INDEX TMC_TEMPLATE_TABLE_ID ON TMC_TEMPLATE_TABLE (TEMPLATE_ID) ;
+
+
+CREATE UNIQUE INDEX TM_CONTEXT_TABLE_CTXTID ON TM_ENTITY (ENTITY_ID) ;
+
+
+CREATE UNIQUE INDEX UNQ_TIMESTAMP_TABLE ON TMC_TIMESTAMP_TABLE (TMC_INSTANCE, TABLE_NAME) ;
+
+
+#ALTER TABLE AM_CONTAINER ADD ( CONSTRAINT PK_AM_CONTAINER PRIMARY KEY (CONTAINER_ID));
+
+#ALTER TABLE AM_POOL ADD ( CONSTRAINT PK_AM_POOL PRIMARY KEY (POOL_ID));
+
+#ALTER TABLE AM_VHOST ADD ( CONSTRAINT AM_VHOSTID_PK PRIMARY KEY (VHOST_ID));
+
+#ALTER TABLE AM_WORKER ADD ( CONSTRAINT PK_AM_WORKER PRIMARY KEY (WORKER_ID));
+
+#ALTER TABLE TMC_ALERTS ADD ( CONSTRAINT PK_TMC_ALERTS PRIMARY KEY (ALERT_ID));
+
+#ALTER TABLE TMC_CONTROLLER ADD ( CONSTRAINT PK_TMC_CONTROLLER PRIMARY KEY (CONTROLLER_ID));
+
+#ALTER TABLE TMC_ENV ADD ( CONSTRAINT PK_TMC_ENV PRIMARY KEY (ENV_ID));
+
+#ALTER TABLE TMC_EX_PROJECT ADD ( CONSTRAINT PK_TMC_EX_PROJECT PRIMARY KEY (EX_PROJECT_ID), UNIQUE (EX_PROJECT_NAME));
+
+#ALTER TABLE TMC_FILES ADD ( CONSTRAINT PK_FILE_ID PRIMARY KEY (FILE_ID));
+
+#ALTER TABLE TMC_FUNCTION ADD ( PRIMARY KEY (FUNCTION_ID));
+
+#ALTER TABLE TMC_HARDWARE ADD ( CONSTRAINT TMC_SERVERID_PK PRIMARY KEY (HARDWARE_ID));
+
+#ALTER TABLE TMC_HARDWARE_POOL ADD ( PRIMARY KEY (HARDWARE_POOL_ID));
+
+#ALTER TABLE TMC_HELP_ITEM ADD ( CONSTRAINT PK_TMC_HELP_ITEM PRIMARY KEY (HELP_ITEM_ID), UNIQUE (HELP_ITEM_TITLE));
+
+#ALTER TABLE TMC_HOSTING_POOL ADD ( CONSTRAINT PK_TMC_HOSTING_POOL PRIMARY KEY (HOSTING_POOL_ID));
+
+#ALTER TABLE TMC_INSTANCE ADD ( CONSTRAINT PK_TMC_INSTANCE PRIMARY KEY (INSTANCE_ID));
+
+#ALTER TABLE TMC_IP ADD ( CONSTRAINT PK_TMC_IP PRIMARY KEY (IP_ID));
+
+#ALTER TABLE TMC_JOB ADD ( CONSTRAINT PK_TMC_JOB PRIMARY KEY (JOB_ID));
+
+#ALTER TABLE TMC_JOB_POOL ADD ( CONSTRAINT PK_TMC_JOB_POOL PRIMARY KEY (JOB_POOL_ID));
+
+#ALTER TABLE TMC_KEYWORD ADD ( CONSTRAINT PK_TMC_KEYWORD PRIMARY KEY (KEYWORD_ID), UNIQUE (KEYWORD_NAME));
+
+#ALTER TABLE TMC_PROJECT ADD ( CONSTRAINT PK_PROJECT PRIMARY KEY (PROJECT_ID));
+
+#ALTER TABLE TMC_REL_HELP_KEYWORD ADD ( CONSTRAINT PK_TMC_REL_HELP_KEYWORD PRIMARY KEY (HELP_ITEM_ID, KEYWORD_ID));
+
+#ALTER TABLE TMC_REL_HELP_TOPIC ADD ( UNIQUE (HELP_ITEM_ID));
+
+#ALTER TABLE TMC_STATE ADD ( CONSTRAINT PK_TMC_STATE PRIMARY KEY (STATE_ID));
+
+#ALTER TABLE TMC_TEMPLATE_TABLE ADD ( CONSTRAINT TMC_TEMPLATE_TABLE_ID PRIMARY KEY (TEMPLATE_ID));
+
+#ALTER TABLE TMC_TIMESTAMP_TABLE ADD ( CONSTRAINT UNQ_TIMESTAMP_TABLE UNIQUE (TMC_INSTANCE, TABLE_NAME));
+
+#ALTER TABLE TMC_TOPIC ADD ( CONSTRAINT PK_TMC_TOPIC PRIMARY KEY (TOPIC_ID), UNIQUE (TOPIC_NAME));
+
+#ALTER TABLE TMC_USER ADD ( CONSTRAINT PK_USERID PRIMARY KEY (USER_ID), UNIQUE (USER_NAME));
+
+#ALTER TABLE TM_CONTAINER ADD ( CONSTRAINT PK_TM_CONTAINER PRIMARY KEY (CONTAINER_ID));
+
+#ALTER TABLE TM_ENTITY ADD ( CONSTRAINT TM_CONTEXT_TABLE_CTXTID PRIMARY KEY (ENTITY_ID));
+
+#ALTER TABLE TM_POOL ADD ( CONSTRAINT PK_TM_POOL PRIMARY KEY (POOL_ID), UNIQUE (POOL_NAME));
+
+#ALTER TABLE TM_WORKER ADD ( CONSTRAINT PK_TM_WORKER PRIMARY KEY (WORKER_ID));
+
+#ALTER TABLE AM_REL_CONTAINER_FILE ADD ( CONSTRAINT CONT_ID_REL FOREIGN KEY (CONTAINER_ID) REFERENCES AM_CONTAINER (CONTAINER_ID) ON DELETE CASCADE);
+
+#ALTER TABLE AM_REL_ENTITY_PROJECT ADD ( CONSTRAINT PROJECT_ID_FK2 FOREIGN KEY (PROJECT_ID) REFERENCES TMC_PROJECT (PROJECT_ID) ON DELETE CASCADE DISABLE, CONSTRAINT VHOST_ID_FK FOREIGN KEY (VHOST_ID) REFERENCES AM_VHOST (VHOST_ID) ON DELETE CASCADE DISABLE);
+
+#ALTER TABLE TMC_REL_HELP_TOPIC ADD ( CONSTRAINT FK_REL_HELP_ITEM_ID FOREIGN KEY (HELP_ITEM_ID) REFERENCES TMC_HELP_ITEM (HELP_ITEM_ID) ON DELETE CASCADE, CONSTRAINT FK_SUBTOPIC_ID FOREIGN KEY (SUBTOPIC_ID) REFERENCES TMC_TOPIC (TOPIC_ID) ON DELETE CASCADE, CONSTRAINT FK_TOPIC_ID FOREIGN KEY (TOPIC_ID) REFERENCES TMC_TOPIC (TOPIC_ID) ON DELETE CASCADE);
+
+#ALTER TABLE TM_REL_ENTITY_PROJECT ADD ( CONSTRAINT ENTITY_ID_FK FOREIGN KEY (ENTITY_ID) REFERENCES TM_ENTITY (ENTITY_ID) ON DELETE CASCADE DISABLE, CONSTRAINT PROJECT_ID_FK FOREIGN KEY (PROJECT_ID) REFERENCES TMC_PROJECT (PROJECT_ID) ON DELETE CASCADE DISABLE);
+
Added: incubator/lokahi/lokahi/branches/mysql/database/mysql/tmc_instance_data.sql
URL: http://svn.apache.org/viewvc/incubator/lokahi/lokahi/branches/mysql/database/mysql/tmc_instance_data.sql?view=auto&rev=451328
==============================================================================
--- incubator/lokahi/lokahi/branches/mysql/database/mysql/tmc_instance_data.sql (added)
+++ incubator/lokahi/lokahi/branches/mysql/database/mysql/tmc_instance_data.sql Fri Sep 29 08:45:56 2006
@@ -0,0 +1,29 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+--
+-- <ScriptOptions statementTerminator="/" />
+-- SQL99
+
+insert into TMC_INSTANCE
+( INSTANCE_NAME )
+values
+--( '@INITIAL_INSTANCE_NAME@' )
+( 'lokahi-dev' )
+/
+commit;
+
Added: incubator/lokahi/lokahi/branches/mysql/database/mysql/tmc_rel_function_user_data.sql
URL: http://svn.apache.org/viewvc/incubator/lokahi/lokahi/branches/mysql/database/mysql/tmc_rel_function_user_data.sql?view=auto&rev=451328
==============================================================================
--- incubator/lokahi/lokahi/branches/mysql/database/mysql/tmc_rel_function_user_data.sql (added)
+++ incubator/lokahi/lokahi/branches/mysql/database/mysql/tmc_rel_function_user_data.sql Fri Sep 29 08:45:56 2006
@@ -0,0 +1,148 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+--
+-- initial data for superuser USER_ID=1
+-- automatically generated by script
+insert into TMC_REL_FUNCTION_USER values ( 1,1,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,2,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,8,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,123,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,64,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,38,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,61,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,120,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,97,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,69,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,29,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,87,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,36,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,32,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,28,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,74,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,90,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,119,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,18,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,34,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,10,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,3,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,76,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,77,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,78,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,79,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,80,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,81,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,82,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,84,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,85,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,127,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,128,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,129,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,130,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,131,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,132,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,133,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,134,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,135,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,121,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,125,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,48,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,37,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,107,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,47,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,4,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,58,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,91,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,99,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,19,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,100,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,45,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,50,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,83,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,96,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,63,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,94,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,49,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,105,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,31,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,115,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,102,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,7,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,62,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,68,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,112,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,109,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,15,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,17,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,25,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,30,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,59,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,65,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,86,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,9,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,11,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,14,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,95,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,101,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,46,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,33,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,21,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,44,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,108,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,110,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,104,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,22,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,56,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,5,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,98,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,26,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,75,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,92,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,35,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,51,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,126,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,60,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,6,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,13,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,12,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,16,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,20,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,103,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,111,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,27,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,118,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,88,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,23,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,117,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,136,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,42,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,39,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,67,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,89,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,41,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,106,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,66,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,24,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,57,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,116,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,114,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,93,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,73,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,43,0,1,0 );
+insert into TMC_REL_FUNCTION_USER values ( 1,40,0,1,0 );
+
+
Added: incubator/lokahi/lokahi/branches/mysql/database/mysql/tmc_user_data.sql
URL: http://svn.apache.org/viewvc/incubator/lokahi/lokahi/branches/mysql/database/mysql/tmc_user_data.sql?view=auto&rev=451328
==============================================================================
--- incubator/lokahi/lokahi/branches/mysql/database/mysql/tmc_user_data.sql (added)
+++ incubator/lokahi/lokahi/branches/mysql/database/mysql/tmc_user_data.sql Fri Sep 29 08:45:56 2006
@@ -0,0 +1,31 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+--
+-- <ScriptOptions statementTerminator="/" />
+-- SQL99
+
+-- WARNING: This erase the User table! (and is needed to have the userid to 1)
+delete from TMC_USER;
+/
+-- ( '@USER@', '@PASSWORD@', 8, '@FIRSTNAME@', '@LASTNAME@', 'Superuser', '@MAIL@',
+
+insert into TMC_USER ( USER_NAME, PASSWORD, STATE_ID, FIRST_NAME, LAST_NAME, JOB_TITLE, EMAIL, COMMENTS )
+values
+( 'lokadmin', 'adminlok', 8, 'Maitre', 'Ludovic', 'Superuser', 'ludovic.maitre@ubik-products.com',
+'This is the first superuser needed to set up everything else from the GUI'
+);
Added: incubator/lokahi/lokahi/branches/mysql/database/mysql/views.sql
URL: http://svn.apache.org/viewvc/incubator/lokahi/lokahi/branches/mysql/database/mysql/views.sql?view=auto&rev=451328
==============================================================================
--- incubator/lokahi/lokahi/branches/mysql/database/mysql/views.sql (added)
+++ incubator/lokahi/lokahi/branches/mysql/database/mysql/views.sql Fri Sep 29 08:45:56 2006
@@ -0,0 +1,119 @@
+--
+-- Licensed to the Apache Software Foundation (ASF) under one
+-- or more contributor license agreements. See the NOTICE file
+-- distributed with this work for additional information
+-- regarding copyright ownership. The ASF licenses this file
+-- to you under the Apache License, Version 2.0 (the
+-- "License"); you may not use this file except in compliance
+-- with the License. You may obtain a copy of the License at
+--
+-- http://www.apache.org/licenses/LICENSE-2.0
+--
+-- Unless required by applicable law or agreed to in writing,
+-- software distributed under the License is distributed on an
+-- "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+-- KIND, either express or implied. See the License for the
+-- specific language governing permissions and limitations
+-- under the License.
+--
+
+CREATE OR REPLACE VIEW AM_ENTITY_VIE
+(VHOST_ID, ENV_ID, STATE_ID, VHOST_NAME, VHOST_ENTRY,
+ HOSTING_POOL_ID, NAMEBASED, CATCHALL, EX_PROJECT_ID, POOL_ID,
+ POOL_NAME, POOL_ENV_ID)
+AS
+SELECT vh.VHOST_ID, vh.ENV_ID, vh.STATE_ID, vh.VHOST_NAME, vh.VHOST_ENTRY, vh.HOSTING_POOL_ID, vh.NAMEBASED, vh.CATCHALL, vh.EX_PROJECT_ID, p.POOL_ID, p.POOL_NAME, p.ENV_ID as POOL_ENV_ID
+FROM AM_VHOST vh, TMC_REL_HOSTING_MODULES rel, am_POOL p
+where vh.hosting_POOL_ID=rel.HOSTING_POOL_ID
+AND p.POOL_ID=rel.AM_POOL_ID;
+
+
+CREATE OR REPLACE VIEW AM_WORKER_VIE
+(WORKER_ID, CONTAINER_ID, STATE_ID, HARDWARE_ID, CONTAINER_NAME,
+ SERVER_ROOT, LOG_LOCATION, SYS_USER, SYS_GROUP, START_COMMAND,
+ STOP_COMMAND, RESTART_COMMAND, FORK_WAIT, PIDFILE_LOCATION, ENV_VARIBLES,
+ MAIN_ID, HARDWARE_NAME, PHYSICAL_LOCATION, COMMENTS, INSTANCE_ID,
+ ENV_ID, HARDWARE_STATE_ID, DEFAULT_IP_ID)
+AS
+SELECT
+ w.worker_id,
+ container_id,
+ w.state_id,
+ hardware_id,
+ c.container_name,
+ c.server_root,
+ c.log_location,
+ c.sys_user,
+ c.sys_group,
+ c.start_command,
+ c.stop_command,
+ c.restart_command,
+ c.fork_wait,
+ c.pidfile_location,
+ c.env_varibles,
+ c.main_id,
+ h.hardware_name,
+ h.physical_location,
+ h.comments,
+ h.instance_id,
+ h.env_id,
+ h.status_id as hardware_state_id,
+ h.default_ip_id
+FROM
+ ((AM_WORKER w join AM_Container c using (container_ID)) JOIN TMC_HARDWARE h using (HARDWARE_ID));
+
+
+CREATE OR REPLACE VIEW TM_ENTITY_VIE
+(ENTITY_ID, ENTITY_NAME, DOC_BASE, ENTITY_STATE_ID, ENV_ID,
+ TOMCAT_POOL_ID, ENTITY_DEFIN, POOL_NAME, EX_PROJECT_ID, POOL_ENV_ID)
+AS
+SELECT
+ entity_id,
+ entity_name,
+ doc_base,
+ entity_state_id,
+ e.env_id,
+ tomcat_pool_id,
+ entity_defin,
+ pool_name,
+ EX_PROJECT_ID,
+ p.env_id as pool_env_id
+FROM
+ TM_ENTITY e JOIN TM_POOL p on e.TOMCAT_POOL_ID = p.POOL_ID;
+
+
+CREATE OR REPLACE VIEW TM_WORKER_VIE
+(WORKER_ID, CONTAINER_ID, STATE_ID, HARDWARE_ID, CONTAINER_NAME,
+ BASE_LOCATION, TOMCAT_HOME, HTTP_PORT, SYS_USER, SYS_GROUP,
+ TOMCAT_TYPE, DESCRIPTION, CONF_ENTRY, STD_OUT_LOG, JAVA_HOME,
+ START_CLASS, HARDWARE_NAME, PHYSICAL_LOCATION, COMMENTS, INSTANCE_ID,
+ ENV_ID, STATUS_ID, DEFAULT_IP_ID)
+AS
+SELECT
+ w.worker_id,
+ container_id,
+ w.state_id,
+ hardware_id,
+ c.container_name,
+ c.base_location,
+ c.tomcat_home,
+ c.http_PORT,
+ c.sys_user,
+ c.sys_group,
+ c.tomcat_type,
+ c.description,
+ c.conf_entry,
+ c.std_out_log,
+ c.java_home,
+ c.start_class,
+ h.hardware_name,
+ h.physical_location,
+ h.comments,
+ h.instance_id,
+ h.env_id,
+ h.status_id,
+ h.default_ip_id
+FROM
+ ((TM_WORKER w join TM_Container c using (container_ID)) JOIN TMC_HARDWARE h using (HARDWARE_ID));
+
+