 /* Copyright (c) Oracle Corporation 2001, All Rights Reserved. */
package oracle.portal.provider.v2.personalize;

import java.io.*;
import java.sql.*;
import java.util.*;
import javax.servlet.*;
import javax.sql.DataSource;
import javax.naming.InitialContext;
import javax.naming.NamingException;


import oracle.portal.provider.v2.PortletDefinition;
import oracle.portal.provider.v2.PortletInstance;
import oracle.portal.provider.v2.ProviderInstance;
import oracle.portal.provider.v2.ProviderUser;
import oracle.portal.provider.v2.utils.ValidationError;
import oracle.portal.provider.v2.security.PortletSecurityManager;
import oracle.portal.log.LogManager;
import oracle.webdb.utils.SimpleStringBuffer;
import oracle.webdb.utils.HTTPUtils;


import oracle.portal.provider.v2.PortletNotFoundException;
import oracle.portal.provider.v2.PortletAlreadyExistsException;
import oracle.portal.provider.v2.security.AccessControlException;

/**
 * The <code>DBPersonalizationManager</code> class implements a
 * <code>PortletPersonalizationManager</code> using data objects to persist
 * customized data in a database.<p>
 *
 * The class of the data objects managed by a
 * <code>DBPersonalizationManager</code> is registered using {@link
 * #setDataClass(String)}. Most commonly this will be the result of a
 * declaration in the provider XML registry (e.g.
 * <code>&lt;dataClass><i>className</i>&lt;/dataClass></code>). 
 *
 * Your data class must implements the {@link PersonalizationObject}
 * interface. It will be responsible for reading and writing its
 * own content through its <code>read()</code> and <code>write()</code>
 * methods. 
 *
 * The framework includes an implementation of the <code>PersonalizationObject
 * </code> interface that allows you to store data in terms of name/value pairs.
 * You can use this or create your own implementation.
 *
 * The persisted data is stored in a database table; one row per customization.
 * Connections to the database are retrieved from a 'pool', managed by the J2EE
 * environment; therefore, you must ensure that you have configured an
 * appropriate data source.  In OC4J, this is done in the data-sources.xml file
 *
 * The name of the data source a DBPersonalizationManager will use to store
 * customizations is specified using {@link #setConnection(String)}.
 *
 * The name of the database table to be used to store customizations defaults
 * to <code>JPDK_PREFERENCE_STORE</code>, but can be overriden with a call to
 * {@link #setTable(String)}. A table with this default name can be created 
 * in a database using the script in 
 * <code>providers/dbPersonalization/jpdk_preference_store.sql</code>.
 *
 * If a table of a different name is to be used, it must conform to the same
 * design.
 *
 */
public class DBPersonalizationManager extends PortletPersonalizationManager
{
    // Constants for SQL representations of true and false
    private static final String TRUE_STRING = "Y";

    // Constants enumerating possible path types produced by this PersonalizationManager
    private static final String STANDARD_PATH_TYPE =
        "oracle.portal.personalization";

    // Always use UNIX-style separators in database paths
    private static final String SEPARATOR_CHAR = "/";

    // Wildcard character used in SQL queries
    private static final String WILDCARD_CHAR = "%";

    // Constants used in SQL statements
    private static final String SINGLE_WHERE_CLAUSE =
        "WHERE path_type = ? AND locale = ? AND reference_path = ?";
    private static final String SINGLE_VALID_WHERE_CLAUSE =
        SINGLE_WHERE_CLAUSE + " AND data_class = ?";
    private static final String MULTI_WHERE_CLAUSE =
        "WHERE path_type = ? AND default_flag = 'Y' AND reference_path = ?";
    private static final String MULTI_VALID_WHERE_CLAUSE =
        MULTI_WHERE_CLAUSE + " AND data_class = ?";

    // The default table name to use in out SQL statements
    private static final String DEFAULT_TABLE_NAME = "jpdk_preference_store";

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

    protected DataSource mDataSource;
    private String mDataSourcePath;
    private String mTableName = DEFAULT_TABLE_NAME;
    
    /**
     * Null Constructor
     */
    public DBPersonalizationManager() throws IOException
    {
        initSqlStatements();
    }

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

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

    /**
     *  Initializes the repository for this <code>DBPersonalizationManager</code>. This
     *  method is invoked on each of a Provider's
     *  PortletPersonalizationManagers whenever the Provider is registered on a
     *  Portal Node (when the Provider's <code>register()</code> method is
     *  called). This gives the personalization manager an opportunity to
     *  initialize the repository for the corresponding Portlet.<p>
     *
     *  The <code>DBPersonalizationManager</code> currently does nothing in response to
     *  this call.
     *
     *  @param p the portlet this <code>DBPersonalizationManager</code> is controlling.
     */
    public void init(PortletDefinition p)
    {
        // do nothing currently
    }

