@Override public FullQuery getAllRowsQuery(Iterable<byte[]> rows, long ts, ColumnSelection columns, boolean includeValue) { String query = " /* GET_ALL_ROWS (" + tableName + ") */ " + " SELECT m.row_name, m.col_name, m.ts" + (includeValue ? ", m.val " : " ") + " FROM " + prefixedTableName() + " m " + " WHERE m.row_name IN " + numParams(Iterables.size(rows)) + " AND m.ts < ? " + (columns.allColumnsSelected() ? "" : " AND m.col_name IN " + numParams(Iterables.size(columns.getSelectedColumns()))); FullQuery fullQuery = new FullQuery(query).withArgs(rows).withArg(ts); return columns.allColumnsSelected() ? fullQuery : fullQuery.withArgs(columns.getSelectedColumns()); }
@Override public FullQuery getAllCellsQuery(Iterable<Cell> cells, long ts, boolean includeValue) { String query = " /* GET_ALL_CELLS (" + tableName + ") */ " + " SELECT m.row_name, m.col_name, m.ts" + (includeValue ? ", m.val " : " ") + " FROM " + prefixedTableName() + " m," + " (VALUES " + groupOfNumParams(2, Iterables.size(cells)) + ") t(row_name, col_name) " + " WHERE m.row_name = t.row_name " + " AND m.col_name = t.col_name " + " AND m.ts < ? "; return addCellArgs(new FullQuery(query), cells).withArg(ts); }
@Test public void columnSelection() { byte[] colTwo = new byte[] { 7, 8, 9 }; FullQuery.Builder builder = FullQuery.builder(); RangePredicateHelper.create(false, DBType.ORACLE, builder).columnSelection(ImmutableList.of(COL_NAME, colTwo)); FullQuery query = builder.build(); assertThat(query.getQuery(), equalTo(" AND (col_name = ? OR col_name = ?) ")); assertThat(query.getArgs(), arrayContaining(COL_NAME, colTwo)); }
@Override public FullQuery getAllCellQuery(Cell cell, long ts, boolean includeValue) { String query = " /* GET_ALL_CELL (" + tableName + ") */ " + " SELECT m.row_name, m.col_name, m.ts" + (includeValue ? ", m.val " : " ") + " FROM " + prefixedTableName() + " m " + " WHERE m.row_name = ? " + " AND m.col_name = ? " + " AND m.ts < ? "; return new FullQuery(query).withArgs(cell.getRowName(), cell.getColumnName(), ts); }
@Override public FullQuery getRowsColumnRangeQuery(RowsColumnRangeBatchRequest batch, long ts) { List<FullQuery> fullQueries = new ArrayList<>(); batch.getPartialFirstRow() .ifPresent(entry -> fullQueries.add(getRowsColumnRangeSubQuery(entry.getKey(), ts, entry.getValue()))); if (!batch.getRowsToLoadFully().isEmpty()) { fullQueries.add(getRowsColumnRangeFullyLoadedRowsSubQuery(batch.getRowsToLoadFully(), ts, batch.getColumnRangeSelection())); } batch.getPartialLastRow() .ifPresent(entry -> fullQueries.add(getRowsColumnRangeSubQuery(entry.getKey(), ts, entry.getValue()))); List<String> subQueries = fullQueries.stream().map(FullQuery::getQuery).collect(Collectors.toList()); int totalArgs = fullQueries.stream().mapToInt(fullQuery -> fullQuery.getArgs().length).sum(); List<Object> args = fullQueries.stream() .flatMap(fullQuery -> Stream.of(fullQuery.getArgs())) .collect(Collectors.toCollection(() -> new ArrayList<>(totalArgs))); String query = Joiner.on(") UNION ALL (") .appendTo(new StringBuilder("("), subQueries) .append(")") .append(" ORDER BY row_name ASC, col_name ASC") .toString(); return new FullQuery(query).withArgs(args); }
private FullQuery getQuery(boolean singleRow) { String pkIndex = PrimaryKeyConstraintNames.get(tableDetails.shortName); FullQuery.Builder queryBuilder = FullQuery.builder() .append("/* GET_CANDIDATE_CELLS_FOR_SWEEPING */ ") .append("SELECT * FROM (") .append(" SELECT /*+ INDEX_ASC(t ").append(pkIndex).append(") ") .append(" NO_INDEX_SS(t ").append(pkIndex).append(")") .append(" NO_INDEX_FFS(t ").append(pkIndex).append(") */") .append(" row_name, col_name, ts"); if (request.shouldCheckIfLatestValueIsEmpty()) { appendEmptyValueFlagSelector(queryBuilder); } queryBuilder .append(" FROM ").append(tableDetails.shortName).append(" t") .append(" WHERE ts < ? ", request.maxTimestampExclusive()); SweepQueryHelpers.appendIgnoredTimestampPredicate(request, queryBuilder); appendRangePredicates(singleRow, queryBuilder); return queryBuilder .append(" ORDER BY row_name, col_name, ts") .append(") WHERE rownum <= ").append(sqlRowLimit) .append(" ORDER BY row_name, col_name, ts") .build(); }
private FullQuery addRowTsArgs(FullQuery fullQuery, Iterable<Entry<byte[], Long>> rows) { for (Entry<byte[], Long> entry : rows) { fullQuery.withArgs(entry.getKey(), entry.getValue()); } return fullQuery; }
public FullQuery build() { // TODO(gbonik): remove new ArrayList<> once we get rid of withArgs methods return new FullQuery(queryBuilder.toString(), new ArrayList<>(argsBuilder.build())); } }
@Test public void startCellTsInclusiveForwardOracle() { FullQuery.Builder builder = FullQuery.builder(); RangePredicateHelper.create(false, DBType.ORACLE, builder).startCellTsInclusive(ROW_NAME, COL_NAME, TS); FullQuery query = builder.build(); assertThat(query.getQuery(), equalTo(" AND (row_name >= ? AND (row_name > ? OR col_name > ? OR (col_name = ? AND ts >= ?)))")); assertThat(query.getArgs(), arrayContaining(ROW_NAME, ROW_NAME, COL_NAME, COL_NAME, TS)); }
private FullQuery getNewOverflowQuery(ConnectionSupplier conns, TableReference tableRef, ArrayHandler arg) { String overflowTableName = getOverflowTableName(conns, tableRef); String query = " /* SELECT_OVERFLOW (" + overflowTableName + ") */ " + " SELECT" + " /*+ USE_NL(t o) LEADING(t o) INDEX(o " + PrimaryKeyConstraintNames.get(overflowTableName) + ") */ " + " o.id, o.val " + " FROM " + overflowTableName + " o," + " TABLE(CAST(? AS " + structArrayPrefix() + "CELL_TS_TABLE)) t " + " WHERE t.max_ts = o.id "; return new FullQuery(query).withArg(arg); }
@Override public FullQuery getAllCellQuery(Cell cell, long ts, boolean includeValue) { String query = " /* GET_ALL_ONE_CELL (" + tableName + ") */ " + " SELECT" + " /*+ INDEX(m " + PrimaryKeyConstraintNames.get(tableName) + ") */ " + " m.row_name, m.col_name, m.ts" + getValueSubselect("m", includeValue) + " FROM " + tableName + " m " + " WHERE m.row_name = ? " + " AND m.col_name = ? " + " AND m.ts < ? "; return new FullQuery(query).withArgs(cell.getRowName(), cell.getColumnName(), ts); }
@Override public FullQuery getRowsColumnRangeQuery( Map<byte[], BatchColumnRangeSelection> columnRangeSelectionsByRow, long ts) { List<String> subQueries = new ArrayList<>(columnRangeSelectionsByRow.size()); int totalArgs = 0; for (BatchColumnRangeSelection columnRangeSelection : columnRangeSelectionsByRow.values()) { totalArgs += 2 + ((columnRangeSelection.getStartCol().length > 0) ? 1 : 0) + ((columnRangeSelection.getEndCol().length > 0) ? 1 : 0); } List<Object> args = new ArrayList<>(totalArgs); for (Map.Entry<byte[], BatchColumnRangeSelection> entry : columnRangeSelectionsByRow.entrySet()) { FullQuery query = getRowsColumnRangeSubQuery(entry.getKey(), ts, entry.getValue()); subQueries.add(query.getQuery()); for (Object arg : query.getArgs()) { args.add(arg); } } String query = Joiner.on(") UNION ALL (").appendTo(new StringBuilder("("), subQueries).append(")") .append(" ORDER BY row_name ASC, col_name ASC").toString(); return new FullQuery(query).withArgs(args); }
private FullQuery getRangeQuery() { String direction = reverse ? "DESC" : "ASC"; FullQuery.Builder queryBuilder = FullQuery.builder() .append("/* GET_RANGE(").append(tableName).append(") */") .append("SELECT wrap.row_name, wrap.col_name, wrap.ts, wrap.val") .append(" FROM ").append(prefixedTableName).append(" wrap, (") .append(" SELECT row_name, col_name, MAX(ts) AS ts FROM ").append(prefixedTableName) .append(" WHERE ts < ? ", ts); RangePredicateHelper.create(reverse, DBType.POSTGRESQL, queryBuilder) .startCellInclusive(currentRowName, firstRowStartColumnInclusive) .endRowExclusive(endExclusive) .columnSelection(columnSelection); queryBuilder .append(" GROUP BY row_name, col_name") .append(" ORDER BY row_name ").append(direction).append(", col_name ").append(direction) .append(" LIMIT ").append(maxCellsPerPage) .append(" ) i") .append(" WHERE wrap.row_name = i.row_name") .append(" AND wrap.col_name = i.col_name") .append(" AND wrap.ts = i.ts") .append(" ORDER BY row_name ").append(direction).append(", col_name ").append(direction); return queryBuilder.build(); } }
private FullQuery addCellTsArgs(FullQuery fullQuery, Collection<Entry<Cell, Long>> cells) { for (Entry<Cell, Long> entry : cells) { Cell cell = entry.getKey(); fullQuery.withArgs(cell.getRowName(), cell.getColumnName(), entry.getValue()); } return fullQuery; }
@Override public FullQuery getAllCellsQuery(Collection<Entry<Cell, Long>> cells, boolean includeValue) { String query = " /* GET_ALL_CELLS (" + tableName + ") */ " + " SELECT m.row_name, m.col_name, m.ts" + (includeValue ? ", m.val " : " ") + " FROM " + prefixedTableName() + " m," + " (VALUES " + groupOfNumParams(3, Iterables.size(cells)) + ") t(row_name, col_name, ts) " + " WHERE m.row_name = t.row_name " + " AND m.col_name = t.col_name " + " AND m.ts < t.ts "; return addCellTsArgs(new FullQuery(query), cells); }
@Test public void startCellTsInclusiveReverseOracle() { FullQuery.Builder builder = FullQuery.builder(); RangePredicateHelper.create(true, DBType.ORACLE, builder).startCellTsInclusive(ROW_NAME, COL_NAME, TS); FullQuery query = builder.build(); assertThat(query.getQuery(), equalTo(" AND (row_name <= ? AND (row_name < ? OR col_name < ? OR (col_name = ? AND ts <= ?)))")); assertThat(query.getArgs(), arrayContaining(ROW_NAME, ROW_NAME, COL_NAME, COL_NAME, TS)); }
@Override public FullQuery getLatestRowsQuery(Iterable<byte[]> rows, long ts, ColumnSelection columns, boolean includeValue) { String query = " /* GET_LATEST_ROWS_INNER (" + tableName + ") */ " + " SELECT m.row_name, m.col_name, max(m.ts) as ts " + " FROM " + prefixedTableName() + " m " + " WHERE m.row_name IN " + numParams(Iterables.size(rows)) + " AND m.ts < ? " + (columns.allColumnsSelected() ? "" : " AND m.col_name IN " + numParams(Iterables.size(columns.getSelectedColumns()))) + " GROUP BY m.row_name, m.col_name "; query = wrapQueryWithIncludeValue("GET_LATEST_ROW", query, includeValue); FullQuery fullQuery = new FullQuery(query).withArgs(rows).withArg(ts); return columns.allColumnsSelected() ? fullQuery : fullQuery.withArgs(columns.getSelectedColumns()); }