Package org.voltdb.regressionsuites

Source Code of org.voltdb.regressionsuites.TestOrderBySuite

/* This file is part of VoltDB.
* Copyright (C) 2008-2010 VoltDB L.L.C.
*
* Permission is hereby granted, free of charge, to any person obtaining
* a copy of this software and associated documentation files (the
* "Software"), to deal in the Software without restriction, including
* without limitation the rights to use, copy, modify, merge, publish,
* distribute, sublicense, and/or sell copies of the Software, and to
* permit persons to whom the Software is furnished to do so, subject to
* the following conditions:
*
* The above copyright notice and this permission notice shall be
* included in all copies or substantial portions of the Software.
*
* THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
* EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
* MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT.
* IN NO EVENT SHALL THE AUTHORS BE LIABLE FOR ANY CLAIM, DAMAGES OR
* OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE,
* ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR
* OTHER DEALINGS IN THE SOFTWARE.
*/

package org.voltdb.regressionsuites;

import java.io.IOException;
import java.util.ArrayList;
import java.util.Collections;

import org.voltdb.BackendTarget;
import org.voltdb.VoltProcedure;
import org.voltdb.VoltTable;
import org.voltdb.VoltType;
import org.voltdb.client.Client;
import org.voltdb.client.NoConnectionsException;
import org.voltdb.client.ProcCallException;
import org.voltdb.client.SyncCallback;
import org.voltdb.compiler.VoltProjectBuilder;
import org.voltdb.regressionsuites.orderbyprocs.InsertO1;
import org.voltdb.regressionsuites.orderbyprocs.InsertO3;
import org.voltdb.regressionsuites.orderbyprocs.OrderByNonIndex;
import org.voltdb.regressionsuites.orderbyprocs.OrderByOneIndex;

public class TestOrderBySuite extends RegressionSuite {

    /*
     * CREATE TABLE O1 ( PKEY INTEGER, A_INT INTEGER, A_INLINE_STR VARCHAR(10),
     * A_POOL_STR VARCHAR(1024), PRIMARY_KEY (PKEY) );
     */
    @SuppressWarnings("unchecked")
    public static final Class<? extends VoltProcedure> PROCEDURES[] = (Class<? extends VoltProcedure>[])new Class<?>[] {
        InsertO1.class,
        InsertO3.class,
        OrderByNonIndex.class,
        OrderByOneIndex.class
    };

    ArrayList<Integer> a_int = new ArrayList<Integer>();
    ArrayList<String> a_inline_str = new ArrayList<String>();
    ArrayList<String> a_pool_str = new ArrayList<String>();

    public final static String bigString = "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ" +
                                    "ABCDEFGHIJ";

    /** add 20 shuffled rows
     * @throws InterruptedException */
    private void load(Client client) throws NoConnectionsException, ProcCallException, IOException, InterruptedException {
        int pkey = 0;
        a_int.clear();
        a_inline_str.clear();
        a_pool_str.clear();

        // if you want to test synchronous latency, this
        //  is a good variable to change
        boolean async = true;

        for (int i=0; i < 20; i++) {
            a_int.add(i);
            a_inline_str.add("a_" + i);
            a_pool_str.add(bigString + i);
        }

        Collections.shuffle(a_int);
        Collections.shuffle(a_inline_str);
        Collections.shuffle(a_pool_str);

        for (int i=0; i < 20; i++) {
            SyncCallback cb = new SyncCallback();
            client.callProcedure(cb,
                    "InsertO1",
                    pkey++,
                    a_int.get(i),
                    a_inline_str.get(i),
                    a_pool_str.get(i));

            if (!async) {
                cb.waitForResponse();
                VoltTable vt = cb.getResponse().getResults()[0];
                assertTrue(vt.getRowCount() == 1);
            }
        }

        client.drain();
    }

