/* Copyright (c) 2001, Oracle Corporation. All rights reserved. */
package oracle.portal.provider.v2.preference;

import java.io.*;
import java.sql.*;
import java.util.*;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import oracle.portal.log.LogManager;
import oracle.portal.provider.v2.*;
import oracle.portal.provider.v2.utils.ValidationError;
import oracle.portal.utils.xml.v2.*;
import oracle.webdb.utils.SimpleStringBuffer;
import oracle.webdb.utils.HTTPUtils;

/**
 * <code>DBPreferenceStore</code> is an implementation of the
 * <code>PreferenceStore</code> storage mechanism that persists data using a
 * JDBC compatible relational database.<p>
 *
 * Connections to the database are retrieved from a standard JDBC 2.0
 * connection pool through JNDI. Therefore, you must ensure that you have made
 * a <code>javax.sql.DataSource</code> for your connection available through
 * JNDI. On OC4J, this is done through the <code>data-sources.xml</code>
 * configuration file (consult the OC4J documentation for more details). The
 * JNDI path to the <code>DataSource</code> for the connection to be used must
 * be registered with a call to {@link #setConnection(String)}, or through a
 * <code>&lt;connection><i>jndi_path</i>&lt;/connection></code> declaration.<p>
 *
 * The persisted data is stored in a database table; one row per customization.
 * The name of the database table to be used to store preference data defaults
 * to <code>JPDK_PREFERENCE_STORE2</code>, but can be overriden with a call to
 * {@link #setTable(String)}, or through a
 * <code>&lt;table><i>TABLE_NAME</i>&lt;/table></code> declaration. A table
 * with this default name can be installed on a database connection using the
 * script in
 * <code>providers/dbPreferenceStore/jpdk_preference_store2.sql</code>.  If a
 * table of a different name is to be used, it must conform to the same design.
 */
