/* SQLTable - an extension to Table.java to 
 * support SQL and JDBC.
 */
import java.sql.*;
import java.util.*;

public class SQLTable extends Table {

  /* Constructor */
  public SQLTable(String name, String pkey) {
    super(name, pkey);
  }

  /* Generate an SQL Statement */
  public String buildSQL() {

    /* Get a list of all tables in the SQL statement. */
    Vector tables = new Vector();
    findTables(tables);

    /* Get a list of all the columns. */
    Vector columns = new Vector();
    findColumns(columns);

    /* Get a list of all the constraints that need to
     * go into the WHERE clause.
     */
    Vector where = new Vector();
    findConstraints(where);

    /* Create a StringBuffer containing the SQL. */
    StringBuffer sql = new StringBuffer("SELECT ");
    sql.append(delimitedList(", ", columns.elements()));
    sql.append(" FROM ");
    sql.append(delimitedList(", ", tables.elements()));
    if (where.size() > 0) { 
      sql.append(" WHERE ");
      sql.append(delimitedList(" AND ", where.elements()));
    }
    return sql.toString();

  }

  /* Use a JDBC Connection to fetch all the rows.
   * Note: caller should close the result set's
   * Statement object.
   */
  public ResultSet fetchRows(Connection conn) 
    throws Exception 
  {

    String sql = buildSQL();

    /* Create the statement (may throw SQLException) */
    Statement stmt = conn.createStatement();

    /* Return the result set. */
    return stmt.executeQuery(sql);

  }

  /* This is a convenience method to get data from
   * every column except primary key columns. It's
   * used in cases where you need the primary key
   * in the result set but don't want to show it to
   * users. For an example of how this method is used,
   * see HtmlSelectListFactory.java.
   */
  public Enumeration getDisplayData(ResultSet rs) 
    throws SQLException 
  {

    ResultSetMetaData rmd = rs.getMetaData();
    Vector result = new Vector();
    for(int i = 0; i < rmd.getColumnCount(); i++) {
      String column = rmd.getColumnName(i + 1);
      if (isPrimaryKey(column)) {
        continue;
      }
      result.addElement( rs.getString(column) );
    }
    return result.elements();

  }

  /* This method generates a delimited list. It's used
   * internally to produce comma- or space-separated
   * lists for SQL statements.
   */
  private String delimitedList(String delim, 
                               Enumeration e) 
  {

    if (!e.hasMoreElements()) {
      return "";
    }

    StringBuffer sb = new StringBuffer();
    sb.append( e.nextElement() ); // Add the first item.

    /* Add the remaining items, but prefix each with
     * the delimiter.
     */
    while (e.hasMoreElements()) {
      sb.append(delim + (String) e.nextElement() );
    }

    return sb.toString();

  }

  /* Recursively search the tables for constraints and 
   * populate a vector with SQL expressions.
   */
  private void findConstraints(Vector v) {

    Enumeration e = constraints.elements();
    while(e.hasMoreElements()) {
      Constraint c = (Constraint) e.nextElement();
      String expr = name + "." + c.column +
                    " " + c.opAsString() + " " +
                    c.value;
      v.add(expr);
    }

    e = children.elements();
    while (e.hasMoreElements()) {
      SQLTable child = (SQLTable) e.nextElement();
      child.findConstraints(v);
    }

  }

  /* Populate a vector with the names of all the tables. */
  private void findTables(Vector v) {

    v.add(name);
    Enumeration e = children.elements();
    while (e.hasMoreElements()) {
      SQLTable child = (SQLTable) e.nextElement();
      child.findTables(v);
    }

  }

  /* Populate a vector with the names of all the columns,
   * qualified by table name (TABLE.COLUMN)
   */
  private void findColumns(Vector v) {

    Enumeration col_enum = columns.elements();
    while (col_enum.hasMoreElements()) {
      String col = (String) col_enum.nextElement();
      v.add(name + "." + col);
    }

    Enumeration child_enum = children.elements();
    while (child_enum.hasMoreElements()) {
      SQLTable child = (SQLTable) child_enum.nextElement();
      child.findColumns(v);
    }

  }
}