You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@baremaps.apache.org by bc...@apache.org on 2023/10/21 11:11:03 UTC
[incubator-baremaps] 01/01: Improve tilestore queries and remove jsqlparser
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 <bc...@gmail.com>
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>