/* ===============================================================================
 *
 * Part of the InfoGlue Content Management Platform (www.infoglue.org)
 *
 * ===============================================================================
 *
 *  Copyright (C)
 * 
 * This program is free software; you can redistribute it and/or modify it under
 * the terms of the GNU General Public License version 2, as published by the
 * Free Software Foundation. See the file LICENSE.html for more information.
 * 
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY, including the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License along with
 * this program; if not, write to the Free Software Foundation, Inc. / 59 Temple
 * Place, Suite 330 / Boston, MA 02111-1307 / USA.
 *
 * ===============================================================================
 */

package org.infoglue.cmsinstaller;
import java.sql.*;
import java.io.*;
import java.util.*;
import java.util.Date;

public class DB2DatabaseCommander extends DatabaseCommander
{
	private final String driver = "com.ibm.db2.jcc.DB2Driver";
	
	public DB2DatabaseCommander(String driverClass, String databaseHostName, String databasePortNumber, String databaseInstance, String url, String userName, String password, String infoglueDatabaseUserName, String infoglueDatabasePassword, String databaseName, String databaseSuffix, String hostName, boolean createDatabase, boolean createInitialData, boolean createAntSeekHome, boolean createOfficeStand, boolean createOfficeStand2, boolean createInfoGlueOrg) throws Exception
	{
		super(driverClass, databaseHostName, databasePortNumber, databaseInstance, url, userName, password, infoglueDatabaseUserName, infoglueDatabasePassword, databaseName, databaseSuffix, hostName, createDatabase, createInitialData, createAntSeekHome, createOfficeStand, createOfficeStand2, createInfoGlueOrg);
		//conn = getConnection();
	}
	
	public String getUrl(String hostName, String databasePortNumber, String database)
	{
		return "jdbc:db2://" + databaseHostName + ":" + databasePortNumber + "/" + databaseName + "";
	}

	public String getUnicodeUrl(String hostName, String databasePortNumber, String database)
	{
		return "jdbc:db2://" + databaseHostName + ":" + databasePortNumber + "/" + databaseName + "";
	}

	public void setupDatabase() throws Exception
	{
		//if(this.createDatabase)
		//	createDatabase();
		
		//if(this.createDatabase)
		//	createUsers();

		if(this.createInitialData)
		{	
			createTables();
			createInitialData();
			setupExamples();
		}
	}
	
	public void deleteDatabase() throws Exception
	{
		//dropDatabase();
		//dropUsers();
	}


	private void createDatabase() throws Exception
	{
		Logger.logInfo("Setting up a new database....");
		issueCommand(getConnection(), "CREATE DATABASE " + (databaseName.length() > 8 ? databaseName.substring(0, 8) : databaseName) + ";");
	}
	
	private void createTables() throws Exception
	{
		String url = "jdbc:db2://" + databaseHostName + ":" + this.databasePortNumber + "/" + databaseName + "";
		Connection conn = getConnection(url, this.userName, this.password);
		
		Logger.logInfo("Setting up all tables....");		
		
		try
		{
			FileInputStream fis = new FileInputStream("db2_infoglue_core_schema.sql");
			StringBuffer sb = new StringBuffer();
			int c;
			while((c = fis.read()) != -1)
			{
				char character = (char)c;
				sb.append(character);
			}
			String script = sb.toString();
			Logger.logInfo("script:" + script);
			
			StringTokenizer st = new StringTokenizer(script, ";");
		    while (st.hasMoreTokens()) 
		    {
		    	String command = st.nextToken();
		    	//Logger.logInfo("Command: " + command);
				issueCommand(conn, command);
		    }
				
		}
		catch(Exception e)
		{
			System.out.println("Error: " + e);
			Logger.logInfo("Error: " + e);
			e.printStackTrace(System.out);
			e.printStackTrace(System.err);
		}
	}

