/*
 * $Header: /home/cvspublic/jakarta-slide/src/stores/org/apache/slide/store/impl/rdbms/StandardRDBMSAdapter.java,v 1.45 2005/05/19 13:01:32 unico Exp $
 * $Revision: 1.45 $
 * $Date: 2005/05/19 13:01:32 $
 *
 * ====================================================================
 *
 * Copyright 1999-2005 The Apache Software Foundation 
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 *
 */

package org.apache.slide.store.impl.rdbms;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Constructor;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Date;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Set;
import java.util.Vector;

import org.apache.slide.common.Service;
import org.apache.slide.common.ServiceAccessException;
import org.apache.slide.common.ServiceParameterErrorException;
import org.apache.slide.common.ServiceParameterMissingException;
import org.apache.slide.common.Uri;
import org.apache.slide.content.NodeProperty;
import org.apache.slide.content.NodeRevisionContent;
import org.apache.slide.content.NodeRevisionDescriptor;
import org.apache.slide.content.NodeRevisionDescriptors;
import org.apache.slide.content.NodeRevisionNumber;
import org.apache.slide.content.RevisionAlreadyExistException;
import org.apache.slide.content.RevisionDescriptorNotFoundException;
import org.apache.slide.content.RevisionNotFoundException;
import org.apache.slide.lock.LockTokenNotFoundException;
import org.apache.slide.lock.NodeLock;
import org.apache.slide.security.NodePermission;
import org.apache.slide.store.ConcurrencyConflictError;
import org.apache.slide.structure.LinkNode;
import org.apache.slide.structure.ObjectAlreadyExistsException;
import org.apache.slide.structure.ObjectNode;
import org.apache.slide.structure.ObjectNotFoundException;
import org.apache.slide.util.logger.Logger;

/**
 * J2EENodeStore Implementation based on the modified NewSlide Schema.
 * This is combined store of DescriptorStore and ContentStore
 * This combined store is necessary to make every operation behave in
 * one transaction.
 * 
 * @version $Revision: 1.45 $
 */

public class StandardRDBMSAdapter extends AbstractRDBMSAdapter {

    protected static final String LOG_CHANNEL = StandardRDBMSAdapter.class.getName();

    protected boolean bcompress;

    public StandardRDBMSAdapter(Service service, Logger logger) {
        super(service, logger);
        bcompress = false;
    }

    public void setParameters(Hashtable parameters)
        throws ServiceParameterErrorException, ServiceParameterMissingException {
        try {
            bcompress = "true".equalsIgnoreCase((String) parameters.get("compress"));
            if (bcompress)
                getLogger().log("Switching on content compression", LOG_CHANNEL, Logger.INFO);
            super.setParameters(parameters);
        } catch (Exception e) {
            getLogger().log(e.toString(), LOG_CHANNEL, Logger.ERROR);
        }
    }

    // Object

    public void createObject(Connection connection, Uri uri, ObjectNode object)
        throws ObjectAlreadyExistsException, ServiceAccessException {
        if (!storeObject(connection, uri, object, true))
            throw new ObjectAlreadyExistsException(uri.toString());
    }

    public void storeObject(Connection connection, Uri uri, ObjectNode object)
        throws ObjectNotFoundException, ServiceAccessException {
        if (!storeObject(connection, uri, object, false))
            throw new ObjectNotFoundException(uri.toString());
    }

    protected boolean storeObject(Connection connection, Uri uri, ObjectNode object, boolean create)
        throws ServiceAccessException {
        String className = object.getClass().getName();
        long uriid;
		Set updatedBindings = object.getUpdatedBindings();
        try {
            PreparedStatement statement = null;
            ResultSet res = null;
            try {
                uriid = assureUriId(connection, uri.toString());
                statement =
                    connection.prepareStatement(
                        "select 1 from OBJECT o, URI u where o.URI_ID=u.URI_ID and u.URI_STRING=?");
                statement.setString(1, uri.toString());
                res = statement.executeQuery();
                if (res.next()) {
                    if (create)
                        return false;
                } else {
                    if (!create)
                        return false;
                }
            } finally {
                close(statement, res);
            }

            if (create) {
                // create object in database
                try {
                    statement = connection.prepareStatement("insert into OBJECT (URI_ID,CLASS_NAME) values (?,?)");
                    statement.setLong(1, uriid);
                    statement.setString(2, className);
                    statement.executeUpdate();
                } finally {
                    close(statement);
                }
            }

            // update binding...

            try {
                clearBinding(connection, uri, updatedBindings);
            } catch (ObjectNotFoundException e1) {
                // clear only if it existed
            }
            
            Enumeration bindings = object.enumerateBindings();
            while (bindings.hasMoreElements()) {
                ObjectNode.Binding binding = (ObjectNode.Binding) bindings.nextElement();
              //Only insert the binding if it has been updated
			  if(updatedBindings.contains(binding.getUuri())){
                try {
                    statement =
                        connection.prepareStatement(
                            "insert into BINDING (URI_ID, NAME, CHILD_UURI_ID) select ?, ?, URI_ID from URI where URI_STRING = ?");
                    statement.setLong(1, uriid);
                    statement.setString(2, binding.getName());
                    statement.setString(3, binding.getUuri());
                    statement.executeUpdate();
                } finally {
                    close(statement);
                }
            }
            }

            Enumeration parentBindings = object.enumerateParentBindings();
            while (parentBindings.hasMoreElements()) {
                ObjectNode.ParentBinding parentBinding = (ObjectNode.ParentBinding) parentBindings.nextElement();
                try {
                    statement =
                        connection.prepareStatement(
                            "insert into PARENT_BINDING (URI_ID, NAME, PARENT_UURI_ID) select ?, ?, URI_ID from URI where URI_STRING = ?");
                    statement.setLong(1, uriid);
                    statement.setString(2, parentBinding.getName());
                    statement.setString(3, parentBinding.getUuri());
                    statement.executeUpdate();
                } finally {
                    close(statement);
                }
            }

            if (object instanceof LinkNode) {
                // update link target
                try {
                    statement = connection.prepareStatement("delete from LINKS where URI_ID = ?");
                    statement.setLong(1, uriid);
                    statement.executeUpdate();
                } finally {
                    close(statement);
                }
                try {
                    statement =
                        connection.prepareStatement(
                            "insert into LINKS (URI_ID, LINK_TO_ID) select ?, u.URI_ID from URI u where u.URI_STRING = ?");
                    statement.setLong(1, uriid);
                    statement.setString(2, ((LinkNode) object).getLinkedUri());
                    statement.executeUpdate();
                } finally {
                    close(statement);
                }
            }
        } catch (SQLException e) {
            throw createException(e, uri.toString());
        }
        object.resetUpdatedBindings();
        return true;
    }

