/*
** Copyright (c) 1998 by Timothy Gerard Endres
** <mailto:time@ice.com>  
** 
** This program is free software.
** 
** You may redistribute it and/or modify it under the terms of the GNU
** General Public License as published by the Free Software Foundation.
** Version 2 of the license should be included with this distribution in
** the file LICENSE, as well as License.html. If the license is not
** included	with this distribution, you may find a copy at the FSF web
** site at 'www.gnu.org' or 'www.fsf.org', or you may write to the
** Free Software Foundation, 675 Mass Ave, Cambridge, MA 02139 USA.
**
** THIS SOFTWARE IS PROVIDED AS-IS WITHOUT WARRANTY OF ANY KIND,
** NOT EVEN THE IMPLIED WARRANTY OF MERCHANTABILITY. THE AUTHOR
** OF THIS SOFTWARE, ASSUMES _NO_ RESPONSIBILITY FOR ANY
** CONSEQUENCE RESULTING FROM THE USE, MODIFICATION, OR
** REDISTRIBUTION OF THIS SOFTWARE. 
** 
*/

package com.ice.sqlclient;

import java.sql.*;
import java.util.*;
import java.net.URL;


public
class		MySQLClient
extends		SQLClientHandler
	{
	private static final String		defDriverPrefix = "mysql";
	private static final String		defDriverClass = "org.gjt.mm.mysql";

	private Class		driverClass;
	private Connection	connection;

	private String		prefix;
	private String		port;
	private String		host;
	private String		name;
	private String		pass;
	private String		database;

	private String[]		dbListCache;
	private String[]		tableListCache;
	private JDBCAdapter		tableAdapterCache;
	private Hashtable		columnAdapterCache;


	public
	MySQLClient( String name )
			throws ClassNotFoundException
		{
		this( name,
				MySQLClient.defDriverPrefix,
				MySQLClient.defDriverClass );
		}

	public
	MySQLClient( String name, String driverPrefix, String driverClassName )
			throws ClassNotFoundException
		{
		super( name );

		this.host = null;
		this.port = null;
		this.name = null;
		this.pass = null;
		this.database = null;
		this.connection = null;

		this.tableListCache = null;
		this.tableAdapterCache = null;
		this.columnAdapterCache = new Hashtable();

		this.prefix = driverPrefix;

		try {
			this.driverClass = Class.forName( driverClassName );
			}
		catch ( ClassNotFoundException ex )
			{
			this.driverClass = null;
			throw ex;
			}
		}

	public void
	finalize()
		{
		this.closeConnection();
		}

	public void
	flushAllCaches()
		{
		this.dbListCache = null;
		this.flushTableCaches();
		}

	public void
	flushTableCaches()
		{
		this.tableListCache = null;
		this.tableAdapterCache = null;
		this.columnAdapterCache = new Hashtable();
		}

	public void
	closeConnection()
		{
		if ( this.isOpen() && this.connection != null )
			{
			try { this.connection.close(); }
			catch ( SQLException ex )
				{
				System.err.println
					( "ERROR closing MySQL connection: "
						+ ex.getMessage() );
				}
			}

		this.isOpen = false;
		this.tableListCache = null;
		this.tableAdapterCache = null;
		}

	public void
	setConnectInfo(
			String host, String port,
			String name, String pass, String database )
		{
		this.host = host;
		this.port = port;
		this.name = name;
		this.pass = pass;
		this.database = database;
		}

	public void
	openConnection()
		throws SQLException
		{
		StringBuffer urlBuf = new StringBuffer();

		urlBuf.append( "jdbc:" );
		urlBuf.append( this.prefix );
		urlBuf.append( "://" );

		urlBuf.append( this.host );
		if ( this.port != null )
			{
			urlBuf.append( ":" );
			urlBuf.append( this.port );
			}

		urlBuf.append( "/" );
		urlBuf.append( this.database );

		java.util.Properties info =
			new java.util.Properties();

		if ( this.name != null )
			{
			info.put( "user", this.name );
			}

		if ( this.pass != null )
			{
			info.put( "password", this.pass );
			}

		this.openConnection( urlBuf.toString(), info );
		}

	private void
	openConnection( String url, Properties info )
		throws SQLException
		{
		if ( this.isOpen() )
			throw new SQLException
				( "connection is already open" );

		try {
			//
			// The url of the database we wish to connect to
			//     <jdbc:mysql://host:port/database>
			//
			Driver driver = null;

			Enumeration drivers = DriverManager.getDrivers();
			for ( ; drivers.hasMoreElements() ; )
				{
				Driver d = (Driver) drivers.nextElement();
				if ( d.getClass() == this.driverClass )
					{
					driver = d;
					break;
					}
				}

			//
			// Set up a connection, as user and password (both as
			// clear text here - the password is encrypted by the
			// driver
			// An empty username is changed to "nobody" in mysql
			//
			if ( driver != null )
				{
				this.connection = driver.connect( url, info );
				}

			if ( this.connection == null )
				{
				this.connection =
					DriverManager.getConnection( url, info );
				}

			this.isOpen = true;
			}
		catch ( SQLException ex )
			{
			this.connection = null;
			throw ex;
			}
		}

	public DatabaseMetaData
	getMetaData()
		throws SQLException
		{
		return this.connection.getMetaData();
		}

	public void
	setCurrentDatabase( String dbName )
			throws SQLException
		{
		// REVIEW This is a crappy situation, but I am not sure
		//        what to do or who to blame. The jms driver is
		//        apparently smartly processing the command and
		//        recognizing the "use " construct, and then
		//        doing "the right thing" to set the current db.
		//        The twz driver, on the other hand, does not
		//        recognize "use ", and uses the setCatalog()
		//        method of Connection to accomplish this. For
		//        now, we count on the "use" statement failing
		//        under twz, which causes us to setCatalog().
		//
		try {
			this.performSQLCommand( "use " + dbName );
			}
		catch ( SQLException ex )
			{
			this.connection.setCatalog( dbName );
			}
		}

	public Statement
	createStatement()
		throws SQLException
		{
		return this.connection.createStatement();
		}

	public PreparedStatement
	createPreparedStatement( String statement )
		throws SQLException
		{
		return this.connection.prepareStatement( statement );
		}

	public boolean
	performSQLCommand( String command )
			throws SQLException
		{
		// Create statement
		Statement stmt = this.connection.createStatement();

		// Execute query
		boolean result = stmt.execute( command );
		//
		// UNDONE Since we close the statement, the client
		//        has no way to get the results. That makes
		//        this useless for commands that return results.
		//
		// Close statement
		stmt.close();

		return result;
		}

	public String
	performSQLQuery( String query )
			throws SQLException
		{
		// Create statement
		Statement stmt = this.connection.createStatement();

		// Execute query
		ResultSet rs = stmt.executeQuery( query );

		// Format results
		StringBuffer result = new StringBuffer();
		int rows = this.formatResults( rs, result );

		// Close result set
		rs.close();

		// Close statement
		stmt.close();

		return result.toString();
		}

	public JDBCAdapter
	performTableSQLQuery( String query )
			throws SQLException
		{
		// Create statement
		Statement stmt = this.connection.createStatement();

		// Execute query
		ResultSet rs = stmt.executeQuery( query );

		MySQLAdapter adapter = new MySQLAdapter( rs );

		// Close result set
		rs.close();

		// Close statement
		stmt.close();

		return adapter;
		}

	public int
	performSQLUpdate( String update )
			throws SQLException
		{
		// Create statement
		Statement stmt = this.connection.createStatement();

		// Execute query
		int nRows = stmt.executeUpdate( update );

		// Close statement
		stmt.close();

		return nRows;
		}

	public JDBCAdapter
	getTableColumnInfo( String tableName )
		throws SQLException
		{
		MySQLAdapter adapter = (MySQLAdapter)
			this.columnAdapterCache.get( tableName );

		if ( adapter != null )
			return adapter;

		if ( true )
			{
			// Create statement
			Statement stmt = this.connection.createStatement();

			// Execute query
			String query = "show columns from " + tableName;
			ResultSet result = stmt.executeQuery( query );
			adapter = new MySQLAdapter( result );

			this.columnAdapterCache.put( tableName, adapter );

			// Close result set
			result.close();

			// Close statement
			stmt.close();
			}
		else
			{
			DatabaseMetaData meta =
				this.connection.getMetaData();

			ResultSet result =
				meta.getColumns( null, null, tableName, "%" );

			adapter = new MySQLAdapter( result );
			}

		return adapter;
		}

	public String[]
	getDatabaseList()
		throws SQLException
		{
		if ( this.dbListCache != null )
			return this.dbListCache;

		// Create statement
		Statement stmt = this.connection.createStatement();

		// Execute query
		String query = "show databases";
		ResultSet result = stmt.executeQuery( query );

		String[] list =
			this.columnToStringList( result, 1 );

		// Close result set
		result.close();

		// Close statement
		stmt.close();

		this.dbListCache = list;

		return list;
		}

	public String[]
	getDatabaseTableList()
		throws SQLException
		{
		if ( this.tableListCache != null )
			return this.tableListCache;

		String[] list = null;

		if ( true )
			{
			// Create statement
			Statement stmt = this.connection.createStatement();

			// Execute query
			String query = "show tables";
			ResultSet result = stmt.executeQuery( query );

			list = this.columnToStringList( result, 1 );

			// Close result set
			result.close();

			// Close statement
			stmt.close();
			}
		else
			{
			DatabaseMetaData meta =
				this.connection.getMetaData();

			ResultSet result =
				meta.getTables( null, null, "%", null );

			list = this.columnToStringList( result, 3 );
			}

		this.tableListCache = list;

		return list;
		}

	public JDBCAdapter
	getDatabaseTableInfo()
		throws SQLException
		{
		if ( this.tableAdapterCache != null )
			return this.tableAdapterCache;

		JDBCAdapter adapter = null;

		if ( true )
			{
			// Create statement
			Statement stmt = this.connection.createStatement();

			// Execute query
			String query = "show tables";
			ResultSet result = stmt.executeQuery( query );
			adapter = new MySQLAdapter( result );

			// Close result set
			result.close();

			// Close statement
			stmt.close();
			}
		else
			{
			DatabaseMetaData meta =
				this.connection.getMetaData();

			ResultSet result =
				meta.getTables( null, null, "%", null );

			adapter = new MySQLAdapter( result );
			}

		this.tableAdapterCache = adapter;

		return adapter;
		}

	public int
	formatResults( ResultSet rs, StringBuffer buf )
		throws SQLException
		{
		return this.formatResults( rs, buf, " " );
		}

	public int
	formatResults( ResultSet rs, StringBuffer buf, String sep )
		throws SQLException
		{
		int i;
		int rows = 0;
		ResultSetMetaData rsmd = rs.getMetaData();
		int numCols = rsmd.getColumnCount();

		buf.append( "Result contains " + numCols + " columns.\n\n" );

		try {
			boolean hasMore = rs.next();
			if ( hasMore )
				{
				// Display column headings
				int widths[] = new int[ numCols + 1 ];

				for ( i = 1 ; i <= numCols ; i++ ) 
					{
					widths[i] = rsmd.getColumnDisplaySize(i);
					if ( widths[i] > 32 )
						widths[i] = 32;
					if ( widths[i] < 6 )
						widths[i] = 6;

					String name = rsmd.getColumnName(i);

					buf.append( name );
					
					int padLen = widths[i] - name.length();
					if ( padLen > 0 )
						buf.append( this.padChars( padLen, " " ) );

					if ( i < numCols )
						buf.append( sep );
					}

				buf.append( "\n" );

				for ( i = 1 ; i <= numCols ; i++ ) 
					{
					buf.append( this.padChars( widths[i], "-" ) );
					if ( i < numCols )
						buf.append( sep );
					}

				buf.append( "\n" );

				// Display data, fetching until end of the result set
				for ( ; hasMore ; ) 
					{          
					rows++;    
					for ( i = 1 ; i <= numCols ; i++ ) 
						{
						String data = rs.getString(i);
						if ( data == null )
							data = "(null)";
						int datalen = data.length();

						if ( data == null )
							{
							buf.append( "(null)" );
							}
						else if ( datalen > widths[i] )
							{
							if ( widths[i] > 6 )
								{
								int trunc = widths[i] - 3;
								buf.append
									( data.substring( 0, (widths[i] - 3) ) );
								}
							else
								{
								buf.append( data.substring
									( 0, ( datalen > widths[i]
											? widths[i] : datalen) ) );
								}
							}
						else
							{
							buf.append( data );
							buf.append(
								this.padChars
									( (widths[i] - data.length()), " " ) );
							}

						if ( i < numCols )
							buf.append( sep );
						}
					
					buf.append( "\n" );
					hasMore = rs.next();
					}
				}
			}
		catch (SQLException e)
			{
			throw e; 
			}

		return rows;
		}
  
	/**
	 * Retrieve the data from column 'colIdx' for all
	 * rows in the 'result' ResultSet, and return a 
	 * String array of the data cells.
	 */

	private String[]
	columnToStringList( ResultSet result, int colIdx )
		throws SQLException
		{
		String[] list = new String[0];

		Vector tempList = new Vector();

		for ( ; result.next() ; )
			{
			String value = result.getString( colIdx );
			tempList.addElement( value );
			}

		list = new String[ tempList.size() ];
		for ( int idx = 0 ; idx < list.length ; ++idx )
			{
			list[idx] = (String) tempList.elementAt( idx );
			}

		return list;
		}

	private String
	padChars( int num, String pad )
		{
		StringBuffer buf =
			new StringBuffer( (num * pad.length()) );

		for ( int i = 0 ; i < num ; ++i )
			buf.append( pad );

		return buf.toString();
		}

	}