/**
 * Redistribution and use of this software and associated documentation
 * ("Software"), with or without modification, are permitted provided
 * that the following conditions are met:
 *
 * 1. Redistributions of source code must retain copyright
 *    statements and notices.  Redistributions must also contain a
 *    copy of this document.
 *
 * 2. Redistributions in binary form must reproduce the
 *    above copyright notice, this list of conditions and the
 *    following disclaimer in the documentation and/or other
 *    materials provided with the distribution.
 *
 * 3. The name "Exolab" must not be used to endorse or promote
 *    products derived from this Software without prior written
 *    permission of Exoffice Technologies.  For written permission,
 *    please contact info@exolab.org.
 *
 * 4. Products derived from this Software may not be called "Exolab"
 *    nor may "Exolab" appear in their names without prior written
 *    permission of Exoffice Technologies. Exolab is a registered
 *    trademark of Exoffice Technologies.
 *
 * 5. Due credit should be given to the Exolab Project
 *    (http://www.exolab.org/).
 *
 * THIS SOFTWARE IS PROVIDED BY EXOFFICE TECHNOLOGIES AND CONTRIBUTORS
 * ``AS IS'' AND ANY EXPRESSED OR IMPLIED WARRANTIES, INCLUDING, BUT
 * NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND
 * FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED.  IN NO EVENT SHALL
 * EXOFFICE TECHNOLOGIES OR ITS CONTRIBUTORS BE LIABLE FOR ANY DIRECT,
 * INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES
 * (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR
 * SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION)
 * HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT,
 * STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE)
 * ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED
 * OF THE POSSIBILITY OF SUCH DAMAGE.
 *
 * Copyright 2003 (C) Exoffice Technologies Inc. All Rights Reserved.
 *
 * $Id: V072toV076SchemaConverter.java,v 1.1 2004/11/26 01:51:16 tanderson Exp $
 */
package org.exolab.jms.tools.db.migration;

import java.io.ByteArrayInputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.exolab.jms.persistence.PersistenceException;
import org.exolab.jms.persistence.SQLHelper;
import org.exolab.jms.tools.db.Database;
import org.exolab.jms.tools.db.RDBMSTool;
import org.exolab.jms.tools.db.SchemaConverter;
import org.exolab.jms.tools.db.SchemaHelper;
import org.exolab.jms.tools.db.Table;


/**
 * A schema converter for converting from the 0.7.2 schema to the 0.7.6 schema
 *
 * @version     $Revision: 1.1 $ $Date: 2004/11/26 01:51:16 $
 * @author      <a href="mailto:tma@netspace.net.au">Tim Anderson</a>
 */
public class V072toV076SchemaConverter implements SchemaConverter {

    /**
     * The database connection
     */
    private Connection _connection;

    /**
     * The RDBMS tool
     */
    private RDBMSTool _tool;

    /**
     * The name of the users table
     */
    private static final String USERS_TABLE = "users";

    /**
     * The name of the messages table
     */
    private static final String MESSAGES_TABLE = "messages";

    /**
     * The name of the handles table
     */
    private static final String HANDLES_TABLE = "message_handles";


    /**
     * Construct a new <code>V072toV076SchemaConverter</code>
     *
     * @param connection the connection to use
     */
    public V072toV076SchemaConverter(Connection connection) {
        _connection = connection;
    }

    public void convert() throws PersistenceException {
        Database schema = SchemaHelper.getSchema();
        try {
            if (_connection.getAutoCommit()) {
                _connection.setAutoCommit(false);
            }
            _tool = new RDBMSTool(_connection);
        } catch (SQLException exception) {
            throw new PersistenceException(exception.getMessage());
        }

        try {
            convertMessagesTable(schema);
            convertHandlesTable(schema);
            createUsersTable(schema);
            SchemaHelper.setVersion(_connection, "V0.7.6");
            _connection.commit();
        } catch (PersistenceException exception) {
            SQLHelper.rollback(_connection);
            throw exception;
        } catch (SQLException exception) {
            SQLHelper.rollback(_connection);
            throw new PersistenceException(exception);
        }
    }

