/*******************************************************************************
 * Copyright (c) 2004 Actuate Corporation.
 * All rights reserved. This program and the accompanying materials
 * are made available under the terms of the Eclipse Public License v1.0
 * which accompanies this distribution, and is available at
 * http://www.eclipse.org/legal/epl-v10.html
 *
 * Contributors:
 *  Actuate Corporation  - initial API and implementation
 *******************************************************************************/

package testutil;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileReader;
import java.io.FilenameFilter;
import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URI;
import java.net.URISyntaxException;
import java.net.URL;
import java.net.URLClassLoader;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.DriverPropertyInfo;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.eclipse.datatools.connectivity.oda.OdaException;
import org.eclipse.datatools.connectivity.oda.util.manifest.ExtensionManifest;
import org.eclipse.datatools.connectivity.oda.util.manifest.ManifestExplorer;

/**
 * Used to 
 * 		create table
 * 		create stored procedure
 * 		insert data into table
 * 		drop table
 * 		drop stored procedure
 * Property: 
 * 		DTETest.driver
 * 		DTETest.url
 * 		DTETest.database
 * 		DTETest.user
 * 		DTETest.password
 * 
 * 		DTETest.otherDB (whether derby is not used)
 */
public class JDBCDataSourceUtil
{
	private Connection conn;
	private PreparedStatement prepStmt;
	private List tableNameList = new ArrayList( );
	private List procedureNameList = new ArrayList( );

	/**
	 * Constructor
	 * 
	 * @throws Exception
	 */
	JDBCDataSourceUtil( ) throws Exception
	{
		conn = createDBConnection( );

		System.out.println( "Test against JDBC database: url="
				+ getURL( ) + " user=" + getUser( ) + " driverClass="
				+ getDriverClassName( ) );
	}

	/**
	 * Create test table according to the passed arguments
	 * 
	 * @param tableName
	 * @param metaInfo
	 * @param dropTable
	 * @throws SQLException
	 */
	public void createTable( String tableName, String createSql,
			boolean dropTable ) throws SQLException
	{
		if ( tableName != null )
		{
			if ( dropTable == true )
			{
				dropTable( tableName );
			}
			statementExecute( createSql );
			tableNameList.add( tableName );
		}
	}

	/**
	 * Create test procedure according to the passed arguments
	 * 
	 * @param proName
	 * @param metaInfo
	 * @param dropProc
	 * @throws SQLException
	 */
	public void createStoredProcedure( String proName, String createSql,
			boolean dropProc ) throws SQLException
	{
		if ( proName != null )
		{
			if ( dropProc == true )
			{
				dropStoredProcedure( proName );
			}
			statementExecute( createSql );
			procedureNameList.add( proName );
		}
	}

	/**
	 * Populate data for test table
	 * 
	 * @param testTableName
	 * @param testTableDataFile
	 * @throws SQLException
	 * @throws IOException
	 */
	public void populateTable( String testTableName, File testTableDataFile )
			throws SQLException, IOException
	{
		FileReader inputFile = new FileReader( testTableDataFile );
		BufferedReader lineReader = new BufferedReader( inputFile );
		//skip first two lines which store metadata information
		lineReader.readLine( );
		String metaData = lineReader.readLine( );
		String str;

		while ( ( str = lineReader.readLine( ) ) != null )
		{
			// skip comment line
			if ( str.charAt( 0 ) == '#' )
				continue;
			String sql = "insert into "
					+ testTableName + " values(" + str + ")";

			// blob support in sql statement
			boolean isBlob = false;
			String[] dataType = metaData.split( "," );
			for ( int i = 0; i < dataType.length; i++ )
			{
				dataType[i] = dataType[i].trim( );
				if ( dataType[i].toUpperCase( ).equals( "BLOB" ) )
				{
					isBlob = true;
					break;
				}
			}

			if ( isBlob == false )
			{
				statementExecute( sql );
			}
			else
			{
				Object[] ob = getPreparedData( testTableName, dataType, str );
				statementExecute( (String) ob[0], (Object[]) ob[1] );
			}
		}
	}