    public void removeObject(Connection connection, Uri uri, ObjectNode object)
        throws ServiceAccessException, ObjectNotFoundException {
        PreparedStatement statement = null;
        try {

            clearBinding(connection, uri);

            // delete links
            try {
                statement =
                    connection.prepareStatement(
                        deleteStatement("LINKS","l",", URI u where l.URI_ID = u.URI_ID and u.URI_STRING = ?"));
                statement.setString(1, uri.toString());
                statement.executeUpdate();
            } finally {
                close(statement);
            }
            // delete version history
            // FIXME: Is this true??? Should the version history be removed if the object is removed???
            try {
                statement =
                    connection.prepareStatement(
                        deleteStatement("VERSION_HISTORY","vh",", URI u where vh.URI_ID = u.URI_ID and u.URI_STRING = ?"));
                statement.setString(1, uri.toString());
                statement.executeUpdate();
            } finally {
                close(statement);
            }
            // delete version
            try {
                statement =
                    connection.prepareStatement(
                        deleteStatement("VERSION","v",", URI u where v.URI_ID = u.URI_ID and u.URI_STRING = ?"));
                statement.setString(1, uri.toString());
                statement.executeUpdate();
            } finally {
                close(statement);
            }
            // delete the object itself
            try {
                statement =
                    connection.prepareStatement(
                        deleteStatement("OBJECT","o",", URI u where o.URI_ID = u.URI_ID and u.URI_STRING = ?"));
                statement.setString(1, uri.toString());
                statement.executeUpdate();
            } finally {
                close(statement);
            }
            // finally delete the uri
            try {
                statement = connection.prepareStatement("delete from URI where URI_STRING = ?");
                statement.setString(1, uri.toString());
                statement.executeUpdate();
            } finally {
                close(statement);
            }
        } catch (SQLException e) {
            throw createException(e, uri.toString());

        }
        object.resetUpdatedBindings();
    }

    public ObjectNode retrieveObject(Connection connection, Uri uri)
        throws ServiceAccessException, ObjectNotFoundException {
        ObjectNode result = null;
        try {
            PreparedStatement statement = null;
            ResultSet res = null;
            String className;
            Vector children = new Vector();
            Vector parents = new Vector();
            Vector links = new Vector();
            try {
                statement =
                    connection.prepareStatement(
                        "select o.CLASS_NAME from OBJECT o, URI u where o.URI_ID = u.URI_ID and u.URI_STRING = ?");
                statement.setString(1, uri.toString());
                res = statement.executeQuery();
                if (res.next()) {
                    className = res.getString(1);
                } else {
                    throw new ObjectNotFoundException(uri);
                }
            } finally {
                close(statement, res);
            }

            try {
                statement =
                    connection.prepareStatement(
                        "SELECT c.NAME, cu.URI_STRING FROM URI u, URI cu, BINDING c WHERE cu.URI_ID = c.CHILD_UURI_ID AND c.URI_ID = u.URI_ID and u.URI_STRING = ?");
                statement.setString(1, uri.toString());
                res = statement.executeQuery();
                while (res.next()) {
                    children.addElement(new ObjectNode.Binding(res.getString(1), res.getString(2)));
                }
            } finally {
                close(statement, res);
            }

            try {
                statement =
                    connection.prepareStatement(
                        "SELECT c.NAME, cu.URI_STRING FROM URI u, URI cu, PARENT_BINDING c WHERE cu.URI_ID = c.PARENT_UURI_ID AND c.URI_ID = u.URI_ID and u.URI_STRING = ?");
                statement.setString(1, uri.toString());
                res = statement.executeQuery();
                while (res.next()) {
                    parents.addElement(new ObjectNode.ParentBinding(res.getString(1), res.getString(2)));
                }
            } finally {
                close(statement, res);
            }

            try {
                statement =
                    connection.prepareStatement(
                        "SELECT lu.URI_STRING FROM URI u, URI lu, LINKS l WHERE lu.URI_ID = l.URI_ID AND l.LINK_TO_ID = u.URI_ID and u.URI_STRING = ?");
                statement.setString(1, uri.toString());
                res = statement.executeQuery();
                while (res.next()) {
                    links.addElement(res.getString(1));
                }
            } finally {
                close(statement, res);
            }
            if (className.equals(LinkNode.class.getName())) {
                try {
                    statement =
                        connection.prepareStatement(
                            "SELECT lu.URI_STRING FROM URI u, URI lu, LINKS l WHERE lu.URI_ID = l.LINK_TO_ID AND l.URI_ID = u.URI_ID and u.URI_STRING = ?");
                    statement.setString(1, uri.toString());
                    res = statement.executeQuery();
                    if (res.next()) {
                        String linkTarget = res.getString(1);
                        result = new LinkNode(uri.toString(), children, links, linkTarget);
                    } else {
                        result = new LinkNode(uri.toString(), children, links);
                    }
                } finally {
                    close(statement, res);
                }
            } else {
                try {
                    Class objclass = Class.forName(className);
                    Class argClasses[] = { String.class, Vector.class, Vector.class, Vector.class };
                    Object arguments[] = { uri.toString(), children, parents, links };
                    Constructor constructor = objclass.getConstructor(argClasses);
                    result = (ObjectNode) constructor.newInstance(arguments);
                    result.setUri(result.getUuri());
                } catch (Exception e) {
                    throw new ServiceAccessException(service, e);
                }
            }
        } catch (SQLException e) {
            throw createException(e, uri.toString());
        }
        return result;
    }

    // Locks
    public Enumeration enumerateLocks(Connection connection, Uri uri) throws ServiceAccessException {

        Vector lockVector = new Vector();
        PreparedStatement statement = null;
        ResultSet res = null;
        try {
            statement =
                connection.prepareStatement(
                    "select l.EXPIRATION_DATE, l.IS_INHERITABLE, l.IS_EXCLUSIVE, u2.URI_STRING as LCK, u3.URI_STRING as SUBJECT, u4.URI_STRING as TYPE, l.OWNER from LOCKS l, URI u, URI u2, URI u3, URI u4 where l.OBJECT_ID=u.URI_ID and u.URI_STRING=? and l.LOCK_ID=u2.URI_ID and l.SUBJECT_ID=u3.URI_ID and l.TYPE_ID = u4.URI_ID");
            statement.setString(1, uri.toString());
            res = statement.executeQuery();

            while (res.next()) {
                Date expirationDate = null;
                try {
                    Long timeValue = new Long(res.getLong("EXPIRATION_DATE"));
                    expirationDate = new Date(timeValue.longValue());
                } catch (NumberFormatException e) {
                    getLogger().log(e, LOG_CHANNEL, Logger.WARNING);
                    expirationDate = new Date();
                }
                NodeLock lock =
                    new NodeLock(
                        res.getString("LCK"),
                        uri.toString(),
                        res.getString("SUBJECT"),
                        res.getString("TYPE"),
                        expirationDate,
                        res.getInt("IS_INHERITABLE") == 1,
                        res.getInt("IS_EXCLUSIVE") == 1,
			res.getString("OWNER"));

                lockVector.addElement(lock);
            }
        } catch (SQLException e) {
            throw createException(e, uri.toString());
        } finally {
            close(statement, res);
        }
        return lockVector.elements();
    }

