/*
 * Copyright
 *   2005 axYus - www.axyus.com
 *   2005 C.Marchand - christophe.marchand@axyus.com
 *   2008 JP.Tessier - jean-philippe.tessier@axyus.com
 *
 * This file is part of XEMELIOS.
 *
 * XEMELIOS 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.
 *
 * XEMELIOS 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 XEMELIOS; if not, write to the Free Software
 * Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA
 */


package fr.gouv.finances.cp.xemelios.data.impl;

import fr.gouv.finances.cp.xemelios.common.Constants;
import fr.gouv.finances.cp.xemelios.data.DocumentInfos;
import fr.gouv.finances.cp.xemelios.data.impl.mysql.MySqlDataResultSet;
import fr.gouv.finances.cp.xemelios.data.impl.mysql.PesAllerPatch33;
import fr.gouv.finances.cp.xemelios.data.impl.mysql.RapportPatch33;
import fr.gouv.finances.cp.xemelios.data.impl.mysql.VersementPatch32;
import fr.gouv.finances.cp.xemelios.data.impl.sqlconfig.TSql;
import fr.gouv.finances.cp.xemelios.data.ui.DlgPatchProgress;
import fr.gouv.finances.cp.xemelios.data.ui.PatchProgress;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.UnsupportedEncodingException;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.sql.Types;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collection;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Stack;
import java.util.TreeSet;
import java.util.Vector;

import javax.swing.JOptionPane;
import javax.xml.namespace.NamespaceContext;
import javax.xml.namespace.QName;
import javax.xml.parsers.DocumentBuilder;
import javax.xml.parsers.DocumentBuilderFactory;
import javax.xml.parsers.ParserConfigurationException;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;
import javax.xml.xpath.XPath;
import javax.xml.xpath.XPathConstants;
import javax.xml.xpath.XPathExpressionException;
import javax.xml.xpath.XPathFactory;

import org.apache.commons.lang.StringEscapeUtils;
import org.apache.log4j.Logger;
import org.w3c.dom.Document;
import org.w3c.dom.Node;
import org.w3c.dom.NodeList;
import org.xml.sax.Attributes;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;

import fr.gouv.finances.cp.utils.Pair;
import fr.gouv.finances.cp.utils.PropertiesExpansion;
import fr.gouv.finances.cp.utils.xml.InvalidPathExpressionException;
import fr.gouv.finances.cp.utils.xml.PathNotFoundException;
import fr.gouv.finances.cp.utils.xml.dompath.DomPath;
import fr.gouv.finances.cp.utils.xml.xpath.FunctionResolver;
import fr.gouv.finances.cp.xemelios.auth.XemeliosUser;
import fr.gouv.finances.cp.xemelios.common.PJRef;
import fr.gouv.finances.cp.xemelios.common.Scramble;
import fr.gouv.finances.cp.xemelios.common.config.ChampModel;
import fr.gouv.finances.cp.xemelios.common.config.CritereModel;
import fr.gouv.finances.cp.xemelios.common.config.DocumentModel;
import fr.gouv.finances.cp.xemelios.common.config.DocumentsModel;
import fr.gouv.finances.cp.xemelios.common.config.ElementModel;
import fr.gouv.finances.cp.xemelios.common.config.EtatModel;
import fr.gouv.finances.cp.xemelios.common.config.RecherchePaireModel;
import fr.gouv.finances.cp.xemelios.common.config.ListeResultatModel;
import fr.gouv.finances.cp.xemelios.common.config.SpecialKeyModel;
import fr.gouv.finances.cp.xemelios.data.AbstractDataImpl;
import fr.gouv.finances.cp.xemelios.data.DataAccessException;
import fr.gouv.finances.cp.xemelios.data.DataConfigurationException;
import fr.gouv.finances.cp.xemelios.data.DataLayerManager;
import fr.gouv.finances.cp.xemelios.data.DataResultSet;
import fr.gouv.finances.cp.xemelios.data.impl.sqlconfig.TCriteria;
import fr.gouv.finances.cp.xemelios.data.impl.sqlconfig.TDocument;
import fr.gouv.finances.cp.xemelios.data.impl.sqlconfig.TEtat;
import fr.gouv.finances.cp.xemelios.data.impl.sqlconfig.TLayer;
import fr.gouv.finances.cp.xemelios.data.impl.sqlconfig.TPatch;
import fr.gouv.finances.cp.xemelios.data.impl.sqlconfig.TPersistenceConfig;
import fr.gouv.finances.cp.xemelios.data.impl.sqlconfig.TSqlIndex;
import fr.gouv.finances.cp.xemelios.data.impl.sqlconfig.TTable;
import fr.gouv.finances.cp.xemelios.data.impl.mysql.PayePatch32;
import fr.gouv.finances.cp.xemelios.export.ConfigModel;
import fr.gouv.finances.cp.xemelios.ui.MainWindow;
import fr.gouv.finances.cp.xemelios.data.ExportableData;
import fr.gouv.finances.cp.xemelios.utils.TextWriter;
import fr.gouv.finances.cp.xemelios.utils.XmlUtils;


import java.lang.reflect.*;
import java.lang.annotation.*;
import fr.gouv.finances.cp.xemelios.utils.LongueurMax;
import java.net.InetAddress;
import java.net.UnknownHostException;
/**
 * @author chm
 */
public class MySqlDataLayer extends AbstractDataImpl {
    static final transient String PARAM_TABLE_NAME = "PARAMETERS";
    /**
     * The system property designed by this name must reference a fully qualified file name
     */
    public static final transient String PROP_CONFIG_FILE_NAME = "xemelios.mysql.config.filename";
    public static final transient String PROP_MIN_POOL_SIZE = "pool-size-mini";
    private static Logger logger = Logger.getLogger(MySqlDataLayer.class);
    private static final SimpleDateFormat mySqlDateFormat = new SimpleDateFormat("yyyy-MM-dd");
    
    public static final transient String LAYER_NAME="mysql";
    private ArrayList<TPersistenceConfig> checkedConfigs = null;
    private ArrayList<Connection> availableConnections = null, usedConnections=null;
    //private boolean embedded = false;
    private PropertiesExpansion props = null;
    
    static {
        DataLayerManager.regiterDataImpl(LAYER_NAME,MySqlDataLayer.class);
    }
    private TreeSet<String> createdTables;		// TreeSet has log(n) cost for contains()
    private DocumentBuilderFactory dbf = null;
    private SAXParserFactory saxFactory = null;
    private XPathFactory xPathFactory = null;
    private DocumentBuilderFactory domFactory = null;
    
    private int minPoolSize = 1;
    private Object poolLocker = new Object();
    private Hashtable<Thread,String> warnings;
    private DlgPatchProgress progress;
    
    public MySqlDataLayer() throws DataConfigurationException {
        logger.info("instancing");
        createdTables = new TreeSet<String>();
        warnings = new Hashtable<Thread,String>();
        initConnections();
        // since 3.2, we add a new Table with application parameters
        // so create ir if necessary
        checkParamTable();
        dbf = DocumentBuilderFactory.newInstance();
        saxFactory = SAXParserFactory.newInstance();
        saxFactory.setNamespaceAware(true);
        try {
            xPathFactory = XPathFactory.newInstance(XPathFactory.DEFAULT_OBJECT_MODEL_URI);
        } catch(Throwable t) {
            xPathFactory = XPathFactory.newInstance();
        }
        xPathFactory.setXPathFunctionResolver(new FunctionResolver());
        domFactory = DocumentBuilderFactory.newInstance();
        domFactory.setNamespaceAware(true);
        applyPatches();
    }
    public String getLayerName() {
        return LAYER_NAME;
    }
    
    public void reset(){
        super.reset();
        checkedConfigs.clear();
        createdTables.clear();
    }
    public PatchProgress getPatchProgress() {
        if(progress==null) {
            progress = new DlgPatchProgress();
            progress.setVisible(true);
        }
        return progress;
    }
    protected void killPatchProgress() {
        if(progress!=null) {
            progress.setVisible(false);
            progress.dispose();
            progress=null;
        }
    }
    