	/**
	 * Used to insert Blob data
	 * 
	 * @return Object[], sql statement and data object array
	 */
	private Object[] getPreparedData( String testTableName, String[] dataType,
			String dataStr )
	{
		String replaceStr = "";
		for ( int i = 0; i < dataType.length; i++ )
		{
			replaceStr += "?";
			if ( i < dataType.length - 1 )
				replaceStr += ",";
		}
		String insertSql = "insert into "
				+ testTableName + " values(" + replaceStr + ")";
		Object[] value = new Object[dataType.length];

		String[] data = dataStr.split( "," );
		for ( int i = 0; i < dataType.length; i++ )
		{
			data[i] = data[i].trim( );
			
			if ( dataType[i].toUpperCase( ).equals( "INT" ) )
			{
				value[i] = Integer.valueOf( data[i] );
			}
			else if ( dataType[i].toUpperCase( ).equals( "CLOB" ) )
			{
				value[i] = data[i];
			}
			else if ( dataType[i].toUpperCase( ).equals( "BLOB" ) )
			{
				value[i] = data[i].toString( ).getBytes( );
			}
			else
			{
				throw new IllegalArgumentException( "" );
			}
		}

		return new Object[]{
				insertSql, value
		};
	}
	
	/**
	 * Drop test table
	 * 
	 * @param tableName
	 * @throws SQLException
	 */
	public void dropTable( String tableName )
	{
		String sql = "drop table " + tableName;
		try
		{
			statementExecute( sql );
			tableNameList.remove( tableName );
		}
		catch ( SQLException e )
		{
			// Assume table does not exist
		}
	}

	/**
	 * Drop test procedure
	 * 
	 * @param proName
	 * @throws SQLException
	 */
	public void dropStoredProcedure( String proName )
	{
		String sql = "drop procedure " + proName;
		try
		{
			statementExecute( sql );
			procedureNameList.remove( proName );
		}
		catch ( SQLException e )
		{
			// Assume table does not exist
		}
	}

	/**
	 * Execute sql statement
	 * 
	 * @param exeStr
	 * @throws SQLException
	 */
	private void statementExecute( String exeStr ) throws SQLException
	{
		prepStmt = conn.prepareStatement( exeStr );
		prepStmt.execute( );
	}

	/**
	 * Execute sql statement, to support BLOB insert
	 * 
	 * @param exeStr
	 * @throws SQLException
	 */
	private void statementExecute( String exeStr, Object[] value )
			throws SQLException
	{
		prepStmt = conn.prepareStatement( exeStr );
		
		for ( int i = 0; i < value.length; i++ )
		{
			int pos = i + 1;
			if ( value[i] instanceof Integer )
			{
				prepStmt.setInt( pos, ( (Integer) value[i] ).intValue( ) );
			}
			else if ( value[i] instanceof String )
			{
				prepStmt.setString( pos, (String) value[i] );
			}
			else if ( value[i] instanceof byte[] )
			{
				prepStmt.setBytes( pos, (byte[]) value[i] );
			}
			else
			{
				throw new IllegalArgumentException( "not support data type"
						+ value[i].getClass( ).getName( ) );
			}
		}
		
		prepStmt.execute( );
	}

	/**
	 * Close test table
	 * 
	 * @param droptable
	 * @throws SQLException
	 */
	public void close( boolean droptable ) throws SQLException
	{
		if ( conn != null )
		{
			if ( prepStmt != null )
			{
				if ( droptable == true )
				{
					for ( int i = 0; i < tableNameList.size( ); i++ )
					{
						prepStmt = conn.prepareStatement( "drop table "
								+ tableNameList.get( i ) );
						prepStmt.executeUpdate( );
					}
				}
				prepStmt.close( );
			}
			conn.close( );
		}
	}

	/**
	 * Create db connection
	 * 
	 * @return
	 * @throws Exception
	 */
	private Connection createDBConnection( ) throws Exception
	{
		if ( getURL( ).startsWith( "jdbc:derby" ) )
			return createDerbyConnection( );

		loadJdbcDrivers( );
		
		java.util.Properties props = new java.util.Properties( );
		props.put( "user", getUser( ) );
		props.put( "password", getPassword( ) );		
		return DriverManager.getConnection( getURL( ), props );
	}
	
	/**
	 * Create derby db connection
	 * 
	 * @return
	 * @throws Exception
	 */
	private Connection createDerbyConnection( ) throws Exception
	{
		Class.forName( getDriverClassName( ) );
		
		String nsURL = getURL( ) + ";create=true";		
		java.util.Properties props = new java.util.Properties( );
		props.put( "user", getUser( ) );
		props.put( "password", getPassword( ) );	
		return DriverManager.getConnection( nsURL, props );
	}
	
	/**
	 * Return driver class name for test table
	 * 
	 * @return
	 */
	static String getDriverClassName( )
	{
		String clsName = System.getProperty( "DTETest.driver" );
		if ( clsName == null )
			clsName = "org.apache.derby.jdbc.EmbeddedDriver";
		return clsName;
	}

