@Override public void delete(RangeRequest range) { String prefixedTableName = prefixedTableNames.get(tableRef, conns); StringBuilder query = new StringBuilder(); query.append(" /* DELETE_RANGE (").append(prefixedTableName).append(") */ "); query.append(" DELETE FROM ").append(prefixedTableName).append(" m "); WhereClauses whereClauses = WhereClauses.create("m", range); List<Object> args = whereClauses.getArguments(); List<String> clauses = whereClauses.getClauses(); if (!clauses.isEmpty()) { query.append(" WHERE "); Joiner.on(" AND ").appendTo(query, clauses); } // execute the query conns.get().updateUnregisteredQuery(query.toString(), args.toArray()); }
public static WhereClauses create(String tableIdentifier, RangeRequest request, String... clauses) { List<String> extraWhereClauses = Lists.newArrayList(clauses); byte[] start = request.getStartInclusive(); byte[] end = request.getEndExclusive(); Collection<byte[]> cols = request.getColumnNames(); List<Object> args = Lists.newArrayListWithCapacity(2 + cols.size()); List<String> whereClauses = Lists.newArrayListWithCapacity(3 + extraWhereClauses.size()); if (start.length > 0) { whereClauses.add(tableIdentifier + (request.isReverse() ? ".row_name <= ?" : ".row_name >= ?")); args.add(start); } if (end.length > 0) { whereClauses.add(tableIdentifier + (request.isReverse() ? ".row_name > ?" : ".row_name < ?")); args.add(end); } if (!cols.isEmpty()) { whereClauses.add(tableIdentifier + ".col_name IN (" + BasicSQLUtils.nArguments(cols.size()) + ")"); args.addAll(cols); } whereClauses.addAll(extraWhereClauses); return new WhereClauses(whereClauses, args); }
@Test public void endOnly() { RangeRequest request = RangeRequest.builder().endRowExclusive(END).build(); WhereClauses whereClauses = WhereClauses.create("i", request); List<String> expectedClauses = ImmutableList.of("i.row_name < ?"); assertEquals(whereClauses.getClauses(), expectedClauses); checkWhereArguments(whereClauses, ImmutableList.of(END)); }
private void checkWhereArguments(WhereClauses whereClauses, List<byte[]> expectedArgs) { List<Object> actualArgs = whereClauses.getArguments(); for (int i = 0; i < actualArgs.size(); i++) { assertArrayEquals(expectedArgs.get(i), (byte[]) actualArgs.get(i)); } assertEquals(expectedArgs.size(), actualArgs.size()); } }
@Test public void startOnly() { RangeRequest request = RangeRequest.builder().startRowInclusive(START).build(); WhereClauses whereClauses = WhereClauses.create("i", request); List<String> expectedClauses = ImmutableList.of("i.row_name >= ?"); assertEquals(whereClauses.getClauses(), expectedClauses); checkWhereArguments(whereClauses, ImmutableList.of(START)); }
private void deleteOverflowRange(String overflowTable, String shortTableName, RangeRequest range) { StringBuilder query = new StringBuilder(); query.append(" /* DELETE_RANGE_OVERFLOW (").append(overflowTable).append(") */ "); query.append(" DELETE /*+ INDEX(m pk_").append(overflowTable).append(") */ "); query.append(" FROM ").append(overflowTable).append(" m "); query.append(" WHERE m.id IN ("); // subquery for finding rows in the short table query.append("SELECT /*+ INDEX(i pk_").append(shortTableName).append(") */ "); query.append(" i.overflow "); query.append(" FROM ").append(shortTableName).append(" i "); // add where clauses WhereClauses whereClauses = WhereClauses.create("i", range, "i.overflow IS NOT NULL"); List<Object> args = whereClauses.getArguments(); List<String> clauses = whereClauses.getClauses(); if (!clauses.isEmpty()) { query.append(" WHERE "); Joiner.on(" AND ").appendTo(query, clauses); } query.append(")"); // execute the query conns.get().updateUnregisteredQuery(query.toString(), args.toArray()); }
@Test public void whereClausesNoColumns() { RangeRequest request = RangeRequest.builder().startRowInclusive(START).endRowExclusive(END).build(); WhereClauses whereClauses = WhereClauses.create("i", request); List<String> expectedClauses = ImmutableList.of("i.row_name >= ?", "i.row_name < ?"); assertEquals(whereClauses.getClauses(), expectedClauses); checkWhereArguments(whereClauses, ImmutableList.of(START, END)); }
public static WhereClauses create(String tableIdentifier, RangeRequest request, String... clauses) { List<String> extraWhereClauses = Lists.newArrayList(clauses); byte[] start = request.getStartInclusive(); byte[] end = request.getEndExclusive(); Collection<byte[]> cols = request.getColumnNames(); List<Object> args = Lists.newArrayListWithCapacity(2 + cols.size()); List<String> whereClauses = Lists.newArrayListWithCapacity(3 + extraWhereClauses.size()); if (start.length > 0) { whereClauses.add(tableIdentifier + (request.isReverse() ? ".row_name <= ?" : ".row_name >= ?")); args.add(start); } if (end.length > 0) { whereClauses.add(tableIdentifier + (request.isReverse() ? ".row_name > ?" : ".row_name < ?")); args.add(end); } if (!cols.isEmpty()) { whereClauses.add(tableIdentifier + ".col_name IN (" + BasicSQLUtils.nArguments(cols.size()) + ")"); args.addAll(cols); } whereClauses.addAll(extraWhereClauses); return new WhereClauses(whereClauses, args); }
WhereClauses whereClauses = WhereClauses.create("m", range); List<Object> args = whereClauses.getArguments(); List<String> clauses = whereClauses.getClauses();
@Test public void withReverseRange() { RangeRequest request = RangeRequest.reverseBuilder().startRowInclusive(END).endRowExclusive(START).build(); WhereClauses whereClauses = WhereClauses.create("i", request); List<String> expectedClauses = ImmutableList.of("i.row_name <= ?", "i.row_name > ?"); assertEquals(whereClauses.getClauses(), expectedClauses); checkWhereArguments(whereClauses, ImmutableList.of(END, START)); }
@Override public void delete(RangeRequest range) { String prefixedTableName = prefixedTableNames.get(tableRef, conns); StringBuilder query = new StringBuilder(); query.append(" /* DELETE_RANGE (").append(prefixedTableName).append(") */ "); query.append(" DELETE FROM ").append(prefixedTableName).append(" m "); WhereClauses whereClauses = WhereClauses.create("m", range); List<Object> args = whereClauses.getArguments(); List<String> clauses = whereClauses.getClauses(); if (!clauses.isEmpty()) { query.append(" WHERE "); Joiner.on(" AND ").appendTo(query, clauses); } // execute the query conns.get().updateUnregisteredQuery(query.toString(), args.toArray()); }
@Test public void whereClausesWithExtraClause() { RangeRequest request = RangeRequest.builder().startRowInclusive(START).endRowExclusive(END).build(); String extraClause = "i.foo = bar"; WhereClauses whereClauses = WhereClauses.create("i", request, extraClause); List<String> expectedClauses = ImmutableList.of("i.row_name >= ?", "i.row_name < ?", extraClause); assertEquals(whereClauses.getClauses(), expectedClauses); checkWhereArguments(whereClauses, ImmutableList.of(START, END)); }
private void deleteOverflowRange(String overflowTable, String shortTableName, RangeRequest range) { StringBuilder query = new StringBuilder(); query.append(" /* DELETE_RANGE_OVERFLOW (").append(overflowTable).append(") */ "); query.append(" DELETE /*+ INDEX(m pk_").append(overflowTable).append(") */ "); query.append(" FROM ").append(overflowTable).append(" m "); query.append(" WHERE m.id IN ("); // subquery for finding rows in the short table query.append("SELECT /*+ INDEX(i pk_").append(shortTableName).append(") */ "); query.append(" i.overflow "); query.append(" FROM ").append(shortTableName).append(" i "); // add where clauses WhereClauses whereClauses = WhereClauses.create("i", range, "i.overflow IS NOT NULL"); List<Object> args = whereClauses.getArguments(); List<String> clauses = whereClauses.getClauses(); if (!clauses.isEmpty()) { query.append(" WHERE "); Joiner.on(" AND ").appendTo(query, clauses); } query.append(")"); // execute the query conns.get().updateUnregisteredQuery(query.toString(), args.toArray()); }
@Test public void whereClausesMultiColumn() { RangeRequest request = RangeRequest.builder().startRowInclusive(START).endRowExclusive(END).retainColumns( ColumnSelection.create(ImmutableList.of(COL1, COL2, COL3))).build(); WhereClauses whereClauses = WhereClauses.create("i", request); List<String> expectedClauses = ImmutableList.of("i.row_name >= ?", "i.row_name < ?", "i.col_name IN (?,?,?)"); assertEquals(whereClauses.getClauses(), expectedClauses); checkWhereArguments(whereClauses, ImmutableList.of(START, END, COL1, COL2, COL3)); }
WhereClauses whereClauses = WhereClauses.create("m", range); List<Object> args = whereClauses.getArguments(); List<String> clauses = whereClauses.getClauses();
@Test public void usesDifferentTableIdentifier() { RangeRequest request = RangeRequest.builder().startRowInclusive(START).endRowExclusive(END).retainColumns( ColumnSelection.create(ImmutableList.of(COL1))).build(); WhereClauses whereClauses = WhereClauses.create("other", request); List<String> expectedClauses = ImmutableList.of( "other.row_name >= ?", "other.row_name < ?", "other.col_name IN (?)"); assertEquals(whereClauses.getClauses(), expectedClauses); checkWhereArguments(whereClauses, ImmutableList.of(START, END, COL1)); }
@Test public void whereClausesOneColumn() { RangeRequest request = RangeRequest.builder().startRowInclusive(START).endRowExclusive(END).retainColumns( ColumnSelection.create(ImmutableList.of(COL1))).build(); WhereClauses whereClauses = WhereClauses.create("i", request); List<String> expectedClauses = ImmutableList.of("i.row_name >= ?", "i.row_name < ?", "i.col_name IN (?)"); assertEquals(whereClauses.getClauses(), expectedClauses); checkWhereArguments(whereClauses, ImmutableList.of(START, END, COL1)); }