/* ===============================================================================
 *
 * 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.util.Iterator;
import java.util.List;
import java.io.*;

import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.Node;
import org.dom4j.XPath;
import org.infoglue.cms.util.dom.DOMBuilder;

/**
 * This is the class doing updates of earlier installed databases and other resources when upgrading from earlier
 * versions of infoglue. 
 */


public class UpgradeManager
{
	private boolean convertData = false;
	private String oldCharset 	= null;
	boolean is1_0 				= false;
	
	public UpgradeManager(boolean convertData, String oldCharset)
	{
		this.convertData = convertData;
		this.oldCharset = oldCharset;
	}

	/**
	 * This method is the entry point for upgrading from 1.0 / 1.1 to 1.2
	 */
	
	public void upgradeToCurrentVersion(DatabaseCommander databaseCommander, String hostName, String portNumber, String databaseName, String userName, String password) throws Exception
	{
	    //upgradeTo1_2(databaseCommander, hostName, portNumber, databaseName, userName, password);
		String url 			= databaseCommander.getUrl(hostName, portNumber, databaseName);
		Connection conn 	= databaseCommander.getConnection(url, userName, password);

	    String previousVersion = getPreviousVersion(conn, databaseCommander.getDatabaseVendor());
	    Logger.logInfo("Previous version of database:" + previousVersion);

	    if(previousVersion.equals("1.2"))
	    {
	        upgradeTo1_3(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo1_3_2(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_0(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_1(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_3(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_8(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_9(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_9_7_1(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_9_8_7(databaseCommander, hostName, portNumber, databaseName, userName, password);
		}
	    else if(previousVersion.equals("1.3"))
	    {
	        upgradeTo1_3_2(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_0(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_1(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_3(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_8(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_9(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_9_7_1(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_9_8_7(databaseCommander, hostName, portNumber, databaseName, userName, password);
		}
	    else if(previousVersion.equals("1.3.2"))
	    {
	        upgradeTo2_0(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_1(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_3(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_8(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_9(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_9_7_1(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_9_8_7(databaseCommander, hostName, portNumber, databaseName, userName, password);
		}
	    else if(previousVersion.equals("2.0"))
	    {
	        upgradeTo2_1(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_3(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_8(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_9(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_9_7_1(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_9_8_7(databaseCommander, hostName, portNumber, databaseName, userName, password);
		}
	    else if(previousVersion.equals("2.1"))
	    {
	        upgradeTo2_3(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_8(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_9(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_9_7_1(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_9_8_7(databaseCommander, hostName, portNumber, databaseName, userName, password);
	    }
	    else if(previousVersion.equals("2.3"))
	    {
	        upgradeTo2_8(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_9(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_9_7_1(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_9_8_7(databaseCommander, hostName, portNumber, databaseName, userName, password);
	    }
	    else if(previousVersion.equals("2.8"))
	    {
	        upgradeTo2_9(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_9_7_1(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_9_8_7(databaseCommander, hostName, portNumber, databaseName, userName, password);
	    }
	    else if(previousVersion.equals("2.9"))
	    {
	        upgradeTo2_9_7_1(databaseCommander, hostName, portNumber, databaseName, userName, password);
	        upgradeTo2_9_8_7(databaseCommander, hostName, portNumber, databaseName, userName, password);
	    }
	    else if(previousVersion.equals("2.9.7.1"))
	    {
	        upgradeTo2_9_8_7(databaseCommander, hostName, portNumber, databaseName, userName, password);
	    }
	}
	
	/**
	 * This method is the entry point for upgrading from 1.0 / 1.1 to 1.2
	 */
	
	public void upgradeTo1_2(DatabaseCommander databaseCommander, String hostName, String portNumber, String databaseName, String userName, String password) throws Exception
	{
		Logger.logInfo("Checking if updates need to be done to the existing database and other resources.");
		
		String url 			= databaseCommander.getUrl(hostName, portNumber, databaseName);
		String unicodeUrl 	= databaseCommander.getUnicodeUrl(hostName, portNumber, databaseName);
		Connection conn 	= databaseCommander.getConnection(url, userName, password);
		Connection conn2 	= databaseCommander.getConnection(unicodeUrl, userName, password);
		
		try 
		{
			upgradeRepositoryTable(conn);
			upgradeLanguageTable(conn);
			
			if(is1_0 && this.convertData)
				upgradeTableContents(conn, conn2, this.oldCharset);
			
			upgradeSiteNodeVersionTable(conn);
			upgradeContentTypeDefinitionTable(conn);
			upgradeContentTable(conn);
			
			updateSystemContentTypesDefinitions(conn);	
			updateSiteNodeTypesDefinitions(conn);
			updateBaseTasks(conn);
			
			conn.close();	
		}
		catch(SQLException ex) 
		{
			System.err.println("SQLException: " + ex.getMessage());
		}

	}


	/**
	 * This method is the entry point for upgrading from 1.2 to 1.3
	 */
	
	public void upgradeTo1_3(DatabaseCommander databaseCommander, String hostName, String portNumber, String databaseName, String userName, String password) throws Exception
	{
		Logger.logInfo("Checking if updates need to be done to the existing database and other resources.");
		
		String url 			= databaseCommander.getUrl(hostName, portNumber, databaseName);
		String unicodeUrl 	= databaseCommander.getUnicodeUrl(hostName, portNumber, databaseName);
		Connection conn 	= databaseCommander.getConnection(url, userName, password);
		Connection conn2 	= databaseCommander.getConnection(unicodeUrl, userName, password);
		
		try 
		{
			updateSystemContentTypesDefinitions(conn);	
						
			databaseCommander.upgradeTo1_3(conn);
			
			if(this.convertData)
				upgradeTableContents(conn, conn2, this.oldCharset);

			conn.close();	
		}
		catch(SQLException ex) 
		{
			System.err.println("SQLException: " + ex.getMessage());
		}

	}

	/**
	 * This method is the entry point for upgrading from 1.2 to 1.3
	 */
	
	public void upgradeTo1_3_2(DatabaseCommander databaseCommander, String hostName, String portNumber, String databaseName, String userName, String password) throws Exception
	{
		Logger.logInfo("Checking if updates need to be done to the existing database and other resources.");
		
		String url 			= databaseCommander.getUrl(hostName, portNumber, databaseName);
		Connection conn 	= databaseCommander.getConnection(url, userName, password);
		
		try 
		{
			databaseCommander.upgradeTo1_3_2(conn);
		
			conn.close();	
		}
		catch(SQLException ex) 
		{
			System.err.println("SQLException: " + ex.getMessage());
		}

	}

	/**
	 * This method is the entry point for upgrading from 1.2 to 1.3
	 */
	
	public void upgradeTo2_0(DatabaseCommander databaseCommander, String hostName, String portNumber, String databaseName, String userName, String password) throws Exception
	{
		Logger.logInfo("Checking if updates need to be done to the existing database and other resources.");
		
		String url 			= databaseCommander.getUrl(hostName, portNumber, databaseName);
		Connection conn 	= databaseCommander.getConnection(url, userName, password);
		
		try 
		{
			databaseCommander.upgradeTo2_0(conn);
		
			conn.close();	
		}
		catch(SQLException ex) 
		{
			System.err.println("SQLException: " + ex.getMessage());
		}

	}

	/**
	 * This method is the entry point for upgrading from 2.0 to 2.1
	 */
	
	public void upgradeTo2_1(DatabaseCommander databaseCommander, String hostName, String portNumber, String databaseName, String userName, String password) throws Exception
	{
		Logger.logInfo("Checking if updates need to be done to the existing database and other resources.");
		
		String url 			= databaseCommander.getUrl(hostName, portNumber, databaseName);
		Connection conn 	= databaseCommander.getConnection(url, userName, password);
		
		try 
		{
			databaseCommander.upgradeTo2_1(conn);
		
			conn.close();	
		}
		catch(SQLException ex) 
		{
			System.err.println("SQLException: " + ex.getMessage());
		}

	}

	/**
	 * This method is the entry point for upgrading from 2.0 to 2.1
	 */
	
	public void upgradeTo2_3(DatabaseCommander databaseCommander, String hostName, String portNumber, String databaseName, String userName, String password) throws Exception
	{
		Logger.logInfo("Checking if updates need to be done to the existing database and other resources.");
		
		String url 			= databaseCommander.getUrl(hostName, portNumber, databaseName);
		Connection conn 	= databaseCommander.getConnection(url, userName, password);
		
		try 
		{
			databaseCommander.upgradeTo2_3(conn);
		
			conn.close();	
		}
		catch(SQLException ex) 
		{
			System.err.println("SQLException: " + ex.getMessage());
		}
	}

	/**
	 * This method is the entry point for upgrading from 2.0 to 2.1
	 */
	
	public void upgradeTo2_8(DatabaseCommander databaseCommander, String hostName, String portNumber, String databaseName, String userName, String password) throws Exception
	{
		Logger.logInfo("Checking if updates need to be done to the existing database and other resources.");
		
		String url 			= databaseCommander.getUrl(hostName, portNumber, databaseName);
		Connection conn 	= databaseCommander.getConnection(url, userName, password);
		
		try 
		{
			databaseCommander.upgradeTo2_8(conn);
		
			conn.close();	
		}
		catch(SQLException ex) 
		{
			System.err.println("SQLException: " + ex.getMessage());
		}

	}

	/**
	 * This method is the entry point for upgrading from 2.0 to 2.1
	 */
	
	public void upgradeTo2_9(DatabaseCommander databaseCommander, String hostName, String portNumber, String databaseName, String userName, String password) throws Exception
	{
		Logger.logInfo("Checking if updates need to be done to the existing database and other resources.");
		
		String url 			= databaseCommander.getUrl(hostName, portNumber, databaseName);
		Connection conn 	= databaseCommander.getConnection(url, userName, password);
		
		try 
		{
			databaseCommander.upgradeTo2_9(conn);
		
			conn.close();	
		}
		catch(SQLException ex) 
		{
			System.err.println("SQLException: " + ex.getMessage());
		}

	}

	/**
	 * This method is the entry point for upgrading from 2.0 to 2.1
	 */
	
	public void upgradeTo2_9_7_1(DatabaseCommander databaseCommander, String hostName, String portNumber, String databaseName, String userName, String password) throws Exception
	{
		Logger.logInfo("Checking if updates need to be done to the existing database and other resources.");
		
		String url 			= databaseCommander.getUrl(hostName, portNumber, databaseName);
		Connection conn 	= databaseCommander.getConnection(url, userName, password);
		
		try 
		{
			databaseCommander.upgradeTo2_9_7_1(conn);
		
			conn.close();	
		}
		catch(SQLException ex) 
		{
			System.err.println("SQLException: " + ex.getMessage());
		}
	}

	/**
	 * This method is the entry point for upgrading from 2.0 to 2.1
	 */
	
	public void upgradeTo2_9_8_7(DatabaseCommander databaseCommander, String hostName, String portNumber, String databaseName, String userName, String password) throws Exception
	{
		Logger.logInfo("Checking if updates need to be done to the existing database and other resources.");
		
		String url 			= databaseCommander.getUrl(hostName, portNumber, databaseName);
		Connection conn 	= databaseCommander.getConnection(url, userName, password);
		
		try 
		{
			databaseCommander.upgradeTo2_9_8_7(conn);
		
			conn.close();	
		}
		catch(SQLException ex) 
		{
			System.err.println("SQLException: " + ex.getMessage());
		}
	}

	/*
	 * This method upgrades the repository table.
	 */
	 
	private void upgradeRepositoryTable(Connection conn) throws Exception
	{
		String sql = "SELECT * FROM cmRepository";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		

		if(rs.getMetaData().getColumnCount() == 3) //This must be the 1.0 as it should be 4 in 1.1
		{
			this.is1_0 = true;
		}

		pstmt.close();
	
		if(this.is1_0)
		{
			Logger.logInfo("Upgrading repository table from the 1.0-schema to 1.1-schema.");
				
			sql = "ALTER TABLE cmRepository ADD dnsName TEXT NULL;";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();
	
			sql = "UPDATE cmRepository SET dnsName='';";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();
	
			sql = "ALTER TABLE cmRepository MODIFY dnsName TEXT NOT NULL;";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();
		}
	}
	
	
	/*
	 * This method upgrades the language table.
	 */
	 
	private void upgradeLanguageTable(Connection conn) throws Exception
	{
		String sql = "SELECT * FROM cmLanguage";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		
		if(rs.getMetaData().getColumnCount() == 3) //This must be the 1.0 as it should be 4 in 1.1
		{
			this.is1_0 = true;
		}

		pstmt.close();
	
		if(this.is1_0)
		{
			Logger.logInfo("Upgrading language table from the 1.0-schema to 1.1-schema.");
				
			sql = "ALTER TABLE cmLanguage ADD charset TEXT NULL;";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();
	
			sql = "UPDATE cmLanguage SET charset='ISO-8859-1';";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();
	
			sql = "ALTER TABLE cmLanguage MODIFY charset TEXT NOT NULL;";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();			
		}
	}
	
	
	/*
	 * This method upgrades the SiteNodeVersion table.
	 */
	 
	private void upgradeSiteNodeVersionTable(Connection conn) throws Exception
	{
		String sql = "SELECT * FROM cmSiteNodeVersion";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		
		boolean needsUpgrade = false;
		
		try
		{
			int columnNumber = rs.findColumn("isProtected");
		}
		catch(Exception e)
		{
			Logger.logInfo("The table cmSiteNodeVersion was not up2date.");
			needsUpgrade = true;
		}
		
		pstmt.close();
	
		if(needsUpgrade)
		{
			Logger.logInfo("Upgrading cmSiteNodeVersion table from the 1.1-schema to 1.2-schema.");
				
			sql = "ALTER TABLE cmSiteNodeVersion ADD isProtected tinyint(4) NULL;";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();
	
			sql = "UPDATE cmSiteNodeVersion SET isProtected=2;";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();
	
			sql = "ALTER TABLE cmSiteNodeVersion MODIFY isProtected tinyint(4) NOT NULL;";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();
			
			
			sql = "ALTER TABLE cmSiteNodeVersion ADD disablePageCache tinyint(4) NULL;";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();
	
			sql = "UPDATE cmSiteNodeVersion SET disablePageCache=2;";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();
	
			sql = "ALTER TABLE cmSiteNodeVersion MODIFY disablePageCache tinyint(4) NOT NULL;";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();
			
			
			sql = "ALTER TABLE cmSiteNodeVersion ADD disableEditOnSight tinyint(4) NULL;";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();

			sql = "UPDATE cmSiteNodeVersion SET disableEditOnSight=2;";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();

			sql = "ALTER TABLE cmSiteNodeVersion MODIFY disableEditOnSight tinyint(4) NOT NULL;";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();
			
			
			sql = "ALTER TABLE cmSiteNodeVersion ADD contentType TEXT NULL;";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();
	
			sql = "UPDATE cmSiteNodeVersion SET contentType='text/html';";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();
	
			sql = "ALTER TABLE cmSiteNodeVersion MODIFY contentType TEXT NOT NULL;";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();			
		}
	}
	
	
	/*
	 * This method upgrades the Content table.
	 */
	 
	private void upgradeContentTable(Connection conn) throws Exception
	{
		String sql = "SELECT * FROM cmContent";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		
		boolean needsUpgrade = false;
		
		try
		{
			int columnNumber = rs.findColumn("isProtected");
		}
		catch(Exception e)
		{
			Logger.logInfo("The table cmContent was not up2date.");
			needsUpgrade = true;
		}
		
		pstmt.close();
	
		if(needsUpgrade)
		{
			Logger.logInfo("Upgrading cmContent table from the 1.1-schema to 1.2-schema.");
				
			sql = "ALTER TABLE cmContent ADD isProtected tinyint(4) NULL;";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();
	
			sql = "UPDATE cmContent SET isProtected=0;";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();
	
			sql = "ALTER TABLE cmContent MODIFY isProtected tinyint(4) NOT NULL;";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();
			
		}
	}
	
	
	/*
	 * This method upgrades the ContentTypeDefinition table.
	 */
	 
	private void upgradeContentTypeDefinitionTable(Connection conn) throws Exception
	{
		String sql = "SELECT * FROM cmContentTypeDefinition";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		
		boolean needsUpgrade = false;
		
		try
		{
			int columnNumber = rs.findColumn("type");
		}
		catch(Exception e)
		{
			Logger.logInfo("The table cmContentTypeDefinition was not up2date.");
			needsUpgrade = true;
		}
		
		pstmt.close();
	
		if(needsUpgrade)
		{
			Logger.logInfo("Upgrading cmContentTypeDefinition table from the 1.1-schema to 1.2-schema.");
				
			sql = "ALTER TABLE cmContentTypeDefinition ADD type tinyint(4) NULL;";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();
	
			sql = "UPDATE cmContentTypeDefinition SET type=0;";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();
	
			sql = "ALTER TABLE cmContentTypeDefinition MODIFY type tinyint(4) NOT NULL;";
			pstmt = conn.prepareStatement(sql);
			pstmt.execute();
			pstmt.close();
			
		}
	}
	
	
	/*
	 * This method goes through all tables of value and converts the data in them to UTF-8-encoded data.
	 * The method assumes that the given encoding is the current dataformat.
	 */

	private void upgradeTableContents(Connection conn, Connection conn2, String oldCharset) throws Exception
	{
		convertTableContents(conn, conn2, "cmAccessRight", new String[]{"accessRightId"}, new String[]{"roleName"}, oldCharset, 0, true);
		convertTableContents(conn, conn2, "cmAvailableServiceBinding", new String[]{"availableServiceBindingId"}, new String[]{"name", "description", "visualizationAction"}, oldCharset, 0, true);
		convertTableContents(conn, conn2, "cmContent", new String[]{"contentId"}, new String[]{"name"}, oldCharset, 0, true);
		convertTableContents(conn, conn2, "cmContentTypeDefinition", new String[]{"contentTypeDefinitionId"}, new String[]{"name", "schemaValue"}, oldCharset, 0, true);
		convertTableContents(conn, conn2, "cmContentVersion", new String[]{"contentVersionId"}, new String[]{"versionValue", "versionComment"}, oldCharset, 0, true);
		//convertTableContents(conn, conn2, "cmDigitalAsset", "digitalAssetId", new String[]{"assetKey", "assetFileName", "assetFilePath", "assetContentType"}, oldCharset, 0, true);
		convertTableContents(conn, conn2, "cmEvent", new String[]{"eventId"}, new String[]{"name", "description", "entityClass"}, oldCharset, 0, true);
		//convertTableContents(conn, conn2, "cmFunction", "functionId", new String[]{"name", "description", "address"}, oldCharset, 0, true);
		convertTableContents(conn, conn2, "cmLanguage", new String[]{"languageId"}, new String[]{"name", "languageCode", "charset"}, oldCharset, 0, true);
		convertTableContents(conn, conn2, "cmPublication", new String[]{"publicationId"}, new String[]{"name", "description"}, oldCharset, 0, true);
		convertTableContents(conn, conn2, "cmPublicationDetail", new String[]{"publicationDetailId"}, new String[]{"name", "description", "entityClass"}, oldCharset, 0, true);
		convertTableContents(conn, conn2, "cmQualifyer", new String[]{"qualifyerId"}, new String[]{"name", "value"}, oldCharset, 0, true);
		convertTableContents(conn, conn2, "cmRepository", new String[]{"repositoryId"}, new String[]{"name", "description", "dnsName"}, oldCharset, 0, true);
		convertTableContents(conn, conn2, "cmRole", new String[]{"roleName"}, new String[]{"roleName", "description"}, oldCharset, 1, false);
		convertTableContents(conn, conn2, "cmServiceBinding", new String[]{"serviceBindingId"}, new String[]{"name", "path"}, oldCharset, 0, true);
		convertTableContents(conn, conn2, "cmServiceDefinition", new String[]{"serviceDefinitionId"}, new String[]{"name", "description", "className"}, oldCharset, 0, true);
		convertTableContents(conn, conn2, "cmSiteNode", new String[]{"siteNodeId"}, new String[]{"name"}, oldCharset, 0, true);
		convertTableContents(conn, conn2, "cmSiteNodeTypeDefinition", new String[]{"siteNodeTypeDefinitionId"}, new String[]{"name", "description", "invokerClassName"}, oldCharset, 0, true);
		convertTableContents(conn, conn2, "cmSiteNodeVersion", new String[]{"siteNodeVersionId"}, new String[]{"versionComment"}, oldCharset, 0, true);
		convertTableContents(conn, conn2, "cmSystemUser", new String[]{"userName"}, new String[]{"userName", "password", "firstName", "lastName", "email"}, oldCharset, 1, false);
		convertTableContents(conn, conn2, "cmRoleContentTypeDefinition", new String[]{"roleContentTypeDefinitionId"}, new String[]{"roleName"}, oldCharset, 0, true);
		convertTableContents(conn, conn2, "cmRoleProperties", new String[]{"rolePropertiesId"}, new String[]{"roleName", "value"}, oldCharset, 0, true);
		convertTableContents(conn, conn2, "cmSystemUserRole", new String[]{"userName", "roleName"}, new String[]{"userName", "roleName"}, oldCharset, 1, false);
		convertTableContents(conn, conn2, "cmUserProperties", new String[]{"userPropertiesId"}, new String[]{"userName", "value"}, oldCharset, 0, true);
		
		//convertTableContents(conn, conn2, "cmTransactionHistory", "transactionHistoryId", new String[]{"name", "transactionObjectName", "systemUserName"}, oldCharset);
	}
	
	private void convertTableContents(Connection conn, Connection conn2, String table, String primaryKey[], String[] columns, String oldCharset, int primaryKeyTypeId, boolean useDriverEncoding) throws Exception
	{
		String sql = "SELECT * FROM " + table;
		Logger.logInfo("upgrading content in table " + table + " to UTF-8....");
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		
		String unconvertedValue = "";
		while(rs.next())
		{
		    String[] id = new String[primaryKey.length];
		    
		    for(int i=0; i<primaryKey.length; i++)
		    {
			    if(primaryKeyTypeId == 0)
			        id[i] = "" + rs.getInt(primaryKey[i]);
			    if(primaryKeyTypeId == 1)
			        id[i] = "" + rs.getString(primaryKey[i]) + "";
		    }

		    //Logger.logInfo("id:" + id);
		    
			for(int i=0; i < columns.length; i++)
			{
				String columnName = columns[i];

				unconvertedValue = rs.getString(columnName);
				
				Logger.logInfo("unconvertedValue:" + unconvertedValue);
				String convertedValue = unconvertedValue;
				//String convertedValue = new String(unconvertedValue.getBytes("ISO-8859-1"), "UTF-8");
				//Logger.logInfo("convertedValue:" + convertedValue);

				//String convertedValue = new String(unconvertedValue.getBytes("ISO-8859-1"));
				
				
				if(useDriverEncoding)
				{
				    String whereSQL = "";
					for(int j=0; j<primaryKey.length; j++)
				    {
				        String primaryKeyName = primaryKey[j];
				        if(whereSQL.length() > 0)
				            whereSQL += " AND ";
				        
				        //whereSQL += primaryKeyName + " = ?";
				        whereSQL += primaryKeyName + " = " + id[j];
				    }
					
					//String updateSQL = "UPDATE " + table + " SET " + columnName + " = ? WHERE " + primaryKey + " = " + id;
					String updateSQL = "UPDATE " + table + " SET " + columnName + " = ? WHERE " + whereSQL;
					Logger.logInfo("updateSQL:" + updateSQL);
					PreparedStatement rowpstmt = conn2.prepareStatement(updateSQL);
					rowpstmt.setString(1, convertedValue);
					
					/*
					for(int idIndex=0; idIndex<id.length; idIndex++)
				    {
				        String primaryKeyId = id[idIndex];
				        rowpstmt.setString(idIndex + 2, primaryKeyId);
				    }
				    */
					rowpstmt.executeUpdate();
					rowpstmt.close();	
				}
				else
				{
				    String whereSQL = "";
					for(int j=0; j<primaryKey.length; j++)
				    {
				        String primaryKeyName = primaryKey[j];
				        if(whereSQL.length() > 0)
				            whereSQL += " AND ";
				        
				        whereSQL += primaryKeyName + " = ?";
				        //whereSQL += primaryKeyName + " = " + id[j];
				    }
					
				  	convertedValue = new String(convertedValue.getBytes("UTF-8"), "ISO-8859-1");
				    String updateSQL = "UPDATE " + table + " SET " + columnName + " = ? WHERE " + whereSQL;
					Logger.logInfo("updateSQL:" + updateSQL);
					PreparedStatement rowpstmt = conn.prepareStatement(updateSQL);
					rowpstmt.setString(1, convertedValue);
					
					for(int idIndex=0; idIndex<id.length; idIndex++)
				    {
				        String primaryKeyId = id[idIndex];
				        rowpstmt.setString(idIndex + 2, primaryKeyId);
				        Logger.logInfo("index value:" + primaryKeyId);
				    }
					
					rowpstmt.executeUpdate();
					rowpstmt.close();	
				}
					/*
				String convertedValue = new String(unconvertedValue.getBytes("UTF-8"));
				String updateSQL = "UPDATE " + table + " SET " + columnName + " = ? WHERE " + primaryKey + " = " + id;
				//Logger.logInfo("updateSQL:" + updateSQL);
				PreparedStatement rowpstmt = conn.prepareStatement(updateSQL);
				rowpstmt.setString(1, convertedValue);
				*/
				
				//rowpstmt.executeUpdate();
				//rowpstmt.close();	
			}
		}
		
		pstmt.close();
	}
	
	
	private void updateSystemContentTypesDefinitions(Connection conn) throws Exception
	{
		String sql = "SELECT * FROM cmContentTypeDefinition";
		Logger.logInfo("upgrading contenttype definitions...");
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		
		boolean hasTaskDefinition = false;
		
		while(rs.next())
		{
			int id = rs.getInt("contentTypeDefinitionId");
			String name = rs.getString("name");
			String schemaValue = rs.getString("schemaValue");
			
			if(name.indexOf("Meta info") > -1)
			{
				if(schemaValue.indexOf("ComponentStructure") == -1)
				{
					DOMBuilder domBuilder = new DOMBuilder();
					Document schemaValueDocument = domBuilder.getDocument(schemaValue);
					//System.out.println(schemaValueDocument.getRootElement().asXML());
					
					Element allElements = getElementsElement(schemaValueDocument);
					//System.out.println("allElements:" + allElements.getName());
					
					String elementString = "<xs:schema elementFormDefault=\"qualified\" attributeFormDefault=\"unqualified\" version=\"2.1\" xmlns:xi=\"http://www.w3.org/2001/XInclude\" xmlns:xs=\"http://www.w3.org/2001/XMLSchema\"><xs:element name=\"ComponentStructure\" type=\"textarea\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined67\" label=\"ComponentStructure\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined38\" label=\"ComponentStructure\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined73\" label=\"normaltextarea\"></value></values></param><param id=\"width\" inputTypeId=\"0\"><values><value id=\"width\" label=\"700\"></value></values></param><param id=\"height\" inputTypeId=\"0\"><values><value id=\"height\" label=\"150\"></value></values></param><param id=\"enableWYSIWYG\" inputTypeId=\"0\"><values><value id=\"enableWYSIWYG\" label=\"false\"></value></values></param><param id=\"enableTemplateEditor\" inputTypeId=\"0\"><values><value id=\"enableTemplateEditor\" label=\"false\"></value></values></param><param id=\"enableFormEditor\" inputTypeId=\"0\"><values><value id=\"enableFormEditor\" label=\"false\"></value></values></param><param id=\"enableRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableRelationEditor\" label=\"false\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element></xs:schema>";
					Document newElementDocument = domBuilder.getDocument(elementString);
					
					List children = newElementDocument.getRootElement().elements("element");
					Iterator childrenIterator = children.iterator();
					while(childrenIterator.hasNext())
					{
						Element child = (Element)childrenIterator.next();
						allElements.add(child.createCopy());
					}
					
					String newValue = schemaValueDocument.asXML();
					
					String updateSQL = "UPDATE cmContentTypeDefinition SET schemaValue = ? WHERE contentTypeDefinitionId = " + id;
					PreparedStatement rowpstmt = conn.prepareStatement(updateSQL);
					rowpstmt.setString(1, newValue);
					rowpstmt.executeUpdate();
					rowpstmt.close();	
				}
			}
			else if(name.equalsIgnoreCase("Article"))
			{
				if(schemaValue.indexOf("RelatedAreas") == -1)
				{
					DOMBuilder domBuilder = new DOMBuilder();
					Document schemaValueDocument = domBuilder.getDocument(schemaValue);

					Element allElements = getElementsElement(schemaValueDocument);
					//System.out.println("allElements:" + allElements.getName());

					String elementString = "<xs:schema elementFormDefault=\"qualified\" attributeFormDefault=\"unqualified\" version=\"2.1\" xmlns:xi=\"http://www.w3.org/2001/XInclude\" xmlns:xs=\"http://www.w3.org/2001/XMLSchema\"><xs:element name=\"RelatedAreas\" type=\"textarea\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined93\" label=\"Related areas\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined30\" label=\"Points out related areas on the site\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined83\" label=\"normaltextfield\"></value></values></param><param id=\"width\" inputTypeId=\"0\"><values><value id=\"width\" label=\"700\"></value></values></param><param id=\"height\" inputTypeId=\"0\"><values><value id=\"height\" label=\"150\"></value></values></param><param id=\"enableWYSIWYG\" inputTypeId=\"0\"><values><value id=\"enableWYSIWYG\" label=\"false\"></value></values></param><param id=\"enableTemplateEditor\" inputTypeId=\"0\"><values><value id=\"enableTemplateEditor\" label=\"false\"></value></values></param><param id=\"enableFormEditor\" inputTypeId=\"0\"><values><value id=\"enableFormEditor\" label=\"false\"></value></values></param><param id=\"enableContentRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableContentRelationEditor\" label=\"false\"></value></values></param><param id=\"enableStructureRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableStructureRelationEditor\" label=\"true\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element></xs:schema>";
					
					Document newElementDocument = domBuilder.getDocument(elementString);
					
					List children = newElementDocument.getRootElement().elements("element");
					Iterator childrenIterator = children.iterator();
					while(childrenIterator.hasNext())
					{
						Element child = (Element)childrenIterator.next();
						allElements.add(child.createCopy());
					}
					
					String newValue = schemaValueDocument.asXML();
					
					String updateSQL = "UPDATE cmContentTypeDefinition SET schemaValue = ? WHERE contentTypeDefinitionId = " + id;
					PreparedStatement rowpstmt = conn.prepareStatement(updateSQL);
					rowpstmt.setString(1, newValue);
					rowpstmt.executeUpdate();
					rowpstmt.close();	
				}
			}
			else if(name.equalsIgnoreCase("HTMLTemplate"))
			{
				Logger.logInfo("Found HTMLTemplate...");
				if(schemaValue.indexOf("ComponentProperties") == -1)
				{
					DOMBuilder domBuilder = new DOMBuilder();
					Document schemaValueDocument = domBuilder.getDocument(schemaValue);

					Element allElements = getElementsElement(schemaValueDocument);
					//System.out.println("allElements:" + allElements.getName());

					String elementString = "<xs:schema elementFormDefault=\"qualified\" attributeFormDefault=\"unqualified\" version=\"2.1\" xmlns:xi=\"http://www.w3.org/2001/XInclude\" xmlns:xs=\"http://www.w3.org/2001/XMLSchema\"><xs:element name=\"ComponentProperties\" type=\"textarea\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined89\" label=\"ComponentProperties\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined40\" label=\"ComponentProperties\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined93\" label=\"normaltextarea\"></value></values></param><param id=\"width\" inputTypeId=\"0\"><values><value id=\"width\" label=\"700\"></value></values></param><param id=\"height\" inputTypeId=\"0\"><values><value id=\"height\" label=\"150\"></value></values></param><param id=\"enableWYSIWYG\" inputTypeId=\"0\"><values><value id=\"enableWYSIWYG\" label=\"false\"></value></values></param><param id=\"enableTemplateEditor\" inputTypeId=\"0\"><values><value id=\"enableTemplateEditor\" label=\"false\"></value></values></param><param id=\"enableFormEditor\" inputTypeId=\"0\"><values><value id=\"enableFormEditor\" label=\"false\"></value></values></param><param id=\"enableRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableRelationEditor\" label=\"false\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element><xs:element name=\"GroupName\" type=\"select\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined89\" label=\"Group Name\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined94\" label=\"The name of the group the component should be in\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined63\" label=\"normaltextfield\"></value></values></param><param id=\"values\" inputTypeId=\"1\"><values><value id=\"Basic Pages\" label=\"Basic Pages\"></value><value id=\"Single Content\" label=\"Single Content\"></value><value id=\"Content Iterators\" label=\"Content Iterators\"></value><value id=\"Navigation\" label=\"Navigation\"></value><value id=\"Layout\" label=\"Layout\"></value><value id=\"Templates\" label=\"Templates\"></value><value id=\"Other\" label=\"Other\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element></xs:schema>";
					
					Document newElementDocument = domBuilder.getDocument(elementString);
					
					List children = newElementDocument.getRootElement().elements("element");
					Iterator childrenIterator = children.iterator();
					while(childrenIterator.hasNext())
					{
						Element child = (Element)childrenIterator.next();
						allElements.add(child.createCopy());
					}
					
					String newValue = schemaValueDocument.asXML();
					Logger.logInfo("newValue: " + newValue);

					String updateSQL = "UPDATE cmContentTypeDefinition SET schemaValue = ? WHERE contentTypeDefinitionId = " + id;
					PreparedStatement rowpstmt = conn.prepareStatement(updateSQL);
					rowpstmt.setString(1, newValue);
					rowpstmt.executeUpdate();
					rowpstmt.close();	
				}
			}
			else if(name.equalsIgnoreCase("HTMLFormular"))
			{
				Logger.logInfo("Found HTMLFormular...");
				if(schemaValue.indexOf("<value id=\"enableWYSIWYG\" label=\"true\">") > -1)
				{
					String newValue = schemaValue.replaceAll("<value id=\"enableWYSIWYG\" label=\"true\">", "<value id=\"enableWYSIWYG\" label=\"false\">");
				    Logger.logInfo("newValue: " + newValue);

					String updateSQL = "UPDATE cmContentTypeDefinition SET schemaValue = ? WHERE contentTypeDefinitionId = " + id;
					PreparedStatement rowpstmt = conn.prepareStatement(updateSQL);
					rowpstmt.setString(1, newValue);
					rowpstmt.executeUpdate();
					rowpstmt.close();	
				}
			}
			else if(name.equalsIgnoreCase("TaskDefinition"))
			{
				hasTaskDefinition = true;
			}

			/*
			if(name.indexOf("Meta info") > -1)
			{
				if(schemaValue.indexOf("ComponentStructure") == -1)
				{
					int lastElementIndex = schemaValue.lastIndexOf("</xs:all></xs:complexType></xs:element>");
					if(lastElementIndex > -1)
					{ 
						String newValue = schemaValue.substring(0, lastElementIndex) + 
						"<xs:element name=\"ComponentStructure\" type=\"textarea\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined67\" label=\"ComponentStructure\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined38\" label=\"ComponentStructure\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined73\" label=\"normaltextarea\"></value></values></param><param id=\"width\" inputTypeId=\"0\"><values><value id=\"width\" label=\"700\"></value></values></param><param id=\"height\" inputTypeId=\"0\"><values><value id=\"height\" label=\"150\"></value></values></param><param id=\"enableWYSIWYG\" inputTypeId=\"0\"><values><value id=\"enableWYSIWYG\" label=\"false\"></value></values></param><param id=\"enableTemplateEditor\" inputTypeId=\"0\"><values><value id=\"enableTemplateEditor\" label=\"false\"></value></values></param><param id=\"enableFormEditor\" inputTypeId=\"0\"><values><value id=\"enableFormEditor\" label=\"false\"></value></values></param><param id=\"enableRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableRelationEditor\" label=\"false\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element>"
						 + schemaValue.substring(lastElementIndex);
	
						String updateSQL = "UPDATE cmContentTypeDefinition SET schemaValue = ? WHERE contentTypeDefinitionId = " + id;
						PreparedStatement rowpstmt = conn.prepareStatement(updateSQL);
						rowpstmt.setString(1, newValue);
						rowpstmt.executeUpdate();
						rowpstmt.close();	
					}
				}
			}
			else if(name.equalsIgnoreCase("Article"))
			{
				if(schemaValue.indexOf("RelatedAreas") == -1)
				{
					int lastElementIndex = schemaValue.lastIndexOf("</xs:all></xs:complexType></xs:element>");
					if(lastElementIndex > -1)
					{ 
						String newValue = schemaValue.substring(0, lastElementIndex) + 
						"<xs:element name=\"RelatedAreas\" type=\"textarea\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined93\" label=\"Related areas\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined30\" label=\"Points out related areas on the site\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined83\" label=\"normaltextfield\"></value></values></param><param id=\"width\" inputTypeId=\"0\"><values><value id=\"width\" label=\"700\"></value></values></param><param id=\"height\" inputTypeId=\"0\"><values><value id=\"height\" label=\"150\"></value></values></param><param id=\"enableWYSIWYG\" inputTypeId=\"0\"><values><value id=\"enableWYSIWYG\" label=\"false\"></value></values></param><param id=\"enableTemplateEditor\" inputTypeId=\"0\"><values><value id=\"enableTemplateEditor\" label=\"false\"></value></values></param><param id=\"enableFormEditor\" inputTypeId=\"0\"><values><value id=\"enableFormEditor\" label=\"false\"></value></values></param><param id=\"enableContentRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableContentRelationEditor\" label=\"false\"></value></values></param><param id=\"enableStructureRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableStructureRelationEditor\" label=\"true\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element>"
						 + schemaValue.substring(lastElementIndex);
	
						String updateSQL = "UPDATE cmContentTypeDefinition SET schemaValue = ? WHERE contentTypeDefinitionId = " + id;
						PreparedStatement rowpstmt = conn.prepareStatement(updateSQL);
						rowpstmt.setString(1, newValue);
						rowpstmt.executeUpdate();
						rowpstmt.close();	
					}
				}
			}
			else if(name.equalsIgnoreCase("HTMLTemplate"))
			{
				Logger.logInfo("Found HTMLTemplate...");
				if(schemaValue.indexOf("ComponentProperties") == -1)
				{
					Logger.logInfo("ComponentProperties was not found in: " + schemaValue);

					int lastElementIndex = schemaValue.lastIndexOf("</xs:all></xs:complexType></xs:element>");
					Logger.logInfo("lastElementIndex: " + lastElementIndex);
					if(lastElementIndex > -1)
					{ 
						String newValue = schemaValue.substring(0, lastElementIndex) + 
						"<xs:element name=\"ComponentProperties\" type=\"textarea\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined89\" label=\"ComponentProperties\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined40\" label=\"ComponentProperties\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined93\" label=\"normaltextarea\"></value></values></param><param id=\"width\" inputTypeId=\"0\"><values><value id=\"width\" label=\"700\"></value></values></param><param id=\"height\" inputTypeId=\"0\"><values><value id=\"height\" label=\"150\"></value></values></param><param id=\"enableWYSIWYG\" inputTypeId=\"0\"><values><value id=\"enableWYSIWYG\" label=\"false\"></value></values></param><param id=\"enableTemplateEditor\" inputTypeId=\"0\"><values><value id=\"enableTemplateEditor\" label=\"false\"></value></values></param><param id=\"enableFormEditor\" inputTypeId=\"0\"><values><value id=\"enableFormEditor\" label=\"false\"></value></values></param><param id=\"enableRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableRelationEditor\" label=\"false\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element><xs:element name=\"GroupName\" type=\"select\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined89\" label=\"Group Name\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined94\" label=\"The name of the group the component should be in\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined63\" label=\"normaltextfield\"></value></values></param><param id=\"values\" inputTypeId=\"1\"><values><value id=\"Basic Pages\" label=\"Basic Pages\"></value><value id=\"Single Content\" label=\"Single Content\"></value><value id=\"Content Iterators\" label=\"Content Iterators\"></value><value id=\"Navigation\" label=\"Navigation\"></value><value id=\"Layout\" label=\"Layout\"></value><value id=\"Templates\" label=\"Templates\"></value><value id=\"Other\" label=\"Other\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element>"
						 + schemaValue.substring(lastElementIndex);
						
						Logger.logInfo("newValue: " + newValue);

						String updateSQL = "UPDATE cmContentTypeDefinition SET schemaValue = ? WHERE contentTypeDefinitionId = " + id;
						PreparedStatement rowpstmt = conn.prepareStatement(updateSQL);
						rowpstmt.setString(1, newValue);
						rowpstmt.executeUpdate();
						rowpstmt.close();	
					}
				}
			}
			else if(name.equalsIgnoreCase("TaskDefinition"))
			{
				hasTaskDefinition = true;
			}
			*/
		}
		
			
		if(!hasTaskDefinition)
		{
			String newValue = "<xs:schema attributeFormDefault=\"unqualified\" elementFormDefault=\"qualified\" version=\"2.0\" xmlns:xi=\"http://www.w3.org/2001/XInclude\" xmlns:xs=\"http://www.w3.org/2001/XMLSchema\"><xs:simpleType name=\"textarea\"><xs:restriction base=\"xs:string\"><xs:maxLength value=\"100\"></xs:maxLength></xs:restriction></xs:simpleType><xs:simpleType name=\"radiobutton\"><xs:restriction base=\"xs:string\"><xs:maxLength value=\"100\"></xs:maxLength></xs:restriction></xs:simpleType><xs:simpleType name=\"checkbox\"><xs:restriction base=\"xs:string\"><xs:maxLength value=\"100\"></xs:maxLength></xs:restriction></xs:simpleType><xs:simpleType name=\"select\"><xs:restriction base=\"xs:string\"><xs:maxLength value=\"100\"></xs:maxLength></xs:restriction></xs:simpleType><xs:simpleType name=\"textfield\"><xs:restriction base=\"xs:string\"><xs:maxLength value=\"100\"></xs:maxLength></xs:restriction></xs:simpleType><xs:complexType name=\"Content\"><xs:all><xs:element name=\"Attributes\"><xs:complexType><xs:all><xs:element name=\"UserInputHTML\" type=\"textarea\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined64\" label=\"UserInputHTML\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined98\" label=\"UserInputHTML\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined26\" label=\"normaltextarea\"></value></values></param><param id=\"width\" inputTypeId=\"0\"><values><value id=\"width\" label=\"700\"></value></values></param><param id=\"height\" inputTypeId=\"0\"><values><value id=\"height\" label=\"150\"></value></values></param><param id=\"enableWYSIWYG\" inputTypeId=\"0\"><values><value id=\"enableWYSIWYG\" label=\"false\"></value></values></param><param id=\"enableTemplateEditor\" inputTypeId=\"0\"><values><value id=\"enableTemplateEditor\" label=\"false\"></value></values></param><param id=\"enableFormEditor\" inputTypeId=\"0\"><values><value id=\"enableFormEditor\" label=\"false\"></value></values></param><param id=\"enableRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableRelationEditor\" label=\"false\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element><xs:element name=\"ScriptCode\" type=\"textarea\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined22\" label=\"ScriptCode\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined90\" label=\"The code\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined99\" label=\"normaltextarea\"></value></values></param><param id=\"width\" inputTypeId=\"0\"><values><value id=\"width\" label=\"700\"></value></values></param><param id=\"height\" inputTypeId=\"0\"><values><value id=\"height\" label=\"600\"></value></values></param><param id=\"enableWYSIWYG\" inputTypeId=\"0\"><values><value id=\"enableWYSIWYG\" label=\"false\"></value></values></param><param id=\"enableTemplateEditor\" inputTypeId=\"0\"><values><value id=\"enableTemplateEditor\" label=\"false\"></value></values></param><param id=\"enableFormEditor\" inputTypeId=\"0\"><values><value id=\"enableFormEditor\" label=\"false\"></value></values></param><param id=\"enableRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableRelationEditor\" label=\"false\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element><xs:element name=\"UserOutputHTML\" type=\"textarea\"><xs:annotation><xs:appinfo><params><param id=\"title\" inputTypeId=\"0\"><values><value id=\"undefined63\" label=\"UserOutputHTML\"></value></values></param><param id=\"description\" inputTypeId=\"0\"><values><value id=\"undefined22\" label=\"UserOutputHTML\"></value></values></param><param id=\"class\" inputTypeId=\"0\"><values><value id=\"undefined28\" label=\"normaltextarea\"></value></values></param><param id=\"width\" inputTypeId=\"0\"><values><value id=\"width\" label=\"700\"></value></values></param><param id=\"height\" inputTypeId=\"0\"><values><value id=\"height\" label=\"150\"></value></values></param><param id=\"enableWYSIWYG\" inputTypeId=\"0\"><values><value id=\"enableWYSIWYG\" label=\"false\"></value></values></param><param id=\"enableTemplateEditor\" inputTypeId=\"0\"><values><value id=\"enableTemplateEditor\" label=\"false\"></value></values></param><param id=\"enableFormEditor\" inputTypeId=\"0\"><values><value id=\"enableFormEditor\" label=\"false\"></value></values></param><param id=\"enableRelationEditor\" inputTypeId=\"0\"><values><value id=\"enableRelationEditor\" label=\"false\"></value></values></param></params></xs:appinfo></xs:annotation></xs:element></xs:all></xs:complexType></xs:element></xs:all></xs:complexType></xs:schema>";
			String insertSQL = "INSERT INTO cmContentTypeDefinition (schemaValue, name) VALUES (?,?);";
			PreparedStatement rowpstmt = conn.prepareStatement(insertSQL);
			rowpstmt.setString(1, newValue);
			rowpstmt.setString(2, "TaskDefinition");
			rowpstmt.executeUpdate();
			rowpstmt.close();	
		}
		
		pstmt.close();
	}
	
	
	private Element getElementsElement(Document schemaValueDocument)
	{
		Element elementsElement = null;
		
		Element schema = schemaValueDocument.getRootElement();
		//System.out.println("schema:" + schema.getName());

		List complexTypes = schemaValueDocument.getRootElement().elements("complexType");
		//System.out.println("complexTypes:" + complexTypes.size());
		Iterator complexTypesIterator = complexTypes.iterator();
		while(complexTypesIterator.hasNext())
		{
			Element complexType = (Element)complexTypesIterator.next();
			//System.out.println("complexType:" + complexType.getName());
			
			List allList = complexType.elements("all");
			//System.out.println("allList:" + allList.size());
			Iterator allIterator = allList.iterator();
			while(allIterator.hasNext())
			{
				Element all = (Element)allIterator.next();
				//System.out.println("all:" + all.getName());
				
				List elementList = all.elements("element");
				//System.out.println("elementList:" + elementList.size());
				Iterator elementListIterator = elementList.iterator();
				while(elementListIterator.hasNext())
				{
					Element element = (Element)elementListIterator.next();
					//System.out.println("element:" + element.getName() + ":" + element.attributeValue("name"));
				
					List complexType2 = element.elements("complexType");
					//System.out.println("complexType2:" + complexType2.size());
					Iterator complexType2Iterator = complexType2.iterator();
					while(complexType2Iterator.hasNext())
					{
						Element complexType2Element = (Element)complexType2Iterator.next();
						//System.out.println("complexType2Element:" + complexType2Element.getName());
						
						List all2 = complexType2Element.elements("all");
						//System.out.println("all2:" + all2.size());
						Iterator all2Iterator = all2.iterator();
						while(all2Iterator.hasNext())
						{
							Element all2Element = (Element)all2Iterator.next();
							//System.out.println("all2Element:" + all2Element.getName());
							
							elementsElement = all2Element;
							/*
							List element2 = all2Element.elements("element");
							System.out.println("element2:" + element2.size());
							Iterator element2Iterator = element2.iterator();
							while(element2Iterator.hasNext())
							{
								Element element2Element = (Element)element2Iterator.next();
								System.out.println("element2Element:" + element2Element.getName());
							}
							*/
						}
					}
				}
			}
		}
		
		return elementsElement;
	}
	
	private int getMaxContentId(Connection conn) throws Exception
	{
		String sql = "SELECT max(contentId) as contentId FROM cmContent";
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();

		while(rs.next())
		{
			return rs.getInt("contentId");
		}
		
		return -1;
	}
	
	private void updateBaseTasks(Connection conn) throws Exception
	{
		String sql = "SELECT * FROM cmContent WHERE name = 'Export Contents Task' OR name = 'Import Contents Task'";
		Logger.logInfo("upgrading system tasks...");
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
	
		boolean hasSystemTasks = false;
		
		while(rs.next())
		{
			hasSystemTasks = true;
		}
	
		if(!hasSystemTasks)
		{
			String command = "INSERT INTO cmContent (name, publishDateTime, expireDateTime, contentTypeDefinitionId, parentContentId, systemUserId, repositoryId, isBranch) VALUES ('Export Contents Task','2003-08-18 13:03:00','2013-08-18 13:03:00','10','30','1','3','0');";
			//Logger.logInfo("Command: " + command);
			PreparedStatement insertPstmt = conn.prepareStatement(command);
			insertPstmt.execute();  
			insertPstmt.close();
			
			int exportContentId = getMaxContentId(conn);
			
			command = "INSERT INTO cmContent (name, publishDateTime, expireDateTime, contentTypeDefinitionId, parentContentId, systemUserId, repositoryId, isBranch) VALUES ('Import Contents Task','2003-08-18 13:03:00','2013-08-18 13:03:00','10','30','1','3','0');";
			//Logger.logInfo("Command: " + command);
			insertPstmt = conn.prepareStatement(command);
			insertPstmt.execute();  
			insertPstmt.close();
			
			int importContentId = getMaxContentId(conn);

			
			Logger.logInfo("upgrading system tasks...");		
			try
			{
				FileInputStream fis = new FileInputStream("infoglue_system_tasks_1_2.sql");
				StringBuffer sb = new StringBuffer();
				int c;
				while((c = fis.read()) != -1)
				{
					char character = (char)c;
					sb.append(character);
				}
				String script = sb.toString();
				script = script.replaceAll("exportContentId", "" + exportContentId);
				script = script.replaceAll("importContentId", "" + importContentId);
				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++)
				{
					command = commands[i];
					
					insertPstmt = conn.prepareStatement(command.trim());
					insertPstmt.execute();  
					insertPstmt.close();
				}
					
			}
			catch(Exception e)
			{
				Logger.logInfo("Error: " + e);
			}
		}
	
		pstmt.close();
	}
	
	private void updateSiteNodeTypesDefinitions(Connection conn) throws Exception
	{
		String sql = "SELECT * FROM cmSiteNodeTypeDefinition";
		Logger.logInfo("upgrading cmSiteNodeTypeDefinition...");
		PreparedStatement pstmt = conn.prepareStatement(sql);
		ResultSet rs = pstmt.executeQuery();
		
		boolean hasComponentPageDefinition = false;
		
		while(rs.next())
		{
			int id = rs.getInt("siteNodeTypeDefinitionId");
			String invokerClassName = rs.getString("invokerClassName");

			if(invokerClassName.equalsIgnoreCase("org.infoglue.cms.invokers.ComponentBasedHTMLPageInvoker"))
			{
				hasComponentPageDefinition = true;
			}
		}
		
		if(!hasComponentPageDefinition)
		{
			String insertSQL = "INSERT INTO cmSiteNodeTypeDefinition (invokerClassName, name, description) VALUES (?,?,?);";
			PreparedStatement rowpstmt = conn.prepareStatement(insertSQL);
			rowpstmt.setString(1, "org.infoglue.cms.invokers.ComponentBasedHTMLPageInvoker");
			rowpstmt.setString(2, "ComponentPage");
			rowpstmt.setString(3, "The new component type page");
			rowpstmt.executeUpdate();
			rowpstmt.close();	
		}
		
		pstmt.close();
	}
	
	public String getPreviousVersion(Connection conn, String databaseName)
	{
	    String previousVersion = "2.9.8.7";

	    try
	    {
	        String sql = "SELECT * FROM cmSiteNodeVersion";
	        if(databaseName.equalsIgnoreCase("Oracle") || databaseName.equalsIgnoreCase("DB2"))
	            sql = "SELECT * FROM cmSiNoVer";
	        
	        PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			rs.next();
			
			rs.getString("forceProtocolChange"); //If this throws exception then it's older than 2.3
			return "2.9.8.7";
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
		    previousVersion = "2.9.7.1";
	    }
	    
	    try
	    {
	        String sql = "SELECT * FROM cmFormEntryAsset";
	        
	        PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			rs.next();
			
			if(rs.getMetaData().getColumnCount() > 0) //If this is above 0 then the table is there and the version is 1.2.
			{
				return "2.9.7.1";
			}
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
		    previousVersion = "2.9";
	    }

	    try
	    {
	        String sql = "SELECT * FROM cmSubscription";
	        
	        PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			rs.next();
			
			if(rs.getMetaData().getColumnCount() > 0) //If this is above 0 then the table is there and the version is 1.2.
			{
				return "2.9";
			}
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
		    previousVersion = "2.8";
	    }

	    try
	    {
	        String sql = "SELECT * FROM cmSiteNodeVersion";
	        if(databaseName.equalsIgnoreCase("Oracle") || databaseName.equalsIgnoreCase("DB2"))
	            sql = "SELECT * FROM cmSiNoVer";
	        
	        PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			rs.next();
			
			rs.getString("pageCacheTimeout"); //If this throws exception then it's older than 2.3
			return "2.8";
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
		    previousVersion = "2.3";
	    }

	    try
	    {
	        String sql = "SELECT * FROM cmSiteNodeVersion";
	        if(databaseName.equalsIgnoreCase("Oracle") || databaseName.equalsIgnoreCase("DB2"))
	            sql = "SELECT * FROM cmSiNoVer";
	        
	        PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			rs.next();
			
			rs.getString("disableLanguages"); //If this throws exception then it's older than 2.3
			return "2.3";
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
		    previousVersion = "2.1";
	    }

	    try
	    {
	        String sql = "SELECT * FROM cmSiteNodeVersion";
	        if(databaseName.equalsIgnoreCase("Oracle") || databaseName.equalsIgnoreCase("DB2"))
	            sql = "SELECT * FROM cmSiNoVer";
	        
	        PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			rs.next();
			
			rs.getString("pageCacheKey"); //If this throws exception then it's older than 2.1
			return "2.1";
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
		    previousVersion = "2.0";
	    }

	    try
	    {
			String sql = "SELECT * FROM cmCategory";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			
			if(rs.getMetaData().getColumnCount() > 0) //If this is above 0 then the table is there and the version is 1.3.
			    return "2.0";			
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
		    previousVersion = "1.3.2";
	    }

	    try
	    {
			String sql = "SELECT * FROM cmRepositoryLanguage";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			rs.next();
			
			rs.getString("sortOrder"); //If this throws exception then it's 1.3
			return "1.3.2";
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
		    previousVersion = "1.3";
	    }

	    try
	    {
			String sql = "SELECT * FROM cmConsequence";
			PreparedStatement pstmt = conn.prepareStatement(sql);
			ResultSet rs = pstmt.executeQuery();
			
			if(rs.getMetaData().getColumnCount() > 0) //If this is above 0 then the table is there and the version is 1.2.
			{
			    return previousVersion = "1.2";
			}
	    }
	    catch(Exception e)
	    {
	        e.printStackTrace();
	        previousVersion = "1.0";
	    }

		return previousVersion;
	}
}