@Override public PreparedStatement buildSql(Connection connection, JdbcSplit split, List<JdbcColumnHandle> columnHandles) throws SQLException { return new QueryBuilder(identifierQuote).buildSql( this, connection, split.getCatalogName(), split.getSchemaName(), split.getTableName(), columnHandles, split.getTupleDomain(), split.getAdditionalPredicate()); }
return domain.isNullAllowed() ? quote(columnName) + " IS NULL" : ALWAYS_FALSE; return domain.isNullAllowed() ? ALWAYS_TRUE : quote(columnName) + " IS NOT NULL"; switch (range.getLow().getBound()) { case ABOVE: rangeConjuncts.add(toPredicate(columnName, ">", range.getLow().getValue(), type, accumulator)); break; case EXACTLY: rangeConjuncts.add(toPredicate(columnName, ">=", range.getLow().getValue(), type, accumulator)); break; case BELOW: throw new IllegalArgumentException("High marker should never use ABOVE bound"); case EXACTLY: rangeConjuncts.add(toPredicate(columnName, "<=", range.getHigh().getValue(), type, accumulator)); break; case BELOW: rangeConjuncts.add(toPredicate(columnName, "<", range.getHigh().getValue(), type, accumulator)); break; default: disjuncts.add(toPredicate(columnName, "=", getOnlyElement(singleValues), type, accumulator)); bindValue(value, type, accumulator); disjuncts.add(quote(columnName) + " IN (" + values + ")"); disjuncts.add(quote(columnName) + " IS NULL");
private List<String> toConjuncts(List<JdbcColumnHandle> columns, TupleDomain<ColumnHandle> tupleDomain, List<TypeAndValue> accumulator) { ImmutableList.Builder<String> builder = ImmutableList.builder(); for (JdbcColumnHandle column : columns) { Type type = column.getColumnType(); if (isAcceptedType(type)) { Domain domain = tupleDomain.getDomains().get().get(column); if (domain != null) { builder.add(toPredicate(column.getColumnName(), domain, type, accumulator)); } } } return builder.build(); }
sql.append(quote(catalog)).append('.'); sql.append(quote(schema)).append('.'); sql.append(quote(table)); List<String> clauses = toConjuncts(columns, tupleDomain, accumulator); if (additionalPredicate.isPresent()) { clauses = ImmutableList.<String>builder()
private static void bindValue(Object value, Type type, List<TypeAndValue> accumulator) { checkArgument(isAcceptedType(type), "Can't handle type: %s", type); accumulator.add(new TypeAndValue(type, value)); } }
Joiner.on(", ").appendTo(sql, transform(columns, column -> quote(column.getColumnName()))); if (columns.isEmpty()) { sql.append("null"); sql.append(quote(catalog)).append('.'); sql.append(quote(schema)).append('.'); sql.append(quote(table)); List<String> clauses = toConjuncts(columns, tupleDomain, accumulator); if (!clauses.isEmpty()) { sql.append(" WHERE ")
private static String bindValue(Object value, Type type, List<TypeAndValue> accumulator) { checkArgument(isAcceptedType(type), "Can't handle type: %s", type); accumulator.add(new TypeAndValue(type, value)); return "?"; } }
@Test public void testEmptyBuildSql() throws SQLException { TupleDomain<ColumnHandle> tupleDomain = TupleDomain.withColumnDomains(ImmutableMap.of( columns.get(0), Domain.all(BIGINT), columns.get(1), Domain.onlyNull(DOUBLE))); Connection connection = database.getConnection(); try (PreparedStatement preparedStatement = new QueryBuilder("\"").buildSql(jdbcClient, connection, "", "", "test_table", columns, tupleDomain, Optional.empty()); ResultSet resultSet = preparedStatement.executeQuery()) { assertEquals(resultSet.next(), false); } }
return domain.isNullAllowed() ? quote(columnName) + " IS NULL" : "FALSE"; return domain.isNullAllowed() ? "TRUE" : quote(columnName) + " IS NOT NULL"; switch (range.getLow().getBound()) { case ABOVE: rangeConjuncts.add(toPredicate(columnName, ">", range.getLow().getValue(), type, accumulator)); break; case EXACTLY: rangeConjuncts.add(toPredicate(columnName, ">=", range.getLow().getValue(), type, accumulator)); break; case BELOW: throw new IllegalArgumentException("High Marker should never use ABOVE bound: " + range); case EXACTLY: rangeConjuncts.add(toPredicate(columnName, "<=", range.getHigh().getValue(), type, accumulator)); break; case BELOW: rangeConjuncts.add(toPredicate(columnName, "<", range.getHigh().getValue(), type, accumulator)); break; default: disjuncts.add(toPredicate(columnName, "=", getOnlyElement(singleValues), type, accumulator)); disjuncts.add(quote(columnName) + " IN (" + Joiner.on(",").join(transform(singleValues, value -> bindValue(value, type, accumulator))) + ")"); disjuncts.add(quote(columnName) + " IS NULL");
private List<String> toConjuncts(List<JdbcColumnHandle> columns, TupleDomain<ColumnHandle> tupleDomain, List<TypeAndValue> accumulator) { ImmutableList.Builder<String> builder = ImmutableList.builder(); for (JdbcColumnHandle column : columns) { Type type = column.getColumnType(); if (isAcceptedType(type)) { Domain domain = tupleDomain.getDomains().get().get(column); if (domain != null) { builder.add(toPredicate(column.getColumnName(), domain, type, accumulator)); } } } return builder.build(); }
@Test public void testBuildSqlWithFloat() throws SQLException { TupleDomain<ColumnHandle> tupleDomain = TupleDomain.withColumnDomains(ImmutableMap.of( columns.get(10), Domain.create(SortedRangeSet.copyOf(REAL, ImmutableList.of( Range.equal(REAL, (long) floatToRawIntBits(100.0f + 0)), Range.equal(REAL, (long) floatToRawIntBits(100.008f + 0)), Range.equal(REAL, (long) floatToRawIntBits(100.0f + 14)))), false))); Connection connection = database.getConnection(); try (PreparedStatement preparedStatement = new QueryBuilder("\"").buildSql(jdbcClient, connection, "", "", "test_table", columns, tupleDomain, Optional.empty()); ResultSet resultSet = preparedStatement.executeQuery()) { ImmutableSet.Builder<Long> longBuilder = ImmutableSet.builder(); ImmutableSet.Builder<Float> floatBuilder = ImmutableSet.builder(); while (resultSet.next()) { longBuilder.add((Long) resultSet.getObject("col_0")); floatBuilder.add((Float) resultSet.getObject("col_10")); } assertEquals(longBuilder.build(), ImmutableSet.of(0L, 14L)); assertEquals(floatBuilder.build(), ImmutableSet.of(100.0f, 114.0f)); } }
@Test public void testBuildSqlWithVarchar() throws SQLException { TupleDomain<ColumnHandle> tupleDomain = TupleDomain.withColumnDomains(ImmutableMap.of( columns.get(3), Domain.create(SortedRangeSet.copyOf(VARCHAR, ImmutableList.of( Range.range(VARCHAR, utf8Slice("test_str_700"), true, utf8Slice("test_str_702"), false), Range.equal(VARCHAR, utf8Slice("test_str_180")), Range.equal(VARCHAR, utf8Slice("test_str_196")))), false))); Connection connection = database.getConnection(); try (PreparedStatement preparedStatement = new QueryBuilder("\"").buildSql(jdbcClient, connection, "", "", "test_table", columns, tupleDomain, Optional.empty()); ResultSet resultSet = preparedStatement.executeQuery()) { ImmutableSet.Builder<String> builder = ImmutableSet.builder(); while (resultSet.next()) { builder.add((String) resultSet.getObject("col_3")); } assertEquals(builder.build(), ImmutableSet.of("test_str_700", "test_str_701", "test_str_180", "test_str_196")); assertContains(preparedStatement.toString(), "\"col_3\" >= ?"); assertContains(preparedStatement.toString(), "\"col_3\" < ?"); assertContains(preparedStatement.toString(), "\"col_3\" IN (?,?)"); } }
@Test public void testBuildSqlWithChar() throws SQLException { CharType charType = CharType.createCharType(0); TupleDomain<ColumnHandle> tupleDomain = TupleDomain.withColumnDomains(ImmutableMap.of( columns.get(11), Domain.create(SortedRangeSet.copyOf(charType, ImmutableList.of( Range.range(charType, utf8Slice("test_str_700"), true, utf8Slice("test_str_702"), false), Range.equal(charType, utf8Slice("test_str_180")), Range.equal(charType, utf8Slice("test_str_196")))), false))); Connection connection = database.getConnection(); try (PreparedStatement preparedStatement = new QueryBuilder("\"").buildSql(jdbcClient, connection, "", "", "test_table", columns, tupleDomain, Optional.empty()); ResultSet resultSet = preparedStatement.executeQuery()) { ImmutableSet.Builder<String> builder = ImmutableSet.builder(); while (resultSet.next()) { builder.add((String) resultSet.getObject("col_11")); } assertEquals(builder.build(), ImmutableSet.of("test_str_700", "test_str_701", "test_str_180", "test_str_196")); assertContains(preparedStatement.toString(), "\"col_11\" >= ?"); assertContains(preparedStatement.toString(), "\"col_11\" < ?"); assertContains(preparedStatement.toString(), "\"col_11\" IN (?,?)"); } }
@Test public void testBuildSqlWithTimestamp() throws SQLException { TupleDomain<ColumnHandle> tupleDomain = TupleDomain.withColumnDomains(ImmutableMap.of( columns.get(6), Domain.create(SortedRangeSet.copyOf(TIMESTAMP, ImmutableList.of( Range.equal(TIMESTAMP, toTimestamp(2016, 6, 3, 0, 23, 37).getTime()), Range.equal(TIMESTAMP, toTimestamp(2016, 10, 19, 16, 23, 37).getTime()), Range.range(TIMESTAMP, toTimestamp(2016, 6, 7, 8, 23, 37).getTime(), false, toTimestamp(2016, 6, 9, 12, 23, 37).getTime(), true))), false))); Connection connection = database.getConnection(); try (PreparedStatement preparedStatement = new QueryBuilder("\"").buildSql(jdbcClient, connection, "", "", "test_table", columns, tupleDomain, Optional.empty()); ResultSet resultSet = preparedStatement.executeQuery()) { ImmutableSet.Builder<Timestamp> builder = ImmutableSet.builder(); while (resultSet.next()) { builder.add((Timestamp) resultSet.getObject("col_6")); } assertEquals(builder.build(), ImmutableSet.of( toTimestamp(2016, 6, 3, 0, 23, 37), toTimestamp(2016, 6, 8, 10, 23, 37), toTimestamp(2016, 6, 9, 12, 23, 37), toTimestamp(2016, 10, 19, 16, 23, 37))); assertContains(preparedStatement.toString(), "\"col_6\" > ?"); assertContains(preparedStatement.toString(), "\"col_6\" <= ?"); assertContains(preparedStatement.toString(), "\"col_6\" IN (?,?)"); } }
try (PreparedStatement preparedStatement = new QueryBuilder("\"").buildSql(jdbcClient, connection, "", "", "test_table", columns, tupleDomain, Optional.empty()); ResultSet resultSet = preparedStatement.executeQuery()) { ImmutableSet.Builder<Date> dateBuilder = ImmutableSet.builder();
try (PreparedStatement preparedStatement = new QueryBuilder("\"").buildSql(jdbcClient, connection, "", "", "test_table", columns, tupleDomain, Optional.empty()); ResultSet resultSet = preparedStatement.executeQuery()) { ImmutableSet.Builder<Long> builder = ImmutableSet.builder();
@Override public PreparedStatement buildSql(JdbcSplit split, List<JdbcColumnHandle> columnHandles) throws SQLException { return new QueryBuilder(identifierQuote).buildSql( getConnection(split), split.getCatalogName(), split.getSchemaName(), split.getTableName(), columnHandles, split.getTupleDomain()); }
@Test public void testEmptyBuildSql() throws SQLException { TupleDomain<ColumnHandle> tupleDomain = TupleDomain.withColumnDomains(ImmutableMap.of( columns.get(0), Domain.all(BIGINT), columns.get(1), Domain.onlyNull(DOUBLE))); Connection connection = database.getConnection(); try (PreparedStatement preparedStatement = new QueryBuilder("\"").buildSql(jdbcClient, connection, "", "", "test_table", columns, tupleDomain); ResultSet resultSet = preparedStatement.executeQuery()) { assertEquals(resultSet.next(), false); } }