    private void loadInOrder(Client client) throws NoConnectionsException,
                                           ProcCallException,
                                           IOException, InterruptedException {
        // if you want to test synchronous latency, this
        //  is a good variable to change
        boolean async = true;

        for (int i = 0; i < 100; i++) {
            SyncCallback cb = new SyncCallback();
            client.callProcedure(cb, "InsertO3", 3, i, i, i);

            if (!async) {
                cb.waitForResponse();
                VoltTable vt = cb.getResponse().getResults()[0];
                assertTrue(vt.getRowCount() == 1);
            }
        }
    }

    private void loadWithDupes(Client client) throws NoConnectionsException, ProcCallException, IOException, InterruptedException {
        client.callProcedure(new SyncCallback(), "InsertO1", new Long(1), new Long(1), "Alice", "AlphaBitters");
        client.callProcedure(new SyncCallback(), "InsertO1", new Long(2), new Long(2), "Alice", "CrunchTubers");
        client.callProcedure(new SyncCallback(), "InsertO1", new Long(3), new Long(3), "Alice", "BetaBuildingBlocks");

        client.callProcedure(new SyncCallback(), "InsertO1", new Long(4), new Long(1), "Betty", "CrunchTubers");
        client.callProcedure(new SyncCallback(), "InsertO1", new Long(5), new Long(2), "Betty", "AlphaBitters");
        client.callProcedure(new SyncCallback(), "InsertO1", new Long(6), new Long(3), "Betty", "BetaBuildingBlocks");

        client.callProcedure(new SyncCallback(), "InsertO1", new Long(7), new Long(1), "Chris", "BetaBuildingBlocks");
        client.callProcedure(new SyncCallback(), "InsertO1", new Long(8), new Long(2), "Chris", "CrunchTubers");
        client.callProcedure(new SyncCallback(), "InsertO1", new Long(9), new Long(3), "Chris", "AlphaBitters");

        client.drain();
    }

    /** select * from T order by A ASC
     * @throws IOException
     * @throws ProcCallException
     * @throws NoConnectionsException
     * @throws InterruptedException */
    public void testOrderBySingleColumnAscending() throws NoConnectionsException, ProcCallException, IOException, InterruptedException {
        VoltTable vt;
        Client client = this.getClient();
        load(client);

        // sort column of ints ascending
        vt = client.callProcedure("@AdHoc", "select * from O1 order by A_INT ASC").getResults()[0];
        assertTrue(vt.getRowCount() == 20);
        int it = 0;
        while (vt.advanceRow()) {
            Integer key = (Integer) vt.get(0, VoltType.INTEGER);
            Integer a = (Integer) vt.get(1, VoltType.INTEGER);
            String b = (String) vt.get(2, VoltType.STRING);
            String c = (String) vt.get(3, VoltType.STRING);

            int pos = a_int.indexOf(a);   // offset of this value in unsorted data

            assertEquals(it, a.intValue());     // a should be order 1, 2, 3..
            assertEquals(pos, key.intValue())// side-effect of insertion method
            assertEquals(b, a_inline_str.get(pos));
            assertEquals(c, a_pool_str.get(pos));

            it++;
        }

        // sort column of inlined strings ascending
        vt = client.callProcedure("@AdHoc", "select * from O1 order by A_INLINE_STR ASC").getResults()[0];
        assertTrue(vt.getRowCount() == 20);
        String lastString = "a";
        while (vt.advanceRow()) {
            Integer key = (Integer) vt.get(0, VoltType.INTEGER);
            Integer a = (Integer) vt.get(1, VoltType.INTEGER);
            String b = (String) vt.get(2, VoltType.STRING);
            String c = (String) vt.get(3, VoltType.STRING);

            assertTrue(lastString.compareTo(b) < 0)// always ascending
            lastString = b;

            int pos = a_inline_str.indexOf(b);   // offset of this value in unsorted data
            assertEquals(pos, key.intValue());   // side-effect of insertion method
            assertEquals(a, a_int.get(pos));     // retrieved value matches at index in unsorted data
            assertEquals(c, a_pool_str.get(pos));
        }


        // sort column of non-inlined strings ascending
        vt = client.callProcedure("@AdHoc", "select * from O1 order by A_POOL_STR ASC").getResults()[0];
        assertTrue(vt.getRowCount() == 20);
        lastString = "A";
        while (vt.advanceRow()) {
            Integer key = (Integer) vt.get(0, VoltType.INTEGER);
            Integer a = (Integer) vt.get(1, VoltType.INTEGER);
            String b = (String) vt.get(2, VoltType.STRING);
            String c = (String) vt.get(3, VoltType.STRING);

            assertTrue(lastString.compareTo(c) < 0)// always ascending
            lastString = c;

            int pos = a_pool_str.indexOf(c);   // offset of this value in unsorted data
            assertEquals(pos, key.intValue());   // side-effect of insertion method
            assertEquals(a, a_int.get(pos));     // retrieved value matches at index in unsorted data
            assertEquals(b, a_inline_str.get(pos));
        }
    }

