Examples with VoltTable org.voltdb.VoltTable used on opensource projects

Example 46 with VoltTable

use of org.voltdb.VoltTable in project voltdb by VoltDB.

the class TestTPCCSuite method testPAYMENT.

public void testPAYMENT() throws IOException, ProcCallException {
    Client client = getClient();
    // create a District, Warehouse, and 4 Customers (multiple, since we
    // want to test for correct behavior of paymentByCustomerName.
    // long d_id, long d_w_id, String d_name, String d_street_1, String
    // d_street_2, String d_city, String d_state, String d_zip, double
    // d_tax, double d_ytd, long d_next_o_id
    final double initialYTD = 15241.45;
    VoltTable district = client.callProcedure("InsertDistrict", D_ID, W_ID, "A District", "Street Addy", "meh", "westerfield", "BA", "99999", .0825, initialYTD, 21L).getResults()[0];
    // check that a district was inserted
    assertEquals(1L, district.asScalarLong());
    // long w_id, String w_name, String w_street_1, String w_street_2,
    // String w_city, String w_zip, double w_tax, long w_ytd
    VoltTable warehouse = client.callProcedure("InsertWarehouse", W_ID, "EZ Street WHouse", "Headquarters", "77 Mass. Ave.", "Cambridge", "AZ", "12938", .1234, initialYTD).getResults()[0];
    // check for successful insertion.
    assertEquals(1L, warehouse.asScalarLong());
    // long c_id, long c_d_id, long c_w_id, String c_first, String c_middle,
    // String c_last, String c_street_1, String c_street_2, String d_city,
    // String d_state, String d_zip, String c_phone, Date c_since, String
    // c_credit, double c_credit_lim, double c_discount, double c_balance,
    // double c_ytd_payment, double c_payment_cnt, double c_delivery_cnt,
    // String c_data
    final double initialBalance = 15.75;
    VoltTable customer1 = client.callProcedure("InsertCustomer", C_ID, D_ID, W_ID, "I", "Be", "lastname", "Place", "Place2", "BiggerPlace", "AL", "91083", "(193) 099 - 9082", new TimestampType(), "BC", 19298943.12, .13, initialBalance, initialYTD, 0L, 15L, "Some History").getResults()[0];
    // check for successful insertion.
    assertEquals(1L, customer1.asScalarLong());
    VoltTable customer2 = client.callProcedure("InsertCustomer", C_ID + 1, D_ID, W_ID, "We", "R", "Customer", "Random Department", "Place2", "BiggerPlace", "AL", "13908", "(913) 909 - 0928", new TimestampType(), "GC", 19298943.12, .13, initialBalance, initialYTD, 1L, 15L, "Some History").getResults()[0];
    // check for successful insertion.
    assertEquals(1L, customer2.asScalarLong());
    VoltTable customer3 = client.callProcedure("InsertCustomer", C_ID + 2, D_ID, W_ID, "Who", "Is", "Customer", "Receiving", "450 Mass F.X.", "BiggerPlace", "CI", "91083", "(541) 931 - 0928", new TimestampType(), "GC", 19899324.21, .13, initialBalance, initialYTD, 2L, 15L, "Some History").getResults()[0];
    // check for successful insertion.
    assertEquals(1L, customer3.asScalarLong());
    VoltTable customer4 = client.callProcedure("InsertCustomer", C_ID + 3, D_ID, W_ID, "ICanBe", "", "Customer", "street", "place", "BiggerPlace", "MA", "91083", "(913) 909 - 0928", new TimestampType(), "GC", 19298943.12, .13, initialBalance, initialYTD, 3L, 15L, "Some History").getResults()[0];
    // check for successful insertion.
    assertEquals(1L, customer4.asScalarLong());
    TPCDataPrinter.printAllData(client);
    // long d_id, long w_id, double h_amount, String c_last, long c_w_id,
    // long c_d_id
    final double paymentAmount = 500.25;
    VoltTable[] results = client.callProcedure("paymentByCustomerName", W_ID, D_ID, paymentAmount, W_ID, D_ID, "Customer", new TimestampType()).getResults();
    assertEquals(3, results.length);
    // check that the middle "Customer" was returned
    assertEquals(C_ID + 1, results[2].fetchRow(0).getLong("c_id"));
    assertEquals("", results[2].fetchRow(0).getString("c_data"));
    // Verify that both warehouse, district and customer were updated
    // correctly
    VoltTable[] allTables = client.callProcedure("SelectAll").getResults();
    warehouse = allTables[TPCDataPrinter.nameMap.get("WAREHOUSE")];
    assertEquals(1, warehouse.getRowCount());
    assertEquals(initialYTD + paymentAmount, warehouse.fetchRow(0).getDouble("W_YTD"));
    district = allTables[TPCDataPrinter.nameMap.get("DISTRICT")];
    assertEquals(1, district.getRowCount());
    assertEquals(initialYTD + paymentAmount, district.fetchRow(0).getDouble("D_YTD"));
    customer1 = allTables[TPCDataPrinter.nameMap.get("CUSTOMER")];
    assertEquals(4, customer1.getRowCount());
    assertEquals(C_ID + 1, customer1.fetchRow(1).getLong("C_ID"));
    assertEquals(initialBalance - paymentAmount, customer1.fetchRow(1).getDouble("C_BALANCE"));
    assertEquals(initialYTD + paymentAmount, customer1.fetchRow(1).getDouble("C_YTD_PAYMENT"));
    assertEquals(2, customer1.fetchRow(1).getLong("C_PAYMENT_CNT"));
    // long d_id, long w_id, double h_amount, String c_last, long c_w_id,
    // long c_d_id
    results = client.callProcedure("paymentByCustomerId", W_ID, D_ID, paymentAmount, W_ID, D_ID, C_ID, new TimestampType()).getResults();
    // Also tests badcredit case.
    assertEquals(3, results.length);
    assertEquals(C_ID, results[2].fetchRow(0).getLong("c_id"));
    // bad credit: insert history into c_data
    String data = results[2].fetchRow(0).getString("c_data");
    assertTrue(data.startsWith(new Long(C_ID).toString()));
    // Verify that both warehouse and district's ytd values were incremented
    // correctly
    allTables = client.callProcedure("SelectAll").getResults();
    warehouse = allTables[TPCDataPrinter.nameMap.get("WAREHOUSE")];
    assertEquals(1, warehouse.getRowCount());
    assertEquals(initialYTD + paymentAmount * 2, warehouse.fetchRow(0).getDouble("W_YTD"));
    district = allTables[TPCDataPrinter.nameMap.get("DISTRICT")];
    assertEquals(1, district.getRowCount());
    assertEquals(initialYTD + paymentAmount * 2, district.fetchRow(0).getDouble("D_YTD"));
    customer1 = allTables[TPCDataPrinter.nameMap.get("CUSTOMER")];
    assertEquals(4, customer1.getRowCount());
    assertEquals(C_ID, customer1.fetchRow(0).getLong("C_ID"));
    assertEquals(initialBalance - paymentAmount, customer1.fetchRow(1).getDouble("C_BALANCE"));
    assertEquals(initialYTD + paymentAmount, customer1.fetchRow(1).getDouble("C_YTD_PAYMENT"));
    assertEquals(1, customer1.fetchRow(0).getLong("C_PAYMENT_CNT"));
}