    public void killLock(Connection connection, Uri uri, NodeLock lock)
        throws ServiceAccessException, LockTokenNotFoundException {
        removeLock(connection, uri, lock);
    }

    public void putLock(Connection connection, Uri uri, NodeLock lock) throws ServiceAccessException {
        PreparedStatement statement = null;
        try {
            int inheritable = lock.isInheritable() ? 1 : 0;
            int exclusive = lock.isExclusive() ? 1 : 0;
            long lockid = assureUriId(connection, lock.getLockId());
            statement =
                connection.prepareStatement(
                    "insert into LOCKS (LOCK_ID,OBJECT_ID,SUBJECT_ID,TYPE_ID,EXPIRATION_DATE,IS_INHERITABLE,IS_EXCLUSIVE,OWNER) select ?, object.URI_ID, subject.URI_ID, type.URI_ID, ?, ?, ?, ? from URI object, URI subject, URI type WHERE object.URI_STRING=? and subject.URI_STRING=? and type.URI_STRING=?");
            statement.setLong(1, lockid);
            statement.setLong(2, lock.getExpirationDate().getTime());
            statement.setInt(3, inheritable);
            statement.setInt(4, exclusive);
            statement.setString(5, lock.getOwnerInfo());
            statement.setString(6, lock.getObjectUri());
            statement.setString(7, lock.getSubjectUri());
            statement.setString(8, lock.getTypeUri());

            statement.execute();
        } catch (SQLException e) {
            throw createException(e, uri.toString());
        } finally {
            close(statement);
        }
    }

    public void renewLock(Connection connection, Uri uri, NodeLock lock)
        throws ServiceAccessException, LockTokenNotFoundException {
        try {
            PreparedStatement statement = null;
            ResultSet rslt = null;
            try {
                statement =
                    connection.prepareStatement(
                        "select 1 from LOCKS l, URI u where l.LOCK_ID = u.URI_ID and u.URI_STRING = ?");
                statement.setString(1, lock.getLockId());
                rslt = statement.executeQuery();
                if (rslt.next()) {
                    removeLock(connection,uri,lock);
                    putLock(connection, uri, lock);
                } else {
                    throw new LockTokenNotFoundException(lock);
                }
            } finally {
                close(statement, rslt);
            }
        } catch (SQLException e) {
            throw createException(e, uri.toString());
        }
    }

    public void removeLock(Connection connection, Uri uri, NodeLock lock)
        throws ServiceAccessException, LockTokenNotFoundException {
        PreparedStatement statement = null;
        try {
            try {
                statement =
                    connection.prepareStatement(
                        deleteStatement("LOCKS","l",", URI u where l.LOCK_ID = u.URI_ID and u.URI_STRING=?"));
                statement.setString(1, lock.getLockId());
                statement.executeUpdate();
            } finally {
                close(statement);
            }
            try {
                statement =
                    connection.prepareStatement(
                        deleteStatement("URI","u",", LOCKS l where u.URI_STRING=?"));
                statement.setString(1, lock.getLockId());
                statement.executeUpdate();
            } finally {
                close(statement);
            }
        } catch (SQLException e) {
            throw createException(e, uri.toString());
        }
    }

    // Permissions

    public Enumeration enumeratePermissions(Connection connection, Uri uri) throws ServiceAccessException {
        Vector permissions = new Vector();
        PreparedStatement statement = null;
        ResultSet res = null;
        try {
            statement =
                connection.prepareStatement(
                    "select ou.URI_STRING, su.URI_STRING, au.URI_STRING, p.VERSION_NO, p.IS_INHERITABLE, p.IS_NEGATIVE from PERMISSIONS p, URI ou, URI su, URI au where p.OBJECT_ID = ou.URI_ID and ou.URI_STRING = ? and p.SUBJECT_ID = su.URI_ID and p.ACTION_ID = au.URI_ID order by SUCCESSION");
            statement.setString(1, uri.toString());
            res = statement.executeQuery();
            while (res.next()) {
                String object = res.getString(1);
                String subject = res.getString(2);
                String action = res.getString(3);
                String revision = res.getString(4);
                if ("NULL".equals(revision)) {
                    revision = null;
                }
                boolean inheritable = (res.getInt(5) == 1);
                boolean negative = (res.getInt(6) == 1);
                NodePermission permission =
                    new NodePermission(object, revision, subject, action, inheritable, negative);
                permissions.add(permission);
            }
        } catch (SQLException e) {
            throw createException(e, uri.toString());
        } finally {
            close(statement, res);
        }
        return permissions.elements();
    }

    public void grantPermission(Connection connection, Uri uri, NodePermission permission)
        throws ServiceAccessException {
        PreparedStatement statement = null;
        ResultSet res = null;
        int succession = 0;

        try {
            // FIXME: This might be useless if insert inserts nothing if insert...select works a i expect
            // FIXME: What happens, if only revision number, inheritable or negative changes?
            // FIXME
            //            revokePermission(connection, uri, permission);

            try {
                statement =
                    connection.prepareStatement(
                        "select max(p.SUCCESSION) from URI ou, PERMISSIONS p where p.OBJECT_ID = ou.URI_ID and ou.URI_STRING = ?");
                statement.setString(1, uri.toString());
                res = statement.executeQuery();
                res.next();
                succession = res.getInt(1) + 1;
            } finally {
                close(statement, res);
            }

            assureUriId(connection,permission.getSubjectUri());
            assureUriId(connection,permission.getActionUri());

            try {              
                
                int inheritable = permission.isInheritable() ? 1 : 0;
                int negative = permission.isNegative() ? 1 : 0;

                statement =
                    connection.prepareStatement(
                        "insert into PERMISSIONS (OBJECT_ID,SUBJECT_ID,ACTION_ID,VERSION_NO, IS_INHERITABLE,IS_NEGATIVE,SUCCESSION) select ou.URI_ID, su.URI_ID, au.URI_ID, ?, ?, ?, ? from URI ou, URI su, URI au where ou.URI_STRING = ? and su.URI_STRING = ? and au.URI_STRING = ?");
                statement.setString(1, getRevisionNumberAsString(permission.getRevisionNumber()));
                statement.setInt(2, inheritable);
                statement.setInt(3, negative);
                statement.setInt(4, succession);
                statement.setString(5, uri.toString());
                statement.setString(6, permission.getSubjectUri());
                statement.setString(7, permission.getActionUri());
                if (statement.executeUpdate() != 1) {
                    String msg = "Failed to insert permission ("
                    + uri.toString() 
                    + "," + permission.getSubjectUri()
                    + "," + permission.getActionUri() 
                    + ")"; 
                    getLogger().log(msg,LOG_CHANNEL,Logger.ERROR);
                    throw new ServiceAccessException(service, msg);
                }
            } finally {
                close(statement);
            }
        } catch (SQLException e) {
            throw createException(e, uri.toString());
        }
    }