    public void testOrderBySingleColumnDescending() throws NoConnectionsException, ProcCallException, IOException, InterruptedException {
        VoltTable vt;
        Client client = this.getClient();
        load(client);

        // sort column of ints descending
        vt = client.callProcedure("@AdHoc", "select * from O1 order by A_INT DESC").getResults()[0];
        assertTrue(vt.getRowCount() == 20);
        int it = 19;
        while (vt.advanceRow()) {
            Integer key = (Integer) vt.get(0, VoltType.INTEGER);
            Integer a = (Integer) vt.get(1, VoltType.INTEGER);
            String b = (String) vt.get(2, VoltType.STRING);
            String c = (String) vt.get(3, VoltType.STRING);

            int pos = a_int.indexOf(a);   // offset of this value in unsorted data

            assertEquals(it, a.intValue());     // a should be order 1, 2, 3..
            assertEquals(pos, key.intValue())// side-effect of insertion method
            assertEquals(b, a_inline_str.get(pos));
            assertEquals(c, a_pool_str.get(pos));

            it--;
        }

        // sort column of inlined strings descending
        vt = client.callProcedure("@AdHoc", "select * from O1 order by A_INLINE_STR DESC").getResults()[0];
        assertTrue(vt.getRowCount() == 20);
        String lastString = "z";
        while (vt.advanceRow()) {
            Integer key = (Integer) vt.get(0, VoltType.INTEGER);
            Integer a = (Integer) vt.get(1, VoltType.INTEGER);
            String b = (String) vt.get(2, VoltType.STRING);
            String c = (String) vt.get(3, VoltType.STRING);

            assertTrue(lastString.compareTo(b) > 0)// always descending
            lastString = b;

            int pos = a_inline_str.indexOf(b);   // offset of this value in unsorted data
            assertEquals(pos, key.intValue());   // side-effect of insertion method
            assertEquals(a, a_int.get(pos));     // retrieved value matches at index in unsorted data
            assertEquals(c, a_pool_str.get(pos));
        }


        // sort column of non-inlined strings ascending
        vt = client.callProcedure("@AdHoc", "select * from O1 order by A_POOL_STR DESC").getResults()[0];
        assertTrue(vt.getRowCount() == 20);
        lastString = bigString + "99";
        while (vt.advanceRow()) {
            Integer key = (Integer) vt.get(0, VoltType.INTEGER);
            Integer a = (Integer) vt.get(1, VoltType.INTEGER);
            String b = (String) vt.get(2, VoltType.STRING);
            String c = (String) vt.get(3, VoltType.STRING);

            assertTrue(lastString.compareTo(c) > 0)// always descending
            lastString = c;

            int pos = a_pool_str.indexOf(c);     // offset of this value in unsorted data
            assertEquals(pos, key.intValue());   // side-effect of insertion method
            assertEquals(a, a_int.get(pos));     // retrieved value matches at index in unsorted data
            assertEquals(b, a_inline_str.get(pos));
        }
    }


            /* create this fascinating survey result table:
         *   Key    Rank    User       Cereal
         *   1      1       Alice      AlphaBitters
         *   2      2       Alice      CrunchTubers
         *   3      3       Alice      BetaBuildingBlocks

         *   4      1       Betty      CrunchTubers
         *   5      2       Betty      AlphaBitters
         *   6      3       Betty      BetaBuildingBlocks

         *   7      1       Chris      BetaBuildingBlocks
         *   8      2       Chris      CrunchTubers
         *   9      3       Chris      AlphaBitters
         */

