// Copyright MageLang Institute; Version $Id: //depot/main/src/edu/modules/Beans/magercises/DatabaseManager/Solution/database/DatabaseManager.java#3 $

/**** DatabaseManager.java ****/

package database;

import java.util.*;
import java.io.*;
import java.sql.*;

//
// Important:  do NOT change '\n' (LINEFEED)
// throughout the file to the system line separator,
// e.g., System.getProperty("line.separator")
//

public class DatabaseManager implements Serializable {
  //
  // Constants:
  //
  private static final String DRIVER =
    "sun.jdbc.odbc.JdbcOdbcDriver";
  private static final String PROTOCOL = "jdbc";
  private static final String SUBPROTOCOL = "odbc";
  private static final String SUB_DATA = "datasource";
  private static final int STARTS_WITH_LEN = 40;
  private static final String LINEFEED = "\n";
  private static final String DOUBLE_LINEFEED = "\n\n";

  //
  // Instance variables:
  //
  private String driver;
  private String url;
  private String subprotocol;
  private String subprotocolData;
  private String sql;
  private String user = "";
  private String password = "";
  private String result = "";
  private String message = "";
  private Connection con = null;
  private Statement stmt = null;
  private boolean connected = false;
  private boolean submitted = false;
  private boolean dbError = false;
  private boolean doubleSpace = false;
  private boolean debug = false;
  private DatabaseMetaData dmd;
  private Vector targets = new Vector();

  public DatabaseManager(String driver, String subprotocol,
      String subprotocolData, String sql) {
    this.driver = checkValue(DRIVER, driver);
    this.subprotocol = checkValue(SUBPROTOCOL, subprotocol);
    this.subprotocolData = checkValue(SUB_DATA, subprotocolData);
    this.sql = checkValue("", sql);
    buildUrl();
  }

  public DatabaseManager() {
    this(DRIVER, SUBPROTOCOL, SUB_DATA, "");
  }

  //
  // for testing:
  //

  public static void main(String[] args) {
    if (usageOnly(args))
      return;
    DatabaseManager dm;
    if (args.length == 4) {
      dm = new DatabaseManager(args[0], args[1], args[2], args[3]);
      dm.connectToDB();
      dm.executeSql();
      System.out.println(dm.getResult());
      dm.closeDB();
    }
  }

  private static boolean usageOnly(String[] args) {
    if (args.length != 4 || (args.length > 0 &&
          (args[0].equalsIgnoreCase("-help") ||
          args[0].equalsIgnoreCase("-h") ||
          args[0].equalsIgnoreCase("-usage")))) {
      System.out.println("Usage: java DatabaseManager" +
        " [<driver> <subprotocol> <subprotocolData> <sql>]");
      return true;
    }
    else
      return false;
  }

  private void buildUrl() {
    url = PROTOCOL + ":";
    url += checkValue(SUBPROTOCOL, subprotocol);
    url += ":";
    url += checkValue(SUB_DATA, subprotocolData);
    handleMessage("Build URL: " + url);
  }

  private void parseUrl() {
    if (url == null || url.length() == 0) {
      handleMessage("URL has been set to null -- now unusable.");
      return;
    }
    StringTokenizer st = new StringTokenizer(url, ":");
    try {
      String protocol = st.nextToken();
      handleMessage("Parsed protocol: " + protocol);
    }
    catch (Exception e) {
      handleMessage("Can't parse URL protocol.");
    }
    try {
      String subprotocol = st.nextToken();
      this.subprotocol = subprotocol;
      handleMessage("Parsed subprotocol: " + subprotocol);
    }
    catch (Exception e) {
      handleMessage("Can't parse URL subprotocol.");
    }
    try {
      String subprotocolData = st.nextToken();
      this.subprotocolData = subprotocolData;
      handleMessage("Parsed subprotocol data: " + subprotocolData);
    }
    catch (Exception e) {
      handleMessage("Can't parse URL subprotocol data.");
    }
  }

  private String checkValue(String alternateValue, String str) {
    return (str == null || str.length() == 0) ?
      alternateValue : str;
  }

  private void handleMessage(String msg) {
    if (debug)
      System.out.println(msg);
    message = msg;
  }

  public synchronized void
    addDatabaseManagerListener(DatabaseManagerListener l) {
      targets.addElement(l);
  }

  public synchronized void
    removeDatabaseManagerListener(DatabaseManagerListener l) {
      targets.removeElement(l);
  }

  protected void notifyTargets() {
    Vector l;
    DatabaseManagerEvent e = new DatabaseManagerEvent(this, result);
    synchronized(this) {
      l = (Vector) targets.clone();
    }
    for (int i = 0; i < l.size(); i++) {
      DatabaseManagerListener dml =
        (DatabaseManagerListener) l.elementAt(i);
      dml.queryPerformed(e);
    }
  }

  synchronized public void connectToDB() {
    dbError = false;
    connected = false;
    closeDB();
    try {
      if (driver.length() == 0)
        driver = DRIVER;
      Class.forName(driver).newInstance();
      con = DriverManager.getConnection(url, user, password);
      stmt = con.createStatement();
      dmd = con.getMetaData();
      result = "Connected to: " + dmd.getURL() + LINEFEED +
        "Driver: " + dmd.getDriverName() + DOUBLE_LINEFEED +
        "Version: " + dmd.getDriverVersion();
      message = "Successfully connected to database.";
      connected = true;
    }
    catch (Exception e) {
      result = "";
      handleMessage("Error connecting to database: " + url);
      dbError = true;
    }
  }