	private void createInitialData() throws Exception
	{
		String url = "jdbc:db2://" + databaseHostName + ":" + this.databasePortNumber + "/" + databaseName + "";
		Connection conn = getConnection(url, this.userName, this.password);
		
		Logger.logInfo("Setting up initial data....");		
		
		try
		{
			FileInputStream fis = new FileInputStream("db2_infoglue_initial_data.sql");
			StringBuffer sb = new StringBuffer();
			int c;
			while((c = fis.read()) != -1)
			{
				char character = (char)c;
				sb.append(character);
			}
			String script = sb.toString();
			//Logger.logInfo("script:" + script);
			
			String[] commands = script.split("--endquery");
			Logger.logInfo("Parsed " + commands.length + " commands from script");
			
			for(int i=0; i<commands.length; i++)
			{
				String command = commands[i];
				if(command.indexOf("SPECIAL") > -1)
					issueSpecialCommand(conn, command.trim());		
				if(command.indexOf("BLOB") > -1)
					issueSpecialBlobCommand(conn, command.trim());		
				else
				{	
					command = command.trim();
					if(!command.equalsIgnoreCase(""))
					{
					    command = command.substring(0, command.length()-1);
					    issueCommand(conn, command);
					}
				}
			}
				
		}
		catch(Exception e)
		{
			Logger.logInfo("Error: " + e);
			e.printStackTrace(System.out);
			e.printStackTrace(System.err);
		}
	}

	
	/**
	 * This method issues special blob-inserts command to the db. 
	 * I had to build my own adoption of sql to make this feature.
	 */

	protected void issueSpecialBlobCommand(Connection conn, String originalSql)
	{
		String sql = originalSql;
		//Logger.logInfo("SpecialBlob Command:" + sql);
		
		try 
		{
			String valuesPart = sql.substring(sql.indexOf("VALUES") + 6).trim();
			sql = sql.substring(0, sql.indexOf("VALUES") + 6);
			//System.out.println("sql:" + sql);
			//System.out.println("valuesPart:" + valuesPart);
			
			String tableName 		= null;
			int blobColumn			= 0;
			List columns 			= null;
			List columnValues 		= null;
			
			StringTokenizer st = new StringTokenizer(sql, " ");
			int i = 0;
			while (st.hasMoreTokens()) 
			{
				String part = st.nextToken();
				//Logger.logInfo("Part: " + part);
				
				if(i == 1)
					blobColumn = new Integer(part).intValue();
				if(i == 4)
					tableName = part;
				if(i == 5)
				{	
					columns = parseColumns(part);
				}
				
				i++;
			}
			
			columnValues = parseValues(valuesPart);
			
			String columnsString = "";
			String valuesString = "";
			Iterator columnsIterator = columns.iterator();
			while(columnsIterator.hasNext())
			{
				columnsString += (columnsString.equals("")) ? (String)columnsIterator.next() : "," + columnsIterator.next();
				valuesString += (valuesString.equals("")) ? "?" : ",?";
			}
			
			sql = "INSERT INTO " + tableName + "(" + columnsString + ") VALUES (" + valuesString + ")";
			
			
			PreparedStatement ps = conn.prepareStatement(sql);
			
			int index = 1;
			int loopCount = 0;
			Iterator columnValuesIterator = columnsIterator = columns.iterator();
			while(columnsIterator.hasNext())
			{
				columnsIterator.next();
				String value = (String)columnValues.get(loopCount);
				
				if(index == 1 || value.indexOf("'") == -1)
				{
					ps.setInt(index, new Integer(value).intValue());
				}
				else if(index == blobColumn)
				{	
					//Logger.logInfo("value:" + value);
					value = value.substring(1, value.length() - 1);
					//Logger.logInfo("value:" + value);
					
					if(value.indexOf("assetBlob:") > -1)
					{
						String fileName = value.substring(10);
						File file = new File(fileName);
						FileInputStream fis = new FileInputStream(file);
						ps.setBinaryStream(index, fis, (int)(file.length()));
						
					}
					else
					{	
						/*
						CLOB cl = CLOB.createTemporary(conn, true, CLOB.DURATION_CALL);
						cl.putString(1,value);
						ps.setClob(index, cl);
						*/
						if(value.indexOf("kommen") > -1)
						{	
							System.out.println(value);
							System.out.println("Välkommen");
						}
						
						ps.setString(index, value);
					}
				}
				else if(value.indexOf("date:") > -1)
				{
					value = value.substring(6);
					Date date = parseDate(value, "yyyy-MM-dd HH:mm:ss");
					
					ps.setDate(index, new java.sql.Date(date.getTime()));
				}
				else
				{	
					//Logger.logInfo("value:" + value);
					value = value.substring(1, value.length() - 1);
					//Logger.logInfo("value:" + value);
					ps.setString(index, value);
				}
				
				index++;
				loopCount++;
			}

			ps.executeUpdate();
		}
		catch(Exception ex) 
		{
			Logger.logInfo("Command failed: " + ex.getMessage());
			Logger.logInfo("SQL: " + originalSql);
			ex.printStackTrace();
			System.err.println("SQLException: " + ex.getMessage());		
		}
	}

