/*
SDX: Documentary System in XML.
Copyright (C) 2000, 2001, 2002  Ministere de la culture et de la communication (France), AJLSM

Ministere de la culture et de la communication,
Mission de la recherche et de la technologie
3 rue de Valois, 75042 Paris Cedex 01 (France)
mrt@culture.fr, michel.bottin@culture.fr

AJLSM, 17, rue Vital Carles, 33000 Bordeaux (France)
sevigny@ajlsm.com

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
or connect to:
http://www.fsf.org/copyleft/gpl.html
*/
package fr.gouv.culture.sdx.utils.database;

import fr.gouv.culture.sdx.exception.SDXException;
import fr.gouv.culture.sdx.exception.SDXExceptionCode;
import fr.gouv.culture.sdx.utils.Utilities;
import fr.gouv.culture.sdx.utils.constants.ContextKeys;
import fr.gouv.culture.sdx.utils.constants.Node;
import fr.gouv.culture.sdx.utils.logging.LoggingUtils;
import fr.gouv.culture.sdx.utils.rdbms.DataSourceComponentBacked;
import fr.gouv.culture.sdx.utils.save.SaveParameters;
import fr.gouv.culture.sdx.utils.save.Saveable;

import org.apache.avalon.framework.service.ServiceException;
import org.apache.avalon.framework.configuration.Configuration;
import org.apache.avalon.framework.configuration.ConfigurationException;
import org.apache.avalon.framework.parameters.ParameterException;
import org.apache.avalon.framework.parameters.Parameters;

import java.io.File;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.*;
import java.util.ArrayList;
import java.util.Hashtable;

/**
 * Created by IntelliJ IDEA.
 * User: rpandey
 * Date: Mar 3, 2003
 * Time: 3:58:12 PM
 */
public abstract class AbstractJDBCDatabase extends DataSourceComponentBacked implements Database {
    private static final long RESULT_MAX_SIZE = 5000;
    protected final String FIELD_ID = "id";
    protected final String FIELD_PROPERTY_NAME = "propertyName";
    protected final String FIELD_PROPERTY_VALUE = "propertyValue";
    protected final int COLUMN_PARAM_INDEX_FIELD_ID = 1;
    protected final int COLUMN_PARAM_INDEX_FIELD_PROPERTY_NAME = 2;
    protected final int COLUMN_PARAM_INDEX_FIELD_PROPERTY_VALUE = 3;
    public static final String[] _searchModes = {"UNION", "INTERSECT", "EXCEPT"};

    public void configure(Configuration configuration) throws ConfigurationException {
        super.configure(configuration);
        if (!Utilities.checkString(this.getId()))
            throw new ConfigurationException("we dont have a valid id with which to build a table name");//TODOException: better message here

        this.tableName = Utilities.getStringFromContext(ContextKeys.SDX.Application.DIRECTORY_NAME, getContext()) + "_" + super.getId();
    }

