/** Called from the ExecutionEngine to request serialized dependencies. */
public byte[] nextDependencyAsBytes(final int dependencyId) {
final VoltTable vt = m_dependencyTracker.nextDependency(dependencyId);
if (vt != null) {
ByteBuffer buffer = vt.getDirectDataReference();
if (d)
LOG.debug(
String.format(
"Passing Dependency %d to EE [rows=%d, cols=%d, bytes=%d/%d]\n%s",
dependencyId,
vt.getRowCount(),
vt.getColumnCount(),
vt.getUnderlyingBufferSize(),
buffer.array().length,
vt.toString()));
assert (buffer.hasArray());
return (buffer.array());
}
// Note that we will hit this after retrieving all the VoltTables for the given dependencyId
// It does not mean that there were no VoltTables at all, it just means that
// we have gotten all of them
else if (d) {
LOG.warn(
String.format(
"Failed to find Dependency %d for EE [dep=%s, count=%d, ids=%s]",
dependencyId,
m_dependencyTracker.m_depsById.get(dependencyId),
m_dependencyTracker.m_depsById.size(),
m_dependencyTracker.m_depsById.keySet()));
}
return null;
}
protected static void checkQueryPlan(Client client, String query, String... patterns)
throws Exception {
VoltTable vt;
assert (patterns.length >= 1);
vt = client.callProcedure("@Explain", query).getResults()[0];
String vtStr = vt.toString();
for (String pattern : patterns) {
assertTrue(vtStr.contains(pattern));
}
}
public void testENG5156() throws IOException, ProcCallException {
Client client = this.getClient();
VoltTable result = null;
String insertProc = "SCORE.insert";
client.callProcedure(insertProc, 1, "b", 1, 1378827221795L, 1, 1);
client.callProcedure(insertProc, 2, "b", 2, 1378827221795L, 2, 2);
result = client.callProcedure("@ExplainProc", "GetTopScores").getResults()[0];
// using the "IDX_SCORE_VALUE_USER" index for sort order only.
assertTrue(result.toString().contains("IDX_SCORE_VALUE_USER"));
assertTrue(result.toString().contains("inline LIMIT with parameter"));
result =
client.callProcedure("GetTopScores", 1378827221793L, 1378827421793L, 1).getResults()[0];
validateTableOfLongs(result, new long[][] {{2, 2}});
// Test AdHoc.
result =
client.callProcedure(
"@Explain",
"SELECT user_id, score_value FROM score "
+ "WHERE score_date > 1378827221793 AND score_date <= 1378827421793 "
+ "ORDER BY score_value DESC, user_id DESC LIMIT 1; ")
.getResults()[0];
assertTrue(result.toString().contains("IDX_SCORE_VALUE_USER"));
assertTrue(result.toString().contains("inline LIMIT with parameter"));
result =
client.callProcedure(
"@AdHoc",
"SELECT user_id, score_value FROM score "
+ "WHERE score_date > 1378827221793 AND score_date <= 1378827421793 "
+ "ORDER BY score_value DESC, user_id DESC LIMIT 1; ")
.getResults()[0];
validateTableOfLongs(result, new long[][] {{2, 2}});
}
protected static void validStatisticsForTableLimitAndPercentage(
Client client, String tableName, long limit, long percentage) throws Exception {
long start = System.currentTimeMillis();
while (true) {
long lastLimit = -1, lastPercentage = -1;
Thread.sleep(1000);
if (System.currentTimeMillis() - start > 10000) {
String percentageStr = "";
if (percentage >= 0) {
percentageStr = ", last seen percentage: " + lastPercentage;
}
fail("Took too long or have wrong answers: last seen limit: " + lastLimit + percentageStr);
}
VoltTable[] results = client.callProcedure("@Statistics", "TABLE", 0).getResults();
for (VoltTable t : results) {
System.out.println(t.toString());
}
if (results[0].getRowCount() == 0) continue;
boolean foundTargetTuple = false;
boolean limitExpected = false;
boolean percentageExpected = percentage < 0 ? true : false;
for (VoltTable vt : results) {
while (vt.advanceRow()) {
String name = vt.getString("TABLE_NAME");
if (tableName.equals(name)) {
foundTargetTuple = true;
lastLimit = vt.getLong("TUPLE_LIMIT");
if (limit == lastLimit) {
limitExpected = true;
}
if (percentageExpected || percentage == (lastPercentage = vt.getLong("PERCENT_FULL"))) {
percentageExpected = true;
}
if (limitExpected && percentageExpected) return;
break;
}
}
if (foundTargetTuple) break;
}
}
}
public VoltTable[] run(long zip) throws VoltAbortException {
Date timestamp = new Date();
// create a District, Warehouse, and 4 Customers (multiple, since we
// want to test for correct behavior of paymentByCustomerName.
final double initialYTD = 15241.45;
voltQueueSQL(
insertDistrict,
7L,
3L,
"A District",
"Street Addy",
"meh",
"westerfield",
"BA",
"99999",
.0825,
initialYTD,
21L);
// check that a district was inserted
long resultsl = voltExecuteSQL()[0].asScalarLong();
assert (resultsl == 1);
voltQueueSQL(
insertWarehouse,
3L,
"EZ Street WHouse",
"Headquarters",
"77 Mass. Ave.",
"Cambridge",
"AZ",
"12938",
.1234,
initialYTD);
// check that a warehouse was inserted
resultsl = voltExecuteSQL()[0].asScalarLong();
assert (resultsl == 1);
final double initialBalance = 15.75;
voltQueueSQL(
insertCustomer,
C_ID,
D_ID,
W_ID,
"I",
"Be",
"lastname",
"Place",
"Place2",
"BiggerPlace",
"AL",
"91083",
"(193) 099 - 9082",
new Date(),
"BC",
19298943.12,
.13,
initialBalance,
initialYTD,
0L,
15L,
"Some History");
// check that a customer was inserted
resultsl = voltExecuteSQL()[0].asScalarLong();
assert (resultsl == 1);
voltQueueSQL(
insertCustomer,
C_ID + 1,
D_ID,
W_ID,
"We",
"Represent",
"Customer",
"Random Department",
"Place2",
"BiggerPlace",
"AL",
"13908",
"(913) 909 - 0928",
new Date(),
"GC",
19298943.12,
.13,
initialBalance,
initialYTD,
1L,
15L,
"Some History");
// check that a customer was inserted
resultsl = voltExecuteSQL()[0].asScalarLong();
assert (resultsl == 1);
voltQueueSQL(
insertCustomer,
C_ID + 2,
D_ID,
W_ID,
"Who",
"Is",
"Customer",
"Receiving",
"450 Mass F.X.",
"BiggerPlace",
"CI",
"91083",
"(541) 931 - 0928",
new Date(),
"GC",
19899324.21,
.13,
initialBalance,
initialYTD,
2L,
15L,
"Some History");
// check that a customer was inserted
resultsl = voltExecuteSQL()[0].asScalarLong();
assert (resultsl == 1);
voltQueueSQL(
insertCustomer,
C_ID + 3,
D_ID,
W_ID,
"ICanBe",
"",
"Customer",
"street",
"place",
"BiggerPlace",
"MA",
"91083",
"(913) 909 - 0928",
new Date(),
"GC",
19298943.12,
.13,
initialBalance,
initialYTD,
3L,
15L,
"Some History");
// check that a customer was inserted
resultsl = voltExecuteSQL()[0].asScalarLong();
assert (resultsl == 1);
// 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 Date());
// assertEquals(3, results.length);
// being proc
voltQueueSQL(getCustomersByLastName, "Customer", D_ID, W_ID);
final VoltTable customers = voltExecuteSQL()[0];
final int namecnt = customers.getRowCount();
if (namecnt == 0) {
throw new VoltAbortException(
"no customers with last name: "
+ "Customer"
+ " in warehouse: "
+ W_ID
+ " and in district "
+ D_ID);
}
try {
System.out.println("PAYMENT FOUND CUSTOMERS: " + customers.toString());
} catch (Exception e) {
e.printStackTrace();
}
final int index = (namecnt - 1) / 2;
final VoltTableRow customer = customers.fetchRow(index);
final long c_id = customer.getLong(C_ID_IDX);
voltQueueSQL(getWarehouse, W_ID);
voltQueueSQL(getDistrict, W_ID, D_ID);
final VoltTable[] results = voltExecuteSQL();
final VoltTable warehouse = results[0];
final VoltTable district = results[1];
try {
System.out.println("WAREHOUSE PRE: " + warehouse.toString());
} catch (Exception e) {
e.printStackTrace();
}
voltQueueSQL(getWarehouses);
VoltTable warehouses = voltExecuteSQL()[0];
try {
System.out.println("WAREHOUSE PRE: " + warehouses.toString());
} catch (Exception e) {
e.printStackTrace();
}
voltQueueSQL(updateWarehouseBalance, paymentAmount, W_ID);
voltQueueSQL(updateDistrictBalance, paymentAmount, W_ID, D_ID);
voltExecuteSQL();
voltQueueSQL(getWarehouses);
warehouses = voltExecuteSQL()[0];
try {
System.out.println("WAREHOUSE PRE: " + warehouses.toString());
} catch (Exception e) {
e.printStackTrace();
}
// do stuff to extract district and warehouse info.
// customer info
final byte[] c_first = customer.getStringAsBytes(C_FIRST_IDX);
final byte[] c_middle = customer.getStringAsBytes(C_MIDDLE_IDX);
final byte[] c_last = customer.getStringAsBytes(C_LAST_IDX);
final byte[] c_street_1 = customer.getStringAsBytes(C_STREET_1_IDX);
final byte[] c_street_2 = customer.getStringAsBytes(C_STREET_2_IDX);
final byte[] c_city = customer.getStringAsBytes(C_CITY_IDX);
final byte[] c_state = customer.getStringAsBytes(C_STATE_IDX);
final byte[] c_zip = customer.getStringAsBytes(C_ZIP_IDX);
final byte[] c_phone = customer.getStringAsBytes(C_PHONE_IDX);
final TimestampType c_since = customer.getTimestampAsTimestamp(C_SINCE_IDX);
final byte[] c_credit = customer.getStringAsBytes(C_CREDIT_IDX);
final double c_credit_lim = customer.getDouble(C_CREDIT_LIM_IDX);
final double c_discount = customer.getDouble(C_DISCOUNT_IDX);
final double c_balance = customer.getDouble(C_BALANCE_IDX) - paymentAmount;
final double c_ytd_payment = customer.getDouble(C_YTD_PAYMENT_IDX) + paymentAmount;
final long c_payment_cnt = customer.getLong(C_PAYMENT_CNT_IDX) + 1;
byte[] c_data;
if (Arrays.equals(c_credit, Constants.BAD_CREDIT_BYTES)) {
c_data = customer.getStringAsBytes(C_DATA_IDX);
byte[] newData =
(c_id + " " + D_ID + " " + W_ID + " " + D_ID + " " + W_ID + " " + paymentAmount + "|")
.getBytes();
int newLength = newData.length + c_data.length;
if (newLength > Constants.MAX_C_DATA) {
newLength = Constants.MAX_C_DATA;
}
ByteBuilder builder = new ByteBuilder(newLength);
int minLength = newLength;
if (newData.length < minLength) minLength = newData.length;
builder.append(newData, 0, minLength);
int remaining = newLength - minLength;
builder.append(c_data, 0, remaining);
c_data = builder.array();
voltQueueSQL(
updateBCCustomer, c_balance, c_ytd_payment, c_payment_cnt, c_data, W_ID, D_ID, c_id);
} else {
c_data = new byte[0];
voltQueueSQL(updateGCCustomer, c_balance, c_ytd_payment, c_payment_cnt, W_ID, D_ID, c_id);
}
// Concatenate w_name, four spaces, d_name
byte[] w_name = warehouse.fetchRow(0).getStringAsBytes(W_NAME_IDX);
final byte[] FOUR_SPACES = {' ', ' ', ' ', ' '};
byte[] d_name = district.fetchRow(0).getStringAsBytes(D_NAME_IDX);
ByteBuilder builder = new ByteBuilder(w_name.length + FOUR_SPACES.length + d_name.length);
builder.append(w_name);
builder.append(FOUR_SPACES);
builder.append(d_name);
byte[] h_data = builder.array();
// Create the history record
voltQueueSQL(insertHistory, c_id, D_ID, W_ID, D_ID, W_ID, timestamp, paymentAmount, h_data);
voltExecuteSQL();
// TPC-C 2.5.3.3: Must display the following fields:
// W_ID, D_ID, C_ID, C_D_ID, C_W_ID, W_STREET_1, W_STREET_2, W_CITY, W_STATE, W_ZIP,
// D_STREET_1, D_STREET_2, D_CITY, D_STATE, D_ZIP, C_FIRST, C_MIDDLE, C_LAST, C_STREET_1,
// C_STREET_2, C_CITY, C_STATE, C_ZIP, C_PHONE, C_SINCE, C_CREDIT, C_CREDIT_LIM,
// C_DISCOUNT, C_BALANCE, the first 200 characters of C_DATA (only if C_CREDIT = "BC"),
// H_AMOUNT, and H_DATE.
// Return the entire warehouse and district tuples. The client provided:
// w_id, d_id, c_d_id, c_w_id, h_amount, h_data.
// Build a table for the rest
final VoltTable misc = misc_template.clone(8192);
misc.addRow(
c_id,
c_first,
c_middle,
c_last,
c_street_1,
c_street_2,
c_city,
c_state,
c_zip,
c_phone,
c_since,
c_credit,
c_credit_lim,
c_discount,
c_balance,
c_data);
// Hand back all the warehouse, district, and customer data
VoltTable[] resultsX = new VoltTable[] {warehouse, district, misc};
// resume test code
// check that the middle "Customer" was returned
assert ((C_ID + 1) == (resultsX[2].fetchRow(0).getLong("c_id")));
assert ("".equals(resultsX[2].fetchRow(0).getString("c_data")));
// Verify that both warehouse, district and customer were updated
// correctly
// VoltTable[] allTables = client.callProcedure("SelectAll");
voltQueueSQL(getWarehouses);
warehouses = voltExecuteSQL()[0];
try {
System.out.println("WAREHOUSE POST: " + warehouses.toString());
} catch (Exception e) {
e.printStackTrace();
}
assert (1 == warehouses.getRowCount());
double amt1 = initialYTD + paymentAmount;
double amt2 = warehouses.fetchRow(0).getDouble("W_YTD");
assert (amt1 == amt2);
voltQueueSQL(getDistricts);
VoltTable districts = voltExecuteSQL()[0];
assert (1 == districts.getRowCount());
assert ((initialYTD + paymentAmount) == districts.fetchRow(0).getDouble("D_YTD"));
voltQueueSQL(getCustomers);
VoltTable customersX = voltExecuteSQL()[0];
assert (4 == customersX.getRowCount());
assert ((C_ID + 1) == customersX.fetchRow(1).getLong("C_ID"));
assert ((initialBalance - paymentAmount) == customersX.fetchRow(1).getDouble("C_BALANCE"));
assert ((initialYTD + paymentAmount) == customersX.fetchRow(1).getDouble("C_YTD_PAYMENT"));
assert (2 == customersX.fetchRow(1).getLong("C_PAYMENT_CNT"));
return null;
}
public void loadTables(String tableNames, String procNames)
throws SQLException, IOException, InterruptedException, ExecutionException {
String[] tableNameArray =
tableNames != null && !"".equals(tableNames) ? tableNames.split(",") : null;
String[] procNameArray =
procNames != null && !"".equals(procNames) ? procNames.split(",") : null;
ExecutorService executor = Executors.newFixedThreadPool(tableNameArray.length * 3);
CompletionService completion = new ExecutorCompletionService(executor);
for (int j = 0; j < tableNameArray.length && tableNameArray != null; j++) {
String tableName = tableNameArray[j];
String procName = procNameArray != null ? procNameArray[j] : "";
// if procName not provided, use the default VoltDB TABLENAME.insert procedure
if (procName.length() == 0) {
if (tableName.contains("..")) {
procName = tableName.split("\\.\\.")[1].toUpperCase() + ".insert";
} else {
procName = tableName.toUpperCase() + ".insert";
}
}
// query the table
String jdbcSelect = "SELECT * FROM " + tableName + ";";
// create query to find count
String countquery = jdbcSelect.replace("*", "COUNT(*)");
int pages = 1;
String base = "";
if (config.srisvoltdb) {
if (config.isPaginated) {
try {
// find count
if (countquery.contains("<") || countquery.contains(">")) {
int bracketOpen = countquery.indexOf("<");
int bracketClose = countquery.indexOf(">");
String orderCol = countquery.substring(bracketOpen + 1, bracketClose);
countquery = countquery.replace("<" + orderCol + ">", "");
}
VoltTable vcount = client.callProcedure("@AdHoc", countquery).getResults()[0];
int count = Integer.parseInt(vcount.toString());
// determine number of pages from total data and page size
pages = (int) Math.ceil((double) count / config.pageSize);
System.out.println(pages);
} catch (Exception e) {
System.out.println("Count formation failure!");
}
}
} else {
// find count
Connection conn =
DriverManager.getConnection(config.jdbcurl, config.jdbcuser, config.jdbcpassword);
base = conn.getMetaData().getDatabaseProductName().toLowerCase();
Statement jdbcStmt =
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);
if (countquery.contains("<") || countquery.contains(">")) {
int bracketOpen = countquery.indexOf("<");
int bracketClose = countquery.indexOf(">");
String orderCol = countquery.substring(bracketOpen + 1, bracketClose);
countquery = countquery.replace("<" + orderCol + ">", "");
}
ResultSet rcount =
jdbcStmt.executeQuery(
countquery); // determine number of pages from total data and page size
if (base.contains("postgres") && config.isPaginated) {
int count = Integer.parseInt(rcount.toString());
pages = (int) Math.ceil((double) count / config.pageSize);
}
}
// establish new SourceReaders and DestinationWriters for pages
SourceReader[] sr = new SourceReader[pages];
DestinationWriter[] cr = new DestinationWriter[pages];
for (int i = 0; i < pages; i++) {
sr[i] = new SourceReader();
cr[i] = new DestinationWriter();
}
Controller processor =
new Controller<ArrayList<Object[]>>(
client, sr, cr, jdbcSelect, procName, config, pages, base);
completion.submit(processor);
}
// wait for all tasks to complete.
for (int i = 0; i < tableNameArray.length; ++i) {
logger.info(
"****************"
+ completion.take().get()
+ " completed *****************"); // will block until the next sub task has
// completed.
}
executor.shutdown();
}