/*
 * $Header: /esup-portail/injac/applications/SlideServer/Packages/esup-serveur-WebDav-3/update/slide/add-ons/stores/uPortalStore/src/stores/org/apache/slide/store/impl/rdbms/OldJDBCAdapter.java,v 1.1 2005/02/08 09:23:45 tbellemb Exp $
 * $Revision: 1.1 $
 * $Date: 2005/02/08 09:23:45 $
 *
 * ====================================================================
 *
 * Copyright 1999-2002 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.lang.reflect.Constructor;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.Enumeration;
import java.util.Hashtable;
import java.util.Vector;

import org.apache.slide.common.Service;
import org.apache.slide.common.ServiceAccessException;
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.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;

/**
* A database adapter, which is using almost the same SQL scheme as the 
* deprecated slidestore.reference.JDBCDescriptorsStore. This adapter this 
* meant for backward compatibility with Slide 1.x. Don't use this adapter
* for new  projects. 
* <strong>The content store methods are not supported.</strong> 
*
* This adapter requires a minor change of table locks. It should look like 
* this  (Postgres dialect, adapt it for your database):
* <pre>
*  create table locks(id varchar(4000), object varchar(4000), 
*  subject varchar(4000), type varchar(4000), 
*  expirationdate varchar(15), inheritable int, 
*  xexclusive int, ownerinfo varchar(255));
* </pre> 
*
* You may overide the method {@link #createException} for
* better error messages in case of parallel access.
*
* Oliver Zeigermann.             
* @version  $Revision: 1.1 $ $Date: 2005/02/08 09:23:45 $
* 
*/
public class OldJDBCAdapter extends AbstractRDBMSAdapter {

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

    // Column numbers

    // Structure descriptors

    protected static final int OBJECTS_URI = 1;
    protected static final int OBJECTS_CLASS = 2;

    protected static final int CHILDREN_URI = 1;
    protected static final int CHILDREN_CHILDURI = 2;

    protected static final int LINKS_LINK = 1;
    protected static final int LINKS_LINKTO = 2;

    // Security descriptors

    protected static final int PERMISSIONS_OBJECT = 1;
    protected static final int PERMISSIONS_REVISION_NUMBER = 2;
    protected static final int PERMISSIONS_SUBJECT = 3;
    protected static final int PERMISSIONS_ACTION = 4;
    protected static final int PERMISSIONS_INHERITABLE = 5;
    protected static final int PERMISSIONS_NEGATIVE = 6;

    // Lock descriptors

    protected static final int LOCKS_ID = 1;
    protected static final int LOCKS_OBJECT = 2;
    protected static final int LOCKS_SUBJECT = 3;
    protected static final int LOCKS_TYPE = 4;
    protected static final int LOCKS_EXPIRATIONDATE = 5;
    protected static final int LOCKS_INHERITABLE = 6;
    protected static final int LOCKS_EXCLUSIVE = 7;
    protected static final int LOCKS_OWNER = 8;

    // Content descriptors

    protected static final int REVISIONS_URI = 1;
    protected static final int REVISIONS_ISVERSIONED = 2;
    protected static final int REVISIONS_INITIALREVISION = 3;

    protected static final int WORKINGREVISION_URI = 1;
    protected static final int WORKINGREVISION_BASEREVISION = 2;
    protected static final int WORKINGREVISION_NUMBER = 3;

    protected static final int LATESTREVISIONS_URI = 1;
    protected static final int LATESTREVISIONS_BRANCHNAME = 2;
    protected static final int LATESTREVISIONS_NUMBER = 3;

    protected static final int BRANCHES_URI = 1;
    protected static final int BRANCHES_NUMBER = 2;
    protected static final int BRANCHES_CHILDNUMBER = 3;

    protected static final int REVISION_URI = 1;
    protected static final int REVISION_NUMBER = 2;
    protected static final int REVISION_BRANCHNAME = 3;

    protected static final int LABEL_URI = 1;
    protected static final int LABEL_NUMBER = 2;
    protected static final int LABEL_LABEL = 3;

    protected static final int PROPERTY_URI = 1;
    protected static final int PROPERTY_NUMBER = 2;
    protected static final int PROPERTY_NAME = 3;
    protected static final int PROPERTY_VALUE = 4;
    protected static final int PROPERTY_NAMESPACE = 5;
    protected static final int PROPERTY_TYPE = 6;
    protected static final int PROPERTY_PROTECTED = 7;

    public OldJDBCAdapter(Service service, Logger logger) {
        super(service, logger);
    }

    // ----------------------------------------------- DescriptorsStore Methods