    /**
     *  Called to allow the Portlet Personalization manager to destroy its
     *  repository.  This method is called each time a Provider that manages
     *  this PortletPersonalizationManager's Portlet is deregistered (from a
     *  portal).<p>
     *
     *  In response to this call, the <code>DBPersonalizationManager</code>
     *  removes all rows from the database table that pertain to this Portlet.
     *
     *  @param p the portlet this PortletPersonalizationManager is controlling.
     */
    public void destroy(ProviderInstance pi, PortletDefinition pd) 
        throws AccessControlException, IOException
    {
        // don't do anything if the portlet doesn't support customizations
        if (!pd.hasShowEdit() && !pd.hasShowEditDefault())
            return;

        Connection connection = getConnection();
        PreparedStatement deleteAllStmt = null;

        try
        {
            deleteAllStmt = connection.prepareStatement(mDeleteAllSql);

            // Instantiate variable in DELETE statement with this path type
            deleteAllStmt.setString(1, STANDARD_PATH_TYPE);

            // Use a search path that will match all paths 'under' this portlet
            deleteAllStmt.setString(2, getSearchPrefix(pi, pd) + WILDCARD_CHAR);

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

            // Commit the change
            connection.commit();

            // clean up resources
            deleteAllStmt.close();
            releaseConnection(connection);
        }
        catch (SQLException e)
        {
            // Make sure we don't leave any transactions open
            try { connection.rollback(); } catch (SQLException e1) {}

            // clean up resources
            try { deleteAllStmt.close(); } catch (SQLException e1) {}
            releaseConnection(connection);

            SimpleStringBuffer msg = new SimpleStringBuffer(100);
            msg.append("DBPersonalizationManager.destroy(): Failed to destroy ");
            msg.append("customizations for Provider id: ");
            msg.append(pi.getProviderId());
            msg.append(" Portlet id: ");
            msg.append(pd.getId());

            LogManager.severe(msg.toString(), e);
            throw new IOException (msg.toString());
        }

    }

    /**
     * Creates the initial personalization reference for this (new)
     * PortletReference and establishes its (static) defaults.<p>
     *
     * In response to this call, the <code>DBPersonalizationManager</code>
     * creates a new instance of the data object class that is registered to
     * it, and calls its <code>init()</code> method. Finally, a default row for
     * the instance is created in the database table, ensuring the initial data
     * persists.
     *
     * @param ref the PortletReference.  The details of the PortletReference
     * determine whether this creates the default, system instance or a specific
     * user instance.
     * @param user the user attempting this operation.  The manager is expected
     * to authorize the user before completing this operation.
     */
    public void create(PortletReference ref, ProviderUser user)
        throws IOException, PortletAlreadyExistsException, AccessControlException
    {

        // First get a brand new data object of the appropriate class
        PersonalizationObject o = createDataObject(ref, ref.getType());

        create(ref, o, user);
    }

    /**
     * Creates the initial personalization reference for this (new)
     * PortletReference.  The initial data object representing this new
     * instance is passed as a parameter.<p>
     *
     * In response to this call, the <code>DBPersonalizationManager</code>
     * saves the passed object to a row in the database table, ensuring the
     * initial data persists.
     *
     * @param ref the PortletReference.  The details of the PortletReference
     *            determine whether this creates the default, system instance 
     *            or a specific user instance.
     * @param o the PersonalizationObject representing the initial values for 
     *          this instance.
     * @param user the user attempting this operation.  The manager is expected
     *             to authorize the user before completing this operation.
     */
    public void create(PortletReference ref, 
                       PersonalizationObject o, 
                       ProviderUser user)
        throws PortletAlreadyExistsException, 
               AccessControlException,
               IOException
    {
        Connection connection = getConnection();
        try
        {
            writeDataObject(o, ref, ref.getType(), connection, false);
            releaseConnection(connection);
        }
        catch (PortletNotFoundException pnfe)
        {
            // This should never get raised when called in this mode
            releaseConnection(connection);
            
            SimpleStringBuffer msg = new SimpleStringBuffer(100);
            msg.append("DBPersonalizationManager.create(): Failed to create ");
            msg.append("customizations for: ");
            msg.append(ref.toString());

            LogManager.warning(msg.toString(), pnfe);
        }

    }

