/*
 * Scone - The Web Enhancement Framework
 * Copyright (C) 2009 Harald Weinreich, Volkert Buchmann, Frank Wollenweber, Torsten Haß
 *
 * 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 (at your option) 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 scone.netobjects;


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import scone.util.ErrorLog;
import scone.util.PersistentProperties;


/**
 * stores the database meta information about a TableRecord.<br>
 * i.e. it knows how to map a TableRecord obect to and from a database.
 * Database fields are seperated into three cases:
 * <ul>
 *  <li> The generated key (autoincrement)
 *  <li> The composed key (may consist of more than one field)
 *  <li> The normal fields
 * </ul>
 * <br>
 * DBTableAdapter knows the name, type and default value of each field.
 *
 * @author Harald Weinreich
 * @author Volkert Buchmann
 */

public class DBTableAdapter {

    public static final String COPYRIGHT = "(C) Harald Weinreich & Volkert Buchmann";

    // column types
    /**
     * Column type in database is String: quote values
     */
    public static final int STRING = 0;

    /**
     * Column type in database is Numerical: do not quote values
     */
    public static final int NUMBER = 1;

    /**
     * Column type in database is Blob: quote values
     */
    public static final int BLOB = 2;

    // key types
    /**
     * Generated Key: AUTO_INCREMENT
     */
    public static final int GENERATED = 0;

    /**
     * Key value field: Use to select record in database for update operation
     */
    public static final int KEY = 1;

    /**
     * Ordinary field
     */
    public static final int FIELD = 2;

    // definition of fields
    /**
     * Array of field names
     */
    protected String[] fieldNames;      // feldnamen

    /**
     * Array of field types: STRING, NUMBER, BLOB
     */
    protected int[] fieldTypes;         // trennzeichen in mysql-anweisungen

    /**
     * Array with default values of fields
     */
    protected String[] fieldDefaults;   // default-werte
    // this variable is used in addField to know how many fields have been defined yet
    /**
     * Number of fields per record
     */
    protected int fieldCount = 0;

    // definitions of keys
    protected int[] keyTypes;           // wer ist key?
    // this variable is used to store the number of keys used for the table.
    protected int keyNumber = 0;
    // stores the line of the autoincrement key
    protected int autoincrementLine = -1;

    /**
     * The name of the database table
     */
    protected String tableName;
    protected Connection con;

    /**
     * The generated key, used if database is not available (con==null || useDB==false)
     */
    protected int generatedKey = 1;

    /**
     * creates a new DBTableAdapter object
     * @param tableName the name of the table
     */
    public DBTableAdapter(String tableName) {

        this.tableName = tableName;

        // create a connection if we use the database
        if (useDb()) {
            this.con = getConnection();
        }

        // read the fields for this object from the sql schema
        Schema.initialize(this);
    }

    /**
     * initializes the internal definition tables
     * @param i the number of fields
     */
    void init(int i) {
        fieldNames = new String[i];
        fieldTypes = new int[i];
        fieldDefaults = new String[i];
        keyTypes = new int[i];
    }

    /**
     * Returns the name of this table
     * @return table name in Scone database.
     */
    public String getTableName() {
        return tableName;
    }

    /**
     * adds a field to the table definition. This is called by scone.netobjects.Schema
     * @param fieldName the name of the field
     * @param fieldType the type of the field. Legal values are: <code>String</code>, <code>Number</code>
     * @param fieldDefault the default value in String format
     * @param keyType whether the field is part of a key.
     */
    public void addField(String fieldName, int fieldType, String fieldDefault, int keyType) {
        if (keyType == GENERATED) {
            autoincrementLine = fieldCount;
        }
        fieldNames[fieldCount] = fieldName;
        fieldTypes[fieldCount] = fieldType;
        fieldDefaults[fieldCount] = fieldDefault;
        keyTypes[fieldCount] = keyType;
        fieldCount++;
        if (keyType != FIELD) {
            keyNumber++;
        }		// Number of keys for this Table
    }

    /**
     * creates a new FieldValueHashTable with the names and defaults of the fields
     * @param set the TableRecord
     * @return the FieldValueHashTable object
     */