    /**
     * Retrive an object.
     * 
     * @param uri Uri of the object we want to retrieve
     * @exception ServiceAccessException Error accessing the Service
     * @exception ObjectNotFoundException The object to retrieve was not found
     */
    public ObjectNode retrieveObject(Connection connection, Uri uri)
        throws ServiceAccessException, ObjectNotFoundException {

        ObjectNode result = null;
        PreparedStatement statement = null;

        try {

            statement =
                connection.prepareStatement(
                    "select * from objects where uri= ?");
            statement.setString(1, uri.toString());

            ResultSet res = statement.executeQuery();

            // Parsing result set

            String className;

            if (res.next()) {
                // Retrieving and loading the object
                className = res.getString(OBJECTS_CLASS);
            } else {
                // Object was not found ...
                throw new ObjectNotFoundException(uri);
            }

            closeStatement(statement);

            // Then, retrieve the children
            statement =
                connection.prepareStatement(
                    "select * from children where uri= ?");
            statement.setString(1, uri.toString());
            res = statement.executeQuery();

            Vector childrenVector = new Vector();

            // Parse result set
            while (res.next()) {
                // Load each permission
                childrenVector.addElement(res.getString(CHILDREN_CHILDURI));
            }
            closeStatement(statement);

            statement =
                connection.prepareStatement(
                    "select * from links where linkto= ?");
            statement.setString(1, uri.toString());
            res = statement.executeQuery();

            Vector linksVector = new Vector();

            // Parse result set
            while (res.next()) {
                // Load each permission
                linksVector.addElement(res.getString(LINKS_LINKTO));
            }

            closeStatement(statement);

            if (className.equals("org.apache.slide.structure.LinkNode")) {

                String linkTo = new String();
                statement =
                    connection.prepareStatement(
                        "select * from links where link= ?");
                statement.setString(1, uri.toString());
                res = statement.executeQuery();

                if (res.next())
                    linkTo = res.getString(LINKS_LINKTO);

                closeStatement(statement);

                result =
                    new LinkNode(
                        uri.toString(),
                        childrenVector,
                        linksVector,
                        linkTo);

            } else {

                try {
                    Class objclass = Class.forName(className);

                    Class[] argClasses =
                        {
                            Class.forName("java.lang.String"),
                            Class.forName("java.util.Vector"),
                            Class.forName("java.util.Vector")};
                    Object[] arguments =
                        { uri.toString(), childrenVector, linksVector };

                    Constructor constructor =
                        objclass.getConstructor(argClasses);
                    result = (ObjectNode) constructor.newInstance(arguments);
                } catch (Exception e) {
                    // ClassNotFoundException, NoSuchMethodException, etc. 
                    throw createException(e, uri);
                }

            }

        } catch (SQLException e) {
            throw createException(e, uri);
        } finally {
            closeStatement(statement);
        }
        return result;
    }

    /**
     * Update an object.
     * 
     * @param object Object to update
     * @exception ServiceAccessException Error accessing the Service
     * @exception ObjectNotFoundException The object to update was not found
     */
    public void storeObject(Connection connection, Uri uri, ObjectNode object)
        throws ServiceAccessException, ObjectNotFoundException {

        PreparedStatement statement = null;

        try {
            statement =
                connection.prepareStatement(
                    "select * from objects where uri= ?");
            statement.setString(1, uri.toString());

            ResultSet res = statement.executeQuery();

            // Parsing result set

            if (!res.next()) {
                throw new ObjectNotFoundException(uri);
            }

            closeStatement(statement);

            // Updating children
            statement =
                connection.prepareStatement(
                    "delete from children where uri= ?");
            statement.setString(1, object.getUri());
            statement.execute();
            closeStatement(statement);

            statement = null;
            Enumeration children = object.enumerateChildren();
            while (children.hasMoreElements()) {
                if (statement == null) {
                    statement =
                        connection.prepareStatement(
                            "insert into children values(?, ?)");
                }
                statement.setString(1, object.getUri());
                statement.setString(2, (String) children.nextElement());
                statement.execute();
            }
            closeStatement(statement);

            // Updating inbound links
            /*
            s = "delete from links where linkto='" + object.getUri() + "'";
            statement.execute(s);
            Enumeration links = object.enumerateLinks();
            while (children.hasMoreElements()) {
                s = "insert into links values('" 
                    + (String) links.nextElement() + "', '" 
                    + object.getUri() + "')";
                statement.execute(s);
            }
            */

            // Updating links
            statement =
                connection.prepareStatement("delete from links where link= ?");
            statement.setString(1, object.getUri());
            statement.execute();
            closeStatement(statement);

            if (object instanceof LinkNode) {
                statement =
                    connection.prepareStatement(
                        "insert into links values(?,?)");
                statement.setString(1, object.getUri());
                statement.setString(2, ((LinkNode) object).getLinkedUri());
                statement.execute();
                closeStatement(statement);
            }

        } catch (SQLException e) {
            throw createException(e, uri);
        } finally {
            closeStatement(statement);
        }

    }

