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()); }
@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()); }
WhereClauses whereClauses = WhereClauses.create("m", range);
@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)); }
@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)); }
@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)); }
@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)); }
@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)); }
@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)); }
@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)); }
@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)); }
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()); }
@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()); }
WhereClauses whereClauses = WhereClauses.create("m", range);