    /**
     *  Returns true if a customization exists for this given reference.  This
     *  will be true if a create() has been previously called.
     *
     *  @param ref the portlet instance to check
     *  @param user the user making this request -- passed to give the manager access
     *  the the session.
     */
    public boolean exists(PortletReference ref, ProviderUser user) 
        throws AccessControlException
    {
        boolean exists = false;
        Connection connection = null;
        try
        {
            connection = getConnection();
            exists = exists(getReferencePath(ref, ref.getType()), 
                            ref.getLocale(), 
                            connection);
            releaseConnection(connection);
        }
        catch (IOException e)
        {
            // We will just have to ignore any exceptions that occur at this stage
            // and signal that a customization doesn't exist
            releaseConnection(connection);
            
            SimpleStringBuffer msg = new SimpleStringBuffer(100);
            msg.append("DBPersonalizationManager.exists(): Failed to determine ");
            msg.append("existence of: ");
            msg.append(ref.toString());
            LogManager.warning(msg.toString(), e);
        }
        return exists;
    }

    /**
     *  Returns a data object containing the customizations particular to this
     *  reference. If no customizations exist then the
     *  <code>PortletNotFoundException</code> is thrown.
     *
     *  @param ref the portlet reference that identifies the instance whose
     *  customizations are being requested.
     *  @param user the user attempting this operation.  The manager is expected
     *  to authorize the user before completing this operation.
     *  @return an Object (of the class specified with
     *  <code>setDataClass</code>) containing the customizations particular to
     *  this reference
     *  @exception PortletNotFoundException if no customizations exist
     */
    public PersonalizationObject read(PortletReference ref, ProviderUser user)
        throws PortletNotFoundException, AccessControlException, IOException
    {
        Connection connection = getConnection();
        PersonalizationObject o = null;
        int type = ref.getType();
        try
        {
            // loop backwards through the possible "levels" of customization from
            // the most specific to the least specific. 
            do
            {
                o = readDataObject(ref, type, connection);
                if (o != null)
                {
                    break;
                }
            }                    
            while(type-- >ref.TYPE_SYSTEM_DEFAULT);
            releaseConnection(connection);
        }
        catch (Exception e)
        {
            // make sure connection is closed
            releaseConnection(connection);

            SimpleStringBuffer msg = new SimpleStringBuffer(150);
            msg.append("DBPersonalizationManager: Failed to read ");
            msg.append("customization for Portlet: ");
            msg.append(ref.toString());
            msg.append(" Type: ");
            msg.append(type);
            
            LogManager.severe(msg.toString(), e);
            throw new IOException(msg.toString());
        } 
                  
        // Create a new default instance if we can't find one
        // This should never happen unless data has been lost from 
        // the customization datastore or a default was not created 
        // correctly when the portlet was added to the page
        if(o == null)
        {
            SimpleStringBuffer msg = new SimpleStringBuffer(150);
            msg.append("DBPersonalizationManager: Failed to locate ");
            msg.append("customization for reference: ");
            msg.append(ref.toString());
            msg.append("...creating new customization record");
            LogManager.warning(msg.toString());

            connection = getConnection();
            try
            {
                o = createDataObject(ref, PortletReference.TYPE_SYSTEM_DEFAULT);
                writeDataObject(o, ref, PortletReference.TYPE_SYSTEM_DEFAULT,
                                connection, false);
                releaseConnection(connection);
            }
            catch (Exception e)
            {
                releaseConnection(connection);
                msg = new SimpleStringBuffer(150);
                msg.append("DBPersonalizationManager: Failed to write ");
                msg.append("substitute default customization for Portlet: ");
                msg.append(ref.toString());
                msg.append(" Type: ");
                msg.append(type);
            
                LogManager.severe(msg.toString(), e);

                if (e instanceof IOException)
                {
                    throw (IOException)e;
                }
                else
                {
                    throw new IOException(msg.toString());
                }
            }
        }
            
        return o;
    }

    /**
     *  Updates this reference's customization using the passed data object.
     *
     *  @param ref the portlet reference that identifies the instance whose
     *  customizations are being updated.
     *  @param o the data object containing the new values (should implement
     *  PersonalizationObject).
     *  @param user the user attempting this operation.  The manager is expected
     *  to authorize the user before completing this operation.
     */
    public void write(PortletReference ref, 
                      PersonalizationObject o, 
                      ProviderUser user)
        throws IOException, 
               PortletNotFoundException, 
               AccessControlException
    {

        Connection connection = getConnection();
        try
        {
            writeDataObject(o, ref, ref.getType(), connection, true);
            releaseConnection(connection);
        }
        catch (PortletAlreadyExistsException paee)
        {
            // This should never get raised when called in this mode
            releaseConnection(connection);
            SimpleStringBuffer msg = new SimpleStringBuffer(150);
            msg.append("DBPersonalizationManager: Failed to write ");
            msg.append("customization for Portlet: ");
            msg.append(ref.toString());
            
            LogManager.warning(msg.toString(), paee);
        }
    }