    /**
      * Create a new object.
      * 
      * @param object ObjectNode
       * @param uri Uri of the object we want to create
       * @exception ServiceAccessException Error accessing the Service
       * @exception ObjectAlreadyExistsException An object already exists 
       * at this Uri
       */
    public void createObject(Connection connection, Uri uri, ObjectNode object)
        throws ServiceAccessException, ObjectAlreadyExistsException {

        PreparedStatement statement = null;
        try {

            String className = object.getClass().getName();
            statement =
                connection.prepareStatement(
                    "select * from objects where uri= ?");
            statement.setString(1, uri.toString());
            ResultSet res = statement.executeQuery();
            // Parsing result set
            if (res.next()) {
                throw new ObjectAlreadyExistsException(uri.toString());
            }

            closeStatement(statement);
            statement =
                connection.prepareStatement("insert into objects values(?,?)");
            statement.setString(1, uri.toString());
            statement.setString(2, className);
            statement.execute();
            closeStatement(statement);
            statement = null;
            // Inserting children
            Enumeration children = object.enumerateChildren();
            while (children.hasMoreElements()) {
                if (statement == null) {
                    statement =
                        connection.prepareStatement(
                            "insert into children values(?,?)");
                }
                statement.setString(1, uri.toString());
                statement.setString(2, (String) children.nextElement());
                statement.execute();
            }
            closeStatement(statement);
            // Updating inbound links
            /*
            Enumeration links = object.enumerateLinks();
            while (children.hasMoreElements()) {
                s = "insert into links values('" 
                    + (String) links.nextElement() + "', '" 
                    + object.getUri() + "')";
                statement.execute(s);
            }
            */ // If the object is a link, also store the link information
            if (object instanceof LinkNode) {
                statement =
                    connection.prepareStatement(
                        "insert into links values(?,?)");
                statement.setString(1, uri.toString());
                statement.setString(2, ((LinkNode) object).getLinkedUri());
                statement.execute();
                closeStatement(statement);
            }

        } catch (SQLException e) {
            throw createException(e, uri);
        } finally {
            closeStatement(statement);
        }

    }

    /**
      * Remove an object.
      * 
      * @param object Object to remove
      * @exception ServiceAccessException Error accessing the Service
      * @exception ObjectNotFoundException The object to remove was not found
      */
    public void removeObject(Connection connection, Uri uri, ObjectNode object)
        throws ServiceAccessException, ObjectNotFoundException {

        PreparedStatement statement = null;

        try {

            // Removing object
            statement =
                connection.prepareStatement("delete from objects where uri= ?");
            statement.setString(1, object.getUri());
            statement.execute();
            closeStatement(statement);

            // Removing children
            statement =
                connection.prepareStatement("delete from children where uri=?");
            statement.setString(1, object.getUri());
            statement.execute();
            closeStatement(statement);

            // Removing inbound links
            /*
            s = "delete from links where linkto='" + object.getUri() + "'";
            statement.execute(s);
            */

            // Removing links
            statement =
                connection.prepareStatement("delete from links where link= ?");
            statement.setString(1, object.getUri());
            statement.execute();
            closeStatement(statement);

        } catch (SQLException e) {
            throw createException(e, uri);
        }
    }
    