    protected FieldValueHashTable initFields(TableRecord set) {
        FieldValueHashTable fieldValues = new FieldValueHashTable(set);

        for (int i = 0; i < fieldNames.length; i++) {
            fieldValues.put(fieldNames[i], fieldDefaults[i]);
        }
        return fieldValues;
    }

    /**
     * initializes a TableRecord object.<br>
     * the fieldValues are created and set to the defaults
     * @param set the TableRecord object
     */
    public void init(TableRecord set) {
        set.fieldValues = initFields(set);
        set.setChanged(true);
    }

    /**
     * tries to find an equivalent set in the database or creates one if it does not exist.<br>
     * This method will only work correctly if the value of the autoincrement key <bR>
     * or the composed key in the TableRecord object have been set!
     * @param set the TableRecord object
     */

    public void dbInit(TableRecord set) {
        if (!getValuesFromDb(set)) {
            createInDB(set);
        }
    }

    /**
     * tries to read from the database an fills the TableRecord if a set exists.
     * Otherwise it sets TableRecord.hasRecordInDB to false.
     * @param set the TableRecord
     */

    public void dbCheck(TableRecord set) {
        set.hasRecordInDB = getValuesFromDb(set);
    }

    /**
     * creates a new Set in the database
     * @param set the TableRecord
     */
    public void dbCreate(TableRecord set) {
        createInDB(set);
    }

    /**
     * writes the values from the TableRecord into the database
     * @param set the TableRecord
     */
    public void updateDB(TableRecord set) {
        synchronized (set) {
            // do nothing if data has not been changed
            // or set does not correspond to a set in the database
            if (set.hasChanged && set.hasRecordInDB && set.persistent) {
                String query = "update " + tableName + " set";
                String delimiter = "";

                // generate "fieldname=fieldvalue ..." string
                for (int i = 0; i < fieldNames.length; i++) {
                    // update only non-key fields!
                    if (keyTypes[i] == FIELD) {
                        if (fieldTypes[i] == STRING || fieldTypes[i] == BLOB) { // Strings must be escaped in put in brackets...
                            query += " "
                                    + fieldNames[i] + "= " + "\'"
                                    + escape(set.fieldValues.get(fieldNames[i]))
                                    + "\',";
                        } else { // Numbers must not be escaped and no brackets...
                            query += " " + fieldNames[i] + "= "
                                    + set.fieldValues.get(fieldNames[i])
                                    + ",";
                        }
                    }
                }

                // do nothing if there are no fields!
                if (query.length()
                        == (new String("update " + tableName + " set")).length()) {
                    return;
                }

                // delete the last comma
                query = query.substring(0, query.length() - 1);

                // add where-clause
                query += getWhereClause(set);

                // System.out.println(query);
                if (set.persistent && useDb() && con != null)  // Shall DB be used???
                {
                    try {
                        synchronized (con) {
                            Statement stmt = con.createStatement();
                            int results = stmt.executeUpdate(query);

                            // update db only if data has been changed again
                            set.setChanged(false);
                        }
                    } catch (SQLException e) { //(com.mysql.jdbc.PacketTooBigException e) {
                        ErrorLog.log(this, "updateDB(TableRecord set)", "Could not store in database! " + query, e);
                        System.out.println("-> Scone: Could not store in database.");
                        System.out.println("   Possible error: data packet too big.");
                        System.out.println("   Hints: Please set in [mysqld]-Section of my.ini:");
                        System.out.println("     'set-variable = max_allowed_packet=16M' (for mysql<=4.0)");
                        System.out.println("     'max_allowed_packet=16M' (for mysql=>4.1)");
                        System.out.println("     and add '?jdbcCompliantTruncation=false' to connection string (4.1).");
                        dbError();
                    } catch (Exception e) {
                        ErrorLog.log(this, "updateDB(TableRecord set)", "Could not store in database! " + query, e);
                        System.out.println("-> Scone: ERROR! Could not store in database:\n " + query);
                        dbError();
                    }
                } else {// set.setChanged(false); // set to weak references??
                }
            }
        }
    }

