/*
 * CreateDBTables.java
 * Copyright (c) 2002 Tom B. Gutwin P.Eng.
 *
 * This program is free software; you can redistribute it and/or
 * modify it under the terms of the GNU General Public License
 * as published by the Free Software Foundation; either version 2
 * of the License, or any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.
 */

package ca.bc.webarts.tools;

import ca.bc.webarts.widgets.WrapperApp;
import java.io.*;

import java.sql.*;
import javax.swing.*;
import javax.swing.event.*;



/**
 *  A class to help build a whack of db tables based on a txt file.<BR />
 *  This App Gets called like: java ca.bc.webarts.tools.CreateDBTables -test
 *  false -f IEEEDBTables.txt IEEEVANC
 *
 * @author     tgutwin
 * @created    July 8, 2002
 */

public class CreateDBTables extends JApplet
{

  /** **/
  static int dbIndex = 1;
  /** */
  final static String[] jdbcDriverName = {"org.postgresql.Driver",
                                          "COM.ibm.db2.jdbc.app.DB2Driver",
                                          "org.postgresql.Driver"};
  /** */
  private static String[] dbBrandName = {"PostgreSQL","DB2","PostgreSQL"};
  /** */
  private static String[] dbServerName = {"warp2","Aurora1","warp2"};
  /** */
  private static String[] dbServerPort = {"5432","6790","5432"};
  /** */
  private static String[] dbName = {"test","sample","test"};
  /** */
  private static String[] dbUserId = {"tgutwin","tgutwin","tgutwin"};
  /** */
  private static String[] dbUserPassword = {"18BoogieWoogie",
                                            "GetYourOwnPassword",
                                            "18BoogieWoogie"};
  /** */
  private static String[] dbURL = {
            "jdbc:postgresql://" + dbServerName[0] + ":" + dbServerPort[0] + "/" + dbName[0],
            "jdbc:db2://"        + dbServerName[1] + ":" + dbServerPort[1] + "/" + dbName[1],
            "jdbc:postgresql://" + dbServerName[2] + ":" + dbServerPort[2] + "/" + dbName[2]};
  /** */
  final static String adminEmail = "webadmin@webarts.bc.ca";
  /** */
  final static String adminEmailURL = "<A HREF=\"" + adminEmail + "\">" + adminEmail + "</A>";
  /** */
  final static String jdbcDriverErrorMsg = "Sorry, a JDBC Driver ERROR occurred.  I won't be able to process you request";
  /** */
  final static String sqlErrorMsg = "Sorry, a SQL ERROR occurred.  I won't be able to process your request";
  /** */
  final static String reportErrorMsg = "Please copy the follwing information and email it to " + adminEmail;
  /** */
  final static String fileErrorMsg = "File Error";
  /** */
  static short numColumns = 0;
  /** */
  String tableNm_;
  /** */
  String testTableName = "testTable";
  /** */
  String[][] tableColumns;
  /**
   *  A flag to specify if the DROP command was specified on the commandline. If
   *  this is true the tablename that is specified will be dropped from the DB.
   */
  boolean dropTable = false;

  /**
   *  A flag to specify if the f command was specified on the commandline. If
   *  this is true the a input filename was specified on the commandline that
   *  will be used to get the table info from.
   */
  boolean fileArgFlag = false;

  /**
   *  The filename that is specified on the commandline in conjunction with the
   *  -f switch
   */
  String inFileName = "testTable.txt";

  /** */
  Connection con = null;
  /**
   *  Input <code>File</code> to read in the formname/dbtablecolumnname at
   *  runtime.
   */
  FileReader fileReader = null;
  /**  Tokenized version of the input config file <code>fileReader</code>  */
  StreamTokenizer inputTokens;

  /** */
  static boolean DEBUG = true;

  /**  Description of the Field */
  static String[] inArgs;

  /*
  static
  {
    try
    {
       Class.forName(jdbcDriverName[0]);
      System.out.println("We Registered The Driver!");
    }
    catch (Exception e)
    {
      System.out.println(jdbcDriverErrorMsg);
      System.out.println(e);
      System.out.println(reportErrorMsg);
      System.exit(1);
    }
  }
  */


  /**
   *  the method that return the programmers customized information about this
   *  applet.
   *
   * @return    The appletInfo value
   */
  public String getAppletInfo()
  {
    return "Database Table Creator, Copyright (c) WebARTS Design 2000 - Tom Gutwin P.Eng.";
  }