    /**
     * Grant a new permission.
     * 
     * @param permission Permission we want to create
     * @exception ServiceAccessException Error accessing the Service
     */
    public void grantPermission(Connection connection, Uri uri, NodePermission permission)
        throws ServiceAccessException {
        
        PreparedStatement statement = null;
        
        try {
            int inheritable = 0;
            if (permission.isInheritable()) {
                inheritable = 1;
            }
            
            int negative = 0;
            if (permission.isNegative()) {
                negative = 1;
            }
            
            NodeRevisionNumber revisionNumber = permission.getRevisionNumber();
            String revisionNumberStr = 
                (revisionNumber == null) ? null : revisionNumber.toString();
            
            statement = connection.prepareStatement
                ("insert into permissions values(?,?,?,?,?,?)");
            statement.setString(1, permission.getObjectUri());
            statement.setString(2, revisionNumberStr);
            statement.setString(3, permission.getSubjectUri());
            statement.setString(4, permission.getActionUri());
            statement.setInt(5, inheritable);
            statement.setInt(6, negative);
            statement.execute();
        } catch (SQLException e) {
            throw createException(e,uri);
        } finally {
            closeStatement(statement);
        }
        
    }
    
    
    /**
     * Revoke a permission.
     * 
     * @param permission Permission we want to create
     * @exception ServiceAccessException Error accessing the Service
     */
    public void revokePermission(Connection connection,Uri uri, NodePermission permission)
        throws ServiceAccessException {
        
        /* Warning changes to this method should also be done to CloudscapeDescriptorsStore */
        
        PreparedStatement statement = null;
        
        try {
            NodeRevisionNumber revisionNumber = permission.getRevisionNumber();
            if(revisionNumber != null) {
                statement = connection.prepareStatement
                    ("delete from permissions where object= ? and subject = ? and action = ?  and revisionnumber = ? ");
                statement.setString(4, revisionNumber.toString());
            }
            else {
                statement = connection.prepareStatement
                    ("delete from permissions where object = ? and subject = ? and action = ? and revisionnumber is NULL");
            }

            statement.setString(1, permission.getObjectUri());
            statement.setString(2, permission.getSubjectUri());
            statement.setString(3, permission.getActionUri());
        
            statement.execute();
        } catch (SQLException e) {
            throw createException(e,uri);
        } finally {
            closeStatement(statement);
        }
        
    }
    
    
    /**
     * Revoke all the permissions on an object.
     * 
     * @param permission Permission we want to create
     * @exception ServiceAccessException Error accessing the Service
     */
    public void revokePermissions(Connection connection,Uri uri)
        throws ServiceAccessException {
        
        PreparedStatement statement = null;
        
        try {
            
            statement = connection.prepareStatement
                ("delete from permissions where object= ?");
            statement.setString(1, uri.toString());
            statement.execute();
        } catch (SQLException e) {
            throw createException(e,uri);
        } finally {
            closeStatement(statement);
        }
        
    }
    
    
    /**
     * Enumerate permissions on an object.
     * 
     * @param permission Permission we want to create
     * @exception ServiceAccessException Error accessing the Service
     */
    public Enumeration enumeratePermissions(Connection connection,Uri uri)
        throws ServiceAccessException {
        
        Vector permissionVector = new Vector();
        PreparedStatement statement = null;
        
        try {
            statement = connection.prepareStatement
                ("select * from permissions where object= ?");
            statement.setString(1, uri.toString());
            ResultSet res = statement.executeQuery();
            
            while (res.next()) {
                String object   = res.getString(PERMISSIONS_OBJECT);
                String revision = res.getString(PERMISSIONS_REVISION_NUMBER);
                String subject  = res.getString(PERMISSIONS_SUBJECT);
                String action   = res.getString(PERMISSIONS_ACTION);
 
                boolean inheritable = false;
                if (res.getInt(PERMISSIONS_INHERITABLE) == 1) {
                    inheritable = true;
                }
                boolean negative = false;
                if (res.getInt(PERMISSIONS_NEGATIVE) == 1) {
                    negative = true;
                }
                NodePermission permission = 
                    new NodePermission(object,revision,subject,
                                       action,inheritable,negative);
                permissionVector.addElement(permission);
            }
            
        } catch (SQLException e) {
            throw createException(e,uri);
        } finally {
            closeStatement(statement);
        }
        
        return permissionVector.elements();
    }
    
