/*
 * $Id: DBHelper.java,v 1.88 2007/08/14 14:50:06 marbobo Exp $
 *
 * Copyright (c) Siemens AG 2008 
 *           (c) Universität Paderborn 2008 
 * Licensed under EUPL 1.1. 
 *
 * $Log: DBHelper.java,v $
 * Revision 1.88  2007/08/14 14:50:06  marbobo
 * + Licenseverwaltung komplett umgeschrieben
 * + Kategoriesuche und verlorene Kategorien anzeigen komplett umgeschrieben
 * + Fehler beim Speichern von Beschreibenungsfeldern für Entitäten beseitigt.
 *
 * Revision 1.87  2006/07/11 23:40:04  marbobo
 * + PostgreSQL ist pingelig und erlaubt kein get/setBoolean auf Int-Spalten
 *
 */
package vkc.db.helper;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.sql.Timestamp;
import java.util.HashMap;
import java.util.Map;

import vkc.db.VKCDatabase;
import vkc.db.entities.VKCCategory;
import vkc.db.entities.VKCDocument;
import vkc.db.entities.VKCEntity;
import vkc.db.entities.VKCUser;
import vkc.exception.DBException;
import vkc.misc.VKCLogManager;
import vkc.view.WebShare;

/**
 * Hilfklasse für viele nützliche Operationen der VKCDatenbank
 * 
 * @author koppy
 * @version 1.0
 */
public final class DBHelper {
    private static Map keyCache = new HashMap();

    /**
     * Führt ein SQL-Update aus
     * 
     * @param sql
     *            die SQL-Anweisung
     */
    public static void execStmt(Connection conn, String sql) throws SQLException {
        PreparedStatement pStmt = null;

        try {
            pStmt = conn.prepareStatement(sql);
            pStmt.execute();
        } finally {
            DBHelper.close(pStmt);
        }
    }

    /**
     * Hilfsmethode, um Zeilen aus einer Tabelle zu löschen Sollte aus
     * Effizienzgründen nur da verwendet werden, wo nur EINE delete() Operation
     * pro Session stattfindet
     * 
     * @param table
     *            Name der Tabelle
     * @param key
     *            Name des Schlüsselattributes
     * @param value
     *            Wert des Schlüsselattributes
     */
    public static void deleteEntry(Connection conn, String table, String key, Object value) throws SQLException {
        PreparedStatement pStmt = null;

        try {
            pStmt = conn.prepareStatement("DELETE FROM " + table + " WHERE " + key + " = ?");
            setElement(pStmt, 1, value);
            pStmt.execute();
        } finally {
            DBHelper.close(pStmt);
        }
    }

    /**
     * Method returning a new, unique identifier for the given table column.
     * 
     * @param sess
     * @param table
     *            The table, a new identifier is requested for.
     * @param col
     *            The table column, a new identifier is requested for.
     * 
     * @return A new, unique identifier.
     */
    public static synchronized Integer getNewId(Connection conn, String table, String col) throws DBException {
        String driver = (String) WebShare.getProperties().get("vkc.db.driver_class");

        return getNewId(conn, WebShare.database.getDbUrl(), driver, table, col);
    }

