/*
 * Copyright 2007-2012 Scott C. Gray
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 * * Unless required by applicable law or agreed to in writing, software * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package org.sqsh;

import java.lang.reflect.Field;
import java.lang.reflect.Modifier;
import java.sql.Connection;
import java.sql.ParameterMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.sql.Types;

import org.sqsh.SqshContext.ExceptionDetail;

/**
 * This class provides a bunch of static methods that help process
 * JDBC results.
 */
public class SQLTools {
    
    
    /**
     * Silently close a result set, ignoring any SQLExceptions.
     * 
     * @param results The results to close, null is silently ignored.
     */
    public static void close(ResultSet results) {
        
        if (results != null) {
            
            try {
                
                results.close();
            }
            catch (SQLException e) {
                
                /* IGNORED */
            }
        }
    }
    
    /**
     * Silently close a statement, ignoring any SQLExceptions.
     * 
     * @param statement The statement to close, null is silently
     *   ignored.
     */
    public static void close(Statement statement) {
        
        if (statement != null) {
            
            try {
                
                statement.close();
            }
            catch (SQLException e) {
                
                /* IGNORED */
            }
        }
    }
    
    /**
     * Silently close a connection, ignoring any SQLExceptions.
     * 
     * @param connection The connection to close, null is silently
     *   ignored.
     */
    public static void close(Connection connection) {
        
        if (connection != null) {
            
            try {
                
                connection.close();
            }
            catch (SQLException e) {
                
                /* IGNORED */
            }
        }
    }
    
    /**
     * Return the current catalog for a connection.
     * 
     * @param connection The connection
     * @return The catalog.
     */
    public static String getCatalog(Connection connection) {
        
        try {
            
            return connection.getCatalog();
        }
        catch (SQLException e) {
            
            return null;
        }
    }
    
    /**
     * Display SQLExceptions (and any nested exceptions) to an output stream
     * in a nicely formatted style.
     * 
     * @param session The session to use for writing the output
     * @param e The exception.
     */
    public static void printException(Session session, SQLException e) {
        
        SQLException origException = e;
        ExceptionDetail detail = session.getContext().getExceptionDetail();
        
        session.setException(e);
        
        session.err.println("SQL Exception(s) Encountered: ");
        while (e != null) {
            
            session.err.print("[State: ");
            session.err.print(e.getSQLState());
            session.err.print("][Code: ");
            session.err.print(e.getErrorCode());
            session.err.print("]");
            
            if (detail != ExceptionDetail.LOW) {
                
                session.err.print(": ");
                session.err.print(e.getMessage());
                session.err.println();
            }
            else {
                
                session.err.println();
                break;
            }
            
            e = e.getNextException();
        }
        
        if (detail == ExceptionDetail.HIGH) {
            
            origException.printStackTrace(session.err);
            if (origException.getCause() != null) {
                
                origException.getCause().printStackTrace(session.err);
            }
        }
        
        session.err.flush();
    }
    
    /**
     * Helper method available to all commands to dump any warnings
     * associated with a connection. The set of warnings is cleared
     * after display.
     * 
     * @param session The session to use for writing
     * @param conn The connection that may, or may not, contain warnings.
     */
    static public void printWarnings(Session session, Connection conn) {
        
        try {
            
            SQLWarning w = conn.getWarnings();
            if (w != null) {
                
                printWarnings(session, w);
                conn.clearWarnings();
            }
        }
        catch (SQLException e) {
            
            /* IGNORED */
        }
    }
    
    /**
     * Helper method available to all commands to dump any warnings
     * associated with a statement. The set of warnings is cleared
     * after display.
     * 
     * @param session The session to use for writing
     * @param statement The statement that may, or may not, contain warnings.
     */
    static public void printWarnings(Session session, Statement statement) {
        
        try {
            
            SQLWarning w = statement.getWarnings();
            if (w != null) {
                
                printWarnings(session, w);
                statement.clearWarnings();
            }
        }
        catch (SQLException e) {
            
            /* IGNORED */
        }
    }
    
    /**
     * Helper method available to all commands to dump any warnings
     * associated with a ResultSet. The set of warnings is cleared
     * after display.
     * 
     * @param session The session to use for writing
     * @param results The ResultSet that may, or may not, contain warnings.
     */
    static public void printWarnings(Session session, ResultSet results) {
        
        try {
            
            SQLWarning w = results.getWarnings();
            if (w != null) {
                
                printWarnings(session, w);
                results.clearWarnings();
            }
        }
        catch (SQLException e) {
            
            /* IGNORED */
        }
    }
    