    /**
     * Create a new lock.
     * 
     * @param lock Lock token
     * @exception ServiceAccessException Service access error
     */
    public void putLock(Connection connection,Uri uri, NodeLock lock)
        throws ServiceAccessException {
        
        PreparedStatement statement = null;
        
        try {
            int inheritable = 0;
            if (lock.isInheritable()) {
                inheritable = 1;
            }
            
            int exclusive = 0;
            if (lock.isExclusive()) {
                exclusive = 1;
            }
            
            statement = connection.prepareStatement
                ("insert into locks values(?,?,?,?,?,?,?,?)");
            statement.setString(1, lock.getLockId());
            statement.setString(2, lock.getObjectUri());
            statement.setString(3, lock.getSubjectUri());
            statement.setString(4, lock.getTypeUri());
            statement.setString
                (5, String.valueOf(lock.getExpirationDate().getTime()));
            statement.setInt(6,inheritable);
            statement.setInt(7, exclusive);
            statement.setString(8,lock.getOwnerInfo());
            statement.execute();
        } catch (SQLException e) {
            throw createException(e,uri);
        } finally {
            closeStatement(statement);
        }
        
    }
    
    
    /**
     * Renew a lock.
     * 
     * @param lock Token to renew
     * @exception ServiceAccessException Service access error
     * @exception LockTokenNotFoundException Lock token was not found
     */
    public void renewLock(Connection connection,Uri uri, NodeLock lock)
        throws ServiceAccessException, LockTokenNotFoundException {
        
        PreparedStatement statement = null;
        
        try {
            
            int inheritable = 0;
            if (lock.isInheritable()) {
                inheritable = 1;
            }
            
            int exclusive = 0;
            if (lock.isExclusive()) {
                exclusive = 1;
            }
            
            statement = connection.prepareStatement
                ("delete from locks where id=?");
            statement.setString(1, lock.getLockId());
            statement.execute();
            closeStatement(statement);
            
            statement = connection.prepareStatement
                ("insert into locks values(?,?,?,?,?,?,?,?)");
            statement.setString(1, lock.getLockId());
            statement.setString(2, lock.getObjectUri());
            statement.setString(3, lock.getSubjectUri());
            statement.setString(4, lock.getTypeUri());
            statement.setString
                (5, String.valueOf(lock.getExpirationDate().getTime()));
            statement.setInt(6, inheritable);
            statement.setInt(7, exclusive);
            statement.setString(8,lock.getOwnerInfo());
            statement.execute();
            
        } catch (SQLException e) {
            throw createException(e,uri);
        } finally {
            closeStatement(statement);
        }
        
    }
    
    
    /**
     * Unlock.
     * 
     * @param lock Token to remove
     * @exception ServiceAccessException Service access error
     * @exception LockTokenNotFoundException Lock token was not found
     */
    public void removeLock(Connection connection,Uri uri, NodeLock lock)
        throws ServiceAccessException, LockTokenNotFoundException {
        
        Statement statement = null;
        
        try {
            
            statement = connection.createStatement();
            
            String s = null;
            
            s = "delete from locks where id='" + lock.getLockId() + "'";
            statement.execute(s);
            
        } catch (SQLException e) {
            throw createException(e,uri);
        } finally {
            closeStatement(statement);
        }
        
    }
    
    
    /**
     * Kill a lock.
     * 
     * @param lock Token to remove
     * @exception ServiceAccessException Service access error
     * @exception LockTokenNotFoundException Lock token was not found
     */
    public void killLock(Connection connection,Uri uri, NodeLock lock)
        throws ServiceAccessException, LockTokenNotFoundException {
        
        removeLock(connection,uri, lock);
        
    }
    
    
    /**
     * Enumerate locks on an object.
     * 
     * @param subject Subject
     * @return Enumeration List of locks which have been put on the subject
     * @exception ServiceAccessException Service access error
     */
    public Enumeration enumerateLocks(Connection connection,Uri uri)
        throws ServiceAccessException {
        
        Vector lockVector = new Vector();
        PreparedStatement statement = null;
        
        try {
            
            statement = connection.prepareStatement
                ("select * from locks where object= ?");
            statement.setString(1, uri.toString());
            statement.execute();
            ResultSet res = statement.getResultSet();
            
            while (res.next()) {
                Date expirationDate = null;
                try {
                    Long timeValue = new Long(res.getString
                                              (LOCKS_EXPIRATIONDATE));
                    expirationDate = new Date(timeValue.longValue());
                } catch (NumberFormatException e) {
                    expirationDate = new Date();
                }
                NodeLock lock = 
                    new NodeLock(res.getString(LOCKS_ID), 
                                 res.getString(LOCKS_OBJECT),
                                 res.getString(LOCKS_SUBJECT), 
                                 res.getString(LOCKS_TYPE),
                                 expirationDate, 
                                 (res.getInt(LOCKS_INHERITABLE) == 1),
                                 (res.getInt(LOCKS_EXCLUSIVE) == 1),
                                 res.getString(LOCKS_OWNER)
                                 );
                lockVector.addElement(lock);
            }
            
        } catch (SQLException e) {
            throw createException(e,uri);
        } finally {
            closeStatement(statement);
        }
        return lockVector.elements();
    }
    
