spillhash.java

derby database source code.good for you.

/*

   Derby - Class org.apache.derbyTesting.functionTests.tests.lang.bug4356

   Copyright 2001, 2004 The Apache Software Foundation or its licensors, as applicable.

   Licensed under the Apache License, Version 2.0 (the "License");
   you may not use this file except in compliance with the License.
   You may obtain a copy of the License at

      http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.

 */

package org.apache.derbyTesting.functionTests.tests.lang;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.SQLException;
import java.sql.Types;
import java.util.BitSet;

import org.apache.derby.tools.ij;
import org.apache.derby.tools.JDBCDisplayUtil;

/**
 * Test BackingStoreHashtable spilling to disk.
 * BackingStoreHashtable is used to implement hash joins, distinct, scroll insensitive cursors,
 * outer joins, and the HAVING clause.
 */
public class SpillHash
{
    private static PreparedStatement joinStmt;
    private static PreparedStatement distinctStmt;
    private static final int LOTS_OF_ROWS = 10000;
    private static int errorCount = 0;
    
    public static void main (String args[]) 
    {
        try {
            /* Load the JDBC Driver class */
            // use the ij utility to read the property file and
            // make the initial connection.
            ij.getPropertyArg(args);
            Connection conn = ij.startJBMS();
            Statement stmt = conn.createStatement();

            for( int i = 0; i < prep.length; i++)
                stmt.executeUpdate( prep[i]);
            PreparedStatement insA = conn.prepareStatement( "insert into ta(ca1,ca2) values(?,?)");
            PreparedStatement insB = conn.prepareStatement( "insert into tb(cb1,cb2) values(?,?)");
            insertDups( insA, insB, initDupVals);

            joinStmt =
              conn.prepareStatement( "select ta.ca1, ta.ca2, tb.cb2 from ta, tb where ca1 = cb1");
            distinctStmt =
              conn.prepareStatement( "select distinct ca1 from ta");

            runStatements( conn, 0, new String[][][] {initDupVals});

            System.out.println( "Growing database.");
            
            // Add a lot of rows so that the hash tables have to spill to disk
            conn.setAutoCommit(false);
            for( int i = 1; i <= LOTS_OF_ROWS; i++)
            {
                insA.setInt(1, i);
                insA.setString(2, ca2Val(i));
                insA.executeUpdate();
                insB.setInt(1, i);
                insB.setString(2, cb2Val(i));
                insB.executeUpdate();

                if( (i & 0xff) == 0)
                    conn.commit();
            }
            conn.commit();
            insertDups( insA, insB, spillDupVals);
            conn.commit();

            conn.setAutoCommit(true);
            runStatements( conn, LOTS_OF_ROWS, new String[][][] {initDupVals, spillDupVals});
            
            conn.close();
        } catch (Exception e) {
            System.out.println("FAIL -- unexpected exception "+e);
            JDBCDisplayUtil.ShowException(System.out, e);
            e.printStackTrace();
            errorCount++;
        }
        if( errorCount == 0)
        {
            System.out.println( "PASSED.");
            System.exit(0);
        }
        else
        {
            System.out.println( "FAILED: " + errorCount + ((errorCount == 1) ? " error" : " errors"));
            System.exit(1);
        }
    } // end of main
    
    private static final String[] prep =
    {
        "create table ta (ca1 integer, ca2 char(200))",
        "create table tb (cb1 integer, cb2 char(200))",
        "insert into ta(ca1,ca2) values(null, 'Anull')",
        "insert into tb(cb1,cb2) values(null, 'Bnull')"
    };

    private static final String[][] initDupVals =
    {
        { "0a", "0b"},
        { "1a", "1b"},
        { "2a"}
    };
    private static final String[][] spillDupVals =
    {
        {},
        { "1c"},
        { "2b"},
        { "3a", "3b", "3c"}
    };

    private static int expectedMincc2( int cc1)
    {
        return 4*cc1;
    }

    private static int expectedMaxcc2( int cc1)
    {
        return expectedMincc2( cc1) + (cc1 & 0x3);
    }
    
    private static void insertDups( PreparedStatement insA, PreparedStatement insB, String[][] dupVals)
        throws SQLException
    {
        for( int i = 0; i < dupVals.length; i++)
        {
            insA.setInt(1, -i);
            insB.setInt(1, -i);
            String[] vals = dupVals[i];
            for( int j = 0; j < vals.length; j++)
            {
                insA.setString( 2, "A" + vals[j]);
                insA.executeUpdate();
                insB.setString( 2, "B" + vals[j]);
                insB.executeUpdate();
            }
        }
    } // end of insertDups
    
    private static String ca2Val( int col1Val)
    {
        return "A" + col1Val;
    }
    
    private static String cb2Val( int col1Val)
    {
        return "B" + col1Val;
    }
    
    private static void runStatements( Connection conn, int maxColValue, String[][][] dupVals)
        throws SQLException
    {
        runJoin( conn, maxColValue, dupVals);
        runDistinct( conn, maxColValue, dupVals);
        runCursor( conn, maxColValue, dupVals);
    }

    private static void runJoin( Connection conn, int maxColValue, String[][][] dupVals)
        throws SQLException
    {
        System.out.println( "Running join");
        int expectedRowCount = maxColValue; // plus expected duplicates, to be counted below
        ResultSet rs = joinStmt.executeQuery();
        BitSet joinRowFound = new BitSet( maxColValue);
        int dupKeyCount = 0;
        for( int i = 0; i < dupVals.length; i++)
        {
            if( dupVals[i].length > dupKeyCount)
                dupKeyCount = dupVals[i].length;
        }
        BitSet[] dupsFound = new BitSet[dupKeyCount];
        int[] dupCount = new int[ dupKeyCount];
        for( int i = 0; i < dupKeyCount; i++)
        {
            // count the number of rows with column(1) == -i
            dupCount[i] = 0;
            for( int j = 0; j < dupVals.length; j++)
            {
                if( i < dupVals[j].length)
                    dupCount[i] += dupVals[j][i].length;
            }
            dupsFound[i] = new BitSet(dupCount[i]*dupCount[i]);
            expectedRowCount += dupCount[i]*dupCount[i];
        }
        
        int count;
        for( count = 0; rs.next(); count++)
        {
            int col1Val = rs.getInt(1);
            if( rs.wasNull())
            {
                System.out.println( "Null in join column.");
                errorCount++;
                continue;
            }
            if( col1Val > maxColValue)