    /**
     *  Destroys a given Portlet instance from the repository by reference. If
     *  the reference refers to the system (default) instance the manager
     *  should additionally delete all user customizations that correspond.
     *
     *  @param  ref the portlet reference that identifies the instance whose
     *  customizations are being deleted.
     *  @param user the user attempting this operation.  The manager is expected
     *  to authorize the user before completing this operation.
     */
    public void destroy(PortletReference ref, ProviderUser user)
        throws PortletNotFoundException, AccessControlException, IOException
    {
        Connection connection = getConnection();
        PreparedStatement deleteStmt = null;
        try
        {
            if (ref.isDefault())
            {
                deleteStmt = connection.prepareStatement(mDeleteAllSql);
                String pattern = getSearchPrefix(ref) + WILDCARD_CHAR;

                deleteStmt.setString(1, STANDARD_PATH_TYPE);
                deleteStmt.setString(2, pattern);
                deleteStmt.executeUpdate();

            }
            else
            {
                deleteStmt = prepareSingleWhere(connection, 1, mDeleteSql,
                                                ref.getLocale(),
                                                getReferencePath(ref, ref.getType()));

                deleteStmt.executeUpdate();
            }

            connection.commit();
            deleteStmt.close();
            releaseConnection(connection);
        }
        catch(SQLException e)
        {
            // Make sure we don't leave any transactions open
            try { connection.rollback(); } catch (Exception e2) {}
            try { deleteStmt.close(); } catch (Exception e2) {}
            releaseConnection(connection);

            SimpleStringBuffer msg = new SimpleStringBuffer(150);
            msg.append("DBPersonalizationManager: Failed to destroy ");
            msg.append("customization for Portlet: ");
            msg.append(ref.toString());
            
            LogManager.severe(msg.toString(), e);
            throw new IOException(msg.toString());
        }
    }