    /**
     * Helper method to allow all commands to dump any warnings that
     * may have occured due to a JDBC call. This method will dump
     * nothing if no warnings have ocurred.
     * 
     * @param session The session to use for writing
     * @param w The warning.
     */
    static private void printWarnings(Session session, SQLWarning w) {
        
        StringBuilder sb = new StringBuilder();
        String lineSep = System.getProperty("line.separator");
        boolean isError = false;
        
        while (w != null) {
            
            String state = w.getSQLState();
            int code = w.getErrorCode();
            
            /*
             * DB2 has this annoying warning:
             * 
             *   WARN [State:      ][Code: 0]: Statement processing was successful
             *   
             * or this one
             * 
             *   WARN [State: 00000][Code: 0]: Statement processing was successful
             *   
             * So for those, we completely discard them as if they never happened.
             */
            if (! ((isEmptyState(state) && code == 0)
                    || "00000".equals(state) && code == 0)) {
                
                /*
                 * I don't know if this will be true of all JDBC drivers, 
                 * but for certain types of messages I don't like to
                 * show the WARN and error code components. 
                 */
                if (state != null 
                    && "01000".equals(state) == false) {
                    
                    isError = true;
                
                    sb.append("WARN [State: ");
                    sb.append(w.getSQLState());
                    sb.append("][Code: ");
                    sb.append(w.getErrorCode() + "]: ");
                }
            
                sb.append(w.getMessage());
                sb.append(lineSep);
            }
            
            w = w.getNextWarning();
        }
        
        if (isError) {
            
            session.err.print(sb.toString());
            session.err.flush();
        }
        else {
            
            session.out.print(sb.toString());
            session.out.flush();
        }
        
    }
    
    /**
     * Checks to see if a SQLSTATE is "empty".  That is, it either has zero
     * length or is all spaces.
     * 
     * @param sqlState The SQLSTATE to check
     * @return True if it is empty
     */
    private static boolean isEmptyState(String sqlState) {
        
        // A NULL state is not considered empty!
        if (sqlState == null) {
            
            return false;
        }
        
        int len = sqlState.length();
        int idx = 0;
        
        for (idx = 0; idx < len && sqlState.charAt(idx) == ' '; ++idx);
        
        return (idx == len);
    }
    
    /**
     * Given a database object identifier (the name of an object)
     * attempts to determine if the identifier needs quotes around
     * it. The rule is pretty simple, if the identifier contains
     * anything other than letters, digits, or an underscore, it is
     * quoted.
     * 
     * @param identifier The identifier to check
     * @return The identifier or the identifier with quotes, if 
     *   necessary.
     */
    public static String quoteIdentifier (String identifier) {

        int len = identifier.length();
        boolean needQuotes = false;
        for (int j = 0; needQuotes == false && j < len; j++) {

            char ch = identifier.charAt(j);
            if (!(Character.isLetter(ch)
                    || Character.isDigit(ch)
                    || ch == '_')) {

                needQuotes = true;
            }
        }
        
        if (needQuotes) {
            
            StringBuilder sb = new StringBuilder();
            sb.append('"')
                .append(identifier)
                .append('"');
            
            return sb.toString();
        }
        
        return identifier;
    }
    