Example 47 with VoltTable

use of org.voltdb.VoltTable in project voltdb by VoltDB.

the class TestTPCCSuite method testPAYMENTMultiPartition.

public void testPAYMENTMultiPartition() throws IOException, ProcCallException {
    Client client = getClient();
    // create 2 Districts, 2 Warehouses, and 2 Customers on each Warehouse/District
    // long d_id, long d_w_id, String d_name, String d_street_1, String
    // d_street_2, String d_city, String d_state, String d_zip, double
    // d_tax, double d_ytd, long d_next_o_id
    final double initialYTD = 15241.45;
    VoltTable district1 = client.callProcedure("InsertDistrict", D_ID, W_ID, "A District", "Street Addy", "meh", "westerfield", "BA", "99999", .0825, initialYTD, 21L).getResults()[0];
    // check that a district was inserted
    assertEquals(1L, district1.asScalarLong());
    VoltTable district2 = client.callProcedure("InsertDistrict", D2_ID, W2_ID, "fdsdfaaaaa", "fsdfsdfasas", "fda", "asdasfddsds", "MA", "99999", .0825, initialYTD, 21L).getResults()[0];
    assertEquals(1L, district2.asScalarLong());
    // long w_id, String w_name, String w_street_1, String w_street_2,
    // String w_city, String w_zip, double w_tax, long w_ytd
    VoltTable warehouse1 = client.callProcedure("InsertWarehouse", W_ID, "EZ Street WHouse", "Headquarters", "77 Mass. Ave.", "Cambridge", "AZ", "12938", .1234, initialYTD).getResults()[0];
    // check for successful insertion.
    assertEquals(1L, warehouse1.asScalarLong());
    VoltTable warehouse2 = client.callProcedure("InsertWarehouse", W2_ID, "easdsdfsdfddfdsd", "asfadasffass", "fdswwwwaafff", "Cambridge", "AZ", "12938", .1234, initialYTD).getResults()[0];
    assertEquals(1L, warehouse2.asScalarLong());
    // customer 1 and 2 are in district1, 3 and 4 are in district2
    // long c_id, long c_d_id, long c_w_id, String c_first, String c_middle,
    // String c_last, String c_street_1, String c_street_2, String d_city,
    // String d_state, String d_zip, String c_phone, Date c_since, String
    // c_credit, double c_credit_lim, double c_discount, double c_balance,
    // double c_ytd_payment, double c_payment_cnt, double c_delivery_cnt,
    // String c_data
    final double initialBalance = 15.75;
    VoltTable customer1 = client.callProcedure("InsertCustomer", C_ID, D_ID, W_ID, "I", "Be", "lastname", "Place", "Place2", "BiggerPlace", "AL", "91083", "(193) 099 - 9082", new TimestampType(), "BC", 19298943.12, .13, initialBalance, initialYTD, 0L, 15L, "Some History").getResults()[0];
    // check for successful insertion.
    assertEquals(1L, customer1.asScalarLong());
    client.callProcedure("InsertCustomerName", C_ID, D_ID, W_ID, "I", "lastname");
    VoltTable customer2 = client.callProcedure("InsertCustomer", C_ID + 1, D_ID, W_ID, "We", "R", "Customer", "Random Department", "Place2", "BiggerPlace", "AL", "13908", "(913) 909 - 0928", new TimestampType(), "GC", 19298943.12, .13, initialBalance, initialYTD, 1L, 15L, "Some History").getResults()[0];
    // check for successful insertion.
    assertEquals(1L, customer2.asScalarLong());
    client.callProcedure("InsertCustomerName", C_ID + 1, D_ID, W_ID, "We", "Customer");
    VoltTable customer3 = client.callProcedure("InsertCustomer", C_ID + 2, D2_ID, W2_ID, "Who", "Is", "Customer", "Receiving", "450 Mass F.X.", "BiggerPlace", "CI", "91083", "(541) 931 - 0928", new TimestampType(), "GC", 19899324.21, .13, initialBalance, initialYTD, 2L, 15L, "Some History").getResults()[0];
    // check for successful insertion.
    assertEquals(1L, customer3.asScalarLong());
    client.callProcedure("InsertCustomerName", C_ID + 2, D2_ID, W2_ID, "Who", "Customer");
    VoltTable customer4 = client.callProcedure("InsertCustomer", C_ID + 3, D2_ID, W2_ID, "ICanBe", "", "Customer", "street", "place", "BiggerPlace", "MA", "91083", "(913) 909 - 0928", new TimestampType(), "GC", 19298943.12, .13, initialBalance, initialYTD, 3L, 15L, "Some History").getResults()[0];
    // check for successful insertion.
    assertEquals(1L, customer4.asScalarLong());
    client.callProcedure("InsertCustomerName", C_ID + 3, D2_ID, W2_ID, "ICanBe", "Customer");
    final double paymentAmount = 500.25;
    // long d_id, long w_id, double h_amount, String c_last, long c_w_id,
    // long c_d_id
    // w_id =Warehouse2 but c_w_id=warehouse1 !
    VoltTable[] results = client.callProcedure("paymentByCustomerName", W2_ID, D2_ID, paymentAmount, W_ID, D_ID, "Customer", new TimestampType()).getResults();
    assertEquals(3, results.length);
    assertTrue(results[0].getRowCount() > 0);
    // only customer2 should be returned as this is a query on warehouse1
    assertEquals(C_ID + 1, results[2].fetchRow(0).getLong("c_id"));
    assertEquals("", results[2].fetchRow(0).getString("c_data"));
    /* TODO : SelectAll doesn't work for multi-partition. how to check results?
        // Verify that both warehouse, district and customer were updated
        // correctly
        VoltTable[] allTables = client.callProcedure("SelectAll");
        warehouse = allTables[TPCDataPrinter.nameMap.get("WAREHOUSE")];
        assertEquals(1, warehouse.getRowCount());
        assertEquals(initialYTD + paymentAmount, warehouse.fetchRow(0)
                .getDouble("W_YTD"));
        district = allTables[TPCDataPrinter.nameMap.get("DISTRICT")];
        assertEquals(1, district.getRowCount());
        assertEquals(initialYTD + paymentAmount, district.fetchRow(0)
                .getDouble("D_YTD"));
        customer1 = allTables[TPCDataPrinter.nameMap.get("CUSTOMER")];
        assertEquals(4, customer1.getRowCount());
        assertEquals(C_ID + 1, customer1.fetchRow(1).getLong("C_ID"));
        assertEquals(initialBalance - paymentAmount, customer1.fetchRow(1)
                .getDouble("C_BALANCE"));
        assertEquals(initialYTD + paymentAmount, customer1.fetchRow(1)
                .getDouble("C_YTD_PAYMENT"));
        assertEquals(2, customer1.fetchRow(1).getLong("C_PAYMENT_CNT"));
        */
    // long d_id, long w_id, double h_amount, String c_last, long c_w_id,
    // long c_d_id
    // w_id =Warehouse2 but c_w_id=warehouse1 !
    results = client.callProcedure("paymentByCustomerId", W2_ID, D2_ID, paymentAmount, W_ID, D_ID, C_ID, new TimestampType()).getResults();
    // Also tests badcredit case.
    assertEquals(3, results.length);
    assertEquals(C_ID, results[2].fetchRow(0).getLong("c_id"));
    // bad credit: insert history into c_data
    String data = results[2].fetchRow(0).getString("c_data");
    assertTrue(data.startsWith(new Long(C_ID).toString()));
/* TODO : SelectAll doesn't work for multi-partition. how to check results?
        // Verify that both warehouse and district's ytd values were incremented
        // correctly
        allTables = client.callProcedure("SelectAll");
        warehouse = allTables[TPCDataPrinter.nameMap.get("WAREHOUSE")];
        assertEquals(1, warehouse.getRowCount());
        assertEquals(initialYTD + paymentAmount * 2, warehouse.fetchRow(0)
                .getDouble("W_YTD"));
        district = allTables[TPCDataPrinter.nameMap.get("DISTRICT")];
        assertEquals(1, district.getRowCount());
        assertEquals(initialYTD + paymentAmount * 2, district.fetchRow(0)
                .getDouble("D_YTD"));
        customer1 = allTables[TPCDataPrinter.nameMap.get("CUSTOMER")];
        assertEquals(4, customer1.getRowCount());
        assertEquals(C_ID, customer1.fetchRow(0).getLong("C_ID"));
        assertEquals(initialBalance - paymentAmount, customer1.fetchRow(1)
                .getDouble("C_BALANCE"));
        assertEquals(initialYTD + paymentAmount, customer1.fetchRow(1)
                .getDouble("C_YTD_PAYMENT"));
        assertEquals(1, customer1.fetchRow(0).getLong("C_PAYMENT_CNT"));
        */
}