    public static synchronized Integer getNewId(Connection conn, String dbUrl, String driver, String table, String col) throws DBException {
        int lastKey = -1;
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
            if (driver.equals(VKCDatabase.FIREBIRD_DRIVER)) {
                // firebird driver
                pStmt = conn.prepareStatement("SELECT GEN_ID(" + table.toUpperCase() + "_SEQ, 1) AS newKeyId FROM RDB$DATABASE");
                rs = pStmt.executeQuery();
                rs.next();
                lastKey = rs.getInt("newKeyId");
            } else if (driver.equals(VKCDatabase.ORACLE_DRIVER)) {
                // oracle driver
                // following statement result everytime in on row!
                pStmt = conn.prepareStatement("SELECT " + table.toUpperCase() + "_SEQ.nextval AS newKeyId FROM acltypes WHERE acltypeId = 1");
                rs = pStmt.executeQuery();
                rs.next();
                lastKey = rs.getInt("newKeyId");
            } else if (driver.equals(VKCDatabase.POSTGRES_DRIVER)) {
                // postgres driver
                pStmt = conn.prepareStatement("SELECT nextval('" + table.toUpperCase() + "_SEQ') AS newKeyId FROM acltypes WHERE acltypeId = 1");
                rs = pStmt.executeQuery();
                rs.next();
                lastKey = rs.getInt("newKeyId");
            } else {
                // all others, if unknown driver
                String keyID = dbUrl + "___" + table + "___" + col;
                Integer lastKeyObj = (Integer) keyCache.get(keyID);

                if (lastKeyObj == null) {
                    pStmt = conn.prepareStatement("SELECT MAX(" + col + ") AS " + col + " FROM " + table);
                    rs = pStmt.executeQuery();
                    rs.next();
                    lastKey = rs.getInt(col);

                    if (rs.wasNull()) {
                        lastKey = 1;
                    }
                } else {
                    // If last key was stored in hashtable, remove it from
                    // hashtable
                    // so that the new one could be inserted.
                    lastKey = lastKeyObj.intValue();
                }

                // Increment last key value und store it in hashtable.
                lastKey++;
                keyCache.put(keyID, new Integer(lastKey));
            }

            return new Integer(lastKey);
        } catch (Exception ex) {
            VKCLogManager.errorLog(DBHelper.class, "", ex.getMessage(), ex);
            throw new DBException(ex);
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }

    /**
     * Returns the id of the given entity (Better solution would be polymorphism
     * but their is too much to change to realize that approach yet)
     * 
     * @param entity
     *            the reference on the VKCEntity object
     * @return the corresponding ID
     */
    public static final Integer getId(VKCEntity entity) {
        if (entity instanceof VKCCategory) {
            return ((VKCCategory) entity).getCategoryId();
        }

        if (entity instanceof VKCDocument) {
            return ((VKCDocument) entity).getDocumentId();
        }

        if (entity instanceof VKCUser) {
            return ((VKCUser) entity).getUserId();
        }

        return null;
    }

    /**
     * Führt ein Rollback durch
     * 
     * @return true, falls erfolgreich, false sonst
     * @author koppy
     */
    public static final boolean rollBack(Connection c) {
        if (c == null) {
            return true;
        }

        try {
            c.rollback();

            return true;
        } catch (SQLException ex) {
            VKCLogManager.errorLog(DBHelper.class, "", "ROLLBACK connection: " + ex.getMessage(), ex);
        }

        return false;
    }

    /**
     * Schließt eine Connection
     * 
     * @return true, falls erfolgreich geschlossen, false sonst
     * @author koppy
     */
    public static final void close(Connection c) {
        try {
            if (c != null) {
                c.close();
                c = null;
            }
        } catch (SQLException e) {
            VKCLogManager.errorLog(DBHelper.class, "", "CLOSE connection: " + e.getMessage(), e);
        }
    }

    /**
     * Schließt ein Statement
     * 
     * @return true, falls erfolgreich geschlossen, false sonst
     * @author koppy
     */
    public static final void close(Statement s) {
        try {
            if (s != null) {
                s.close();
                s = null;
            }
        } catch (SQLException e) {
            VKCLogManager.errorLog(DBHelper.class, "", "CLOSE statement: " + e.getMessage(), e);
        }
    }

