@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); }
@Override public FullQuery getRowsColumnRangeCountsQuery( Iterable<byte[]> rows, long ts, ColumnRangeSelection columnRangeSelection) { String query = " /* GET_ROWS_COLUMN_RANGE_COUNT(" + tableName + ") */" + " SELECT m.row_name, COUNT(m.col_name) AS column_count " + " FROM " + prefixedTableName() + " m " + " WHERE m.row_name IN " + numParams(Iterables.size(rows)) + " AND m.ts < ? " + (columnRangeSelection.getStartCol().length > 0 ? " AND m.col_name >= ?" : "") + (columnRangeSelection.getEndCol().length > 0 ? " AND m.col_name < ?" : "") + " GROUP BY m.row_name"; FullQuery fullQuery = new FullQuery(query).withArgs(rows).withArg(ts); if (columnRangeSelection.getStartCol().length > 0) { fullQuery = fullQuery.withArg(columnRangeSelection.getStartCol()); } if (columnRangeSelection.getEndCol().length > 0) { fullQuery = fullQuery.withArg(columnRangeSelection.getEndCol()); } return fullQuery; }
@Override protected FullQuery getRowsColumnRangeSubQuery( byte[] row, long ts, BatchColumnRangeSelection columnRangeSelection) { String query = " /* GET_ROWS_COLUMN_RANGE (" + tableName + ") */ " + "SELECT s.row_name, s.col_name, s.ts" + getValueSubselect("s", true) + " FROM ( SELECT m.row_name, m.col_name, max(m.ts) as ts" + getValueSubselectForGroupBy("m") + " FROM " + tableName + " m" + " WHERE m.row_name = ?" + " AND m.ts < ? " + (columnRangeSelection.getStartCol().length > 0 ? " AND m.col_name >= ?" : "") + (columnRangeSelection.getEndCol().length > 0 ? " AND m.col_name < ?" : "") + " GROUP BY m.row_name, m.col_name" + " ORDER BY m.row_name ASC, m.col_name ASC ) s WHERE rownum <= " + columnRangeSelection.getBatchHint(); FullQuery fullQuery = new FullQuery(query).withArg(row).withArg(ts); if (columnRangeSelection.getStartCol().length > 0) { fullQuery = fullQuery.withArg(columnRangeSelection.getStartCol()); } if (columnRangeSelection.getEndCol().length > 0) { fullQuery = fullQuery.withArg(columnRangeSelection.getEndCol()); } return fullQuery; }
@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()); }
@Override protected FullQuery getRowsColumnRangeSubQuery( byte[] row, long ts, BatchColumnRangeSelection columnRangeSelection) { String query = " /* GET_ROWS_COLUMN_RANGE (" + tableName + ") */ " + " SELECT m.row_name, m.col_name, max(m.ts) as ts" + " FROM " + prefixedTableName() + " m " + " WHERE m.row_name = ? " + " AND m.ts < ? " + (columnRangeSelection.getStartCol().length > 0 ? " AND m.col_name >= ?" : "") + (columnRangeSelection.getEndCol().length > 0 ? " AND m.col_name < ?" : "") + " GROUP BY m.row_name, m.col_name" + " ORDER BY m.col_name ASC LIMIT " + columnRangeSelection.getBatchHint(); FullQuery fullQuery = new FullQuery(wrapQueryWithIncludeValue("GET_ROWS_COLUMN_RANGE", query, true)) .withArg(row) .withArg(ts); if (columnRangeSelection.getStartCol().length > 0) { fullQuery = fullQuery.withArg(columnRangeSelection.getStartCol()); } if (columnRangeSelection.getEndCol().length > 0) { fullQuery = fullQuery.withArg(columnRangeSelection.getEndCol()); } return fullQuery; }
@Override protected FullQuery getRowsColumnRangeFullyLoadedRowsSubQuery( List<byte[]> rows, long ts, ColumnRangeSelection columnRangeSelection) { String query = " /* GET_ROWS_COLUMN_RANGE_FULLY_LOADED_ROW (" + 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 < ? " + (columnRangeSelection.getStartCol().length > 0 ? " AND m.col_name >= ?" : "") + (columnRangeSelection.getEndCol().length > 0 ? " AND m.col_name < ?" : "") + " GROUP BY m.row_name, m.col_name" + " ORDER BY m.row_name ASC, m.col_name ASC"; String wrappedQuery = wrapQueryWithIncludeValue("GET_ROWS_COLUMN_RANGE_FULLY_LOADED_ROW", query, true); FullQuery fullQuery = new FullQuery(wrappedQuery).withArgs(rows).withArg(ts); if (columnRangeSelection.getStartCol().length > 0) { fullQuery = fullQuery.withArg(columnRangeSelection.getStartCol()); } if (columnRangeSelection.getEndCol().length > 0) { fullQuery = fullQuery.withArg(columnRangeSelection.getEndCol()); } return fullQuery; } }
@Override public FullQuery getLatestCellsQuery(Iterable<Cell> cells, long ts, boolean includeValue) { String query = " /* GET_LATEST_CELLS_INNER (" + tableName + ") */ " + " SELECT m.row_name, m.col_name, max(m.ts) as ts " + " 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 < ? " + " GROUP BY m.row_name, m.col_name "; query = wrapQueryWithIncludeValue("GET_LATEST_CELLS", query, includeValue); return addCellArgs(new FullQuery(query), cells).withArg(ts); }
@Override public FullQuery getRowsColumnRangeCountsQuery( Iterable<byte[]> rows, long ts, ColumnRangeSelection columnRangeSelection) { String query = " /* GET_ROWS_COLUMN_RANGE_COUNT(" + tableName + ") */" + " SELECT m.row_name, COUNT(m.col_name) AS column_count " + " FROM " + tableName + " m, TABLE(CAST(? AS " + structArrayPrefix() + "CELL_TS_TABLE)) t " + " WHERE m.row_name = t.row_name " + " AND m.ts < ? " + (columnRangeSelection.getStartCol().length > 0 ? " AND m.col_name >= ?" : "") + (columnRangeSelection.getEndCol().length > 0 ? " AND m.col_name < ?" : "") + " GROUP BY m.row_name"; FullQuery fullQuery = new FullQuery(query).withArgs(rowsToOracleArray(rows), ts); if (columnRangeSelection.getStartCol().length > 0) { fullQuery = fullQuery.withArg(columnRangeSelection.getStartCol()); } if (columnRangeSelection.getEndCol().length > 0) { fullQuery = fullQuery.withArg(columnRangeSelection.getEndCol()); } return fullQuery; }
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 protected FullQuery getRowsColumnRangeFullyLoadedRowsSubQuery( List<byte[]> rows, long ts, ColumnRangeSelection columnRangeSelection) { String query = " /* GET_ROWS_COLUMN_RANGE_FULLY_LOADED_ROWS (" + tableName + ") */ " + "SELECT * FROM ( SELECT m.row_name, m.col_name, max(m.ts) as ts" + " FROM " + tableName + " m, TABLE(CAST(? AS " + structArrayPrefix() + "CELL_TS_TABLE)) t " + " WHERE m.row_name = t.row_name " + " AND m.ts < ? " + (columnRangeSelection.getStartCol().length > 0 ? " AND m.col_name >= ?" : "") + (columnRangeSelection.getEndCol().length > 0 ? " AND m.col_name < ?" : "") + " GROUP BY m.row_name, m.col_name" + " ORDER BY m.row_name ASC, m.col_name ASC )"; String wrappedQuery = wrapQueryWithIncludeValue("GET_ROWS_COLUMN_RANGE_FULLY_LOADED_ROWS", query, true); FullQuery fullQuery = new FullQuery(wrappedQuery).withArgs(rowsToOracleArray(rows), ts); if (columnRangeSelection.getStartCol().length > 0) { fullQuery = fullQuery.withArg(columnRangeSelection.getStartCol()); } if (columnRangeSelection.getEndCol().length > 0) { fullQuery = fullQuery.withArg(columnRangeSelection.getEndCol()); } return fullQuery; }
private FullQuery getOldOverflowQuery(ArrayHandler arg) { String query = " /* SELECT_OVERFLOW */ " + " SELECT" + " /*+ USE_NL(t o) LEADING(t o) INDEX(o " + PrimaryKeyConstraintNames.get(config.singleOverflowTable()) + ") */ " + " o.id, o.val " + " FROM " + config.singleOverflowTable() + " o, " + " TABLE(CAST(? AS " + structArrayPrefix() + "CELL_TS_TABLE)) t " + " WHERE t.max_ts = o.id "; return new FullQuery(query).withArg(arg); }
@Override public FullQuery getAllRowQuery(byte[] row, long ts, ColumnSelection columns, boolean includeValue) { String query = " /* GET_ALL_ONE_ROW (" + 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.ts < ? " + (columns.allColumnsSelected() ? "" : " AND EXISTS (" + "SELECT" + " /*+ NL_SJ */" + " 1" + " FROM TABLE(CAST(? AS " + structArrayPrefix() + "CELL_TS_TABLE))" + " WHERE row_name = m.col_name) "); FullQuery fullQuery = new FullQuery(query).withArgs(row, ts); return columns.allColumnsSelected() ? fullQuery : fullQuery.withArg(rowsToOracleArray(columns.getSelectedColumns())); }
@Override public FullQuery getAllCellsQuery(Collection<Map.Entry<Cell, Long>> cells, boolean includeValue) { String query = " /* GET_ALL_CELLS_MANY_BOUNDS (" + tableName + ") */ " + " SELECT" + " /*+ USE_NL(t m) CARDINALITY(t 1) CARDINALITY(m 10) INDEX(m " + PrimaryKeyConstraintNames.get(tableName) + ") */ " + " m.row_name, m.col_name, m.ts" + getValueSubselect("m", includeValue) + " FROM " + tableName + " m, TABLE(CAST(? AS " + structArrayPrefix() + "CELL_TS_TABLE)) t " + " WHERE m.row_name = t.row_name " + " AND m.col_name = t.col_name " + " AND m.ts < t.max_ts "; return new FullQuery(query).withArg(cellsAndTimestampsToOracleArray(cells)); }
@Override public FullQuery getLatestRowQuery(byte[] row, long ts, ColumnSelection columns, boolean includeValue) { String query = " /* GET_LATEST_ONE_ROW_INNER (" + tableName + ") */ " + " SELECT" + " /*+ USE_NL(t m) CARDINALITY(t 1) CARDINALITY(m 10) INDEX(m " + PrimaryKeyConstraintNames.get(tableName) + ") */ " + " m.row_name, m.col_name, max(m.ts) as ts " + " FROM " + tableName + " m " + " WHERE m.row_name = ? " + " AND m.ts < ? " + (columns.allColumnsSelected() ? "" : " AND EXISTS (" + "SELECT " + " /*+ NL_SJ */" + " 1" + " FROM TABLE(CAST(? AS " + structArrayPrefix() + "CELL_TS_TABLE))" + " WHERE row_name = m.col_name)") + " GROUP BY m.row_name, m.col_name"; query = wrapQueryWithIncludeValue("GET_LATEST_ONE_ROW", query, includeValue); FullQuery fullQuery = new FullQuery(query).withArgs(row, ts); return columns.allColumnsSelected() ? fullQuery : fullQuery.withArg(rowsToOracleArray(columns.getSelectedColumns())); }
@Override public FullQuery getLatestCellsQuery(Collection<Map.Entry<Cell, Long>> cells, boolean includeValue) { String query = " /* GET_LATEST_CELLS_MANY_BOUNDS_INNER (" + tableName + ") */ " + " SELECT" + " /*+ USE_NL(t m) CARDINALITY(t 1) CARDINALITY(m 10) INDEX(m " + PrimaryKeyConstraintNames.get(tableName) + ") */ " + " m.row_name, m.col_name, max(m.ts) as ts " + " FROM " + tableName + " m, TABLE(CAST(? AS " + structArrayPrefix() + "CELL_TS_TABLE)) t " + " WHERE m.row_name = t.row_name " + " AND m.col_name = t.col_name " + " AND m.ts < t.max_ts " + " GROUP BY m.row_name, m.col_name"; query = wrapQueryWithIncludeValue("GET_LATEST_CELLS_MANY_BOUNDS", query, includeValue); return new FullQuery(query).withArg(cellsAndTimestampsToOracleArray(cells)); }
@Override public FullQuery getAllRowsQuery(Iterable<byte[]> rows, long ts, ColumnSelection columns, boolean includeValue) { String query = " /* GET_ALL_ROWS_SINGLE_BOUND (" + tableName + ") */ " + " SELECT" + " /*+ USE_NL(t m) CARDINALITY(t 1) CARDINALITY(m 10) INDEX(m " + PrimaryKeyConstraintNames.get(tableName) + ") */ " + " m.row_name, m.col_name, m.ts" + getValueSubselect("m", includeValue) + " FROM " + tableName + " m, TABLE(CAST(? AS " + structArrayPrefix() + "CELL_TS_TABLE)) t " + " WHERE m.row_name = t.row_name " + " AND m.ts < ? " + (columns.allColumnsSelected() ? "" : " AND EXISTS (" + "SELECT" + " /*+ NL_SJ */" + " 1" + " FROM TABLE(CAST(? AS " + structArrayPrefix() + "CELL_TS_TABLE))" + " WHERE row_name = m.col_name) "); FullQuery fullQuery = new FullQuery(query).withArgs(rowsToOracleArray(rows), ts); return columns.allColumnsSelected() ? fullQuery : fullQuery.withArg(rowsToOracleArray(columns.getSelectedColumns())); }
@Override public FullQuery getAllRowsQuery(Collection<Map.Entry<byte[], Long>> rows, ColumnSelection columns, boolean includeValue) { String query = " /* GET_ALL_ROWS_MANY_BOUNDS (" + tableName + ") */ " + " SELECT" + " /*+ USE_NL(t m) CARDINALITY(t 1) CARDINALITY(m 10) INDEX(m " + PrimaryKeyConstraintNames.get(tableName) + ") */ " + " m.row_name, m.col_name, m.ts" + getValueSubselect("m", includeValue) + " FROM " + tableName + " m, TABLE(CAST(? AS " + structArrayPrefix() + "CELL_TS_TABLE)) t " + " WHERE m.row_name = t.row_name " + " AND m.ts < t.max_ts " + (columns.allColumnsSelected() ? "" : " AND EXISTS (" + "SELECT" + " /*+ NL_SJ */" + " 1" + " FROM TABLE(CAST(? AS " + structArrayPrefix() + "CELL_TS_TABLE))" + " WHERE row_name = m.col_name) "); FullQuery fullQuery = new FullQuery(query).withArg(rowsAndTimestampsToOracleArray(rows)); return columns.allColumnsSelected() ? fullQuery : fullQuery.withArg(rowsToOracleArray(columns.getSelectedColumns())); }
@Override public FullQuery getLatestRowsQuery(Iterable<byte[]> rows, long ts, ColumnSelection columns, boolean includeValue) { String query = " /* GET_LATEST_ROWS_SINGLE_BOUND_INNER (" + tableName + ") */ " + " SELECT" + " /*+ USE_NL(t m) CARDINALITY(t 1) CARDINALITY(m 10) INDEX(m " + PrimaryKeyConstraintNames.get(tableName) + ") */ " + " m.row_name, m.col_name, max(m.ts) as ts " + " FROM " + tableName + " m, TABLE(CAST(? AS " + structArrayPrefix() + "CELL_TS_TABLE)) t " + " WHERE m.row_name = t.row_name " + " AND m.ts < ? " + (columns.allColumnsSelected() ? "" : " AND EXISTS (" + "SELECT" + " /*+ NL_SJ */" + " 1" + " FROM TABLE(CAST(? AS " + structArrayPrefix() + "CELL_TS_TABLE))" + " WHERE row_name = m.col_name) ") + " GROUP BY m.row_name, m.col_name"; query = wrapQueryWithIncludeValue("GET_LATEST_ROWS_SINGLE_BOUND", query, includeValue); FullQuery fullQuery = new FullQuery(query).withArgs(rowsToOracleArray(rows), ts); return columns.allColumnsSelected() ? fullQuery : fullQuery.withArg(rowsToOracleArray(columns.getSelectedColumns())); }
@Override public FullQuery getLatestRowsQuery(Collection<Map.Entry<byte[], Long>> rows, ColumnSelection columns, boolean includeValue) { String query = " /* GET_LATEST_ROWS_MANY_BOUNDS_INNER (" + tableName + ") */ " + " SELECT" + " /*+ USE_NL(t m) CARDINALITY(t 1) CARDINALITY(m 10) INDEX(m " + PrimaryKeyConstraintNames.get(tableName) + ") */ " + " m.row_name, m.col_name, max(m.ts) as ts " + " FROM " + tableName + " m, TABLE(CAST(? AS " + structArrayPrefix() + "CELL_TS_TABLE)) t " + " WHERE m.row_name = t.row_name " + " AND m.ts < t.max_ts " + (columns.allColumnsSelected() ? "" : " AND EXISTS (" + "SELECT" + " /*+ NL_SJ */" + " 1" + " FROM TABLE(CAST(? AS " + structArrayPrefix() + "CELL_TS_TABLE))" + " WHERE row_name = m.col_name) ") + " GROUP BY m.row_name, m.col_name"; query = wrapQueryWithIncludeValue("GET_LATEST_ROWS_MANY_BOUNDS", query, includeValue); FullQuery fullQuery = new FullQuery(query).withArg(rowsAndTimestampsToOracleArray(rows)); return columns.allColumnsSelected() ? fullQuery : fullQuery.withArg(rowsToOracleArray(columns.getSelectedColumns())); }