Example 48 with VoltTable

use of org.voltdb.VoltTable in project voltdb by VoltDB.

the class TestTableCountSuite method testTableCountsWithOperation.

public void testTableCountsWithOperation() throws Exception {
    Client client = getClient();
    client.callProcedure("TU1.insert", 1, 1);
    client.callProcedure("TU1.insert", 2, 2);
    client.callProcedure("TU1.insert", 3, 3);
    client.callProcedure("TU1.insert", 6, 6);
    client.callProcedure("TU1.insert", 8, 8);
    VoltTable table = client.callProcedure("@AdHoc", "SELECT (COUNT(*)+1) FROM TU1").getResults()[0];
    assertTrue(table.getRowCount() == 1);
    assertTrue(table.advanceRow());
    assertEquals(6, table.getLong(0));
    // subquery temp table count
    table = client.callProcedure("@AdHoc", "select count(*) from (SELECT * FROM TU1) Temp").getResults()[0];
    assertTrue(table.getRowCount() == 1);
    assertTrue(table.advanceRow());
    assertEquals(5, table.getLong(0));
}

Example 49 with VoltTable

use of org.voltdb.VoltTable in project voltdb by VoltDB.

the class TestTableCountSuite method testTableCounts.

public void testTableCounts() throws Exception {
    Client client = getClient();
    client.callProcedure("TU1.insert", 1, 1);
    client.callProcedure("TU1.insert", 2, 2);
    client.callProcedure("TU1.insert", 3, 3);
    client.callProcedure("TU1.insert", 6, 6);
    client.callProcedure("TU1.insert", 8, 8);
    VoltTable table;
    table = client.callProcedure("@AdHoc", "SELECT COUNT(*) FROM TU1").getResults()[0];
    assertTrue(table.getRowCount() == 1);
    assertTrue(table.advanceRow());
    assertEquals(5, table.getLong(0));
    // subquery temp table count
    table = client.callProcedure("@AdHoc", "select count(*) from (SELECT * FROM TU1) Temp").getResults()[0];
    assertTrue(table.getRowCount() == 1);
    assertTrue(table.advanceRow());
    assertEquals(5, table.getLong(0));
    table = client.callProcedure("@AdHoc", "select count(*) " + "from (SELECT * FROM TU1 where id > 3) Temp").getResults()[0];
    assertTrue(table.getRowCount() == 1);
    assertTrue(table.advanceRow());
    assertEquals(2, table.getLong(0));
    // Unique Map, two column index
    client.callProcedure("TU3.insert", 1, 1, 123);
    client.callProcedure("TU3.insert", 2, 2, 123);
    client.callProcedure("TU3.insert", 3, 3, 123);
    client.callProcedure("TU3.insert", 4, 6, 123);
    client.callProcedure("TU3.insert", 5, 8, 123);
    client.callProcedure("TU3.insert", 6, 1, 456);
    client.callProcedure("TU3.insert", 7, 2, 456);
    client.callProcedure("TU3.insert", 8, 3, 456);
    client.callProcedure("TU3.insert", 9, 6, 456);
    client.callProcedure("TU3.insert", 10, 8, 456);
    table = client.callProcedure("@AdHoc", "SELECT COUNT(*) FROM TU3").getResults()[0];
    assertTrue(table.getRowCount() == 1);
    assertTrue(table.advanceRow());
    assertEquals(10, table.getLong(0));
    // subquery temp table count
    table = client.callProcedure("@AdHoc", "select count(*) from (SELECT * FROM TU3) Temp").getResults()[0];
    assertTrue(table.getRowCount() == 1);
    assertTrue(table.advanceRow());
    assertEquals(10, table.getLong(0));
    // Multi-map, two column index
    client.callProcedure("TM2.insert", 1, 1, "xin");
    client.callProcedure("TM2.insert", 2, 2, "xin");
    client.callProcedure("TM2.insert", 3, 3, "xin");
    client.callProcedure("TM2.insert", 4, 3, "xin");
    client.callProcedure("TM2.insert", 5, 3, "xin");
    client.callProcedure("TM2.insert", 6, 5, "xin");
    client.callProcedure("TM2.insert", 7, 6, "xin");
    client.callProcedure("TM2.insert", 8, 6, "xin");
    client.callProcedure("TM2.insert", 9, 8, "xin");
    client.callProcedure("TM2.insert", 10, 8, "xin");
    client.callProcedure("TM2.insert", 11, 1, "jia");
    client.callProcedure("TM2.insert", 12, 2, "jia");
    client.callProcedure("TM2.insert", 13, 3, "jia");
    client.callProcedure("TM2.insert", 14, 3, "jia");
    client.callProcedure("TM2.insert", 15, 3, "jia");
    client.callProcedure("TM2.insert", 16, 5, "jia");
    client.callProcedure("TM2.insert", 17, 6, "jia");
    client.callProcedure("TM2.insert", 18, 6, "jia");
    client.callProcedure("TM2.insert", 19, 8, "jia");
    client.callProcedure("TM2.insert", 20, 8, "jia");
    table = client.callProcedure("@AdHoc", "SELECT COUNT(*) FROM TM2").getResults()[0];
    assertTrue(table.getRowCount() == 1);
    assertTrue(table.advanceRow());
    assertEquals(20, table.getLong(0));
    // subquery temp table count
    table = client.callProcedure("@AdHoc", "select count(*) from (SELECT * FROM TM2) Temp").getResults()[0];
    assertTrue(table.getRowCount() == 1);
    assertTrue(table.advanceRow());
    assertEquals(20, table.getLong(0));
}

