/*   Copyright (C) 2003 Finalist IT Group
    *
    *   This file is part of JAG - the Java J2EE Application Generator
    *
    *   JAG 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
    *   (at your option) any later version.
    *   JAG 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 JAG; if not, write to the Free Software
   *   Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
   */
  
  package com.finalist.jaggenerator;
  
  import org.apache.commons.logging.Log;
  import org.apache.commons.logging.LogFactory;
  
  import java.util.ArrayList;
  import java.util.HashMap;
  import java.util.List;
  import java.util.Collections;
  import java.sql.Connection;
  import java.sql.ResultSet;
  import java.sql.SQLException;
  import java.sql.DatabaseMetaData;
  
  /***
   * This class is a refactoring mid-step.  The original code contained duplicate methods for database access
   * in different classes - I've rehoused them here.
   *
   * @author Michael O'Connor, Rudie Ekkelenkamp - Finalist IT Group
   */
  public class DatabaseUtils {
     static Log log = LogFactory.getLog(DatabaseUtils.class);
  
     //a map of table name (String) --> ArrayList of Column objects.
     private static final HashMap columnsCache = new HashMap();
  
     //a list of table names (String)
     private static ArrayList tablesCache;
  
     //I think this can be refactored out, once getPrimaryKeys() reference in JagGenerator is removed..
     private static final HashMap pkCache = new HashMap();
     private static final String TABLE_NAME = "TABLE_NAME";
     private static final String[] DEFAULT_TABLE_TYPES = new String[]{"TABLE"};
  
  
     /***
      * Gets all columns in the specified table, setting up a database connection if one isn't already available.
      *
      * @param tablename the name of the table.
      * @return an ArrayList of Column objects for all columns in the specified table,
      *         or <code>null</code> if the table/column doesn't exist.
      */
     public static ArrayList getColumns(String tablename) {
        return getColumns(tablename, true);
     }
  
     /***
      * Gets all columns in the specified table.
      *
      * @param tablename       the name of the table.
      * @param forceConnection set to <code>true</code> if this method should force a database connect,
      *                        if not already connected.
      * @return an ArrayList of Column objects for all columns in the specified table;
      *         or <code>null</code> if the table/column doesn't exist, or if no database connection was available and
      *         <code>forceConnection</code> was set to <code>false</code>.
      */
     public static ArrayList getColumns(String tablename, boolean forceConnection) {
        if (columnsCache.get(tablename) != null) {
           return (ArrayList) columnsCache.get(tablename);
        }
  
        if (!forceConnection && JagGenerator.getConManager() == null) {
           return null;
        }
        ArrayList pkeys = getPrimaryKeys(tablename);
        GenericJdbcManager conManager = JagGenerator.getConManager();
        Connection con = null;
        ArrayList list = new ArrayList();
        try {
           con = conManager.connect();
           DatabaseMetaData meta = con.getMetaData();
           ResultSet columns = meta.getColumns(null, conManager.getSchema(), tablename, "%");
           Column c = null;
           while (columns.next()) {
              c = new Column();
              switch (columns.getInt("NULLABLE")) {
                 case DatabaseMetaData.columnNullable:
                    c.setNullable(true);
                    break;
                 case DatabaseMetaData.columnNoNulls:
                    c.setNullable(false);
                    break;
                case DatabaseMetaData.columnNullableUnknown:
                   c.setNullable(false);
                default:
                   c.setNullable(true);
             }
 
             c.setName(columns.getString("COLUMN_NAME"));
             if (pkeys.contains(columns.getString("COLUMN_NAME"))) {
                c.setPrimaryKey(true);
             } else {
                c.setPrimaryKey(false);
             }
 
             c.setLength(columns.getInt("COLUMN_SIZE"));
             c.setPrecision(columns.getInt("COLUMN_SIZE"));
             c.setScale(columns.getInt("DECIMAL_DIGITS"));
             c.setSqlType(columns.getString("TYPE_NAME"));
             list.add(c);
          }
          columns.close();
 
       } catch (Exception e) {
          e.printStackTrace();
       } finally {
          if (con != null) {
             try {
                con.close();
             } catch (SQLException e) {
             }
          }
       }
 
       columnsCache.put(tablename, list);
       return list;
    }
 
    /***
     * Gets information about any foreign keys that are imported into the specified table.
     *
     * @param tablename
     * @return a List of ForeignKey objects, never <code>null</code>..
     */
    public static List getForeignKeys(String tablename) {
       log.debug("Get the foreign keys for table: " + tablename);
       ArrayList fkeys = new ArrayList();
       GenericJdbcManager conManager = JagGenerator.getConManager();
       if (conManager == null) {
          JagGenerator.logToConsole("Can't retrieve foreign keys - no database connection!");
       } else {
          Connection con = null;
          try {
             con = conManager.connect();
             ResultSet foreignKeys = con.getMetaData().getImportedKeys("", conManager.getSchema(), tablename);
 
             while (foreignKeys.next()) {
                ForeignKey fk = new ForeignKey();
                try {
                   fk.setPkTableCat(foreignKeys.getString("PKTABLE_CAT"));
                } catch (Exception e) {
                   // Ignore, in case the JDBC driver doesn't support this propery.
                }
                try {
                   fk.setPkTableSchem(foreignKeys.getString("PKTABLE_SCHEM"));
                } catch (Exception e) {
                   // Ignore, in case the JDBC driver doesn't support this propery.
                }
                try {
                   fk.setPkTableName(foreignKeys.getString("PKTABLE_NAME"));
                } catch (Exception e) {
                   // Ignore, in case the JDBC driver doesn't support this propery.
                }
                try {
 
                   fk.setPkColumnName(foreignKeys.getString("PKCOLUMN_NAME"));
                } catch (Exception e) {
                   // Ignore, in case the JDBC driver doesn't support this propery.
                }
                try {
                   fk.setFkTableCat(foreignKeys.getString("FKTABLE_CAT"));
                } catch (Exception e) {
                   // Ignore, in case the JDBC driver doesn't support this propery.
                }
                try {
 
                   fk.setFkTableSchem(foreignKeys.getString("FKTABLE_SCHEM"));
                } catch (Exception e) {
                   // Ignore, in case the JDBC driver doesn't support this propery.
                }
                try {
 
                   fk.setFkTableName(foreignKeys.getString("FKTABLE_NAME"));
                } catch (Exception e) {
                   // Ignore, in case the JDBC driver doesn't support this propery.
                }
                try {
 
                   fk.setFkColumnName(foreignKeys.getString("FKCOLUMN_NAME"));
                } catch (Exception e) {
                   // Ignore, in case the JDBC driver doesn't support this propery.
                }
                try {
 
                   fk.setKeySeq(foreignKeys.getShort("KEY_SEQ"));
                } catch (Exception e) {
                   // Ignore, in case the JDBC driver doesn't support this propery.
                }
                try {
                   fk.setUpdateRule(foreignKeys.getShort("UPDATE_RULE"));
                } catch (Exception e) {
                   // Ignore, in case the JDBC driver doesn't support this propery.
                }
                try {
 
                   fk.setDeleteRule(foreignKeys.getShort("DELETE_RULE"));
                } catch (Exception e) {
                   // Ignore, in case the JDBC driver doesn't support this propery.
                }
                try {
 
                   fk.setPkName(foreignKeys.getString("PK_NAME"));
                } catch (Exception e) {
                   // Ignore, in case the JDBC driver doesn't support this propery.
                }
                try {
 
                   fk.setDeferrability(foreignKeys.getShort("DEFERRABILITY"));
                } catch (Exception e) {
                   // Ignore, in case the JDBC driver doesn't support this propery.
                }
                // Log the tables and columns from which relations are generated.
                log.debug("Foreign key table and column name: " + fk.getFkTableName() + " - " + fk.getFkColumnName());
                log.debug("foreign table and pk column name: " + fk.getPkTableName() + " - " + fk.getPkColumnName());
 
                // Since the fk name is not set, we use the column name to set it.s
                fk.setFkName(Utils.format(fk.getFkColumnName()));
                fkeys.add(fk);
             }
          } catch (Exception e) {
             e.printStackTrace();
          } finally {
             if (con != null) {
                try {
                   con.close();
                } catch (SQLException e) {
                }
             }
          }
       }
 
       return fkeys;
    }
 
    /***
     * A list with Strings of all primary key fields.
     *
     * @param tablename
     * @return an ArrayList of primary key column names for the specified table, never <code>null</code>.
     * @todo make this private - all primary key work should be done in this class.
     */
    public static ArrayList getPrimaryKeys(String tablename) {
       if (pkCache.get(tablename) != null) {
          return (ArrayList) pkCache.get(tablename);
       }
       GenericJdbcManager conManager = JagGenerator.getConManager();
       Connection con = null;
       ArrayList pkeys = new ArrayList();
       try {
          con = conManager.connect();
          ResultSet r = con.getMetaData().getPrimaryKeys(null, conManager.getSchema(), tablename);
          while (r.next()) {
             pkeys.add(r.getString("COLUMN_NAME"));
          }
       } catch (Exception e) {
          e.printStackTrace();
       } finally {
          if (con != null) {
             try {
                con.close();
             } catch (SQLException e) {
             }
          }
       }
 
       pkCache.put(tablename, pkeys);
       return pkeys;
    }
 
    /***
     * Grabs the list of tables from the database.
     *
     * @return a List of table names (String), never <code>null</code>.
     */
    public static ArrayList getTables() {
       if (tablesCache == null) {
          tablesCache = new ArrayList();
          GenericJdbcManager conManager = JagGenerator.getConManager();
          String[] displayTableTypes = conManager.getDisplayTableTypes();
          if (displayTableTypes == null) {
             displayTableTypes = DEFAULT_TABLE_TYPES;
          }
          ResultSet tables = null;
          Connection con = null;
          try {
             con = conManager.connect();
             // Look for tables of the type table, not for synonyms: ,"SYNONYM"
             ResultSet schemas = con.getMetaData().getSchemas();
             while (schemas.next()) {
                Object o = schemas.getObject(1);
                System.out.println("schema:" + o);
             }
 
             tables = con.getMetaData().getTables(null, conManager.getSchema(), "%", displayTableTypes);
 
             while (tables.next()) {
                // Iterate over the tablenames and get the tablenames.
                String tableName = tables.getString(TABLE_NAME);
                if (tableName != null) {
                   tablesCache.add(tableName);
                }
             }
          } catch (Exception e) {
             e.printStackTrace();
             JagGenerator.logToConsole("Error getting tables list: " + e.toString());
 
          } finally {
 
             if (tables != null)
                try {
                   tables.close();
                } catch (Exception e) {
                }
 
             if (con != null)
                try {
                   con.close();
                } catch (SQLException e) {
                }
          }
       }
       if (tablesCache != null)
          Collections.sort(tablesCache);
       return tablesCache;
    }
 
    /***
     * This needs to be called when databases are switched.
     */
    public static void clearCache() {
       tablesCache = null;
    }
 
    /***
     * Forces an update of a particular table's columns the next time they are required.
     *
     * @param tableName
     */
    public static void clearColumnsCacheForTable(String tableName) {
       columnsCache.remove(tableName);
    }
 
 }