    /**
       * Retrieve the revisions informations of an object.
       * 
       * @param uri Uri
       * @exception ServiceAccessException Service access error
       * @exception RevisionDescriptorNotFoundException Revision descriptor 
       * was not found
       */
      public NodeRevisionDescriptors retrieveRevisionDescriptors(Connection connection,Uri uri)
          throws ServiceAccessException, RevisionDescriptorNotFoundException {
        
          NodeRevisionDescriptors revisionDescriptors = null;
          PreparedStatement statement = null;
          PreparedStatement statement2 = null;
        
          try {
              ResultSet res = null;
            
              NodeRevisionNumber initialRevision = new NodeRevisionNumber();
              Hashtable workingRevisions = new Hashtable();
              Hashtable latestRevisionNumbers = new Hashtable();
              Hashtable branches = new Hashtable();
              boolean isVersioned = false;
            
              statement = connection.prepareStatement
                  ("select * from revisions where uri= ?");
              statement.setString(1, uri.toString());
              res = statement.executeQuery();
            
              if (res.next()) {
                  int isVersionedInt = res.getInt(REVISIONS_ISVERSIONED);
                  if (isVersionedInt == 1) {
                      isVersioned = true;
                  }
              } else {
                  throw new RevisionDescriptorNotFoundException(uri.toString());
              }
            
              closeStatement(statement);
            
              statement = connection.prepareStatement
                  ("select * from workingrevision where uri= ?");
              statement.setString(1, uri.toString());
              res = statement.executeQuery();
            
              while(res.next()) {
                  // TODO : Parse each working revision definition
              }
            
              closeStatement(statement);
            
              statement = connection.prepareStatement
                  ("select * from latestrevisions where uri=?");
              statement.setString(1, uri.toString());
              res = statement.executeQuery();
            
              while(res.next()) {
                  latestRevisionNumbers
                      .put(res.getString(LATESTREVISIONS_BRANCHNAME), 
                           new NodeRevisionNumber
                               (res.getString(LATESTREVISIONS_NUMBER)));
              }
              closeStatement(statement);
            
              statement = connection.prepareStatement
                  ("select * from revision where uri= ?");
              statement.setString(1, uri.toString());
              res = statement.executeQuery();
            
              while(res.next()) {
                  String currentRevisionNumber = res.getString(REVISION_NUMBER);
                
                  // We parse the revision list of the object
                  if (statement2 == null){
                      statement2 = connection.prepareStatement
                          ("select * from branches where uri = ? and xnumber = ?");
                  }
                  statement2.setString(1, uri.toString());
                  statement2.setString(2, currentRevisionNumber);
                  ResultSet res2 = statement2.executeQuery();
                  Vector childList = new Vector();
                
                  while (res2.next()) {
                      childList.addElement(new NodeRevisionNumber
                          (res2.getString(BRANCHES_CHILDNUMBER)));
                  }
                
                  branches.put(new NodeRevisionNumber(currentRevisionNumber), 
                               childList);
                
                  res2.close();
              }
              closeStatement(statement2);
            
              revisionDescriptors = new NodeRevisionDescriptors
                  (uri.toString(), initialRevision, workingRevisions, 
                   latestRevisionNumbers, branches, isVersioned);
            
          } catch (SQLException e) {
              throw createException(e,uri);
          } finally {
              closeStatement(statement);
              closeStatement(statement2);
          }
          return revisionDescriptors;
      }
    
    
      /**
       * Create a new revision information object.
       * 
       * @param uri Uri
       * @param revisionDescriptors Node revision descriptors
       * @exception ServiceAccessException Service access error
       */
      public void createRevisionDescriptors
          (Connection connection,Uri uri, NodeRevisionDescriptors revisionDescriptors)
          throws ServiceAccessException {
        
          // TODO : Here, we have the option of "cleaning up" before 
          // creating the new records in the database.
        
          PreparedStatement statement = null;
        
          try {
            
              // Creating record in revisions tables
            
              int isVersioned = 0;
              if (revisionDescriptors.isVersioned()) {
                  isVersioned = 1;
              }
            
              statement = connection.prepareStatement
                  ("insert into revisions values(?,?,?)");
              statement.setString(1,uri.toString());
              statement.setInt(2, isVersioned);
              statement.setString
                  (3, revisionDescriptors.getInitialRevision().toString());
              statement.execute();
              closeStatement(statement);
            
              // Creating records in working revisions table
              // ... TODO (working revisions are not used for now)
            
              // Creating records in latest revisions table
            
              // For now, only the latest revision from the main branch is stored
              if (revisionDescriptors.getLatestRevision() != null) {
                  statement = connection.prepareStatement
                      ("insert into latestrevisions values(?,?,?)");
                  statement.setString(1, uri.toString());
                  statement.setString
                      (2, NodeRevisionDescriptors.MAIN_BRANCH.toString());
                  statement.setString
                      (3, revisionDescriptors.getLatestRevision().toString());
                  statement.execute();
                  closeStatement(statement);
              }
            
              // Creating records in the branches table
              // TODO
            
          } catch (SQLException e) {
              throw createException(e,uri);
          } finally {
              closeStatement(statement);
          }
        
      }
    
    
      /**
       * Update revision information.
       * 
       * @param uri Uri
       * @param revisionDescriptors Node revision descriptors
       * @exception ServiceAccessException Service access error
       * @exception RevisionDescriptorNotFoundException Revision descriptor 
       * was not found
       */
      public void storeRevisionDescriptors
          (Connection connection,Uri uri, NodeRevisionDescriptors revisionDescriptors)
          throws ServiceAccessException, RevisionDescriptorNotFoundException {
        
          removeRevisionDescriptors(connection,uri);
          createRevisionDescriptors(connection,uri, revisionDescriptors);
        
      }
    
    
      /**
       * Remove revision information.
       * 
       * @param uri Uri
       * @exception ServiceAccessException Service access error
       */
      public void removeRevisionDescriptors(Connection connection,Uri uri)
          throws ServiceAccessException {
        
          PreparedStatement statement = null;
        
          try {
            
              statement = connection.prepareStatement
                  ("delete from revisions where uri= ?");
              statement.setString(1, uri.toString());
              statement.execute();
              closeStatement(statement);
            
              statement = connection.prepareStatement
                  ("delete from workingrevision where uri= ?");
              statement.setString(1, uri.toString());
              statement.execute();
              closeStatement(statement);
            
              statement = connection.prepareStatement
                  ("delete from latestrevisions where uri= ?");
              statement.setString(1, uri.toString());
              statement.execute();
              closeStatement(statement);
            
              statement = connection.prepareStatement
                  ("delete from branches where uri= ?");
              statement.setString(1, uri.toString());
              statement.execute();
              closeStatement(statement);
            
          } catch (SQLException e) {
              throw createException(e,uri);
          } finally {
              closeStatement(statement);
          }
        
      }
    
    
      /**
       * Retrieve an individual object's revision descriptor.
       * 
       * @param Uri uri
       * @param revisionNumber Node revision number
       */
      public NodeRevisionDescriptor retrieveRevisionDescriptor
          (Connection connection,Uri uri, NodeRevisionNumber revisionNumber)
          throws ServiceAccessException, RevisionDescriptorNotFoundException {
        
          NodeRevisionDescriptor revisionDescriptor = null;
          PreparedStatement statement = null;

      if(revisionNumber == null)
          throw new RevisionDescriptorNotFoundException(uri.toString());
        
          try {
            
              ResultSet res = null;
            
              String branchName = null;
              Vector labels = new Vector();
              Hashtable properties = new Hashtable();
            
              // Retrieving branch name (and also check that revision 
              // does indeed exist)
            
              statement = connection.prepareStatement
                  ("select * from revision where uri= ? and xnumber = ?");
              statement.setString(1, uri.toString());
              statement.setString(2, revisionNumber.toString());
              res = statement.executeQuery();
            
              if (res.next()) {
                  branchName = res.getString(REVISION_BRANCHNAME);
              } else {
                  throw new RevisionDescriptorNotFoundException(uri.toString());
              }
            
              closeStatement(statement);
            
              // Retrieve labels
            
              statement = connection.prepareStatement
                  ("select * from label where uri= ? and xnumber = ?");
              statement.setString(1, uri.toString());
              statement.setString(2, revisionNumber.toString());
              res = statement.executeQuery();
            
              while (res.next()) {
                  labels.addElement(res.getString(LABEL_LABEL));
              }
            
              closeStatement(statement);
            
              // Retrieve properties
            
              statement = connection.prepareStatement
                  ("select * from property where uri= ? and xnumber = ?");
              statement.setString(1, uri.toString());
              statement.setString(2, revisionNumber.toString());
              res = statement.executeQuery();
            
              while (res.next()) {
                  String propertyName = res.getString(PROPERTY_NAME);
                  String propertyNamespace = res.getString(PROPERTY_NAMESPACE);
                  NodeProperty property = 
                      new NodeProperty(propertyName, 
                                       res.getString(PROPERTY_VALUE), 
                                       propertyNamespace, 
                                       res.getString(PROPERTY_TYPE), 
                                       (res.getInt(PROPERTY_PROTECTED) == 1));
                  properties.put(propertyNamespace + propertyName, property);
              }
            
              revisionDescriptor = 
                  new NodeRevisionDescriptor(revisionNumber, branchName, 
                                             labels, properties);
            
          } catch (SQLException e) {
              throw createException(e,uri);
          } finally {
              closeStatement(statement);
          }
        
          return revisionDescriptor;
      }
    
    
      /**
       * Create a new revision descriptor.
       * 
       * @param uri Uri
       * @param revisionDescriptor Node revision descriptor
       * @exception ServiceAccessException Service access error
       */
      public void createRevisionDescriptor
          (Connection connection,Uri uri, NodeRevisionDescriptor revisionDescriptor)
          throws ServiceAccessException {
        
          PreparedStatement statement = null;
        
          try {
               
              statement = connection.prepareStatement
                  ("insert into revision values(?, ?, ?)");
              statement.setString(1, uri.toString());
              statement.setString
                  (2, revisionDescriptor.getRevisionNumber().toString());
              statement.setString(3, revisionDescriptor.getBranchName());
              statement.execute();
              closeStatement(statement);
            
              // Creating revision labels
              statement = null;
              Enumeration labels = revisionDescriptor.enumerateLabels();
              while (labels.hasMoreElements()) {
                  if (statement == null){
                      statement = connection.prepareStatement
                          ("insert into label values(?,?,?)");
                  }
                  statement.setString(1, uri.toString());
                  statement.setString
                      (2, revisionDescriptor.getRevisionNumber().toString());
                  statement.setString(3, (String)labels.nextElement());
                  statement.execute();
              }
              closeStatement(statement);

              // Creating associated properties
              statement = null;
              Enumeration properties = revisionDescriptor.enumerateProperties();
              while (properties.hasMoreElements()) {
                  NodeProperty property = 
                      (NodeProperty) properties.nextElement();
                  int protectedProperty = 0;
                  if (property.isProtected()) {
                      protectedProperty = 1;
                  }
                  if (statement == null){
                      statement = connection.prepareStatement
                          ("insert into property values(?,?,?,?,?,?,?)");
                  }
                  statement.setString(1, uri.toString());
                  statement.setString
                      (2, revisionDescriptor.getRevisionNumber().toString());
                  statement.setString(3, property.getName());
                  statement.setString(4, property.getValue().toString());
                  statement.setString(5, property.getNamespace());
                  statement.setString(6, property.getType());
                  statement.setInt(7, protectedProperty);
                  statement.execute();
              }
              closeStatement(statement);
            
          } catch (SQLException e) {
              throw createException(e,uri);
          } finally {
              closeStatement(statement);
          }
        
      }
    
    
      /**
       * Update a revision descriptor.
       * 
       * @param uri Uri
       * @param revisionDescriptors Node revision descriptor
       * @exception ServiceAccessException Service access error
       * @exception RevisionDescriptorNotFoundException Revision descriptor 
       * was not found
       */
      public void storeRevisionDescriptor
          (Connection connection,Uri uri, NodeRevisionDescriptor revisionDescriptor)
          throws ServiceAccessException, RevisionDescriptorNotFoundException {
        
          removeRevisionDescriptor(connection,uri, revisionDescriptor.getRevisionNumber());
          createRevisionDescriptor(connection,uri, revisionDescriptor);
      }
    
    
      /**
       * Remove a revision descriptor.
       * 
       * @param uri Uri
       * @param revisionNumber Revision number
       * @exception ServiceAccessException Service access error
       */
      public void removeRevisionDescriptor(Connection connection,Uri uri, NodeRevisionNumber number)
          throws ServiceAccessException {
        
          PreparedStatement statement = null;
        
          try {
            
              statement = connection.prepareStatement
                  ("delete from revision where uri= ? and xnumber = ?");
              statement.setString(1, uri.toString());
              statement.setString(2, number.toString());
              statement.execute();
              closeStatement(statement);
            
              // Removing revision labels
            
              statement = connection.prepareStatement
                  ("delete from label where uri= ? and xnumber = ?");
              statement.setString(1, uri.toString());
              statement.setString(2, number.toString());
              statement.execute();
              closeStatement(statement);
            
              // Removing associated properties
            
              statement = connection.prepareStatement
                  ("delete from property where uri= ? and xnumber = ?");
              statement.setString(1, uri.toString());
              statement.setString(2, number.toString());
              statement.execute();
            
          } catch (SQLException e) {
              throw createException(e,uri);
          } finally {
              closeStatement(statement);
          }
        
      }
    