    /**
     *  Copies the instance customizations from one reference to another. If
     *  the reference refers to the system (default) instance the manager
     *  should additionally copy any other corresponding default customizations
     *  for different locales. If the customization for the to reference
     *  doesn't exist it is created else the current values are overwritten.
     *
     *  @param from reference for the portlet instance we are copying from.
     *  @param to reference for the portlet instance we are copying to.
     *  @param user the user attempting this operation.  The manager is expected
     *  to authorize the user before completing this operation.
     */
    public void copy(PortletReference from, PortletReference to, ProviderUser user)
        throws PortletNotFoundException, AccessControlException, IOException
    {
        Connection connection = getConnection();
        PreparedStatement deleteStmt = null;
        PreparedStatement selectStmt = null;
        PreparedStatement insertStmt = null;
        String fromPath = getReferencePath(from, from.getType());
        String toPath = getReferencePath(to, to.getType());
        String toSearch = getSearchPath(to);
        Locale l = from.getLocale();
        boolean isDefault = from.isDefault();

        if (!exists(fromPath, l, connection))
        {
            SimpleStringBuffer msg = new SimpleStringBuffer(150);
            msg.append("DBPersonalizationManager: Failed to locate ");
            msg.append("source customization: ");
            msg.append(from.toString());
        
            LogManager.severe(msg.toString());
            throw new PortletNotFoundException(msg.toString());
        }
        
        // from and to should either be both default references or both
        // non-default references
        if(isDefault != to.isDefault())
        {
            SimpleStringBuffer msg = new SimpleStringBuffer(150);
            msg.append("DBPersonalizationManager: Failed to copy ");
            msg.append("customization. Source and destination references ");
            msg.append("are not of same type");
        
            LogManager.severe(msg.toString());
            throw new AccessControlException(msg.toString());
        }

        ResultSet result = null;
        try
        {

            // If we are copying for the default user, we select both the
            // default language default customization and the
            // language-specific default customizations
            if(isDefault)
            {
                // Make sure the destinations don't already exist
                deleteStmt = prepareMultiWhere(connection, 1, mDeleteMultiSql,
                                               toPath);

                // Select all the defaults with this same reference path
                selectStmt = prepareMultiValidWhere(connection, 1, mSelectMultiSql,
                                                    fromPath);

            }
            // Otherwise, we just select the row for the specific reference
            else
            {

                // Make sure the destination doesn't already exist
                deleteStmt = prepareSingleWhere(connection, 1, mDeleteSql,
                                                l, toPath);

                // Select the single source row
                selectStmt = prepareSingleValidWhere(connection, 1, mSelectSql,
                                                     l, fromPath);
            }
            deleteStmt.executeUpdate();

            // Create an INSERT statement that sets all the columns. We
            // have to do this BEFORE fetching any rows or it causes input
            // streams to be closed
            insertStmt = prepareSingleValidWhere(connection, 4, mInsertSql,
                                                 l, toPath);

            result = selectStmt.executeQuery();

            // Now, we copy the selected rows
            if(result.next())
            {
                if(isDefault)
                {
                    insertStmt.setString(1, TRUE_STRING);
                }
                else
                {
                    insertStmt.setNull(1, Types.VARCHAR);
                }

                insertStmt.setString(2, toSearch);

                // Copy the data (and locale) of each selected row
                do
                {
                    // Use an input stream to send the data so that there
                    // is no restriction on length
                    byte[] bytes = result.getBytes(1);
                    insertStmt.setBinaryStream(3, new ByteArrayInputStream(bytes), bytes.length);

                    // If we selected multiple defaults, we have to make
                    // sure we copy over the locale
                    if(isDefault)
                    {
                        insertStmt.setString(5, result.getString(2));
                    }

                    insertStmt.executeUpdate();                       
                } while(result.next());
                    
                // Commit the transaction
                connection.commit();
            }
            
            // free all the resources
            result.close();
            selectStmt.close();
            deleteStmt.close();
            insertStmt.close();                
        }
        catch (SQLException e)
        {
            // Make sure we don't leave any transactions open
            try { connection.rollback(); } catch (Exception e2) {}
            
            // free all resources
            try { result.close(); } catch (Exception e2) {}
            try { selectStmt.close(); } catch (Exception e2) {}
            try { deleteStmt.close(); } catch (Exception e2) {}
            try { insertStmt.close(); } catch (Exception e2) {}
            releaseConnection(connection);

            SimpleStringBuffer msg = new SimpleStringBuffer(150);
            msg.append("DBPersonalizationManager: Failed to copy ");
            msg.append("customization from reference: ");
            msg.append(from.toString());
            msg.append(" to reference: ");
            msg.append(to.toString());
        
            LogManager.severe(msg.toString(), e);
            throw new IOException(msg.toString());
        }
    }

    /**
     * Validates the internal state of this
     * <code>DBPersonalizationManager</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, "DBPersonalizationManager",
                                       null);
        // Validate the supertype.
        super.validate(ctx, errors);
        
        // Check for the existence of a data source.
        if (mDataSourcePath == null)
        {
            errors.addError(ctx, "Data source location not set", null);
        }

        try
        {
            //Let's 'warm up' the appropriate connection in the connection pool
            Connection conn = getConnection();
            releaseConnection(conn);
        }
        catch(IOException ioe)
        {
            errors.addError(ctx, "Error obtaining connection from data source",
                            mDataSourcePath);
        }
    }

    // Initialize local array of SQL statements with versions that reference
    // the desired table
    private void initSqlStatements()
    {
        SimpleStringBuffer buff = new SimpleStringBuffer();

        buff.append("SELECT long_value FROM ");
        buff.append(mTableName);
        buff.append(" ");
        buff.append(SINGLE_VALID_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("SELECT long_value, locale FROM ");
        buff.append(mTableName);
        buff.append(" ");
        buff.append(MULTI_VALID_WHERE_CLAUSE);
        mSelectMultiSql = buff.toString();

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

        buff.setLength(0);
        buff.append("UPDATE ");
        buff.append(mTableName);
        buff.append(" SET default_flag = ?, 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();

        buff.setLength(0);
        buff.append("DELETE FROM ");
        buff.append(mTableName);
        buff.append("  ");
        buff.append("WHERE path_type = ? AND search_path LIKE ?");;
        mDeleteAllSql = buff.toString();

        buff.setLength(0);
        buff.append("DELETE FROM ");
        buff.append(mTableName);
        buff.append("  ");
        buff.append(MULTI_WHERE_CLAUSE);
        mDeleteMultiSql = buff.toString();
    }

    private Connection getConnection() throws IOException
    {
        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("\"");
                
                LogManager.severe(exc.toString());
                throw new IOException(exc.toString());
            }
        }

        try
        {
            Connection conn = mDataSource.getConnection();
            conn.setAutoCommit(false);
            return conn;
        }
        catch (SQLException se)
        {
            String msg = "DBPersonalizationManager: Failed to obtain db connection";
            LogManager.severe(msg, se);
            throw new IOException(msg);
        }
    }

    private void releaseConnection(Connection conn)
    {
        try
        {
            conn.close();
        }
        catch(Exception e)
        {
            LogManager.warning("DBPPersonalizationManager: Error while " +
                               "releasing database connection", e);
        }
    }

    
    private void closeStatement(Statement s)
    {
        try
        {
            s.close();
        }
        catch(SQLException e)
        {
            LogManager.warning("DBPersonalizationManager: Error closing statement",
                               e);
        }
    }

    /**
     * create a new data object instance and initialize it from the PortletReference. 
     *
     * @param ref PortletReference identifying the portlet instance being
     *            customized
     * @return the newly created/intialized object
     */
    private PersonalizationObject createDataObject(PortletReference ref, 
                                                   int refType) 
        throws IOException 
    {
        PersonalizationObject po = null;
        try
        {
            po = (PersonalizationObject)getDataClass().newInstance();
            po.init(ref);
        }
        catch(Exception e) 
        {
            SimpleStringBuffer msg = new SimpleStringBuffer(150);
            msg.append("DBPersonalizationManager: Failed to create ");
            msg.append("data object for reference: ");
            msg.append(ref.toString());
            msg.append(" Dataclass: ");
            msg.append(getDataClass().getName());

            LogManager.severe(msg.toString(), e);
            throw new IOException(msg.toString());
        }
        return po;
    }