    public void revokePermission(Connection connection, Uri uri, NodePermission permission)
        throws ServiceAccessException {
        if (permission == null) return;
        StringBuffer sql = new StringBuffer("delete from PERMISSIONS where (OBJECT_ID, SUBJECT_ID, ACTION_ID) IN" +
            " (SELECT ou.URI_ID, su.URI_ID, au.URI_ID FROM URI ou, URI su, URI au WHERE ou.URI_STRING = ? and su.URI_STRING = ? and au.URI_STRING = ?)");
        PreparedStatement statement = null;
        try {
            final NodeRevisionNumber revisionNumber;
            revisionNumber = permission.getRevisionNumber();
            // generate proper sql based on content of revision number
            if (revisionNumber != null) {
                sql.append(" and VERSION_NO = ?");
            } else {
                sql.append(" and VERSION_NO IS NULL");
            }
            statement = connection.prepareStatement(sql.toString());
            statement.setString(1, uri.toString());
            statement.setString(2, permission.getSubjectUri());
            statement.setString(3, permission.getActionUri());
            if (revisionNumber != null) {
                statement.setString(4, revisionNumber.toString());
            }
            statement.executeUpdate();
        } catch (SQLException e) {
            throw createException(e, uri.toString());
        } finally {
            close(statement);
        }
    }

    public void revokePermissions(Connection connection, Uri uri) throws ServiceAccessException {
        PreparedStatement statement = null;
        try {
            statement =
                connection.prepareStatement(
                    deleteStatement("PERMISSIONS","p",", URI u where p.OBJECT_ID = u.URI_ID and u.URI_STRING = ?"));
            statement.setString(1, uri.toString());
            statement.executeUpdate();
        } catch (SQLException e) {
            throw createException(e, uri.toString());
        } finally {
            close(statement);
        }
    }

    public void createRevisionDescriptor(Connection connection, Uri uri, NodeRevisionDescriptor revisionDescriptor)
        throws ServiceAccessException {

        PreparedStatement statement = null;
        try {

            assureVersionInfo(connection, uri, revisionDescriptor);
            createVersionLabels(connection, uri, revisionDescriptor);
            for (Enumeration properties = revisionDescriptor.enumerateProperties(); properties.hasMoreElements();) {
                try {
                    NodeProperty property = (NodeProperty) properties.nextElement();
                    statement =
                        connection.prepareStatement(
                            "insert into PROPERTIES (VERSION_ID,PROPERTY_NAMESPACE,PROPERTY_NAME,PROPERTY_VALUE,PROPERTY_TYPE,IS_PROTECTED) select vh.VERSION_ID, ?, ?, ?, ?, ? from VERSION_HISTORY vh, URI u where vh.URI_ID = u.URI_ID and u.URI_STRING = ? and vh.REVISION_NO = ?");
                    int protectedProperty = property.isProtected() ? 1 : 0;
                    statement.setString(1, property.getNamespace());
                    statement.setString(2, property.getName());
                    statement.setString(3, property.getValue().toString());
                    statement.setString(4, property.getType());
                    statement.setInt(5, protectedProperty);
                    statement.setString(6, uri.toString());
                    statement.setString(7, revisionDescriptor.getRevisionNumber().toString());
                    statement.executeUpdate();
                } finally {
                    close(statement);
                }
            }
        } catch (SQLException e) {
            throw createException(e, uri.toString());
        }
    }

    public void removeRevisionContent(Connection connection, Uri uri, NodeRevisionDescriptor revisionDescriptor)
        throws ServiceAccessException {
        try {
            PreparedStatement statement = null;
            try {
                statement =
                    connection.prepareStatement(
                        deleteStatement("VERSION_CONTENT","vc",", VERSION_HISTORY vh, URI u where vc.VERSION_ID = vh.VERSION_ID and vh.REVISION_NO = ? and vh.URI_ID=u.URI_ID AND u.URI_STRING=?"));
                statement.setString(1, revisionDescriptor.getRevisionNumber().toString());
                statement.setString(2, uri.toString());
                statement.executeUpdate();
            } finally {
                close(statement);
            }
        } catch (SQLException e) {
            throw createException(e, uri.toString());
        }
    }

    public void removeRevisionDescriptor(Connection connection, Uri uri, NodeRevisionNumber revisionNumber)
        throws ServiceAccessException {
        PreparedStatement statement = null;
        try {
            removeVersionLabels(connection, uri, revisionNumber);
            try {
                statement =
                    connection.prepareStatement(
                        deleteStatement("PROPERTIES","p",", VERSION_HISTORY vh, URI u where p.VERSION_ID = vh.VERSION_ID and vh.REVISION_NO = ? and vh.URI_ID = u.URI_ID AND u.URI_STRING = ?"));
                statement.setString(1, revisionNumber.toString());
                statement.setString(2, uri.toString());
                statement.executeUpdate();
            } finally {
                close(statement);
            }
        } catch (SQLException e) {
            throw createException(e, uri.toString());
        }
    }

    public void removeRevisionDescriptors(Connection connection, Uri uri) throws ServiceAccessException {
        PreparedStatement statement = null;
        try {
            statement =
                connection.prepareStatement(
                    deleteStatement("VERSION_PREDS","vp",", VERSION_HISTORY vh, URI u where vp.VERSION_ID = vh.VERSION_ID and vh.URI_ID = u.URI_ID and u.URI_STRING = ?"));
            statement.setString(1, uri.toString());
            statement.executeUpdate();
        } catch (SQLException e) {
            throw createException(e, uri.toString());
        } finally {
            close(statement);
        }
    }

