This is an automated email from the ASF dual-hosted git repository. bchapuis pushed a commit to branch optimize-postgres-tilestore in repository https://gitbox.apache.org/repos/asf/incubator-baremaps.git
commit 74c2f1de93e7a92d15859d3ce7c702115717ac4e Author: Bertil Chapuis <[email protected]> AuthorDate: Sat Oct 21 13:10:52 2023 +0200 Improve tilestore queries and remove jsqlparser --- baremaps-core/pom.xml | 4 - .../org/apache/baremaps/iploc/IpLocRepository.java | 1 - .../baremaps/stream/PartitionedSpliterator.java | 16 +- .../org/apache/baremaps/stream/StreamUtils.java | 3 +- .../org/apache/baremaps/tilestore/TileEntry.java} | 29 ++- .../org/apache/baremaps/tilestore/TileStore.java | 41 ++++ .../baremaps/tilestore/mbtiles/MBTilesStore.java | 43 +++- .../baremaps/tilestore/postgres/PostgresGroup.java | 111 --------- .../baremaps/tilestore/postgres/PostgresQuery.java | 131 ---------- .../tilestore/postgres/PostgresQueryGenerator.java | 8 +- .../tilestore/postgres/PostgresTileStore.java | 271 +++++++-------------- .../org/apache/baremaps/utils/SqliteUtils.java | 15 +- .../baremaps/workflow/tasks/ExportVectorTiles.java | 34 ++- .../stream/PartitionedSpliteratorTest.java | 20 +- .../apache/baremaps/stream/StreamUtilsTest.java | 4 +- .../tilestore/postgres/JSQLParserTest.java | 64 ----- .../postgres/PostgresQueryGeneratorTest.java | 3 +- .../postgres/PostgresQueryParserTest.java | 107 -------- .../tilestore/postgres/PostgresTileStoreTest.java | 85 +++++-- pom.xml | 8 +- 20 files changed, 314 insertions(+), 684 deletions(-) diff --git a/baremaps-core/pom.xml b/baremaps-core/pom.xml index f7d3c99f..e4b75527 100644 --- a/baremaps-core/pom.xml +++ b/baremaps-core/pom.xml @@ -54,10 +54,6 @@ limitations under the License. <groupId>com.github.ben-manes.caffeine</groupId> <artifactId>caffeine</artifactId> </dependency> - <dependency> - <groupId>com.github.jsqlparser</groupId> - <artifactId>jsqlparser</artifactId> - </dependency> <dependency> <groupId>com.google.guava</groupId> <artifactId>guava</artifactId> diff --git a/baremaps-core/src/main/java/org/apache/baremaps/iploc/IpLocRepository.java b/baremaps-core/src/main/java/org/apache/baremaps/iploc/IpLocRepository.java index f3782730..cc726d9f 100644 --- a/baremaps-core/src/main/java/org/apache/baremaps/iploc/IpLocRepository.java +++ b/baremaps-core/src/main/java/org/apache/baremaps/iploc/IpLocRepository.java @@ -217,7 +217,6 @@ public final class IpLocRepository { */ public void save(Stream<IpLocObject> ipLocObjects) { StreamUtils.partition(ipLocObjects, 100) - .map(Stream::toList) .forEach(this::save); } } diff --git a/baremaps-core/src/main/java/org/apache/baremaps/stream/PartitionedSpliterator.java b/baremaps-core/src/main/java/org/apache/baremaps/stream/PartitionedSpliterator.java index ab0079dd..de42729a 100644 --- a/baremaps-core/src/main/java/org/apache/baremaps/stream/PartitionedSpliterator.java +++ b/baremaps-core/src/main/java/org/apache/baremaps/stream/PartitionedSpliterator.java @@ -19,6 +19,8 @@ package org.apache.baremaps.stream; +import java.util.ArrayList; +import java.util.List; import java.util.Spliterator; import java.util.function.Consumer; import java.util.stream.Stream; @@ -28,7 +30,7 @@ import java.util.stream.Stream; * * @param <T> the type of elements returned by this {@code Spliterator} */ -public class PartitionedSpliterator<T> implements Spliterator<Stream<T>> { +public class PartitionedSpliterator<T> implements Spliterator<List<T>> { private final Spliterator<T> spliterator; @@ -47,23 +49,23 @@ public class PartitionedSpliterator<T> implements Spliterator<Stream<T>> { /** {@inheritDoc} */ @Override - public boolean tryAdvance(Consumer<? super Stream<T>> action) { - Stream.Builder<T> partition = Stream.builder(); + public boolean tryAdvance(Consumer<? super List<T>> action) { + var list = new ArrayList<T>(partitionSize); int size = 0; - while (size < partitionSize && spliterator.tryAdvance(partition::add)) { + while (size < partitionSize && spliterator.tryAdvance(list::add)) { size++; } if (size == 0) { return false; } - action.accept(partition.build()); + action.accept(list); return true; } /** {@inheritDoc} */ @Override - public Spliterator<Stream<T>> trySplit() { - HoldingConsumer<Stream<T>> consumer = new HoldingConsumer<>(); + public Spliterator<List<T>> trySplit() { + HoldingConsumer<List<T>> consumer = new HoldingConsumer<>(); tryAdvance(consumer); return Stream.ofNullable(consumer.value()).spliterator(); } diff --git a/baremaps-core/src/main/java/org/apache/baremaps/stream/StreamUtils.java b/baremaps-core/src/main/java/org/apache/baremaps/stream/StreamUtils.java index 17397487..349a2c06 100644 --- a/baremaps-core/src/main/java/org/apache/baremaps/stream/StreamUtils.java +++ b/baremaps-core/src/main/java/org/apache/baremaps/stream/StreamUtils.java @@ -20,6 +20,7 @@ package org.apache.baremaps.stream; import java.util.Iterator; +import java.util.List; import java.util.Spliterator; import java.util.Spliterators; import java.util.concurrent.CompletableFuture; @@ -190,7 +191,7 @@ public class StreamUtils { } /** Partition the provided stream according to a partition size. */ - public static <T> Stream<Stream<T>> partition( + public static <T> Stream<List<T>> partition( Stream<T> stream, int partitionSize) { return StreamSupport.stream( diff --git a/baremaps-core/src/test/java/org/apache/baremaps/stream/StreamUtilsTest.java b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/TileEntry.java similarity index 60% copy from baremaps-core/src/test/java/org/apache/baremaps/stream/StreamUtilsTest.java copy to baremaps-core/src/main/java/org/apache/baremaps/tilestore/TileEntry.java index eb2ecc0b..58333c7a 100644 --- a/baremaps-core/src/test/java/org/apache/baremaps/stream/StreamUtilsTest.java +++ b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/TileEntry.java @@ -15,22 +15,25 @@ * limitations under the License. */ -package org.apache.baremaps.stream; +package org.apache.baremaps.tilestore; -import static org.junit.jupiter.api.Assertions.assertEquals; +import java.nio.ByteBuffer; -import java.util.List; -import java.util.stream.Collectors; -import java.util.stream.IntStream; -import org.junit.jupiter.api.Test; +public class TileEntry { -class StreamUtilsTest { + private final TileCoord tileCoord; + private final ByteBuffer byteBuffer; - @Test - void partition() { - List<Integer> list = IntStream.range(0, 100).boxed().toList(); - List<List<Integer>> partitions = StreamUtils.partition(list.stream(), 10) - .map(stream -> stream.collect(Collectors.toList())).toList(); - assertEquals(partitions.size(), 10); + public TileEntry(TileCoord tileCoord, ByteBuffer byteBuffer) { + this.tileCoord = tileCoord; + this.byteBuffer = byteBuffer; + } + + public TileCoord getTileCoord() { + return tileCoord; + } + + public ByteBuffer getByteBuffer() { + return byteBuffer; } } diff --git a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/TileStore.java b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/TileStore.java index 5d2b1a2f..c0332150 100644 --- a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/TileStore.java +++ b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/TileStore.java @@ -20,6 +20,8 @@ package org.apache.baremaps.tilestore; import java.nio.ByteBuffer; +import java.util.ArrayList; +import java.util.List; /** Represents a store for tiles. */ public interface TileStore { @@ -33,6 +35,21 @@ public interface TileStore { */ ByteBuffer read(TileCoord tileCoord) throws TileStoreException; + /** + * Reads the content of a list of tiles. + * + * @param tileCoords the tile coordinates + * @return the content of the tiles + * @throws TileStoreException + */ + default List<ByteBuffer> read(List<TileCoord> tileCoords) throws TileStoreException { + var blobs = new ArrayList<ByteBuffer>(); + for (var tileCoord : tileCoords) { + blobs.add(read(tileCoord)); + } + return blobs; + } + /** * Writes the content of a tile. * @@ -42,6 +59,18 @@ public interface TileStore { */ void write(TileCoord tileCoord, ByteBuffer blob) throws TileStoreException; + /** + * Writes the content of a list of tiles. + * + * @param entries the tile entries + * @throws TileStoreException + */ + default void write(List<TileEntry> entries) throws TileStoreException { + for (var entry : entries) { + write(entry.getTileCoord(), entry.getByteBuffer()); + } + } + /** * Deletes the content of a tile. * @@ -49,4 +78,16 @@ public interface TileStore { * @throws TileStoreException */ void delete(TileCoord tileCoord) throws TileStoreException; + + /** + * Deletes the content of a list of tiles. + * + * @param tileCoords the tile coordinates + * @throws TileStoreException + */ + default void delete(List<TileCoord> tileCoords) throws TileStoreException { + for (var tileCoord : tileCoords) { + delete(tileCoord); + } + } } diff --git a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/mbtiles/MBTilesStore.java b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/mbtiles/MBTilesStore.java index 22baf690..b6560778 100644 --- a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/mbtiles/MBTilesStore.java +++ b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/mbtiles/MBTilesStore.java @@ -18,7 +18,6 @@ package org.apache.baremaps.tilestore.mbtiles; - import java.io.IOException; import java.nio.ByteBuffer; import java.sql.Connection; @@ -27,9 +26,11 @@ import java.sql.ResultSet; import java.sql.SQLException; import java.sql.Statement; import java.util.HashMap; +import java.util.List; import java.util.Map; import javax.sql.DataSource; import org.apache.baremaps.tilestore.TileCoord; +import org.apache.baremaps.tilestore.TileEntry; import org.apache.baremaps.tilestore.TileStore; import org.apache.baremaps.tilestore.TileStoreException; @@ -40,7 +41,7 @@ import org.apache.baremaps.tilestore.TileStoreException; */ public class MBTilesStore implements TileStore { - private static final String CREATE_TABLE_METADATA = + public static final String CREATE_TABLE_METADATA = "CREATE TABLE IF NOT EXISTS metadata (name TEXT, value TEXT, PRIMARY KEY (name))"; private static final String CREATE_TABLE_TILES = @@ -75,7 +76,9 @@ public class MBTilesStore implements TileStore { this.dataSource = dataSource; } - /** {@inheritDoc} */ + /** + * {@inheritDoc} + */ @Override public ByteBuffer read(TileCoord tileCoord) throws TileStoreException { try (Connection connection = dataSource.getConnection(); @@ -95,7 +98,9 @@ public class MBTilesStore implements TileStore { } } - /** {@inheritDoc} */ + /** + * {@inheritDoc} + */ @Override public void write(TileCoord tileCoord, ByteBuffer blob) throws TileStoreException { try (Connection connection = dataSource.getConnection(); @@ -104,13 +109,39 @@ public class MBTilesStore implements TileStore { statement.setInt(2, tileCoord.x()); statement.setInt(3, reverseY(tileCoord.y(), tileCoord.z())); statement.setBytes(4, blob.array()); - statement.executeUpdate(); + statement.execute(); } catch (SQLException e) { throw new TileStoreException(e); } } - /** {@inheritDoc} */ + /** + * {@inheritDoc} + */ + @Override + public void write(List<TileEntry> tileEntries) throws TileStoreException { + try (Connection connection = dataSource.getConnection()) { + // connection.setAutoCommit(false); + try (PreparedStatement statement = connection.prepareStatement(INSERT_TILE)) { + for (TileEntry tileEntry : tileEntries) { + TileCoord tileCoord = tileEntry.getTileCoord(); + ByteBuffer byteBuffer = tileEntry.getByteBuffer(); + statement.setInt(1, tileCoord.z()); + statement.setInt(2, tileCoord.x()); + statement.setInt(3, reverseY(tileCoord.y(), tileCoord.z())); + statement.setBytes(4, byteBuffer.array()); + statement.execute(); + } + } + // connection.commit(); + } catch (SQLException e) { + throw new TileStoreException(e); + } + } + + /** + * {@inheritDoc} + */ @Override public void delete(TileCoord tileCoord) throws TileStoreException { try (Connection connection = dataSource.getConnection(); diff --git a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresGroup.java b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresGroup.java deleted file mode 100644 index 2c221ce2..00000000 --- a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresGroup.java +++ /dev/null @@ -1,111 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one or more - * contributor license agreements. See the NOTICE file distributed with - * this work for additional information regarding copyright ownership. - * The ASF licenses this file to you under the Apache License, Version 2.0 - * (the "License"); you may not use this file except in compliance with - * the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, software - * distributed under the License is distributed on an "AS IS" BASIS, - * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - * See the License for the specific language governing permissions and - * limitations under the License. - */ - -package org.apache.baremaps.tilestore.postgres; - - - -import java.util.List; -import java.util.Objects; -import java.util.Optional; -import java.util.stream.Collectors; -import net.sf.jsqlparser.statement.select.FromItem; -import net.sf.jsqlparser.statement.select.Join; -import net.sf.jsqlparser.statement.select.SelectItem; - -/** - * Models the groups identified in the input queries of a {@code PostgresTileStore}. These groups - * are used to form common table expressions (CTE). - */ -class PostgresGroup { - - private final List<SelectItem> selectItems; - private final FromItem fromItem; - private final List<Join> joins; - - /** - * Constructs a {@code PostgresGroup} with objects extracted from an AST obtained by parsing a SQL - * query with JSQLParser. - * - * @param selectItems the selected columns. - * @param fromItem the from clause - * @param joins the join clauses - */ - public PostgresGroup(List<SelectItem> selectItems, FromItem fromItem, List<Join> joins) { - this.selectItems = selectItems; - this.fromItem = fromItem; - this.joins = joins; - } - - /** - * Returns the selected columns. - * - * @return the selected columns - */ - public List<SelectItem> getSelectItems() { - return selectItems; - } - - /** - * Returns the from clause. - * - * @return the from clause - */ - public FromItem getFromItem() { - return fromItem; - } - - /** - * Returns the join clauses. - * - * @return the join clauses - */ - public List<Join> getJoins() { - return joins; - } - - /** - * Returns the unique alias of this group. - * - * @return the alias - */ - public String getAlias() { - return String.format("h%x", hashCode()); - } - - /** {@inheritDoc} */ - @Override - public boolean equals(Object o) { - if (this == o) { - return true; - } - if (!(o instanceof PostgresGroup)) { - return false; - } - return hashCode() == o.hashCode(); - } - - /** {@inheritDoc} */ - @Override - public int hashCode() { - String selectItemsString = selectItems.toString(); - String fromItemString = fromItem.toString(); - String joinsString = Optional.ofNullable(joins).stream().flatMap(List::stream) - .map(Join::toString).collect(Collectors.joining()); - return Objects.hash(selectItemsString, fromItemString, joinsString); - } -} diff --git a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresQuery.java b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresQuery.java deleted file mode 100644 index 7afa59bb..00000000 --- a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresQuery.java +++ /dev/null @@ -1,131 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one or more - * contributor license agreements. See the NOTICE file distributed with - * this work for additional information regarding copyright ownership. - * The ASF licenses this file to you under the Apache License, Version 2.0 - * (the "License"); you may not use this file except in compliance with - * the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, software - * distributed under the License is distributed on an "AS IS" BASIS, - * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - * See the License for the specific language governing permissions and - * limitations under the License. - */ - -package org.apache.baremaps.tilestore.postgres; - - - -import net.sf.jsqlparser.JSQLParserException; -import net.sf.jsqlparser.parser.CCJSqlParserUtil; -import net.sf.jsqlparser.statement.select.PlainSelect; -import net.sf.jsqlparser.statement.select.Select; -import net.sf.jsqlparser.statement.select.SelectExpressionItem; -import net.sf.jsqlparser.statement.select.SelectItem; -import net.sf.jsqlparser.statement.select.SelectItemVisitorAdapter; - -/** Models the input queries of a {@code PostgresTileStore}. */ -public class PostgresQuery { - - private final String layer; - private final Integer minzoom; - private final Integer maxzoom; - private final String sql; - private final PlainSelect ast; - - /** - * Constructs a {@code PostgresQuery}. - * - * @param layer the layer name - * @param minzoom the min zoom - * @param maxzoom the max zoom - * @param sql the sql query - */ - public PostgresQuery(String layer, Integer minzoom, Integer maxzoom, String sql) { - this.layer = layer; - this.minzoom = minzoom; - this.maxzoom = maxzoom; - this.sql = sql; - this.ast = parse(sql); - } - - /** - * Returns the layer name. - * - * @return the layer name - */ - public String getLayer() { - return layer; - } - - /** - * Returns the min zoom. - * - * @return the min zoom - */ - public Integer getMinzoom() { - return minzoom; - } - - /** - * Returns the max zoom. - * - * @return the max zoom - */ - public Integer getMaxzoom() { - return maxzoom; - } - - /** - * Returns the SQL query. - * - * @return the SQL query - */ - public String getSql() { - return sql; - } - - /** - * Returns the abstract syntax tree (AST) obtained by parsing the query with JSQLParser. - * - * @return the AST - */ - public PlainSelect getAst() { - return ast; - } - - private PlainSelect parse(String query) { - // Try to parse the query - PlainSelect plainSelect; - try { - Select select = (Select) CCJSqlParserUtil.parse(query); - plainSelect = (PlainSelect) select.getSelectBody(); - } catch (JSQLParserException e) { - String message = String.format("The query is malformed.\n" + "\tQuery:\n\t\t%s", query); - throw new IllegalArgumentException(message, e); - } - - // Check the number of columns - if (plainSelect.getSelectItems().size() != 3) { - String message = String.format("The query is malformed.\n" - + "\tExpected format:\n\t\tSELECT c1::bigint, c2::jsonb, c3::geometry FROM t WHERE c\n" - + "\tActual query:\n\t\t%s", query); - throw new IllegalArgumentException(message); - } - - // Remove all the aliases - for (SelectItem selectItem : plainSelect.getSelectItems()) { - selectItem.accept(new SelectItemVisitorAdapter() { - @Override - public void visit(SelectExpressionItem selectExpressionItem) { - selectExpressionItem.setAlias(null); - } - }); - } - - return plainSelect; - } -} diff --git a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresQueryGenerator.java b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresQueryGenerator.java index 64f65ca7..5774bfba 100644 --- a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresQueryGenerator.java +++ b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresQueryGenerator.java @@ -18,12 +18,12 @@ package org.apache.baremaps.tilestore.postgres; - import java.util.List; import java.util.stream.Collectors; import javax.sql.DataSource; import org.apache.baremaps.postgres.metadata.DatabaseMetadata; import org.apache.baremaps.postgres.metadata.TableMetadata; +import org.apache.baremaps.vectortile.tileset.TilesetQuery; /** * A generator that uses PostgreSQL metadata to generate input queries for a {@code @@ -82,14 +82,14 @@ public class PostgresQueryGenerator { * * @return the queries */ - public List<PostgresQuery> generate() { + public List<TilesetQuery> generate() { return new DatabaseMetadata(dataSource) .getTableMetaData(catalog, schemaPattern, tableNamePattern, types).stream() .filter(table -> table.primaryKeys().size() == 1) .filter(table -> table.getGeometryColumns().size() == 1).map(this::getLayer).toList(); } - private PostgresQuery getLayer(TableMetadata table) { + private TilesetQuery getLayer(TableMetadata table) { String tableSchema = table.table().tableSchem(); String tableName = table.table().tableName(); String layer = String.format("%s.%s", tableSchema, tableName); @@ -102,6 +102,6 @@ public class PostgresQueryGenerator { .collect(Collectors.joining(", ", "hstore(array[", "])")); String sql = String.format("SELECT %s, %s, %s FROM %s", idColumn, tagsColumns, geometryColumn, tableName); - return new PostgresQuery(layer, 0, 20, sql); + return new TilesetQuery(0, 20, sql); } } diff --git a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresTileStore.java b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresTileStore.java index b5edbd83..cca0d304 100644 --- a/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresTileStore.java +++ b/baremaps-core/src/main/java/org/apache/baremaps/tilestore/postgres/PostgresTileStore.java @@ -18,33 +18,23 @@ package org.apache.baremaps.tilestore.postgres; +import org.apache.baremaps.tilestore.TileCoord; +import org.apache.baremaps.tilestore.TileStore; +import org.apache.baremaps.tilestore.TileStoreException; +import org.apache.baremaps.vectortile.tileset.Tileset; +import org.apache.baremaps.vectortile.tileset.TilesetQuery; +import org.slf4j.Logger; +import org.slf4j.LoggerFactory; +import javax.sql.DataSource; import java.io.ByteArrayOutputStream; -import java.io.IOException; +import java.io.OutputStream; import java.nio.ByteBuffer; import java.sql.Connection; import java.sql.ResultSet; -import java.sql.SQLException; import java.sql.Statement; -import java.util.LinkedHashMap; -import java.util.List; import java.util.Map; -import java.util.Optional; -import java.util.stream.Collectors; import java.util.zip.GZIPOutputStream; -import javax.sql.DataSource; -import net.sf.jsqlparser.expression.Expression; -import net.sf.jsqlparser.expression.Parenthesis; -import net.sf.jsqlparser.expression.operators.conditional.OrExpression; -import net.sf.jsqlparser.schema.Column; -import net.sf.jsqlparser.statement.select.Join; -import org.apache.baremaps.tilestore.TileCoord; -import org.apache.baremaps.tilestore.TileStore; -import org.apache.baremaps.tilestore.TileStoreException; -import org.apache.baremaps.tilestore.VariableUtils; -import org.apache.baremaps.vectortile.tileset.Tileset; -import org.slf4j.Logger; -import org.slf4j.LoggerFactory; /** * A read-only {@code TileStore} implementation that uses the PostgreSQL to generate vector tiles. @@ -55,209 +45,126 @@ public class PostgresTileStore implements TileStore { private static final Logger logger = LoggerFactory.getLogger(PostgresTileStore.class); - private static final String TILE_ENVELOPE = "st_tileenvelope(%1$s, %2$s, %3$s)"; - - private static final String WITH_QUERY = "with %1$s %2$s"; - - private static final String CTE_QUERY = - "%1$s as (select * from %3$s%4$s where %5$s st_intersects(%2$s, $envelope))"; - - private static final String CTE_WHERE = "(%s) and"; + public static final String CONTENT_ENCODING = "gzip"; - private static final String STATEMENT_QUERY = - "select st_asmvt(target, '%1$s', 4096, 'geom', 'id') from (%2$s) as target"; + public static final String CONTENT_TYPE = "application/vnd.mapbox-vector-tile"; - private static final String STATEMENT_LAYER_QUERY = "select " + "%1$s as id, " - + "(%2$s || jsonb_build_object('geometry', lower(replace(st_geometrytype(%3$s), 'ST_', '')))) as tags, " - + "st_asmvtgeom(%3$s, $envelope, 4096, 256, true) as geom " + "from %4$s %5$s"; + private final DataSource datasource; - private static final String STATEMENT_WHERE = "where %s"; + private final Tileset tileset; - private static final String UNION = " union all "; + public PostgresTileStore(DataSource datasource, Tileset tileset) { + this.datasource = datasource; + this.tileset = tileset; + } - private static final String COMMA = ", "; + protected boolean zoomPredicate(TilesetQuery query, int zoom) { + return query.getMinzoom() <= zoom && zoom < query.getMaxzoom(); + } - private static final String SPACE = " "; + public String withQuery(TileCoord tileCoord) { + var layers = tileset.getVectorLayers().stream() + .map(layer -> Map.entry(layer.getId(), layer.getQueries().stream() + .filter( + query -> query.getMinzoom() <= tileCoord.z() && tileCoord.z() < query.getMaxzoom()) + .toList())) + .filter(entry -> entry.getValue().size() > 0) + .toList(); - private static final String EMPTY = ""; + var queryBuilder = new StringBuilder(); + queryBuilder.append("SELECT ("); - public static final String CONTENT_ENCODING = "gzip"; + for (int i = 0; i < layers.size(); i++) { + var layer = layers.get(i); + var layerId = layer.getKey(); + var layerQueries = layer.getValue().stream() + .filter(layerQuery -> zoomPredicate(layerQuery, tileCoord.z())).toList(); - public static final String CONTENT_TYPE = "application/vnd.mapbox-vector-tile"; + if (layerQueries.size() > 0) { + if (i > 0) { + queryBuilder.append(" || "); + } - private final DataSource datasource; + var sqlBuilder = new StringBuilder(); + sqlBuilder.append("(WITH mvtgeom AS (\n"); - private final List<PostgresQuery> queries; + for (int j = 0; j < layerQueries.size(); j++) { + if (j != 0) { + sqlBuilder.append("\n UNION \n"); + } + var layerQuery = layerQueries.get(j).getSql().replace(";", ""); + sqlBuilder.append(String.format(""" + SELECT ST_AsMVTGeom(t.geom, ST_TileEnvelope(%d, %d, %d)) AS geom, t.tags, t.id + FROM (%s) AS t + WHERE t.geom && ST_TileEnvelope(%d, %d, %d, margin => (64.0/4096)) + """, + tileCoord.z(), tileCoord.x(), tileCoord.y(), + layerQuery, + tileCoord.z(), tileCoord.x(), tileCoord.y())); - public PostgresTileStore(DataSource datasource, List<PostgresQuery> queries) { - this.datasource = datasource; - this.queries = queries; - } + } - public PostgresTileStore(DataSource datasource, Tileset tileset) { - this.datasource = datasource; - this.queries = tileset.getVectorLayers().stream() - .flatMap(layer -> layer.getQueries().stream().map(query -> new PostgresQuery(layer.getId(), - query.getMinzoom(), query.getMaxzoom(), query.getSql()))) - .toList(); + queryBuilder.append(sqlBuilder) + .append(String.format(") SELECT ST_AsMVT(mvtgeom.*, '%s') FROM mvtgeom\n)", layerId)); + } + } + queryBuilder.append(") mvtTile"); + return queryBuilder.toString().replace("$zoom", String.valueOf(tileCoord.z())); } - /** {@inheritDoc} */ @Override public ByteBuffer read(TileCoord tileCoord) throws TileStoreException { - String sql = withQuery(tileCoord); + String query = withQuery(tileCoord); + + logger.debug("Executing query: {}", query); + + long start = System.currentTimeMillis(); + try (Connection connection = datasource.getConnection(); - Statement statement = connection.createStatement(); - ByteArrayOutputStream data = new ByteArrayOutputStream()) { + Statement statement = connection.createStatement(); + ResultSet resultSet = statement.executeQuery(query)) { int length = 0; - if (queries.stream().anyMatch(query -> zoomPredicate(query, tileCoord.z()))) { - logger.debug("Executing query: {}", sql); - long start = System.currentTimeMillis(); - try (GZIPOutputStream gzip = new GZIPOutputStream(data); - ResultSet resultSet = statement.executeQuery(sql)) { - while (resultSet.next()) { - byte[] bytes = resultSet.getBytes(1); - length += bytes.length; - gzip.write(bytes); - } + try (ByteArrayOutputStream data = new ByteArrayOutputStream(); + OutputStream gzip = new GZIPOutputStream(data)) { + + while (resultSet.next()) { + byte[] bytes = resultSet.getBytes(1); + length += bytes.length; + gzip.write(bytes); } + long stop = System.currentTimeMillis(); long duration = stop - start; // Log slow queries (> 10s) if (duration > 10_000) { - logger.warn("Executed query for tile {} in {} ms: {}", tileCoord, duration, sql); + logger.warn("Executed query for tile {} in {} ms: {}", tileCoord, duration, query); } - } - if (length > 0) { - return ByteBuffer.wrap(data.toByteArray()); - } else { - return null; + if (length > 0) { + return ByteBuffer.wrap(data.toByteArray()); + } else { + return ByteBuffer.allocate(0); + } } - } catch (SQLException | IOException e) { + } catch (Exception e) { + logger.error(e.getMessage()); throw new TileStoreException(e); } } /** - * Returns a WITH query for the provided tile. - * - * @param tileCoord the tile - * @return the WITH query - */ - protected String withQuery(TileCoord tileCoord) { - int zoom = tileCoord.z(); - String sourceQueries = ctes(queries, zoom); - String targetQueries = statements(queries, zoom); - String withQuery = String.format(WITH_QUERY, sourceQueries, targetQueries); - Map<String, String> variables = - Map.of("envelope", tileEnvelope(tileCoord), "zoom", String.valueOf(zoom)); - return VariableUtils.interpolate(variables, withQuery); - } - - /** - * Returns the common table expressions for a list of input queries at a specified zoom level. - * - * @param queries the queries - * @param zoom the zoom level - * @return the common table expressions - */ - protected String ctes(List<PostgresQuery> queries, int zoom) { - return queries.stream().filter(query -> zoomPredicate(query, zoom)) - .collect(Collectors.groupingBy(this::commonTableExpression, LinkedHashMap::new, - Collectors.toList())) - .entrySet().stream().map(entry -> cte(entry.getKey(), entry.getValue())).distinct() - .collect(Collectors.joining(COMMA)); - } - - /** - * Returns the common table expression for a group of queries. - * - * @param group the common table expression - * @param queries the input queries associated with the provided group - * @return the common table expression - */ - protected String cte(PostgresGroup group, List<PostgresQuery> queries) { - String alias = group.getAlias(); - String geom = group.getSelectItems().get(2).toString(); - String from = group.getFromItem().toString(); - String joins = Optional.ofNullable(group.getJoins()).stream().flatMap(List::stream) - .map(Join::toString).collect(Collectors.joining(SPACE)); - String where = queries.stream().map(query -> query.getAst().getWhere()) - .map(Optional::ofNullable).map(o -> o.orElse(new Column("true"))).map(Parenthesis::new) - .map(Expression.class::cast).reduce(OrExpression::new) - .map(expression -> String.format(CTE_WHERE, expression)).orElse(EMPTY); - return String.format(CTE_QUERY, alias, geom, from, joins, where); - } - - /** - * Returns the statements for a list of input queries at a specified zoom level. - * - * @param queries the queries - * @param zoom the zoom level - * @return the statements + * This operation is not supported. */ - protected String statements(List<PostgresQuery> queries, int zoom) { - return queries.stream().filter(query -> zoomPredicate(query, zoom)) - .collect( - Collectors.groupingBy(PostgresQuery::getLayer, LinkedHashMap::new, Collectors.toList())) - .entrySet().stream().map(entry -> layerStatements(entry.getValue(), entry.getKey())) - .collect(Collectors.joining(UNION)); - } - - /** - * Returns the statements for a list of input queries corresponding to a layer. - * - * @param queries the queries - * @param layer the layer name - * @return the statements - */ - protected String layerStatements(List<PostgresQuery> queries, String layer) { - return String.format(STATEMENT_QUERY, layer, queries.stream() - .map(queryValue -> layerStatement(queryValue)).collect(Collectors.joining(UNION))); - } - - /** - * Returns the statement for a query in a layer. - * - * @param query the query - * @return the statement - */ - protected String layerStatement(PostgresQuery query) { - String alias = commonTableExpression(query).getAlias(); - var ast = query.getAst(); - String id = ast.getSelectItems().get(0).toString(); - String tags = ast.getSelectItems().get(1).toString(); - String geom = ast.getSelectItems().get(2).toString(); - String where = Optional.ofNullable(query.getAst().getWhere()) - .map(expression -> String.format(STATEMENT_WHERE, expression)).orElse(EMPTY); - return String.format(STATEMENT_LAYER_QUERY, id, tags, geom, alias, where); - } - - protected boolean zoomPredicate(PostgresQuery query, int zoom) { - return query.getMinzoom() <= zoom && zoom < query.getMaxzoom(); - } - - protected PostgresGroup commonTableExpression(PostgresQuery query) { - return new PostgresGroup( - query.getAst().getSelectItems(), - query.getAst().getFromItem(), - query.getAst().getJoins()); - } - - protected String tileEnvelope(TileCoord tileCoord) { - return String.format(TILE_ENVELOPE, tileCoord.z(), tileCoord.x(), tileCoord.y()); - } - - /** This operation is not supported. */ @Override public void write(TileCoord tileCoord, ByteBuffer blob) { throw new UnsupportedOperationException("The postgis tile store is read only"); } - /** This operation is not supported. */ + /** + * This operation is not supported. + */ @Override public void delete(TileCoord tileCoord) { throw new UnsupportedOperationException("The postgis tile store is read only"); diff --git a/baremaps-core/src/main/java/org/apache/baremaps/utils/SqliteUtils.java b/baremaps-core/src/main/java/org/apache/baremaps/utils/SqliteUtils.java index 9bf6a582..a0677b28 100644 --- a/baremaps-core/src/main/java/org/apache/baremaps/utils/SqliteUtils.java +++ b/baremaps-core/src/main/java/org/apache/baremaps/utils/SqliteUtils.java @@ -20,8 +20,6 @@ package org.apache.baremaps.utils; import com.google.common.io.Resources; -import com.zaxxer.hikari.HikariConfig; -import com.zaxxer.hikari.HikariDataSource; import java.io.IOException; import java.net.URL; import java.nio.charset.StandardCharsets; @@ -31,6 +29,9 @@ import java.sql.DriverManager; import java.sql.SQLException; import java.sql.Statement; import javax.sql.DataSource; + +import com.zaxxer.hikari.HikariConfig; +import com.zaxxer.hikari.HikariDataSource; import org.sqlite.SQLiteConfig; import org.sqlite.SQLiteConfig.JournalMode; import org.sqlite.SQLiteConfig.LockingMode; @@ -52,7 +53,7 @@ public final class SqliteUtils { public static DataSource createDataSource(Path path, boolean readOnly) { var sqliteConfig = new SQLiteConfig(); sqliteConfig.setReadOnly(readOnly); - sqliteConfig.setCacheSize(1000000); + sqliteConfig.setCacheSize(-1000000); sqliteConfig.setPageSize(65536); sqliteConfig.setJournalMode(JournalMode.OFF); sqliteConfig.setLockingMode(LockingMode.EXCLUSIVE); @@ -63,11 +64,11 @@ public final class SqliteUtils { sqliteDataSource.setConfig(sqliteConfig); sqliteDataSource.setUrl("jdbc:sqlite:" + path.toAbsolutePath()); - var hikariConfig = new HikariConfig(); - hikariConfig.setDataSource(sqliteDataSource); - hikariConfig.setMaximumPoolSize(readOnly ? Runtime.getRuntime().availableProcessors() : 1); + var hikariConfig = new HikariConfig(); + hikariConfig.setDataSource(sqliteDataSource); + hikariConfig.setMaximumPoolSize(readOnly ? Runtime.getRuntime().availableProcessors() : 1); - return new HikariDataSource(hikariConfig); + return new HikariDataSource(hikariConfig); } /** diff --git a/baremaps-core/src/main/java/org/apache/baremaps/workflow/tasks/ExportVectorTiles.java b/baremaps-core/src/main/java/org/apache/baremaps/workflow/tasks/ExportVectorTiles.java index 06d1455c..59c24019 100644 --- a/baremaps-core/src/main/java/org/apache/baremaps/workflow/tasks/ExportVectorTiles.java +++ b/baremaps-core/src/main/java/org/apache/baremaps/workflow/tasks/ExportVectorTiles.java @@ -24,8 +24,7 @@ import com.fasterxml.jackson.databind.ObjectMapper; import java.io.IOException; import java.nio.file.Files; import java.nio.file.Path; -import java.util.HashMap; -import java.util.Map; +import java.util.*; import java.util.stream.Collectors; import javax.sql.DataSource; import org.apache.baremaps.config.ConfigReader; @@ -75,12 +74,31 @@ public record ExportVectorTiles( : new Envelope(-180, 180, -85.0511, 85.0511); var count = TileCoord.count(envelope, tileset.getMinzoom(), tileset.getMaxzoom()); - - var stream = - StreamUtils.stream(TileCoord.iterator(envelope, tileset.getMinzoom(), tileset.getMaxzoom())) - .peek(new ProgressLogger<>(count, 5000)); - - StreamUtils.batch(stream).forEach(new TileChannel(sourceTileStore, targetTileStore)); + var start = System.currentTimeMillis(); + + + var tileCoordIterator = + TileCoord.iterator(envelope, tileset.getMinzoom(), tileset.getMaxzoom()); + var tileCoordStream = + StreamUtils.stream(tileCoordIterator).peek(new ProgressLogger<>(count, 5000)); + var bufferedTileEntryStream = StreamUtils.bufferInCompletionOrder(tileCoordStream, tile -> { + try { + return new TileEntry(tile, sourceTileStore.read(tile)); + } catch (TileStoreException e) { + throw new RuntimeException(e); + } + }, 1000); + var partitionedTileEntryStream = StreamUtils.partition(bufferedTileEntryStream, 1000); + partitionedTileEntryStream.forEach(batch -> { + try { + targetTileStore.write(batch); + } catch (TileStoreException e) { + throw new RuntimeException(e); + } + }); + + var stop = System.currentTimeMillis(); + logger.info("Exported {} tiles in {}s", count, (stop - start) / 1000); } private TileStore sourceTileStore(Tileset tileset, DataSource datasource) { diff --git a/baremaps-core/src/test/java/org/apache/baremaps/stream/PartitionedSpliteratorTest.java b/baremaps-core/src/test/java/org/apache/baremaps/stream/PartitionedSpliteratorTest.java index 79476740..ad39b098 100644 --- a/baremaps-core/src/test/java/org/apache/baremaps/stream/PartitionedSpliteratorTest.java +++ b/baremaps-core/src/test/java/org/apache/baremaps/stream/PartitionedSpliteratorTest.java @@ -22,10 +22,10 @@ import static org.junit.Assert.assertFalse; import static org.junit.Assert.assertTrue; import static org.junit.Assert.fail; +import java.util.List; import java.util.Spliterator; import java.util.concurrent.atomic.AtomicInteger; import java.util.stream.IntStream; -import java.util.stream.Stream; import org.junit.jupiter.api.Test; class PartitionedSpliteratorTest { @@ -67,11 +67,11 @@ class PartitionedSpliteratorTest { PartitionedSpliterator<Integer> stream = new PartitionedSpliterator<>(IntStream.range(0, 1000).spliterator(), 250); - Spliterator<Stream<Integer>> a = stream.trySplit(); - Spliterator<Stream<Integer>> b = stream.trySplit(); - Spliterator<Stream<Integer>> c = stream.trySplit(); - Spliterator<Stream<Integer>> d = stream.trySplit(); - Spliterator<Stream<Integer>> e = stream.trySplit(); + Spliterator<List<Integer>> a = stream.trySplit(); + Spliterator<List<Integer>> b = stream.trySplit(); + Spliterator<List<Integer>> c = stream.trySplit(); + Spliterator<List<Integer>> d = stream.trySplit(); + Spliterator<List<Integer>> e = stream.trySplit(); AtomicInteger i = new AtomicInteger(); a.forEachRemaining(s -> s.forEach(item -> { assertEquals(i.get(), (long) item); // cast necessary otherwise call is ambiguous @@ -100,10 +100,10 @@ class PartitionedSpliteratorTest { PartitionedSpliterator<Integer> stream = new PartitionedSpliterator<>(IntStream.range(0, 600).spliterator(), 250); - Spliterator<Stream<Integer>> a = stream.trySplit(); - Spliterator<Stream<Integer>> b = stream.trySplit(); - Spliterator<Stream<Integer>> c = stream.trySplit(); - Spliterator<Stream<Integer>> d = stream.trySplit(); + Spliterator<List<Integer>> a = stream.trySplit(); + Spliterator<List<Integer>> b = stream.trySplit(); + Spliterator<List<Integer>> c = stream.trySplit(); + Spliterator<List<Integer>> d = stream.trySplit(); AtomicInteger i = new AtomicInteger(); a.forEachRemaining(s -> s.forEach(item -> { assertEquals(i.get(), (long) item); // cast necessary otherwise call is ambiguous diff --git a/baremaps-core/src/test/java/org/apache/baremaps/stream/StreamUtilsTest.java b/baremaps-core/src/test/java/org/apache/baremaps/stream/StreamUtilsTest.java index eb2ecc0b..ee1cc354 100644 --- a/baremaps-core/src/test/java/org/apache/baremaps/stream/StreamUtilsTest.java +++ b/baremaps-core/src/test/java/org/apache/baremaps/stream/StreamUtilsTest.java @@ -20,7 +20,6 @@ package org.apache.baremaps.stream; import static org.junit.jupiter.api.Assertions.assertEquals; import java.util.List; -import java.util.stream.Collectors; import java.util.stream.IntStream; import org.junit.jupiter.api.Test; @@ -29,8 +28,7 @@ class StreamUtilsTest { @Test void partition() { List<Integer> list = IntStream.range(0, 100).boxed().toList(); - List<List<Integer>> partitions = StreamUtils.partition(list.stream(), 10) - .map(stream -> stream.collect(Collectors.toList())).toList(); + List<List<Integer>> partitions = StreamUtils.partition(list.stream(), 10).toList(); assertEquals(partitions.size(), 10); } } diff --git a/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/JSQLParserTest.java b/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/JSQLParserTest.java deleted file mode 100644 index 464eab81..00000000 --- a/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/JSQLParserTest.java +++ /dev/null @@ -1,64 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one or more - * contributor license agreements. See the NOTICE file distributed with - * this work for additional information regarding copyright ownership. - * The ASF licenses this file to you under the Apache License, Version 2.0 - * (the "License"); you may not use this file except in compliance with - * the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, software - * distributed under the License is distributed on an "AS IS" BASIS, - * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - * See the License for the specific language governing permissions and - * limitations under the License. - */ - -package org.apache.baremaps.tilestore.postgres; - -import static org.junit.jupiter.api.Assertions.assertNotNull; - -import net.sf.jsqlparser.JSQLParserException; -import net.sf.jsqlparser.parser.CCJSqlParserUtil; -import net.sf.jsqlparser.statement.Statement; -import net.sf.jsqlparser.statement.select.Select; -import org.junit.jupiter.api.Test; - -class JSQLParserTest { - - @Test - void parseArray() throws JSQLParserException { - Statement statement = CCJSqlParserUtil - .parse("SELECT id, hstore(array['tag1', 'tag2'], array[tag1, tag2]), geom FROM table"); - assertNotNull(statement); - } - - @Test - void parseWithStatement() throws JSQLParserException { - String sql = "WITH a AS (SELECT c FROM t) SELECT c FROM a"; - Select select = (Select) CCJSqlParserUtil.parse(sql); - assertNotNull(select); - } - - @Test - void parseUnionStatement() throws JSQLParserException { - String sql = "SELECT a FROM t1 UNION ALL SELECT a FROM t2"; - Select select = (Select) CCJSqlParserUtil.parse(sql); - assertNotNull(select); - } - - @Test - void parseVariable() throws JSQLParserException { - String sql = "SELECT $variable FROM table"; - Select select = (Select) CCJSqlParserUtil.parse(sql); - assertNotNull(select); - } - - @Test - void parseBoolean() throws JSQLParserException { - String sql = "SELECT true"; - Select select = (Select) CCJSqlParserUtil.parse(sql); - assertNotNull(select); - } -} diff --git a/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/PostgresQueryGeneratorTest.java b/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/PostgresQueryGeneratorTest.java index ae2f0a3e..e421e753 100644 --- a/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/PostgresQueryGeneratorTest.java +++ b/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/PostgresQueryGeneratorTest.java @@ -22,6 +22,7 @@ import static org.junit.jupiter.api.Assertions.assertEquals; import java.util.List; import javax.sql.DataSource; import org.apache.baremaps.openstreetmap.postgres.PostgresRepositoryTest; +import org.apache.baremaps.vectortile.tileset.TilesetQuery; import org.junit.jupiter.api.Tag; import org.junit.jupiter.api.Test; @@ -31,7 +32,7 @@ class PostgresQueryGeneratorTest extends PostgresRepositoryTest { @Tag("integration") void generate() { DataSource dataSource = dataSource(); - List<PostgresQuery> queries = + List<TilesetQuery> queries = new PostgresQueryGenerator(dataSource, null, "public", null, null, "TABLE").generate(); assertEquals(3, queries.size()); assertEquals( diff --git a/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/PostgresQueryParserTest.java b/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/PostgresQueryParserTest.java deleted file mode 100644 index 56cf27c8..00000000 --- a/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/PostgresQueryParserTest.java +++ /dev/null @@ -1,107 +0,0 @@ -/* - * Licensed to the Apache Software Foundation (ASF) under one or more - * contributor license agreements. See the NOTICE file distributed with - * this work for additional information regarding copyright ownership. - * The ASF licenses this file to you under the Apache License, Version 2.0 - * (the "License"); you may not use this file except in compliance with - * the License. You may obtain a copy of the License at - * - * http://www.apache.org/licenses/LICENSE-2.0 - * - * Unless required by applicable law or agreed to in writing, software - * distributed under the License is distributed on an "AS IS" BASIS, - * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. - * See the License for the specific language governing permissions and - * limitations under the License. - */ - -package org.apache.baremaps.tilestore.postgres; - -import static org.junit.jupiter.api.Assertions.assertEquals; - -import java.util.Optional; -import org.junit.jupiter.api.Test; - -class PostgresQueryParserTest { - - @Test - void parse1() { - parse("SELECT id, tags, geom FROM table", "id", "tags", "geom", "table", Optional.empty()); - } - - @Test - void parse2() { - parse("select id, tags, geom from table", "id", "tags", "geom", "table", Optional.empty()); - } - - @Test - void parse3() { - parse("SELECT id AS a, tags AS b, geom AS c FROM table", "id", "tags", "geom", "table", - Optional.empty()); - } - - @Test - void parse4() { - parse("select id as a, tags as b, geom as c from table", "id", "tags", "geom", "table", - Optional.empty()); - } - - @Test - void parse5() { - parse("SELECT id, tags, geom FROM table WHERE condition", "id", "tags", "geom", "table", - Optional.of("condition")); - } - - @Test - void parse6() { - parse( - "SELECT id, tags, geom FROM table WHERE tags ? 'building' AND st_geometrytype(geom) LIKE 'ST_Polygon'", - "id", "tags", "geom", "table", - Optional.of("tags ? 'building' AND st_geometrytype(geom) LIKE 'ST_Polygon'")); - } - - @Test - void parse7() { - parse("select id, tags, geom from table where condition", "id", "tags", "geom", "table", - Optional.of("condition")); - } - - @Test - void parse8() { - parse("SELECT id, hstore(ARRAY['tag1', 'tag2'], ARRAY[tag1, tag2]), geom FROM table", "id", - "hstore(ARRAY['tag1', 'tag2'], ARRAY[tag1, tag2])", "geom", "table", Optional.empty()); - } - - @Test - void parse9() { - parse("SELECT id, hstore('tag', tag), geom FROM table", "id", "hstore('tag', tag)", "geom", - "table", Optional.empty()); - } - - @Test - void parse10() { - parse("SELECT id, hstore('tag', tag) as tags, geom FROM table", "id", "hstore('tag', tag)", - "geom", "table", Optional.empty()); - } - - @Test - void parse11() { - parse("SELECT id, tags, st_transform(geom, '1234') as geom FROM table", "id", "tags", - "st_transform(geom, '1234')", "table", Optional.empty()); - } - - @Test - void parse12() { - parse("SELECT id, a(b(c), d(e)), geom FROM table", "id", "a(b(c), d(e))", "geom", "table", - Optional.empty()); - } - - void parse(String sql, String id, String tags, String geom, String from, Optional<String> where) { - PostgresQuery query = new PostgresQuery("layer", 0, 1, sql); - assertEquals(id, String.valueOf(query.getAst().getSelectItems().get(0))); - assertEquals(tags, String.valueOf(query.getAst().getSelectItems().get(1))); - assertEquals(geom, String.valueOf(query.getAst().getSelectItems().get(2))); - assertEquals(from, String.valueOf(query.getAst().getFromItem())); - assertEquals(where, Optional.ofNullable(query.getAst().getWhere()).map(String::valueOf)); - } -} diff --git a/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/PostgresTileStoreTest.java b/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/PostgresTileStoreTest.java index 8b0bc3fe..0e01747f 100644 --- a/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/PostgresTileStoreTest.java +++ b/baremaps-core/src/test/java/org/apache/baremaps/tilestore/postgres/PostgresTileStoreTest.java @@ -19,46 +19,97 @@ package org.apache.baremaps.tilestore.postgres; import static org.junit.jupiter.api.Assertions.assertEquals; -import java.util.Arrays; import java.util.List; +import java.util.Map; import org.apache.baremaps.tilestore.TileCoord; +import org.apache.baremaps.vectortile.tileset.Tileset; +import org.apache.baremaps.vectortile.tileset.TilesetLayer; +import org.apache.baremaps.vectortile.tileset.TilesetQuery; import org.junit.jupiter.api.Test; class PostgresTileStoreTest { @Test void sameQueries() { - List<PostgresQuery> queries = - Arrays.asList(new PostgresQuery("a", 0, 20, "SELECT id, tags, geom FROM table"), - new PostgresQuery("b", 0, 20, "SELECT id, tags, geom FROM table")); - PostgresTileStore tileStore = new PostgresTileStore(null, queries); + Tileset tileset = new Tileset(); + tileset.setMinzoom(0); + tileset.setMaxzoom(20); + tileset.setVectorLayers(List.of( + new TilesetLayer("a", Map.of(), "", 0, 20, + List.of(new TilesetQuery(0, 20, "SELECT id, tags, geom FROM table"))), + new TilesetLayer("b", Map.of(), "", 0, 20, + List.of(new TilesetQuery(0, 20, "SELECT id, tags, geom FROM table"))))); + PostgresTileStore tileStore = new PostgresTileStore(null, tileset); String query = tileStore.withQuery(new TileCoord(0, 0, 10)); assertEquals( - "with ha14cb45b as (select * from table where ((true) OR (true)) and st_intersects(geom, st_tileenvelope(10, 0, 0))) select st_asmvt(target, 'a', 4096, 'geom', 'id') from (select id as id, (tags || jsonb_build_object('geometry', lower(replace(st_geometrytype(geom), 'ST_', '')))) as tags, st_asmvtgeom(geom, st_tileenvelope(10, 0, 0), 4096, 256, true) as geom from ha14cb45b ) as target union all select st_asmvt(target, 'b', 4096, 'geom', 'id') from (select id as id, (tags || json [...] + """ + SELECT ((WITH mvtgeom AS ( + SELECT ST_AsMVTGeom(t.geom, ST_TileEnvelope(10, 0, 0)) AS geom, t.tags, t.id + FROM (SELECT id, tags, geom FROM table) AS t + WHERE t.geom && ST_TileEnvelope(10, 0, 0, margin => (64.0/4096)) + ) SELECT ST_AsMVT(mvtgeom.*, 'a') FROM mvtgeom + ) || (WITH mvtgeom AS ( + SELECT ST_AsMVTGeom(t.geom, ST_TileEnvelope(10, 0, 0)) AS geom, t.tags, t.id + FROM (SELECT id, tags, geom FROM table) AS t + WHERE t.geom && ST_TileEnvelope(10, 0, 0, margin => (64.0/4096)) + ) SELECT ST_AsMVT(mvtgeom.*, 'b') FROM mvtgeom + )) mvtTile""", query); } @Test void differentConditions1() { - List<PostgresQuery> queries = - Arrays.asList(new PostgresQuery("a", 0, 20, "SELECT id, tags, geom FROM table"), - new PostgresQuery("b", 0, 20, "SELECT id, tags, geom FROM table WHERE condition = 1")); - PostgresTileStore tileStore = new PostgresTileStore(null, queries); + Tileset tileset = new Tileset(); + tileset.setMinzoom(0); + tileset.setMaxzoom(20); + tileset.setVectorLayers(List.of( + new TilesetLayer("a", Map.of(), "", 0, 20, + List.of(new TilesetQuery(0, 20, "SELECT id, tags, geom FROM table"))), + new TilesetLayer("b", Map.of(), "", 0, 20, List + .of(new TilesetQuery(0, 20, "SELECT id, tags, geom FROM table WHERE condition = 1"))))); + PostgresTileStore tileStore = new PostgresTileStore(null, tileset); String query = tileStore.withQuery(new TileCoord(0, 0, 10)); - assertEquals( - "with ha14cb45b as (select * from table where ((true) OR (condition = 1)) and st_intersects(geom, st_tileenvelope(10, 0, 0))) select st_asmvt(target, 'a', 4096, 'geom', 'id') from (select id as id, (tags || jsonb_build_object('geometry', lower(replace(st_geometrytype(geom), 'ST_', '')))) as tags, st_asmvtgeom(geom, st_tileenvelope(10, 0, 0), 4096, 256, true) as geom from ha14cb45b ) as target union all select st_asmvt(target, 'b', 4096, 'geom', 'id') from (select id as id, (tags [...] + assertEquals(""" + SELECT ((WITH mvtgeom AS ( + SELECT ST_AsMVTGeom(t.geom, ST_TileEnvelope(10, 0, 0)) AS geom, t.tags, t.id + FROM (SELECT id, tags, geom FROM table) AS t + WHERE t.geom && ST_TileEnvelope(10, 0, 0, margin => (64.0/4096)) + ) SELECT ST_AsMVT(mvtgeom.*, 'a') FROM mvtgeom + ) || (WITH mvtgeom AS ( + SELECT ST_AsMVTGeom(t.geom, ST_TileEnvelope(10, 0, 0)) AS geom, t.tags, t.id + FROM (SELECT id, tags, geom FROM table WHERE condition = 1) AS t + WHERE t.geom && ST_TileEnvelope(10, 0, 0, margin => (64.0/4096)) + ) SELECT ST_AsMVT(mvtgeom.*, 'b') FROM mvtgeom + )) mvtTile""", query); } @Test void differentConditions2() { - List<PostgresQuery> queries = Arrays.asList( - new PostgresQuery("a", 0, 20, "SELECT id, tags, geom FROM table WHERE condition = 1"), - new PostgresQuery("b", 0, 20, "SELECT id, tags, geom FROM table WHERE condition = 2")); - PostgresTileStore tileStore = new PostgresTileStore(null, queries); + Tileset tileset = new Tileset(); + tileset.setMinzoom(0); + tileset.setMaxzoom(20); + tileset.setVectorLayers(List.of( + new TilesetLayer("a", Map.of(), "", 0, 20, + List.of( + new TilesetQuery(0, 20, "SELECT id, tags, geom FROM table WHERE condition = 1"))), + new TilesetLayer("b", Map.of(), "", 0, 20, List + .of(new TilesetQuery(0, 20, "SELECT id, tags, geom FROM table WHERE condition = 2"))))); + PostgresTileStore tileStore = new PostgresTileStore(null, tileset); String query = tileStore.withQuery(new TileCoord(0, 0, 10)); assertEquals( - "with ha14cb45b as (select * from table where ((condition = 1) OR (condition = 2)) and st_intersects(geom, st_tileenvelope(10, 0, 0))) select st_asmvt(target, 'a', 4096, 'geom', 'id') from (select id as id, (tags || jsonb_build_object('geometry', lower(replace(st_geometrytype(geom), 'ST_', '')))) as tags, st_asmvtgeom(geom, st_tileenvelope(10, 0, 0), 4096, 256, true) as geom from ha14cb45b where condition = 1) as target union all select st_asmvt(target, 'b', 4096, 'geom', 'id') [...] + """ + SELECT ((WITH mvtgeom AS ( + SELECT ST_AsMVTGeom(t.geom, ST_TileEnvelope(10, 0, 0)) AS geom, t.tags, t.id + FROM (SELECT id, tags, geom FROM table WHERE condition = 1) AS t + WHERE t.geom && ST_TileEnvelope(10, 0, 0, margin => (64.0/4096)) + ) SELECT ST_AsMVT(mvtgeom.*, 'a') FROM mvtgeom + ) || (WITH mvtgeom AS ( + SELECT ST_AsMVTGeom(t.geom, ST_TileEnvelope(10, 0, 0)) AS geom, t.tags, t.id + FROM (SELECT id, tags, geom FROM table WHERE condition = 2) AS t + WHERE t.geom && ST_TileEnvelope(10, 0, 0, margin => (64.0/4096)) + ) SELECT ST_AsMVT(mvtgeom.*, 'b') FROM mvtgeom + )) mvtTile""", query); } } diff --git a/pom.xml b/pom.xml index bd0d0906..0d86dc05 100644 --- a/pom.xml +++ b/pom.xml @@ -99,7 +99,6 @@ limitations under the License. <version.lib.jaxb-runtime>2.3.5</version.lib.jaxb-runtime> <version.lib.jersey>2.35</version.lib.jersey> <version.lib.jmh>1.35</version.lib.jmh> - <version.lib.jsqlparser>4.2</version.lib.jsqlparser> <version.lib.jts>1.19.0</version.lib.jts> <version.lib.junit>5.7.1</version.lib.junit> <version.lib.junit-vintage>5.7.2</version.lib.junit-vintage> @@ -113,7 +112,7 @@ limitations under the License. <version.lib.servicetalk>0.42.28</version.lib.servicetalk> <version.lib.servlet>3.1.0</version.lib.servlet> <version.lib.slf4j>2.0.7</version.lib.slf4j> - <version.lib.sqlite>3.39.3.0</version.lib.sqlite> + <version.lib.sqlite>3.43.2.0</version.lib.sqlite> <version.lib.swagger-parser>2.1.13</version.lib.swagger-parser> <version.lib.testcontainers>1.17.3</version.lib.testcontainers> <version.lib.validation>2.0.2</version.lib.validation> @@ -184,11 +183,6 @@ limitations under the License. <artifactId>caffeine</artifactId> <version>${version.lib.caffeine}</version> </dependency> - <dependency> - <groupId>com.github.jsqlparser</groupId> - <artifactId>jsqlparser</artifactId> - <version>${version.lib.jsqlparser}</version> - </dependency> <dependency> <groupId>com.google.guava</groupId> <artifactId>guava</artifactId>