	public void upgradeTo1_3(Connection conn) throws Exception
	{
	}

	public void upgradeTo1_3_2(Connection conn) throws Exception
	{
		Logger.logInfo("Upgrading to 1.3.2");		
		
		try
		{
			FileInputStream fis = new FileInputStream("db2_upgrade1_3_to1_3_2.sql");
			StringBuffer sb = new StringBuffer();
			int c;
			while((c = fis.read()) != -1)
			{
				char character = (char)c;
				sb.append(character);
			}
			String script = sb.toString();
			Logger.logInfo("script:" + script);
			
			StringTokenizer st = new StringTokenizer(script, ";");
		    while (st.hasMoreTokens()) 
		    {
		    	String command = st.nextToken();
		    	//Logger.logInfo("Command: " + command);
				issueCommand(conn, command + ";");
		    }	
		}
		catch(Exception e)
		{
			Logger.logInfo("Error: " + e);
		}
	}

	public void upgradeTo2_0(Connection conn) throws Exception
	{
		Logger.logInfo("Upgrading to 2_0");		
		
		try
		{
			FileInputStream fis = new FileInputStream("db2_upgrade1_3_2_to2_0.sql");
			StringBuffer sb = new StringBuffer();
			int c;
			while((c = fis.read()) != -1)
			{
				char character = (char)c;
				sb.append(character);
			}
			String script = sb.toString();
			Logger.logInfo("script:" + script);
			
			StringTokenizer st = new StringTokenizer(script, ";");
		    while (st.hasMoreTokens()) 
		    {
		    	String command = st.nextToken();
		    	//Logger.logInfo("Command: " + command);
				issueCommand(conn, command + ";");
		    }	
		}
		catch(Exception e)
		{
			Logger.logInfo("Error: " + e);
		}
	}

	public void upgradeTo2_1(Connection conn) throws Exception
	{
		Logger.logInfo("Upgrading to 2_1");		
		
		try
		{
			FileInputStream fis = new FileInputStream("db2_upgrade2_0_to2_1.sql");
			StringBuffer sb = new StringBuffer();
			int c;
			while((c = fis.read()) != -1)
			{
				char character = (char)c;
				sb.append(character);
			}
			String script = sb.toString();
			Logger.logInfo("script:" + script);
			
			StringTokenizer st = new StringTokenizer(script, ";");
		    while (st.hasMoreTokens()) 
		    {
		    	String command = st.nextToken();
		    	//Logger.logInfo("Command: " + command);
				issueCommand(conn, command + ";");
		    }	
		}
		catch(Exception e)
		{
			Logger.logInfo("Error: " + e);
		}
	}

	public void upgradeTo2_3(Connection conn) throws Exception
	{
		Logger.logInfo("Upgrading to 2_3");		
		
		try
		{
			FileInputStream fis = new FileInputStream("db2_upgrade2_1_to2_3.sql");
			StringBuffer sb = new StringBuffer();
			int c;
			while((c = fis.read()) != -1)
			{
				char character = (char)c;
				sb.append(character);
			}
			String script = sb.toString();
			Logger.logInfo("script:" + script);
			
			StringTokenizer st = new StringTokenizer(script, ";");
		    while (st.hasMoreTokens()) 
		    {
		    	String command = st.nextToken();
		    	//Logger.logInfo("Command: " + command);
				issueCommand(conn, command + ";");
		    }	
		}
		catch(Exception e)
		{
			Logger.logInfo("Error: " + e);
		}
	}