    // Derive the portlet reference suffix of the reference path key, with MOST
    // selective values first
    private String getReferenceSuffix(PortletReference ref)
    {
        // Derive the suffix of the search path key, with MOST selective values
        // first
        SimpleStringBuffer path = new SimpleStringBuffer();
        path.append(SEPARATOR_CHAR);
        path.append(ref.getInstanceName());
        path.append(SEPARATOR_CHAR);
        path.append(ref.getPortletId());
        path.append(SEPARATOR_CHAR);
        path.append(ref.getProviderId());
        return path.toString();
    }

    // Derive the reference path key, with MOST selective values first
    private String getReferencePath(PortletReference ref, int type)
    {
        SimpleStringBuffer path = new SimpleStringBuffer();
        // Only include username if not a default
        if (type == PortletReference.TYPE_USER)
        {
            String userName = HTTPUtils.encodeUTF(ref.getUser().getName());
            path.append(userName);
        }
        path.append(getReferenceSuffix(ref));
        return path.toString();
    }

    // Derive the portlet prefix of the search path key, with LEAST selective
    // values first
    private String getSearchPrefix(ProviderInstance pi, PortletDefinition pd)
    {
        SimpleStringBuffer path = new SimpleStringBuffer(100);
        path.append(pi.getProviderId());
        path.append(SEPARATOR_CHAR);
        path.append(pd.getId());
        path.append(SEPARATOR_CHAR);
        return path.toString();
    }

    // Derive the portlet reference prefix of the search path key, with LEAST
    // selective values first
    private String getSearchPrefix(PortletReference ref)
    {
        SimpleStringBuffer path = new SimpleStringBuffer(100);
        path.append(getSearchPrefix(ref.getProviderInstance(), 
                                    ref.getPortletDefinition()));
        path.append(ref.getInstanceName());
        path.append(SEPARATOR_CHAR);
        return path.toString();
    }

    // Derive the search path key, with LEAST selective values first
    private String getSearchPath(PortletReference ref)
    {
        SimpleStringBuffer path = new SimpleStringBuffer(getSearchPrefix(ref));
        // Only include username if not a default
        if (!ref.isDefault())
        {
            String userName = HTTPUtils.encodeUTF(ref.getUser().getName());
            path.append(userName);
        }
        return path.toString();
    }

    private String getLanguagePath(Locale l)
    {
        String language = l.getLanguage();
        String country = l.getCountry();

        if (country == null || country.length() == 0)
        {
            return language;
        }
        else
        {
            return language + "_" + country;
        }
    }

