package example;

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

/*
 * This example application demonstrates some of the drivers other features
 * by implementing a simple psql replacement in Java.
 *
 */

public class psql
{
	Connection	db;		// The connection to the database
	Statement	st;		// Our statement to run queries with
	DatabaseMetaData dbmd;	// This defines the structure of the database
	boolean done = false;		  // Added by CWJ to permit \q command

	public psql(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());

		System.out.println();

		// This provides us the means of reading from stdin
		StreamTokenizer input = new StreamTokenizer(new InputStreamReader(System.in));
		input.resetSyntax();
		input.slashSlashComments(true); // allow // as a comment delimiter
		input.eolIsSignificant(false);	// treat eol's as spaces
		input.wordChars(32, 126);
		input.whitespaceChars(59, 59);
		// input.quoteChar(39); *** CWJ: messes up literals in query string ***

		// Now the main loop.
		int tt = 0, lineno = 1;
		while (tt != StreamTokenizer.TT_EOF && ! done)
		{
			System.out.print("[" + lineno + "] ");
			System.out.flush();

			// Here, we trap SQLException so they don't terminate the application
			try
			{
				if ((tt = input.nextToken()) == StreamTokenizer.TT_WORD)
				{
					processLine(input.sval);
					lineno++;
				}
			}
			catch (SQLException ex)
			{
				System.out.println(ex.getMessage());
			}
		}

		System.out.println("Now closing the connection");
		st.close();
		db.close();
	}

	/*
	 * This processes a statement
	 */
	public void processLine(String line) throws SQLException
	{
		if (line.startsWith("\\"))
		{
			processSlashCommand(line);
			return;
		}

		boolean type = st.execute(line);
		boolean loop = true;
		while (loop)
		{
			if (type)
			{
				// A ResultSet was returned
				ResultSet rs = st.getResultSet();
				displayResult(rs);
			}
			else
			{
				int count = st.getUpdateCount();

				if (count == -1)
				{
					// This indicates nothing left
					loop = false;
				}
				else
				{
					// An update count was returned
					System.out.println("Updated " + st.getUpdateCount() + " rows");
				}
			}

			if (loop)
				type = st.getMoreResults();
		}
	}

	/*
	 * This displays a result set.
	 * Note: it closes the result once complete.
	 */
	public void displayResult(ResultSet rs) throws SQLException
	{
		ResultSetMetaData rsmd = rs.getMetaData();

		// 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())
		{
			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"));
			}
		}

		// 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 if (line.equals("\\q")) // Added by CWJ to permit \q command
			done = true;
		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 example shows how some of the other JDBC features work within the\ndriver. It does this by implementing a very simple psql equivalent in java.\nNot everything that psql does is implemented.\n");
		System.out.println("Useage:\n java example.psql 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 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 psql example v6.3 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
		{
			psql test = new psql(args);
		}
		catch (Exception ex)
		{
			System.err.println("Exception caught.\n" + ex);
			ex.printStackTrace();
		}
	}
}