    public NodeRevisionContent retrieveRevisionContent(
        Connection connection,
        Uri uri,
        NodeRevisionDescriptor revisionDescriptor,
        boolean temporaryConnection)
        throws ServiceAccessException, RevisionNotFoundException {
        NodeRevisionContent result = null;
        try {
            PreparedStatement statement = null;
            ResultSet res = null;
            try {
                statement =
                    connection.prepareStatement(
                        "select vc.CONTENT from VERSION_CONTENT vc, VERSION_HISTORY vh, URI u where vc.VERSION_ID = vh.VERSION_ID and vh.URI_ID = u.URI_ID and u.URI_STRING = ? and vh.REVISION_NO = ?");
                statement.setString(1, uri.toString());
                statement.setString(2, revisionDescriptor.getRevisionNumber().toString());
                res = statement.executeQuery();
                if (!res.next()) {
                    throw new RevisionNotFoundException(uri.toString(), revisionDescriptor.getRevisionNumber());
                }
                InputStream is = res.getBinaryStream("CONTENT");
                if (is == null) {
                    throw new RevisionNotFoundException(uri.toString(), revisionDescriptor.getRevisionNumber());
                }
                result = new NodeRevisionContent();
                if (bcompress) {
                    getLogger().log("DeCompressing the data", LOG_CHANNEL, Logger.DEBUG);
                    StoreContentZip ziputil = new StoreContentZip();
                    ziputil.UnZip(is);
                    revisionDescriptor.setContentLength(ziputil.getContentLength());
                    is = ziputil.getInputStream();
                    result.setContent(is);
                } else {
                    if (temporaryConnection) {
                        result.setContent(new JDBCAwareInputStream(is, statement, res, connection));
                    } else {
                        result.setContent(new JDBCAwareInputStream(is, statement, res, null));
                    }
                }
            } finally {
                try {
                    close(statement, res);
                } finally {
                    // do not close when not compressed, as stream needs to be read
                    // before
                    if (bcompress) {
                        if (temporaryConnection) {
                            // XXX is needed, as calling store does not know if
                            // connection should be closed now
                            try {
                                connection.commit();
                            } finally {
                                connection.close();
                            }
                        }
                    }
                }
            }
        } catch (SQLException e) {
            throw createException(e, uri.toString());
        } catch (RevisionNotFoundException e) {
            // This is not that bad, but only means, no content available. Do not warn, as this happens frequently with users / actions 
            /* 
            getLogger().log(
                "RevisionNotFoundException encountered for " + uri.toString() + " revision " + revisionDescriptor.getRevisionNumber(),
                LOG_CHANNEL,
                4);
            */
            throw e;
        } catch (Exception e) {
            getLogger().log(e, LOG_CHANNEL, Logger.ERROR);
            throw new ServiceAccessException(service, e);
        }
        return result;
    }

    public NodeRevisionDescriptor retrieveRevisionDescriptor(
        Connection connection,
        Uri uri,
        NodeRevisionNumber revisionNumber)
        throws ServiceAccessException, RevisionDescriptorNotFoundException {
        NodeRevisionDescriptor revisionDescriptor = null;
        if (revisionNumber == null)
            throw new RevisionDescriptorNotFoundException(uri.toString());
        try {
            PreparedStatement statement = null;
            ResultSet res = null;

            String branch = null;
            Vector labels = new Vector();
            Hashtable properties = new Hashtable();
            long versionId = 0;
            try {
                statement =
                    connection.prepareStatement(
                        "select vh.VERSION_ID, b.BRANCH_STRING from VERSION_HISTORY vh, BRANCH b, URI u where vh.URI_ID = u.URI_ID and u.URI_STRING = ? and b.BRANCH_ID = vh.BRANCH_ID and vh.REVISION_NO = ?");
                statement.setString(1, uri.toString());
                statement.setString(2, revisionNumber.toString());
                res = statement.executeQuery();
                if (res.next()) {
                    versionId = res.getLong(1);
                    branch = res.getString(2);
                } else {
                    throw new RevisionDescriptorNotFoundException(uri.toString());
                }
            } finally {
                close(statement, res);
            }
            try {
                statement =
                    connection.prepareStatement(
                        "select l.LABEL_STRING from VERSION_LABELS vl, LABEL l where vl.VERSION_ID = ? and l.LABEL_ID = vl.LABEL_ID");
                statement.setLong(1, versionId);
                res = statement.executeQuery();
                while (res.next()) {
                    labels.addElement(res.getString(1));
                }
            } finally {
                close(statement, res);
            }
            try {
                statement =
                    connection.prepareStatement(
                        "select PROPERTY_NAME, PROPERTY_NAMESPACE, PROPERTY_VALUE, PROPERTY_TYPE, IS_PROTECTED from PROPERTIES where VERSION_ID = ?");
                statement.setLong(1, versionId);
                res = statement.executeQuery();
                while (res.next()) {
                    String propertyName = res.getString(1);
                    String propertyNamespace = res.getString(2);
                    NodeProperty property =
                        new NodeProperty(
                            propertyName,
                            res.getString(3),
                            propertyNamespace,
                            res.getString(4),
                            res.getInt(5) == 1);
                    properties.put(propertyNamespace + propertyName, property);
                }
            } finally {
                close(statement, res);
            }
            revisionDescriptor = new NodeRevisionDescriptor(revisionNumber, branch, labels, properties);
        } catch (SQLException e) {
            throw createException(e, uri.toString());
        }
        return revisionDescriptor;
    }

    public NodeRevisionDescriptors retrieveRevisionDescriptors(Connection connection, Uri uri)
        throws ServiceAccessException, RevisionDescriptorNotFoundException {

        NodeRevisionDescriptors revisionDescriptors = null;
        PreparedStatement statement = null;
        ResultSet res = null;
        try {
            NodeRevisionNumber initialRevision = new NodeRevisionNumber();
            // FIXME: Some code might be lost: workingRevisions is not filled with values
            Hashtable workingRevisions = new Hashtable();
            Hashtable latestRevisionNumbers = new Hashtable();
            Hashtable branches = new Hashtable();
            Vector allRevisions = new Vector();
            
            boolean isVersioned = false;
            // check if versioned
            try {
                statement =
                    connection.prepareStatement(
                        "select IS_VERSIONED from VERSION v, URI u where v.URI_ID = u.URI_ID and u.URI_STRING = ?");
                statement.setString(1, uri.toString());
                res = statement.executeQuery();
                if (res.next()) {
                    isVersioned = (res.getInt("IS_VERSIONED") == 1);
                } else {
                    throw new RevisionDescriptorNotFoundException(uri.toString());
                }
            } finally {
                close(statement, res);
            }
            // retrieve revision numbers
            try {
                statement =
                    connection
                        .prepareStatement(
                            "select vh.REVISION_NO, b.BRANCH_STRING from VERSION_HISTORY vh, BRANCH b, URI u where vh.BRANCH_ID = b.BRANCH_ID and vh.URI_ID = u.URI_ID and u.URI_STRING = ? order by "
                                + convertRevisionNumberToComparable("vh.REVISION_NO"));
                statement.setString(1, uri.toString());
                res = statement.executeQuery();
                while (res.next()) {
                    NodeRevisionNumber revisionNumber = new NodeRevisionNumber(res.getString(1));
                    allRevisions.add(revisionNumber); // will be used to get revisions successor
                    latestRevisionNumbers.put(res.getString(2), revisionNumber); // store the lastest revision / branch
                }
            } finally {
                close(statement, res);
            }
            
            for (Enumeration e = allRevisions.elements(); e.hasMoreElements();) {
                NodeRevisionNumber revisionNumber = (NodeRevisionNumber) e.nextElement();
                // get successors
                try {
                    statement =
                        connection.prepareStatement(
                            "select distinct pvh.REVISION_NO from VERSION_HISTORY vh, VERSION_HISTORY pvh, VERSION_PREDS vp, URI u where pvh.VERSION_ID = vp.VERSION_ID and vp.VERSION_ID = vh.VERSION_ID and vh.URI_ID = u.URI_ID and u.URI_STRING = ? and vh.REVISION_NO = ?");
                    statement.setString(1, uri.toString());
                    statement.setString(2, revisionNumber.toString());
                    res = statement.executeQuery();
                    Vector predecessors = new Vector();
                    while (res.next()) {
                        predecessors.add(new NodeRevisionNumber(res.getString(1)));
                    }
                    // XXX it is important to call ctor with String, otherwise it does an increment in minor number :(
                    branches.put(new NodeRevisionNumber(revisionNumber.toString()), predecessors);
                } finally {
                    close(statement, res);
                }
            }
            revisionDescriptors =
                new NodeRevisionDescriptors(
                    uri.toString(),
                    initialRevision,
                    workingRevisions,
                    latestRevisionNumbers,
                    branches,
                    isVersioned);
        } catch (SQLException e) {
            throw createException(e, uri.toString());
        }
        return revisionDescriptors;
    }