    private void writeBytes(PortletReference ref,
                            int refType,
                            Connection connection,
                            boolean shouldExist,
                            byte[] bytes)
        throws SQLException, PortletAlreadyExistsException,
               PortletNotFoundException
    {
        PreparedStatement selectLockStmt = null;
        PreparedStatement updateStmt = null;
        ResultSet result = null;
        OutputStream os = null;
        String out = getReferencePath(ref, refType);
        try
        {
            selectLockStmt = prepareSingleWhere(connection, 1, mSelectLockSql,
                                                ref.getLocale(), out);
            // Lock the row, if it exists
            result = selectLockStmt.executeQuery();
            // First, determine whether the row actually exists
            boolean rowExists = result.next();
            // If so, determine whether it is 'valid', i.e. its DATA_CLASS
            // matches the data class name
            boolean validRow = rowExists && result.getString(1).equals(getDataClass().getName());

            // free resources from select            
            result.close();
            selectLockStmt.close();

            // Validate that the row exists when it should
            if(shouldExist)
            {
                if(!validRow)
                {
                    SimpleStringBuffer msg = new SimpleStringBuffer(150);
                    msg.append("DBPersonalizationManager: Customization ");
                    msg.append("not valid for update: ");
                    msg.append(ref.toString());
                    msg.append(" Expected dataclass: ");
                    msg.append(getDataClass().getName());
        
                    LogManager.severe(msg.toString());
                    throw new PortletNotFoundException(msg.toString());
                }
            }
            else
            {
                if(validRow)
                {
                    SimpleStringBuffer msg = new SimpleStringBuffer(150);
                    msg.append("DBPersonalizationManager: Customization ");
                    msg.append("found - but not expected: ");
                    msg.append(ref.toString(refType));
        
                    LogManager.severe(msg.toString());
                    throw new PortletAlreadyExistsException();
                }
            }

            // Now UPDATE / INSERT a row as necessary
            if(rowExists)
            {
                // Create an UPDATE statement that sets the non-PK columns
                updateStmt = prepareSingleWhere(connection, 5, mUpdateSql,
                                                ref.getLocale(), out);

                // Make sure the data class is up to date
                updateStmt.setString(4, getDataClass().getName());
                
            }
            else
            {
                // Create an INSERT statement that sets all the columns
                updateStmt = prepareSingleValidWhere(connection, 4, mInsertSql,
                                                     ref.getLocale(), out);
            }

            // Set all the common variables

            // We indicate defaults with a 'Y' and non-defaults with NULL
            if(ref.isDefault())
            {
                updateStmt.setString(1, TRUE_STRING);
            }
            else
            {
                updateStmt.setNull(1, Types.VARCHAR);
            }
            // Derive a 'search path', which should be a 'mirror image' of
            // the reference path
            updateStmt.setString(2, getSearchPath(ref));

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

            updateStmt.executeUpdate();
            
            updateStmt.close();
        }
        catch (SQLException se)
        {
            // free resources but do not close the connection because we did
            // not open it.
            try { result.close(); } catch (Exception e) {}
            try { selectLockStmt.close(); } catch (Exception e) {}
            try { updateStmt.close(); } catch (Exception e) {}
            
            SimpleStringBuffer msg = new SimpleStringBuffer(150);
            msg.append("DBPersonalizationManager: Failed to update ");
            msg.append("customization for reference: ");
            msg.append(ref.toString());
        
            LogManager.severe(msg.toString(), se);
            throw se;
        }
    }

    // Support saving and restoring of data objects implementing
    // PersonalizationObject 
    private void writeDataObject(PersonalizationObject o, 
                                 PortletReference ref,
                                 int refType,
                                 Connection connection, 
                                 boolean shouldExist)
        throws PortletAlreadyExistsException, 
               PortletNotFoundException,
               IOException
    {
        // We need to know the total number of bytes in the output stream, so
        // we use an intermediate ByteArrayOutputStream
        ByteArrayOutputStream bos = new ByteArrayOutputStream();
        try
        {
            o.write(bos);
            bos.close();
            writeBytes(ref, refType, connection, shouldExist, bos.toByteArray());
            bos = null;
            connection.commit();
        }
        catch (Exception e)
        {
            try { bos.close(); } catch (Exception e2) {}
            try { connection.rollback(); } catch (Exception e2) {}
            
            SimpleStringBuffer msg = new SimpleStringBuffer(150);
            msg.append("DBPersonalizationManager: Failed to write ");
            msg.append("data object to reference: ");
            msg.append(ref.toString(refType));
            LogManager.severe(msg.toString(), e);

            if(e instanceof PortletAlreadyExistsException)
            {
                throw (PortletAlreadyExistsException)e;
            }
            else if(e instanceof PortletNotFoundException)
            {
                throw (PortletNotFoundException)e;
            }
            else if(e instanceof IOException)
            {
                throw (IOException)e;
            }
            else
            {
                throw new IOException(msg.toString());
            }
        }
    }

