package example;

import java.io.*;
import java.sql.*;
import java.text.*;

/**
 * This example application is not really an example. It actually performs
 * some tests on various methods in the DatabaseMetaData and ResultSetMetaData
 * classes.
 *
 * To use it, simply have a database created. It will create some work tables
 * and run tests on them.
 */

public class metadata
{
  Connection	   db;		// The connection to the database
  Statement	   st;		// Our statement to run queries with
  DatabaseMetaData dbmd;	// This defines the structure of the database
  
  /**
   * These are the available tests on DatabaseMetaData
   */
  public void doDatabaseMetaData() throws SQLException {
    if(doTest("getProcedures() - should show all available procedures"))
      displayResult(dbmd.getProcedures(null,null,null));
    
    if(doTest("getProcedures() with pattern - should show all circle procedures"))
      displayResult(dbmd.getProcedures(null,null,"circle%"));
    
    if(doTest("getProcedureColumns() on circle procedures"))
      displayResult(dbmd.getProcedureColumns(null,null,"circle%",null));
    
    if(doTest("getTables()"))
      displayResult(dbmd.getTables(null,null,null,null));
    
    if(doTest("getColumns() - should show all tables, can take a while to run"))
      displayResult(dbmd.getColumns(null,null,null,null));
    
    if(doTest("getColumns() - should show the test_b table"))
      displayResult(dbmd.getColumns(null,null,"test_b",null));
    
    if(doTest("getColumnPrivileges() - should show all tables"))
      displayResult(dbmd.getColumnPrivileges(null,null,null,null));
    
    if(doTest("getPrimaryKeys()"))
      displayResult(dbmd.getPrimaryKeys(null,null,null));
    
    if(doTest("getTypeInfo()"))
      displayResult(dbmd.getTypeInfo());
    
  }
  
  /**
   * These are the available tests on ResultSetMetaData
   */
  public void doResultSetMetaData() throws SQLException {
    
    String sql = "select imagename,descr,source,cost from test_a,test_b,test_c where test_a.id=test_b.imageid and test_a.id=test_c.imageid";
    
    System.out.println("Executing query for tests");
    ResultSet rs = st.executeQuery(sql);
    ResultSetMetaData rsmd = rs.getMetaData();
    
    if(doTest("isCurrency()"))
      System.out.println("isCurrency on col 1 = "+rsmd.isCurrency(1)+" should be false\nisCurrency on col 4 = "+rsmd.isCurrency(4)+" should be true");
    
    // Finally close the query. Now give the user a chance to display the
    // ResultSet.
    //
    // NB: displayResult() actually closes the ResultSet.
    if(doTest("Display query result")) {
      System.out.println("Query: "+sql);
      displayResult(rs);
    } else
      rs.close();
  }
  
  /**
   * This creates some test data
   */
  public void init() throws SQLException {
    System.out.println("Creating some tables");
    cleanup();
    st.executeUpdate("create table test_a (imagename name,image oid,id int4)");
    st.executeUpdate("create table test_b (descr text,imageid int4,id int4)");
    st.executeUpdate("create table test_c (source text,cost money,imageid int4)");
    
    System.out.println("Adding some data");
    st.executeUpdate("insert into test_a values ('test1',0,1)");
    st.executeUpdate("insert into test_b values ('A test description',1,2)");
    st.executeUpdate("insert into test_c values ('nowhere particular','$10.99',1)");
  }
  
  /**
   * This removes the test data
   */
  public void cleanup() throws SQLException {
    try {
      st.executeUpdate("drop table test_a");
      st.executeUpdate("drop table test_b");
      st.executeUpdate("drop table test_c");
    } catch(Exception ex) {
      // We ignore any errors here
    }
  }
  