    public DatabaseEntity getEntity(final String id) throws SDXException {
        if (!Utilities.checkString(id)) return null;
        DatabaseConnection sqldbConn = getConnection();
        Connection conn = sqldbConn.getConnection();
        String queryString = getEntityGetQuery();
        try {
            Template template = new Template(conn, queryString);
            QueryExecutor qe = new QueryExecutor() {
                DatabaseEntity l_dbe = null;

                public void prepare(PreparedStatement ps) throws SQLException {
                    ps.setString(COLUMN_PARAM_INDEX_FIELD_ID, id);
                }

                public void collect(ResultSet rs) throws SQLException, SDXException {
                    l_dbe = getEntity(id, rs);
                }

                public Object get() {
                    return l_dbe;
                }
            };
            template.execute(qe, Template.MODE_EXECUTE_QUERY);
            return (DatabaseEntity) qe.get();
        } catch (SDXException e) {
            String[] args = new String[1];
            args[0] = id;
            throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_GET_ENTITY, args, e);
        } finally {
            releaseConnection(sqldbConn);
        }


    }

    public DatabaseEntity[] getEntities() throws SDXException {
        DatabaseConnection sqldbConn = getConnection();
        Connection conn = sqldbConn.getConnection();
        String queryString = getAllEntitiesQuery();
        try {
            Template template = new Template(conn, queryString);
            QueryExecutor qe = new QueryExecutor() {
                DatabaseEntity[] l_dbes = null;

                public void collect(ResultSet rs) throws SQLException, SDXException {
                    l_dbes = getEntities(rs);
                }

                public Object get() {
                    return l_dbes;
                }
            };
            template.execute(qe, Template.MODE_EXECUTE_QUERY);
            return (DatabaseEntity[]) qe.get();
        } catch (SDXException e) {
            String[] args = new String[1];
            args[0] = this.getId();
            throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_GET_ENTITIES, args, e);
        } finally {
            releaseConnection(sqldbConn);
        }
    }

    public String getPropertyValue(String entityId, String name) throws SDXException {
        DatabaseEntity dbe = getEntity(entityId);
        if (dbe == null || !Utilities.checkString(name)) return null;
        return dbe.getProperty(name);
    }

    public String[] getPropertyValues(String entityId, String propertyName) throws SDXException {
        DatabaseEntity dbe = getEntity(entityId);
        if (dbe == null || !Utilities.checkString(propertyName)) return null;
        return dbe.getPropertyValues(propertyName);
    }

    public Property[] getProperties(String entityId) throws SDXException {
        DatabaseEntity dbe = getEntity(entityId);
        if (dbe == null) return null;
        return dbe.getProperties();
    }

    public synchronized void save(DatabaseEntity ent) throws SDXException {
        // Now the properties
        if (ent == null) return;

        final String entityId = ent.getId();

        if (!Utilities.checkString(entityId)) return;//TODO?:should we be throwing an exception here,not sure as DatabseEntity constructor is restrictive enough?-rbp

        final Property[] props = ent.getProperties();
        DatabaseConnection sqldbConn = getConnection();
        Connection conn = sqldbConn.getConnection();
        String queryString = getPropertyAddQuery();
        try {
            //adding actual properties
            Template template = new Template(conn, queryString);
            QueryExecutor qe = new QueryExecutor() {
                public void prepare(PreparedStatement ps) throws SQLException, SDXException {
                    if (props != null && props.length > 0) {
                        for (int i = 0; i < props.length; i++) {
                            final String[] values = props[i].getValues();
                            if (values != null) {
                                for (int j = 0; j < values.length; j++) {
                                    ps.setString(COLUMN_PARAM_INDEX_FIELD_ID, entityId);
                                    //TODO: i don't think the property should be stored if the name or val is null
                                    String propName = props[i].getName();
                                    String propVal = values[j];
                                    if (Utilities.checkString(propName) && Utilities.checkString(propVal)) {
                                        ps.setString(COLUMN_PARAM_INDEX_FIELD_PROPERTY_NAME, propName);
                                        ps.setString(COLUMN_PARAM_INDEX_FIELD_PROPERTY_VALUE, propVal);
                                        ps.executeUpdate();
                                        ps.clearParameters();
                                    }
                                }

                            }
                        }
                        //ent.dump(System.out);
                    } else {
                        //adding a placeholder for the property, if no properties
                        ps.clearParameters();
                        ps.setString(COLUMN_PARAM_INDEX_FIELD_ID, entityId);
                        ps.setString(COLUMN_PARAM_INDEX_FIELD_PROPERTY_NAME, "");
                        ps.setString(COLUMN_PARAM_INDEX_FIELD_PROPERTY_VALUE, "");
                        ps.executeUpdate();

                    }

                }

            };
            template.execute(qe, Template.MODE_NONE);
        } catch (SDXException e) {
            String[] args = new String[2];
            args[0] = entityId;
            args[1] = this.getId();
            throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_SAVE_ENTITY, args, e);
        } finally {
            sqldbConn.commit();
            releaseConnection(sqldbConn);

        }


    }

    public synchronized void delete(DatabaseEntity ent) throws SDXException {

        if (ent != null) {
            final String entityId = ent.getId();
            if (Utilities.checkString(entityId)) {
                DatabaseConnection sqldbConn = getConnection();
                Connection conn = sqldbConn.getConnection();
                String queryString = getEntityDeleteQuery();
                try {
                    Template template = new Template(conn, queryString);
                    QueryExecutor qe = new QueryExecutor() {
                        public void prepare(PreparedStatement ps) throws SQLException {
                            ps.setString(COLUMN_PARAM_INDEX_FIELD_ID, entityId);
                        }
                    };
                    template.execute(qe, Template.MODE_EXECUTE_UPDATE);
                } catch (SDXException e) {
                    String[] args = new String[2];
                    args[0] = entityId;
                    args[1] = this.getId();
                    throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_DELETE_ENTITY, args, e);
                } finally {
                    sqldbConn.commit();
                    releaseConnection(sqldbConn);
                }
            }
        }

    }

    public synchronized void delete(DatabaseEntity[] entities) throws SDXException {
        if (entities == null) return;
        Connection conn = null;
        String queryString = getEntityDeleteQuery();
        DatabaseConnection sqldbConn = getConnection();
        try {
            //TODO:this should be one query executed with a list of id's

            Template template = new Template(conn, queryString);
            for (int i = 0; i < entities.length; i++) {
                DatabaseEntity ent = entities[i];
                if (ent != null) {
                    final String entityId = ent.getId();
                    if (Utilities.checkString(entityId)) {
                        if (conn == null) {
                            conn = sqldbConn.getConnection();
                            QueryExecutor qe = new QueryExecutor() {
                                public void prepare(PreparedStatement ps) throws SQLException {
                                    ps.clearParameters();
                                    ps.setString(COLUMN_PARAM_INDEX_FIELD_ID, entityId);
                                }
                            };
                            template.execute(qe, Template.MODE_EXECUTE_UPDATE);
                        }
                    }
                }
            }
        } catch (SDXException e) {
            String[] args = new String[2];
            args[0] = this.getId();
            throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_DELETE_ENTITIES, args, e);
        } finally {
            sqldbConn.commit();
            releaseConnection(sqldbConn);
        }


    }

    public void update(DatabaseEntity ent) throws SDXException {
        if (ent != null) {
            delete(ent);
            save(ent);
        }
    }

    public long size() {
        DatabaseConnection sqldbConn = null;
        Connection conn = null;
        int ret = -1;
        try {
            sqldbConn = getConnection();
            //TODO: what is the proper query here given no unique id in this structure
            conn = sqldbConn.getConnection();
            String queryString = "SELECT COUNT(DISTINCT " + FIELD_ID + ") FROM " + getTableName();
            Template template = new Template(conn, queryString);
            QueryExecutor qe = new QueryExecutor() {
                int l_value = 0;

                public void collect(ResultSet rs) throws SQLException {
                    if (rs.next()) l_value = rs.getInt(1);
                }

                public int value() {
                    return l_value;
                }
            };
            template.execute(qe, Template.MODE_EXECUTE_QUERY);
            return qe.value();
        } catch (SDXException e) {
            return ret;
        } finally {
            try {
                releaseConnection(sqldbConn);
            } catch (SDXException e) {
                LoggingUtils.logException(super.getLog(), e);
            }
        }
    }

    public synchronized void empty() throws SDXException {
        DatabaseConnection sqlDbConn = getConnection();
        Connection conn = null;
        try {
            conn = sqlDbConn.getConnection();
            String queryString = getDeleteAllQuery();
            Template template = new Template(conn, queryString);
            QueryExecutor qe = new QueryExecutor() {
            };
            template.execute(qe, Template.MODE_EXECUTE_UPDATE);
        } catch (SDXException e) {
            String[] args = new String[2];
            args[0] = this.getId();
            args[1] = e.getMessage();
            //TODOException: better message here, basically database and repository exceptions need to be refactored
            throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_EMPTY, args, e);
        } finally {
            sqlDbConn.commit();
            releaseConnection(sqlDbConn);
        }
    }

    public boolean entityExists(String id) {
        if (!Utilities.checkString(id)) return false;

        DatabaseEntity dbe = null;
        try {
            dbe = this.getEntity(id);
        } catch (SDXException e) {
            LoggingUtils.logException(super.getLog(), e);
            return false;
        }
        if (dbe == null)
            return false;
        else
            return true;
    }

    /**
     * @see fr.gouv.culture.sdx.utils.database.Database#search(org.apache.avalon.framework.parameters.Parameters)
     */
    public String[] search(Parameters params) throws SDXException {

        if ( params.getNames().length >= 2 )
            return searchWithParams(params);
        else
        return search(params, SEARCH_MODE_AND);
    }

    public String[] search(final Parameters params, int mode) throws SDXException {
        if (mode != SEARCH_MODE_OR && mode != SEARCH_MODE_AND && mode != SEARCH_MODE_NOT)
            return new String[0];
        String modeString = _searchModes[mode];
        //SELECT * FROM tablename WHERE propertyName = paramName propertyValue = paramValue.
        if (params == null) return new String[0];
        String[] entities = new String[0];
        DatabaseConnection sqlDbConn = getConnection();
        Connection conn = sqlDbConn.getConnection();
        String queryString = "";
        final String l_templateQueryString = "SELECT " + FIELD_ID + " FROM " + getTableName() + " WHERE " + FIELD_PROPERTY_NAME + " = ? AND " + FIELD_PROPERTY_VALUE + " = ?";

        //getting the param names
        //TODO: can this be made more efficient, how can we avoid two loops?
        final String[] paramNames = params.getNames();

        try {

            //building the query statement
            for (int i = 0; i < paramNames.length; i++) {
                //TODONOW: this must be optimized with a better query possibly using temp tables
                if (i == 0)
                    queryString += l_templateQueryString;
                /*Problem here : INTERSECT syntax is not avalaible with MySQL. Is it with HSQL ?
                 * Also, DISTINCT can't be used after INTERSECT. It must be used with SELECT DISTINCT. -mp*/
                else
                    //queryString += " " + modeString + " DISTINCT " + l_templateQueryString;//DISTINCT have to be used after SELECT, not here
                    queryString += " " + modeString + " " + l_templateQueryString;

            }


            //fill the query statement with params
            if (Utilities.checkString(queryString)) {
                Template template = new Template(conn, queryString);
                QueryExecutor qe = new QueryExecutor() {
                    String[] l_dbes = null;

                    public void prepare(PreparedStatement ps) throws SQLException, SDXException {
                        //setting the params
                        for (int j = 0; j < paramNames.length; j++) {
                            //ps.setString(paramIdx++ , FIELD_PROPERTY_NAME);
                            int paramIdx = j * 2;
                            ps.setString(paramIdx + 1, paramNames[j]);
                            // ps.setString(paramIdx++, FIELD_PROPERTY_VALUE);
                            try {
                                ps.setString(paramIdx + 2, params.getParameter(paramNames[j]));
                            } catch (ParameterException e) {
                                throw new SDXException(getLog(), SDXExceptionCode.ERROR_GET_PARAMETERS, null, e);
                            }
                        }

                    }

                    public void collect(ResultSet rs) throws SQLException, SDXException {
                        l_dbes = getEntityIds(rs);
                    }

                    public Object get() {
                        return l_dbes;
                    }
                };
                template.execute(qe, Template.MODE_EXECUTE_QUERY);
                entities = (String[]) qe.get();

            }

            return entities;
        } catch (SDXException e) {
            String[] args = new String[1];
            args[0] = this.getId();
            throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_SEARCH_DATABASE, args, e);
        } finally {
            releaseConnection(sqlDbConn);
        }


    }


    /**Search entities in SDX system databases with two parameters and a single table.
     * Syntax avalaible in MySQL and HSQL.
     * More than two parameters not implemented at this time. -mp*/
    public String[] searchWithParams(final Parameters params) throws SDXException {

        if (params == null) return new String[0];
        String[] entities = new String[0];
        DatabaseConnection sqlDbConn = getConnection();
        Connection conn = sqlDbConn.getConnection();
        String queryString = "";

        //getting the param names
        //TODO: can this be made more efficient, how can we avoid two loops?
        final String[] paramNames = params.getNames();

        // FIXME : is this used realy : paramValue is never used. May be it's a control !? [MP]
        String paramValue = null;
        for (int j = 0; j < paramNames.length; j++) {
            try {
                paramValue = params.getParameter(paramNames[j]);
            } catch (ParameterException e1) {
                e1.printStackTrace();
            }
        }

        try {

            //building the query statement
            //One param in the query
            if ( paramNames.length < 2 ) {
                queryString += "SELECT " + FIELD_ID + " FROM " + getTableName() + " WHERE " + FIELD_PROPERTY_NAME + " = ? AND " + FIELD_PROPERTY_VALUE + " = ?";
            }

            //2 params in the query. We must have these syntax :
            //SELECT t1.id from tablename AS t1, tablename AS t2 WHERE
            //t1.propertyName=paramName1 AND
            //t1.propertyValue=paramValue1 AND
            //t2.propertyName=paramName2 AND
            //t2.propertyValue=paramValue2 AND
            //t1.id = t2.id;
            else if ( paramNames.length == 2 ) {
                queryString += "SELECT t1." + FIELD_ID + " FROM " + getTableName() + " AS t1, " + getTableName() + " AS t2 " ;
                queryString += " WHERE t1." + FIELD_PROPERTY_NAME + " = ? AND t1." + FIELD_PROPERTY_VALUE + " = ? ";
                queryString += " AND t2." + FIELD_PROPERTY_NAME + " = ? AND t2." + FIELD_PROPERTY_VALUE + " = ? " ;
                queryString += " AND t1.id = t2.id";
            }
            //more than two parameters not implemented
            else {
                String[] args = new String[2];
                args[0] = this.getId();
                args[1] = "Search in database with more tha two parameters not implemented.";
                new SDXException(getLog(), SDXExceptionCode.ERROR_SEARCH_DATABASE, args, null);
            }

            //fill the query statement with params
            if (Utilities.checkString(queryString)) {
                Template template = new Template(conn, queryString);
                QueryExecutor qe = new QueryExecutor() {
                    String[] l_dbes = null;

                    public void prepare(PreparedStatement ps) throws SQLException, SDXException {
                        //setting the params
                        for (int j = 0; j < paramNames.length; j++) {
                            //ps.setString(paramIdx++ , FIELD_PROPERTY_NAME);
                            int paramIdx = j * 2;
                            ps.setString(paramIdx + 1, paramNames[j]);
                            // ps.setString(paramIdx++, FIELD_PROPERTY_VALUE);
                            try {
                                ps.setString(paramIdx + 2, params.getParameter(paramNames[j]));
                            } catch (ParameterException e) {
                                throw new SDXException(getLog(), SDXExceptionCode.ERROR_GET_PARAMETERS, null, e);
                            }
                        }

                    }

                    public void collect(ResultSet rs) throws SQLException, SDXException {
                        l_dbes = getEntityIds(rs);
                    }

                    public Object get() {
                        return l_dbes;
                    }
                };
                template.execute(qe, Template.MODE_EXECUTE_QUERY);
                entities = (String[]) qe.get();

            }

            return entities;
        } catch (SDXException e) {
            String[] args = new String[1];
            args[0] = this.getId();
            throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_SEARCH_DATABASE, args, e);
        } finally {
            releaseConnection(sqlDbConn);
        }


    }


    /** Initializes the repository.
     *
     * If there are no tables in the database,
     * we create the necessary table
     *
     * @throws fr.gouv.culture.sdx.exception.SDXException  */
    public void init() throws SDXException {
        /** First try to access the database and see if the tables exist. */
        DatabaseConnection sqlDbConn = getConnection();
        Connection conn = null;
        ResultSet rs = null;
        try {

            conn = sqlDbConn.getConnection();
            //if we don't have a database we can't do much
            //TODO: check for null connection-rbp
            DatabaseMetaData dbmd = conn.getMetaData();
            rs = dbmd.getTables(null, null, getTableName(), null);
            if (!rs.next()) {
                // The table doesn't exist, so we should create it.
                createTable(conn);
                createIndicies(conn);
            }
        } catch (SQLException e) {
            String[] args = new String[1];
            args[0] = this.getId();
            throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_INIT_DATABASE, args, e);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    String[] args = new String[2];
                    args[0] = this.getId();
                    args[1] = e.getMessage();
                    throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_CLOSE_RESULT_SET, args, e);
                }
            }
            sqlDbConn.commit();
            releaseConnection(sqlDbConn);
        }
    }

    protected String getTableCreationQuery() {
        return "CREATE TABLE " + getTableName() + " ( " + FIELD_ID + " VARCHAR(255) NOT NULL, " + FIELD_PROPERTY_NAME + " VARCHAR(255) NOT NULL, " + FIELD_PROPERTY_VALUE + " VARCHAR(255) NOT NULL, "
                + "PRIMARY KEY (" + FIELD_ID + "(255), " + FIELD_PROPERTY_NAME + "(255), " + FIELD_PROPERTY_VALUE + "(255) ))";
    }

    /** Returns an SQL query that could select a entity using its id.
     * <p>
     * This query should have one parameter for the id.
     * @return The query.
     */
    protected String getEntityGetQuery() {
        return "SELECT * FROM " + getTableName() + " WHERE " + FIELD_ID + " = ?";
    }

    protected String getAllEntitiesQuery() {
        return "SELECT * FROM " + getTableName();
    }

    /**
     * Implementation of statement with a limit parameter
     * @param offset
     * @param number
     * @return the query
     */
    protected abstract String getEntriesWithLimitQuery(long offset, long number);

    protected String getEntityDeleteQuery() {
        return "DELETE FROM " + getTableName() + " WHERE " + FIELD_ID + " = ?";
    }

    protected String getPropertyAddQuery() {
        return "INSERT INTO " + getTableName() + " (" + FIELD_ID + "," + FIELD_PROPERTY_NAME + "," + FIELD_PROPERTY_VALUE + ") VALUES (?, ?, ?)";
    }

    protected String getPropertyRemoveQueryWithId() {
        return "DELETE FROM " + getTableName() + " WHERE " + FIELD_ID + " = ? AND " + FIELD_PROPERTY_NAME + " = ? AND " + FIELD_PROPERTY_VALUE + " = ?";
    }

    protected String getPropertyRemoveQueryWithoutId() {
        return "DELETE FROM " + getTableName() + " WHERE " + FIELD_PROPERTY_NAME + " = ? AND " + FIELD_PROPERTY_VALUE + " = ?";
    }

    protected String getDeleteAllQuery() {
        return "DELETE FROM " + getTableName();
    }


    protected String getCreateIndiciesQuery(String fieldName) {
        return "CREATE INDEX " + fieldName + " ON " + getTableName() + " (" + fieldName + ")";
        //return "CREATE INDEX " + getIndexName() + " ON " + getTableName() + " (" + FIELD_ID + ", " + FIELD_PROPERTY_NAME + ", " + FIELD_PROPERTY_VALUE + ")";
    }

    /**Calling methods should close ResultSet after calling this method
     *
     * @param id
     * @param rs
     * @return
     * @throws fr.gouv.culture.sdx.exception.SDXException
     */
    protected DatabaseEntity getEntity(String id, ResultSet rs) throws SDXException {
        if (!Utilities.checkString(id) || rs == null) return null;
        DatabaseEntity dbe = null;
        try {
            while (rs.next()) {
                String rowid = rs.getString(FIELD_ID);
                String propName = rs.getString(FIELD_PROPERTY_NAME);
                String propVal = rs.getString(FIELD_PROPERTY_VALUE);

                if (Utilities.checkString(rowid) && id.equals(rowid)) {
                    if (dbe == null)
                        dbe = new DatabaseEntity(id);
                    dbe.addProperty(propName, propVal);
                }
            }

            return dbe;
        } catch (SQLException e) {
            String[] args = new String[1];
            args[0] = id;
            throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_GET_ENTITY, args, e);
        }

    }

    /**Calling methods should close ResultSet after calling this method
     *
     * @param rs
     * @return
     * @throws fr.gouv.culture.sdx.exception.SDXException
     */
    protected DatabaseEntity[] getEntities(ResultSet rs) throws SDXException {
        if (rs == null) return null;
        Hashtable entities = null;

        try {
            while (rs.next()) {
                if (entities == null) entities = new Hashtable();
                String rowid = rs.getString(FIELD_ID);
                String propName = rs.getString(FIELD_PROPERTY_NAME);
                String propVal = rs.getString(FIELD_PROPERTY_VALUE);

                if (Utilities.checkString(rowid) && Utilities.checkString(propName)) {
                    DatabaseEntity dbe = (DatabaseEntity) entities.get(rowid);
                    if (dbe == null) dbe = new DatabaseEntity(rowid);
                    dbe.addProperty(propName, propVal);
                    entities.put(rowid, dbe);
                }
            }

            if (entities == null || entities.size() == 0) return null;
            return (DatabaseEntity[]) entities.values().toArray(new DatabaseEntity[0]);
        } catch (SQLException e) {
            String[] args = new String[1];
            args[0] = this.getId();
            throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_GET_ENTITIES, args, e);
        }

    }

    /**Calling methods should close ResultSet after calling this method
     *
     * @param rs
     * @return
     * @throws fr.gouv.culture.sdx.exception.SDXException
     */
    protected String[] getEntityIds(ResultSet rs) throws SDXException {
        if (rs == null) return new String[0];
        ArrayList ids = new ArrayList();

        try {
            while (rs.next()) {
                String rowid = rs.getString(FIELD_ID);
                if (Utilities.checkString(rowid))
                    ids.add(rowid);
            }
            return (String[]) ids.toArray(new String[0]);
        } catch (SQLException e) {
            String[] args = new String[1];
            args[0] = this.getId();
            throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_GET_ENTITIES, args, e);
        }

    }


    /**
     * Creates and index on the "id" field.
     */
    protected void createIndicies(Connection conn) throws SDXException {
        try {
            Template template = new Template(conn, getCreateIndiciesQuery(FIELD_ID));
            template.execute(new QueryExecutor() {
            }, Template.MODE_EXECUTE_UPDATE);

            template = new Template(conn, getCreateIndiciesQuery(FIELD_PROPERTY_NAME));
            template.execute(new QueryExecutor() {
            }, Template.MODE_EXECUTE_UPDATE);

            template = new Template(conn, getCreateIndiciesQuery(FIELD_PROPERTY_VALUE));
            template.execute(new QueryExecutor() {
            }, Template.MODE_EXECUTE_UPDATE);
        } catch (SDXException e) {
            String[] args = new String[2];
            args[0] = this.getTableName();
            args[1] = this.getId();
            throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_CREATE_INDEX, args, e);
        }
    }

    /**If the entity does not exist the method fails silently
     *
     * @param entityId
     * @param propertyName
     * @param propertyValue
     * @throws SDXException
     */
    public void addProperty(final String entityId, final String propertyName, final String propertyValue) throws SDXException {
        //don't have good params
        if (!Utilities.checkString(entityId) || !Utilities.checkString(propertyName) || !Utilities.checkString(propertyValue)) return;


        if (this.entityExists(entityId)) {
            DatabaseConnection sqldbConn = getConnection();
            Connection conn = sqldbConn.getConnection();
            String queryString = getPropertyAddQuery();
            try {
                Template template = new Template(conn, queryString);
                QueryExecutor qe = new QueryExecutor() {
                    public void prepare(PreparedStatement ps) throws SQLException, SDXException {
                        ps.setString(COLUMN_PARAM_INDEX_FIELD_ID, entityId);
                        ps.setString(COLUMN_PARAM_INDEX_FIELD_PROPERTY_NAME, propertyName);
                        ps.setString(COLUMN_PARAM_INDEX_FIELD_PROPERTY_VALUE, propertyValue);
                    }

                };
                template.execute(qe, Template.MODE_EXECUTE_UPDATE);
            } catch (SDXException e) {
                String[] args = new String[2];
                args[0] = entityId;
                args[1] = this.getId();
                throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_ADD_PROPERTY, args, e);
            } finally {
                sqldbConn.commit();
                releaseConnection(sqldbConn);
            }
        }

    }

    /**If the entity does not exist the method fails silently
     *
     * @param entityId
     * @param propertyName
     * @param propertyValue
     * @throws SDXException
     */
    public void removeProperty(final String entityId, final String propertyName, final String propertyValue) throws SDXException {
        //don't have good params
        if (!Utilities.checkString(entityId) || !Utilities.checkString(propertyName) || !Utilities.checkString(propertyValue)) return;

        if (this.entityExists(entityId)) {
            DatabaseConnection sqldbConn = getConnection();
            Connection conn = sqldbConn.getConnection();
            String queryString = getPropertyRemoveQueryWithId();
            try {
                Template template = new Template(conn, queryString);
                QueryExecutor qe = new QueryExecutor() {
                    public void prepare(PreparedStatement ps) throws SQLException, SDXException {
                        ps.setString(COLUMN_PARAM_INDEX_FIELD_ID, entityId);
                        ps.setString(COLUMN_PARAM_INDEX_FIELD_PROPERTY_NAME, propertyName);
                        ps.setString(COLUMN_PARAM_INDEX_FIELD_PROPERTY_VALUE, propertyValue);
                    }

                };
                template.execute(qe, Template.MODE_EXECUTE_UPDATE);
            } catch (SDXException e) {
                String[] args = new String[2];
                args[0] = entityId;
                args[1] = this.getId();
                throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_REMOVE_PROPERTY_WITH_ID, args, e);
            } finally {
                sqldbConn.commit();
                releaseConnection(sqldbConn);
            }
        }
    }

    public void removeProperty(final String propertyName, final String propertyValue) throws SDXException {

        //don't have good params
        if (!Utilities.checkString(propertyName) || !Utilities.checkString(propertyValue)) return;

        DatabaseConnection sqldbConn = getConnection();
        Connection conn = sqldbConn.getConnection();
        String queryString = getPropertyRemoveQueryWithoutId();
        try {
            Template template = new Template(conn, queryString);
            QueryExecutor qe = new QueryExecutor() {
                public void prepare(PreparedStatement ps) throws SQLException, SDXException {
                    ps.setString(COLUMN_PARAM_INDEX_FIELD_PROPERTY_NAME, propertyName);
                    ps.setString(COLUMN_PARAM_INDEX_FIELD_PROPERTY_VALUE, propertyValue);
                }

            };
            template.execute(qe, Template.MODE_EXECUTE_UPDATE);
        } catch (SDXException e) {
            String[] args = new String[3];
            args[0] = propertyName;
            args[1] = propertyValue;
            args[2] = this.getId();
            throw new SDXException(super.getLog(), SDXExceptionCode.ERROR_REMOVE_PROPERTY_WITHOUT_ID, args, e);
        } finally {
            sqldbConn.commit();
            releaseConnection(sqldbConn);
        }

    }

    public DatabaseConnection getConnection() throws SDXException {
        try {
            DatabaseConnection dbConn = new JDBCDatabaseConnection(getDataSourceComponent());
            dbConn.enableLogging(getLog());
            dbConn.setAutoCommit(false);
            return dbConn;
        } catch (SQLException e) {
            String[] args = new String[2];
            args[0] = this.getId();
            args[1] = e.getMessage();
            throw new SDXException(getLog(), SDXExceptionCode.ERROR_GET_CONNECTION, args, e);
        } catch (ServiceException e) {
            String[] args = new String[1];
            args[0] = e.getMessage();
            //null super.getLog() passed to prevent double logging
            SDXException sdxE = new SDXException(null, SDXExceptionCode.ERROR_ACQUIRE_DATASOURCE, args, e);

            String[] args2 = new String[2];
            args2[0] = this.getId();
            args2[1] = sdxE.getMessage();
            throw new SDXException(getLog(), SDXExceptionCode.ERROR_GET_CONNECTION, args2, sdxE);
        }
    }

    public void releaseConnection(DatabaseConnection conn) throws SDXException {
        if (conn != null) {
            //closing the sql connection if there is one open
            if (conn.getConnection() != null)
                ((JDBCDatabaseConnection) conn).releaseSqlConnection();
            //releasing the datasource service from cocoon's dbSelector
            this.releaseDataSourceComponent(((JDBCDatabaseConnection) conn).getDataSource());
        }
    }

    protected String getClassNameSuffix() {
        return Database.CLASS_NAME_SUFFIX;
    }

    public String getWildcardSearchToken() {
        return "%";//sql wildcard character is percent
    }

    /* Save the data objects of the database
     * @see fr.gouv.culture.sdx.utils.save.Saveable#backup(fr.gouv.culture.sdx.utils.save.SaveParameters)
     */
    public void backup(SaveParameters save_config) throws SDXException {
        if(save_config != null)
            if(save_config.getAttributeAsBoolean(Saveable.ALL_SAVE_ATTRIB,false))
            {
                save_config.setAttribute(Node.Name.ID,this.getId());
                save_config.savePathInConfig(null);

                String dir_name = save_config.getStoreBasePath() + File.separator +
                                save_config.getAttribute(Saveable.PATH_ATTRIB,"");
                backupAllDatabaseEntries(dir_name);
            }
    }

    private void backupAllDatabaseEntries(String dir_name) throws SDXException
    {
        FileWriter file_out =null;
        DatabaseConnection sqldbConn = getConnection();
        Connection conn = sqldbConn.getConnection();
        try{
            //Create the destination file
            file_out = new FileWriter(dir_name + File.separator + "data.bak");

            Statement st = conn.createStatement();

            //Get the number of entries
            ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM " + getTableName());
            long num_entries=0;
            while(rs.next())
                num_entries = rs.getLong(1);

            //Calculate how many request to make with a max batch of RESULT_MAX_SIZE
            long max_batch = num_entries / RESULT_MAX_SIZE;
            if( (num_entries % RESULT_MAX_SIZE) != 0)
                max_batch++;

            for(long i=0; i<max_batch; i++)
            {
                rs = st.executeQuery(this.getEntriesWithLimitQuery(i*RESULT_MAX_SIZE, RESULT_MAX_SIZE));
                while(rs.next())
                {
                    file_out.write(rs.getString(FIELD_ID)+"\t");
                    file_out.write(rs.getString(FIELD_PROPERTY_NAME)+"\t");
                    file_out.write(rs.getString(FIELD_PROPERTY_VALUE)+"\r\n");
                }
            }

        }catch(SQLException e)
        {
            String[] args = new String[2];
            args[0] = this.getId();
            args[1] = e.getMessage();
            throw new SDXException(getLog(), SDXExceptionCode.ERROR_BACKUP_DATABASE, args, e);
        }
        catch(IOException e)
        {
            String[] args = new String[2];
            args[0] = this.getId();
            args[1] = e.getMessage();
            throw new SDXException(getLog(), SDXExceptionCode.ERROR_BACKUP_DATABASE, args, e);
        }
        finally
        {
            try{
                if(conn != null)
                    releaseConnection(sqldbConn);
                file_out.close();
            }catch(IOException ee)
            {
                String[] args = new String[2];
                args[0] = this.getId();
                args[1] = ee.getMessage();
                throw new SDXException(getLog(), SDXExceptionCode.ERROR_BACKUP_DATABASE, args, ee);
            }
        }

    }

    /* Restore the data objects of the database
     * @see fr.gouv.culture.sdx.utils.save.Saveable#restore(fr.gouv.culture.sdx.utils.save.SaveParameters)
     */
    public void restore(SaveParameters save_config) throws SDXException {


    }
}