    public void createRevisionDescriptors(Connection connection, Uri uri, NodeRevisionDescriptors revisionDescriptors)
        throws ServiceAccessException {

        PreparedStatement statement = null;
        ResultSet res = null;
        try {
            int isVersioned = 0;
            if (revisionDescriptors.isVersioned())
                isVersioned = 1;
            boolean revisionExists;
            try {
                statement =
                    connection.prepareStatement(
                        "SELECT 1 FROM VERSION v, URI u WHERE v.URI_ID = u.URI_ID and u.URI_STRING = ?");
                statement.setString(1, uri.toString());
                res = statement.executeQuery();
                revisionExists = res.next();
            } finally {
                close(statement, res);
            }
            if (!revisionExists) {
                try {
                    statement =
                        connection.prepareStatement(
                            "insert into VERSION (URI_ID, IS_VERSIONED) select u.URI_ID, ? from URI u where u.URI_STRING = ?");
                    statement.setInt(1, isVersioned);
                    statement.setString(2, uri.toString());
                    statement.executeUpdate();
                } finally {
                    close(statement, res);
                }
            }
            boolean versionHistoryExists;
            NodeRevisionNumber versionNumber = revisionDescriptors.hasRevisions() ? revisionDescriptors.getLatestRevision() : new NodeRevisionNumber(); 
            try {
                statement =
                    connection.prepareStatement(
                        "SELECT 1 FROM VERSION_HISTORY vh, URI u WHERE vh.URI_ID = u.URI_ID and u.URI_STRING = ? and REVISION_NO = ?");
                statement.setString(1, uri.toString());
                statement.setString(2, versionNumber.toString());
                res = statement.executeQuery();
                versionHistoryExists = res.next();
            } finally {
                close(statement, res);
            }
            if (!versionHistoryExists && revisionDescriptors.getLatestRevision() != null) {
                try {
                    statement =
                        connection.prepareStatement(
                            "insert into VERSION_HISTORY (URI_ID, BRANCH_ID, REVISION_NO) select u.URI_ID, b.BRANCH_ID, ? from URI u, BRANCH b where u.URI_STRING = ? and b.BRANCH_STRING = ?");
                    statement.setString(1, getRevisionNumberAsString(versionNumber));
                    statement.setString(2, uri.toString());
                    statement.setString(3, NodeRevisionDescriptors.MAIN_BRANCH);
                    // FIXME: Create new revisions on the main branch???
                    statement.executeUpdate();
                } finally {
                    close(statement, res);
                }
            }
            
            
            // Add revision successors
            Enumeration revisionNumbers = revisionDescriptors.enumerateRevisionNumbers();
            while (revisionNumbers.hasMoreElements()) {
                NodeRevisionNumber nodeRevisionNumber = (NodeRevisionNumber) revisionNumbers.nextElement();
                
                Enumeration successors = revisionDescriptors.getSuccessors(nodeRevisionNumber);
                while (successors.hasMoreElements())
                {    
                    try {
                        NodeRevisionNumber successor = (NodeRevisionNumber) successors.nextElement();
                        
                        statement =
                            connection.prepareStatement(
                                    "insert into VERSION_PREDS (VERSION_ID, PREDECESSOR_ID) " +
                                    " select vr.VERSION_ID, suc.VERSION_ID" +
                                    " FROM URI uri, VERSION_HISTORY  vr, VERSION_HISTORY suc " +                            
                                    " where vr.URI_ID = uri.URI_ID " +
                                    " and suc.URI_ID = uri.URI_ID " +
                                    " and uri.URI_STRING = ? " +
                                    " and vr.REVISION_NO = ? " +
                                    " and suc.REVISION_NO = ? " );
                        
                        statement.setString(1, uri.toString());
                        statement.setString(2, nodeRevisionNumber.toString());
                        statement.setString(3, successor.toString());
                        statement.executeUpdate();
                    } finally {
                        close(statement);
                    }
                }
            }
            getLogger().log(
                revisionDescriptors.getOriginalUri() + revisionDescriptors.getInitialRevision(),
                LOG_CHANNEL,
                Logger.DEBUG);
        } catch (SQLException e) {
            throw createException(e, uri.toString());
        }
    }

    public void createRevisionContent(
        Connection connection,
        Uri uri,
        NodeRevisionDescriptor revisionDescriptor,
        NodeRevisionContent revisionContent)
        throws ServiceAccessException, RevisionAlreadyExistException {
        if (!storeRevisionContent(connection, uri, revisionDescriptor, revisionContent, true)) {
            throw new RevisionAlreadyExistException(uri.toString(), revisionDescriptor.getRevisionNumber());
        }
    }

    public void storeRevisionContent(
        Connection connection,
        Uri uri,
        NodeRevisionDescriptor revisionDescriptor,
        NodeRevisionContent revisionContent)
        throws ServiceAccessException, RevisionNotFoundException {
        if (!storeRevisionContent(connection, uri, revisionDescriptor, revisionContent, false)) {
            throw new RevisionNotFoundException(uri.toString(), revisionDescriptor.getRevisionNumber());
        }
    }

    public boolean storeRevisionContent(
        Connection connection,
        Uri uri,
        NodeRevisionDescriptor revisionDescriptor,
        NodeRevisionContent revisionContent,
        boolean create)
        throws ServiceAccessException {
        try {
            // check if revision already exists
            PreparedStatement statement = null;
            ResultSet res = null;
            try {
                statement =
                    connection.prepareStatement(
                        "select 1 from VERSION_CONTENT vc, VERSION_HISTORY vh, URI u where vc.VERSION_ID = vh.VERSION_ID and vh.URI_ID = u.URI_ID and u.URI_STRING = ? and vh.REVISION_NO = ?");
                statement.setString(1, uri.toString());
                statement.setString(2, revisionDescriptor.getRevisionNumber().toString());
                res = statement.executeQuery();
                if (res.next()) {
                    if (create)
                        return false;
                } else {
                    if (!create)
                        return false;
                }
            } finally {
                close(statement, res);
            }
            if (!create) {
                removeRevisionContent(connection, uri, revisionDescriptor);
            }
            storeContent(connection, uri, revisionDescriptor, revisionContent);
        } catch (SQLException e) {
            throw createException(e, uri.toString());
        } catch (IOException e) {
            getLogger().log(e, LOG_CHANNEL, Logger.ERROR);
            throw new ServiceAccessException(service, e);
        }
        return true;
    }