    public void testMultiColumnOrderBy() throws NoConnectionsException, ProcCallException, IOException, InterruptedException {
        VoltTable vt;
        Client client = this.getClient();
        loadWithDupes(client);

        // order by reverse rank and ascending name ..
        vt =  client.callProcedure("@AdHoc", "select * from O1 order by A_INT DESC, A_INLINE_STR ASC" ).getResults()[0];
        assertTrue(vt.getRowCount() == 9);

        vt.advanceRow();
        Integer a = (Integer) vt.get(1, VoltType.INTEGER);
        String b = (String) vt.get(2, VoltType.STRING);
        String c = (String) vt.get(3, VoltType.STRING);
        assertEquals(3, a.intValue());
        assertEquals(b, "Alice");
        assertEquals(c, "BetaBuildingBlocks");

        vt.advanceRow();
        a = (Integer) vt.get(1, VoltType.INTEGER); b = (String) vt.get(2, VoltType.STRING); c = (String) vt.get(3, VoltType.STRING);
        assertEquals(3, a.intValue());
        assertEquals(b, "Betty");
        assertEquals(c, "BetaBuildingBlocks");

        vt.advanceRow();
        a = (Integer) vt.get(1, VoltType.INTEGER); b = (String) vt.get(2, VoltType.STRING); c = (String) vt.get(3, VoltType.STRING);
        assertEquals(3, a.intValue());
        assertEquals(b, "Chris");
        assertEquals(c, "AlphaBitters");

        // 2nd rank
        vt.advanceRow();
        a = (Integer) vt.get(1, VoltType.INTEGER); b = (String) vt.get(2, VoltType.STRING); c = (String) vt.get(3, VoltType.STRING);
        assertEquals(2, a.intValue());
        assertEquals(b, "Alice");
        assertEquals(c, "CrunchTubers");

        vt.advanceRow();
        a = (Integer) vt.get(1, VoltType.INTEGER); b = (String) vt.get(2, VoltType.STRING); c = (String) vt.get(3, VoltType.STRING);
        assertEquals(2, a.intValue());
        assertEquals(b, "Betty");
        assertEquals(c, "AlphaBitters");

        vt.advanceRow();
        a = (Integer) vt.get(1, VoltType.INTEGER); b = (String) vt.get(2, VoltType.STRING); c = (String) vt.get(3, VoltType.STRING);
        assertEquals(2, a.intValue());
        assertEquals(b, "Chris");
        assertEquals(c, "CrunchTubers");

        // 1st rank
        vt.advanceRow();
        a = (Integer) vt.get(1, VoltType.INTEGER); b = (String) vt.get(2, VoltType.STRING); c = (String) vt.get(3, VoltType.STRING);
        assertEquals(1, a.intValue());
        assertEquals(b, "Alice");
        assertEquals(c, "AlphaBitters");

        vt.advanceRow();
        a = (Integer) vt.get(1, VoltType.INTEGER); b = (String) vt.get(2, VoltType.STRING); c = (String) vt.get(3, VoltType.STRING);
        assertEquals(1, a.intValue());
        assertEquals(b, "Betty");
        assertEquals(c, "CrunchTubers");

        vt.advanceRow();
        a = (Integer) vt.get(1, VoltType.INTEGER); b = (String) vt.get(2, VoltType.STRING); c = (String) vt.get(3, VoltType.STRING);
        assertEquals(1, a.intValue());
        assertEquals(b, "Chris");
        assertEquals(c, "BetaBuildingBlocks");
    }