    /**
     * Given a SQL data type, returns true if the type should be quoted
     * when displayed.
     * @param type The type
     * @return true fi the type should be quoted.
     */
    public static boolean needsQuotes (int type) {
        
        boolean needsQuotes = false;
        
        switch (type) {
        
        case Types.BINARY:
        case Types.VARBINARY:
        case Types.LONGVARBINARY:
        case Types.BLOB:
        case Types.CHAR:
        case Types.VARCHAR:
        case Types.NVARCHAR:
        case Types.LONGNVARCHAR:
        case Types.LONGVARCHAR:
        case Types.NCHAR:
        case Types.CLOB:
        case Types.NCLOB:
        case Types.DATE:
        case Types.TIME:
        case Types.TIMESTAMP:
        case Types.SQLXML:
            needsQuotes = true;
            break;
            
        default:
            break;
        }
        
        return needsQuotes;
    }
    
    
    /**
     * This method is used to convert a {@link java.sql.Types} datatype
     * value to a displayable string. This conversion is done by reflecting
     * the {@link java.sql.Types} class.  The reason this method exists
     * is that the actual string returned by 
     * {@link java.sql.ResultSetMetaData#getColumnTypeName(int)} is dependant
     * upon the driver and does not reflect the actual JDBC datatype.
     * 
     * @param type The type id
     * @return The name of the type
     */
    public static String getTypeName(int type) {
        
        Field []fields = Types.class.getFields();
        for (int i = 0; i < fields.length; i++) {
            
            int flags = fields[i].getModifiers();
            if (Modifier.isPublic(flags)
                    && Modifier.isStatic(flags)
                    && fields[i].getType() == Integer.TYPE) {
                
                try {
                    
                    int typeValue = fields[i].getInt(null);
                    if (typeValue == type) {
                    
                        return fields[i].getName();
                    }
                }
                catch (IllegalAccessException e) {
                    
                    /* IGNORED */
                }
            }
        }
        
        return "[TYPE #" + type + "]";
    }
    
    /**
     * Decodes a parameter mode number into a readable string
     * @param mode The mode
     * @return The string
     */
    public static String getParameterMode(int mode) {
        
        switch (mode) {
        case ParameterMetaData.parameterModeIn: return "IN";
        case ParameterMetaData.parameterModeInOut: return "INOUT";
        case ParameterMetaData.parameterModeOut: return "OUT";
        case ParameterMetaData.parameterModeUnknown: return "UNKNOWN";
        default:
            return "UNRECOGNIZED";
        }
    }
    
    /***
     * Decodes a parameter nullability indicator into a readable string
     * @param isnull The nullability indicator
     * @return The string
     */
    public static String getParameterNullability(int isnull) {
        switch (isnull) {
        case ParameterMetaData.parameterNoNulls: return "NOT NULL";
        case ParameterMetaData.parameterNullable: return "NULL";
        case ParameterMetaData.parameterNullableUnknown: return "UNKNOWN";
        default:
            return "UNRECOGNIZED";
        }
    }
    
    /**
     * Used to parse a database object name of the forms:
     * <pre>
     *    a
     *    a.b
     *    a.b.c
     *    a.b.c.d
     * </pre>
     * and does its best to determine which part of the name is
     * what (e.g. "a.b" is assumed to be "schema.name".
     * 
     * <p>Currently the parsing logic doesn't deal with any sort of
     * quoting, such as [my schema].[my table] or "my schema"."my table";
     * it simply looks at the dots and does the work.
     * 
     * @param name The name to be parsed.
     * @return A parsed version of the name.
     */
    public static ObjectDescription parseObjectName (String name) {
        
        return new ObjectDescription(name);
    }
    
    /**
     * Returned by parseObjectName() as an object description.
     */
    public static class ObjectDescription {
        
        private String name = null;
        private String column = null;
        private String schema = null;
        private String catalog = null;
        
        /**
         * Creates a new object description by parsing the 
         * provided name.
         */
        protected ObjectDescription (String str) {
            
            String []parts = str.split("\\.");
            
            switch (parts.length) {
                
                case 0 :
                    break;
                
                case 1 : 
                    name = parts[0];
                    break;
                
                case 2 :
                    schema = parts[0];
                    name = parts[1];
                    break;
                    
                case 3 :
                    catalog = parts[0];
                    schema = parts[1];
                    name = parts[2];
                    break;
                    
                case 4 :
                default :
                    catalog = parts[0];
                    schema = parts[1];
                    name = parts[2];
                    column = parts[3];
                    break;
            }
        }

        
        /**
         * @return the name
         */
        public String getName () {
        
            return name;
        }

        /**
         * @return the column
         */
        public String getColumn () {
        
            return column;
        }

        /**
         * @return the schema
         */
        public String getSchema () {
        
            return schema;
        }
        
        /**
         * @return the catalog
         */
        public String getCatalog () {
        
            return catalog;
        }
        
        public String toString() {
            
            StringBuilder sb = new StringBuilder();
            if (catalog != null) {
                
                sb.append("[").append(catalog).append("]");
            }
            if (schema != null) {
                
                sb.append("[").append(schema).append("]");
            }
            if (schema != null) {
                
                sb.append("[").append(name).append("]");
            }
            if (column != null) {
                
                sb.append("[").append(column).append("]");
            }
            
            return sb.toString();
        }
    }
}