    protected void assureVersionInfo(Connection connection, Uri uri, NodeRevisionDescriptor revisionDescriptor)
        throws SQLException {
        PreparedStatement statement = null;
        ResultSet res = null;
        boolean revisionExists;
        try {
            statement =
                connection.prepareStatement(
                    "select 1 from VERSION v, URI u where v.URI_ID = u.URI_ID and u.URI_STRING = ?");
            statement.setString(1, uri.toString());
            res = statement.executeQuery();
            revisionExists = res.next();
        } finally {
            close(statement, res);
        }
        // FIXME: Is it true, that the default for IS_VERSIONED is 0 ??
        if (!revisionExists) {
            try {
                statement =
                    connection.prepareStatement(
                        "insert into VERSION (URI_ID, IS_VERSIONED) select u.URI_ID, ? from URI u where u.URI_STRING = ?");
                statement.setInt(1, 0);
                statement.setString(2, uri.toString());
                statement.executeUpdate();
            } finally {
                close(statement);
            }
        }
        boolean versionHistoryExists;
        try {
            statement =
                connection.prepareStatement(
                    "select 1 from VERSION_HISTORY vh, URI u where vh.URI_ID = u.URI_ID and u.URI_STRING = ? and REVISION_NO = ?");
            statement.setString(1, uri.toString());
            statement.setString(2, revisionDescriptor.getRevisionNumber().toString());
            res = statement.executeQuery();
            versionHistoryExists = res.next();
        } finally {
            close(statement, res);
        }
        if (!versionHistoryExists) {
            long branchId = assureBranchId(connection, revisionDescriptor.getBranchName());
            try {
                statement =
                    connection.prepareStatement(
                        "insert into VERSION_HISTORY (URI_ID, BRANCH_ID, REVISION_NO) select u.URI_ID, ?, ? from URI u where u.URI_STRING = ?");
                statement.setLong(1, branchId);
                statement.setString(2, getRevisionNumberAsString(revisionDescriptor.getRevisionNumber()));
                statement.setString(3, uri.toString());
                statement.executeUpdate();
            } finally {
                close(statement);
            }
        }
    }

    protected void storeContent(
        Connection connection,
        Uri uri,
        NodeRevisionDescriptor revisionDescriptor,
        NodeRevisionContent revisionContent)
        throws IOException, SQLException 
    {
        assureVersionInfo(connection, uri, revisionDescriptor);
        
        InputStream is = revisionContent.streamContent();
        if (is != null) {
            long blobLength = 0;
            File tempFile = null;
            
            if (bcompress) {
                getLogger().log("Compressing the data", LOG_CHANNEL, Logger.DEBUG);
                StoreContentZip ziputil = new StoreContentZip();
                ziputil.Zip(is);
                is = ziputil.getInputStream();
                
                // fix RevisionDescriptor Content Length
                if (revisionDescriptor.getContentLength() == -1) {
                    revisionDescriptor.setContentLength(ziputil.getInitialContentLength());
                }
                
                blobLength = ziputil.getContentLength();
            } else {
                // fix RevisionDescriptor Content Length
                if (revisionDescriptor.getContentLength() == -1) {
                    try {
                        tempFile = File.createTempFile("content", null);
                        FileOutputStream fos = new FileOutputStream(tempFile);
                        try {
                            byte buffer[] = new byte[2048];
                            do {
                                int nChar = is.read(buffer);
                                if (nChar == -1) {
                                    break;
                                }
                                fos.write(buffer, 0, nChar);
                            } while (true);
                        } finally {
                            fos.close();
                        }
                        is.close();
                        is = new FileInputStream(tempFile);
                        
                        revisionDescriptor.setContentLength(tempFile.length());
                        
                    } catch (IOException ex) {
                        getLogger().log(ex.toString() + " during the calculation of the content length.",
                            LOG_CHANNEL, Logger.ERROR);
                        throw ex;
                    }
                }
                
                blobLength = revisionDescriptor.getContentLength();
            }     
            
            PreparedStatement statement = null;
            try {
                statement =
                    connection.prepareStatement(
                        "insert into VERSION_CONTENT (VERSION_ID, CONTENT) select vh.VERSION_ID, ? from VERSION_HISTORY vh, URI u where vh.URI_ID = u.URI_ID and u.URI_STRING = ? and vh.REVISION_NO = ?");
                statement.setBinaryStream(1, is, (int) blobLength);
                statement.setString(2, uri.toString());
                statement.setString(3, revisionDescriptor.getRevisionNumber().toString());
                statement.executeUpdate();
                if (tempFile != null) {
                    is.close();
                    is = null;
                    tempFile.delete();
                }
            } finally {
                try {
                    close(statement);
                } finally {
                    if (is != null) {
                        // XXX some JDBC drivers seem to close the stream upon closing of 
                        // the statement; if so this will raise an IOException 
                        // silently ignore it...
                        try {
                            is.close();
                        } catch (IOException ioe) {
                            logger.log("Could not close stream", ioe, LOG_CHANNEL, Logger.DEBUG);
                        }
                    }
                }
            }
        }
    }

    public void storeRevisionDescriptor(Connection connection, Uri uri, NodeRevisionDescriptor revisionDescriptor)
        throws ServiceAccessException, RevisionDescriptorNotFoundException {
        removeRevisionDescriptor(connection, uri, revisionDescriptor.getRevisionNumber());
        createRevisionDescriptor(connection, uri, revisionDescriptor);
    }

    public void storeRevisionDescriptors(Connection connection, Uri uri, NodeRevisionDescriptors revisionDescriptors)
        throws ServiceAccessException, RevisionDescriptorNotFoundException {
        removeRevisionDescriptors(connection, uri);
        createRevisionDescriptors(connection, uri, revisionDescriptors);
    }

    protected void removeVersionLabels(Connection connection, Uri uri, NodeRevisionNumber revisionNumber)
            throws SQLException {
        PreparedStatement statement = null;
        try {
            statement = connection
                    .prepareStatement(deleteStatement("VERSION_LABELS","vl",", VERSION_HISTORY vh, URI u where vl.VERSION_ID = vh.VERSION_ID and vh.REVISION_NO = ? and vh.URI_ID = u.URI_ID AND u.URI_STRING = ?"));
            statement.setString(1, revisionNumber.toString());
            statement.setString(2, uri.toString());
            statement.executeUpdate();
        } finally {
            close(statement);
        }
    }