    /** FIXME
    public void testOrderByUseIndex() throws NoConnectionsException,
                                     ProcCallException,
                                     IOException, InterruptedException {
        @SuppressWarnings("unused")
        long start, elapsed;
        //long base;
        VoltTable vt;
        Client client = this.getClient();
        if (this.isHSQL())
            return;

        loadInOrder(client);

        // the duration of doing sequential scan followed by a quicksort
        // start = System.currentTimeMillis();
        // vt = client.callProcedure("OrderByNonIndex")[0];
        // base = System.currentTimeMillis() - start;

        // sort one index column of ints ascending
        start = System.currentTimeMillis();
        vt = client.callProcedure("OrderByOneIndex").getResults()[0];
        elapsed = System.currentTimeMillis() - start;
        // at least 3 times faster
        // TODO (nshi): This should really belong to performance tests.
        // assertTrue(elapsed <= base / 3);
        assertEquals(3, vt.getRowCount());
        long it = Integer.MAX_VALUE;
        while (vt.advanceRow()) {
            int b = (Integer) vt.get(1, VoltType.INTEGER);
            int c = (Integer) vt.get(2, VoltType.INTEGER);
            int d = (Integer) vt.get(3, VoltType.INTEGER);

            assertEquals(b, c);
            assertEquals(c, d);
            assertTrue(b <= it);
            it = b;
        }
    }
    **/

    public void testAggOrderByGroupBy() throws IOException, ProcCallException, InterruptedException
    {
        VoltTable vt;
        Client client = this.getClient();
        loadWithDupes(client);
        vt =  client.callProcedure("@AdHoc", "select sum(A_INT), A_INLINE_STR, sum(PKEY) from O1 group by A_INLINE_STR order by A_INLINE_STR" ).getResults()[0];
        System.out.println(vt.toString());
        vt.advanceRow();
        assertEquals(6, vt.get(0, VoltType.INTEGER));
        assertEquals("Alice", vt.get(1, VoltType.STRING));
        assertEquals(6, vt.get(2, VoltType.INTEGER));
        vt.advanceRow();
        assertEquals(6, vt.get(0, VoltType.INTEGER));
        assertEquals("Betty", vt.get(1, VoltType.STRING));
        assertEquals(15, vt.get(2, VoltType.INTEGER));
        vt.advanceRow();
        assertEquals(6, vt.get(0, VoltType.INTEGER));
        assertEquals("Chris", vt.get(1, VoltType.STRING));
        assertEquals(24, vt.get(2, VoltType.INTEGER));
    }

    //
    // Suite builder boilerplate
    //

    public TestOrderBySuite(String name) {
        super(name);
    }

    public static void main(String args[]) {
        org.junit.runner.JUnitCore.runClasses(TestOrderBySuite.class);
    }

    static public junit.framework.Test suite() {
        VoltServerConfig config = null;
        MultiConfigSuiteBuilder builder = new MultiConfigSuiteBuilder(TestOrderBySuite.class);
        builder.setGlobalConfParameter("site.specexec_enable", false);
       
        VoltProjectBuilder project = new VoltProjectBuilder("testorderby");
        project.addSchema(TestOrderBySuite.class.getResource("testorderby-ddl.sql"));
        project.addTablePartitionInfo("O1", "PKEY");
        project.addProcedures(PROCEDURES);

        config = new LocalSingleProcessServer("testorderby-onesite.jar",
                1, BackendTarget.NATIVE_EE_JNI);
        config.compile(project);
        builder.addServerConfig(config);

        config = new LocalSingleProcessServer("testorderby-threesites.jar",
                    3, BackendTarget.NATIVE_EE_JNI);
        config.compile(project);
        builder.addServerConfig(config);

        config = new LocalSingleProcessServer("testorderby-hsql.jar",
                1, BackendTarget.HSQLDB_BACKEND);
        config.compile(project);
        builder.addServerConfig(config);

        // Cluster
        config = new LocalCluster("testorderby-cluster.jar", 2, 2,
                                  1, BackendTarget.NATIVE_EE_JNI);
        config.setConfParameter("site.exec_adhoc_sql", true);
        config.compile(project);
        builder.addServerConfig(config);

        return builder;
    }
}
TOP

Related Classes of org.voltdb.regressionsuites.TestOrderBySuite

TOP
Copyright © 2018 www.massapi.com. All rights reserved.
All source code are property of their respective owners. Java is a trademark of Sun Microsystems, Inc and owned by ORACLE Inc. Contact coftware#gmail.com.