    public boolean importElement(DocumentModel dm, EtatModel em, Pair codeBudget, Pair collectivite, String sourceFileName, String elementName, String strElement, String encoding) throws DataAccessException, DataConfigurationException {
        //logger.debug("importing element\n"+strElement);
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        TEtat sec = pc.getEtat(em.getId());
        Connection con = null;
        PreparedStatement ps = null;
        IndexerHandler ih = null;
        String strWarnings=null;
        try {
            con = getConnection();
            ensureTablesExists(pc,sec,codeBudget,collectivite,con);
            
            // inserting document
            StringBuilder sb = new StringBuilder();
            sb.append("INSERT INTO ").append(sec.getDocumentTable().getBaseName()).append(" (BUDGET,COLLECTIVITE, INITIAL_DOC_NAME, DOC_ID, DOC, ENCODING) VALUES (?,?,?,?,?,?)");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1,codeBudget.key);
            ps.setString(2,collectivite.key);
            ps.setString(3,sourceFileName);
            ps.setString(4,elementName);
            String strBuffer = strElement;
            byte[] buffer = strBuffer.getBytes(encoding);
            InputStream is = new ByteArrayInputStream(buffer);
            ps.setBinaryStream(5,is,buffer.length);
            ps.setString(6,encoding);
            ps.executeUpdate();
            ps.close();
            // indexing
            ih = new IndexerHandler(sec,codeBudget.key, collectivite.key,elementName,con,dm.getNamespaces());
            ih.reset();
            SAXParser parser = saxFactory.newSAXParser();
            parser.parse(new ByteArrayInputStream(strElement.getBytes(encoding)),ih);
            ih.reset();
            strWarnings = ih.getWarnings();
            ih = null;
        } catch(SQLException sqlEx) {
            sqlEx.printStackTrace();
            throw new DataAccessException(sqlEx);
        } catch(SAXException saxEx) {
            saxEx.printStackTrace();
            throw new DataAccessException(ih.getException()!=null?ih.getException():saxEx);
        } catch(ParserConfigurationException pcEx) {
            pcEx.printStackTrace();
            throw new DataConfigurationException(pcEx);
        } catch(IOException ioEx) {
            ioEx.printStackTrace();
            throw new DataAccessException(ih.getException()!=null?ih.getException():ioEx);
        } catch(Throwable t) {
            t.printStackTrace();
        } finally {
            if(con!=null) releaseConnection(con);
        }
        if(strWarnings!=null) warnings.put(Thread.currentThread(),strWarnings);
        else warnings.remove(Thread.currentThread());
        return (strWarnings==null);
    }
    public boolean saveRepository(DocumentModel dm, Pair collectivite, Document repository,Pair nomenclatureReference) throws DataConfigurationException, DataAccessException {
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        boolean bRet = false;
        try {
            con = getConnection();
            String repositoryEncoding = repository.getXmlEncoding();
            String strRepository = TextWriter.toString(repository,repositoryEncoding);
            StringBuilder sb = new StringBuilder();
            if(!doesRepositoryExist(pc,collectivite.key,con,nomenclatureReference.key)) {
                sb.append("INSERT INTO ").append(pc.getRepositoryTable().getBaseName()).append(" (LIBELLE,REPOSITORY,ENCODING,COLLECTIVITE,ID) VALUES (?,?,?,?,?)");
            } else {
                sb.append("UPDATE ").append(pc.getRepositoryTable().getBaseName()).append(" SET LIBELLE=?, REPOSITORY=?, ENCODING=? WHERE COLLECTIVITE=? AND ID=?");
            }
            ps=con.prepareStatement(sb.toString());
            byte[] buffer = strRepository.getBytes(repositoryEncoding);
            ByteArrayInputStream is = new ByteArrayInputStream(buffer);
            ps.setString(1,nomenclatureReference.libelle==null?"":nomenclatureReference.libelle);
            ps.setBinaryStream(2,is,buffer.length);
            ps.setString(3,repositoryEncoding);
            ps.setString(4,collectivite.key);
            ps.setString(5,nomenclatureReference.key==null?"":nomenclatureReference.key);
            bRet=(ps.executeUpdate()==1);
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch(UnsupportedEncodingException ueEx) {
            throw new DataConfigurationException("The char encoding of this file is unknown",ueEx);
        } finally {
            if(ps!=null) try { ps.close(); } catch(Throwable t) {}
            if(con!=null) releaseConnection(con);
        }
        return bRet;
    }
    public void registerBudgetCollectivite(DocumentModel dm, Pair codeBudget, Pair collectivite, String docName) throws DataConfigurationException, DataAccessException {
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            for(TEtat sec:pc.getEtats())
                ensureTablesExists(pc,sec,codeBudget,collectivite,con);
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT 1 FROM ").append(pc.getListBcTable().getBaseName()).append(" WHERE BUDGET=? AND COLLECTIVITE=? AND INITIAL_DOC_NAME=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1,codeBudget.key);
            ps.setString(2,collectivite.key);
            ps.setString(3,docName);
            ResultSet rs = ps.executeQuery();
            if(!rs.next()) {
                sb = new StringBuilder();
                sb.append("INSERT INTO ").append(pc.getListBcTable().getBaseName()).append(" (BUDGET,COLLECTIVITE,INITIAL_DOC_NAME,BUDGET_LIB,COLLECTIVITE_LIB) VALUES (?,?,?,?,?)");
                ps = con.prepareStatement((sb.toString()));
                ps.setString(1,codeBudget.key);
                ps.setString(2,collectivite.key);
                ps.setString(3,docName);
                ps.setString(4,codeBudget.libelle);
                ps.setString(5,collectivite.libelle);
                ps.executeUpdate();
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(ps!=null) try { ps.close(); } catch(Throwable t) {}
            if(con!=null) releaseConnection(con);
        }
    }
    public void saveSpecialKeys(DocumentModel dm, Pair budget, Pair collectivite, String docName, Pair sk1, Pair sk2, Pair sk3) throws DataConfigurationException, DataAccessException {
        if(sk1==null && sk2==null && sk3==null) return;
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            for(TEtat sec:pc.getEtats())
                ensureTablesExists(pc,sec,budget,collectivite,con);
            StringBuilder sb = new StringBuilder();
            sb.append("INSERT INTO ").append(pc.getSpecialKeyTable().getBaseName()).append(" (BUDGET,COLLECTIVITE,INITIAL_DOC_NAME,KEY1,LIB1,KEY2,LIB2,KEY3,LIB3) VALUES(?,?,?,?,?,?,?,?,?)");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1,budget.key);
            ps.setString(2,collectivite.key);
            ps.setString(3,docName);
            if(sk1!=null) {
                ps.setString(4,sk1.key);
                ps.setString(5,sk1.libelle);
            } else {
                ps.setNull(4,Types.VARCHAR);
                ps.setNull(5,Types.VARCHAR);
            }
            if(sk2!=null) {
                ps.setString(6,sk2.key);
                ps.setString(7,sk2.libelle);
            } else {
                ps.setNull(6,Types.VARCHAR);
                ps.setNull(7,Types.VARCHAR);
            }
            if(sk3!=null) {
                ps.setString(8,sk3.key);
                ps.setString(9,sk3.libelle);
            } else {
                ps.setNull(8,Types.VARCHAR);
                ps.setNull(9,Types.VARCHAR);
            }
            ps.executeUpdate();
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(ps!=null) try { ps.close(); } catch(Throwable t) {}
            if(con!=null) releaseConnection(con);
        }
    }
    public boolean canSearch(DocumentModel dm, EtatModel em) throws DataConfigurationException, DataAccessException {
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        TEtat sec = pc.getEtat(em.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            ResultSet rs = null;
            try {
                rs = con.createStatement().executeQuery("SELECT 1 FROM "+sec.getDocumentTable().getBaseName()+" LIMIT 1");
            } catch(SQLException sqlEx) {
                // table doesn't exists
                return false;
            }
            if(rs!=null) return rs.next();
            return false;
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(ps!=null) try { ps.close(); } catch(Throwable t) {}
            if(con!=null) releaseConnection(con);
        }
    }
    public Vector<Pair> getBudgets(DocumentModel dm, Pair collectivite) throws DataConfigurationException, DataAccessException {
        Vector<Pair> vRet = new Vector<Pair>();
        if(dm==null || collectivite==null) return vRet;
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT DISTINCT BUDGET, BUDGET_LIB FROM ").append(pc.getListBcTable().getBaseName()).append(" WHERE COLLECTIVITE=? ORDER BY BUDGET_LIB");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1,collectivite.key);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                vRet.add(new Pair(rs.getString(1),rs.getString(2)));
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return vRet;
    }
    public Vector<Pair> getCollectivites(DocumentModel dm) throws DataConfigurationException, DataAccessException {
        Vector<Pair> vRet = new Vector<Pair>();
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT DISTINCT COLLECTIVITE, COLLECTIVITE_LIB FROM ").append(pc.getListBcTable().getBaseName()).append(" ORDER BY COLLECTIVITE_LIB");
            ResultSet rs = con.createStatement().executeQuery(sb.toString());
            while(rs.next()) {
                vRet.add(new Pair(rs.getString(1),rs.getString(2)));
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return vRet;
    }
    public Vector<Pair> getSpecialKeys1(DocumentModel dm, Pair collectivite, Pair budget, boolean distinct) throws DataConfigurationException, DataAccessException {
        Vector<Pair> vRet = new Vector<Pair>();
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT ").append(distinct?"DISTINCT ":" ").append("KEY1,LIB1 FROM ").append(pc.getSpecialKeyTable().getBaseName()).append(" WHERE COLLECTIVITE=? AND BUDGET=?");
            PreparedStatement ps = con.prepareStatement(sb.toString());
            ps.setString(1,collectivite.key);
            ps.setString(2,budget.key);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                vRet.add(new Pair(rs.getString(1),rs.getString(2)));
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return vRet;
    }
    public Vector<Pair> getSpecialKeys2(DocumentModel dm, Pair collectivite, Pair budget, Pair key1,boolean distinct) throws DataConfigurationException, DataAccessException {
        Vector<Pair> vRet = new Vector<Pair>();
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT ").append(distinct?"DISTINCT ":" ").append("KEY2,LIB2 FROM ").append(pc.getSpecialKeyTable().getBaseName()).append(" WHERE KEY1=? AND COLLECTIVITE=? AND BUDGET=? AND KEY2 is not null");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1,key1.key);
            ps.setString(2,collectivite.key);
            ps.setString(3,budget.key);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                vRet.add(new Pair(rs.getString(1),rs.getString(2)));
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(ps!=null) { try { ps.close(); } catch(Throwable t) {} }
            if(con!=null) releaseConnection(con);
        }
        return vRet;
    }
    public Vector<Pair> getSpecialKeys3(DocumentModel dm, Pair collectivite, Pair budget, Pair key1,Pair key2,boolean distinct) throws DataConfigurationException, DataAccessException {
        Vector<Pair> vRet = new Vector<Pair>();
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT ").append(distinct?"DISTINCT ":" ").append("KEY3,LIB3 FROM ").append(pc.getSpecialKeyTable().getBaseName()).append(" WHERE KEY1=? AND KEY2=? AND COLLECTIVITE=? AND BUDGET=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1,key1.key);
            ps.setString(2,key2.key);
            ps.setString(3,collectivite.key);
            ps.setString(4,budget.key);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                vRet.add(new Pair(rs.getString(1),rs.getString(2)));
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(ps!=null) { try { ps.close(); } catch(Throwable t) {} }
            if(con!=null) releaseConnection(con);
        }
        return vRet;
    }
    public Collection<Pair> queryRepository(DocumentModel dm, Pair collectivite, RecherchePaireModel rpm) throws DataConfigurationException, DataAccessException {
        Vector<Pair> vRet = new Vector<Pair>();
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT REPOSITORY FROM ").append(pc.getRepositoryTable().getBaseName()).append(" WHERE COLLECTIVITE=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1,collectivite.key);
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getBinaryStream(1);
                int length=is.read(buff);
                while(length>0) {
                    baos.write(buff,0,length);
                    length=is.read(buff);
                }
                DocumentBuilder domBuilder = domFactory.newDocumentBuilder();
                Document repository = domBuilder.parse(new ByteArrayInputStream(baos.toByteArray()));
                XPath xp = xPathFactory.newXPath();
                xp.setNamespaceContext(dm.getNamespaces());
                NodeList nl = (NodeList)xp.evaluate(rpm.getPath().getPath(),repository,XPathConstants.NODESET);
                DomPath dpCode = rpm.getCodePath().getDomPath(dm.getNamespaces());
                DomPath dpLibelle = rpm.getLibellePath().getDomPath(dm.getNamespaces());
                for(int i=0;i<nl.getLength();i++) {
                    Node node = nl.item(i);
                    String code = (String)dpCode.getValue(node,false);
                    String libelle = (String)dpLibelle.getValue(node,true);
                    vRet.add(new Pair(code,libelle));
                }
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch(IOException ioEx) {
            throw new DataAccessException(ioEx);
        } catch(ParserConfigurationException pcEx) {
            throw new DataConfigurationException(pcEx);
        } catch(SAXException saxEx) {
            throw new DataAccessException(saxEx);
        } catch(XPathExpressionException xpEx) {
            throw new DataConfigurationException(xpEx);
        } catch(InvalidPathExpressionException ipeEx) {
            throw new DataConfigurationException(ipeEx);
        } catch(PathNotFoundException pnfEx) {
            throw new DataConfigurationException(pnfEx);
        } finally {
            if(ps!=null) { try { ps.close(); } catch(Throwable t) {} }
            if(con!=null) releaseConnection(con);
        }
        return vRet;
    }
    
    public Collection<Pair> queryRepository(DocumentModel dm, Pair collectivite, RecherchePaireModel rpm, Pair refNomenclature) throws DataConfigurationException, DataAccessException {
        Vector<Pair> vRet = new Vector<Pair>();
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT REPOSITORY FROM ").append(pc.getRepositoryTable().getBaseName()).append(" WHERE COLLECTIVITE=? AND ID=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1,collectivite.key);
            String id = "";
            if(refNomenclature!=null && refNomenclature.key!=null && refNomenclature.key.length()>0) id=refNomenclature.key;
            ps.setString(2,id);
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getBinaryStream(1);
                int length=is.read(buff);
                while(length>0) {
                    baos.write(buff,0,length);
                    length=is.read(buff);
                }
                DocumentBuilder domBuilder = domFactory.newDocumentBuilder();
                Document repository = domBuilder.parse(new ByteArrayInputStream(baos.toByteArray()));
                XPath xp = xPathFactory.newXPath();
                xp.setNamespaceContext(dm.getNamespaces());
                NodeList nl = (NodeList)xp.evaluate(rpm.getPath().getPath(),repository,XPathConstants.NODESET);
                DomPath dpCode = rpm.getCodePath().getDomPath(dm.getNamespaces());
                DomPath dpLibelle = rpm.getLibellePath().getDomPath(dm.getNamespaces());
                for(int i=0;i<nl.getLength();i++) {
                    Node node = nl.item(i);
                    String code = (String)dpCode.getValue(node,false);
                    String libelle = (String)dpLibelle.getValue(node,true);
                    vRet.add(new Pair(code,libelle));
                }
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch(IOException ioEx) {
            throw new DataAccessException(ioEx);
        } catch(ParserConfigurationException pcEx) {
            throw new DataConfigurationException(pcEx);
        } catch(SAXException saxEx) {
            throw new DataAccessException(saxEx);
        } catch(XPathExpressionException xpEx) {
            throw new DataConfigurationException(xpEx);
        } catch(InvalidPathExpressionException ipeEx) {
            throw new DataConfigurationException(ipeEx);
        } catch(PathNotFoundException pnfEx) {
            throw new DataConfigurationException(pnfEx);
        } finally {
            if(ps!=null) { try { ps.close(); } catch(Throwable t) {} }
            if(con!=null) releaseConnection(con);
        }
        return vRet;
    }
    
    public boolean isDocumentExists(DocumentModel dm, Pair budget, Pair collectivite, String docName) throws DataConfigurationException, DataAccessException {
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        boolean ret = false;
        try{
            con = getConnection();
            for(TEtat sec:pc.getEtats()) {
                ensureTablesExists(pc,sec,budget,collectivite,con);
                ret |= documentExists(pc,budget,collectivite,docName,con);
                if(ret) return true;
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return ret;
    }
    public void removeDocument(DocumentModel dm, Pair budget, Pair collectivite, String docName) throws DataConfigurationException, DataAccessException {
        logger.debug("remove document "+docName+" / "+collectivite.key+" / "+budget.key);
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        try{
            con = getConnection();
            for(TEtat sec:pc.getEtats()) {
                ensureTablesExists(pc,sec,budget,collectivite,con);
                StringBuilder sql = null;
                PreparedStatement ps = null;
                // delete from indexes
                sql = new StringBuilder();
                // bug 283
                sql.append("delete from ");
                for(TTable sti:sec.getIndexTables()) {
                    sql.append("\n").append(sti.getBaseName()).append(",");
                }
                sql.deleteCharAt(sql.length()-1);
                String docTableName = sec.getDocumentTable().getBaseName();
                sql.append("\nusing ").append(docTableName);
                for(TTable sti:sec.getIndexTables()) {
                    String baseName = sti.getBaseName();
                    sql.append("\nLEFT OUTER JOIN ").append(baseName).append(" ON (").append(baseName).append(".COLLECTIVITE=").append(docTableName).append(".COLLECTIVITE AND ").append(baseName).append(".BUDGET=").append(docTableName).append(".BUDGET AND ").append(baseName).append(".DOC_ID=").append(docTableName).append(".DOC_ID )");
                }
                sql.append("\nwhere ");
                //                for(Enumeration<SqlTableInfo> enumer=sec.getIndexTables();enumer.hasMoreElements();) {
                //                    SqlTableInfo sti = enumer.nextElement();
                //                    String tableName = sti.getBaseName();
                //                    sql.append(tableName).append(".COLLECTIVITE=").append(docTableName).append(".COLLECTIVITE AND ").append(tableName).append(".BUDGET=").append(docTableName).append(".BUDGET AND ").append(tableName).append(".DOC_ID=").append(docTableName).append(".DOC_ID AND ");
                //                }
                sql.append(docTableName).append(".COLLECTIVITE=? AND ").append(docTableName).append(".BUDGET=? AND ").append(docTableName).append(".INITIAL_DOC_NAME=?");
                //logger.debug(sql.toString());
                ps = con.prepareStatement(sql.toString());
                ps.setString(1,collectivite.key);
                ps.setString(2,budget.key);
                ps.setString(3,docName);
                ps.executeUpdate();
                
                
                // delete from special keys
                if(pc.getSpecialKeyTable()!=null) {
                    sql = new StringBuilder();
                    sql.append("delete from ").append(pc.getSpecialKeyTable().getBaseName()).append(" where COLLECTIVITE=? AND BUDGET=? AND INITIAL_DOC_NAME=?");
                    ps = con.prepareStatement(sql.toString());
                    ps.setString(1,collectivite.key);
                    ps.setString(2,budget.key);
                    ps.setString(3,docName);
                    ps.executeUpdate();
                }
                // delete from documents
                sql = new StringBuilder();
                sql.append("delete from ").append(sec.getDocumentTable().getBaseName()).append(" where COLLECTIVITE=? AND BUDGET=? AND INITIAL_DOC_NAME=?");
                ps = con.prepareStatement(sql.toString());
                ps.setString(1,collectivite.key);
                ps.setString(2,budget.key);
                ps.setString(3,docName);
                ps.executeUpdate();
                // delete from list bud/coll
                sql = new StringBuilder();
                sql.append("delete from ").append(pc.getListBcTable().getBaseName()).append(" where COLLECTIVITE=? AND BUDGET=? AND INITIAL_DOC_NAME=?");
                ps = con.prepareStatement(sql.toString());
                ps.setString(1,collectivite.key);
                ps.setString(2,budget.key);
                ps.setString(3,docName);
                ps.executeUpdate();
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
    }
    public String getRepository(DocumentModel dm, Pair collectivite, Pair budget,String idNomenclature) throws DataConfigurationException, DataAccessException {
        String ret = null;
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT REPOSITORY, ENCODING FROM ").append(pc.getRepositoryTable().getBaseName()).append(" WHERE COLLECTIVITE=? AND ID=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1,collectivite.key);
            ps.setString(2,idNomenclature);
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                String encoding = rs.getString(2);
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getBinaryStream(1);
                int length=is.read(buff);
                while(length>0) {
                    baos.write(buff,0,length);
                    length=is.read(buff);
                }
                ret = new String(baos.toByteArray(),encoding);
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch(IOException ioEx) {
            throw new DataAccessException(ioEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return ret;
    }
    
    public Vector<String> getRepositorys(DocumentModel dm, Pair collectivite, Pair budget) throws DataConfigurationException, DataAccessException {
    	Vector<String> ret = new Vector<String>();
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT REPOSITORY, ENCODING FROM ").append(pc.getRepositoryTable().getBaseName()).append(" WHERE COLLECTIVITE=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1,collectivite.key);
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                String encoding = rs.getString(2);
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getBinaryStream(1);
                int length=is.read(buff);
                while(length>0) {
                    baos.write(buff,0,length);
                    length=is.read(buff);
                }
                ret.add(new String(baos.toByteArray(),encoding));
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch(IOException ioEx) {
            throw new DataAccessException(ioEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return ret;
    }
    
    public Vector<String> getListeRepository(DocumentModel dm, Pair collectivite) throws DataConfigurationException, DataAccessException {
		// TODO pour la multi nomenclature
    	Vector<String> ret = new Vector<String>();
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT REPOSITORY, ENCODING FROM ").append(pc.getRepositoryTable().getBaseName()).append(" WHERE COLLECTIVITE=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1,collectivite.key);
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                String encoding = rs.getString(2);
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getBinaryStream(1);
                int length=is.read(buff);
                while(length>0) {
                    baos.write(buff,0,length);
                    length=is.read(buff);
                }
                ret.add(new String(baos.toByteArray(),encoding));
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch(IOException ioEx) {
            throw new DataAccessException(ioEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return ret;
	}
    
    /**
     * 
     * @param dm : DocumentModel
     * @param collectivite : collectivite pour laquelle on veux les repository
     * @return : Un vector de Pair(code,libelle) de repository
     * @throws DataConfigurationException
     * @throws DataAccessException
     */
    public Vector<Pair> getListeCodeLibelleRepository(DocumentModel dm, Pair collectivite) throws DataConfigurationException, DataAccessException {
    	Vector<Pair> ret = new Vector<Pair>();
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT ID, LIBELLE FROM ").append(pc.getRepositoryTable().getBaseName()).append(" WHERE COLLECTIVITE=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1,collectivite.key);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                String code = rs.getString(1).equals("")?"":rs.getString(1);                
                String libelle = rs.getString(2).equals("")?"Nomenclature par défaut":rs.getString(2);
                ret.add(new Pair(code,libelle));
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return ret;
	}
	
    public Document getRepositoryAsDom(DocumentModel dm, Pair collectivite,String idNomenclature) throws DataConfigurationException, DataAccessException {
        Vector<Pair> vRet = new Vector<Pair>();
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        Document repository = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT REPOSITORY FROM ").append(pc.getRepositoryTable().getBaseName()).append(" WHERE COLLECTIVITE=? AND ID=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1,collectivite.key);
            ps.setString(2,idNomenclature);
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getBinaryStream(1);
                int length=is.read(buff);
                while(length>0) {
                    baos.write(buff,0,length);
                    length=is.read(buff);
                }
                DocumentBuilder domBuilder = domFactory.newDocumentBuilder();
                repository = domBuilder.parse(new ByteArrayInputStream(baos.toByteArray()));
            }
        } catch(Throwable t) {
            throw new DataConfigurationException("invalid code !",t);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return repository;
    }
    
    public Vector<Document> getRepositorysAsDom(DocumentModel dm, Pair collectivite) throws DataConfigurationException, DataAccessException {
        Vector<Document> vRet = new Vector<Document>();
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT REPOSITORY FROM ").append(pc.getRepositoryTable().getBaseName()).append(" WHERE COLLECTIVITE=?");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1,collectivite.key);
            ResultSet rs = ps.executeQuery();
            while(rs.next()) {
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getBinaryStream(1);
                int length=is.read(buff);
                while(length>0) {
                    baos.write(buff,0,length);
                    length=is.read(buff);
                }
                DocumentBuilder domBuilder = domFactory.newDocumentBuilder();
                Document doc = domBuilder.parse(new ByteArrayInputStream(baos.toByteArray()));
                vRet.add(doc);
            }
        } catch(Throwable t) {
            throw new DataConfigurationException("invalid code !",t);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return vRet;
    }
    
    public Collection<String> getDistinctValues(ElementModel em, Pair collectivite, Pair budget, String path) throws DataConfigurationException, DataAccessException {
        TDocument pc = getPersistenceConfig(em.getParent().getParent()).getLayer("mysql").getDocument(em.getParent().getParent().getId());
        TEtat sec = pc.getEtat(em.getParent().getId());
        TSqlIndex si = sec.getSqlIndexByPath(path);
        TTable sti = sec.getIndexTable(si.getTableId());
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT DISTINCT(").append(si.getColumn()).append(") FROM ").append(sti.getBaseName()).append(" ");
        sb.append("WHERE COLLECTIVITE='").append(StringEscapeUtils.escapeSql(collectivite.key)).append("' AND BUDGET='").append(StringEscapeUtils.escapeSql(budget.key)).append("' AND ").append(si.getColumn()).append(" IS NOT NULL ORDER BY 1");
        
        Vector<String> vRet = new Vector<String>();
        Connection con = null;
        Statement st = null;
        try {
            con = getConnection();
            st = con.createStatement();
            ResultSet rs = st.executeQuery(sb.toString());
            while(rs.next()) {
                vRet.add(rs.getString(1));
            }
        } catch(SQLException sqlEx) {
            throw new DataConfigurationException("invalid code !",sqlEx);
        } finally {
            if(st!=null) try { st.close(); } catch(Throwable t) {}
            if(con!=null) releaseConnection(con);
        }
        return vRet;
    }
    public boolean removeFichier(DocumentModel dm, String fichier) throws DataConfigurationException, DataAccessException {
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        try {
            con = getConnection();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT TABLE_NAME FROM TABLES_VERSIONS");
            ArrayList<String> existingTables = new ArrayList<String>();
            while(rs.next()) existingTables.add(rs.getString(1));
            // delete from special keys
            StringBuilder sql = null;
            PreparedStatement ps = null;
            if(pc.getSpecialKeyTable()!=null) {
                if(existingTables.contains(pc.getSpecialKeyTable().getBaseName())) {
                    sql  = new StringBuilder();
                    sql.append("delete from ").append(pc.getSpecialKeyTable().getBaseName()).append(" where INITIAL_DOC_NAME=?");
                    ps = con.prepareStatement(sql.toString());
                    ps.setString(1,fichier);
                    ps.executeUpdate();
                }
            }
            // delete from list bud/coll
            sql= new StringBuilder();
            if(existingTables.contains(pc.getListBcTable().getBaseName())) {
                sql  = new StringBuilder();
                sql.append("delete from ").append(pc.getListBcTable().getBaseName()).append(" where INITIAL_DOC_NAME=?");
                ps = con.prepareStatement(sql.toString());
                ps.setString(1,fichier);
                ps.executeUpdate();
            }
            for(TEtat sec:pc.getEtats()) {
                // delete from index
                for(TTable sti:sec.getIndexTables()) {
                    if(existingTables.contains(sti.getBaseName())) {
                        sql = new StringBuilder();
                        sql.append("delete from ").append(sti.getBaseName()).append(" where DOC_ID=?");
                        ps = con.prepareStatement(sql.toString());
                        ps.setString(1,fichier);
                        ps.executeUpdate();
                    }
                }
                // delete from documents
                if(existingTables.contains(sec.getDocumentTable().getBaseName())) {
                    sql = new StringBuilder();
                    sql.append("delete from ").append(sec.getDocumentTable().getBaseName()).append(" where INITIAL_DOC_NAME=?");
                    ps = con.prepareStatement(sql.toString());
                    ps.setString(1,fichier);
                    ps.executeUpdate();
                }
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return true;
    }
    public boolean removeBudget(DocumentModel dm, Pair collectivite, Pair budget) throws DataConfigurationException, DataAccessException {
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        try {
            con = getConnection();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT TABLE_NAME FROM TABLES_VERSIONS");
            ArrayList<String> existingTables = new ArrayList<String>();
            while(rs.next()) existingTables.add(rs.getString(1));
            // delete from special keys
            StringBuilder sql = null;
            PreparedStatement ps = null;
            if(pc.getSpecialKeyTable()!=null) {
                if(existingTables.contains(pc.getSpecialKeyTable().getBaseName())) {
                    sql  = new StringBuilder();
                    sql.append("delete from ").append(pc.getSpecialKeyTable().getBaseName()).append(" where COLLECTIVITE=? AND BUDGET=?");
                    ps = con.prepareStatement(sql.toString());
                    ps.setString(1,collectivite.key);
                    ps.setString(2,budget.key);
                    ps.executeUpdate();
                }
            }
            // delete from list bud/coll
            sql= new StringBuilder();
            if(existingTables.contains(pc.getListBcTable().getBaseName())) {
                sql  = new StringBuilder();
                sql.append("delete from ").append(pc.getListBcTable().getBaseName()).append(" where COLLECTIVITE=? AND BUDGET=?");
                ps = con.prepareStatement(sql.toString());
                ps.setString(1,collectivite.key);
                ps.setString(2,budget.key);
                ps.executeUpdate();
            }
            for(TEtat sec:pc.getEtats()) {
                // delete from index
                for(TTable sti:sec.getIndexTables()) {
                    if(existingTables.contains(sti.getBaseName())) {
                        sql = new StringBuilder();
                        sql.append("delete from ").append(sti.getBaseName()).append(" where COLLECTIVITE=? AND BUDGET=?");
                        ps = con.prepareStatement(sql.toString());
                        ps.setString(1,collectivite.key);
                        ps.setString(2,budget.key);
                        ps.executeUpdate();
                    }
                }
                // delete from documents
                if(existingTables.contains(sec.getDocumentTable().getBaseName())) {
                    sql = new StringBuilder();
                    sql.append("delete from ").append(sec.getDocumentTable().getBaseName()).append(" where COLLECTIVITE=? AND BUDGET=?");
                    ps = con.prepareStatement(sql.toString());
                    ps.setString(1,collectivite.key);
                    ps.setString(2,budget.key);
                    ps.executeUpdate();
                }
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return true;
    }
    public boolean removeCollectivite(DocumentModel dm, Pair collectivite) throws DataConfigurationException, DataAccessException {
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            Statement st = con.createStatement();
            ResultSet rs = st.executeQuery("SELECT TABLE_NAME FROM TABLES_VERSIONS");
            ArrayList<String> existingTables = new ArrayList<String>();
            while(rs.next()) existingTables.add(rs.getString(1));
            StringBuilder sql = null;
            // delete from special keys
            if(pc.getSpecialKeyTable()!=null) {
                if(existingTables.contains(pc.getSpecialKeyTable().getBaseName())) {
                    logger.debug("delete from "+pc.getSpecialKeyTable().getBaseName());
                    sql = new StringBuilder();
                    sql.append("delete from ").append(pc.getSpecialKeyTable().getBaseName()).append(" where COLLECTIVITE=?");
                    ps = con.prepareStatement(sql.toString());
                    ps.setString(1,collectivite.key);
                    ps.executeUpdate();
                }
            }
            // delete from list bud/coll
            if(existingTables.contains(pc.getListBcTable().getBaseName())) {
                logger.debug("delete from "+pc.getListBcTable().getBaseName());
                sql = new StringBuilder();
                sql.append("delete from ").append(pc.getListBcTable().getBaseName()).append(" where COLLECTIVITE=?");
                ps = con.prepareStatement(sql.toString());
                ps.setString(1,collectivite.key);
                ps.executeUpdate();
            }
            // delete from repository (it is the only case)
            if(existingTables.contains(pc.getRepositoryTable().getBaseName())) {
                logger.debug("delete from "+pc.getRepositoryTable().getBaseName());
                sql = new StringBuilder();
                sql.append("delete from ").append(pc.getRepositoryTable().getBaseName()).append(" where COLLECTIVITE=?");
                ps = con.prepareStatement(sql.toString());
                ps.setString(1,collectivite.key);
                ps.executeUpdate();
            }
            for(TEtat sec:pc.getEtats()) {
                // delete from index
                for(TTable sti:sec.getIndexTables()) {
                    if(existingTables.contains(sti.getBaseName())) {
                        logger.debug("delete from "+sti.getBaseName());
                        sql = new StringBuilder();
                        sql.append("delete from ").append(sti.getBaseName()).append(" where COLLECTIVITE=?");
                        ps = con.prepareStatement(sql.toString());
                        ps.setString(1,collectivite.key);
                        ps.executeUpdate();
                    }
                }
                // delete from documents
                if(existingTables.contains(sec.getDocumentTable().getBaseName())) {
                    logger.debug("delete from "+sec.getDocumentTable().getBaseName());
                    sql = new StringBuilder();
                    sql.append("delete from ").append(sec.getDocumentTable().getBaseName()).append(" where COLLECTIVITE=?");
                    ps = con.prepareStatement(sql.toString());
                    ps.setString(1,collectivite.key);
                    ps.executeUpdate();
                }
            }
            // delete from attachments
            sql=new StringBuilder();
            sql.append("delete from ATTACHMENTS where COLLECTIVITE=?");
            ps = con.prepareStatement(sql.toString());
            ps.setString(1,collectivite.key);
            ps.executeUpdate();
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(ps!=null) try { ps.close(); } catch(Throwable t) {}
            if(con!=null) releaseConnection(con);
        }
        return true;
    }
    public boolean removeDocumentModel(DocumentModel dm) throws DataConfigurationException, DataAccessException {
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        try {
            con = getConnection();
            for(TEtat sec:pc.getEtats()) {
                ensureTablesExists(pc,sec,null,null,con);
                StringBuilder sql = new StringBuilder();
                Statement st = null;
                // delete from index
                for(TTable sti:sec.getIndexTables()) {
                    sql.append("delete from ").append(sti.getBaseName());
                    st = con.createStatement();
                    st.executeUpdate(sql.toString());
                    sql = new StringBuilder();
                    st.close();
                }
                st = con.createStatement();
                // delete from special keys
                if(pc.getSpecialKeyTable()!=null) {
                    sql.append("delete from ").append(pc.getSpecialKeyTable().getBaseName());
                    st.executeUpdate(sql.toString());
                }
                // delete from documents
                sql = new StringBuilder();
                sql.append("delete from ").append(sec.getDocumentTable().getBaseName());
                st.executeUpdate(sql.toString());
                // delete from list bud/coll
                sql = new StringBuilder();
                sql.append("delete from ").append(pc.getListBcTable().getBaseName());
                st.executeUpdate(sql.toString());
                // delete from repository (it is the only case)
                sql = new StringBuilder();
                sql.append("delete from ").append(pc.getRepositoryTable().getBaseName());
                st.executeUpdate(sql.toString());
                st.close();
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return true;
    }
    public boolean removeSpecialKey1(DocumentModel dm, Pair collectivite, Pair budget, Pair key1) throws DataConfigurationException, DataAccessException {
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT INITIAL_DOC_NAME FROM ").append(pc.getSpecialKeyTable().getBaseName()).append(" WHERE COLLECTIVITE=? AND BUDGET=? AND KEY1=?");
            PreparedStatement ps = con.prepareStatement(sb.toString());
            ps.setString(1,collectivite.key);
            ps.setString(2,budget.key);
            ps.setString(3,key1.key);
            ResultSet rs = ps.executeQuery();
            TreeSet<String> al = new TreeSet<String>();
            while(rs.next()) al.add(rs.getString(1));
            for(String docName:al) {
                removeDocument(dm,budget,collectivite,docName);
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return true;
    }
    public boolean removeSpecialKey2(DocumentModel dm, Pair collectivite, Pair budget, Pair key1, Pair key2) throws DataConfigurationException, DataAccessException {
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        try {
            logger.debug("deleting "+dm.getId()+"/"+collectivite.libelle+"/"+budget.libelle+"/"+key1.key+"/"+key2.key);
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT INITIAL_DOC_NAME FROM ").append(pc.getSpecialKeyTable().getBaseName()).append(" WHERE COLLECTIVITE=? AND BUDGET=? AND KEY1=? AND KEY2=?");
            PreparedStatement ps = con.prepareStatement(sb.toString());
            ps.setString(1,collectivite.key);
            ps.setString(2,budget.key);
            ps.setString(3,key1.key);
            ps.setString(4,key2.key);
            ResultSet rs = ps.executeQuery();
            TreeSet<String> al = new TreeSet<String>();
            while(rs.next()) al.add(rs.getString(1));
            for(String docName:al) {
                removeDocument(dm,budget,collectivite,docName);
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return true;
    }
    public boolean removeSpecialKey3(DocumentModel dm, Pair collectivite, Pair budget, Pair key1, Pair key2, Pair key3) throws DataConfigurationException, DataAccessException {
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT INITIAL_DOC_NAME FROM ").append(pc.getSpecialKeyTable().getBaseName()).append(" WHERE COLLECTIVITE=? AND BUDGET=? AND KEY1=? AND KEY2=? AND KEY3=?");
            PreparedStatement ps = con.prepareStatement(sb.toString());
            ps.setString(1,collectivite.key);
            ps.setString(2,budget.key);
            ps.setString(3,key1.key);
            ps.setString(4,key2.key);
            ps.setString(5,key3.key);
            ResultSet rs = ps.executeQuery();
            TreeSet<String> al = new TreeSet<String>();
            while(rs.next()) al.add(rs.getString(1));
            for(String docName:al) {
                removeDocument(dm,budget,collectivite,docName);
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return true;
    }
    public DataResultSet getEmptyDataResultSet() {
        return new MySqlDataResultSet();
    }
    public DataResultSet search(ElementModel elementModel, Pair collectivite, Pair budget, String xPath,ListeResultatModel lrm,Vector<CritereModel> models) throws DataConfigurationException, DataAccessException {
        String sql = transformXPathToSql(elementModel,collectivite,budget,xPath,models);
        logger.debug(sql);
        DataResultSet rs = null;
        try {
            rs = new MySqlDataResultSet(elementModel,collectivite,budget,xPath,sql,lrm,this);
        } catch(SQLException sqlEx) {
            throw new DataConfigurationException(sqlEx);
        }
        return rs;
    }
    public Pair[] getOperators(String datatype) {
//        logger.debug("operators for type "+datatype+" have been requested");
        if("string".equals(datatype)) {
            return new Pair[] { new Pair("starts-with", "commence par"), new Pair("contains", "contient"), new Pair("xem:ends-with", "fini par"), new Pair("xem:StringEquals", "égal à "), new Pair("xem:StringDiffers", "diffère de"), new Pair("xem:StringIsNull","est nul"), new Pair("xem:StringIsNotNull","n'est pas nul") };
        } else if("numeric".equals(datatype)) {
            return new Pair[] { new Pair("=", "="), new Pair(">=", ">="), new Pair(">", ">"), new Pair("<", "<"), new Pair("<=", "<="), new Pair("!=", "!=") };
        } else if("date".equals(datatype)) {
//            logger.debug("giving operators for date");
            return new Pair[] { new Pair("xem:DateBefore", "avant le"), new Pair("xem:DateEquals", "le"), new Pair("xem:DateAfter","après le") } ;
        }
        return null;
    }
    
    public String getDocumentAsString(EtatModel etatModel, Pair collectivite, Pair budget, String docId) throws DataConfigurationException, DataAccessException {
        DocumentModel dm = etatModel.getParent();
        String ret = null;
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(etatModel.getParent().getId());
        TEtat sec = pc.getEtat(etatModel.getId());
        Connection con = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT DOC, ENCODING FROM ").append(sec.getDocumentTable().getBaseName()).append(" WHERE BUDGET=? AND COLLECTIVITE=? AND DOC_ID=?");
            PreparedStatement ps = con.prepareStatement(sb.toString());
            ps.setString(1,budget.key);
            ps.setString(2,collectivite.key);
            ps.setString(3,docId);
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                String encoding = rs.getString(2);
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getAsciiStream(1);
                int length=is.read(buff);
                while(length>0) {
                    baos.write(buff,0,length);
                    length=is.read(buff);
                }
                ret = new String(baos.toByteArray(),encoding);
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch(IOException ioEx) {
            throw new DataAccessException(ioEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        //        logger.debug(ret);
        return ret;
    }
    public Document getDocumentAsDom(EtatModel etatModel, Pair collectivite, Pair budget, String docId) throws DataConfigurationException, DataAccessException {
        DocumentModel dm = etatModel.getParent();
        Document ret = null;
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(etatModel.getParent().getId());
        TEtat sec = pc.getEtat(etatModel.getId());
        Connection con = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT DOC FROM ").append(sec.getDocumentTable().getBaseName()).append(" WHERE BUDGET=? AND COLLECTIVITE=? AND DOC_ID=?");
            PreparedStatement ps = con.prepareStatement(sb.toString());
            ps.setString(1,budget.key);
            ps.setString(2,collectivite.key);
            ps.setString(3,docId);
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getAsciiStream(1);
                int length=is.read(buff);
                while(length>0) {
                    baos.write(buff,0,length);
                    length=is.read(buff);
                }
                DocumentBuilder builder = domFactory.newDocumentBuilder();
                byte[] byteArray = baos.toByteArray();
                InputStream bis = new ByteArrayInputStream(byteArray);
                ret = builder.parse(bis);
                if("true".equals(System.getProperty(Constants.SYS_PROP_LOG_DOM_DOC))) {
                    logger.debug(new String(byteArray));
                }
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch(IOException ioEx) {
            throw new DataAccessException(ioEx);
        } catch (ParserConfigurationException pcEx) {
            throw new DataConfigurationException(pcEx);
        } catch (SAXException saxEx) {
            throw new DataAccessException(saxEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return ret;
    }
    public DocumentInfos getDocumentInfos(EtatModel etatModel, Pair collectivite, Pair budget, String docId) throws DataConfigurationException, DataAccessException {
        DocumentModel dm = etatModel.getParent();
        Document ret = null;
        String encoding = null;
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(etatModel.getParent().getId());
        TEtat sec = pc.getEtat(etatModel.getId());
        Connection con = null;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT DOC, ENCODING FROM ").append(sec.getDocumentTable().getBaseName()).append(" WHERE BUDGET=? AND COLLECTIVITE=? AND DOC_ID=?");
            PreparedStatement ps = con.prepareStatement(sb.toString());
            ps.setString(1,budget.key);
            ps.setString(2,collectivite.key);
            ps.setString(3,docId);
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                encoding = rs.getString(2);
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getAsciiStream(1);
                int length=is.read(buff);
                while(length>0) {
                    baos.write(buff,0,length);
                    length=is.read(buff);
                }
                DocumentBuilder builder = domFactory.newDocumentBuilder();
                byte[] byteArray = baos.toByteArray();
                InputStream bis = new ByteArrayInputStream(byteArray);
                ret = builder.parse(bis);
                if("true".equals(System.getProperty(Constants.SYS_PROP_LOG_DOM_DOC))) {
                    logger.debug(new String(byteArray));
                }
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch(IOException ioEx) {
            throw new DataAccessException(ioEx);
        } catch (ParserConfigurationException pcEx) {
            throw new DataConfigurationException(pcEx);
        } catch (SAXException saxEx) {
            throw new DataAccessException(saxEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return new DocumentInfos(docId,ret,encoding);
    }
    
    private Connection createNewConnection() throws SQLException {
        logger.info("create new connection");
        Connection con = DriverManager.getConnection(props.getProperty("database.url"),props);
        Statement st = null;
        ResultSet rs = null;
        try {
            st = con.createStatement();
            st.execute("SET sql_mode=''");
            st.close();
            st = null;
            //        	st = con.createStatement();
            //        	rs = st.executeQuery("SELECT @@sql_mode");
            //        	while(rs.next()) logger.info("sql_mode="+rs.getString(1));
        } finally {
            if(rs!=null) rs.close();
            if(st!=null) st.close();
        }
        return con;
    }
    public void releaseConnection(Connection con) {
        //        logger.debug(con.getClass().getName());
        synchronized(poolLocker) {
            if(usedConnections.contains(con)) {
                usedConnections.remove(con);
            } else {
                Exception ex = new Exception();
                logger.warn("unknown connection to release !!!",ex);
            }
            if(usedConnections.size()+availableConnections.size()>minPoolSize) {
                logger.info("closing connection");
                try { con.close(); } catch(SQLException sqlEx) { logger.warn("while closing connection",sqlEx); }
            } else {
                availableConnections.add(con);
            }
        }
    }
    public Connection getConnection() throws SQLException {
        synchronized(poolLocker) {
            Connection con = null;
            if(availableConnections.size()>0) {
                con = availableConnections.remove(0);
            } else {
                con = createNewConnection();
            }
            usedConnections.add(con);
            return con;
        }
    }
    /**
     * Tests if this docName has already been imported
     * @param sec
     * @param codeBudget
     * @param collectivite
     * @param docName
     * @return <tt>true</tt> is this <tt>docName</tt> has been imported
     * @throws SQLException
     */
    protected boolean documentExists(TDocument pc,Pair codeBudget,Pair collectivite, String docName, Connection con) throws SQLException {
        PreparedStatement ps = null;
        boolean bRet = false;
        try {
            StringBuilder sb = new StringBuilder();
            sb.append("select 1 from ").append(pc.getListBcTable().getBaseName()).append(" where INITIAL_DOC_NAME=? AND BUDGET=? AND COLLECTIVITE=? LIMIT 1");
            ps = con.prepareStatement(sb.toString());
            ps.setString(1,docName);
            ps.setString(2,codeBudget.key);
            ps.setString(3,collectivite.key);
            ResultSet rs = ps.executeQuery();
            bRet = rs.next();
            rs.close();
        } finally {
            if(ps!=null) try { ps.close(); } catch(Throwable t) {}
        }
        return bRet;
    }
    protected boolean doesRepositoryExist(TDocument pc, String collectivite, Connection con,String idNomenclature) throws SQLException {
        boolean bRet = false;
        StringBuilder sb = new StringBuilder();
        sb.append("SELECT 1 FROM ").append(pc.getRepositoryTable().getBaseName()).append(" WHERE COLLECTIVITE=? AND ID=?");
        PreparedStatement ps = null;
        try {
            ps = con.prepareStatement(sb.toString());
            ps.setString(1,collectivite);
            ps.setString(2,idNomenclature==null?"":idNomenclature);
            ResultSet rs = ps.executeQuery();
            bRet = rs.next();
            rs.close();
        } finally {
            if(ps!=null) try { ps.close(); } catch(Throwable t) {}
        }
        return bRet;
    }
    
    protected void ensureTablesExists(TDocument pc, TEtat sec, Pair codeBudget, Pair collectivite, Connection con) throws SQLException {
        Statement st = con.createStatement();
        createTable(pc.getListBcTable(),st);
        createTable(pc.getRepositoryTable(),st);
        createTable(pc.getSpecialKeyTable(),st);
        
        createTable(sec.getDocumentTable(),st);
        for(TTable table:sec.getIndexTables()) {
            createTable(table,st);
        }
        st.close();
    }
    private void createTable(TTable sti, Statement st) throws SQLException {
        if(sti==null) return;
        if(!createdTables.contains(sti.getBaseName())) {
            st.executeUpdate(sti.getScript());
            if(st.executeQuery("SELECT 1 FROM TABLES_VERSIONS WHERE TABLE_NAME='"+sti.getBaseName()+"'").next()) {
                st.executeUpdate("UPDATE TABLES_VERSIONS SET VERSION="+sti.getVersion()+" WHERE TABLE_NAME='"+sti.getBaseName()+"'");
            } else {
                st.executeUpdate("INSERT INTO TABLES_VERSIONS (TABLE_NAME,VERSION) VALUES ('"+sti.getBaseName()+"',"+sti.getVersion()+")");
            }
            createdTables.add(sti.getBaseName());
        }
    }
    private void updateTableVersion(TTable table,Connection con) throws SQLException {
        String sql = "SELECT VERSION FROM TABLES_VERSIONS WHERE TABLE_NAME=?";
        PreparedStatement ps = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            ps = con.prepareStatement(sql);
            ps.setString(1,table.getBaseName());
            rs = ps.executeQuery();
            if(rs.next()) {
                int existingVersion = rs.getInt(1);
                if(existingVersion>=table.getVersion()) return;
                TPatch patch = table.getPatchFromVersion(existingVersion);
                st = con.createStatement();
                for(TSql order:patch.getOrders()) {
                    SQLException sqlEx = null;
                    try {
                        st.execute(order.getSql());
                    } catch(SQLException ex) {
                        sqlEx = ex;
                    }
                    if(sqlEx!=null && order.isFailOnError()) throw sqlEx;
                }
                rs.close();
                sql = "UPDATE TABLES_VERSIONS SET VERSION=? WHERE TABLE_NAME=?";
                ps=con.prepareStatement(sql);
                ps.setInt(1,table.getVersion());
                ps.setString(2,table.getBaseName());
                ps.executeUpdate();
            } else {
                st = con.createStatement();
                createTable(table,st);
            }
        } finally {
            if(rs!=null) { try { rs.close(); } catch(Throwable t) {} }
            if(st!=null) { try { st.close(); } catch(Throwable t) {} }
            if(ps!=null) { try { ps.close(); } catch(Throwable t) {} }
        }
    }
    
    protected String transformXPathToSql(ElementModel elementModel, Pair collectivite,Pair budget,String xPath,Vector<CritereModel> models) throws DataConfigurationException {
        EtatModel etatModel = elementModel.getParent();
        DocumentModel documentModel = etatModel.getParent();
        
        TDocument pc = getPersistenceConfig(documentModel).getLayer("mysql").getDocument(documentModel.getId());
        TEtat sec = pc.getEtat(etatModel.getId());
        
        // ensure xPath expr starts with element path
        if(!xPath.substring(0,elementModel.getPath().getPath().length()).equals(elementModel.getPath().getPath())) {
            throw new DataConfigurationException("XPath: "+xPath+" does not start with element["+elementModel.getId()+"] path");
        }
        
        StringBuilder sb = new StringBuilder();
        
        // splitts by OR clause
        ArrayList<CritereModel> part = new ArrayList<CritereModel>();
        boolean firstPart = true;
        if (models!=null) {
            for(CritereModel cm:models) {
                TCriteria ci = sec.getCriteria(cm.getId());
                if(ci==null) throw new DataConfigurationException("no criteria persistence info found for "+cm.getId());
                cm.setAdditionnalData(ci);
                //            String andOr = cm.getProperty("AND_OR");
                part.add(cm);
            }
        }
        appendRequest(part,sb,sec,collectivite,budget, firstPart);
        return sb.toString();
    }
    protected void appendRequest(ArrayList<CritereModel> criterias, StringBuilder sb, TEtat sec,Pair collectivite,Pair budget, boolean firstPart) throws DataConfigurationException {
        TTable mainTable = sec.getMaintable();
        TreeSet<TTable> criteriasUsedTables = new TreeSet<TTable>();
        
        StringBuilder fromClause = new StringBuilder();
        StringBuilder whereClause = new StringBuilder();
        sb.append("SELECT SQL_CALC_FOUND_ROWS DISTINCT(").append(mainTable.getBaseName()).append(".DOC_ID) FROM ");
        sb.append(sec.getMaintable().getBaseName());
        
        whereClause.append(" WHERE ");
        
        whereClause.append(mainTable.getBaseName()).append(".COLLECTIVITE='").append(StringEscapeUtils.escapeSql(collectivite.key)).append("' AND ").append(mainTable.getBaseName()).append(".BUDGET='").append(StringEscapeUtils.escapeSql(budget.key)).append("' AND ( (");
        for(CritereModel cm:criterias) {
            TCriteria sci = (TCriteria)cm.getAdditionnalData();
            if(sci.getSqlIndexId()==null) {
                logger.warn("try to use a mis-defined criteria : "+sci.getId());
                continue;
            }
            TSqlIndex si = sec.getSqlIndexById(sci.getSqlIndexId());
            TTable sti = sec.getIndexTable(si.getTableId());
            if(cm.getProperty("AND_OR")!=null) {
                String andOr = cm.getProperty("AND_OR");
                if("||".equals(andOr)) {
                    whereClause.append(") OR (");
                } else whereClause.append(" AND ");
            }
            String s = sci.getSubstitutedWhereClause(cm);
            s=s.replaceAll("\\$\\{table\\}",sti.getBaseName());
            s=s.replaceAll("\\$\\{column\\}",si.getColumn());
            whereClause.append(s);
            criteriasUsedTables.add(sti);
        }
        if(whereClause.substring(sb.length()-6).equals(" AND ")) for(int i=0;i<4;i++) whereClause.deleteCharAt(whereClause.length()-1);
        if(whereClause.substring(sb.length()-5).equals(" OR ")) for(int i=0;i<3;i++) whereClause.deleteCharAt(whereClause.length()-1);
        whereClause.append(") )");
        
        for(TTable sti:sec.getIndexTables()) {
            if(!sti.isMainTable() && criteriasUsedTables.contains(sti)) {
                fromClause.append(" LEFT OUTER JOIN ").append(sti.getBaseName()).append(" ON ").append(sec.getMaintable().getBaseName()).append(".DOC_ID=").append(sti.getBaseName()).append(".DOC_ID AND ").append(sec.getMaintable().getBaseName()).append(".COLLECTIVITE=").append(sti.getBaseName()).append(".COLLECTIVITE AND ").append(sec.getMaintable().getBaseName()).append(".BUDGET=").append(sti.getBaseName()).append(".BUDGET");
            }
        }
        
        sb.append(fromClause.toString()).append(whereClause.toString());
    }
    protected String[] evaluateCondition(String cond) {
        // [ path, operator, value ]
        String[] ret = new String[3];
        return ret;
    }
    public ResultSet execute(String sql) throws SQLException {
        Connection con = null;
        ResultSet rs = null;
        con = getConnection();
        Statement st = con.createStatement();
        rs = st.executeQuery(sql);
        return rs;
    }
    
    private void initConnections() throws DataConfigurationException {
        String configFileName = System.getProperty(PROP_CONFIG_FILE_NAME);
        props = new PropertiesExpansion();
        for(Object key:System.getProperties().keySet()) {
            props.put(key,System.getProperty((String)key));
        }
        Connection pCon = null;
        try {
            props.load(new FileInputStream(configFileName));
            // uncrypt password
            String cipheredPassword = props.getProperty("password");
            String clearPassword = Scramble.unScramblePassword(cipheredPassword);
            props.setProperty("password",clearPassword);
            Class.forName(props.getProperty("driver.class"));
            String sTmp = props.getProperty(PROP_MIN_POOL_SIZE);
            if(sTmp==null || sTmp.length()==0) sTmp="1";
            minPoolSize = 1;
            try { minPoolSize = Integer.parseInt(sTmp); } catch(NumberFormatException nfEx) { logger.error("property "+PROP_MIN_POOL_SIZE+" has an invalid value"); }
            availableConnections = new ArrayList<Connection>(minPoolSize);
            usedConnections = new ArrayList<Connection>();
            checkedConfigs = new ArrayList<TPersistenceConfig>();
            for(int i=0;i<minPoolSize;i++) availableConnections.add(createNewConnection());
            addShutdownHook(availableConnections, usedConnections);
            pCon = getConnection();
            Statement st = pCon.createStatement();
            st.executeUpdate("CREATE TABLE IF NOT EXISTS TABLES_VERSIONS (TABLE_NAME VARCHAR(64) NOT NULL, VERSION INT NOT NULL)");
            st.executeUpdate("CREATE TABLE IF NOT EXISTS ATTACHMENTS (COLLECTIVITE VARCHAR(15) NOT NULL, PJ_NAME VARCHAR(270) NOT NULL, FILE_NAME VARCHAR(280) NOT NULL, DATA LONGBLOB, INDEX IX_PJ (COLLECTIVITE,PJ_NAME) ) TYPE MYISAM");
            // table des configurations des exports
            st.executeUpdate("CREATE TABLE IF NOT EXISTS CONFIGS_EXPORT (ID int unsigned NOT NULL auto_increment, DOC_ID varchar(100) NOT NULL, ETAT_ID varchar(100) NOT NULL, OWNER varchar(25) NOT NULL, DATE_CREA datetime NOT NULL, CODE varchar(25) NOT NULL, LIBELLE VARCHAR(200) NOT NULL, CONFIG longblob NOT NULL, PRIMARY KEY (ID, DOC_ID, ETAT_ID))");
            st.close();
            //	      DEBUG
            DatabaseMetaData dmd = pCon.getMetaData();
            logger.info("driver = "+dmd.getDriverName()+" - "+dmd.getDriverVersion());
            logger.info("database = "+dmd.getDatabaseProductName()+" - "+dmd.getDatabaseProductVersion());
            if(logger.isDebugEnabled()) {
                Vector<String> v = new Vector<String>();
                ResultSet rs = pCon.createStatement().executeQuery("SHOW TABLES");
                while(rs.next()) v.add(rs.getString(1));
                for(String s:v) {
                    ResultSet rs2 = pCon.createStatement().executeQuery("SELECT CONCAT('"+s+"   ',CONV(COUNT(*),10,10)) FROM "+s);
                    if(rs2.next()) logger.debug(rs2.getString(1));
                }
            }
//            releaseConnection(pCon);
        } catch(Throwable t) {
            logger.error("in initConnections()",t);
            StringBuilder sb = new StringBuilder();
            sb.append("Impossible de se connecter au serveur Xemelios :\ndatabase.url=").append(props.getProperty("database.url"));
            throw new DataConfigurationException(sb.toString(),t);
        } finally {
            if(pCon!=null) releaseConnection(pCon);
        }
    }
    private void addShutdownHook(ArrayList<Connection> available,ArrayList<Connection> used) {
        Runtime.getRuntime().addShutdownHook(new ConnectionFinalizer(available,used));
    }
    private class IndexerHandler extends DefaultHandler {
        private TEtat sec = null;
        private Stack<QName> stack = null;
        private String key1 = null, key2 = null, key3 = null, key4 = null, key5 = null, key6 = null, key7 = null, key8 = null, key9 = null, key10 = null;
        private String pathToResetKey1 = null, pathToResetKey2 = null, pathToResetKey3 = null, pathToResetKey4 = null, pathToResetKey5 = null, pathToResetKey6 = null, pathToResetKey7 = null, pathToResetKey8 = null, pathToResetKey9 = null, pathToResetKey10 = null;
        private String collectivite = null, budget=null, docId=null;
        private SQLException sqlEx = null;
        private long indexDuration = 0L;
        private Hashtable<TSqlIndex,Object> cachedData = null;
        private Connection con = null;
        private StringBuffer warnings;
        private Stack<StringBuffer> datas  =null;
        private NamespaceContext nsCtx = null;
        public long getIndexDuration() {
            return indexDuration;
        }
        public void reset() {
            indexDuration = 0L;
            if(cachedData!=null) {
                cachedData.clear();
            }
            if(stack!=null) stack.clear();
            //            sec = null;
            key1 = null; key2 = null; key3 = null; key4 = null; key5 = null ; key6 = null; key7 = null; key8 = null; key9 = null; key10 = null;
            pathToResetKey1 = null; pathToResetKey2 = null; pathToResetKey3 = null; pathToResetKey4 = null; pathToResetKey5 = null; pathToResetKey6 = null; pathToResetKey7 = null; pathToResetKey8 = null; pathToResetKey9 = null; pathToResetKey10 = null;
        }
        public IndexerHandler(TEtat sec, String codeBudget, String codeCollectivite, String elementName, Connection con,NamespaceContext nsCtx) throws SQLException {
            super();
            this.sec = sec;
            stack = new Stack<QName>();
            this.collectivite = codeCollectivite;
            this.budget = codeBudget;
            this.docId = elementName;
            cachedData = new Hashtable<TSqlIndex,Object>();
            this.con=con;
            warnings=new StringBuffer();
            datas = new Stack<StringBuffer>();
            this.nsCtx=nsCtx;
            if(nsCtx==null) throw new IllegalArgumentException("nsCtx can not be null)");
        }
        @Override
            public void startElement(String uri, String localName, String qName, Attributes attributes) throws SAXException {
            //String tagName = XmlUtils.getShortTagName(uri,localName,qName);
            QName qname = XmlUtils.getQName(uri,localName,qName);
            stack.push(qname);
            datas.push(new StringBuffer());
            String pathTag = XmlUtils.getPath(stack,nsCtx);
            for(int i=0;i<attributes.getLength();i++) {
                //String attrName = attributes.getLocalName(i);
                String prefix = nsCtx.getPrefix(attributes.getURI(i));
                StringBuffer sb = new StringBuffer();
                sb.append(pathTag).append("/@");
                if(prefix!=null && prefix.length()>0) sb.append(prefix).append(":");
                sb.append(attributes.getLocalName(i));
                String fullPath = sb.toString();
                //logger.debug("startElement: "+fullPath);
                if(sec.getKey1()!=null && sec.getKey1().getPathes().contains(fullPath)) {
                    key1=attributes.getValue(i);
                    pathToResetKey1 = pathTag;
                }
                if(sec.getKey2()!=null && sec.getKey2().getPathes().contains(fullPath)) {
                    key2=attributes.getValue(i);
                    pathToResetKey2 = pathTag;
                }
                if(sec.getKey3()!=null && sec.getKey3().getPathes().contains(fullPath)) {
                    key3=attributes.getValue(i);
                    pathToResetKey3 = pathTag;
                }
                if(sec.getKey4()!=null && sec.getKey4().getPathes().contains(fullPath)) {
                    key4=attributes.getValue(i);
                    pathToResetKey4 = pathTag;
                }
                if(sec.getKey5()!=null && sec.getKey5().getPathes().contains(fullPath)) {
                    key5=attributes.getValue(i);
                    pathToResetKey5 = pathTag;
                }
                if(sec.getKey6()!=null && sec.getKey6().getPathes().contains(fullPath)) {
                    key6=attributes.getValue(i);
                    pathToResetKey6 = pathTag;
                }
                if(sec.getKey7()!=null && sec.getKey7().getPathes().contains(fullPath)) {
                    key7=attributes.getValue(i);
                    pathToResetKey7 = pathTag;
                }
                if(sec.getKey8()!=null && sec.getKey8().getPathes().contains(fullPath)) {
                    key8=attributes.getValue(i);
                    pathToResetKey8 = pathTag;
                }
                if(sec.getKey9()!=null && sec.getKey9().getPathes().contains(fullPath)) {
                    key9=attributes.getValue(i);
                    pathToResetKey9 = pathTag;
                }
                if(sec.getKey10()!=null && sec.getKey10().getPathes().contains(fullPath)) {
                    key10=attributes.getValue(i);
                    pathToResetKey10 = pathTag;
                }
                //logger.debug(fullPath);
                TSqlIndex si = sec.getSqlIndexByPath(fullPath);
                if(si!=null) {
                    String sValue = attributes.getValue(i);
                    Object value = null;
                    if(TPersistenceConfig.DATATYPE_INTEGER.equals(si.getDatatype())) {
                        try {
                            value = new Integer(Integer.parseInt(sValue.trim()));
                        } catch(NumberFormatException ex) { /*logger.warn(value+" is not an integer"); */}
                    } else if(TPersistenceConfig.DATATYPE_DECIMAL.equals(si.getDatatype())) {
                        try {
                            value = new BigDecimal(sValue.trim());
                        } catch(NumberFormatException ex) { /*logger.warn(value+" is not a decimal"); */}
                    } else if(TPersistenceConfig.DATATYPE_FLOAT.equals(si.getDatatype())) {
                        try {
                            value = new Float(Float.parseFloat(sValue.trim()));
                        } catch(NumberFormatException ex) { /*logger.warn(value+" is not a float"); */}
                    } else if(TPersistenceConfig.DATATYPE_DATE.equals(si.getDatatype())) {
                        try {
                            value = si.getDateValue(sValue.trim());
                        } catch(java.text.ParseException ex) { logger.warn(ex.getMessage()); }
                    } else if(TPersistenceConfig.DATATYPE_STRING.equals(si.getDatatype())) {
                        value=si.getFormattedStringValue(sValue);
                    } else if(TPersistenceConfig.DATATYPE_BOOLEAN.equals(si.getDatatype())){
                        value=si.getBooleanValue(sValue.trim());
                    } else {
                        value = sValue;
                        logger.error("unknown datatype for index "+si.getId()+": "+si.getDatatype());
                    }
                    if(value!=null)
                        cachedData.put(si,value);
                }
            }
        }
        @Override
            public void endElement(String uri, String localName, String qName) throws SAXException {
            //FIXME: move index writting after /text() processing
            String pathTag = XmlUtils.getPath(stack,nsCtx);
            //logger.debug("endElement: "+pathTag);
            // reset tags
            if(pathTag.equals(pathToResetKey10)) {
                pathToResetKey10 = null;
                try {
                    writeIndexesOnKey10();
                } catch(SQLException sqlEx) {
                    this.sqlEx=sqlEx;
                    sqlEx.printStackTrace();
                    throw new SAXException("SQLException occurs. See nested exception for details)",sqlEx);
                } catch(DataConfigurationException dcEx) {
                    dcEx.printStackTrace();
                    throw new SAXException("DataConfigurationException occurs. See nested exception for details)",sqlEx);
                }
                key10 = null;
            }
            if(pathTag.equals(pathToResetKey9)) {
                pathToResetKey9 = null;
                try {
                    writeIndexesOnKey9();
                } catch(SQLException sqlEx) {
                    this.sqlEx=sqlEx;
                    sqlEx.printStackTrace();
                    throw new SAXException("SQLException occurs. See nested exception for details)",sqlEx);
                } catch(DataConfigurationException dcEx) {
                    dcEx.printStackTrace();
                    throw new SAXException("DataConfigurationException occurs. See nested exception for details)",sqlEx);
                }
                key9 = null;
            }
            if(pathTag.equals(pathToResetKey8)) {
                pathToResetKey8 = null;
                try {
                    writeIndexesOnKey8();
                } catch(SQLException sqlEx) {
                    this.sqlEx=sqlEx;
                    sqlEx.printStackTrace();
                    throw new SAXException("SQLException occurs. See nested exception for details)",sqlEx);
                } catch(DataConfigurationException dcEx) {
                    dcEx.printStackTrace();
                    throw new SAXException("DataConfigurationException occurs. See nested exception for details)",sqlEx);
                }
                key8 = null;
            }
            if(pathTag.equals(pathToResetKey7)) {
                pathToResetKey7 = null;
                try {
                    writeIndexesOnKey7();
                } catch(SQLException sqlEx) {
                    this.sqlEx=sqlEx;
                    sqlEx.printStackTrace();
                    throw new SAXException("SQLException occurs. See nested exception for details)",sqlEx);
                } catch(DataConfigurationException dcEx) {
                    dcEx.printStackTrace();
                    throw new SAXException("DataConfigurationException occurs. See nested exception for details)",sqlEx);
                }
                key7 = null;
            }
            if(pathTag.equals(pathToResetKey6)) {
                pathToResetKey6 = null;
                try {
                    writeIndexesOnKey6();
                } catch(SQLException sqlEx) {
                    this.sqlEx=sqlEx;
                    sqlEx.printStackTrace();
                    throw new SAXException("SQLException occurs. See nested exception for details)",sqlEx);
                } catch(DataConfigurationException dcEx) {
                    dcEx.printStackTrace();
                    throw new SAXException("DataConfigurationException occurs. See nested exception for details)",sqlEx);
                }
                key6 = null;
            }
            if(pathTag.equals(pathToResetKey5)) {
                pathToResetKey5 = null;
                try {
                    writeIndexesOnKey5();
                } catch(SQLException sqlEx) {
                    this.sqlEx=sqlEx;
                    sqlEx.printStackTrace();
                    throw new SAXException("SQLException occurs. See nested exception for details)",sqlEx);
                } catch(DataConfigurationException dcEx) {
                    dcEx.printStackTrace();
                    throw new SAXException("DataConfigurationException occurs. See nested exception for details)",sqlEx);
                }
                key5 = null;
            }
            if(pathTag.equals(pathToResetKey4)) {
                pathToResetKey4 = null;
                try {
                    writeIndexesOnKey4();
                } catch(SQLException sqlEx) {
                    this.sqlEx=sqlEx;
                    sqlEx.printStackTrace();
                    throw new SAXException("SQLException occurs. See nested exception for details)",sqlEx);
                } catch(DataConfigurationException dcEx) {
                    dcEx.printStackTrace();
                    throw new SAXException("DataConfigurationException occurs. See nested exception for details)",sqlEx);
                }
                key4 = null;
            }
            if(pathTag.equals(pathToResetKey3)) {
                pathToResetKey3 = null;
                try {
                    writeIndexesOnKey3();
                } catch(SQLException sqlEx) {
                    this.sqlEx=sqlEx;
                    sqlEx.printStackTrace();
                    throw new SAXException("SQLException occurs. See nested exception for details)",sqlEx);
                } catch(DataConfigurationException dcEx) {
                    dcEx.printStackTrace();
                    throw new SAXException("DataConfigurationException occurs. See nested exception for details)",sqlEx);
                }
                key3 = null;
            }
            if(pathTag.equals(pathToResetKey2)) {
                pathToResetKey2 = null;
                try {
                    writeIndexesOnKey2();
                } catch(SQLException sqlEx) {
                    this.sqlEx=sqlEx;
                    sqlEx.printStackTrace();
                    throw new SAXException("SQLException occurs. See nested exception for details)",sqlEx);
                } catch(DataConfigurationException dcEx) {
                    dcEx.printStackTrace();
                    throw new SAXException("DataConfigurationException occurs. See nested exception for details)",sqlEx);
                }
                key2 = null;
            }
            if(pathTag.equals(pathToResetKey1)) {
                pathToResetKey1 = null;
                try {
                    writeIndexesOnKey1();
                } catch(SQLException sqlEx) {
                    this.sqlEx=sqlEx;
                    sqlEx.printStackTrace();
                    throw new SAXException("SQLException occurs. See nested exception for details)",sqlEx);
                } catch(DataConfigurationException dcEx) {
                    dcEx.printStackTrace();
                    throw new SAXException("DataConfigurationException occurs. See nested exception for details)",sqlEx);
                }
                key1 = null;
            }
            String fullPath = pathTag+"/text()";
            TSqlIndex si = sec.getSqlIndexByPath(fullPath);
            if(si!=null) {
                String sValue = datas.peek().toString();
                Object value = null;
                if(TPersistenceConfig.DATATYPE_INTEGER.equals(si.getDatatype())) {
                    try {
                        value = new Integer(Integer.parseInt(sValue.trim()));
                    } catch(NumberFormatException ex) { logger.warn(sValue+" is not an integer"); }
                } else if(TPersistenceConfig.DATATYPE_DECIMAL.equals(si.getDatatype())) {
                    try {
                        value = new BigDecimal(sValue.trim());
                    } catch(NumberFormatException ex) { logger.warn(sValue+" is not a decimal"); }
                } else if(TPersistenceConfig.DATATYPE_FLOAT.equals(si.getDatatype())) {
                    try {
                        value = new Float(Float.parseFloat(sValue.trim()));
                    } catch(NumberFormatException ex) { logger.warn(sValue+" is not a float"); }
                } else if(TPersistenceConfig.DATATYPE_DATE.equals(si.getDatatype())) {
                    try {
                        value = si.getDateValue(sValue.trim());
                    } catch(java.text.ParseException ex) { logger.warn(ex.getMessage()); }
                } else if(TPersistenceConfig.DATATYPE_STRING.equals(si.getDatatype())) {
                    value=si.getFormattedStringValue(sValue);
                } else if(TPersistenceConfig.DATATYPE_BOOLEAN.equals(si.getDatatype())) {
                    String sTmp = sValue.toString();
                    if("1".equals(si.getBooleanValue(sValue))) value = Boolean.TRUE;
                    else value=Boolean.FALSE;
                } else {
                    value = sValue;
                    logger.error("unknown datatype for index "+si.getId()+": "+si.getDatatype());
                }
                if(value!=null)
                    cachedData.put(si,value);
            }
            datas.pop();
            stack.pop();
        }
        @Override
            public void endDocument() throws SAXException {
            try {
                writeElementBasedIndexes();
                cachedData.clear();
            } catch(SQLException sqlEx) {
                sqlEx.printStackTrace();
                this.sqlEx=sqlEx;
                throw new SAXException("SQLException occurs. See nested exception for details)",sqlEx);
            } catch(DataConfigurationException dcEx) {
                dcEx.printStackTrace();
                throw new SAXException("DataConfiguration occurs. See nested exception for details",dcEx);
            }
        }
        protected void writeIndexesOnKey1() throws SQLException, DataConfigurationException {
            try {
                writeIndexesOnKey("key1");
            } catch(SQLException sqlEx) {
                logger.error(sqlEx);
                throw sqlEx;
            } catch(DataConfigurationException dcEx) {
                logger.error(dcEx);
                throw dcEx;
            }
        }
        protected void writeIndexesOnKey2() throws SQLException, DataConfigurationException {
            writeIndexesOnKey("key2");
        }
        protected void writeIndexesOnKey3() throws SQLException, DataConfigurationException {
            writeIndexesOnKey("key3");
        }
        protected void writeIndexesOnKey4() throws SQLException, DataConfigurationException {
            writeIndexesOnKey("key4");
        }
        protected void writeIndexesOnKey5() throws SQLException, DataConfigurationException {
            writeIndexesOnKey("key5");
        }
        protected void writeIndexesOnKey6() throws SQLException, DataConfigurationException {
            writeIndexesOnKey("key6");
        }
        protected void writeIndexesOnKey7() throws SQLException, DataConfigurationException {
            writeIndexesOnKey("key7");
        }
        protected void writeIndexesOnKey8() throws SQLException, DataConfigurationException {
            writeIndexesOnKey("key8");
        }
        protected void writeIndexesOnKey9() throws SQLException, DataConfigurationException {
            writeIndexesOnKey("key9");
        }
        protected void writeIndexesOnKey10() throws SQLException, DataConfigurationException {
            writeIndexesOnKey("key10");
        }
        protected void writeIndexesOnKey(String keyName) throws SQLException, DataConfigurationException {
            // First, try to get index table
            TTable elementTable = null;
            for(TTable ti:sec.getIndexTables()) {
                if(keyName.equals(ti.getBasedOn())) {
                    elementTable=ti;
                    break;
                }
            }
            if(elementTable==null) throw new DataConfigurationException("No table to store "+keyName+"indexes was found !");
            //
            StringBuilder sql = new StringBuilder();
            sql.append("INSERT INTO ").append(elementTable.getBaseName()).append(" (COLLECTIVITE,BUDGET,DOC_ID,");
            Vector<Object> valuesToWrite = new Vector<Object>();
            Vector<TSqlIndex> indexesToWrite = new Vector<TSqlIndex>();
            for(Enumeration<TSqlIndex> enumer=cachedData.keys();enumer.hasMoreElements();) {
                TSqlIndex index = enumer.nextElement();
                String tableId = index.getTableId();
                TTable sti = sec.getIndexTable(tableId);
                if(sti==elementTable) {
                    // we are on the element-based table
                    sql.append(index.getColumn()).append(",");
                    indexesToWrite.add(index);
                    valuesToWrite.add(cachedData.get(index));
                    cachedData.remove(index);
                }
            }
            if(valuesToWrite.size()>0) {
                sql.deleteCharAt(sql.length()-1);
                sql.append(") VALUES (?,?,?,");
                for(int i=0;i<valuesToWrite.size();i++) sql.append("?,");
                sql.deleteCharAt(sql.length()-1);
                sql.append(")");
                PreparedStatement ps = null;
                try {
                    ps = con.prepareStatement(sql.toString());
                    ps.setString(1,collectivite);
                    ps.setString(2,budget);
                    ps.setString(3,docId);
                    for(int i=0;i<valuesToWrite.size();i++) {
                        TSqlIndex index = indexesToWrite.elementAt(i);
                        Object value = valuesToWrite.elementAt(i);
                        if(TPersistenceConfig.DATATYPE_DECIMAL.equals(index.getDatatype())) {
                            ps.setBigDecimal(i+4,(BigDecimal)value);
                        } else if(TPersistenceConfig.DATATYPE_DATE.equals(index.getDatatype())) {
                            ps.setDate(i+4,(Date)value);
                        } else if(TPersistenceConfig.DATATYPE_FLOAT.equals(index.getDatatype())) {
                            ps.setFloat(i+4,((Float)value).floatValue());
                        } else if(TPersistenceConfig.DATATYPE_INTEGER.equals(index.getDatatype())) {
                            ps.setInt(i+4,((Integer)value).intValue());
                        } else if(TPersistenceConfig.DATATYPE_BOOLEAN.equals(index.getDatatype())) {
                            ps.setInt(i+4,("true".equals(value)||"1".equals(value))?1:0);
                        } else {
                            ps.setString(i+4,value.toString());
                        }
                        cachedData.remove(index);
                    }
                    ps.executeUpdate();
                    SQLWarning warn = ps.getWarnings();
                    while(warn!=null) {
                        warnings.append(warn.getMessage()).append("\n");
                        warn=warn.getNextWarning();
                    }
                } catch(SQLException sqlEx) {
                    sqlEx.printStackTrace();
                } finally {
                    if(ps!=null) { try { ps.close(); } catch(Throwable t) {} }
                }
                //sql.clear();
            }
            valuesToWrite.clear();
            indexesToWrite.clear();
        }
        protected void writeElementBasedIndexes() throws SQLException, DataConfigurationException {
            // First, try to get index table
            TTable elementTable = null;
            for(TTable ti:sec.getIndexTables()) {
                //                if(ti.getBasedOn()==null) {
                //                    elementTable=ti;
                //                    break;
                //                }
                if(ti.isMainTable()) {
                    elementTable=ti;
                    break;
                }
            }
            if(elementTable==null) throw new DataConfigurationException("No table to store element indexes was found !");
            
            StringBuilder sql = new StringBuilder();
            sql.append("INSERT INTO ").append(elementTable.getBaseName()).append(" (COLLECTIVITE,BUDGET,DOC_ID,");
            Vector<Object> valuesToWrite = new Vector<Object>();
            Vector<TSqlIndex> indexesToWrite = new Vector<TSqlIndex>();
            for(Enumeration<TSqlIndex> enumer=cachedData.keys();enumer.hasMoreElements();) {
                TSqlIndex index = enumer.nextElement();
                String tableId = index.getTableId();
                TTable sti = sec.getIndexTable(tableId);
                if(sti==elementTable) {
                    // we are on the element-based table
                    sql.append(index.getColumn()).append(",");
                    indexesToWrite.add(index);
                    valuesToWrite.add(cachedData.get(index));
                    cachedData.remove(index);
                }
            }
            
            if(valuesToWrite.size()>0) {
                sql.deleteCharAt(sql.length()-1);
                sql.append(") VALUES (?,?,?,");
                for(int i=0;i<valuesToWrite.size();i++) sql.append("?,");
                sql.deleteCharAt(sql.length()-1);
                sql.append(")");
                PreparedStatement ps = null;
                try {
                    ps = con.prepareStatement(sql.toString());
                    ps.setString(1,collectivite);
                    ps.setString(2,budget);
                    ps.setString(3,docId);
                    for(int i=0;i<valuesToWrite.size();i++) {
                        TSqlIndex index = indexesToWrite.elementAt(i);
                        Object value = valuesToWrite.elementAt(i);
                        if(TPersistenceConfig.DATATYPE_DECIMAL.equals(index.getDatatype())) {
                            ps.setBigDecimal(i+4,(BigDecimal)value);
                        } else if(TPersistenceConfig.DATATYPE_DATE.equals(index.getDatatype())) {
                            ps.setDate(i+4,(Date)value);
                        } else if(TPersistenceConfig.DATATYPE_FLOAT.equals(index.getDatatype())) {
                            ps.setFloat(i+4,((Float)value).floatValue());
                        } else if(TPersistenceConfig.DATATYPE_INTEGER.equals(index.getDatatype())) {
                            ps.setInt(i+4,((Integer)value).intValue());
                        } else {
                            ps.setString(i+4,value.toString());
                        }
                    }
                    ps.executeUpdate();
                    SQLWarning warn = ps.getWarnings();
                    while(warn!=null) {
                        warnings.append(warn.getMessage()).append("\n");
                        warn=warn.getNextWarning();
                    }
                } catch(SQLException sqlEx) {
                    sqlEx.printStackTrace();
                } finally {
                    if(ps!=null) { try { ps.close(); } catch(Throwable t) {} }
                }
            }
            valuesToWrite.clear();
            indexesToWrite.clear();
        }
        public Exception getException() { return sqlEx; }
        @Override
            protected void finalize() throws Throwable {
            reset();
            super.finalize();
        }
        public String getWarnings() { if(warnings.length()>0) return warnings.toString(); return null; }
        @Override
            public void characters(char[] ch, int start, int length) throws SAXException {
            StringBuffer sb = datas.peek();
            sb.append(ch,start,length);
        }
    }
    
    /**
     * This class closes all the open connections
     * Use it as a shutdownHook
     * @author chm
     */
    private class ConnectionFinalizer extends Thread {
        private ArrayList<Connection> v1, v2;
        public ConnectionFinalizer(ArrayList<Connection> v1, ArrayList<Connection> v2) {
            super();
            this.v1=v1;
            this.v2=v2;
        }
        @Override
            public void run() {
            for(Connection con:v1) killConnection(con);
            for(Connection con:v2) killConnection(con);
        }
        private void killConnection(Connection con) {
            try {
                if(con!=null) {
                    if(!con.isClosed()) {
                        System.out.println("closing connection");
                        con.close();
                    }
                }
            } catch (Throwable t) {
                t.printStackTrace();
            }
        }
    }
    
    @Override
        public TPersistenceConfig getPersistenceConfig(DocumentModel dm) throws DataConfigurationException {
        TPersistenceConfig pc = super.getPersistenceConfig(dm);
        if(!checkedConfigs.contains(pc)) {
            logger.info("Chargement de la configuration "+dm.getTitre());
            boolean allOk = true;
            boolean newer = false;
            boolean older = false;
            Connection con = null;
            ArrayList<TTable> tables = new ArrayList<TTable>();
            TDocument spc = pc.getLayer("mysql").getDocument(dm.getId());
            try {
                con = getConnection();
                int ret=0;
                if(spc.getListBcTable()!=null) {
                    ret=checkTableVersion(spc.getListBcTable(),con);
                    allOk = allOk & (ret==0); if(ret<0) older=true; if(ret>0) newer=true;
                    tables.add(spc.getListBcTable());
                }
                if(spc.getRepositoryTable()!=null) {
                    ret=checkTableVersion(spc.getRepositoryTable(),con);
                    allOk = allOk & (ret==0); if(ret<0) older=true; if(ret>0) newer=true;
                    tables.add(spc.getRepositoryTable());
                }
                if(spc.getSpecialKeyTable()!=null) {
                    ret=checkTableVersion(spc.getSpecialKeyTable(),con);
                    allOk = allOk & (ret==0); if(ret<0) older=true; if(ret>0) newer=true;
                    tables.add(spc.getSpecialKeyTable());
                }
                for(TEtat sec:spc.getEtats()) {
                    ret=checkTableVersion(sec.getDocumentTable(),con);
                    allOk = allOk & (ret==0); if(ret<0) older=true; if(ret>0) newer=true;
                    tables.add(sec.getDocumentTable());
                    for(TTable table:sec.getIndexTables()) {
                        ret=checkTableVersion(table,con);
                        allOk = allOk & (ret==0); if(ret<0) older=true; if(ret>0) newer=true;
                        tables.add(table);
                    }
                }
                if(!allOk) {
                    XemeliosUser user = MainWindow.getInstance().getConnectedUser();
                    if(older) {
                        StringBuilder msg = new StringBuilder();
                        msg.append("Votre base de donnée contient des tables dont la version est plus récente\nque votre version de Xemelios.\n");
                        msg.append("Il vous faut mettre à jour votre version de Xemelios avant de pouvoir l'utiliser.");
                        JOptionPane.showMessageDialog(MainWindow.getInstance(),msg.toString(),"Problème de configuration",JOptionPane.ERROR_MESSAGE);
                        throw new DataConfigurationException("Version de Xemelios trop ancienne");
                    }
                    if(newer) {
                        //                        if(user.isAllowedTo(null,XemeliosUser.ROLE_CLEAN)) {
                        //                            StringBuilder msg = new StringBuilder();
                        //                            msg.append("Votre base de donnée contient des tables dont la version est plus ancienne\nque votre version de Xemelios.\n");
                        //                            msg.append("Afin de mettre à jour votre base de donnée,\nXemelios doit supprimer les données de ");
                        //                            msg.append(dm.getTitre()).append(". Vous devrez les réimporter.\n\nVoulez-vous continuer ?");
                        //                            if(JOptionPane.showConfirmDialog(MainWindow.getInstance(),msg.toString(),"Problème de configuration",JOptionPane.YES_NO_OPTION)==JOptionPane.YES_OPTION) {
                        if(true) {
//                            Statement st = con.createStatement();
                            for(TTable table:tables) {
                                //st.executeUpdate("DROP TABLE IF EXISTS "+tbName);
                                //st.executeUpdate("DELETE FROM TABLES_VERSIONS WHERE TABLE_NAME='"+tbName+"'");
                                updateTableVersion(table,con);
                            }
//                            st.close();
                            checkedConfigs.add(pc);
                        } else {
                            throw new DataConfigurationException("Version de base de donnée trop ancienne");
                        }
                        //                        } else {
                        //                            StringBuilder msg = new StringBuilder();
                        //                            msg.append("Votre base de donnÃ©e contient des tables dont la version est plus ancienne\nque votre version de Xemelios.\n");
                        ////                            msg.append("Afin de mettre Ã  jour votre base de donnÃ©e,\nXemelios doit supprimer les donnÃ©es de ");
                        //                            msg.append("Vous n'avez pas les droits permettant cette\nla mise Ã  jour de la base de donnÃ©es. Veuillez contacter votre administrateur pour\neffectuer cette opÃ©ration.");
                        //                            JOptionPane.showMessageDialog(MainWindow.getInstance(),msg.toString(),"ProblÃ¨me de configuration",JOptionPane.ERROR_MESSAGE);
                        //                            throw new DataConfigurationException("Version de base de donnÃ©e trop ancienne");
                        //                        }
                    }
                }
                checkedConfigs.add(pc);
            } catch(SQLException sqlEx) {
                throw new DataConfigurationException(sqlEx);
            } finally {
                if(con!=null) releaseConnection(con);
            }
        }
        return pc;
    }
    /**
     * Compares table version in database and in config file.
     * Returns <li>
     * <ul>x&lt;0 if database version is older than config version</ul>
     * <ul>x=0 if database version is same than config version</ul>
     * <ul>x&gt;0 if database version is newer than config</ul>
     * @param sti
     * @param con
     * @return
     */
    protected static int checkTableVersion(TTable sti,Connection con) {
        if(sti==null) return 0;
        PreparedStatement ps = null;
        int ret = 0;
        try {
            ps=con.prepareStatement("SELECT VERSION FROM TABLES_VERSIONS WHERE TABLE_NAME=?");
            ps.setString(1,sti.getBaseName());
            ResultSet rs = ps.executeQuery();
            if(rs.next()) ret = (sti.getVersion()-rs.getInt(1));
        } catch(SQLException sqlEx) {
            sqlEx.printStackTrace();
        } finally {
            if(ps!=null) try { ps.close(); } catch(Throwable t) {}
        }
        return ret;
    }
    
    
    public String getLastWarnings() {
        return warnings.get(Thread.currentThread());
    }
    
    public void importPj(PJRef pj) throws DataConfigurationException, DataAccessException {
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection con = null;
        try {
            con = getConnection();
            String s = "SELECT 1 FROM ATTACHMENTS WHERE COLLECTIVITE=? AND PJ_NAME=?";
            ps = con.prepareStatement(s);
            ps.setString(1,pj.getCollectivite());
            ps.setString(2,pj.getPjName());
            rs = ps.executeQuery();
            if(rs.next()) {
                ps.close();
                ps = con.prepareStatement("DELETE FROM ATTACHMENTS WHERE COLLECTIVITE=? AND PJ_NAME=?");
                ps.setString(1,pj.getCollectivite());
                ps.setString(2,pj.getPjName());
                ps.executeUpdate();
            }
            ps.close();
            ps = con.prepareStatement("INSERT INTO ATTACHMENTS (COLLECTIVITE,PJ_NAME,FILE_NAME,DATA) VALUES (?,?,?,?)");
            ps.setString(1,pj.getCollectivite());
            ps.setString(2,pj.getPjName());
            ps.setString(3,pj.getFileName());
            ps.setBinaryStream(4,pj.getDataInputStream(),(int)pj.getUncompressedSize());
            ps.executeUpdate();
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch(Exception ex) {
            throw new DataConfigurationException(ex);
        } finally {
            if(rs!=null) try { rs.close(); } catch(Throwable t) {}
            if(ps!=null) try { ps.close(); } catch(Throwable t) {}
            if(con!=null) releaseConnection(con);
        }
        
    }
    public PJRef getPj(Pair collectivite, String pjName) throws DataConfigurationException, DataAccessException {
        Connection con = null;
        PJRef pj = null;
        logger.debug("getting "+pjName+" in "+collectivite.key);
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT FILE_NAME, DATA FROM ATTACHMENTS WHERE COLLECTIVITE=? AND FILE_NAME=?");
            PreparedStatement ps = con.prepareStatement(sb.toString());
            ps.setString(1,collectivite.key);
            ps.setString(2,pjName);
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                pj = new PJRef();
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getAsciiStream(2);
                int length=is.read(buff);
                while(length>0) {
                    baos.write(buff,0,length);
                    length=is.read(buff);
                }
                pj.setData(baos.toByteArray());
                pj.setFileName(rs.getString(1));
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch(IOException ioEx) {
            throw new DataAccessException(ioEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return pj;
    }
    public PJRef getPjByUniqueId(Pair collectivite, String id) throws DataConfigurationException, DataAccessException {
        Connection con = null;
        PJRef pj = null;
        logger.debug("getting "+id+" in "+collectivite.key);
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT FILE_NAME, DATA FROM ATTACHMENTS WHERE COLLECTIVITE=? AND PJ_NAME=?");
            PreparedStatement ps = con.prepareStatement(sb.toString());
            ps.setString(1,collectivite.key);
            ps.setString(2,id);
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                pj = new PJRef();
                byte[] buff = new byte[512];
                ByteArrayOutputStream baos = new ByteArrayOutputStream();
                InputStream is = rs.getAsciiStream(2);
                int length=is.read(buff);
                while(length>0) {
                    baos.write(buff,0,length);
                    length=is.read(buff);
                }
                pj.setData(baos.toByteArray());
                pj.setFileName(rs.getString(1));
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } catch(IOException ioEx) {
            throw new DataAccessException(ioEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return pj;
    }
    public boolean isPjAvailable(Pair collectivite, String id) throws DataConfigurationException, DataAccessException {
        Connection con = null;
        boolean ret = false;
        //        logger.debug("looking for "+id+" in "+collectivite.key);
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT 1 FROM ATTACHMENTS WHERE COLLECTIVITE=? AND FILE_NAME=?");
            PreparedStatement ps = con.prepareStatement(sb.toString());
            ps.setString(1,collectivite.key);
            ps.setString(2,id);
            ResultSet rs = ps.executeQuery();
            if(rs.next()) {
                ret = true;
            }
            rs.close();
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return ret;
    }
    
    public ArrayList<String> getDocumentListFromSpecialKeys(ElementModel em, String collectivite, String budget, String sp1, String sp2, String sp3) throws DataConfigurationException, DataAccessException {
        ArrayList<String> ret = new ArrayList<String>();
        StringBuffer sb = new StringBuffer();
        EtatModel etatModel = em.getParent();
        TDocument pc = getPersistenceConfig(etatModel.getParent()).getLayer("mysql").getDocument(etatModel.getParent().getId());
        TEtat sec = pc.getEtat(etatModel.getId());
        sb.append("SELECT DOC_ID FROM ").append(sec.getMaintable().getBaseName()).append(" WHERE COLLECTIVITE=? AND BUDGET=? ");
        DocumentModel dm = etatModel.getParent();
        Vector<SpecialKeyModel> specialKeys = dm.getSpecialKeys();
        if(sp1!=null) {
            // search SPK1 XPath
            SpecialKeyModel skm = null;
            for(SpecialKeyModel sk:specialKeys) {
                if(sk.getPos()==1) skm = sk;
            }
            if(skm!=null) {
                String path = skm.getPath();
                TSqlIndex si = sec.getSqlIndexByPath(path);
                if(si.getTableId().equals(sec.getMaintable().getId())) {
                    // ok, it is the same table
                    sb.append("AND ").append(si.getColumn()).append("=? ");
                } else {
                    throw new DataConfigurationException(path+" is not indexed in "+sec.getMaintable().getBaseName());
                }
            } else {
                throw new DataConfigurationException("no special key in pos 1 defined for "+dm.getId());
            }
        }
        if(sp2!=null) {
            // search SPK1 XPath
            SpecialKeyModel skm = null;
            for(SpecialKeyModel sk:specialKeys) {
                if(sk.getPos()==2) skm = sk;
            }
            if(skm!=null) {
                String path = skm.getPath();
                TSqlIndex si = sec.getSqlIndexByPath(path);
                if(si.getTableId().equals(sec.getMaintable().getId())) {
                    // ok, it is the same table
                    sb.append("AND ").append(si.getColumn()).append("=? ");
                } else {
                    throw new DataConfigurationException(path+" is not indexed in "+sec.getMaintable().getBaseName());
                }
            } else {
                throw new DataConfigurationException("no special key in pos 2 defined for "+dm.getId());
            }
        }
        if(sp3!=null) {
            // search SPK3 XPath
            SpecialKeyModel skm = null;
            for(SpecialKeyModel sk:specialKeys) {
                if(sk.getPos()==3) skm = sk;
            }
            if(skm!=null) {
                String path = skm.getPath();
                TSqlIndex si = sec.getSqlIndexByPath(path);
                if(si.getTableId().equals(sec.getMaintable().getId())) {
                    // ok, it is the same table
                    sb.append("AND ").append(si.getColumn()).append("=? ");
                } else {
                    throw new DataConfigurationException(path+" is not indexed in "+sec.getMaintable().getBaseName());
                }
            } else {
                throw new DataConfigurationException("no special key in pos 3 defined for "+dm.getId());
            }
        }
        logger.debug(sb.toString());
        // well, now sql is complete
        Connection con = null;
        PreparedStatement ps=null;
        ResultSet rs = null;
        try {
            con = getConnection();
            ps = con.prepareStatement(sb.toString());
            ps.setString(1,collectivite);
            logger.debug("1->"+collectivite);
            ps.setString(2,budget);
            logger.debug("2->"+budget);
            if(sp1!=null) {
                ps.setString(3,sp1);
                logger.debug("3->"+sp1);
            }
            if(sp2!=null) ps.setString(4,sp2);
            if(sp3!=null) ps.setString(5,sp3);
            rs = ps.executeQuery();
            while(rs.next()) ret.add(rs.getString(1));
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(rs!=null) try { rs.close(); } catch(Throwable t) {}
            if(ps!=null) try { ps.close(); } catch(Throwable t) {}
            if(con!=null) releaseConnection(con);
        }
        return ret;
    }
    public ArrayList<String> getDocumentListFromXPath(ElementModel em, String collectivite, String budget, String sp1, String sp2, String sp3, String xpath) throws DataConfigurationException, DataAccessException {
        ArrayList<String> ret = new ArrayList<String>();
        StringBuffer sb = new StringBuffer();
        EtatModel etatModel = em.getParent();
        TDocument pc = getPersistenceConfig(etatModel.getParent()).getLayer("mysql").getDocument(etatModel.getParent().getId());
        TEtat sec = pc.getEtat(etatModel.getId());
        sb.append("SELECT DOC_ID FROM ").append(sec.getMaintable().getBaseName()).append(" WHERE COLLECTIVITE=? AND BUDGET=? ");
        DocumentModel dm = etatModel.getParent();
        Vector<SpecialKeyModel> specialKeys = dm.getSpecialKeys();
        if(sp1!=null) {
            // search SPK1 XPath
            SpecialKeyModel skm = null;
            for(SpecialKeyModel sk:specialKeys) {
                if(sk.getPos()==1) skm = sk;
            }
            if(skm!=null) {
                String path = skm.getPath();
                TSqlIndex si = sec.getSqlIndexByPath(path);
                if(si.getTableId().equals(sec.getMaintable().getId())) {
                    // ok, it is the same table
                    sb.append("AND ").append(si.getColumn()).append("=? ");
                } else {
                    throw new DataConfigurationException(path+" is not indexed in "+sec.getMaintable().getBaseName());
                }
            } else {
                throw new DataConfigurationException("no special key in pos 1 defined for "+dm.getId());
            }
        }
        if(sp2!=null) {
            // search SPK1 XPath
            SpecialKeyModel skm = null;
            for(SpecialKeyModel sk:specialKeys) {
                if(sk.getPos()==2) skm = sk;
            }
            if(skm!=null) {
                String path = skm.getPath();
                TSqlIndex si = sec.getSqlIndexByPath(path);
                if(si.getTableId().equals(sec.getMaintable().getId())) {
                    // ok, it is the same table
                    sb.append("AND ").append(si.getColumn()).append("=? ");
                } else {
                    throw new DataConfigurationException(path+" is not indexed in "+sec.getMaintable().getBaseName());
                }
            } else {
                throw new DataConfigurationException("no special key in pos 2 defined for "+dm.getId());
            }
        }
        if(sp3!=null) {
            // search SPK3 XPath
            SpecialKeyModel skm = null;
            for(SpecialKeyModel sk:specialKeys) {
                if(sk.getPos()==3) skm = sk;
            }
            if(skm!=null) {
                String path = skm.getPath();
                TSqlIndex si = sec.getSqlIndexByPath(path);
                if(si.getTableId().equals(sec.getMaintable().getId())) {
                    // ok, it is the same table
                    sb.append("AND ").append(si.getColumn()).append("=? ");
                } else {
                    throw new DataConfigurationException(path+" is not indexed in "+sec.getMaintable().getBaseName());
                }
            } else {
                throw new DataConfigurationException("no special key in pos 3 defined for "+dm.getId());
            }
        }
        if(xpath!=null) {
            /*
             * on suppose que le xpath est de la forme /toto/titi/tata[tutu/@V=x1][@tete=x2]...
             * ou /toto/titi/tata est le chemin de l'element a chercher (pour simplifier encore)
             * on va chercher a isoler les chemins et les valeurs :
             * /toto/titi/tata/tutu/@V -> x1
             * /toto/titi/tata/@tete   -> x2
             * Si ces chemins sont indexes, alors on pourra ajouter des clauses where dans le sql.
             * Cette syntaxe devrait etre suffisante pour gÃ©rer la navigation dans le compte de gestion.
             * C'est du bricolage, mais je n'ai pas d'autre solution pour le moment
             */
            logger.debug("xpath is "+xpath);
            if(xpath.startsWith(em.getPath().getPath())) {
                final String shortPath = xpath.substring(em.getPath().getPath().length());
                if(!shortPath.startsWith("[")) throw new DataConfigurationException("XPath too complicated. This implementation can only deal with XPath formed by <path-to-element>[cond on sub-element][cond on sub element]...");
                // decoupage par groupe de [...]
                String[] conds = splitConditions(shortPath);
                for(String s:conds) {
                    logger.debug(s);
                    // pour le moment, on cherche les opérateurs <= >= = < >
                    s=s.substring(1,s.length()-1);
                    String op = null;
                    int pos = -1;
                    pos = s.indexOf("<=");
                    if(pos>=0) op="<=";
                    else {
                        pos= s.indexOf(">=");
                        if(pos>=0) op=">=";
                        else {
                            pos=s.indexOf('=');
                            if(pos>=0) op="=";
                            else {
                                pos=s.indexOf('<');
                                if(pos>=0) op="<";
                                else {
                                    pos=s.indexOf('>');
                                    if(pos>=0) op=">";
                                }
                            }
                        }
                    }
                    String left = s.substring(0,pos);
                    String right = s.substring(pos+op.length());
                    String indexedPath = em.getPath().getPath()+"/"+left;
                    logger.debug(indexedPath+" "+op+" "+right);
                    //
                    TSqlIndex si = sec.getSqlIndexByPath(indexedPath);
                    if(si!=null) {
                        if(si.getTableId().equals(sec.getMaintable().getId())) {
                            sb.append("AND ").append(si.getColumn()).append(op).append(sqlEscapeString(right)).append(" ");
                        } else {
                            throw new DataConfigurationException("index "+si.getId()+" is not on main table of "+sec.getId());
                        }
                    } else {
                        throw new DataConfigurationException("path not indexed: "+indexedPath);
                    }
                }
            } else {
                throw new DataConfigurationException("XPath too complicated. only XPath starting with element path are allowed. Here, XPath should start with "+em.getPath().getPath());
            }
        }
        logger.debug(sb.toString());
        // well, now sql is complete
        Connection con = null;
        PreparedStatement ps=null;
        ResultSet rs = null;
        try {
            con = getConnection();
            ps = con.prepareStatement(sb.toString());
            ps.setString(1,collectivite);
            logger.debug("1->"+collectivite);
            ps.setString(2,budget);
            logger.debug("2->"+budget);
            if(sp1!=null) {
                ps.setString(3,sp1);
                logger.debug("3->"+sp1);
            }
            if(sp2!=null) ps.setString(4,sp2);
            if(sp3!=null) ps.setString(5,sp3);
            rs = ps.executeQuery();
            while(rs.next()) ret.add(rs.getString(1));
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(rs!=null) try { rs.close(); } catch(Throwable t) {}
            if(ps!=null) try { ps.close(); } catch(Throwable t) {}
            if(con!=null) releaseConnection(con);
        }
        return ret;
    }
    private static String[] splitConditions(final String cond) throws DataConfigurationException {
        ArrayList<String> ret = new ArrayList<String>();
        String s = cond;
        while(s.length()>0 && s.charAt(0)=='[') {
            int end = findClosingParenthesis(s,0,"[]");
            ret.add(s.substring(0,end+1));
            s=s.substring(end+1);
        }
        String aRet[] = new String[ret.size()];
        ret.toArray(aRet);
        return aRet;
    }
    /**
     * This method searches for the closing parenthesis that matches the specified one.
     * <br><b>Warning&nbsp;:</b>&nbsp;it assumes that the character pointed out by <code>first</code>
     * <b>is actually</b> an openning parenthesis.
     * @param expr The expression to search through.
     * @param first Index of the openning parenthesis
     * @return Index of the closing parenthesis, if found.
     * @throws ParsingException if no closing parenthesis were found.
     */
    private static int findClosingParenthesis(String expr,int first,String delimitors) throws DataConfigurationException {
        final char OPENNING_DELIM = delimitors.charAt(0);
        final char CLOSING_DELIM = delimitors.charAt(1);
        int len = expr.length();
        int close = -1;
        int pCount = 1;
        for(int i=(first+1) ; i<len ; i++) {
            char c = expr.charAt(i);
            if(c==OPENNING_DELIM) pCount++;
            else if(c==CLOSING_DELIM) {
                pCount--;
            }
            if(pCount==0) {
                close = i;
                break;
            }
        }
        if(close==(-1)) {
            //            ParsingException pEx = new ParsingException(ParsingException.UNBALANCED_PARENTHESIS);
            //            fireParsingEvent(first,expr,pEx,null);
            throw new DataConfigurationException("unbalanced delimitor");
        }
        return close;
    }
    public SimpleDateFormat getDateFormatter() { return mySqlDateFormat; }
    
    /**
     * This methods returns the list of operators that can be applied to the given data type,
     * for a MySQL database.
     * @param datatype The type of data we want the operators for
     * @return an array of Pair object. The first part of the pair is the MySQL expression
     * that performs the operation, the second attribute of the pair is the textual
     * representation of the operator.
     */
    public Pair[] getAggregateOperators(String datatype) {
        ArrayList<Pair> result=new ArrayList<Pair>();
        logger.debug("Requested aggregate operators for data type "+datatype);
        if(datatype.equals(ChampModel.DATATYPE_INTEGER) || datatype.equals(ChampModel.DATATYPE_DECIMAL) || datatype.equals(ChampModel.DATATYPE_AMOUNT)) {
            result.add(new Pair("AVG","Moyenne"));
            result.add(new Pair("SUM","Somme"));
            result.add(new Pair("MIN","Minimum"));
            result.add(new Pair("MAX","Maximum"));
            result.add(new Pair("STDDEV_POP","Ecart type"));
            result.add(new Pair("VAR_POP", "Variance"));
        } else if(datatype.equals(ChampModel.DATATYPE_DATE)) {
            result.add(new Pair("SEC_TO_TIME(SUM(TIME_TO_SEC","Somme"));
            result.add(new Pair("SEC_TO_TIME(AVG(TIME_TO_SEC","Moyenne"));
        }
        
        Pair[] aRet = new Pair[result.size()];
        result.toArray(aRet);
        return aRet;
    }
    
    public String calculateAggregate(DocumentModel dm, EtatModel em, String columnXPath, String operator, ChampModel cm, DataResultSet rs)
        throws DataConfigurationException, DataAccessException {
        TPersistenceConfig pers = this.getPersistenceConfig(dm);
        TLayer layer = pers.getLayer(this.getLayerName());
        TDocument tdoc = layer.getDocument(dm.getId());
        TEtat tet = tdoc.getEtat(em.getId());
        // il faut convertir le XPath incluant le namespace en XPath comme il est Ã©crit en persistence.
        /*
         * Petit hack pour l'instant, en attendant de rÃ©soudre le problÃ¨me
         * YLA - 25/01/2007
         */
        //    	String xpath = columnXPath.replaceAll("n:", "");
        //logger.debug(xpath);
        // parfois, il y a des informations de position dans les xpath d'affichage de colonne, il faut les supprimer
        columnXPath = columnXPath.replaceAll("\\[[0-9]\\]","");
        logger.debug(columnXPath);
        TSqlIndex tsqlIndex = tet.getSqlIndexByPath(columnXPath);
        if(tsqlIndex==null) throw new DataConfigurationException("Pas d'index sur "+columnXPath);
        String columnName = tsqlIndex.getColumn();
        String tableName = tet.getIndexTable(tsqlIndex.getTableId()).getBaseName();
        
        if(rs!=null) {
            MySqlDataResultSet myRs = (MySqlDataResultSet)rs;
            return myRs.calculateAggregate(tableName+"."+columnName, tableName, operator, cm);
        } else
            return "";
    }
    
    private void checkParamTable() {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        try {
            con = getConnection();
            st = con.createStatement();
            rs = st.executeQuery("SELECT 1 FROM "+PARAM_TABLE_NAME);
            rs.close();
        } catch(SQLException sqlEx) {
            if(sqlEx.getErrorCode()==1146) {
                // Table does not exists
                try {
                    st.executeUpdate("CREATE TABLE "+PARAM_TABLE_NAME+" (PARAM_NAME VARCHAR(100) NOT NULL, PARAM_VALUE VARCHAR(100), UNIQUE INDEX PARAM_IX (PARAM_NAME)) TYPE MYISAM");
                } catch(Throwable t) {
                    logger.error("while creating "+PARAM_TABLE_NAME,t);
                }
            }
        } finally {
            if(st!=null) try { st.close(); } catch(Throwable t) {}
            if(con!=null) releaseConnection(con);
        }
    }
    
    /**
     * Returns the value of defined parameter.
     * Returns null if parameter is not defined.
     * @param paramName the Name of parameter to retreive value
     * @return The param value or null if parameter is not defined
     */
    public String getParameterValue(final String paramName) {
        Connection con = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String ret = null;
        try {
            con = getConnection();
            ps = con.prepareStatement("SELECT PARAM_VALUE FROM "+PARAM_TABLE_NAME+" WHERE PARAM_NAME=?");
            ps.setString(1,paramName);
            rs = ps.executeQuery();
            if(rs.next()) ret = rs.getString(1);
        } catch(SQLException sqlEx) {
            logger.error("in getParameterValue",sqlEx);
        } finally {
            if(rs!=null) try { rs.close(); } catch(Throwable t) {}
            if(ps!=null) try { ps.close(); } catch(Throwable t) {}
            if(con!=null) releaseConnection(con);
        }
        return ret;
    }
    public void setParameterValue(final String paramName, final String paramValue) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            String ret = getParameterValue(paramName);
            if(ret == null) {
                ps = con.prepareStatement("INSERT INTO "+PARAM_TABLE_NAME+" (PARAM_VALUE, PARAM_NAME) VALUES (?, ?)");
            } else {
                ps = con.prepareStatement("UPDATE "+PARAM_TABLE_NAME+" SET PARAM_VALUE=? WHERE PARAM_NAME=?");
            }
            ps.setString(1,paramValue);
            ps.setString(2,paramName);
            ps.executeUpdate();
        } catch(SQLException sqlEx) {
            logger.error("in getParameterValue",sqlEx);
        } finally {
            if(ps!=null) try { ps.close(); } catch(Throwable t) {}
            if(con!=null) releaseConnection(con);
        }
    }
    private void applyPatches() {
        if(getParameterValue(PayePatch32.PARAM_PAYE_PATCH_32)==null) {
            PayePatch32 patcher = new PayePatch32(this);
            patcher.run();
        }
        if(getParameterValue(VersementPatch32.PARAM_VERSEMENT_PATCH_32)==null) {
            VersementPatch32 patcher = new VersementPatch32(this);
            patcher.run();
        }
        if(getParameterValue(PesAllerPatch33.PARAM_PESV2_PATCH_33)==null) {
            PesAllerPatch33 patcher = new PesAllerPatch33(this);
            patcher.run();
        }
        if(getParameterValue(RapportPatch33.PARAM_RAPPORT_PATCH_33)==null) {
            RapportPatch33 patcher = new RapportPatch33(this);
            patcher.run();
        }
        // add here other patches
        killPatchProgress();
    }
    
    public void insertConfigExport(ConfigModel cm) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            ps = con.prepareStatement("INSERT INTO CONFIGS_EXPORT (DOC_ID,ETAT_ID,OWNER,DATE_CREA,CODE,LIBELLE,CONFIG) VALUES (?,?,?,CURRENT_DATE(),?,?,?)");
            int i=1;
            ps.setString(i++, cm.getDocID());
            ps.setString(i++, cm.getEtatID());
            ps.setString(i++, cm.getOwner());
            ps.setString(i++, cm.getCode());
            ps.setString(i++, cm.getLibelle());
            byte[] buffer = cm.getConfigLines();
            InputStream is = new ByteArrayInputStream(buffer);
            ps.setBinaryStream(i++, is, buffer.length);
            ps.executeUpdate();
        } catch (Exception e) {
            logger.error("in insertConfigExport ", e);
        } finally {
            if (ps!=null) try { ps.close(); } catch (Throwable t) {}
            if (con!=null) releaseConnection(con);
        }
    }
    
    public void updateConfigExport(ConfigModel cm) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            ps = con.prepareStatement("UPDATE CONFIGS_EXPORT set CODE=?, LIBELLE=?, CONFIG=? WHERE ID=? AND DOC_ID=? AND ETAT_ID=?");
            int i=1;
            ps.setString(i++, cm.getCode());
            ps.setString(i++, cm.getLibelle());
            byte[] buffer = cm.getConfigLines();
            InputStream is = new ByteArrayInputStream(buffer);
            ps.setBinaryStream(i++, is, buffer.length);
            ps.setString(i++, cm.getId());
            ps.setString(i++, cm.getDocID());
            ps.setString(i++, cm.getEtatID());
            ps.executeUpdate();
        } catch (Exception e) {
            logger.error("in updateConfigExport ", e);
        } finally {
            if (ps!=null) try { ps.close(); } catch (Throwable t) {}
            if (con!=null) releaseConnection(con);
        }
    }
    
    public Vector<ConfigModel> getListeConfigsExport(String docID, String etatID) {
        Connection con = null;
        PreparedStatement ps = null;
        Vector<ConfigModel> ret = new Vector<ConfigModel>();
        try {
            con = getConnection();
            ps = con.prepareStatement("SELECT ID,DOC_ID,ETAT_ID,OWNER,DATE_CREA,CODE,LIBELLE,CONFIG FROM CONFIGS_EXPORT WHERE DOC_ID=? AND ETAT_ID=? ORDER BY CODE");
            int i=1;
            ps.setString(i++, docID);
            ps.setString(i++, etatID);
            ResultSet rs = ps.executeQuery();
            while (rs.next()) {
                ret.add(new ConfigModel(rs));
            }
        } catch (Exception e) {
            logger.error("in getListeConfigsExport ", e);
        } finally {
            if (ps!=null) try { ps.close(); } catch (Throwable t) {}
            if (con!=null) releaseConnection(con);
        }
// DEBUG
//        XmlOutputter xo = new XmlOutputter(new OutputStreamWriter(System.out),"ISO-8859-1");
//        for(ConfigModel cm:ret) {
//            cm.marshall(xo);
//        }
//        try { xo.getWriter().flush(); } catch(Throwable t) {}
// END DEBUG
        return ret;
    }
    
    public void deleteConfigExport(ConfigModel cm) {
        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = getConnection();
            ps = con.prepareStatement("DELETE FROM CONFIGS_EXPORT WHERE ID=? AND DOC_ID=? AND ETAT_ID=?");
            int i=1;
            ps.setString(i++, cm.getId());
            ps.setString(i++, cm.getDocID());
            ps.setString(i++, cm.getEtatID());
            ps.executeUpdate();
        } catch (Exception e) {
            logger.error("in deleteConfigExport ", e);
        } finally {
            if (ps!=null) try { ps.close(); } catch (Throwable t) {}
            if (con!=null) releaseConnection(con);
        }
    }
    public ExportableData searchFichiers(DocumentModel dm, String coll, String budg) throws DataAccessException, DataConfigurationException {
        ExportableData dD = null;
        TDocument pc = getPersistenceConfig(dm).getLayer("mysql").getDocument(dm.getId());
        Connection con = null;
        boolean found = false;
        try {
            con = getConnection();
            StringBuilder sb = new StringBuilder();
            sb.append("SELECT COLLECTIVITE, COLLECTIVITE_LIB, BUDGET, BUDGET_LIB, INITIAL_DOC_NAME FROM ").append(pc.getListBcTable().getBaseName());
            if (coll != null) {
                sb.append(" WHERE COLLECTIVITE=? ");
            }
            if (budg != null) {
                if (coll == null) {
                    sb.append(" WHERE ");
                } else {
                    sb.append(" AND ");
                }
                sb.append(" BUDGET = ? ");
            }
            sb.append(" ORDER BY COLLECTIVITE_LIB, BUDGET_LIB, INITIAL_DOC_NAME");
            PreparedStatement ps = con.prepareStatement(sb.toString());
            int i=1;
            if (coll != null) ps.setString(i++, coll);
            if (budg != null) ps.setString(i++, budg);
            ResultSet rs = ps.executeQuery();
            String lastColl = "", lastBudg = "";
            
            while(rs.next()) {
                if (!found) {
                    dD = new ExportableData();
                    dD.setKey(dm.getId());
                    dD.setLevel(ExportableData.LVL_DOC);
                    dD.setLibelle(dm.getTitre());
                    dD.setSelected(false);
                }
                found = true;
                String curColl = rs.getString(1);
                String curBudg = rs.getString(3);
                
                ExportableData dF = new ExportableData();
                dF.setKey(dm.getId()+"|"+rs.getString(1)+"|"+rs.getString(3)+"|"+rs.getString(5));
                dF.setLevel(ExportableData.LVL_FIC);
                dF.setLibelle(rs.getString(5));
                dF.setSelected(false);
                
                ExportableData dC = null;
                if (!curColl.equals(lastColl)) {
                    dC = new ExportableData();
                    dC.setKey(dm.getId()+"|"+rs.getString(1));
                    dC.setLevel(ExportableData.LVL_COL);
                    dC.setLibelle(rs.getString(1)+" - "+rs.getString(2));
                    dC.setSelected(false);
                    dD.addChild(dC);
                    // Puisque la collectivitÃ© a changÃ©, il faut considÃ©rer le budget comme diffÃ©rent lui aussi
                    lastBudg="";
                } else {
                    dC = dD.getChild(dm.getId()+"|"+rs.getString(1));
                }
                ExportableData dB = null;
                if (!curBudg.equals(lastBudg)) {
                    dB = new ExportableData();
                    dB.setKey(dm.getId()+"|"+rs.getString(1)+"|"+rs.getString(3));
                    dB.setLevel(ExportableData.LVL_BUD);
                    dB.setLibelle(rs.getString(3)+" - "+rs.getString(4));
                    dB.setSelected(false);
                    dC.addChild(dB);
                } else {
                    dB = dC.getChild(dm.getId()+"|"+rs.getString(1)+"|"+rs.getString(3));
                }
                dB.addChild(dF);
                lastBudg = curBudg;
                lastColl = curColl;
            }
        } catch(SQLException sqlEx) {
            throw new DataAccessException(sqlEx);
        } finally {
            if(con!=null) releaseConnection(con);
        }
        return (found?dD:null);
    }
    public Vector<ExportableData> searchFichiers(DocumentsModel dms, String coll, String budg) throws DataAccessException, DataConfigurationException {
        Vector<ExportableData> ret = new Vector<ExportableData>();
        
        for(DocumentModel doc:dms.getDocuments()) {
            try {
                ExportableData tmp = searchFichiers(doc, coll, budg);
                if (tmp!=null)
                    ret.add(tmp);
            } catch (DataAccessException dae) {
                if (dae.getMessage().endsWith("doesn't exist"))
                    ;
                else
                    throw dae;
            }
        }
        
        return ret;
    }
    
    protected String buildSelectToExport(int whatToExport, ElementModel elementModel, Pair coll, Pair budg, String fic, Pair sk1, Pair sk2) throws DataConfigurationException {
        EtatModel etatModel = elementModel.getParent();
        DocumentModel documentModel = etatModel.getParent();
        
        TDocument pc = getPersistenceConfig(documentModel).getLayer("mysql").getDocument(documentModel.getId());
        TEtat sec = pc.getEtat(etatModel.getId());
        TTable mainTable = sec.getMaintable();
        TTable docTable = sec.getDocumentTable();
        
        StringBuffer sb = new StringBuffer();
        
        switch (whatToExport) {
        case EXPORT_BUD : {
            sb.append("SELECT ").append(docTable.getBaseName()).append(".DOC_ID FROM ").append(docTable.getBaseName());
            sb.append(" WHERE ").append(docTable.getBaseName()).append(".COLLECTIVITE='").append(StringEscapeUtils.escapeSql(coll.key)).append("'");
            sb.append(" AND ").append(docTable.getBaseName()).append(".BUDGET='").append(StringEscapeUtils.escapeSql(budg.key)).append("'");
            sb.append(" ");
            break;
        }
        case EXPORT_SK1 : {
            sb.append("SELECT ").append(docTable.getBaseName()).append(".DOC_ID FROM ").append(docTable.getBaseName());
            sb.append(" LEFT OUTER JOIN ").append(pc.getSpecialKeyTable().getBaseName()).append(" ON ");
            sb.append(docTable.getBaseName()).append(".INITIAL_DOC_NAME=").append(pc.getSpecialKeyTable().getBaseName()).append(".INITIAL_DOC_NAME ");
            sb.append(" AND ").append(docTable.getBaseName()).append(".COLLECTIVITE=").append(pc.getSpecialKeyTable().getBaseName()).append(".COLLECTIVITE ");
            sb.append(" AND ").append(docTable.getBaseName()).append(".BUDGET=").append(pc.getSpecialKeyTable().getBaseName()).append(".BUDGET ");
            sb.append(" WHERE ").append(docTable.getBaseName()).append(".COLLECTIVITE='").append(StringEscapeUtils.escapeSql(coll.key)).append("'");
            sb.append(" AND ").append(docTable.getBaseName()).append(".BUDGET='").append(StringEscapeUtils.escapeSql(budg.key)).append("'");
            sb.append(" AND ").append(pc.getSpecialKeyTable().getBaseName()).append(".KEY1='").append(StringEscapeUtils.escapeSql(sk1.key)).append("'");
            sb.append(" ");
            break;
        }
        case EXPORT_SK2 : {
            sb.append("SELECT ").append(docTable.getBaseName()).append(".DOC_ID FROM ").append(docTable.getBaseName());
            sb.append(" LEFT OUTER JOIN ").append(pc.getSpecialKeyTable().getBaseName()).append(" ON ");
            sb.append(docTable.getBaseName()).append(".INITIAL_DOC_NAME=").append(pc.getSpecialKeyTable().getBaseName()).append(".INITIAL_DOC_NAME ");
            sb.append(" AND ").append(docTable.getBaseName()).append(".COLLECTIVITE=").append(pc.getSpecialKeyTable().getBaseName()).append(".COLLECTIVITE ");
            sb.append(" AND ").append(docTable.getBaseName()).append(".BUDGET=").append(pc.getSpecialKeyTable().getBaseName()).append(".BUDGET ");
            sb.append(" WHERE ").append(docTable.getBaseName()).append(".COLLECTIVITE='").append(StringEscapeUtils.escapeSql(coll.key)).append("'");
            sb.append(" AND ").append(docTable.getBaseName()).append(".BUDGET='").append(StringEscapeUtils.escapeSql(budg.key)).append("'");
            sb.append(" AND ").append(pc.getSpecialKeyTable().getBaseName()).append(".KEY1='").append(StringEscapeUtils.escapeSql(sk1.key)).append("'");
            sb.append(" AND ").append(pc.getSpecialKeyTable().getBaseName()).append(".KEY2='").append(StringEscapeUtils.escapeSql(sk2.key)).append("'");
            sb.append(" ");
            break;
        }
        case EXPORT_FIC : {
            sb.append("SELECT ").append(docTable.getBaseName()).append(".DOC_ID FROM ").append(docTable.getBaseName());
            sb.append(" WHERE ").append(docTable.getBaseName()).append(".COLLECTIVITE='").append(StringEscapeUtils.escapeSql(coll.key)).append("'");
            sb.append(" AND ").append(docTable.getBaseName()).append(".BUDGET='").append(StringEscapeUtils.escapeSql(budg.key)).append("'");
            sb.append(" AND ").append(docTable.getBaseName()).append(".INITIAL_DOC_NAME='").append(StringEscapeUtils.escapeSql(fic)).append("'");
            sb.append(" ");
            break;
        }
        }
        
        return sb.toString();
    }
    
    public DataResultSet getDataResultSetForExport(int whatToExport, ElementModel em, Pair coll, Pair budg, String fic, Pair SK1, Pair SK2) throws DataConfigurationException, SQLException {
        String sql = buildSelectToExport(whatToExport, em, coll, budg, fic, SK1, SK2);
//logger.debug("SQL : "+sql);
        ListeResultatModel lrm = em.getListeResultat();
        
        MySqlDataResultSet drs = new MySqlDataResultSet(em, coll, budg, em.getPath().getPath(), sql, lrm, (MySqlDataLayer)DataLayerManager.getImplementation());
        
        return drs;
    }

    // LM 07/02/2008 Methodes pour implementation pseudo hashtable stockees dans la BD
    // 
//    private static Connection HSWcon;

    private class HSWStorageHandle {
        Class[] classes; // liste des classes des objets stockes
        Hashtable<String, PreparedStatement> requetes ; // les requetes
        Connection con;


        HSWStorageHandle(Hashtable<String, PreparedStatement> preqs, Class[]  pclasses,Connection con) {
            classes = pclasses;
            requetes = preqs;
            this.con=con;
        }
    };


    /**
     *  initTempStorage 
     *  
     *  @param storageId = nom d etable temporaire
     *  @return  Object = un type opaque
     *  (en fait  un  HSWStorageHandle )
     *  
     */
    public  Object  HSWinitTempStorage( String storageId, String keyId, int maxKeyLength, Vector<java.lang.reflect.Field> fieldList ) {
        // Parcours fieldList pour en deduire les types et les longiuers des champs 
        Class[] classes = null;
        int[] sizes = null;
        String names[]= null;

        if( fieldList != null) {
            classes = new Class[fieldList.size()] ;
            sizes =   new int[fieldList.size()];
            names =   new String[fieldList.size()];

            for (int i=0; i<fieldList.size(); i++) {
                Field fld  = fieldList.get(i);
                String typeName = fld.getType().toString();
                LongueurMax anno ;

                if(  typeName.equals( "class java.lang.String" )) {
                    Integer longueurMax = 255; // surdimensionner par defaut
                    // On va chercher l'annotation pour connaitre la longueur max
                    anno = fld.getAnnotation(LongueurMax.class);
                    if(anno != null) {
                        longueurMax  = anno.value();
                    } else {
                        throw new RuntimeException("The field "+fld.getName()+" has no annotation @LongueurMax(value = xxx)");
                    }

                    classes[i] = String.class;
                    sizes[i] = longueurMax;

                } else if(  typeName.equals("int")
                            || typeName.equals("class java.lang.Integer" )) {
                    Integer longueurMax = 10; // surdimensionner par defaut
                    // On va chercher l'annotation pour connaitre la longueur max
                    anno = fld.getAnnotation(LongueurMax.class);
                    if(anno != null) {
                        longueurMax  = anno.value();
                    } 
                    classes[i] = Integer.class;
                    sizes[i] = longueurMax;
                }else if(  typeName.equals("float")
                           || typeName.equals("class java.lang.Float" )) {
                    Integer longueurMax = 10; // surdimensionner par defaut
                    // On va chercher l'annotation pour connaitre la longueur max
                    anno = fld.getAnnotation(LongueurMax.class);
                    if(anno != null) {
                        longueurMax  = anno.value();
                    }  
                    classes[i] = Float.class;
                    sizes[i] = longueurMax;
                }else if(  typeName.equals("double")
                           || typeName.equals("class java.lang.Double" )) {
                    Integer longueurMax = 10; // surdimensionner par defaut
                    // On va chercher l'annotation pour connaitre la longueur max
                    anno = fld.getAnnotation(LongueurMax.class);
                    if(anno != null) {
                        longueurMax  = anno.value();
                    } 
                    classes[i] = Float.class;
                    sizes[i] = longueurMax;
                }else if(  typeName.equals("class java.util.Date" )) {

                    classes[i] =java.util.Date.class;
                    sizes[i] =  999; // irrelevant;
                }
                names[i] = fld.getName();
            }
        }
        return  HSWinitTempStorage(  storageId,  keyId,  maxKeyLength, classes,  sizes, names );

    }

    /**
     *  initTempStorage 
     *  
     *  @param storageId = nom d etable temporaire
     *  @return  Object = un type opaque
     *  (en fait  un HSWStorageHandle)
     *  
     */
    public  Object  HSWinitTempStorage( String storageId, String keyId, int maxKeyLength, Class[] classes, int[] sizes , String names[]) {

        Connection HSWcon = null;
        Hashtable<String, PreparedStatement>  reqs = new Hashtable<String, PreparedStatement>();

        StringBuffer sqlCreateBuffer = new StringBuffer();
        StringBuffer sqlInsertBuffer = new StringBuffer();
        StringBuffer sqlInsertValuesBuffer= new StringBuffer();
        StringBuffer sqlSelectBuffer = new StringBuffer();
        StringBuffer sqlExistsBuffer = new StringBuffer();
        StringBuffer sqlCountBuffer  = new StringBuffer();
        StringBuffer sqlDeleteBuffer = new StringBuffer();
        StringBuffer sqlDropBuffer   = new StringBuffer();

        String virgule = ",";
        sqlCreateBuffer.append(  "CREATE TEMPORARY TABLE " + storageId + "(\n" );
        sqlInsertBuffer.append(  "INSERT INTO " + storageId + "(\n" );
        sqlInsertValuesBuffer.append(  "VALUES(\n" );
        sqlExistsBuffer.append(  "SELECT COUNT(*) FROM " + storageId + "\n" );
        sqlSelectBuffer.append(  "SELECT \n" );
        sqlDeleteBuffer.append(   "DELETE FROM " + storageId + "\n" ); 
        sqlDropBuffer.append(   "DROP TABLE " + storageId + "\n" ); 
        sqlCountBuffer.append(  "SELECT COUNT(*) FROM " + storageId + "\n" );

        if(classes != null) {
            // la cle 
            sqlCreateBuffer.append(  keyId + " VARCHAR(" + maxKeyLength+ ") UNIQUE");
            sqlInsertBuffer.append(  keyId );
            sqlInsertValuesBuffer.append( "?");
            sqlSelectBuffer.append(  keyId );
            sqlExistsBuffer.append( "WHERE " + keyId + " = ? " ); 
            sqlDeleteBuffer.append( "WHERE " + keyId + " = ? " ); 

            for (int i=0; i< classes.length; i++) {
                Class cls  = classes[i];
                Integer longueurMax = sizes[i];
                sqlCreateBuffer.append(virgule);
                sqlCreateBuffer.append("\n");
                sqlCreateBuffer.append(names[i] + " ");

                sqlInsertBuffer.append(virgule);
                sqlInsertBuffer.append("\n");
                sqlInsertBuffer.append(names[i] + " ");
                sqlInsertValuesBuffer.append(virgule);
                sqlInsertValuesBuffer.append("\n");
                sqlInsertValuesBuffer.append("?");


                sqlSelectBuffer.append(virgule);
                sqlSelectBuffer.append("\n");
                sqlSelectBuffer.append(names[i] + " ");

                String typeName = cls.toString();
                if(  typeName.equals( "class java.lang.String" )) {
                    sqlCreateBuffer.append("VARCHAR(" + longueurMax.toString()+ ")" );

                } else if(  typeName.equals("int")
                            || typeName.equals("class java.lang.Integer" )) {
                    sqlCreateBuffer.append("INTEGER(" + longueurMax.toString()+ ")" );
                } else if(  typeName.equals("float")
                           || typeName.equals("class java.lang.Float" )) {
                    sqlCreateBuffer.append("FLOAT(" + longueurMax.toString()+ ")" );
                } else if(  typeName.equals("double")
                           || typeName.equals("class java.lang.Double" )) {
                    sqlCreateBuffer.append("DOUBLE(" + longueurMax.toString()+ ")" );
                } else if(  typeName.equals("class java.util.Date" )) {
                    sqlCreateBuffer.append("DATE" );
                }
            }
            sqlCreateBuffer.append("\n)\n");
            sqlInsertBuffer.append("\n)\n");
            sqlInsertValuesBuffer.append("\n)\n");

            sqlSelectBuffer.append(" FROM " + storageId + " WHERE  " + keyId + " = ? " );



            try {
                HSWcon = getConnection();


                reqs.put("Create", HSWcon.prepareStatement(sqlCreateBuffer.toString()));
                reqs.put("Insert", HSWcon.prepareStatement( sqlInsertBuffer.toString() 
                                                            +  sqlInsertValuesBuffer.toString()));
                reqs.put("Exists", HSWcon.prepareStatement(sqlExistsBuffer.toString()));          
                reqs.put("Select", HSWcon.prepareStatement( sqlSelectBuffer.toString())); 
                reqs.put("Delete",  HSWcon.prepareStatement( sqlDeleteBuffer.toString())); 
                reqs.put("Drop",   HSWcon.prepareStatement( sqlDropBuffer.toString())); 
                reqs.put("Count",  HSWcon.prepareStatement( sqlCountBuffer.toString())); 
            } catch( SQLException se ) {
                logger.error( "init : SQL Exception:",se ) ;
            } catch( Exception e ) {
                logger.error("init: other exception", e ) ;
            }
        }

        return new HSWStorageHandle( reqs, classes, HSWcon) ;
    }



    public void HSWdoCreateStorage(Object pStorageHandle ){
        if( pStorageHandle != null && pStorageHandle instanceof  HSWStorageHandle )  {
            HSWStorageHandle storageHandle = (HSWStorageHandle)pStorageHandle;
            Hashtable<String,PreparedStatement> ht = storageHandle.requetes;


            try {
                PreparedStatement ps  = ht.get("Create");// HSWcon.prepareStatement(sqlcreate);
                ps.executeUpdate();
            } catch( SQLException se ) {
                logger.error( "Create SQL Exception:", se ) ;
            } catch( Exception e ) {
                logger.error( e ) ;
            }
        }
    }


    public void HSWdoInsert(Object pStorageHandle, String key,  Object[] values ){
        if( pStorageHandle != null && pStorageHandle instanceof  HSWStorageHandle )  {
            HSWStorageHandle storageHandle = (HSWStorageHandle)pStorageHandle;
            Hashtable<String,PreparedStatement> ht = storageHandle.requetes;
            try {
                PreparedStatement ps =  ht.get("Insert"); //HSWcon.prepareStatement(sqlInsert);
                ps.setString(1, key);
                for( int i = 0; i < values.length;++i) {
                    int j = i + 2;
                    if ( values[i] instanceof String ) {
                        ps.setString(j, (String)values[i]);
                        //   
                    } else if ( values[i] instanceof java.lang.Integer ) {
                        ps.setInt(j, (Integer)values[i]);
                        // 
                    } else if ( values[i] instanceof java.lang.Float ) {
                        ps.setFloat(j, (Float)values[i]);
                        // S
                    } else if ( values[i] instanceof java.util.Date ) {
                        java.sql.Date sqlDate = new java.sql.Date(((java.util.Date)values[i]).getTime());
                        ps.setDate(j, sqlDate);
                    }
                }
                ps.executeUpdate();
            } catch( SQLException se ) {
                logger.error( "doInsert SQL Exception:", se ) ;
            } catch( Exception e ) {
                logger.error( e ) ;
            }
        }
    }

    public  boolean  HSWdoExists(Object pStorageHandle, String key){
        boolean  ret = false;
        if( pStorageHandle != null && pStorageHandle instanceof  HSWStorageHandle )  {
            HSWStorageHandle storageHandle = (HSWStorageHandle)pStorageHandle;
            try {
                Hashtable<String,PreparedStatement> ht = storageHandle.requetes;
                PreparedStatement ps = ht.get("Exists");//HSWcon.prepareStatement(sqlexists);
                ps.setString(1, key);
                ResultSet rs = ps.executeQuery();
                if(rs.next()) {
                    int count = rs.getInt( 1 );
                    if(count > 0) 
                        ret = true;
                }

            } catch( SQLException se ) {
                logger.error( "doExists SQL Exception:", se );
            } catch( Exception e ) {
                logger.error( e ) ;
            }
        }
        return ret;
    }



    public Object[]  HSWdoSelect(Object pStorageHandle, String key , Object[] inoutObj){
        Object[] ret = null;

        if( pStorageHandle != null && pStorageHandle instanceof  HSWStorageHandle )  {
            HSWStorageHandle storageHandle = (HSWStorageHandle)pStorageHandle;
            try {
                Hashtable<String,PreparedStatement> ht = storageHandle.requetes;
                Class[] classes = storageHandle.classes;
                PreparedStatement ps = ht.get("Select"); //HSWcon.prepareStatement(sqlselect);
                ps.setString(1, key);
                ResultSet rs = ps.executeQuery();
                if(rs.next()) {
                    for (int i=0; i<classes.length; i++) {
                        int j = i + 2;
                        Class cls  = classes[i];
                        String typeName = cls.toString();
                        if(  typeName.equals( "class java.lang.String" )) {
                            inoutObj[i] =  rs.getString( j);

                        }  else if(  typeName.equals("int")
                                     || typeName.equals("class java.lang.Integer" )) {
                            inoutObj[i]  =  rs.getInt( j);
                        } else if(  typeName.equals("float")
                                   || typeName.equals("class java.lang.Float" )) {
                            inoutObj[i]  =  rs.getFloat( j);
                        } else if(  typeName.equals("double")
                                   || typeName.equals("class java.lang.Double" )) {
                            inoutObj[i]  =  rs.getDouble( j);
                        } else if(   typeName.equals("class java.util.Date" )) {

                            java.sql.Date sqlDate =  rs.getDate( j);
                            inoutObj[i]  = sqlDate;
                        }
                    }
                    ret =   inoutObj;
                }
            } catch( SQLException se ) {
                logger.error( "doSelect SQL Exception:", se ) ;
            } catch( Exception e ) {
                logger.error( e ) ;
            }
        }
        return ret;
    }

    public  boolean  HSWdoDelete(Object pStorageHandle, String key ){
        boolean ret =false;
        if( pStorageHandle != null && pStorageHandle instanceof  HSWStorageHandle )  {
            HSWStorageHandle storageHandle = (HSWStorageHandle)pStorageHandle;
            try {
                Hashtable<String,PreparedStatement> ht = storageHandle.requetes;
                PreparedStatement ps = ht.get("Delete");//HSWcon.prepareStatement(sqldelete);
                ps.setString(1, key);
                ps.executeUpdate();
                ret = true;
            } catch( SQLException se )  {
                logger.error( "doDelete SQL Exception:", se ) ;
            } catch( Exception e ) {
                logger.error( e ) ;
            }
        }
        return ret;
    }
    public  int  HSWdoCount(Object pStorageHandle){
        int  ret = -1;
        if( pStorageHandle != null && pStorageHandle instanceof  HSWStorageHandle )  {
            HSWStorageHandle storageHandle = (HSWStorageHandle)pStorageHandle;
            try {
                Hashtable<String,PreparedStatement> ht = storageHandle.requetes;
                PreparedStatement ps = ht.get("Count");//HSWcon.prepareStatement(sqlcount);
                ResultSet rs = ps.executeQuery();
                if(rs.next()) {
                    ret = rs.getInt( 1 );
                }
            } catch( SQLException se ) {
                logger.error( "doCount SQL Exception:", se ) ;
            } catch( Exception e ) {
                logger.error( e ) ;
            }
        }
        return ret;
    }

    public  boolean  HSWdoDrop(Object pStorageHandle){
        boolean  ret = false;
        if( pStorageHandle != null && pStorageHandle instanceof  HSWStorageHandle )  {
            HSWStorageHandle storageHandle = (HSWStorageHandle)pStorageHandle;
            Connection HSWcon = null;
            try {
                Hashtable<String,PreparedStatement> ht = storageHandle.requetes;
                PreparedStatement ps =  ht.get("Drop"); //HSWcon.prepareStatement(sqldrop);
                HSWcon = storageHandle.con;
                ps.executeUpdate();
            } catch( SQLException se ) {
                logger.error( "doDrop : SQL Exception:", se ) ;
            } catch( Exception e ) {
                logger.error( e ) ;
            } finally {
                if(HSWcon!=null) {
                    releaseConnection(HSWcon);
                    storageHandle.con=null;
                }
            }
        }
        return ret;
    }

    public boolean isLocalComputerServer() {
        String driver = props.getProperty("driver.class");
        // mysql embedded
        if("com.mysql.embedded.jdbc.MySqlEmbeddedDriver".equals(driver)) return true;
        else if("com.mysql.jdbc.Driver".equals(driver)) {
            String url = props.getProperty("database.url");
            // url is jdbc:mysql://localhost/xemelios or 
            url = url.substring(13);
            int pipePos = url.indexOf(':');
            int slashPos = url.indexOf('/');
            if(pipePos<0) pipePos = 10000;
            if(slashPos<0) slashPos = 10000;
            int pos = Math.min(pipePos,slashPos);
            if(pos>url.length()) pos=url.length();
            String server = url.substring(0, pos);
            try {
                InetAddress serverAddress = InetAddress.getByName(server);
                InetAddress localAddress = InetAddress.getLocalHost();
                return localAddress.equals(serverAddress);
            } catch(UnknownHostException uhEx) {
                logger.error(null,uhEx);
            }
        } else {
            // unknown driver
            throw new RuntimeException("no support is provided for this driver. Please see MySqlDataLayer.isLocalComputerServer() and provide adapted code.");
        }
        return false;
    }
    public static String sqlEscapeString(String data) {
        StringBuilder ret = new StringBuilder();
        if(data.startsWith("'") && data.endsWith("'")) {
            ret.append("'");
            ret.append(sqlEscapeString(data.substring(1, data.length()-1)));
            ret.append("'");
        } else {
            ret.append(data.replaceAll("'","''"));
        }
        return ret.toString();
    }
}