  public metadata(String args[]) throws ClassNotFoundException, FileNotFoundException, IOException, SQLException
  {
    String url = args[0];
    String usr = args[1];
    String pwd = args[2];
    
    // Load the driver
    Class.forName("org.postgresql.Driver");
    
    // Connect to database
    System.out.println("Connecting to Database URL = " + url);
    db = DriverManager.getConnection(url, usr, pwd);
    
    dbmd = db.getMetaData();
    st = db.createStatement();
    
    // This prints the backend's version
    System.out.println("Connected to "+dbmd.getDatabaseProductName()+" "+dbmd.getDatabaseProductVersion());
    
    init();
    
    System.out.println();
    
    // Now the tests
    if(doTest("Test DatabaseMetaData"))
      doDatabaseMetaData();
    
    if(doTest("Test ResultSetMetaData"))
      doResultSetMetaData();
    
    System.out.println("\nNow closing the connection");
    st.close();
    db.close();
    
    cleanup();
  }
  
  /**
   * This asks if the user requires to run a test.
   */
  public boolean doTest(String s) {
    System.out.println();
    System.out.print(s);
    System.out.print(" Perform test? Y or N:");
    System.out.flush();
    char c = ' ';
    try {
      while(!(c=='n' || c=='y' || c=='N' || c=='Y')) {
	c=(char)System.in.read();
      }
    } catch(IOException ioe) {
      return false;
    }
    
    return c=='y' || c=='Y';
  }
  
  /**
   * This displays a result set.
   * Note: it closes the result once complete.
   */
  public void displayResult(ResultSet rs) throws SQLException
  {
    ResultSetMetaData rsmd = rs.getMetaData();
    int count=0;
    
    // Print the result column names
    int cols = rsmd.getColumnCount();
    for(int i=1;i<=cols;i++)
      System.out.print(rsmd.getColumnLabel(i)+(i<cols?"\t":"\n"));
    
    // now the results
    while(rs.next()) {
      count++;
      for(int i=1;i<=cols;i++) {
	Object o = rs.getObject(i);
	if(rs.wasNull())
	  System.out.print("{null}"+(i<cols?"\t":"\n"));
	else
	  System.out.print(o.toString()+(i<cols?"\t":"\n"));
      }
    }
    
    System.out.println("Result returned "+count+" rows.");
    
    // finally close the result set
    rs.close();
  }
  
  /**
   * This process / commands (for now just /d)
   */
  public void processSlashCommand(String line) throws SQLException
  {
    if(line.startsWith("\\d")) {
      
      if(line.startsWith("\\d ")) {
	// Display details about a table
	String table=line.substring(3);
	displayResult(dbmd.getColumns(null,null,table,"%"));
      } else {
	String types[] = null;
	if(line.equals("\\d"))
	  types=allUserTables;
	else if(line.equals("\\di"))
	  types=usrIndices;
	else if(line.equals("\\dt"))
	  types=usrTables;
	else if(line.equals("\\ds"))
	  types=usrSequences;
	else if(line.equals("\\dS"))
	  types=sysTables;
	else
	  throw new SQLException("Unsupported \\d command: "+line);
	
	// Display details about all system tables
	//
	// Note: the first two arguments are ignored. To keep to the spec,
	//       you must put null here
	//
	displayResult(dbmd.getTables(null,null,"%",types));
      }
    } else
      throw new SQLException("Unsupported \\ command: "+line);
  }
  
  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"};
  
  /**
   * Display some instructions on how to run the example
   */
  public static void instructions()
  {
    System.out.println("\nThis is not really an example, but is used to test the various methods in\nthe DatabaseMetaData and ResultSetMetaData classes.\n");
    System.out.println("Useage:\n java example.metadata jdbc:postgresql:database user password [debug]\n\nThe debug field can be anything. It's presence will enable DriverManager's\ndebug trace. Unless you want to see screens of debug items, don't put anything in\nhere.");
    System.exit(1);
  }
  
  /**
   * This little lot starts the test
   */
  public static void main(String args[])
  {
    System.out.println("PostgreSQL metdata tester v6.4 rev 1\n");
    
    if(args.length<3)
      instructions();
    
    // This line outputs debug information to stderr. To enable this, simply
    // add an extra parameter to the command line
    if(args.length>3)
      DriverManager.setLogStream(System.err);
    
    // Now run the tests
    try {
      metadata test = new metadata(args);
    } catch(Exception ex) {
      System.err.println("Exception caught.\n"+ex);
      ex.printStackTrace();
    }
  }
}