	public void upgradeTo2_8(Connection conn) throws Exception
	{
		Logger.logInfo("Upgrading to 2_8");		
		
		try
		{
			FileInputStream fis = new FileInputStream("db2_upgrade2_6_to2_8.sql");
			StringBuffer sb = new StringBuffer();
			int c;
			while((c = fis.read()) != -1)
			{
				char character = (char)c;
				sb.append(character);
			}
			String script = sb.toString();
			Logger.logInfo("script:" + script);
			
			StringTokenizer st = new StringTokenizer(script, ";");
		    while (st.hasMoreTokens()) 
		    {
		    	String command = st.nextToken();
		    	//Logger.logInfo("Command: " + command);
				issueCommand(conn, command + ";");
		    }	
		}
		catch(Exception e)
		{
			Logger.logInfo("Error: " + e);
		}
	}

	public void upgradeTo2_9(Connection conn) throws Exception
	{
		Logger.logInfo("Upgrading to 2_9");		
		
		try
		{
			FileInputStream fis = new FileInputStream("db2_upgrade2_8_to2_9.sql");
			StringBuffer sb = new StringBuffer();
			int c;
			while((c = fis.read()) != -1)
			{
				char character = (char)c;
				sb.append(character);
			}
			String script = sb.toString();
			Logger.logInfo("script:" + script);
			
			StringTokenizer st = new StringTokenizer(script, ";");
		    while (st.hasMoreTokens()) 
		    {
		    	String command = st.nextToken();
		    	//Logger.logInfo("Command: " + command);
				issueCommand(conn, command + ";");
		    }	
		}
		catch(Exception e)
		{
			Logger.logInfo("Error: " + e);
		}
	}

	public void upgradeTo2_9_7_1(Connection conn) throws Exception
	{
		Logger.logInfo("Upgrading to 2_9_7_1");		
		
		try
		{
			FileInputStream fis = new FileInputStream("db2_upgrade2_9_to2_9_7_1.sql");
			StringBuffer sb = new StringBuffer();
			int c;
			while((c = fis.read()) != -1)
			{
				char character = (char)c;
				sb.append(character);
			}
			String script = sb.toString();
			Logger.logInfo("script:" + script);
			
			StringTokenizer st = new StringTokenizer(script, ";");
		    while (st.hasMoreTokens()) 
		    {
		    	String command = st.nextToken();
		    	//Logger.logInfo("Command: " + command);
				issueCommand(conn, command + ";");
		    }	
		}
		catch(Exception e)
		{
			Logger.logInfo("Error: " + e);
		}
	}
	
	public void upgradeTo2_9_8_7(Connection conn) throws Exception
	{
		Logger.logInfo("Upgrading to 2_9_8_7");		
		
		try
		{
			FileInputStream fis = new FileInputStream("db2_upgrade2_9_7_1_to2_9_8_7.sql");
			StringBuffer sb = new StringBuffer();
			int c;
			while((c = fis.read()) != -1)
			{
				char character = (char)c;
				sb.append(character);
			}
			String script = sb.toString();
			Logger.logInfo("script:" + script);
			
			StringTokenizer st = new StringTokenizer(script, ";");
		    while (st.hasMoreTokens()) 
		    {
		    	String command = st.nextToken();
		    	//Logger.logInfo("Command: " + command);
				issueCommand(conn, command + ";");
		    }	
		}
		catch(Exception e)
		{
			Logger.logInfo("Error: " + e);
		}
	}

	private void createUsers() throws Exception
	{
		Logger.logInfo("Setting up all users....");		
		
		String url = "jdbc:db2://" + databaseHostName + "";
		Connection conn = getConnection(url, this.userName, this.password);
		
		String grantSQL = "GRANT CREATETAB, LOAD ON DATABASE " + this.databaseName + " TO " + this.infoglueDatabaseUserName + " WITH GRANT OPTION;";
		issueCommand(conn, grantSQL);
		/*
		issueCommand(conn, "GRANT ALL PRIVILEGES ON *.* TO '" + this.infoglueDatabaseUserName + "'@'%' IDENTIFIED BY '" + this.infoglueDatabasePassword + "';");
		issueCommand(conn, "GRANT ALL PRIVILEGES ON *.* TO '" + this.infoglueDatabaseUserName + "'@'localhost' IDENTIFIED BY '" + this.infoglueDatabasePassword + "';");
		issueCommand(conn, "GRANT ALL PRIVILEGES ON *.* TO '" + this.infoglueDatabaseUserName + "'@'127.0.0.1' IDENTIFIED BY '" + this.infoglueDatabasePassword + "';");
		issueCommand(conn, "GRANT ALL PRIVILEGES ON *.* TO '" + this.infoglueDatabaseUserName + "'@'" + this.databaseHostName + "' IDENTIFIED BY '" + this.infoglueDatabasePassword + "';");
		issueCommand(conn, "GRANT ALL PRIVILEGES ON *.* TO '" + this.infoglueDatabaseUserName + "'@'" + this.hostName + "' IDENTIFIED BY '" + this.infoglueDatabasePassword + "';");
		issueCommand(conn, "GRANT ALL PRIVILEGES ON *.* TO '" + this.infoglueDatabaseUserName + "'@'" + getHostAddress() + "' IDENTIFIED BY '" + this.infoglueDatabasePassword + "';");
		*/
	}
		