    private PersonalizationObject readDataObject(PortletReference ref, 
                                  int type,
                                  Connection connection)
        throws IOException
    {
        PersonalizationObject o = null;
        InputStream is = null;
        String in = getReferencePath(ref, type);
        PreparedStatement selectStmt = null;
        ResultSet result = null;
        try
        {
            selectStmt = prepareSingleValidWhere(connection, 1, mSelectSql,
                                                 ref.getLocale(), in);
            result = selectStmt.executeQuery();
            if(result.next())
            {
                is = result.getBinaryStream(1);
            }
            else
            {
                // customization was not found for this ref/type combination
                // Return NULL so calling method can try another ref/type
                // combination
                return null;
            }

            // Get a brand new data object of the appropriate class
            o = createDataObject(ref, type);
            o.read(is);
            
            // free resources
            is.close();
            result.close();
            selectStmt.close();
        }
        catch(Exception e)
        {
            try { is.close(); } catch (Exception e2) {}
            try { result.close(); } catch (Exception e2) {}
            try { selectStmt.close(); } catch (Exception e2) {}
            
            SimpleStringBuffer msg = new SimpleStringBuffer(150);
            msg.append("DBPersonalizationManager: Failed to read ");
            msg.append("data object for reference: ");
            msg.append(ref.toString());
        
            LogManager.severe(msg.toString(), e);
            throw new IOException(msg.toString());
        }
        return o;
    }
    

    private boolean exists(String referencePath, Locale l,
                           Connection connection)
        throws IOException
    {
        PreparedStatement selectStmt = null;
        ResultSet result = null;
        try
        {
            // Instantiate variables
            selectStmt = prepareSingleValidWhere(connection, 1, mSelectSql,
                                                 l, referencePath);

            result = selectStmt.executeQuery();

            // If we have a row, then it must exist
            return result.next();
        }
        catch(SQLException e)
        {
            try { result.close(); } catch (Exception e2) {}
            try { selectStmt.close(); } catch (Exception e2) {}
            
            SimpleStringBuffer msg = new SimpleStringBuffer(150);
            msg.append("DBPersonalizationManager: Failed to determine ");
            msg.append("existence for reference path: ");
            msg.append(referencePath);
        
            LogManager.severe(msg.toString(), e);
            throw new IOException(msg.toString());
        }
    }

    // Utility methods for creating SQL statements that use the various
    // 'flavours' of WHERE clause
    private PreparedStatement prepareSingleWhere(Connection connection,
                                                 int startIndex,
                                                 String SQL,
                                                 Locale l,
                                                 String path)
        throws SQLException
    {
        PreparedStatement stmt = connection.prepareStatement(SQL);
        int currIndex = startIndex;
        stmt.setString(currIndex++, STANDARD_PATH_TYPE);
        stmt.setString(currIndex++, getLanguagePath(l));
        stmt.setString(currIndex++, path);
        return stmt;
    }

    private PreparedStatement prepareSingleValidWhere(Connection connection,
                                                      int startIndex,
                                                      String SQL,
                                                      Locale l,
                                                      String path)
        throws SQLException
    {
        PreparedStatement stmt = connection.prepareStatement(SQL);
        int currIndex = startIndex;
        stmt.setString(currIndex++, STANDARD_PATH_TYPE);
        stmt.setString(currIndex++, getLanguagePath(l));
        stmt.setString(currIndex++, path);
        stmt.setString(currIndex++, getDataClass().getName());
        return stmt;
    }

    private PreparedStatement prepareMultiWhere(Connection connection,
                                                int startIndex,
                                                String SQL,
                                                String path)
        throws SQLException
    {
        PreparedStatement stmt = connection.prepareStatement(SQL);
        int currIndex = startIndex;
        stmt.setString(currIndex++, STANDARD_PATH_TYPE);
        stmt.setString(currIndex++, path);
        return stmt;
    }

    private PreparedStatement prepareMultiValidWhere(Connection connection,
                                                     int startIndex,
                                                     String SQL,
                                                     String path)
        throws SQLException
    {
        PreparedStatement stmt = connection.prepareStatement(SQL);
        int currIndex = startIndex;
        stmt.setString(currIndex++, STANDARD_PATH_TYPE);
        stmt.setString(currIndex++, path);
        stmt.setString(currIndex++, getDataClass().getName());
        return stmt;
    }

}