public class DBPreferenceStore extends PreferenceStore
    implements InitializableXMLObject
{
    // Special characters used in pattern matching
    protected static final char SINGLE_MATCH_CHAR = '_';
    protected static final char WILDCARD_CHAR = '%';
    protected static final char ESCAPE_CHAR = '\\';

    // Constants used in SQL statements
    protected static final String SINGLE_WHERE_CLAUSE =
        "WHERE path_type = ? AND reference_path = ?";
    protected static final String SINGLE_VALID_WHERE_CLAUSE =
        SINGLE_WHERE_CLAUSE + " AND data_class = ?";

    // The default table name to use in our SQL statements
    protected static final String DEFAULT_TABLE_NAME = "jpdk_preference_store2";

    protected static final int READ_BUFFER_SIZE = 256;

    // Member variables for each of the SQL statements required by this
    // DBPreferenceStore. These are pre-Initialized by
    // initSqlStatements()
    protected String mSelectSql;
    protected String mSelectLockSql;
    protected String mInsertSql;
    protected String mUpdateSql;
    protected String mDeleteSql;
    protected String mDeleteMultiSql;

    protected DataSource mDataSource;
    protected String mDataSourcePath;
    protected String mTableName = DEFAULT_TABLE_NAME;
    
    /**
     * Null Constructor
     */
    public DBPreferenceStore()
    {
        initSqlStatements();
    }

    /**
     * Should be called immediately after construction of a
     * <code>DBPreferenceStore</code> to associate it with its parent object
     * (usually a {@link ProviderDefinition}). Currently, this method does
     * nothing.
     * @param parent the parent object. 
     * @exception NodeHandlerException if the parent object is not suitable.
     */
    public void preInitialize(Object parent) throws NodeHandlerException
    {
    }

    /**
     * Should be called once initialization of this
     * <code>DBPreferenceStore</code> is complete, to complete initialization
     * and perform any necessary validation.
     * @exception NodeHandlerException if validation is unsuccessful
     */
    public void postInitialize() throws NodeHandlerException
    {
    }

    /**
     * Sets the path to the <code>DataSource</code> from which this
     * <code>DBPreferenceStore</code> should retrieve its database connections.
     *
     * @param dataSourcePath JNDI path to a <code>DataSource</code> for a
     * pooled database connection
     * @exception PreferenceStoreException if a <code>DataSource</code> of the
     * given name cannot be retrieved
     */
    public void setConnection(String dataSourcePath) throws PreferenceStoreException
    {
        mDataSourcePath = dataSourcePath;
    }

    /**
     * Sets the name of the database table in which this
     * <code>DBPreferenceStore</code> should store customization data.  If not
     * set explicitly, the default name is
     * <code>"JPDK_PREFERENCE_STORE2"</code>.
     *
     * @param tableName name of the database table storing customization data
     */
    public void setTable(String tableName)
    {
        if(!mTableName.equalsIgnoreCase(tableName))
        {
            mTableName = tableName;
            initSqlStatements();
        }
    }

    /**
     * Gets an <code>Iterator</code> over all of the {@link Preference} objects
     * in this <code>PreferenceStore</code> stored under the given context path
     * with one of the given preference types. Note that this listing is
     * 'recursive', in that <code>Preference</code>s stored in context paths
     * below the given one will also be returned. Note also that the data
     * object is not loaded into any of the listed <code>Preference</code>s.
     * @param context the context path under which <code>Preference</code>s
     * should be listed.
     * @param preferenceTypes a list of strings, naming the preference types to
     * include, or <code>null</code> if all preference types should be
     * included.
     * @exception PreferenceStoreException if an error occurs during the
     * operation.
     */
    public Iterator list(String context, List preferenceTypes)
        throws PreferenceStoreException
    {
        return new DBPreferenceIterator(context, preferenceTypes);
    }

    /**
     * Destroys all of the {@link Preference} objects in this
     * <code>PreferenceStore</code> stored under the given context path with
     * one of the given preference types. Note that this operation is
     * 'recursive', in that <code>Preference</code>s stored in context paths
     * below the given one will also be destroyed.
     * @param context the context path under which <code>Preference</code>s
     * should be listed.
     * @param preferenceTypes a list of strings, naming the preference types to
     * include, or <code>null</code> if all preference types should be
     * included.
     * @exception PreferenceStoreException if an error occurs during the
     * operation.
     */
    public void destroy(String context, List preferenceTypes)
        throws PreferenceStoreException
    {
        Connection connection = getConnection();

        // Derive a delete statement that only includes the desired list of
        // types
        SimpleStringBuffer buff =
            new SimpleStringBuffer(225);
        buff.append("DELETE FROM ").append(mTableName).append(" ");
        buff.append(getMultiWhereClause(preferenceTypes));
        String deleteMultiSql = buff.toString();

        PreparedStatement deleteMultiStmt = null;
        try
        {
            deleteMultiStmt = connection.prepareStatement(deleteMultiSql);

            // Use a search path that will match all paths 'under' this context
            deleteMultiStmt.setString(1, createSearchPattern(context));

            // Delete all rows with matching paths
            deleteMultiStmt.executeUpdate();

            // Commit the change
            connection.commit();

            // Release resources (avoiding overhead of general-purpose finaly{}
            // block)
            deleteMultiStmt.close();
            connection.close();
        }
        // Gracefully release resources before passing on error
        catch (Exception e)
        {
            // Make sure we don't leave any transactions open
            quietCloseStatement(deleteMultiStmt);
            quietReleaseConnection(connection);
            throw new PreferenceStoreException(null, e.getMessage(), null, e);
        }
    }

    /**
     * Returns <code>true</code> if persisted data exists for the given
     * <code>Preference</code>.
     * @return <code>true</code> if persisted data exists for the given
     * <code>Preference</code>, <code>false</code> otherwise.
     */
    public boolean exists(Preference pref)
        throws PreferenceStoreException
    {
        Connection connection = getConnection();
        PreparedStatement selectStmt = null;
        String encodedReferencePath =
            encodeReferencePath(pref.getReferencePath());
        try
        {
            // Instantiate variables
            selectStmt = prepareSelect(connection,
                                       pref.getType(),
                                       encodedReferencePath,
                                       pref.getDataClassName());

            ResultSet result = selectStmt.executeQuery();

            // If we have a row, then it must exist
            boolean retVal = result.next();

            // Rollback from the last commit.
            try
            {
                connection.rollback();
            }
            catch (SQLException se)
            {
                LogManager.log("DBPreferenceStore: Error rolling back database "
                               + "connection", se);
                throw new PreferenceStoreException(null,
                                                   se.getMessage(),
                                                   null,
                                                   se);
            }
            // Release resources before returning value
            selectStmt.close();
            connection.close();
            return retVal;
        }
        catch(Exception e)
        {
            quietCloseStatement(selectStmt);
            quietReleaseConnection(connection);
            throw new PreferenceStoreException(null,
                                               e.getMessage(),
                                               null,
                                               e);
        }
    }
 
    /**
     * Gets an <code>InputStream</code> for reading the data of the given
     * <code>Preference</code>.<p> 
     *
     * This method is provided in conjunction with {@link #getOutputStream} as
     * a way of streamlining the process of copying data between
     * <code>PreferenceStore</code>s, since data can be copied directly from
     * the <code>InputStream</code> of one <code>Preference</code> to the
     * <code>OutputStream</code> of another, without having to instantiate a
     * <code>PreferenceDataObject</code>.
     * @exception EntryNotFoundException if no data for the given
     * <code>Preference</code> exists.
     * @exception PreferenceStoreException if any other error occurs during the
     * operation.
     */
    public InputStream getInputStream(Preference pref)
        throws EntryNotFoundException,
               PreferenceStoreException
    {
        Connection connection = getConnection();
        PreparedStatement selectStmt = null;
        InputStream returnVal = null;
        String encodedReferencePath =
            encodeReferencePath(pref.getReferencePath());

        try
        {
            // Select the single row corresponding to this Preference
            selectStmt = prepareSelect(connection,
                                       pref.getType(),
                                       encodedReferencePath,
                                       pref.getDataClassName());

            // Fetch the first row, if there is one
            ResultSet result = selectStmt.executeQuery();

            // Wrap this input stream around the one read from the row
            if(result.next())
            {
                returnVal = new DBInputStream(connection,
                                              selectStmt,
                                              result.getBinaryStream(1));
            }
            else
            {
                Object[] args = {pref.getReferencePath()};
                throw new EntryNotFoundException(null,
                                                 "Preference {0} not found",
                                                 args);
            }

        }
        catch (Exception e) 
        {
            quietCloseStatement(selectStmt);
            quietReleaseConnection(connection);
            if(e instanceof EntryNotFoundException)
            {
                throw (EntryNotFoundException)e;
            }
            else
            {
                throw new PreferenceStoreException(null,
                                                   e.getMessage(),
                                                   null,
                                                   e);
            }
        }
        return returnVal;
    }

    /**
     * Gets an <code>OutputStream</code> for writing the data of the given
     * <code>Preference</code> directly into storage.<p> 
     *
     * This method is provided in conjunction with {@link #getInputStream} as a
     * way of streamlining the process of copying data between
     * <code>PreferenceStore</code>s, since data can be copied directly from
     * the <code>InputStream</code> of one <code>Preference</code> to the
     * <code>OutputStream</code> of another, without having to instantiate a
     * <code>PreferenceDataObject</code>.
     * @exception EntryAlreadyExistsException if <code>overwrite</code> was
     * <code>false</code> and data for the given <code>Preference</code>
     * already exists in storage.
     * @exception PreferenceStoreException if any other error occurs during the
     * operation.
     */
    public OutputStream getOutputStream(Preference pref,
                                        boolean overwrite)
        throws EntryAlreadyExistsException,
               PreferenceStoreException
    {
        return new DBOutputStream(pref, overwrite);
    }

    /**
     * Destroys the data persisted for the given <code>Preference</code>.
     * @param pref the <code>Preference</code> whose persisted data should be
     * destroyed.
     * @exception PreferenceStoreException if an error occurs during the
     * operation.
     */
    public void destroy(Preference pref)
        throws PreferenceStoreException
    {
        Connection connection = getConnection();
        PreparedStatement deleteStmt = null;
        String encodedReferencePath =
            encodeReferencePath(pref.getReferencePath());
        try
        {
            deleteStmt = prepareSingleWhere(connection, mDeleteSql,
                                            pref.getType(),
                                            encodedReferencePath, 1);
            deleteStmt.executeUpdate();

            connection.commit();

            deleteStmt.close();
            connection.close();
        }
        catch(Exception e)
        {
            // Make sure we don't leave any transactions open
            quietCloseStatement(deleteStmt);
            quietReleaseConnection(connection);
            throw new PreferenceStoreException (null, e.getMessage(), null, e);
        }

    }

    /**
     * Validates the internal state of this <code>DBPreferenceStore</code>
     * instance. Errors found are reported using the supplied {@link
     * oracle.portal.provider.v2.utils.ValidationError} instance.
     *
     * @param context The context of the calling instance.
     * @param errors The current list of errors.
     */
    public void validate(String context, ValidationError errors)
    {
        String ctx = ValidationError.newContext(context, "DBPreferenceStore",
                                                getName());
        // validate the supertype       
        super.validate(ctx, errors);

        // Check the validity of the member variables
        if( mTableName == null )
        {
            errors.addError(ctx, "table must be set", null);
        }
        if( mDataSourcePath == null )
        {
            errors.addError(ctx, "dataSourcePath must be set", null);
        }
        if (mDataSourcePath != null)
        {
            try
            {
                //test the connection
                Connection conn = getConnection();
                conn.rollback();
                conn.close();
            }
            catch(SQLException se)
            {
                errors.addError(ctx, "Cant connect to database",
                                se.getMessage());
            }
            catch(PreferenceStoreException pse)
            {
                errors.addError(ctx, "Cant connect to database",
                                pse.getMessage());
            }
        }
    }
            
    // Given a 'context' reference path, create an encoded ASCII version that
    // can be used in a LIKE expression to match all paths under it (escaping
    // special characters with '\').
    protected String createSearchPattern(String referencePath)
    {
        StringTokenizer st = new StringTokenizer(referencePath,
            PATH_SEPARATOR_STRING);
        SimpleStringBuffer buff = new SimpleStringBuffer(200);
        while(st.hasMoreTokens())
        {
            String encodedString = HTTPUtils.encodeUTF(st.nextToken());
            // 'Escape' special characters such as '%', '_' and the escape
            // character with an escape character
            char[] encodedChars = encodedString.toCharArray();
            for(int i=0; i<encodedChars.length; i++)
            {
                switch(encodedChars[i])
                {
                case SINGLE_MATCH_CHAR:
                case WILDCARD_CHAR:
                case ESCAPE_CHAR:
                    buff.append(ESCAPE_CHAR);
                    break;
                }
                buff.append(encodedChars[i]);
            }
            buff.append(PATH_SEPARATOR_CHAR);
        }
        buff.append(WILDCARD_CHAR);
        return buff.toString();
    }

    // Derive 'reversed' encoded reference path from 'real' reference
    // path. Validate reference path components in the process
    protected String encodeReferencePath(String referencePath)
        throws PreferenceStoreException
    {
        // First, build up an array of encoded path components in reverse
        // order, so that we can then build up the reference path
        StringTokenizer st = new StringTokenizer(referencePath,
                                                 PATH_SEPARATOR_STRING, true);
        int componentCount = st.countTokens();
        SimpleStringBuffer referencePathBuff = new SimpleStringBuffer(200);
        if(componentCount > 0)
        {
            String[] encodedComponents = new String[componentCount];
            int i = componentCount;
            while(st.hasMoreTokens())
            {
                String component = st.nextToken();
                if(!component.equals(PATH_SEPARATOR_STRING))
                {
                    if(!isValidPathComponent(component, st.hasMoreTokens()))
                    {
                        SimpleStringBuffer exc = new SimpleStringBuffer(200);
                        exc.append("Invalid reference path \"");
                        exc.append(referencePath);
                        exc.append("\"");
                        throw new PreferenceStoreException(null, exc.toString());

                    }
                    component = HTTPUtils.encodeUTF(component);
                }
                encodedComponents[--i] = component;
            }

            // Now append together the reversed array of encoded component
            // strings to create the encoded reference path
            for(i=0; i<componentCount; i++)
            {
                referencePathBuff.append(encodedComponents[i]);
            }
        }
        return referencePathBuff.toString();
    }

    protected PreparedStatement prepareSingleWhere(Connection connection,
                                                   String stmtString,
                                                   String preferenceType,
                                                   String encodedReferencePath,
                                                   int startIndex)
        throws SQLException
    {
        PreparedStatement stmt = connection.prepareStatement(stmtString);
        int currIndex = startIndex;
        stmt.setString(currIndex++, preferenceType);
        stmt.setString(currIndex++, encodedReferencePath);
        return stmt;
    }

    protected PreparedStatement prepareSelect(Connection connection,
                                              String preferenceType,
                                              String encodedReferencePath,
                                              String dataClassName)
        throws SQLException
    {
        String stmtString = mSelectSql;
        if(dataClassName != null)
        {
            stmtString += " AND (data_class = ? OR data_class IS NULL)";
        }                
        PreparedStatement stmt = connection.prepareStatement(stmtString);
        stmt.setString(1, preferenceType);
        stmt.setString(2, encodedReferencePath);
        if(dataClassName != null)
        {
            stmt.setString(3, dataClassName);
        }
        return stmt;
    }

    protected class DBPreferenceIterator implements Iterator
    {
        String mContext;
        Connection mConnection;
        PreparedStatement mSelectStmt;
        ResultSet mResultSet;
        boolean mHasNext;

        protected DBPreferenceIterator(String context, List preferenceTypes)
            throws PreferenceStoreException
        {
            // Derive a select statement that only includes the desired list of
            // types
            SimpleStringBuffer buff = new SimpleStringBuffer(200);
            buff.append("SELECT search_path, reference_path, path_type, ");
            buff.append("data_class FROM ").append(mTableName).append(" ");
            buff.append(getMultiWhereClause(preferenceTypes));
            String selectMultiSql = buff.toString();

            mConnection = getConnection();
            try
            {
                mContext = context;

                // Select all the Preferences under the given context reference path
                mSelectStmt = mConnection.prepareStatement(selectMultiSql);
                mSelectStmt.setString(1, createSearchPattern(context));

                // Fetch the first row, if there is one
                mResultSet = mSelectStmt.executeQuery();
                if (!(mHasNext = mResultSet.next()))
                {
                    // Release the resource if next() will never be called.
                    cleanUp();
                }

            }
            catch (SQLException se) 
            {
                // Release our resources before we pass on the exception to the
                // caller
                cleanUp();
                throw new PreferenceStoreException(null, se.getMessage(), null, se);
            }
        }

        // Returns true if the iteration has more elements. (In other words,
        // returns true if next would return an element rather than throwing an
        // exception.)
        public boolean hasNext()
        {
            return mHasNext;
        }

        // Returns the next element in the iteration.
        public Object next()
        {
            try
            {
                if(!hasNext())
                {
                    throw new NoSuchElementException();
                }

                Preference retVal;
                try
                {
                    retVal = new Preference(decodePath(mResultSet.getString(1)),
                                                       mResultSet.getString(3),
                                                       mResultSet.getString(4));
                }
                catch(PreferenceStoreException pse)
                {
                    throw new RuntimeException("Invalid preference data: " +
                                               pse.getMessage());
                }

                // If this is the last row, then close all our resources
                if(!(mHasNext = mResultSet.next()))
                {
                    cleanUp();
                }
                return retVal;
            }
            catch(SQLException se)
            {
                mHasNext = false;
                cleanUp();
                LogManager.log("Database error in DBPreference.next()", se);
                throw new RuntimeException("Database error in DBPreference.next()");
            }
        }

        // Removes from the underlying collection the last element returned by
        // the iterator (optional operation). Not implemented.
        public void remove()
        {
            throw new UnsupportedOperationException();
        }

        protected void cleanUp()
        {
            quietCloseStatement(mSelectStmt);
            mSelectStmt = null;
            quietReleaseConnection(mConnection);
            mConnection = null;
        }

        // Called by the garbage collector on an object when garbage collection
        // determines that there are no more references to the object. A
        // subclass overrides the finalize method to dispose of system
        // resources or to perform other cleanup.
        protected void finalize() throws Throwable
        {
            cleanUp();
            super.finalize();
        }
    }

    protected class DBInputStream extends FilterInputStream
    {
        Connection mConnection;
        PreparedStatement mSelectStmt;

        protected DBInputStream(Connection connection,
                                PreparedStatement statement,
                                InputStream inputStream)
        {
            super(inputStream);
            mConnection = connection;
            mSelectStmt = statement;
        }

        // Closes this input stream and releases any system resources
        // associated with the stream.
        public void close()
            throws IOException
        {
            super.close();
            cleanUp();
        }

        protected void cleanUp()
        {
            quietCloseStatement(mSelectStmt);
            mSelectStmt = null;
            quietReleaseConnection(mConnection);
            mConnection = null;
        }

        // Called by the garbage collector on an object when garbage collection
        // determines that there are no more references to the object. A
        // subclass overrides the finalize method to dispose of system
        // resources or to perform other cleanup.
        protected void finalize() throws Throwable
        {
            cleanUp();
            super.finalize();
        }
    }

    protected class DBOutputStream extends ByteArrayOutputStream
    {
        Preference mPreference;
        String mEncodedReferencePath;
        String mEncodedSearchPath;
        Connection mConnection;
        boolean mRowExists;

        protected DBOutputStream(Preference pref, boolean overwrite)
            throws EntryAlreadyExistsException,
                   PreferenceStoreException
        {
            super();

            mPreference = pref;
            String referencePath = pref.getReferencePath();
            mEncodedReferencePath = encodeReferencePath(referencePath);

            // Set the encoded 'search path', whose path components should be
            // in the same order as the 'real' search path.
            mEncodedSearchPath = encodePath(referencePath);

            // Now lock the row that this OutputStream is going to write to
            mConnection = getConnection();
            PreparedStatement selectLockStmt = null;
            boolean error = false;
            try
            {
                selectLockStmt = prepareSingleWhere(mConnection,
                                                    mSelectLockSql,
                                                    pref.getType(),
                                                    mEncodedReferencePath,
                                                    1);
                // Lock the row, if it exists
                ResultSet result = selectLockStmt.executeQuery();
                // First, determine whether the row actually exists
                mRowExists = result.next();
                // If so, determine whether it is 'valid', i.e. its DATA_CLASS
                // matches the data class name
                boolean validRow = false;
                if(mRowExists)
                {
                    String thisClassName = mPreference.getDataClassName();
                    String existingClassName = result.getString(1);
                    validRow = thisClassName == null ||
                        existingClassName == null ||
                        existingClassName.equals(thisClassName);
                }

                if(!overwrite && validRow)
                {
                    Object[] args = {pref.getReferencePath()};
                    throw new EntryAlreadyExistsException(null,
                                                          "Preference {0} already exists",
                                                          args);
                }
                selectLockStmt.close();

                //Intentionally leave connection open for now
            }
            // Clean up on error
            catch (Exception e)
            {
                quietCloseStatement(selectLockStmt);
                cleanUp();
                if(e instanceof EntryAlreadyExistsException)
                {
                    throw (EntryAlreadyExistsException)e;
                }
                else
                {
                    throw new PreferenceStoreException(null, e.getMessage(), null, e);
                }
            }
        }

        // Closes this input stream and releases any system resources
        // associated with the stream.
        public void close()
            throws IOException
        {
            super.close();
            writeBytes();
        }

        protected void writeBytes()
            throws IOException
        {
            PreparedStatement updateStmt = null;
            byte[] bytes = toByteArray();
            InputStream byteStream = new ByteArrayInputStream(bytes);
            String referencePath = mPreference.getReferencePath();
            String type = mPreference.getType();
            try
            {
                // Now UPDATE / INSERT a row as necessary
                int classNameIndex;
                if(mRowExists)
                {
                    // Create an UPDATE statement that sets the non-PK columns
                    updateStmt = prepareSingleWhere(mConnection,
                                                    mUpdateSql,
                                                    type,
                                                    mEncodedReferencePath,
                                                    4);
                    classNameIndex = 3;                
                }
                else
                {
                    // Create an INSERT statement that sets all the columns
                    updateStmt = prepareSingleWhere(mConnection,
                                                    mInsertSql,
                                                    type,
                                                    mEncodedReferencePath,
                                                    3);
                    classNameIndex = 5;
                }

                // Set all the common variables

                // Make sure the data class is up to date
                String dataClassName = mPreference.getDataClassName();
                if(dataClassName == null)
                {
                    updateStmt.setNull(classNameIndex, Types.VARCHAR);
                }
                else
                {
                    updateStmt.setString(classNameIndex, dataClassName);
                }

                updateStmt.setString(1, mEncodedSearchPath);

                // Use an input stream so there is no restriction on length
                updateStmt.setBinaryStream(2, byteStream, bytes.length);

                updateStmt.executeUpdate();
                mConnection.commit();

                // Clean up resources
                byteStream.close();
                updateStmt.close();
                cleanUp();
            }
            catch(Exception e)
            {
                try
                {
                    byteStream.close();
                }
                catch(Exception e2)
                {
                }
                quietCloseStatement(updateStmt);
                cleanUp();
                if(e instanceof IOException)
                {
                    throw (IOException)e.fillInStackTrace();
                }
                if(e instanceof RuntimeException)
                {
                    throw (RuntimeException)e.fillInStackTrace();
                }
                else
                {
                    throw new IOException(e.getMessage());
                }
            }
        }

        protected void cleanUp()
        {
            if(mConnection != null)
            {
                quietReleaseConnection(mConnection);
                mConnection = null;
            }
        }

        // Called by the garbage collector on an object when garbage collection
        // determines that there are no more references to the object. A
        // subclass overrides the finalize method to dispose of system
        // resources or to perform other cleanup.
        protected void finalize() throws Throwable
        {
            cleanUp();
            super.finalize();
        }
    }

    private String getMultiWhereClause(List preferenceTypes)
    {
        SimpleStringBuffer buff = new SimpleStringBuffer(100);

        // Derive a 'WHERE' clause for selecting multiple rows
        buff.append("WHERE");

        // If appropriate, append an 'IN' clause that mentions all the path
        // types that are to be operated on
        Iterator i = preferenceTypes.iterator();
        if(i.hasNext())
        {
            buff.append(" path_type IN('");
            buff.append((String)i.next());
            buff.append("'");
            while(i.hasNext())
            {
                buff.append(", '");
                buff.append((String)i.next());
                buff.append("'");
            }
            buff.append(") AND");
        }
        // Make sure we use an 'escaped' comparison
        buff.append(" search_path LIKE ? ESCAPE '");
        buff.append(ESCAPE_CHAR);
        buff.append("'");
        return buff.toString();
    }

    // Initialize local array of SQL statements with versions that reference
    // the desired table
    private void initSqlStatements()
    {
        SimpleStringBuffer buff =
            new SimpleStringBuffer(200);
        buff.append("SELECT long_value FROM ");
        buff.append(mTableName);
        buff.append(" ");
        buff.append(SINGLE_WHERE_CLAUSE);
        mSelectSql = buff.toString();

        buff.setLength(0);
        buff.append("SELECT data_class FROM ");
        buff.append(mTableName);
        buff.append(" ");
        buff.append(SINGLE_WHERE_CLAUSE);
        buff.append(" FOR UPDATE");
        mSelectLockSql = buff.toString();

        buff.setLength(0);
        buff.append("INSERT INTO ");
        buff.append(mTableName);
        buff.append(" (search_path, long_value, path_type, reference_path, data_class) ");
        buff.append("VALUES (?, ?, ?, ?, ?)");
        mInsertSql = buff.toString();

        buff.setLength(0);
        buff.append("UPDATE ");
        buff.append(mTableName);
        buff.append(" SET search_path = ?, long_value = ?, data_class = ? ");
        buff.append(SINGLE_WHERE_CLAUSE);
        mUpdateSql = buff.toString();

        buff.setLength(0);
        buff.append("DELETE FROM ");
        buff.append(mTableName);
        buff.append(" ");
        buff.append(SINGLE_WHERE_CLAUSE);
        mDeleteSql = buff.toString();
    }

    private Connection getConnection() throws PreferenceStoreException
    {
        try
        {
            if(mDataSource == null)
            {
                try
                {
                    InitialContext ic = new InitialContext();
                    mDataSource = (DataSource)ic.lookup(mDataSourcePath);
                }
                catch(NamingException ne)
                {
                    SimpleStringBuffer exc = new SimpleStringBuffer(200);
                    exc.append("Error obtaining DataSource with path \"");
                    exc.append(mDataSourcePath);
                    exc.append("\"");
                    throw new PreferenceStoreException(null, exc.toString(), null, ne);
                }
            }

            Connection conn = mDataSource.getConnection();

            // LOB locators don't persist over transactions, so we have to disable
            // auto-commit
            if(conn.getAutoCommit())
            {
                conn.setAutoCommit(false);
            }

            return conn;
        }
        catch (SQLException se)
        {
            throw new PreferenceStoreException(null, se.getMessage(), null, se);
        }
    }

    private void quietReleaseConnection(Connection conn)
    {
        if(conn == null)
        {
            return;
        }

        try
        {
            // Roll back any changes made since the last commit.
            conn.rollback();
        }
        catch (SQLException se)
        {
            LogManager.log("DBPreferenceStore: Error rolling back database " +
                           "connection", se);
        }
        try
        {
            conn.close();
        }
        catch(Exception e)
        {
            LogManager.log("DBPreferenceStore: Error while closing database connection", e);
        }
    }

    private void quietCloseStatement(Statement s)
    {
        if(s == null)
        {
            return;
        }

        try
        {
            s.close();
        }
        catch(Exception e)
        {
            LogManager.log("DBPreferenceStore: Error while closing statement", e);
        }
    }    

}