	private void dropDatabase() throws Exception
	{
		Logger.logInfo("Deleting the database....");		
		issueCommand(getConnection(), "DROP DATABASE " + databaseName + ";");
	}


	private void dropUsers() throws Exception
	{
		Logger.logInfo("Dropping all users....");		
		
		String url = "jdbc:db2://" + databaseHostName + "";
		Connection conn = getConnection(url, this.userName, this.password);
		
		issueCommand(conn, "DELETE FROM mysql.columns_priv WHERE User = '" + this.databaseSuffix + "'");
		issueCommand(conn, "DELETE FROM mysql.tables_priv WHERE User = '" + this.databaseSuffix + "'");
		issueCommand(conn, "DELETE FROM mysql.db WHERE User = '" + this.infoglueDatabaseUserName + "'");
		issueCommand(conn, "DELETE FROM mysql.user WHERE User = '" + this.infoglueDatabaseUserName + "'");
	}
	
	/**
	 * This method issues command to the db.
	 */
	
	private void issueCommand(Connection conn, String sql)
	{
		if(sql == null || sql.trim().length() == 0 || sql.trim().equalsIgnoreCase(";"))
			return;
			              
        try 
        {
			//sql = sql.substring(0, sql.length() - 1);
			System.out.println("issueCommand: " + sql);
        	
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.execute();  
            pstmt.close();
        	//conn.close();
        	
        	//Statement statement = conn.createStatement();
        	//statement.executeUpdate(sql);
        	//statement.close();
        }
        catch(SQLException ex) 
        {
			Logger.logInfo("Command failed: " + ex.getMessage());
			Logger.logInfo("SQL: " + sql);
            System.err.println("SQLException: " + ex.getMessage());		
        }
	}
	
	
	public void createCastorFile() throws Exception
	{
		StringBuffer xmlFile = new StringBuffer();
		
		xmlFile.append("<!DOCTYPE database PUBLIC \"-//EXOLAB/Castor JDO Configuration DTD Version 1.0//EN\" \"http://castor.codehaus.org/jdo-conf.dtd\">\n");
		xmlFile.append("<jdo-conf>\n");
		xmlFile.append("<database name=\"INFOGLUE_CMS\" engine=\"db2\">\n");
	    xmlFile.append("<data-source class-name=\"org.apache.commons.dbcp.BasicDataSource\">\n");
	    xmlFile.append("<param name=\"driver-class-name\" value=\"com.ibm.db2.jcc.DB2Driver\"/>\n");
	    xmlFile.append("<param name=\"username\" value=\"" + this.infoglueDatabaseUserName + "\"/>\n");
	    xmlFile.append("<param name=\"password\" value=\"" + this.infoglueDatabasePassword + "\"/>\n");
	    xmlFile.append("<param name=\"url\" value=\"jdbc:db2://" + databaseHostName + ":" + this.databasePortNumber + "/" + databaseName + "\"/>\n");
	    xmlFile.append("<param name=\"validation-query\" value=\"SELECT 1 from cmInfoGlueProperties\"/>");
	    xmlFile.append("<param name=\"max-active\" value=\"100\"/>\n");
	    xmlFile.append("<param name=\"connection-properties\" value=\"SetBigStringTryClob=true\"/>\n");
	    xmlFile.append("</data-source>\n");
	    xmlFile.append("<mapping href=\"classes/oracle_mapping.xml\"/>\n");
	    xmlFile.append("</database>\n");
	    xmlFile.append("<transaction-demarcation mode=\"local\" />\n");
	    xmlFile.append("</jdo-conf>\n");
						
		PrintWriter pw = new PrintWriter(new FileWriter("localConfigs/database.xml"));
		pw.println(xmlFile.toString());
		pw.close();
	}
	