    /**
     * tries to load the values corresponding to the TableRecord from the database.
     * @param set the TableRecord
     * @return true if the method was successfull, false otherwise
     */
    public boolean getValuesFromDb(TableRecord set) {

        if (!useDb() || con == null) {
            return false;
        }  // No Database in use...

        String query = "select * from " + tableName + getWhereClause(set);

        // System.out.println(query);
        synchronized (set) {
            try {
                synchronized (con) {
                    Statement stmt = con.createStatement();
                    ResultSet results = stmt.executeQuery(query);

                    return fill(set, results);
                }
            } catch (Exception e) {
                ErrorLog.log(this, "getValuesFromDb(TableRecord set)", "Could not read from database! " + query, e);
                System.out.println("-> Scone: ERROR! Could not read from database:\n " + query);
                dbError();
            }
            return false;
        }
    }

    /**
     * tries to load the values corresponding to the TableRecord from the database.
     * @param con A connection to the DB
     * @param tableName The name of the table in the scone database
     * @param sqlClause A where or order by clause for the querey.
     * @return A Resultset with the found objects
     */
    public static ResultSet queryDb(Connection con, String tableName, String sqlClause) {

        if (!useDb() || con == null) {
            return null;
        }  // No Database in use...

        String query = "select * from " + tableName + " " + sqlClause;

        // System.out.println(query);
        try {
            Statement stmt = con.createStatement();
            ResultSet results = stmt.executeQuery(query);

            return results;
        } catch (Exception e) {
            ErrorLog.log(tableName, "DBTableAdapter.queryDb()", "Could not read from database! " + query, e);
            System.out.println("-> Scone: ERROR! Could not read from database:\n " + query);
            dbError();
        }
        return null;
    }

    /**
     * Tries to get the number of rows in the database to a given query.
     * @param con A connection to the DB
     * @param tableName The name of the table in the scone database
     * @param sqlClause A where or order by clause for the querey.
     * @return An integer with the number of found objects. Returns -1 for error.
     */
    public static int rowCountDb(Connection con, String tableName, String sqlClause) {

        if (!useDb() || con == null) {
            return -1;
        }  // No Database in use...

        String query = "select count(*) from " + tableName + " " + sqlClause;

        // System.out.println(query);
        try {
            Statement stmt = con.createStatement();
            ResultSet results = stmt.executeQuery(query);

            if (results.next()) {
            	int result = results.getInt(1);
	            con.close();
                return result;
            }
            con.close();
            return -1;
        } catch (Exception e) {
            ErrorLog.log(tableName, "DBTableAdapter.rowCountDb()", "Could not read from database! " + query, e);
            System.out.println("-> Scone: ERROR! Could not read from database:\n " + query);
            dbError();
        }
        return -1;
    }

    /**
     * tries to read from the ResultSet into the TableRecord.<br>
     * @param set the TableRecord
     * @param results the ResultSet
     * @return false, if the ResultSet conatined no data, true otherwise
     */
    protected boolean fill(TableRecord set, ResultSet results) {
        synchronized (set) {
            try {
                if (results.next()) {

                    // read all field values
                    for (int i = 0; i < fieldNames.length; i++) {
                        if (fieldTypes[i] == STRING || fieldTypes[i] == BLOB) { // Strings must be unescaped
                            set.fieldValues.put(fieldNames[i], unEscape(safeString(results.getString(fieldNames[i]))));
                        } else { // Numbers were not escaped
                            set.fieldValues.put(fieldNames[i], safeString(results.getString(fieldNames[i])));
                        }
                    }
                    // the set object now corresponds to a set in the database
                    set.hasRecordInDB = true;
                    return true;
                }

            } catch (Exception e) {
                ErrorLog.log(this, "fill(TableRecord set,ResultSet results)", "Could not read from database! ", e);
                dbError();
            }
            return false;
        }
    }