  synchronized public void closeDB() {
    dbError = false;
    try {
      closeStatement();
      if (con != null) {
        connected = false;
        stmt = null;
        con.close();
        con = null;
      }
    }
    catch (Exception e) {
      dbError = true;
      con = null;
      handleMessage(
        "Error closing the database connection: " + url);
    }
  }

  private void closeStatement() {
    try {
      if (stmt != null)
        stmt.close();
    }
    catch (Exception e) {
      handleMessage("Error closing the current statement.");
    }
  }

  private void formatResultSetOutput(ResultSet rs) {
    result = "";
    try {
      ResultSetMetaData rsmd = rs.getMetaData();
      int cols = rsmd.getColumnCount();
      for (int i = 1; i <= cols; i++) {
        if (i > 1)
          result += ", ";
        result += rsmd.getColumnLabel(i);
      }
      result += DOUBLE_LINEFEED;
      while (rs.next()) {
        for (int i = 1; i <= cols; i++) {
          if (i > 1)
            result += ", ";
          result += rs.getString(i);
        }
        result += doubleSpace ? DOUBLE_LINEFEED : LINEFEED;
      }
      message = "";
    }
    catch (Exception e) {
      handleMessage("Error displaying the current statement.");
    }
  }

  synchronized public void executeSql() {
    if (con == null || stmt == null) {
      handleMessage("Please connect to a database.");
      return;
    }
    int len = sql.length();
    if (len == 0) {
      handleMessage("SQL statement is empty.");
      return;
    }
    if (len < 6) {
      handleMessage("SQL statement is invalid.");
      return;
    }
    try {
      submitted = true;
      String keyword = sql.substring(0, 6);
      int startLength =
        (len < STARTS_WITH_LEN) ? len : STARTS_WITH_LEN;
      String cmdStart = sql.substring(0, startLength);
      if (keyword.equalsIgnoreCase("select")) {
        ResultSet rs = stmt.executeQuery(sql);
        formatResultSetOutput(rs); // sets 'result'
        rs.close();
        handleMessage("Executed query: " + cmdStart + "...");
      }
      else if (keyword.equalsIgnoreCase("update") ||
          keyword.equalsIgnoreCase("insert") ||
          keyword.equalsIgnoreCase("delete")) {
        int result = stmt.executeUpdate(sql);
        this.result = "Executed update: " +
          cmdStart + "... with result: " + result;
        handleMessage(this.result);
      }
      else {
        stmt.execute(sql);
        result = "Executed: " + cmdStart + "...";
        handleMessage(result);
      }
      notifyTargets();
    }
    catch (Exception e) {
      handleMessage("Error executing the current statement.");
    }
  }

  public String executeSqlGetResult() {
    executeSql();
    return result;
  }

  public void printResult() {
    System.out.println(result);
  }

  synchronized public void reset() {
    dbError = false;
    sql = "";
    result = "";
    message = "";
  }

  synchronized public String getResultAsHtmlParagraph() {
    if (result.indexOf("<p>") != -1 ||
        result.indexOf("<br>") != -1 ||
        result.indexOf("<table>") != -1)
      return result;
    String html = "<p>";
    StringTokenizer st = new StringTokenizer(result, "\n");
    while (st.hasMoreTokens())
      html += st.nextToken() + "<br>";
    html += "</p>";
    return html;
  }

  synchronized public String getResultAsHtmlTextArea() {
    String html = "<textarea name=result rows=4 cols=60>";
    html += result;
    html += "</textarea>";
    return html;
  }

  //
  // properties:
  //

  public String getMessage() {
    return message;
  }

  public void setMessage(String message) {
    // do nothing -- read-only property
  }

  public String getResult() {
    return result;
  }

  public void setResult(String result) {
    // do nothing -- read-only property
  }

  public boolean getDBError() {
    return dbError;
  }

  public boolean isConnected() {
    return connected;
  }

  public void setConnected(boolean state) {
    if (!state) {
      closeDB();
    }
    else {
      connectToDB();
    }
  }

  public boolean isSubmitted() {
    return submitted;
  }

  public void setSubmitted(boolean state) {
    if (!state) {
      if (submitted)
        setSql("");
    }
    else {
      executeSql();
    }
  }

  public String getDriver() {
    return driver;
  }

  synchronized public void setDriver(String str) {
    driver = str;
  }

  public String getSubprotocol() {
    return subprotocol;
  }

  synchronized public void setSubprotocol(String sp) {
    subprotocol = sp;
    buildUrl();
  }

  public String getSubprotocolData() {
    return subprotocolData;
  }

  synchronized public void setSubprotocolData(String spd) {
    subprotocolData = spd;
    buildUrl();
  }

  public String getUrl() {
    return url;
  }

  synchronized public void setUrl(String str) {
    url = str;
    parseUrl();
  }

  public String getUser() {
    return user;
  }

  synchronized public void setUser(String str) {
    user = str;
  }

  public String getPassword() {
    return password;
  }

  synchronized public void setPassword(String str) {
    password = str;
  }

  public String getSql() {
    return sql;
  }

  synchronized public void setSql(String str) {
    submitted = false;
    sql = str;
  }

  public boolean getDoubleSpace() {
    return doubleSpace;
  }

  synchronized public void setDoubleSpace(boolean state) {
    doubleSpace = state;
  }

  public boolean getDebug() {
    return debug;
  }

  synchronized public void setDebug(boolean state) {
    debug = state;
  }
} // DatabaseManager class //