	public void createCastorRootFile() throws Exception
	{
		StringBuffer xmlFile = new StringBuffer();
		
		xmlFile.append("<!DOCTYPE database PUBLIC \"-//EXOLAB/Castor JDO Configuration DTD Version 1.0//EN\" \"http://castor.codehaus.org/jdo-conf.dtd\">\n");
		xmlFile.append("<jdo-conf>\n");
		xmlFile.append("<database name=\"INFOGLUE_CMS\" engine=\"db2\">\n");
	    xmlFile.append("<data-source class-name=\"org.apache.commons.dbcp.BasicDataSource\">\n");
	    xmlFile.append("<param name=\"driver-class-name\" value=\"com.ibm.db2.jcc.DB2Driver\"/>\n");
	    xmlFile.append("<param name=\"username\" value=\"" + this.userName + "\"/>\n");
	    xmlFile.append("<param name=\"password\" value=\"" + this.password + "\"/>\n");
	    xmlFile.append("<param name=\"url\" value=\"jdbc:db2://" + databaseHostName + ":" + this.databasePortNumber + "/" + databaseName + "\"/>\n");
	    xmlFile.append("<param name=\"validation-query\" value=\"SELECT 1 from cmInfoGlueProperties\"/>");
	    xmlFile.append("<param name=\"max-active\" value=\"100\"/>\n");
	    xmlFile.append("<param name=\"connection-properties\" value=\"\"/>\n");
	    xmlFile.append("</data-source>\n");
	    xmlFile.append("<mapping href=\"classes/oracle_mapping.xml\"/>\n");
	    xmlFile.append("</database>\n");
	    xmlFile.append("<transaction-demarcation mode=\"local\" />\n");
	    xmlFile.append("</jdo-conf>\n");
		
		PrintWriter pw = new PrintWriter(new FileWriter("localConfigs/database.xml"));
		pw.println(xmlFile.toString());
		pw.close();
	}
	
	
	public void createOSPropertiesRootFile() throws Exception
	{
	    StringBuffer xmlFile = new StringBuffer();
	    
	    xmlFile.append("<propertysets>");
	    xmlFile.append("<propertyset name=\"aggregate\" class=\"com.opensymphony.module.propertyset.aggregate.AggregatePropertySet\"/>");
	    xmlFile.append("<propertyset name=\"cached\" class=\"com.opensymphony.module.propertyset.cached.CachingPropertySet\"/>");
	    xmlFile.append("<propertyset name=\"jdbc\" class=\"org.infoglue.cms.util.workflow.InfoGlueJDBCPropertySet\">");
        xmlFile.append("<arg name=\"username\" value=\"" + this.infoglueDatabaseUserName + "\"/>");
        xmlFile.append("<arg name=\"password\" value=\"" + this.infoglueDatabasePassword + "\"/>");
        xmlFile.append("<arg name=\"driverClassName\" value=\"com.ibm.db2.jcc.DB2Driver\"/>");
        xmlFile.append("<arg name=\"url\" value=\"jdbc:db2://" + databaseHostName + ":" + this.databasePortNumber + "/" + databaseName + "\"/>");

        xmlFile.append("<arg name=\"dbcp.validationQuery\" value=\"SELECT 1 from cmInfoGlueProperties\"/>");
        
        xmlFile.append("<arg name=\"table.name\" value=\"OS_PROPERTYENTRY\"/>");
        /*
        xmlFile.append("<arg name=\"col.globalKey\" value=\"GLOBAL_KEY\"/>");
        xmlFile.append("<arg name=\"col.itemKey\" value=\"ITEM_KEY\"/>");
	    xmlFile.append("<arg name=\"col.itemType\" value=\"ITEM_TYPE\"/>");
	    xmlFile.append("<arg name=\"col.string\" value=\"STRING_VALUE\"/>");
	    xmlFile.append("<arg name=\"col.date\" value=\"DATE_VALUE\"/>");
	    xmlFile.append("<arg name=\"col.data\" value=\"DATA_VALUE\"/>");
	    xmlFile.append("<arg name=\"col.float\" value=\"FLOAT_VALUE\"/>");
	    xmlFile.append("<arg name=\"col.number\" value=\"NUMBER_VALUE\"/>");
	    */
	    xmlFile.append("<arg name=\"col.globalKey\" value=\"entity_name\"/>");
	    xmlFile.append("<arg name=\"col.entityId\" value=\"entity_id\"/>");
	    xmlFile.append("<arg name=\"col.itemKey\" value=\"entity_key\"/>");
	    xmlFile.append("<arg name=\"col.itemType\" value=\"key_type\"/>");
	    xmlFile.append("<arg name=\"col.booleanVal\" value=\"boolean_val\"/>");
	    xmlFile.append("<arg name=\"col.string\" value=\"string_val\"/>");
	    xmlFile.append("<arg name=\"col.date\" value=\"date_val\"/>");
	    xmlFile.append("<arg name=\"col.data\" value=\"data_val\"/>");
	    xmlFile.append("<arg name=\"col.float\" value=\"double_val\"/>");
	    xmlFile.append("<arg name=\"col.number\" value=\"int_val\"/>");

	    xmlFile.append("</propertyset>");
        xmlFile.append("<propertyset name=\"ejb\" class=\"com.opensymphony.module.propertyset.ejb.EJBPropertySet\"/>");
        xmlFile.append("<propertyset name=\"javabeans\" class=\"com.opensymphony.module.propertyset.javabeans.BeanIntrospectorPropertySet\"/>");
        xmlFile.append("<propertyset name=\"map\" class=\"com.opensymphony.module.propertyset.map.MapPropertySet\"/>");
        xmlFile.append("<propertyset name=\"memory\" class=\"com.opensymphony.module.propertyset.memory.MemoryPropertySet\"/>");
        xmlFile.append("<propertyset name=\"serializable\" class=\"com.opensymphony.module.propertyset.memory.SerializablePropertySet\"/>");
        xmlFile.append("<propertyset name=\"ofbiz\" class=\"com.opensymphony.module.propertyset.ofbiz.OFBizPropertySet\"/>");
        xmlFile.append("<propertyset name=\"hibernate\" class=\"org.infoglue.cms.util.workflow.hibernate.InfoGlueHibernatePropertySet\"/>");
        xmlFile.append("<propertyset name=\"xml\" class=\"com.opensymphony.module.propertyset.xml.XMLPropertySet\"/>");
        xmlFile.append("</propertysets>");
	
		PrintWriter pw = new PrintWriter(new FileWriter("propertyset.xml"));
		pw.println(xmlFile.toString());
		pw.close();
	}