    /**
     * Creates a record in the database and fills it with key values
     * If a generated key exists, it reads the value from the database.
     * @param set the TableRecord
     */
    public void createInDB(TableRecord set) {
        synchronized (set) {
            String columns = "";  // (uri)
            String values = "";   // ("http://uni.de/")
            String delimiter = "";

            // work key names and values into query-string
            for (int i = 0; i < fieldNames.length; i++) {
                if (keyTypes[i] != GENERATED) {
                    columns += fieldNames[i] + ",";
                    if (fieldTypes[i] == STRING || fieldTypes[i] == BLOB) { // Strings must be escaped in put in brackets...
                        values += "\'"
                                + escape(set.fieldValues.get(fieldNames[i]))
                                + "\',";
                    } else { // Numbers must not be escaped and no brackets...
                        values += set.fieldValues.get(fieldNames[i])
                                + ",";
                    }
                }
            }

            // chop off trailing commas
            columns = columns.substring(0, columns.length() - 1);
            values = values.substring(0, values.length() - 1);

            // System.out.println(columns);
            // System.out.println(values);

            // create query string with key values. DELAYED optimizes speed: Continue at once and write if time available!
            String query = "INSERT ";

            if (autoincrementLine < 0) {   // Only "delayed" if generated key is not needed!
                query += "DELAYED ";
            }
            query += "INTO " + tableName + " (" + columns + ") VALUES(" + values + ")";
            // System.out.println("DBTableAdapter.createInDB: "+query);
            if (set.persistent && useDb() && con != null)    // Using Database???
            {
                try {
                    synchronized (con) {
                        Statement stmt = con.createStatement();
                        int i = stmt.executeUpdate(query);

                        set.hasRecordInDB = true;  // set now corresponds to a set in the database

                        // if there is a generated key, get the value!
                        if (autoincrementLine >= 0) {
                            Statement stmt2 = con.createStatement();

                            query = "select " + fieldNames[autoincrementLine]
                                    + " from " + tableName + getWhereClause(set);
                            ResultSet r = stmt2.executeQuery(query);

                            r.next();
                            set.fieldValues.put(fieldNames[autoincrementLine], safeString(r.getString(fieldNames[autoincrementLine])));
                        }
                        set.setChanged(false);
                    }
                } catch (SQLException e) { //(com.mysql.jdbc.PacketTooBigException e) {
                    ErrorLog.log(this, "createInDB(TableRecord set)", "Could not store in database! " + query, e);
                    System.out.println("-> Scone: Could not store in database.");
                    System.out.println("          Possible error: data packet too big.");
                    System.out.println("          Please set 'set-variable = max_allowed_packet=16M' in");
                    System.out.println("          [mysqld]-Section of mysql.ini");
                    dbError();
                } catch (Exception e) {
                    ErrorLog.log(this, "createInDB(TableRecord set)", "Could not insert into db: query = " + query, e);
                    System.out.println("-> Scone: ERROR! Could not insert into database:\n " + query);
                    dbError();
                }
            } else // database deactivated...
            {
                if (autoincrementLine >= 0) {
                    set.fieldValues.put(fieldNames[autoincrementLine], String.valueOf(generatedKey));
                    generatedKey++;
                }
                set.hasRecordInDB = true;
                // set.setChanged(false)
            }
        }
    }

    /**
     * generates a <code>where</code> clause to find the specified TableRecord in the database.<BR>
     * Will return the autoincrement key, if a value is given, or the composed key otherwise.
     * @param set the TableRecord
     * @return the <code>where</code> clause corresponding to the TableRecord object
     */
    public String getWhereClause(TableRecord set) {
        // System.out.println("where-clause "+set.getClass().getName());
        // is there a generated key?
        if ((autoincrementLine >= 0)
                && (set.fieldValues.get(fieldNames[autoincrementLine])
                        != fieldDefaults[autoincrementLine])) {
            String ret = " where " + fieldNames[autoincrementLine] + "="
                    + set.fieldValues.get(fieldNames[autoincrementLine]);

            // System.out.println("ret "+ret);
            return ret;
        } // no, use the composed key
        else {
            // System.out.println("composed key");
            String keyString = "";
            String delimiter = "";
            int i;

            for (i = 0; i < fieldNames.length; i++) {
                // System.out.println(fieldNames[i]+":"+keyTypes[i]);
                if (keyTypes[i] == KEY) {
                    if (keyString.length() > 0) {
                        keyString += " and";
                    }
                    if (fieldTypes[i] == STRING || fieldTypes[i] == BLOB) { // Strings must be escaped and put in brackets...
                        keyString += " " + fieldNames[i] + "=" + "\'"
                                + escape(set.fieldValues.get(fieldNames[i]))
                                + "\'";
                    } else { // Numbers must not be escaped and no brackets...
                        keyString += " " + fieldNames[i] + " = "
                                + set.fieldValues.get(fieldNames[i]);
                    }
                }

            }
            // no key.
            if (keyString.length() == 0) {
                return "";
            } else {
                return " where" + keyString;
            }
        }
    }

