/**
 * This class demonstrates a possible issue with Derby backing store.
 */
public class DerbyHoldabilityTest
{
    /** The JDBC URL used to connect to Derby */
    private static final String URL = "jdbc:derby:C:\\DERBY\\" + System.currentTimeMillis();

    /** The database connection used in this test */
    private java.sql.Connection m_connection;

    /**
     * Runs the test to demonstrate a possible issue with Derby backing store and holdability.
     *
     * This issue can be duplicated as follows:
     *
     * <ol>
     * <li>Open an embedded Derby database, specifying holdability of HOLD_CURSORS_OVER_COMMIT.</li>
     * <li>Create two tables with a sufficient number and length of columns.</li>
     * <li>Populate both tables with many rows.</li>
     * <li>Open a result set on the join of the two tables.</li>
     * <li>In another statement on the same connection, do something that commits a transaction.</li>
     * <li>Iterate over the result set of the first join, and observe an eventual <code>SQLException</code>
     *     indicating that a closed heap container was accessed.</li>
     * </ol>
     * @param args ignored
     */
    public static void main(String args[])
    {
        try
        {
            DerbyHoldabilityTest test = new DerbyHoldabilityTest();

            // This is the default behavior, but let's just make sure
            test.m_connection.setHoldability(java.sql.ResultSet.HOLD_CURSORS_OVER_COMMIT);
            test.m_connection.setAutoCommit(true);

            // Build two tables with sufficient data.  Various combinations of column numbers, row count, and column
            // width can be used.  The threshold seems to be hit about when the product of the three is a bit over
            // 512,000
            test.buildTables(25, 500, 50);

            // Create a result set on the join of the two dummy tables
            final java.sql.PreparedStatement ps = test.m_connection.prepareStatement("select * from table1, table2 where table1.tableID = table2.tableID");
            ps.execute();
            final java.sql.ResultSet rs = ps.getResultSet();

            // Do something else unrelated that causes a commit
            test.createTable("table3", 10, 100);

            // After some number of rows, this dump will throw an exception
            test.dumpResultSet(rs);
        }
        catch (Throwable t)
        {
            t.printStackTrace();
        }
    }

    /**
     * Constructs a new object to test Derby holdability and backing store.
     */
    private DerbyHoldabilityTest()
    {
        try
        {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver").newInstance();

            m_connection = java.sql.DriverManager.getConnection(URL + ";create=true");
        }
        catch (Throwable t)
        {
            t.printStackTrace();
        }
    }

    /**
     * Dumps a representation of the passed result set for debugging inspection purposes.
     * @param rs the result set to dump
     */
    public void dumpResultSet(java.sql.ResultSet rs)
    {
        try
        {
            final int numCols = rs.getMetaData().getColumnCount();

            while (rs.next())
            {
                for (int i = 1; i<= numCols; i++)
                {
                    // output just enough to prove we are getting the rows
                    String dbValue = rs.getString(i);
                    String output = dbValue.startsWith("x") ? dbValue.substring(0, 1) + ' ' : dbValue + ' ';
                    System.out.print(output);
                }
                System.out.println();
            }
            System.out.println();
        }
        catch (java.sql.SQLException e)
        {
            System.out.println("SQLException dumping result set: " + e);
        }
    }

    /**
     * Builds and populates two dummy tables of the same size and shape, named <code>table1</code> and
     * <code>table2</code>.
     * @param numCols The number of extra VARCHAR columns to put in the dummy tables
     * @param numRows the number of rows to insert into the tables
     * @param colWidth the maximum length of the extra VARCHAR columns
     * @throws java.sql.SQLException thrown on any database error
     */
    private void buildTables(int numCols, int numRows, int colWidth) throws java.sql.SQLException
    {
        StringBuffer sql = new StringBuffer();

        createTable("table1", numCols, colWidth);
        insertTable("table1", numCols, colWidth - 1, numRows);

        createTable("table2", numCols, colWidth);
        insertTable("table2", numCols, colWidth - 1, numRows);
    }

    /**
     * Creates a dummy test table with one integer primary key column and some extra VARCHAR columns.
     * @param name the name of the table to create
     * @param numExtraCols the number of extra VARCHAR columns to create
     * @param colWidth the width of the extra VARCHAR columns
     * @throws java.sql.SQLException thrown on a database error
     */
    private void createTable(String name, int numExtraCols, int colWidth) throws java.sql.SQLException
    {
        StringBuffer sql = new StringBuffer();

        sql.append("CREATE TABLE ");
        sql.append(name);
        sql.append("( tableID int primary key");

        for (int i = 1; i <= numExtraCols; i++)
        {
            sql.append(", ");
            sql.append("column");
            sql.append(i);
            sql.append(" VARCHAR(");
            sql.append(colWidth);
            sql.append(")");
        }
        sql.append(")");

        java.sql.Statement stmt = m_connection.createStatement();
        stmt.execute(sql.toString());
        stmt.close();
    }

    /**
     * Inserts dummy rows into a test table created from the <code>createTable</code> method.
     * @param name the name of the insert table
     * @param numExtraCols the number of extra columns in the table, in addition to the row ID number.
     * @param insertWidth the length of dummy strings to put in each VARCHAR column of the table
     * @param numRows the number of dummy rows to insert
     * @throws java.sql.SQLException thrown on a database error
     */
    private void insertTable(String name, int numExtraCols, int insertWidth, int numRows) throws java.sql.SQLException
    {
        for (int i = 1; i <= numRows; i++)
        {
            StringBuffer sql = new StringBuffer();

            sql.append("insert into ");
            sql.append(name);
            sql.append(" values (");
            sql.append(i);

            for (int j = 1; j <= numExtraCols; j++)
            {
                sql.append(", '");
                sql.append(getString(insertWidth));
                sql.append("'");
            }
            sql.append(")");

            java.sql.Statement stmt = m_connection.createStatement();
            stmt.execute(sql.toString());
            stmt.close();

            if (i % 100 == 0)
            {
                System.out.println("Inserting row " + i + " into " + name);
            }
        }
    }

    /**
     * Helper method that returns a dummy string for inserting into a table.
     * @param length the desired length of the returned string
     * @return Returns a string of length 'x' characters.
     */
    private String getString(int length)
    {
        StringBuffer buf = new StringBuffer(length);

        for (int i = 0; i < length; i++)
        {
            buf.append('x');
        }

        return buf.toString();
    }
}