  /**
   *  the method that returns the information about the possible applet
   *  parameters.
   *
   * @return    a table of Strings (String[][]) holding the input param name,
   *      type and description
   */
  public String[][] getParameterInfo()
  {
    String[][] retVal = {
        {"INPUTFILE",
         "String",
         "the string holding the filename to read the table data "}
        };
    return retVal;
  }


  /**
   *  Initializes everything needed to start/run this applet. It gets called
   *  upon 1st call of the applet. It performs the following initializations:
   *
   *  <UL>
   *    <LI> <B>retrieves the codebase</B>
   *    <LI> <B>retrieves the applet input parameters</B>
   *    <LI> <B>sets the look and feel attribute</B>
   *  </UL>
   *
   */
  public void init()
  {
    setVisible(false);

    /*
     *  get the JApplets root pane & make a fix so this applet does not crash NS or IE
     */
    JRootPane rp = getRootPane();
    rp.putClientProperty("defeatSystemEventQueueCheck", Boolean.TRUE);

    TableCreator(inArgs);
  }


  /**
   * @param  numSwitches  Description of the Parameter
   * @param  switchArgs   Description of the Parameter
   * @return              Description of the Return Value
   */
  private boolean parseSwitches(int numSwitches, String[][] switchArgs)
  {
    boolean retVal = true;
    for (int i = 0; i < numSwitches; i++)
    {
      if (switchArgs[i][0].equals("-f"))
      {
        inFileName = switchArgs[i][1];
        fileArgFlag = true;
      }
      if (switchArgs[i][0].equals("-test"))
      {
        if (switchArgs[i][1].equals("true"))
        {
          DEBUG = true;
        }
        else
        {
          DEBUG = false;
        }
      }
    }
    return retVal;
  }


  /**
   * @return    Description of the Return Value
   */
  private boolean initFileTokens()
  {
    boolean retVal = true;

    try
    {
      System.out.println("Tokenizing file " + inFileName);
      fileReader = new FileReader(inFileName);

      inputTokens = new StreamTokenizer(fileReader);
      inputTokens.eolIsSignificant(true);
      inputTokens.lowerCaseMode(true);
      inputTokens.slashSlashComments(true);
      inputTokens.slashStarComments(true);
    }
    catch (FileNotFoundException e)
    {
      System.err.println(fileErrorMsg);
      System.err.println(reportErrorMsg);
      System.err.println("e.printStackTrace()");
      retVal = false;
    }
    catch (java.lang.NullPointerException e)
    {
      System.err.println(fileErrorMsg);
      System.err.println(reportErrorMsg);
      System.err.println("e.printStackTrace()");
      retVal = false;
    }

    return retVal;
  }


  /**
   * Reads one token from the inputTokens, if it is a StreamTokenizer.TT_WORD
   * it is assumed to be the next table name and puts it in the class tableNm_
   * field.
   *
   * @return    false if success, true if at EOF
   */
  private boolean getNextTableName()
  {
    //returns true if atEOF
    boolean retVal = false;
    String tempStr = "";
    tableNm_ = testTableName;
    int tempResult = 0;

    try
    {
      tempResult = inputTokens.nextToken();
      if (tempResult == StreamTokenizer.TT_WORD)
      {
        tempStr = inputTokens.sval;
        System.out.println("Next Table=" + tempStr);
        tempResult = inputTokens.nextToken();
        // read the EOL
      }
      else
      {
        //       if(tempResult == StreamTokenizer.TT_EOF)
        retVal = true;
      }

    }
    catch (IOException ex)
    {
      System.err.println(fileErrorMsg);
      System.err.println(reportErrorMsg);
      System.err.println("getNextTableName reading file:" + inFileName);
      retVal = true;
    }
    tableNm_ = tempStr;
    return retVal;
  }