    protected void createVersionLabels(Connection connection, Uri uri, NodeRevisionDescriptor revisionDescriptor)
            throws SQLException {

        PreparedStatement statement = null;
        for (Enumeration labels = revisionDescriptor.enumerateLabels(); labels.hasMoreElements();) {
            long labelId = assureLabelId(connection, (String) labels.nextElement());
            try {
                statement = connection
                        .prepareStatement("insert into VERSION_LABELS (VERSION_ID, LABEL_ID) select VERSION_ID, ? from VERSION_HISTORY vh, URI u where vh.URI_ID = u.URI_ID and u.URI_STRING = ? and vh.REVISION_NO = ?");
                statement.setLong(1, labelId);
                statement.setString(2, uri.toString());
                statement.setString(3, revisionDescriptor.getRevisionNumber().toString());
                statement.executeUpdate();
            } finally {
                close(statement);
            }
        }
    }

    protected long assureUriId(Connection connection, String uri) throws SQLException {
        PreparedStatement statement = null;
        ResultSet res = null;
        try {
            statement = connection.prepareStatement("select URI_ID from URI where URI_STRING=?");
            statement.setString(1, uri);
            res = statement.executeQuery();
            if (res.next()) {
                long id = res.getLong(1);
                return id;
            }
        } finally {
            close(statement, res);
        }
        try {
            statement = connection.prepareStatement("insert into URI (URI_STRING) values (?)");
            statement.setString(1, uri);
            statement.executeUpdate();
        } finally {
            close(statement);
        }
        return assureUriId(connection, uri);
    }

    protected long assureBranchId(Connection connection, String branch) throws SQLException {
        PreparedStatement statement = null;
        ResultSet res = null;
        try {
            statement = connection.prepareStatement("select BRANCH_ID from BRANCH where BRANCH_STRING=?");
            statement.setString(1, branch);
            res = statement.executeQuery();
            if (res.next()) {
                long id = res.getLong(1);
                return id;
            }
        } finally {
            close(statement, res);
        }
        try {
            statement = connection.prepareStatement("insert into BRANCH (BRANCH_STRING) values (?)");
            statement.setString(1, branch);
            statement.executeUpdate();
        } finally {
            close(statement);
        }
        return assureBranchId(connection, branch);
    }

    protected long assureLabelId(Connection connection, String label) throws SQLException {
        PreparedStatement statement = null;
        ResultSet res = null;
        try {
            statement = connection.prepareStatement("select LABEL_ID from LABEL where LABEL_STRING=?");
            statement.setString(1, label);
            res = statement.executeQuery();
            if (res.next()) {
                long id = res.getLong(1);
                return id;
            }
        } finally {
            close(statement, res);
        }
        try {
            statement = connection.prepareStatement("insert into LABEL (LABEL_STRING) values (?)");
            statement.setString(1, label);
            statement.executeUpdate();
        } finally {
            close(statement);
        }
        return assureLabelId(connection, label);
    }

    protected void clearBinding(Connection connection, Uri uri)
        throws ServiceAccessException, ObjectNotFoundException, SQLException {
        PreparedStatement statement = null;

        // clear this uri from having bindings and being bound 

        try {
            statement =
                connection.prepareStatement(
                    deleteStatement("BINDING","c",", URI u where c.URI_ID = u.URI_ID and u.URI_STRING = ?"));
            statement.setString(1, uri.toString());
            statement.executeUpdate();
        } finally {
            close(statement);
        }

        try {
            statement =
                connection.prepareStatement(
                    deleteStatement("PARENT_BINDING","c",", URI u where c.URI_ID = u.URI_ID and u.URI_STRING = ?"));
            statement.setString(1, uri.toString());
            statement.executeUpdate();
        } finally {
            close(statement);
        }
    }

    protected void clearBinding(Connection connection, Uri uri, Set updatedBindings)
        throws ServiceAccessException, ObjectNotFoundException, SQLException {
        PreparedStatement statement = null;

        // XXX This default implementation does not do anything meaningfull with the additional
        // information in updatedBindings. Implement this in the specific adapters to remove only
        // those bindings that have actually changed. Have a look at the implementation for MySQL
        // as a reference. 
        clearBinding(connection, uri);
    }

    /**
     * Permits varying delete semantics between different db's or even db versions.
     * @param table name of table from which to delete
     * @param alias alias of table from which to delete
     * @param sql delete statement starting with " where" for single table and ", tablename" for multiple table delete
     * @return Delete statement in correct SQL dialect
     */
    protected String deleteStatement(String table, String alias, String sql) {
      return "delete " + table + " from " + table + " " + alias + sql;
    }

    // null means permission is valid for all revisions
    protected String getRevisionNumberAsString(NodeRevisionNumber revisionNumber) {
        return revisionNumber != null ? revisionNumber.toString() : null;
    }

    protected String convertRevisionNumberToComparable(String revisioNumber) {
        return "convert(numeric, SUBSTRING("+revisioNumber+",1,charindex('.',"+revisioNumber+"))), convert(numeric, SUBSTRING("+revisioNumber+",charindex('.',"+revisioNumber+")+1,100))";
    }

    protected void close(PreparedStatement statement) {
        try {
            if (statement != null) {
                statement.close();
		statement = null;
            }
        } catch (SQLException e) {
            getLogger().log(e, LOG_CHANNEL, Logger.WARNING);
        }
    }

    protected void close(PreparedStatement statement, ResultSet resultSet) {
        try {
            if (resultSet != null) {
                resultSet.close();
            }
        } catch (SQLException e) {
            getLogger().log(e, LOG_CHANNEL, Logger.WARNING);
        } finally {
            try {
                if (statement != null) {
                    statement.close();
		    statement = null;
                }
            } catch (SQLException e) {
                getLogger().log(e, LOG_CHANNEL, Logger.WARNING);
            }
        }
    }

    // overload this method to have a more detailed error handling
    protected ServiceAccessException createException(SQLException e, String uri) {
        /*
         * For Postgresql error states (match SQL92 ones for class 23
         * and 40) see
         * http://developer.postgresql.org/docs/postgres/errcodes-appendix.html
         */

        String sqlstate = e.getSQLState();

        if (sqlstate != null) {
            if (sqlstate.startsWith("23")) {
                getLogger().log(e.getErrorCode() + ": Low isolation conflict for " + uri, LOG_CHANNEL,
                        Logger.WARNING);
                throw new ConcurrencyConflictError(e, uri);

            } else if (sqlstate.startsWith("40")) {
                getLogger().log(e.getErrorCode() + ": Deadlock resolved on " + uri, LOG_CHANNEL,
                        Logger.WARNING);
                throw new ConcurrencyConflictError(e, uri);
            }
        }
        getLogger().log(
                "SQL error (" + e.getErrorCode() + ") state" + e.getSQLState() + " on " + uri
                        + ": " + e.getMessage(), LOG_CHANNEL, Logger.ERROR);
        return new ServiceAccessException(service, e);
    }
}