	public void createOSPropertiesFile() throws Exception
	{
	    StringBuffer xmlFile = new StringBuffer();
	    
	    xmlFile.append("<propertysets>");
	    xmlFile.append("<propertyset name=\"aggregate\" class=\"com.opensymphony.module.propertyset.aggregate.AggregatePropertySet\"/>");
	    xmlFile.append("<propertyset name=\"cached\" class=\"com.opensymphony.module.propertyset.cached.CachingPropertySet\"/>");
	    xmlFile.append("<propertyset name=\"jdbc\" class=\"org.infoglue.cms.util.workflow.InfoGlueJDBCPropertySet\">");
        xmlFile.append("<arg name=\"username\" value=\"" + this.infoglueDatabaseUserName + "\"/>");
        xmlFile.append("<arg name=\"password\" value=\"" + this.infoglueDatabasePassword + "\"/>");
        xmlFile.append("<arg name=\"driverClassName\" value=\"com.ibm.db2.jcc.DB2Driver\"/>");
        xmlFile.append("<arg name=\"url\" value=\"jdbc:db2://" + databaseHostName + ":" + this.databasePortNumber + "/" + databaseName + "\"/>");

        xmlFile.append("<arg name=\"dbcp.validationQuery\" value=\"SELECT 1 from cmInfoGlueProperties\"/>");
        
        xmlFile.append("<arg name=\"table.name\" value=\"OS_PROPERTYENTRY\"/>");
        /*
        xmlFile.append("<arg name=\"col.globalKey\" value=\"GLOBAL_KEY\"/>");
        xmlFile.append("<arg name=\"col.itemKey\" value=\"ITEM_KEY\"/>");
	    xmlFile.append("<arg name=\"col.itemType\" value=\"ITEM_TYPE\"/>");
	    xmlFile.append("<arg name=\"col.string\" value=\"STRING_VALUE\"/>");
	    xmlFile.append("<arg name=\"col.date\" value=\"DATE_VALUE\"/>");
	    xmlFile.append("<arg name=\"col.data\" value=\"DATA_VALUE\"/>");
	    xmlFile.append("<arg name=\"col.float\" value=\"FLOAT_VALUE\"/>");
	    xmlFile.append("<arg name=\"col.number\" value=\"NUMBER_VALUE\"/>");
	    */
	    xmlFile.append("<arg name=\"col.globalKey\" value=\"entity_name\"/>");
	    xmlFile.append("<arg name=\"col.entityId\" value=\"entity_id\"/>");
	    xmlFile.append("<arg name=\"col.itemKey\" value=\"entity_key\"/>");
	    xmlFile.append("<arg name=\"col.itemType\" value=\"key_type\"/>");
	    xmlFile.append("<arg name=\"col.booleanVal\" value=\"boolean_val\"/>");
	    xmlFile.append("<arg name=\"col.string\" value=\"string_val\"/>");
	    xmlFile.append("<arg name=\"col.date\" value=\"date_val\"/>");
	    xmlFile.append("<arg name=\"col.data\" value=\"data_val\"/>");
	    xmlFile.append("<arg name=\"col.float\" value=\"double_val\"/>");
	    xmlFile.append("<arg name=\"col.number\" value=\"int_val\"/>");

	    xmlFile.append("</propertyset>");
        xmlFile.append("<propertyset name=\"ejb\" class=\"com.opensymphony.module.propertyset.ejb.EJBPropertySet\"/>");
        xmlFile.append("<propertyset name=\"javabeans\" class=\"com.opensymphony.module.propertyset.javabeans.BeanIntrospectorPropertySet\"/>");
        xmlFile.append("<propertyset name=\"map\" class=\"com.opensymphony.module.propertyset.map.MapPropertySet\"/>");
        xmlFile.append("<propertyset name=\"memory\" class=\"com.opensymphony.module.propertyset.memory.MemoryPropertySet\"/>");
        xmlFile.append("<propertyset name=\"serializable\" class=\"com.opensymphony.module.propertyset.memory.SerializablePropertySet\"/>");
        xmlFile.append("<propertyset name=\"ofbiz\" class=\"com.opensymphony.module.propertyset.ofbiz.OFBizPropertySet\"/>");
        xmlFile.append("<propertyset name=\"hibernate\" class=\"org.infoglue.cms.util.workflow.hibernate.InfoGlueHibernatePropertySet\"/>");
        xmlFile.append("<propertyset name=\"xml\" class=\"com.opensymphony.module.propertyset.xml.XMLPropertySet\"/>");
        xmlFile.append("</propertysets>");
	
		PrintWriter pw = new PrintWriter(new FileWriter("localConfigs/propertyset.xml"));
		pw.println(xmlFile.toString());
		pw.close();
	}

	public void testSetupDummyDatabase() throws Exception
	{
		//issueCommand(getConnection(), "CREATE DATABASE igDummy;");

        PreparedStatement pstmt = getConnection().prepareStatement("CREATE TABLE DUMMYTABLE(ID INTEGER NOT NULL)");
        pstmt.execute();  
        pstmt.close();
        
        pstmt = getConnection().prepareStatement("DROP TABLE DUMMYTABLE");
        pstmt.execute();  
        pstmt.close();

        //issueCommand(getConnection(), "DROP DATABASE igDummy;");
	}
	
	
	public void testConnectDatabase() throws Exception
	{
		System.out.println(this.infoglueDatabaseUserName + ":" + this.infoglueDatabasePassword);
		getConnection(getUrl(this.databaseHostName, this.databasePortNumber, this.databaseName + this.databaseSuffix), this.infoglueDatabaseUserName, this.infoglueDatabasePassword);
		//getConnection(getUrl(this.databaseHostName, this.databaseName + this.databaseSuffix), "infoglue" + this.databaseSuffix, this.infoglueDatabasePassword);
	}
	

	public String getDriver()
	{
		return this.driver;
	}

    public String getDatabaseVendor() throws Exception
    {
        return "DB2";
    }

}