    /**
     * Converts the message identifier columns from long to string
     */
    private void convertMessagesTable(Database schema)
        throws PersistenceException, SQLException {
        Table table = SchemaHelper.getTable(schema, MESSAGES_TABLE);

        // create a temporary table to perform conversion
        Table tmpTable = new Table();
        String tmpName = "openjms_tmp_" + MESSAGES_TABLE;
        tmpTable.setName(tmpName);
        tmpTable.setAttribute(table.getAttribute());

        _tool.drop(tmpTable);
        _tool.create(tmpTable);

        // convert the messages table, inserting converted records into
        // the temporary table
        PreparedStatement select = _connection.prepareStatement(
            "select messageid, destinationid, priority, createTime,"
            + "expiryTime, processed, messageBlob from " + MESSAGES_TABLE);
        ResultSet set = select.executeQuery();
        while (set.next()) {
            long id = set.getLong(1);
            long destinationId = set.getLong(2);
            int priority = set.getInt(3);
            long createTime = set.getLong(4);
            long expiryTime = set.getLong(5);
            int processed = set.getInt(6);
            byte[] blob = set.getBytes(7);
            String messageId = "ID:" + id;
            migrateMessage(tmpName, messageId, destinationId, priority,
                createTime, expiryTime, processed, blob);
        }
        set.close();
        select.close();

        // recreate the destinations table
        _tool.drop(table);
        _tool.create(table);

        // copy the data from the temporary table into the messages table
        select = _connection.prepareStatement(
            "select messageid, destinationid, priority, createTime,"
            + "expiryTime, processed, messageBlob from " + tmpName);

        set = select.executeQuery();
        while (set.next()) {
            String messageId = set.getString(1);
            long destinationId = set.getLong(2);
            int priority = set.getInt(3);
            long createTime = set.getLong(4);
            long expiryTime = set.getLong(5);
            int processed = set.getInt(6);
            byte[] blob = set.getBytes(7);
            migrateMessage(tmpName, messageId, destinationId, priority,
                createTime, expiryTime, processed, blob);
        }
        set.close();
        select.close();

        // drop the temporary table
        _tool.drop(tmpTable);
    }

    private void migrateMessage(String table, String messageId,
                                long destinationId, int priority,
                                long createTime, long expiryTime,
                                int processed, byte[] blob)
        throws SQLException {
        PreparedStatement insert = null;
        try {
            // create, populate and execute the insert
            insert = _connection.prepareStatement(
                "insert into " + table + " values (?,?,?,?,?,?,?)");
            insert.setString(1, messageId);
            insert.setLong(2, destinationId);
            insert.setInt(3, priority);
            insert.setLong(4, createTime);
            insert.setLong(5, expiryTime);
            insert.setInt(6, processed);
            insert.setBinaryStream(7, new ByteArrayInputStream(blob),
                blob.length);

            // execute the insert
            if (insert.executeUpdate() != 1) {
                throw new SQLException("Failed to add message=" + messageId);
            }
        } finally {
            SQLHelper.close(insert);
        }
    }

    /**
     * Converts the message identifier columns from long to string
     */
    private void convertHandlesTable(Database schema)
        throws PersistenceException, SQLException {

        Table table = SchemaHelper.getTable(schema, HANDLES_TABLE);

        // create a temporary table to perform conversion
        Table tmpTable = new Table();
        String tmpName = "openjms_tmp_" + HANDLES_TABLE;
        tmpTable.setName(tmpName);
        tmpTable.setAttribute(table.getAttribute());

        _tool.drop(tmpTable);
        _tool.create(tmpTable);

        // convert the messages_handles table, inserting converted records into
        // the temporary table
        PreparedStatement select = _connection.prepareStatement(
            "select messageid, destinationid, consumerid, priority, "
            + " acceptedTime, sequenceNumber, expiryTime, delivered"
            + " from " + HANDLES_TABLE);
        ResultSet set = select.executeQuery();
        while (set.next()) {
            long messageId = set.getLong(1);
            long destinationId = set.getLong(2);
            long consumerId = set.getLong(3);
            int priority = set.getInt(4);
            long acceptedTime = set.getLong(5);
            long sequenceNo = set.getLong(6);
            long expiryTime = set.getLong(7);
            int delivered = set.getInt(8);
            migrateHandle(tmpName, messageId, destinationId, consumerId,
                priority, acceptedTime, sequenceNo, expiryTime,
                delivered);
        }
        set.close();
        select.close();

        // recreate the destinations table
        _tool.drop(table);
        _tool.create(table);

        // copy the data from the temporary table into the messages table
        select = _connection.prepareStatement(
            "insert into " + HANDLES_TABLE + " select * from " +
            tmpName);
        select.executeQuery();
        select.close();

        // drop the temporary table
        _tool.drop(tmpTable);
    }

    private void migrateHandle(String table, long messageId,
                               long destinationId, long consumerId,
                               int priority, long acceptedTime,
                               long sequenceNo, long expiryTime,
                               int delivered) throws SQLException {
        PreparedStatement insert = null;
        try {
            // create, populate and execute the insert
            insert = _connection.prepareStatement(
                "insert into " + table + " values (?,?,?,?,?,?,?,?)");
            insert.setString(1, "ID:" + messageId);
            insert.setLong(2, destinationId);
            insert.setLong(3, consumerId);
            insert.setInt(4, priority);
            insert.setLong(5, acceptedTime);
            insert.setLong(6, sequenceNo);
            insert.setLong(7, expiryTime);
            insert.setInt(8, delivered);

            // execute the insert
            if (insert.executeUpdate() != 1) {
                throw new SQLException("Failed to add handle=" + messageId);
            }
        } finally {
            SQLHelper.close(insert);
        }
    }

    private void createUsersTable(Database schema)
        throws PersistenceException {
        Table table = SchemaHelper.getTable(schema, USERS_TABLE);
        _tool.create(table);
    }


} //-- V072toV076SchemaConverter