    protected void closeStatement(Statement statement) {
        try {
            if (statement != null) {
                statement.close();
            }
        } 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(
                this,
                e,
                OldJDBCAdapter.LOG_CHANNEL,
                Logger.WARNING);
        } finally {
            try {
                if (statement != null) {
                    statement.close();
                }
            } catch (SQLException e) {
                getLogger().log(e, LOG_CHANNEL, Logger.WARNING);
            }
        }
    }

    /**
      * overload this method to have a more detailed error handling
      */
    protected ServiceAccessException createException(Exception e, Uri uri) {
        getLogger().log(
            "Error  on " + uri.toString() + ": " + e.getMessage(),
            LOG_CHANNEL,
            Logger.ERROR);
        return new ServiceAccessException(service, e);
    }

    /**
     * overload this method to have a more detailed error handling
     */
    protected ServiceAccessException createException(SQLException e, Uri uri) {
        e.printStackTrace();
        getLogger().log(
            "Error "
                + e.getErrorCode()
                + "," + e.getSQLState()
                + " on "
                + uri.toString()
                + ": "
                + e.getMessage(),
            LOG_CHANNEL,
            Logger.ERROR);
        return new ServiceAccessException(service, e);
    }

    /**
     * This method will always fail, since the ContentStore interface is not supported.
     */
    public NodeRevisionContent retrieveRevisionContent(Connection conn, Uri uri, NodeRevisionDescriptor revisionDescriptor, boolean temporaryConnection) throws ServiceAccessException, RevisionNotFoundException {       
       throw createException(new UnsupportedOperationException("ContentStore interface not suported"),uri);
    }

    /**
     * This method will always fail, since the ContentStore interface is not supported.
     */
    public void createRevisionContent(Connection conn, Uri uri, NodeRevisionDescriptor revisionDescriptor, NodeRevisionContent revisionContent) throws ServiceAccessException, RevisionAlreadyExistException {
        throw createException(new UnsupportedOperationException("ContentStore interface not suported"),uri);
        
    }
    /**
     * This method will always fail, since the ContentStore interface is not supported.
     */
    public void storeRevisionContent(Connection conn, Uri uri, NodeRevisionDescriptor revisionDescriptor, NodeRevisionContent revisionContent) throws ServiceAccessException, RevisionNotFoundException {
        throw createException(new UnsupportedOperationException("ContentStore interface not suported"),uri);
    }

    /**
     * This method will always fail, since the ContentStore interface is not supported.
     */
    public void removeRevisionContent(Connection conn, Uri uri, NodeRevisionDescriptor revisionDescriptor) throws ServiceAccessException {
        throw createException(new UnsupportedOperationException("ContentStore interface not suported"),uri);
    }
}
