/* ===============================================================================
 *
 * 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.*;

public class SQLServerDatabaseCommander extends DatabaseCommander
{
	private final String driver = "net.sourceforge.jtds.jdbc.Driver";
	private static String sqlServerVersion = "";
	
	public SQLServerDatabaseCommander(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);
	}
	
	public String getUrl(String hostName, String databasePortNumber, String database)
	{
		String url = "jdbc:jtds:sqlserver://" + hostName + ":" + databasePortNumber + ((this.getDatabaseInstance().equalsIgnoreCase("") ? ";DatabaseName=" + database + ";SelectMethod=Cursor" : ";INSTANCE=" + this.getDatabaseInstance() + ";DatabaseName=" + database + ";SelectMethod=Cursor"));
		return url;
		//return "jdbc:jtds:sqlserver://" + hostName + ":" + databasePortNumber + ";DatabaseName=" + database + "";
		//return "jdbc:microsoft:sqlserver://" + hostName + ":1433;DatabaseName=" + database + "";
	}

	public String getUnicodeUrl(String hostName, String databasePortNumber, String database)
	{
		String url = "jdbc:jtds:sqlserver://" + hostName + ":" + databasePortNumber + ((this.getDatabaseInstance().equalsIgnoreCase("") ? ";DatabaseName=" + database + ";SelectMethod=Cursor" : ";INSTANCE=" + this.getDatabaseInstance() + ";DatabaseName=" + database + ";SelectMethod=Cursor"));
		return url;
		//return "jdbc:jtds:sqlserver://" + hostName + ":" + databasePortNumber + ";DatabaseName=" + database + "";
		//return "jdbc:microsoft:sqlserver://" + hostName + ":1433;DatabaseName=" + database + "";
	}

	public String getTDSSpecficUrl(String hostName, String databasePortNumber, String database)
	{
		String url = "jdbc:jtds:sqlserver://" + hostName + ":" + databasePortNumber + ((this.getDatabaseInstance().equalsIgnoreCase("") ? ";DatabaseName=" + database + ";SelectMethod=Cursor" : ";INSTANCE=" + this.getDatabaseInstance() + ";DatabaseName=" + database + ";SelectMethod=Cursor"));
		return url;
		//return "jdbc:jtds:sqlserver://" + hostName + ":" + databasePortNumber + "/" + database + "";
		//return "jdbc:jtds:sqlserver://" + hostName + ":1433;DatabaseName=" + database + "";
		//return "jdbc:microsoft:sqlserver://" + hostName + ":1433;DatabaseName=" + database + "";
	}
	
	public void setupDatabase() throws Exception
	{
		if(this.createDatabase)
			createDatabase();
		
		System.out.println("this.sqlServerVersion:" + this.sqlServerVersion);
		if(this.sqlServerVersion.indexOf("9.0") > -1)
			callProcedure(getConnection(), "sp_dbcmptlevel", databaseName, "80");		

		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 = getTDSSpecficUrl(databaseHostName, databasePortNumber, databaseName);
		Connection conn = getConnection(url, this.userName, this.password);
		
		Logger.logInfo("Setting up a new database....");		
		
		try
		{
			//System.out.println("Connection:" + conn.toString());
			
			
			String sql = "CREATE TABLE #test ("
						+ " f_int INT,"
						+ " f_varchar VARCHAR(255) )";
			Statement stmt = conn.createStatement();
			stmt.execute( sql );
			
			FileInputStream fis = new FileInputStream("sqlserver_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 = getTDSSpecficUrl(databaseHostName, databasePortNumber, databaseName);
		Connection conn = getConnection(url, this.userName, this.password);
		
		Logger.logInfo("Setting up initial data....");		
		
		try
		{
			FileInputStream fis = new FileInputStream("sqlserver_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("sqlserver_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("sqlserver_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("sqlserver_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("sqlserver_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("sqlserver_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("sqlserver_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("sqlserver_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("sqlserver_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("sqlserver_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:microsoft:sqlserver://" + databaseHostName + ":1433";
		String url = "jdbc:jtds:sqlserver://" + databaseHostName + ":" + databasePortNumber + ((this.getDatabaseInstance().equalsIgnoreCase("")) ? "" : ";INSTANCE=" + this.getDatabaseInstance());
		//String url = "jdbc:jtds:sqlserver://" + databaseHostName + ":1433";
		Connection conn = getConnection(url, this.userName, this.password);
		
		callProcedure(conn, "sp_addlogin", this.infoglueDatabaseUserName, this.infoglueDatabasePassword, databaseName);
		
		conn.close();
		
		url = getTDSSpecficUrl(databaseHostName, databasePortNumber, databaseName);

		conn = getConnection(url, this.userName, this.password);
		
		callProcedure(conn, "sp_grantdbaccess", this.infoglueDatabaseUserName, databaseName);
		callProcedure(conn, "sp_addrolemember", "db_owner", this.infoglueDatabaseUserName);		
		callProcedure(conn, "sp_addrolemember", "db_owner", databaseName);		
		callProcedure(conn, "sp_addrolemember", "db_owner", this.infoglueDatabaseUserName + this.databaseSuffix);				
	}
		

	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:microsoft:sqlserver://localhost:1433;DatabaseName=mysql";
		//conn = getConnection(url, this.userName, this.password);		
	}
	
	/**
	 * This method issues command to the db.
	 */
	
	private void issueCommand(Connection conn, String sql)
	{
        if(sql == null || sql.trim().length() == 0)
        	return;
        
        try 
        {
			Statement stmt = conn.createStatement();
			stmt.execute(sql);
			stmt.close();
            //PreparedStatement pstmt = conn.prepareStatement(sql);
            //pstmt.execute();  
			//pstmt.close();
        }
        catch(SQLException ex) 
        {
        	Logger.logInfo("Command failed: " + ex.getMessage());
			Logger.logInfo("SQL: " + sql);
            System.err.println("SQLException: " + ex.getMessage());		
        }
	}
	




	/**
	 * This method issues command to the db.
	 */
	
	private void callProcedure(Connection conn, String procedure, String arg1, String arg2, String arg3)
	{
        Logger.logInfo("procedure: " + procedure + " (" + arg1 + "," + arg2 + ")");
                
        try 
        {
            CallableStatement cs = conn.prepareCall("{call " + procedure + " (?,?,?)}");
			cs.setString(1, arg1);
			cs.setString(2, arg2);
			cs.setString(3, arg3);
			cs.executeQuery();
            cs.close();
        	//conn.close();
        }
        catch(SQLException ex) 
        {
			Logger.logInfo("callProcedure failed: " + ex.getMessage());
			Logger.logInfo("procedure: " + procedure + " (" + arg1 + "," + arg2 + ")");
            System.err.println("SQLException: " + ex.getMessage());		
        }
	}
	
	
	/**
	 * This method issues command to the db.
	 */
	
	private void callProcedure(Connection conn, String procedure, String arg1, String arg2)
	{
        Logger.logInfo("procedure: " + procedure + " (" + arg1 + "," + arg2 + ")");
                
        try 
        {
            CallableStatement cs = conn.prepareCall("{call " + procedure + " (?,?)}");
			cs.setString(1, arg1);
			cs.setString(2, arg2);
			cs.executeQuery();
            cs.close();
        	//conn.close();
        }
        catch(SQLException ex) 
        {
			Logger.logInfo("callProcedure failed: " + ex.getMessage());
			Logger.logInfo("procedure: " + procedure + " (" + arg1 + "," + arg2 + ")");
            System.err.println("SQLException: " + ex.getMessage());		
        }
	}

	/**
	 * This method issues command to the db.
	 */
	
	private void callProcedure(Connection conn, String SQL)
	{
        Logger.logInfo("SQL: " + SQL);
                
        try 
        {
            CallableStatement cs = conn.prepareCall("{call " + SQL + "}");
			cs.executeQuery();
            cs.close();
        	//conn.close();
        }
        catch(SQLException ex) 
        {
			Logger.logInfo("callProcedure 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=\"sql-server\">\n");
	    xmlFile.append("<data-source class-name=\"org.apache.commons.dbcp.BasicDataSource\">\n");
	    xmlFile.append("<param name=\"driver-class-name\" value=\"" + driverClass + "\"/>\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:jtds:sqlserver://" + databaseHostName + ":" + databasePortNumber + ((this.getDatabaseInstance().equalsIgnoreCase("")) ? ";DatabaseName=" + databaseName + ";SelectMethod=Cursor\"/>\n" : ";INSTANCE=" + this.getDatabaseInstance() + ";DatabaseName=" + databaseName + ";SelectMethod=Cursor\"/>\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=\"DatabaseName\" value=\"" + databaseName + "\"/>\n");
    	xmlFile.append("<param name=\"SelectMethod\" value=\"cursor\"/>\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=\"sql-server\">\n");
	    xmlFile.append("<data-source class-name=\"org.apache.commons.dbcp.BasicDataSource\">\n");
	    xmlFile.append("<param name=\"driver-class-name\" value=\"" + driverClass + "\"/>\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:jtds:sqlserver://" + databaseHostName + ":" + databasePortNumber + ((this.getDatabaseInstance().equalsIgnoreCase("")) ? ";DatabaseName=" + this.databaseName + ";SelectMethod=Cursor\"/>\n" : ";INSTANCE=" + this.getDatabaseInstance() + ";DatabaseName=" + databaseName + ";SelectMethod=Cursor\"/>\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=\"DatabaseName\" value=\"" + databaseName + "\"/>\n");
    	xmlFile.append("<param name=\"SelectMethod\" value=\"cursor\"/>\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=\"" + driverClass + "\"/>");
        //xmlFile.append("<arg name=\"url\" value=\"jdbc:jtds:sqlserver://" + databaseHostName + ":1433/" + databaseName + ";SelectMethod=Cursor\"/>");
        xmlFile.append("<arg name=\"url\" value=\"jdbc:jtds:sqlserver://" + databaseHostName + ":" + databasePortNumber + ((this.getDatabaseInstance().equalsIgnoreCase("")) ? ";DatabaseName=" + databaseName + ";SelectMethod=Cursor\"/>" : ";INSTANCE=" + this.getDatabaseInstance() + ";DatabaseName=" + databaseName + ";SelectMethod=Cursor\"/>"));
	    
        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=\"" + driverClass + "\"/>");
        //xmlFile.append("<arg name=\"url\" value=\"jdbc:jtds:sqlserver://" + databaseHostName + ":1433/" + databaseName + ";SelectMethod=Cursor\"/>");
        xmlFile.append("<arg name=\"url\" value=\"jdbc:jtds:sqlserver://" + databaseHostName + ":" + databasePortNumber + ((this.getDatabaseInstance().equalsIgnoreCase("")) ? ";DatabaseName=" + databaseName + ";SelectMethod=Cursor\"/>" : ";INSTANCE=" + this.getDatabaseInstance() + ";DatabaseName=" + databaseName + ";SelectMethod=Cursor\"/>"));
	    
        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("DROP DATABASE infoglueDummy;");
        pstmt.execute();  
        pstmt.close();

        Connection conn = getConnection();
		sqlServerVersion = conn.getMetaData().getDatabaseMajorVersion() + "." + conn.getMetaData().getDatabaseMinorVersion();
		System.out.println("sqlServerVersion:" + sqlServerVersion);

	}
	
	
	public void testConnectDatabase() throws Exception
	{
		Connection conn = getConnection(getUrl(this.databaseHostName, this.databasePortNumber, this.databaseName + this.databaseSuffix), this.infoglueDatabaseUserName, this.infoglueDatabasePassword);
		
		sqlServerVersion = conn.getMetaData().getDatabaseMajorVersion() + "." + conn.getMetaData().getDatabaseMinorVersion();
		System.out.println("sqlServerVersion:" + sqlServerVersion);
	}
	

	/* (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
		
	}
	public String getDriver()
	{
		return driver;
	}
	
    public String getDatabaseVendor() throws Exception
    {
        return "SQL Server";
    }


}