	/**
	 * Return URL for test table
	 * 
	 * @return
	 */
	static String getURL( )
	{
		String url = System.getProperty( "DTETest.url" );
		if ( url != null )
			return url;
		else
			return "jdbc:derby:" + getDatabase( );
	}
	
	/**
	 * Return database name for test table
	 * 
	 * @return
	 */
	private static String getDatabase( )
	{
		String database = System.getProperty( "DTETest.database" );
		if ( database != null )
			return database;
		else
			return "DTETest";
	}
	
	/**
	 * Return user for test table
	 * 
	 * @return
	 */
	static String getUser( )
	{
		String user = System.getProperty( "DTETest.user" );
		if ( user != null )
			return user;
		else
			return "user";
	}

	/**
	 * Return password for test table
	 * 
	 * @return
	 */
	static String getPassword( )
	{
		String pwd = System.getProperty( "DTETest.password" );
		if ( pwd != null )
			return pwd;
		else
			return "password";
	}

	/**
	 * Load jdbc drivers
	 * 
	 * @throws Exception
	 */
	private void loadJdbcDrivers() throws Exception
	{
		try
		{
			if ( "true".equals( System.getProperty( "DTETest.otherDB" ) ) )
			{
				File driverHomeDir = getDriverHomeDir( );
				URL[] urlList = getDriverFileURLs( driverHomeDir );
				URLClassLoader urlClassLoader = new URLClassLoader( urlList,
						this.getClass( ).getClassLoader( ) );
				Class c = urlClassLoader.loadClass( getDriverClassName( ) );
				DriverExt driverExt = new DriverExt( (Driver) c.newInstance( ) );
				DriverManager.registerDriver( driverExt );
			}
			else
			{
				Class.forName( getDriverClassName( ) );
			}
		}
		catch ( ClassNotFoundException e )
		{
			System.out.println( "Due to licence issue, Driver lib for "
					+ getDriverClassName( ) + " can not be " );
			System.out.println( "checked into Eclipse CVS. Please manually download this driver lib and put it " );
			System.out.println( "into test\\plugins\\org.eclipse.birt.report.data.oda.jdbc\\drivers directory." );
			throw e;
		}		
	}
	
	/**
	 * Gets driver home dir
	 */
	private File getDriverHomeDir( ) throws OdaException, IOException
	{
		File driverHomeDir = null;
		ExtensionManifest extMF = ManifestExplorer.getInstance( )
				.getExtensionManifest( "org.eclipse.birt.report.data.oda.jdbc" );
		if ( extMF != null )
		{
			URL url = extMF.getRuntimeInterface( ).getLibraryLocation( );
			try
			{
				URI uri = new URI( url.toString( ) );
				driverHomeDir = new File( uri.getPath( ), "drivers" );
			}
			catch ( URISyntaxException e )
			{
				driverHomeDir = new File( url.getFile( ), "drivers" );
			}
		}
		return driverHomeDir;
	}
	
	/**
	 * Gets URL array of driver files
	 */
	private URL[] getDriverFileURLs( File driverHomeDir )
			throws MalformedURLException
	{
		String files[] = driverHomeDir.list( new FilenameFilter( ) {

			public boolean accept( File dir, String name )
			{
				return isDriverFile( name );
			}
		} );

		URL[] urls = new URL[files.length];
		for ( int i = 0; i < files.length; i++ )
		{
			urls[i] = new URL( "file", null, ( new File( driverHomeDir,
					files[i] ) ).getAbsolutePath( ) );
		}
		return urls;
	}

	/**
	 * Checks whether the file is a jar file
	 */
	private boolean isDriverFile( String fileName )
	{
		String lcName = fileName.toLowerCase( );
		return lcName.endsWith( ".jar" ) || lcName.endsWith( ".zip" );
	}

	/**
	 * Support dynamically load JDBC drivers.
	 */
	private class DriverExt implements Driver
	{
		private Driver driver;

		DriverExt( Driver d )
		{
			this.driver = d;
		}

		public boolean acceptsURL( String u ) throws SQLException
		{
			return this.driver.acceptsURL( u );
		}

		public Connection connect( String u, Properties p ) throws SQLException
		{
			return this.driver.connect( u, p );
		}

		public int getMajorVersion( )
		{
			return this.driver.getMajorVersion( );
		}

		public int getMinorVersion( )
		{
			return this.driver.getMinorVersion( );
		}

		public DriverPropertyInfo[] getPropertyInfo( String u, Properties p )
				throws SQLException
		{
			return this.driver.getPropertyInfo( u, p );
		}

		public boolean jdbcCompliant( )
		{
			return this.driver.jdbcCompliant( );
		}
	}
	
}