    /**
     * Ensures that a String is not null
     * @param unsafe the String
     * @return the same String or an empty String if the argument was <code>null</code>>
     */
    public static String safeString(String unsafe) {
        if (unsafe != null) {
            return unsafe;
        } else {
            return "";
        }
    }

    /**
     * Calls escape(StringBuffer s)
     */

    public static String escape(String s) {
        return escape(new StringBuffer(s));
    }

    /**
     * Escape is used to escape characters, that cause problems with mySQL or mm.mysql<P>
     *
     * The following strings are converted:
     * <UL>
     * <LI> null (0) -> \0
     * <LI> Backspace (8) -> \b
     * <LI> New line (10) -> \n
     * <LI> CR (13) -> \r
     * <LI> Double qoutes " (34) -> \"
     * <LI> Single quote ' (39) -> \'
     * <LI> Percent % (92) -> \%
     * <LI> Underscore _ (95) -> \_
     * <LI> Math brackets { } (123, 124) -> \( \)
     * <LI> Pipe | (124) -> \|
     * </UL>
     */
    public static String escape(StringBuffer s) {
        StringBuffer t = new StringBuffer(s.length() + 100);

        for (int i = 0; i < s.length(); i++) {
            char test = s.charAt(i);

            switch (test) {
            case 0:  // null
                {
                    t.append('\\');
                    t.append('0');
                    break;
                }

            case 10: // newline
                {
                    t.append('\\');
                    t.append('n');
                    break;
                }

            case 13: // carriage return
                {
                    t.append('\\');
                    t.append('r');
                    break;
                }

            case 8:  // backspace
                {
                    t.append('\\');
                    t.append('b');
                    break;
                }

            case 9:  // HT
                {
                    t.append('\\');
                    t.append('h');
                    break;
                }

            case 39: // '
                {
                    t.append('\\');
                    t.append('\'');
                    break;
                }

            case 34: // "
                {
                    t.append('\\');
                    t.append('\"');
                    break;
                }

            case 92: // backslash \
                {
                    t.append('\\');
                    t.append('\\');
                    break;
                }

            case 37: // %
                {
                    t.append('\\');
                    t.append('%');
                    break;
                }

            case 95: // _
                {
                    t.append('\\');
                    t.append('_');
                    break;
                }

            case 123:   // {  // These have to be escapes as well with mm.mysql. I don't know why!?!
                {
                    t.append('\\');
                    t.append('(');
                    break;
                }

            case 124:   // |
                {
                    t.append('\\');
                    t.append('I');
                    break;
                }

            case 125:   // }
                {
                    t.append('\\');
                    t.append(')');
                    break;
                }

            default:
                t.append(test);
            }
        }
        return t.toString();
    }

    public static String unEscape(String s) {
        return unEscape(new StringBuffer(s));
    }