  /**
   *  A method to fill the <code>tableColumns</code> static array with the
   *  column information. This information goes directly into the SQL statement
   *  that is used to create the table.
   *
   * @return    The nextTable value
   */
  private short getNextTable()
  {
    numColumns = 0;
    tableColumns = new String[250][3];
    tableColumns[0][0] = "Column1";
    tableColumns[0][1] = "SMALLINT";
    tableColumns[0][2] = "NOT NULL";

    int tempResult = 0;
    int numFields = 0;

    System.out.println("Getting Table Column data...");
    try
    {
      do // while not(EOF, NUMBER)
      {
        System.out.println("Col #"+numColumns);
        do // while not(EOL,EOF, NUMBER)
        {
          System.out.println("Col - "+numColumns);
          tempResult = inputTokens.nextToken();
          if (tempResult == StreamTokenizer.TT_WORD)
          {
            if (inputTokens.sval.trim().equals("notnull"))
            {
              tableColumns[numColumns][numFields++] = "NOT NULL";
            }

            // CHAR
            else if (inputTokens.sval.equals("char") || inputTokens.sval.equals("varchar"))
            {
              tableColumns[numColumns][numFields] = inputTokens.sval;
              tempResult = inputTokens.nextToken();
              if (tempResult == StreamTokenizer.TT_NUMBER)
              {
                tableColumns[numColumns][numFields++] += "(" + inputTokens.nval + ")";
                tempResult = inputTokens.nextToken();
              }
              else
              {
                tableColumns[numColumns][numFields++] += "(254)";
              }
            }

            // datalink URL
            else if (inputTokens.sval.equals("datalink"))
            {
              tableColumns[numColumns][numFields++] = inputTokens.sval + " LINKTYPE URL ";
            }

            // CLOB
            else if (inputTokens.sval.equals("clob"))
            {
              tableColumns[numColumns][numFields] = inputTokens.sval;
              tempResult = inputTokens.nextToken();
              if (tempResult == StreamTokenizer.TT_NUMBER)
              {
                tableColumns[numColumns][numFields++] += "(" + new Integer((int) inputTokens.nval) + " K)";
                tempResult = inputTokens.nextToken();
              }
              else
              {
                tableColumns[numColumns][numFields++] += "(500 K)";
              }
            }
            else
            {
              tableColumns[numColumns][numFields++] = inputTokens.sval;
            }
          }
        } while (   !(tempResult == StreamTokenizer.TT_NUMBER) &&
                    tempResult != StreamTokenizer.TT_EOL &&
                    tempResult != StreamTokenizer.TT_EOF);
        numFields = 0;
        numColumns++;
      } while (!(tempResult == StreamTokenizer.TT_NUMBER) &&
          tempResult != StreamTokenizer.TT_EOF);
      if (tempResult == StreamTokenizer.TT_NUMBER)
      {
        tempResult = inputTokens.nextToken();
        // read and discard the next EOL
        numColumns--;
      }

    }
    catch (IOException ex)
    {
      System.err.println(fileErrorMsg);
      System.err.println(reportErrorMsg);
      System.err.println("getNextTable reading file:" + inFileName);
    }
    return numColumns;
  }


  /**
   * @param  table    Description of the Parameter
   * @param  cols     Description of the Parameter
   * @param  numCols  Description of the Parameter
   * @return          Description of the Return Value
   */
  private boolean createTable(String table, String[][] cols, short numCols)
  {

    String sqlString;
    String columnString = "";
    boolean retVal = true;

    for (int i = 0; i < numCols; i++)
    {
      if (cols[i][1] == null)
      {
        cols[i][1] = "SMALLINT";
      }
      if (cols[i][2] == null)
      {
        cols[i][2] = " ";
      }
    }

    for (int i = 0; i < numCols; i++)
    {
      columnString += cols[i][0] + " " + cols[i][1] + " " + cols[i][2];
      if (i + 1 < numCols)
      {
        columnString += ",\n";
      }
    }
    sqlString = "CREATE TABLE " + table + " ( \n" + columnString + ",\nPrimary KEY(" + cols[0][0] + ") \n)";

    if (!DEBUG)
    {
      try
      {
        Statement stmt = con.createStatement();

        ResultSet rs;
        System.out.println("ExecutingSQL = " + sqlString);
        rs = stmt.executeQuery(sqlString);

        rs.close();
        stmt.close();

      }
      catch (SQLException e)
      {
        System.err.println(sqlErrorMsg);
        System.err.println(reportErrorMsg);
        System.err.println(e.toString());
        System.err.println("Puked While Creating Table:" + table);
        //  e.printStackTrace();
        retVal = false;
      }
    }
    else
    {
      System.out.println("SQL = " + sqlString);
    }
    return retVal;
  }


