/*
 * Browser.java
 *
 * Created on June 23, 2007, 10:15 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

//package browser;

import java.sql.*;

/**
 * A command-line interpreter that executes SQL
 * commands against a JDBC-compliant database.
 * The default values of the driver, database,
 * user, and password open my Oracle cs174 database
 * running on Sigma. These must be changed to connect
 * to other databases.
 */
public class SQLBrowser extends Console {

    /**
	 * A JDBC driver converts JDBC queries into database-
	 * specific commands. Thus, each type of database
	 * requires a specific driver.Sometimes the driver manager can
	 * automatically locate and load the appropritate driver.
	 * We explicitly load the appropriate driver, which means
	 * we must know its fully qualified name.
	 */
	protected String driverName =
	   //"oracle.jdbc.driver.OracleDriver";
	   //"COM.cloudscape.core.RmiJdbcDriver";
	   "com.mysql.jdbc.Driver";
	   //"org.apache.derby.jdbc.ClientDriver";

   /**
	* The specific database to be opened is identified by a
	* URL of the form PROTOCOL:[@HOST:]PORT:NAME
	*/
	protected String dbasePrefix =
	   //"jdbc:cloudscape:rmi:";
	   //"jdbc:oracle:thin:@sigma.mathcs.sjsu.edu:1521:CS174";
	   //"jdbc:cloudscape:rmi:addresses";
	   "jdbc:mysql://localhost/";
	   //"jdbc:derby://localhost:1527/";

	/**
	 * Some databases require a login to connect. Put your
	 * Oracle user name and password here:
	 */
	protected String username= "pearce";
	protected String password= "";

    /**
	 * The connection to a database is a JDBC object:
	 */
	protected Connection connection;

	/**
	 * Statement seems like a mis-named class. This is really an object
	 * that executes arbitrary SQL statements.
	 */
	protected Statement statement;

	/**
	 * Executing most SQL statements results in a virtual table or
	 * view, which is represented in JDBC by a ResultSet object.
	 */
	protected ResultSet result;

    /**
	 * This constructor attempts to create a new database if the
	 * requested database can't be found. It initializes connection
	 * and statement.
	 */
	public SQLBrowser(String dbase) throws SQLException, ClassNotFoundException {
		String dbaseName = dbasePrefix + dbase;
		try {
			Class.forName(driverName);
			connection = DriverManager.getConnection(dbaseName, username, password);
		} catch (Exception e) { // dbase not found?
			System.err.println("can't connect to " + dbaseName + " because " + e);
			connection = DriverManager.getConnection(dbaseName + ";create=true");
		}
		statement = connection.createStatement();
	}

	public SQLBrowser() throws SQLException, ClassNotFoundException {
			String dbaseName = dbasePrefix;
			System.setProperty("jdbc.drivers", driverName);
			try {
				Class.forName(driverName);
				connection = DriverManager.getConnection(dbaseName, username, password);
			} catch (Exception e) { // dbase not found?
				System.err.println("can't connect to " + dbaseName + " because " + e);
				connection = DriverManager.getConnection(dbaseName + ";create=true");
			}
			statement = connection.createStatement();
	}

	public void finalize() throws SQLException {
		statement.close();
		connection.close();
	}

    /**
	 * Converts a result set into a formatted string semi-suitable
	 * for printing. The main significance of this method is that it
	 * shows how result sets are typically manipulated.
	 */
	private String toString(ResultSet rs) throws SQLException {

		StringBuffer results = new StringBuffer();
		ResultSetMetaData metaData = rs.getMetaData();
		int numCols = metaData.getColumnCount();
		String spaces = "    ";

		// get column names:
		for(int i = 1; i <= numCols; i++) {
			results.append(metaData.getColumnName(i) + spaces);
		}

		results.append("\n");

		while(rs.next()) {
			for(int i = 1; i <= numCols; i++) {
				results.append(rs.getObject(i) + spaces);
			}
			results.append("\n");
		}

		return results.toString();
	}

    /**
	 * This method is called by the control loop in the Console
	 * class.
	 */
	public String execute(String sql) throws AppError {

		String result = "???";
		try {
			if (sql.equals("commit")) {
				connection.commit();
				result = "done";
			} else if (sql.equals("rollback")) {
				connection.rollback();
				result = "done";
			} else if (statement.execute(sql)) {
			   result = toString(statement.getResultSet());
			} else {
			   result = "done";
			}
		} catch (SQLException e) {
			throw new AppError(e.toString());
		}
		return result;
	}

    /**
	 * This method is also called by the Console's control loop
	 */
	protected void help() {
		super.help();
		try {
			stdout.println("SQL commands: ");
			stdout.println("   create table NAME (COL_1, ..., COL_N)");
			stdout.println("   insert into NAME values (VAL_1, ..., VAL_N)");
			stdout.println("   select COLUMNS from NAMES where CONDITION");
			stdout.println("   update NAME set COL = VAL where CONDITION");
			stdout.println("   delete COL from NAME");
			stdout.println("   drop table NAME COL");
			stdout.println("   commit");
			stdout.println("   rollback");
			stdout.println("   etc.");
		} catch (Exception e) {
			stderr.println("error: " + e);
		}
	}

    /**
	 * Start the browser
	 */
	static public void main(String[] args) {

		try {
			SQLBrowser browser = new SQLBrowser("unc");
			browser.controlLoop();
		}

		catch (SQLException e) {
			System.err.println("dbase error: " + e);
		}

		catch (Exception e) {
			System.err.println("error: " + e);
		}
	}
}