import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

/**
 * This program is used to create the database.  Compile
 * and run, after making sure that the hsqldb server is running on the port
 * you pass on the command line.  You'll also need to include the location
 * of hsqldb.jar in the classpath.  For example, if hsqldb.jar is in
 * c:\hsqldb\lib, and you want to run the hsqldb server on port 4288, you 
 * would do:
 * 
 * 1) In a command window, run the hsqldb server by typing:
 * 
 * 		java -classpath c:\hsqldb\lib\hsqldb.jar org.hsqldb.Server -port 4288 -database example
 * 
 * (if the hsqldb.jar file is somewhere else, you'll need to change the classpath
 * to match the actual location)
 * 
 * Be sure that the directory you are in when you run the server is the directory
 * where you want the database files to be created.
 * 
 * 2) In another command window, run the CreateDatabase program to create the table by typing:
 * 
 * 		java -classpath c:\hsqldb\lib\hsqldb.jar;. CreateDatabase 4288
 * 
 * (Again, if hsqldb.jar is located in a different directory, change the classpath
 * to match the actual location)
 * 
 * Note that if you are running this on Unix, replace the semicolon in the
 * classpath with a colon.
 * 
 * You only need to run this program once...in fact, running it a second time should
 * generate an exception because the table already exists.
 * 
 * You should write your server in a similar manner to this, allowing the port number
 * for the database server to be passed into the program...this allows me to run the
 * database server and test programs against it without changing port numbers in your
 * code.  
 * 
 */

public class CreateDatabase
{
	public static void main (String[] args)
	{
		if (args.length != 1)
		{
			System.out.println ("You must provide the port number for hsqldb on the command line");
			return;
		}
		
		/* Load in the JDBC driver for hsqldb */
		try
		{
			Class.forName("org.hsqldb.jdbcDriver");
		} 
		catch (ClassNotFoundException e)
		{
			e.printStackTrace();
			return;
		}
		
		/* Create the connection and the table */
		Connection conn = null;
		Statement stmt = null;

		try
		{
			conn = DriverManager.getConnection("jdbc:hsqldb:hsql://localhost:" + args [0], "sa", "");
			stmt = conn.createStatement();
			stmt.executeUpdate("create table CUSTOMERS (id integer PRIMARY KEY, name varchar (50), state varchar(2), balance double)");
			stmt.executeUpdate("insert into CUSTOMERS values (100, 'Jay Shaffstall', 'OH', 500.0)");
			stmt.executeUpdate("insert into CUSTOMERS values (200, 'John Smith', 'PA', -500.0)");
			stmt.executeUpdate("insert into CUSTOMERS values (300, 'Jennifer Williams', 'WA', 10000.0)");
			conn.close ();
		} 
		catch (SQLException e)
		{
			e.printStackTrace();
			return;
		}
		
		System.out.println ("Database table created successfully!");
	}
}