/* ===============================================================================
 *
 * 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 com.mysql.jdbc.ResultSetMetaData;

public class MySQLDatabaseCommander extends DatabaseCommander
{
	private final String driver = "com.mysql.jdbc.Driver";
	private static String charset = null;
	private static String mysqlVersion = null;
	
	//private Connection conn = null;
	
	public MySQLDatabaseCommander(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:mysql://" + databaseHostName + ":" + databasePortNumber + "/" + databaseName + "";
	}

	public String getUnicodeUrl(String hostName, String databasePortNumber, String database)
	{
		return "jdbc:mysql://" + databaseHostName + ":" + databasePortNumber + "/" + databaseName + "?useUnicode=true&characterEncoding=UTF-8";
	}

	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 + ";");
	}
	
	private void createTables() throws Exception
	{
		String url = "jdbc:mysql://" + databaseHostName + ":" + this.databasePortNumber + "/" + databaseName + "";
		Connection conn = getConnection(url, this.userName, this.password);
		
		Logger.logInfo("Setting up a new database....");		
		
		try
		{
			FileInputStream fis = new FileInputStream("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)
		{
			Logger.logInfo("Error: " + e);
		}
	}

	private void createInitialData() throws Exception
	{
		String url = "jdbc:mysql://" + databaseHostName + ":" + this.databasePortNumber + "/" + databaseName + "";
		Connection conn = getConnection(url, this.userName, this.password);
		
		Logger.logInfo("Setting up initial data....");		
		
		try
		{
			FileInputStream fis = new FileInputStream("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());		
				else
					issueCommand(conn, command.trim());
			}
				
		}
		catch(Exception e)
		{
			Logger.logInfo("Error: " + e);
		}
		
	}


	public void upgradeTo1_3(Connection conn) throws Exception
	{
		Logger.logInfo("Upgrading to 1.3");		
		
		try
		{
			FileInputStream fis = new FileInputStream("upgrade1_2_to1_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 upgradeTo1_3_2(Connection conn) throws Exception
	{
		Logger.logInfo("Upgrading to 1.3.2");		
		
		try
		{
			FileInputStream fis = new FileInputStream("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("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("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("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("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("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("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("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:mysql://" + databaseHostName + ":" + this.databasePortNumber + "/mysql";
		Connection conn = getConnection(url, this.userName, this.password);

		Logger.logInfo("url:" + url);		
		
		Logger.logInfo("conn:" + conn);	

		System.out.println("GRANT ALL PRIVILEGES ON `" + this.databaseName + "`.* TO '" + this.infoglueDatabaseUserName + "'@'%' IDENTIFIED BY '" + this.infoglueDatabasePassword + "';");
		
		issueCommand(conn, "GRANT ALL PRIVILEGES ON `" + this.databaseName + "`.* TO '" + this.infoglueDatabaseUserName + "'@'%' IDENTIFIED BY '" + this.infoglueDatabasePassword + "';");
		issueCommand(conn, "GRANT ALL PRIVILEGES ON `" + this.databaseName + "`.* TO '" + this.infoglueDatabaseUserName + "'@'localhost' IDENTIFIED BY '" + this.infoglueDatabasePassword + "';");
		issueCommand(conn, "GRANT ALL PRIVILEGES ON `" + this.databaseName + "`.* TO '" + this.infoglueDatabaseUserName + "'@'127.0.0.1' IDENTIFIED BY '" + this.infoglueDatabasePassword + "';");
		issueCommand(conn, "GRANT ALL PRIVILEGES ON `" + this.databaseName + "`.* TO '" + this.infoglueDatabaseUserName + "'@'" + this.databaseHostName + "' IDENTIFIED BY '" + this.infoglueDatabasePassword + "';");
		issueCommand(conn, "GRANT ALL PRIVILEGES ON `" + this.databaseName + "`.* TO '" + this.infoglueDatabaseUserName + "'@'" + this.hostName + "' IDENTIFIED BY '" + this.infoglueDatabasePassword + "';");
		issueCommand(conn, "GRANT ALL PRIVILEGES ON `" + this.databaseName + "`.* 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:mysql://" + databaseHostName + ":" + this.databasePortNumber + "/mysql";
		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 
        {
            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=\"mysql\">\n");
	    xmlFile.append("<data-source class-name=\"org.apache.commons.dbcp.BasicDataSource\">\n");
	    xmlFile.append("<param name=\"driver-class-name\" value=\"com.mysql.jdbc.Driver\"/>\n");
	    xmlFile.append("<param name=\"username\" value=\"" + this.infoglueDatabaseUserName + "\"/>\n");
	    xmlFile.append("<param name=\"password\" value=\"" + this.infoglueDatabasePassword + "\"/>\n");
	    
	    if(mysqlVersion.indexOf("4.1") > -1 && charset != null)
      	{
    	    xmlFile.append("<param name=\"url\" value=\"jdbc:mysql://" + databaseHostName + ":" + this.databasePortNumber + "/" + databaseName + "?autoReconnect=true\"/>\n");
      	    //xmlFile.append("<param name=\"useUnicode\" value=\"true\"/>\n");
      	    //xmlFile.append("<param name=\"characterEncoding\" value=\"" + charset + "\"/>\n");
      	}
      	else
      	{
    	    xmlFile.append("<param name=\"url\" value=\"jdbc:mysql://" + databaseHostName + ":" + this.databasePortNumber + "/" + databaseName + "?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8\"/>\n");
      	    //xmlFile.append("<param name=\"useUnicode\" value=\"true\"/>\n");
      	    //xmlFile.append("<param name=\"characterEncoding\" value=\"UTF-8\"/>\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("<param name=\"autoReconnect\" value=\"true\"/>\n");
	    
      	xmlFile.append("</data-source>\n");
	    xmlFile.append("<mapping href=\"classes/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=\"mysql\">\n");
	    xmlFile.append("<data-source class-name=\"org.apache.commons.dbcp.BasicDataSource\">\n");
	    xmlFile.append("<param name=\"driver-class-name\" value=\"com.mysql.jdbc.Driver\"/>\n");
	    xmlFile.append("<param name=\"username\" value=\"" + this.userName + "\"/>\n");
	    xmlFile.append("<param name=\"password\" value=\"" + this.password + "\"/>\n");
	    
      	if(mysqlVersion.indexOf("4.1") > -1 && charset != null)
      	{
    	    xmlFile.append("<param name=\"url\" value=\"jdbc:mysql://" + databaseHostName + ":" + this.databasePortNumber + "/" + databaseName + "\"/>\n");
      	    //xmlFile.append("<param name=\"useUnicode\" value=\"true\"/>\n");
      	    //xmlFile.append("<param name=\"characterEncoding\" value=\"" + charset + "\"/>\n");
      	}
      	else
      	{
    	    xmlFile.append("<param name=\"url\" value=\"jdbc:mysql://" + databaseHostName + ":" + this.databasePortNumber + "/" + databaseName + "?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8\"/>\n");
      	    //xmlFile.append("<param name=\"useUnicode\" value=\"true\"/>\n");
      	    //xmlFile.append("<param name=\"characterEncoding\" value=\"UTF-8\"/>\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("<param name=\"autoReconnect\" value=\"true\"/>\n");

      	xmlFile.append("</data-source>\n");
	    xmlFile.append("<mapping href=\"classes/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.mysql.jdbc.Driver\"/>");

        if(mysqlVersion.indexOf("4.1") > -1 && charset != null)
      	{
            xmlFile.append("<arg name=\"url\" value=\"jdbc:mysql://" + databaseHostName + ":" + this.databasePortNumber + "/" + databaseName + "?autoReconnect=true\"/>");
      	}
        else
      	{
            xmlFile.append("<arg name=\"url\" value=\"jdbc:mysql://" + databaseHostName + ":" + this.databasePortNumber + "/" + databaseName + "?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8\"/>");
        }

        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.mysql.jdbc.Driver\"/>");

        if(mysqlVersion.indexOf("4.1") > -1 && charset != null)
      	{
            xmlFile.append("<arg name=\"url\" value=\"jdbc:mysql://" + databaseHostName + ":" + this.databasePortNumber + "/" + databaseName + "?autoReconnect=true\"/>");
      	}
        else
      	{
            xmlFile.append("<arg name=\"url\" value=\"jdbc:mysql://" + databaseHostName + ":" + this.databasePortNumber + "/" + databaseName + "?autoReconnect=true&amp;useUnicode=true&amp;characterEncoding=UTF-8\"/>");
        }

        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
	{
        PreparedStatement pstmt = getConnection().prepareStatement("CREATE DATABASE infoglueDummy;");
        pstmt.execute();  
        pstmt.close();

        pstmt = getConnection().prepareStatement("CREATE TABLE infoglueDummy.cmTest(test varchar(200) NOT NULL) TYPE=MyISAM;");
        pstmt.execute();  
        pstmt.close();

        pstmt = getConnection().prepareStatement("INSERT INTO infoglueDummy.cmTest(test) values ('Test');");
        pstmt.execute();  
        pstmt.close();

        String sql = "SELECT * from infoglueDummy.cmTest;";			
		PreparedStatement ps = getConnection().prepareStatement(sql);
		ps.execute();
		ResultSet rs = ps.getResultSet();
		mysqlVersion = ps.getConnection().getMetaData().getDatabaseMajorVersion() + "." + ps.getConnection().getMetaData().getDatabaseMinorVersion();
		charset = ((ResultSetMetaData)rs.getMetaData()).getColumnCharacterSet(1);
		System.out.println("mysqlVersion:" + mysqlVersion);
		System.out.println("Encoding:" + charset);
		
        pstmt = getConnection().prepareStatement("DROP DATABASE infoglueDummy;");
        pstmt.execute();  
        pstmt.close();
	}
	
	
	public void testConnectDatabase() throws Exception
	{
		System.out.println("testConnectDatabase");

		Connection conn = 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);

		System.out.println("conn:" + conn);

		String sql = "SELECT * from cmContent;";			
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.execute();
		ResultSet rs = ps.getResultSet();
		mysqlVersion = ps.getConnection().getMetaData().getDatabaseMajorVersion() + "." + ps.getConnection().getMetaData().getDatabaseMinorVersion();
		charset = ((ResultSetMetaData)rs.getMetaData()).getColumnCharacterSet(1);
		System.out.println("mysqlVersion:" + mysqlVersion);
		System.out.println("Encoding:" + charset);
	}

	/* (non-Javadoc)
	 * @see org.infoglue.cmsinstaller.DatabaseCommander#issueSpecialBlobCommand(java.sql.Connection, java.lang.String)
	 */
	protected void issueSpecialBlobCommand(Connection conn, String sql)
	{
		// TODO Auto-generated method stub
		
	}

	/**
	 * @return Returns the driver.
	 */
	public String getDriver()
	{
		return driver;
	}

    public static String getCharset()
    {
        return charset;
    }
    
    public static String getMysqlVersion()
    {
        return mysqlVersion;
    }

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