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

import java.io.*;
import java.sql.*;
import java.util.*;
import javax.servlet.*;
import oracle.portal.provider.v1.*;
import oracle.webdb.utils.SimpleStringBuffer;
import oracle.portal.utils.v1.ConnectionManager;
import oracle.portal.utils.v1.ConnectionManagerException;
import oracle.portal.utils.v1.MultiByteEncoder;

/**
 * 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>). If your data
 * class implements the {@link oracle.portal.provider.v1.PersonalizationObject}
 * interface (recommended), it will be responsible for reading and writing its
 * own content through its <code>read()</code> and <code>write()</code>
 * methods. Otherwise, it is assumed that the data class implements the
 * <code>Serializable</code> interface, and can therefore be saved and restored
 * through Java serialization. For maximum extensibility, it is recommended
 * that you use {@link
 * oracle.portal.provider.v1.NameValuePersonalizationObject} as a basis for
 * your data class.<p>
 *
 * The persisted data is stored in a database table; one row per customization.
 * Connections to the database are retrieved from a 'pool', managed by {@link
 * oracle.portal.utils.v1.ConnectionManager}; therefore, you must ensure that
 * you have set up a configuration file for <code>ConnectionManager</code> and
 * that your JVM knows its whereabouts. The name of the pooled connection to be
 * used must be registered with a call to {@link #setConnection(String)}, or
 * through a <code>&lt;connection><i>connectionName</i>&lt;/connection></code>
 * declaration.<p>
 *
 * 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)}, 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/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 implements 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;

    private Class mDataClass;
    private String mConnectionName;
    private boolean mUseSerialization = false;
    private String mTableName = DEFAULT_TABLE_NAME;
    
    /**
     * Null Constructor
     */
    public DBPersonalizationManager() throws ConnectionManagerException
    {
        initSqlStatements();
    }

    /**
     *  Sets the class name of the data objects this instance manages. This
     *  class must implement <code>PersonalizationObject</code> or
     *  <code>Serializable</code>. It is recommended that you subclass your
     *  data object from <code>NameValuePersonalizationObject</code>.
     *
     *  @param className the class name for that class that represents the data
     *  to be managed.
     */
    public void setDataClass(String className) throws PersonalizationException

    {
        Object template = null;
        try
        {
            mDataClass = Class.forName(className);
            template = mDataClass.newInstance();
        }
        catch (ClassNotFoundException e)
        {
          throw new PersonalizationException(e);
        }
        catch (IllegalAccessException e)
        {
          throw new PersonalizationException(e);
        }
        catch (InstantiationException e)
        {
          throw new PersonalizationException(e);
        }

        // Decide up front whether we are going to have to use Java
        // Serialization for the data objects, rather than the
        // PersonalizationObject interface, for backwards compatibility
        if(!(template instanceof PersonalizationObject))
        {
            if(template instanceof Serializable)
            {
                mUseSerialization = true;
            }
            // If the Object doesn't even implement Serializable then we
            // can't use it at all
            else
            {
                throw new PersonalizationException("PortletPersonalizationManager Data Class does not implement supported interface.");
            }
        }
    }

    /**
     * Sets the name of the connection pool, managed by {@link
     * ConnectionManager} from which this <code>DBPersonalizationManager</code>
     * should retrieve its database connections.
     *
     * @param connection a connection pool name
     * @exception PersonalizationException if a connection pool of the given
     * name cannot be initialized by {@link ConnectionManager}
     */
    public void setConnection(String connection) throws PersonalizationException
    {
        mConnectionName = connection;

        //Let's 'warm up' the appropriate connection in the connection pool
        try
        {
            releaseConnection(getConnection());
        }
        catch (IOException ioe)
        {
            throw new PersonalizationException(ioe);
        }
    }

    /**
     * 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 a new <code>DBPersonalizationManager</code> instance. This
     *  method should be invoked on each of a Provider's
     *  PortletPersonalizationManagers whenever the Provider instance is
     *  initialized on startup (by the Provider's <code>init()</code>
     *  method).<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 initInstance(Portlet p)
    {
    }

    /**
     *  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(Portlet 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(Portlet p) throws AccessControlException, IOException
    {
        // don't do anything if the portlet doesn't support customizations
        if (!p.hasShowEdit() && !p.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(p) + WILDCARD_CHAR);

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

            // Commit the change
            connection.commit();
        }
        catch (SQLException e)
        {
            // Make sure we don't leave any transactions open
            try
            {
              connection.rollback();
            }
            catch (SQLException e1)
            {
            }
            throw new IOException (e.getMessage());
        }
        finally
        {
            if(deleteAllStmt != null)
            {
                closeStatement(deleteAllStmt);
            }
            releaseConnection(connection);
        }

    }

    /**
     * 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 if the class implements
     * PersonalizationObject or CustomizationObject. 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
        Object o = createDataObject(ref);

        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 Object representing the initial values for this instance
     * (should implement PersonalizationObject or Serializable).
     * @param user the user attempting this operation.  The manager is expected
     * to authorize the user before completing this operation.
     */
    public void create(PortletReference ref, Object o, ProviderUser user)
        throws PortletAlreadyExistsException, AccessControlException,
               IOException
    {
        checkAuthorization(ref, user);
        Connection connection = getConnection();
        try
        {
            writeDataObject(o, ref, connection, false);
        }
        // This should never get raised when called in this mode
        catch (PortletNotFoundException pnfe)
        {
        }
        finally
        {
            releaseConnection(connection);
        }

    }

    /**
     *  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
    {
        checkAuthorization(ref, user);

        Connection connection = null;
        try
        {
            connection = getConnection();
            return(exists(getReferencePath(ref), ref.getLocale(), connection));
        }
        // We will just have to ignore any exceptions that occur at this stage
        // and signal that a customization doesn't exist
        catch (IOException e)
        {
        }
        finally
        {
            if(connection != null)
            {
                releaseConnection(connection);
            }
        }
        return false;
    }

    /**
     *  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 Object read(PortletReference ref, ProviderUser user)
        throws PortletNotFoundException, AccessControlException, IOException
    {

        checkAuthorization(ref, user);

        Connection connection = getConnection();
        Object o = null;
        try
        {
            o = readDataObject(ref, connection);
        }
        finally
        {
            releaseConnection(connection);
        }

        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 or Serializable).
     *  @param user the user attempting this operation.  The manager is expected
     *  to authorize the user before completing this operation.
     */
    public void write(PortletReference ref, Object o, ProviderUser user)
        throws java.io.IOException, PortletNotFoundException, AccessControlException
    {

        checkAuthorization(ref, user);

        Connection connection = getConnection();
        try
        {
            writeDataObject(o, ref, connection, true);
        }
        // This should never get raised when called in this mode
        catch (PortletAlreadyExistsException paee)
        {
        }
        finally
        {
            releaseConnection(connection);
        }
    }

    /**
     *  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
    {
        checkAuthorization(ref, user);

        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));

                deleteStmt.executeUpdate();
            }

            connection.commit();
        }
        catch(SQLException e)
        {
            // Make sure we don't leave any transactions open
            try
            {
              connection.rollback();
            }
            catch (SQLException e1)
            {
            }
            throw new IOException (e.getMessage());
        }
        finally
        {
            if(deleteStmt != null)
            {
                closeStatement(deleteStmt);
            }
            releaseConnection(connection);
        }
    }

    /**
     *  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
    {
        checkAuthorization(from, user);

        Connection connection = getConnection();
        PreparedStatement deleteStmt = null;
        PreparedStatement selectStmt = null;
        PreparedStatement insertStmt = null;
        String fromPath = getReferencePath(from);
        String toPath = getReferencePath(to);
        String toSearch = getSearchPath(to);
        Locale l = from.getLocale();
        boolean isDefault = from.isDefault();

        // Use try - finally block to make sure we release the connection after
        // any exit point from this code block
        try
        {
            if (!exists(fromPath, l, connection))
                throw new PortletNotFoundException();

            // from and to should either be both default references or both
            // non-default references
            if(isDefault != to.isDefault())
            {
                throw new AccessControlException("PortletReference mismatch");
            }

            try
            {
                ResultSet result = null;

                // 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();
                }                
            }
            catch (SQLException e)
            {
                // Make sure we don't leave any transactions open
                try
                {
                    connection.rollback();
                }
                catch (SQLException e1)
                {
                }
                throw new IOException(e.getMessage());
            }
        } // end try {
        finally
        {
            if(deleteStmt != null)
            {
                closeStatement(deleteStmt);
            }
            if(selectStmt != null)
            {
                closeStatement(selectStmt);
            }
            if(insertStmt != null)
            {
                closeStatement(insertStmt);
            }
            releaseConnection(connection);
        }
    }

    // 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
    {
        Connection conn = null;
        try
        {
            conn = ConnectionManager.getConnection(mConnectionName);
            if(conn == null)
            {
                throw new IOException("Connection Manager does not know about a connection called '" +
                                      mConnectionName + "'");
            }
            // LOB locators don't persist over transactions, so we have to disable
            // auto-commit
            if(conn.getAutoCommit())
            {
                conn.setAutoCommit(false);
            }
        }
        catch (SQLException e)
        {
            throw new IOException("Database error initialising connection '"+
                                  mConnectionName + "': " + e.getMessage());
        }
        catch (ConnectionManagerException cme)
        {
            throw new IOException(cme.getMessage());
        }
        return conn;
    }

    private void releaseConnection(Connection conn)
    {
        ConnectionManager.releaseConnection(mConnectionName, conn);
    }

    private void closeStatement(Statement s)
    {
        try
        {
            s.close();
        }
        catch(SQLException e)
        {
        }
    }

    private void checkAuthorization(PortletReference ref, ProviderUser user) throws AccessControlException
    {
        PortletSecurityManager security = ref.getPortlet().getSecurityManager();

        try
        {
            if (security != null && !security.hasAccess(ref, user))
            {
                throw new AccessControlException();
            }
        }
        catch(PortletNotFoundException e)
        {
            // unexpected
            throw new AccessControlException();
        }
    }

    // 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.getName());
        path.append(SEPARATOR_CHAR);
        path.append(ref.getPortlet().getId());
        path.append(SEPARATOR_CHAR);
        path.append(ref.getProvider().getProviderId());
        return path.toString();
    }

    // Derive the reference path key, with MOST selective values first
    private String getReferencePath(PortletReference ref)
    {
        SimpleStringBuffer path = new SimpleStringBuffer();
        // Only include username if not a default
        if (!ref.isDefault())
        {
            String userName = MultiByteEncoder.encodeUTF(ref.getUserCustomization().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(Portlet p)
    {
        SimpleStringBuffer path = new SimpleStringBuffer();
        path.append(p.getProvider().getProviderId());
        path.append(SEPARATOR_CHAR);
        path.append(p.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(getSearchPrefix(ref.getPortlet()));
        path.append(ref.getName());
        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 = MultiByteEncoder.encodeUTF(ref.getUserCustomization().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,
                            Connection connection,
                            boolean shouldExist,
                            byte[] bytes)
        throws SQLException, PortletAlreadyExistsException,
               PortletNotFoundException
    {
        PreparedStatement selectLockStmt = null;
        PreparedStatement updateStmt = null;
        OutputStream os = null;
        String out = getReferencePath(ref);
        try
        {
            selectLockStmt = prepareSingleWhere(connection, 1, mSelectLockSql,
                                                ref.getLocale(), out);
            // Lock the row, if it exists
            ResultSet 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(mDataClass.getName());

            // Validate that the row exists when it should
            if(shouldExist)
            {
                if(!validRow)
                {
                    throw new PortletNotFoundException();
                }
            }
            else
            {
                if(validRow)
                {
                    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, mDataClass.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();
        }
        finally
        {
            if(selectLockStmt != null)
            {
                closeStatement(selectLockStmt);
            }
            if(updateStmt != null)
            {
                closeStatement(updateStmt);
            }
        }
    }

    // Support saving and restoring of data objects implementing either
    // PersonalizationObject or Serializable

    private void writeDataObject(Object o, PortletReference ref,
                                 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
        {
            if(!mUseSerialization)
            {
                ((PersonalizationObject)o).write(bos);
            }
            else
            {
                ObjectOutputStream oos = new ObjectOutputStream(bos);
                oos.writeObject(o);
            }
            bos.close();
            writeBytes(ref, connection, shouldExist, bos.toByteArray());
            bos = null;
            connection.commit();
        }
        catch (Exception e)
        {
            try
            {
                if (bos != null)
                    bos.close();
                connection.rollback();
            }
            catch (Exception e2)
            {
                // do nothing
            }
            if(e instanceof PortletAlreadyExistsException)
            {
                throw (PortletAlreadyExistsException)e;
            }
            else if(e instanceof PortletNotFoundException)
            {
                throw (PortletNotFoundException)e;
            }
            else
            {
                ref.getProvider().log("Error creating LONG with path '" +
                                      getReferencePath(ref) +
                                      "' : " + e.getMessage());
                throw new IOException(e.getMessage());
            }
        }
    }

    private Object readDataObject(PortletReference ref, Connection connection)
        throws IOException, PortletNotFoundException
    {
        Object o = null;
        InputStream is = null;
        String in = getReferencePath(ref);
        PreparedStatement selectStmt = null;
        try
        {
            selectStmt = prepareSingleValidWhere(connection, 1, mSelectSql,
                                                 ref.getLocale(), in);
            ResultSet result = selectStmt.executeQuery();
            if(result.next())
            {
                is = result.getBinaryStream(1);
            }
            else
            {
                throw new PortletNotFoundException();
            }

            if(!mUseSerialization)
            {
                // Get a brand new data object of the appropriate class
                o = createDataObject(ref);
                ((PersonalizationObject)o).read(is);
            }
            else
            {
                ObjectInputStream ois = new ObjectInputStream(is);
                o = ois.readObject();
            }
        }
        catch(PortletNotFoundException pnfe)
        {
            throw pnfe;
        }
        catch(Exception e)
        {
            ref.getProvider().log("Error reading from LONG with path '" +
                                  getReferencePath(ref) +
                                  "' : " + e.getMessage());
            throw new IOException(e.getMessage());
        }
        finally
        {
            try
            {
                if (is != null) is.close();
            }
            catch (Exception e)
            {
                // do nothing
            }
            if(selectStmt != null)
            {
                closeStatement(selectStmt);
            }
        }
        return o;
    }

    private Object createDataObject(PortletReference ref) throws IOException
    {
        Object o = null;
        try
        {
            o = mDataClass.newInstance();
            if(!mUseSerialization)
            {
                PersonalizationObject po = (PersonalizationObject)o;
                po.init(ref);
                po.setReferencePath(getReferencePath(ref));
            }
            // Provide backwards compatibility by supporting data objects
            // implementing old CustomizationObject interface
            else if(o instanceof CustomizationObject)
            {
                ((CustomizationObject)o).init(ref);
            }
        }
        catch(Exception e)
        {
            ref.getProvider().log("DBPersonalizationManager Error instantiating " +
                                  mDataClass.getName() + " : " + e.getMessage());
            throw new IOException(e.getMessage());
        }
        return o;
    }

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

            ResultSet result = selectStmt.executeQuery();

            // If we have a row, then it must exist
            return result.next();
        }
        catch(SQLException e)
        {
            throw new IOException(e.getMessage());
        }
        finally
        {
            if(selectStmt != null)
            {
                closeStatement(selectStmt);
            }
        }
    }

    // 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++, mDataClass.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++, mDataClass.getName());
        return stmt;
    }

}
