package coreservlets;

import java.sql.*;
import coreservlets.beans.*;

/** Create a simple table named "music" in the
 *  database specified on the command line. The driver
 *  for the database is loaded from the utility class
 *  DriverUtilities.
 *  <P>
 *  Taken from Core Servlets and JavaServer Pages 2nd Edition
 *  from Prentice Hall and Sun Microsystems Press,
 *  http://www.coreservlets.com/.
 *  &copy; 2003 Marty Hall and Larry Brown.
 *  May be freely used or adapted.
 */

public class CreateMusicTable {
  public static void main(String[] args) {
    if (args.length < 5) {
      printUsage();
      return;
    }
    String vendor = args[4];
    // Change to DriverUtilities2.loadDrivers() to
    // load the drivers from an XML file.
    DriverUtilities.loadDrivers();
    if (!DriverUtilities.isValidVendor(vendor)) {
      printUsage();
      return;
    }
    String driver = DriverUtilities.getDriver(vendor);
    String host = args[0];
    String dbName = args[1];
    String url =
      DriverUtilities.makeURL(host, dbName, vendor);
    String username = args[2];
    String password = args[3];
    String format =
      "(id INTEGER, composer VARCHAR(16), " +
      " concerto VARCHAR(24), available INTEGER, " +
      " price FLOAT)";
    String[] rows = {
      "(1, 'Mozart',      'No. 21 in C# minor',  7, 24.99)",
      "(2, 'Beethoven',   'No. 3 in C minor',   28, 10.99)",
      "(3, 'Beethoven',   'No. 5 Eb major',     33, 10.99)",
      "(4, 'Rachmaninov', 'No. 2 in C minor',    9, 18.99)",
      "(5, 'Mozart',      'No. 24 in C minor',  11, 21.99)",
      "(6, 'Beethoven',   'No. 4 in G',         33, 12.99)",
      "(7, 'Liszt',       'No. 1 in Eb major',  48, 10.99)" };
    Connection connection =
      ConnectionInfoBean.getConnection(driver, url,
                                       username, password);
    createTable(connection, "music", format, rows);
    try {
      connection.close();
    } catch(SQLException sqle) {
      System.err.println("Problem closing connection: " + sqle);
    }
  }

  /** Build a table with the specified format and rows. */

  private static void createTable(Connection connection,
                                  String tableName,
                                  String tableFormat,
                                  String[] tableRows) {
    try {
      Statement statement = connection.createStatement();
      // Drop previous table if it exists, but don't get
      // error if not. Thus, the separate try/catch here.
      try {
        statement.execute("DROP TABLE " + tableName);
      } catch(SQLException sqle) {}
      String createCommand =
        "CREATE TABLE " + tableName + " " + tableFormat;
      statement.execute(createCommand);
      String insertPrefix =
        "INSERT INTO " + tableName + " VALUES";
      for(int i=0; i<tableRows.length; i++) {
        statement.execute(insertPrefix + tableRows[i]);
      }
    } catch(SQLException sqle) {
      System.err.println("Error creating table: " + sqle);
    }
  }

  private static void printUsage() {
     System.out.println("Usage: CreateMusicTable host dbName " +
                        "username password vendor.");
  }
}