    /**
     * unEscape Strings read from DB to original values
     */
    public static String unEscape(StringBuffer s) {
        StringBuffer t = new StringBuffer();
        boolean slash = false;

        for (int i = 0; i < s.length(); i++) {
            char test = s.charAt(i);

            if (!slash) {
                if (test == '\\') { // is it the escape symbol?
                    slash = true;
                } else {
                    t.append(test);
                }
            } else  // We had a slash just before
            {
                slash = false;
                switch (test) {
                case '0':  // null
                    {
                        t.append(0);
                        break;
                    }

                case 'n':   // newline
                    {
                        t.append(10);
                        break;
                    }

                case 'r':   // carriage return
                    {
                        t.append(13);
                        break;
                    }

                case 'b':   // backspace
                    {
                        t.append(8);
                        break;
                    }

                case 'h':   // HT
                    {
                        t.append(9);
                        break;
                    }

                case '\'':  // '
                    {
                        t.append(39);
                        break;
                    }

                case '\"':  // "
                    {
                        t.append(34);
                        break;
                    }

                case '\\':  // backslash \
                    {
                        t.append(92);
                        break;
                    }

                case '%':   // %
                    {
                        t.append(37);
                        break;
                    }

                case '_':   // _
                    {
                        t.append('_');
                        break;
                    }

                case '(':   // {
                    {
                        t.append('{');
                        break;
                    }

                case 'I':   // {
                    {
                        t.append('|');
                        break;
                    }

                case ')':   // {
                    {
                        t.append('}');
                        break;
                    }
                }
            }
        }
        return t.toString();
    }

    // -----------------------------------------------------------------
    // stores the values from db.ini
    protected static String driverName = "org.gjt.mm.mysql.Driver";
    protected static String dbURI = "jdbc:mysql://localhost:3306/Scone";
    protected static String userName = "scone";
    protected static String password = "sc0ne";  // with zero.
    protected static boolean useDb = true;
    protected static boolean requireDb = false;

    /**
     * initializes the DB class by parsing scone/config/db.ini
     */
    public static void init() {

        // load preferences for the database
        PersistentProperties props = new PersistentProperties("config/scone/db.xml");

        // read driverName
        driverName = props.get("JDBC Driver");
        // read databasePath
        dbURI = props.get("Database URI");
        // read userName
        userName = props.get("Username");
        // read passwd
        password = props.get("Password");

        if (props.get("Use Database").equals("true")) {
            useDb = true;
            // System.out.println("-> Scone: Using database "+driverName+" on "+dbURI);
            System.out.println("-> Scone: Using database "+dbURI);
        } else {
            useDb = false;
            System.out.println("-> Scone: database turned off.");
        }

        if (props.get("Deactivate database on errors").equals("true")) {
            requireDb = true;
            System.out.println("-> Scone: Database will be deactivated on database exceptions.");
        } else {
            requireDb = false;
        }

        // init database
        if (useDb) {
            try {
                Class.forName(driverName).newInstance();
            } catch (Exception e) {
                ErrorLog.log("static DBTableAdapter", "init()", "Could not load mysql driver: " + driverName, e);
                System.out.println("-> Scone: ERROR! --------------------------------------------------");
                System.out.println("   Could not load mysql driver: " + driverName);
                dontUseDb();
            }
        }
    }

    /**
     * returns a new <code>Connection</code> to the database
     */

    public static Connection getConnection() {
        Connection con = null;

        if (useDb()) {
            try {
                con = DriverManager.getConnection(dbURI, userName, password);
            } catch (Exception e) {
                ErrorLog.log("static DBTableAdapter", "getConnection()", "Could not connect to database " + dbURI, e);
                System.out.println("-> Scone: ERROR! --------------------------------------------------");
                System.out.println("          Could not connect to database " + dbURI);
                dontUseDb();
            }
        } else {
            System.out.println("-> Scone: ERROR! --------------------------------------------------");
            System.out.println("   The database was deactivated but was requested by a plugin!");
            System.out.println("");
            System.out.println("   ----------------------------------------------------------------");
        }
        return con;
    }

    /**
     * this method should be called when an SQLException has been caught.
     * It disables database access and notifies the user.
     */
    protected static void dontUseDb() {
        useDb = false;
        System.out.println("-> Scone: Database access deactivated due to an fatal exception!");
        System.out.println("   ----------------------------------------------------------------\n");
    }

    /**
     * this method should be called when an SQLException has been caught.
     * It disables database access and notifies the user.
     */
    protected static void dbError() {
        System.out.println("   Attention: Results may be corrupted.");
        if (requireDb) {
            useDb = false;
            System.out.println("   Database access has been DISABLED due to an exception!");
        }
        System.out.println("   ----------------------------------------------------------------\n");
    }

    /**
     * returns true, if the database is used,
     * and false if local memory is used instead
     * @return whether the databse is used
     */
    public static boolean useDb() {
        return useDb;
    }

}