  /**
   * Constructor.
   *
   * @param  argv  Description of the Parameter
   */
  public void TableCreator(String argv[])
  {
    boolean switchError = false;

    /*
     *  get the commandline parms
     */
    switch (argv.length)
    {
        case 0:
          dbIndex = 0;
          //dbName[0] = "sample";
          System.out.println("NO Commandline DB specified. Using -->" +
                              dbName[dbIndex]);
          break;

        case 1:
          /*
           *  database name
           */
          dbName[0] = argv[0];
          System.out.println("Using DB -->" + dbName[0]);
          break;

        case 2:
          /*
           *  -DROP database name
           */
          if (!argv[0].equals("-DROP"))
          {
            switchError = true;
          }
          dropTable = true;
          dbName[0] = argv[argv.length - 1];
          System.out.println("Using DB -->" + dbName[0]);
          break;

        default:
          /*
           *  get the command switches
           */
          String[][] switches = new String[argv.length][2];
          int i = 0;
          int k = 0;
          while (i < argv.length - 1)
          {
            switches[k][0] = argv[i++];
            switches[k++][1] = argv[i++];
          }
          parseSwitches((int) ((argv.length - 1) / 2), switches);
          dbName[0] = argv[argv.length - 1];
          System.out.println("Using DB -->" + dbName[0]);
    }

    try
    {
      Class.forName(jdbcDriverName[dbIndex]);
      System.out.println("We Registered The Driver!");


      //  construct the URL ( sample is the database name )
      boolean tableCreated = true;
      boolean atEOF = false;
      //  connect to database with userid and password
      System.out.println("Connecting:");
      System.out.println("            "+dbURL[dbIndex]);
      System.out.println("            "+dbUserId[dbIndex]);
      con = DriverManager.getConnection(dbURL[dbIndex], dbUserId[dbIndex], dbUserPassword[dbIndex]);
      System.out.println("Connected.");

      if (!con.isClosed())
      {
        /*
         *  read the nput file to get the next Itablename and columns
         */
        initFileTokens();

        while (!atEOF && tableCreated)
        {

          atEOF = getNextTableName();
          numColumns = getNextTable();
          tableCreated = false;
          if (!atEOF && tableNm_.length() > 0 && numColumns > 0)
          {
            tableCreated = createTable(tableNm_, tableColumns, numColumns);
          }
          else
          {
            if (!atEOF)
            {
              System.err.println(fileErrorMsg);
              System.err.println(reportErrorMsg);
              System.err.println("DBName = " + dbName + ", " + "Table Name =" + tableNm_ + ",     " + numColumns + " columns.");
              atEOF = true;
            }
          }

          if (tableCreated && !DEBUG)
          {
            // retrieve data from the database
            System.out.println("Retrieve some data from the database...");
            Statement stmt = con.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * from " + tableNm_);

            System.out.println("Received results:");

            // display the result set
            // rs.next() returns false when there are no more rows
            while (rs.next())
            {
              String a = rs.getString(1);

              System.out.print(" entry= " + a);
              System.out.print("\n");
            }
            System.out.print("---------------------\n");

            rs.close();
            stmt.close();
            con.commit();
          }
          else
          {
            if (!DEBUG)
            {
              con.rollback();
            }
          }

        }
        con.close();

      }
      else
      {
        System.err.println("Lost the DB Connection");
        System.err.println(reportErrorMsg);
        System.exit(1);
      }
    }
    catch (SecurityException e)
    {
      System.err.println("Input File is Not Readable");
      System.err.println(reportErrorMsg);
      e.printStackTrace(System.err);
    }
    catch (SQLException e)
    {
      System.err.println(sqlErrorMsg);
      System.err.println(reportErrorMsg);
      e.printStackTrace(System.err);

    }
    catch (Exception e)
    {
      System.out.println(jdbcDriverErrorMsg);
      System.out.println(e);
      System.out.println(reportErrorMsg);
      e.printStackTrace(System.err);
      System.exit(1);
    }

    System.out.println("Completed.");
    try
    {
      if (con!=null && !con.isClosed())
      {
        con.close();
      }
    }
    catch (SQLException ex)
    {
      System.err.println("SQLError while attempting to close a residual connection");
    }
  }


  /**
   *  This method gets called if this applet is envoked as an application.
   *
   * @param  argv  Description of the Parameter
   */
  public static void main(String argv[])
  {
//        TableCreator tc = new TableCreator(argv);

    inArgs = argv;

    /*
     *  create an app frame that will start this applet inside it
     */
    WrapperApp app = new WrapperApp("DB Table Creator", new CreateDBTables());
    app.pack();
    app.setSize(800, 600);

    /*
     *  Put the opening app up
     */
    //  app.setVisible(true);
    //  app.setScreenLocation(2,2,0,0);
    app.dispose();
    System.exit(0);
  }
}