Double quote PostgreSQL table names in the backend

[nicolas-heigvd]

I faced this error after starting the server with a custom layer having non standard SQL characters (must be double quoted):

[WARN ] 2021-02-21 14:54:26.213 [armeria-common-worker-epoll-2-5] HttpResponseSubscriber - [id: 0x1dea6d9c, L:/172.28.0.3:9000 - R:/10.193.21.89:42482] Unexpected exception from a service or a response publisher: com.baremaps.server.TileService@238a9e9c
java.lang.IllegalArgumentException: The SQL query malformed
	at com.baremaps.tile.postgres.PostgisQueryParser.parse(PostgisQueryParser.java:52) ~[baremaps-tile-postgres-0.4.0.jar:?]
	at com.baremaps.tile.postgres.PostgisTileStore.lambda$null$0(PostgisTileStore.java:137) ~[baremaps-tile-postgres-0.4.0.jar:?]
	at java.util.stream.ReferencePipeline$3$1.accept(ReferencePipeline.java:195) ~[?:?]
	at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1655) ~[?:?]
	at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484) ~[?:?]
	at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474) ~[?:?]
	at java.util.stream.ForEachOps$ForEachOp.evaluateSequential(ForEachOps.java:150) ~[?:?]
	at java.util.stream.ForEachOps$ForEachOp$OfRef.evaluateSequential(ForEachOps.java:173) ~[?:?]
	at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[?:?]
	at java.util.stream.ReferencePipeline.forEach(ReferencePipeline.java:497) ~[?:?]
	at java.util.stream.ReferencePipeline$7$1.accept(ReferencePipeline.java:274) ~[?:?]
	at java.util.ArrayList$ArrayListSpliterator.forEachRemaining(ArrayList.java:1655) ~[?:?]
	at java.util.stream.AbstractPipeline.copyInto(AbstractPipeline.java:484) ~[?:?]
	at java.util.stream.AbstractPipeline.wrapAndCopyInto(AbstractPipeline.java:474) ~[?:?]
	at java.util.stream.ReduceOps$ReduceOp.evaluateSequential(ReduceOps.java:913) ~[?:?]
	at java.util.stream.AbstractPipeline.evaluate(AbstractPipeline.java:234) ~[?:?]
	at java.util.stream.ReferencePipeline.collect(ReferencePipeline.java:578) ~[?:?]
	at com.baremaps.tile.postgres.PostgisTileStore.query(PostgisTileStore.java:138) ~[baremaps-tile-postgres-0.4.0.jar:?]
	at com.baremaps.tile.postgres.PostgisTileStore.read(PostgisTileStore.java:110) ~[baremaps-tile-postgres-0.4.0.jar:?]
	at com.baremaps.server.TileService.lambda$doGet$0(TileService.java:58) ~[baremaps-server-0.4.0.jar:?]
	at java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1700) ~[?:?]
	at com.linecorp.armeria.common.RequestContext.lambda$makeContextAware$3(RequestContext.java:522) ~[armeria-1.4.0.jar:?]
	at io.micrometer.core.instrument.internal.TimedRunnable.run(TimedRunnable.java:44) ~[micrometer-core-1.6.3.jar:1.6.3]
	at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515) ~[?:?]
	at java.util.concurrent.FutureTask.run(FutureTask.java:264) ~[?:?]
	at java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:304) ~[?:?]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[?:?]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[?:?]
	at java.lang.Thread.run(Thread.java:834) [?:?]

I suspect the PostgreSQL layer name MUST be escaped in the SQL query defined in the ./config/layer.yaml file, especially when they contain non standard SQL characters such as capital letters or digits, because when I escape the layer name using double quotes, it doesn't print this error at startup.

...
FROM 1_differential_rabbiHousing${zoom}
---

-> goes bad...

...
FROM "1_differential_rabbiHousing"${zoom}
...

-> goes better...

Would it be possible to quote (/check for) double quote around the PostgreSQL table names in the backend?

Here's the config.yaml:

id: OpenStreetMapVecto
server:
  host: localhost
  port: 9000
center:
  lon: 6.5743
  lat: 46.5189
  zoom: 14
bounds:
  minLon: 5.8358
  minLat: 45.6591
  maxLon: 10.9794
  maxLat: 47.8700
  minZoom: 6
  maxZoom: 14 
layers:
  - config/rabbits/1_differential_rabbiHousing/layer.yaml

stylesheets:
  - config/rabbits/1_differential_rabbiHousing/stylesheet.yaml

/config/rabbits/1_differential_rabbiHousing/layer.yaml:

id: 1_differential_rabbiHousing
type: point
queries:
  - minZoom: 13
    maxZoom: 22
    sql: |
      SELECT id, rabbit_id, name, familiy, has_carot, speed, geometry
      FROM "1_differential_rabbiHousing"${zoom}
      WHERE geometrytype(geometry) LIKE '%POINT%'

/config/rabbits/1_differential_rabbiHousing/stylesheet.yaml:

id: 1_differential_rabbiHousing
styles:
  - id: ogc_fid
    layer: 1_differential_rabbiHousing
    type: fill
    layout:
      visibility: visible
    paint:
      fill-color: rgb(0, 0, 228)
      fill-antialias: true

I'm not sure whereas the layer and id in the stylesheet has to represent the same object and what they do refer to?

[bchapuis]

Would it be possible to quote (/check for) double quote around the PostgreSQL table names in the backend?

I think so, could you provide me with a greater excerpt of the config in order to reproduce the error?

[bchapuis]

I suspect the PostgreSQL layer name MUST be escaped in the SQL query defined in the ./config/layer.yaml file, especially when they contain non standard SQL characters such as capital letters or digits, because when I escape the layer name using double quotes, it doesn't print this error at startup.

Yes, it has to be escaped. Also, notice that the zoom level is supposed to be part of the table name. I tried to reproduce your case and I think it is working. Assuming that a table named 1_differential_rabbiHousing_z14 exists in the database, baremaps will be able to create tiles (at zoom level 14) that include the following layer:

id: mylayer
type: linestring
queries:
  - minZoom: 14
    maxZoom: 14
    sql: |
      SELECT id, tags, geom
      FROM "1_differential_rabbiHousing_z${zoom}"

The following exerpt (quoted from your comment) is supposed to fail:

FROM "1_differential_rabbiHousing"${zoom}

I'm not sure whereas the layer and id in the stylesheet has to represent the same object and what they do refer to?

Not necessarily, but naming things is hard... ;)