import java.awt.*;
import java.awt.event.*;
import java.sql.*;
import java.applet.Applet;

// This frontend is based on Listing 15.28 in 
//     Core Web Programming 
// from
//    Prentice Hall Publishers, 
// which may be freely used or adapted. 
// 1997 Marty Hall, hall@apl.jhu.edu.

public class PsqlFrontend extends Applet
  implements ActionListener {

  public static void main(String[] args) {
    new PsqlFrontend("PostgreSQL Frontend");
  }

  protected ScrollPane tablePane;
  protected LabeledTextField tableField, hostField, dbNameField;
  protected LabeledTextField userField, passwdField;
  protected Button showTableButton, makeNewConnectionButton, doSQLButton;
  protected Button showTableInfoButton, showAllTablesInfoButton;
  protected Button clearSQLButton, clearLogButton;
  protected Panel inputPanel, tablePanel, logPanel, sqlPanel;
  protected Connection connection;
  protected Statement statement;
  protected DatabaseMetaData dbmd;
  protected TextArea sqlField, logField;
  protected int logCount = 0;
  protected Checkbox selectBox, createBox, insertBox, updateBox, dropBox, deleteBox;
  protected Container c;

  public void init() {
    c = this;
    c.setLayout(new BorderLayout());
    initGUI();
  }

  public void initGUI() {
    inputPanel = makeInputPanel();
    c.add("West", inputPanel);
    sqlPanel = makeSQLPanel();
    logPanel = makeLogPanel();
    Panel southPanel = new Panel(new GridLayout(0,1));
    southPanel.add(sqlPanel);
    southPanel.add(logPanel);
    c.add("South", southPanel);
  }

  public PsqlFrontend() {}

  public PsqlFrontend(String title) {
    c = new ClosableFrame(title);
    initGUI();
    ((Frame)c).pack();
    c.setVisible(true);
    ((Frame)c).show();
  }

  /** When the user clicks the "Show Table" button,
   *  the specified table is retrieved and 
   *  a Panel is created to hold the results.
   */

  private void showResultSet(ResultSet rs, String[] headers) {
    c.invalidate();
    if (tablePane != null)
      c.remove(tablePane);
    tablePanel = makeTablePanel(rs, headers);
    tablePane = new ScrollPane();
    tablePane.add(tablePanel);
    c.add("Center", tablePane);
    //c.pack();
    c.validate();
  }

  private void performSQL(String sql) {
    pp_log(sql);
    c.invalidate();
    if (tablePane != null)
      c.remove(tablePane);
    tablePanel = doSQL(sql);
    tablePane = new ScrollPane();
    tablePane.add(tablePanel);
    c.add("Center", tablePane);
    //    c.pack();
    c.validate();
  }

  private static final String allUserTables[] = {"TABLE","INDEX","SEQUENCE"};
  private static final String usrIndices[] = {"INDEX"};
  private static final String usrTables[] = {"TABLE"};
  private static final String usrSequences[] = {"SEQUENCE"};
  private static final String sysTables[] = {"SYSTEM TABLE","SYSTEM INDEX"};
  
  private String getTableName() {
    return tableField.getTextField().getText();
  }

  public void actionPerformed(ActionEvent event) {
    try {
      if (event.getSource() == showTableButton) {
	performSQL("SELECT * FROM " + getTableName());
      } else if (event.getSource() == makeNewConnectionButton) {
	dropConnection();
	makeConnection();
	pp_log("New connection established.");
      } else if (event.getSource() == doSQLButton) {
	performSQL(sqlField.getText());
      } else if (event.getSource() == clearLogButton) {
	logCount = 0;
	logField.setText("");
      } else if (event.getSource() == clearSQLButton) {
	sqlField.setText("");
      } else if (event.getSource() == showTableInfoButton) {
	pp_log("Show Table " + getTableName());
	String[] headers = {"COLUMN_NAME", "TYPE_NAME", "COLUMN_SIZE"};
	showResultSet(dbmd.getColumns(null,null,getTableName(),"%"), headers);
      } else if (event.getSource() == showAllTablesInfoButton) {
	pp_log("Show All Tables");
	String[] headers = {"TABLE_NAME", "TABLE_TYPE"};
	showResultSet(dbmd.getTables(null,null,"%",usrTables), headers);
      }
    } catch (SQLException se) {
      pp_error("" + se);
    } catch (Exception e) {
      pp_error("" + e);
    }
  }

  class Items implements ItemListener {
    public void itemStateChanged(ItemEvent event) {
      try {
	if (event.getItem() == selectBox.getLabel()) {
	  sqlField.append("SELECT DISTINCT ... FROM ...\n  WHERE ... \n  GROUP BY ...;");
	  selectBox.setState(false);
	}
	if (event.getItem() == createBox.getLabel()) {
	  sqlField.append("CREATE TABLE table (field type, ..., field type);");
	  createBox.setState(false);
	}
	
	if (event.getItem() == insertBox.getLabel()) {
	  sqlField.append("INSERT INTO table VALUES (...);");
	  insertBox.setState(false);
	}
	if (event.getItem() == updateBox.getLabel()) {
	  sqlField.append("UPDATE table SET field = value, ..., field = value \n  WHERE ...;");
	  updateBox.setState(false);
	}
	if (event.getItem() == dropBox.getLabel()) {
	  sqlField.append("DROP TABLE table;");
	  dropBox.setState(false);
	}
	if (event.getItem() == deleteBox.getLabel()) {
	  sqlField.append("DELETE FROM table WHERE ...;");
	  deleteBox.setState(false);
	}
      } catch (Exception e) {
	pp_error(""+e);
      }
    }
  }

  // Connect to the specified host and database
  // using the Connect SW driver and a preset
  // username and password.
                         
  private void makeConnection() throws SQLException {
    String driver = "postgresql.Driver";
    try {
      Class.forName(driver);
      String host = hostField.getTextField().getText();
      String url =
        "jdbc:postgresql://" + host + "/" +
        dbNameField.getTextField().getText();
      String user = userField.getTextField().getText();
      String password = passwdField.getTextField().getText();
      connection =
        DriverManager.getConnection(url, user, password);
      dbmd = connection.getMetaData();
      statement = connection.createStatement();
    } catch(ClassNotFoundException cnfe) {
      pp_error("No such class: " + driver);
    }
  }

  public void destroy() {
    dropConnection();
  }

  private void dropConnection() {
    try {
      if (statement != null)
	statement.close();
      if (connection != null)
	connection.close();
    } catch (SQLException se) {
      pp_error("" + se);
    } catch (Exception e) {
      pp_error(""+e);
    }
  }

  // Create a Panel that holds the textfields that
  // gather user input, plus the "Show Table" button.
                         
  private Panel makeInputPanel() {
    Panel inputPanel = new Panel(new GridLayout(0,1));
    inputPanel.setBackground(Color.lightGray);
    hostField = new LabeledTextField("Host Name:", "ellemose.dina.kvl.dk", 20);
    inputPanel.add(hostField);
    userField = new LabeledTextField("User Name:", "gp", 20);
    inputPanel.add(userField);
    passwdField = new LabeledTextField("Password:", "e2000", 20);
    inputPanel.add(passwdField);
    dbNameField = new LabeledTextField("DB Name:", "gp", 20);
    inputPanel.add(dbNameField);
    Panel makeNewConnectionPanel = new Panel();
    makeNewConnectionButton = new Button("Make New Connection");
    makeNewConnectionPanel.add(makeNewConnectionButton);
    inputPanel.add(makeNewConnectionPanel);
    makeNewConnectionButton.addActionListener(this);
    Panel showAllTabelsInfoPanel = new Panel();
    showAllTablesInfoButton = new Button("Show All Tables");
    showAllTablesInfoButton.addActionListener(this);
    showAllTabelsInfoPanel.add(showAllTablesInfoButton);
    inputPanel.add(showAllTabelsInfoPanel);
    tableField = new LabeledTextField("Table Name:", 15);
    inputPanel.add(tableField);
    showTableButton = new Button("Show Table");
    showTableButton.addActionListener(this);
    Panel showTableButtonPanel = new Panel();
    showTableButtonPanel.add(showTableButton);
    showTableInfoButton = new Button("Show Table Info");
    showTableInfoButton.addActionListener(this);
    showTableButtonPanel.add(showTableInfoButton);
    inputPanel.add(showTableButtonPanel);
    return(inputPanel);
  }

  private Panel makeSQLPanel() {
    Panel sqlPanel = new Panel();
    sqlPanel.setBackground(Color.lightGray);
    Panel labPanel = new Panel(new GridLayout(0,1));
    labPanel.add(new Label("SQL statement:"));
    doSQLButton = new Button("Do SQL");
    doSQLButton.addActionListener(this);
    labPanel.add(doSQLButton);
    clearSQLButton = new Button("Clear");
    clearSQLButton.addActionListener(this);
    labPanel.add(clearSQLButton);
    sqlPanel.add(labPanel);
    sqlField = new TextArea(8,80);
    sqlPanel.add(sqlField);

    Panel selPanel = new Panel(new GridLayout(0,1));
    Items items = new Items();
    selectBox = new Checkbox("SELECT", false);
    selectBox.addItemListener(items);
    selPanel.add(selectBox);
    createBox = new Checkbox("CREATE", false);
    createBox.addItemListener(items);
    selPanel.add(createBox);
    insertBox = new Checkbox("INSERT", false);
    insertBox.addItemListener(items);
    selPanel.add(insertBox);
    updateBox = new Checkbox("UPDATE", false);
    updateBox.addItemListener(items);
    selPanel.add(updateBox);
    dropBox = new Checkbox("DROP", false);
    dropBox.addItemListener(items);
    selPanel.add(dropBox);
    deleteBox = new Checkbox("DELETE", false);
    deleteBox.addItemListener(items);
    selPanel.add(deleteBox);
    sqlPanel.add(selPanel);
    return sqlPanel;
  }

  private Panel makeLogPanel() {
    Panel logPanel = new Panel();
    logPanel.setBackground(Color.lightGray);
    Panel labPanel = new Panel(new GridLayout(0,1));
    labPanel.add(new Label("Log:"));
    clearLogButton = new Button("Clear");
    clearLogButton.addActionListener(this);
    labPanel.add(clearLogButton);
    logPanel.add(labPanel);
    logField = new TextArea(5,90);
    logPanel.add(logField);
    return(logPanel);
  }

  private static String padLeft(int w, String s) {
    String res = s;
    int filler = w - s.length();
    for (;filler > 0;filler--)
      res = " " + res;
    return res;
  }

  private void pp_error(String e) {
    pp_log("ERROR: " + e + "\n");
  }

  private void pp_log(String e) {
    logField.append(padLeft(2,"") + logCount +": " + e + "\n");
    logCount++;
  }

  private Panel doSQL(String sql) {
    Panel tablePanel = new Panel();
    try {
      boolean type = statement.execute(sql);
      do {
	if (type) {
	  // We should print a table
	  ResultSet results = statement.getResultSet();
	  return makeTablePanel(results); // Bug -- we return the first table for the first resultset!
	} else {
	  // We updated some rows, or we are done
	  int count = statement.getUpdateCount();
	  if (count >= 0)
	    pp_log("Updated " + count + " rows."); // Buggy 03/12-1999, Niels
	}
      } while (type = statement.getMoreResults());
    } catch (SQLException se) {
      pp_error("" + se);
    } catch (Exception e) {
      pp_error("" + e);
    }
    return tablePanel;
  }
	    
  // Create a Panel with one TextField for each table
  // entry, plus an extra row for the column names.

  private Panel makeTablePanel(ResultSet results, String[] headers) {
    Panel tablePanel = new Panel();
    tablePanel.setBackground(Color.white);
    try {
      ResultSetMetaData metaData = results.getMetaData();
      // Layout Panel to hold specified number of
      // columns and whatever number of rows is needed
      int cols;
      if (headers.length == 0) 
	cols = metaData.getColumnCount();
      else
	cols = headers.length;
      tablePanel.setLayout(new GridLayout(0, cols));
      // A boldface textfield for each column name
      TextField field;
      Font headerFont = new Font("SansSerif", Font.BOLD, 8);
      for(int i=1; i<=cols; i++) {
	if (headers.length == 0)
	  field = new TextField(metaData.getColumnName(i));
	else
	  field = new TextField(headers[i-1]);
        field.setFont(headerFont);
	//Panel p = new Panel();
	//p.add(field);
        tablePanel.add(field);
      }
      // A regular-face textfield for each table entry
      Font bodyFont = new Font("Serif", Font.PLAIN, 10);
      String value;
      while(results.next())
        for(int i=1; i<=cols; i++) {
	  if (headers.length == 0)
	    value = results.getString(i);
	  else
	    value = results.getString(headers[i-1]);
          if (value == null)
            value = "<null>";
          field = new TextField(value);
          field.setFont(bodyFont);
	  //	Panel p = new Panel();
	  //	p.add(field);

          tablePanel.add(field);
        }
    } catch(SQLException se) {
      pp_error("" + se);
    } catch(Exception e) {
	pp_error("" + e);
    }
    Panel p = new Panel();
    p.add(tablePanel);
    return p;
  }

  private Panel makeTablePanel(ResultSet results) {
    String[] h = {};
    return makeTablePanel(results, h);
  }
}