Example 50 with VoltTable

use of org.voltdb.VoltTable in project voltdb by VoltDB.

the class TestUnionSuite method testIntersect.

/**
     * Three table Intersect - A.I, B.I and C.I
     * @throws NoConnectionsException
     * @throws IOException
     * @throws ProcCallException
     */
public void testIntersect() throws NoConnectionsException, IOException, ProcCallException {
    Client client = this.getClient();
    VoltTable vt;
    //In the final result set
    client.callProcedure("InsertA", 0, 0);
    //In the final result set
    client.callProcedure("InsertA", 1, 1);
    //Eliminated (duplicate)
    client.callProcedure("InsertA", 2, 1);
    //Eliminated (duplicate)
    client.callProcedure("InsertB", 1, 0);
    //Eliminated (duplicate)
    client.callProcedure("InsertB", 2, 1);
    //Eliminated (not in A)
    client.callProcedure("InsertB", 3, 2);
    //Eliminated (duplicate)
    client.callProcedure("InsertC", 1, 1);
    //Eliminated (not in A)
    client.callProcedure("InsertC", 2, 2);
    //Eliminated (duplicate)
    client.callProcedure("InsertC", 3, 0);
    vt = client.callProcedure("@AdHoc", "SELECT I FROM A INTERSECT SELECT I FROM B " + "INTERSECT SELECT I FROM C order by i;").getResults()[0];
    assertEquals(2, vt.getRowCount());
    validateTableOfScalarLongs(vt, new long[] { 0, 1 });
    vt = client.callProcedure("@AdHoc", "(SELECT I FROM A INTERSECT SELECT I FROM B) " + "INTERSECT SELECT I FROM C order by i;").getResults()[0];
    assertEquals(2, vt.getRowCount());
    validateTableOfScalarLongs(vt, new long[] { 0, 1 });
    vt = client.callProcedure("@AdHoc", "SELECT I FROM A INTERSECT " + "(SELECT I FROM B INTERSECT SELECT I FROM C) order by i;").getResults()[0];
    assertEquals(2, vt.getRowCount());
    validateTableOfScalarLongs(vt, new long[] { 0, 1 });
}