    /**
     * Schließt ein ResultSet
     * 
     * @return true, falls erfolgreich geschlossen, false sonst
     * @author koppy
     */
    public static final void close(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
                rs = null;
            }
        } catch (SQLException e) {
            VKCLogManager.errorLog(DBHelper.class, "", "CLOSE resultset: " + e.getMessage(), e);
        }
    }

    /**
     * Prueft, ob ein Eintrag in einer Tabelle existiert
     * 
     * @param conn
     *            die DB-Verbindung
     * @param table
     *            Name der Tabelle
     * @param column
     *            Name der Tabellenspalte
     * @param value
     *            Wert Tabellenspalte
     * @throws SQLException
     */
    public static boolean existsEntry(Connection conn, String table, String column, Object value) throws SQLException {
        PreparedStatement pStmt = null;
        ResultSet rs = null;

        try {
            // String[] keys = new String[] { column };
            // Object[] values = new Object[] { value };
            StringBuffer sb = new StringBuffer();

            sb.append("SELECT * FROM ");
            sb.append(table);
            sb.append(" WHERE ");
            sb.append(column);
            sb.append(" = ?");

            pStmt = conn.prepareStatement(sb.toString());
            pStmt.setString(1, (value == null) ? "-1" : value.toString());
            rs = pStmt.executeQuery();

            return rs.next();
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }

    /**
     * Prueft, ob ein Eintrag in einer Tabelle existiert
     * 
     * @param conn
     *            die DB-Verbindung
     * @param table
     *            Name der Tabelle
     * @param column
     *            Namen der Tabellenspalten
     * @param values
     *            Werte des Tabellenspalten
     * @throws SQLException
     */
    public static boolean existsEntry(Connection conn, String table, String[] column, Object[] values) throws SQLException {
        ResultSet rs = null;
        PreparedStatement pStmt = null;

        try {
            StringBuffer sb = new StringBuffer();

            sb.append("SELECT * FROM ");
            sb.append(table);
            sb.append(" WHERE ");

            int size = column.length;

            for (int i = 0; i < size; i++) {
                sb.append(column[i]);
                sb.append(" = ?");

                if (i + 1 < size) {
                    sb.append(" AND ");
                }
            }

            pStmt = conn.prepareStatement(sb.toString());

            for (int i = 0; i < size; i++) {
                pStmt.setString(i + 1, (values[i] == null) ? "-1" : values[i].toString());
            }
            rs = pStmt.executeQuery();

            return rs.next();
        } finally {
            DBHelper.close(rs);
            DBHelper.close(pStmt);
        }
    }

    public static final void setElements(PreparedStatement pStmt, Object[] values) throws SQLException {
        if (values != null) {
            for (int i = 0; i < values.length; i++) {
                setElement(pStmt, i + 1, values[i]);
            }
        }
    }

    public static final void setElement(PreparedStatement pStmt, int index, Object value) throws SQLException {
        if (value instanceof Integer) {
            pStmt.setInt(index, ((Integer) value).intValue());
        } else if (value instanceof Long) {
            pStmt.setLong(index, ((Long) value).longValue());
        } else if (value instanceof Short) {
            pStmt.setShort(index, ((Short) value).shortValue());
        } else if (value instanceof BigDecimal) {
            pStmt.setBigDecimal(index, (BigDecimal) value);
        } else if (value instanceof Byte) {
            pStmt.setByte(index, ((Byte) value).byteValue());
        } else if (value instanceof Byte[]) {
            Byte[] values = (Byte[]) value;
            byte[] bytes = new byte[values.length];

            for (int i = 0; i < values.length; i++) {
                bytes[i] = values[i].byteValue();
            }

            pStmt.setBytes(index, bytes);
        } else if (value instanceof Float) {
            pStmt.setFloat(index, ((Float) value).floatValue());
        } else if (value instanceof Double) {
            pStmt.setDouble(index, ((Double) value).doubleValue());
        } else if (value instanceof Boolean) {
            pStmt.setInt(index, ((Boolean) value).booleanValue() ? 1 : 0);
        } else if (value instanceof Date) {
            pStmt.setDate(index, (Date) value);
        } else if (value instanceof Time) {
            pStmt.setTime(index, (Time) value);
        } else if (value instanceof Timestamp) {
            pStmt.setTimestamp(index, (Timestamp) value);
        } else if (value instanceof String) {
            pStmt.setString(index, (String) value);
        } else {
            pStmt.setObject(index, value);
        }
    }
}